Представьте, что у нас есть показатели продаж компании:
И что мы хотим найти общие цифры за год до настоящего времени. Мы можем добавить выпадающий список так:
Так что мы можем указать текущий месяц. Следовательно, теперь мы хотим рассчитать текущий год для марта. Самый простой формат - иметь формулы, которые распространяются на весь диапазон:
А потом мы просто меняли формулы каждый месяц.
Однако Excel допускает другой подход. Мы могли бы настроить динамический диапазон, размер которого варьировался в зависимости от месяца, в котором мы находимся. Когда мы меняем месяц в раскрывающемся списке, размер диапазона изменяется.
Таким образом, для марта диапазон составляет 3 столбца, а для июня - 6 месяцев.
Размер диапазона регулируется по месяцам. Один из способов сформулировать это - использовать функцию месяца:
= Месяц (c8)
Где c8 - адрес ячейки нашего раскрывающегося списка. Однако предпочтительным методом является использование функции ПОИСКПОЗ для определения положения текущих месяцев во всех месяцах нашего отчета:
ПОИСКПОЗ (c8, $ c $ 3: $ j $ 3,0)
Где:
• c8 - адрес ячейки текущего месяца
• C3: J3 - адрес всех наших месяцев
• 0 означает точное соответствие
Теперь мы можем указать размер нашего динамического диапазона с помощью функции OFFSET, которая имеет 5 аргументов:
= СМЕЩЕНИЕ (ссылка, строки, столбцы, высота, ширина)
Где:
• Ссылка - это верхний левый угол нашего динамического диапазона - ячейка C5 - первая ячейка, которую мы хотим суммировать.
• Rows - количество строк вниз от нашей базовой ячейки - это 0
• Cols - количество столбцов напротив нашего базового вызова - это 0
• Ширина нашего динамического диапазона - в данном случае 3. Однако, поскольку мы хотим, чтобы диапазон варьировался от месяца к месяцу, мы поместим здесь наши формулы ПОИСКПОЗ.
• Это высота нашего динамического диапазона, равная 1
Итак, наши формулы СМЕЩЕНИЯ:
= СМЕЩЕНИЕ (c5,0,0; ПОИСКПОЗ (c8; $ c $ 3: $ j $ 3,0); 1)
Наконец, нам нужно сообщить Excel, чтобы он суммировал это, чтобы получить полные формулы в следующем виде:
= СУММ (СМЕЩЕНИЕ (c5,0,0; ПОИСКПОЗ (c8; $ c $ 3: $ j $ 3,0); 1))
У нас есть:
Теперь, если мы изменим месяц в раскрывающемся списке, правильное число от года до даты появится:
Поскольку это автоматическое обновление, этот подход имеет следующие преимущества:
• Нет необходимости менять формулы каждый месяц.
• Чем меньше изменений формул, тем меньше вероятность ошибки.
• Таблицу могут использовать те, у кого ограниченные знания Excel - они могут просто изменить раскрывающийся список и не беспокоиться о формулах.
