Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
W internecie znajdziesz wiele stron pozwalających wygenerować losowy PESEL. Ponieważ skupiam się na Excelu, dlatego postanowiłem zrobić taki generator w Excelu (przy okazji również generatory NIP i REGON).
Żeby wygenerować losowy PESEL musimy znać jego strukturę. Większość z nas wie, że pierwsze 6 cyfr odpowiada za datę urodzenia. Już nie wszyscy wiedzą, że numery miesiąca zmieniają się w zależności od stulecia. Nas interesuje tylko XX wiek – lata 1900–1999 oraz XXI wiek – lata 2000–2099. XX wiek nie zmienia nic w numerowaniu miesięcy, ale XXI wiek dodaje liczbę 20 do numeru miesiąca w roku. Czyli osoba urodzona w 1910-05-23 i 2010-05-23 będzie miała początek PESEL odpowiednio 100523 i 102523.
Jak to zrobić w Excelu? Przede wszystkim potrzebujemy wylosować datę urodzenia. Świetnie się tu sprawdza funkcja LOS.ZAKR. Której wpisujemy w cudzysłowach daty minimalną i maksymalną pomiędzy którymi chcemy losować:
=LOS.ZAKR("1900–01-01";"2010–01-01")
Zanim zaczniemy przekształcać odpowiednio tą datę wylosujmy jeszcze płeć naszego posiadacza PESEL (od tego zależy 10 cyfra PESEL). Posłużymy się tutaj funkcją INDEKS, której na stałe wpiszemy tablicę liter ‘k’ i ‘m’ i będziemy losować albo 1 albo 2 pozycję tablicy (funkcja LOS.ZAKR):
=INDEKS({"k";"m"};LOS.ZAKR(1;2))
Teraz zacznijmy przekształcać datę. Będziemy musieli ją podzielić na część odpowiedzialną za rok, miesiąc i dzień. Rok będzie najprostszy, bo wystarczy odpowiednio sformatować wylosowaną datę jako rok za pomocą funkcji TEKST:
=TEKST([Data];"rr")
Powyższy zapis wstawia tylko 2 ostatnie cyfry z roku. Teraz przyszła pora na miesiąc. Z nim jest trudniej – możemy wyciągnąć miesiąc z daty za pomocą funkcji MIESIĄC, ale musimy dołożyć mechanizm, który dodawałby 20 dla XXI wieku (inne stulecia poza dwoma wcześniej ustalonymi nas nie interesują). W taki razie wystarczy sprawdzić, czy ROK z daty jest większy od 1999 i jeżeli tak to zwrócić 20, a jeśli nie to 0 za pomocą funkcji JEŻELI. Potrzeba do tego dodać jeszcze numer miesiąca z daty:
JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data])
Niestety to jeszcze nie wystarczy ponieważ dla miesięcy z XX wieku mogą nam wyjść wyniki 1 cyfrowe, a my potrzebujemy mieć zawsze 2 cydry, dlatego powyższą formułę musimy wstawić do funkcji TEKST, która będzie wymuszała wiodące 0. Tak uzyskaną formułę łączymy z cyframi roku:
Teraz zajmijmy się wyznaczeniem 4 kolejnych cyfr. Powiedzieliśmy sobie, że 10 cyfra oznacza płeć. Dokładnie liczby {0;2;4;6;8} oznaczają kobietę, a {1;3;5;7;9} mężczyznę. 3 wcześniejsze cyfry to kolejność urodzenia w danym dniu, czyli wystarczy funkcja LOS.ZAKR z odpowiednimi granicami. A musimy pamiętać, że to zawsze muszą być 3 cyfry, więc dodajemy funkcję TEKST, która zapewni ewentualne wiodące zera.
=TEKST(LOS.ZAKR(1;999);"000")
Jak jednak ustalić numer odpowiedzialny za płeć.
Jak się mu przyjrzymy to możemy zobaczyć, że jeśli będziemy losować liczbę z przedziału od 0 do 4 i przemnożymy ją przez 2 to zawsze wyjdzie nam kobieta. W takim razie wystarczy, że sprawdzimy za pomocą funkcji JEŻELI, czy wylosowaliśmy wcześniej kobietę czy mężczyznę i odpowiednio zwracali 0 lub 1 i tą wartość dodali do naszego losowania.
LOS.ZAKR(0;4)*2+JEŻELI([@Płeć]="k";0;1)
Łącząc powyższe dwie formuły mamy 4 kolejne cyfry numeru PESEL:
Teraz czeka nas najtrudniejsze zadanie – musimy obliczyć cyfrę kontrolną. Zasada jej obliczania wymaga przemnożenia każdej cyfry przez przypisaną jej wagę, a następnie ich zsumowanie. Wagi możemy zapisać w tablicy {1;3;7;9;1;3;7;9;1;3} ważne, że ta tablica ma w Excelu orientację pionową (każdy średnik oznacza nowy wiersz).
Już kilka razy wyciągaliśmy pojedyncze znaki z tekstu (np.: porada 295 i 296) z numerem PESEL jest analogicznie, a nawet prościej, bo zawsze mamy 10 cyfr (musimy je tylko połączyć z wcześniejszych obliczeń) i możemy zapisać na stałe tablicę {1;2;3;4;5;6;7;8;9;10} (ta sama orientacja co tablica z wagami, żeby mieć pewność poprawnych wyników). Czyli nasze mnożenie i sumowanie wygląda tak:
To jeszcze nie koniec naszych obliczeń, bo z takiego mnożenia i sumowania (swoją drogą dzięki wpisaniu tablic na stałe możemy zatwierdzać tą formułę normalnie, a nie jako formułę tablicową) daje wynik większy od 1 cyfry. W obliczeniach liczby kontrolnej musimy wyciągnąć resztę dzielenia przez 10 (funkcja MOD) i odjąć ją od 10. Ponieważ czasami wynik może wyjść 10, to jeszcze raz musimy go wstawić do funkcji MOD:
Potrzebujemy wyciągnąć wszystkie cyfry z tekstu jako liczbę. Nasze ograniczenia są takie, że nie możemy skorzystać z pomocy funkcji POŁĄCZ.TEKSTY czy ZŁĄCZ. TEKST, a dodatkowo nasza formuła nie może być zatwierdzana za pomocą kombinacji klawiszy Ctrl + Shift + Enter. To ciężkie zadanie, ale już ktoś taką formułę wymyślił:
Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.
Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:
=ADR.POŚR("1:"&DŁ(A2))
Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.
=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))
Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14}
Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:
Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów
Wynikiem tej formuły będzie tablica z pozycjami cyfr i zerami tam gdzie cyfr nie było:
{1;0;0;0;0;0;0;0;0;0;0;12;13;14}
Do naszych potrzeb potrzebujemy ustawić tą tablicę od największych wartości do najmniejszych, czyli skorzystamy z funkcji MAX.K, ale zanim to zrobimy wykonamy pewną sztukę, która sprawi, że na zakończenie nie będziemy musieli zatwierdzać naszej formuły jako formuły tablicowej. Sztuczka ta polega na wstawieniu naszej formuły do funkcji INDEKS i wyciągnięcia całego wiersza danych wpisując w drugim argumencie funkcji INDEKS wartości zero.
Nie wiem dokładnie dlaczego ta sztuczka działa, bo podglądając wynik wygląda on tak samo z i bez funkcji INDEKS, ale najważniejsze, że działa. Teraz jak wspomnieliśmy wkładamy naszą formułę do funkcji MAX.K, a jako argument k posłuży nam znów liczenie po kolei wierszy.
Podglądając wynik zobaczymy najprawdopodobniej dużo zer.
{14;13;12;1;0;0;0;0;0;0;0;0;0;0}
Niestety będą nam te zera przeszkadzać później w funkcji FRAGMENT.TEKSTU, bo nie możemy wyciągać znaków od zerowego znaku (taki nie istnieje). Żeby się ich pozbyć po prostu dodajemy jedynkę do naszej formuły (+1). Jednak jeśli wykonamy taką operacje to przesuną się znaki, które będziemy wyciągać, dlatego dodatkowo dołożymy na początek naszego tekstu zero (0&A2)
Musimy pamiętać, że to znowu są liczby, które Excel traktuje jako tekst. Musimy te wartości przemnożyć przez liczby w zależności od ich pozycji. Dokładnie pierwszą wartość potrzebujemy przemnożyć przez 1, drugą przez 10, trzecią przez 100 itd. Są to kolejne potęgi 10, więc wystarczy, żebyśmy 10 podnieśli do potęgi pozycji cyfry na liście. Czyli znów korzystamy z listy wierszy:
10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10
Tylko potęgowanie musimy podzielić przez 10, żebyśmy zaczęli od 1, a nie od 10.
Przed nami ciężkie zadanie wyciągnięcia wszystkich cyfr. Okazuje się ono dużo prostsze jeśli w Twojej wersji Excel 2016 (lub 365) masz zainstalowany już update z funkcją POŁĄCZ.TEKSTY i ZŁĄCZ.TEKST (ważne! bez Y na końcu, bo ZŁĄCZ.TEKSTY działa inaczej).
Jeśli masz te funkcję to czytaj dalej. Jeśli nie to zajrzyj do następnej porady (296).
Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.
Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:
=ADR.POŚR("1:"&DŁ(A2))
Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.
=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))
Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14}
Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:
Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów
Na szczęście jest nam to na rękę ponieważ nam zależy tylko na liczbach, a tam gdzie pojawił się błąd nie chcemy nic wyświetlać, więc wystarczy, że wstawimy naszą formułę do funkcji JEŻELI.BŁĄD, która zamiast błędów będzie zwracała pusty ciąg tekstowy (dwa podwójne cudzysłowy)
Teraz formułą zwraca tablicę cyfr i pustych ciągów znaków,
{5;"";"";"";"";"";"";"";"";"";"";1;0;0}
którą możemy łatwo połączyć za pomocą funkcji ZŁĄCZ.TEKST (przypominam bez Y na końcu) lub POŁĄCZ.TEKSTY jeśli zależy nam, żeby rozdzielać cyfry konkretnym ogranicznikiem, a nie bezpośrednio łączyć je ze sobą, ale w większości sytuacji chcemy je bezpośrednio łączyć, więc formuła będzie wyglądać tak:
Potrzebujesz dowiedzieć się ile razy dane ciąg tekstowy pojawia się w innym ciągu tekstowym? Formuła na to jest dość prosta i opisywałem ją w pytaniu od widzów nr 79:
pojawiło się pytanie w komentarzach co w sytuacji, gdy szukamy ciągu powtarzających się znaków np: "AAAA" i natrafimy na powtórzenie 5 razy danego znaku, a nie szukane 4?
Formuła opisane w pytaniu widzów 79, zliczy tylko 1 wystąpienie tego ciągu, a co jeśli nam zależy, żeby zliczała każde pojawienie się, czyli ciąg "AAAAA" oznaczałby dwukrotne powtórzenie szukanego ciągu "AAAA" i odpowiednio im więcej razy powtórzy się "A", tym więcej razy mamy odnaleźć szukanych ciągów.
W takiej sytuacji nasza formuła będzie bardziej skomplikowane. Potrzebujemy sprawdzić każdy ciąg długości naszego szukanego ciągu w przeszukiwanym tekście, czyli w naszym przykładzie wyciągnąć 4 znaki od pierwszego znaku, później od drugiego itd. Zaczniemy budować naszą formułę od środka — w pierwszej kolejności potrzebujemy ciąg początkowych znaków, czyli w uproszczeniu 1,2,3…
Żeby zbudować go w Excelu możemy posłużyć się funkcją PRZESUNIĘCIE, która zaczyna od komórki $A$1. Nie chcemy zmienić pozycję naszego zakresu, tylko jego wysokość, dlatego możemy wpisać wartości 0, lub nic w argumenty wiersze i kolumny. Musimy natomiast zmienić wysokość zakresu — żeby uprościć formułę, wysokość będzie równa długości przeszukiwanego ciągu:
=PRZESUNIĘCIE($A$1;;;DŁ(A4))
Tylko, że nas nie interesują wartości z poszczególnych komórek zakresu, tylko numery wierszy dla poszczególnych komórek:
=WIERSZ(PRZESUNIĘCIE($A$1;;;DŁ(A4)))
Dzięki temu uzyskaliśmy ciąg {1;2;3;4;…}, czyli numery znaków, od których będziemy wyciągać nasze ciągi do porównania do szukanego ciągu. Wykorzystamy do tego funkcję FRAGMENT.TEKSTU.
Przy tak zapisanej formule końcowe ciągi są krótsze od szukanego ciągu i dlatego nie będą mu nigdy równe,
ale formuła tu będzie ciut prostsza, a nie ma to wpływu na wynik.
Teraz jak mamy wyciągnięte poszczególne kawałki, możemy je przyrównać do ciągu szukanego przez nas, a następnie możemy je policzyć. Żeby nie potrzebować zatwierdzania jako formuła tablicowa wykorzystamy funkcję SUMA.ILOCZYNÓW, tylko będziemy musieli zamienić wyniki PRAWDA i FAŁSZ porównań na wartości 1 i 0 np. za pomocą podwójnego minusa:
Szef zlecił Ci zadanie (może ma na imię Robert ;)), żeby wyciągnąć tekst, który się znajduje pomiędzy przedostatnim, a ostatnim backslashem (\). To zadanie wymaga trochę pracy. Żeby było łatwiejsze podzielimy je sobie na trzy części. Najpierw znajdziemy pozycję przedostatniego i ostatniego backslasha, a potem już łatwo wyciągniemy tekst pomiędzy nimi.
Żeby odnaleźć pozycję przedostatniego i ostatniego backslasha najpierw będziemy potrzebować policzyć ile ich jest. Zrobimy to podmieniając wszystkie backslashe w tekście na pusty ciąg tekstowy, czyli po prostu usuniemy je. To uzyskamy za pomocą funkcji PODSTAW:
=PODSTAW(A2;"\";"")
Kolejnym krokiem będzie obliczenie długości tekstu/ścieżki, po usunięciu backslashy, to da nam funkcja DŁ, a żeby uzyskać liczbę backslashy potrzebujemy odjąć tą długość od faktycznej długości tekstu:
=DŁ(A2)-DŁ(PODSTAW(A2;"\";""))
Założyliśmy sobie, że najpierw odszukamy pozycję przedostatniego backslasha, więc musimy tu jeszcze odjąć jedynkę. Teraz możemy, ten konkretny numer znaku zastąpić takim ciągiem tekstowym, który na pewno nie powtórzy się w przeszukiwanym tekście np.: #@, to znów będzie funkcja PODSTAW:
Teraz jak mamy już pozycję ostatniego i przedostatniego znaku to łatwo wyciągnąć to co jest pomiędzy nimi za pomocą funkcji FRAGMENT.TEKSTU. Trzeba tylko pamiętać dodać i odjąć jeden, żeby nie wyciągnąć również backslashy:
