Перейти к контенту
Все об Экселе: формулы, полезные советы и решения
  • VBA
  • Графики
  • Инструкции
  • Макросы
  • Поиск площади
  • Формулы
  • Функции
Главная » Все статьи

5 основных функции для работы с массивами

Рубрика: Все статьи
Содержание
  1. Виды массивов функций Excel
  2. Классика жанра – товарный чек
  3. Разрешите Вас… транспонировать?
  4. Функция СТРОКА
  5. Функция СТОЛБЕЦ
  6. Функция АДРЕС
  7. Функция ДВССЫЛ
  8. Функция ВЫБОР (CHOOSE)
  9. Функция ИНДЕКС (INDEX)
  10. Функция ПОИСКПОЗ (MATCH)
  11. Редактирование формулы массива
  12. Описание категории «Ссылки и массивы»
  13. ВПР
  14. Пример применения формулы массива
  15. ГПР
  16. Функции для работы со ссылками и массивами
  17. Функция ГПР

Виды массивов функций Excel

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

В зависимости от расположения элементов различают массивы:

  • одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
  • двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

  • горизонтальными (данные – в строке);
  • вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

  • подсчитать количество знаков в определенном диапазоне;
  • суммировать только те числа, которые соответствуют заданному условию;
  • суммировать все n-ные значения в определенном диапазоне.

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

Классика жанра – товарный чек

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

  1. выделяем ячейку С7
  2. вводим с клавиатуры =СУММ(
  3. выделяем диапазон B2:B5
  4. вводим знак умножения (звездочка)
  5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
  6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter.

Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

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

  • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
  • вводим функцию транспонирования =ТРАНСП(
  • в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

Пример использования:

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

Пример использования:

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

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

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
  • стиль_ссылки – необязательный аргумент. Логическое значение:
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».
  • имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

Пример использования:

=АДРЕС=АДРЕС=АДРЕС=АДРЕС

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

  • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, “C3”, “R3C3” или “D8:D9”.
  • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
    • ИСТИНА – стиль A1. Является значением по умолчанию;
    • ЛОЖЬ – стиль R1C1.

Пример использования:

=ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
=ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

Функция ВЫБОР (CHOOSE)

Позволит вам выбрать значение из общего списка по указанному номеру позиции:

=ВЫБОР(2;”Стул”;”Стол”;”Шкаф”;”Диван”)

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

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

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

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

Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:

=ПОИСКПОЗ(B3;B2:B5;0)

С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Описание категории «Ссылки и массивы»

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

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

Принцип работы функции ВПР из категории «Ссылки и массивы»

    ВПР

    Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

    1. Вставляем функцию ВПР:

      =ВПР(A2;$E$4:$G$7;3;ЛОЖЬ)
      =VLOOKUP(A2,$E$4:$G$7,3,FALSE)

      Пояснение:

      • Функция ВПР ищет значение ID (104) в крайнем левом столбце диапазона $E$4:$G$7 и возвращает значение из третьего столбца той же строки (так как третий аргумент функции имеет значение 3).
      • Четвёртый аргумент функции равен ЛОЖЬ (FALSE) – это значит, что либо будет найдено точное совпадение, либо будет показано сообщение об ошибке #Н/Д (#N/A).
    2. Потащите мышью, чтобы скопировать функцию ВПР из ячейки B2 вниз по столбцу до ячейки B11.Пояснение: Когда мы копируем функцию ВПР вниз, абсолютная ссылка $E$4:$G$7 остаётся неизменной, в то время как относительная ссылка A2 изменяется на A3, A4, A5 и так далее.

    Пример применения формулы массива

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

    Товарный чек

    Как бы мы решали данную задачу стандартным образом?

    Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.

    Подсчет суммы заказа

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

    Расчет общей суммы покупки

    Ту же самую задачу можно решить с помощью простейшей формулы массива.

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

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

    Если сейчас нажать Enter, то появится ошибка.

    Ошибка при нажатии Enter

    Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enter и получим результат.

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

    Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…

    Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.

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

    Скобки формулы массива

    Это не текстовые скобки, то есть нельзя их ввести с клавиатуры, чтобы сделать формулу формулой массива. Они появляются автоматически при нажатии сочетания клавиш Ctrl + Shift + Enter. Если после создания формулы массива вам необходимо ее отредактировать, то в конце необходимо вновь нажать сочетание клавиш, а не просто клавишу Enter.

    ГПР

    Похожим образом работает и функция ГПР (HLOOKUP):

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

    В Excel имеется ряд функций для обработки ссылок и массивов: вычисление номеров строки или столбца таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т. д.

    Таблица 4.10. Функции для работы со ссылками и массивами.

    АДРЕС Создает адрес ячейки в виде текста, используя номер строки и номер столбца.
    ВПР Просматривает левый столбец массива в поисках определенного значения и возвращает значение из указанной ячейки.
    ВЫБОР Использует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений.
    ГИПЕРССЫЛКА Создание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet.
    ГПР Просматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки.
    двссыл Возвращает ссылку, заданную аргументом ссылка на ячейку.
    ИНДЕКС Выбирает по индексу значение из ссылки или массива.
    ОБЛАСТИ Возвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка.
    ПОИСКПОЗ Возвращает относительную позицию элемента массива.
    ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ Получение данных сводной таблицы
    ПРОСМОТР Ищет значения в векторе или массиве.
    СМЕЩ Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов.
    СТОЛБЕЦ Возвращает номер столбца по заданной ссылке.
    СТРОКА Возвращает номер строки, определяемой ссылкой.
    ТРАНШ Возвращает транспонированный массив.
    ЧИСЛСТОЛБ Возвращает количество столбцов в ссылке или массиве.
    ЧСТРОК Возвращает количество строк в ссылке или массиве.

    Функция ГПР

    Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

    Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

    Источники


    • https://exceltable.com/master-klass/massivy-funktciy-excel
    • https://www.planetaexcel.ru/techniques/2/91/
    • https://office-menu.ru/uroki-excel/13-uverennoe-ispolzovanie-excel/45-funktsii-ssylok-i-massivov-excel
    • https://topexcel.ru/5-osnovnyx-funkcii-dlya-raboty-s-massivami/
    • https://msoffice-prowork.com/ref/excel/excelfunc/lookup-reference/
    • https://office-guru.ru/excel/funkcii-excel-dlja-raboty-so-ssylkami-i-massivami-515.html
    • https://zen.yandex.ru/media/id/5c4d8a530aaeef00ac928ac3/5cb46bff23aed400b46a0c52
    • http://samoychiteli.ru/document37861.html

    Смотрите также:

    1. Как использовать Мастер функций для создания формул в Excel
    2. Функция ВЕЙБУЛЛ для расчета распределения Вейбулла в Excel
    3. Функция СУММЕСЛИМН() Сложение с несколькими критериями в EXCEL (Часть 2
    4. Функция РАБДЕНЬ() в EXCEL
    Рейтинг
    ( Пока оценок нет )
    Понравилась статья? Поделиться с друзьями:
    Вам также может быть интересно
    Все статьи
    Функция ЧИСТРАБДНИ() – подсчет рабочих дней в EXCEL
    Все статьи
    Округление числа до требуемой точности (заданного количества значащих цифр)
    Все статьи
    Квантили распределений EXCEL
    Все статьи
    Рабочая книга в Excel
    Все статьи
    Как создать шаблон диаграммы (графика) и использовать его в Excel
    Все статьи
    Групповое изменение числовых значений в EXCEL
    Все об Экселе: формулы, полезные советы и решения
    © 2023 Все об Экселе: формулы, полезные советы и решения