แม้ว่า Excel จะมีฟังก์ชันเวิร์กชีตที่มีอยู่แล้วภายในจํานวนมาก แต่เป็นไปได้ว่า Excel ไม่มีฟังก์ชันสําหรับการคํานวณทุกชนิดที่คุณทํา ผู้ออกแบบ Excel ไม่สามารถคาดการณ์ความต้องการในการคํานวณของผู้ใช้ทุกคนได้ แต่ Excel มีความสามารถในการสร้างฟังก์ชันแบบกําหนดเองซึ่งอธิบายไว้ในบทความนี้แทน
ฟังก์ชันแบบกําหนดเอง เช่น แมโคร ให้ใช้ภาษาการเขียนโปรแกรม Visual Basic for Applications (VBA) ซึ่งแตกต่างจากแมโครด้วยสองวิธีสําคัญ ขั้นแรก จะใช้กระบวนงานฟังก์ชันแทนกระบวนงานย่อย กล่าวคือ คําสั่งจะเริ่มต้นด้วยคําสั่ง ฟังก์ชัน แทนคําสั่ง ย่อย และลงท้ายด้วย ฟังก์ชัน End แทน End Sub ประการที่สอง จะทําการคํานวณแทนการดําเนินการ คําสั่งบางชนิด เช่น คําสั่งที่เลือกและจัดรูปแบบช่วงจะถูกแยกออกจากฟังก์ชันแบบกําหนดเอง ในบทความนี้ คุณจะได้เรียนรู้วิธีการสร้างและใช้ฟังก์ชันแบบกําหนดเอง เมื่อต้องการสร้างฟังก์ชันและแมโคร ให้คุณทํางานกับ Visual Basic Editor (VBE) ซึ่งจะเปิดในหน้าต่างใหม่ที่แยกต่างหากจาก Excel
สมมติว่าบริษัทของคุณเสนอส่วนลดปริมาณ 10 เปอร์เซ็นต์ในการขายผลิตภัณฑ์ โดยการสั่งซื้อมีมูลค่ามากกว่า 100 หน่วย ในย่อหน้าต่อไปนี้ เราจะสาธิตฟังก์ชันในการคํานวณส่วนลดนี้
ตัวอย่างด้านล่างแสดงแบบฟอร์มการสั่งซื้อที่แสดงรายการสินค้า ปริมาณ ราคา ส่วนลด (ถ้ามี) และราคาแบบขยายที่เป็นผลลัพธ์
เมื่อต้องการสร้างฟังก์ชัน DISCOUNT แบบกําหนดเองในเวิร์กบุ๊กนี้ ให้ทําตามขั้นตอนต่อไปนี้:
-
กด Alt+F11 เพื่อเปิด Visual Basic Editor (บน Mac ให้กด FN+ALT+F11) แล้วคลิก แทรก > โมดูล หน้าต่างมอดูลใหม่จะปรากฏขึ้นทางด้านขวาของ Visual Basic Editor
-
คัดลอกและวางรหัสต่อไปนี้ลงในโมดูลใหม่
Function DISCOUNT(quantity, price) If quantity >=100 Then DISCOUNT = quantity * price * 0.1 Else DISCOUNT = 0 End If DISCOUNT = Application.Round(Discount, 2) End Function
หมายเหตุ: เมื่อต้องการทําให้โค้ดของคุณอ่านได้ง่ายขึ้น คุณสามารถใช้แป้น Tab เพื่อเยื้องบรรทัดได้ การเยื้องมีไว้เพื่อสิทธิประโยชน์ของคุณเท่านั้น และจะมีหรือไม่ก็ได้ เนื่องจากโค้ดจะทํางานโดยมีหรือไม่มีโค้ด หลังจากที่คุณพิมพ์บรรทัดที่เยื้อง Visual Basic Editor จะถือว่าบรรทัดถัดไปของคุณจะถูกเยื้องในทํานองเดียวกัน เมื่อต้องการย้ายออก (ซึ่งอยู่ทางซ้าย) หนึ่งอักขระแท็บ ให้กด Shift+Tab
ตอนนี้คุณก็พร้อมที่จะใช้ฟังก์ชัน DISCOUNT ใหม่แล้ว ปิด Visual Basic Editor เลือกเซลล์ G7 แล้วพิมพ์ดังต่อไปนี้:
=DISCOUNT(D7,E7)
Excel จะคํานวณส่วนลด 10 เปอร์เซ็นต์สําหรับ 200 หน่วยที่ $47.50 ต่อหน่วย และส่งกลับ $950.00
ในบรรทัดแรกของโค้ด VBA ของคุณ ฟังก์ชัน DISCOUNT(ปริมาณ ราคา) คุณระบุว่าฟังก์ชัน DISCOUNT ต้องการอาร์กิวเมนต์ ปริมาณ และ ราคาสองตัว เมื่อคุณเรียกใช้ฟังก์ชันในเซลล์เวิร์กชีต คุณต้องใส่สองอาร์กิวเมนต์ดังกล่าว ในสูตร =DISCOUNT(D7,E7) D7 คืออาร์กิวเมนต์ quantity และ E7 คืออาร์กิวเมนต์ price ตอนนี้คุณสามารถคัดลอกสูตรส่วนลดไปยัง G8:G13 เพื่อให้ได้ผลลัพธ์ที่แสดงด้านล่าง
มาลองพิจารณาวิธีที่ Excel แปลกระบวนงานฟังก์ชันนี้ เมื่อคุณกด Enter Excel จะค้นหาชื่อ DISCOUNT ในเวิร์กบุ๊กปัจจุบัน และพบว่าเป็นฟังก์ชันแบบกําหนดเองในมอดูล VBA ชื่ออาร์กิวเมนต์ที่อยู่ในวงเล็บ ปริมาณ และ ราคา คือพื้นที่ที่สํารองไว้สําหรับค่าที่ใช้คํานวณส่วนลด
คําสั่ง If ในบล็อกรหัสต่อไปนี้จะตรวจสอบอาร์กิวเมนต์ ปริมาณ และกําหนดว่าจํานวนสินค้าที่ขายมากกว่าหรือเท่ากับ 100:
If quantity >= 100 Then
DISCOUNT = quantity * price * 0.1
Else
DISCOUNT = 0
End If
ถ้าจํานวนของสินค้าที่ขายมากกว่าหรือเท่ากับ 100 VBA จะดําเนินการคําสั่งต่อไปนี้ ซึ่งจะคูณค่า ปริมาณ ด้วยค่า ราคา แล้วคูณผลลัพธ์ด้วย 0.1:
Discount = quantity * price * 0.1
ผลลัพธ์จะถูกจัดเก็บเป็น ส่วนลดผันแปร คําสั่ง VBA ที่เก็บค่าในตัวแปรเรียกว่าคําสั่ง การกําหนด เนื่องจากคําสั่งจะประเมินนิพจน์ทางด้านขวาของเครื่องหมายเท่ากับ และกําหนดผลลัพธ์ให้กับชื่อตัวแปรทางด้านซ้าย เนื่องจาก ส่วนลด ของตัวแปรมีชื่อเดียวกับกระบวนงานฟังก์ชัน ค่าที่เก็บไว้ในตัวแปรจะถูกส่งกลับไปยังสูตรเวิร์กชีตที่เรียกว่าฟังก์ชัน DISCOUNT
ถ้า ปริมาณ น้อยกว่า 100 VBA จะดําเนินการคําสั่งต่อไปนี้:
Discount = 0
สุดท้าย คําสั่งต่อไปนี้จะปัดเศษค่าที่กําหนดให้กับตัวแปร ส่วนลด เป็นทศนิยมสองตําแหน่ง:
Discount = Application.Round(Discount, 2)
VBA ไม่มีฟังก์ชัน ROUND แต่ Excel มี ดังนั้น เมื่อต้องการใช้ ROUND ในคําสั่งนี้ คุณบอก VBA ให้ค้นหาเมธอด Round (ฟังก์ชัน) ในวัตถุ Application (Excel) คุณสามารถทําได้โดยการเพิ่มคําว่า Application ก่อนคําว่า Round ใช้ไวยากรณ์นี้เมื่อใดก็ตามที่คุณจําเป็นต้องเข้าถึงฟังก์ชัน Excel จากมอดูล VBA
ฟังก์ชันแบบกําหนดเองต้องเริ่มต้นด้วยคําสั่ง ฟังก์ชัน และลงท้ายด้วยคําสั่ง สิ้นสุดฟังก์ชัน นอกจากชื่อฟังก์ชันแล้ว โดยปกติคําสั่ง ฟังก์ชัน จะระบุอาร์กิวเมนต์อย่างน้อยหนึ่งอาร์กิวเมนต์ อย่างไรก็ตาม คุณสามารถสร้างฟังก์ชันที่ไม่มีอาร์กิวเมนต์ได้ Excel มีฟังก์ชันที่มีอยู่แล้วภายในหลายฟังก์ชัน เช่น RAND และ NOW ที่ไม่ใช้อาร์กิวเมนต์
ตามหลังคําสั่ง ฟังก์ชัน กระบวนงานฟังก์ชันจะมีคําสั่ง VBA อย่างน้อยหนึ่งคําสั่งที่ทําการตัดสินใจและทําการคํานวณโดยใช้อาร์กิวเมนต์ที่ส่งผ่านไปยังฟังก์ชัน สุดท้าย ในขั้นตอนการทํางาน คุณต้องรวมคําสั่งที่กําหนดค่าให้กับตัวแปรที่มีชื่อเดียวกันกับฟังก์ชัน ค่านี้จะถูกส่งกลับไปยังสูตรที่เรียกใช้ฟังก์ชัน
จํานวนคําสําคัญ VBA ที่คุณสามารถใช้ในฟังก์ชันแบบกําหนดเองมีขนาดเล็กกว่าจํานวนที่คุณสามารถใช้ในแมโครได้ ฟังก์ชันแบบกําหนดเองไม่ได้รับอนุญาตให้ทําสิ่งอื่นนอกเหนือจากการส่งกลับค่าไปยังสูตรในเวิร์กชีต หรือไปยังนิพจน์ที่ใช้ในแมโครหรือฟังก์ชัน VBA อื่น ตัวอย่างเช่น ฟังก์ชันแบบกําหนดเองไม่สามารถปรับขนาดหน้าต่าง แก้ไขสูตรในเซลล์ หรือเปลี่ยนตัวเลือกฟอนต์ สี หรือรูปแบบสําหรับข้อความในเซลล์ได้ ถ้าคุณใส่โค้ด "action" ชนิดนี้ในกระบวนงานฟังก์ชัน ฟังก์ชันจะส่งกลับ #VALUE! ข้อผิดพลาด
การดําเนินการหนึ่งที่กระบวนงานฟังก์ชันสามารถทําได้ (นอกเหนือจากการคํานวณ) จะแสดงกล่องโต้ตอบ คุณสามารถใช้คําสั่ง กล่องป้อนข้อมูล ในฟังก์ชันแบบกําหนดเองเป็นวิธีการรับอินพุตจากผู้ใช้ที่ดําเนินการฟังก์ชัน คุณสามารถใช้คําสั่ง MsgBox เป็นสื่อความหมายของการสื่อข้อมูลให้กับผู้ใช้ คุณยังสามารถใช้กล่องโต้ตอบแบบกําหนดเองหรือ ฟอร์มผู้ใช้ได้ แต่นี่เป็นเรื่องที่อยู่นอกเหนือขอบเขตของบทนํานี้
แม้แต่แมโครที่เรียบง่ายและฟังก์ชันแบบกําหนดเองก็อ่านได้ยาก คุณสามารถทําให้เข้าใจได้ง่ายขึ้นด้วยการพิมพ์ข้อความอธิบายในรูปแบบของข้อคิดเห็น คุณเพิ่มข้อคิดเห็นโดยนําหน้าข้อความอธิบายด้วยเครื่องหมายอัญประกาศเดี่ยว ตัวอย่างเช่น ตัวอย่างต่อไปนี้แสดงฟังก์ชัน DISCOUNT พร้อมข้อคิดเห็น การเพิ่มข้อคิดเห็นเช่นนี้ทําให้คุณหรือผู้อื่นสามารถเก็บรักษาโค้ด VBA ของคุณได้ง่ายยิ่งขึ้นเมื่อเวลาผ่านไป ถ้าคุณจําเป็นต้องทําการเปลี่ยนแปลงรหัสในอนาคต คุณจะมีเวลาทําความเข้าใจสิ่งที่คุณทําในตอนแรกได้ง่ายขึ้น
เครื่องหมายอัญประกาศเดี่ยวบอกให้ Excel ละเว้นทุกอย่างทางด้านขวาของบรรทัดเดียวกัน เพื่อให้คุณสามารถสร้างข้อคิดเห็นทีละบรรทัดหรือทางด้านขวาของบรรทัดที่มีโค้ด VBA ได้ คุณอาจเริ่มบล็อกโค้ดที่ค่อนข้างยาวด้วยข้อคิดเห็นที่อธิบายวัตถุประสงค์โดยรวม แล้วใช้ข้อคิดเห็นแบบอินไลน์เพื่อจัดทําเอกสารแต่ละคําสั่ง
อีกวิธีหนึ่งในการจัดทําเอกสารแมโครและฟังก์ชันแบบกําหนดเองของคุณคือการตั้งชื่อที่ให้คําอธิบายแก่แมโคร ตัวอย่างเช่น แทนที่จะตั้งชื่อ แมโคร ป้ายชื่อ คุณสามารถตั้งชื่อว่า MonthLabels เพื่ออธิบายวัตถุประสงค์ที่แมโครทําหน้าที่ได้โดยเฉพาะมากขึ้น การใช้ชื่อที่ให้คําอธิบายสําหรับแมโครและฟังก์ชันแบบกําหนดเองจะมีประโยชน์อย่างยิ่งเมื่อคุณสร้างหลายกระบวนงาน โดยเฉพาะอย่างยิ่งถ้าคุณสร้างกระบวนงานที่มีวัตถุประสงค์คล้ายกัน แต่ไม่เหมือนกัน
วิธีที่คุณจัดทําเอกสารแมโครและฟังก์ชันแบบกําหนดเองเป็นเรื่องของการกําหนดลักษณะส่วนบุคคล สิ่งสําคัญคือการใช้วิธีการจัดทําเอกสารบางอย่างและใช้อย่างสม่ําเสมอ
เมื่อต้องการใช้ฟังก์ชันแบบกําหนดเอง เวิร์กบุ๊กที่มีมอดูลที่คุณสร้างฟังก์ชันต้องเปิดอยู่ ถ้าเวิร์กบุ๊กนั้นไม่ได้เปิดอยู่ คุณจะได้รับ #NAME ใช่หรือไม่ เมื่อคุณพยายามใช้ฟังก์ชัน ถ้าคุณอ้างอิงฟังก์ชันในเวิร์กบุ๊กอื่น คุณต้องนําหน้าชื่อฟังก์ชันด้วยชื่อของเวิร์กบุ๊กที่มีฟังก์ชันนั้นอยู่ ตัวอย่างเช่น ถ้าคุณสร้างฟังก์ชันที่เรียกว่า DISCOUNT ในเวิร์กบุ๊กที่เรียกว่า Personal.xlsb และคุณเรียกใช้ฟังก์ชันนั้นจากเวิร์กบุ๊กอื่น คุณต้องพิมพ์ =personal.xlsb!discount() ไม่ใช่เพียง =discount()
คุณสามารถบันทึกการกดแป้นพิมพ์ (และข้อผิดพลาดในการพิมพ์ที่เป็นไปได้) ด้วยการเลือกฟังก์ชันแบบกําหนดเองของคุณจากกล่องโต้ตอบ แทรกฟังก์ชัน ฟังก์ชันแบบกําหนดเองของคุณจะปรากฏในประเภท ผู้ใช้กําหนดเอง ดังนี้
วิธีที่ง่ายกว่าในการทําให้ฟังก์ชันแบบกําหนดเองของคุณพร้อมใช้งานตลอดเวลาคือการเก็บฟังก์ชันเหล่านั้นไว้ในเวิร์กบุ๊กที่แยกต่างหาก แล้วบันทึกเวิร์กบุ๊กนั้นเป็น Add-in จากนั้นคุณสามารถทําให้ Add-in พร้อมใช้งานเมื่อใดก็ตามที่คุณเรียกใช้ Excel วิธีการมีดังนี้:
-
หลังจากที่คุณสร้างฟังก์ชันที่คุณต้องการแล้ว ให้คลิก ไฟล์ > บันทึกเป็น
-
ในกล่องโต้ตอบ บันทึกเป็น ให้เปิดรายการดรอปดาวน์ บันทึกเป็นชนิด แล้วเลือก Add-In ของ Excel บันทึกเวิร์กบุ๊กภายใต้ชื่อที่รู้จัก เช่น MyFunctions ในโฟลเดอร์ AddIns กล่องโต้ตอบ บันทึกเป็น จะเสนอโฟลเดอร์นั้น ดังนั้นสิ่งที่คุณต้องทําคือยอมรับตําแหน่งที่ตั้งเริ่มต้น
-
หลังจากที่คุณบันทึกเวิร์กบุ๊กแล้ว ให้คลิก ไฟล์ > ตัวเลือก Excel
-
ในกล่องโต้ตอบ ตัวเลือกของ Excel ให้คลิกประเภท Add-In
-
ในรายการดรอปดาวน์ จัดการ ให้เลือก Add-in ของ Excel จากนั้นคลิกปุ่ม ไป
-
ในกล่องโต้ตอบ Add-in ให้เลือกกล่องกาเครื่องหมายที่อยู่ด้านข้างชื่อที่คุณใช้บันทึกเวิร์กบุ๊กของคุณ ตามที่แสดงไว้ด้านล่าง
-
หลังจากที่คุณสร้างฟังก์ชันที่คุณต้องการแล้ว ให้คลิก ไฟล์ > บันทึกเป็น
-
ในกล่องโต้ตอบ บันทึกเป็น ให้เปิดรายการดรอปดาวน์ บันทึกเป็นชนิด แล้วเลือก Add-In ของ Excel บันทึกเวิร์กบุ๊กภายใต้ชื่อที่รู้จัก เช่น ฟังก์ชันของฉัน
-
หลังจากที่คุณบันทึกเวิร์กบุ๊กแล้ว ให้คลิก เครื่องมือ > Add-in ของ Excel
-
ในกล่องโต้ตอบ Add-in ให้เลือกปุ่ม เรียกดู เพื่อค้นหา Add-in ของคุณ คลิก เปิด แล้วเลือกกล่องข้าง Add-In ของคุณในกล่อง Add-in ที่มีอยู่
หลังจากที่คุณทําตามขั้นตอนเหล่านี้ แล้ว ฟังก์ชันแบบกําหนดเองของคุณจะพร้อมใช้งานในแต่ละครั้งที่คุณเรียกใช้ Excel ถ้าคุณต้องการเพิ่มลงในไลบรารีฟังก์ชันของคุณ ให้กลับไปที่ Visual Basic Editor ถ้าคุณดูใน Visual Basic Editor Project Explorer ภายใต้หัวเรื่อง VBAProject คุณจะเห็นมอดูลที่ตั้งชื่อตามหลังไฟล์ Add-in ของคุณ Add-in ของคุณจะมีส่วนขยาย .xlam
การดับเบิลคลิกโมดูลนั้นใน Project Explorer จะทําให้ Visual Basic Editor แสดงโค้ดฟังก์ชันของคุณ เมื่อต้องการเพิ่มฟังก์ชันใหม่ ให้จัดตําแหน่งจุดแทรกของคุณหลังจากคําสั่ง สิ้นสุดฟังก์ชัน ที่สิ้นสุดฟังก์ชันสุดท้ายในหน้าต่าง โค้ด แล้วเริ่มพิมพ์ คุณสามารถสร้างฟังก์ชันได้มากเท่าที่คุณต้องการในลักษณะนี้ และฟังก์ชันเหล่านั้นจะพร้อมใช้งานในประเภท ผู้ใช้กําหนดเอง ในกล่องโต้ตอบ แทรกฟังก์ชัน เสมอ
เนื้อหานี้ถูกสร้างขึ้นโดย Mark Dodge และ Craig Stinson เป็นส่วนหนึ่งของหนังสือ Microsoft Office Excel 2007 Inside Out ซึ่งได้รับการอัปเดตเพื่อนําไปใช้กับ Excel เวอร์ชันที่ใหม่กว่าด้วยเช่นกัน
ต้องการความช่วยเหลือเพิ่มเติมไหม
คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community หรือรับการสนับสนุนใน ชุมชน