Добре, така че в първите ми дни на анализ на данните имах нужда автоматично да получа уникалните елементи в excel от списък, вместо да премахвам дублирани всеки път. И аз измислих начин да го направя. След това таблото ми за Excel беше още по -динамично от преди. Така че нека да видим как можем да го направим …
За да извлечем списък с уникални стойности от списък, ще използваме INDEX, MATCH и COUNTIF. Ще използвам и IFERROR, само за да има чисти резултати, по избор.
И да, това ще бъде формула на масив … Така че нека го свършим …
Обща формула за извличане на уникални стойности в Excel
{= INDEX (ref_list, MATCH (0, COUNTIF (разширяващ се_изходен_ диапазон, ref_list), 0))}
Ref_list: Списъкът, от който искате да извлечете уникални стойности
Expanding_output_range: Сега това е много важно. Това е диапазонът, в който искате да видите извлечения списък. Този диапазон трябва да има отделно заглавие, което не е част от списъка, а формулата ви ще бъде под заглавието (ако заглавието е в E1, тогава формулата ще бъде в E2).
Сега разширяването означава, че когато плъзнете надолу формулата си, тя трябва да се разшири в обхвата на изхода. За да направите това, трябва да посочите заглавие като $ E $ 1: E1 (Заглавието ми е в E1). Когато го плъзна надолу, той ще се разшири. В Е2 ще бъде $ E $ 1: E1. В E3 ще бъде $ E $ 1: E2. В Е2 ще бъде $ E $ 1: E3 и така нататък.
Сега нека видим пример. Ще стане ясно.
Извличане на уникални стойности Пример за Excel
Тук имам този списък с клиенти в Колона А в обхвата A2: A16. Сега в колона E искам да получа уникални стойности само от клиенти. Сега този диапазон A2: A16 също може да се увеличи, така че искам формулата ми да извлича всяко ново име на клиент от списъка, когато списъкът се увеличава.
Добре, сега, за да извлечете уникални стойности от колона А, напишете тази формула в Клетка Е2, и удари CTRL+SHIFT+ENTER за да го направите формула на масив.
{= ИНДЕКС ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
$ 2: A16: Очаквам този списък да се разшири и може да има нови уникални стойности, които бих искал да извлека. Ето защо го оставих отворен отдолу, а не по абсолютна препратка към A16. Това ще му позволи да се разширява, когато копирате формулата по -долу.
Знаем как работи функцията INDEX и MATCH. Основната част тук е:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Тази формула ще върне масив от 1s и 0s. Винаги когато стойност в диапазона $ E $ 1: E1 се намира в списъка с критерии $ A $ 2: A16, стойността се преобразува в 1 на позицията си в диапазона $ A $ 2: A16.
Сега, използвайки функцията MATCH, търсим стойности 0. Match ще върне позицията на първите 0, намерени в масива, върнат от функцията COUNTIF. След това INDEX ще разгледа $ 2: A16 за връщане на стойността, намерена при индекс, върнат от функцията MATCH.
Може би е малко трудно за схващане, но работи. 0 в края на списъка показва, че няма повече уникални стойности. Ако не виждате 0 в крайна сметка, трябва да копирате формулата в клетките по -долу.
Сега за избягване #NA в можете да използвате IFERROR функцията на excel.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}}
Така че да, можете да използвате тази формула, за да получите уникални стойности от списък. В Excel 2019 с абонамент за Office 365, Microsoft предлага функция, наречена UNIQUE. Той просто приема диапазон като аргумент и връща масив от уникални стойности. Не е наличен в Microsoft Excel 2016 еднократна покупка.
Свали файл:
Формула на Excel за извличане на уникални стойности от списъкФормула на Excel за извличане на уникални стойности от списък
Как да броите уникални стойности в Excel
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност
Как да използвате функцията VLOOKUP в Excel
Как да използвате функцията COUNTIF в Excel
Как да използвате функцията SUMIF в Excel