Аз якчанд китобҳо ҷадвалҳо бо сарлавҳаҳои гуногун созед

Тартиб додани мушкилот

Мо дар як папка якчанд файл дорем (дар мисоли мо - 4 дона, дар ҳолати умумӣ - ба қадри шумо, ки мехоҳед). ҳисоботҳо:

Дар дохили ин файлҳо чунинанд:

Дар куҷо:

• Варақаи маълумоте, ки ба мо лозим аст, ҳамеша номида мешавад Суратҳо, аммо метавонад дар ҳама ҷо дар китоби корӣ бошад.
  
• Ғайр аз варақ Суратҳо Ҳар як китоб метавонад варақҳои дигар дошта бошад.
• Ҷадвалҳои дорои маълумот шумораи сатрҳои гуногун доранд ва метавонанд бо сатри дигар дар варақи корӣ оғоз шаванд.
• Номҳои як сутунҳо дар ҷадвалҳои гуногун метавонанд фарқ кунанд (масалан, Миқдор = Миқдор = Миқдор).
• Сутунҳоро дар ҷадвалҳо бо тартиби дигар ҷойгир кардан мумкин аст.

Вазифа: ҷамъоварии маълумоти фурӯш аз ҳама файлҳо аз варақ Суратҳо ба як ҷадвали умумӣ табдил диҳед, то ки минбаъд хулоса ё ҳама гуна таҳлили дигарро дар он созед.

Қадами 1. Омода кардани феҳристи номҳои сутунҳо

Аввалин коре, ки бояд анҷом дод, омода кардани маълумотнома бо тамоми имконоти имконпазири номҳои сутунҳо ва тафсири дурусти онҳо мебошад:

Мо ин рӯйхатро бо истифода аз тугмаи Формат ҳамчун ҷадвал дар ҷадвал ба ҷадвали динамикии "интеллектуалӣ" табдил медиҳем хона (Хона — Формат ҳамчун ҷадвал) ё миёнабури клавиатура Ctrl+T ва онро ба Power Query бо фармон бор кунед Маълумот - Аз ҷадвал/диапазон (Маълумот - Аз ҷадвал/диапазон). Дар версияҳои охирини Excel, он ба ном иваз карда шудааст Бо баргҳо (Аз варақ).

Дар равзанаи муҳаррири дархости Power Query, мо одатан қадамро нест мекунем Навъи ивазшуда ва ба ҷои он бо пахш кардани тугма қадами нав илова кунед f_xдар сатри формула (агар он намоён набошад, шумо метавонед онро дар ҷадвал фаъол созед Шарҳи) ва формуларо ба забони дарунсохташудаи Power Query M ворид кунед:

=Ҷадвал.ТоРавҳо(Манбаъ)

Ин фармон фармонеро, ки дар қадами қаблӣ бор карда шудааст, табдил медиҳад сарчашма ҷадвали истинод ба рӯйхате, ки аз рӯйхатҳои дохилшуда (Рӯйхат) иборат аст, ки ҳар яки онҳо дар навбати худ як ҷуфт арзишҳо мебошанд шуд- шуд аз як сатр:

Мо ин навъи маълумотро каме дертар, ҳангоми ба таври оммавӣ тағир додани номи сарлавҳаҳо аз ҳамаи ҷадвалҳои боршуда лозим хоҳад кард.

Пас аз ба итмом расонидани табдил, фармонҳоро интихоб кунед Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед… ва намуди воридот Танҳо пайвастшавӣ эҷод кунед (Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед… — Фақат пайвастшавӣ эҷод кунед) ва ба Excel баргардед.

Қадами 2. Мо ҳама чизро аз ҳама файлҳо тавре, ки ҳаст, бор мекунем

Акнун биёед мундариҷаи ҳамаи файлҳои худро аз ҷузвдон бор кунем - ҳоло, тавре ки ҳаст. Интихоби дастаҳо Маълумот - Гирифтани маълумот - Аз файл - Аз ҷузвдон (Маълумот - Гирифтани маълумот - Аз файл - Аз ҷузвдон) ва баъд папкаеро, ки дар он китобҳои сарчашмаҳои мо ҳастанд.

Дар равзанаи пешнамоиш, клик кунед Табдил додан (Тағйир додан) or иваз (Таҳрир):

Ва он гоҳ мундариҷаи ҳамаи файлҳои зеркашидашударо васеъ кунед (Бинарӣ) тугмаи бо тирчаҳои дукарата дар сарлавҳаи сутун Content:

Power Query дар мисоли файли аввал (Vostok.xlsx) аз мо номи варақеро мепурсад, ки мо аз ҳар як дафтарчаи корӣ гирифтан мехоҳем - интихоб кунед Суратҳо ва OK -ро пахш кунед:

Пас аз он (воқеан), якчанд ҳодисаҳое рух медиҳанд, ки барои корбар маълум нестанд, ки оқибатҳои онҳо дар панели чап ба таври равшан намоёнанд:

1. Power Query файли аввалро аз ҷузвдон мегирад (мо онро дорем Vostok.xlsx — дидан Намунаи файл) ҳамчун намуна ва мундариҷаи онро тавассути эҷоди дархост ворид мекунад Табдил додани файли намунавӣ. Ин дархост якчанд қадамҳои оддии монанди сарчашма (дастрасӣ ба файл) Навигатсия (интихоби варақ) ва эҳтимолан баланд бардоштани унвонҳо. Ин дархост метавонад танҳо маълумотро аз як файли мушаххас бор кунад Vostok.xlsx.
   
2. Дар асоси ин дархост, функсияи бо он алоқаманд эҷод карда мешавад Табдил додани файл (бо аломати хос нишон дода шудааст f_x), ки дар он файли манбаъ дигар доимӣ нест, балки арзиши тағирёбанда - параметр хоҳад буд. Ҳамин тариқ, ин функсия метавонад маълумотро аз ҳар китобе, ки мо ҳамчун далел ба он ворид мекунем, истихроҷ кунад.
   
3. Функсия бо навбат ба ҳар як файл (Бинарӣ) аз сутун истифода мешавад Content - қадам барои ин масъул аст Функсияи фармоишӣ занг занед дар дархости мо, ки ба рӯйхати файлҳо сутун илова мекунад Табдил додани файл бо натиҷаҳои воридот аз ҳар як китоби корӣ:

   

4. Сутунҳои иловагӣ хориҷ карда мешаванд.
5. Мундариҷаи ҷадвалҳои лона гузошташуда васеъ карда мешаванд (қадам Сутуни ҷадвали васеъ) - ва мо натиҷаҳои ниҳоии ҷамъоварии маълумотро аз ҳама китобҳо мебинем:

   
   

Қадами 3. Сангрезӣ

Скриншоти қаблӣ ба таври возеҳ нишон медиҳад, ки маҷлиси мустақим "чун мавҷуд аст" сифати паст баромад:

• Сутунҳо баргардонида шудаанд.
• Бисёр сатрҳои иловагӣ (холӣ ва на танҳо).
• Сарлавҳаҳои ҷадвал ҳамчун сарлавҳаҳо қабул карда намешаванд ва бо маълумот омехта шудаанд.
  

Шумо метавонед ҳамаи ин мушкилотро ба осонӣ ҳал кунед - танҳо дархости Табдилдиҳии Намунаи файлро тағир диҳед. Ҳама ислоҳоте, ки мо ба он мегузорем, ба таври худкор ба функсияи алоқаманди Табдилдиҳии файл дохил мешаванд, яъне онҳо баъдтар ҳангоми воридоти маълумот аз ҳар як файл истифода мешаванд.

Бо кушодани дархост Табдил додани файли намунавӣ, барои филтр кардани сатрҳои нолозим қадамҳо илова кунед (масалан, аз рӯи сутун Column2) ва боло бурдани сарлавҳаҳо бо тугма Сатри аввалро ҳамчун сарлавҳа истифода баред (Сатри аввалро ҳамчун сарлавҳа истифода баред). Ҷадвал хеле беҳтар хоҳад буд.

Барои он ки сутунҳои файлҳои гуногун баъдтар ба таври худкор дар зери ҳамдигар ҷойгир шаванд, онҳо бояд якхела номида шаванд. Шумо метавонед чунин тағир додани номи оммавиро мувофиқи директорияи қаблан сохташуда бо як сатри рамзи M иҷро кунед. Биёед боз тугмаро пахш кунем f_x дар сатри формула ва илова кардани функсия барои тағир додани:

= Ҷадвали.Сутунҳои номбаршударо (#"Сарлавҳаҳои баландшуда", сарлавҳаҳо, MissingField.Ignore)

Ин функсия ҷадвалро аз қадами қаблӣ мегирад Сарлавҳаҳои баланд ва номи ҳамаи сутунҳоро дар он мувофиқи рӯйхати ҷустуҷӯи лона иваз мекунад Сарлавҳаҳо. Далели сеюм MissingField.Игнор кардан лозим аст, ки дар он сарлавҳаҳое, ки дар директория ҳастанд, вале дар ҷадвал нестанд, хатогӣ рӯй надиҳад.

Дар асл, ҳамааш ҳамин аст.

Бозгашт ба дархост ҳисоботҳо мо манзараи тамоман дигарро мебинем - назар ба расми пешина хеле зеботар:

• Power Query, Power Pivot, Power BI чист ва чаро корбари Excel ба онҳо ниёз дорад
  
• Ҷамъоварии маълумот аз ҳама файлҳо дар ҷузвдони додашуда
• Ҷамъоварии маълумот аз ҳама варақаҳои китоб дар як ҷадвал