آموزش ایجاد سیستم مدیریت انبار در اکسل
این پست از مجموعه آموزش‌های اکسل هست که به آموزش پیاده سازی سیستم مدیریت انبار در اکسل می‌پردازد. در صورتی که تمایل دارید بقیه آموزش‌های مرتبط با اکسل را ببینید، از این لینک استفاده کنید.
با یادگیری مطالب این پست، قادر خواهید بود که یک سیستم مدیریت انبار با قابلیت‌های کنترل موجودی کالاهای مختلف، کنترل نقاط سفارش کالاهای مختلف و کنترل دسترسی در اکسل ایجاد کنید. در ضمن اگه حال و حوصله خوندن متن ندارید، از ویدیو آخر این پست استفاده کنید.
پیشنیاز این آموزش، آشنایی با اکسل مخصوصا ایجاد جداول و فرمول نویسی ستون‌ها در حد خوب هست.
قدم اول: موجودی اول دوره
خب بریم سراغ ایجاد این سیستم، اکسل را باز می‌کنم و یک شیت (Datasheet) جدید به نام موجودی اول دوره ایجاد می‌کنم (یا شیت فعلی را تغییر نام میدم) و دیتای اولیه را داخلش می‌نویسم. دیتا من شامل ستون‌های کد کالا، نام، موجودی اولیه، قیمت، ارزش کلی و نقطه سفارش هست.
موجودی اول دوره کالاها می‌تونه همون موجودی آخر دوره قبلی باشه یا موجودی حاصل از شمارش کالاها در اول دوره باشه. منظورم از دوره هم، دوره انبارداری است، این دوره براساس سیاست‌های سازمان شما مشخص میشه، مثلا شاید شما هر سه ماه یکبار انبار گردانی انجام میدید، پس ممکنه این دوره را سه ماهه در نظر بگیرید.
وقتی دیتا خودم را وارد اکسل کردم، باید اون را تبدیل به جدول (Table) کنم، برای همین روی یکی از سلول‌های دیتا کلید‌های کنترل و T را (Ctrl+T) می‌زنم تا دیتا تبدیل به جدول بشه. بعد از تب Table Design اسم جدول خودم را initial قرار میدم:
ستون ارزش کلی هم که مشخصه از ضرب موجودی اولیه در قیمت با فرمول زیر بدست اومده:
= [@[موجودی اولیه]]*[@قیمت]
قدم دوم: کالاهای ورودی
برای کنترل کالاهای ورودی به انبار در یک شیت جدید به نام ورودی، جدولی به نام input با ستون‌های ردیف، کد کالا، نام، ورودی و تاریخ ایجاد کردم که منظورم از نام همون نام کالا و منظور از ورودی، تعداد کالای ورودی است و داده‌های مربوط به کالاهای ورودی را در این جدول نوشته‌ام:
نکته مهم این جدول اینه که من نام کالا را خودم دستی ننوشتم و این نام با توجه به کد کالا از جدول initial خونده میشه. این کار وقتی که تعداد کد کالاها زیاده، به جلوگیری از ثبت اشتباه کالای ورودی، کمک بزرگی میکنه. برای این کار از تابع VLOOKUP استفاده کرده‌ام:
= VLOOKUP([@[کد کالا]], initial, 2,FALSE)
همچنین شاید شما نیاز داشته باشید که ستون ردیف هم به صورت خودکار آپدیت شود، یعنی وقتی کاربر یک سطر جدید ایجاد میکنه، خود اکسل شماره ردیف جدید را در سلول مربوطه بنویسه. برای اینکار فرمول زیر را در اولین سلول از ستون ردیف بنویسید:
= ROW() -1
قدم سوم: کالاهای خروجی
برای کنترل کالاهای خروجی از انبار در یک شیت جدید به نام خروجی، جدولی به نام output مشابه با جدول ورودی ایجاد کرده‌ام، فقط با این تفاوت که به جای ستون ورودی، ستون خروجی دارم:
قدم چهارم: موجودی فعلی
در یک شیت جدید، جدولی برای محاسبه موجودی فعلی با ستون‌های ردیف، کد کالا، نام، موجودی اولیه، خروجی، ورودی، موجودی نهایی، قیمت و ارزش کل ایجاد می‌کنم:
اگه قصد داشته باشم که ردیف‌های خودم خودکار ایجاد بشه، اینجام هم می‌تونم از فرمول زیر در ستون ردیف استفاده کنم:
= ROW() -1
برای وارد شدن خودکار ستون‌های نام، موجودی اولیه و قیمت از جدول initial هم به ترتیب از فرمول‌های زیر استفاده می‌کنم:
= VLOOKUP([@[کد کالا]],initial,2,FALSE)
= VLOOKUP([@[کد کالا]],initial,3,FALSE)
= VLOOKUP([@[کد کالا]],initial,4,FALSE)
با نوشتن فرمول‌های بالا در ستون‌های مربوطه، بعد از وارد کردن کد کالا این سه ستون به صورت خودکار آپدیت می‌شوند.
برای محاسبه ستون خروجی باید از تابع SUMIF استفاده کنم:
= SUMIF(output[کد کالا],[@[کد کالا]], output[خروجی])
منطق این فرمول این شکلیه که میره در جدول output اون کد کالاهایی که برابر با کد کالا سطر فعلی جدول موجودی نهایی هست را پیدا میکنه و ستون خروجی اون‌ها را جمع میکنه.
برای محاسبه ورودی هم عملیات بالا را روی جدول input انجام میدم:
= SUMIF(input[کد کالا],[@[کد کالا]], input[ورودی])
برای محاسبه ستون موجودی نهایی باید مقدار موجودی اولیه را از مقدار خروجی کم کنیم و حاصل را با ورودی جمع کنیم:
= [@[موجودی اولیه]]-[@خروجی]+[@ورودی]
قدم پنجم: مدیریت نقطه سفارش
در این مرحله لازم است، کالاهایی که به نقطه سفارش خود رسیده‌اند و نیاز به سفارش دارند، مشخص شوند. برای همین کنار ستون موجودی فعلی در جدول اخیر، یک ستون جدید به نام سفارش؟ ایجاد می‌کنم و فرمول زیر را برای این ستون می‌نویسم:
= IF(VLOOKUP(@[کد کالا],initial,6,FALSE) >= @[موجودی نهایی], "خیر" , "بله")
این فرمول، کد کالای هر سطر از جدول را در نظر می‌گیره و نقطه سفارش مربوط به آن کد کالا را از جدول initial با استفاده از تابع VLOOKUP پیدا میکنه، بعدش اگه نقطه سفارش از موجودی نهایی بزرگتر باشه، عبارت بله و در غیر اینصورت عبارت خیر را نمایش میده. توجه کنید که بزرگتر بودن نقطه سفارش از موجودی نهایی به معنای این است که کالا نیاز به سفارش گذاری دارد.
الان من یک ستونی دارم که کالاهایی که نیاز به سفارش دارند را با بله مشخص میکنه و کالاهایی که هنوز به نقطه سفارش خود نرسیده‌اند را با خیر مشخص میکنه و با یک فیلتر ساده می‌تونم در هر لحظه متوجه بشم که کدوم کالاها را باید سفارش بدم. علاوه بر این با امکان Conditional Formatting اکسل می‌تونم اون کالاهایی که نیاز به سفارش دارند را با رنگ دیگه‌ای نمایش بدم:
قدم ششم: کنترل دسترسی
فرض کنید من قراره فایل اکسلی که ایجاد کردم را در اختیار بقیه افراد هم قرار بدم و قصد دارم که کسی به جز خودم نتونه تغییری در شیت‌های موجودی اولیه یا موجودی نهایی ایجاد کنه، یا به عبارت دیگه افراد دیگه فقط قادر باشند ورودی یا خروجی ثبت کنند. برای این کار من دو تا گزینه دارم که می‌تونم هر دو را همزمان اجرا کنم:
قفل کامل شیت
در این حالت شیت من کامل قفل میشه و فقط افرادی که رمز عبور دارند قادر هستند در شیت من تغییری ایجاد کنند. برای این کار من به شیت موجودی اول دوره میرم و از تب review روی گزینه protect sheet کلیک می‌کنم. در قسمت Password to unprotect sheet عبارتی به عنوان پسورد وارد می‌کنم و OK می‌زنم. در پنجره بعدی دوباره همان پسورد را وارد می‌کنم و OK می‌زنم. الان شیت من قفل شده و فقط افرادی که پسورد این شیت را دارند قادر به انجام تغییر در این شیت هستند. از این گزینه برای شیت‌های موجودی اول دوره و موجودی نهایی استفاده می‌کنم:
قفل قسمتی از شیت
فرض کنید من قصد دارم شیت مربوط به ثبت ورودی‌ها را هم قفل کنم ولی فقط ستون ورودی و کد کالا را باز بگذارم تا افرادی بتونن داخل این ستون‌ها دیتا وارد کنند. برای همین این دو تا ستون را انتخاب می‌کنم و روی یکی از آن‌ها کلیک راست می‌کنم و روی Format cells کلیک می‌کنم. در پنجره باز شده از تب Protection گزینه Locked را غیر فعال می‌کنم و OK می‌زنم. الان دقیقا کاری که برای قفل کامل شیت انجام دادم را انجام میدم تا شیت قفل بشه. بعد از انجام این عملیات همه ستون‌های شیت به جز ستون‌های کد کالا و ورودی برای بقیه افراد قفل میشه. از این گزینه برای شیت‌های ورودی و خروجی استفاده می‌کنم:
ویدیو آموزش مدیریت انبار در اکسل
من به جز اینجا، ویدیوها را در چنل یوتوب خودم هم آپلود می‌کنم. خیلی خوشحال میشم اگه اونجا هم من را همراهی کنید. برای دیدن چنل یوتوب من روی این لینک کلیک کنید.