10 podstawowych funkcji Microsoft Excel do analizy danych
Różne / / November 29, 2021
1. POWIĄZAĆ
=CONCATENATE to jedna z najważniejszych funkcji analizy danych, ponieważ umożliwia łączenie tekstu, liczb, dat itp. z wielu komórek w jedną. Funkcja jest szczególnie przydatna do łączenia danych z różnych komórek w jedną komórkę. Na przykład przydaje się do tworzenia parametrów śledzenia dla kampanii marketingowych, tworzenia zapytań API, dodawania tekstu do formatu liczbowego i kilku innych rzeczy.
![Analiza danych podstawowych funkcji programu Excel 1](/f/ef4844c9256f6aeeb7180f57b791172d.jpg)
W powyższym przykładzie chciałem umieścić miesiąc i sprzedaż w jednej kolumnie. W tym celu użyłem formuły = CONCATENATE (A2, B2) w komórce C2, aby otrzymać jako wynik 700 USD.
Formuła: =ZŁĄCZ.TEKSTY(komórki, które chcesz połączyć)
2. LEN
=LEN to kolejna przydatna funkcja do analizy danych, która zasadniczo wyświetla liczbę znaków w dowolnej komórce. Funkcja jest używana głównie podczas tworzenia tagów tytułów lub opisów, które mają limit znaków. Może być również przydatne, gdy próbujesz znaleźć różnice między różnymi unikalnymi identyfikatorami, które często są dość długie i nie są we właściwej kolejności.
![Analiza danych niezbędnych funkcji programu Excel 2](/f/d7cadade746a26cd545ab58daa0e9ad7.jpg)
W powyższym przykładzie chciałem policzyć liczby dotyczące liczby wyświetleń, które uzyskiwałem każdego miesiąca. W tym celu użyłem formuły =LEN(C2) w komórce D2, aby otrzymać wynik 5.
Formuła: =DŁ(komórka)
3. WYSZUKAJ.PIONOWO
=WYSZUKAJ.PIONOWO to prawdopodobnie jedna z najbardziej rozpoznawalnych funkcji dla każdego, kto zna się na analizie danych. Możesz go użyć do dopasowania danych z tabeli do wartości wejściowej. Funkcja oferuje dwa tryby dopasowywania — dokładne i przybliżone, kontrolowane przez zakres wyszukiwania. Jeśli ustawisz zakres na FALSE, będzie szukał dokładnego dopasowania, ale jeśli ustawisz go na TRUE, będzie szukał przybliżonego dopasowania.
![Analiza danych niezbędnych funkcji programu Excel 3](/f/6e053d7c4c477481f07d505cdf6a876f.jpg)
W powyższym przykładzie chciałem sprawdzić liczbę wyświetleń w danym miesiącu. W tym celu użyłem formuły = WYSZUKAJ.PIONOWO("Cze", A2:C13, 3) w komórce G4 i jako wynik otrzymałem 74992. Tutaj „Jun” to wartość wyszukiwania, A2:C13 to tablica tabeli, w której szukam „Jun”, a 3 to numer kolumny, w której formuła znajdzie odpowiednie widoki dla czerwca.
Jedynym minusem korzystania z tej funkcji jest to, że działa ona tylko z danymi, które zostały ułożone w kolumny, stąd nazwa — wyszukiwanie pionowe. Więc jeśli masz dane ułożone w rzędach, musisz najpierw przełożyć wiersze na kolumny.
Formuła: =WYSZUKAJ.PIONOWO(szukana_wartość, tablica_tabeli, numer_indeksu_kolumn, [wyszukiwanie_zakresu])
4. INDEKS/DOPASOWANIE
Podobnie jak funkcja WYSZUKAJ.PIONOWO, funkcje INDEKS i PODAJ.POZYCJĘ przydają się do wyszukiwania określonych danych na podstawie wartości wejściowej. INDEKS i PODAJ.POZYCJĘ, używane razem, mogą przezwyciężyć ograniczenia funkcji WYSZUKAJ.PIONOWO w dostarczaniu błędnych wyników (jeśli nie jesteś ostrożny). Więc kiedy połączysz te dwie funkcje, mogą wskazać odniesienie do danych i wyszukać wartość w tablicy z jednym wymiarem. To zwraca współrzędne danych jako liczbę.
![Niezbędne funkcje Excela Analiza danych 4](/f/b13227460bc7cb0fa84f9d41f380d98d.jpg)
W powyższym przykładzie chciałem sprawdzić liczbę wyświetleń w styczniu. W tym celu użyłem formuły =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Tutaj A2: C13 to kolumna danych, którą chcę zwrócić formuła, „Jan” to wartość, którą chcę dopasować, A2: A13 to kolumna, w której formuła znajdzie „Jan”, a 0 oznacza, że chcę, aby formuła znalazła dokładne dopasowanie dla wartość.
Jeśli chcesz znaleźć przybliżone dopasowanie, musisz zastąpić 0 na 1 lub -1. Tak, że 1 znajdzie największą wartość mniejszą lub równą wartości wyszukiwania, a -1 znajdzie najmniejszą wartość mniejszą lub równą wartości wyszukiwania. Pamiętaj, że jeśli nie użyjesz 0, 1 lub -1, formuła użyje 1, by.
![Analiza danych niezbędnych funkcji programu Excel 5](/f/47d28f577f724d05e8f8ee098e91c6b7.jpg)
Teraz, jeśli nie chcesz na stałe zakodować nazwy miesiąca, możesz zastąpić ją numerem komórki. Możemy więc zastąpić „Jan” we wzorze wspomnianym powyżej przez F3 lub A2, aby uzyskać ten sam wynik.
Formuła: =INDEKS(kolumna danych, które chcesz zwrócić, DOPASUJ (wspólny punkt danych, który próbujesz dopasować, kolumna innego źródła danych, które ma wspólny punkt danych, 0))
5. MINI/MAXIFY
Funkcje =MINIFS i =MAXIFS są bardzo podobne do funkcji =MIN i =MAX, z wyjątkiem tego, że pozwalają na wybranie minimalnego/maksymalnego zestawu wartości i dopasowanie ich również do określonych kryteriów. Zasadniczo funkcja szuka wartości minimalnych/maksymalnych i dopasowuje je do kryteriów wejściowych.
![Analiza danych niezbędnych funkcji programu Excel 7](/f/fae987f5603a9782d0f1136c8a385c01.jpg)
W powyższym przykładzie chciałem znaleźć minimalne wyniki na podstawie płci ucznia. W tym celu użyłem formuły =MINIFS (C2:C10, B2:B10, "M") i uzyskałem wynik 27. Tutaj C2:C10 to kolumna, w której formuła będzie szukać wyników, B2:B10 to kolumna, w której formuła będzie szukać kryteriów (płeć), a „M” to kryteria.
![Analiza danych niezbędnych funkcji programu Excel 6](/f/e122c3fc7d8eafccda0a531139c4dea9.jpg)
Podobnie, aby uzyskać maksymalne wyniki, użyłem formuły =MAXIFS(C2:C10, B2:B10, "M") i uzyskałem wynik 100.
Formuła dla MINIFII: =MINIFY(min_zakres, kryteria_zakres1, kryteria1,...)
Formuła dla MAXIFS: =MAKS.maksymalny_zakres, kryteria_zakres1, kryteria1,...)
6. ŚREDNIA JEŻELI
Funkcja =ŚREDNIA.IFS umożliwia znalezienie średniej dla określonego zestawu danych na podstawie jednego lub większej liczby kryteriów. Korzystając z tej funkcji należy pamiętać, że każde kryterium i średni zakres może być inny. Jednak w funkcji =ŚREDNIA.JEŻELI zarówno zakres kryteriów, jak i zakres sumy muszą mieć ten sam zakres rozmiarów. Zauważ różnicę liczby pojedynczej i mnogiej między tymi funkcjami? Cóż, tutaj musisz być ostrożny.
![Niezbędne funkcje Excela Analiza danych 8](/f/a2eb06f67342cde2eb6ee86ea6a8d351.jpg)
W tym przykładzie chciałem znaleźć średni wynik na podstawie płci uczniów. W tym celu użyłem formuły =ŚREDNIA.JEŻELI(C2:C10, B2:B10, "M") i otrzymałem 56,8 jako wynik. Tutaj C2:C10 to zakres, w którym formuła będzie szukać średniej, B2:B10 to zakres kryteriów, a „M” to kryteria.
Formuła: =ŚREDNIAJEŻELI(zakres_średni, zakres_kryteriów1, kryteria1,...)
7. LICZNIKI
Teraz, jeśli chcesz policzyć liczbę wystąpień zestawu danych spełniających określone kryteria, musisz użyć funkcji =LICZ.WARUNKI. Ta funkcja umożliwia dodawanie nieograniczonej liczby kryteriów do zapytania, a tym samym sprawia, że jest to najłatwiejszy sposób na znalezienie liczby na podstawie kryteriów wejściowych.
![Analiza danych niezbędnych funkcji programu Excel 9](/f/39635ab1eeffded1042cfd7c6249a7f2.jpg)
W tym przykładzie chciałem znaleźć liczbę uczniów lub studentów, którzy zdali pozytywne oceny (tj. >=40). W tym celu użyłem formuły =LICZ.WARUNKI(B2:B10, "M", C2:C10, ">=40"). Tutaj B2:B10 to zakres, w którym formuła będzie szukać pierwszego kryterium (płeć), „M” to pierwsze kryterium, C2:C10 to zakres, w którym formuła będzie szukać drugiego kryterium (znaków), a ">=40" to drugie kryteria.
Formuła: =LICZ.WARUNKI(kryteria_zakres1, kryteria1,...)
8. SUMA PRODUKT
Funkcja =SUMPRODUCT pomaga mnożyć zakresy lub tablice, a następnie zwraca sumę produktów. Jest to dość wszechstronna funkcja i może być używana do zliczania i sumowania tablic, takich jak LICZ.WARUNKI lub SUMA.WARUNKI, ale z dodatkową elastycznością. Możesz także użyć innych funkcji w SUMPRODUCT, aby jeszcze bardziej rozszerzyć jego funkcjonalność.
![Analiza danych niezbędnych funkcji programu Excel 10](/f/f4d91eb407af82ac40d18cb36e6bbd13.jpg)
W tym przykładzie chciałem znaleźć sumę wszystkich sprzedanych produktów. W tym celu użyłem formuły =SUMPRODUCT(B2:B8, C2:C8). Tutaj B2:B8 to pierwsza tablica (ilość sprzedanych produktów), a C2:C8 to druga tablica (cena każdego produktu). Formuła następnie mnoży ilość każdego sprzedanego produktu przez jego cenę, a następnie sumuje ją, aby zapewnić całkowitą sprzedaż.
Formuła: =PRODUKCJASUMA(tablica1, [tablica2], [tablica3],...)
9. PRZYCINAĆ
Funkcja =TRIM jest szczególnie przydatna podczas pracy z zestawem danych zawierającym kilka spacji lub niechcianych znaków. Funkcja umożliwia łatwe usuwanie tych spacji lub znaków z danych, co pozwala uzyskać dokładne wyniki podczas korzystania z innych funkcji.
![Analiza danych niezbędnych funkcji programu Excel 11](/f/04381c96e973bee237ac85395449540a.jpg)
W tym przykładzie chciałem usunąć wszystkie dodatkowe spacje między słowami Mysz i podkładka w A7. W tym celu użyłem formuły =TRIM(A7).
![Analiza danych niezbędnych funkcji programu Excel 12](/f/6d8e3d59bdbc3535366f7e4eeeedb7de.jpg)
Formuła po prostu usunęła dodatkowe spacje i dostarczyła wynikową podkładkę pod mysz z pojedynczą spacją.
Formuła: =PRZYC(tekst)
10. ZNAJDŹ/SZUKAJ
Zaokrąglaniem są funkcje ZNAJDŹ/SZUKAJ, które pomogą Ci wyizolować określony tekst w zestawie danych. Obie funkcje są dosyć podobne w tym, co robią, z wyjątkiem jednej zasadniczej różnicy — funkcja =ZNAJDŹ zwraca tylko dopasowania uwzględniające wielkość liter. Tymczasem funkcja =SZUKAJ nie ma takich ograniczeń. Funkcje te są szczególnie przydatne podczas wyszukiwania anomalii lub unikalnych identyfikatorów.
![Analiza danych niezbędnych funkcji programu Excel 13](/f/94b07268b0748d7e1ecddc2c94ec57ee.jpg)
W tym przykładzie chciałem sprawdzić, ile razy „Gui” pojawiło się w Guiding Tech, dla którego użyłem formuły = ZNAJDŹ(A2, B2), co dało wynik 1. Teraz, gdybym chciał zamiast tego sprawdzić, ile razy „gui” pojawiło się w Guiding Tech, musiałbym użyć formuły =SEARCH, ponieważ nie jest w niej rozróżniana wielkość liter.
Formuła Znajdź: =ZNAJDŹ(znajdź_tekst, w_tekście, [numer_początkowy])
Formuła wyszukiwania: =SZUKAJ(znajdź_tekst, w_tekście, [numer_początkowy])