Większość użytkowników Excela uważa, że osiągnięcie szybkości i wydajności wymaga zapamiętywania setek skomplikowanych formuł i funkcji. Ja też tak myślałem, dopóki nie odkryłem, że zaledwie trzy podstawowe funkcje mogą zastąpić szeroki zakres formuł, do których zdążyłem się przyzwyczaić. Te trzy funkcje radykalnie zmieniły sposób, w jaki pracuję w Excelu, oszczędzając mi mnóstwo czasu i wysiłku. Jeśli szukasz sposobów na uproszczenie pracy i zwiększenie produktywności w Excelu, oto kilka cennych wskazówek.
Szybkie linki
3. XLOOKUP: Nowoczesna funkcja wyszukiwania w programie Excel
Pamiętasz, jak funkcja WYSZUKAJ.PIONOWO zmuszała Cię do liczenia kolumn, aby znaleźć numer indeksu? Albo jak szukałeś danych po lewej stronie kolumny kluczowej i musiałeś przeorganizować cały arkusz? Te problemy już się skończyły.
Funkcja XLOOKUP to nowoczesne rozwiązanie firmy Excel dla każdego Błędy funkcji WYSZUKAJ.PIONOWOJest prostszy i bardziej elastyczny, a także dostępny w programach Excel 365, Excel 2021 i nowszych. Zamiast zajmować się numerami kolumn lub ograniczeniami trendów, wystarczy określić trzy rzeczy: czego szukać, gdzie szukać i co ma zostać zwrócone.
Oto podstawowa formuła funkcji XLOOKUP:
2. SUMA.JEŻELI/LICZ.JEŻELI: Twoje potężne narzędzia do analizy warunkowej w programie Excel
Jeśli nadal filtrujesz dane za każdym razem, gdy potrzebujesz szybkiego sumowania lub liczenia, wkładasz w to więcej wysiłku niż powinieneś. Funkcje SUMA.JEŻELI i LICZ.JEŻELI to ważne narzędzia, które często są pomijane, szczególnie w przypadku raportów sprzedaży, budżetów i innych zbiorów danych, w których konieczne jest wykonanie obliczeń warunkowych.
Wyobraź sobie, że Twój menedżer pyta Cię: „Jaka jest nasza łączna sprzedaż w Azji produktów powyżej 150 USD zamówionych online?”. Zamiast tworzyć trzy filtry z ryzykiem błędu, możesz odpowiedzieć w jednym wierszu, korzystając z funkcji SUMIFS:
Na przykład:
Ta formuła dodaje wszystkie wartości w kolumnie L, gdzie kolumna A równa się „Azja”, kolumna J jest większa niż 150, a kolumna D równa się „Online”.
Jeśli testujesz wartości tekstowe, pamiętaj o uwzględnieniu kryteriów w cudzysłowie.
Funkcja LICZ.JEŻELI działa w ten sam sposób, z tą różnicą, że zwraca liczbę wierszy spełniających warunki:
Na przykład:
Zamiast kwoty, jak w przypadku funkcji SUMIFS, otrzymasz liczbę zamówień z Azji, które zostały złożone online i których cena jednostkowa była wyższa niż 150 USD.
Funkcje SUMA.JEŻELI i LICZ.JEŻELI również obsługują znaki zamiast wartości. Na przykład możesz policzyć wszystkie kraje zaczynające się na literę „T” i wszystkie produkty z wyjątkiem owoców:
Znaki zamiast wartości są przydatne, ale opierają się na czystych danych. Jeśli Twój zbiór danych zawiera niespójne wielkie i małe litery lub ukryte znaki, wyniki mogą wydawać się błędne. Szybkie uporządkowanie niezorganizowanego arkusza Excela Oszczędzi ci to wielu kłopotów później.
Zaletą funkcji SUMA.JEŻELI i LICZ.JEŻELI jest to, że nie powodują one zmian w oryginalnych danych. Nigdy nie powoduje to dzielenia ani uszkodzenia oryginalnego zestawu danych. A jeśli potrzebujesz większej złożoności, Excel obsługuje do 127 par kryteriów.
Niezależnie od tego, czy chcesz sprawdzić „Ilu klientów wydało ponad 500 USD w ciągu ostatnich 30 dni?”, czy „Jaki jest łączny przychód ze sprzedaży akcesoriów za mniej niż 50 USD w Ameryce Północnej?”, te dwie funkcje natychmiast dadzą Ci odpowiedź.
1. Funkcja FILTER w programie Excel: łatwe i profesjonalne filtrowanie danych
Funkcja FILTER to potężne i unikalne rozszerzenie programu Excel, które rewolucjonizuje sposób filtrowania danych. Zamiast skomplikowanych kroków wymagających klikania w menu i ustawiania warunków, funkcja FILTER robi to wszystko za pomocą jednej prostej formuły:
Załóżmy, że chcesz wyświetlić przychody ze sprzedaży, których cena jednostkowa przekracza 600 USD:
Lub w prawdziwym zbiorze danych:
Liczby spełniające Twoje kryteria pojawią się natychmiast. Jeśli wartość nie spełnia kryteriów, możesz dodać alternatywny komunikat zamiast błędu:
Podobnie jak SUMA.WARUNKÓW, funkcja FILTER nie jest ograniczona do jednego warunku. Można użyć logiki LUB (przynajmniej jeden warunek musi być spełniony) lub logiki I (wszystkie warunki muszą być spełnione). Oto kilka prostych przykładów:
