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

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

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

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

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

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

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 เลือกเซลล์ 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 วิธีการมีดังนี้:

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

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

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