ค้นหาคำสำคัญในข้อความ

การค้นหาคำหลักในข้อความต้นฉบับเป็นหนึ่งในงานทั่วไปเมื่อทำงานกับข้อมูล มาดูวิธีแก้ปัญหาในหลายๆ วิธีโดยใช้ตัวอย่างต่อไปนี้:

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

วิธีที่ 1. Power Query

แน่นอน อันดับแรก เราจะเปลี่ยนตารางของเราให้เป็นไดนามิก (“สมาร์ท”) โดยใช้แป้นพิมพ์ลัด Ctrl+T หรือคำสั่ง หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง)ตั้งชื่อ (เช่น แสตมป์и อะไหล่สำรอง) และโหลดทีละรายการลงในตัวแก้ไข Power Query โดยเลือกบนแท็บ ข้อมูล – จากตาราง/ช่วง (ข้อมูล — จากตาราง/ช่วง). หากคุณมี Excel 2010-2013 เวอร์ชันเก่าซึ่งติดตั้ง Power Query เป็น Add-in แยกต่างหาก ปุ่มที่ต้องการจะอยู่บนแท็บ Power Query. หากคุณมี Excel 365 เวอร์ชันใหม่ล่าสุด ให้กดปุ่ม จากตาราง/ช่วง เรียกว่าตอนนี้ มีใบ (จากแผ่นงาน).

หลังจากโหลดแต่ละตารางใน Power Query เราจะกลับไปที่ Excel ด้วยคำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดไปที่… — สร้างการเชื่อมต่อเท่านั้น (หน้าแรก — ปิด & โหลด — ปิด & โหลดไปที่… — สร้างการเชื่อมต่อเท่านั้น).

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

ตรรกะของการกระทำมีดังต่อไปนี้:

1. บนแท็บขั้นสูง การเพิ่มคอลัมน์ เลือกทีม คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ — คอลัมน์กำหนดเอง) และป้อนสูตร = แบรนด์. หลังจากคลิกที่ OK เราจะได้คอลัมน์ใหม่ที่ในแต่ละเซลล์จะมีตารางที่ซ้อนกันซึ่งมีรายการคำสำคัญของเรา – ยี่ห้อผู้ผลิตรถยนต์:

   

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

   

3. บนแท็บขั้นสูง การเพิ่มคอลัมน์ เลือกทีม คอลัมน์เงื่อนไข (คอลัมน์เงื่อนไข) และกำหนดเงื่อนไขในการตรวจสอบการเกิดขึ้นของคำหลัก (แบรนด์) ในข้อความต้นฉบับ (คำอธิบายส่วน):

   

4. ในการทำให้ตัวพิมพ์ค้นหาไม่ละเอียดอ่อน ให้เพิ่มอาร์กิวเมนต์ที่สามในแถบสูตรด้วยตนเอง เปรียบเทียบ OrdinalIgnoreCase ไปที่ฟังก์ชันตรวจสอบเหตุการณ์ที่เกิดขึ้น ข้อความประกอบด้วย (หากมองไม่เห็นแถบสูตร ก็สามารถเปิดใช้งานบนแท็บได้ รีวิว):

   

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

   

7. หากต้องการแยกเกรดสำหรับแต่ละส่วน ให้เพิ่มคอลัมน์จากการคำนวณอีกคอลัมน์บนแท็บ การเพิ่มคอลัมน์ – คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ — คอลัมน์กำหนดเอง) และใช้สูตรที่ประกอบด้วยตาราง (ซึ่งอยู่ในคอลัมน์ของเรา รายละเอียด) และชื่อของคอลัมน์ที่แยกออกมา:

   

8. เราคลิกที่ปุ่มที่มีลูกศรคู่ที่ส่วนหัวของคอลัมน์ผลลัพธ์และเลือกคำสั่ง แยกค่า (แยกค่า)เพื่อออกตราประทับด้วยอักขระคั่นใด ๆ ที่คุณต้องการ:

   

9. การลบคอลัมน์ที่ไม่จำเป็นออก รายละเอียด.
10. หากต้องการเพิ่มชิ้นส่วนที่หายไปจากตารางผลลัพธ์ซึ่งไม่พบแบรนด์ในคำอธิบายเราดำเนินการตามขั้นตอนการรวมแบบสอบถาม ผล ด้วยคำขอเดิม อะไหล่สำรอง ปุ่ม รวมกัน แถบ หน้าแรก (หน้าแรก — รวมแบบสอบถาม). ประเภทการเชื่อมต่อ – สิทธิ์การเข้าร่วมภายนอก (รวมภายนอกขวา):

    

11. สิ่งที่เหลืออยู่คือการลบคอลัมน์พิเศษและเปลี่ยนชื่อ-ย้ายคอลัมน์ที่เหลือ - และงานของเราก็ได้รับการแก้ไข:

    
    

วิธีที่ 2 สูตร

หากคุณมี Excel เวอร์ชัน 2016 หรือใหม่กว่า ปัญหาของเราสามารถแก้ไขได้ด้วยวิธีที่กะทัดรัดและสวยงามมากโดยใช้ฟังก์ชันใหม่ รวมกัน (รวมข้อความ):

ตรรกะเบื้องหลังสูตรนี้ง่ายมาก:

• ฟังก์ชัน SEARCH (หา) ค้นหาการเกิดขึ้นของแต่ละยี่ห้อในคำอธิบายปัจจุบันของชิ้นส่วนและส่งกลับหมายเลขซีเรียลของสัญลักษณ์ เริ่มต้นจากที่พบยี่ห้อ หรือข้อผิดพลาด #VALUE! หากแบรนด์ไม่ได้อยู่ในคำอธิบาย
• จากนั้นใช้ฟังก์ชั่น IF (ถ้า) и อีโอชิบก้า (SERROR) เราแทนที่ข้อผิดพลาดด้วยสตริงข้อความว่าง “” และหมายเลขลำดับของอักขระด้วยชื่อแบรนด์เอง
• อาร์เรย์ที่เป็นผลลัพธ์ของเซลล์ว่างและแบรนด์ที่พบจะรวมกันเป็นสตริงเดียวผ่านอักขระตัวคั่นที่กำหนดโดยใช้ฟังก์ชัน รวมกัน (รวมข้อความ).

การเปรียบเทียบประสิทธิภาพและการบัฟเฟอร์แบบสอบถาม Power Query สำหรับการเร่งความเร็ว

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

• เวลาคำนวณใหม่ตามสูตร (วิธีที่ 2) – 9 วินาที เมื่อคุณคัดลอกสูตรไปยังทั้งคอลัมน์ในครั้งแรกและ 2 วินาที ที่ซ้ำ (อาจมีผลต่อการบัฟเฟอร์)
  
• เวลาอัปเดตของคิวรี Power Query (วิธีที่ 1) แย่กว่านั้นมาก – 110 วินาที
  

แน่นอนว่าหลายอย่างขึ้นอยู่กับฮาร์ดแวร์ของพีซีเฉพาะและ Office เวอร์ชันที่ติดตั้งและการอัปเดต แต่ภาพรวมนั้นฉันคิดว่าชัดเจน

เพื่อเพิ่มความเร็วคิวรี Power Query เรามาบัฟเฟอร์ตารางการค้นหากัน แสตมป์เนื่องจากไม่เปลี่ยนแปลงในกระบวนการของการดำเนินการคิวรี และไม่จำเป็นต้องคำนวณใหม่อย่างต่อเนื่อง (เหมือนที่ Power Query ทำโดยพฤตินัย) สำหรับสิ่งนี้เราใช้ฟังก์ชัน ตารางบัฟเฟอร์ จากภาษา M ของ Power Query ในตัว

เมื่อต้องการทำเช่นนี้ เปิดคิวรี ผล และบนแท็บ รีวิว กดปุ่ม ตัวแก้ไขขั้นสูง (ดู — ตัวแก้ไขขั้นสูง). ในหน้าต่างที่เปิดขึ้น ให้เพิ่มบรรทัดที่มีตัวแปรใหม่ มาร์กี้ 2ซึ่งจะเป็นเวอร์ชันบัฟเฟอร์ของไดเร็กทอรี automaker ของเรา และใช้ตัวแปรใหม่นี้ในภายหลังในคำสั่งเคียวรีต่อไปนี้:

หลังจากการปรับแต่งดังกล่าว ความเร็วในการอัปเดตคำขอของเราเพิ่มขึ้นเกือบ 7 เท่า – สูงสุด 15 วินาที ค่อนข้างแตกต่างกัน🙂

• การค้นหาข้อความคลุมเครือใน Power Query
• การแทนที่ข้อความจำนวนมากด้วยสูตร
• การแทนที่ข้อความจำนวนมากใน Power Query ด้วยฟังก์ชัน List.Accumulate