Po latach pracy ze skomplikowanymi i chaotycznymi arkuszami kalkulacyjnymi odkryłem cztery funkcje Excela, które oszczędzają mi godziny pracy tygodniowo, automatyzując rutynowe zadania, które większość osób wykonuje ręcznie. Funkcje te są niezbędne dla każdego, kto regularnie pracuje z danymi, niezależnie od tego, czy jest profesjonalnym analitykiem danych, czy po prostu okazjonalnym użytkownikiem, który chce uprościć swoją pracę.
Szybkie linki
4. XLOOKUP: zaawansowane wyszukiwanie w arkuszach kalkulacyjnych
XWYSZUKAJ Jest to zaawansowana funkcja wyszukiwania w programach arkuszy kalkulacyjnych, takich jak Microsoft Excel i Arkusze Google, która wykracza poza możliwości tradycyjnych funkcji wyszukiwania, takich jak WYSZUKAJ.PIONOWO و WYSZUKAJ.POZIOMODostępność. XWYSZUKAJ Większa elastyczność, wydajniejsze przetwarzanie danych i mniej typowych błędów związanych ze starszymi funkcjami. XWYSZUKAJ Niezbędne narzędzie dla analityków finansowych, naukowców zajmujących się danymi i każdego, kto pracuje z dużymi zbiorami danych i potrzebuje szybko i dokładnie wydobyć konkretne informacje. XWYSZUKAJMożesz wyszukać wartość w określonym zakresie i zwrócić odpowiadającą jej wartość z innego zakresu, niezależnie od lokalizacji kolumn lub wierszy. Obsługuje również XWYSZUKAJ Funkcja ta umożliwia wyszukiwanie od prawej do lewej i od dołu do góry, co czyni ją bardziej wszechstronną niż inne funkcje.
Żegnaj, funkcja WYSZUKAJ.PIONOWO: XLOOKUP to idealne rozwiązanie
Przestałem używać funkcji WYSZUKAJ.PIONOWO lata temu, kiedy odkryłem funkcję WYSZUKAJ.PIONOWO. Podczas gdy funkcja WYSZUKAJ.PIONOWO przeszukuje tylko w prawo i zawiesza się podczas przesuwania kolumn, funkcja WYSZUKAJ.PIONOWO działa w dowolnym kierunku i pozostaje elastyczna. Funkcja WYSZUKAJ.PIONOWO jest jedną z Funkcje programu Excel, które mogą zaoszczędzić Ci czas Znajdź określone dane w arkuszach kalkulacyjnych.
W danych o cenach podzespołów komputerowych muszę znaleźć konkretne ceny procesorów graficznych na podstawie modeli produktów. Korzystając z funkcji WYSZUKAJ.PIONOWO, musiałbym przebudować całą tabelę. Natomiast korzystając z funkcji WYSZUKAJ.PIONOWO, wystarczy wpisać:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12 GB Gaming OC", C:C, D:D)
Funkcja XLOOKUP przeszukuje całą kolumnę produktu, znajduje mój procesor graficzny i zwraca odpowiadającą mu cenę. Nie ma znaczenia, gdzie znajduje się kolumna z ceną, a program nie zawiesi się, jeśli później dodam więcej kolumn. Korzystam z niej cały czas, aby odwoływać się do informacji o produkcie w różnych arkuszach bez konieczności formatowania.
Podstawowa formuła funkcji XLOOKUP jest następująca:
=XLOOKUP(przeszukiwana_wartość; szukana_tablica; zwracana_tablica)
- szukana_wartość: Wartość, którą chcesz wyszukać.
- szukana_tablica: Miejsce, w którym szukasz wartości.
- tablica_zwrotna: Kolumna lub wiersz zawierający wartość, którą chcesz zwrócić.
W moim przypadku szukałem wartości „GIGABYTE GeForce RTX 3060 12GB Gaming OC”. Chciałem odszukać tę wartość w kolumnie C:C i zwrócić odpowiadającą jej wartość z kolumny D:D w tym samym wierszu, w którym znaleziono dopasowanie.
Kolejną rzeczą, którą lubię w funkcji XLOOKUP, jest to, że jeśli dodam „,-1” na końcu formuły, przeszukuje ona dane od dołu do góry, pozwalając mi automatycznie znaleźć najnowszy wpis cenowy. Dzięki temu nie muszę ręcznie sortować danych za każdym razem, gdy odświeżam arkusze kalkulacyjne.
3. Korzystanie z moich funkcji SUMA و LICZBY W arkuszach kalkulacyjnych
Profesjonalne zarządzanie wieloma standardami
Podstawowe funkcje SUMA i LICZNIK wystarczą do prostych zadań, ale zawodzą w analizie danych rzeczywistych. Kiedy muszę analizować dane cenowe w wielu warunkach, zazwyczaj używam funkcji SUMA.WARUNKÓW i LICZ.WARUNKÓW. Pozwalają mi one łatwo segmentować setki wierszy.
Załóżmy, że chcę policzyć procesory AMD dostępne na Amazon US. Zamiast filtrować ręcznie, wpisuję:
=LICZ.WARUNKI(F:F, "Amazon US", K:K, "AMD")
To od razu pokazuje mi, że w moim zbiorze danych na Amazonie znajduje się 14 procesorów AMD. Piękno tego rozwiązania polega na tym, że mogę skompilować tyle testów porównawczych, ile potrzebuję.
W analizie cen funkcja SUMIFS działa w ten sam sposób. Aby obliczyć całkowitą wartość wszystkich procesorów Intel dostępnych obecnie w magazynie, używam:
=SUMIFS(D:D, K:K, "Intel", G:G, "W magazynie")
Dodaje wszystkie ceny w kolumnie D, gdzie marka to „Intel”, a stan magazynowy to „W magazynie”.
Składnia funkcji SUMIFS jest następująca:
=SUMIFS(zakres_sum, zakres_kryteriów1, kryteria1, zakres_kryteriów2, kryteria2...)
- zakres_sumy: Kolumna, którą chcesz zsumować.
- zakres_kryteriów1: Pierwsza kolumna, względem której sprawdzane są warunki.
- kryteria1: Pierwszy stan zasięgu.
- zakres_kryteriów2, kryteria2: Dodatkowe warunki i postanowienia (opcjonalne).
Funkcja LICZ.JEŻELI działa podobnie, z tą różnicą, że zlicza pasujące wiersze zamiast sumować wartości:
=LICZ.JEŻELI(zakres_kryteriów1; kryteria1; zakres_kryteriów2; kryteria2...)
Do szybkich raportów wolę używać funkcji SUMA.WARUNKÓW i LICZ.WARUNKI, ponieważ natychmiast aktualizują nowe dane, idealnie dopasowują się do moich istniejących formuł i pozwalają mi zachować wszystko w jednym miejscu bez konieczności tworzenia osobnej tabeli przestawnej. Te narzędzia umożliwiają dokładną i wydajną analizę danych, oszczędzając czas i wysiłek w tworzeniu złożonych raportów. Korzystanie z funkcji takich jak SUMA.WARUNKÓW i LICZ.WARUNKI to niezbędna umiejętność dla każdego analityka danych, który chce szybko i łatwo wyciągać cenne wnioski z danych.
2. Przycinanie i czyszczenie: niezbędne kroki w celu utrzymania wyglądu
Żegnaj bałaganie danych
Nic nie rujnuje arkusza kalkulacyjnego szybciej niż nieustrukturyzowane dane wypełnione dodatkowymi spacjami i ukrytymi znakami. Przekonałem się o tym na własnej skórze, gdy moje wyszukiwania ciągle kończyły się niepowodzeniem z powodu dodatkowych spacji na końcu nazw formularzy.
Funkcja TRIM usuwa dodatkowe spacje z początku i końca tekstu, a także wszelkie dodatkowe spacje między wyrazami. Podczas importowania danych z różnych źródeł nazwy produktów często zawierają niespójne spacje. Zamiast ręcznie czyścić każdą komórkę, tworzę kolumnę pomocniczą i używam:
=PRZYTNIJ(C2)
Następnie przesuwam wskaźnik myszy do krawędzi komórki, aż przyjmie kształt znaku plus (+), a następnie przeciągam go w dół do wszystkich wierszy, na których chcę zastosować funkcję PRZYTNIJ.ZBĘDNE.ODSTĘPY.
1. TEXTBEFORE i TEXTAFTER: szczegółowe wyjaśnienie i ich znaczenie
Dokładnie wyodrębnij wymagane dane
Funkcje TEXTBEFORE i TEXTAFTER należą do moich ulubionych funkcji Excela do porządkowania nieuporządkowanych arkuszy kalkulacyjnych. Nowoczesne funkcje tekstowe Excela doskonale radzą sobie z wyodrębnianiem konkretnych informacji z nieustrukturyzowanych ciągów tekstowych. Na przykład w mojej kolumnie z cenami wpisy takie jak „177.52 USD”, „178.33 USD”, „9055 ₱” i „9645.50 PHP” były pomieszane.
Funkcja TEXTBEFORE wyodrębnia wszystko, co znajduje się przed określonym separatorem:
=TEKST PRZED(D2, "USD")
W ten sposób funkcja natychmiast wyodrębniła kwotę „178.33” z kwoty „178.33 USD”.
Funkcja TEXTAFTER działa w odwrotnej kolejności, wyodrębniając wszystko po separatorze:
=TEKST PO(C2, "AMD")
W ten sposób wyodrębniłem funkcję „Ryzen 5 5700X 8-Core AM4 Processor” z „AMD Ryzen 5 5700X 8-Core AM4 Processor”.
W przypadku złożonych ekstrakcji łączę obie funkcje. Aby uzyskać cenę liczbową 177.52 USD:
=TEKST PRZED(TEKST PO(D8, "$"), "USD")
Ogólna składnia funkcji TEXTBEFORE i TEXTAFTER jest następująca:
=TEXTBEFORE(tekst, ogranicznik) i =TEXTAFTER(tekst, ogranicznik)
Ogromna poprawa, jaką wnoszą te dwie funkcje, polega na ich precyzji. Zamiast stosować skomplikowane kombinacje funkcji FRAGMENT.TEKSTU, ZNAJDŹ i DŁ., mogę uzyskać czyste ekstrakcje za pomocą prostych, łatwych do odczytania formuł. Często korzystam z tych funkcji do oddzielania numerów modeli, wyodrębniania specyfikacji produktów i wyodrębniania czystych danych z importowanego tekstu, który wcześniej wymagał wielu godzin ręcznej edycji.
Te cztery funkcje rozwiązują niektóre z największych strat czasu w programie Excel, takie jak wyszukiwanie danych za pomocą elastycznych wyszukiwań, analiza oparta na wielu kryteriach, porządkowanie zaimportowanego tekstu i wyodrębnianie konkretnych informacji ze złożonych ciągów tekstowych. Większość osób wykonuje te zadania ręcznie, poświęcając godziny na to, co normalnie zajęłoby tylko kilka minut, aby wdrożyć poprawne formuły.
Używałeś tych funkcji do wszystkiego, od analizy cen komponentów po raporty z zarządzania zapasami. Działają niezależnie od branży, ponieważ chaotyczność danych i złożone wymagania wyszukiwania to powszechne problemy. Gdy opanujesz te funkcje, będziesz się zastanawiać, jak mogłeś kiedyś zarządzać arkuszami kalkulacyjnymi bez nich.
