:: Trik 1. Wyszukiwanie danych w kilku tabelach jednocześnie
:: Trik 2. Wygodna nawigacja po obszernych zakresach danych
:: Trik 3. Szybka analiza bazy danych
:: Trik 4. Wynik formuły i jej składnia w sąsiednich komórkach
:: Trik 5. Niestandardowe filtrowanie tekstów
Trik 1
Wyszukiwanie danych w kilku tabelach jednocześnie
Pobierz plik z przykładem
Dużo łatwiej jest wyszukiwać dane w kompletnych, spójnych zestawieniach niż w tabelach cząstkowych porozrzucanych po różnych zakresach lub wręcz umieszczonych w oddzielnych arkuszach. Gdy przyjdzie Ci pracować z tymi drugimi, będziesz musisz sobie jakoś poradzić, ponieważ na łączenie danych mógłbyś stracić zbyt wiele czasu. Przykładowe zestawienia cząstkowe przedstawia rysunek 1.
Rys. 1. Dane umieszczone w oddzielnych zestawieniach
Przyjmijmy, że Twoim zadaniem jest odszukanie kwoty potrzebnej do wyprodukowania 20 sztuk produktów. Niestety, te dane nie znajdują się w jednym zestawieniu. Należy zatem w pierwszej kolejności sprawdzić, jakie jest zapotrzebowanie na czas (pierwsza tabela). Na podstawie tej informacji trzeba odszukać w drugiej tabeli, ilu pracowników musi być zaangażowanych do tej pracy. Otrzymany wynik powinien zostać porównany z danymi zgromadzonymi w trzeciej tabeli. W taki sposób wyznaczysz szukaną kwotę.
Zadanie należałoby w zasadzie przeprowadzić w trzech etapach za pomocą trzech formuł opartych na funkcjach WYSZUKAJ.PIONOWO. Okazuje się, że możesz to zrobić szybciej za pomocą jednej formuły.
=> W tym celu:
1. W komórce E4 wpisz szukaną ilość: 20.
2. W komórce E5 wprowadź następującą formułę:
=WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO
(WYSZUKAJ.PIONOWO(E4;A4:B6;2);A11:B13;2);A18:B20;2)
Wyjaśnienie działania formuły:
Działanie formuły polega na wyznaczaniu po kolei pierwszego argumentu poprzedzającej (nadrzędnej) funkcji WYSZUKAJ.PIONOWO. Trzecia funkcja wyszukuje wartość czasu potrzebną na wyprodukowanie liczby sztuk określonej w komórce E4. Otrzymana wartość (125) jest przekazywana do pierwszego argumentu drugiej funkcji WYSZUKAJ.PIONOWO, która w drugim zestawieniu odszukuje odpowiednią liczbę pracowników. Otrzymana w ten sposób wartość (5) jest przekazywana do pierwszej funkcji i na tej podstawie zwracany jest końcowy wynik działania formuły.
Rys. 2. Koszt wyprodukowania określonej liczby sztuk produktu
Trik 2
Wygodna nawigacja po obszernych zakresach danych
Pobierz plik z przykładem
Jeśli po wielogodzinnym przeszukiwaniu obszernych zestawień masz problem ze skupieniem wzroku na właściwym wierszu lub kolumnie, to mamy dla Ciebie bardzo przydatne rozwiązanie. Krzyżowe zaznaczenie pozwoli na wygodne przeglądanie danych dzięki podświetleniu całej kolumny i wiersza, w których znajduje się aktualnie zaznaczona komórka.
=> Aby uzyskać taki efekt, wykonaj następujące czynności:
1. Kliknij prawym przyciskiem myszy zakładkę arkusza, w którym znajdują się analizowane dane.
2. Z menu podręcznego wybierz pozycję Wyświetl kod.
3. W oknie Edytora VBA, które zostanie wyświetlone, wpisz kod makr z rysunku 1. Znajdziesz go także w pliku z przykładem załączonym do tego triku.
Rys. 1. Kod makr podświetlających aktywny wiersz i kolumnę
4. Zapisz i zamknij Edytor VBA.
Od tego momentu po kliknięciu dowolnej komórki w arkuszu zostanie podświetlony cały aktywny wiersz i kolumna, jak to przedstawia rysunek 2.
Rys. 2. Krzyżowe zaznaczenie
Uwaga
Jeśli będziesz potrzebował zaznaczyć tylko jedną komórkę, to należy poza obszarem zaznaczonym kliknąć prawym przyciskiem myszy, a następnie tę samą komórkę kliknąć lewym przyciskiem myszy. Jest to pewna niedogodność, która jednak w niczym nie umniejsza możliwości arkusza i nie spowalnia działania Excela.
Trik 3
Szybka analiza bazy danych
Pobierz plik z przykładem
Gdy chcesz przeanalizować dane w rozbudowanej bazie, nie trać czasu na grupowanie rekordów i późniejsze stosowanie formuł obliczeniowych. Możesz śmiało skorzystać z funkcji bazodanowych, które znacznie przyspieszą i ułatwią wykonanie tego typu zadania. Przyjmijmy, że dysponujesz obszerną listą sprzedaży zrealizowanej przez sprzedawców, którą przedstawia rysunek 1.
Rys. 1. Fragment bazy danych
Chcesz szybko znaleźć średnią, maksymalną i minimalną wartość transakcji zrealizowanej w określonym dniu i mieście przez danego handlowca. Zadanie na pozór wydaje się złożone. Ale tylko na pozór.
=> W tym celu:
1. Wstaw 3 puste wiersze powyżej listy z danymi.
2. Skopiuj wiersz nagłówkowy i wklej go do pierwszego wiersza.
3. Wprowadź kryteria wyszukiwania, czyli datę sprzedaży, nazwisko sprzedawcy i miasto.
4. Do komórki A3 wpisz następującą formułę:
=BD.ŚREDNIA($A$4:$E$30;5;$A$1:$E$2)
i skopiuj ją do dwóch kolejnych komórek po prawej. W skopiowanych formułach zmień słowo ŚREDNIA na MAX oraz MIN.
Rys. 2. Szybkie obliczenia na podstawie bazy
Wyjaśnienie działania formuły:
Pierwszy argument funkcji BD.ŚREDNIA to zakres komórek, który ma zostać przeszukany. Jak widzisz adres obejmuje także wiersz nagłówkowy bazy danych. W drugim argumencie określasz numer kolumny, której dotyczy obliczenie. W tym przykładzie chcesz wyliczyć średnią wartość z kwot, a zatem wskazujesz kolumnę 5. Ostatni argument funkcji BD.ŚREDNIA to zakres komórek zawierający kryteria wyszukiwania (także z wierszem nagłówkowym).
Trik 4
Wynik formuły i jej składnia w sąsiednich komórkach
Pobierz plik z przykładem
W trakcie kontrolowania poprawności obliczeń zachodzi niekiedy potrzeba jednoczesnego wyświetlenia wyniku formuły i jej składni. Pojawia się wówczas problem, ponieważ w Excelu nie ma takiej opcji. Po przełączeniu się w tryb inspekcji formuł lub zmianie opcji programu, widoczne są albo formuły albo ich wyniki. Jednakże, gdyby obok wyników były widoczne również formuły, dużo szybciej można by było wychwycić ewentualne pomyłki w obliczeniach.
=> Proponujemy zastosować sprytny trik, aby poradzić sobie z tą niedogodnością:
1. Zaznacz komórkę w której znajduje się formuła.
2. Przejdź w tryb edycji komórki wciskają klawisz F2.
3. Wyświetloną formułę zaznacz myszką, tak aby podświetliła się na czarno.
Rys. 1. Zaznaczona formuła
4. Skopiuj formułę do schowka, wciskając na klawiaturze Ctrl + C.
5. Wciśnij klawisz Esc, aby wyłączyć tryb edycji komórki.
6. Przejdź do komórki, w której ma być wstawiona formuła, wstaw w niej znak apostrofu (chodzi o klawisz znajdujący się obok Entera) i wciśnij kombinację klawiszy Ctrl + V.
Po zatwierdzeniu klawiszem Enter uzyskasz pożądany efekt.
Rys. 2. Formuła obok wyniku
Trik 5
Niestandardowe filtrowanie tekstów
Pobierz plik z przykładem
Dysponujesz listą osób, którą chcesz tak przefiltrować, aby widoczne były jedynie nazwiska zaczynające się na literę B i kończące na (ski). Otwierasz zatem okno autofiltra niestandardowego i przeglądasz kolejne kryteria. Niestety za pomocą standardowych opcji nie uzyskasz takiego pogrupowania danych. Aby wykonać takie zadanie, konieczne będzie skorzystanie z symboli wieloznacznych.
Fragment listy nazwisk przedstawia rysunek 1.
Rys. 1. Lista nazwisk do przefiltrowania
=> W tym celu:
1. Zaznacz dowolną komórkę w obrębie listy i z menu Dane wybierz polecenie Filtr/Autofiltr (w Excelu 2007: uaktywnij kartę Dane i w grupie poleceń Sortowanie i filtrowanie wskaż Filtruj).
2. Z rozwijanej listy w komórce A1 wybierz pozycję Niestandardowe (w Excelu 2007: Filtry tekstu/Filtr niestandardowy).
3. Z pierwszej listy po lewej wybierz równa się, a w pole obok wpisz:
Tak zdefiniowane kryterium oznacza, że mają być odnalezione nazwiska rozpoczynające się literą B i kończące znakami (ski). Pomiędzy nimi może się znajdować dowolna liczba znaków dowolnego rodzaju.
Rys. 2. Poprawnie przefiltrowane dane
Wskazówka
W trakcie definiowania kryteriów możesz także używać znaku zapytania (?), który zastępuje pojedynczy dowolny znak. A zatem kryterium B??k spowoduje wyświetlenie czteroznakowych tekstów rozpoczynających się literą B i kończących się literą k.
|