วิธีใช้คอลัมน์ตามเงื่อนไขใน Excel Power Query

สูตรใน Excel นั้นยอดเยี่ยมจนกว่าคุณจะต้องซ้อน 10 สูตรเข้าด้วยกัน—วงเล็บผิดเพียงอันเดียวสามารถทำลายช่วงบ่ายทั้งหมดของคุณได้ คุณสามารถข้ามความปวดหัวนี้โดยการย้ายตรรกะนั้นไปยัง Power Query คอลัมน์ตามเงื่อนไขช่วยให้คุณสร้างกฎ "if/then" ได้อย่างเห็นภาพ ทำให้ข้อมูลของคุณสะอาดและจัดการง่ายขึ้น

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

เชี่ยวชาญการใช้เครื่องมือเพิ่มคอลัมน์ตามเงื่อนไข

ทิ้งวงเล็บแห่งความหายนะ

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

ในขณะที่ Power Query สามารถดึงข้อมูลจากช่วงของ Excel ใด ๆ ได้ ผมขอแนะนำอย่างยิ่งให้คุณจัดรูปแบบข้อมูลของคุณเป็นตาราง Excel (Ctrl+T) ก่อน ตารางมีความยืดหยุ่น ดังนั้นเมื่อคุณเพิ่มแถวใหม่ Power Query จะรวมแถวนั้นในการรีเฟรชการค้นหาในครั้งถัดไป เมื่อข้อมูลของคุณถูกจัดรูปแบบและตารางของคุณมีชื่อ:

1. เลือกเซลล์ใดก็ได้ในตารางของคุณ จากนั้นไปที่แท็บ ข้อมูล แล้วคลิก จากตาราง/ช่วง เพื่อเปิดตัวแก้ไข Power Query
2. เปิดแท็บ เพิ่มคอลัมน์ และคลิก คอลัมน์ตามเงื่อนไข
3. เริ่มโดยการป้อนป้ายกำกับที่มีคำอธิบายในช่อง ชื่อคอลัมน์ใหม่

ในการสร้างกฎ คุณจำเป็นต้องกรอกแถวที่มีสี่ช่อง:

• ชื่อคอลัมน์: คอลัมน์ที่มีอยู่ซึ่งคุณต้องการประเมิน
• ตัวดำเนินการ: ตรรกะ (เช่น "มากกว่า," "มี," "เริ่มต้นด้วย," และอื่น ๆ)
• ค่า: เกณฑ์หรือข้อความที่คุณกำลังมองหา
• ผลลัพธ์: คอลัมน์ใหม่ควรจะแสดงอะไรหากเงื่อนไขเป็นจริง

เพื่อให้เสร็จสมบูรณ์ของตรรกะ ใช้สิ่งต่อไปนี้:

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

อินเทอร์เฟซใช้ลำดับการทำงานจากบนลงล่าง หากแถวใดตรงตามเกณฑ์แรก มันจะหยุดที่นั่น หากไม่ตรง มันจะไปยังเกณฑ์ถัดไป

เมื่อคุณทำเสร็จแล้ว ให้คลิก ตกลง โดยค่าเริ่มต้น Power Query จะวางคอลัมน์ใหม่ของคุณไปทางด้านขวาสุด แต่คุณสามารถจัดเรียงคอลัมน์ใหม่ทีหลังได้โดยการลากหัวคอลัมน์

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

กรณีการใช้งาน 1: การจัดระดับซับซ้อน

ก้าวไปเกินกว่าหมวดหมู่พื้นฐาน

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

สถานการณ์: คุณจำเป็นต้องกำหนดตัวแทนฝ่ายขายให้กับระดับผลการดำเนินงาน: ใครก็ตามที่มียอดขายเกิน $10,000 จะเป็น Platinum, เกิน $5,000 จะเป็น Gold, และคนอื่น ๆ จะเป็น Standard.

นี่คือวิธีทำสิ่งนี้:

1. เปิดกล่องโต้ตอบ คอลัมน์ตามเงื่อนไข และตั้งชื่อคอลัมน์ใหม่ของคุณว่า ระดับผลงาน

2. กรอกข้อมูลในช่องสำหรับเงื่อนไขแรกดังนี้:

   • ชื่อคอลัมน์: รายได้
   • ตัวดำเนินการ: มากกว่าหรือเท่ากับ
   • มูลค่า: 10000
   • ผลลัพธ์: แพลตตินัม
3. คลิก เพิ่มข้อกำหนด และทำซ้ำขั้นตอนสำหรับ ทอง โดยตั้งตัวดำเนินการเป็น มากกว่าหรือเท่ากับ และค่าของ 5000
4. ในกล่อง Else สุดท้าย พิมพ์ Standard.
5. คลิก ตกลง และคอลัมน์มีเงื่อนไขใหม่ของคุณจะปรากฏทางด้านขวาของคำสั่งค้นหาของคุณ

กรณีการใช้งาน 2: ทำความสะอาดสตริงข้อความที่ยุ่งเหยิง

การใช้ "contains" เพื่อแก้ไขข้อมูลที่ไม่สอดคล้องกัน

ตัวดำเนินการ "contains" เป็นตัวเปลี่ยนเกมเพราะมันทำการจับคู่สตริงย่อยโดยค้นหาข้อความที่ใดก็ได้ภายในสตริง

คอลัมน์ หมายเหตุ ของคุณมีรายการที่ไม่สอดคล้องกัน เช่น "สาย - คนขับล่าช้า," "ล่าช้าเนื่องจากสภาพอากาศ," และ "ลูกค้านัดใหม่." คุณต้องการคอลัมน์เดียวที่ระบุเพียง "สาย" หากมีคำสำคัญที่เกี่ยวข้องปรากฏ

เมื่อคุณใช้เครื่องมือคอลัมน์ตามเงื่อนไขเป็นครั้งแรก มันจะกลายเป็นส่วนที่เข้าใจง่ายของเวิร์กโฟลว์ของคุณ อย่างไรก็ตาม การจับคู่ข้อความใน Power Query บางครั้งอาจให้ผลลัพธ์ที่ไม่คาดคิดหากข้อมูลของคุณไม่ได้มาตรฐาน—for example ค่าหนึ่งเช่น "delayed" อาจไม่ตรงกับ "Delayed" เสมอไป—ซึ่งเป็นเหตุผลว่าทำไมจึงมีขั้นตอนการทำให้เป็นมาตรฐานเพิ่มขึ้นก่อนการใช้เงื่อนไข:

1. เลือกคอลัมน์ Notes ของคุณแล้วคลิก Transform > Format > Lowercase
2. เปิดกล่องโต้ตอบ คอลัมน์ตามเงื่อนไข และตั้งชื่อคอลัมน์ว่า กลุ่มสถานะ

3. สำหรับเงื่อนไขแรก:

   • ชื่อคอลัมน์: บันทึก
   • ผู้ปฏิบัติการ: มี
   • ค่า: สาย (ตรวจสอบให้แน่ใจว่านี่เป็นตัวอักษร พิมพ์เล็ก เพื่อให้ตรงกับข้อความมาตรฐานของคุณ)
   • ผลลัพธ์: สาย
4. คลิก เพิ่มข้อกำหนด และทำซ้ำขั้นตอนสำหรับคำหลัก ล่าช้า และ เลื่อนกำหนด
5. ในช่อง Else ให้พิมพ์ ตรงเวลา
6. เมื่อคุณคลิก ตกลง ความคิดเห็นจะถูกจัดหมวดหมู่ตามคำสำคัญอย่างเป็นระเบียบ

กรณีการใช้งานที่ 3: สร้างระบบป้องกันความล้มเหลวสำหรับข้อผิดพลาด

ใช้ฟิลด์ "Else" เป็นตัวเติมข้อมูล

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

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

นี่คือขั้นตอน:

1. เพิ่ม คอลัมน์มีเงื่อนไข และตั้งชื่อว่า ตรวจสอบหมวดหมู่

2. ก่อนอื่น กำหนดกฎเกณฑ์ของคุณสำหรับอิเล็กทรอนิกส์:

   • ชื่อคอลัมน์: รหัสสินค้า
   • ผู้ปฏิบัติการ: เริ่มด้วย
   • ค่า: ELEC
   • ผลลัพธ์: อิเล็กทรอนิกส์
3. คลิก เพิ่มข้อกำหนด และทำซ้ำขั้นตอนนี้สำหรับสินค้าภายในบ้านและสำนักงาน
4. สำหรับช่อง Else ให้พิมพ์ Needs Review เพื่อเตือนให้คุณตรวจสอบ ID เหล่านี้อีกครั้งและตัดสินใจว่าจะทำอย่างไรกับพวกมัน
5. คลิก ตกลง เพื่อดูหมวดหมู่ใหม่ของคุณในการใช้งาน

คุณไม่จำเป็นต้องอยู่ในโลกที่เต็มไปด้วยวงเล็บไม่สิ้นสุดและเกตตรรกะที่เสีย การย้ายกฎ "if/then" ของคุณไปยังคอลัมน์เชิงเงื่อนไขทำให้ข้อมูลของคุณอ่านง่ายขึ้นและยากต่อการทำลาย เมื่อตอนคุณเห็นว่ากระบวนการทำงานของคุณเร็วขึ้นมาก เพียงใช้เวลาครึ่งชั่วโมงสำรวจเครื่องมือแปลงข้อมูลอื่น ๆ ของ Power Query — คุณอาจพบว่ามันสามารถจัดการงานประจำวันหลาย ๆ อย่างได้มีประสิทธิภาพมากกว่าสูตร Excel ปกติ