Глава 6. Использование массива, как аргумента функции

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава     Оглавление     Следующая глава

Ранее в этой книге вы познакомились с операторами массивов: математическими, сравнения и конкатенации. В этой главе рассматриваются операции с массивами, выступающими аргументами функций. Для начала задумайтесь, как работает аргумент функции одного элемента. Например, функция ДЛСТР подсчитывает количество символов в ячейке (рис. 6.1). Текстовый аргумент функция ДЛСТР ожидает, что в него будет помещен один элемент. Благодаря этому функция ДЛСТР обеспечивает однозначный ответ – 15 символов в ячейке А3.

Рис. 6.1. Текст в ячейке A3 – это один элемент, который помещен в текстовый аргумент

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Что произойдет, если вы поместите в текстовый аргумент функции ДЛСТР более одного элемента? Допустим, ваша цель – подсчитать общее число символов в диапазоне А2:А6 (рис. 6.2). Попробуйте ввести A2:A6 в качестве аргумента функции ДЛСТР. И формула =ДЛСТР(A2:A6) и вариант формулы массива {=ДЛСТР(A2:A6)} дадут ответ 15. Не работает!

Рис. 6.2. Попытка ввести массив в аргумент функции не увенчается успехом, даже если нажмете Ctrl+ Shift+Enter

Если вы выделите диапазон A2:A6 (как на рис. 6.2) и нажмете F9, то увидите, что аргумент функции ДЛСТР содержит пять различных текстовых строк (рис. 6.3). (Не забудьте отменить расчет, нажав Ctrl+Z, прежде чем двигаться дальше.)

Рис. 6.3. Аргумент функции ДЛСТР содержит пять текстовых строк

Примечание: текстовая строка обозначает последовательность символов (ноль или более символов; позже вы узнаете, что значит строка текста с нулем символов), которые Excel распознает, как текст, а не как числа или логические значения.

Если вы выделите всю функцию ДЛСТР и нажмете F9, то увидите, что функция возвращает массив из пяти значений (рис. 6.4). Это означает, что если вы введете в аргумент функции ДЛСТР более одного элемента, вы получите как бы несколько ответов.

Рис. 6.4. Функция ДЛСТР возвращает пять ответов, если ее текстовый аргумент содержит пять элементов; выделите формулу целиком (слева) и нажмите F9 (справа).

Итак:

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

Если функция ДЛСТР возвращает массив чисел, вы легко можете их суммировать с помощью функции СУММПРОИЗВ (рис. 6.5). При вводе формулы, вы не должны использовать Ctrl+Shift+Enter, поскольку аргумент функции СУММПРОИЗВ изначально был запрограммирован для обработки массивов.

Рис. 6.5. Поместите функцию ДЛСТР в качестве аргумента функции СУММПРОМЗВ

Окончательный результат – 69 (рис. 6.6); видно, что фигурные скобки в строке формул отсутствуют.

Рис. 6.6. Формула массива не требует Ctrl+Shift+Enter

Перейдем к следующему примеру. Допустим, ваши исходные данные содержат объем продаж различных товаров (диапазон А3:С7 на рис. 6.7). Цель – подсчитать суммарные продажи, затраты и прибыль. Если затраты на производство товаров содержатся в отдельной таблице (F3:G6), вы можете в основной таблице создать вспомогательный столбец (D3:D7), и с помощью функции ВПР извлечь данные о затратах. Далее останется просуммировать продажи, затраты и вычислить прибыль (ячейки В9:В11).

Рис. 6.7. Решение с помощью вспомогательного столбца и функции ВПР

Существует ли возможность обойтись без вспомогательного столбца и решить задачу с помощью формулы в одной ячейке? У вас может возникнуть соблазн разместить массив в аргументе искомое_значение функции ВПР (рис. 6.8). К сожалению, этот аргумент не запрограммирован на операции с массивами (функция ГПР также не умеет этого). Выделите формулу целиком и нажмите F9. Видно, что ВПР вернула не массив, а одно число.

Рис. 6.8 Выделите формулу целиком (слева) и нажмите F9 (справа)

Попробуем вместо ВПР использовать СУММЕСЛИ (рис. 6.9). Обычно аргумент критерий функции СУММЕСЛИ состоит из одного элемента. Мы же «подсунули» ему пять элементов. В итоги СУММЕСЛИ вернет также пять ответов (рис. 6.10). Выделите целиком функцию СУММЕСЛИ и нажмите F9, чтобы увидеть эти пять элементов. Заметим, что функция СУММЕСЛИ будет выдавать неверные значения, если в диапазоне Е4:Е6 будут дубли. Аргумент критерий может иметь дубли, а вот аргумент диапазон – нет.

Рис. 6.9. Поместим в аргумент критерий функции СУММЕСЛИ массив значений

Рис. 6.10. Выделите функцию СУММЕСЛИ (слева) и нажмите F9 (справа)

Так как нам требуется просуммировать элементы, которые нам вернула функция СУММЕСЛИ, поместите эту последнюю в качестве аргумента функции СУММПРОИЗВ (рис. 6.11).

Рис. 6.11. СУММПРОИЗВ совместно с СУММЕСЛИ

Если товары в справочной таблице отсортированы по алфавиту, формула становится еще проще. Вместо СУММЕСЛИ воспользуйтесь функцией ПРОСМОТР (рис. 6.12). К тому же конструкция СУММПРОИЗВ + ПРОСМОТР работает быстрее, чем СУММПРОИЗВ + СУММЕСЛИ.

Рис. 6.12. Если таблица отсортирована по возрастанию, проще использовать функцию ПРОСМОТР


Прокомментировать