Рылымдалған деректер. Аралық және жалпы қортындылар.

СТУДЕНТТЕРГЕ АРНАЛҒАН

АҚПАРАТТЫҚ –ДИДАКТИКАЛЫҚ БЛОК

ЖӘНЕ ӘДІСТЕМЕЛІК НҰСҚАУЛАР

ТӘЖРИБЕЛІК САБАҚҚА

Тақырып №

Ақпаратты өңдеу құралдары. MS Excel деректер қоры ретінде. Аралық қортынды. Консолидация.

Жинақтау кестесі және диаграммасы.»

Құрастырушы

аға оқытушы Дихамбеков Ж.К.

Семей, 2011 ж.

«Студенттерге арналған ақпараттық-дидактикалық блок және әдістемелік нұсқаулар тәжрибелік сабаққа: Тақырып № «Ақпаратты өңдеу құралдары. MS Excel деректер қоры ретінде. Аралық қортынды. Консолидация. Жинақтау кестесі және диаграммасы.»

кафедра (курс) мәжілісінде қарастырылды 2011жыл

Хаттама №

Курсқа жауапты Буланова Р.К.

1. Тақырыбы: «Ақпаратты өңдеу құралдары. MS Excel деректер қоры ретінде. Аралық қортынды. Консолидация. Жинақтау кестесі және диаграммасы.»

2. Оқу сағат саны: : 3 академиялық. сағат (150 мин)

3. Тақырып өзектiлiгi(оқу дәлелдемесi): Деректер қоры ретінде құрылған электрондық кестелермен жұмыс жүргізуге дағдылану.

4. Сабақтың мақсаты : Деректер қоры ретінде құрылған электрондық кестелермен орындауға болатын негізгі әрекеттерді меңгеру.

5. Оқыту міндеті: Деректер қорымен жұмыс істеуге, қортынды шығаруға және жинақтау кестесін құруға машықтандыру

6. Студент бiлуге тиiс:

КП Microsoft Excel интерфейсінің құрылымы:

1. Microsoft Excel құжаттарын жұмысқа қосу.

2. Кестелік процессор терезесінің: қолданба және құжат – негізгі элементтері.

3. Ұяшық пен ұяшықтар блогын белгілеу.

7. Студент iстей алуға тиiс:

- Мәзірді қолдану,

- Сұқбат терезелерімен жұмыс істеу,

- Кестеде бір және көп деңгейлі сұрыптау жүргізу,

- Аралық және жалпы қортынды кестесін құру,

- Жинақтау кестесін құру,

- Құжаттағы кестелерді консолидациялау.

8. Сабаққа дайындалу сұрақтары :

- негiзгi бiлiм бойынша :

1. Microsoft Exce құжаттарын жұмысқа қосу.

2. Кестелік процессор терезесінің: қолданба және құжат – негізгі элементтері.

3. Ұяшық пен ұяшықтар блогын белгілеу.

4. Сұқбат терезелері, оларда мәліметтерді көрсету және енгізу.

- осы сабақ тақырыбы бойынша:

1. Деректер қорын құру

2. Сұрыптау бірдеңгейлік және көпдеңгейлік

3. Сүзгі. Сүзгі қою. Белгілі деректер бойынша және қолданушы сүзгісі көмегімен деректер диапазоны бойынша сүзгілеу жүргізу. Деректерді қайтару. Сүзгіні алып тастау.

4. Деректерді құрылымдастыру. Аралық және жалпы қортындылар. Аралық және жалпы қортындылар кестелерін жасау..

5. Консолидация. Консолидацияланған деректер кестесін құру.

6. Жинақтау кестесі есебі және диаграммасы. Жинақтау есебі және диаграммасын құру.

Дебиеттер

Негiзгi әдебиеттер:

1. Дихамбеков Ж.К., Барлық мамандықтар бойынша Информатика пәнінен 1-курс студенттеріне арналған әдістемелік нұсқаулардың ақпараттық блоктар жинағы, Семей, 2009, 121-124

2. Абдуакитова А.Е., «Excel 2007» және Медициналық информатика, оқу құралы, Семей, 2010, 57-57

3. Изтлеуов М.К. және басқалар, Ақтөбе, 2005, 260 – 283.

Қосымша әдебиеттер:

1. Камардинов О. Информатика. Алматы : Ғылым, 2004, 227-235

2. Кафедра дайындаған тақырып бойынша методикалық құралдар

3. Дихамбеков Ж.К., Информатика пәнінен Кестелік процессор Microsoft Excel тақырыбына тестер жинағы, Семей, 2010, 22-27

10. АҚПАРАТТЫҚ-ДИДАКТИКАЛЫҚ БЛОК (АННОТАЦИЯ, ПОСОБИЯ)

Кестелік процессор Microsoft Excel тек қана кестелік деректер бойынша есептеулер жүргізуге ғана емес, сонымен бірге, деректерді сұрыптауға немесе белгілі шарттар бойынша таңдауға мүмкіндік беретін, деректер қоры ретінде де қолданылады.

Microsoft Excelді деректер қоры ретінде қолданғанда деректер кестеге жазылады және кестеде әр баған есімделуі керек, яғни баған есімдері жолында біріктірілген ұяшықтар болмауы керек. Егер кестедегі деректерді дұрыс түсіну үшін бағандар есімдерінде біріктірілген ұяшықтар болу керек болса (сурет 1, 2-ші жол), онда есімдерге бірнеше жол арнау керек. Олардың ең төменгісінде біріккен ұяшықтар болмауы және барлығы есімделуі керек (сурет 1, 3-ші жол).

Сұрыптау.

Деректер қорымен орындалатын жұмыстардың бірі – ол деректерді белгілі бір тәртіппен көрсету, яғни деректерге сұрыптау жүргізу. Сұрыптауды деректердің бір не бірнеше бағандары бойынша жүргізуге және екі бағыттың біреуімен жүргізуге болады: А-дан Я-ға қарай өсу не Я-дан А-ға қарай кему бағытында.

Деректер қорының бір бағаны бойынша сұрыптау жүргізу керек болса, осы бағандағы бірнеше деректерді белгілеп, келесі екі әрекеттің бірі орындау керек:

1) не «Бас» – «Өңдеу» тобындағы «Сұрыптау және сүзгі» - «А-дан Я-ға қарай сұрыптау» не «Я-дан А-ға қарай сұрыптау»

2) не «Деректер» - «Сұрыптау және сүзгі» тобындағы не

Сұрыптау кезінде жол толығымен сұрыпталатын бағандағы дерекпен бірге орын ауыстырады.

Деректер қорының бірнеше бағаны бойынша сұрыптау жүргізу керек болса, онда

1) кестеде бірнеше деректерді белгілеу керек;

2) «Сұрыптау» сұқбат терезесін (сурет 2) шақыру керек. Ол үшін орындалады

А) не «Бас» - «Өңдеу» тобындағы «Сұрыптау және сүзгі» - «Күйленетін сұрыптау...»

Б) не «Деректер» - «Сұрыптау және сүзгі» тобындағы «Сұрыптау»;

3) «Сұрыптау» сұқбат терезесінде бірінші деңгейлік сұрыптау шартын қою үшін орындау керек:

А) «Баған» «Бойынша сұрыптау» өрісіндегі стрелканы шертіп, пайда болған баған есімдері тізімнен қажеттісін таңдау,

Б) «Сұрыптау» «Мәндер» өрісіндегі стрелканы шертіп деректердің қандай түрлері: мәндер, ұяшық түсі, қарып түсі, ұяшық белгісі – бойынша сұрыпталу жүргізілетінін көрсету,

В) «Тәртіп» өрісіндегі стрелканы шертіп, тізімнен сұрыпталу бағытын «А-дан Я-ға» не«Я-дан А-ға», не «күйленетін тізім...таңдау керек;

4) «Деңгей қосу» батырмасын шертіп, пайда болған жолда 3-ші әрекеттегіндей екінші деңгейлік сұрыптау шарттарын енгізу керек;

5) 4-ші әрекетті қайталап үшінші, төртінші және т.с.с. сұрыптау деңгейлерінің (64 бағанға дейін) шарттарын қоюға болады;

6) Сұрыптау шарттарын орнатып болған соң, сұрыптау жүргізуді растап «OK» батырмасын шерту керек.

Сүзгілеу.

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

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

«Іздеу» өрісі арқылы сүзгілеу жүргізгенде сүзгі интерфейсінде мәтінді және сандарды іздестіруге болады.

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

Сүзгілеу жүргізу үшін мынандай әрекеттерді орындау керек:

1) Электрондық кестенің деректерін (тақырып жолынан басқа) толық не бөлігін белгілеу керек;

2) Бағандар тақырыбы жолына сүзгі белгісін (сурет 3) қою үшін орындау керек:

А) не «Бас» -«Өңдеу» тобындағы «Сұрыптау және сүзгі» - «Сүзгі»,

Б) не «Деректер» - «Сұрыптау және сүзгі» тобындағы «Сүзгі» ;

3) Сүзгілеу жүргізілетін баған тақырыбындағы стрелка белгісін шертіп, сүзгілеу шартына қолдануға болтын мәндер тізімін (сурет 4) шығарып алуға болады. Ескерту: Бағандағы деректер түріне байланысты «Мәтіндік сүзгі» не «Сандық сүзгі» бұйрығы шығады (сурет 4). Олардың бағыныңқы мәзірлеріндегі бұйрықтар (сурет 5) әртүрлі болады.

Сандық не мәтіндік сүзгісінің кезкелген элементін (сурет 5) таңдаса, «Қолданушы сүзгісі» сұқбат терезесі (сурет 6) шығады. Осы терезенің көмегімен сүзгілеу деректердің белгілі бір диапазоны үшін жүргізіледі (сурет 6).

4) Сүзгі шартын тізімнен таңдау үшін: а) «Барлығын белгілеу» алдындағы жалаушаны алып тастап, б) сүзгі шарты болатын деректер алдына жалауша қойып, «ОК» батырмасын шерту керек (сурет 4). Қордағы сүзгілеу шартына тең деректері бар барлық жолдар көрсетіледі де, қалған жолдар жасырылады. Мысалы, 4-суретте сүзгілеу бағанында 8, 10 және 13 сандары бар жолдарды көрсететін сүзгі қойылған.

5) Егер тізімдегі деректер (сурет 4) бойынша сізгілеу қарастырылып орырған жағдайға келмейтін болса, онда «Мәтіндік сүзгі» не «Сандық сүзгі» бұйрығын шерту керек. Пайда болған сандық не мәтіндік сүзгілер тізімінен (сурет 5) қажеттісін шертіп, ашылған «Қолданушы сүзгісі» сұқбат терезесін (сурет 6) толтырып, «ОК» батырмасын шертіп растау керек. 6-шы суретте көрсетілген «Қолданушы сүзгісі» сұқбат терезесінде сүзгіленетін бағанда 451 мен 872 аралығындағы деректер бар жолдарды көрсететін шарттар қойылған.

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

Сүзгіні алып тастау сүзгіні қою әрекетімен орындалады:

А) не «Бас» -«Өңдеу» тобындағы «Сұрыптау және сүзгі» - «Сүзгі»;

Б) не «Деректер» - группы «Сұрыптау және сүзгі» тобындағы «Сүзгі».

рылымдалған деректер. Аралық және жалпы қортындылар.

Кесте тізіміндегі деректерді топтау және қосындылау үшін 8-ге дейін деңгейден тұратын (1,2,3 сурет 7,1) құрылым жасалады. Бір деңгейде әр топ жеке құрылымдастырылады (сурет 7, 5). Әр ішкі құрылым оның символдары ретінде қолданылатын сандардың үлкенімен (2,3 сурет 7.1) белгіленеді. Құрылым символын құрылымдастырылған құжаттың көрінісін өзгертіп, алдыңғы сырт деңгей деректерін толық көрсету үшін пайдалануға болады. Толық деректерді көрсету не жасыру үшін «плюс», «минус» таңбаларын немесе құрылым деңгейін көрсететін 1, 2, 3 сандарды шертуге болады. Толық деректер ол бастапқы деректері бар жолдар не бағандар. Қортындылар құрылымдар (Сурет 7-де - ВостокАпрВсего, ВостокМарВсего, ЗападАпрВсего, ЗападМарВсего) бойынша және толық деректер бойынша ( Сурет 7-де Все продажи) есептеледі. Егер құрымдастыру жолдар бойынша жүргізілетін болса, толық деректер қортындыдан жоғары орналасады, ал құрылымдастыру баған бойынша жүргізілетін болса, толық деректер қортындыдан оңға қарай орналасады. Әр топ үшін қортынды жолды не бағанды немесе толық деректерді жылдам көрсету үшін жолдан (төмендегі мысалда көрсетілген), не бағаннан не жол мен бағаннан тұратын көп деңгейлі құрылым құруға болады.

Мысалы, 7-ші суретте:

1- Деңгейлер жолын көру үшін қажетті құрылымға сай символдарын шерту керек.

2- 1-ші деңгейде барлық жолдар бойынша толық деректерімен сату қосындысы (Все продажи).

3- 2-ші деңгейде әр айдағы әр аймақтағы сату қосындысы (ВостокАпрВсего, ВостокМарВсего, ЗападАпрВсего, ЗападМарВсего) .

4- 3-ші деңгей толық деректі жолдар (11-ден 13-ке дейінгі жолдар).

5- Көп деңгейлі құрылымда деректерді жасыру не көрсету үшін шертілетін және құрылым символдары.

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

Егер кітап үшін формула бойынша автоматты есептеу таңдалған болса, онда «Аралық бақылау» бұйрығы көмегімен толық деректердің әр өзгерісінде аралық мәндерді және жалпы қортындыны автоматты түрде қайта есептетуге болады.

Жалпы қортындылар толық деректер бойынша емес, аралық қортындылар негізінде есептеледі.

Аралық қортындының және жалпы қортындының бір деңгейін қою үшін орындау керек:

1) Аралық қортынды есептелетін бағандардың әр қайсысының тақырыбы бар, ал бағандағы деректер бір текті, және бос жолдар мен бағандар жоқ екендігіне көз жеткізу керек.

2) Диапазонда ұяшықты белгілеу.

3) Кестедегі (деректер қорындағы) деректерді сұрыптау, мүмкін болса көп деңгейлі, жүргізу керек: «Деректер» - «Сұрыптау және сүзгі» тобындағы «Сұрыптау». Мысалы, 7–ші суретте сұрыптаудың бірінші деңгейі «Регион» өсу бағытында, екінші деңгей «Месяц» өсу бағытында, үшінші деңгей «Продажи» өсу бағытында.

4) «Деректер» - «Құрылым» тобындағы «Аралық қортынды» орындап «Аралық қортынды» сұқбат терезесі ашу.

5) «Әрбір өзгерісте»өрісінде қортынды есептелетін баған таңдалады (сурет 8). 7-ші суреттте « Месяц» бағаны.

6) «Операция» өрісінде аралық қортындыны есептейтін қортынды функция таңдалады. 7-ші суреттегі мысалда Қосындыфункциясы таңдалған.

7) «Қортынды қосу» өрісінде қортынды жасауға қажетті деректері бар өрістер есімдері алдына жалауша қою қажет. 7-ші суреттегі мысалда жалауша Продажиалдына қойылған.

8) Әр қортындыдан соң автоматты түрде парақ үзілісін қамтамасыз ету үшін «Топтар арасында парақ соңы» алдына жалауша қою керек.

9) Қортынды жолды деректер жолы үстінде орналастыру үшін «Кортынды деректер астында» алдындағы жалауша алып тасталынады. Қортынды жолды деректер жолы астына орналастыру үшін. 7-ші суреттегі мысалда жалауша «Кортынды деректер астында» алдына қойылған.

10) Қойылған қортындылар қайталанбауы үшін «Ағымды қортындыларды алмастыру» алдынан жалаушаны алып тасталынады.

11) Қажет болған жағдайда «Аралық қортынды» бұйрығын қосымша қортындылар жолын қосу үшін 1-ден 7-ге дейінгі адымдарды басқа функцияларды қолданып қайталауға болады.

Аралық қортындының сыртқы деңгейін қою үшін орындау керек:

Аралық қортындының және жалпы қортындының бір деңгейін қою әрекеттерін 4-ші адымнан бастап орындау керек.

5-ші адымда «Әрбір өзгерісте» өрісінде қортынды есептелетін басқа баған қосу керек. 7-ші суреттегі мысалда Регионбағаны таңдалған.

Басқа әрекеттер сол қалыпында орындалады.

Консолидация.

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

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

Деректерді консолидациялаудың екі әдіс жолы бар.

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

Категорияға байланысты консолидация.Бұл әдіс қолданылады, егер әртүрлі көздегі деректер әртүрлі реттелген болса, бірақ бағандар мен жолдар бірдей аталған болса (мысалы, кестелер бірдей үлгімен жасалған бірақ элементтері әртүрлі немесе элементтерінің саны әртүрлі).

Деректерді консолидациялау үшін «Деректер» қосымшасының «Деректермен жұмыс» тобындағы «Консолидация» бұйрығы қолданылады. Консолидация жасау үшін формулаларды немесе жинақтау кесте есебін пайдалануға болады.

Консолидациялау жүргізу үшін орындалады:

1) Консолидацияланатын әрбір парақта деректерді төменде көрсетілгендей дайындау керек.

· Деректердің барлық диапазондары тізімдік пішім түрінде көрсетілуі керек: бағандардың бірінші жолында баған есімдері жазылған, басқа жолдарда біртекті деректер орналасқан, бос жолдар мен бағандар жоқ.

· Әрбір диапазон бөлек парақтарда болуы қажет. Диапазондар консолидация жасалатын парақта болмауы керек.

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

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

3) «Деректер» қосымшасының «Деректермен жұмыс» тобындағы «Консолидация» бұйрығын орындату. «Консолидация» сұқбат терезесі шығады (сурет. 9).

4) «Функция» өрісіндегі стрелканы шертіп, ашылған тізімнен қортынды функциясын таңдау.

5) Егер консолидация жасалатын деректер бар парақтар сол кітаптың ішінде болса, төмендегі әрекеттерді орындау керек:

А) Парақтағы деректерді белгілеуге мүмкіндік алу үшін «Сілтеу» өрісіндегі сұқбат терезесін кішірейтіп жабатын тиекті басу;

Б) Деректері консолидацияланатын беттің жарлығын шертіп, ондағы кестенің қажетті диапазонын белгілеп, «Консолидация» сұқбат терезесін толық ашу тегін шерту;

В) «Консолидация» сұқбат терезесінде «Қосу» тиегін шерту және А мен Б әрекеттерін барлық қажетті диапазондарды қосу үшін қайталау;

Г) Консолидацияны жаңарту әдісін таңдау:

· «Бастапқы деректермен байланыс құру» алдына жалауша қойылады;

· «Есім ретінде қолдану» тобында бастапқы диапазондардың қай жерінде есімдер орналасқанын көрсету үшін не «Жоғарғы жолдағы жазулар», не «Сол жақ бағандағы мәндер», не олардың екеуінің алдына жалауша қою керек.