Обобщаване на определени клетки в Excel. Селективно сумиране по два критерия

Формулиране на проблема

Имаме таблица за продажби, например, в следната форма:

Задача : обобщава всички поръчки, които мениджърът Григориев е изпълнил за магазин "Копейка".

Метод 1. Функция SUMIF

Ако нашата задача имаше само едно условие (всички поръчки от Григориев или всички поръчки до "Копейка"), тогава задачата щеше да бъде решена доста лесно с помощта на вградената функция на ExcelSUMIF разглеждане на математическа категория. Можете да прочетете как да го използвате. Но в нашия случай има две условия, а не едно, така че този метод не е подходящ ...

Метод 2. Колона-индикатор

Нека добавим още една колона към нашата таблица, която ще служи като вид индикатор: ако поръчката е била в "Копейка" и от Григориев, тогава клетката на тази колона ще има стойност 1, иначе 0. Формулата, която трябва да бъде в тази колона е много проста:

\u003d (A2 \u003d "Копейка") * (B2 \u003d "Григориев")

Булевите равенства в скоби дават TRUE или FALSE, което за Excel е равно на 1 и 0. Така че, тъй като умножаваме тези изрази, 1 в крайна сметка ще работи само ако и двете условия са верни. Сега цената на продажбите остава да се умножи по стойностите на получената колона и да се сумират получените суми:

Метод 3. Формула на магически масив

Ако досега не сте попадали на такава прекрасна функция на Excel като формули за масиви, съветвам ви да прочетете много хубави неща за тях предварително.... Е, в нашия случай проблемът се решава по една формула:

SUM ((A2: A26 \u003d "Пени") * (B2: B26 \u003d "Григориев") * D2: D26)

След като въведете тази формула, трябва да натиснете не Enter, както обикновено, аCtrl + Shift + Enter - тогава Excel ще го възприеме катоформула за масив и ще добавя къдравите скоби сам. Не е необходимо да въвеждате скоби от клавиатурата. Лесно е да се осъзнае, че този метод (като предишния) е лесно мащабируем с три, четири и т.н. условия без никакви ограничения.

Метод 4. Функция на базата данни BDSUMM

В категория Бази данни можете да намерите функциятаBDSUM (DSUM) , което също може да ни помогне да решим проблема си:

Нюансът е, че за да работи тази функция, трябва да създадете диапазон от клетки на листа, съдържащ условията за избор, и след това да посочите този диапазон на функцията като аргумент:

BDSUMM (A1: D26; D1; F1: G2)

Метод 5. Съветник за частична сума

Това е името на добавка за Excel, която ви помага да създавате сложни формули за многокритериално сумиране. Можете да свържете тази безплатна добавка през менютоУслуга - добавки - съветник за сумиране(Инструменти - Добавки - Съветник за условна сума)... След това в менютоОбслужване командата трябва да се появиЧастична сума за стартиране на съветника за сумиране:

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

И накрая, на 3-та и 4-та стъпка посочваме клетката, където искате да покажете резултата. И в крайна сметка получаваме следното:

Лесно е да се види, че използвахме нещо подобно на тази формула на масив в метод 3. Само тук изобщо не можете да докосвате клавиатурата - да живее мързелът - двигателят на прогреса!

Знаеше ли, какво е мисловен експеримент, експеримент с геданки?
Това е несъществуваща практика, отвъдно преживяване, въображението на онова, което не е в действителност. Мисловните експерименти са като будни сънища. Те раждат чудовища. За разлика от физическия експеримент, който е експериментален тест на хипотези, "мисловен експеримент" хитро замества експериментален тест с желаните, не тествани на практика заключения, манипулирайки логически конструкции, които всъщност нарушават самата логика, като използват недоказани предпоставки като доказани, тоест чрез заместване. По този начин основната задача на кандидатите за „мисловни експерименти“ е да заблудят слушателя или читателя, като заменят истински физически експеримент с неговата „кукла“ - фиктивни разсъждения при предсрочно освобождаване без самата физическа проверка.
Изпълването на физиката с въображаеми, „мисловни експерименти“ доведе до появата на абсурдна сюрреалистична, объркана и объркана картина на света. Истинският изследовател трябва да различава такива „обвивки“ от реалните ценности.

Релативистите и позитивистите твърдят, че „мисловният експеримент“ е много полезен инструмент за тестване на теории (също възникващи в нашия ум) за последователност. В това те заблуждават хората, тъй като всяка проверка може да се извърши само от източник, независим от обекта на проверката. Кандидатът за хипотезата не може да бъде тест на собственото си твърдение, тъй като самата причина за това твърдение е липсата на противоречия в изявлението, видими за кандидата.

Виждаме това в примера на SRT и GRT, които са се превърнали в един вид религия, която контролира науката и обществено мнение... Никакви факти, които им противоречат, не могат да преодолеят формулата на Айнщайн: „Ако даден факт не съответства на теорията, променете факта“ (В друга версия, „- Фактът не съответства на теорията? - Толкова по-лошо за факт ").

Максимумът, който може да претендира „мисловен експеримент“, е само вътрешната последователност на хипотезата в рамките на собствената, често в никакъв случай вярна логика. Това не проверява пригодността на практиката. Този тест може да се проведе само при валиден физически експеримент.

Експериментът също е експеримент, че не е усъвършенстване на мисълта, а тест на мисълта. Мисъл, която е самопоследователна в себе си, не може да се самопроверява. Това е доказано от Курт Гьодел.

ФОРУМНИ НОВИНИ Ефирни рицари

30.10.2017 - 06:17: -\u003e - Карим_Кайдаров. 19.10.2017 - 04:24: -\u003e - Карим_Кайдаров. 11.10.2017 - 05:10: -\u003e - Карим_Кайдаров. 05.10.2017 - 11:03:

Функция SUMIF се използва, когато е необходимо да се сумират стойностите на диапазона, които отговарят на посоченото условие. Да предположим например, че в колона с числа искате да добавите само стойности, по-големи от 5. За да направите това, можете да използвате следната формула: \u003d SUMIF (B2: B25, "\u003e 5")

Това видео е част курс на обучение Добавете числа в Excel 2013.

Съвет:

Синтаксис

SUMIF (диапазон, условие, [обхват_сума])

Аргументи на функцията SUMIF са описани по-долу.

Обхват ... Необходим аргумент. Обхватът на клетките за оценка на съответствието. Клетките във всеки диапазон трябва да съдържат числа, имена, масиви или препратки към числа. Празни клетки и клетки, съдържащи текстови стойности, се пропускат. Избраният диапазон може да съдържа дати в стандартен формат на Excel (вижте примерите по-долу).

Състояние ... Необходим аргумент. Условие под формата на число, израз, препратка към клетка, текст или функция, което определя кои клетки да се сумират. Например условие може да бъде представено като: 32, "\u003e 32", B5, "32", "ябълки" или ДНЕС ().

Важно: Всички текстови условия и условия с логически и математически знаци трябва да бъдат затворени в двойни кавички ( " ). Ако условието е число, не е необходимо да използвате кавички.

Sum_range ... Незадължителен аргумент. Клетки, от които се добавят стойности, ако се различават от клетките, посочени като обхват ... Ако аргументът сума_диапазон пропуснато, Excel сумира клетките, посочени в аргумента обхват (същите клетки, за които се отнася условието).

В спора състояние могат да се използват заместващи символи: въпросителен знак ( ? ) и звездичка ( * ). Въпросният знак съответства на всеки отделен знак, а звездичката съответства на всяка последователност от символи. Ако искате да намерите директно въпросителен знак (или звездичка), трябва да поставите тилда пред него ( ~ ).

Бележки

Функцията SUMIF връща неправилни резултати, ако се използва за съвпадение на низове, по-дълги от 255 знака или когато е приложена към низа #VALUE!

Аргумент сума_диапазон може да не е със същия размер като аргумента обхват ... Горната лява клетка на аргумента се използва като начална точка при определяне на действителните клетки, които трябва да бъдат добавени сума_диапазон и след това се сумират клетките на частта от диапазона, съответстваща по размер на аргумента обхват ... Пример:

Ако обаче аргументите обхват и сума_диапазон Тъй като функциите SUMIF съдържат различен брой клетки, преизчисляването на листа може да отнеме повече време от очакваното.

Примери за

Пример 1

Копирайте примерните данни от следващата таблица и ги поставете в клетка A1 на новата работен лист на Excel... За да покажете резултатите от формулите, изберете ги и натиснете F2, след което натиснете Enter. Променете ширината на колоните, ако е необходимо, за да видите всички данни.

Пример 2

Копирайте примерните данни от таблицата по-долу и ги поставете в клетка A1 на нов лист на Excel. За да покажете резултатите от формулите, изберете ги и натиснете F2, след което натиснете Enter. Освен това можете да регулирате ширината на колоните според данните, които съдържат.

Добър ден!

Ще продължа да се опитвам да опиша разнообразието от функции в Excel и следващото за наше разглеждане. Това е друг представител на функциите за сумиране, но със свои специфични условия. BDSUMM функция в Excel търси числа във вашата таблица според критериите, които сте определили, това е нейното основно свойство.

Честно казано, мога да кажа, че много изчисления и изчисления могат да бъдат направени без него с помощта на различни или SUMPRODUCT, но ако трябва да направите сложен подбор с помощта на заместващи символи, тогава определено трябва да използвате героя на нашата статия.

Първо, нека разгледаме синтаксиса, който използва:

\u003d BDSUMM (обхватът на вашата база данни; поле за търсене; термин за търсене)където

При работа с функция BDSUMM заслужава да се отбележат няколко условия, на които трябва да се обърне внимание при работа:

• Когато работите върху цяла колона, пълна с данни, не забравяйте да вмъкнете празен ред под заглавията на колоните в посочения диапазон от критерии;
• Обхватът на критериите не трябва да се припокрива със списъка;
• Въпреки че можете да поставите данните, за да формирате диапазона от условия навсякъде на листа, не трябва да ги поставяте след списъка, тъй като данните, които добавяме към списъка, ще бъдат вмъкнати в първия ред след списъка и ако има данни в реда, след което добавянето на нови няма да работи;
• Можете да използвате произволен диапазон за условия, които съдържат поне едно заглавие и поне една от клетките, което се намира под заглавието и съдържа условието.

И така, теоретичната част, която считам за завършена, нека сега да преминем към практическото приложение bDSUMM функции в нашата работа, за това ще разгледаме няколко примера за изпълнение, направих примерите по принципа, но вместо да събирам стойности по критерий, ще има сумиране:

BDSUMM функция с един цифров критерий

И така, да започнем с това, нека разгледаме един прост пример с един числов критерий, за това избираме колоната „Жътва“ и посочваме, че се нуждаем от дървета с добив „\u003e \u003d 10“. За да получим резултата, се нуждаем от формула от този вид (съветвам ви да използвате абсолютни референции):

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; E2: E3),

където, $ B $ 6: $ G $ 12 е диапазонът, в който ще обобщим, E6 е колоната, в която ще обобщим, а E2: E3 е диапазонът, в който сме въвели критериите за сумиране. В резултат на това формулата намери 3 позиции за общо 34.

За да получите подобен резултат, можете също да използвате следните формули:

\u003d SUMIF (E7: E12, "\u003e \u003d 10")

\u003d SUMIF (E7: E12, E3)

BDSUMM функция с един текстов критерий

Сега нека разгледаме как се държи функцията BDSUMM с текстови критерии, като цяло всичко остава същото в предишния пример, с изключение на това как е посочен критерият за текст и е посочен само в тази форма: \u003d "\u003d s.Serovo" и тогава ще получите резултата, в противен случай формулата няма да може да разпознае вашия критерий. Сега заместваме този критерий във формулата и получаваме:

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; C2: C3), както виждаме, само обхватът на критерия се е променил.

За да получите подобен резултат, ще ви трябва:

\u003d SUMIF (C7: C12, "s.Serovo", E7: E12)

Сумиране по два критерия в различни колони

Примерът се усложнява от прилагането на два критерия, но няма да приложим нищо принципно ново, ще посочим текстовия критерий "С. Серово" и числовия критерий "\u003e \u003d 10", оставяйки полето за сумиране "Реколта", ние ще получи промяна във формулата само за последния аргумент, в резултат ... Нашата формула сега ще изглежда така:

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; C2: E3), отново виждате промени само в адреса на диапазона от критерии.

Алтернативна опция може да се получи, като се използва и подобно на това:

\u003d SUMIF (E7: E12; C7: C12; C3; E7: E12; E3)

\u003d SUMIF (E7: E12; C7: C12; "с.Серово"; E7: E12; "\u003e \u003d 10")

Сумиране по едно от двете условия в една колона

Нека разгледаме друга опция като приложена, но сега ще използваме не един критерий, а двоен, но за едно поле. Два критерия трябва да са на различни линии. Същността на формулата се свежда до факта, че тя преминава през един и същ диапазон два пъти, като брои всеки от критериите поотделно. За този пример формулата ще изглежда така:

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; C2: C4), тук отново променяме обхвата на критерия, но не по ширина, а по височина.

Също като заместител можете да използвате сумата от функцията SUMIF:

\u003d SUMIF (C7: C12; C3; E7: E12) + SUMIF (C7: C12; C4; E7: E12).

Сумиране по едно от двете условия в две различни колони

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

\u003d BDSUMM ($ B $ 6: $ G $ 12; D6; C2: D4), принципът за формиране на формулата се запазва, с изключение на обхвата от критерии, който включва три реда: заглавие и два критерия.

Сумиране по два текстови критерия по две колони

В този пример за работа ще разгледаме почти пълен аналог на разглеждания по-рано пример, когато в две колони имаше два критерия, но имаше числов и текстов критерий, но тук ще разгледаме сумирането по два текстови критерия и от две колони. Използваме критериите "\u003d" \u003d s.Ivanovo "" и "\u003d" \u003d Cherry "", които ще посочим в диапазона от критерии. Така че нашата формула ще изглежда така:

\u003d BDSUMM ($ B $ 6: $ G $ 12; D6; B2: C3).

Използване на резултата от формула за получаване на критерий за подбор и сумиране

В този пример за функцията BDSUMM използвам формула за определяне на критерия за подбор и сумиране, в този случай ще открием кои дървета ни носят доход и за определяне на условния аргумент вземаме средната стойност за продажбите на плодове от дървета и всичко над средното за нас представлява интерес. За да определим средната стойност, която ще се превърне в наш ИСТИНСКИ критерий, ние създаваме формулата:

\u003d G7\u003e СРЕДЕН ($ G $ 7: $ G $ 12), не забравяйте да коригирате диапазона, така че когато формулата премине през стойностите, те да не се плъзгат надолу, но стойността G7 трябва да се плъзга по целия диапазон, за да се определи дали е „FALSE“ или „TRUE“.

Също така е много важно заглавията на заглавията да не се дублират, да имат разлика, затова ще кръстя полето за критерии „Средно“. И тогава формулата ще започне да работи, тя ще се повтори в целия диапазон от $ G $ 7: $ G $ 12 за наличието на средна стойност и когато положителен резултат е "TRUE", тя ще се сумира. Следната формула ще се справи с тази работа:

\u003d BDSUMM ($ B $ 6: $ G $ 12; G6; $ G $ 2: $ G $ 3)

И ако много се интересувате от алтернативно решение на въпроса, опитайте опцията с функцията SUMIF в тази форма:

\u003d SUMIF ($ G $ 7: $ G $ 12; "\u003e" & СРЕДНА ($ G $ 7: $ G $ 12))

BDSUMM функция според три критерия

В този пример нека изчислим средните продажби на плодове, отглеждани в две села: „село Иваново“ и „село Уютное“. Вече описах основната идея за подбор по критерии, така че няма да се повтарям, просто ще кажа, че това ще бъде комбинация от разгледаните по-рано критерии. За да получим резултата, той ни трябва в тази форма:

\u003d BDSUMM ($ B $ 6: $ G $ 12; G6; $ C $ 2: $ G $ 4)

Сумиране по текстов критерий, чувствителен към малки и големи букви

Както споменах по-рано, той може да търси не само с заместващи символи, но и с чувствителност към малки и големи букви, сега това е точно опцията. Като начало дефинираме условието за избор на критерий, ако се срещне името "s. IVANOVO", с главни букви, тогава извършваме сумирането, за да определим този критерий ни е необходима формулата:

\u003d СЪВЕТ ("С. ИВАНОВО"; C7)

Но сега можем да напишем функция, която ще провери обхвата за наличието на посочения критерий и, когато се получи стойността "TRUE", ще извърши сумирането. В примера специално посочих веднъж според условието и както виждате, формулата успешно избра всички селища и намери желаното и получи резултата "9". За това беше използвана формулата:

\u003d BDSUMM ($ B $ 6: $ G $ 12; D6; $ C $ 2: $ C $ 3)

Е, мисля, че описах подробностите в много подробности, така че ще има малко въпроси, но много ползи. Ако имате някакви въпроси, пишете коментари, очаквам с нетърпение вашите харесвания и отзиви. Можете да научите за други функции на моя уебсайт.

Успех за вас във вашия бизнес!

„Парите, разбира се, могат да се купят очарователно куче, но никакви пари не могат да го накарат да маха с радост.
"
Г. Билингс

Препоръчваме да прочетете

Офис програми

Календар на значими и запомнящи се дати в Русия, обявена за Година на екологията

Смартфони

Как да решаваме примери с дроби

Таблетки

Афоризми и цитати за религията

Офис програми

Какво е унция и как се превръща унцията в грамове?

Офис програми

Кратък тон Кратък тон до метричен тон

Таблетки

Какво трябва да яде жената, за да увеличи бюста си