Перегляньте відео про символи підстановки 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.
Як це працює? - Коли ви додаєте зірочку (*) після A, Excel фільтруватиме все, що починається з A. Це відбувається тому, що зірочка (яка є символом підстановки Excel) може представляти будь -яку кількість символів.
Тепер за тією ж методологією ви можете використовувати різні критерії для фільтрації результатів.
Наприклад, якщо ви хочете відфільтрувати компанії, які починаються з алфавіту А і містять у ньому алфавіт С, використовуйте рядок A*C. Це дасть вам лише 2 результати - ABC Ltd. та Amazon.com.
Якщо ви використовуєте A? C замість цього в результаті ви отримаєте лише компанію ABC Ltd (оскільки між "а" та "с" допускається лише один символ)
Примітка: Таку саму концепцію також можна застосувати при використанні розширених фільтрів 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 досить універсальні.
Ви можете використовувати його як у складній формулі, так і в базових функціональних можливостях, таких як Знайти та Замінити.
Припустимо, у вас є дані, як показано нижче:
У наведених вище даних регіон вводився різними способами (наприклад, Північно-Західний, Північно-Західний, Північно-Західний).
Часто це стосується даних про продажі.
Щоб очистити ці дані та зробити їх узгодженими, ми можемо використовувати знаки підстановки «Знайти та замінити» Excel.
Ось як це зробити:
- Виберіть дані, де потрібно знайти, і замініть текст.
- Перейдіть додому -> Знайти та вибрати -> Перейти. Відкриється діалогове вікно Знайти та замінити. (Ви також можете скористатися комбінацією клавіш - Control + H).
- Введіть наступний текст у діалоговому вікні пошуку та заміни:
- Знайти що: Північ*З*
- Замінити: Північний захід
- Натисніть Замінити все.
Це миттєво змінить усі різні формати та зробить його узгодженим із Північно-Західним.
Як це працює?
У полі пошуку ми використовували Північ*З* який знайде будь -який текст, що містить слово «Північ» і містить алфавіт «W» в будь -якому місці після нього.
Отже, він охоплює всі сценарії (північно-західний, північно-західний та північно-західний).
Функція "Знайти та замінити" знаходить усі ці екземпляри та змінює їх на Північно-Західну та робить її послідовною.
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
