lekcja 11, excel, triki execel, lekcja 11


0x01 graphic

 

Wersja do wydruku [PDF]

:: 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.

0x01 graphic

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.

0x01 graphic

Rys. 2. Koszt wyprodukowania określonej liczby sztuk produktu

[do góry]

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.

0x01 graphic

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.

0x01 graphic

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.

[do góry]

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.

0x01 graphic

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.

0x01 graphic

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).

[do góry]

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.

0x01 graphic

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.

0x01 graphic

Rys. 2. Formuła obok wyniku

[do góry]

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.

0x01 graphic

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:

B*ski

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.

0x01 graphic

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.

[do góry]

 

Wiedza i Praktyka sp. z o.o., ul. Łotewska 9a, 03-918 Warszawa,
Centrum Obsługi Klienta: tel.: (022) 518 29 29 fax: (022) 617 60 10, e-mail: e-serwis(malpa)wip.pl

Numer NIP: 526-19-92-256 Numer KRS: 0000098264 - Sad Rejonowy dla m.st. Warszawy, Sad Gospodarczy XIII Wydzial Gospodarczy Rejestrowy Wysokosc kapitalu zakladowego: 200 000 zl

 

0x01 graphic



__________ NOD32 Informacje 4030 (20090423) __________

Wiadomosc zostala sprawdzona przez System Antywirusowy NOD32
http://www.nod32.com lub http://www.nod32.pl



Wyszukiwarka

Podobne podstrony:
lekcja 19, excel, triki execel, lekcja 19
[lekcja 11] Operacje porównania Kurs C++ » Poziom 1
AutoCAD - Kurs zaawansowany - Lekcja 11, autocad kurs, Zaawansowany
Lekcja 11 Szene 11
11 Lekcja
lekcja 11 1
Lekcja 11 M stawów bliższych i dalszych ręki
jezyk ukrainski lekcja 11
Lekcja 11 Past Perfect, lekcje
Lekcja,11,12
lekcja 11, suplementy, Materiały na prace licencjacką, szkoła, farmakologia
Lekcja 11, szkolnictwo, studia
Lekcja 11
[lekcja 11] Operacje porównania Kurs C++ » Poziom 1
AutoCAD - Kurs zaawansowany - Lekcja 11, autocad kurs, Zaawansowany
11 Lekcja 10 Tabele
Lekcja 11 odpowiedzi
Lekcja 11
Lekcja 11

więcej podobnych podstron