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

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

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

หมายเหตุ: กระบวนการเดียวกันนี้ควรใช้ได้กับ Microsoft Office เกือบทุกเวอร์ชัน ภาพหน้าจออาจดูแตกต่างกันเล็กน้อย

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

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

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

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

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

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

ดังที่เราได้กล่าวไว้ข้างต้นมาโครคือรหัส VBA แต่สิ่งที่ดีอย่างหนึ่งเกี่ยวกับ Excel คือคุณสามารถสร้าง / บันทึกได้โดยไม่ต้องมีการเข้ารหัส - อย่างที่เราจะทำที่นี่

ในการสร้างมาโครให้ไปที่ View> Macros> Record Macro

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

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

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

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

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

จากนั้นใช้สูตรที่เหมาะสม (ตามลำดับ):

• = SUM (B2: K2)
• = ค่าเฉลี่ย (B2: K2)
• = ขั้นต่ำ (B2: K2)
• = สูงสุด (B2: K2)
• = สื่อกลาง (B2: K2)

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

เมื่อเสร็จแล้วแต่ละแถวควรแสดงสรุปตามลำดับ

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

ตามลำดับ:

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

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

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

• ตัวหนา
• อยู่กึ่งกลาง
• สีเติมพื้นหลัง

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

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

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

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

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

โดยเลือกเซลล์ทั้งหมดแล้วลบออก

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

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

ใช้ Excel Macro

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

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

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

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

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

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

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

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

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

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

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

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

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

ยกตัวอย่างของเราไปอีกขั้น ...

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

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

เมื่อเสร็จแล้วทุกครั้งที่เปิดเทมเพลตจะมีมาโครสองอันอันหนึ่งโหลดข้อมูลของเราและอีกอันที่จัดรูปแบบ

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

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

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

ดาวน์โหลดเทมเพลต Excel Macro จาก How-To Geek