Jeśli korzystasz z Excela/Arkuszy Google w codziennej pracy, istnieje kilka bardzo pomocnych formuł, do których będziesz wracać, jeśli raz się ich nauczysz.
Istnieje prawdopodobnie ponad sto różnych formuł arkusza kalkulacyjnego, które działają dla różnych typów obliczeń.
Moim celem na tym blogu jest omówienie najprostszych ze wszystkich, tych, które stanowią podstawę wszystkich zaawansowanych obliczeń, których możesz nauczyć się później.
Najpierw omówimy kilka podstawowych poleceń obliczeń arytmetycznych, a następnie przedstawię kilka sposobów wykonywania bardziej zaawansowanych analiz.
Obejmują one instrukcje IF, VLOOKUP, INDEX i MATCH itp.
To prostsze niż myślisz!
Kluczowe wnioski
- Excel i Arkusze Google mają wiele wbudowanych poleceń formuł, które są przydatne do wykonywania obliczeń.
- SUM, AVERAGE, COUNT, VLOOKUP, INDEX i MATCH to niektóre formuły, które mogą pomóc w codziennych obliczeniach. Można również bawić się nimi, wprowadzając logikę warunkową.
- Podczas pisania formuły nie może być żadnych błędów, w przeciwnym razie nie uzyskasz wyników. W szczególności należy unikać odwołań cyklicznych i zapobiegać przechodzeniu programu Excel w nieskończoną pętlę obliczeniową.
Czym dokładnie jest formuła arkusza kalkulacyjnego?
Polecenia, za pomocą których można wykonywać obliczenia danych, są znane jako formuły arkusza kalkulacyjnego.
Zarówno Microsoft Excel, jak i Dokumenty Google są pełne takich poleceń, co częściowo jest powodem, dla którego są one podstawowymi narzędziami w każdym miejscu pracy.
Znak (=) poprzedza każdą formułę zapisaną w programie Excel. Jest to wskazówka dla programu Excel, aby traktować następujący tekst jako formułę do wykonania wymaganych obliczeń, a nie zwykły wpis tekstowy.
Nigdy więcej nie martw się, że sztuczna inteligencja wykryje twoje teksty. Undetectable AI Może ci pomóc:
- Spraw, by pisanie wspomagane przez sztuczną inteligencję wyglądało podobny do człowieka.
- Obejście wszystkie główne narzędzia do wykrywania AI za pomocą jednego kliknięcia.
- Użycie AI bezpiecznie i pewnie w szkole i pracy.
Oprócz używania formuł Arkuszy Google lub arkusza kalkulacyjnego Excel dla dat, oto kilka powodów, dla których Excel i Arkusze Google mają setki poleceń formuł:
- Szybko automatyzuje obliczenia
Każdy z nas musi wykonywać podstawowe obliczenia, takie jak arytmetyka lub obliczanie średnich, niezależnie od charakteru naszej pracy.
Jeśli Twoja praca wymaga dużej ilości danych, potrzebujesz bardziej rozbudowanych obliczeń z dużo większą częstotliwością.
Jest to bardzo wygodne, jeśli możesz wykonać te obliczenia w arkuszu kalkulacyjnym, w którym przechowywane są dane, zamiast importować je do kalkulatora.
Możesz także ustawić formułę raz, a będzie ona działać dla tylu wierszy lub danych, ile potrzeba.
- Zmniejsza liczbę błędów ręcznych
Eksportowanie danych do aplikacji kalkulatora jest nie tylko czasochłonne, ale także podatne na błędy. Jeden błędny wpis i całe obliczenia stają się niewiarygodne.
Nawet przy wielokrotnych korektach nie można mieć pewności, czy wszystkie wartości zostały wprowadzone poprawnie.
Uruchamianie formuły arkusza kalkulacyjnego zmniejsza liczbę błędów.
- Logicznie łączy dane
Dzięki obliczeniom można nadać zestawom danych znacznie bardziej logiczny sens. Posortuj dane, przefiltruj je, przeprowadź analizę i lepiej zrozum, czemu odpowiadają te wszystkie liczby w każdej komórce arkusza kalkulacyjnego.
Świetnie nadaje się do:
- Budżetowanie
- Prowadzenie osobistej lub biznesowej dokumentacji finansowej
- Nauczyciele chcący rejestrować zadania/wyniki egzaminów swoich uczniów
- Dane badawcze
- Obliczanie zwrotów z inwestycji
Teraz, niezależnie od tego, czy potrzebujesz formuł arkusza kalkulacyjnego Google, czy też chcesz uzyskać listę formuł arkusza kalkulacyjnego Excel, możesz je znaleźć w następnej sekcji.
Podstawowe formuły potrzebne każdemu użytkownikowi
Zarówno Excel, jak i Arkusze Google mają setki formuł, które wykonują różne obliczenia. Jeśli chcesz nauczyć się ich wszystkich, zajmie ci to kilka miesięcy. Poniżej opisałem jednak kilka bardzo przydatnych.
- SUM dla sum
Polecenie SUM() służy do sumowania wszystkich zaznaczonych komórek. Oczywiście, aby polecenie zadziałało, w komórce musi znajdować się wartość liczbowa. Formuła nie będzie działać na komórkach nieliczbowych.
W nawiasie należy określić pierwszą i ostatnią komórkę segmentu arkusza kalkulacyjnego, który ma zostać dodany, oddzielając je dwukropkiem.
Tak więc, jeśli chcę dodać zawartość wierszy od 3 do 7 w pierwszej kolumnie, napisałbym formułę jako: SUM(A3:A7). Formuła działa również dla wielu wierszy.
Ponadto formuła ta pozostaje taka sama zarówno dla Arkuszy Google, jak i Excela.
Spójrzmy na ten przykład:
Oprócz najprostszej funkcji SUMA, można również wykonywać następujące rodzaje dodawania:
- SUMIF: Dodaje liczby z zakresu spełniające jeden warunek
- SUMIFS: dodawanie liczb w zakresie spełniającym wiele warunków
- SUMPRODUCT: Mnożenie odpowiednich wartości w tablicach, a następnie dodawanie wyników.
- SUMSQ: dodawanie kwadratów liczb w zakresie
- SUMX2MY2: Dodaje wynik (x² - y²) dla odpowiednich wartości w dwóch zakresach.
- SUMX2PY2: Dodaje wynik (x² + y²) dla odpowiednich wartości w dwóch zakresach.
- SUMXMY2: Dodaje kwadraty różnic (x - y)² dla dwóch zakresów.
- DSUM: Dodaje wartości w kolumnie, które spełniają kryteria zdefiniowane w tabeli bazy danych.
- IMSUM: Dodawanie liczb zespolonych.
- SERIESSUM: Oblicz sumę szeregu potęgowego przy użyciu podanych współczynników.
- ŚREDNIA dla spostrzeżeń
Formuła ŚREDNIA() służy do obliczania średniej danego zestawu komórek w arkuszu kalkulacyjnym.
Ponownie, działanie jest prawie takie samo. W nawiasach należy umieścić numer pierwszej i ostatniej komórki, oddzielając je dwukropkiem, np. AVERAGE(E2:E11), a otrzymamy średnią dla wybranych komórek.
Ponownie, wzór na średnią jest taki sam w Excelu, jak w Arkuszach Google.
Niewielkie zmiany w formule ŚREDNIA mogą pomóc w obliczeniach warunkowych, takich jak:
- ŚREDNIA: Aby obliczyć średnią arytmetyczną liczb w zakresie
- AVERAGEA: Użyj go, gdy chcesz uzyskać średnią, w tym liczby, tekst i wartości logiczne
- AVERAGEIF: To polecenie służy do obliczania średniej liczb spełniających jeden warunek
- AVERAGEIFS: Oblicza średnią liczb spełniających wiele warunków
- DAVERAGE: Oblicza średnią wartości w kolumnie bazy danych, które spełniają określone kryteria.
- COUNT do śledzenia przedmiotów
To polecenie jest przydatne do obliczania liczby komórek w arkuszu kalkulacyjnym, które zawierają dane liczbowe.
Nie ma sensu jej używać, gdy dane są ograniczone tylko do kilku wierszy, ale jeśli masz setki wierszy danych i chcesz wiedzieć, ile z nich to wartości liczbowe, to jest to, co zamierzasz osiągnąć.
Jest on również napisany w podobny sposób, tj. zaczyna się od znaku =, następnie wspomina polecenie COUNT, otwiera nawias, wspomina pierwszą i ostatnią komórkę oddzieloną dwukropkiem i zamyka nawias.
Formuła jest taka sama zarówno w Arkuszach Google, jak i w Excelu.
Na przykład, =COUNT(E1:E100)
Podobnie jak formuły SUMA i ŚREDNIA, COUNT może być również używane na kilka różnych sposobów, tj,
- COUNTA: Zliczanie niepustych komórek (liczb, tekstu lub wartości).
- COUNTBLANK: zlicza tylko puste komórki w zakresie
- COUNTIF: Zlicza komórki spełniające jeden warunek
- COUNTIFS: Służy do zliczania komórek spełniających wiele warunków.
- DCOUNT: Zlicza wartości liczbowe w kolumnie bazy danych, które spełniają określone kryteria
- DCOUNTA: Służy do zliczania niepustych wartości w kolumnie bazy danych, które spełniają określone kryteria
Zaawansowane formuły analizy danych
Dla większości użytkowników formuły, które omówiłem wcześniej, będą więcej niż wystarczające. Niektóre formuły są jednak potrzebne do zaawansowanych obliczeń i są bardzo przydatne, jeśli praca obejmuje analizę danych.
- VLOOKUP dla wyszukiwań
Formuła VLOOKUP służy do znajdowania wartości w tabeli i zwracania powiązanych informacji z tego samego wiersza.
Mówiąc prościej, po wprowadzeniu VLOOKUP arkusz kalkulacyjny wyszukuje wartość w pierwszej kolumnie tabeli, a następnie podaje wartość z innej kolumny w tym wierszu.
W naszym przykładowym arkuszu, jeśli kolumna A zawiera identyfikatory produktów, a kolumna C zawiera cenę jednostkową, możemy użyć VLOOKUP, aby wyszukać określony identyfikator produktu i uzyskać jego cenę z kolumny C.
Zostanie ona zapisana jako VLOOKUP(“P-1001”, A:C, 3, FALSE)
- Instrukcje IF dla logiki
Funkcja JEŻELI służy do podejmowania logicznych decyzji w arkuszu kalkulacyjnym. Po prostu mówi arkuszowi, co zrobić, gdy warunek jest prawdziwy i co zrobić, gdy jest fałszywy.
Aby napisać instrukcję IF, należy rozpocząć od znaku =, dołączyć polecenie IF, otworzyć nawiasy, a następnie opisać warunek.
Następnie dodaj przecinek i wspomnij, co chcesz, aby arkusz zrobił, jeśli warunek zostanie spełniony. Następnie dodaj kolejny przecinek i określ, co ma się stać, jeśli polecenie nie zostanie spełnione.
Na przykład, jeśli wyobrazimy sobie arkusz z wynikami uczniów i chcemy określić warunek, że wynik 50 lub wyższy jest wynikiem pozytywnym, a poniżej 50 jest wynikiem negatywnym, możemy użyć logiki IF.
Oto jak to napisać: =IF(A1>=50, “Pass”, “Fail”)
Arkusz przeskanuje wartość w komórce, jeśli jest większa niż 50, pokaże, że uczeń zdał. W przeciwnym razie pojawi się informacja o niezaliczeniu.
- INDEX & MATCH dla wyszukiwania
INDEX i MATCH są używane razem do wyszukiwania informacji w tabeli w bardziej elastyczny sposób niż formuła LOOKUP.
Funkcja MATCH jest odpowiedzialna za znajdowanie pozycji wartości w wierszu lub kolumnie. Funkcja MATCH służy do wyszukiwania wartości w tabeli i nie zwraca samej wartości. Zwróci ona liczbę reprezentującą miejsce, w którym pojawia się wartość.
Na przykład, jeśli identyfikator produktu jest piątą pozycją na liście, MATCH zwróci 5.
Funkcja INDEX używa tego numeru pozycji do zwrócenia wartości z określonej lokalizacji w tabeli lub kolumnie. Pobiera więc po prostu dowolną wartość istniejącą we wskazanym wierszu.
Wskazówki pozwalające uniknąć typowych błędów w formułach
Jak już zapewne zauważyłeś, zarówno Excel, jak i Arkusze Google wymagają wprowadzania formuł z dokładnością do 100%.
Wszelkie błędy w pisaniu formuły nie przyniosą rezultatów, pojawi się tylko komunikat o błędzie #N/A.
Oto kilka wskazówek, które pomogą uniknąć tego komunikatu o błędzie.
- Dokładnie sprawdź nawiasy
Formuła musi być napisana dokładnie w formacie opisanym powyżej. Jeśli przez przypadek pozostawisz nawiasy otwarte lub po prostu opiszesz zakres komórek bez nawiasów, dane nie zostaną obliczone.
Zawsze upewnij się, że nawiasy otwierające i zamykające są dobrze umieszczone. Upewnij się również, że dwukropek rozróżnia pierwszą i ostatnią komórkę zakresu.
- Typy danych zegarka
Formuły arkusza kalkulacyjnego działają najlepiej tylko z niektórymi typami danych. Na przykład nie można oczekiwać średniej wartości dla danych, które nie są wymierne, tj. opisowych form danych.
Podczas wstawiania formuł należy dwukrotnie sprawdzić dane komórek, aby nie wprowadzić nieprawidłowego zakresu komórek.
- Unikaj odwołań cyklicznych
Odwołanie cykliczne oznacza, że arkusz kalkulacyjny powraca do własnej komórki, w której jest opisane. Tworzy to nieskończoną pętlę cyrkulacji, w której nie można uzyskać odpowiedzi, a zamiast tego wyświetlany jest komunikat o błędzie.
Na przykład, jeśli wpiszesz SUM(A1:A10) w komórce A10, utworzysz pętlę kołową, w której komórka będzie musiała stale dodawać dowolną wartość zsumowaną w komórce A10, a proces ten nigdy się nie zakończy.
Formuła musi znać swój własny wynik, aby znaleźć wartość komórki A10.
Zawsze upewnij się, że komórka, w której wprowadziłeś formułę arkusza kalkulacyjnego, nie jest częścią samego zakresu komórek formuły.
Łączenie formuł dla lepszej wydajności
Łączenie formuł w arkuszach kalkulacyjnych pozwala na bardziej efektywne rozwiązywanie złożonych problemów. Podczas korzystania z prostych formuł, o których mówiłem wcześniej, będziesz obliczać rzeczy krok po kroku w wielu komórkach.
W formule połączonej można zagnieżdżać wiele obliczeń, tak aby jedna formuła zasilała bezpośrednio inną.
Na przykład, można połączyć IF z SUM, aby zsumować wartości spełniające tylko określone warunki.
Można również łączyć COUNT lub AVERAGE z warunkami, aby dokładniej analizować dane.
To powiedziawszy, łączenie formuł nie przychodzi łatwo na początku. Istnieje prawdziwa krzywa uczenia się, a jeśli wskoczysz do złożonych formuł zagnieżdżonych bez użycia prostych formuł wiele razy, skończysz na dezorientacji.
Dlatego zawsze najpierw ćwicz z prostymi formułami. Gdy już poczujesz się komfortowo, stopniowo rozwijaj się, łącząc dwie funkcje, następnie trzy, a ostatecznie bardziej zaawansowane konfiguracje.
Korzystanie z formuł w rzeczywistych zadaniach
Gdy już opanujesz te formuły, zaczniesz zauważać, jak często mają one zastosowanie do rzeczy, które już robisz.
Na przykład:
- Właściciel małej firmy może skorzystać z formuły SUMIF śledzenie wydatków według kategorii
- Nauczyciel może też połączyć IF z COUNTIF funkcje oznaczające uczniów, którzy mają zaległości w zadaniach
- To samo dotyczy freelancerów, którzy zarządzają fakturami klientów. Mogą oni korzystać z prostego VLOOKUP aby pobierać stawki klientów z listy głównej bez konieczności ręcznego przewijania wierszy.
Rzecz w tym, że wiedza o tym, która formuła pasuje do konkretnej sytuacji, to prawie połowa sukcesu. I to jest dokładnie to, gdzie Undetectable's Chatbot AI może być Twoim przewodnikiem.
Możesz opisać, co próbujesz zrobić w prostym języku, a aplikacja zasugeruje odpowiednią formułę dla twojego zadania.
Masz teraz współpracownika znającego się na arkuszach kalkulacyjnych, do którego możesz zadzwonić, gdy utkniesz w martwym punkcie, i nie ogranicza się to tylko do zapytań w Excelu lub arkuszu kalkulacyjnym Google.
Poniżej dowiesz się, w jaki sposób nasz AI Detector i Humanizer mogą ulepszyć Twoje treści!
Przemyślenia końcowe
Mam nadzieję, że ten artykuł pomógł ci dowiedzieć się czegoś o formułach arkuszy kalkulacyjnych.
Możesz stać się w nich lepszy, im więcej ich używasz, a za każdym razem, gdy nie wiesz, jakiego ciągu formuły użyć, po prostu zwróć się do Undetectable AI Czat AI, i pomoże ci zdecydować, które polecenie będzie najlepsze do wykonania żądanych obliczeń.
Możesz również dwukrotnie sprawdzić swoją formułę, uruchamiając ją przez AI Chat, aby potwierdzić, że nie popełniasz żadnych błędów.
Wypróbuj Niewykrywalna sztuczna inteligencja dzisiaj!