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