ورود مقادیری از جدول دیگر با شرایط مشخص

**M_ExceL** · 2019/05/12, 13:27

نوشته اصلی توسط maziardm

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

سلام،
می تونید از فرمول sumif و یا توابع آرایه ای برای این مورد استفاده کنید.
در فایل پیوست از توابع آرایه ای استفاده شده است.

فایل های پیوست شده

File2.xlsx (16.1 کیلو بایت, مشاهدات 7)

**maziardm** · 2019/05/12, 17:01

نوشته اصلی توسط M_ExceL

سلام،
می تونید از فرمول sumif و یا توابع آرایه ای برای این مورد استفاده کنید.
در فایل پیوست از توابع آرایه ای استفاده شده است.

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

فایل های پیوست شده

File 2.xlsx (15.7 کیلو بایت, مشاهدات 3)

**M_ExceL** · 2019/05/12, 19:32

نوشته اصلی توسط maziardm

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

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

کد:

Public Function Sum_multiplecol(cel, rng As Range, col1, col2, col3, col4, col5 As Byte)
Dim rr As Range
For Each rr In rng
If cel = rr Then
If col1 > 0 Then sm1 = rr.Offset(0, col1)
If col2 > 0 Then sm2 = rr.Offset(0, col2)
If col3 > 0 Then sm3 = rr.Offset(0, col3)
If col4 > 0 Then sm4 = rr.Offset(0, col4)
If col5 > 0 Then sm5 = rr.Offset(0, col5)
Sum_multiplecol = Sum_multiplecol + sm1 + sm2 + sm3 + sm4 + sm5
End If
Next rr
End Function

اگر از تابع فوق خواستید استفاده کنید، جهت بروز شدن تابع در زمان استفاده، کد زیر رو هم به رویداد open ورکبوک و همچنین رویداد
activate شیت مورد نظر اضافه کنید.

کد:

ThisWorkbook.ForceFullCalculation = True

فایل های پیوست شده

File 6.zip (20.4 کیلو بایت, مشاهدات 6)

**M_ExceL** · 2019/05/13, 02:15

نوشته اصلی توسط maziardm

فقط یه مشکلی هست و اینه که ممکنه ستون های دارای یک موضوع پشت سر هم نباشند. منظورم اینه که ابتدا فرض شده ستون های دارای یک موضوع (برای مثل حقوق مبنا یا حقوق تکمیلی) در شیت جدول حقوق پشت سر هم هستند. حالا فرض کنید که پشت سر هم نباشد. مثلا یک ردیف حقوق مبنا باشه و یه ردیف یا چند ردیف حقوق تکمیلی و بعدش دوباره حقوق پایه. اون موقع باید چیکار کرد.
با تشکر

تابع بروز رسانی و بهینه گردید :
با این تابع می تونید هر تعداد ستونی که مد نظر دارید رو بصورت شرطی جمع ببندید حتی ستون هایی که
به ترتیب کنار هم نیستند.

کد:

Function Multicol_sumif(cel As Range, rng As Range, ParamArray col() As Variant) As Variant
Dim rr As Range
For i = 0 To UBound(col)
For Each rr In rng
If cel = rr Then
sm = rr.Offset(0, col(i))
Multicol_sumif = Multicol_sumif + sm
End If
Next rr
Next i
End Function

ارگومان های تابع فوق بشرح زیر می باشد :
ارگومان cel ، سلولی که در صورت وجود اون دخل ارگومان rng (محدوده ای که cel در اون جستجو می شود)، سلول ستون مرد نظر رو جمع می بندد.
ارگومان col هم ستون یا ستون هایی هست که فاصله اون نسبت به ستون rng با وارد کردن عدد مورد نظر مشخص می گردد.
یا حق.

**maziardm** · 2019/05/13, 10:35

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

**M_ExceL** · 2019/05/13, 18:57

نوشته اصلی توسط maziardm

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

سلام، خواهش میکنم
از تابع Multicol_sumif (پست شماره 5) طبق فایل پیوست استفاده نمایید.

فایل های پیوست شده

File 7.zip (20.0 کیلو بایت, مشاهدات 20)

**maziardm** · 2019/05/15, 18:03

نوشته اصلی توسط m_excel

سلام، خواهش میکنم
از تابع multicol_sumif (پست شماره 5) طبق فایل پیوست استفاده نمایید.

سلام و تشکر دوباره
فقط اینکه فایل رو باز می کنم ارور #name? تو سلول های فرمول داده شده میده. نمی دونم چرا؟
با تشکر

**M_ExceL** · 2019/05/15, 19:29

نوشته اصلی توسط maziardm

سلام و تشکر دوباره
فقط اینکه فایل رو باز می کنم ارور #name? تو سلول های فرمول داده شده میده. نمی دونم چرا؟
با تشکر

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

**maziardm** · 2019/05/20, 11:51

نوشته اصلی توسط M_ExceL

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

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

**حسام بحرانی** · 2019/05/21, 01:10

Remove Personal

نوشته اصلی توسط maziardm

....
راستی تو فایلی که قرار دادید وقتی سیو می کنم هشدار زیر میاد. می دونید چطوری برطرف میشه؟
با تشکر

[ATTACH=CONFIG]18972[/ATTACH]

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

نکته:
توجه داشته باشید که در مرحله ی 6 تیک مربوط به Remove Personal رو همانند تصویر بردارید.
موفق باشید.

**maziardm** · 2019/05/22, 12:49

نوشته اصلی توسط حسام بحرانی

سلام دوست عزیز
به تصویر زیر نگاه کنید و مراحل رو به ترتیب شماره انجام بدید.
نکته:
توجه داشته باشید که در مرحله ی 6 تیک مربوط به Remove Personal رو همانند تصویر بردارید.
موفق باشید.

سلام و تشکر
عالی بود. درست شد.
با تشکر

**maziardm** · 2019/05/22, 13:00

نوشته اصلی توسط M_ExceL

سلام، خواهش میکنم
از تابع Multicol_sumif (پست شماره 5) طبق فایل پیوست استفاده نمایید.

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

**M_ExceL** · 2019/05/22, 18:12

نوشته اصلی توسط maziardm

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

سلام،
اگر سر ستون هایی که می فرمایید رو جابه جا یا عوض کنید، در این صورت شما باید داخل تابع هم شماره ستون ها (ارگومان col) رو اصلاح بفرمایید.
سوالی که هست اینه که چه لزومی داره که شما هر بار بخواهید سرستون هارو عوض کنید؟!
تابعی که بنده نوشتم هم برای همین هست، شما بعد از جا به جایی هم می تونید ستون هارو داخلش اصلاح کنید.
بله، میشه با vba کاری کرد که بصورت داینامیک این تغییرات اعمال شود.
اگر این تابع خواستتون رو براورده نمیکنه، که بنده بعید میدونم، منتظر راه حل سایر دوستان باشید.
یا حق.

**maziardm** · 2019/05/25, 12:33

نوشته اصلی توسط M_ExceL

سلام،
اگر سر ستون هایی که می فرمایید رو جابه جا یا عوض کنید، در این صورت شما باید داخل تابع هم شماره ستون ها (ارگومان col) رو اصلاح بفرمایید.
سوالی که هست اینه که چه لزومی داره که شما هر بار بخواهید سرستون هارو عوض کنید؟!
تابعی که بنده نوشتم هم برای همین هست، شما بعد از جا به جایی هم می تونید ستون هارو داخلش اصلاح کنید.
بله، میشه با vba کاری کرد که بصورت داینامیک این تغییرات اعمال شود.
اگر این تابع خواستتون رو براورده نمیکنه، که بنده بعید میدونم، منتظر راه حل سایر دوستان باشید.
یا حق.

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