Прескочи на основното съдържание

Сумиране, ако клетките съдържат определен текст в друга колона

Автор: Xiaoyang Последна промяна: 2021-11-19

Този урок ще ви покаже как да сумирате стойности, ако клетките съдържат специфичен или частичен текст в друга колона. Вземете диапазона от данни по-долу като пример, за да получите общите количества на продуктите, съдържащи текста „Тениска“, както функцията SUMIF, така и функцията SUMPRODUCT могат да се справят с тази задача в Excel.

Сумирайте стойностите, ако клетката съдържа конкретен или частичен текст с функция SUMIF

За да сумирате стойности, ако клетка съдържа конкретен текст в друга колона, можете да използвате функцията SUMIF със заместващ знак (*), общият синтаксис е:

Обща формула с твърдо кодиран текст:

=SUMIF(range,"*text*",sum_range)
  • range: Диапазонът от данни, който искате да оцените с помощта на критериите;
  • *text*: Критериите, въз основа на които искате да сумирате стойностите. Тук заместващият знак * се използва за намиране на произволен брой знаци, за да съответства на всички елементи, които съдържат определен текст, поставете текста между двата знака *. (Имайте предвид, че трябва да оградите текста и заместващия знак в двойни кавички.)
  • sum_range: Диапазонът от клетки със съвпадащи числови стойности, които искате да сумирате.

Обща формула с препратка към клетка:

=SUMIF(range,"*"&cell&"*",sum_range)
  • range: Диапазонът от данни, който искате да оцените с помощта на критериите;
  • "*"&cell&"*": Критериите, въз основа на които искате да сумирате стойностите;
    • *: Заместващият знак, който намира произволен брой знаци.
    • клетка: Клетката съдържа конкретния текст, който да търсите.
    • &: Този оператор за конкатенация (&) се използва за свързване на препратка към клетка със звездичките.
  • sum_range: Диапазонът от клетки със съвпадащи числови стойности, които искате да сумирате.

След като знаете основните операции на функцията, моля, използвайте някоя от следните формули, от които се нуждаете, и след това натиснете Въведете ключ за получаване на резултата:

=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12)                   (Use a cell reference)

Забележка: Тази функция SUMIF не е чувствителна към главни и малки букви.

Сумирайте стойностите, ако клетката съдържа конкретен или частичен текст с функцията SUMPRODUCT

Функцията SUMPRODUCT също може да ви помогне да завършите тази работа, без да използвате заместващ знак. Тук трябва да обвиете функциите ISNUMBER и SEARCH във функция SUMPRODUCT, общият синтаксис е:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • sum_range: Диапазонът от клетки със съвпадащи числови стойности, които искате да сумирате;
  • criteria: Критериите, въз основа на които искате да сумирате стойностите. Това може да бъде препратка към клетка или конкретен текст, който сте дефинирали;
  • range: Диапазонът от данни, който искате да оцените с помощта на критериите;

Моля, използвайте някоя от формулите по-долу в празна клетка и натиснете Въведете ключ за връщане на резултата:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12))))                    (Use a cell reference)

Обяснение на тази формула:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("Тениска",$A$2:$A$12))))

  • ТЪРСЕНЕ("Тениска",$A$2:$A$12): Функцията SEARCH връща местоположението на конкретния текст “T-Shirt” от диапазона от данни A2:A12, така че ще получите масив като този: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#СТОЙНОСТ!;#СТОЙНОСТ!;#СТОЙНОСТ!;#СТОЙНОСТ!;7}.
  • ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Тази функция ISNUMBER се използва за тестване на числовите стойности и връща нов масив: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
  • $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: За да умножите тези два масива, математическата операция принуждава стойностите TRUE и FALSE да бъдат 1s и 0s. И така, умножението на двата масива ще бъде така: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
  • SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Накрая функцията SUMPRODUCT сумира всички стойности в масива, за да получи резултата:1605.

Използвана относителна функция:

  • SUMIF:
  • Функцията SUMIF може да помогне за сумиране на клетки въз основа на един критерий.
  • SUMPRODUCT:
  • Функцията SUMPRODUCT може да се използва за умножаване на две или повече колони или масиви заедно и след това да се получи сумата от продуктите.
  • ISNUMBER:
  • Функцията ISNUMBER на Excel връща TRUE, когато клетка съдържа число, и FALSE, ако не.
  • ТЪРСИ:
  • Функцията ТЪРСЕНЕ може да ви помогне да намерите позицията на конкретен знак или подниз от даден текстов низ

Още статии:

  • Сума на най-малките или долните N стойности
  • В Excel за нас е лесно да сумираме диапазон от клетки с помощта на функцията SUM. Понякога може да се наложи да сумирате най-малките или долните 3, 5 или n числа в диапазон от данни, както е показано на екранната снимка по-долу. В този случай SUMPRODUCT заедно с функцията SMALL може да ви помогне да разрешите този проблем в Excel.

Най-добрите инструменти за производителност в офиса

Kutools за Excel - помага ви да се откроите от тълпата

🤖 Kutools AI помощник: Революционизирайте анализа на данни въз основа на: Интелигентно изпълнение   |  Генериране на код  |  Създаване на персонализирани формули  |  Анализирайте данни и генерирайте диаграми  |  Извикване на функциите на Kutools...
Популярни функции: Намерете, маркирайте или идентифицирайте дубликати  |  Изтриване на празни редове  |  Комбинирайте колони или клетки без загуба на данни  |  Кръг без формула ...
Супер VLookup: Множество критерии  |  Множество стойности  |  В много листове  |  Размито търсене...
адв. Падащ списък: Лесен падащ списък  |  Зависим падащ списък  |  Падащ списък с множество избори...
Мениджър на колони: Добавете конкретен брой колони  |  Преместване на колони  |  Превключване на състоянието на видимост на скритите колони  |  Сравнете колони с Изберете Същите и различни клетки ...
Препоръчани функции: Мрежов фокус  |  Изглед на дизайна  |  Голям формула бар  |  Мениджър на работни книги и листове | Библиотека с ресурси (Автоматичен текст)  |  Избор на дата  |  Комбинирайте работни листове  |  Шифроване/декриптиране на клетки  |  Изпращайте имейли по списък  |  Супер филтър  |  Специален филтър (филтър получер/курсив/зачертано...) ...
Топ 15 комплекта инструменти:  12 Текст Инструменти (добавяне на текст, Премахване на символи ...)  |  50 + Графика Видове (диаграма на Гант ...)  |  40+ Практичен формули (Изчислете възрастта въз основа на рождения ден ...)  |  19 вмъкване Инструменти (Въведете QR код, Вмъкване на картина от пътя ...)  |  12 Конверсия Инструменти (Числа към думи, Валутен обмен ...)  |  7 Обединяване и разделяне Инструменти (Разширено комбиниране на редове, Разделете клетки на Excel ...)  |  ... и още

Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...

Описание

Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)

  • Една секунда за превключване между десетки отворени документи!
  • Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
  • Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
  • Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations