Power Query ашиглан өөр өөр Excel файлуудаас хүснэгтүүдийг угсарч байна

Асуудлын томъёолол

Ихэнх Excel-ийн хэрэглэгчид эрт орой хэзээ нэгэн цагт тулгардаг стандарт нөхцөл байдлын нэг сайхан шийдлийг харцгаая: та олон тооны файлуудаас өгөгдлийг хурдан бөгөөд автоматаар нэг эцсийн хүснэгтэд цуглуулах хэрэгтэй. 

Бидэнд салбар хотуудын өгөгдөл бүхий хэд хэдэн файл агуулсан дараах хавтас байна гэж бодъё.

Файлын тоо чухал биш бөгөөд ирээдүйд өөрчлөгдөж магадгүй юм. Файл бүр нэртэй хуудастай Борлуулалтынөгөгдлийн хүснэгт хаана байрладаг:

Хүснэгт дэх мөрүүдийн тоо (захиалга) нь мэдээжийн хэрэг өөр өөр боловч баганын багц нь хаа сайгүй стандарт байдаг.

Даалгавар: хотын файлууд эсвэл хүснэгтэд мөр нэмэх, устгах үед дараа нь автоматаар шинэчлэх замаар бүх файлаас өгөгдлийг нэг ном болгон цуглуулах. Эцсийн нэгтгэсэн хүснэгтийн дагуу, дараа нь ямар ч тайлан, пивот хүснэгт, шүүлтүүрээр эрэмбэлэх өгөгдөл гэх мэтийг бүтээх боломжтой болно. Хамгийн гол нь цуглуулах чадвартай байх явдал юм.

Бид зэвсгийг сонгодог

Шийдлийн хувьд бидэнд Excel 2016-ийн хамгийн сүүлийн хувилбар (шаардлагатай функцийг өгөгдмөл байдлаар суулгасан) эсвэл үнэгүй нэмэлт суулгасан Excel 2010-2013-ын өмнөх хувилбарууд хэрэгтэй. Эрчим хүчний асуулга Microsoft-аас (энд татаж авна уу). Power Query нь Excel-д өгөгдлийг гадаад ертөнцөөс ачаалж, дараа нь хуулж, боловсруулахад зориулагдсан супер уян хатан, хүчирхэг хэрэгсэл юм. Power Query нь текст файлаас эхлээд SQL, тэр байтугай Facebook хүртэлх бараг бүх мэдээллийн эх сурвалжийг дэмждэг 🙂

Хэрэв танд Excel 2013 эсвэл 2016 байхгүй бол та цааш унших боломжгүй (зүгээр л тоглож байна). Excel-ийн хуучин хувилбаруудад ийм даалгаврыг зөвхөн Visual Basic-д макро програмчлах (энэ нь эхлэгчдэд маш хэцүү байдаг) эсвэл гар аргаар нэг хэвийн хуулах замаар (энэ нь удаан хугацаа зарцуулж, алдаа гаргадаг) гүйцэтгэнэ.

Алхам 1. Нэг файлыг жишээ болгон импортлох

Нэгдүгээрт, жишээ болгон нэг ажлын номноос өгөгдлийг импортолъё, ингэснээр Excel "санааг авах" болно. Үүнийг хийхийн тулд шинэ хоосон ажлын дэвтэр үүсгэж,…

• Хэрэв танд Excel 2016 байгаа бол табыг нээнэ үү Өгөгдөл Тэгээд Асуулга үүсгэх - Файлаас - Номоос (Өгөгдөл - Шинэ асуулга - Файлаас - Excel-ээс)
• Хэрэв танд Power Query нэмэлт суулгасан Excel 2010-2013 байгаа бол табыг нээнэ үү. Эрчим хүчний асуулга дээр нь сонго Файлаас - Номоос (Файлаас - Excel-ээс)

Дараа нь нээгдэх цонхон дээр манай тайлангийн хавтас руу очоод хотын аль ч файлыг сонгоно уу (аль нь хамаагүй, учир нь тэдгээр нь бүгд ердийн зүйл юм). Хэдэн секундын дараа Navigator цонх гарч ирэх бөгөөд та зүүн талд шаардлагатай хуудсыг (Борлуулалт) сонгох хэрэгтэй бөгөөд түүний агуулга баруун талд гарч ирнэ.

Хэрэв та энэ цонхны баруун доод буланд байгаа товчлуур дээр дарвал Татаж авах (Ачаалах), дараа нь хүснэгтийг нэн даруй анхны хэлбэрээр нь хуудас руу оруулах болно. Нэг файлын хувьд энэ нь сайн, гэхдээ бид ийм олон файлыг ачаалах шаардлагатай тул бид арай өөрөөр явж, товчлуур дээр дарна. Залруулга (Засварлах). Үүний дараа Power Query асуулгын засварлагчийг номынхоо өгөгдлийн хамт тусдаа цонхонд харуулах ёстой.

Энэ бол хүснэгтийг бидэнд хэрэгтэй харагдахаар "дуусгах" боломжийг олгодог маш хүчирхэг хэрэгсэл юм. Түүний бүх функцийг өнгөцхөн тайлбарлах нь зуу орчим хуудас шаардагдах боловч маш товчхон бол энэ цонхыг ашиглан та дараахь зүйлийг хийх боломжтой.

• шаардлагагүй өгөгдөл, хоосон мөр, алдаатай мөрүүдийг шүүнэ
• өгөгдлийг нэг буюу хэд хэдэн баганаар эрэмбэлэх
• давтахаас салах
• наалттай текстийг баганаар (хязгаарлагч, тэмдэгтийн тоо гэх мэт) хуваах.
• текстийг дарааллаар нь байрлуулах (нэмэлт зай арилгах, том үсгийг засах гэх мэт)
• өгөгдлийн төрлийг бүх аргаар хөрвүүлэх (текст гэх мэт тоонуудыг ердийн тоо болгон хувиргах ба эсрэгээр)
• Хүснэгтүүдийг шилжүүлэх (эргэх) ба хоёр хэмжээст хөндлөн хүснэгтүүдийг хавтгай болгон өргөжүүлэх
• Хүснэгтэд нэмэлт багана нэмж, Power Query-д суулгасан M хэлийг ашиглан тэдгээрт томьёо болон функцуудыг ашиглана уу.
• ...

Жишээлбэл, дараа нь пивот хүснэгтийн тайланг бүтээхэд хялбар байхын тулд хүснэгтэд тухайн сарын текстийн нэр бүхий баганыг нэмье. Үүнийг хийхийн тулд баганын гарчиг дээр хулганы баруун товчийг дарна уу он сар өдөрболон командыг сонгоно уу Давхардсан багана (Давхардсан багана), дараа нь гарч ирэх давхардсан баганын толгой дээр хулганы баруун товчийг дараад "Commands"-ыг сонгоно уу. Хувиргах - Сар - Сарын нэр:

Мөр бүрийн хувьд тухайн сарын текстийн нэр бүхий шинэ багана үүсгэх ёстой. Баганын гарчиг дээр давхар товшсоноор та нэрийг нь өөрчлөх боломжтой Хуулбарлах огноо илүү тохь тухтай болгох Сараар, жишээ нь.

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

Та энгийн шүүлтүүр ашиглан алдаатай эсвэл хоосон мөр, шаардлагагүй менежер эсвэл үйлчлүүлэгчдийг хасч болно.

Нэмж дурдахад, гүйцэтгэсэн бүх өөрчлөлтүүд нь баруун талын самбарт бэхлэгдсэн бөгөөд тэдгээрийг үргэлж буцааж (хөндлөн) эргүүлэх эсвэл параметрүүдийг (араа) өөрчлөх боломжтой.

Хөнгөн, дэгжин, тийм үү?

Алхам 2. Хүсэлтээ функц болгон хувиргацгаая

Импортолсон ном бүрт хийсэн бүх өгөгдлийн хувиргалтыг дараа нь давтахын тулд бид үүсгэсэн хүсэлтээ функц болгон хувиргах хэрэгтэй бөгөөд энэ нь эргээд бидний бүх файлд хэрэглэгдэх болно. Үүнийг хийх нь үнэндээ маш энгийн.

Query Editor-ийн View tab руу очоод товчийг дарна уу Нарийвчилсан редактор (Харах — Нарийвчилсан редактор). Бидний өмнөх бүх үйлдлүүд M хэл дээр код хэлбэрээр бичигдэх цонх нээгдэх ёстой. Бидний жишээн дээр импортолсон файлын зам кодонд хатуу кодлогдсон болохыг анхаарна уу:

Одоо хэд хэдэн тохируулга хийцгээе:

Тэдний утга нь энгийн: эхний мөр (файлын зам)=> бидний процедурыг аргументтай функц болгон хувиргадаг Файлын зам, мөн доор нь бид энэ хувьсагчийн утга руу тогтмол замыг өөрчилдөг. 

Бүгд. Дээр товшино уу Finish мөн үүнийг харах ёстой:

Өгөгдөл алга болсон гэж бүү ай - үнэн хэрэгтээ бүх зүйл хэвийн, бүх зүйл ийм байх ёстой 🙂 Бид өгөгдөл импортлох, боловсруулах бүх алгоритмыг тодорхой файлд холбохгүйгээр санах боломжтой захиалгат функцийг амжилттай бүтээсэн. . Энэ нь илүү ойлгомжтой нэр өгөх хэвээр байна (жишээлбэл өгөгдөл авах) талбарын баруун талд байгаа самбарт нэр мөн та хурааж чадна Нүүр хуудас — Хаах ба татаж авах (Нүүр хуудас — Хаах ба ачаалах). Бидний жишээн дээр импортолсон файлын зам кодонд хатуу кодлогдсон болохыг анхаарна уу. Та Microsoft Excel-ийн үндсэн цонх руу буцах болно, гэхдээ баруун талд манай функцтэй холболт үүсгэсэн самбар гарч ирнэ.

Алхам 3. Бүх файлыг цуглуулах

Хамгийн хэцүү хэсэг нь ард үлдэж, тааламжтай, хялбар хэсэг нь үлддэг. Таб руу оч Өгөгдөл - Асуулга үүсгэх - Файлаас - Хавтаснаас (Өгөгдөл - Шинэ асуулга - Файлаас - Хавтаснаас) эсвэл, хэрэв танд Excel 2010-2013 байгаа бол табтай адил Эрчим хүчний асуулга. Гарч ирэх цонхонд манай бүх эх хотын файлууд байгаа хавтсыг зааж өгөөд дарна уу OK. Дараагийн алхам нь энэ хавтсанд (болон түүний дэд хавтаснууд) олдсон бүх Excel файлууд болон тэдгээрийн дэлгэрэнгүй мэдээллийг жагсаах цонхыг нээх болно.

Дарах Change (Засварлах) Бид дахин танил асуулга засварлагч цонх руу орно.

Одоо бид файл бүрээс өгөгдлийг "татах" функцээр хүснэгтдээ өөр багана нэмэх хэрэгтэй. Үүнийг хийхийн тулд таб руу очно уу Багана нэмэх - Тусгай багана (Багана нэмэх - Тусгай багана нэмэх) гарч ирэх цонхонд бидний функцийг оруулна уу өгөгдөл авах, файл бүрийн бүтэн замыг аргумент болгон зааж өгнө үү:

Үргэлжлүүлсний дараа OK үүсгэсэн баганыг баруун талд байгаа манай хүснэгтэд нэмэх ёстой.

Одоо бүх шаардлагагүй баганыг устгацгаая (Excel дээр байгаа шиг хулганы баруун товчийг ашиглан - Remove), зөвхөн нэмсэн багана болон файлын нэртэй баганыг үлдээнэ, учир нь энэ нэр (илүү нарийвчлалтай, хот) мөр бүрийн нийт өгөгдөлд ашигтай байх болно.

Одоо "хөөх мөч" - нэмсэн баганын баруун дээд буланд байрлах өөрийн гэсэн сумтай дүрс дээр дарна уу.

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

Бүрэн гоо сайхныг бий болгохын тулд та файлын нэр бүхий эхний баганаас .xlsx өргөтгөлүүдийг устгаж болно - стандарт "юу ч" гэж солих замаар (баганын толгой дээр хулганы баруун товчийг дарна уу -). Орлуулагч) болон энэ баганын нэрийг өөрчил Хот. Мөн огноотой баганад байгаа өгөгдлийн форматыг засна уу.

Бүгд! Дээр товшино уу Нүүр хуудас - Хаах ба ачаалах (Нүүр хуудас — Хаах, ачаалах). Бүх хотуудын асуулгад цуглуулсан бүх өгөгдлийг одоогийн Excel хуудсанд "ухаалаг хүснэгт" форматаар байршуулах болно.

Үүсгэсэн холболт болон бидний угсралтын функцийг ямар ч байдлаар тусад нь хадгалах шаардлагагүй - тэдгээрийг одоогийн файлын хамт ердийн аргаар хадгалдаг.

Цаашид хавтас (хот нэмэх, хасах) эсвэл файлд (мөрийн тоог өөрчлөх) өөрчлөлт гарсан тохиолдолд хүснэгт эсвэл баруун самбар дээрх асуулга дээр хулганы баруун товчийг дарахад хангалттай. тушаал Шинэчлэх & Хадгалах (Сэргээх) – Power Query нь хэдхэн секундын дотор бүх өгөгдлийг дахин "дахин бүтээх" болно.

PS

Нэмэлт өөрчлөлт. 2017 оны XNUMX-р сарын шинэчлэлтүүдийн дараа Power Query нь Excel-ийн ажлын номыг хэрхэн цуглуулж сурсан, өөрөөр хэлбэл тусдаа функц хийх шаардлагагүй болсон - энэ нь автоматаар хийгддэг. Тиймээс, энэ нийтлэлийн хоёр дахь алхам шаардлагагүй болж, бүх үйл явц мэдэгдэхүйц хялбар болно:

1. сонгоно уу Хүсэлт үүсгэх – Файлаас – Фолдероос – Хавтас сонгох – OK
2. Файлуудын жагсаалт гарч ирсний дараа товчийг дарна уу Change
3. Query Editor цонхноос хоёртын баганыг давхар сумаар өргөжүүлж, файл бүрээс авах хуудасны нэрийг сонгоно уу.

Тэгээд л болоо! Дуу!

• Хөндлөн табыг пивот хүснэгт бүтээхэд тохиромжтой хавтгай болгож дахин төлөвлө
• Power View дээр хөдөлгөөнт бөмбөлөг график үүсгэж байна
• Янз бүрийн Excel файлуудын хуудсыг нэг дор цуглуулах макро