Üks Power Pivoti võimsamaid funktsioone on võimalus luua tabelite vahel seoseid ja seejärel kasutada seotud tabeleid seotud andmete otsimiseks või filtreerimiseks. Seotud väärtused saate tabelitest tuua Power Pivoti andmeanalüüsi avaldiste (DAX) valemikeele abil. DAX kasutab relatsioonmudelit ja saab seetõttu hõlpsalt ja täpselt tuua teises tabelis või veerus seostuvaid või vastavaid väärtusi. Kui olete Exceli funktsiooniga VLOOKUP tuttav, on see Power Pivoti funktsioon sarnane, kuid seda on palju lihtsam rakendada.
Saate luua valemeid, mis otsivad arvutusliku veeru osana või mõõdu osana PivotTable-liigendtabelis või PivotChart-liigenddiagrammis kasutamiseks. Lisateavet leiate järgmistest teemadest.
Arvutuslikud väljad Power Pivotis
Arvutatud veerud Power Pivotis
Selles jaotises kirjeldatakse otsinguks pakutavaid DAX-i funktsioone ja näiteid funktsioonide kasutamise kohta.
Märkus.
Olenevalt sellest, millist tüüpi otsingutoimingut või otsinguvalemi soovite kasutada, peate võib-olla esmalt looma tabelite vahel seose.
Otsingufunktsioonide ülevaade
Vastendamise või mõnest muust tabelist seotud andmete otsimise võimalus on eriti kasulik olukordades, kus praegusel tabelil on ainult mingi identifikaator, kuid vajalikud andmed (nt toote hind, nimi või muud üksikasjalikud väärtused) talletatakse seotud tabelis. See on kasulik ka siis, kui teises tabelis on mitu rida, mis on seotud praeguse rea või praeguse väärtusega. Näiteks saate hõlpsalt tuua kõik kindla piirkonna, poe või müügiesindajaga seotud müügitehingud.
Erinevalt Massiividel põhinevatest Exceli otsingufunktsioonidest (nt VLOOKUP) või funktsioonist LOOKUP, mis saab esimesena mitmest vastetavast väärtusest, järgib DAX võtmetega ühendatud tabelite olemasolevaid seoseid, et saada täpselt vastav väärtus. DAX saab tuua ka kirjetabeli, mis on seotud praeguse kirjega.
Märkus.
Kui olete relatsioonandmebaasidega tuttav, võite Power Pivoti otsinguid pidada sarnaselt Transact-SQL-i pesastatud alamvalikulausega.
Ühe seostuva väärtuse toomine
Funktsioon RELATED tagastab üksikväärtuse teisest tabelist, mis on seotud praeguse tabeli praeguse väärtusega. Saate määrata veergu, mis sisaldab soovitud andmeid, ja funktsioon järgib olemasolevaid tabelitevahelisi seoseid, et tuua väärtus seotud tabeli määratud veerust. Mõnel juhul peab funktsioon andmete toomiseks järgima seoste ahelat.
Oletagem näiteks, et teil on Excelis loend tänastest saadetistest. Loend sisaldab aga ainult töötaja ID-numbrit, tellimuse ID-t ja ekspediitri ID-numbrit, mis muudab aruande lugemise keeruliseks. Soovitud lisateabe saamiseks saate selle loendi teisendada Power Pivoti lingitud tabeliks ja seejärel luua seosed tabelitega Töötaja ja Edasimüüja, vastendades töötaja ID väljaga TöötajaVõti ja EdasimüüjaID väljale EdasimüüjaVõti.
Lingitud tabelis otsinguteabe kuvamiseks tuleb lisada kaks uut arvutatud veergu koos järgmiste valemitega.
= RELATED('Töötajad'[TöötajaNimi])
= RELATED('Edasimüüjad'[EttevõtteNimi])
Tänased saadetised enne otsingut
| OrderID | Töötaja ID | Edasimüüja ID |
|---|---|---|
| 100314 | 230 | 445 |
| 100315 | 15 | 445 |
| 100316 | 76 | 108 |
Tabel „Töötajad“
| Töötaja ID | Töötaja | Edasimüüja |
|---|---|---|
| 230 | Kuppa Vamsi | Modular Cycle Systems |
| 15 | Pilar Ackeman | Modular Cycle Systems |
| 76 | Kim Ralls | Seotud jalgrattad |
Tänased saadetised koos otsingutega
| OrderID | Töötaja ID | Edasimüüja ID | Töötaja | Edasimüüja |
|---|---|---|---|---|
| 100314 | 230 | 445 | Kuppa Vamsi | Modular Cycle Systems |
| 100315 | 15 | 445 | Pilar Ackeman | Modular Cycle Systems |
| 100316 | 76 | 108 | Kim Ralls | Seotud jalgrattad |
Funktsioon kasutab lingitud tabeli ning tabeli Töötajad ja Edasimüüjad vahelisi seoseid, et saada aruande iga rea jaoks õige nimi. Seotud väärtusi saate kasutada ka arvutustes. Lisateavet ja näiteid leiate teemast Funktsioon RELATED.
Seostuvate väärtuste loendi toomine
Funktsioon RELATEDTABLE järgib olemasolevat seost ja tagastab tabeli, mis sisaldab kõiki määratud tabeli vastavaid ridu. Oletagem näiteks, et soovite teada saada, mitu tellimust on iga edasimüüja sel aastal esitanud. Tabelis Edasimüüjad saate luua uue arvutatud veeru, mis sisaldab järgmist valemit, mis otsib iga edasimüüja kirjeid ResellerSales_USD tabelist ja loendab iga edasimüüja esitatud tellimuste arvu.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
Selles valemis saab funktsioon RELATEDTABLE esmalt väärtuse ResellerKey iga praeguse tabeli edasimüüja kohta. (ID-veergu pole vaja valemis kuskil määrata, kuna Power Pivot kasutab tabelite vahel olemasolevat seost.) Seejärel saab funktsioon RELATEDTABLE kõik ResellerSales_USD tabeli read, mis on iga edasimüüjaga seotud, ja loendab read. Kui kahe tabeli vahel puudub seos (otsene või kaudne), saate kõik read ResellerSales_USD tabelist.
Edasimüüja Modular Cycle Systemsi kohta meie näidisandmebaasis on müügitabelis neli tellimust, seega tagastab funktsioon väärtuse 4. Seostuvate jalgrataste puhul edasimüüjal pole müüki, seega tagastab funktsioon tühja väärtuse.
| Edasimüüja | Selle edasimüüja müügitabeli kirjed |
|---|---|
| Modular Cycle Systems | Edasimüüja ID |
| 445 | |
| 445 | |
| 445 | |
| 445 | |
| Edasimüüja ID | |
| Seotud jalgrattad |
Märkus.
Kuna funktsioon RELATEDTABLE tagastab tabeli, mitte ühe väärtuse, tuleb seda kasutada argumendina funktsioonile, mis teostab tabelitega toiminguid. Lisateavet leiate teemast Funktsioon RELATEDTABLE.