Menene Query Query / Pivot / Map / View / BI kuma me yasa suke buƙatar mai amfani da Excel

Sharuɗɗan "Query Power", "Power Pivot", "Power BI" da sauran "ikon" suna ƙara fitowa a cikin labarai da kayan aiki game da Microsoft Excel. A cikin kwarewata, ba kowa ba ne ya fahimci abin da ke bayan waɗannan ra'ayoyin, yadda suke haɗuwa da kuma yadda za su iya taimakawa mai amfani da Excel mai sauƙi.

Mu fayyace lamarin.

Tambayar .arfi

Komawa cikin 2013, ƙungiyar masu haɓakawa ta musamman da aka ƙirƙira a cikin Microsoft sun fitar da ƙari kyauta don Excel. Tambayar .arfi (wasu sunaye sune Data Explorer, Get & Transform), wanda zai iya yin abubuwa da yawa masu amfani ga aikin yau da kullum:

• Upload bayanai a cikin Excel daga kusan tushe daban-daban 40, gami da bayanan bayanai (SQL, Oracle, Access, Teradata…), tsarin ERP na kamfani (SAP, Microsoft Dynamics, 1C…), Ayyukan Intanet (Facebook, Google Analytics, kusan kowane gidan yanar gizo).
• Tattara bayanai daga files duk manyan nau'ikan bayanai (XLSX, TXT, CSV, JSON, HTML, XML…), duka guda ɗaya kuma a cikin girma - daga duk fayiloli a cikin ƙayyadadden babban fayil. Daga littattafan aikin Excel, zaku iya zazzage bayanai ta atomatik daga duk zanen gado lokaci guda.
  
• Tsaftacewa an karɓi bayanai daga “datti”: ƙarin ginshiƙai ko layuka, maimaitawa, bayanan sabis a cikin “header”, ƙarin sarari ko haruffa marasa bugawa, da sauransu.
• Kawo bayanai a ciki domin: daidai shari'ar, lambobi-as-rubutu, cike giɓi, ƙara daidai "hutu" na tebur, rarraba rubutun "mai ɗaure" cikin ginshiƙai kuma manne shi baya, raba kwanan wata zuwa sassa, da dai sauransu.
  
• ta kowace hanya mai yiwuwa fasalin Tables, kawo su cikin sigar da ake so (tace, rarraba, canza tsari na ginshiƙai, juyawa, ƙara jimla, faɗaɗa teburan giciye zuwa lebur da rugujewa baya).
• Sauya bayanai daga wannan tebur zuwa wani ta hanyar daidaita ma'auni ɗaya ko fiye, watau kyakkyawan aikin maye gurbin VPR (VLOOKUP) da analogues.

Ana samun Query Query a cikin nau'i biyu: azaman add-in daban don Excel 2010-2013, wanda za'a iya sauke shi daga gidan yanar gizon Microsoft na hukuma, kuma a matsayin ɓangare na Excel 2016. A cikin akwati na farko, bayan shigarwa, wani shafin daban ya bayyana a ciki. Excel:

A cikin Excel 2016, duk aikin Query Query an riga an gina shi ta tsohuwa kuma yana kan shafin data (Kwanan wata) a matsayin rukuni Samu ku tuba (Samu & Canza):

Yiwuwar waɗannan zaɓuɓɓukan gaba ɗaya iri ɗaya ne.

Babban fasalin Tambayar Wutar Lantarki shine cewa duk ayyuka don shigo da bayanai ana adana su ta hanyar tambaya - jerin matakai a cikin harshen shirye-shiryen Query na ciki, wanda a takaice ake kira "M". Ana iya gyara matakai koyaushe kuma a sake kunna su kowane adadin lokuta (tambayoyin sabunta).

Babban taga Power Query yawanci yana kama da wani abu kamar haka:

A ra'ayina, wannan shine ƙari mafi fa'ida da aka jera a cikin wannan labarin don yawancin masu amfani. Aiyuka da yawa waɗanda dole ne kuyi muguwar murguda su tare da dabaru ko rubuta macros yanzu cikin sauƙi kuma da kyau ana yin su a cikin Query Query. Ee, kuma tare da sabuntawa ta atomatik na sakamako na gaba. Kuma idan aka yi la'akari da shi kyauta ne, dangane da ƙimar ingancin farashi, Query Query ya fita daga gasa kuma cikakkiyar dole ne ga kowane mai amfani da Excel mai matsakaicin ci gaba a kwanakin nan.

powerpivot

Power Pivot shima ƙari ne don Microsoft Excel, amma an tsara shi don ayyuka daban-daban. Idan Tambayar Wutar Wuta ta mayar da hankali kan shigo da aiki da sarrafawa, to ana buƙatar Power Pivot musamman don hadadden bincike na bayanai masu yawa. A matsayin ƙimar farko, zaku iya tunanin Power Pivot azaman tebur pivot mai ban sha'awa.

Babban ka'idodin aiki a cikin Power Pivot sune kamar haka:

1. Mu ne na farko lodi bayanai a cikin Power Pivot - 15 kafofin daban-daban suna da tallafi: bayanan gama gari (SQL, Oracle, Access ...), Fayilolin Excel, fayilolin rubutu, ciyarwar bayanai. Bugu da kari, zaku iya amfani da Query Query azaman tushen bayanai, wanda ke sa binciken ya zama kusan ko'ina.
2. Sannan tsakanin teburan da aka ɗora an saita haɗin kai ko kuma, kamar yadda suke faɗa, an halicce su Samfurin Bayanai. Wannan zai ba da damar a nan gaba don gina rahotanni akan kowane fage daga teburin da ke akwai kamar tebur ɗaya ne. Kuma babu VPR kuma.
3. Idan ya cancanta, ana ƙara ƙarin ƙididdiga zuwa Samfuran Bayanai ta amfani da ginshiƙan ƙididdiga (mai kama da ginshiƙi tare da dabaru a cikin “smart tebur”) da matakan (analojin filin da aka ƙididdigewa a cikin taƙaitaccen bayani). Duk waɗannan an rubuta su a cikin wani harshe na musamman na Power Pivot mai suna DAX (Data Analysis eXpressions).
4. A kan takardar Excel, bisa ga Tsarin Bayanai, an gina rahotannin sha'awar mu a cikin tsari tebur masu mahimmanci da zane-zane.

Babban taga Power Pivot yayi kama da haka:

Kuma wannan shine yadda Model Data ya yi kama, watau duk allunan da aka ɗora tare da ƙirƙira dangantaka:

Power Pivot yana da fasaloli da yawa waɗanda suka mai da shi kayan aiki na musamman don wasu ayyuka:

• A cikin Power Pivot babu iyaka (kamar yadda yake a cikin Excel). Kuna iya ɗaukar tebur na kowane girman kuma a sauƙaƙe aiki tare da su.
  
• Power Pivot yana da kyau sosai a damfara bayanai lokacin loda su a cikin Model. Fayil ɗin rubutu na asali 50MB na iya juyewa cikin sauƙi zuwa 3-5MB bayan an saukar da shi.
• Tun da "ƙarƙashin kaho" Power Pivot, a gaskiya, yana da cikakken injin bayanai, yana jimre da adadi mai yawa na bayanai. sosai azumi. Kuna buƙatar nazarin bayanan 10-15 miliyan kuma gina taƙaitaccen bayani? Kuma duk wannan akan tsohuwar kwamfutar tafi-da-gidanka? Babu matsala!

Abin takaici, har yanzu ba a haɗa Power Pivot a cikin duk nau'ikan Excel ba. Idan kuna da Excel 2010, zaku iya saukar da shi kyauta daga gidan yanar gizon Microsoft. Amma idan kuna da Excel 2013-2016, to duk ya dogara da lasisinku, saboda. a wasu nau'ikan an haɗa shi (Office Pro Plus, alal misali), kuma a wasu ba (Office 365 Home, Office 365 Personal, da sauransu) Kuna iya karanta ƙarin game da wannan anan.

Taswirar Wuta

Wannan add-on ya fara bayyana a cikin 2013 kuma ana kiransa da asali GeoFlow. An yi niyya don ganin bayanan geo-data, watau bayanan lambobi akan taswirorin yanki. Ana ɗaukar bayanan farko don nuni daga Samfurin Bayanai na Pivot guda ɗaya (duba sakin layi na baya).

Sigar demo na Taswirar Wuta (kusan babu bambanci da cikakken ɗaya, ta hanya) ana iya sake sauke shi gaba ɗaya kyauta daga gidan yanar gizon Microsoft. An haɗa cikakken sigar a cikin wasu fakiti na Microsoft Office 2013-2016 tare da Power Pivot - a cikin hanyar maɓalli. Taswirar 3D tab Saka (Saka - taswirar 3D):

Maɓalli na Taswirar Wuta:

• Maps na iya zama duka lebur da voluminous (globe).
• Kuna iya amfani da nau'ikan daban-daban iri-iri na gani (histograms, ginshiƙan kumfa, taswirar zafi, cika yanki).
• Kuna iya ƙarawa lokacin aunawa, watau rayar da tsarin kuma kallon yadda yake tasowa.
  
• Ana ɗora taswira daga sabis ɗin Taswirar Bing, watau Kuna buƙatar haɗin Intanet mai sauri don dubawa. Wani lokaci akwai matsaloli tare da daidai ganewa na adiresoshin, saboda. Sunayen da ke cikin bayanan ba koyaushe suke daidai da na Taswirorin Bing ba.
  
• A cikin cikakken sigar (ba demo) ta Taswirar Wuta, zaku iya amfani da naku zazzage taswirori, alal misali, don ganin baƙi zuwa cibiyar kasuwanci ko farashin gidaje a cikin ginin zama daidai akan tsarin ginin.
• Dangane da abubuwan hangen nesa da aka ƙirƙira, zaku iya ƙirƙirar bidiyo kai tsaye a cikin Taswirar Wuta (misali) don raba su daga baya tare da waɗanda ba su shigar da ƙara ko haɗa su a cikin gabatarwar Wutar Wuta.
  

kallon iko

Da farko an gabatar da shi a cikin Excel 2013, an ƙirƙiri wannan ƙarawa don kawo bayanan ku zuwa rayuwa tare da zane-zane, zane-zane, taswira, da teburi. Wani lokaci ana amfani da kalmomin don wannan. gaban (dashboard) or gaban (katin maki). Layin ƙasa shine zaku iya saka takarda ta musamman ba tare da sel ba a cikin fayil ɗin Excel ɗinku - nunin faifan Power View, inda zaku iya ƙara rubutu, hotuna da nau'ikan abubuwan gani daban-daban dangane da bayananku daga Model Data Pivot.

Zai yi kama da wani abu kamar haka:

Abubuwan da ke faruwa a nan su ne:

• Ana ɗaukar bayanan farko daga wuri ɗaya - daga Model Data Pivot.
• Don aiki tare da Power View, kana buƙatar shigar da Silverlight a kan kwamfutarka - misalin Microsoft na Flash (kyauta).
  

A kan gidan yanar gizon Microsoft, ta hanya, akwai ingantaccen kwas ɗin horo akan Power View a cikin .

Power BI

Ba kamar waɗanda suka gabata ba, Power BI ba ƙari ba ne don Excel, amma samfuri ne daban, wanda shine duka saitin kayan aikin don nazarin kasuwanci da hangen nesa. Ya ƙunshi abubuwa masu mahimmanci guda uku:

1. Power BI Desktop - A shiri don nazarin da kayan gani, wanda ya hada da, a tsakanin sauran abubuwa, duk ayyukan pivot na porce da ƙara matakan gani daga + Ingantattun hanyoyin gani daga wutar lantarki da kuma ƙarfin iko. Kuna iya saukewa kuma shigar dashi kyauta daga gidan yanar gizon Microsoft.

A cikin Power BI Desktop kuna iya:

• Load da bayanai daga kan 70 kafofin daban-daban (kamar a cikin Query Query + ƙarin masu haɗawa).
• daura Tables don yin samfuri (kamar a cikin Power Pivot)
• Ƙara ƙarin ƙididdiga zuwa bayanai tare da matakan и ginshiƙai masu ƙididdigewa akan DAX (kamar yadda yake cikin Power Pivot)
• Ƙirƙiri kyakkyawan tushen bayanai m rahotanni tare da nau'ikan abubuwan gani daban-daban (mai kama da Power View, amma har ma mafi kyau kuma mafi ƙarfi).
• buga ƙirƙira rahotanni akan rukunin sabis na Power BI (duba batu na gaba) kuma raba su tare da abokan aiki. Bugu da ƙari, yana yiwuwa a ba da haƙƙoƙi daban-daban (karantawa, gyarawa) ga mutane daban-daban.
  

2. Power BI sabis na kan layi – Don sanya shi a sauƙaƙe, wannan rukunin yanar gizon ne da ku da kowane mai amfani a cikin kamfanin ku za ku sami nasu “sandbox” (spacespace) inda zaku iya loda rahotannin da aka kirkira a cikin Power BI Desktop. Baya ga kallo, har ma yana ba ku damar gyara su, yana sake haifar da kusan dukkanin ayyukan Power BI Desktop akan layi. Hakanan zaka iya aro abubuwan gani ɗaya daga cikin rahotannin mutane anan, tare da tattara dashboard na marubucin daga gare su.

Yana kama da wani abu kamar haka:

3. Power BI Mobile aikace-aikace ne don iOS / Android / Windows don haɗawa zuwa Sabis na Power BI da dubawa cikin dacewa (ba gyara) rahotannin da aka ƙirƙira da dashboards daidai akan allon wayarku ko kwamfutar hannu. Kuna iya saukar da shi (cikakkiyar kyauta) anan.

A kan iPhone, alal misali, rahoton da aka samar a sama yayi kama da haka:

Kuma duk wannan yayin da ake ci gaba da yin hulɗa da motsin rai + ɗaure don taɓawa da zane akan allo tare da alkalami. Cikin kwanciyar hankali. Don haka, bayanan kasuwanci ya zama samuwa ga duk manyan mutane na kamfanin a kowane lokaci kuma a kowane wuri - samun damar Intanet kawai ake buƙata.

Shirye-shiryen farashin Power BI. Power BI Desktop da Wayar hannu ba su da kyauta daga cikin akwatin, kuma yawancin fasalulluka na Sabis na Power BI ma kyauta ne. Don haka don amfanin kai ko amfani a cikin ƙaramin kamfani, ba kwa buƙatar biyan dinari don duk abubuwan da ke sama kuma kuna iya tsayawa kan shirin lafiya. free. Idan kuna son raba rahotanni tare da abokan aiki da gudanar da haƙƙin samun damar su, dole ne ku je wurin BESS ($ 10 kowane wata kowane mai amfani). Akwai wasu kuma Premium - don manyan kamfanoni (> masu amfani da 500) waɗanda ke buƙatar keɓancewar ajiya da damar uwar garke don bayanai.

• Project Gantt Chart a cikin Excel tare da Query Query
• Yadda ake ƙirƙirar bayanai a cikin Excel ta amfani da Power Pivot
• Hange motsi tare da hanya akan taswira a Taswirar Wuta