Един от често срещаните въпроси в Board Board е как да използвате функцията SumIf с две различни условия. За съжаление отговорът е, че SumIf не може да се справи с две различни условия.
За да направите две условия, трябва да използвате доста сложна формула на масива. Добавката на съветника за условна сума ви позволява да въвеждате тези сложни формули с лекота.
Ето работен лист на Excel с колони за продукти, търговски представител и продажби. Данните са в клетки A2: C29.
Ако искате да обобщите продажбите, ще работи проста функция SUM (). =SUM(C2:C29).
Много отличници откриват функцията SumIf. Използвайки тази функция, е доста лесно да се разбере общата сума на продажбите на продукта ABC.=SUMIF(A2:A29,E2,C2:C29)
Също така е лесно да се разбере общият обем на продажбите, направени от търговския представител Джо =SUMIF(B2:B29,E2,C2:C29).
След това бихте предположили, че е възможно да разберете общите продажби на продукт ABC, направени от Джо. Обаче няма начин да направите това с функцията SumIf. Оказва се, че трябва да използвате доста сложен масив или формула на CSE.
Нека си признаем - формулата Sum е Excel 101. Формулата SumIf не изостава по сложност. Формулата на CSE за изчисляване на общия брой продажби на ABC, извършени от Джо, е достатъчна, за да се върти дори главата ми.
Добрата новина - Microsoft предлага съветника за условна сума, който позволява дори на начинаещ да въведе сложни условни формули въз основа на 1, 2 или повече условия. Съветникът за условна сума е добавка. За да добавите тази функционалност към Excel, отидете в менюто Инструменти и изберете Добавки. В диалоговия прозорец Добавки поставете отметка в квадратчето до Съветника за условна сума и изберете ОК. Възможно е в този момент да ви е необходим инсталационният компактдиск, защото Microsoft не включва съветника в инсталацията по подразбиране.
След като добавката бъде успешно включена, ще има избор на Conditonal Sum … в долната част на менюто Tools.
Изберете една клетка в набора от данни и изберете Инструменти - условна сума. Ако приемем, че вашите данни са добре форматирани с един ред заглавия, Excel правилно ще отгатне обхвата на вашите данни. Изберете Напред.
В стъпка 2 изберете колоната за сумиране. В този случай съветникът вече е предположил, че искате да сумирате първата (и единствена) числова колона - Продажби. В средата на диалоговия прозорец има три падащи контроли. Това се оказва правилно за първото условие - продуктът е равен на ABC, така че изберете бутона Добавяне на условие.
След това можете да добавите второто си условие. В този случай искате да посочите, че търговският представител е Джо. Изберете стрелката за първото падащо меню. Excel предлага азбучен списък с наличните имена на колони. Изберете търговски представител
Падащото меню в центъра е правилно, но за пълнота тук можете да видите, че бихте могли да изберете равно, по-малко от, по-голямо от, по-малко или равно, по-голямо или равно, или не равно.
От третото падащо меню изберете Джо.
Изберете бутона Добавяне на условие.
Вече сте готови да преминете към Стъпка 3. Натиснете бутона Напред.
В стъпка 3 имате два избора. При първия избор съветникът ще въведе една формула със стойности „ABC“ и „Joe“, кодирани твърдо във формулата. Той ще ви даде отговор, но няма да има възможност лесно да промените формулата. С втория избор Excel ще настрои нова клетка със стойността "ABC" и нова клетка със стойността "Джо". Трета клетка ще съдържа формулата, която прави условна сума въз основа на тези две стойности. С тази опция можете да въведете нови стойности в клетките, за да видите общите XYZ, продадени от Адам.
След това съветникът ще попита къде искате стойността за ABC. Изберете клетка и изберете Напред. Повторете, докато съветникът ви помоли да изберете клетка за Джо и формулата.
Когато изберете Finish в последната стъпка, Excel ще създаде малко по-различна (но валидна) версия на формулата на CSE.
Тази формула изчислява, че Джо е продал $ 33,338 от ABC.
Ако промените клетката за въвеждане на продукт от ABC на DEF, формулата ще преизчисли, за да покаже, че Джо е продал $ 24 478 от DEF.
Съветникът за условна сума поставя сложни формули в обсега на всички собственици на Excel.
Допълнителна информация:Ако искате да създадете таблица, която да показва продажбите на всеки продукт от всеки търговски представител, има някои специални „грижи и хранене“, които ще трябва да знаете за тези формули. Въведете всеки търговски представител в горната част на диапазона. Въведете всеки продукт надолу в лявата колона на диапазона. Редактирайте формулата, предоставена от съветника. На изображението по-долу формулата сочи продукта в клетка E6. Тази справка наистина трябва да бъде $ E6. Ако оставите препратката като E6 и копирате формулата в колона G, формулата ще изглежда F6 вместо E6 и това би било погрешно. Добавянето на знак за долар преди E в E6 ще гарантира, че формулата винаги гледа продукта в колона E. Формулата също сочи към търговски представител в клетка F5. Тази справка наистина трябва да бъде F $ 5. Ако сте оставили референцията като F5 и копирате до ред 7,референцията за F5 ще се промени на F6 и това не е правилно. Добавянето на знак за долар преди номера на реда ще заключи номера на реда и препратката винаги ще сочи към ред 5.
В режим Редактиране (изберете клетката и натиснете F2, за да редактирате), въведете $ преди E. Въведете знак за долар преди 5 във F5. Не натискайте Enter още!
Тази формула е специален тип формула. Ако натиснете Enter, ще получите 0, което не е правилно.
Вместо да пишете Enter, задръжте клавишите Ctrl и Shift, докато натискате Enter. Тази магическа комбинация от C trl + S hift + E nter е причината, поради която наричам тези формули на CSE.
Има едно последно съображение преди копирането на формулата в останалата част на таблицата. Вашата склонност може да бъде да копирате F6 и да го поставите във F6: G8. Ако опитате това, Excel ще ви даде озадачаващото съобщение „Не можете да промените част от масив“. Excel се оплаква, че не можете да поставите формула за CSE в диапазон, който съдържа оригиналната формула за CSE.
Лесно е да се заобиколи това. Копирайте F6. Поставете във F7: F8.
Копие F6: F8. Поставете към G6: G8. Ще имате таблица на формулите за ПИ, показващи суми въз основа на две условия.
