Inżynieria danych w arkuszu. Dowiedz się, jak wyciągać informacje z numeru PESEL, manipulować ciągami znaków i kontrolować czas.
Wstęp: Inżynieria danych na maturze
Często na egzaminie nie otrzymujesz danych podanych "na tacy". Zamiast osobnej kolumny z datą urodzenia i płcią, otrzymujesz wyłącznie kolumnę z numerami PESEL. Zamiast rocznika samochodu – numer rejestracyjny (VIN). Twoim zadaniem jest użycie funkcji tekstowych, aby "rozciąć" te ciągi znaków, wyciągnąć z nich to, co niezbędne, i przekształcić w wartości gotowe do obliczeń matematycznych.
✂️ Spis treści: Funkcje Tekstowe i Daty
1
Wycinanie tekstu: Narzędzia chirurga
Najpotężniejsze trio w całym arsenale inżynierii danych to funkcje wycinające. Zawsze pobierają one tekst źródłowy oraz informację o tym, ile znaków chcesz "odciąć".
=LEWY(tekst; liczba_znaków)Odlicza podaną liczbę znaków zaczynając od skrajnej, lewej strony tekstu.
=PRAWY(tekst; liczba_znaków)Odlicza znaki zaczynając od samego końca ciągu (od skrajnej, prawej strony).
=FRAG...TEKSTU(tekst; liczba_startowa; liczba_znaków)Zaczyna wycinanie od konkretnej, podanej przez Ciebie pozycji w środku słowa i pobiera tyle znaków, ile zażądasz.
A2 znajduje się przykładowy PESEL: 03251401234.| Co wyciągamy? | Użyta funkcja | Wynik |
|---|---|---|
| Dwie pierwsze cyfry (Rok) | =LEWY(A2; 2) | "03" |
| Miesiąc (Zaczyna się od 3 cyfry, długość 2) | =FRAGMENT.TEKSTU(A2; 3; 2) | "25" |
| Dzień (Zaczyna się od 5 cyfry, długość 2) | =FRAGMENT.TEKSTU(A2; 5; 2) | "14" |
| Płeć (Przedostatnia cyfra)* | =FRAGMENT.TEKSTU(A2; 10; 1) | "3" |
To najczęstszy błąd maturzystów w zadaniach z inżynierią danych. Wycięta z PESEL-u cyfra "3" to dla procesora litera, a nie wartość matematyczna (dlatego na powyższym przykładzie wyniki są w cudzysłowach!).
Jeśli spróbujesz np. sprawdzić, czy wycięta płeć jest liczbą parzystą za pomocą funkcji CZY.PARZYSTE() lub podzielić ją używając MOD(), arkusz zwróci błąd, ponieważ nie umie dzielić liter.
ŹLE: =MOD( FRAGMENT.TEKSTU(A2; 10; 1) ; 2)
DOBRZE: =MOD( FRAGMENT.TEKSTU(A2; 10; 1) * 1 ; 2 )
Złota reguła: Zawsze, gdy chcesz użyć wyniku funkcji LEWY, PRAWY lub FRAGMENT.TEKSTU do obliczeń matematycznych, pomnóż ten wynik przez 1 (*1). To najszybszy sposób na zmuszenie arkusza do zmiany typu danych z tekstu na liczbę.
2
Długość i łączenie: Architektura napisów
Po wycięciu potrzebnych fragmentów tekstu, często musimy sprawdzić ich poprawność lub połączyć je w zupełnie nowe ciągi znaków. Do tego właśnie służą narzędzia weryfikacji długości oraz sklejania tekstu. To stały element zadań polegających na generowaniu haseł, e-maili czy unikalnych identyfikatorów.
Funkcja DŁ (ang. LEN) zlicza wszystkie znaki w komórce. Używamy jej najczęściej do filtrowania błędnych lub niepełnych danych.
# Składnia:
=DŁ(tekst_lub_komórka)
=JEŻELI(DŁ(A2)=11; "OK"; "BŁĄD") – upewnia się, że badany ciąg ma dokładnie 11 cyfr.Uwaga na niewidzialne znaki! Funkcja DŁ liczy dosłownie wszystko, łącznie ze spacjami. Jeśli w komórce masz słowo "Jan ", funkcja zwróci wynik 4 (zamiast 3). Zawsze sprawdzaj, czy dane importowane z TXT nie mają ukrytych spacji na końcu!
Chociaż arkusz posiada wbudowaną funkcję ZŁĄCZ.TEKSTY (ang. CONCATENATE), na maturze znacznie szybsze, czytelniejsze i mniej podatne na błędy w składni jest używanie operatora & (ampersand). Działa on jak cyfrowy superglue dla komórek.
# Tradycyjnie (wolno):
=ZŁĄCZ.TEKSTY(A2; " "; B2)
# Nowocześnie (szybko):
=A2 & " " & B2
Zauważ krytyczną zasadę: adresy komórek (np. A2, B2) podajemy bezpośrednio, ale każdy tekst wpisywany "z palca" (nawet zwykła spacja oddzielająca wyrazy, myślnik czy kropka) musi być umieszczony w cudzysłowie " ". Inaczej arkusz zwróci błąd.
Typowe zadanie CKE polega na wygenerowaniu unikalnego loginu pracownika lub ucznia na podstawie informacji z innych kolumn. Wykorzystujemy do tego kombinację poznanych wcześniej funkcji wycinających (LEWY/PRAWY) oraz operatora łączenia.
# Rozwiązanie (Formuła w komórce D2):
=LEWY(A2; 3) & PRAWY(B2; 2) & PRAWY(C2; 2) & "@szkola.pl"
3 Konwersja: Z tekstu na liczbę
Na egzaminie liczy się czas i zwięzłość kodu. Zamiast pisać długie słowo "WARTOŚĆ", możesz wymusić konwersję (tzw. niejawne rzutowanie typów), wykonując na wyciętym tekście dowolną neutralną operację matematyczną.
# Zamiast słowa WARTOŚĆ, wystarczy dopisać:
=FRAGMENT.TEKSTU(A2; 3; 2) * 1
# Alternatywnie:
=FRAGMENT.TEKSTU(A2; 3; 2) + 0
Procesor napotykając znak matematyczny stwierdza: "Aha, użytkownik chce to pomnożyć. Skoro to wygląda jak cyfra, zamieniam to w locie na liczbę". Wynik natychmiast ląduje po prawej stronie komórki.
2024-05-15, funkcja zwróci czystą liczbę .4
Operacje na Datach: Czas to tylko liczba
Pora poznać największy sekret arkuszy kalkulacyjnych: Dla procesora komputera data nie istnieje! Każda data, którą widzisz na ekranie (np. 2024-05-15), to w rzeczywistości zwykła liczba całkowita określająca, ile dni minęło od 1 stycznia 1900 roku. Ponieważ daty to pod spodem zwykłe liczby, możesz wykonywać na nich standardowe operacje matematyczne (np. odjąć od siebie dwie daty, aby dowiedzieć się, ile dni minęło między nimi).
Wydobywanie elementów: ROK, MIESIĄC, DZIEŃ
Wyobraź sobie, że importujesz plik z logowaniami użytkowników. Masz kolumnę z pełnymi datami, a polecenie od CKE brzmi: "Zlicz wszystkie logowania, które miały miejsce w maju, niezależnie od roku". Nie zrobisz tego funkcjami tekstowymi (bo dla komputera to wciąż ukryte liczby). Użyjesz dedykowanych funkcji ekstrakcji czasu.
2024-05-15, funkcja zwróci czystą liczbę .=JEŻELI(MIESIĄC(A2) = 5; "To jest maj"; "Inny miesiąc")
Harmonogramy: DNI.ROBOCZE (Odliczanie weekendów)
Zwykłe matematyczne odjęcie od siebie dwóch dat (np. =B2 - A2) podaje całkowitą liczbę dni kalendarzowych. Bardzo często na maturze trzeba jednak obliczyć czas realizacji zadania z wyłączeniem sobót i niedziel.
# Składnia:
=DNI.ROBOCZE(data_początkowa; data_końcowa; [święta])
# Przykład bez świąt:
=DNI.ROBOCZE(A2; B2)
Jeśli w arkuszu podano tabelkę z datami świąt narodowych (np. w komórkach Z1:Z10), zaznaczasz ten zakres jako trzeci argument. Arkusz automatycznie sprawdzi te daty i odejmie je od wyniku. Pamiętaj o zablokowaniu tego zakresu klawiszem F4 (czyli $Z$1:$Z$10), aby kłódka trzymała święta w miejscu podczas przeciągania formuły!
Formatowanie Niestandardowe: Magiczne Kody
Często polecenie brzmi: "Podaj, w jakim dniu tygodnia urodziło się najwięcej osób". Nie musisz pisać skomplikowanych formuł! Wystarczy skopiować daty do nowej kolumny, wcisnąć Ctrl + 1 (Formatowanie komórek) i w zakładce Niestandardowe wpisać odpowiedni kod maski wizualnej.
| Kod | Co wyświetla? | Przykład (dla 5 maja) |
|---|---|---|
d / m | Dzień / Miesiąc bez zera wiodącego | 5 / 5 |
dd / mm | Dzień / Miesiąc z zerem (wymusza 2 znaki) | 05 / 05 |
ddd / mmm | Krótka nazwa dnia / miesiąca | Pt / Maj |
dddd / mmmm | Pełna nazwa (ZŁOTO MATURALNE) | Piątek / Marzec |
Uwaga! Formatowanie niestandardowe to tylko i wyłącznie maska wizualna. Jeśli użyjesz kodu dddd, na ekranie zobaczysz piękne słowo "Piątek", ale w głębi komputera (w pasku formuły) to nadal jest pierwotna liczba, np. 45427.
Jeśli spróbujesz użyć na tym maskowanym tekście funkcji liczącej: =LICZ.JEŻELI(A2:A100; "Piątek"), arkusz zwróci , ponieważ szuka tekstu, a pod spodem leżą ukryte liczby!
Rozwiązanie: Funkcja TEKST
# Ta funkcja działa jak aparat fotograficzny. Robi zdjęcie zmaskowanej daty i zapisuje jako prawdziwy, wyszukiwalny tekst!
=TEKST(A2; "dddd")
Teraz arkusz na stałe przekonwertuje datę na czysty napis "Piątek", który możesz bez obaw analizować za pomocą LICZ.JEŻELI.
Znasz już mechanizmy wycinania informacji, ich długości, konwersji typów oraz pełnej manipulacji czasem. Posiadasz teraz pełen zestaw "narzędzi chirurga", dzięki którym z surowych danych wyciągniesz absolutnie wszystko, o co poprosi Cię CKE na maturze.