1. Пребарување по ознаки

Замислете табела во која имињата на одделенијата (или сметки или нешто друго) се наведени во редови по ред.

Ги сумира клетките според критериум
Неопходно е да се пресмета вкупниот износ за секој оддел. Многумина го прават тоа со филтер и пишување со пенкала во клетките.
Иако тоа може да се направи лесно и едноставно со само една функција - SUMMESLI .
SUMMESLES (SUMIF) - Ги собира клетките што ги задоволуваат дадените услови (може да се специфицира само еден услов). Оваа функција може да се користи и ако табелата е поделена на колони по периоди (месечно, во секој месец, три колони - Приходи | Трошок | Разлика) и треба да го пресметате вкупниот износ за сите периоди само од Приходи, Расходи и Разлики.

Постојат вкупно три аргументи за SUMMESLI: Range , Criterion , Range_Summing .
= ЛЕТО (A1: A20000; A1; Б1: B20000)
= SUMIF (A1: A20000, А1, Б1: B20000)

• Опсег (A1: A20000) - го покажува опсегот со критериумите. Односно Колоната во која ќе ја бара вредноста означена со аргументот Критериум .
• Критериумот (А1) е вредноста (текст или нумерички, како и датумот) што мора да се најде во опсегот . Може да ги содржи маска со знаци "*" и "?". Односно специфицирајќи го критериумот "* маса *" за да ги сумира вредностите во кои се појавува зборот "маса". Во исто време, зборот "маса" може да се случи било каде во текстот, или може да има само еден збор во една ќелија. И со наведување "маса *", сите вредности почнувајќи од "маса" ќе бидат сумирани. "?" - заменува само еден знак, т.е. ��о спецификација "mas? a" можете да ги сумирате линиите со вредноста "маса" и вредноста "маска", итн.
  Ако критериумот е напишан во ќелија и сеуште треба да користите знаци со маска, можете да направите врска до оваа ќелија со додавање на потребната. Да претпоставиме дека треба да ги сумирате вредностите кои го содржат зборот "вкупно". Зборот "вкупно" е напишан во ќелијата А1, додека во колоната А може да има различни правописни вредности што го содржат зборот "вкупно": "изнесува за јуни", "изнесува за јули", "изнесува за март". Формулата тогаш треба да изгледа вака:
  = SUMMERS (A1: A20000; "*" & A1 & "*"; Б1: B20000)
  "*" & A1 & "*" - & знакот (ampersand) комбинира неколку вредности во еден. Односно резултатот ќе биде "* резултат *".
  За подобро разбирање на принципот за тоа како функционираат формулите, подобро е да се користи алатката Calculate Formula : Како да ги видите чекорите за пресметување на формулите
  Сите текстуални критериуми и критериуми со логички и математички знаци мора да се затворат во двојни цитати (= SUMMESLI (A1: A20000; "вкупно", Б1: B20000)). Ако критериумот е број, не се потребни цитати. Ако сакате директно да најдете знак за знак или ѕвезда, треба да поставите тилда (~) пред него.
  За тилда и неговите карактеристики може да се најде во оваа статија: Како да се замени / отстрани / пронајде ѕвездичка?
• Sum_Range (B1: B20000) (опционален аргумент) - го одредува опсегот на суми или нумерички вредности кои треба да се сумираат.

Како работи: функцијата го пребарува опсегот за вредноста одредена со аргументот Критериум , и кога се пронајде совпаѓање, ги сумира податоците наведени во аргументот Range_Amount. Односно ако имаме име на одделот во колоната А и износ во колоната Б, а потоа одредување на Одделот за развој како критериум ќе резултира во збирот на сите вредности на колоната Б, наспроти кој Секторот за развој се наоѓа во колона А. Всушност, SumArrangement не може да биде со иста големина како аргументот Range и ова нема да предизвика грешка на самата функција. Меѓутоа, кога се дефинираат ќелиите за собирање, горната лева ќелија од аргументот Range_Amount ќе се користи како почетна ќелија за собирање, а потоа ќе се сумираат клетките кои соодветствуваат на големината и формата на аргументот Range.

Некои карактеристики
Последниот аргумент на функцијата (Sum_And_Band: B1: B20000) не е задолжителен. Ова значи дека не може да се специфицира. Ако не го специфицирате, функцијата ќе ги додаде вредностите наведени во аргументот Range . Што е тоа за. На пример, треба да добиете збир од само оние броеви кои се поголеми од нула. Во колона А од износот. Тогаш функцијата ќе изгледа вака:
= ЛЕТО (A1: A20000; "> 0")

Што треба да се разгледа: range_summing и опсегот треба да бидат еднакви во бројот на линии. Инаку, може да добиете погрешен резултат. Оптимално, ако тоа ќе изгледа како во формулите што ги дадов: опсегот и опсегот на сумациите започнуваат од една линија и имаат ист број на линии: A1: A20000; Б1: B20000

Сумирање на два или повеќе критериуми
Но, што да направите кога критериумите за собирање 2 и повеќе? Да претпоставиме дека треба да ги сумираме само оние износи што припаѓаат на еден оддел и само за одреден датум. Среќни сопственици на канцелариски верзии 2007 и погоре можат да ја користат функцијата SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Првиот аргумент го одредува опсегот на клетки кои ги содржат количините што ќе бидат собрани во една.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Го одредува опсегот на келии во кои сакате да пребарувате по натпревар според критериум.
$ I $ 3, $ H8 - критериум. Овде, како и во SUMMESLI, се користат маска на знаци * и ? и тие работат на ист начин.

Спецификации за специфицирање на аргументите: прво, опсегот на критериуми е наведен (тие се нумерирани), тогаш вредноста (критериумот) е означена директно во точка-запирка, која во овој опсег мора да се најде - $ A $ 2: $ A $ 50; $ I $ 3. И ништо друго. Не треба да се обидувате прво да ги наведете сите опсези, а потоа и критериумите за нив - функцијата или ќе даде грешка, или нема да го сумира она што е потребно.

Сите услови се споредуваат според принципот I. Ова значи дека ако се исполнети сите наведени услови. Ако барем еден услов не е исполнет, функцијата ја прескокнува линијата и не додава ништо.
Што се однесува до ЛЕТОТ, сумирањето и критериумите треба да бидат еднакви во бројот на редови.

Затоа што SUMMESLIMN се појави само во верзии на Excel, почнувајќи од 2007 година, тогаш како може да бидат несреќни корисници на претходните верзии во такви случаи? Многу едноставно: користете друга функција - SUMPRODUCT. Јас нема да ги насликам аргументите, бидејќи Постојат многу од нив и сите тие се низи на вредности. Оваа функција ги множи низите прикажани со аргументите. Ќе се обидам да го опишам генералниот принцип на користење на оваа функција за да ги сумираме податоците за неколку услови.
За да се реши проблемот со збирот по неколку критериуми, функцијата ќе изгледа вака:
= SUMPRODUCT ($ 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 - опсег со износи.

Ние ја анализираме логиката, бидејќи за многумина, тоа ќе биде сосема нејасно само со гледање на оваа функција. Ако само затоа што во помошта оваа апликација не е опишана. За поголема читливост, намалете ја големината на опсезите:
= SUMPRODUCT ($ 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) се логични и враќаат низи на логички FALSE и TRUE. ИСТИНА ако ќелијата од опсегот $ A $ 2: $ A $ 5 е еднаква на вредноста на клетката $ I $ 3, а ќелијата од опсегот $ B $ 2: $ B $ 5 е еднаква на вредноста на ќелијата H5. Односно го имаме следното:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Како што можете да видите, во првата низа постојат две натпревари за состојбата, а во втората. Понатаму, овие две низи се множат (знакот за множење (*) е одговорен за ова). Кога се случува множење, се појавува имплицитна конверзија на низите FALSE и TRUE на нумерички константи 0 и 1, соодветно ({0; 1; 1; 0} * {0; 0; 1; 0}). Како што знаете, кога се множи со нула, добиваме нула. И резултатот е една низа:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Тогаш низата {0; 0; 1; 0} се множи со низа броеви во опсегот $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Како резултат на тоа, добиваме 30. Она што ни е потребно - добиваме само износот што го исполнува критериумот. Ако има повеќе од една сума што ги задоволува критериумите, тогаш тие ќе бидат сумирани.

Предност на SUMMYROIZV
Ако аргументите имаат знак плус наместо знакот за множење:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
тогаш условите ќе се споредат според принципот на OR: т.е. вкупните суми ќе бидат сумирани ако се исполни барем една состојба: или $ A $ 2: $ A $ 5 е еднакво на вредноста на ќелијата $ I $ 3 или ќелија од опсег $ B $ 2: $ B $ 5 е еднаква на вредноста на клетката H5.
Ова е предност на SUMMPRODUCT над SUMMESLIMN. SUMMESLIMN не може да ги сумира вредностите според принципот OR, само според принципот AND (сите услови мора да бидат исполнети).

Недостатоци
SUMPRODUCT не може да користи дијалошки * и ?. Можно е попрецизно да се користи, но тие нема да се сметаат за специјални карактери, туку како ѕвездичка и прашалник. Мислам дека ова е значителен недостаток. И покрај тоа што ова може да биде заобиколено, јас користам други функции во SUMPRODUCT - сè уште ќе биде одлично ако функцијата некако може да користи дијалоги.

Во примерот ќе најдете неколку примери на функции за подобро разбирање на она што е напишано погоре.

Преземете пример

Износ по неколку критериуми (41,5 КБ, 10,477 превземања)

Исто така погледнете:
Сумирање на клетки со боја на полнење
Збирка на клетки со боја на фонт
Сумирање на клетки по клеточен формат
Пресметајте ја количината на клетки со боја на пополнување
Пресметајте ја количината на клетки со боја на фонт
Како да ги сумираме податоците од неколку листови, вклучувајќи ги и условите

{"Дното": {"textstyle": "статичен", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, 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 ":" приказ: блок; постава: апсолутна; горната: 0px; лева: 0px; ширина: 100%; висина: 100% ; позадина-боја: # 333333; непроѕирност: 0,6; филтер: а lpha (непроѕирност = 60); "," titlecss ":" екран: блок; позиција: роднина; фонт: задебелен 14px \ "Луцида без Уникод \", "Лусида Гранде", без шанс, Arial; боја: #fff; "," descriptioncss ":" екран: блок; позиција: роднина; фонт: 12px "Лусида без Уникод", "Лусида Гранде", без шанс, Arial; боја: #fff; margin-top: 8px; "," buttoncss ":" display: block; позиција: роднина; маргина-врвот: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": неточно, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пребарување по ознаки