Зводная табліца Excel: як стварыць і працаваць? Праца са зводным табліцамі Excel

Многія офісныя карыстачы часцяком сутыкаюцца з цэлым шэрагам праблем пры спробе стварэння і рэдагавання якіх-небудзь офісных дакументаў. Часцяком звязана гэта з тым, што ў кампаніях выкарыстоўваецца некалькі офісных праграм, прынцыпы працы ў якіх могуць моцна адрознівацца. Асабліва шмат цяжкасцяў можа стварыць зводная табліца Excel.

На шчасце, параўнальна нядаўна з'явіўся MS Office 2010-2013, які не толькі ўключае ў сябе шэраг абноўленых праграм для апрацоўкі тэкставых файлаў, табліц, баз дадзеных і прэзентацый, але і дазваляе працаваць з імі адначасова некалькім работнікам, што ў карпаратыўнай асяроддзі проста неацэнна.

Чаму менавіта новыя версіі?

Каб разабрацца ў гэтым пытанні, трэба ўяўляць сабе ўсе найбольш значныя змены, якія адбыліся з гэтым офісным пакетам.

Як і раней, менавіта Excel з'яўляецца другой па запатрабаванасці праграмай, якая дазваляе не толькі ствараць простыя табліцы, але нават ствараць даволі складаныя базы дадзеных. Як і ў іншыя кампаненты, у яго была дададзеная кнопка «Офіс», націснуўшы на якую вы можаце захаваць дакумент у патрабаваным вам фармаце, змяніць яго або паказаць патрабаваныя меры па яе абароне. Быў значна абноўлены модуль абароны дакумента.

Што ж тычыцца найбольш значных змяненняў у Excel, то варта адзначыць выпраўленне мноства памылак у формулах, з-за чаго ў мінулых версіях нярэдка ўзнікалі даволі грубыя памылкі пры разліках.

Трэба сказаць, што новая версія пакета - гэта не проста набор офісных праграм, але і наймагутнейшы інструмэнт, прыдатны для вырашэння нават вельмі складаных задач. Як вы маглі зразумець, сёння намі будзе разгледжана зводная табліца Excel.

Што гэта такое і для чаго яна патрэбна?

Найбольш значным змяненнем ў новых версіях «Офіса» з'яўляецца іх цалкам перакроены інтэрфейс, які быў названы стваральнікамі Ribbon (стужка). У стужках усе 1500 каманд зручна згрупаваныя па катэгорыях, а таму вам не прыйдзецца іх доўга шукаць. Каб цалкам адпавядаць гэтым параметрах, распрацоўшчыкі дадалі ў Excel яшчэ і палепшаныя зводныя табліцы.

Сёння разгледзім Excel «для чайнікаў». Зводныя табліцы - гэта адмысловая прылада, у якім наглядна групуюцца вынікі якога-небудзь працэсу. Прасцей кажучы, з іх дапамогай можна ўбачыць, колькі тых ці іншых тавараў прадаў кожны прадавец за сваю працоўную змену. Акрамя таго, іх выкарыстоўваюць у тых выпадках, калі неабходна:

• Падрыхтаваць аналітычныя выкладкі для напісання выніковых справаздач.
• Вылічыць кожны іх паказчык у паасобку.
• Вырабіць групоўку дадзеных па іх тыпах.
• Вырабіць фільтраванне і паглыблены аналіз атрыманых дадзеных.

У гэтым артыкуле мы разгледзім простыя спосабы іх стварэння. Вы даведаецеся, як зводная табліца Excel дапамагае ствараць і аналізаваць часовыя шэрагі, стварыць і разабраць падрабязней прагноз.

Ствараем патрэбны дакумент

Так як стварыць зводную табліцу ў Excel? Перш трэба скласці просты дакумент, у які мы ўносім дадзеныя для іх наступнага аналізу. На адзін слупок павінен прыходзіцца адзін параметр. Возьмем найпросты прыклад:

• Час продажу.
• Прададзены тавар.
• Кошт усяго збыту.

Такім чынам, паміж усімі параметрамі ў кожным канкрэтным слупку утворыцца сувязь: выкажам здагадку, што красоўкі былі прададзеныя ў 9 гадзін раніцы, прычым прыбытак склала n-рублёў.

фарматаванне табліцы

Прыгатаваўшы ўсе зыходныя звесткi, ставіце курсор у першую вочка першага ж слупка, адкрываеце ўкладку «Устаўка», пасля чаго клікаеце па кнопцы «Зводная табліца». Адразу з'явіцца дыялогавае акно, у якім вы можаце прарабіць наступныя аперацыі:

• Калі вы адразу ж націснеце на кнопку "ОК", то зводная табліца Excel адразу ж будзе выведзена на асобны ліст.
• Цалкам наладзіць вывядзенне.

У апошнім выпадку распрацоўшчыкі даюць нам магчымасць вызначыць дыяпазон вочак, у які будзе выводзіцца шуканая інфармацыя. Пасля гэтага карыстальнік павінен вызначыць, дзе канкрэтна будзе створана новая табліца: на ўжо існуючым ці ж на зноў створаным аркушы. Націснуўшы «ОК», вы тут жа ўбачыце перад сабой гатовую табліцу. На гэтым стварэнне зводных табліц у Excel практычна скончана.

У правай частцы ліста размешчаны тыя вобласці, з якімі вам трэба будзе працаваць. Поля можна перацягваць у асобныя вобласці, пасля чаго дадзеныя з іх будуць адлюстраваныя ў табліцы. Адпаведна, у левай частцы працоўнай вобласці будзе размешчана сама зводная табліца.

Заціснуўшы пры націснутай левай кнопцы мышы полі «Тавар», адпраўляем яго ў «Назва радкоў», а пункт «Сума усіх продажаў» такім жа чынам перапраўляюць у «Значэнні» (у левай частцы ліста). Вось так можна атрымаць суму продажаў за ўвесь аналізуемы перыяд.

Стварэнне і групоўка часовых шэрагаў

Каб прааналізаваць продажу па канкрэтных часовых адрэзках, неабходна ўставіць адпаведныя пункты ў саму табліцу. Для гэтага неабходна перайсці ў ліст «Дадзеныя» і пасля гэтага ўставіць тры новыя слупка адразу пасля даты. Вылучаем слупок з назвай тавару, пасля чаго ціснем на кнопку «Уставіць».

Вельмі важна, каб усе тыя, што ствараюцца слупкі знаходзіліся ўнутры ўжо існуючай табліцы з зыходнымі дадзенымі. У гэтым выпадку стварэнне зводных табліц у Excel зноўку не запатрабуецца. Вы проста будзеце дадаваць новыя поля з патрабаванымі вам дадзенымі.

выкарыстоўваюцца формулы

Для прыкладу зноў створаныя слупкі можна назваць «Год», «Месяц», «Месяцы-Гады». Каб атрымаць якія цікавяць нас дадзеныя, у кожны з іх прыйдзецца прапісаць асобную формулу для разлікаў:

• У «Гадавы» вставляем формулу выгляду: «= ГОД» (спасылаючыся пры гэтым на дату).
• Месяц трэба дапоўніць выразам: «= МЕСЯЦ» (таксама са спасылкай на дату).
• У трэці слупок вставляем формулу выгляду: «= счапіўшыся» (спасылаючыся пры гэтым на год і на месяц).

Адпаведна, атрымліваем тры слупка з усімі зыходнымі дадзенымі. Зараз неабходна перайсці ў меню «Зводная», пстрыкаем па любым свабоднаму месцы ў табліцы правай клавішай мышы, пасля чаго ў якое адкрылася кантэкстным меню выбіраем пункт «Абнавіць». Звярніце ўвагу! Ўважліва пішыце формулы ў зводных табліцах Excel, так як любая памылка прывядзе да недакладнасці прагнозу.

Як праводзіць аналіз продажаў па гадах?

Зноў зацісканы левай клавішай мышы пункт «Год», пасля чаго цягнем яго ў «Назва слупкоў», пасля чаго зводная табліца пачынае адлюстроўваць вынікі продажу канкрэтнага віду тавару па ўсіх гадам. А калі неабходна гэтак жа прааналізаваць месячны збыт? Сапраўды гэтак жа зацісканы ЛКМ «Месяц», перацягваючы яго пад гадавы слупок.

Каб атрымаць наглядную дынаміку месячных змяненняў продажаў па гадах, поле «Месяц» трэба перацягнуць прама ў гадавы слупок, раней ужо перамешчаны ў «Назва слупкоў». Выканаўшы гэтую аперацыю, вы ўбачыце табліцу ў наступным прадстаўленні:

• Сума продажаў канкрэтнага тавару за ўвесь год.
• Дынаміку продажаў кожнага з іх па гадах.

Прыбіраем дадзеныя з выдачы

Выкажам здагадку, што з выдачы нам трэба прыбраць дадзеныя па кастрычніка, так як у нас няма поўнай статыстыкі па ім. У зводнай табліцы шукаем «Фільтр справаздачы», пасля чаго перацягваць на яго «Год - месяц».

Над табліцай з'яўляецца фільтр, у якім трэба паставіць сцяжок насупраць пункта «Вылучыць некалькі элементаў». Пасля гэтага ў якое з'явілася спісе здымаем сцяжок з кастрычніка, пасля чаго націскаем на «ОК».

Такім чынам, можна дадаваць і прыбіраць з фільтра элементы, фармуючы сапраўды актуальныя і неабходныя вам аналізы. Праграма аўтаматычна пралічыць паказчыкі па тых месяцах, якія вас цікавяць, наглядна адлюстроўваючы дынаміку змены паказчыкаў.

разлічваем прагнозы

Паспрабуем спрагназаваць вынікі продажаў на некаторы час наперад. Для гэтага папярэдне варта адключыць агульныя вынікі, каб яны не ўнеслі недакладнасці ў ствараны прагноз.

Каб зрабіць гэта, ставім курсор у полі «Агульны вынік», пасля чаго клікаем па кнопцы «Выдаліць агульны вынік». Пасля гэтага выконваем аўтаматычнае абнаўленне зводнай табліцы Excel спосабам, апісаным намі вышэй (ціснем на правую клавішу мышы, выбіраем «Абнавіць»).

У выніку гэтыя дадзеныя знікаюць з нашай зводнай табліцы. Каб пабудаваць нагляднае адлюстраванне прагнозаў па продажах, ставім курсор на той год, ад якога плануем адштурхоўвацца, пасля чаго клікаем левай клавішай мышы па кнопцы «Графік Мадэль прагнозу".

Пасля гэтага распачынаецца прыгожы і інфарматыўны графік, на якім у нагляднай форме прадстаўлены прагназуемыя вынікі пакупніцкай актыўнасці па які цікавіць вас прагнозу. Гэта дазволіць зэканоміць вашы сілы і захаваць грошы, якія можна накіраваць на закупку сапраўды неабходных тавараў.

Выкарыстоўваем іншыя крыніцы дадзеных для фарміравання зводнай табліцы

Пара разгледзець больш складаныя ўрокі Excel. Зводная табліца можа быць створана на аснове дакументаў, якія былі адрэдагаваныя іншымі карыстальнікамі.

У гэтым нам дапаможа функцыя «Зліццё». Гэтая праграма дазваляе аўтаматычна аб'яднаць тэкст дакумента і неабходныя адрасы, імёны або іншую неабходную інфармацыю для стварэння персаналізаваных прагнозаў, што ў нашым выпадку і патрабуецца.

Функцыя зліцця ў пазнейшых версіях «Офіса» практычна не змянілася ў параўнанні з Office 2003. Тым не менш запыты да баз дадзеных для выбару крыніцы дадзеных могуць выконвацца з прымяненнем Microsoft Query. Каб атрымаць дадзеныя зводнай табліцы Excel, неабходна наяўнасць двух аб'ектаў:

• Асноўны дакумент, які змяшчае патрэбную вам інфармацыю, а таксама поля, якія паказваюць праграме Excel, якія дадзеныя варта ўстаўляць ў дакумент. Асноўная табліца з'яўляецца стандартным дакументам таблічнага працэсара, аднак неабходна ўказаць ў акне дыялогу Зліццё (Mail Merge Helper), што менавіта ён павінен выкарыстоўвацца як асноўны.
• Крыніца дадзеных, які змяшчае тую інфармацыю, якая неабходна для складання прагнозу. Можна стварыць новую табліцу тым спосабам, які мы паказвалі вышэй.

Калі адбываецца працэс стварэння зводных дакументаў, то праграма стварае адну копію для кожнай з запісаў, якія ўтрымліваюцца ў файле крыніцы дадзеных. Гэтыя файлы могуць быць паказаны ў выглядзе асобных раздзелаў новага дакумента або жа выкарыстаны ў якасці раздзелаў вашай зводнай табліцы.

Варта толькі клікнуць па патрэбным вам полі левай клавішай мышы, і вы зможаце паглядзець дадзеныя крыніцы замест імёнаў палёў, якія адлюстроўваюцца ў асноўным дакуменце. Поля можна ўстаўляць у любое месца дакумента.

выкарыстоўваюцца катэгорыі

Таксама тут можна выкарыстоўваць катэгорыю ASK, што значыць "запытаць", і FILLIN, што значыць «запоўніць», NEXT і NEXTIF, ELSE. Таксама можна вырабіць наладу параметраў тых пунктаў прагнозу, якія будуць адлюстраваныя ў гатовай табліцы. Знайсці неабходную запіс можна пры дапамозе адмысловага фільтра або жа шляхам простай сартавання дадзеных, пра што мы ўжо казалі вышэй.

Калі ўсё гатова для стварэння складовага дакумента, акно дыялогу Merge дае карыстальніку магчымасць выявіць і ліквідаваць асобныя памылкі да іх з'яўлення. Калі ўсе праверана, то можна смела ціснуць на кнопку «Аб'яднаць». Калі ж зліццё павінна вырабляцца праз электронную пошту, то выйдзе запыт на ўстаноўку паштовай праграмы (калі ў вас яна не ўсталяваная).

У выпадку калі ў працэсе зліцця ўзнікае нейкая памылка, то гэта гаворыць пра некарэктна паказаным поле ў чужым дакуменце, ці ж якія выкарыстоўваюцца дадзеныя прадстаўлены ў непадтрымоўванымі фармаце. Таму гэтую працэдуру варта яшчэ раз перагледзець.

Наогул, Excel (праца са зводным табліцамі у якім намі разглядаецца) дазваляе не толькі праводзіць параўнальна складаныя аналізы пакупніцкай здольнасці, але і ствараць на іх аснове прагнозы.