змест
зводныя табліцы з'яўляецца адным з самых магутных інструментаў у Excel. Яны дазваляюць аналізаваць і сумаваць розныя зводкі вялікіх аб'ёмаў даных усяго за некалькі пстрычак мышы. У гэтым артыкуле мы пазнаёмімся са зводнымі табліцамі, разбярэмся, што гэта такое, навучымся іх ствараць і наладжваць.
Гэты артыкул быў напісаны з выкарыстаннем Excel 2010. Канцэпцыя зводных табліц практычна не змянілася за гэтыя гады, але спосаб іх стварэння трохі адрозніваецца ў кожнай новай версіі Excel. Калі ў вас версія Excel не 2010, будзьце гатовыя, што скрыншоты ў гэтым артыкуле будуць адрознівацца ад таго, што вы бачыце на экране.
трохі гісторыі
У першыя дні праграмнага забеспячэння для электронных табліц, мяч правіла Lotus 1-2-3. Яго панаванне было настолькі поўным, што намаганні Microsoft па распрацоўцы ўласнага праграмнага забеспячэння (Excel) у якасці альтэрнатывы Lotus выглядалі пустой тратай часу. А цяпер хуткая перамотка ў 2010 год! Excel дамінуе ў электронных табліцах больш, чым код Lotus калі-небудзь у сваёй гісторыі, і колькасць людзей, якія ўсё яшчэ выкарыстоўваюць Lotus, блізкая да нуля. Як такое магло здарыцца? Што стала прычынай такога драматычнага павароту падзей?
Аналітыкі вылучаюць два асноўных фактару:
- Па-першае, Lotus вырашыў, што гэтая навамодная платформа GUI пад назвай Windows - проста мімалётная мода, якая не пратрымаецца доўга. Яны адмовіліся ствараць версію Lotus 1-2-3 для Windows (але толькі на некалькі гадоў), прадказваючы, што DOS-версія іх праграмнага забеспячэння будзе ўсім, што спатрэбіцца спажыўцам. Microsoft, натуральна, распрацавала Excel спецыяльна для Windows.
- Па-другое, Microsoft прадставіла ў Excel інструмент пад назвай PivotTables, які не быў даступны ў Lotus 1-2-3. Зводныя табліцы, эксклюзіўныя для Excel, апынуліся настолькі надзвычай карыснымі, што людзі вырашылі прытрымлівацца новага пакета праграмнага забеспячэння Excel, а не працягваць выкарыстоўваць Lotus 1-2-3, у якім іх не было.
Зводныя табліцы, разам з недаацэнкай поспеху Windows у цэлым, сыгралі марш смерці для Lotus 1-2-3 і адкрылі поспех Microsoft Excel.
Што такое зводныя табліцы?
Такім чынам, які найлепшы спосаб ахарактарызаваць, што такое зводныя табліцы?
Кажучы простымі словамі, зводныя табліцы - гэта зводкі некаторых даных, створаныя для палягчэння аналізу гэтых даных. У адрозненне ад вынікаў, створаных уручную, зводныя табліцы Excel інтэрактыўныя. Пасля стварэння вы можаце лёгка змяніць іх, калі яны не даюць той карціны, на якую вы спадзяваліся. З дапамогай усяго некалькіх пстрычак мышы вынікі можна перавярнуць так, каб загалоўкі слупкоў сталі загалоўкамі радкоў і наадварот. Вы можаце рабіць шмат рэчаў са зводнымі табліцамі. Замест таго, каб спрабаваць апісаць словамі ўсе магчымасці зводных табліц, прасцей прадэманстраваць гэта на практыцы…
Дадзеныя, якія вы аналізуеце з дапамогай зводных табліц, не могуць быць выпадковымі. Гэта павінны быць неапрацаваныя неапрацаваныя даныя, як нейкі спіс. Напрыклад, гэта можа быць спіс продажаў, зробленых кампаніяй за апошнія паўгода.
Паглядзіце на дадзеныя, паказаныя на малюнку ніжэй:
Звярніце ўвагу, што гэта не зыходныя зыходныя даныя, бо яны ўжо былі абагульнены. У ячэйцы B3 мы бачым 30000 30000 долараў, што, верагодна, з'яўляецца агульным вынікам, атрыманым Джэймсам Кукам у студзені. Дзе тады зыходныя дадзеныя? Адкуль узялася лічба ў XNUMX XNUMX долараў? Дзе арыгінальны спіс продажаў, з якога была атрымана гэтая сума за месяц? Відавочна, што хтосьці прарабіў вялікую працу па арганізацыі і сартаванні ўсіх даных аб продажах за апошнія паўгода і пераўтварэнні іх у табліцу агульных вынікаў, якую мы бачым. Як вы думаеце, колькі часу гэта заняло? Гадзіна? Дзесяць гадзін?
Справа ў тым, што прыведзеная вышэй табліца не з'яўляецца зводнай. Ён быў створаны ўручную з неапрацаваных даных, якія захоўваюцца ў іншым месцы, і на апрацоўку спатрэбілася не менш за пару гадзін. Менавіта такую зводную табліцу можна стварыць з дапамогай зводных табліц ўсяго за некалькі секунд. Давайце разбярэмся, як...
Калі мы вернемся да зыходнага спісу продажаў, ён будзе выглядаць прыкладна так:
Вы можаце быць здзіўлены, што з гэтага спісу здзелак з дапамогай зводных табліц і ўсяго за некалькі секунд мы можам стварыць штомесячную справаздачу аб продажах у Excel, якую мы прааналізавалі вышэй. Так, мы можам зрабіць гэта і многае іншае!
Як стварыць зводную табліцу?
Па-першае, пераканайцеся, што ў вас ёсць некаторыя зыходныя дадзеныя на аркушы Excel. Пералік фінансавых аперацый - найбольш тыповы з усіх, якія сустракаюцца. Фактычна, гэта можа быць спіс чаго заўгодна: кантактная інфармацыя супрацоўнікаў, калекцыя кампакт-дыскаў або даныя аб расходзе паліва ў вашай кампаніі.
Такім чынам, запускаем Excel ... і загружаем такі спіс ...
Пасля таго, як мы адкрылі гэты спіс у Excel, мы можам пачаць стварэнне зводнай табліцы.
Выберыце любую ячэйку з гэтага спісу:
Затым на таб устаўка (Уставіць) выбраць каманду Зводная табліца (Зводная табліца):
З'явіцца дыялогавае акно Стварыце зводную табліцу (Стварэнне зводнай табліцы) з двума пытаннямі для вас:
- Якія дадзеныя выкарыстоўваць для стварэння новай зводнай табліцы?
- Дзе размясціць зводную табліцу?
Паколькі на папярэднім этапе мы ўжо выбралі адну з вочак спісу, увесь спіс будзе аўтаматычна выбраны для стварэння зводнай табліцы. Звярніце ўвагу, што мы можам выбраць іншы дыяпазон, іншую табліцу і нават некаторую знешнюю крыніцу даных, напрыклад, табліцу базы дадзеных Access або MS-SQL. Акрамя таго, нам трэба выбраць, дзе размясціць новую зводную табліцу: на новым лісце або на адным з існуючых. У гэтым прыкладзе мы абярэм варыянт - Новы працоўны ліст (на новы аркуш):
Excel створыць новы ліст і размесціць на ім пустую зводную табліцу:
Як толькі мы пстрыкнем любую ячэйку ў зводнай табліцы, з'явіцца іншае дыялогавае акно: Спіс палёў зводнай табліцы (Палі зводнай табліцы).
Спіс палёў у верхняй частцы дыялогавага акна - гэта спіс усіх назваў з зыходнага спісу. Чатыры пустыя вобласці ўнізе экрана дазваляюць вам сказаць зводнай табліцы, як вы хочаце абагульніць дадзеныя. Пакуль гэтыя вобласці пустыя, у табліцы таксама нічога няма. Усё, што нам трэба зрабіць, гэта перацягнуць загалоўкі з верхняй вобласці ў пустыя вобласці ніжэй. Пры гэтым аўтаматычна генеруецца зводная табліца ў адпаведнасці з нашымі інструкцыямі. Калі мы дапусцім памылку, мы можам выдаліць загалоўкі з ніжняй вобласці або перацягнуць іншыя, каб замяніць іх.
Вобласць каштоўнасці (Значэнні), напэўна, самае важнае з чатырох. Загаловак, размешчаны ў гэтай вобласці, вызначае, якія даныя будуць абагульняцца (сума, сярэдняе, максімальнае, мінімальнае і г.д.). Гэта амаль заўсёды лікавыя значэнні. Выдатным кандыдатам на месца ў гэтай галіне з'яўляюцца дадзеныя пад загалоўкам сума (Кошт) нашай арыгінальнай табліцы. Перацягніце гэты загаловак у вобласць каштоўнасці (Значэнні):
Звярніце ўвагу, што назва сума цяпер пазначана галачкай і ў вобласці каштоўнасці (Значэнні) з'явіўся запіс Сума сумы (поле Amount Amount), якое паказвае, што слупок сума падсумаваў.
Калі мы паглядзім на саму зводную табліцу, мы ўбачым суму ўсіх значэнняў з слупка сума арыгінальны стол.
Такім чынам, наша першая зводная табліца створана! Зручна, але не асабліва ўражвае. Верагодна, мы хочам атрымаць больш інфармацыі аб нашых дадзеных, чым мы маем цяпер.
Давайце звернемся да зыходных дадзеных і паспрабуем вызначыць адзін або некалькі слупкоў, якія можна выкарыстоўваць для падзелу гэтай сумы. Напрыклад, мы можам сфарміраваць нашу зводную табліцу такім чынам, што агульная сума продажаў разлічваецца для кожнага прадаўца асобна. Тыя. у нашу зводную табліцу будуць дададзены радкі з імёнамі кожнага прадаўца ў кампаніі і іх агульным аб'ёмам продажаў. Каб дасягнуць гэтага выніку, проста перацягніце назву прадавец (гандлёвы прадстаўнік) у вобл Этыкеткі радкоў (Радкі):
Становіцца цікавей! Наша зводная табліца пачынае набываць форму...
Бачыце перавагі? У пару клікаў мы стварылі табліцу, стварэнне якой уручную заняло б вельмі шмат часу.
Што яшчэ мы можам зрабіць? Што ж, у пэўным сэнсе наша зводная табліца гатовая. Мы стварылі карысную зводку зыходных даных. Важная інфармацыя ўжо атрымана! У астатняй частцы гэтага артыкула мы разгледзім некаторыя спосабы стварэння больш складаных зводных табліц, а таксама даведаемся, як іх наладжваць.
Налада зводнай табліцы
Па-першае, мы можам стварыць двухмерную зводную табліцу. Давайце зробім гэта з дапамогай загалоўка слупка Спосаб аплаты (Спосаб аплаты). Проста перацягніце назву Спосаб аплаты да вобласці Этыкеткі слупкоў (Слупкі):
Атрымліваем вынік:
Выглядае вельмі крута!
Зараз зробім трохмерную табліцу. Як бы выглядаў такі стол? Пабачым…
Перацягнуць загаловак пакет (Комплекс) да пл фільтры справаздач (Фільтры):
Звярніце ўвагу, дзе ён…
Гэта дае нам магчымасць адфільтраваць справаздачу па прынцыпе «Які комплекс адпачынку быў аплачаны». Напрыклад, мы можам убачыць разбіўку па прадаўцам і па спосабах аплаты для ўсіх комплексаў, або ў пару клікаў мышкай змяніць выгляд зводнай табліцы і паказаць такую ж разбіўку толькі для тых, хто заказваў комплекс. Шукальнікі сонца.
Такім чынам, калі вы гэта правільна разумееце, то нашу зводную табліцу можна назваць трохмернай. Давайце працягнем наладжванне…
Калі раптам апынецца, што ў зводнай табліцы павінна адлюстроўвацца толькі аплата чэкам і крэдытнай картай (гэта значыць безнаяўны разлік), то можна адключыць адлюстраванне назвы Cash (Наяўныя). Для гэтага побач Этыкеткі слупкоў націсніце стрэлку ўніз і зніміце сцяжок у выпадальным меню Cash:
Давайце паглядзім, як цяпер выглядае наша зводная табліца. Як бачыце, калонка Cash знік з яе.
Фарматаванне зводных табліц у Excel
Зводныя табліцы, відавочна, з'яўляюцца вельмі магутным інструментам, але пакуль вынікі выглядаюць трохі проста і сумна. Напрыклад, лічбы, якія мы складаем, не выглядаюць як сумы ў доларах - гэта проста лічбы. Давайце выправім гэта.
Вельмі хочацца зрабіць тое, да чаго вы прывыклі ў такой сітуацыі, проста вылучыць усю табліцу (або ўвесь ліст) і з дапамогай стандартных кнопак фарматавання лікаў на панэлі інструментаў задаць патрэбны фармат. Праблема гэтага падыходу ў тым, што калі вы калі-небудзь зменіце структуру зводнай табліцы ў будучыні (што адбудзецца з верагоднасцю 99%), фарматаванне будзе страчана. Нам патрэбны спосаб зрабіць гэта (амаль) пастаянным.
Спачатку давайце знойдзем запіс Сума сумы in каштоўнасці (Значэнні) і націсніце на яго. У якое з'явілася меню абярыце пункт Налады поля значэнняў (Параметры палёў значэнняў):
З'явіцца дыялогавае акно Налады поля значэнняў (Параметры палёў значэнняў).
націсніце кнопку Фармат ліку (Фармат ліку), адкрыецца дыялогавае акно. Фарматаваць вочкі (фармат ячэйкі):
Са спісу катэгорыя (Фарматы лікаў) выбер Бухгалтарскі ўлік (Фінансавы) і ўсталюйце колькасць знакаў пасля коскі роўным нулю. Цяпер націсніце некалькі разоў OKкаб вярнуцца да нашай зводнай табліцы.
Як бачыце, лічбы адфарматаваны як сумы ў доларах.
Пакуль мы займаемся фарматаваннем, давайце наладзім фармат для ўсёй зводнай табліцы. Ёсць некалькі спосабаў зрабіць гэта. Мы выкарыстоўваем тое, што прасцей…
націсніце Інструменты зводнай табліцы: дызайн (Праца са зводнымі табліцамі: Канструктар):
Далей разгарніце меню, націснуўшы на стрэлку ў правым ніжнім куце раздзела Стылі зводнай табліцы (Стылі зводнай табліцы), каб убачыць шырокую калекцыю ўбудаваных стыляў:
Выберыце любы прыдатны стыль і паглядзіце вынік у вашай зводнай табліцы:
Іншыя налады зводнай табліцы ў Excel
Часам патрабуецца фільтраваць дадзеныя па датах. Напрыклад, у нашым спісе здзелак шмат-шмат дат. Excel прадастаўляе інструмент для групавання даных па днях, месяцах, гадах і гэтак далей. Давайце паглядзім, як гэта робіцца.
Спачатку выдаліце запіс. Спосаб аплаты з вобласці Этыкеткі слупкоў (Стоўбцы). Для гэтага перацягніце яго назад у спіс назваў, а на яго месца перамесціце назву Дата браніравання (дата браніравання):
Як бачыце, гэта часова зрабіла нашу зводную табліцу бескарыснай. Excel стварыў асобны слупок для кожнай даты здзелкі. У выніку атрымаўся вельмі шырокі стол!
Каб выправіць гэта, пстрыкніце правай кнопкай мышы любую дату і абярыце з кантэкстнага меню GROUP (Група):
З'явіцца дыялогавае акно групоўкі. Мы выбіраем Месяцаў (Месяцы) і націсніце OK:
Вуаля! Гэтая табліца значна больш карысная:
Дарэчы, гэтая табліца практычна ідэнтычная той, што была паказана ў пачатку артыкула, дзе вынікі продажаў складаліся ўручную.
Ёсць яшчэ адзін вельмі важны момант, які трэба ведаць! Вы можаце стварыць не адзін, а некалькі ўзроўняў загалоўкаў радкоў (або слупкоў):
... і гэта будзе выглядаць так ...
Тое ж самае можна зрабіць з загалоўкамі слупкоў (ці нават фільтрамі).
Давайце вернемся да зыходнага выгляду табліцы і паглядзім, як паказаць сярэднія значэнні замест сум.
Каб пачаць, націсніце Сума сумы і ў якое з'явілася меню абярыце Налады поля значэнняў (Параметры палёў значэнняў):
спіс Сумаваць поле значэння па (Аперацыя) у дыялогавым акне Налады поля значэнняў (Параметры поля значэнняў) абярыце сярэдні (Сярэдняе):
У той жа час, пакуль мы тут, давайце зменім CustomName (Імя на заказ) с Сярэдняя сума (поле Сума Сума) да чагосьці карацейшага. Увядзіце ў гэта поле нешта накшталт у сярэднім:
прэс OK і паглядзець, што адбываецца. Звярніце ўвагу, што ўсе значэнні змяніліся з агульных на сярэднія, а загаловак табліцы (у верхняй левай ячэйцы) змяніўся на у сярэднім:
Пры жаданні вы можаце неадкладна атрымаць суму, сярэдняе значэнне і колькасць (продажаў), змешчаных у адну зводную табліцу.
Вось крок за крокам, як гэта зрабіць, пачынаючы з пустой зводнай табліцы:
- Перацягнуць загаловак прадавец (гандлёвы прадстаўнік) у вобл Этыкеткі слупкоў (Стоўбцы).
- Тры разы перацягніце назву сума (Кошт) у вобласці каштоўнасці (Значэнні).
- Для першага поля сума змяніць назву на Цэна (Сума), і фармат ліку ў гэтым полі Бухгалтарскі ўлік (Фінансавы). Колькасць знакаў пасля коскі роўная нулю.
- Другое поле сума імя Сярэдніe, усталяваць аперацыю для яго сярэдні (Сярэдняе) і фармат ліку ў гэтым полі таксама зменіцца на Бухгалтарскі ўлік (Фінансавы) з нулявым знакам пасля коскі.
- Для трэцяга поля сума ўсталяваць назву Лічыць і аперацыя для яго - Лічыць (Колькасць)
- У Этыкеткі слупкоў Поле (Слупкі) створана аўтаматычна Σ Каштоўнасці (Σ Значэнні) – перацягнуць у вобласць Этыкеткі радкоў (радкі)
Вось што мы атрымаем:
Агульная сума, сярэдняе значэнне і колькасць продажаў - усё ў адной зводнай табліцы!
заключэнне
Зводныя табліцы ў Microsoft Excel ўтрымліваюць мноства функцый і налад. У такім невялікім артыкуле яны нават не могуць ахапіць усіх. Каб цалкам апісаць усе магчымасці зводных табліц, спатрэбіцца невялікая кніга або вялікі сайт. Смелыя і дапытлівыя чытачы могуць працягнуць вывучэнне зводных табліц. Для гэтага проста клікніце правай кнопкай мышы практычна на любым элеменце зводнай табліцы і паглядзіце, якія функцыі і налады адкрываюцца. На стужцы вы знойдзеце дзве ўкладкі: Інструменты зводнай табліцы: параметры (аналіз) і дызайн (Канструктар). Не бойцеся памыліцца, вы заўсёды можаце выдаліць зводную табліцу і пачаць спачатку. У вас ёсць магчымасць, якой ніколі не было ў карыстальнікаў DOS і Lotus 1-2-3.