เนื้อหา
วิดีโอที่มีคนดูมากที่สุดในช่อง YouTube ของฉันคือวิดีโอเกี่ยวกับ Flash Fill ใน Microsoft Excel สาระสำคัญของเครื่องมือนี้คือ หากคุณต้องการแปลงข้อมูลต้นฉบับของคุณอย่างใด คุณเพียงแค่ต้องเริ่มพิมพ์ผลลัพธ์ที่คุณต้องการได้รับในคอลัมน์ที่อยู่ติดกัน หลังจากพิมพ์หลายเซลล์ด้วยตนเอง (ปกติ 2-3 เซลล์ก็เพียงพอ) Excel จะ "เข้าใจ" ตรรกะของการแปลงที่คุณต้องการและดำเนินการในสิ่งที่คุณพิมพ์ต่อไปโดยอัตโนมัติ ทำงานที่น่าเบื่อหน่ายทั้งหมดให้กับคุณ:
แก่นสารของประสิทธิภาพ ปุ่มวิเศษ "ทำถูกต้อง" ที่เราทุกคนรักมากใช่ไหม?
อันที่จริงมีอะนาล็อกของเครื่องมือดังกล่าวใน Power Query ซึ่งเรียกว่า คอลัมน์จากตัวอย่าง (คอลัมน์จากตัวอย่าง). อันที่จริง นี่คือปัญญาประดิษฐ์ขนาดเล็กที่สร้างขึ้นใน Power Query ที่สามารถเรียนรู้จากข้อมูลของคุณได้อย่างรวดเร็วแล้วแปลงข้อมูลนั้น มาดูความสามารถของมันในสถานการณ์เชิงปฏิบัติหลายๆ อย่างให้ละเอียดยิ่งขึ้น เพื่อทำความเข้าใจว่าสิ่งนี้มีประโยชน์ต่อเราในด้านใดบ้างในงานจริง
ตัวอย่างที่ 1 การติดกาว/ตัดข้อความ
สมมติว่าเรามีตารางที่ "ฉลาด" ใน Excel พร้อมข้อมูลเกี่ยวกับพนักงาน:
โหลดลงใน Power Query ด้วยวิธีมาตรฐาน – ด้วยปุ่ม จากตาราง/ช่วง แถบ ข้อมูล (ข้อมูล — จากตาราง/ช่วง).
สมมติว่าเราต้องเพิ่มคอลัมน์ที่มีนามสกุลและชื่อย่อสำหรับพนักงานแต่ละคน (Ivanov SV สำหรับพนักงานคนแรก ฯลฯ) ในการแก้ปัญหานี้ คุณสามารถใช้หนึ่งในสองวิธี:
- คลิกขวาที่ส่วนหัวของคอลัมน์ที่มีข้อมูลต้นฉบับและเลือกคำสั่ง เพิ่มคอลัมน์จากตัวอย่าง (เพิ่มคอลัมน์จากตัวอย่าง);
- เลือกหนึ่งคอลัมน์ขึ้นไปที่มีข้อมูลและบนแท็บ การเพิ่มคอลัมน์ เลือกทีม คอลัมน์จากตัวอย่าง. ที่นี่ในรายการแบบหล่นลง คุณสามารถระบุว่าต้องวิเคราะห์คอลัมน์ที่เลือกทั้งหมดหรือเฉพาะ
จากนั้นทุกอย่างก็เรียบง่าย – ในคอลัมน์ที่ปรากฏทางด้านขวา เราจะเริ่มป้อนตัวอย่างของผลลัพธ์ที่ต้องการ และปัญญาประดิษฐ์ที่สร้างขึ้นใน Power Query พยายามทำความเข้าใจตรรกะการเปลี่ยนแปลงของเราและดำเนินการต่อด้วยตัวของมันเอง:
อย่างไรก็ตาม คุณสามารถป้อนตัวเลือกที่ถูกต้องในเซลล์ใดๆ ของคอลัมน์นี้ เช่น ไม่จำเป็นต้องเรียงจากบนลงล่างและเรียงในแถว นอกจากนี้ คุณสามารถเพิ่มหรือลบคอลัมน์ออกจากการวิเคราะห์ได้อย่างง่ายดายในภายหลังโดยใช้ช่องทำเครื่องหมายในแถบชื่อ
ให้ความสนใจกับสูตรที่ด้านบนของหน้าต่าง – นี่คือสิ่งที่ Power Query อัจฉริยะสร้างขึ้นเพื่อให้ได้ผลลัพธ์ที่เราต้องการ อย่างไรก็ตาม นี่คือความแตกต่างพื้นฐานระหว่างเครื่องมือนี้กับ เติมทันที ใน Excel การบรรจุทันทีทำงานเหมือน "กล่องดำ" ซึ่งไม่ได้แสดงตรรกะของการเปลี่ยนแปลงให้เราทราบ แต่เพียงแค่ให้ผลลัพธ์สำเร็จรูปและเราถือว่ายอมรับ ทุกอย่างโปร่งใสที่นี่ และคุณสามารถเข้าใจได้อย่างชัดเจนว่าเกิดอะไรขึ้นกับข้อมูลอย่างแน่นอน
หากคุณเห็นว่า Power Query "จับความคิด" คุณสามารถกดปุ่มได้อย่างปลอดภัย OK หรือแป้นพิมพ์ลัด Ctrl+เข้าสู่ – คอลัมน์แบบกำหนดเองที่มีสูตรที่คิดค้นโดย Power Query จะถูกสร้างขึ้น อย่างไรก็ตาม ภายหลังสามารถแก้ไขได้ง่ายๆ เป็นคอลัมน์ที่สร้างเองตามปกติ (ด้วยคำสั่ง การเพิ่มคอลัมน์ – คอลัมน์ที่กำหนดเอง) โดยคลิกไอคอนรูปเฟืองทางด้านขวาของชื่อขั้นตอน:
ตัวอย่างที่ 2: กรณีในประโยค
หากคุณคลิกขวาที่ส่วนหัวของคอลัมน์ที่มีข้อความแล้วเลือกคำสั่ง การแปลง (แปลง)จากนั้นคุณจะเห็นคำสั่งสามคำสั่งที่รับผิดชอบในการเปลี่ยนการลงทะเบียน:
สะดวกและเท่ แต่ในรายการนี้ ส่วนตัวฉันขาดอีกหนึ่งตัวเลือกเสมอ - กรณีเช่นในประโยคเมื่อตัวพิมพ์ใหญ่ (ตัวพิมพ์ใหญ่) ไม่ใช่ตัวอักษรตัวแรกในแต่ละคำ แต่มีเพียงตัวอักษรตัวแรกในเซลล์และ ข้อความที่เหลือเมื่อ This จะแสดงด้วยอักษรตัวพิมพ์เล็ก (เล็ก)
คุณลักษณะที่ขาดหายไปนี้ใช้งานง่ายด้วยปัญญาประดิษฐ์ คอลัมน์จากตัวอย่าง – เพียงป้อนสองสามตัวเลือกสำหรับ Power Query เพื่อดำเนินการต่อในลักษณะเดียวกัน:
ตามสูตรในที่นี้ Power Query ใช้ฟังก์ชันมากมาย ข้อความด้านบน и ข้อความด้านล่าง, การแปลงข้อความเป็นตัวพิมพ์ใหญ่และตัวพิมพ์เล็กตามลำดับ และฟังก์ชั่น ข้อความเริ่มต้น и ข้อความกลาง - แอนะล็อกของฟังก์ชัน Excel LEFT และ PSTR สามารถแยกสตริงย่อยออกจากข้อความจากด้านซ้ายและจากตรงกลาง
ตัวอย่างที่ 3 การเรียงสับเปลี่ยนของคำ
บางครั้ง เมื่อประมวลผลข้อมูลที่ได้รับ จำเป็นต้องจัดเรียงคำในเซลล์ใหม่ตามลำดับที่กำหนด แน่นอน คุณสามารถแบ่งคอลัมน์ออกเป็นคอลัมน์คำแยกกันโดยใช้ตัวคั่น แล้วกาวกลับตามลำดับที่ระบุ (อย่าลืมเพิ่มช่องว่าง) แต่ด้วยความช่วยเหลือของเครื่องมือ คอลัมน์จากตัวอย่าง ทุกอย่างจะง่ายขึ้นมาก:
ตัวอย่างที่ 4: เฉพาะตัวเลข
งานที่สำคัญอีกอย่างหนึ่งคือการดึงเฉพาะตัวเลข (ตัวเลข) ออกจากเนื้อหาของเซลล์ เช่นเคย หลังจากโหลดข้อมูลลงใน Power Query แล้ว ให้ไปที่แท็บ การเพิ่มคอลัมน์ – คอลัมน์จากตัวอย่าง และกรอกข้อมูลในสองเซลล์ด้วยตนเองเพื่อให้โปรแกรมเข้าใจสิ่งที่เราต้องการอย่างแท้จริง:
บิงโก!
อีกครั้ง ควรดูที่ด้านบนของหน้าต่างเพื่อให้แน่ใจว่า Query สร้างสูตรอย่างถูกต้อง - ในกรณีนี้จะมีฟังก์ชัน ข้อความ. เลือกซึ่งคุณอาจเดาได้ว่าจะแยกอักขระที่กำหนดออกจากข้อความต้นฉบับตามรายการ ต่อจากนี้ คุณสามารถแก้ไขรายการนี้ได้อย่างง่ายดายในแถบสูตร หากจำเป็น
ตัวอย่างที่ 5: ข้อความเท่านั้น
เช่นเดียวกับตัวอย่างก่อนหน้านี้ คุณสามารถดึงออกมาและในทางกลับกัน – เฉพาะข้อความ การลบตัวเลขทั้งหมด เครื่องหมายวรรคตอน ฯลฯ
ในกรณีนี้ จะใช้ฟังก์ชันที่มีความหมายตรงกันข้ามอยู่แล้ว – Text.Remove ซึ่งจะลบอักขระออกจากสตริงดั้งเดิมตามรายการที่กำหนด
ตัวอย่างที่ 6: การดึงข้อมูลจากโจ๊กที่เป็นตัวอักษรและตัวเลข
Power Query สามารถช่วยในกรณีที่ยากขึ้นเช่นกัน เมื่อคุณต้องการดึงข้อมูลที่เป็นประโยชน์จากโจ๊กที่เป็นตัวอักษรและตัวเลขในเซลล์ เช่น รับหมายเลขบัญชีจากคำอธิบายของวัตถุประสงค์ในการชำระเงินในใบแจ้งยอดจากธนาคาร:
โปรดทราบว่าสูตรการแปลง Power Query ที่สร้างนั้นค่อนข้างซับซ้อน:
เพื่อความสะดวกในการอ่านและทำความเข้าใจ มันสามารถแปลงเป็นรูปแบบที่มีเหตุผลมากขึ้นโดยใช้บริการออนไลน์ฟรี ตัวจัดรูปแบบ Power Query:
สิ่งที่มีประโยชน์มาก – เคารพผู้สร้าง!
ตัวอย่างที่ 7: การแปลงวันที่
เครื่องมือ คอลัมน์จากตัวอย่าง สามารถใช้กับคอลัมน์วันที่หรือวันที่และเวลาได้เช่นกัน เมื่อคุณป้อนตัวเลขแรกของวันที่ Power Query จะแสดงรายการตัวเลือกการแปลงที่เป็นไปได้ทั้งหมดที่เป็นประโยชน์:
คุณจึงสามารถแปลงวันที่เดิมเป็นรูปแบบแปลกใหม่ได้อย่างง่ายดาย เช่น “ปี-เดือน-วัน”:
ตัวอย่างที่ 8: การจัดหมวดหมู่
ถ้าเราใช้เครื่องมือ คอลัมน์จากตัวอย่าง ในคอลัมน์ที่มีข้อมูลตัวเลข การทำงานจะแตกต่างออกไป สมมติว่าเราโหลดผลการทดสอบพนักงานลงใน Power Query (คะแนนตามเงื่อนไขในช่วง 0-100) และเราใช้การไล่ระดับตามเงื่อนไขต่อไปนี้:
- ปรมาจารย์ – ผู้ที่ทำคะแนนได้มากกว่า 90
- ผู้เชี่ยวชาญ – คะแนนจาก 70 ถึง 90
- ผู้ใช้ – ตั้งแต่ 30 ถึง 70
- ผู้เริ่มต้น – ผู้ที่ได้คะแนนน้อยกว่า 30
ถ้าเราเพิ่มคอลัมน์จากตัวอย่างลงในรายการและเริ่มจัดเรียงการไล่สีเหล่านี้ด้วยตนเอง ในไม่ช้า Power Query จะรับแนวคิดของเราและเพิ่มคอลัมน์ที่มีสูตร โดยที่ตัวดำเนินการซ้อนกัน if ตรรกะจะถูกนำไปใช้ คล้ายกับสิ่งที่เราต้องการมาก:
อีกครั้งคุณไม่สามารถกดสถานการณ์จนจบ แต่คลิกที่ OK แล้วแก้ไขค่าเกณฑ์ในสูตรให้ถูกต้อง – เร็วกว่าด้วยวิธีนี้:
สรุป
แน่นอนเครื่องมือ คอลัมน์จากตัวอย่าง ไม่ใช่ "ยาวิเศษ" และไม่ช้าก็เร็วจะมีสถานการณ์ที่ไม่ได้มาตรฐานหรือกรณีที่ถูกละเลยโดยเฉพาะอย่างยิ่งของ "ฟาร์มรวม" ในข้อมูลเมื่อ Power Query จะล้มเหลวและจะไม่สามารถทำงานตามที่เราต้องการได้ อย่างถูกต้องสำหรับเรา อย่างไรก็ตาม เป็นเครื่องมือเสริมได้ดีมาก นอกจากนี้ ด้วยการศึกษาสูตรที่เขาสร้างขึ้น คุณยังสามารถเพิ่มพูนความรู้ของคุณเกี่ยวกับฟังก์ชันของภาษา M ซึ่งจะมีประโยชน์เสมอในอนาคต
- การแยกวิเคราะห์ข้อความด้วยนิพจน์ทั่วไป (RegExp) ใน Power Query
- การค้นหาข้อความคลุมเครือใน Power Query
- เติมแฟลชใน Microsoft Excel