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

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

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

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

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

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

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

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

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

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

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

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

1. کافیه که لیست محصولات رو به Table تبدیل کنیم.
2. ستون نام محصول در فاکتور رو انتخاب میکنیم و از تب Data/Data Validation روی List کلیک میکنیم.
3. فرمول مشابه زیر رو در قسمت 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 قرار بدیم. این کار برای زمانی است که اگر دیتای وارد شده از اندازه سلول مشخص شده، بزرگتر بود، ظاهر فرم بهم نریزه و فونت کوچکتر نمایش داده بشه تا اون دیتا داخل سلول جا بگیره. برای این کار کافیه روی فیلدهایی مثل نام محصول، فروشنده و خریدار و … کلیک راست کرده و از 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 رو میزنیم و پسورد مورد نظر رو تعیین میکنیم.

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

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

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

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

آدرس ایمیل

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

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