Excel Grundlagen kompakt erklärt – Teil 2

Im 2. Teil der Excel Grundlagen Kompakt geht es um die Funktionen:

 

Zählenwenn

Summenwenn

Wenn

Wenn1

Sverweis (dieser Funktion wird der 3. Teil gewidmet und erscheint wahrscheinlich im Januar 2019)

Video Excel Grundlagen Kompakt – Teil 2

Wer jetzt doch lieber zuerst Excel Grundlagen Kompakt Teil 1 sehen möchte, klickt auf den Link.

Download der Excel Dateien in zwei Formaten

Im Video zeige ich Ihnen in einer Excel-Tabelle die wichtigsten Grundfunktionen von Excel. Deshalb verzichte ich in diesem Text auf ausführlichere Beschreibungen.

Ich arbeite mit der Office 2016 Version. Den Download der Datei biete ich an in den Dateiformaten  …xlsx und …xls

Sie bekommen die Excel-Datei kostenfrei. Wenn Ihnen das Video und diese Datei gefallen hat, freue ich mich über einen positiven Kommentar in YouTube.

Downloads (identisch mit Excel-Tabelle aus Teil 1)

Wer die Tabelle bereits aus Teil 1 heruntergeladen hat, braucht sie  hier nicht noch einmal laden.

xlsx Format: EXCEL 2016 Datei_Grundlagen erklaert

xls-FormatEXCEL 2016 Datei_Grundlagen erklaert

Wie funktioniert das nun, wenn die Excel Datei heruntergeladen wurde?

Sie öffnen Sie die Datei. Klicken Sie auf „Bearbeiten aktivieren“.

Das Eingangsblatt „Themen“ verweist über Hyperlinks zu den Blättern. Achtung: SVERWEIS wird erst in Teil 3 erklärt. Video wird voraussichtlich im Januar 2019 veröffentlicht. Wenn Sie diesen teil nicht verpassen möchten, abonnieren Sie doch meinen YouTube Kanal.

In den Blättern finden Sie am oberen Rand einen Link „zurück zu den Themen“.

Im Video ist das gut nachzuvollziehen.

Übung macht den Meister

Im Blatt „Wiederholung“  haben Sie Gelegenheit zu üben.

Danke und weiter geht´s im neuen Jahr 2019

Der dritte Teil aus Excel Grundlagen Kompakt beschäftigt sich ausführlicher mit der Funktion SVERWEIS. Das Video wird voraussichtlich im Januar 2019 veröffentlicht. Wenn Sie Teil 3 aus Excel Grundlagen Kompakt nicht verpassen möchten, abonnieren Sie doch meinen YouTube Kanal.

Ich wünsche Ihnen viel Erfolg mit Excel.

Excel Grundlagen kompakt erklärt – Teil 1

Video Excel Grundlagen Kompakt ansehen

Download der Excel Dateien in zwei Formaten

Im Video zeige ich Ihnen in einer Excel-Tabelle die wichtigsten Grundfunktionen von Excel. Deshalb verzichte ich in diesem Text auf ausführlichere Beschreibungen.

Ich arbeite mit der Office 2016 Version. Den Download der Datei biete ich an in den Dateiformaten  …xlsx und …xls

Sie bekommen die Excel-Datei völlig kostenfrei. Wenn Ihnen das Video und diese Datei gefallen hat, freue ich mich über einen positiven Kommentar in YouTube.

Wie funktioniert das nun, wenn die Excel Datei heruntergeladen wurde?

 

Natürlich öffnen Sie die Datei.

Das Eingangsblatt „Themen“ (erste Abbildung oben) verweist über Hyperlinks zu den Blättern. In den Blättern finden Sie am oberen Rand einen Link „zurück zu Themen“. So navigieren Sie in der Excel-Datei.

Was sehe ich im Video Excel Grundlagen kompakt erklärt?

Ich beginne mit den verschiedenen Möglichkeiten der Dateneingabe und den Ausfüllfunktionen: Text, Zahlen, Mischung Text und Zahlen, Datum.

Im zweiten Schritt erstelle ich eine Formel in der Grundrechenart Subtraktion und zum Schluss des ersten Teils, stelle ich die Summe-,Mittelwert- und Max-Funktion dar.

Die übrigen Funktionen:

Die Min Funktion, Anzahl Funktion und Anzahl2 Funktion sind im jeweiligen Tabellenblatt erklärt. Das Bild oben zeigt ein Beispiel für die Anzahl2 Funktion.

Innerhalb der Tabellenblätter liefere ich ausführliche Erläuterungen.

Am Ende nochmals die Möglichkeit des Downloads der Excel Dateien

Ih arbeite mit der Office 2016 Version. Den Download der Datei biete ich an in den Dateiformaten  …xlsx und …xls

Sie bekommen die Excel-Datei völlig kostenfrei. Wenn Ihnen das Video und diese Datei gefallen hat, freue ich mich über einen positiven Kommentar in YouTube.

„Wiederholung“

Im Blatt „Wiederholung“ haben Sie Gelegenheit zu üben, sofern Sie das möchten.

Excel Grundlagen Kompakt Teil 2

„kommt in den nächsten Tagen“ war etwas zu ambitioniert von mir. Es sind dann doch wieder X Wochen geworden. Ich freue mich sehr darüber, dass ich gefragter als je zuvor bin. Leider bleiben die Videos dabei auf der Strecke. Ich bitte um Ihr Verständnis.

 

Ich wünsche viel Spaß und Erfolg mit Excel

Ihr Othmar Wrana

Excel Datei Analyse

Excel Datei Analyse

Wir zeigen Ihnen, welche Möglichkeiten sie haben, sich über den Inhalt einer komplexen Excel Datei zu informieren. Mit einer Excel Datei Analyse verschaffen Sie sich von Anfang an einen guten Überblick in einer umfangreichen Excel-Datei. Schritt für Schritt gehen Sie mit Cornelia Volk durch neun Tabellenblätter und schauen sich Formeln, Bereichsnamen und bedingte Formatierungen genauer an.

Möglicher Anwendungsfall: Sie bekommen von einem Kollegen ein Arbeitsgebiet übergeben und in Verbindung damit eine Excel-Datei, über die sie sich einen Überblick verschaffen wollen.

Downloaden der beiden Tabellen für Ihre Übungen und ein PDF Kurzbefehle:

Tabelle 1 – Demo_Analyse

Tabelle 2 – RepGehalt

PDF Format – Zusammenfassung Befehle für Excel Datei Analyse

Unsere Referentin, Cornelia Volk, referiert in der ICH-Form und so haben wir auch den Text zum Video verfasst.

Wieviele Blätter gibt es grundsätzlich?

Rechtsklick im Navigationsbereich und ich bekomme in einem neuen Fenster sämtliche vorhandene Blätter aufgelistet. Mit einem Klick auf ein Blatt wähle ich, welches ich zuerst sehen möchte. Im Beispiel entscheide ich mich für das Blatt Ausgaben-Kontrolle“.

Ist das wirklich alles, was ich hier sehe?

Wenn ich sehen möchte, ob eine Tabelle noch weitere Informationen enthält, halte ich die STRG-Taste fest und tippe gleichzeitig die „ENDE-Taste an.

STRG festhalten ► gleichzeitig ENDE

Der Cursor landet in der Zelle AC16, d. h. es gibt tatsächlich noch einen Auswertungsbereich, der weiter abgelegen ist.

Die Tastenkombination STRG + ENDE bringt sie immer zur maximalen Ausdehnung des belegten Bereiches, im Beispiel rechts von AC unterhalb von Zeile 16 kann es keine Inhalte mehr geben. 

Was wurde berechnet und wie sehen die Formeln aus?

Tabellenblatt Umsatzergebnisse: Um auf einen Streich sämtliche Formeln zu sehen, können Sie entweder mit der Tastenkombination STRG + Umsch + ` die Formeln einblenden (automatisch erweitert sich die Spaltenbreite), oder Sie gehen über die Menüführung FormelnFormeln anzeigen

Tipp: Schnellzugriffsleiste erweitern

Finden Sie diesen Schalter praktisch, dann klicken Sie „Formeln anzeigen“ mit rechts an und fügen den Schalter zu Ihrer Symbolleiste für den Schnellzugriff hinzu. Automatisch wird der Schalter am rechtem Ende angeschlossen.

Ihre Schnellzugriffsleiste befindet sich vermutlich oben. Wenn das so ist, berühren Sie vielleicht bald den Dateinamen. TIPP dazu: die Schnellzugriffsleiste unter dem Menüband anzeigen lassen. Hier haben Sie genug Raum für alle Schalter.

Leider gibt es noch keinen allgemeinen Schalter, der die Excel Datei Analyse zusammenfasst und alles in einem Rutsch erledigt

Bedingte Formatierung ja oder nein?

Die Produktionszahlen (Blatt Prod_Zahlen) zeigen, an welchem Tag welche Maschine, welche Anzahl von Exemplaren produziert hat. Hier wurde mit Füllfarben gearbeitet und ich frage mich, warum hat der Kollege das von Hand formatiert? Der höchste und der niedrigste Wert wurden in der Beispieltabelle markiert.

Wenn ich nicht sicher bin, ob etwas unstimmig ist mit den Füllfarben, wie in der Zeile 5 im Beispiel. Dort wurde vom Kollegen nur ein Feld gefärbt. Steuern Sie über

START ► SUCHEN UND AUSWÄHLEN ► BEDINGTE FORMATIERUNG

alle Zellen an, die eine bedingte Formatierung enthalten.

Ich stelle fest, die Zeilen zwei und vier sind mit einer bedingten Formatierung versehen. Die Zeile fünf ist es nicht. Wenn ich alle möglicherweise überflüssigen Farben entfernen möchte, kann ich problemlos den Bereich markieren und keine Füllung einstellen. Natürlich bleiben die Zellen erhalten, die über die bedingte Formatierung gefärbt sind. Nur das, was offensichtlich manuell eingetragen war, verschwindet.

Geschütztes Tabellenblatt

Das Tabellenblatt Büromaterial ist ein kleines Rechnungsformular für Bürozubehör. Sofort fällt auf, dass im Register START alle Schalter grau sind, d.h. ausgeblendet und nicht zu aktivieren sind. Das ist ein sicherer Hinweis darauf, dass es sich um eine geschützte Tabelle handelt. Sie können das ganz einfach testen.

Markieren Sie einen beliebigen Bereich und tippen auf die ENTF-Taste. Eine entsprechende Meldung taucht auf. Die Mengen in der Tabelle wären durchaus veränderbar, aber die Formel natürlich nicht.

Tabelle Werteliste nur Namen und Werte

Die Prüfung der Tabelle Werteliste: Die Tabelle enthält Namen und Werte. Mit STRG + ENDE stelle ich fest, die Tabelle enthält nicht mehr als diese Aufstellung.

Datenüberprüfung

Die Tabelle Artikel-Info zeigt Farben. Wenn ich die Farben lösche, verschwinden sie wirklich, d. h. es war keine bedingte Formatierung und sie sehen, dass sich eine Dropdown-Liste zeigt. Dropdown-Listen können entweder über Steuerelemente, oder über Datenüberprüfung eingerichtet sein.

Am schnellsten sehen sie nach, wenn sie wieder den Schalter

SUCHEN und AUSWÄHLEN ►Datenprüfung verwenden.

 

Wie zuvor schon angenommen, enthalten die beiden Felder in den Zeilen 1 und 3 eine Datenüberprüfung. Offensichtlich unterliegt der Einkaufspreis auch bestimmten Regeln.  Das hätten Sie ohne Nachschauen nicht bemerkt. 

Markieren Sie nun nur den Bereich der E-Preise.

datenüberprüfung regelwerk

Datenüberprüfung Regelwerk

Über DATEN – DATENÜBERPRÜFUNG sehen Sie nach,

welches Regelwerk vorgesehen ist und zwar im Fenster Datenüberprüfung. Tatsächlich sind hier nur Einkaufspreise zwischen 5 und 50 gestattet.

fenster datenüberprüfung

Fenster Datenüberprüfung

Befinden sich irgendwelche Formeln in der Rechnungsvorlage?

Die Rechnungsvorlage scheint relativ groß. Wenn ich wissen möchte, ob sich darauf irgendwelche Formeln befinden, klicke ich auf SUCHEN und AUSWÄHLEN im Start-Register. Ein weiterer Klick auf FORMELN zeigt mir in einer Meldung, es sind nirgendwo Zellen, die Formeln enthalten.

SUCHEN und AUSWÄHLEN ► FORMELN

Überflüssige Bereichsnamen löschen

Ich verwende die leere Tabelle 3 um zu prüfen, ob es Bereichsnamen gibt. Dazu stelle ich den Cursor in ein beliebiges leeres Feld und tippe die Funktionstaste F3.

bereichsnamen

Bereichsnamen

Ich könnte Bereichsnamen in Formeln übernehmen, aber ich möchte gerne eine Liste in die leere Tabelle einfügen. Mit Blick auf das Blatt Büromaterial erkenne ich, dass offensichtlich mit Bereichsnamen gearbeitet wurde. Bei den Umsatzergebnissen gibt es auch noch etwas. Zusätzlich gibt es hier einen Bereichsnamen, der schon lange überflüssig ist.

Überflüssige Bereichsnamen löschen Sie über folgenden Weg

FORMELN ► NAMENS-MANAGER.

Im neuen Fenster markieren Sie den Bereichsnamen und löschen Sie die überflüssigen Bereichsnamen. Wenn Sie gelöscht haben, dann die Namensliste gleich noch einmal abrufen mit F3 und Liste einfügen. Sie aktualisiert sich nicht selbst.

ueberfluessige bereichsnamen loeschen

überflüssige Bereichsnamen löschen

Verknüpfungen prüfen

Ich erinnere mich an den Kollegen, als er mir sagte, es gäbe Verknüpfungen im Mitarbeiterblatt. Wo kann ich das prüfen?

Das geschieht über  DATEI ► INFORMATIONEN.

 datei-informationen

Datei-Informationen-Verknüpfung

Unten rechts gibt es nun einen Schalter an dem Sie erkennen, ob die Datei überhaupt Verknüpfungen enthält: VERKNÜPFUNGEN MIT DATEIEN BEARBEITEN. Er wäre sonst gar nicht sichtbar.

 Ich klicke VERKNÜPFEN MIT DATEIEN BEARBEITEN an. Ein neues Fenster öffnet sich. Ich könnte die Quelle öffnen, ändern oder die Verbindung einfach aufheben.

Im nachfolgenden Bild sehen Sie auch, dass es sich um die verknüpfte Datei RepGehalt.xlsx handelt. Sollten Sie mit unserer Beispieldatei die Übungen nacharbeiten, laden Sie sich bitte beide Dateien herunter, damit Sie keine Fehlermeldung beim Öffnen erhalten. Natürlich könnten Sie an dieser Stelle die Verknüpfung nun aufheben.

verknüpfungen mit Dateien bearbeiten

Verknüpfungen mit Dateien bearbeiten

Mit dem linken oberen Pfeil gehe ich aus dieser Informationsanzeige zurück in die Tabelle. Wir haben viele Tabellenblätter in dieser Excel Datei und ich möchte eine letzte Prüfung vornehmen.

Welche Tabellenblätter sind mit der externen Quelle verbunden?

  • Ich steuere auf die erste Tabelle.
  • Halte die Shift oder Umschalttaste fest
  • Steuere an das Ende und klicke dort die letzte Tabelle an.

Damit sind sämtliche Tabellenblätter ausgewählt.

Oben im Dateinamen finden Sie den Vermerk Gruppe. Das bedeutet, dass Excel das nächste Kommando auf die ganze Gruppe von Tabellenblättern anwendet.

verbundene tabellen suchen

verbundene tabellen suchen

Ich lasse suchen. Klicken Sie wieder auf den Schalter in der Menüleiste

 SUCHEN und AUSWÄHLEN ► SUCHEN.

Im darauffolgen Fenster geben Sie im Feld „Suchen nach“ eine eckige Klammer ein. Am besten klicken Sie auf den Befehl „Alle suchen“.

Ziehen Sie den Abschnitt für das Ergebnis etwas auf. Es gibt eine Reihe von Feldern, die diese Verknüpfung enthalten. In der Beispieldatei befinden sich alle im Blatt Ausgaben-Kontrolle.

Mit der Eingabe der eckigen Klammer finden Sie Verknüpfungsfelder

Der Name der externen Datei ist immer von eckigen Klammern umgeben und dementsprechend finden Sie Verknüpfungsfelder ganz leicht, wenn Sie nach einer eckigen Klammer in den Formeln suchen.

Viel Erfolg und Spaß mit Excel und vor allem bei der zukünftigen Excel Datei Analyse.

Cornelia Volk und Othmar Wrana

wenn Funktionen in Excel 2016 mit mehreren Bedingungen erstellen

Wenn Funktionen in Excel (Maxwenns-Minwenns-Mittelwertwenns) 2016 Schritt für Schritt erklärt

Im Video stellt Ihnen Cornelia Volk neue wenn Funktionen in Excel  vor. Sie finden die neuen Funktionen in der Version Office 2016/Office 365. Es handelt sich um die Möglichkeit, die statistische Auswertung Maximum, Minimum und Durchschnitt, die Ihnen bereits bekannt sind, mit Bedingungen zu versehen. Die neuen Bezeichnungen für die Funktionen lauten: 

MINWENNS, MAXWENNS und MITTELWERTWENNS

Mit den WENN Funktionen in Excel können Sie nun bis zu 127 Werte überprüfen. Ähnliche Funktionen wie Summewenns und Zählenwenns sind seit der Office Version 2007 integriert.

Im Video haben wir eine Tabelle vorbereitet.

Wir wollen darstellen, wie hoch der Umsatz in welcher Filiale ist, oder welche Filiale den geringsten Umsatz aufweist. Zuletzt berechnen wir den Durchschnitt.

Sie können wie immer mit dem Assistenten vorgehen. Starten Sie das Video.

Video zum Thema „wenn Funktionen in Excel 2016 mit mehren Bedingungen“

 

 

 

 

Statistikfunktion MINWENNS

In der Gruppe der Statistikfunktionen wählen Sie die Funktion MINWENNS.

Wenn Funktionen in Excel

Erster Kriterienbereich

Ich möchte das Minimum aus dem Wertebereich Betrag (Spalte C) erfahren.

Dazu soll Excel zwei Bedingungen berücksichtigen.

Kriterien_Bereich1 ist der Bereich der Filialen (Spalte A).

Das zugehörige Kriterium1 ist die Filiale, also der Eintrag Frankfurt in der Zelle F2.

Erster Kriterienbereich in wenn Funktionen in Excel

Zweiter Kriterienbereich

Analog dazu haben Sie einen zweiten

Kriterien_Bereich2, den der Produkte (Spalte B).

Als Kriterium2 wählen Sie Produkte NB in der Zelle F3.

Sie können viele Kriterien Bereiche und jeweils ein Kriterium vorsehen. Mit der WENNS-Funktion können Sie bis zu 127 Werte überprüfen. Der Auswertungsbereich bleibt immer gleich. Betrachten Sie das Formelergebnis in der Vorschau.

Das Fenster bitte mit OK bestätigen.

Wenn Sie für einen kurzen Test die verschiedenen Eintragungen für Frankfurt markieren, stellen Sie fest, das mit 345 der niedrigste Umsatz beziffert ist, der in einer Filiale Frankfurt mit dem Produkt in NB erreicht wurde.

In gleicher Weise funktioniert die Statistikfunktion MAXWENNS

Wenn Sie beginnen einen Funktionsnamen zu schreiben, versucht Excel den passenden zu ermitteln. Also geben Sie direkt ein „=max“ (ohne Anführungsstriche) und übernehmen MAXWENNS mit der Tab-Taste.

Schauen Sie sich die Hinweise auf die erforderlichen Angaben an.

Der Max-Bereich ist der Wertebereich Betrag (Spalte C)

Kriterien_Bereich1 und 2 –  MAXWENNS

Der Kriterien_Bereich1 ist der Standort (Spalte A)
Das Kriterium1 hierzu die aktuelle Filiale Frankfurt (Zelle F2)
Der Kriterien_Bereich2 das Produkt (Spalte B)

Das Kriterium2 hierzu das aktuelle Produkt NB (Zelle F3)

Wie Sie vielleicht wissen, dürfen Sie bei diesen Funktionen die letzte Klammer vernachlässigen.
Excel setzt sie selber ein.

Statistikfunktion MITTELWERTWENNS (Durchschnitt) eintragen.

Auswertungsbereich (Spalte C)

Kriterien_Bereich 1 und 2 MITTELWERTWENNS

Erster Kriterien_Bereich1 (Spalte A für Filiale).
Erstes Kriterium1, hier Filiale Frankfurt

Zweiter Kriterien_Bereich2 (Spalte B für Produkt)
Zweites Kriterium2, hier Produkt NB.

Wenn Funktionen in Excel in der fertigen Tabelle

Nun können Sie flexibel vergleichen.

Wie sieht es zum Beispiel in Wiesbaden aus?

Zusammenfassung

Syntax neue wenn Funktionen in Excel:

=MAXWENNS(Max_Bereich; Kriterienbereich1; Kriterien1; [Kriterienbereich2; Kriterien2]; …)

=MINWENNS(Min_Bereich; Kriterienbereich1; Kriterien1; [Kriterienbereich2; Kriterien2]; …)

=MITTELWERTWENNS(Summe_Bereich; Kriterien_Bereich1; Kriterien1; [Kriterien_Bereich2; Kriterien2]; …)

 Die Funktionen MIN, MAX und MITTELWERT mit mehreren Bedingungen.

In der Excel 2016 Version ist es jetzt möglich, die oben genannten Funktionen mit mehreren Bedingungen zu erstellen. Bislang war es nur möglich, einen Kriterien-Bereich und ein Kriterium vorzugeben. Die neuen Bezeichnungen für die Funktionen lauten:

MAXWENNS; MINWENNS; MITTELWERTWENNS

Damit können mehrere Kriterien Bereiche und die zugehörigen Kriterien festgelegt werden. Bei der Eingabe über den Funktions-Assistenten werden die erforderlichen Argumente wie folgt eingegeben:

Argumente in der Hilfszeile der Funktion

Zunächst wird der Bereich markiert, aus dem die gewünschten Werte ermittelt werden sollen, im Assistent die Zeile Max_Bereich.

Danach wird der erste Kriterien Bereich markiert und im Feld Kriterien1 das gewünscht Kriterium eingegeben. Dieser Vorgang wird analog für die nächsten Kriterien Bereiche und Kriterien durchgeführt.

Wird die Funktionseingabe per Tastatur durchgeführt, werden die notwendigen Argumente in der Hilfszeile der Funktion angezeigt, wie deutlich im letzten Bild dieses Beitrages zu erkennen ist.

Das war ein kleiner Überblick über die wenn Funktionen in Excel mit mehreren Bedingungen.

Viel Erfolg wünscht Ihnen

Othmar Wrana

Ewiger Kalender für Excel erstellen

Ewiger Kalender für Excel

ein ewiger Kalender für Excel zeichnet sich dadurch aus, dass Sie für ein beliebiges Jahr den zu einem Datum gehörenden Wochentag ablesen können. Mit diesem kurzen Skript erhalten Sie die Schritte und Formeln für einen ewigen Kalender.
Erfahren Sie, wie Sie die Wochenenden farbig hervorheben und die Schaltjahre berücksichtigen.  Zum Schluss heben Sie Ihren Geburtstag hervor und lesen ab, auf welche Wochentage die zukünftigen Geburtstagsfeiern fallen. Nicht unbedingt ein MUST TO HAVE – sondern eher ein NICE TO HAVE.

VIDEO

Schritte und Formeln zum ewigen Kalender für Excel:

VORGEHENSWEISE

  1. Eintrag der Jahreszahl 2017 im Feld A1 ENTER
  2. Eintrag von „Januar“ im Feld A2 ENTER
  3. Der Eintrag von A2 wird bis zur Spalte L ausgefüllt;
    ergibt Januar ….. Dezember
  4. In A3 wir folgende Formel erstellt:

=DATUM($A$1;SPALTE();ZEILE()-2)                  ENTER

 Die Formel wird bis zu Zeile A33 ausgefüllt

  1. Der Bereich A3:A33 wird markiert.
  2. Dieser Bereich wird bis Spalte L ausgefüllt
  3. Damit wird das Grundgerüst erstellt.
  4. Manuell werden jetzt die Felder entfernt, die keinen 31. Tag haben; für den Februar entsprechend. Also für Februar, April, Juni, September und November.

Datumsformat optimieren und Wochentage anzeigen

Diese Anzeige erhalten Sie über die Zahlenformate:
Zunächst die Tabelle wieder von A3 : L33 markieren und wie im Bild die Reihenfolge abarbeiten.

Wochenenden farbig hervorheben

Dazu wird die Bedingte Formatierung verwendet, wobei eine Formel für die Wochenenden zugrunde gelegt wird.

VORGEHENSWEISE:

  1. Markieren von A3:L33
  2. Register START – Bedingte Formatierung
  3. Neue Regel
  4. Formel zur Ermittlung der zu formatierenden Zellen verwenden.

Neue Regel

Für den Samstag

Für den Sonntag

5. Formatieren klicken

6. Im Register AUSFÜLLEN wählen Sie die Farbe – in unserem Beispiel GRÜN – und bestätigen alles mit einem Klick auf OK.

Erstellen der Formel für die Schaltjahre im ewigen Kalender für Excel

VORGEHENSWEISE

  • Dafür muss in Feld B31 folgende Wenn-Funktion erstellt werden:
  • =WENN(TAG(B30+1)=29;B30+1;““)

Die Funktion Tag ermittelt den Tag eines Datums.

Den eigenen Geburtstag ROT anzeigen lassen

VORGEHENSWEISE

  1. Markieren Sie die Monatsspalte Ihres Geburtstages
  2. Start – Bedingte Formatierung
  3. Neue Regel
  4. Wählen Sie den Befehl: „Nur Zellen formatieren die enthalten“

Für den 29. April zum Beispiel lautet die Formel  =datum($A$1;04;29)

$a$1 steht für die Zelle mit der Jahreszahl; 04 für den Monat April und 29 für den Tag.

Geben Sie nun eine Jahreszahl in der Zukunft an, z. B. 2018, sehen Sie gleich , dass Ihr Geburtstag in 2018 auf einen Sonntag fällt.

Vielen Dank und viel Erfolg mit Excel

Differenz zwischen zwei Datumsangaben berechnen

Wir möchten Ihnen an einem einfachen Beispiel zeigen, wie Sie die Berechnung der Differenz zwischen unterschiedlichen Datumsangaben erreichen und bringen Ihnen die Funktion für diese Berechnung näher.

Wozu benötige ich überhaupt die Differenz zwischen zwei Datumsangaben, die sog. Datumsdifferenz?

Was kann man mit dieser Funktion bewirken? Zunächst kann man ganz genau die Jahre, Monate und Tage zwischen zwei Datumswerten errechnen. Wieviel Jahre wir auf der Welt wandeln ist meistens klar, aber bei den Monaten oder Tagen hört es dann auf. Wie viele Tage müssen Sie bis zum nächsten Geburtstag warten? Je älter man wird, desto mehr interessiert vielleicht die Berechnung der Wartezeit auf die Rente? Das sind mehr oder wenige Beispiele für den privaten Einsatz.

Im beruflichen Umfeld sieht das anders aus. Abgesehen von den einfachen Beispielen im ersten Absatz, dem Video und am Ende des Beitrages, wird diese Art der Datumsberechnung häufig nötig für Rentenberechnungen, für das Erfassen von Projektarbeitszeiten, zur Berechnung von Auftragslaufzeiten, Versicherungsprämien und dergleichen.

Im Video zeigen wir das Anwendungsbeispiel für das Alter einer Person in Jahren, Monaten und Tagen zum heutigen Datum: (also Datum der Videoerstellung)

 

Die Funktion DATEDIF berechnet die Datumsdifferenz

Die Funktion, die es schon lange im EXCEL gibt (aus Kompatibilitätsgründen mit Lotus 123) die aber nicht dokumentiert ist, erfassen Sie manuell. Der Assistent ist nicht hilfreich. Die Syntax lautet:

= DATEDIF(Anfangsdatum;Enddatum;“Zeiteinheit“)

Die DATIF-Funktion braucht drei Argumente:

  1. das Anfangsdatum, oder Startdatum
  2. das Enddatum ( im Video wegen stichtagsgenauer Berechnung das Tagesdatum, das automatisch mit „heute“ ermittelt wird, ansonsten tragen sie den entsprechenden Stichtag ein.
  3. eine Zeiteinheit (was soll ausgegeben werden?), die in Anführungsstrichen und einem Kürzel (d,m,y,yd,ym und md) geschrieben wird.

Die (Zeit)-einheiten lauten:

Differenz in Tagen: =DATEDIF(Anfangsdatum;Enddatum;“D“)

Differenz in Monaten: =DATEDIF(Anfangsdatum;Enddatum;“M“)

Differenz in Jahren: =DATEDIF(Anfangsdatum;Enddatum;“Y“)

Differenz in Tagen ohne volle Jahre: =DATEDIF(Anfangsdatum;Enddatum;“YD“)

Differenz in Monaten ohne volle Jahre: =DATEDIF(Anfangsdatum;Enddatum;“YM“)

Differenz in Tagen ohne volle Jahre/Monate: =DATEDIF(Anfangsdatum;Enddatum;“MD“)

Nach meiner Erfahrung ist die englische Bezeichnung wirklich in allen Versionen, auch in gemischten Sprachversionen für Excel verständlich. Auch wichtig: Das Anfangsdatum muss vor dem Enddatum liegen, sonst wird die Fehlermeldung #ZAHL ausgegeben

Wie groß ist der Altersunterschied zwischen Partnern genau?

 

 

Wie alt bin ich zu einem bestimmten Stichtag, zum Beispiel am 27.04.2024?

Excel Wochenende farbig darstellen-bedingte Formatierung

Excel Wochenende farbig

Cornelia Volk zeigt Ihnen im Video, wie sie in Ergänzung der vorgefertigten bedingten Formatierungen eigene Formeln kreieren können, um größere Tabellen sinnvoll zu formatieren. An einem Beispiel führen wir Sie Schritt für Schritt durch den Prozess einer neuen Regel. Im Klartext: Wir zeigen Ihnen, wie Sie in einer Excel Tabelle alle Samstage und alle Sonntage, das Wochenende also, farbig markieren.

Die vorbereitete Excel Tabelle weist mehrere Mitarbeiter auf und eine Reihe von Tagen, das mag ein Urlaubskalender, ein Schichtkalender oder etwas Ähnliches sein. In jedem Fall wurde zur besseren Kontrolle das Datumsformat um die verkürzte Anzeige des Tagesnamens erweitert. Für dieses Excel Blatt sollen die Zeilen, deren Tag ein Samstag oder ein Sonntag ist, grün gefärbt werden. Dazu müssen mehrere Funktionen ineinander verschachtelt werden.

Schauen Sie sich das Vorgehen im Video an, oder arbeiten Sie die Beschreibung ab. Für den separaten Druck, gibt es ein PDF Dokument.

VIDEO

bedingte formatierung

Im Register Start finden Sie den Aufruf für die Bedingte Formatierung

Wo finden Sie die Befehle, um in Excel Wochenende farbig darzustellen?

Die Befehle für die bedingte Formatierung erreichen Sie zentral über die Schaltfläche Bedingte Formatierung, die sich auf der Registerkarte Start befindet. Klicken Sie bitte nacheinander in der Menüleiste:  Bedingte Formatierung – Neue Regel

fenster neue formatierungsregel

Regeltyp wählen

Regeltyp wählen

Im neuen Fenster „Neue Formatierungsregel“ wählen Sie den Regeltyp:
Formel zur Ermittlung der zu formatierenden Zellen verwenden

formel bedingte formatierung wochentage

Manuelles Erfassen der Formel

Manuelles Erfassen der Formel in Excel

Excel muss prüfen, ob der Wochentag in Spalte A entweder ein Samstag, oder ein Sonntag ist. Dazu setzen Sie zunächst die logische Funktion „=oder“ ein. Da hier der Assistent nicht zur Verfügung steht, erfassen Sie manuell die Formel, oder zuerst im Tabellenblatt eintragen, testen und dann kopieren. Wir beginnen im dafür vorgesehen Feld unter dem Punkt Regelbeschreibung.

Bedingung eins: die Funktion Wochentag bezogen auf das entsprechende Datum. Fest ist hier nur die Spalte A. Diese Funktion ergibt eine 7, es handelt sich um einen Sonntag,

Bedingung zwei: das Ergebnis der Funktion Wochentag angewendet auf das Datum in Spalte A ergibt eine 1; es handelt sich um einen Samstag

Die Formel für Excel Wochenende farbig: =oder(wochentag($A3)=7;wochentag($A3)=1)

zellen formatieren in farbe

Wählen Sie die Farbe

Die Farbe wählen

Trifft eine dieser beiden Bedingungen zu, dann soll der entsprechende Bereich ausgefüllt werden mit z.B. grüner Farbe. Klicken Sie im gleichen Fenster auf Formatieren und wählen unter dem Register Ausfüllen ihren Farbwunsch. Bestätigen Sie das Fenster mit einem Klick auf OK.

Die neue Regel verwalten und bearbeiten

Mit Klick auf Bedingte Formatierung und Regeln verwalten bearbeiten Sie die soeben erstellte Regel. Mit Regel bearbeiten gelangen Sie in den Eingabebereich.

Zusammenfassung: Logikfunktion „=oder“ mit zwei Bedingungen: Es wird jeweils das Ergebnis der Funktion Wochentag ausgewertet. Ergibt diese Funktion angewendet auf das fragliche Datum eine 7, handelt es sich um einen Sonntag, ansonsten um einen Samstag.

Ich wünsche Ihnen viel Spaß und Erfolg mit Excel.

Dynamische Tabelle Excel

Dynamische Tabelle in Excel

Wir stellen Ihnen eine dynamische Tabelle in Excel vor. Im Video mit einer Beispieldatei und hier als Textbeitrag haben wir für Sie das Prozedere noch einmal zusammengefasst. Die dynamische Tabelle bildet eine Funktionalität in Excel, die leicht übersehen wird. In unserer Tabelle sind keine leeren Spalte und Zeilen enthalten. Wie immer, wenn Sie mit Datensatzlisten arbeiten; gleichgültig ob Sie sortieren, filtern, berechnen oder eben eine dynamische Tabelle erzeugen wollen

Video

dynamische tabelle erzeugen

Dynamische Tabelle erzeugen

Wie man eine dynamische Tabelle erzeugt

Klicken Sie irgendwo innerhalb Ihrer Tabelle, gleichgültig ob in der ersten, in der letzten Spalte, am Anfang, am Ende der Zeilen und wählen Sie aus dem „Register START“ den Schalter „Als Tabelle formatieren“. Im darauffolgenden Fenster öffnen sich zahlreiche Tabellenformatvorlagen.

Auf den ersten Blick wirkt das Ganze lediglich wie eine Farbauswahl. Wir wählen im Beispiel einen mittleren Typ aus den Tabellenformatvorlagen. Der hell-dunkel-wechsel ist bei langen Listen gut zu lesen.

als tabelle formatieren

Tabelle formatieren

In neuen Fenster erkennt das System den Daten- oder Eingabebereich automatisch, markiert ihn und erkennt auch, dass die Tabelle Überschriften/Feldnamen hat. Mit O. K. dieses Fenster bestätigen. An dieser Stelle haben Sie bereits an dynamische Tabelle erzeugt

Merkmale dynamische Tabelle

register tabellentools

Register Tabellentools

Wenn sie genauer auf den Bildschirm schauen, stellen Sie fest, dass mit Erzeugen dieser dynamischen Tabelle, ein weiteres Befehlsregister, nämlich Tabellentools, aufgetaucht ist.

Weiter bemerken Sie, das offensichtlich automatisch die Filterfunktion eingeschaltet ist. Das könnte man natürlich auch über Daten filtern erreichen. Hier gehört das alles zum Gesamtpaket Dynamische Tabellen. Weiter zum Gesamtpaket gehört, dass rechts unten in der letzten Zelle der Tabelle, eine kleine Eckmarkierung auftaucht. Diese Markierung stellt das aktuelle Ende der Tabelle dar.

Schauen wir, was diese Tabelle so dynamisch macht

und was die Arbeit mit einer dynamischen Tabelle so angenehm erscheinen lässt.

fixierung spaltenueberschriften

Spaltenüberschriften-Fixierung

Sie scrollen in der Tabelle nach unten. Fügen Sie zum Beispiel einen weiteren Mitarbeiter hinzu. Bemerken Sie, dass automatisch die Titel, d.h. die Spaltenüberschriften, fixiert sind? Diese Fixierung ist erst dann aufgehoben, d.h. Überschriften verschwinden erst dann, wenn Sie unterhalb der Tabelle klicken, um einen neuen Eintrag vorzunehmen. Nach der Eingabe Ihres neuen Mitarbeiters sind die Titel wieder fixiert und die Formatierung wird automatisch fortgeführt. Wenn Sie statt einer neuen Zeile eine neue Spalte einfügen, etwa um eine Berechnung zu ergänzen, wird die Formatierung in alle Richtungen fortgeführt.

 

dynamische tabelle neue spalte

Neue Spalte in einer dynamischen tabelle

Sie ergänzen in unserem Beispiel eine Spalte mit dem Namen „Provision“. Tragen Sie in die Zelle eine Formel ein, werden Sie bemerken, dass diese Formel ein wenig anders aussieht als andere Formeln. Mit Hilfe einer einfachen Rechenanweisung berechnen Sie für jeden Mitarbeiter 3 % von seinem Umsatz als Provisionsbetrag. Wie immer beginnen Sie im ersten Ergebnisfeld mit einem Gleichheitszeichen und klicken das erste Rechenfeld an. Normalerweise muss die Formel lauten: = E2 * 3 %.

Klicken Sie E an, dann taucht nicht etwa die Adresse E2 auf, sondern der Platzhalter „@ und der Datenfeldname „Umsatz“. Also @Umsatz. Fügen Sue nun noch hinzu: * 3 %. Die Formel lautet komplett: @Umsatz*3%. Bestätigen Sie diese Berechnung mit ENTER. Die Formel wird automatisch in alle Zeilen Ihrer dynamischen Tabelle kopiert. Das Format ist noch nicht so gelungen. Bitte markieren Sie die Spalte und wählen das Währungsformat aus.

Nehmen Sie nun einen weiteren Eintrag auf. Sie werden feststellen, dass auch die Formel automatisch fortgeführt wird. Man muss die Formel nicht kopieren. Die Filterpfeile, die integriert sind, entsprechen den Standard Filterpfeilen wie sie in Datensatzlisten Verwendung finden können.

Dynamische Tabelle wieder in eine normale Tabelle überführen

register tabellentools

Register Tabellentools

Möchten Sie die Liste lediglich umformatieren, entscheiden Sie sich für eine andere Tabellenformatvorlage. Im Register Tabellentools setzen Sie mit Häkchen die verschiedenen Optionen ihrer Tabelle. Beispielsweise können Sie bestimmen, dass die erste Spalte separat hervorgehoben wollen soll. Unter Tabellentools können Sie Ihre Tabelle gegebenenfalls auch exportieren, also direkt zum Beispiel nach Visio übergeben, oder in eine SharePoint Liste umwandeln. „Duplikate entfernen“ entspricht dem normalen Befehl des Registers Daten. Aus dieser Tabelle lässt sich sofort eine Pivot-Tabelle erzeugen.

 

dynamische tabelle umwandeln

Dynamische Tabelle umwandeln in eine normale tabelle

Umwandlung dynamische Tabelle zurück in die normale Tabelle

Es gibt mal wieder zwei Möglichkeiten, die dynamische Tabelle in eine ganz normale Tabelle umzuwandeln. Wenn Sie die Formatierung (Farbe etc.) beibehalten möchte, dann wählen Sie den Schalter „in Bereich konvertieren“.

Wenn Sie dagegen auch die Farbgebung wieder neutralisieren möchten, nehmen Sie zuerst die Farbe fort, in dem Sie sich im Bereich der Tabellenformatvorlagen für die helle Vorlage entscheiden. Anschließend wie oben beschrieben mit dem Schalter „in Bereich konvertieren“ die Tabelle umwandeln. Mit OK bestätigen Sie den Vorgang.

Lediglich eine Eigenheit erinnert an die dynamische Tabelle. Die Formel in unserer Beispieltabelle – in der Spalte Provision – ist anderes aufgebaut, als wenn von Hand eingetragen worden wäre. Der Blattname ist dem Rechenfeld vorangestellt, die Spalte E ist mit einem Dollarzeichen fixiert. Natürlich ist die Formel völlig funktionstüchtig.

Vorteile von dynamischen Tabellen sind zweifellos die automatische Übertragbarkeit der Formate und mehr noch, das automatische Übernehmen/Ergänzen von Formeln.

Danke und viel Spaß und Erfolg mit Excel.

Mit Excel rechnen ohne eine Formel einzugeben

Mit Excel rechnen ohne eine Formel zu erstellen-
gilt für alle Excel Versionen

Gleichgültig mit welcher Excel-Version Sie arbeiten, unsere Referentin Cornelia Volk zeigt Ihnen im folgenden Excel-Video, wie Sie mit Excel rechnen ohne eine Formel einzugeben.

Beschreibung Excel Video zum Thema „Mit Excel rechnen ohne eine Formel zu erstellen“

Die Tabelle im Video zeigt eine Preisliste aus dem Jahr 2015. Die Preise sollen im Jahr 2016 um 2,5 Prozent angehoben werden. Frau Volk zeigt Ihnen, wie Sie mit Excel rechnen, ohne eine Formel einzugeben. Das können Sie in allen Excel Versionen praktizieren.

1. Dazu tragen Sie bitte den Berechnungsfaktor in eine beliebige leere Zelle ein, (hier 1,025).  Anschließend markieren und kopieren Sie diese Zelle.

2. Markieren Sie den Bereich, der die neuen Preise aufnehmen soll.

3. Wählen Sie nun in der Menüleiste den Punkt Einfügen – Inhalte einfügen. Es öffnet sich ein neues Dialogfenster. Dieses Fenster nutzen Sie für Ihre Berechnungen, in diesem Fall markieren Sie „Multiplizieren“ und klicken anschließen auf OK. Die Preise wurden erhöht.

4. Jetzt formatieren Sie noch, aktualisieren die Tabellenüberschrift und löschen die Hilfszelle.

Vielen Dank und viel Spaß mit Excel.

Excel Blattschutz einrichten

Excel Blattschutz einrichten

Möchten Sie das versehentliche Überschreiben von Formeln, oder das Eintragen aktueller Werte verhindern? PCA Wrana zeigt im Video, wie Sie den Excel Blattschutz einrichten, damit Ihre Excel-Tabellenblätter von Dritten nur gezielt oder gar nicht bearbeitet werden können.

Im Video Tutorial „Excel Blattschutz einrichten“ zeigen wir Ihnen Schritt für Schritt, die Einstellungen für den Blattschutz, auch mit Vergabe eines Kennwortes. Darüber hinaus können Sie die Videoinhalte in diesem Beitrag weiter unten nachlesen. Das Video steht zur  kostenlosen Verfügung. In unserer Beispieltabelle sehen Sie zwölf Monate des aktuellen Jahres. Dazu die entsprechenden Kategorien für die Ausgaben und Beträge. Die Zellen sind mit Werten gefüllt bis einschließlich August. Im unteren Blattteil befindet sich ein Abfragebereich. Der Anwender kann den Monat und die Kategorie wählen. Der entsprechende Betrag wird mit Hilfe einer Formel angezeigt.

Excel Blattschutz einrichten im Einzelnen – was dürfen Anwender (Dritte) in der Tabelle bearbeiten und was nicht.

Es soll möglich sein,

  • dass Anwender (Dritte) Werte eintragen bzw. korrigieren können.
  • Monate und Kategorien sollen im Abfragebereich ausgewählt werden können.

Es soll nicht möglich sein,

  • die Formel für die Berechnung zu löschen oder zu ändern.
  • irgendwo im freien Bereich etwas einzutragen.

Excel Blattschutz einrichten in zwei Schritten

Im ersten Schritt

  • markieren Sie allen Zellen in denen Sie später Änderungen bzw. Eingaben vornehmen möchte. Das sind natürlich die Zellen, die die Werte enthalten sollen, darüber hinaus die Listenfelder im unteren Bereich unseres Beispiels – also die Felder „Monat“ und „Kategorie“ im Abfragebereich.
  • Mit einem Rechtsklick in die Tabelle gelangen Sie zum Dialogfeld „Zellen formatieren“. Im neuen Fenster sehen Sie das letzte Register „Schutz“, das für uns von Bedeutung ist. Klicken Sie dieses Register an.
  • Es öffnet ein neues Fenster. In diesem Fenster entfernen Sie das Häkchen vor dem Feld „Gesperrt“.
    Erläuterung dazu: Standardmäßig sind alle Zellen gesperrt. Das bedeutet, wenn Sie nicht vorher die variablen Felder freigeben, können Sie in keinem einzigen Feld der gesamten Tabelle eine Eingabe vornehmen. Die Sperrung deaktivieren Sie mit der Entfernung des Häkchens wie oben beschrieben und bestätigen mit OK.

Im zweiten Schritt – Thema: Excel Blattschutz einrichten

  • wechseln Sie mit einem Klick auf das Register „Überprüfen“.
  • Hier erfolgt mit einem Klick auf „Blatt schützen“ der Blattschutz.
  • Es öffnet sich ein neues Dialogfenster „Blatt schützen:
  • Sie können ein Kennwort eintragen.
  • Differenzieren Sie weiter, was dem Anwender erlaubt werden soll. Hierzu ein paar Erläuterung: Gewöhnlich darf der Anwender „Gesperrte Zellen auswählen“, sich beispielsweise informieren mit welcher Formel gearbeitet wird. Er darf natürlich auch „Nicht gesperrte Zellen auswählen“, sonst wären dem Anwender überhaupt keine Eingaben möglich. Manchmal ist es hilfreich das Formatieren der Spalten oder Zeilen zu gestatten. Wenn man während des Jahres feststellt, dass Beträge so hoch werden, dass die Spaltenbreite zur Anzeige angepasst werden muss. Weiterhin überlegen Sie, ob der Anwender Sortiervorgänge ausführen darf. Ein paar Augenblicke überlegen und hier weitere Optionen sinnvoll wählen könnte von Ihrem Interesse sein.
  • Letzter Schritt im Dialogfenster „Blatt schützen“: Mit OK bestätigen Sie Ihre Eingaben und werden wie immer bei Kennworteingaben darum gebeten, ihr Kennwort zu wiederholen bzw. zu bestätigen.

Excel Blattschutz einrichten am Ende auf Funktion prüfen

Wechseln Sie nun in der Menüleiste mit einem Klick auf Start in das gleichnamige Register. Sie sehen auf den ersten Blick, dass der Blattschutz aktiv ist. Sie Können Schriftart oder Schriftgröße und Zahlenformate nicht verändern. Sie können auf dem Blatt nichts einfügen, außer Kopf- und Fußzeile, was sich streng genommen auch nicht im Tabellenbereich befindet.

Der Anwender kann neue Werte aufnehmen, oder falsch erfasste Werte verändern/korrigieren. Weiterhin ist es möglich im Abfragefenster Monat und Kategorie auszuwählen. Mit einem Klick auf das Formelfeld kann der Anwender einen Blick auf die Formel werfen. Es ist nicht möglich mit Entfernen die Formel zu löschen, oder an irgendeiner Stelle im Tabellenblatt etwas einzutragen. So haben wir uns das vorgestellt. Zum Thema Excel Blattschutz einrichten sind wir mit dieser Lektion am Ende angekommen und wünschen Ihnen

viel Spaß und Erfolg mit Excel.