Bill je poslal vprašanje o Excelu v tem tednu.
V Excelu imam bazo podatkov o dogodkih in moj šef želi, da po mesecih narišem frekvenčne lestvice. Prebrala sem vaš trik, kako spremeniti dnevne datume na mesečne datume in o formulah Excel CSE. Preizkusil sem vsa merila, ki se jih spomnim v spodnji formuli Excel CountIf, da si ogledam 2 merila.
Vaš položaj bi verjetno lahko enostavno rešili z vrtilno tabelo (XL95-XL2000) ali vrtilno tabelo (samo XL2000). Za zdaj se lotimo vprašanja, ki ste ga postavili. Na levi je vaš delovni list. Videti je, da boste želeli v celice B4406: D4415 vnesti formule za izračun števila določenih dogodkov vsak mesec.
Funkcija CountIf je specializirana oblika formule matrike, ki je odlična, če imate eno samo merilo. Če imate več meril, ne deluje dobro. Naslednji vzorčni formuli bi šteli število vrstic z Rain in število dogodkov v januarju 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Ni mogoče uporabiti CountIf, da dobimo presečišče dveh pogojev.
Vsakemu bralcu, ki ni seznanjen z vnašanjem formul matrike, toplo priporočam, da pregledate Uporaba formul iskalnikov po meri za dopolnitev Excela.
Bill tega ni navedel v svojem vprašanju, želim pa sestaviti formulo, ki jo lahko vnese samo enkrat v celico B4406, ki jo je mogoče enostavno kopirati v druge celice v svojem obsegu. Z uporabo absolutnih in mešanih referenc v formuli lahko prihranite težave pri vnosu nove formule za vsako križišče.
Tu je hiter pregled absolutnih, relativnih in mešanih formul. Običajno, če vnesete formulo kot =SUM(A2:A4403)v D1 in nato kopirate formulo v E2, se bo formula v E2 spremenila v =SUM(B3:C4403). To je prijetna značilnost delovnih listov, imenovana "relativno naslavljanje", vendar včasih ne želimo, da se to zgodi. V tem primeru želimo, da se vsaka formula nanaša na obseg A2: B4403. Ko kopiramo formulo iz celice v celico, mora vedno kazati na A2: B4403. Med vnašanjem formule enkrat pritisnite F4 po vnosu obsega in vaša formula se bo spremenila v=SUM($A$2:$A$4403). Znak za dolar označuje, da se ta del sklica med kopiranjem formule ne bo spremenil. To se imenuje absolutno naslavljanje. Možno je zakleniti samo stolpec z $ in dovoliti, da je vrstica relativna. To se imenuje mešana referenca in bi se vneslo kot =$A4406. Če želite zakleniti vrstico, vendar omogočiti relativni stolpec, uporabite =B$4405. Ko vnašate formulo, s tipko F4 preklapljajte med štirimi okusi relativne, absolutne in mešane reference.
Tu je formula za celico B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Vnesite formulo. Ko končate formulo, pridržite Ctrl, Shift in nato vnesite. Zdaj lahko kopirate formulo v C4406: D4406 in nato kopirate te tri celice v vsako vrstico v tabeli rezultatov.
Formula uporablja vse tri oblike mešanih in absolutnih referenc. Vgnezdi 2, če stavki, ker funkcija AND () v formuli matrike ne deluje. Za boljšo razlago dogajanja s funkcionalnostjo polja preberite zgoraj Uporabite formule iskalnikov po meri za polnjenje zgoraj omenjenega Excela.
