Manangona latabatra avy amin'ny rakitra Excel samihafa miaraka amin'ny Power Query

Famolavolana ny olana

Andeha hojerentsika ny vahaolana tsara tarehy ho an'ny iray amin'ireo toe-javatra manara-penitra izay atrehin'ny ankamaroan'ny mpampiasa Excel na ho ela na ho haingana: mila manangona angon-drakitra avy amin'ny rakitra be dia be ao anaty latabatra farany ianao. 

Eritrereto hoe manana ity lahatahiry manaraka ity isika, izay misy rakitra maromaro misy angona avy amin'ny tanàn-dehiben'ny sampana:

Tsy misy dikany ny isan'ny rakitra ary mety hiova amin'ny ho avy. Ny rakitra tsirairay dia manana taratasy misy anarana Salesizay misy ny tabilao data:

Ny isan'ny andalana (baiko) ao amin'ny latabatra, mazava ho azy, dia samy hafa, fa ny fitambaran'ny tsanganana dia manara-penitra na aiza na aiza.

Asa: manangona angona avy amin'ny rakitra rehetra ao anaty boky iray miaraka amin'ny fanavaozana mandeha ho azy manaraka rehefa manampy na mamafa ny rakitra na andalana ao anaty tabilao. Araka ny latabatra mitambatra farany, dia ho azo atao ny manangana tatitra, tabilao pivot, angon-drakitra sivana, sns. Ny tena zava-dehibe dia ny fahafahana manangona.

Mifidy fitaovam-piadiana izahay

Ho an'ny vahaolana dia mila ny kinova farany an'ny Excel 2016 izahay (efa natsangana ao anatiny ny fampiasa ilaina) na ny dikan-teny teo aloha Excel 2010-2013 miaraka amin'ny fampidirana maimaim-poana napetraka. Fanontaniana momba ny herinaratra avy amin'ny Microsoft (misintona azy eto). Power Query dia fitaovana faran'izay malefaka sy mahery vaika hametahana angon-drakitra ao amin'ny Excel avy any ivelany, avy eo manala sy manodina azy. Ny Power Query dia manohana saika ny loharanom-baovao rehetra misy - manomboka amin'ny rakitra an-tsoratra ka hatramin'ny SQL ary na Facebook aza 🙂

Raha tsy manana Excel 2013 na 2016 ianao dia tsy afaka mamaky bebe kokoa (mivazivazy fotsiny). Amin'ny dikan-teny taloha kokoa amin'ny Excel, ny asa toy izany dia tsy azo tanterahina raha tsy amin'ny alàlan'ny fandaharana macro amin'ny Visual Basic (izay sarotra be ho an'ny vao manomboka) na amin'ny alàlan'ny kopia manual monotonous (izay maharitra ela ary miteraka fahadisoana).

Dingana 1. Manafatra rakitra iray ho santionany

Voalohany, andao hanafatra angona avy amin'ny bokikely iray ho ohatra, mba "handray ny hevitra" i Excel. Mba hanaovana izany, mamorona bokikely vaovao tsy misy dikany ary…

• raha manana Excel 2016 ianao dia sokafy ny tabilao Data ary avy eo Mamorona fanontaniana - Avy amin'ny rakitra - Avy amin'ny boky (Angona - Fanontaniana vaovao- Avy amin'ny rakitra - Avy amin'ny Excel)
• raha manana Excel 2010-2013 ianao miaraka amin'ny Power Query add-in napetraka, dia sokafy ny tabilao Fanontaniana momba ny herinaratra ary fidio eo Avy amin'ny rakitra - Avy amin'ny boky (Avy amin'ny rakitra - Avy amin'ny Excel)

Avy eo, eo amin'ny varavarankely misokatra, mandehana any amin'ny lahatahiry misy tatitra ary mifidiana ny iray amin'ireo rakitra ao an-tanàna (tsy maninona izay iray, satria izy rehetra dia mahazatra). Rehefa afaka segondra vitsy, dia tokony hiseho ny varavarankely Navigator, izay mila mifidy ny taratasy ilaintsika (Vambony) eo amin'ny ilany havia, ary ny ao anatiny dia haseho eo amin'ny ilany havanana:

Raha tsindrio ny bokotra eo amin'ny zoro ambany havanana amin'ity varavarankely ity Download (Enta-mavesatra), dia hafarana avy hatrany any amin'ny takelaka amin'ny endriny voalohany ny latabatra. Ho an'ny rakitra tokana dia tsara izany, saingy mila mametaka rakitra maro toy izany isika, noho izany dia handeha amin'ny fomba hafa isika ary tsindrio ny bokotra fanitsiana (Hanova). Aorian'izay, ny tonian-dahatsoratry ny fangatahana Power Query dia tokony aseho amin'ny varavarankely mitokana miaraka amin'ny angonay avy amin'ny boky:

Ity dia fitaovana mahery vaika ahafahanao "mamita" ny latabatra amin'ny fomba fijery ilaintsika. Na dia ny filazalazana kely momba ny asany rehetra aza dia mila pejy zato eo ho eo, fa raha fohy dia azonao atao ny mampiasa ity varavarankely ity:

• sivana ny angona tsy ilaina, tsipika foana, tsipika misy hadisoana
• alaharo ny angona amin'ny tsanganana iray na maromaro
• manala ny miverimberina
• zarao amin'ny tsanganana ny lahatsoratra mipetaka (amin'ny tsipika, isan'ny tarehintsoratra, sns.)
• apetraho amin'ny filaharana ny lahatsoratra (esory ny habaka fanampiny, ny sora-baventy, sns.)
• manova ny karazana angona amin'ny fomba rehetra azo atao (manova ny isa toy ny lahatsoratra ho isa mahazatra ary ny mifamadika amin'izany)
• avadika (ahodina) ny latabatra ary ampitomboy ny latabatra mipetaka amin'ny lafiny roa ho fisaka
• ampio tsanganana fanampiny amin'ny latabatra ary ampiasao ny raikipohy sy ny fiasa ao amin'izy ireo amin'ny fampiasana ny fiteny M natsangana ao amin'ny Power Query.
• ...

Ohatra, ndao asiana tsanganana misy ny anaran'ny lahatsoratra amin'ny volana eo amin'ny latabatra, mba ho mora kokoa ny manangana tatitra tabilao pivot any aoriana. Mba hanaovana izany, tsindrio havanana amin'ny lohatenin'ny tsanganana datyary fidio ny baiko Andry dika mitovy (Tsanganana duplikat), ary tsindrio havanana amin'ny lohatenin'ny tsanganana dika mitovy izay miseho ary mifidiana Commands Transform – Volana – Anaran’ny volana:

Tokony hatsangana ny tsanganana vaovao miaraka amin'ny anaran'ny lahatsoratra amin'ny volana isaky ny andalana. Amin'ny fanindriana indroa eo amin'ny lohatenin'ny tsanganana iray dia azonao atao ny manova ny anarany avy amin'ny Daty kopia ho mampahazo aina kokoa Month, oh.

Raha tsy fantatry ny programa amin'ny tsanganana sasany ny karazana data, azonao atao ny manampy azy amin'ny fipihana ny kisary endrika eo amin'ny ilany havia amin'ny tsanganana tsirairay:

Azonao atao ny manilika andalana misy hadisoana na tsipika foana, ary koa mpitantana na mpanjifa tsy ilaina, mampiasa sivana tsotra:

Ambonin'izany, ny fanovana rehetra natao dia raikitra ao amin'ny tontonana havanana, izay ahafahan'izy ireo mihodina (mihazona) na manova ny mari-pamantarana (fitaovana):

Maivana sy kanto, sa tsy izany?

Dingana 2. Andao hanova ny fangatahanay ho asa

Mba hamerenana indray ny fanovana angon-drakitra natao ho an'ny boky nafarana tsirairay avy eo, dia mila mamadika ny fangatahana noforonina ho lasa fiasa isika, izay hampiharina amin'ny rakitra rehetra avy eo. Ny fanaovana izany dia tena tsotra.

Ao amin'ny Query Editor, mandehana any amin'ny tabilao View ary tsindrio ny bokotra Tonian-dahatsoratra mandroso (Jereo - Tonian-dahatsoratra mandroso). Tokony hisokatra ny varavarankely izay hanoratana ny hetsika rehetra nataontsika teo aloha amin'ny endrika kaody amin'ny fiteny M. Mariho fa ny lalana mankany amin'ny rakitra izay nafaranay ho an'ny ohatra dia hardcode ao amin'ny code:

Andeha isika hanao fanitsiana roa:

Tsotra ny hevitr'izy ireo: ny andalana voalohany (filepath) => mamadika ny fomba fiasantsika ho asa misy argument filepath, ary eto ambany dia manova ny lalana raikitra mankany amin'ny sandan'ity fari-piainana ity. 

Rehetra. Tsindrio eo farany ary tokony ho hitanao ity:

Aza matahotra fa nanjavona ny angon-drakitra - raha ny marina, OK ny zava-drehetra, tokony ho toy izao ny zava-drehetra 🙂 Nahomby izahay namorona ny fiasa manokana, izay tsaroana ny algorithm manontolo amin'ny fanafarana sy fanodinana angon-drakitra nefa tsy mifamatotra amin'ny rakitra manokana. . Mbola omena anarana azo takarina kokoa izany (ohatra getData) eo amin'ny tontonana eo ankavanana amin'ny saha First anarana ary afaka mijinja ianao Home — Akatona ary alaivo (Horonan-trano - Akatona sy Ampidiro). Mariho fa ny lalana mankany amin'ny rakitra izay nafaranay ho an'ny ohatra dia hardcode ao amin'ny code. Hiverina any amin'ny varavarankely lehibe Microsoft Excel ianao, fa ny tontonana misy fifandraisana noforonina amin'ny fiasantsika dia tokony hiseho eo ankavanana:

Dingana 3. Manangona ny rakitra rehetra

Ny ampahany sarotra rehetra dia ao aoriana, ny ampahany mahafinaritra sy mora mijanona. Mandehana any amin'ny tabilao Data - Mamorona fanontaniana - Avy amin'ny rakitra - Avy amin'ny lahatahiry (Data - Fanontaniana vaovao - Avy amin'ny rakitra - Avy amin'ny lahatahiry) na, raha manana Excel 2010-2013 ianao, mitovy amin'ny tabilao Fanontaniana momba ny herinaratra. Ao amin'ny varavarankely izay miseho, mamaritra ny fampirimana misy ny loharanon-tanànanay rehetra ary tsindrio OK. Ny dingana manaraka dia tokony hanokatra varavarankely iray izay ahitana ny rakitra Excel rehetra hita ao amin'ity lahatahiry ity (sy ny zana-tsipìkany) sy ny antsipiriany ho an'ny tsirairay amin'izy ireo:

Tsindrio Change (Hanova) ary miditra ao amin'ny varavarankelin'ny mpanonta fanontaniana mahazatra indray isika.

Ankehitriny dia mila manampy tsanganana iray hafa amin'ny latabatray miaraka amin'ny asa noforoninay izahay, izay "hisintona" ny angona avy amin'ny rakitra tsirairay. Mba hanaovana izany, mandehana any amin'ny tabilao Ampio tsanganana - tsanganana manokana (Ampio Tsanganana - Hanampy Tsanganana manokana) ary ao amin'ny varavarankely izay miseho, midira ny asantsika getData, mamaritra azy ho toy ny argument ny lalana feno amin'ny rakitra tsirairay:

Taorian'ny fikitihana OK ny tsanganana noforonina dia tokony ampiana amin'ny latabatray eo ankavanana.

Andeha hofafantsika ny tsanganana tsy ilaina rehetra (toy ny ao amin'ny Excel, mampiasa ny bokotra havanana amin'ny totozy - Esory), ny tsanganana fanampiny sy ny tsanganana miaraka amin'ny anaran'ny rakitra ihany no sisa, satria io anarana io (ny marimarina kokoa, ny tanàna) dia ilaina amin'ny fitambaran'ny angona isaky ny andalana.

Ary izao ny "wow moment" - tsindrio ny kisary miaraka amin'ny zana-tsipìkany eo amin'ny zoro havanana ambony amin'ny tsanganana fanampiny miaraka amin'ny fiasantsika:

… esory ny marika Ampiasao ny anaran'ny tsanganana tany am-boalohany ho toy ny prefix (Ampiasao ho prefix ny anaran'ny tsanganana voalohany)Ary tsindrio OK. Ary ny asantsika dia hamindra sy handrindra ny angona avy amin'ny rakitra tsirairay, manaraka ny algorithm voarakitra ary manangona ny zava-drehetra ao anaty latabatra iraisana:

Ho an'ny hatsarana tanteraka, azonao atao koa ny manala ny fanitarana .xlsx amin'ny tsanganana voalohany misy anaran-drakitra - amin'ny fanoloana mahazatra amin'ny "tsy misy" (tsindrio havanana amin'ny lohatenin'ny tsanganana - mpisolo toerana) ary ovay anarana ity tsanganana ity ho fihetseham-po. Ary ahitsio koa ny endrika angona ao amin'ny tsanganana miaraka amin'ny daty.

Rehetra! Tsindrio eo Home - Akatona sy Loatra (Trano - Akatona & Ampidiro). Ny angon-drakitra rehetra voaangona amin'ny fangatahana ho an'ny tanàna rehetra dia halefa any amin'ny takelaka Excel amin'izao fotoana izao amin'ny endrika "smart table":

Ny fifandraisana noforonina sy ny asa fivorian-tsika dia tsy mila tehirizina misaraka amin'ny fomba rehetra - izy ireo dia voatahiry miaraka amin'ny rakitra ankehitriny amin'ny fomba mahazatra.

Amin'ny ho avy, raha misy fiovana ao amin'ny lahatahiry (manampiana na manala tanàna) na amin'ny rakitra (manova ny isan'ny andalana), dia ampy ny manindry havanana mivantana eo amin'ny latabatra na eo amin'ny fanontaniana eo amin'ny tontonana havanana ary safidio ny didy Fanavaozana & Tehirizo (Havaozina) - Power Query dia "hanangana" indray ny angon-drakitra rehetra ao anatin'ny segondra vitsy.

PS

Fanitsiana. Taorian'ny fanavaozam-baovao tamin'ny Janoary 2017, ny Power Query dia nianatra nanangona ny bokin'ny Excel irery, izany hoe tsy mila manao asa misaraka intsony - mandeha ho azy izany. Noho izany, ny dingana faharoa amin'ity lahatsoratra ity dia tsy ilaina intsony ary ny dingana manontolo dia lasa tsotra kokoa:

1. Mifidiana Mamorona fangatahana - Avy amin'ny rakitra - Avy amin'ny lahatahiry - Mifidiana Folder - OK
2. Rehefa miseho ny lisitry ny rakitra, tsindrio Change
3. Ao amin'ny varavarankelin'ny Query Editor, manitatra ny tsanganana Binary miaraka amin'ny zana-tsipìka roa ary safidio ny anaran'ny takelaka alaina amin'ny rakitra tsirairay.

Ary izay ihany! Hira!

• Avereno amboarina ho fisaka mety amin'ny fanamboarana latabatra pivot ny takelaka lakroa
• Manangana tabilao bubble animé ao amin'ny Power View
• Makro hanangona takelaka avy amin'ny rakitra Excel samihafa ho iray