فرمول sumifs برای جستجو و محاسبه مقادیر یک عنوان خاص در چند شیت چیست؟

[Mohsen®]

سلام
یه ورک شیت دارم حاوی 4 شیت مجزا
در سه شیت اول اطلاعات وارد کردم و در شیت چهارم جمع مقادیر

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

نمونه فایل رو پیوست کردم، منتظر کمکتون هستم.

* توضیح اینکه در شیت دوم خودم تونستم دانشگاه ازاد رو با استفاده از درج عنوان جمع بزنم (بدون انتخاب کردن سلول) ولی این کار رو برای تمام شیت ها نمیتونم انجام بدم.

[amir_ts]

با سلام
فرمول زیر را امتحان کنید...
سلول های f2 تا f4 نام شیت ها میباشد.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$F$2:$F$4&"'!d1:D200");J5;INDIRECT("'"&$F$2:$F$4&"'!e1:e200"))) 

[Mohsen®]

با سلام
فرمول زیر را امتحان کنید...
سلول های f2 تا f4 نام شیت ها میباشد.

=sumproduct(sumif(indirect("'"&$f$2:$f$4&"'!d1:d200");j5;indirect("'"&$f$2:$f$4&"'!e1:e200"))) 

سلام داداش دمت گرم همینه
من دستی هم حساب کردم درست بود
فقط خواهشا یه توضیح در مورد عملکردش بهم بده
و کلا کاراکتر هایی که توش هست رو هم برام اگه میشه شرح بده
منظورم اینه که چرا sumproduct - چرا indirect - چرا "" و اینکه $ علامت چیه - مثلا محدوده رو با : مشخص میکنن و ; جداکننده محدوده از هم هست - علامت تعجب ! برای چی هست
خواهش میکنم توضیح بدید برام تا خیلی از سوالهام رو جوابش رو پیدا کرده باشم
ممنون

[amir_ts]

فقط خواهشا یه توضیح در مورد عملکردش بهم بده
و کلا کاراکتر هایی که توش هست رو هم برام اگه میشه شرح بده
منظورم اینه که چرا sumproduct - چرا indirect - چرا "" و اینکه $ علامت چیه - مثلا محدوده رو با : مشخص میکنن و ; جداکننده محدوده از هم هست - علامت تعجب ! برای چی هست
خواهش میکنم توضیح بدید برام تا خیلی از سوالهام رو جوابش رو پیدا کرده باشم

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

[Mohsen®]

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

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

[Mohsen®]

امير آقا راه حلي كه شما ارائه كرديد درست بود
اما سوالي كه پيش اومده اينه كه چه فرمولي بايد نوشت كه با فراخواني داده ها (نه ادرس دهي مثل همون سه تا سلولي كه شما به نام Area برام ساختي) فرمولي نوشت كه در هر سه شيت مقادير عنوان "دانشگاه آزاد" رو رو جمع بزنه و در شيت چهارم به نمايش در بياد ؟

منظورم اينه كه راه حل اين مشكل غير از INDRIRECT چيه؟

[amir_ts]

منظورم اينه كه راه حل اين مشكل غير از INDRIRECT چيه؟

با سلام
خواست شما نیاز به فرمول سه بعدی دارد که باید از تابع indirect برای دسترسی به شیت های دیگه مورد استفاده قرار بگیره...

[Mohsen®]

با سلام
خواست شما نیاز به فرمول سه بعدی دارد که باید از تابع indirect برای دسترسی به شیت های دیگه مورد استفاده قرار بگیره...

امیر جان مشکل رو با این فرمول حل کردم

=SUM(SUMIF(Area2!D:D;"دانشگاه آزاد";Area2!E:E)+(SUMIF(Area3!D:D;"دانشگاه آزاد";Area3!E:E))) 

یعنی جمع دو شرط در یک فرمول
_________________________
آیا میشه دو محدوده محاسبه جداگانه در دو شیت رو در یک آرگومان جدا داد؟ یعنی بین دو محدوده باید جه علامتی قرار بگیره که ارور نده؟ منطورم این دو محدوده در فرمول بالاست Area2!D:D و Area3!D:D ؟

[amir_ts]

در نظر داشته باشید اگر تعداد شیت ها زیاد باشه این روش مناسب نیست.چون برای هر شیت یک فرمول نیاز هست. برای این منظور از ترکیب توابع استفاده میشه.

آیا میشه دو محدوده محاسبه جداگانه در دو شیت رو در یک آرگومان جدا داد؟ یعنی بین دو محدوده باید جه علامتی قرار بگیره که ارور نده؟ منطورم این دو محدوده در فرمول بالاست Area2!D:D و Area3!D:D ؟

تابع sumifs برای قرار دادن بیش از یک شرط مورد استفاده قرار میگیره...
دو محدوده در sumif میخواهید؟

[Mohsen®]

در نظر داشته باشید اگر تعداد شیت ها زیاد باشه این روش مناسب نیست.چون برای هر شیت یک فرمول نیاز هست. برای این منظور از ترکیب توابع استفاده میشه

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

تابع sumifs برای قرار دادن بیش از یک شرط مورد استفاده قرار میگیره...
دو محدوده در sumif میخواهید؟

_____________
فرقی نمیکنه
هر تابعی که نتیجه کار رو محاسبه کنه و ارور نده

با اموزه های قبلی و چیزایی که از شما یاد گرفتم این فرمول رو وارد کردم ولی ارور #Value داد

=SUM(SUMIF(Area2:Area3!D:D;"دانشگاه آزاد";Area2:Area3!E:E)) 

شرط مشترک همون دانشگاه ازاد هست
محدوده من در هر سه شیت محدوده D:D هست
و محدوده محاسبه ی من E:E

[amir_ts]

=SUM(SUMIF(Area2:Area3!D:D;"دانشگاه آزاد";Area2:Area3!E:E)) 

این طریق غلط هست و قابل اجرا نیست.
تابع sumif یک رنج میپذیرد و اگر بخواهید از رنج شیت های دیگه استفاده کنید باید از فرمول سه بعدی که قرار داده شده استفاده کنید.
دقیقا برای خروجی مورد نظر شما باید از تابع indirect و همچنین تابع sumproduct (یا آرایه ای کردن فرمول) استفاده بشه.
اگر فرمول صحیح هست چه اصراری به تغییر دادن اون دارید.