Bill zaslal tento týden otázku v Excelu.
Mám databázi událostí v aplikaci Excel a můj šéf chce, abych vykreslil frekvenční grafy podle měsíce. Přečetl jsem si váš trik, jak změnit denní data na měsíční a o vzorcích Excel CSE. Vyzkoušel jsem všechna kritéria, která mě napadnou ve vzorci Excel CountIf níže, abych se podíval na 2 kritéria.
Vaši situaci lze pravděpodobně snadno vyřešit pomocí kontingenčního stolu (XL95-XL2000) nebo kontingenčního grafu (pouze XL2000). Prozatím se zaměřme na otázku, kterou jste položili. Vlevo je váš list. Vypadá to, že budete chtít do buněk B4406: D4415 zadávat vzorce, abyste každý měsíc vypočítali počet určitých událostí.
Funkce CountIf je specializovaná forma maticového vzorce, což je skvělé, když máte jediné kritérium. To nefunguje dobře, když máte více kritérií. Následující ukázkové vzorce by počítaly počet řádků s Rain a počet událostí v lednu 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Neexistuje způsob, jak pomocí CountIf získat křižovatku dvou podmínek.
Pro každého čtenáře, který není obeznámen s tím, jak zadávat maticové vzorce, důrazně doporučuji zkontrolovat použití vzorců CSE k přeplňování aplikace Excel.
Bill to ve své otázce neuváděl, ale chci vytvořit vzorec, který může zadat pouze jednou v buňce B4406, který lze snadno zkopírovat do ostatních buněk v jeho rozsahu. Použitím absolutních a smíšených odkazů ve vzorci můžete ušetřit potíže se zadáváním nového vzorce pro každou křižovatku.
Zde je rychlý přehled absolutních, relativních a smíšených vzorců. Normálně, pokud zadáte vzorec jako =SUM(A2:A4403)v D1 a poté zkopírujete vzorec do E2, váš vzorec v E2 se změní na =SUM(B3:C4403). Toto je skvělá vlastnost listů nazývaná „relativní adresování“, ale někdy nechceme, aby se to stalo. V tomto případě chceme, aby každý vzorec odkazoval na rozsah A2: B4403. Při kopírování vzorce z buňky do buňky by měl vždy ukazovat na A2: B4403. Při zadávání vzorce stiskněte po zadání rozsahu klávesu F4 a vzorec se změní na=SUM($A$2:$A$4403). Znak dolaru znamená, že se tato část odkazu při kopírování vzorce nezmění. Tomu se říká absolutní adresování. Je možné uzamknout pouze sloupec s $ a povolit relativní řádek. Tomu se říká smíšený odkaz a bude zadán jako =$A4406. Chcete-li řádek uzamknout, ale umožnit relativní sloupec, použijte =B$4405. Při zadávání vzorce použijte klávesu F4 k přepínání mezi čtyřmi příchutěmi relativních, absolutních a smíšených odkazů.
Zde je vzorec pro buňku B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Zadejte vzorec. Po dokončení vzorce podržte Ctrl, Shift a poté zadejte. Nyní můžete zkopírovat vzorec do C4406: D4406 a poté zkopírovat tyto tři buňky do každého řádku v tabulce výsledků.
Vzorec používá všechny tři formy smíšených a absolutních odkazů. Vnořuje 2, pokud příkazy od funkce AND () zřejmě nefungovaly ve vzorci pole. Chcete-li lépe vysvětlit, co se děje s funkcemi pole, přečtěte si znovu Pomocí vzorců CSE přeplňujte výše uvedený Excel.
