5 вариантов использования функции ИНДЕКС (INDEX). Функция в excel index
Функция ИНДЕКС в Excel | Понятный Excel
Здравствуйте друзья!
В этой статье я возвращаюсь к такой отрасли функций как массивы и в статье рассмотрим еще одну функцию, которая поможет нам работать с большими объемами данных, это функция ИНДЕКС в Excel. Кстати многие ее незаслуженно обходят своим вниманием, сам такой был, каюсь, но всё же хочу развеять эту дремучую тьму и принести немного света и понимания при работе с этой функцией.
Функция ИНДЕКС очень быстра, поразительно проворна, да и еще стоить отметить, что она обладает большой гибкостью. Эта функция может вернуть не только одно, нужное вам, значение, но и целый массив, а в некоторых случаях можно получить не только ссылку на конкретную ячейку, но и на целый диапазон.
Хотя наибольшую эффективность функция ИНДЕКС в Excel проявляет в тандеме с другими функциями, такими как функция ПОИСКПОЗ (написана отдельная статья с примерами, рекомендую к прочтению), ЕСЛИ, СУММПРОИЗВ и прочее. Эта функция в тандеме очень хорошая альтернатива функции ВПР (детально о функции в статье), она в некоторых моментах может то, что ей не доступно, например, поиск с левой стороны. Да в принципе можно много достоинств описывать, но всё же лучше приступить к практике, и как всегда начнём с синтаксиса функции ИНДЕКС.
Синтаксис, который имеет функция ИНДЕКС в Excel, следующий:
= ИНДЕКС(массив, номер строки, [номер столбика]), где
- массив – это обязательный аргумент, который содержит в себе константу на массив или диапазон ячеек;
- номер строки – является обязательным аргументом, который указывает, из какого номера строки нам нужно вернуть результаты;
- номер столбика – этот аргумент не является обязательным при условии, когда в аргументе массив указан один конкретный столбик, но становится обязательным, когда в массиве, два и более столбика, тогда нужно указать столбик, из которого нужно будет вернуть значение.
Рассмотрим на примере механизм работы функция ИНДЕКС в Excel. В простом исполнении эта функция особенно не блещет и имеет вполне посредственное применение, оно всего лишь возвращает с массива указанное значение:
=ИНДЕКС(C1:G12;6; 2)
вернет значение ячейки B4, то есть, значение с четвертой строки во втором столбике; 
В более сложном исполнении функции ИНДЕКС мы вернем значение целого массива:
{=ИНДЕКС(C2:G12; ; 2)}
данная интерпретация формулы вернет вам ссылку на диапазон B1:B5, которую, в дальнейшем вы сможете использовать в вычислениях. Как видите, формула заключена в фигурные скобки, а это означает вводиться формула должна как формула массива, не просто кнопка Enter, а горячей комбинацией клавиш CTRL + Shift + Enter, которая введет значение как массив и сама поставит фигурные скобки, просто так, вручную это сделать невозможно! 
Наиболее ярко функция ИНДЕКС в Excel ведет себя совместно с другими функциями, такой тандем наиболее популярный и эффективный. В соединении с функцией ПОИСКПОЗ, которая будет определять, и передавать номера строк и столбцов (детально в статье «Как используется функция ПОИСКПОЗ в Excel») мы получим достойную замену, даже, функции ВПР, так как:
- во-первых, функция ИНДЕКС более скоростная, нежели ВПР и чем больше нужно найти и извлечь данных, тем более заметна скорость работы;
- во-вторых, главная отличительная черта функции ИНДЕКС, то что она может искать необходимые значения слева от заданного исходного столбика, а вот ВПР этого лишена.
Я не буду повторяться с примерами по функции ПОИСКПОЗ, так как, я детально и в разнообразных примерах рассмотрел эту функции в своей статье, и вам рекомендую пройти по ссылке и ознакомится с предоставленными материалами.
На этом я буду заканчивать эту статью, очень надеюсь, что эта великолепная функция ИНДЕКС с большими возможностями, позволит вам решить ваши задачи. Очень надеюсь, что я написал понятно и доступно и вы в полной мере смогли понять и овладеть работой с функцией, если есть замечания или предположения, пишите комментарии! Если статья понравилась, жду ваших лайков! Встреча с другими функциями вам доступна в «Справочнике функций».
До новых встреч на страницах сайта!
"Они нуждаются, обладая богатством, — а это самый тяжелый вид нищеты."Л.А. Сенека
Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!
topexcel.ru
Функция Индекс в Excel | SirExcel
В этой статье мы будем рассматривать функцию ИНДЕКС(). Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или поименованного диапазона, заданного номером строки и номером столбца.
Синтаксис функции ИНДЕКС довольно прост:ИНДЕКС(массив, номер_строки, [номер_столбца])
Массив — обязательный аргумент, диапазон ячеек или константа массива.
Если массив содержит только одну строку или один столбец, аргумент «номер_строки» или «номер_столбца» соответственно не является обязательным. Если массив занимает больше одной строки и одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».
Номер_строки — обязательный аргумент, номер строки в таблице, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.Номер_столбца — необязательный аргумент, номер столбца в таблице, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.
Смотрите так же нашу статью о том, как использовать функцию ИНДЕКС и ПОИСКПОЗ в Excel с примером.
Напомним, что формулы массива заключаются в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ВВОД.) в горизонтальный диапазон ячеек для строки и вертикальный для столбца. Чтобы ввести формулу массива, нажмите клавиши CTRL + SHIFT + ВВОД.
Аргументы «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива или тблицы, в противном случае функция ИНДЕКС возвратит значение ошибки #ССЫЛ!.
Рассмотрим пример использования функции Индекс, допустим у нас есть какая-либо таблица данных:
Данные
Мы хотим при помощи функции Индекс вытащить значение «u» из таблицы. Значение «u» находится на пересечении Строки 2 и Столбца 3 (т.к. нумерация начинается с 0), т.о. формула имеет вид =ИНДЕКС(B2:E5;2;3):
Использование функции Индекс
sirexcel.ru
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), чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.
Ссылки по теме
СохранитьСохранитьwww.planetaexcel.ru
Функция ИНДЕКС в Excel | TutorExcel.Ru
Рассмотрим пример использования функции ИНДЕКС в Excel, которая возвращает значение или ссылку на значение из диапазона, заданного номерами строки и столбца.
Описание функции ИНДЕКС
Функция ИНДЕКС имеет 2 формы записи: форму массива и ссылочную. Давайте разберем в чем особенности каждой из них.
Форма массива
ИНДЕКС(массив; номер_строки; [номер_столбца])Возвращает значение элемента таблицы или массива на пересечении конкретных строки и столбца, в данном диапазоне.
- Массив (обязательный аргумент) — диапазон ячеек;
- Номер строки (обязательный аргумент) — выбирает строку в массиве из которой будет возвращаться значение;
- Номер столбца (необязательный аргумент) — выбирает столбец в массиве из которой будет возвращаться значение.
Ссылочная форма
ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области])Возвращает ссылку на ячейку на пересечении конкретных строки и столбца, в данном диапазоне.
- Ссылка (обязательный аргумент) — ссылка на один или несколько диапазонов ячеек;
- Номер строки (обязательный аргумент) — выбирает строку в указанном аргументе «ссылка» из которой будет возвращаться ссылка;
- Номер столбца (необязательный аргумент) — выбирает столбец в указанном аргументе «ссылка» из которой будет возвращаться ссылка;
- Номер области (необязательный аргумент) — номер диапазона в аргументе «ссылка».
Пример использования функции ИНДЕКС
Предположим у нас есть таблица данных с продуктами:
Например, мы хотим получить значение из таблицы на пересечении строки №2 и столбца №3 (критерий 1 в нашем примере).В качестве массива выбираем диапазон ячеек A2:C5, задаем номер строки как ячейку F3 (строка №2) и номер столбца как ячейку F4 (столбец №3), в качестве результата получаем «Груши»:
Пример №1 использования функции ИНДЕКС
Также представим, что наша таблица состоит из областей и мы хотим получить значение из таблицы на пересечении строки №2 и столбца №1 из определенной области (критерий 2 в нашем примере).В качестве ссылки выбираем 2 диапазона ячеек A2:C3 и A4:C5, задаем номер строки как ячейку F10 (строка №2), номер столбца как ячейку F11 (столбец №1) и номер области как ячейку F12 (область №1, т.е. диапазон A2:C3),в качестве результата получаем «Яблоки»:
Пример №2 использования функции ИНДЕКС
Особенности функции ИНДЕКС
В качестве результата функции ИНДЕКС в Excel можно получить ссылку:
- Если параметр номер_строки или номер_столбца равен 0 или отсутствует, то, например, результатом выполнения функции "=ИНДЕКС(A1:C6;0;2)" будет диапазон B2:B6, т.е. столбец №2 в диапазоне A1:C6.
- Результат функции ИНДЕКС можно использовать в качестве ссылкы в других функциях. Например, результатов выполнения функции "=СУММ(C3:ИНДЕКС(A1:C6;6;3))" будет сумма значений из диапазона C3:C6, т.е. строка №6 и столбец №3 в диапазоне A1:C6 будет ячейка С6.
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
Поделиться с друзьями:
Поиск по сайту:
tutorexcel.ru
Функции ИНДЕКС и ПОИСКПОЗ в Excel и примеры их использования
В Excel есть очень удобная, но почему-то редко используемая функция, которая называется ИНДЕКС. Удобная она потому, что позволяет выдавать значение из диапазона по заданным номерам строки и столбца.
На практике ИНДЕКС редко используется, скорее всего, из-за того, что эти самые номер строки и столбца приходится вводить каждый раз. Ведь искомое значение не всегда нужно выдавать по порядку. Но тогда к функции ИНДЕКС на помощь приходит функция ПОИСКПОЗ, которая как раз таки позволяет найти нужную позицию.
Пример использования функций ИНДЕКС и ПОИСКПОЗ
Рассмотрим интересный пример, который позволит понять прелесть функции ИНДЕКС и неоценимую помощь ПОИСКПОЗ. Имеем сводную таблицу, в которой ведется учет купленной продукции.
Наша цель: создать карточку заказа, где по номеру артикула можно будет видеть, что это за товар, какой клиент его приобрел, сколько было куплено и по какой общей стоимости. Сделать это поможет функция ИНДЕКС совместно с ПОИСКПОЗ.
Для начала создадим выпадающий список для поля АРТИКУЛ ТОВАРА, чтобы не вводить цифры с клавиатуры, а выбирать их. Для этого кликаем в соответствующую ячейку (у нас это F13), затем выбираем вкладку ДАННЫЕ – ПРОВЕРКА ДАННЫХ. В открывшемся окне в пункте ТИП ДАННЫХ выбираем СПИСОК. А в качестве источника выделяем столбец с артикулами, включая шапку. Так у нас получился выпадающий список артикулов, которые мы можем выбирать.
Теперь нужно сделать так, чтобы при выборе артикула автоматически выдавались значения в остальных четырех строках. Воспользуемся функцией ИНДЕКС. Записываем ее и параллельно изучаем синтаксис.
Массив. В данном случае это вся таблица заказов. Выделяем ее вместе с шапкой и фиксируем клавишей F4.
Номер строки. Если бы у нас требовалось вывести одно значение, мы бы написали какую-то конкретную цифру. Но раз нам нужно, чтобы результат менялся, воспользуемся функцией ПОИСКПОЗ. Она будет искать необходимую позицию каждый раз, когда мы будем менять артикул.
Записываем команду ПОИСКПОЗ и проставляем ее аргументы.
Искомое значение. В нашем случае это ячейка, в которой указывается артикул, т.е. F13. Фиксируем ее клавишей F4.
Просматриваемый массив. Т.к. мы ищем по артикулу, значит, выделяем столбец артикулов вместе с шапкой. Фиксируем F4.
Тип сопоставления. Excel предлагает три типа сопоставления: больше, меньше и точное совпадение. У нас конкретный артикул, поэтому выбираем точное совпадение. В программе оно значится как 0 (ноль). На этом аргументы ПОИСКПОЗ закончились.
Номер столбца. Опять же воспользуемся ПОИСКПОЗ. Искомым значением будет ячейка E14, где указано наименование параметра, который мы ищем (ТОВАР). Просматриваемый массив: шапка с наименованиями, потому что искать система будет по слову ТОВАР. Тип сопоставления: 0.
Синтаксис функции ИНДЕКС закончен. Как в итоге выглядит формула, видно на скриншоте выше. Видим, что артикул 3516 действительно у арахиса. Протянем формулу на остальные строки и проверим. Теперь, меняя артикул товара, мы будем видеть, кто его купил, сколько и почем.
Поиск индекса максимального числа массива в Excel
Функция ИНДЕКС также помогает выделить из массива максимальное число. Рассмотрим тот же самый пример. Попробуем определить максимальные значения купленного количества товара, цены и суммы.
Начнем с количества. В любой ячейке под этим столбцом пишем =ИНДЕКС.
Первым аргументом у нас будет не просто массив, а максимальное число из массива. Поэтому дополнительно используем команду МАКС и выделяем соответствующий массив.
В принципе, нам больше не нужны никакие аргументы, но требуется ввести номер строки и столбца. В таком случае напишем два нуля.
Скачать примеры использования функций ИНДЕКС и ПОИСКПОЗ
Получили простейшую формулу, помогающую вывести максимальное значение из массива. Протянем ее вправо, получив аналогичную информацию по цене и сумме.
exceltable.com
Поле со списком и функция ИНДЕКС в Excel
Опубликовано 13 Июл 2013Рубрика: Справочник Excel | 3 комментария
В статье «Расчет передачи винт-гайка» я обещал рассказать, как работает функция ИНДЕКС в Excel. Конечно, все желающие могут разобраться в этом самостоятельно, изучив формулы в вышеназванной статье и почитав об этой функции в Справке Excel и в Сети. Эта небольшая статья...
...написана для тех, кто не разобрался или не пожелал разбираться самостоятельно.
При создании расчетных прикладных программ в MS Excel и OOo Calc существует задача оптимизации и автоматизации ввода данных из справочников. Выбор и ввод информации из справочников позволяет минимизировать ошибки пользователя, возникающие при наборе данных на клавиатуре, существенно ускоряет работу, делает ее более комфортной, приятной и понятной. В качестве справочников очень часто используется набор одномерных и двухмерных таблиц, размещенных на одном рабочем листе с программой расчета или на других листах файла Excel.
Рассмотрим использование поля со списком и функции ИНДЕКС на примере.
Итак, есть в наличии таблица, например, перечень материалов для изготовления колеса червячной передачи с механическими характеристиками [σв] и [σт]. Необходимо из этой таблицы для выбранной пользователем марки бронзы скопировать значения предела прочности [σв] и предела текучести [σт] в ячейки Excel, где расположена расчетная программа. То есть, необходимо реализовать следующий алгоритм: пользователь программы выбирает материал для червячного колеса… – и всё!!! После выбора материала копии значений [σв] и [σт], соответствующие выбранной бронзе, копируются из таблицы в ячейки расчетного модуля программы автоматически!
На представленном снимке экрана изображен пример с результатом выполнения вышеописанного алгоритма.
Пройдем по шагам весь процесс:
1. Создаем новый файл Excel — pole-so-spiskom-i-funktsiya-indeks-v-excel.xls.
2. Размещаем на листе базу данных – таблицу с заголовком в область A1:C9.
3. Делаем соответствующие оформительские записи в расчетном блоке в ячейках A12, A13, A14, C13, C14 и вписываем заголовок в объединенные ячейки A11, B11, C11.
4. Активируем, если не активирована, панель инструментов «Формы». Для этого заходим в закладки «Вид» — «Панели инструментов» — «Формы» и ставим «галочку».
5. На панели инструментов «Формы» выбираем элемент «Поле со списком» и размещаем его над ячейками B12 и C12.
6. Делаем щелчок правой кнопкой мыши на элементе «Поле со списком» и в выпавшем контекстном меню выбираем «Формат объекта».
7. В появившемся окне «Формат элемента управления» переходим на вкладку «Элемент управления».
8. Формируем список по диапазону $A$5:$A$9.
9. Устанавливаем связь с ячейкой $A$3.
10. Изменяем количество строк списка с 8 на 5 – по количеству строк в базе.
11. Ставим галочку внизу окна – включаем объемное затенение. Так элемент выглядит симпатичнее.
12. Нажимаем на кнопку «ОК» и закрываем окно «Форматирование объекта».
13. Проверяем, как работает «Поле со списком». Для этого нажимаем на кнопку справа «Поля…» и в «выпавшем» списке выбираем, например, четвертую запись – БрО5Ц5С5 (песч. форма). После щелчка левой кнопкой мыши на выбранном элементе он появляется в окошке, а полный список «сворачивается» (исчезает).
Обращаю ваше внимание, что после сделанного нами выбора в ячейке A3 появилось число 4. Это число показывает порядковый номер выбранной записи в «Поле со списком» и появилось оно потому, что именно с этой ячейкой в шаге №9 мы установили связь.
Материал для червячного колеса мы выбрали и видим его в установленном над ячейками B12 и C12 элементе «Поле со списком». Теперь выведем значения [σв] и [σт] для выбранной бронзы в ячейки B13 и B14. Для этого запишем в эти ячейки формулы:
14. В ячейку B13: =ИНДЕКС(B5:B9;A3)=150
15. В ячейку B14: =ИНДЕКС(C5:C9;A3)=80
Теперь при выборе из выпадающего списка любого материала функция ИНДЕКС тут же выведет в ячейки B13 и B14 соответствующие этому материалу значения предела прочности [σв] и предела текучести [σт]. Эти значения могут участвовать в дальнейших расчетах, подставляться в формулы в качестве исходных данных.
Одним щелчком мыши мы заполняем сразу три строки исходных данных некой расчетной программы, а если исходная таблица будет с большим количеством строк и столбцов, то ускорение и упрощение работы пользователя становятся очень существенными.
На этом – всё о совместном использовании функции ИНДЕКС и элемента «Поле со списком» из панели инструментов «Формы» для обеспечения доступа к базе данных в виде двухмерной таблицы при написании расчетных программ.
Гуру в Excel расскажут вам, возможно, массу других «более правильных» и простых способов решения подобных задач. Но я, однажды решив эту задачу описанным выше приемом, счел для себя лишним продолжать поиски других путей решения, так как результат меня полностью удовлетворяет вот уже более десяти лет.
Все описанные действия выполнялись в Excel-2003. Для более новых версий программы действия будут похожими – думаю, разберетесь. В OOo Calc аналогом функции ИНДЕКС MS Excel является функция index.
Уважаемые читатели, для получения анонсов статей моего блога прошу оформить подписку в окне «Подпишитесь на новости», расположенном вверху страницы. Введите адрес своей электронной почты и нажмите на кнопку «Получать анонсы статей». Один раз в 7…10 дней к вам на почтовый ящик будет приходить небольшое уведомление о появлении на моем блоге новой статьи, ее название и краткое описание. Если вам что-то не понравится или просто надоест автор или тема, вы прямо в почте всегда можете отказаться от подписки.
Жду ваших комментариев!
Ссылка на скачивание файла: pole-so-spiskom-i-funktsiya-indeks-v-excel (xls 31,0KB).
Другие статьи автора блога
На главную
Статьи с близкой тематикой
Отзывы
al-vo.ru
Функция индекс (англ. index) в excel с примерами
О том как работает функция ИНДЕКС (англ. INDEX) мы уже писали в отдельной статье, но в чистом виде как правило данная функция применяется не так часто. Напомним, что функция ИНДЕКС возвращает значение на пересечении указанной строки и столбца определенного диапазона.
Давайте вспомним как работает эта функция, а после этого рассмотрим работу данной функции совместно с функцией ПОИСКПОЗ (англ. MATCH)
Посмотрите на вот этот пример
Видео: Поиск соответствия в таблице на основе значения из одного столбца ИНДЕКС и ПОИСКПОЗ
Есть таблица с продажами различных фруктов в разных магазинах — это область A2:F10
Напомню синтаксис функции ИНДЕКС:
=ИНДЕКС(массив- номер_строки- номер_столбца)
где массив - это наша таблица A2:F10
номер_строки — как следует из описания — это номер строки нашего массива (таблицы), Обратите внимание! Номер строки необходимо считать именно по нашему указанному массиву, а не вообще с первой строки. Наша таблица начинается со второй строки.
номер_столбца — это номер столбца указанного массива. В нашем случае первый столбец совпадает с первым столбцом нашего массива.
Давайте представим, что нам необходимо найти продажи Слив в магазине Перекресток. С помощью формулы с использованием функции ИНДЕКС это будет выглядеть следующим образом.
=ИНДЕКС(A2:F10-5-3) — смотрите рисунок выше
Еще раз обратите внимание, слива находится в 6-й строке на данном листе, но если рассматривать именно наш массив A2:F10, то видно, что Слива расположена на 5-й строчке данной таблицы. Как видите, все очень просто, но в таком виде формула не имеет применения, так как номер строки и номер столбца мы считали устно и без формулы. Если нам необходимо найти данные по другим товарам нам так же придется все считать устно и указывать номера строк и номера столбцов. Поэтому, в большинстве случаях функцию ИНДЕКС используют совместно с другими функциями, часто с функцией ПОИСКПОЗ, которая позволяет найти номер строки и столбца автоматически.
Функция ИНДЕКС в Excel с функцией ПОИСКПОЗ
Рассмотрим тот же пример. У нас есть таблица по продажам, у нас она маленькая, но это может быть огромный отчет на несколько листов и большим количество магазинов.
И вот из этого отчета нам необходимо вытащить определенные данные. Например, продажи только Груш, Слив и Киви в магазине Перекресток и Лента — правая таблица с желтыми ячейками.
Конечно, в данном случае можно использовать функцию ВПР, при этом номер столбца нам нужно будет считать вручную. Перекресток это 3-й столбец, а Лента это 4-й столбец. Но представим, что количество магазинов будет очень много, к тому же данный отчет нам присылают каждый месяц и магазины могут быть в разных столбцах, кроме того, могут добавляться новые магазины. В данном случае нам будет неудобно использовать ВПР, так как номер столбца нам в каждом случае придется находить заново.
Поэтому в данном случае мы будет использовать функцию ИНДЕКС и ПОИСКПОЗ, к тому же, если данных очень много, то функция ИНДЕКС работает заметно быстрее функции ВПР. Функцию ИНДЕКС мы рассмотрели выше, функцию ПОИСКПОЗ мы описывали в отдельной статье.
[ads]
Итак, давайте для наглядности, чтобы вы видели последовательность действий, сначала пропишем функцию ИНДЕКС в чистом виде. В ячейке L4 нам необходимо найти из таблицы A2:F10 продажи Груш в Перекрестке. Пропишем формулу
=ИНДЕКС(A2:F10-3-3) — груша находится в третьей строке таблицы A2:F10, а Перекресток в третьем столбце. Отлично, а теперь пропишем формулу, чтобы номер строки и номер столбца считался автоматически.
Чтобы найти номер строки используем функцию ПОИСКПОЗ — поиск позиции. Синтаксис функции:
=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
искомое_значение — нашем случае, в ячейке L4 мы ищем груши, поэтому искомое значение у нас будет K4
просматриваемый массив — нашем примере нам необходимо найти груши с столбце с фруктами — это диапазон A2:A10
тип_сопоставления — указываем 0, так как мы ищем полное совпадение.
Формула будет иметь следующий вид:
Видео: Функция ИНДЕКС в Excel
=ПОИСКПОЗ(K4-A2:A10-0) — итогом данной формулы будет позиция 3 в диапазоне A2:A10
аналогично, только в горизонтальном виде находим номер столбца.
искомое_значение - магазин перекресток или ячейка L3
просматриваемый массив - магазин мы находим в строке с магазинами - это диапазон A2:F2
тип_сопоставления - указываем 0, так как мы ищем точное совпадение.
в итоге получаем формулу:
=ПОИСКПОЗ(L3-A2:F2-0) — итогом данной формулы будет позиция 3 в диапазоне A2:F2
Теперь в нашу формулу =ИНДЕКС(A2:F10-3,3) вместо номера строки и номера столбца пропишем раноценные значения, но в виде формул:
Видео: Функции ИНДЕКС и ПОИСКПОЗ в Excel
=ИНДЕКС(A2:F10-ПОИСКПОЗ(K4-A2:A10-0)-ПОИСКПОЗ(L3-A2:F2-0))
Наша формула практически готова. У нас автоматически находится номер строки и номер столбца. Осталось только доделать формулу, чтобы ее можно было протянуть вниз на все товары и право на все магазины. Если мы сделаем это сейчас, то расчет будет неверным так как:
наш массив A2:F10 имеет относительный адрес, поэтому при протягивании формулы вниз и право диапазон так же будет сдвигаться, а он у нас постоянный, поэтому пропишем преобразуем его в абсолютный адрес, для этого пропишем знаки долларов перед столбцами и строки (можно выделить данный диапазон в формуле и нажать клавишу F4).
A2:F10 → $A$2:$F$10
Далее идет номер строки с формулой ПОИСКПОЗ(K4-A2:A10-0), при протягивании вниз у нас автоматически K4 (Груши) поменяется на K5 (Сливы), что нам и требуется, но диапазон, А2:A10 у нас постоянный, поэтому пропишем его в абсолютном виде А2:A10 → $А$2:$A$10
Все отлично, но когда мы будем протягивать формулу вправо, то K4 (Груши) автоматически поменяется на L4, нам же необходимо, чтобы при протягивании право K4 не менялось. Но мы помним, что в то же время нам необходимо, чтобы K4 менялось при протягивании вниз. Поэтому нам необходимо закрепить только столбец (K), а строка должна меняться. Пропишем знак доллар только перед столбцом К4 → $K4
В итоге формула поиска номера строки будет выглядеть ПОИСКПОЗ($K4-$A$2:$A$10-0)
Аналогично с номером столбца, диапазон должен быть полностью закреплен, при протягивании вправо столбец должен меняться, а при протягивании вниз номер строки (строка с магазинами) не должен меняться. Для этого пропишем знак доллара только перед номером строки L3 → L$3
В итоге формула поиска номера столбца будет выглядеть ПОИСКПОЗ(L$3-$A$2:$F$2-0)
Итоговая формула, которую вы можете протянуть вниз и вправо
=ИНДЕКС($A$2:$F$10-ПОИСКПОЗ($K4-$A$2:$A$10-0)-ПОИСКПОЗ(L$3-$A$2:$F$2-0))
=INDEX ($A$2:$F$10-MATCH ($K4-$A$2:$A$10-0)-MATCH (L$3-$A$2:$F$2-0)) — англ. версии
Важно, чтобы названия магазинов и фруктов были полностью аналогичными, если в одной из таблиц будет, например, ошибка или лишний пробел, то формула выдаст ошибку.
С данной формулой вы можете менять местами названия столбцов, добавлять другие магазины и фрукты — формула все равно будет работать, но не забудьте так же менять диапазоны. Можно заранее взять с большим запасом диапазоны, если вы знаете, что данные будут добавляться.
Надеюсь, что статья помогла вам разобраться в данной полезной функции ИНДЕКС и ПОИСКПОЗ. Спасибо за лайки, подписывайтесь на наши страницы и группы в социальных сетях.
Скачать пример файла - Функция-ИНДЕКС-и-ПОИСКПОЗ.xlsx
Поделись в социальных сетях:Похожие
ranuaetdown.ru
