ข้ามไปที่เนื้อหาหลัก
การสนับสนุน
ลงชื่อเข้าใช้

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

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

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

สมมติว่าบริษัทของคุณมีส่วนลดปริมาณของ10เปอร์เซ็นต์ในการขายผลิตภัณฑ์ให้คำสั่งซื้อเป็นหน่วยมากกว่า๑๐๐หน่วย ในย่อหน้าต่อไปนี้เราจะแสดงให้เห็นถึงฟังก์ชันเพื่อคำนวณส่วนลดนี้

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

ตัวอย่างฟอร์มการสั่งซื้อที่ไม่มีฟังก์ชันแบบกำหนดเอง

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

  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
    

หมายเหตุ: เมื่อต้องการทำให้โค้ดของคุณอ่านได้ง่ายขึ้นคุณสามารถใช้แป้น Tab เพื่อเยื้องเส้นได้ การเยื้องสำหรับสิทธิประโยชน์ของคุณเท่านั้นและจะเป็นตัวเลือกที่จะทำงานด้วยหรือไม่ก็ได้ หลังจากที่คุณพิมพ์บรรทัดที่เยื้องแล้ว Visual Basic Editor จะถือว่าบรรทัดถัดไปของคุณจะถูกเยื้องในทำนองเดียวกัน เมื่อต้องการย้าย (ที่อยู่ทางด้านซ้าย) อักขระแท็บหนึ่งให้กดShift + tab

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

= ส่วนลด (D7, E7)

Excel จะคำนวณส่วนลด10เปอร์เซ็นต์ในหน่วย๒๐๐ที่ $๔๗.๕๐ต่อหน่วยและส่งกลับ $๙๕๐.๐๐

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

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

ตัวอย่างฟอร์มการสั่งซื้อที่มีฟังก์ชันแบบกำหนดเอง

คำสั่ง If ในบล็อกของโค้ดต่อไปนี้จะตรวจสอบความถูกต้องของอาร์กิวเมนต์ ปริมาณ และกำหนดว่าจำนวนของรายการที่ขายมากกว่าหรือเท่ากับ๑๐๐:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

ถ้าจำนวนของรายการที่ขายมากกว่าหรือเท่ากับ๑๐๐ VBA จะดำเนินการคำสั่งต่อไปนี้ซึ่งคูณค่า ปริมาณ ตามค่า ราคา แล้วคูณผลลัพธ์โดย๐.๑:

Discount = quantity * price * 0.1

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

ถ้า ปริมาณ น้อยกว่า๑๐๐ VBA จะดำเนินการคำสั่งต่อไปนี้:

Discount = 0

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

Discount = Application.Round(Discount, 2)

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

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

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

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

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

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

ตัวอย่างของฟังก์ชัน VBA ที่มีข้อคิดเห็น

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

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

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

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

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

กล่องโต้ตอบแทรกฟังก์ชัน

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

  1. หลังจากที่คุณได้สร้างฟังก์ชันที่คุณต้องการแล้วให้คลิกไฟล์>บันทึกเป็น

    ใน Excel 2007 ให้คลิก ปุ่ม Microsoft Officeแล้วคลิก บันทึกเป็น

  2. ในกล่องโต้ตอบบันทึกเป็นให้เปิดรายการดรอปดาวน์บันทึกเป็นชนิดแล้วเลือกadd-in ของ Excel บันทึกเวิร์กบุ๊กภายใต้ชื่อที่รู้จักเช่นMyFunctionsในโฟลเดอร์AddIns กล่องโต้ตอบ บันทึกเป็น จะนำเสนอโฟลเดอร์ดังนั้นสิ่งที่คุณจำเป็นต้องทำคือยอมรับตำแหน่งที่ตั้งเริ่มต้น

  3. หลังจากที่คุณบันทึกเวิร์กบุ๊กแล้วให้คลิกไฟล์>ตัวเลือก Excel

    ใน Excel 2007 ให้คลิกปุ่ม Microsoft Officeแล้วคลิกตัวเลือกของ Excel

  4. ในกล่องโต้ตอบตัวเลือกของ Excelให้คลิกประเภทadd-in

  5. ในรายการดรอปดาวน์Manageให้เลือกExcel add-in จากนั้นคลิกปุ่มไป

  6. ในกล่องโต้ตอบ add-in ให้เลือกกล่องกาเครื่องหมายที่อยู่ข้างๆชื่อที่คุณใช้ในการบันทึกเวิร์กบุ๊กของคุณดังที่แสดงไว้ด้านล่าง

    กล่องโต้ตอบ Add-in

  1. หลังจากที่คุณได้สร้างฟังก์ชันที่คุณต้องการแล้วให้คลิกไฟล์>บันทึกเป็น

  2. ในกล่องโต้ตอบบันทึกเป็นให้เปิดรายการดรอปดาวน์บันทึกเป็นชนิดแล้วเลือกadd-in ของ Excel บันทึกเวิร์กบุ๊กภายใต้ชื่อที่รู้จักเช่นMyFunctions

  3. หลังจากที่คุณบันทึกเวิร์กบุ๊กแล้วให้คลิกเครื่องมือ> add-in ของ Excel

  4. ในกล่องโต้ตอบadd-inให้เลือกปุ่มเรียกดูเพื่อค้นหา add-in ของคุณแล้วคลิกเปิดจากนั้นเลือกกล่องที่อยู่ข้างๆ Add-In ของคุณในกล่องadd-in ที่พร้อมใช้งาน

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

โมดูลที่มีชื่อใน VBE

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

เนื้อหานี้ถูกเขียนโดยการทำเครื่องหมายดอดจ์และเครก Stinson เป็นส่วนหนึ่งของสมุดบัญชีMicrosoft Office Excel ๒๐๐๗ภายใน เนื่องจากได้รับการอัปเดตเพื่อนำไปใช้กับ Excel เวอร์ชันที่ใหม่กว่าด้วย

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

คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community ขอความช่วยเหลือใน Answers community หรือแนะนำฟีเจอร์ใหม่หรือการปรับปรุงบน Excel User Voiceได้เสมอ

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

ขยายทักษะ Office ของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×