Nazywanie komórek (zakresów komórek)

Polecenie: Wprowadzić do dowolnej komórki pustego arkusza kurs Euro (np. 4,50 zł), a następnie nadać tej komórce nazwę „euro”. W podobny sposób nadać nazwę komórce z kursem dolara - „dolar” oraz franka - „frank”.

Rozwiązanie: Do dowolnej komórki pustego arkusza (w tym przykładzie jest to Akrusz1) wpisujemy kurs Euro, Dolara, Franka.

Zaznaczamy komórkę A2. Na wstążce przechodzimy na kartę „Formuły”. Klikamy ikonkę „Definiuj nazwę”. Pojawi się okno dialogowe:

Jeżeli dane na arkuszu wyglądały jak w przykładzie, Excel najprawdopodobniej „domyśli się”, że chcemy nadać komórce A2 nazwę „Euro”. Jeżeli na arkuszu nie byłoby opisów komórek, lub chcielibyśmy nadać inną nazwę, wpisujemy ją w polu nazwa.

Pole „zakres” decyduje o miejscu dostępności definiowanej nazwy. Jeżeli wybierzemy pozycję „Skoroszyt” definiowana nazwa będzie możliwa do wykorzystania w formułach we wszystkich arkuszach skoroszytu. Jeżeli wybierzemy inaczej np. „Arkusz2” nazwa Euro będzie tylko „widoczna” w arkuszu drugim.

Pole „Odwołuje się do” określa, które komórki nazywamy.

Po zatwierdzeniu „OK” możemy już używać nazwy euro w formułach.

Nazwy obowiązujące w całym skoroszycie wykorzystujemy tak:

Nazwy komórek lub zakresów przypisane do konkretnego arkusza można wykorzystywać w innych arkuszach w następujący sposób:

W razie konieczności modyfikacji parametrów zdefiniowanej nazwy lub jej usunięcia należy na karcie „Formuły” kliknąć ikonkę „Menedżer nazw”.

Nazwę można także nadać zakresowi komórek. Załóżmy, że posiadamy listę sprzedaży naszych produktów, którą wykorzystujemy w różnych formułach (np. wyszukujących). Wygodnie jest wtedy nadać nazwę zakresowi komórek a w formułach używać nazw zamiast zakresów.

Komórkom A1:B5 nadajemy nazwę „towary”

Wykorzystanie nazwanego zakresu na innym arkuszu (np. arkuszu do wystawienia rachunku/faktury)

Widok formuł

Do komórki B1 wpisujemy nazwę towaru, natomiast cena jest pobierana z zakresu „towary”.

Widok wyników



POWRÓT NA GÓRĘ

Tworzenie prostych makr

Polecenie: stworzyć makro, które automatycznie zaktualizuje definicję zakresu komórek.

Założenie: Na arkuszu o nazwie „magazyn” znajduje się lista towarów, ich cena oraz stan w magazynie.

Obecnie lista towarów obejmuje zakres komórek A1:C5 i ma zdefiniowaną nazwę „towary”. Nazwa „towary” może być używana w wielu formułach w bieżącym skoroszycie lub w innych skoroszytach (gdy lista towarów znajduje się na dysku sieciowym).

Problem pojawiłby się w przypadku dopisania kilku pozycji do listy. Wówczas nie byłyby one uwzględnione w zakresie „towary”.

Rozwiązanie:
Rozwiązaniem jest zmiana pozycji w polu „Odwołuje się do” w menedżerze nazw.

Jednak gdyby uruchomić rejestratora makr i wykonać zmianę zakresu dla nazwy „towary” zarejestrowane zostanie tylko aktualne ustawienie zakresu komórek (np. A1:C6).

Dlatego stworzone makro wykona następującą sekwencję:
- usunięcie definicji nazwy „towary”
- zaznaczenie bieżącego zakresu komórek
- nadanie zaznaczonemu zakresowi nazwy „towary”

Po zakończeniu rejestracji makra konieczna będzie jego drobna modyfikacja.

W celu rozpoczęcia rejestracji makra przechodzimy na kartę „Deweloper” a następnie klikamy ikonkę „Zarejestruj makro”.

W polu „Nazwa makra” wpisujemy nazwę „aktualizacja”. Makro przechowywane będzie w bieżącym skoroszycie. Rozpoczęło się rejestrowanie makra.

Pierwszą czynnością będzie przejście na kartę „Formuły” i kliknięcie w ikonkę „Menedżer nazw” a w nim usunięcie definicji nazwy „towary”.

Kolejnym krokiem jest zaznaczenie komórki A1 i wydanie komendy „Zaznacz bieżący obszar” (najprościej kombinacją klawiszy CTRL+SHIFT+8).

Następnie nadajemy zaznaczonemu obszarowi nazwę „Towary” (najprościej wpisać tę nazwę w polu nazwy i wcisnąć Enter).
Teraz można już zakończyć rejestrowanie makra.

Sprawdźmy jak Excel zapisał nasze poczynania.
Załączamy edytor Visual Basic (kombinacją klawiszy ALT+F11) a następnie przechodzimy do Module1 (jeśli w skoroszycie nie zapisywaliśmy żadnych innych makr)

Kod zapisany przez rejestratora:

Sub aktualizacja()
'
' aktualizacja Makro
'

'
ActiveWorkbook.Names("towary").Delete
Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="towary", RefersToR1C1:="=magazyn!R1C1:R5C3"
End Sub

Modyfikacji wymaga fragment RefersToR1C1:="=magazyn!R1C1:R5C3", który zmieniamy na:

RefersToR1C1:=Selection

Makro jest gotowe! Powinno wyglądać tak:

Sub aktualizacja()
'
' aktualizacja Makro
'

'
ActiveWorkbook.Names("towary").Delete
Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="towary", RefersToR1C1:=Selection
End Sub

Ostatnim elementem jest jeszcze moment uruchomienia makra. Makro może być uruchamiane skrótem klawiszowym, ikonką umieszczoną na pasku narzędzi „Szybki dostęp” lub przez wybranie makra z listy makr wyświetlanej po kliknięciu w ikonkę „Makra” na karcie „Deweloper”.

POWRÓT NA GÓRĘ

Skróty klawiszowe



Skrót Działanie
CTRL+A Zaznaczyć wszystko
CTRL+C Skopiować zaznaczoną (zaznaczone) komórki lub obiekt
CTRL+X Wyciąć zaznaczoną (zaznaczone) komórki lub obiekt
CTRL+V Wkleić komórkę (komórki) lub obiekt
CTRL+Z Cofnąć ostatnią czynność
CTRL+Y Powtórzyć ostatnią czynność
(lewy) ALT+ENTER Wstawienie kolejnego wiersza w tej samej komórce
CTRL+ENTER Wypełnienie kilku zaznaczonych komórek aktualnie wpisywaną treścią lub formułą
CTRL+D Wypełnienie w dół zaznaczonego obszaru
CTRL+R Wypełnienie w prawo zaznaczonego obszaru
F2 Przejście do edycji aktywnej komórki
CTRL + ; Wstawienie aktualnej daty
CTRL + SHIFT + ; Wstawienie aktualnej godziny
CTRL + SHIFT + ~ Nadanie komórce formatu ogólnego
CTRL + SHIFT + 8 Zaznaczenie bieżącego obszaru
CTRL + klawisz strzałki Przejście do ostatniej niepustej komórki w wierszu lub kolumnie w kierunku wskazanym przez klawisz strzałki.
CTRL + SHIFT + klawisz strzałki Zaznaczenie obszaru od aktywnej komórki do ostatniej niepustej komórki w tym samym wierszu lub kolumnie, co aktywna komórka
CTRL + PAGE DOWN Przejście do następnego arkusza (w prawo)
CTRL + PAGE UP Przejście do poprzedniego arkusza (w lewo)
CTRL + SPACJA Zaznaczenie bieżącej kolumny
SHIFT + SPACJA Zaznaczenie bieżącego wiersza
CTRL + \ Zaznaczenie komórek w zaznaczonym wierszu, które mają inną wartość niż pierwsza widoczna komórka tego wiersza
CTRL + SHIFT +\ Zaznaczenie komórek z zaznaczonej kolumny, które mają inną wartość niż pierwsza widoczna komórka tej kolumny
CTRL + [ Zaznaczenie komórek, do których bezpośrednio odwołują się formuły w zaznaczonym obszarze
CTRL + ] Zaznaczenie komórek, które bezpośrednio odwołują się do aktywnej komórki
CTRL + SHIFT +[ Zaznaczenie komórek, do których bezpośrednio lub pośrednio odwołują się formuły w zaznaczonym obszarze
CTRL + SHIFT +] Zaznaczenie komórek, które bezpośrednio lub pośrednio odwołują się do aktywnej komórki
POWRÓT NA GÓRĘ

Adresowanie względne, bezwzględne i mieszane



Polecenie: przygotować zestawienie (symulację) obliczające ratę spłacanego kredytu ze stałym oprocentowaniem w całym okresie kredytowania oraz ze stałą comiesięczną płatnością. Symulację przeprowadzić dla okresów kredytowanie od 12 do 120 miesięcy.

Rozwiązanie: Funkcją finansową pozwalającą na obliczenie stałej raty spłacanego kredytu przy niezmieniającym się oprocentowaniu w całym okresie kredytowania jest funkcja finansowa PMT. Funkcję tę wykorzystują wszystkie instytucje finansowe (np. banki) obliczając naszą comiesięczną płatność np. kredytu mieszkaniowego. Funkcję tę znajdziemy przechodząc na kartę „Formuły” i klikając w ikonkę „Finansowe” a następnie z rozwiniętej listy w PMT. Pojawi się okno z argumentami funkcji.

W polu „Stopa” należy podać stopę oprocentowania. Uwaga! Powinna być to stopa dla okresu płatności, czyli jeśli płacimy miesięcznie to stopa miesięczna. Stopę miesięczną uzyskamy dzieląc przez 12 stopę roczną, którą najczęściej podają banki.

Liczba_rat to po prostu liczba naszych płatności, (jeśli płacimy co miesiąc – będzie to liczba miesięcy).

Wa – wartość naszego kredytu. Uwaga! Jeśli wpiszemy wartość dodatnią, wynik funkcji będzie ujemny. Jest to związane z kierunkiem przepływu środków finansowych. Jeśli chcemy, aby wynik funkcji nie budził wątpliwości (czy to my płacimy – czy nam płacą), można wartość kredytu podać z minusem).

Wp – jeśli pole to pozostawimy puste – Excel uzna, że w ciągu podanej liczby miesięcy zamierzamy spłacić cały kredyt. Jeśli tak nie jest, to podajemy wartość naszego długu, który pozostanie jeszcze do spłacenia po upłynięciu podanej liczby miesięcy.

Typ – wpisujemy 1 lub 0, (pozostawienie pustego pola jest równoznaczne z wpisaniem 0). Jeśli odsetki naliczane są na koniec miesiąca – wpisujemy 0 lub pozostawiamy pole puste.

Wiemy już, jak obliczyć wartość comiesięcznej płatności a teraz zajmiemy się poprawnym skonstruowaniem formuły użytej w naszym przykładzie (symulacji). Jak pokazano na rysunku wypełnionej tabelki, pierwsza (i jedyna) formuła, którą stworzymy znajduje się w komórce D8. Po poprawnym wpisaniu formuły skopiujemy ją na wszystkie komórki w zestawieniu.

Rozpoczynamy wypełnianie formuły w komórce D8.
Pierwszym argumentem funkcji PMT jest stopa oprocentowania. W naszym przypadku stopa w stosunku rocznym podana jest w komórce D7, dzielimy ją, przez 12 ponieważ płatności dokonujemy miesięcznie.

Kolejnym argumentem jest liczba rat. W naszym przypadku komórka C8.

Ostatnim argumentem jest kwota kredytu. W naszym przykładzie – komórka E4. Przed adresem tej komórki wpisujemy minus.

Formuła brzmi teraz

=PMT(D7/12;C8;-E4)

Nie byłoby nic więcej do zrobienia z tą formułą, gdyby nie konieczność skopiowania jej na pozostałe do wypełnienia komórki. Ze względu na reguły, jakim przy kopiowaniu podlega adres w postaci względnej musimy przed kopiowaniem dokonać w tej formule pewnych modyfikacji.

W adresie komórki D7 (czyli stopy) wprowadzamy symbol $ przed numerem wiersza, aby przy kopiowaniu w dół numer ten nie zmienił się.

W adresie C8 (liczba miesięcy) wprowadzamy $ przed symbolem kolumny, aby przy kopiowaniu w prawo symbol kolumny nie zmienił się.

W adresie E4 wprowadzamy symbol $ zarówno przed symbolem kolumny jak i przed numerem wiersza, aby wartość z tej komórki była uwzględniania we wszystkich pozostałych komórkach zestawienia.

Tak więc gotowa formuła powinna mieć postać:

=PMT(D$7/12;$C8;-$E$4)

Ostatnią czynnością jest teraz skopiowanie wpisanej formuły w dół a następnie w prawo lub w prawo a następnie w dół.

POWRÓT NA GÓRĘ

Filtrowanie zaawansowane



Polecenie: bazując na liście państw świata wykonać następujące czynności:

1 – wyświetlić kraje, których powierzchnia przekracza 5000 km2 lub liczba ludności przekracza 100 milionów,

2 – wyświetlić wszystkie kraje, w których językiem urzędowym (niekoniecznie jedynym) jest język angielski,

3 – wyświetlić wszystkie kraje, dla których nie podano stolicy,

4 – wyświetlić kraje, które mają wypełnione wszystkie pola,

5 – wyświetlić kraje, które nie mają wypełnionego któregokolwiek z pól.



Ad 1

Jeżeli chcemy w filtrze zaawansowanym użyć warunku LUB między różnymi kolumnami listy, należy kryteria wpisać a kilku wierszach



Ad 2

Użycie w filtrze zaawansowanym opcji „Zawiera” wymaga użycia symboli wieloznacznych zawartych między cudzysłowami poprzedzonymi znakiem równości =”=*………….*” Znak * zastępuje dowolny ciąg znaków, także pusty



Znak ? zastępuje tylko jeden znak i jest on wymagany. Poniższy przykład wyświetliłby wszystkie kraje, których nazwa składa się z 5 liter



Filtrując rekordy zawierające w tekście znaki takie jak: * ? ~ należy odpowiedni znak poprzedzić znakiem tyldy (~) Poniższy przykład wyświetliłby wszystkie kraje zawierające w komórce z nazwą państwa gwiazdkę



Ad 3

Jeśli chcemy wyświetlić rekordy nieposiadające wpisanej wartości, w kryteriach wpisujemy tylko znak równości



Ad 4

Chcąc wybrać rekordy posiadające we wskazanej kolumnie jakieś wartości, w kryteriach wpisujemy znak mniejszości (<) oraz większości (>) Jeśli kryteria mają spełniać warunek ORAZ muszą być wpisane w tym samym wierszu



Ad 5

Jeśli chcemy „wyłowić” rekordy zawierające pustkę gdziekolwiek, w kryteriach wpisujemy znak równości w każdej kolumnie, ale w osobnych wierszach



We wszystkich tych przypadkach po załączeniu filtra zaawansowanego w pozycji „Zakres listy” powinien się znaleźć zakres filtrowanej listy a w pozycji „Zakres kryteriów” zakres obejmujący nagłówki oraz wpisane kryteria pod nimi.

Uwaga! Zakres kryteriów musi mieć co najmniej dwa wiersze, gdzie jeden z nich to nagłówki kolumn. Najbezpieczniej jest wstawić kilka pustych wierszy nad filtrowaną listą, następnie skopiować wiersz z nagłówkami i wkleić go do pierwszego wiersza arkusza a pod nim wpisywać kryteria.



POWRÓT NA GÓRĘ

Narzędzie „Szukaj wyniku”

Polecenie: przygotować zestawienie obliczające zysk ze sprzedaży podanej ilości towaru a następnie określić próg rentowności sprzedaży przy podanych kosztach stałych.

Założenie: Sprzedajemy pączki. Koszt produkcji jednego pączka wynosi 65 groszy. Cena sprzedaży wynosi 1,15 zł. Miesięczne koszty wynajmu lokalu, pensji pracowników i energii niezależne od wielkości produkcji wynoszą 2500 zł.



Wartości stałe, które możemy zmieniać, to liczba pączków, koszt 1 pączka, cena pączka oraz koszty stałe. W pozostałych komórkach znajdują się formuły obliczające:

- koszt produkcji wszystkich pączków (koszt 1 pączka x liczba pączków)

- przychód ze sprzedaży (cena pączka x liczba pączków)

- koszty całkowite (koszty stałe + koszt produkcji)

- zysk (przychód ze sprzedaży – koszty całkowite)

Dla podanych w przykładzie danych nasz zysk wyniósłby 750 zł. Aby określić próg rentowności musimy znaleźć taką wielkość produkcji, przy której przychód ze sprzedaży pokryje nasze koszty całkowite. Oczywiście można by zastosować metodę przybliżeń i po prostu trafić w odpowiednią produkcję, ale szybciej będzie, jeśli poprosimy o to Excela.

Rozwiązanie: Wykorzystamy do tego narzędzie „Szukaj wyniku” Znajdziemy je na karcie „Dane” po kliknięciu w ikonkę „Analiza symulacji”. Z rozwiniętej listy wybieramy „Szukaj wyniku”.



W polu „Ustaw komórkę” podajemy adres komórki, której wynik znamy. W naszym przykładzie to będzie komórka z zyskiem (B8). W polu „Wartość” wpisujemy ręcznie odpowiednią wartość, którą ma mieć komórka wymieniona w poprzednim polu (w naszym przykładzie wpisujemy zero). W polu „Zmieniając komórkę” podajemy adres komórki, którą Excel ma zmienić w taki sposób, by po wszystkich przeliczeniach wynik komórki ustawianej osiągnął wartość wpisaną przez nas w polu „Wartość” (w naszym przykładzie podajemy adres komórki z wielkością produkcji B1).

Uwaga! Pozycja „Ustaw komórkę” musi być komórką zawierającą formułę a nie wpisaną wartość. Wynik tej formuły dodatkowo musi zależeć od komórki podanej w polu „Zmieniając komórkę”.

Z kolei komórka podana w polu „Zmieniając komórkę” nie może zawierać formuły zależnej od innych komórek, lecz musi być wartością.

W naszym przykładzie wynikiem produkcji, przy której „wyjdziemy na zero” jest 3846,15384615385 zł. Oczywiście Excel z aptekarską dokładnością policzył, co mu kazaliśmy. My przyjmiemy, że aby nie dokładać do interesu wystarczy wyprodukować i sprzedać 3847 pączków.

Smacznego!

POWRÓT NA GÓRĘ

Pakiet biurowy MS Office | Microsoft Word | Microsoft Excel | Microsoft PowerPoint | Tworzenie stron www | CorelDraw | VBA | Obsługa komputera | Pisanie bezwzrokowe |