Представете си, че имаме някои цифри за продажби за една компания:
И че искаме да намерим общите цифри за годината до момента. Можем да добавим падащо меню по следния начин:
За да можем да посочим текущия месец. Затова сега искаме да отработим досегашната година за март. Най -простият формат би бил формулите, които да се разширяват в обхвата:
И тогава просто сменихме формулите всеки месец.
Excel обаче позволява друг подход. Можем да настроим динамичен диапазон, чийто размер варира в зависимост от месеца, в който се намираме. Когато променяме месеца в падащото меню, размерът на диапазона се променя.
Така че за месец март диапазонът е с дължина 3 колони, а за месец юни ще продължи 6 месеца.
Размерът на диапазона се регулира от месеца. Един от начините да се формулира това е да се използва функцията Month:
= Месец (c8)
Където c8 е клетъчният адрес на падащото ни меню. Предпочитаният метод обаче е да се използва функцията MATCH за определяне на позицията на текущите месеци във всички месеци в нашия отчет:
МАТЧ (c8, $ c $ 3: $ j $ 3,0)
Където:
• c8 е клетъчният адрес на текущия месец
• C3: J3 е адресът на всички наши месеци
• 0 е за осигуряване на точно съвпадение
Сега можем да определим размера на нашия динамичен диапазон чрез функцията OFFSET, която има 5 аргумента:
= OFFSET (справка, редове, cols, височина, ширина)
Където:
• Референцията е горният ляв ъгъл на нашия динамичен диапазон - клетка C5 - първата клетка, която искаме да сумираме
• Редове - броят редове надолу от нашата базова клетка - това е 0
• Cols - броят на cols срещу нашия основен разговор - това е 0
• Ширината на нашия динамичен диапазон - която в този случай е 3. Въпреки това, тъй като искаме диапазонът да варира според месеца, ние ще поставим нашите MATCH формули тук
• Това е височината на нашия динамичен диапазон, която е 1
Така че нашите ОФСЕТ формули са:
= 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 - те могат просто да променят падащото меню и да не се притесняват от формулите
