Спосаб 1. Формулы
Пачнем, для размінкі, з самага простага варыянту - формул. Калі ў якасці ўваходных дадзеных у нас ёсць невялікая табліца, адсартаваная па даце, то для разліку выніковай сумы ў асобным слупку нам спатрэбіцца элементарная формула:
Галоўнай асаблівасцю тут з'яўляецца хітрая фіксацыя дыяпазону ўнутры функцыі SUM - спасылка на пачатак дыяпазону робіцца абсалютнай (са знакамі даляра), а на канец - адноснай (без даляраў). Адпаведна, пры капіяванні формулы на ўвесь слупок мы атрымліваем пашыраецца дыяпазон, суму якога і вылічаем.
Недахопы такога падыходу відавочныя:
- Табліцу неабходна адсартаваць па даце.
- Пры даданні новых радкоў з дадзенымі формулу прыйдзецца пашырыць ўручную.
Спосаб 2. Зводная табліца
Гэты спосаб трохі складаней, але значна прыемней. І для абвастрэння давайце разгледзім больш сур'ёзную праблему - табліцу з 2000 радкоў дадзеных, дзе няма сартавання па слупку даты, але ёсць паўторы (г.зн. мы можам прадаваць некалькі разоў у адзін дзень):
Мы пераўтворым нашу зыходную табліцу ў «разумную» (дынамічную) спалучэнне клавіш Ctrl+T або каманда Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу), а затым мы будуем на ім зводную табліцу з дапамогай каманды Устаўка – зводная табліца (Устаўка — зводная табліца). У вобласці радкоў у зводцы змяшчаем дату, а ў вобласці значэнняў - колькасць прададзеных тавараў:
Звярніце ўвагу, што калі ў вас не зусім старая версія Excel, то даты аўтаматычна групуюцца па гадах, кварталах і месяцах. Калі вам патрэбна іншая групоўка (або яна зусім не патрэбна), то вы можаце выправіць гэта, пстрыкнуўшы правай кнопкай мышы на любой даце і выбраўшы каманды Група / Разгрупаваць (Групаваць / Разгрупаваць).
Калі вы жадаеце бачыць як выніковыя сумы па перыядах, так і выніковую суму ў асобным слупку, то мае сэнс перакінуць поле ў вобласць значэнняў прададзены зноў атрымаць дублікат поля - у ім мы ўключым адлюстраванне бягучых вынікаў. Для гэтага пстрыкніце поле правай кнопкай мышы і абярыце каманду Дадатковыя разлікі – сукупная сума (Паказваць значэнні як — выніковыя вынікі):
Там жа можна выбраць опцыю росту сумы ў працэнтах, а ў наступным акне трэба выбраць поле, па якім будзе ісці назапашванне - у нашым выпадку гэта поле даты:
Перавагі гэтага падыходу:
- Вялікі аб'ём дадзеных хутка счытваецца.
- Няма неабходнасці ўводзіць формулы ўручную.
- Пры змене зыходных даных дастаткова абнавіць зводку правай кнопкай мышы або камандай Дадзеныя – Абнавіць усе.
Недахопы вынікаюць з таго, што гэта канспект, а значыць, рабіць у ім усё, што заўгодна (ўстаўляць радкі, пісаць формулы, будаваць якія-небудзь дыяграмы і г.д.) ужо не атрымаецца.
Спосаб 3: Power Query
Давайце загрузім нашу «разумную» табліцу з зыходнымі дадзенымі ў рэдактар запытаў Power Query з дапамогай каманды Дадзеныя – з табліцы/дыяпазону (Дадзеныя — з табліцы/дыяпазону). У апошніх версіях Excel яго, дарэчы, перайменавалі – цяпер так і называецца З лісточкамі (З аркуша):
Затым мы выканаем наступныя дзеянні:
1. Сартуйце табліцу ў парадку ўзрастання па слупку даты з дапамогай каманды Сартаваць па ўзрастанні у выпадальным спісе фільтраў у загалоўку табліцы.
2. Крыху пазней для падліку назапашанай сумы нам спатрэбіцца дапаможны слупок з парадкавым нумарам радка. Давайце дадамо яго з дапамогай каманды Дадаць слупок – Слупок індэксу – з 1 (Дадаць слупок — Слупок індэкс — Ад 1).
3. Акрамя таго, для разліку агульнай сумы нам патрэбна спасылка на слупок прададзены, дзе ляжаць нашы абагульненыя дадзеныя. У Power Query слупкі таксама называюцца спісамі (спіс), і каб атрымаць спасылку на іх, пстрыкніце правай кнопкай мышы на загалоўку слупка і абярыце каманду Падрабязнасці (Паказаць дэталі). У радку формул з'явіцца неабходнае нам выраз, якое складаецца з назвы папярэдняга кроку #"Індэкс дададзены", адкуль мы бярэм табліцу і назву слупка [Продажы] з гэтай табліцы ў квадратных дужках:
Скапіруйце гэты выраз у буфер абмену для далейшага выкарыстання.
4. Выдаліце непатрэбнае больш апошні крок прададзены і дадайце замест гэтага вылічаны слупок для разліку агульнай сумы з дапамогай каманды Даданне слупка - Карыстальніцкі слупок (Дадаць слупок — карыстальніцкі слупок). Неабходная нам формула будзе выглядаць так:
Вось функцыя Спіс.Дыяпазон прымае зыходны спіс (слупок [Распродажы]) і здабывае з яго элементы, пачынаючы з першага (у формуле гэта 0, бо нумарацыя ў Power Query пачынаецца з нуля). Колькасць элементаў для атрымання - гэта нумар радка, які мы бярэм са слупка [Індэкс]. Такім чынам, гэтая функцыя для першага радка вяртае толькі адну першую ячэйку слупка прададзены. Для другога радка – ужо першыя дзве клеткі, для трэцяга – першыя тры і г.д.
Ну, тады функцыя Спіс.Сума сумуе вынятыя значэння, і мы атрымліваем у кожным радку суму ўсіх папярэдніх элементаў, г.зн. сукупны вынік:
Засталося выдаліць непатрэбны нам слупок Index і загрузіць вынікі назад у Excel з дапамогай каманды Home – Close & Load to.
Праблема вырашана.
фарсаж
У прынцыпе, гэта можна было б спыніць, але ёсць кропка дзегцю — створаны намі запыт працуе з хуткасцю чарапахі. Напрыклад, на маім не самым слабым ПК табліца з усяго 2000 радкоў апрацоўваецца за 17 секунд. Што рабіць, калі ёсць больш дадзеных?
Для паскарэння можна выкарыстоўваць буферызацыю з дапамогай спецыяльнай функцыі List.Buffer, якая загружае перададзены ёй у якасці аргументу спіс (спіс) у аператыўную памяць, што значна паскарае доступ да яго ў далейшым. У нашым выпадку мае сэнс буферызаваць спіс #”Added index”[Sold], да якога Power Query мае доступ пры падліку агульнай сумы ў кожным радку нашай табліцы з 2000 радкоў.
Для гэтага ў рэдактары Power Query на ўкладцы Галоўнае націсніце кнопку Advanced Editor (Галоўная – Advanced Editor), каб адкрыць зыходны код нашага запыту на мове M, убудаванай у Power Query:
А потым дадайце туды радок са зменнай МойСпіс, значэнне якога вяртаецца функцыяй буферызацыі, і на наступным этапе мы замяняем выклік спісу гэтай зменнай:
Пасля ўнясення гэтых змяненняў наш запыт стане значна хутчэйшым і справіцца з табліцай з 2000 радкоў усяго за 0.3 секунды!
Іншая справа, праўда? 🙂
- Дыяграма Парэта (80/20) і як яе пабудаваць у Excel
- Пошук па ключавых словах у тэксце і буферызацыя запытаў у Power Query