การใช้งาน excel : การจัดการ Text Value

Text Value เป็นรูปแบบที่ท่านผู้อ่าน how to excel ต่างประสบปัญหากันมาบ้างไม่มากก็น้อย ตัวผมเองก็ประสบมาด้วยตัวเองก็พอสมควรดังได้เคยนำมาเล่าในหัวข้อการค้นหาข้อมูลด้วย VLOOKUP ไปแล้วครับ วันนี้ก็เลยมีความตั้งใจที่จะเสนอแนะลักษณะงานที่เกี่ยวกับ Text value ในการใช้งาน excel เนื่องด้วยบทความนี้เป็นบทความที่ 50 พอดี ย้อนกลับไปก็ให้ฉงนเหมือนกันว่าผมเขียนไปบทความไปเกือบครึ่งร้อยซะแล้ว วันนี้เลยขอนำเสนอบทความที่สบายๆเป็นการผ่อนคลายไปในตัวหล่ะกันนะครับ

วันนี้ผมอยากนำเสนอฟังก์ชัน excel ที่ใช้ในการจัดการข้อมูลประเภท Text Value ซัก 3 ฟังก์ชัน เนื่องด้วยอาทิตย์ที่ผ่านมามีโอกาสได้ใช้จัดการวางแผนการผลิต เรามาเริ่มสูตร Excel สูตรแรกกันเลยครับ

1. UPPER / LOWER เป็น excel function ที่ใช้ในการแปลงอักษรทั้งหมดในข้อความให้เป็นตัวพิมพ์ใหญ่(UPPER) หรือเป็นตัวพิมพ์เล็ก(LOWER) ท่านผู้อ่านใช้ excel function ในด้านใดบ้างครับ ผมเองจะใช้ในการตรวจสอบเงื่อนไขครับ เพื่อป้องกันผู้ใช้พิมพ์ตัวพิมพ์เล็กหรือพิมพ์ใหญ่ทำให้ง่ายต่อการตรวจสอบครับ โดยเรานำข้อความมาทำการแปลงก่อนนำไปเปรียบเทียบเงื่อนไขครับ ตัวอย่างเช่น หากตัวอักษร 2 ตัวแรกของข้อความที่นำเข้ามาเป็นตัวอักษร “EZ” ให้พิมพ์คำว่า yes หากมิใช่ให้พิมพ์ no ท่านผู้อ่านจะเห็นว่าความเสี่ยงที่ผู้ใช้จะพิมพ์ตัวอักษร 2 ตัวแรกในรูปแบบที่แตกต่างกันมีความเป็นไปได้ เช่น Ez , ez , eZ , EZ ดังนั้นเพื่อให้การตรวจสอบความถูกต้องสามารถทำได้ง่าย เราอาจเขียนเป็นสูตร excel ได้ดังนี้ B2 =if(Left(UPPER(A2),2) = “EZ” ,”EZ” , “NO”) จากตัวอย่างที่ผมได้นำเสนอสามารถแสดงได้ดังภาพที่ 1

ภาพที่ 1 ตัวอย่างการใช้งาน excel function ในการตรวจสอบเงื่อนไข

excel function : UPPER / LOWER ในการเขียน VBA excel จะใช้ฟังก์ชัน UCase / LCase

ท่านผู้อ่านจะเห็นว่าผมใช้ฟังก์ชัน Left เพื่อตัดเอาตัวอักษร 2 ตัวแรกด้านซ้ายมาแปลงเป็นตัวพิมพ์ใหญ่และตรวจสอบว่าเป็นตัวอักษร “EZ” หากใช่ก็กำหนดให้เท่ากับ EZ ในหลัก B หากไม่ใช่จะกำหนดให้เท่ากับ NO ในหลัก B

2. การประยุกต์ฟังก์ชัน Find และฟังก์ชัน Left Mid ในการแยกชื่อและนามสกุลออกจากกัน ในบางครั้งเราอาจมีความประสงค์ที่จะแยกชื่อและนามสกุลออกจากันเพื่อนำไปใช้งานอื่นๆ มาดูแนวคิดการแยกชื่อและนามสกุลกันเลยครับ โดยปกติชื่อและนามสกุลจะถูกคั่นด้วยช่องว่างดังนั้นขั้นตอนแรกต้องเราต้องหาตำแหน่งของช่องว่างในชื่อ-นามสกุล จากนั้นใช้ฟังก์ชั่น Left เพื่อแยกชื่อมาแสดง จากนั้นเราจะใช้ฟังก์ชัน Mid ในการแยกนามสกุล ดังแสดงเป็นสูตร Excel ในภาพที่ 2

ภาพที่ 2 การแยกชื่อนามสกุลออกจากกัน

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

การใช้งาน Excel ตอน การค้นหาข้อความด้วย Excel Text Function

สวัสดีครับท่านผู้อ่าน How to excel ทุกท่านครับ วันนี้ขออนุญาตหยิบปัญหาที่ผมได้รับจากลูกค้าภายในที่ทำงาน เกี่ยวกับการค้นหาข้อความและแยกข้อความที่ต้องการเก็บไว้ในเซลล์ต่างหาก ใน Excel ด้วยเหตุที่ว่าข้อมูลที่มีอยู่ในปัจจุบันมีเยอะมาก การบันทึกข้อมูลดังกล่าวคงไม่สามารถทำได้โดยง่ายนักใน Excel วันนี้จึงขอนำเสนอ Excel text function ที่นิยมช่วยในการค้นหาข้อความและสำเนาข้อความที่ค้นเจอ ดังนี้ครับ

1.สูตร Excel text : Left เป็นสูตร Excel ที่ใช้สำเนาข้อความต้นฉบับ (text) โดยเริ่มสำเนาจากด้านซ้ายไปยังด้านขวาตามจำนวนตัวอักษรที่กำหนด มีรูปแบบดังนี้

LEFT(text,[num_chars])

โดย text คือ ข้อความต้นฉบับ

num_chars คือจำนวนตัวอักษรที่ต้องการสำเนา

ตัวอย่างเช่น สมมุติข้อความในเซลล์ B2 เท่ากับ LOVE หากเราใช้ สูตร Excel =LEFT(B2,2) สูตร LEFT จะสำเนาข้อความกลับมาให้เป็น LO เป็นต้น

2.สูตร Excel text : FIND เป็นสูตร Excel ที่ใช้หาตำแหน่งของตัวอักษรหรืออักขระที่อยู่ภายในข้อความ มีรูปแบบดังนี้

FIND(find_text,within_text,[start_num])

โดย find_text คือ ตัวอักษรหรืออักขระที่ต้องการค้นหาตำแหน่ง

within_text คือข้อความที่ต้องการค้นหา

start_num คือตำแหน่งในข้อความที่เริ่มค้นหาโดยตำแหน่งที่ 1 จะเริ่มจากตัวอักษรแรกจากซ้าย

ตัวอย่างเช่น FIND(“/”,”12/13”,0) จะคืนค่า 3 กลับมา เป็นต้น

3. สูตร Excel text : MID เป็นสูตร Excel ที่ใช้สำเนาอักษรหรือตัวอักขระจากข้อความโดยกำหนดตำแหน่งเริ่มต้นในการสำเนาและจำนวนอักษรหรือตัวอักขระที่ต้องการสำเนา มีรูปแบบดังนี้

MID(text,start_num,[num_chars])

โดย text คือข้อความต้นฉบับที่ต้องการสำเนา

start_num คือ ตำแหน่งเริ่มต้นในการสำเนา

num_chars คือ จำนวนอักษรหรือตัวอักขระที่ต้องการสำเนา

ตัวอย่างเช่น MID(“12/03/53”,3,2) จะคืนอักษร “03” กลับมา เป็นต้น

จากที่ได้ยกตัวอย่างมาเรามาดูตัวอย่างกันดีกว่าครับ สมมุติเรามีข้อมูลชื่อบริษัทและที่ตั้งของบริษัทลูกค้าอยู่ในเซลล์เดียวกันดังแสดงในภาพที่ 1 หากเราต้องการแยกชื่อของบริษัทลูกค้าออกมาเก็บไว้ในหลักใหม่เราสามารถประยุกต์ใช้ Excel text function ได้ ท่านผู้อ่านจะสังเกตเห็นว่าชื่อบริษัทและที่ตั้งจะถูกแบ่งด้วยอักขระ “,” จากเงื่อนไขนี้ หากเราสามารถหาตำแหน่งของ “,” ได้เราก็จะสามารถสำเนาชื่อของบริษัทลูกค้าออกมาเก็บไว้ในหลักใหม่ได้ โดยมีระเบียบขั้นตอนการใช้สูตร Excel ดังนี้

1. ใช้ สูตร FIND หาตำแหน่งของ “,”

2. ใช้สูตร LEFT สำเนาชื่อบริษัทไปเก็บไว้ในหลักใหม่

เพื่อให้การใช้สูตร Excel เป็นไปด้วยความสะดวกจึงรวบรวมขั้นตอนการใช้สูตร Excel ทั้งสองขั้นตอนได้ดังนี้

C2 = LEFT(B2,FIND(“,”,B2,1)-1) ผลการใช้สูตร Excel แสดงได้ดังภาพที่ 2

ภาพที่ 1 ข้อมูลการทดสอบ Excel text function

ภาพที่ 2 ผลการใช้สูตร Excel : LEFT&FIND

หากต้องการสำเนาที่อยู่หลังอักขระ “,” เก็บไว้ในหลัก D สามารถเขียนสูตร Excel ได้ดังนี้

D2 = MID(B2,FIND(“,”,B2,1)+1,LEN(B2)) โดย LEN เป็นสูตร Excel ที่คืนค่าความยาวของข้อความ ผลการใช้สูตร Excel นี้แสดงได้ดังภาพที่ 3

ภาพที่ 3 ผลการใช้สูตร Excel : MID , FIND , LEN

หวังว่าท่านผู้อ่านบทความนี้คงได้ไอเดียในการประยุกต์ใช้สำหรับการสำเนาข้อความในไฟล์ Excel นะครับ ส่วนตัวผมเองนอกจากจะตอบโจทย์ลูกค้าภายในบริษัทได้แล้ว ท่านผู้อ่านคงจะได้เห็นผมประยุกต์ใช้ สูตร Excel นี้ในการประเมินผลด้วย KPI ต่อไปครับ ราตรีสวัสดิ์ครับ

VLOOKUP in Excel กับการค้นหาข้อมูลชนิด Text Format

เมื่ออาทิตย์ที่ผ่านมาสุดที่รักให้การบ้านมาหนึ่งชิ้นเกี่ยวกับการใช้ excel ค้นหาข้อมูลสินค้าตามรหัสและเมื่อหาเจอแล้วให้นำข้อมูลของปริมาณของสินค้าต่อกล่องไปคำนวณหาจำนวนกล่องซึ่งใช้บรรจุสินค้าใน Work Sheet ถัดไป เป็นไงครับเห็นโจทย์แล้วหลายท่านบอกว่าการใช้ excel แบบหมูๆ Search ด้วย VLOOKUP in Excel ซิ ผมก็คิดเช่นนั้นครับ ลองมาดูปัญหากัน ผมขอยกตารางมาให้ทุกท่านได้เข้าใจตรงกันดีกว่าครับ

สมมุติว่าผมมีตารางข้อมูลใน Work Sheet ที่ชื่อ Master Product ดังแสดงในตารางที่ 1 ครับ

ตารางที่ 1 ตาราง Master Product

ในแต่ละวันแฟนผมจะได้รับออร์เดอร์จากลูกค้าในการสั่งซื้อสินค้าแต่ละชนิดโดยข้อมูลการสั่งซื้อจะถูกบันทึกไว้ในไฟล์ Excel ที่ Work Sheet ชื่อ Order
ตัวอย่างข้อมูลดังแสดงในตารางที่ 2

ตารางที่ 2 ตาราง Order

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

ขั้นแรกเช็คหน่วยในหลัก D กันก่อนโดยใช้เงื่อนไข สูตร excel : OR ครับ สมมุติผมเริ่มเขียนในแถวที่สองหล่ะกัน เขียน สูตร excel ได้ดังนี้ OR(D2="ขด" , D2="ถง") เอาหล่ะคับ สูตร excel : OR จะคืนค่าจริง หากหน่วยใน D2 เป็น ขด หรือ ถง อย่างใดอย่างหนึ่ง และจะคืนค่าเท็จหากเป็นหน่วยอื่นๆ
จากนั้นเราจะเอาค่าที่คืนจาก สูตร excel : OR ไปเป็นเงื่อนไขใน สูตร excel : IF ครับโดยสามารถเขียนได้ดังนี้

IF(OR(D2="ขด" , D2="ถง") ,หากจริง, หากเท็จ)

มาดูต่อครับ หากเงื่อนไข สูตร excel : OR เป็นจริง แฟนผมบอกให้เอาค่าในหลักออเดอร์มาวางได้เลย ดังนั้นจะเขียน สูตร excel : ได้ต่อดังนี้

IF(OR(D2="ขด" , D2="ถง") ,C2, หากเท็จ)

ทีนี้หากเงื่อนไข สูตร excel : OR เป็นเท็จ เราก็ต้องหารจำนวนออเดอร์ของสินค้านั้นด้วยปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น สามารถเขียน สูตร excel : ได้ต่อดังนี้

IF(OR(D2="ขด" , D2="ถง") ,C2, C2/ปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น)

ปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น เราสามารถหาได้จากข้อมูลในตาราง Master Product โดยค้นหาจากรหัสสินค้า ด้วยฟังก์ชัน VLOOKUP ซึ่งผมได้อธิบายไปแล้วในบทความที่ผ่านมา ขอยกมาใช้เลยครับ

ปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น = VLOOKUP(A2,'Master Product'!$A$2:$C$4,3,0)
นำไปแทนในสูตรโดยรวมจะได้สูตรในหลัก E เพื่อหาจำนวนกล่องที่ต้องบรรจุสินค้าแต่ละชนิดดังนี้

IF(OR(D2="ขด" , D2="ถง") ,C2, C2/VLOOKUP(A2,'Master Product'!$A$2:$C$4,3,0))

เอาหล่ะครับ คำนวณได้เลย ผลการคำนวณแสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการคำนวณโดยใช้ excel

เป็นไงครับผลการคำนวณเป็นไปตามเงื่อนไขที่เราสั่งมั้ยครับ จะเห็นว่า excel ก็ทำงานตามคำสั่งนะครับ แล้วทำไม สองแถวแรกถึงแจ้งว่า #N/A ท่านที่ใช้ VLOOKUP อยู่บ้างแล้วคงแปลความหมายออกครับ มันบอกว่า ค้นหารหัสสินค้าที่ต้องการไม่พบครับ ตอนแรกผมงงมากบอกแฟนไปว่ารหัสสินค้าใน Master Product คงมีไม่ครบแน่ๆ มันถึงหาไม่เจอ แต่ก็ได้รับการยืนยันแบบจริงจังว่า มีครบทุกรหัส เอาแล้วไงครับ งานเข้า ทำไงดี
ก่อนแก้ปัญหาผมจึงสอบถามที่มาของข้อมูลใน Master Product เธอบอกว่านำเข้าจากโปรแกรมสำเร็จรูปตัวหนึ่ง เอาหล่ะครับเริ่มเห็นทางสว่าง จึงลองใช้ สูตร excel ที่จัดการด้าน Text มาช่วยตรวจสอบดูครับ อันดับแรก ผมลองเช็คความยาวของ รหัสสินค้า ด้วย สูตร excel : LEN(Text) ซึงจะคืนค่าความยาวของ Text ในเซลล์นั้นครับ ปรากฎว่าโชคดีมากครับ เพราะความยาวที่ สูตร excel : LEN คืนมาเท่ากับ 9 แต่หากนับตัวอักษรที่เห็นในรหัสสินค้ามันมี 7 หลัก นั่นไงครับเจอสาเหตุที่ VLOOKUP หารหัสสินค้าไม่เจอแล้ว สาเหตุคือ ข้อความของรหัสสินค้าที่นำเข้ามานอกจากมีตัวอักษร 7 ตัวแล้วยังมีช่องว่างแถมมาต่อท้ายด้วย 2 ช่อง ทำให้เมื่อนำรหัสที่ต้องการค้นหาจากตารางออเดอร์ซึ่งไม่มีช่องว่างเลย VLOOKUP เลยหาข้อมูลไม่เจอ วิธีแก้ของผมคือใช้ สูตร excel : LEFT เพื่อเอาเฉพาะข้อความที่เป็นตัวอักขระที่มองเห็นเท่านั้น โดยในที่นี้ผมทราบว่ารหัสสินค้าของแฟนผมมี 7 หลักเท่านั้น ดังนั้นผมจึงใช้ สูตร excel : ดังนี้
LEFT(text,7) เมื่อตัดช่องว่างออกไปเมื่อประมวลผลใหม่ก็ได้ภาพที่ 2 ครับ

ภาพที่ 2 ผลการคำนวณโดยใช้ excel หลังแก้ไข Text Format

จากปัญหาที่ผมได้นำเสนอจะสรุปได้ว่า
1. หากข้อมูลที่ต้องค้นหาด้วย สูตร excel : VLOOKUP เป็น Text Format เราต้องตรวจสอบข้อมูลให้แน่ใจว่าไม่มีอักขระที่มองไม่เห็นอยู่ใน Text
2. หาก Text Format ที่นำเข้ามาเป็นตัวเลขหมด ให้ใช้ สูตร excel : VALUE ใน excel แปลงเป็นตัวเลขก่อนซึ่งสามารถแก้ปัญหาในข้อที่ 1 ได้

ก็เป็นตัวอย่างประสบการณ์จริงของผมที่นำมาเสนอครับ หวังว่าคงเป็นประโยชน์กับท่านผู้อ่านครับ

ปล ข้อมูลที่นำเข้าจากโปรแกรมสำเร็จรูปอื่น จะพบปัญหาการดำเนินการกับช่องว่างภายในข้อความเนื่องจากช่องว่างดังกล่าวอาจจะไม่ใช่การเคาะ Space bar ลองเข้าไปอ่านดูในบทความ การใช้ excel แก้ปัญหาการค้นหาหรือลบช่องว่างภายในข้อความไม่ได้ดูนะครับ