Обобщените таблици на Excel са невероятни (знам, че споменавам това всеки път, когато пиша за обобщени таблици, но е вярно).
С основно разбиране и малко плъзгане и пускане можете да свършите натоварване с кофа за няколко секунди.
Въпреки че много неща могат да бъдат направени с няколко кликвания в обобщените таблици, има някои неща, които ще се нуждаят от няколко допълнителни стъпки или малко работа.
И едно такова нещо е да се броят отделни стойности в обобщена таблица.
В този урок ще ви покажа как да броите отделни стойности, както и уникални стойности в обобщена таблица на Excel.
Но преди да скоча как да преброя отделните стойности, важно е да разбера разликата между „различен брой“ и „уникален брой“
Различен брой срещу уникален брой
Въпреки че това може да изглежда като едно и също нещо, не е.
По -долу е даден пример, където има набор от имена и аз изброих уникални и отделни имена отделно.
Уникални стойности/имена са тези, които се появяват само веднъж. Това означава, че всички имена, които се повтарят и имат дубликати, не са уникални. Уникални имена са изброени в колона C в горния набор от данни
Различни стойности/имена са тези, които се срещат поне веднъж в набора от данни. Така че, ако едно име се появи три пъти, то все още се брои като едно отделно име. Това може да се постигне чрез премахване на дублиращите се стойности/имена и запазване на всички отделни. Различните имена са изброени в колона B в горния набор от данни.
Въз основа на това, което съм видял, повечето пъти, когато хората казват, че искат да получат уникалния брой в обобщена таблица, те всъщност означават различен брой, което покривам в този урок.Пребройте отделните стойности в обобщена таблица на Excel
Да предположим, че имате данни за продажбите, както е показано по -долу:
Щракнете тук, за да изтеглите примерния файл и продължете
С горния набор от данни, да речем, че искате да намерите отговора на следните въпроси:
- Колко търговски представители има във всеки регион (което не е нищо друго освен различен брой търговски представители във всеки регион)?
- Колко представители по продажбите са продали принтера през 2021-2022 г.?
Докато обобщените таблици могат незабавно да обобщят данните с няколко кликвания, за да получите броя на отделните стойности, ще трябва да направите още няколко стъпки.
Ако използвате Excel 2013 или версии след това, има вградена функционалност в обобщена таблица, която бързо ви дава различен брой. И ако използвате Excel 2010 или версии преди това, ще трябва да промените изходните данни, като добавите помощна колона.В този урок са разгледани следните два метода:
- Добавяне на помощна колона в оригиналния набор от данни за отчитане на уникални стойности (работи във всички версии).
- Добавяне на данните към модел на данни и използване на опция Различно броене (налично в Excel 2013 и версии след това).
Има и трети метод, който Роджър показва в тази статия (който той нарича метод Pivot the Pivot Table).
Да започваме!
Добавяне на помощна колона в набора от данни
Забележка: Ако използвате Excel 2013 и по -нови версии, пропуснете този метод и преминете към следващия (тъй като той използва вградена функционалност на обобщена таблица - Отличен брой).Това е лесен начин за преброяване на отделни стойности в обобщената таблица, тъй като трябва само да добавите помощна колона към изходните данни. След като сте добавили помощна колона, можете да използвате този нов набор от данни за изчисляване на отделния брой.
Въпреки че това е лесно решение, има някои недостатъци на този метод (обхванати по -късно в този урок).
Нека първо ви покажа как да добавите помощна колона и да получите отделен брой.
Да предположим, че имам набор от данни, както е показано по -долу:
Добавете следната формула в колона F и я приложите за всички клетки, които имат данни в съседните колони.
= АКО (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)
Горната формула използва функцията COUNTIFS, за да брои броя пъти, когато едно име се появява в дадения регион. Също така имайте предвид, че диапазонът на критериите е $ C $ 2: C2 и $ B $ 2: B2. Това означава, че той продължава да се разширява, докато слизате надолу по колоната.
Например в клетка E2 диапазоните на критериите са $ C $ 2: C2 и $ B $ 2: B2, а в клетка E3 тези диапазони се разширяват до $ C $ 2: C3 и $ B $ 2: B3.
Това гарантира, че функцията COUNTIFS отчита първата инстанция на име като 1, втората инстанция на името като 2 и т.н.
Тъй като искаме само да получим различни имена, се използва функцията IF, която връща 1, когато име се появи за регион за първи път и връща 0, когато се появи отново. Това гарантира, че се броят само различни имена, а не повторенията.
По -долу е как би изглеждал вашият набор от данни, когато сте добавили помощната колона.
Сега, когато променихме изходните данни, можем да използваме това, за да създадем обобщена таблица и да използваме помощната колона, за да получим отделния брой на търговските представители във всеки регион.
По -долу са описани стъпките за това:
- Изберете всяка клетка в набора от данни.
- Щракнете върху раздела Вмъкване.
- Щракнете върху Pivot Table (или използвайте клавишната комбинация - ALT + N + V)
- В диалоговия прозорец Създаване на обобщена таблица се уверете, че таблицата/диапазонът е правилен (и включва помощната колона) и „Нов работен лист“ в избрания.
- Щракнете върху OK.
Горните стъпки биха вмъкнали нов лист, който има обобщена таблица.
Плъзнете полето „Регион“ в областта Редове и полето „D Брой“ в областта Стойности.
Ще получите обобщена таблица, както е показано по -долу:
Сега можете да промените заглавката на колоната от „Сума от D броя“ на „Търговски представител“.
Недостатъци на използването на помощна колона:
Въпреки че този метод е доста ясен, трябва да подчертая някои недостатъци, които идват с промяната на изходните данни в обобщена таблица:
- Източникът на данни с помощната колона не е толкова динамичен, колкото обобщена таблица. Въпреки че можете да нарязвате и нарязвате данните по какъвто искате начин с обобщена таблица, когато използвате помощна колона, губите част от тази способност. Да приемем, че добавяте помощна колона, за да получите броя на отделни търговски представители във всеки регион. Какво ще стане, ако искате също така да получите отчетлив брой на продавачите, продаващи принтери. Ще трябва да се върнете към изходните данни и да промените формулата на помощната колона (или да добавите нова помощна колона).
- Тъй като добавяте повече данни към източника на обобщена таблица (който също се добавя към кеша на оборотната таблица), това може да доведе до по -голям размер на Excel файл.
- Тъй като използваме формула на Excel, тя може да забави работната ви книга на Excel в случай, че имате хиляди редове с данни.
Добавете данни към модела на данните и обобщете, като използвате различен брой
Обобщената таблица добави нова функционалност в Excel 2013, която ви позволява да получите различен брой, докато обобщавате набора от данни.
В случай, че използвате предишна версия, няма да можете да използвате този метод (както трябва да опитате да добавите помощната колона, както е показано в метода над този).
Да предположим, че имате набор от данни, както е показано по -долу и искате да получите броя на уникалните търговски представители във всеки регион.
По -долу са стъпките за получаване на отделна стойност на преброяване в обобщената таблица:
- Изберете всяка клетка в набора от данни.
- Щракнете върху раздела Вмъкване.
- Щракнете върху Pivot Table (или използвайте клавишната комбинация - ALT + N + V)
- В диалоговия прозорец Създаване на обобщена таблица се уверете, че таблицата/диапазонът е правилен и Нов работен лист в Избрано.
- Поставете отметка в квадратчето, което гласи - „Добавете тези данни към модела на данните“
- Щракнете върху OK.
Горните стъпки биха вмъкнали нов лист, който има новата обобщена таблица.
Плъзнете региона в областта Редове и Търговския представител в областта Стойности. Ще получите обобщена таблица, както е показано по -долу:
Горната обобщена таблица дава общия брой на търговските представители във всеки регион (а не отделния брой).
За да получите отделния брой в обобщената таблица, следвайте стъпките по -долу:
- Щракнете с десния бутон върху която и да е клетка в графата „Брой представители на продажбите“.
- Щракнете върху Настройки на поле за стойност
- В диалоговия прозорец Настройки на полето за стойност изберете „Различен брой“ като вид изчисление (може да се наложи да превъртите списъка надолу, за да го намерите).
- Щракнете върху OK.
Ще забележите, че името на колоната се променя от „Брой представители на продажбите“ на „Различен брой представители на продажбите“. Можете да го промените на каквото искате.
Някои неща, които знаете, когато добавяте данните си към модела на данните:
- Ако запишете данните си в модела с данни и след това отворите в по -стара версия на Excel, той ще ви покаже предупреждение - „Някои функции на обобщена таблица няма да бъдат запазени“. Възможно е да не виждате отделния брой (и модела на данните), когато се отваря в по -стара версия, която не го поддържа.
- Когато добавите данните си към модел на данни и направите обобщена таблица, тя няма да показва опциите за добавяне на изчислени полета и изчислени колони.
Щракнете тук, за да изтеглите примерния файл
Какво става, ако искате да броите уникални стойности (а не различни стойности)?
Ако искате да броите уникални стойности, нямате вградена функционалност в обобщената таблица и ще трябва да разчитате само на помощни колони.
Запомнете - Уникалните стойности и отделните стойности не са еднакви. Щракнете тук, за да разберете разликата.Един пример може да бъде, когато имате набора от данни по -долу и искате да разберете колко търговски представители са уникални за всеки регион. Това означава, че те работят само в един конкретен регион, а не в другите.
В такива случаи трябва да създадете една от повече от една помощна колона.
За този случай формулата по -долу върши работа:
= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)
Горната формула проверява дали име на търговски представител се среща само в един регион или в повече от един регион. Той прави това, като брои броя на появата на име в регион и го разделя на общия брой на появата на името. Ако стойността е по -малка от 1, това показва, че името се среща в два или повече от два региона.
В случай, че името се среща в повече от един регион, той връща 0, в противен случай връща единица.
Формулата също така проверява дали името се повтаря в същия регион или не. Ако името се повтаря, само първият екземпляр на името връща стойността 1, а всички останали екземпляри връщат 0.
Това може да изглежда малко сложно, но отново зависи от това какво се опитвате да постигнете.
Така че, ако искате да броите уникални стойности в обобщена таблица, използвайте помощни колони и ако искате да броите различни стойности, можете да използвате вградената функционалност (в Excel 2013 и по -нови) или можете да използвате помощна колона.
Щракнете тук, за да изтеглите примерния файл
Може да харесате и следните уроци за обобщена таблица:
- Как да филтрирате данни в обобщена таблица в Excel
- Как да групирате дати в обобщени таблици в Excel
- Как да групирате числа в обобщена таблица в Excel
- Как да приложите условно форматиране в обобщена таблица в Excel
- Слайсери в обобщена таблица на Excel
- Как да опресните обобщена таблица в Excel
- Изтрийте обобщена таблица в Excel
