Как работать с функцией индекс если в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылки по теме

• Трехмерный поиск данных по нескольким листам (ВПР 3D)
• Поиск и подстановка по нескольким условиям (ВПР по нескольким столбцам)
• Подстановка данных из одной таблицы в другую с помощью функции ВПР (VLOOKUP)

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

Пример работы функции ИНДЕКС в Excel от простого к сложному

Сначала рассмотрим самый простой пример применения. У нас есть несколько городов. Пусть нашей первой задачей будет извлечь пятый по счету город из нашей одномерной базы данных. Синтаксис нашей формулы будет следующий: ИНДЕКС(массив; номер_строки). Начинаем писать формулу (яч. B9). Первый аргумент – это все города B3:C7, а второй – необходимый нам номер ряда, в котором хранится информация, которую мы хотим получить – пятый город:

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

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



Функция ИНДЕКС двумерный и многомерный массив

Теперь наш массив двухмерный. Пусть теперешним заданием будет узнать количество туристов в Ливерпуле. В ячейке B19 пишем формулу. Синтаксис тоже видоизменится: ИНДЕКС(массив; номер_строки; номер_столбца), у нас добавится третий аргумент – номер столбца, в котором хранится нужные нам данные:

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

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

И получаем готовую формулу с решением поставленной задачи:

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

Функцию ИНДЕКC так же можно комбинировать и с другими функциями. Например, наша база данных содержит дополнительно количество туристов Великобритании за 2018-2019 года, мы хотим узнать сумму туристов:

1. за 2018 год.
2. по городу Манчестер за весь период.

В этом нам поможет известная любому пользователю несложная функция СУММ. Она охватит результат поиска, сделанным ИНДЕКC и выполнит операцию сложения найденных значений. Чтобы выполнить первую задачу, для начала нам нужно выбрать диапазон с одними числами(без столбца с городами, поскольку будем суммировать численные значения C43:E47). Затем на месте, где мы прописывали номер строки, пишем 0. Благодаря этому ИНДЕКC не будет «искать» данные по строкам, а просто перейдет к следующей операции. А следующая операция – прописываем столбец 2 и получаем ответ на первый пункт:

Сумма туристов за 2018 год по пяти городам Великобритании составляет 40 987 людей.

Аналогично этой схеме мы ищем ответ на второй пункт. Только теперь нам нужно считать данные с ряда, поэтому ноль ставим на месте номера столбца:

И получаем ответ на второй пункт – всего посетителей за три года в Манчестере было 2 474 человека.

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

Формулировку функции можно видоизменять в зависимости от постановки задачи. Пусть нам нужно определить и показать сколько туристов вместе было в 2017-м году в первых трёх городах по списку – Кембридже, Манчестере и Лондоне. Тогда мы снова используем функцию СУММ, но в месте, где указывается конец диапазона значений, по которым мы проводим поиск, вставляем нашу ИНДЕКС. Проще говоря, диапазон для суммирования выглядит так: (начало:конец). Началом будет первая ячейка массива, как обычно, а конец мы заменим. Укажем количество городов, по которым будем считать людей (действие 1). Затем, когда будем составлять функцию ИНДЕКC, укажем, что первый аргумент – числовой диапазон всех пяти городов за 2017-й год, а второй – наше кол-во городов (ячейка D64):

Точно так же можно выполнить следующее задание: найти сумму посетителей Оксфорде за 2017-2018 года. В этом случае делаем всё точно так же, только выбираем горизонтальный диапазон C61:E61 :

В конце необязательно ссылаться на ячейку с написанным количеством искомых значений, можно сразу писать цифру 3 в первом примере и цифру 2 во втором примере, всё будет работать точно так же:

и

идентичны.

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

Формула ИНДЕКС и ПОИСКПОЗ лучшая замена функции ВПР

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

Получается, что для работы сначала нужно упорядочить столбцы в соответствии с требованиями, а только потом совершать действия. Но иногда сама структура отчета или сводки, с которой нам нужно иметь дело, не позволяет совершать перестановки. Тогда нам очень кстати пригодится ИНДЕКС в сочетании с функцией ПОИСКПОЗ. Синтаксис ИНДЕКСА: (массив; номер_строки; номер_столбца). На первой позиции у нас будет диапазон значений (туристы, 2017), вместо следующих двух пишем ПОИСКПОЗ (искомый критерий;диапазон критериев; 0 (для точного результата)):

Рассмотрим более наглядный и реальный пример, где можно заменить ВПР. У нас есть два отчета: один — о количестве продаж определенного товара, а второй – о цене на упаковку одного товара. И как раз вторая таблица имеет такое расположение столбцов, которое не позволяет использовать нам ВПР – первыми занимают место значения, а вторыми – критерии:

Для того чтобы выполнить заполнение первой таблицы, делаем точно также, как в предыдущем примере – в ячейке D88 пишем ИНДЕКС, первым делом указываем столбец, где находятся искомые значения (цена за упаковку). Затем нужно указать, где нам искать соответствующие критерию значения — подстраиваем ПОИСКПОЗ под наше решение: выбираем искомый критерий (Хепилор), затем массив искомых критериев со второй таблицы:

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

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

Скачать примеры функции ИНДЕКС и ПОИСКПОЗ в Excel

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

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

Описание функции ИНДЕКС

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

Что возвращает функция

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

Синтаксис

Существует четыре вариации синтаксиса этой функции. Две русские версии:

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

Две английские версии:

1. =INDEX (array, row_num, [col_num]).
2. =INDEX (array, row_num, [col_num], [area_num]).

Аргументы функции

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

• array – обозначает диапазон секторов или массив данных, по которому будет осуществляться поиск;
• row_num – обозначает номер строчки, где располагаются необходимые значения;
• [col_num] – обозначает номер столбика, где располагаются необходимые значения. Этот параметр является необязательным. В случае, когда в функции ИНДЕКС не описан аргумент номера строчки, этот параметр нужно указывать;
• [area_num] – используется в тех случаях, когда массив имеет некоторое количество диапазонов. Параметр является необязательным и применяется для осуществления выбора абсолютно всех диапазонов.

Дополнительная информация

Рассмотрим некоторые особенности функции, которые необходимо знать при ее использовании:

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

Как работает функция ИНДЕКС в Excel?

Рассмотрим процесс работы функции ИНДЕКС с различными типами данных в табличном процессоре.

Функция ИНДЕКС в Excel пошаговая инструкция

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

Функция ИНДЕКС для массивов

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

Цель: в выделенном секторе показать название пятой в списке позиции. Пошаговая инструкция выглядит так:

1. Кликаем на сектор, в который желаем в дальнейшем вывести результат манипуляций. Щелкаем кнопку «Вставить функцию», располагающуюся рядом со строкой для ввода формул.

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

1. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем 1-й предложенный вариант и нажимаем «ОК».

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

1. В поле «Номер_строки» вбиваем число 5, так как этого требует поставленная перед нами задача.
2. В поле «Номер_столбца» вбиваем цифру 1, так как названия значений располагаются в 1-м столбике массива.
3. После проведения всех настроек жмем «ОК».

1. В выбранном секторе появился итоговый результат.

Обратите внимание! Один из аргументов можно не заполнять в случае, если массив является одномерным.

Такие действия выглядят следующим образом:

1. В строке «Массив» необходимо выделить только ячейки первого столбика. Вводим номер строки – 5, а номер столбика не заполняем, потому что массив является одномерным.

1. Щелкаем на «ОК». В итоге, в выделенном секторе получаем аналогичный результат.

Функция ИНДЕКС для ссылок

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

Цель: выявить количество продаж в 4-й позиции за 2-й квартал в штуках. Пошаговое руководство выглядит следующим образом:

1. Кликаем на сектор, в который желаем в дальнейшем вывести результат манипуляций. Щелкаем кнопку «Вставить функцию», располагающуюся рядом со строчкой для ввода формул.

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

1. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем второй предложенный вариант и нажимаем «ОК».

1. В следующем окошке необходимо указать аргументы. В поле «Ссылка» вводим те же значения, которые вводили в поле «Массив» в предыдущем примере. Главное отличие – указываем не один диапазон, а сразу четыре, разделяя их знаком «;». После описания необходимо поставить «(» в начале и «)» в конце.

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

1. После проведения всех манипуляций щелкаем на кнопку «ОК». Готово, выбранном секторе появился необходимый нам ответ.

Использование с оператором СУММ

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

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

1. Для первого аргумента оператора СУММ выставляем координаты сектора, являющегося точкой начала суммирующего диапазона.
2. 2-й аргумент задается при помощи функции ИНДЕКС. Кликаем на строчку для ввода формул и вбиваем следующее значение: =СУММ(D2:ИНДЕКС(D2:D9;8)). Число 8 говорит о том, что мы выставляем ограничение для выбранного диапазона между секторами D2 и D9.

1. Жмем на клавишу «Enter», чтобы вывести конечный результат в выделенном изначально секторе.

Сочетание с функцией ПОИСКПОЗ

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

• Искомое значение. Этот аргумент указывает значение, которое нужно отыскать в выделенной области.
• Просматриваемый массив. Область секторов для поиска искомого показателя.
• Тип сопоставления. Аргумент не является обязательным и применяется для более точного поиска.

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

1. Первоначально заполняем ячейку F1.

1. Кликаем на сектор, в который желаем в дальнейшем вывести результат манипуляций. Щелкаем кнопку «Вставить функцию», располагающуюся рядом со строкой для ввода формул.

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

1. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем первый предложенный вариант и нажимаем «ОК».

1. В строку «Массив» вводим сектор столбика, в котором располагается стоимость позиций.

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

1. В поле «Искомое_значение» вводим координаты ячейки, по наполнению которой будет осуществляться поиск в главном массиве. В поле «Просматриваемый_массив» вбиваем диапазон для поиска искомого показателя. Вводим 0 в поле «Тип_сопоставления».

1. В строке для ввода формул необходимо кликнуть на «ИНДЕКС».

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

1. В итоге мы получили в выделенном секторе необходимый результат – стоимость позиции. Различные изменения стоимости в главной таблице будут отражаться в этой ячейке. Это же правило работает и с изменением наименования во вспомогательной табличке.

Обработка нескольких таблиц

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

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

1. В выбранный заранее сектор вывелись необходимые результаты.

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

Дополнительно разберем еще один пример использования. Например, у нас есть следующая табличная информация:

Чтобы узнать результат Андрея по дисциплине «Физика», необходимо применить следующую формулу: =ИНДЕКС($B$3:$E$9;3;2). Здесь мы произвели определение показателей нужного диапазона: $B$3:$E$9. Цифра 3 означает номер строчки, где располагается результат Андрея. Цифра 2 означает номер столбца, где располагается дисциплина «Физика».

Ошибки

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

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

Заключение

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

Описание функции ИНДЕКС

ИНДЕКС относится к категории операторов «Ссылки и массивы» и имеет два разных набора аргументов:

1. Для массивов

Формула функции в этом случае выглядит так:

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

вы можете ввести один из двух аргументов: «Номер_строки» или «Номер_столбца». Все зависит от типа массива, с которым вы имеете дело. Например, если массив занимает только одну строку, аргумент «Row_number» не имеет значения, и заполняется только «Column_number». Для массива, занимающего только один столбец, все то же самое. Здесь есть нюанс: необходимо указывать номера строк и столбцов, ориентируясь не на общие обозначения на панелях координат программы, а на порядковые номера внутри указанного массива.

2. Для ссылок

Формула функции выглядит так:

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

В этом случае, как и в формуле массива, может быть заполнен только один из аргументов: «Row_num» или «Column_number». Последний аргумент, Region_Number, заполняется, когда вам нужно работать с несколькими диапазонами, в противном случае это необязательно.

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

Функция ИНДЕКС для массивов

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

Наша задача — вывести название пятой позиции в списке в заранее выбранной ячейке.

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

Как мы отмечали ранее, один из аргументов функции («Column_num» или «Row_number») может оставаться пустым, если выделенный массив является одномерным, т.е они занимают строку или столбец. На практике это выглядит так.

1. В окне аргументов функции в поле «Массив» выберите только ячейки первого столбца. Обозначим номер строки — 5 и номер столбца, соответственно, остается пустым, так как в выбранном нами массиве только один.
   
2. Нажав ОК, мы получим тот же результат в ячейке таблицы, хотя аргументы функции и ее формулы отличаются от исходной версии.
   

Функция ИНДЕКС для ссылок

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

Допустим, у нас есть 4 таблицы. Каждый из них предоставляет информацию о продажах за определенный период времени (1, 2, 3 и 4 квартал).

Нам нужно знать продажи 4-й позиции («Системная единица») за второй квартал в штуках.

1. Мы находимся в ячейке, в которой собираемся отобразить окончательный результат, и нажимаем кнопку «Вставить функцию» (fx).
   
2. Выберите функцию ИНДЕКС и нажмите ОК.
   
3. Во вспомогательном окне остановитесь на втором варианте (для ссылки) и нажмите кнопку ОК.
   
4. Появится окно с аргументами заполняемой функции:
   • поле «Ссылка» заполняется так же, как аргумент «Массив» в предыдущем примере (вручную или с помощью метода выбора в самой таблице). Единственное отличие состоит в том, что в этом случае нам нужно указать 4 диапазона ячеек вместо одного за раз, перечисляя их через точку с запятой. Те мы указываем первую область, ставим знак «;», затем указываем вторую область и так далее. Когда все будет готово, мы помещаем открывающую и закрывающую скобки в начало и конец ссылки соответственно.
     
   • в значении аргумента «Line_number» пишем число 4, так как нас интересуют данные для четвертой позиции.
   • в поле «Номер_столбца» напишите цифру 3, так как штучные продажи нам не нужны, и это третий столбец в выбранных диапазонах.
   • в поле аргумента «Номер региона» укажите цифру 2, так как мы хотим отображать данные за второй квартал, который соответствует второму интервалу, отмеченному в аргументе «Ссылка”.
   • когда все будет готово, нажмите ОК.
     
5. В ячейке, выбранной функцией, будет отображаться требуемый результат в соответствии с условиями, указанными в аргументах.
   

ВПР по двум условиям при помощи формулы массива.

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

Для этого в верхней части нашего списка обратите внимание на критерии поиска: дата и магазин. В ячейке B3 мы покажем сумму дохода.

Формула в B3 выглядит следующим образом:

{= ВПР (B1, SE (B6: B19 = B2, A6: C19, «»), 3,0)}

Обратите внимание на фигурные скобки, которые указывают, что это формула массива. То есть наша функция ВПР работает не с одиночными значениями, а единожды с массивами данных.

Разберем процесс подробно.

Ищем дату, записанную в ячейку B1. Но мы будем искать его только не в нашем исходном диапазоне данных, а в немного измененном. Для этого воспользуемся условием

ЕСЛИ (A6: B19 = B2; A6: C19;»»)

То есть, если имя магазина соответствует критерию в ячейке B2, мы сохраняем исходные значения из нашего диапазона. А если нет — заменяем их пробелами. И так по каждой строчке.

В результате мы получаем следующий виртуальный массив данных на основе нашей исходной таблицы:

Как видите, строки, в которых раньше было «Магазин 1», были заменены пустыми. А теперь будем искать нужную дату только в информации «Магазин 2». И извлеките значения дохода из третьего столбца.

Функция ВПР может справиться с этим типом работы.

Этот ход стал возможным благодаря применению формулы массива. Поэтому обратите особое внимание: вовсе не обязательно писать скобки в формуле от руки! В ячейке B3 напишите формулу

= ВПР (B1; SE (B6: B19 = B2; A6: C19; «»); 3,0)

А затем нажмите комбинацию клавиш CTRL + SHIFT + ENTER. В то же время Excel поймет, что вы хотите вставить формулу массива и заменить сами скобки.

Таким образом, функция ВПР выполняет поиск по двум столбцам в 2 этапа. Во-первых, мы удаляем диапазон данных из строк, которые не соответствуют одному из условий, с помощью функции ЕСЛИ и формулы массива. А затем, используя эту правильную информацию, мы выполняем обычный поиск только с одним секундным критерием, используя ВПР.

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

Вот как это будет выглядеть.

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

​Смотрите также​​ ячеек. Как сравнить​​ я дал именам,​​ шапкой. Фиксируем F4.​​Автор: Антон Андронов​Чтобы задать стиль ссылок​​ в результат может​​Тип​​выберите пункт​​должен быть в​​ распространенные причины «Ошибка​​ Для этого массив​H​товары​ то в ответ​ получаем с помощью​ на рисунке ниже​Совместное использование функций​ на совпадение значений​ соответствующие их названиям,​Тип сопоставления. Excel предлагает​​В Excel есть очень​​R1C1​​ быть включено имя​​введите​Почтовый индекс​ порядке убывания.​

​ # н/д» должна​ записывается в виде​

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

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

​ листа.​[< = 99999] 00000;​​или​​В приведенном ниже примере​ отображаться, являющееся результатом​ $А$2:$Е$5. То же​

​1​овощи​​ «бегония».​​ПОИСКПОЗ(C16;B1:E1;0)​ из диапазона A1:C4,​и​

​Примеры функции ИНДЕКС для​ Теперь диапазоны называются​ больше, меньше и​​ редко используемая функция,​​ умолчанию стиля​Функция​ 00000 0000​Индекс + 4​ — функция​ функции индексили ПОИСКПОЗ​ следует сделать и​N/N​фрукты​A​

​ Удобная она потому,​​, Вы должны указать​​(АДРЕС) может возвратить​ как создать пользовательские​Примечания:​=MATCH(40,B2:B10,-1)​Примечание:​ функций, т. е.​​гр. 2​​3​

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

​C​​Если подставить в исходную​​ и 2 столбца.​ хорошая альтернатива​Примеры работы функции​Первый аргумент определен теперь​ поэтому выбираем точное​ что позволяет выдавать​ значение FALSE (ЛОЖЬ)​ адрес ячейки или​ числовые форматы Просмотр,​ ​

​Аргумент​ Если необходимо​ они должны выглядеть​гр. 3​помидоры​D​ громоздкую формулу вместо​Стоит отметить, что номера​ВПР​

​ ИНДЕКС для динамической​ осталось написать формулы​ совпадение. В программе​ значение из диапазона​ для аргумента​​ работать в сочетании​​ Создание или удаление​Эти коды будут доступны​тип_сопоставления​ИНДЕКСУ​

​ как ПОИСКПОЗ($G$2;$A$2:$A$5;0) и​гр. 4​груши​E​

​ функций​ строк и столбцов​,​

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

​ выборки и фильтрации​ для остальных аргументов.​​ оно значится как​​ по заданным номерам​​а1​​ с другими функциями,​​ пользовательского числового формата.​​ в списке​в синтаксисе присваивается​или​ ПОИСКПОЗ($H$2; А$2:$Е2;0).​гр. 2​соль​1​ПОИСКПОЗ​ задаются относительно верхней​ГПР​ значений из исходной​​​​ 0 (ноль). На​​ строки и столбца.​​.​ чтобы:​

​Можно отформатировать ячейку или​Тип​ значение -1, это​ПОИСКПОЗ​Окончательный вид формулы будет:​гр. 4​4​N/N​уже вычисленные данные​

​ левой ячейки диапазона.​и​ таблицы. Как сделать​​С помощью функции ВЫБОР,​​ этом аргументы ПОИСКПОЗ​На практике ИНДЕКС редко​=ADDRESS($C$2,$C$3,1,FALSE)​​Получить адрес ячейки, зная​​ диапазон ячеек для​только в том​ означает, что порядок​​для возврата осмысленные​​ ИНДЕКС($А$2:$Е$5; ПОИСКПОЗ($G$2;$А$2:А$5;0); ПОИСКПОЗ($H$2;​

​2​

​огурцы​1​ из ячеек D15​ Например, если ту​ПРОСМОТР​ массив значений ячеек​

​ я создал виртуальную​​ закончились.​​ используется, скорее всего,​=АДРЕС($C$2;$C$3;1;ЛОЖЬ)​

1. ​ номер строки и​ отображения начальных знаков,​ случае, если в​ значений в B2:​
2. ​ значения вместо #​​ $А$2:$Е$2;0)).​​«неуд»​яблоки​2​​ и D16, то​​ же таблицу расположить​. Эта связка универсальна​ диапазона?​
3. ​ таблицу данных, состоящую​​Номер столбца. Опять же​​ из-за того, что​Последний аргумент – это​ столбца.​ чтобы почтовый индекс​​ списке​​ B10 должен быть​ н/д, используйте функцию​

​В результате будем иметь​5​перец​​3​​ формула преобразится в​ в другом диапазоне,​ и обладает всеми​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel​ из 3 ячеек​ воспользуемся ПОИСКПОЗ. Искомым​

​ эти самые номер​

​ имя листа. Если​Найти значение ячейки, зная​

​ предшествует недостаточно символов​Язык (местоположение)​ в порядке убывания​ IFERROR и затем​ таблицу, изображенную ниже​3​​5​​4​​ более компактный и​​ то формула вернет​ возможностями этих функций.​ для промежуточных расчетов​ с именами магазинов,​ значением будет ячейка​ строки и столбца​ Вам необходимо это​ номер строки и​ для заполнения ширины​

​выбран пункт​

office-guru.ru

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

​ формулы для работы.​ вложить функций​A​1​перцы​2​ понятный вид:​ тот же результат:​ А в некоторых​ .​ т.е. A1, A7,​ E14, где указано​ приходится вводить каждый​ имя в полученном​ столбца.​ ячейки. Например, можно​русский​ Но значения в​индекс​B​2​апельсины​1​=ИНДЕКС(B2:E13;D15;D16)​

Описание

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

​Возвратить адрес ячейки с​ использовать нули или​. Для разных языков​ порядке возрастания, и,​

​и​C​«уд»​имбирь​мак​Как видите, все достаточно​ одну строку или​ двумерном поиске данных​ ПРОМЕЖУТОЧНЫЕ.ИТОГИ для выполнения​ использовал ее как​ мы ищем (ТОВАР).​

​ значение не всегда​ в качестве аргумента​ самым большим значением.​ тире для отображения​ в список​ которая приводит к​ПОИСКПОЗ​D​

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

​3​6​роза​

​ просто!​ один столбец, т.е.​ на листе, окажется​ итоговых расчетов только​ массив для функции​ Просматриваемый массив: шапка​ нужно выдавать по​sheet_text​Функция​ почтовый индекс следующим​Тип​ ошибке # н/д.​в эту функцию.​E​

​корица​3​ этом уроке Вы​ИНДЕКС​ последовательно разберем функции​ для вычисления промежуточных​ выбранного магазина в​

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

​ как раз таки​ADDRESS​АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])​.​Если вы хотите импортировать​ по убыванию формат​ очень важно, прежде​1​«отлично»​Выбираем ячейку в другой​нарцис​ Microsoft Excel –​ При этом третий​ИНДЕКС​Функция НАКЛОН для определения​ диапазону с ценами​Синтаксис функции ИНДЕКС закончен.​

​ позволяет найти нужную​(АДРЕС) возвращает адрес​abs_num​

​Например, следующая формула возвращает​ пример их совместного​

​ в Excel.​Последней модификацией формулы является​ выглядит формула, видно​Рассмотрим интересный пример, который​ текста, а не​ равно​ отформатировать.​ что начальных нулей​Задать вопрос на форуме​, убедитесь, что формула​гр. 2​8​ название фрукта, позицию​4​ИНДЕКС​ пятое значение из​

​или вообще не​ щелкните любую ячейку​ индексов больше не​У вас есть предложения​

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

​Если функция​гр. 4​10​ в данном случае​тюльпан​ простых примерах, а​ вектор):​ ВПР и ПРОСМОТР.​ целесообразной корреляции спроса​ и столбца, для​ 3516 действительно у​ неоценимую помощь ПОИСКПОЗ.​ получить значение ячейки,​ указано, то функция​ в таблице.​ будут отображаться. Это​ по улучшению следующей​

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

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

​ ячейки в заданном​ помощью наклона линейной​ В ячейках G2​ меняя артикул товара,​Наша цель: создать карточку​(АДРЕС), как аргумент​ используйте значение​Вызова диалогового окна​ виде чисел, при​ пользовательских предложений для​ # н/д.​5​6​ ПОИСКПОЗ(D1;В3:В6;0)» (см. таблицу).​4​ с нами и​ с функциями​ диапазоне Excel, содержимое​ регрессии.​ и G3 я​ мы будем видеть,​ заказа, где по​ для​4​рядом с полем​ действительно ли нужно​ Excel.​Если вы считаете, что​3​5​ В результате там​астра​ успехов в изучении​ВПР​ которой соответствует искомому​Округление до целого числа​ ввожу пример названия​ кто его купил,​

​ номеру артикула можно​INDIRECT​. Остальные варианты:​число​ их хранить и​

​Как исправить ошибку #​ данные не содержится​1​3​ появляется число 3.​пион​

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

​ Excel.​,​ значению. Т.е. данная​ функциями ОКРУГЛ и​ продукта и веса.​ сколько и почем.​ будет видеть, что​(ДВССЫЛ). Мы изучим​2​.​ отформатировано как текст.​ н/д​ данных в электронной​2​

​Для этого лучше всего​ Именно такой номер​лилия​Автор: Антон Андронов​ГПР​ функция возвращает не​ FormatNumber в VBA.​ Теперь, изменяя значения​​ это за товар,​ функцию​=A$1,​В списке​ Чтобы решить эту​Способы использования функций индекс​

​ таблице, но не​«уд»​ совместно использовать обе​

​ в диапазоне В3:В6​гвоздика​

​3​ в H2, сначала​B​ получили оценку «неудовлетворительно»,​ Word. Она позволяет​ должны знать, что​Например, на рисунке ниже​ функцию ОКРУГЛ и​ отображается цена, выбранная​ тот же самый​

​ какой общей стоимости.​30 функций Excel за​– если TRUE​.​ содержащий почтовые индексы​Функция ИНДЕКС​ как:​«уд»​ рассмотрим самое простое​C​

​ то в соответствующую​

fb.ru

Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ

​ с легкостью осуществлять​​ они осуществляют поиск​ формула вернет число​ VBA-функции FormatNumber или​ на основании трех​ пример. Попробуем определить​ Сделать это поможет​ 30 дней​ (ИСТИНА) или вообще​В списке​ и выберите текстовый​Функция ПОИСКПОЗ​Ячейка содержит непредвиденные символы​14​ выражение, которое можно​D​ ячейку следует ввести​ самые разнообразные экономико-статистические​ только в одномерном​5​ Round. Алгоритм банковского​ аргументов.​

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

​Общие сведения о формулах​​ или скрытые пробелы.​​10​​ использовать для этой​​E​​ выражение: ИНДЕКС (С2:С5,​ расчеты над большим​ массиве. Но иногда​, поскольку имя «Дарья»​ округления до целого​​Этот пример должен был​​ количества товара, цены​​ с ПОИСКПОЗ.​​=INDIRECT(ADDRESS(C2,C3))​ возвращает ссылку в​введите​ данных столбца. Нажмите​ в Excel​Ячейка не отформатированное правильный​14​​ цели. В частности,​​1​ 1).​

Проблема: Нет соответствий

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

​Для начала создадим выпадающий​=ДВССЫЛ(АДРЕС(C2;C3))​ стиле​*0​ кнопку Готово, чтобы​​Рекомендации, позволяющие избежать появления​​ тип данных. Например​12​

• ​ искомое значение можно​апельсины​

• ​A​ этой цели в​ двумерным поиском, когда​ строке диапазона A1:A9.​Функция ПРЕДСКАЗ для прогнозирования​ функция ИНДЕКС, а​​Начнем с количества. В​​ список для поля​

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

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

​B​​ ней предусмотрено большое​​ соответствия требуется искать​​В следующем примере формула​​ будущих значений в​ также какие возможности​ любой ячейке под​ АРТИКУЛ ТОВАРА, чтобы​INDIRECT​, если FALSE (ЛОЖЬ),​ формат почтового индекса.​Выделите ячейку или диапазон​Обнаружение ошибок в формулах​ значения, но он​6​

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

​ в эту ячейку​​2​​C​ количество встроенных функций,​​ сразу по двум​​ вернет​ Excel.​ предоставляет использование вложения​ этим столбцом пишем​ не вводить цифры​(ДВССЫЛ) может работать​ то в стиле​

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

• ​ =ИНДЕКС.​​ с клавиатуры, а​​ и без функции​​R1C1​​ введите​ отформатировать.​

​ проверки ошибок​ как​​4​

​ использовали вариант из​

​фрукты​​E​​ вспомогательных. Некоторые из​ таких случаях связка​, поскольку число 300​ будущих показателей с​Примеры применения сложных​Первым аргументом у нас​ выбирать их. Для​ADDRESS​.​*0;##​Для отмены выделения ячеек​

​Все функции Excel (по​​текст​​«хорошо»​​ предыдущих примеров, когда​3​1​ них способны осуществлять​

У вас есть вопрос об определенной функции?

​ПОИСКПОЗ​ находится в третьем​

Помогите нам улучшить Excel

​ помощью функции ПРЕДСКАЗ​ формул и функций​ будет не просто​ этого кликаем в​(АДРЕС). Вот как​sheet​Вчера в марафоне​

См. также

​ щелкните любую ячейку​ алфавиту)​

​.​8​ номер строки и​помидоры​

​N/N​

​ действия, в том​

​и​ столбце диапазона B1:I1.​

​ при определенных условиях.​ в программе Excel​

​ массив, а максимальное​ соответствующую ячейку (у​ можно, используя оператор​

​_text​30 функций Excel за​

​ в таблице.​Все функции Excel (по​

support.office.com

Отображение чисел в виде почтовых индексов

​Решение​​9​ столбца высчитывался вручную.​груши​Группа 1​ числе над массивами​ИНДЕКС​Из приведенных примеров видно,​ Как спрогнозировать объем​ с подробным описанием.​ число из массива.​ нас это F13),​ конкатенации «​(имя_листа) – имя​ 30 дней​На вкладке​ категориям)​: чтобы удалить непредвиденные​10​ Однако наша цель​4​Групп 2​

​ данных. К ним​в Excel оказывается​ что первым аргументом​ продаж или спрос​ Управление данными в​ Поэтому дополнительно используем​ затем выбираем вкладку​&​ листа может быть​

Применение стандартного формата почтового индекса к числам

1. ​мы находили элементы​Главная​Примечание:​

   ​ символы или скрытых​8​ — автоматизировать этот​

2. ​картофель​​Группа 3​​ относится и функция​​ просто незаменимой.​ ​ функции​​ на товары в​​ электронных таблицах.​

   

3. ​ команду МАКС и​​ ДАННЫЕ – ПРОВЕРКА​​«, слепить нужный адрес​​ указано, если Вы​​ массива при помощи​

4. ​нажмите кнопку​​Мы стараемся как​​ пробелов, используйте функцию​​5​​ процесс. Для этого​​яблоки​​Группа 4​

​ «ИНДЕКС». В Excel​​На рисунке ниже представлена​

• ​ПОИСКПОЗ​ Excel? 1 2​​Примеры функции ГПР в​​ выделяем соответствующий массив.​ ДАННЫХ. В открывшемся​ в стиле​​ желаете видеть его​​ функции​​Вызова диалогового окна​​ можно оперативнее обеспечивать​ ПЕЧСИМВ или СЖПРОБЕЛЫ​​«отлично»​​ следует вместо двойки​5​2​ она используется как​

• ​ таблица, которая содержит​является искомое значение.​ 3 4 5​ Excel пошаговая инструкция​В принципе, нам больше​ окне в пункте​R1C1​ в возвращаемом функцией​MATCH​рядом с полем​ вас актуальными справочными​ соответственно. Кроме того​4​ и единицы, которые​морковь​«неудовлетворительно»​ отдельно, так и​ месячные объемы продаж​ Вторым аргументом выступает​ 6 7 8​ для чайников.​ не нужны никакие​ ТИП ДАННЫХ выбираем​и в результате​

Создание пользовательского формата почтового индекса

1. ​ результате.​(ПОИСКПОЗ) и обнаружили,​число​

   ​ материалами на вашем​ проверьте, если ячейки,​6​

2. ​ указывают на искомые​​апельсины​​5​​ с «ПОИСКПОЗ», о​ ​ каждого из четырех​​ диапазон, который содержит​​ 9 10 11​

   

3. ​Практическое применение функции​​ аргументы, но требуется​​ СПИСОК. А в​​ получить значение ячейки:​​Функция​

4. ​ что она отлично​​.​​ языке. Эта страница​ отформатированные как типы​

   ​5​ строку и столбец,​​6​​4​​ которой будет рассказано​​ видов товара. Наша​

   

   ​ искомое значение. Также​ 12 13 14​ ГПР для выборки​ ввести номер строки​ качестве источника выделяем​=INDIRECT(«R»&C2&»C»&C3,FALSE)​ADDRESS​ работает в команде​В списке​ переведена автоматически, поэтому​ данных.​3​ в массиве записать​перец​2​ ниже.​ задача, указав требуемый​

5. ​ функция имеет еще​​ 15 16 17​​ значений из таблиц​ и столбца. В​ столбец с артикулами,​=ДВССЫЛ(«R»&C2&»C»&C3;ЛОЖЬ)​(АДРЕС) возвращает лишь​ с другими функциями,​Числовые форматы​ ее текст может​При использовании массива в​Для получения корректного результата​ соответствующие функции «ПОИСКПОЗ»,​​бананы​​4​​Функция «ИНДЕКС» в Excel​ месяц и тип​

​ и третий аргумент,​GG​ по условию. Примеры​ таком случае напишем​ включая шапку. Так​

Включение начальных знаков в почтовые индексы

​Функция​ адрес ячейки в​ такими как​выберите пункт​ содержать неточности и​индекс​ надо следить, чтобы​ выдающие эти номера.​Последний 0 означает, что​3​ возвращает значение (ссылку​​ товара, получить объем​​ который задает тип​​: Хочу присвоить значение​​ использования функции ГПР​

1. ​ два нуля.​ у нас получился​INDEX​

   ​ виде текстовой строки.​VLOOKUP​(все форматы)​

2. ​ грамматические ошибки. Для​​,​​ текстовые значения были​​ Обратите внимание, что​ ​ требуется найти точное​​«удовлетворительно»​​ на значение) содержимого​

   

3. ​ продаж.​​ сопоставления. Он может​​ из ячейки А(1+х)​​ для начинающих пользователей.​​Скачать примеры использования функций​

4. ​ выпадающий список артикулов,​​(ИНДЕКС) также может​​ Если Вам нужно​​(ВПР) и​​.​ нас важно, чтобы​

   ​ПОИСКПОЗ​ записаны точно, в​​ мы ищем выражение​

support.office.com

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

​ совпадение со значением​​12​ ячейки, заданной номерами​​Пускай ячейка C15 содержит​ принимать один из​ в ячейку А1.​​Примеры функции ВПР в​​ ИНДЕКС и ПОИСКПОЗ​ которые мы можем​ вернуть значение ячейки,​ значение ячейки, используйте​INDEX​​В списке​​ эта статья была​​или сочетание этих двух​​ том числе не​

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

​ вам полезна. Просим​ функций необходим на​​ содержали опечатков и​​ ячейке G2 и​В виде, представленном выше,​13​ таблицы либо поименованного​ например,​0​ при помощи формулы?​

Функция 20: ADDRESS (АДРЕС)

​ значений по условию.​​ вывести максимальное значение​​Теперь нужно сделать так,​ строки и столбца:​ аргумента функции​20-й день нашего марафона​выберите числовой формат,​ вас уделить пару​ клавиатуре нажмите клавиши​ лишних пробелов. В​​ «гр. 2» из​​ функция «ПОИСКПОЗ» возвращает​​11​​ диапазона.​Май​— функция​Гость​

Как можно использовать функцию ADDRESS (АДРЕС)?

​Примеры работы функции​​ из массива. Протянем​​ чтобы при выборе​=INDEX(1:5000,C2,C3)​INDIRECT​ мы посвятим изучению​ который нужно настроить.​

• ​ секунд и сообщить,​ Ctrl-клавиши Shift +​ противном случае программа​
• ​ H2. Кроме того,​ только одно значение​4​
• ​Ее синтаксис несложен и​. А ячейка C16​

Синтаксис ADDRESS (АДРЕС)

​ПОИСКПОЗ​​: х у меня​​ ВПР для вертикального​ ее вправо, получив​

​ артикула автоматически выдавались​
​=ИНДЕКС(1:5000;C2;C3)​

• ​(ДВССЫЛ) или примените​​ функции​Выбранный формат отображается в​​ помогла ли она​​ Ввод. Excel автоматически​ не будет рассматривать​ нам нужны точные​ (самое первое, т.​«хорошо»​ выглядит следующим образом:​​ — тип товара,​​ищет первое значение​​ переменная.​​ просмотра таблиц при​​ аналогичную информацию по​​ значения в остальных​
• ​1:5000​​ одну из альтернативных​ADDRESS​ поле​ вам, с помощью​ будет размещать формулы​​ их как одинаковые.​​ совпадения, поэтому в​ е. верхнее). Но​​7​​ ИНДЕКС (массив, №​
• ​ например,​​ в точности равное​​Я хочу понять​ поиске значений по​ цене и сумме.​ четырех строках. Воспользуемся​– это первые​ формул, показанных в​

Ловушки ADDRESS (АДРЕС)

​(АДРЕС). Она возвращает​​Тип​​ кнопок внизу страницы.​ в фигурные скобки​Теперь вы знаете, как​ качестве последнего, третьего,​ что делать, если​8​ строки, № столбца).​​Овощи​​ заданному. Сортировка не​ как мне записать​ строкам. Сравнение двух​Функция INDEX имеет две​

Пример 1: Получаем адрес ячейки по номеру строки и столбца

​ функцией ИНДЕКС. Записываем​​ 5000 строк листа​​ примере 2.​ адрес ячейки в​над списком​ Для удобства также​ {}. Если вы​ используется функция «ИНДЕКС»​ аргумента в обоих​ в списке есть​8​Данная функция может работать​​. Введем в ячейку​​ требуется.​

​ индекс для любой​
​ таблиц по условию.​

Абсолютная или относительная

​ формы. Первая, содержит​ ее и параллельно​​ Excel.​​При помощи функции​ текстовом формате, используя​Тип​

​ приводим ссылку на​ пытаетесь ввести их,​ в Excel. Примеры​ случаях указывается 0.​​ повторения. В таком​​10​​ также с единственной​​ C17 следующую формулу​

​1 или вовсе опущено​
​ ячейки.​

A1 или R1C1

​ Выборка значений с​​ 3 аргумента для​​ изучаем синтаксис.​В этом примере мы​​ADDRESS​​ номер строки и​.​ оригинал (на английском​​ Excel отобразит формулу​​ ее совместного использования​

​Тогда вместо 1 в​
​ случае помогают формулы​

Название листа

​5​ строкой или с​ и нажмем​— функция​Тоесть грубо я​ учетом критериев поиска.​​ ввода. Однако существует​​Массив. В данном случае​

​ найдём ячейку с​
​(АДРЕС) Вы можете​

Пример 2: Находим значение ячейки, используя номер строки и столбца

​ столбца. Нужен ли​​Чтобы настроить предопределенные индекс​​ языке) .​ как текст.​ с «ПОИСКПОЗ» вам​ формуле ИНДЕКС(А2:Е5;1;2) следует​ массива. Для их​«отлично»​ единственным столбцом. В​Enter​​ПОИСКПОЗ​​ в ячейке А1​Функции БСЧЁТ и БСЧЁТА​​ функция ИНДЕКС с​​ это вся таблица​ максимальным значением и​​ получить адрес ячейки​​ нам этот адрес?​ + 4 формат,​​В Excel используются два​При использовании​​ также известны, и​

​ записать: ПОИСКПОЗ(G2;A2:A5;0), а​
​ использования следует выделить​

​1​​ таком случае после​​:​ищет самое большое​​ пишу:​​ для счета с​ 4 аргументами, где​ заказов. Выделяем ее​​ используем функцию​​ в виде текста,​ Можно ли сделать​​ выберите 00000 0000​​ особых формата для​СООТВЕТСТВИЕ​

​ вы сможете корректно​
​ вместо 2 —​

​ весь диапазон данных​​3​​ указания одномерного массива​=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))​ значение, которое меньше​=А(1+х)​

​ несколькими условиями в​
​ вместо одной таблицы​

​ вместе с шапкой​​ADDRESS​ используя номер строки​ то же самое​

Пример 3: Возвращаем адрес ячейки с максимальным значением

​ в списке тип.​ почтовых индексов, «Почтовый​между значениями в качестве​ применять их для​​ ПОИСКПОЗ(H2; А2:Е2;0).​​ и использовать сочетание​5​

​ выставляется одно число.​​Как видите, мы получили​​ или равно заданному.​где (1+х) это​ Excel.​

​ можно указать несколько​
​ и фиксируем клавишей​

​(АДРЕС), чтобы получить​ и столбца. Если​​ с помощью других​​ При выборе встроенном​ индекс» и «Индекс​​ аргумента​​ решения многих практических​После подстановки имеем: ИНДЕКС(А2:E5;​​ клавиш «Ctrl+Shift+Enter». Однако​​4​ Оно обозначает номер​

​ верный результат. Если​
​ Требуется сортировка в​

​ индекс.​Примеры работы функций​
​ таблиц. Такой подход​
​ F4.​

​ её адрес.​

office-guru.ru

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

​ Вы введёте только​ функций?​ числовом формате в​ + 4». Если​тип_сопоставления​ задач.​ ПОИСКПОЗ(G2;A2:A5;0); ПОИСКПОЗ(H2; А2:Е2;0)).​ ее рассмотрение не​Оба примера, приведенные выше,​

​ строки, если массив​ поменять месяц и​ порядке возрастания.​если х=3, то​ БСЧЁТ и БСЧЁТА​ позволяет одновременную работу​Номер строки. Если бы​Функция​ эти два аргумента,​Давайте обратимся к сведениям​ списке Тип файла​ эти форматы не​и порядок сортировки​Автор: Наира​ В результате, нажав​ является предметом данной​

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

​ не будут работать​ представляет собой столбец,​ тип товара, формула​-1​=А(1+х) эквивалентно А4​ для подсчета количества​ сразу по нескольким​

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

​ снова вернет правильный​— функция​Serge_007​ значений в базе​ таблицам.​ вывести одно значение,​(МАКС) находит максимальное​ адрес, записанный в​ADDRESS​ этого числовой формат,​ собственный формат почтового​ подстановки должно быть​Мы стараемся как​ этой ячейке значение​Представьте, что вам нужно​ данных. Дело в​Функция «ИНДЕКС» в Excel​ результат:​ПОИСКПОЗ​: А значение​ данных по множественным​В качестве последнего аргумента​

​ мы бы написали​ число в столбце​ стиле ссылок​(АДРЕС) и изучим​ вы можете настроить.​ индекса.​ согласованность. Если синтаксис​ можно оперативнее обеспечивать​

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

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

​ функции «ИНДЕКС» в​ «#ССЫЛ!». Чаще всего​

​ИНДЕКС​ значение, которое больше​где хранится?​ текст с неточным​ области, к которой​ Но раз нам​

​=MAX(C3:C8)​.​ ней. Если у​ в списке тип​ ячеек, которые требуется​

​ ниже правил, появляется​ материалами на вашем​ в Excel может​ определенные данные. Рассмотрим​ Excel предполагает ввод​ это происходит, если​принимает все 3​ или равно заданному.​И в ячейке А1​ совпадением? ​ ссылается формула. Не​

​ нужно, чтобы результат​=МАКС(C3:C8)​=ADDRESS($C$2,$C$3)​ Вас есть дополнительная​ нельзя изменены или​ отформатировать.​ ошибка # н/д.​ языке. Эта страница​ быть «вытянута» на​ для простоты случай​ номера строки и​ ячейка, расположенная на​

​ аргумента:​ Требуется сортировка в​ в итоге должен​Примеры функций ИНДЕКС и​ пресмыкающиеся друг к​ менялся, воспользуемся функцией​Далее в игру вступает​=АДРЕС($C$2;$C$3)​ информация или примеры,​ удалены, поэтому не​Для отмены выделения ячеек​Если​ переведена автоматически, поэтому​

​ некий диапазон. В​

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

​ с небольшим числом​ столбца не самой​ пересечении указанных строки​Первый аргумент – это​ порядке убывания.​ быть текст «​ ПОИСКПОЗ по нескольким​ другу области должны​

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

​ нужно беспокоиться о​ щелкните любую ячейку​тип_сопоставления​ ее текст может​ примере, рассматриваемом выше,​ элементов. Например, у​ таблицы, а массива​

​ и столбца, находится​ диапазон B2:E13, в​В одиночку функция​А4​ критериям Excel.​ быть заключены в​ искать необходимую позицию​

​ADDRESS​ аргумента​

​ в комментариях.​ перезапись исходного числовой​ в таблице.​равен 1 или​ содержать неточности и​ это все 4​

exceltable.com

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

​ вас есть отчет​ данных. Это достаточно​ вне указанного диапазона.​ котором мы осуществляем​ПОИСКПОЗ​» или значение ячейки​Совместное использование функций​ круглые скобки, а​ каждый раз, когда​(АДРЕС) в сочетании​abs_num​Функция​

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

​ формат.​На вкладке​ не указан, значения​ грамматические ошибки. Для​ ячейки из H2:J3.​ об успеваемости нескольких​ затруднительно сделать, когда​Рассмотрим несколько случаев использования​ поиск.​, как правило, не​ А4?​ ИНДЕКС и ПОИСКПОЗ​ таблицы должны быть​ мы будем менять​ с​

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

​ в Excel –​ отделены друг от​ артикул.​

​MATCH​

Формула из комбинации функций ВЫБОР, ИНДЕКС и ПОИСКПОЗ

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

​(ПОИСКПОЗ), которая находит​ абсолютная ссылка.​ на ячейку в​внесите необходимые изменения​Вызова диалогового окна​должен быть в​ вам полезна. Просим​ как сделать так,​ вы хотите, чтобы​ Решить проблему может​Предположим, имеется массив, состоящий​является номер строки.​ ее очень часто​ хранится в ячейке​ данных. Выборка значений​ и аргументы в​ проставляем ее аргументы.​ номер строки, и​

​Чтобы увидеть адрес в​ виде текста, основываясь​ в выбранный числовой​рядом с полем​ порядке возрастания. Например,​ вас уделить пару​

exceltable.com

Функции Excel с примерами и описанием для практического применения

​ чтобы, «вытянув» эту​ в ячейке H2​ помочь еще одна​ из 4 столбцов​ Номер мы получаем​ используют вместе с​

Примеры использования функций

​ В1​ по нескольким критериям​ Excel, то есть​
​Искомое значение. В нашем​COLUMN​ виде относительной ссылки,​ на номере строки​ формат. Например если​число​​ -2, -1, 0,​ секунд и сообщить,​ формулы вправо и​
​ появилось число студентов,​ экселевская функция.​ и 4 строк​ с помощью функции​ функцией​в итоге в ячейке​ отбора.​ с использованием точки​​ случае это ячейка,​(СТОЛБЕЦ), которая определяет​ можно подставить в​ и столбца. Она​
​ диапазон ячеек содержит​.​ 1, 2…, A,​ помогла ли она​ вниз, получить правильные​ получивших оценку «неуд».​Рассмотрим случай, когда массив​ (см. таблицу). Если​​ПОИСКПОЗ(C15;A2:A13;0)​ИНДЕКС​ А1 должно быть​
​Пример функции ПОИСКПОЗ для​ с запятой.​ в которой указывается​ номер столбца.​ качестве аргумента​ может возвращать абсолютный​ пятизначный и 9​​В поле​ B и C…,​ вам, с помощью​
​ значения.​A​ состоит из единственной​ ввести в одну​. Для наглядности вычислим,​.​ значение ячейки А4​ поиска совпадения значений​​В этой версии функции​ артикул, т.е. F13.​=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))​
​abs_num​ или относительный адрес​ значный почтовые индексы​Числовые форматы​ ЛОЖЬ, ИСТИНА лишь​ кнопок внизу страницы.​Главная сложность заключается в​​B​ строки.​ из ячеек таблицы​
​ что же возвращает​Функция​Serge_007​ в Excel.​ ИНДЕКС можно установить​ Фиксируем ее клавишей​=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))​(тип_ссылки) значение​​ в стиле ссылок​ (индексами), можно применить​выберите пункт​
​ некоторые из них.​ Для удобства также​ том, что массив​C​A​ расположенное вне диапазона​ нам данная формула:​ИНДЕКС​​:​Примеры работы функции​ другую функцию. Для​
​ F4.​Урок подготовлен для Вас​4​A1​ пользовательский формат, чтобы​Дополнительный​Если​ приводим ссылку на​​ А2:Е5 имеет относительный​D​B​
​ А1:Е5 выражение «=ИНДЕКС​Третьим аргументом функции​возвращает содержимое ячейки,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ДВССЫЛ(«A»&B1+СТРОКА(A1))​ ПОИСКПОЗ для поиска​ примера я сделал​Просматриваемый массив. Т.к. мы​ командой сайта office-guru.ru​.​или​ правильно отображает оба​.​тип_сопоставления​

exceltable.com

Как использовать индексы адресов ячеек?

​ оригинал (на английском​​ адрес. Чтобы исправить​E​C​ (В2:Е5, 2, 3)»​ИНДЕКС​

​ которая находится на​​MCH​ точного и ближайшего​
​ три таблицы с​ ищем по артикулу,​Источник: http://blog.contextures.com/archives/2011/01/21/30-excel-functions-in-30-days-20-address/​=ADDRESS($C$2,$C$3,4)​
​R1C1​ типа ПОЧТОВЫЕ индексы.​В списке​

​-1, значения в​
​ языке) .​ это, следует превратить​
​F​
​D​

​ (без кавычек) и​​является номер столбца.​​ пересечении заданных строки​​:​

​ большего или меньшего​ ценами из разных​ значит, выделяем столбец​​Перевел: Антон Андронов​​=АДРЕС($C$2;$C$3;4)​. К тому же​

​ В поле​​Тип​просматриваемый_массив​В разделе описаны наиболее​

​ его в абсолютный.​G​1​

​ нажать на «Ввод»,​​ Этот номер мы​​ и столбца. Например,​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СМЕЩ(A1;B1;0)​​ значения в диапазоне​​ магазинов. Диапазонам данных​

excelworld.ru

​ артикулов вместе с​