Как извлечь значение из массива excel
При работе с данными время от времени возникают ситуации, когда нам необходимо найти значение ячейки на пересечении определенных строки и столбца.
В данной статье я Вам расскажу о трёх способах осуществления этой задачи.
1. ИНДЕКС (INDEX)
Данная функция позволяет извлечь значение из диапазона ячеек по номеру строки и столбца.
Аргументы функции выглядят следующим образом:
Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;
Номер_строки – строка, из которой нужно извлечь значения;
Номер_столбца – столбец, из которого нужно извлечь значение.
Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности.
Схематически работу функции можно изобразить так:
Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).
В таком случае формула выглядит примерно так:
=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))
При смене параметров поиска, значение пересекающейся ячейки будет изменяться автоматически.
2. СУММПРОИЗВ (SUMPRODUCT)
Очень интересная функция, которой можно найти много применений. Она перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.
Для решения нашей задачи формула будет выглядеть так:
=СУММПРОИЗВ((C6:F6=I6)*(B7:B9=I5)*(C7:F9))
Простыми словами синтаксис функции можно представить так:
=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))
Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.
3. ДВССЫЛ (INDIRECT)
Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).
Функция ДВССЫЛ возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого. Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы.
Если на первый взгляд функция выглядит простой и малополезной, то при дальнейшем её изучении Вы узнаете, что с её помощью мы можем перемещаться по листам, транспонировать таблицы, отбирать чётные (нечётные) строки и многое другое. Ведь для нее аргумент Ссылка_на_ячейку – всего лишь текстовая строка, которую можно изменять формулами.
Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.
Для начала нам необходимо создать диапазоны для строк и столбцов.
Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3
Аналогичную операцию проделываем и для строк.
Сама формула будет иметь такой вид:
=ДВССЫЛ(I5) ДВССЫЛ(I6)
Обратите внимание что функции разделены пробелом.
Excel даёт нам возможность использовать несколько вариантов решения для одной задачи. Там, где невозможно применить один способ, практически всегда есть возможность подобрать другой. Настоятельно рекомендую Вам изучить функции, рассмотренные в данной статье и Ваша работа с данными, станет намного эффективнее.
Автор: Михаил Беленчук, тренер DATA bi
Как извлечь значение из массива excel
Здравствуйте
Сломал себе голову уже.
Ищем значение G4 в столбце B:B
Если в столбце B:B найдено значение G4 подставить в ячейку H4 все уникальные данные из столбца C:C для значения G4
В H4 подставлять только уникальные данные из столбца C:C без повторений
Если за одно действие не получается то за несколько действий
Заранее спасибо за подробный ответ.
Прикрепленные файлы
- образец.xlsx (9.24 КБ)
Пользователь
Сообщений: 14615 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
08.12.2016 19:42:47
| Цитата |
|---|
| zimbura написал: за подробный ответ |
пример не глянул. Максимально подходящее под пример решение — на VBA: Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли
Если VBA недопустимо, то сначала это: ВПР с возвратом всех значений
а далее сцепка всех значений. В 2013 и выше это можно сделать при помощи функции ОБЪЕДИНИТЬ. В ранних версиях только через танцы с бубном.
Изменено: The_Prist — 09.12.2016 02:05:19
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.
ИНДЕКС (функция ИНДЕКС)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.
Функцию ИНДЕКС можно использовать двумя способами:
- Если вы хотите возвращать значение указанной ячейки или массива ячеек, см. раздел Форма массива.
- Если требуется возвращать ссылку на указанные ячейки, см. раздел Ссылочная форма.
Форма массива
Описание
Возвращает значение элемента в таблице или массиве, выбранное по индексам номеров строк и столбцов.
Если первый аргумент функции ИНДЕКС является константной массива, используйте форму массива.
Синтаксис
ИНДЕКС(массив; номер_строки; [номер_столбца])
Форма массива функции INDEX имеет следующие аргументы:
- массив. Обязательный аргумент. Диапазон ячеек или константа массива.
- Если массив содержит только одну строку или столбец, соответствующий аргумент номер_строки или номер_столбца является необязательным.
- Если массив содержит более одной строки и более одного столбца и используется только номер_строки или номер_столбца, ИНДЕКС возвращает массив всей строки или столбца в массиве.
Замечания
- Если используются аргументы номер_строки и номер_столбца, функция ИНДЕКС возвращает значение в ячейке на пересечении номеров_строки и номера_столбца.
- row_num и column_num должны указывать на ячейку в массиве; в противном случае ИНДЕКС возвращает #ССЫЛКА! ошибку «#ЗНАЧ!».
- Если задать для row_num или column_num значение 0 (ноль), ИНДЕКС возвращает массив значений для всего столбца или строки соответственно. Чтобы использовать значения, возвращаемые в виде массива, введите функцию ИНДЕКС как формулу массива.
Примечание: Если у вас есть текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите CTRL+SHIFT+ENTER для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Примеры
Пример 1
В этих примерах функция ИНДЕКС используется для поиска значения ячейки, находящейся на пересечении заданных строки и столбца.
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.
Формулы массива в Excel
Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов: Формулы массива в Excel — это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории — те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах.
Пример 1. Классика жанра — товарный чек
- выделяем ячейку С7
- вводим с клавиатуры =СУММ(
- выделяем диапазон B2:B5
- вводим знак умножения (звездочка)
- выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter.
Пример 2. Разрешите Вас. транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.
Допустим, имеем двумерный массив ячеек, который хотим транспонировать.
- Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
- вводим функцию транспонирования =ТРАНСП(
- в качестве аргумента функции выделяем наш массив ячеек A1:B8
жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:
Редактирование формулы массива
Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.
Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Пример 3. Таблица умножения
Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:
При помощи формул массива она вся делается в одно движение:
- выделяем диапазон B2:K11
- вводим формулу =A2:A11*B1:K1
- жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива
и получаем результат:
Пример 4. Выборочное суммирование
Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:
В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.
Ссылки по теме
- Формула массива для извлечения непустых ячеек из диапазона
- Формула массива для извлечения уникальных ячеек из диапазона
- Формула массива для извлечения данных из списка (многоразовый ВПР)