14 начина за извличане на уникални стойности в Excel

Извличане на списък с уникални стойности в Excel е сравнително често срещана операция. В тази статия ще подходя като един истински Ексел маниак и ще дам максимално много начини за извличане на списък с уникални стойности. Не че всичките ще ви потрябват някога, или пък всичките са много ефективни. Реално 2-3 от тях са напълно достатъчни. Но ей така – за майтапа, а и за да покажа за пореден път колко гъвкав и многообразен инструмент е Excel.

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

Съдържание:

Малък пример за контекст

Имаме произволен списък с имена. Както виждате – много от тях се повтарят. Искаме да направим списък с уникалните имена, в който всяко име присъства само един път. Обърнете внимание, че някои от имената са изписани по различен начин. Например DRAGAN, Dragan или Dina, DINA, DinA. В някои случаи искаме уникалният списък да отчита разликата в изписването, в други – това не важно. Ще разгледаме и двата варианта.

1. Извличане на списък с уникални стойности в Excel по бабешкия ръчен начин

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

Можем да направим известна оптимизация като маркираме няколко групи стойности едновременно.

Ако не знаете – става като задържаме бутоните GTRL или SHIFT докато кликаме върху съответните клетки (CTRL за несъседни области, SHIFT за последователни/съседни области).

Започвам с този начин, защото това все пак Е начин макар и силно неефективен. Той отнема много време, скучен и изтощителен е за потребителя. И при промяна на изходните данни, трябва да се мине през същата дълга и скучна процедура наново, за да се извлече нов списък с уникални стойности.

Методът има обаче едно много голямо предимство пред всички останали – дава възможност ОСЪЗНАТО да елиминираме стойности и да се запознаем със списъка. Понякога това може да ви е полезно. Особено в случай като на картинката по-долу. Например името “Dina” е изписано по няколко начина. При сортиране те ясно се подреждат един над друг и така визуално можем да ги различим. Както ще видим по-надолу – не всеки метод ще направи такава разлика.

Голямото ограничение на този метод е, че той наистина е изцяло ръчен. При 20-30-100 стойности може и да свърши работа. Но ако работите с хиляди редове – вече става изключително трудно и податливо на грешки.

 2. Уникални стойности в Excel с обикновен филтър

Това е известно подобрение на горния метод, при което използваме най-обикновен филтър, за да филтрираме една по една стойностите.

Така си спестяваме сортирането и ръчното определяне къде започва и къде свършва една стойност. Отново имаме възможност визуално да разпознаем еднакви стойности, изписани по различен начин (Dragan и DRAGAN).

Този метод има всички недостатъци на първия, но е една идея по-бърз и удобен. Освен това дава възможност да филтрираме само стойности по някакъв допълнителен критерий. Представете си например, че става дума за списък с футболисти и някаква статистика за тяхното представяне. С филтър можем първо да филтрираме само тези, които играят в дадено първенство и след това да извадим уникален списък с техните имена.

3. Уникални стойности с advanced filter

Това съм почти сигурен, че не го знаете. Даже и чатГПТ като го питах първия път не го спомена. След това му казах, че мога да използвам и advanced filter за извличане на списък с уникални стойности в Excel и той се съгласи. Няколко дни по-късно като отворих нова чат и го попитах за всички методи, които знае за извличане на списък с уникални стойности в Excel, той вече споменаваше и този. С известна нескромност мога да твърдя, че съм обучил това чудо на нещо (умря онази дето че хвалеше, та започнах сам).

Как работи?

Най-напред отиваме на Data > Sort & Filter > Advanced

Това отваря менюто за Advanced filter, където най-напред избираме областта с данните (List range). В случая това е А1:А40. Обърнете внимание, че областта включва и заглавието на колоната. Може и без него, но ще видите какво ще стане след малко.

След това указваме списъкът с уникални стойности да се копира на друго място, за да не развалим изходните данни (Copy to another location). Съответно в полето Copy to посочваме произволна клетка, от която ще започне списъкът с уникални стойности. В случая съм избрал С1. Може да бъде всяка друга клетка, хубаво е да бъде добре разделена от изходните данни.

Най-накрая слагаме отметка и на Unique records only.

И когато цъкнем ОК – бум. Нашият списък е готов

Нарочно съм оцветил с жълто стойностите DRAGAN и DinA. Искам да обърна внимание как работи advanced filter. По същество той прави същото като в метод 2 по-горе, само че автоматично вместо нас – филтрира стойностите и после за всяка от тях изтрива всички редове без първия. Затова му се вика advanced.

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

4. Remove duplicates

По-опитните потребители вероятно вече се питат „Абе този какво ни занимава с глупости, когато си има специален инструмент за извличане на списък с уникални стойности в Excel?“. И може би са прави. Защото наистина – ако кликнем някъде в колоната с изходните данни и след това Data > Data tools > Remove Duplicates

Това отваря менюто Remove Duplicates

Ексел е толкова добре направен, че сам разбира колко е голяма областта. Трябва само да му укажем кои колони да гледа (да, можем да направим уникални стойности по повече от една колона) и дали имаме заглавен ред или не.

Когато цъкнем ОК – Excel любезно ни предоставя любопитна статистика колко стойности е изтрил и колко уникални са останали. Това може да бъде много полезно в определени случаи. Например, ако очаквате да останат 8 уникални стойности вместо 10 – значи имаме проблем.

Ето го и списъкът с уникални стойности. Отново обърнете внимание, че Excel връща DRAGAN и DinA. т.е. работи по същия начин както при advanced филтрирането.

Предимствата на Remove Duplicates са очевидни – бърз, лесен за приложение, с минимално човешко усилие. Идеален за еднократни задачи, дори и списъкът ви да е десетки хиляди редове. (Специално за целите на статията направих списък от 500 хиляди реда и Remove Duplicates върна уникалните стойности за по-малко от секунда).

При все това методът има и няколко сериозни недостатъка:

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

5. Пивот таблица

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

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

Това автоматично създава списък с уникалните стойности. При това – сортиран.

Предимството на този метод е, че е сравнително автоматичен. Изисква първоначално настройване на пивот таблицата и след това рефреш при промяна в данните. Има един капан – пивот таблицата трябва да е така настроена, че да обхваща добавяне на нови редове в данните. Най-лесно става като се превърнат изходните данни в обект таблица. При този метод също така изходните данни не се променят, изтриват или повреждат.

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

Може би ставам досаден, но и при този метод Excel не прави разлика между DRAGAN, dragan, DRagaN и други възможни вариации на едно и също име.

6. Извличане на уникални стойности с COUNTIF и филтър

Това е един малко старомоден и не твърде ефективен метод. Използвахме го в началото на века, когато Remove duplicates не съществуваха, а пивот таблиците бяха екзотика с доста по-сложно меню. Но при определени обстоятелства този метод може да бъде единственият ви избор (след малко ще разберете защо).

Този метод изисква една помощна колона (COUNTIF), в която въвеждаме следната формула:

Какво прави тази формула? Брои колко пъти думата в колона А се среща в редовете преди нея. Обърнете внимание на абсолютното адресиране за клетка А2. Когато копираме формулата надолу тя ще „разширява“ областта на броене с всеки един ред. Но винаги ще започва от А2. Затова DRAGAN на ред 3 има 1, на ред 8 има 2, а на ред 11 – 3. Просто от ред 2 до ред 3 тази стойност се среща 1 път, от ред 2 до ред 8 – два пъти, от ред 2 до ред 11 – три пъти и т.н.

Следващата ни стъпка е да сложим един филтър на таблицата и да филтрираме в колона Count само стойност 1

И нашият списък с уникални стойности е готов.

Остава само да го копираме и пейстнем, където ни е нужен. А можем и да филтрираме да се покажат всички стойности, които не са 1 и след това да изтрием тези редове. Въпрос на вкус и нужда.

Смятам, че недостатъците на този метод са ясни – изисква допълнителна колона. Формулата COUNTIF може да направи работата с файла по-бавна, ако става въпрос за няколко стотин хиляди реда. При промяна или добавяне на нови данни – трябва да се копират формулите отново и т.н. Да не говорим, че е възможно и да се обърка нещо при копирането на формулата.

Този метод обаче има едно предимство пред повечето от останалите – позволява да извлечем повече от 1 уникална стойност. Например, ако са ни необходими по две една под друга (да кажем за количество и за сума).

Как става това? Просто първо сортираме колоната с имената, а после филтрираме стойност 1 и 2 в Countif колоната:

Не че не можем да вземем уникалния списък, получен с другите методи, да го копираме и пейстнем втори път и след това да сортираме. Но нали говорим за някаква ефективност и използване на възможно най-малко действия.

 7. Извличане на уникални стойности с EXACT и филтър

Това е една много интересна комбинация, благодарение на която можем да направим нещо, което с другите методи до тук не успяваме. А именно – да извлечем уникални стойности, но с отчитане на различния начин на изписване на еднакви стойности (Dragan, DRAGAN и т.н.)

Първо трябва да сортираме изходните данни като отиваме на Data > Sort  & Filter > Sort

В менюто сорт трябва да кликнем там, където в 99.9% от случаите не кликаме – на бутона Options

И след това да сложим отметка на Case Sensitive.

Цъкаме ОК колкото е необходимо и вижте как са сортирани данните (колоната вляво):

Вдясно нарочно съм сложил същите данни, но сортирани без Case Sensitive да е отметнато, за да видите разликата.

Сега идва ред за функцията EXACT. Тази функция сравнява две стойности и връща TRUE, ако те са абсолютно и напълно еднакви. В противен случай връща False. Но какво ще сравним с нея?

Ами – ще проверим дали стойността на всеки ред в колоната Names е абсолютно същата като тази на следващия. По този начин ще получим редуващи се стойности от TRUE и FALSE. Като FALSE ще бъде точно там, където имаме нова стойност в колоната с имената.

При това, забележете – FALSE имаме не само, където имената са напълно различни, но и там, където имаме едно и също име, изписано по различен начин.

Сега остава просто да сложим един филтър и да филтрираме в колона EXACT само стойностите FALSE. И ето го нашият списък с уникални стойности, който отчита различното изписване на еднакви имена.

Това, разбира се, е голямото предимство на този метод. Той е лесен и сравнително автоматичен за предлагане.

Като недостатъци – има нужда от помощна колона, не се обновява при промяна и добавяне на данни, изисква копиране и пействане на формули.

8. Power Query

Power Query е много подходящ инструмент точно за тази задача – извличане на списък с уникални стойности в Excel. За целта трябва да позиционираме курсора някъде в колоната и да отидем на Data > Get & Transform > From Table/Range

Тук кликаме ОК

И данните се зареждат в Power Query

Цъкаме с десен бутон върху заглавния ред и от менюто избираме Remove Duplicates

И готово – нашият списък с уникални стойности е готов.

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

Погледнете по-внимателно в реда за формули на Power Query. Вижте каква формула е създал той за нас: = Table.Distinct(#“Changed Type“)

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

 = Table.Distinct(#“Changed Type“, Comparer.OrdinalIgnoreCase)

Последната стъпка е да заредим данните в Excel с бутона Close & load.

Предимствата на този метод е, че е гъвкав – може с отчитане на различното изписване, а може и без. Доста лесен е – само няколко кликания по менютата. Освен това при промяна на изходните данни, списъкът с уникалните стойности се обновява само с натискане на бутона рефреш. Много голямо предимство на този метод е, че може да работи с данни, които са над 1 млн. реда. При това те може да не са дори в Excel, а например в .txt, .csv, .pdf файл, в база данни или дори в някоя система.

Като недостатък мога да посоча само, че не работи с версии 2007 и надолу. Но в 2025 година, ако все още използвате такив древни версии, редно е да помислите за промяна към по-горна.

 9. Функцията UNIQUE

В Excel 365 и десктоп версии от 2021 нагоре има специална функция, която „гледа“ в даден диапазон от клетки и връща списък с уникални стойности. Нейният синтаксис е доволно прост – само посочвате от кой диапазон искате да върне уникалните стойности.

UNIQUE обаче не отчита различното изписване, защото не е case sensitive.

10. Case sensitive UNIQUE

Не е невъзможно с динамична формула да се постигне списък с уникални стойности в Excel, който е case sensitive, т.е. прави разлика между DRAGAN и Dragan.

За целта ще използвам идеята на метод 7 по-горе. Най-напред трябва да сортираме изходните данни във възходящ ред като използваме опцията за case sensitive сортиране. След това въвеждаме тази формула в случая в клетка С2. Това е достатъчно резултатите да се изчислят до края на списъка. Виждате там, където има смяна на името – резултатът е FALSE, иначе е TRUE. Същото като в метод 7, но без да правим Copy / Paste.

Тук към формулата е добавен един IF, който проверява дали стойността на долния ред е еднаква с тази от текущия. Ако двете стойности са еднакви – връща празно (“”), ако са различни – връща стойността от текущия ред.

До тук успяхме да създадем една динамична област, която съдържа всички уникални стойности, с отчитане на различното изписване. Но също така и доста празни клетки. Как ще вземем само имената? Трябва да филтрираме. Затова ще използваме динамичната функция FILTER, която филтрира диапазон от клетки по зададаен критерий. Ето така:

Буквално казваме на Ексел – филтрирай областта от имена и празни клетки, като ни върнеш само тези клетки, които не са празни  (<>””)

Този метод е една идея по-автоматизиран от метод №7. Отново изисква ръчно сортиране (с опция за case sensitive) на изходните данни, но не трябва да копираме формули надолу. Достатъчно е вместо A2:A40 да зададем област A2:A1000, т.е. да обхваща много голям брой редове.

11. Списък с уникални стойности, чрез SUBTOTAL

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

  • Изчисляване на сума, средно, броене и други за всяка група данни.
  • Автоматично групиране и създаване на структура (outline) за лесна навигация.

Именно второто ще използваме, за да извлечем списък с уникални стойности. Започваме като сортираме изходните данни. Използваме опцията за case sensitive сортиране:

След това поставяме курсора в някоя клетка от таблицата и отиваме на Отидете в меню DataOutlineSubtotal.

В това меню казваме на Excel да сложи субтотал при всяка нова стойност в колоната Names (At each change in) и да използва функцията за броене Count (Use function)

Когато дадем ОК се получава това:

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

Обърнете внимание, че Excel освен формулите SUBTOTAL, добавя и едни бутони с цифрите 1, 2  и 3 в горния ляв ъгъл. Те дават възможност да се показват различни детайли на данните. При натиснат бутон 3 (за най-ниското ниво, в случая трето) виждаме всичко. Ако натиснем 1, ще видим само общата бройка на най-високото (първо ниво):

А, ако натиснем 2 (за второ ниво) – показва се това:

Вижте, че колона С съдържа точно списъка с уникални стойности (без отчитане на различно изписване на еднакви имена). Остава само да си го копираме някъде и да премахнем „ Count” с помощта на Find & Replace и ще имаме каквото ни трябва.

Този метод е сравнително бърз, не е сложен (след като му хванете цаката), но както казах вече – след като имаме пивот таблици (а и Power Query) – за какво ни е да се занимаваме с това?

Има едно маааалко предимство – тъй като след всяка дума в списъка е добавено „ Count”, можем да го заменим с помощта на Find & Replace с нещо друго, ако ни трябва. Представете си, че след всяко име трябва да стои „ Служител в компания Хикс“. Примерно. Тогава си правим списъка със субтотал, копираме го и заместваме „ Count” със „ Служител в компания Хикс“. Бързо, лесно, просто. Ако е за един път обаче. Ако ще е повтаряема операция – бих посъветвал да помислите за решение с Power Query.

12. Извличане на списък с уникални стойности с макрос

Разбира се, че тази задача може да бъде изпълнена и с макрос. Възможни са всякакви варианти, но по-долу давам един код, който при стартиране извежда меню, в което трябва да посочите областта с изходните данни и след това създава нов шийт с име “Unique values”,  в който е списъкът с уникални стойности.

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

Sub ExtractUniqueValues()

    Dim ws As Worksheet

    Dim rng As Range

    Dim uniqueValues As Collection

    Dim outputSheet As Worksheet

    Dim cell As Range

    Dim i As Long

    Dim sheetName As String

    ‘ Името на работния лист

    sheetName = „Unique Values“

    ‘ Проверка и изтриване на стария лист, ако съществува

    On Error Resume Next

    Set ws = ThisWorkbook.Sheets(sheetName)

    If Not ws Is Nothing Then

        Application.DisplayAlerts = False ‘ Изключване на предупрежденията

        ws.Delete

        Application.DisplayAlerts = True ‘ Включване на предупрежденията

    End If

    On Error GoTo 0

    ‘ Показване на диалогов прозорец за избор на област

    On Error Resume Next

    Set rng = Application.InputBox(„Select the range to extract unique values:“, _

                                   „Select Range“, Type:=8)

    On Error GoTo 0

    ‘ Проверка дали е избрана област

    If rng Is Nothing Then

        MsgBox „No range selected.“, vbExclamation, „Error“

        Exit Sub

    End If

    ‘ Създаване на колекция за уникални стойности

    Set uniqueValues = New Collection

    On Error Resume Next

    For Each cell In rng

        If Not IsEmpty(cell.Value) Then

            uniqueValues.Add cell.Value, CStr(cell.Value)

        End If

    Next cell

    On Error GoTo 0

    ‘ Създаване на нов работен лист за извеждане на резултатите

    Set outputSheet = ThisWorkbook.Worksheets.Add

    outputSheet.Name = sheetName

    ‘ Извеждане на уникалните стойности в новия работен лист

    For i = 1 To uniqueValues.Count

        outputSheet.Cells(i, 1).Value = uniqueValues(i)

    Next i

    MsgBox „The list of unique values has been created in a new worksheet ‘Unique Values’.“, vbInformation, „Success“

End Sub

13 и 14 Office Scripts и Python

В началото обещах 14 начина и те наистина са толкова. Но последните два, а именно чрез използване на Office Scripts и Python само ще ги маркирам. Най-вече защото не съм навлезнал в дълбочина в тази материя. Само ще кажа, че това са два сравнително нови инструмента, добавени в Excel в последните няколко години. Тепърва тяхното използване ще се задълбочава и тепърва предстои тази статия да бъде допълнена с конкретни формули и текст за тях. За сега – само ги отбелязвам за пълнота.

Списък с уникални стойности в Excel: заключение

Всеки един от разгледаните методи има свои предимства и недостатъци. В долната таблица съм направил кратко обобщение.

Ако се чудите кой от всички да използвате, то моето лично мнение е:

Remove duplicates за бързо, еднократно упражнение с не много данни

Power Query – за повторяеми действия, особено с много данни от различни източници

UNIQUE – ако ползвате Офис 365, данните не са много и различното изписване на еднакви думи не е важно

Файл с разгледаните примери, в който може да видите формули, макрос, power query заявката може да изтеглите от тук

Метод Плюсове Минуси Подходящ за
Ръчен метод (сортиране и триене) – Лесен за разбиране – Много времеемък Малки набори от данни
– Дава пълен контрол върху данните – Податлив на грешки
– Не се обновява автоматично
Обикновен филтър – Лесен за прилагане – Скучен и бавен Малки до средни набори от данни с допълнителни критерии
– Визуално разпознаване на повтарящи се стойности – Не се обновява при промени в данните
Advanced Filter – Автоматизира сортирането и премахването на дубликати – Не различава case-sensitive стойности Малки до средни набори от данни с допълнителни критерии
– Копира резултатите на друго място – Не се обновява при промени
Remove Duplicates – Бърз и ефективен – Унищожава изходните данни Големи набори от данни за еднократна обработка
– Работи добре с големи таблици – Не различава case-sensitive стойности
– Не се обновява автоматично
Пивот таблица – Не променя оригиналните данни – Изисква рефреш за актуалност Повтарящи се задачи със средни и големи по обем данни
– Автоматизация чрез обновяване – Удвоява обема на файла
– Сортира уникалните стойности – Не различава case-sensitive стойности
COUNTIF и филтър – Позволява извличане на повече от една уникална стойност – Бавен за големи таблици Данни с малко колони и специфични изисквания
– Гъвкавост при работа с допълнителни критерии – Изисква помощна колона
– Податлив на грешки при копиране на формули
EXACT и филтър – Поддържа case-sensitive извличане – Изисква помощна колона Данни с различно изписване на дубликати
– Лесен за прилагане – Не се обновява автоматично
– По-сложен за начинаещи
Power Query – Мощен и гъвкав инструмент – Не е наличен в Excel 2007 и по-стари версии Големи набори от данни
– Поддържа case-sensitive извличане – Изисква начално обучение Повтарящи се задачи
– Лесно обновяване  Данни от външен източник – txt, csv, pdf файл, бази данни и т.н.
Функцията UNIQUE – Изключително лесна за използване – Не поддържа case-sensitive извличане Модерни версии на Excel
– Динамично обновяване – Налична само в Excel 365/2021 Малки и средни данни, за които се предполага, че ще се променят често
Case-sensitive UNIQUE – Динамично обновяване – По-сложна за настройка Данни с различно изписване на дубликати
– Поддържа case-sensitive извличане – Изисква сортиране Малки и средни данни, за които се предполага, че ще се променят често
Subtotals – Лесен за прилагане – По-стара функционалност Малък до среден обем данни и еднократни действия
– Позволява добавяне на текст към уникалните стойности – По-малко интуитивен интерфейс
– Не се обновява автоматично
Макрос – Пълна автоматизация – Изисква познания по VBA Повтарящи се задачи
– Може да бъде персонализиран – Не е динамичен без ръчно стартиране Сложни операции
Office Scripts – Лесна автоматизация за Microsoft 365 – Не е наличен в по-стари версии Същото като за макроса
– Поддържа case-sensitive извличане – Изисква базови познания по JavaScript
Python – Подходящ за големи набори от данни – Изисква външни инструменти (като Jupyter или Visual Studio Code) Програмисти и потребители с големи данни
– Лесен за разширяване с допълнителни функции – Все още е сравнително нова функционалност – липсват обучения и ресурси

– Наличен в Офис365

Публикувано в Формули, Хитрини. Постоянна връзка.