Жалпы және аралық қорытындыларды автоматты түрде есептеу

5. Жалпы және аралық қорытындыларды автоматты түрде есептеу

Жалпы және аралық қорытындыларды автоматты түрде жүргізу мүмкіндігін алу үшін кестедегі деректер тізім немесе деректер базасы түрінде ұйымдастырылуы керек.

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

Егер кестелерде деректер қате (тізім түрінде емес) ұйымдастырылған болса, онда Excel кесте құрылымын түсінбеуі және аралық қорытындылар құра алмауы мүмкін.

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

Деректердің бір немесе сол тобы үшін біруақытта бірнеше функциялар көмегімен аралық қорытындыларды есептеуге, сонымен қатар «қатпарлы» (вложенные) немесе көпдеңгейлі қорытындылар есептеуге болады.

Аралық қорытындылар жүргізу кезінде автоматты түрде мыналар есептелінуі мүмкін:

Қосынды Сандар саны

Мәндер саны ығыса ауытқу

Орташа ығыспай ауытқу

Максимум ығысқан дисперсия

Минимум ағыспаған дисперсия

Көбейтінді

Автоматты түрде қорытынды жүргізу үшін келесі іс-әрекеттер тізбегін орындау қажет:

1. 
   баған бойынша аралық қорытындыны есептеу үшін қажет тізімді сұрыптау;
   
2. 
   кестенің немесе қажет диапазонның кез келген ұяшығын ерекшелеу;
   
3. 
   ДАННЫЕИтоги командасын орындау;
   
4. 
   Тізімнен аралық қорытынды диалогтік терезесінде При каждом изменении в: топтардан тұратын бағанды таңдау. Бұл тізімнің сұрыпталуы жүргізілген баған болуы керек;
   
5. 
   Операция тізімінен қорытынды жүргізу үшін қажет функцияны таңдау, мысалы, Қосынды;
   
6. 
   Добавить итоги по: тізімінен қорытынды жүргізуге қажет мәндерден тұратын бағанды таңдау.
   

Бір немесе сол кесте үшін ДАННЫЕИтоги командасы бірнеше рет орындалуы мүмкін. Егер Промежуточные итоги диалогтік терезесінде Заменить текущие итоги переключателі алынса, алдын-ала құрылған қорытындылар жаңалармен ауыстырылуы, сонымен қатар өзгеріссіз қалуы мүмкін.

№4 дәріс. Подстановкалар кестесі

Дәріс жоспары

1. 
   Бір параметрі бар подстановкалар кестесі
   
2. 
   Қосымша формулалары бар подстановкалар кестесі
   
3. 
   Екі параметрі бар подстановкалар кестесі
   

1. 
   Ехсеl-де алғашқы деректердің әртүрлі жиынынан нәтиже тәуелділігін анализдеу үшін «Подстановка кестесі» пайдаланылады. «Подстановка кестесі» - бұл бір немесе бірнеше фрмулаларға бір немесе екі аргументтердің (параметрлер) берілген мәндерін қою нәтижесінен тұратын, кестені алуға мүмкіндік беретін Ехсе1 құралы. Подстановка кестесі – деректер анализі құралының бірі. Подстановкалара кестесін құруға әкелетін бірінші есеп. Бір праметрге тәуелді функциялар жиынын қарастырайық: F1(a), F2(a), …Fm(a). Осы функциялардың әрбіреуі Excel формуласымен берілсін дейік. Осы функциялардың а параметрінің мәнінен тәуелділігін анализдеу қажет болсын. Көбінесе бізді параметрлер a1, a2, …an мәндерінің соңғы жиыны үшін нәтижелер қызықтырады. Бұл жағдайда анализдеу үшін бізге қажет – бұл n*m өлшемді тікбұрышты кесте тұрғызу, олардың элементтері Fj(ai) болады. Excel осындай кестені тұрғызуға мүмкіндік береді.
   

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

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

«Подстановка кестесін» пайдалану принципі келесіден тұрады:

. есептің алғашқы дерегі, сонымен қатар аргумент-параметр мәнінің бірі беріледі.

. параметрдің мәндер диапазоны (баған немесе жол түрінде) беріледі.

. аргументтің берілген мәнінен функция мәнін орналастыру үшін орын бөлінеді (оң жаққа баған немесе төмен жаққа жол).

. аргумент-параметрден функция тәуелділігін анықтайтын формула беріледі.

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

. бағанның әрбір ұяшығы үшін жолдың көрші ұяшығына, яғни жол бойынша

. жолдың әрбір ұяшығы үшін бағанның көршіұяшығына, яғни баған бойынша.

2. Қосымша формулалармен подстановка кестесі

Подстановка кестесі бір ғана емес, бірнеше формулалардан тұруы мүмкін. Қосымша формулалар, егер подстановка жол бойынша жүргізілсе, оның оң жақ және баған бойынша подстановкасында бар формуланың төменгі жағында подстановка кестесінде орналасады. Содан соң параметр мен формулалардан тұратын блок ерекшеленеді, «Данные-Таблица подстановки»... командасы орындалады. Кестеден жеке ұяшықтарды жоюға болмайды, барлық кестені тазартуға болады. Ол үшін есептелген мәндерді ерекшелеп, Правка/Очистить/ Содержимое немесе DELETE пернесін басу командасын орындау қажет.

«Данные/Таблица подстановки»... командасын пайдалана отырып, екі параметрмен кесте құруға болады. Осы кезде оның біреуінің мәні бағанда, ал басқасының мәні – жолда орналасуы керек. Формула ұяшық блогының сол жақ жоғарғы бұрышында тұруы керек. Подстановка нәтижесі команда орындалғаннан кейін баған мен жолдың қиылысына қойылады. Подстановка кестесін құру өтп оңай, бірақ оны пайдалануға белгілі шектеулер қойылған. Ең басты шектеу – ол алғашқы деректері бар тек бір немесе екі ұяшықтарымен уақытша операция жасауы мүмкін. Басқаша айтқанда, алғашқы деректері бар үш немесе одан да көп ұяшық комбинациясын пайдалана, подстановка кестесін құруға болмайды.

Бір енгізу жолы бар подстановка кестесінде бір енгізу параметрінің әртүрлі мәндері болған кезде бір немесе бірнеше формулалар бойынша есептеулер нәтижесін жүргізеді.

Кестені жұмыс бетінің кез келген жеріне орналастыруға болады. Сол жақ баған енгізу параметрінің әртүрлі мәндерінен тұрады. Жоғарғы жол формулалар немесе формулалары бар ұяшыққа сілтемеден тұрады, ол бойынша нәтиже есептеледі. Формулаға сілтеменің кез келген санын пайдалануға болады (немесе тек біреу ғана). Жоғарғы сол жақ ұяшық пайдаланылмайды. Excel әрбір алғашқы мәнінен енгізу ұяшығына қою нәтижесінде алынады, және нәтижені сәйкес бағанға орналастырады.

Төменде келтірілген мысалда ипотекалық қарыз есептелетін жұмыс беті пайдаланылады.

3-сурет. Жұмыс бетінің мысалы

7%-тен 9%-ға 0,25% қадаммен ставканың өзгеруі кезінде Қарыз мөлшері, Айлық төлем, Комиссиялық төлемнің жалпы соммасы ұяшықтарында орналасқан, формулалар бойынша есептелген мәндер көрсетілетін кесте құру мысалын қарастырайық. 3-суретте сипатталған мысал үшін подстановка кестесіне дайындық көрсетілген. 2 жол формулалары бар сәйкес ұяшықтарына сілтемеден тұрады.

4-сурет. Бір енгізу жолы бар подстановка кестесін құруға дайындық

Подстановка кестесін құру үшін ұяшықтар диапазонын ерекшелеу керек (қарастырылып отырған мысал үшін G2:K11), содан кейін Данные | Таблица подстановки командасын таңдаңыз. 4-суретте көрсетілген диалогтік терезе пайда болады.


5-сурет. Подстановка кестесі диалогтік терезесі

Алғашқы деректер қойылатын беттің ұяшығын анықтау қажет. Барлық алғашқы деректер бағанда орналасқандықтан, адресті жол бойынша мәндерді ... қою керек (Подставлять значения по строкам в) өрісіне қою керек (біздің мысал үшін $D$7 енгізу керек). OK батырмасына бассақ, Excel сәйкес нәтижелермен кестені толтырады (6-сурет).

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

Екі енгізу жолы бар подстановка кестесі екі енгізу параметрлерінің өзгеруі кезінде есептеу нәтижесін экранға шығаратуға мүмкіндік береді. Кестенің бұл типінің макеті 7-суретте көрсетілген.

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

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

• 
  Баспа материалдарының құны. Бір жарнамалық буклетті басып шығару құны. Баға санына байланысты өзгереді: 0,20 – егер экземплярлар саны 200000 аспаса; 0,15 – 200001-ден 300000-ге дейін экземплярлар; 0,10 – егер 300000 көп болса. Басып шығарылған материалдар (олардың санына байланысты) құны келесі формула бойынша анықталады:
  

• 
  Пошталық шығындар. Олардың құны белгілі және бір пошталық жіберуге 0,32 құрайды.
  
• 
  Респонедттер саны. Жауаптардың шамаланған санын алу. Ол шамаланған жауап пайызына және таратылған материалдар санына байланысты анықталады. Бұл ұяшық үшін формула келесідей: =Процент_ответевших*Разослано_материалов
  
• 
  Бір респондентке түсетін табыс. Бұл белгілі мән. Компанияға әрбір тапсырыс үшін 22 пайда алатыны белгілі.
  
• 
  Жалпы пайда. Жалпы пайда қарапайым формула бойынша есептеледі., онда бір тапсырыстан алынған табыс мөлшері тапсырыс санына көбейтіледі: =Доход_на_одного_респондента*Число_респондентов.
  
• 
  Жалпы шығындар. Осы ұяшықта орналасқан формула бойынша жарнамаға кететін жалпы шығынды есептейді, оған баспа материалдарының және пошталық қызметтің құны кіреді:
  

• 
  Таза пайда. Жалпы табыс пен жалпы шығын айырмасы ретінде анықталады.
  

Екі енгізу жолы бар подстановка кестесі деректері бойынша үшөлшемді диаграмма құру керек (10-сурет).

Подстановка кестесі көмегімен деректерді талдау тиімді болып табылады. бірақ оның бірнеше кемшіліктері бар.

біруақытта тек бір немесе екі алғашқы параметрінің өзгеруі кезінде есептелетін деректерді талдау керек.

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

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

Бұл мысалда үш сценарий анықталған, олар 1-кестеде келтірілген. Ең жақсы жағдай үшін сағаттық төлем тарифі мен материалдар құны сценарийінде, ең жаман жағдай үшін сценарийде бұл мәндер ең үлкен. Үшінші сценарийде немесе ең ықтимал жағдайда осы параметрлердің аралық мәні пайдаланылады (олар менеджермен факторлар қатары есебімен анықталады). Менеджерлер ең жаман жағдайға дайын болуы керек, бірақ егер ең жақсы жағдай сценарийі жұмыс істеп кетсе, оларға не болатынын білу қажет.

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

• 
  Сценарий атауы. Бұл өрісте сценарийдің кез келген атын көрсету керек, бірақ ол бір нәрсені білдіретіндей болу керек.
  
• 
  Өзгертілетін ұяшықтар. Сценарий үшін алғашқы деректер табылатын ұяшық. Бұл өріске ұяшықтың абсолютті адресін немесе оның атын енгізуге болады. Бірнеше ұяшықты таңдауға болады, сонымен қатар олардың барлығы қатар орналасуы керек. Атау берілген әрбір сценарийде өзгертілетін ұяшықтардың бір жиынын немесе әртүрлі өзгертілетін ұяшықтар жиынын пайдалануға болады. Бір сценарий үшін өзгертілетін ұяшық саны 32 санымен шектелген.
  
• 
  Ескерту (примечание). Excel бұл өрісіне сценарийді құрған, сонымен қатар оны құру күні туралы ақпарат орналасады. Бірақ Сіз бұл мәтінді редактрлеп, оған жаңа мәтін енгізуге немесе оны алып тастауыңызға болады.
  
• 
  Қорғау. Сценарийді өзгерістен және оны жасырып қоюдан қорғауға мүмкіндік беретін екі опцияны егер жұмыс беті қорғалған және Защитить лист диалогтік терезесінде Сценарии опциясы белсендендірілген жағдайда ғана белсендендіруге болады. Сценарийді қорғау оны басқа біреумен модификациялауды болдырмайды, ал жасырын сценарий Сценарийлер диспетчері диалогтік терезесінде пайда болмайды.
  

• 
  Сервис – Настройка командасын орындау.
  
• 
  Настройка диалогтік терезесінен Команды вкладкасын таңдау.
  
• 
  Сервис категориясын таңдау.
  
• 
  Сценарий құралын ерекшелеу және оны құралдар панелінің кез келген жеріне тартып апару.
  
• 
  Жабу батырмасын басу.
  

№6 дәріс. Құрама кесте (сводные таблицы) көмегімен деректерді талдау және жалпылау

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

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

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

Құрама кестелер құру үшін деректердің әртүрлі қайнар көздерін пайдалануға болады. Көбінесе тізім немесе деректер базасы түрінде ұйымдастырылған Excel кестесі пайдаланылады.

Сонымен қатар, құрама кестелерді былайша құруға болады:

• 
  деректер консолидациясының (бірігу) нәтижесінде алынған кестелер негізінде;
  
• 
  басқа құрама кестелер негізінде;
  
• 
  деректердің сыртқы қайнар көздері негізінде.
  

Құрама кестелерді құру және модификациялау Мастера сводных таблиц көмегімен орындалады, оның терезесі ДАННЫЕСводная таблица командасын енгізгеннен кейін немесе Сводные таблицы құралдар панелінде Мастер сводных таблиц батырмасын шерту арқылы экранда пайда болады.