Уявіть, що у нас є деякі цифри продажів для компанії:
І що ми хочемо знайти загальні цифри за поточний рік. Ми можемо додати спадне меню так:
Щоб ми могли вказати поточний місяць. Тому тепер ми хочемо розрахувати нинішній рік на березень. Найпростіший формат - мати б формули, які розширюються по всьому діапазону:
І тоді ми б просто змінювали формули щомісяця.
Однак Excel допускає інший підхід. Ми могли б налаштувати динамічний діапазон, розмір якого змінювався в залежності від місяця, в якому ми знаходимось. Коли ми змінюємо місяць у спадному меню, тоді змінюється і розмір діапазону.
Таким чином, для березня місяць діапазон має три стовпці, а для червня місяця це буде 6 місяців разом.
Розмір асортименту регулюється місяцем. Одним із способів формулювання цього є використання функції місяця:
= Місяць (c8)
Де c8 - адреса комірки нашого випадаючого меню. Однак метод, який є кращим, полягає у використанні функції MATCH для визначення стану поточних місяців у всіх місяцях нашого звіту:
МАТЧ (c8, $ c $ 3: $ j $ 3,0)
Де:
• c8 - адреса комірки поточного місяця
• C3: J3 - це адреса всіх наших місяців
• 0 для забезпечення точної відповідності
Тепер ми можемо вказати розмір нашого динамічного діапазону за допомогою функції OFFSET, яка має 5 аргументів:
= OFFSET (посилання, рядки, колі, висота, ширина)
Де:
• Посилання - це верхній лівий кут нашого динамічного діапазону - комірка C5 - перша клітинка, яку ми хочемо підсумувати
• Рядки - кількість рядків вниз від нашої базової комірки - це 0
• Cols - кількість cols навпроти нашого базового виклику - це 0
• Ширина нашого динамічного діапазону - у цьому випадку 3. Однак, оскільки ми хочемо, щоб діапазон змінювався за місяцями, ми розмістимо тут наші формули MATCH
• Це висота нашого динамічного діапазону 1
Отже, наші формули OFFSET:
= OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1)
Нарешті, нам потрібно повідомити Excel, щоб він підсумував це, щоб надати повні формули так:
= SUM (OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1))
Ми маємо:
Тепер, якщо ми змінимо місяць у випадаючому списку, правильна цифра за рік до дати протікає через:
Оскільки це автоматичне оновлення, цей підхід має такі переваги:
• Немає необхідності змінювати формули щомісяця
• Оскільки формул стає менше, тим менше можливостей для помилок
• Електронною таблицею може користуватися той, хто має обмежені знання Excel - вони можуть просто змінити випадаюче меню і не турбуватись формулами
