مدیریت موجودی کالا در اکسل یکی از مهم‌ترین بخش‌های هر فروشگاه و کسب‌وکار کالامحور است. اگر ورود و خروج کالا به‌درستی ثبت نشود، احتمال اختلاف موجودی، کمبود کالا یا خرید بیش از نیاز افزایش پیدا می‌کند.

با استفاده از اکسل می‌توان یک فایل ساده و کاربردی برای ثبت خرید، فروش، ورود و خروج کالا و محاسبه موجودی ایجاد کرد. در این مقاله نحوه ساخت چنین فایلی را مرحله‌به‌مرحله آموزش می‌دهیم.

مدیریت موجودی کالا در اکسل چیست؟

مدیریت موجودی کالا در اکسل یعنی ثبت اطلاعات کالا و تراکنش‌های مربوط به آن در یک فایل Excel و محاسبه موجودی با استفاده از فرمول‌ها.

یک فایل ساده انبارداری می‌تواند شامل این اطلاعات باشد:

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

ساخت جدول موجودی کالا در اکسل

ابتدا یک شیت با عنوان کالاها ایجاد کنید.

ساختار پیشنهادی:

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

فرمول محاسبه موجودی کالا در اکسل

فرمول ساده موجودی به شکل زیر است:

موجودی فعلی = موجودی اولیه + ورود – خروج

اگر:

• موجودی اولیه در C2
• ورود در D2
• خروج در E2

باشد، فرمول موجودی فعلی:

=C2+D2-E2

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

ثبت ورود و خروج کالا

بهتر است تراکنش‌های انبار را در یک شیت جداگانه ثبت کنید.

برای مثال:

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

چرا ثبت تراکنش‌ها در شیت جدا بهتر است؟

اگر تمام تغییرات موجودی را مستقیماً در جدول کالاها وارد کنید، بررسی سوابق دشوار می‌شود.

اما با داشتن شیت تراکنش‌ها می‌توانید بدانید:

• چه کالایی وارد شده است؟
• چه کالایی خارج شده است؟
• چه زمانی تراکنش انجام شده؟
• چه تعدادی جابه‌جا شده است؟
• موجودی یک کالا چگونه تغییر کرده است؟

این روش برای تهیه گزارش‌های دقیق‌تر کاربردی‌تر است.

محاسبه مجموع ورود کالا با SUMIF

فرض کنید:

• کد کالا در ستون B
• نوع تراکنش در ستون D
• تعداد در ستون E

قرار دارد.

برای محاسبه مجموع ورود کالای دارای کد 1001 می‌توان از SUMIFS استفاده کرد:

=SUMIFS(E:E,B:B,1001,D:D,”ورود”)

این فرمول تمام تراکنش‌های ورود مربوط به کد 1001 را جمع می‌کند.

برای محاسبه خروج:

=SUMIFS(E:E,B:B,1001,D:D,”خروج”)

محاسبه موجودی واقعی کالا

اکنون می‌توانیم موجودی را بر اساس تراکنش‌ها محاسبه کنیم.

فرمول کلی:

موجودی = موجودی اولیه + مجموع ورود – مجموع خروج

مثلاً:

=C2+D2-E2

که در آن D2 مجموع ورود و E2 مجموع خروج است.

ایجاد لیست کالاها

در یک فایل حرفه‌ای بهتر است اطلاعات پایه کالاها در یک شیت جدا قرار بگیرد.

شیت «کالاها»

این اطلاعات می‌تواند مبنای سایر بخش‌های فایل قرار گیرد.

استفاده از XLOOKUP برای نمایش نام کالا

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

در Excelهای جدید:

=XLOOKUP(B2,کالاها!A:A,کالاها!B:B,””)

این فرمول کد کالا را پیدا کرده و نام مربوط به آن را برمی‌گرداند.

در نسخه‌های قدیمی‌تر Excel می‌توان از VLOOKUP نیز استفاده کرد.

ایجاد فهرست کشویی برای انتخاب کالا

برای جلوگیری از اشتباه در ورود نام کالا، می‌توانید از Data Validation استفاده کنید.

مسیر کلی:

Data → Data Validation → List

سپس لیست کالاها را به‌عنوان منبع انتخاب کنید.

با این کار، کاربر به‌جای تایپ دستی نام کالا، آن را از فهرست انتخاب می‌کند.

هشدار کمبود موجودی

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

فرض کنید:

• موجودی فعلی در F2
• حداقل موجودی در G2

باشد.

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

=IF(F2<=G2,”نیاز به خرید”,”موجودی مناسب”)

به این ترتیب، کالاهایی که موجودی آن‌ها پایین است سریع‌تر شناسایی می‌شوند.

استفاده از Conditional Formatting

برای حرفه‌ای‌تر کردن فایل می‌توانید از Conditional Formatting استفاده کنید.

برای مثال:

• موجودی کمتر از حد مجاز → نمایش هشدار
• موجودی صفر → هشدار جدی
• موجودی مناسب → حالت عادی

این قابلیت مخصوصاً زمانی مفید است که تعداد کالاها زیاد باشد.

گزارش کالاهای رو به اتمام

می‌توان یک جدول جدا برای کالاهای کم‌موجودی ایجاد کرد.

این گزارش به مسئول خرید کمک می‌کند کالاهایی را که به حداقل موجودی رسیده‌اند سریع‌تر شناسایی کند.

محاسبه ارزش موجودی انبار

علاوه بر تعداد کالا، می‌توان ارزش موجودی را نیز محاسبه کرد.

فرمول ساده:

ارزش موجودی = تعداد موجودی × قیمت واحد

اگر موجودی در F2 و قیمت خرید در E2 باشد:

=F2*E2

برای مثال اگر ۲۰ عدد کالا با قیمت خرید ۳۰۰ هزار تومان در انبار باشد:

۲۰ × ۳۰۰,۰۰۰ = ۶,۰۰۰,۰۰۰ تومان

محاسبه ارزش کل موجودی

اگر ارزش موجودی هر کالا در ستون H باشد:

=SUM(H2:H100)

این فرمول ارزش کل موجودی ثبت‌شده در جدول را محاسبه می‌کند.

ساخت گزارش ورود و خروج کالا

یک گزارش کاربردی دیگر، گزارش گردش کالا است:

این گزارش دید مناسبی از وضعیت انبار ایجاد می‌کند.

ساخت داشبورد موجودی در اکسل

برای یک فایل حرفه‌ای‌تر می‌توانید داشبورد طراحی کنید.

مثلاً در بالای داشبورد:

تعداد کالاها: ۱۵۰
کالاهای کم‌موجودی: ۱۲
کالاهای بدون موجودی: ۴
ارزش موجودی: … تومان

همچنین می‌توان نمودارهایی برای نمایش موارد زیر ایجاد کرد:

• کالاهای پرفروش
• ورود و خروج ماهانه
• ارزش موجودی
• کالاهای کم‌موجودی
• تعداد کالاها در هر دسته‌بندی

مدیریت موجودی چند انبار در اکسل

اگر کسب‌وکار چند انبار دارد، بهتر است یک ستون با عنوان انبار اضافه شود.

مثلاً:

سپس می‌توان گزارش موجودی هر انبار را به‌صورت جداگانه تهیه کرد.

نکات مهم برای طراحی فایل انبارداری

۱. کد یکتا برای هر کالا تعیین کنید

وجود کد کالا باعث می‌شود شناسایی محصولات ساده‌تر شود.

۲. ورود و خروج را ثبت کنید

بهتر است هر تغییر موجودی به‌عنوان یک تراکنش ثبت شود.

۳. فرمول‌ها را محافظت کنید

سلول‌های فرمول‌دار را قفل کنید تا به‌صورت تصادفی تغییر نکنند.

۴. از فایل نسخه پشتیبان داشته باشید

اطلاعات انبار معمولاً برای کسب‌وکار اهمیت زیادی دارد.

۵. واحد کالا را مشخص کنید

مثلاً عدد، کیلوگرم، متر، بسته یا کارتن.

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

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

• لیست کالاها
• کد کالا
• دسته‌بندی
• ثبت ورود
• ثبت خروج
• محاسبه موجودی
• حداقل موجودی
• هشدار کمبود کالا
• ارزش موجودی
• گزارش گردش کالا
• جستجوی کالا
• گزارش ماهانه
• داشبورد انبار

چنین فایلی را می‌توان متناسب با تعداد کالاها و نوع فعالیت کسب‌وکار شخصی‌سازی کرد.

آیا اکسل برای مدیریت انبار مناسب است؟

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

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

جمع‌بندی

مدیریت موجودی کالا در اکسل با ایجاد دو بخش اصلی یعنی لیست کالاها و تراکنش‌های ورود و خروج قابل انجام است. با استفاده از فرمول‌هایی مانند SUMIF و SUMIFS می‌توان مقدار ورود و خروج هر کالا را محاسبه کرد و موجودی فعلی را به‌صورت خودکار به دست آورد.

همچنین با امکاناتی مانند XLOOKUP، Data Validation، Conditional Formatting و نمودارها می‌توان یک فایل انبارداری حرفه‌ای‌تر ایجاد کرد.