Bill je poslao ovotjedno Excel pitanje.
Imam bazu podataka događaja u Excelu i moj šef želi da ucrtam frekvencije po mjesecima. Pročitao sam vaš trik kako promijeniti dnevne datume u mjesečne i o Excel CSE formulama. Isprobao sam sve kriterije kojih se sjetim u dolje navedenoj formuli Excel CountIf kako bih ih pogledao u 2 kriterija.
Vaša bi se situacija vjerojatno mogla lako riješiti pomoću zaokretne tablice (XL95-XL2000) ili zaokretne tablice (samo XL2000). Za sada, obratimo se pitanju koje ste postavili. S lijeve strane nalazi se vaš radni list. Čini se da ćete htjeti unijeti formule u ćelije B4406: D4415 da biste izračunali broj određenih događaja svakog mjeseca.
Funkcija CountIf specijalizirani je oblik formule niza što je izvrsno kada imate jedan kriterij. Ne funkcionira dobro kada imate više kriterija. Sljedeće uzorke formula računaju broj redaka s kišom i broj događaja u siječnju 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Ne postoji način da se pomoću CountIf dobije presjek dvaju uvjeta.
Za svakog čitatelja koji nije upoznat s načinom unošenja formula niza, toplo preporučujem pregled Korištenje CSE formula za nadopunu Excel-a.
Bill to nije naveo u svom pitanju, ali želim izgraditi formulu koju može unijeti samo jednom u ćeliju B4406, a koja se lako može kopirati u ostale stanice iz njegovog raspona. Upotrebom apsolutnih i mješovitih referenci u formuli možete uštedjeti gnjavažu oko unošenja nove formule za svako sjecište.
Evo kratkog pregleda apsolutnih, relativnih i mješovitih formula. Ako unesete formulu kao =SUM(A2:A4403)u D1, a zatim kopirate formulu u E2, vaša će se formula u E2 promijeniti u =SUM(B3:C4403). Ovo je sjajna značajka radnih listova koja se naziva "relativno adresiranje", ali ponekad ne želimo da se to dogodi. U ovom slučaju želimo da se svaka formula odnosi na raspon A2: B4403. Dok kopiramo formulu iz stanice u ćeliju, uvijek bi trebala ukazivati na A2: B4403. Tijekom unosa formule pritisnite F4 jednom nakon unosa raspona i vaša će se formula promijeniti u=SUM($A$2:$A$4403). Znak dolara označava da se taj dio reference neće mijenjati dok kopirate formulu. To se naziva apsolutno adresiranje. Moguće je zaključati samo stupac s $ i omogućiti da redak bude relativan. To se naziva mješovitom referencom i unosilo bi se kao =$A4406. Za zaključavanje retka, ali dopustite da stupac bude relativan, upotrijebite =B$4405. Dok unosite formulu, koristite F4 za prebacivanje između četiri okusa relativne, apsolutne i mješovite reference.
Evo formule za ćeliju B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Upišite formulu. Kad završite s formulom, držite tipke Ctrl, Shift i zatim unesite. Sada formulu možete kopirati u C4406: D4406, a zatim te tri ćelije kopirati u svaki redak u tablici rezultata.
Formula koristi sva tri oblika mješovite i apsolutne reference. Gnijezdi 2 ako izrazi budući da funkcija AND () izgleda nije radila u formuli niza. Za bolje objašnjenje što se događa s funkcijom niza, pročitajte ponovo Koristite CSE formule za nadopunu gore spomenutog programa Excel.
