Олон номноос өөр өөр толгойтой хүснэгтүүдийг бүтээх

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

Бид нэг хавтсанд хэд хэдэн файл (бидний жишээнд - 4 ширхэг, ерөнхий тохиолдолд - таны хүссэн хэмжээгээр) байна тайлан:

Дотор нь эдгээр файлууд дараах байдалтай байна.

Үүнд:

• Бидэнд хэрэгтэй мэдээллийн хуудсыг үргэлж дууддаг Фото зураг, гэхдээ ажлын дэвтрийн хаана ч байж болно.
  
• Хуудасны цаана Фото зураг Ном бүр өөр хуудастай байж болно.
• Өгөгдөл бүхий хүснэгтүүд нь өөр өөр мөртэй байх ба ажлын хуудасны өөр мөрөөр эхэлж болно.
• Өөр өөр хүснэгтүүдийн ижил баганын нэр өөр байж болно (жишээлбэл, Тоо хэмжээ = Тоо хэмжээ = Тоо хэмжээ).
• Хүснэгт дэх багануудыг өөр дарааллаар байрлуулж болно.

Даалгавар: хуудасны бүх файлаас борлуулалтын мэдээллийг цуглуул Фото зураг Дараа нь хураангуй эсвэл бусад аналитикийг бий болгохын тулд нэг нийтлэг хүснэгтэд оруулаарай.

Алхам 1. Баганын нэрсийн лавлах бэлтгэх

Хамгийн эхний хийх зүйл бол баганын нэр, тэдгээрийн зөв тайлбар бүхий лавлах ном бэлтгэх явдал юм.

Бид таб дээрх Хүснэгт болгон форматлах товчийг ашиглан энэ жагсаалтыг динамик "ухаалаг" хүснэгт болгон хувиргадаг Нүүр хуудас (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах) эсвэл гарын товчлол Ctrl+T тушаалаар Power Query руу ачаална уу Өгөгдөл - Хүснэгтээс/Мужаас (Өгөгдөл - Хүснэгтээс/Мужаас). Excel-ийн сүүлийн хувилбаруудад нэрийг нь өөрчилсөн Навчтай (Хуудаснаас).

Power Query асуулгын засварлагч цонхонд бид уламжлал ёсоор алхамыг устгадаг Өөрчлөгдсөн төрөл товчлуур дээр дарж оронд нь шинэ алхам нэмнэ үү f_xтомъёоны мөрөнд (хэрэв энэ нь харагдахгүй бол таб дээрээс идэвхжүүлж болно тойм) болон томьёог суулгасан Power Query хэлний M-д оруулна уу:

=Хүснэгт.Мөрүүд(Эх сурвалж)

Энэ тушаал нь өмнөх алхамд ачаалагдсаныг хөрвүүлнэ Эх сурвалж лавлагаа хүснэгтийг үүрлэсэн жагсаалтаас (Жагсаалт) бүрдэх жагсаалт болгон хувиргах ба тэдгээр нь тус бүр нь эргээд хос утгууд юм. Энэ бол - болсон нэг мөрөөс:

Хэсэг хугацааны дараа ачаалагдсан бүх хүснэгтийн толгойн нэрийг өөрчлөх үед бидэнд ийм төрлийн өгөгдөл хэрэгтэй болно.

Хөрвүүлэлт дууссаны дараа командуудыг сонгоно уу Нүүр хуудас — Хаах ба ачаалах — Хаах ба ачаалах... импортын төрөл Зүгээр л холболт үүсгэ (Нүүр хуудас — Хаах&Ачаалах — Хаах&Ачаалах... — Зөвхөн холболт үүсгэх) Тэгээд Excel рүү буцна уу.

Алхам 2. Бид бүх файлаас бүгдийг байгаагаар нь ачаална

Одоо бүх файлынхаа агуулгыг хавтаснаас ачаалцгаая - одоо байгаагаар. Багуудыг сонгох Өгөгдөл - Өгөгдөл авах - Файлаас - Хавтаснаас (Өгөгдөл - Өгөгдөл авах - Файлаас - Хавтаснаас) тэгээд манай эх номнууд байгаа хавтас.

Урьдчилан харах цонхонд товшино уу Хөрвүүлэх (Өөрчлөх) or Change (Засварлах):

Дараа нь татаж авсан бүх файлын агуулгыг өргөжүүлнэ үү (хоёртын систем) баганын гарчиг дахь давхар сумтай товчлуур Агуулга:

Эхний файлын жишээн дээрх Power Query (Vostok.xlsx) биднээс ажлын дэвтэр бүрээс авахыг хүссэн хуудасны нэрийг асуух болно - сонгоно уу Фото зураг болон OK дарна уу:

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

1. Power Query нь хавтсаас эхний файлыг авах болно (бидэнд байх болно Vostok.xlsx — харах Файлын жишээ) жишээ болгон, асуулга үүсгэх замаар агуулгыг нь импортлодог Жишээ файлыг хөрвүүлэх. Энэ асуулга нь хэд хэдэн энгийн алхамтай байх болно Эх сурвалж (файл хандалт) Жолоодлогын (хуудас сонгох) болон гарчгийг нэмэгдүүлэх боломжтой. Энэ хүсэлт нь зөвхөн нэг тодорхой файлаас өгөгдлийг ачаалах боломжтой Vostok.xlsx.
   
2. Энэ хүсэлт дээр үндэслэн түүнтэй холбоотой функцийг үүсгэнэ Файл хөрвүүлэх (онцлог дүрсээр тэмдэглэгдсэн f_x), эх файл нь тогтмол байхаа больж, хувьсах утга – параметр болно. Тиймээс энэ функц нь бидний аргумент болгон оруулдаг аливаа номноос өгөгдлийг гаргаж авах боломжтой.
   
3. Функцийг баганаас файл бүрт (Хоёртын файл) ээлжлэн хэрэглэнэ Агуулга - алхам нь үүнийг хариуцдаг Захиалгат функцийг дуудах файлын жагсаалтад багана нэмдэг бидний асуулгад Файл хөрвүүлэх Ажлын ном бүрээс импортын үр дүнгийн хамт:

   

4. Нэмэлт багануудыг устгасан.
5. Оруулсан хүснэгтүүдийн агуулгыг өргөжүүлсэн (алхам Өргөтгөсөн хүснэгт багана) – мөн бид бүх номноос мэдээлэл цуглуулсны эцсийн үр дүнг харж байна:

   
   

Алхам 3. Зүлгүүр

Өмнөх дэлгэцийн агшинд шууд угсралт "байгаагаараа" чанар муутай болсон нь тодорхой харагдаж байна.

• Баганууд нь эсрэгээрээ байна.
• Олон тооны нэмэлт мөрүүд (хоосон, зөвхөн биш).
• Хүснэгтийн толгойг гарчиг гэж ойлгодоггүй бөгөөд өгөгдөлтэй холилддог.
  

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

Хүсэлт нээх замаар Жишээ файлыг хөрвүүлэх, шаардлагагүй мөрүүдийг шүүх алхмуудыг нэмнэ үү (жишээлбэл, баганаар Column2) болон гарчгийг товчлуураар дээшлүүлнэ Эхний мөрийг толгой болгон ашигла (Эхний мөрийг гарчиг болгон ашиглах). Хүснэгт нь илүү сайхан харагдах болно.

Өөр өөр файлуудын багануудыг дараа нь автоматаар бие биенийхээ доор байрлуулахын тулд тэдгээрийг ижил нэртэй байх ёстой. Та өмнө нь үүсгэсэн лавлахын дагуу нэг мөр M-кодын дагуу ийм бөөнөөр нэрлэх боломжтой. Дахин товчлуурыг дарцгаая f_x томьёоны мөрөнд функц нэмж, өөрчлөх:

= Хүснэгт.Багануудын нэрийг өөрчлөх(#”Өндөр толгой”, Толгой хэсэг, MissingField. Үл тоомсорлох)

Энэ функц нь өмнөх алхамын хүснэгтийг авдаг Өргөгдсөн толгой мөн доторх бүх баганын нэрийг үүрлэсэн хайлтын жагсаалтын дагуу өөрчлөнө Гарчиг. Гурав дахь аргумент MissingField. Үл тоох Энэ нь лавлахад байгаа боловч хүснэгтэд байхгүй байгаа гарчигуудад алдаа гарахгүй байх шаардлагатай.

Үнэндээ бол энэ л байна.

Хүсэлт рүү буцах тайлан Бид огт өөр дүр зургийг харах болно - өмнөхөөсөө хамаагүй сайхан:

• Power Query, Power Pivot, Power BI гэж юу вэ, Excel хэрэглэгчдэд яагаад хэрэгтэй вэ
  
• Өгөгдсөн хавтас дахь бүх файлаас өгөгдөл цуглуулах
• Номын бүх хуудаснаас өгөгдлийг нэг хүснэгтэд цуглуулах