Excel

Niedoceniona funkcja excela – generuj funkcję weźdanetabeli

3 Mar , 2018  

generuj funkcję weźdanetabeli

Patrząc na osoby, które pracują w Excelu, rzadko w ich zestawieniu pojawia się funkcja generuj funkcję weźdanetabeli, a przecież możliwości jej są nieocenione. Warto ją zastosować przy tworzeniu interaktywnych raportów, czy po prostu wyselekcjonowania konkretnej informacji z tabeli przestawnej.

Jak generować funkcję wezdanetabeli?

Najpierw musimy stworzyć na podstawie danych tabele przestawną, upewnić się że mamy włączone generowania automatyczne tej funkcji. Warto zapoznać się również z funkcją przesuniecie.

Zaczynamy!

Zakładam że wiesz jak tworzyć tabele przestawne, wiec ten krok pominę, jeśli nie, możesz skorzystać z tego pliku, w którym jest już gotowa tabela przestawna.
Tabela przestawna pokazuje miesięczne wydatki podzielone na kategorie budżetu domowego.

Sprawdźmy czy mamy włączoną opcję Generuj funkcję WeźDaneTabeli, musimy stać kursorem na tabeli przestawnej, przechodzimy do zakładki Analiza, odnajdujemy ikonę z opisem Tabela przestawna, klikamy i z dostępnej listy wybieramy Opcje klikając na małą strzłke. Pojawia się na samym dole listy funkcja Generuj funkcję WeźDaneTabeli. Jeśli przy tej funkcji jest zaznaczony ptaszek, to oznacza że funkcja jest włączona. Wygląda to tak jak na poniższym screenie.

generuj funkcję weźdanetabeli

Skopiujmy nagłówek tabeli do wiersza 12-stego, a w komórce C13 wpisujemy = i zaznzaczamy komórkę C5

generuj funkcję weźdanetabeli
Pierwszym argumentem tej funkcji jest pole danych, nas interesuje kwota. Kolejnym argumentem jest wskazanie tabeli przestawnej, tu wystarczy wskazać adres w obrębie tabeli przestawnej, domyślnie jest to pierwsza komórka naszej tabeli przestawnej $A$3.
Kolejne pola w formule są to parametry, które możemy dynamicznie zmieniać wskazując komórkę którą będziemy modyfikować.
Zmodyfikujmy naszą formułę tak, aby reagowała na wartość w komórce A13 w której będziemy zmieniać datę. Wprowadźmy w A13 datę „2018-01-01”. Teraz zmodyfikujmy formułę, stajemy na komórce C13 i zaznaczamy obszar w pasku formuł.

wpisujemy w zaznaczony zakres adres naszej daty $A13


Formuła będzie dynamicznie wyświetlać wyniki dla wybranego miesiąca, zmieniając miesiąc w komórce A13.

Kolejny parametr w naszje formule to kat1 (kategoria), należy postąpić tak jak zrobliśmy to z datą.
Podmieniamy słowo „dzieci” na adres C12.
Pozostało nam skopiować formułę do pozostałych komórek i cieszyć się wynikiem.
Jeśli pojawił Ci się błąd sprawdź czy masz dobrze zadolarowane adresy komórek: $A13 i C$12 w komórce C13.

Modyfikując komórkę A13 na listę, mamy już ciekawy efekt zmiany wartości w poszczególnych kategoriach w zależności od wybranej daty.

 

Przykład poniżej do ściągnięcia

WezDaneTabeli.xls

 

, , , , , , ,

Excel

Warunkowe blokowanie komórek w excelu – chroń komórki Excela!

2 Wrz , 2016  

warunkowe blokowanie komórek w excelu

Warunkowe blokowanie komórek w excelu – chroń komórki Excela!

Chcemy chronić wszystkie komórki arkusz  lub warunkowe blokowanie komórek (wybrane komórki). Jak się za to zabrać?

W Excelu mamy taką funkcjonalność możemy chronić komórki w arkuszu,  na początek trzeba zrozumieć, że wszystkie komórki domyślnie są zablokowane w momencie, gdy chronimy arkusz lub cały skoroszyt. Najpierw musimy odblokować wszystkie komórki, następnie zablokować wybrane komórki i włączyć ochronę.

Do dzieła!

Na zakładce „Narzędzia główne” klikamy na polecenie Format:

warunkowe blokowanie komórek w excelu

Z rozwijanego menu na dole mamy dwa polecenia: „Chroń arkusz…” i „Zablokuj komórkę”

Drugie polecenie blokuj_komorke „Zablokuj komórkę” domyślnie jest włączone, tzn. wszystkie komórki są zablokowane.  Ktoś powie przecież ja mogę w każdej komórce zmieniać dane, owszem ma rację. To polecenie działa dopiero, gdy włączymy polecenie chron_arkusz „Chroń arkusz...”.

Warunkowe blokowanie komórek w excelu

Jeśli chcemy zablokować tylko wybrane komórki (warunkowe blokowanie) to wpierw należy wyłączyć domyślnie zablokowane wszystkie komórki naciskając kombinację CTRL+A  (zaznaczymy wszystkie komórki w arkuszu), następnie wyłączamy odblokuk komorki(widać znikło obramowanie wokół ikony).

Kolejny krok to wybieramy komórki do zablokowania, w naszym przykładzie zablokujemy komórki od D8:D20. Chcemy zablokować, są tam wprowadzone formuły i nie chcemy aby była możliwość ich modyfikacji.

warunkowe blokowanie komórek w excelu

Zaznaczamy komórki D9:D20 z meny „Narzędzia główne” wybieramy polecenie „Format” i z listy klikamy na polecenie „Zablokuj komórkę”.

warunkowe blokowanie komórek w excelu

W kolejnym kroku znów z  meny „Narzędzia główne” wybieramy polecenie „Format” i z listy klikamy na polecenie „Chroń arkusz„.

warunkowe blokowanie komórek w excelu

Pojawia się nam nowe okno, gdzie zatwierdzamy przyciskiem „OK„.

warunkowe blokowanie komórek w excelu

 

Możemy również w polu „Hasło do usunięcia ochrony arkusza:”  wprowadzić hasło, które zabezpieczy nas przed wyłączeniem ochrony wybranych komórek.

Efekt końcowy: w przypadku próby modyfikacji zablokowanych komórek pojawia nam się komunikat:

blokowanie_8

Przykład poniżej:

excel ochrona komórek

Miesieczne_koszty.xlsx

Miesieczne_koszty.xlsx

, , , ,

Excel,Triki

Funkcja przesuniecie

9 Sie , 2016  

Jedną z ulubionych moich funkcji w MS Excel jest funkcja PRZESUNIĘCIE. Można ją wykorzystać na wiele sposobów, najpopularniejszy sposób to dynamicznie zmieniający się wykres.

Efekt końcowy widać na poniższym rysunku:
przesuniecie_4

 

Co trzeba wiedzieć o funkcji aby móc z niej skorzystać?

Funkcja PRZESUNIĘCIE potrzebuje 5 parametrów, gdzie 2 są opcjonalne.

Składnia
PRZESUNIĘCIE(odwołanie;wiersze;kolumny;[wysokość];[szerokość])

Odwołanie – Argument wymagany. Konkretna komórka w Excelu, od której będzie wskazywany zakres komórek lub komórka.

Wiersze – Argument wymagany. O ile wierszy należy się przesunąć od komórki, aby dostać się do żądanego zakresu lub komórki.

Kolumny – Argument wymagany. O ile kolumn należy się przesunąć od komórki, aby dostać się do żądanego zakresu lub komórki.

Wysokość – Argument opcjonalny. Liczba wierszy, z których składa się zakres, który chcemy pobrać.

Szerokość – Argument opcjonalny. Liczba kolumn, z których składa się zakres, który chcemy pobrać.

Tyle teorii zaczynamy część praktyczną!

1. Najlepiej poniższą tabele skopiować do excel’a lub pobrać plik dane_przesuniecie.xlsm

Rok Messi Ronaldo
 2015/2106  41 51
 2014/2015  58  61
 2013/2014  41  51
 2012/2013  60  55
 2011/2012  73  60
 2010/2011  53  53
 2009/2010  47  33

 

2. Przechodzimy do menu Developer rozwijamy Wstaw i wybieramy narzędzie Przycisk pokrętła

przesuniecie_1

3. Stojąc kursorem na przycisku pokrętła, prawym klawiszem myszy wybieramy polecenie przesuniecie_2

przesuniecie_1

4. Pojawia się nam nowe okno gdzie w polu Łącze komórki wprowadzamy wartość: = $E$1, oraz ustawiamy wartość minimalną i maksymalną.

przesuniecie_3

Efekt końcowy – klikając na pokrętło zmienia nam się dynamicznie wartość w komórce E1.  Przycisk pokrętła jest już gotowy do wykorzystania go w funkcji przesunięcie.

5. Przechodzimy do funkcji przesunięcie, ustawiamy aktywną komórkę E2, i wprowadzamy w pasku formuł polecenie „=PRZESUNIĘCIE(A2;0;$E$1;1;1)” i naciskamy enter.

przesuniecie_4

 

Wyjaśnienie formuły:

przesuniecie_5

Żeby obrazowo wyjaśnić o co chodzi z tym zakresem przesuniecie_6 skopiujmy formułę z komórki E2 do E3.

 

 

W komórce E1 mamy wartość 1 wiec wyświetlają nam się dane z pierwszej kolumny od kolumny A.

przesuniecie_7

Jeśli zmodyfikujemy formulę w komórce E3 na: =SUMA(PRZESUNIĘCIE(A2;0;$E$1;1;2)), pamiętamy że ostatni parametr steruje szerokością. W tym przypadku podaliśmy 2 czyli 2 kolumny, wówczas efekcie końcowym będziemy mieli zsumowane dwie wartości 41 + 51 = 92

przesuniecie_8

Koniec wyjaśnień na temat formuły.

 

6. Stajemy na komórce E2 i przeciągamy formułę do komórki E9

przesuniecie_2

 

Kręcąc pokrętłem mamy efekt końcowy czyli zmieniają nam się wartości w kolumnie E. Wystarczy teraz zaznaczyć ten zakres plus zakres z kolumny A i wstawić wykres, który będzie zmieniał się dynamicznie.

przesuniecie_3

 

7. Do zmiany zdjęcia został wykorzystany prosty kod vba podpięty do pokrętła:

Gotowy plik do pobrania: przesuniecie.xlsm

 

 

, ,

Excel,Triki

Excel – konwertowanie liczb przechowywanych jako tekst

7 Gru , 2014  

Często zdarza się, że otrzymujemy dane z innego źródła, lub wklejamy dane do Excela i liczby nie są traktowane jak prawdziwe liczby. Próbując wykonać działania matematyczne na tych liczbach nie mamy zamierzonego efektu. Widok tych liczba, które są traktowane jako tekst widać poniżej:

Excel - konwertowanie liczb przechowywanych jako tekst

Jak wiadomo tekst domyślnie wyrównany jest to lewej strony komórki, zaś liczby do prawej. W lewym górnym roku pojawia się w Excelu pomocna funkcja, która umożliwia konwertowanie liczb przechowywanych jako tekst na liczbę:

Excel - konwertowanie liczb przechowywanych jako tekst

 

Excel - konwertowanie liczb przechowywanych jako tekst

Efekt końcowy jest widoczny poniżej, liczby przechowywanych jako tekst są już liczbami i można wykonywać na nich operacje matematyczne.

Excel - konwertowanie liczb przechowywanych jako tekst

Inna metodą jest wymnożenie wartości przez wartość 1 i liczby przechowywanych jako tekst zostaną przekonwertowane na liczby:

Excel - konwertowanie liczb przechowywanych jako tekst Excel - konwertowanie liczb przechowywanych jako tekst

 

,