Käytä INDIRECT-funktiota Excel-kaavoissa muuttaaksesi kaavassa käytettäviä soluviitteitä, ilman että sinun täytyy itse muokata kaavaa. Näin varmistetaan, että samoja soluja käytetään, vaikka laskentataulukko muuttuisi.
Huomautus: Tässä artikkelissa olevat ohjeet koskevat Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac ja Excel Online.
Käytä dynaamista aluetta COUNTIF - INDIRECT-kaavalla
INDIRECT-funktiota voidaan käyttää useilla toiminnoilla, jotka hyväksyvät soluversion argumenttina, kuten SUM- ja COUNTIF-toiminnot.
Käyttämällä INDIRECTia COUNTIF-argumenttina luodaan dynaaminen solualueen viitearvo, jonka funktio voi laskea, jos soluarvot täyttävät kriteerit. Se tekee tämän kääntämällä tekstidataa, joskus kutsutaan tekstijonoksi, soluviitteeksi.
Tämä esimerkki perustuu yllä olevassa kuvassa esitettyihin tietoihin. Tutorialissa luotu COUNTIF - INDIRECT-kaava on seuraava:
= LASKE.JOS (epäsuora (E1 & ":" & E2), "> 10")
Tässä kaavassa argumentti INDIRECT-funktiolle sisältää:
- Soluviitteet E1 ja E2, jotka sisältävät tekstidataa D1 ja D6.
- Alueoperaattori, kaksoispiste (:), jota ympäröivät kaksoisvetomerkit (' '), joka muuttaa kaksoispisteen tekstiriviksi.
- Kaksi ampersands (&), joita käytetään ketjuttamiseen tai liittyä yhteen, kaksoispiste soluviitteillä E1 ja E2.
Tuloksena on, että INDIRECT muuntaa tekstijono D1: D6 soluviitteeksi ja siirtää sen COUNTIF-funktioon, joka lasketaan, jos viitattuja soluja on suurempi kuin 10.
INDIRECT-toiminto hyväksyy minkä tahansa tekstinsyötön. Nämä voivat olla laskentataulukon soluja, jotka sisältävät teksti- tai tekstisolujen viitteitä, jotka syötetään suoraan funktioon.
Dynaamisesti muuttaa kaavan rajaa
Muista, että tavoitteena on luoda kaava, jolla on dynaaminen alue. Dynaamista aluetta voidaan muuttaa muuttamatta kaavaa.
Vaihtelemalla soluissa E1 ja E2 sijaitsevista tekstidiedoista D1: stä ja D6: sta D3: een ja D7: een voidaan funktioalueella muuttaa helposti D1: D6: stä D3: D7: ään. Tämä poistaa tarpeen muokata kaavaa suoraan solussa G1.
COUNTIF-toiminto tässä esimerkissä laskee vain soluja, jotka sisältävät numeroita, jos ne ovat suurempia kuin 10. Vaikka neljä viidestä D1: D6-soluista sisältää tietoja, vain kolme solua sisältävät numeroita. Toiminto ei huomioi soluja, jotka ovat tyhjiä tai sisältävät tekstidataa.
Tekstin laskeminen COUNTIF: llä
COUNTIF-toiminto ei ole rajoitettu numeeristen tietojen laskemiseen. Se myös laskee soluja, jotka sisältävät tekstiä tarkistamalla, vastaavatko ne tiettyyn tekstiin.
Tätä varten soluun G2 syötetään seuraava kaava:
= LASKE.JOS (epäsuora (E1 & ":" & E2), "kaksi")
Tässä kaavassa INDIRECT-funktio viittaa solut B1 - B6. COUNTIF-funktio kertoo niiden solujen lukumäärän, joilla on teksti-arvo kaksi heissä.
Tällöin tulos on 1.
COUNTA, COUNTBLANK ja INDIRECT
Kaksi muuta Excel-laskutoimintoa ovat COUNTA, joka laskee soluja, jotka sisältävät minkä tahansa tyyppistä dataa ja jättäen huomiotta vain tyhjät tai tyhjät solut, ja COUNTBLANK, joka laskee vain tyhjiä tai tyhjiä soluja alueella.
Koska molemmilla näillä toiminnoilla on samanlainen syntaksi COUNTIF-toiminnon kanssa, ne voidaan korvata edellä esitetyllä esimerkillä INDIRECTin avulla luoda seuraavat kaavat:
= LASKE.A (epäsuora (E1 & ":" & E2))= COUNTBLANK (epäsuora (E1 & ":" & E2)
Alueella D1: D6 COUNTA palauttaa vastauksen neljästä, sillä neljä viidestä solusta sisältää tietoja. COUNTBLANK palauttaa vastauksen 1, koska alueella on vain yksi tyhjäsolu. INDIRECT-toiminnon hyödyntäminen kaikissa näissä kaavoissa on, että uudet solut voidaan sijoittaa mihin tahansa alueeseen. Alue vaihtelee dynaamisesti eri toimintojen sisällä ja tulokset päivittyvät vastaavasti. Ilman INDIRECT-funktiota jokainen toiminto on muokattava siten, että se sisältää kaikki 7 solua, mukaan lukien uusi. INDIRECT-toiminnon edut ovat, että tekstiviestit voidaan lisätä soluviitteiksi ja päivittää dynaamisesti alueita aina, kun laskentataulukko muuttuu. Tämä tekee laskentataulukon yleisestä ylläpidosta paljon helpompaa, varsinkin hyvin suurille laskentataulukkoille. Miksi käyttää välinettä?
