เพื่อนของฉันฉันมักจะติดต่อกับเขาเกี่ยวกับปัญหาในชีวิตจริงที่เขาเผชิญเมื่อทำงานกับข้อมูลใน Excel
หลายครั้งที่ฉันแปลข้อความค้นหาของเขาเป็นบทช่วยสอนเกี่ยวกับ Excel บนไซต์นี้ เนื่องจากอาจเป็นประโยชน์กับผู้อ่านคนอื่นๆ ของฉันด้วย
นี่เป็นบทช่วยสอนอย่างหนึ่งเช่นกัน
เพื่อนของฉันโทรหาฉันเมื่อสัปดาห์ที่แล้วด้วยคำถามต่อไปนี้:
ฉันมีข้อมูลที่อยู่ในคอลัมน์ใน Excel และฉันต้องการระบุ/กรองเซลล์ที่มีสตริงข้อความ (คำ) ที่ซ้ำกันในที่อยู่
ต่อไปนี้คือชุดข้อมูลที่คล้ายกันซึ่งเขาต้องการกรองเซลล์ (เซลล์ที่มีลูกศรสีแดง) ที่มีสตริงข้อความซ้ำในนั้น:
สิ่งที่ทำให้ยากในตอนนี้คือข้อมูลไม่สอดคล้องกันเนื่องจากเป็นการรวบรวมชุดข้อมูลที่สร้างขึ้นโดยตัวแทนฝ่ายขาย จึงอาจมีความคลาดเคลื่อนในชุดข้อมูล
下 一下 ：
- สตริงข้อความใดๆ สามารถทำซ้ำได้ในชุดข้อมูลนี้ตัวอย่างเช่น อาจเป็นชื่อภูมิภาคหรือชื่อเมือง หรือทั้งสองอย่าง
- คำต่างๆ จะถูกคั่นด้วยอักขระเว้นวรรค และไม่มีความสอดคล้องกันว่าชื่อเมืองจะอยู่หลังอักขระหกหรือแปดตัว
- มีระเบียนดังกล่าวหลายพันรายการและจำเป็นต้องกรองระเบียนที่มีสตริงข้อความซ้ำๆ
หลังจากพิจารณาตัวเลือกมากมาย เช่น ข้อความเป็นคอลัมน์และสูตร ในที่สุดฉันก็ตัดสินใจใช้ VBA เพื่อทำงาน
ดังนั้นฉันจึงสร้างฟังก์ชัน VBA ที่กำหนดเอง ('IdDuplicate') เพื่อวิเคราะห์เซลล์เหล่านี้และให้ TRUE กับฉันหากมีคำที่ซ้ำกันในสตริงข้อความและ FALSE หากไม่มีคำซ้ำ (ดังที่แสดงด้านล่าง):
ฟังก์ชันแบบกำหนดเองนี้จะวิเคราะห์แต่ละคำในสตริงข้อความและตรวจสอบว่าคำนั้นเกิดขึ้นในข้อความกี่ครั้งส่งกลับ TRUE ถ้าจำนวนมากกว่า 1;มิฉะนั้นจะคืนค่า FALSE
นอกจากนี้ยังสร้างขึ้นเพื่อนับเฉพาะคำที่มีอักขระมากกว่าสามตัว
เมื่อฉันมีข้อมูล TRUE/FALSE แล้ว ฉันสามารถกรองระเบียนทั้งหมดที่เป็น TRUE ได้อย่างง่ายดาย
ตอนนี้ให้ฉันแสดงวิธีการทำสิ่งนี้ใน Excel
รหัส VBA สำหรับฟังก์ชันที่กำหนดเอง
ทำได้โดยการสร้างฟังก์ชันแบบกำหนดเองใน VBAฟังก์ชันนี้สามารถใช้เป็นฟังก์ชันเวิร์กชีตอื่นๆ ใน Excel ได้
นี่คือรหัส VBA:
Function IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) ถึง 0 ขั้นตอนที่ -1 ถ้า Len(StringtoAnalyze(i)) < minWordLen จากนั้น GoTo SkipA For j = 0 To i - 1 ถ้า StringtoAnalyze(j) = StringtoAnalyze(i) จากนั้น IdDuplicates = "TRUE" GoTo SkipB End ถ้าถัดไป j SkipA: ถัดไป i IdDuplicates = "FALSE" SkipB: สิ้นสุดฟังก์ชัน
วิธีใช้โค้ด VBA นี้
เมื่อคุณมีโค้ด VBA แล้ว คุณต้องใส่โค้ดดังกล่าวในแบ็กเอนด์ของ Excel เพื่อให้สามารถทำงานเป็นฟังก์ชันเวิร์กชีตปกติได้
ต่อไปนี้เป็นขั้นตอนในการใส่โค้ด VBA ในส่วนแบ็คเอนด์:
- ไปที่แท็บนักพัฒนา
- คลิกที่ Visual Basic (คุณยังสามารถใช้แป้นพิมพ์ลัด ALT+F11)
- ในแบ็กเอนด์ตัวแก้ไข VB ที่เปิดอยู่ ให้คลิกขวาที่วัตถุสมุดงาน
- ไปที่ "แทรก" และคลิก "โมดูล"ซึ่งจะแทรกอ็อบเจ็กต์โมดูลของเวิร์กบุ๊ก
- ในหน้าต่างโค้ดโมดูล ให้คัดลอกและวางโค้ด VBA ที่กล่าวถึงข้างต้น
เมื่อคุณมีโค้ด VBA ในแบ็กเอนด์แล้ว คุณสามารถใช้ฟังก์ชัน – 'IdDuplicates' เป็นฟังก์ชันเวิร์กชีตปกติอื่นๆ ได้
ฟังก์ชันนี้รับพารามิเตอร์หนึ่งตัว การอ้างอิงเซลล์ของเซลล์ที่คุณมีข้อความ
ผลลัพธ์ของฟังก์ชันนี้คือ TRUE (หากมีคำที่ซ้ำกันอยู่ในนั้น) หรือ FALSE (หากไม่มีคำซ้ำ)เมื่อคุณมีรายการ TRUE/FALSE นี้แล้ว คุณสามารถกรองเซลล์ที่มี TRUE เพื่อรับเซลล์ทั้งหมดที่มีสตริงข้อความซ้ำกัน
หมายเหตุ: รหัสที่ฉันสร้างใช้สำหรับคำที่ยาวเกินสามอักขระเท่านั้นเพื่อให้แน่ใจว่าหากมีคำในสตริงข้อความที่มีความยาว 1, 2 หรือ 3 อักขระ (เช่น 12 A, KGM หรือ LDA) คำเหล่านั้นจะถูกละเว้นเมื่อนับซ้ำคุณสามารถเปลี่ยนรหัสนี้ได้อย่างง่ายดายหากจำเป็น
คุณลักษณะนี้มีเฉพาะในเวิร์กบุ๊กที่คุณคัดลอกโค้ดในโมดูลเท่านั้นถ้าคุณต้องการให้โค้ดนี้พร้อมใช้งานในเวิร์กบุ๊กอื่นด้วย คุณต้องคัดลอกและวางโค้ดนี้ลงในเวิร์กบุ๊กเหล่านั้นอีกวิธีหนึ่ง คุณสามารถสร้าง Add-in ได้ (การเปิดใช้ฟีเจอร์นี้จะทำให้ฟีเจอร์นี้พร้อมใช้งานในเวิร์กบุ๊กทั้งหมดในระบบของคุณ)
นอกจากนี้ อย่าลืมบันทึกเวิร์กบุ๊กนี้ด้วยนามสกุล .xlsm (เนื่องจากมีโค้ดมาโคร)
