Excel кестелік процессорымен жұмыс. Есептеу кестелерін құрастыру және диаграмма құру. Қорытынды кесте құру. Деректер қорымен жұмыс. 11 сынып

Зертханалық жұмыс №4

6 сағат

Сабақтың тақырыбы: Excel кестелік процессорымен жұмыс. Есептеу кестелерін құрастыру және диаграмма құру. Қорытынды кесте құру. Деректер қорымен жұмыс.

Сабақтың мақсаты: Excel құжаттарын форматтау және баспаға беру. Математикалық функция мәнін есептеу. Текстік және сандық деректерді Excel электрондық кестесіне енгізуді үйрену және формулалық есептеулерді шығару. Автотолтыру әдісін формуланы көшіру үшін  қолдану және кейбір корытындылау функцияларымен танысу.

1) Excel электрондық кесте құралдарымен деректерді өңдеу

6-сурет

1.       А1 ұяшығына кестенің тақырыбын жаз.

2.       Текстік берілгендерді А3:Н3, А9, А11 ұяшықтарына сәйкесінше енгіз.

3.       А3:А8 ұяшықтарына сатушылардың аты-жөнін жаз.

4.       B4:G8 ұяшықтарына сатылған автокөліктердің санын енгіз.

5.       B4:G8 ұяшықтар аралығын белгіле.

6.       Құралдар тақтасындағы қосынды (S) таңбасы бейнеленген тетікті бас.

7.       Әр адамның жарты жылда қанша автокөлік сатқанын қорытындыны бағанасына енгіз.

8.       Жарты жылда сатылған автокөліктердің жалпы санын пайыз (%) арқылы көрсету үшін, В11 ұяшығына =В9/$H$9 формуласын енгіз. Содан кейін құралдар тақтасындағы % таңбасы бейнеленген тетікті бас.

9.       В11 ұяшығындағы формуланы С11:Н11 ұяшықтар аралығына көшіру үшін, алдымен тышқан құрылғысының көрсеткішін В11 ұяшығына қойып, тышқанның сол жақ батырмасын жібермей оңға жылжыту арқылы Н11 ұяшығына қойыңыз.

10.   Кестені автоформаттау үшін: А2:Н11 ұяшықтар аралығын белгілеп алыңыз. Формат мәзірін (менюін) ашып, Автоформатирование бөлігін таңдап, көрсетілген тізімнен Простой - ды таңдап аласыз.

10. Диаграмма құру.

Мәндері х осьінің бойына жазуға қолданылатын B3:G8 ұяшықтар аралығын белгілеңіз. CTRL тетігін басып тұрып А4:А8 ұяшықтар аралығын белгілеп, CTRL тетігін босатыңыз. Мастер диаграмма баспасын басыңыз. Экранда Мастер диаграмм сұхбат терезесі пайда болады. Далее баспасын басу арқылы диаграмманың атын жаза аласыз, кейін Готово баспасын бассаңыз диаграммаңыз дайын болады.

2) Excel-де прайс-парақты дайындау және форматтау

1.       Пуск ® Программы ® Mіcrosoft  Excel  программасын жүктеңіз.

2.       Қолданылмаған жұмысшы парақ жарлығында шерту арқылы немесе Вставка ® Лист командасымен жаңа парақ құрыңыз. Оған Прейскурант деген ат беріңіз. Параққа жаңа ат беру үшін парақ жарлығын 2 рет шертіңіз.

3.       А1  ұяшығына Прейскурант текстін енгізіңіз.

4.       А2 ұяшығына Есептеу курсы текстін, В2 ұяшығына 1у.е.= текстін, С2 ұяшығына ағымды есептеу ($)  курсын енгізіңіз.

5.       А3 ұяшығына Тауар атттары текстін, В3 ұяшығына Баға(у.е), С3  ұяшығына Баға(теңге) текстін енгізіңіз.

6.       А бағанасының келесі торларына прейскурантқа кіретін тауарлар тізімін енгізіңіз(10).

7.       В бағанасының сәйкес торларына тауарлар бағасын шартты бірліктерде  (у.е) енгізіңіз.

8.       С4 ұяшығына  =В4*$C$2   формуласын енгізіңіз. Бұл формула  шартты бірліктегі бағаны теңге бағасына ауыстырады.

9.       А және В бағаналарының толтырылған ұяшықтарына сәйкес автотолтыру әдісімен 8-пункте енгізілген формуланы С бағанасының барлық ұяшықтарына көшіріңіз.

10.   С2 торындағы есептеу курсын өзгертіңіз. Өзгерісті байқаңыз.

11.   А1:С1 диапазонын белгілеңіз де Формат  ® Ячейки  командасын беріңіз. Туралау пунктінде Центр бойынша таңдап, ұяшықтарды біріктіру  жалаушасын тағайындаңыз.

12.   Шрифт кірісінде (вкладка) шрифт мөлшерін 14 беріп, начертания тізімінде Жартылай қара нұсқасын таңдаңыз. ОК батырмасын басыңыз.

13.   В2 ұяшығына  тышқанның оң жақ батырмасын басып Формат    ячеек командасын таңдаңыз. Оң жақ шетке туралауды беріп, ОК басыңыз.

14.   С2 ұяшығына тышқанның оң жақ батырмасын басып Формат    ячеек командасын таңдаңыз. Сол жақ шетке туралауды беріп, ОК басыңыз.

15.    В2:С2 диапазонын белгілеңіз. Форматтау панеліндегі  Границы батырмасының қасындағы ашылатын батырманы шертіңіз де, осы ұяшықтар үшін жалпақ (широкий) сыртқы жақтауды беріңіз (ашылған палитрада төменгі оң жақ бұрышта).

16.   А және В, В және С, С және D  бағаналар аттарының  шекараларында 2 рет шертіңіз. А,В және С бағаналар ені қалай өзгергенін байқаңыз.

17.   Алдын ала көру батырмасын шертіп, құжаттың басылымдағы көрінісі қалай болатынын көріп алыңыз.

18.   Жұмыс кітабын Book1.xls деп сақтаңыз.

3) Excel кестелік процессорында функцияларды қолдану

Есептеулерде әртүрлі функциялар қолданылуы мүмкін, сонымен бірге аргумент ретінде тұрақты, ұяшыққа сілтеме немесе аймақ (область) аты  да кездесуі мүмкін. Функцияның аргументтері оның атынан кейін жай (домалақ) жақшаның ішінде жазылуы керек. Формулалық өрнектердегі функцияларды клавиатурадан теруге болады. Егер аргумент ретінде ұяшыққа сілтеме қолданылатын болса, онда "тышқан" құрылғысы арқылы ұяшықты белгілеуге болады. Алайда, функциялармен жұмыс жасағанда құралдар тақтасындағы "Мастер функция - f_x" баспасы ыңғайлы немесе Вставка        Функция мәзірін (меню), Shіft+F3 пернелерін басуға болады.

EXCEL бағдарламасы апта күндерін, ай аттарын, толық күнтізбе және кез-келген сандар қатарын автоматты жалғастыруға ыңғайланған. Қандайда бір белгіленген ұяшықтан курсорды оңға немесе төмен тартқанда сандық мән келесі ұяшықтарда көбейіп, ал солға немесе жоғары жылжытсақ азаяды.

    Егер қатар мәндерінің өзгеру заңы күрделі болса, онда бұл процедура қиындай түседі. Бірінші, ұяшықты таңдап алып, және оған керекті мәнді енгізіп, Правка ® Заполнить ®  Прогрессия басқаруын беру керек. Экранда прогрессия параметрлерін беретін және толтыру бағытын көрсететін Прогрессия сұхбаттасу терезесі ашылады.

     Расположение тобы толтыру бағытын анықтайды, ал Тип тақтасынан мәндер түрін таңдап аласыз. Егер ұяшықтар күнтізбеден (датадан) тұрса, онда Единицы тақтасы қосымша шарттар енгізуге мүмкіндік береді.

Сұхбат терезесінің төменгі бөлігінде ең жоғарғы мән мен прогрессия қадамы беріледі. ОК пернесін басқаннан кейін қойылған параметрлерге сәйкес деректер кестеге  енеді.

Тапсырма:

Вариантқа сәйкес берілген математикалық өрнектерден аргументке қатысты шарттың орындалуына байланысты У функциясының мәнін есептеу керек. Аргументтің мәні берілген аралықта белгілі қадаммен өзгереді.

Толтыру протоколы:

1.       А1 ұяшығына -0,5 - ті енгіз.

2.       А2 ұяшығына t - ны енгіз.

3.       В2 ұяшығына у - ті енгіз.

4.       А3 ұяшығына бастапқы мән 0 - ді енгіз.

5.       Курсорды А3 -ке қойып, Правка ® Заполнить ® Прогрессия мәзіріне 0,5 қадаммен 10-ға тең  шеттік мәнді беру керек. Бағана бойынша орналастырыңыз.

6.       В3 ұяшығына курсорды қойып, Мастер функция-ны шақырыңыз. Сұхбат терезесіндегі ЕСЛИ функциясын таңдап алыңыз.

          Функцияның жазылу түрі: ЕСЛИ(логикалық өрнек; ақиқат мән; жалған мән)

         Логикалық өрнек терезесіне (и(а₃>=3; a₃<=5)) шартын енгізіңіз, курсорды ақиқат мән терезесіне қойып, $A$1*A3^2*ln(A3) формуласын енгізіңіз, курсорды жалған мән терезесіне қойып, Мастер функция-ны шақарыңыз. ЕСЛИ функциясын таңдап, логикалық өрнек терезесіне А3>5 шартын енгізіңіз, курсорды ақиқат мән терезесіне ауыстырып 1,5*cos(A3)^2 формуласын теріңіз, содан соң курсорды жалған мән терезесіне қойып КОРЕНЬ(А3) формуласын енгізіңіз де, ENTER пернесін басыңыз.

7.     В3 - тегі формуланы В3:В23 - аралығына көшіріңіз.

8.     Жұмыс кітабын Book2.xls деп сақтаңыз.

5-кесте  

4) Деректерді өңдеу және қорытындылау функцияларын қолдану

Тапсырма1:

1.       Excel электрондық кестесін жүктеңіз.

2.       Жаңа жұмыс кітабын құрыңыз.

3.       Ағымды жұмыс парағының жарлығын екі рет басып және осы параққа Деректер деген ат беріңіз.

4.       Файл  ® Сохранить как командасымен жұмысшы кітапты Book3.xls деп сақтаңыз.

5.       А1 ұяшығына Өлшем нәтижелері деген тақырып енгізіңіз.

6.       А бағанасының ұяшықтарына А2 ұяшығынан бастап кез келген сандар енгізіңіз.

7.       В1 ұяшығына Екі еселенген сөздерін енгізіңіз.

8.       С1 ұяшығына Мәндер квадраты сөздерін енгізіңіз.

9.       D1 ұяшығына Келесі санның квадраты сөздерін енгізіңіз.

10.   В2 ұяшығына  =2*A2  формуласын енгізіңіз.

11.   C2 ұяшығына  =A2*A2  формуласын енгізіңіз.

12.   D2 ұяшығына  =B2+C2+1  формуласын енгізіңіз.

13.   Созу әдісімен В2, С2 және D2 ұяшықтарын белгілеңіз.

14.   Белгіленген диапазонды (аралықты) қамтитын қоршаудың оң жақ төменгі бұрышындағы толтыру маркеріне тышқан көрсеткішін қойыңыз. Тышқанның сол жақ батырмасын басыңыз да, А бағанасында қанша жол болса, сонша В, С және D бағаналарында қоршауды тартыңыз.

15.   А бағанасының ағымды жолындағы тор мәнімен жұмыс жасауға арналған формулалар автоматты түрде түрленгенін байқаңыз.

16.   А бағанасында бір ұяшықтың мәнін өзгертсеңіз, В, С және D бағаналарының осы жолындағы сәйкес мәндері де автоматты түрде қайта есептелгенін байқаңыз.

17.   Е1 ұяшығына Масштабты көбейткіш деген сөзді енгізіңіз.

18.   Е2 ұяшығына 5 санын енгізіңіз.

19.   F1 ұяшығына Масштабтау сөзін енгізіңіз.

20.   F2 ұяшығына =А2*Е2 формуласын енгізіңіз.

21.   А бағанасының толтырылған ұяшықтарына сәйкес F бағанасы ұяшықтарына 20п. формуланы автотолтыру әдісімен көшіріңіз.

22.   Масштабтау нәтижесі дұрыс болмағанын байқаңыз. Бұл формуладағы Е2 сілтемесі (адресі) қатынасты түрде берілуіне байланысты.

23.   F2 ұяшына шертіңіз.

24.   F бағанасын F2 ұяшығындағы формуламен толтыруды қайталаңыз.

25.   F бағанасының торлары абсолюттік адрестерді қолдануға байланысты енді дұрыс есептелетініне көз жеткізіңіз. Жұмысшы кітапты  book3.xls атымен сақтаңыз.

Тапсырма2:

1.       Excel программасын жүктеңіз де, 1-тапсырмада құрылған book3.xls кітабын ашыңыз.

2.       Деректер атты жұмысшы парағын таңдаңыз.

3.       А бағанасының бос ұяшықтары ішінде біріншісін ағымды етіңіз.

4.       Стандарттық саймандар панеліндегі Автосумма батырмасын шертіңіз.

5.       Программа СУММ функциясын формулаға автоматты түрде қондырғанын және қосылғыш ұяшықтар аралығын дұрыс таңдағанына көз жеткізіңіз.

6.       А бағанасының бос ұяшықтары ішінде келесісін ағымды етіңіз.

7.       Стандарттық саймандар панеліндегі Вставка функции батырмасын шертіңіз.

8.       Категория тізімінде Статистические пунктін таңдаңыз.

9.       Функция тізімінде Срзнач функциясын таңдаңыз да, ОК батырмасын шертіңіз.

10.   Егер қажет ұяшықтар формула палитрасымен жабулы тұрса, онда оны тасымалдау  әдісімен басқа орынға ауыстырыңыз. Қажет торлар аралығын созу әдісімен дұрыс таңдап алып, Enter пернесін басыңыз (қосынды тұрған ұяшық бұл аралыққа түспеуі қажет).

11.   6-10пп. әрекеттер ретін қолданып берілген сандар жиыны ішінен ең кіші санды (МИН функциясы), ең үлкен санды (МАХ функциясы), жиындағы элементтер санын (СЧЕТ) есептеңіз.

12. Жұмысшы кітапты  book3.xls атымен сақтаңыз.

5) Деректерді анализдеу. Қорытындылау.

1.       Келесі өрістері бар деректер қоймасын құрыңыз: Сатушы, Марка, Шыққан жылы, Айналымы (оборот), Мерзімі. Жазбалар саны 10.

2.       Қорытындылауды автоматты есептеу функциясын қолданып, көрсетілген уақыт аралығында әр сатушы үшін айналым туралы деректерді табыңыз.

3.       Қорытындылауды автоматты есептеу функциясы көмегімен әр сатушы сатқан автомобильдер санын табыңыз.

4.       Деректердің әр тобын жеке парақтарға басып шығаруды дайындаңыз.

5.       Зертханалық жұмысқа есеп беру қорытындысын WORD-та дайындыңыз.

Excel-де берілеген осы есепті шығару үшін қорытындылауды автоматты есептеу функциясын қолданамыз. Ол Деректер (Данные) тізбелігінен Қорытынды (Итоги) командасымен шақырылады. Бұл функцияны қолдану үшін деректер тізім түрінде бейнеленуі керек.

6-кесте

Көрсетілген функция тек берілген деректер тобы үшін ғана қорытындылауды есептейтін болғандықтан, алдымен кестедегі деректерді сұрыптап қойған жөн. Бізге әр сатушы үшін айналым қорытындысын анықтау керек болғандықтан, кестедегі деректер сатушы фамилиялары бойынша сұрыпталуы керек . Сұрыптаудың екінші критериі ретінде автомобильдің шыққан жылын немесе авомобиль маркасын беруге болады.

                     7-сурет

Ұяшық көрсеткішін кесте ішіндегі кез-келген ұяға ауыстырыңыз да, Деректер (Данные) тізбелігінен Сұрыптау (Сортировка) командасын активтендіріңіз. Сұрыпталатын аймақ автоматты түрде белгіленеді. Сортировка терезесінде сұрыптау критериін көрсету қажет. ОК батырмасын басқаннан кейін Сортировка терезесіндегі кестедегі деректер сұрыпталып шығады.

                      8-сурет         

              Бөлек мәндердің қосындыларын алу үшін Деректер (Данные) тізбелігінен Қорытынды (Итоги) командасын пайдаланамыз (бұл кезде ұя көрсеткіші тізім кестесінің ішінде тұруы қажет). Промежуточные итоги (Аралық қорытынды) терезесі ашылады. Бізге әрбір сатушы үшін барлық сатылған автомобильдер бойынша айналым қорытындысын есептеу керек болғандықтан, При каждом изменении в (Ішіндегі әрбір өзгерісте) өрісінде Сатушы бағанасының тақырыбын таңдау қажет. Операция (Әрекет) өрісінде тізімнен Сумма (Қосынды) функциясын таңдаңыз. Содан соң ұяшықтары қорытындыларды есептеу үшін қолданылатын бағананы көрсету керек. Ол үшін Добавить итоги по: (Қорытындыларды қосу) өрісінде Айналымды таңдаңыз және Марка алып тастаңыз.

Марка бойынша канша екенін табу үшін  Операция (Әрекет) өрісіндегі тізімнен Количество значении  (мпав) функциясын таңдаңдап, Добавить итоги по: (Қорытындыларды қосу) өрісінде Марка таңдаңыз және Айналымды алып тастаңыз.

       Екі критерийі бойынша қорытындылар бір кестеде көрсетілу үшін ОК батырмасын басар алдында заменить текущие итоги (Ағымды қорытындыларды айырбастау) әрекетін алып тастау керек.

Командаларың нәтижелері 7-ші кестеде көрсетілген.

      Қорытындылары бар жолдарды жою үшін Промежуточные итоги терезесіндегі Убрать все (Бәрін алып тастау) кнопкасын пайдалану қажет.

       Келісім бойынша, қорытындылау мәндерінен тұратын жолдар деректер тобының бөлек мәндері бар жолдардың астына қондырылады. Егер қорытындыларды топ деректерінің алдына орналастыру қажет болса, Промежуточные итоги терезесіндегі Итоги под данными (Деректер астындағы қорытындылар) опциясын алып тастау керек.

7-кесте

Баспаға шығарғанда деректер қорытындылары бар әрбір деректер тобын бөлек қағазға басуға болады. Ол үшін Промежуточные итоги терезесінде Конец страницы между группами (топтар арасындағы парақ шеті) опциясын тағайындаңыз.

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

1.     Excel кестелік процессоры қалай жүктеледі?

2.     Ұяшық деген не? Ұяшыққа не енгізуге болады?

3.     Excel кестесіндегі бағаналардың/жолдардың мах саны неше?

4.     Ұялар диапазоны деген не? Ұялар диапазоны қалай белгіленеді?

5.     Ұяға формуланы қалай енгізуге болады? Ұядағы формуланы басқа ұяшықтар аралығында көшіру үшін қандай әрекет жасау керек?

6.     Кестені автоформаттау қалай жүзеге асады?

7.     Кестедегі деректер қосындысы қалай есептелінеді?

8.     Ұяшықтың абсолюттік адресі деген не? Ұяшық адресіндегі бағана әріптері, жол нөмірі өзгермеу үшін қандай таңба қолданылады?

9.     Деректерді  %  бейнелеу үшін қандай амал жасау керек?

10. Ұяларды/ұяны қалай белгілеуге болады?

11. Диаграмма деген не? Диаграмма құру үшін не істеу керек? Диаграмманы қалай өңдеуге болады?

12. Excel құжатының кеңейту аты қандай?

13. Бір жұмыс кітабында қанша парақ болады? Жаңа парақты қалай құруға болады? Парақ атын қалай өзгертуге болады?

14. Формуланы көшіру әдісі қандай?

15. Торлар диапазонын қалай белгілеуге болады?

16. Бағаналар енін қалай өзгерту керек?

17. Ұяшықтарды қалай біріктіруге болады?

18. Ұядағы тексті туралау әдістері қандай?

19. Excel құжатын баспаға қалай шығарады?

20. Excel-де функциялардың жазылу көрінісі қалай?

21. Функциялармен жұмыс жасау үшін қандай баспа қолданылады?

22. Функцияларды шақырудың неше жолы бар?

23. Функция аргументі ретіндегі ұяшыққа қалай жетуге болады?

24. Автоматты түрде толтырылатын деректер тобы қандай?

25. Правка ® Заполнить ® Прогрессия   мәзірі қандай әрекеттерді басқарады?

26. Прогрессия қадамы деген не?

27. ЕСЛИ функциясының жазылу түрі.

28. ЕСЛИ функциясындағы логикалық өрнек нені көрсетеді?

29. Мастер функция-дағы  функцияларды қалай таңдап алуға болады?

30. Текстік, сандық деректер және формулалар Excel кестесіне қалай енгізіледі? Формулалар қалай көшіріледі?

31. Салыстырмалы  және абсолютті  сілтемелер қай жағдайларда қолданылады? Абсолютті сілтемелер қалай  белгіленеді?

32. Деректер жинағын сипаттайтын қандай қорытындылау функцияларды білесіз.

33. Қорытындылау  функциялары қалай  шақырылады?

34. Деректер жиынының автоматты қосындысын қалай табуға болады?

35. Функциямен өңделетін ұяшықтар аралығы автоматты түрде қалай анықталады?

36. Кесте түрінде берілген деректерді сұрыптау үшін не істеу керек?

37. Сұрыптау критерийлері қалай көрсетіледі?

38. Қорытындылар жолдары қалай жойылады?

39. Қорытынды парақтарын баспаға басу жолдары қандай?

40. Қорытындылау процестері қалай жүргізіледі?