انبارداری با اکسل برای کسبوکارهای کوچک، فروشگاه های کم تراکنش و مجموعه هایی که تعداد محدودی کالا دارند، راهکاری ساده و کمهزینه است. در یک فایل اکسل میتوان مشخصات کالاها، ورود و خروج، موجودی فعلی، ارزش ریالی انبار و نقطه سفارش را ثبت کرد. با این حال، دقت این روش کاملاً به ساختار فایل و ثبت منظم اطلاعات وابسته است.
فهرست مطالب
یک فایل نامنظم که همه اطلاعات را در یک شیت و بدون کدگذاری مشخص نگهداری میکند، بهمرور به منبع خطا تبدیل میشود. به همین دلیل، در این آموزش انبارداری با اکسل فقط به معرفی چند تابع اکتفا نمیکنیم و مراحل ساخت یک فایل قابل استفاده برای کنترل موجودی را بررسی میکنیم.
برای آشنایی با عملیات و اصطلاحات پایه این حوزه، مطالعه مقاله انبارداری چیست میتواند پیش از شروع این آموزش مفید باشد.
انبارداری با اکسل چیست و برای چه کسبوکارهایی مناسب است؟
انبارداری با اکسل یعنی ثبت و کنترل اطلاعات کالاها در یک یا چند صفحه گسترده. در این روش، ورود کالا، خروج کالا، موجودی اول دوره، مانده فعلی، قیمت خرید، ارزش موجودی و حد سفارش با استفاده از جدولها و فرمولهای اکسل محاسبه میشوند.
این روش معمولاً برای کسبوکارهایی مناسب است که:
- تعداد کالاهای محدودی دارند.
- روزانه تراکنش زیادی ثبت نمیکنند.
- فقط یک انبار یا محل نگهداری دارند.
- یک یا دو نفر اطلاعات را ثبت میکنند.
- هنوز به اتصال خودکار فروش، خرید و حسابداری نیاز ندارند.
- بودجه یا نیاز کافی برای تهیه سیستم مستقل ندارند.
اکسل میتواند محاسبات را بلافاصله انجام دهد، اما ورود و خروج کالا را بهصورت خودکار تشخیص نمیدهد. مگر اینکه فایل به سیستم فروش، بارکدخوان یا منبع اطلاعاتی دیگری متصل شده باشد، هر تراکنش باید توسط کاربر ثبت شود. بنابراین عبارت «موجودی لحظهای» در اکسل زمانی درست است که همه ورودها و خروجها بدون تأخیر وارد شده باشند.
فایل انبارداری با اکسل چه بخشهایی دارد؟
حساب و کتاب خود را محک بزنید
نرم افزار حسابداری محک ویژه +200 گروه شغلی
دریافت دمو و مشاوره رایگان نرم افزار حسابداری محک
در صورت تمایل به دریافت مشاوره رایگان و مشاهده دمو نرم افزار حسابداری محک، لطفا فرم زیر را تکمیل نمایید.
برای ساخت یک برنامه انبارداری ساده با اکسل بهتر است اطلاعات در چند شیت جداگانه نگهداری شوند. قرار دادن همه اطلاعات در یک صفحه، کنترل فایل را دشوار میکند و احتمال تغییر ناخواسته فرمولها را افزایش میدهد.
| نام شیت | اطلاعات پیشنهادی |
| کالاها | کد کالا، نام کالا، گروه، واحد، بهای واحد و نقطه سفارش |
| گردش کالا | تاریخ، شماره سند، کد کالا، ورود، خروج و شرح عملیات |
| موجودی | موجودی اول دوره، مجموع ورود، مجموع خروج و مانده فعلی |
| گزارشها | گزارش موجودی، کالاهای کمموجودی و ارزش ریالی انبار |
| تنظیمات | فهرست انبارها، واحدها، گروه کالا و انواع سند |
در فایلهای بسیار ساده میتوان شیت موجودی و گردش کالا را ترکیب کرد. با این حال، جدا کردن اطلاعات ثابت کالا از تراکنشها باعث میشود گزارشگیری، جستوجو و اصلاح اطلاعات راحتتر باشد.
چه ستونهایی در جدول انبارداری لازم است؟
ستونهای موردنیاز به نوع کسبوکار بستگی دارند، اما یک فایل استاندارد بهتر است حداقل اطلاعات زیر را پوشش دهد:
| ستون | کاربرد |
| کد کالا | شناسایی یکتای هر کالا |
| نام کالا | عنوان قابل مشاهده کالا |
| گروه کالا | دستهبندی کالاها |
| واحد اندازهگیری | عدد، کیلوگرم، متر، بسته و موارد مشابه |
| نام انبار | مشخص کردن محل نگهداری |
| تاریخ عملیات | تاریخ ورود یا خروج |
| شماره سند | شماره رسید، حواله یا فاکتور |
| نوع عملیات | ورود، خروج، برگشت یا انتقال |
| مقدار ورودی | تعداد کالای واردشده |
| مقدار خروجی | تعداد کالای خارجشده |
| موجودی فعلی | مانده کالا پس از هر عملیات |
| بهای واحد | قیمت خرید یا بهای ثبتشده |
| ارزش موجودی | موجودی فعلی ضربدر بهای واحد |
| نقطه سفارش | سطح موجودی نیازمند خرید مجدد |
| محل نگهداری | قفسه، راهرو یا موقعیت کالا |
| تاریخ انقضا | برای کالاهای تاریخدار |
| توضیحات | اطلاعات تکمیلی تراکنش |
برای هر کالا یک کد یکتا تعریف کنید. استفاده از نام کالا بهتنهایی کافی نیست؛ زیرا ممکن است یک کالا با چند املای متفاوت ثبت شود. برای مثال «کابل یک متری»، «کابل ۱ متر» و «کابل یکمتری» ممکن است در گزارش بهعنوان سه کالای جداگانه شناخته شوند.
اگر به ثبت تاریخچه کامل گردش هر کالا نیاز دارید، مقاله کاردکس انبار نحوه ثبت ورود، خروج و مانده کالا را با جزئیات بیشتری توضیح میدهد.
آموزش ساخت فایل انبارداری با اکسل
۱. شیت اطلاعات کالا را بسازید
در شیت اول ستونهای کد کالا، نام، گروه، واحد اندازهگیری، نقطه سفارش و بهای واحد را ایجاد کنید. هر سطر باید فقط به یک کالا اختصاص داشته باشد.
محدوده اطلاعات را انتخاب کنید و با کلیدهای Ctrl + T آن را به Table تبدیل کنید. جدولهای اکسل هنگام اضافه شدن ردیف جدید، فرمولها و قالببندی را بهصورت منظم ادامه میدهند.
یک نام مشخص مانند tblProducts برای جدول کالاها انتخاب کنید.
۲. شیت گردش کالا را ایجاد کنید
در شیت گردش کالا، هر ورود یا خروج باید در یک ردیف مستقل ثبت شود. یک ردیف را به چند عملیات اختصاص ندهید.
ستونهای اصلی این شیت میتوانند شامل موارد زیر باشند:
- تاریخ
- شماره سند
- کد کالا
- نام کالا
- نوع عملیات
- ورودی
- خروجی
- بهای واحد
- شرح
ورود کالا باید براساس رسید و خروج کالا براساس حواله، فاکتور یا سند داخلی ثبت شود. برای آشنایی بیشتر با رسید، حواله و ثبت اسناد انبار میتوانید راهنمای حسابداری انبار را مطالعه کنید.
۳. برای کد کالا فهرست کشویی بسازید
برای جلوگیری از ثبت کدهای نادرست، از Data Validation استفاده کنید:
- ستون کد کالا را انتخاب کنید.
- وارد بخش Data شوید.
- گزینه Data Validation را انتخاب کنید.
- نوع داده را روی List قرار دهید.
- محدوده کدهای موجود در شیت کالاها را بهعنوان منبع تعیین کنید.
با این کار، کاربر بهجای تایپ دستی، کد کالا را از فهرست انتخاب میکند.
۴. نام کالا را براساس کد نمایش دهید
در نسخههای جدید اکسل میتوان از XLOOKUP استفاده کرد:
این فرمول کد انتخابشده را در جدول کالاها پیدا میکند و نام همان کالا را نمایش میدهد.
در نسخههای قدیمیتر اکسل میتوان از VLOOKUP یا ترکیب INDEX و MATCH استفاده کرد.
۵. موجودی هر کالا را محاسبه کنید
موجودی فعلی = موجودی اول دوره + مجموع ورود − مجموع خروج
اگر در یک جدول خلاصه برای هر کالا یک ردیف دارید، میتوانید از SUMIFS استفاده کنید:
این فرمول تمام ورودیها و خروجیهای مربوط به کد کالا را جمع میکند و مانده را نمایش میدهد.
در بعضی تنظیمات اکسل، جداکننده آرگومانهای فرمول بهجای ویرگول، نقطهویرگول است.
فرمولهای کاربردی انبارداری در اکسل
تابع SUM برای جمع ورود یا خروج
تابع SUM برای جمع یک محدوده استفاده میشود:
برای مثال، اگر ستون D مقدار ورود کالا را نشان دهد، این فرمول مجموع ورودیهای ثبتشده را محاسبه میکند.
ضرب دو سلول تابع SUM نیست. برای ضرب موجودی در بهای واحد باید از فرمول ساده ضرب استفاده شود:
تابع SUMIFS برای محاسبه گردش یک کالا
اگر بخواهید فقط ورودیهای مربوط به یک کد کالا را جمع کنید، SUMIFS مناسبتر است:
این تابع فقط ردیفهایی را جمع میکند که کد کالای آنها با مقدار سلول A2 برابر باشد.
تابع IF برای هشدار موجودی کم
برای مقایسه موجودی فعلی با نقطه سفارش میتوان از IF استفاده کرد:
در این مثال، F2 موجودی فعلی و H2 نقطه سفارش است.
تابع XLOOKUP برای دریافت مشخصات کالا
با انتخاب کد کالا میتوان نام، واحد یا قیمت آن را از جدول اصلی دریافت کرد:
تابع SORT برای مرتبسازی گزارش
در نسخههای جدید اکسل میتوانید گزارش کالاها را براساس موجودی مرتب کنید:
عدد ۶ نشان میدهد مرتبسازی براساس ستون ششم انجام شود و عدد ۱ ترتیب صعودی را مشخص میکند. با این روش کالاهای دارای موجودی کمتر در بالای گزارش دیده میشوند.
تابع RANK.EQ برای رتبهبندی کالاها
اگر بخواهید کالاها را براساس فروش، مصرف یا ارزش موجودی رتبهبندی کنید، از RANK.EQ استفاده کنید:
عدد صفر رتبهبندی از بیشترین به کمترین مقدار را مشخص میکند. این تابع برای شناسایی کالاهای پرفروش یا پرمصرف مفید است، اما برای محاسبه موجودی ضروری نیست.
محاسبه نقطه سفارش و هشدار کمبود موجودی
نقطه سفارش مقداری از موجودی است که با رسیدن کالا به آن، باید فرایند خرید مجدد آغاز شود. یک روش ساده برای محاسبه آن عبارت است از:
نقطه سفارش = مصرف روزانه × زمان تأمین + موجودی اطمینان
فرض کنید یک کالا روزانه بهطور میانگین ۴ عدد مصرف میشود، تأمین آن ۷ روز زمان میبرد و میخواهید ۱۰ عدد موجودی اطمینان نگه دارید:
۴ × ۷ + ۱۰ = ۳۸
در این شرایط، وقتی موجودی به ۳۸ عدد برسد، باید سفارش خرید بررسی شود.
برای نمایش هشدار بصری میتوانید از Conditional Formatting استفاده کنید:
- ستون موجودی فعلی را انتخاب کنید.
- وارد Conditional Formatting شوید.
- یک قانون جدید ایجاد کنید.
- سلولهایی را که مقدار آنها کمتر یا مساوی نقطه سفارش است مشخص کنید.
- برای آنها قالب هشدار انتخاب کنید.
نقطه سفارش باید با توجه به زمان واقعی تأمین، نوسان فروش، حداقل مقدار خرید و احتمال تأخیر تأمینکننده تعیین شود.
گزارشگیری از موجودی با PivotTable
PivotTable به شما کمک میکند حجم زیادی از تراکنشها را به یک گزارش خلاصه تبدیل کنید. با استفاده از آن میتوانید موارد زیر را بررسی کنید:
- مجموع ورود هر کالا
- مجموع خروج هر کالا
- گردش کالا در هر ماه
- موجودی هر گروه کالا
- خروج کالا از هر انبار
- کالاهای پرمصرف
- ارزش موجودی براساس گروه یا انبار
برای ساخت PivotTable:
- یکی از سلولهای جدول گردش کالا را انتخاب کنید.
- وارد منوی Insert شوید.
- PivotTable را انتخاب کنید.
- محل ساخت گزارش را تعیین کنید.
- کد یا نام کالا را در بخش Rows قرار دهید.
- ورودی و خروجی را در بخش Values قرار دهید.
- تاریخ یا نام انبار را در بخش Filters قرار دهید.
اطلاعات PivotTable پس از تغییر داده های اصلی باید Refresh شوند. بنابراین بهتر است پس از ثبت تراکنشهای جدید، گزارشها نیز بهروزرسانی شوند.
۷ نکته برای کاهش خطا در انبارداری با اکسل
۱. برای هر کالا کد یکتا تعریف کنید
نام کالا ممکن است تغییر کند یا با املای متفاوت نوشته شود، اما کد کالا باید ثابت بماند. گزارشگیری و اتصال جدولها باید براساس این کد انجام شود.
۲. اطلاعات را همزمان با عملیات ثبت کنید
اگر خروج کالا امروز انجام شود اما فردا در اکسل ثبت شود، موجودی فایل تا زمان ثبت صحیح نخواهد بود. مسئولیت و زمان ثبت تراکنشها باید مشخص باشد.
۳. سلولهای فرمول را قفل کنید
ستونهای محاسباتی را Protect کنید تا کاربران فقط بتوانند اطلاعات ورودی را تغییر دهند. حذف یا ویرایش اتفاقی یک فرمول میتواند کل گزارش را نادرست کند.
۴. از Data Validation استفاده کنید
برای کد کالا، نام انبار، واحد اندازهگیری و نوع سند فهرست کشویی ایجاد کنید. این کار از ثبت چند شکل متفاوت یک مقدار جلوگیری میکند.
۵. نسخه پشتیبان و تاریخچه تغییرات داشته باشید
فایل را در محلی نگهداری کنید که نسخههای قبلی آن قابل بازیابی باشند. نامگذاری فایلها با تاریخ، نگهداری نسخه ماهانه یا استفاده از فضای اشتراکی دارای Version History میتواند از حذف ناخواسته اطلاعات جلوگیری کند.
۶. موجودی ثبتشده را با شمارش فیزیکی تطبیق دهید
اکسل فقط اطلاعات ثبتشده را نمایش میدهد و نمیتواند سرقت، ضایعات، خرابی یا خروج ثبتنشده را تشخیص دهد. به همین دلیل باید در بازههای مشخص، موجودی واقعی شمارش و با فایل مقایسه شود.
برای بررسی مراحل کنترل فیزیکی موجودی میتوانید راهنمای انبارگردانی را مطالعه کنید.
۷. زمان عبور از اکسل را تشخیص دهید
با افزایش تعداد کالا، تراکنش، انبار و کاربران، کنترل فرمولها و نسخههای مختلف فایل دشوار میشود. در این شرایط ادامه استفاده از اکسل ممکن است زمان بیشتری از تهیه و راهاندازی یک سیستم یکپارچه صرف کند.
مزایا و محدودیتهای انبارداری با اکسل
| معیار | مزیت یا محدودیت |
| هزینه شروع | پایین و در دسترس |
| انعطافپذیری | امکان ساخت جدول متناسب با نیاز |
| یادگیری | برای فایل ساده نسبتاً آسان |
| گزارشگیری | مناسب گزارشهای اولیه و PivotTable |
| ورود اطلاعات | معمولاً دستی |
| کنترل کاربران | محدودتر از نرمافزار تخصصی |
| ثبت اسناد | نیازمند طراحی دستی |
| چند انبار | با افزایش انبارها پیچیده میشود |
| بارکد و سریال | نیازمند تنظیمات یا ابزار جانبی |
| تاریخ انقضا | قابل ثبت است، اما کنترل آن دستی یا فرمولی است |
| امنیت فرمولها | وابسته به قفل کردن فایل و سطح دسترسی |
| اتصال به فروش و خرید | بهصورت پیشفرض وجود ندارد |
| ممیزی تغییرات | محدودتر از سیستمهای تخصصی |
مقایسه اکسل و نرم افزار انبارداری
اکسل برای شروع و گردش محدود مناسب است، اما نرمافزار انبارداری برای ثبت یکپارچه عملیات طراحی شده است.
| معیار | اکسل | نرمافزار انبارداری |
| ثبت ورود و خروج | دستی | مبتنی بر رسید، حواله و عملیات سیستم |
| ارتباط با فروش | معمولاً جداگانه | قابل اتصال به فاکتور فروش |
| ارتباط با خرید | معمولاً جداگانه | قابل اتصال به خرید و رسید |
| چندکاربره بودن | محدود و وابسته به روش اشتراک | دارای کاربران و سطوح دسترسی |
| چند انبار | نیازمند طراحی پیچیدهتر | قابل مدیریت در ساختار سیستم |
| کاردکس کالا | با فرمول و جدول | گزارش آماده |
| بارکد | نیازمند تنظیمات جانبی | بسته به امکانات سیستم قابل استفاده |
| گزارش موجودی | نیازمند طراحی | گزارشهای آماده و قابل فیلتر |
| ردیابی تغییرات | محدود | معمولاً دقیقتر و مبتنی بر کاربر |
| حسابداری | جدا از فایل انبار | قابل ارتباط با عملیات مالی |
کسبوکارهایی که از اکسل عبور کردهاند، میتوانند امکانات نرم افزار حسابداری انبارداری را براساس تعداد کالاها، انبارها، کاربران و نوع عملیات خود بررسی کنند.
چه زمانی باید از اکسل به نرم افزار انبارداری مهاجرت کرد؟
وجود یکی از موارد زیر الزاماً به معنی کنار گذاشتن اکسل نیست، اما تجمع چند مورد نشان میدهد فایل فعلی دیگر پاسخگوی عملیات نیست:
- چند نفر همزمان فایل را تغییر میدهند.
- نسخههای مختلفی از فایل میان کاربران جابهجا میشود.
- موجودی سیستم با شمارش واقعی اختلاف مداوم دارد.
- تعداد ورود و خروج روزانه زیاد شده است.
- چند انبار یا شعبه باید مدیریت شود.
- کنترل سریال، بچ، تاریخ تولید یا تاریخ انقضا لازم است.
- فروش بدون بررسی موجودی انجام میشود.
- موجودی منفی بهدفعات رخ میدهد.
- اطلاعات خرید، فروش و انبار در فایلهای جداگانه نگهداری میشوند.
- تهیه گزارش به اصلاح دستی فرمولها وابسته است.
- سطح دسترسی کاربران اهمیت پیدا کرده است.
در چنین شرایطی، بررسی نرم افزار حسابداری انبارداری محک و مشاهده نحوه ثبت کالا، رسید، حواله، کاردکس و گزارش موجودی میتواند به تصمیمگیری دقیقتر کمک کند. امکانات قابل استفاده باید براساس نسخه نرمافزار و نیاز واقعی کسبوکار بررسی شوند.
پرسشهای متداول
آیا انبارداری با اکسل رایگان است؟
اگر اکسل یا ابزار صفحه گسترده در اختیار داشته باشید، ساخت فایل ساده هزینه جداگانهای ندارد. با این حال، طراحی فایل، نگهداری فرمولها، ثبت اطلاعات و کنترل خطا به زمان و نیروی انسانی نیاز دارد.
فایل انبارداری با اکسل چه ستونهایی دارد؟
کد کالا، نام کالا، تاریخ، شماره سند، مقدار ورود، مقدار خروج، مانده، بهای واحد، ارزش موجودی، نقطه سفارش و نام انبار از ستونهای اصلی هستند.
فرمول موجودی انبار در اکسل چیست؟
فرمول پایه برابر است با موجودی اول دوره بهاضافه مجموع ورود و منهای مجموع خروج. برای محاسبه براساس کد کالا میتوان از SUMIFS استفاده کرد.
چگونه از موجودی منفی جلوگیری کنیم؟
پیش از ثبت خروج باید مانده کالا بررسی شود. همچنین میتوان با IF و Conditional Formatting برای موجودی صفر یا منفی هشدار ایجاد کرد. این کنترل در اکسل به صحت و زمان ثبت اطلاعات وابسته است.
آیا اکسل برای انبارداری فروشگاه مناسب است؟
برای فروشگاه کوچک با تعداد کالای محدود و تراکنش کم میتواند مناسب باشد. در فروشگاههایی با فروش روزانه زیاد، بارکد، چند صندوق یا چند کاربر، اتصال مستقیم انبار به فروش اهمیت بیشتری پیدا میکند.
تفاوت فایل موجودی و کاردکس کالا چیست؟
فایل موجودی معمولاً مانده فعلی را نشان میدهد؛ اما کاردکس کالا تاریخچه ورود، خروج و مانده پس از هر تراکنش را نگهداری میکند.
آیا میتوان چند انبار را در یک فایل مدیریت کرد؟
بله، با اضافه کردن ستون نام یا کد انبار و استفاده از SUMIFS و PivotTable امکانپذیر است. با افزایش تعداد انبارها و انتقالهای بین آنها، فایل پیچیدهتر و کنترل آن دشوارتر میشود.
چه زمانی نرمافزار انبارداری بهتر از اکسل است؟
زمانی که تعداد تراکنشها، کاربران، انبارها یا نیازهای کنترلی افزایش یافته و ثبت دستی اطلاعات باعث مغایرت، دوبارهکاری یا تأخیر در گزارشگیری شده باشد، نرمافزار تخصصی انتخاب مناسبتری است.
جمعبندی
انبارداری با اکسل برای شروع یک کسبوکار کوچک و کنترل تعداد محدودی کالا قابل استفاده است؛ به شرط آنکه کالاها کدگذاری شوند، هر ورود و خروج براساس سند ثبت شود، ستونهای فرمول محافظت شوند و موجودی فایل بهطور دورهای با موجودی واقعی تطبیق داده شود.
یک فایل استاندارد باید فقط مانده کالا را نشان ندهد؛ بلکه تاریخچه گردش، نقطه سفارش، ارزش موجودی و کالاهای کمموجودی را نیز مشخص کند. با افزایش حجم عملیات، محدودیتهای ثبت دستی، چندکاربره بودن و ارتباط نداشتن فایل با خرید و فروش بیشتر دیده میشوند. در این مرحله باید هزینه و ریسک ادامه کار با اکسل را با استفاده از یک سیستم انبارداری یکپارچه مقایسه کرد.
