สร้างฟังก์ชันแบบกำหนดเองใน Excel

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

การสร้างฟังก์ชันแบบกําหนดเองอย่างง่าย

ฟังก์ชันแบบกําหนดเอง เช่น แมโคร ใช้ภาษาโปรแกรม Visual Basic for Applications (VBA) พวกเขาแตกต่างจากแมโครในสองวิธีที่สําคัญ ก่อนอื่นพวกเขาใช้กระบวนงานฟังก์ชันแทนกระบวนงานย่อย กล่าวคือ จะเริ่มต้นด้วยคําสั่ง Function แทนคําสั่ง Sub และลงท้ายด้วย End Function แทน End Sub ประการที่สอง พวกเขาทําการคํานวณแทนการดําเนินการ คําสั่งบางชนิด เช่น คําสั่งที่เลือกและจัดรูปแบบช่วง จะถูกแยกออกจากฟังก์ชันแบบกําหนดเอง ในบทความนี้ คุณจะได้เรียนรู้วิธีการสร้างและใช้ฟังก์ชันแบบกําหนดเอง เมื่อต้องการสร้างฟังก์ชันและแมโคร คุณจะต้องทํางานกับ Visual Basic Editor (VBE) ซึ่งจะเปิดขึ้นในหน้าต่างใหม่ที่แยกจาก Excel

สมมติว่าบริษัทของคุณเสนอส่วนลดจํานวน 10 เปอร์เซ็นต์สําหรับการขายของผลิตภัณฑ์ โดยมีเงื่อนไขว่าคําสั่งซื้อมียอดขายมากกว่า 100 หน่วย ในย่อหน้าต่อไปนี้ เราจะสาธิตฟังก์ชันคํานวณส่วนลดนี้

ตัวอย่างด้านล่างแสดงแบบฟอร์มการสั่งซื้อที่แสดงแต่ละรายการ จํานวน ราคา ส่วนลด (ถ้ามี) และราคาที่เพิ่มขึ้นของผลลัพธ์

เมื่อต้องการสร้างฟังก์ชัน DISCOUNT แบบกําหนดเองในเวิร์กบุ๊กนี้ ให้ทําตามขั้นตอนต่อไปนี้:

1. กด Alt+F11 เพื่อเปิด Visual Basic Editor (บน Mac ให้กด FN+ALT+F11) แล้วคลิก แทรก>มอดูล หน้าต่างโมดูลใหม่จะปรากฏขึ้นทางด้านขวามือของ Visual Basic Editor

2. คัดลอกและวางโค้ดต่อไปนี้ลงในโมดูลใหม่

   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
   
   

การใช้ฟังก์ชันแบบกําหนดเอง

ขณะนี้คุณพร้อมที่จะใช้ฟังก์ชัน DISCOUNT ใหม่แล้ว ปิด Visual Basic Editor เลือกเซลล์ G7 แล้วพิมพ์ดังนี้:

=DISCOUNT(D7,E7)

Excel จะคํานวณส่วนลด 10 เปอร์เซ็นต์สําหรับ 200 หน่วยในราคา $47.50 ต่อหน่วย และส่งกลับ $950.00

ในบรรทัดแรกของโค้ด VBA ของคุณ ฟังก์ชัน DISCOUNT(quantity, price) คุณระบุว่าฟังก์ชัน DISCOUNT ต้องใช้สองอาร์กิวเมนต์ ได้แก่ ปริมาณ และราคา เมื่อคุณเรียกใช้ฟังก์ชันในเซลล์เวิร์กชีต คุณต้องใส่อาร์กิวเมนต์ทั้งสองนั้นไว้ด้วย ในสูตร =DISCOUNT(D7,E7) D7 คืออาร์กิ วเมนต์ปริมาณ และ E7 คืออาร์กิ วเมนต์ราคา ตอนนี้ คุณสามารถคัดลอกสูตร DISCOUNT ไปยัง G8:G13 เพื่อรับผลลัพธ์ที่แสดงด้านล่าง

ลองพิจารณาว่า Excel ตีความกระบวนงานฟังก์ชันนี้อย่างไร เมื่อคุณกด Enter Excel จะ ค้นหาชื่อส่วนลด ในเวิร์กบุ๊กปัจจุบัน และพบว่าเป็นฟังก์ชันแบบกําหนดเองในโมดูล 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

ผลลัพธ์จะถูกจัดเก็บเป็นตัวแปร Discount คําสั่ง VBA ที่เก็บค่าในตัวแปรเรียกว่าคําสั่ง assign เนื่องจาก จะประเมินนิพจน์ทางด้านขวาของเครื่องหมายเท่ากับ และกําหนดผลลัพธ์ให้กับชื่อตัวแปรทางด้านซ้าย เนื่องจากตัวแปร Discount มีชื่อเดียวกับกระบวนงานของฟังก์ชัน ค่าที่จัดเก็บไว้ในตัวแปรจะถูกส่งกลับไปยังสูตรเวิร์กชีตที่เรียกว่าฟังก์ชัน DISCOUNT

ถ้า quantity น้อยกว่า 100 VBA จะดําเนินการคําสั่งต่อไปนี้:

Discount = 0

สุดท้าย คําสั่งต่อไปนี้จะปัดค่าที่กําหนดให้กับตัวแปร Discount ให้เป็นทศนิยมสองตําแหน่ง:

Discount = Application.Round(Discount, 2)

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

ทําความเข้าใจเกี่ยวกับกฎของฟังก์ชันแบบกําหนดเอง

ฟังก์ชันแบบกําหนดเองต้องเริ่มต้นด้วยคําสั่งฟังก์ชัน และลงท้ายด้วยคําสั่งฟังก์ชันสิ้นสุด นอกจากชื่อฟังก์ชันแล้ว คําสั่งฟังก์ชันมักจะระบุอย่างน้อยหนึ่งอาร์กิวเมนต์ อย่างไรก็ตาม คุณสามารถสร้างฟังก์ชันที่ไม่มีอาร์กิวเมนต์ได้ Excel มีฟังก์ชันในตัวหลายฟังก์ชัน เช่น RAND และ NOW ที่ไม่ใช้อาร์กิวเมนต์

ต่อจากคําสั่งฟังก์ชัน กระบวนงานของฟังก์ชันจะมีคําสั่ง VBA อย่างน้อยหนึ่งคําสั่งที่ทําการตัดสินใจและทําการคํานวณโดยใช้อาร์กิวเมนต์ที่ส่งไปยังฟังก์ชัน สุดท้าย ในกระบวนงานฟังก์ชัน คุณต้องรวมคําสั่งที่กําหนดค่าให้กับตัวแปรที่มีชื่อเดียวกันกับฟังก์ชัน ค่านี้จะถูกส่งกลับไปยังสูตรที่เรียกใช้ฟังก์ชัน

การใช้คําสําคัญ VBA ในฟังก์ชันแบบกําหนดเอง

จํานวนคําสําคัญ VBA ที่คุณสามารถใช้ในฟังก์ชันแบบกําหนดเองมีน้อยกว่าจํานวนที่คุณสามารถใช้ในแมโคร ฟังก์ชันแบบกําหนดเองไม่ได้รับอนุญาตให้ทําสิ่งอื่นนอกเหนือจากส่งค่ากลับไปยังสูตรในเวิร์กชีต หรือไปยังนิพจน์ที่ใช้ในแมโครหรือฟังก์ชัน VBA อื่น ตัวอย่างเช่น ฟังก์ชันแบบกําหนดเองไม่สามารถปรับขนาดหน้าต่าง แก้ไขสูตรในเซลล์ หรือเปลี่ยนตัวเลือกฟอนต์ สี หรือรูปแบบสําหรับข้อความในเซลล์ได้ ถ้าคุณรวมโค้ด "การกระทํา" ชนิดนี้ไว้ในกระบวนงานของฟังก์ชัน ฟังก์ชันจะส่งกลับ #VALUE! ข้อผิดพลาด

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

การจัดทําเอกสารแมโครและฟังก์ชันแบบกําหนดเอง

แม้แต่แมโครง่ายๆ และฟังก์ชันแบบกําหนดเองก็อาจอ่านได้ยาก คุณสามารถทําให้เข้าใจได้ง่ายขึ้นด้วยการพิมพ์ข้อความอธิบายในรูปแบบของข้อคิดเห็น คุณสามารถเพิ่มข้อคิดเห็นได้ด้วยเครื่องหมายอัญประกาศเดี่ยวนําหน้าข้อความอธิบาย ตัวอย่างเช่น ตัวอย่างต่อไปนี้แสดงฟังก์ชัน DISCOUNT พร้อมข้อคิดเห็น การเพิ่มข้อคิดเห็นเช่นนี้จะช่วยให้คุณหรือผู้อื่นรักษาโค้ด VBA ของคุณได้ง่ายขึ้นเมื่อเวลาผ่านไป ถ้าคุณจําเป็นต้องทําการเปลี่ยนแปลงโค้ดในอนาคต คุณจะเข้าใจสิ่งที่คุณทําตั้งแต่ต้นได้ง่ายขึ้น

เครื่องหมายอัญประกาศเดี่ยวบอกให้ Excel ละเว้นทุกสิ่งทางด้านขวาบนบรรทัดเดียวกัน เพื่อให้คุณสามารถสร้างข้อคิดเห็นบนบรรทัดเองหรือบนด้านขวาของบรรทัดที่มีโค้ด VBA คุณอาจเริ่มบล็อกโค้ดที่ค่อนข้างยาวด้วยข้อคิดเห็นที่อธิบายวัตถุประสงค์โดยรวม แล้วใช้ข้อคิดเห็นแบบอินไลน์เพื่อจัดทําเอกสารแต่ละคําสั่ง

อีกวิธีหนึ่งในการทําเอกสารแมโครและฟังก์ชันแบบกําหนดเองของคุณคือการตั้งชื่อที่ให้คําอธิบาย ตัวอย่างเช่น แทนที่จะตั้งชื่อแมโคร เป็น ป้ายชื่อ คุณสามารถตั้งชื่อว่า MonthLabels เพื่ออธิบายวัตถุประสงค์ของแมโครให้บริการได้เฉพาะเจาะจงยิ่งขึ้น การใช้ชื่อคําอธิบายสําหรับแมโครและฟังก์ชันแบบกําหนดเองจะมีประโยชน์อย่างมากเมื่อคุณได้สร้างกระบวนงานหลายกระบวน โดยเฉพาะอย่างยิ่ง ถ้าคุณสร้างกระบวนงานที่มีวัตถุประสงค์คล้ายกันแต่ไม่เหมือนกัน

วิธีที่คุณจัดทําเอกสารแมโครและฟังก์ชันแบบกําหนดเองเป็นเรื่องของการกําหนดลักษณะส่วนบุคคล สิ่งสําคัญคือนําวิธีการจัดทําเอกสารมาใช้ และใช้อย่างสม่ําเสมอ

การทําให้ฟังก์ชันแบบกําหนดเองของคุณพร้อมใช้งานได้จากทุกที่

เมื่อต้องการใช้ฟังก์ชันแบบกําหนดเอง เวิร์กบุ๊กที่มีมอดูลที่คุณสร้างฟังก์ชันจะต้องเปิดอยู่ ถ้าเวิร์กบุ๊กนั้นไม่ได้เปิดอยู่ คุณจะได้รับ #NAME? เมื่อคุณพยายามใช้ฟังก์ชัน ถ้าคุณอ้างอิงฟังก์ชันในเวิร์กบุ๊กอื่น คุณต้องนําหน้าชื่อฟังก์ชันด้วยชื่อของเวิร์กบุ๊กที่มีฟังก์ชันนั้นอยู่ ตัวอย่างเช่น ถ้าคุณสร้างฟังก์ชันที่เรียกว่า DISCOUNT ในเวิร์กบุ๊กที่ชื่อว่า Personal.xlsb และคุณเรียกใช้ฟังก์ชันดังกล่าวจากเวิร์กบุ๊กอื่น คุณต้องพิมพ์ =personal.xlsb!discount() ไม่ใช่เพียงแค่ =discount()

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

วิธีที่ง่ายกว่าในการทําให้ฟังก์ชันแบบกําหนดเองของคุณพร้อมใช้งานตลอดเวลาคือการเก็บฟังก์ชันเหล่านั้นไว้ในเวิร์กบุ๊กแยกต่างหาก แล้วบันทึกเวิร์กบุ๊กนั้นเป็น Add-in จากนั้นคุณสามารถทําให้ Add-in พร้อมใช้งานเมื่อใดก็ตามที่คุณใช้ Excel ต่อไปนี้คือวิธีการทำสิ่งนี้:

หลังจากที่คุณทําตามขั้นตอนเหล่านี้ ฟังก์ชันแบบกําหนดเองของคุณจะพร้อมใช้งานทุกครั้งที่คุณเรียกใช้ Excel ถ้าคุณต้องการเพิ่มลงในไลบรารีฟังก์ชัน ให้กลับไปยัง Visual Basic Editor ถ้าคุณดูใน Project Explorer ของ Visual Basic Editor ภายใต้ส่วนหัว VBAProject คุณจะเห็นโมดูลที่ตั้งชื่อตามไฟล์ Add-in ของคุณ Add-in ของคุณจะมีนามสกุล .xlam

ดับเบิลคลิกโมดูลนั้นใน Project Explorer จะทําให้ Visual Basic Editor แสดงโค้ดฟังก์ชันของคุณ เมื่อต้องการเพิ่มฟังก์ชันใหม่ ให้จัดตําแหน่งจุดแทรกของคุณหลังคําสั่งฟังก์ชันสิ้นสุดที่จะสิ้นสุดฟังก์ชันสุดท้ายในหน้าต่างรหัส แล้วเริ่มพิมพ์ คุณสามารถสร้างฟังก์ชันได้มากเท่าที่คุณต้องการด้วยวิธีนี้ และฟังก์ชันเหล่านั้นจะพร้อมใช้งานในประเภท ผู้ใช้กําหนดเอง ในกล่องโต้ตอบ แทรกฟังก์ชัน เสมอ

เกี่ยวกับผู้เขียน

เนื้อหานี้ถูกสร้างขึ้นครั้งแรกโดย Mark Dodge และ Craig Stinson โดยเป็นส่วนหนึ่งของหนังสือ Microsoft Office Excel 2007 Inside Out หลังจากได้รับการอัปเดตเพื่อนําไปใช้กับ Excel เวอร์ชันที่ใหม่กว่าเช่นกัน

ต้องการความช่วยเหลือเพิ่มเติมไหม

คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ