Совет: Попробуйте использовать новые функции ПРОСМОТРX и ПОИСКПОЗX, улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их проще и удобнее в использовании, чем их предшественники.
Предположим, что у вас есть список номеров расположения офисов, и вам нужно знать, какие сотрудники находятся в каждом офисе. Электронная таблица огромна, поэтому вы можете подумать, что это сложная задача. Это на самом деле довольно легко сделать с функцией поиска.
Функции ВПР и ГПР вместе с ИНДЕКС и MATCH являются одними из наиболее полезных функций в Excel.
Примечание: Функция мастера подстановки больше не доступна в Excel.
Ниже приведен пример использования функции ВПР.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом — элементом данных, который должен работать функции. Для ВПР этот первый аргумент является значением, которое требуется найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например smith или 21000. Второй аргумент — это диапазон ячеек C2-:E7, в котором выполняется поиск нужного значения. Третий аргумент — это столбец в диапазоне ячеек, содержащий нужное значение.
Четвертый аргумент необязателен. Введите TRUE или FALSE. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым или ввести TRUE, вы получаете большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) ВПР выполняет поиск ячеек в диапазоне C2:E7 (2-й аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца в диапазоне, столбца E (3-й аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы знакомы с функцией ВПР, функция ГПР также проста в использовании. Вы вводите те же аргументы, но выполняется поиск по строкам, а не по столбцам.
Использование ИНДЕКС и MATCH вместо ВПР
При использовании ВПР существуют определенные ограничения: функция ВПР может искать значение только слева направо. Это означает, что столбец, содержащий искать значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если электронная таблица не создана таким образом, не используйте VLOOKUP. Вместо этого используйте сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подстановки, прежде чем опробовать их с собственными данными, вот некоторые примеры данных.
Пример ВПР на работе
Скопируйте следующие данные в пустую электронную таблицу.
Совет: Перед вставкой данных в Excel задайте ширину столбцов от A до C равным 250 пикселям и нажмите кнопку Обтекать текст (вкладка Главная , группа Выравнивание ).
|
Плотность |
Вязкость |
Температура |
|---|---|---|
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Формула |
Описание |
Результат |
|
=ВПР(1,A2:C10,2) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке. |
2,17 |
|
=ВПР(1,A2:C10,3,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке. |
100 |
|
=ВПР(0,7,A2:C10,3,ЛОЖЬ) |
Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(0,1,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(2,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равняется 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке. |
1,71 |
Пример ГПР
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Перед вставкой данных в Excel задайте ширину столбцов от A до C равным 250 пикселям и нажмите кнопку Обтекать текст (вкладка Главная , группа Выравнивание ).
|
Оси |
Подшипники |
Болты |
|---|---|---|
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Формула |
Описание |
Результат |
|
=ГПР(«Оси»;A1:C4;2;ИСТИНА) |
Поиск слова «Оси» в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A). |
4 |
|
=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ) |
Поиск слова «Подшипники» в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B). |
7 |
|
=ГПР(«П»;A1:C4;3;ИСТИНА) |
Поиск буквы «П» в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как «П» найти не удалось, возвращается ближайшее из меньших значений: «Оси» (в столбце A). |
5 |
|
=ГПР(«Болты»;A1:C4;4) |
Поиск слова «Болты» в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C). |
11 |
|
=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА) |
Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как «c» было найдено в строке 2 того же столбца, что и 3, возвращается «c». |
c |
Примеры ИНДЕКС и MATCH
В последнем примере функции ИНДЕКС и MATCH используются вместе для возврата самого раннего номера счета и соответствующей даты для каждого из пяти городов. Так как дата возвращается в виде числа, мы используем функцию TEXT, чтобы отформатировать ее как дату. Функция ИНДЕКС использует результат, возвращенный функцией ПОИСКПОЗ, как аргумент. Сочетание функций ИНДЕКС и ПОИСКПОЗ используется в каждой формуле дважды — сперва для возврата номера счета, а затем для возврата даты.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Перед вставкой данных в Excel задайте ширину столбцов от A до D в 250 пикселей и нажмите кнопку Переносить текст (вкладка Главная , группа Выравнивание ).
|
Счет |
Город |
Дата выставления счета |
Счет с самой ранней датой по городу, с датой |
|---|---|---|---|
|
3115 |
Казань |
07.04.12 |
=»Казань = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),3),»m/d/yy») |
|
3137 |
Казань |
09.04.12 |
=»Орел = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),3),»m/d/yy») |
|
3154 |
Казань |
11.04.12 |
=»Челябинск = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),3),»m/d/yy») |
|
3191 |
Казань |
21.04.12 |
=»Нижний Новгород = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний Новгород»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний Новгород»,$B$2:$B$33,0),3),»m/d/yy») |
|
3293 |
Казань |
25.04.12 |
=»Москва = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),1)& «, Дата выставления счета: » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),3),»m/d/yy») |
|
3331 |
Казань |
27.04.12 |
|
|
3350 |
Казань |
28.04.12 |
|
|
3390 |
Казань |
01.05.12 |
|
|
3441 |
Казань |
02.05.12 |
|
|
3517 |
Казань |
08.05.12 |
|
|
3124 |
Орел |
09.04.12 |
|
|
3155 |
Орел |
11.04.12 |
|
|
3177 |
Орел |
19.04.12 |
|
|
3357 |
Орел |
28.04.12 |
|
|
3492 |
Орел |
06.05.12 |
|
|
3316 |
Челябинск |
25.04.12 |
|
|
3346 |
Челябинск |
28.04.12 |
|
|
3372 |
Челябинск |
01.05.12 |
|
|
3414 |
Челябинск |
01.05.12 |
|
|
3451 |
Челябинск |
02.05.12 |
|
|
3467 |
Челябинск |
02.05.12 |
|
|
3474 |
Челябинск |
04.05.12 |
|
|
3490 |
Челябинск |
05.05.12 |
|
|
3503 |
Челябинск |
08.05.12 |
|
|
3151 |
Нижний Новгород |
09.04.12 |
|
|
3438 |
Нижний Новгород |
02.05.12 |
|
|
3471 |
Нижний Новгород |
04.05.12 |
|
|
3160 |
Москва |
18.04.12 |
|
|
3328 |
Москва |
26.04.12 |
|
|
3368 |
Москва |
29.04.12 |
|
|
3420 |
Москва |
01.05.12 |
|
|
3501 |
Москва |
06.05.12 |
См. также
Краткий справочник: обзор функции ВПР
Функции ссылки и поиска (справка)
Использование аргумента массива таблицы в функции ВПР
Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?
Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX). Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.
Вариант 1. Извлечение данных из столбца по номеру ячейки
Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:
=ИНДЕКС(Диапазон_столбец; Порядковый_номер_ячейки)
Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH), которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP):
… но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.
Вариант 2. Извлечение данных из двумерного диапазона
Если диапазон двумерный, т.е. состоит из нескольких строк и столбцов, то наша функция будет использоваться немного в другом формате:
=ИНДЕКС(Диапазон; Номер_строки; Номер_столбца)
Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.
Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:
Вариант 3. Несколько таблиц
Если таблица не одна, а их несколько, то функция ИНДЕКС может извлечь данные из нужной строки и столбца именно заданной таблицы. В этом случае используется следующий синтаксис:
=ИНДЕКС((Диапазон1;Диапазон2;Диапазон3); Номер_строки; Номер_столбца; Номер_диапазона)
Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой.
Вариант 4. Ссылка на столбец / строку
Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:
Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM), СРЗНАЧ (AVERAGE) и т.п.
Вариант 5. Ссылка на ячейку
Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:
Нечто похожее можно реализовать функцией СМЕЩ (OFFSET), но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.
Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте — это сочетание с функцией СЧЁТЗ (COUNTA), чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.
Ссылки по теме
- Трехмерный поиск данных по нескольким листам (ВПР 3D)
- Поиск и подстановка по нескольким условиям (ВПР по нескольким столбцам)
- Подстановка данных из одной таблицы в другую с помощью функции ВПР (VLOOKUP)
Реклама. АО «ТаймВэб». erid: LjN8KMRrP
Функция ИНДЕКС в Microsoft Excel является мощным инструментом для получения данных из таблицы по определенным координатам. Ее применение становится особенно полезным при работе с большими массивами данных, где требуется найти конкретное значение в зависимости от его расположения. ИНДЕКС позволяет не только получать информацию из таблиц, но и комбинироваться с другими функциями, что делает ее незаменимой при создании сложных и автоматизированных вычислительных схем.
В этой статье я расскажу о различных способах использования функции ИНДЕКС, начиная от базового примера и заканчивая сложными комбинациями с другими функциями. В качестве примеров мы будем использовать таблицу с данными о товарах и их продажах, где каждая строка – это отдельный товар, его категория, количество проданных единиц и цена.
Создание таблицы для примеров
Для начала создадим таблицу с данными, на которой будут основаны все примеры. В этой таблице представлена информация о товарах, их категориях, количестве проданных единиц и ценах. Вы видите ее на следующем изображении и можете в точности повторить для удобства разбора каждого примера.
Учитывайте, что именно значения из этой таблицы будут браться при разборе различных формул с участием функции ИНДЕКС в Экселе.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Пример 1: Простое использование ИНДЕКС для получения значения
Начнем с самого простого примера использования функции ИНДЕКС. Допустим, нам нужно получить значение из конкретной ячейки в таблице. Например, мы хотим узнать цену третьего товара (Товар 3).
В этой задаче мы просто извлекаем значение из конкретной ячейки на пересечении определенной строки и столбца. Таблица состоит из 5 строк (товары) и 4 столбцов (название товара, категория, количество, цена). Нам нужно извлечь цену третьего товара. Для получения значения из третьей строки и четвертого столбца (цена товара) используем формулу:
=ИНДЕКС(D2:D6; 3)
Здесь диапазон D2 – это столбец с ценами, а число 3 указывает на третью строку, откуда мы извлекаем значение. Результатом будет значение 300, которое соответствует цене третьего товара.
Это самый простой пример использования функции ИНДЕКС, когда нужно извлечь конкретное значение из указанной строки и столбца в Microsoft Excel. Функция будет полезна для быстрого доступа к данным без необходимости вручную просматривать таблицу, используя визуальный метод, что особенно неудобно при работе с большими массивами данных или разными листами в рамках одной книги.
Пример 2: Использование ИНДЕКС для работы с двумя измерениями
В таблицах Экселя данные часто организуются в виде двумерных массивов, где информация расположена в строках и столбцах. Функция ИНДЕКС позволяет выбирать данные как по строкам, так и по столбцам, что особенно удобно при работе с большими массивами.
Предположим, нам необходимо получить количество проданных единиц товара, который находится в четвертой строке таблицы и втором столбце (количество). В этом случае мы будем выбирать значение по двум измерениям: строка и столбец.
=ИНДЕКС(A2:D6; 4; 3)
Здесь A2 – это диапазон всей таблицы, 4 – номер строки, а 3 – номер столбца, откуда мы хотим получить значение. В результате формула вернет значение 20, которое соответствует количеству проданных единиц для товара в четвертой строке.
Подводя итог по этому примеру, получается, что функция ИНДЕКС может работать не только с одним столбцом, но и с целыми массивами данных. Это позволяет более гибко выбирать информацию из таблиц, учитывая и строки, и столбцы.
Пример 3: Комбинирование ИНДЕКС с функцией ПОИСКПОЗ
Теперь рассмотрим пример, когда нам нужно выбрать значение на основании определенного условия. В этом случае мы хотим получить цену товара, но знаем только его название. В таких случаях удобно использовать функцию ПОИСКПОЗ в комбинации с ИНДЕКС.
Нам нужно найти цену товара по его названию. Вместо того чтобы вручную искать номер строки с нужным товаром, мы используем функцию ПОИСКПОЗ для нахождения позиции товара, а затем передаем ее в ИНДЕКС для получения значения цены. Чтобы найти цену товара «Товар 2», используем следующую формулу:
=ИНДЕКС(D2:D6; ПОИСКПОЗ("Товар 2"; A2:A6; 0))
Функция ПОИСКПОЗ ищет строку, в которой находится «Товар 2», а ИНДЕКС использует эту строку для получения значения из столбца с ценами. В данном случае результат будет 120.
Комбинация функций ИНДЕКС и ПОИСКПОЗ делает поиск данных в таблицах более автоматизированным и удобным. Мы можем легко находить нужную информацию, не зная ее точного местоположения, что существенно упрощает работу с большими таблицами.
Пример 4: ИНДЕКС с функциями СУММ и МАКС
Теперь рассмотрим более сложный пример, где мы используем ИНДЕКС для нахождения максимальной суммы проданных товаров по категориям. Допустим, нам нужно определить категорию с наибольшей общей стоимостью продаж.
Мы хотим узнать, какая категория товаров принесла наибольшую выручку. Для этого нужно вычислить общую стоимость товаров каждой категории и найти наибольшую сумму. Затем с помощью ИНДЕКС мы выведем название этой категории. Сначала создадим дополнительные столбцы, где для каждой категории вычислим общую стоимость продаж (количество * цена).
= C2 * D2
Если речь идет об уже готовой таблице, столбец с заголовком будет добавлен автоматически, а функция растянута на всю длину таблицы. В нашем случае это будет диапазон F2:F6, который используем для дальнейших расчетов. При необходимости, если ранее не осуществляли форматирование в таблицу, можете растянуть функцию самостоятельно, потянув вниз за ее правый нижний угол.
Теперь в отдельной ячейке можно вывести информацию о том, какая была получена максимальная выручка из всех сумм, созданных только что. Это исключительно вспомогательная информация, которая будет использоваться для визуального представления отчетов в вашей таблице. Для этого используется следующая простая функция:
=МАКС(E2:E6)
Ее действие очень простое и предполагает получение максимального значения из указанного диапазона данных. Так вы будете видеть, каких товаров удалось продать на самую большую сумму.
Осталось только сделать так, чтобы в таблице автоматически менялась не только эта сумма, но и название категории товаров, которых было продано больше всего. Для этого как раз и подойдет более сложная формула с использованием ИНДЕКС.
=ИНДЕКС(B2:B6; ПОИСКПОЗ(МАКС(E2:E6); E2:E6; 0))
В результате этой формулы будет возвращена категория товара с наибольшей выручкой. Например, если самая большая сумма продаж приходится на категорию «Бытовая техника», функция вернет ее название.
Этот пример показывает, как можно использовать ИНДЕКС в сочетании с вычислительными функциями для анализа данных. Комбинируя ИНДЕКС с МАКС, СУММ и ПОИСКПОЗ, мы можем решать сложные задачи и автоматически получать необходимые значения из таблиц.
Заключение
Функция ИНДЕКС в Microsoft Excel – одна из тех, которые позволяют эффективно работать с данными, извлекая нужные значения из массивов на основе их положения. В комбинации с другими функциями, не только теми, которые были описаны в рамках данного материала, она значительно упрощает работу с большими таблицами и автоматизирует вычислительные процессы. Независимо от того, нужно ли вам просто получить данные из ячейки или провести сложные расчеты, ИНДЕКС всегда найдет свое применение в ваших таблицах.
Читайте также
Функция ИНДЕКС помогает извлекать значение из массива данных. Массив может различаться своей структурой, количеством столбцов и строк и даже количество самих массивов может быть больше одного.
Пример работы функции ИНДЕКС в Excel от простого к сложному
Сначала рассмотрим самый простой пример применения. У нас есть несколько городов. Пусть нашей первой задачей будет извлечь пятый по счету город из нашей одномерной базы данных. Синтаксис нашей формулы будет следующий: ИНДЕКС(массив; номер_строки). Начинаем писать формулу (яч. B9). Первый аргумент – это все города B3:C7, а второй – необходимый нам номер ряда, в котором хранится информация, которую мы хотим получить – пятый город:
Функция возвратила значение с второго ряда нашей таблицы. Теперь дополним нашу таблицу информацией о туристах, посетивших город в 2017 году:
Для дальнейшего ознакомления работы функции расширим задачу до двухмерного массива исходных данных.
Функция ИНДЕКС двумерный и многомерный массив
Теперь наш массив двухмерный. Пусть теперешним заданием будет узнать количество туристов в Ливерпуле. В ячейке B19 пишем формулу. Синтаксис тоже видоизменится: ИНДЕКС(массив; номер_строки; номер_столбца), у нас добавится третий аргумент – номер столбца, в котором хранится нужные нам данные:
Аналогично функция работает с трёхмерными массивами, четырёхмерными и так далее. Теперь давайте рассмотрим, как решать задачи, когда таблиц несколько. Пусть у нас будут города трёх стран с количеством посетителей:
На этот раз у нас три двумерные таблицы – три страны с различными городами и их посетителями. Нам необходимо найти количество туристов в городе Ницца страны Франции или, если перефразировать под наш синтаксис, количество посетителей третьего ряда второй колонки второй таблицы. Первым делом, как и раньше, нам нужно выбрать диапазон. НО теперь их у нас три, а значит, все три мы и выбираем, предварительно взяв в скобки эту часть синтаксиса. После чего добавляем уже знакомые нам координаты искомой информации – строку и столбец, а также номер таблицы, которая содержит искомый нами диапазон:
И получаем готовую формулу с решением поставленной задачи:
Пример формулы комбинации функций ИНДЕКС и СУММ
Функцию ИНДЕКC так же можно комбинировать и с другими функциями. Например, наша база данных содержит дополнительно количество туристов Великобритании за 2018-2019 года, мы хотим узнать сумму туристов:
- за 2018 год.
- по городу Манчестер за весь период.
В этом нам поможет известная любому пользователю несложная функция СУММ. Она охватит результат поиска, сделанным ИНДЕКC и выполнит операцию сложения найденных значений. Чтобы выполнить первую задачу, для начала нам нужно выбрать диапазон с одними числами(без столбца с городами, поскольку будем суммировать численные значения C43:E47). Затем на месте, где мы прописывали номер строки, пишем 0. Благодаря этому ИНДЕКC не будет «искать» данные по строкам, а просто перейдет к следующей операции. А следующая операция – прописываем столбец 2 и получаем ответ на первый пункт:
Сумма туристов за 2018 год по пяти городам Великобритании составляет 40 987 людей.
Аналогично этой схеме мы ищем ответ на второй пункт. Только теперь нам нужно считать данные с ряда, поэтому ноль ставим на месте номера столбца:
И получаем ответ на второй пункт – всего посетителей за три года в Манчестере было 2 474 человека.
Для примера зачастую используются самые простые задачи и однотипные решения через функцию. Но в работе объем данных всегда больше и сложнее, поэтому для решения таких заданий используют несколько функций или их сочетание. Несколько таких примеров будем рассматривать дальше.
Формулировку функции можно видоизменять в зависимости от постановки задачи. Пусть нам нужно определить и показать сколько туристов вместе было в 2017-м году в первых трёх городах по списку – Кембридже, Манчестере и Лондоне. Тогда мы снова используем функцию СУММ, но в месте, где указывается конец диапазона значений, по которым мы проводим поиск, вставляем нашу ИНДЕКС. Проще говоря, диапазон для суммирования выглядит так: (начало:конец). Началом будет первая ячейка массива, как обычно, а конец мы заменим. Укажем количество городов, по которым будем считать людей (действие 1). Затем, когда будем составлять функцию ИНДЕКC, укажем, что первый аргумент – числовой диапазон всех пяти городов за 2017-й год, а второй – наше кол-во городов (ячейка D64):
Точно так же можно выполнить следующее задание: найти сумму посетителей Оксфорде за 2017-2018 года. В этом случае делаем всё точно так же, только выбираем горизонтальный диапазон C61:E61 :
В конце необязательно ссылаться на ячейку с написанным количеством искомых значений, можно сразу писать цифру 3 в первом примере и цифру 2 во втором примере, всё будет работать точно так же:
и
идентичны.
Комбинирование нескольких функций могут делать то же, что и одна отдельная функция, но при этом быть менее требовательными к расположению данных, к их размерам или количеству.
Формула ИНДЕКС и ПОИСКПОЗ лучшая замена функции ВПР
Существуют весьма весомые аргументы преимущества использования формулы ИНДЕКС и ПОИСКПОЗ в Excel лучше, чем функция ВПР. Если вы уже знакомы с функцией ВПР, то наверняка знаете, что для её корректной работы искомые данные всегда должны располагаться по правую сторону от критериев:
Получается, что для работы сначала нужно упорядочить столбцы в соответствии с требованиями, а только потом совершать действия. Но иногда сама структура отчета или сводки, с которой нам нужно иметь дело, не позволяет совершать перестановки. Тогда нам очень кстати пригодится ИНДЕКС в сочетании с функцией ПОИСКПОЗ. Синтаксис ИНДЕКСА: (массив; номер_строки; номер_столбца). На первой позиции у нас будет диапазон значений (туристы, 2017), вместо следующих двух пишем ПОИСКПОЗ (искомый критерий;диапазон критериев; 0 (для точного результата)):
Рассмотрим более наглядный и реальный пример, где можно заменить ВПР. У нас есть два отчета: один — о количестве продаж определенного товара, а второй – о цене на упаковку одного товара. И как раз вторая таблица имеет такое расположение столбцов, которое не позволяет использовать нам ВПР – первыми занимают место значения, а вторыми – критерии:
Для того чтобы выполнить заполнение первой таблицы, делаем точно также, как в предыдущем примере – в ячейке D88 пишем ИНДЕКС, первым делом указываем столбец, где находятся искомые значения (цена за упаковку). Затем нужно указать, где нам искать соответствующие критерию значения — подстраиваем ПОИСКПОЗ под наше решение: выбираем искомый критерий (Хепилор), затем массив искомых критериев со второй таблицы:
Теперь просто копируем формулу до конца столбца, но не забываем закрепить ссылки, иначе массив будет спускаться дальше и мы получим некорректные значения. Наша таблица готова:
Если у нас нет данных во второй таблице о цене и препаратах, у нас будет в ячейке ошибка #Н/Д. Теперь к нашим ранее использованным функциям добавим ЕСЛИОШИБКА, которая нам поможет изменить внешний вид отчета, сделать его более понятным для читателя. Опять же, поскольку у нас вторая таблица не соответствует требованиям ВПР, на помощь приходит ИНДЕКС+ПОИСКПОЗ. Усложняем уже имеющуюся формулу, добавляя ЕСЛИОШИБКА, указывая, что при отсутствии данных, мы хотим видеть прочерк:
Скачать примеры функции ИНДЕКС и ПОИСКПОЗ в Excel
Таким образом формула из комбинации функций ИНДЕКС и ПОИСПОЗ работают лучше популярной функции ВПР и не имеют никаких ограничений для выборки данных из таблицы даже по нескольким условиям.
Функция INDEX (ИНДЕКС) в Excel возвращает значение в заданном месте диапазона или массива. Вы можете использовать ИНДЕКС для получения отдельных значений или целых строк и столбцов. Функция ПОИСКПОЗ часто используется вместе с ИНДЕКС для указания номеров строк и столбцов.
Содержание
- Что делает функция INDEX в Excel?
- Возвращаемое значение
- Аргументы
- Синтаксис
- Примечания по использованию
- Основное использование
- ИНДЕКС и СООТВЕТСТВИЕ
- INDEX и MATCH с горизонтальной таблицей
- Вся строка/столбец
- Ссылка как результат
- Две формы
- Форма массива
- Справочная форма
Функция INDEX в Excel позволяет получить значение в списке или таблице в зависимости от местоположения.
Возвращаемое значение
Значение в данном месте.
Аргументы
- array — диапазон ячеек или константа массива.
- row_num — позиция строки в ссылке или массиве.
- col_num — [необязательно] Позиция столбца в ссылке или массиве.
- area_num — [необязательный] Диапазон ссылок, который следует использовать.
Синтаксис
=INDEX(array, row_num, [col_num], [area_num])
Примечания по использованию
Функция ИНДЕКС возвращает значение в заданном месте диапазона или массива. INDEX — мощная и универсальная функция. Вы можете использовать функцию для получения отдельных значений или целых строк и столбцов. ИНДЕКС часто используется вместе с функцией MATCH. В этом сценарии функция находит и передает позицию в функцию ИНДЕКС, и возвращает значение в этой позиции.
В наиболее распространенном использовании INDEX принимает три аргумента: array, row_num, и col_num. array — это диапазон или массив, из которого извлекаются значения. Row_num — это номер строки, из которой извлекается значение, а col_num — это номер столбца, из которого извлекается значение. Col_num является необязательным и не требуется, если массив одномерный.
В примере, показанном выше, цель — получить диаметр планеты Юпитер. Поскольку Юпитер — пятая планета в списке, а Диаметр — третий столбец, формула в G7 имеет вид:
=INDEX(B5:E13,5,3) // диаметр Юпитера
Приведенная выше формула имеет ограниченную ценность, поскольку номер строки и номер столбца жестко запрограммированы. Обычно функция MATCH используется внутри INDEXдля получения этих чисел.
Основное использование
INDEX получает значение в заданном месте в диапазоне ячеек на основе числовой позиции. Если диапазон одномерный, вам нужно указать только номер строки. Если диапазон двумерный, вам потребуется указать номер строки и столбца. Например, чтобы получить третий элемент из одномерного диапазона A1:A5:
=INDEX(A1:A5,3) // возвращает значение в A3
Формулы ниже показывают, как можно использовать ИНДЕКС для получения значения из двумерного диапазона:
=INDEX(A1:B5,2,2) // возвращает значение в B2
=INDEX(A1:B5,3,1) // возвращает значение в A3
ИНДЕКС и СООТВЕТСТВИЕ
В приведенных выше примерах позиция «жестко запрограммирована». Обычно функция MATCH используется для поиска позиций по INDEX. Например, на экране ниже функция MATCH используется для поиска «Марса» (G6) в строке 3 и передачи этой позиции в ИНДЕКС. Формула в G7:
=INDEX(B5:E13,MATCH(G6,B5:B13,0),3)
ПОИСКПОЗ передает номер строки (4) в ИНДЕКС. Номер столбца по-прежнему жестко запрограммирован как 3.
INDEX и MATCH с горизонтальной таблицей
На экране ниже приведенная выше таблица транспонирована горизонтально. Функция MATCH возвращает номер столбца (4), а номер строки жестко запрограммирован как 2. Формула в C10:
=INDEX(C4:K6,2,MATCH(C9,C4:K4,0))
Вся строка/столбец
INDEX можно использовать для возврата целых столбцов или строк, например:
=INDEX(range,0,n) // вся колонна целиком
=INDEX(range,n,0) // весь ряд
где n представляет номер возвращаемого столбца или строки.
Ссылка как результат
Важно отметить, что функция ИНДЕКС в результате возвращает ссылку. Например, в следующей формуле INDEX возвращает A2:
=INDEX(A1:A5,2) // возвращает A2
В типичной формуле в качестве результата вы увидите значение в ячейке A2, поэтому неочевидно, что ИНДЕКС возвращает ссылку. Однако это полезная функция в таких формулах, как эта, которая использует функцию для создания динамического именованного диапазона. Вы можете использовать функцию CELL , чтобы сообщить ссылку, возвращаемую INDEX.
Две формы
Функция ИНДЕКС имеет две формы: массив и ссылку. Обе формы ведут себя одинаково: INDEX возвращает ссылку в массиве на основе заданного местоположения строки и столбца. Разница в том, что ссылочная форма функцию позволяет использовать более одного массива вместе с необязательным аргументом для выбора того, какой массив следует использовать. В большинстве формул используется массив ИНДЕКС, но обе формы обсуждаются ниже.
Форма массива
В форме массива INDEX первым параметром является массив , который предоставляется в виде диапазона ячеек или константы массива. Синтаксис массива ИНДЕКС:
INDEX(array,row_num,[col_num])
- Если указаны и row_num, и col_num, INDEX возвращает значение в ячейке на пересечении row_num и col_num .
- Если row_num установлен в ноль, функция возвращает массив значений для всего столбца. Чтобы использовать эти значения массива, вы можете ввести функцию ИНДЕКС как формулу массива в горизонтальном диапазоне или передать массив в другую функцию.
- Если для столбца col_num установлено нулевое значение, INDEX возвращает массив значений для всей строки. Чтобы использовать эти значения массива, вы можете ввести функцию ИНДЕКС как формулу массива в вертикальном диапазоне или передать массив в другую функцию.
Справочная форма
В ссылочной форме INDEX первый параметр представляет собой ссылку на один или несколько диапазонов, а четвертый необязательный аргумент, area_num, предоставляется для выбора соответствующего диапазона. Синтаксис ссылочной формы ИНДЕКС:
INDEX(reference,row_num,[col_num],[area_num])
Как и форма массива INDEX, ссылочная форма INDEX возвращает ссылку на ячейку на пересечении row_num и col_num. Разница в том, что аргумент ссылки содержит более одного диапазона, а параметр area_num выбирает, какой диапазон следует использовать. Аргумент area_num предоставляется в виде числа, действующего как числовой индекс. Первый массив внутри ссылки равен 1, второй массив — 2 и так далее.
Например, в приведенной ниже формуле для параметра area_num указано значение 2, что соответствует диапазону A7:C10:
=INDEX((A1:C5,A7:C10),1,3,2)
В приведенной выше формуле ИНДЕКС вернет значение в строке 1 и столбце 3 A7:C10.
- Несколько диапазонов в ссылке разделяются запятыми и заключаются в круглые скобки.
- Все диапазоны должны находиться на одном листе, иначе ИНДЕКС вернет ошибку #ЗНАЧЕНИЕ. В качестве обходного пути используйте функцию CHOOSE.