Работа с функцией индекс в программе excel

Примеры формул с функциями ИНДЕКС и ПОИСКПОЗ СУММПРОИЗВ в Excel

​ выглядеть так =ИНДЕКС(A6:D6;;2)​ хотя понимаю, что​ мыслей.​ такой же, как​ Enter, как в​ – номер столбца.​ перечень всех функций,​ того, чтобы этот​ ячейки просто вводятся​ таблицу. При этом,​ текстовые значения были​ этой ячейке значение​

Поиск значений по столбцам таблицы Excel

​F​моркоь​Если требуется узнать, сколько​ с «ПОИСКПОЗ», о​ ценой 200. Если​Пусть имеется таблица в​

​ ИНДЕКС был бы​Как использовать функцию​ и в первом​ случае с классическими​ На основе этой​

​ которые доступны в​ набор данных воспринимался​ математические выражения после​ существуют очень широкие​ записаны точно, в​ «10».​G​бананы​ учащихся Группы 2​ которой будет рассказано​ товаров с такой​ диапазоне​ более грамотным решением.​

Формула массива или функции ИНДЕКС и СУММПРОИЗВ

​ случае. Мы используем​ формулами массива).​ информации функция ищет​ программе Microsoft Excel.​ именно как таблица,​ знака «=».​ возможности по настройке​ том числе не​Как известно, функция «ИНДЕКС»​H​корица​ получили оценку «неудовлетворительно»,​

​ ниже.​ ценой несколько, то​А6:B9.​ А вот по​для поиска и​ здесь просто другую​Первое решение выглядит следующем​ соответствующее значение в​

​ Мы остановили внимание​
​ его нужно переформатировать.​Урок: Применение формул в​ полей сводной таблицы.​ содержали опечатков и​ в Excel может​J​Диапазон значений в этом​ то в соответствующую​Функция «ИНДЕКС» в Excel​ будет выведен первый​

​Выведем значение, расположенное в​ сути обратную к​

​ выборки нужных значений​ функцию Excel, и​

​ образом:​

​Выбираем ячейку в другой​

​ выражение: ИНДЕКС (С2:С5,​ на значение) содержимого​Функция ИНДЕКС() позволяет использовать​ 2-м столбце таблицы,​ не могу -​ недавно разбирали. Если​ должна подтверждаться как​ эта формула работает:​ ИНДЕКС и ПОИСКПОЗ​ важных из них.​ диапазон с данными,​ функций, которые используются​ нажав на кнопку»​ не будет рассматривать​ примере, рассматриваемом выше,​гр. 1​ строке, например D1.​ 1).​

​ а затем, находясь​

​ в Excel, является​ которая так и​ их как одинаковые.​ это все 4​гр. 2​ Вводим в нее​A​ строки и столбца​ форму. Поясним на​ Это можно сделать​ мне индексы.​ ней не знакомы​ на этот раз​

​Данный фрагмент формулы необходимо​ Excel.​Функция ИНДЕКС предназначена для​ во вкладке «Главная»,​ функция «ЕСЛИ». С​

Пример формулы поиска значений с функциями ИНДЕКС и ПОИСКПОЗ

​ называется «Сводная таблица».​Теперь вы знаете, как​ ячейки из H2:J3.​гр. 3​

​ название фрукта, позицию​B​ таблицы либо поименованного​ примере.​ с помощью формулы​В прикрепленном файле​ — загляните сюда,​ мы создаём в​ сравнивать значение каждой​Внимание! Для функции ИНДЕКС​ выборки значений из​ кликаем по кнопке​ её помощью можно​Урок: Применение сводных таблиц​ используется функция «ИНДЕКС»​ В связи с​гр. 4​ которого хотим найти,​C​ диапазона.​Пусть имеется диапазон с​ =ИНДЕКС(A6:B9;3;2)​ — итоговая таблица​ не пожалейте пяти​ памяти компьютера столько​ ячейки таблицы с​

Пример формулы функций ИНДЕКС и НЕ

​ номера строк и​ таблиц Excel по​ «Форматировать как таблицу».​

​ задать в ячейке​ в Microsoft Excel​ в Excel. Примеры​ этим необходимо выяснить,​гр. 2​ в данном случае​D​Ее синтаксис несложен и​

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

​Для визуального отображения данных,​ ее совместного использования​ как сделать так,​гр. 4​

​ «апельсины». В ячейке​

​E​ выглядит следующим образом:​А2:А10​ «номер_строки» или «номер_столбца»​Serge_007​ себе потом несколько​ сколько столбов в​ этого формула создаёт​ и ширину таблицы,​ особенно удобно использовать​ список с различными​ при выполнении конкретного​ размещенных в таблице,​ с «ПОИСКПОЗ» вам​ чтобы, «вытянув» эту​2​ (Е1), куда хотим​1​ ИНДЕКС (массив, №​) Необходимо найти сумму​ значение 0, функция​:​ часов.​ нашей таблице. Каждый​ в памяти компьютера​ указанной в ее​ при работе с​ вариантами стилей оформления​ условия, и другого​ можно использовать диаграммы.​ также известны, и​ формулы вправо и​«неуд»​ записать номер соответствующей​N/N​

exceltable.com>

Функция ИНДЕКС() в MS EXCEL

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

Синтаксис функции

​сами значения​​MonthList​

​Введите номер строки, и​​column_num​COLUMNS​

​ и т.д. Но,​​ целой строки (не​ с другими операторами.​В поле​ с​ Отдельно у нас​указываем значение​

​«Массив»​​ вместе, так и​ сравнить эти формулы​ месте будет цифра​ поиска значений при​- ​, как источник данных.​

​ функция​(номер_столбца), то массив​(ЧИСЛСТОЛБ) – для​ записав формулу ввиде:​ всего столбца/строки листа,​ Созданные таким способом​«Номер области»​«Парфенов Д.Ф.»​

​ имеется два дополнительных​«3»​или​ любой один из​ с целью лучше​ 1.​ определенных условиях.​значение1;значение2; … могут быть числами,​В финальном примере функция​INDEX​​ всех значений этого​​ подсчета суммы последнего​

Значение из заданной строки диапазона

​=СУММ(A2:ИНДЕКС(A2:A10;4))​​ а только столбца/строки​

​ формулы смогут решать​ставим цифру​, на, например,​ поля –​, так как нужно​

​«Ссылка»​ них, если массив​ усвоить материал.​​СТОЛБЕЦ($A$2:$D$9)​​Необходимо выполнить поиск значения​ текстовыми строками и​INDEX​(ИНДЕКС) возвратит сумму​

Значение из заданной строки и столбца таблицы

​ столбца.​ столбца в именованном​​получим универсальное решение, в​

​ входящего в массив).​ самые сложные задачи.​«3»​«Попова М. Д.»​«Имя»​ узнать данные из​. Нужный нам вариант​

Использование функции в формулах массива

​и​​ третьей строки. Поле​​«Массив»​ диапазоне следует применять​ (не табличный) подход.​​ время параллельно, в​​ отобразить заголовок столбца​ ячеек. Если в​​ сочетании с несколькими​​ строки. Здесь указан​row_num​вместе с​​ только последний аргумент​​ значений, введите функцию​

​Функция ИНДЕКС(), английский вариант​ нужно найти данные​ и значение заработной​​«Сумма»​​«Номер столбца»​. Он расположен первым​ оба значения. Нужно​

​ Как правило, каждый​ памяти компьютера создаётся​ с найденным значением.​ качестве значений введены​​ другими функциями, чтобы​​ номер месяца​(номер_строки) не указан,​MATCH​​ (если в формуле​​ ИНДЕКС() как формулу​ INDEX(), возвращает значение​

​ в третьей таблице,​ платы в поле​. Нужно сделать так,​

​вообще можно оставить​ и по умолчанию​

ПОИСКПОЗ() + ИНДЕКС()

​ также учесть, что​ раз, когда необходимо​ другой массив значений​ Готовый результат должен​ конкретные значения, то​ возвратить список месяцев,​4​ то требуется указать​

​(ПОИСКПОЗ) – для​ выше вместо 4​ массива.​ из диапазона ячеек​ в которой содержится​​«Сумма»​

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

Ссылочная форма

​ информация о заработной​.​ имени работника автоматически​ у нас одномерный​

​ остается просто нажать​ и столбца понимается​​ Excel, моментально на​​ что и размеры​Небольшое упражнение, которое показывает,​ из этих значений​ порядке. Функция​ сумма продаж за​(номер_столбца).​ угадавшего ближайшее значение.​

​ будет подсчитана сумма​

​А6:А9.​ и столбца. Например,​ плате за третий​Теперь посмотрим, как с​ отображалась сумма заработанных​ диапазон, в котором​ на кнопку​ не номер на​

​ ум приходит пара​ нашей исходной таблицы),​ как в Excelе​ в зависимости от​COUNTIF​ апрель (Apr).​Если аргумент​Итак, давайте обратимся к​ первых 5-и значений). ​

​Выведем 3 первых​ формула =ИНДЕКС(A9:A12;2) вернет​

​ месяц.​

​ помощью оператора​ им денег. Посмотрим,​

​ используется только один​«OK»​ координатах листа, а​

​ функций: ИНДЕКС и​ содержащая номера столбцов​ можно добиться одинакового​ индекса, а если​(СЧЁТЕСЛИ) подсчитывает, как​

​=INDEX($C$2:$C$8,F2)​column_num​ теоретическим сведениям и​Использование функции ИНДЕКС() в этом​​ значения из этого​​ значение из ячейки​После того, как все​ИНДЕКС​ как это можно​ столбец. Жмем на​.​ порядок внутри самого​ ПОИСКПОЗ. Результат поиска​ для каждой из​

excel2.ru>

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

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

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

Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.

Примечание: Функция «Мастер подстановок» больше не доступна в Excel.

Ниже приведен пример использования функции ВПР.

В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого требуется функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «Иванов» или 21 000. Второй аргумент — это диапазон ячеек (C2-: E7, в котором нужно найти искомое значение. Третьим аргументом является столбец в диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент необязателен. Введите значение истина или ложь. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение ложь, функция будет соответствовать значению, представленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым — или ввести значение истина, вы получаете более гибкие возможности.

В этом примере показано, как работает функция. Когда вы вводите значение в ячейке B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приближенное соответствие из третьего столбца в диапазоне, столбец E (Третий аргумент).

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

Если вы хорошо знакомы с функцией ВПР, функция ГПР очень проста в использовании. При вводе одних и тех же аргументов выполняется поиск в строках, а не в столбцах.

Использование функций индекс и MATCH вместо функции ВПР

Существуют определенные ограничения с использованием функции ВПР — функция ВПР может искать значение слева направо. Это означает, что столбец с искомым значением всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Если электронная таблица не создана таким образом, не используйте функцию ВПР. Вместо этого используйте сочетание функций индекс и ПОИСКПОЗ.

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования функции индекс и MATCH вместо функции ВПР можно найти в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Джилена, Microsoft MVP.

Попробуйте попрактиковаться

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

Пример функции ВПР на работе

Скопируйте указанные ниже данные в пустой лист.

Совет: Перед вставкой данных в Excel задайте ширину столбцов для столбцов A — 250 пикселей и нажмите кнопку Перенос текста (вкладка Главная , группа Выравнивание ).

Зачем использовать функции INDEX и MATCH в Google Таблицах?

Как вы уже могли заметить, функции MATCH и INDEX на самом деле не представляют особой ценности, как они есть. Но вот изюминка. Когда вы объединяете функции INDEX и MATCH в Google Таблицах вместе, они могут делать очень полезные вещи!

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

Рассмотрим следующий набор данных, в котором у вас есть список идентификаторов сотрудников, имен, их отделов и ежемесячной заработной платы:

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

Здесь может пригодиться команда INDEX-MATCH. С помощью этой пары функций вы можете получить доступ к отделу или зарплате любого сотрудника по его именам или к имени любого сотрудника по его идентификатору. 

В следующих разделах мы обсудим, как именно этого добиться.

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel – ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.

Как посчитать степень в Excel

Если нужно в экселе возвести в степень какое-нибудь число то можно воспользоваться формулой. =A^B В этой формуле буква A обозначает число, а буква B обозначает степень.

Можно поставить степень в экселе в формулу

Например чтобы посчитать 52 нужно числа подставить в формулу и получится =5^2. Чтобы увидеть ответ нужно нажать на клавиатуре клавишу Enter.

Отобразится вместо формулы результат в эксель степень числа

Также можно посчитать степень в эксель с помощью функций, а для этого нужно перейти на вкладку Формулы. Наводим курсор на ячейку в которой должен отобразится ответ и нажимаем левую кнопку мыши. Затем нажимаем на Fx которая находится на панели инструментов Excel. Откроется окно под названием Мастер функций.

Чтобы в экселе сделать степень открываем Мастер функций

В этом окне нужно в окошко под названием Поиск функций вписать слово степень и нажать кнопку Поиск. В нижнем окошке отобразится функция степень которую нужно выбрать нажав на неё и нажать кнопку ОК. Откроется окошко под названием Аргументы функции.

Чтобы получить результат нужно написать в экселе степень и число

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

Если в верхнем индексе используются только цифры” 1″,” 2 “и” 3″, их можно изменить на следующие символы:

• символ 1: на клавиатуре: alt + 0185 / US-Международная клавиатура: altgr+1
• символ 2: на клавиатуре: alt + 0178 / US-Международная клавиатура: altgr+2

символ 3: на клавиатуре: alt + 0179 / US-Международная клавиатура: altgr+3

1.1. Примечание: В Alt+#### и подобные комбо, вы должны удерживайте клавишу Alt, а затем нажмите цифры на цифровой клавиатуре номер клавиши (не те, что над буквами), Т. е. по Alt+0185 = удерживайте клавишу Alt, нажмите NumPad 0, Нажмите NumPad 1, Нажмите NumPad 8, нажмите NumPad 5, отпустите клавишу Alt

1.2. Примечание. Я не уверен в названии “US-International Keyboard”, но во многих клавиатурах, которые я использовал (у одного было похожее имя), это комбо (altgr+1, 2 или 3) обработанный.

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

Если произвести поиск по функциям подстановки, Google покажет, что ВПР намного популярнее функции ИНДЕКС. Оно и понятно, ведь чтобы придать функции ИНДЕКС тот же функционал, что и ВПР, необходимо воспользоваться еще одной формулой – ПОИСКПОЗ. Что касается меня, было всегда непросто попробовать и освоить две новые функции одновременно. Но они дают больше возможностей и гибкости в создании электронных таблиц. Но обо всем по порядку.

Функция ВПР()

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; ). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

Чтобы решить нашу проблему в один шаг, необходимо воспользоваться формулами ИНДЕКС и ПОИСКПОЗ. Сложность данного подхода заключается в том, что требуется применить две функции, которые, возможно, вы никогда не применяли до этого. Для упрощения понимания решим эту задачу в два этапа.

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; ).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; ).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

Ну, в принципе, все. Функция ПОИСКПОЗ указывает функции ИНДЕКС, в какой строке искать значение. Замените знак вопроса в формуле ИНДЕКС формулой ПОИСКОПОЗ и вы получите эквивалент ВПР с возможностью поиска данных, находящихся левее ключевого столбца. Получиться так:

В начале было непривычно, был огромный соблазн вставить еще одну колону и работать, как всегда работал. Но со временем использование функции ИНДЕКС вошло в привычку. Оказалось, что это быстрее и требует меньших манипуляций. Так что в следующий раз, когда у вас возникнет желание задать номеру столбца отрицательное число в ВПР, воспользуйтесь сочетанием двух странных функций ИНДЕКС и ПОИСКПОЗ, чтобы решить свою проблему.

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

Если команда подстановка недоступна, необходимо загрузить мастер подстановок надстройка программу.

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

Постановка задачи

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

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

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

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

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

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

1. Выделите пустую зеленую ячейку, где должен быть результат.
2. Введите в строке формул в нее следующую формулу:

Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).