Pivot Таблици - Суми и Подсуми - част 1

Безплатни 20 урока
регистрирай се и научи

Ако сте вече потребител - Регистриран съм
Видео

Тест за преминаване към следващия урок

 
SubTotal или подсума е:
последната стойност в таблицата
всяка стойност в таблицата различна от нула
стойностите, които обобщават сумите от подкатегориите

Въпроси и отговори

  • Как мога да извадя броя на редовете в таблицата?

    Весела Костадинова:

    Ако в Pivot таблицата в редовете са първо регионите и след това служителите, как мога да извадя в Pivota броя на служителите по региони без да ги селектирам с мишката, като това се вижда като някакъв тотал в таблицата? Например колко са общо служителите в Централния район, колко са в Северозападния и т.н. В случая не са много и лесно може да се селектират с мишката и да видят, но ако са много или са динамичен не мога да го направя.

    Краси Кръстев:

    Здравейте, Весела Костадинова.

    Първо доста интересен въпрос за бърз анализ по региони, който анализ да покаже колко са хората които работят в даден регион :)

    Да уточня първо че ако се пробваме да го направим само с Count за даденото поле ( в случая "Име" от урока ), нещата няма да се получат ! :) ( снимка 1 позиция 1 ). Видимо е че това което се брой е общата бройка на полето Field, а не само уникалните имена ( снимка 1 позиция 2 ).

    Снимка_1

    Как може да решим казуса ?

    За начало доста е важно да се даде една доста важна отметка при създаването на Pivot таблицата.Тази отметка е : Add this data to data model ( снимка 2 позиция 1 ). Най - общо тази отметка е доста важна, защото играе роля при начина на интегриране на данни в самата таблица.Идеята е че опцията дава възможност за изграждане на "модел", където данните от много различни източници може да се комбинира чрез създаване на "връзки" между източниците на данни.

    Снимка_2

    След като създадем таблицата следва съответно да сложим полето "Региони" в Rows ( редовете ) ( снимка 3 позиция 1 ) , а във Values ( стойности ) да сложим полето "Име" ( снимка 3 позиция 2 ).

    Снимка_3

    След което кликваме на стрелката на полето "Име" ( снимка 4 позиция 1 ) и избираме Value field Settings ( снимка 4 позиция 2 ).

    Снимка_4

    След което в новоотворилия се прозорец, избираме Distinct count ( снимка 5 позиция 1 ) т.е. да се броят само уникалните ( обособентите ) име на за дадения регион.С ОК потвърждавам промените по настройките ( снимка 5 позиция 2 )

    Снимка_5

    Съответно вече имаме колона в която се сумират уникалните имена за даден регион - снимка 6.

    Снимка_6

    Дали всичко е ОК, ще направя така в базата данни че да поразместя регионите, ще направя така че в Централния регион ще работи само определен човек - снимка 7.

    Снимка_7

    Отивам на Pivot таблицата, кликвам с десен бутон върху нея, след това избирам Refresh - снимка 8.

    Снимка_8

    Резултата е на снимка 9.На нея ясно се вижда че това което направих в базта от данни ( снимка 7 ), а именно сложих за централния регион само един човек и това "Пенка Димитрова"

    Снимка_9

    Надявам се да съм бил полезен с отговора.

    Весела Костадинова:

    Много благодаря за бързия отговор. Всичко изглежда толкова лесно, но не мога да намеря къде да сложа отметката Add this data to data model.При мен не се появява това като опция. Работа на версия 2010 - дали е на друго място или го няма като опция изобщо?

    Краси Кръстев:

    Не опционалността е след 2013 версия :/

    Весела Костадинова:

    Ееее, жалко! Ще карам по стария начин. За тези, които са като мен на версия преди 2013 ще кажа, че сортирам по имена и си правя помощна колона, в която смятам горното име равно ли е на долното име. Така се получава само по една 1-ца на име. И посля сумирам единиците. Надявам се въпроса ми да е бил полезен и за другите :)

    Краси Кръстев:

    Ами съвета ми е ако имаш възможност да си инсталираш наистина по - висока версия.

    А по отношение на това което си направила с помощната колона, да това е един от вариантите.

    Ако тази помощтна колона я правиш до самата Pivot таблица, не ти препоръчвам този подход, защото шанса да добавиш някое име или пък да промениш pivot таблицата е голям.По - скоро тук бих ти дал препоръчал да направиш една помощна колона в базата от данни ( снимка 1 позиция 1 ).Като при нея е използвана същата хитринка само че тук във условието участва и това в кой регион ( снимка 1 ) е човека т.е. ще имаш единици по веднъж за всеки човек и пак за същия човек, но в друг регион.Формулата има следния вид:

    =IF(SUMPRODUCT(($B$2:B2=B2)($C$2:C2=C2))>1,0,1)*

    Снимка_1

    След което тази колона ще можеш да си я вкараш като отделен FIeld в Pivot таблицата.

    Сложи я във Values ( снимка 2 позиция 2 ), и ѝ задай да се сумира ( снимка 2 позиция 3 ), потвърди с ОК промените, след което ще имаш отделна колона, подчертавам че колоната е част от Pivot таблица, която ще ти показва броя на служителите в дадения регион ( снимка 2 позиция 5 ).

    Снимка_2

    Ето и една снимка след бърза промяна в базата от данни:

    Снимка_3

    Няма проблем с разместването намаляването на таблицата и т.н. всичко е част от Pivot таблицата.

    Весела Костадинова:

    Супер! Не я знаех тази формула (SUMPRODUCT).Върши страхотна работа и ще започна да я използвам много в работата си. Дори ми решава един друг проблем, който се получава в случая, когато уникалността определям с моята формула, а именно, ако имам допълнителен филтър - например по месеци, информацията не е коректна, защото излизат 0-лите. Точно щях да ви пиша, но го реших като смених формулата – вместо реда с регионите, взех реда с месеците и се получи! Благодаря много!

    Краси Кръстев:

    Моля! Радвам се, че се е получило.

    Николай Николов:

    Add this data to data model - Имам я тази опция, но не е активна. Сигурно ще ми е от полза при моите таблици, но не знам как да я активирам. Може ли да помогнете? Поздрави!

    Краси Кръстев:

    Здравейте, инж. Николай Николов.

    На 90% предполагам че отметката не е активна, защото файла или може би настройката, която позлвате за AutoSAVE е нагласена за запазване във формат .xls , а не в .xlsx. проверете го това.

    Ето при .xls ( снимка 1 позиция 1 ) какво се случва и при мен ( снимка 1 позиция 2 ), опцията определено не е активна.

    Снимка_1

    Докато вече при .xlsx ( снимка 2 позиция 1 ), опцията е активна и може да се отметне ( снимка 2 позиция 2 ) .

    Снимка_2

    Николай Николов:

    За съжаление на моята версия няма xlsx. xls ми излиза само когато записвам от Аутокад в Ексел. И още mdb, csv, txt. И когато запомнян екселски файл ми липсва този формат.

    Николай Николов:

    Само че на Ексела на лаптопа ми го има. Тои е с Уиндолс 10 и е възможно там това да е причината да го има. Така че ще го имам в предвид да пробвам какво ще се получи. Поздрави и благодаря!

    Краси Кръстев:

    Да така е AutoCAD при DataExtraction създава .xls файл ( снимка 1 позиция 1 ), НО това не означава че не можеш един .xls да го Save as ( снимка 1 позиция 2 ) като .xlsx ( снимка 1 позиция 3 ).

  • Може ли да създам Grand total суми по тримесечие? 

    Atanaska:

    Здравейте,
    Има ли някакъв начин да се прави сумиране на база тримесечие. Примерно за месец Февруари + Март + Април = Grand Total / Май + Юни + Юли = Grand Total.

    Краси Кръстев:

    Здравей, да може да се направи групиране по месеци, и съответно да се включи опцията за това групиране да се показва сумата (Grand Total) за всяка една група.Тези групи са по твое усмотрение може да са Януари Февруари Март //// Април Май ... може да са Януари, Фефруари ///// Март, Април , т.е. може да е тримесечие, двумесечие, шестмесечие и т.н.

    Как става това може да видиш в този урок:

    Pivot Таблици - Суми и Подсуми - част 2

    Снимка_1

    от курса Анализи с Excel.


    Надявам се да съм бил полезен с отговора.

    Atanaska:

    Благодаря ти!

  • Как да добавя" Distrinct Count" ? Еxcel 2016

    Филиз Велиева:

    Искам да преброя колко служители работят в регионите и четох, че това става с тази опция, но при мен тя липсва.

    Kocho:

    Липсва, защото това е "обикновена" пивот таблица. Distinct Count е наличен: 1. Само за версии 2013 и нагоре 2. Ако таблицата е вкарана в Data Model-а, т.е. Power Pivot

    Как става? Правиш си нормална пивот таблица: Insert > Pivot Table В менюто Create PivotTable най-отдолу в ляво трябва да сложиш отметка пред Add this to the Data Model даваш ОК и готово

    Пивот таблицата изглежда по същия начин както преди, само че сега вече е налично и Distinct Count

    Разликата, разбира се не е само това, но да не отваряме темата за Power Pivot, че е много голяма

    Филиз Велиева:

    Благодаря за бързият отговор, получи се :)