В тази статия ще научим как да броим стойности, които се появяват само веднъж в списък в Excel.
Сценарий:
Понякога, когато работите с дълъг списък от стойности, това могат да бъдат числа, текстове, дати, часове или валута. И ние трябва да намерим стойностите, които не се повтарят в списъка. Можете или да ги маркирате, като използвате условно форматиране, или да ги преброите. Неповтарящите се стойности се наричат уникални стойности. Например в списък с хранителни стоки има ориз, пшеница, под и пшеница. Текстови стойности Оризът и подът са уникални и не се повтарят, докато пшеницата се повтаря. Нека научим как да използваме това с помощта на Общи функции на Excel за предишната версия на Excel и нова функция Excel 365 Уникална функция
За предишни версии в Excel
Тук ще използваме комбинацията от SUMPRODUCT и COUNTIF функция. Тук функцията COUNTIF отчита броя на стойностите в диапазона. SUMPRODUCT намира сумата от стойности само за съответните 1s.
Синтаксис на формулата:
| = SUMPRODUCT (1 / COUNTIF (диапазон, диапазон)) |
range: списък на стойностите в зададения диапазон
За най -новата версия на Excel: Excel 365
Уникална функция Синтаксис:
| = UNIQUE (масив, [by_col], [точно_однократно]) |
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук
Да кажем, че имаме списък с имена. Сега този списък може да съдържа дублирани стойности. Как можем да броим само уникални имена? Вижте таблицата по -долу.
Напишете тази формула за уникален брой навсякъде на листа. И ще имате уникален брой артикули в даден диапазон.
| = СУМПРОДУКТ (1/COUNTIF (A2: A11, A2: A11)) |
Обяснение:
Първо COUNTIF(A2: A11, A2: A11): Тази част ще върне масив от брой на всеки елемент в клетката. Ако изберете тази част от формулата и натиснете F9, ще видите масива. Ако “radha” се появи два пъти и “Reena” 3, тогава масивът ще бъде. {2,2,3,3,3}. В този пример е {2; 3; 3; 3; 3; 3; 3; 2; 2; 2}.
Сега това 1/COUNTIF (A2: A11, A2: A11) : Тук разделяме 1 на всеки елемент от масива, върнат от COUNTIF (A2: A11, A2: A11). Той отново ще върне масив от дроби. Ако масивът, върнат от функцията Counti, е {2,2,3,3,3}, тогава масивът с дроби ще бъде {0,5,0,5,0,333,0,333,0,333}. Ако ги намерим, ще получим {1,1}. 1 за всеки елемент в масива. За нашия пример е {0,5; 0,333; 0,333; 0,333; 0,333; 0,333; 0,333; 0,5; 0,5; 0,5}.
Сега това СУМПРОДУКТ(1/COUNTIF(A2: A11, A2: A11)): Той ще сумира масива, предоставен от 1/COUNTIF(A2: A11, A2: A11), което ще бъде броят на уникалните стойности в диапазона.
Както можете да видите в горната снимка, броят на уникалните стойности е само 4. Горният метод може да се използва само за преброяване на уникални стойности. Новите функции на Excel извличат всички уникални стойности от списъка.
Извличане на стойности, които се появяват само веднъж в диапазона.
В горния пример получихме уникални стойности от диапазона. Ако една стойност се срещаше веднъж, два пъти или повече, ние имаме само една позиция за нея. Но ако искаме да извлечем стойности, които се срещат само веднъж в диапазона (уникални стойности в диапазона), тогава формулата ще бъде:
| = УНИКАЛНО (A2: A11,1) |
Тук задаваме точно_ един път променлива на TRUE. Това връща стойностите, които са уникални в самия диапазон/масив.
Ето всички бележки за наблюдение, използващи формулата в Excel
Бележки:
- Формулата работи с числа, текст, стойности на дата.
- Използвайте същия тип данни, за да съответствате на критериите в списъка с критерии като име в списъка с имена, дата в стойности на дата.
- Списъците като диапазони на критерии трябва да са със същата дължина или формулата връща #VALUE! грешка.
- Оператори като равен на ( = ), по -малко от равно на ( <= ), по-голяма от ( > ) или не е равно на () може да се използва в аргументи на критерия, само с числа.
- Например съвпадение на времеви стойности, които имат стойност на час 6. Така че можете да използвате "06:*:*". Всеки знак ( *) със звездичка се използва за несигурните минути и секунди.
Надявам се тази статия за Как да използвате функцията … в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Формула на Excel за сортиране на текст : За да сортирате текстови стойности, използвайки формула в Excel, просто използваме функцията COUNTIF. Ето формулата
Формула на Excel за извличане на уникални стойности от списък : За да намерим всички уникални стойности от списък в Excel 2016 и по -стари версии, използваме тази проста формула.
УНИКАЛНА функция за Excel 2016 : Excel 2016 не предоставя УНИКАЛНА функция, но можем да я създадем. Тази функция UDF UNIQUE връща всички уникални стойности от списъка.
Как да броим уникални стойности в Excel с критерии : За да броим уникални стойности в excel с критерии, използваме комбинация от функции. Функцията FREQUENCY е ядрото на тази формула
Как да използвате функцията SUBTOTAL в Excel : Връща SUM, COUNT, AVERAGE, STDEV или PRODUCT за приложени филтрирани данни в Excel.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнявайте задачите си в Excel. Тези преки пътища ще ви помогнат да увеличите ефективността на работата си в Excel.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.