Как в экселе сделать формулу с несколькими если

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов: как в экселе сделать формулу с несколькими если

arrays0.gif

Формулы массива в Excel - это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории - те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах...

Пример 1. Классика жанра - товарный чек

arrays1.gif

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

  1. выделяем ячейку С7
  2. вводим с клавиатуры =СУММ(
  3. выделяем диапазон B2:B5
  4. вводим знак умножения (звездочка)
  5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ - в итоге должно получиться так:
    arrays2.gif
  6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

arrays3.gif

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле - отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно - они автоматически появляются при нажатии Ctrl + Shift + Enter.

Пример 2. Разрешите Вас... транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

arrays6.gif

  • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
  • вводим функцию транспонирования =ТРАНСП(   
  • в качестве аргумента функции выделяем наш массив ячеек A1:B8

arrays7.gif

жмем Ctrl + Shift + Enter и получаем "перевернутый массив" в качестве результата:

arrays8.gif

Редактирование экселе формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике... На обороте тетради на обложке было что? Таблица умножения вот такого вида:

arrays4_1.jpg

При помощи формул массива она вся делается в одно движение:

arrays5.gif

  1. выделяем диапазон B2:K11
  2. вводим формулу =A2:A11B1:K1
  3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

arrays4.gif

Пример 4. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

arrays10.gif

 В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

 


Источник: http://www.planetaexcel.ru/techniques/2/91/


Рекомендуем посмотреть ещё:


Закрыть ... [X]

Функция ЕСЛИ с несколькими условиями Microsoft Excel - группа Блеск для пластика в машину

Как в экселе сделать формулу с несколькими если Использование формул массива: рекомендации и примеры
Как в экселе сделать формулу с несколькими если Несколько условий в функциях MS Excel ЕСЛИ (IF) и УСЛОВИЯ
Как в экселе сделать формулу с несколькими если Функция ЕСЛИ - Служба поддержки Office - Office Support
Как в экселе сделать формулу с несколькими если Функция ЕСЛИ в Excel Excel Training
Как в экселе сделать формулу с несколькими если Формулы массива в Excel
Как в экселе сделать формулу с несколькими если Вышивка картины лентами. Цветочная радость. Мастер-класс с
Как в экселе сделать формулу с несколькими если Гемоглобин : функции и норма гемоглобина у мужчин и женщин
Как в экселе сделать формулу с несколькими если Георгий Малец Блоги Эхо Москвы
Как в экселе сделать формулу с несколькими если День медицинского работника Форум
Дизайн ногтей шеллаком осень-зима идеи 92 фото Зимние рисунки на ногтях Как подобрать красную помаду правильно и с чем её носить Команды для командного блока в Майнкрафт Лучшие Пословицы про Топор со всего мира (страница 1) Мастер-класс из бумаги и ниток 9 фотографий ВКонтакте