Błąd obliczeniowy podczas zmiany tabeli WYSZUKAJ.PIONOWO - wskazówki dotyczące programu Excel

Występuje dziwny błąd, który może powodować błędy obliczeń w programie Excel po wprowadzeniu zmian w tabeli odnośników. Biorąc pod uwagę, że motto zespołu Excela brzmi „Recalc or Die”, nie jestem pewien, dlaczego nie załatają tego błędu.

Poniższy rysunek przedstawia formułę WYSZUKAJ.PIONOWO w kolumnie C. Wyszukuje element w B, zwracając czwartą kolumnę z pomarańczowej tabeli odnośników. Na tym etapie wszystko jest w porządku.

Typowa funkcja WYSZUKAJ.PIONOWO. Program Excel jest szybki dzięki algorytmowi inteligentnego przeliczania. W tym przypadku algorytm decyduje się nie przeliczać komórek, które należy obliczyć.

Jeśli ktoś nieumyślnie usunie kolumnę lub wstawi ją do tabeli przeglądowej, dzieje się dziwna rzecz.

Wstaw kolumnę H, a arkusz kalkulacyjny tylko częściowo przelicza.

Co tu się dzieje? To wygląda jak:

Formuła w C2 jest zależna od kolumn F: K, więc przelicza. Spieprzyliśmy sprawę, ponieważ WYSZUKAJ.PIONOWO nadal zwraca czwartą kolumnę tabeli. To daje nam kolor zamiast ceny i powoduje, że formuła Total w D2 zawodzi.
Teraz, gdybym był silnikiem Excel Recalc Engine i gdybym był świadomy i miałbym osobowość, mógłbym powiedzieć do siebie: „Hmmm. Wartość w C2 uległa zmianie. Może powinienem przeliczyć każdą inną identyczną formułę w tej kolumnie”. Ta myśl spowodowałaby ponowne przeliczenie C3, C4 i C5. Ale Excel nie przelicza tych komórek. Nie ma to nic wspólnego z błędem w D2. Nawet bez wzoru w D2, formuły w C3, C4 i C5 nie są obliczane w tym momencie.
Komórki C3, C4 i C5 pozostają błędne, dopóki nie naciśniesz Ctrl + alt = "" + Shift + F9 w celu pełnego przeliczenia.

Nie zrozum mnie źle. Uwielbiam WYSZUKAJ.PIONOWO. Ale ludzie, którzy narzekają na WYSZUKAJ.PIONOWO, sugerują użycie PODAJNIKA jako trzeciego argumentu WYSZUKAJ.PIONOWO w celu rozwiązania tej sytuacji.

Dodaj formułę dopasowania jako trzeci argument WYSZUKAJ.PIONOWO.

Jeśli użyjesz powyższego wzoru, problem z ponownym obliczeniem nie pojawi się.

Poinformowałem zespół programu Excel o tym błędzie, ale dziwnie nie mają oni żadnego priorytetu w naprawianiu problemu. Istnieje od co najmniej programu Excel 2010.

W każdy piątek sprawdzam błąd lub inne podejrzane zachowanie w programie Excel.

Myśl dnia Excela

Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia: