У гэтай частцы падручніка падрабязна апісана, як стварыць зводную табліцу ў Excel. Гэты артыкул быў напісаны для Excel 2007 (а таксама больш позніх версій). Інструкцыі для больш ранніх версій Excel можна знайсці ў асобным артыкуле: Як стварыць зводную табліцу ў Excel 2003?
У якасці прыкладу разгледзім наступную табліцу, якая змяшчае дадзеныя аб продажах кампаніі за першы квартал 2016 года:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | дата | Спасылка на рахунак-фактуру | сума | Гандлёвы прадстаўнік | Рэгіён |
| 2 | 01/01/2016 | 2016 - 0001 | $ 819 | Барнс | Да поўначы |
| 3 | 01/01/2016 | 2016 - 0002 | $ 456 | карычневы | Поўдзень |
| 4 | 01/01/2016 | 2016 - 0003 | $ 538 | Джонс | Поўдзень |
| 5 | 01/01/2016 | 2016 - 0004 | $ 1,009 | Барнс | Да поўначы |
| 6 | 01/02/2016 | 2016 - 0005 | $ 486 | Джонс | Поўдзень |
| 7 | 01/02/2016 | 2016 - 0006 | $ 948 | Каваль | Да поўначы |
| 8 | 01/02/2016 | 2016 - 0007 | $ 740 | Барнс | Да поўначы |
| 9 | 01/03/2016 | 2016 - 0008 | $ 543 | Каваль | Да поўначы |
| 10 | 01/03/2016 | 2016 - 0009 | $ 820 | карычневы | Поўдзень |
| 11 | ... | ... | ... | ... | ... |
Для пачатку давайце створым вельмі простую зводную табліцу, якая будзе паказваць агульны аб'ём продажаў кожнага з прадаўцоў у адпаведнасці з табліцай вышэй. Для гэтага зрабіце наступнае:
- Выберыце любую ячэйку з дыяпазону даных або ўвесь дыяпазон для выкарыстання ў зводнай табліцы.УВАГА: Калі вы вылучыце адну ячэйку з дыяпазону даных, Excel аўтаматычна выявіць і выбярэ ўвесь дыяпазон даных для зводнай табліцы. Каб Excel правільна выбраў дыяпазон, павінны быць выкананы наступныя ўмовы:
- Кожны слупок у дыяпазоне дадзеных павінен мець сваё ўласнае унікальнае імя;
- Дадзеныя не павінны ўтрымліваць пустых радкоў.
- Націсканне кнопкі зводная табліца (Зводная табліца) у раздзеле Табліцы (Табліцы) ўкладка Уставіць (Уставіць) Стужкі меню Excel.
- На экране з'явіцца дыялогавае акно. Стварыце зводную табліцу (Стварыць зводную табліцу), як паказана на малюнку ніжэй.
Пераканайцеся, што выбраны дыяпазон супадае з дыяпазонам ячэек, якія трэба выкарыстоўваць для стварэння зводнай табліцы. Тут вы таксама можаце паказаць, куды трэба ўставіць створаную зводную табліцу. Вы можаце выбраць існуючы аркуш, каб уставіць на яго зводную табліцу, або варыянт – На новы аркуш (Новы ліст). націсніце OK. - З'явіцца пустая зводная табліца, а таксама панэль Палі зводнай табліцы (Спіс палёў зводнай табліцы) з некалькімі палямі даных. Звярніце ўвагу, што гэта загалоўкі з арыгінальнай табліцы дадзеных.
- У панэлях Палі зводнай табліцы (Спіс палёў зводнай табліцы):
- перацягнуць Гандлёвы прадстаўнік да вобласці Радкі (Пазнакі радкоў);
- перацягнуць сума в Каштоўнасці (Каштоўнасці);
- Правяраем: in Каштоўнасці (Значэнні) павінна быць значэннем Поле сумы Сума (Сума сумы), а не Сума па полі Сума (Падлік сумы).
У гэтым прыкладзе калонка сума змяшчае лікавыя значэнні, таму плошча Σ Каштоўнасці (Σ Значэнні) будзе выбрана па змаўчанні Поле сумы Сума (Сума сумы). Калі ў слупок сума будзе ўтрымліваць нелікавыя або пустыя значэнні, тады можна выбраць зводную табліцу па змаўчанні Сума па полі Сума (Падлік сумы). Калі гэта адбудзецца, вы можаце змяніць колькасць наступным чынам:
- У Σ Каштоўнасці (Σ Значэнні) націсніце Сума па полі Сума (Падлік сумы) і абярыце опцыю Параметры палёў значэнняў (Налады поля значэнняў);
- На ўкладцы Дадаткова аперацыя (Абагульніце значэнні па) абярыце аперацыю Сума (Сума);
- Клікніце тут OK.
Пераканайцеся, што выбраны дыяпазон супадае з дыяпазонам ячэек, якія трэба выкарыстоўваць для стварэння зводнай табліцы. Тут вы таксама можаце паказаць, куды трэба ўставіць створаную зводную табліцу. Вы можаце выбраць існуючы аркуш, каб уставіць на яго зводную табліцу, або варыянт – На новы аркуш (Новы ліст). націсніце OK.
Зводная табліца будзе запоўнена агульнымі аб'ёмамі продажаў для кожнага прадаўца, як паказана на малюнку вышэй.
Калі вы хочаце адлюстраваць аб'ём продажаў у грашовых адзінках, вы павінны адфарматаваць вочкі, якія змяшчаюць гэтыя значэнні. Самы просты спосаб зрабіць гэта - вылучыць вочкі, фармат якіх вы хочаце наладзіць, і выбраць фармат Грашовыя (Валюта) раздзел Нумар Укладка (Нумар). Галоўная (Галоўная) Стужкі меню Excel (як паказана ніжэй).
У выніку зводная табліца будзе выглядаць так:
- зводная табліца перад наладай фармату ліку
- зводная табліца пасля ўстаноўкі фармату валюты
Звярніце ўвагу, што фармат валюты па змаўчанні залежыць ад налад сістэмы.
Рэкамендуемыя зводныя табліцы ў апошніх версіях Excel
У апошніх версіях Excel (Excel 2013 або больш позняй версіі) на Уставіць Прысутнічае кнопка (Уставіць). Рэкамендуемыя зводныя табліцы (Рэкамендуемыя зводныя табліцы). На аснове выбраных зыходных даных гэты інструмент прапануе магчымыя фарматы зводных табліц. Прыклады можна паглядзець на сайце Microsoft Office.