نحوه خودکارسازی گزارشات در اکسل (همراه با تصاویر)

2024 نویسنده: Jason Gerald | [email protected]. آخرین اصلاح شده: 2024-01-19 22:12

مایکروسافت اکسل دارای ویژگی های مختلفی است و یکی از آنها تولید گزارش به صورت خودکار است. شما می توانید صفحات گسترده تعاملی ایجاد کنید تا روند ورود داده ها به دیگران را در کتاب کار ساده کرده و در عین حال تولید گزارش را نیز خودکار کنید. هر دوی این ویژگی ها نیاز به دانش کافی از ویژوال بیسیک دارند. مراحل انجام هر دو وظیفه در زیر شرح داده شده است.

گام

روش 1 از 2: ایجاد یک صفحه گسترده تعاملی

مرحله 1. طرح صفحه گسترده را تعیین کنید

طرح صفحه گسترده باید طوری انجام شود که دیگران بتوانند فیلدهای مورد نیاز برای وارد کردن داده ها را پیدا کنند.

طرح بندی صفحات گسترده را می توان به صورت افقی یا عمودی قرار داد. کار اکثر کاربران با چیدمان عمودی آسان تر است ، به ویژه اگر صفحه گسترده چاپ شود

مرحله 2. ایجاد برچسب های متنی برای صفحه گسترده

یک برچسب در بالای هر ستون و در سمت چپ هر سلول در ستونی که قصد دارید به عنوان ورودی داده استفاده کنید ، بنویسید.

مرحله 3. کلیدهای alt="Image" و F11 را با هم فشار دهید

این ترکیب کلید ویرایشگر Microsoft Visual Basic را باز می کند.

مرحله 4. روی "This Workbook" در قسمت "Project-VBA Project" در بالا سمت چپ دوبار کلیک کنید

پنجره ای برای نوشتن کد در قسمت اصلی ویرایشگر ظاهر می شود.

مرحله 5. از منوی Insert "رویه" را انتخاب کنید

کادر محاوره ای Add Procedure ظاهر می شود.

مرحله 6. نام رویه را در قسمت Name وارد کنید

اگر از صفحه گسترده برای گزارش هزینه های سفر استفاده می شود ، نام معناداری برای این روش وارد کنید ، مانند "SumExpenses". روی OK کلیک کنید تا کادر محاوره ای بسته شود.

نام رویه ها نمی تواند شامل فاصله باشد ، اما می تواند از خط زیر (_) برای جایگزینی فاصله ها استفاده کند.
پس از بسته شدن کادر محاوره ای Add Procedure ، یک خط با برچسب "Public Sub" ظاهر می شود که به دنبال آن نام رویه ذکر شده است. در زیر آن خط یک فاصله و کلمات "End Sub" قرار دارد.

مرحله 7. کد هر قسمت ورودی را در صفحه گسترده وارد کنید

برای هر ورودی دو خط کد می نویسید.

اولین خط کد به شکل "محدوده (" نام سلول ") است." را انتخاب کنید ، "نام سلول" نشان دهنده سلولی است که ورودی در آن وارد شده است. نام سلول را که بلافاصله در سمت راست برچسب متن قرار دارد ، پر کنید. اگر برچسب متن در سلول A2 باشد ، قسمت ورودی سلول B2 است (محدوده ("B2"). انتخاب کنید). نقل قول های قبل و بعد از نام سلول هنوز در این مرحله نوشته می شوند ، اما دیگر در دستور کد کامل گنجانده نشده اند.
کد در خط دوم "ActiveCell. Value = InputBox (" InputPrompt ")" است. "InputPrompt" متنی را نشان می دهد که ظاهر می شود نوع داده ای را که باید وارد سلول ورودی شود به کاربر اطلاع می دهد. به عنوان مثال ، اگر سلول ورودی با هزینه غذا پر می شود ، "InputPrompt" را با "کل هزینه های غذا ، از جمله نکات" را وارد کنید. (علامت های نقل قول برای متن دستورالعمل ورودی هنوز وجود دارد ، در حالی که نقل قول های قبل و بعد از دستور نیازی به نوشتن ندارند.)

مرحله 8. کد هر قسمت محاسبه را وارد کنید

دوباره دو خط استفاده شده مشابه قبل هستند ، اما این بار ActiveCell. Value محاسبه یک تابع عددی است ، برای مثال SUM ، که در آن تابع InputBox function برای نمایش دستورالعمل های ورودی استفاده می شود.

مرحله 9. برای ذخیره صفحه گسترده تعاملی خود ، یک خط کد اضافه کنید

فرمت "ActiveWorkbook. SaveAs Filename: =" Filename.xls "است." Filename "نام صفحه گسترده تعاملی است. (علامت های نقل قول قبل و بعد از" Filename.xls "همچنان نوشته می شوند ، در حالی که نقل قول برای همه دستورات لازم نیست.)

اگر از Microsoft Excel نسخه 2007 یا بالاتر استفاده می کنید ، به جای ".xls" از ".xlsx" استفاده کنید. با این حال ، اگر کاربران صفحه گسترده تعاملی با نسخه Excel 2003 و پایین تر وجود داشته باشند ، نمی توانند از صفحه گسترده بدون افزونه خواننده استفاده کنند

مرحله 10. کلیدهای alt="Image" و Q را همزمان فشار دهید

ویرایشگر ویژوال بیسیک بسته می شود.

مرحله 11. کلیدهای alt="Image" و F8 را همزمان فشار دهید

کادر محاوره ای ماکرو ظاهر می شود.

مرحله 12. روی نام رویه در لیست Macros کلیک کنید

اگر رویه ایجاد شده تنها روش موجود در لیست باشد ، به طور خودکار انتخاب می شود.

مرحله 13. روی دکمه Options کلیک کنید

از شما خواسته می شود که یک کاراکتر صفحه کلید را وارد کنید تا به عنوان میانبر با کلید Ctrl استفاده شود. یک حرف معنی دار که به عنوان یک میانبر استفاده نشده است انتخاب کنید ، مانند "e" برای "ورود".

مرحله 14. روی "تأیید" کلیک کنید تا گفتگوی گزینه های ماکرو بسته شود

اکنون می توانید صفحات گسترده تعاملی را بین کاربران توزیع کنید. پس از باز کردن آن ، کاربر می تواند از کلیدهای میانبر برای ایجاد ورودی استفاده کند و دستورالعمل هایی را که برای پر کردن داده ها ایجاد کرده اید دنبال کند.

روش 2 از 2: تولید گزارش خودکار

مرحله 1. یک گزارش در PivotTable ایجاد کنید

PivotTables برای خلاصه سازی داده ها طراحی شده است تا بتوانید اعداد را مقایسه کرده و روندها را مشخص کنید. جدول محوری باید به داده هایی مربوط باشد که در یکی از پردازنده های داده قرار دارند یا از پایگاه داده خاصی وارد شده اند.

مرحله 2. یک اسکریپت ویژوال بیسیک بنویسید تا گزارش باز و بسته شود

اسکریپت باید بتواند عملکردهای مختلف ذکر شده در زیر را انجام دهد. هر تابع شرح داده می شود و به دنبال آن کدی که در پرانتز برای پیاده سازی آن آمده است. هنگام نوشتن کد واقعی ، آن را در یک بلوک واحد بنویسید ، نام مثال را با نام خود جایگزین کنید و پرانتز را در ابتدا و انتهای نمونه کد وارد نکنید.

صفحه گسترده را در حالت فقط خواندنی باز کنید. [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]
در صورت ذخیره صفحه گسترده در Excel 2007 و فرمت بعدی XML ، از ".xlsx" به جای ".xls" در انتهای صفحه گسترده استفاده کنید.

مرحله 3. برای شروع اسکریپت ویژوال بیسیک ، یک اسکریپت دسته ای بنویسید

هدف از نوشتن اسکریپت این است که اسکریپت های ویژوال بیسیک به طور خودکار اجرا شوند. بدون اسکریپت دسته ای ، اسکریپت های VB باید به صورت دستی اجرا شوند.

اسکریپت با فرمت زیر نوشته شده است و نام پوشه و نام فایل خود را برای نام ذکر شده در این مثال جایگزین می کند: [cscript /nologo / fileloc / script.vbs]

مرحله 4. یک اسکریپت دسته ای بنویسید تا مطمئن شوید فایل خروجی به صورت ایجاد شده وجود دارد

اسکریپت شما باید عملکردهای زیر را انجام دهد. هر تابع با کد ارائه شده در پرانتز برای پیاده سازی دنبال می شود. هنگام نوشتن کد واقعی ، آن را در یک بلوک واحد بنویسید ، نام مورد نیاز را در مثال جایگزین کنید و پرانتز را در ابتدا و انتهای کد نمونه وارد نکنید.

بررسی کنید که آیا فایل خروجی وجود دارد یا خیر. [For /f "tokens = 2-4 delims = /" ٪٪ a in ('date /t') do rreport = reportname _ ٪٪ c-٪٪ a-٪٪ b.pdf)] اگر فرمت فایل خروجی PDF نیست ، ".pdf" را با پسوند مناسب جایگزین کنید.
اگر فایل خروجی/گزارش وجود دارد ، آن را به عنوان یک ایمیل برای شخصی که به آن نیاز دارد ارسال کنید. [در صورت وجود / pdfloc \٪ rreport٪ (sendmail -f [email protected] -t [email protected] -u گزارش برنامه ریزی شده -m گزارش ٪٪ گزارش٪ پیوست شده است. -a / pdfloc \٪ rreport٪ -s yourserver: port -xu نام کاربری -xp password)]
اگر فایل/گزارش خروجی در محل مشخص شده وجود ندارد ، رویه ای ایجاد کنید تا بتواند پیامی مبنی بر عدم تحویل به شما ارسال کند. [دیگر (sendmail -f [email protected] -t [email protected] -u گزارش اجرا نشد -m فایل٪ rreport٪ در / pdfloc / -s مالک سرور شما وجود ندارد: پورت -xu نام کاربری -xp گذرواژه)]