Автофильтрация 3 страница

1- жаттығу. Пәтер сатып алуға, 30 жылға, бастапқы салымы (первоначальный взнос) 20% құрайтын, 8% жылдық ставкамен берілген 35 000 $ ипотекалық ссуданы және оны өтеу үшін жасалатын ай сайынғы және жыл сайынғы төлемақыны есептеу керек.

1. Excel программасын іске қосыңыз;

2. Бірінші бетті (Лист1) Задача1 деп өзгертіп, келесі кестені /15.1-кесте/ құрыңыз:

15.1- кесте

	A	B	C	D
	Ипотеканы есептеу (функция ПЛТ)
	Бастапқы мәліметтер
	Баға	35 000$
	Алғашқы салым	20%
	Жылдық ставка	0,08
	Ссуда көлемі
	Ссуданы өтеу мерзімі		жыл
	Есептеу нәтижесі
	Ай сайынғы төлемақы
	Жыл сайынғы төлемақы
	Төлемақылырдың жалпы сомасы

3. Берілетін ссуданы есептеу үшін В6 ұяшыққа келесі: =B3*(1-B4) формула енгізіледі;

4. Ай сайынғы төлемақыны есептеу үшін курсорды В9 ұяшыққа апарып, ПЛТ функциясы шақырылады да, қажет параметрлер беріледі;

5. Жыл сайынғы төлемақы мен оның жалпы сомасын (30 жылғы төлемақы) өзіңіз есептеңіз.

2- жаттығу. Сіз 1 жыл мерзімге 10 000 доллар көлемінде ссуда алмақсыз. Ол ссуданы қайтару үшін бір жыл бойына, ай сайын 900 доллардан төлеп отырсаңыз, оның жылдық ставкасы (годовая процентная ставка) қанша болғаны?Бұл есепті шығаруда Подбор параметра командасы қолданылады.

1. Екінші бетті (Лист2), Задача2 деп өзгертіңіз;

2. Кесте құрыңыз /15.2- сурет/;

15.2 -кесте

	А	В	С
	Есепті Подбора параметра командасын пайдаланып шығару
	Ссуда көлемі
	Жыл мерзімі
	Ставка
	Ай сайынғы төлемақы

3. Бұл кестедегі В4 ұяшыққа бастапқы мән ретінде, мысалы,10,0% -ті жазамыз, бұл мән өзгеріп отырады және соңғы нәтиже осы ұяшықта шығады;

4. Формат->Ячейки командасын пайдаланып, B4 ұяшығының форматынүтірден кейін бір сан қалатындай етіп процентный формат түріне келтіріңіз;

5. В5 ұяшыққа= ПЛТ(B4/12;В3*12;-10000) формуласын жазып орындауға жіберіңіз.

6. Кестедегі формула жазылған В5 ұяшықты белгілеп алыңыз.

7. Сервис -> Подбор параметра командасын орындаңыз. Подбор параметра командасының терезесіндегі Установить в ячейке тұсында В5 ұяшықтың адресі тұрады;

8. Значение тұсына айлық төлемақының шамасы, яғни 900 жазылады;

9. Изменяя значение ячейки тұсына бастапқы мән жазылған В4 ұяшық көрсетіледі, (сол В4 ұяшыққа барып шертуге де болады), ОК басылады, нәтиже осы ұяшықта шығады.

3 - жаттығу. 10% жылдық ставкамен 3 жылға берілген 800 000 теңге займды өтеудің бірінші айының проценттік өсімін есептеңіз.

1. Үшінші бетті (Лист3), Задача3 деп өзгертіңіз;

2. Есеп шартына сәйкес кесте құрыңыз;

3. Проценттік өсімді есептеу үшін ПРПЛТ функциясын қолданыңыз.

4 - жаттығу. 100 000$ ссуда, 2% жылдық ставкамен 5 жылға беріледі деп есептеп, негізгі төлемақыны (основной платеж), проценттік төлемақыны (плата по процентам), әрбір жылғы жалпы төлемақыны және қалған қарызды табыңыз.

1. Төртінші бетті (Лист4) Задача4 деп өзгертіңіз;

2. Кесте құрыңыз /15.3- кесте/;

15.3- кесте

	А	В	С	D	Е
	Қарызды өтеу
	Ставка	2%
	Уақыт мерзімі
	Жылдық төлемақы
	Ссуда көлемі
	Жыл	Жыл басындағы займ сомасы	Проценттік төлемақы	Негізгі төлемақы	Қарыздың қалғаны

3. Жылдық төлемақыны есептеу үшін келесі: =ПЛТ(В3;В4;В5) формуланы пайдаланыңыз;

4. Проценттік төлемақы: =ПРПЛТ($B$2;A7;$B$3;$B$5) формуласымен есептеледі, мұны қалған ұяшықтарға да көшіру қажет;

5. Негізгі төлемақы: =ОСПЛТ($B$2;A7;$B$3;$B$5), формуласымен есептеледі, мұны қалған ұяшықтарға да көшіру қажет;

6. Қалған қарыз = алдыңғы қарыз – негізгі төлемақы, яғни =B7+D7 («+» болатын себебі негізгі төлемақы теріс сан болғандықтан), формуласымен есептеледі, бұл формуланы да қалған ұяшықтарға көшіру керек;

7. В8 ұяшыққа: =Е7 формуласы жазылады;

8. 5-ші жылғы қарыздың қалғаны 0 болуы тиіс, сонда қарыз немесе ссуда толығымен өтелген болып есептеледі.

5- жаттығу. Айталық сіз 60 000 теңгеге компьютер алдыңыз. Оның пайдалану мерзімі (срок эксплуатации) 5 жыл, ал содан кейінгі құны 10 000 теңгеге бағаланады. Компьютердің әрбір пайдаланылған жылдағы құнының қаншаға төмендейтінін табыңыз.

Бұл есепті АПЛ (нач_стоимость; остаточная_стоимость; время_эксплуатации) формуласын пайдаланып шығарасыз.

6-жаттығу. Айталық сіз 60 000 теңгеге компьютер алдыңыз. Оның пайдалану мерзімі (срок эксплуатации) 5 жыл, ал содан кейінгі құны 10 000 теңгеге бағаланады. Компьютердің бірінші және бесінші жылдардағы амортизация құнын табыңыз.

Бұл есепті АСЧ (нач_стоимость; остаточная_стоимость; время_эксплуатации; период) формуласын пайдаланып шығарасыз.

Өзіндік жұмыс тапсырмалары:

(Тапсырманы орындауға қажетті мәліметтер нұсқалар бойынша төменде берілген

15.4, 15.5, 15.6, 15.7, 15.8 -кестелерден алынады)

1. Сіз банкке П% жылдық ставкамен В мың. теңгені N жылға саласыз. N жылдан кейін сіздің есепшотыңызда қанша ақша болады?

15.4-кесте

Нұсқа
В
П		10.5			10.9
N

2. П% жылдық ставкамен N жылға берілген Т мың. теңге займның К жылдағы проценттік төлемақысын есептеңіз. Проценттік өсім квартал сайын қосылып отырады деп есептеңіз.

15.5-кесте

Нұсқа
Т
П
N
К

3. П% жылдық ставкамен К жылға берілген Т мың. теңге займның ай сайынғы төлемақысын есептеңіз.

15.6-кесте

Нұсқа
Т
П
К

4. Пәтер сатып алуға, бастапқы салымы А% құрайтын, i% жылдық ставкамен, n жылға берілетін ипотекалық ссуданы есептеңіз. Кестеде берілген мәліметтерді /15.7-кесте/ пайдалана отырып, оны өтеу үшін жасалатын ай сайынғы және жыл сайынғы төлемақыны есептеу керек.:

15.7-кесте

Нұсқа
n
Р
i
А

5. Р көлемдегі ссуда, i % жылдық ставкамен n жылға беріледі деп есептеп, негізгі төлемақыны (основной платеж), проценттік төлемақыны (плата по процентам), әрбір жылғы жалпы төлемақыны және қалған қарызды табыңыз.

15.8-кесте

Нұсқа
n
P
i

Бақылау сұрақтары:

1. Төлемақыны есептеуде қолданылатын қандай финанстық функцияларды білесіз?

2. ОСПЛТ функциясы қандай қызмет атқарады?

3. Амортизация деген не?

4. АСЧ функциясының АПЛ функциясынан өзгешелігі қандай?

5. ПРПЛТ функциясы не үшін қолданылады?

ЗЕРТХАНАЛЫҚ ЖҰМЫС № 16

Тақырыбы: «Қорытынды кестелер (Сводная таблица). Мәліметтер консолидациясы»

Мақсаты: Қорытынды кестелерді құра білуге және мәліметтер консолидациясын қолдана білуге үйрету.

Негізгі түсініктер.

Қорытынды кестелер үлкен тізімдердегі, мәліметтер қорындағы, жұмыс кітаптарындағы және т. б. кестелердегі мәліметтерді талдау, жалпылау және де басқа мақсаттарға пайдалану үшін қолданылады. Қорытынды кестелердегі мәліметтер бастапқы мәліметтермен байланысты болады, бірақ қорытынды кестелердегі мәліметтер үшін, мүмкін болған жағдайларда есептеулер жүргізуге болады. Жалпы қорытынды кестелер өте үлкен көлемдегі мәліметтерге талдау жасауда өте ыңғайлы құрал. Excel программасында қорытынды кестелерді мәліметттер қоры ретінде пайдаланылатын бір немесе бірнеше кестелердің негізінде құрады. Мысалы, бір өнім түрінің (товардың) түрлі аймақтардағы сатылу көрсеткішін сипатттау үшін қорытынды кесте құру керек болады.

Жатттығу жұмыстары.

1- жаттығу. Үш түрлі компьютерлік техника сату орталықтарында сатылған компьютерлердің ай сайынғы сатылу сомасын есептейтін қорытынды кесте құрыңыз.

1. Жұмыс кітабының бірінші бетін (Лист1) Свод_табл деп өзгертіңіз және 18- суреттегідей кесте құрыңыз;

18-сурет

2. Мастер Сводных таблиц терезесін шақыру үшін Данные®Сводная таблица командасын орындаңыз;

3. Пайда болған терезедегі в списке или базе данных Microsoft Excel, тұсына белгі қойыңыз да, Далее батырмасына шертіңіз;

4. Бастапқы мәліметтер орналасқан диапазонды (мәліметтер орналасқан кетені) белгілеңіз де, Далее батырмасына шертіңіз, келесі шыққан бетте новый лист тұсына белгі қойыңыз және Макет батырмасына шертіңіз;

5. Пайда болған терезеде, «жасалатын» қорытынды кестенің құрылымы анықталады: қорытынды кестедегі нәтиже жазылатын өрістің аты тұрған батырма белгіленіп, Данные облысына (Стоимость батырмасы Данные облысына) апарылады; Данные облысындағы Стоимость батырмасына екі рет шерту нәтижесінде Вычисление поля сводной таблицы диалогтық терезесі пайда болады, бұл терезеде нәтижені есептеуге қажет амал –операция таңдалынады (Сумма операциясын таңдаңыз);

6. Әрі қарай, қорытынды кестенің жолдарының қызметін атқаратын өрістер таңдалынады да, оларға сәйкес батырмалар Строка облысына (Магазин батырмасы Строка облысына) апарылады;

7. Келесі қадамда, қорытынды кестенің бағандарының қызметін атқаратын өрістер таңдалынады да, оларға сәйкес батырмалар Столбец облысына (Месяц батырмасы Столбец облысына) апарылады;

8. Мастер Сводных таблиц көмегіменжасалған қорытынды кестенің өрістері аттарының тұсында арнайы батырмалар болады, бұл батырмаларды пайдаланып, кестедегі көрсетілетін мәліметтерді өзгертіп қалауыңызша таңдауға болады;

9. Егер қорытынды кесте құруда пайдаланылған бастапқы мәліметтер өзгерген болса Данные -> Обновить данные командасын пайдаланып қорытынды кестедегі мәліметтерді де жаңалауға болады;

10. Қорытынды кестедегі мәліметтерді пайдаланып график салыңыз.

2жаттығу. Орналасу реті бойынша және категориясы бойынша мәліметтердің консолидациясын құру.

1. Жұмыс кітабының екінші бетін (Лист2) Консол_располож деп өзгертіңіз де, 19- суреттегідей кесте құрыңыз;

19-сурет

2. Табыс салығы мен қолға берілетін соманы есептеңіз;

3. Орналасу реті бойынша консолидация жасаңыз, ол үшін: курсорды консолидацияланған мәліметтер кестесі бастап орналасатын бірінші ұяшыққа апарыңыз (мысалы, А10 ұяшыққа);

§ Данные -> Консолидация командасын орындаңыз;

§ Пайда болған Консолидация диалогтық терезесіндегі тізімнен Сумма функциясын таңдап, подписи верхней строки, значения левого столбца тұсына белгі қойыңыз;

§ Курсорды Ссылка жолына орналастырып, бастапқы кесте берілген терезеден A2:D7 диапазонды белгілеңіз;

§ Добавить батырмасына шертіңіз, сол кезде, Список диапазонов жолында белгіленген диапазон адресі пайда болады;

§ Курсорды қайтадан Ссылка жолына апарып, енді екінші кесте орналасқан, F2:I7 диапазонды белгілеңіз;

§ Тағы да, Добавить батырмасына шертіңіз, сол кезде, Список диапазонов жолында белгіленген екінші диапазонның да адресі шығады; ОК батырмасына шертіледі.

4. Консол_располож бетіндегі екі кестені жаңа Консол_категор бетіне көшіріңіз және екінші кестені өгертіңіз: Премия деп аталатын баған қосып, оны толтырыңыз, Дятлов фамилиясын жол (жазба) түрінде тіркеңіз.

5. Категория бойынша мәліметтер консолидациясын құрыңыз, ол үшін:

§ курсорды консолидацияланған мәліметтер кестесі бастап орналасатын бірінші ұяшыққа апарыңыз, Данные -> Консолидация командасын орындаңыз;

§ Пайда болған Консолидация диалогтық терезесіндегі тізімнен Сумма функциясын таңдап, подписи верхней строки, значения левого столбца тұсына белгі қойыңыз;.

§ Курсорды Ссылка жолына орналастырып, бастапқы кесте берілген терезеден A2:D7 диапазонды белгілеңіз;

§ Добавить батырмасына шертіңіз, сол кезде, Список диапазонов жолында белгіленген диапазон адресі пайда болады;

§ Курсорды Ссылка жолына орналастырып, бастапқы кесте берілген терезеден F2:J8 диапазонды белгілеңіз;

§ Добавить батырмасына шерткенде, Список диапазонов жолында белгіленген диапазон адресі пайда болады, ОК батырмасына шертіңіз.

Өзіндік жұмыс тапсырмалары:

(Тапсырманы орындауға қажетті мәліметтер нұсқалар бойынша төменде берілген

16 -кестеден алынады)

1.... есептейтін қорытынды кесте құрыңыз /16- кесте, 1-тапсырма/.

2.... сәйкес орналасу реті бойынша мәліметтердің консолидациясын құрыңыз /16- кесте, 2-тапсырма/.

16-кесте

№	1 - Нұсқа	2 - Нұсқа	3 - Нұсқа	4 - Нұсқа	5 - Нұсқа
	Сату орталық-тарындағы компьютерлердің санын	Әрбір айдағы сатылған компьютерлер санын	Сату орталық-тарындағы компьютерлердің бағаларын	Әрбір айдағы компьютер лердің бағаларын	Компьютер лердің түріне сәйкес бағаларын
	Орташа жалақыға	Жалақының ең үлкен мөлшеріне	Жалақының ең аз мөлшеріне	Орташа жалақыға	Жалақының ең үлкен мөлшеріне

Бақылау сұрақтары:

1. Қорытынды кестелер не үшін қажет?

2. Қорытынды кестелер қандай команда көмегімен жасалады?

3. Мәліметтер консолидациясы не үшін қолданылады?

4. Мәліметтер консолидациясы қандай команда көмегімен орындалады?

5. Мәліметтер консолидациясының қандай түрлері бар?

зертханалық жұмыс № 17

Тақырыбы: «Microsoft Access деректер базасын басқару жүйесі. Бірнеше кестелерден деректер базасын жасау»

Мақсаты: Microsoft Access деректер базасын басқару жүйесімен, оның негізгі компоненттерінің қызметімен, деректер, жазба, өріс, кілттік өріс объектілерді жасау әдістерімен, өрістердің типтерімен таныстыру.

Негізгі ұғымдар: Кез келген деректер базасын басқару жүйесінің (ДББЖ) жұмысы – деректер базасын жасау, енгізу және өңдеу.

Microsoft Access ақпаратты сақтау және көрсету үшін қолданылатын жеке компоненттерден тұрады. Бұл компоненттерге кестелер, пішімдер (формы), есеп беру (отчет), сұрату (запрос), макростар, модулдер жатады. Microsoft Access-тің әрбір объектісін жасаудың кем дегенде 2 әдіспен: Конструктор көмегімен немесе Мастер көмегімен жасалады. Конструктор режімінде объект құрылымы жасалынады немесе өзгертіледі, ал Мастер режімінде объектілердің дайын нұсқалары таңдалынып, қолданылады.

Деректер дегеніміз – мерзімді түрде жұмыс істеуге мүмкіндік болу үшін сақталуы қажетті кез келген ақпарат. Көптеген деректер базасының құрылымы кестелік болады. Кестелік құрылымда деректер адресі жолдар мен бағандардың қиылысуы арқылы анықталады. Деректер базасындағы бағандар өрістер деп, ал жолдар жазбалар деп аталады. Өрістер деректер базасының құрылымын жасайды, ал жазбалар онда сақталатын ақпараттан тұрады.

Өріс – бұл деректер базасы құрылымының негізгі элементтері. Олардың өзіндік қасиеттері бар. Өріске қандай типті деректерді енгізуге болатыны, қандай деректерді енгізуге болмайтыны, сонымен қатар өрістегі деректермен не істеуге болатыны өрістің қасиетіне байланысты.

Мысалы, «Құны (Стоимость)» өрісіндегі деректерге жалпы нәтижені алу үшін қосу операциясын қолдануға болады. Ал «Телефон номері» өрісіндегі деректерді телефон номерлері санмен жазылса да қосуға болмайды. Бұл өрістердің қасиеттері әртүрлі және әртүрлі типке жатады.

Өрістердің әртүрлі типтері әртүрлі қасиеттерге ие.

1. Мәтіндік өрістің негізгі қасиеті – өлшемі.

2. Сандық өріс сандық деректерді енгізу үшін қажет.Мұның да өлшемі бар, бірақ сандық өрістер әртүрлі болады, мысалы, бүтін сандарды енгізу үшін және нақты сандарды енгізу үшін. Соңғы жағдайда өрістің өлшемімен қатар санның ондық бөлігінің өлшемі беріледі.

3. Күн мен уақытты енгізу өрісі Күн/уақыт (Дата/время) типті болады. «Иә немесе Жоқ», «1 немесе 0», «Ақиқат немесе Жалған» және тағы осы сияқты тек екі мәні бар логикалық деректерді енгізу үшін арнайы логикалық өріс қолданылады. Мұндай өрістің ұзындығы әрқашанда 1-ге тең.

4. Өрістің ерекше өрісі – Ақшалы (Денежный) өріс. Мұнда қандай деректер сақталатыны атауынан-ақ белгілі. Ақшаның сомаларының көрсеткіштерін сандық өрісте де сақтауға болады, бірақ ақшалық типтегі түрде олармен жұмыс жасау жеңіл. Бұл жағдайда компьютер санды ақша бірліктерімен бірге көрсетеді, рубль мен тиынды, фунт пен пенсты, доллар мен центті ажыратады.

5. Қазіргі кездегі деректер базаларында тек сандар мен әріптерді ғана емес, сонымен бірге суреттерді, сазды клиптер мен бейнежазбаларды сақтауға болады. Мұндай объектілерге арналған өріс OLE объектісінің өрісі деп аталады.

6. Мәтіндік өрістегі бір кемшілік оның өлшемінің шектеулігіне (256 символдан аспайды) байланысты. Егер өріске ұзын мәтін қою керек болса, бұл үшін MEMO типті өріс қолданылады, онда 65 535 символға дейін сақтауға болады. MEMO өрісінің ерекшелігі – деректер өрісте емес басқа жерде сақталады, ал өрісте тек мәтіннің қайда орналасқанын көрсететін көрсеткіш (указатель) сақталады.

7. Санауыш (Счетчик) өрісі. Бір қарағанда бұл қарапайым сандық өріс сияқты, бірақ оның автоматты түрде мәні артатын қасиеті бар. Егер деректер қорында мұндай өріс болса, онда жаңа жазбаны енгізу кезінде, оған алдыңғы жазбадағы сол өрістің мәнінен бірге артық сан автоматты түрде енгізіледі. Бұл өріс жазбаларды нөмірлеуде ыңғайлы.

Егер ақпарат қарапайым құрылымда сақталған болса, онда олармен арнайы деректер қорын басқару жүйесінің көмегінсіз жұмыс істеуге мүмкіндік бар. Практика жүзінде бір-бірімен өзара байланысты көптеген кестелерден тұратын күрделі құрылымдармен жұмыс жасауға тура келеді. Бір-бірімен өзарабайланысты кестелерден тұратын деректер қоры реляциялық деректер қоры деп те аталады.

Дата добавления: 2014-11-07