Home-theater-designers
Formuły Microsoft Excel mogą zrobić prawie wszystko. W tym artykule dowiesz się, jak potężne mogą być formuły i formatowanie warunkowe programu Microsoft Excel, z trzema przydatnymi przykładami.
Poznawanie Microsoft Excel
Omówiliśmy wiele różnych sposobów lepszego wykorzystania programu Excel, takich jak gdzie znaleźć świetne szablony Excela do pobrania , oraz jak używać Excela jako narzędzia do zarządzania projektami .
Znaczna część mocy programu Excel kryje się za formułami i regułami programu Excel, które pomagają automatycznie manipulować danymi i informacjami, niezależnie od tego, jakie dane wstawiasz do arkusza kalkulacyjnego.
Przyjrzyjmy się, jak używać formuł i innych narzędzi, aby lepiej korzystać z programu Microsoft Excel.
Formatowanie warunkowe za pomocą formuł Excel
Jednym z narzędzi, których ludzie nie używają wystarczająco często, jest formatowanie warunkowe. Za pomocą formuł Excel, reguł lub kilku naprawdę prostych ustawień możesz przekształcić arkusz kalkulacyjny w automatyczny pulpit nawigacyjny.
Aby przejść do formatowania warunkowego, wystarczy kliknąć Dom i kliknij Formatowanie warunkowe ikona paska narzędzi.
W formatowaniu warunkowym dostępnych jest wiele opcji. Większość z nich wykracza poza zakres tego konkretnego artykułu, ale większość z nich dotyczy podświetlania, kolorowania lub cieniowania komórek na podstawie danych w tej komórce.
Jest to prawdopodobnie najczęstsze zastosowanie formatowania warunkowego — na przykład zmienianie komórki na kolor czerwony przy użyciu formuł typu „mniej niż” lub „większe niż”. Dowiedz się więcej o tym, jak używać instrukcji IF w programie Excel.
Jednym z rzadziej używanych narzędzi do formatowania warunkowego jest Zestawy ikon opcja, która oferuje świetny zestaw ikon, których można użyć do przekształcenia komórki danych programu Excel w ikonę wyświetlania pulpitu nawigacyjnego.
Po kliknięciu Zarządzaj regułami , zabierze Cię do Menedżer reguł formatowania warunkowego .
zmień okno lokalizacji kopii zapasowej iPhone'a 10
W zależności od danych, które wybrałeś przed wybraniem zestawu ikon, zobaczysz komórkę wskazaną w oknie Menedżera z wybranym zestawem ikon.
Po kliknięciu Edytuj regułę , zobaczysz okno dialogowe, w którym dzieje się magia.
Tutaj możesz utworzyć logiczną formułę i równania, które będą wyświetlać wybraną ikonę pulpitu nawigacyjnego.
Ten przykładowy pulpit nawigacyjny pokaże czas spędzony na różnych zadaniach w porównaniu z czasem przewidzianym w budżecie. Jeśli przekroczysz połowę budżetu, wyświetli się żółte światło. Jeśli całkowicie przekroczysz budżet, zrobi się czerwony.
Jak widać, ten pulpit nawigacyjny pokazuje, że budżetowanie czasu nie jest skuteczne.
Prawie połowa czasu spędzana jest znacznie ponad zabudżetowanymi kwotami.
Czas na skupienie się i lepsze zarządzanie czasem!
1. Korzystanie z funkcji VLookup
Jeśli chcesz korzystać z bardziej zaawansowanych funkcji programu Microsoft Excel, oto kilka, które możesz wypróbować.
Prawdopodobnie znasz funkcję VLookup, która umożliwia przeszukiwanie listy określonego elementu w jednej kolumnie i zwracanie danych z innej kolumny w tym samym wierszu, co ten element.
Niestety funkcja wymaga, aby element, którego szukasz na liście, znajdował się w lewej kolumnie, a dane, których szukasz, znajdowały się po prawej, ale co, jeśli zostaną zamienione?
W poniższym przykładzie, co jeśli chcę znaleźć Zadanie, które wykonałem w dniu 25.06.2018 z poniższych danych?
W tym przypadku przeszukujesz wartości po prawej stronie i chcesz zwrócić odpowiednią wartość po lewej stronie.
Jeśli czytasz fora użytkowników programu Microsoft Excel, wiele osób twierdzi, że nie jest to możliwe w przypadku funkcji VLookup. Aby to zrobić, musisz użyć kombinacji funkcji Index i Match. To nie do końca prawda.
Możesz sprawić, by funkcja VLookup działała w ten sposób, zagnieżdżając w nim funkcję WYBIERZ. W tym przypadku formuła Excela wyglądałaby tak:
'=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)'Ta funkcja oznacza, że chcesz znaleźć datę 25.06.2013 na liście wyszukiwania, a następnie zwrócić odpowiednią wartość z indeksu kolumny.
W tym przypadku zauważysz, że indeks kolumny to '2', ale jak widzisz, kolumna w powyższej tabeli to faktycznie 1, prawda?
To prawda, ale co robisz z „ WYBIERAĆ Funkcja ' manipuluje dwoma polami.
Przypisujesz numery referencyjne indeksów do zakresów danych - przypisujesz daty do indeksu nr 1, a zadania do indeksu nr 2.
Tak więc, gdy wpiszesz „2” w funkcji VLookup, w rzeczywistości odwołujesz się do indeksu numer 2 w funkcji WYBIERZ. Fajnie, prawda?
VLookup używa teraz Data kolumna i zwraca dane z Zadanie kolumna, mimo że zadanie znajduje się po lewej stronie.
jaka jest funkcja w programowaniu
Teraz, gdy już znasz ten mały smakołyk, wyobraź sobie, co jeszcze możesz zrobić!
Jeśli próbujesz wykonać inne zaawansowane zadania wyszukiwania danych, zapoznaj się z tym artykułem na wyszukiwanie danych w Excelu za pomocą funkcji wyszukiwania .
2. Formuła zagnieżdżona do analizy ciągów
Oto jeszcze jedna szalona formuła Excela dla Ciebie! Mogą wystąpić przypadki, w których importujesz dane do programu Microsoft Excel z zewnętrznego źródła składającego się z ciągu danych rozdzielanych.
Po wprowadzeniu danych chcesz je przeanalizować na poszczególne komponenty. Oto przykład informacji o nazwisku, adresie i numerze telefonu rozdzielonych znakiem „;” postać.
Oto, jak możesz przeanalizować te informacje za pomocą formuły Excela (sprawdź, czy możesz mentalnie podążać za tym szaleństwem):
W przypadku pierwszego pola, aby wyodrębnić skrajny lewy element (imię i nazwisko osoby), wystarczy użyć w formule funkcji LEFT.
'=LEFT(A2,FIND(';',A2,1)-1)'Oto jak działa ta logika:
- Przeszukuje ciąg tekstowy z A2
- Znajduje ';' symbol ogranicznika
- Odejmuje jeden dla prawidłowego położenia końca tej sekcji ciągu
- Chwyta tekst z lewej strony do tego momentu
W tym przypadku tekst po lewej stronie to „Ryan”. Misja zakończona.
3. Formuła zagnieżdżona w Excelu
Ale co z pozostałymi sekcjami?
Mogą być na to prostsze sposoby, ale ponieważ chcemy spróbować stworzyć najbardziej szaloną formułę zagnieżdżonego programu Excel (która faktycznie działa), użyjemy unikalnego podejścia.
Aby wyodrębnić części po prawej stronie, musisz zagnieździć wiele PRAWEJ funkcji, aby pobrać sekcję tekstu aż do pierwszego ';' i ponownie wykonaj na nim funkcję LEWO. Oto, jak wygląda wyodrębnianie części adresu z numerem ulicy.
'=LEFT((RIGHT(A2,LEN(A2)-FIND(';',A2))),FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))),1)-1)'Wygląda na szalone, ale nie jest trudno je poskładać. Jedyne, co zrobiłem, to wziąłem tę funkcję:
RIGHT(A2,LEN(A2)-FIND(';',A2))I włożył go w każde miejsce w LEWO funkcja powyżej, gdzie znajduje się „A2”.
To poprawnie wyodrębnia drugą sekcję ciągu.
jak naprawić brak zabezpieczonego internetu?
Każda kolejna sekcja ciągu wymaga utworzenia kolejnego gniazda. Teraz wszystko, co musisz zrobić, to wziąć ' PRAWIDŁOWY ' równanie, które utworzyłeś w ostatniej sekcji, i wklej je do nowej formuły PRAWY z poprzednią formułą PRAWY wklejoną tam, gdzie widzisz 'A2'. Oto jak to wygląda.
(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))))))Następnie musisz wziąć TĘ formułę i umieścić ją w oryginalnej formule LEWEJ, gdziekolwiek jest „A2”.
Ostateczna formuła zginająca umysł wygląda tak:
'=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),FIND(';',(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),1)-1)'Formuła ta prawidłowo wyodrębnia „Portland, ME 04076” z oryginalnego ciągu.
Aby wyodrębnić następną sekcję, powtórz powyższy proces od nowa.
Twoje formuły programu Excel mogą być naprawdę zapętlone, ale wszystko, co robisz, to wycinanie i wklejanie długich formuł do siebie, tworząc długie gniazda, które nadal działają.
Tak, spełnia to warunek „szalony”. Ale bądźmy szczerzy, istnieje o wiele prostszy sposób na osiągnięcie tego samego za pomocą jednej funkcji.
Wystarczy wybrać kolumnę z danymi rozdzielonymi, a następnie pod Dane element menu, wybierz Tekst do kolumn .
Spowoduje to wyświetlenie okna, w którym możesz podzielić ciąg według dowolnego ogranicznika. Wystarczy wpisać ' ; ', a zobaczysz, że podgląd wybranych danych zmieni się odpowiednio.
Za pomocą kilku kliknięć możesz zrobić to samo, co ta szalona formuła powyżej… ale gdzie w tym zabawa?
Zaszalej z formułami Microsoft Excel
Więc masz to. Powyższe formuły dowodzą, jak przesadnie można uzyskać, tworząc formuły Microsoft Excel w celu wykonania określonych zadań.
Czasami te formuły programu Excel nie są w rzeczywistości najłatwiejszym (lub najlepszym) sposobem osiągnięcia pewnych rzeczy. Większość programistów powie Ci, aby zachować prostotę, i jest to tak samo prawdziwe w przypadku formuł programu Excel, jak w przypadku wszystkich innych.
Jeśli naprawdę chcesz poważnie podejść do korzystania z programu Excel, zapoznaj się z naszym przewodnikiem dla początkujących dotyczącym korzystania z programu Microsoft Excel . Ma wszystko, czego potrzebujesz, aby zwiększyć produktywność dzięki Excelowi. Następnie zapoznaj się z naszą ściągawką z podstawowymi funkcjami programu Excel, aby uzyskać więcej wskazówek.
Źródło obrazu: kues/Depositphotos
Udział Udział Ćwierkać E-mail 7 najważniejszych funkcji finansowych w ExceluNiezależnie od tego, czy jesteś księgowym, czy finansistą, powinieneś znać te formuły Excela.
Czytaj dalej Powiązane tematy- Wydajność
- Wskazówki dotyczące arkuszy kalkulacyjnych
- Microsoft Excel
- Microsoft Office 2016
- Porady dotyczące pakietu Microsoft Office
- Microsoft Office 2019
Ryan posiada tytuł licencjata z elektrotechniki. Pracował 13 lat w inżynierii automatyzacji, 5 lat w IT, a teraz jest inżynierem aplikacji. Były redaktor zarządzający MakeUseOf, przemawiał na krajowych konferencjach poświęconych wizualizacji danych i występował w krajowej telewizji i radiu.
Więcej od Ryana DubeZapisz się do naszego newslettera
Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!
Kliknij tutaj, aby zasubskrybować