Excel Formel Einkommensteuer 2018

Excel Formel Einkommensteuer 2018

In  meinem Beitrag zum Thema Excel Formel Einkommensteuer 2018 zeige ich Ihnen, wie Sie das Ergebnis Ihrer Steuererklärung prüfen können. Selbstverständlich habe ich auch gleich die Formeln der Jahre 2014 bis 2019 eingefügt. Den Download der Excel-Datei finden Sie weiter unten.

Für manche Anwender ist Excel oft nur ein Programm, das die vier Grundrechenarten beherrscht und mit dem Listen schnell und einfach sortiert werden können. Generell ist noch die Summenfunktion bekannt und vielleicht die Möglichkeit, Daten zu filtern. Das mit Excel auch ausgefallene Rechenoperationen nachvollzogen werden können, ist für den Fachmann selbstverständlich. Aber was kann der Durchschnittsanwender damit anfangen?

In den Anfängen meiner Excel-Welt

…damals noch die Programme Lotus 1-2-3 bzw. Multiplan, hatte ich die Idee, die Einkommens-Steuerformel nach §32a, Einkommensteuergesetz per Tabellenkalkulation nachzubauen. Lange Zeit hatte ich die Formeln nicht mehr aktualisiert. Jetzt endlich habe ich die Jahre 2014 bis 2019 wieder auf die neuen Vorgaben umgestellt.

Grundfreibetrag und Spitzensteuersatz

Zwei Begriffe, die vielen Steuerzahlern bekannt sind, ist der Grundfreibetrag und der Spitzensteuersatz. Der Grundfreibetrag beträgt für das Jahr 2018 exakt 9.000 €. Bis zu diesem Betrag sind Einkommen steuerfrei. Der Betrag wird jedes Jahr per Gesetzesänderung an die Inflationsrate angepasst, für das Jahr 2019 ist der Grundfreibetrag auf 9.168 € angehoben worden. Der Spitzensteuersatz beträgt 45% und ist ab einem Einkommen von 265.327 € (für 2019) zu zahlen; auch dieser Wert wird jedes Jahr angepasst, also erhöht.

Einkommen – was ist das?

Der Begriff „Einkommen“ ist hier mit dem Begriff „zu versteuerndes Einkommen“ (Amtsdeutsch) gleichzusetzen. Vereinfacht ausgedrückt bedeutet das: Alle Ihre Einnahmen abzüglich der vom Finanzamt anerkannten Ausgaben bildet das zu versteuernde Einkommen.

Steuerlast für Single und Ehepaare

In der beigefügten Excel-Tabelle lässt sich die vom Finanzamt mitgeteilte Steuerschuld einfach kontrollieren. In das grüne Feld tragen Sie Ihr zu versteuerndes Einkommen gemäß Ihrem Steuerbescheid ein.

Das Ergebnis gibt die Steuerlast für Single und für Ehepaare aus.

Ich wünsche Ihnen viel Spaß und Erfolg mit den Steuerformeln. Der Download ist gratis.

Das könnte Sie auch interessieren

Excel bedingte Formatierung – Regeln in Reihenfolge

Die Bedingte Formatierung von Excel bietet Ihnen die Möglichkeit nicht den Inhalt einer Zelle, sondern deren Aussehen, deren Format, an Bedingungen zu knüpfen. Optische Effekte in Ihrer Tabelle sind schnell eingerichtet, halten aber auch eine Überraschung vor, wenn Sie deren Reihenfolge nicht beachten.

 

Die Aufgabe einer Seminarteilnehmerin

Die hier vorliegende Fragestellung resultiert aus einem Anliegen einer Kursteilnehmerin. Die Umsatzspalte soll formatiert werden

 

Gewünschte Formatkombination:

 

Die Umsatzspalte soll formatiert werden, zunächst in dem alle Texteingaben blau gefärbt werden.

 

Dann die Umsatzwerte zwischen 90.000 und 100.000 in grün als Beispiel für Umsätze, die im Allgemeinen von den Mitarbeitern zu erwarten sind.

 

Dritte Bedingung ist eine weitere Wertebedingung: Alles über 100.000 Umsatz soll gelb abgebildet werden.

 

Was wird gezeigt:

 

Anlegen einer neuen Formatierungsregel

Anwenden vorgegebener Möglichkeiten aus Bedingte Formatierung

Kombination mehrerer Regeln in der logischen Reihenfolge

 

Erste Excel Bedingung: Texte in Blau

Wie bekomme ich heraus, ob die Zelle einen Text, oder eine Zahl enthält?

Warum kann ich in der Beispieltabelle nicht einfach nach „k.A“ suchen lassen?

Letztere Frage: In der Tabelle der Fragestellerin, wurde nicht immer k.A eintragen, sondern manchmal auch „keine Angaben“ oder „keine Info“. Die Texte stimmten nicht überein.

 

Für eine solche Fragestellung bietet Excel die Funktion ISTTEXT

isttext

 

 

Die Auswertung ergibt „falsch“ bei den Umsatzwerten. Denn diese Zellen enthalten wirkliche Umsatzzahlen. Die Annahme, dass es sich bei „k.A“ um einen Text handelt, bestätigt sich mit „wahr“.

Also beginnen wir mit der Abfrage: Handelt es sich um Texte?

Im Menü START finden Sie den Schalter bedingte Formatierung. Keine der gebotenen Möglichkeiten ist passend. Wir müssen eine neue Regel erstellen, und zwar auf Basis einer Formel.

(an der Stelle muss natürlich alles getippt werden. Wenn ich die erste Zielzelle anklicke, ergänzt innerhalb der bedingten Formatierung Excel immer die Dollarzeichen für absolute Adressen, die ich zunächst entferne.)

Formatieren – das gewünschte zarte blau für den Hintergrund.

Alle Zellen der Spalte D sind blau gefärbt, ob die Annahme Text wahr oder falsch ist.

Zweite Bedingung: Umsatzwerte zwischen 90.000 und 100.000 in Grün.

Sie können sich an den vorgegebenen Möglichkeiten bedienen

Menü START – Bedingte Formatierung – Regeln zum Hervorheben von Zellen Zwischen – wenn der Betrag zwischen 90.000 und 100.000 ist, soll das Feld grün gefärbt werden. 

 

Dritte Bedingung: Alles über 90.000 in Gelb

Alles über 90.000 Umsatz soll Gelb dargestellt werden. Wie zuvor bei der zweiten Regel, gehe ich über den
Schalter „Bedingte Formatierung“ – größer als

Was ist nun passiert? Alles GELB?

Wir haben schon die richtigen Regeln, aber in der falschen Reihenfolge.

alles gelb

alles gelb

 

Im Manager für Regeln zur bedingten Formatierung ändern Sie diese Reihenfolge.

Der Bereich markieren.

Menü START-Bedingte Formatierung-Regeln verwalten

Sie befinden sich nun im Manager für Regeln.

Das System zeichnet die Regeln in der Reihenfolge auf, in der ich sie eintrage, d. h. die zuletzt gesetzte Regel steht ganz oben und wird damit als erste Regel ausgeführt. GELB!

Verschieben die Regeln

Als erstes soll das System prüfen, ob die Zelle einen Text enthält. Wenn ja – blau färben. Diese Regel wird nach oben geführt.

Wenn erste Regel nicht zutrifft, folgt die zweite Regel: Hier soll geprüft werden, ob der Inhalt zwischen 90.000 und 100.000 liegt und damit grün gefärbt wird. Positionieren Sie die 2. Regel unter die 1. Regel.

Jetzt folgt die 3. Regel, die prüfen wird, ob es sich um einen Wert größer als 100.000 handelt, um die Zellen gelb zu färben. Diese Regel steht nun an letzter Position.

Alle Zellen sind richtig eingefärbt.

farbige zellen in excel

 

Fazit

Bei der Kombination mehrerer Regeln müssen sie auch immer die Logik bedenken und gegebenenfalls die Reihenfolge ändern.

Viel Spaß und Erfolg mit Excel

Ihre Cornelia Volk

Das könnte Sie auch interessieren

Excel 365 Pivot Tabellen Grundlagen

Excel 365 Pivot Tabellen Grundlagen

Excel 365 Pivot Tabellen Grundlagen   behandelt im ersten Teil meiner geplanten Mini-Video-Serie die Voraussetzungen für Pivot-Tabellen. Anschließend erstelle ich...

mehr lesen

Formate übertragen in Excel, Word und PowerPoint

Formate übertragen mit Cornelia Volk

Cornelia zeigt, wie Sie in Excel, PowerPoint und Word Formatierungen übernehmen können. 
Wie immer haben wir Text und Video für Sie bereitgestellt.

Video

Formate übertragen im Excel Programm

Wenn Sie in Excel Formate übertragen, werden die Textformate komplett übernommen, also Schriftart, Größe und die Attribute wie fett, kursiv, unterstrichen, durchgestrichen u.s.w.

Weiterhin die Zahlenformate wie die Währungskennung, Anzahl von Dezimalstellen, Datumsformate, benutzerdefinierte Zahlenformate und natürlich Formateinstellungen, die für die ganze Zelle vorgenommen werden: Rahmen, Teillinien, Füllfarbe und Ausrichtung.

Stellen Sie ein Format Ihrer Wahl zusammen: Füllfarbe, das Schriftattribut fett, Schriftgrößen, weiterhin ein tausender Format, aber ohne Nachkommastellen, und es sollte auch ein Rahmen nicht fehlen.

Wenn Ihnen dieses Format nun gefällt und Sie es für viele Felder übernehmen möchten, markieren Sie es zunächst. Beginnen Sie an der Quelle. Im Register START den FORMATPINSEL  einmal anklicken. Das Format wird genau einmal übertragen.

Wenn Sie jetzt gleich mehrere Zellen formatieren möchten, können Sie das einfach tun, indem Sie die Maustaste nicht loslassen. Aber was ist mit Zellen, die vereinzelt eine besondere Formatierung erhalten sollen und nicht hintereinander liegen.

 

Zellen formatieren, die möglicherweise weit auseinanderliegen.

Klicken Sie den FormatPinsel doppelt an. Das bedeutet, er bleibt aktiv und Sie können das Format beliebig oft übertragen auf einzelne Zellen, oder zusammenhängende Bereiche und natürlich sogar in andere Tabellen.

 

Es gibt noch eine Besonderheit bei Excel

Ein ausgewähltes Format hätten Sie gerne für die ganze Liste übernommen? Das kann man sicherlich einfach überstreichen mit dem FormatPinsel. Aber nehmen wir mal an, Sie hätten eine sehr, sehr lange Liste. Dann wollen Sie vermutlich nicht mit dem Formatpinsel überstreichen.

 

Dazu ein TIPP.

Ich beginne wieder an der Quelle und markiere mein Wunschformat. Aktivieren Sie den Formatpinsel. Jetzt markieren Sie nicht die Felder, sondern halten zwei Tasten fest und tippen eine dritte Taste an. Die STRG + Shift (Umschalttaste, die sie für Großbuchstaben brauchen), halten Sie fest und tippen den Absatzpfeil an. Die ganze Liste stellt sich im zuvor gewählten Format dar.

Formate übertragen in PowerPoint

Welche Möglichkeiten gibt es in PowerPoint?

 

Auch in PowerPoint übertragen Sie immer sämtliche Formate des Objektes in einem Schritt. Bei PowerPoint und Word übertragen sie die Textformate genau wie bei Excel, Schriftart, Größe, Attribute. Bei Objekten etwa die Füllfarben, die Linienfarben, Strichtyp, Formeffekte und immer noch für viele neu bei 2016, oder PowerPoint 365: Sie können auch Animationseffekte übertragen.

 

Das Zeichenformat lässt sich ganz einfach kopieren. Erst wieder den Mustertext markieren (die Quelle), den Pinsel aktivieren und wenn nur eine Übertragung stattfinden soll, dann einfach ins Ziel klicken. Auch hier hätten wir mit einem Doppelklick arbeiten können und der Pinsel bleibt aktiv. Hinterher entweder mit einem Klick, oder mit Escape

diese Funktion ausschalten.

 

Wenn Sie einen ganzen Absatz markieren, übernimmt PowerPoint auch die Absatzformate. Leider ein bisschen ungewünscht auch die Schrift. Das muss man wissen: Die Funktion hat sich nach dem ersten Texteintrag gerichtet. Ich übernehme und damit eben nicht nur die Absatzformate, wie etwa die Nummerierung oder Aufzählung, sondern bei PowerPoint auch sofort die Schrift.

 

Wenn Sie nun die unterschiedlichen Objekte ansehen, formatieren Sie vielleicht eines mit einem Formeffekt, das Ihnen nett erscheint, mit einer anderen Schriftfarbe vielleicht, auch mit einer Linie, die Sie jetzt mal schwarz setzen. Wenn Sie das Quellobjekt markiert haben, klicken Sie den FormatPinsel ruhig doppelt, und übertragen das Format auf andere Objekte.

 

Bei PowerPoint übertragen Sie jetzt auch Animationseffekte. Im Video hat das Objekt einen Animationspfad. Um diesen zu übertragen, markieren Sie das Objekt und sorgen dafür, dass Sie wirklich auf dem Register Animationen stehen. Denn der normale, herkömmliche Format Pinsel überträgt keine Animation. Diesen Schalter finden Sie im Register Animationen „Animation übertragen“.

 

Doppelklick erlaubt eine Mehrfachübertragung. Hier gibt es sogar eine Tastenkombination ALT + SHIFT + C.

 

Das ist eine Bedienmöglichkeit, die PowerPoint sogar über Excel hinaushebt. Sie können bei PowerPoint und Word, Formate auch über TASTEN-KOMBIS übertragen.

 

Markieren Sie die Quelle und leiten die Kopie ein mit der Tastenkombination STRG – SHIFT – C.  STRG – Shift -V überträgt dann.

 

Das kennen Sie bestimmt: STRG + C zum Kopieren von Objekten aller Art, genauso wie STRG + V zum Einfügen. Halten Sie dabei die Shift-Taste (Umschalttaste) fest, übertragen Sie das Format.

Formate übertragen in WORD

Es wird Sie nicht überraschen. Auch hier können Sie Schriftart, Größe, Attribute übernehmen. Darüber hinaus ganz interessant: Absatzformate gibt es mehrere. Die Ausrichtung, links-, rechts zentrierter Blocksatz, Dinge wie Nummerierung, Aufzählung und die zugehörigen Zeichen, die verschiedenen Einzüge, die Zeilenabstände und natürlich auch hier etwa Rahmen.

 

Markieren des Quelltextes. Pinsel anklicken und mit einem Klick an ein anderes Ziel übernehmen. Pinsel doppelt anklicken für viele einzelne Übertragungen.

 

Abschalten mit Escape, oder mit einem Klick auf den Pinsel.

 

Wie bei PowerPoint können Sie auch bei Word das Format mithilfe einer Tastenkombination übertragen.

 

Immer zuerst die Quelle markieren

Mit STRG – SHIFT + C kopieren Sie

Mit STRG – SHIFT + V das markierte Format übertragen.

 

WORD Absatzformate übernehmen.

Markieren Sie den ganzen Absatz. Übertragen Sie das das Format und oh – verlieren dabei die Schrift, die Sie vorher ausgezeichnet hatten.  

 

Es gibt bei WORD einen Extrakniff.

 

Es gibt bei Word die Möglichkeit, gezielt nur die Absatzformate zu übernehmen. Dazu schalten Sie die Sonderzeichen ein, also Absatzformate, Leerzeichen und Tabsprünge.

 

Wenn Sie nur die Absatzmarke markieren und den Pinsel einzeln, oder doppelt, anklicken, können Sie ganz ausgewählt nur das Absatzformat übertragen.

 

Wenn Sie also in einem älteren Dokument ein Absatzformat finden, an dem ein Kollege vielleicht lange getüftelt hat, bis alles stimmte, dann muss ich nicht nachsehen, mit welchem Einzug, mit welchem Zeilenabstand das Ganze eingerichtet ist, sondern übertrage gezielt nur die Absatzformate.

 

Vielen Dank fürs Zuschauen und viel Spaß mit dem Ausprobieren.

 

Cornelia Volk

Das könnte Sie auch interessieren

PowerPoint Pack and Go

PowerPoint Pack and Go

PowerPoint pack and go PowerPoint Präsentation für eine Weitergabe packen in Office 2010/2013. Für PowerPoint in Office 2016 sind die Schritte im Video fast identisch....

mehr lesen

Excel kompakt Grundlagen-Teil 3-SVERWEIS & WVERWEIS

Excel Grundlagen kompakt erklärt – 3teiliger Kurs

Teil 3 – SVERWEIS und WVERWEIS

Excel Grundlagen Kompakt erklärt richtet sich nicht an absolute Neulinge. Sie sollten schon mit Excel gearbeitet haben. Es ist aber auch kein tiefer gehendes Fachwissen nötig und ein Excel Power-User müssen Sie auch nicht sein. Ich habe in den drei Teilen  zusammengefasst, welche Aufgaben immer wieder in den Inhouseschulungen anstehen.

Außerdem möchte ich meinen Teilnehmern die Excel Grundlagen quasi zum Nachlesen/Nachhören zur Verfügung stellen.

Excel Grundlagen – SVERWEIS & WVERWEIS

Im dritten Teil dieser Videoserie gehe ich in knapp 9 Minuten auf die Funktionen SVERWEIS und auch WVERWEIS ein. Zwei Funktionen um eine Tabelle nach Werten zu durchsuchen und Werte ausgeben zu lassen.

Auch der dritte Teil ist kostenlos. Ich erinnere noch einmal daran, dass dieser dreiteilige Videokurs „Excel Grundlagen kompakt erklärt“ bald auch einen Platz finden wird in meiner Office-Uni. Dort sind diese 60 Minuten in kleinere Kapitel aufgeteilt und stellt rein optisch eher den Kurs-Charakter dar.

Regelmäßige Informationen über neue Kurse und andere Veröffentlichungen verpassen Sie nicht, wenn Sie meinen Newsletter bestellen.

Vorschau auf die Digitale-Office-Uni.de

Video SVERWEIS und WVERWEIS

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 für die SVERWEIS Funktionen nur in *.xlsx an

 

Excel-Grundlagen die anderen beiden Teile

Wenn es gefallen hat

freue ich mich sehr auf ein  YOUTUBE Abo und/oder einen Daumen hoch. Vielen Dank. 

Excel Grundlagen kompakt erklärt-Teil 2-Wenn Funktionen

Excel Grundlagen kompakt erklärt – 3teiliger Kurs 

Teil 2 – Wenn Funktionen

 

Excel Grundlagen Kompakt erklärt richtet sich nicht an absolute Neulinge. Sie sollten schon mit Excel gearbeitet haben. Es ist aber auch kein tiefer gehendes Fachwissen nötig und ein Excel Power-User müssen Sie auch nicht sein. Ich habe in den drei Teilen zusammengefasst, welche Aufgaben immer wieder in den Inhouse-Schulungen anstehen.

Außerdem möchte ich meinen Teilnehmern die Excel Grundlagen quasi zum Nachlesen/Nachhören zur Verfügung stellen.

Alle drei Kursteile stelle ich Gratis zur Verfügung. Außer Zeit (ca. 35 Minuten), haben Sie nichts zu verlieren. 

Inhalte Teil 2 – Wenn Funktionen

Zählenwenn

Summenwenn

Wenn

Wenn1

Video Excel – Teil 2 – Wenn Funktionen

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.

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

 

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.

Excel-Grundlagen die beiden anderen Teile

Excel Grundlagen kompakt erklärt-Teil 1-Dateneingabe & Ausfüllfunktionen

Excel Grundlagen 3-teiliger Kurs

Teil 1 – Dateneingabe und Ausfüll-Funktionen

 

Excel Grundlagen Kompakt erklärt richtet sich nicht an absolute Neulinge. Sie sollten schon mit Excel gearbeitet haben. Es ist aber auch kein tiefer gehendes Fachwissen nötig und ein Excel Power-User müssen Sie auch nicht sein. Ich habe in den drei Teilen  zusammengefasst, welche Aufgaben immer wieder in den Inhouseschulungen anstehen.

Außerdem möchte ich meinen Teilnehmern die Excel Grundlagen quasi zum Nachlesen/Nachhören zur Verfügung stellen.

Alle drei Kursteile stelle ich Gratis zur Verfügung. Außer Zeit (ca. 35 Minten), haben Sie nichts zu verlieren.

Video Excel Grundlagen Kompakt ansehen

Inhalte im 1. Teil Excel Grundlagen

 

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 in Excel Grundlagen Kompakt:

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.

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.

Innerhalb der Tabellen-Blätter liefere ich ausführliche Erläuterungen.

„Wiederholung“

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

Excel-Grundlagen die beiden anderen Teile

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

Das könnte Sie auch interessieren

6 neue Diagramme in Excel 2016

6 neue Diagramme in Excel 2016

6 neue Diagramm-Typen in Excel 2016   Bei den Diagrammen in Excel 2016 ergaben sich interessante  Änderungen im Vergleich zur Version 2010 Das sind die...

mehr lesen
Excel Formel Einkommensteuer 2018

Excel Formel Einkommensteuer 2018

Excel Formel Einkommensteuer 2018 In  meinem Beitrag zum Thema Excel Formel Einkommensteuer 2018 zeige ich Ihnen, wie Sie das Ergebnis Ihrer Steuererklärung prüfen...

mehr lesen

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

Das könnte Sie auch interessieren

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

Das könnte Sie auch interessieren

Differenz zwischen zwei Datumsangaben berechnen

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

 

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.

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

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?

Das könnte Sie auch interessieren

WordPress Cookie Plugin von Real Cookie Banner