Функции индекс


Содержание

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

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

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

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

Синтаксис, который имеет функция ИНДЕКС в Excel, следующий:

= ИНДЕКС(массив, номер строки, [номер столбика]), где

  • массив – это обязательный аргумент, который содержит в себе константу на массив или диапазон ячеек;
  • номер строки – является обязательным аргументом, который указывает, из какого номера строки нам нужно вернуть результаты;
  • номер столбика – этот аргумент не является обязательным при условии, когда в аргументе массив указан один конкретный столбик, но становится обязательным, когда в массиве, два и более столбика, тогда нужно указать столбик, из которого нужно будет вернуть значение.

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

=ИНДЕКС(C1:G12;6; 2)

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

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

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

Я не буду повторяться с примерами по функции ПОИСКПОЗ, так как, я детально и в разнообразных примерах рассмотрел эту функции в своей статье, и вам рекомендую пройти по ссылке и ознакомится с предоставленными материалами.

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

До новых встреч на страницах сайта!

«Они нуждаются, обладая богатством, — а это самый тяжелый вид нищеты.
»
Л.А. Сенека

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

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

Возвращает значение элемента таблицы или массив, заданного номером строки и номером столбца.

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


ИНДЕКС (массив, номер строки, [номер столбца])

аргумент функции ИНДЕКС описаны ниже.

Массив — обязательный аргумент. Диапазон ячеек или константа массива.

Если массив содержит только одну строку или один столбец, соответствующий аргумент «номер строки» или «номер столбца» не является обязательным.

Если массив содержит больше одной строки и одного столбца, а из аргументов «номер строки» и «номер столбца» задан только один, функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».

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

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

Если используются оба аргумента — и «номер строки», и «номер столбца», — функция ИНДЕКС возвращает значение ячейки на пересечении указанных строки и столбца.

Если указать в качестве аргумента «номер строки» или «номер столбца» значение 0 (ноль), функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формула массива в горизонтальный диапазон ячеек для строки и в вертикальный — для столбца. Чтобы ввести формулу массива, нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

Аргументы «номер строки» и «номер столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС возвратит значение ошибки #ССЫЛКА!.

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

Пример рисунок 27.

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

В книге ниже приведены примеры этой функции. Можно проверить, изменить существующие формулы или ввести собственные, чтобы просмотреть эту функцию в деле.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные Формула Результат

Рисунок 27 Применение функции ИНДЕКС форма массива

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

ИНДЕКС (ссылка; но мер строки; [номер столбца]; [номер области])

Аргументы функции ИНДЕКС описаны ниже.

Ссылка — обязательный аргумент. Ссылка на один или несколько диапазонов ячеек.


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

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

Номер строки — обязательный аргумент. Номер строки в диапазоне, заданном аргументом «ссылка», из которого требуется возвратить ссылку.

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

Номер области — необязательный аргумент. Диапазон в аргументе «ссылка», из которого требуется возвратить значение ячейки на пересечении строки и столбца, заданных аргументами «номер строки» и «номер столбца». Первая введенная или выделенная область имеет номер 1, вторая — 2 и т. д. Если аргумент «номер области» опущен, в функции ИНДЕКС используется область 1.

Например, если аргумент «ссылка» определяет ячейки (A1: B4, D1: E4, G1: H4), номер области 1 соответствует диапазону A1:B4, номер области 2 — диапазону D1:E4, а номер области 3 — диапазону G1:H4.

Илон Маск рекомендует:  Атрибут src в HTML

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

Если указать в качестве аргумента «номер строки» или «номер столбца» значение 0 (ноль), функция ИНДЕКС возвратит ссылку на целый столбец или целую строку соответственно.

Аргументы «номер строки», «номер столбца» и «номер области» должны указывать на ячейку внутри аргумента «ссылка»; в противном случае функция ИНДЕКС возвращает значение ошибки #ССЫЛКА!. Если аргументы «номер строки» и «номер столбца» опущены, функция ИНДЕКС возвращает область в аргументе «ссылка», заданную аргументом «номер области».

Результатом вычисления функции ИНДЕКС является ссылка, которая интерпретируется в качестве таковой другими функциями. В зависимости от формулы значение, возвращаемое функцией ИНДЕКС, может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА(«ширина»; ИНДЕКС(A1:B2;1;2)) эквивалентна формуле ЯЧЕЙКА(«ширина»;B1). Функция ЯЧЕЙКА использует значение, возвращаемое функцией ИНДЕКС, как ссылку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2;1;2), преобразует значение, возвращаемое функцией ИНДЕКС, в число в ячейке B1.

Пример рисунок 28

В книге ниже приведены примеры этой функции. Можно проверить, изменить существующие формулы или ввести собственные, чтобы просмотреть эту функцию в деле.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные Формула Результат

Рисунок 28 Применение функции ИНДЕКС ссылочная фор

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Как то на паре, один преподаватель сказал, когда лекция заканчивалась — это был конец пары: «Что-то тут концом пахнет». 8379 — | 8010 — или читать все.

188.64.174.135 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)

очень нужно

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

Рассмотрим пример использования функции ИНДЕКС в 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.

Функция ИНДЕКС в Excel: краткое описание, применение и примеры

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

Описание

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

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

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

Функция «ИНДЕКС» в Excel иногда выдает значение «#ССЫЛ!». Чаще всего это происходит, если ячейка, расположенная на пересечении указанных строки и столбца, находится вне указанного диапазона.

Примеры применения

Рассмотрим несколько случаев использования функции «ИНДЕКС» на практике.

Предположим, имеется массив, состоящий из 4 столбцов и 4 строк (см. таблицу). Если ввести в одну из ячеек таблицы расположенное вне диапазона А1:Е5 выражение «=ИНДЕКС (В2:Е5, 2, 3)» (без кавычек) и нажать на «Ввод», то в ответ будет выдано значение «бегония».

Если требуется узнать, сколько учащихся Группы 2 получили оценку «неудовлетворительно», то в соответствующую ячейку следует ввести выражение: ИНДЕКС (С2:С5, 1).

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

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

Рассмотрим случай, когда массив состоит из единственной строки.

Диапазон значений в этом случае В3:В6.


Выбираем ячейку в другой строке, например D1. Вводим в нее название фрукта, позицию которого хотим найти, в данном случае «апельсины». В ячейке (Е1), куда хотим записать номер соответствующей строки, вводим «= ПОИСКПОЗ(D1;В3:В6;0)» (см. таблицу). В результате там появляется число 3. Именно такой номер в диапазоне В3:В6 у выражения «апельсины».

Последний 0 означает, что требуется найти точное совпадение со значением D1.

Как найти все текстовые значения, удовлетворяющие некому критерию

В виде, представленном выше, функция «ПОИСКПОЗ» возвращает только одно значение (самое первое, т. е. верхнее). Но что делать, если в списке есть повторения. В таком случае помогают формулы массива. Для их использования следует выделить весь диапазон данных и использовать сочетание клавиш «Ctrl+Shift+Enter». Однако ее рассмотрение не является предметом данной статьи.

Функция «ИНДЕКС» и «ПОИСКПОЗ» в Excel: примеры

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

Для этого лучше всего совместно использовать обе функции. Чтобы узнать, что необходимо ввести в H2, сначала рассмотрим самое простое выражение, которое можно использовать для этой цели. В частности, искомое значение можно получить, если записать в эту ячейку «=ИНДЕКС(А2:Е5;1;2)». Здесь мы использовали вариант из предыдущих примеров, когда номер строки и столбца высчитывался вручную. Однако наша цель — автоматизировать этот процесс. Для этого следует вместо двойки и единицы, которые указывают на искомые строку и столбец, в массиве записать соответствующие функции «ПОИСКПОЗ», выдающие эти номера. Обратите внимание, что мы ищем выражение «уд», расположенное в ячейке G2 и «гр. 2» из H2. Кроме того, нам нужны точные совпадения, поэтому в качестве последнего, третьего, аргумента в обоих случаях указывается 0.

Илон Маск рекомендует:  Переполнение буфера

Тогда вместо 1 в формуле ИНДЕКС(А2:Е5;1;2) следует записать: ПОИСКПОЗ(G2;A2:A5;0), а вместо 2 — ПОИСКПОЗ(H2; А2:Е2;0).

После подстановки имеем: ИНДЕКС(А2:E5; ПОИСКПОЗ(G2;A2:A5;0); ПОИСКПОЗ(H2; А2:Е2;0)). В результате, нажав «Ввод», имеем в этой ячейке значение «10».

Как распространить действие полученной формулы на некий диапазон

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

Главная сложность заключается в том, что массив А2:Е5 имеет относительный адрес. Чтобы исправить это, следует превратить его в абсолютный. Для этого массив записывается в виде $А$2:$Е$5. То же следует сделать и для обеих встроенных функций, т. е. они должны выглядеть как ПОИСКПОЗ($G$2;$A$2:$A$5;0) и ПОИСКПОЗ($H$2; А$2:$Е2;0).

Окончательный вид формулы будет: ИНДЕКС($А$2:$Е$5; ПОИСКПОЗ($G$2;$А$2:А$5;0); ПОИСКПОЗ($H$2; $А$2:$Е$2;0)).

В результате будем иметь таблицу, изображенную ниже

Функции MS Excel ИНДЕКС (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.

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

Форма массива

  • Если массив содержит только одну строку или один столбец, соответствующий аргумент «номер_строки» или «номер_столбца» не является обязательным.
  • Если массив содержит больше одной строки и одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».
  • Если используются оба аргумента — и «номер_строки», и «номер_столбца», — функция ИНДЕКС возвращает значение ячейки на пересечении указанных строки и столбца.
  • Если указать в качестве аргумента «номер_строки» или «номер_столбца» значение 0 (ноль), функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формулу массива в горизонтальный диапазон ячеек для строки и в вертикальный — для столбца. Чтобы ввести формулу массива, нажмите сочетание клавиш CTRL + SHIFT + ENTER .
  • В Excel Web App невозможно создавать формулы массива.

  • Аргументы «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС возвратит значение ошибки #ССЫЛКА!.

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

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

Например, если аргумент ссылка определяет ячейки A1:B4,D1:E4,G1:H4 , номер области 1 соответствует диапазону A1:B4 , номер области 2 — диапазону D1:E4 , а номер области 3 — диапазону G1:H4 .

Функция «ИНДЕКС» в Excel: описание, применение и примеры

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

Илон Маск рекомендует:  Что такое код http запрос

Описание

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

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

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

Функция «ИНДЕКС» в Excel иногда выдает значение «#ССЫЛ!». Чаще всего это происходит, если ячейка, расположенная на пересечении указанных строки и столбца, находится вне указанного диапазона.

Примеры применения

Рассмотрим несколько случаев использования функции «ИНДЕКС» на практике.

Предположим, имеется массив, состоящий из 4 столбцов и 4 строк (см. таблицу). Если ввести в одну из ячеек таблицы расположенное вне диапазона А1:Е5 выражение «=ИНДЕКС (В2:Е5, 2, 3)» (без кавычек) и нажать на «Ввод», то в ответ будет выдано значение «бегония».

Функция «Индекс»

1 . Откройте книгу Сводные данные и сохраните ее под именем
Счет_индекс.
2 . Лист Квартира 1 скопируйте и копию переименуйте в
Счет_автомат.
3 . Шапку квитанции Счет_автомат переделайте, расположив строки в
соответствии с: № квартиры, Фамилия, Лицевой счет, Число
проживающих, Общая площадь, Льгота наем, Льгота ком. услуги (см.
Таблицу 7).
4 . Ячейкам с соответствующими числовыми данными задайте имена:
номер_квартиры, Фамилия, Лицевой_счет, Число_проживающих,
Общая_площадь, Льгота_наем, Льгота_ком_услуги.
5 . В таблице на листе Список жильцов строки отсортируйте по
возрастанию номеров квартир.
6 . В ячейку с именем Фамилия введите функцию ИНДЕКС. Указание.
Синтаксис функции = ИНДЕКС(массив;номер_строки;номер_столбца).
Данные для параметра массив берутся из листа Список жильцов (обвести
нужные данные мышью). Строки выдаются строками. Поэтому исходный
массив нужно транспонировать: =ТРАНСП(массив). Если пропустить
параметр №строки, то из массива напечатается указанный столбец.
= ИНДЕКС(ТРАНСП(массив);;номер_квартиры)
7 . Вывод результата функции в виде массива:
o выделите диапазон ячеек, куда необходимо выдать результат,
начиная с ячейки с формулой;
o Нажмите F2, затем Ctrl + Shift + Enter.
8 . К ячейке, в которой должен быть № квартиры, сделайте примечание
«Введи № квартиры» по команде Вставка – Примечание. Сделайте его
всегда видимым по команде Сервис – Параметры – Вид…
9 . Отредактируйте формулы в столбцах Рассчитано для оплаты и
Льгота, чтобы правильно выводилась Общая сумма оплаты.

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

Вложения

Счета1.xlsx (47.0 Кб, 7 просмотров)
30.03.2014, 20:03

Требуется чтобы в «Таблице» при заполнении столбца «тип картриджа» столбец «аппарат» заполнялся автоматически
Доброго времени суток! Есть таблица по учету картриджей и техники. Требуется чтобы в «Таблице» при.

Как сделать: если во всех ячейках диапазона знач.=»выполнено», то в данной ячейке тоже «выполнено» иначе «в работе»
Прилагаю файл. В ячейке G17 формула, в которой надо упростить условие: Если в диапазоне ячеек.

Функция ПРОСМОТР все равно возвращает какое-то значение, а не «Н\Д»
Доброго дня. Столкнулся с непонятной мне логикой при использовании функции «ПРОСМОТР»: при вводе в.

Функция ЕСЛИ со значениями формата «Дата», несколько условий
Добрый день! Просьба посмотреть формулу из файла во вложении. Если «Срок жизни» менее года.

Поле со списком и функция ИНДЕКС в Excel

В статье «Расчет передачи винт-гайка» я обещал рассказать, как работает функция ИНДЕКС в 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. В ячейку B 13 : =ИНДЕКС(B5:B9;A3) =150

15. В ячейку B 14 : =ИНДЕКС(C5:C9;A3) =80

Теперь при выборе из выпадающего списка любого материала функция ИНДЕКС тут же выведет в ячейки B13 и B14 соответствующие этому материалу значения предела прочности [σв] и предела текучести [σт]. Эти значения могут участвовать в дальнейших расчетах, подставляться в формулы в качестве исходных данных.

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

На этом – всё о совместном использовании функции ИНДЕКС и элемента «Поле со списком» из панели инструментов «Формы» для обеспечения доступа к базе данных в виде двухмерной таблицы при написании расчетных программ.

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

Все описанные действия выполнялись в Excel-2003. Для более новых версий программы действия будут похожими – думаю, разберетесь. В OOo Calc аналогом функции ИНДЕКС MS Excel является функция index.

Уважаемые читатели, для получения анонсов статей моего блога прошу оформить подписку в окне «Подпишитесь на новости», расположенном вверху страницы. Введите адрес своей электронной почты и нажмите на кнопку «Получать анонсы статей». Один раз в 7…10 дней к вам на почтовый ящик будет приходить небольшое уведомление о появлении на моем блоге новой статьи, ее название и краткое описание. Если вам что-то не понравится или просто надоест автор или тема, вы прямо в почте всегда можете отказаться от подписки.

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL