Chii chinonzi Power Query / Pivot / Mepu / Tarisa / BI uye nei vachida mushandisi weExcel

Mazwi ekuti "Simba Query", "Simba Pivot", "Simba BI" nemamwe "masimba" ari kuwedzera kubuda muzvinyorwa uye zvinhu nezveMicrosoft Excel. Muchiitiko changu, havasi vese vanonyatsonzwisisa zviri kuseri kwepfungwa idzi, kuti dzakabatanidzwa sei uye kuti dzinogona kubatsira sei mushandisi weExcel ari nyore.

Ngatijekese mamiriro acho ezvinhu.

Simba Query

Kudzoka muna 2013, boka rakanyatsogadzirwa revagadziri mukati meMicrosoft rakaburitsa yemahara yekuwedzera yeExcel. Simba Query (mamwe mazita ari Data Explorer, Tora & Shandura), iyo inogona kuita zvinhu zvakawanda zvinobatsira kubasa remazuva ese:

• Upload data muExcel kubva kunosvika makumi mana kwakasiyana masosi, kusanganisira dhatabhesi (SQL, Oracle, Access, Teradata…), makambani ERP masisitimu (SAP, Microsoft Dynamics, 40C…), Internet masevhisi (Facebook, Google Analytics, angangoita chero mawebhusaiti).
• Unganidza data kubva Files ese makuru emhando dzedata (XLSX, TXT, CSV, JSON, HTML, XML…), ese ari ega uye muhuwandu - kubva kune ese mafaera ari mune yakataurwa folda. Kubva kuExcel workbooks, unogona kurodha otomatiki data kubva pamashiti ese kamwechete.
  
• Tsvaira yakagamuchira data kubva ku "marara": mamwe makoramu kana mitsara, kudzokorora, ruzivo rwesevhisi mu "musoro", nzvimbo dzekuwedzera kana mavara asina kudhindwa, nezvimwe.
• Hunza data mukati urongwa: nyaya chaiyo, nhamba-se-zvinyorwa, zadza mapeji, wedzera iyo chaiyo "cap" yetafura, tsanangura "zvinonamira" zvinyorwa mumakoramu uye wozvinamira kumashure, patsanura zuva kuita zvikamu, nezvimwe.
  
• munzira dzose dzinobvira rokuchinja matafura, achivaunza mune yaidiwa fomu (sefa, rongedza, shandura marongero emakoramu, transpose, wedzera ma total, wedzera matafura emuchinjiko kuti flat uye kudonha kumashure).
• Tsiva data kubva kune imwe tafura kuenda kune imwe nekufananidza imwe kana kupfuura paramita, kureva yakanaka yekutsiva basa VPR (VLOOKUP) uye analogues ayo.

Power Query inowanikwa mushanduro mbiri: sekuwedzera kwakasiyana kweExcel 2010-2013, iyo inogona kutorwa kubva pawebhusaiti yeMicrosoft yepamutemo, uye sechikamu cheExcel 2016. Muchiitiko chekutanga, mushure mekuiswa, tabhu yakaparadzana inoonekwa mukati Excel:

MuExcel 2016, mashandiro ese eSimba Query akatovakwa mukati nekusarudzika uye ari patebhu Data (Zuva) seboka Tora uye shandura (Tora & Shandura):

Mikana yezvisarudzo izvi zvakafanana zvachose.

Chinhu chakakosha chePower Query ndechekuti zviito zvese zvekupinza nekushandura data zvinochengetwa muchimiro chemubvunzo - kutevedzana kwematanho mumutauro wemukati Power Query programming mutauro, unonzi muchidimbu "M". Matanho anogona kugara achigadziriswa uye kudzokororwa chero nhamba yenguva (mubvunzo wekuvandudza).

Iyo huru Power Query hwindo kazhinji inotaridzika seizvi:

Sekuona kwangu, iyi ndiyo inonyanya kukosha yekuwedzera yakanyorwa muchinyorwa chino kune vakasiyana-siyana vashandisi. Mazhinji emabasa awaifanira kukanganisa zvakanyanya nemafomula kana kunyora macros ave nyore uye anoyevedza kuitwa muPower Query. Ehe, uye nekuzotevera otomatiki kugadzirisa kwemhedzisiro. Uye uchifunga kuti ndeyemahara, maererano nemutengo-mhando reshiyo, Power Query ingori kunze kwemakwikwi uye mhedziso inofanirwa-kuve nayo kune chero yepakati-yepamberi Excel mushandisi mazuva ano.

powerpivot

Power Pivot zvakare yekuwedzera-mukati yeMicrosoft Excel, asi yakagadzirirwa mabasa akasiyana zvishoma. Kana Power Query yakatarisana nekupinza uye kugadzirisa, ipapo Power Pivot inodiwa zvakanyanya pakuongorora kwakaoma kwehuwandu hwedata. Seyekutanga kufungidzira, iwe unogona kufunga nezveSimba Pivot seye fancy pivot tafura.

Iwo misimboti yekushanda muPower Pivot ndeaya anotevera:

1. Isu tisu vekutanga kurodha data muSimba Pivot - gumi nemashanu masosi akasiyana anotsigirwa: akajairwa dhatabhesi (SQL, Oracle, Access ...), Excel mafaera, mameseji mafaera, data feeds. Mukuwedzera, iwe unogona kushandisa Power Query se data sosi, izvo zvinoita kuti kuongororwa kunenge omnivorous.
2. Zvadaro pakati pematafura akatakura ma connections anogadziriswa kana, sezvavanotaura, yakasikwa Data Model. Izvi zvichabvumira mune ramangwana kuvaka mishumo pane chero minda kubva kumatafura aripo sekunge iri tafura imwe. Uye hapana VPR zvakare.
3. Kana zvichidikanwa, mamwe maverengero anowedzerwa kuData Model uchishandisa akaverengerwa columns (yakafanana nekoramu ine mafomula mu "smart tafura") uye matanho (analogue yendima yakaverengerwa muchidimbu). Zvese izvi zvakanyorwa mune yakakosha Power Pivot mutauro wemukati unonzi DAX (Data Analysis eXpressions).
4. Papepa reExcel, maererano neData Model, mishumo yekufarira kwatiri inovakwa mufomu pivot matafura uye madhayagiramu.

Iyo huru Power Pivot hwindo inotaridzika seizvi:

Uye iyi ndiyo nzira iyo Data Model inotaridzika, kureva matafura akaremerwa ane hukama hwakagadzirwa:

Power Pivot ine akati wandei maficha anoita kuti ive yakasarudzika chishandiso kune mamwe mabasa:

• Mune Simba Pivot hapana muganhu wemutsara (sezviri muExcel). Iwe unogona kurodha matafura emhando ipi neipi uye nyore kushanda navo.
  
• Power Pivot yakanaka kwazvo compress data pakuvaisa muModel. Iyo 50MB yepakutanga mavara faira anogona nyore kushanduka kuita 3-5MB mushure mekurodha.
• Sezvo "pasi pehodhi" Power Pivot, chokwadi, ine yakazara-yakazara dhatabhesi injini, inotarisana nehuwandu hukuru hweruzivo. nekukurumidza kwazvo. Unoda kuongorora 10-15 miriyoni zvinyorwa uye kuvaka pfupiso? Uye zvese izvi palaptop yekare? Hapana dambudziko!

Nehurombo, Power Pivot haisati yabatanidzwa mune ese mavhezheni eExcel. Kana iwe uine Excel 2010, unogona kuidhawunirodha mahara kubva kuMicrosoft webhusaiti. Asi kana iwe uine Excel 2013-2016, saka zvese zvinoenderana nerezinesi rako, nekuti. mune dzimwe shanduro inosanganiswa (Office Pro Plus, somuenzaniso), uye mune dzimwe haisi (Office 365 Home, Office 365 Personal, etc.) Unogona kuverenga zvakawanda pamusoro peizvi pano.

Power Mepu

Iyi yekuwedzera yakatanga kuoneka muna 2013 uye yakatanga kunzi GeoFlow. Inoitirwa kuonesa geo-data, kureva ruzivo rwenhamba pamamepu enzvimbo. Iyo yekutanga data yekuratidzira inotorwa kubva kune imwecheteyo Power Pivot Data Model (ona ndima yapfuura).

Iyo demo vhezheni yeSimba Mepu (inenge isina kusiyana neiyo yakazara, nenzira) inogona kutorwa zvachose pasina muripo zvakare kubva kuMicrosoft webhusaiti. Iyo yakazara vhezheni inosanganisirwa mune mamwe Microsoft Office 2013-2016 mapakeji pamwe nePower Pivot - muchimiro chebhatani. 3D mepu Tab Insert (Isa - 3D-mepu):

Mamiriro akakosha ePower Mepu:

• Mepu inogona kunge yakatsetseka uye yakazara (globe).
• Unogona kushandisa akawanda akasiyana kuona mhando (histograms, bubble charts, kupisa mepu, nzvimbo inozadza).
• Iwe unogona kuwedzera kuyera nguva, kureva kuti raramai maitiro uye muone achikura.
  
• Mamepu anoiswa kubva kusevhisi Bing Maps, kureva kuti Unoda internet inokurumidza kubatanidza kuti uone. Dzimwe nguva kune matambudziko nekuzivikanwa kwekero, nekuti. mazita ari mudata haawanzoenderana neaya eBing Mepu.
  
• Mune yakazara (isina-demo) vhezheni yeSimba Mepu, unogona kushandisa yako kudhawunirodha mepu, semuenzaniso, kufungidzira vashanyi kune nzvimbo yekutengesa kana mitengo yemafurati muchivako chekugara pane chirongwa chekuvaka.
• Zvichienderana neyakagadzirwa geo-kuona, unogona kugadzira mavhidhiyo zvakananga muPower Mepu (muenzaniso) kuti ugovane gare gare nevaya vasina iyo yekuwedzera-mukati yakaiswa kana kuvasanganisa mune Power Point mharidzo.
  

Power view

Kutanga kuunzwa muExcel 2013, iyi yekuwedzera-mukati yakagadzirirwa kuunza data rako kuhupenyu nemagirafu anopindirana, machati, mepu, uye matafura. Dzimwe nguva mazwi anoshandiswa kune izvi. dashboard (dashboard) or dashboard (scorecard). Chinokosha ndechekuti iwe unogona kuisa pepa rakakosha risina masero muExcel faira yako - Power View slide, kwaunogona kuwedzera zvinyorwa, mapikicha uye akawanda emhando dzakasiyana-siyana dzekuona zvichienderana nedata rako kubva kuPower Pivot Data Model.

Ichatarisa chimwe chinhu seichi:

Iwo nuances apa ndeaya:

• Iyo data yekutanga inotorwa kubva panzvimbo imwechete - kubva kuPower Pivot Data Model.
• Kuti ushande nePower View, unofanirwa kuisa Silverlight pakombuta yako - Microsoft's analogue yeFlash (yemahara).
  

Pawebhusaiti yeMicrosoft, nenzira, kune kosi yekudzidziswa yakanaka kwazvo paPower View mu.

Simba BI

Kusiyana neaya apfuura, Power BI haisi yekuwedzera yeExcel, asi chigadzirwa chakasiyana, inova seti yakazara yezvishandiso zvekuongorora bhizinesi uye kuona. Inosanganisira zvinhu zvitatu zvakakosha:

1. Simba BI Desktop - chirongwa chekuongorora nekuona data, iyo inosanganisira, pakati pezvimwe zvinhu, kushanda kwese kwePower Query uye Power Pivot add-ons + yakagadziridzwa maitiro ekuona kubva kuPower View uye Power Mepu. Unogona kudhawunirodha nekuiisa mahara kubva kuMicrosoft webhusaiti.

MuPower BI Desktop unogona:

• Rodha data kubva kumusoro 70 zvinyorwa zvakasiyana (semuna Power Query + mamwe majoini).
• kusunga matafura ekuenzanisira (semuPower Pivot)
• Wedzera mamwe maverengero kune data ne matanho и makoramu akaverengerwa paDAX (sezviri muPower Pivot)
• Gadzira yakanaka data yakavakirwa interactive mishumo nemhando dzakasiyana dzekuona (zvakanyanya kufanana nePower View, asi zvirinani uye zvine simba zvakanyanya).
• Publish yakagadzira mishumo pane iyo Power BI Service saiti (ona inotevera poindi) uye ugovane nevamwe vaunoshanda navo. Uyezve, zvinokwanisika kupa kodzero dzakasiyana (kuverenga, kugadzirisa) kune vanhu vakasiyana.
  

2. Simba BI online service - kutaura zviri nyore, ino inzvimbo iyo iwe nemushandisi wega wega mukambani yako muchava ne "sandbox" yavo (nzvimbo yebasa) kwaunogona kurodha mishumo yakagadzirwa muPower BI Desktop. Pamusoro pekuona, inotokubvumidza kuti uzvigadzirise, kuburitsa kanenge ese mashandiro ePower BI Desktop online. Iwe unogona zvakare kukwereta zviratidziro zvemunhu kubva kune vamwe vanhu mishumo pano, uchitora wako wega madhibhodhi kubva kwavari.

Zvinotaridzika seizvi:

3. Simba BI Mobile Chishandiso cheIOS / Android / Windows chekubatanidza kuPower BI Service uye kuona zviri nyore (kwete kugadzirisa) mishumo yakagadzirwa uye dashibhodhi chaipo pachiratidziro chefoni kana piritsi rako. Unogona kuirodha (zvemahara zvachose) pano.

Pa iPhone, semuenzaniso, chirevo chakagadzirwa pamusoro chinotaridzika seizvi:

Uye zvese izvi uchichengetedza kupindirana uye animation + kusungwa nekubata uye kudhirowa pachiratidziro nepeni. Zvakanaka kwazvo. Nekudaro, hungwaru hwebhizinesi hunowanikwa kune vese vanhu vakakosha vekambani chero nguva uye munzvimbo chero ipi zvayo - chete kuwana Internet kunodiwa.

Power BI mitengo yezvirongwa. Power BI Desktop uye Nharembozha dzakasununguka kunze kwebhokisi, uye mazhinji Power BI Service maficha akasununguka zvakare. Saka kuti ushandise wega kana kushandisa mukati mekambani diki, haufanirwe kubhadhara peni kune zvese zviri pamusoro uye unogona kugara wakachengeteka pachirongwa. Free. Kana iwe uchida kugovera mishumo nevaunoshanda navo uye kutonga kodzero dzavo dzekuwana, uchafanirwa kuenda BESS ($10 pamwedzi pamushandisi). Pane zvimwe Premium - kumakambani makuru (> 500 vashandisi) inoda yakaparadzana kuchengetedza uye sevha hunyanzvi hwedata.

• Project Gantt Chati muExcel ine Simba Query
• Maitiro ekugadzira dhatabhesi muExcel uchishandisa Power Pivot
• Kuona mafambiro munzira pamepu muPower Mepu