การแทนที่ข้อความจำนวนมากด้วยสูตร

สมมติว่าคุณมีรายชื่อซึ่งมี "ความตรงไปตรงมา" ในระดับต่างๆ ที่เขียนขึ้น เช่น ที่อยู่หรือชื่อบริษัท:

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

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

จะทำอย่างไร? อย่าแทนที่ข้อความที่คดเคี้ยว 100500 ครั้งด้วยข้อความที่ถูกต้องผ่านช่อง "ค้นหาและแทนที่" หรือโดยการคลิก Ctrl+H?

สิ่งแรกที่นึกถึงในสถานการณ์เช่นนี้คือการแทนที่จำนวนมากตามหนังสืออ้างอิงที่รวบรวมไว้ล่วงหน้าซึ่งจับคู่ตัวเลือกที่ไม่ถูกต้องและไม่ถูกต้อง - เช่นนี้:

น่าเสียดาย ด้วยความแพร่หลายของงานดังกล่าว Microsoft Excel ไม่มีวิธีการง่ายๆ ในตัวในการแก้ปัญหา เริ่มต้นด้วย มาหาวิธีการทำเช่นนี้กับสูตร โดยไม่ต้องเกี่ยวข้องกับ "ปืนใหญ่" ในรูปแบบของมาโครใน VBA หรือ Power Query

กรณีที่ 1 การเปลี่ยนแบบเต็มจำนวนมาก

เริ่มจากกรณีที่ค่อนข้างง่าย - สถานการณ์ที่คุณต้องแทนที่ข้อความคดเคี้ยวเก่าด้วยข้อความใหม่ อย่างเต็มที่.

สมมติว่าเรามีสองตาราง:

ในตอนแรก – ชื่อบริษัทที่แตกต่างกัน ในครั้งที่สอง – หนังสืออ้างอิงของจดหมายโต้ตอบ หากเราพบชื่อบริษัทในตารางแรกคำใด ๆ จากคอลัมน์ การค้นหาจากนั้นคุณต้องแทนที่ชื่อที่คดเคี้ยวนี้ด้วยชื่อที่ถูกต้อง - จากคอลัมน์ แทน ตารางค้นหาที่สอง

เพื่อความสะดวก:

• ตารางทั้งสองถูกแปลงเป็นไดนามิก ("สมาร์ท") โดยใช้แป้นพิมพ์ลัด Ctrl+T หรือทีม แทรก – ตาราง (แทรก — ตาราง).
  
• บนแท็บที่ปรากฏขึ้น นวกรรมิก (ออกแบบ) ตารางแรกชื่อ ข้อมูลและตารางอ้างอิงที่สอง – แทน.

มาอธิบายตรรกะของสูตรกันก่อนดีกว่า

ยกตัวอย่างบริษัทแรกจากเซลล์ A2 และลืมบริษัทที่เหลือไปชั่วคราว ลองพิจารณาว่าตัวเลือกใดจากคอลัมน์ การค้นหา เจอกันที่นั่น ในการดำเนินการนี้ ให้เลือกเซลล์ว่างในส่วนว่างของแผ่นงานแล้วป้อนฟังก์ชันที่นั่น การค้นหา (หา):

ฟังก์ชันนี้กำหนดว่ารวมสตริงย่อยที่กำหนดหรือไม่ (อาร์กิวเมนต์แรกคือค่าทั้งหมดจากคอลัมน์ การค้นหา) ลงในข้อความต้นฉบับ (บริษัทแรกจากตารางข้อมูล) และควรแสดงหมายเลขลำดับของอักขระที่พบข้อความ หรือแสดงข้อผิดพลาดหากไม่พบสตริงย่อย

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

หากคุณมี Excel เวอร์ชันก่อนหน้า หลังจากคลิกที่ เข้าสู่ เราจะเห็นเฉพาะค่าแรกจากอาร์เรย์ผลลัพธ์ นั่นคือ error #VALUE! (#ค่า!).

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

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

ตอนนี้ มาเพิ่มฟังก์ชันให้กับสูตรของเรากัน ดู(ค้นหา):

ฟังก์ชั่นนี้มีสามอาร์กิวเมนต์:

1. ค่าที่ต้องการ – คุณสามารถใช้จำนวนที่มากเพียงพอ (สิ่งสำคัญคือมีความยาวเกินความยาวของข้อความใด ๆ ในข้อมูลต้นฉบับ)
   
2. Viewed_เวกเตอร์ – ช่วงหรืออาร์เรย์ที่เรากำลังมองหาค่าที่ต้องการ นี่คือฟังก์ชันที่แนะนำก่อนหน้านี้ การค้นหาซึ่งส่งคืนอาร์เรย์ {#VALUE!:4:#VALUE!}
   
3. เวกเตอร์_ผล – ช่วงที่เราต้องการคืนค่าหากพบค่าที่ต้องการในเซลล์ที่เกี่ยวข้อง นี่คือชื่อที่ถูกต้องจากคอลัมน์ แทน ตารางอ้างอิงของเรา
   

คุณลักษณะหลักและไม่ชัดเจนที่นี่คือฟังก์ชัน ดู หากไม่มีการจับคู่แบบตรงทั้งหมด ให้มองหาค่าที่น้อยที่สุด (ก่อนหน้า) ที่ใกล้ที่สุดเสมอ. ดังนั้น โดยการระบุตัวเลขจำนวนมาก (เช่น 9999) เป็นค่าที่ต้องการ เราจะบังคับ ดู ค้นหาเซลล์ที่มีจำนวนน้อยที่สุด (4) ในอาร์เรย์ {#VALUE!:4:#VALUE!} และส่งกลับค่าที่สอดคล้องกันจากเวกเตอร์ผลลัพธ์ เช่น ชื่อบริษัทที่ถูกต้องจากคอลัมน์ แทน.

ความแตกต่างประการที่สองคือ ในทางเทคนิค สูตรของเราคือสูตรอาร์เรย์ เนื่องจาก function การค้นหา ส่งคืนเป็นผลลัพธ์ ไม่ใช่หนึ่งรายการ แต่เป็นอาร์เรย์ของค่าสามค่า แต่เนื่องจากหน้าที่ ดู รองรับอาร์เรย์นอกกรอบแล้วเราไม่ต้องป้อนสูตรนี้เป็นสูตรอาร์เรย์แบบคลาสสิก – โดยใช้แป้นพิมพ์ลัด Ctrl+เปลี่ยน+เข้าสู่. ง่ายๆก็พอ เข้าสู่.

นั่นคือทั้งหมดที่ หวังว่าคุณจะเข้าใจตรรกะ

ยังคงถ่ายโอนสูตรสำเร็จรูปไปยังเซลล์ B2 แรกของคอลัมน์ คงที่ – และงานของเราได้รับการแก้ไขแล้ว!

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

กรณีที่ 2 การเปลี่ยนชิ้นส่วนจำนวนมาก

กรณีนี้ค่อนข้างซับซ้อนกว่าเล็กน้อย อีกครั้งเรามีตาราง "ฉลาด" สองตาราง:

ตารางแรกที่มีที่อยู่เขียนคดเคี้ยวที่ต้องแก้ไข (ฉันเรียกมันว่า ข้อมูล 2). ตารางที่สองเป็นหนังสืออ้างอิงตามที่คุณต้องทำการแทนที่บางส่วนของสตริงย่อยภายในที่อยู่ (ฉันเรียกว่าตารางนี้ ตัวสำรอง2).

ความแตกต่างพื้นฐานที่นี่คือ คุณต้องแทนที่เพียงส่วนหนึ่งของข้อมูลเดิม ตัวอย่างเช่น ที่อยู่แรกมีข้อมูลที่ไม่ถูกต้อง "เซนต์. ปีเตอร์สเบิร์ก” ด้านขวา "เซนต์. ปีเตอร์สเบิร์ก”, ทิ้งที่อยู่ที่เหลือ (รหัสไปรษณีย์, ถนน, บ้าน) ไว้ตามเดิม

สูตรที่เสร็จแล้วจะออกมาเป็นแบบนี้ (เพื่อให้เข้าใจง่าย ผมแบ่งเป็นกี่บรรทัดครับ) อื่น ๆ+เข้าสู่):

งานหลักที่นี่ทำโดยฟังก์ชันข้อความ Excel มาตรฐาน ทดแทน (ทดแทน)ซึ่งมี 3 อาร์กิวเมนต์:

1. ข้อความต้นฉบับ – ที่อยู่คดเคี้ยวแรกจากคอลัมน์ที่อยู่
2. สิ่งที่เรากำลังมองหา – ที่นี่เราใช้เคล็ดลับกับฟังก์ชั่น ดู (ค้นหา)จากวิธีก่อนหน้านี้ในการดึงค่าจากคอลัมน์ การค้นหาซึ่งรวมอยู่ในส่วนย่อยในที่อยู่แบบโค้ง
3. สิ่งที่จะแทนที่ด้วย – ในลักษณะเดียวกับที่เราพบค่าที่ถูกต้องที่สอดคล้องกับมันจากคอลัมน์ แทน.

ใส่สูตรนี้ด้วย Ctrl+เปลี่ยน+เข้าสู่ ไม่จำเป็นในที่นี้ ทั้งที่จริงแล้วเป็นสูตรอาร์เรย์

และเห็นได้ชัดว่า (ดูข้อผิดพลาด #N/A ในภาพก่อนหน้า) ว่าสูตรดังกล่าว สำหรับความสง่างามทั้งหมด มีข้อเสียอยู่สองสามข้อ:

• ฟังก์ชัน SUBSTITUTE คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ดังนั้นจึงไม่พบ “Spb” ในบรรทัดสุดท้ายในตารางการแทนที่ เพื่อแก้ปัญหานี้ คุณสามารถใช้ฟังก์ชัน ซาเมนิต (แทนที่)หรือนำทั้งสองตารางมาไว้ในทะเบียนเดียวกันในเบื้องต้น
• หากข้อความในตอนแรกถูกต้องหรืออยู่ในนั้น ไม่มีชิ้นส่วนให้เปลี่ยน (บรรทัดสุดท้าย) จากนั้นสูตรของเราจึงแสดงข้อผิดพลาด ช่วงเวลานี้สามารถถูกทำให้เป็นกลางได้โดยการสกัดกั้นและแทนที่ข้อผิดพลาดโดยใช้ฟังก์ชัน การอ้างอิง (ไอเฟอร์เรอร์):

  

• หากข้อความต้นฉบับมี หลายส่วนจากไดเร็กทอรีพร้อมกันจากนั้นสูตรของเราจะแทนที่เฉพาะสูตรสุดท้าย (ในบรรทัดที่ 8 Ligovsky «ถนน« เปลี่ยนไปเป็น “pr-t”, แต่ “เอส-พีบี” on "เซนต์. ปีเตอร์สเบิร์ก” ไม่ได้อีกต่อไปเพราะ “ส-พีบี” จะสูงกว่าในไดเร็กทอรี) ปัญหานี้แก้ได้ด้วยการรันสูตรของเราเองใหม่ แต่ตามคอลัมน์แล้ว คงที่:

  
  

ไม่สมบูรณ์แบบและยุ่งยากในสถานที่ต่างๆ แต่ดีกว่าการเปลี่ยนด้วยตนเองแบบเดียวกันใช่ไหม 🙂

PS

ในบทความถัดไป เราจะหาวิธีปรับใช้การแทนที่จำนวนมากโดยใช้แมโครและ Power Query

• ฟังก์ชัน SUBSTITUTE ทำงานอย่างไรเพื่อแทนที่ข้อความ
  
• การค้นหาข้อความที่ตรงกันโดยใช้ฟังก์ชัน EXACT
• การค้นหาและการแทนที่แบบตรงตามตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ (VLOOKUP ที่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่)