Бројање различитих вредности у Екцел изведеној табели (једноставан корак-по-корак водич)

Екцел заокретне табеле су невероватне (знам да ово помињем сваки пут када пишем о изведеним табелама, али то је истина).

Уз основно разумевање и мало повлачења и испуштања, можете обавити велики део посла за неколико секунди.

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

Једна од таквих ствари је пребројавање различитих вредности у изведеној табели.

У овом водичу ћу вам показати како бројати различите вредности као и јединствене вредности у Екцел изведеној табели.

Али пре него што скочим у то како да бројим различите вредности, важно је разумети разлику између „различитог броја“ и „јединственог бројања“

Дистинцт Цоунт Вс Уникуе Цоунт

Иако ово може изгледати као иста ствар, није.

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

Јединствене вредности/имена су они који се јављају само једном. То значи да сва имена која се понављају и имају дупликате нису јединствена. Јединствена имена наведена су у колони Ц у горњем скупу података

Различите вредности/имена су они који се појављују најмање једном у скупу података. Дакле, ако се име појави три пута, и даље се рачуна као једно посебно име. Ово се може постићи уклањањем дуплираних вредности/назива и задржавањем свих различитих. Различита имена наведена су у колони Б у горњем скупу података.

На основу онога што сам видео, у већини случајева када људи кажу да желе да добију јединствени број у изведеној табели, они заправо мисле на различит број, што и покривам у овом водичу.

Пребројите различите вредности у Екцел изведеној табели

Претпоставимо да имате податке о продаји као што је приказано испод:

Кликните овде да преузмете датотеку примера и пратите је

Уз горњи скуп података, рецимо да желите да пронађете одговор на следећа питања:

1. Колико продајних представника има у сваком региону (што није ништа друго до различит број продајних представника у сваком региону)?
2. Колико је продајних представника продало штампач у периоду 2021-2022?

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

Ако користите Екцел 2013 или верзије након тога, постоји уграђена функција у изведеној табели која вам брзо даје посебан број. А ако користите Екцел 2010 или верзије пре тога, мораћете да измените изворне податке додавањем помоћне колоне.

Следеће две методе су обрађене у овом водичу:

• Додавање помоћне колоне у оригинални скуп података за бројање јединствених вредности (ради у свим верзијама).
• Додавање података у модел података и коришћење опције Дистинцт Цоунт (доступно у програму Екцел 2013 и верзијама након тога).

Постоји и трећа метода коју Рогер приказује у овом чланку (коју назива методом заокретања заокретне табеле).

Хајде да почнемо!

Додавање колоне помоћника у скуп података

Напомена: Ако користите Екцел 2013 и новије верзије, прескочите овај метод и пређите на следећи (јер користи уграђену функцију изведене табеле - Дистинцт Цоунт).

Ово је једноставан начин за пребројавање различитих вредности у изведеној табели јер је потребно само да додате помоћну колону изворним подацима. Након што додате помоћну колону, можете користити овај нови скуп података за израчунавање различитог броја.

Иако је ово једноставно заобилазно решење, постоје неки недостаци ове методе (описане касније у овом водичу).

Дозволите ми да вам прво покажем како да додате помоћну колону и добијете посебан број.

Претпоставимо да имам скуп података као што је приказано испод:

Додајте следећу формулу у колону Ф и примените је на све ћелије које имају податке у суседним колонама.

= АКО (ЦОУНТИФС ($ Ц $ 2: Ц2, Ц2, $ Б $ 2: Б2, Б2)> 1,0,1)

Горња формула користи функцију ЦОУНТИФС за пребројавање броја појављивања имена у датом региону. Такође имајте на уму да је опсег критеријума $ Ц $ 2: Ц2 и $ Б $ 2: Б2. То значи да се наставља ширити док се спуштате низ колону.

На пример, у ћелији Е2, опсези критеријума су $ Ц $ 2: Ц2 и $ Б $ 2: Б2, а у ћелији Е3 ови опсези се проширују на $ Ц $ 2: Ц3 и $ Б $ 2: Б3.

Ово осигурава да функција ЦОУНТИФС броји прву инстанцу имена као 1, другу инстанцу имена као 2 итд.

Пошто желимо само да добијемо различита имена, користи се функција ИФ која враћа 1 када се име појави за регион први пут и враћа 0 када се поново појави. Ово осигурава да се броје само различита имена, а не понављања.

У наставку је приказано како би ваш скуп података изгледао када додате помоћну колону.

Сада када смо изменили изворне податке, можемо ово користити за креирање изведене табеле и помоћу колоне помоћника да добијемо различит број продајних представника у сваком региону.

У наставку су наведени кораци за то:

1. Изаберите било коју ћелију у скупу података.
2. Кликните на картицу Уметни.
3. Кликните на заокретну табелу (или користите пречицу на тастатури - АЛТ + Н + В)
4. У дијалогу Креирање изведене табеле проверите да ли је табела/опсег исправна (и укључује помоћну колону) и да је изабран „Нови радни лист“.
5. Притисните ОК.

Горе наведени кораци би уметнули нови лист који има изведену табелу.

Превуците поље „Регион“ у области „Ровс“ и „Д Цоунт“ у области „Валуес“.

Добићете изведену табелу као што је приказано испод:

Сада можете променити заглавље колоне са „Збир Д броја“ у „Представник продаје“.

Недостаци коришћења колоне помоћника:

Иако је овај метод прилично јасан, морам да истакнем неколико недостатака који долазе са изменом изворних података у изведеној табели:

• Извор података са помоћном колоном није тако динамичан као изведена табела. Иако можете да исечете и исечете податке на било који начин помоћу изведене табеле, када користите помоћну колону, губите део те способности. Рецимо да додате помоћну колону да бисте добили број различитих продајних представника у сваком региону. Шта ако желите и да добијете јасан број продајних представника који продају штампаче. Мораћете да се вратите на изворне податке и измените формулу колоне помоћника (или додате нову колону помоћника).
• Пошто додајете више података у извор изведене табеле (који се такође додаје у изведену кеш меморију), то може довести до веће величине Екцел датотеке.
• Пошто користимо Екцел формулу, то може успорити радну свеску програма Екцел у случају да имате хиљаде редова података.

Додајте податке моделу података и резимирајте помоћу различитог броја

Заокретна табела је додала нову функционалност у програму Екцел 2013 која вам омогућава да добијете различит број док сажимате скуп података.

У случају да користите претходну верзију, нећете моћи да користите овај метод (требало би да покушате да додате помоћну колону као што је приказано у методи изнад ове).

Претпоставимо да имате скуп података као што је приказано испод и желите да добијете број јединствених продајних представника у сваком региону.

Испод су кораци за добијање посебне вредности бројача у изведеној табели:

1. Изаберите било коју ћелију у скупу података.
2. Кликните на картицу Уметни.
3. Кликните на заокретну табелу (или користите пречицу на тастатури - АЛТ + Н + В)
4. У дијалогу Креирање изведене табеле проверите да ли је табела/опсег исправна и да је нови радни лист у Изабрано.
5. Означите поље са натписом „Додај ове податке у модел података“
6. Притисните ОК.

Горе наведени кораци би уметнули нови лист који има нову изведену табелу.

Повуците регију у подручју редова и продајног представника у подручју вриједности. Добићете изведену табелу као што је приказано испод:

Горња изведена табела даје укупан број продајних представника у сваком региону (а не засебан број).

Да бисте добили јасан број у изведеној табели, следите доле наведене кораке:

1. Кликните десним тастером миша на било коју ћелију у колони „Број продајног представника“.
2. Кликните на Поставке поља вредности
3. У дијалогу Поставке поља вредности изаберите „Различит број“ као врсту израчуна (можда ћете морати да се померите надоле по листи да бисте је пронашли).
4. Притисните ОК.

Приметићете да се назив колоне мења из „Број представника продаје“ у „Различит број представника продаје“. Можете га променити у шта год желите.

Неке ствари знате када додате податке у модел података:

• Ако податке сачувате у моделу података, а затим отворите у старијој верзији програма Екцел, приказаће вам се упозорење - „Неке функције изведене табеле неће бити сачуване“. Можда нећете видети различит број (и модел података) када се отвори у старијој верзији која га не подржава.
• Када додате податке у модел података и направите заокретну табелу, неће се приказати опције за додавање израчунатих поља и израчунатих колона.

Кликните овде за преузимање датотеке примера

Шта ако желите да бројите јединствене вредности (а не различите вредности)?

Ако желите да бројите јединствене вредности, немате никакву уграђену функцију у изведеној табели и мораћете да се ослоните само на помоћне колоне.

Запамтите - јединствене вредности и различите вредности нису исте. Кликните овде да бисте сазнали разлику.

Један пример би могао бити када имате доњи скуп података и желите да сазнате колико је представника продаје јединствено за сваки регион. То значи да они послују само у једном одређеном региону, а не и у другом.

У таквим случајевима морате да креирате једну од више колона помоћника.

У овом случају, формула испод помаже:

= ИФ (ИФ (ЦОУНТИФС ($ Ц $ 2: $ Ц $ 1001, Ц2, $ Б $ 2: $ Б $ 1001, Б2)/ЦОУНТИФ ($ Ц $ 2: $ Ц $ 1001, Ц2) 1,0,1), 0)

Горња формула проверава да ли се назив продајног представника налази само у једном региону или у више од једног региона. То чини тако што броји број појављивања имена у региону и дели га укупним бројем појављивања имена. Ако је вредност мања од 1, то значи да се име појављује у два или више од два региона.

У случају да се име појављује у више од једне регије, враћа 0, у супротном враћа један.

Формула такође проверава да ли се назив понавља у истом региону или не. Ако се име понови, само прва инстанца имена враћа вредност 1, а све остале инстанце 0.

Ово може изгледати помало сложено, али опет зависи од тога шта покушавате да постигнете.

Дакле, ако желите да бројите јединствене вредности у изведеној табели, користите помоћне колоне, а ако желите да бројите различите вредности, можете да користите уграђену функционалност (у програму Екцел 2013 и новије верзије) или можете да користите помоћну колону.

Кликните овде за преузимање датотеке примера

Можда ће вам се допасти и следећи водичи за изведену табелу:

• Како филтрирати податке у изведеној табели у програму Екцел
• Како груписати датуме у изведеним табелама у Екцелу
• Како груписати бројеве у изведеној табели у програму Екцел
• Како применити условно обликовање у изведеној табели у програму Екцел
• Резачи у Екцел изведеној табели
• Како освежити изведену табелу у програму Екцел
• Избришите изведену табелу у програму Екцел