Gina teburi tare da kanun labarai daban-daban daga littattafai da yawa

Samar da matsala

Muna da fayiloli da yawa (a cikin misalinmu - guda 4, a cikin yanayin gaba ɗaya - gwargwadon yadda kuke so) a cikin babban fayil ɗaya Rahotanni:

A ciki, waɗannan fayilolin suna kama da haka:

A ciki:

• Ana kiran takardar bayanan da muke buƙata koyaushe Photos, amma yana iya zama ko'ina a cikin littafin aiki.
  
• Bayan takardar Photos Kowane littafi na iya samun wasu zanen gado.
• Teburan da ke da bayanai suna da adadin layuka daban-daban kuma suna iya farawa da jere daban akan takardar aikin.
• Sunayen ginshiƙai ɗaya a cikin teburi daban-daban na iya bambanta (misali, Yawan = Yawan = Qty).
• Za a iya shirya ginshiƙai a cikin tebur a cikin wani tsari daban.

Aiki: tattara bayanan tallace-tallace daga duk fayiloli daga takardar Photos a cikin tebur na gama-gari don daga baya a gina taƙaitawa ko wani nazari akansa.

Mataki 1. Ana shirya kundin sunayen shafi

Abu na farko da za a yi shi ne shirya littafin tunani tare da duk zaɓuɓɓukan zaɓuɓɓuka don sunayen ginshiƙai da madaidaicin fassarar su:

Muna jujjuya wannan jeri zuwa tebur mai ƙarfi "mai wayo" ta amfani da Tsarin azaman maɓallin tebur akan shafin Gida (Gida - Tsarin azaman Tebur) ko gajeriyar hanyar keyboard Ctrl+T kuma loda shi a cikin Power Query tare da umarni Bayanai - Daga Tebur / Range (Bayanai - Daga Tebura/Range). A cikin sassan Excel na baya-bayan nan, an sake masa suna zuwa Tare da ganye (Daga takarda).

A cikin taga editan tambaya ta Power, muna share matakin bisa ga al'ada Canza Nau'in kuma ƙara sabon mataki maimakon shi ta danna maɓallin f_xa cikin mashaya dabara (idan ba a bayyane ba, to zaku iya kunna shi akan shafin review) kuma shigar da dabarar a can a cikin ginanniyar Harshen Tambayar Wuta M:

=Table.ToRows(Madogararsa)

Wannan umarni zai canza wanda aka ɗora a mataki na baya source Teburin tunani cikin jeri mai kunshe da jerin gwano (List), kowannensu, bi da bi, dabi'u biyu ne. An-zama daga layi daya:

Za mu buƙaci irin wannan bayanan kaɗan daga baya, lokacin da taro ke canza sunan kanun labarai daga duk allunan da aka ɗora.

Bayan kammala juyawa, zaɓi umarni Gida - Rufe kuma Loda - Rufe kuma Load a… da nau'in shigo da kaya Kawai ƙirƙirar haɗi (Gida - Rufe&Load - Rufe & Load zuwa… - Ƙirƙiri haɗi kawai) kuma komawa zuwa Excel.

Mataki 2. Mun load duk abin da daga duk fayiloli kamar yadda yake

Yanzu bari mu loda abubuwan da ke cikin dukkan fayilolin mu daga babban fayil - a yanzu, kamar yadda yake. Zabar ƙungiyoyi Bayanai - Sami bayanai - Daga fayil - Daga babban fayil (Data - Sami Data - Daga fayil - Daga babban fayil) sai kuma babban fayil inda littattafan tushen mu suke.

A cikin preview taga, danna maida (canza) or Change (Edit):

Sannan fadada abubuwan da ke cikin duk fayilolin da aka sauke (Binary) maɓalli tare da kibiyoyi biyu a cikin taken shafi Content:

Tambayar Wuta akan misalin fayil ɗin farko (Vostok.xlsx) zai tambaye mu sunan takardar da muke son ɗauka daga kowane littafin aiki - zaɓi Photos kuma danna Ok:

Bayan haka (a zahiri), abubuwa da yawa waɗanda ba a bayyane suke ga mai amfani ba za su faru, sakamakon abin da ke bayyane a fili a cikin sashin hagu:

1. Tambayar Wutar Lantarki zai ɗauki fayil na farko daga babban fayil (za mu sami shi Vostok.xlsx - gani Misalin fayil) a matsayin misali da shigo da abun ciki ta hanyar ƙirƙirar tambaya Maida fayil ɗin samfurin. Wannan tambayar zata sami wasu matakai masu sauki kamar source (shigar da fayil) navigation (zabin takardar) da yuwuwar haɓaka taken. Wannan buƙatar na iya loda bayanai daga takamaiman fayil ɗaya kawai Vostok.xlsx.
   
2. Dangane da wannan buƙatar, za a ƙirƙiri aikin da ke tattare da shi Maida fayil (wanda aka nuna ta alamar siffa f_x), inda fayil ɗin tushen ba zai ƙara zama dindindin ba, amma ƙima mai canzawa - siga. Don haka, wannan aikin zai iya fitar da bayanai daga kowane littafi da muka zame cikinsa a matsayin hujja.
   
3. Za a yi amfani da aikin bi da bi zuwa kowane fayil (Binary) daga ginshiƙi Content – mataki ne alhakin wannan Kira aikin al'ada a cikin tambayar mu da ke ƙara shafi zuwa jerin fayiloli Maida fayil tare da sakamakon shigo da kaya daga kowane littafin aiki:

   

4. An cire ƙarin ginshiƙai.
5. An faɗaɗa abubuwan da ke cikin tebur ɗin gida (mataki Rukunin tebur mai tsawo) - kuma muna ganin sakamakon ƙarshe na tattara bayanai daga duk littattafai:

   
   

Mataki na 3. Sanding

Hoton da ya gabata ya nuna a sarari cewa taron kai tsaye "kamar yadda yake" ya zama mara kyau:

• An juya ginshiƙan.
• Yawancin karin layukan (ba komai ba kawai ba).
• Ba a ganin taken tebur a matsayin masu kai kuma an gauraye su da bayanai.
  

Kuna iya gyara duk waɗannan matsalolin cikin sauƙi - kawai tweak da Maida Samfurin Fayil. Duk gyare-gyaren da muka yi da shi za su faɗi kai tsaye cikin aikin Maida fayil ɗin da ke da alaƙa, wanda ke nufin za a yi amfani da su daga baya lokacin shigo da bayanai daga kowane fayil.

Ta hanyar buɗe buƙata Maida fayil ɗin samfurin, ƙara matakai don tace layuka marasa mahimmanci (misali, ta shafi Column2) da kuma tayar da kanun labarai tare da maɓallin Yi amfani da layin farko azaman masu kai (Yi amfani da layin farko azaman masu kai). Tebur zai yi kyau sosai.

Domin ginshiƙan fayiloli daban-daban su dace da juna ta atomatik daga baya, dole ne a sanya musu suna iri ɗaya. Kuna iya yin irin wannan sake suna bisa ga kundin adireshi da aka ƙirƙira a baya tare da layi ɗaya na M-code. Bari mu sake danna maɓallin f_x a cikin dabarar mashaya kuma ƙara aiki don canzawa:

= Tebura.Sake Sunan Rukunoni (#"Maɗaukakin Ƙaƙwalwa", Masu kai, MissingField.Ignore)

Wannan aikin yana ɗaukar tebur daga mataki na baya Maɗaukakin kai kuma ya sake suna duk ginshiƙan da ke cikinsa bisa ga lissafin neman gida Adadin labarai. Hujja ta uku Rashin Filin. Yi watsi da shi ana buƙatar don haka akan waɗancan taken da ke cikin kundin adireshin, amma ba a cikin tebur ba, kuskure ba ya faruwa.

A gaskiya, shi ke nan.

Komawa ga buƙatar Rahotanni za mu ga hoto daban-daban - mafi kyau fiye da na baya:

• Menene Query, Power Pivot, Power BI da dalilin da yasa mai amfani da Excel ke buƙatar su
  
• Tattara bayanai daga duk fayiloli a cikin babban fayil da aka bayar
• Ana tattara bayanai daga dukkan zanen gado na littafin cikin tebur guda