Значение в MS EXCEL на пересечении строки и столбца

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

Для поиска значения на пересечении строки и столбца требуется 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:G16 (таблицу продаж вместе с заголовками);
  • нажмите кнопку «Создать из выделенного фрагмента» (Формулы/Определенные имена/ Создать из выделенного фрагмента);
  • убедитесь, что стоят галочки «В строке выше» и «В столбце слева»;
  • нажмите ОК.

Проверить, какие имена были созданы, можно через Диспетчер Имен (Формулы/ Определенные имена/ Диспетчер имен).

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).

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.3 (6 оценок)