O sursă dinamică a datelor PivotTable
Când lucrați cu PivotTable de mai multe ori întâlnit problema atunci când noile date nu se încadrează în raport. Tabel recapitulativ sa bazat pe un interval fix a foii de lucru, iar noua linie doar în afara intervalului. Găsiți o eroare în acest caz, este foarte dificil, deoarece raportul de sinteză este actualizat, dar se bazează pe date incomplete. Noi oferim o soluții mai mult sau mai puțin universale pentru a evita astfel de probleme.
Ca atașat la articolul 3 ilustrat exemplu de realizare a unui raport consolidat în baza de date din foaia de lucru.
interval fix
În funcție de sarcinile practice poate fi necesar să adăugați o nouă sursă de date în raportul de sinteză. De obicei, se adaugă noi linii. Acest lucru poate avea loc fie prin introducerea manuală sau de copiere și pentru primirea automată a datelor din sisteme externe. După adăugarea unui simplu sursă de date în jos gama Excel nu include noua linie în PivotTable sursa. Actualizarea raportului nu va duce la o schimbare. Într-adevăr, tabelul de sinteză se bazează pe o serie de date fixe predeterminate care nu include noua linie. Un exemplu de o astfel de situație este prezentată în exemplul de fișier de date-foi și ReportData - liniile 11 și 12 nu au fost puse în raport:
O soluție la această problemă este de a adăuga rânduri noi în mijlocul intervalului - în timp ce Excel stabilească în mod corect link-ul de la sursa de date tabel pivot.
În practică, este de obicei mai convenabil și mai ușor pentru a determina în avans intervalul maxim posibil de linii sursă PivotTable adăugarea de rezervă un număr suficient de rânduri. În funcție de sarcina, puteți adăuga ca 10 sau 10.000 de linii, destul de ciudat, nu are aproape nici un efect asupra performanței PivotTable. Fișierul de schimbare Exemplul pivottable sursa de date ReportData urmează:
Această metodă este destul de potrivit pentru a fi utilizat dacă sunteți sigur că furnizarea de linii goale acoperă întotdeauna cantitatea posibilă nouă de date. Adesea, acest lucru nu poate fi garantată, mai ales în cazul în care fișierul este proiectat pentru a fi utilizat pe termen lung și mai mulți utilizatori care lucrează cu el.
Creați un stoc de linii goale din sursa de date poate fi, de asemenea, vechi tabele pivot xls-format. Aici, cu toate acestea, există un mic defect în interfața: În cazul în care coloana stochează data și datele sursă conține valori NULL, un raport de sinteză nu va permite să se aplice în acest domeniu, grupul standard de ani, trimestre, luni, etc. Noi PivotTable xlsx format nu au acest dezavantaj.
„Grafic inteligent“
Intervalul dinamic numit
Utilizatorii «avansat» Excel știu cum să folosească zone denumite foaie de lucru. Cu aceste denumiri pot fi înlocuite cu trimiteri la formule sau de a specifica sursele de date. Convențional numit game - este doar o înlocuire de link-uri lungi în coordonatele foii de lucru în numele scurt. Deci, în exemplul de pe foaia de lucru de date, puteți selecta zona A1: D12, da un nume, și apoi specificați-l ca sursa de date ReportData PivotTable.
Rezultatul va fi exact la fel ca atunci când se utilizează un interval de referință fix. Ie problema cu adăugarea de noi linii de gamă simplu nume nu rezolvă.
Un exemplu de o astfel de utilizare în numele formulei foaie numele și ReportName.
Numele de formula sursă este după cum urmează:
COUNT () și COUNTIF () sunt utilizate pentru a calcula valorile sunt în intervalul, COUNT () este utilizat pentru celule numerice, COUNTIF () - pentru text. În număr formulă calculat de șiruri non-goale pentru tipul câmpului dată (coloana A) și numărul de coloane din câmpurile de antet (rândul 1). Prin numărul de coloane adăugate la aparat, deoarece intervalul include, de asemenea, rândul antet. Rezultanta OFFSET () returnează o bandă dreptunghiulară cu deplasarea relativă a foii de lucru colțul din stânga sus (nume! $ A $ 1). În cazul în care numărul de câmpuri de date de intrare nu se schimba, în locul ultimului parametru funcția este cel mai bine pentru a specifica în mod explicit acest număr:
Prin adăugarea de câteva rânduri în jos tabelul umplut cu date brute, vă puteți asigura că tabelul de sinteză este cu succes le tratează (prin apăsarea butonului „Update“).
Intervalul dinamic numit poate fi considerat modul cel mai universal pentru a determina sursa de PivotTable, deoarece nu are nevoie de facilități speciale ( „tabelul inteligente“) și funcționează chiar și în vechea xls-format. În acest exemplu, există o cerință pentru identificarea prezenței unui câmp nevidă în intervalul sursă. De fapt, ea poate fi evitată prin utilizarea de calcule suplimentare (de exemplu, adăugarea de funcția COUNTBLANK ()), principalul lucru care a fost gasit ca rezultat al sfârșitului benzii umplut.