Visszaadhatja-e az Excel VLOOKUP az összes eredményt, és egy vesszővel összekapcsolhatja őket?
Videót nézni
- Cél az összes szöveges válasz összefűzése egy VLOOKUP alkalmazásból
- Bill módszere: Használjon egy GetAll nevű VBA függvényt
- Egyedi lista a duplikátumok eltávolításával
- Mike módszere:
- Egyedi lista a Speciális szűrő használatával
- TEXTJOIN függvény hozzáadva az Office 365-ben
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Az IF függvény miatt a képlethez a Ctrl + Shift + Enter billentyűkombinációra van szükség a képlet szerkesztésekor
- Az Alt AQOR Enter újra futtatja a Speciális szűrőt!
Videó átirat
183. rész: Csatlakozzon az összes VLOOKUP mérkőzéshez
Bill Jelen: Hé, üdvözlöm újra. Itt az ideje egy újabb Dueling Excel Podcastnak. Bill Jelen vagyok, és Mike Girvin csatlakozik az Excel Is Fun programhoz. Ez a 183. részünk: Csatlakozzon az összes VLOOKUP mérkőzéshez.
(Zene)
Rendben, a mai kérdés Matttől. Visszaadhatja-e a VLOOKUP az összes eredményt, és egyesítheti őket vesszőközzel az egyes pontok között. Például 109876, amelyek itt ezek a kettő, visszaadhatja az alacsony olajtartalmú vesszőteret Ellenőrizve 12/12. És persze, ha többen lennének, többet is hozna. Rendben, szóval itt a megoldásom valamilyen VBA-t fog használni. Rendben, ezért győződjön meg róla, hogy xlsm formátumban van elmentve, különben nem tudja futtatni a VBA vagy az xlsb fájlt, de az xlsx nem - az xlsx az egyetlen fájl, amely nem tudja futtatni a VBA-t. Megnyomjuk az Alt + F11 billentyűkombinációt, ellenőrizzük, hogy a Dual183 rendszert használja-e, vagy bármi is legyen a munkafüzete neve. Helyezze be a modult az üres modulba, és be fogjuk illeszteni ezt a kódot, rendben.
Vessünk egy pillantást erre a GetAll funkcióra, és itt van a keresett azonosító szám, majd a keresni kívánt tartomány. És elindulunk, a GetAll nevű változót adjuk vissza, tehát azzal kezdjük, hogy egyenlő legyen az üres üres. Ha a tartományom minden egyes cellájára a Cell Értéket keressük, akkor a GetAll = GetAll & "" elemet, majd a Cell.Offset (0 sor, 1 oszlop), más szóval az értéket vesszük. ez csak az említett azonosító szám mellett, mert a VBA-ban itt van az azonosító szám. Ha megtaláljuk a megfelelő azonosító számot, akkor 1 oszloppal át akarunk menni. És mi van akkor, ha 2 oszlopot vagy 3 oszlopot szeretne átadni, nos, akkor ezt a 0 sort és 1 oszlopot 2-re változtatja. Rendben, ellenőrizze azt is, hogy - ha vesszük, vesszőt nem teszünk az első.Tehát ha a GetAll változó jelenleg „”, akkor nem vesszük be a vesszőt, rendben?
Tehát most, hogy itt van ez a funkció, nézze meg, mennyire könnyű megoldani Matt problémáját. Ide fogunk jönni, és vegyük az azonosítóit, a Ctrl + C billentyűt, és illesszük be a Ctrl + V parancsot. Kattintson az Adatok elemre, a Másolatok eltávolítása elemre. Tehát van egy egyedi azonosító lista, majd azt akarjuk mondani, hogy = getall, és ezt az értéket az E2 vesszőben keressük. Itt átnézve ezt a tartományt, megnyomom az F4 billentyűt. Az F4 ugyanúgy működik, mint egy szokásos funkció. És ismét elmozdítja Matt kérdését az útból, kattintson duplán, hogy lelője ezt. Működni fog.
És próbáljuk csak, próbáljunk itt valami őrültséget. Készítsünk egy 1. kifejezést, és csak tegyünk belőlük egy csomót, például az 1.-10. Kifejezést. Mindezeket aláírjuk az 109999-re. Másolja le ezt a képletet, szerkessze a képletet, hogy az egészen az aljára menjen, természetesen. Aha. És visszaadja mindazokat a kifejezéseket. Rendben, szóval ez az én megoldásom, a VBA, egy kis funkció ott. Mike, nézzük meg, mi van.
Mike Girvin: Köszönöm. GetAll, ez egy fantasztikus VBA funkció. Rendben, itt megyek át a lepedőhöz. Már átalakítottam egy Excel táblává, hogy amikor az alábbiakban rekordokat adunk hozzá, remélhetőleg a dolgok frissülni fognak.
Most először két részre teszem ezt. Itt megtehetnék egy képletet egy egyedi lista kinyerésére, de szeretnék megnézni egy másik lehetőséget: A Speciális szűrőnek van egy kivonat egyedi lista opciója, és frissíthető. Csak az azonosító oszlop adatait emelem ki, az Speciális szűrőbe, vagy az Alt, A, Q billentyűzetet használom. Most, a Szűrő lista helyben, semmiképp. Másolni akarom egy másik helyre. Csak az A oszlopot kapta, és mivel ez egy Excel táblázat, amely később kibővül. Nincs kritériumom, szeretném átmásolni a D1-be és csak az egyedi rekordokat ellenőrizni. Kattintson az OK gombra.
Most átmegyek ide, az összes megjegyzés beíródik, és egy olyan funkciót fogok használni, amely csak az Excel 2016 Office 365 alkalmazásban működik: = TEXTJOIN függvény. Önmagában ez a funkció érdemes megkapni az Excel legújabb verzióját. Ez olyan gyakori feladat, amelyet az emberek meg akarnak csinálni, sok mindent összekapcsolnak. Most határolónk a „,” -ban, és ennek a funkciónak a nagyszerű eleme, hogy elmondhatjuk az üres cellák figyelmen kívül hagyásának. Most feltehetem az IGAZ, 1 vagy Hagyhatom, kihagyhatom. Szóval, otthagyom, kihagyom. És itt van szükségünk a szövegünkre. Az IF függvény segítségével kiszűrjük és megszerezzük a kívánt elemeket. Azt akarom mondani, hogy nézze át ezt az egész oszlopot: A táblázat neve, majd a () mező mezőben, bármelyikőtök = erre a relatív cellahivatkozásra, ez a logikai teszt. Ha rákattintok és az F9 billentyűre kattintok az értékeléshez,láthatod, hogy most csak 2 IGAZZSÁGUNK van, a Ctrl + Z. Most vesszőt írok, és a Trues és Falses tömbjével most megadhatom neki a kiválasztandó elemeket. Tehát most ebből a tartományból csak azokat az elemeket választjuk ki, amelyeknek IGAZA van. Vesszővel szeretnénk feltétlenül beírni a „” karaktert - ez üres cellaként jelenik meg a TEXTJOIN második argumentuma szempontjából.
Now, I'm going to close parenthesis and now the IF function will create that string of Trues and Falses, the actual items from this range will be picked up if it sees it True and all the other items will have that empty cell. And guess what? TEXTJOIN will totally ignore all of those empty cells and return just the items that match this ID, and then join it with that delimiter. Now this is definitely an Array formula that requires the special keystroke Ctrol+Shift+Enter. The logical test argument holds our Array operation and that argument cannot calculate this Array operation correctly unless we use the keyboard Ctrl+Shift+Enter. Now I'm going to close parentheses. Actually we could prove 1 right here in Text 1 if I F9 all this, we could see we get the 2 items, the rest of those empty cells will be ignored. Ctrl+Z. Now, let's enter this into the cell with Ctrl+Shift+Enter. Immediately look up to the Formula Bar. Those curly brackets are Excel telling you it understood and calculated this as an Array formula. Now I can double-click and send it down. That is looking good.
Megyek az utolsó cellába, és lenyomom az F2 billentyűt, hogy ellenőrizzem, hogy az összes tartomány helyesen néz-e ki. Most nem akarom megtenni, hogy nem akarom megütni az Enter billentyűt, mert ez a képlet, miután Szerkesztési módba helyeztük, csak akkor számít helyesen, ha a Ctrl + Shift + Enter billentyűt használjuk; vagy mivel már megadtuk a képletet, az Esc billentyűvel egyszerűen visszatérhetünk a cellába, mielőtt a Szerkesztés módba állítanánk.
Most teszteljük ezt. Kattintson az utolsó cellára itt, és megnyomom a Tab gombot, majd beírok egy új azonosítót, Tab, Tab. Egy másik új lemez, a Tab, és már látom, hogy nem volt elég munkám itt. Én vagyok, betesszük - Tökéletes, majd Enter. Ez nem fog automatikusan frissülni, mintha egy csomó képletünk lenne, amelyek egyedi elemeket számolunk, majd egyedi elemeket vonunk ki, de nem probléma. Ezt nézd. Frissíthetjük ezt az egyedi rekordok listáját, mert az Advanced Filter-t használtuk, és mindegy, hogy melyik cellából indul ki, mert az Advanced Filter meghívásakor megjegyzi a kivonat tartományát és az eredetileg nézett tartományokat. Kattintson a Speciális szűrő gombra, vagy használhatja az Alt + A + Q billentyűzetet. Ki kell választanunk a Másolás más helyre lehetőséget, de ezt nézzük meg.Teljesen emlékezett az A13-ra, és kibővült az Excel táblázat szolgáltatás miatt. Eszébe jutott a kivonat tartománya. Csak az egyedi rekordokat kell ellenőriznem, de kattintson az OK gombra.
Most át kell mennem és le kell másolnom ezt a képletet. És tessék, használja a Speciális szűrőt és a csodálatos TEXTJOIN funkciót a Array műveletben, hogy csak a megfelelő elemeket kapja meg. Rendben, dobd vissza.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Ó, hé, szeretnék köszönetet mondani mindenkinek, hogy betértek. Legközelebb találkozunk egy másik Dueling Excel podcast-lal, és az Excel Fun.
Fájl letöltése
Töltse le a minta fájlt innen: Duel183.xlsm
