على الرغم من أن قائمة وظائف Excel الطويلة هي واحدة من أكثر الميزات جاذبية لتطبيق جداول البيانات من Microsoft ، إلا أن هناك القليل من الأحجار الكريمة غير المستغلة التي تعزز هذه الوظائف. من الأدوات التي غالبًا ما يتم إغفالها هي "تحليل ماذا لو".

تنقسم أداة تحليل ماذا لو^{(What-If Analysis)} في Excel إلى ثلاثة مكونات رئيسية. الجزء الذي تمت مناقشته هنا هو ميزة Goal Seek القوية التي تتيح لك العمل بشكل عكسي من وظيفة وتحديد المدخلات اللازمة للحصول على الإخراج المطلوب من صيغة في خلية. تابع القراءة لمعرفة كيفية استخدام أداة تحليل What-If Analysis Goal Seek في Excel.^{(What-If Analysis Goal Seek)}

مثال على أداة Goal Seek في Excel

لنفترض أنك تريد الحصول على قرض رهن عقاري لشراء منزل وأنت قلق بشأن كيفية تأثير سعر الفائدة على القرض على المدفوعات السنوية. مبلغ الرهن العقاري هو 100000 دولار وسوف تسدد القرض على مدار 30 عامًا.

باستخدام وظيفة PMT^(PMT) في Excel ، يمكنك بسهولة معرفة المدفوعات السنوية إذا كان معدل الفائدة 0٪. من المحتمل أن يبدو جدول البيانات كما يلي:

تمثل الخلية في A2 معدل الفائدة السنوي ، والخلية في B2 هي طول القرض بالسنوات ، والخلية في C2 هي مبلغ قرض الرهن العقاري. الصيغة في D2 هي:

=PMT(A2,B2,C2)

ويمثل المدفوعات السنوية لرهن عقاري لمدة 30 عامًا ، 100،000 دولار بفائدة 0٪. لاحظ^(Notice) أن الرقم في D2 سالب لأن Excel يفترض أن المدفوعات عبارة عن تدفق نقدي سلبي من وضعك المالي.

لسوء الحظ ، لن يقرضك أي مقرض رهن عقاري 100000 دولار بفائدة 0٪. لنفترض^(Suppose) أنك قمت ببعض التفكير واكتشفت أنه يمكنك سداد 6000 دولار سنويًا في مدفوعات الرهن العقاري. أنت الآن تتساءل ما هو أعلى سعر فائدة يمكنك تحمله للقرض للتأكد من أنك لن تدفع أكثر من 6000 دولار في السنة.

سيبدأ العديد من الأشخاص في هذه الحالة ببساطة في كتابة الأرقام في الخلية A2 حتى يصل الرقم في D2 إلى ما يقرب من 6000 دولار. ومع ذلك ، يمكنك جعل Excel يقوم بالعمل نيابة عنك باستخدام أداة What-If Analysis Goal Seek . بشكل أساسي ، ستجعل Excel يعمل بشكل عكسي من النتيجة في D4 حتى يصل إلى معدل فائدة يلبي الحد الأقصى لمدفوعاتك البالغة 6000 دولار.

ابدأ بالنقر فوق علامة التبويب البيانات^(Data) على الشريط^(Ribbon) وتحديد موقع زر تحليل ماذا لو في قسم ^{(What-If Analysis)}أدوات البيانات^{(Data Tools)} . انقر فوق الزر What-If Analysis واختر Goal Seek من القائمة.

يفتح Excel نافذة صغيرة ويطلب منك إدخال ثلاثة متغيرات فقط. يجب أن يكون متغير Set Cell خلية تحتوي على صيغة. ^{(Set Cell)}في مثالنا هنا ، إنه D2 . متغير To Value هو المقدار الذي تريد أن تكون الخلية عند D2 في نهاية التحليل.

بالنسبة لنا ، هو -6000 . تذكر أن Excel يرى المدفوعات كتدفق نقدي سلبي. متغير By Changing Cell هو معدل الفائدة الذي تريد أن يجده Excel لك حتى يكلفك الرهن العقاري الذي يبلغ 100000 دولار 6000 دولار فقط في السنة. لذلك ، استخدم الخلية A2 .

انقر فوق الزر " موافق^(OK) " وقد تلاحظ أن Excel يومض مجموعة من الأرقام في الخلايا المعنية حتى تتقارب التكرارات في النهاية على رقم نهائي. في حالتنا ، يجب أن تقرأ الخلية في A2 الآن حوالي 4.31٪.

يخبرنا هذا التحليل أنه من أجل عدم إنفاق أكثر من 6000 دولار سنويًا على قرض عقاري مدته 30 عامًا بقيمة 100000 دولار ، فأنت بحاجة إلى تأمين القرض بنسبة لا تزيد عن 4.31٪. إذا كنت ترغب في الاستمرار في إجراء تحليلات ماذا لو ، فيمكنك تجربة مجموعات مختلفة من الأرقام والمتغيرات لاستكشاف الخيارات المتاحة لك عند محاولة تأمين سعر فائدة جيد على الرهن العقاري.

تعد أداة What-If Analysis Goal Seek من Excel تكملة قوية للوظائف والصيغ المختلفة الموجودة في جدول البيانات النموذجي. من خلال العمل بشكل عكسي من نتائج الصيغة في خلية ، يمكنك استكشاف المتغيرات المختلفة في حساباتك بشكل أكثر وضوحًا.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s lоng list of functions is one of the most enticing features of Microsoft’s sprеadsheet application, there a few undеrutilized gems that enhance these fυnctіons. One often-overlooked tool is the What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• كيفية استخدام تحليل ماذا لو في Excel

• كيفية استخدام ميزة Speak Cells في Excel

• 2 طرق لاستخدام وظيفة تبديل Excel

• كيفية فهم تحليل ماذا لو في Microsoft Excel

• كيفية حذف الأسطر الفارغة في Excel

• كيفية دمج البيانات في ملفات Excel متعددة

• استخدم لوحة المفاتيح لتغيير ارتفاع الصف وعرض العمود في Excel

• كيفية عمل مخطط دائري في Excel

• كيفية إصلاح أخطاء # N / A في صيغ Excel مثل VLOOKUP

• كيفية تحريك الأعمدة في Excel

• ربط برنامج Excel بـ MySQL

• كيفية إنشاء VBA ماكرو أو برنامج نصي في Excel

• استخدم أسماء النطاق الديناميكي في Excel للقوائم المنسدلة المرنة

• كيفية استخدام Flash Fill في Excel

• لن يتم فتح Microsoft Outlook؟ 10 طرق لإصلاح

• كيفية تتبع المعالين في Excel

• كيفية الفرز حسب التاريخ في Excel

• جداول بيانات Google مقابل Microsoft Excel - ما هي الاختلافات؟

• ما هي مصفوفة VBA في Excel وكيفية البرمجة الأولى

• كيفية كتابة صيغة IF / بيان في Excel