3 szalone formuły Excel, które robią niesamowite rzeczy

  • Peter Holmes
  • 0
  • 5501
  • 1271
Reklama

Formuły Excel mogą zrobić prawie wszystko. W tym artykule dowiesz się, jak potężne mogą być formuły Microsoft Excel i formatowanie warunkowe, z trzema przydatnymi przykładami.

Kopanie w Microsoft Excel

Omówiliśmy wiele różnych sposobów lepszego wykorzystania programu Excel, takich jak używanie go do tworzenia własnego szablonu kalendarza lub używanie go jako narzędzia do zarządzania projektami.

Wiele mocy leży w formułach i regułach Excela, które można pisać w celu automatycznego manipulowania danymi i informacjami, niezależnie od tego, jakie dane wstawisz do arkusza kalkulacyjnego.

Zobaczmy, jak korzystać z formuł i innych narzędzi, aby lepiej wykorzystać program Microsoft Excel.

Formatowanie warunkowe za pomocą formuł programu Excel

Jednym z narzędzi, z których ludzie nie korzystają wystarczająco często, jest formatowanie warunkowe. Jeśli szukasz bardziej zaawansowanych informacji na temat formatowania warunkowego w programie Microsoft Excel, zapoznaj się z artykułem Sandy na temat formatowania danych w programie Microsoft Excel z formatowaniem warunkowym.

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 opcji Formatowanie warunkowe dostępnych jest wiele opcji. Większość z nich jest poza zakresem tego konkretnego artykułu, ale większość z nich dotyczy wyróżniania, kolorowania lub cieniowania komórek na podstawie danych w tej komórce.

Jest to prawdopodobnie najczęstsze zastosowanie formatowania warunkowego, np. Zmiana koloru komórki na czerwoną przy użyciu formuł mniejszych lub większych niż formuły. Dowiedz się więcej o tym, jak używać instrukcji IF w programie Excel.

Jednym z rzadziej używanych narzędzi formatowania warunkowego jest Zestawy ikon opcja, która oferuje świetny zestaw ikon, których można użyć do zamiany komórki danych Excela w ikonę wyświetlania pulpitu nawigacyjnego.

Po kliknięciu Zarządzaj regułami, zabierze Cię do Menedżer reguł formatowania warunkowego.

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.

W tym miejscu możesz utworzyć logiczną formułę i równania, które wyświetlą żądaną ikonę deski rozdzielczej.

Ten przykładowy pulpit pokaże czas spędzony na różnych zadaniach w porównaniu do czasu przewidzianego w budżecie. Jeśli przekroczysz połowę budżetu, wyświetli się żółte światło. Jeśli przekroczysz budżet, staną się czerwone.

Jak widać, ten pulpit pokazuje, że budżetowanie czasu się nie powiedzie.

Prawie połowa czasu spędzana jest znacznie powyżej kwot zapisanych w budżecie.

Czas na zmianę ostrości i lepsze zarządzanie czasem!

1. Korzystanie z funkcji VLookup

Jeśli chcesz korzystać z bardziej zaawansowanych funkcji Microsoft Excel, oto kolejna dla Ciebie.

Prawdopodobnie znasz funkcję VLookup, która pozwala przeszukiwać listę konkretnego elementu w jednej kolumnie i zwracać dane z innej kolumny w tym samym wierszu co ten element.

Niestety funkcja wymaga, aby szukany element na liście znajdował się w lewej kolumnie, a dane, których szukasz, znajdowały się po prawej stronie, ale co, jeśli zostaną przełączone?

W poniższym przykładzie, co jeśli chcę znaleźć Zadanie, które wykonałem 25.06.2018 na podstawie następujących danych?

W tym przypadku przeszukujesz wartości po prawej stronie i chcesz zwrócić odpowiednią wartość po lewej stronie - w przeciwieństwie do normalnego działania VLookup.

Jeśli czytasz fora dla użytkowników programu Microsoft Excel, znajdziesz wiele osób, które mówią, że nie jest to możliwe w VLookup i że musisz to zrobić, używając kombinacji funkcji Indeks i Dopasuj. To nie do końca prawda.

Możesz sprawić, aby VLookup działał w ten sposób, zagnieżdżając w nim funkcję WYBIERZ. W takim przypadku formuła programu Excel wygląda następująco:

„= WYSZUKAJ.PIONOWO (DATA (2018,6,25), WYBIERZ (1,2, E2: E8, A2: A8), 2,0)”

Ta funkcja oznacza, że ​​chcesz znaleźć datę 6/25/2013 na liście odnośników, a następnie zwrócić odpowiednią wartość z indeksu kolumny.

W takim przypadku zauważysz, że indeks kolumny to “2)”, ale jak widać kolumna w powyższej tabeli to tak naprawdę 1, prawda?

To prawda, ale to, co robisz z “WYBIERAĆ” funkcja manipuluje dwoma polami.

Przypisujesz referencję “indeks” liczby do zakresów danych - przypisywanie dat do indeksu numer 1 i zadania do indeksu numer 2.

Więc kiedy piszesz “2)” w funkcji VLookup faktycznie odnosisz się do indeksu nr 2 w funkcji WYBIERZ. Fajnie, prawda?

Więc teraz VLookup używa Data i zwraca dane z Zadanie kolumna, mimo że Zadanie jest po lewej stronie.

Teraz, gdy 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 pełnym artykułem Danna dotyczącym wyszukiwania danych w programie Excel za pomocą funkcji wyszukiwania.

2. Zagnieżdżona formuła do parsowania ciągów

Oto jeszcze jedna szalona formuła Excela.

Mogą wystąpić przypadki, w których albo importujesz dane do programu Microsoft Excel ze źródła zewnętrznego składającego się z ciągu danych ograniczonych.

Po wprowadzeniu danych chcesz je przeanalizować w poszczególnych komponentach. Oto przykład nazwy, adresu i numeru telefonu ograniczonych przez “;” postać.

Oto, w jaki sposób możesz przeanalizować te informacje za pomocą formuły Excela (sprawdź, czy możesz mentalnie postępować zgodnie z tym obłędem):

W przypadku pierwszego pola, aby wyodrębnić lewy element (nazwisko osoby), wystarczy użyć funkcji LEWEJ w formule.

„= LEWY (A2, ZNAJDŹ („; ”, A2,1) -1)”

Oto jak działa ta logika:

  • Przeszukuje ciąg tekstowy z A2
  • Znajduje “;” symbol separatora
  • Odejmuje jeden dla prawidłowego położenia końca tej sekcji ciągu
  • Pobiera do tego momentu tekst znajdujący się najbardziej na lewo

W tym przypadku tekst znajdujący się najbardziej na lewo to “Ryan”. Misja zakończona sukcesem.

3. Zagnieżdżona formuła w programie Excel

Ale co z pozostałymi sekcjami?

Mogą to być łatwiejsze sposoby, ale ponieważ chcemy spróbować stworzyć najbardziej szaloną możliwą formułę zagnieżdżonego programu Excel (która faktycznie działa), zastosujemy unikalne podejście.

Aby wyodrębnić części po prawej, musisz zagnieździć wiele PRAWYCH funkcji, aby pobrać sekcję tekstu aż do pierwszej “;” symbol i ponownie wykonaj na nim funkcję LEWĄ. Oto, jak wygląda wyodrębnienie części numeru ulicy.

„= LEWY ((PRAWY (A2, LEN (A2) -FIND („; ”, A2))), ZNAJDŹ („; ”, (PRAWY (A2, LEN (A2) -FIND („; ”, A2)) ), 1) -1) ”

Wygląda na szalony, ale nie jest trudno go złożyć. Wszystko, co zrobiłem, to wziąłem tę funkcję:

PRAWO (A2, LEN (A2) -FIND (";", A2))

I wstawiłem go do każdego miejsca w LEWEJ funkcji powyżej, gdzie jest “A2”.

To poprawnie wyodrębnia drugą sekcję ciągu.

Każda kolejna sekcja ciągu wymaga utworzenia kolejnego gniazda. Więc teraz po prostu zwariuj “DOBRZE” równanie, które utworzyłeś dla ostatniej sekcji, a następnie przekaż to do nowej PRAWEJ formuły z poprzednią PRAWĄ formułą wklejoną w nią, gdziekolwiek zobaczysz “A2”. Oto jak to wygląda.

(PRAWO ((PRAWO (A2, LEN (A2) -FIND (";", A2))), LEN ((PRAWO (A2, LEN (A2) -FIND (";", A2)))) - ZNAJDŹ ( „;”, (PRAWY (A2, LEN (A2) -FIND („;”, A2))))))

Następnie weź TAKĄ formułę i umieść ją w oryginalnej LEWEJ formule, gdziekolwiek jest “A2”.

Ostateczna formuła zaskakująca wygląda następująco:

"= LEWY ((PRAWY ((PRAWY (A2, LEN (A2) -FIND ("; ", A2))), LEN ((PRAWY (A2, LEN (A2) -FIND ("; ", A2)))) ) -FIND (";", (RIGHT (A2, LEN (A2) -FIND (";", A2)))))), FIND (";", (RIGHT ((RIGHT (A2, LEN (A2)) -FIND (";", A2))), LEN ((PRAWO (A2, LEN (A2) -FIND (";", A2)))) - ZNAJDŹ (";", (PRAWO (A2, LEN (A2) ) -FIND (";", A2)))))), 1) -1) "

Ta formuła poprawnie 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 Excela mogą być naprawdę zapętlone, ale wszystko, co robisz, to wycinanie i wklejanie do siebie długich formuł, tworzenie długich gniazd, które faktycznie działają.

Tak, spełnia to wymaganie “zwariowany”. Ale bądźmy szczerzy, istnieje znacznie prostszy sposób, aby osiągnąć to samo za pomocą jednej funkcji.

Wystarczy wybrać kolumnę z ograniczonymi danymi, 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.

Za pomocą kilku kliknięć możesz zrobić to samo, co ta szalona formuła powyżej… ale gdzie jest w tym zabawa?

Szalony dzięki formułom Microsoft Excel

Więc masz to. Powyższe formuły dowodzą, jak over-the-top osoba może uzyskać podczas tworzenia formuł Microsoft Excel w celu wykonania określonych zadań.

Czasami te formuły Excela nie są tak naprawdę najłatwiejszym (lub najlepszym) sposobem na osiągnięcie celu. Większość programistów powie Ci, żebyś nie musiał się z tym prostować, i to tak samo w przypadku formuł Excela, jak i wszystkiego innego.

Jeśli naprawdę chcesz poważnie korzystać z programu Excel, zapoznaj się z naszym przewodnikiem dla początkujących po korzystaniu z programu Microsoft Excel. Przewodnik dla początkujących po programie Microsoft Excel Przewodnik dla początkujących po programie Microsoft Excel Skorzystaj z tego przewodnika dla początkujących, aby rozpocząć korzystanie z programu Microsoft Excel. Podstawowe wskazówki dotyczące arkuszy kalkulacyjnych pomogą ci zacząć uczyć się Excela na własną rękę. . Ma wszystko, czego potrzebujesz, aby zacząć zwiększać produktywność dzięki Excelowi. Następnie zapoznaj się z naszymi niezbędnymi funkcjami arkusza kalkulacyjnego Excel Najważniejsze formuły i funkcje arkusza kalkulacyjnego Microsoft Excel Niezbędne formuły i arkusze kalkulacyjne Microsoft Excel Pobierz ten arkusz ze wzorami Excel, aby uzyskać skrót do ulubionego programu arkuszy kalkulacyjnych na świecie. .




Jeszcze bez komentarzy

O nowoczesnej technologii, prostej i niedrogiej.
Twój przewodnik w świecie nowoczesnych technologii. Dowiedz się, jak korzystać z technologii i gadżetów, które nas otaczają każdego dnia i dowiedz się, jak odkrywać ciekawe rzeczy w Internecie.