[Update] วิธีจัดการข้อมูลซ้ำใน Excel (แบบลึกซึ้ง) | clean ช่อง 2 – NATAVIGUIDES

หากเราไม่แน่ใจว่ามีข้อมูลซ้ำอยู่ในรายการข้อมูล Excel หรือไม่? แล้วเราอยากจะรู้หรืออยากจะทำให้ข้อมูลมันไม่ซ้ำกัน เราจะทำยังไงดี? มาดูกันครับ

สถานการณ์ คือ เรามีข้อมูล เป็น Customer ID และวันที่ของการบันทึกข้อมูลลูกค้า
สมมติว่า เราอยากจะได้เฉพาะข้อมูลครั้งล่าสุดเท่านั้น เราจะทำยังไงดี?

แนวทาง #1 : ลบข้อมูลซ้ำด้วย Remove Duplicates

เครื่องมือ Remove Duplicates มันจะเก็บข้อมูลไว้เฉพาะบรรทัดบนสุดเท่านั้น ดังนั้นก่อนจะใช้เครื่องมือนี้ ผมแนะนำให้เรียงข้อมูลให้อยู่ในลักษณะที่ หากข้อมูลซ้ำกัน ให้เอาแถวที่เราอยากได้ไว้บนสุดซะก่อนครับ โดยใช้ Data –> Sort ก่อน

ในที่นี้เราจะเรียงตาม CustomerID ก่อน แล้วเรียงตามวันที่ โดยเอาวันที่ใหม่สุดไว้บน

จากนั้นเราจะได้ข้อมูลที่เรียงกันในแบบที่อยากได้แล้ว

ให้เราเลือกข้อมูลช่องใดช่องหนึ่ง แล้วไปที่ Data -> Remove Duplicates

แล้วให้ติ๊กเฉพาะ CustomerID เท่านั้น (ติ๊กเอา Date ออกซะ) เพราะถ้าติ๊กทั้งคู่มันจะมองทั้งสองคอลัมน์ ซึ่งจะทำให้มันไม่เอา Cust 004 วันที่ 15/3/2019 ออกไป

พอกด ok จะเห็นว่า ในคอลัมน์ CustomerID ที่ซ้ำๆ กัน ผลลัพธ์จะเหลือเฉพาะบรรทัดบนสุดของแต่ละตัวเท่านั้น (นี่แหละการเรียงถึงสำคัญ)

และสังเกตว่า Remove Duplicates มัยไม่สนใจเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย (มองว่าเหมือนกัน ลองดูที่ CUST-007 สิ)

แนวทาง #2 : Sort แล้วเขียนสูตรเทียบกับตัวบน

วิธีนี้ผมจะ Sort เช่นเดียวกับตอนทำ Remove Duplicates เพื่อให้ตัวที่อยากได้อยู่บน

จากนั้นจะเขียนสูตรเช็ค Duplicates ง่ายๆ ดังนี้ ว่า CustomerID บรรทัดตัวเอง ซ้ำกับ CustomerID บรรทัดเหนือมัน 1 อันรึเปล่า? ถ้าซ้ำก็เรียกว่า Dup ตัวบนนี่แหละ

ซึ่งถ้าเป็น TRUE ก็จะเป็นตัวที่เราไม่ต้องการนั่นเอง

เขียนสูตรนี้เสร็จแล้ว ถ้าจะลบข้อมูลซ้ำทิ้งเลย จะ Filter TRUE แล้วลบ Row ทิ้งตรงๆ ก็ได้

มันจะเหลือเฉพาะแถวที่ต้องการแล้ว
ส่วน Column C ที่เอาไว้ Check Duplicates ไม่ใช้แล้วก็ลบทิ้งได้ครับ

Tips : ถ้ายังไม่ลบทิ้ง แล้วจะเอาไปใช้อะไรต่อ อย่าลืม Paste Value ตัว TRUE / FALSE ด้วย ไม่งั้นพอ Sort ใหม่แล้วเดี๋ยวสูตรผิดนะครับ

แนวทาง #3 : ดึงเอาเฉพาะข้อมูลที่ไม่ซ้ำด้วย PivotTable

วิธีนี้เราจะใช้ PivotTable สรุปข้อมูล ซึ่งทำไม่ยากเลย ดังนี้

เลือกข้อมูลช่องใดช่องหนึ่ง แล้ว Insert -> PivotTable -> ok

ลาก CustomerID ไว้ที่ Row และ Date ไว้ที่ Value (สังเกตว่า PivotTable ก็ไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่เหมือนกัน)

จากนั้นคลิ๊กขวาตรง Count of Date แล้วเปลี่ยน Summarized Value by เป็น Max

ที่เห็นเป็นเลข 4 หมื่นกว่าๆ จริงๆ ก็คือวันที่นี่แหละ ถูกแล้ว แต่ว่าแค่ยังไม่ได้เปลี่ยน Format เป็นวันที่เท่านั้นเอง ดังนั้นเราจะคลิ๊กขวา เลือกเมนู Number Format… (อย่าเลือก เมนู Format Cell นะ) แล้วเปลี่ยนให้เป็นวันที่ซะ

แนวทาง #4 : ใช้ Power Query

(เดิมเขียนผิด อันนี้แก้แล้ว)

Power Query นี่ทำงานพวกนี้ได้สบายมากๆ ครับ เรายังไม่จำเป็นต้องเรียงข้อมูลตอนแรกด้วยซ้ำ (เพราะไปเรียงใน Power Query ได้)

ให้เลือกข้อมูลช่องใดช่องหนึ่งแล้วเอาเข้า Power Query ตรง import from Table/Range แล้วกด Ok

เรื่องจาก Power Query จะให้ความสำคัญกับตัวพิมพ์ใหญ่ พิมพ์เล็กด้วยเสมอ ดังนั้นเราจะทำการแปลงข้อมูลให้มีลักษณะเหมือนกันก่อน เช่น ใช้ Transform Capitalize Each Word เพื่อแก้ปัญหาให้ CUST-007 ก่อน

จากนั้น ผม Sort ตาม customer A to Z และ กด Sort ตามวันที่ ใหม่ไปเก่า จะได้ดังรูป

จากนั้นเลือก คอลัมน์แรก แล้ว เลือก Remove Rows -> Remove Duplicates

มันจะเหลือเฉพาะตัวที่ไม่ซ้ำแล้ว แต่ปรากฏว่า Cust-004 ดันเหลือ 15/3/2562 ซะงั้น!! ทั้งๆ ที่ควรจะเหลือวันที่ 8/4/2562 ซึ่งเราอุตส่าห์เอามาไว้ข้างบนแล้วแท้ๆ

ซึ่งปรากฏการณ์ที่เกิดขึ้นนี้ ผมมองว่าเป็น Bug ของ Power Query ครับ ซึ่งมีวิธีแก้ไขคือ ให้กด Add Index Number แทรกเอาไว้หลังจาก sort (ก่อนจะสั่ง Remove Duplicates ) ครับ

โดยกดย้อนกลับไปที่ Step การ Sort

แล้วกด Add Index Column -> From 1
แล้วมันจะเตือนว่าเป็นการ Insert step ก็ ok ไปครับ

กดไปที่ Step สุดท้าย

จะเห็นว่าผลลัพธ์ถูกต้องแล้วครับ

จากนั้น ลบ คอลัมน์ index ออกไป เพราะไม่ใช้แล้ว จากนั้นกด Close & Load เป็นอันจบ

แถม 1 # : ระบุข้อมูลซ้ำด้วย Conditional Formatting

ถ้าเราอยากจะแค่รู้ว่ามีข้อมูลซ้ำรึเปล่า? ก็ใช้ Conditional Format -> Highlights Duplicates มาช่วยได้

ผมเลือกแค่คอลัมน์แรกก่อน ค่อยกด Highlight ดังนี้ แล้ว ok

จะเห็นว่ามัน Highlight ส่วนที่ซ้ำกันให้

เราสามาถ Filter เฉพาะตัวที่ Highlights ได้ถ้าต้องการ โดยคลิ๊กขวา Filter by Selected Cell’s Color

มันก็จะเหลือเฉพาะบรรทัดที่มีประเด็น แต่ว่าเราจะจัดการยังไงต่อ ก็เรื่องของเราแล้วล่ะ ซึ่งแนวทางใช้ Conditional Format นี้ ไม่สามารถกำจัดตัวซ้ำได้ด้วยตัวมันเองนะครับ

แถม #2 : ใช้สูตรคำนวณหา Max Date ของลูกค้าแต่ละคน

ใครมี Excel 2016 ขึ้นไป จะมี MAXIFS ให้ใช้ ก็ง่ายหน่อย
=MAXIFS(max_range,criteria_range,criteria,…)
=MAXIFS([Date],[CustomerID],[@CustomerID]) หรือ
=MAXIFS($B$3:$B$15,$A$3:$A$15,A3)

ถ้าไม่มี MAXIFS ก็ต้องใช้ Array Formula
=MAX(([CustomerID]=[@CustomerID])*[Date]) แล้วกด Ctrl+Shift+Enter หรือ
=MAX(($A$3:$A$15=A3)*$B$3:$B$15) แล้วกด Ctrl+Shift+Enter

จะเห็นว่าถ้าทำเป็น Table ก่อน สูตรจะอ่านง่ายกว่านะผมว่า

แบบ Range ต้องมาเล็งว่า A3 คืออะไรอีก…

หมายเหตุ : ภาพ Cover เป็นตัวละครจากการ์ตูนเรื่อง Naruto ใช้คาถาแยกเงาพันร่าง

 ใช่ช่องเซลล์ทำอย่างไร

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

📎

ขั้นตอนที่ 2

กด Tap ช่อง “Replace with” ให้เพื่อนๆปล่อยว่างๆไว้เช่นเดิม

📎

ขั้นตอนที่ 3

ใช้ Mouse คลิกบนแถบ

“Replace All”

ดังรูปที่ 4

สรุป

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

หลักการทำงานของวิธีนี้ คือ

เอ็กเซลจะค้นหาเซลทั้งหมดที่มีช่องว่าง 1 ช่องตามเงื่อนไขที่ใส่ใว้ในช่องของ “Find what”
เอ็กเซลจะแทนที่ด้วยการที่

ไม่

มีช่องว่างที่อยู่ในเงื่อนไขของช่อง “Replace”

อธิบายเพิ่มเติม

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

ช่อง “Replace” นั้น คำแปลของเขาค่อนข้างชัดเจนอยู่แล้วคือแปลว่า “แทนที่” เขามีหน้าที่ที่จะนำข้อมูลที่เรา-ท่านกรอกลงไปในช่อง “Replace” นี้ไปใส่แทนคำที่ค้นหาเจอจากช่อง “Find what”(หาอะไร) นั่นเอง

ดังนั้น เคล็ดลับที่เราใช้ก็คือ เรา

ไม่

ใส่ค่าใดๆลงไปในช่อง “Replace” เลย นั่นแสดงว่าค่าในช่อง “Replace” ก็คือ ไม่มีค่าใดๆ (ไม่มีตัวหนังสือ ไม่มีตัวเลข ไม่มีเครื่องหมายใดๆ รวมทั้งไม่มี

ช่องว่าง

)

ฉะนั้น Logic ที่ผมคิดก็คือ เมื่อ “Find what” ทำการค้นหาเจอ ช่องว่าง(Space)ก็จะส่งค่านี้ต่อไปให้ “Replace” ทำงานต่อ เมื่อไม่มีการกรอกข้อมูลใดไว้ในช่อง “Replace” ช่อง “Replace” จึงส่งค่าที่ไม่มีข้อมูลใดๆไปแทนที่ ดังนั้นเมื่อเขาพบ “ช่องว่าง(Space)” ที่ส่งมาจากช่อง”Find what” ช่อง “Replace” จึงส่งค่าไม่ว่างไปแทนที่ในช่อง “Find what” ผลก็คือ ค่า “ช่องว่าง” ถูกแทนที่ด้วย “ไม่ว่าง” แทนนั่นเองครับ

“Find what” และ “Replace” จะทำงานโดยผมจะอธิบายง่ายๆก็คือ เขาจะทำงานในคราวเดียวกันเบร็ดเสร็จครับเมื่อท่านใช้ Mouse คลิกไปที่แถบ “Replace All”

วิธีนี้สามารถปรับใช้งานได้ดังนี้

ความพิเศษของวิธีนี้ คือ

1. ทำได้ง่าย 
2. ไม่ต้องใช้สูตร 
3. ไม่ต้อง copy และ paste 
4. สามารถทำได้ในคราวเดียวกันทั้ง Sheet หรือเลือกเฉพาะเจาะจงเฉพาะที่ได้
5. สามารถลบช่องว่าง(Space)ได้ทุกที่ที่มีช่องว่างอยู่ ไม่ว่าจะเป็น ด้านหน้า กลาง ด้านหลังคำ หรือ ระหว่างคำ
6. ไม่ต้องระบุเซลอ้างอิง
7. สามารถใช้ได้แม้จะไม่ทราบว่ามีช่องว่างอยู่ในเซลใดบ้าง

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

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

วิธีการลบช่องว่างต่างๆที่ผมพบในระบบปฏิบัติการเอ็กเซลนั้น แม้กระทั่งคนออกแบบหรือเขียนเอ็กเซลเองก็เขียนสูตรต่างๆเอาไว้เพื่อช่วยในการแก้ปัญหาที่เกิดจากการที่มี “ช่องว่าง(Space)” เกิดขึ้น

แต่สูตรเหล่านั้นก็มีข้อจำกัดของการนำไปใช้งาน

ข้อจำกัดใช้งานสูตรการลบช่องว่าง(Space)เคาะ เช่น สูตร = trim() / =SUBSTITUTE() เช่น

• ต้องมีเซลที่ว่างเพื่อกรอกสูตรนั้นๆลงไป
• จะต้องมีการระบุเซลอ้างอิงหรือช่วงของเซลเสียก่อนจึงจะใช้สูตรใด้
• ไม่เหมาะกับช่องว่างที่มีอยู่หลายๆเซลหรือว่ากระจัดกระจาย
• ต้องทำการ copy และ paste ข้อมูลที่ได้มาใหม่นั้นใส่ทับลงไปในเซลเดิม

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

เมื่อท่านโชคดีที่บังเอิญได้อ่านความพิเศษของการใช้งานของ “Find & Replace” สำหรับใช้ ลบช่องว่างเคาะ(Space) ท่านมีคนที่ใช้งานเอ็กเซลที่ท่านรู้จักอีกหรือไม่ หากว่ามี กรุณาท่านช่วยบอกต่อและส่งต่อลิงค์ไปให้เพื่อนๆของท่านด้วย

กรณีที่ท่านต้องการสอบถามเพิ่มเติมหรือว่าต้องการติ-ชมใดๆ เรายินดีรับฟังทุกข้อคิดเห็นและข้อเสนอแนะครับ

จากการทำตามขั้นตอนทั้งหมดผมที่เขียนไว้ข้างบนนี้ ท่านก็จะได้ผลลัพธ์ตามที่ต้องการคือ ช่องว่างจะหายไป ซึ่งท่านสามารถสังเกตุเห็นได้จากรูปที่ 4 จะเห็นว่า คำว่า “ฉัน” ที่เป็นคำแรกของประโยคไปอยู่ชิดติดกับขอบเส้นด้านซ้ายของคอลัมน์ C เรียบร้อยแล้วเอ็กเซลจะค้นหาเซลทั้งหมดที่มีช่องว่าง 1 ช่องตามเงื่อนไขที่ใส่ใว้ในช่องของ “Find what”เอ็กเซลจะแทนที่ด้วยการที่มีช่องว่างที่อยู่ในเงื่อนไขของช่อง “Replace”ช่อง “Find what” จะค้นหาค่าที่เราใส่ลงไป ซึ่งส่วนใหญ่แล้วจะเป็นตัวเลขหรือตัวหนังสือที่เราสามารถมองเห็นมันได้ แต่ผมได้พลิกแพลงคิดต่อไปว่า กรณีของช่องว่างที่มองไม่เห็นด้วยตาเปล่าของมนุษย์เรานั้น เอ็กเซลน่าจะมองเห็น ผมจึงได้ทดลองทำการ”เคาะ”ลงไปในช่องดังกล่าวครับช่อง “Replace” นั้น คำแปลของเขาค่อนข้างชัดเจนอยู่แล้วคือแปลว่า “แทนที่” เขามีหน้าที่ที่จะนำข้อมูลที่เรา-ท่านกรอกลงไปในช่อง “Replace” นี้ไปใส่แทนคำที่ค้นหาเจอจากช่อง “Find what”(หาอะไร) นั่นเองดังนั้น เคล็ดลับที่เราใช้ก็คือ เราใส่ค่าใดๆลงไปในช่อง “Replace” เลย นั่นแสดงว่าค่าในช่อง “Replace” ก็คือ ไม่มีค่าใดๆ (ไม่มีตัวหนังสือ ไม่มีตัวเลข ไม่มีเครื่องหมายใดๆ รวมทั้งไม่มีฉะนั้น Logic ที่ผมคิดก็คือ เมื่อ “Find what” ทำการค้นหาเจอ ช่องว่าง(Space)ก็จะส่งค่านี้ต่อไปให้ “Replace” ทำงานต่อ เมื่อไม่มีการกรอกข้อมูลใดไว้ในช่อง “Replace” ช่อง “Replace” จึงส่งค่าที่ไม่มีข้อมูลใดๆไปแทนที่ ดังนั้นเมื่อเขาพบ “ช่องว่าง(Space)” ที่ส่งมาจากช่อง”Find what” ช่อง “Replace” จึงส่งค่าไม่ว่างไปแทนที่ในช่อง “Find what” ผลก็คือ ค่า “ช่องว่าง” ถูกแทนที่ด้วย “ไม่ว่าง” แทนนั่นเองครับ”Find what” และ “Replace” จะทำงานโดยผมจะอธิบายง่ายๆก็คือเขาจะทำงานในคราวเดียวกันเบร็ดเสร็จครับเมื่อท่านใช้ Mouse คลิกไปที่แถบ “Replacell”วิธีนี้สามารถปรับใช้งานได้ดังนี้ของวิธีนี้ คือวิธีนี้เมาก เพราะเป็นวิธีที่ง่าย ทำได้โดยที่ไม่ต้องเขียนสูตรอะไรใช้ซับซ้อนเสียเวลา เพราะการเขียนสูตรหรือใช้สูตรต้องใช้ตรรกะในการคิด เมื่อคิดได้แล้วก็ต้องทำการ copy และ paste ต่างๆทับเซลเดิมอีกครั้งหนึ่ง ซึ่งมันมีขั้นตอนที่มากพอสมควรวิธีนี้เป็นวิธีที่มีการเผยแพร่ในที่นี้เป็นที่แรกในโลกออนไลน์ เพราะผมพยายามค้นหาวิธีต่างๆด้วยตัวผมเอง ผมคิดและพลิกแพลงจากสิ่งที่มีอยู่ ซึ่งวิธีนี้ก็มีความภูมิใจที่คิดได้ด้วยตัวผมเอง จึงได้นำมาแบ่งปันให้ท่านได้นำไปใช้กันให้เกิดประต่อตัวท่านและส่วนรวมต่อไปวิธีการลบช่องว่างต่างๆที่ผมพบในระบบปฏิบัติการเอ็กเซลนั้น แม้กระทั่งคนออกแบบหรือเขียนเอ็กเซลเองก็เขียนสูตรต่างๆเอาไว้เพื่อช่วยในการแก้ปัญหาที่เกิดจากการที่มี “ช่องว่าง(Space)” เกิดขึ้นแต่สูตรเหล่านั้นก็มีข้อจำกัดของการนำไปใช้งานดังนั้นวันนี้ท่านก็ถือว่าเป็นโชคเป็นลาภที่ได้พบกับวิธีการใช้งานที่ง่าย ไม่ซับซ้อน ขั้นตอนน้อย และได้ผลลัพธ์ที่ทรงประสิทธิภาพจากการปรับประยุกต์(Apply)ใช้จากเราเมื่อท่านโชคดีที่บังเอิญได้อ่านความพิเศษของการใช้งานของ “Find & Replace” สำหรับใช้ ลบช่องว่างเคาะ(Space) ท่านมีคนที่ใช้งานเอ็กเซลที่ท่านรู้จักอีกหรือไม่ หากว่ามี กรุณาท่านช่วยบอกต่อและส่งต่อลิงค์ไปให้เพื่อนๆของท่านด้วยกรณีที่ท่านต้องการสอบถามเพิ่มเติมหรือว่าต้องการติ-ชมใดๆ เรายินดีรับฟังทุกข้อคิดเห็นและข้อเสนอแนะครับ