Срок доставки товара в течении 1-3 дней !!!
|
|
5 вариантов использования функции ИНДЕКС (INDEX). Индекс в экселе
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 | SirExcel
В этой статье мы будем рассматривать функцию ИНДЕКС(). Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или поименованного диапазона, заданного номером строки и номером столбца.
Синтаксис функции ИНДЕКС довольно прост:ИНДЕКС(массив, номер_строки, [номер_столбца])
Массив — обязательный аргумент, диапазон ячеек или константа массива.
Если массив содержит только одну строку или один столбец, аргумент «номер_строки» или «номер_столбца» соответственно не является обязательным. Если массив занимает больше одной строки и одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».
Номер_строки — обязательный аргумент, номер строки в таблице, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.Номер_столбца — необязательный аргумент, номер столбца в таблице, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.
Смотрите так же нашу статью о том, как использовать функцию ИНДЕКС и ПОИСКПОЗ в Excel с примером.
Напомним, что формулы массива заключаются в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ВВОД.) в горизонтальный диапазон ячеек для строки и вертикальный для столбца. Чтобы ввести формулу массива, нажмите клавиши CTRL + SHIFT + ВВОД.
Аргументы «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива или тблицы, в противном случае функция ИНДЕКС возвратит значение ошибки #ССЫЛ!.
Рассмотрим пример использования функции Индекс, допустим у нас есть какая-либо таблица данных:
Данные
Мы хотим при помощи функции Индекс вытащить значение «u» из таблицы. Значение «u» находится на пересечении Строки 2 и Столбца 3 (т.к. нумерация начинается с 0), т.о. формула имеет вид =ИНДЕКС(B2:E5;2;3):
Использование функции Индекс
sirexcel.ru
Функции MS Excel ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) – более гибкая альтернатива функции ВПР
Многие пользователи знают и применяют формулу ВПР. И это правильно. Известно также, что ВПР имеет ряд особенностей и ограничений, которые несложно обойти. Однако есть еще один нюанс, который значительно ограничивает возможности функции ВПР.
ВПР требует, чтобы в диапазоне с искомыми данными столбец критериев всегда был первым слева. Это обстоятельство, конечно, является ограничением ВПР. Как же быть, если искомые данные находятся не справа, а слева от столбца с критерием? Можно, конечно, расположить столбцы в нужном порядке, что в целом, является неплохим выходом из ситуации. Но бывает так, что сделать этого нельзя, или трудно. К примеру, вы работаете в чужом документе или используете исходные данные, получаемые в некотором определенном виде. В общем, напрашивается решение, которое позволяло бы подставлять («подтягивать») данные независимо от взаиморасположения столбцов. Такое решение существует.
Нужно воспользоваться комбинацией двух функций: ИНДЕКС и ПОИСКПОЗ. При этом не нужно будет выделять всю таблицу, достаточно указать столбец с критериями (для ПОИСКПОЗ) и столбец с искомыми значениями (для ИНДЕКС).
Формула работает следующим образом. ИНДЕКС отсчитывает необходимое количество ячеек вниз в диапазоне искомых значений. Количество отсчитываемых ячеек определяется по столбцу критериев функцией ПОИСКПОЗ. Работу комбинации этих функций удобно рассмотреть с середины, где вначале находится номер ячейки с подходящим критерием, а затем этот номер подставляется в ИНДЕКС.
Таким образом, чтобы подтянуть значение цены, соответствующее первому коду (книге), нужно прописать такую формулу.
Следует обратить внимание на корректность ссылок, чтобы при копировании формулы ничего не «съехало».
Протягивая формулу вниз, заполняем весь столбец. Если в таблице, откуда подтягиваются данные, нет искомого критерия, то функция выдает ошибку #Н/Д, что и показано на рисунке ниже.
Довольно стандартная ситуация, с которой успешно справляется функция ЕСЛИОШИБКА. Она перехватывает ошибки и вместо них выдает что-либо другое, например, нули.
Конструкция формулы будет следующая:
Вот, собственно, и все.
Таким образом, комбинация функций ИНДЕКС и ПОИСКПОЗ является полной заменой ВПР и обладает дополнительным преимуществом: умеет находить данные слева от столбца с критерием. Кроме того, сами столбцы можно двигать как угодно, лишь бы ссылка не съехала, чего нельзя проделать с ВПР, т.к. количество столбцов там указывается конкретным числом. Посему комбинация ИНДЕКС и ПОИСКПОЗ более универсальна, чем ВПР.
Ниже видеоурок по работе функций ИНДЕКС и ПОИСКПОЗ.
Скачать файл с примером.
statanaliz.info
Поле со списком и функция ИНДЕКС в 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
