فرمولي جايگزين goal seek

[sajad2000]

سلام به دوستان و اساتيد گرامي
ميخام فرمولي استفاده كنم كه كار ابزار goal seek رو انجام بده
چون اين ابزار براي زماني هست كه داده ها كم باشه نه اينكه زياد ميخام فرمولي باشه كه با يك كپي و پيست عدد مجهول بيرون بياد
مرسي

[Behnam]

لطفا یه فایل نمونه واسه واضح تر شدن آپلود کنید
میتونید از ابزارهای دیگه what if مثل data table و scenario استفاده کنید

[sajad2000]

البته فايل اصليم حاوي تعداد زيادي فرمول هست كه من بصورت فايل جديد بيرون آوردم

[Behnam]

یعنی چی که میخوام شماره 1 با شماره 2 بالانس بشه؟

[sajad2000]

اجازه بديد فايل اصلي بزارم تا بتونيم روي اين فايل عمليات انجام بديم
در اين فايل ميخام براساس قرارداد خالص قابل پرداخت را يكي كنم
به عنوان مثال از طريق goal seek عدد رو يكي كردم اما همانطور كه مشاهده مي كنيد تعداد داده هام زياده ميخام با روشي راحت تر اين كار رو انجام بدم

[sajad2000]

يه توضيحي كه در اين مورد بايد بگم عددي كه بايد اين دو رو بالانس كنه . اضافه كاري عاديه يعني بايد با اين عدد بازي كنم تا اون دو يكي بشه
اميدوارم منظورم رو واضح گفته باشم

[Behnam]

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

حالا جناب پارسا فکر کنم متوجه شدند

[Ali Parsaei]

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

[sajad2000]

دوست گرامي اضافه كاري عادي چيزي هست كه قرار داد رو با خالص پرداختي يكي ميكنه و عدد رو به همون مبلغ ميرسونه
مشكل من ميخام همه با هم اتوماتيك اين كار انجام بشه نه اينكه دونه دونه

[Ali Parsaei]

سلام،
دوست عزيز دقيقا" مطابق چيزي که ذيلا" عرض مي کنم عمل کنيد، انشا الله مشکلتان حل خواهد شد:
1- فيلتر ستون BH را برداريد
2- فرمول ستون BG را به صورتي بنويسيد که نتيجه رند شود، يعني در خانه BG2 بنويسيد:

=ROUND(VLOOKUP(B2;'لیست حقوق'!B:P;15;0);0) 

و تا آخرين رديف اطلاعات فرمول را درگ کنيد
حال يک ماژول تشکيل داده و کد زير را داخل آن بنويسيد:

Dim c As Range
 For Each c In Range("AP2:AP647")
  c.GoalSeek Goal:=c(1, 18), ChangingCell:=c(1, -31)
  Next 

در آخر ماژول را اجرا کنيد. فکر کنم حدود يک دقيقه محاسبات طول بکشد و سپس نتيجه دلخواه بايد حاصل شود.
توجه کنيد که کد فوق جهت همين ترکيب فعلي فايل شما نوشته شده و اگر ستونها را جابجا کنيد بايد کد را هم تغيير دهيد.
مشکلي بود در خدمتم. موفق باشيد.

[sajad2000]

من امتحان كردم نشد چرا همه اين كارا رو كردم ولي رو اون كد ماژوال ارور ميده
وآيا اين ماژوال چي رو داره ميگيره لطفا همون فايل رو برام قرار بديد اگر امكانش هست
ميگم ميشه اين كد رو در يك كماند ريخت كه با كليك بر اون ماژوال فعال شود
مرسي

[Ali Parsaei]

حتما" يک جاي کار را اشتباه کرده ايد من روي کامپيوتر خودم انجام دادم و شد. فقط يک دقيقه طول مي کشد،
ببينيد شما يک ماکرو ضبط کنيد و بدون اينکه کاري انجام دهيد ماکرو را خاتمه دهيد بعد داخل ماکرو در محيط vba کد مربوطه را وارد کنيد. به عکس پيوست دقت کنيد
ضمنا" قبلش فيلتري که گفتم برداشته فرمول round را بنويسيد و درگ کنيد يادتان نرود و الي اعداد با اعشار زياد با مشکل مواجه مي شود.
البته کد را داخل کماند هم مي توان ريخت. باز اگه مشکل حل نشد بگيد فايل را آپلود کنم. ماکرويي که من نوشتم با ctrl+shift+a فعال مي شود

[Ali Parsaei]

براي استفاده در جاي ديگر اولا" محدوده ap2 تا ap647 در کد را بايد تصحيح کنيد
ثانيا" اعداد 18 و -31 در خط سوم از پايين در کد مربوطه بايد اصلاح گردد. اين دو عدد نشان دهنده فاصله ستون اضافه کاري عادي و ستون قرارداد با ستون ap است يعني ستون اضافه کاري 31 ستون قبل از ستون خالص قابل پرداخت است و ستون قرارداد 18 ستون بعد از آن. با اين توضيحات مي توانيد فرمول را بعدا" براي جاي ديگر اصلاح کنيد. توجه کنيد که c در کد مربوطه يک متغيير است که به ترتيب خانه هاي ap2 تا ap647 را به خود خواهد گرفت.
براي فهم بهتر يک ماکرو ضبط کنيد و يک عمليات goal seek براي يک سطر انجام داده و ماکرو را متوقف کنيد. حال ماکرو بوجود آمده را با کد من مقايسه کنيد. در اصل کد من تغيير داده شده کد ماکرو به صورت پويا براي محدوده مورد نظر است