Изтеглете примерната работна книга
Този урок ще покаже как да използвате функцията SUMIFS и заместващи знаци за сумиране на данни, съответстващи на клетки, които съдържат конкретен текст в Excel и Google Sheets.
Сума, ако текстът съдържа
Първо ще демонстрираме как да сумираме данни, свързани с клетки, съдържащи конкретен текст, използвайки функцията SUMIFS.
Функцията SUMIFS сумира редове с данни, които отговарят на определени критерии. Неговият синтаксис е:
Този пример ще обобщи всичко Резултати с Име на държавата който съдържа „Dakota“, използвайки функцията SUMIFS и * заместващия знак *.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Дакота*") |
Знакът * позволява на всяко място (включително нула) други знаци да заеме неговото място.
В този пример се използва за намиране на всички клетки, които включват текста „Dakota“. Това търсене не е чувствително към регистъра, така че „dakota“ се счита за същото като „Dakota“ или „DAKOTA“. Северна Дакота и Южна Дакота съдържат „Дакота“ и следователно са включени в сумата.
Сума, ако текстът започва с
Знакът * може да се използва и за търсене на клетки, които започват с посочения текст:
| 1 | = SUMIFS (C3: C9, B3: B9, "Ново*") |
Ню Йорк, Ню Джърси, и Ню Мексико започват с „Ново“ и следователно са включени в сумата. Имайте предвид, че с термина за търсене „New*“ текстовата клетка трябва да започва с „New“; простото съдържане на тези знаци не е достатъчно.
Сума, ако текстът завършва с
По същия начин можем да обобщим всичко Резултати за Щати завършващ на „o“, използвайки:
| 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?*") |
Тази функционалност е особено полезна при търсене на продуктови кодове, пощенски кодове или серийни номера, където позицията на всеки знак има специфично значение.
Използване на символа ~ (Tilde)
Специалният символ ~ (известен като 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 Sheets, както и в Excel.
