Гледайте видео на знаците на заместването на Excel
Има само 3 заместващи знака на Excel (звездичка, въпросителен знак и тилда) и с тях може да се направи много.
В този урок ще ви покажа четири примера, при които тези заместващи знаци на Excel са абсолютни спасители.
Заместващи знаци на Excel - Въведение
Заместващите символи са специални знаци, които могат да заемат всяко място на всеки знак (оттук и името - заместващ знак).
В Excel има три заместващи знака:
- * (звездичка) - Той представлява произволен брой знаци. Например Ex* може да означава Excel, Excels, Example, Expert и т.н.
- ? (въпросителен знак) - Той представлява един единствен знак. Например, Tr? Mp може да означава Тръмп или Бродяга.
- ~ (тилда) - Използва се за идентифициране на заместващ знак (~, *,?) В текста. Например, да предположим, че искате да намерите точната фраза Excel* в списък. Ако използвате Excel* като низ за търсене, той ще ви даде всяка дума, която има Excel в началото, последвана от произволен брой знаци (като Excel, Excels, Excellent). За да търсим специално Excel*, трябва да използваме ~. Така че нашият низ за търсене ще бъде excel ~*. Тук наличието на ~ гарантира, че Excel чете следния знак такъв, какъвто е, а не като заместващ знак.
Забележка: Не съм попадал на много ситуации, в които трябва да използвате ~. Въпреки това е добре да знаете функцията.
Сега нека преминем през четири страхотни примера, при които заместващите знаци вършат цялата тежка работа.
Заместващи знаци на Excel - Примери
Нека сега разгледаме четири практически примера, при които заместващите знаци на Excel могат да бъдат изключително полезни:
- Филтриране на данни с помощта на заместващ знак.
- Частично търсене с заместващ знак и VLOOKUP.
- Намерете и заменете частични съвпадения.
- Пребройте непразни клетки, които съдържат текст.
#1 Филтрирайте данни с помощта на заместващи знаци на Excel
Заместващите знаци на Excel са полезни, когато имате огромни набори от данни и искате да филтрирате данни въз основа на условие.
Да предположим, че имате набор от данни, както е показано по -долу:
Можете да използвате заместващ знак звездичка (*) във филтъра за данни, за да получите списък с компании, които започват с азбуката А.
Ето как да направите това:
- Изберете клетките, които искате да филтрирате.
- Отидете на Данни -> Сортиране и филтриране -> Филтър (Клавишна комбинация - Control + Shift + L).
- Кликнете върху иконата на филтъра в заглавната клетка
- В полето (под опцията Текстов филтър) въведете A*
- Щракнете върху OK.
Това незабавно ще филтрира резултатите и ще ви даде 3 имена - ABC Ltd., Amazon.com и Apple Stores.
Как работи? - Когато добавите звездичка (*) след А, Excel ще филтрира всичко, което започва с А. Това е така, защото звездичка (като заместващ знак на Excel) може да представлява произволен брой знаци.
Сега със същата методология можете да използвате различни критерии за филтриране на резултатите.
Например, ако искате да филтрирате компании, които започват с азбуката А и съдържат азбуката С в нея, използвайте низа A*C. Това ще ви даде само 2 резултата - ABC Ltd. и Amazon.com.
Ако използвате A? C вместо това ще получите само ABC Ltd като резултат (тъй като само един знак е разрешен между „a“ и „c“)
Забележка: Същата концепция може да се приложи и при използване на разширени филтри на Excel.
#2 Частично търсене с помощта на заместващи знаци и VLOOKUP
Частично търсене е необходимо, когато трябва да търсите стойност в списък и няма точно съвпадение.
Да предположим например, че имате набор от данни, както е показано по -долу, и искате да потърсите компанията ABC в списък, но в списъка има ABC Ltd вместо ABC.
В този случай не можете да използвате обикновената функция VLOOKUP, тъй като стойността за търсене няма точно съвпадение.
Ако използвате VLOOKUP с приблизително съвпадение, това ще ви даде грешни резултати.
Можете обаче да използвате заместващ знак във функцията VLOOKUP, за да получите правилните резултати:
Въведете следната формула в клетка D2 и я плъзнете за други клетки:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Как действа тази формула?
В горната формула, вместо да се използва стойността за търсене такава, каквато е, тя е оградена от двете страни със звездичка за заместване на Excel (*) - “*” & C2 & ”*”
Това казва на Excel, че трябва да търси всеки текст, който съдържа думата в C2. Той може да има произволен брой знаци преди или след текста в C2.
Следователно формулата търси съвпадение и веднага щом получи съвпадение, връща тази стойност.
3. Намерете и заменете частични съвпадения
Заместващите знаци на Excel са доста гъвкави.
Можете да го използвате в сложна формула, както и в основни функционалности като Find and Replace.
Да предположим, че имате данните, както е показано по -долу:
В горните данни регионът е въведен по различни начини (като северозапад, северозапад, северозапад).
Това често се случва с данните за продажбите.
За да изчистим тези данни и да ги направим последователни, можем да използваме заместващи знаци „Намери и замени с Excel“.
Ето как да направите това:
- Изберете данните, в които искате да намерите, и заменете текста.
- Отидете на Начало -> Намиране и избор -> Отиди на. Това ще отвори диалоговия прозорец Намери и замени. (Можете също да използвате клавишната комбинация - Control + H).
- Въведете следния текст в диалоговия прозорец за намиране и замяна:
- Намери какво: Север*W*
- Замени с: Северозападна
- Кликнете върху Замени всички.
Това незабавно ще промени всички различни формати и ще го направи съвместим със Северозапад.
Как работи това?
В полето Find намерихме Север*W* който ще намери всеки текст, който има думата Север и съдържа азбуката „W“ навсякъде след нея.
Следователно, той обхваща всички сценарии (Северозапад, Северозапад и Северозапад).
Find and Replace намира всички тези екземпляри и го променя на North-West и го прави последователен.
4. Пребройте непразни клетки, които съдържат текст
Знам, че сте умни и мислите, че Excel вече има вградена функция за това.
Ти си абсолютно прав!!
Това може да стане с помощта на функцията COUNTA.
НО … Има един малък проблем с него.
Много пъти, когато импортирате данни или използвате работния лист на други хора, ще забележите, че има празни клетки, но това може да не е така.
Тези клетки изглеждат празни, но в тях има = ””. Проблемът е, че
Проблемът е, че функцията COUNTA не счита това за празна клетка (брои го като текст).
Вижте примера по -долу:
В горния пример използвам функцията COUNTA, за да намеря клетки, които не са празни и тя връща 11, а не 10 (но можете ясно да видите, че само 10 клетки имат текст).
Причината, както споменах, е, че не счита A11 за празен (докато трябва).
Но така работи Excel.
Поправката е да се използва заместващ знак на Excel във формулата.
По -долу е формула, използваща функцията COUNTIF, която брои само клетки, които имат текст в нея:
= COUNTIF (A1: A11, "?*")
Тази формула казва на Excel да брои само ако клетката има поне един знак.
В ?* комбо:
- ? (въпросителен знак) гарантира, че присъства поне един знак.
- * (звездичка) освобождава място за произволен брой допълнителни знаци.
Забележка: Горната формула работи, когато има само текстови стойности в клетките. Ако имате списък, който има както текст, така и числа, използвайте следната формула:
= COUNTA (A1: A11) -COUNTBLANK (A1: A11)
По същия начин можете да използвате заместващи знаци в много други функции на Excel, като IF (), SUMIF (), AVERAGEIF () и MATCH ().
Интересно е също да се отбележи, че макар да можете да използвате заместващи знаци във функцията SEARCH, не можете да ги използвате във функцията FIND.
Надявам се, че тези примери ви дават усет за гъвкавостта и силата на заместващите символи на Excel.
Ако имате друг иновативен начин да го използвате, споделете го с мен в секцията за коментари.
Може да намерите за полезни следните уроци по Excel:
- Използване на COUNTIF и COUNTIFS с множество критерии.
- Създаване на падащ списък в Excel.
- Оператор на пресичане в Excel
