เรียนรู้วิธีการใช้ Excel Macros โดยอัตโนมัติทำให้งานที่น่าเบื่อ

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

หากคุณสงสัยว่ามาโครคืออะไรหรือสร้างอย่างไรจริง ๆ ไม่มีปัญหาเราจะแนะนำคุณตลอดกระบวนการ.

บันทึก: กระบวนการเดียวกันควรทำงานใน Microsoft Office เวอร์ชันส่วนใหญ่ ภาพหน้าจออาจดูแตกต่างออกไปเล็กน้อย.

มาโครคืออะไร?

แมโคร Microsoft Office (เนื่องจากฟังก์ชันนี้ใช้กับแอปพลิเคชั่น MS Office หลายตัว) เป็นเพียงรหัส Visual Basic สำหรับแอปพลิเคชัน (VBA) ที่บันทึกไว้ในเอกสาร สำหรับการเปรียบเทียบที่คล้ายคลึงกันให้คิดว่าเอกสารเป็น HTML และแมโครเป็น Javascript ในลักษณะเดียวกับที่ Javascript สามารถจัดการ HTML บนเว็บเพจแมโครสามารถจัดการเอกสารได้.

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

• ใช้สไตล์และการจัดรูปแบบ.
• จัดการข้อมูลและข้อความ.
• สื่อสารกับแหล่งข้อมูล (ฐานข้อมูลไฟล์ข้อความ ฯลฯ ).
• สร้างเอกสารใหม่ทั้งหมด.
• ชุดค่าผสมใด ๆ ในลำดับใด ๆ ข้างต้น.

การสร้างแมโคร: คำอธิบายโดยตัวอย่าง

เราเริ่มต้นด้วยไฟล์ CSV สวนหลากหลายของคุณ ไม่มีอะไรพิเศษที่นี่เพียงชุดตัวเลข 10 × 20 ระหว่าง 0 ถึง 100 มีทั้งส่วนหัวของแถวและคอลัมน์ เป้าหมายของเราคือผลิตแผ่นข้อมูลที่จัดรูปแบบได้ดีและมีอยู่ซึ่งรวมถึงผลรวมโดยสรุปสำหรับแต่ละแถว.

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

ในการสร้างมาโครไปที่มุมมอง> มาโคร> บันทึกมาโคร.

กำหนดชื่อแมโคร (ไม่มีช่องว่าง) และคลิกตกลง.

เมื่อเสร็จแล้ว, ทั้งหมด การกระทำของคุณจะได้รับการบันทึก - ทุกการเปลี่ยนแปลงของเซลล์การเลื่อนการกระทำการปรับขนาดหน้าต่างคุณตั้งชื่อ.

มีสถานที่สองแห่งที่ระบุว่า Excel เป็นโหมดบันทึก หนึ่งคือโดยการดูเมนูแมโครและสังเกตว่าหยุดการบันทึกได้แทนที่ตัวเลือกสำหรับบันทึกแมโคร.

อีกอันอยู่ที่มุมล่างขวา ไอคอน 'หยุด' แสดงว่าอยู่ในโหมดมาโครและการกดที่นี่จะหยุดการบันทึก (เช่นเดียวกันเมื่อไม่ได้อยู่ในโหมดบันทึกไอคอนนี้จะเป็นปุ่มบันทึกแมโครซึ่งคุณสามารถใช้แทนการไปที่เมนูมาโคร).

ตอนนี้เรากำลังบันทึกแมโครของเรามาใช้การคำนวณสรุปของเรา ก่อนอื่นให้เพิ่มส่วนหัว.

ถัดไปใช้สูตรที่เหมาะสม (ตามลำดับ):

• = SUM (B2: K2)
• = เฉลี่ย (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

ตอนนี้ไฮไลต์เซลล์การคำนวณทั้งหมดและลากความยาวของแถวข้อมูลทั้งหมดของเราเพื่อใช้การคำนวณกับแต่ละแถว.

เมื่อดำเนินการเสร็จแล้วแต่ละแถวควรแสดงบทสรุปที่เกี่ยวข้อง.

ตอนนี้เราต้องการได้รับข้อมูลสรุปสำหรับชีตทั้งหมดดังนั้นเราจึงใช้การคำนวณเพิ่มเติมอีกสองสามประการ:

ตามลำดับ:

• = SUM (L2: L21)
• = เฉลี่ย (B2: K21) *ต้องคำนวณจากข้อมูลทั้งหมดเนื่องจากค่าเฉลี่ยของค่าเฉลี่ยแถวไม่จำเป็นต้องเท่ากับค่าเฉลี่ยของค่าทั้งหมด.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * คำนวณจากข้อมูลทั้งหมดด้วยเหตุผลเดียวกันกับข้างบน.

ตอนนี้การคำนวณเสร็จสิ้นแล้วเราจะใช้สไตล์และการจัดรูปแบบ ก่อนอื่นให้ใช้การจัดรูปแบบตัวเลขทั่วไปกับเซลล์ทั้งหมดโดยเลือกทั้งหมด (Ctrl + A หรือคลิกเซลล์ระหว่างส่วนหัวของแถวและคอลัมน์) และเลือกไอคอน“ สไตล์จุลภาค” ภายใต้เมนูโฮม.

ถัดไปใช้การจัดรูปแบบภาพกับทั้งส่วนหัวของแถวและคอลัมน์:

• กล้า.
• ศูนย์กลาง.
• เติมสีพื้นหลัง.

และสุดท้ายใช้สไตล์กับผลรวมทั้งหมด.

เมื่อทั้งหมดเสร็จสิ้นนี่คือสิ่งที่แผ่นข้อมูลของเรามีลักษณะ:

เนื่องจากเราพอใจกับผลลัพธ์แล้วให้หยุดการบันทึกแมโคร.

ขอแสดงความยินดี - คุณเพิ่งสร้างมาโคร Excel.

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

ในการทำเช่นนี้เลือกเซลล์ทั้งหมดและลบออก.

ขณะที่ข้อมูลถูกล้าง (แต่แมโครยังคงรวมอยู่ในไฟล์ Excel) เราต้องการบันทึกไฟล์เป็นไฟล์เทมเพลต (XLTM) ที่เปิดใช้งานแมโคร เป็นสิ่งสำคัญที่จะต้องทราบว่าหากคุณบันทึกเป็นไฟล์เทมเพลตมาตรฐาน (XLTX) แล้วแมโครจะ ไม่ สามารถเรียกใช้จากมัน อีกวิธีหนึ่งคุณสามารถบันทึกไฟล์เป็นไฟล์เทมเพลตดั้งเดิม (XLT) ซึ่งจะอนุญาตให้เรียกใช้มาโครได้.

เมื่อคุณบันทึกไฟล์เป็นเทมเพลตแล้วให้ไปข้างหน้าแล้วปิด Excel.

ใช้แมโคร Excel

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

• มาโครอาจเป็นอันตรายได้.
• ดูจุดด้านบน.

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

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

ต่อไปเราจะนำเข้าชุดข้อมูลล่าสุดจาก CSV (นี่คือแหล่งที่แผ่นงานที่ใช้ในการสร้างแมโครของเรา).

เพื่อให้การนำเข้าไฟล์ CSV เสร็จสมบูรณ์คุณอาจต้องตั้งค่าตัวเลือกสองสามอย่างเพื่อให้ Excel ตีความได้อย่างถูกต้อง (เช่นมีตัวคั่นส่วนหัวแสดง ฯลฯ ).

เมื่อนำเข้าข้อมูลของเราแล้วให้ไปที่เมนูมาโคร (ใต้แท็บมุมมอง) แล้วเลือกดูมาโคร.

ในกล่องโต้ตอบผลลัพธ์เราจะเห็นมาโคร“ FormatData” ที่เราบันทึกไว้ด้านบน เลือกและคลิกเรียกใช้.

เมื่อทำงานแล้วคุณอาจเห็นเคอร์เซอร์กระโดดข้ามไปครู่หนึ่ง แต่จะทำให้คุณเห็นข้อมูลที่ถูกจัดการ อย่างแน่นอน ตามที่เราบันทึกไว้ เมื่อทุกคนพูดและทำมันควรจะดูเหมือนกับต้นฉบับของเรา - ยกเว้นข้อมูลที่แตกต่างกัน.

มองใต้กระโปรง: อะไรทำให้งานมาโคร

ดังที่เราได้กล่าวถึงสองสามครั้งแมโครถูกขับเคลื่อนด้วยรหัส Visual Basic for Applications (VBA) เมื่อคุณ“ บันทึก” แมโคร Excel จะแปลทุกอย่างที่คุณทำลงในคำแนะนำ VBA ที่เกี่ยวข้อง คุณไม่ต้องเขียนโค้ดใด ๆ เลยเพราะ Excel กำลังเขียนโค้ดให้คุณ.

หากต้องการดูรหัสที่ทำให้แมโครของเราทำงานจากกล่องโต้ตอบ Macros ให้คลิกปุ่มแก้ไข.

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

ก้าวไปอีกขั้นหนึ่ง ...

สมมุติว่าสมมุติฐาน data data ของเราคือ data.csv ผลิตโดยกระบวนการอัตโนมัติซึ่งจะบันทึกไฟล์ไว้ในตำแหน่งเดียวกันเสมอ (เช่น C: \ Data \ data.csv เป็นข้อมูลล่าสุดเสมอ) กระบวนการเปิดไฟล์นี้และนำเข้าไฟล์สามารถทำเป็นมาโครได้อย่างง่ายดายเช่นกัน:

1. เปิดไฟล์เท็มเพลต Excel ที่มีมาโคร“ FormatData” ของเรา.
2. บันทึกแมโครใหม่ชื่อ“ LoadData”.
3. ด้วยการบันทึกแมโครให้นำเข้าไฟล์ข้อมูลตามปกติ.
4. เมื่อนำเข้าข้อมูลแล้วให้หยุดการบันทึกแมโคร.
5. ลบข้อมูลเซลล์ทั้งหมด (เลือกทั้งหมดจากนั้นลบ).
6. บันทึกเทมเพลตที่อัปเดต (อย่าลืมใช้รูปแบบเทมเพลตที่เปิดใช้งานแมโคร).

เมื่อทำสิ่งนี้เสร็จแล้วเมื่อใดก็ตามที่เทมเพลตเปิดขึ้นจะมีมาโครสองอัน - อันหนึ่งซึ่งโหลดข้อมูลของเราและอีกอันที่ฟอร์แมต.

หากคุณต้องการทำให้มือของคุณสกปรกด้วยการแก้ไขรหัสเล็กน้อยคุณสามารถรวมการกระทำเหล่านี้ลงในแมโครเดียวได้อย่างง่ายดายโดยคัดลอกรหัสที่สร้างจาก“ LoadData” และแทรกไว้ที่จุดเริ่มต้นของรหัสจาก“ FormatData”.

ดาวน์โหลดเทมเพลตนี้

เพื่อความสะดวกของคุณเราได้รวมทั้งเท็มเพลต Excel ที่ผลิตในบทความนี้รวมถึงไฟล์ข้อมูลตัวอย่างเพื่อให้คุณได้เล่นด้วย.

ดาวน์โหลดเทมเพลตแมโครของแมโครจาก How-To Geek