Spójne tworzenie Excel, praca danych, usuwanie

Excel to dość potężne narzędzie, które pozwala pracować z dużymi tablicami danych, wykonując z nimi szereg operacji. Można argumentować, że tabele programu Excel to relacyjne bazy danych, z którymi można pracować bez posiadania umiejętności programisty. Ale jeśli program jest używany profesjonalnie, podstawowe funkcje nie wystarczą do generowania raportów o różnych strukturach. Do tych celów procesor TabList ma bardziej funkcjonalne i potężne tabele narzędzia - podsumowanie.

Dlaczego Excel potrzebuje tabel podsumowujących

W oryginale termin ten brzmi jak tabela obrotu, a tłumaczenie frazy jest nieco mgliste: „Tabela, którą można zmienić, przekręcić, wykazać się w różnych projekcjach”. Co to znaczy? Interpretacja danych tabelarycznych - zadanie nie jest łatwe. Zwykle odbywa się to przy użyciu formuł, otrzymując ostateczne wyniki na końcu tabeli.

Jeśli chcesz tworzyć złożone raporty za pomocą filtrów i zmiany danych źródłowych we właściwej sekwencji, zrób to za pomocą standardowych środków Excel, jeśli to możliwe, jest to bardzo trudne. Ale dzięki skonsolidowanemu tabelom takie raporty są znacznie łatwiejsze do utworzenia, jak różne diagramy, umożliwiające tylko przy pomocy myszy na grupowanie linii i kolumn, zmieniają je w miejscach, używać różnych elementów sterujących.

Większość regularnych użytkowników Excel nie jest nawet świadoma obecności tak potężnego narzędzia. Dla jasności podajemy mały i orientacyjny przykład zastosowania skonsolidowanych tabel Excel „dla manekinów”.

Załóżmy, że jesteś menedżerem firmy i jesteś odpowiedzialny za utrzymanie dokumentacji sprzedaży. Wszystkie transakcje są wprowadzane w tabeli wskazujące wszystkie szczegóły (sprzedawca, kupujący, ilość, kwota, data sprzedaży, adres kupującego itp. D.). Lider prosi o raport na temat sprzedaży według regionu.

Jak się robimy? Tworzymy układ nowej tabeli, w czapce, o której wskazujemy całą gamę sprzedawanych towarów i w wierszach - nazwy regionów. Aby to zrobić, kopiujemy z tabeli źródłowej kolumna z towarami, usuwając duplikat. Korzystając z trybu specjalnej wkładki, przeniesiemy tę kolumnę do czapki skonsolidowanej strony. Przychodzimy w ten sam sposób z regionami, ale ponieważ znajdują się one w skonsolidowanym tabeli pionowo, po prostu kopiujemy bez transportu.

W każdej komórce podsumowujemy przychody za pomocą funkcji Summail. Na koniec dodaj końcowe wyniki na dole i wyślij raport do menedżera. Lider prosi regiony o zorganizowanie u góry, a towary - na liniach. Zrobisz nowy raport w 5 minut i otrzymujesz nowe zadanie: dostarczaj dane zysków, a nie przychody, to znaczy, biorąc pod uwagę koszty. Cóż, zmieniamy formułę trochę i ponownie wynik jest gotowy w ciągu kilku minut. Wreszcie, istnieje prośba o przygotowanie takich raportów dla każdego sprzedawcy w celu oceny ich skuteczności. I to nie jest problem dla tabeli podsumowującej, jeśli wiesz, gdzie znajdują się niezbędne dane początkowe!

Tymczasem każdy użytkownik Excel wie, jak trudno jest wykonać takie manipulacje w ramach tabeli źródłowej.

Tworzenie i praca z skonsolidowanymi tabelami w programie Excel

Załóżmy, że mamy tabelę źródłową sprzedaży:

Zastanów się, jak utworzyć skonsolidowaną tabelę na podstawie tych danych tabelarycznych. Aby to zrobić, wybierz dowolną komórkę, po czym wybieramy kartę „Wstaw” w panelu głównym. Pojawią się dwie dodatkowe zakładki: „Tabela podsumowujące” i po lewej - „Zalecane tabele podsumowujące”. Jeśli jesteś początkującym i nie do końca rozumiesz, w jaki sposób możesz uporządkować dane źródłowe, zalecamy użycie drugiego przycisku. To mistrz, który zaoferuje Ci gotowe opcje raportów. Musisz tylko wybrać odpowiednią opcję i uzyskać do dyspozycji gotowy skonsolidowany stół. Pozostaje tylko po to, by ją poprawić, jeśli, zgodnie z pewnymi kryteriami, ona cię nie zadowoli.

Drugim głównym sposobem tworzenia skonsolidowanych tabel jest „od zera”, dla którego nacisrzymy pierwszy przycisk.

Otwiera się nowe okno, w którym konieczne jest ustawienie dwóch parametrów: początkowego zakresu komórki i lokalizacji ST. Ponieważ wybraliśmy komórkę przed utworzeniem tabeli obrotowej, zakres zostanie określony automatycznie, wartością domyślną dla drugiego parametru jest nowy arkusz. Więc niczego nie zmieniamy i klikamy OK. W rezultacie otworzy się nowy arkusz z niepełnym układem.

Aby go skonfigurować, użyj prawego panelu zatytułowanego „Pole tabeli podsumowującej”. Tutaj, w górnej części, wymieniono listę dostępnych pól odpowiadających kolumnie tabeli źródłowej. Możesz dodać pola na dwa sposoby: zakładając znacznik wyboru naprzeciwko pożądanej wartości, a następnie automatycznie dostanie się do układu. Ale daleko od zawsze do potrzebnego miejsca. W takich przypadkach wystarczy przeciągnąć pole do niezbędnej pozycji układu. Usunięcie pozycji w układzie jest przeprowadzane w podobny sposób, usuwając znak wybuchu lub przeciągając na prawym panelu.

Zawiera cztery regiony poniżej, na podstawie których zostanie utworzona tabela podsumowująca. Rozważ je bardziej szczegółowo:

• Obszar wartości jest główną częścią ST, ponieważ tutaj wskazano, jak przetwarzać kolumny i linie. Zwykle jest to podsumowanie - ta metoda agregacji zostanie domyślnie ustalona, ​​pod warunkiem, że wszystkie komórki zakresu zakresu akceptują wartości numeryczne. W obecności co najmniej jednej pustej komórki lub z danymi w formacie, który różni się od liczbowej, zamiast sumę wartości komórki, ich liczba zostanie obliczona, więc fakt ten należy wziąć pod uwagę. W naszym przykładzie sumowanie zostanie przeprowadzone dla każdego produktu w odpowiednim regionie. Oprócz podsumowania możesz ustawić dowolną inną metodę obliczeń, które mają około dwóch tuzinów (obliczanie średniej wartości, udziału itp.). Najłatwiejszym sposobem na to jest bezpośrednio w tabeli podsumowującej, klikając PKM dla dowolnej komórki i wybór niezbędnej metody obliczeń;
• Obszar linii - składa się z unikalnych wartości komórek znajdujących się w lewej kolumnie. Jeśli wstawisz tutaj kilka pól, tabela podsumowująca nie będzie już płaska, ale wielopoziomowa;
• Obszar kolumny zawiera również wartości pola, ale już w stosunku do górnej linii tabeli początkowej;
• Obszar filtra pozwala nałożyć ograniczenia na warunki obliczania wartości komórek. Zazwyczaj pole jest umieszczane tutaj, zgodnie z którym należy filtrować próbkę w trzech innych obszarach.

Dzięki obecności tych obszarów możesz skonfigurować próbkę z tabeli początkowej prawie każdej złożoności, spędzając kilka minut.

Aby to wyjaśnić, wyjaśniamy powyższe przy użyciu przykładu opisanego w poprzednim rozdziale.

Tak więc, krok -bo -krotność instrukcji, jak zrobić skonsolidowany tabelę w programie Excel:

• Z listy pól w tabeli podsumowującej przeciągamy pola „przychód” do obszaru wartości;
• „Region” (region kupującego) jest wciągany do linii;
• Pole „Produkt” umieszczone w obszarze kolumny.

Tylko trzy działania - i tabela podsumowująca odpowiednia do wysyłania władz, jest gotowa! Spotkanie go zajęło nam około 10 sekund.

Jak spełnić inne życzenia dyrektora?

Aby osiągnąć zysk zamiast przychodów, wystarczy umieścić odpowiednie pole w obszarze wartości zamiast przychodów, odpowiednie obliczenia w podsumowaniu zostaną automatycznie wykonane.

Drugie zadanie jest również wykonywane w sekundach - aby zmienić regiony i towary, po prostu przeciągnij je z jednego obszaru do drugiego (zmień miejsca).

Aby uzyskać wyniki dla każdego menedżera, wystarczy umieścić pole „Menedżera” w obszarze filtra, wówczas wyróżnienie pojawi się na górze, w którym musisz określić żądane nazwisko, aby otrzymać raport. Istnieje możliwość połączenia raportu o kilku sprzedawców.

Oczywiście nie przyniosliśmy najtrudniejszego, ale bardzo orientacyjnego przykładu, który pozwala zrozumieć, jak tworzyć tabele podsumowujące w programie Excel. Metoda obliczania wartości w komórkach może być bardziej skomplikowana, może być konieczne użycie obliczonych pól, a także zastosowanie formatowania warunkowego - możliwości w tym zakresie w programie Excel są imponujące.

O źródle danych

Wspominaliśmy już, że pewne wymagania są nakładane na początkowe dane używane do prawidłowego utworzenia skonsolidowanych tabel w programie Excel. Wymieńmy je:

• Główne ograniczenie wiąże się z obowiązkową obecnością nazwisk nad kolumnami zaangażowanymi w obliczenia. Takie identyfikatory są niezbędne do tworzenia wynikowych raportów - przy dodawaniu nowych rekordów (ciągów) do tabeli początkowej format nie jest konieczny do zmiany formatu ST, a wyniki zostaną ponownie obliczone automatycznie;
• Upewnij się, że w komórkach linii i kolumn uczestniczących w próbce wprowadzono parametry numeryczne. Jeśli są puste lub zawierają wartości tekstowe, linie te spadną z obliczeń, które zniekształcą wyniki obliczeń;
• Postępuj zgodnie z korespondencją formatów linii i zawartości komórek. Jeśli jest zdefiniowany jako data, wszystkie wartości w kolumnie muszą mieć ten sam format, w przeciwnym razie filtrowanie i przeliczenie się będą błędne.

Jeśli przestrzegasz tych zasad, nie powinno się pojawić konstrukcji problemów z tabelą podsumowującym.

Aktualizacja danych w tabeli zestawu Excel

Chociaż rozmawialiśmy o automatycznym przeliczeniu skonsolidowanych tabel podczas wprowadzania danych źródłowych lub strukturze samego ST, w rzeczywistości ze względu na oszczędzanie zasobów komputerowych, musisz wykonać określone działania.

Możesz zaktualizować dane w naszej tabeli ekspozycji Excel na dwa sposoby:

• Kliknięcie prawym przyciskiem myszy na dowolnej komórce i wybór elementu „Aktualizacja” w menu kontekstowym;
• Korzystanie z zakładki „Data” i przycisku „Aktualizuj wszystko”.

Buforowanie tabel jest szczególnie uzasadnione, jeśli są one duże, w tym przypadku prędkość programu znacznie wzrośnie, zwłaszcza jeśli regularnie wprowadzasz nowe linie i przy wysokiej częstotliwości.

Dodanie kolumny lub linii do programu Excel

Ogólnie rzecz biorąc, operacja jest trywialna, jeśli pożądany parametr znajduje się w tabeli źródłowej. Musisz tylko przeciągnąć prawe pole w odpowiedni obszar. Jeśli w tabeli początkowej nie ma ciągu ani kolumny, dodaj go, wprowadź, jeśli to konieczne, wartości. Następnie kliknij kartę „Analiza” i otwórz naszą tabelę zmienioną. Pozostaje tylko aktualizacja danych (patrz. W rezultacie lista pól zmieni się w prawym panelu ST - do niego zostanie dodany nowy parametr.

Często tabela podsumowująca nie ma dość wygodnych gatunków do wizualizacji. Na przykład, gdy w liniach jest kilka pól. Wówczas jeden z nich jest wyświetlany w całości, a wartości drugiego muszą być wskazane w górnej części tabeli. Jeśli istnieje niewiele takich wartości (powiedzmy, w naszym przykładzie regionów sprzedaży tylko 6), warto dodać kolumnę „regiony” tabeli obrotowej do odpowiedniego obszaru, przenosząc ją z obszaru linie.

Zmiana procedury podążania za kolumnami jest również łatwa do osiągnięcia, po prostu przeniesienie kolumny na kierunku do żądanej pozycji za pomocą myszy.

Utworzenie tabeli podsumowującej z kilku plików (tabele) Excel

To bardziej skomplikowane, ale całkiem kompletne zadanie. Użyj Master ST, który należy dodać do panelu szybkiego dostępu.

Po lewej stronie, w pobliżu elementu „Plik”, kliknij skierowaną strzałkę, wybierz element „Inne polecenia”. Nowe okno zostanie otwarte, w środkowym panelu klikamy opcje „wszystkie zespoły”. Na liście „Master of Consolidated Tabele” znajduje się na liście, która otwiera się, wybierz ją i kliknij przycisk „Dodaj”. Odpowiednia ikona pojawi się w górnym panelu.

Uruchamiamy mistrza. Załóżmy, że musimy połączyć dane z dwóch arkuszy, maja i w czerwcu. Głównym warunkiem jest to, że muszą się pokryć strukturą. Jeśli tak, to w pierwszym oknie Master wybieramy opcję „Tabela substancji” i następny parametr - „Kilka zakresów konsolidacji”.

Na drugim kroku wskazujemy Mistrza, że ​​potrzebujemy kilku pól. Na trzecim - wybierz zakres komórek do podsumowania pierwszego arkusza, kliknij „Dodaj” i zrób to samo dla drugiego liścia Excel. Teraz musisz podać nazwy dwóch pól. Robimy to najpierw dla pierwszego pola (kliknij selektor „1”, w polu kolumny „1” Wprowadzamy „może”), a następnie, analogicznie, dla drugiego. W takim przypadku konieczne będzie wskazanie na liście zakresów odpowiedniej linii, pierwszego lub drugiego.

Po kliknięciu przycisku „Gotowy” otwiera się nowe okno, w którym wskazujemy „nowy liść” i ponownie naciśnij przycisk „Ready”.

UWAGA. Otrzymanie tabel wielowymiarowych jest niewygodne, ponieważ trudno jest nawigować z dużą liczbą pól, a konieczne obliczenia należy przeprowadzić, za każdym razem wskazując niezbędne wartości z górnego obszaru tabeli.

Jak usunąć sztukę

Najłatwiejszy przypadek - kiedy utworzyłeś skonsolidowany tabelę, wysłałeś wyniki do szefa kuchni i już go nie potrzebujesz. Jeśli masz pewność, po prostu wybierz tabelę i kliknij klawisz Usuń. Proste i skutecznie.

Ale nagle w przyszłości może być potrzebna struktura stołu? Excel ma możliwość usunięcia tylko wyników lub danych z komórek. Zastanów się, jak to się robi.

Aby usunąć wyniki obliczeń, wykonujemy następujące kroki:

• Oznaczamy dowolną komórkę ST;
• Wybierz kartę „Analiza” na górnym panelu;
• Z listy dostępnych działań wybierz „Wyczyść”;
• Kliknij punkt „Wyczyść wszystko”.

Ale co zrobić, jeśli chcesz zapisać wyniki, ale sam nie potrzebujesz danych, to znaczy, chcesz uwolnić tabelę? Ta sytuacja często pojawia się, jeśli przywództwo potrzebuje tylko wyników. Algorytm działań:

• Ponownie wybierz dowolną komórkę, kliknij kartę „Analiza”;
• Wybierz element menu „Action”, kliknij „Wybierz”, zaznacz całą skonsolidowaną tabelę za pomocą myszy;
• Kliknij PCM wewnątrz wybranego obszaru;
• Z menu kontekstowego wybierz element „Kopiuj”;
• Przechodzimy do zakładki „Home”, ponownie kliknij PKM i wybieramy „Wstaw”;
• Wybierz kartę „Wstaw wartość”, w nim odnotowujemy parametr „Wstaw jako wartość”.

W rezultacie tabela podsumowująca zostanie wymazana przy zachowaniu wyników.

RADA. Możesz przyspieszyć procedurę za pomocą kombinacji kluczowej. Aby wybrać tabelę, użyj Ctrl + A, do kopiowania - Ctrl + C. Następnie naciśnij Alt + E, Alt + S, Alt + V i uzupełnij procedurę, naciskając Enter.

Aby usunąć skonsolidowane tabele w programie Excel 2007/2010, musisz użyć innego algorytmu:

• Wybierz ST zaprojektowany do usuwania;
• Kliknij kartę „Parametry”;
• W „Grupie Działań” wybierz element „Clean”, a następnie - „Wyczyść wszystko”.

Jeśli twój szef uwielbia wizualizację danych, oczywiste jest, że będziesz musiał użyć skonsolidowanych diagramów. Ponieważ zajmują dużo miejsca w stole, po użyciu zwykle są usuwane.

W starych wersjach programu musisz wybrać schemat, kliknij kartę „Analiza”, wybierz grupę danych i kliknij sekwencyjnie „Clean” i „Oczyść wszystko”.

Ponadto, jeśli schemat jest powiązany z najbardziej podsumowującą tabelą, po jego usunięciu stracisz wszystkie ustawienia tabeli, pola i formatowanie.

W przypadku wersji starszych niż Excel 2010 musisz wybrać schemat, wybrać „Działania” na karcie „Analysis” i kliknąć „Clean” i „Clean Everything”. Wynik będzie podobny.

Mamy nadzieję, że nasze lekcje na skonsolidowanych tabelach pozwolą ci odkryć tę wystarczająco potężną funkcjonalność. Jeśli nadal masz pytania, zadaj je w komentarzach, postaramy się na nie odpowiedzieć.