Един динамичен източник на обобщена информация
Когато работите с PivotTables няколко пъти се сблъскали с проблема, когато новите данни не попадат в доклада. Обобщена таблица се основава на определен кръг от работния лист и новата линия само извън обхват. Намери грешка в този случай е много трудно, тъй като обобщения доклад се актуализира, но се основава на непълна информация. Ние предлагаме повече или по-малко универсални решения, за да се избегнат такива проблеми.
Като прикрепен към член 3, илюстриран примерен вариант на консолидиран отчет въз основа на базата данни на работен лист на.
фиксиран диапазон
В зависимост от практическите задачи може да се наложи да добавите нов източник на данни в обобщения доклад. Обикновено се добавят нови линии. Това може да стане или чрез ръчно въвеждане или копиране, както и за автоматизирано получаване на данни от външни системи. След добавяне на прости изходни данни надолу гама Excel не включва новата линия в източник обобщени таблици Pivot. Актуализиране на доклада няма да доведе до промяна. Всъщност таблицата с обобщена информация се основава на предварително определен фиксиран диапазон данни, които не са включени в новата линия. Пример за такава ситуация е показано в примера на файл с данни-листове и ReportData - линии 11 и 12 не са били пуснати в доклада:
Едно от решенията на проблема е да се добавят нови редове в средата на диапазона - докато Excel правилно определи връзката с източника на осевата таблица данни.
На практика, то обикновено е по-удобно и по-лесно да се определи предварително максималната възможна гама от източници линии обобщена добавяне резерв достатъчен брой редове. В зависимост от задачата, можете да добавите като 10 или 10,000 линии, достатъчно странно, че тя почти не оказва влияние върху работата на обобщени таблици Pivot. файл смяна Пример обобщена Източникът на данни ReportData следва:
Този метод е доста подходящ за използване, ако сте сигурни, че доставката на празни редове винаги покрие евентуална нова количеството данни. Често това не може да бъде гарантирана, особено ако файлът е предназначен за продължителна употреба и множество потребители, които работят с него.
Създаване на склад на празни редове в източника на данни може да бъде и за стари Pivot таблици XLS формат. Тук обаче има малък недостатък в интерфейса: Ако колоната съхранява датата и данни източник съдържа NULL стойности, обобщен доклад няма да позволи да се прилага по отношение на тази област, стандартната групата в продължение на години, тримесечия, месец и т.н. Нови PivotTables XLSX формат не трябва този недостатък.
"Смарт Графика"
Dynamic наименуван диапазон
Потребителите «Advanced» Excel знаят как да използват името диапазони лист. С тези имена могат да се заменят с позовавания на формулите или да посочите източниците на данни. Конвенционални име диапазони - това е просто смяна на дълги връзки в координатите на работния лист в краткото име. Така в примера на работния лист на данни, можете да изберете район A1: D12, дайте име и след това да го посочите като източник на данни ReportData обобщена таблица.
Резултатът ще бъде точно същата, както при използване на фиксираната референтна диапазон. Т.е. проблема с добавянето на нови линии на прост наименуван диапазон не решава.
Пример за такава употреба от името на формула лист Име и ReportName на.
име на източник формула е както следва:
COUNT () и COUNTIF () се използва за изчисляване на стойности са в границите, COUNT () се използва за цифровите клетки, COUNTIF () - за текст. Във формула изчислява броя на не-празни нанизи за типа дата поле (колона А) и броя на колоните на колекторни полета (ред 1). Чрез броя на колоните добавя към устройството, като обхвата включва също ред глава. Полученият OFFSET () връща правоъгълна лента с относителното преместване на лист горния ляв ъгъл (Име! $ A $ 1). Ако броят на областите на входни данни не се променя, а последния параметър на функцията е най-добре да укажете в това число:
С добавянето на няколко реда надолу в таблицата, изпълнен с необработените данни, можете да сте сигурни, че таблицата с обобщена информация е успешно ги обработва (чрез натискане на бутона "Update").
Dynamic наименуван диапазон може да се счита за най-универсален начин за определяне на източника на обобщени таблици Pivot, защото тя не изисква специални съоръжения ( "интелигентна маса") и работи дори и в старата XLS-формат. В този пример има изискване за идентифициране на присъствието на не-празна област в обхвата източник. В действителност, тя също може да бъде заобиколено с помощта на допълнителни изчисления (например добавянето на функция COUNTBLANK ()), най-важното, че е установено в резултат на края на попълнено групата.