Kodi Power Query / Pivot / Map / View / BI ndi chiyani ndipo chifukwa chiyani amafunikira wogwiritsa ntchito Excel

Mawu akuti "Power Query", "Power Pivot", "Power BI" ndi "mphamvu" zina zikuchulukirachulukira m'nkhani ndi zida za Microsoft Excel. Muzochitika zanga, si aliyense amene amamvetsetsa bwino zomwe zili kumbuyo kwa mfundozi, momwe zimagwirizanirana komanso momwe angathandizire wogwiritsa ntchito Excel wosavuta.

Tiyeni tifotokoze bwino mmene zinthu zilili.

Kufunsa Mphamvu

Kubwerera mu 2013, gulu lopangidwa mwapadera la opanga mkati mwa Microsoft lidatulutsa chowonjezera chaulere cha Excel. Kufunsa Mphamvu (mayina ena ndi Data Explorer, Get & Transform), omwe amatha kuchita zinthu zambiri zothandiza pantchito yatsiku ndi tsiku:

• Kwezani deta mu Excel kuchokera pafupifupi 40 magwero osiyanasiyana, kuphatikizapo nkhokwe (SQL, Oracle, Access, Teradata…), makampani ERP machitidwe (SAP, Microsoft Dynamics, 1C…), ntchito Intaneti (Facebook, Google Analytics, pafupifupi Websites aliyense ).
• Sungani deta kuchokera owona mitundu yonse yayikulu ya data (XLSX, TXT, CSV, JSON, HTML, XML…), zonse payekha komanso zambiri - kuchokera pamafayilo onse omwe ali mufoda yomwe yatchulidwa. Kuchokera m'mabuku ogwirira ntchito a Excel, mutha kutsitsa zokha pamapepala onse nthawi imodzi.
  
• Konza adalandira deta kuchokera ku "zinyalala": mizati yowonjezera kapena mizere, kubwereza, zambiri zautumiki mu "mutu", malo owonjezera kapena zilembo zosasindikizidwa, ndi zina zotero.
• Bweretsani deta mu dongosolo: zolondola, manambala-monga-malemba, lembani mipata, onjezani "kapu" yoyenera patebulo, perekani mawu oti "chomata" m'mizati ndikumatanso, gawani tsikulo kukhala zigawo, ndi zina zotero.
  
• m'njira iliyonse yotheka kusandutsa Matebulo, kuwabweretsa mu mawonekedwe omwe mukufuna (sefa, sinthani, sinthani dongosolo la mizati, sinthani, onjezerani ziwopsezo, onjezerani matebulo opingasa kuti aphwanye ndi kugweranso).
• Sinthani deta kuchokera ku tebulo kupita ku lina pofananiza magawo amodzi kapena angapo, mwachitsanzo, ntchito yabwino yosinthira VPR (VLOOKUP) ndi analogues ake.

Power Query imapezeka m'matembenuzidwe awiri: monga chowonjezera chosiyana cha Excel 2010-2013, chomwe chikhoza kumasulidwa kuchokera ku webusaiti ya Microsoft yovomerezeka, komanso monga gawo la Excel 2016. Poyamba, pambuyo pa kukhazikitsa, tabu yosiyana ikuwonekera Excel:

Mu Excel 2016, magwiridwe antchito onse a Power Query adamangidwa kale mwachisawawa ndipo ali pa tabu Deta (Tsiku) monga gulu Pezani ndikusintha (Pezani & Sintha):

Mwayi wa zosankhazi ndizofanana kwathunthu.

Chofunika kwambiri cha Power Query ndi chakuti zochita zonse zoitanitsa ndikusintha deta zimasungidwa mu mawonekedwe a funso - ndondomeko yotsatizana muchinenero chamkati cha Power Query, chomwe chimatchedwa "M". Masitepe amatha kusinthidwa ndikuseweredwanso kangapo konse (funso lotsitsimutsa).

Zenera lalikulu la Power Query nthawi zambiri limawoneka motere:

M'malingaliro anga, izi ndizowonjezera zowonjezera zomwe zalembedwa m'nkhaniyi kwa ogwiritsa ntchito osiyanasiyana. Ntchito zambiri zomwe mumayenera kuzipotoza moyipa ndi mafomula kapena kulemba ma macros tsopano zachitika mosavuta komanso mokongola mu Power Query. Inde, komanso ndikusintha kotsatira kwazotsatira. Ndipo poganizira kuti ndi zaulere, malinga ndi kuchuluka kwa mtengo, Power Query yangotuluka pampikisano ndipo mtheradi uyenera kukhala nawo kwa aliyense wogwiritsa ntchito wapakatikati wa Excel masiku ano.

mphamvu pivot

Power Pivot ndi chowonjezera cha Microsoft Excel, koma chopangidwira ntchito zosiyana pang'ono. Ngati Power Query ikuyang'ana pa kuitanitsa ndi kukonza, ndiye kuti Power Pivot ndiyofunikira makamaka pakusanthula kovuta kwa deta yambiri. Monga kuyerekezera koyamba, mutha kuganiza za Power Pivot ngati tebulo lapamwamba la pivot.

Mfundo zazikuluzikulu zogwirira ntchito mu Power Pivot ndi izi:

1. Ndife oyamba kutsitsa deta mu Power Pivot - magawo 15 osiyanasiyana amathandizidwa: nkhokwe wamba (SQL, Oracle, Access ...), mafayilo a Excel, mafayilo amawu, ma feed a data. Kuphatikiza apo, mutha kugwiritsa ntchito Power Query ngati gwero la data, zomwe zimapangitsa kusanthula kukhala pafupifupi omnivorous.
2. Ndiye pakati pa matebulo odzaza maulumikizidwe amapangidwa kapena, monga akunena, adalengedwa Chitsanzo cha Data. Izi zidzalola m'tsogolo kupanga malipoti pa minda iliyonse kuchokera ku matebulo omwe alipo monga ngati tebulo limodzi. Ndipo palibe VPR kachiwiri.
3. Ngati ndi kotheka, mawerengedwe owonjezera amawonjezedwa ku Data Model pogwiritsa ntchito mizati yowerengeka (zofanana ndi ndime yokhala ndi mafomu mu "smart table") ndi miyeso (analogue ya gawo lowerengedwa muchidule). Zonsezi zalembedwa m'chinenero chapadera cha Power Pivot chamkati chotchedwa DAX (Data Analysis eXpressions).
4. Pa pepala la Excel, malinga ndi Data Model, malipoti omwe ali ndi chidwi kwa ife amapangidwa mu mawonekedwe matebulo ozungulira ndi zithunzi.

Zenera lalikulu la Power Pivot likuwoneka motere:

Ndipo umu ndi momwe Data Model imawonekera, mwachitsanzo, matebulo onse odzaza ndi maubale opangidwa:

Power Pivot ili ndi zinthu zingapo zomwe zimapangitsa kuti ikhale chida chapadera pantchito zina:

• Mu Power Pivot palibe malire a mzere (monga mu Excel). Mutha kukweza matebulo amtundu uliwonse ndikugwira nawo ntchito mosavuta.
  
• Power Pivot ndiyabwino kwambiri compress data powatsitsa mu Model. Fayilo yoyambirira ya 50MB imatha kusintha kukhala 3-5MB ikatsitsa.
• Popeza "pansi pa hood" Power Pivot, kwenikweni, ili ndi injini ya database yathunthu, imalimbana ndi zidziwitso zambiri. mwachangu kwambiri. Mukufuna kusanthula zolemba 10-15 miliyoni ndikupanga chidule? Ndipo zonsezi pa laputopu yakale? Palibe vuto!

Tsoka ilo, Power Pivot sinaphatikizidwebe m'mitundu yonse ya Excel. Ngati muli ndi Excel 2010, mutha kutsitsa kwaulere patsamba la Microsoft. Koma ngati muli ndi Excel 2013-2016, ndiye zonse zimatengera chilolezo chanu, chifukwa. m'matembenuzidwe ena amaphatikizidwa (Office Pro Plus, mwachitsanzo), ndipo ena sali (Office 365 Home, Office 365 Personal, etc.) Mukhoza kuwerenga zambiri za izi apa.

Mapu a Mphamvu

Zowonjezera izi zidawonekera koyamba mu 2013 ndipo poyambirira zidatchedwa GeoFlow. Amapangidwa kuti aziwonetsa za geo-data, mwachitsanzo, manambala pamapu a malo. Zomwe zimayambira zowonetsera zimatengedwa ku Power Pivot Data Model yomweyo (onani ndime yapitayi).

Mtundu wa chiwonetsero cha Power Map (pafupifupi sichosiyana ndi chathunthu, mwa njira) utha kutsitsidwanso kwaulere patsamba la Microsoft. Mtundu wathunthu umaphatikizidwa ndi phukusi la Microsoft Office 2013-2016 pamodzi ndi Power Pivot - mu mawonekedwe a batani Mapu a 3D tsamba Ikani (Lowetsani - 3D-mapu):

Zofunikira za Power Map:

• Maps ikhoza kukhala yosalala komanso yozungulira (globe).
• Mukhoza kugwiritsa ntchito zosiyanasiyana mitundu yowonera (ma histogram, matchati a nthibwibwibwi, mamapu otentha, kudzaza madera).
• Mutha kuwonjezera kuyeza nthawi, mwachitsanzo, yambitsani ndondomekoyi ndikuwona ikukula.
  
• Mamapu adakwezedwa kuchokera kuntchito Mapu a Bing, mwachitsanzo, Mufunika intaneti yothamanga kwambiri kuti muwone. Nthawi zina pamakhala zovuta kuzindikira ma adilesi olondola, chifukwa. mayina omwe ali mu data safanana nthawi zonse ndi mamapu a Bing.
  
• Mumtundu wathunthu (wopanda demo) wa Power Map, mutha kugwiritsa ntchito yanu mapu otsitsidwa, mwachitsanzo, kuti muwone m'maso mwa alendo obwera ku malo ogulitsira kapena mitengo yazipinda zogona m'nyumba yomwe ili pa pulani yomanga.
• Kutengera ndi mawonekedwe a geo-visualizations, mutha kupanga makanema mwachindunji mu Power Map (chitsanzo) kuti muwagawire pambuyo pake ndi omwe alibe chowonjezera kapena muphatikizepo mu Power Point.
  

mawonekedwe amphamvu

Yoyamba idayambitsidwa mu Excel 2013, chowonjezerachi chapangidwa kuti chikhale ndi moyo ndi ma graph, ma chart, mamapu, ndi matebulo. Nthawi zina mawuwa amagwiritsidwa ntchito pa izi. lakutsogolo (dashboard) or lakutsogolo (chikhadi). Mfundo yofunika kwambiri ndi yakuti mukhoza kuyika pepala lapadera lopanda maselo mu fayilo yanu ya Excel - Power View slide, komwe mungathe kuwonjezera malemba, zithunzi ndi mitundu yambiri ya maonekedwe okhudzana ndi deta yanu kuchokera ku Power Pivot Data Model.

Zikuwoneka ngati izi:

Ma nuances apa ndi awa:

• Deta yoyamba imatengedwa kuchokera kumalo omwewo - kuchokera ku Power Pivot Data Model.
• Kuti mugwire ntchito ndi Power View, muyenera kukhazikitsa Silverlight pa kompyuta yanu - analogue ya Microsoft ya Flash (yaulere).
  

Pa tsamba la Microsoft, mwa njira, pali maphunziro abwino kwambiri pa Power View mu .

Mphamvu BI

Mosiyana ndi zam'mbuyomu, Power BI si chowonjezera cha Excel, koma chinthu chosiyana, chomwe ndi zida zonse zowunikira bizinesi ndikuwonera. Lili ndi zinthu zitatu zofunika:

1. Power BI Desktop - pulogalamu yowunikira ndikuwona deta, yomwe imaphatikizapo, mwa zina, ntchito zonse za Power Query ndi Power Pivot zowonjezera + njira zowonetsera bwino kuchokera ku Power View ndi Power Map. Mutha kutsitsa ndikuyiyika kwaulere patsamba la Microsoft.

Mu Power BI Desktop mutha:

• Kwezani deta kuchokera pamwamba 70 magwero osiyanasiyana (monga mu Power Query + zolumikizira zowonjezera).
• kumanga matebulo oti muwonetsere (monga mu Power Pivot)
• Onjezani mawerengedwe owonjezera ku data ndi miyeso и mizati yowerengera pa DAX (monga mu Power Pivot)
• Pangani zokongola deta zochokera malipoti oyankhulana ndi mitundu yosiyanasiyana yowonera (yofanana kwambiri ndi Power View, koma yabwinoko komanso yamphamvu kwambiri).
• kufalitsa adapanga malipoti patsamba la Power BI Service (onani mfundo yotsatira) ndikugawana ndi anzawo. Komanso, ndizotheka kupereka maufulu osiyanasiyana (kuwerenga, kukonza) kwa anthu osiyanasiyana.
  

2. Mphamvu BI pa intaneti utumiki - kunena mophweka, iyi ndi malo omwe inu ndi aliyense wogwiritsa ntchito kampani yanu mudzakhala ndi "sandbox" (malo ogwirira ntchito) omwe mungathe kukweza malipoti opangidwa mu Power BI Desktop. Kuphatikiza pakuwona, kumakupatsani mwayi wowasintha, kupanganso pafupifupi magwiridwe antchito onse a Power BI Desktop pa intaneti. Mutha kubwerekanso zowonera zanu kuchokera kumalipoti a anthu ena pano, kusonkhanitsa ma dashboards a olemba anu kuchokera kwa iwo.

Zikuwoneka motere:

3. Mphamvu BI Mobile ndi pulogalamu ya iOS / Android / Windows yolumikizana ndi Power BI Service ndikuwona mosavuta (osasintha) malipoti opangidwa ndi ma dashboard omwe ali pazenera la foni kapena piritsi yanu. Mukhoza kukopera (kwaulere kwathunthu) apa.

Pa iPhone, mwachitsanzo, lipoti lomwe lapangidwa pamwambapa likuwoneka motere:

Ndipo zonsezi ndikusunga kuyanjana ndi makanema ojambula + kumangidwa chifukwa chogwira ndi kujambula pazenera ndi cholembera. Momasuka kwambiri. Chifukwa chake, nzeru zamabizinesi zimapezeka kwa anthu onse ofunika pakampani nthawi iliyonse komanso malo aliwonse - intaneti yokha ndiyofunikira.

Malingaliro a magawo a Power BI. Power BI Desktop ndi Mobile ndi zaulere m'bokosi, ndipo zambiri za Power BI Service ndi zaulere nazonso. Chifukwa chake kuti mugwiritse ntchito nokha kapena mugwiritse ntchito pakampani yaying'ono, simuyenera kulipira kakobiri pazonse zomwe zili pamwambapa ndipo mutha kukhalabe papulaniyo. Free. Ngati mukufuna kugawana malipoti ndi anzanu ndikuwongolera ufulu wawo wopeza, muyenera kupita BESS ($ 10 pamwezi pa wogwiritsa ntchito). Pali zinanso umafunika - kwa makampani akuluakulu (> ogwiritsa ntchito 500) omwe amafunikira kusungirako kosiyana ndi ma seva a data.

• Tchati cha Project Gantt mu Excel ndi Power Query
• Momwe mungapangire database mu Excel pogwiritsa ntchito Power Pivot
• Kuwona mayendedwe panjira pamapu mu Power Map