:: Trik 1. Odchylenie procentowe w stosunku do minionego okresu
:: Trik 2. Obiekt tekstowy w arkuszu połączony ze źródłem
:: Trik 3. Przenoszenie oznaczonych wierszy do innego arkusza
:: Trik 4. Wyznaczenie adresu komórki z największą wartością
:: Trik 5. Wydzielenie określonych znaków z dłuższego ciągu tekstowego
Trik 1
Odchylenie procentowe w stosunku do minionego okresu
Pobierz plik z przykładem
Jeżeli chcesz wyznaczyć odchylenia procentowe bez użycia dodatkowych komórek pomocniczych czy czasochłonnych obliczeń pośrednich, skorzystaj z formuły zawierającej funkcję MODUŁ.LICZBY. Zwraca ona w wyniku wartość bezwzględną danej liczby. Oznacza to, że liczba zostaje zwrócona jako wartość dodatnia, niezależnie od poprzedzającego ją znaku.
MODUŁ.LICZBY potrzebuje jako argumentu wyłącznie liczby, której wartość bezwzględną chcesz podać. Można ją wskazać bezpośrednio lub jako odwołanie do komórki. Do przedstawienia zmian procentowych posłużymy się przykładową tabelą. W komórkach A2:A13 wpisane są nazwy miesięcy. Obok wprowadzone są liczby dotyczące obrotów.
Rys. 1. Przykładowe dane
=> Aby w kolumnie C tej tabeli podać zmiany procentowe w stosunku do miesiąca poprzedniego, wykonaj następujące czynności:
1. W komórce C2 wpisz następującą formułę:
=(B3-B2)/MODUŁ.LICZBY(B2)
a zostaną zwrócone odchylenia dziesiętne.
2. Zmień format komórki na procentowy, korzystając z kombinacji klawiszy Ctrl + Shift + 5.
Rys. 2. Obliczone zmiany procentowe
3. Następnie skopiuj poniżej zawartość komórki C2 aż do komórki C12.
Trik 2
Obiekt tekstowy w arkuszu połączony ze źródłem
Pobierz plik z przykładem
Przyjmijmy, że w arkuszu analizujesz sprzedaż produktów w poszczególnych regionach. Część danych dotyczących III kwartału znajduje się w dokumencie Worda. Chcesz mieć do nich stały wgląd i na bieżąco śledzić zmiany wprowadzane w pliku. Aby uniknąć każdorazowego poszukiwania dokumentu i otwierania go, proponujemy osadzenie danych w arkuszu i połączenie z plikiem źródłowym. Przykładowe wartości przedstawia rysunek 1.
Rys. 1. Dane w dokumencie
Pokażemy Ci teraz, jak osadzić te dane w arkuszu i utworzyć łącze z plikiem źródłowym.
=> W tym celu:
1. Otwórz skoroszyt Excela i z menu Wstaw wybierz polecenie Obiekt (w Excelu 2007: uaktywnij kartę Wstawianie i w grupie poleceń Tekst wskaż Obiekt).
2. W oknie dialogowym, które się pojawi, przejdź do zakładki Utwórz z pliku.
3. Kliknij przycisk Przeglądaj i odszukaj odpowiedni plik na dysku swojego komputera.
4. Zaznacz go i kliknij przycisk Wstaw.
Rys. 2. Osadzanie obiektu
5. Zaznacz pole Łącz z plikiem i zatwierdź, klikając OK. Twój arkusz będzie teraz wyglądał jak na rysunku 3.
Rys. 3. Osadzony dokument
Automatyczna aktualizacja zmian
Dzięki temu, że połączyłeś obiekt z plikiem źródłowym, wszystkie zmiany wprowadzone w dokumencie będą automatycznie uwzględnione w obiekcie osadzonym w arkuszu. Zamknij teraz skoroszyt i otwórz go ponownie. Excel wyświetli komunikat dotyczący aktualizowania łączy i będzie się pojawiał przy każdym otwarciu skoroszytu zawierającego łącza.
Trik 3
Przenoszenie oznaczonych wierszy do innego arkusza
Pobierz plik z przykładem
Chcesz sprawnie przenieść określone wiersze z danymi do innego arkusza? Nie ma szybszego sposobu niż wykorzystanie makra. Załóżmy, że chcesz uaktualnić bazę pracowników obecnie zatrudnionych. Ci, którzy już nie pracują, zostali oznaczeni kolorem czerwonym. Chcesz, żeby te osoby zostały przeniesione do arkusza Rezygnacje i jednocześnie usunięte z arkusza Pracownicy (rys. 1.)
Rys. 1. Lista zatrudnionych pracowników
=> Aby przygotować odpowiednie makro:
1. Uruchom Edytor Visual Basic za pomocą kombinacji klawiszy lewy Alt + F11.
2. W oknie Project - VBAProject (po lewej stronie), kliknij dwukrotnie Arkusz1(Pracownicy) i w otwartym oknie modułu wpisz kod z rysunku 2. Znajdziesz go także w pliku z przykładem załączonym do tego triku.
3. Zapisz i zamknij Edytor VB.
Rys. 2. Makro automatycznie przenosi oznaczone dane do innego arkusza
4. Wciśnij kombinację klawiszy lewy Alt + F8, wybierz z listy makro: przenoszenie_kolor i kliknij Uruchom.
Wszystkie dane w arkuszu Pracownicy oznaczone kolorem czerwonym zostały przeniesione do arkusza Rezygnacje (rys. 3).
Rys. 3. Dane automatycznie przeniesione do innego arkusza
Wskazówka
Jeśli arkusz, do którego dane mają być przeniesione, ma inną nazwę, zastąp nią wpisy Rezygnacje zawarte w kodzie.
Trik 4
Wyznaczenie adresu komórki z największą wartością
Pobierz plik z przykładem
Przyjmijmy, że otworzyłeś arkusz, gdzie w kolumnie A znajduje się kilka tysięcy kwot. Ciebie interesuje tylko jedna informacja, w której komórce znajduje się największa wartość, aby sprawdzić, której daty dotyczy. Przeglądanie kolumny wiersz po wierszu można porównać do szukania igły w stogu siana. Jeśli chcesz to zadanie wykonać w kilka chwil, zastosuj formułę, która zwróci adres komórki z największą wartością. Jej składnia wygląda następująco:
="A"&PODAJ.POZYCJĘ(MAX(A:A);A:A;0)
Rys. 1. Adres komórki z największą wartością
Wyjaśnienie działania formuły:
W pierwszym argumencie funkcji PODAJ.POZYCJĘ określasz szukaną wartość. Jest ona wyznaczana za pomocą funkcji MAX. W drugim argumencie wskazujesz obszar arkusza, gdzie ma być szukana największa liczba. W naszym przykładzie jest to cała kolumna A. Ostatni argument określa, że ma zostać zwrócona pozycja pierwszej napotkanej wartości spełniającej kryterium (w przypadku, gdyby istniało kilka takich samych liczb będących największymi wartościami). Funkcja PODAJ.POZYCJĘ zwróci pozycję, na której znajduje się wartość maksymalna, co będzie odpowiadało numerowi wiersza. Do niego doklejane jest oznaczenie kolumny ("A"&) i zwracany jest kompletny adres.
Trik 5
Wydzielenie określonych znaków z dłuższego ciągu tekstowego
Pobierz plik z przykładem
Załóżmy, że do arkusza przeniosłeś dane z innego programu. Niestety wartości dotyczące różnych kategorii zostały wprowadzone do jednej komórki. Ciebie interesuje liczba znajdująca się wewnątrz ciągu tekstowego. Przykładowe dane przedstawia rysunek 1.
Rys. 1. Niepoprawnie zaimportowane wartości
Do dalszych obliczeń potrzebujesz tylko liczb znajdujących się pomiędzy wpisami w nawiasach. W jaki sposób je szybko wyodrębnić do osobnych komórek? Rozwiązaniem jest elastyczna formuła oparta na funkcjach tekstowych.
=> Aby z niej skorzystać:
1. Do komórki B3 wprowadź następującą formułę:
=FRAGMENT.TEKSTU(A3;ZNAJDŹ("]";A3)+2;
ZNAJDŹ("[";A3;ZNAJDŹ("]";A3))-ZNAJDŹ("]";A3)-3)
2. Skopiuj ją do komórek poniżej.
Rys. 2. Wyodrębnione liczby
Wyjaśnienie działania formuły:
Funkcja FRAGMENT.TEKSTU zwraca wartość tekstową zawartą w określonym ciągu znaków. Pierwszy argument to komórka, w której znajduje się tekst bazowy. Drugi argument to pozycja pierwszego znaku tekstu, który ma zostać wyodrębniony. Ostatni argument to liczba znaków, ile ma zostać wyodrębnionych. W przykładzie pierwszy argument to komórka A3. Drugi argument ZNAJDŹ("]";A3)+2 określa, od której pozycji rozpoczyna się ciąg tekstowy, który ma zostać zwrócony. W przykładzie jest to drugi znak następujący po pierwszym nawiasie zamykającym w komórce A3. Trzeci argument określa, z ilu znaków będzie się składał tekst przeznaczony do wyodrębnienia. Zauważ, że w naszym przykładzie nie ma wpisów o jednakowej długości. Dlatego, do tego celu została zastosowana skomplikowana kombinacja funkcji ZNAJDŹ. Jej zadaniem jest obliczenie pozycji drugiego wystąpienia nawiasu otwierającego i odjęcie od niej pozycji pierwszego nawiasu zamykającego. W ten sposób otrzymamy długość tekstu znajdującego się między tymi nawiasami. W celu pominięcia spacji otrzymany wynik korygujemy poprzez odjęcie od niego liczby 3. Dzięki temu za pomocą jednej uniwersalnej formuły możliwe jest wyodrębnienie wartości o różnej liczbie znaków.
|