Spis treści

Spis treści

Redakcja

Od redakcji

Redakcja odpowiada

Problem z odejmowaniem godzin

Usuwanie danych z pominięciem formuł

Jak wyznaczyć liczbę kolumn i wierszy z zakresu arkusza

Jak szybko przygotować podsumowanie w odniesieniu< do określonych godzin

Problem z wyliczeniem daty przesuniętej o 10 dni roboczych

Tematy publikacji w pełnej wersji

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-3306-6

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: rjanus@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!

Typowym wyzwaniem podczas pracy z Excelem jest uporządkowanie danych i doprowadzenie ich do postaci, w której nadają się do właściwej obróbki. Jakość danych to pojęcie niezwykle istotne z biznesowego punktu widzenia. Niestety, tematyka ta jest podejmowana przeważnie w momencie importowania danych z zewnętrznych źródeł czy wdrażania nowego systemu raportowego, kiedy zachodzi potrzeba migracji danych pomiędzy systemami. Wtedy okazuje się, jak bardzo czasochłonne i kosztowne jest uporządkowanie firmowych baz danych. Artykuł „Analiza źródeł danych” A 064 wyjaśnia, jak zadbać o jakość danych. Pokazuje, jak budować arkusze, które wyręczą użytkownika w mozolnej, ręcznej pracy. Excel sam wyszuka wiele błędów, poprawi je lub poinformuje o wystąpieniu niepoprawnych danych.

Excel służy jednak nie tylko do analizowania informacji z przeszłości. Świetnie nadaje się również do tworzenia planów, harmonogramów i prognozowania przyszłych wyników czy zdarzeń. Nie trzeba przy tym mieć specjalistycznej wiedzy analitycznej lub statystycznej. Przydatne informacje, które pomogą w podjęciu trafnych decyzji, można uzyskać za pomocą kombinacji prostych funkcji arkuszowych. W artykule „Optymalizacja dostaw do magazynu” O 004 autor wyjaśnia, jak zbudować zestawienie do planowania dostępności zasobów.

Poza tym arkusz kalkulacyjny umożliwia nie tylko przeprowadzanie różnorodnych obliczeń, ale również prezentację wyników w estetycznych tabelach i na wykresach. W zakresie formatowania i kształtowania tabel nowsze wersje Excela oferują wiele nowych możliwości. Bez wysiłku i konieczności definiowania każdego elementu szaty graficznej arkusza z osobna, można szybko utworzyć efektowne tabele i zestawienia, jednolite pod względem kolorystycznym z wykresami i innymi elementami dokumentu. W artykule „Ujednolicanie wyglądu raportów” U 001 Czytelnicy przeczytają, czym są motywy i jak stosować je we własnych dokumentach.

Użytkownicy przyzwyczaili się, że do tworzenia formularzy wykorzystuje się edytor tekstu Word. Okazuje się, że arkusz Excela lepiej nadaje się do tego celu, ponieważ pozwala zautomatyzować wiele czynności wykonywanych przy wypełnianiu kolejnych rubryk dokumentów. Artykuł „Automatyzacja uzupełniania danych w formularzach” A 065 przedstawia, jak bez potrzeby korzystania z makr utworzyć formularz, w którym większość informacji będzie wprowadzał sam Excel.

Zapraszam do lektury

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ć!

PROBLEM Z ODEJMOWANIEM GODZIN

Każdy, kto rozlicza czas pracy w Excelu, na pewno nieraz napotkał problemy przy wykonywaniu działań na tych wartościach. Niestety, niewiele jest funkcji arkuszowych, które pozwalają sprawnie obliczać wartości czasu, szczególnie wówczas, gdy trzeba je od siebie odejmować.

Zastosowanie zwykłej różnicy może skutkować wyświetleniem znaków ###. Dlatego proponujemy korzystać z formuły, która zawsze poda poprawne wyniki, niezależnie od tego, czy odejmujesz godzinę wcześniejszą od późniejszej, czy odwrotnie.

Jeżeli wartości czasów znajdują się w kolumnach A i B:

1. Do pustej komórki kolumny C wstaw następującą formułę: =B2-A2+(A2>B2)*1

2. Skopiuj ją następnie do komórek leżących poniżej.

Uzyskamy oczekiwany efekt, jak jest to przedstawione na rysunku 1.

Rys. 1. Różnice czasów

USUWANIE DANYCH Z POMINIĘCIEM FORMUŁ

Utworzoną w Excelu tabelę chciałabym wykorzystywać jako szablon, do którego wraz z nadejściem nowego okresu mogłabym wprowadzić nowe dane. Formuły musiałyby pozostać bez zmian i warto je pozostawić nienaruszone. Jak mogę szybko wyczyścić tylko dane z poprzedniego okresu?

Aby usunąć wartości liczbowe (zakres B2:F8), pozostawiając formuły oraz nagłówki kolumn i etykiety wierszy, wykonaj następujące czynności: 1. Zaznacz wszystkie komórki, w których znajdują się dane (A1:F11). Najszybciej zrobisz to przez kliknięcie dowolnej komórki w obrębie zestawienia i potem wciśnięcie kombinacji klawiszy Ctrl + Shift + 8.

2. Wciśnij klawisz funkcyjny F5.

Rys. 1. Zestawienie okresowe

3. W oknie dialogowym, które się pojawi, wybierz przycisk Specjalnie.

4. W kolejnym oknie zaznacz opcję Stałe, a poniżej wyłącz pola wyboru: Tekst, Logiczne, Błędy.

5. Zatwierdź ustawienia, klikając przycisk OK. Uzyskasz zaznaczenie w arkuszu jak na rysunku 3.

6. Wciśnij klawisz Delete, aby usunąć zbędne wpisy.

Tak przygotowane zostawienie zapisz jako szablon lub zwykły skoroszyt i nadaj mu łatwo rozpoznawalną nazwę.

JAK WYZNACZYĆ LICZBĘ KOLUMN I WIERSZY Z ZAKRESU ARKUSZA

W Excelu skomplikowane obliczenia są najczęściej wykonywane za pomocą wielopiętrowych formuł. Czy jest jakiś sposób na uproszczenie tego typu obliczeń?

Można samodzielnie przygotować podręczny kalkulator, który na podstawie wpisanego adresu obszaru obliczy jego rozmiar w wierszach i kolumnach. W tym celu: 1. W pierwszej kolejności przygotuj szablon jak na poniższym rysunku.

2. W komórce B1 wpisz adres obszaru arkusza, np. B5:L89.

3. W komórce B2 wprowadź następującą formułę: =LICZBA.KOLUMN(ADR.POŚR(B1))

4. W komórce B3 wpisz:

=ILE.WIERSZY(ADR.POŚR(B1))

Jeśli będziesz potrzebował sprawdzić liczbę kolumn i wierszy innego obszaru, po prostu wprowadź jego adres w formacie przedstawionym na rysunku, a formuły błyskawicznie zwrócą poprawne wyniki.

JAK SZYBKO PRZYGOTOWAĆ PODSUMOWANIE W ODNIESIENIU< DO OKREŚLONYCH GODZIN

Mam arkusz zawierający zestawienie liczb (np. transakcji), które odnoszą się do określonych dni oraz godzin. Potrzebuję sprawdzić, jaka jest suma transakcji przeprowadzonych w czasie zmiany dziennej (w godzinach 8.00–20.00).

W zasadzie można taką analizę przeprowadzić na piechotę, najpierw sprawdzić czas, a następnie dodać liczby. Niestety, jeśli zestawienie będzie obejmowało dużo komórek, to stracisz mnóstwo czasu. Proponujemy wykorzystać formułę tablicową.

W celu wyznaczenia łącznej liczby transakcji w godzinach 8.00–19.59: 1. Do dowolnej pustej komórki arkusza wpisz następującą formułę: =SUMA((GODZINA(A2:A12)>=8)*(GODZINA(A2:A12)<20)*B2:B12)

2. Koniecznie zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Jej składnia zostanie ujęta w nawiasy klamrowe widoczne na pasku formuły. Nie próbuj wpisywać ich ręcznie.

Wyjaśnienie działania formuły:

W pierwszej kolejności za pomocą funkcji GODZINA sprawdzane jest, które godziny w kolumnie A są większe lub równe 8. Tworzona jest tablica wartości logicznych PRAWDA oraz FAŁSZ. W ten sam sposób będzie sprawdzany drugi warunek. Zwracana jest także tablica wartości logicznych. Dzięki zastosowaniu iloczynu pomiędzy tymi warunkami poszczególne odpowiadające sobie pozycje tablicy są porównywane. W momencie wykonywania działań arytmetycznych z użyciem wartości logicznych następuje automatyczna zamiana wartości PRAWDA na liczbę 1, a wartości FAŁSZ na 0. Jeśli więc w obu przypadkach wynikiem okaże się PRAWDA, wówczas do tablicy zbiorczej jest przekazywana liczba 1. Jeżeli przynajmniej jeden warunek zwróci FAŁSZ, wtedy do tablicy zbiorczej jest przekazywane 0. Na koniec każda z tych liczb jest przemnażana przez odpowiadający jej element tablicy wartości z kolumny B, w której znajdują się liczby sztuk sprzedanych towarów. Na tej podstawie zostanie stworzona tablica wyników końcowych, która jest sumowana (funkcja SUMA).

PROBLEM Z WYLICZENIEM DATY PRZESUNIĘTEJ O 10 DNI ROBOCZYCH

Prowadzę rejestr zamówień materiałowych w naszej firmie, gdzie zapisuję datę zamówienia, dział zamawiający i stan realizacji. Teraz dodałem do tego zestawienia datę dostawy, która powinna być o 10 dni roboczych późniejsza od daty zamówienia, ponieważ aktualna umowa przewiduje 10 dni roboczych na realizację. W jaki sposób mogę wyliczyć datę dostawy?

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

Redakcja odpowiada

Problem z odejmowaniem godzin

Usuwanie danych z pominięciem formuł

Jak wyznaczyć liczbę kolumn i wierszy z zakresu arkusza

Jak szybko przygotować podsumowanie w odniesieniu< do określonych godzin

Problem z wyliczeniem daty przesuniętej o 10 dni roboczych

Triki

Aparat fotograficzny – obrazy wykresów

Wyszukiwanie wartości

Zliczanie warunków, czyli Excel analizuje dla nas dane

Funkcje

Autofiltr – ukrywanie zbędnych informacji

Pobieranie danych ze stron WWW

Pobieranie danych z Accessa

Ochrona arkusza w Excelu

Ograniczenie wpisywania danych

Analiza źródeł danych

Automatyzacja uzupełniania danych w formularzu

Optymalizacja dostaw do magazynu

Ujednolicanie wyglądu raportów