W tym poście nauczymy się, jak wyciągnąć długość przedmiotu w centymetrach z nazwy produktu w Excelu.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Jak widać na rysunku powyżej mamy pełne nazwy produktów i wewnątrz nich znajduje się długość poszczególnych przedmiotów (50cm, 35cm itd.), czyli mamy podaną liczbę a za nią oznaczenie cm.
Od Excela 2013 możemy tą informację wyciągnąć za pomocą FlashFill, który działa niejako w tle. Wystarczy, że w pierwszym wierszu napiszemy 50, a w drugim 35. To już powinno wystarczyć, żeby wybrać polecenie Wypełnianie błyskawiczne z karty Dane (rys. nr 2). Ewentualnie możemy skorzystać ze skrótu klawiszowego Ctrl+E.
Rys. nr 2 – polecenie Wypełnianie błyskawiczne
Excel wyciągnie odpowiednie informacje i wypełni pozostałe wiersze, co widać na rys. nr 3.
Rys. nr 3 – działanie Wypełniania błyskawicznego
Jeśli nie masz jeszcze Excela 2013 z opcją FlashFill, to mamy dla Ciebie inne rozwiązanie. Trzeba użyć formuły. Skorzystamy tutaj z tego, że nasza długość ma maksymalnie 3 cyfry, ponieważ w takiej sytuacji łatwo jest wyciągnąć takie informacje. Możemy też zauważyć, że dane które chcemy wyciągnąć poprzedza spacja lub znak -. Pierwszym krokiem będzie odnalezienie tekstu "cm". Użyjemy do tego funkcji SZUKAJ.TEKST, która nie rozróżnia wielkości liter. Pierwszym argumentem funkcji jest szukany_tekst, czyli cm w podwójnych cudzysłowach. Drugi argument funkcji to obejmujący_tekst, czyli tekst, w którym chcemy znaleźć nasze znaki (A2). Zapis formuły powinien wyglądać następująco:
=SZUKAJ.TEKST("cm";A2)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy dane przedstawione na rys. nr 4.
Rys. nr 4 – wyniki funkcji SZUKAJ.TEKST
Otrzymaliśmy liczby, czyli pierwszą pozycję szukanego przez nas tekstu "cm" w całym ciągu znaków. My jednak nie chcemy wyciągać informacji od tego znaku, a przed tym znakiem. Chcemy się cofnąć i wyciągnąć informacje poprzedzające dwuznak cm. Wystarczy, że od tej pozycji, na której się zaczyna ciąg cm odjąć wartość 3, bo nasza liczna ma maksymalnie 3 znaki. Zapis formuły powinien wyglądać następująco:
=SZUKAJ.TEKST("cm";A2)-3
Otrzymamy nowe pozycje o 3 znaki wcześniej przedstawione na rys. nr 5.
Rys. nr 5 – nowa pozycja o 3 znaki wcześniej
Dopiero od tych zmodyfikowanych pozycji chcemy zacząć wyciągać tekst. Użyjemy do tego funkcji FRAGMENT.TEKSTU. Pierwszym argumentem funkcji jest tekst, czyli tekst z którego chcemy wyg=ciągnąć jakiś fragment (A2). Drugi argument funkcji to liczba_początkowa, czyli pozycja od jakiej chcemy zacząć wyciągać informacje (wynik poprzedniej formuły). Trzeci opcjonalny argument to liczba_znaków, czyli ile znaków chcemy wyciągnąć (3). Zapis formuły powinien wyglądać następująco:
=FRAGMENT.TEKSTU(A2;SZUKAJ.TEKST("cm";A2)-3;3)
Powyższą formułę zatwierdzamy. Otrzymamy wyciągnięte dane dotyczące długości poszczególnych produktów przedstawione na rys. nr 6.
Rys. nr 6 – wyciągnięte dane dotyczące długości produktów
Jak łatwo zauważyć na rysunku powyżej, przez to że wyciągnęliśmy 3 znaki, otrzymaliśmy część danych poprzedzonych minusem lub spacją. Musimy te dodatkowe znaki usunąć. Wystarczy dołożyć jedną funkcję. Zanim jednak to zrobimy, musimy zwrócić uwagę, że nasze dane to według Excela tekst, a nie wartości liczbowe, bo są wyrównane do lewej strony. Wynika to z użycia funkcji FRAGMENT.TEKSTU, która jest funkcją tekstową i zwraca tekst. Musimy użyć tutaj funkcji matematycznej MODUŁ.LICZBY, która konwertuje tekst na liczbę, jak również usuwa znak minus i spację sprzed liczby. Zapis formuły powinien wyglądać następująco:
W tym poście dowiemy się, jak wygenerować prosty losowy kod.
Do tego celu będziemy używać dwóch funkcji KOD i ZNAK. Argumentem funkcjo KOD jest tekst. Funkcja ta podstawia wartość numeryczną danego znaku. Zapis formuły powinien wyglądać następująco:
=KOD(C2)
Literce A przypisana jest wartość liczbowa 65, natomiast literce Z – 90. Wyniki funkcji zostały przedstawione na rys. nr 1.
Rys. nr 1 – wyniki funkcji KOD
Na podstawie tych numerków (wartości liczbowych przypisanych literom) za pomocą funkcji ZNAK, możemy znowu uzyskać literę. Czyli z liczby funkcja zwraca nam literę. Argumentem funkcji ZNAK jest liczba. Zapis formuły powinien wyglądać następująco:
=ZNAK(D2)
Otrzymamy wyniki przedstawione na rys. nr 2.
Rys. nr 2 – wyniki funkcji ZNAK
Teraz, kiedy poznaliśmy działanie funkcji KOD i ZNAK możemy zająć się wygenerowaniem losowego kodu. W Excelu mamy funkcje LOS.ZAKR, której pierwszym argumentem jest dół, czyli u nas wartość 65 dla literki A. Drugi argument funkcji to góra, czyli wartość 90 dla litery Z. Zapis funkcji powinien wyglądać następująco:
=LOS.ZAKR(65;90)
Funkcja ta będzie nam losowała dowolne liczby całkowite z podanego zakresu, czyli między liczbą 65 a 90. Klawiszem F9 możemy odświeżać kod, żeby pojawiały się nowe wyniki. Jeśli dodatkowo powyższą funkcję otoczymy funkcją ZNAK, to wylosowana liczba zostanie zamieniona na literę. Otrzymamy zapis formuły przedstawiony poniżej:
=ZNAK(LOS.ZAKR(65;90))
Przykładowy wylosowany kod został przedstawiony na rys. nr 3.
Rys. nr 3 – przykładowy wylosowany kod
Chcielibyśmy wygenerować tych znaków więcej, aby nasz kod składał się z kilku liter. Nie możemy tutaj użyć funkcji POWTÓRZ, ponieważ ona powtórzyła by ten sam znak. Nam zależy, żeby to były różne znaki. W tym celu musimy skopiować zapis funkcji i skleić ze sobą za pomocą znaków &. Zapis formuły dla trzech losowych znaków powinien wyglądać następująco:
Po zatwierdzeniu powyższej formuły i przeciągnięciu jej na komórki poniżej otrzymamy losowe kody przedstawione na rys. nr 4.
Rys. nr 4 – losowe kody z trzech znaków
Po każdorazowym naciśnięciu klawisza F9 kody wygenerują się ponownie.
Jeśli zależy nam, żeby te kody zostały na stałe to zaznaczamy odpowiedni zakres i klikamy prawym przyciskiem myszy. Następnie z podręcznego menu wybieramy ikonkę Wartość z Opcji wklejania (rys. nr 5).
Rys. nr 5 – Opcje wklejania
Po takiej czynności wygenerowane kody nie mają formuły, zostały wklejone same wyniki, których nie możemy odświeżać za pomocą klawisza F9.
Ponadto od Excela 2013 są nowe funkcje o nazwach UNICODE i ZNAK.UNICODE. Możemy je podejrzeć, jeśli zaznaczymy kolumnę Znak, klikniemy na nią prawym przyciskiem myszy, a następnie wybierzemy z podręcznego menu opcję Odkryj (rys. nr 6).
Rys. nr 6 – opcja Odkryj
Otrzymamy wyniki funkcji UNICODE i ZNAK.UNICODE przedstawione na rys. nr 7.
Rys. nr 7 – wyniki funkcji UNICODE I ZNAK.UNICODE
Dzięki tym nowym funkcjom mamy dużo więcej znaków do wyboru. Jednak do tworzenia prostych losowych kodów w zupełności wystarczą nam funkcje KOD i ZNAK.
W tym poście dowiemy się jak wstawić symbole we fragmentatorach.
Fragmentatory mogą pokazywać tekst, ale też różne symbole, z którymi możemy pracować, czy filtrować nasze tabele w Excelu lub tabele przestawne. Temat ten omówimy na podstawie przykładowych danych z rys. nr 1 .
Rys. nr 1 – przykładowe dane
W pierwszym kroku musimy sobie "przygotować" te symbole, których chcemy użyć, czyli wstawić je do Excela. Mogą to być znaczki z czcionki np. Calibri, Arial, ale może to być równie dobrze Unicode. Żeby wstawić symbol, rozwijamy polecenie Symbole (punkt 2 na rys. nr 2) z karty Wstawianie (punkt 1), następnie wybieramy polecenie Symbol (punkt 3).
Rys. nr 2 – wstawianie symbolu
Otworzy nam się okno Symbol, gdzie na karcie Symbole (punkt 1 na rys. nr 3), w polu Czcionka (punkt 2) wybieramy Arial, następnie w polu Podzbiór (punkt 3) wybieramy Kształty geometryczne. Pozostaje nam wybrać kształt, który nam się podoba (punkt 4) i dwukrotnie na niego kliknąć lub po jego zaznaczeniu zatwierdzić wybór klawiszem Wstaw.
Rys. nr 3 – okno Symbol
Analogicznie aby otrzymać strzałki, w oknie Symbol, wybieramy rodzaj Czcionki – Calibri, następnie w polu Podzbiór wybieramy Strzałki. Wybieramy interesującą nas strzałkę i klikamy ją dwukrotnie (rys. nr 4).
Rys. nr 4 – wybór konkretnego podzbioru dla czcionki Calibri
Trochę inaczej wygląda sytuacja dla czcionki Wingdings3, ponieważ ona tak jakby modyfikuje istniejące symbole. Jeśli tutaj klikniemy strzałki w górę i w dół, w komórce pojawią nam się zamiast strzałek litery p i q (rys. nr 5). Wynika to z tego, że czcionka Wingdings3 zamienia standardowe znaki na symbole geometryczne. Podsumowując, ż tą czcionką jest najwięcej problemów.
Rys. nr 5 – wstawienie znaków p i q zamiast strzałek w komórce (czcionka Wingdings3)
Wstawiliśmy wszystkie wybrane kształty w jedną komórkę, ponieważ później będziemy mogli kopiować te, których aktualnie będziemy potrzebować. Symbol z czcionki Wingdings3, jeśli zostanie wstawiony w komórkę sformatowaną ogólnie będzie pokazywał literkę. Aby pokazywał się kształt a nie litera, musimy rozwinąć typy czcionek dla tej komórki na karcie Narzędzia główne i wybrać czcionkę Wingdings3 (rys. nr 6).
Rys. nr 6 – wybór czcionki Wingdings3
Z tą czcionką będzie najwięcej problemów, aby użyć jej na fragmentatorze i w tabeli z danymi. Przy tworzeniu danych używamy prostych funkcji z testem logicznym, żeby sprawdzić czy wartości sprzedaży w danym roku wzrosły lub zmalały dla poszczególnych sprzedawców. Na tej podstawie, chcemy otrzymać strzałkę, która pokazuje kierunek w dół, jeśli sprzedaż zmalała lub w górę, kiedy sprzedaż wzrosła. Przy czcionce Wingdings3, kiedy chcemy edytować naszą formułę z funkcją za pomocą klawisza F2, to wszystkie litery są zamienione na znaczki i kształty co widać na rys. nr 7. W takim zapisie bardzo ciężko się zorientować, jaki kształt odpowiada jakiej literce.
Rys. nr 7 – formuła w czcionce Wingdings3
Na szczęście w pasku formuły jest zachowana oryginalna czcionka, w jakiej pisaliśmy formułę (rys. nr 8).
Rys. nr 8 – zachowana oryginalna czcionka w pasku formuły
Pozostaje nam do omówienia Unicode, który daje najwięcej możliwości. Od Excela 2013 dostępna jest funkcja ZNAK.UNICODE, dzięki której możemy numerek (liczbę) zamienić na znak. Są dostępne strony internetowe, na których można znaleźć potrzebne znaki Unicode.
Kiedy znajdziemy interesujący nas znaczek (symbol), wystarczy, że skopiujemy jego kod HTML i wstawimy go do funkcji ZNAK.UNICODE (rys. nr 9)
Rys. nr 9 – kod HTML znaku Unicode
We wcześniejszych Excelach np. 2010 wystarczy ręcznie zaznaczyć i skopiować znaczek za pomocą skrótu klawiszowego Ctrl+C i wkleić go bezpośrednio do komórki (Ctrl+V).
Teraz zajmiemy się wstawianiem fragmentatorów. Jeśli udało nam się powstawiać do tabeli odpowiednie znaki, to pomijając czcionkę Wingdings3, mamy bardzo proste zadanie. Mamy przygotowaną tabelę Excela, więc od Excela 2013 wybieramy polecenie Wstawfragmentator z karty Projekt tabeli (rys. nr 10).
Rys. nr 10 – polecenie Wstaw fragmentator
Otworzy nam się okno Wstawianie fragmentatora, gdzie zaznaczamy checkboxy przy nazwach kolumn, dla których chcemy otrzymać fragmentatory, następnie zatwierdzamy przyciskiem OK (rys. nr 11).
Rys. nr 11 – okno Wstawiania fragmentatora
Otrzymamy wstawione 4 fragmentatory (rys. nr 12).
Rys. nr 12 – wstawione fragmentatory
Jak widać na rysunku powyżej, trzy ze wstawionych fragmentatorów są prawidłowe. Problem pojawia się we fragmentatorze z czcionką Wingdings3. Fragmentator pokazuje litery p i q zamiast strzałek w górę i w dół. Aby dodatkowo rozróżnić poszczególne fragmentatory, możemy im zmienić czcionkę. Zaznaczamy wybrany fragmentator i na karcie Fragmentator wybieramy Styl kolorystyczny (rys. nr 13).
Rys. nr 13 – wybór stylu kolorystycznego fragmentatora
Po zmianie stylów dla wszystkich fragmentatorów otrzymamy dane przedstawione na rys. nr 14.
Rys. nr 14 – fragmentatory po zmianie kolorystyki
Pozostaje nam zmienić fragmentator z czcionką Wingdings3, żeby pokazywały nam się odpowiednie kształty, a nie litery. W tym celu klikamy prawym przyciskiem myszki na styl formatowania naszego fragmentatora (punkt 2 na rys. nr 15) na karcie Fragmentator (punkt 1), a następnie wybieramy polecenie Duplikuj (punkt 3).
Rys. nr 15 – Duplikowanie stylu fragmentatora
Otworzy nam się okno Modyfikowanie stylu fragmentatora, gdzie w polu Nazwa (punkt 1 na rys. nr 16) możemy nazwać nasz styl formatowania. Następnie w polu Element fragmentatora wybieramy opcję Cały fragmentator (punkt 2) i klikamy przycisk Format (punkt 3)
Rys. nr 16 – okno Modyfikowanie stylu fragmentatora
Otworzy nam się okno Formatowania elementu fragmentatora, gdzie w polu Czcionka musimy wyszukać czcionkę Wingdings3 i kliknąć na nią dwukrotnie myszką (rys. nr 17).
Rys. nr 17 – okno Formatowania elementu fragmentatora
Excel powróci do okna Modyfikowanie stylu fragmentatora, gdzie musimy jeszcze zmodyfikować Nagłówek, ponieważ zmieniając czcionkę dla całego fragmentatora w nagłówku również otrzymamy znaczki zamiast liter. W polu Element fragmentatora wybieramy Nagłówek, a następnie klikamy na klawisz Format (jak na rys. nr 16). Analogicznie otworzy nam się okno Formatowania elementu fragmentatora, gdzie w polu Czcionka ustawiamy np. czcionkę Calibri i zatwierdzamy przyciskiem OK (jak na rys. nr 17).
Excel powróci znowu do okna Modyfikowanie stylu fragmentatora, który zatwierdzamy przyciskiem OK. Teraz musimy zaznaczyć wybrany fragmentator i na karcie Fragmentator zmienić mu Styl formatowania na ten, który zmodyfikowaliśmy (jak na rys. nr . Otrzymamy fragmentator przedstawiony na rys. nr 18.
Rys. nr 18 – fragmentator po zmianie stylu formatowania
Dzięki temu, czego się dziś nauczyliśmy, możesz dodawać dowolne znaki do swoich fragmentatorów, a co za tym idzie tworzyć ciekawe dashboardy.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście nauczymy się jak wykorzystać aparat fotograficzny, czyli jak zrobić zdjęcie w Excelu.
Mamy kilka możliwości, pierwsza polega na kliknięciu ikonki aparat fotograficzny na pasku Szybkiego dostępu. Aby się nam pojawiała na tym pasku musimy ją dodać. W tym celu klikamy na strzałkę (punkt 1 na rys. nr 1), aby rozwinąć opcje Dostosuj pasek narzędzi Szybki dostęp, następnie wybieramy polecenie Więcej poleceń (punkt 2).
Rys. nr 1 – Dostosuj pasek narzędzi Szybki dostęp
Otworzy nam się okno Opcje programu Excel, gdzie w polu Wybierz polecenia z wybieramy z listy rozwijanej Wszystkie polecenia (punkt 1 na rys. nr 2). Przechodzimy na pole pod spodem i klikamy literki A i P, żeby przeskoczyć do polecenia Aparat Fotograficzny (punkt 2). Następnie klikamy przycisk Dodaj (punkt 3) i zatwierdzamy przyciskiem OK (punkt 4).
Rys. nr 2 – okno Opcji programu Excel
Na Pasku szybkiego dostępu pojawi się ikonka Aparat fotograficzny (rys. nr 3), zaznaczamy obszar, któremu chcemy zrobić zdjęcie i klikamy w to polecenie.
Rys. nr 3 – ikonka Aparat fotograficzny
Następnie klikamy w miejscu, gdzie chcemy, aby Excel wkleił nam zdjęcie zaznaczonego zakresu (rys. nr 4).
Rys. nr 4 – zdjęcie zaznaczonego zakresu
Obraz uzyskany za pomocą polecenia Aparat fotograficzny jest powiązany z zakresem, który zaznaczyliśmy. W pasku formuły widzimy odwołanie do tego zakresu, kiedy zaznaczymy ten obraz (rys. nr 5).
Rys. nr 5 – odwołanie do zakresu obrazu na pasku formuły
Jeśli zmienimy w jakiś sposób komórki z zakresu, któremu zrobiliśmy zdjęcie, zmieni się również nasze zdjęcie. Np. powiększając obrazek tulipana w zakresie, powiększy się też ten obraz na fotografii. Podsumowując, za pomocą polecenia Aparat fotograficzny otrzymujemy zdjęcie "na żywo".
Aby zmiany w zakresie nie wpływały na naszą fotografię, wystarczy, że po zaznaczeniu zdjęcia usuniemy zakres z paska formuły i zatwierdzimy to. Otrzymamy wtedy zdjęcie statyczne. Jeśli teraz wprowadzimy zmiany w naszym początkowym zakresie, nasza fotografia nie ulegnie zmianie.
Od Excela 2010 mamy prostszy sposób na zrobienie zdjęcia. Zaznaczamy interesujące nas komórki, klikamy skrót klawiszowy Ctrl+C, następnie klikamy prawym przyciskiem myszki w dowolnym miejscu, rozwijamy polecenie Wklej specjalnie (punkt 1 na rys. nr 6), a następnie wybieramy ikonkę Obraz (punkt 2) lub ikonkę Obraz połączony.
Rys. nr 6 – Polecenie Obraz z grupy poleceń Wklej specjalnie
Polecenie Obraz wkleja nam obraz zaznaczonego obszaru z danej chwili, a Obraz połączony jest obrazem z odwołaniem do konkretnych komórek. Podsumowując, jeśli zmienimy coś w zakresie wyjściowym po użyciu polecenia Obraz, nie zmieni się nasza fotografia. Jeśli natomiast użyjemy polecenia Obraz połączony, każda zmiana w zakresie będzie widoczna z naszym obrazie.
Do pokazania tych opcji używaliśmy Excela bez linii siatki. Aby je włączyć zaznaczamy checkbox przy poleceniu Linie siatki na karcie Widok (rys. nr 7).
Rys. nr 7 – polecenie Linie siatki
Kiedy mamy włączone linie siatki, to nasze obrazy "na żywo" (te uzyskane za pomocą polecenia Obraz połączony) będą pokazywać podwojone linie siatki, czyli linie siatki z zakresu źródłowego i linie, na których jest aktualnie usytuowany. Natomiast obraz uzyskany za pomocą polecenia Obraz nie będzie pokazywać żadnych linii. Widać to na rys. nr 8.
Rys. nr 8 – linie siatki na fotografiach uzyskanych za pomocą poleceń Obraz i Obraz połączony
W Excelu mamy jeszcze jedną możliwość tworzenia obrazów (fotografii) z zaznaczonego zakresu. Zaznaczamy obraz, który chcemy przedstawić jako obraz, następnie rozwijamy polecenie Kopiuj (punkt 2 na rys. nr 9) z karty Narzędzia główne (punkt 1) i wybieramy polecenie Kopiuj jako obraz (punkt 3).
Rys. nr 9 – polecenie Kopiuj jako obraz
Otworzy nam się okienko Kopiowania obrazu, gdzie w grupie poleceń Wygląd zaznaczamy checkbox Tak jak na wydruku (dzięki temu będą zachowane właściwości wydruku),następnie zatwierdzamy przyciskiem OK (rys. nr 10).
Rys. nr 10 – okienko Kopiowania obrazu
Teraz, aby pojawił nam się skopiowany obraz, musimy użyć skrótu klawiszowego Ctrl+V. Otrzymamy obraz przedstawiony na rys. nr 11.
Rys. nr 11 – skopiowany obraz Tak jak na wydruku
Co istotne, nasz obraz z zaznaczoną opcją Tak jak na wydruku spowodował, że nie widzimy linii siatki z obrazu źródłowego. Wynika to z moich prywatnych ustawień drukowania, gdzie mam wybraną opcję drukowania bez linii siatki. Ponadto drukarka nie drukuje ikonek z filtrami w poszczególnych kolumnach, dlatego są one niewidoczne.
Podsumowując, przedstawiliśmy sposoby na uzyskanie obrazu z zaznaczonych danych. Mogą one być połączone ze źródłowym zakresem bądź nie.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście nauczymy się jak zmienić systemy zapisów liczbowych.
W filmie Excel na luzie odcinek nr 6, przedstawiłem formułę Bila Szysza z okazji 1 kwietnia (prima aprilis). Formuła ta z liczb tworzyła słowa. Używaliśmy do tego funkcji PODSTAWA, która opiera się o systemy liczbowe. W wyżej wymienionym filmie pokazywałem jak to policzyć ręcznie, za pomocą różnych funkcji, takich jak: INDEKS, PODAJ.POZYCJĘ, ILE.WIERSZY (rys. nr 1).
Rys. nr 1 – podgląd rozwiązania odcinka nr 6 Excela na luzie
Pokazywałem ręczne rozwiązanie, bo nie udało mi się wtedy znaleźć funkcji, która przechodziła by z systemu 36-cio znakowego na standardowy dziesiętny. Temat ten omówimy na podstawie przykładowych danych z rys. nr 2.
Rys. nr 2 – przykładowe dane
Zajmiemy się dziś funkcjami PODSTAWA i DZIESIĘTNA, które dostępne są dla użytkowników od Excela 2013. Mamy nasz standardowy zapis dziesiętny, który ma 10 znaków (0,1,2,3,4,5,6,7,8,9). Istnieją też inne systemy, np. system dwójkowy, którego zapis przedstawiony został na rys. nr 3. W tym systemie używa się do zapisu liczb dwóch znaków: 1 i 0.
Rys. nr 3 – zapis systemu dwójkowego
Innym popularnym zapisem jest szesnastkowy, przedstawiony na rys. nr 4. W zapisie tym zaczynają się pojawiać litery jako cyfry.
Rys. nr 4 – zapis systemu szesnastkowego
W maksymalnym, dostępnym dla Excela 36-cio znakowym zapisie, również mamy litery (rys. nr 5).
Rys. nr 5 – zapis systemu 36-cio znakowego
W tym zapisie
mamy dostępne wszystkie litery z języka angielskiego (bez polskich znaków),
dzięki temu można w zabawny sposób budować sobie liczby.
Aby obliczyć
słowo PRIMA, możemy użyć funkcji DZIESIĘTNA. Pierwszym argumentem
funkcji jest liczba, czyli w naszym przykładzie komórka F2, która w tym
zapisie jest liczbą. Drugi argument funkcji to podstawa, czyli jakiego
systemu zapisu użyliśmy – u nas 36-cio znakowy. Zapis formuły powinien wyglądać
następująco:
=DZIESIĘTNA(F2;36)
Po zatwierdzeniu
formuły otrzymamy wynik przedstawiony na rys. nr 6.
Rys. nr 6 – liczba uzyskana z zapisu PRIMA dzięki funkcji DZIESIĘTNA
Analogicznie, aby otrzymać wynik dla zapisu APRILIS, wystarczy skopiować formułę na komórkę obok. Otrzymamy wtedy liczby, jakie w zapisie 36-cio znakowym utworzą podane słowa (rys. nr 7).
Rys. nr 7 – słowa PRIMA i APRILIS zapisane jako liczby
W Excelu istnieją funkcje, które zamieniają zapisy, ale zazwyczaj opierają się na podstawowych systemach (dwójkowy, ósemkowy, szesnastkowy czy dziesiętny), co widać na rys. nr 8.
Rys. nr 8 – funkcje zamieniające systemy zapisu bezpośrednio
Funkcja
DZIESIĘTNA radzi sobie z taką zamianą zapisu od 2 do 36 cyfr. Teraz mamy słowo
PRIMA w formie liczby w zapisie dziesiętnym. Naszym celem jest uzyskanie liczby
w zapisie ósemkowym. Użyjemy do tego funkcji PODSTAWA. Pierwszym
argumentem funkcji jest liczba, czyli komórka z liczbą, która chcemy
zamienić (F7). Drugi argument funkcji to podstawa, czyli ilość znaków w
systemie, u nas zmieniamy liczbę na zapis ósemkowy, więc musimy odwołać się do
komórki E8. Zapis formuły powinien wyglądać następująco:
=PODSTAWA(F7;E8)
Po
zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 9.
Rys. nr 9 – obliczenie zapisu ósemkowego liczby zapisanej w systemie dziesiętnym
Następnie
zapis w systemie ósemkowym chcemy przekształcić na zapis w systemie szesnastkowym.
Jeśli mamy informację, że to jest zapis ósemkowy i chcemy przekształcić go na
szesnastkowy to musimy zrobić dwa kroki. Najpierw musimy zamienić tą liczbę na
system dziesiętny za pomocą funkcji DZIESIĘTNA. Zapis jej wyglądać będzie następująco:
=DZIESIĘTNA(F8;E8)
Następnie
uzyskaną z funkcji DZIESIĘTNA liczbę, musimy zamienić na liczbę w systemie
szesnastkowym za pomocą funkcji PODSTAWA. Jako pierwszy argument wpisujemy
wynik funkcji DZIESIĘTNA, a drugi argument to odwołanie do komórki E9. Zapis
funkcji powinien wyglądać następująco:
=PODSTAWA(DZIESIĘTNA(F8;E8);E9)
Po
zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 10.
Rys. nr 10 – liczba w zapisie ósemkowym przekształcona na liczbę w systemie szesnastkowym
Mając stworzoną
formułę, możemy się pobawić tymi zapisami zmieniając ilość znaków w systemie z
16 na inne np. 2 czy 4.
Podsumowując,
dzięki funkcjom DZIESIĘTNA i PODSTAWA, możemy zmieniać zapisy liczbowe na systemy
o różnej ilości cyfr.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście nauczymy się jak odwrócić tablicę, czyli jak pokazać jej wartości od dołu do góry. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1 .
Rys. nr 1 – przykładowe dane
W naszych danych dla ułatwienia mamy wartości numeryczne (liczebniki od pierwszego do dziesiątego) i naszym zadaniem będzie ich odwrócenie, czyli uzyskanie wartości od dziesiątego do pierwszego.
Możemy to
zrobić zarówno w Excelu klasycznym jak i tablicowym poprzez budowanie sekwencji.
Na przykład możemy wpisać liczbę 10 w komórce B2, następnie liczbę 9 w komórce
B3. Potem wystarczy przeciągnąć ręcznie te wartości do końca tabelki. Otrzymamy
wartości w odpowiedniej kolejności przedstawione na rys. nr 2.
Rys. nr 2 – ręcznie przeciągnięte wartości liczbowe
My chcemy
jednak te wartości uzyskać poprzez funkcjonalności Excela, a nie wpisywanie
ręczne.
W klasycznym
Excelu możemy użyć funkcji ILE.WIERSZY. Argumentem tej funkcji jest tablica,
czyli zakres (tabela A2:$A$11). Istotne tutaj jest, że ostatnią komórkę zakresu
musimy zablokować bezwzględnie, natomiast pierwsza ma pozostać względna. Zapis
formuły powinien wyglądać następująco:
=ILE.WIERSZY(A2:$A$11)
Po
zatwierdzeniu formuły i przeciągnięciu jej na wiersze poniżej otrzymamy
numerację wierszy przedstawioną na rys. nr 3.
Rys. nr 3 – numeracja wierszy uzyskana dzięki funkcji ILE.WIERSZY
Jak widać
taki ciąg nie jest wstawiony ręcznie a za pomocą formuły Excela. Jeśli mamy
numery wierszy, które chcemy wyciągnąć, to wystarczy je wstawić do funkcji INDEKS.
Pierwszym argumentem funkcji INDEKS jest tablica, czyli zakres naszych
wartości z tablicy Lista ($A$2:$A$11). Musimy tutaj pamiętać, aby zakres argumentu
tablica zablokować bezwzględnie. Drugi argument funkcji to nr_wiersza,
czyli numery malejące uzyskane z funkcji ILE.WIERSZY. Zapis formuły powinien
wyglądać następująco:
=INDEKS($A$2:$A$11;
ILE.WIERSZY(A2:$A$11))
Powyższą
formułę zatwierdzamy klawiszem Enter i kopiujemy na wiersze poniżej. Otrzymamy numery
wierszy ustawione malejąco przedstawione na rys. nr 4.
Rys. nr 4 – numeracja wierszy uzyskana dzięki funkcji INDEKS
Teraz
zajmiemy się rozwiązaniem w Excelu tablicowym. Wykorzystamy tutaj funkcję SEKWENCJA,
która zwraca sekwencję liczb. Pierwszym argumentem funkcji SEKWENCJA są wiersze,
gdzie należy podać ilość wierszy jakie chcemy uzyskać (10). Formułę wpiszeZapis
formuły powinien wyglądać następująco:
=SEKWENCJA(10)
Po
zatwierdzeniu formuły otrzymamy sekwencję liczb przedstawioną na rys. nr 5.
Rys. nr 5 – wyniki funkcji SEKWENCJA
Przy takim
wypełnieniu funkcji otrzymaliśmy numery wierszy w kolejności rosnącej. Jeśli
chcemy uzyskać kolejność odwrotną, musimy podać funkcji SEKWENCJA więcej
argumentów.
Drugi
argument funkcji to kolumny, czyli ilość kolumn, jakie chcemy uzyskać (w
naszym przykładzie wpisujemy wartość 1 – jedna kolumna). Trzeci argument
funkcji to początek, czyli wartość, od której chcemy zacząć naszą numerację
(tutaj wartość 10). Czwarty argument funkcji to krok, czyli wartość o
jaką mają się różnić poszczególne wyniki. My chcemy uzyskać listę malejącą,
czyli w argumencie krok musimy wpisać ujemną wartość (-1). Zapis całej funkcji
powinien wyglądać następująco:
=SEKWENCJA(10;1;10;-1)
Powyższą
formułę zatwierdzamy i otrzymamy ciąg liczb malejący przedstawiony na rys. nr 6.
Rys. nr 6 – lista malejąca uzyskana z funkcji SEKWENCJA
Przy
większej ilości danych możemy mieć problem z określeniem ilości wierszy, jakie
chcemy otrzymać. Natomiast jeśli znamy tą ilość, funkcja SEKWENCJA będzie
dobrym wyborem. Ilość wierszy moglibyśmy podliczyć za pomocą funkcji ILE.WIERSZY.
Teraz
wystarczy, że tak jak w Excelu klasycznym dołożymy funkcję INDEKS (wstawimy
funkcję SEKWENCJA do funkcji INDEKS).
Pierwszym
argumentem funkcji INDEKS jest tablica, czyli zakres z tabeli Lista. W Excelu
tablicowym nie musimy pamiętać o zablokowaniu bezwzględnym zakresu tablicy,
ponieważ funkcja SEKWENCJA generuje określoną ilość wyników. Drugi argument
(nr_wiersza) to wartości uzyskane z funkcji SEKWENCJA. Zapis formuły powinien
wyglądać następująco:
=INDEKS(A2:A11;
SEKWENCJA(10;1;10;-1))
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki, które automatycznie rozlewają
się na określony zakres, przedstawione na rys. nr 7.
Rys. nr 7 – wyniki funkcji INDEKS
Istotne
jest, że formuła znajduje się tylko w pierwszym wierszu (komórka D2), natomiast
w pozostałych wierszach mamy tylko rozlane wyniki.
Jeśli masz
dostęp do nowych funkcji tablicowych, to śmiało powinieneś ich używać. Ponieważ
nie musisz pamiętać o kopiowaniu/ przeciąganiu formuły na odpowiedni zakres.
Przy formułach tablicowych wyniki same rozlewają się na odpowiednią ilość
komórek.
Ponadto
moglibyśmy zastąpić w formule funkcji SEKWENCJA, argumenty wiersze i początek
za pomocą funkcji ILE.WIERSZY. Sprawi to, że nasza formuła będzie dynamiczna
(zmiany w danych bazowych spowodują zmianę w wynikach całej formuły). Zapis
takiej formuły powinien wyglądać następująco:
Wykorzystaliśmy
funkcję tablicową, żeby pokazać, że funkcje z Excela klasycznego świetnie współpracują
z nowymi funkcjami tablicowymi. Ponadto użycie funkcji tablicowej sprawia, że
wyniki formuły automatycznie rozlewają się na odpowiedni obszar, nie musimy
kopiować formuły na pozostałe komórki.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
