Pivot-taulukot sopivat kaikille – ne laskevat nopeasti ja konfiguroidaan joustavasti, ja muotoilu voidaan tarvittaessa kääriä niihin tyylikkäästi. Mutta on myös muutamia kärpäsiä, erityisesti kyvyttömyys luoda yhteenvetoa, jossa arvoalueella ei pitäisi olla numeroita, vaan tekstiä.
Yritetään kiertää tämä rajoitus ja keksiä "pari kainalosauvaa" samanlaiseen tilanteeseen.
Oletetaan, että yrityksemme kuljettaa tuotteitaan konteissa useisiin kaupunkeihin Maamme ja Kazakstanissa. Kontteja lähetetään enintään kerran kuukaudessa. Jokaisella säiliöllä on aakkosnumeerinen numero. Lähtötietona on vakiona toimitukset listattu taulukko, josta pitää tehdä jonkinlainen yhteenveto, jotta näet selkeästi kuhunkin kaupunkiin ja kuukausittain lähetettyjen konttien määrät:
Mukavuuden vuoksi tehdään taulukosta alkutiedoista "älykäs" etukäteen komennolla Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona) ja anna hänelle nimi toimitukset kieleke Rakentaja (Design). Tulevaisuudessa tämä yksinkertaistaa elämää, koska. taulukon ja sen sarakkeiden nimeä voidaan käyttää suoraan kaavoissa.
Tapa 1. Helpoin – käytä Power Queryä
Power Query on erittäin tehokas työkalu tietojen lataamiseen ja muuntamiseen Excelissä. Tämä apuohjelma on rakennettu Exceliin oletusarvoisesti vuodesta 2016 lähtien. Jos sinulla on Excel 2010 tai 2013, voit ladata ja asentaa sen erikseen (täysin ilmaiseksi).
Selvyyden vuoksi analysoin koko prosessin vaihe vaiheelta seuraavassa videossa:
Jos Power Queryn käyttäminen ei ole mahdollista, voit käyttää muita tapoja – pivot-taulukon tai kaavojen kautta.
Menetelmä 2. Ylimääräinen yhteenveto
Lisätään vielä yksi sarake alkuperäiseen taulukkoomme, jossa yksinkertaisella kaavalla lasketaan taulukon jokaisen rivin numero:
Ilmeisesti -1 tarvitaan, koska taulukossamme on yksirivinen otsikko. Jos taulukkosi ei ole taulukon alussa, voit käyttää hieman monimutkaisempaa, mutta yleismaailmallista kaavaa, joka laskee eron nykyisen rivin ja taulukon otsikon numeroissa:
Nyt rakennamme tavalliseen tapaan tietojemme perusteella halutun tyyppisen pivot-taulukon, mutta arvokenttään pudotamme kentän Linjan numero sen sijaan, mitä haluamme astia:
Koska meillä ei ole useita kontteja samassa kaupungissa saman kuukauden aikana, tiivistelmämme ei itse asiassa anna tarvitsemamme konttien määrää, vaan rivinumeroita.
Lisäksi voit poistaa pää- ja välisummat käytöstä välilehdellä Rakentaja – Yleiset summat и Välisummat (Suunnittelu – loppusummat, välisummat) ja samassa paikassa vaihda yhteenveto kätevämpään taulukkoasetteluun -painikkeella Ilmoita mallista (Raportin asettelu).
Olemme siis jo puolessa välissä tulosta: meillä on taulukko, jossa kaupungin ja kuukauden risteyksessä on lähdetaulukon rivinumero, jossa on tarvitsemamme konttikoodi.
Kopioidaan nyt yhteenveto (samalle arkille tai toiselle) ja liitetään se arvoiksi ja syötetään sitten kaavamme arvoalueelle, joka poimii konttikoodin yhteenvedosta löytyvän rivinumeron mukaan:
Toiminto IF (JOS), tässä tapauksessa tarkistaa, ettei yhteenvedon seuraava solu ole tyhjä. Jos tyhjä, anna tyhjä tekstimerkkijono “”, eli jätä solu tyhjäksi. Jos se ei ole tyhjä, ota se pois sarakkeesta Kontti lähdetaulukko toimitukset solun sisältö rivinumeron mukaan funktion avulla INDEX (INDEKSI).
Ehkä ainoa ei kovin ilmeinen kohta tässä on kaksoissana Kontti kaavassa. Niin outo kirjoitusmuoto:
Tarvikkeet[[Säilö]:[Säilö]]
… tarvitaan vain sarakkeeseen viittaamiseen Kontti oli absoluuttinen (kuten viittaus $-merkeillä tavallisille "ei-älykkäille" taulukoille) eikä luiskahtanut viereisiin sarakkeisiin kopioitaessa kaavaamme oikealle.
Jatkossa lähdetaulukon tietoja muutettaessa toimitukset, meidän on muistettava päivittää apuyhteenveto rivinumeroilla napsauttamalla sitä hiiren kakkospainikkeella ja valitsemalla komennon Päivitä & Tallenna (Virkistää).
Menetelmä 3. Kaavat
Tämä menetelmä ei vaadi välipivot-taulukon luomista ja manuaalista päivitystä, vaan käyttää Excelin ”raskasta asetta” – toimintoa. SUMMESLIMN (SUMIFS). Sen sijaan, että etsit rivinumeroita yhteenvedosta, voit laskea ne käyttämällä seuraavaa kaavaa:
Tietyn ulkoisen koon vuoksi tämä on itse asiassa tavallinen käyttötapaus valikoivalle summaustoiminnolle SUMMESLIMNA, joka summaa tietyn kaupungin ja kuukauden rivinumerot. Jälleen, koska meillä ei ole useita kontteja samassa kaupungissa saman kuukauden aikana, toimintomme ei itse asiassa anna määrää, vaan itse rivinumeron. Ja sitten edellisestä menetelmästä jo tuttu toiminto INDEX Voit myös poimia säilökoodit:
Tietenkään tässä tapauksessa sinun ei enää tarvitse ajatella yhteenvedon päivittämistä, vaan suurissa taulukoissa funktio SUMMESLI voi olla huomattavan hidas. Sitten sinun on poistettava kaavojen automaattinen päivitys käytöstä tai käytettävä ensimmäistä menetelmää - pivot-taulukkoa.
Jos yhteenvedon ulkoasu ei ole kovin sopiva raportiisi, voit poimia siitä rivinumerot lopputaulukkoon ei suoraan, kuten teimme, vaan käyttämällä toimintoa GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kuinka tämä tehdään, löydät täältä.
- Raportin luominen pivot-taulukon avulla
- Laskelmien määrittäminen pivot-taulukoissa
- Valikoiva laskenta SUMIFS-, COUNTIFS- jne.