เมื่อเร็วๆ นี้ เพื่อนคนหนึ่งของฉันติดต่อฉันเพื่อขอให้ช่วยสร้างวลีที่เป็นไปได้ทั้งหมดซึ่งประกอบด้วยชุดคำที่กำหนด ปัญหาประเภทนี้อาจเกิดขึ้นได้เมื่อรวบรวมรายการคำหลักและวลีสำหรับการโฆษณาออนไลน์และการส่งเสริมการขาย SEO เมื่อคุณจำเป็นต้องดำเนินการเรียงสับเปลี่ยนของคำที่เป็นไปได้ทั้งหมดในคำค้นหา:
ในวิชาคณิตศาสตร์ การดำเนินการนี้เรียกว่า ผลิตภัณฑ์คาร์ทีเซียน. คำจำกัดความอย่างเป็นทางการมีดังนี้: ผลคูณคาร์ทีเซียนของเซต A และ B คือเซตของคู่ทั้งหมด องค์ประกอบแรกเป็นของเซต A และส่วนประกอบที่สองเป็นของเซต B นอกจากนี้ องค์ประกอบของเซตสามารถเป็นได้ทั้ง ตัวเลขและข้อความ
แปลเป็นภาษามนุษย์หมายความว่าถ้าในเซต A เรามีคำว่า "ขาว" และ "แดง" และในชุด B "BMW" และ "Mercedes" จากนั้นหลังจากผลิตภัณฑ์คาร์ทีเซียนของทั้งสองชุดเรา get on the output คือชุดของวลีที่เป็นไปได้ทั้งหมด ซึ่งประกอบด้วยคำของทั้งสองรายการ:
- bmw สีขาว
- bmw สีแดง
- Mercedes สีขาว
- รถเบนซ์สีแดง
… นั่นคือสิ่งที่เราต้องการ ลองดูสองสามวิธีในการแก้ปัญหานี้ใน Excel
วิธีที่ 1 สูตร
เริ่มจากสูตรกันก่อน สมมติว่าเป็นข้อมูลเริ่มต้น เรามีรายการคำดั้งเดิมสามรายการในคอลัมน์ A, B และ C ตามลำดับ และจำนวนองค์ประกอบในแต่ละรายการอาจแตกต่างกันไป:
ขั้นแรก ให้สร้างคอลัมน์สามคอลัมน์ด้วยดัชนี กล่าวคือ ตัวเลขลำดับของคำจากแต่ละรายการในชุดค่าผสมที่เป็นไปได้ทั้งหมด แถวแรกของหน่วย (E2:G2) จะถูกป้อนด้วยตนเอง และส่วนที่เหลือ เราจะใช้สูตรต่อไปนี้:
ตรรกะในที่นี้ง่าย: หากดัชนีในเซลล์ก่อนหน้าที่เหนือกว่าถึงจุดสิ้นสุดของรายการแล้ว นั่นคือ เท่ากับจำนวนองค์ประกอบในรายการที่คำนวณโดยฟังก์ชัน COUNT (นับ)จากนั้นเราจะเริ่มการนับใหม่ มิฉะนั้น เราจะเพิ่มดัชนีขึ้น 1 ให้ความสนใจเป็นพิเศษกับการกำหนดช่วงอย่างชาญฉลาดด้วยเครื่องหมายดอลลาร์ ($) เพื่อให้คุณสามารถคัดลอกสูตรไปทางขวาได้
ตอนนี้เรามีเลขลำดับของคำที่เราต้องการจากแต่ละรายการแล้ว เราก็สามารถแยกคำเหล่านั้นออกมาได้โดยใช้ฟังก์ชัน ดัชนี (ดัชนี) เป็นสามคอลัมน์แยกกัน:
หากคุณไม่เคยเจอฟังก์ชันนี้ในงานของคุณมาก่อน เราขอแนะนำให้คุณศึกษามันอย่างน้อยในแนวทแยง - มันช่วยได้ในหลาย ๆ สถานการณ์และมีประโยชน์ไม่น้อย (และมากยิ่งขึ้นไปอีก!) VPR (วีลุคอัพ).
หลังจากนั้นจะเหลือเพียงการติดชิ้นส่วนที่เป็นผลลัพธ์ทีละบรรทัดโดยใช้สัญลักษณ์การต่อ (&):
… หรือ (ถ้าคุณมี Excel เวอร์ชันล่าสุด) ด้วยฟังก์ชันที่มีประโยชน์ รวมกัน (รวมข้อความ)ซึ่งสามารถติดเนื้อหาทั้งหมดของเซลล์ที่ระบุผ่านอักขระตัวคั่นที่กำหนด (ช่องว่าง):
วิธีที่ 2 ผ่าน Power Query
Power Query เป็น Add-in ที่มีประสิทธิภาพสำหรับ Microsoft Excel ที่ทำงานหลักสองอย่าง: 1. กำลังโหลดข้อมูลลงใน Excel จากแหล่งภายนอกเกือบทั้งหมด และ 2. การแปลงตารางที่โหลดทุกประเภท Power Query มีอยู่แล้วใน Excel 2016-2019 และสำหรับ Excel 2010-2013 นั้นได้รับการติดตั้งเป็น Add-in แยกต่างหาก (คุณสามารถดาวน์โหลดได้จากเว็บไซต์ทางการของ Microsoft ได้ฟรี) หากคุณยังไม่ได้เริ่มใช้ Power Query ในงานของคุณ ก็ถึงเวลาที่จะต้องคิดเกี่ยวกับมันแล้ว เนื่องจากการแปลงแบบที่อธิบายข้างต้นนั้นทำได้อย่างง่ายดายและเป็นธรรมชาติในการเคลื่อนไหวเพียงไม่กี่ครั้ง
ก่อนอื่น ให้โหลดรายการแหล่งที่มาเป็นคิวรีแยกกันใน Power Query เมื่อต้องการทำเช่นนี้ สำหรับแต่ละตาราง ให้ทำตามขั้นตอนต่อไปนี้:
- มาเปลี่ยนตารางให้กลายเป็นโต๊ะ “ฉลาด” ด้วยปุ่มกันเถอะ จัดรูปแบบเป็นตาราง แถบ หน้าแรก (หน้าแรก — รูปแบบเป็นตาราง) หรือแป้นพิมพ์ลัด Ctrl+T. แต่ละโต๊ะจะได้รับชื่อโดยอัตโนมัติ ตารางที่ 1,2,3…ซึ่งสามารถเปลี่ยนแปลงได้หากต้องการบนแท็บ นวกรรมิก (ออกแบบ).
- เมื่อตั้งค่าเซลล์ที่ใช้งานอยู่ในตารางแล้ว ให้กดปุ่ม จากโต๊ะ (จากตาราง) แถบ ข้อมูล (วันที่) หรือบนแท็บ Power Query (ถ้าคุณได้ติดตั้งไว้เป็นส่วนเสริมแยกต่างหากสำหรับ Excel 2010-2013)
- ในหน้าต่างตัวแก้ไขแบบสอบถามที่เปิดขึ้น ให้เลือกคำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่..) แล้วก็ตัวเลือก เพียงแค่สร้างการเชื่อมต่อ (สร้างการเชื่อมต่อเท่านั้น). การดำเนินการนี้จะออกจากตารางที่โหลดไว้ในหน่วยความจำและอนุญาตให้เข้าถึงได้ในอนาคต
หากคุณทำทุกอย่างถูกต้อง ผลลัพธ์ในแผงด้านขวาควรเป็นคำขอสามรายการในโหมด การเชื่อมต่อเท่านั้น ด้วยชื่อตารางของเรา:
ตอนนี้ให้คลิกขวาที่แบบสอบถามแรกและเลือกคำสั่ง ลิงค์ (อ้างอิง)เพื่อสร้างสำเนาที่อัปเดตได้ แล้วเพิ่มคอลัมน์เพิ่มเติมลงในข้อมูลผ่านคำสั่ง การเพิ่มคอลัมน์ ž – คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ -ž คอลัมน์กำหนดเอง). ในหน้าต่างป้อนสูตร ให้ป้อนชื่อของคอลัมน์ใหม่ (เช่น Fragment2) และนิพจน์ง่ายๆ เป็นสูตร:
=ตารางที่2
… กล่าวอีกนัยหนึ่ง ชื่อของข้อความค้นหาที่สอง:
หลังจากคลิกที่ OK เราจะเห็นคอลัมน์ใหม่ในแต่ละเซลล์ซึ่งจะมีตารางที่ซ้อนกันพร้อมวลีจากตารางที่สอง (คุณสามารถดูเนื้อหาของตารางเหล่านี้ได้หากคุณคลิกในพื้นหลังของเซลล์ถัดจากคำ ตาราง):
มันยังคงขยายเนื้อหาทั้งหมดของตารางที่ซ้อนกันเหล่านี้โดยใช้ปุ่มที่มีลูกศรคู่ในส่วนหัวของคอลัมน์ผลลัพธ์และยกเลิกการเลือก ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า (ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า):
… และเราได้รับองค์ประกอบที่เป็นไปได้ทั้งหมดจากสองชุดแรก:
นอกจากนี้ทุกอย่างก็คล้ายกัน เพิ่มคอลัมน์จากการคำนวณอื่นด้วยสูตร:
=ตารางที่3
… แล้วขยายตารางที่ซ้อนกันอีกครั้ง – และตอนนี้เรามีตัวเลือกที่เป็นไปได้ทั้งหมดสำหรับการเปลี่ยนคำจากสามชุดตามลำดับ:
ยังคงเลือกทั้งสามคอลัมน์จากซ้ายไปขวาโดยถือ Ctrlและเชื่อมเนื้อหาโดยคั่นด้วยช่องว่างโดยใช้คำสั่ง รวมคอลัมน์ (รวมคอลัมน์) จากแท็บ การแปลง (แปลง):
ผลลัพธ์ที่ได้สามารถโหลดกลับเข้าสู่แผ่นงานได้ด้วยคำสั่งที่คุ้นเคย หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่..):
หากในอนาคตมีการเปลี่ยนแปลงบางอย่างในตารางต้นฉบับด้วยแฟรกเมนต์ แค่อัปเดตการสืบค้นที่สร้างขึ้นโดยคลิกขวาที่ตารางผลลัพธ์แล้วเลือกคำสั่งก็เพียงพอแล้ว อัปเดตและบันทึก (รีเฟรช) หรือโดยการกดแป้นพิมพ์ลัด Ctrl+อื่น ๆ+F5.
- Power Query, Power Pivot, Power Map และ Power BI คืออะไร และเหตุใดจึงจำเป็นต้องมีผู้ใช้ Excel
- การสร้างแผนภูมิแกนต์ใน Power Query
- 5 วิธีในการใช้ฟังก์ชัน INDEX