Примери за документи с Excel таблици

Сводната таблица е може би най-полезният инструмент в Excel. С негова помощ има широки възможности за анализ на големи количества данни и бързи изчисления.

Какво представляват обобщените таблици в Excel? Осеви таблици в Excel за манекени

Обобщените таблици са инструмент на Excel за обобщаване и анализ на големи количества данни.

Да приемем, че имаме таблица от 1000 реда продажби по клиенти за годината:

Таблицата показва:

Дати на поръчки;
Регионът, в който се намира клиентът;
Тип клиент;
Клиент;
Брой продажби;
Приходи;
Печалба.

Сега, нека си представим, че нашият мениджър е задал задачи за изчисляване:

ТОП пет клиенти по приходи;

Можете да използвате различни функции и формули, за да намерите отговора на тези въпроси. Но какво, ако за тези данни има не три, а тридесет задачи? Всеки път трябва да сменяте формули и функции и да коригирате за всеки тип изчисление.

Това е точно случаят, в който инструментът PivotTable ще се превърне в незаменим помощник за вас. С негова помощ можете да отговорите на всеки въпрос за секунди, според данните от таблицата.

Надявам се, че сте разбрали в горния пример колко полезна и необходима е обобщена таблица. Нека да разберем как да ги използваме.

Как да направите обобщена таблица в Excel

За да създадете обобщена таблица в Excel, изпълнете следните стъпки:

Изберете която и да е клетка в таблицата с данните, въз основа на които искате да направите обобщена таблица;
Кликнете върху раздела „Вмъкване“ \u003d\u003e „Осева таблица“:

В изскачащия диалогов прозорец системата автоматично ще определи границите на данните, въз основа на които можете да създадете обобщена таблица. Този метод работи по подразбиране в повечето случаи. Препоръчвам ви всеки път, когато създавате обобщена таблица, да се уверите, че системата е дефинирала правилно параметрите за нейното създаване:
- Таблица или диапазон: Системата автоматично открива граници на данни, за да създаде обобщена таблица. Те ще бъдат правилни, при условие че в заглавията и редовете в таблицата няма интервали. Можете да регулирате диапазона от данни, ако е необходимо.
Системата създава таблица по подразбиране в нов раздел на файла Excel. Ако искате да го създадете на определено място на определен лист, тогава можете да посочите границите, които да създадете в колоната „На съществуващ лист“.

Щракнете върху „OK“.

След като щракнете върху бутона „OK“, ще бъде създадена обобщената таблица.

След като се създаде обобщената таблица, няма да видите никакви данни на листа. Всичко, което ще бъде на разположение, е името на обобщената таблица и менюто за избор на данни за показване.

Сега, преди да започнем да анализираме данните, предлагам да разберем какво означава всяко поле и област на обобщената таблица.

Региони на обобщена таблица в Excel

За да използвате ефективно обобщените таблици, е важно да знаете задълбочено как работят и от какво се състоят.

Научете повече за областите по-долу:

Кеш на обобщена таблица
Област на ценностите
Струнни области
Площ на колони
Област на филтрите

Какво е кеш на обобщена таблица

Когато създавате обобщена таблица, Excel създава кеш (междинен буфер за бърз достъп, съдържащ информацията, която е най-вероятно да бъде поискана) на данните, от които ще бъде изградена таблицата.

Когато извършвате изчисления на създадената таблица, Excel не се позовава на оригиналните данни всеки път, а използва информация от кеша. Тази функция значително намалява количеството системни ресурси, изразходвани за обработка и изчисляване на данни.

Важно е да запомните, че кешът с данни увеличава размера на файла на Excel.

Област на ценностите

Областта Стойности съдържа всички стойности на таблицата и може да бъде показана като основен компонент на обобщената таблица. Представете си, че искаме да покажем продажбите на региони по месеци (от примера в началото на статията) с помощта на обобщена таблица. Засенчени стойности жълт на изображението по-долу и са стойностите, които посочваме в обобщена таблица в областта Ценности.

В горния пример създадохме обобщена таблица, която показва данни за продажбите по региони, разпределени по месеци.

Струнни области

Заглавките на таблиците вляво от стойностите са редове. В нашия пример това са имената на регионите. На екранната снимка по-долу линиите са маркирани в червено:

Площ на колони

Заглавията в горната част на стойностите на таблицата се наричат \u200b\u200b„Колони“.

В примера по-долу полетата „Колони“ са маркирани в червено, в нашия случай това са стойностите на месеците.

Област на филтрите

Областта "Филтри" не е задължителна и ви позволява да зададете нивото на детайлност за данните на обобщената таблица. Например можем да посочим данните „Тип клиент“ - „Хранителен магазин“ като филтър и Excel ще покаже данни в таблицата, отнасящи се само за хранителни магазини.

Осеви таблици в Excel. Примери за

В примерите по-долу ще разгледаме как да използваме обобщени таблици, за да отговорим на трите въпроса в началото на тази статия:

Какъв е обемът на приходите за Северния регион през 2017 г.?
ТОП пет клиенти по приходи;
Какво е мястото на IP клиента Ludnikov в района на Vostok по отношение на приходите?

Преди да анализирате данните, е важно да решите как трябва да изглеждат данните на таблицата (какви данни да се маркират в колони, редове, стойности, филтри). Например, ако трябва да покажем данни за продажбите на клиенти по региони, тогава трябва да поставим имената на регионите в редове, месеци в колони, стойности на продажбите в полето „Стойности“. След като имате представа как виждате крайната обобщена таблица, започнете да я създавате.

Прозорецът "Полета на обобщената таблица" съдържа области и полета със стойности за поставяне в обобщената таблица:

Полетата се създават въз основа на оригиналните данни, използвани за обобщената таблица. Разделът Области е мястото, където поставяте полетата и според това в коя област ще бъде поставено полето, вашите данни се актуализират в обобщената таблица.

Преместването на полета от област в област е удобен интерфейс, в който при преместване данните в обобщената таблица се актуализират автоматично.

Сега, нека се опитаме да отговорим на въпросите на мениджъра от началото на тази статия, като използваме примерите по-долу.

Пример 1. Колко приходи има северният регион?

За да се изчисли обемът на продажбите на Северния регион, предлагам да поставите данните за продажбите на всички региони в осевата таблица. За това се нуждаем от:

създайте обобщена таблица и преместете полето "Регион" в областта "Редове";
поставете полето „Приходи“ в областта „Стойности“

Ще получим отговора: продажбите на Северния регион са 1 233 006 966 ₽:

Пример 2. ТОП пет клиенти по продажби

в обобщената таблица преместете полето „Клиент“ в областта „Редове“;
поставете полето „Приходи“ в областта „Стойности“;
задайте формат на финансов номер на пивотни клетки със стойности.

Ще имаме следната обобщена таблица:

По подразбиране Excel сортира данните в таблицата по азбучен ред. За да сортирате данните по обем на продажбите, изпълнете следните стъпки:

отидете в менюто "Сортиране" \u003d\u003e "Сортиране по низходящ":

В резултат на това ще получим сортиран списък с клиенти по приходи.

Пример 3. Какво е мястото по отношение на приходите на клиента Ludnikov IE в региона Vostok?

За да се изчисли мястото по обема на приходите на клиента Ludnikov IP в региона Vostok, предлагам да създам обобщена таблица, която да показва данни за приходите по региони и клиенти в този регион.

За това:

поставете полето на обобщената таблица „Регион” в областта „Редове”;
поставете полето „Клиент“ в областта „Струни“ под полето „Регион“;
задайте формата на финансовия номер на клетките със стойности.

Веднага щом поставите полетата „Регион“ и „Клиент“ едно под друго в областта „Редове“, системата ще разбере как искате да покажете данните и ще предложи подходящата опция.

полето „Приходи“ ще бъде поставено в областта „Стойности“.

В резултат на това получихме обобщена таблица, която отразява данните за приходите на клиентите във всеки регион.

За да сортирате данните, изпълнете следните стъпки:

щракнете с десния бутон на мишката върху който и да е от редовете с данни за приходите в обобщената таблица;
отидете в менюто "Сортиране" \u003d\u003e "Сортиране по низходящ":

В получената таблица можем да определим какво място заема клиентът на Ludnikov IP сред всички клиенти на региона Vostok.

Има няколко възможности за решаване на този проблем. Можете да преместите полето "Регион" в областта "Филтри" и да поставите данни за продажбите на клиенти в редовете, като по този начин отразявате данните за приходите само за клиенти в региона на Изтока.

Ако никога преди не сте използвали електронна таблица за създаване на документи, препоръчваме да прочетете нашето ръководство на Excel за манекени.

След това можете да създадете първата си електронна таблица с таблици, графики, математически формули и форматиране.

Подробна информация за основните функции и възможности процесор за маса MS Excel. Описание на основните елементи на документа и инструкции за работа с тях в нашия материал.

Работа с клетки. Подплата и форматиране

Преди да продължите с конкретни действия, трябва да разберете основен елемент всеки документ в Excel. Файлът на Excel се състои от един или повече листа, разделени на малки клетки.

Клетката е основен компонент на всеки отчет, електронна таблица или графика на Excel. Всяка клетка съдържа един блок информация. Това може да бъде число, дата, валута, мерна единица или друг формат на данните.

За да попълните клетка, просто кликнете върху нея с показалеца и въведете необходимата информация. За да редактирате предварително попълнена клетка, щракнете двукратно върху нея.

Фигура: 1 - пример за запълване на клетки

Всяка клетка на листа има свой уникален адрес. По този начин можете да извършвате изчисления или други операции с него. Когато кликнете върху клетка, в горната част на прозореца ще се появи поле с адреса, името и формулата (ако клетката участва в някакви изчисления).

Нека да изберем клетката "Споделяне на акции". Адресът му за местоположение е A3. Тази информация е посочена в панела за свойства, който се отваря. Можем да видим и съдържанието. Тази клетка няма формули, така че те не са показани.

Повече свойства и функции на клетки, които могат да се използват по отношение на нея, са налични в контекстното меню. Кликнете върху клетката с десния бутон на манипулатора. Ще се отвори меню, с което можете да форматирате клетката, да анализирате съдържанието, да присвоите различна стойност и други действия.

Фигура: 2 - контекстно меню на клетка и нейните основни свойства

Сортиране на данни

Често потребителите са изправени пред задачата да сортират данни на лист в Excel. Тази функция ви помага бързо да избирате и преглеждате само данните, които искате от цялата таблица.

Пред вас е вече попълнена таблица (ще разберем как да я създадем по-късно в статията). Представете си, че искате да сортирате данните за януари във възходящ ред. Как бихте го направили? Банално пренабиране на маса е допълнителна работа, освен това, ако е обемна, никой няма да я направи.

Excel има специална функция за сортиране. От потребителя се изисква само:

Изберете таблица или блок информация;
Отворете раздела "Данни";
Щракнете върху иконата "Сортиране";

Фигура: 3 - раздел "Данни"

В прозореца, който се отваря, изберете колоната на таблицата, над която ще извършим действия (януари).
След това типът на сортиране (ние групираме по стойност) и накрая, редът е възходящ.
Потвърдете действието, като кликнете върху "OK".

Фигура: 4 - задаване на параметри за сортиране

Данните ще бъдат автоматично сортирани:

Фигура: 5 - резултатът от сортирането на цифрите в колоната "Януари"

По същия начин можете да сортирате по цвят, шрифт и други параметри.

Математически изчисления

Основното предимство на Excel е възможността за автоматично извършване на изчисления в процеса на попълване на таблицата. Например имаме две клетки със стойности 2 и 17. Как можем да въведем резултата им в третата клетка, без да правим изчисленията сами?

За да направите това, трябва да кликнете върху третата клетка, в която ще бъде въведен крайният резултат от изчисленията. След това кликнете върху иконата на функцията f (x), както е показано на фигурата по-долу. В отворения прозорец изберете действието, което искате да приложите. SUM е сумата, AVERAGE е средната стойност и т.н. Пълен списък функции и имената им в редактора на Excel могат да бъдат намерени на официалния уебсайт на Microsoft.

Трябва да намерим сумата от две клетки, така че кликваме върху „SUM“.

Фигура: 6 - избор на функцията "SUM"

Прозорецът на аргументите на функцията има две полета: "Номер 1" и "Номер 2". Изберете първото поле и кликнете върху клетката с числото "2". Адресът му ще бъде записан в аргументарния низ. Кликнете върху "Номер 2" и кликнете върху клетката с номер "17". След това потвърдете действието и затворете прозореца. Ако трябва да извършите математика с три или повече полета, просто продължете да въвеждате стойностите на аргументите в Число 3, Число 4 и т.н.

Ако в бъдеще стойността на сумираните клетки се промени, тяхната сума ще се актуализира автоматично.

Фигура: 7 - резултатът от изчисленията

Създаване на таблици

Всички данни могат да се съхраняват в таблици на Excel. С помощта на функцията за бърза настройка и форматиране е много лесно да се организира лична система за контрол на бюджета, списък с разходи, цифрови данни за отчитане и т.н. в редактора.

Таблиците в Excel имат предимство пред подобната опция в Word и други офис програми. Тук имате възможност да създадете таблица от всякакво измерение. Данните се попълват лесно. Има функционален панел за редактиране на съдържание. В допълнение, готова маса може да се интегрира в docx файл с помощта на обичайната функция copy-paste.

За да създадете таблица, следвайте инструкциите:

Щракнете върху раздела Вмъкване. От лявата страна на екрана с опции изберете Таблица. Ако трябва да обобщите някакви данни, изберете елемента "Осева таблица";
С помощта на мишката изберете място на листа, което ще бъде отредено за таблицата. А също така можете да въведете местоположение на данни в прозореца за създаване на елемент;
Щракнете върху OK, за да потвърдите действието.

Фигура: 8 - създаване на стандартна таблица

Да форматирате външен вид от получената плоча, отворете съдържанието на конструктора и в полето "Стил" кликнете върху шаблона, който харесвате. Ако желаете, можете да създадете свой собствен облик с различна цветова схема и избор на клетки.

Фигура: 9 - форматиране на таблица

Резултатът от попълването на таблицата с данни:

Фигура: 10 - попълнена маса

За всяка клетка в таблицата можете също да персонализирате типа данни, форматирането и режима на показване на информация. Прозорецът на дизайнера съдържа всички необходими опции за по-нататъшно конфигуриране на плочата, според вашите изисквания.

Добавяне на графики / диаграми

За изграждане на диаграма или графика се изисква готова плоча, тъй като графичните данни ще се базират точно на информация, взета от отделни линии или клетки.

За да създадете диаграма / графика, трябва:

Изберете таблицата напълно. Ако трябва да създадете графичен елемент само за показване на данни определени клетки, изберете само тях;
Отворете раздела за вмъкване;
В полето с препоръчителни диаграми изберете иконата, която според вас най-добре визуално описва табличната информация. В нашия случай това е триизмерна кръгова диаграма. Преместете показалеца към иконата и изберете външния вид на елемента;

По същия начин можете да създавате графики за разпръскване, редови диаграми и зависимости от елементи на таблица. Всички получени графични елементи също могат да бъдат добавени към текстови документи Word.

Има много други функции в редактора на електронни таблици на Excel, но техниките, описани в тази статия, ще бъдат достатъчни за първоначална работа. В процеса на създаване на документ много потребители независимо овладяват по-разширени опции. Това се дължи на лесен за употреба и интуитивен интерфейс. най-новите версии програми.

Тематични видеоклипове:

Pivot Table в Excel е мощен инструмент за анализ на данни, който ще ви помогне бързо:

Подгответе данни за отчети;
Изчисляване на различни показатели;
Групови данни;
Филтрирайте и анализирайте показателите, които представляват интерес.

И също така ще ви спести много време.

В тази статия ще научите:

Как се прави обобщена таблица;
Как да използвам обобщена таблица групови времеви редове и оценявайте данните в динамика по години, тримесечия, месеци, дни ...
Как да изчислим прогноза, използвайки обобщена таблица и Forecast4AC PRO;

Първо, нека се научим как да правим обобщени таблици.

За да направим обобщена таблица, трябва да изградим данните под формата на обикновена таблица. Всяка колона трябва да има 1 разбран параметър. Например имаме 3 колони:

Продукт
Продажби в рубли

И в всеки ред Третите параметри са свързани, т.е. например 02/01/2010, Продукт 1 е продаден за 422 656 рубли.

След като подготвите данните за обобщената таблица, задайте курсора към първата колона до първата клетка на обикновена таблица, след това отидете в менюто "Вмъкване" и натиснете бутона "Pivot table"

Ще се появи диалогов прозорец, в който:

можеш веднага натиснете бутона "OK", и обобщената таблица ще се покаже на отделен лист.
или можете да конфигурирате параметрите на изхода за данни на обобщената таблица:

Обхват с данни, които да се показват в обобщената таблица;
Къде да се покаже обобщената таблица (към нов лист или към съществуващ (ако изберете към съществуващ, ще трябва да посочите клетката, в която искате да поставите обобщената таблица)).

Щракнете върху "OK", обобщената таблица е готова и се показва в нов лист. Нека наречем листа Pivot.

От дясната страна на листа ще видите полета и области, с които можете да работите. Можете да плъзнете полетата към областите и те ще бъдат показани в обобщената таблица на листа.
От лявата страна на листа е обобщена таблица.

Сега задръжте полето „Продукт“ с левия бутон на мишката - плъзнете го до „Имена на линии“, „Продажби в рубли“. - в „Стойности“ в обобщената таблица. По този начин получихме общите продажби по стоки за целия период:

Групиране и филтриране на времеви редове в обобщена таблица

Сега, ако искаме да анализираме и сравним продажбите на продукти по години, тримесечия, месеци, дни, тогава имаме нужда добавете съответни полета към обобщена таблица.

За целта отидете на лист „Данни“ и след датата вмъкнете 3 празни колони. Изберете колоната „Продукт“ и кликнете върху „Вмъкване“.

Важнотака че новодобавените колони да са в обхвата на съществуваща таблица с данни, тогава няма да е необходимо да повтаряме въртенето, за да добавяме нови полета, ще бъде достатъчно да го актуализираме.

Вмъкнатите колони се наричат \u200b\u200b„Година“, „Месец“, „Година-месец“.

Сега във всяка от тези колони добавете подходящата формула, за да получите времевия параметър, който ви интересува:

Добавете формулата към колоната "Година" \u003d ГОДИНА (с препратка към датата);
Добавете формулата към колоната „Месец“ \u003d МЕСЕЦ (с препратка към датата);
Добавете формулата към колоната „Година - месец“ \u003d CONCATENATE (връзка към година; ""; връзка към месец).

Получаваме 3 колони с година, месец и година и месец:

Сега отидете на листа "Обобщение", поставете курсора върху обобщената таблица, щракнете с десния бутон върху менюто и натиснете бутона "Актуализиране"... След актуализацията в списъка с полета имаме нови полета на обобщена таблица "Година", "Месец", "Година - месец", към които добавихме проста маса с данни:

Сега нека анализираме продажбите по години.

За целта плъзгаме полето "Година" в "имената на колоните" на обобщената таблица. Получаваме таблица с продажби по продукти по години:

Сега искаме да "слезем" още по-дълбоко до нивото на месеците и да анализираме продажбите по години и по месеци. За да направите това, плъзнете полето за месец под годината в "имена на колони":

Да се \u200b\u200bанализира динамиката на месеците по години, можем да преместим месеците в пивотната област „Имена на редове“ и да получим следния изглед на обобщената таблица:

В този изглед на обобщена таблица виждаме:

продажби за всеки продукт в размер за цялата година (ред с името на продукта);
по-подробно продажбите за всеки продукт през всеки месец в динамика за 4 години.

Следващото предизвикателство, ние искаме да премахнем продажбите за месец от анализа (например октомври 2012 г.), защото все още нямаме данни за продажби за цял месец.
За да направите това, плъзнете "Година - месец" до ос "Филтър за отчети"

Щракнете върху обобщаващия филтър, който се показва по-горе и поставете отметка в квадратчето „Избор на множество елементи“... След това в списъка с години и брой месеци премахнете отметката от 2012 10 и щракнете върху OK.

По този начин можете да добавите нови параметри за промяна на времето и да анализирате онези периоди от време, които са интересни за вас, и във формата, в която имате нужда. Осевата таблица ще изчисли индикатори за тези полета и филтри, които сте задали, и ще добави към тях като поле на интерес.

Изчисляване на pronoz с помощта на обобщена таблица и Forecast4AC PRO

Нека изградим продажби, като използваме обобщена таблица по продукти, по години и по месеци. Също така ще изключим общите суми, така че те да не бъдат включени в изчислението.

За да изключите общите суми в обобщената таблица, поставете курсора върху колоната "Общ сбор" и кликнете върху бутона "Изтриване на общия сбор". Общото изчезва от обобщението.

и натиснете бутона "Chart Model Chart" в менюто Forecast4AC PRO

Получаваме изчислението на прогнозата за 12 месеца и красива графика с анализ на прогнозния модел (тенденция, сезонност и модел) спрямо действителните данни. Forecast4AC PRO може да изчислява прогнози, сезонни темпове, тенденции и други показатели за вас и да изгражда графики въз основа на данните, показани в обобщената таблица.

Сводната таблица в Excel е мощен инструмент за анализ на данни, който ви позволява бързо и лесно да изчислявате показатели и да изграждате данни във формата, която ви интересува.