Redakcja

Autor:

Jakub Kudliński

Kierownik grupy wydawniczej:

Agnieszka Konopacka-Kuramochi

Wydawca:

Weronika Wota

Redaktor prowadzący:

Rafał Janus

Korekta:

Zespół

Skład i łamanie:

Norbert Bogajczyk

Projekt okładki:

Piotr Fedorczyk

Druk: Miller

ISBN: 978-83-269-4509-0

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

Warszawa 2016

Wydawnictwo Wiedza i Praktyka sp. z o.o.

03-918 Warszawa, ul. Łotewska 9a

tel. 22 518 29 29, faks 22 617 60 10

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ł

„52 najlepsze triki w Excelu, czyli jak szybciej wykonać obliczenia” wraz z przysługującymi Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w książce „52 najlepsze triki w Excelu, czyli jak szybciej wykonać obliczenia” 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.

Publikacja „52 najlepsze triki w Excelu, czyli jak szybciej wykonać obliczenia” została przygotowana z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifikacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „52 najlepsze triki w Excelu, czyli jak szybciej wykonać obliczenia” 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 „52 najlepsze triki w Excelu, czyli jak szybciej wykonać obliczenia” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przypadków.

Wstęp

Excel to arkusz kalkulacyjny – ta nazwa wskazuje, że u podstaw jego, niekiedy bardzo zaawansowanych, możliwości leżą obliczenia. Celem tej książki jest przedstawienie zasad rządzących obliczeniami w tym programie oraz pokazanie wielu przydatnych trików, dzięki którym można sprawnie wykonać wiele codziennych czynności.

Dowiesz się, jak obliczyć średnią ważoną, zobaczysz, jakie problemy mogą sprawiać zaokrąglenia i jak je rozwiązać oraz jak w praktyce wykorzystać odwołania cykliczne, które przy standardowych ustawieniach Excela powodują komunikat o błędzie. Wszystkie triki są omówione krok po kroku i wzbogacone zrzutami ekranowymi.

Bardzo przydatne są triki dotyczące operacji na wartościach czasu, które wielu użytkownikom sprawiają problemy. Dowiesz się, w jaki sposób odejmować wartości czasu, podsumowywać czas pracy, wyznaczać określone daty i wiele innych.

Książka pokazuje również sposoby, jak sprawnie kontrolować obliczenia przeprowadzane za pomocą formuł. W kilku trikach zawarte są wskazówki, jak szybko zaznaczyć wszystkie formuły w arkuszu, wyświetlać w komórkach wartości obliczeń i formuły oraz jak sprawdzić powiązania między komórkami wykorzystywanymi w obliczeniach.

Znajdujące się na końcu triki wprowadzają w świat obliczeń statystycznych. Tematyka ta została przedstawiona w bardzo przystępny sposób i na praktycznych przykładach. Dowiesz się, jak obliczyć skumulowane sumy wartości zamówień oraz przyporządkujesz dane do zdefiniowanych zakresów.

Trik 1. Formuła zwracająca wartość z losowej komórki

Przyjmijmy, że po zakończonym konkursie chcesz wylosować zwycięzcę. Mamy zgromadzone w arkuszu 500 identyfikatorów osób, które zgłosiły się do zabawy. Zamiast drukować te numery, wycinać i wrzucać do szklanej kuli, wykorzystaj sprytną formułę Excela.

Rys. 1. Fragment kolumny zawierającej 500 identyfikatorów

Formuła będzie wykorzystywać funkcję wyszukującą INDEKS oraz funkcje LOS.ZAKRS, której zadaniem jest zwrócenie losowej wartości z przedziału ograniczonego dwiema liczbami całkowitymi.

Rys. 2. Identyfikator z losowej komórki

Aby odszukać identyfikator zwycięzcy konkursu, do dowolnej pustej komórki wstaw następującą formułę:

=INDEKS(A1:A500;LOS.ZAKR(1;500))

Przy każdym przeliczeniu arkusza formuła będzie losowała nowy identyfikator. Zalecamy zatem utrwalić zwrócony wynik lub wyłączyć automatyczne przeliczanie arkusza w oknie opcji Excela.

Trik 2. Automatyczne kontrolowanie kwot wpisywanych do arkusza

Przy wprowadzaniu do arkusza składników procentowych dość często pojawia się problem przekroczenia wartości 100%. Może to wynikać z nieodpowiednich zaokrągleń lub zwyczajnej nieuwagi osoby wypełniającej komórki danymi liczbowymi. Aby się przed tym zabezpieczyć (a także w każdej innej sytuacji, kiedy zakres dopuszczalnych wartości w danej komórce jest ograniczony), proponujemy zastosować bardzo przyjazne w obsłudze narzędzie Sprawdzanie poprawności danych.

Rys. 3. Przykładowe składniki procentowe

W tym celu:

1. Zaznacz odpowiedni zakres komórek, począwszy od B4.

2. Otwórz kartę Dane i w grupie poleceń Narzędzia danych wskaż Poprawność danych.

3. W wyświetlonym oknie dialogowym przejdź do zakładki Ustawienia.

4. Z rozwijanej listy Dozwolone wybierz pozycję Niestandardowe.

5. W pole poniżej wpisz następującą formułę:

=SUMA($B$4:$B$15)<=$E$3

Rys. 4. Ustawienia sprawdzania poprawności

6. Zatwierdź, klikając OK.

Od tego momentu, jeśli suma udziałów procentowych przekroczy wartość z komórki E3 (100%), wówczas pojawi się komunikat ostrzegawczy o wprowadzeniu niedozwolonej wartości.

Rys. 5. Suma składników przekroczyła 100%

Trik 3. Odwołania cykliczne (pętle) w formułach

Odwołanie cykliczne występuje, jeśli w formule jest podane niepoprawne odwołanie do komórek z danymi. Taka sytuacja najczęściej jest niepożądana i traktowana jako błąd w obliczeniach. Okazuje się, że wystąpienie odwołania cyklicznego można wykorzystać do własnych celów przy analizowaniu danych czy wykonywaniu obliczeń.

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

Trik 1. Formuła zwracająca wartość z losowej komórki

Trik 2. Automatyczne kontrolowanie kwot wpisywanych do arkusza

Trik 3. Odwołania cykliczne (pętle) w formułach

Trik 4. Wygodne obliczenia z użyciem nazw zakresów

Trik 5. Obliczanie średniej ważonej

Trik 6. Wyszukiwanie wartości optymalnie dopasowanej do potrzeb

Trik 7. Wyznaczenie adresu komórki z wartością skrajną

Trik 8. Obliczanie rzeczywistej k-tej najmniejszej wartości w tabeli

Trik 9. Odrzucenie wartości skrajnych przy wyliczaniu średniej

Trik 10. Obliczanie sumy kilku najmniejszych i największych wartości

Trik 11. Obliczenia opłacalności (przyszłej wartości) inwestycji

Trik 12. Zliczanie transakcji za pomocą formuły tablicowej

Trik 13. Szybka analiza wyników ankiety

Trik 14. Warunki LUB i ORAZ w formułach tablicowych

Trik 15. Pierwsze wystąpienie wartości minimalnej

Trik 16. Obliczenia statystyczne i funkcje tekstowe

Trik 17. Wyszukanie wartości najmniejszej, ale większej od zadanej

Trik 18. Wprowadzanie ułamków zwykłych

Trik 19. Obliczanie progu rentowności

Trik 20. Sumowanie warunkowe z wieloma kryteriami

Trik 21. Plan oszczędzania

Trik 22. Wartość pieniądza w czasie

Trik 23. Wymagany okres oszczędzania

Trik 24. Sumowanie narastająco z pominięciem błędów

Trik 25. Metody liczenia częstości wystąpień

Trik 26. Obliczanie średniej z pominięciem wartości zerowych

Trik 27. Przypisanie wartości do kategorii

Trik 28. Analiza wystąpień określonych wartości

Trik 29. Kontrolowanie poprawności obliczeń – zaznaczanie wszystkich formuł w arkuszu

Trik 30. Kontrolowanie poprawności obliczeń – wyświetlanie formuł w komórkach arkusza

Trik 31. Kontrolowanie poprawności obliczeń – jednoczesne wyświetlenie formuł i wyników

Trik 32. Kontrolowanie poprawności obliczeń – wyświetlanie powiązań pomiędzy komórkami

Trik 33. Kontrolowanie poprawności obliczeń – formuły arkusza w komentarzach

Trik 34. Trend liniowy w prognozowaniu zmian wartości

Trik 35. Trendy odbicia (zniżki i zwyżki)

Trik 36. Wykładniczy trend wzrostowy

Trik 37. Obliczanie trendu bez użycia formuł

Trik 38. Rozpoznawanie trendu za pomocą różnic wartości średnich

Trik 39. Poprawne odejmowanie czasów

Trik 40. Poprawne obliczanie numeru tygodnia w roku

Trik 41. Podsumowanie czasu pracy z podziałem na dni tygodnia

Trik 42. Określanie wieku poszczególnych pracowników

Trik 43. Zaokrąglanie czasu do kwadransów

Trik 44. Wyznaczanie daty poniedziałkowej w danym tygodniu

Trik 45. Liczba miesięcy pomiędzy dwiema datami

Trik 46. Analiza rozkładu danych

Trik 47. Procentowy rozkład danych

Trik 48. Skumulowany liczbowy rozkład danych

Trik 49. Skumulowany procentowy rozkład danych

Trik 50. Skumulowane sumy wartości zamówień

Trik 51. Nieskumulowane sumy wartości zamówień

Trik 52. Obliczenia z dokładnością taką, jak wyświetlane wartości

Przygotowanie do wersji elektronicznej: RASTER studio, 603 59 59 71