Podczas pracy z danymi w programie Excel niektóre zadania mogą wydawać się niepotrzebnie żmudne. Na przykład trzeba podzielić kolumnę z imionami i nazwiskami na osobne kolumny dla imion i nazwisk lub połączyć tekst z wielu komórek, używając konkretnych przecinków. Nie są to skomplikowane wyzwania analityczne, lecz podstawowe zadania przetwarzania danych, które pojawiają się regularnie.
Dobrą wiadomością jest to, że Excel ma wbudowane funkcje zaprojektowane specjalnie na takie sytuacje. Często jednak są one pomijane, ponieważ nie są częścią Standardowy zestaw narzędzi programu Excel, którego uczy się większość ludzi, w tym ja. Funkcje, które tu omówię, nie dotyczą zaawansowanych obliczeń, ale jeśli wykonujesz powtarzalną pracę z danymi, te funkcje mogą zaoszczędzić Ci trochę czasu.
Szybkie linki
5. PODZIEL TEKST
Oddziela sklejone ze sobą teksty
Jeśli kiedykolwiek otrzymałeś arkusz kalkulacyjny, w którym ktoś wcisnął swoje imię i nazwisko, a może nawet inicjały drugiego imienia, w jedną komórkę, wiesz, jak trudno jest rozdzielić te dane. Funkcja TextSplit rozwiązuje ten problem – pobiera tekst z jednej komórki i dzieli go na wiele kolumn na podstawie określonego separatora.
Popracujmy nad przykładowym arkuszem kalkulacyjnym sprzedaży. Zobaczysz imiona i nazwiska przedstawicieli handlowych: „Sarah Chen”, „Mike Johnson” i „Lisa Park”, wszystkie w jednej kolumnie. Zamiast ręcznie wpisywać każde imię i nazwisko w osobnych kolumnach, TextSplit może wykonać tę pracę automatycznie.
Wzór jest następujący:
=TEXTSPLIT(tekst, ogranicznik_kolumny, [ogranicznik_wierszy], [ignoruj_puste], [tryb_dopasowania], [uzupełnij])
Oto, co robi każdy nauczyciel:
- tekst: Komórka zawierająca tekst, który chcesz podzielić.
- ogranicznik_kolumny: Znak oddzielający dane (np. spacja, przecinek lub średnik).
- row_delimiter (opcjonalny): Używane przy podziale na wiersze i kolumny.
- ignoruj_puste (opcjonalne): TRUE ignoruje wartości puste, FALSE je zachowuje (domyślnie ustawione jest FALSE).
- match_mode (opcjonalny): Steruje uwzględnianiem wielkości liter (0 oznacza uwzględnianie wielkości liter, 1 oznacza brak uwzględniania wielkości liter).
- pad_with (opcjonalny): Czym wypełnić puste komórki, jeśli wyniki mają różną długość?
Na przykład w przypadku nazwisk przedstawicieli handlowych użyłbym następującego wzoru, aby podzielić nazwiska na osobne kolumny:
=PODZIELTEKST(A2, " ")
Funkcja automatycznie tworzy wymaganą liczbę kolumn na podstawie Twoich danych. Chociaż to podstawowe podejście działa w większości przypadków, istnieją dodatkowe parametry, które dają Ci szczegółową kontrolę nad… Funkcja TEXTSPLIT w programie Excel.
4. TEKSTJOIN
Połącz wiele komórek w jedną komórkę
Funkcja TEXTJOIN działa odwrotnie niż TEXTSPLIT. Pobiera tekst z wielu komórek i łączy go w jedną komórkę za pomocą wybranego separatora. Jest to przydatne, gdy trzeba utworzyć wartości sekwencyjne, takie jak pełne adresy, opisy produktów lub listy e-mail.
Wzór wygląda następująco:
=TEXTJOIN(rozdzielacz, ignoruj_puste, tekst1, [tekst2], ...)
Oto, co kontroluje każdy parametr:
- rozgranicznik: Znak lub tekst oddzielający osadzone wartości (przecinek, spacja, myślnik itp.).
- ignoruj_puste: TRUE powoduje ignorowanie pustych komórek, FALSE powoduje ich uwzględnienie w wyniku.
- tekst1, tekst2, itd.: Komórki lub zakresy, które chcesz połączyć (możesz określić pojedyncze komórki lub całe zakresy).
Patrząc na arkusz kalkulacyjny sprzedaży, jeśli mam osobne kolumny dla imienia i regionu, ale potrzebuję ich w jednej kolumnie, która je łączy, użyłbym funkcji TEXTJOIN. ignorować_puste Wartość TRUE oznacza, że wszystkie puste komórki zostaną automatycznie pominięte.
=POŁĄCZENIETEKSTÓW(" - ", PRAWDA, B2, D2)Wybierając pomiędzy różnymi metodami integracji tekstu, należy zrozumieć Różnice między funkcjami CONCAT i TEXTJOIN Może pomóc Ci wybrać właściwe narzędzie spełniające Twoje specyficzne potrzeby w zakresie integracji danych.
3. WYBIERZKOLE
Określ konkretne kolumny swoich danych.
Funkcja CHOOSECOLS umożliwia wyodrębnienie określonych kolumn z zakresu bez kopiowania i wklejania ani tworzenia odniesień. Jeśli masz duży zbiór danych, ale do analizy potrzebujesz tylko kolumn 2, 5 i 8, ta funkcja pobierze potrzebne dane i odrzuci resztę.
Na podstawie danych sprzedażowych mogę chcieć wyodrębnić tylko nazwisko sprzedawcy i jego imię i nazwisko, ignorując daty zamówień, kategorie produktów i inne szczegóły. Zamiast ręcznie wybierać i kopiować kolumny, funkcja CHOOSECOLS tworzy dynamiczne odwołanie, które automatycznie aktualizuje się po zmianie danych źródłowych.
Funkcja ta jest zgodna z następującym wzorem:
=WYBIERZKOLE(tablica, nr_kolumny1, [nr_kolumny2], ...)
Oto jak działa każdy parametr:
- szyk: Zakres lub tabela zawierająca dane źródłowe (może to być zakres komórek, np. A1:F100, lub odwołanie do tabeli).
- numer_kolumny1: Numer pierwszej kolumny, którą chcesz wyodrębnić (1 dla pierwszej kolumny, 2 dla drugiej kolumny itd.).
- numer_kolumny2 itd.: Dodatkowe numery kolumn, które chcesz uwzględnić (opcjonalnie – możesz określić dowolną liczbę).
Na przykład, gdybym chciał wyodrębnić nazwiska przedstawicieli handlowych z kolumny 2 i ich statusy z kolumny 9, użyłbym:
=WYBIERZKOLE(A1:I23, 2, 9)
Funkcja zwraca obie kolumny jako tablicę strumieniową, której rozmiar jest automatycznie dostosowywany do danych. Dlatego CHOOSECOLS jest jednym z Funkcje programu Excel, które mogą zaoszczędzić Ci mnóstwo czasuEliminuje konieczność stosowania wielu formuł VLOOKUP lub ręcznego kopiowania kolumn podczas pracy z dużymi zbiorami danych.
Program Excel ma również funkcję CHOOSEROWS, która działa w podobny sposób, ale wybiera konkretne wiersze zamiast kolumn i wykorzystuje tę samą strukturę formuły z numerami wierszy.
2. WEŹ i UPUST
Wyodrębnij części swoich danych
Funkcje TAKE i DROP działają parami, aby przechwycić określone fragmenty zakresu danych. Funkcja TAKE wyodrębnia określoną liczbę wierszy lub kolumn z początku lub końca zestawu danych, natomiast funkcja DROP usuwa wiersze lub kolumny z początku lub końca, pozostawiając to, co pozostało.
Funkcje te działają jak precyzyjne narzędzia do pobierania próbek danych. Niezależnie od tego, czy potrzebujesz tylko pierwszych dziesięciu wierszy danych do szybkiej analizy, czy chcesz usunąć wiersze nagłówków, które zatykają obliczenia, te funkcje sprawnie sobie z tym poradzą.
TAKE korzysta z następującego wzoru:
=TAKE(tablica, wiersze, [kolumny])
DROP stosuje podobny schemat:
=DROP(tablica, wiersze, [kolumny])
Oto jak działają parametry dla obu funkcji:
- szyk: Zakres danych źródłowych, który chcesz wyodrębnić lub zmodyfikować.
- wydziwianie: Liczba rzędów, które mają zostać wzięte/usunięte (liczby dodatnie zaczynają się od góry, ujemne od dołu).
- kolumny (opcjonalnie):
Liczba kolumn, które chcesz wziąć lub usunąć (dodatnia od lewej, ujemna od prawej).
Aby uzyskać pierwsze pięć wierszy danych sprzedaży, należy użyć następującej formuły:
=WEŹ(A1:C100, 5)
Aby usunąć pierwsze 20 wierszy i pracować z czystymi danymi, spróbuj:
=USUŃ(A1:C23, 20)
Możesz łączyć operacje na wierszach i kolumnach. Na przykład, poniższa formuła zwraca pierwsze dziesięć wierszy i pierwsze trzy kolumny:
=WEŹ(A1:F23, 10, 3)
Funkcje te stają się bardzo przydatne, zwłaszcza gdy potrzebujesz dynamicznych podzbiorów danych, które dostosowują się automatycznie. Dowiedz się więcej Jak korzystać z funkcji WEŹ i UPUST w programie Excel Otwiera to możliwość tworzenia elastycznych raportów, które dostosowują się do zmieniających się rozmiarów zestawów danych.
1. AGREGAT
Potężne obliczenia, które radzą sobie z chaotycznymi danymi
AGGREGATE łączy funkcjonalność 19 różnych funkcji statystycznych w jedną elastyczną formułę. Jej cechą wyróżniającą jest możliwość ignorowania błędów, ukrytych wierszy i przefiltrowanych danych – czego standardowe funkcje, takie jak SUMA czy ŚREDNIA, nie potrafią niezawodnie zrobić.
Jeśli Twoje dane zawierają błędy #N/A lub jeśli filtrujesz, aby wyświetlać tylko wybrane regiony, AGGREGATE może obliczyć sumy, średnie lub inne statystyki bez zakłócania wyników przez te problemy. Uważam to za przydatne podczas pracy z dynamicznymi zbiorami danych, gdzie widoczność i jakość danych często się zmieniają.
Budowa zdania składa się z kilku elementów:
=AGGREGAT(funkcja_num, opcje, tablica, [k])
Każde kryterium kontroluje różne aspekty obliczeń:
- numer_funkcji: Liczba od 1 do 19 określająca funkcję, która ma zostać użyta (1=ŚREDNIA, 4=MAKS, 9=SUMA, 12=MEDIANA itd.).
- opcje: Kontroluje, co ma być ignorowane podczas obliczeń (0=brak, 1=ukryte wiersze, 2=wartości błędów, 3=ukryte wiersze i błędy, 5=tylko wartości błędów, 6=ukryte wiersze i wartości błędów).
- szyk: Zakres komórek, które mają zostać obliczone.
- k (opcjonalnie):
- Używane tylko z niektórymi funkcjami, takimi jak DUŻY, MAŁY lub PERCENTYL.
Aby podsumować kwoty sprzedaży, ignorując ewentualne błędy, mogę użyć:
=AGREGACJA(9, 6, D2:D23)
Liczba 9 określa SUMA, a liczba 6 nakazuje funkcji ignorować zarówno wiersze ukryte, jak i wartości błędów.
Właśnie dlatego uwzględniono funkcję AGGREGATE, która umożliwia wykonywanie obliczeń. Lista funkcji programu Excel, które powinien znać każdy pracownik biurowy—Radzi sobie z chaosem rzeczywistych danych, z którym prostsze funkcje nie potrafią sobie skutecznie poradzić.
Wbudowane narzędzia, z których warto korzystać
Najważniejsze funkcje programu Excel często nie są tymi, których uczymy się na początku. Rozwiązują one jednak subtelne problemy pojawiające się w rzeczywistej pracy z arkuszami kalkulacyjnymi, takie jak radzenie sobie z nieuporządkowanymi danymi tekstowymi, wyodrębnianie określonych fragmentów z dużych zbiorów danych oraz wykonywanie obliczeń na niekompletnych danych. Żadna z omawianych funkcji nie wymaga zaawansowanych umiejętności obsługi programu Excel. Funkcje TEXTSPLIT, CHOOSECOLS, TAKE i DROP są jednak dostępne tylko w pakiecie Microsoft 365 i programie Excel dla sieci Web.
Następnym razem, gdy będziesz wielokrotnie czyścić dane lub ręcznie kopiować kolumny, pamiętaj o tych funkcjach. Są one wbudowane w Excela i obsługują żmudne czynności, dzięki czemu możesz skupić się na tym, co dane tak naprawdę Ci przekazują.
