Redakcja

Redaktor Naczelny

Piotr Gromulski

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–2944-1

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.

Jak się zalogować?

Po zalogowaniu zostaniesz przeniesiony do strefy dla prenumeratorów. Możesz tutaj:

– zadać pytanie ekspertowi,

– przeczytać archiwalne artykuły,

– pobrać przykład do konkretnego tekstu,

– wysłać swoje pytania i sugestie do redakcji poradnika.

Jak zadać pytanie ekspertowi?

Krok 1

Kliknij odnośnik Mam problem ze swoim arkuszem – potrzebuję pomocy!

Krok 2

Jak skorzystać z archiwalnych artykułów?

Krok 1

Kliknij odnośnik Teksty archiwalne poradnika [PDF]. Uzyskasz dostęp do archiwum zawierającego setki porad dotyczących posługiwania się Excelem. Możesz przeglądać je za pomocą indeksu literowego lub użyć wyszukiwarki. W tym celu przejdź do kroku 2.

Krok 2

Jak pobrać przykłady do tekstów?

Krok 1

Krok 2

Serwis internetowy poradnika „Excel w praktyce”

www.excelwpraktyce.pl

Na środku strony pojawi się formularz Pytanie do eksperta. Wypełnij w nim dokładnie wszystkie pola i koniecznie załącz plik zawierający przykładowe dane, formuły lub makra, z którymi masz problemy. Kliknij Wyślij pytanie.

Wpisz login i hasło znajdujące się u dołu przedniej okładki bieżącej aktualizacji, a następnie kliknij Zaloguj.

Wpisz je do pola wyszukiwarki i kliknij Szukaj. Na środku strony pojawi się odnośnik do pliku z przykładem.

W okienku Szukaj

wpisz słowo kluczowe lub frazę, np. analiza sprzedaży i kliknij przycisk Szukaj.

Sprawdź oznaczenie swojego artykułu w aktualizacji. Znajdziesz je w górnym rogu strony (np. F 114).

Od Redaktora

Drodzy Czytelnicy!

Tysiące kolumn i wierszy mogą obejmować dane, z którymi na co dzień przychodzi nam się zmierzyć. Prawie zawsze pojawia się problem nawigacji po tak obszernych arkuszach. Paski przewijania nie są zbyt wygodne, ponieważ trudno jest ustawić odpowiedni obszar za pomocą myszy. Istnieją wprawdzie pewne niestandardowe rozwiązania, ale nie do końca spełniają oczekiwania użytkowników. Warto sięgnąć po proste makra i tak zmodyfikować swój roboczy arkusz, aby praca z nim była szybsza i wygodniejsza. Więcej na ten temat w tekście Formanty formularza – nawigacja po arkuszu może być wygodniejsza F 160.

Jeżeli na jednym wykresie chcemy przedstawić dane z obszernego zestawienia, bardzo prawdopodobne, że nie otrzymamy czytelnej prezentacji. Dużo korzystniej jest przedstawiać tylko część informacji (np. zmiany w wybranym przedziale czasu lub ogólnie rysujący się trend zmian). W tekście Prezentacja graficzna – szybka selekcja danych na wykresie P 079 przeczytamy, jak zbudować wykres, który przedstawi dane stanowiące tylko fragmenty tabeli źródłowej (określone wiersze). Wybór wartości będzie wspomagany przez formuły wyszukujące.

Przyjmijmy, że w pracy mamy do czynienia z zestawieniami, w których w obrębie jednej kolumny występują wielokrotnie te same wpisy. Dotyczyć to może na przykład miast, nazw produktów, imion czy nawet nazwisk pracowników. W niektórych sytuacjach przydatne staje się posortowanie listy, gdzie kryterium jest częstość występowania określonego wpisu w tabeli. Zapoznajmy się z hasłem Przetwarzanie danych – rozkład tabeli według częstości ich występowania P 080, a sprawnie wykonamy takie sortowanie.

Bardzo często się zdarza, że w arkuszach z obszernymi zestawieniami najczęściej interesuje nas jedynie niewielki fragment tabeli. Takie cząstkowe zestawienie uzyskamy dzięki filtrowaniu. Niestety, gdy zawartość arkusza ulegnie zmianie, konieczne będzie grupowanie danych od nowa. Okazuje się, że nie musimy tracić na to czasu. Jeżeli dodamy do arkusza odpowiednie formuły, a ich działanie uzależnimy od pól kombi, bardzo szybko otrzymamy przefiltrowane listy cząstkowe z uwzględnionymi zmianami w arkuszu. Więcej na ten temat w tekście Pola kombi – jak w szybki sposób utworzyć cząstkowe listy danych P 081.

Problemem przy prowadzeniu np. magazynu jest żmudne przeglądanie kolejnych regałów w poszukiwaniu konkretnego towaru, który w danej chwili okazał się potrzebny. Przygotujmy zatem sprytną wyszukiwarkę, która pomoże nam błyskawicznie zlokalizować określony towar w naszym magazynie. O tym, jak ją wykonać, przeczytamy w haśle Wyszukiwarka – tworzymy narzędzie oparte na dynamicznym formatowaniu warunkowym W 106.

Serdecznie pozdrawiam

Redaktor Naczelny poradnika „Excel w praktyce”(pgromulski@wip.pl)

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

Warto przeczytać:

Co zrobić, aby przy wpisywaniu hasła były wyświetlane gwiazdki?

Jak podsumować pięć końcowych wartości w kolumnie?

Kiedy stosować funkcję CZY.LICZBA, a kiedy funkcję CZY.NIE.TEKST?

Jak poprawić format dat zaimportowanych z zewnętrznego programu?

Co zrobić, aby przy wpisywaniu hasła były wyświetlane gwiazdki

Nie chcę, aby napisane przeze mnie makro było uruchamiane przez niepowołane osoby, dlatego zabezpieczyłem je hasłem. W tym celu zastosowałem na początku makra funkcję InputBox. W zasadzie działa bez zarzutu, ale jest jeden problem. Przy wpisywaniu hasła wszystkie litery są widoczne. Czy można w jakiś sposób spowodować, żeby zamiast liter pojawiały się gwiazdki jak w większości okien do wprowadzania haseł?

Rysunek 1 przedstawia kod makra nadesłanego przez Czytelnika.

Rys. 1. Początek makra z funkcją InputBox

Przy próbie uruchomienia makra pojawia się pole do podania hasła. Wpisywane znaki są widoczne, jak to przedstawia rysunek 2.

W przypadku podania błędnego hasła pojawi się komunikat jak na rysunku 3.

Aby wpisywane hasło było ukryte pod postacią gwiazdek, powinniśmy utworzyć własny formularz do wpisywania hasła i ustawić odpowiednio właściwość PasswordChar pola tekstowego.

W tym celu:

1. Otwieramy Edytor Visual Basic za pomocą kombinacji klawiszy lewy Alt + F11.

2. Wybieramy polecenie menu Insert/UserForm.

3. Pojawi się projekt nowego formularza. W okienku Properties, przy właściwości Name wpisujemy: frmHasło, a przy właściwości Caption tekst, który ma się pojawić w tytule okna formularza: Podajemy hasło.

4. W okienku Toolbox klikamy polecenie Label i zaznaczamy miejsce na samej górze formularza. Gdy mamy zaznaczoną wstawioną kontrolkę, w oknie Properties, przy właściwości Caption wpisujemy tekst: Aby uruchomić makro, musimy podać hasło. Wpisz hasło:.

5. W okienku Toolbox klikamy polecenie TextBox i zaznaczamy miejsce poniżej kontrolki Label, aby wstawić pole tekstowe, do którego będzie wpisywane hasło. Gdy mamy zaznaczoną wstawioną kontrolkę, w oknie Properties, przy właściwości Name wpisujemy: txtHasło, a przy właściwości PasswordChar wpisujemy znak gwiazdki.

6. W okienku Toolbox klikamy polecenie CommandButton i zaznaczamy miejsce po lewej stronie poniżej kontrolki TextBox, aby wstawić przycisk polecenia. Gdy mamy zaznaczoną wstawioną kontrolkę, w oknie Properties, przy właściwości Name wpisujemy: cmdOK, przy właściwości Caption wprowadzamy OK. Właściwość Default ustaw na True.

7. Obok przycisku polecenia wstawiamy drugi przycisk o takich samych rozmiarach. Gdy mamy zaznaczoną wstawioną kontrolkę, w oknie Properties, przy właściwości Name wpisujemy: cmdAnuluj, a przy właściwości Caption – Anuluj, właściwość Cancel ustawiamy na True.

8. Klikamy dwa razy przycisk OK. Otworzy się kod modułu formularza ze szkieletem procedury zdarzenia kliknięcia tego przycisku. Wypełniamy ją kodem zgodnie z rysunkiem 6, a później dodajemy na początku modułu zmienną globalną hasłoOK oraz procedurę kliknięcia przycisku Anuluj.

9. Przechodzimy do kodu swojego makra i zmieniamy jego początek jak na rysunku 7.

10. Zamykamy Edytor Visual Basic.

Teraz po uruchomieniu makra pojawi się okno formularza UserForm, gdzie będzie można wpisać hasło. Od tego momentu zamiast wpisywanych liter pojawią się symbole gwiazdki.

Jak podsumować pięć końcowych wartości w kolumnie

Arkusz Excela wykorzystuję do wpisywania premii pracowników za wyniki pracy. Dodatek może być przyznawany co miesiąc, ale jest to uzależnione od wyników. Wysokość premii nie jest ponadto stała i często zdarza się, że w danym miesiącu pracownik nie otrzymuje żadnego bonusu pieniężnego. Dane premii przyznanej w poszczególnych miesiącach dopisuję co miesiąc na dole tabeli. Mam problem, ponieważ nie wiem, jak wyznaczyć sumę ostatnich pięciu premii, które otrzymał pracownik, ponieważ dla każdego sumowany będzie zupełnie inny okres. Czy jest możliwe napisanie takiej formuły?

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

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

Co zrobić, aby przy wpisywaniu hasła były wyświetlane gwiazdki

Jak podsumować pięć końcowych wartości w kolumnie

Kiedy stosować funkcję CZY.LICZBA, a kiedy funkcję CZY.NIE.TEKST

Jak poprawić format dat zaimportowanych z zewnętrznego programu

Triki

Porównywanie dwóch plików o takich samych nazwach

Grupowanie wierszy poprzez formatowanie warunkowe i zmianę koloru wypełnienia

Usuwanie niepotrzebnych apostrofów w komórkach

Wygodniejsze dopisywanie danych do długiej listy

Makro uruchamiane w zależności od wpisu w komórce

Wyświetlanie ostatniego wpisu na długiej liście

Formanty formularza – nawigacja po arkuszu może być wygodniejsza

Wygodne przeglądanie i porównywanie danych

Szybki podgląd podsumowania na końcu tabeli

Szybkie uaktywnianie innego arkusza

Przycisk otwierający dowolny skoroszyt

Wygodna obsługa przycisków

Prezentacja graficzna – szybka selekcja danych na wykresie

Przetwarzanie danych – rozkład tabeli według częstości ich występowania

Pola kombi – jak w szybki sposób utworzyć cząstkowe listy danych