Show
ก่อนจะนำข้อมูลไปสรุปผลในบทต่อๆ ไปได้ คุณอาจจำเป็นต้องจัดการข้อมูล ให้ออกมาอยู่ในรูปแบบที่คุณต้องการก่อน เพื่อให้สามารถสรุปข้อมูลได้มีประสิทธิภาพมากขึ้น เช่น แยกชื่อและนามสกุลออกจากกัน หรือมีการแปลงคำบางอย่าง เช่น แปลงตัวพิมพ์เล็กพิมพ์ใหญ่ หนึ่งในฟังก์ชั่นที่มีบทบาทในการแปลงข้อมูลหรือจัดการข้อมูลให้เรียบร้อยมากขึ้นมากที่สุดตัวหนึ่งก็คือ ฟังก์ชั่นกลุ่ม TEXT นั่นเอง ซึ่งคุณจะต้องรู้จักฟังก์ชั่นพื้นฐานเหล่านี้ก่อนครับ คุณอาจต้องใช้มันผสมผสานกันพอสมควรเลยล่ะ สรุปฟังก์ชั่นกลุ่ม Text ที่ใช้บ่อยLENนับจำนวนตัวอักษร (นับทั้งอักษร ช่องว่าง สระ และวรรณยุกต์ด้วย)  =LEN(text) LEFTตัดคำจากทางซ้าย (ระบุได้ว่าตัดกี่ตัวอักษร) =LEFT(text,[num_chars]) MIDตัดคำโดยระบุได้ว่าเริ่มตัดที่ตำแหน่งไหน เป็นจำนวนกี่ตัวอักษร =MID(text,start_num,num_chars) 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 ตัวแรกที่หาเจอ
วิธีที่ 2 :ใช้ SUBSTITUTE แทนที่ Space ตัวที่สองด้วยอักขระแปลกๆ แล้วค่อยตัดคำจากอักขระนั้น
ตัวอย่าง3 : การหาว่ามีอักขระที่สนใจอยู่กี่ตัวในคำที่ต้องการหากผมต้องการหาว่าในคำที่ผมต้องการ เช่น baseball มีตัว b อยู่กี่ตัว? ผมจะเขียนสูตรยังไงดี แนวคิด คือ ให้นับจำนวนตัวอักษรทั้งหมดไว้ก่อน จากนั้นให้แทนที่ b ด้วย blank แล้วนับจำนวนอักษรอีกที แล้วค่อยมาลบกัน |
สูตร excel ค้นหา ข้อความที่ต้องการ
กระทู้ที่เกี่ยวข้อง
ลิขสิทธิ์ © 2024 th.apacode Inc.
