Sisällys
Onko sinulla Excelissä taulukoita tiedoilla, joiden kokoa voidaan muuttaa, eli rivien (sarakkeiden) määrä voi kasvaa tai pienentyä työn aikana? Jos pöydän koot "kelluvat", sinun on jatkuvasti seurattava tätä hetkeä ja korjattava se:
- raporttikaavojen linkit, jotka viittaavat taulukkoomme
- pivot-taulukoiden alkuperäiset alueet, jotka on rakennettu taulukkomme mukaan
- taulukkomme mukaan rakennettujen kaavioiden alustavat alueet
- vaihteluvälit avattaville valikoille, jotka käyttävät taulukkoamme tietolähteenä
Kaikki tämä yhteensä ei anna sinun kyllästyä 😉
On paljon kätevämpää ja oikeampaa luoda dynaaminen "kumi"-alue, joka mukautuu automaattisesti koon tietorivien ja sarakkeiden todelliseen määrään. Tämän toteuttamiseksi on useita tapoja.
Menetelmä 1. Älykäs pöytä
Korosta solualue ja valitse välilehdestä Etusivu – Muotoile taulukona (Etusivu – Muotoile taulukkona):
Jos et tarvitse taulukkoon sivuvaikutuksena lisättyä raidallista kuviota, voit poistaa sen käytöstä avautuvassa välilehdessä Rakentaja (suunnittelu). Jokainen tällä tavalla luotu taulukko saa nimen, joka voidaan korvata kätevämmällä samassa välilehden kohdassa Rakentaja (suunnittelu) kentällä Taulukon nimi (taulukon nimi).
Nyt voimme käyttää dynaamisia linkkejä "älykkääseen taulukkoomme":
- Taulukko 1 – linkki koko taulukkoon otsikkoriviä lukuun ottamatta (A2:D5)
- Taulukko 1[#Kaikki] – linkki koko taulukkoon (A1:D5)
- Taulukko 1 [Pietari] – viittaus aluesarakkeeseen ilman ensimmäistä soluotsikkoa (C2:C5)
- Taulukko 1[#Headers] – linkki otsikkoon sarakkeiden nimillä (A1:D1)
Tällaiset viittaukset toimivat hyvin kaavoissa, esimerkiksi:
= SUM (Taulukko 1[Moskova]) – sarakkeen "Moskova" summan laskeminen
or
=VPR(F5;Taulukko 1;3;0) – etsi taulukosta kuukausi solusta F5 ja anna sille Pietarin summa (mikä on VLOOKUP?)
Tällaisia linkkejä voidaan käyttää onnistuneesti luotaessa pivot-taulukoita valitsemalla välilehdestä Insert – Pivot Table (Insert – Pivot Table) ja syötä älykkään taulukon nimi tietolähteeksi:
Jos valitset fragmentin tällaisesta taulukosta (esimerkiksi kaksi ensimmäistä saraketta) ja luot minkä tahansa tyyppisen kaavion, uusia rivejä lisättäessä ne lisätään automaattisesti kaavioon.
Avattavia listoja luotaessa ei voi käyttää suoria linkkejä älykkäisiin taulukkoelementteihin, mutta voit kiertää tämän rajoituksen helposti käyttämällä taktista temppua – käytä toimintoa EPÄSUORISTA (Epäsuora), joka muuttaa tekstin linkiksi:
Nuo. tekstimerkkijonon muodossa oleva linkki älytaulukkoon (lainausmerkeissä!) muuttuu täysimittaiseksi linkiksi, ja pudotusluettelo havaitsee sen normaalisti.
Tapa 2: Dynaaminen nimetty alue
Jos tietojesi muuttaminen älykkääksi taulukoksi ei jostain syystä ole toivottavaa, voit käyttää hieman monimutkaisempaa, mutta paljon hienovaraisempaa ja monipuolisempaa menetelmää – luoda Excelissä dynaaminen nimetty alue, joka viittaa taulukkoomme. Sitten, kuten älytaulukon tapauksessa, voit vapaasti käyttää luodun alueen nimeä missä tahansa kaavoissa, raporteissa, kaavioissa jne. Aloitetaan yksinkertaisella esimerkillä:
Tehtävä: tee dynaaminen nimetty alue, joka viittaa kaupunkiluetteloon ja venyttää ja pienentää kokoaan automaattisesti, kun lisäät uusia kaupunkeja tai poistat niitä.
Tarvitsemme kaksi sisäänrakennettua Excel-funktiota, jotka ovat saatavilla missä tahansa versiossa − POICPOZ (OTTELU) määrittääksesi alueen viimeisen solun ja INDEX (INDEKSI) luodaksesi dynaamisen linkin.
Etsitään viimeinen solu komennolla MATCH
VASTAA (hakuarvo, väli, vastaavuustyyppi) – funktio, joka etsii tiettyä arvoa alueelta (rivi tai sarake) ja palauttaa sen solun järjestysnumeron, josta se löydettiin. Esimerkiksi kaava MATCH("Maaliskuu";A1:A5;0) palauttaa tuloksena luvun 4, koska sana "maaliskuu" sijaitsee sarakkeen A1:A5 neljännessä solussa. Viimeinen funktion argumentti Match_Type = 0 tarkoittaa, että etsimme tarkkaa vastaavuutta. Jos tätä argumenttia ei ole määritetty, funktio siirtyy lähimmän pienimmän arvon hakutilaan – juuri tätä voidaan käyttää onnistuneesti taulukon viimeisen varatun solun löytämiseen.
Tempun ydin on yksinkertainen. MATCH etsii soluja alueella ylhäältä alas ja teoriassa sen pitäisi pysähtyä, kun se löytää lähimmän arvon annettua arvoa lähimpänä. Jos määrität halutuksi arvoksi arvon, joka on selvästi suurempi kuin mikään taulukossa saatavilla oleva arvo, niin MATCH saavuttaa taulukon aivan loppuun, ei löydä mitään ja antaa viimeksi täytetyn solun järjestysnumeron. Ja me tarvitsemme sitä!
Jos taulukossamme on vain numeroita, voimme määrittää halutuksi arvoksi luvun, joka on selvästi suurempi kuin mikään taulukossa olevista:
Takuussa voit käyttää numeroa 9E + 307 (9 kertaa 10 307:n potenssiin eli 9 307 nollalla) – maksimiluku, jolla Excel voi periaatteessa toimia.
Jos sarakkeessamme on tekstiarvoja, niin suurimman mahdollisen luvun vastineeksi voit lisätä konstruktion REPEAT(“i”, 255) – 255 kirjaimesta koostuva tekstijono “i” – numeron viimeinen kirjain. aakkoset. Koska Excel itse asiassa vertaa merkkikoodeja haettaessa, mikä tahansa taulukon teksti on teknisesti "pienempi" kuin tällainen pitkä "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" rivi:
Luo linkki käyttämällä INDEXiä
Nyt kun tiedämme taulukon viimeisen ei-tyhjän elementin sijainnin, on vielä muodostettava linkki koko valikoimaamme. Tätä varten käytämme toimintoa:
INDEKSI(väli; rivin_numero; sarakkeen_numero)
Se antaa solun sisällön alueelta rivi- ja sarakenumeroittain, eli esimerkiksi funktio =INDEX(A1:D5;3;4) taulukossamme kaupungeilla ja kuukausilla edellisestä menetelmästä antaa 1240 – sisällön. 3. riviltä ja 4. sarakkeelta eli soluista D3. Jos sarakkeita on vain yksi, sen numero voidaan jättää pois, eli kaava INDEKSI(A2:A6;3) antaa "Samara" viimeisessä kuvakaappauksessa.
Ja on yksi ei aivan ilmeinen vivahde: jos INDEKSI ei kirjoiteta vain soluun =-merkin jälkeen, kuten tavallisesti, vaan sitä käytetään viimeisenä osana viittausta alueeseen kaksoispisteen jälkeen, se ei enää anna solun sisältö, mutta sen osoite! Siten kaava kuten $A$2:INDEKSI($A$2:$A$100;3) antaa viittauksen alueelle A2:A4 lähdössä.
Ja tässä tulee käyttöön MATCH-funktio, jonka lisäämme INDEXin sisään määrittääksemme dynaamisesti luettelon lopun:
=$A$2:INDEKSI($A$2:$100; OSTO(TOISTUS("I";255);A2:A100))
Luo nimetty alue
On vielä pakattava kaikki yhdeksi kokonaisuudeksi. Avaa välilehti kaava (Kaavat) Ja napsauta Name Manager (Nimipäällikkö). Napsauta avautuvassa ikkunassa painiketta luoda (Uusi), kirjoita alueen nimi ja kaava kenttään alue (Viite):
Jäljelle jää klikata OK ja valmis aluetta voidaan käyttää missä tahansa kaavoissa, pudotusvalikoissa tai kaavioissa.
- VLOOKUP-funktion käyttäminen taulukoiden ja hakuarvojen linkittämiseen
- Automaattisesti täytetyn avattavan luettelon luominen
- Pivot-taulukon luominen suuren datamäärän analysoimiseksi