Започнете с многогодишни данни.
Да приемем, че имате детайлни записи от две години. Всеки запис има дневна дата. Когато изграждате обобщена таблица от този отчет, ще имате стотици редове дневни дати в обобщената таблица. Това не е много обобщение.
Изберете една от тези клетки с дата в обобщената таблица. От раздела Анализ на лентата изберете Групово поле.
Тъй като сте в поле за дата, получавате тази версия на диалоговия прозорец Групиране. В него премахнете отметката от Месеци и изберете Години.
Ежедневните дати се навиват до години. Преместете полето за дата от РЕДОВЕ в КОЛОНИ.
Резултатът е почти перфектен. Но вместо общ сбор в колона D, вероятно искате процентна дисперсия.
За да се отървете от графата Общ сбор, в раздела Дизайн изберете Общи суми, Включено само за колони. (Съгласен съм, това е един от най-неудобните формулировки в Excel.)
За да изградите колоната на дисперсията, трябва да напишете формула извън обобщената таблица, която сочи вътре в обобщената таблица. Не докосвайте мишката или клавишите със стрелки, докато изграждате формулата, или ще се появи неприятната функция GETPIVOTDATA. Вместо това просто напишете =C5/B5-1и натиснете Enter.
Благодаря на Александр Воробьев, че предложи този съвет.
Гледам видео
- Започнете с многогодишни данни
- Вмъкване, обобщена таблица
- Плъзнете полето за дата в областта на реда
- Excel 2016: Натиснете Ctrl + Z, за да разгрупирате датите
- Плъзнете приходите в областта на стойностите
- Изберете всяка дата в областта на реда
- Използвайте групово поле
- Изберете месеци и години
- Как да добавяме междинни суми към полето години след групиране на обобщена таблица
- Използвайте таблична форма в обобщена таблица, за да придадете на всяко поле на ред собствена колона
- Повторете всички етикети на редове, за да попълните празните места в обобщена таблица
- Плъзнете години в областта на колоната
- Щракнете с десния бутон върху заглавието на общата сума и премахнете
- Как да избегнем функцията GetPivotData, когато формулата сочи към обобщена таблица
- За да създадете дисперсия, въведете формулата без мишка или стрелки
- Благодаря на Александр Воробьев, че предложи този съвет
Видео транскрипция
Научете Excel от MrExcel подкаст, епизод 1998. Осева таблица Година през година.
Ще подкастирам цялата тази книга, отидете, щракнете върху „i“ в горния десен ъгъл за плейлиста MrExcel Excel.
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днес първата тема в книгата за обобщените таблици и как да създадете отчет за годината. И така, започваме с набора от данни, който обхваща две години, разбирате ли, отиваме от 2018 до 2019. Ще създам обобщена таблица, вмъкване, обобщена таблица, добре, всичко е наред, нека отидем на нов работен лист.
На новия работен лист ще вземем полето Дата и ще го плъзнем в областта Редове. И тогава, виждате ли, в Excel 2016 той автоматично групира нещата. Сега знам, че повечето хора все още са в Excel 2013, може би Excel 2010, така че ще натисна Ctrl + Z, за да отмените. Това е опитът, който ще получите през 2010 и 2013 г. Ще вземем полето Приходи и ще го плъзнем в областта Стойности. Така че започваме с грозен отчет, ще има стотици и стотици редове, защото всеки ден имахме продажби. Но ще отида в първото поле за дата, полето за първа дата, отидете на инструментите за обобщена таблица, Анализирайте, през 2013 или 2016 и 2010 се наричаше Опции и изберете Групово поле Уверете се, че сте избрали дата, преди да изберете Групово поле и да превъртите нещата до месеци и години. Или тримесечия и години,или Месеци и тримесечия и години, щракнете върху OK. И това, което получаваме, е ново виртуално поле, наречено Години тук в областта на редовете.
Сега някои досади. Досадно е, че не ни дават суми за годината, достатъчно е лесно да се поправи това, въпреки че в моя случай не е нужно да поправям това. Щракваме върху Field Settings и Automatic, защото не планирам да оставям годините тук. Разбира се всеки път, когато създавам обобщена таблица, отивам в Design и казвам Show in Tabelar Form, за да вкарам всичко в собствената си колона. И след това повторете всички етикети на артикули, за да попълните тези празни места отляво. Отново, това не би било необходимо тук, защото не планирам да оставям годините там, където са, ще взема годините и ще ги премествам в колоните и това е докладът, за който стрелях.
Добре, така че имаме месеци надолу отляво, от 2018 до 2019 г. В колона D, какво всъщност искам, не искам общ сбор, щраквам с десния бутон и казвам Премахване на големия Общо и сега сме извън обобщена таблица. И така,% Change, много изкушаващо да изградите тази формула с помощта на мишката или клавишите със стрелки, но не можете, защото ще получите функцията … get Pivot Data, която не се копира лесно. Така че = C5 / B5-1, ще форматираме това като процент и щракнете двукратно, за да го копирате, нали. Красив доклад, показващ къде бяхме миналата година, къде бяхме тази година и след това процентното изменение от година на година.
Е, този съвет и 40 други са всички в тази книга. Продължете, кликнете върху „i“ в горния десен ъгъл до книгата. Добре, така че започнете с набора от данни с няколко години, направете Вмъкване, Осева таблица, плъзнете полето за дата в областта на реда. И в Excel 2016 натиснете Ctrl + Z, за да отмените автоматичните им актуализации за групиране. Защо просто не оставим това тук? Е, всъщност зависи. Ако искате да имате данни от, да речем, от 2 януари до 30 декември, те няма да групират тези данни правилно. Трябва да прекара цяла година, за да получите полето за годината. Може да се окаже, че се получава при вас, аз съм малко изрод за контрол, по-скоро просто ще имам контрол, затова отменя. Има: плъзнете приходите в областта на стойностите, изберете която и да е клетка, която и да е дата в областта на редовете и след това Групово поле, изберете Месеци и години,плъзнете години над областта на колоната, щракнете с десния бутон на общата сума, за да премахнете и след това изградете дисперсия, въведете формулата, не използвайте мишката или клавишите със стрелки.
Нямам идея как да се произнесе, но благодаря на този читател (Александр Воробьев), че предложи този съвет и благодаря на това, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast1998.xlsx
