Сочетание мощности
Объединение формул СУММЕСЛИ и СМЕЩЕНИЕ, списка проверки и поля со списком для получения сводки данных, которые будут выбраны по месяцам — поистине мощная комбинация!
Шаг 1: Выбор элементов из списков проверки
Выбор элемента из списка проверки (столбец A на снимке экрана)
позволяет формулам, введенным в ячейки в столбцах C и D, идентифицировать текст и возвращать итоговые результаты из столбца, который содержит критерии (выбранный элемент) для месяца, выбранного в поле со списком.
Чтобы добавить список проверки к диапазону ячеек:
-
Выделите диапазон ячеек (на скриншоте на предыдущей странице выделены ячейки A12: A15)
-
Выберите ячейки A19: A23.
-
В меню «Данные» выберите «Проверка».
-
В диалоговом окне «Проверка данных» выберите вкладку «Настройки» и выберите «Список» в поле «Разрешить».
-
В поле «Источник» нажмите F3, выберите имя, определенное для списка (Уровень 3 в этом примере, см. Снимок экрана на последней странице этого совета) и нажмите «ОК».
Шаг 2. Ввод формул, возвращающих итоговые балансы для выбранных товаров. Формула в ячейках C12: C15:
СУММЕСЛИ (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2))
Формула в ячейках D12: D15:
СУММЕСЛИ (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2-12))
Пояснение:
Формула СУММЕСЛИ в столбце C суммирует балансовые суммы из столбца за декабрь 2003 г .; формула СУММЕСЛИ в столбце D суммирует суммы баланса из столбца за декабрь 2002 года.
Формула СУММЕСЛИ имеет три аргумента:
Первый аргумент:
Диапазон для оценки в соответствии с критериями, введенными во втором аргументе формулы СУММЕСЛИ. В этом примере диапазон — DataLevel3, который является именем, определенным для столбца C в базе данных баланса. Он содержит элементы баланса 3-го уровня, такие как денежные средства, дебиторская задолженность, запасы и т. Д.
Второй аргумент:
Критерий — это элемент, выбранный из списка валидации Level3.
Третий аргумент:
Столбец, из которого будут суммироваться данные. Это будет выбрано в соответствии с формулой СМЕЩЕНИЕ для столбца месяца, которая корректируется числом, выбранным из списка месяцев в поле со списком. Формула OFFSET позволяет отвести выбранный месяц от базового столбца (столбец C на снимке экрана ниже).
Как работает формула СМЕЩЕНИЕ
Столбец 29 — это номер столбца за декабрь 2003 года, а номер столбца за декабрь 2002 года — 17, что на 12 столбцов раньше (см. Снимок экрана ниже).
Как изменить заголовки заголовков на листе с символов на числа: {пусто} 1. В меню «Инструменты» выберите «Параметры».
-
На вкладке «Общие» установите флажок «Стиль ссылки R1C1».
Когда декабрь 2003 года выбран из раскрывающегося списка Combo Box (Список месяцев), номер месяца в этом списке равен 24 (это рассчитывается путем определения количества месяцев с января 2002 года по декабрь 2003 года:
2 года * 12 месяцев = 24). Связанная ячейка с полем со списком получает значение 24.
В таблице данных столбец 3 — это базовый столбец, который формула СУММЕСЛИ вычисляет для критериев во втором аргументе формулы СУММЕСЛИ.
В этом случае декабрь 2003 г. удален от столбца C на 24 + 2 = 26 (2 = столбец D и столбец E).
В третьем аргументе Sum_range должен находиться на расстоянии 26 столбцов от базового столбца. Формула СМЕЩЕНИЕ возвращает результат 26 и заставляет формулу СУММЕСЛИ суммировать числа из столбца за декабрь 2003 г.