·
การแยกชื่อ-นามสกุล ออกเป็น 2 คอลัมน์
·
การแยก ตำบล, อำเภอ, จังหวัด
ให้อยู่คนละคอลัมน์
ในกรณีที่ข้อความเดิมมีสิ่งที่บ่งบอกในการแบ่งคอลัมน์ชัดเจน เรียกว่าตัวคั่น ใช้ใน Excel
เช่น ช่องว่าง(Space) ใช้ 8Tab หรือใช้จุลภาค (,) เป็นต้น ข้อมูลที่พบบ่อยได้แก่ ทะเบียนประวัติพนักงาน
ชื่อ-นามสกุล ที่อยู่ ถนน ตำบล อำเภอ จังหวัด เป็นต้น
จากตัวอย่างข้อมูลด้านบน
จะเห็นว่าในแต่ละแถวของคอลัมน์ A จะมีทั้งคำนำหน้า ชื่อและนามสกุล
อยู่ในเซลล์เดียวกัน โดยมีช่องว่าง (Space) คั่นอยู่ จึงสามารถใช้วิธี Text to Column เพื่อใช้แยกคำนำหน้า ชื่อ และ นามสกุล ออกเป็นคนละคอลัมน์ได้ ดังนี้
1)
เลือกช่วงข้อมูลที่ต้องการจะแยกข้อความ
1)
คลิกที่เมนู Data (ข้อมูล) เลือก Text
to columns.. (ข้อความเป็นคอลัมน์..)
2)
จะปรากฎหน้าต่าง Text to Columns
Wizard Step 1 of 3 ขึ้นมา ในที่นี้ให้เลือกตัวเลือกเป็น Delimited
(ตัวคั่น) แล้วคลิก Next (ถัดไป)
1)
เลือกลักษณะของตัวคั่น (Delimiters) ซึ่งในตัวอย่างนี้เราจะเลือก Space หรือช่องว่าง
ส่วนในกรอบด้านล่างก็จะแสดงตัวอย่างการแยกคอลัมน์ให้เห็น จากนั้นคลิกปุ่ม Next
(ถัดไป)
1)
หากกดปุ่ม Finish (เสร็จสิ้น)
ทันที ตำแหน่งแรกของผลลัพธ์ที่ได้ จะทับลงในเซลล์เดิม
แต่ถ้าไม่ต้องการให้ทับข้อมูลในเซลล์เดิม ก็สามารถกำหนดตำแหน่งแรกของผลลัพธ์ได้
โดยใส่ตำแหน่งเซลล์ที่ต้องการในช่อง Destination (เป้าหมาย)
แล้วจึงกดปุ่ม Finish ก็จะได้ผลลัพธ์ดังรูปด้านล่าง
กรณีที่ข้อมูลเดิมไม่มีตัวคั่น
เราสามารถใช้สูตรและฟังก์ชั่นในการแยกคอลัมน์
ดังนี้
1)
การใช้ฟังก์ชั่น LEFT และ
IF เพื่อแสดงคำนำหน้าชื่อ
ฟังก์ชั่น
LEFT
เป็นฟังก์ชั่นที่ใช้ในการส่งกลับอักขระตัวแรกที่อยู่ซ้ายสุดของข้อความ
หรือส่งกลับกลุ่มอักขระที่นับจากซ้ายสุดของข้อความตามจำนวนที่ระบุ
รูปแบบฟังก์ชั่น
LEFT (text,num_chars)
Text ข้อความ
หรืออ้างอิงไปยังเซลที่มีข้อความที่ต้องการให้มีการส่งกลับ
อักขระตัวแรกหรือกลุ่มอักขระ
Num_chars ระบุจำนวนอักขระที่ต้องการให้ส่งกลับจากข้อความใน
text
ฟังก์ชั่น IF
Function IF () จะใช้สำหรับทดสอบเงื่อนไขและตัดสินใจในการทำงาน ตามเงื่อนไขที่เรากำหนดลงไป
ซึ่งจะใช้กับข้อมูลที่มีทางเลือกในการตัดสินใจหลายทาง
โดยโปรแกรมจะนำเงื่อนไขไปทดสอบแล้วส่งค่ากลับเป็นตรรกศาสตร์ว่าจริง (True) หรือเท็จ (False) แล้วถ้าจริงให้ทำอะไรและถ้าเท็จจะให้ทำอะไร
รูปแบบฟังก์ชั่น
=IF(logical_test,[value_if_true],[value_if_falsel)
logical_test หมายถึง เงื่อนไขที่จะใช้ทดสอบ
value_if_true หมายถึง ถ้าเงื่อนไขเป็นจริงให้ทำอะไร อาจจะเป็น
สูตร ตัวเลข หรือ “ข้อความ” ใดๆ
value_if_false หมายถึง ถ้าเงื่อนไขเป็นเท็จให้ทำอะไร
อาจจะเป็น สูตร ตัวเลข หรือ “ข้อความ”
ใดๆ
**
ฟังก์ชั่น IF
จะใช้ได้สูงสุดไม่เกิน 7 ชั้น ต่อ 1 สูตร
**
เมื่อเปิดฟังก์ชั่น IF
จะตามด้วยเครื่องหมาย ( วงเล็บเปิดเสมอ ดังนั้นก็ต้องปิดด้วย )
วงเล็บปิด ให้ครบตามจำนวน IF ที่เปิดด้วย
**
การพิมพ์สูตร IF
ต้องพิมพ์ยาวต่อเนื่องติดกันในครั้งเดียวในจนกว่าจะจบสูตรแล้วจึงกด
Enter
หากเราใช้ฟังก์ชั่น LEFT เราจะไม่สามารถคัดลอกสูตรไปใช้ได้ทั้งแถว เนื่องจากคำนำหน้าของแต่ละคนจะไม่เหมือนกัน
เช่น นาย นางสาว น.ส. นาง
จึงต้องทำการพิมพ์สูตรใหม่ในแต่ละคอลัมน์ซึ่งทำให้ยุ่งยากและเสียเวลา
เราจึงใช้ฟังก์ชั่น
IF รวมกับคำสั่ง LEFTเพื่อตรวจสอบคำนำหน้าชื่อ แล้วจึงแสดงผล
โดยกำหนดเงื่อนไขจากจำนวนตัวอักษรของคำนำหน้า
นางสาว = 6 ตัวอักษร
น.ส. = 4
ตัวอักษร
นาย นาง = 3
ตัวอักษร
ก็จะสามารถกำหนดเงื่อนไขได้ดังนี้
“ถ้าตัวอักษรแรกของชื่อตรงกับคำว่า “นางสาว” ให้ LEFT แสดงแค่ 6 ตัวอักษรแรก
แต่ถ้าไม่เป็นตามเงื่อนไขแรก ก็ทำการตรวจสอบอีกว่า ถ้า 4 ตัวอักษรแรกตรงกับคำว่า “น.ส.” หรือไม่ ถ้าใช่ ก็ให้ LEFT แสดงแค่ 4 ตัวอักษรแรก หากไม่ตรงตามเงื่อนไขทั้งสองที่ระบุ ก็ให้ LEFT
แสดงแค่ 3 ตัวอักษรแรกเท่านั้น” เขียนสูตรได้ดังนี้
=IF(LEFT(A2,6)=
“นางสาว”,LEFT(A2,6),IF(LEFT(A2,4)= “น.ส”,LEFT(A2,4),LEFT(A2,3) ) )
1)
การแยกชื่อด้วยฟังก์ชั่น MID, LEN และ FIND
ฟังก์ชั่น MID
เป็นฟังก์ชั่นที่ใช้ส่งกลับกลุ่มอักขระที่ต้องการแยกออกจากข้อความตามตำแหน่งและจำนวนที่ระบุ
รูปแบบฟังก์ชั่น
MID(text,start_num,num_chars)
Text ข้อความในเครื่องหมาย “ ”
หรืออ้างอิงไปยังเซลที่มีความความที่ต้องการให้มีการส่งกลับอักขระตัวแรกหรือกลุ่มอักขระ
Start_num ตัวเลขระบุตำแหน่งของอักขระตัวแรกที่ต้องการแยกออกจากข้อความโดยกำหนดให้
1 หมายถึงอักขระตัวแรก
Num_chars จำนวนอักขระที่ต้องการแยกออกจากข้อความ
ฟังก์ชั่น LEN
เป็นฟังก์ชั่นส่งกลับจำนวนอักขระทั้งหมดในข้อความนั้น
โดยอักขระจะรวมถึง ตัวเลข ตัวอักษร (สระ และวรรณยุกต์ ถ้าเป็นภาษาไทย)
สัญลักษณ์ต่างๆ และช่องว่าง
รูปแบบฟังก์ชั่น
LEN(text)
Text ข้อความ
หรืออ้างอิงไปยังเซลที่มีข้อความที่ต้องการนับจำนวนอักขระทั้งหมด
ฟังก์ชั่น FIND
เป็นฟังก์ชั่นที่ใช้ค้นหาข้อความที่ต้องการจากข้อความอื่นๆ ที่ระบุ
(อักขระต้องตรงกันทั้งตัวพิมพ์ใหญ่และตัวพิมพ์เล็ก)
แล้วส่งกลับค่าตัวเลขที่แสดงตำแหน่งของอักขระตัวแรกของข้อความที่ค้นพบ
ตัวอย่างเช่น ให้ค้นหาข้อความ “First Sample” จากข้อความ “My
First Sample” ฟังก์ชั่น FIND จะส่งกลับค่าตัวเลข
4 ซึ่งเป็นตำแหน่งของตัว “F” ถ้าฟังก์ชั่น FIND ไม่พบข้อความที่ต้องการ จะแสดงข้อผิดพลาด #VALUE!
รูปแบบฟังก์ชั่น
FIND(find_text,within_text,start_num)
Find_text ข้อความที่ต้องการค้นหา
Within_text ข้อความที่มีข้อความที่ต้องการค้นหา
Start_num ตัวเลขระบุตำแหน่งอักขรุใน
within_text ที่ต้องการให้เริ่มค้นหา โดยกำหนดให้ลำดับที่ 1
หมายถึงอักขระตัวแรกของ within_text
ลำดับที่ 2
หมายถึงอักขระตัวที่สองของ within_text และอื่นๆ ตามลำดับ โดยนับรวมช่องว่างด้วย
ถ้าไม่ระบุ start_num ไว้จะหมายถึงลำดับที่ 1
ถ้า start_num เป็นค่าติดลบหรือมีค่ามากกว่าจำนวนอักขระใน
within_text จะแสดงข้อผิดพลาด #VALUE!
จากตัวอย่างสามารถใส่สูตรได้ดังนี้
=MID(A2,LEN(B2)+1,FIND(" ",A2,1)-1-LEN(B2))
1)
การแยกนามสกุลโดยการใช้สูตร TRIM ,RIGHT, LEN,
FIND
ฟังก์ชั่น TRIM
เป็นฟังก์ชั่นที่ใช้ตัดช่องว่างออกจากข้อความ
(ยกเว้นช่องว่างระหว่างคำ)
สามารถนำมาใช้ตัดช่องว่างที่มากเกินปกติออกไปจากข้อความได้ เช่น
ข้อความที่นำเข้ามาจากโปรแกรมประยุกต์อื่นๆ ที่มีการเว้นวรรคระหว่างคำมากเกินไป
เป็นต้น
รูปแบบฟังก์ชั่น
TRIM(text)
Text ข้อความ
หรืออ้างอิงไปยังเซลที่มีข้อความที่ต้องการนำมาตัดช่องว่างที่เกินปกติ
เขียนสูตรได้ดังนี้ =TRIM(RIGHT(A2,LEN(A2)-FIND(" ",A2,1)))
<Key Word สำหรับค้นหาบทความนี้> เรียน Excel, เรียนเขียนสูตร, การใช้ Pivot Table,ฟังก์ชั่น if(), SumIF(), CountIF(), VLookUp(), สอน Excel สำหรับ HR
