ซ่อมคอมพิวเตอร์นอกสถานที่ บางกะปิ รามคำแหง

วันพุธที่ 18 กรกฎาคม พ.ศ. 2555

Microsoft Excel Tips and Tricks: การตัดข้อความกรณีที่คำนำหน้านามติดกับชื่อ


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


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


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


  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


0 ความคิดเห็น:

แสดงความคิดเห็น