اغلب ، باید نتیجه نهایی را برای ترکیب های مختلف داده های ورودی محاسبه کنید. بنابراین ، کاربر قادر خواهد بود تا گزینه های ممکن برای اقدامات را ارزیابی کند ، افرادی را انتخاب کند که نتایج تعامل او را راضی کند ، و در آخر ، بهینه ترین گزینه را انتخاب کند. در اکسل ، برای انجام این کار ، یک ابزار ویژه وجود دارد - "جدول داده" (جدول جایگزینی) بیایید نحوه استفاده از آن را برای تکمیل سناریوهای فوق دریابیم.
همچنین بخوانید: انتخاب پارامتر در اکسل
با استفاده از جدول داده ها
ساز "جدول داده" این در نظر گرفته شده است که برای تغییرات مختلف یک یا دو متغیر تعریف شده ، محاسبه نتیجه را نشان می دهد. پس از محاسبه ، تمام گزینه های ممکن در قالب جدول ظاهر می شود که به آن ماتریس تجزیه و تحلیل عاملی گفته می شود. "جدول داده" به گروهی از ابزارها اشاره دارد "اگر تجزیه و تحلیل شود"، که بر روی روبان در زبانه قرار داده شده است "داده" در بلوک "کار با داده ها". قبل از اکسل 2007 ، این ابزار خوانده می شد جدول جایگزینی، که حتی دقیق تر جوهر آن را از نام فعلی بازتاب می داد.
در جدول جستجو می توان در بسیاری موارد استفاده کرد. به عنوان مثال ، یک گزینه معمولی زمانی است که شما نیاز به محاسبه مبلغ پرداخت وام ماهانه برای تغییرات مختلف دوره اعتبار و مبلغ وام یا دوره اعتبار سنجی و نرخ بهره دارید. همچنین از این ابزار می توان در تحلیل مدل های پروژه های سرمایه گذاری استفاده کرد.
اما همچنین باید توجه داشته باشید که استفاده بیش از حد از این ابزار می تواند منجر به ترمز سیستم شود ، زیرا داده ها به طور مداوم بازگو می شوند. بنابراین ، در آرایه های جدول کوچک توصیه می شود مشکلات مشابه را با استفاده از این ابزار حل نکنید ، بلکه از فرمول کپی کردن با استفاده از نشانگر fill استفاده کنید.
برنامه توجیه شده "جداول داده" فقط در رده های جدول بزرگ است ، هنگامی که کپی کردن فرمول ها می تواند زمان زیادی را ببرد ، و در طی روش خود احتمال خطا افزایش می یابد. اما در این حالت ، توصیه می شود تا محاسبه مجدد خودکار فرمول ها در محدوده جدول تعویض انجام شود تا از بار غیر ضروری روی سیستم جلوگیری شود.
تفاوت اصلی بین کاربردهای مختلف جدول داده تعداد متغیرهای درگیر در محاسبه است: یک متغیر یا دو.
روش 1: از ابزار با یک متغیر استفاده کنید
فوراً بیایید وقتی جدول داده با یک مقدار متغیر استفاده می شود گزینه را بررسی کنیم. نمونه بارزترین نمونه وام را بگیرید.
بنابراین ، در حال حاضر شرایط وام زیر به ما پیشنهاد می شود:
- مدت وام - 3 سال (36 ماه)؛
- مبلغ وام - 900000 روبل؛
- نرخ بهره - 5/12٪ در سال.
پرداخت ها در پایان دوره پرداخت (ماه) طبق برنامه سالیانه ، یعنی در سهام برابر انجام می شود. در عین حال ، در ابتدای کل مدت وام ، بخش قابل توجهی از پرداخت ها مربوط به پرداخت بهره است ، اما با کم شدن بدن ، میزان پرداخت سود کاهش می یابد و میزان بازپرداخت بدن نیز افزایش می یابد. کل پرداخت ، همانطور که در بالا ذکر شد ، بدون تغییر باقی می ماند.
لازم است محاسبه کنید که مبلغ پرداخت ماهانه از جمله بازپرداخت بدن وام و پرداخت های بهره چه مقدار خواهد بود. برای این کار ، اکسل یک اپراتور دارد PMT.
PMT متعلق به گروه توابع مالی است و وظیفه آن محاسبه نوع پرداخت وام ماهانه سالانه بر اساس میزان وام ، مدت وام و نرخ بهره است. نحو این عملکرد به صورت ارائه شده است
= PLT (نرخ؛ nper؛ ps؛ bs؛ نوع)
پیشنهاد - استدلالی که نرخ سود پرداخت اعتبار را تعیین می کند. شاخص برای دوره تنظیم می شود. دوره پرداخت ما برابر با یک ماه است. بنابراین نرخ سالانه 5/12٪ باید بر حسب ماه در یک سال ، یعنی 12 تقسیم شود.
"نپر" - استدلالی که تعداد دوره های کل مدت وام را تعیین می کند. در مثال ما دوره یک ماه و مدت وام 3 سال یا 36 ماه است. بنابراین ، تعداد دوره ها در اوایل 36 خواهد بود.
"PS" - استدلالی که ارزش فعلی وام را تعیین می کند ، یعنی اندازه بدنه وام در زمان صدور آن است. در مورد ما ، این رقم 900000 روبل است.
"BS" - استدلالی که نشانگر اندازه بدنه وام در زمان پرداخت کامل است. طبیعتا این شاخص برابر با صفر خواهد بود. این استدلال اختیاری است. اگر آن را جست و جو کنید ، فرض بر این است که برابر با عدد "0" است.
"نوع" - همچنین یک استدلال اختیاری. وی اعلام می کند که دقیقاً چه پرداختی انجام می شود: در آغاز دوره (پارامتر - "1") یا در پایان دوره (پارامتر - "0") همانطور که به یاد می آوریم ، پرداخت ما در پایان ماه تقویم انجام می شود ، یعنی ارزش این استدلال برابر خواهد بود "0". اما با توجه به اینکه این شاخص اجباری نیست و به طور پیش فرض در صورت عدم استفاده از مقدار برابر است "0"سپس در مثال ذکر شده می توان آنرا حذف کرد.
- بنابراین ، ما به محاسبه ادامه می دهیم. سلول را روی برگه انتخاب کنید که مقدار محاسبه شده نمایش داده شود. روی دکمه کلیک کنید "درج عملکرد".
- شروع می کند جادوگر ویژگی. ما به سمت دسته حرکت می کنیم "مالی"، نام را از لیست انتخاب کنید "PLT" و روی دکمه کلیک کنید "خوب".
- پس از این ، پنجره آرگومان های عملکرد بالا فعال می شوند.
مکان نما را در قسمت میدان قرار دهید پیشنهادو بعد از آن روی سلول روی برگه با مقدار نرخ بهره سالانه کلیک می کنیم. همانطور که مشاهده می کنید ، مختصات آن بلافاصله در قسمت نمایش داده می شوند. اما ، همانطور که به یاد می آوریم ، ما نیاز به یک نرخ ماهانه داریم ، و بنابراین نتیجه را با 12 تقسیم می کنیم (/12).
در زمینه "نپر" به همین روش وارد مختصات سلولهای مدت وام می شویم. در این حالت نیازی به اشتراک گذاری چیزی نیست.
در زمینه روان باید مختصات سلول حاوی ارزش بدنه وام را مشخص کنید. ما این کار را می کنیم همچنین یک علامت را در مقابل مختصات نمایش داده شده قرار می دهیم "-". واقعیت این است که عملکرد PMT بطور پیش فرض با علامت منفی نتیجه نهایی را می دهد ، درست با در نظر گرفتن ضرر پرداخت ماهانه وام. اما برای شفافیت استفاده از جدول داده ها ، باید این عدد مثبت باشد. بنابراین ، ما یک علامت می گذاریم منهای قبل از یکی از آرگومان های عملکرد. ضرب شناخته شده است منهای در منهای در پایان می دهد به علاوه.
داخل مزارع "Bs" و "نوع" داده به هیچ وجه وارد نشده است. روی دکمه کلیک کنید "خوب".
- پس از آن ، اپراتور نتیجه کل پرداخت ماهانه را در یک سلول از پیش تعیین شده محاسبه و نمایش می دهد - 30108,26 روبل اما مسئله این است که وام گیرنده قادر است حداکثر 29000 روبل در ماه بپردازد ، یعنی او یا باید شرایطی را ارائه دهد که بانکی با نرخ سود کمتری داشته باشد ، یا بدنه وام را کاهش دهد ، یا مدت وام را افزایش دهد. جدول جستجو به ما کمک می کند تا گزینه های مختلفی را کشف کنیم.
- ابتدا از جدول جستجو با یک متغیر استفاده کنید. بیایید ببینیم که میزان پرداخت ماهانه اجباری ماهانه با تغییرات مختلف در نرخ سالانه ، از ابتدا چگونه تغییر خواهد کرد 9,5% در سال و پایان دادن 12,5% در سال در افزایش 0,5%. همه شرایط دیگر بدون تغییر باقی مانده است. ما یک جدول جدول را ترسیم می کنیم ، نام ستون های آن با تغییرات مختلف نرخ بهره مطابقت دارد. با این خط "پرداخت ماهانه" ترک همانطور که هست سلول اول آن باید حاوی فرمولی باشد که قبلاً محاسبه کردیم. برای اطلاعات بیشتر می توانید خطوط اضافه کنید "کل مبلغ وام" و "کل بهره". ستونی که محاسبه در آن قرار دارد بدون هدر انجام می شود.
- در مرحله بعد ، کل مبلغ وام را تحت شرایط فعلی محاسبه می کنیم. برای انجام این کار ، اولین سلول ردیف را انتخاب کنید "کل مبلغ وام" و محتویات سلول را ضرب کنید "پرداخت ماهانه" و "مدت وام". پس از آن بر روی دکمه کلیک کنید وارد شوید.
- برای محاسبه مقدار کل بهره تحت شرایط فعلی ، به همین ترتیب مقدار بدنه وام را از کل مبلغ وام کم می کنیم. برای نمایش نتیجه روی صفحه ، روی دکمه کلیک کنید وارد شوید. بنابراین ، مبلغی را که هنگام بازپرداخت وام بیش از آن پرداخت کردیم ، دریافت می کنیم.
- اکنون زمان آن رسیده است که ابزار را بکار بگیرید "جدول داده". ما کل آرایه جدول را به جز نام های سطر انتخاب می کنیم. پس از آن ، به برگه بروید "داده". روی دکمه روی روبان کلیک کنید "اگر تجزیه و تحلیل شود"که در گروه ابزار قرار دارد "کار با داده ها" (در اکسل 2016 ، گروهی از ابزارها "پیش بینی") سپس یک منوی کوچک باز می شود. در آن ما یک موقعیت را انتخاب می کنیم "جدول داده ها ...".
- یک پنجره کوچک باز می شود ، که نامیده می شود "جدول داده". همانطور که می بینید ، این دو رشته دارد. از آنجا که ما با یک متغیر کار می کنیم ، فقط به یکی از آنها نیاز داریم. از آنجا که ستون متغیر را با ستون تغییر می دهیم ، از این فیلد استفاده خواهیم کرد جایگزین مقادیر ستون در. مکان نما را در آنجا تنظیم کنید ، و سپس روی سلول در مجموعه داده اصلی که حاوی درصد فعلی است ، کلیک کنید. پس از نمایش مختصات سلول در قسمت ، روی دکمه کلیک کنید "خوب".
- ابزار محاسبه و پر کردن کل جدول جدولی با مقادیر متناسب با گزینه های مختلف برای نرخ بهره. اگر مکان نما را در هر عنصر از این قسمت جدول قرار دهید ، می بینید که نوار فرمول فرمول معمول برای محاسبه پرداخت را نشان نمی دهد بلکه فرمول خاصی برای یک آرایه غیرقابل توصیف است. یعنی اکنون تغییر مقادیر در سلولهای فردی غیرممکن است. شما می توانید فقط نتایج محاسبه را حذف کنید ، و نه جداگانه.
علاوه بر این ، می بینید که پرداخت ماهانه 12.5٪ در سال به دست آمده در نتیجه اعمال جدول جستجوی مربوط به مقدار همان میزان علاقه ای است که ما با استفاده از عملکرد دریافت کردیم PMT. این یک بار دیگر صحت محاسبه را اثبات می کند.
پس از تجزیه و تحلیل این مجموعه جدول ، باید گفت که همانطور که مشاهده می کنید ، تنها با نرخ 9.5٪ در سال ، ما یک سطح پرداخت ماهانه قابل قبول (کمتر از 29000 روبل) دریافت می کنیم.
درس: محاسبه پرداخت سالانه در اکسل
روش 2: از ابزار با دو متغیر استفاده کنید
البته ، پیدا کردن در حال حاضر بانک ها که وام را با 9.5 درصد در سال صادر می کنند ، در غیر اینصورت غیرممکن است بسیار دشوار است. بنابراین ، خواهیم دید که چه گزینه هایی برای سرمایه گذاری در سطح قابل قبول پرداخت ماهانه برای ترکیب های مختلف متغیرهای دیگر وجود دارد: اندازه بدنه وام و مدت وام. در این حالت نرخ بهره بدون تغییر (12.5٪) باقی خواهد ماند. در حل این مشکل ابزاری به ما کمک می کند. "جدول داده" با استفاده از دو متغیر.
- ما یک آرایه جدول جدید ترسیم می کنیم. اکنون در نام ستون ها مدت وام (از 2 قبل 6 سال در ماه در افزایش یک سال) ، و در سطر - اندازه بدن وام (از 850000 قبل 950000 روبل در افزایشی 10000 روبل) در این حالت ، یک پیش شرط این است که سلول که فرمول محاسبه در آن قرار دارد (در مورد ما PMT) ، واقع در مرز نام سطر و ستون. بدون این شرط ، در هنگام استفاده از دو متغیر ، ابزار کار نخواهد کرد.
- سپس کل محدوده جدول حاصل از جمله نام ستون ها ، ردیف ها و یک سلول را با فرمول انتخاب کنید PMT. برو به برگه "داده". مانند دفعه قبل ، روی دکمه کلیک کنید "اگر تجزیه و تحلیل شود"، در گروه ابزار "کار با داده ها". در لیستی که باز می شود ، را انتخاب کنید "جدول داده ها ...".
- پنجره ابزار شروع می شود "جدول داده". در این حالت ، ما به هر دو زمینه نیاز داریم. در زمینه جایگزین مقادیر ستون در مختصات سلول حاوی مدت وام را در داده های اولیه نشان می دهد. در زمینه "مقادیر جایگزین سطر به ردیف در" آدرس سلول از پارامترهای اولیه حاوی ارزش بدن وام را مشخص کنید. بعد از وارد کردن تمام داده ها روی دکمه کلیک کنید "خوب".
- برنامه محاسبه را انجام می دهد و محدوده جدول را با داده ها پر می کند. در تقاطع ردیف ها و ستون ها اکنون می توان مشاهده کرد که پرداخت ماهانه دقیقاً با میزان مربوط به سود سالانه و مدت وام ذکر شده دقیقاً چه خواهد بود.
- همانطور که می بینید ، ارزشهای زیادی وجود دارد. برای حل سایر مشکلات ، ممکن است موارد دیگری نیز وجود داشته باشد. بنابراین ، برای نمایش نتایج حاصل از بصری و فوراً تعیین اینکه کدام مقادیر شرایط را برآورده نمی کند ، می توانید از ابزارهای تجسم استفاده کنید. در مورد ما ، این قالب بندی مشروط خواهد بود. ما تمام مقادیر جدول را انتخاب می کنیم ، به استثنای عناوین سطر و ستون.
- به برگه بروید "خانه" و بر روی آیکون کلیک کنید قالب بندی شرطی. در بلوک ابزار واقع شده است. سبک ها روی نوار در منوی باز شده ، را انتخاب کنید قوانین انتخاب سلول. در لیست اضافی ، بر روی موقعیت کلیک کنید "کمتر ...".
- پس از این ، پنجره تنظیمات قالب بندی شرطی باز می شود. در قسمت سمت چپ مقدار کمتری را انتخاب کنید که سلولها انتخاب شوند. همانطور که به یاد می آوریم ، از این شرط راضی هستیم که پرداخت وام ماهانه کمتر از باشد 29000 روبل ما این شماره را وارد می کنیم در قسمت درست ، می توانید رنگ برجسته را انتخاب کنید ، اگرچه می توانید به طور پیش فرض آن را ترک کنید. پس از وارد کردن تمام تنظیمات مورد نیاز ، روی دکمه کلیک کنید "خوب".
- پس از آن ، تمام سلول هایی که مقادیر آنها با شرایط فوق مطابقت دارد برجسته می شوند.
با تجزیه و تحلیل آرایه جدول ، می توان نتیجه گیری کرد. همانطور که مشاهده می کنید ، با مدت وام موجود (36 ماه) ، برای سرمایه گذاری در مبلغ ذکر شده مبلغ پرداخت ماهانه ، باید وام حداکثر 860000.00 روبل ، یعنی 40،000 کمتر از آنچه که در ابتدا برنامه ریزی شده بود ، بگیریم.
اگر ما هنوز قصد وام 900000 روبل را داشته باشیم ، مدت اعتبار وام باید 4 سال (48 ماه) باشد. فقط در این حالت ، پرداخت ماهانه بیش از حد تعیین شده 29000 روبل نخواهد بود.
بنابراین ، با استفاده از این آرایه جدول و تجزیه و تحلیل جوانب و منافع هر گزینه ، وام گیرنده می تواند تصمیم خاصی را در مورد شرایط وام اتخاذ کند و از بین همه گزینه های مناسب ، گزینه مناسب را انتخاب کند.
البته از جدول جستجوی می توان نه تنها برای محاسبه گزینه های اعتباری بلکه برای حل بسیاری از مشکلات دیگر استفاده کرد.
درس: قالب بندی شرطی در اکسل
به طور کلی باید توجه داشت که جدول جستجوی ابزاری بسیار مفید و نسبتاً ساده برای تعیین نتیجه برای ترکیبهای مختلف متغیرها است. با استفاده از قالب بندی شرطی به طور همزمان ، علاوه بر این ، می توانید اطلاعات دریافت شده را تجسم کنید.
