سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

صدور فیش حقوقی در اکسل

۳.۷/۵ - (۳ امتیاز)

فراخوانی اطلاعات و ایجاد فیش حقوقی

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

فرض کنید دیتابیسی مطابق شکل ۱ داریم که شامل اطلاعات حقوق افراد مختلف (میزان حقوق، مالیات ،بیمه و …) است.

نکته:
برای حل این مسئله از Mail Merge هم میشه استفاده کرد که میتونید در مقاله مربوطه مطالعه کنید

دیتابیس داده های حقوق

شکل ۱ – دیتابیس داده های حقوق

 حالا میخواهیم فیش حقوقی مطابق شکل ۲ داشته باشیم که با انتخاب کد ملی هر نفر، اطلاعات مربوطه را در جای خودش ثبت کنه.

فرم خام طراحی شده برای فیش حقوقی

شکل ۲- فرم خام طراحی شده برای فیش حقوقی

حالا میخواهیم در سلول 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 تبدیل و در یک فولدر با نام خودش ذخیره کنه.

کد زیر رو در یک ماژول کپی و اجرا میکنیم.

دقت داشته باشید در کد نوشته شده، مسیر ذخیره رو مطابق با سیستم خودتون تغییر بدید.

Folder_Address = “C:\Users\ExcelPedia\Desktop\Prints\

در واقع متغیر Folder_Address متغیری هست که مسیر ذخیره فولدری که قراره فایل های PDF در اون ذخیره بشن رو تعیین میکنه. پس با همین الگو، قسمت قرمز رنگ رو مطابق با سیستم و فولدر خودتون تغییر بدید.

ویدئو زیر برای تشریح نحوه کدنویسی ارائه شده.

در حال بارگذاری...

جهت دیدن جزئیات بیشتر و استفاده از فایل نهایی، فایل زیر رو دانلود کنید و آموزشها رو از روی اون مجدد مطالعه کنید.

دانلود فایل نمونه این آموزش

جهت دانلود این فایل روی دکمه زیر کلیک کنید:

129

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • مجید اسدی ۲۵ آبان ۱۴۰۱ / ۹:۲۵ ب٫ظ

    سلام وقت بخیر، قابل دانلود نیست متاسفانه راهی نداره که دانلود کنیم؟

    • سامان چراغی ۹ آذر ۱۴۰۱ / ۱۰:۲۱ ق٫ظ

      سلام، وقت بخیر
      متأسفانه به دلیل مشکلات و محدودیت های ایجاد شده، برخی از قسمت های سایت دچار مشکل شدن. علی ایحال طبق آزمایشی که کردیم، اگر در حساب کاربری لاگین کرده باشید و درخواست ارسال ایمیل رو بزنید، ایمیل ارسال میشه.
      لطفا از این طریق اقدام کنید و اگر مشکل همچنان حل نشده بود بفرمائید مستقیم خدمتتون ارسال کنیم.

  • رامتین پورغلامی ۱ آبان ۱۴۰۱ / ۱۰:۱۵ ق٫ظ

    سلام متاسفانه فایل نمونه قابل دانلود نیست

    • آواتار
      حسنا خاکزاد ۱۰ آبان ۱۴۰۱ / ۲:۱۷ ب٫ظ

      درود
      داخل سایت لاگ این کنید و دانلود کنید
      متاسفانه افزونه فعلا مشکل داره

  • ایرج عباس نژاد ۲۹ مرداد ۱۴۰۱ / ۳:۴۶ ب٫ظ

    سلام و درود بر شما
    سپاسگزار لطف و محبت شما در ارائه مطالب مفید و آموزنده هستم.
    ۱. من از VBA شما برای چاپ کارنامه دانش آموزان استفاده کردم. آیا امکان دارد در برگه A5 چاپ کرد؟ چه تغییراتی در کدنویسی لازم دارد؟
    ۲. از چه فرمولی باید استفاده کرد تا هنگام ورود نمرات در کاربرگ اکسل به جای عدد ۹۷، کلمه «غ موجه » ، نوشته شود؟
    ممنون از راهنمایی شما.

    • آواتار
      حسنا خاکزاد ۳۰ مرداد ۱۴۰۱ / ۱:۲۴ ب٫ظ

      درود
      تنظیمات پرینت رو تغییر بدید نیاز به تغییر در کد نیست

      ۲- مورد دوم هم خب کافیه تایپ کنید

  • نگین ۲۵ مرداد ۱۴۰۱ / ۱۰:۳۰ ق٫ظ

    درود بر شما
    بسیار سپاسگزارم بخاطر این آموزش
    من اصلا نمیتونم اون بخش پرینت را اجرا کنم . روی کلید ماکرو و run را انتخاب میکنم ولی همش یک پیغام میدهد
    Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=File_Address
    باید چکار کنم ؟
    ممنون از شما

    • آواتار
      حسنا خاکزاد ۲۵ مرداد ۱۴۰۱ / ۳:۵۰ ب٫ظ

      درود بر شما
      ادرس خودتون رو باید بدید
      برید داخا محیط کد و اون قسمتی که مربوط به ادرس هست رو مطابق با مسیر دلخواه خودتون تغییر بدید

  • رضائی ۲ مرداد ۱۴۰۱ / ۱۱:۴۸ ق٫ظ

    با سلام
    ضمن عرض تشکر از زحمات شما و این آموزش بسیار کاربری. من یک سئوال داشتم: کلیه مراحل را همانند آموزش طی کردم و تغییرات مورد نیاز را هم اعمال کردم. تنها مشکلی که وجود دارد این است که فایل ها pdf که ایجاد میشوند قبل از کد ملی، کلمه print هم در اسم ایجاد شده وجود دارد. من ولی نیاز دارم که همانند آموزش شما، اسم فایل فقط کد ملی باشد.
    لطفا راهنمائی نمائید.

    با تشکر مجدد

    • آواتار
      حسنا خاکزاد ۳ مرداد ۱۴۰۱ / ۹:۱۲ ق٫ظ

      درود بر شما
      اون کدی که اسم فایل رو تولید میکنه چک کنید
      ببینید چی اضافه شده
      حذفش کنید

  • مهدی پاک نیا ۶ اردیبهشت ۱۴۰۱ / ۱:۲۸ ق٫ظ

    درود بر شما
    بسیار سپاسگزارم بخاطر این آموزش
    مشکلی که من دارم این است که وقتی خروجی فایل PDF را به من می دهدد در محدوده خانه های مورد نظر نیست و بخش زیادی از صفحه سفید است و در گوشه ای از آن صفحه تنها قسمتی از فیش حقوقی مورد نظر را نشان می دهد. آیا راهی هست که تنظیمات پرینت فایل را انجام بدهیم که خروجی نهایی دقیقاً همان خانه ها و قسمتهایی از شیت باشد که مورد نظر است؟
    باسپاس دوباره

    • آواتار
      حسنا خاکزاد ۶ اردیبهشت ۱۴۰۱ / ۲:۲۸ ب٫ظ

      رود بر شما
      خودتون دقیقا جواب رو به درستی فرمودید
      باید تنظیمات پرینت رو مطابق با فیش انجام بدید
      که پی دی اف درست در بیاد

  • ولی اله احساسی ۲۸ فروردین ۱۴۰۱ / ۱۰:۵۷ ب٫ظ

    درود بر شما و تشکر از زحماتتون. واقعا سایت فوق العاده آموزنده ای دارید. میشه لطفا بفرمایید چرا پس از وارد کردن فرمول فوق در سلول مورد نظر در فیش حقوقی خروجی کد ملی نیست و عدد صفر نمایش داده می شود؟ کجای کارم اشتباه کرده ام؟

    • آواتار
      حسنا خاکزاد ۲۹ فروردین ۱۴۰۱ / ۲:۰۲ ب٫ظ

      درود بر شما
      فرمول و داده ها با هم باید بررسی بشه
      یا جنس داده ها در دو طرف یک نیست
      یا ارگومان اخر تابع رو ۰ نذاشتید
      یا ترتیب داده ها متفاوته

  • کیخسرو ۲۵ فروردین ۱۴۰۱ / ۸:۴۳ ق٫ظ

    سلام
    وقت بخیر
    اجرتون با خدا
    فایل فرمول بندی شده قرارداد پرسنل میخواستم اگه دارین ممنون میشم برام ارسال بفرمایید

    • آواتار
      حسنا خاکزاد ۲۶ فروردین ۱۴۰۱ / ۱۱:۴۸ ب٫ظ

      درود بر شما
      منطق همین فایل فیش حقوقی هست
      با مقدار یاعمال تغییرات در فایل میتونید برای قرار داد استفاده کنید

  • ali ۲ اسفند ۱۴۰۰ / ۵:۱۸ ب٫ظ

    سلام و درودارسال نمیشود

    • آواتار
      حسنا خاکزاد ۳ اسفند ۱۴۰۰ / ۹:۱۲ ق٫ظ

      درود بر شما
      مشکل برطرف شد
      مجدد چک بفرمایید
      فقط لطفا فولدر اسپم رو هم بررسی کنید و اگر اسپم شده بود حتما روی Not Spam کلیک کنید

  • navid ۲۱ بهمن ۱۴۰۰ / ۱۰:۴۱ ق٫ظ

    با سلام و خسته نباشید
    می خواستم به جای ایجاد ۱ فایل pdf برای هر شخص،فیش کلیه افراد داخل یک فایل pdf باشه

    • آواتار
      حسنا خاکزاد ۲۱ بهمن ۱۴۰۰ / ۱۱:۲۶ ق٫ظ

      درود
      همین کار و میکنه!
      فولدر ر مشخص کنید
      برا هر فیش یک پی دی اف با شمارنده خودش میده

      در نهایت کد ر و بررسی کنید
      هر مدل میخواید تغییر میتونید بدید

      • Navid ۲۱ بهمن ۱۴۰۰ / ۷:۱۰ ب٫ظ

        ببخشید فکر کنم متوجه منظورم نشدید.
        می خوام کلیه فیش های حقوقی داخل یک فایل pdf باشه.یعنی برای هر کس یک فایل ایجاد نکنه

        • آواتار
          حسنا خاکزاد ۲۱ بهمن ۱۴۰۰ / ۹:۵۵ ب٫ظ

          بله درسته!
          این کد این کار و نمیکنه
          اما خیلی راحت بعدش میتونید پی دی اف ها رو مرج کنید
          اگر هم که نه باید برید کد رو تغییر بدید که در اخر خودش این کار و بکنه

  • امیرهوشنگ فرمانی ۲ بهمن ۱۴۰۰ / ۱:۵۲ ب٫ظ

    با سلام و احترام؛
    ضمن عرض خسته نباشید، با توجه به اینکه از تابع VLOOKUP استفاده می شه و نیاز به یونیک بودن کد ملی هست در صورتی که در جدول اصلی تمام ماهها را ثبت کنیم در این صورت کد ملی تکرار شده و یونیک نیست برای این مواقع چیه پیشنهادی ارائه می فرمائید.

    • آواتار
      حسنا خاکزاد ۲ بهمن ۱۴۰۰ / ۸:۰۰ ب٫ظ

      در.د بر شما
      این مقاله رو مطالعه کنید
      https://excelpedia.net/search-duplicates/
      جستجوی موارد تکراری بعد با توجه به ماه انتخاب میکنید

      یا جستجو رو بر اساس ترکیب ماه و کد ملی کنید و از توابع dfunction مثل dget استفاده کنید

  • Ali Ghulami ۲۶ آبان ۱۴۰۰ / ۷:۳۰ ق٫ظ

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

  • Ali Ghulami ۲۵ آبان ۱۴۰۰ / ۳:۱۹ ب٫ظ

    اون فایل نمونه را هرچی ایمل میدم که لینکش بیاد دانلود کنم نمیاد

    • آواتار
      حسنا خاکزاد ۲۵ آبان ۱۴۰۰ / ۸:۴۸ ب٫ظ

      اگر عضو سایت هستید، لاگ این کنید، مستقیم میتونید دانلود کنید
      در غیر اینصورت احتمالا وارد فولدر Spam شما شده

      • Ali Ghulami ۲۶ آبان ۱۴۰۰ / ۷:۱۰ ق٫ظ

        نخیر عضو نیستم ولی از شب تاحالا چک کردم نیامده در فولدر spam

  • Ali Ghulami ۲۵ آبان ۱۴۰۰ / ۳:۱۶ ب٫ظ

    سلام
    تشگر ازینگه این آموزشها را به اشتراک مگذارید.
    بعد من این کدها را نوشتم ولی خروجی pdf نمیدهد .هرچند گوشش میکنم کد نویسی هم اشتباه نمیدهد .نمیدانم مشکل ور کجاست نصب office من هم درست است .

    • آواتار
      حسنا خاکزاد ۲۵ آبان ۱۴۰۰ / ۸:۴۹ ب٫ظ

      درود بر شما
      از فایل نمونه استفاده کنید
      میتونید کد رو مرحله به مرحله با کلید F8 اجرا کنید و دیباگ کنید تا متوجه بشید که مشکل کجاست

  • سهند مالکی ۲۷ شهریور ۱۴۰۰ / ۱۰:۳۹ ق٫ظ

    سلام چرا دانلود نمیشه؟

    • آواتار
      حسنا خاکزاد ۲۹ شهریور ۱۴۰۰ / ۱۱:۰۱ ق٫ظ

      درود
      اصلاح شد

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان