Video ด้านล่างนี้อธิบายความหมายและสาธิตการใช้งานฟังก์ชั่นในหมวด Text ซึ่งเป็นฟังก์ชั่นที่ใช้บ่อยครั้งในการทำงานกับ Excel จึงควรศึกษาให้เข้าใจ ใช้ให้เป็น ประโยชน์ที่สำคัญยิ่งอีกประการคือ ฟังก์ชั่นเหล่านี้จะเป็นพื้นฐานในการประยุกต์ใช้งานที่ซับซ้อนในลำดับสูง ๆ ขึ้นไป
สอบถามปัญหาการใช้งาน Excel และ VBA ได้ที่ snasui.com
การจัดเรียงบ้านเลขที่จึงต้องอาศัยวิธีการอื่นเข้ามาช่วย เช่นการแบ่งบ้านเลขที่ออกเป็น 2 คอลัมน์แล้วค่อยจัดเรียง หรือสามารถใช้สูตร Array เข้ามาช่วยได้
จากภาพด้านบนจะเป็นการนำข้อมูลบ้านเลขที่ในคอลัมน์ A มาเรียงใหม่ โดยให้เรียงจากน้อยไปหามาก
สูตรในเซลล์ C2 คือ
=INDEX($A$2:$A$14,MATCH(SMALL(--SUBSTITUTE(IF(ISNUMBER(FIND("/",$A$2:$A$14)),
$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT("0",10-LEN(MID($A$2:$A$14,
FIND("/",$A$2:$A$14&"/")+1,255)))),ROWS(C$2:C2)),--SUBSTITUTE(IF(
ISNUMBER(FIND("/",$A$2:$A$14)),$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT(
"0",10-LEN(MID($A$2:$A$14,FIND("/",$A$2:$A$14&"/")+1,255)))),0))
ซึ่งจะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array จากนั้น Copy สูตรลงด้านล่าง สามารถดาวน์โหลดไฟล์แนบตามด้านล่างไปศึกษาได้ตามสะดวกครับ
แม้จะเป็นระดับการประยุกต์การใช้งานที่ซับซ้อนก็ตาม เราก็ต้องอาศัยฟังก์ชั่นเหล่านี้เป็นหลัก ซึ่งได้แก่
✏ Min หาค่าที่น้อยที่สุด เช่น Min(A1:C10)
✏ Max หาค่าที่มากที่สุด เช่น Max(D8:E20)
✏ Average หาค่าเฉลี่ย เช่น Average(B5:F50)
✏ Count นับว่ามีตัวเลขกี่ตัว เช่น Count(I5:K8)
✏ Counta นับว่าไม่เป็นเซลล์ว่างกี่เซลล์ เช่น Counta(H8:V3)
✏ Countifs นับตามเงื่อนไข เช่น Countifs(A2:A5,"OK",B2:B5,1) เป็นการนับว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Sumifs รวมตามเงื่อนไข เช่น Sumifs(C2:C5,A2:A5,"OK",B2:B5,1) เป็นการรวมค่าใน C2:C5 โดยมีเงื่อนไขว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Left เป็นการตัดอักขระด้านซ้าย เช่น Left(A2,8) เป็นการตัดอักขระด้านซ้ายของเซลล์ A2 มา 8 อักขระ
✏ Right เป็นการตัดอักขระด้านขวา เช่น Right(B2,4) เป็นการตัดอักขระด้านขวาของเซลล์ B2 มา 4 อักขระ
✏ Mid เป็นการตัดอักขระโดยระบุตำแหน่งเริ่มและจำนวนอักขระที่ตัด เช่น Mid(D2,3,4) เป็นการตัดอักขระในเซลล์ D2 โดยเริ่มอักขระที่ 3 มาจำนวน 4 อักขระ
✏ Len เป็นการนับจำนวนอักขระ เช่น Len(C8) เป็นการนับว่าในเซลล์ C8 มีกี่อักขระ
✏ If เป็นการพิจารณาตามเงื่อนไขว่าจริงหรือเท็จ เช่น If(A2=5,"Yes","No") เป็นการพิจารณาว่า A2 เท่ากับ 5 จริงหรือไม่ หากเป็นจริงให้แสดงคำตอบเป็น Yes หากเป็นเท็จให้แสดงคำตอบเป็น No
✏ Vlookup เป็นการค้นหาค่าใด ๆ ในคอลัมน์แรกของตารางแล้วแสดงผลลัพธ์เป็นค่าในคอลัมน์ใด ๆ ของตารางตามที่กำหนด เช่น Vlookup("Finish",B3:F100,2,0) เป็นการค้นหาคำว่า Finish ใน B3:B100 (คอลัมน์แรกของตาราง) แล้วแสดงผลลัพธ์เป็นค่าใน C3:C100 (คอลัมน์ที่ 2 ของตาราง) หากไม่พบคำว่า Finish ใน B3:B100 จะให้ผลลัพธ์เป็น #N/A
✏ Sumproduct เป็นการหาผลรวมของผลคูณ เช่น Sumproduct(A2:A4,B2:B4) เป็นการหาผลรวมของผลคูณของ A2:A4 และ B2:B4 ลักษณะการทำงานคือ A2*B2+A3*B3+A4*B4
✏ Index เป็นการระบุพื้นที่แล้วแสดงผลลัพธ์ในตำแหน่งที่กำหนด เช่น Index(A3:C8,1,3) เป็นการนำบรรทัดที่ 1 คอลัมน์ที่ 3 ของพื้นที่ A3:C8 มาแสดง ค่าผลลัพธ์คือ C3
✏ Match เป็นการค้นหาค่าใด ๆ ในช่วงแล้วแสดงผลลัพธ์เป็นลำดับที่พบ เช่น Match(True,B5:B10,0) เป็นการหาว่าค่า True อยู่ในลำดับที่เท่าไรของช่วง B5:B10 หากไม่พบค่า True จะแสดงผลลัพธ์เป็น #N/A
✏ Indirect เป็นการอ้างอิงโดยอ้อม เช่น B2 มีค่าเท่ากับ 5, A10 มีค่าเท่ากับ B2 เซลล์ใด ๆ คีย์ Indirect("A10") คำตอบจะได้ 5 ลักษณะการทำงานของสูตรคือ หาก่อนว่าค่า A10 คืออะไร เมื่อพบว่า A10 คือ B2 ฟังก์ชั่นนี้จึงแสดงคำตอบที่เป็นค่าใน B2 สังเกตว่าค่าที่จะคีย์ลงไปใน Indirect ได้จะต้องเป็นการอ้างอิงไปยังปลายทางที่เป็นเซลล์หรือช่วงเซลล์เท่านั้น
✏ Offset เป็นการระบุตำแหน่งเซลล์โดยมีจุดเริ่ม ตำแหน่งบรรทัดที่ถัดจากจุดเริ่ม ตำแหน่งคอลัมน์ที่ถัดจากจุดเริ่ม ความสูงของข้อมูล และความกว้างของข้อมูล เช่น Offset(B4,0,0,1,5) เป็นการระบุช่วงข้อมูลโดยเริ่มที่ B4 ตำแหน่งบรรทัดที่ห่างจาก B4 เป็น 0 ตำแหน่งคอลัมน์ที่ห่างจาก B4 เป็น 0 แสดงว่ายังอยู่ที่จุดเริ่มเช่นเดิมคือ B4 ความสูงของข้อมูลเป็น 1 ความกว้างของข้อมูลเป็น 5 แสดงว่าจาก B4 ขยายไปทางขวา 5 คอลัมน์ ผลลัพธ์จะได้เป็นช่วงเซลล์ B4:F4
สิ่งสำคัญอย่างหนึ่งสำหรับการใช้งานฟังก์ชั่นได้อย่างคล่องแคล่วคือการฝึกฝนใช้งานบ่อย ๆ หากเข้าใจและใช้งานฟังก์ชั่นตามด้านบนนี้ได้เป็นอย่างดี จะสามารถนำไปพัฒนาต่อยอดเพื่อหาคำตอบที่ซับซ้อนขึ้นเรื่อย ๆ ได้อย่างไร้กังวลครับ
มีปัญหาการใช้งาน Microsoft Excel and VBA สอบถามได้ที่ snasui.com
Revised: December 23, 2017 at 12:04
สำหรับการแยกข้อความออกมาเป็นข้อความละเซลล์เพื่อง่ายต่อการจัดการหากว่าเราทราบแน่ชัดว่าข้อความนั้น ๆ แบ่งด้วยอักขระใด เราสามารถแบ่งด้วย Text to columns ได้อย่างง่าย ๆ และใช้ฟังก์ชั่นที่ไม่ซับซ้อนนักในการหาลำดับที่ของแต่ละข้อความออกมาได้
จากภาพด้านล่าง เซลล์ A2 เป็นตัวอย่างข้อความต้นทางหลายข้อความคั่นแต่ละข้อความด้วยเครื่องหมายคอมม่า ส่วนเซลล์ C2 เป็นข้อความเพียงบางส่วนที่เราจะนำไปหาจากข้อความใน A2 ว่าแต่ละข้อความใน C2 อยู่ในลำดับที่เท่าไรของ A2 โดยให้แสดงลำดับที่พบเรียงลงไปด้านล่าง เริ่มลำดับแรกที่เซลล์ D2
เมื่อนำแต่ละข้อความในเซลล์ C2 ไปหาจากข้อความในเซลล์ A2 จะพบว่า
[caption id="attachment_2767" align="alignnone" width="600"]
=IFERROR(INDEX(MATCH(MID($C$2,SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2)))),SMALL(IF(MID($C$2&",",ROW(
INDIRECT("1:"&LEN($C$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($C$2)+1))),
ROW(INDIRECT("1:"&LEN($C$2))))-SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2))))),MID($A$2,SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2)))),SMALL(IF(MID($A$2&",",ROW(
INDIRECT("1:"&LEN($A$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($A$2)+1))),
ROW(INDIRECT("1:"&LEN($A$2))))-SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2))))),0),ROWS(D$2:D2)),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง
สูตรนี้เป็นสูตร Array ที่มีความซับซ้อนสูง ซ้อนกันหลายชั้นเพื่อที่จะตัดแต่ละข้อความออกมาเสียก่อน แล้วค่อยหาลำดับที่พบข้อความใน C2 จากข้อความใน A2 ผู้ที่จะศึกษาสูตรลักษณะนี้จะต้องเข้าใจแต่ละฟังก์ชั่นเป็นอย่างดีมาก่อน สามารถดาวน์โหลดไฟล์แนบด้านล่างไปศึกษาได้ตามสะดวกครับครับ
ยกตัวอย่างเช่น มีรายชื่อตัวยาอยู่ในช่วงเซลล์ A2:A10 ต้องการคีย์ค้นหาในเซลล์ C2 แค่บางอักขระแล้วให้แสดงรายชื่อตัวยาออกมาในเซลล์ D2 เป็นต้นไป
เราสามารถเขียนสุตรได้ตามด้านล่างครับ
ภาพตัวอย่างการค้นหาบางอักขระในข้อความ
ยกตัวอย่างเช่น เราจะหาว่าตัว W ปรากฏกี่ครั้งในข้อความด้านล่างนี้ซึ่งอยู่ที่เซลล์ A1
When Where What Who How
ซึ่งหากนับด้วยตาเราก็พบว่า 5 อักขระ ใครที่คิดว่าตนเองมีความชำนาญในการใช้ฟังก์ชั่น ลองคิดหาวิธีก่อนที่ผมจะเฉลยดูครับ
ฟังก์ชั่นที่ใช้ในการหาจำนวนครั้งที่ปรากฏของ W คือที่ B1 คีย์
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))
หลักการคิดคือนำจำนวนอักขระทั้งหมดหักด้วยจำนวนอักขระคงเหลือหลังจากลบอักขระที่ต้องการหาออกไปแล้ว
การแกะสูตร
จากสูตร =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))
หากลากเมาส์คลุม Substitute("WHAT WHERE WHEN WHO HOW","W","") แล้วกดแป้น F9 จะได้เป็น
LEN("HAT HERE HEN HO HO")
จะเห็นว่าตัว W หายไป ผลลัพธ์จากฟังก์ชั่น LEN("HAT HERE HEN HO HO") จะได้ค่า 18
เมื่อนำ 23 จากข้อ 1 มาหักจาก 18 ตามข้อ 3 จะได้ 5 ซึ่งเป็นคำตอบตรงกับที่นับด้วยตา
คำถาม ทำไมต้องใช้ฟังก์ชั่น Upper() มาช่วย 
คำตอบ เนื่องจากโจทย์นี้มีทั้ง W และ w สองตัวนี้มีค่าไม่เทียบเท่ากันในฟังก์ชั่น Substitute จึงจำเป็นต้องแปลงให้เป็นตัวเล็กหรือตัวใหญ่เสียก่อน ซึ่งผมแปลงให้เป็นตัวใหญ่ด้วยฟังก์ชั่น Upper
สำหรับท่านที่ต้องการเลือกเลขทะเบียนรถเองโดยมีเงื่อนไขในการคัดเลือก ยกตัวอย่างเช่นต้องการให้
การหาค่าดังกล่าวสามารถใช้สูตรหรือ VBA มาคำนวณได้ครับ
Sub ListNum()
Dim i As Integer, bln As Boolean
Dim c As Integer, j As Integer
Dim a As String, s As Integer
For i = 1 To 9999
bln = False
s = 0
For j = 1 To Len(CStr(i))
a = Mid(i, j, 1)
Select Case a
Case 0, 3, 7, 8
bln = True
Exit For
Case Else
s = s + a
End Select
Next j
If bln = False Then
Select Case s
Case 9, 18, 27, 36
c = c + 1
Cells(c + 1, 1) = i
End Select
End If
Next i
End Sub
โปรแกรมจะทำการ Run ตัวเลขมาให้ตามเงื่อนไขที่ต้องการ
การทำ Hyperlink นั้นอำนวยความสะดวกในการเข้าถึงข้อมูลได้อย่างรวดเร็ว ง่ายต่อการนำไปใช้งาน และหากเราดึงข้อมูลตามเงื่อนไขที่ต้องการมาได้แล้ว สามารถคลิกรายการที่สนใจ เพื่อ Link กลับไปยังข้อมูลต้นแหล่งได้ด้วย นับว่ายิ่งเพิ่มมูลค่าให้กับงานเราอย่างน่าทึ่งมากครับ
ยกตัวอย่างการดึงข้อมูลตามเงื่อนไขตามภาพด้านล่าง ใน Sheet2 เป็นการดึงข้อมูลเฉพาะเซลล์ที่มีความยาวข้อมูลระหว่าง 60 ถึง 70 ตัวอักษรจาก Sheet1 และสำหรับเซลล์ที่แสดงผลนั้น สามารถอ้างถึงตำแหน่งที่มาของข้อมูลและยังมีความพิเศษที่สามารถคลิกลงบนรายการที่สนใจ เพื่อ Link กับไปยังข้อมูลต้นแหล่งได้อีกด้วย
ภาพประกอบการ Lookup พร้อมทำ Hyperlink กลับไปยังต้นแหล่ง
ตามวิธีการด้านล่างนี้จะต้อง Save ไฟล์ก่อน เพราะจะต้องนำชื่อไฟล์มาใช้ในสูตร
ซึ่งมีขั้นตอนดังนี้
Note: จากสูตรในข้อ 4 ขอเพิ่มเติมคร่าว ๆ เพื่อเป็นแนวทางครับ
กรณีที่คำนำหน้านามไม่ติดกับชื่อ สามารถใช้ Excel จัดการได้ง่าย ๆ ครับไม่ว่าจะด้วยสูตรหรือใช้ Text to columns แต่หากเมื่อใดที่คำนำหน้านามมีความหลากหลายทั้งยังอยู่ติดกับชื่อแล้ว การจัดการจะยุ่งยากขึ้นครับ
ตามตัวอย่างนี้จะแสดงวิธีการหนึ่งที่จะแยกคำนำหน้านาม ชื่อ และนามสกุลออกจากกันในกรณีที่คำนำหน้านามมีความหลากหลาย ซึ่งจะใช้สูตร Array เข้ามาช่วยครับ
ภาพประกอบการตัดข้อความกรณีมีคำนำหน้านามติดกับชื่อ
ซึ่งมีขั้นตอนดังนี้
ตัวอย่างวิธีคิดหลักๆ จากสูตรในเซล C3
ตัวอย่างการแกะสูตร จากสูตรที่ C3 ซึ่งเป็นสูตร Array
จากสูตร =SUBSTITUTE(RIGHT(A3,MIN(LEN(SUBSTITUTE(A3,$F$2:$F$12,"")))),D3,"")
กรณีที่มีการเขียนภาษาอังกฤษต่อด้วยภาษาไทยหรือตรงกันข้ามกัน และต้องการแยกคำออกมา สามารถใช้สูตรในการแยกภาษาออกมาได้ครับ สมมุติ
Note: สูตรที่ B1 ทั้งสองสูตรเป็นสูตรแบบอาร์เรย์ การคีย์ให้รับสูตรจะต้อง Ctrl+Shift+Enter ครับ
