Excel сумм индекс поискпоз

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

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

Рассмотрим список с повторяющимися значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .

Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива

можно найти все эти позиции. Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER . В позициях, в которых есть значение Груши будет выведено соответствующее значение позиции, в остальных ячейках быдет выведен 0.

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

Подсчет количества рабочих дней в Excel по условию начальной даты

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

Вид таблицы данных:

Как видно на рисунке в ячейке A10 снова используется выпадающий список, созданный по аналогичной схеме, описанной выше.

Для определения искомого значения даты используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

Первая функция ИНДЕКС выполняет поиск ячейки с датой из диапазона A1:I1. Номер строки указан как 1 для упрощения итоговой формулы. Функция СТОЛБЕЦ возвращает номер столбца с ячейкой, в которой хранится первая запись о часах работы. Выражение «ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)«»» выполняет поиск первой непустой ячейки для выбранной фамилии работника, указанной в ячейке A10 (”” – не равно пустой ячейке). Второй аргумент «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер строки с выбранной фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)«»» — номер позиции значения ИСТИНА в массиве (соответствует номеру столбца), полученном в результате операции сравнения с пустым значением.

Примеры определения дат для нескольких сотрудников:

Для автоматического подсчета количества только рабочих дней начиная от даты приема сотрудника на работу, будем использовать функцию ЧИСТРАБДНИ:

Для проверки выберем другую фамилию сотрудника из выпадающего списка в ячейке A9:

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

Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:

1. Сколько очков заработала команда (поиск по названию) на данный момент.
2. Суммарное значение очков, заработанных всеми командами.
3. Сколько игр было сыграно какой-либо командой.

Вид исходной таблицы данных:

Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:

В открывшемся диалоговом окне необходимо выбрать «Тип данных:» — «Список» и указать в поле «Источник» диапазон ячеек:

Такой же выпадающий список следует создать и для ячейки A15.

Для подсчета общего количества очков в ячейке B11 используем формулу:

Для получения корректного результата выражение должно быть выполнено как формула массива. Функция СУММ получает массив ячеек в виде столбца таблицы, номер которого был определен функцией ПОИСКПОЗ по критерию поиска «Очки» (наименование столбца). Поскольку в качестве аргумента номер_строки функции ИНДЕКС было передано значение 0, будет возвращен весь столбец.

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

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

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

В результате мы получили значение по 2-м критериям:

IT IS MY LIFE…

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

Параметры поиска

Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:

1. Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
2. Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
3. Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П???», отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
4. Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

Также вы можете зайти в настройки:

1. В окне «Найти» нажмите «Параметры».
2. В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
3. Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
4. Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:

1. В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
2. Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
3. Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск нескольких слов

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

Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:

1. Напишите их в строке поиска.
2. Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
3. Этим способом можно задать ключ даже с отдельными буквами.

ИНДЕКС ПОИСКПОЗ с несколькими условиями в нескольких строках и столбцах

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

Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями в строках и столбцах:

где:

Массив таблицы — область для поиска, т. е. все значения таблицы, кроме заголовков столбцов и строк.

Значение_поиска — то, что вы ищете по вертикали в столбце.

Столбец_поиска — диапазон столбцов для поиска, обычно это заголовки строк.

Значение_поиска1, значение_поиска2, … — то, что вы ищете по горизонтали в строках.

Строка_поиска1, строка_поиска2, … — диапазоны строк для поиска, обычно это заголовки столбцов.

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

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

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

• Массив_таблицы — B3:E4
• Значение_поиска  — H1
• Столбец_поиска  (заголовки строк: товары) — A3:A5
• Значение_поиска1  (целевой регион) — H2
• Значение_поиска 2 (целевой продавец) — H3
• Строка_поиска1 (заголовки столбцов 1: регионы) — B1:E1
• Строка_поиска2 (заголовки столбцов 2: продавцы) — B2:E2

А теперь подставьте аргументы в общую формулу, описанную выше, и вы получите такой результат:

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

Пошагово рассмотрим, как работает эта формула.

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

Шаг 1.

Номер_строки предоставляется функцией ПОИСКПОЗ(H1;A3:A5;0), которая сравнивает целевой элемент (бананы) в H1 с заголовками строк в A3:A5. 

ПОИСКПОЗ(«Бананы»;{«Апельсины»:»Бананы»:»Лимоны»};0)

Это дает результат 2, потому что «Бананы» — это второй элемент в указанном списке.

Шаг 2.

Номер_столбца вычисляется путем объединения двух значений поиска и двух массивов поиска: ПОИСКПОЗ(H2&H3; B1:E1&B2:E2; 0)

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

ПОИСКПОЗ(«СеверПродавец 2»; {«СеверПродавец 1″;»СеверПродавец 2″;»ЮгПродавец 1″;»ЮгПродавец 2»}; 0)

Поскольку « СеверПродавец 2» является вторым элементом в массиве, функция возвращает 2.

Шаг 3.

После этого наша длинная двумерная формула ИНДЕКС ПОИСКПОЗ превращается в такую простую:

=ИНДЕКС(B3:E5; 2; 2)

Шаг 4.

Она возвращает значение на пересечении 2-й строки и 2-го столбца в диапазоне B3:E5, что является значением в ячейке C4.

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

Еще несколько статей по теме:

Функция НЕ

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

Функция NOT меняет значение своего аргумента. Так что, если логическое значение ИСТИНА, тогда оно возвращает ЛОЖЬ. И если логическое значение ЛОЖЬ, оно вернет ИСТИНА.

Это будет легче объяснить на некоторых примерах.

Структура функции НЕ имеет вид;

  = НЕ (логическое) 

НЕ Функциональный Пример 1

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

Функция NOT была вложена в логический тест функции IF ниже, чтобы сторнировать ИСТИННЫЙ результат.

  = ЕСЛИ (НЕ (B2 = "London"), "Да", "Нет") 

Это также может быть достигнуто с помощью логического оператора NOT <>. Ниже приведен пример.

  = ЕСЛИ (В2 <> "Лондон", "Да", "Нет") 

НЕ Функциональный Пример 2

Функция NOT полезна при работе с информационными функциями в Excel. Это группа функций в Excel, которые что-то проверяют и возвращают TRUE, если проверка прошла успешно, и FALSE, если это не так.

Например, функция ISTEXT проверит, содержит ли ячейка текст, и вернет TRUE, если она есть, и FALSE, если нет. Функция NOT полезна, потому что она может отменить результат этих функций.

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

Функция ISTEXT используется для проверки наличия текста. Это возвращает TRUE, если текст есть, поэтому функция NOT переворачивает это на FALSE. И если ИФ выполняет свой расчет.

  = ЕСЛИ (НЕ (ISTEXT (В2)), В2 * 5%, 0) 

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

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP(“lookup value”,A1:C10,2)=ВПР(“lookup value”;A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

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

=VLOOKUP(A2,B5:D10,3,FALSE)=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.

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

Как найти значение в массиве

Для понимания темы алгоритм выполнения поставленной задачи необходимо рассмотреть на конкретном примере. Составим в Excel таблицу заказов за один день, в которой будут столбцы: «№ заказа», «Клиент», «Товар», «Количество», «Цена за ед.», «Сумма». Необходимо найти значение в массиве, т.е. составить индивидуальную карточку заказа клиента, чтобы можно было в сжатом виде получить информацию из ячеек исходной таблицы.

Внешний вид составленной таблички

Для этого надо выполнить ряд действий по алгоритму:

1. Составить карточку заказа клиента.

Карточка заказа клиента

2. Для первой строчки карточки надо создать выпадающий список, в котором будут прописаны имена клиентов из основного массива. Впоследствии, выбрав то или иное имя, пользователь увидит сжатую информацию по нему, которая отобразится в других строках карточки заказа.
3. Поставить курсор мышки в первую строку карточки и войти в раздел «Данные» сверху главного меню программы.
4. Кликнуть по кнопке «Проверка данных».
5. В отобразившемся окошке в поле «Тип данных» указать вариант «Список», а в качестве источника выделить диапазон ячеек исходного массива, в котором прописан перечень всех клиентов.

Необходимые действия в окошке «Проверка вводимых значений». Здесь выбираем вариант «Список» и указываемы диапазон всех клиентов

6. В правой части ячейки первого столбца карточки появится стрелочка. Если кликнуть по ней, то можно увидеть список всех клиентов. Здесь надо выбрать любого клиента.

Перечень клиентов, который появится в первой строке карточки после выполнения предыдущих манипуляций

7. В строке «№ заказа» прописать функцию «=ИНДЕКС(», после чего кликнуть по значку «fx» рядом со строкой формул Эксель.
8. В открывшемся меню Мастера функций из списка выбрать форму массива для функции «ИНДЕКС» и нажать на «ОК».

Выбор формы массива для функции «ИНДЕКС»

9. Откроется окно «Аргументы функции», в котором надо заполнить все строки, указывая соответствующие диапазоны ячеек.

Внешний вид окошка «Аргументы функции»

10. Сначала надо кликнуть по пиктограмме напротив поля «Массив» и выделить целиком исходную табличку вместе с шапкой.

Заполнение строчки «Массив». Здесь надо нажать по пиктограмме в конце поля и выделить изначальную табличку

11. В поле «Номер строки» нужно заполнить функцию «ПОИСКПОЗ». На первом месте в скобке в качестве аргумента указываем название клиента, который был выбран в карточке заказа. В качестве второго аргумента функции «ПОИСКПОЗ» нужно указать весь диапазон клиентов в исходном табличном массиве. На месте третьего аргумента необходимо прописать число 0, т.к. будет искаться точное совпадение.

Заполнение поля Номер строки в меню аргументов функции. Здесь применяется оператор «ПОИСКПОЗ»

12. В строчке «Номер столбца» еще раз прописать вспомогательную функцию «ПОИСКПОЗ» с соответствующими аргументами.
13. В качестве первого аргумента для функции надо указать незаполненную ячейку в строке «Товар» в карточке заказа. При этом навешивать знаки долларов на аргументы теперь не надо, т.к. искомый аргумент должен получиться «плавающим».
14. Заполняя второй аргумент функции «ПОИСКПОЗ», нужно выделить шапку исходного массива, а затем нажать на кнопку «F4» для закрепления символов.
15. В качестве последнего аргумента необходимо прописать 0, закрыть скобку и щелкнуть по «ОК» внизу окошка «Аргументы функции». В данной ситуации число 0 является точным совпадением.

Заполнение поля «Номер столбца». Здесь еще раз надо указать все аргументы для функции «ПОИСКПОЗ», выделяя соответствующие диапазоны ячеек табличного массива. В качестве последнего аргумента указывается 0

16. Проверить результат. После проделывания таких длительных действий в строке «№ заказа» должно отобразиться число, соответствующее выбранному клиенту.

Финальный результат. В поле«№ заказа» появилось соответствующее значение из исходного табличного массива

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

Растягивание формулы на все строки таблицы. Полностью заполненный массив. Данные будут сменяться при выборе другого клиента

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

​ двумя полезными функциями​ месяц и тип​ИНДЕКС​ которой соответствует искомому​ чисел ближайшее к​-1​в качестве значения​ может быть использован​ таком случае напишем​

​Просматриваемый массив. Т.к. мы​ будет видеть, что​Результат​ таковой другими функциями.​ ИНДЕКС используется область 1. ​ скобки . Если​Данные​ опущен, аргумент «номер_столбца»​ Microsoft Excel –​ товара, получить объем​возвращает содержимое ячейки,​ значению. Т.е. данная​ правильному.​, результатом будет наименьшее​ аргумента​ другими функциями, такими​

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

​ два нуля.​ ищем по артикулу,​ это за товар,​=ИНДЕКС(A2:C6;2;3)​ В зависимости от​ Все указанные здесь​ вы попытаетесь ввести​

​Яблоки​ является обязательным.​ПОИСКПОЗ​ продаж.​ которая находится на​ функция возвращает не​Функция​ значение, которое больше​match_type​ как​Скачать примеры использования функций​ значит, выделяем столбец​

​ какой клиент его​Пересечение второй строки и​ формулы значение, возвращаемое​ области должны находиться​ их вручную, Excel​Лимоны​Номер_столбца​и​Пускай ячейка C15 содержит​ пересечении заданных строки​ само содержимое, а​ABS​ искомого или эквивалентное​(тип_сопоставления), чтобы выполнить​INDEX​ ИНДЕКС и ПОИСКПОЗ​ артикулов вместе с​ приобрел, сколько было​ третьего столбца в​ функцией ИНДЕКС, может​ на одном листе. ​ отобразит формулу как​

​Бананы​ — необязательный аргумент.​ИНДЕКС​ указанный нами месяц,​ и столбца. Например,​ его местоположение в​возвращает модуль разницы​ ему. В нашем​

​ поиск точного совпадения.​(ИНДЕКС) или​Получили простейшую формулу, помогающую​ шапкой. Фиксируем F4.​ куплено и по​ диапазоне A2:C6, т. е.​

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

​ вывести максимальное значение​Тип сопоставления. Excel предлагает​

​ какой общей стоимости.​ содержимое ячейки C3.​ или как значение.​ находящиеся на разных​ вводе формулы не​Формула​

​ массиве, из которого​ простых примерах, а​Май​ формула возвращает значение​Например, на рисунке ниже​

​ и правильным числами.​ равно 54. Поскольку​ точное совпадение текстовой​(ВПР). Например:​ из массива. Протянем​ три типа сопоставления:​ Сделать это поможет​38​ Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2))​ листах, произойдет ошибка​ нажать клавиши CTRL+SHIFT+ВВОД,​

​Описание​ требуется возвратить значение.​ также посмотрели их​. А ячейка C16​ из диапазона A1:C4,​ формула вернет число​Функция​ такого значения нет​ строки, то в​Найти положение элемента в​ ее вправо, получив​ больше, меньше и​

​ функция ИНДЕКС совместно​=ИНДЕКС((A1:C6;A8:C11);2;2;2)​ эквивалентна формуле ЯЧЕЙКА(«ширина»;B1).​ #ЗНАЧ! Если необходимо​ она возвратит ошибку​Результат​ Если аргумент «номер_столбца»​ совместное использование. Надеюсь,​ — тип товара,​ которое находится на​5​MIN​ в списке баллов,​

​ искомом значении допускается​

Поиск индекса максимального числа массива в Excel

​ несортированном списке.​ аналогичную информацию по​ точное совпадение. У​ с ПОИСКПОЗ.​Пересечение второй строки и​ Функция ЯЧЕЙКА использует​ использовать диапазоны, находящиеся​ #ЗНАЧ!​

​=ИНДЕКС(A2:B3;2;2)​ опущен, аргумент «номер_строки»​ что данный урок​ например,​

​ пересечении 3 строки​, поскольку имя «Дарья»​(МИН) находит наименьшую​ то возвращается элемент,​ использовать символы подстановки.​Использовать вместе с​ цене и сумме.​

​ нас конкретный артикул,​Для начала создадим выпадающий​ второго столбца во​ значение, возвращаемое функцией​ на разных листах,​К началу страницы​Значение ячейки на пересечении​

​ является обязательным.​ Вам пригодился. Оставайтесь​

​Овощи​ и 2 столбца.​ находится в пятой​ из разниц.​ соответствующий значению 60.​В следующем примере, чтобы​

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

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

. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

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

Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте — это сочетание с функцией СЧЁТЗ (COUNTA) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

Использование в расчетах вложенных функций

• В ячейку I2 ввести название новой графы «Наличие скидки».
• Открыть «Прайс-лист».
• В ячейку А12 ввести с клавиатуры «Скидка», в ячейку В12 ввести 5%.
• Выделить блок ячеек А12:В12 и выбрать пункт меню Вставка|Имя|Создать.
• В открывшемся окне Создать имена установить флажок В столбце слева и нажать ОК.
• Снять выделение с блока ячеек.
• Перейти на лист «Отчет».
• Установить курсор в ячейку I3 и щелкнуть по пиктограмме Вставка функции.
• В появившемся окне Мастер функций выбрать категорию функций Логические.
• В списке функций выбрать ЕСЛИ.
• Нажать ОК.
• Подвинуть появившееся на экране окно за пределы таблицы, чтобы не мешать указателю мыши работать с ячейками таблицы.
• Установить курсор в окно Логическое выражение и щелкнуть по кнопке со стрелкой списка функций, которая располагается в строке формул на месте поля имен (см.рис.17).

• В открывшемся списке выбрать Другие функции.
• В появившемся окне Мастер функций выбрать категорию функций Логические.
• В списке функций выбрать И.
• Нажать ОК. Открылось следующее окно (рис.18):

• В появившемся окне Аргументы функции (функции ЕСЛИ) перевести курсор в поле Значение_если_истина.
• Нажать функциональную клавишу F3 на клавиатуре и в открывшемся окне выбрать имя блока «Скидка». Нажать ОК.
• Перевести курсор в окно Значение_если ложь и нажать клавишу Пробел на клавиатуре, что будет означать отсутствие скидки (рис.21).

=ЕСЛИ(И(F3>500;E3=»Нал»);Скидка;» «)