16 formuł Excel, które pomogą Ci rozwiązać rzeczywiste problemy

Wiele osób postrzega program Excel jako narzędzie, które jest dobre tylko dla aplikacji biznesowych. 3 szalone formuły Excel, które robią niesamowite rzeczy 3 szalone formuły Excel, które robią niesamowite rzeczy Formuły Excel mają potężne narzędzie do formatowania warunkowego. W tym artykule omówiono trzy sposoby zwiększenia wydajności za pomocą MS Excel. , ale gdy zobaczysz poniższe proste i przydatne formuły, zobaczysz, że Excel może również pomóc Ci rozwiązać rzeczywiste problemy w domu.

Niezależnie od tego, czy kupujesz nową pożyczkę na samochód, chcesz dowiedzieć się, która inwestycja w fundusz wspólnego inwestowania jest dla Ciebie najlepsza, czy też po prostu próbujesz znaleźć sens na swoim koncie bankowym, Excel jest potężnym narzędziem, które może pomóc.

15 poniższych wzorów zostało starannie wybranych ze względu zarówno na prostotę, jak i na to, jak skutecznie pomagają zwykłym ludziom rozwiązywać złożone problemy. Jeśli jesteś nauczycielem, który próbuje uporządkować oceny uczniów, rodzic stara się kontrolować wydatki domowe lub młoda para kupuje pierwszą pożyczkę, poniższe formuły mogą pomóc.

Formuły finansowe

Kupujesz nowy dom i jesteś zdezorientowany całym żargonem hipotecznym? Szukając nowego samochodu i myląc się warunkami kredytu samochodowego, sprzedawca rzuca się na ciebie?

Nie bać się. Zanim zaczniesz robić zakupy, przeprowadź badania z Excelem u swojego boku!

PMT - płatność

Kiedykolwiek jesteś porównywanie warunków kredytu i chcesz szybko obliczyć faktyczną miesięczną płatność, biorąc pod uwagę różne warianty, skorzystaj z potężnego (i prostego) Formuła PMT.

Oto, czego potrzebujesz, aby użyć tej formuły:

• procentowa stopa procentowa pożyczki
• okres pożyczki (ile płatności?)
• zasada początkowa pożyczki
• wartość w przyszłości, jeżeli z jakiegoś powodu pożyczka zostanie uznana za spłaconą, zanim osiągnie zero (opcjonalnie)
• rodzaj pożyczki - 0, jeśli płatności są należne na koniec każdego miesiąca, lub 1, jeśli są wymagalne na początku (opcjonalnie)

Oto świetny sposób na szybkie porównanie różnych pożyczek, aby zobaczyć, jak będą wyglądać Twoje płatności; wystarczy utworzyć arkusz Excela, który zawiera listę wszystkich potencjalnych pożyczek i wszystkie dostępne informacje na ich temat. Następnie utwórz plik “Płatności” i użyj formuły PMT.

Po prostu chwyć prawy dolny róg utworzonej wcześniej komórki PMT i przeciągnij ją w dół, aby obliczyć sumę płatności dla wszystkich warunków kredytu wymienionych w arkuszu.

Jak widać, kiedy można porównać warunki pożyczki obok siebie, pojawiają się pewne surowe realia. Na przykład w przypadku pożyczki samochodowej w wysokości 19 500 USD, jeśli poświęcisz czas na pokazanie się i uzyskanie pożyczki, która jest tylko kilka punktów procentowych niższa, może zaoszczędzić wiele pieniędzy każdego miesiąca i tysiące dolarów w ciągu całego życia pożyczka.

(Bardzo dziękuję Markowi Jonesowi (@redtexture na Twitterze), który zwrócił uwagę, że w przypadku formuł PMT i FV musisz bardzo uważać na korzystanie z tego samego okresu - w tym przypadku korzystanie z płatności miesięcznych wymaga podzielenia okresu odsetkowego przez 12 miesięcy)

@rdube
Rażące błędy: formuła PMT i FV oblicza procent na miesiąc, a nie na rok. Stopy procentowe obniżone o 12x. Https://t.co/wrC5eXFzNw

- Mark Jones (@redtexture) 26 listopada 2015 r

Właśnie dlatego nasi czytelnicy są tacy wspaniali - dziękuję za pomoc z tą poprawką Mark!

FV - Przyszła wartość

Następna formuła przyda się, gdy będziesz chce zainwestować trochę pieniędzy na coś takiego jak CD (certyfikat depozytowy), a chcesz wiedzieć, co będzie warte CD na koniec tego okresu.

Oto, co musisz wiedzieć, aby korzystać z Wzór FV:

• oprocentowanie pożyczki
• liczba płatności (lub okres inwestycji w miesiącach)
• płatność za każdy okres (zwykle co miesiąc)
• aktualne saldo początkowe (opcjonalnie)
• rodzaj pożyczki - 0, jeśli płatności są należne na koniec każdego miesiąca, lub 1, jeśli są wymagalne na początku (opcjonalnie)

Porównajmy więc kilka płyt CD z bankami, korzystając z warunków, które znasz z informacji przekazanych przez banki. W poniższym przykładzie załóżmy, że masz 20 000 dolarów na zainwestowanie w płytę CD.

Jak widać, stopy procentowe są ponownie przedstawiane w formacie dziesiętnym (weź stopę procentową podaną przez bank i podziel przez 100), a płatności wynoszą zero, ponieważ płyty CD są zazwyczaj oparte na wartości początkowej i wypłaconej w przyszłości. Oto, jak wygląda porównanie, gdy używasz formuły FV dla każdej rozważanej płyty CD.

Bez wątpienia płyta CD o wyższym oprocentowaniu przez dłuższy okres czasu wydaje dużo więcej. Jedyną wadą jest oczywiście to, że nie można dotknąć żadnych pieniędzy przez trzy całe lata. Dla wielu ludzi to trochę za długo, ale taka jest natura inwestowania!

Funkcje logiczne (IF lub AND)

Obecnie większość banków umożliwia pobieranie prawie rocznych transakcji bankowych do formatu CSV. Jest to idealny format do analizy wydatków za pomocą programu Excel, ale czasami dane otrzymywane z banków są bardzo niezorganizowane.

Korzystanie z funkcji logicznych to świetny sposób przekroczenie wydatków na miejscu.

W idealnym przypadku bank albo automatycznie kategoryzuje twoje wydatki, albo skonfigurowałeś swoje konto, aby rzeczy były dzielone na kategorie wydatków. Na przykład wszelkie restauracje, do których idziemy, otrzymują oznaczenie “Jedzenie na mieście” etykieta.

Ułatwia to użycie funkcji logicznej do identyfikacji, kiedy wychodzimy zjeść i wydamy ponad 20 USD.

Aby to zrobić, po prostu utwórz funkcję logiczną w nowej kolumnie, szukając dowolnej wartości, w której znajduje się kolumna kategorii “Jedzenie na mieście” a kolumna transakcji jest większa niż - 20 USD (porównanie poniżej pokazuje “<“, less than, because the values in column C are all negative. Here's what that looks like:

Używanie IF i AND razem w jednej formule wygląda na trudne, ale jeśli się nad tym zastanowić, ma to sens. Instrukcja IF wyświetli kwotę w dolarach (C2), jeśli instrukcja AND jest prawdziwa, lub FALSE, jeśli nie jest. Instrukcja AND sprawdza, czy kategoria jest “Jedzenie na mieście” a wartość transakcji przekracza 20 USD.

Masz to! Bez konieczności ręcznego przesiewania wszystkich tych transakcji, znasz teraz dokładnie te czasy, kiedy przepadłeś w określonej kategorii. Jak zwykle formuły programu Excel okazują się ogromnie oszczędzać czas.

Zrozumieć listy

Listy są dużą częścią codziennego życia. Jeśli zarządzasz gospodarstwem domowym, stale korzystasz z list.

Może jesteś w komitecie rodziców i nauczycieli i otrzymałeś długą listę darowizn od społeczności. Od Ciebie zależy, kto w społeczności przekaże darowiznę na określony projekt szkolny i ile. A może prowadzisz firmę rodzinną i płacisz za pół tuzina zatrudnionych pracowników i musisz podsumować ich indywidualne koszty.

COUNT i COUNTIF

Bez względu na listę, Excel może pomóc w szybkim organizowaniu i sortowaniu wartości. Weźmy przykład PTC. Oto lista darowizn od członków społeczności.

Jeśli chcesz zobaczyć, ile razy nazwisko osoby pojawia się na liście, możesz połączyć formułę LICZBA z formułą JEŻELI. Najpierw utwórz kolumnę, aby sprawdzić, czy osoba jest Michelle, czy nie. Jeśli tak, ustaw komórkę na 1. Jeśli nie, ustaw ją jako spację ” “.

Następnie utwórz kolejną kolumnę, która zlicza, ile razy znalazłeś Michelle Johnson na liście.

Daje to liczbę wszystkich miejsc w kolumnie E, w których jest 1 zamiast pustego.

Jest to więc najprostszy sposób na zrobienie tego rodzaju rzeczy, ale wymaga dwóch kroków.

Jeśli nie masz nic przeciwko użyciu nieco bardziej zaawansowanej funkcji, możesz rozważyć użycie jednej z wielu połączonych funkcji “JEŚLI” działa jak SUMIF, COUNTIF, lub ŚREDNIA. Umożliwiają one wykonywanie funkcji (LICZ., SUMA lub ŚREDNIA) tylko wtedy, gdy warunek logiczny jest spełniony. Oto jak to działa na powyższym przykładzie.

Ta formuła patrzy na kolumnę A, która zawiera wszystkie nazwy dawców, a jeśli komórka w zakresie odpowiada kryteriom podanym w cudzysłowie, to zlicza jeden. Daje to liczbę wszystkich czasów, gdy nazwa dawcy jest równa “Michelle Johnson” w jednym kroku.

Jest to znacznie szybsze niż użycie dwóch kolumn, ale dla niektórych osób może być nieco zbyt skomplikowane - więc zastosuj podejście, które najlepiej pasuje do twojej sytuacji.

Funkcja LEN

Inną funkcją, z której można czasami korzystać w sposób kreatywny, jest funkcja LEN. Ta funkcja informuje, ile znaków jest w ciągu tekstu. Oszczędność czasu dzięki operacjom tekstowym w programie Excel Oszczędność czasu dzięki operacjom tekstowym w programie Excel Excel potrafi wykonywać magię z liczbami i równie dobrze obsługuje znaki. Ten podręcznik pokazuje, jak analizować, konwertować, zamieniać i edytować tekst w arkuszach kalkulacyjnych. Te podstawy pozwolą Ci wykonywać złożone transformacje. .

Jednym z interesujących sposobów wykorzystania tego w powyższym przykładzie byłoby wyróżnienie dawców, którzy przekazali darowiznę powyżej 1000 USD, poprzez zliczenie liczby cyfr w kolumnie darowizny.

Następnie, jeśli chcesz, możesz sformatować inną kolumnę na podstawie wartości z kolumny E; wynik funkcji LEN.

Aby to zrobić, musisz zaznaczyć wszystkie komórki w kolumnie Darowizna, wybierz Dom zakładkę w menu i kliknij Formatowanie warunkowe na pasku narzędzi. Następnie wybierz Użyj formuły, aby określić, które komórki sformatować.

Ustaw zakres poniżej Sformatuj wartości, w których ta formuła jest prawdziwa: do kolumny / zakresu, w którym wyświetlane są wszystkie wyjścia funkcji LEN.

W tym przykładzie, jeśli spełnisz warunek “> 3”, wtedy wszystko powyżej 1000 USD otrzyma specjalne formatowanie. Nie zapomnij kliknąć Format… i wybierz rodzaj specjalnego formatowania.

Również krótka notatka. Zauważysz, że mój zasięg jest zdefiniowany jako “E2 $: E11 $”, nie “2 USD: 11 USD. 11 USD”. Z jakiegoś powodu, gdy wybierzesz zakres, domyślnie będzie to pierwszy, który nie zadziała. Musisz użyć względnego adresowania, jak pokazano na powyższym obrazku. Następnie formatowanie warunkowe będzie działać w oparciu o warunek drugiego zakresu.

Organizowanie pobrań bankowych i finansowych

Czasami, gdy pobierasz informacje od firm - bez względu na to, czy jest to twój bank, zakład ubezpieczeń zdrowotnych, czy gdziekolwiek prowadzisz działalność, format przychodzących danych nie zawsze odpowiada temu, czego potrzebujesz..

Załóżmy na przykład, że w eksportowanych danych z banku podajesz datę w standardowym formacie.

Jeśli chcesz dodać własną kolumnę z własną, która jest poprzedzona rokiem i zawiera informacje o odbiorcy płatności (do własnych celów sortowania), wyodrębnienie informacji z kolumny jest naprawdę łatwe.

PRAWO, LEWO, TEKST i POŁĄCZ SIĘ

Możesz wyciągnąć rok z tekstu w tej kolumnie, używając DOBRZE funkcjonować.

Powyższa funkcja mówi Excelowi, aby wziął tekst z kolumny D i wyodrębnił cztery znaki z prawej strony. The POWIĄZAĆ funkcja składa razem te cztery cyfry z tekstem odbiorcy z następnej kolumny.

Pamiętaj, że jeśli chcesz wyodrębnić tekst z daty, musisz przekonwertować go na format tekstowy (zamiast daty) przy użyciu “= TEKST (D2,”mm / dd / rrrr”)” funkcjonować. Następnie możesz użyć funkcji PRAWO, aby wyciągnąć rok.

CONCATENATE naprawdę przydaje się, gdy masz tekst z wielu różnych kolumn, które chcesz poskładać w jeden długi ciąg. Na przykład możesz połączyć imię, nazwisko, adres, miejscowość i stan osoby w polu zawierającym cały adres osoby w jednej kolumnie.

Wybieranie losowych nazw z kapelusza

Ostatnią zabawną funkcją jest ta, której możesz użyć, jeśli musisz zrobić coś takiego, jak wybrać kilka nazwisk z kapelusza. Jak uzyskać bezpłatny szablon biletu loterii dla programu Microsoft Word Jak uzyskać bezpłatny szablon biletu loterii dla programu Microsoft Word Zbiórka funduszy to wiele praca. Pozwól nam wziąć trochę z twoich ramion i pokażemy Ci, jak znaleźć szablony i dostosować bezpłatne bilety na loterię. Wszystko czego potrzebujesz to edytor tekstu. na przyjęcie bożonarodzeniowe. Odłóż czapkę i skrawki papieru, a zamiast tego wyciągnij laptopa i uruchom program Excel!

Korzystanie z funkcji RANDBETWEEN, możesz mieć Excela losowo wybierać liczbę spośród określonego zakresu liczb.

Dwie wartości, których musisz użyć, to najniższa i najwyższa liczba, które powinny znajdować się na końcu zakresu liczb podanych dla imienia i nazwiska każdej osoby.

Po naciśnięciu klawisza Enter funkcja losowo wybierze jedną z liczb z zakresu.

Jest tak losowy i odporny na manipulacje, jak to tylko możliwe. Zamiast wybierać liczbę z czapki, wybierz numer z Excela!

Korzystanie z programu Excel do codziennych problemów

Jak widać, Excel jest nie tylko dla guru analizy danych i profesjonalistów biznesowych. Każdy może skorzystać z wielu funkcji ukrytych w Excelu.

Czy ten artykuł zawiera jakieś kreatywne pomysły na temat korzystania z programu Excel do rozwiązywania problemów we własnym życiu? Czy kiedykolwiek wcześniej korzystałeś z niektórych z tych funkcji programu Excel w kreatywny sposób?? Podziel się swoimi pomysłami i historiami w sekcji komentarzy poniżej, chciałbym je usłyszeć!

Zdjęcie: Goodluz przez Shutterstock.com