Массив — это данные, сгруппированные в группу. В данном случае группа представляет собой массив функций в Excel. Любую таблицу, которую мы создаем и заполняем в Excel, можно назвать матрицей. Пример:
Матрицы классифицируются по расположению элементов:
- одномерные (данные находятся в ОДНОЙ строке или ОДНОМ столбце);
- двумерный (НЕСКОЛЬКО строк и столбцов, матрица).
Одномерные массивы:
- горизонтальные (данные — на одной строке);
- вертикальный (данные — в столбце).
Примечание. Двумерные массивы Excel могут охватывать сразу несколько листов (это сотни и тысячи данных).
Формула массива — позволяет обрабатывать данные из этого массива. Он может возвращать одно значение или возвращать массив (набор) значений.
С формулами массива это реально:
- подсчитать количество символов в определенном диапазоне;
- добавлять только те числа, которые удовлетворяют заданному условию;
- суммирует все n-е значения в определенном диапазоне.
Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.
Классика жанра – товарный чек
Задача: рассчитать общую сумму заказа. Если вы выберете классический метод, вам нужно будет добавить столбец, в котором вы умножаете цену и количество, а затем берете сумму из этого столбца. Если применить формулу массива, все будет гораздо красивее:
- выберите ячейку C7
- введите с клавиатуры =СУММ(
- выберите диапазон B2:B5
- введите знак умножения (звездочка)
- выберите диапазон C2:C5 и закройте скобки функции СУММ; в итоге должно получиться так:
- чтобы Excel воспринял нашу формулу как формулу массива, нажимаем не Enter, как обычно, а Ctrl+Shift+Enter
Привет!
Те. Excel произвел попарное перемножение элементов массивов B2:B5 и C2:C5 и сформировал новый стоимостной массив (в памяти компьютера), а затем сложил все элементы этого нового массива.
Обратите внимание на фигурные скобки в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они появляются автоматически при нажатии Ctrl+Shift+Enter.
Разрешите Вас… транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е перевернуть таблицу на бок, чтобы данные, которые раньше шли в строке, теперь оказались в столбцах и наоборот. В математике эта операция называется транспонированием. Используя формулу массива и функцию ТРАНСП, это делается за один раз.
Допустим, у нас есть двумерный массив ячеек, которые мы хотим транспонировать.
- Выберите диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек состоял из 8 строк и 2 столбцов, нам нужно выбрать диапазон пустых ячеек размером 2 строки и 8 столбцов.
- введите функцию транспонирования =TRANSP(
- в качестве аргумента функции выбираем наш массив ячеек A1:B8
нажимаем Ctrl+Shift+Enter и получаем в результате «перевернутый массив:
Функция СТРОКА
Получает и возвращает номер строки указанной ячейки.
Синтаксис: =STRING([ссылка]), где аргумент ссылки является необязательным. Если опущено, возвращается текущий номер строки.
Пример использования:
=СТРОКА(D4) — результат 4.
=СТРОКА() — функция вернет номер строки, в которой она находится.
Функция СТОЛБЕЦ
Возвращает номер столбца ячейки, указанной ссылкой.
Синтаксис: =COLUMN([ссылка]), где «ссылка» не является обязательной. По умолчанию возвращается номер текущего столбца.
Пример использования:
=СТОЛБН(C4) — формула вернет значение 3.
=COLUMN(): Функция возвращает номер столбца, в котором он находится.
Функция АДРЕС
Возвращает текст, представляющий адрес ячейки, заданный номерами строки и столбца.
Синтаксис: =АДРЕС(строка, столбец, [тип_вывода], [стиль_ссылки], [имя_листа]), где:
- Строка является обязательным аргументом. Число, представляющее номер строки, для которой вы хотите вернуть адрес;
- Столбец является обязательным аргументом. Число, представляющее номер столбца целевой ячейки.
- anchor_type — необязательный аргумент. Число от 1 до 4, указывающее якорь индексов ссылок:
- 1 — значение по умолчанию, когда все индексы закреплены;
- 2 — исправить индекс строки;
- 3 — фиксация индекса столбца;
- 4 — адрес без пинов.
- link_style — необязательный аргумент. Логическое значение:
- TRUE — формат ссылок «А1»;
- FALSE — Формат ссылки «R1C1».
- имя_листа — необязательный аргумент. Строка, представляющая имя листа, которое будет добавлено к тексту адреса ячейки.
Пример использования:
=АДРЕС>(1,1)>=АДРЕС>(1,1,4)>=АДРЕС>(1,1,4,ЛОЖЬ)>=АДРЕС>(1,1,4,ЛОЖЬ,»Лист1″)>
Функция ДВССЫЛ
Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на заданный адрес.
Синтаксис: =ДВССЫЛ(адрес_ссылки, [стиль_ссылки]), где
- url_url — обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9”.
- link_style — необязательный аргумент. Логическое значение, определяющее стиль ссылки:
- ИСТИНА — стиль A1. Это значение по умолчанию;
- ЛОЖЬ — стиль R1C1.
Пример использования:
=ДВССЫЛ(«a3») — возвращает ссылку на ячейку A3.
=ДВССЫЛ(«r3c3») вернет #ССЫЛКА! потому что текст ссылки имеет формат R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(«r3c3»; ЛОЖЬ) — возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3),СТОЛБЦ(C3))) — функция вернет результат, аналогичный предыдущему примеру.
Вложение функции ДВССЫЛ со ссылкой на диапазон:
Функция ВЫБОР (CHOOSE)
Позволяет выбрать значение из общего списка по указанному номеру позиции:
=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван”)
Функция ИНДЕКС (INDEX)
Эта функция возвращает указанное значение из одно- или двумерного диапазона:
=ИНДЕКС(A1:C6,4,3)
Как видно из примера, результирующее значение 37 в указанном диапазоне находится на пересечении строки №4 и столбца №3 в диапазоне A1:C6, указанном в формуле. На более простом примере показано, как в диапазоне C1:C6 значение 15 стоит на 2-м месте:
=ИНДЕКС(C1:C6,2)
Функция ПОИСКПОЗ (MATCH)
Эта функция вернет позицию искомого значения в указанном диапазоне:
=ПОИСКПОЗ(B3,B2:B5,0)
В примере видно, что слово «Таблица» занимает вторую позицию в указанном диапазоне. Обратите внимание, что третий аргумент функции является необязательным. Если введено значение 0, функция вернет позицию элемента в массиве, которая точно соответствует искомому значению. В случае отсутствия точного совпадения функция выдаст ошибку #Н/Д (#Н/Д).
Редактирование формулы массива
Если формула массива находится не в одной ячейке (как в примере 1), а в нескольких ячейках (как в примере 2), Excel не позволит вам редактировать или удалить одну формулу (например, в ячейке D10) и будет отображать предупреждающее сообщение.
Чтобы отредактировать формулу массива, выделите весь диапазон (в нашем случае A10:H11) и измените формулу в строке формул (или нажав F2). Затем нужно повторить ввод измененной формулы массива, нажав комбинацию клавиш Ctrl+Shift+Enter.
Excel также не позволит вам свободно перемещать ячейки, которые являются частью формулы массива, или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е в диапазон A10:H11 в нашем случае)
Описание категории «Ссылки и массивы»
Категория функций Links and Arrays, другое название категории Display Functions, используется для поиска значений в списках или таблицах, манипулирования диапазоном данных, а также получения дополнительной информации, такой как информация о количестве строк или столбцы в диапазоне
Ярким представителем функций этой категории является наиболее популярная функция ВПР, которая позволяет найти значение в ячейке диапазона путем поиска в первом столбце того же диапазона
Как работает функция ВПР категории «Ссылки и исправления
ВПР
Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки в указанном столбце той же строки.
- Вставьте функцию ВПР:
=ВПР(A2,$E$4:$G$7,3,ЛОЖЬ)
=ВПР(A2,$E$4:$G$7,3,ЛОЖЬ)
Объяснение:
- Функция ВПР ищет идентификатор значения (104) в крайнем левом столбце диапазона $E$4:$G$7 и возвращает значение в третьем столбце той же строки (поскольку третий аргумент функции равен 3).
- Четвертый аргумент функции — ЛОЖЬ, что означает, что либо будет найдено точное совпадение, либо будет показано сообщение об ошибке #Н/Д).
- Перетащите, чтобы скопировать ВПР из ячейки B2 вниз по столбцу в ячейку B11.
Объяснение: Когда мы копируем вниз функцию ВПР, абсолютная ссылка $E$4:$G$7 остается неизменной, а относительная ссылка A2 меняется на A3, A4, A5 и т д.
Пример применения формулы массива
Самая простая задача, чтобы проиллюстрировать силу формулы массива, — это простой товарный чек. То есть у нас есть наименование товара, его количество и цена за единицу. При оформлении заказа мы должны получить общую сумму покупки.
Квитанция о покупке
Как бы мы решили эту задачу стандартным способом?
Во-первых, мы получили бы общее количество каждого продукта, умножив количество продукта на его цену.
Расчет суммы заказа
Ну а потом добавили бы расписку суммы для получения конечной. То есть нам понадобится дополнительный столбец для промежуточных вычислений.
Расчет общей суммы покупки
Та же проблема может быть решена с помощью более простой формулы массива.
Нам нужно получить сумму, поэтому воспользуемся соответствующей функцией СУММ. Но нам нужно просуммировать произведение цены товара на его количество, что мы и сделаем: выделим диапазон значений капитала В и умножим его на такой же диапазон значений в столбце С.
Формула массива
Если вы нажмете Enter сейчас, появится ошибка.
Ошибка при нажатии Enter
Поскольку в формуле используются диапазоны (массивы) данных, формула также должна быть формулой массива. Для этого нажмите комбинацию клавиш Ctrl+Shift+Enter и получите результат.
Формула массива
Что произошло и как работает формула массива? Это важно понимать, потому что в будущем можно будет применять матричные формулы для решения гораздо более сложных задач…
Итак, при вычислении формулы массива Excel сделал попарное умножение значений в диапазонах B2:B5 и C2:C5. В результате получился массив значений, который был просуммирован в соответствующей функции. Получаем только одно значение общей суммы без вспомогательных вычислений.
Посмотрите на формулу в строке формул. Он заключен в квадратные скобки, что говорит нам о том, что мы имеем дело с формулой массива.
Скобки формулы массива
Это не текстовые скобки, что означает, что вы не можете ввести их на клавиатуре, чтобы преобразовать формулу в формулу массива. Они появляются автоматически при нажатии сочетания клавиш Ctrl+Shift+Enter. Если после создания формулы массива вам нужно ее отредактировать, то в конце нужно еще раз нажать сочетание клавиш, а не только клавишу Enter.
ГПР
Аналогично работает функция ГПР):
Функции для работы со ссылками и массивами
В Excel есть ряд функций для работы со ссылками и массивами: вычисление номеров строк или столбцов таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т д
Таблица 4.10. Функции для работы со ссылками и массивами.
| НАПРАВЛЕНИЕ | Создайте адрес ячейки в виде текста, используя номер строки и номер столбца. |
| ПОИСКV | Выполняет поиск определенного значения в левом столбце массива и возвращает значение указанной ячейки. |
| ВЫБОР | Использует номер индекса для выбора и возврата значения из списка аргументов значения. |
| ГИПЕРССЫЛКА | Создайте ссылку, которая открывает документ, расположенный на жестком диске, сетевом сервере или в Интернете. |
| Георадар | Выполняет поиск определенного значения в верхней строке массива и возвращает значение указанной ячейки. |
| двссыл | Возвращает ссылку, предоставленную аргументом ссылки на ячейку. |
| ПОКАЗАТЕЛЬ | Выберите по индексу значение из ссылки или массива. |
| ОБЛАСТИ | Возвращает количество регионов в ссылке. Область — это диапазон соседних ячеек или одна ячейка. |
| СООТВЕТСТВОВАТЬ | Возвращает относительную позицию элемента массива. |
| ДЛЯ ПОЛУЧЕНИЯ ДАННЫХ. ТАБЛИЦА РЕЗУЛЬТАТОВ | Получение данных сводной таблицы |
| ПОСМОТРЕТЬ | Ищите значения в векторе или матрице. |
| КОМПЕНСИРОВАТЬ | Возвращает ссылку указанной высоты и ширины, отделенную от другой ссылки указанным количеством строк и столбцов. |
| СТОЛБЕЦ | Возвращает номер столбца данной ссылки. |
| ЛИНИЯ | Возвращает номер строки, указанный ссылкой. |
| РАЗДЕЛ | Возвращает матрицу транспонирования. |
| НОМЕР СТОЛБЦА | Возвращает количество столбцов в ссылке или массиве. |
| Удары | Возвращает количество строк в ссылке или массиве. |
Функция ГПР
Первое горизонтальное равенство. Находит совпадение ключа в первой строке указанного диапазона и возвращает значение указанной строки этого диапазона в столбце, соответствующем ключу.
Синтаксис: =HLOOKUP(ключ; диапазон; номер_строки; [диапазон_поиска]).
Поскольку эта функция аналогична функции ВПР, за исключением того, что она использует горизонтальные таблицы вместо вертикальных, описания и примеры использования также подходят для ВПР с учетом отмеченных различий.