 |
|
БДСУММ(База данных; Поле;Критерий).
1-тапсырма.Бірнеше менеджерлермен жүзеге асырылған сату туралы мәліметтерден тұратын мәліметтер қорын құру және қажетті ақпаратты алу.
1-қадам: Мәліметтер қорының жұмыс парағын құру.
В1 ұяшығына Продажи за ноябрь,А3 ұяшығына Дата, С3 ұяшығына Кому, D3 ұяшығына Суммасөздерін енгізіңіз; Лист1жұмыс парағына Ноябрьатын берңіз.
Данные-Формаменю командасын таңдаңыз; Оk пернесін басыңыз. Ноябрь аты берілген экранда форма диалогы ашылады.
Диалог алаңдарын 7-суреттегідей толтырыңыз. Келесі алаңға көшу үшін Таb клавишасын немесе тышқанды қолданамыз. Әр жолды енгізіп болғаннан кейін Добавитьпернесін басамыз. Барлық жазуларды енгізіп болғаннан кейін Закрытьпернесін басамыз.
2-қадам: Афтофильтр құру.
С1 ұяшығына СУММфункцисын қолданып, айдағы жалпы сату көлемін есептейміз. Мәліметтер базасына әлі де жазулар енгізілетіндіктен максимальды мүмкін диапазонды көрсетеміз: СУММ(D4:D16384).Е3 ұяшығына Итогосөзін енгіземіз.
Функция шеберіндегі Математическиекатегориясындағы Промежуточные Итогифункциясын таңдаймыз. Номер функцииалаңына 9 санын енгіземіз. Ссылка1алаңына D4:D16384диапазонын көрсетеміз. Оk пернесін басыңыз. F3ұяшығында нәтиже шығады: 70700.
Енді автофильтр құрамыз. Баған тақырыптары орналасқан А3: D3диапазонын ерекшелейміз.
Данные•Фильтр•Автофильтрменю командаларын таңдаймыз. Ерекшеленген әр бағанның оң жағында автофильтр кнопкасы пайда болады.
Менеджер Ивановпен жүзеге асырылған жалпы сату көлемі қажет болсын. Ол үшін:
Менеджерыбағанының оң жағындағы автофильтр кнопкасын басыңыз. Мүмкін болатын фильтр тізімі шығады.
7-сурет – Экранда форма диалогы
Ивановфильтрін таңдаймыз. Нәтижесінде Ивановпен жасалған сату туралы ақпараттар шығады, ал қалған жазулар жасырылып тұрады. F3 ұяшығында Ивановтың сату көлемі 31000 шығады. Сонымен Промежуточные Итогифункциясы экранда көрініп тұрған жазулар бойынша қорытынды есептейді.
Фильтрлеуді алып тастау үшін автофильтр кнопкасын басып (Все)командасын таңдаймыз.
Сату көлемін АО Салем+ фирмасына қатысты фильтрлеп көріңіз.
Иванов пен Сериковтың сату көлемін анықтау үшін Менеджерұяшығындағы автофильтр кнопкасын түртіп, ашылған тізімнен (Условие...)таңдаймыз. Пользовательский автофильтрдиалогы ашылады. Бұл диалог көмегімен равно, больше, меньшежәне т.б.салыстыру операторлары көмегімен фильтрлеу шартын беруге болады.
Сол жақ жоғары алаңға равно, жоғары оң жақ тізімнен Ивановмәнін таңдаймыз. ИЛИауыстырғышын орнатамыз.
Төменгі сол жақ тізімде равнооператорын, төменгі оң жақтағы тізімнен Сериковмәнін таңдап, Оkпернесін басыңыз.
Мысалы, 12.11.02 күнінен кейін жасалған сату көлемін анықтау үшін Датаұяшығындағы автофильтр кнопкасын түртіп, ашылған тізімнен (Условие...)таңдаймыз. Пользовательский автофильтрдиалогы ашылады. Сол жақ жоғары алаңға больше, жоғары оң жақ тізімнен 12.11.02мәнін таңдаймыз. Оkпернесін басыңыз.
Мәліметтер қорындағы барлық бағандардағы фильтрлерді алып тастау үшін: Данные-Фильтр-Отобразить всеменю командасын қолданамыз. Автофильтрді өшіру үшін: Данные-Фильтр-Автофильтр.
3-қадам: Мәліметтер қорынан таңдау
Әр менеджермен жасалған сату көлемі туралы мәлімет, оларды өзара салыстыру үшін қажет болсын. Оны жаңа парақта жасаймыз.
Лист2парағында А1 ұяшығына Продажи менеджеров за ноябрьтақырыбын енгіземіз. А3-ке Менеджер,А4,А5,А6 ұяшықтарына менеджер фамилияларын ензізіңіз: Иванов, Асанов, Сериков.
Вбағанына әр менеджердің сату көлемін есептеу үшін формула енгіземіз: Курсорды В4ұяшығына қойып, Функция шеберінен Работа с базой данныхкатегориясыннан БДСУММфункциясын таңдаймыз.
Ашылған терезедегі База данныхалаңына Ноябрьпарағындағы $C$3:$D$16384 диапазонын енгіземіз.
Полеалаңына «Сумма» бағанының атын енгіземіз.
Критерийалаңына А3:А4 ұяшық диапазонын енгізіп, Оkпернесін басыңыз. Нәтижесінде менеджер Ивановпен жасалған сату көлемі есептелінеді, ал формула жолында келесі формула шығып тұрады:
БДСУММ(Ноябрь! $C$3:$D$16384;"Сумма";А3:А4).Сонымен БДСУММфункциясы көрсетілген мәліметтер қорындағы Суммабағанындағы Менеджер=Ивановкритериіне сәйкес келетін барлық мәндерді қарап, қосындысын есептейді.
В5 ұяшығында Асановтың сату көлемін анықтау үшін: :
БДСУММ(Ноябрь! $C$3:$D$16384;"Сумма";А3:А5)-В4;
В6 ұяшығында Сериковтың сату көлемін анықтау үшін: :
БДСУММ(Ноябрь! $C$3:$D$16384;"Сумма";А3:А6)-В5-В4;
Мәліметтер қорына жаңа ақпараттар енгізіп, қалай өзгеретіндіктерін байқаңыздар.
4-тәжірибелік жұмыс. Құрама кестелерді құрастыру
Құрамакестелердің мастермен жұмыс істеу. Қосымша операцияларды қолдану. Құрамакестелер бірнеше консолидацияларда.
Жұмыстың мақсаты мен мазмұны
Есепті орындау студенттерге құрама кесте сияқты Excel-дің мәліметтер қорымен жұмыс жасау құралын игеруге мүмкіндік береді.
Жұмыстың орындау методикасы
Құрама кестелер ақпаратты талдау үшін және мәліметтер қорында, жұмыс парақтарында, сыртқы файлдарда сақталынатын ақпараттарды жалпылау үшін қолданылады. Құрама кесте интерактивті кесте болып табылады, оның көмегімен мәліметтердің үлкен көлемдерін жылдам біріктіруге және салыстыруға болады, алғашқы мәліметтер бойынша әр түрлі нәтижелер алуға болады, сонымен бірге керекті облыстар бойынша мәліметтерді көрсетуге болады.
1-тапсырма.Эмитенттер мен құнды қағаздардың түрлері бойынша сұраныс пен ұсынысты талдау үшін 2-кестеде көрсетілген мәліметтер қоры бойынша құрама кестені құру қажет.
2-кесте – Құнды қағаздарының деректер базасы
| ҚҚ түрінің коды | Эмитент коды | ҚҚ номиналы | ҚҚ эмиссиясы | ҚҚ сұранасы | ҚҚ курсы | Ұсыныс құны | Сұраныс құны |
| А | П1 | 1,05 | |||||
| А | П1 | 0,07 | |||||
| А | П2 | 0,98 | |||||
| А | П3 | 0,97 | |||||
| В | П1 | 1,12 | |||||
| В | П2 | 1,06 | |||||
| В | П3 | 1,09 | |||||
| В | П3 | 1,12 | |||||
| О | П1 | 1,01 | |||||
| О | П2 | 1,02 | |||||
| О | П3 | 1,02 | |||||
| О | П2 | ||||||
| А | П2 | 0,98 | |||||
| В | П3 | 0,97 | |||||
| В | П1 | 1,1 | |||||
| В | П2 | 1,06 | |||||
| О | П3 | 1,07 | |||||
| О | П1 | 1,04 | |||||
| О | П3 | 1,02 | |||||
| А | П3 | 1,02 | |||||
| В | П3 | 0,98 | |||||
| О | П1 | 1,15 | |||||
| О | П2 | 1,04 |
Ұсыныс бағасы эмиссияны номиналды бағаға көбейткенге тең болады.Сұраныс бағасы құнды қағаз сұранысы, номинал және берілген құнды қағаз курсының көбейтіндісіне тең.
Құрама кестені құру үшін бастапқы кестенің облысына курсорды қою керек, содан соң Данныеменюіне кіру керек те,Сводная таблицакомандасын орындау керек. Әрі қарай сіз жиынтық кестені құру үшін деректер көзінің түрі ретінде Excel деректер базасын(тізімін) көрсетуіңіз керек. Келесі қадамда макет облыстарында бағалы қағаздардың деректер базасының өрісін келесі түрде орналастырыңыз:
- жолдарға – бағалы қағаздар түрінің коды;
- бағандарға – эмитент коды.
Данныеоблысында Стоимость предложенияаттықорытындыөріс болу керек. Сіз оны ҚҚ бойынша сұраныс деп атын өзгертіп, Суммаоперациясын қолдану керексіз.
Құрылған кестені жаңа бетке орналастырыңыз және оны «Құрама кесте» деп атаңыз.
Курсорды құрама кестенің облысына орналастырыңыз да тышқанның оң жақ батырмасын басып, Формулы,Вычисляемое поле командасын орындаңыз. Жаңа есептеуіш өрістің – Дефицит/Избыток атын көрсетіңіз. Есептеу формуласы: Ұсыныс құны – Сұраныс құны.
Құрама кестенің Данные облысына жаңа өрістер қосыңыз:
- Ұсыныс құны – Эмитент бойынша ұсыныс құрылымыатымен, операция – сома, қосымша есептеулер – Жол бойынша сома бөлігі;
- Сұраныс құны – Эмитенттер БҚ–дарына сұраныс құрылымы атымен, операция – сома, қосымша есептеулер – Жол бойынша сома бөлігі;
- Ұсыныс құны – Эмитенттің ұсыныс құныатымен, операция – сома, қосымша есептеулер – Баған бойынша сома бөлігі.
Параметрыконтексті меню командасы арқылы құрама кестенің келесі параметрлерін өзгертіңіз:
- баған бойынша жалпы сомасы;
- жол бойынша жалпы сомасы;
- афтоформат;
- форматтауды сақтау;
- кестелері бар деректерді сақтау;
- ашу кезінде жаңарту.
2-тапсырма.Қалалар бойынша банктер жұмысын талдаңыз және құрама кесте мастерінің көмегі арқылы бастапқы 3-кесте негізінде 4-кестені құрыңыз.
Банк қызметінің анализінде қарыз капитал меншікті капиталдың 10 %-іне тең. Ағымдағы берешек ағымдағы активтер мен қарыз капитал арасындағы алымға тең. Ағымдағы активтердің пайдалылығы таза пайданы ағымдағы активтердің орташа сомасына бөлгенге тең.
3-кесте – Банктер бойынша деректер базасы
| Қала | Банк коды | Таза пайда | Меншікті капитал | Меншікті капиталдың орташа сомасы | Ағымдағы активтер | Ағымдағы активтердің орташа сомасы | Негізгі қорлардың орташа құны |
| Алматы | |||||||
| Астана | |||||||
| Астана | |||||||
| Алматы | |||||||
| Алматы | |||||||
| Москва | |||||||
| Астана | |||||||
| Астана | |||||||
| Алматы | |||||||
| Алматы | |||||||
| Алматы | |||||||
| Астана | |||||||
| Алматы | |||||||
| Астана | |||||||
| Москва | |||||||
| США | |||||||
| Астана | |||||||
| Алматы | |||||||
| Москва | |||||||
| Москва | |||||||
| Алматы | |||||||
| Алматы |
4-кесте – Қалалар бойынша банктердің жұмысын талдау
| Қала | Москва | |
| Банк коды | Мәліметтер | Қорытынды |
| Таза пайда | ||
| Меншікті капитал | ||
| Қарыз капитал | 95560,6 | |
| Ағымдағы берешек | 2070295,4 | |
| Ағымдағы активтердің пайдалылығы | 0,124090995 | |
| Негізгі қорлардың пайдалылығы | 0,173816315 | |
| Меншікті капиталдың пайдалылығы | 0,028124562 | |
| Таза пайда | ||
| Меншікті капитал | ||
| Қарыз капитал | 84656,3 | |
| Ағымдағы берешек | 464999,7 | |
| Ағымдағы активтердің пайдалылығы | 0,207308983 | |
| Негізгі қорлардың пайдалылығы | 0,871928182 | |
| Меншікті капиталдың пайдалылығы | 0,159474251 | |
| Таза пайда | ||
| Меншікті капитал | ||
| Қарыз капитал | ||
| Ағымдағы берешек | ||
| Ағымдағы активтердің пайдалылығы | 0,093147973 | |
| Негізгі қорлардың пайдалылығы | 0,080362573 | |
| Меншікті капиталдың пайдалылығы | 0,067042773 | |
| Таза пайда | ||
| Меншікті капитал | ||
| Қарыз капитал | 58935,5 | |
| Ағымдағы берешек | 2106999,5 | |
| Ағымдағы активтердің пайдалылығы | 0,185193761 | |
| Негізгі қорлардың пайдалылығы | 0,509641581 | |
| Меншікті капиталдың пайдалылығы | 0,18727083 | |
| Қорытынды Таза пайда | ||
| Қорытынды Меншікті капитал | ||
| Қорытынды Қарыз капитал | 306952,4 | |
| Қорытынды Ағымдағы берешек | 5840091,6 | |
| Қорытынды Ағымдағы активтердің пайдалылығы | 0,162778639 | |
| Қорытынды Негізгі қорлардың пайдалылығы | 0,291033524 | |
| Қорытынды Меншікті капиталдың пайдалылығы | 0,103503019 |
Негізгі қорлардың пайдалылығы таза пайданы негізгі қорлардың орташа құнына бөлу арқылы табамыз. Меншікті капиталдың пайдалылығы таза пайданы меншікті капиталға бөлгенге тең.
3-тапсырма.Алғашқы ведомосінің 5-кестесі негізінде құрама кестенің мастері көмегімен 6-кестені құру.
«Төлеуге арналған шығындардың үлес салмағы» және «Құнындағы үлес салмағы» атты өрістер есептелетін болып табылады. Қосымша есептеулер ретінде баған бойынша соманың бөлігін қойыңыз.
5-кесте – Өткізілген тауарлардың тізімдемесі
| № п/п | Тауар топтарының коды | Тауар аты | Сатылған тауарлар саны | Бірлік бағасы |
| Ет | 7980,00 | |||
| Колбаса өнімдері | 8152,00 | |||
| Сүт | 5133,00 | |||
| Сыр | 6551,00 | |||
| Ұн | 3461,00 | |||
| Тары | 1472,00 | |||
| Рис | 4216,00 | |||
| Манка | 3825,00 | |||
| Гречка | 4917,00 | |||
| Сәбіз | 1266,00 | |||
| Қызылша | 1977,00 | |||
| Картоп | 2511,00 |
6-кесте – Өткізілген тауарлар анализінің тізімдемесі
| Тауар аты | Деректер | Қорытынды |
| Колбаса өнімдері | Төлеуге арналған шығындар(сома), тенге | |
| Төлеуге арналған шығындардың үлес салмағы | 29,31% | |
| Өткізілген сомасы | ||
| Құнындағы үлес салмағы | 4,85% | |
| Сүт | Төлеуге арналған шығындар(сома), тенге | |
| Төлеуге арналған шығындардың үлес салмағы | 18,45% | |
| Өткізілген сомасы | ||
| Құнындағы үлес салмағы | 2,45% | |
| Ет | Төлеуге арналған шығындар(сома), тенге | |
| Төлеуге арналған шығындардың үлес салмағы | 28,69% | |
| Өткізілген сомасы | ||
| Құнындағы үлес салмағы | 5,36% | |
| Сыр | Төлеуге арналған шығындар(сома), тенге | |
| Төлеуге арналған шығындардың үлес салмағы | 23,55% | |
| Өткізілген сомасы | ||
| Құнындағы үлес салмағы | 2,74% | |
| Қорытынды Төлеуге арналған шығындар(сома), тенге | ||
| Қорытынды Төлеуге арналған шығындардың үлес салмағы | 100,00% | |
| Қорытынды Өткізілген сомасы | ||
| Қорытынды Құнындағы үлес салмағы | 100,00% |
Бақылау сұрақтары
1) Құрама кестелердің жұмыс қажеттілігін және механизмін түсіндіріңіз.
2) Эмитенттер бойынша ұсыныс құрылымы, Эмитенттердің ҚҚ-на сұраныс құрылым және Эмитент ұсыныстарының құрылымы өрістері арасындағы айырмашылықтарды түсіндіріңіз.
3) Пайдалылық пен тиімділіктің түсініктері
5-тәжірибелік жұмыс. Несие төлемдерін есептеуде қаржылық функцияларды пайдалану
Еxcel кестелік процессорында ПЛТ, ПРПЛТ, ОСПЛТ қаржылық функцияларын қолдану арқылы, кезеңдегі несие төлемдерін, негізгі төлемдер мен проценттік төлемдерді есептеуге болады.
Кезеңдегі жалпы төлемдерді есептеу үшін ПЛТ функциясы қолданылады. Оның аргументтері:
Ставка – кезеңдегі проценттік ставка.
КПЕР – төлемдерді төлеу мерзімінің жалпы саны.
ПС – ағымдық мерзімдегі несиенің жалпы сомасы.
БС – соңғы төлемнен кейінгі болашақта болуы мүмкін құн немесе қолма-қол баланс. Егер БС аргументі жоқ болса, оны 0 деп есептейді.
ТИП – 0 немесе 1 сандары төлемінің сәйкес мерзімінің басында немесе соңында төленетінін көрсетеді. Егер 0 болса, онда төлем кезеңнің соңында, ал 1 болса, кезеңнің басында төленеді.
Әр кезеңдегі негізгі төлемдер мен процент бойынша төлемдерді есептеу үшін ОСПЛТжәне ПРПЛТфункцияларын қолданамыз. Бұл функциялардың да, тек төлем кезеңін көрсететін «Период»аргументінен басқа барлық аргуметтері ПЛТ функциясының аргументтерімен бірдей.
Бұл функцияларды қолдану барысында мыналарды есте ұстау қажет:
- Егер төлем ай сайын төленетін болса, онда жылдық процент ставкасын 12-ге бөлу керек;
- ПЛТ функциясымен есептелетін төлемдер негізгі төлемдер мен процент бойынша төлемдерді қамтиды, бірақ кейде қарызбен байланысты болатын салықтарды, резервтік төлемдерді немесе комиссияларды қоспайды.
1-есеп. 10000 доллар көлемінде 5 жылға, жылдық 17%-тік ставкамен алынған қарыз бойынша жылдық төлем шамасын есептеу.
8-суреттегі В3:В5 ұяшықтары аралығына алғашқы мәліметтерді енгіземіз. В6 ұяшығына жылдық төлем шамасын есептеу үшін ПЛТ функциясын қолданамыз, аргументтерін келесі түрде енгіземіз: =ПЛТ($B$3;$B$4;-$B$5)
Жалпы төлем сомасы: =B6*B4
Әр кезеңде төленетін процент бойынша төлемдер мен негізгі төлемдерді анықтау үшін келесі формулаларды енгіземіз:
8-сурет – Есептің шешуі
D10 ұяшығына =ПРПЛТ($B$3;C10;$B$4;-$B$5) енгізіп, курсорды төменгі оң жақ бұрышқа орналастырып, соңғы кезеңге дейін тышқанның сол жақ батырмасын басулы ұстап тартамыз.
Е10 ұяшығына =ОСПЛТ($B$3;C10;$B$4;-$B$5) енгізіп, курсорды төменгі оң жақ бұрышқа орналастырып, соңғы кезеңге дейін тышқанның сол жақ батырмасын басулы ұстап тартамыз. Нәтижесінде әр кезеңдегі процент бойынша төлемдер мен негізгі төлемдердің шамасы анықталады. Қарыз қалдығын өз бетіңізбен орындаңыздар.
2-есеп.3 жылға жылдық 15%-пен алынған құны 700000 теңге болатын тұтыну несиесі бойынша айлық төлем шамаларын (негізгі төлем шамасын, проценттік төлем шамасын және жалпы айлық төлем шамасын) және қарыз қалдығын есептеңіздер.
3-есеп.20 жылға 11 % жылдық ставкамен 52000 тенге ақша салынғанда шотта қанша ақша болатынын есептеу керек. Процент ай сайын есептеледі.
Шешімі: Болашақ құнды есептеу үшін күрделі процентті есептейтін БС функциясы қолданылады:
БС(ставка; кпер; плт; пс; тип)
Біздің есебімізде функция төмендегідей жазылады:
БС(11 %/12;20*12;;-52000)
Есептеу терезесі 3.9-суреттегідей болады:
9-сурет – Салымның болашақ құнын есептеу
Болашақ құнның шамасын есептеу үшін В8 ұяшығына мына формуланы жазамыз:
= БС(B4/B5;B6*B5;;-52000)
4-есеп.Проектіні іске асыру барысында жыл сайын түсетін пайда 54 000 000 болады деген болжам бар. Проектінің өзін-өзі ақтау мерзімін анықтау керек. Алғашқы инвестиция мөлшері 140 000 000, дисконттау нормасы 7,67 %
Шешімі:Төлемдер мерзімін анықтау үшін КПЕР(ставка; плт; пс; бс; тип) функциясы қолданылады. Біздің есебіміз бойынша КПЕР(7,67 %; 54000000; -140000000) = 3
5-есеп.Облигация 200 000 номиналмен 7 жылға шығарылған. Процентті есептеу мынандай тəртіппен жүргізіледі: бірінші жылы – 11 %, келесі үш жылда – 16 проценттен, қалған үш жылда – 20 проценттен. Күрделі проценттік ставкамен облигацияның болашақ құнын есептеу керек.
Шешімі:Күрделі проценттік ставкамен болашақ құнды төмендегі функцияның көмегімен есептейміз:
БЗРАСПИС(первичное; план)
Біздің есебіміз үшін ол 10-суреттегідей түрде болады:
10-сурет – Күрделі проценттік ставкамен өсетін құнды есептеу
Мұндағы В15 ұяшыққа = БЗРАСПИС(B3;B7:B13) формуласын енгіземіз
6-есеп.Проект бойынша шығындар 600 млн. Келесі 5 жыл ішінде түсетін пайда: 50, 100, 300, 200, 300 млн. Пайда нормасы 15 % болғанда инвестиция айналымының жылдамдығына байланысты проектінің тиімділігін бағалау керек.
Шешімі: Инвестиция айналымының ішкі жылдамдығын есептеу үшін ВСД(значения; предположения) функциясы қолданылады. Біздің есебімізде Значения аргументі ғана қолданылады. Олардың біреуі міндетті түрде теріс сан болады. Егер инвестиция айналымының ішкі жылдамдығы пайда нормасынан жоғары болса, онда проект тиімді деп саналады. Ондай болмаған жағдайда проект іске асырылмау керек.
Есептеу терезесі 11-суретте көрсетілген :
Мұнда В13 ұяшығына мына формуланы енгіземіз:
= ВСД(B4:B9)
В15 ұяшығына енгізілетін формула:
= ЕСЛИ(B13>B11;"Проект тиімді";"Проект тиімсіз")
11-сурет – Инвестиция айналымының ішкі жылдамдығын есептеу
Тақырып бойынша тапсырмалар
1-есеп. 7 % жылдық ставкамен берілген 10 000 000т. 7 жылдық ипотекалық ссуданың ай сайынғы жəне жыл сайынғы төлемдерін есептеу керек. Бастапқы салым 10 %. ПЛТ функциясын қолданыңыз.
2-есеп. 6 % жылдық ставкамен берілген 15 000 000т. 3 жылдық ипотекалық ссуданың ай сайынғы жəне жыл сайынғы төлемдерін есептеу керек. Бастапқы салым 14 %. ПЛТ функциясын қолданыңыз.
3-есеп. Шотқа 1000000т. 8 % жылдық ставкамен салғанда 10 жылда қанша болатынын есептеу керек. Процент квартал сайын есептеледі. БС функциясын қолданыңыз.
4-есеп. 4 жыл бұрын 200000 ссуда 20 % жылдық ставкамен берілген. Процент ай сайын есептеледі. Қанша сома төленетінін анықтаңыз. БС функциясын қолданыңыз.
5-есеп. 14,5 % проценттік ставкамен салынған 1000000 сомаға əр жылдың аяғындағы төлем 10,897 болу үшін неше жыл өту керек? КПЕР функциясын қолданыңыз.
6-есеп. Процентті есептеу ай сайын жүргізілетін болса жəне проценттік жылдық ставка 35,18 % болса неше жылдан кейін 500000 мөлшердегі салым 1000000-ға жетеді. КПЕР функциясын қолданыңыз.
7-есеп. 7 жылға шығарылған номиналы 200000 болатын облигацияның болашақ құны қанша болады? Процент мынандай тəртіппен есептеледі: алғашқы үш жылға – 18 %, қалған төрт жылға – 21 %. БЗРАСПИС функциясын қолданыңыз.
8-есеп. 12 жылдан кейін кəсіпорынға 5 млн ақша қажет болады. Кəсіпорын осы соманы алу үшін қазіргі уақытта депозитке ақша салмақ ойы бар. Ол үшін 12 % проценттік ставкамен қанша мөлшерде депозитке ақша салу керек? ПС функциясын қолданыңыз.
Бақылау сұрақтары:
1) Кезеңдегі несие төлемдерін қандай функция анықтайды?
2) Негізгі төлемдер мен проценттік төлемдерді есептеуге арналған функцияларды ата.
3) ПЛТ функциясының аргументтері қандай?
4. ПРПЛТ функциясының аргументтері қандай?
5) ОСПЛТ функциясының аргументтері қандай?
6-тәжірибелік жұмыс. Параметрді келтіру құралын пайдалануы
MS Excel-дің Подбор параметра құралы тəуелді ұяшықта қажетті нəтижені алу үшін осы ұяшыққа кіретін тəуелсіз ұяшықта қандай мəн болу керектігін анықтауға жол береді.
1-есеп. 5 жылдың ішінде проект бойынша алынатын пайда: 120 000 000, 200 000000, 300 000 000, 250 000 000, 320 000 000 болады деген болжау бар.А.йналым жылдамдығы 12 % болғанда проектіге кететін алғашқы шығын қандай болу керек?
Шешім:Инвестиция айналымының ішкі жылдамдығы ВСД (значения;предположения) функциясының көмегімен табылады. Берілгендерді енгізу төмендегідей 12-суретте көрсетілген терезеде жүргізіледі:
12-сурет – Проект бойынша бастапқы шығындарды анықтау терезесі
Алғашында проектіні іске асыру үшін кететін шығын мөлшеріне ойдан кез-келген мəн бере саламыз (тіпті бұл ұяшықты бос қалдырса да болады).
В13ұяшығына =ВСД(B4:B9) формуласын енгіземіз.
Одан кейін Сервис – Подбор параметра командасын орындап, шыққан 13 суретте көрсетілген терезеге параметрлерді енгіземіз:
13-сурет – Подбор параметра құралының терезесі
Нəтижесінде біздің шарттарды қанағаттандыратын проектіге қажетті
шығындар мөлшері 14-суретте көрсетілгендей есептеліп шығады:
14-сурет – Бастапқы шығындарды есептеудің нəтижелік терезесі
2-есеп. Сізден танысыңыз 15000 қарыз сұрап, оны төмендегідей тəртіппен қайтарып бермекші: бір жылдан кейін – 3000, екі жылдан кейін – 5000, үш жылдан кейін – 9000. Танысыңыз қарызды қандай проценттік ставкамен алмақшы?
Шешімі: Бұл есепті шешу үшін ЧПС(ставка; значение1; значение2; ...) функциясын жəне Подбор параметра құралын пайдаланамыз.
Берілгендерді енгізу терезесі 15- суретте көрсетілген.
15-сурет – Бірыңғай емес капитал салымын табуға арналған жұмыс беті
Алғашында есептеу үшін жылдық проценттік ставкаға кез-келген мəн жазамыз (немесе бұл ұяшықты бос қалдыруға да болады).
В9 ұяшығына =ЧПС(B8;B4:B6) формуласын енгіземіз.
Одан кейін Сервис – Подбор параметра командасын орындап, шыққан 16-суретте көрсетілген терезеге параметрлерді енгіземіз:
16-сурет – Подбор параметра құралының терезесі
Нəтижесінде 17-суреттегідей біздің беретін қарызымыз қандай проценттік ставкамен беріліп тұрғандығын анықтаймыз.
17-сурет – Бірыңғай емес капитал салымы табылған нəтижелік бет
Тақырып бойынша тапсырмалар
1-есеп.Сізден танысыңыз 250000т. ақша сұрап, оны төмендегідей тəртіппен қайтармақшы: бір жылдан кейін – 80000, екі жылдан кейін – 90000, үш жылдан кейін – 100000. Қарызды қандай жылдық проценттік ставкамен алмақшы? ЧПС функциясын қолданыңыз.
2-есеп. Сізден танысыңыз 320000т. ақша сұрап, оны төмендегідей тəртіппен қайтармақшы: бір жылдан кейін – 80000, келесі үш жылда – 100000-нан, 5-ші жылы – 110000. Қарызды қандай жылдық проценттік ставкамен алмақшы? ЧПС функциясын қолданыңыз.
3-есеп. 6 жылға шығарылған облигацияда процентті есептеу тəртібі мынандай: бірінші жылы – 10 %, келесі екі жылда – 15%, қалған үш жылда –17 %. Оның болашақ құны 1546,88 болатынын білсек, облигацияның номиналы қаншаға тең? БЗРАСПИС функциясын қолданыңыз.
4-есеп. Мөлшері 1500 инвестицияның болашақ құны 4 жылдан кейін 3000 болады. Оның табысы мынандай: бірінші жылы – 15 %, екінші жылы – 17 %, төртінші жылы – 23 %. Үшінші жылғы инвестиция табысын табу керек. БЗРАСПИС функциясын қолданыңыз.
5-есеп. Проект бойынша табыс 4 жыл ішінде мынандай: 50000, 100000, 300000, 200000. айналым жылдамдығы 10 % болу үшін бастапқы шығын қанша болу керек? ВСД функциясын қолданыңыз.
6-есеп. Табыс нормасы 9 % болу үшін төмендегі табыстардың бастапқы шығыны қанша болу керек: 2, 5, 6, 8, 10 млн. ВСД функциясын қолданыңыз.
7-тәжірибелік жұмыс. Қою кестесі (таблица подстановки).
Бір айнымалысы бар қою кестесін қолдану
Займ бойынша ай сайын төленетін төлемдерді есептеу мысалын қарастырайық. Есептеуде ПЛТ функциясы қолданылады.
Шешуі:
а) Қолданушыға түсінікті болатындай берілгендері жазылған есептеу бетін 18-суреттегідей құрамыз:
Мұнда В5 ұяшығындағы мəндер төмендегі формуламен есептелінеді:
=ПЛТ($B$4/12;$B$3*12;$B$2)
Бұл формулада абсолютті адрестеуді қолданған дұрыс.
ə) Түрлі проценттік ставкада ай сайынғы төлемдер қалай өзгеретінін анықтау үшін сол мəндер орналасатын диапазондарды 19-суреттегідей дайындаймыз;
18-сурет – Берілгендер кестесі
б) Берілген мəндер мен шығатын нəтижелер орналасатын диапазондарды ерекшелеп, Данные – Таблица подстановки командасын орындаймыз, нәтіжесі 20-суретте көрсетілген;
в) Шыққан Таблица подстановки 21-суретте көрсетілген терезесіне дайындаған мəндердің формулада қай ұяшық орнына қойылатыны көрсетеміз. Біздің мысалымызда ол В4;
г) Нəтижесінде ай сайынғы төлемдер түрлі проценттік ставкаға сəйкес 22-суреттегідей есептелініп шығады.
19-сурет – Мəндер кестесін дайындау
20-сурет – Қою кестесін ерекшелеу
21-сурет – Таблица подстановки терезесі
22-сурет – Бір өлшемді нəтижелік қою кестесі
Екі айнымалысы бар қою кестесін қолдану
Борышты өтеудің түрлі мерзімдері жəне түрлі проценттік ставкаға байланысты займ бойынша ай сайынғы төлемдерді есептеуді қарастырайық.
Шешімі:
а) Қолданушыға түсінікті болатындай берілгендер жазылған жұмыс бетін 23-суреттегідей дайындаймыз:
23-сурет – Берілгендер кестесі
Мұнда В5 ұяшығына =ПЛТ($B$4/12;$B$3*12;$B$2) формуласын жазамыз.
ə) Түрлі проценттік ставка жəне мерзімдер үшін ай сайынғы төлемдерді есептеу үшін мəндер кестесін 24-суреттегідей дайындаймыз:
24-сурет – Мəндер кестесі
б) Екі айнымалының мəндерін қолданып есептеулер жүргізілетін диапазонды ерекшелеп, Данные – Таблица подстановки командасын орындаймыз. Шыққан 25-суретте көрсетілген терезеге параметрлерді енгіземіз:
25-сурет – Таблица подстановки терезесі
в) Нəтижесінде ай сайынғы төлемдер түрлі проценттік ставкаға жəне мерзімге сəйкес 26-суреттегідей есептелініп шығады:
26-сурет – Екі өлшемді нəтижелік қою кестесі
Тақырып бойынша тапсырмалар
1-есеп. Əр түрлі мерзімдер мен проценттік ставкалар үшін мөлшері 400 млн займ төлемдеріне қою кестесін құрыңыз. ПЛТ функциясын қолданыңыз;
2-есеп. Жылдың аяғында проект бойынша капитал салымдары 150 млн., ал 4 жылдың ішінде түсетін пайда 45, 48, 56, 60 млн деп болжанады. Проектінің таза ағымдық құнын түрлі дисконттау нормалары мен капитал салымдарының мəндеріне байланысты қою кестелерінде есептеу керек. ЧПС функциясын қолданыңыз.
3-есеп. Облигациялар 89 курспен, 9.09.2001 сатып алынған жəне купондық пайда (ставка) 10%, төлем периоды жырты жылда бір рет. Облигация борышы өтелетін күн 15.09.2005, курсы – 100 деп болжанады. Қою кестесінде облигация бағасы мен купонның жылдық ставкаға тигізетін əсерін көрсету керек. Уақытша есептеу базисы – 1. ДОХОД функциясын қолданыңыз.
4-есеп. Номиналы 1000 облигация, купондық ставкасы 8 %, төлем периоды – жылына 4 рет, шығарылған күні - 1.09.2003, сатып алынған күні – 5.10.2003, купонның бірінші рет төленген күні – 12.12.2003, есептеу базисы – 1. Қою кестесінің көмегімен жиілік пен купондық ставканың жинақталған табыстың мөлшеріне əсерін көрсетіңіз. НАКОПДОХОД функциясын қолданыңыз.
5-есеп. 200000 бағаға купонсыз облигация (инвестиция) 06.09.2002 сатып алынған. Борыш өтелетін күн – 12.09.2005, бағасы – 250000. Уақытша есептеу базисы – 1. Қою кестесінің көмегімен қосымша пайданың жылдық ставкасының өзгеруі инвестиция мөлшері мен борышты өтеу бағасына қалай байланысты екенін көрсетіңіз. ИНОРМА функциясын қолданыңыз.
6-есеп. Вексельдің берілген күні – 6.09.2002, сомасы (инвестиция) – 250000, 8 % ставкамен төленген күні – 12.09.2004 , Уақытша есептеу базисы – 1. Қою кестесінің көмегімен вексель бойынша алынатын соманың шамасының өзгеруі инвестиция мөлшері мен жеңілдікке байланысты қалай жүретінін көрсетіңіз. ПОЛУЧЕНО функциясын қолданыңыз.
7-есеп. Облигация алынған күні – 11.08.2001, борышты өтеу күні – 25.11.2003, купондық пайда – 10 %, проценттер төлемі жарты жылда бір рет, пайданың жылдық ставкасы – 12 %. Уақытша есептеу базисы – 1. Қою кестесінің көмегімен құнды қағаздың ұзақтығының өзгерісі пайданың жəне купонның мөлшеріне байланысты қалай жүретінін көрсетіңіз. ДЛИТ функциясын қолданыңыз.
8-есеп. 350000 бағаға купонсыз облигация (инвестиция) 12.09.2004 сатып алынған. Борыш өтелетін күн – 12.09.2008, бағасы – 450000. Уақытша есептеу базисы – 1. Қою кестесінің көмегімен қосымша пайданың жылдық ставкасының өзгеруі инвестиция мөлшері мен борышты өтеу бағасына қалай байланысты екенін көрсетіңіз. ИНОРМА функциясын қолданыңыз.
9-есеп. Вексельдің берілген күні – 14.02.2005, сомасы (инвестиция) – 150000, 8 % ставкамен төленген күні – 12.03.2007 , Уақытша есептеу базисы – 1. Қою кестесінің көмегімен вексель бойынша алынатын соманың шамасының өзгеруі инвестиция мөлшері мен жеңілдікке байланысты қалай жүретінін көрсетіңіз. ПОЛУЧЕНО функциясын қолданыңыз.
10-есеп. Облигация алынған күні – 18.06.2000, борышты өтеу күні – 25.11.2007, купондық пайда – 10 %, проценттер төлемі жарты жылда бір рет, пайданың жылдық ставкасы – 12 %. Уақытша есептеу базисы – 1. Қою кестесінің көмегімен құнды қағаздың ұзақтығының өзгерісі пайданың жəне купонның мөлшеріне байланысты қалай жүретінін көрсетіңіз. ДЛИТ функциясын қолданыңыз.
7-тәжірибелік жұмыс. Шешімді іздеу (поиск решения) баптамасының көмегімен тиімділеу есептерін шығару
Күнделікті өмірде оптимизациялау есептерін шешу қажеттілігімен жие кездесеміз. Қарапайым мысал, сауда дүкеніне кіргенде әрдайым мына бір сұрақ туындайды: амиянымыздың мүмкіндігіне қарай қажеттілігімізді барынша қалай қанағаттандыруға болады? Ал, менеджерлер, экономистер үнемі жұмыстары барысында қызметкерлер штатын жоспарлау, еңбек ақы қорын жоспарлау, өндірістің тиімді жоспарын құру, тауарды нарыққа жылжыту бойынша жарнама кампанияларын жоспарлау секілді мәселелермен кездеседі.
Өмірде және экономикада жие кездесетін осындай есептердің көптүрлілігіне қарамастан, Excel оларды шешудің тиімді әдісі – Поиск решения құралын ұсынады. ДК пайдаланушысынан талап етілетіні Excel үшін есептің дұрыс қойылуын қалыптастыру, ал есептің тиімді шешімін дәл және тез арада Поиск решения құралы тауып береді.
Поиск решения(Шешімін табу)– оптимизациялау есептерін шешуге арналған Excel қондырмасы. Егер Сервис менюінде Поиск решения командасы болмаса, онда оны жүктеу қажет. Ол үшін Сервис→Надстройки командасын ашып, Поиск решения қондырмасын белсенді ету керек. Егер Поиск решения Надстройки сұхбат терезенде болмаса, Windows-ң басқару панеліндегі Установка и удаление программ пиктограммасына шертіп, Excel (немесе Office) орнату программасының көмегімен Поиск решения қондырмасын орнату керек.
Поиск решения 27-суреттегі сұхбат терезесінің элементтерін қарастырайық.
Установить целевую ячейку(Мақсатты ұяшықты орнату)өрісіне максимумы, минимумы немесе берілген мән табылатын функция ұяшығына сілтеме жасалады. Шешім мен мақсатты ұяшық арасындағы өзара байланыстың типі Равной(Тең болатын)тобындағы ауыстырғыштарға белгі қою арқылы орнатылады. Мысалы, мақсатты функцияның максимумын немесе минимумын табу үшін ауыстырғыш Максимальному значениюнемесе Минимальному значениюжағдайына сәйкесінше қойылады. Мақсатты функцияның берілген мәнін табу үшін Равнойтобындағы Значениюжағдайы таңдалады.
Изменяя ячейки(өзгеретін ұяшықтар)өрісіне есептің шешімін табу барысында өзгеруі қажет ұяшықтар адресі, яғни айнымалыларға бөлінген ұяшықтар көрсетіледі.
Есептің айнымалыларына қойылатын шарттар
- Ограничения(Шектеулер)өрісінде бейнеленеді. Шешімін табу құралы теңдік, теңсіздік, сонымен қатар айнымалылардың бүтін сан болу шарты түріндегі шектеулерді енгізуге мүмкіндік береді. Шектеулер біртіндеп енгізіледі. Шектеулерді енгізу үшін Поиск решения сұхбат терезесіндегі
- Добавитьбатырмасына басу қажет және ашылған Добавление ограничения (Шектеулерді енгізу)28-суретте көрсетілген сұхбат терезесінің өрістерін толтыру керек.
27-сурет – Поиск решения терезесі
Ссылка на ячейкус(Ұяшыққа сілтеме)өрісіне шектеудің (шарттың) сол жағы, Ограничение(Шектеулер)өрісіне оң жағы енгізіледі. Ашылатын мына 
тізім арқылы шектеудің сол жағы мен оң жағының арақатысы таңдалады. Келесі шектеулерді енгізу үшін Добавление ограничениясұхбат терезесіндегі Добавитьбатырмасына басу қажет. Барлық шектеулер енгізілгеннен кейін ОКбатыршамасы шертіледі.
28-сурет – Шектеулерді енгізу терезесі
Поиск решения сұхбат терезесінің Параметрыбатырмасы шешімді табу үшін қандай параметрлер берілгендігін текеру мақсатына қолданылады.
Параметры поиска решениясұхбат терезесінде зерттелетін мәселенің шешімін табудың варианттары мен шарттарын өзгертуге, сондай-ақ тиімді модельді жүктеуге және сақтауға болады. Үнсіз келісім бойынша қолданылатын басқару элементтерінің жағдайлары мен мәндері көптеген есептердің шешімі үшін жарайды.
Параметры поиска решениясұхбат терезесінің элементтерін арастырайық:
- Максимальное времяөрісі есептің шешімін табуға кететін уақытты шектеу үшін арналған.
- Предельное число итерацийөрісі аралық есептеулер санын шектеу үшін қажет.
- Относительная погрешностьжәне Допустимое отклонениеөрістері шешімнің қандай дәлдікпен табылатындығын анықтау үшін арналған. Үнсіз келісім бойынша берілген параметрлер арқылы табылған шешімді үлкен дәлдікпен және аз ауытқумен қайта тауып, сосын бастапқы шешіммен салыстырған орынды.
Мұндай тексеруді айнымалыларына бүтін сандық шарты қойылған есептерге жүргізу ұсынылады.
- Линейная модельжалаушасы сызықтық оптимизациялау есептерінің шешімін табу немесе сызықтық емес септердің сызықтық аппроксимациясы үшін қызмет етеді. Сызықтық емес есептер жағдайында бұл жалауша алынып тасталуы, ал сызықтық есеп жағдайында салынуы қажет, себебі кері жағдайда бұрыс шешім алынуы мүмкін.
- Показывать результаты итерацийжалаушасы шешім табуды тоқтата тұруға және жекелеген итерациялардың нәтижелерін қарау үшін арналған.
- Автоматическое масштабированиежалаушасы өлшемі бойынша сапалық тұрғыда ерекшеленетін кіріс және шығыс мәндерді автоматты түрде нормализациялау үшін арналған, мысалы миллион теңгемен есептелетін салымдарға қатысты пайда пайызын максимизациялау жағдайында.
- Оценкатобы экстрополяция әдісін таңдау үшін қызмет етеді.
- Производныетобы сандық дифференциациялау әдісін таңдау үшін арналған.
- Методтобы оптимизациялау алгоритмін таңдау үшін арналған.
Есептің шешімінің нәтижелері туралы есеп беруді дайындау үшін Результаты поиска решениясұхбат терезесінде қажетті есеп беру типін таңдау қажет: Результаты, Устойчивость, Пределы.
1-тапсырма. Бояу өндірісін жоспарлау
Келесі өндірісті жоспарлауесебін қарастырайық. Кіші фабрика 2 түрлі: ішкі (І) және сыртқы (Е) жұмыстар үшін бояу шығарысымен айналысады.
Екі түрлі өнім көтерме саудаға түседі. Бояларды өндіру үшін екі бастапқы өнімдер А және В пайдаланылады. Тәуліктік максималды қоры 6 және 8 тонна құрайды. А және В өнімдерінің сәйкесінше бояулардың бір тоннаға шығындары 7-кестеге келтірілген.
Өткізу нарығын зерттеуі І бояуының тәуліктік сұранысы Е бояуынан ешқашан 1 тоннаға артық болмағанын көрсетті. Осыған қоса, І бояуына деген сұраныс күніне 2 тоннадан артық емес екенін көрсетті. Бояулардың көтерме сауда бағалары: Е бояуы үшін 3000 тенге және І бояуы үшін 2000 тенге. Өнімді ақшаға айналдырудың табысы максималды болуы үшін фабрика әр бояудың қандай мөлшерін өндіру керек?
7-кесте– Бояу өндірісін жоспарлау есебінің бастапқы мәндері
| Бастапқы өнім | Бір тонна бояуға бастапқы өнім шығыны, т | Максималды мүмкін қор, т | |
| Е бояуы | I бояуы | ||
| А | |||
| B |
Осы есепті шығару үшін математикалық моделін құру керек. Моделді құру процессін бастау үшін келесі үш сұраққа жауап беру керек:
- модел қандай шамаларды анықтауға құрылады (яғни айнымалы моделдер);
- мүмкін болатын айнымалы шамалардың көбінен тиімдіні таңдау мақсаты неден құрылады;
- белгісіздер қандай шектерден аспауы керек.
Біздің жағдайымызда табысты барынша көп алу үшін фабрика дұрыс өндіріс көлемін жоспарлау керек. Осыған сәйкес айнымалылар: хІ І бояуының тәуліктік өндіріс көлемі және хЕ Е бояуының тәуліктік өдіріс көлемі.
Тәуліктік табыс жиынтығы z І бояуының өндірістік хІ және Е бояуының х2 z=3000х1+2000хЕ тең болады. Мүмкін болатын хЕ және хІ шамалардың ішінен фабрика мақсаты табыс жиынтығын максималданатын, яғни мақсатты z функциясын анықтау.
Енді х1 және хЕ қойылатын шектерге көшейік. Бояларды өндіру көлемі теріс бола алмайды, яғни х1 , хЕ >= 0
Бояулардың екі түрін өндіруге қажетті бастапқы өнім шығыны мүмкін болатын бұл бастапқы өнім қорынан артық бола алмайды. Яғни,
,
.
Осыған қоса, бояуға деген сұраныс шамасының шегі:
,
.
Осылай, бұл есептің математикалық моделі келесі түрге ие:
Төменгі шек арқылы z=3000х1+2000хЕ максималдау:
,
,
,
,
.
Мұндағы модель сызықтық екенін ұмытпау керек, себебі мақсатты функция және шектер айнымалылардан сызықты байланыста тұр.
Бұл есепті Сервис, Шешім іздеу (Поиск решения) командасы арқылы шешейік. Шешім іздеу құралы Excel қондырғысының біреуі болып табылады. Егер Се