Дапусцім, вы кіруеце некалькімі праектамі з рознымі бюджэтамі і хочаце візуалізаваць свае выдаткі на кожны з іх. Гэта значыць, з гэтай зыходнай табліцы:
.. атрымаць нешта накшталт гэтага:
Іншымі словамі, вам трэба размеркаваць бюджэт на дні кожнага праекта і атрымаць спрошчаную версію дыяграмы Ганта праекта. Рабіць гэта сваімі рукамі доўга і сумна, макрасы складаныя, але Power Query for Excel у такой сітуацыі паказвае сваю моц ва ўсёй красе.
Запыт на харчаванне гэта надбудова ад Microsoft, якая можа імпартаваць даныя ў Excel практычна з любой крыніцы, а затым трансфармаваць іх мноствам розных спосабаў. У Excel 2016 гэты дадатак ужо ўбудаваны па змаўчанні, а для Excel 2010-2013 яго можна загрузіць з вэб-сайта Microsoft, а затым усталяваць на свой ПК.
Спачатку давайце ператворым нашу зыходную табліцу ў «разумную», выбраўшы каманду Фармат у выглядзе табліцы таб Галоўная (Галоўная — фармат у табліцу) або націснуўшы спалучэнне клавіш Ctrl+T :
Затым перайдзіце на ўкладку Дата (калі ў вас Excel 2016) або на ўкладцы Запыт на харчаванне (калі ў вас Excel 2010-2013 і вы ўсталявалі Power Query як асобную надбудову) і націсніце кнопку «З табліцы / дыяпазону». :
Наша разумная табліца загружаецца ў рэдактар запытаў Power Query, дзе першым крокам з'яўляецца наладжванне фарматаў лікаў для кожнага слупка з дапамогай выпадаючых меню ў загалоўку табліцы:
Каб разлічыць бюджэт на дзень, трэба разлічыць працягласць кожнага праекта. Для гэтага абярыце (ўтрымлівайце клавішу Ctrl) слупок першы Заканчэнне, А затым дома і выбраць каманду Дадаць слупок – Дата – Адняць дні (Дадаць слупок — Дата — Адняць дні):
Атрыманыя лічбы на 1 менш, чым трэба, таму што кожны праект мы павінны пачынаць у першы дзень раніцай і заканчваць у апошні дзень увечары. Таму абярыце атрыманы слупок і дадайце ў яго адзінку з дапамогай каманды Пераўтварэнне – Стандартны – Дадаць (Пераўтварэнне — Стандарт — Дадаць):
Зараз дадамо слупок, дзе разлічваем бюджэт на дзень. Для гэтага на таб Дадаць слупок Я не гуляю Карыстальніцкі слупок (Карыстальніцкі слупок) і ў якое з'явілася акне увядзіце назву новага поля і формулу разліку, выкарыстоўваючы назвы слупкоў са спісу:
Цяпер самы тонкі момант - ствараем яшчэ адзін вылічальны слупок са спісам дат ад пачатку да канца з крокам у 1 дзень. Для гэтага зноў націсніце кнопку Карыстальніцкі слупок (Карыстальніцкі слупок) і выкарыстоўваць убудаваную мову Power Query M, якая называецца Спіс.Даты:
Гэтая функцыя мае тры аргументы:
- дата пачатку – у нашым выпадку яна бярэцца са слупка дома
- колькасць дат, якія трэба згенераваць – у нашым выпадку гэта колькасць дзён для кожнага праекта, якую мы палічылі раней у слупку Адніманне
- крок па часе - зададзены дызайнам #працягласць(1,0,0,0), што азначае на мове М – адзін дзень, нуль гадзін, нуль хвілін, нуль секунд.
Пасля націску на кнопку OK атрымліваем спіс (List) дат, які можна разгарнуць у новыя радкі з дапамогай кнопкі ў шапцы табліцы:
... і мы атрымліваем:
Цяпер усё, што засталося, гэта згарнуць табліцу, выкарыстоўваючы згенераваныя даты ў якасці імёнаў для новых слупкоў. За гэта адказвае калектыў. Падрабязная калонка (Зводны слупок) таб Канвертаваць (Ператварэнне):
Пасля націску на кнопку OK атрымліваем вынік вельмі блізкі да жаданага:
Null - у дадзеным выпадку аналаг пустой ячэйкі ў Excel.
Засталося прыбраць непатрэбныя слупкі і камандай выгрузіць атрыманую табліцу побач з зыходнымі дадзенымі Зачыніць і загрузіць – Зачыніць і загрузіць у… (Зачыніць і загрузіць — Зачыніць і загрузіць у...) таб Галоўная (Дадому):
Атрымліваем у выніку:
Для большай прыгажосці вы можаце наладзіць знешні выгляд атрыманых разумных табліц на ўкладцы Канструктар (Дызайн): усталяваць адзіны каляровы стыль, адключыць кнопкі фільтраў, уключыць вынікі і г.д. Акрамя таго, вы можаце выбраць табліцу з датамі і ўключыць для яе вылучэнне нумароў з дапамогай умоўнага фарматавання на ўкладцы Галоўная — Умоўнае фарматаванне — Каляровыя шкалы (Галоўная — Умоўнае фарматаванне — Каляровыя шкалы):
І самае лепшае, што ў будучыні вы можаце смела рэдагаваць старыя або дадаваць новыя праекты ў зыходную табліцу, а затым абнавіць патрэбную табліцу з датамі правай кнопкай мышы - і Power Query паўторыць усе дзеянні, якія мы зрабілі аўтаматычна. .
Вуаля!
- Дыяграма Ганта ў Excel з выкарыстаннем умоўнага фарматавання
- Каляндар этапаў праекта
- Стварэнне дублікатаў радкоў з дапамогай Power Query