บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขี
ก่อนจะนำข้อมูลไปสรุปผลในบทต่อๆ ไปได้ คุณอาจจำเป็นต้องจัดการข้อมูล ให้ออกมาอยู่ในรูปแบบที่คุณต้องการก่อน เพื่อให้สามารถสรุปข้อมูลได้มีประสิทธิภาพมากขึ้น เช่น แยกชื่อและนามสกุลออกจากกัน หรือมีการแปลงคำบางอย่าง เช่น แปลงตัวพิมพ์เล็กพิมพ์ใหญ่
หนึ่งในฟังก์ชั่นที่มีบทบาทในการแปลงข้อมูลหรือจัดการข้อมูลให้เรียบร้อยมากขึ้นมากที่สุดตัวหนึ่งก็คือ ฟังก์ชั่นกลุ่ม TEXT นั่นเอง ซึ่งคุณจะต้องรู้จักฟังก์ชั่นพื้นฐานเหล่านี้ก่อนครับ คุณอาจต้องใช้มันผสมผสานกันพอสมควรเลยล่ะ
สรุปฟังก์ชั่นกลุ่ม Text ที่ใช้บ่อย
LEN
นับจำนวนตัวอักษร (นับทั้งอักษร ช่องว่าง สระ และวรรณยุกต์ด้วย)
=LEN(text)
=LEN(ข้อความที่จะนับอักษร)
ผลลัพธ์ : เป็น Number
ตัวอย่าง : =LEN(“มดแดง”) =5
LEFT
ตัดคำจากทางซ้าย (ระบุได้ว่าตัดกี่ตัวอักษร)
=LEFT(text,[num_chars])
=LEFT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด])
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =LEFT(“มดแดง”,2) =“มด”
MID
ตัดคำโดยระบุได้ว่าเริ่มตัดที่ตำแหน่งไหน เป็นจำนวนกี่ตัวอักษร
=MID(text,start_num,num_chars)
=MID(ข้อความต้นฉบับ,อักษรที่เริ่มตัด,จำนวนอักษรที่จะตัด)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =MID(“มดแดง”,3,2) =“แด”
RIGHT
ตัดคำจากทางขวา (ระบุได้ว่าตัดกี่ตัวอักษร)
=RIGHT(text, [num_chars])
=RIGHT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด])
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =RIGHT(“มดแดง”,2) =“ดง”
TRIM
ตัดช่องว่างส่วนเกินออก (ลบช่องว่างที่อยู่หน้า หลัง รวมถึงช่องว่างตรงกลางที่เกิน 1 เคาะ)
=TRIM(text)
=TRIM(ข้อความที่จะตัด)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =TRIM(” inw excel “) =”inw excel” (เหลือช่องว่างกลาง 1 space)
FIND
ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด (สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่
=FIND(find_text, within_text, [start_num])
=FIND(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา])
ผลลัพธ์ : เป็น Number
ตัวอย่าง : =FIND(“Excel”,”inwexcel is Excellent”) =13
SEARCH
ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด โดยใช้การหาแบบ Wildcard (ไม่สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่
=SEARCH(find_text,within_text,[start_num])
=SEARCH(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา])
ผลลัพธ์ : เป็น Number
ตัวอย่าง : =SEARCH(“Excel”,”inwexcel is Excellent”) =4
SUBSTITUTE
แทนที่คำที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้คำที่จะถูกแทนที่
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=SUBSTITUTE(คำที่ต้องการแทนที่, ส่วนของคำเดิมที่จะถูกแทนที่, ส่วนของคำใหม่ที่จะมาแทนที่, [เจาะจงอันที่ถูกแทนที่ได้])
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =SUBSTITUTE(“ผม like มาก”,”like”,”ชอบ”) = “ผม ชอบ มาก”
REPLACE
แทนที่ตำแหน่งที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้ตำแหน่งและจำนวนตัวอักษรที่จะถูกแทนที่
=REPLACE(old_text, start_num, num_chars, new_text)
=REPLACE(คำที่ต้องการแทนที่, ตำแหน่งเริ่มที่จะแทนที่, จำนวนอักษรที่จะแทนที่, คำใหม่ที่จะมาแทนที่)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =REPLACE(“081-234-5678”,5,3, “ไม่บอก”) = “081-ไม่บอก-5678”
TEXT
เปลี่ยนรูปแบบ Number Format โดยใช้สูตร
=TEXT(value, format_text)
ในช่อง format_text ให้ใส่รูปแบบคล้ายการทำ Custom Format ซึ่งจะอธิบายต่อไปในบทหลัง
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =TEXT(1234.5678,”0.00″)= “1234.57”
UPPER
แปลงข้อความให้กลายเป็นตัวพิมพ์ใหญ่ทั้งหมด
= UPPER(text)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =UPPER(“mOnKey”)= “MONKEY”
LOWER
แปลงข้อความให้กลายเป็นตัวพิมพ์เล็กทั้งหมด
= LOWER(text)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =LOWER(“mOnKey”)= “monkey”
PROPER
แปลงข้อความแต่ละคำให้ขึ้นต้นด้วยตัวพิมพ์ใหญ่ นอกนั้นตัวพิมพ์เล็กทั้งหมด
= PROPER(text)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =PROPER(“mOnKey”)= “Monkey”
ตัวอย่างการประยุกต์
ตัวอย่าง1 : แยกชื่อและนามสกุล
เรามาลองดูกันว่าถ้า A1 มีชื่อคนเขียนว่า “สมเทพ จริงนะ” อยู่ หากเราจะเอาคำว่า “สมเทพ” และ “จริงนะ” ออกมาไว้แต่ละช่องแยกกันด้วยวิธีการเขียนสูตร เราจะต้องเขียนสูตรว่าอย่างไร
วิธีคิด : เคล็ดลับคือให้คิดว่าถ้าเป็นเราเอง เรารู้ได้ยังไงว่าตรงไหนคือชื่อ ตรงไหนคือนามสกุล? ผมจะจำลองวิธีคิดในหัวสมองของเรา จากนั้นจะเทียบเป็นการเขียนฟังก์ชั่นใน Excel ให้ดูครับ
| ขั้นตอนการคิด | Function หลัก | วิธีการเขียนสูตร |
| รู้ว่าชื่อและนามสกุลแยกกันด้วยการเว้นวรรค | เครื่องหมาย เว้นวรรค |
เครื่องหมายเว้นวรรค แทนด้วย ” “ |
| ค้นหาเครื่องหมายเว้นวรรค | FIND | =FIND(” “,A1) จะได้ผลเป็นเลข 6 |
| เอาทุกอย่างที่อยู่หน้า (ทางซ้าย) ของเว้นวรรคไปใส่ในช่อง ชื่อ | LEFT | ใช้ LEFT ไปจนถึงตำแหน่งของการเว้นวรรค แต่ลบด้วย1 เพราะไม่ต้องการเครื่องหมายเว้นวรรคติดมาด้วย =LEFT(A1, FIND(” “,A1)-1) |
| เอาทุกอย่างที่อยู่หลัง (ทางขวา) ของเว้นวรรคไปใส่ในช่อง นามสกุล | RIGHTLEN | จะใช้ RIGHT แต่ต้องหาว่าต้องนับจากทางขวากี่ตัวอักษร วิธีการหา คือ เอาความยาวคำทั้งหมด ลบด้วยตำแหน่งเครื่องหมายเว้นวรรค =LEN(A1)-FIND(” “,A1)จากนั้นพอเอามาใส่ใน RIGHT จะได้ว่า =RIGHT(A1, LEN(A1)-FIND(” “,A1)) |
ถ้าถามว่าแล้วถ้ามีเว้นวรรค 2 ที เช่น มีคำนำหน้า ชื่อ และนามสกุล แบบนี้จะแยกออกมาเป็น 3 คอลัมน์ยังไง?
ถ้าให้ง่ายที่สุด ผมก็แนะนำให้ทำแบบนี้ 2 รอบครับ รอบแรกจะแยก space ตัวแรกสุดได้ เหลือเป็น นาย สมเทพ จริงนะ ส่วนรอบ 2 จะแยก space ตัวที่สองได้ เป็น นาย สมเทพ จริงนะ
ตัวอย่าง2 : การแยกคำที่ช่องว่างตัวที่สอง
ถ้า A1 มีชื่อคนเขียนว่า “สมเทพ จริงนะ เชื่อสิ” อยู่ หากเราจะเอาคำว่า “สมเทพ จริงนะ” และ “เชื่อสิ” ออกมาไว้แต่ละช่องแยกกันด้วยวิธีการเขียนสูตร เราจะต้องเขียนสูตรว่าอย่างไร
วิธีคิด : ในตัวอย่างนี้ จะใช้สูตร FIND หาช่องว่างตรงๆ ไม่ได้ เพราะ FIND จะเจอช่องว่างตัวแรกเสมอ ที่นี้ผมมีวิธีแก้ไข 2 วิธีมาแนะนำ คือ
วิธีที่ 1 :
ใช้ FIND เหมือนเดิม แต่ระบุ Argument =FIND(find_text,within_text,[start_num]) ให้ไปเริ่มหลังจาก Space ตัวแรกที่หาเจอ
- Step 1 : หา space ตัวแรกด้วย Find ตามปกติ จาก =FIND(” “,A1) จะได้ผลลัพธ์เป็นเลข 6 ทดไว้ที่ A2
- Step 2 :เอาค่าที่ได้จาก step 1 บวกเพิ่มไป 1 ตัวแล้วให้เป็น start_num ของ find อีกตัว จะได้ว่า =FIND(” “,A1,A2+1) จะได้ผลเป็นเลข 13 ซึ่งคือตำแหน่ง space ตัวที่สอง ทดไว้ที่ A3
- Step 3 :ใช้LEFTกับRIGHTตัดคำ คล้ายตัวอย่างที่แล้ว
- =LEFT(A1, A3 -1)
- =RIGHT(A1, LEN(A1)- A3)
วิธีที่ 2 :
ใช้ SUBSTITUTE แทนที่ Space ตัวที่สองด้วยอักขระแปลกๆ แล้วค่อยตัดคำจากอักขระนั้น
- Step 1 : ใช้ SUBSTITUTE แทนที่ Space ตัวที่สอง โดยการระบุ Argument
=SUBSTITUTE(text,old_text,new_text,[instance_num]) เป็น 2
จะได้ว่า =SUBSTITUTE(A1,” “,”|”,2) จะได้ผลลัพธ์เป็น สมเทพ จริงนะ|เชื่อสิ ทดไว้ที่ A2 - Step 2 : หาคำแหน่งของอักขระ | ด้วย FIND
ได้ว่า =FIND(“|”,A2) จะได้ผลลัพธ์เป็นตำแหน่งที่ 13 ทดไว้ที่ A3 - Step 3 : จากนั้นก็ใช้ LEFT กับ RIGHT ตัดคำเหมือนวิธีที่แล้ว
ตัวอย่าง3 : การหาว่ามีอักขระที่สนใจอยู่กี่ตัวในคำที่ต้องการ
หากผมต้องการหาว่าในคำที่ผมต้องการ เช่น baseball มีตัว b อยู่กี่ตัว? ผมจะเขียนสูตรยังไงดี
แนวคิด คือ ให้นับจำนวนตัวอักษรทั้งหมดไว้ก่อน จากนั้นให้แทนที่ b ด้วย blank แล้วนับจำนวนอักษรอีกที แล้วค่อยมาลบกัน
สมมติ คำที่ผมสนใจอยู่ใน A1
- Step 1 : นับจำนวนตัวอักษรทั้งหมด
=LEN(A1) - Step 2 : แทนที่ b ด้วย blank
จะได้ว่า =SUBSTITUTE(A1,”b”,””) จะได้ผลลัพธ์เป็น aseall ผมเก็บไว้ใน A3 - Step 3 : นับจำนวนตัวอักษรทั้งหมด หลังจากตัด b ทิ้งแล้ว
=LEN(A3) - Step 4 : หาผลต่างของจำนวนอักษร
=LEN(A1)-LEN(A3) นั่นเอง
