Excel. Некоторые примеры использования формул массива

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

Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

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

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

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

Cкачать заметку в формате Word, примеры в формате Excel

Создание массивов на основе значений ячеек диапазона

На рис. 1 показан рабочий лист, содержащий данные в диапазоне A1:C4. Диапазон D8:F11 содержит массив, созданный на основе этих данных с помощью формулы {=A1:C4}

Рис. 1. Создание массива на основе значений ячеек диапазона

Массив в диапазоне D8:F11 связан со значениями диапазона A1:C4. Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве D8:F11.

Создание массива констант на основе значений диапазона ячеек

В предыдущем примере формула массива в ячейках D8:F11, по существу, являлась ссылкой на диапазон A1:C4. Чтобы «разорвать» эту связь и создать массив констант, выполните ряд действий.

  1. Выделите диапазон, содержащий формулу массива (в этом примере – D8:F11).
  2. Нажмите клавишу <F2>, чтобы перейти в режим редактирования формулы.
  3. Нажмите клавишу <F9>, чтобы преобразовать ссылки на ячейки в соответствующие значения.
  4. Нажмите комбинацию клавиш <Ctrl+Shift+Enter>.

Теперь в диапазоне D8:F11 находится следующий массив: {1;"кот";3:4;5;"собака":7;89;9,12: "обезьяна";11;44}. На рис. 2 показано, как этот массив выглядит в строке формул.

Рис. 2. После нажатия клавиши <F9> в строке формул отобразится массив констант

Выполнение операций над массивами

Следующая формула массива создает прямоугольный массив и умножает каждый его элемент на 2: {={1;2;3;4:5;6;7;8:9;10;11;12}*2} (рис. 3)

Рис. 3. Результат умножения массива на 2

Следующая формула умножает каждый элемент массива на самого себя: {={1;2;3;4:5;6;7;8:9;10;11;12}*{1;2;3;4:5;6;7;8:9;10;11;12)}. Эту формулу можно переписать в более компактном виде: {={1;2;3;4:5;6;7;8:9;10;11;12}^2}. Если массив хранится в диапазоне ячеек (допустим, А1:С4), то подобная формула возвратит квадрат каждого элемента этого массива: {=А1:С4^2} (рис. 4).

Рис. 4. Возведение значений массива в квадрат

Применение функций к массивам

В операциях над массивами можно использовать функции. Следующая формула массива, которую нужно ввести в вертикальный диапазон, состоящий из 10 ячеек, вычисляет квадратные корни каждого элемента массива: {=КОРЕНЬ({1:2:3:4:5:6:7:8:9:10))}. Если массив хранится в диапазоне ячеек (например, А1:А10), подобная формула выполнит эти же вычисления для каждого элемента массива: {=КОРЕНЬ(А1:А10)} (рис. 5).

Рис. 5. Извлечение квадратного корня из элементов массива

Транспонирование массивов

При транспонировании массива его строки становятся столбцами, а столбцы — строками. Если массив одномерный, то при транспонировании горизонтальный массив становится вертикальным и наоборот. Для выполнения операции транспонирования используется функция Excel ТРАНСП. Допустим, имеется одномерный горизонтальный массив {1; 2; 3; 4; 5}, расположенный в диапазоне А1:Е1. С помощью функции ТРАНСП можно преобразовать его в вертикальный массив. Для этого выделите вертикальный диапазон, состоящий из пяти ячеек, введите формулу =ТРАНСП(А1:Е1) и нажмите комбинацию клавиш <Ctrl+Shift+Enter> (рис. 6).

Рис. 6. Транспонирование одномерного массива

Транспонирование двухмерных массивов выполняется подобным образом. На рис. 7 показан двухмерный массив в диапазоне A1:D3. Формула{=TPAHCП(A1:D3)} создает в диапазоне F1:H4 транспонированный массив.

Рис. 7. Транспонирование двумерного массива

Генерирование последовательности натуральных чисел

С помощью формул массивов легко генерировать последовательности натуральных чисел. Для этого идеально подходит функция СТРОКА, возвращающая номер строки. Рассмотрим формулу массива, введенную в вертикальный диапазон, состоящий из 12 ячеек: {=СТРОКА(1:12)}. Эта формула возвращает массив из 12 элементов, содержащий числа от 1 до 12 (рис. 8). Отметим, что данная формула возвращает правильный результат, независимо от того, где расположен диапазон, в который вводится эта формула.

Рис. 8. Последовательности натуральных чисел на основе функции СТРОКА

Если вы поэкспериментируете с приведенной формулой, то обнаружите присущий ей недостаток: при добавлении новой строки выше диапазона, где расположена эта формула, Excel изменит номера строк, и формула автоматически преобразуется в формулу, возвращающую значения от 2 до 13: {=СТРОКА(2:13)} (рис. 9).

Рис. 9. Добавление строки над массивом изменяет ряд чисел

Приведем формулу массива, которая лишена этого недостатка: {=СТРОКА(ДВССЫЛ("1:12"))}. Эта формула использует функцию ДВССЫЛ, аргументом которой является текстовая строка. Excel не изменяет этот аргумент при вставке или удалении строк на рабочем листе, поэтому данная формула всегда возвращает правильный результат, состоящий из чисел от 1 до 12 (рис. 10).

Рис. 10. Последовательность натуральных чисел на основе функции ДВССЫЛ не меняется при добавлении строк над массивом

Функции, возвращающие массив. Результатом выполнения некоторых функций Excel является массив. Чтобы такие функции возвращали правильный результат, выделите диапазон, введите формулу, содержащую одну из этих функций, как формулу массива. Вот несколько функций, которые возвращают массив: ПРЕДСКАЗ, ЧАСТОТА, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ, МОБР, МУМНОЖ и ТЕНДЕНЦИЯ. Дополнительную информацию об этих функциях можно найти в справочной системе Excel.

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

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

Подсчет количества символов в диапазоне

Если необходимо подсчитать общее количество символов в определенном диапазоне, то традиционный метод выполнения этой задачи состоит в том, чтобы найти количество символов в каждой ячейке диапазона, например, с помощью формулы =ДЛСТР(А1), и затем суммировать эти значения (рис. 11). Формула массива {=СУММ(ДЛСТР(А1:А10))} выполняет эти вычисления без промежуточных формул. Эта формула с помощью функции ДЛСТР создает виртуальный массив в памяти компьютера, содержащий количество символов каждой ячейки диапазона, а затем суммирует элементы виртуального массива.

Рис. 11. Одна формула массива заменяет целый ряд отдельных формул

Суммирование трех наименьших значений диапазона

Предположим, что имеется диапазон чисел, названный Данные, и вы хотите узнать сумму трех наименьших чисел этого диапазона. Формула массива решает эту задачу: {=СУММ(НАИМЕНЬШИЙ(Данные;{1;2;3}))} (рис. 12). Здесь в качестве второго аргумента в функции НАИМЕНЬШИЙ используется массив констант. Это вынуждает Excel сгенерировать виртуальный массив, содержащий три наименьших значения из диапазона Данные. Затем к этому виртуальному массиву применяется функция СУММ, которая суммирует его элементы и возвращает искомый результат.

Рис. 12. Формула массива возвращает сумму трех наименьших значений поименованного диапазона Данные (A1:А10)

Подсчет количества ячеек, содержащих текст

В следующей формуле функция ЕСЛИ используется для проверки содержимого каждой ячейки диапазона. Затем создается виртуальный массив (того же размера, что и исходный), в котором содержатся числа 1 (если в ячейке находится текст) и 0 (в противном случае). Новый массив передается в функцию СУММ, которая возвращает сумму элементов массива. В итоге получаем количество ячеек, содержащих текст: {=СУММ(ЕСЛИ(ЕТЕКСТ(А1:D5);1;0))}. На рис. 13 показан пример использования этой формулы в ячейке С8. В результате выполнения функции ЕСЛИ создается виртуальным массив {0;1;1;1:1;0;0;0:1;0;0;0:1;0;0;0:1;0;0;0}. Обратите внимание на то, что виртуальный массив состоит из пяти строк, содержащих по четыре элемента (т.е. тот же размер, что и исходный массив). Функция СУММ суммирует значения этого виртуального массива.

Рис. 13. Формула массива возвращает число ячеек диапазона, содержащих текст

Следующая формула выполняет туже работу, но записана в более компактном виде {=СУММ(ЕТЕКСТ(A1:D5)*1)}. Здесь вместо функции ЕСЛИ используется тот факт, что ИСТИНА*1=1, а ЛОЖЬ*1=0

Исключение промежуточных формул

Одно из достоинств формул массивов заключается в том, что они позволяют исключать промежуточные формулы (и соответственно результаты промежуточных вычислений) с рабочих листов. Это делает таблицы на рабочих листах более компактными и понятными. На рис. 14 показан рабочий лист, содержащий баллы студентов до и после выполнения тестов. В столбце D содержатся формулы, вычисляющие разность между этими баллами, в ячейке D12 – формула, вычисляющая среднее значение этих разностей: =CPЗHAЧ(D2:D11).

Рис. 14. Вычисление среднего с помощью промежуточных формул и с помощью одной формулы массива

С помощью следующей формулы массива, записанной в ячейке С15, можно исключить промежуточные вычисления в столбце D: {=СРЗНАЧ(С2:С11-В2:В11)}. В этой формуле используются два массива, содержащиеся в диапазонах С2:С11 и В2:В11. Формула создает виртуальный массив, состоящий из разностей значений этих двух массивов. Этот виртуальный массив хранится в памяти компьютера, а не в рабочем листе Excel. Функция СРЗНАЧ использует этот виртуальный массив в качестве своего аргумента и возвращает среднее его элементов. Виртуальный массив имеет вид {27;18;18;10;3;8;0;17;8;-8}. Поэтому в данном случае предыдущая формула принимает вид =СРЗНАЧ({27;18;18;10;3;8;0;17;8;-8})

Аналогично создаются и работают формулы массивов, вычисляющие различные характеристики диапазонов данных. Например, следующая формула находит максимальное число среди разностей значений диапазонов С2:С11 и В2:В11: {=МАКС(C2:C11-B2:B11)}. Эта формула вернет число 27. А эта формула {=МИН(C2:C11-B2:B11)} возвращает наименьшее число разностей значений двух диапазонов. Формула вернет число -8.

Использование массивов вместо ссылок на диапазоны

Если в формулах используются ссылки на диапазоны, то их можно заменить на массивы констант. Это очень удобно, если значения в диапазонах, на которые указывают ссылки, не будут изменяться. [1]

На рис. 15 представлена таблица, в которой производится поиск названия заданного числа. Например, если 9 – искомое значение, то формула вернет слово Девять. В ячейку С1 введена следующая формула: =ВПР(В1;D1:Е10;2;ЛОЖЬ). Вместо ссылки на таблицу можно использовать двухмерный массив. Формула, приведенная ниже, работает аналогично предыдущей, но не использует таблицу, которая находится в диапазоне D1:E10.

=ВПР(В1;{1;"Один":2;"Два":3;"Три":4; "Четыре":5;"Пять":6;"Шесть": 7;"Семь": 8; "Восемь":9;"Девять":10;"Десять"};2;ЛОЖЬ)

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

Если вас заинтересовали формулы массива, рекомендую книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel


[1] К сожалению, в функциях, работающих с базами данных (например, БДСУММ), нельзя заменить ссылку на диапазон, содержащий критерии, массивом констант.

Комментарии: 6 комментариев

Возможно ли проделать операции над отдельными элементами массива? Например, есть виртуальный массив СУММПРОИЗВ(A1:A5;B1:B5;C1:C5). Надо, например, первые три элемента виртуального массива удалить или умножить на 0, остальные просуммировать.

Алексей, насколько я понимаю, формулы массива работают именно с массивами. Для обработки отдельных элементов массива используются, например, функции ИНДЕКС и ПОИСПОЗ. В вашем случае можно, например, добавить пятый столбец, содержащий нули и единицы, и использовать формулу =СУММПРОИЗВ(A1:A5;B1:B5;C1:C5;D1:D5).

Пример есть пример и в реальности, увы, ещё один столбец добавить не получится. Я пробовал использовать в ИНДЕКС(СУММПРОИЗВ();x), но функция сначала считает итоговое значение СУММПРОИЗВ(). Получается массив из одного значения.

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

Наименование кол во цена итого
Яблоки кг 3
Груши кг 2
Огурцы кг 1
Помидоры кг 5

Итого

Заранее благодарю.

=июнь!D15 Подскажите, что это за формула, не могу разобрать


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