Потренировался в массивах. Скоро читать эту лекцию на курсе "Волшебные формулы". Хороший пример будет для учеников.
Вся прелесть массивов в том, что их очень сложно объяснять ученикам. Если ты просто решатель задач, то за счёт применения массивов можно быть вечным волшебником. Но я волшебников учу и мне нужны внятные формулировки, помогающие людям самим решать свои задачи. Великий Уокенбах и его "Библия Excel", конечно же, прекрасны. Но пока продерёшься сквозь теорию и массивы констант, которым (пока) не вижу применения... Поэтому ищу постоянно примеры задач, которые можно решить с применением массивов.
Спасибо автору за задачку и разрешение использовать её для создания этого поста.
В файлике два листа:
Попытайтесь сами создать все формулы на листе "Исходный", а потом сравните их с листом "Решение".
"Ключик" к массивам я нашёл совсем недавно. Он в определении, которое я излагаю ученикам:
"Массив — это воображаемый диапазон".
Т.е., необходимость в использовании массивов возникает, когда по какой-то причине не хватает столбца или строчки. Их почему-то нельзя добавить в таблицу. Скорее всего из-за того что тогда структура таблицы рассыплется и ею будет уже неудобно пользоваться. Эти дополнительные диапазоны будут содержать промежуточные данные, необходимые для итоговых расчётов. Никакой пользы напрямую для хозяина таблицы они принести не могут, поэтому их нет смысла отображать.
Вот как в этом примере.
Давайте рассмотрим столбик "№1". В нём стоят числа, обозначающие сколько каких радиодеталей надо собрать в первую посылку. Над этим столбцом, в клетке B2 надо поставить результат говорящий, сколько наименований радиодеталей имеет нехватку для этой посылки. Нам бы пригодился дополнительный столбик, в котором бы мы поместили формулу типа такой "=ЕСЛИ(B5>M5;1;0)" Она проставила бы 1 напротив тех позиций, в которых есть нехватка и 0 там, где запасов хватает на отправку. Потом бы мы просуммировали значения в этом дополнительном столбце и узнали бы, сколько наименований для этой посылки имеют недостачу.
Но у нас нет такой роскоши — добавлять ещё 10 столбцов, не несущих особой информационной нагрузки, в эту таблицу. Поэтому мы заменим в формуле адреса конкретных ячеек на адреса диапазонов (B5 на B5:B71), при этом для диапазона запасов (M5:M71) мы создадим имя "Запасы".
Теперь формула будет выглядеть так: =СУММ(ЕСЛИ(B5:B71>Запасы;1;0)) Функция СУММ нужна для получения итогового результата.
Но чтобы формула правильно сработала, надо нажимать не просто Enter, а Ctrl+Shift+Enter. Так мы сообщаем компьютеру, что это формула с использованием массивов и к адресам диапазонов в ней надо относиться особым образом.
При этом по краям формулы будут нарисованы знаки фигурных скобочек {...}. Их компьютер рисует сам. Так можно визуально отличить формулу с использованием массива от обычной формулы.
Попробуйте сами воссоздать формулы в строке 1, в которой надо показать суммарно недостачу по всем товарным позициям в посылке/
И в строке 3, где надо проставить буквы: "С", если для посылки хватает всех деталей, "Н", если в какой-то позиции есть недостача и пустоту, если посылка не собирается вообще, а столбец стоит просто так, "про запас".
Обратите внимание на применение условного форматирования на листе "Решение". Оно показывает для каких позиций хватает деталей с учётом приоритета посылок, т.е., если важно собирать посылки именно в том порядке, который представлен в таблице.
Вот сам файлик для тренировки: Перечень закупок.xlsx