Videó megtekintése az Excel helyettesítő karakterein
Csak 3 Excel helyettesítő karakter van (csillag, kérdőjel és tilde), és ezekkel sokat lehet tenni.
Ebben az oktatóanyagban négy példát mutatok be, ahol ezek az Excel helyettesítő karakterek abszolút életmentők.
Excel helyettesítő karakterek - Bevezetés
A helyettesítő karakterek speciális karakterek, amelyek bármely karakter helyét elfoglalhatják (innen a név - helyettesítő karakter).
Az Excelben három helyettesítő karakter található:
- * (csillag) - Bármilyen számú karaktert képvisel. Például az Ex* jelentése Excel, Excels, Példa, Szakértő stb.
- ? (kérdőjel) - Egyetlen karaktert képvisel. Például Tr? Mp jelentheti Trumpot vagy Trampot.
- ~ (tilde) - Egy helyettesítő karakter (~, *,?) Azonosítására szolgál a szövegben. Tegyük fel például, hogy a pontos Excel* kifejezést szeretné megtalálni egy listában. Ha az Excel -t* használja keresési karakterláncként, akkor minden olyan szót megad, amelynek elején az Excel szerepel, és tetszőleges számú karakter (például Excel, Excel, Kiváló). Ahhoz, hogy kifejezetten az excelt*keressük, a ~ -ot kell használnunk. Tehát a keresési karakterláncunk excel ~*lenne. Itt a ~ jelenléte biztosítja, hogy az Excel a következő karaktert úgy olvassa, ahogy van, és nem helyettesítő karakterként.
Megjegyzés: Nem sok olyan helyzettel találkoztam, ahol a ~ -ot kell használni. Ennek ellenére jó tudni ezt a funkciót.
Nézzünk most négy félelmetes példát, ahol a helyettesítő karakterek végzik a nehéz feladatokat.
Excel helyettesítő karakterek - példák
Nézzünk most négy gyakorlati példát, ahol az Excel helyettesítő karakterei rendkívül hasznosak lehetnek:
- Adatok szűrése helyettesítő karakter használatával.
- Részleges keresés helyettesítő karakter és VLOOKUP használatával.
- Részleges egyezések keresése és cseréje.
- Szám A szöveget tartalmazó nem üres cellák számolása.
#1 Adatok szűrése Excel helyettesítő karakterek használatával
Az Excel helyettesítő karakterei akkor hasznosak, ha hatalmas adatkészletei vannak, és az adatokat egy feltétel alapján szeretné szűrni.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Az adatszűrőben a csillag (*) helyettesítő karaktert használhatja az A betűvel kezdődő vállalatok listájának megtekintéséhez.
Ezt a következőképpen teheti meg:
- Válassza ki a szűrni kívánt cellákat.
- Lépjen az Adatok -> Rendezés és szűrés -> Szűrő menüpontra (Billentyűparancs - Control + Shift + L).
- Kattintson a szűrő ikonra a fejléc cellájában
- Írja be a mezőbe (a Szűrőszűrő opció alatt) A*
- Kattintson az OK gombra.
Ez azonnal kiszűri az eredményeket, és 3 nevet ad - ABC Ltd., Amazon.com és Apple Store.
Hogyan működik? - Ha az A után csillagot (*) ad hozzá, az Excel minden A betűvel kezdődő dolgot szűr. Ez azért van, mert a csillag (Excel helyettesítő karakter) tetszőleges számú karaktert jelenthet.
Most ugyanazzal a módszertannal különböző kritériumokat használhat az eredmények szűrésére.
Ha például olyan vállalatokat szeretne szűrni, amelyek A betűvel kezdődnek, és a C ábécét tartalmazzák, használja a karakterláncot A*C. Ez csak 2 eredményt ad - az ABC Ltd. és az Amazon.com.
Ha használja A? C ehelyett csak az ABC Ltd -t kapja eredményül (mivel csak egy karakter megengedett az „a” és „c” között)
Jegyzet: Ugyanez a koncepció alkalmazható Excel speciális szűrők használatakor is.
#2 Részleges keresés helyettesítő karakterek és VLOOKUP használatával
Részleges keresésre akkor van szükség, ha értéket kell keresnie a listában, és nincs pontos egyezés.
Tegyük fel például, hogy rendelkezik adathalmazzal az alábbiak szerint, és az ABC vállalatot szeretné keresni egy listában, de a listán az ABC Ltd szerepel.
Ebben az esetben nem használhatja a szokásos VLOOKUP funkciót, mivel a keresési érték nem egyezik pontosan.
Ha a VLOOKUP -ot hozzávetőleges egyezéssel használja, az rossz eredményeket ad.
Azonban a VLOOKUP függvényen belül helyettesítő karaktert is használhat a megfelelő eredmények eléréséhez:
Írja be a következő képletet a D2 cellába, és húzza a többi cellához:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Hogyan működik ez a képlet?
A fenti képletben ahelyett, hogy a keresési értéket használná, mindkét oldalán az Excel helyettesítő karakter csillaggal (*) van szegélyezve - "*" & C2 & "*"
Ez azt mondja az excelnek, hogy meg kell keresnie minden olyan szöveget, amely tartalmazza a szót a C2 -ben. A C2 szöveg előtt vagy után tetszőleges számú karakter lehet.
Ezért a képlet egyezést keres, és amint egyezést kap, visszaadja ezt az értéket.
3. Keresse meg és cserélje ki a részleges illesztéseket
Az Excel helyettesítő karakterei sokoldalúak.
Használhatja komplex képletben, valamint olyan alapvető funkciókban, mint a Find and Replace.
Tegyük fel, hogy megvannak az alábbi adatok:
A fenti adatok szerint a régiót különböző módon adták meg (például Északnyugat, Északnyugat, Északnyugat).
Gyakran ez a helyzet az értékesítési adatokkal.
Az adatok tisztításához és következetessé tételéhez használjuk a Find and Replace with Excel helyettesítő karaktereket.
Ezt a következőképpen teheti meg:
- Válassza ki azokat az adatokat, ahol szöveget szeretne megtalálni és cserélni.
- Lépjen a Kezdőlapra -> Keresés és kiválasztás -> Ugrás ide. Ezzel megnyílik a Keresés és csere párbeszédpanel. (Használhatja a billentyűparancsot is - Control + H).
- Írja be a következő szöveget a keresés és csere párbeszédpanelbe:
- Megtalálja, amit: Észak*Ny*
- Cseréld ki: Északnyugat
- Kattintson az Összes cseréje gombra.
Ez azonnal megváltoztatja a különböző formátumokat, és konzisztenssé teszi az északnyugati irányt.
Hogy működik ez?
A Keresés mezőben használtuk Észak*Ny* amely megtalálja az északi szót tartalmazó szöveget, amely után bárhol megtalálható a „W” ábécé.
Ezért lefedi az összes forgatókönyvet (északnyugati, északnyugati és északnyugati).
A Find and Replace megkeresi ezeket az eseteket, és északnyugatira változtatja, és következetessé teszi.
4. Számolja a szöveget tartalmazó nem üres cellákat
Tudom, hogy okos vagy, és azt gondolod, hogy az Excel már rendelkezik egy beépített funkcióval.
Teljesen igazad van!!
Ezt megteheti a COUNTA funkció használatával.
DE… Egy apró probléma van vele.
Sokszor, amikor adatokat importál, vagy mások munkalapját használja, észre fogja venni, hogy üres cellák vannak, bár ez nem így van.
Ezek a cellák üresnek tűnnek, de = ”” van benne. A baj az, hogy a
A baj az, hogy a COUNTA függvény ezt nem tekinti üres cellának (hanem szövegnek tekinti).
Lásd az alábbi példát:
A fenti példában a COUNTA függvényt használom a nem üres cellák keresésére, és 11 -et ad vissza, és nem 10 -et (de jól látható, hogy csak 10 cellában van szöveg).
Az ok, ahogy említettem, az, hogy nem tartja üresnek az A11 -et (bár kellene).
De az Excel így működik.
A javítás az Excel helyettesítő karakterének használata a képletben.
Az alábbiakban a COUNTIF függvényt használó képlet található, amely csak azokat a cellákat számolja, amelyekben szöveg van:
= COUNTIF (A1: A11, "?*")
Ez a képlet azt mondja az Excelnek, hogy csak akkor számoljon, ha a cella legalább egy karaktert tartalmaz.
Ban,-ben ?* kombináció:
- ? (kérdőjel) biztosítja, hogy legalább egy karakter jelen legyen.
- * (csillag) teret enged további számú karakternek.
Jegyzet: A fenti képlet akkor működik, ha csak szöveges értékek vannak a cellákban. Ha olyan listája van, amely szöveget és számokat is tartalmaz, használja a következő képletet:
= COUNTA (A1: A11)-COUNTBLANK (A1: A11)
Hasonlóképpen, számos más Excel -funkcióban is használhat helyettesítő karaktereket, például IF (), SUMIF (), AVERAGEIF () és MATCH ().
Érdekes megjegyezni azt is, hogy bár használhatja a helyettesítő karaktereket a SEARCH funkcióban, nem tudja használni a FIND funkcióban.
Reméljük, hogy ezek a példák érzékeltetik az Excel helyettesítő karakterek sokoldalúságát és erejét.
Ha van más innovatív módja annak használatára, ossza meg velem a megjegyzések részben.
Az alábbi Excel oktatóanyagok hasznosak lehetnek:
- A COUNTIF és a COUNTIFS használata több feltétellel.
- Legördülő lista létrehozása Excelben.
- Metsző operátor az Excelben
