Cov ntsiab lus "Power Query", "Power Pivot", "Power BI" thiab lwm yam "powers" tau nce zuj zus hauv cov ntawv thiab cov ntaub ntawv hais txog Microsoft Excel. Hauv kuv qhov kev paub dhau los, tsis yog txhua tus neeg nkag siab meej txog dab tsi nyob tom qab cov ntsiab lus no, lawv sib cuam tshuam li cas thiab lawv tuaj yeem pab tus neeg siv Excel yooj yim li cas.
Cia peb qhia qhov xwm txheej.
Lub Zog Lus Nug
Rov qab rau xyoo 2013, ib pab pawg tsim tshwj xeeb ntawm cov neeg tsim khoom hauv Microsoft tau tso tawm qhov pub dawb ntxiv rau Excel. Lub Zog Lus Nug (lwm lub npe yog Data Explorer, Tau & Hloov), uas tuaj yeem ua ntau yam tseem ceeb rau kev ua haujlwm txhua hnub:
- upload cov ntaub ntawv hauv Excel los ntawm yuav luag 40 qhov sib txawv, suav nrog cov databases (SQL, Oracle, Access, Teradata…), koom nrog ERP systems (SAP, Microsoft Dynamics, 1C…), Internet kev pabcuam (Facebook, Google Analytics, yuav luag txhua lub vev xaib ).
- Sau cov ntaub ntawv los ntawm Cov ntaub ntawv tag nrho cov ntaub ntawv loj (XLSX, TXT, CSV, JSON, HTML, XML…), ob leeg ib leeg thiab ntau - los ntawm tag nrho cov ntaub ntawv hauv cov ntawv teev tseg. Los ntawm Excel phau ntawv ua haujlwm, koj tuaj yeem rub tawm cov ntaub ntawv los ntawm txhua nplooj ntawv ib zaug.
- So huv si tau txais cov ntaub ntawv los ntawm "khoom khib nyiab": cov kab ntxiv lossis kab, rov ua dua, cov ntaub ntawv kev pabcuam hauv "header", qhov chaw ntxiv lossis cov cim tsis tau luam tawm, thiab lwm yam.
- Nqa cov ntaub ntawv rau hauv kev txiav txim: Cov ntaub ntawv raug, cov lej-raws li cov ntawv sau, sau qhov khoob, ntxiv qhov tseeb "cap" ntawm lub rooj, cais cov ntawv "nplaum" rau hauv txhua kab thiab muab nws rov qab, faib hnub rau hauv cov khoom, thiab lwm yam.
- nyob rau hauv txhua txoj kev ua tau txia cov ntxhuav, coj lawv mus rau hauv daim ntawv xav tau ( lim, cais, hloov qhov kev txiav txim ntawm txhua kab, transpose, ntxiv tag nrho, nthuav cov rooj hla mus rau lub tiaj thiab rov qab).
- Hloov cov ntaub ntawv los ntawm ib lub rooj mus rau lwm qhov los ntawm kev sib piv ib lossis ntau qhov tsis sib xws, piv txwv li kev hloov pauv zoo VPR (VLOOKUP) thiab nws cov analogues.
Power Query muaj nyob rau hauv ob lub versions: raws li ib tug cais add-in rau Excel 2010-2013, uas yuav tsum downloaded los ntawm lub official Microsoft lub website, thiab raws li ib feem ntawm Excel 2016. Thawj kis, tom qab installation, ib tug cais tab tshwm nyob rau hauv Excel:
Hauv Excel 2016, tag nrho cov haujlwm ntawm Lub Hwj Chim Query twb tau tsim los ntawm lub neej ntawd thiab nyob rau ntawm tab Cov ntaub ntawv (Hnub) raws li ib pawg Tau txais thiab hloov pauv (Tau & Hloov):
Qhov ua tau ntawm cov kev xaiv no zoo ib yam nkaus.
Lub hauv paus tseem ceeb ntawm Lub Hwj Chim Query yog tias txhua qhov kev ua rau kev xa tawm thiab hloov cov ntaub ntawv raug khaws cia hauv daim ntawv nug - ib ntu ntawm cov kauj ruam hauv cov lus nug hauv Power Query programming, uas yog hu ua "M". Cov kauj ruam ib txwm tuaj yeem hloov kho thiab rov ua dua txhua lub sijhawm (cov lus nug rov qab).
Lub ntsiab Power Query window feem ntau zoo li no:
Hauv kuv lub tswv yim, qhov no yog qhov tseem ceeb tshaj plaws add-on teev nyob rau hauv tsab xov xwm no rau ntau tus neeg siv. Ntau yam dej num uas koj yuav tsum tau ua phem heev nrog cov qauv lossis sau macros tam sim no yooj yim thiab zoo nkauj ua tiav hauv Power Query. Yog, thiab nrog kev hloov kho tsis siv neeg tom qab ntawm cov txiaj ntsig. Thiab txiav txim siab nws yog dawb, hais txog tus nqi-zoo piv, Lub Hwj Chim Query tsuas yog tawm ntawm kev sib tw thiab qhov yuav tsum muaj rau txhua tus neeg siv Excel nruab nrab hnub no.
Powerpivot
Power Pivot kuj yog ib qho ntxiv rau Microsoft Excel, tab sis tsim los rau cov hauj lwm sib txawv me ntsis. Yog tias Lub Hwj Chim Query tau tsom mus rau kev xa khoom thiab ua tiav, ces Lub Zog Pivot yog qhov xav tau tshwj xeeb rau kev tsom xam cov ntaub ntawv loj. Raws li kev kwv yees thawj zaug, koj tuaj yeem xav txog Power Pivot li lub rooj pivot zoo nkauj.
Cov ntsiab cai dav dav ntawm kev ua haujlwm hauv Power Pivot yog raws li hauv qab no:
- Peb yog thawj thauj khoom cov ntaub ntawv Hauv Power Pivot - 15 qhov chaw sib txawv tau txais kev txhawb nqa: ntau cov ntaub ntawv (SQL, Oracle, Access ...), Excel cov ntaub ntawv, cov ntawv nyeem, cov ntaub ntawv pub. Tsis tas li ntawd, koj tuaj yeem siv Power Query ua cov ntaub ntawv, uas ua rau kev tsom xam yuav luag txhua yam.
- Ces ntawm loaded rooj kev sib txuas yog configured los yog, raws li lawv hais, yog tsim Cov Qauv Qauv. Qhov no yuav tso cai rau yav tom ntej los tsim cov ntawv ceeb toom ntawm txhua qhov chaw los ntawm cov rooj uas twb muaj lawm zoo li nws yog ib lub rooj. Thiab tsis muaj VPR dua.
- Yog tias tsim nyog, kev suav ntxiv yog ntxiv rau Cov Qauv Cov Ntaub Ntawv siv suav kab (zoo ib yam li kab nrog cov qauv hauv "smart table") thiab ntsuas (ib qho piv txwv ntawm kev suav teb hauv cov ntsiab lus). Tag nrho cov no yog sau nyob rau hauv tshwj xeeb Power Pivot lus sab hauv hu ua DAX (Data Analysis eXpressions).
- Ntawm daim ntawv Excel, raws li Cov Qauv Cov Ntaub Ntawv, cov lus ceeb toom ntawm kev txaus siab rau peb tau tsim hauv daim ntawv piv rooj thiab daim duab.
Lub ntsiab Power Pivot qhov rais zoo li no:
Thiab qhov no yog li cas cov ntaub ntawv qauv zoo li, piv txwv li tag nrho cov rooj thauj khoom nrog tsim kev sib raug zoo:
Power Pivot muaj ntau yam nta uas ua rau nws yog ib qho cuab yeej tshwj xeeb rau qee qhov haujlwm:
- Hauv Power Pivot tsis muaj kab txwv (zoo li hauv Excel). Koj tuaj yeem thauj cov ntxhuav ntawm txhua qhov loj thiab yooj yim ua haujlwm nrog lawv.
- Lub zog Pivot yog qhov zoo heev ntawm compress cov ntaub ntawv thaum thauj lawv rau hauv Model. Ib daim ntawv 50MB thawj cov ntawv tuaj yeem hloov mus rau 3-5MB tom qab rub tawm.
- Txij li thaum "hauv qab lub hood" Lub Hwj Chim Pivot, qhov tseeb, muaj lub tshuab luam ntawv tag nrho, nws tiv nrog ntau cov ntaub ntawv. ceev heev. Yuav tsum tau txheeb xyuas 10-15 lab cov ntaub ntawv thiab tsim cov ntsiab lus? Thiab tag nrho cov no ntawm lub qub laptop? Tsis muaj teeb meem!
Hmoov tsis zoo, Power Pivot tseem tsis tau suav nrog hauv txhua lub versions ntawm Excel. Yog tias koj muaj Excel 2010, koj tuaj yeem rub tawm dawb los ntawm Microsoft lub vev xaib. Tab sis yog tias koj muaj Excel 2013-2016, ces txhua yam nyob ntawm koj daim ntawv tso cai, vim. Hauv qee qhov versions nws suav nrog (Piv txwv li Office Pro Plus), thiab qee qhov nws tsis yog (Office 365 Home, Office 365 Personal, thiab lwm yam) Koj tuaj yeem nyeem ntxiv txog qhov no ntawm no.
Power Maps
Qhov no add-on thawj zaug tshwm sim nyob rau hauv 2013 thiab yog Ameslikas hu ua GeoFlow. Nws yog npaj rau kev pom ntawm cov ntaub ntawv geo, piv txwv li cov ntaub ntawv xov xwm ntawm thaj chaw maps. Cov ntaub ntawv thawj zaug rau zaub yog muab los ntawm tib lub zog Pivot Cov Ntaub Ntawv Qauv (saib kab lus dhau los).
Lub demo version ntawm Power Map (yuav luag tsis txawv ntawm tag nrho ib qho, los ntawm txoj kev) tuaj yeem rub tawm dawb kiag li rov qab los ntawm Microsoft lub vev xaib. Cov ntawv tag nrho yog suav nrog qee qhov Microsoft Office 2013-2016 pob khoom nrog rau Power Pivot - hauv daim ntawv khawm Daim ntawv qhia 3D tab Insert (Ntxig - 3D-daim ntawv qhia):
Cov yam ntxwv tseem ceeb ntawm Power Map:
- maps tuaj yeem ua tau ob qho tib si tiaj tus thiab voluminous (lub ntiaj teb).
- Koj tuaj yeem siv ntau yam sib txawv hom kev pom pom (histograms, daim duab npuas, daim ntawv qhia tshav kub, thaj chaw sau).
- Koj ntxiv tau lub sij hawm ntsuas, piv txwv li animate cov txheej txheem thiab saib nws txhim kho.
- Maps tau thauj khoom los ntawm kev pabcuam Bing Maps, ie Koj xav tau kev sib txuas hauv internet ceev heev los saib. Qee zaum muaj teeb meem nrog qhov tseeb ntawm qhov chaw nyob, vim. cov npe hauv cov ntaub ntawv tsis ib txwm sib xws nrog Bing Maps.
- Hauv tag nrho (tsis yog-demo) version ntawm Power Map, koj tuaj yeem siv koj tus kheej download tau maps, piv txwv li, kom pom cov neeg tuaj saib hauv lub khw lossis cov nqi rau cov tsev nyob hauv ib lub tsev nyob sab xis ntawm lub tsev phiaj xwm.
- Raws li qhov tsim geo-visualizations, koj tuaj yeem tsim cov yeeb yaj kiab ncaj qha hauv Power Map (piv txwv) los qhia lawv tom qab nrog cov uas tsis muaj qhov ntxiv-hauv nruab lossis suav nrog hauv Power Point nthuav qhia.
lub zog saib
Ua ntej tshaj tawm hauv Excel 2013, qhov txuas ntxiv no yog tsim los coj koj cov ntaub ntawv mus rau lub neej nrog cov duab sib tham, kab kos, daim duab qhia chaw, thiab cov ntxhuav. Qee zaum cov lus siv rau qhov no. dashboard (dashboard) or dashboard (scorecard). Cov kab hauv qab yog tias koj tuaj yeem ntxig ib daim ntawv tshwj xeeb yam tsis muaj hlwb rau hauv koj cov ntaub ntawv Excel - Power View swb, qhov twg koj tuaj yeem ntxiv cov ntawv, duab thiab ntau ntau hom kev pom raws li koj cov ntaub ntawv los ntawm Power Pivot Data Model.
Nws yuav zoo li tej yam zoo li no:
Cov nuances ntawm no yog:
- Thawj cov ntaub ntawv yog muab los ntawm tib qhov chaw - los ntawm Power Pivot Data Model.
- Txhawm rau ua haujlwm nrog Power View, koj yuav tsum nruab Silverlight ntawm koj lub computer - Microsoft lub analogue ntawm Flash (dawb).
Ntawm Microsoft lub vev xaib, los ntawm txoj kev, muaj kev cob qhia zoo heev ntawm Power View hauv .
Lub hwj chim BI
Tsis zoo li cov yav dhau los, Power BI tsis yog ib qho ntxiv rau Excel, tab sis yog cov khoom sib cais, uas yog tag nrho cov cuab yeej rau kev lag luam tsom xam thiab kev pom. Nws muaj peb lub ntsiab lus tseem ceeb:
1. Fais fab BI Desktop - ib qho kev pab cuam rau kev txheeb xyuas thiab pom cov ntaub ntawv, uas suav nrog, ntawm lwm yam, tag nrho cov haujlwm ntawm Lub Hwj Chim Query thiab Power Pivot add-ons + txhim kho kev pom kev pom los ntawm Power View thiab Power Map. Koj tuaj yeem rub tawm thiab nruab nws dawb los ntawm Microsoft lub vev xaib.
Hauv Power BI Desktop koj tuaj yeem:
- Load cov ntaub ntawv los ntawm tshaj 70 txawv qhov chaw (raws li hauv Power Query + cov khoom txuas ntxiv).
- khi rooj rau qauv (xws li hauv Power Pivot)
- Ntxiv kev suav ntxiv rau cov ntaub ntawv nrog ntsuas и suav cov kab hauv DAX (raws li hauv Power Pivot)
- Tsim cov ntaub ntawv zoo nkauj raws li sib tham sib qhia nrog ntau hom kev pom (zoo ib yam li Power View, tab sis txawm zoo dua thiab muaj zog dua).
- luam tawm tsim cov ntawv ceeb toom ntawm Lub Chaw Pabcuam Power BI (saib cov ntsiab lus tom ntej) thiab qhia rau cov npoj yaig. Ntxiv mus, nws muaj peev xwm muab cov cai sib txawv (nyeem, kho) rau cov neeg sib txawv.
2. Fais fab BI kev pabcuam online - muab tso rau nws yooj yim, qhov no yog qhov chaw uas koj thiab txhua tus neeg siv hauv koj lub tuam txhab yuav muaj lawv tus kheej "sandbox" (chaw ua haujlwm) uas koj tuaj yeem xa cov ntawv ceeb toom tsim hauv Power BI Desktop. Ntxiv rau kev saib, nws tseem tso cai rau koj los kho lawv, rov tsim dua yuav luag txhua qhov haujlwm ntawm Power BI Desktop online. Koj tseem tuaj yeem qiv qhov kev pom ntawm tus kheej los ntawm lwm tus neeg cov lus ceeb toom ntawm no, sau koj tus kheej tus kws sau ntawv lub dashboards los ntawm lawv.
Nws zoo li qhov no:
3. Fais fab BI Mobile yog ib daim ntawv thov rau iOS / Android / Windows rau txuas rau Power BI Service thiab yooj yim saib (tsis kho) cov ntawv ceeb toom tsim thiab dashboards txoj cai ntawm lub vijtsam ntawm koj lub xov tooj lossis ntsiav tshuaj. Koj tuaj yeem rub tawm nws (dawb tag nrho) ntawm no.
Ntawm iPhone, piv txwv li, tsab ntawv ceeb toom tsim tawm saum toj no zoo li no:
Thiab tag nrho cov no thaum tswj kev sib cuam tshuam thiab cov animation + raug kaw rau kov thiab kos duab ntawm lub vijtsam nrog tus cwj mem. Yooj yim heev. Yog li, kev txawj ntse kev lag luam yuav muaj rau txhua tus neeg tseem ceeb ntawm lub tuam txhab txhua lub sijhawm thiab nyob rau txhua qhov chaw - tsuas yog siv Internet xwb.
Power BI pricing plan. Power BI Desktop thiab Mobile yog dawb tawm ntawm lub thawv, thiab feem ntau Power BI Service nta yog dawb ib yam nkaus. Yog li kev siv tus kheej lossis siv hauv lub tuam txhab me me, koj tsis tas yuav them nyiaj rau tag nrho cov saum toj no thiab koj tuaj yeem nyob nyab xeeb ntawm txoj kev npaj. dawb. Yog tias koj xav qhia cov lus ceeb toom nrog cov npoj yaig thiab tswj hwm lawv txoj cai nkag, koj yuav tsum mus rau BESS ($ 10 toj ib hlis rau ib tus neeg siv). Puas muaj ib co ntxiv hwm - rau cov tuam txhab loj (> 500 cov neeg siv) uas xav tau kev khaws cia thiab server muaj peev xwm rau cov ntaub ntawv.
- Project Gantt Chart hauv Excel nrog Lub Zog Query
- Yuav ua li cas los tsim ib lub database hauv Excel siv Power Pivot
- Kev pom kev txav mus los ntawm txoj kev ntawm daim ntawv qhia hauv Power Map