Több keresési értéket kaphat egyetlen cellában (ismétléssel és anélkül)

Megtekinthetjük és visszaadhatjuk-e több értéket egy cellában az Excelben (vesszővel vagy szóközzel elválasztva)?

Ezt a kérdést számos kollégám és olvasóm többször feltette nekem.

Az Excel csodálatos lekérdezési képletekkel rendelkezik, például VLOOKUP, INDEX/MATCH (és most XLOOKUP), de ezek egyike sem kínál módot több egyező érték visszaadására. Mindezek az első egyezés azonosításával és visszaadásával működnek.

Tehát egy kis VBA kódolást végeztem, hogy előállítsak egy egyéni funkciót (más néven felhasználó által definiált funkciót) az Excelben.

Frissítés: Miután az Excel dinamikus tömböket és félelmetes funkciókat, például UNIQUE és TEXTJOIN kiadott, mostantól egyszerű képletet és adja vissza az összes egyező értéket egy cellában (ebben az oktatóanyagban szerepel).

Ebben az oktatóanyagban megmutatom, hogyan kell ezt megtenni (ha az Excel legújabb verzióját használja - a Microsoft 365 -öt az összes új funkcióval), valamint egy módszert, ha régebbi verziókat használ ( VBA használatával).

Kezdjük hát el!

Több érték megkeresése és visszaadása egy cellában (képlet használatával)

Ha Excel 2016 -ot vagy korábbi verziókat használ, ugorjon a következő szakaszra, ahol bemutatom, hogyan kell ezt megtenni a VBA használatával.

A Microsoft 365 előfizetéssel az Excel most sokkal hatékonyabb funkciókkal és funkciókkal rendelkezik, amelyek nem léteznek a korábbi verziókban (például XLOOKUP, Dynamic Arrays, UNIQUE/FILTER funkciók stb.)

Tehát ha a Microsoft 365 -öt (korábban Office 365 -ként ismerte) használja, akkor az ebben a szakaszban ismertetett módszerek segítségével több értéket kereshet és adhat vissza az Excel egyetlen cellájában.

És mint látni fogod, ez egy nagyon egyszerű képlet.

Az alábbiakban van egy adathalmazom, ahol az A oszlopban szereplő személyek neve és a B oszlopban végzett képzés szerepel.

Kattintson ide a mintafájl letöltéséhez, és kövesse

Minden egyes személynél szeretném megtudni, hogy milyen képzést végeztek el. A D oszlopban megtalálom az egyedi nevek listáját (az A oszlopból), és szeretném gyorsan megkeresni és kivonni az összes képzést, amelyet minden ember elvégezett, és ezeket egyetlen készletben (vesszővel elválasztva) megszerezni.

Az alábbiakban ezt a képletet találja meg:

= TEXTJOIN (",", TRUE, IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

Miután beírta a képletet az E2 cellába, másolja azt az összes cellára, ahol szeretné az eredményeket.

Hogyan működik ez a képlet?

Hadd bontsam le ezt a képletet, és magyarázzam el, hogy az egyes részek hogyan adják meg az eredményt.

Az IF képlet logikai tesztje (D2 = $ A $ 2: $ A $ 20) ellenőrzi, hogy a D2 névcella megegyezik -e az A2: A20 tartományban lévővel.

Átmegy az A2: A20 tartomány minden celláján, és ellenőrzi, hogy a név megegyezik -e a D2 cellában. ha ugyanaz a név, akkor IGAZ értéket ad vissza, különben HAMIS értéket ad vissza.

Tehát a képlet ezen része egy tömböt ad az alábbiak szerint:

{IGAZ; HAMIS; HAMIS; IGAZ

Mivel csak Bob számára szeretnénk kiképzést kapni (a D2 cella értéke), meg kell szereznünk a megfelelő képzést azoknak a celláknak, amelyek a fenti tömbben IGAZ értéket adnak vissza.

Ez könnyen elvégezhető úgy, hogy az IF képlet [value_if_true] részét adja meg a képzés tartományának. Ez biztosítja, hogy ha a D2 cellában lévő név megegyezik az A2: A20 tartományban lévő névvel, az IF képlet visszaadja az adott személy összes képzését.

És bárhol, ahol a tömb HAMIS értéket ad vissza, a [value_if_false] értéket „” (üres) értékként adtuk meg, tehát üres értéket ad vissza.

A képlet IF része az alábbi módon adja vissza a tömböt:

{"Excel"; ""; ""; "PowerPoint"; ""; "" "" "" "" "" "" "" "" "" "" "" "" ""; ""; "" "" "}}

Ahol ott van Bob képzésének neve és üres helyek, bárhol is volt a neve Bob.

Most már csak össze kell kapcsolnunk ezeket a képzési nevet (vesszővel elválasztva), és vissza kell adnunk egy cellába.

Ez pedig könnyen elvégezhető az új TEXTJOIN képlet használatával (elérhető az Excel2021-2022 és az Excel a Microsoft 365 rendszerben)

A TEXTJOIN képlet három érvet tartalmaz:

• a határoló - ami példánkban „,”, mivel szeretném, ha a képzést vesszővel és szóközzel elválasztanánk
• IGAZ - amely azt mondja a TEXTJOIN képletnek, hogy figyelmen kívül hagyja az üres cellákat, és csak azokat kombinálja, amelyek nem üresek
• Az If képlet, amely a kombinálni kívánt szöveget adja vissza

Ha Excel -t használ a Microsoft 365 -ben, amely már rendelkezik dinamikus tömbökkel, akkor írja be a fenti képletet, és nyomja meg az enter billentyűt. Ha pedig Excel2021-2022-t használ, be kell írnia a képletet, tartsa lenyomva a Control és a Shift billentyűt, majd nyomja meg az Enter billentyűt

Kattintson ide a mintafájl letöltéséhez, és kövesse

Több keresési értéket kaphat egyetlen cellában (ismétlés nélkül)

Mivel az EGYEDI képlet csak az Excel programban érhető el a Microsoft 365-ben, ezt a módszert nem tudja használni az Excel2021-2022 programban

Abban az esetben, ha az adathalmazban ismétlődések vannak, amint az alább látható, kissé módosítania kell a képletet, hogy csak egyetlen cellában kapjon egyedi értékek listáját.

A fenti adatkészletben egyesek többször is részt vettek képzésen. Például Bob és Stan kétszer vett részt az Excel képzésen, Betty pedig kétszer MS Word képzésen. Eredményünk szerint azonban nem akarjuk, hogy az edzésnév megismétlődjön.

Ehhez az alábbi képletet használhatja:

= TEXTJOIN (",", IGAZ, EGYEDI (HA (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

A fenti képlet ugyanúgy működik, kisebb módosítással. az IF képletet használtuk az UNIQUE függvényen belül, hogy amennyiben az if képlet eredményében ismétlődések vannak, az UNIQUE függvény eltávolítja azt.

Kattintson ide a mintafájl letöltéséhez

Több érték megkeresése és visszaadása egy cellában (VBA használatával)

Ha Excel 2016 vagy korábbi verziókat használ, akkor nem lesz hozzáférése a TEXTJOIN képlethez. Tehát a legjobb módja annak, hogy utána keressen és több egyező értéket kapjon egyetlen cellában, ha egyéni képletet használ, amelyet a VBA segítségével hozhat létre.

Ahhoz, hogy egyetlen cellában több keresési értéket kapjunk, létre kell hoznunk egy függvényt a VBA -ban (hasonlóan a VLOOKUP függvényhez), amely ellenőrzi az oszlop minden celláját, és ha a keresési érték megtalálható, hozzáadja az eredményhez.

Itt van a VBA kód, amely ezt megteheti:

'Sumit Bansal (https://trumpexcel.com) kódja .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Vége funkció

Hová tegye ezt a kódot?

1. Nyisson meg egy munkafüzetet, és kattintson az Alt + F11 gombra (ez megnyitja a VBA szerkesztő ablakát).
2. Ebben a VBA szerkesztő ablakban, a bal oldalon található egy projektfelfedező (ahol az összes munkafüzet és munkalap fel van sorolva). Kattintson a jobb gombbal a munkafüzet bármelyik objektumára, ahol ezt a kódot szeretné használni, majd lépjen a Beszúrás -> Modul menüpontra.
3. A modul ablakában (amely a jobb oldalon jelenik meg) másolja ki és illessze be a fenti kódot.
4. Most már minden készen áll. Lépjen a munkafüzet bármely cellájába, és írja be = SingleCellExtract és csatlakoztassa a szükséges bemeneti argumentumokat (azaz LookupValue, LookupRange, ColumnNumber).

Hogyan működik ez a képlet?

Ez a funkció a VLOOKUP funkcióhoz hasonlóan működik.

3 argumentumot tartalmaz bemenetként:

1. Keresési érték - Egy karakterlánc, amelyet meg kell keresnünk egy cellatartományban.
2. LookupRange - Egy cellasor, ahonnan le kell kérnünk az adatokat ($ B3: $ C18 ebben az esetben).
3. Oszlopszám - A tábla/tömb oszlopszáma, amelyből vissza kell adni a megfelelő értéket (ebben az esetben 2).

Amikor ezt a képletet használja, ellenőrzi a keresési tartomány bal szélső oszlopának minden celláját és amikor talál egyezést, akkor hozzáadja az eredményt a cellában, amelyben a képletet használta.

Emlékezik: Mentse a munkafüzetet makró-engedélyezett munkafüzetként (.xlsm vagy .xls), hogy újra felhasználhassa ezt a képletet. Ez a funkció ezenkívül csak ebben a munkafüzetben lenne elérhető, és nem minden munkafüzetben.

Kattintson ide a mintafájl letöltéséhez

Ismerje meg, hogyan automatizálhatja az unalmas ismétlődő feladatokat az Excel VBA használatával. Csatlakozz a Excel VBA tanfolyam

Több keresési értéket kaphat egyetlen cellában (ismétlés nélkül)

Lehetőség van arra, hogy az adatok ismétlődjenek.

Ha a fentebb használt kódot használja, az ismétléseket ad az eredményben is.

Ha azt az eredményt szeretné elérni, ahol nincs ismétlés, akkor kicsit módosítania kell a kódot.

Itt található a VBA -kód, amely több keresési értéket ad egyetlen cellában, ismétlés nélkül.

'Sumit Bansal (https://trumpexcel.com) kódja .Count If LookupRange.Cells (i, 1) = Lookupvalue then For J = 1 To i - 1 If LookupRange.Cells (J, 1) = Lookupvalue then If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, Oszlopszám), majd a GoTo kihagyása, ha vége, ha a következő J Eredmény = Eredmény & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funkció

Miután elhelyezte ezt a kódot a VB szerkesztőben (ahogy az oktatóanyag fent látható), használhatja a MultipleLookupNoRept funkció.

Itt egy pillanatkép az eredményről, amelyet ezzel kap MultipleLookupNoRept funkció.

Kattintson ide a mintafájl letöltéséhez

Ebben az oktatóanyagban kitértem arra, hogyan lehet képleteket és VBA -t használni az Excelben több keresési érték megkeresésére és visszaadására az Excel egy cellájában.

Míg ez könnyen elvégezhető egy egyszerű képlettel, ha Excel -t használ a Microsoft 365 előfizetésben, ha korábbi verziókat használ, és nem fér hozzá olyan funkciókhoz, mint a TEXTJOIN, akkor is megteheti ezt a VBA használatával, ha létrehozza saját egyéni funkció.