Функция индекс в excel

Функция ИНДЕКС() в MS EXCEL

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

Синтаксис функции

​сами значения​​MonthList​

​Введите номер строки, и​​column_num​COLUMNS​

​ и т.д. Но,​​ целой строки (не​ с другими операторами.​В поле​ с​ Отдельно у нас​указываем значение​

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

​ функция​(номер_столбца), то массив​(ЧИСЛСТОЛБ) – для​ записав формулу ввиде:​ всего столбца/строки листа,​ Созданные таким способом​«Номер области»​«Парфенов Д.Ф.»​

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

Значение из заданной строки диапазона

​=СУММ(A2:ИНДЕКС(A2:A10;4))​​ а только столбца/строки​

​ формулы смогут решать​ставим цифру​, на, например,​ поля –​, так как нужно​

​«Ссылка»​ них, если массив​ усвоить материал.​​СТОЛБЕЦ($A$2:$D$9)​​Необходимо выполнить поиск значения​ текстовыми строками и​INDEX​(ИНДЕКС) возвратит сумму​

Значение из заданной строки и столбца таблицы

​ столбца.​ столбца в именованном​​получим универсальное решение, в​

​ входящего в массив).​ самые сложные задачи.​«3»​«Попова М. Д.»​«Имя»​ узнать данные из​. Нужный нам вариант​

Использование функции в формулах массива

​и​​ третьей строки. Поле​​«Массив»​ диапазоне следует применять​ (не табличный) подход.​​ время параллельно, в​​ отобразить заголовок столбца​ ячеек. Если в​​ сочетании с несколькими​​ строки. Здесь указан​row_num​вместе с​​ только последний аргумент​​ значений, введите функцию​

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

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

​ в третьей таблице,​ платы в поле​. Нужно сделать так,​

​вообще можно оставить​ и по умолчанию​

ПОИСКПОЗ() + ИНДЕКС()

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

​(ПОИСКПОЗ) – для​ выше вместо 4​ массива.​ из диапазона ячеек​ в которой содержится​​«Сумма»​

​ что при введении​ пустым, так как​ выделен. Поэтому нам​ под номером строки​ что-то искать в​ (тех же размеров,​ выглядеть следующим образом:​ функция возвращает одно​ отсортированный в алфавитном​, поэтому результатом будет​column_num​ поиска имени участника,​ ввести 5, то​Пусть имеется одностолбцовый диапазон​ по номеру строки​

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

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

​ остается просто нажать​ и столбца понимается​​ Excel, моментально на​​ что и размеры​Небольшое упражнение, которое показывает,​ из этих значений​ порядке. Функция​ сумма продаж за​(номер_столбца).​ угадавшего ближайшее значение.​

​ будет подсчитана сумма​

​А6:А9.​ и столбца. Например,​ плате за третий​Теперь посмотрим, как с​ отображалась сумма заработанных​ диапазон, в котором​ на кнопку​ не номер на​

​ ум приходит пара​ нашей исходной таблицы),​ как в Excelе​ в зависимости от​COUNTIF​ апрель (Apr).​Если аргумент​Итак, давайте обратимся к​ первых 5-и значений). ​

​Выведем 3 первых​ формула =ИНДЕКС(A9:A12;2) вернет​

​ месяц.​

​ помощью оператора​ им денег. Посмотрим,​

​ используется только один​«OK»​ координатах листа, а​

​ функций: ИНДЕКС и​ содержащая номера столбцов​ можно добиться одинакового​ индекса, а если​(СЧЁТЕСЛИ) подсчитывает, как​

​=INDEX($C$2:$C$8,F2)​column_num​ теоретическим сведениям и​Использование функции ИНДЕКС() в этом​​ значения из этого​​ значение из ячейки​После того, как все​ИНДЕКС​ как это можно​ столбец. Жмем на​.​ порядок внутри самого​ ПОИСКПОЗ. Результат поиска​ для каждой из​

excel2.ru>

10 ответов

Originally posted by Namik Доброго Вам времени суток Уважаемые господа просмотрите пожалуйста нижеследующий код

———————————————— Dim s As Integer s = ActiveCell.Characters.Count With ActiveCell.Characters(Start:=s, Length:=l).Font .Superscript = True End With End Sub

этот код позволяет мне перевести в верхний индекс последний символ в выделенной ячейке а мне необхадимо чтобы в верхний индекс переводился символ который я выделю

как мне этого добиться помогите пожалуйста

С Уважением и Благодарностью Намик

А что значит “выделю”? Ты не сможешь запустить макрос, если находишься в состоянии правки ячейки.

А что значит “выделю”? Ты не сможешь запустить макрос, если находишься в состоянии правки ячейки.

Да Уважаемый Вы правы я не правильно выразился

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

можете Вы мне помочь в этом ?

С Уважением и Благодарностью Намик

P.S. а каже тогда это реализуется в Excel? ведь там мы выделяем символ в ячейке (ячейка становится редактируемой) щелкаем правой кнопкой мыши выбираем формат ячейки и т.д.

Да Уважаемый Вы правы я не правильно выразился

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

можете Вы мне помочь в этом ?

С Уважением и Благодарностью Намик

P.S. а каже тогда это реализуется в Excel? ведь там мы выделяем символ в ячейке (ячейка становится редактируемой) щелкаем правой кнопкой мыши выбираем формат ячейки и т.д.

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

Ты лучше скажи, зачем тебе это нужно? Наверняка существует более простой путь.

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

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

Функция ВПР()

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; ). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

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

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

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; ).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; ).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

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

Функции ИНДЕКС и ПОИСКПОЗ в 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.

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) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

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

​ использовать функцию ВПР.​ которая приводит к​СООТВЕТСТВИЕ​ поиска значения «Воронеж»​ 2345768, но вы​ с номерами рабочего​ ПОИСКПОЗ, ВПР, ГПР,​.​ этом случае используется​ парашютом, имеет семеро​A35:B38​ помощью формулы =ИНДЕКС(A6:A9;2)​3438​09.04.12​

​ из меньших значений:​ нет, возвращается сообщение​ и посмотрите, какие​=ВПР(B2;C2:E7,3,ИСТИНА)​ ошибке # н/д.​, возможно, так​ в диапазоне B1:B11​ перепутали две цифры​​ листа Excel и​​ ПРОСМОТР, с которыми​Один из весьма распространенных​ следующий синтаксис:​ детей и черный​Связка ПОИСКПОЗ() + ИНДЕКС()​Если диапазон горизонтальный (расположен​Нижний Новгород​=»Орел = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),1)& «,​ «Оси» (в столбце​

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

​ об ошибке.​ из них подходящий​​В этом примере B2​​Решение:​ как:​ будет использоваться функция​ и ввели их​ не обязаны соответствовать​ может прекрасно сочетаться​ на практике сценариев​=ИНДЕКС((Диапазон1;Диапазон2;Диапазон3); Номер_строки; Номер_столбца; Номер_диапазона)​

​ пояс в шахматах,​

​ даже гибче, чем​ в одной строке,​02.05.12​ Дата выставления счета:​​ A).​​#Н/Д​ вариант.​​ — это первый​​Измените аргумента​Ячейка содержит непредвиденные символы​ ПОИСКПОЗ. Оно найдено​ в формулу следующим​ им.​​ в сложных формулах.​​ применения​

​Обратите особое внимание, что​ да и, вообще,​ функция ВПР(), т.к.​ например,​3471​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),3),»m/d/yy»)​

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

​5​=ВПР(0,1,A2:C10,2,ИСТИНА)​Скопируйте следующие данные в​аргумент​тип_сопоставления​ или скрытые пробелы.​

​ в строке 4.​

​ образом:​GP75​ Но все же​ИНДЕКС​ в этом случае​

​ добрейшей души человек​ с помощью ее​​А6:D6​​Нижний Новгород​​3154​​=ГПР(«Болты»;A1:C4;4)​Используя приблизительное соответствие, функция​

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

​ пустой лист.​— элемент данных, функция​1 или сортировка​​Ячейка не отформатированное правильный​​ Затем функция ИНДЕКС​=ВПР​: Проблема в использовании​ гибкость и простота​в таком варианте​ первый аргумент –​

​ и умница?​

​ можно, например, определить​), то формула для​04.05.12​Казань​Поиск слова «Болты» в​ ищет в столбце​Совет:​ должна работать. Функции​

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

​ по убыванию формат​ тип данных. Например​​ использует это значение​​(2345678;A1:E7;5)​ функций ИНДЕКС и​ в этой функции​ — это сочетание​ список диапазонов -​Так и в Microsoft​ товар с заданной​ вывода значения из​3160​

​11.04.12​​ строке 1 и​​ A значение 0,1.​    Прежде чем вставлять​ ВПР это первый​​ таблицы. Затем повторите​​ ячейка содержит числовые​ в качестве аргумента​. Формула возвращает цену​ ПОИСКПОЗ на двух​​ на первом месте.​​ с функцией​​ заключается в скобки,​​ Excel: есть несколько​

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

​ ценой (обратная задача,​ 2-го столбца будет​Москва​=»Челябинск = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),1)& «,​​ возврат значения из​​ Поскольку 0,1 меньше​​ данные в Excel,​​ аргумент — значение,​ попытку.​ значения, но он​​ поиска и находит​​ на другую деталь,​ листах. В прикреплении​Допустим мы работаем с​​СЧЁТЗ (COUNTA)​​ а сами диапазоны​ похожих функций, про​ так называемый «левый​ выглядеть так =ИНДЕКС(A6:D6;;2)​18.04.12​ Дата выставления счета:​ строки 4, находящейся​ наименьшего значения в​ установите для столбцов​​ которое требуется найти.​​Задать вопрос на форуме​

​ может быть отформатированные​ численность населения Воронежа​​ потому что функция​​ файл. Уважаемые знатоки​ большой таблицей данных​​, чтобы получить автоматически​​ перечисляются через точку​​ которых фраза «внешность​​ ВПР()»). Формула =ИНДЕКС(A35:A38;ПОИСКПОЗ(200;B35:B38;0))​Пусть имеется таблица в​3328​​ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),3),»m/d/yy»)​​ в том же​ столбце A, возвращается​ A – С​ Этот аргумент может​ сообщества, посвященном Excel​ как​ в четвертом столбце​ ВПР нашла ближайшее​​ EXCEL посоветуйте, как​​ с множеством строк​

​ растягивающиеся диапазоны для​ с запятой.​ обманчива» работает на​​ определяет товар с​​ диапазоне​Москва​3191​​ столбце (столбец C).​​ сообщение об ошибке.​ ширину в 250​ быть ссылка на​У вас есть предложения​

planetaexcel.ru>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Здравствуйте друзья!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Почему функция не работает

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

Нужно точное совпадение

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

Поэтому если требуется уникальное значение, то нужно обязательно указывать последний аргумент со значением ЛОЖЬ.

Необходима фиксация ссылок на таблицу

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

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

Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать

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

19

Чтобы решить эту проблему, достаточно просто нажать на клавишу F4, чтобы зафиксировать адрес ссылки.

Простыми словами, формула должна обрести следующий вид.

=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

Вставлена колонка

Для чего нужен аргумент «номер столбца»? Для того, чтобы задать функции, какие именно данные должны быть извлечены. 

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

20

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

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

Увеличение размеров таблицы

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

21

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

Функция не умеет анализировать данные слева

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

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

Дублирование данных

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

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

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

Предположим у нас есть таблица данных с продуктами:

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