Бақылау сұрақтары:
1) MS EXCEL-де формула қандай таңбамен басталып жазылады?
2) Сандардың қосындысын есептейтін функцияны ата.
3) MS EXCEL-дегі адрестеу түрлері және олардың қолданылуы ерекшеліктері қандай?
3-тәжірибелік жұмыс. Еxcel электронды кестесінде мәліметтер қорын құруға, автофильтр құруға, мәліметтер қорынан таңдау жасауға үйрету.
Афтофильтр тек белгілі бір мәннен тұратын жолды экранға шығаруға мүмкіндік береді.
Промежуточные.итоги математикалық функциясы тізімді фильтрлеу нәтижесінде болған жасырын жолдарды ескермей, қорытынды мәнді есептейді.
Промежуточные.Итоги(Номер функции; Ссылка1)
БДСУММ функциясы көрсетілген мәліметтер қорындағы белгілі бір ақпараттардан анықталған критериге сәйкес келетін барлық мәндерді қарап, қосындысын есептейді.
БДСУММ(База данных; Поле;Критерий).
1-тапсырма. Бірнеше менеджерлермен жүзеге асырылған сату туралы мәліметтерден тұратын мәліметтер қорын құру және қажетті ақпаратты алу.
1-қадам: Мәліметтер қорының жұмыс парағын құру.
В1 ұяшығына Продажи за ноябрь, А3 ұяшығына Дата, С3 ұяшығына Кому, D3 ұяшығына Сумма сөздерін енгізіңіз; Лист1 жұмыс парағына Ноябрь атын берңіз.
Данные-Форма меню командасын таңдаңыз; Оk пернесін басыңыз. Ноябрь аты берілген экранда форма диалогы ашылады.
Диалог алаңдарын 3.7-суреттегідей толтырыңыз. Келесі алаңға көшу үшін Таb клавишасын немесе тышқанды қолданамыз. Әр жолды енгізіп болғаннан кейін Добавить пернесін басамыз. Барлық жазуларды енгізіп болғаннан кейін Закрыть пернесін басамыз.
2-қадам: Афтофильтр құру.
С1 ұяшығына СУММ функцисын қолданып, айдағы жалпы сату көлемін есептейміз. Мәліметтер базасына әлі де жазулар енгізілетіндіктен максимальды мүмкін диапазонды көрсетеміз: СУММ(D4:D16384). Е3 ұяшығына Итого сөзін енгіземіз.
Функция шеберіндегі Математические категориясындағы Промежуточные Итоги функциясын таңдаймыз. Номер функции алаңына 9 санын енгіземіз. Ссылка1 алаңына D4:D16384 диапазонын көрсетеміз. Оk пернесін басыңыз. F3 ұяшығында нәтиже шығады: 70700.
Енді автофильтр құрамыз. Баған тақырыптары орналасқан А3: D3 диапазонын ерекшелейміз.
Данные•Фильтр•Автофильтр меню командаларын таңдаймыз. Ерекшеленген әр бағанның оң жағында автофильтр кнопкасы пайда болады.
Менеджер Ивановпен жүзеге асырылған жалпы сату көлемі қажет болсын. Ол үшін:
Менеджеры бағанының оң жағындағы автофильтр кнопкасын басыңыз. Мүмкін болатын фильтр тізімі шығады.
3.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-тапсырма. Эмитенттер мен құнды қағаздардың түрлері бойынша сұраныс пен ұсынысты талдау үшін 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
|
Негізгі қорлардың пайдалылығы таза пайданы негізгі қорлардың орташа құнына бөлу арқылы табамыз. Меншікті капиталдың пайдалылығы таза пайданы меншікті капиталға бөлгенге тең.
Достарыңызбен бөлісу: |