Завантажте зразок робочої книги
Цей підручник покаже, як використовувати функцію SUMIFS та символи підстановки для підсумовування даних, відповідних клітинкам, що містять певний текст у Excel та Google Таблицях.
Сума, якщо текст містить
По -перше, ми покажемо, як підсумовувати дані, пов’язані з клітинками, що містять певний текст, за допомогою функції SUMIFS.
Функція SUMIFS підсумовує рядки даних, які відповідають певним критеріям. Його синтаксис такий:
Цей приклад підсумує все Оцінки з Назва штату який містить “Dakota” за допомогою функції SUMIFS та символу підстановки *.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Дакота*") |
Символ * дозволяє будь -якій кількості (включаючи нуль) інших символів зайняти його місце.
У цьому прикладі використовується для пошуку всіх клітинок, які містять текст «Дакота». Цей пошук не враховує регістр, тому "dakota" вважається таким же, як "Dakota" або "DAKOTA". Північна Дакота та Південна Дакота містять "Дакоту" і тому включені до суми.
Сума, якщо текст починається з
Символ * також можна використовувати для пошуку клітинок, які починаються з зазначеного тексту:
| 1 | = SUMIFS (C3: C9, B3: B9, "Новий*") |
Нью-Йорк, Нью Джерсі, і Нью -Мексико починаються з “Нового” і тому включаються до суми. Зауважте, що з пошуковим терміном «Новий*» текстова клітинка повинна починатися з «Новий»; одного вмісту цих символів недостатньо.
Сума, якщо текст закінчується на
Так само ми можемо підсумувати все Оцінки за Штатів закінчується на «о», використовуючи:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Нью -Мексико та Огайо закінчуються на «о» і тому включаються до суми.
Використовуючи? Підстановний символ
The? символ може бути використаний для представлення будь -якого окремого символу в текстовому рядку.
Цей приклад знаходить все Державні назви починаючи з “Нового”, після чого роблять рівно 7 символів (включаючи пробіли).
| 1 | = SUMIFS (C3: C9, B3: B9, "Новий ???????") |
Нью Джерсі та Нью -Мексико відповідають цим критеріям, але Нью-Йорк цього не відбувається, оскільки після "Нового" в ньому є лише 5 символів Нью-Йорк.
Зверніть увагу, що * і? символи підстановки можна комбінувати, якщо потрібно, для створення дуже конкретних команд пошуку. Цей наступний приклад знаходить Державні назви які починаються на “N” і містять “o” перед останнім символом рядка. Це виключає Нью -Мексико; він починається з “N”, але не має “o” перед останнім символом.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Ця функція особливо корисна під час пошуку за кодами продуктів, поштовими індексами або серійними номерами, де положення кожного символу має певне значення.
Використання символу ~ (Тільда)
Спеціальний символ ~ (відомий як a тильда) дозволяє нам лікувати * або? символи так, ніби це прості текстові значення і не поводяться як підстановні знаки.
У наведеному нижче прикладі нам потрібно підсумувати Рівень акцій коли Назва продукту конкретно відповідає тексту "Товар?":
| 1 | = SUMIFS (C3: C8, B3: B8, "Продукт ~?") |
The ~ безпосередньо перед a * або a? символ перетворює його назад у текстове значення, тому пошуковий термін "Продукт ~?" використовується для пошуку точного текстового збігу з "Товаром?".
Поєднання символів підстановки SUMIFS із посиланнями на клітинки
Як правило, не рекомендується перетворювати значення у формули на жорсткий код. Натомість гнучкіше використовувати окремі клітинки для визначення значень для наших пошукових термінів.
Щоб знайти, чи містять комірки текст, що міститься в комірці E3, ми можемо скористатися функцією SUMIFS із посиланням на клітинку та * символами підстановки:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Зверніть увагу, що текст “Dakota” замінено посиланням на клітинку & E3 &, а символи * поставлено в лапки (”“).
Кілька посилань на клітинки та символи підстановки також можна об’єднати разом. Знайти Державні назви які починаються з тексту в комірці E3 і містять текст у комірці F3, за яким слідує ще принаймні 1 символ, можна використати таку формулу:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Блокування посилань на клітинки
Щоб полегшити читання наших формул, ми показали формули без заблокованих посилань на клітинки:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Але ці формули не працюватимуть належним чином при копіюванні та вставці в інше місце у файлі. Замість цього, ви повинні використовувати заблоковані посилання на клітинки, як це:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше.
Сума, якщо клітинка містить певний текст за допомогою символів підстановки у Таблицях Google
Ці формули працюють точно так само в Google Таблицях, як і в Excel.
