Използване на обобщени таблици

По-рано вече говорих за факта, че когато се отнася до клетка обобщена таблица вместо нормална връзка се връща функцията GET.DATA.PUMP.TABLE (вижте). Ако се интересувате от като за да се преодолее това неудобство, препоръчвам да се обърнете към споменатата бележка. Ако се чудите, защо това се случва, както и кои са положителните аспекти на функцията GET DATA.COMPLETE.TABLE, тогава предлагам фрагмент от книгата Jelen, Alexander. (глава 15). Разглежданата техника ще ви позволи да се справите с много проблеми, които причиняват главоболие на потребителите на обобщени таблици, по-специално:

Когато осевата таблица се обнови, приложеното преди това форматиране изчезва. Форматите на числата се губят. Резултатите от корекцията на ширината на колоната изчезват.
Не съществува лесен начин създаване на асиметрична обобщена таблица. Единствената опция е да се използват наименовани набори, но този метод е достъпен само за тези, които използват обобщени таблици на модела на данни, а не обикновени обобщени таблици.
Excel не може да запомни шаблони. Ако трябва да пресъздавате обобщени таблици отново и отново, ще трябва да групирате отново, да приложите изчислени полета и членове и да изпълните редица други подобни задачи.

Всъщност всичко описано тук не е ново. Освен това подобни техники се използват от Excel 2002. Въпреки това, комуникацията ми с потребители показва, че по-малко от 1% са запознати с тях. Единственият въпрос, който потребителите имат, е как да изключат странната функция GET DATA OF PANEL.TABLE. Жалко…

Изтеглете бележка във формат или, примери във формат

Е, да започнем по ред.

Как да изоставим проблемната функция ВЗЕМЕТЕ ДАННИ. ЛИЧНИ ТАБЛИЦИ

Функцията GET PIVOTTABLE DATA отдавна е главоболие за много потребители. Изведнъж, без никакво предупреждение, поведението на обобщените таблици се промени в Excel 2002. Веднага след като започнете да създавате формули извън обобщената таблица, които препращат нейните данни, тази функция идва от нищото.

Да предположим, че в обобщената таблица, показана на фиг. 1, трябва да сравните данните за 2015 и 2014 г.

Фигура: 1. Оригинална обобщена таблица

Добавете заглавието "% Height" към клетка D3.
Копирайте формата от клетка C3 в клетка D3.
Въведете знак за равенство в клетка D4.
Кликнете върху клетка C4.
Въведете знака / (наклонена черта), за да представите операцията по разделяне.
Кликнете върху клетка B4.
Въведете -1 и натиснете клавишната комбинация да остане в същата килия. Форматирайте резултата като процент. Ще видите, че Западът отбеляза спад от 43,8% на доходите (Фигура 2). Не много добри резултати.
Когато приключите с въвеждането на първата си формула, изберете клетка D4.
Щракнете двукратно върху малкия квадрат, разположен в долния десен ъгъл на клетката. Този квадрат представлява манипулатор за попълване, който можете да използвате, за да копирате формула, за да попълните цяла колона в отчета.

След като приключите с копирането на формулата, хвърляйки поглед към екрана, ще разберете, че нещо не е наред - всеки регион е показал спад от 43,8% за годината (фиг. 3).

Фигура: 3. Когато приключите с копирането на формулата във всички клетки в колоната, ще видите, че всеки регион е показал спад от 43,8%

Това е малко вероятно да се случи в реалния живот. Всеки ще ви каже, че след като изпълните стъпките по-горе, Excel ще създаде формулата \u003d C4 / B4-1. Върнете се в клетка D4 и забележете лентата с формули (Фигура 4). Просто някакъв дявол! Простата формула \u003d C4 / B4-1 вече не съществува. Вместо него, програмата замества сложна конструкция с функцията GET.DATA.PUMP.TABLE. Защо тази формула дава правилни резултати в клетка D4, но след копиране в клетките под нея отказва да работи?

Първата реакция на всеки потребител на случилото се ще бъде следната: „Каква е тази странна конструкция на GET.DATA.PERSONAL.TABLE, която съсипа моя отчет?“ Повечето потребители ще искат да се отърват от тази функция веднага. Някои ще зададат въпроса: „Защо Microsoft ни подмени тази функция?“

Нямаше нищо подобно в excel пъти 2000. След като започнах да срещам редовно функцията GET.DATA.PERSONAL.TABLES, просто я мразех. Когато на един от семинарите някой ме попита как може да се използва в полза на каузата, бях онемял. Никога не съм си задавал такъв въпрос! Според мен и според повечето потребители на Excel функцията GET.DATA.PUMP.TABLE е продукт на зло, което няма нищо общо със силите на доброто. За щастие има два начина да деактивирате тази функция.

Блокиране на функцията GET PIVOTTABLE DATA чрез въвеждане на формула.Има лесен начин да се предотврати появата на функцията GET PIVOTTABLE. За да направите това, трябва да създадете формула, без да използвате мишката или клавишите на курсора. Просто следвайте тези стъпки.

Отидете до клетка D4 и въведете \u003d (знак за равенство).
Въведете C4.
Въведете / (наклонена черта за разделяне).
Въведете B4.
Въведете -1.
Щракнете Въведете.

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

Фигура: 5. Просто въведете \u003d C4 / B4-1 от клавиатурата и формулата ще работи както ви е необходимо

Някои потребители ще се чувстват неудобно при обичайния ред на въвеждане на формули. Освен това предлаганият вариант е по-трудоемък. Ако сте един от тези потребители, вторият начин е за вас ...

Деактивирайте функцията GET PANEL.TABLE DATA.Можете да забраните за постоянно функцията GET PANEL.TABLE DATA. Кликнете върху лентата на менюто Файл → Настроики... В отворения прозорец НастроикиExcel отидете на принос Формули и премахнете отметката от квадратчето до опцията Използвайте функциятаGetPivotData за връзки на обобщена таблица... Щракнете Добре.

Алтернативен вариант. Кликнете върху обобщената таблица и в контекстния раздел, който се появява Анализ щракнете върху падащия списък до бутона Настроики... Премахнете отметката от квадратчето до Създайте GetPivotData (фиг. 7). Полето за отметка е включено по подразбиране.

Защо Microsoft ни предложи функцията GET PANEL.TABLES.Ако тази функция е толкова ужасна, защо Microsoft я активира по подразбиране? Защо се грижат да поддържат поддръжка за тази функция в по-новите версии на Excel? Наясно ли са с настроенията на потребителите? И преминаваме към забавната част ...

Използване на GET PIVOTTABLE DATA за подобряване на обобщените таблици

Осевите маси са велико изобретение на човечеството. Осевата таблица се създава само с няколко щраквания, елиминирайки необходимостта от прилагане на разширен филтър, BDSUMM функция и таблици с данни. С обобщените таблици можете да създавате отчети от една страница от огромни количества данни. Тези предимства засенчват някои от недостатъците на обобщените таблици, като неясно форматиране и необходимостта от преобразуване на обобщени таблици в стойности за допълнително персонализиране. На фиг. Фигура 8 показва типичен процес за създаване на обобщена таблица. В този случай всичко започва с първоначалните данни. Създаваме обобщена таблица и използваме всички възможни техники, за да я персонализираме и подобрим. Понякога превръщаме обобщената таблица в стойности и правим окончателно форматиране.

Новата методология на обобщената таблица, предложена от Роб Коли (разработчик в Microsoft) и обсъдена по-долу, е резултат от подобрения в процеса, описан по-горе. В този случай първо се създава примитивна обобщена таблица. Не е необходимо да форматирате тази таблица. След това се следва едноетапен, относително отнемащ време процес, за да се създаде добре форматирана обвивка, в която ще се помести окончателният отчет. След това функцията GET.DATA.PERSONAL.TABLE се използва за бързо попълване на отчет, който е в черупката с данни. След като получите новите данни, можете да ги поставите на листа, да актуализирате примитивната обобщена таблица и да отпечатате отчета, който е в черупката (Фигура 9). Тази техника има редица неоспорими предимства. Например не е нужно да се притеснявате за форматирането на отчета веднага след като го създадете. Процесът на създаване на обобщени таблици става почти напълно автоматизиран.

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

Създайте примитивна обобщена таблица.Първоначалните данни (фиг. 10) са представени под формата на транзакции, съдържащи информация за планирани и действителни показатели за всеки регион, в който има клонове на компанията. Планираните цифри са подробно описани на месечно ниво, а реалните - на ниво отделни дни. Планираните показатели се създават за предстоящата година, а действителните за последните месеци. Тъй като отчетът ще се актуализира всеки месец, този процес е значително опростен, ако източникът на данни на обобщената таблица нараства, тъй като в дъното се добавят нови данни. В остарели версии Създаване на Excel подобен източник на данни е реализиран с помощта на наименуван динамичен диапазон, използвайки функцията OFFSET (вижте подробности). Когато работите в Excel 2013, просто изберете една от клетките с данни и натиснете клавишната комбинация Ctrl + T (създайте таблица). Това създава именен набор от данни, който автоматично се разширява при добавяне на нови редове и колони.

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

Създайте обобщена таблица:

Изберете Екип Поставете → Осева масаи след това в диалоговия прозорец Създаване на обобщена таблицащракнете Добре.
В списъка с полета на обобщената таблица изберете поле датата... Списък от дати ще се появи в лявата част на обобщената таблица (фиг. 11).
Изберете която и да е клетка за дата, например A4. В контекстния раздел Анализразположени в набора от контекстни раздели Работа с обобщени таблици, щракнете върху бутона Групиране по поле (виж детайлите). В диалоговия прозорец Групиране изберете опция Месеци (фиг. 12). Щракнете Добре... Имената на месеците ще се появят отляво на обобщената таблица (фиг. 13).
Плъзнете полето датата до областта Колони на обобщената таблица.
Плъзнете полето Индекс в областта Колони на списъка с полета на обобщената таблица.
Изберете поле Регионда се показва в лявата колона на обобщената таблица.
Изберете поле Доходкоето се появява в областта Стойности на обобщената таблица.

Фигура: 11. Започнете с групиране по поле датата

В този момент нашата обобщена таблица изглежда доста примитивна (Фигура 14). Наистина не обичам надписването Имена на редове и Имена на колони... Непрактично е да се показват суми за Ян План и Ян Факт в колона Г и т.н. Но не се притеснявайте външен вид тази обобщена таблица, защото освен вас никой друг няма да я види. От този момент нататък ще създадем обвивка на отчет, източник на данни за която ще бъде новосъздадената обобщена таблица.

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

Продължете както следва (фиг. 15).

В клетка A1 въведете името на отчета - Планирани и действителни показатели по региони.
Отидете в раздела У дома, щракнете върху бутона Стилове на клетки изберете формат Заглавие 1.
В клетка A2 въведете формулата \u003d EON МЕСЕЦИ (ДНЕС (); 0). Тази функция връща последния ден от текущия месец. Например, ако четете тези редове на 14 август 2014 г., клетка А2 ще покаже датата 31 август 2014 г.
Изберете клетка A2. Натиснете комбинацията от клавиши Ctrl + 1, за да се покаже диалоговият прозорец Формат на клетка... В раздела Брой щракнете върху елемент Всички формати... Въведете персонализиран формат на номера като "От месец" MMMM "планирани показатели" (фиг. 16). В резултат на това изчислената дата ще изглежда като текст.
В клетка A5 въведете заглавие Регион.
Въведете заглавията на регионите в останалите клетки в колона А. Заглавията на регионите трябва да съответстват на имената на регионите, показани в обобщената таблица.
Ако е необходимо, добавете етикети към колоната за общи суми по отдели.
В долната част на отчета добавете реда Общо за компанията.
В клетка B4 въведете формулата \u003d ДАТА (ГОДИНА ($ A $ 2); КОЛОНА (A1); 1). Тази формула връща датите 01/01/2014, 01/02/2104 и т.н., първите дни от всички 12 месеца на текущата година.
Изберете клетка B4. Натиснете клавишната комбинация Ctrl + 1, за да отворите прозореца Формат на клетка... В раздела Брой В глава Всички формати въведете персонализиран формат на номера Ммм... Този формат показва името на месеца с три букви. Подравнете текста вдясно от клетката.
Копирайте съдържанието на клетка B4 в диапазона C4: M4. В горната част на обобщената таблица се появява ред с имената на месеците.
В клетка B5 въведете формулата \u003d IF (МЕСЕЦ (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Факт, а за настоящото и бъдещето - План.
Добавете заглавие към клетка N5 Резултат... Към клетка O4 - Резултат, O5 - План, Р5 - % отклонение.
Въведете обичайните формули на Excel, използвани за изчисляване на суми за отделите, общ ред на компанията, обща колона и колона% отклонение:
1. в клетка B8 въведете формулата \u003d SUM (B6: B7) и я копирайте в други клетки в реда;
2. в клетка N6 въведете формулата \u003d SUM (B6: M6) и я копирайте в други клетки в колоната;
3. в клетка P6 въведете формулата \u003d IFERROR ((N6 / O10) -1; 0) и я копирайте в други клетки в колоната;
4. в клетка B13 въведете формулата \u003d SUM (B10: B12) и я копирайте в други клетки в реда;
5. в клетка B17 въведете формулата \u003d SUM (B15: B16) и я копирайте в други клетки в реда;
6. в клетка B19 въведете формулата \u003d SUM (B6: B18) / 2 и я копирайте в други клетки в реда.
Приложете стила Heading 4 към етикетите в колона A и заглавията в редове 4 и 5.
За диапазона от клетки B6: O19 изберете числовия формат # ## 0.
За клетките в колона P изберете числовия формат 0,0%.

И така, завършихме създаването на черупката на отчета, показана на фиг. 15. Този отчет включва цялото необходимо форматиране. Следващият раздел демонстрира как да използвате функцията GET PIVOTTABLE DATA за попълване на отчет.

Фигура: 15. Докладване на обвивка, преди да добавите формули GET.DATA.PERSONAL.TABLES

Използване на функцията GET PIVOTTABLE DATA за попълване на черупката на отчета с данни.Отсега нататък ще можете да изпитате всички предимства на използването на функцията GET PANEL.TABLE DATA. Ако сте изчистили квадратчето за отметка, активиращо тази функция, върнете се към съответната настройка и върнете отметката (вижте описанието на фиг. 6 или 7).

Изберете клетка B6 на обвивката на отчета. Тази клетка съответства на Североизточния регион и действителните данни за януари.

Въведете \u003d (знак за равенство), за да започнете да въвеждате формулата.
Отидете до листа от обобщената таблица и кликнете върху клетката, която съответства на североизточния регион и действителните цифри за януари - C12 (фиг. 17).
Натиснете бутона Въведетеза да завършите въвеждането на формулата и да се върнете към обвивката на отчета. В резултат на това Excel ще добави функцията ПОЛУЧИ ПИВОТАТАЛНИ ДАННИ в клетка B6. Клетката ще покаже стойността от $ 277 435.

Запомнете това число, тъй като ще се изисква при сравняване с резултатите от формулата, която ще редактирате по-късно. Формулата, генерирана от програмата, има следната форма: \u003d GET.DATA.PERSONAL.TABLE ("Доход"; 'Фиг. 11-14 ′! $ A $ 3; "Регион"; "Североизток"; "Дата" ; 1; "Индикатор"; "Факт"). Ако досега сте пренебрегнали функцията GET PivotTable, е време да я разгледате отблизо. На фиг. 18 тази формула се показва в режим на редактиране заедно с подсказка.

Аргументи на функцията:

Data_field. Поле от областта на стойността на обобщената таблица. Моля, обърнете внимание: в този случай полето се използва Доход, но не Сума в полето Доход.
Осева_таблица. С този параметър Microsoft ви пита: "Коя обобщена таблица искате да използвате?" Достатъчно е да посочите една от клетките на обобщената таблица. Вписването „Фиг. 11-14 '! $ A $ 3 се отнася до първата клетка в обобщената таблица, където се въвеждат данни. Тъй като в нашия случай можете да посочите всяка клетка, свързана с обобщената таблица, оставете аргумента непроменен. Адресът на клетката $ A $ 3 е подходящ във всички отношения.
Поле 1; елемент 1. В автоматично генерираната формула се избира името на полето Региони като стойност на полето - Североизток... Тук се крие причината за проблемите, които възникват при работа с функцията GET DATA.PUMP.TABLE. Автоизбраните стойности не могат да бъдат копирани, защото са кодирани твърдо. Следователно, ако копирате формули в цялата област на отчета, ще трябва да ги промените ръчно. Заменете Североизток с препратка към клетка от формата $ A6. Поставяйки знак за долар пред колона А, вие определяте дали редовата част на препратката може да бъде променена, когато копирате формулата в клетките в колоната.
Поле 2; т. 2. Тази двойка аргументи дефинира полето датата със стойност 1. Ако оригиналната обобщена таблица е групирана по месец, полето за месец запазва името на оригиналното поле датата... Числовата стойност на месеца е 1, което съответства на януари. Едва ли е препоръчително да се използва такава стойност при създаване на огромни формули, които са посочени в десетки или дори стотици клетки на отчета. По-добре да използвате формула, която изчислява стойностите на полетата датата, като формулата в клетка B4. Вместо 1 в този случай можете да използвате формулата МЕСЕЦ (В $ 4). Знакът за долар, предхождащ 4, показва, че формулата може да присвоява стойности на полето датата въз основа на други месеци, тъй като формулата се копира в клетките в реда.
Поле 3; елемент 3. В този случай името на полето се присвоява автоматично Индекс и стойност на полето Факт... Тези стойности са верни за януари, но за следващите месеци стойността на полето ще трябва да бъде променена на Plan. Променете твърдо кодираната стойност на полето Факт към връзката B $ 5.
Поле 4; Елемент 4. Тези аргументи не се използват, защото полетата свършиха.

Новата формула е показана на фиг. 19. След минута вместо твърдо кодирана формула, предназначена да работи с една стойност, беше създадена гъвкава формула, която може да бъде копирана във всички клетки в набора от данни. Натиснете бутона Въведетеи получавате същия резултат като преди редактиране на формулата. Редактираната формула приема следната форма: \u003d GET.PUMP.TABLE DATA ("Доход"; 'Фиг. 11-14 ′! $ A $ 3; "Регион"; $ A6; "Дата"; MONTH (B $ 4) ; "Индикатор"; B $ 5)

Фигура: 19. След завършване на редактирането формулата GET.DATA.PERSONAL.TABLE е подходяща за копиране във всички клетки от диапазона

Копирайте формулата във всички празни клетки в колони B: M, където се изчисляват резултатите. Сега, когато отчетът съдържа реални числа, можете да направите окончателни корекции на ширините на колоните.

В следващата стъпка ще настроим формулата GET.PUMP.TABLE за изчисляване на крайните цели. Ако просто копирате формулата в клетка O6, съобщението за грешка #REF! Причината за тази грешка е, че думата Резултат в клетка O4 не е името на месеца. За да се гарантира правилната работа на функцията ПОЛУЧАВАНЕ НА ПИВОТАТАЛНИ ДАННИ, необходимата стойност трябва да бъде в обобщената таблица. Но тъй като в оригиналната обобщена таблица, полето Индекс е второто поле в областта на колоната, колоната с данни Обобщение на плана всъщност отсъства. Преместете полето Индекс така че да стане първият в областта на колоната (Фигура 20).

Фигура: 20. Настройте оформлението на полетата в областта на колоната, така че колоната да се появи Контурен план

Сравнете с фиг. 14. Там, в областта на КОЛОНАТА, полето беше първото датата, което доведе до факта, че първоначално колоните бяха групирани по дата, а в рамките на всеки месец по план / факт. Сега първото е полето Индикатор, а в резюмето колоните са първи План, вътре сортирано по месец и след това всички колони Факт.

Връщайки се към листа на обвивката на отчета, застанете в клетка O6, въведете \u003d (знак за равенство) и се обърнете към клетка N12 на листа на обобщената таблица, съответстваща на планираните общи стойности за североизточния регион. Щракнете Въведете... Резултатът е формулата \u003d GET.DATA.PERSONAL.TABLES ("Доход"; "Фиг. 11-14"! $ A $ 3; "Регион"; "Североизток"; "Индикатор"; "План"). Редактирайте го: \u003d ВЗЕМЕТЕ. DATA.SUMMARY.TABLES ("Доход"; ’Фиг. 11-14 ′! $ A $ 3;" Регион "; $ A6;" Индикатор "; O $ 5). Копирайте тази формула в други клетки в колона O (Фигура 21). Моля, обърнете внимание, че дори ако преместите различни области на отчета за обобщената таблица, черупката работи правилно. Разбира се, ако направите някои осни полета неактивни, черупката няма да се справи с това ...

Фигура: 21. Окончателен доклад, който може да бъде представен на мениджъра

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

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

Поставете действителните цифри под оригиналния набор от данни. Тъй като изходните данни са във формат на таблица, форматирането на таблици автоматично се разпространява в нови редове с данни. Той също така разширява дефиницията на оригиналната обобщена таблица (във файла на Excel вече съм добавил действителните цифри за цялата година).
Отидете до осевата таблица. Щракнете с десния бутон и изберете Обнови... Външният вид на обобщената таблица ще се промени, но това е добре.
Отидете до обвивката на отчета. По принцип вече е направено всичко за актуализиране на отчета, но не пречи да се тестват резултатите. Променете формулата в клетка A2, например, на тази: \u003d EON МЕСЕЦ (ДНЕС () +31 ; 0) и вижте какво ще се случи.

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

Не мислех, че някога ще кажа следното: „Функцията GET.DATA.PUMP.TABLE е най-голямата благословия. Как сме съществували без нея преди? "

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

По-рано вече говорих за факта, че при препратка към клетка на обобщена таблица, вместо нормална връзка, се връща функцията GET.PIVOTTABLE DATA (вижте). Ако се интересувате от като за да се преодолее това неудобство, препоръчвам да се обърнете към споменатата бележка. Ако се чудите, защо това се случва, както и кои са положителните аспекти на функцията GET DATA.COMPLETE.TABLE, тогава предлагам фрагмент от книгата Jelen, Alexander. (глава 15). Разглежданата техника ще ви позволи да се справите с много проблеми, които причиняват главоболие на потребителите на обобщени таблици, по-специално:

Когато осевата таблица се обнови, приложеното преди това форматиране изчезва. Форматите на числата се губят. Резултатите от корекцията на ширината на колоната изчезват.
Няма лесен начин за създаване на асиметрична обобщена таблица. Единствената опция е да се използват назовани набори, но този метод е достъпен само за онези, които използват обобщени таблици на модела на данни, а не обикновени обобщени таблици.
Excel не може да запомни шаблони. Ако трябва да пресъздавате обобщени таблици отново и отново, ще трябва да групирате отново, да приложите изчислени полета и членове и да изпълните редица други подобни задачи.

Изтеглете бележка във формат или, примери във формат

Е, да започнем по ред.

Как да изоставим проблемната функция ВЗЕМЕТЕ ДАННИ. ЛИЧНИ ТАБЛИЦИ

Да предположим, че в обобщената таблица, показана на фиг. 1, трябва да сравните данните за 2015 и 2014 г.

Фигура: 1. Оригинална обобщена таблица

Добавете заглавието "% Height" към клетка D3.
Копирайте формата от клетка C3 в клетка D3.
Въведете знак за равенство в клетка D4.
Кликнете върху клетка C4.
Въведете знака / (наклонена черта), за да представите операцията по разделяне.
Кликнете върху клетка B4.
Въведете -1 и натиснете клавишната комбинация да остане в същата килия. Форматирайте резултата като процент. Ще видите, че Западът отбеляза спад от 43,8% на доходите (Фигура 2). Не много добри резултати.
Когато приключите с въвеждането на първата си формула, изберете клетка D4.
Щракнете двукратно върху малкия квадрат, разположен в долния десен ъгъл на клетката. Този квадрат представлява манипулатор за попълване, който можете да използвате, за да копирате формула, за да попълните цяла колона в отчета.

Нищо подобно не се случи в дните на Excel 2000. Когато започнах да срещам редовно функцията GET.DATA.PERSONAL.TABLES, просто я мразех. Когато на един от семинарите някой ме попита как може да се използва в полза на каузата, бях онемял. Никога не съм задавал такъв въпрос! Според мен и според повечето потребители на Excel функцията GET.DATA.PUMP.TABLE е продукт на зло, което няма нищо общо със силите на доброто. За щастие има два начина да деактивирате тази функция.

Отидете до клетка D4 и въведете \u003d (знак за равенство).
Въведете C4.
Въведете / (наклонена черта за разделяне).
Въведете B4.
Въведете -1.
Щракнете Въведете.

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

Фигура: 5. Просто въведете \u003d C4 / B4-1 от клавиатурата и формулата ще работи както ви е необходимо

Използване на GET PIVOTTABLE DATA за подобряване на обобщените таблици

Създайте примитивна обобщена таблица.Първоначалните данни (фиг. 10) са представени под формата на транзакции, съдържащи информация за планирани и действителни показатели за всеки регион, в който има клонове на компанията. Планираните цифри са подробно описани на месечно ниво, а реалните - на ниво отделни дни. Планираните показатели се създават за предстоящата година, а действителните за последните месеци. Тъй като отчетът ще се актуализира всеки месец, този процес е значително опростен, ако източникът на данни на обобщената таблица нараства, тъй като в дъното се добавят нови данни. В по-старите версии на Excel такъв източник на данни е създаден с помощта на наименуван динамичен диапазон с помощта на функцията OFFSET (вижте подробности). Когато работите в Excel 2013, просто изберете една от клетките с данни и натиснете клавишната комбинация Ctrl + T (създайте таблица). Това създава именен набор от данни, който автоматично се разширява при добавяне на нови редове и колони.

Създайте обобщена таблица:

Изберете Екип Поставете → Осева масаи след това в диалоговия прозорец Създаване на обобщена таблицащракнете Добре.
В списъка с полета на обобщената таблица изберете поле датата... Списък от дати ще се появи в лявата част на обобщената таблица (фиг. 11).
Изберете която и да е клетка за дата, например A4. В контекстния раздел Анализразположени в набора от контекстни раздели Работа с обобщени таблици, щракнете върху бутона Групиране по поле (виж детайлите). В диалоговия прозорец Групиране изберете опция Месеци (фиг. 12). Щракнете Добре... Имената на месеците ще се появят отляво на обобщената таблица (фиг. 13).
Плъзнете полето датата до областта Колони на обобщената таблица.
Плъзнете полето Индекс в областта Колони на списъка с полета на обобщената таблица.
Изберете поле Регионда се показва в лявата колона на обобщената таблица.
Изберете поле Доходкоето се появява в областта Стойности на обобщената таблица.

Фигура: 11. Започнете с групиране по поле датата

В този момент нашата обобщена таблица изглежда доста примитивна (Фигура 14). Наистина не обичам надписването Имена на редове и Имена на колони... Непрактично е да се показват суми за Ян План и Ян Факт в колона Г и т.н. Но не се притеснявайте за появата на тази обобщена таблица, защото никой друг няма да я види освен вас. От този момент нататък ще създаваме черупка на отчета, източник на данни за която ще бъде новосъздадената обобщена таблица.

Продължете както следва (фиг. 15).

В клетка A1 въведете името на отчета - Планирани и действителни показатели по региони.
Отидете в раздела У дома, щракнете върху бутона Стилове на клетки изберете формат Заглавие 1.
В клетка A2 въведете формулата \u003d EON МЕСЕЦИ (ДНЕС (); 0). Тази функция връща последния ден от текущия месец. Например, ако четете тези редове на 14 август 2014 г., клетка А2 ще покаже датата 31 август 2014 г.
Изберете клетка A2. Натиснете комбинацията от клавиши Ctrl + 1, за да се покаже диалоговият прозорец Формат на клетка... В раздела Брой щракнете върху елемент Всички формати... Въведете персонализиран формат на номера като "От месец" MMMM "планирани показатели" (фиг. 16). В резултат на това изчислената дата ще изглежда като текст.
В клетка A5 въведете заглавие Регион.
Въведете заглавията на регионите в останалите клетки в колона А. Заглавията на регионите трябва да съответстват на имената на регионите, показани в обобщената таблица.
Ако е необходимо, добавете етикети към колоната за общи суми по отдели.
В долната част на отчета добавете реда Общо за компанията.
В клетка B4 въведете формулата \u003d ДАТА (ГОДИНА ($ A $ 2); КОЛОНА (A1); 1). Тази формула връща датите 01/01/2014, 01/02/2104 и т.н., първите дни от всички 12 месеца на текущата година.
Изберете клетка B4. Натиснете клавишната комбинация Ctrl + 1, за да отворите прозореца Формат на клетка... В раздела Брой В глава Всички формати въведете персонализиран формат на номера Ммм... Този формат показва името на месеца с три букви. Подравнете текста вдясно от клетката.
Копирайте съдържанието на клетка B4 в диапазона C4: M4. В горната част на обобщената таблица се появява ред с имената на месеците.
В клетка B5 въведете формулата \u003d IF (МЕСЕЦ (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Факт, а за настоящото и бъдещето - План.
Добавете заглавие към клетка N5 Резултат... Към клетка O4 - Резултат, O5 - План, Р5 - % отклонение.
Въведете обичайните формули на Excel, използвани за изчисляване на суми за отделите, общ ред на компанията, обща колона и колона% отклонение:
1. в клетка B8 въведете формулата \u003d SUM (B6: B7) и я копирайте в други клетки в реда;
2. в клетка N6 въведете формулата \u003d SUM (B6: M6) и я копирайте в други клетки в колоната;
3. в клетка P6 въведете формулата \u003d IFERROR ((N6 / O10) -1; 0) и я копирайте в други клетки в колоната;
4. в клетка B13 въведете формулата \u003d SUM (B10: B12) и я копирайте в други клетки в реда;
5. в клетка B17 въведете формулата \u003d SUM (B15: B16) и я копирайте в други клетки в реда;
6. в клетка B19 въведете формулата \u003d SUM (B6: B18) / 2 и я копирайте в други клетки в реда.
Приложете стила Heading 4 към етикетите в колона A и заглавията в редове 4 и 5.
За диапазона от клетки B6: O19 изберете числовия формат # ## 0.
За клетките в колона P изберете числовия формат 0,0%.

Фигура: 15. Докладване на обвивка, преди да добавите формули GET.DATA.PERSONAL.TABLES

Въведете \u003d (знак за равенство), за да започнете да въвеждате формулата.
Отидете до листа от обобщената таблица и кликнете върху клетката, която съответства на североизточния регион и действителните цифри за януари - C12 (фиг. 17).
Натиснете бутона Въведетеза да завършите въвеждането на формулата и да се върнете към обвивката на отчета. В резултат на това Excel ще добави функцията ПОЛУЧИ ПИВОТАТАЛНИ ДАННИ в клетка B6. Клетката ще покаже стойността от $ 277 435.

Аргументи на функцията:

Data_field. Поле от областта на стойността на обобщената таблица. Моля, обърнете внимание: в този случай полето се използва Доход, но не Сума в полето Доход.
Осева_таблица. С този параметър Microsoft ви пита: "Коя обобщена таблица искате да използвате?" Достатъчно е да посочите една от клетките на обобщената таблица. Вписването „Фиг. 11-14 '! $ A $ 3 се отнася до първата клетка в обобщената таблица, където се въвеждат данни. Тъй като в нашия случай можете да посочите всяка клетка, свързана с обобщената таблица, оставете аргумента непроменен. Адресът на клетката $ A $ 3 е подходящ във всички отношения.
Поле 1; елемент 1. В автоматично генерираната формула се избира името на полето Региони като стойност на полето - Североизток... Тук се крие причината за проблемите, които възникват при работа с функцията GET DATA.PUMP.TABLE. Автоизбраните стойности не могат да бъдат копирани, защото са кодирани твърдо. Следователно, ако копирате формули в цялата област на отчета, ще трябва да ги промените ръчно. Заменете Североизток с препратка към клетка от формата $ A6. Поставяйки знак за долар пред колона А, вие определяте дали редовата част на препратката може да бъде променена, когато копирате формулата в клетките в колоната.
Поле 2; т. 2. Тази двойка аргументи дефинира полето датата със стойност 1. Ако оригиналната обобщена таблица е групирана по месец, полето за месец запазва името на оригиналното поле датата... Числовата стойност на месеца е 1, което съответства на януари. Едва ли е препоръчително да се използва такава стойност при създаване на огромни формули, които са посочени в десетки или дори стотици клетки на отчета. По-добре да използвате формула, която изчислява стойностите на полетата датата, като формулата в клетка B4. Вместо 1 в този случай можете да използвате формулата МЕСЕЦ (В $ 4). Знакът за долар, предхождащ 4, показва, че формулата може да присвоява стойности на полето датата въз основа на други месеци, тъй като формулата се копира в клетките в реда.
Поле 3; елемент 3. В този случай името на полето се присвоява автоматично Индекс и стойност на полето Факт... Тези стойности са верни за януари, но за следващите месеци стойността на полето ще трябва да бъде променена на Plan. Променете твърдо кодираната стойност на полето Факт към връзката B $ 5.
Поле 4; Елемент 4. Тези аргументи не се използват, защото полетата свършиха.

Фигура: 21. Окончателен доклад, който може да бъде представен на мениджъра

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

Поставете действителните цифри под оригиналния набор от данни. Тъй като изходните данни са във формат на таблица, форматирането на таблици автоматично се разпространява в нови редове с данни. Той също така разширява дефиницията на оригиналната обобщена таблица (във файла на Excel вече съм добавил действителните цифри за цялата година).
Отидете до осевата таблица. Щракнете с десния бутон и изберете Обнови... Външният вид на обобщената таблица ще се промени, но това е добре.
Отидете до обвивката на отчета. По принцип вече е направено всичко за актуализиране на отчета, но не пречи да се тестват резултатите. Променете формулата в клетка A2, например, на тази: \u003d EON МЕСЕЦ (ДНЕС () +31 ; 0) и вижте какво ще се случи.

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

За да получите бърз достъп до функцията, трябва да въведете знак за равенство в клетката (\u003d) и да изберете необходимата клетка в обобщената таблица. Excel ще генерира функцията GET PIVOTTABLE DATA автоматично.

Деактивирайте създаването на GetPivotData

За да изключите автоматичното генериране на функцията GET DATA.PIVOTTABLE, изберете която и да е клетка в обобщената таблица, щракнете върху раздела Работа с обобщени таблици -\u003e Опциикъм групата Осева таблица.Щракнете върху стрелката надолу до раздела Настроики.В падащото меню премахнете отметката от квадратчето СъздавамGetPivotData.

Използване на референции за клетки във функцията GET DATA.PUMP.TABLE

Вместо да посочвате имената на елементи или полета във функцията GET.DATA.PUMP.TABLE, можете да се обърнете към клетки на работния лист. В примера по-долу клетка E3 съдържа името на продукта и формулата в клетка E4 се отнася към него. В резултат на това ще бъде върнат общият обем за тортите.

Използване на препратки към полета на обобщена таблица

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

В примера клетка E3 съдържа името на полето за данни "Количество" и би било хубаво да се обърнете към тази клетка във функцията, вместо да имате името на полето във формулата GET.DATA.PUMP.TABLE.

Ако обаче сменим първия аргумент data_fieldкъм препратката към клетка E3, Excel ще ни върне грешката #REF!

ВЗЕМЕТЕ ЛИЧНИ ДАННИ ЗА МАСА (E3; $ A $ 3)

Простото добавяне на празен низ („”) в началото или края на препратката към клетката решава проблема.

ВЗЕМЕТЕ ЛИЧНИ ДАННИ ЗА МАСА (E3 & ""; $ A $ 3)

Една проста корекция на формулата ще върне правилната стойност.

Използване на дати във функцията GET DATA.PUMP.TABLE

Ако използвате дати във функцията ПОЛУЧАВАНЕ НА ПИВОТАТАЛНИ ДАННИ, може да имате проблеми, дори датата да се показва в обобщената таблица. Например, формулата по-долу е датата „21.04.2013 г.“, а обобщената таблица съдържа поле с дати на продажби. Формулата в клетка E4 обаче връща грешка.

GET.DATA.SUMMARY.TABLE ("Обем"; $ A $ 3; "Дата"; "21.04.2013 г.")

За да избегнете грешки, свързани с дати, можете да използвате един от следните методи:

Сравнете форматите за дати във формула и обобщена таблица
Използвайте функцията DATEVALUE
Използвайте функцията DATE
Обърнете се към клетката с точна дата

Сравнете форматите за дати във формула и обобщена таблица.

За да получите правилния резултат, когато използвате функцията GET PIVOTTABLE DATA, уверете се, че форматите на датата на аргумента на формулата и обобщената таблица са еднакви.

В клетка E4 формулата използва формата на датата „DD.MM.YYYY“ и в резултат връща правилната информация.

Използване на функцията DATEVALUE

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

В клетка E4 датата се въвежда с помощта на функцията DATEVALUE и Excel връща необходимата информация.

RECEIVE.SUMMARY.TABLE.DATA ("Обем"; $ A $ 3; "Дата"; DATEVALUE ("21.04.2013 г."))

Използване на функцията DATE

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

RECEIVE.SUMMARY.TABLE.DATA ("Обем"; $ A $ 3; "Дата"; DATE (2013; 4; 21))

Препратка към клетка за дата

Вместо ръчно въвеждане на датата във формула, можете да се обърнете към клетка, която съдържа дата (във всеки формат, който Excel интерпретира данните като дати). В примера в клетка E4 формулата се отнася до клетка E3 и Excel връща правилните данни.

GET.DATA.COMPLETE.TABLE ("Обем"; $ A $ 3; "Дата"; E3)