В тази статия ще се научим да връщаме SUM само от формули в Excel.
С прости думи, докато работите с частично попълнени данни. Понякога трябва да намерим сумата от стойност, която има условие. Условието е да получите сумата, в която стойностите се извличат само с формули.
За тази статия ще се нуждаем от използването на следните функции:
- Функция SUMPRODUCT
- Функция ISFORMULA
Функцията SUMPRODUCT е математическа функция в Excel. Работи в няколко диапазона. Той умножава съответните масиви и след това ги добавя.
Общо = (A1 * B1 * C1 * … + A2 * B2 * C2 * … + …)
Синтаксис:
| = SUMPRODUCT (масив1, [масив2], …) |
масив: Това е диапазонът или списък със стойности.
Функцията ISFORMULA връща TRUE, когато формулата съществува в cell_reference и връща FALSE в противен случай.
Синтаксис:
| = ISFORMULA (справка) |
справка: препратка към клетка, къде да проверите формулата
Сега ще направим формула от тези функции. Тук ще дадем данните и се нуждаем от сумарни резултати, когато се прилага формулата.
Използвайте формулата:
| = SUMPRODUCT (масив * ISFORMULA (масив)) |
Обяснение:
- Функцията ISFORMULA връща TRUE & FALSE въз основа на проверяваното условие в клетките.
- Функцията SUMPRODUCT отчита стойност 1 за всяка TRUE стойност и 0 за FALSE стойност.
- Продуктът, взет между съответните стойности, ще игнорира FALSE стойностите, тъй като стойностите се умножават по 0s. Само TRUE стойностите получават SUM, тъй като стойностите се умножават по 1s.
Пример:
Нека тестваме тази формула, като я изпълним на пример
Тук имаме данни, в които цената на зърното е извлечена от продукта на количеството към неговата единична цена, а някои стойности на цената са въведени ръчно. Така че, ако трябва да намеря сумата на цената за стойността, от която формулата извлича общата цена.
Сега ще използваме формулата по -долу, за да получим SUM
Формула:
| = СУМПРОДУКТ (E2: E15 * ИЗФОРМУЛА (E2: E15)) |
Обяснение:
- Функцията ISFORMULA връща TRUE & FALSE въз основа на проверяваното условие в клетки от масив от E2 до E15.
- Функцията SUMPRODUCT отчита стойност 1 за всяка получена TRUE стойност и 0 за FALSE стойност, както е показано по -долу.
= СУМПРОДУКТ (E2: E15*
{ ВЯРНО ; FALSE; FALSE; FALSE; ВЯРНО ; ВЯРНО ; FALSE; ВЯРНО ; FALSE; ВЯРНО ; ВЯРНО ; ВЯРНО ; ВЯРНО ; ВЯРНО } )
- Продуктът, взет между съответните стойности, ще игнорира FALSE стойностите, тъй като стойностите се умножават по 0s. Само TRUE стойностите получават SUM, тъй като стойностите се умножават по 1s, както е показано по -долу.
= СУМПРОДУКТ ({58.41; 0; 0; 0; 82.84; 95.58; 0; 90.27; 0; 37.8; 78.48; 57.97; 97.72; 77.88}
Тук масивът на функцията е даден като cell_reference. Натиснете Enter, за да получите резултата.
Както можете да видите в горната снимка, сумата от извлечени стойности, където съществува формулата.
Ако трябва да намерите сумата от стойности, които нямат формула, използвайте функцията NOT с функцията ISFORMULA.
Използвайте формулата:
| = СУМПРОДУКТ (E2: E15 * НЕ (ИЗФОРМУЛА (E2: E15))) |
Използвайте формулата и получете стойността, както е показано на снимката по -долу.
Както можете да видите от горната формула, можете да получите условни стойности.
Бележки:
- Функцията SUMPRODUCT разглежда нецифрени стойности като 0s.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да е със същата дължина, в противен случай функцията връща грешка.
Надявам се, че тази статия за това как да върнете SUM само от формули в Excel е обяснима. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Как да използвате функцията SUMPRODUCT в Excel
Как да премахнете текст в Excel, започвайки от позиция
Валидиране на текстови записи
Създайте падащ списък в Excel с цвят
Премахнете водещите и крайните интервали от текста в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
