نحوه محاسبه شروط ترکیبی در اگسل

[dina1355]

با سلام و خسته نباشید
اطلاعات شرکت تعاونی در یک شیت وارد شده ، در سلول بعدی مبالغ واریزی و در سلول دیگر سال واریزی : سوال بنده این است اگر بخواهم بدانم هر فردی در هر سال چقدر واریز کرده باید از چه فرمول ترکیبی استفاده کنم- بنده در از فرمول ترکیبی بشرح ذیل استفاده کردم =(((SUMIF(Sheet1!$C$3:$I$70؛Sheet2!C3؛Sheet1!$G$ 3:$AV$70)&(VLOOKUP(C3؛D3؛Sheet1!G3:G70؛0))))) ,ولی جواب نمیده
فایل نمونه را ارسال می کنم
جواب در شیت دو می باشد- خواهش می کنم راهنمایی کنید

[amir_ts]

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

=IFERROR(SUM(OFFSET(Sheet1!$F$2:$AT$2;MATCH(B3&C3&D3;Sheet1!$B$3:$B$69&Sheet1!$C$3:$C$69&Sheet1!$E$3:$E$69;0);));"")

[dina1355]

مهندس امکان دارد راهنمایی بیشتر بفرمائید ، بعد گذاشتن فرمول سغید نشان می دهد
ممنون و متشکر

[dina1355]

مهندس وقتی فرمول شما را دقت می کنم اولش کروشه هست ولی بنده آنرا اضافه می کنم خطا می دهد

[dina1355]

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

[amir_ts]

مهندس امکان دارد راهنمایی بیشتر بفرمائید ، بعد گذاشتن فرمول سغید نشان می دهد
ممنون و متشکر
مهندس وقتی فرمول شما را دقت می کنم اولش کروشه هست ولی بنده آنرا اضافه می کنم خطا می دهد
یگ نکته بنده فرمول شما را در سیستم قراردادید دقت کردم ، سرجمع پرداختی را نشان می دهد هر سال را به تفکیک نشان نمی دهد یعنی برای سال 88 را فقط جمع نمی کند
با سلام
با عرض پوزش من فراموش کردم توضیح فرمول رو بدم که فرمول آرایه ایست CSE formulas)) و باید بعد از نوشتن فرمول به جای Enter خالی باید کلیدهای Ctrl+shift+Enter رو با هم فشار بدید که این عمل شما با نمایان شدن علامت {} در ابتدا و انتهای فرمول تاییدمیشه.
ضمنا در فرمول کد ها بر اساس ستون C و سال واریزی بر اساس ستون D شروطش سنجیده میشه .
سل های زرد رنک شیت رو برای نمونه ببینید.

[dina1355]

مهندس بزرگوار فکر کنم من نتونستم خوب توضیح بدم ما دو تا شیت داذیم یک شیت که واریزی اعضا می باشد مثلا 86 -87-88-89-الی 93 که نشان دهنده وایزی هر شخص در آن سال نی باشد . حالا می می خواهم بدانم آقای x در سال 86 چقدر واریزی داشته است . حضرتعالی این فرمولی که نوشتین کل واربری هر شحض را در طی 5 سال نشان می هد . این جواب را که بنده در شیت یک دارم . خواهش می کنم راهنمایی بفرمائید مجدد فایلم را اصلاح و مجدد ارسال می کنم

[Amir Ghasemiyan]

سلام دوست عزیز
اگر درست متوجه سوالتون شده باشم شما نیاز به این فرمول آرایه ای دارید


=SUM(IF(VALUE(Sheet1!D3:AD3)=86;OFFSET(Sheet1!D3:A D3;0;1);""))

[dina1355]

مهندس با تشکر فراوان بنده هدفم اینه که بدانم آقای x در سال 86 چقدر واریز کرده برای سالهای 87 و الی آخر جدا به ما گزارش دهد
ممنون می شوم راهنمایی کنید

[dina1355]

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

[amir_ts]

با سلام
فرمول زیر رو در شیت دوم سلول E3 قرار بدید. (فرمول آرایه ایست).
ضمنا سال مورد نظر در فرمول قرار گرفته که بهتره به یک سل ارتباط داده بشه که بصورت پویا سال های دیگر رو هم به تفکیک بتونید محاسبه کنید.


=IFERROR(SUMIF(OFFSET(Sheet1!$D$2:$EF$2;MATCH(B3;S heet1!$B$3:$B$23;0););86;OFFSET(Sheet1!$E$2:$EF$2; MATCH(B3;Sheet1!$B$3:$B$23;0);));"")

[dina1355]

با سلام بنده با تمام دردسر توانستم برای هر نفر عضو تعاونی مبالغ واریزی را دستی جمع بزنم و وارد شیت 1 الی 20 کردم ، تمام هدفم این بود به مثل شیت بپیوست اطلاعات هر فرد دسته بندی شود . الان دو تا راهمایی لازم دارم فرمول اینکه دستی بنده بخواهم اطلاعات را از sheet1 به شماره های 1 الی 20 ( با استفاده از فرمول ) انتقال بدهم از چه فرمولی استفاده کنم - دوم اینکه در sheet2 نمی توانم فرمولها d3 الی n3 را به پایین امتداد بدهم برای هر سلول را فرمول خاص بنویسم .از اساتید محترم خواهشمندم بنده را راهنمایی کنید بسیار ممنون و متشکر

[amir_ts]

با سلام
اگر فرمول ارسالی رو تست میکردید به دردسری که اشاره کردید دچار نمیشدید ولی اگر بحث جداسازی مطرح بود که ناچار به تغییر ساختار بودید که ظاهرا هم انجام دادید.


فرمول اینکه دستی بنده بخواهم اطلاعات را از sheet1 به شماره های 1 الی 20 ( با استفاده از فرمول ) انتقال بدهم از چه فرمولی استفاده کنم
برای این منظور احتیاج به کمی تغیر در لیست اطلاعات دارید.
1-اصولا Merge سل ها مشکل ساز هست و باید تا حد امکان از آن اجتناب شود.
2-نام ها باید دقیقا فرمتشون مانند هم باشه که تابع match بتونه تشخیص بده.
تا شیت 4 رو انجام دادم باقی رو خودتون انجام بدید.


=IFERROR(INDEX(Sheet1!$A$1:$R$70;MATCH($A$1;Sheet1 !$B$1:$B$69;0);SMALL(IF(--LEFT(OFFSET(Sheet1!$D$2:$Q$2;MATCH($A$1;Sheet1!$B$ 3:$B$69;0)+1;);2)=$B3;COLUMN(Sheet1!$D$4:$Q$4));CO LUMNS($A$1:A1)));"")



دوم اینکه در sheet2 نمی توانم فرمولها d3 الی n3 را به پایین امتداد بدهم برای هر سلول را فرمول خاص بنویسم

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


=IF(C3>0;TRANSPOSE('1'!$O$3:$O$11);"")

فرمول ها آرایه ای هستند و باید به جای Enter عادی ، Ctrl+Shift+Enter رو با هم بفشارید.

[dina1355]

ممنون و متشکر از راهنمایی شما استاد فرزانه