Функцыя SUMIF у Excel і сума па некалькіх умовах

Excel - гэта неверагодна функцыянальная праграма. Нават убудаванага набору функцый дастаткова для выканання практычна любой задачы. І акрамя стандартных, знаёмых многім, ёсць і такія, пра якія мала хто чуў. Але пры гэтым яны не перастаюць быць карыснымі. Яны маюць больш вузкую спецыялізацыю, і не заўсёды ў іх ёсць неабходнасць. Але калі ведаць пра іх, то ў адказны момант яны могуць вельмі спатрэбіцца.

Сёння мы пагаворым аб адной з такіх функцый − САММЕСЛІМН.

Калі перад карыстальнікам стаіць задача сумаваць некалькі значэнняў, арыентуючыся на пэўныя крытэрыі, то неабходна выкарыстоўваць функцыю САММЕСЛІМН. Формула, якая выкарыстоўвае гэтую функцыю, прымае гэтыя ўмовы ў якасці аргументаў, затым сумуе значэння, якія ім адпавядаюць, а затым знойдзенае значэнне ўводзіцца ў вочка, у якой яно запісана. 

Падрабязнае апісанне функцыі SUMIFS

Перш чым разглядаць функцыю САММЕСЛІМН, вы павінны спачатку зразумець, што такое больш простая версія - СУМЭСЛІ, так як менавіта на ім грунтуецца разгляданая намі функцыя. Напэўна, кожны з нас ужо знаёмы з двума часта выкарыстоўваюцца функцыямі - SUM (ажыццяўляе сумаванне значэнняў) і КАЛІ (правярае значэнне на адпаведнасць зададзеным умовам).

Калі вы аб'яднаеце іх, вы атрымаеце іншую функцыю - СУМЭСЛІ, які правярае даныя на адпаведнасць зададзеным карыстальнікам крытэрам і сумуе толькі тыя лічбы, якія адпавядаюць гэтым крытэрыям. Калі казаць пра англамоўнай версіі Excel, то гэтая функцыя называецца SUMIF. Кажучы простымі словамі, -моўная назва - гэта прамы пераклад англамоўнай. Гэтую функцыю можна выкарыстоўваць у розных мэтах. У прыватнасці, яго можна выкарыстоўваць у якасці альтэрнатывы ВПР, гэта значыць запісаць

Асноўнае адрозненне функ САММЕСЛІМН  ад звычайнай функцыі СУМЭСЛІ заключаецца ў тым, што выкарыстоўваецца некалькі крытэрыяў. Яе сінтаксіс на першы погляд даволі складаны, але пры бліжэйшым разглядзе аказваецца, што логіка гэтай функцыі вельмі простая. Спачатку трэба выбраць дыяпазон, дзе будуць правярацца дадзеныя, а затым задаць ўмовы, на адпаведнасць якім будзе праводзіцца аналіз. І такая аперацыя можа праводзіцца пры даволі вялікай колькасці станаў.

Сам сінтаксіс:

СУМЫ (дыяпазон_сумы, дыяпазон_умоў1, умова1, [дыяпазон_умоў2, умова2], …)

У падыходных месцах неабходна расставіць масівы вочак, прыдатныя ў канкрэтным выпадку. 

Разгледзім аргументы падрабязней:

1. Дыяпазон_сумы. Гэты аргумент, а таксама дыяпазон умовы 1 і ўмовы 1 з'яўляюцца абавязковымі. Гэта набор вочак, якія неабходна сумаваць.
2. Дыяпазон_умоў1. Гэта дыяпазон, у якім будзе правярацца ўмова. Ён спалучаецца з наступным аргументам - Condition1. Сумаванне значэнняў, адпаведных крытэру, ажыццяўляецца ў межах вочак, указаных у папярэднім аргументе.
3. Умова1. Гэты аргумент вызначае крытэрыі для праверкі. Яе можна ўсталяваць, напрыклад, такім чынам: «> 32».
4. Дыяпазон умоў 2, Умова 2… Тут такім жа чынам задаюцца наступныя ўмовы. Калі трэба ўказаць больш за некалькі ўмоў, дадаюцца аргументы "Дыяпазон умоў 3" і "Умова 3". Сінтаксіс аднолькавы для наступных аргументаў.

Функцыя дазваляе максімальна апрацоўваць да 127 пар умоў і дыяпазонаў. 

Вы можаце выкарыстоўваць яго адразу ў некалькіх сферах (мы прывядзём толькі некаторыя, спіс насамрэч яшчэ большы):

1. Бухгалтарскі ўлік. Напрыклад, добра выкарыстоўваць функцыю САММЕСЛІМН ствараць зводныя справаздачы па кварталах, напрыклад, для выдаткаў звыш пэўнай сумы. Або стварыць справаздачу па адным тавары з пэўнай цэнавай катэгорыі.
2. Кіраванне продажамі. Тут таксама функцыя можа быць вельмі карыснай. Напрыклад, перад намі стаіць задача падсумаваць толькі кошт тавараў, якія былі прададзены пэўнаму пакупніку ў пэўны час. І ў такой сітуацыі функцыя САММЕСЛІМН можа быць вельмі карысным.
3. адукацыя. Больш практычныя прыклады з гэтай вобласці мы сёння прывядзем. У прыватнасці, вы можаце выкарыстоўваць яго, каб атрымаць зводку адзнак студэнтаў. Вы можаце выбраць для аднаго прадмета або для асобных адзнак. Чалавек можа задаць адразу некалькі крытэрыяў, па якіх будзе выбірацца ацэнка, што вельмі зручна і дазваляе значна зэканоміць час.

Як бачыце, спектр прымянення гэтай функцыі вельмі шырокі. Але гэта не адзіная яго заслуга. Давайце разгледзім яшчэ некалькі пераваг гэтай функцыі:

1. Магчымасць задаць некалькі крытэрыяў. Чаму гэта перавага? Вы можаце выкарыстоўваць звычайную функцыю СУМЭСЛІ! А ўсё таму, што гэта зручна. Няма неабходнасці праводзіць асобныя разлікі па кожным з крытэраў. Усе дзеянні можна запраграмаваць загадзя, нават раней. як будзе сфарміравана табліца дадзеных. Гэта выдатная эканомія часу.
2. Аўтаматызацыя. Сучасны век - гэта век аўтаматызацыі. Шмат зарабіць можа толькі той чалавек, які ўмее правільна аўтаматызаваць сваю працу. Вось чаму здольнасць асвоіць Excel і функцыя САММЕСЛІМН у прыватнасці, так важна для любога чалавека, які хоча пабудаваць кар'еру. Веданне адной функцыі дазваляе выконваць некалькі дзеянняў адначасова, як адно. І тут мы пяройдзем да наступнага перавагі гэтай функцыі.
3. Эканомія часу. Проста за кошт таго, што адна функцыя выконвае адразу некалькі задач.
4. Прастата. Нягледзячы на ​​​​тое, што сінтаксіс на першы погляд даволі цяжкі з-за сваёй грувасткасці, на самай справе логіка гэтай функцыі вельмі простая. Спачатку выбіраецца дыяпазон даных, затым дыяпазон значэнняў, якія будуць правярацца на адпаведнасць пэўнай умове. І, вядома, само ўмова таксама павінна быць указана. І так некалькі разоў. Па сутнасці, гэтая функцыя заснавана толькі на адной лагічнай канструкцыі, што робіць яе прасцей, чым добра вядомая ВПР нягледзячы на ​​тое, што яго можна выкарыстоўваць у тых жа мэтах, таксама з улікам большай колькасці крытэраў. 

Асаблівасці выкарыстання функцыі СУМІФС

Ёсць некалькі асаблівасцяў выкарыстання гэтай функцыі, на якія неабходна звярнуць увагу. Перш за ўсё, гэтая функцыя ігнаруе дыяпазоны з тэкставымі радкамі або нулямі, паколькі гэтыя тыпы даных не могуць быць складзеныя разам у арыфметычны шаблон, толькі аб'яднаны, як радкі. Гэтая функцыя не можа гэтага зрабіць. Таксама трэба звярнуць увагу на наступныя ўмовы:

1. Вы можаце выкарыстоўваць гэтыя тыпы значэнняў у якасці ўмоў для выбару вочак для далейшага дадання значэнняў, якія змяшчаюцца ў іх: лікавыя значэнні, лагічныя выразы, спасылкі на вочкі і гэтак далей. 
2. Калі правяраецца тэкст, лагічныя выразы або матэматычныя знакі, то такія крытэрыі ўказваюцца ў двукоссе.
3. Нельга выкарыстоўваць тэрміны даўжэйшыя за 255 знакаў.
4. Магчыма выкарыстанне прыблізных крытэрыяў выбару значэнняў з дапамогай падстаноўных знакаў. Пытальнік выкарыстоўваецца для замены аднаго сімвала, а знак множання (зорачка) неабходны для замены некалькіх сімвалаў. 
5. Лагічныя значэнні, якія знаходзяцца ў дыяпазоне сумавання, аўтаматычна пераўтворацца ў лікавыя значэнні ў адпаведнасці з іх тыпам. Такім чынам, значэнне “TRUE” ператвараецца ў адзінку, а “FALSE” – у нуль. 
6. Калі #VALUE! памылка з'яўляецца ў ячэйцы, гэта азначае, што колькасць вочак у дыяпазоне ўмоў і сумавання адрозніваецца. Вы павінны пераканацца, што памеры гэтых аргументаў аднолькавыя. 

Прыклады выкарыстання функцыі SUMIFS

функцыя САММЕСЛІМН не так складана, як здаецца на першы погляд, аказваецца. Але для большай яснасці давайце разгледзім некалькі практычных прыкладаў выкарыстання гэтай функцыі САММЕСЛІМН. Гэта значна палегчыць паглыбленне ў тэму.

Дынамічны дыяпазон падсумавання ўмоў

Такім чынам, пачнем з першага прыкладу. Дапусцім, у нас ёсць табліца, якая змяшчае звесткі аб тым, як вучні спраўляюцца з вучэбнай праграмай па тым ці іншым прадмеце. Ёсць набор адзнак, паспяховасць ацэньваецца па 10-бальнай сістэме. Задача - знайсці адзнаку за экзамен тым студэнтам, прозвішча якіх пачынаецца на літару А, а мінімальны бал - 5.

Табліца выглядае так.

Для таго, каб вылічыць агульны бал на аснове апісаных вышэй крытэрыяў, нам трэба ўжыць наступную формулу.

Апішам аргументы больш падрабязна:

1. C3:C14 - гэта дыяпазон сумавання. У нашым выпадку ён супадае з дыяпазонам умоў. З яго будуць выбраны балы, якія выкарыстоўваюцца для разліку сумы, але толькі тыя, якія падпадаюць пад нашы крытэрыі.
2. «>5» - наша першая ўмова.
3. B3:B14 - гэта другі дыяпазон сумавання, які апрацоўваецца ў адпаведнасці з другім крытэрыем. Мы бачым, што няма супадзення з дыяпазонам сумавання. З гэтага мы робім выснову, што дыяпазон падсумоўвання і дыяпазон умовы могуць супадаць, а могуць і не супадаць. 
4. “А*” – другі дыяпазон, які вызначае выстаўленне адзнак толькі тым студэнтам, прозвішча якіх пачынаецца на А. У нашым выпадку зорачка азначае любую колькасць знакаў. 

Пасля разлікаў атрымліваем наступную табліцу.

Як бачыце, формула сумавала значэння на аснове дынамічнага дыяпазону і на аснове ўмоў, зададзеных карыстальнікам.

Выбарачнае сумаванне па ўмове ў Excel

Цяпер выкажам здагадку, што мы хочам атрымаць інфармацыю аб тым, якія тавары былі адпраўлены ў якія краіны на працягу апошняга квартала. Пасля гэтага знайсці агульную выручку ад адгрузкі за ліпень і жнівень.

Сама табліца выглядае так. 

Для вызначэння канчатковага выніку нам спатрэбіцца такая формула.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

У выніку разлікаў, праведзеных па гэтай формуле, атрымліваем наступны вынік.

Увага! Гэтая формула выглядае даволі вялікай, хаця мы выкарыстоўвалі толькі два крытэрыі. Калі дыяпазон даных аднолькавы, вы можаце значна паменшыць даўжыню формулы, як паказана ніжэй.

Функцыя SUMIFS для сумавання значэнняў па некалькіх умовах

Зараз прывядзём яшчэ адзін прыклад для ілюстрацыі. У гэтым выпадку табліца застаецца такой жа, як і ў папярэднім выпадку. 

Мы выкарыстоўваем наступную формулу (але запісваем яе ў выглядзе формулы масіва, гэта значыць ўводзім праз камбінацыю клавіш CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Пасля функц САММЕСЛІМН прасумуе масіў значэнняў па крытэрыях, указаным у формуле (гэта значыць краіны Кітай і Грузія), атрыманы масіў сумуецца звычайнай функцыяй СУМ, які запісваецца ў выглядзе формулы масіва.

Калі ўмовы былі перададзены як канстанта масіва для больш чым адной пары, то формула дасць няправільны вынік.

Зараз давайце паглядзім на табліцу, якая змяшчае вынікі.

Як бачыце, у нас усё атрымалася. У вас таксама ўсё абавязкова атрымаецца. Вялікі поспех на гэтай ніве. Гэта вельмі простая функцыя, якую можа зразумець чалавек, які толькі ступіў на шлях вывучэння Excel. І мы ўжо ведаем, што функцыя САММЕСЛІМН дазваляе быць эфектыўным у любой сферы дзейнасці, пачынаючы ад бухгалтэрыі і заканчваючы нават адукацыяй. Нават калі вы будуеце кар'еру ў якой-небудзь іншай сферы, якая не была апісана вышэй, гэтая функцыя ўсё роўна дапаможа вам зарабіць грошы. Гэтым яна і каштоўная.

Самае галоўнае, гэта дазваляе зэканоміць час, які, на жаль, з'яўляецца абмежаваным рэсурсам. Здавалася б, на прымяненне двух функцый ёсць пара секунд, але калі трэба выканаць велізарную колькасць паўтаральных аперацый, то гэтыя секунды складаюцца ў гадзіны, якія можна было б выдаткаваць на нешта іншае. Таму мы рэкамендуем вам патрэніравацца ў выкарыстанні гэтай функцыі. Больш за тое, гэта неверагодна проста.