Redakcja

Redaktor prowadzący

Rafał Janus

Wydawca

Monika Kijok

Opracowanie graficzne okładki

Małgorzata Piaskowska

Opracowanie graficzne

Zbigniew Korzański

Koordynator produkcji

Mariusz Jezierski

Korekta

Zespół

ISBN 978–83–269–3150-5

Nakład: 1000 egz.

Wydawnictwo Wiedza i Praktyka sp. z .o.o.

03–918 Warszawa, ul. Łotewska 9a

Tel. 22 518 29 29, faks 22 617 60 10, e-mail: pgromulski@wip.pl

NIP: 526–19–92–256

Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy

XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł

Skład i łamanie: Triograf Dariusz Kołacz

Miller Druk Sp. z o. o., 03–301 Warszawa, ul. Jagiellońska 82, tel.: 22 614 17 67

Copyright © by Wydawnictwo Wiedza i Praktyka sp. z o.o. Warszawa 2014

Poradnik „Excel w praktyce” wraz z przysługującym Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w poradniku „Excel w praktyce” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło.

Poradnik „Excel w praktyce” został przygotowany z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifikacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Excel w praktyce” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako oficjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Excel w praktyce” lub w innych dostępntych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przykładów.

Wydawca nie odpowiada za treść zamieszczonej reklamy; ma prawo odmówić zamieszczenia reklamy, jeżeli jej treść lub forma są sprzeczne z linią programową bądź charakterem publikacji oraz interesem Wydawnictwa Wiedza i Praktyka.

Od Redakcji

Drodzy Czytelnicy!

Celem analizy danych sprzedażowych może być stworzenie zestawień o różnych strukturach i o różnej dokładności. Dodatkowym wymaganiem stawianym przed osobą wykonującą takie zadanie jest możliwość szybkiej modyfikacji zestawień wg określonych wytycznych oraz możliwość szybkiej aktualizacji danych w nich zawartych. Kwestia rzetelności oraz poprawności wyników jest oczywista. Artykuł Zastosowanie tabel przestawnych do analizy dużych zbiorów danych sprzedażowych opisuje narzędzie do takich właśnie zadań. Omawiany w nim przykład przedstawia analizę całorocznej sprzedaży (ilościowej i wartościowej) fikcyjnej firmy oraz wyznaczenie na jej podstawie kierunku rozwoju spółki na rok następny.

Często potrzebujemy posortować dane, znajdujące się dwóch arkuszach. Zadanie z reguły utrudnia fakt, że niektóre dane się powtarzają. Jeśli zmagasz się z takim problemem, zajrzyj do hasła Automatyczne sortowanie danych z dwóch arkuszy wg numeru. Dowiesz się z niego, w jaki sposób uporać się z tym problemem oraz poznasz formuły, które wykonają takie narzędzie. Podpowiadany także, w jaki sposób korzystać z formuł tablicowych i jak utworzyć formatowanie warunkowe, które będzie automatycznie wstawiało obramowanie w przypadku pojawienia się następnych danych.

Jeśli potrzebujesz sporządzić raport, zawierający tabele z danymi źródłowymi oraz wykresy, warto zarezerwować dużo miejsca w arkuszu. Jeżeli dodatkowo chcesz mieć te dane w wersji drukowanej, to prawdopodobnie zużyjesz także sporo kartek papieru. Można jednak zoptymalizować raport i zastosować w nim zamiast pełnowymiarowych wykresów minidiagramy. Jeśli nie wiesz, jak się do tego zabrać, zachęcam do lektury artykułu Prezentacja graficzna – miniwykresy pozwolą zwizualizować wyniki cząstkowe analiz.

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żesz go uwzględnić dzięki zastosowaniu odpowiednich formuł. Problemy pojawiają się wówczas, gdy dopuszczalne wartości błędu chcesz zaznaczyć na wykresie. Excel jest przygotowany do budowania takich prezentacji. Aby dowiedzieć się, jak je tworzyć, warto zajrzeć do artykułu Oznaczanie marginesu błędu na wykresach prognoz. Znajdują się w nim także opisy, jak ustalić wielkość błędu indywidualnie dla każdego punktu danych oraz jak określić stały zakres błędów niezależnie od wartości punktu serii danych.

Zapraszam do lektury

Rafał Janus

redaktor prowadzący poradnika „Excel w praktyce”(rjanus@wip.pl)

Redakcja odpowiada

Sprawdźmy, z jakimi problemami borykają się inni użytkownicy Excela. Te rozwiązania mogą się przydać!

Jak zmodyfikować identyfikatory, aby wszystkie miały identyczną długość?

W bazie mamy identyfikatory o różnej długości, które powinny składać się z dziewięciu znaków. Krótsze identyfikatory powinny mieć odpowiednią liczbę zer na początku, aby wyrównać do dziewięciu liczb. Niestety, Excel, formatując liczby, automatycznie usuwa zera przed wartościami.

Rys. 1. Problem z długością identyfikatora

Te brakujące zera można łatwo uzupełnić na dwa sposoby. Pierwszy polega na zmianie formatowania wartości. Do kolumny E skopiujmy wartość z kolumny A. Za pomocą skrótu klawiaturowego [Ctrl]+[1] przywołamy okno formatowania komórek, w którym należy przejść do kategorii Niestandardowe.

Rys. 2. Formatowanie niestandardowe

W polu Typ usuńmy domyślną wartość Standardowy. Identyfikator składa się maksymalnie z dziewięciu cyfr, dlatego w to miejsce należy wstawić dziewięć zer.

Rys. 3. Niestandardowy format komórki

Dzięki tej operacji Excel sam sprawdzi długość identyfikatora, jeżeli jego długość będzie mniejsza od dziewięciu, na początku komórki zostaną uzupełnione o właściwą liczbę zer. Rysunek 4 przedstawia efekt.

Sposób drugi polega na uzupełnieniu brakujących zer z wykorzystaniem formuły. Do tego celu posłużą trzy funkcje:

JEŻELI,

DŁ (funkcja długość),

POWT (funkcja powtarzaj).

Rys. 4. Efekt formatowania niestandardowego

Najpierw musimy zaprogramować formułę, aby wiedziała, kiedy należy wstawić zera. W naszym przypadku nastąpi to po stwierdzeniu, że pole z identyfikatorem jest krótsze niż dziewięć znaków. Do sprawdzenia liczby znaków w komórce posłuży funkcja DŁ.

Aby Excel wiedział, kiedy dokładać brakujące zera, musimy w formule użyć funkcji JEŻELI. Za pomocą tej funkcji przekażemy informację, aby zostały uzupełnione tylko te identyfikatory, które mają mniej niż dziewięć znaków. Natomiast identyfikatory z dziewięcioma znakami pozostaną w niezmienionej formie.

Jeśli identyfikator ma mniej niż dziewięć znaków, Excel musi dokładnie wiedzieć, ile zer ma wstawić, dopełniając wartość komórki z identyfikatorem. Żeby przekazać taką informację, posłużymy się funkcją POWT oraz funkcją DŁ jako jednym z jej argumentów. Funkcja POWT służy do powielania określonych znaków i używa dwóch argumentów:

1. Teskt – tekst, który ma być powtarzany. W naszym przypadku będzie to zero zapisane pomiędzy dwoma cudzysłowami (0).

2. Ile_razy – ile razy wartość pierwszego argumentu ma zostać powtórzona. W naszym przypadku wartość ta jest dynamiczna i dla każdego identyfikatora może być inna. Jeden identyfikator składa się z jednej cyfry, kolejny z pięciu, a jeszcze inny z dziewięciu cyfr.

Wiemy natomiast, że powinien się składać z dziewięciu znaków. Wiemy też, ile znaków jest w danej komórce (to mówi wynik funkcji DŁ). Jeżeli od dziewięciu, bo tyle musi być w komórce znaków, odejmiemy liczbę znaków w danej komórce (w kolumnie A), otrzymamy liczbę zer, o jaką Excel powinien uzupełnić identyfikator.

Poskładajmy teraz naszą formułę razem i zaprogramujmy Excela, aby w zależności od długości identyfikatora uzupełniał wynikowe komórki odpowiednią liczbą zer.

Opis słowny formuły: jeżeli długość komórki z identyfikatorem (kolumna A) jest równa dziewięć (wymagana długość), to weź całą wartość komórki z kolumny A. Jeżeli natomiast długość komórki jest różna od dziewięciu, to dopełnij wartość komórki wynikowej zerami odpowiednią liczbę razy. Aby Excel wiedział, ile zer ma wstawić, niech przez odejmowanie od wymaganej liczby znaków odejmie obecną długość identyfikatora.

Zapis formuły:

=JEŻELI(DŁ(A2)=9;A2;POWT(„0”;9-DŁ(A2))&A2)

Rys. 5. Wynik działania formuły uzupełniającej identyfikator

Zwróćmy uwagę na znak konkatenacji (&), który zastępuje funkcję Złącz.teksty. W tym przypadku ma on za zadanie połączyć dopisywane zera z pierwotną wersją identyfikatora.

Zwróćmy uwagę na jeszcze jedną rzecz. Identyfikator w kolumnie A był równany do prawej strony, natomiast w wyniku działania formuły wartości identyfikatora automatycznie zostały wyrównane do lewej strony. Stało się tak dlatego, że nowy identyfikator w kolumnie I zmienił typ danych i stał się tekstem. Jest to domyślne działanie Excela względem tekstu. Naturalnie można zmienić równanie do prawej za pomocą przycisku wyrównaj tekst do prawej.

Co zrobić, gdy Excel zaznacza za dużo komórek

Zdarzyło mi się kilka razy zaobserwować, że program Excel dziwnie się zachowuje. Nie znalazłem żadnych powodów, dlaczego tak się dzieje. Po kliknięciu dowolnej komórki zaznaczony zostaje obszar znajdujący się pomiędzy komórką aktywną a klikniętą komórką, np. jeśli poprzednio była aktywna komórka G3, a zaznaczyłem komórkę B8, to zaznaczony został obszar B3:G8. Po zaznaczeniu innej komórki znowu jest zaznaczany obszar między aktywną komórką G3 a klikniętą komórką. Po zamknięciu programu Excel i ponownym otwarciu pliku jest wszystko OK. Takie dziwne zachowanie zdarzyło się ostatnio kilka razy. Czy to jest jakiś błąd Excela i w jaki sposób można to naprawić?

Rys. 1. Widok arkusza przed zaznaczeniem komórki

Rys. 2. Widok arkusza po zaznaczeniu komórki B8

Opis zachowania Excela przedstawiony w tym liście wskazuje na to, że program Excel wszedł przypadkowo w Tryb rozszerzenia. Mogło to być spowodowane np. przypadkowym wciśnięciem klawisza F8. Tryb rozszerzenia powoduje, że zaznaczony obszar musi zawierać aktywną komórkę. W tym trybie możesz zmienić aktywną komórkę za pomocą klawiszy Tab, Enter lub kombinacji klawiszy Shift + Tab i Shift + Enter. Ten tryb zaznaczania komórek przewidziany jest dla użytkowników, którzy nie stosują myszki, i ułatwia im zaznaczanie wielu komórek za pomocą klawiatury. W tym trybie możesz zmieniać zaznaczony obszar komórek z użyciem klawiszy strzałek.

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

Redakcja odpowiada

Sprawdźmy, z jakimi problemami borykają się inni użytkownicy Excela. Te rozwiązania mogą się przydać!

Jak zmodyfikować identyfikatory, aby wszystkie miały identyczną długość?

Co zrobić, gdy Excel zaznacza za dużo komórek

Jak przygotować czytelną prezentację przedziałów czasowych

Triki

Używanie filtra zaawansowanego do filtrowania danych na podstawie wielu kryteriów

Porównywanie zawartości dwóch komórek

Automatyczne sortowanie danych z dwóch arkuszy wg numeru

Analiza danych – podział tabeli na zakresy przyspiesza obliczenia

Oznaczenie marginesu błędu na wykresach prognoz

Zastosowanie tabel przestawnych do analizy dużych zbiorów danych sprzedażowych