Использование формул массивов в Excel
Категория: Учебник, просмотров: 74122, опубликовано: 08.03.2007 Скачать пример |
Терминология
Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:
Формулы массива в Excel - это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории - те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах...
Пример 1. Классика жанра - товарный чек
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
- выделяем ячейку С7
- вводим с клавиатуры =СУММ(
- выделяем диапазон B2:B5
- вводим знак умножения (звездочка)
- выделяем диапазон C2:C5 и закрываем скобку функции СУММ - в итоге должно получиться так:
- чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter
Вуаля!
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Обратите внимание на фигурные скобки, появившиеся в формуле - отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно - они автоматически появляются при нажатии Ctrl + Shift + Enter.
Пример 2. Разрешите Вас... транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП это делается на раз.
Допустим, имеем следующий двумерный массив ячеек, который хотим транспонировать:
Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
вводим функцию транспонирования =ТРАНСП(
в качестве аргумента функции выделяем наш массив ячеек A1:B8
жмем Ctrl + Shift + Enter и получаем "перевернутый массив" в качестве результата:
Редактирование формулы массива
Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение:
Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Пример 3. Таблица умножения
Вспомните детство, школу, свою тетрадку по математике... На обороте тетради на обложке было что? Таблица умножения вот такого вида:
При помощи формул массива она вся делается в одно движение:
- выделяем диапазон B2:K11
- вводим формулу =A2:A11*B1:K1
- жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива
и получаем результат:
Пример 4. Выборочное суммирование
Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:
В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.
Комментарии:
| Алексей 04.11.2008 |
Все гениальное -просто! =))). |
| Сергей 07.11.2008 |
Для редактирования формулы массива возвращающей значения в диапазон ячеек, нет необходимости выделять весь диапазон. Достаточно редактировать на месте (в ячейке) с помощью двойного щелчка. 1)указываем мышью на любую ячейку формулы массива 2)двойной щелчок левой кнопкой мыши, активизирует текущую ячейку и показывает формулу массива 3)редактируем формулу 4)нажимаем Ctrl + Shift + Enter формула изменена и выделенные после ввода ячейки сответсуют диапазону вывода формулы массива.. |
| Сергей 07.11.2008 |
или воспользоваться быстрыми клавишами 1)Встать на ячейку массива 2)Нажатием CTRL+/ выделить весь массив 3)изменить формулу в строке формул 4)нажав сочетание клавиш Ctrl + Shift + Enter, ввести новую формулу . |
| Василий 25.11.2008 |
Спасибо за механнику массива, до этого голову ломал! Вопрос! Я ввел массив в бланк (таблицу), но в бланке в ячеках временно не вписанны цифры, соответственно в поле отображается -0- Как оставив массив в ячейке, заставить её не отображать цифру -0-?. |
| Сева 30.01.2009 |
Пример 4 часто использую, но хотел знать как "расшифровать" сумму по входящим в нее значениям, например с помощью проставления в столбце E какого-нибудь значения?. |
| Игорёк 19.02.2009 |
Вмонтируй функцию ЕСЛИ,а вообще массивы использую два года, а мой коллега всё на VBA пишет, он безнадежно отстал. Прикольно о массивах написано Уокенбахом, у него целая глава "Магия формул массивов". Я вам скажу реальная магия, изучайте и пользуйтесь этим чудом . |
| Александр 23.07.2009 |
Спасибо большое за разъяснение, до этого использовал индексы. У меня вопрос по 4 примеру, а можно ли добавить дополнительное условие для распознания сумм заказов с учетом даты продажи (от и до) и можно ли это использовать функцию СЧЕТ, т.е. нужно определить и сумму и количество заказов. . |
| Александр 23.07.2009 |
Разобрался по своему вопросу, на счет добавления в Примере 4 проверки даты, путем добавления 2 дополнительных условий < и > необходимой даты, а результат количества операций можно сделать путем добавления дополнительного столбца со значением 1 и та же формула только суммирует значение в столбце с 1.. |
| Александр 05.10.2009 |
Прекрасный сайт, очень мне помогает в работе. А подскажите, пожалуйста как вычислить среднее значение по 3 условиям?. |
| Алексей 24.10.2009 |
А как мне сделать,если массив большой и часть формулы просто не обрабатывается?. |
| Вася 15.01.2010 |
умные люди подскажите пожалуйста как мне сделать, чтобы из таблицы (пример) яблоки покупные 100 груши покупные 50 персики покупные 30 яблоки собственные 200 груши собственные 100 персики собственные 50 яблоки привезенные 60 груши привезенные 50 персики привезенные 40 вытянуть значение к примеру груш собственных = 100. Никак формула не хочет вытягивать - пытался и ВПР-ом и ЕСЛИ и массивы использовал - никак не вытягивает((( . |
| kэt 21.01.2010 |
какое элегантное решение! столько времени ломала голову, как решить задачу суммирования значений в строках таблицы при выполнении одновременно двух и более условий, а все оказалось так изящно :) . |
| Владимир 24.01.2010 |
Да на таких вариантах всё понятно!!! А вот моя задача(ломаю голову уже оч. долго) Есть 1ый лист на нём показана динамика развития в таблице: месяца 1 2 3 4 5 кол. раб. 1 1 1 2 2 (к примеру) а/м выручка 10 10 10 20 20 расходы 5 5 10 10 10 (на производство+закупку техники) дозакупка 0 0 1 2 2 ( дозакупка единиц техники) а/м прибыль 5 5 0 10 10 а на втором листе в подробностях: Месяц единица стоимость прибыль дозакупки закупки с даной единицы 1 комбаин 5 10 1 самосвал 5 5 2 комбаин 5 10 А на 3ем затраты на з/п, топливо, рем. Как я могу вывести суммы расходов (стоим. закупки+з/п+топливо), за определённый месяц на первый лист? И как вывести единцы закупаемой техники в цифрах(кол-во шт.) в строку дозакупка? заранее благодарен!!! . |
| Andrey 04.03.2010 |
Как я могу вывести суммы расходов (стоим. закупки+з/п+топливо), за определённый месяц на первый лист? И как вывести единцы закупаемой техники в цифрах(кол-во шт.) в строку дозакупка? заранее благодарен!!! _______________________________________________________________ Массивом является диапазон ячеек, соответственно ему можно присвоить имя, которое станет в конечном итоге гиперссылкой на диапазон, делается это так же, как и с именами ячеек. Соответственно, если правильно структурировать базу входящих данных и присвоить каждому диапазону данных соответствующее имя, то в вормуле массива можно указать не явную ссылку на диапазон формата <A1:A200> а гиперссылку на массив данных, не зависимо от того на каком листе, и в каком файле данный массив храниться. Желаю удачи.. |
| Andrey 04.03.2010 |
Так же необходимо отметить, что при выделении массива данных, в момент формирования формулы массива, необходимо выделять именно ячейки, т.е. ссылка на диапазон должна быть именно вида <A1:B4> т.к. формула массива не умеет корректно читать ссылки вида <D:D> или <4:4>, которые получаются при выделении строки или столбца целиком.. |
| serega 22.03.2010 |
Доброго времени суток! Оч. Порадовал пример №4, существенно облегчил жизнь))), Осталась еще одна не решенная мной проблема, прошу совета, кто чем может) В общем есть 2у мерный массив данных со списком контрагентов и помесячной отгрузкой. С помощью фильтра «содержит»-«Метеор», т.к. одномерный вертикальный массив с наименованием контрагентов имеет вид: "Азиенда Агрикола Силла Доменико Танча МетеорООО" "Анекуп Вина Тендида МетеорООО" "Бушон Резерва МетеорООО" "Бушон Резерва МАНКЕЙ ООО" "Вердье Анжуйские вина Сладкий дворООО" "Вердье Кюве Спесьаль Сладкий дворООО" "Вино Зупа Плодовые вина 1л МетеорООО" "Вино Зупа Сортовые вина 1л МетеорООО" "Вино Зупа Традиционные сербские вина 1л МетеорООО" "Группо-Колтива Белла Тавола (столовые вина) МетеорООО" "Долня Баня Акура 0.75л МетеорООО" "ЕКВВК ""Арарат"" Ной Арас пел РомашкаООО" "ЕКВВК ""Арарат"" Ной Араспел МетеорООО" "ЕКВВК ""Арарат"" Ной Классик РомашкаООО" …. выдергиваю отгрузки и заношу в таблицу. Ищу способ этот процесс автоматизировать. Ранее с формулами массива не работал, рад что на них наткнулся))) Спасибо! . |
| Татьяна 28.03.2010 |
Моя задача такая же, как пример №2. Выполняю все как указано в примере. Но вместо значений в перевернутом массиве, получаю только формулы. Что я делаю не так? Посоветуйте, как решить задачу!. |
| Павел 29.03.2010 |
что то ни как не разберусь как например в первой таблице при выборе в отдельных ячейках года и месяца, в третьей ячейке появлялось значение пересечения этих колонок например: при выборе ячейка 1: год 2001 ячейка 2: месяц май ячейка 3: $140025 (это значение должно появлятся) ??????. |
| Андрей 14.04.2010 |
После ввода формулы как в Примере 4 у меня Excel выдает #Н/Д или #ЗНАЧ! данные введены... что может быть не могу понять ??!!. |
| Sergant 17.04.2010 |
Обратите внимание на фигурные скобки, появившиеся в формуле - отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно - они автоматически появляются при нажатии Ctrl + Shift + Enter. . |
| Toni 02.05.2010 |
Нелзя ли (и, если можно, то как!?) использовать диапазон ячеек (или ссылки на отдельные ячейки) в качестве аргумента(тов) массива? Ищу более изящное решение для формулы вида: =ЕСЛИ(И(B5="";C5="";D5="";E5="");"";1) Хотелось бы заменить на что-то типа: =ЕСЛИ(B5:E5="";"";1) Но, понятно, в таком виде не работает. Буду весьма признателен за помощь . |
| Toni 02.05.2010 |
Хм... кажется предыдущий вопрос снимается... Сейчас ввел формулу, как формулу массива, и все заработало... Т.е. вот так работает: {=ЕСЛИ(B5:E5="";"";1)} Ну, век живи... Извиняюсь за беспокойство, Спасибо.. |
| Toni 03.05.2010 |
Эх... поторопился обрадоваться. Не работает оно так. Т.е. (реагирует исключительно на первую ячейку диапазона. В данном примере, только, если B5 заполнить). Тогда... вопрос, опять актуалеьн. Буду признателен за помощ.. |
07.05.2010 |
Спасибо! Интересно и просто изложено!. |
| Alex 16.06.2010 |
Помогите разобраться и правильно составить формулу, есть таблица следующего вида: Янв Фев Март ... итд 1 а б в 2 в а б 3 а а 4 б в 5 в б а 6 в в ...итд мне нужно чтобы в другом листе(книге) при вводе (например в А1) названия месяца заполнялась новая таблица в виде: А1=Фев Фев 1 б 2 а 3 а 4 - с пропуском пустых ячеек (не содержащих данных) 5 б 6 в заранее спасибо.... |
| Kant 17.06.2010 |
Как сделать так чтоб у меня суммировал в массиве тока определенные части массива, а не весь массив? Пример: Массив 1 Массив 2: 3 5 3 5 7 5 7 5 3 5 3 5 3 5 Ответ: 4 (Сумма разницы между массивом 1 и массивом 2 > 0) То есть в массиве 1 3 меньше 5 не берем, а когда доходим до 7 видим что больше 5 берем разницу(2) и т.д. И берем сумму тока положительных значений. . |
| Kant 17.06.2010 |
Как сделать так чтоб у меня суммировал в массиве тока определенные части массива, а не весь массив? Пример: Массив 1_______ Массив 2: 3______________ 5 3______________ 5 7______________ 5 7______________ 5 3______________ 5 3______________ 5 3______________ 5 Ответ: 4 (Сумма разницы между массивом 1 и массивом 2 > 0) То есть в массиве 1 3 меньше 5 не берем, а когда доходим до 7 видим что больше 5 берем разницу(2) и т.д. И берем сумму тока положительных значений. заранее спасибо..... |
| Вита 18.06.2010 |
Підскажіть як записати від'ємні елементи матриці (20 на 20) у масив B(400), а додатні - у масив C(400). Записи у масиві робити без пропусків елементів масиву. . |
| Sasha 09.07.2010 |
Подскажите как отображать в одной определенной ячейке содержание последней ячейки другого столбца, при том что этот столбец постоянно добавляется. Столбец - это грубо говоря пополняющийся список слов. И нужно что бы последнее отображалось в той самой заветной ячейке. . |
| Максим 13.07.2010 |
Sasha, если в столбце нет пропусков, то вам поможет формула =ИНДЕКС(a:a;СЧЁТЗ(a:a)) Вместо А укажите нужный столбец. . |
| Pavel 29.07.2010 |
а может кто знает как развернуть массив наоборот, так чтоб 1 элемент стал последним и, соответственно последний первым. Заранее благодарен.. |
| FoxR 29.07.2010 |
Уверен есть ещё способы но можно воспользоватся таким: =ДВССЫЛ(АДРЕС(СТРОКА($K$7)-СТРОКА()+1;СТОЛБЕЦ($K$7))) где $К$7 'это обязательно действительная ссылка на последнюю ячейку массива . |