Power Query дахь өгөгдлийн замыг параметржүүлэх

Хэрэв та Microsoft Excel-ийн үнэгүй Power Query нэмэлт хэрэгслийг аль хэдийн ашиглаж эхэлсэн бол тун удахгүй та эх сурвалжтай холбогдох холбоосыг байнга таслахтай холбоотой маш нарийн мэргэшсэн, гэхдээ маш олон удаа, ядаргаатай асуудалтай тулгарах болно. Асуудлын мөн чанар нь хэрэв та асуулгадаа гадаад файл эсвэл хавтас руу хандвал 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 баг хааж татаж аваарай (Нүүр хуудас — Хаах, ачаалах) таб Нүүр хуудас:

Хүсэлт доторх файлын замыг хайж байна

Одоо "M" товч нэртэй Power Query-д суулгасан дотоод хэлээр бидний асуулга "бүрээсний доор" хэрхэн харагдахыг харцгаая. Үүнийг хийхийн тулд баруун талын самбар дээр давхар товшиж бидний асуулга руу буцна уу Хүсэлт ба холболтууд болон таб дээр тойм сонгох Нарийвчилсан редактор (Харах — Нарийвчилсан редактор):

Нээгдсэн цонхонд хоёр дахь мөрөнд бидний анхны байршуулах файл руу хатуу кодлогдсон замыг нэн даруй харуулна. Хэрэв бид энэ текстийн мөрийг параметр, хувьсагч эсвэл энэ замыг урьдчилан бичсэн Excel хуудасны нүд рүү холбох холбоосоор сольж чадвал дараа нь амархан өөрчлөх боломжтой.

Файлын зам бүхий ухаалаг хүснэгт нэмнэ үү

Одоо Power Query-г хааж, файл руугаа буцъя Handler.xlsx. Шинэ хоосон хуудас нэмж, дээр нь жижиг "ухаалаг" хүснэгт хийцгээе, түүний цорын ганц нүдэнд манай эх өгөгдлийн файлын бүрэн замыг бичсэн болно.

Энгийн хүрээнээс ухаалаг ширээ үүсгэхийн тулд та гарын товчлолыг ашиглаж болно Ctrl+T эсвэл товчлуур Хүснэгт хэлбэрээр форматлах таб Нүүр хуудас (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах). Баганын гарчиг (A1 нүд) нь юу ч байж болно. Тодорхой болгохын тулд би хүснэгтэд нэр өгсөн гэдгийг анхаарна уу Үзүүлэлтүүд таб байгуулагч (Дизайн).

Explorer-ээс замыг хуулах эсвэл гараар оруулах нь мэдээжийн хэрэг тийм ч хэцүү биш боловч хүний ​​хүчин зүйлийг багасгаж, боломжтой бол автоматаар замыг тодорхойлох нь дээр. Үүнийг Excel-ийн ажлын хуудасны стандарт функцийг ашиглан хэрэгжүүлж болно Үүрэн (ЭС), энэ нь аргумент болгон заасан нүдний тухай олон хэрэгтэй мэдээллийг өгч болох бөгөөд үүнд одоогийн файл руу орох зам орно:

Хэрэв бид эх өгөгдлийн файл нь үргэлж манай процессортой нэг хавтсанд байдаг гэж үзвэл бидэнд хэрэгтэй замыг дараах томъёогоор үүсгэж болно.

=ЗҮҮН(CELL(“файлын нэр”); ОЛОХ(“[“;CELL(“файлын нэр”))-1)&”Шилдэг 100 бүтээгдэхүүн.xls”

эсвэл англи хувилбараар:

=ЗҮҮН(CELL(«файлын нэр»);ОЛОХ(«[«;CELL(«файлын нэр»))-1)&»Топ-100 товаров.xls»

… функц хаана байна LEVSIMV (Зүүн) нь бүтэн холбоосоос нээлтийн дөрвөлжин хаалт (жишээ нь одоогийн хавтас руу орох зам) хүртэлх текстийн хэсгийг авч, дараа нь манай эх өгөгдлийн файлын нэр, өргөтгөл наасан байна.

Асуулгад замыг параметрчил

Сүүлчийн бөгөөд хамгийн чухал мэдрэгчтэй зүйл бол хүсэлтэд эх файл руу хүрэх замыг бичих явдал юм Шилдэг 100 бүтээгдэхүүн.xls, бидний бүтээсэн "ухаалаг" хүснэгтийн А2 нүдийг дурдаж байна Үзүүлэлтүүд.

Үүнийг хийхийн тулд Power Query асуулга руу буцаж очоод дахин нээцгээе Нарийвчилсан редактор таб тойм (Харах — Нарийвчилсан редактор). Хашилт доторх текстийн мөр-замын оронд “E: Борлуулалтын тайлан Шилдэг 100 бүтээгдэхүүн.xlsx” Дараах бүтцийг танилцуулъя.

Excel.CurrentWorkbook(){[Нэр="Тохиргоо"]}[Агуулга]0 {}[Эх өгөгдөлд хүрэх зам]

Энэ нь юунаас бүрдэхийг харцгаая:

• Excel.CurrentWorkbook() нь одоогийн файлын агуулгад хандах M хэлний функц юм
• {[Нэр="Тохиргоо"]}[Агуулга] - энэ нь өмнөх функцийг сайжруулах параметр бөгөөд бид "ухаалаг" хүснэгтийн агуулгыг авахыг хүсч байгааг харуулж байна. Үзүүлэлтүүд
• [Эх өгөгдөлд хүрэх зам] нь хүснэгтийн баганын нэр юм Үзүүлэлтүүдбид үүнд хамаарна
  
• 0 {} хүснэгт дэх мөрийн дугаар юм Үзүүлэлтүүдүүнээс бид өгөгдөл авахыг хүсч байна. Тагийг тооцохгүй бөгөөд дугаарлалт нь нэгээс биш тэгээс эхэлдэг.

Үнэн хэрэгтээ энэ л байна.

Энэ нь дээр дарахад л үлддэг Finish мөн бидний хүсэлт хэрхэн ажилладагийг шалгана уу. Одоо хоёр файлын хамт хавтасыг бүхэлд нь өөр компьютерт илгээх үед хүсэлт нь ажиллаж, өгөгдөлд хүрэх замыг автоматаар тодорхойлох болно.

• Power Query гэж юу вэ, Microsoft Excel дээр ажиллахад яагаад хэрэгтэй вэ
• Power Query руу хөвөгч текстийн хэсгийг хэрхэн импортлох вэ
• Power Query бүхий XNUMXD хөндлөн табыг хавтгай хүснэгт болгон дахин загварчилж байна