Theo verwendet ein Excel-Arbeitsblatt, um Reservierungen in seinem Unternehmen zu verfolgen. Die Daten bestehen nur aus drei Spalten. Der erste ist der Name einer Person, der zweite die Nummer der ersten Woche (1-52) der Reservierung und der dritte die Nummer der letzten Woche der Reservierung.

Personen können für mehrere Wochen reserviert werden (d. H. Startwoche ist 15 und Endwoche ist 19). Theo benötigt eine Möglichkeit, eine Wochennummer einzugeben und dann anhand einer Formel zu bestimmen, welcher Name (Spalte A) dieser Wochennummer zugeordnet ist. Die Daten werden nicht in einer bestimmten Reihenfolge sortiert, und das Unternehmen lässt Theo kein Makro verwenden, um das Ergebnis zu erhalten (es muss eine Formel sein).

Theos Situation klingt recht einfach, ist aber voller Fallstricke bei der Erarbeitung einer Lösung. Ein Blick auf die potenziellen Daten (wie in der folgenden Abbildung gezeigt) zeigt schnell, warum dies der Fall ist. (Siehe Abbildung 1.)

image

Abbildung 1. Mögliche Daten für Theos Problem.

Beachten Sie, dass die Daten (wie Theo sagte) nicht in einer bestimmten Reihenfolge sind.

Beachten Sie auch, dass es einige Wochen gibt, in denen keine Reservierungen vorliegen (z. B. Woche 5 oder 6), Wochen, in denen mehrere Personen anwesend sind (z. B. Woche 11 oder 16), und Wochen, in denen jemand reserviert ist, aber die Woche Die Nummer wird in Spalte B oder C nicht angezeigt (z. B. Woche 12 oder 17).

Bevor wir uns mit möglichen Lösungen befassen, nehmen wir an, dass die Woche, über die Sie Bescheid wissen möchten, Zelle E1 ist. Sie sollten diesen Bereich als Abfrage bezeichnen. Benennen Sie außerdem den Bereich, der die Namen der Personen enthält (in diesem Beispiel die Zellen A2: A10), als ResNames, die Startwochen (B2: B10) als StartWeeks und die Endwochen (C2: C10) als EndWeeks. Definieren Sie abschließend einen Namen für die gesamte Tabelle (A2: C10), z. B. MyData. Diese Benennung ist zwar nicht unbedingt erforderlich, erleichtert jedoch das Verständnis der Formeln erheblich.

Eine mögliche Lösung besteht darin, eine sogenannte „Hilfssäule“ hinzuzufügen. Fügen Sie der Zelle D2 Folgendes hinzu:

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

Kopieren Sie die Formel für so viele Zellen, wie Namen in der Tabelle enthalten sind. (Kopieren Sie es beispielsweise durch Zelle D10.) Wenn Sie eine Wochennummer in Zelle E1 einfügen, wird das Wort „RESERVIERT“ rechts von jeder Reservierung angezeigt, die diese Wochennummer betrifft. Es ist auch leicht zu erkennen, ob für diese Woche mehrere Personen reserviert sind oder ob für diese Woche keine Personen reserviert sind. Sie können sogar einen AutoFilter anwenden und auswählen, dass nur diese Datensätze mit dem Wort „RESERVIERT“ in Spalte D angezeigt werden.

Falls gewünscht, können Sie auf die Hilfsspalte verzichten und die bedingte Formatierung verwenden, um anzuzeigen, wer für eine gewünschte Woche reserviert ist.

Wählen Sie einfach die Namen in Spalte A aus und fügen Sie eine bedingte Formatierungsregel hinzu, die die folgende Formel verwendet:

=AND(Query>=B2,Query<=C2)

(Wie Sie bedingte Formatierungsregeln eingeben, wurde in anderen Ausgaben von _ExcelTips ausführlich beschrieben.) Stellen Sie die Regel so ein, dass sich die auf die Zelle angewendete Schattierung (das Muster) ändert und Sie leicht erkennen können, welche Reservierungen für die gelten Woche, an der Sie interessiert sind.

Ein anderer Ansatz ist die Verwendung einer Array-Formel. Wählen Sie einige Zellen mehr als die Anzahl der erwarteten überlappenden Reservierungen aus und geben Sie Folgendes in diese Zellen ein, indem Sie Strg + Umschalt + Eingabetaste drücken:

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)(EndWeeks>=Query)(ROW(ResNames)),ROW()-1)-1),"")

Wenn Sie die Anzahl der Zellen auswählen, soll diese Array-Formel belegt sein. Sehen Sie sich beispielsweise die Anzahl der Personen an, die über Woche 11 reserviert werden können. In dem in diesem Tipp gezeigten Beispiel sind es 2 Personen. Wählen Sie mehr als diese Anzahl von Zellen aus und fügen Sie dann die Array-Formel in diese Zellen ein.

Wenn Sie davon ausgehen, dass möglicherweise 20 Personen für dieselbe Woche gebucht haben, sollten Sie eine größere Anzahl von Zellen auswählen, z. B. 20 oder 30. Wählen Sie einfach die Zellen aus, geben Sie die Formel in die Formelleiste ein und dann Drücken Sie Strg + Umschalt + Eingabetaste.

Schließlich sollten Sie wirklich überlegen, wie Ihre Daten angeordnet sind.

Sie können ein Arbeitsblatt mit Wochennummern in Spalte A (1 bis 52 oder 53) erstellen und dann Namen in Spalte B einfügen. Wenn eine Person für zwei Wochen reserviert wurde, wird ihr Name zweimal in Spalte B angezeigt, einmal neben jedem der beiden zwei Wochen, die sie reserviert haben.

Mit Ihren Daten in diesem Format können Sie die Daten leicht scannen, um festzustellen, welche Wochen verfügbar sind, welche genommen werden und von wem sie genommen werden.

Wenn Sie eine Art Suche durchführen möchten, ist es einfach, die VLOOKUP-Funktion basierend auf der Wochennummer zu verwenden, da dies die erste Spalte der Daten in sortierter Reihenfolge ist.

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

Dieser Tipp (11078) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Bestimmen eines Namens für eine Wochennummer.