Параметризатсияи роҳҳои маълумот дар Query Power

Агар шумо аллакай ба истифодаи абзорҳои иловагии ройгони Power Query дар Microsoft Excel шурӯъ карда бошед, пас ба зудӣ шумо бо як мушкили махсусгардонидашуда, вале хеле зуд-зуд ва озори марбут ба пайваста вайрон кардани истинодҳо ба маълумоти манбаъ дучор мешавед. Моҳияти мушкилот дар он аст, ки агар шумо дар дархости худ ба файлҳо ё ҷузвдонҳои беруна муроҷиат кунед, пас Power Query роҳи мутлақи онҳоро дар матни дархост код мекунад. Ҳама чиз дар компютери шумо хуб кор мекунад, аммо агар шумо қарор диҳед, ки файлро бо дархост ба ҳамкасбони худ фиристед, пас онҳо ноумед хоҳанд шуд, зеро. онҳо ба маълумоти сарчашма дар компютери худ роҳи дигар доранд ва дархости мо кор намекунад.

Дар чунин вазъият чй бояд кард? Биёед ин қазияро бо мисоли зерин муфассалтар дида бароем.

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

Фарз мекунем, ки мо дар папка дорем E:Ҳисоботи фурӯш файл аст 100 маҳсулоти беҳтарин.xls, ки боргузорӣ аз базаи корпоративии мо ё системаи ERP (1C, SAP ва ғайра) мебошад.

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

Аз ин рӯ, дар паҳлӯи ин файл дар ҳамон ҷузвдон мо боз як файли нав эҷод мекунем Handler.xlsx, ки дар он мо дархости Power Query эҷод мекунем, ки маълумоти зишти файли боркунии манбаъро бор мекунад 100 маҳсулоти беҳтарин.xls, ва онҳоро ба тартиб оред:

Қабули дархост ба файли беруна

Кушодани файл Handler.xlsx, дар ҷадвал интихоб кунед Маълумот фармон Гирифтани маълумот - аз файл - аз китоби кории Excel (Маълумот - Гирифтани маълумот - Аз файл - Аз Excel), пас ҷойгиршавии файли сарчашма ва варақеро, ки ба мо лозим аст, муайян кунед. Маълумоти интихобшуда ба муҳаррири Power Query бор карда мешавад:

Биёед онҳоро ба ҳолати муқаррарӣ баргардонем:

1. Тоза кардани сатрҳои холӣ бо Хона — Нест кардани хатҳо — Тоза кардани сатрҳои холӣ (Хона - Тоза кардани сатрҳо - Тоза кардани сатрҳои холӣ).
2. 4 хати болоии нолозимро аз тариқи нест кунед Хона — Тоза кардани сатр — Тоза кардани сатрхои боло (Хона - Нест кардани сатрҳо - Хориҷ кардани сатрҳои боло).
3. Сатри аввалро ба сарлавҳаи ҷадвал бо тугма баланд кунед Сатри аввалро ҳамчун сарлавҳа истифода баред ҷадвали хона (Хона - Сатри аввалро ҳамчун сарлавҳа истифода баред).
4. Бо истифода аз фармон мақолаи панҷрақамаро аз номи маҳсулот дар сутуни дуюм ҷудо кунед сутуни тақсимшуда ҷадвали дигаргунсозии (Тағйир додан — Сутуни тақсимшуда).
5. Сутунҳои нолозимро нест кунед ва сарлавҳаҳои боқимондаро барои дидани беҳтар номгузорӣ кунед.

Дар натиҷа, мо бояд тасвири зерини хеле гуворо ба даст орем:

Баргардонидани ин ҷадвали олӣ ба варақи файли мо боқӣ мемонад Handler.xlsx дастаи пӯшед ва зеркашӣ кунед (Ба хона — Пӯшидан ва бор кардан) ҷадвали хона:

Ҷустуҷӯи роҳ ба файл дар дархост

Акнун биёед бубинем, ки дархости мо бо забони дохилӣ, ки дар Power Query бо номи мухтасари "M" сохта шудааст, чӣ гуна "дар зери сарпӯш" ба назар мерасад. Барои ин, ба дархости мо баргардед, ки онро дар панели рост ду маротиба клик кунед Дархостҳо ва алоқаҳо ва дар лавҳа Шарҳи интихоб кардан Муҳаррири пешрафта (Намоиш — Муҳаррири пешрафта):

Дар равзанаи кушодашуда, сатри дуюм фавран роҳи рамзбандии файли аслии боркунии моро нишон медиҳад. Агар мо метавонем ин сатри матниро бо параметр, тағирёбанда ё пайванд ба ячейкаи варақи Excel, ки ин роҳ пешакӣ навишта шудааст, иваз кунем, пас мо метавонем онро баъдтар ба осонӣ иваз кунем.

Ҷадвали интеллектуалӣ бо роҳи файл илова кунед

Биёед ҳоло Power Query-ро пӯшем ва ба файли худ баргардем Handler.xlsx. Биёед як варақи нави холӣ илова кунем ва дар он ҷадвали хурди "ақлӣ" созем, ки дар ягона ячейкаи он роҳи пурраи файли маълумоти сарчашмаи мо навишта мешавад:

Барои сохтани ҷадвали интеллектуалӣ аз доираи муқаррарӣ, шумо метавонед миёнабури клавиатураро истифода баред Ctrl+T ё тугмача Формат ҳамчун ҷадвал ҷадвали хона (Хона — Формат ҳамчун ҷадвал). Сарлавҳаи сутун (ячейкаи A1) метавонад ҳама чиз бошад. Инчунин қайд кунед, ки барои равшанӣ ман ба ҷадвал ном додам параметрҳо ҷадвали Конструктор (Тарроҳӣ).

Нусхабардории роҳ аз Explorer ё ҳатто ба таври дастӣ ворид кардани он, албатта, он қадар душвор нест, аммо беҳтар аст, ки омили инсониро ба ҳадди ақалл кам кунед ва роҳро, агар имконпазир бошад, ба таври худкор муайян кунед. Инро бо истифода аз функсияи стандартии варақаи кории Excel амалӣ кардан мумкин аст ҲУЧайра (ҳуҷайра), ки метавонад як қатор маълумоти муфидро дар бораи ячейкае, ки ҳамчун далел нишон дода шудааст, диҳад, аз ҷумла роҳ ба файли ҷорӣ:

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

=ЧАП(CELL("номи файл");ФИНД("[“;CELL("номи файл"))-1)&"100 маҳсулоти беҳтарин.xls"

ё бо версияи англисӣ:

=ЧАП(ЧАП(«номи файл»);ПАЙДО(«[«;ЯЧАЧА(«номи файл»))-1)&»Топ-100 товаров.xls»

… функсия дар куҷост ЛЕВСИМВ (ЧАП) як пораи матнро аз истиноди пурра то қавси мураббаъ кушода мегирад (яъне роҳ ба ҷузвдони ҷорӣ) ва сипас ном ва васеъшавии файли маълумоти сарчашмаи мо ба он часпонида мешавад.

Роҳро дар дархост параметрҳо кунед

Охирин ва муҳимтарин ламс боқӣ мемонад - навиштани роҳ ба файли манбаъ дар дархост 100 маҳсулоти беҳтарин.xls, бо истинод ба ячейкаи A2 ҷадвали "ақл"-и мо сохташуда параметрҳо.

Барои ин, биёед ба дархости Power Query баргардем ва онро боз кушоем Муҳаррири пешрафта ҷадвали Шарҳи (Намоиш — Муҳаррири пешрафта). Ба ҷои як сатри матн дар нохунак "E: Ҳисоботи фурӯш дар бораи 100 маҳсулот.xlsx" Биёед сохтори зеринро муаррифӣ кунем:

Excel.CurrentWorkbook(){[Ном="Танзимот"]}[Мӯҳтаво]0 {}[Роҳ ба маълумоти манбаъ]

Биёед бубинем, ки он аз чӣ иборат аст:

• Excel.CurrentWorkbook() вазифаи забони M барои дастрасӣ ба мундариҷаи файли ҷорӣ мебошад
• {[Ном="Танзимот"]}[Мӯҳтаво] - ин параметри такмилдиҳии функсияи қаблӣ мебошад, ки нишон медиҳад, ки мо мехоҳем мундариҷаи ҷадвали "интеллектуалӣ" -ро гирем параметрҳо
• [Роҳ ба маълумоти манбаъ] номи сутуни ҷадвал аст параметрҳоки мо ба он мурочиат мекунем
  
• 0 {} рақами сатр дар ҷадвал аст параметрҳоки мо мехоҳем аз он маълумот гирем. Сарпӯш ҳисоб намешавад ва рақамгузорӣ аз сифр оғоз мешавад, на аз як.

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

Барои пахш кардан боқӣ мемонад тамом кардан ва санҷед, ки дархости мо чӣ гуна кор мекунад. Ҳоло, ҳангоми фиристодани тамоми ҷузвдон бо ҳарду файл дар дохили компютери дигар, дархост фаъол боқӣ мемонад ва роҳи ба маълумотро ба таври худкор муайян мекунад.

• Power Query чист ва чаро он ҳангоми кор дар Microsoft Excel лозим аст
• Чӣ тавр як пораи матнии шинокунандаро ба Power Query ворид кардан мумкин аст
• Аз нав тарҳрезии Crosstab XNUMXD ба ҷадвали ҳамвор бо дархости барқ