صدور فیش حقوقی در اکسل
فراخوانی اطلاعات و ایجاد فیش حقوقی
صدور فیش حقوقی یکی از پرکارتکرار ترین مسائل اکسلی هست. خیلی وقت ها افراد برای مجموعه خودشون میخوان فیش حقوقی مطابق با خواسته خودشون طراحی کنن. برای این کار هم نمیخوان از نرم افزارهای آماده استفاده کنن. طراحی یک فیش حقوقی با ویژگی ها و جزئیات دلخواه، براحتی در اکسل امکان پذیر هست. از این روش برای طراحی فاکتورها، فرم ها و … میشه استفاده کرد.
در این آموزش نحوه فرمول نویسی در فرم برای صدور فیش برای افراد مختلف و پرینت این فرم ها رو یاد میگیریم.
فرض کنید دیتابیسی مطابق شکل ۱ داریم که شامل اطلاعات حقوق افراد مختلف (میزان حقوق، مالیات ،بیمه و …) است.
شکل ۱ – دیتابیس داده های حقوق
حالا میخواهیم فیش حقوقی مطابق شکل ۲ داشته باشیم که با انتخاب کد ملی هر نفر، اطلاعات مربوطه را در جای خودش ثبت کنه.
شکل ۲- فرم خام طراحی شده برای فیش حقوقی
حالا میخواهیم در سلول I3 هر کد ملی که وارد کردیم، اطلاعات مربوطه از شیت دیتابیس در قسمت مناسب جایگذاری بشه. برای این کار از تابع Vlookup و ترکیبش با Match استفاده میکنیم. در این صورت تابع جلوی هر سلولی قرار بگیره، مقدار مربوطه با اون سلول رو از شیت دیتابیس فراخوانی میکنه.
دقت داشته باشید که عناوین موجود در فیش حقوقی حتما باید با سرستون های دیتابیس عینا مشابه باشند در غیر اینصورت تابع Match خروجی صحیحی نخواهد داشت.
=VLOOKUP($I$3,’حقوق و دستمزد’!$A$1:$L$15,MATCH(فیش!J4,’حقوق و دستمزد’!$A$1:$L$1,0),0)
در این فرمول، سلول I3 در دیتابیس جستجو میشه و وقتی پیدا شد، داده ی مربوطه در شماره ستون مورد نظر (خروجی تابع Match ) رو بر میگردونه.
در انتها و در قسمت خالص دریافتی به حروف هم از افزونه ABH استفاده شده که مقدار خالص دریافتی رو به حروف تبدیل میکنه و برای اینکه عدد نهایی ،اعشار نداشته باشه که وقتی به حروف تبدیل میشه خیلی طولانی نشه، نتیجه نهایی رو با تابع Roundup گرد کردیم.
در مورد گرد کردن حتما مقاله مربوط به گرد کردن اعداد رو مطالعه کنید.
گاهی اوقات مقداری که فراخوانی میشه از پهنای سلول بزرگتره و در سلول جا نمیشه و نیاز به تغییر اندازه فونت یا پهنای سلول وجود داره. برای اینکه این مشکل رو حل کنیم همه سلول های حاوی فرمول (که متغیر هستن) رو روی Shrink to fit تنظیم میکنیم. به این ترتیب دیگه مشکلی از این لحاظ نخواهیم داشت و در صورت نیاز، فونت سلول کوچک میشه و نیازی به تغییر دستی این اندازه ها نیست. ویدئو زیر نحوه کار رو تشریح میکنه.
پرینت فیش
حالا میخواهیم کاری کنیم که با زدن یک کلید، برای همه کدهای ملی موجود در دیتابیس، فیش حقوقی در قالب PDF ایجاد بشه.
برای این کار از کدنویسی VBA استفاده میکنیم. و حلقه For که بره و توی ستون کد ملی تک تک سلول ها رو بیاره، جایگزین کنه و شیت رو به PDF تبدیل و در یک فولدر با نام خودش ذخیره کنه.
کد زیر رو در یک ماژول کپی و اجرا میکنیم.
|
۱ ۲ ۳ ۴ ۵ ۶ ۷ ۸ ۹ ۱۰ ۱۱ ۱۲ |
Sub Print_to_File() Dim File_Address As String, Folder_Address As String Dim Persons_Numbers As Integer Dim ICounter As Integer Persons_Numbers = WorksheetFunction.CountA(Sheet1.Range("A:A")) - ۱ Folder_Address = "C:\Users\ExcelPedia\Desktop\Prints\ " For ICounter = ۱ To Persons_Numbers Sheet2.Range("I3") = Sheet1.Range("A1").Offset(ICounter, ۰) File_Address = Folder_Address & Sheet2.Range("I3") & ".pdf" Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=File_Address Next ICounter End Sub |
دقت داشته باشید در کد نوشته شده، مسیر ذخیره رو مطابق با سیستم خودتون تغییر بدید.
Folder_Address = “C:\Users\ExcelPedia\Desktop\Prints\“
در واقع متغیر Folder_Address متغیری هست که مسیر ذخیره فولدری که قراره فایل های PDF در اون ذخیره بشن رو تعیین میکنه. پس با همین الگو، قسمت قرمز رنگ رو مطابق با سیستم و فولدر خودتون تغییر بدید.
ویدئو زیر برای تشریح نحوه کدنویسی ارائه شده.
جهت دیدن جزئیات بیشتر و استفاده از فایل نهایی، فایل زیر رو دانلود کنید و آموزشها رو از روی اون مجدد مطالعه کنید.
دانلود فایل نمونه این آموزش
جهت دانلود این فایل روی دکمه زیر کلیک کنید:
سلام
با تشکر از اموزش خیلی خوب شما –متاسفانه در زمان نوشتن این پیام نتونستم از روش شما استفاده کنم و جواب نداد (احتمالا به علت مشکل جدول خودم ) –سوالی داشتم در اموزش ، جدول شما چپ چین است ، ایا از قصد ، اینکار انجام شده –چون شنیدم تابع loockup مشکلاتی در این خصوص دارد . من تازه کار هستم و دو هفته است که با روزی یک دوساعت شروع کردم .ایا امکان استفاده از این روش با تابع index و match است و همچنین با تابع xloockup که متناسب با جدول کناری خود فیلد خالی را پر کند – وجود دارد .
درود بر شما
نه چپ پین بودن اثری نداره
تابع vlookup رو به جلو عمل میکنه… حالا چه در راست به چپ و چه برعکس
سلام وقتتون بخیر من ۸۰۰۰ تا داده دارم میخوام یه تعداد مشخص رو کپی کنم میشه راهنمایی کنین
درود بر شما
خب قاعده انتخاب اون ی تعداد چیه؟
انتخاب کنید و کپی کنید !
احتمالا سر انتخاب مشکل داردی که باید بیشتر توضیح بدید
سلام
ممنون بابت این آموزش کاربردی
چگونه کد رو تغییر بدیم تا بجای تبدیل به pdf ، پرینت گرفته بشه ؟
درود بر شما
یک بار ماکرو پرینت رو ضبط کنید
مقایسه کنید کامل متوجه میشید
سلام وقت بخیر، قابل دانلود نیست متاسفانه راهی نداره که دانلود کنیم؟
سلام، وقت بخیر
متأسفانه به دلیل مشکلات و محدودیت های ایجاد شده، برخی از قسمت های سایت دچار مشکل شدن. علی ایحال طبق آزمایشی که کردیم، اگر در حساب کاربری لاگین کرده باشید و درخواست ارسال ایمیل رو بزنید، ایمیل ارسال میشه.
لطفا از این طریق اقدام کنید و اگر مشکل همچنان حل نشده بود بفرمائید مستقیم خدمتتون ارسال کنیم.
سلام متاسفانه فایل نمونه قابل دانلود نیست
درود
داخل سایت لاگ این کنید و دانلود کنید
متاسفانه افزونه فعلا مشکل داره
سلام و درود بر شما
سپاسگزار لطف و محبت شما در ارائه مطالب مفید و آموزنده هستم.
۱. من از VBA شما برای چاپ کارنامه دانش آموزان استفاده کردم. آیا امکان دارد در برگه A5 چاپ کرد؟ چه تغییراتی در کدنویسی لازم دارد؟
۲. از چه فرمولی باید استفاده کرد تا هنگام ورود نمرات در کاربرگ اکسل به جای عدد ۹۷، کلمه «غ موجه » ، نوشته شود؟
ممنون از راهنمایی شما.
درود
تنظیمات پرینت رو تغییر بدید نیاز به تغییر در کد نیست
۲- مورد دوم هم خب کافیه تایپ کنید
درود بر شما
بسیار سپاسگزارم بخاطر این آموزش
من اصلا نمیتونم اون بخش پرینت را اجرا کنم . روی کلید ماکرو و run را انتخاب میکنم ولی همش یک پیغام میدهد
Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=File_Address
باید چکار کنم ؟
ممنون از شما
درود بر شما
ادرس خودتون رو باید بدید
برید داخا محیط کد و اون قسمتی که مربوط به ادرس هست رو مطابق با مسیر دلخواه خودتون تغییر بدید
با سلام
ضمن عرض تشکر از زحمات شما و این آموزش بسیار کاربری. من یک سئوال داشتم: کلیه مراحل را همانند آموزش طی کردم و تغییرات مورد نیاز را هم اعمال کردم. تنها مشکلی که وجود دارد این است که فایل ها pdf که ایجاد میشوند قبل از کد ملی، کلمه print هم در اسم ایجاد شده وجود دارد. من ولی نیاز دارم که همانند آموزش شما، اسم فایل فقط کد ملی باشد.
لطفا راهنمائی نمائید.
با تشکر مجدد
درود بر شما
اون کدی که اسم فایل رو تولید میکنه چک کنید
ببینید چی اضافه شده
حذفش کنید
درود بر شما
بسیار سپاسگزارم بخاطر این آموزش
مشکلی که من دارم این است که وقتی خروجی فایل PDF را به من می دهدد در محدوده خانه های مورد نظر نیست و بخش زیادی از صفحه سفید است و در گوشه ای از آن صفحه تنها قسمتی از فیش حقوقی مورد نظر را نشان می دهد. آیا راهی هست که تنظیمات پرینت فایل را انجام بدهیم که خروجی نهایی دقیقاً همان خانه ها و قسمتهایی از شیت باشد که مورد نظر است؟
باسپاس دوباره
رود بر شما
خودتون دقیقا جواب رو به درستی فرمودید
باید تنظیمات پرینت رو مطابق با فیش انجام بدید
که پی دی اف درست در بیاد
درود بر شما و تشکر از زحماتتون. واقعا سایت فوق العاده آموزنده ای دارید. میشه لطفا بفرمایید چرا پس از وارد کردن فرمول فوق در سلول مورد نظر در فیش حقوقی خروجی کد ملی نیست و عدد صفر نمایش داده می شود؟ کجای کارم اشتباه کرده ام؟
درود بر شما
فرمول و داده ها با هم باید بررسی بشه
یا جنس داده ها در دو طرف یک نیست
یا ارگومان اخر تابع رو ۰ نذاشتید
یا ترتیب داده ها متفاوته
سلام
وقت بخیر
اجرتون با خدا
فایل فرمول بندی شده قرارداد پرسنل میخواستم اگه دارین ممنون میشم برام ارسال بفرمایید
درود بر شما
منطق همین فایل فیش حقوقی هست
با مقدار یاعمال تغییرات در فایل میتونید برای قرار داد استفاده کنید
سلام و درودارسال نمیشود
درود بر شما
مشکل برطرف شد
مجدد چک بفرمایید
فقط لطفا فولدر اسپم رو هم بررسی کنید و اگر اسپم شده بود حتما روی Not Spam کلیک کنید
با سلام و خسته نباشید
می خواستم به جای ایجاد ۱ فایل pdf برای هر شخص،فیش کلیه افراد داخل یک فایل pdf باشه
درود
همین کار و میکنه!
فولدر ر مشخص کنید
برا هر فیش یک پی دی اف با شمارنده خودش میده
در نهایت کد ر و بررسی کنید
هر مدل میخواید تغییر میتونید بدید
ببخشید فکر کنم متوجه منظورم نشدید.
می خوام کلیه فیش های حقوقی داخل یک فایل pdf باشه.یعنی برای هر کس یک فایل ایجاد نکنه
بله درسته!
این کد این کار و نمیکنه
اما خیلی راحت بعدش میتونید پی دی اف ها رو مرج کنید
اگر هم که نه باید برید کد رو تغییر بدید که در اخر خودش این کار و بکنه
با سلام و احترام؛
ضمن عرض خسته نباشید، با توجه به اینکه از تابع VLOOKUP استفاده می شه و نیاز به یونیک بودن کد ملی هست در صورتی که در جدول اصلی تمام ماهها را ثبت کنیم در این صورت کد ملی تکرار شده و یونیک نیست برای این مواقع چیه پیشنهادی ارائه می فرمائید.
در.د بر شما
این مقاله رو مطالعه کنید
https://excelpedia.net/search-duplicates/
جستجوی موارد تکراری بعد با توجه به ماه انتخاب میکنید
یا جستجو رو بر اساس ترکیب ماه و کد ملی کنید و از توابع dfunction مثل dget استفاده کنید
سلام دوباره
خانم خاکزاد در اون فیش حقوقی بدون اینکه بریم بریم در محیط کد نویسی راهی است اون کد را در محیط اکسل با یک کلید اجرا کنیم . امید وارم متوجه شده باشید
این مقاله رو بخونید
https://excelpedia.net/excel-macro/
اون فایل نمونه را هرچی ایمل میدم که لینکش بیاد دانلود کنم نمیاد
اگر عضو سایت هستید، لاگ این کنید، مستقیم میتونید دانلود کنید
در غیر اینصورت احتمالا وارد فولدر Spam شما شده
نخیر عضو نیستم ولی از شب تاحالا چک کردم نیامده در فولدر spam
سلام
تشگر ازینگه این آموزشها را به اشتراک مگذارید.
بعد من این کدها را نوشتم ولی خروجی pdf نمیدهد .هرچند گوشش میکنم کد نویسی هم اشتباه نمیدهد .نمیدانم مشکل ور کجاست نصب office من هم درست است .
درود بر شما
از فایل نمونه استفاده کنید
میتونید کد رو مرحله به مرحله با کلید F8 اجرا کنید و دیباگ کنید تا متوجه بشید که مشکل کجاست
سلام چرا دانلود نمیشه؟
درود
اصلاح شد