เชี่ยวชาญ Excel: 3 วิธีที่พิสูจน์แล้วสำหรับ Find and Replace ข้ามหลายไฟล์

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

ดาวน์โหลดสมุดงาน

คุณสามารถดาวน์โหลดสมุดงาน Excel ฝึกฝนฟรีได้จากที่นี่

3 วิธีค้นหาและแทนที่ค่าในหลายแผ่นงานและสมุดงานใน Excel

ในส่วนต่อไป คุณจะได้เรียนรู้วิธี ค้นหาและแทนที่ค่าบางอย่างในหลายแผ่นงานและไฟล์ Excel ด้วยฟีเจอร์ Find and Replace และกับ VBA macro ใน Excel

1. ค้นหาและแทนที่ค่าในหลายแผ่นงานในไฟล์ Excel

คุณสามารถ ค้นหาค่าที่เฉพาะเจาะจงจากแผ่นงานทั้งหมดของสมุดงานที่มีอยู่และแทนที่ทั้งหมดทีเดียวด้วยค่าใดก็ตามที่คุณต้องการ มาดูวิธีการทำด้วยเครื่องมือ Find & Replace ของ Excel

ในภาพต่อไปนี้ คุณสามารถเห็นได้ว่าเรามีแผ่นงาน Excel ชื่อ One

ในสมุดงานเดียวกัน เรามีอีกแผ่นงานชื่อ Two

ตอนนี้สิ่งที่เราจะทำคือ เราจะ ค้นหาคำเฉพาะและแทนที่ด้วยคำอื่นด้วยฟีเจอร์ Find & Replace ใน Excel ในกรณีของเรา เราจะแทนที่คำ “Ambrose” ด้วย “Moxley”

ขั้นตอน:

• จากแท็บ Home เลือก Find & Select -> Replace…

• ตามมา กล่องป๊อปอัป Find and Replace จะปรากฏ คลิกที่ Options>> (แสดงในภาพด้านล่าง)

• หลังจากนั้น ตัวเลือกใหม่จะปรากฏในกล่องป๊อปอัป เขียนคำที่คุณต้องการค้นหา (ในกรณีนี้คือ “Ambrose”) ในส่วน Find what
• จากนั้น เขียนคำที่คุณต้องการแทนที่ คำก่อนหน้า (คำคือ “Moxley” ในกรณีนี้) ในส่วน Replace with
• หลังจากนั้น จากรายการแบบเลื่อนลงถัดจาก Within ให้เลือก Workbook นี่จะค้นหาคำที่กำหนดในทุกแผ่นงานในสมุดงานนั้นและแทนที่ทั้งหมด
• สุดท้าย คลิก Replace All

คุณจะได้ กล่องยืนยัน แสดงการแทนที่ทั้งหมดที่เกิดขึ้น เนื่องจากเรามีเพียง 2 แผ่นงานในสมุดงาน จึงมีการแทนที่เพียง 2 ครั้งในกรณีนี้ หากคุณมีแผ่นงานมากกว่านั้น การแทนที่จะเกิดขึ้นมากขึ้น

ตอนนี้ลองตรวจสอบว่าการแทนที่เกิดขึ้นจริงหรือไม่!

พิจารณาทั้งภาพด้านบนและภาพต่อไปนี้

คำ “Ambrose” จากทั้งสองแผ่นงานของเราตอนนี้ถูกแทนที่ด้วยคำ “Moxley” แล้ว

อ่านเพิ่มเติม: วิธีค้นหาและแทนที่หลายค่าใน Excel ด้วย VBA (3 ตัวอย่าง)

2. ค้นหาและแทนที่ค่าในหลายแผ่นงานของหลายสมุดงาน

คุณรู้ไหมว่าหลังจากที่คุณทำการ Find & Replace สำหรับแผ่นงานเฉพาะเสร็จแล้ว กล่องป๊อปอัป Find and Replace จะไม่ปิดจนกว่าคุณจะปิดด้วยตนเอง? ดังนั้น คุณสามารถใช้ประโยชน์จากฟีเจอร์นี้เมื่อคุณต้องการแทนที่ค่าในหลายสมุดงาน

ดูภาพต่อไปนี้ เราเปิดสมุดงาน Excel สองเล่ม เล่มหนึ่งชื่อ Find and Replace Value (ดูภาพด้านล่าง)

อีกเล่มหนึ่งชื่อ Transpose Multiple Columns (แสดงในภาพด้านล่าง)

ตอนนี้ หลังจากที่คุณทำการแทนที่ค่าจากแผ่นงานเฉพาะเสร็จแล้ว คุณจะสังเกตเห็นว่า กล่องป๊อปอัป Find and Replace ยังคงอยู่

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

คุณสามารถดำเนินการนี้ได้กับสมุดงานไม่ว่าจะมากแค่ไหน

อ่านเพิ่มเติม: วิธีค้นหาและแทนที่โดยใช้สูตรใน Excel (4 ตัวอย่าง)

บทความที่คล้ายกัน:

• วิธีแทนที่ข้อความในสูตร Excel (7 วิธีง่าย)
• วิธีค้นหาและแทนที่อักขระ Asterisk (*) ใน Excel
• ค้นหาและแทนที่ข้อความในช่วงด้วย Excel VBA (Macro และ UserForm)
• วิธีค้นหาและแทนที่ในคอลัมน์ Excel (6 วิธี)
• วิธีค้นหาและแทนที่ภายในการเลือกใน Excel (7 วิธี)

3. ใช้ VBA เพื่อค้นหาและแทนที่ค่าในสมุดงานที่เปิดอยู่ใน Excel

คุณสามารถทำทั้งหมดที่อภิปรายในส่วนก่อนหน้าได้เพียงแค่ด้วย โค้ด VBA หากคุณเป็นผู้ใช้ Excel ระดับสูง ส่วนนี้เหมาะสำหรับคุณ การนำ VBA ไปใช้เป็นวิธีที่มีประสิทธิภาพ ที่สุด เร็วที่สุด และปลอดภัยที่สุดในการรันการดำเนินการใดๆ ใน Excel คุณสามารถ ค้นหาและแทนที่ค่าจากแผ่นงานทั้งหมดของสมุดงาน ใน Excel มาดูวิธีทำกับ VBA macro

ขั้นตอน:

• ในตอนแรก กด Alt + F11 บนคีย์บอร์ดหรือไปที่แท็บ Developer -> Visual Basic เพื่อเปิด Visual Basic Editor

• ต่อไป ในหน้าต่างโค้ดป๊อปอัป จากแถบเมนู คลิก Insert -> Module

• จากนั้น คัดลอก โค้ดต่อไปนี้และ วาง ลงในหน้าต่างโค้ด

Sub MultipleFindAndReplace() Dim iSheet As Worksheet Dim OldValue As Variant Dim NewValue As Variant Dim i As Long OldValue = Array("John", "Roman", "Dean", "Seth", "Finn") NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh") For i = LBound(OldValue) To UBound(OldValue) For Each iSheet In ActiveWorkbook.Worksheets iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next iSheet Next i End Sub

โค้ดของคุณพร้อมรันแล้ว

ชิ้นส่วนโค้ดนี้จะแทนที่อาร์เรย์ค่า “John, Roman, Dean, Seth, Finn” ด้วยค่า “Ben, Alex, Joe, Chris, Josh”

• ตอนนี้ กด F5 บนคีย์บอร์ดหรือจากแถบเมนูเลือก Run -> Run Sub/UserForm คุณยังสามารถคลิกที่ ไอคอน Run ขนาดเล็ก ในแถบเมนูย่อยเพื่อรันแมโครได้

หลังจากรันโค้ดเสร็จ ให้ดูภาพด้านล่างเพื่อดูผลลัพธ์

ค่าทั้งหมด (“John, Roman, Dean, Seth, Finn”) ที่คุณใส่ในโค้ดเพื่อแทนที่ด้วยค่า (“Ben, Alex, Joe, Chris, Josh”) ตอนนี้ถูกแทนที่สำเร็จแล้ว

คุณยังสามารถรันโค้ดนี้เพื่อ ค้นหาและแทนที่หลายค่าจากหลายแผ่นงานในหลายสมุดงาน ได้

• หลังจากรันแมโครบนสมุดงานหนึ่ง ตอนนี้เพียง เอาเมาส์ไปวางที่อีกเล่มหนึ่ง จากนั้นไปที่ Developer -> Macros
• จะมีกล่องป๊อปอัป Macro ปรากฏ ที่นั่น เลือกชื่อแมโครและคลิก Run

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

คำอธิบายโค้ด VBA

Dim iSheet As Worksheet Dim OldValue As Variant Dim NewValue As Variant Dim i As Long

กำหนดตัวแปร

OldValue = Array("John", "Roman", "Dean", "Seth", "Finn") NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")

ตั้งค่าค่าที่ต้องการค้นหาในตัวแปร OldValue และตั้งค่าค่าที่ต้องการแทนที่ในตัวแปร NewValue

 For i = LBound(OldValue) To UBound(OldValue) For Each iSheet In ActiveWorkbook.Worksheets iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next iSheet Next i

ชิ้นส่วนโค้ดนี้จะวนลูปผ่านแต่ละรายการในรายการอาร์เรย์ จากนั้นวนลูปผ่านแต่ละแผ่นงานใน ActiveWorkbook หลังจากสแกนแล้ว จะแทนที่ OldValue ด้วย NewValue เมื่อพบค่าตรงกัน หลังจากวนลูปในแผ่นงานหนึ่งเสร็จ จะไปแผ่นงานถัดไปและเริ่มรันลูปอีกครั้ง

อ่านเพิ่มเติม: Find And Replace Multiple Values in Excel (6 วิธีรวดเร็ว)

สรุป

สรุปได้ว่า บทความนี้แสดงวิธีที่แตกต่างกันเกี่ยวกับวิธี ค้นหาและแทนที่ค่าในหลายแผ่นงานและไฟล์ ใน Excel ด้วยเครื่องมือ Find and Replace และ VBA macro หวังว่าบทความนี้จะมีประโยชน์มากสำหรับคุณ รู้สึกสบายใจถามคำถามใดๆ เกี่ยวกับหัวข้อนี้ได้เลย

บทความที่เกี่ยวข้อง

• เทคนิคทำความสะอาดข้อมูลใน Excel: เพิ่มข้อความลงในเซลล์
• วิธีเพิ่มข้อความก่อนสูตรใน Excel (4 วิธีง่าย)
• Find And Replace Values Using Wildcards in Excel
• วิธีใช้ฟังก์ชัน Substitute ใน Excel VBA (3 ตัวอย่าง)
• วิธีแทนที่อักขระพิเศษใน Excel (6 วิธี)