[حل شده] باقیماده اقلام پاس نشده انبار

[keyvana]

با سلام خدمت دوستان عزیز
فایلی به نام امانی ارسالی ضمیمه کرده ام که دو شیت به نامهای امانی و برگشتی دارد. هر شماره از ستون شماره سند امانی در شیت امانی یک سند است که میتواند یک یا چند ردیف(آیتم) داشته باشد. مثل سند شماره 10 که 3 آیتم دارد.

در شیت برگشتی هر شماره سند برگشتی، عطف به یک سند امانی صادر شده و آن امانی را پاس کرده است، منتها ممکن است کل یا بخشی از سند امانی پاس شده باشد و بخشی دیگر با سند برگشتی دیگر پاس شده باشد یا حتی بخشی نیز باقی بماند. مثل سند امانی 33 و 59 و 60 و 106

چه فرمولی میشود در شیت دیگری نوشت که مانده اقلام امانی (آنهایی که هنوز پاس نشده) را نشان دهد.

با تشکر از دست اندرکاران محترم

[amir_ts]

با سلام
فایل نمونه رو ملاحظه کنید.با استفاده از یک ستون کمکی در شیت امانی جستو صورت گرفته برسی کنید که آیا نتیجه مورد نظر شما بدست امده یا نه؟
اگر درست بود فرمول رو توضیح بدم.

[keyvana]

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

5 عدد دینام کولر 1/2
10 عدد موتور کولر 3/4
1 عدد موتور هواکش

در شیت برگشتی، توسط سند برگشتی شماره 58 موارد زیر پاس شده :

3 عدد موتور کولر 3/4

قاعدتاً باید مانده امانی 66 بشود :

5 عدد دینام کولر 1/2
7 عدد موتور کولر 3/4
1 عدد موتور هواکش

مورد 1 و 3 درست است. ولی 7 عدد موتور کولر 3/4 دیده نمیشود.

در خصوص امانی شماره 106 هم همینطور است. یعنی مانده تونر 53 و تونر 80 صحیح نیست.

با تشکر از شما

[amir_ts]

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

[keyvana]

با سلام و خسته نباشید خدمت شما و همه دوستان زحمت کش
در آخرین فایل که سه ستون کمکی گذاشتید چند تا از سندها هنوز به صورت صحیح مانده گیری نشده است.
مثلاً امانی شماره 9 تعداد 2 عدد دستگاه فاکس بوده که 1 عدد آن با برگشتی 25 و 1 عدد با برگشتی 35 پاس شده و نمیبایستی مانده ای داشته باشد. در حالیکه در شیت مانده امانی هنوز 1 عدد مانده نشان میدهد.

امانی های 33 و 59 و 60 و 66 و 106 هم همین حالت را دارند.
خیلی تو فرمولهای شما فکر کردم و تا حدودی یک چیزایی رو تشخیص دادم ولی در کل نتوانستم کلیت انجام محاسبه رو درک کنم.

با تشکر از شما

[amir_ts]

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

جمع با شروط مشخص برای تعداد اقلام پاس شده:

=SUMIFS(برگشتي!$A$1:$A$118;برگشتي!$E$1:$E$118;F2;برگشتي!$D$1:$D$118;D2) 

جمع با شروط مشخص برای تعداد اقلام امانی:

=SUMIFS($A$1:$A$194;$D$1:$D$194;D2;$F$1:$F$194;F2) 

با استفاده از تابع Exact مقایسه این دو صورت میگیره و نتیجه اینکه جمع تعداد اقلام امانی با تعداد اقلام پاس شده مساوی هست یاخیر بدست میاد اگر هست true و اگر نیست false
با true ها که کاری نداریم چون تماما پاس شده و باقی نمانده ولی با یک فرمول آرایه ای تمام false ها رو در لیست اقلام برگشتی فراخوان میکنیم.

=IFERROR(INDEX(اماني!$A$1:$G$194;SMALL(IF(اماني!$J$2:$J$194=FALSE;ROW((اماني!$J$2:$J$194)));ROW(A1));COLUMN());"") 

توضیح فرمول آرایه ای خارج مجال این توضیحات هست فقط به همین بسنده میکنم که تمام ردیف های false شده پشت سر هم در شیت مربوطه قرار میگیره.(در فرمول های آرایه ای بعد از نوشتن فرمول به جای کلید Enter از ترکیب و فشردن همزمان Ctrl+shift+enter استفاده میکنند).
خوب فقط تعداد باقیمانده باید مشخص بشه که با کسر دو ستون کمکی شیت امانی (ستون I و H) که فرمول های آن در بالا آورده شد به سادگی بدست میاد.
اگر چنانچه سوال یا ابهامی داشتید بفرمایید.

[keyvana]

با سلام خدمت دوستان عزیز

امیر آقا

ممنون از زحماتتون

در آخرین فایلی که فرستادید دیتاهای واقعی رو وارد کردم. ولی در شیت مانده اقلام امانی همونطور که میبینید (فایل پیوست) از ردیف 194 به بعد یعنی از سند امانی 120 به بعد ستون تعداد رو محاسبه نمیکنه و خالیه.

لطفا بفرمایید مشکلش کجاست

سپاسگذارم

[iranweld]

با سلام

دو تا شیت خود را در یک شیت کپی نمایید و اقلام امانی را با منفی و اقلام برگشتی را با مثبت مشخص کنید و سپس با پیوت تیبل گزارشگیری نمایید

[amir_ts]

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

[keyvana]

با تشکر و سپاس فراوان