Как да броите уникални стойности в Excel

Как да броите уникални стойности в Excel

Наборите от данни в Excel често съдържат една и съща стойност няколко пъти в колона. Понякога може да бъде полезно да се знае колко уникални стойности има в колона. Например, ако управлявате магазин и имате електронна таблица с всичките си транзакции, може да искате да определите колко уникални клиенти имате, вместо да броите всяка отделна транзакция.





Възможно е да направите това, като преброите уникални стойности в Excel, като използвате методите, които ще ви разкажем по -долу.



Премахване на дублирани данни от колона

Бърз и мръсен начин за преброяване на уникалните стойности в Excel е да премахнете дубликатите и да видите колко записи са останали. Това е добър вариант, ако имате нужда от отговор бързо и не е нужно да проследявате резултата.





Копирайте данните в нов лист (за да не изтриете случайно всички необходими данни). Изберете стойностите или колоната, от която искате да премахнете дублиращите се стойности. В Инструменти за данни раздел на Данни изберете раздела Премахване на дубликати . Това премахва всички дублирани данни и оставя само уникалните стойности.

Същият процес работи, ако информацията е разделена между две колони. Разликата е, че трябва да изберете и двете колони. В нашия пример имаме колона за първото име и втора за фамилното име.



Ако искате да проследите броя на уникалните стойности, по -добре е вместо това да напишете формула. Ще ви покажем как да направите това по -долу.

Свързани: Как да филтрирате в Excel, за да покажете данните, които искате



Пребройте уникалните стойности с формула на Excel

За да броим само уникални стойности, трябва да комбинираме няколко функции на Excel. Първо, трябва да проверим дали всяка стойност е дубликат, след това трябва да преброим останалите записи. Трябва също да използваме функция за масив.

Ако просто търсите отговора, използвайте тази формула, като замените всеки екземпляр на A2: A13 с клетките, които искате да използвате:



{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

Как стигнахме там е малко сложно. Така че, ако искате да разберете защо тази формула работи, ще я разделим по части по -долу.

Обясняване на функция от масив

Нека първо да обясним какво е масив. Масивът е единична променлива, която съдържа множество стойности. Това е все едно да се позовавате на куп Excel клетки наведнъж, вместо да се позовавате на всяка клетка поотделно.

Това е странно разграничение от наша гледна точка. Ако кажем на формула да разглежда клетките A2: A13 нормално или като масив, данните ни изглеждат еднакви. Разликата е в това как Excel третира данните зад кулисите. Това е толкова фина разлика, че най -новите версии на Excel вече дори не правят разлика между тях, въпреки че по -старите версии го правят.

За нашите цели е по -важно да знаем как можем да използваме масиви. Ако имате най -новата версия на Excel, тя автоматично съхранява данни като масив, когато е по -ефективно да го направите. Ако имате по -стара версия, когато приключите с писането на формулата, натиснете Ctrl + Shift + Enter . След като направите това, формулата ще бъде заобиколена от къдрави скоби, за да покаже, че е в режим на масив.

Представяне на функцията FREQUENCY

Функцията FREQUENCY ни казва колко пъти дадено число се появява в списък. Това е чудесно, ако работите с числа, но нашият списък е текстов. За да използваме тази функция, първо трябва да намерим начин да преобразуваме текста си в числа.

Ако се опитвате да преброите уникалните стойности в списък с числа, можете да пропуснете следващата стъпка.

Използване на функцията MATCH

Функцията MATCH връща позицията на първото появяване на стойност. Можем да използваме това, за да преобразуваме нашия списък с имена в числови стойности. Трябва да знае три части информация:

  • Каква стойност търсите?
  • Какъв набор от данни проверявате?
  • Търсите ли стойности по -високи, по -ниски или равни на целевата стойност?

В нашия пример искаме да потърсим всяко име на нашите клиенти в нашата електронна таблица на Exel, за да видим дали точното им име се появява отново на друго място.

андроид режим на възстановяване изтрийте кеша дял

В горния пример търсим в нашия списък (A2: A13) за Tiah Gallagher (A2) и искаме точно съвпадение. 0 в последното поле указва, че трябва да е точно съвпадение. Нашият резултат ни казва къде в списъка името се е появило първо. В този случай това беше първото име, така че резултатът е 1.

Проблемът с това е, че се интересуваме от всички наши клиенти, а не само от Тиа. Но ако се опитаме да търсим A2: A13 вместо само A2, получаваме грешка. Тук функциите на масива са удобни. Първият параметър може да приема само една променлива или иначе връща грешка. Но масивите се третират като една единична променлива.

Сега нашата функция казва на Excel да провери за съвпадения за целия ни масив. Но изчакайте, нашият резултат не се е променил! Все още пише 1. Какво става тук?

Нашата функция връща масив. Той преминава през всеки елемент в нашия масив и проверява за съвпадения. Резултатите от всички имена се записват в масив, който се връща като резултат. Тъй като една клетка показва само една променлива наведнъж, тя показва първата стойност в масива.

Можете да проверите това сами. Ако промените първия диапазон на A3: A13, резултатът ще се промени на 2. Това е така, защото името на Eiliyah е второ в списъка и тази стойност се запазва първа в масива сега. Ако промените първия диапазон на A7: A13, отново получавате 1, защото името на Тиа се появява за първи път на първата позиция от набора от данни, които проверяваме.

Свързани: Формули на Excel, които ще ви помогнат да решите проблеми от реалния живот

Използване на функцията FREQUENCY

Сега, след като сме променили имената в числови стойности, можем да използваме функцията FREQUENCY. Подобно на MATCH, той изисква цел за търсене и набор от данни за проверка. Също подобно на MATCH, не искаме да търсим само една стойност, искаме функцията да проверява всеки елемент в нашия списък.

Целта, която искаме да проверява функцията FREQUENCY, е всеки елемент от масива, който връща нашата функция MATCH. И искаме да проверим набора от данни, върнат от функцията MATCH. По този начин изпращаме функцията MATCH, която създадохме по -горе, и за двата параметъра.

Ако търсите уникални числа и сте пропуснали предишната стъпка, ще изпратите диапазона от числа като двата параметъра. За да търсите във всички числа в списъка си, ще трябва да използвате и функция за масив, така че не забравяйте да натиснете Ctrl + Shift + Enter след като сте въвели формулата, ако използвате по -стара версия на Excel.

Сега нашият резултат е 2. Отново нашата функция връща масив. Той връща масив от броя на появяванията на всяка уникална стойност. Клетката показва първата стойност в масива. В този случай името на Тиа се появява два пъти, така че върнатата честота е 2.

Използване на функцията IF

Сега нашият масив има същия брой стойности, както ние имаме уникални стойности. Но не сме приключили напълно. Нуждаем се от начин да добавим това. Ако преобразуваме всички стойности в масива в 1 и ги сумираме, тогава най -накрая ще разберем колко уникални стойности имаме.

Можем да създадем IF функция, която променя всички стойности над нула на 1. Тогава всички стойности ще бъдат равни на 1.

За да направим това, искаме нашата функция IF да провери дали стойностите в нашия масив FREQUENCY са по -големи от нула. Ако е вярно, трябва да върне стойността 1. Ще забележите, че сега първата стойност в масива се връща като една.

Използване на функцията SUM

На финала сме! Последната стъпка е да СУМИРАТЕ масива.

Увийте предишната функция във функция SUM. Готово! Така че нашата последна формула е:

{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

Преброяване на уникални записи в Excel

Това е усъвършенствана функция, която изисква много познания за Excel. Опитът може да бъде плашещ. Но след като е настроен, той може да бъде много полезен, така че може да си струва да проработите нашето обяснение, за да сте сигурни, че го разбирате.

Ако не се налага толкова често да броите уникални записи, бързият и мръсен съвет за премахване на дублиращи се стойности ще свърши работа!

Дял Дял Туит електронна поща Как да копирате формули в Microsoft Excel

Изучаването на всички най -добри методи за копиране и поставяне на формули във вашата електронна таблица на Excel е чудесен начин да започнете да пестите време.

Прочетете Напред Свързани теми
  • Производителност
  • Електронна таблица
  • Microsoft Excel
  • Анализ на данни
За автора Дженифър Сийтън(21 статии са публикувани)

Дж. Сийтън е научен писател, специализиран в разбиването на сложни теми. Има докторска степен от университета в Саскачеван; нейното изследване се фокусира върху използването на базирано на игри обучение за повишаване на ангажираността на учениците онлайн. Когато тя не работи, ще я намерите с четене, игра на видео игри или градинарство.

Още от Дженифър Сийтън

Абонирайте се за нашия бюлетин

Присъединете се към нашия бюлетин за технически съвети, рецензии, безплатни електронни книги и изключителни оферти!

Щракнете тук, за да се абонирате