Kontrollkästchen sind Steuerelemente. Sie dienen dazu, Rechenoperationen zuzulassen oder nicht zuzulassen. Ist ein Kontrollkästchen aktiviert (durch Setzen des Hakens), wird eine Operation, die daran geknüpft ist, ausgeführt.
Excel bietet zwei Arten von Kontrollkästchen an, als Formularsteuerelement und als ActiveX-Steuerelement. Wo findest Du sie? Über Entwicklertools / Einfügen wählst Du jeweils das dritte Element von links. Klicke jetzt die Zelle an, in der das Kästchen liegen soll.
Das Formularsteuerelement formatierst Du, indem Du es mit der rechten Maustaste anklickst und „Steuerelement formatieren“ aufrufst. Du kannst z.B. die Füllung des Elements und die Linien gestalten.
Rufst Du „Steuerung“ auf, kannst Du als Zellverknüpfung eine Zelladresse eingeben. Dort wird bei Aktivierung des Kästchens WAHR und bei Deaktivierung FALSCH stehen. bei Deaktivierung FALSCH stehen. Als Verknüpfung kannst Du auch B4, also unter dem Element wählen. Dann sind WAHR oder FALSCH nicht zu sehen. Besser ist allerdings, die Verknüpfungszelle sichtbar zu halten.
Um das ActiveX-Steuerelement zu formatieren, rufst Du unter Entwicklertools den Entwurfsmodus auf. Klicke dann auf Eigenschaften und formatiere. Die Hintergrundfarbe findest Du unter BackColor. Die Zellverknüpfung kannst Du unter LinkedCell eingeben. Verlasse danach wieder den Entwurfsmodus.
Bei Aktivierung erscheint in der verknüpften Zelle WAHR, bei Deaktivierung FALSCH.
In beiden Elementen kannst Du die Inschrift ändern.
Das Formularsteuerelement klickst Du mit rechter Maustaste an, setzt den Cursor in den Text und änderst ihn. Beim ActiveX-Element gehst Du über den Entwurfsmodus und Eigenschaften. Unter „Caption“ kannst Du den Text ändern.
Wozu kannst Du die Steuerelemente nun nutzen?
Am Beispiel des Formularsteuerelements will ich das erklären, mit dem anderen Element arbeitest Du genauso.
Wenn z.B. das Element auf WAHR geschaltet ist, sollen die Werte in B9 und C9 addiert werden. Ist auf FALSCH geschaltet, sollen sie multipliziert werden.
Weitere Verwendungsmöglichkeiten wirst Du sicher selbst finden.
In den vergangenen Jahren konntest Du im Blog nur die Spielpläne bis hin zur Abschlusstabelle für die 1. Fußball-Bundesliga finden.
Ich erweitere ab der nächsten Saison das Angebot um die 2. Bundesliga und die 3. Liga.
Die 1. Bundesliga beginnt am 13.08.2021 mit dem ersten Spiel. Die 2. Bundesliga und die 3. Liga beginnen ihren Spielbetrieb am 23.07.2021. Am Ende des Beitrags findest Du jeweils die Spielpläne und die Tabelle. Die Spielpläne entstammen den unten angegebenen Quellen.
Für die drei Ligen wurde die Vorlage zur Verwaltung der Spiele unverändert vom Vorjahr übernommen. Folge den beiden Links zu Nachlesen:
Welche Ergebnisse an allen 34 (1. und 2. Liga) bzw. 38 (3. Liga) Spieltagen erzielt werden, kannst du jeweils bequem in die aktuellen Excel-Vorlagen eintragen. Benutze dazu bitte nur die Tabellenblätter 1 bis 34 bzw. 1 bis 38 und trage die Spielergebnisse in die gelb markierten Zellen ein.
Das Modell rechnet sich dann durch bis zur Abschlusstabelle. Dort musst du lediglich in Zelle E5 den aktuellen Spieltag manuell eingeben.
Die Abschlusstabelle wird in der Reihenfolge nicht nach jedem Spiel mit der offiziellen Tabelle der Deutschen Fußball-Liga übereinstimmen, denn es kann zumindest am Anfang noch so sein, dass zwei Mannschaften dieselben Punkte, dieselbe Tordifferenz und dieselbe Anzahl der erzielten Tore aufweisen.
Beachte bitte: Vor dem ersten Spieltag sind in der Abschlusstabelle 1. Bundesliga nur Borussia Mönchengladbach, in der Abschlusstabelle 2. Bundesliga nur der FC Schalke 04 bzw. in der Abschlusstabelle 3. Liga nur der SC Freiburg II gelistet.
Das liegt daran, dass alle Mannschaften noch auf Rang 1 liegen und in der Abschlusstabelle Spalte C mit der Funktion KKLEINSTE operiert wird. Am Ende des ersten Spieltages enthält die Tabelle alle beteiligten Mannschaften.
Das Modell berücksichtigt bei der Berechnung des Rangs nur
Anzahl der Punkte
Tordifferenz
erzielte Tore
Das hat sich für die vergangene Saison als ausreichend erwiesen.
Die Vorlagen kannst Du Dir hier anschließend herunterladen. Viel Spaß damit.
Du möchtest mit Daten aus einer Pivottabelle rechnen. Diese Daten sind nicht in den Quelldaten vorhanden bzw. können dort auch nicht ergänzt werden.
Da hast Du zuerst die Möglichkeit, einfach in einer an die Pivottabelle angrenzenden Spalte diese Berechnungen durchzuführen. Das aber hat den Nachteil, dass diese Spalte bei einer Erweiterung der Spalten der Pivottabelle überschrieben werden kann. Diese Spalte passt sich außerdem nicht einer Veränderung der Zeilenstruktur an.
Das heißt, Du beginnst nach einer Aktualisierung der Pivottabelle erneut, die Werte in der neuen Spalte berechnen zu lassen.
Eine bessere Möglichkeit ist die Arbeit mit sogenannten berechneten Feldern. Das soll der Hauptinhalt dieses Beitrags sein.
Die Tabellenfunktion DATEDIF ist eine undokumentierte Funktion, sie ist deshalb nicht unter Formeln Funktionsbibliothek zu finden.
Laut Angaben der Microsoft Office Support Homepage wird DATEDIF nur angeboten, um ältere Arbeitsmappen von Lotus zu unterstützen.
Mit der Funktion kannst Du die Differenz zwischen einem Anfangs- und einem Enddatum ausrechnen.
Die Syntax für diese Funktion lautet:
=DATEDIF(Anfangsdatum; Enddatum;Zeiteinheit)
Als Zeiteinheiten kommen, bezogen auf die Differenz zwischen beiden Daten, in Frage:
„Y“ Anzahl vollständiger Jahre „M“ Anzahl vollständiger Monate „D“ Anzahl ganzer Tage „MD“ Differenz zwischen den Tagen in Anfangs- und Enddatum, Monate und Jahre werden ignoriert „YM“ Differenz zwischen den Monaten in Anfangs- und Enddatum, Tag und Jahre werden ignoriert „YD“ Differenz zwischen den Tagen in Anfangs- und Enddatum, Jahre werden ignoriert
Ein paar Beispiele dazu:
Formeln in Zeile 5:
=DATEDIF(A5;B5;“Y“)
=DATEDIF(A5;B5;“M“) =DATEDIF(A5;B5;“D“)
Formeln in Zeile 5:
=DATEDIF(A5;B5;“MD“)
=DATEDIF(A5;B5;“YM“)
=DATEDIF(A5;B5;“YD“)
Willst Du nun wissen, wieviel vollständige Jahre + vollständige Monate + ganze Tage zwischen zwei Daten vergangen sind oder anders ausgedrückt: Seit dem Anfangsdatum sind … Jahre + … Monate + … Tage vergangen, solltest Du so vorgehen:
Formeln in Zeile 5:
=DATEDIF(A5;B5;“Y“)
=DATEDIF(A5;B5;“YM“)
=DATEDIF(A5;B5;“MD“)
Ziehe die Formeln jeweils bis Zeile 9 herunter.
Über diesen Link kannst Du Dir eine Beispieldatei„Datumsdifferenzen mit DATEDIF“ herunterladen:
Zu diesem Thema habe ich im Mai 2015 einen Beitrag für die Anwendung unter Excel 2007 veröffentlicht.
Als Aufgabenbereich wurden zu der Zeit mehrere Dateien angelegt, die oft gleichzeitig geöffnet werden müssen. Dies konnte der Fall sein, wenn mehrere Dateien miteinander zu verknüpfen waren. Wenn ein Aufgabenbereich geöffnet wurde, lud Excel alle dazugehörigen Dateien.
Wie war dazu vorzugehen? Zuerst musstest Du alle Dateien, die Du im Aufgabenbereich sehen wolltest, öffnen. Zu beachten war, dass nicht dazugehörige Dateien geschlossen waren. In Excel 2007 konntest Du im Menü Ansicht / Fenster / Aufgabenbereiche speichern aufrufen. Es erschien ein Dialogfenster, das bereits die Dateiendung „.xlw“ (für Aufgabenbereiche) vorgab.
Auf diese Art und Weise konntest Du viele Aufgabenbereich mit den unterschiedlichsten Zusammenstellungen definieren. Der Vorteil war, Du konntest keine Datei vergessen, auf die vielleicht auch verknüpft wurde. Wolltest Du Aufgabenbereiche ändern, hast Du die vorhandenen gelöscht und die Zusammenstellung neu erstellt.
2. Mehrere Dateien unter Excel 2019 gleichzeitig öffnen
Unter Excel 2019 ist diese einfache Möglichkeit nicht mehr vorhanden.
Dennoch gibt es das Problem auch heute noch. Du arbeitest an einem Projekt und musst dazu jeweils mehrere Dateien gleichzeitig geöffnet haben.
Wie das Problem gelöst werden kann, haben Dominik Petri und Markus Hahner in einem aktuellen Lösungsvideo [1] gezeigt.
Lege alle Excel-Dateien, die Du für Dein Projekt benötigst (nur Excel-Dateien) in einen Ordner.
Öffne Excel, gehe auf Datei / Optionen und klicke auf der linken Seite „Erweitert“ an. Scrolle nun die rechte Seite ganz nach unten. Unter „Allgemein“ findest Du „Beim Start alle Dateien öffnen in“. Gebe dort den vollständigen Pfad für den Ablageort Deiner Projektdateien ein und bestätige mit OK.
Beim erneuten Öffnen von Excel werden nun alle Dateien des jeweiligen Ordners geöffnet.
„Wenn Du artig bist, darfst Du ein Eis essen.“ „Wenn Du nicht artig bist, darfst Du kein Eis essen.“
Solche oder ähnliche Mahnungen hat sicherlich schon jeder von uns in seiner Kindheit zu hören bekommen. Das nachfolgende Handeln wird bestimmt durch die Erfüllung einer Vorgabe.
Was aber hat das mit Excel zu tun?
Bei der Erstellung von Planungen, von Kalkulationen oder von Berichten mit dem Tabellenkalkulationsprogramm Excel wird es immer wieder Situationen geben, in denen eine Entscheidung getroffen werden muss, soll es in diese Richtung oder in eine andere weitergehen?
Excel ist auf diese Situationen vorbereitet und bietet mit seinen Funktionen zahlreiche Möglichkeiten, Entscheidungen zu treffen und darauf aufbauend weiterzuarbeiten.
Dieser Beitrag wird sich mit der Nutzung der Excel-Funktionen WENN und WENNS in unterschiedlichsten Konstellationen beschäftigen. Du wirst kennenlernen, wie verschiedene Problemstellungen in Excel-Arbeitsmappen umgesetzt werden können.
Oftmals kommt es vor, dass Vor- und Nachnamen von Personen in einer Zelle stehen. Manchmal ist es aber besser, Vor- und Nachnamen in separaten Spalten abzubilden.
Die beispielhaften Namen sollen nun auf die Zellen D3:E7 aufgeteilt werden.
Dazu markierst Du B3:B7 und klickst in der Multifunktionsleiste auf Daten und dann auf Text in Spalten. Auf dem Bildschirm erscheint diese Anzeige:
Wähle „Weiter“ und klicke dann als Trennzeichen „Leerzeichen“ an.
Wähle wieder „Weiter“, gebe als Zielbereich $D$3 ein und klicke auf „Fertigstellen“.
Im Bereich D3:E7 werden jetzt Vor- und Nachnamen getrennt dargestellt.
In Excel gibt es mittlerweile drei Funktionen, um den Inhalt zweier oder mehrerer Zellen in einer weiteren Zelle zu verbinden.
Die eine ist die Funktion VERKETTEN (engl. CONCATENATE), die es seit Excel 2003 gibt. Hierzu findest Du im Blog auch einen früheren Beitrag: „Zellinhalte in Excel verketten“.
Die Funktion dient in diesem Beitrag dem Vergleich mit den neuen Funktionen.
Die anderen sind die Funktionen TEXTKETTE (engl. CONCAT), verfügbar seit Excel 2019 und TEXTVERKETTEN (engl. TEXTJOIN), ebenfalls seit Excel 2019 verfügbar. Der Beitrag erklärt die drei Funktionen und zeigt Unterschiede auf.
1. Die Funktion VERKETTEN
Die Syntax für die VERKETTEN-Funktion lautet: =VERKETTEN(Text1;Text2,…)
Alternativ kann eine Verkettung auch mit dem Operator & herbeigeführt werden: =Text1&Text2
Text1 (erforderlich) ist das erste Element, das kombiniert werden soll. Es kann Text, wie Wörter oder Zahlen, von Anführungszeichen umgebene Leerzeichen oder Zellverweise auf den Speicherort von Daten in einem Arbeitsblatt sein.
Text2;Text3;…;Text255 (optional bis zu 255 Texteinträge) sind die anderen zu kombinierenden Einträge. Diese können der Funktion VERKETTEN bis zu maximal 8.192 Zeichen einschließlich Leerzeichen hinzugefügt werden. Jeder Eintrag muss durch ein Semikolon getrennt sein.
Verkettet werden können sowohl Texteinträge als auch Zahlen. Das Ergebnis einer Zahlenverkettung wird allerdings als Text gezeigt und kann nicht für mathematische Operationen verwendet werden.
Es ist aber wie gesagt möglich, unterschiedliche Formen miteinander zu kombinieren:
Text: Text schreiben Sie in Excel mit Anführungszeichen. So erkennt das Programm, wie es die Information zu verarbeiten hat.
Zahlen: Zahlen können Sie direkt in die Formel eintragen. Diese können, aber müssen Sie nicht in Anführungszeichen setzen.
Zellbezüge: Möchten Sie den Inhalt einer Zelle an die Funktion übergeben, tragen Sie den Zellbezug direkt als Argument in die Formel ein. Sie können den Bezug entweder relativ (ohne Markierung) oder absolut (mit Dollarzeichen als Marker) einfügen.
Funktionen: Sie haben außerdem die Möglichkeit, andere Funktionen in die VERKETTEN-Funktion einzufügen. Der Output dieser Funktionen wird dann mit den anderen Elementen verkettet.
1.1 Texte und Zellbezüge verketten
a) Die Worte „Laub“ und „Wald“ sollen verkettet werden. Die nachfolgende Abbildung zeigt drei Möglichkeiten dazu.
In Zeile 3 werden die Argumente als Text in Anführungszeichen eingegeben.
In Zeile 4 wird mit Bezügen gearbeitet.
Zeile 5 schließlich zeigt die Verwendung des Operators &.
b) Die nächsten Beispiele sind ebenfalls in den unter a) gezeigten Möglichkeiten darstellbar, hier wird nur die Arbeit mit Bezügen gezeigt.
In Zeile 3 werden Vor- und Nachnamen verkettet, zwischen den Argumenten wird ein Leerzeichen eingefügt.
In Zeile 4 werden die Argumente mit dem in Firmennamen üblichen „&“ verkettet. In Zeile 5 werden drei Argumente mit Leerzeichen und dem „&“ verkettet.
1.2 Zahlen verketten
Die Abbildung zeigt drei Beispiele:
In Zeile 3 werden zwei Zahlen verkettet. Beachte, das Ergebnis ist eine Zeichenfolge im Textformat. Das Textformat ist auch an der linksbündigen Ausrichtung ersichtlich.
In Zeile 4 wurde diese (Text)-Zeichenfolge mit 1 multipliziert. Dadurch wird die Zeichenfolge zur Zahl, was auch an der rechtsbündigen Ausrichtung ersichtlich ist.
In Zeile 5 wird eine Zahl mit einem Text kombiniert. Als Ergebnis erhältst Du eine Geschwindigkeitsangabe.
1.3 Andere Funktionen in VERKETTEN einfügen
Hierzu stelle ich Dir zwei Beispiele vor:
In Zeile 3 ist Text2 kleingeschrieben. Da es sich um einen Nachnamen handelt, willst Du den Text in der Verkettung großgeschrieben sehen. Du verwendest zunächst ein Leerzeichen zwischen Vor- und Nachnamen und setzt vor Text2 die Funktion GROSS2. Damit erreichst Du, dass „bartels“ großgeschrieben wird.
In Zeile 4 ist Text2 großgeschrieben. Da beide Argumente ohne Leerzeichen verkettet werden sollen, setzt Du vor Text2 die Funktion KLEIN. Der Output von KLEIN wird dann mit Text1 verkettet.
Die Syntax für die TEXTKETTE-Funktion lautet: =TEXTKETTE(Text1;Text2,…)
TEXTKETTE ersetzt die ältere Funktion VERKETTEN (siehe Abschn. 1), die aber weiterhin nutzbar bleibt.
Text1 (erforderlich) ist der erste Textwert, Zellbezug oder Bereich. Das zu verkettende Textelement. Eine Zeichenfolge oder ein Array von Zeichenfolgen, wie z. B. ein Zellbereich.
Text2(optional) ist der zweite Textwert, Zellbezug oder Bereich mit weiteren zu verkettenden Textelementen. Für die Textelemente sind maximal 253 Textargumente zulässig. Dabei kann es sich jeweils um eine Zeichenfolge oder ein Array von Zeichenfolgen, wie z. B. um einen Zellbereich, handeln.
Text3 usw. sind optional.
Die Funktion TEXTKETTE führt Text aus Zellen oder Zellbereichen zusammen, wobei keine Trennzeichen oder leere Zellinhalte gesondert berücksichtigt werden. Dazu übergibst Du Text, Zellbezüge sowie Zellbereiche an die Funktion TEXTKETTE welche Du in einer neuen Zelle zusammenfassen möchtest.
2.1 Texte, Zahlen und Zellbezüge verketten
Die Beispiele zeigen, dass sich eine Verkettung von Texten, Zahlen und Zellbezügen mit der Funktion TEXTKETTE genauso verhält wie mit der alten Funktion VERKETTEN.
In den Zeilen 3 und 4 werden Text1 und Text2 als Worte in die Formel eingetragen. Beide Ergebnisse sind nicht korrekt, richtig wäre z.B. Zeile 5.
Zeile 7 zeigt die Verkettung von drei Zahlen. Die Zahlen wurden in die Formel eingetragen. Das Ergebnis ist eine Textzeichenfolge.
In Zeile 8 wurden Bezüge in die Formel eingetragen, das Ergebnis ist eine Textzeichenfolge.
In Zeile 9 wurde zu dem Verkettungsergebnis eine Zahl addiert, das Ergebnis ist nun eine Zahl.
Zeile 11 zeigt das Erstellen einer Textkette über Bezüge.
In Zeile 12 enthält C nichts. Ohne die Ergänzung „ „ in der Formel würde das Ergebnis „Haus-Nr.“ lauten. Die Funktion TEXTKETTE berücksichtigt keine Leerzellen.
Die Zeilen und zeigen die Verkettung von Vor- und Nachname ohne und mit Leerzeichen in der Formel.
2.2 Einfache Bereiche verketten
Die Fähigkeit, z.B. einen Zellbereich mit einer Formel zu verketten, ist erst mit der Funktion TEXTKETTE gegeben. Mit VERKETTEN war das nicht möglich.
Zeile 3 zeigt ein einfaches Beispiel. Drei Zellinhalte werden als Argument B:D in die Formel eingegeben.
In Zeile 4 wird noch einmal ein vorheriges Beispiel aufgegriffen. Die Zelle C5 enthält hier ein Leerzeichen. B:D kann deshalb als Argument eingegeben werden. Das Ergebnis lautet korrekt „Haus-Nr. 16“.
2.3 Mehrspaltige Bereiche verketten
Auch das war mit der alten Funktion VERKETTEN nicht machbar. TEXTKETTE kann aus mehreren Zeilen und Spalten bestehende Bereiche verketten.
Text1 und Text2 umfassen jeweils vier Zeichenfolgen.
In D3 werden die Bereiche B3:B6 sowie C3:C6 durch Semikolon getrennt als Argumente eingegeben.
In D5 wird der Bereich B6:C6 gesamt als Argument gewählt.
Die Funktion „TEXTVERKETTEN“ geht über die beiden vorangegangenen Funktionen hinaus.
Sie kombiniert den Text aus mehreren Bereichen und/oder Zeichenfolgen und fügt zwischen jedem zu kombinierenden Textwert ein anzugebendes Trennzeichen ein. Wenn das Trennzeichen eine leere Textzeichenfolge ist, verkettet diese Funktion effektiv die Bereiche.
Trennzeichen (erforderlich) ist eine Textzeichenfolge, entweder leer oder mindestens ein Zeichen in doppelten Anführungszeichen oder ein Bezug auf eine gültige Textzeichenfolge. Eine eingegebene Zahl wird als Text behandelt. Das können irgendein einzelnes Zeichen, eine Zahl oder Buchstaben sein oder auch ein Wort oder Text.
Leer_ignorieren (erforderlich), wenn es WAHR ist, werden leere Zellen ignoriert, wenn es FALSCH ist, werden leere Zellen einbezogen.
Text1 (erforderlich) ist das zu verkettende Textelement. Eine Textzeichenfolge oder ein Array von Zeichenfolgen, z. B. ein Zellbereich. Text2 (optional) sind weitere zu verkettende Textelemente. Für die Textelemente sind maximal 252 Textargumente zulässig, einschließlich Text1. Dabei kann es sich jeweils um eine Textzeichenfolge oder ein Array von Zeichenfolgen (z. B. um einen Zellbereich) handeln.
3.1 Aufzählungen
In Zeile 3 wurde als Trennzeichen Komma und Leerzeichen gewählt. Mit dem Argument WAHR wird verhindert, dass die leere Zelle D3 mit in die Aufzählung genommen wird. In G3 ist die Aufzählung zu sehen.
In Zeile 6 wird der Bereich B6:B8 verkettet. Die Leerzellen in C6:E8 werden durch das Argument WAHR ignoriert.
Als Trennzeichen wurde Leerzeichen / Bindestrich / Leerzeichen verwendet.
3.2 Text und Währungsangaben verketten
Was wurde hier verkettet? Da sind Vorname, Name, Ort und ein Betrag. Das Währungszeichen „€“ ist nicht in den Texten vorhanden und wurde deshalb als weiteres Textelement in die Formel aufgenommen.
3.3 Verkettung mit verschiedenen Trennzeichen
In diesem Beispiel werden zwei verschiedene Trennzeichen benötigt, ein Leerzeichen sowie Komma und Leerzeichen.
Dazu wurde die Hilfstabelle B10:C10 angelegt. B10 enthält ein Leerzeichen, C10 enthält Komma und Leerzeichen.
Dieser Bereich wird als Argument Trennzeichen in die Formel eingegeben.
3.4 Verkettung verschiedener Inhalte
In diesem Beispiel enthalten Zellen unterschiedlich viele Wörter, Währungsangaben fehlen oder Leerzellen werden berücksichtigt.
In C2:C7 werden Betrag und Währungssymbol manuell dazu getragen.
In C4, D3 und C10 sind jeweils zwei Wörter in einer Zelle enthalten.
In C12 gibt es keinen Eintrag. Die Formel bezieht die Leerzelle mit ein, da als Argument FALSCH angegeben ist. Im Ergebnis sind zwischen „sind“ und „11° C“ jetzt zwei Leerzeichen vorhanden.
4. Fazit
TEXTKETTE und TEXTVERKETTEN sind schon komfortabler als die alte Funktion VERKETTEN.
Dennoch sind alle Beispiele des Beitrages auch noch mit VERKETTEN lösbar.
Und mehr sollte der Beitrag nicht zeigen. Über diesen Link kannst Du die Beispieldatei herunterladen:
