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

ปกติแล้วการแยกค่าในเซลล์เดียวเป็นหลายเซลล์สามารถใช้ 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 สูตรลงด้านล่าง สามารถดาวน์โหลดไฟล์แนบตามด้านล่างไปศึกษาได้ตามสะดวกครับ

การแกะสูตรการหาจำนวนอักขระจากข้อความ

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

ยกตัวอย่างเช่น เราจะหาว่าตัว W ปรากฏกี่ครั้งในข้อความด้านล่างนี้ซึ่งอยู่ที่เซลล์ A1

When Where What Who How

ซึ่งหากนับด้วยตาเราก็พบว่า 5 อักขระ ใครที่คิดว่าตนเองมีความชำนาญในการใช้ฟังก์ชั่น ลองคิดหาวิธีก่อนที่ผมจะเฉลยดูครับ

ฟังก์ชั่นที่ใช้ในการหาจำนวนครั้งที่ปรากฏของ W คือที่ B1 คีย์

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))

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

การแกะสูตร

จากสูตร =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))

1. Len(A1) เป็นการนับว่าอักขระทั้งหมดมีกี่อักขระ คำตอบคือ 23
2. Upper(A1) เป็นการแปลงให้เซลล์ A1 เป็นตัวใหญ่ทั้งหมด การแกะสูตรให้นำเมาส์ไปคลุม Upper(A1) ที่ Formula bar แล้วกดแป้น F9 จะได้เป็น "WHAT WHERE WHEN WHO HOW"
3. จากข้อ 2 จะได้เป็น Substitute("WHAT WHERE WHEN WHO HOW","W","") ซึ่งหมายความว่าให้เปลี่ยน W ที่ปรากฎในข้อความให้เป็นค่าว่าง

หากลากเมาส์คลุม Substitute("WHAT WHERE WHEN WHO HOW","W","") แล้วกดแป้น F9 จะได้เป็น

LEN("HAT HERE HEN HO HO")

จะเห็นว่าตัว W หายไป ผลลัพธ์จากฟังก์ชั่น LEN("HAT HERE HEN HO HO") จะได้ค่า 18

เมื่อนำ 23 จากข้อ 1 มาหักจาก 18 ตามข้อ 3 จะได้ 5 ซึ่งเป็นคำตอบตรงกับที่นับด้วยตา

คำถาม ทำไมต้องใช้ฟังก์ชั่น Upper() มาช่วย

คำตอบ เนื่องจากโจทย์นี้มีทั้ง W และ w สองตัวนี้มีค่าไม่เทียบเท่ากันในฟังก์ชั่น Substitute จึงจำเป็นต้องแปลงให้เป็นตัวเล็กหรือตัวใหญ่เสียก่อน ซึ่งผมแปลงให้เป็นตัวใหญ่ด้วยฟังก์ชั่น Upper

การค้นหาตัวเลขทะเบียนรถที่ถูกโฉลก

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

1. ให้รวมตัวเลขทุกตัวแล้วได้ค่าเป็น 9, 18, 27 หรือ 36
2. สามารถคัดชุดที่มีตัวเลขที่ไม่ต้องการออกไปได้ด้วย ยกตัวอย่างเช่นไม่รวมตัวเลขที่มีค่าต่อไปนี้ 0, 3, 7, 8

การหาค่าดังกล่าวสามารถใช้สูตรหรือ VBA มาคำนวณได้ครับ

กรณีใช้สูตรในการคำนวณมีวิธีการดังนี้

1. เปิดสมุดงานใหม่แล้วเลือกชีทว่าง
2. ที่ A1 คีย์ No. และ ที่ B1 คีย์ Select เพื่อเป็นชื่อ Field
3. ที่ A2 คีย์ Row()-1 > Copy ลงด้านล่างตามต้องการ
4. ที่ B2 คีย์สูตร
   =IF(MIN(LEN(SUBSTITUTE(A2,{0,3,7,8},"")))<LEN(A2),"",IF(ISNUMBER(MATCH(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),{9,18,27,36},0)),SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),""))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

กรณีใช้ VBA ในการคำนวณมีวิธีการดังนี้

1. กดแป้น Alt+11 เพื่อเปิดหน้าต่าง VBE
2. คลิกเมนู Insert > Module
3. Copy Code ด้านล่างไปวาง
   
   

   Sub ListNum()
       Dim i As Integer, bln As Boolean
       Dim c As Integer, j As Integer
       Dim a As String, s As Integer
       For i = 1 To 9999
           bln = False
           s = 0
           For j = 1 To Len(CStr(i))
               a = Mid(i, j, 1)
               Select Case a
                   Case 0, 3, 7, 8
                       bln = True
                       Exit For
                   Case Else
                       s = s + a
               End Select
           Next j
           If bln = False Then
               Select Case s
                   Case 9, 18, 27, 36
                       c = c + 1
                       Cells(c + 1, 1) = i
               End Select
           End If
       Next i
   End Sub
   

   
   
   
   
4. กดแป้น Alt+Q เพื่อกลับมายังโปรแกรม Excel
   
5. กดแป้น Alt+F8 > เลือก ListNum > Run

โปรแกรมจะทำการ Run ตัวเลขมาให้ตามเงื่อนไขที่ต้องการ

ภาพตัวอย่างการใช้งาน

เทคนิคการแทรกสัญญลักษณ์ต่าง ๆ ในข้อความในตำแหน่งที่ไม่แน่นอน

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

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

ภาพตัวอย่างการแทรกสัญญลักษณ์ “ / “ ในข้อความในตำแหน่งที่ไม่แน่นอน

จากภาพด้านบนจะเห็นว่าที่เซลล์ A2:A3 นั้นเป็นข้อความที่ประกอบด้วยชื่อคนแจ้งตามด้วยหน้าที่ที่ทำ และเราต้องการแทรก “ / “ ไว้ท้ายชื่อคนแจ้ง โดยมีฐานข้อมูลชื่อคนแจ้งที่เซลล์ A10:A14

เราสามารถทำดังนี้ครับ

ที่เซลล์ B2 คีย์สูตรเพื่อแทรกสัญญลักษณ์หลังชื่อคนแจ้ง

=SUBSTITUTE(A2,INDEX($A$10:$A$14,MATCH(MIN(LEN(SUBSTITUTE(A2,$A$10:$A$14,""))),LEN(SUBSTITUTE(A2,$A$10:$A$14,"")),0)),INDEX($A$10:$A$14,MATCH(MIN(LEN(SUBSTITUTE(A2,$A$10:$A$14,""))),LEN(SUBSTITUTE(A2,$A$10:$A$14,"")),0))&" / ")

Ctrl+Shift+Enter > Copy ไปด้านล่าง ซึ่งการกดแป้นให้รับสูตรต้องกดแป้น Ctrl+Shift ค้างไว้แล้วตามด้วย Enter จะกดแป้น Enter อย่างเดียวไม่ได้ครับ หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ การแก้ไขเปลี่ยนแปลงสูตร จะต้องกดให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

หลักการของสูตรคือ หาว่ามีข้อมูลในตำแหน่งใดในช่วง A10:A14 อยู่ในข้อความที่ A2 ก็ให้นำค่าในตำแหน่งนั้นมาเชื่อมด้วย “ / “ แล้วเปลี่ยนแทนข้อมูลเดิม

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

การตัดข้อความกรณีที่มีเครื่องหมายต่าง ๆ คั่นระหว่างข้อความ นอกจากจะใช้ 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, 2, 3, …0 ให้เป็น A, B, C, …J

การเปลี่ยนข้อความลักษณะนี้สามารถใช้สูตร Substitute มาช่วยได้ครับ แต่หากตัวที่ต้องการเปลี่ยนมีจำนวนมากใน Excel 2003 จะต้องพึ่งเซลล์ช่วยหลายเซลล์เนื่องจากสามารถซ้อนสูตรได้เพียง 7 ชั้น แต่หากเป็น Excel 2007 ขึ้นไป สามารถที่จะซ้อนสูตรได้ถึง 64 ชั้นจึงสามารถเขียนสูตรในเซลล์เดียวกรณีไม่เกิน 64 เงื่อนไข

ภาพประกอบ



ตัวอย่างการใช้สูตรใน Excel 2003

สมมุติค่าเป้าหมายเริ่มที่ A1

1. ที่ B1 คีย์สูตรในลำดับที่ 1
   =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"J"),1,"A"),2,"B"),3,"C"),4,"D"),5,"E"),6,"F"),7,"G")
   Enter
2. ที่ C1 คีย์สูตรในลำดับที่ 2 เพื่อเปลี่ยนให้เป็นค่าที่ต้องการ
   =SUBSTITUTE(SUBSTITUTE(B1,8,"H"),9,"I")
   Enter

ตัวอย่างการใช้สูตรใน Excel 2007

ที่ B1 คีย์
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"J"),1,"A"),2,"B"),3,"C"),4,"D"),5,"E"),6,"F"),7,"G"),8,"H"),9,"I")
Enter

สำหรับท่านที่คุ้นเคยกับการใช้งาน VBA สามารถเขียนฟังก์ชั่นในการเปลี่ยนค่าตามลักษณะสูตรด้านบนได้ดังนี้ครับ

Option Explicit 
Function ctext(r) As String 
    Dim i As Byte 
    For i = 1 To Len(r) 
        Select Case Mid(r, i, 1) 
            Case 1: Mid(r, i, 1) = "A" 
            Case 2: Mid(r, i, 1) = "B" 
            Case 3: Mid(r, i, 1) = "C" 
            Case 4: Mid(r, i, 1) = "D" 
            Case 5: Mid(r, i, 1) = "E" 
            Case 6: Mid(r, i, 1) = "F" 
            Case 7: Mid(r, i, 1) = "G" 
            Case 8: Mid(r, i, 1) = "H" 
            Case 9: Mid(r, i, 1) = "I" 
            Case 0: Mid(r, i, 1) = "J" 
        End Select 
    Next i 
    ctext = r 
End Function

และหากมีเงื่อนไขว่าให้ลบค่าอักขระที่ไม่ใช่ Number ออกทั้งหมด ส่วนค่าที่เป็น Number ให้เปลี่ยนเป็นอักษรตามเงื่อนไขด้านบน สามารถใช้ Code ตามด้านล่างได้ครับ

Function cntt(r) As String 
    Dim i As Byte 
    Dim b As Variant 
    b = Array("J", "A", "B", "C", "D", "E", "F", "G", "H","I") 
    For i = 1 To Len(r) 
        On Error Resume Next 
        cntt = cntt & b(Mid(r, i, 1)) 
    Next i 
End Function

การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว

สำหรับข้อมูลที่อยู่ในฟอร์มเดียวกันแต่แยกเป็นหลาย Sheet และต้องการนำมาต่อกันเป็นฐานข้อมูลใน Sheet เดียวกันนั้น สามารถใช้สูตรทำได้ครับ แต่อาจจะมีวิธีการหลายขั้นตอน ดังจะแสดงตามด้านล่าง

ภาพ Sheet ที่ใช้รวมข้อมูล 



ภาพ Sheet ที่ 1 



ภาพ Sheet ที่ 2 



ภาพ Sheet ที่ 3 



จากภาพด้านบนเป็นการนำข้อมูล Sheet1, 2, 3 มาต่อกัน ให้ทำตามลำดับดังนี้ครับ

1. เขียนชื่อชีทเรียงกันไว้ตามต้องการที่ G1:G3
2. เซลล์ H1 คีย์สูตรเพื่อนับว่ามีข้อมูลใน Sheet1 เท่าไร
   =COUNTA(INDIRECT("'"&G1&"'!"&"A2:A65536"))
   Enter แล้ว Copy ลงด้านล่าง
3. เซลล์ H4 รวมจำนวนจากด้านบน คีย์
   =SUM(H1:H3)
   Enter
4. เซลล์ I1 ใช้สูตรในการ Run ข้อมูลใหม่เพื่อใช้ในสูตร Lookup
   =SUM($H$1:H1)-H1+1
   Enter แล้ว Copy ลงด้านล่าง
5. เซลล์ E2 คีย์สูตรเพื่อ List รายชื่อ Sheet
   =IF(ROWS($E$2:E2)>$H$4,"",LOOKUP(ROWS($E$2:E2),$I$1:$I$3,$G$1:$G$3))
   Enter แล้ว Copy ไปด้านล่าง
6. เซลล์ A2 คีย์สูตรเพื่อนำข้อมูลใน Sheet ต่าง ๆ มาแสดงต่อกัน
   =IF($E2<>"",INDIRECT("'"&$E2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($E$2:$E2,$E2)+1),"")
   Enter แล้ว Copy ไปทางขวาและลงด้านล่าง

จะเห็นว่าการนำข้อมูลจากหลาย ๆ Sheet มาต่อกันนั้น ไม่ใช่เรื่องยากอีกต่อไปสำหรับพวกเราครับ

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

กรณีที่คำนำหน้านามไม่ติดกับชื่อ สามารถใช้ 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

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

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

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 ครับ