Что такое массивы и формулы массива?
Доброе время суток всем! В текущем уроке мы рассмотрим работу с таким удобным инструментом Excel, как массивы и формулы массивов. Что же это такое и как с этим работать? Если говорить официально, то массив это диапазон, содержащий данные одного типа. Массив обрабатывается как одно целое. Именно поэтому поменять формулу только для части массива невозможно. Почему это удобно и как с этим работать? Давайте разберемся на практике. Прежде всего стоит заметить, что те ячейки с определенной структурой и границами, которые мы гордо называем таблицами – это не таблицы. Почему? Потому что данные в таблице связаны между собой. Точно так же, как номер телефона в телефонной книге привязан к определенному абоненту. Для простоты, чтобы проще различать таблицы и диапазоны между собой, в русской версии Excel настоящие таблицы называются умными.
Диапазоны же – это области из ячеек, то есть пересечений строк и столбцов на листе, обычно расположенные рядом. Диапазоны могут быть из нескольких строк и столбцов, а могут содержать только одну строку или один столбец. Такие диапазоны, состоящие только из ячеек одной стоки или столбца, называются векторами.
Диапазоны могут хранить данные разного типа. В Excel это могут быть числа, текст, логические данные, дата, время, ссылки на объекты. Вектор ячеек, в котором находятся данные произвольного типа, одинакового или нет – неважно, называют списком. А вот любой диапазон, в том числе и вектор, содержащий данные одного типа, называют массивом. В чем его польза и преимущество? В том, что если тип данных одинаковый, то для них можно использовать одинаковые формулы. Массив можно рассматривать как единое целое, состоящее из отдельных элементов. Поэтому любую формулу можно применять сразу ко всему массиву, не используя ее по отдельности для каждой ячейки. Именно такая формула, связанная с массивом в целом, а не с отдельной его частью, называется формула массива. Приведем примеры использования формул массива для решения практичеких задач.
Простой пример использования массива для получения общего итога.
Приведем простой вариант использования в Excel массива. Это, который можно назвать классическим. Посмотрите на следующее изображение фрагмента листа:
Необходимо найти итоговую сумму. Обратите внимание, что графы, в которой бы содержалось значение суммы построчно для каждой позиции нет. Конечно, мы можем с помощью формулы посчитать общую стоимость для каждой строки в отдельном столбце, а затем применить к полученным результатам автосумму. Все это не будет ошибкой, и именно такие действия применят большинство из сотрудников организации.
Однако давайте внимательно посмотрим на итоговую формулу. В скобках указан диапазон, значения ячеек которого просуммированы. Что же находится в этих ячейках? Очевидно, что результаты умножения ячеек в текущей строке из столбцов «В» и «С» с ценой и количеством соответственно.
А теперь подумаем вот о чем. Диапазон с ценами – это массив? Безусловно, там же только числа. Аналогично и диапазон с количеством также является массивом. А если это массивы, то вместо того, чтобы попарно перемножать значения цены и находящегося в той же строки количества, можно применить Функцию «СУММ» непосредственно к произведению этих массивов!
В результате вместо трех действий – написание формулы, копирование ее вниз, применение новой формулы – мы использовали только одну формулу с тем же результатом. Мы сделали ту же работу, но гораздо быстрее.
Обратите внимание на фигурные скобки в строке формул. Они говорят о том, что перед нами не просто формула, а формула массива. Для ее ввода одновременно нажимаем комбинацию клавиш «Ctrl + Shift + Enter». Если просто нажать»Enter» то программа решит, что мы вводим обычную формулу. Она просто не поймет, как можно в ней ОДНОВРЕМЕННО перемножать несколько ячеек и даст ошибку. Поэтому будьте внимательны!
Применение массива для подсчета отработанных часов.
Рассмотрим на примере, как используется формула массива в типичной для бухгалтеров ситуации по начислению заработной платы. Мы применяем для начисления почасовую оплату труда. Раньше, что греха таить, всем ставили одинаковые часы. Главное, чтобы сотрудник на работу пришел, а 8 отработанных часов в табеле будут в любом случае.
Сейчас же во многих организациях стоит специальный турникет. При входе и выходе мы прикладываем свою карточку, а система сама считает время, в течении которого мы находились на рабочем месте. Проблема в том, что система показывает время работы в часах и минутах, а начисление происходит именно за часы.
В качестве варианта можно поступить таким образом. Вначале извлечь и просуммировать только часы. Затем извлечь и просуммировать минуты. Минуты превратить в часы, поделив полученное значение на 60. Наконец, сложить начальную сумму часов с суммой часов, полученных из минут. Все хорошо, но только пока вы высчитываете часы максимум для десяти человек. А если их хотя бы 100? Причем в реальности вариантов гораздо больше. Это и срок доставки, когда мы оплачиваем за каждый час работы курьера. Это и время перевозок. Примеров много. Как же быть? На помощь опять приходит формула массива. В ней мы сделаем все этапы, перечисленные выше, вместе, применив для расчета функции ЧАС, МИНУТЫ И СУММ.
ЧАС – извлекает из указанного времени значение часа. В частности, формула =ЧАС(«10:52») даст результат 10.
МИНУТЫ – аналогична предыдущей, но извлекает соответственно минуты. Таким образом, формула =МИНУТЫ(«10:52») даст результат 52. Кстати, обратите внимание на кавычки в формуле. Они нужны, так как время указано явно, а не получено из ячейки.
СУММ – суммирует данные.
В итоге для данного примера получаем комбинацию:
{=СУММ(ЧАС(D2:D6))+СУММ(МИНУТЫ(D2:D6))/60}
Не забываем про нажатие в конце сочетания клавиш «Ctrl + Shift + Enter».
В ячейке D7 содержится уже общая сумма отработанных часов с учетом минут. Осталось его умножить на почасовую ставку, и все готово.
Нахождение минимального, максимального и среднего значения при помощи формулы массива.
В следующем примере мы с помощью формулы массива получим среднюю, минимальную и максимальную цену по каждому товару. Причем, если среднюю цену можно найти с помощью встроенной функции СРЗНАЧЕСЛИ, то функций МИНЕСЛИ и МАКСЕСЛИ не предусмотрено.
Чтобы вычислить значение минимальной цены, надо указать программе, что надо находить минимальное значение не для всего диапазона, а только для тех ячеек с ценой, которые соответствуют ячейкам с нужным наименованием. Проверить их можно с помощью сравнения по очереди ячеек наименования с эталонным на данный момент. И если очередное наименование совпадает с нужным нам, то для него начать высчитывать среднее значение. Получаем следующую формулу:
{=СРЗНАЧ(ЕСЛИ($A$2:$A$17=F2;$B$2:$B$17))}
Формула расшифровывается так: если в диапазоне $A$2:$A$17 значение очередной ячейки совпадает с значением в ячейке F2, то для соответствующих ячеек из диапазона $B$2:$B$17 вычислить среднее значение. Excel загрузит таблицу, уберет из нее все строки, которые не содержат нужное нам наименование. После этого по оставшимся строкам в цене – а $B$2:$B$17 это диапазон с ценами – вычисляется среднее значение.
Если теперь отфильтровать наименования, выбрав только «Стол» и вычислить среднее значение по отображаемым ячейкам с помощью стандартных функций СРЗНАЧ или СРЗНАЧЕСЛИ, то закономерно выходит тот же результат.
Это значит, что формула написана верно. Поменяв в ней СРЗНАЧ на МИН, мы можем найти минимальное значение цены для выбранного товара, поменяв на МАКС – максимальное значение цены, ну и так далее.
В примере ниже для адреса ячейки с наименование товара применена смешанная адресация с закреплением только столбца. Такой трюк позволил вначале протянуть полученную формулу вправо, после чего поменять в ней вариант вычисления на нужный именно в текущем столбце. К примеру, для минимального использовалась функция МИН, а для максимального значения – функция МАКС. Затем все результаты выбрали вместе и протянули вниз.
С помощью фактически ОДНОЙ ФОРМУЛЫ получили несколько РАЗНЫХ ВАРИАНТОВ итоговых значений. Это и минимум, и максимум, и среднее. Удобно? Конечно!
В новом примере с помощью аналогичной формулы были найдены максимальные списания процентов по кредиту. С учетом того, что проценты для клиента отрицательны, то использовалась функция МИН, а не МАКС, как кто-то, может быть, ожидал. Обратите внимание, что указывать нужное наименование полностью в этой формуле можно, а вот использовать подстановочные знаки, увы, нельзя.
Выбор данных с помощью ВПР с учетом двух и более критериев.
Наконец, разберем более сложный вариант с применением формул массива. Имеются следующие данные.
Необходимо найти цену на хлеб в марте. Можно было бы применить сразу ВПР, но – позиций с хлебом несколько, как и позиций с мартом. Как быть? На помощь снова приходит формула массива.
Еще раз обращаю внимание на схожесть формулы с той, которую разбирали в предыдущей части. Конструкция ЕСЛИ($A$2:$A$19=F2;$B$2:$D$19) позволила отсечь строки в диапазоне ;$B$2:$D$19 , у которых месяц совпадал с указанным в ячейке F2, то есть – мартом. Из оставшихся данных был создан виртуальный диапазон, в котором и сработала функция ВПР. Формулу можно было бы и усложнить, например, получив цену по хлебу не только в определенном месяце, но и по определенном городе.
Заключение.
Таким образом, мы убедились, что применение формул массивов позволяют значительно быстрее мол учить нужные данные за счет обработки массива ячеек как единого целого. Формулы массива позволяют более гибки выполнять различные расчеты.
Однако не стоит забывать, что такие формулы применяют тогда, когда надо стразу применить один и тот же расчет к множеству данных ОДНОГО типа. Если значения в диапазоне разного типа, то формула может и не сработать. В то же время — это мощное оружие в руках пользователя, и применять его для решения мелких задач все равно что из пушки стрелять по воробьям. Кроме этого, не забывайте, что для ввода формул массива обязательно требуется комбинация «Ctrl + Shift + Enter».
С другой стороны, знание и умение применять такие формулы сильно облегчает работу, о чем тоже не надо забывать.
На этом наш урок завершен. Всем хорошего дня и успехов в работе! Если вас заинтересовал материал, то встретимся на занятиях в нашем учебном центре для еще более углубленного обучения возможностям Excel на практике.
