Иванов фильтрін таңдаймыз. Нәтижесінде Ивановпен жасалған сату туралы ақпараттар шығады, ал қалған жазулар жасырылып тұрады. 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-тапсырма. Эмитенттер мен құнды қағаздардың түрлері бойынша сұраныс пен ұсынысты талдау үшін 3.2-кестеде көрсетілген мәліметтер қоры бойынша құрама кестені құру қажет.
3.2-кесте – Құнды қағаздарының деректер базасы
ҚҚ түрінің коды
|
Эмитент коды
|
ҚҚ номиналы
|
ҚҚ эмиссиясы
|
ҚҚ сұранасы
|
ҚҚ курсы
|
Ұсыныс
құны
|
Сұраныс құны
|
А
|
П1
|
1000
|
10
|
10
|
1,05
|
|
|
А
|
П1
|
1500
|
2
|
2
|
0,07
|
|
|
А
|
П2
|
500
|
6
|
3
|
0,98
|
|
|
А
|
П3
|
100
|
3
|
4
|
0,97
|
|
|
В
|
П1
|
5000
|
2
|
3
|
1,12
|
|
|
В
|
П2
|
10000
|
1
|
2
|
1,06
|
|
|
В
|
П3
|
2000
|
1
|
1
|
1,09
|
|
|
В
|
П3
|
15000
|
3
|
1
|
1,12
|
|
|
О
|
П1
|
5000
|
6
|
5
|
1,01
|
|
|
О
|
П2
|
500
|
3
|
4
|
1,02
|
|
|
О
|
П3
|
1000
|
5
|
2
|
1,02
|
|
|
О
|
П2
|
2000
|
4
|
3
|
1
|
|
|
А
|
П2
|
5000
|
6
|
3
|
0,98
|
|
|
В
|
П3
|
100
|
3
|
4
|
0,97
|
|
|
В
|
П1
|
5000
|
2
|
3
|
1,1
|
|
|
В
|
П2
|
500
|
1
|
2
|
1,06
|
|
|
О
|
П3
|
500
|
5
|
1
|
1,07
|
|
|
О
|
П1
|
2000
|
6
|
5
|
1,04
|
|
|
О
|
П3
|
500
|
3
|
4
|
1,02
|
|
|
А
|
П3
|
1000
|
4
|
2
|
1,02
|
|
|
В
|
П3
|
500
|
1
|
3
|
0,98
|
|
|
О
|
П1
|
500
|
5
|
1
|
1,15
|
|
|
О
|
П2
|
1000
|
5
|
5
|
1,04
|
|
|
Ұсыныс бағасы эмиссияны номиналды бағаға көбейткенге тең болады.Сұраныс бағасы құнды қағаз сұранысы, номинал және берілген құнды қағаз курсының көбейтіндісіне тең.
Құрама кестені құру үшін бастапқы кестенің облысына курсорды қою керек, содан соң Данные менюіне кіру керек те, Сводная таблица командасын орындау керек. Әрі қарай сіз жиынтық кестені құру үшін деректер көзінің түрі ретінде Excel деректер базасын(тізімін) көрсетуіңіз керек. Келесі қадамда макет облыстарында бағалы қағаздардың деректер базасының өрісін келесі түрде орналастырыңыз:
- жолдарға – бағалы қағаздар түрінің коды;
- бағандарға – эмитент коды.
Данные облысында Стоимость предложения атты қорытынды өріс болу керек. Сіз оны ҚҚ бойынша сұраныс деп атын өзгертіп, Сумма операциясын қолдану керексіз.
Құрылған кестені жаңа бетке орналастырыңыз және оны «Құрама кесте» деп атаңыз.
Курсорды құрама кестенің облысына орналастырыңыз да тышқанның оң жақ батырмасын басып, Формулы, Вычисляемое поле командасын орындаңыз. Жаңа есептеуіш өрістің – Дефицит/Избыток атын көрсетіңіз. Есептеу формуласы: Ұсыныс құны – Сұраныс құны.
Құрама кестенің Данные облысына жаңа өрістер қосыңыз:
- Ұсыныс құны – Эмитент бойынша ұсыныс құрылымы атымен, операция – сома, қосымша есептеулер – Жол бойынша сома бөлігі;
- Сұраныс құны – Эмитенттер БҚ–дарына сұраныс құрылымы атымен, операция – сома, қосымша есептеулер – Жол бойынша сома бөлігі;
- Ұсыныс құны – Эмитенттің ұсыныс құны атымен, операция – сома, қосымша есептеулер – Баған бойынша сома бөлігі.
Параметры контексті меню командасы арқылы құрама кестенің келесі параметрлерін өзгертіңіз:
- баған бойынша жалпы сомасы;
- жол бойынша жалпы сомасы;
- афтоформат;
- форматтауды сақтау;
- кестелері бар деректерді сақтау;
- ашу кезінде жаңарту.
2-тапсырма. Қалалар бойынша банктер жұмысын талдаңыз және құрама кесте мастерінің көмегі арқылы бастапқы 3.3-кесте негізінде 3.4-кестені құрыңыз.
Банк қызметінің анализінде қарыз капитал меншікті капиталдың 10 %-іне тең. Ағымдағы берешек ағымдағы активтер мен қарыз капитал арасындағы алымға тең. Ағымдағы активтердің пайдалылығы таза пайданы ағымдағы активтердің орташа сомасына бөлгенге тең.
3.3-кесте – Банктер бойынша деректер базасы
Қала
|
Банк коды
|
Таза пайда
|
Меншікті капитал
|
Меншікті капиталдың орташа сомасы
|
Ағымдағы активтер
|
Ағымдағы активтердің орташа сомасы
|
Негізгі қорлардың орташа құны
|
Алматы
|
123
|
123000
|
688799
|
568493
|
152356
|
15499
|
15000
|
Астана
|
123
|
186500
|
700000
|
600000
|
160000
|
16000
|
15500
|
Астана
|
115
|
15600
|
521358
|
684929
|
2165652
|
356513
|
59656
|
Алматы
|
145
|
59696
|
566389
|
659878
|
2656233
|
596568
|
596523
|
Алматы
|
112
|
126000
|
798931
|
684521
|
296532
|
526532
|
265945
|
Москва
|
137
|
135005
|
846563
|
659779
|
549656
|
651226
|
154835
|
Астана
|
108
|
15862
|
568598
|
550000
|
2656235
|
546563
|
154512
|
Астана
|
107
|
123687
|
798956
|
780025
|
358989
|
326562
|
788952
|
Алматы
|
197
|
56900
|
635799
|
516782
|
2698565
|
56598
|
265953
|
Алматы
|
131
|
148900
|
598369
|
600000
|
6589235
|
187652
|
126556
|
Алматы
|
156
|
15687
|
574894
|
550000
|
2168562
|
184952
|
1548535
|
Астана
|
122
|
132567
|
635979
|
590489
|
6598779
|
489926
|
154562
|
Алматы
|
125
|
122680
|
568722
|
505050
|
4587963
|
265623
|
645879
|
Астана
|
147
|
98503
|
843689
|
845000
|
2154652
|
219856
|
656546
|
Москва
|
159
|
110369
|
589355
|
600050
|
2165935
|
595965
|
216562
|
США
|
146
|
125863
|
597997
|
560045
|
6597556
|
26562
|
956235
|
Астана
|
111
|
149024
|
587600
|
560421
|
9562326
|
748756
|
595624
|
Алматы
|
108
|
125679
|
890000
|
850000
|
7521356
|
265562
|
265626
|
Москва
|
156
|
45455
|
678000
|
666023
|
1265597
|
487987
|
565624
|
Москва
|
121
|
26876
|
955606
|
850000
|
2165856
|
216583
|
154623
|
Алматы
|
196
|
26583
|
669768
|
669120
|
3457953
|
216956
|
595622
|
Алматы
|
110
|
139813
|
780008
|
764035
|
9432523
|
454526
|
1545213
|
3.4-кесте – Қалалар бойынша банктердің жұмысын талдау
Қала
|
Москва
|
|
|
|
|
Банк коды
|
Мәліметтер
|
Қорытынды
|
121
|
Таза пайда
|
26876
|
Меншікті капитал
|
955606
|
Қарыз капитал
|
95560,6
|
Ағымдағы берешек
|
2070295,4
|
Ағымдағы активтердің пайдалылығы
|
0,124090995
|
Негізгі қорлардың пайдалылығы
|
0,173816315
|
Меншікті капиталдың пайдалылығы
|
0,028124562
|
3.4-кестенің соңы
137
|
Таза пайда
|
135005
|
Меншікті капитал
|
846563
|
Қарыз капитал
|
84656,3
|
Ағымдағы берешек
|
464999,7
|
Ағымдағы активтердің пайдалылығы
|
0,207308983
|
Негізгі қорлардың пайдалылығы
|
0,871928182
|
Меншікті капиталдың пайдалылығы
|
0,159474251
|
156
|
Таза пайда
|
45455
|
Меншікті капитал
|
678000
|
Қарыз капитал
|
67800
|
Ағымдағы берешек
|
1197797
|
Ағымдағы активтердің пайдалылығы
|
0,093147973
|
Негізгі қорлардың пайдалылығы
|
0,080362573
|
Меншікті капиталдың пайдалылығы
|
0,067042773
|
159
|
Таза пайда
|
110369
|
Меншікті капитал
|
589355
|
Қарыз капитал
|
58935,5
|
Ағымдағы берешек
|
2106999,5
|
Ағымдағы активтердің пайдалылығы
|
0,185193761
|
Негізгі қорлардың пайдалылығы
|
0,509641581
|
Меншікті капиталдың пайдалылығы
|
0,18727083
|
Қорытынды Таза пайда
|
317705
|
Қорытынды Меншікті капитал
|
3069524
|
Қорытынды Қарыз капитал
|
306952,4
|
Қорытынды Ағымдағы берешек
|
5840091,6
|
Қорытынды Ағымдағы активтердің пайдалылығы
|
0,162778639
|
Қорытынды Негізгі қорлардың пайдалылығы
|
0,291033524
|
Қорытынды Меншікті капиталдың пайдалылығы
|
0,103503019
|
Негізгі қорлардың пайдалылығы таза пайданы негізгі қорлардың орташа құнына бөлу арқылы табамыз. Меншікті капиталдың пайдалылығы таза пайданы меншікті капиталға бөлгенге тең.
3-тапсырма. Алғашқы ведомосінің 3.5-кестесі негізінде құрама кестенің мастері көмегімен 3.6-кестені құру.
«Төлеуге арналған шығындардың үлес салмағы» және «Құнындағы үлес салмағы» атты өрістер есептелетін болып табылады. Қосымша есептеулер ретінде баған бойынша соманың бөлігін қойыңыз.
3.5-кесте – Өткізілген тауарлардың тізімдемесі
№ п/п
|
Тауар топтарының коды
|
Тауар аты
|
Сатылған тауарлар саны
|
Бірлік бағасы
|
1
|
Ет
|
2005
|
250
|
7980,00
|
1
|
Колбаса өнімдері
|
1258
|
360
|
8152,00
|
1
|
Сүт
|
4587
|
50
|
5133,00
|
1
|
Сыр
|
569
|
450
|
6551,00
|
2
|
Ұн
|
14789
|
45
|
3461,00
|
2
|
Тары
|
1258
|
26
|
1472,00
|
2
|
Рис
|
456
|
70
|
4216,00
|
2
|
Манка
|
789
|
50
|
3825,00
|
2
|
Гречка
|
123
|
85
|
4917,00
|
3
|
Сәбіз
|
478
|
15
|
1266,00
|
3
|
Қызылша
|
589
|
25
|
1977,00
|
3
|
Картоп
|
233
|
20
|
2511,00
|
3.6-кесте – Өткізілген тауарлар анализінің тізімдемесі
Тауар аты
|
Деректер
|
Қорытынды
|
Колбаса өнімдері
|
Төлеуге арналған шығындар(сома), тенге
|
8152
|
Төлеуге арналған шығындардың үлес салмағы
|
29,31%
|
Өткізілген сомасы
|
452880
|
Құнындағы үлес салмағы
|
4,85%
|
Сүт
|
Төлеуге арналған шығындар(сома), тенге
|
5133
|
Төлеуге арналған шығындардың үлес салмағы
|
18,45%
|
Өткізілген сомасы
|
229350
|
|
Құнындағы үлес салмағы
|
2,45%
|
Ет
|
Төлеуге арналған шығындар(сома), тенге
|
7980
|
Төлеуге арналған шығындардың үлес салмағы
|
28,69%
|
Өткізілген сомасы
|
501250
|
Құнындағы үлес салмағы
|
5,36%
|
Сыр
|
Төлеуге арналған шығындар(сома), тенге
|
6551
|
Төлеуге арналған шығындардың үлес салмағы
|
23,55%
|
Өткізілген сомасы
|
256050
|
Құнындағы үлес салмағы
|
2,74%
|
3.6-кестенің соңы
Қорытынды Төлеуге арналған шығындар(сома), тенге
|
27816
|
Қорытынды Төлеуге арналған шығындардың үлес салмағы
|
100,00%
|
Қорытынды Өткізілген сомасы
|
9345090
|
Қорытынды Құнындағы үлес салмағы
|
100,00%
|
Бақылау сұрақтары
1) Құрама кестелердің жұмыс қажеттілігін және механизмін түсіндіріңіз.
2) Эмитенттер бойынша ұсыныс құрылымы, Эмитенттердің ҚҚ-на сұраныс құрылым және Эмитент ұсыныстарының құрылымы өрістері арасындағы айырмашылықтарды түсіндіріңіз.
3) Пайдалылық пен тиімділіктің түсініктері
5-тәжірибелік жұмыс. Несие төлемдерін есептеуде қаржылық функцияларды пайдалану
Еxcel кестелік процессорында ПЛТ, ПРПЛТ, ОСПЛТ қаржылық функцияларын қолдану арқылы, кезеңдегі несие төлемдерін, негізгі төлемдер мен проценттік төлемдерді есептеуге болады.
Кезеңдегі жалпы төлемдерді есептеу үшін ПЛТ функциясы қолданылады. Оның аргументтері:
Ставка – кезеңдегі проценттік ставка.
КПЕР – төлемдерді төлеу мерзімінің жалпы саны.
ПС – ағымдық мерзімдегі несиенің жалпы сомасы.
БС – соңғы төлемнен кейінгі болашақта болуы мүмкін құн немесе қолма-қол баланс. Егер БС аргументі жоқ болса, оны 0 деп есептейді.
ТИП – 0 немесе 1 сандары төлемінің сәйкес мерзімінің басында немесе соңында төленетінін көрсетеді. Егер 0 болса, онда төлем кезеңнің соңында, ал 1 болса, кезеңнің басында төленеді.
Әр кезеңдегі негізгі төлемдер мен процент бойынша төлемдерді есептеу үшін ОСПЛТ және ПРПЛТ функцияларын қолданамыз. Бұл функциялардың да, тек төлем кезеңін көрсететін «Период» аргументінен басқа барлық аргуметтері ПЛТ функциясының аргументтерімен бірдей.
Бұл функцияларды қолдану барысында мыналарды есте ұстау қажет:
- Егер төлем ай сайын төленетін болса, онда жылдық процент ставкасын 12-ге бөлу керек;
- ПЛТ функциясымен есептелетін төлемдер негізгі төлемдер мен процент бойынша төлемдерді қамтиды, бірақ кейде қарызбен байланысты болатын салықтарды, резервтік төлемдерді немесе комиссияларды қоспайды.
1-есеп. 10000 доллар көлемінде 5 жылға, жылдық 17%-тік ставкамен алынған қарыз бойынша жылдық төлем шамасын есептеу.
3.8-суреттегі В3:В5 ұяшықтары аралығына алғашқы мәліметтерді енгіземіз. В6 ұяшығына жылдық төлем шамасын есептеу үшін ПЛТ функциясын қолданамыз, аргументтерін келесі түрде енгіземіз: =ПЛТ($B$3;$B$4;-$B$5)
Жалпы төлем сомасы: =B6*B4
Әр кезеңде төленетін процент бойынша төлемдер мен негізгі төлемдерді анықтау үшін келесі формулаларды енгіземіз:
3.8-сурет – Есептің шешуі
D10 ұяшығына =ПРПЛТ($B$3;C10;$B$4;-$B$5) енгізіп, курсорды төменгі оң жақ бұрышқа орналастырып, соңғы кезеңге дейін тышқанның сол жақ батырмасын басулы ұстап тартамыз.
Е10 ұяшығына =ОСПЛТ($B$3;C10;$B$4;-$B$5) енгізіп, курсорды төменгі оң жақ бұрышқа орналастырып, соңғы кезеңге дейін тышқанның сол жақ батырмасын басулы ұстап тартамыз. Нәтижесінде әр кезеңдегі процент бойынша төлемдер мен негізгі төлемдердің шамасы анықталады. Қарыз қалдығын өз бетіңізбен орындаңыздар.
2-есеп
Достарыңызбен бөлісу: |