196

Tworzenie tabel przestawnych za pomocą makr

Excela umożliwia gromadzenie, przechowywanie i obróbkę olbrzymich ilości danych. Jednak z pewnością wiesz już, że dane to nie wszystko. Ważna jest umiejętność i możliwość takiego grupowania wartości, aby móc wyciągnąć z nich jakąś informację, posiąść wiedzę. Właśnie do takich celów wyśmienicie nadają się tabele przestawne. A jeśli do tworzenia tabel przestawnych użyjemy makr, otrzymamy ogromne możliwości.

Przenoszenie danych z arkusza do dokumentu

Dość często zdarza się, że zestawienia analityczne lub wykresy przygotowane w arkuszu powinny się znaleźć w ogólnym raporcie i stanowić uzupełnienie jego części opisowej. Trzeba zatem w dokumencie Worda zawierającym tekst osadzić tabele lub obiekty graficzne. Okazuje się, że wykorzystanie do tego celu standardowej operacji Kopiuj/Wklej nie zawsze jest wskazane lub ogranicza pewne funkcje umożliwiające łatwiejszą pracę z dokumentem. Z tego artykułu dowiesz się, jak poprawnie przenosić dane z Excela do Worda.

Operacje logiczne w formatowaniu warunkowym

Za pomocą narzędzia formatowania warunkowego możesz nadawać formaty komórkom lub całym zakresom na podstawie zdefiniowanych przez Ciebie kryteriów. Excel udostępnia mnóstwo predefiniowanych ustawień formatowania, których stosowanie nie wymaga od Ciebie w zasadzie żadnej wiedzy dotyczącej operacji logicznych. Niestety, chcąc zastosować bardziej zaawansowaną regułę formatowania, wówczas gotowe opcje mogą być niewystarczające i będziesz zmuszony od podstaw zbudować własny warunek, opierając się na formule wykorzystującej wartości logiczne.

195

Zaawansowane przykłady wykorzystania formuł warunkowych

 W tym artykule przedstawiamy kilka praktycznych przykładów użycia formuł warunkowych w praktycznych zastosowaniach, z jakimi może spotkać się użytkownika Excela. Omawiamy możliwości wykorzystania sumowania warunkowego. Pokazujemy, jak automatycznie rozliczać na bilingu prywatne połączenia pracowników. Na koniec wyjaśniamy, w jakiś sposób wyróżnić w zestawieniach skrajne wartości.

Tabele przestawne - triki

Tabele przestawne są znakomitym narzędziem Excela, umożliwiającym przeprowadzenie wygodnej analizy nawet niezwykle obszernych zakresów danych. Jeśli opanujesz metody dostosowywania danych i tabel przestawnych do swoich potrzeb, istotnie zwiększysz efektywność pracy. Skupisz bowiem uwagę na wybranej grupie danych, a nie całej tabeli.

194

Warunki logiczne w formułach tablicowych

Warunki w formułach tablicowych tworzy się podobnie jak w zwykłych formułach. Konieczne jest utworzenie działania logicznego, które w wyniku zwróci wartości logiczne PRAWDA/FAŁSZ lub odpowiednio jedynki i zera. Różnica polega jednak na tym, że w formule tablicowej takich sprawdzeń występuje bardzo wiele, ponieważ operuje na rozległych zakresach danych. Używa się zatem równań z operatorami porównawczymi, które opierają się na zakresach komórek (np. A1:A5>0), a nie na pojedynczych komórkach (np. A1>0). Zadaniem użytkownika jest przekazanie wielu wyników cząstkowych przeznaczonych do dalszych obliczeń.

Tworzenie zaawansowanych formuł obliczeniowych

Niektóre tabele, z którymi mamy do czynienia, nie zawsze mają układ listy i analizowanie ich za pomocą prostych formuł obliczeniowych jest utrudnione. Szef nie będzie chciał słuchać tłumaczeń, że dane były niepoprawnie przygotowane. Ważny jest efekt końcowy, czyli właściwie przeprowadzone obliczenia. Nasza w tym głowa, jak je wykonamy. W takich sytuacjach musimy korzystać z niestandardowych rozwiązań.

193

Wykres prognozy z oznaczeniem marginesu błędu

W trakcie przeprowadzania różnego rodzaju prognoz lub badań statystycznych prawie zawsze przyjmuje się określony margines błędu. W przypadku zestawień liczbowych bardzo łatwo możemy go uwzględnić dzięki zastosowaniu odpowiednich formuł. Problemy pojawiają się wówczas, gdy dopuszczalne wartości błędu chcemy zaznaczyć na wykresie. Excel jest przygotowany do budowania takich prezentacji. Aby dowiedzieć się, jak je tworzyć, warto skorzystać z tego hasła. Znajdują się w nim także odpowiedzi na następujące pytania:

Ukrywanie danych w tabeli przestawnej

Jeżeli zamierzasz budować wielowariantowe raporty, to tabela przestawna okaże się najlepszym narzędziem do tego celu. Niestety, stanowi specyficzny zakres arkusza i przez to zmiana struktury danych jest utrudniona. Okazuje się, że są sposoby na sprytne omijanie ograniczeń dotyczących układu raportu przestawnego. Z tego artykułu dowiesz się, jak zmienić zakres danych tabeli przestawnej i jak za pomocą makra filtrować dane w raporcie przestawnym.

192

Raporty produkcyjne – grupowanie pól tabeli przestawnej

Tabela przestawna umożliwia wykonanie takiej operacji bez konieczności dodawania pomocniczych kolumn w zestawieniu, z którego jest budowana. Wykonuje się je przy użyciu opcji grupowania pól tabeli przestawnej. Przykładem będą raporty firmy zajmującej się produkcją i sprzedażą farb różnego przeznaczenia. Asortyment produktów firmy dzieli się na pięć różnych grup ze względu na ich zastosowanie. Są to: farby do fasad, farby do podłóg, farby do ścian i sufitów, farby do ogrodzeń oraz farby do kuchni i łazienek. Każda grupa zawiera 25 kolorów oraz maksymalnie 5 różnych pojemności. Daje to ogromny asortyment produktów (375 różnych wyrobów). Z systemu produkcyjnego możliwy do pobrania jest zestaw danych za 2013 rok, jak na rysunku 1.

Planowanie budżetu z użyciem narzędzia Tabela danych

Budżet firmy na kolejny rok powstaje we współpracy z różnymi działami przedsiębiorstwa, np. sprzedaży, produkcji, zasobów ludzkich itd. Jest to długotrwały i skomplikowany proces, ponieważ planowaniu podlega większość z obszarów działalności danej organizacji. Wynikiem takiej wewnętrznej kooperacji jest plan funkcjonowania firmy na przyszły okres (przeważnie roku), wyznaczający cele w wielu aspektach i dziedzinach funkcjonowania jednostki. W tym artykule przyjrzymy się firmie dystrybucyjnej obsługującej około 80 klientów, której asortyment towarów liczy w przybliżeniu 120 pozycji. Na jej przykładzie pokażemy, jak w prosty i szybki sposób wyliczyć wszystkie warianty modelu dla kombinacji dwóch parametrów przyjmujących kilkanaście różnych wartości (łącznie 135 kombinacji) .

191

Wizualizacja wyników sprzedażowych za pomocą tabeli przestawnej

Często użytkowników nie stać na zakup drogich programów Business Intelligence przeznaczonych do analizy dużych zbiorów danych w różnych układach i o odmiennym poziomie szczegółowości. Jednak od pracowników zajmujących się analizą wymagane są zestawienia i raporty pokazujące dane w różnych układach, często będące wynikiem analiz opracowanych na podstawie tysięcy rekordów.

Unikanie błędnych zaokrągleń

Arkusz kalkulacyjny Excel pozwala przeprowadzić obliczenia nie tylko szybko, ale i bardzo dokładnie. Paradoksalnie właśnie duża dokładność obliczeń może mieć czasem fatalne skutki i być przyczyną błędów w zestawieniach czy różnych innych dokumentach tworzonych w arkuszu kalkulacyjnym. Zdarza się to wówczas, gdy użytkownik programu nie ma odpowiedniej wiedzy na temat sposobu przeprowadzania obliczeń na wartościach zaokrąglanych.