Обща формула
=SUM(OFFSET(start,0,0,N,1))
Обобщение
За да сумирате набор от месечни данни през n брой месеца, можете да използвате формула, базирана на функциите SUM и OFFSET. В показания пример формулата в G6 е:
=SUM(OFFSET(C5,0,0,G5,1))
Обяснение
В показания пример имаме месечни данни за годините 2017 и 2018 г. Целта е динамично да сумираме стойности през даден брой месеци, кодирани като 6 в клетка G5. Това се прави чрез подаване на референция, конструирана с функцията OFFSET във функцията сума.
Функцията OFFSET ви позволява да изградите референция, като използвате начална точка, отместване на редове и колони и височина и ширина. OFFSET е удобен във формули, които динамично осредняват или сумират "последните n стойности", "първите n стойности" и т.н. В клетка G6 OFFSET е конфигуриран по следния начин:
OFFSET(C5,0,0,G5,1)
Преведено: от начална позиция на C5, изградете референтни 6 реда x 1 колона без отместване. С числото 6 в G5, OFFSET връща референцията C5: C10 към SUM:
=OFFSET(C5,0,0,G5,1) // returns C5:C10 =SUM(C5:C10)) // returns 5775
Формулата в G7 е настроена по същия начин. Единствената разлика е началната точка:
=OFFSET(D5,0,0,G5,1) // returns D5:D10 =SUM(D5:D10)) // returns 6380
Когато числото в G5 се промени, и двете формули се актуализират динамично и връщат нов резултат.
Промяна от година на дата
Вместо да кодирате твърдо стойността във формулата, можете да използвате функцията COUNT, за да преброите съществуващите записи в дадена колона и да върнете това число в OFFSET. Обща версия на тази формула ще изглежда така:
=SUM(OFFSET(start,0,0,COUNT(rng),1))
където rng е препратка към диапазон, който съдържа YTD стойности.
