Как найти ячейку на пересечении

Для поиска значения на пересечении строки и столбца требуется наличие таблицы специального вида: в строке заголовков и самом левом столбце должны быть неповторяющиеся значения.

Для поиска значения на пересечении строки и столбца требуется 2 критерия: «координаты» по строке и столбцу. Для однозначного поиска требуется, чтобы в строке заголовков и самом левом столбце, по которым будет производиться поиск значений, находились

неповторяющиеся

значения.

Примером такой задачи может служить таблица умножения: первый множитель определяет координату в строке, а второй – в столбце, результат (произведение) – на пересечении.

Другой пример – табель учета рабочего времени: ищется дата (диапазон поиска – строка), затем сотрудник (диапазон поиска – столбец), а на их пересечении – результат (присутствовал на работе, был в отгуле или в отпуске).

Задача

Создадим таблицу продаж машин различных марок по месяцам. Выбирая марку машины и месяц, пользователь получает число проданных машин.

Для поиска значения на пересечении строки и столбца можно использовать разные подходы (см.

файл примера, лист Пример1

).

Поиск с помощью функции ИНДЕКС()

Запишем длинную, но простую для понимания формулу

=ИНДЕКС($B$13:$G$21;ПОИСКПОЗ(D10;$A$13:$A$21;0);ПОИСКПОЗ(E9;$B$12:$G$12;0))

Две функции

ПОИСКПОЗ()

определяют номер строки и столбца для функции

ИНДЕКС()

. Для однозначного поиска номера строки (столбца), в этих диапазонах не должно быть повторов.

Поиск с помощью функции СУММПРОИЗВ()

Другой вариант поиска – использование функции

СУММПРОИЗВ()

=СУММПРОИЗВ((B12:G12=J9)*(A13:A21=I10)*(B13:G21))

Поиск методом пересечения

В EXCEL существует малоизвестный

метод

Пересечений

, основанный на использовании

именованных диапазонов

. Для создания пересечения сделайте следующее:

• выделите диапазон
  
  A7:
  
  G
  
  16
  
  (таблицу продаж вместе с заголовками);
• нажмите кнопку «
  
  Создать из выделенного фрагмента
  
  »
  
  (
  
  );
• убедитесь, что стоят галочки «
  
  В строке выше
  
  » и «
  
  В столбце слева
  
  »;
• нажмите ОК.

Проверить, какие

имена

были созданы, можно через

Диспетчер Имен

(

).

EXCEL создал 15 именованных диапазонов. В качестве имен использованы 6 названий месяцев и 9 марок автомобилей. Теперь произведем, собственно, поиск.

• введите в ячейки
  
  А10
  
  и
  
  B9
  
  два критерия:
  
  Янв
  
  и
  
  Saab
  
  (определим продажи Saab в январе)
• введите формулу
  
  =ДВССЫЛ(A10) ДВССЫЛ(B9)
  
  (между функциями
  
  ДВССЫЛ()
  
  – пробел).
• Нажмите
  
  ENTER
  
  .

При вычислении, вместо формул с

ДВССЫЛ()

будут подставлены, определенные ранее имена:

Янв

и

Saab

, совпадающие с критериями. Наличие пробела означает, что будет использован

метод Пересечений

– будет выведено значение на пересечении соответствующих именам строке и столбцу.

Разберем подробнее. В

Строке формул

выделите

ДВССЫЛ(A10)

и нажмите

клавишу

F9

.

Получим значения соответствующего именованного диапазона

Saab

{

3600

;1520;5480;4588;5336;2588}. Затем выделите

ДВССЫЛ(B9)

. Получим другой массив {4064:1992:812:3185:4617:

3600

:5594:4218:3637}. Метод пересечений вернет значение на пересечении строки и столбца, т.е.

3600

.

Пример 2

Второй пример – это определение зарплаты сотрудника по ведомости (см. файл примера, лист Пример2).

Выбрав Фамилию и Квартал, можно узнать зарплату.

Решение основано на использовании формул, рассмотренных в предыдущем примере.

Оператор пересечения – один из основных операторов в Microsoft Excel, однако знаком он только небольшому количеству пользователей. В данной статье я исправлю эту ситуацию и детально расскажу о том, что это за оператор и в каких ситуациях может использоваться.

Для чего нужен оператор пересечения

Обратите внимание на следующий скриншот. На нем вы видите таблицу со столбцами и строками. Выделен столбец со строкой, и есть ячейка, в которой они пересекаются. Оператор пересечения и нужен для поиска этой ячейки. По умолчанию он выведет как раз значение, которое находится в этом месте.

Стандартное использование оператора пересечения

Для начала давайте разберемся с тем, как просто использовать оператор пересечения и вывести в пустой ячейке то самое искомое значение. Вам понадобится любая таблица, состоящая из нескольких столбцов и строк с заполненными данными. Далее остается выполнить следующие действия:

1. Выделите пустую ячейку для вывода результата, в ней добавьте знак = и выделите левой кнопкой мыши всю строку.

2. После этого поставьте пробел. Этот знак и является оператором пересечения.

3. После пробела укажите диапазон столбца, точно так же выделив его левой кнопкой мыши.

4. Нажмите клавишу Enter, чтобы ознакомиться с результатом. Как видно, оператор узнал, какая ячейка есть как в столбце, так и в строке, отобразив ее значение в клетке.

Использование оператора пересечения в формулах

Описанная выше формула с применением оператора пересечения хоть и кажется интересной, но в чистом виде практически не используется, поскольку и внешне понятно, какое значение есть в столбце и строке. Чаще этот оператор используют вместе с разными функциями при расчетах. Вы можете сделать строку по примеру =СУММ(A3:E3 C1:C5)+500, чтобы оператором пересечения найти ячейку и добавить к ней еще 500. Это довольно простая и стандартная операция. 

Примерно то же самое можно сделать и с функцией ЕСЛИ. Для логического значения используется оператор пересечения и срабатывает определенный результат лжи или истины, который вы настраиваете самостоятельно при помощи аргументов функции.

Дополнительные сведения про оператор пересечения

В завершение давайте рассмотрим еще несколько интересных моментов работы с оператором, которые могут оказаться полезными при проведении расчетов в электронной таблице. Например, вы можете взять диапазон, состоящий не только из одного столбца и строки, но и из нескольких, последовательно выделив все ячейки примерно так, как это показано на скриншоте.

По умолчанию оператор пересечения не сможет вывести два или более значений, поэтому такое выделение диапазонов используется исключительно с другими функциями. Например, вы можете суммировать все значения, подходящие под пересечение.

Вместе с этим можно найти минимальное значение среди них, применив знакомую всем формулу МИН. Не забывайте только ставить пробел между столбцами и строками, поскольку это и есть знак оператора пересечения.

Если вы используете умную таблицу или для каждого столбца со строками задали свои названия, то при выделении с использованием оператора вместо стандартных обозначений ячеек вы можете вводить целые названия диапазонов. Тогда вид формулы будет примерно такой, как это показано на следующем изображении.

В заключение

На этом все, и базовые принципы работы с оператором пересечения мы рассмотрели. Надеюсь, вы сможете применить эти навыки в будущих расчетах, чтобы упростить процесс работы с электронными таблицами.

Читайте также: 

1. Как включить калькулятор в Microsoft Excel.
2. Как работать с шаблонами в Microsoft Excel.
3. Как защитить диапазон, лист и книгу в Microsoft Excel.

При работе с данными время от времени возникают ситуации, когда нам необходимо найти значение ячейки на пересечении определенных строки и столбца.

В данной статье я Вам расскажу о трёх способах осуществления этой задачи.

1. ИНДЕКС (INDEX)

Данная функция позволяет извлечь значение из диапазона ячеек по номеру строки и столбца.

Аргументы функции выглядят следующим образом:

Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;

Номер_строки – строка, из которой нужно извлечь значения;

Номер_столбца – столбец, из которого нужно извлечь значение.

Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности.

Схематически работу функции можно изобразить так:

Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).

В таком случае формула выглядит примерно так:

=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))

При смене параметров поиска, значение пересекающейся ячейки будет изменяться автоматически.

2. СУММПРОИЗВ (SUMPRODUCT)

Очень интересная функция, которой можно найти много применений. Она перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.

Для решения нашей задачи формула будет выглядеть так:

=СУММПРОИЗВ((C6:F6=I6)*(B7:B9=I5)*(C7:F9))

Простыми словами синтаксис функции можно представить так:

=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))

Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.

3. ДВССЫЛ (INDIRECT)

Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).

Функция ДВССЫЛ возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого. Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы.

Если на первый взгляд функция выглядит простой и малополезной, то при дальнейшем её изучении Вы узнаете, что с её помощью мы можем перемещаться по листам, транспонировать таблицы, отбирать чётные (нечётные) строки и многое другое. Ведь для нее аргумент Ссылка_на_ячейку – всего лишь текстовая строка, которую можно изменять формулами.

Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.

Для начала нам необходимо создать диапазоны для строк и столбцов.

Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3

Аналогичную операцию проделываем и для строк.

Сама формула будет иметь такой вид:

=ДВССЫЛ(I5) ДВССЫЛ(I6)

Обратите внимание что функции разделены пробелом.

Excel даёт нам возможность использовать несколько вариантов решения для одной задачи. Там, где невозможно применить один способ, практически всегда есть возможность подобрать другой. Настоятельно рекомендую Вам изучить функции, рассмотренные в данной статье и Ваша работа с данными, станет намного эффективнее.

Автор: Михаил Беленчук, тренер DATA bi

Если вы знакомы с функцией ВПР (VLOOKUP) или ее горизонтальным аналогом ГПР (HLOOKUP), то должны помнить, что эта замечательные функции ищут информацию только по одному параметру, т.е. в одномерном массиве – по строке или по столбцу. А если нам необходимо выбирать данные из двумерной таблицы по совпадению сразу двух параметров – и по строке и по столбцу одновременно? Давайте рассмотрим несколько жизненных примеров таких задач и их решения.

Пример 1. Найти значение по товару и городу

Предположим, что у нас имеется вот такой двумерный массив данных по городам и товарам:

Пользователь вводит (или выбирает из выпадающих списков) в желтых ячейках нужный товар и город. В зеленой ячейке нам нужно формулой найти и вывести число из таблицы, соответствующее выбранным параметрам. Фактически, мы хотим найти значение ячейки с пересечения определенной строки и столбца в таблице. Для наглядности, разобъем задачу на три этапа.

• Во-первых, нам нужно определить номер строки, соответствующей выбранному пользователем в желтой ячейке товару. Это поможет сделать функция ПОИСКПОЗ (MATCH) из категории Ссылки и массивы (Lookup and Reference). В частности, формула ПОИСКПОЗ(J2; A2:A10; 0) даст нам нужный результат (для Яблока это будет число 6). Первый аргумент этой функции – искомое значение (Яблоко из желтой ячейки J2), второй – диапазон ячеек, где мы ищем товар (столбец с товарами в таблице – A2:A10), третий аргумент задает тип поиска (0 – точное совпадение наименования, приблизительный поиск запрещен).
• Во-вторых, совершенно аналогичным способом мы должны определить порядковый номер столбца в таблице с нужным нам городом. Функция ПОИСКПОЗ(J3; B1:F1; 0) сделает это и выдаст, например, для Киева, выбранного пользователем в желтой ячейке J3 значение 4.
• И, наконец, в-третьих, нам нужна функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца – функция ИНДЕКС (INDEX) из той же категории Ссылки и массивы (Lookup and Reference). Первый аргумент этой функции – диапазон ячеек (в нашем случае это вся таблица, т.е. B2:F10), второй – номер строки, третий – номер столбца (а их мы определим с помощью функций ПОИСКПОЗ).

Итого, соединяя все вышеперечисленное в одну формулу, получаем для зеленой ячейки решение:

=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))

или в английском варианте

=INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))

Пример 2. Приблизительный двумерный поиск

Слегка модифицируем предыдущий пример. Предположим, что у нас имеется вот такая ситуация:

Идея в том, что пользователь должен ввести в желтые ячейки высоту и ширину двери для, например, шкафа, которую он хочеть заказать у компании-производителя, а в серой ячейке должна появиться ее стоимость из таблицы. Важный нюанс в том, что если пользователь вводит нестандартные значения размеров, то они должны автоматически округлиться до ближайших имеющихся в таблице и в серой ячейке должна появиться стоимость изготовления двери для этих округленных стандарных размеров.

Решение для серой ячейки будет практически полностью аналогично предыдущему примеру:

=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))

=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))

Разница только в последнем аргументе обеих функций ПОИСКПОЗ (MATCH) – Типу сопоставления (здесь он равен минус 1). Это некий аналог четвертого аргумента функции ВПР (VLOOKUP) – Интервального просмотра (Range Lookup). Вообще говоря, возможных значений для него три:

• 1 – поиск ближайшего наименьшего числа, т.е. введенные пользователем размеры двери округлялись бы до ближайших наименьших подходящих размеров из таблицы. В нашем случае высота 500 округлилась бы до 450, а ширина 480 до 300, и стоимость двери была бы 135.
• -1 – поиск ближайшего наибольшего числа, т.е. нестандартная высота 500 округлялась бы до 700, а ширина 480 – до 600 и стоимость составила бы уже 462. Для бизнеса так гораздо интереснее! 🙂
• 0 – поиск точного соответствия без каких либо округлений. Используется для 100%-го совпадения искомого значения с одним из значений в таблице. Естественно, применяется при поиске текстовых параметров (как в прошлом примере), т.к. для них округление невозможно.

Важно отметить, что при использовании приблизительного поиска с округлением диапазон поиска – а значит и вся таблица – должна быть отсортирована по возрастанию (для Типа сопоставления = 1) или по убыванию (для Типа сопоставления = -1) по строчкам и по столбцам. Иначе приблизительный поиск корректно работать не будет!

Для точного поиска (Тип сопоставления = 0) сортировка не нужна и никакой роли не играет.

P.S. Обратная задача

В комментах неоднократно интересуются – а как сделать обратную операцию, т.е. определить в первом примере город и товар если мы знаем значение из таблицы? Тут потребуются две небольшие формулы массива (не забудьте ввести их с помощью сочетания клавиш Ctrl+Shift+Enter, а не обычного Enter):

Принцип их работы следующий:

1. перебираем все ячейки в диапазоне B2:F10 и ищем совпадение с искомым значением (13) из ячейки J4 с помощью функции ЕСЛИ (IF)
2. когда нашли совпадение, то определяем номер строки (столбца) первого элемента в таблице в этой строке (столбце) с помощью функций СТОЛБЕЦ (COLUMN) и СТРОКА (ROW)
3. выдергиваем значение города или товара из таблицы с помощью функции ИНДЕКС (INDEX)

Ссылки по теме

• Использование функции ВПР (VLOOKUP) для подстановки значений
• Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПОЗ
• Улучшаем функцию ВПР (VLOOKUP2)
• ВПР (VLOOKUP) с учетом регистра
• Многоразовый ВПР для вывода сразу всех значений