سبد خرید شما خالی است.
صدور فیش حقوقی در اکسل
فراخوانی اطلاعات و ایجاد فیش حقوقی
صدور فیش حقوقی یکی از پرکارتکرار ترین مسائل اکسلی هست. خیلی وقت ها افراد برای مجموعه خودشون میخوان فیش حقوقی مطابق با خواسته خودشون طراحی کنن. برای این کار هم نمیخوان از نرم افزارهای آماده استفاده کنن. طراحی یک فیش حقوقی با ویژگی ها و جزئیات دلخواه، براحتی در اکسل امکان پذیر هست. از این روش برای طراحی فاکتورها، فرم ها و … میشه استفاده کرد.
در این آموزش نحوه فرمول نویسی در فرم برای صدور فیش برای افراد مختلف و پرینت این فرم ها رو یاد میگیریم.
فرض کنید دیتابیسی مطابق شکل ۱ داریم که شامل اطلاعات حقوق افراد مختلف (میزان حقوق، مالیات ،بیمه و …) است.
شکل ۱ – دیتابیس داده های حقوق
حالا میخواهیم فیش حقوقی مطابق شکل ۲ داشته باشیم که با انتخاب کد ملی هر نفر، اطلاعات مربوطه را در جای خودش ثبت کنه.
شکل ۲- فرم خام طراحی شده برای فیش حقوقی
حالا میخواهیم در سلول I3 هر کد ملی که وارد کردیم، اطلاعات مربوطه از شیت دیتابیس در قسمت مناسب جایگذاری بشه. برای این کار از تابع Vlookup و ترکیبش با Match استفاده میکنیم. در این صورت تابع جلوی هر سلولی قرار بگیره، مقدار مربوطه با اون سلول رو از شیت دیتابیس فراخوانی میکنه.
نکته:
دقت داشته باشید که عناوین موجود در فیش حقوقی حتما باید با سرستون های دیتابیس عینا مشابه باشند در غیر اینصورت تابع Match خروجی صحیحی نخواهد داشت.
دقت داشته باشید که عناوین موجود در فیش حقوقی حتما باید با سرستون های دیتابیس عینا مشابه باشند در غیر اینصورت تابع Match خروجی صحیحی نخواهد داشت.
=VLOOKUP($I$3,’حقوق و دستمزد’!$A$1:$L$15,MATCH(فیش!J4,’حقوق و دستمزد’!$A$1:$L$1,0),0)
در این فرمول، سلول I3 در دیتابیس جستجو میشه و وقتی پیدا شد، داده ی مربوطه در شماره ستون مورد نظر (خروجی تابع Match ) رو بر میگردونه.
در انتها و در قسمت خالص دریافتی به حروف هم از افزونه ABH استفاده شده که مقدار خالص دریافتی رو به حروف تبدیل میکنه و برای اینکه عدد نهایی ،اعشار نداشته باشه که وقتی به حروف تبدیل میشه خیلی طولانی نشه، نتیجه نهایی رو با تابع Roundup گرد کردیم.
در مورد گرد کردن حتما مقاله مربوط به گرد کردن اعداد رو مطالعه کنید.
نکته:
گاهی اوقات مقداری که فراخوانی میشه از پهنای سلول بزرگتره و در سلول جا نمیشه و نیاز به تغییر اندازه فونت یا پهنای سلول وجود داره. برای اینکه این مشکل رو حل کنیم همه سلول های حاوی فرمول (که متغیر هستن) رو روی Shrink to fit تنظیم میکنیم. به این ترتیب دیگه مشکلی از این لحاظ نخواهیم داشت و در صورت نیاز، فونت سلول کوچک میشه و نیازی به تغییر دستی این اندازه ها نیست. ویدئو زیر نحوه کار رو تشریح میکنه.
گاهی اوقات مقداری که فراخوانی میشه از پهنای سلول بزرگتره و در سلول جا نمیشه و نیاز به تغییر اندازه فونت یا پهنای سلول وجود داره. برای اینکه این مشکل رو حل کنیم همه سلول های حاوی فرمول (که متغیر هستن) رو روی 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 در اون ذخیره بشن رو تعیین میکنه. پس با همین الگو، قسمت قرمز رنگ رو مطابق با سیستم و فولدر خودتون تغییر بدید.
ویدئو زیر برای تشریح نحوه کدنویسی ارائه شده.
در حال بارگذاری...
جهت دیدن جزئیات بیشتر و استفاده از فایل نهایی، فایل زیر رو دانلود کنید و آموزشها رو از روی اون مجدد مطالعه کنید.
دانلود فایل نمونه این آموزش
جهت دانلود این فایل روی دکمه زیر کلیک کنید:
درود بر شما
بسیار سپاسگزارم بخاطر این آموزش
مشکلی که من دارم این است که وقتی خروجی فایل PDF را به من می دهدد در محدوده خانه های مورد نظر نیست و بخش زیادی از صفحه سفید است و در گوشه ای از آن صفحه تنها قسمتی از فیش حقوقی مورد نظر را نشان می دهد. آیا راهی هست که تنظیمات پرینت فایل را انجام بدهیم که خروجی نهایی دقیقاً همان خانه ها و قسمتهایی از شیت باشد که مورد نظر است؟
باسپاس دوباره
رود بر شما
خودتون دقیقا جواب رو به درستی فرمودید
باید تنظیمات پرینت رو مطابق با فیش انجام بدید
که پی دی اف درست در بیاد
درود بر شما و تشکر از زحماتتون. واقعا سایت فوق العاده آموزنده ای دارید. میشه لطفا بفرمایید چرا پس از وارد کردن فرمول فوق در سلول مورد نظر در فیش حقوقی خروجی کد ملی نیست و عدد صفر نمایش داده می شود؟ کجای کارم اشتباه کرده ام؟
درود بر شما
فرمول و داده ها با هم باید بررسی بشه
یا جنس داده ها در دو طرف یک نیست
یا ارگومان اخر تابع رو ۰ نذاشتید
یا ترتیب داده ها متفاوته
سلام
وقت بخیر
اجرتون با خدا
فایل فرمول بندی شده قرارداد پرسنل میخواستم اگه دارین ممنون میشم برام ارسال بفرمایید
درود بر شما
منطق همین فایل فیش حقوقی هست
با مقدار یاعمال تغییرات در فایل میتونید برای قرار داد استفاده کنید
سلام و درودارسال نمیشود
درود بر شما
مشکل برطرف شد
مجدد چک بفرمایید
فقط لطفا فولدر اسپم رو هم بررسی کنید و اگر اسپم شده بود حتما روی Not Spam کلیک کنید
با سلام و خسته نباشید
می خواستم به جای ایجاد ۱ فایل pdf برای هر شخص،فیش کلیه افراد داخل یک فایل pdf باشه
درود
همین کار و میکنه!
فولدر ر مشخص کنید
برا هر فیش یک پی دی اف با شمارنده خودش میده
در نهایت کد ر و بررسی کنید
هر مدل میخواید تغییر میتونید بدید
ببخشید فکر کنم متوجه منظورم نشدید.
می خوام کلیه فیش های حقوقی داخل یک فایل pdf باشه.یعنی برای هر کس یک فایل ایجاد نکنه
بله درسته!
این کد این کار و نمیکنه
اما خیلی راحت بعدش میتونید پی دی اف ها رو مرج کنید
اگر هم که نه باید برید کد رو تغییر بدید که در اخر خودش این کار و بکنه
با سلام و احترام؛
ضمن عرض خسته نباشید، با توجه به اینکه از تابع VLOOKUP استفاده می شه و نیاز به یونیک بودن کد ملی هست در صورتی که در جدول اصلی تمام ماهها را ثبت کنیم در این صورت کد ملی تکرار شده و یونیک نیست برای این مواقع چیه پیشنهادی ارائه می فرمائید.
در.د بر شما
این مقاله رو مطالعه کنید
https://excelpedia.net/search-duplicates/
جستجوی موارد تکراری بعد با توجه به ماه انتخاب میکنید
یا جستجو رو بر اساس ترکیب ماه و کد ملی کنید و از توابع dfunction مثل dget استفاده کنید
سلام دوباره
خانم خاکزاد در اون فیش حقوقی بدون اینکه بریم بریم در محیط کد نویسی راهی است اون کد را در محیط اکسل با یک کلید اجرا کنیم . امید وارم متوجه شده باشید
این مقاله رو بخونید
https://excelpedia.net/excel-macro/
اون فایل نمونه را هرچی ایمل میدم که لینکش بیاد دانلود کنم نمیاد
اگر عضو سایت هستید، لاگ این کنید، مستقیم میتونید دانلود کنید
در غیر اینصورت احتمالا وارد فولدر Spam شما شده
نخیر عضو نیستم ولی از شب تاحالا چک کردم نیامده در فولدر spam
سلام
تشگر ازینگه این آموزشها را به اشتراک مگذارید.
بعد من این کدها را نوشتم ولی خروجی pdf نمیدهد .هرچند گوشش میکنم کد نویسی هم اشتباه نمیدهد .نمیدانم مشکل ور کجاست نصب office من هم درست است .
درود بر شما
از فایل نمونه استفاده کنید
میتونید کد رو مرحله به مرحله با کلید F8 اجرا کنید و دیباگ کنید تا متوجه بشید که مشکل کجاست
سلام چرا دانلود نمیشه؟
درود
اصلاح شد