rozwiń filtry »

Wyszukiwanie publikacji

Porady

maj
21

Praca z Excelem - 100 porad i tricków (3/5)

21.05.2015 13:35, dodał: Krzysztof Cyło
Ocena:

Wracamy z naszym cyklem poświęconym programowi Excel. Obiecaliśmy Wam 100 przydatnych porad i tricków i słowa dotrzymujemy. W tej części kursu zaczniemy pracować na formułach.

To już trzecia odsłona naszego cyklu. Do tej pory - streszczając - mówiliśmy już sobie o poruszaniu się w arkuszu, formatowaniu tekstu, pracy na datach, tabelach i wykresach. Porady dotyczące tych zagadnień, jak i wielu innych, znajdziecie pod niżej wymienionymi linkami. Dzisiaj przejdziemy już jednak do kolejnych tematów.

Praca z Excelem - 100 porad i tricków (1/5)

Praca z Excelem - 100 porad i tricków (2/5)

Jak na razie starałem się unikać w naszym poradniku wprowadzania zbyt wielu formuł - chciałem zachować pewien ciąg opisywanych zagadnień. Nie zawsze było to możliwe, stąd też o kilku mieliście okazję poczytać w pierwszej lub drugiej części cyklu. Dzisiejsza odsłona - i kolejne - poświęcona już będzie jednak temu zasadniczemu obszarowi pracy z Excelem. Moim założeniem było, wprowadzić Was w mechanizmy rządzące arkusze kalkulacyjnym, nauczyć jak się w nim poruszać, by dopiero z czasem przejść do formuł. W tym artykule zaczniemy od tych najbardziej przydatnych - oczywiście wedle mojego widzimisię ;-) Będzie trochę o funkcjach logicznych, służących wyszukiwaniu danych i niestandardowemu sumowaniu. O funkcjach statystycznych i finansowych powiemy sobie w kolejnej odsłonie. Ten artykuł zaczniemy jednak od kilku - może oczywistych - wskazówek odnośnie pracy na formułach w ogóle. Zanim przejdziemy do samych porad, chciałbym jednak poczynić jeszcze pewne zastrzeżenia:

1. Jak zapewne zauważyliście, pracuję na wersji Office'a 2010 (a w tym artykule również miejscami na wersji 2007). Z tego względu, że w wersji 2013 wprowadzono pewne zmiany, zwłaszcza mam tu na myśli rozlokowanie pewnych funkcji w interfejsie, a zatem i zmianę ich skrótów klawiszowych, postanowiłem zrezygnować z przedstawiania tych ostatnich - na pewno jednak dowiecie się, jak daną funkcję zlokalizować! :-)

2. Przydatność lwiej części porad może nie być do końca widoczna na przytaczanych przykładach. Wynika to z tego, że Excel stworzony jest do pracy na dużych bazach. Poszczególne funkcje sprawdzają się właśnie wtedy, gdy mogą zautomatyzować pracę, która "manualnie" zajęłaby połowę życia. Przydatność wzrasta również wraz z nabyciem biegłości w łączeniu formuł i funkcji. Kilka ciekawych połączeń postarałem się Wam zaprezentować.

3. W tej odsłonie dołożyłem starań, by porady miały bardziej zróżnicowany poziom trudności. Dajcie proszę znać w komentarzach, czy taka forma Wam odpowiada.

A teraz przejdźmy już do samych porad. Zacznijmy od czegoś na rozgrzewkę.

Praca z formułami

#41 Inspekcja formuł

Stopień zaawansowania waszego arkusza zależy w dużej mierze od Waszej znajomości różnych formuł. W Excelu naprawdę niewiele jest rzeczy, które trzeba robić mechanicznie, manualnie - prawie na wszystko jest jakiś sposób. Dobra ich znajomość nie tylko przyspieszy waszą pracę, ale i sprawi, że arkusz będzie czytelniejszy i wymagał mniejszego nakładu pracy.

Celem korzystania z formuł niekoniecznie jest wykorzystywanie formatki Formuły, z górnego paska. Ich załączenia odbywa się po poprzedzeniu tekstu znakiem "=". Z tego względu nie wszyscy mogą być świadomi, jakie dodatkowe funkcje znajdują się w tej formatce.

Szczególnie przydatne są przyciski Pokaż formuły Śledź zależności. Pierwszy rozwinie każdą komórkę, w której znajduje się jakaś formułą, pokazując jej treść. Druga natomiast wskaże strzałkami komórki, w których znajdują się formuły wykorzystujące wskazaną komórkę. W tej sekcji znajdziecie też kilka innych, przydatnych funkcji. Wasz szef z pewnością z nich korzysta sprawdzając waszą pracę ;-)

#42 Co zrobić, gdy rozwinięcie formuły zakrywa przydatne komórki?

Jest to porada odpowiadająca na "problem pierwszego świata", czyli taki, który nie jest zbyt poważny, ale jednocześnie potrafi rozdrażnić. Poniżej widzicie przykład - pracuję właśnie nad formułą, która zakrywa mi dwie komórki po lewej stronie (B7 i C7).

Chcę natomiast ich użyć w obliczeniach. Mogę oczywiście wpisać zakres ręcznie, lub zaznaczyć komórkę przylegającą i nakierować strzałką na właściwą. Być może szybsze okażę się jednak przeniesienie rozwinięcia na drugą stronę - zwyczajnie wystarczy wyrównać zawartość komórki z formułą do przeciwnej krawędzi (w moim przypadku do lewej).

#43 Stałe odwołanie do jednej komórki - $

Również ta porada będzie miała stosunkowo elementarny wydźwięk, ale z mojego doświadczenia wynika, że wciąż wiele osób nie zna zastosowania znaku "$" w formułach. Załóżmy, że mamy taki przykład - poniżej wypisałem kilka produktów, którym w kolejnej komórce chce przypisać obliczoną kwotę VAT-u. Ten przykład jest o tyle niepraktyczny, że wystarczyłoby przemnożyć pierwszą wartość przez wpisaną na twardo wartość 0,23 i po prostu przeciągnąć tak skonstruowaną formułę do kolejnych komórek. Dla celów dydaktycznych załóżmy, że stawkę VAT-u chcemy jednak pobrać z tabeli obok.

W tym celu zaczynamy pisać naszą funkcję - kwotę netto z komórki C, przemnażamy przez VAT z komórki H5. Gdybyśmy teraz przeciągnęli taką formułę, również w dół przesuwałaby się komórkę mnożenia z VAT-em (do H6, H7, itd.). Nam zależy, by niezależnie od ilości wierszy, zawsze wartość netto z komórki C była mnożona przez komórkę H5. W tym celu, wystarczy wpisać znak "$" przed literą kolumny i numerem wiersza. W ten sposób: $H$5. Szybszym sposobem jest, mając w formule kursor na komórce, którą chcemy zablokować, wcisnąć klawisz F4.

#44 Formuła dla całego wiersza / kolumny / tabeli - jest na to skrót

Wszystko w Excelu da się robić szybciej. Załóżmy teraz, że chcemy zsumować kwotę netto i VAT-u, uzyskując w ten sposób kwotę brutto. Jednym z najczęściej używanych przeze mnie skrótów jest ALT + +, który do wskazanej komórki wstawia formułę sumy. Jeżeli skrót ten wciśniemy w kolumnie E, wówczas program automatycznie zlokalizuje wartości, które w tym miejscu powinien dodać. Podobnie chcemy sobie zsumować kwoty netto i VAT-u.

Chcemy teraz formułę sumującą kwotę netto i VAT przenieść także do pozostałych wierszy. Możemy użyć do tego myszki i przeciągnąć ją wzdłuż zakresu (przeciągając, gdy kursor zamieni kolor na czarny) lub też - co zalecam - użyć skrótu klawiszowego.

Zaznaczamy (SHIFT + strzałki) zakres komórek, w których chcemy wprowadzić sumy. Następnie wystarczy wybrać skrót CTRL + D (down), by funkcja ta została przeniesiona. W przypadku wierszy, zaznaczamy obszar w prawo, a następnie wciskamy CTRL + R (right).

W naszym przykładzie posługujemy się wyłącznie sumą, stąd jest jeszcze szybszy sposób na zsumowanie za jednym zamachem wierszy i kolumn. Wystarczy zaznaczyć cały zakres danych (kwoty netto i VAT), wraz z pustą kolumną po prawej (miejsce kwoty brutto) i pustym wierszem poniżej, a następnie wcisnąć ALT ++. Puste komórki wypełnią się sumą zaznaczonego obszaru - wiersza lub kolumny.

#45 Wypełnianie luk

Mamy arkusz jak poniżej. W jednej kolumnie (ale tylko przy pierwszy wierszu) widnieje nazwa firmy, w drugiej kwartał, w trzeciej wielkość sprzedaży. Często w zbliżony sposób wyglądają pliki wyeksportowane z zewnętrznych źródeł. Istnieją jednak pewne bariery w pracy na tak skonstruowanych danych. Dobrze by było, gdyby do każdego wiersza była przypisana również nazwa spółki - to ułatwi dopasowywanie danych z danego kwartału, danego przychodu, do odpowiadającej mu spółki. Wystarczy zaznaczyć  w kolumnie A zakres czterech komórek, a następnie wcisnąć znany Wam już skrót - CTRL + D. Wiersze poniżej automatycznie uzupełnią się danymi. To samo należy zrobić z pozostałymi. Jest to o wiele szybszy sposób, niż kopiowanie i wklejanie.

Na tego typu problemy jest jednak jeszcze szybsze rozwiązanie. Należy zaznaczyć obszar z pustymi miejscami, które mają zostać uzupełnione (A4:A15), a następnie wcisnąć klawisz F5. Stamtąd ścieżka kliknięć jest następująca:

Specjalnie -> Wybierz - puste.

Mając zaznaczone wszystkie puste pola, wystarczy teraz tylko w pierwszej pustej komórce od góry wpisać znak "=", ze wskazaniem komórki powyżej, czyli tą, której treść ma zostać do niej wklejona. Następnie skrótem CTRL + ENTER wprowadzimy tę komendę do wszystkich pustych komórek, które będą pobierały zawartość bezpośrednio z komórki nad nimi.

#46 TAB - wybieraj funkcje bez użycia myszki

Wpisując w dowolnej komórce znak "=", a następnie pierwsze litery pożądanej formuły, Excel zacznie rozwijać listę wszystkich formuł rozpoczynających się od wybranej litery. Do tej pory zapewne spora część z Was formuły wybierała dwukrotnym kliknięciem myszki, jednocześnie frustrując się, że wciśnięcie klawisza "enter" nie wprowadziło jej do arkusza. Klawiszem, który wprowadzi formułę do komórki jest w Excelu TAB.

#47 Tabela danych - tabliczka mnożenia

Ta i kolejna porada dotyczyć będzie tworzenia tablicy uwzględniającej wyniki formuły w różnych kombinacjach zmiennych. Zacznijmy od prostszej wersji - tabeli danych, która mnożyć będzie wartości przypisane kolumnom i wierszom, w sposób, by każda wartość z wiersza była przemnożona przez każdą wartość z kolumny, tworząc tabelę z kombinacją wyników.

Zacznijmy od puste jeszcze tabeli. W kolumnie B podana jest wielkość sprzedaży, a w wierszu ósmym możliwe ceny. Chcemy poznać wielkość przychodu w przypadku kombinacji poszczególnych pozycji z kolumny B do cen z wartości 8.

W tym celu zaznaczamy obszar na szaro - w nim dokonywane będą obliczenia. Następnie zaznaczamy pierwszy obszar, który chcemy mnożyć, tj. ceny. Po tym wstawiamy znak mnożenia i zaznaczamy drugi zakres - ilości.

Mając taką funkcję i zaznaczony obszar, w którym chcemy, by ona się znalazła, należy uważać, by odruchowo nie wcisnąć ENTER, gdyż to spowoduje wpisanie formuły jedynie do zaznaczonej komórki. Skrót, który wypełni całą tabelę, to CTRL + SHIFT + ENTER. Jest on również niezwykle użyteczny w pracy na macierzach.

W ten sposób uzyskaliśmy prostą tabliczkę mnożenia. Co jednak, gdy w tabeli chcemy zawrzeć wyniki bardziej skomplikowanej formuły, z różnymi kombinacjami zmiennych?

#48 Analiza symulacji

Zakładamy firmę. Celem sprawdzenia, na jakim poziomie sprzedaży (i przy jakiej cenie) wyjdziemy na zero, a przy jakiej kombinacji tych zmiennych zaczniemy zarabiać - przy określonym poziomie kosztów stałych - możemy wyprowadzić prosty wzór funkcji zysku. W postaci formuły widzicie tę funkcję poniżej, w tym miejscu szybko rozpiszę, na czym polegać będzie przykład.

Wychodzimy od przychodu (ILOŚĆ x CENA), od tego odejmujemy koszty zmienne przypadające na każdą jednostkę sprzedaży (ILOŚĆ x KOSZTY ZMIENNE) i od tego odejmujemy jeszcze koszty stałe.

Zmieniając w arkuszu (komórki C6 i C7) parametry ILOŚĆ i CENA dojdziemy do różnych kombinacji wyniku (zysku lub straty). Naszym zadaniem jest jednak zaprezentowanie wszystkich możliwych kombinacji w postaci tabeli. W tym celu przepiszemy sobie naszą funkcję w miejsce komórki B11 z opisem wierszy i kolumn. Wykorzystamy na razie dokładnie ten sam zakres danych, pomijając tabelę.

Zaznaczmy teraz całą tabelę (tak, by w rogu zaznaczenia była nasza formuła). Następnie przejdziemy do zakładki Dane -> Analiza symulacji (Office 2007)/ Analiza warunkowa (Office 2010) -> Tabela danych. W nowym oknie dialogowym należy zdecydować, co jest obszarem wyjściowym dla wierszy - u nas jest to CENA (komórka C7), a co dla kolumn - u nas ILOŚĆ (C6). Po wybraniu tych opcji wciskamy ENTER.

Tabela sama się uzupełni. Można z niej wyciągnąć kilka wniosków - przy cenie 2, będziemy musieli sprzedać najmniej 80 sztuk, by przy określonym poziomie kosztów stałych, wyjść na zero. Z kolei przy cenie 3,50 wystarczy już tylko 40 sztuk.

Strona:






Polecane artykuły

Testy

Poradniki

Tagi

 android   apple   ciekawostki   gaming   google   gry   internet   komputery   kosmos   microsoft   mobilne   nauka   polska   samsung   smartfon   smartfony   tablet   technologia   telefony   windows 

Korzystamy z plików cookies i umożliwiamy zamieszczanie ich osobom trzecim. Pliki cookie pozwalają na poznanie twoich preferencji na podstawie zachowań w serwisie.
Uznajemy, że jeżeli kontynuujesz korzystanie z serwisu, wyrażasz na to zgodę. Poznaj szczegóły i możliwości zmiany ustawień w Polityce Cookies.