แยกข้อมูลในเซลล์โดยให้ค่าที่ซ้ำแสดงค่าเดียว

ปกติแล้วการแยกค่าในเซลล์เดียวเป็นหลายเซลล์สามารถใช้ Data > Text to columns เข้ามาช่วยได้ และถ้าหากในเซลล์นั้นคั่นแต่ละส่วนด้วยอักขระใด ๆ แล้วจะยิ่งแยกได้โดยง่าย แต่หากว่าเซลล์นั้นมีค่าซ้ำกันหลายค่าแต่เราต้องการนำค่าที่ซ้ำกันนั้นมาแสดงเพียงค่าเดียวย่อมจะมีความยุ่งยากลำบากกว่าปกติ

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

ภาพแสดงแยกข้อมูลในเซลล์โดยให้ค่าที่ซ้ำแสดงค่าเดียว

ที่เซลล์ B2 เราสามารถเขียนสูตรได้ดังนี้

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE( $A$2,",",REPT(" ",50)),50(ROW(INDIRECT("1:50"))-1)+1,50)),SMALL( IF(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",50)),50(ROW( INDIRECT("1:50"))-1)+1,50)),TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",50)), 50*(ROW(INDIRECT("1:50"))-)+1,50)),0),ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2)))),ROWS(B$2:B2))),"")

Ctrl+Shift+Enter > คัดลอกสูตรลงด้านล่าง สามารถดาวน์โหลดไฟล์ตัวอย่างไปศึกษาได้ตามสะดวก

กรณีมีปัญหาเกี่ยวกับการใช้งาน Excel และ VBA สามารถสอบถามได้ที่ Excel Forum

เทคนิคการตัดข้อความที่คั่นด้วยเครื่องหมายต่าง ๆ

การตัดข้อความกรณีที่มีเครื่องหมายต่าง ๆ คั่นระหว่างข้อความ นอกจากจะใช้ Text to columns ได้แล้ว เรายังสามารถใช้สูตรมาจัดการได้ เพื่อจะได้ไม่ต้องทำ Text to columns ซ้ำ ๆ ครับ

โดยหลักการก็คือจัดการแปลงเครื่องหมายที่ใช้คั่นข้อความให้เป็นค่าว่างเสียก่อนเป็นลำดับแรก แต่การแปลงเครื่องหมายที่ใช้คั่นข้อความให้เป็นค่าว่างนั้นมีเทคนิคที่ต้องแปลงเป็นค่าว่างเท่ากับจำนวนอักขระทั้งหมดของข้อความที่เรากำลังจะตัดครับ

ตัวอย่างตามภาพด้านล่าง ข้อความอยู่ที่เซลล์ A1 และคั่นด้วยเครื่องหมายคอมม่า (,) เราต้องการตัดข้อความออกเป็นแต่ละค่าโดยแสดงที่ A3 เป็นต้นไป

การแบ่งแต่ละข้อความเราสามารถใช้สูตรดังนี้ครับ

1. ที่ A2 คีย์
   =Len(A1)
   Enter
2. ที่ A3 คีย์สูตรเพื่อแบ่งข้อความ
   =TRIM(MID(SUBSTITUTE(","&A$1,",",REPT(" ",A$2)),ROWS(A$3:A3)*A$2,A$2))
   Enter > Copy ลงด้านล่าง

เราสามารถแกะสูตรในข้อ 2 ตามด้านล่างครับ

1. จากสูตรในข้อ 2 หมายถึงให้ตัดช่องว่างที่เกินความจำเป็นจากผลลัพธ์ของสูตร MID(SUBSTITUTE(","&A$1,",",REPT(" ",A$2)),ROWS(A$3:A3)*A$2,A$2)
2. จากสูตร MID(SUBSTITUTE(","&A$1,",",REPT(" ",A$2)),ROWS(A$3:A3)*A$2,A$2)) หมายถึงให้ตัดข้อความที่ได้จากผลลัพธ์ของสูตร SUBSTITUTE(","&A$1,",",REPT(" ",A$2)) โดยเริ่มจากตำแหน่งที่ได้จากผลลัพธ์ของสูตร ROWS(A$3:A3)*A$2 มาเป็นจำนวนอักขระตามค่าในเซลล์ A2
3. จากสูตร SUBSTITUTE(","&A$1,",",REPT(" ",A$2)) หมายถึงให้เปลี่ยนค่าอักขระ "," ในข้อความ ","&A$1 ให้เป็นอักขระ REPT(" ",A$2)
4. จากสูตร REPT(" ",A$2) หมายถึงให้แสดงค่า " " เป็นจำนวนครั้งเท่ากับค่าใน A2
5. จากสูตร ROWS(A$3:A3)*A$2 หมายถึงให้นำจำนวนแถวในช่วง A$3:A3 มาคูณกับค่าใน A2 เพื่อจะใช้เป็นจุดเริ่มตัดข้อความ

การตัดข้อความกรณีที่คำนำหน้านามติดกับชื่อ

กรณีที่คำนำหน้านามไม่ติดกับชื่อ สามารถใช้ Excel จัดการได้ง่าย ๆ ครับไม่ว่าจะด้วยสูตรหรือใช้ Text to columns แต่หากเมื่อใดที่คำนำหน้านามมีความหลากหลายทั้งยังอยู่ติดกับชื่อแล้ว การจัดการจะยุ่งยากขึ้นครับ

ตามตัวอย่างนี้จะแสดงวิธีการหนึ่งที่จะแยกคำนำหน้านาม ชื่อ และนามสกุลออกจากกันในกรณีที่คำนำหน้านามมีความหลากหลาย ซึ่งจะใช้สูตร Array เข้ามาช่วยครับ

ภาพประกอบการตัดข้อความกรณีมีคำนำหน้านามติดกับชื่อ

 
ซึ่งมีขั้นตอนดังนี้

1. จากภาพให้ทำการกรอกคำนำหน้าชื่อที่คิดว่าจะมีทั้งหมดก่อนที่ F2:F12
2. แล้วคีย์ในแต่ละเซลล์ดังนี้
1. เซลล์ D3 คีย์สูตรเพื่อตัดนามสกุลมาแสดง
   =RIGHT(A3,LEN(A3)-FIND(" ",A3))
   Enter
2. เซลล์ C3 คีย์สูตรเพื่อตัดชื่อมาแสดง
   =SUBSTITUTE(RIGHT(A3,MIN(LEN(SUBSTITUTE(A3,$F$2:$F$13,"")))),D3,"") Ctrl+Shift+Enter
3. เซลล์ B3 คีย์สูตรเพื่อตัดคำนำหน้านามมาแสดง
   =SUBSTITUTE(SUBSTITUTE(A3,C3,""),D3,"")
   Enter

ตัวอย่างวิธีคิดหลักๆ จากสูตรในเซล C3

1. เปลี่ยนคำนำหน้านามให้เป็นค่าว่าง ซึ่งจะเหลือเพียงชื่อและนามสกุล แล้วนับอักขระ จะได้จำนวนอักขระไม่รวมคำนำหน้านาม คำไหนที่ถูกเปลี่ยนโดยเอาคำนำหน้านามออกไปแล้ว จำำนวนอักขระจะน้อยลง เอาค่าที่น้อยที่สุดไปกระทำในข้อที่ 2 (ดูตัวอย่า่งการแกะสูตรด้านล่างประกอบ)
2. ใช้สูตร Right() เพื่อตัดอักขระด้านขวาของเซลต้นแหล่งให้เหลือเท่ากับจำนวนที่นับได้ในข้อ 1 ผลลัพธ์จะได้ชื่อและนามสกุล
3. ใช้สูตร Substitute() เพื่อเปลี่ยนนามสกุล(ซึ่งหาไว้ก่อนแล้วซึ่งก็คือเซล D3)ให้เป็นค่าว่างโดยต้องการให้เหลือแค่ชื่อ

ตัวอย่างการแกะสูตร จากสูตรที่ C3 ซึ่งเป็นสูตร Array

จากสูตร =SUBSTITUTE(RIGHT(A3,MIN(LEN(SUBSTITUTE(A3,$F$2:$F$12,"")))),D3,"")

1. เปลี่ยนคำนำหน้านามให้เป็นค่าว่าง
   คลุม SUBSTITUTE(A3,$F$2:$F$12,"") แล้วกดแป้น F9 จะได้
   =SUBSTITUTE(RIGHT(A3,MIN(LEN({"นางสาวมานี สวยสม";"สาวมานี สวยสม";"มานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม"}))),D3,"")
2. นับอักขระที่เหลือจากการเปลี่ยนคำนำหน้าชื่อให้เป็นค่าว่างแล้ว
   คลุม LEN({"นางสาวมานี สวยสม";"สาวมานี สวยสม";"มานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม"}) แล้วกดแป้น F9 จะได้
   =SUBSTITUTE(RIGHT(A3,MIN({16;13;10;16;16;16;16;16;16;16;16})),D3,"")
3. หาค่าที่น้อยที่สุดหลังจากนับแล้ว
   คลุม MIN({16;13;10;16;16;16;16;16;16;16;16}) แล้วกดแป้น F9 จะได้
   =SUBSTITUTE(RIGHT(A3,10),D3,"")
   คำตอบคือ "มานี "
   จะเห็นได้ว่าคำว่า "มานี " มีวรรคต่อท้ายมา 1 อักขระ ซึ่งสามารถทีจะใช้ฟังก์ชั่น Trim ครอบสูตรที่ C3 และ B3 เพื่อที่จะตัดช่องว่างนี้ออกไปได้ครับ ซึ่งจะได้สูตรเป็น
1. เซลล์ C3
   =TRIM(SUBSTITUTE(RIGHT(A3,MIN(LEN(SUBSTITUTE(A3,$F$3:$F$13,"")))),D3,""))
   Ctrl+Shift+Enter
2. เซลล์ B3
   =TRIM(SUBSTITUTE(SUBSTITUTE(A3,C3,""),D3,""))
   Enter