A minap készültem létrehozni az Excel két nem szomszédos oszlopának egyedi kombinációját. Ezt általában a Duplikátumok eltávolítása vagy az Advanced Filter alkalmazással végzem, de gondoltam megpróbálom megtenni az új UNIQUE funkcióval, amely 2019-ben érkezik az Office 365-be. Számos ötletet kipróbáltam, és egyik sem fog működni. Szóval a Dynamic Arrays mesteréhez, Joe McDaid-hez mentem segítségért. A válasz nagyon jó, és biztos vagyok benne, hogy elfelejtem, ezért dokumentálom neked és nekem. Biztos vagyok benne, hogy két év múlva a Google-on fogom ezt megtenni, és rájövök: "Ó, nézd! Én vagyok az, aki erről cikket írt!"
Mielőtt eljutna az UNIQUE funkcióhoz, nézze meg, mit próbálok csinálni. Szeretném a B oszlopból származó értékesítési képviselő és a C oszlop termékének minden egyedi kombinációját. Általában ezeket a lépéseket követem:
- Másolja a fejléceket a B1 és a D1 alól a munkalap üres szakaszába
- A B1 csoportban válassza az Adatok, Szűrő, Speciális lehetőséget
- A Speciális szűrő párbeszédpanelen válassza a Másolás új helyre lehetőséget
- Adja meg az 1. lépés fejlécét kimeneti tartományként
- Jelölje be a Csak az egyedi értékek jelölőnégyzetet
- Kattintson az OK gombra
Az eredmény a két mező minden egyedi kombinációja. Vegye figyelembe, hogy a Speciális szűrő nem rendezi az elemeket - az eredeti sorrendben jelennek meg.
Ez a folyamat az Excel 2010-ben a szalag Adatok lapján található Másolatok eltávolítása paranccsal könnyebbé vált. Kovesd ezeket a lepeseket:
- Válassza a B1: D227 és a Ctrl + C másoláshoz
-
Illessze be a munkalap üres szakaszába.
Készítsen másolatot az adatokról, mivel az Ismétlődések eltávolítása pusztító hatású - Válassza az Adatok, az Ismétlődések eltávolítása lehetőséget
- Az Ismétlődések eltávolítása párbeszédpanelen törölje a Dátum jelölést. Ez azt mondja az Excel-nek, hogy csak a Rep és a Product elemeket nézze meg.
-
Kattintson az OK gombra
Mondja meg az Ismétlődések eltávolítása elemet, hogy csak a Rep és a Date dátumot vegye figyelembe
Az eredmények majdnem tökéletesek - csak törölnie kell a Dátum oszlopot.
A kérdés: Van-e valamilyen módja annak, hogy az UNIQUE függvény csak a B & D oszlopokat nézze meg? (Ha még nem látta az új UNIQUE függvényt, olvassa el: UNIQUE funkció az Excel-ben.)
Ha ezt kéri =UNIQUE(B2:D227), megkapja a Rep, a Date és a Product minden egyedi kombinációját, amelyet nem mi keresünk.
Amikor szeptemberben bevezették a dinamikus tömböket, azt mondtam, hogy soha többé nem kell aggódnunk a Ctrl + Shift + Enter képletek összetettsége miatt. De ennek a problémának a megoldásához az emelés nevű fogalmat fogja használni. Remélhetőleg mára letöltötted az Excel Dynamic Arrays Straight To The Point e-könyvet. Az emelés teljes magyarázatához lapozzon a 31-33. Oldalra.
Vegyünk egy Excel függvényt, amely egyetlen értéket vár. Például =CHOOSE(Z1,"Apple","Banana")az Apple-t vagy a Banánt adja vissza attól függően, hogy a Z1 1-et (az Apple esetében) vagy 2-t (a banán esetében) tartalmaz-e. A CHOOSE függvény skalárt vár első argumentumként.
De ehelyett az (1,2) tömbállandót adja át a KIVÁLASZÁS első argumentumaként. Az Excel végrehajtja az Emelési műveletet, és kétszer kiszámítja a KIVÁLASZTÁST. Az 1 értékéhez a B2: B227 értékesítési képviselőket szeretné megadni. A 2 értékéhez a D2: D227 termékeket szeretné megkapni.
Normális esetben a régi Excelben az implicit kereszteződés csorbította volna az eredményeket. De most, hogy az Excel sok cellára képes továbbítani az eredményeket, a fenti képlet sikeresen visszaküldi az összes válasz tömbjét B és D formában:
Úgy érzem, sérteném az intelligenciádat, ha megírnám a cikk többi részét, mert innentől nagyon egyszerű.
Tekerje be az előző képernyőkép képletét UNIQUE-ba, és csak a Sales Rep és a Product egyedi kombinációit kapja =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).
A megértés ellenőrzéséhez próbálja meg megváltoztatni a fenti képletet, hogy három oszlop összes egyedi kombinációját adja vissza: Értékesítési képviselő, Termék, Szín.
Először változtassa meg a tömbállandót az (1,2,3) hivatkozásra.
Ezután adjunk hozzá egy negyedik érv dönt, hogy visszatér színt E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).
Jó lenne ezeket az eredményeket rendezni, ezért a SORT és a SORTBY használatával képlettel fordulunk a Rendezés elemhez.
Normál esetben az első oszlop növekvő rendezésére szolgáló funkció =SORT(Array)vagy =SORT(Array,1,1).
Három oszlop szerinti rendezéshez néhány páros emelést kell végrehajtania =SORT(Array,(1,2,3),(1,1,1)). Ebben a képletben, amikor eljut a SORT második argumentumához, az Excel tudni akarja, hogy mely oszlop szerint rendezze. Egyetlen érték helyett küldjön három oszlopot egy tömbállandóba: (1,2,3). Amikor eljut a harmadik argumentumhoz, ahol adja meg az 1 értéket a Növekvő vagy a -1 értéket a Növekvő számára, küldjön egy három 1-es tömbállandót az Ascending, Ascending, Ascending jelölésére. A következő képernyőképen látható =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).
Legalábbis 2018 végéig ingyenesen letöltheti az Excel Dynamic Arrays könyvet az oldal alján található link használatával.
Arra biztatok, hogy a mai kérdésre adott válasz kissé bonyolult. Amikor megjelentek a Dinamikus tömbök, azonnal gondoltam azokra a csodálatos képletekre, amelyeket Aladin Akyurek és mások az Üzenőtáblán tettek közzé, és hogy ezek a képletek sokkal egyszerűbbé válnak az új Excelben. De a mai példa azt mutatja, hogy továbbra is szükség lesz formulagéniuszokra, hogy új módszereket alkossanak a dinamikus tömbök használatához.
Videót nézni
Töltse le az Excel fájlt
Az excel fájl letöltése: egyedi-a-nem-szomszédos-columns.xlsx fájlból
Excel gondolata a napnak
Az Excel Master barátaimtól kértem tanácsokat az Excel-mel kapcsolatban. Mai gondolat, hogy elgondolkodjak:
"A listákra vonatkozó szabályok: nincsenek üres sorok, nincsenek üres oszlopok, egy cellafejléc, hasonló a hasonlóval"
Anne Walsh
