Excel

รับนามสกุลจากชื่อ

Get Last Name From Name

สูตร Excel: รับนามสกุลจากชื่อสูตรทั่วไป |_+_| สรุป

หากคุณต้องการแยกนามสกุลออกจากชื่อเต็ม คุณสามารถทำได้ด้วยสูตรที่ค่อนข้างซับซ้อนนี้ซึ่งใช้หลายฟังก์ชัน ในรูปแบบทั่วไปของสูตร (ด้านบน) ชื่อคือชื่อเต็ม โดยมีช่องว่างคั่นชื่อจากส่วนอื่นๆ ของชื่อ





ในตัวอย่าง เซลล์ที่ใช้งานอยู่มีสูตรนี้:

วิธีย่อใน excel
= RIGHT (name, LEN (name)- FIND ('*', SUBSTITUTE (name,' ','*', LEN (name)- LEN ( SUBSTITUTE (name,' ','')))))
คำอธิบาย

ที่แกนกลาง สูตรนี้ใช้ฟังก์ชัน RIGHT เพื่อแยกอักขระที่เริ่มต้นจากด้านขวา ฟังก์ชันอื่นๆ ที่ประกอบเป็นส่วนที่ซับซ้อนของสูตรนี้ทำสิ่งเดียวเท่านั้น: ฟังก์ชันนี้จะคำนวณว่าต้องแยกอักขระกี่ตัว





ในระดับสูง สูตรจะแทนที่ช่องว่างสุดท้ายในชื่อด้วยเครื่องหมายดอกจัน '*' จากนั้นใช้ FIND เพื่อกำหนดตำแหน่งของดอกจันในชื่อ ตำแหน่งนี้ใช้คำนวณจำนวนอักขระที่จะแยกด้วย RIGHT

ฟังก์ชันจะแทนที่เฉพาะช่องว่างสุดท้ายอย่างไร นี่คือส่วนที่ฉลาด



หัวเข็มขัดคำอธิบายได้รับบิตเทคนิค

กุญแจสำคัญของสูตรนี้คือบิตนี้:

 
= RIGHT (B4, LEN (B4)- FIND ('*', SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))))

ซึ่งแทนที่จริงของช่องว่างสุดท้ายด้วย '*'

SUBSTITUTE มีอาร์กิวเมนต์ที่สี่ (เป็นทางเลือก) ซึ่งระบุว่าควรแทนที่ 'อินสแตนซ์' ของข้อความค้นหาใด หากไม่มีการระบุสิ่งใดสำหรับอาร์กิวเมนต์นี้ อินสแตนซ์ทั้งหมดจะถูกแทนที่ อย่างไรก็ตาม ถ้าสมมติว่ามีหมายเลข 2 มา ระบบจะแทนที่เฉพาะอินสแตนซ์ที่สองเท่านั้น ในตัวอย่างด้านบน อินสแตนซ์คำนวณโดยใช้ SUBSTITUTE ที่สอง:

 
 SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))

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

ในชื่อตัวอย่างใน B4 มีช่องว่างสองช่องในชื่อ ดังนั้นเราจึงได้:

15 - 13 = 2

และใช้สองตัวในหมายเลขอินสแตนซ์:

 
 LEN (B4)- LEN ( SUBSTITUTE (B4,' ',''))

ซึ่งแทนที่ช่องว่างที่สองด้วย '*' ชื่อจะมีลักษณะดังนี้:

'ซูซาน แอน*ช้าง'

ฟังก์ชัน FIND จะเข้ามาแทนที่ '*' ในชื่อ:

 
 SUBSTITUTE (B4,' ','*',2)

ผลลัพธ์คือ 10 (* อยู่ในตำแหน่งที่ 10) ซึ่งถูกลบออกจากความยาวทั้งหมดของชื่อ:

 
 FIND ('*', 'Susan Ann*Chang')

เนื่องจากชื่อมีอักขระ 15 ตัว เราจึงมี:

15-10 = 5

RIGHT ใช้หมายเลข 5 ดังนี้:

 
 LEN (B4)-10

ซึ่งส่งผลให้ 'ช้าง'

วิธีเชื่อมต่อใน excel

อย่างที่คุณเห็น การคำนวณ 5 แบบง่ายๆ ข้างต้นเป็นงานหนักมาก!

การจัดการพื้นที่ที่ไม่สอดคล้องกัน

การเว้นวรรคเกินจะทำให้เกิดปัญหากับสูตรนี้ ทางออกหนึ่งคือการใช้ ฟังก์ชัน TRIM ขั้นแรกให้ทำความสะอาดสิ่งต่าง ๆ แล้วใช้สูตรการแยกวิเคราะห์

ผู้เขียน Dave Bruns


^