:: Trik 1. Zabezpieczenie przed niepoprawnym posortowaniem listy
:: Trik 2. Poprawa dat na późniejsze o 2 lata
:: Trik 3. Wygodne rozliczanie sprzedaży zrealizowanej przez handlowców
:: Trik 4. Autoprzewijanie arkusza na koniec tabeli
:: Trik 5. Niestandardowa numeracja wierszy
Trik 1
Zabezpieczenie przed niepoprawnym posortowaniem listy
Pobierz plik z przykładem
Załóżmy, że od swojego współpracownika otrzymałeś listę danych ułożonych chronologicznie. W arkuszu brakuje kolumny z datami, a Ty przez przypadek posortowałeś wartości tekstowe, przez co dane zostały ułożone w porządku alfabetycznym. Skoroszyt został zapisany, więc nie możesz skorzystać z polecenia Cofnij (to ograniczenie nie dotyczy Excela 2007). W tym momencie szybki powrót do pierwotnego układu danych będzie stanowił nie lada problem. Proponujemy zatem, abyś poznał trik, który pozwoli się przed tym zabezpieczyć.
Przykładową listę transakcji przedstawia rysunek 1.
Rys. 1. Pierwotna kolejność danych
Jeśli planujesz sortować listę i chcesz mieć pewność, że będziesz mógł w każdej chwili naprawić swój błąd przy sortowaniu, wystarczy, że ponumerujesz wiersze w pustej kolumnie na końcu zestawienia.
=> W tym celu:
1. Zaznacz komórkę D2 i wpisz do niej liczbę 1.
2. Kliknij prawym przyciskiem myszy prawy dolny róg komórki D2 i przeciągnij zawartość w dół aż do samego końca listy.
3. Po zwolnieniu przycisku myszy pojawi się menu, z którego wybierz pozycję Wypełnij serią.
Rys. 2. Szybki sposób ponumerowania wierszy
Wiersze zostały ponumerowane, więc możesz dowolnie sortować swoją listę. Pamiętaj jednak, aby przy tej operacji zawsze uwzględniać kolumnę pomocniczą. Zakres komórek do posortowania musi zatem obejmować kolumnę D.
Załóżmy, że ułożyłeś dane wg kolumny Kwota w porządku rosnącym.
=> Aby powrócić teraz do pierwotnego układu, wykonaj następujące czynności:
1. Zaznacz całą listę razem z wierszem nagłówkowym i kolumną pomocniczą.
2. Z menu Dane wybierz polecenie Sortuj (w Excelu 2007 uaktywnij kartę Narzędzia główne, w grupie poleceń Edycja kliknij Sortuj i filtruj/Sortowanie niestandardowe).
3. Z pierwszej rozwijanej listy wybierz pozycję (Kolumna D) i określ kolejność Rosnąco (w Excelu 2007: Od najmniejszych do największych).
Rys. 3. Kryteria sortowania
Po zatwierdzeniu OK dane w arkuszu zostaną ułożone w początkowej kolejności jak to przedstawia rysunek 1.
Trik 2
Poprawa dat na późniejsze o 2 lata
Pobierz plik z przykładem
Zbudowałeś zestawienie, do którego wprowadzasz informacje dotyczące urządzeń działających w Twojej firmie. W kolumnie B znajdują się daty uruchomienia danej maszyny. Niestety są one niepoprawne, gdyż powinny być późniejsze o 2 lata. W jaki sposób szybko je poprawić? Rozwiązaniem jest sprytna formuła.
Rys. 1. Fragment listy
=> Aby z niej skorzystać:
1. W pierwszej kolejności zaznacz całą kolumnę C, klikając jej nagłówek z oznaczeniem literowym i wciśnij kombinację klawiszy Ctrl + Shift + = (znak równości). Zostanie wstawiona pomocnicza kolumna.
2. Do komórki C2 wprowadź następującą formułę:
=DATA(ROK(B2)+2;MIESIĄC(B2);DZIEŃ(B2))
3. Skopiuj ją do komórek poniżej.
Uwaga
Jeżeli zamiast dat w komórkach pojawiły się liczby, zaznacz je i nadaj format daty poprzez okno dialogowe Formatowanie komórek (Ctrl + 1).
Rys. 2. Daty późniejsze o 2 lata
=> Aby teraz usunąć błędne daty:
1. Zaznacz daty zwrócone przez formułę i wciśnij kombinację klawiszy Ctrl + C.
2. Nie zmieniając zaznaczenia komórek, z menu Edycja wybierz polecenie Wklej specjalnie (w Excelu 2007: uaktywnij kartę Narzędzia główne, rozwiń listę poniżej przycisku Wklej i wskaż Wklej specjalnie).
3. Zaznacz pole Wartości i zatwierdź OK.
4. Na koniec usuń całą kolumnę B.
Trik 3
Wygodne rozliczanie sprzedaży zrealizowanej przez handlowców
Pobierz plik z przykładem
Koordynujesz pracę działu handlowego w firmie i pod koniec miesiąca spływają do Ciebie informacje od handlowców dotyczące osiągniętych utargów. Niestety nie otrzymujesz ich w tym samym czasie, więc zależy Ci na tym, aby na bieżąco sprawdzać, kto rozliczył utarg za dany miesiąc, ile jest aktualnie rozliczonych kwot i jaka jest ich łączna wartość. Takie informacje możesz błyskawicznie odczytywać z podręcznego rejestru.
Rejestr w wyjściowej postaci przedstawia rysunek 1.
Rys. 1. Tutaj wprowadzane są informacje o utargach
Etap 1. Dołączenie pól wyboru
=> Aby móc szybko zaznaczać, który z handlowców już dokonał rozliczenia, w kolumnie pomocniczej zastosuj formanty:
1. Kolumnie D nadaj nazwę Stan rozliczenia.
2. Teraz z menu Widok wybierz polecenie Paski narzędzi/Formularze (w Excelu 2007: uaktywnij kartę Deweloper i w grupie poleceń Formanty kliknij Wstaw).
Wskazówka
Jeśli karta Deweloper jest niewidoczna, to kliknij Przycisk pakietu Office/Opcje programu Excel i w zakładce Popularne zaznacz opcję Pokaż kartę Deweloper na wstążce.
3. Zaznacz ikonę formantu Pole wyboru i narysuj go w obrębie komórki D2.
Rys. 2. Pierwsze pole wyboru zostało wstawione
4. Kliknij go prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Edytuj tekst.
5. Przytrzymaj klawisz Delete, aby usunąć opis formantu.
6. Ponownie kliknij pole wyboru prawym przyciskiem myszy, ale tym razem wybierz polecenie Kopiuj.
7. Zaznacz komórkę D2 i wciśnij kombinację klawiszy Ctrl + V. W ten sposób wstawisz formant do kolejnej komórki.
8. Analogiczną czynność wykonaj dla kolejnych wierszy.
Etap 2. Ustawienia formantów
=> Teraz powinieneś dostosować ustawienia poszczególnych formantów:
1. Kliknij pierwszy z nich prawym przyciskiem myszy i z menu podręcznego wybierz Formatuj formant.
2. Przejdź do zakładki Formant i w polu Łącze komórki wprowadź adres komórki, do której wstawione jest pole wyboru (czyli D2). Możesz także uaktywnić pole Cień 3-W.
3. W analogiczny sposób zdefiniuj ustawienia pozostałych formantów.
Gdy teraz włączysz dowolne pole wyboru, zauważysz, że w komórce została wyświetlona wartość PRAWDA.
Rys. 3. Zaznaczone pole wyboru wyświetla wartość PRAWDA
Na niej będą bazowały formuły - więc nie może być usunięta. Proponujemy ją ukryć.
=> Aby to zrobić:
4. Zaznacz wszystkie komórki z formantami i wciśnij kombinację klawiszy Ctrl + 1.
5. Na liście kategorii zaznacz Niestandardowe, a w polu Typ wpisz trzy średniki (;;;).
6. Zatwierdź OK.
Etap 3. Formuły obliczeniowe
Aby na bieżąco śledzić stan rozliczeń i ich kwotę, powinieneś zastosować odpowiednie formuły obliczeniowe:
=> W tym celu:
1. W dowolnej pustej komórce wpisz formułę:
=SUMA.JEŻELI(D2:D7;"PRAWDA";C2:C7)
która zwróci sumę kwot utargów, obok których zaznaczone jest pole wyboru.
2. Do komórki poniżej wpisz natomiast formułę, która zliczy zaznaczone formanty:
=LICZ.JEŻELI(D2:D10;"PRAWDA")
Rys. 4. Gotowy rejestr
Trik 4
Autoprzewijanie arkusza na koniec tabeli
Pobierz plik z przykładem
Przyjmijmy, że pracujesz z tabelą, która obejmuje dużą liczbę wierszy. Kolejne wpisy dodajesz na jej końcu, ale często przeglądasz też górną część zestawienia. Zdarza się wówczas, że musisz przewijać arkusz na koniec listy, co z pewnością nie jest wygodne. Proponujemy pewne usprawnienie. Przy każdym otworzeniu do arkusza zostanie automatycznie wyświetlony koniec listy, dzięki czemu wygodnie dopiszesz nowe dane.
Aby osiągnąć efekt przewijania arkusza, musisz zastosować makro, które będzie się uruchamiało w momencie aktywacji arkusza.
=> W tym celu:
1. Kliknij prawym przyciskiem myszy zakładkę arkusza i z menu podręcznego wybierz polecenie Wyświetl kod.
2. Otworzy się okno Edytora Visual Basic. Wpisz do niego kod widoczny na rysunku 1. Znajdziesz go także w pliku z przykładem załączonym do tego triku.
Rys. 1. Kod makra
3. Zapisz i zamknij Edytor Visual Basic.
Teraz po przewinięciu arkusza w górę, przejściu do innego arkusza i powrocie do arkusza z wprowadzonym kodem, nastąpi automatyczne przejście na koniec listy.
Rys. 2. Lista przewinięta na koniec
Uwaga
Jeśli pusty wiersz na końcu tabeli będzie widoczny, to arkusz nie przewinie się, ale komórka na końcu tabeli się uaktywni.
Trik 5
Niestandardowa numeracja wierszy
Pobierz plik z przykładem
Otrzymałeś dane sprzedażowe z 3 ostatnich lat. Niestety wiersze nie są oznaczone miesiącami i trudno Ci odnaleźć kwotę z danego okresu. Przykładowe wartości przedstawia rysunek 1.
Rys. 1. Dane w nieczytelnym układzie
W wierszu 2. znajdują się dane ze stycznia, w 3. wierszu z lutego itd. - aż do wiersza 14., w którym znajdują się dane styczniowe z kolejnego roku. Należałoby zatem tak ponumerować listę, aby pierwsze 12 wierszy z kwotami miało numery 1-12 i kolejne 12 wierszy miało takie same numery od 1-12.
=> W tym celu:
1. Wstaw nową kolumnę na początku zestawienia. Zaznacz całą kolumnę A, klikając jej nagłówek z oznaczeniem literowym i wybierz kombinację klawiszy Ctrl + Shift + = (znak równości).
2. W komórce A2 wprowadź następującą formułę:
3. Następnie skopiuj ją do komórek poniżej.
Rys. 2. Numeracja od 1 do 12
|