Функцыя СУММЕСЛИ, а так жа СУММЕСЛИ па двух крытэрыях

1. Пошук па пазнаках

Уявім сабе табліцу, у якой у радках ўперамешку пазначаны назвы аддзелаў (або рахункаў, ці яшчэ чаго-то).

Сумуецца ячэйкі па крытэры
Неабходна вылічыць агульную суму па кожнаму аддзелу. Многія робяць гэта пры дапамозе фільтра і запісы ручкамі ў вочкі.
Хоць зрабіць гэта можна лёгка і проста пры дапамозе ўсяго адной функцыі - СУММЕСЛИ.
СУММЕСЛИ (SUMIF) - сумуюцца ячэйкі, якія задавальняюць зададзеным умове (ўмова можна задаць толькі адно). Гэтую функцыю так жа можна ўжыць, калі табліца пабіта ў слупках на перыяды (памесячна, у кожным месяцы па тры слупкі - Прыбытак | Выдатак | Розніца) і неабходна падлічыць агульную суму за ўсе перыяды толькі па даходаў, расходаў і розніца.

Усяго для СУММЕСЛИ прадугледжана тры аргументу: Дыяпазон, Крытэрый, Диапазон_Суммирования.
= СУММЕСЛИ (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Дыяпазон (A1: A20000) - указваецца дыяпазон з крытэрамі. Г.зн. слупок, у якім шукаць значэнне, азначанае аргументам Крытэрый.
• Крытэрый (A1) - значэнне (тэкставае або лікавае, а так жа дата), якое неабходна знайсці ў дыяпазоне. Можа ўтрымліваць сымбалі падстаноўкі "*" і "?". Г.зн. паказаўшы ў якасці крытэрыяў "* маса *" будуць прасумаваныя значэння, у якіх сустракаецца слова "маса". Пры гэтым слова "маса" можа альбо сустракацца ў любым месцы тэксту, альбо ў вочку можа быць толькі адно гэтае слова. А пазначыўшы "маса *", будуць прасумаваныя ўсе значэнні, якія пачынаюцца на "маса". "?" - замяняе толькі адзін знак, г.зн. пазначыўшы "мас? а" вы зможаце прасумаваць радкі і са значэннем "маса" і са значэннем "маска" і г.д.
  Калі крытэрый запісаны ў вочку і трэба ўсё ж выкарыстоўваць падстаноўныя сімвалы, то можна зрабіць спасылку на гэтую вочка дадаўшы патрэбнае. Дапусцім, трэба прасумаваць значэння, якія змяшчаюць слова "вынік". Слова "вынік" запісана ў вочку A1, ў Стоўбцах A пры гэтым могуць сустракацца розныя па напісанні значэння, якія змяшчаюць слова "вынік": "вынікі за чэрвень", "вынікі за ліпень", "вынікі за сакавік". Формула тады павінна выглядаць так:
  = СУММЕСЛИ (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - знак & (Ампэрсанд) аб'ядноўвае некалькі значэнняў у адно. Г.зн. у выніку атрымаецца "* вынік *".
  Каб лепш зразумець прынцып працы формул лепш выкарыстоўваць інструмент Вылічыць формулу: Як праглядзець этапы вылічэнні формул
  Усе тэкставыя крытэры і крытэры з лагічнымі і матэматычнымі знакамі неабходна заключаць ў падвойныя двукоссі (= СУММЕСЛИ (A1: A20000; "вынік"; B1: B20000)). Калі крытэрыем з'яўляецца лік, выкарыстоўваць двукоссі не патрабуецца. Калі патрабуецца знайсці непасрэдна пытальнік або зорачку, неабходна паставіць перад ім знак "тыльды" (~).
  Пра Тыльда і яе адметнасці можна даведацца ў гэтым артыкуле: Як замяніць / выдаліць / знайсці зорачку?
• Диапазон_Суммирования (B1: B20000) (неабавязковы аргумент) - указваецца дыяпазон сум або лікавых значэнняў, якія неабходна прасумаваць.

Як гэта працуе: функцыя шукае ў дыяпазоне значэнне, азначанае аргументам Крытэрый, і пры знаходжанні супадзення сумуе дадзеныя, названыя аргументам Диапазон_Суммирования. Г.зн. калі ў нас у слупку А назва аддзела, а ў Стоўбцах У сумы, то паказаўшы ў якасці крытэрыю "Аддзел развіцця" вынікам функцыі будзе сума ўсіх значэнняў слупка У, насупраць якіх ў Стоўбцах А сустракаецца "Аддзел развіцця". Фактычна Диапазон_Суммирования можа не супадаць па памеры з аргументам Дыяпазон і памылкі самой функцыі гэта не выкліча. Аднак пры вызначэнні вочак для падсумоўвання, у якасці пачатковай вочкі для падсумоўвання будзе выкарыстаная верхняя левая ячэйка аргументу Диапазон_Суммирования, а затым сумуюцца ячэйкі, якія адпавядаюць па памеры і форме аргументу Дыяпазон.

некаторыя асаблівасці
Апошні аргумент функцыі (Диапазон_Суммирования - B1: B20000) з'яўляецца неабавязковым. А гэта значыць, што яго можна не паказваць. Калі яго не паказаць, то функцыя просуммирует значэння, названыя аргументам Дыяпазон. Для чаго гэта трэба. Напрыклад, Вам неабходна атрымаць суму толькі тых лікаў, якія больш за нуль. У слупку А сумы. Тады функцыя будзе мець такі выгляд:
= СУММЕСЛИ (A1: A20000; "> 0")

Што варта варта ўлічваць: диапазон_суммирования і дыяпазон павінны быць роўныя па колькасці радкоў. Інакш можна атрымаць няправільны вынік. Аптымальна, калі гэта будзе выглядаць як у прыведзеных мной формулах: дыяпазон і диапазон_суммирования пачынаюцца з аднаго радка і маюць аднолькавую колькасць радкоў: A1: A20000; B1: B20000

Сумаванне па двух i больш крытэрам
Але што рабіць, калі крытэрыяў для падсумоўвання 2 і больш? Дапусцім, Вам трэба прасумаваць толькі тыя сумы, якія адносяцца да аднаго аддзелу і толькі за пэўную дату. Шчаслівыя ўладальнікі версій офіса 2007 года і вышэй могуць скарыстацца функцыяй СУММЕСЛИМН:
= СУММЕСЛИМН ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - диапазон_суммирования. Першым аргументаў паказваецца дыяпазон вочак, якія змяшчаюць сумы, якія і будуць збірацца ў адну.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Диапазон_критерия. Паказваецца дыяпазон вочак, у якіх неабходна шукаць супадзенне па крытэры.
$ I $ 3, $ H8 - крытэр. Тут, як і ў СУММЕСЛИ, дапускаецца ўказанне сімвалаў падстаноўкі * і? і працуюць яны гэтак жа.

Асаблівасць ўказанні аргументаў: спачатку паказваецца дыяпазон крытэрыю (яны пранумараваны) затым праз кропку-з-коскі паказваецца непасрэдна значэнне (крытэрый), якое ў гэтым дыяпазоне неабходна знайсці - $ A $ 2: $ A $ 50; $ I $ 3. І ніяк інакш. Не варта спрабаваць спачатку паказаць усе дыяпазоны, а потым крытэрыі да іх - функцыя выдасць альбо памылку, альбо просуммирует не тое, што трэба.

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

Бо СУММЕСЛИМН з'явілася толькі ў версіях Excel, пачынаючы з 2007, то як жа быць у такіх выпадках няшчасным карыстачам больш ранніх версій? Вельмі проста: выкарыстоўваць іншую функцыю - СУММПРОИЗВ. Не буду распісваць аргументы, бо іх шмат і ўсе яны з'яўляюцца масівамі значэнняў. Дадзеная функцыя перамнажаюцца масівы, названыя аргументамі. Я паспрабую апісаць агульны прынцып выкарыстання гэтай функцыі для падсумоўвання дадзеных па некалькіх умовам.
Для вырашэння задачы падсумоўвання па некалькіх крытэрах функцыя будзе выглядаць так:
= СУММПРОИЗВ (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - дыяпазон дат. $ I $ 3 - дата крытэра, за якую неабходна прасумаваць дадзеныя.
$ B $ 2: $ B $ 50 - найменні аддзелаў. H5 - найменне аддзела, дадзеныя па якім неабходна прасумаваць.
$ C $ 2: $ C $ 50 - дыяпазон з сумамі.

Разбяром логіку, бо многім яна будзе зусім не зразумелая проста пры поглядзе на дадзеную функцыю. Хоць бы таму, што ў даведцы падобнае яе прымяненне не апісваецца. Для большай чытэльнасці паменшым памеры дыяпазонаў:
= СУММПРОИЗВ (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Такім чынам, выраз ($ A $ 2: $ A $ 5 = $ I $ 3) і ($ B $ 2: $ B $ 5 = H5) з'яўляюцца лагічнымі і вяртаюць масівы лагічных ХЛУСНЯ і ПРАЎДА. ПРАЎДА, калі вочка дыяпазону $ A $ 2: $ A $ 5 роўная значэнню ячэйкі $ I $ 3 і вочка дыяпазону $ B $ 2: $ B $ 5 роўная значэнню ячэйкі H5. Г.зн. атрымліваецца ў нас наступнае:
= СУММПРОИЗВ ({ХЛУСНЯ; ПРАЎДА; ПРАЎДА; ХЛУСНЯ} * {ХЛУСНЯ; ХЛУСНЯ; ПРАЎДА; ХЛУСНЯ}; $ C $ 2: $ C $ 50)
Як бачна, у першым масіве два супадзення умове, а ў другім адно. Далей гэтыя два масіва перамнажаюцца (за гэта адказвае знак множання (*)). Пры перамнажэннем адбываецца невідавочнае пераўтварэнне масіваў ХЛУСНЯ і ПРАЎДА ў лікавыя канстанты 0 і 1 адпаведна ({0; 1; 1; 0} * {0; 0; 1; 0}). Як вядома, пры памнажэньні на нуль атрымліваем нуль. І ў выніку атрымліваецца адзін масіў:
= СУММПРОИЗВ ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Далей адбываецца ўжо перамнажэннем масіва {0; 0; 1; 0} на масіў лікаў у дыяпазоне $ C $ 2: $ C $ 50:
= СУММПРОИЗВ ({0; 0; 1; 0}; {10; 20; 30; 40})
І як вынік атрымліваем 30. Што нам і трэба было - мы атрымліваем толькі тую суму, якая адпавядае крытэру. Калі сум, якія задавальняюць крытэру будзе больш, як адна, то яны будуць прасумаваныя.

перавага СУММИРОИЗВ
Калі ў аргументаў замест знака множання паказаць знак плюс:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
то ўмовы будуць параўноўвацца па прынцыпе АБО: г.зн. падсумоўвацца выніковыя сумы будуць у выпадку, калі хаця б адна ўмова выконваецца: ці $ A $ 2: $ A $ 5 роўная значэнню ячэйкі $ I $ 3 ці вочка дыяпазону $ B $ 2: $ B $ 5 роўная значэнню ячэйкі H5.
У гэтым перавага СУММПРОИЗВ перад СУММЕСЛИМН. СУММЕСЛИМН не можа падсумаваць значэння па прынцыпе АБО, толькі па прынцыпе І (усе ўмовы павінны выконвацца).

недахопы
У СУММПРОИЗВ немагчыма выкарыстаць знакі падстаноўкі * і?. Дакладней выкарыстоўваць можна, але яны будуць успрынятыя не як спец.символы, а як непасрэдна зорачка і вопр.знак. Я лічу гэта істотным мінусам. І хоць гэта можна абыйсці, выкарыстоўваю ўнутры СУММПРОИЗВ іншыя функцыі - усё ж было б вельмі добра, калі б функцыя нейкім чынам магла выкарыстаць знакі падстаноўкі.

У прыкладзе знойдзеце пару прыкладаў функцый для больш лепшага разумення напісанага вышэй.

Спампаваць прыклад

Сума па некалькіх крытэрыях (41,5 KiB 10 477 запамповак)

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

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук па пазнаках