طراحی فاکتور در اکسل: دانلود فرمول نویسی شده و رایگان

طراحی فاکتور در اکسل

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

پکیج آماده فاکتور در اکسل

قالب های آماده فاکتور در اکسل

مشاهده پکیج

مرحله اول: طراحی فاکتور

شکل ۱- فاکتور نمونه

برای طراحی فاکتور کافیه که مشخص کنیم فاکتور مورد نظر ما قراره چه مواردی رو داشته باشه و مثلا چند قلم کالا رو شامل بشه. مثلا فرض کنید فاکتوری مشابه شکل ۱ رو میخوایم در اکسل طراحی کنیم.

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

مرحله دوم: فرمول نویسی و محاسبات فاکتور در اکسل

حالا که فرم خام رو طراحی کردیم، باید تنظیماتی انجام بدیم، فرمول هایی بنویسیم که این فرم اتومات بشه و بصورت خودکار بتونه فاکتورهای دلخواه رو صادر کنه. پس در ادامه چندتا ویژگی برای طراحی فاکتور در اکسل رو براتون شرح میدیم.

ایجاد لیست داینامیک از محصولات

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

۱.کافیه که لیست محصولات رو به Table تبدیل کنیم.

۲.ستون نام محصول در فاکتور رو انتخاب میکنیم و از تب Data/Data Validation روی List کلیک میکنیم.

۳.فرمول مشابه زیر رو در قسمت Source تایپ میکنیم.

شکل ۲- ایجاد لیست کشویی از اسم محصولات

فراخوانی قیمت محصول انتخاب شده

حالا میخوایم با انتخاب هر محصول، قیمت آن از شیت محصولات، فراخوانی بشه و در ستون قیمت واحد قرار داده بشه. برای این کار کافیه با تابعی مثل vlookup نام محصول رو در دیتابیس اطلاعات محصول جستجو کرده و قیمت اونو فراخوانی کنیم. مشابه شکل ۳.

IFERROR(VLOOKUP(C9,Table1[#All],۲,۰),”-“)

برای اینکه برای سلول های خالی خطای NA() نده، از تابع Iferror  استفاده کردیم.

شکل ۳- فراخوانی قیمت هر محصول از شیت اطلاعات محصول

تفکیک قیمت کل رقم به رقم

حالا مرحله بعدی طراحی فاکتور در اکسل، محاسبه قیمت کل بصورت تفکیک شده است. که هر رقم رو در یک ستون قرار بده. برای این کار از تابع MID استفاده میکنیم. تابع MID محتوای یک سلول رو میتونه تفکیک کنه و هر بار یک کاراکتر رو به ما بده. اگر با تابع MID آشنایی ندارین حتما این مقاله رو مطالعه کنید. نکته ای که وجود داره اینه که صفحه راست به چپه و ما تفکیک اعداد رو از چپ به راست لازم داریم پس باید در تعیین شمارنده تابع MID حواسمون به این موضوع باشه. ما میخوایم مقدار ()تعداد * مبلغ(-تخفیف) در سلول های روبرو تفکیک بشه. اولین رقم بیاد اولین سلول سمت چپ و دومین رقم بیاد دومین سلول سمت چپ و … تعداد کل رقم ها ۹ تا است. یعنی مبالغ تا ۹۹۹۰۰۰۰۰۰ پشتیبانی میشن. برای این کار فرمول زیر رو در سلول K9 وارد میکنیم مطابق شکل ۴.

MID($H9*$I9-$J9,۱۰-COLUMN(A1),۱)

آرگومان دوم این تابع مشخص میکنه که تفکیک از چندمین کاراکتر شروع بشه. و چون ما عدد کاهشی لازم داریم، یعنی اول ۹، بعد ۸ و بعد ۷ و … برای همین عدد ۱۰ رو از عدد افزایشی تابع colmn(a1) کم کردیم. (همونطور که میدونید تابع column شماره ستون رفرنس ورودی رو به ما میده. پس column(A1) یعنی عدد ۱)

شکل ۴- تفکیک رقم نهایی در قسمت قیمت کل

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

IFERROR(MID($H9*$I9-$J9,10-COLUMN(A1),1),“”)

محاسبات مربوط به جمع کل فاکتور در اکسل

مرحله بعدی، تنظیمات مربوط به جمع نهایی فاکتور هست. هم جمع به عدد هم جمع به حروف. برای محاسبه مجموع به عدد، کافیه که ستون تعداد در ستون قیمت ضرب و از تخفیف کم بشه. یعنی با تابع sumproduct تعداد ر در قیمت واحد ضرب کنیم و بعد جمع کنیم و در نهایت کل مقادیر تخفیف ها رو از آن کم کنیم. پس فرمول مشابه زیر نوشته میشه:

SUMPRODUCT(H9:H23,I9:I23)-SUM(J9:J23)

شکل ۵- محاسبه جمع کل به عدد

برای محاسبه جمع کل به حروف هم کافیه افزونه ABH رو نصب کنیم که در این مقاله کامل توضیح دادیم و بعد از تابع مخصوص تبدیل عدد به حروف استفاده کنیم.

شکل ۶- محاسبه ردیف جمع کل به حروف

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

تنظیمات پرینت فاکتور

حالا که کار طراحی فاکتور در اکسل کامل شده، مرحله بعدی انجام تنظیمات پرینت برای این فاکتور هست که بعد از ورود داده بتونیم خیلی راحت پرینت بگیریم با به PDF تبدیل کنیم. برای انجام تنظیمات پرینت فقط کافیه که از تب page layout/ page setup از تب اول، جهت صفحه رو عمودی (Portrait) و scale رو fit to با ابعاد ۱ در ۱ انتخاب کنیم.

شکل ۶- تنظیمات پرینت فاکتور

۴ ترفند مهم جهت راحت تر کار کردن با فاکتور در اکسل

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

برای اینکه راحت تر بتونیم با فاکتور در اکسل کار کنیم و دیتا رو وارد کنیم، پیشنهاد میشه که یک سری ردیف ها رو freeze کنیم که فیکس بشه (مشابه شکل ۶) یعنی کاری کنیم که ۸ ردیف اول ثابت بمونن. برای این کار روی سلول C9 کلیک کرده و از تب view گزینه Freeze/ Freeze Pane رو انتخاب میکنیم. با این کار ۸ ردیف اول و ستون B و A ثابت میشن و راحت میتونیم به هر طرف اسکرول کنیم.

ترفند دوم: تنظیمات Shrink to fit

یه نکته کاربردی برای طراحی فاکتور در اکسل اینه که همه فیلدهای پرکردنی رو روی تنظیمات Shrink to fit قرار بدیم. این کار برای زمانی است که اگر دیتای وارد شده از اندازه سلول مشخص شده، بزرگتر بود، ظاهر فرم بهم نریزه و فونت کوچکتر نمایش داده بشه تا اون دیتا داخل سلول جا بگیره. برای این کار کافیه روی فیلدهایی مثل نام محصول، فروشنده و خریدار و … کلیک راست کرده و از Format Cell/ Alignment تیک گزینه Shrink to fit رو بزنیم. (شکل ۸)

شکل ۸- تنظیم shrink to fit برای فیلدهای پرکردنی

ترفند سوم: حرکت کردن بین فیلدها

برای اینکه بتونیم با زدن Enter به ترتیب روی فیلدهای پرکردنی حرکت کنیم مراحل زیر رو انجام میدیم: سلول های مورد نظر رو به ترتیب انتخاب میکنیم مثلا: تلفن ،کد اقتصادی، فروشگاه، توسط ،روز، ماه، سال، شرح کالا، تعداد و تخفیف (اولین ردیف همینطور به ترتیب تا آخرین ردیف)، تلفن بعد از قسمت Name Box یک نام تعیین میکنیم و Enter میزنیم. حالا هر بار خواستیم فرم رو پر کنیم، کافیه از Name Box اسم مورد نظر رو انتخاب کنیم و با زدن Enter بین فیلدهای دلخواه حرکت کنیم. این موضوع خیلی عملیه، اگر از رو نوشته متوجه نشدی، ویدئو زیر رو ببینید:

ترفند چهارم: قفل سلول های حاوی فرمول

هنگام طراحی فاکتور در اکسل سلول های حاوی فرمول رو قفل میکنیم که اشتباهی حذف نشن یا تغییر نکنن. برای این کار:

1. همه سلول ها رو انتخاب کرده و از قسمت format cell/Protection تیک گزینه Lock رو برمیداریم.
2. Go To/ Special/ Formula/ Ok رو میزنیم و مجدد از مسیر Format cell/ Protection تیک Lock رو میزنیم.
3. تب Review/ Protect Sheet رو میزنیم و پسورد مورد نظر رو تعیین میکنیم.

جهت درک بهتر میتونید ویدئو زیر رو هم مشاهده کنید.

دانلود نمونه فاکتور آماده در اکسل

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

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

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...