Как да извлека уникални стойности от множество колони в Excel?
Да предположим, че имате няколко колони с множество стойности, някои стойности се повтарят в същата колона или различна колона. И сега искате да намерите стойностите, които присъстват в която и да е колона само веднъж. Има ли бързи трикове за извличане на уникални стойности от множество колони в Excel?
Извличайте уникални стойности от множество колони с формули
Този раздел ще обхване две формули: едната използва формула за масив, подходяща за всички версии на Excel, а другата използва формула за динамичен масив специално за Excel 365.
Извличайте уникални стойности от множество колони с формула за масив за всички версии на Excel
За потребители с всяка версия на Excel формулите за масиви могат да бъдат мощен инструмент за извличане на уникални стойности в множество колони. Ето как можете да го направите:
1. Приемайки вашите стойности в диапазон A2: C9, моля, въведете следната формула в клетка E2:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
2. След това натиснете Shift + Ctrl + Enter ключове заедно и след това плъзнете манипулатора за попълване, за да извлечете уникалните стойности, докато се появят празни клетки. Вижте екранна снимка:
- $A$2:$C$9: Това указва диапазона от данни, който трябва да се провери, който е клетките от A2 до C9.
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
- $A$2:$C$9<>"" проверява дали клетките в диапазона не са празни.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 определя дали стойностите на тези клетки все още не са изброени в диапазона от клетки от E1 до E1.
- Ако и двете условия са изпълнени (т.е. стойността не е празна и все още не е посочена в колона E), функцията IF изчислява уникално число въз основа на своя ред и колона (ROW($2:$9)*100+COLUMN($A: $C)).
- Ако условията не са изпълнени, функцията връща голямо число (7^8), което служи като контейнер.
- МИН.(...): Намира най-малкото число, върнато от функцията IF по-горе, съответстващо на местоположението на следващата уникална стойност.
- ТЕКСТ(...,"R0C00"): Преобразува този минимален брой в адрес в стил R1C1. Кодът на формата R0C00 показва преобразуването на числото във формат за референтна клетка на Excel.
- НЕПРЯК(...): Използва функцията INDIRECT, за да преобразува адреса в стил R1C1, генериран в предишната стъпка, обратно в нормална препратка към клетка в стил A1. Функцията INDIRECT позволява рефериране на клетки въз основа на съдържанието на текстов низ.
- &"": Добавянето на &"" в края на формулата гарантира, че крайният резултат се третира като текст, така че четните числа ще се показват като текст.
Извлечете уникални стойности от множество колони с формула за Excel 365
Excel 365 поддържа динамични масиви, което прави много по-лесно извличането на уникални стойности от множество колони:
Моля, въведете или копирайте следната формула в празна клетка, където искате да поставите резултата, и след това щракнете Въведете ключ, за да получите всички уникални стойности наведнъж. Вижте екранна снимка:
=UNIQUE(TOCOL(A2:C9,1))
Извличайте уникални стойности от множество колони с Kutools AI Aide
Освободете силата на Kutools AI помощник за безпроблемно извличане на уникални стойности от множество колони в Excel. Само с няколко щраквания този интелигентен инструмент пресява вашите данни, като идентифицира и изброява уникални записи във всеки избран диапазон. Забравете неприятностите на сложните формули или vba кода; Прегърнете ефективността на Kutools AI помощник и трансформирайте вашия работен процес на Excel в по-продуктивно изживяване без грешки.
След като инсталирате Kutools за Excel, моля, щракнете Kutools AI > AI помощник за да отворите Kutools AI помощник панел:
- Въведете вашето изискване в полето за чат и щракнете Изпрати бутон или натиснете Въведете ключ за изпращане на въпроса;
„Извличане на уникални стойности от диапазона A2:C9, игнориране на празните клетки, и поставяне на резултатите, започващи от E2:“ - След като анализирате, щракнете Изпълнение бутон за бягане. Kutools AI Aide ще обработи вашата заявка с помощта на AI и ще върне резултатите в посочената клетка директно в Excel.
Извличайте уникални стойности от множество колони с обобщена таблица
Ако сте запознати с обобщената таблица, можете лесно да извлечете уникалните стойности от множество колони със следните стъпки:
1. Първо, моля, вмъкнете една нова празна колона отляво на вашите данни, в този пример ще вмъкна колона A до оригиналните данни.
2. Щракнете върху една клетка във вашите данни и натиснете Alt + D клавиши, след което натиснете P ключ незабавно, за да отворите Съветник за обобщена таблица и обобщена диаграма, избирам Множество диапазони на консолидация в стъпка 1 на съветника вижте екранна снимка:
3, След това кликнете Напред бутон, проверете Създайте поле за една страница за мен опция в стъпка 2 на съветника, вижте екранна снимка:
4. Продължете да щракате Напред щракнете, за да изберете диапазона от данни, който включва лявата нова колона от клетки, след което щракнете Добави бутон за добавяне на диапазона от данни към Всички диапазони списъчно поле, вижте екранна снимка:
5. След като изберете диапазона от данни, щракнете върху Продължете Напред, в стъпка 3 на съветника изберете къде искате да поставите отчета с обобщена таблица, както желаете.
6. Най-накрая щракнете завършеност за да завършите съветника и е създадена обобщена таблица в текущия работен лист, след това премахнете отметките от всички полета от Изберете полета, които да добавите към отчета раздел, вижте екранна снимка:
7. След това проверете полето Стойност или плъзнете стойността към Редове етикет, сега ще получите уникалните стойности от множеството колони, както следва:
Извличайте уникални стойности от множество колони с VBA код
Със следния VBA код можете също да извлечете уникалните стойности от множество колони.
1. Задръжте натиснат ALT + F11 ключове и отваря Прозорец на Microsoft Visual Basic за приложения.
2. Щракнете Поставете > Модулии поставете следния код в прозореца на модула.
VBA: Извлечете уникални стойности от множество колони
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. След това натиснете F5 за да изпълните този код и ще изскочи поле за подкана, за да ви напомни да изберете диапазона от данни, който искате да използвате. Вижте екранна снимка:
4, И после щракнете върху OK, ще се появи друго поле за подкана, което ви позволява да изберете място за поставяне на резултата, вижте екранната снимка:
5. Кликнете OK за да затворите този диалогов прозорец и всички уникални стойности са извлечени наведнъж.
Още относителни статии:
- Пребройте броя на уникалните и различни стойности от списък
- Да предположим, че имате дълъг списък от стойности с някои дублиращи се елементи, сега искате да преброите броя на уникалните стойности (стойностите, които се появяват в списъка само веднъж) или различни стойности (всички различни стойности в списъка, това означава уникални стойности +1-ви дублирани стойности) в колона, както е показано на лявата екранна снимка. В тази статия ще говоря за това как да се справя с тази работа в Excel.
- Извличане на уникални стойности въз основа на критерии в Excel
- Да предположим, че имате следния диапазон от данни, в който искате да изброите само уникалните имена на колона B въз основа на конкретен критерий на колона A, за да получите резултата, както е показано на екранната снимка по-долу. Как бихте могли да се справите с тази задача в Excel бързо и лесно?
- Разрешаване само на уникални стойности в Excel
- Ако искате да запазите само уникални стойности, въведени в колона на работен лист и да предотвратите дублирането, тази статия ще ви представи някои бързи трикове, за да се справите с тази задача.
- Сумирайте уникални стойности въз основа на критерии в Excel
- Например, сега имам набор от данни, който съдържа колони Име и Поръчка, за да сумирам само уникални стойности в колоната Поръчка въз основа на колоната Име, както е показано на следната екранна снимка. Как да решите тази задача бързо и лесно в Excel?
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
