Бил изпрати въпроса за Excel тази седмица.
Имам база данни за събития в Excel и шефът ми иска да начертая честотни диаграми по месеци. Прочетох вашия трик за промяна на дневни дати на месечни дати и за формули на CSE на Excel. Опитах всички критерии, за които се сетя, във формулата Excel CountIf по-долу, за да разгледам 2 критерия.
Ситуацията ви вероятно би могла да бъде разрешена лесно с обобщена таблица (XL95-XL2000) или обобщена диаграма (само XL2000). Засега нека разгледаме въпроса, който сте задали. Вляво е вашият работен лист. Изглежда, че ще искате да въведете формули в клетки B4406: D4415, за да изчислите броя на определени събития всеки месец.
Функцията CountIf е специализирана форма на формула на масив, която е чудесна, когато имате единични критерии. Не работи добре, когато имате множество критерии. Следните примерни формули ще отчитат броя на редовете с дъжд и броя на събитията през януари 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Няма начин да се използва CountIf, за да се получи пресечната точка на две условия.
За всеки читател, който не е запознат с начина на въвеждане на формули за масиви, силно препоръчвам да прегледате Използване на формули за CSE за презареждане на Excel.
Бил не го заяви в въпроса си, но искам да изградя формула, която той може да въведе само веднъж в клетка B4406, която лесно може да бъде копирана в останалите клетки от неговия диапазон. Използвайки абсолютни и смесени препратки във формулата, можете да спестите неприятностите при въвеждането на нова формула за всяко пресичане.
Ето бърз преглед на абсолютни, относителни и смесени формули. Обикновено, ако въведете формула като =SUM(A2:A4403)в D1 и след това копирате формулата в E2, вашата формула в E2 ще се промени на =SUM(B3:C4403). Това е страхотна характеристика на работни листове, наречена „относително адресиране“, но понякога не искаме това да се случи. В този случай искаме всяка формула да се отнася до диапазона A2: B4403. Докато копираме формулата от клетка в клетка, тя винаги трябва да сочи към A2: B4403. Докато въвеждате формулата, натиснете F4 веднъж след въвеждане на диапазона и вашата формула ще се промени на=SUM($A$2:$A$4403). Знакът за долар показва, че тази част от препратката няма да се промени, докато копирате формулата. Това се нарича абсолютно адресиране. Възможно е да заключите само колоната с $ и да позволите на реда да бъде относителен. Това се нарича смесена препратка и би било въведено като =$A4406. За да заключите реда, но позволете на колоната да бъде относителна, използвайте =B$4405. Докато въвеждате формула, използвайте F4, за да превключвате между четирите вкуса на относителни, абсолютни и смесени препратки.
Ето формулата за клетка B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Въведете формулата. Когато завършите формулата, задръжте Ctrl, Shift и след това въведете. Вече можете да копирате формулата в C4406: D4406 и след това да копирате тези три клетки надолу към всеки ред във вашата таблица с резултати.
Формулата използва и трите форми на смесени и абсолютни препратки. Той влага 2, ако операторите, тъй като функцията AND () изглежда не работи във формула на масив. За по-добро обяснение на случващото се с функционалността на масива, прочетете отново Използвайте формули за CSE за презареждане на Excel, споменати по-горе.
