Office

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

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

คุณกำลังมองหาข้อมูลเกี่ยวกับวิธีการสร้างฟังก์ชัน Javascript แบบกำหนดเองที่คุณสามารถทำงานบน Excel สำหรับ Windows, Excel for Mac หรือ 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 ใช่ไหม ข้อผิดพลาดเมื่อคุณพยายามใช้ฟังก์ชัน ถ้าคุณอ้างอิงฟังก์ชันในเวิร์กบุ๊กอื่นคุณต้องนำหน้าชื่อฟังก์ชันที่มีชื่อของเวิร์กบุ๊กที่มีฟังก์ชันอยู่ ตัวอย่างเช่นถ้าคุณสร้างฟังก์ชันที่เรียกว่าส่วนลดในเวิร์กบุ๊กที่เรียกว่า .xlsb และคุณเรียกใช้ฟังก์ชันนี้จากเวิร์กบุ๊กอื่นคุณต้องพิมพ์= .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 ของเรา

×