Samouczek programu Excel: Jak zamienić zagnieżdżone IF na WYSZUKAJ.PIONOWO

W tym krótkim filmie przyjrzymy się, jak zastąpić typową zagnieżdżoną formułę IF formułą WYSZUKAJ.PIONOWO. W porównaniu z zagnieżdżonymi instrukcjami IF funkcja WYSZUKAJ.PIONOWO jest prostsza i bardziej przejrzysta. Łatwiej jest też dostosować później. Po skonfigurowaniu możesz zmienić logikę formuły, nawet nie dotykając samej formuły. Po prostu działa.

Możesz utworzyć lub odziedziczyć arkusz roboczy, który używa serii zagnieżdżonych instrukcji IF do przypisywania jakichś wartości. Wiele osób używa w ten sposób zagnieżdżonych instrukcji JEŻELI, ponieważ podejście jest łatwe, gdy już to zrozumiesz. Jednak zagnieżdżone instrukcje IF mogą być trudne do utrzymania i debugowania.

Spójrzmy, jak zamiast tego możesz użyć funkcji WYSZUKAJ.PIONOWO.

Tutaj mamy klasyczny problem przypisywania ocen punktom. Każdy uczeń na liście ma zestaw wyników testów, które są uśrednione w kolumnie G. W kolumnie H formuła wykorzystuje serię czterech stwierdzeń IF, aby określić ocenę na podstawie średniej. Formuła zaczyna się od niskich wyników i działa aż do wysokich, używając operatora mniej niż.

Dodajmy kolejną kolumnę, która oblicza tę samą ocenę za pomocą funkcji WYSZUKAJ.PIONOWO.

Pierwszą rzeczą, którą zrobimy, jest zbudowanie tabeli, za pomocą której będziemy mogli przypisywać oceny. Będziemy potrzebować kolumny z punktami i kolumną z ocenami. Aby ułatwić uzyskanie potrzebnych wartości z istniejącej formuły, przekonwertujemy zagnieżdżoną formułę JEŻELI na tekst, dodając pojedynczy apostrof przed znakiem równości. Teraz możemy zobaczyć formułę podczas pracy. Musimy dodać wiersz dla każdej możliwej oceny.

Możemy użyć malarza formatów, aby szybko zastosować formatowanie.

Teraz mamy to, czego potrzebujemy, aby przypisywać oceny za pomocą funkcji WYSZUKAJ.PIONOWO. WYSZUKAJ.PIONOWO jest zgodne w pierwszej kolumnie tabeli. Domyślnie funkcja WYSZUKAJ.PIONOWO nie wymaga dokładnego dopasowania, co jest ważne, ponieważ nie chcemy dodawać wiersza dla każdego możliwego wyniku. Jednak tabelę należy posortować w kolejności rosnącej.

Zanim zaczniemy korzystać z funkcji WYSZUKAJ.PIONOWO, zdefiniujmy nazwę tabeli. Nie jest to bezwzględnie konieczne, ale ułatwi to odczytanie naszej formuły. Nazwijmy tabelę „grade_key”.

Teraz dodajmy naszą formułę WYSZUKAJ.PIONOWO. Pierwszy argument to szukana przez nas wartość, którą otrzymujemy z kolumny G. Drugi argument to tabela przeglądowa. Trzecim argumentem jest kolumna zawierająca żądaną wartość. Ponieważ oceny są w drugiej kolumnie, używamy cyfry 2.

WYSZUKAJ.PIONOWO przyjmuje opcjonalny czwarty argument, który kontroluje dokładne dopasowanie. Wartość domyślna to TRUE, co oznacza „dopasowanie niedokładne”. W trybie dopasowania niedokładnego funkcja WYSZUKAJ.PIONOWO dopasuje dokładne wartości, jeśli to możliwe, i następną najniższą wartość, jeśli nie.

Kiedy wchodzimy do wzoru, otrzymujemy pierwszą ocenę. Teraz możemy po prostu skopiować formułę w dół tabeli.

Widać, że otrzymujemy te same oceny, ale z kilkoma fajnymi zaletami.

Po pierwsze, sama formuła jest znacznie łatwiejsza do odczytania. Ponadto klucz oceny jest odsłonięty w arkuszu, aby ułatwić odniesienie. Wreszcie, sam klucz oceny kontroluje oceny. Z łatwością możemy zmienić punktację i uzyskać nowe oceny. Dodatkowo możemy dodać nowe wiersze do klucza i istniejącą formułę „po prostu działa”.

Nie ma potrzeby kłócić się z niesfornym stadem nawiasów.

Następnym razem, gdy napotkasz formułę z zagnieżdżonymi IF, rozważ użycie funkcji WYSZUKAJ.PIONOWO