В тази статия ще научим как да използваме функцията IF вместо SUMPRODUCT и SUMIFS в Excel.
Сценарий:
С прости думи, когато работим с дълъг разпръснат набор от данни, понякога трябва да намерим сумата от числа с някои критерии над нея. Например намирането на сумата на заплатите в определен отдел или наличието на множество критерии за дата, имена, отдел или дори може да номерира данни като заплати под стойност или количество над стойност. За това обикновено използвате функцията SUMPRODUCT или SUMIFS. Но няма да повярвате, изпълнявате същата функция с основната функция на Excel IF функция.
Как да решим проблема?
Трябва да мислите как е възможно това, да извършвате логически операции над масиви от таблици, използвайки IF функция. IF функцията в excel е много полезна, тя ще ви преведе през някои трудни задачи в Excel или други езици за кодиране. Функцията IF тества условията в масива, съответстващи на необходимите стойности, и връща резултата като масив, съответстващ на True условия като 1 и False като 0.
За този проблем ще използваме следните функции:
- SUM функция
- IF функция
Ние ще изискваме тези по -горе функции и някакъв основен смисъл на работа с данни. логически условия към масиви могат да бъдат приложени с помощта на логически оператори. Тези логически оператори работят както върху текст, така и върху числа. По -долу тук е общата формула. { } къдравите скоби са вълшебният инструмент за изпълнение на формули за масиви с функция IF.
Обща формула:
| { = SUM (IF ((логически_1) * (логически_2) * … * (логически_n), сума_масив)) } |
Забележка: За къдрави скоби ( { } ) Използвайте Ctrl + Shift + Enter при работа с масиви или диапазони в Excel. Това ще генерира къдрави скоби във формулата по подразбиране. НЕ се опитвайте да кодирате твърдо фигурни скоби.
Логически 1: тества условие 1 на масив 1
Логично 2: тества условие 2 на масив 2 и така нататък
sum_array: масив, сумата на операцията се изпълнява
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу. Тук имаме данни за доставени продукти до различни градове, заедно със съответните полета на категории и количества. Тук имаме данните и трябва да намерим количеството бисквитки, изпратени до Бостън, където количеството е по -голямо от 40.
Таблицата с данни и таблицата с критерии са показани на горното изображение. За целите на разбирането използвахме именовани диапазони за използваните масиви. Назованите диапазони са изброени по -долу.
Тук :
Град, определен за масив A2: A17.
Категория, определена за масив B2: A17.
Количество, определено за масив C2: C17.
Сега сте готови да получите желания резултат, като използвате формулата по -долу.
Използвайте формулата:
| { = SUM (IF ((City = "Boston") * (Category = "Cookies") * (Количество> 40), Количество)) } |
Обяснение:
- City = "Boston": проверява стойностите в диапазона на града, за да съвпадат с "Boston".
- Категория = „Бисквитки“: проверява стойностите в диапазона от категории, за да съвпадат с „Бисквитки“.
- Количество> 40: проверява стойностите в диапазона на количеството до ma
- Количеството е масив, където се изисква сума.
- Функцията IF проверява всички критерии и звездичката char (*) умножава всички резултати от масива.
= SUM (IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Сега функцията IF връща само количествата, съответстващи на 1s, а останалите се игнорират.
- Функцията SUM връща SUM.
Сега количеството, съответстващо на 1, се добавя само за получаване на резултата.
Както можете да видите, количество 43 се връща, но има три поръчки за бисквитки, доставени до "Бостън" с количество 38, 36 и 43. Трябваше ни сума от количество, където количеството е над 40. Така че формулата връща само 43. Сега използвайте други критерии, за да получите СУММ количеството за града: „Лос Анджелис“ & Категория: „Барове“ & Количеството е по -малко от 50.
Използвайте формулата
| { = SUM (IF ((Град = "Лос Анджелис") * (Категория = "Барове") * (Количество <50), Количество)) } |
Както можете да видите, формулата връща стойностите 86 като резултат. Която е сумата от 2 поръчки, отговарящи на условията с количество 44 и 42. Тази статия илюстрира как да замените вложената формула на IF с единична IF във формула на масив. Това може да се използва за намаляване на сложността на сложните формули. Този конкретен проблем обаче може лесно да бъде решен с функцията SUMIFS или SUMPRODUCT.
Използване на функцията SUMPRODUCT:
Функцията SUMPRODUCT връща сумата от съответните стойности в масива. Така че ще накараме масивите да върнат 1s a стойностите на израза True и 0s на стойностите на False. Така че последната сума ще съответства, когато всички твърдения са верни.
Използвайте формулата:
| = SUMPRODUCT ( - (Град = "Бостън"), - (Категория = "Бисквитки"), - (Количество> 40), Количество) |
-: операция, използвана за преобразуване на всички TRUEs в 1s и False в 0.
Функцията SUMPRODUCT проверява отново SUM на количеството, върнато от функцията SUM и IF, обяснено по -горе.
По същия начин за втория пример резултатът е същият.
Както можете да видите, функцията SUMPRODUCT може да изпълнява същата задача.
Ето всички забележки от наблюденията относно използването на формулата.
Бележки:
- Sum_array във формулата работи само с числа.
- Ако формулата връща грешка #VALUE, проверете дали фигурните скоби трябва да присъстват във формулата, както е показано в примерите в статията.
- Отрицанието (-) char променя стойностите, TRUEs или 1s на FALSEs или 0s и FALSEs или 0s в TRUEs или 1s.
- Операции като равно на ( = ), по -малко от равно на ( <= ), по-голяма от ( > ) или не е равно на () може да се извърши в рамките на приложена формула, само с числа.
Надявам се, че тази статия за това как да използвате функцията IF вместо SUMPRODUCT и функцията SUMIFS в Excel е обяснима. Намерете още статии за формули за сумиране тук. Ако нашите блогове са ви харесали, споделете го с вашите първенци във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща SUM от стойности в множество колони, имащи условие в excel.
Популярни статии:
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.