1. LITERY.MAŁE. (ang. LOWER) Funkcja zmienia wszystkie litery z wielkich na małe. Wybieramy interesującą nas komórkę i potwierdzamy enterem. Jeśli wierszy do zmiany jest więcej, wystarczy przeciągnąć formułę w dół przy pomocy małego kwadracika w prawym dolnym rogu komórki. Na koniec warto zamienić formuły na wartości.
Zapewne w wielu plikach, na których pracujesz spotkałeś się z tajemniczymi znakami $, np. w zapisie: A$1. Albo tak: $A1 Albo jeszcze tak: $A$1 (to z pewnością najczęściej). Co to znaczy? Po co to? Zapewne zapis $A$1 jest Ci bliski – blokuje on komórkę. Ale te dwa poprzednie? Wszystkie powyższe zapisy to różne rodzaje adresowania komórek. Stosujemy je wbrew pozorom po to, aby ułatwić sobie życie. Można wyróżnić dwa zastosowania, w którym adresowanie komórek jest szczególnie przydatne: Powtarzalność pojedynczej wartości – masz dane, pozornie stałe, ale mogące się zmieniać, np. kurs CHF czy EUR. Na tych wartościach opierasz swoje obliczenia. Powtarzalność formuł – tworzysz formuły niemal identyczne, np. plan handlowców na poszczególne miesiące. Powtarzalność wartości, to np. sytuacja, gdy chcemy ustalić ceny cennikowe produktów. Potrzebujemy do tego cenę zakupu i narzut. Cena zakupu jest wyrażona w EUR, a potrzebujemy przeliczyć ją na PLN. Narzut jest procentem – my chcemy dodać konkretną wartość złotówkową do ceny zakupu, aby powstała nam cena cennikowa. Zarówno kurs EUR jak i narzut są u nas wspólne, czyli takie same dla wszystkich produktów. Tak wygląda ta sytuacja: Ustalanie ceny cennikowej Zauważ, że zarówno kurs EUR, jak i narzut to wartości używane przez inne formuły (takie samych dla nich wszystkich!). Na tę chwilę wynoszą 4,50 zł i 25%, jednak jutro ta sytuacja może się zmienić. Jeśli się zmieni – nie ma problemu – po prostu podmienimy wartości w żółtych komórkach i już! Oparte na nich formuły same się przeliczą :). Powtarzalność formuł, to np. sytuacja, gdy planujemy sprzedaż miesięczną dla przedstawicieli handlowych. Znamy plan roczny dla każdego przedstawiciela, znamy sezonowość sprzedaży (mówi nam o tym współczynnik) – ustalamy plan miesięczny, o tak: Ustalanie planu sprzedaży miesięcznej Można sobie pomyśleć, że w powyższym przykładzie, dla każdego handlowca, w styczniu stałą wartością jest współczynnik – 1%, dla lutego – 2%, dla marca – 4% itd. Daje nam to łącznie 12 oddzielnych, prawie identycznych formuł (plan roczny * miesięczny_współczynnik%), obliczających plan handlowców dla każdego miesiąca. Wygląda na to, że potrzebujemy oddzielnych 12 formuł, ponieważ współczynniki miesięczne są różne. Podobnie, jakbyśmy spojrzeli na tę macierz w drugą stronę: każdy plan roczny handlowca jest różny. Nie ma jednej wspólnej stałej wartości, ale jest wspólny wzór/schemat obliczania. Czyli formuła. Wzór jest taki: plan_roczny * współczynnik_miesięczny. Korzystając z odpowiedniego adresowania komórek napiszemy jedną formułę w komórce D8 i skopiujemy ją do pozostałych komórek. (Oczywiście nad taką tabelą trzeba będzie jeszcze popracować z zaokrągleniami, ale podstawę już mamy). Teraz zastanowimy się, którego adresowania komórek użyć w każdej z tych sytuacji i wreszcie: wytłumaczę Ci na czym ono polega. Kluczowe w zrozumieniu adresowania, jest uświadomienie sobie co tak naprawdę Excel robi podczas kopiowania formuł (w końcu będziemy kopiować formuły!): zachowuje on pewien schemat. Schemat ten (cały!) jest przenoszony identycznie zgodnie z kierunkiem kopiowania. Nadaje się to idealnie, gdy mamy do czynienia z sytuacją, kiedy mamy różne wartości komórek, np.: Różne wartości – brak dolarów Tutaj nie mamy dolarów, ponieważ wartości ceny zakupu i narzutu są różne dla każdego produktu. Formuła w kolejnym wierszu (wiersz 9), będzie więc korzystała z komórek z tego kolejnego wiersza (wiersza 9). Kolumny D i E, w których są cena zakupu i narzut, pozostaną bez zmian, ponieważ kopiujemy formułę w dół, więc schemat (dodawanie dwóch sąsiednich komórek) przesunie się w dół. I wszyscy zadowoleni: my i Excel. Prościzna. Powtarzalność wartości Sytuacja jednak zaczyna komplikować się, gdy mamy już pewną stałą wartość, czyli chcemy dopiero obliczyć cenę zakupu w PLN, na podstawie ceny w EUR i stałego dla wszystkich formuł kursu EUR. Gdybyśmy zastosowali proste kopiowanie formuł mnożącej cenę w EUR i kurs EUR (C8*C3) otrzymalibyśmy taki wynik: Skutek braku $… Pierwsza wartość jest OK, ale kolejne… nie :). Zobaczmy dlaczego: Przyczyna błędnych wyników Schemat się przesunął… Konkretnie: przesunął się w dół, czyli pobiera wartość ze złego wiersza dla czerwonej komórki (kursu EUR). Powinien nadal czerpać go z wiersza nr 3. Dla każdego produktu. I to trzeba jakoś Excelowi powiedzieć. Dochodzimy więc do sedna sprawy: to właśnie dolary ($) informują Excela co ma zostać zablokowane (nie mają nic wspólnego z walutą USD). A zasada jest taka, że dolary blokują to, przed czym stają. A mogą stać przed kolumną ($A1) lub przed wierszem (A$1). Lub czyli jest jeszcze trzecia opcja: przed wierszem i kolumną jednocześnie $A$1, czyli ta najczęściej spotykana opcja. Najczęściej spotykana, ponieważ w większości sytuacji wystarczająca i dodatkowo… łatwiej wstawić 2 dolary, niż jeden. Czemu? Ponieważ do wstawiania znaczka $ służy klawisz F4 na klawiaturze (uwaga na laptopy, gdzie może być konieczność użycia dodatkowo klawisza Fn: Fn+F4). Działa on tak: 1 x F4 → $A$1 2 x F4 → A$1 3 x F4 → $A1 4 x F4 → A1 Użycie dwóch dolarów blokuje całą komórkę, czyli zarówno wiersz jak i kolumnę. Dlatego w naszym przykładzie, zarówno kurs EUR, jak i narzut (C4: 25%) możemy tak właśnie zablokować. Choć oczywiście nie ma aż takiej potrzeby, ponieważ obie nasze formuły – obliczającą cenę zakupu w PLN i narzut – kopiujemy tylko w dół, zatem zmieniają się tylko wiersze, a nie kolumny. Natomiast szybciej nam wstawić dwa dolary niż jeden (wystarczy raz nacisnąć F4), więc jest to bardzo powszechna praktyka w przypadku, gdy mamy do czynienia ze stałą, powtarzającą się wartością. Takie formuły zatem tutaj pokażę. Formuła na obliczenie ceny cennikowej PLN (D8) może być taka: =C8*$C$3 A na obliczenie narzutu (E8) – taka: =D8*$C$4 Powtarzalność formuł Sytuacja jednak komplikuje się, gdy nie ma wspólnej wartości. Np. we wspomnianej wcześniej sytuacji ustalania miesięcznych planów sprzedaży dla handlowców: Ustalanie miesięcznego planu sprzedaży Aby obliczyć plan miesięczny dla handlowca (np. Nowak), należy pomnożyć jego plan roczny (C8: 7 400 zł) przez współczynnik sezonowości (D5: 1%). Ten schemat należałoby powtórzyć dla każdego handlowca. Jak jesteśmy sprytni, to zauważymy, że jeśli napiszemy 12 formuł (dla każdego miesiąca), to w sumie można zablokować w każdej z nich komórkę ze współczynnikiem (np. $D$5, potem $E$5, $F$5 itd.). Ale to nadal jest 12 formuł… Prawie identycznych formuł… Słabiutko… Trzy minut męczarni z szefem, który stoi nad Tobą i sapie… 😉 Nie ma co, trzeba szukać innego rozwiązania. I ono oczywiście jest :). Zauważ, że tutaj tylko pozornie nie ma części wspólnej, ponieważ nie ma jednej wspólnej wartości. Ale część wspólna jest w samej formule, czyli w samym WZORZE matematycznym, który stosujemy. Zobacz: Metoda krzyżyka Widzisz krzyżyk, który się utworzył? Ja to tak nazywam: metodą krzyżyka w namierzaniu danych, które należy zablokować. Tutaj są to: plan roczny, który zawsze, niezależnie od handlowca jest w kolumnie C. współczynnik sezonowości, który zawsze, niezależnie od miesiąca jest w wierszu 5. Są to zatem elementy, które są wspólne, więc należy je zablokować. Po wpisaniu takiej formuły do pierwszej komórki (D8): =$C8*D$5 i skopiowaniu jej do pozostałych, otrzymujemy taki wynik: Ustalanie planu sprzedaży miesięcznej: WYNIK Oczywiście w metodzie krzyżyka nie zawsze będziemy szukać krzyżyka. Krzyżyk jest w macierzach, z jaką niewątpliwie mamy tutaj do czynienia. Ale może to być tylko jedna z jego ramion: wiersz albo kolumna. Zawsze zaś będą to elementy wspólne. Metoda krzyżyka, moja ulubiona, świetnie się nadaje również w popularnej tabliczce mnożenia, która podobno jest często stosowana w testach sprawdzających excelowe umiejętności kandydatów do pracy: Krzyżyk w tabliczce mnożenia I takich przykładów można byłoby mnożyć i mnożyć… Jak widzisz temat adresowania komórek jest dość złożony. Jestem jednak pewna, że po przeczytaniu tego artykułu będzie Ci już łatwiej się w nim odnaleźć. Pamiętaj tylko, aby ćwiczyć i używać tego. Wiesz jak to jest, jak czegoś nie używamy… baaardzo szybko to zapominamy… A żeby było Ci łatwiej ćwiczyć, pobierz plik z zadaniami z tego artykułu: MalinowyExcel Szybkie blokowanie adresowanie komórek
Należy pamiętać przy tym, że symbole to rzecz umowna i można w opracowaniach znaleźć inną postać symbolu, jednakże w poniższym spisie przedstawiono najbardziej popularną postać danego symbolu statystycznego w odniesieniu do danej miary statystycznej. Symbol. Nazwa miary statystycznej. \ (N, n\) liczebność. \ (\sum\) suma. \ (M, m SPIS TREŚCI 1 Po co nam znaki dolara w Excelu?2 Co zrobić, żeby zablokować adresy komórek?3 Kiedy, w praktyce, najlepiej wstawiać dolary?4 Wygodny sposób na wstawianie znaków dolara w Excelu5 Wstawianie tylko jednego znaku dolara w Excelu Jednym z podstawowych narzędzi Excela są formuły, a wraz z nimi znak dolara. Wewnątrz formuły (czasem nawet bardzo prostej) możesz wskazywać wybrane komórki, przykładowo: „przemnóż zawartość komórki C5 przez zawartość komórki D5”. W poniższym przykładzie, C5 i D5 to adresy komórek. Przy tych adresach mogą czasem stać znaki dolara – $C$5 oraz $D$5 (rys. 1). Kiedy te dolary wstawiamy? Do czego to jest potrzebne? Co te dolary zmieniają? Na te pytania odpowiemy sobie poniżej. Rysunek 1. Przykład PRODUKTY. Po co nam znaki dolara w Excelu? Trzeba sobie uświadomić jedną rzecz – gdy przeciągamy formuły, to wraz z przeciągnięciem, zmieniają się odpowiednio adresy komórek użyte w tej formule. W przykładzie PRODUKTY, jeśli przeciągnę formułę 3 wiersze w dół, to adresy też zmienią się o 3 wiersze w dół (C5 zmieni się na C8, a D5 zmieni się na D8) (rys. 2). Jest to zupełnie normalne w Excelu, a wręcz często tego po prostu oczekujemy. Po co więc te dolary? Czytaj dalej… Rysunek 2. Wobrażmy sobie teraz inny przykład, gdzie analizujemy jeden konkretny produkt, który ma stałą cenę (420 zł) i liczymy jego sprzedaż w kolejnych miesiącach (rys. 3). Tutaj formułą będzie przemnożenie ilości przez cenę, czyli C5 mnożone przez G4. Rysunek 3. Przykład MIESIĄCE. Jeśli przeciągniemy taką formułe znów o 3 wiersze w dół, to C5 zmieni się na C8, a G4 na G7. Jak widać na rysunku 4, używanie komórki G7 jest bez sensu bo tam wcale nie ma ceny produktu. Zamiast G7 powinno być dalej G4. Na tym przykładzie widać, że nie zawsze chcemy, żeby wraz z przeciągnięciem formuły, przesuwały się adresy komórek. Rysunek 4. Co zrobić, żeby zablokować adresy komórek? Żeby G4 nie zmieniało nam się na G7, należy do G4 dodać $ (dolary). Na razie zróbmy to ręcznie, czyli: W przykładzie MIESIĄCE (rys. 5) wejdź do formuły w komórce D5 i zwyczajnie dopisz „$” (znak dolara) przed „G” i przed „4” i naciśnij ENTER (żeby zapisać zmiany w formule). Dużo więcej filmów z dolarów w Excelu Sprawdź za darmo gdzie znajdziesz wiele więcej zastosowań dla dolarów. Rysunek 5. Gdy przeciągniesz teraz formułę o 3 wiersze w dół, to G4 pozostanie dalej G4 (rys. 6). Rysunek 6. Wstawienie znaków dolara w Excelu przy adresie blokuje tę komórkę, czyli po przeciągnięciu formuły, komórka pozostaje niezmieniona. Zwróc uwagę na to, że C5 wciąż zamienia się na C8, więc możesz swobodnie wybrać, która z komórek ma być zablokowana. Kiedy, w praktyce, najlepiej wstawiać dolary? Gdy zaczynamy używać dolarów w adresach komórek, najczęściej problem sprawia rozpoznanie, w którym miejscu te dolary się przydadzą. Taką podstawową radą jest to, że blokujemy te komórki, których mamy jedną sztukę. Patrząc na przykład MIESIĄCE (rys. 6), ilości produktów mamy wiele (dla każdego miesiąca inna ilość), ale cena produktu jest tylko jedna (stała dla wszystkich miesięcy) – więc wstawiamy znaki dolara przy cenie. Poniżej przykład KURS (rys. 7), w którym przeliczamy kwoty w PLN na kwoty w USD. Mamy wiele kwot w PLN (B6, B7, B8, itd.), ale tylko jeden kurs wymiany tych walut (D4), więc dolary dodajemy przy kursie ($D$4). Rysunek 7. KURS. Wygodny sposób na wstawianie znaków dolara w Excelu Gdy chcesz wstawić dolary do adresu komórki, to zamiast wpisywać te dolary ręcznie, możesz użyć skrótu klawiszowego: Musisz mieć otwartą formułę. Jeśli jesteś w trakcie jej pisania, to siłą rzeczy masz formułę otwartą. Jeśli chcesz wrócić do formuły po jakimś czasie, żeby ją poprawić, to kliknij w komórkę dwukrotnie, albo zaznacz komórkę i wejdź w pasek formuły (wszystkie powyższe czynności dają ten sam efekt). Ważne jest, żeby być „wklikniętym” w formule przy adresie, który chcesz blokować, czyli ta pionowa migająca kreska musi znajdować się obok tego adresu (w przykładzie KURS, rys 7 – albo przed „D”, albo między „D” a „3” albo po „3”). Następnie, naciśnij na klawiaturze klawisz F4. Powinny się wstawić 2 dolary. Jeśli tak się nie dzieje, to prawdopodobnie masz na swojej klawiaturze włączone klawisze funkcyjne, więc musisz nacisnąc klawisz „fn” razem z „F4” (klawisz „fn” znajduje się obok lewego CTRL). Gdy przyzwyczaisz się do tego skrótu to zobaczysz jak bardzo ułatwia on pracę z dolarami w Excelu. Spróbuj nacisnąć F4 kilkukrotnie. Zobaczysz, że zmienia on sekwencję dolarów, czyli: Pierwsze naciśnięcie wstawia 2 dolary: $D$3. Drugie naciśnięcie wstawia 1 dolara przed liczbą: D$3. Trzecie naciśnięcie wstawia 1 dolara przed literą: $D3. Czwarte naciśnięcie usuwa dolary: D3. Kolejne naciśnięcia powtarzają tę samą sekwencję. Do czego przydają się D$3 albo $D3, czyli pojedyczne dolary? Czytaj o tym w dalszej części. Dużo więcej filmów z dolarów w Excelu Sprawdź za darmo gdzie znajdziesz wiele więcej zastosowań dla dolarów. Wstawianie tylko jednego znaku dolara w Excelu Wstawianie tylko jednego dolara w adresie komórki blokuje ją tylko częściowo. Aby wyjaśnić to, posłużę się przykładem CENNIK (rys. 8). W wierszach znajdują się kolejne produkty oraz ich ceny. Chce za pomocą formuły stworzyć cennik, czyli obliczyć ceny dla każdego z produktów, w przypadku różnych rabatów. Wielkość rabatu zależy od tego ile sztuk produktu zostało kupionych. Jeśli ktoś kupił do 5 sztuk, to rabat wynosi 1 zł, jeśli od 5 do 10 sztuk, to rabat wynosi 3 zł, jeśli od 10 do 15 sztuk, to rabat wynosi 5 zł, itd. Rabaty umieszczone są w kolejnych kolumnach. Rysunek 8. Przykład CENNIK. Podane rabaty (1 zł, 3 zł, 5 zł, itd.) to rabaty od ceny jednostkowej i takie ceny jednostkowe też chcemy obliczyć w tabeli – zobacz rysunek 9 (wyliczono ceny dla 2 pierwszych produktów). W D6 cena jest bez zmian, w E6 cena spada o 3 zł, w F6 cena spada o 5 zł, itd. Rysunek 9. Czym ten przykład różni się od poprzednich? Tym, że formułę będziemy przeciągać nie tylko z góry na dół, ale też z lewej do prawej. I to jest bardzo ważna różnica, która sprawia, że często będziemy używać nie podwójnych, a pojedynczych dolarów. Zacznijmy rozwiązywać to zadanie podobnie jak wcześniejsze, czyli wstawiam formułę, gdzie zapisuje dolary przy D5. Oczywiście formuła odejmje od ceny (C6) kwotę rabatu (D5) (rys. 10). Rysunek 10. Jeśli przeciągnę tę formułę tylko w dół (tak jak we wcześniejszych przypadkach), to wszystko wygląda dobrze, a wyniki są prawidłowe (rys. 11). Rysunek 11. Teraz wprowadzimy ten nowy element, czyli przeciągnięcie formuły w prawo. Przeciągną na razie tylko tę formułę z pierwszego wiersza. Widać, że C6 niepotrzebnie przesunęło się na G6, a D5 powinno przesunąć się na H5, ale tego nie zrobiło (rys. 12). Więc wszystko jest źle, włącznie z wynikiem formuły. Rysunek 12. Jak więc rozwiązać ten problem. Trzeba uświadomić sobie, że czasem chcemy w adresie komórki zablokować tylko wiersz, albo tylko kolumnę, a nie oba elementy jednocześnie. Tak jest w tym przykładzie: Gdy przeciągnęliśmy formułę w dół, C6 miało przesuwać się do kolejnych wierszy (C7, C8, C9, itd.), więc wiersza w C6 nie powinniśmy blokować (rys. 11) . Gdy przeciągnęliśmy formułę w prawo, to C6 zmieniło nam się na G6, a powinno pozostać dalej C6 (rys. 12). Czyli musimy zablokować kolumnę w C6, czyli zapisać $C6. Dolar blokuje zawsze „to przed czym stoi”, czyli $C6 zablokuje jedynie „C” (nie zablokuje „6”, czyli wiersza”) (rys. 13). Gdy przeciągnęliśmy formułę w dół, D5 nie miało przesuwać się do kolejnych wierszy (miało cały czas pozostać D5) (rys. 11), więc wiersz w D5 musimy zablokować, czyli zapisać D$5. Dolar blokuje zawsze „to przed czym stoi”, czyli D$5 zablokuje jedynie „5”. Gdy przeciągnęliśmy formułę w prawo, to D5 pozostało dalej D5, a powinno zmienić się na kolejne kolumny (D5, E5, F5, itd.) ( . Czyli nie możemy blokować kolumny w D5, czyli musimy pozostawić D$5 ( Rysunek. 13. Jeśli temat dolarów w Excelu jest dla Ciebie nowy, to blokowanie pojedynczymi znakami dolarów będzie na początku nieco kłopotliwe, więc nie zrażaj się tym, że nie od razu każdą formułę jesteś w stanie z miejsca perfekcyjnie zapisać. Wymaga to trochę ćwiczeń, czyli tworzenia kolejnych formuł. Jeśli chcesz poćwiczyć, oglądając filmy szkoleniowe i pobierając kolejne ćwiczenia, to zapraszam się do naszych kursów z Excela online, gdzie możesz za darmo przetestować tę platformę i przećwiczyć dolary w Excelu. Moja podpowiedź jest taka, aby zastanowić się z czego będzie Twoja formuła Excelowa zawsze korzystać: w CENNIKU (rys. 13) korzystasz z ceny produktu oraz z rabatu. Cena produktu jest zawsze w kolumnie C, a rabat zawsze w wierszu 5. Idę więc do formuły „=C6-D5” i w ciemno blokuję „C” oraz „5”. Powiązane wpisy poradnikoweAby podzielić sumę komórek od A1 do A10 przez 2 w Excelu, należy zastosować formułę: = SUMA (A1: A10) /2. Możliwe jest również wprowadzanie liczb bezpośrednio do formuły . Wprowadzając formułę w programie Microsoft Excel, można użyć "wskazania", aby wybrać komórki do odniesienia. Aby "wskazać" komórkę, kliknij ją podczas
W odpowiedzi na pojawiające się zapytania o dokładną zawartość kursu Excel – Nauka na przykładach, poniżej przedstawiam dokładny spis treści poruszanych zagadnień w samouczku. Kurs Excel jest udostępniany w formie elektronicznego pliku, tzw. e-booka. Sam decydujesz w jakim tempie się uczysz oraz jakie rozdziały interesują Cię w szczególności. Więcej informacji na temat kursu znajdziesz na stronie po kliknięciu w poniższe zdjęcie lub na stronie Podrecznik MS Excel Excel. Nauka na przykładach. Excel kurs – ebook Cały kurs online Excel składa się z 15 rozdziałów. Najpierw omawiane są kolejno podstawowe funkcje Excelowe, następnie pokazywane są kolejne przykłady wykorzystania bardziej zaawansowanych formuł. Przedstawiane są także wszystkie najważniejsze funkcjonalności Excela: tabele przestawne, możliwości formatowania warunkowego, liczne triki, skróty klawiaturowe, metody obliczeniowe. Dodatkowo jest oddzielny rozdział, który zawiera ponad 60 Excel zadań wraz z rozwiązaniami. Ćwiczenia te bazują na moim doświadczeniu zawodowym w największych firmach w Polsce. Rozdział ten pokazuje jak rozwiązywać konkretne problemy biznesowe za pomocą Excela w obszarze: sprzedaży, finansów, controllingu. marketingu, logistyki. Ten kurs Excela to prawdziwe kompendium wiedzy do samodzielnej nauki. W żadnym innym Excel podręczniku nie znajdziesz porównywalnego bogactwa materiału. Przy czym podręcznik zawiera maksimum przykładów, a minimum słów. To jest najprostsza metoda nauki Excela. Wystarczy raz prześledzić przykład, aby zrozumieć daną funkcję. Na stronie, której link został podanych powyżej, załączony jest film pokazujący samouczek od środka, rozdział po rozdziale. Można też tam pobrać darmowy fragment książki, aby sprawdzić, czy ten format prezentacji materiału Excela nam odpowiada. Samouczek powstał z myślą o wszystkich wersjach Excela, o tych najnowszych, a także dla starszych wersji programu, czyli Excel 2013, Excel 2010. Użytkownicy starszych wersji, nie będą widzieli paru formuł, zobaczą tylko ich opisy. Ale w 95% ta książka online dostosowana jest do wszystkich edycji arkuszy kalkulacyjnych. I cyklicznie jest aktualizowana o nowe funkcje, które są udostępniane w wersji Excel 365. W przypadku dodatkowych pytań dotyczących lekcji Excela online lub dedykowanych szkoleń dla firm zapraszam do kontaktu na email: sklep@ Excel. Nauka na przykładach – Spis treści Rozdział 1. Funkcje Excel MATEMATYCZNE i STATYSTYCZNE funkcja SUMA() funkcja ILOCZYN() funkcja MIN() funkcja MAX() funkcja ŚREDNIA() funkcja funkcja MEDIANA() funkcja PERCENTYL() funkcja funkcja # Współczynnik zmienności funkcja funkcja funkcja POTĘGA() funkcja PIERWIASTEK() funkcja MOD() funkcja # Marża na sprzedaży # Różnica MAX-MIN przez ŚREDNIĄ # Suma trzech największych wartości # Procentowa zmiana funkcja ( b – a ) / a # Zwiększenie / zmniejszenie wartości o x% # Średnia ważona # Zamiana brutto na netto Rozdział 2. Funkcje Excel DATA i CZAS funkcja DZIŚ() funkcja TERAZ() funkcja funkcja DZIEŃ() funkcja funkcja MIESIĄC() funkcja ROK() funkcja GODZINA() funkcja MINUTA() funkcja TEKST() funkcja RZYMSKIE() funkcja DATA() funkcja funkcja # Kwartał na podst. daty # Połowa roku na podst. daty # Zmiana zapisu czasu na minuty # Zapis daty w formacie RRRRMM # Dodawanie do daty miesięcy / lat # Obliczanie wieku na podstawie daty urodzenia # Zamiana nazwy miesiąca na liczbę # Wskazanie pierwszego dnia miesiąca wypadającego w … # Co robić, gdy pojawia się ##### zamiast daty? Rozdział 3. Excel Funkcje ZLICZAJĄCE funkcja funkcja funkcja funkcja funkcja funkcja POZYCJA() funkcja INDEKS() funkcja funkcja CZĘSTOŚĆ() funkcja # Średnia ważona 2 funkcja funkcja AGREGUJ() funkcja DŁ() # Zliczanie komórek zawierających … # Zlicz komórki zawierające # Czy komórka zawiera X lub Y? # Zliczanie unikalnych wystąpień Rozdział 4. Excel funkcje z WARUNKAMI funkcja JEŻELI() funkcja LUB() funkcja ORAZ() # Złożone warunki – łączenie funkcji LUB() i ORAZ() funkcja # Funkcja wielokrotnie zagnieżdżona # Sprawdzanie spełnienia warunku PRAWDA/FAŁSZ funkcja funkcja # z dodatkowymi warunkami Rozdział 5. Funkcje Excel WYSZUKUJĄCE i ŁĄCZĄCE DANE funkcja funkcja 2 funkcja funkcja funkcja # Zagnieżdżone # Wyszukaj po lewej stronie – połączenie funkcji INDEKS() & # Wyszukiwanie z wieloma warunkami funkcja WYBIERZ() # Wyszukanie ostatniej wartości w wierszu Rozdział 6. Funkcje PRZEKSZTAŁCAJĄCE DANE funkcja funkcja funkcja # Pierwsza litera z wielkiej litery pozostałe z małej funkcja LEWY() funkcja PRAWY() funkcja # Łączenie komórek poprzez & funkcja ZASTĄP() funkcja PODSTAW() funkcja TEKST() funkcja WARTOŚĆ() funkcja # Pobierz część tekstu od znaku „&” do końca komórki funkcja ZAOKR() # Zaokrąglanie cen – 9 lub 9,99 na końcu funkcja funkcja funkcja # Łączenie tekstu z wynikiem formuły # Czy wielka litera? # Czy fragment tekstu to cyfry? # Rozdzielenie imienia i nazwiska z jednej do dwóch komórek # Zliczanie liczby wyrazów w komórce # Tekst w nowej linii Rozdział 7. TRANSPOZYCJA DANYCH # Transpozycja poprzez opcję „Wklej specjalnie” funkcja TRANSPONUJ() # Transpozycja z odwróconą kolejnością Rozdział 8. WYKRYWANIE DUPLIKATÓW komórek w Excelu # Usuwanie duplikatów poprzez Menu # Usuwanie duplikatów poprzez filtrowanie zaawansowane # Usuwanie duplikatów poprzez tabelę przestawną # Zliczanie powtórzeń za pomocą funkcji # Wyróżnianie duplikatów poprzez formatowanie warunkowe Rozdział 9. Excel FORMATOWANIE WARUNKOWE # Formatowanie w zależności od wartości komórki # Automatyczne zaznaczenie 3 największych wartości # Porównywanie komórek – zaznaczenie różnic # Paski danych i skale kolorów # Wyróżnianie co drugiego wiersza innym kolorem # Wyróżnianie kolorem dat z najbliższego tygodnia # Wyróżnienie kolorem komórek zawierających … # Wyróżnienie kolorem największych i najmniejszych wartości Rozdział 10. ADRESACJA KOMÓREK funkcja KOMÓRKA() funkcja WIERSZ() funkcja # Litera kolumny funkcja # Adresowanie względne # Adresowanie bezwzględne i mieszane funkcja PRZESUNIĘCIE() Rozdział 11. Funkcje TABLICOWE w Excelu # Wartość MAX/ MIN z dodatkowym warunkiem # Suma ze złożonymi warunkami # Suma X największych wartości. # SUMA(JEŻELI()) # Zliczanie komórek spełniających kryteria # Zliczanie liczby znaków w wybranym zakresie komórek # Kilka kryteriów dla różnych kolumn # Znalezienie największej zmiany # Sprawdzenie, czy w którejś z komórek jest dana fraza # Czy komórka zawiera tylko dozwolone znaki z listy Rozdział 12. TABELE PRZESTAWNE w Excelu Jak utworzyć tabelę przestawną? Jak czytać tabelę przestawną? Do czego używać tabeli przestawnej? Główne ustawienia tabeli przestawnej. Obliczenia na polu wartości: Licznik, Suma, Minimum, Maksimum, Średnia, … Udziały %-owe. „Pokazywanie wartości jako”: % sumy końcowej, % kolumny, % wiersza, … Filtrowanie tabeli przestawnej. Pola obliczeniowe – dokonywanie obliczeń na jednym lub kilku polach tabeli. Układ danych – dowolne sterowanie położeniem wymiarów i miar. Ranking – wyznaczenie pozycji danej wartości na liście. Układ domyślny i klasyczny tabeli przestawnej. Grupowanie – kategoryzacja. Fragmentatory – szybkie filtrowanie danych. Wskazówki i porady przy pracy z tabelami przestawnymi. Rozdział 13. Excel TRIKI I WSKAZÓWKI Skróty klawiszowe. Pracuj szybciej i efektywniej. Dobierz domyślną kolorystykę skoroszytu. Atrakcyjna wizualizacja zwiększa czytelność. Zapewnij poprawność wprowadzanych danych. Nie trać czasu na późniejsze poprawki. Zamiana tekstu na kolumny. Przyśpiesz pracę na danych. Blokuj „przewijanie” wierzy lub kolumn, aby łatwiej pracować na większych tabelach. Szybkie kopiowanie formatowania komórek (kolory, czcionka, obramowanie,…). Zaznaczanie, modyfikowanie lub kopiowanie wartości tylko z widocznych komórek. Nazywanie zakresów danych. Przyspiesz pracę, gdy pracujesz na tym samym zbiorze danych. Ukrywanie 'siatki’ – tj. domyślnego obramowania komórek w Excel. Tworzenie serii danych. Przyspiesz wprowadzanie danych. Szybkie operacje na zakresie komórek: dodawanie, odejmowanie, mnożenie, dzielenie. Przyczyny najczęstszych błędów. Co robić, gdy formuła zwraca błąd? Ukrywanie niepotrzebnych kolumn, wierszy lub całych arkuszy. Ograniczenie wielkości arkusza. Sortowanie wartości po kolumnach – od lewej do prawej i na odwrót. Formatowanie niestandardowe. Dodanie znaku '+’ przed dodatnimi wartościami. Czyszczenie zakresu z formatowania lub całej zawartości. Dostosowywanie szerokości kolumn i wierszy do długości tekstu w komórkach. Grupowanie wierszy lub kolumn. Szybkie zaznaczenie tysięcy wierszy. Zaawansowane znajdowanie i zamienianie wartości. Zamiana na tysiące i miliony poprzez formatowanie. Scalanie komórek. Sortowanie kilku kolumn. Podział arkusza na rozdzielne okna. Podgląd paru otwartych arkuszy. Drukowanie tabel – powtarzanie nagłówków na każdej z drukowanych stron. Zapisywanie jako PDF kilku arkuszy jednocześnie. Szybsze i poprawne wprowadzanie formuł. Nazywanie kolejnych wersji pliku, aby łatwo było go odszukać w folderze. Przy większych tabelach zamieniaj formuły na statyczne wartości. Nazywanie pojedynczych wartości. Hiperłącza – linkowanie. Ustawienie częstotliwości autozapisywania. Rozdział 14. Excel ĆWICZENIA i ROZWIĄZANIA Analiza sprzedaży. Analiza dynamiki zmian – miesięcznych i kwartalnych. Agregowanie wartości sprzedaży według dni. Zliczanie wystąpień danej frazy z warunkami. Łączenie danych z różnych tabel, tworzenie nowych kategorii. Funkcja warunkowa wielokrotnie zagnieżdżona. Porównanie dwóch list danych – odszukiwanie elementów wspólnych. Wyszukiwanie wartości i tworzenie rankingów. Dopasowywanie wyników do wartości wskazanych na liście rozwijanej. Tworzenie listy wszystkich wartości spełniających wskazane kryteria. Zestawienia baz adresów email. Łączenie kolejnych komórek z kolumny w jeden ciąg znaków oddzielonych separatorem. Dodawanie znacznika, gdy komórka zawiera określoną frazę w tekście. Weryfikacja i przekształcanie wartości komórki. Zliczanie wystąpień kolejnych wartości. Sumowanie komórek o zakresie wskazanym przez użytkownika. Sumowanie wartości, które spełniają jednocześnie kilka warunków. Analiza zamówień. Kategoryzacja danych. Przekształcanie danych, zamiana walut. Przypisywanie kategorii na podstawie słów kluczowych w tekście. Analiza sprzedaży wg dni tygodnia. Wyszukiwanie ostatniej wartości. Zmiana układu tabeli. Wyszukiwanie na podstawie dwóch kryteriów. Wyliczanie premii, przygotowanie prognoz rocznych. Agregacja danych, wyliczanie średniej ważonej ceny produktu. Sumowanie wielu kolumn po spełnieniu wskazanych warunków. Wyszukiwanie wartości po wierszach i kolumnach. Zliczanie unikalnych wystąpień według wskazane kryterium. Ustalanie cen końcowych sprzedaży dla podanych warunków. Wyliczanie średniej ruchomej, wykrywanie dużych zmian wartościowych. Wyodrębnianie fragmentów tekstu i dokonywanie na nich obliczeń. Przekształcanie danych oraz tworzenie rankingu. Przypisywanie wartości w zależności od daty. Przypisywanie wartości w zależności od Rozdział daty od. i Rozdział daty do.. Zliczanie czasu pracy – czas regularny i nadgodziny. Podsumowanie wyników w obiekcie 'tabela Excel’. Zmiana ułożenia danych za pomocą funkcji. Tworzenie serii danych. Zliczanie okresu pomiędzy dwoma datami – statystyki efektywności. Zestawienie prognoz wyników z rzeczywistym wykonaniem – analiza odchyleń. Ranking sprzedaży produktów w kolejnych miesiącach. Sumowanie co drugiej, co trzeciej, … co szóstej kolumny. Przypisywanie wartości na podstawie licznych zmiennych Oznakowanie dublujących się rekordów na podstawie wartości z dwóch kolumn Rozliczenie grafika pracy przy pracy zmianowej Zliczanie wystąpień rekordów z daną frazą. Wydzielanie fragmentu tekstu. Zliczanie częstości występowania wartości. Sumowanie kolumn, które zostały wskazane w komórce. Tworzenie listy wartości spełniających warunek. Przypisywanie najbliższej wartości spełniającej warunek. Wyszukiwanie wartości i obliczanie z nich średniej. Podsumowanie wyników sprzedaży według krajów (tabele przestawne). Agregacja danych, wyliczanie udziałów procentowych (tabele przestawne). Agregacja danych – układ wierszowy (tabele przestawne). Kategoryzacja danych i użycie ich w tabeli przestawnej. Tworzenie rankingu produktów oraz obliczanie dynamiki zmian (tabela przestawna). Rozkład zamówień według dnia tygodnia oraz godziny (tabela przestawna). Opracowanie listy TOP 3 produktów wg krajów (tabela przestawna). Szablon do wypełniania faktur. Formularz ocen – przypisywanie ocen w zależności od spełnienia kryteriów. Wyliczanie średniej, min., max., w zależności od innych warunków.
Co oznacza w formule Excel? Operator @ w odwołaniach strukturalnych w tabelach programu Ms Excel. Operator @ zastępuje operator # ten wiersz – dotyczy komórek w tym samym wierszu, co formuła, tak jak odwołanie wzgędne Jedynie w sytuacji, gdy tabela składa się tylko z jednego wierszamoże pojawić się # również w najnowszych wersjach.Opowiem ci na przykładzie, dobrze?Powiedzmy, że w wierszu 3 masz takie nagłówki: ciuchy, cena netto, cena bruttoI pod nimi w kolumnie A masz buty, sukienkę, żakiet, torebkę, kolumnie B masz ceny netto: 120, 150, 140, 100, komórce B1 masz stawkę VAT na odzież i dodatki. 23%W komórce C4 masz formułe obliczającą cenę brutto:= B4 * B1I jak ją przeciągniesz w dół, to w komórce C5 będziesz mieć formułę:= B5 * B2Komórkę B2 masz pusta, więc cena brutto wychodzi ci 0A ty chcesz, zeby ci sie VAT dobrze więc w C4 formułę:= B4 * B$1I jak ją przeciągniesz w dół, to w komórce C5 będziesz miec formułę:= B5 * b$1A przy pasku= B8 * B$1$ wskazuje: przy kopiowaniu formuły nie dodawaj +1 do numeru wiersza, tylko przepisz na $B1 będzie oznaczało: przy kopiowaniu formuły w bok usztywnij wskazanie kolumny$B$1 oznacza, że przy kopiowaniu formuły w dowolną stronę (dół, bok) dana ze wskazanej komórki będzie to w arkuszu, to zrozumiesz.Uwagi: We wzorze D4 zawiera wartość wyszukiwania; B3: B7 to zakres komórek zawierający szukaną wartość; numer 0 oznacza, że szukasz dokładnej wartości dopasowania. Przykład 2: Funkcja MATCH dla przybliżonego dopasowania. Ta sekcja mówi o używaniu funkcji PODAJ.POZYCJĘ do wyszukiwania przybliżonych dopasowań w programie Excel.Istnieje kilka sposobów ukrywania wartości błędów i wskaźników błędów w komórkach. Istnieje wiele powodów, dla których formuły mogą zwracać błędy. Na przykład dzielenie przez 0 nie jest dozwolone, a po wprowadzeniu formuły =1/0, funkcja Excel zwraca wartość #DIV/0. Do wartości błędów należą: #DIV/0!, #N/A, #NAME?, # ko8RGx5. 33327339366471348462210108
