Haɗa tebur daga fayilolin Excel daban-daban tare da Query Query

Samar da matsala

Bari mu kalli kyakkyawan bayani don ɗayan daidaitattun yanayin da yawancin masu amfani da Excel ke fuskanta ba dade ko ba dade ba: kuna buƙatar tattara bayanai da sauri ta atomatik daga babban adadin fayiloli zuwa tebur na ƙarshe. 

A ce muna da babban fayil mai zuwa, wanda ya ƙunshi fayiloli da yawa tare da bayanai daga garuruwan reshe:

Adadin fayiloli ba shi da mahimmanci kuma yana iya canzawa a nan gaba. Kowane fayil yana da takardar mai suna Tallace-tallaceinda teburin bayanai yake:

Yawan layuka (umarni) a cikin tebur, ba shakka, ya bambanta, amma saitin ginshiƙan daidaitattun ko'ina.

Aiki: don tattara bayanai daga duk fayiloli zuwa littafi ɗaya tare da sabuntawa ta atomatik lokacin ƙara ko share fayilolin birni ko layuka a cikin teburi. Bisa ga tebur na ƙarshe na haɗin gwiwa, to, za a iya gina duk wani rahoto, tebur pivot, bayanan tacewa, da dai sauransu. Babban abu shine samun damar tattarawa.

Muna zabar makamai

Don mafita, muna buƙatar sabon sigar Excel 2016 (an riga an gina aikin da ake buƙata a ciki ta tsohuwa) ko sigogin Excel 2010-2013 da suka gabata tare da shigar da ƙari kyauta. Tambayar .arfi daga Microsoft (zazzage shi nan). Tambayar Wutar Lantarki babban kayan aiki ne mai sassauƙa da ƙarfi don loda bayanai cikin Excel daga duniyar waje, sannan cirewa da sarrafa su. Tambayar Wuta tana goyan bayan kusan duk tushen bayanan data kasance - daga fayilolin rubutu zuwa SQL har ma da Facebook 🙂

Idan ba ku da Excel 2013 ko 2016, to ba za ku iya ƙara karantawa ba (kawai wasa). A cikin tsofaffin nau'ikan Excel, irin wannan aikin ba za a iya cika shi ta hanyar tsara macro a cikin Visual Basic (wanda ke da matukar wahala ga masu farawa) ko ta hanyar kwafin hannu guda ɗaya (wanda ke ɗaukar lokaci mai tsawo kuma yana haifar da kurakurai).

Mataki 1. Shigo fayil ɗaya azaman samfuri

Da farko, bari mu shigo da bayanai daga littafin aiki ɗaya a matsayin misali, don Excel “ya ɗauki ra’ayin”. Don yin wannan, ƙirƙiri sabon littafin aiki mara komai kuma…

• idan kana da Excel 2016, to bude shafin data sai me Ƙirƙiri Tambaya - Daga Fayil - Daga Littafi (Bayanai - Sabuwar Tambaya- Daga fayil - Daga Excel)
• idan kuna da Excel 2010-2013 tare da shigar da ƙarar Query Query, sannan buɗe shafin. Tambayar .arfi kuma zaɓi a kai Daga fayil - Daga littafin (Daga fayil - Daga Excel)

Bayan haka, a cikin taga da ke buɗewa, je zuwa babban fayil ɗinmu tare da rahotanni kuma zaɓi kowane fayil ɗin birni (ba komai ko wanne ne, saboda dukkansu na yau da kullun ne). Bayan 'yan daƙiƙa biyu, taga Navigator ya kamata ya bayyana, inda kake buƙatar zaɓar takardar da muke buƙata (Sales) a gefen hagu, kuma abubuwan da ke ciki za a nuna su a gefen dama:

Idan ka danna maɓallin da ke ƙasan kusurwar dama na wannan taga Download (Load), to za a shigo da tebur nan da nan zuwa takardar a cikin asalin sa. Don fayil guda ɗaya, wannan yana da kyau, amma muna buƙatar ɗaukar irin waɗannan fayiloli da yawa, don haka za mu ɗan ɗan bambanta kuma danna maɓallin. gyara (Edit). Bayan haka, ya kamata a nuna editan tambaya ta Power a wata taga daban tare da bayanan mu daga littafin:

Wannan kayan aiki ne mai ƙarfi wanda ke ba ka damar "gama" teburin zuwa ra'ayin da muke bukata. Ko da bayanin zahirin duk ayyukansa zai ɗauki kusan shafuka ɗari, amma, idan a taƙaice, ta amfani da wannan taga zaku iya:

• tace bayanan da ba dole ba, layukan wofi, layi da kurakurai
• tsara bayanai ta hanyar guda ɗaya ko fiye da ginshiƙai
• kawar da maimaitawa
• raba rubutu mai ɗaure ta ginshiƙai (ta masu iyaka, adadin haruffa, da sauransu)
• sanya rubutu cikin tsari (cire ƙarin sarari, madaidaicin akwati, da sauransu)
• canza nau'ikan bayanai ta kowace hanya mai yiwuwa (juya lambobi kamar rubutu zuwa lambobi na yau da kullun da akasin haka)
• jujjuya (juya) tebur kuma faɗaɗa teburin giciye mai girma biyu zuwa masu lebur
• ƙara ƙarin ginshiƙai zuwa teburin kuma yi amfani da dabaru da ayyuka a cikinsu ta amfani da yaren M da aka gina cikin Tambayoyin Ƙarfi.
• ...

Misali, bari mu ƙara ginshiƙi mai sunan rubutun watan a kan tebur ɗinmu, ta yadda daga baya zai kasance da sauƙi don gina rahotannin tebur na pivot. Don yin wannan, danna dama akan taken shafi datekuma zaɓi umarnin Kwafin shafi (Shafin Kwafi), sa'an nan kuma danna-dama a kan taken rubutun kwafi wanda ya bayyana kuma zaɓi Umurni Canji - Wata - Sunan Wata:

Ya kamata a kafa sabon shafi tare da sunayen rubutun watan na kowane jere. Ta danna sau biyu akan taken shafi, zaku iya sake suna daga ciki Kwafi Kwanan Wata zuwa mafi dadi Watan, misali.

Idan a wasu ginshiƙai shirin bai gane nau'in bayanan daidai ba, to, zaku iya taimaka masa ta danna gunkin tsarin da ke gefen hagu na kowane shafi:

Kuna iya keɓance layi tare da kurakurai ko layukan wofi, da manajoji ko abokan ciniki maras buƙata, ta amfani da tace mai sauƙi:

Haka kuma, duk canje-canjen da aka yi ana gyara su a cikin madaidaicin panel, inda koyaushe ana iya jujjuya su (gicciye) ko canza sigogi (gear):

Haske da kyan gani, ko ba haka ba?

Mataki 2. Bari mu mayar da bukatar mu zuwa wani aiki

Domin sake maimaita duk bayanan da aka yi don kowane littafi da aka shigo da shi, muna buƙatar mu canza buƙatar da muka ƙirƙira zuwa aiki, wanda za a yi amfani da shi, bi da bi, ga duk fayilolinmu. Don yin wannan a zahiri abu ne mai sauqi qwarai.

A cikin Editan Tambaya, je zuwa shafin Dubawa kuma danna maɓallin Babban Edita (Duba - Babban Edita). Ya kamata taga ta buɗe inda za a rubuta duk ayyukanmu na baya a cikin nau'in lamba a cikin M yaren. Da fatan za a lura cewa hanyar zuwa fayil ɗin da muka shigo da shi don misali yana da ƙulli a cikin lambar:

Yanzu bari mu yi gyare-gyare guda biyu:

Ma'anar su mai sauƙi ne: layin farko (filepath) => yana juya tsarin mu zuwa aiki tare da jayayya hanyar fayil, kuma a ƙasa muna canza madaidaiciyar hanya zuwa ƙimar wannan madaidaicin. 

Duka. Danna kan Gama kuma ya kamata ku ga wannan:

Kada ku ji tsoro cewa bayanan sun ɓace - a gaskiya, duk abin da yake daidai, duk abin da ya kamata ya yi kama da wannan 🙂 Mun sami nasarar ƙirƙirar aikin mu na al'ada, inda ake tunawa da duk algorithm don shigo da bayanai ba tare da ɗaure shi zuwa wani takamaiman fayil ba. . Ya rage don ba shi suna mai sauƙin fahimta (misali samunData) a cikin panel a hannun dama a cikin filin Sunan rana kuma za ku iya girbi Gida - Rufe kuma zazzagewa (Gida - Kusa da Load). Da fatan za a lura cewa hanyar zuwa fayil ɗin da muka shigo da shi don misali yana da hardcoded a cikin lambar. Za ku koma babban taga Microsoft Excel, amma panel tare da haɗin da aka ƙirƙira zuwa aikinmu yakamata ya bayyana a hannun dama:

Mataki 3. Tattara duk fayiloli

Duk abin da ya fi wuya a baya, sashi mai dadi da sauƙi ya rage. Jeka shafin Bayanai - Ƙirƙiri Tambaya - Daga Fayil - Daga Jaka (Bayanai - Sabuwar Tambaya - Daga fayil - Daga babban fayil) ko, idan kuna da Excel 2010-2013, daidai da shafin Tambayar .arfi. A cikin taga da ya bayyana, saka babban fayil inda duk fayilolin garinmu suke kuma danna OK. Mataki na gaba ya kamata ya buɗe taga inda duk fayilolin Excel da aka samo a cikin wannan babban fayil (da manyan manyan fayiloli) da cikakkun bayanai na kowane ɗayan su za a jera su:

Click Change (Edit) kuma mun sake shiga cikin sanannen taga editan tambaya.

Yanzu muna buƙatar ƙara wani shafi zuwa teburin mu tare da aikin da aka ƙirƙira, wanda zai "jawo" bayanan daga kowane fayil. Don yin wannan, je zuwa shafin Ƙara Rukunin - Shagon Al'ada (Ƙara Rukunin - Ƙara Shagon Al'ada) kuma a cikin taga da ya bayyana, shigar da aikin mu samunData, Ƙayyade shi azaman hujja cikakkiyar hanyar zuwa kowane fayil:

Bayan danna kan OK ginshiƙin da aka halicce ya kamata a ƙara zuwa teburin mu a dama.

Yanzu bari mu share duk ginshiƙan da ba dole ba (kamar a cikin Excel, ta amfani da maɓallin linzamin kwamfuta na dama - cire), barin kawai ginshiƙi da aka ƙara da ginshiƙi tare da sunan fayil, saboda wannan sunan (mafi daidai, birni) zai zama da amfani don samun cikakkun bayanai na kowane jere.

Kuma yanzu "lokacin wow" - danna gunkin tare da kibansa a cikin kusurwar dama ta dama na ginshiƙi da aka ƙara tare da aikinmu:

… cirewa Yi amfani da sunan ginshiƙi na asali azaman prefix (Yi amfani da sunan shafi na asali azaman prefix)kuma danna OK. Kuma aikinmu zai loda da sarrafa bayanai daga kowane fayil, bin algorithm da aka yi rikodin kuma tattara komai a cikin tebur gama gari:

Don cikakkiyar kyakkyawa, zaku iya cire kari na .xlsx daga ginshiƙi na farko tare da sunayen fayil - ta daidaitaccen sauyawa tare da "ba komai" (danna dama akan taken shafi - Canje) kuma sake suna wannan shafi zuwa City. Sannan kuma gyara tsarin bayanai a cikin ginshiƙi tare da kwanan wata.

Duka! Danna kan Gida - Kusa kuma Load (Gida - Rufe & Load). Duk bayanan da aka tattara ta hanyar tambaya don duk biranen za a ɗora su zuwa takaddar Excel na yanzu a cikin tsarin “smart table”:

Haɗin da aka ƙirƙira da aikin taronmu baya buƙatar adanawa daban ta kowace hanya - an ajiye su tare da fayil ɗin yanzu a hanyar da aka saba.

A nan gaba, tare da kowane canje-canje a cikin babban fayil (ƙara ko cire birane) ko a cikin fayiloli (canza yawan layin), zai isa ya danna dama kai tsaye a kan tebur ko a kan tambaya a cikin dama panel kuma zaɓi . umarni Sabunta & Ajiye (Sake sabuntawa) - Tambayoyin wutar lantarki za su sake "sake gina" duk bayanan a cikin 'yan dakiku.

PS

Gyara. Bayan sabuntawar Janairu 2017, Tambayar Wuta ta koyi yadda ake tattara littattafan aikin Excel da kanta, watau babu buƙatar sake yin wani aiki daban - yana faruwa ta atomatik. Don haka, mataki na biyu daga wannan labarin baya buƙatar kuma gabaɗayan tsarin ya zama mafi sauƙi a bayyane:

1. zabi Ƙirƙiri buƙatar - Daga Fayil - Daga Jaka - Zaɓi Jaka - Ok
2. Bayan jerin fayilolin ya bayyana, danna Change
3. A cikin taga Editan Tambaya, fadada ginshiƙin Binary tare da kibiya biyu kuma zaɓi sunan takardar da za a ɗauka daga kowane fayil.

Kuma shi ke nan! Waka!

• Sake tsara ma'aunin giciye cikin lebur ɗin da ya dace da ginin teburi
• Gina taswirar kumfa mai rai a cikin Power View
• Macro don haɗa zanen gado daga fayilolin Excel daban-daban zuwa ɗaya