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

ปกติแล้วการแยกค่าในเซลล์เดียวเป็นหลายเซลล์สามารถใช้ 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 Functions

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

Video ด้านล่างนี้อธิบายความหมายและสาธิตการใช้งานฟังก์ชั่นในหมวด Text ซึ่งเป็นฟังก์ชั่นที่ใช้บ่อยครั้งในการทำงานกับ Excel จึงควรศึกษาให้เข้าใจ ใช้ให้เป็น ประโยชน์ที่สำคัญยิ่งอีกประการคือ ฟังก์ชั่นเหล่านี้จะเป็นพื้นฐานในการประยุกต์ใช้งานที่ซับซ้อนในลำดับสูง ๆ ขึ้นไป

Video แสดงการใช้งาน Text Functions

สอบถามปัญหาการใช้งาน Excel และ VBA ได้ที่ snasui.com

การจัดเรียงบ้านเลขที่

การจัดเรียงบ้านเลขที่นั้นไม่สามารถทำได้อย่างง่าย ๆ เนื่องจากบ้านเลขที่มักจะมีเครื่องหมายทับ (/) ปนอยู่ด้วย ทำให้เครื่องมือของ Excel ที่ใช้ในการจัดเรียงไม่สามารถให้ผลลัพธ์ที่ต้องการได้

การจัดเรียงบ้านเลขที่จึงต้องอาศัยวิธีการอื่นเข้ามาช่วย เช่นการแบ่งบ้านเลขที่ออกเป็น 2 คอลัมน์แล้วค่อยจัดเรียง หรือสามารถใช้สูตร Array เข้ามาช่วยได้

ภาพการเรียงบ้านเลขที่จากน้อยไปหามากด้วยสูตร

 

จากภาพด้านบนจะเป็นการนำข้อมูลบ้านเลขที่ในคอลัมน์ A มาเรียงใหม่ โดยให้เรียงจากน้อยไปหามาก

สูตรในเซลล์ C2 คือ

=INDEX($A$2:$A$14,MATCH(SMALL(--SUBSTITUTE(IF(ISNUMBER(FIND("/",$A$2:$A$14)),
$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT("0",10-LEN(MID($A$2:$A$14,
FIND("/",$A$2:$A$14&"/")+1,255)))),ROWS(C$2:C2)),--SUBSTITUTE(IF(
ISNUMBER(FIND("/",$A$2:$A$14)),$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT(
"0",10-LEN(MID($A$2:$A$14,FIND("/",$A$2:$A$14&"/")+1,255)))),0))

ซึ่งจะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array จากนั้น Copy สูตรลงด้านล่าง สามารถดาวน์โหลดไฟล์แนบตามด้านล่างไปศึกษาได้ตามสะดวกครับ

สูตรหาลำดับข้อความภายในอีกข้อความ

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

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

จากภาพด้านล่าง เซลล์ A2 เป็นตัวอย่างข้อความต้นทางหลายข้อความคั่นแต่ละข้อความด้วยเครื่องหมายคอมม่า ส่วนเซลล์ C2 เป็นข้อความเพียงบางส่วนที่เราจะนำไปหาจากข้อความใน A2 ว่าแต่ละข้อความใน C2 อยู่ในลำดับที่เท่าไรของ A2 โดยให้แสดงลำดับที่พบเรียงลงไปด้านล่าง เริ่มลำดับแรกที่เซลล์ D2

เมื่อนำแต่ละข้อความในเซลล์ C2 ไปหาจากข้อความในเซลล์ A2 จะพบว่า

1. A อยู่ในลำดับที่ 1
2. B อยู่ในลำดับที่ 2
3. AB อยู่ในลำดับที่ 6
4. K อยู่่ในลำดับที่ 9
5. CD อยู่ในลำดับที่ 3

ภาพแสดงการนำแต่ละข้อความในเซลล์ C2 ไปหาว่าอยู่ลำดับที่เท่าไรจากข้อความใน A2

[caption id="attachment_2767" align="alignnone" width="600"] String order in other string[/caption]

เราสามารถเขียนสูตรที่ D2 เพื่อหาคำตอบดังนี้

=IFERROR(INDEX(MATCH(MID($C$2,SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2)))),SMALL(IF(MID($C$2&",",ROW(
INDIRECT("1:"&LEN($C$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($C$2)+1))),
ROW(INDIRECT("1:"&LEN($C$2))))-SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2))))),MID($A$2,SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2)))),SMALL(IF(MID($A$2&",",ROW(
INDIRECT("1:"&LEN($A$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($A$2)+1))),
ROW(INDIRECT("1:"&LEN($A$2))))-SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2))))),0),ROWS(D$2:D2)),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

สูตรนี้เป็นสูตร Array ที่มีความซับซ้อนสูง ซ้อนกันหลายชั้นเพื่อที่จะตัดแต่ละข้อความออกมาเสียก่อน แล้วค่อยหาลำดับที่พบข้อความใน C2 จากข้อความใน A2 ผู้ที่จะศึกษาสูตรลักษณะนี้จะต้องเข้าใจแต่ละฟังก์ชั่นเป็นอย่างดีมาก่อน สามารถดาวน์โหลดไฟล์แนบด้านล่างไปศึกษาได้ตามสะดวกครับครับ

การตัดเอาตัวอักษรตัวแรกของข้อความมาแสดง

กรณีมีข้อความอยู่ในเซลล์ใด ๆ แล้วต้องการตัดตัวอักษรโดยมีเงื่อนไขตามด้านล่างคือ

1. ถ้าตัวอักษรแรกเป็น พยัญชนะ ให้ตัดตัวอักษรที่เป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา
2. ถ้าตัวอักษรแรกเป็น สระ ให้ตัดตัวอักษรที่ถัดจากสระซึ่งเป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา

ยกตัวอย่างเช่น

ถ้า A1 คือ กาญจนา ผลลัพท์ที่ต้องการ คือ ก
ถ้า A1 คือ เอกวัฒน์ ผลลัพท์ที่ต้องการ คือ อ
ถ้า A1 คือ รุ้งทอง ผลลัพท์ที่ต้องการ คือ ร

เราสามารถเขียนสูตรที่ B1 เพื่อหาอักขระตัวแรกของค่าใน A1 ได้ตามด้านล่างครับ

=MID(A1,MIN(SEARCH(CHAR(ROW($161:$206)),A1&CHAR(ROW($161:$206)))),1)

Ctrl+Shift+Enter

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 เพื่อจะใช้เป็นจุดเริ่มตัดข้อความ

แยกภาษาไทยออกจากอังกฤษหรืออังกฤษออกจากไทย

กรณีที่มีการเขียนภาษาอังกฤษต่อด้วยภาษาไทยหรือตรงกันข้ามกัน และต้องการแยกคำออกมา สามารถใช้สูตรในการแยกภาษาออกมาได้ครับ สมมุติ

1. เซลล์ A1 มีคำว่า Bangkokกรุงเทพมหานคร ต้องการแยก Bangkok และกรุงเทพมหานครให้อยู่คนละเซลล์สามารถทำได้ดังนี้
1. เซลล์ B1 แยก Bangkok ออกมาแสดงโดยคีย์สูตร
   =LEFT(A1,MATCH("zzz",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
   Ctrl+Shift+Enter
2. เซลล์ C1 แยกกรุงเทพมหานครออกมาแสดงโดยคีย์สูตร
   =Substitute(A1,B1,"")
   Enter
2. เซลล์ A1 มีคำว่า กรุงเทพมหานครBangkok ต้องการแยก กรุงเทพมหานคร และ Bangkok ให้อยู่คนละเซลล์สามารถทำได้ดังนี้
1. เซลล์ B1 แยกกรุงเทพมหานครออกมาโดยคีย์สูตร
   =LEFT(A1,MIN(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A1&"abcdefghijklmnopqrstuvwxyz")-1))
   Ctrl+Shift+Enter
2. เซลล์ C1 แยก Bangkok ออกมาแสดงโดยคีย์สูตร
   =Substitute(A1,B1,"")
   Enter

Note: สูตรที่ B1 ทั้งสองสูตรเป็นสูตรแบบอาร์เรย์ การคีย์ให้รับสูตรจะต้อง Ctrl+Shift+Enter ครับ