Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Oppiskele Dynaamiset ja parametriohjatut haut | Kehittyneet Hakujärjestelmät ja Relaatioiden Mallinnus
Excel-kaavat

Dynaamiset ja parametriohjatut haut

Pyyhkäise näyttääksesi valikon

Työkirja tukee jo relaatiohakuja ja dynaamista raportointia. Tässä luvussa rakennetaan kategoriatasoiset yhteenvedot ja otetaan käyttöön parametriohjattu logiikka, joka muuttaa laskentaa dynaamisesti käyttäjän valitsemien skenaarioiden perusteella.

SUMPRODUCT-rakenne

=SUMPRODUCT(array1 * array2 * ...)
  • array1: ensimmäinen laskentataulukko;
  • array2: toinen laskentataulukko;
  • TRUE: muunnetaan arvoksi 1;
  • FALSE: muunnetaan arvoksi 0.

Tämän avulla loogiset ehdot ja yhteenlaskenta voidaan toteuttaa yhdellä kaavalla.

INDIRECT-rakenne

=INDIRECT(ref_text, [a1])
  • ref_text: teksti, joka muunnetaan aktiiviseksi viittaukseksi;
  • [a1]: valinnainen viittaustyylin argumentti.

INDIRECT mahdollistaa kaavojen viittausten vaihtamisen dynaamisesti soluarvojen perusteella.

Vaihe 1 Luo kategoria-yhteenveto
expand arrow

Summary-välilehdelle lisää seuraavat otsikot:

Category
Total_Revenue
Total_Cost
Total_Profit
Vaihe 2 Luo kategorialista
expand arrow

Soluun A10 kirjoita:

=UNIQUE(Products[Category])

Kategorialista laajenee nyt automaattisesti uusien kategorioiden lisäyksen myötä.

Vaihe 3 Laske liikevaihto kategorioittain
expand arrow

Soluun B10 kirjoita:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*Sales_Data[Revenue])
  • XLOOKUP(...): hakee kategoria-arvot jokaiselle tuotteelle;
  • =A10: tarkistaa täsmääkö kategoria;
  • Sales_Data[Revenue]: summattavat arvot.

Täytä kaava sarakkeeseen alas.

Vaihe 4 Laske kustannukset kategorioittain
expand arrow

Soluun C10 kirjoita:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*XLOOKUP(Sales_Data[Product],Products[Product],Products[Cost],0)*Sales_Data[Units])

Kaava laskee dynaamisesti kokonaiskustannuksen kategorioittain.

Vaihe 5 Laske voitto
expand arrow

Soluun D10 kirjoita:

=B10-C10

Täytä kaava sarakkeeseen alas ja muotoile kaikki arvot asianmukaisesti.

Vaihe 6 Luo skenaariovalitsin
expand arrow

Summary-välilehdelle luo solu seuraavalle:

Active Pricing Scenario

Lisää tietojen validointi seuraavilla vaihtoehdoilla:

Pricing_Tiers
Pricing_Tiers_Promo
Vaihe 7 Rakenna dynaaminen alennushaku
expand arrow

Sales_Data-välilehdellä korvaa aiempi alennuskaava seuraavalla:

=XLOOKUP([@Units],INDIRECT(Summary!$F$9&"[Min_Units]"),INDIRECT(Summary!$F$9&"[Discount_Rate]"),0,-1)
  • Summary!$F$9: valittu skenaariotaulu;
  • INDIRECT(...): muuntaa tekstin aktiivisiksi taulukkoviittauksiksi;
  • -1: likimääräinen vastaavuustila.

Haku vaihtuu nyt dynaamisesti hinnoitteluskenaarioiden välillä.

Vaihe 8 Testaa skenaariovaihto
expand arrow

Vaihda valittu arvo skenaariovalikossa.

Varmista, että:

  • Discount_Rate päivittyy automaattisesti;
  • Discounted_Revenue päivittyy automaattisesti;
  • Kaikki riippuvat laskelmat reagoivat valittuun hinnoittelumalliin.

1. Mikä on SUMPRODUCT-funktion rooli tässä luvussa?

2. Miksi INDIRECT-funktiota käytetään parametriohjatuissa malleissa?

3. Mikä on tärkein hyöty UNIQUE- ja SUMPRODUCT-funktioiden käytöstä yhteenvetotaulukoissa?

question mark

Mikä on SUMPRODUCT-funktion rooli tässä luvussa?

Valitse oikea vastaus

question mark

Miksi INDIRECT-funktiota käytetään parametriohjatuissa malleissa?

Valitse oikea vastaus

question mark

Mikä on tärkein hyöty UNIQUE- ja SUMPRODUCT-funktioiden käytöstä yhteenvetotaulukoissa?

Valitse oikea vastaus

Oliko kaikki selvää?

Miten voimme parantaa sitä?

Kiitos palautteestasi!

Osio 3. Luku 5

Kysy tekoälyä

expand

Kysy tekoälyä

ChatGPT

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Osio 3. Luku 5
some-alt