Logos of the Microsoft Office component Excel on a heap. Copy space. Web banner format.

รับรายการแผ่นงาน Excel ด้วย Power Query

โลโก้คอมโพเนนต์ Microsoft Office Excel บนสแต็ก  พื้นที่คัดลอก  รูปแบบแบนเนอร์เว็บ
รูปถ่าย: Andreas Prout / Adobe Stock

เมื่อไร Microsoft Excel สมุดงานมีแผ่นงานจำนวนมาก และบ่อยครั้งที่แท็บแผ่นงานที่คุณต้องการไม่อยู่ในสายตา ซึ่งน่ารำคาญและทำให้คุณช้าลงเล็กน้อย มีวิธีแก้ปัญหามากมาย แต่รายชื่อชีตอาจมีประโยชน์ โดยเฉพาะอย่างยิ่งหากผู้ใช้ไม่มีทักษะพื้นฐานของ Excel เมื่อคุณมีรายชื่อแผ่นงานแล้ว ผู้ใช้สามารถใช้เพื่อนำทางในเวิร์กบุ๊กได้ มีวิธีง่ายๆ ในการสร้างรายการชีตผ่าน Microsoft Power Query

ในบทช่วยสอนนี้ ฉันจะแสดงวิธีใช้ Power Query เพื่อสร้างรายชื่อชีตที่คุณสามารถโหลดลงใน Excel และใช้เพื่อนำทางในเวิร์กบุ๊ก แนวทางของเอกสารนี้น่าสนใจ แต่จุดสนใจที่แท้จริงคือการเปิดเผยข้อมูลเมตาของเวิร์กบุ๊กด้วย Power Query

ที่เราเห็น: Google Workspace กับ Microsoft 365: การวิเคราะห์พร้อมกับรายการตรวจสอบ (TechRepublic พรีเมี่ยม)

ฉันใช้ Microsoft 365 บน Windows 10 64 บิต แต่คุณสามารถใช้ Excel เวอร์ชันก่อนหน้าบน Excel 2010 ได้ Excel สำหรับเว็บ ไม่สนับสนุน Power Query แม้ว่าจะสนับสนุนการสอบถามและการเชื่อมต่อที่มีอยู่

คุณสามารถ ดาวน์โหลดไฟล์สาธิต Microsoft Excel สำหรับบทช่วยสอนนี้

วิธีปิดการใช้งาน OneDrive

ก่อนจะทำอะไรต้องปิดการใช้งาน วันไดรฟ์. หากไม่ทำเช่นนั้น คุณจะได้รับข้อผิดพลาดว่ามีคนอื่นกำลังใช้เวิร์กบุ๊กอยู่ ดังนั้นคุณจึงไม่สามารถเข้าถึงได้ ผู้ร้ายคือ OneDrive

หากต้องการปิดใช้งาน OneDrive ชั่วคราว ให้ค้นหาในแถบงานหรือพื้นที่ล้น (รูป A). คลิกไอคอนรูปเมฆสีน้ำเงิน แล้วคลิกการตั้งค่า – เฟืองที่มุมบนขวา ในรายการผลลัพธ์ ให้เลือกออกจาก OneDrive ยืนยันการดำเนินการโดยเลือกปิด OneDrive เมื่อได้รับพร้อมท์ หากคุณเป็นสมาชิก 365 คุณกำลังใช้ OneDrive ไม่ว่าคุณจะรู้ตัวหรือไม่ก็ตาม

รูป A

ปิดใช้งาน OneDrive ชั่วคราว
ปิดใช้งาน OneDrive ชั่วคราว

นี่เป็นเงื่อนไขชั่วคราว เมื่อเสร็จแล้ว คุณจะต้องเปิดใช้งาน OneDrive อีกครั้ง ซึ่งฉันจะแสดงวิธีการดำเนินการในภายหลัง

วิธีเรียกใช้ Power Query

เราจะทำงานกับสมุดงาน Excel อย่างง่ายดังที่แสดงใน รูป Bกระดาษเปล่าหกแผ่น แผ่นงานแผ่นหนึ่งยังมีช่วงที่มีชื่อซึ่งระบุด้วยเส้นขอบ เพื่อจุดประสงค์ในการสาธิต เราไม่ต้องการข้อมูลใดๆ เนื่องจากเรากำลังมองหาข้อมูลเมตาของเวิร์กบุ๊ก ซึ่งรวมถึงชื่อชีต

รูป B

โหลดข้อมูล Excel ลงใน Power Query
โหลดข้อมูล Excel ลงใน Power Query

เมื่อคุณปิดใช้งาน OneDrive คุณก็พร้อมที่จะเรียกใช้ Power Query แล้ว:

1. จากภายในสมุดงาน Excel ที่มีแท็บทั้งหมด ให้คลิกที่แท็บ ข้อมูล

2. ในกลุ่ม Data Acquisition & Transformation ให้คลิกเมนูแบบเลื่อนลง Get Data แล้วเลือก From File

3. ถัดไป เลือกจากสมุดงาน Excel

4. เมื่อได้รับแจ้ง ให้ค้นหาและเลือกเวิร์กบุ๊กเดียวกัน จากนั้นคลิก นำเข้า นี่คือที่ที่คุณจะประสบปัญหาหากคุณไม่ปิดใช้งาน OneDrive ก่อน หากเกิดเหตุการณ์นี้กับคุณ ให้ปิดทุกอย่างและปิดใช้งาน OneDrive ตามที่อธิบายไว้ข้างต้น

5. ในบานหน้าต่างผลลัพธ์ ให้เลือกแผ่นงานใดก็ได้ (รูป C). คุณต้องการเพียงหนึ่ง โปรดทราบว่ารายการยังมีช่วงที่มีชื่อคือ SheetANamedRange เราจะไม่ใช้มัน แต่ฉันต้องการให้คุณเห็นว่ากระบวนการอัปโหลดนี้มีมากกว่าเอกสาร นอกจากนี้ยังจะแสดงชื่อตาราง

รูป C

โหลดแผ่นงานเปล่าลงใน Power Query
โหลดแผ่นงานเปล่าลงใน Power Query

6. คลิก แปลงข้อมูล เพื่อเริ่ม Power Query

ในขณะนี้ Power Query ว่างเปล่า

วิธีแสดงข้อมูลเมตาของไฟล์ Excel ใน Power Query

ไม่มีข้อมูลใน Power Query แต่นั่นคือสิ่งที่เราต้องการ ซึ่งช่วยให้เราเปิดเผยข้อมูลเมตาของไฟล์ได้

ทางด้านขวา รายการขั้นตอนประยุกต์มีสามขั้นตอน แต่เราต้องเก็บเฉพาะบรรทัดแรกซึ่งเป็นต้นทาง ลบการนำทางและประเภทตัวแปรโดยคลิก X ทางด้านซ้ายของแต่ละขั้นตอน การทำเช่นนี้จะเปิดเผยข้อมูลเมตาของเวิร์กบุ๊กดังที่แสดงใน รูป d.

รูป d

เปิดเผยข้อมูลเมตาของไฟล์
เปิดเผยข้อมูลเมตาของไฟล์

คอลัมน์ Type จะแสดงประเภทของรายการ มีหกเอกสารและหนึ่งชื่อเฉพาะ เราต้องการเพียงหกแผ่นเท่านั้น ดังนั้นขั้นตอนแรกของเราคือกรองแถวชื่อที่กำหนด เมื่อใช้เทคนิคนี้กับข้อมูลของคุณเอง คุณอาจไม่มีชื่อที่ระบุ แต่เราขอแนะนำให้ใช้ตัวกรองต่อไป หากคุณวางแผนที่จะใช้แบบสอบถามนี้ซ้ำ เมื่อคุณอัปเดตรายการในภายหลัง คุณอาจมีช่วงหรือตารางที่มีชื่อ

ในการใช้ตัวกรองเพื่อแสดงเฉพาะแผ่นงาน ให้ทำดังต่อไปนี้:

1. คลิกเมนูแบบเลื่อนลงคอลัมน์ประเภท

2. ในรายการผลลัพธ์ ให้ยกเลิกการเลือกทุกอย่างยกเว้นชีต (รูป E).

รูป E

ลบทุกอย่างยกเว้นแผ่น
ลบทุกอย่างยกเว้นแผ่น

3. คลิกตกลงเพื่อใช้ตัวกรอง

รูปร่างและ แสดงระเบียนผลลัพธ์ ไม่มีแถวสำหรับชื่อที่ระบุ เมื่อคุณนำไปใช้กับงานของคุณเอง ข้อมูลเมตาอาจมีองค์ประกอบอื่นที่ไม่ใช่ชื่อแผ่นงาน ตรวจสอบให้แน่ใจว่าทุกอย่างยกเว้นแผ่นงานถูกกรอง

รูปร่างและ

แถวชื่อที่ระบุหายไป
แถวชื่อที่ระบุหายไป

ณ จุดนี้ เราต้องลบคอลัมน์ทั้งหมดยกเว้นคอลัมน์ชื่อ มาทำกัน

1. คลิกขวาที่ส่วนหัวของชื่อเพื่อเลือกคอลัมน์นี้

2. เลือก ลบคอลัมน์อื่น จากเมนูย่อยที่เป็นผลลัพธ์

หลังจากลบทุกอย่างยกเว้นคอลัมน์ชื่อแผ่นงานแล้ว ให้คลิกโหลดและปิดเพื่อโหลดรายการลงใน Excel ผลลัพธ์คือตารางในชีตใหม่ที่เรียกว่า APR(2)

วิธีใช้เมนูเพื่อย้ายระหว่างแผ่นงานใน Excel

วิธีที่คุณใช้ตารางชื่อแผ่นงานนั้นขึ้นอยู่กับคุณ แต่เราจะสร้างคอลัมน์ของไฮเปอร์ลิงก์สำหรับการนำทางอย่างรวดเร็ว หากต้องการแปลงชื่อแผ่นงานเป็นไฮเปอร์ลิงก์ ให้ป้อนฟังก์ชันต่อไปนี้ใน B2 แล้วคัดลอกไปยังเซลล์ที่เหลือ ดังที่แสดงใน รูปร่าง g:

=HYPERLINK("[PQSheetHyperlinks_Demo.xlsx]" & A2 & "!A1",A2)

รูปร่าง g

เพิ่มไฮเปอร์ลิงก์ไปยังชื่อแผ่นงานเพื่อการเข้าถึงอย่างรวดเร็ว
เพิ่มไฮเปอร์ลิงก์ไปยังชื่อแผ่นงานเพื่อการเข้าถึงอย่างรวดเร็ว

ฟังก์ชันนี้ต้องใช้ชื่อแผ่นงานปัจจุบัน”[PQSheetHyperlinks_Demo.xlsx]’ แม้ว่าจะนำทางภายในเวิร์กบุ๊กเดียวกันก็ตาม คอมโพเนนต์ &A2 จะรวมชื่อของชีตใน A2 ระบุ ‘! A1″ A1 ของแผ่นงานที่เกี่ยวข้อง อาร์กิวเมนต์สุดท้าย A2 ระบุข้อความที่แสดง ในกรณีนี้คือชื่อของแผ่นงาน

วิธีอัปเดตตารางชื่อแผ่นงานใน Excel

เมื่อมีการเพิ่ม ลบ หรือเปลี่ยนชื่อแผ่นงาน คุณจะต้องอัปเดตรายการ ขั้นแรก บันทึกเวิร์กบุ๊ก จากนั้นคลิกขวาที่เมนูและเลือกรีเฟรชจากเมนูย่อยที่เป็นผลลัพธ์

หากคุณมี Microsoft 365 และ OneDrive คุณต้องเปิดใช้งาน OneDrive อีกครั้งก่อนจึงจะใช้งานได้ วิธีที่ง่ายที่สุดในการทำเช่นนี้คือการลงชื่อเข้าใช้บัญชี Microsoft 365 แล้วคลิกซิงค์ในเมนู

ค่อนข้างแปลกที่คุณต้องปิดการใช้งาน OneDrive เพื่อสร้างรายการ จากนั้นเปิดใช้งานเพื่ออัปเดตรายการ สิ่งนี้สามารถเปลี่ยนแปลงได้ด้วยการอัปเดตใดๆ

#รบรายการแผนงาน #Excel #ดวย #Power #Query

Leave a Comment

Your email address will not be published. Required fields are marked *