วิธีทำให้รายงานอัตโนมัติใน Excel (พร้อมรูปภาพ)

2024 ผู้เขียน: Jason Gerald | [email protected]. แก้ไขล่าสุด: 2024-01-31 09:39

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

ขั้นตอน

วิธีที่ 1 จาก 2: การสร้างสเปรดชีตเชิงโต้ตอบ

ขั้นตอนที่ 1 กำหนดเค้าโครงของสเปรดชีต

เค้าโครงของสเปรดชีตต้องทำเพื่อให้ผู้อื่นสามารถค้นหาฟิลด์ที่จำเป็นในการป้อนข้อมูล

เค้าโครงสเปรดชีตสามารถจัดวางในแนวนอนหรือแนวตั้ง ผู้ใช้ส่วนใหญ่ใช้งานเลย์เอาต์แนวตั้งได้ง่ายขึ้น โดยเฉพาะถ้าจะพิมพ์สเปรดชีต

ขั้นตอนที่ 2 สร้างป้ายกำกับข้อความสำหรับสเปรดชีต

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

ขั้นตอนที่ 3 กดปุ่ม alt=""Image" และ F11 พร้อมกัน</h4" />
คีย์ผสมนี้จะเปิดโปรแกรมแก้ไข Microsoft Visual Basic

รายงานอัตโนมัติใน Excel ขั้นตอนที่4

ขั้นตอนที่ 4 ดับเบิลคลิก "สมุดงานนี้" ในบานหน้าต่าง "โครงการ-VBA โครงการ" ที่ด้านบนซ้าย

หน้าต่างสำหรับเขียนโค้ดจะปรากฏในส่วนหลักของตัวแก้ไข

ขั้นตอนที่ 5. เลือก "ขั้นตอน" จากเมนูแทรก

กล่องโต้ตอบเพิ่มขั้นตอนจะปรากฏขึ้น

ขั้นตอนที่ 6 ป้อนชื่อของขั้นตอนในฟิลด์ชื่อ

ป้อนชื่อที่สื่อความหมายสำหรับขั้นตอน เช่น "SumExpenses" หากจะใช้สเปรดชีตเพื่อรายงานค่าใช้จ่ายในการเดินทาง คลิกตกลงเพื่อปิดกล่องโต้ตอบ

ชื่อโปรซีเจอร์ต้องไม่มีช่องว่าง แต่สามารถใช้ขีดล่าง (_) เพื่อแทนที่ช่องว่างได้
หลังจากที่กล่องโต้ตอบเพิ่มขั้นตอนปิดลง จะมีบรรทัดปรากฏขึ้นว่า "ย่อยสาธารณะ" ตามด้วยชื่อของขั้นตอน ด้านล่างบรรทัดนั้นมีช่องว่างและคำว่า "End Sub"

ขั้นตอนที่ 7 ป้อนรหัสสำหรับแต่ละช่องป้อนข้อมูลในสเปรดชีต

คุณจะเขียนโค้ดสองบรรทัดสำหรับแต่ละรายการ

รหัสบรรทัดแรกอยู่ในรูปแบบ "Range("cellname")). Select", "cellname" หมายถึงเซลล์ที่ป้อนข้อมูล กรอกชื่อเซลล์ที่อยู่ทางขวาของป้ายข้อความ ถ้าป้ายข้อความอยู่ในเซลล์ A2 ฟิลด์สำหรับป้อนข้อมูลคือเซลล์ B2 (ช่วง("B2")). เลือก) เครื่องหมายอัญประกาศก่อนและหลังชื่อเซลล์จะยังคงเขียนอยู่ในขั้นตอนนี้ แต่จะไม่รวมอยู่ในคำสั่งรหัสที่สมบูรณ์อีกต่อไป
รหัสในบรรทัดที่สองคือ "ActiveCell. Value = InputBox("InputPrompt")" "InputPrompt" หมายถึงข้อความที่ปรากฏขึ้นเพื่อแจ้งให้ผู้ใช้ทราบถึงประเภทของข้อมูลที่จะป้อนลงในเซลล์อินพุต ตัวอย่างเช่น หากเซลล์ป้อนข้อมูลจะเต็มไปด้วยค่าอาหาร ให้แทนที่ "InputPrompt" ด้วย "ป้อนค่าใช้จ่ายทั้งหมดสำหรับค่าอาหาร รวมถึงทิป" (เครื่องหมายอัญประกาศสำหรับข้อความคำสั่งป้อนยังคงรวมอยู่ ในขณะที่ไม่จำเป็นต้องเขียนเครื่องหมายอัญประกาศก่อนและหลังคำสั่ง)

ขั้นตอนที่ 8 ป้อนรหัสสำหรับแต่ละฟิลด์การคำนวณ

อีกครั้ง สองบรรทัดที่ใช้ยังคงเหมือนเดิม แต่คราวนี้ ActiveCell. Value เป็นการคำนวณของฟังก์ชันตัวเลข เช่น SUM โดยที่ฟังก์ชัน InputBox จะใช้เพื่อแสดงคำแนะนำในการป้อนข้อมูล

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

รูปแบบคือ "ActiveWorkbook. SaveAs Filename:="Filename.xls" "Filename" คือชื่อของสเปรดชีตแบบโต้ตอบ (เครื่องหมายคำพูดก่อนและหลัง "Filename.xls" จะยังคงเขียนอยู่ ในขณะที่เครื่องหมายคำพูดสำหรับคำสั่งทั้งหมดจะเป็น ไม่ต้องการ.)

หากคุณใช้ Microsoft Excel เวอร์ชัน 2007 หรือใหม่กว่า ให้ใช้ ".xlsx" แทน ".xls" อย่างไรก็ตาม หากมีผู้ใช้สเปรดชีตเชิงโต้ตอบที่มีเวอร์ชัน Excel 2003 หรือต่ำกว่า พวกเขาจะไม่สามารถใช้สเปรดชีตได้หากไม่มีปลั๊กอินตัวอ่าน

ขั้นตอนที่ 10 กดปุ่ม alt=""Image" และ Q พร้อมกัน</h4" />
ตัวแก้ไข Visual Basic จะปิดลง

รายงานอัตโนมัติใน Excel ขั้นตอนที่ 11

ขั้นตอนที่ 11 กดปุ่ม alt=""Image" และ F8 พร้อมกัน</h4" />
กล่องโต้ตอบมาโครจะปรากฏขึ้น

รายงานอัตโนมัติใน Excel ขั้นตอนที่ 12

ขั้นตอนที่ 12. คลิกชื่อขั้นตอนในรายการมาโคร

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

ขั้นตอนที่ 13 คลิกปุ่มตัวเลือก

คุณจะถูกขอให้ป้อนอักขระบนแป้นพิมพ์เพื่อใช้เป็นทางลัดด้วยปุ่ม Ctrl เลือกตัวอักษรที่มีความหมายที่ไม่ได้ถูกใช้เป็นอักขระทางลัด เช่น "e" สำหรับ "entry"

ขั้นตอนที่ 14 คลิก "ตกลง" เพื่อปิดกล่องโต้ตอบตัวเลือกมาโคร

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

วิธีที่ 2 จาก 2: สร้างรายงานโดยอัตโนมัติ

ขั้นตอนที่ 1. สร้างรายงานใน PivotTable

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

ขั้นตอนที่ 2 เขียนสคริปต์ Visual Basic เพื่อเปิดและปิดรายงาน

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

เปิดสเปรดชีตในโหมดอ่านอย่างเดียว [DIM XLAppSet XLApp = CreateObject("Excel. App")xlapp.visible=falsexlapp.workbooks.open \excelloc\filename.xls, 3,]
โหลดข้อมูลซ้ำและบันทึกรายงาน ในตัวอย่างนี้ ข้อมูลจะถูกบันทึกเป็น PDF พร้อมคำบรรยายวันที่ [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, \pdfloc\reportname_ & DatePart("yyyy, Now()) & "-" & Right("0" & DatePart("m", Now()), 2) & "-" Right("0" & DatePart("d", Now()), 2) & ".pdf"] หากรูปแบบเอกสารที่ส่งออกแตกต่างกัน ให้แทนที่รูปแบบ ".pdf" ด้วย ส่วนต่อขยายที่เหมาะสมตามต้องการ
ปิดสเปรดชีตโดยไม่บันทึก จากนั้นปิด Excel [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
ใช้ ".xlsx" แทน ".xls" ที่ส่วนท้ายของสเปรดชีต ถ้าสเปรดชีตถูกบันทึกใน Excel 2007 และรูปแบบ XML ที่ใหม่กว่า

ขั้นตอนที่ 3 เขียนสคริปต์ชุดงานเพื่อเริ่มสคริปต์ Visual Basic

จุดประสงค์ในการเขียนสคริปต์คือเพื่อให้สคริปต์ Visual Basic ทำงานโดยอัตโนมัติ หากไม่มีแบตช์สคริปต์ สคริปต์ VB จะต้องทำงานด้วยตนเอง

สคริปต์เขียนในรูปแบบต่อไปนี้ แทนที่ด้วยชื่อโฟลเดอร์และชื่อไฟล์ของคุณเองสำหรับชื่อที่ระบุในตัวอย่างนี้: [cscript /nologo \fileloc\script.vbs]

ขั้นตอนที่ 4 เขียนแบตช์สคริปต์เพื่อให้แน่ใจว่าไฟล์เอาต์พุตมีอยู่ตามที่สร้างขึ้น

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

ตรวจสอบว่ามีไฟล์เอาต์พุตหรือไม่ [สำหรับ /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] หากรูปแบบไฟล์ที่ส่งออก ไม่ใช่ PDF ให้แทนที่ ".pdf" ด้วยนามสกุลที่เหมาะสม
หากมีไฟล์/รายงานที่ส่งออก ให้ส่งเป็นอีเมลไปยังบุคคลที่ต้องการ [ถ้ามี \pdfloc\%rreport% (sendmail -f [email protected] -t [email protected] -u Scheduled Report -m Report %%report% แนบมาด้วย -a \pdfloc\%rreport% -s yourserver:port -xu username -xp password)]
หากไม่มีไฟล์/รายงานเอาต์พุตในตำแหน่งที่ระบุ ให้สร้างขั้นตอนเพื่อให้สามารถส่งข้อความแจ้งว่าการส่งล้มเหลว [อื่น ๆ (sendmail -f [email protected] -t [email protected] -u Report ไม่ทำงาน -m file %rreport% ไม่มีอยู่ใน \pdfloc\ -s yourserver:port -xu username -xp password)]