Erstellen Sie einen Kalender in Excel mit einer Formel, indem Sie eine vom Array eingegebene Formel verwenden.
Schauen Sie sich diese Figur an:
Diese Formel =Coolist in jeder Zelle von B5: H10 dieselbe Formel! Aussehen:
Es wurde Array-eingegeben, sobald B5: H10 zum ersten Mal ausgewählt wurde. In diesem Artikel sehen Sie, was hinter der Formel steckt.
Übrigens gibt es eine Zelle, die noch nicht angezeigt wird. Dies ist der Monat, der angezeigt werden soll. Das heißt, Zelle J1 enthält =TODAY()(und ich schreibe dies im Dezember), aber wenn Sie es auf den 08.05.2012 ändern, werden Sie sehen:
Dies ist Mai 2012. OK, definitiv cool! Beginnen Sie von vorne und arbeiten Sie sich im Kalender bis zu dieser Formel vor und sehen Sie, wie es funktioniert.
Nehmen Sie außerdem an, dass heute der 8. Mai 2012 ist.
Schauen Sie sich zunächst diese Abbildung an:
Die Formel macht nicht wirklich Sinn. Es wäre, wenn es von umgeben wäre =SUM, aber Sie möchten sehen, was hinter der Formel steckt, also erweitern Sie es, indem Sie es auswählen und die Taste F9 drücken.
Die obige Abbildung wird zur folgenden Abbildung, wenn die Taste F9 gedrückt wird.
Beachten Sie, dass nach der 3 ein Semikolon steht - dies zeigt eine neue Zeile an. Neue Spalten werden durch ein Komma dargestellt. Sie werden das also ausnutzen.
Die Anzahl der Wochen in einem Monat variiert, aber kein Kalender benötigt mehr als sechs Zeilen, um einen Monat darzustellen, und natürlich haben alle sieben Tage. Schauen Sie sich diese Figur an:
Geben Sie die Werte 1 bis 42 manuell in B5: H10 ein. Wenn Sie =B5:H10eine Zelle eingeben und dann die Formelleiste erweitern, sehen Sie, was hier angezeigt wird:
Beachten Sie die Platzierung der Semikolons - nach jedem Vielfachen von 7 -, die eine neue Zeile anzeigen. Dies ist der Anfang der Formel, aber anstelle einer so langen können Sie diese kürzere Formel verwenden. Wählen Sie B5: H10. Art
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
als Formel, aber drücken Sie nicht die Eingabetaste.
Um Excel mitzuteilen, dass es sich um eine Array-Formel handelt, müssen Sie Strg + Umschalt mit der linken Hand gedrückt halten. Halten Sie Strg + Umschalt gedrückt und drücken Sie mit der rechten Hand die Eingabetaste. Lassen Sie dann Strg + Umschalt los. Für den Rest dieses Artikels wird dieser Satz von Tastenanschlägen als Strg + Umschalt + Eingabetaste bezeichnet.
Wenn Sie Strg + Umschalt + Eingabetaste richtig gemacht haben, werden geschweifte Klammern um die Formel in der Formelleiste angezeigt und die Zahlen 1 bis 42 werden in B5: H10 angezeigt, wie hier gezeigt:
Beachten Sie, dass Sie die durch Semikolons getrennten Zahlen 0 bis 5 (jeweils eine neue Zeile) nehmen und mit 7 multiplizieren. Dies ergibt effektiv Folgendes:
Die vertikale Ausrichtung dieser Werte, die zur horizontalen Ausrichtung der Werte 1 bis 7 addiert wird, ergibt die gleichen Werte wie gezeigt. Die Erweiterung ist identisch mit dem, was Sie zuvor hatten. Angenommen, Sie fügen diesen Zahlen HEUTE hinzu?
Hinweis: Das Bearbeiten einer vorhandenen Array-Formel ist sehr schwierig. Führen Sie die folgenden Schritte sorgfältig aus: Wählen Sie B5: H10. Klicken Sie in die Formelleiste, um die vorhandene Formel zu bearbeiten. Geben Sie + J1 ein, drücken Sie jedoch nicht die Eingabetaste. Um die bearbeitete Formel zu akzeptieren, drücken Sie Strg + Umschalt + Eingabetaste.
Das Ergebnis für den 8. Mai 2012 ist:
Diese Nummern sind Seriennummern (die Anzahl der Tage seit dem 1.1.1900). Wenn Sie diese als kurze Daten formatieren:
Klar nicht richtig, aber du wirst es schaffen. Was ist, wenn Sie diese einfach als "d" für den Tag des Monats formatieren:
Sieht fast wie ein Monat aus, aber kein Monat beginnt mit dem neunten des Monats. Ah, hier ist ein Problem. Sie haben J1 verwendet, das den 08.05.2012 enthält, und Sie müssen wirklich das Datum des Ersten des Monats verwenden. Angenommen, Sie geben =DATE(YEAR(J1),MONTH(J1),1)J2 ein:
Zelle J1 enthält den 08.05.2012 und Zelle J2 ändert dies auf den ersten des Monats, was auch immer in J1 eingegeben wurde. Wenn Sie also J1 in der Formel des Kalenders in J2 ändern:
Näher dran, aber immer noch nicht richtig. Eine weitere Anpassung ist erforderlich, dh Sie müssen den Wochentag des ersten Tages abziehen. Das heißt, Zelle J3 enthält =WEEKDAY(J2). 3 steht für Dienstag. Wenn Sie nun J3 von dieser Formel abziehen, erhalten Sie:
Und das ist eigentlich richtig für Mai 2012!
Okay, du bist ganz nah dran. Was immer noch falsch ist, ist, dass die 29 und 30 vom April im Mai-Kalender erscheinen und der 1. bis 9. Juni ebenfalls auftauchen. Sie müssen diese löschen.
Sie können der Formel zur leichteren Bezugnahme einen Namen geben. Nennen Sie es "Cal" (noch nicht "cool"). Siehe diese Abbildung:
Dann können Sie die Formel so ändern, dass sie einfach ist =Cal(immer noch Strg + Umschalt + Eingabetaste):
Jetzt können Sie die Formel so ändern, dass gelesen wird, dass das Ergebnis leer sein sollte, wenn sich das Ergebnis in Zeile 5 befindet und das Ergebnis beispielsweise über 20 liegt. Zeile 5 enthält die erste Woche eines Monats, daher sollten Sie niemals Werte über 20 sehen (oder eine Zahl über sieben wäre falsch - eine Zahl wie 29, die Sie in Zelle B5 der obigen Abbildung sehen, stammt aus dem Vormonat). So können Sie verwenden =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Beachten Sie zunächst, dass die Zellen B5: D5 leer sind. Die Formel lautet nun "Wenn dies Zeile 5 ist, wenn der TAG des Ergebnisses über 20 liegt, wird leer angezeigt".
Sie können die niedrigen Zahlen am Ende - die Werte des nächsten Monats - weiter entfernen. Hier erfahren Sie, wie Sie dies einfach tun können.
Bearbeiten Sie die Formel und wählen Sie den endgültigen Verweis auf "Cal".
Beginnen Sie mit der Eingabe von IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal), um das endgültige Cal zu ersetzen.
Die endgültige Formel sollte sein
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Drücken Sie Strg + Umschalt + Eingabetaste. Das Ergebnis sollte sein:
Zwei Dinge bleiben noch zu tun. Sie können diese Formel nehmen und ihr den Namen "Cool" geben:
Verwenden Sie das dann in der hier gezeigten Formel:
Definierte Namen werden übrigens so behandelt, als ob sie in ein Array eingegeben würden.
Sie müssen nur noch die Zellen formatieren und die Wochentage und den Namen des Monats eingeben. So erweitern Sie die Spalten, erhöhen die Zeilenhöhe, erhöhen die Schriftgröße und richten den Text aus:
Setzen Sie dann Ränder um die Zellen:
Monat und Jahr zusammenführen, zentrieren und formatieren:
Dann schalten Sie die Gitterlinien aus und voila:
Dieser Gastartikel stammt von Excel MVP Bob Umlas. Es ist aus dem Buch Excel Outside the Box. Klicken Sie hier, um die anderen Themen im Buch anzuzeigen.
