tartalom
Már írtam arról, hogyan lehet több cellából gyorsan egy szöveget ragasztani, és fordítva, egy hosszú szöveges karakterláncot komponensekké elemezni. Most nézzünk meg egy közeli, de kicsit összetettebb feladatot – hogyan ragassunk szöveget több cellából, ha egy bizonyos meghatározott feltétel teljesül.
Tegyük fel, hogy van egy ügyféladatbázisunk, ahol egy cégnév megfelelhet az alkalmazottak több különböző e-mail-címének. Az a feladatunk, hogy az összes címet cégnevek szerint összegyűjtsük és összefűzzük (vesszővel vagy pontosvesszővel elválasztva), hogy például levelezőlistát készítsünk az ügyfeleknek, azaz valami ilyesmi kimenetet kapjunk:
Más szóval, szükségünk van egy eszközre, amely a feltételnek megfelelően összeragasztja (linkeli) a szöveget - a függvény analógja SUMMESLI (SUMIF), hanem szövegre.
0. módszer. Képlet
Nem túl elegáns, de a legegyszerűbb. Írhat egy egyszerű képletet, amely ellenőrzi, hogy a következő sorban lévő cég eltér-e az előzőtől. Ha nem különbözik, akkor a következő címet ragasszuk vesszővel elválasztva. Ha eltér, akkor újrakezdjük a felhalmozott értéket:
Ennek a megközelítésnek a hátrányai nyilvánvalóak: a kapott további oszlop összes cellájából cégenként csak az utolsóra van szükségünk (sárga). Ha a lista nagy, akkor a gyors kijelölésük érdekében a funkció segítségével újabb oszlopot kell hozzáadnia DLSTR (LEN), a felhalmozott karakterláncok hosszának ellenőrzése:
Most már kiszűrheti a megfelelőket, és a szükséges címragasztást átmásolhatja a további felhasználáshoz.
1. módszer. Egy feltétellel történő ragasztás makrofunkciója
Ha az eredeti lista nincs cég szerint rendezve, akkor a fenti egyszerű képlet nem működik, de egy kis egyedi funkcióval könnyedén megkerülheti a VBA-ban. Nyissa meg a Visual Basic Editort egy billentyűparancs megnyomásával Alt + F11 vagy a gomb segítségével Visual Basic lap fejlesztő (Fejlesztő). A megnyíló ablakban szúrjon be egy új üres modult a menün keresztül Beszúrás – Modul és másold oda a függvényünk szövegét:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Elválasztó As String, i As Long Delimeter = ", " ragasztások nem egyenlőek egymással - hibával lépünk ki, ha SearchRange.Count <> TextRange.Count Akkor MergeIf = CVERr(xlErrRef) Kilépés a függvény vége Ha 'megyünk végig az összes cellán, ellenőrizzük a feltételt, és gyűjtsük össze a szöveget az OutText For i = 1 To SearchRange változóban. Cells.Count If Search Range.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Következő i 'az eredmények megjelenítése utolsó határoló nélkül MergeIf = Bal(Kiszöveg, Len(Kiszöveg) - Len(Elválasztó)) Vége funkció
Ha most visszatér a Microsoft Excelhez, akkor a függvénylistában (gomb fx a képletsávban vagy a lapon Képletek – Függvény beszúrása) lehet majd megtalálni a funkciónkat MergeIf kategóriában Felhasználó által meghatározott (Felhasználó által meghatározott). A függvény argumentumai a következők:
2. módszer: Szöveg összefűzése pontatlan feltétel alapján
Ha a makrónk 13. sorában kicseréljük az első karaktert = a hozzávetőleges meccskezelőnek Mint, akkor lehetséges lesz a ragasztás a kiindulási adatok és a kiválasztási kritérium pontatlan illeszkedésével. Például, ha a cégnév többféle változatban is felírható, akkor ezeket egy funkcióval ellenőrizhetjük és összegyűjthetjük:
A szabványos helyettesítő karakterek támogatottak:
- csillag (*) – tetszőleges számú karaktert jelöl (beleértve azok hiányát is)
- kérdőjel (?) – bármely karaktert jelöl
- font jel (#) – bármely számjegy (0-9)
A Like operátor alapértelmezés szerint megkülönbözteti a kis- és nagybetűket, azaz például az „Orion” és az „orion” különböző cégeket érti. A kis- és nagybetűk figyelmen kívül hagyásához hozzáadhatja a sort a modul legelejére a Visual Basic szerkesztőben Lehetőség Szöveg összehasonlítása, amely átváltja a Like to be kis- és nagybetűket.
Ily módon nagyon összetett maszkokat állíthat össze a feltételek ellenőrzéséhez, például:
- ?1##??777RUS – a 777-es régió összes rendszámának kiválasztása, 1-től kezdve
- LLC* – minden olyan vállalat, amelynek neve LLC-vel kezdődik
- ##7## – minden termék ötjegyű digitális kóddal, ahol a harmadik számjegy 7
- ?????? – minden ötbetűs név stb.
3. módszer. Makró funkció szöveg ragasztásához két feltétel mellett
A munkában probléma adódhat, ha egynél több feltételt kell összekapcsolnia a szöveggel. Például képzeljük el, hogy az előző táblázatunkban még egy oszlopot adtunk hozzá a várossal, és a ragasztást nem csak adott cégnél, hanem városnál is el kell végezni. Ebben az esetben a funkciónkat kissé korszerűsíteni kell egy újabb tartományellenőrzéssel:
Függvény MergeIfs(SzövegTartomány Tartományként, Keresési tartomány1 Tartományként, Feltétel1 Karakterláncként, Keresési tartomány2 Tartományként, Feltétel2 Karakterláncként) Dim Elválasztó karakterként, i As Long Delimeter = ", " 'határoló karakterek (helyettesíthetők szóközzel vagy ; stb.). e.) 'ha az érvényesítési és ragasztási tartományok nem egyenlőek egymással, hibával lépjen ki If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVERr(xlErrRef) Kilépés a függvényből End If menjen végig az összes cellán, ellenőrizze az összes feltételt, és gyűjtse össze a szöveget az OutText változóba For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Feltétel1 És SearchRange2.Cells(i) = Feltétel2 Akkor OutText = OutText & TextRange.Cells(i) & Elválasztó vége Ha Következő i 'megjeleníti az eredményeket utolsó határoló nélkül MergeIfs = Bal(Kiszöveg, Len(Kiszöveg) - Len(Elválasztó)) Végfüggvény
Ugyanúgy lesz alkalmazva – csak az argumentumokat kell többet megadni:
4. módszer. Csoportosítás és ragasztás a Power Queryben
A probléma VBA-ban történő programozás nélkül is megoldható, ha az ingyenes Power Query bővítményt használja. Excel 2010-2013-hoz innen tölthető le, az Excel 2016-ban pedig már alapból be van építve. A műveletek sorrendje a következő lesz:
A Power Query nem tudja, hogyan kell normál táblákkal dolgozni, ezért az első lépés az, hogy a táblázatunkat „okossá” alakítsuk. Ehhez válassza ki, és nyomja meg a kombinációt Ctrl+T vagy válasszon a lapról Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása). Az ezután megjelenő lapon építész (Tervezés) beállíthatja a táblázat nevét (elhagytam a szabványt Táblázat 1):
Most töltsük be a táblázatunkat a Power Query bővítménybe. Ehhez a lapon dátum (ha van Excel 2016), vagy a Power Query lapon (ha van Excel 2010-2013) kattintson Az asztalról (Adatok – táblázatból):
A megnyíló lekérdezésszerkesztő ablakban válassza ki az oszlopot a fejlécre kattintva Cégünkről és nyomja meg a fenti gombot Csoport (Csoportosít). Adja meg az új oszlop nevét és a művelet típusát a csoportosításban – Minden sor (Minden sor):
Kattintson az OK gombra, és megkapjuk az egyes cégekhez tartozó csoportosított értékek minitáblázatát. A táblázatok tartalma jól látható, ha a kapott oszlopban bal egérgombbal a cellák fehér hátterére (nem a szövegre!) kattintunk:
Most adjunk hozzá még egy oszlopot, ahol a függvény segítségével az egyes minitáblázatok Cím oszlopainak tartalmát vesszővel elválasztva felragasztjuk. Ehhez a lapon Oszlop hozzáadása megnyomjuk Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop) és a megjelenő ablakban írja be az új oszlop nevét és a csatolási képletet a Power Querybe épített M nyelven:
Vegye figyelembe, hogy az összes M-függvény megkülönbözteti a kis- és nagybetűket (ellentétben az Excellel). Kattintás után OK kapunk egy új oszlopot ragasztott címekkel:
Marad a már felesleges oszlop eltávolítása Táblázatcímek (jobb klikk a címre) Oszlop törlése), és a lapra kattintva töltse fel az eredményeket a lapra Kezdőlap — Zárja be és töltse le (Kezdőlap – bezárás és betöltés):
Fontos árnyalat: Az előző módszerekkel (függvényekkel) ellentétben a Power Query táblái nem frissülnek automatikusan. Ha a jövőben bármilyen változás történik a forrásadatokban, akkor jobb gombbal kell kattintania bárhol az eredménytáblázatban, és ki kell választania a parancsot Frissítés és mentés (Frissítés).
- Hogyan lehet egy hosszú szöveges karakterláncot részekre bontani
- Számos módja annak, hogy különböző cellákból egy szöveget ragasszon
- A Like operátor használata a szöveg maszkkal szembeni tesztelésére