Jan’s Unternehmen hat kürzlich ein Upgrade auf Office 365 ProPlus durchgeführt. Mit ihm kam eine #SPILL! Fehler bei der Verwendung von VLOOKUP, die er viel verwendet. Er kann VLOOKUP jetzt nicht mit einem Filter verwenden. Jan weiß, dass es einfach wäre, die # N / A aus dem vorherigen VLOOKUP zu sortieren und / oder zu entfernen, aber dieser Fehler verursacht ihm viel zusätzliche Zeit und Mühe. Er fragt sich, ob es eine Möglichkeit gibt, diese „Funktion“ auszuschalten.

In der nahen Vergangenheit hat Microsoft die Berechnung von Arbeitsblättern geändert.

Dies war eine RIESIGE Änderung, und Sie haben vielleicht anderswo darüber gelesen. Hier ist ein Artikel, der einige großartige Informationen zu der Änderung enthält:

https://exceljet.net/dynamic-array-formulas-in-excel

Der Artikel ist ziemlich lang; Sie sollten etwas Zeit einplanen, um die darin enthaltenen Informationen zu verarbeiten. Wenn Sie viele Formeln in Excel erstellen, sollten Sie dies jedoch tun. Die Änderung am Programm bedeutet, dass Sie es schließlich verstehen müssen.

Im Wesentlichen hat Microsoft das Konzept der Array-Funktionen abgeschafft (obwohl sie weiterhin funktionieren) und stattdessen fast allen Funktionen, einschließlich VLOOKUP, ermöglicht, ein Array von Werten zurückzugeben. Wenn das Array der zurückgegebenen Werte nicht in den verfügbaren Speicherplatz passt, erhalten Sie die neue #SPILL! Error.

Ganz ehrlich, die Antwort ist nicht, #SPILL zu deaktivieren! Fehler; Es gibt wirklich keine Möglichkeit dazu. Die Antwort besteht darin, zu verstehen, was Excel jetzt beim Berechnen tut, und dann Ihre Formeln entsprechend zu ändern.

Schauen wir uns ein Beispiel an. Angenommen, Sie haben ein Arbeitsblatt, in dem Artikel und ihre Preise in einem einfachen zweispaltigen Datensatz aufgelistet sind. Rechts davon geben Sie einige Artikel ein und verwenden eine VLOOKUP-Funktion, um die mit jedem dieser Artikel verbundenen Preise zurückzugeben. Wenn Sie diese Arbeitsmappe in einer älteren Version von Excel (2019 oder früher) öffnen, erhalten Sie hervorragende Ergebnisse. (Siehe Abbildung 1.)

image

Abbildung 1. Eine einfache VLOOKUP-Formel in Excel 2010.

Dieser Screenshot wurde mit einem Excel 2010-System aufgenommen, funktioniert jedoch genauso, wenn Sie ihn in Excel 2016 oder sogar in Excel 2019 betrachten. Beachten Sie in diesem Beispiel, dass die VLOOKUP-Formel in Zelle F2 (in der Formelleiste angezeigt, weil Zelle F2) angezeigt wird ausgewählt ist) wird in den Bereich F2: F8 kopiert. Sie erhalten die gewünschten Ergebnisse, da die VLOOKUP-Funktion einen einzelnen Wert aus der Tabelle zurückgibt.

Schauen wir uns nun an, was passiert, wenn Sie dieselbe Arbeitsmappe mit denselben Formeln in der mit Office 365 bereitgestellten Excel-Version erstellen. In diesem Fall werden Fehler angezeigt. (Siehe Abbildung 2.)

image

Abbildung 2. Dieselbe einfache VLOOKUP-Formel in der neuesten Version von Excel.

Beachten Sie die #SPILL! Fehler. Dieser Fehler tritt auf, weil die VLOOKUP-Formel jetzt mehr als einen einzelnen Wert zurückgeben kann. Wenn Sie im ersten Parameter für VLOOKUP einen Zellenbereich verwenden, wird jetzt für jede Zelle in diesem Bereich ein Wert zurückgegeben. Wenn Sie also den Bereich E2: E8 für den ersten Parameter verwenden, gibt VLOOKUP 7 Werte zurück. Mit anderen Worten, es wird automatisch ein Array von Werten zurückgegeben. Wenn diese Werte nicht alle angezeigt werden können, erhalten Sie die #SPILL! Error. Deshalb sehen Sie die #SPILL! Fehler in den Zellen F2: F7; Unter ihnen befinden sich Dinge, die verhindern, dass alle in diesen Formeln zurückgegebenen Werte angezeigt werden. Der Fehler in Zelle F8 wird nicht angezeigt, da sich unter dieser Zelle nichts befindet, was die Anzeige stoppt.

Wie können Sie das beheben? Es gibt tatsächlich drei Möglichkeiten, wie Sie das Problem beheben können.

In diesem speziellen Beispiel wäre der einfachste Weg, einfach alles in den Zellen F3: F8 zu löschen. Dadurch kann die Formel in Zelle F2

„verschütten“ richtig zu den restlichen Zellen darunter.

Der zweite Ansatz wäre, die Formel in Zelle F2 zu ändern, also sieht es so aus:

=VLOOKUP(@E$2:E$8,A$2:B$19,2)

Beachten Sie die Verwendung des @ -Symbols unmittelbar vor dem ersten Parameter. Dies teilt Excel mit, dass die VLOOKUP-Formel nur einen einzigen Wert zurückgeben soll. Die andere Möglichkeit, die Formel anzupassen, besteht darin, sie in Zelle F2 so aussehen zu lassen:

=VLOOKUP(E2,A$2:B$19,2)

Jetzt können Sie eine der beiden Formeln aus Zelle F2 in den gesamten Bereich von F2: F8 kopieren, und Sie haben kein Problem. Warum? Weil VLOOKUP in diesen Fällen (wieder) nur einen einzelnen Wert zurückgibt, kein Array von Werten.

Das Ergebnis ist, dass der beste Weg, um Ihre Formeln zu ändern, entweder (1)

ist Stellen Sie sicher, dass nichts die Anzeige des gesamten Wertearrays blockiert, das VLOOKUP zurückgeben soll, oder (2) ändern Sie den ersten Parameter so, dass er nur auf eine einzelne Zelle verweist.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13750) gilt für Microsoft Excel Excel in Office 365.