جدول داده مایکروسافت اکسل
اغلب لازم است محاسبه نتیجه نهایی برای ترکیب های مختلف داده های ورودی باشد. بنابراین، کاربر قادر خواهد بود تمام گزینه های ممکن برای عمل را ارزیابی کند، آنهایی را انتخاب کنند که نتیجه تعامل آنها را ارضا کند، و در نهایت گزینه مطلوب را انتخاب کنید. در اکسل، برای انجام این کار، یک ابزار خاص - "جدول داده" ( "جدول گرین کارت" ) وجود دارد. بیایید نحوه استفاده از آن را برای انجام سناریوهای فوق پیدا کنیم.
همچنین ببینید: انتخاب اکسل
محتوا
با استفاده از جدول داده
ابزار Data Table برای محاسبه نتیجه با تغییرات مختلف یک یا دو متغیر خاص طراحی شده است. بعد از محاسبه، تمام گزینه های ممکن در قالب یک جدول ظاهر می شود که ماتریس تحلیل عامل است. "جدول داده ها" به گروه ابزار تجزیه و تحلیل "چه می شود " اشاره دارد که بر روی نوار در زبانه "Data" در بلوک "Working with Data" قرار می گیرد. قبل از اکسل 2007، این ابزار "جدول گرین کارت" نامیده می شد ، که حتی دقیق تر ذات خود را نسبت به نام فعلی منعکس می کرد.
جدول جستجو در بسیاری موارد می تواند مورد استفاده قرار گیرد. برای مثال، یک گزینه معمول زمانی است که شما نیاز به محاسبه مبلغ پرداخت وام ماهانه با تغییرات مختلف دوره اعتباری و مبلغ وام یا دوره اعتباری و نرخ بهره دارید. این ابزار همچنین می تواند در هنگام تحلیل مدل های سرمایه گذاری مورد استفاده قرار گیرد.
اما شما همچنین باید بدانید که استفاده بیش از حد از این ابزار می تواند به ترمز سیستم منجر شود، زیرا داده ها به طور مداوم مجددا محاسبه می شوند. بنابراین، توصیه می شود که از این ابزار در آرایه های جدول کوچک برای حل مشکلات مشابه استفاده نکنید، اما برای کپی سازی فرمول ها با استفاده از نشانگر پر شدن، از آن استفاده کنید.
استفاده از "جدول داده ها" تنها در محدوده جدول بزرگ توجیه می شود، در حالی که فرمول های کپی می توانند زمان زیادی را اتخاذ کنند و در طول روش خود، احتمال اشتباهات افزایش می یابد. اما حتی در این مورد، توصیه می شود که محاسبه خودکار فرمول ها را در محدوده جدول جستجو غیر فعال کنید تا از بار غیر ضروری در سیستم جلوگیری شود.
تفاوت اصلی بین استفاده های مختلف از یک جدول داده، تعداد متغیرهای درگیر در محاسبه است: یک متغیر یا دو.
روش 1: استفاده از ابزار با یک متغیر
بلافاصله گزینه ای را در نظر بگیرید که یک جدول داده با یک مقدار متغیر استفاده می شود. مثال معمولی از وام گرفتن را بیابید.
بنابراین، در حال حاضر ما شرایط اعتباری زیر را ارائه می دهیم:
- مدت وام - 3 سال (36 ماه)؛
- مبلغ وام - 900000 روبل؛
- نرخ بهره - 12.5٪ در سال.
پرداخت ها در پایان دوره پرداخت (ماه) با استفاده از طرح سالانه انجام می شود، یعنی در سهام های مساوی. در عین حال، در ابتدای دوره کل وام، پرداخت های بهره بخش مهمی از پرداخت ها را تشکیل می دهند، اما با کاهش جرم، پرداخت های بهره کاهش می یابد و میزان بازپرداخت بدن افزایش می یابد. پرداخت کل، همانطور که در بالا ذکر شد، باقی می ماند.
لازم است محاسبه مقدار مبلغ پرداخت ماهانه، که شامل بازپرداخت وام و پرداخت بهره است. برای این، اکسل یک اپراتور PMT دارد.
PMT به گروهی از توابع مالی اشاره دارد و وظیفه آن محاسبه پرداخت وام ماهانه سالانه بر اساس مبلغ وام بدن، وام و نرخ بهره است. نحو برای این عملکرد به شرح زیر است.
=ПЛТ(ставка;кпер;пс;бс;тип)
"نرخ" - یک استدلال که نرخ بهره اعتباری را تعیین می کند. این شاخص برای دوره تعیین شده است. دوره پرداخت ما یک ماه است. بنابراین، نرخ سالیانه 12.5٪ باید به چند ماه در یک سال تقسیم شود، یعنی 12.
"Kper" یک بحث است که تعداد دوره ها را برای کل دوره وام تعیین می کند. در مثال ما این دوره یک ماه است و مدت وام 3 سال یا 36 ماه است. بنابراین، تعداد دوره ها در اوایل 36 سالگی خواهد بود.
"PS" یک استدلال است که ارزش فعلی وام را تعیین می کند، یعنی آن اندازه وام بدن در زمان صدور آن است. در مورد ما این رقم 900000 روبل است.
"BS" استدلالی است که نشان می دهد اندازه وام بدن در زمان بازپرداخت کامل است. به طور طبیعی این شاخص برابر با صفر است. این استدلال اختیاری است. اگر شما آن را رد کنید، فرض می شود که آن برابر با عدد "0" است.
نوع نیز یک استدلال اختیاری است. او در مورد زمانی که پرداخت انجام می شود، اطلاع می دهد: در ابتدای دوره (پارامتر "1" ) یا در پایان دوره (پارامتر "0" ) است. همانطور که به یاد می آوریم، پرداخت ما در پایان ماه تقویم انجام می شود، یعنی ارزش این استدلال برابر با "0" است . اما، با توجه به این که این شاخص اجباری نیست و به طور پیش فرض، اگر از آن استفاده نشود، ارزش فرض شده برابر با "0" است ، سپس در این مثال می توان آن را حذف کرد.
- بنابراین، ما به محاسبه ادامه می دهیم. سلول بر روی صفحه که در آن مقدار محاسبه نمایش داده خواهد شد را انتخاب کنید. بر روی دکمه "درج تابع" کلیک کنید .
- جادوگر تابع شروع می شود. انتقال به رده "مالی" را تغییر دهید ، از لیست نام "PLT" را انتخاب کنید و روی دکمه "OK" کلیک کنید.
- پس از این، فعال کردن پنجره استدلال تابع بالا انجام می شود.
مکان را در فیلد «Rate» قرار دهید و سپس روی سلول روی صفحه با ارزش نرخ بهره سالیانه کلیک کنید. همانطور که می بینید، مختصات آن بلافاصله در این زمینه نمایش داده می شود. اما، به یاد داشته باشید، ما به نرخ ماهانه نیاز داریم، و بنابراین نتیجه را به 12 ( / 12 ) تقسیم می کنیم.
در زمینه "Nper" به همان شیوه ما مختصات سلول های وام را وارد می کنیم. در این مورد، هیچ چیز نیازی به تقسیم ندارد.
در قسمت "PS" شما باید مختصات سلول حاوی اندازه بدن وام را مشخص کنید. ما این کار را انجام می دهیم ما همچنین نشانه "-" را در مقابل مختصات نمایش داده شده قرار داده ایم. واقعیت این است که عملکرد PMT ، به طور پیش فرض، نتیجه نهایی را با نشانه منفی، به درستی با توجه به پرداخت وام ماهانه از دست دادن. اما برای وضوح، ما باید جدول داده ها مثبت باشد. بنابراین، ما یک علامت منفی در مقابل یکی از عبارات تابع قرار داده ایم. همانطور که می دانید، ضرب منفی " با " منهای " در نهایت به " یک "اضافه می شود .
در زمینه "BS" و "Type" اطلاعات در همه وارد نشده است. روی دکمه OK کلیک کنید.
- پس از آن، اپراتور محاسبه و نمایش کل مبلغ ماهانه در یک سلول پیش تعیین شده - 30108.26 روبل. اما مشکل این است که وام گیرنده قادر است حداکثر 29000 روبل را در ماه پرداخت کند، یعنی او باید شرایط ارائه بانک را با نرخ بهره پایین تر یا کم کردن وام و یا تمدید مدت وام افزایش دهد. محاسبه گزینه های مختلف برای عمل به جدول جستجوی ما کمک خواهد کرد.
- برای شروع از table lookup با یک متغیر استفاده کنید. بیایید ببینیم که ارزش پرداخت اجباری ماهانه با تغییرات مختلف نرخ سالانه متفاوت است، از 9.5 درصد در سال و با پایان دادن به 12.5 درصد در سال با افزایش 0.5 درصد . تمام شرایط دیگر بدون تغییر باقی مانده است. یک جدول جدول را رسم کنید، نام ستون های آن با تغییرات مختلف نرخ بهره مطابقت دارد. در این حالت، خط "پرداخت ماهانه" به عنوان آن است که باقی مانده است. اولین سلول آن باید حاوی فرمولی است که قبلا محاسبه کردیم. برای کسب اطلاعات بیشتر، می توانید خط "مبلغ کل وام" و "کل مبلغ سود" را اضافه کنید . ستون که در آن محاسبه واقع شده است بدون هدر انجام می شود.
- بعد، کل مبلغ وام را در شرایط فعلی محاسبه می کنیم. برای انجام این کار ابتدا سلول "مجموع وام" را انتخاب کنید و محتویات "پرداخت ماهانه" و " بلوغ وام " را چند برابر کنید. پس از آن بر روی کلید Enter کلیک کنید .
- برای محاسبه کل مبلغ منافع در شرایط کنونی، به طور مشابه، ما مبلغ وام را از مبلغ کل وام حذف می کنیم. برای نمایش نتیجه بر روی صفحه، بر روی دکمه Enter کلیک کنید. به این ترتیب، مبلغی را دریافت می کنیم که در هنگام بازپرداخت وام به ما پرداخت می شود.
- اکنون زمان استفاده از ابزار Data Table است . کل آرایه جدول را انتخاب کنید، به جز نام ردیفها. پس از آن به تب "داده" بروید . بر روی دکمه روی نوار تحلیل آن چه که در آن قرار دارد، در گروه ابزار «کار با داده ها» (در Excel 2016 گروه ابزار «پیش بینی» ) قرار دارد. سپس یک منوی کوچک باز می شود. در آن، موقعیت "جدول داده ..." را انتخاب کنید .
- یک پنجره کوچک باز می شود که "جدول داده" نامیده می شود. همانطور که می بینید، آن دو زمینه دارد. از آنجایی که ما با یک متغیر کار میکنیم، فقط یک مورد نیاز داریم. از آنجا که تغییرات متغیرهای ما در ستون رخ می دهد، از "مقادیر جایگزین در ستون ها در" فیلد استفاده می کنیم. ما مکان نما را در آن قرار می دهیم و سپس روی سلول موجود در مجموعه داده اصلی کلیک می کنیم که حاوی مقدار فعلی درصد است. بعد از اینکه مختصات سلول در این قسمت نمایش داده می شود، بر روی دکمه "OK" کلیک کنید.
- ابزار محاسبه و تمام محدوده جدول را با مقادیری که با گزینه های مختلف نرخ بهره مطابقت دارد، محاسبه می کند. اگر مکان نما را در هر عنصری از یک اتاق جداگانه داده شده قرار دهید، می توانید ببینید که نوار فرمول یک فرمول محاسبه پرداخت منظم را نمایش نمی دهد، بلکه فرمول خاصی از یک آرایه بدون شکست است. بدین معنی است که ارزش سلول های فردی را نمی توان تغییر داد. نتایج محاسبات حذف تنها می تواند همه با هم، و نه جداگانه باشد.
علاوه بر این، می توان اشاره کرد که ارزش پرداخت ماهانه در 12.5٪ در سال، که در نتیجه استفاده از جدول جستجو به دست آمده، مربوط به ارزش با همان نرخ بهره است که ما با استفاده از عملکرد PMT دریافت کردیم. این یک بار دیگر صحت محاسبات را ثابت می کند.
پس از تجزیه و تحلیل این آرایه جدول، باید گفت که، همانطور که می بینیم، تنها با نرخ 9.5٪ در سال، ما می توانیم سطح قابل قبول پرداخت ماهانه (کمتر از 29،000 روبل) را دریافت کنیم.
درس: محاسبه پرداخت سالیانه در اکسل
روش 2: از یک ابزار با دو متغیر استفاده کنید
البته، اگر در واقع واقع بینانه، برای پیدا کردن بانک ها که وام را با 9.5٪ در سال می گذرانند بسیار مشکل است. بنابراین، بگذارید ببینیم که گزینه هایی برای سرمایه گذاری در سطح قابل قبول پرداخت ماهانه برای ترکیب های مختلف متغیرهای دیگر وجود دارد: اندازه وام و دوره وام. در همان زمان، نرخ بهره بدون تغییر باقی خواهد ماند (12.5٪). در حل این مشکل، ابزار Table Table به ما در استفاده از دو متغیر کمک می کند.
- رسم یک آرایه جدول جدید. در حال حاضر نام ستونها دوره اعتباری را نشان می دهد (از 2 تا 6 سال در ماه با یک گام یک سال)، و در ردیف - اندازه وام بدن (از 850000 تا 950000 روبل با یک قدم از 10،000 روبل). در این مورد، پیش نیاز است که سلول، در آن فرمول محاسبه واقع شده است (در مورد ما، PMT )، در مرز از نام ردیف ها و ستون قرار دارد. بدون این شرایط، ابزار هنگام کار با دو متغیر کار نمی کند.
- سپس کل مجموعه جدول به دست آمده از جمله نام ستون ها، ردیف ها و سلول با فرمول PMT را انتخاب می کنیم . به برگه "Data" بروید. همانطور که در زمان قبلی، ما بر روی "تجزیه و تحلیل" آنچه که اگر "" در گروه ابزار "کار با داده ها" کلیک کنید. در لیستی که باز می شود، مورد "جدول داده ..." را انتخاب کنید .
- ابزار داده جدول راه اندازی شده است . در این مورد، ما به هر دو فیلد نیاز داریم. در قسمت "مقادیر جایگزین توسط ستون ها" ما مختصات سلول حاوی وام را در داده های اولیه نشان می دهد. در فیلد "ارزش های جایگزین با ردیف ها" ، آدرس سلول پارامترهای اولیه حاوی اندازه بدن وام را نشان می دهد. بعد از اینکه تمام اطلاعات وارد شد روی دکمه OK کلیک کنید.
- برنامه محاسبات را انجام می دهد و طیف جدول با داده ها را پر می کند. در تقاطع ردیف ها و ستون ها، اکنون می توانید ببینید که دقیقا پرداخت ماهانه با مبلغ مربوط به سالیانه و یک دوره اعتبار خاص تعریف شده است.
- همانطور که می بینید مقدار زیادی از ارزش ها را می بینید. برای حل مشکلات دیگر ممکن است حتی بیشتر باشد. بنابراین، برای ایجاد خروجی نتایج بصری و بلافاصله تعیین می کند که کدام مقادیر شرایط راضی نمی کنند، می توانید از ابزارهای تجسم استفاده کنید. در مورد ما قالب بندی شرطی خواهد بود. تمام مقادیر محدوده جدول را انتخاب کنید، به غیر از سرصفحه ردیف و ستون.
- به زبانه Home بروید و روی نماد Formatting Conditional کلیک کنید. این در نوار ابزار Styles در نوار است. در منو که باز می شود، "قوانین انتخاب سلول ها" را انتخاب کنید . در لیست اضافی، روی موقعیت "کمتر ..." کلیک کنید.
- پس از این، پنجره تنظیمات قالب بندی شرطی باز می شود. در قسمت چپ ما مقدار را نشان می دهیم که کمتر از آن سلول ها انتخاب می شوند. همانطور که ما به یاد می آوریم، ما با شرایطی که پرداخت ماهانه وام کمتر از 29000 روبل است راضی هستیم. این شماره را وارد کنید در قسمت سمت راست، می توانید رنگ انتخاب را انتخاب کنید، اگر چه شما می توانید آن را به طور پیش فرض را ترک کنید. پس از وارد شدن تمام تنظیمات لازم، روی دکمه "OK" کلیک کنید.
- پس از آن، تمام سلول هایی که مقادیر آنها با شرایط فوق مطابقت دارند، در رنگ مشخص می شوند.
پس از تجزیه و تحلیل آرایه جدول، شما می توانید برخی از نتیجه گیری. همانطور که می بینید، با در نظر گرفتن مدت زمان اجاره ای (36 ماه)، به منظور سرمایه گذاری در مبلغ فوق پرداخت ماهانه، باید وام بیش از 8،600،000.00 روبل، یعنی 40،000 کمتر از آنچه که در ابتدا برنامه ریزی شده بود، را بپردازیم.
اگر ما همچنان قصد داریم وام را در مبلغ 900000 روبل بگیریم، پس مدت وام 4 سال (48 ماه) است. فقط در این مورد، مبلغ پرداخت ماهانه از حد معینی از 29000 روبل تجاوز نمی کند.
بنابراین، با بهره گیری از این آرایه جدولی و تجزیه و تحلیل جوانب مثبت و منفی هر گزینه، وام گیرنده می تواند تصمیم خاصی در مورد شرایط وام گیری انتخاب کند، گزینه ای را انتخاب کند که به بهترین وجه از همه گزینه های ممکن مناسب است.
البته، جدول جستجو را می توان نه تنها برای محاسبه گزینه های اعتباری، بلکه برای حل بسیاری از مشکلات دیگر.
به طور کلی، باید اشاره کرد که جدول جستجو یک ابزار بسیار مفید و نسبتا ساده برای تعیین نتیجه ترکیب های مختلف متغیرها است. با استفاده از قالب بندی مشروط در همان زمان، شما همچنین می توانید اطلاعات دریافت شده را تجسم کنید.