Kukusanya majedwali kutoka kwa faili tofauti za Excel na Hoja ya Nguvu

Uundaji wa shida

Hebu tuangalie suluhisho nzuri kwa mojawapo ya hali za kawaida ambazo watumiaji wengi wa Excel wanakabiliwa mapema au baadaye: unahitaji haraka na moja kwa moja kukusanya data kutoka kwa idadi kubwa ya faili kwenye meza moja ya mwisho. 

Tuseme tuna folda ifuatayo, ambayo ina faili kadhaa zilizo na data kutoka kwa miji ya matawi:

Idadi ya faili haijalishi na inaweza kubadilika katika siku zijazo. Kila faili ina laha iliyopewa jina Mauzoambapo meza ya data iko:

Idadi ya safu (maagizo) katika meza, bila shaka, ni tofauti, lakini seti ya nguzo ni ya kawaida kila mahali.

Kazi: kukusanya data kutoka kwa faili zote hadi kwenye kitabu kimoja na kusasisha kiotomatiki baadae wakati wa kuongeza au kufuta faili za jiji au safu katika majedwali. Kwa mujibu wa jedwali la mwisho lililoimarishwa, basi itawezekana kujenga ripoti yoyote, meza za pivot, data ya aina ya chujio, nk Jambo kuu ni kuwa na uwezo wa kukusanya.

Tunachagua silaha

Kwa suluhisho, tunahitaji toleo la hivi karibuni la Excel 2016 (utendaji muhimu tayari umejengwa ndani yake kwa chaguo-msingi) au matoleo ya awali ya Excel 2010-2013 na programu-jalizi ya bure iliyosakinishwa. Hoja ya Nguvu kutoka kwa Microsoft (pakua hapa). Power Query ni zana inayoweza kunyumbulika na yenye nguvu sana ya kupakia data kwenye Excel kutoka ulimwengu wa nje, kisha kuiondoa na kuichakata. Hoja ya Nguvu inasaidia karibu vyanzo vyote vya data vilivyopo - kutoka faili za maandishi hadi SQL na hata Facebook 🙂

Ikiwa huna Excel 2013 au 2016, basi huwezi kusoma zaidi (tu utani). Katika matoleo ya zamani ya Excel, kazi kama hiyo inaweza kukamilishwa tu kwa kupanga macro katika Visual Basic (ambayo ni ngumu sana kwa Kompyuta) au kwa kunakili kwa mikono (ambayo inachukua muda mrefu na kutoa makosa).

Hatua ya 1. Leta faili moja kama sampuli

Kwanza, hebu tuingize data kutoka kwa kitabu kimoja cha kazi kama mfano, ili Excel "ichukue wazo". Ili kufanya hivyo, tengeneza kitabu kipya cha kazi na…

• ikiwa una Excel 2016, kisha ufungue tabo Data na kisha Unda Swali - Kutoka kwa Faili - Kutoka kwa Kitabu (Data - Hoja Mpya- Kutoka kwa faili - Kutoka Excel)
• ikiwa una Excel 2010-2013 na programu jalizi ya Swala la Nguvu iliyosakinishwa, kisha fungua kichupo. Hoja ya Nguvu na uchague juu yake Kutoka kwa faili - Kutoka kwa kitabu (Kutoka kwa faili - Kutoka Excel)

Kisha, katika dirisha linalofungua, nenda kwenye folda yetu na ripoti na uchague faili yoyote ya jiji (haijalishi ni ipi, kwa sababu yote ni ya kawaida). Baada ya sekunde chache, dirisha la Navigator linapaswa kuonekana, ambapo unahitaji kuchagua karatasi tunayohitaji (Mauzo) upande wa kushoto, na yaliyomo yake yataonyeshwa upande wa kulia:

Ikiwa unabonyeza kitufe kwenye kona ya chini ya kulia ya dirisha hili Pakua (Mzigo), basi meza itaingizwa mara moja kwenye karatasi katika fomu yake ya awali. Kwa faili moja, hii ni nzuri, lakini tunahitaji kupakia faili nyingi kama hizo, kwa hivyo tutaenda tofauti kidogo na bonyeza kitufe. Marekebisho (Hariri). Baada ya hapo, kihariri cha hoja ya Nguvu kinapaswa kuonyeshwa kwenye dirisha tofauti na data yetu kutoka kwa kitabu:

Hii ni zana yenye nguvu sana ambayo inakuwezesha "kumaliza" meza kwa mtazamo tunaohitaji. Hata maelezo ya juu juu ya kazi zake zote yanaweza kuchukua takriban kurasa mia moja, lakini, ikiwa ni kwa ufupi sana, kwa kutumia dirisha hili unaweza:

• chuja data isiyo ya lazima, mistari tupu, mistari iliyo na makosa
• panga data kwa safu wima moja au zaidi
• kuondokana na kurudia
• gawanya maandishi nata kwa safu wima (kwa vitenganishi, idadi ya herufi, n.k.)
• weka maandishi kwa mpangilio (ondoa nafasi za ziada, herufi sahihi, n.k.)
• badilisha aina za data kwa kila njia inayowezekana (geuza nambari kama maandishi kuwa nambari za kawaida na kinyume chake)
• geuza (zungusha) jedwali na kupanua meza-msalaba za pande mbili kuwa gorofa
• ongeza safu wima za ziada kwenye jedwali na utumie fomula na vitendaji ndani yake kwa kutumia lugha ya M iliyojumuishwa katika Hoja ya Nguvu.
• ...

Kwa mfano, hebu tuongeze safu iliyo na jina la maandishi la mwezi kwenye jedwali letu, ili baadaye iwe rahisi kuunda ripoti za jedwali la egemeo. Ili kufanya hivyo, bonyeza-click kwenye kichwa cha safu tarehena uchague amri Safu wima rudufu (Rudufu Safu wima), na kisha ubofye-kulia kwenye kichwa cha safu wima inayoonekana na uchague Amri Badilisha - Mwezi - Jina la Mwezi:

Safu wima mpya inapaswa kuundwa yenye majina ya maandishi ya mwezi kwa kila safu mlalo. Kwa kubofya mara mbili kwenye kichwa cha safu, unaweza kukipa jina jipya kutoka Nakili Tarehe kwa starehe zaidi mwezikm.

Ikiwa katika safu wima zingine programu haikutambua kwa usahihi aina ya data, basi unaweza kuisaidia kwa kubofya ikoni ya umbizo upande wa kushoto wa kila safu:

Unaweza kuwatenga mistari iliyo na makosa au mistari tupu, na vile vile wasimamizi au wateja wasio wa lazima, kwa kutumia kichungi rahisi:

Kwa kuongezea, mabadiliko yote yaliyofanywa yamewekwa kwenye paneli ya kulia, ambapo yanaweza kurudishwa nyuma (msalaba) au kubadilisha vigezo vyao (gia):

Mwanga na kifahari, sivyo?

Hatua ya 2. Hebu tubadilishe ombi letu kuwa kipengele cha kukokotoa

Ili kurudia baadaye mabadiliko yote ya data yaliyofanywa kwa kila kitabu kilichoagizwa, tunahitaji kubadilisha ombi letu tulilounda kuwa chaguo la kukokotoa, ambalo litatumika, kwa upande wake, kwa faili zetu zote. Kufanya hivyo kwa kweli ni rahisi sana.

Katika Mhariri wa Hoja, nenda kwenye kichupo cha Tazama na ubofye kitufe Mhariri wa hali ya juu (Angalia - Mhariri wa hali ya juu). Dirisha linapaswa kufunguliwa ambapo vitendo vyetu vyote vya awali vitaandikwa kwa njia ya msimbo katika lugha ya M. Tafadhali kumbuka kuwa njia ya faili ambayo tuliingiza kwa mfano ni ngumu katika msimbo:

Sasa wacha tufanye marekebisho kadhaa:

Maana yao ni rahisi: mstari wa kwanza (njia ya faili)=> hugeuza utaratibu wetu kuwa utendaji wenye hoja njia ya faili, na chini tunabadilisha njia iliyowekwa kwa thamani ya kutofautiana hii. 

Wote. Bonyeza Kumaliza na inapaswa kuona hii:

Usiogope kwamba data imetoweka - kwa kweli, kila kitu ni sawa, kila kitu kinapaswa kuonekana kama hii 🙂 Tumefanikiwa kuunda kazi yetu maalum, ambapo algorithm nzima ya kuagiza na usindikaji wa data inakumbukwa bila kufungwa kwa faili maalum. . Inabakia kuipa jina linaloeleweka zaidi (kwa mfano getData) kwenye paneli iliyo upande wa kulia kwenye uwanja Jina la kwanza na unaweza kuvuna Nyumbani - Funga na upakue (Nyumbani - Funga na Upakie). Tafadhali kumbuka kuwa njia ya faili ambayo tulileta kwa mfano ni ngumu katika msimbo. Utarudi kwenye dirisha kuu la Microsoft Excel, lakini paneli iliyo na unganisho iliyoundwa kwa kazi yetu inapaswa kuonekana kulia:

Hatua ya 3. Kukusanya faili zote

Sehemu ngumu zaidi iko nyuma, sehemu ya kupendeza na rahisi inabaki. Nenda kwenye kichupo Data - Unda Hoja - Kutoka kwa Faili - Kutoka kwa Folda (Data - Hoja Mpya - Kutoka kwa faili - Kutoka kwa folda) au, ikiwa una Excel 2010-2013, sawa na kichupo Hoja ya Nguvu. Katika dirisha inayoonekana, taja folda ambapo faili zetu zote za jiji la chanzo ziko na ubofye OK. Hatua inayofuata inapaswa kufungua dirisha ambapo faili zote za Excel zilizopatikana kwenye folda hii (na folda zake ndogo) na maelezo kwa kila moja yao yataorodheshwa:

Bonyeza Mabadiliko ya (Hariri) na tena tunaingia kwenye dirisha la mhariri wa swala linalofahamika.

Sasa tunahitaji kuongeza safu nyingine kwenye meza yetu na kazi yetu iliyoundwa, ambayo "itavuta" data kutoka kwa kila faili. Ili kufanya hivyo, nenda kwenye kichupo Ongeza Safu - Safu Wima Maalum (Ongeza Safu - Ongeza Safu Wima Maalum) na katika dirisha inayoonekana, ingiza kazi yetu getData, ikibainisha kama hoja njia kamili ya kila faili:

Baada ya kubonyeza OK safu iliyoundwa inapaswa kuongezwa kwenye meza yetu upande wa kulia.

Sasa hebu tufute safu wima zote zisizo za lazima (kama katika Excel, kwa kutumia kitufe cha kulia cha panya - Ondoa), na kuacha safu iliyoongezwa tu na safu iliyo na jina la faili, kwa sababu jina hili (kwa usahihi zaidi, jiji) litakuwa na manufaa kuwa na data ya jumla kwa kila safu.

Na sasa "wakati wa wow" - bonyeza kwenye ikoni na mishale yake kwenye kona ya juu ya kulia ya safu iliyoongezwa na kazi yetu:

… batilisha uteuzi Tumia jina la safu wima asili kama kiambishi awali (Tumia jina la safu wima asili kama kiambishi awali)na bonyeza OK. Na kazi yetu itapakia na kuchakata data kutoka kwa kila faili, kufuata kanuni iliyorekodiwa na kukusanya kila kitu kwenye jedwali la pamoja:

Kwa uzuri kamili, unaweza pia kuondoa viendelezi vya .xlsx kwenye safu wima ya kwanza yenye majina ya faili - kwa kubadilisha kawaida na "hakuna chochote" (bofya kulia kwenye kichwa cha safu wima - Msaada) na ubadilishe jina la safu hii kuwa Mji/Jiji. Na pia sahihisha umbizo la data kwenye safu na tarehe.

Wote! Bonyeza Nyumbani - Funga na Upakie (Nyumbani - Funga na Upakie). Data yote iliyokusanywa na hoja ya miji yote itapakiwa kwenye laha ya sasa ya Excel katika umbizo la "smart table":

Uunganisho ulioundwa na kazi ya mkutano wetu hauhitaji kuokolewa tofauti kwa njia yoyote - huhifadhiwa pamoja na faili ya sasa kwa njia ya kawaida.

Katika siku zijazo, na mabadiliko yoyote kwenye folda (kuongeza au kuondoa miji) au faili (kubadilisha idadi ya mistari), itatosha kubofya kulia moja kwa moja kwenye meza au kwenye swala kwenye paneli ya kulia na uchague amri Sasisha na Uhifadhi (Onyesha upya) - Hoja ya Nguvu "itaunda upya" data yote tena kwa sekunde chache.

PS

Marekebisho. Baada ya sasisho za Januari 2017, Power Query ilijifunza jinsi ya kukusanya vitabu vya kazi vya Excel peke yake, yaani, hakuna haja ya kufanya kazi tofauti tena - hutokea moja kwa moja. Kwa hivyo, hatua ya pili kutoka kwa nakala hii haihitajiki tena na mchakato mzima unakuwa rahisi sana:

1. Kuchagua Unda Ombi - Kutoka kwa Faili - Kutoka kwa Folda - Chagua Folda - Sawa
2. Baada ya orodha ya faili kuonekana, bonyeza Mabadiliko ya
3. Katika dirisha la Mhariri wa Hoja, panua safu wima ya Binary na mshale mara mbili na uchague jina la laha litakalochukuliwa kutoka kwa kila faili.

Na hiyo ndiyo yote! Wimbo!

• Panga upya kichupo kiwe tambarare kinachofaa kwa ajili ya kujenga jedwali egemeo
• Kuunda chati ya viputo iliyohuishwa katika Mwonekano wa Nguvu
• Macro ya kukusanya laha kutoka faili tofauti za Excel hadi moja