Dynaamisen alueen käyttäminen Excelissä COUNTIF:n ja INDIRECTin kanssa

Sisällysluettelo:

2024 Kirjoittaja: Abigail Brown | [email protected]. Viimeksi muokattu: 2023-12-17 06:46

Mitä tietää

PYÖRÄ-funktio muuttaa soluviittausten alueen kaavassa muokkaamatta kaavaa.
Käytä INDIRECTia argumenttina COUNTIF-arvolle luodaksesi dynaamisen solualueen, joka täyttää määritetyt ehdot.
Ehdot määritetään EPÄSUORA-funktiolla, ja vain ehdot täyttävät solut lasketaan.

Tässä artikkelissa selitetään, kuinka EXCEL-kaavojen EPÄSUORA-funktiota käytetään kaavassa käytettyjen soluviittausten alueen muuttamiseen tarvitsematta muokata itse kaavaa. Tämä varmistaa, että samoja soluja käytetään, vaikka laskentataulukko muuttuu. Tiedot koskevat Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac ja Excel Online.

Käytä dynaamista aluetta COUNTIF - INDIRECT -kaavan kanssa

Puolisuora-funktiota voidaan käyttää useiden funktioiden kanssa, jotka hyväksyvät soluviittauksen argumenttina, kuten SUMMA- ja COUNTIF-funktiot.

Käyttämällä INDIRECTiä argumenttina COUNTIF luo dynaamisen alueen soluviittauksia, jotka funktio voi laskea, jos solujen arvot täyttävät ehdot. Se tekee tämän muuttamalla tekstidataa, jota joskus kutsutaan tekstimerkkijonoksi, soluviittaukseksi.

Tämä esimerkki perustuu yllä olevassa kuvassa esitettyihin tietoihin. Opetusohjelmassa luotu COUNTIF - INDIRECT -kaava on:

=LASKEJOS(EPÄSUORA(E1&":"&E2), ">10")

Tässä kaavassa EPÄSUORA-funktion argumentti sisältää:

Solussa on viittaukset E1 ja E2, jotka sisältävät tekstidatan D1 ja D6.
Alueoperaattori, kaksoispiste (:), jota ympäröivät lainausmerkit (" "), joka muuttaa kaksoispisteen tekstiksi merkkijono.
Kaksi et-merkkiä (&), joita käytetään kaksoispisteen ketjuttamiseen tai yhdistämiseen soluviittauksilla E1 ja E2.

Tulos on se, että INDIRECT muuntaa tekstijonon D1:D6 soluviittaukseksi ja välittää sen COUNTIF-funktioon, joka lasketaan, jos viitatut solut ovat suurempia kuin 10.

PÄÄLLÄ-toiminto hyväksyy kaikki tekstinsyötteet. Nämä voivat olla laskentataulukon soluja, jotka sisältävät tekstiä tai tekstisoluviittauksia, jotka on syötetty suoraan funktioon.

Vaihda dynaamisesti kaavan aluetta

Muista, että tavoitteena on luoda kaava, jolla on dynaaminen alue. Dynaamista aluetta voidaan muuttaa muokkaamatta itse kaavaa.

Muutamalla soluissa E1 ja E2 olevaa tekstidataa D1:stä ja D6:sta D3:ksi ja D7:ksi funktion summaama alue voidaan helposti muuttaa D1:D6:sta D3:D7:ksi. Tämä poistaa tarpeen muokata kaavaa suoraan solussa G1.

Tämän esimerkin COUNTIF-funktio laskee vain numeroita sisältävät solut, jos ne ovat suurempia kuin 10. Vaikka neljä viidestä D1:D6-alueen solusta sisältää dataa, vain kolme solua sisältää numeroita. Funktio ohittaa solut, jotka ovat tyhjiä tai sisältävät tekstidataa.

Tekstin laskeminen COUNTIF:llä

COUNTIF-toiminto ei rajoitu numeeristen tietojen laskemiseen. Se myös laskee tekstiä sisältävät solut tarkistamalla, vastaavatko ne tiettyä tekstiä.

Tämä tehdään syöttämällä seuraava kaava soluun G2:

=LASKE JOS(VÄLÄ(E1&":"&E2), "kaksi")

Tässä kaavassa EPÄSUORA-funktio viittaa soluihin B1–B6. COUNTIF-funktio laskee yhteen niiden solujen lukumäärän, joissa on tekstiarvo kaksi.

Tässä tapauksessa tulos on 1.

COUNTA, COUNTBLANK ja EPÄSUORA

Kaksi muuta Excel-laskentafunktiota ovat COUNTA, joka laskee solut, jotka sisältävät minkä tahansa tyyppisiä tietoja jättäen huomioimatta vain tyhjät tai tyhjät solut, ja COUNTBLANK, joka laskee vain tyhjät tai tyhjät solut alueella.

Koska näillä molemmilla funktioilla on samanlainen syntaksi kuin COUNTIF-funktiolla, ne voidaan korvata yllä olevassa esimerkissä INDIRECT:llä seuraavien kaavojen luomiseksi:

=LASKEA(VÄLÄSUORA(E1&":"&E2))

=LASKETYHJÄ(VÄLÄ(E1&":"&E2)

Alulle D1:D6 COUNTA palauttaa vastauksen 4, koska neljä viidestä solusta sisältää dataa. COUNTBLANK palauttaa vastauksen 1, koska alueella on vain yksi tyhjä solu.

Miksi käyttää EPÄSUORA funktiota?

Etu EPÄSUORA-funktion käyttämisestä kaikissa näissä kaavoissa on, että uusia soluja voidaan lisätä mihin tahansa alueelle.

Alue siirtyy dynaamisesti eri toimintojen sisällä, ja tulokset päivittyvät vastaavasti.

Ilman EPÄSUORA-funktiota jokainen funktio olisi muokattava niin, että se sisältää kaikki 7 solua, mukaan lukien uusi.

PÄRISTÄ-funktion edut ovat, että tekstiarvoja voidaan lisätä soluviittauksina ja että se päivittää alueet dynaamisesti aina, kun laskentataulukko muuttuu.

Tämä tekee laskentataulukoiden ylläpidosta paljon helpompaa, etenkin erittäin suurille laskentataulukoille.