Jak extrahovat jedinečné hodnoty z více sloupců v aplikaci Excel?
Předpokládejme, že máte několik sloupců s více hodnotami, některé hodnoty se opakují ve stejném sloupci nebo jiném sloupci. A nyní chcete najít hodnoty, které jsou v kterémkoli sloupci přítomny pouze jednou. Existují nějaké rychlé triky, jak extrahovat jedinečné hodnoty z více sloupců v aplikaci Excel?
Extrahujte jedinečné hodnoty z více sloupců pomocí vzorců
Tato část pokryje dva vzorce: jeden používá maticový vzorec vhodný pro všechny verze Excelu a druhý používá dynamický maticový vzorec speciálně pro Excel 365.
Extrahujte jedinečné hodnoty z více sloupců pomocí vzorce Array pro všechny verze aplikace Excel
Pro uživatele s jakoukoli verzí Excelu mohou být maticové vzorce výkonným nástrojem pro extrahování jedinečných hodnot z více sloupců. Můžete to udělat takto:
1. Předpokládejme své hodnoty v rozsahu A2: C9, do buňky E2 zadejte následující vzorec:
=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. Pak stiskněte tlačítko Shift + Ctrl + Enter klávesy dohromady a poté přetažením úchytu výplně extrahujte jedinečné hodnoty, dokud se neobjeví prázdné buňky. Viz screenshot:
- $ A $ 2: $ C $ 9: Toto určuje rozsah dat, který se má zkontrolovat, což jsou buňky od A2 do 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<>"" kontroluje, zda buňky v rozsahu nejsou prázdné.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 určuje, zda hodnoty těchto buněk ještě nebyly uvedeny v rozsahu buněk od E1 do E1.
- Pokud jsou splněny obě podmínky (tj. hodnota není prázdná a ještě není uvedena ve sloupci E), funkce IF vypočítá jedinečné číslo na základě jeho řádku a sloupce (ROW($2:$9)*100+COLUMN($A: $C)).
- Pokud podmínky nejsou splněny, funkce vrátí velké číslo (7^8), které slouží jako zástupný symbol.
- MIN(...): Najde nejmenší číslo vrácené výše uvedenou funkcí KDYŽ, odpovídající umístění další jedinečné hodnoty.
- TEXT(..."R0C00"): Převede toto minimální číslo na adresu ve stylu R1C1. Kód formátu R0C00 označuje převod čísla do referenčního formátu buňky Excel.
- NEPŘÍMÝ(...): Používá funkci NEPŘÍMÉ pro převod adresy stylu R1C1 vygenerované v předchozím kroku zpět na normální odkaz na buňku stylu A1. Funkce NEPŘÍMÉ umožňuje odkazování na buňky na základě obsahu textového řetězce.
- &"": Přidání &"" na konec vzorce zajistí, že konečný výstup bude zpracován jako text, takže sudá čísla budou zobrazena jako text.
Extrahujte jedinečné hodnoty z více sloupců pomocí vzorce pro Excel 365
Excel 365 podporuje dynamická pole, takže je mnohem snazší extrahovat jedinečné hodnoty z více sloupců:
Zadejte nebo zkopírujte následující vzorec do prázdné buňky, kam chcete umístit výsledek, a poté klikněte vstoupit klíč k získání všech jedinečných hodnot najednou. Viz snímek obrazovky:
=UNIQUE(TOCOL(A2:C9,1))
Extrahujte jedinečné hodnoty z více sloupců pomocí Kutools AI Aide
Poznejte sílu Kutools AI asistent pro bezproblémové extrahování jedinečných hodnot z více sloupců v Excelu. Pouhými několika kliknutími tento inteligentní nástroj prozkoumá vaše data, identifikuje a vypisuje jedinečné položky v libovolném vybraném rozsahu. Zapomeňte na potíže se složitými vzorci nebo kódem vba; Využijte efektivitu Kutools AI asistent a přeměňte svůj pracovní postup v Excelu na produktivnější a bez chyb.
Po instalaci Kutools pro Excel klikněte prosím Kutools AI > AI asistent k otevření Kutools AI asistent podokno:
- Zadejte svůj požadavek do pole chatu a klikněte Poslat nebo stiskněte tlačítko vstoupit klíč k odeslání otázky;
"Extrahujte jedinečné hodnoty z rozsahu A2:C9, ignorujte prázdné buňky a umístěte výsledky počínaje E2:" - Po analýze klikněte Provést tlačítko pro spuštění. Kutools AI Aide zpracuje váš požadavek pomocí AI a vrátí výsledky v zadané buňce přímo v Excelu.
Extrahujte jedinečné hodnoty z více sloupců pomocí kontingenční tabulky
Pokud jste obeznámeni s kontingenční tabulkou, můžete snadno extrahovat jedinečné hodnoty z více sloupců pomocí následujících kroků:
1. Nejprve vložte jeden nový prázdný sloupec nalevo od dat, v tomto příkladu vložím sloupec A vedle původních dat.
2. Klikněte na jednu buňku ve svých datech a stiskněte Alt + D a stiskněte P klíč okamžitě otevřete Průvodce kontingenční tabulkou a kontingenčním grafemzvolte Několik rozsahů konsolidace v kroku průvodce 1, viz screenshot:
3. Pak klikněte na tlačítko další tlačítko, zkontrolujte Vytvořte pro mě jednostránkové pole možnost v kroku 2 průvodce, viz screenshot:
4. Pokračujte kliknutím další Klepnutím vyberte rozsah dat, který zahrnuje nový nový sloupec buněk, a poté klepněte na přidat tlačítko pro přidání rozsahu dat do Všechny rozsahy seznam, viz screenshot:
5. Po výběru rozsahu dat pokračujte kliknutím další, v kroku průvodce 3 vyberte, kam chcete umístit sestavu kontingenční tabulky, jak chcete.
6. Konečně klikněte na tlačítko úprava k dokončení průvodce a v aktuálním listu byla vytvořena kontingenční tabulka, potom zrušte zaškrtnutí všech polí v Vyberte pole, která chcete přidat do sestavy sekce, viz screenshot:
7. Poté zkontrolujte pole Hodnota nebo přetáhněte hodnotu na Řádky štítek, nyní získáte jedinečné hodnoty z více sloupců takto:
Extrahujte jedinečné hodnoty z více sloupců pomocí kódu VBA
S následujícím kódem VBA můžete také extrahovat jedinečné hodnoty z více sloupců.
1. Podržte ALT + F11 klíče a otevře se Okno Microsoft Visual Basic pro aplikace.
2, klikněte Vložit > Modula vložte následující kód do okna modulu.
VBA: Extrahujte jedinečné hodnoty z více sloupců
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. Pak stiskněte tlačítko F5 ke spuštění tohoto kódu a vyskočí okno s výzvou, aby vám připomnělo vybrat rozsah dat, který chcete použít. Viz snímek obrazovky:
4. A pak klikněte na tlačítko OK, zobrazí se další pole s výzvou, které vám umožní vybrat místo pro vložení výsledku, viz screenshot:
5. klikněte OK zavřete toto dialogové okno a všechny jedinečné hodnoty byly extrahovány najednou.
Více relativních článků:
- Spočítejte počet jedinečných a odlišných hodnot ze seznamu
- Předpokládejme, že máte dlouhý seznam hodnot s některými duplicitními položkami, nyní chcete spočítat počet jedinečných hodnot (hodnoty, které se v seznamu objeví pouze jednou) nebo odlišné hodnoty (všechny různé hodnoty v seznamu, to znamená jedinečné hodnoty + 1. duplicitní hodnoty) ve sloupci, jak je zobrazen snímek obrazovky vlevo. V tomto článku budu hovořit o tom, jak řešit tuto práci v aplikaci Excel.
- Extrahujte jedinečné hodnoty na základě kritérií v aplikaci Excel
- Předpokládejme, že máte následující rozsah dat, který chcete vypsat pouze jedinečné názvy sloupce B na základě konkrétního kritéria sloupce A, abyste získali výsledek, jak je znázorněno níže. Jak jste mohli s tímto úkolem v aplikaci Excel jednat rychle a snadno?
- Povolit pouze jedinečné hodnoty v aplikaci Excel
- Pokud chcete zachovat zadávání pouze jedinečných hodnot ve sloupci listu a zabránit duplikátům, tento článek představí několik rychlých triků, jak se s tímto úkolem vypořádat.
- Součet jedinečných hodnot na základě kritérií v aplikaci Excel
- Například mám řadu dat, která obsahuje sloupce Název a Objednávka, nyní, abych shrnul pouze jedinečné hodnoty ve sloupci Objednávka na základě sloupce Název, jak ukazuje následující snímek obrazovky. Jak rychle a snadno vyřešit tento úkol v aplikaci Excel?
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!