Bill poslal tento týždeň otázku v programe Excel.
Mám databázu udalostí v programe Excel a môj šéf chce, aby som grafy frekvencie vykresľoval podľa mesiacov. Čítal som váš trik, ako zmeniť denné dátumy na mesačné a o vzorcoch Excel CSE. Vyskúšal som všetky kritériá, na ktoré si spomeniem, vo vzorci Excel CountIf nižšie, aby som sa pozrel na 2 kritériá.
Vaša situácia by sa dala pravdepodobne ľahko vyriešiť pomocou kontingenčnej tabuľky (XL95-XL2000) alebo kontingenčného grafu (iba XL2000). Zatiaľ sa venujme otázke, ktorú ste položili. Naľavo je váš pracovný hárok. Zdá sa, že budete chcieť do buniek B4406: D4415 zadávať vzorce, aby ste vypočítali počet určitých udalostí každý mesiac.
Funkcia CountIf je špecializovaná forma maticového vzorca, ktorá je skvelá, keď máte jediné kritérium. To nefunguje dobre, keď máte viac kritérií. Nasledujúce vzorové vzorce spočítajú počet riadkov s Rain a počet udalostí v januári 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Neexistuje spôsob, ako použiť CountIf na získanie priesečníka dvoch podmienok.
Pre všetkých čitateľov, ktorí nie sú oboznámení s tým, ako zadávať maticové vzorce, dôrazne odporúčam prečítať si článok Používanie vzorcov CSE na preplnenie programu Excel.
Bill to vo svojej otázke neuviedol, ale chcem vytvoriť vzorec, ktorý môže zadať iba raz do bunky B4406, ktorý sa dá ľahko skopírovať do ďalších buniek v jeho rozsahu. Použitím absolútnych a zmiešaných odkazov vo vzorci môžete ušetriť námahu so zadávaním nového vzorca pre každú križovatku.
Tu je rýchly prehľad absolútnych, relatívnych a zmiešaných vzorcov. Za normálnych okolností, ak zadáte vzorec ako =SUM(A2:A4403)v D1 a potom vzorec skopírujete do E2, vzorec v E2 sa zmení na =SUM(B3:C4403). Toto je skvelá vlastnosť pracovných listov nazývaná „relatívne adresovanie“, niekedy však nechceme, aby sa tak stalo. V takom prípade chceme, aby každý vzorec odkazoval na rozsah A2: B4403. Pri kopírovaní vzorca z bunky do bunky by mal vždy smerovať k A2: B4403. Pri zadávaní vzorca stlačte po zadaní rozsahu jedenkrát kláves F4 a vzorec sa zmení na=SUM($A$2:$A$4403). Znak dolára naznačuje, že táto časť referencie sa pri kopírovaní vzorca nezmení. Toto sa nazýva absolútne adresovanie. Je možné uzamknúť iba stĺpec so znakom $ a umožniť relatívny riadok. Toto sa nazýva zmiešaná referencia a zadalo by sa ako =$A4406. Ak chcete riadok uzamknúť, ale umožniť relatívny stĺpec, použite =B$4405. Pri zadávaní vzorca použite klávesu F4 na prepínanie medzi štyrmi príchuťami relatívnych, absolútnych a zmiešaných referencií.
Tu je vzorec pre bunku B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Zadajte vzorec. Po dokončení vzorca podržte kláves Ctrl, Shift a potom stlačte Enter. Teraz môžete skopírovať vzorec na C4406: D4406 a potom skopírovať tieto tri bunky do každého riadku tabuľky s výsledkami.
Vzorec používa všetky tri formy zmiešaných a absolútnych odkazov. Hniezdi 2, ak sa zdá, že príkazy od funkcie AND () nefungovali vo vzorci poľa. Pre lepšie vysvetlenie toho, čo sa deje s funkčnosťou poľa, si prečítajte znovu Použite vzorce CSE na preplnenie vyššie uvedeného programu Excel.
