Excel Blattschutz aufheben trotz vergessenem Kennwort

Lesedauer 4 Minuten

Excel Blattschutz aufheben trotz vergessenem Kennwort

Sofern in Excel für ein Tabellenblatt ein Kennwortschutz eingerichtet wurde, ist der Zugriff auf das Blatt bei vergessenem Kennwort nicht mehr möglich. Excel hat keine Funktionen, um das Kennwort auszulesen oder zurückzusetzen.

Man kann den Excel Blattschutz aufheben mit Hilfe von zwei anderen kostenfreien Programmen.

  1. In diesem Video wird das Freeware Programm 7-zip genutzt. 
  2. Zusätzlich wird ein Editor benötigt. Der Editor aus dem Windows Zubehör-Ordner ist für unsere Zwecke völlig ausreichend.

Video

 

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

Zwei Schritte zum Aufheben des Kennwortschutzes

Erster Schritt zum Excel Blattschutz aufheben

 

Die zugrundeliegende Datei lautet in unserem Beispiel:
Einkaufsliste.xlsx und hat zwei Blätter, englisch Sheet1 und Sheet2 und ist auf dem Desktop gespeichert. Blatt2 = Sheet2 hat einen Kennwortschutz

  1. Der Excel-Dateiname wird umbenannt in *.ZIP
  2. Dazu ein Rechtsklick auf den Dateinamen, dann UMBENNEN und statt Einkaufsliste.XLSX wird Einkaufsliste.ZIP eingetragen
  3. Der Hinweis zur Dateinamenerweiterung wird mit JA bestätigt.
  4. Der Dateiname EInkaufsliste.zip wird mit Rechtsklick geklickt und ÖFFNEN gewählt
  5. Es folgt eine Ordnerliste mit verschiedenen Namen
  6. Im Ordner XL liegt der Unterordner WORKSHEETS; hier sind die Tabellenblätter abgelegt
  7. Die Blattnamen lauten lediglich Sheet1.xml; Sheet2.xml, usw.
  8. In unserem Fall ist das Kennwort in Sheet 2 vergeben
  9. Dieses Sheet2.xml wird auf den Desktop kopiert

Soweit Schritt 1

Zweiter Schritt zum Excel Blattschutz aufheben

 

In diesem Schritt wird das Kennwort entfernt.

 

  1. Starte das Programm Editor aus dem Windows-Zubehör
  2. Mit Datei-Öffnen wird Sheet2.xml geöffnet; bei Dateiname Alle  Dateien anzeigen lassen
  3.  Im geöffneten Fenster muss nach dem Begriff „sheetProtection“ gesucht werden. MIt der Tastenkombination STRG + F öffnet sich das Suchfeld.
  4. Im Bereich von <sheetProtection  bis scenarios=“1″/> ist das Kennwort verschlüsselt abgelegt. Dieser Teil muss gelöscht werden
  5. Beispiel: Die Markierung muss Die beiden Spitzklammern  und > einschließen  <sheetProtection algorithmName=“SHA-512″ hashValue=“+UuzOCY9846GevpSSXVryDbwU2uVw3Msr7vOiYZp+c2Fcg6eZcibPxvaDcauhH4mbT18TDC7/aznhvnZrwOWvQ==“ saltValue=“1gSf6dyhDuTpqmITD6DKPg==“ spinCount=“100000″ sheet=“1″ objects=“1″ scenarios=“1″/>
  6. Nach dem Löschen die Datei speichern.
  7. Jetzt die Datei wieder zurückschieben in den geöffneten ZIP Ordner von 7-Zip, also in Einkaufsliste.zip – XL – Worksheets und die alte Datei überschreiben.
  8. Zum Schluss die Einkaufsliste.zip wieder umbenennen in Einkaufsliste.XLSX

Die Excel Datei öffnen und das Blatt zwei (sheet2) hat kein Kennwort mehr.

 

Zusammenfassung Excel Blattschutz aufheben trotz vergessenem Kennwort

Sie heben den Blattschutz auf mit zwei Programmen. Ein Pack- oder Archivierungsprogramm wir 7-Zipp oder WinRAR. Natürlich funktionieren auch Bezahlprogramme. Weiterhin benötigen Sie einen Editor, um das Blatt mit dem vergessenen Kennwortschutz zu bearbeiten. Nehmen Sie sich etwas Ruhe und Zeit. Konzentration ist wichtig. Ich empfehle grundsätzlich die Excel-Datei zu kopieren und zunächst mit dieser Kopie zu üben. Sie werden sehen, gar nicht schwer.

Viele Erfolg und Grüße

Othmar Wrana

Das könnte Sie auch interessieren

Excel Blattschutz aufheben oder einrichten

Lesedauer 7 Minuten

Excel Blattschutz aufheben oder einrichten

Allgemeines zum Excel Blattschutz aufheben

In Excel kann der Blattschutz auf drei verschiedene Arten aktiviert werden.

  1. Zum ersten kann das gesamte Blatt, ohne Ausnahme, geschützt werden. Es sind dann keine Eingaben möglich, es können keine Zellen gelöscht werden und es kann auch nicht formatiert werden.
  2. Die zweite Möglichkeit besteht darin, eine bestimmte Anzahl von Zellen zu markieren und diese Auswahl nicht zu schützen.
  3. Mit der dritten Möglichkeit lassen sich nur die Formeln schützen, alle anderen Zellen bleiben frei für Eingaben oder zum Löschen.

Sofern der Schutz aktiviert ist, erfolgt die Aufhebung immer mit gleicher Aktion über das Register Überprüfen und das Icon Blattschutz aufheben.

Video Excel Blattschutz einrichten oder aufheben

 

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

Mit dem Befehl Blatt schützen folgt diese Dialogbox:

Blattschutz

In dieser Dialogbox können Ausnahmen vom Blattschutz aktiviert werden, z. B. kann das einfügen von  Spalten zulässig sein.

Ein Kennwort wird NICHT eingetragen.

Mit OK wird der Blattschutz aktiviert. Alle Zellen sind danach geschützt gegen Eingaben, gegen Löschen und die Zellen können nicht formatiert werden.

Der Blattschutz verhindert nicht, Zellen zu markieren, dann zu kopieren und auf einem neuen Blatt einzufügen.

Blattschutz aufheben

Der Blattschutz wird aufgehoben über das Register:

ÜBERPRÜFEN – BLATTSCHUTZ AUFHEBEN

Mit Klick auf BLATTSCHUTZ AUFHEBEN wird der Schutz sofort aufgehoben, es erfolgt keine Kennwort Abfrage, da kein Kennwort vergeben wurde.

Ausgewählte Zellen NICHT schützen

In diesem Schritt sollen nicht alle Zellen geschützt werden. Es sollen nur bestimmte Zellen NICHT geschützt werden. Dazu sind zwei Schritte erforderlich.

Im ersten Schritt müssen die Zellen markiert werden, die NICHT geschützt sein sollen. Generell sind alle Zellen gesperrt, wenn ein Blatt geschützt wird. Diese Sperre muss deaktiviert werden, bevor der Schutz eingeschaltet wird. Diese Deaktivierung von markierten Zellen erfolgt über das Zahlenformat

Die Aufhebung der Sperre wird aktiviert über Formatierung von Zellen.

Entsperren von Zellen

In dieser Dialogbox muss die Funktion GESPERRT deaktiviert werden. Damit sind die markierten Zellen NICHT geschützt, wenn im folgenden Schritt das Blatt geschützt wird.

Der Schutz erfolgt wieder mit dem Befehl:

Überprüfen – Blatt schützen

Nur Formel Felder schützen

Die dritte Schutz Möglichkeit besteht darin, nur die Formeln zu schützen; die restlichen Zellen bleiben ungeschützt. Zusätzlich können die Formeln aus der Eingabezeile ausgeblendet werden.

Zunächst müssen alle Zellen auf entsperrt gestellt werden. Dazu wird das gesamte Blatt markiert.

Über die Formatierung werden alle Zellen entsperrt.

Formelzellen markieren

Jetzt besteht die Notwendigkeit, nur die Formel-Zellen zu markieren. Im Blatt müssen keine Zellen markiert werden.

Die Markierung aller Formeln erfolgt über die Funktionstaste F5 – Gehe zu – Inhalte…

Hier wird die Option Formeln gewählt und alle Formelzellen im Blatt werden automatisch markiert.

Diese markierten Zellen müssen jetzt gesperrt werden, wenn das Blatt geschützt wird.

Dazu muss erneut das Zahlenformat gestartet werden und im Register Schutz werden die beiden Optionen GESPERRT  und  AUSGEBLENDET aktiviert.

Jetzt muss Im letzten Schritt wieder der Blattschutz eingeschaltet werden. Dann sind nur die Formelzellen geschützt, alle anderen Zellen können bearbeitet werden.

Kennwort für den Blattschutz vergessen

Die Antwort darauf ist ziemlich einfach: Das Blatt ist dann nicht mehr verfügbar, die Daten dieses Blattes sind verloren.
Excel bietet keine Funktionen um das Kennwort zurück zusetzen oder wiederherzustellen. 

In einem weiteren Video zeige ich, wie man mit zwei zusätzlichen, anderen Programmen das Kennwort entfernen kann. Dazu dient u.a. das Programm 7-Zip sowie ein beliebiger Editor. Dieses Vorgehen zeige ich im nächsten Video.

Freundliche Grüße – Othmar Wrana

Das könnte Sie auch interessieren

Mit Excel rechnen ohne eine Formel einzugeben

Lesedauer 3 Minuten

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.

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

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.

Das könnte Sie auch interessieren

Excel Wochenende farbig darstellen-bedingte Formatierung

Lesedauer 7 Minuten

Excel Wochenende farbig darstellen

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. 

VIDEO

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

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.

Das könnte Sie auch interessieren

Excel sortieren Funktion in Excel 365

Lesedauer 13 Minuten

Excel sortieren Funktion in Excel 365

In diesem Beitrag wird die neue Funktion SORTIEREN in der Excel 365 Version vorgestellt. Entscheidend dabei ist die Möglichkeit, die Daten nicht im aktuellen Bereich zu sortieren, sondern die Sortierung in einem neuen Bereich vorzunehmen. Der neue Bereich ist verknüpft mit den Original Daten, so dass bei einer Änderung in den Original-Daten auch der neue sortierte Bereich sofort aktualisiert wird. (Dynamische Verbindung)

Hinweis: Diese Funktion ist zurzeit nur für Microsoft 365-Abonnenten verfügbar, sofern der monatliche Kanal abonniert ist.

Zum Video 

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

Die Syntax der SORTIEREN-Funktion:

Die Funktion besitzt folgende Argumente

  1. Matrix: Der Zellbereich, der sortiert zurückgegeben werden soll
  2. Sortierindex Die Spalte nach der sortiert werden soll; Standardwert 1

bedeutet, es wird nach der ersten Spalte der Matrix sortiert.

  1. Sortierreihenfolge 1= aufsteigende Sortierung; -1 = absteigende Sortierung
  2. Nach Spalte WAHR = nach Spalten sortieren. FALSCH = nach Zeilen sortieren

Statt WAHR kann auch 1 eingetragen werden, statt Falsch gilt der Wert 0

Die Argumente 2-4 sind optional.

Werden diese Argumente nicht benutzt, wird die neue Tabelle immer aufsteigend nach der ersten Spalte sortiert.

Eine Spalte aus einer Matrix in einen neuen Bereich sortieren

Ziel ist es, die Spalte „NAME“ in ein neues Tabellenblatt sortiert zu übernehmen. (s. rechte Tabelle)

Diese Tabelle wurde als dynamische Tabelle formatiert mit dem Befehl: „Als Tabelle formatieren“. Zunächst soll nur die Name-Spalte in ein neues Blatt kopiert und sortiert werden.

Sortierfunktion

Beginn auf einem neuen Blatt mit der  Funktion „SORTIEREN“ in A2 .

Die Spaltenüberschrift wird manuell eingetragen.

Vorgehensweise

Nach Aufruf der Funktion SORTIEREN markiere ich die gesamte Tabelle. Als Matrix wird der Tabellenname mit dem Parameter ALLE gezeigt. Da ich nur die Namen-Spalte sortieren möchte, ändere ich ALLE in NAME ab.

Das nächste Argument „Sortierindex“ wird hier zunächst übersprungen durch Eingabe eines Semikolons.

Es folgt die Sortierfolge, hier „Aufsteigend“. Dafür steht die 1. Für „Absteigend“ sortieren ist der Parameter „-1“ einzutragen.

Das letzte Argument entscheidet, ob Spalten oder Zeilen sortiert werden sollen. Da hier die Zeilen sortiert werden sollen, kommt das Argument „Falsch“ oder „0“ zum Einsatz.

Mit Enter wird die Funktion abgeschlossen und die Namen-Spalte ist aufsteigend sortiert in einem neuen Tabellenblatt.

Was sind die Vorteile?

Wenn im Original-Blatt ein neuer Name eingetragen wird, z. B. „Musterfrau“, dann wird der neue Name sofort in die neue Liste richtig einsortiert. Beispiel: Neue Einträge sind Musterfrau und Aab, am Ende der Ursprungstabelle. Wird im Original ein Name nur korrigiert, erscheint die Korrektur auch in der sortierten Liste.

In der sortierten Tabelle sind die neuen Namen richtig „einsortiert“.

SORTIEREN mit SORTIER-INDEX

Im nächsten Fall soll nicht nur eine Spalte sortiert werden, sondern die gesamte Tabelle. Ich starte einen neuen Bereich und erstelle zunächst nur die Überschriften pro Spalte.

Die Funktion SORTIEREN besitzt bekanntlich folgende Argumente:

Die Funktion SORTIEREN wird gestartet, entweder per Assistent oder manuelle Eingabe.

Im ersten Schritt wird die gesamte Tabelle markiert.

Der Parameter #ALLE übernimmt auch die Spalten Überschriften.

Der Parameter #Daten übernimmt nicht die Spalten-Überschriften, sondern nur die Daten.

Bedeutung der letzten 3 Argumente

4 = Sortierindex; d. h. es wird nach der 4. Spalte der Original-Matrix sortiert

1 = Sortierreihenfolge; 1 bedeutet aufsteigend sortieren;  -1 bedeutet absteigend sortieren

0 = Es wird nach Zeile sortiert; dafür kann auch das Wort „Falsch“ eingetragen werden. Wird der Wert 1 eingetragen, wird nach Spalten sortiert.

Funktions-Assistent

Sofern mit dem Funktions-Assistenten gearbeitet wird, hat die Funktions Eingabe dieses Aussehen.

Zusammenfassung

Der Nutzen dieser SORTIEREN-Funktion liegt u. a. darin, dass die Originaldaten unverändert bestehen bleiben können, also in der ursprünglichen Eingabe-Reihenfolge. In einem neuen Bereich können jetzt diese Daten beliebig sortiert werden. Da die neue Tabelle dynamisch mit den Original-Daten verknüpft ist, werden Korrekturen sofort in die neue, sortierte Tabelle übernommen.

Ich wünsche Ihnen viel Erfolg mit der neuen SORTIEREN Funktion von Excel 365

Ihr Othmar Wrana

Das könnte Sie auch interessieren

Excel Xverweis – die neue Funktion in Excel 365

Lesedauer 8 Minuten

Excel Xverweis – die neue Funktion in Excel 365

Sie sind genau richtig hier, wenn Sie die neue Funktion Excel Xverweis kennenlernen möchten. Seit einiger Zeit schon ist in der Abonnement-Version von Office 365 die neue Funktion integriert. Sie wurde im Vorfeld von Microsoft hochgelobt. Die neue Funktion Xverweis soll komfortabler und vielseitiger sein als der altbekannte SVerweis. Schauen wir uns das einmal gemeinsam an.

Zum Video – Excel Xverweis in Microsoft 365

 

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

PDF anschauen, PDF downloaden

Klicken Sie auf den unteren Button, um den Inhalt von book.designrr.co zu laden.

Inhalt laden

Xverweis Tabelle

Am Beispiel der Liste mit fünf Informationsspalten, möchte ich zum Namen des Mitarbeiters Schneider in der Spalte daneben den aktuellen Umsatz ausgeben. Ich benutze dazu die XVerweis-Funktion.

Xverweis – 3 Argumente

Ich gebe zunächst an, welches Kriterium ich suche. In dem Fall den Nachnamen.

Als zweites Argument den Bereich, der durchsucht werden soll – die Namensspalte, und als drittes Argument den Bereich, aus dem ich ein Ergebnis geliefert haben möchte – die Umsatzspalte. Die anderen angebotenen Argumente sind hier aktuell für uns ohne Bedeutung.

Ich bestätige das mit ENTER und bekomme das korrekte Ergebnis für Herrn Schneider, nämlich 94.143 €.

Von Links nach Rechts und umgekehrt

Ein Vorteil des XVerweises gegenüber dem Sverweis besteht darin, dass er nicht nur von links nach rechts arbeitet, sondern auch umgekehrt von rechts nach links.

Wenn ich meine Liste in eine andere Reihenfolge bringe, also den Umsatz etwa an erster Stelle habe, dann stellen Sie fest, dass das System in der Lage ist, die Argumente anzupassen. Es kann problemlos von rechts nach links durchsucht werden. Und natürlich können wir uns hierzu noch mal ganz kurz die Argumente detailliert ansehen.

  • Was wird gesucht?
  • In welchem Tabellenbereich soll er gesucht werden?
  • Aus welchem Ergebnisbereich möchte ich eine Rückgabe?

Xverweis – 2 Rückgabewerte

 

Sie können anders als bisher üblich, gleich zwei Rückgabewerte auf einmal erreichen. Allerdings müssen die beiden gewünschten Rückgabewerte unmittelbar nebeneinanderliegen.

 

Schauen wir uns das an. Ich gebe die Formel im ersten Feld ein. Natürlich wieder XVerweis, wiederum den Namen den ich suche, den Suchbereich und nun den Ergebnisbereich bzw. in diesem Fall auch gleich zwei Ergebnisbereiche, oder Rückgabematrizen. Ich markiere die beiden nebeneinanderliegenden Bereiche, bestätige mit ENTER, und habe definitiv gleich zwei Werte auf einmal gefunden.

 

Klicke ich die erste Formel an, taucht die zweite Formel in der Markierung mit auf. Markiere ich die zweite Formel, kann man beobachten, dass sie grau ist, denn die Hauptformel steht im Feld H2 in unserer Beispieltabelle.

4 Argumente

 

Ich suche nach einem Mitarbeiter mit Namen Mustermann, der sich aber in dieser Liste nicht befindet. Sicherlich kennen Sie das Problem vom SVerweis. Da taucht in der Regel eine Fehlermeldung NV auf, die man durch andere Funktionen unterdrücken kann. Beim XVerweis funktioniert das alles in einem einzigen Vorgang.

 

Welcher Name?
Was wird gesucht?
Welches Suchkriterium?
Welcher Rückgabebereich?

 

Sie erkennen schon das vierte Argument?
Wenn nicht gefunden, dann sagen Sie einfach „kein Eintrag vorhanden“ oder „Mitarbeiter nicht erfasst“.

Argumente zusammengefasst

 

Suchbereich – Suchmatrix – Ergebnisbereich und die Alternativausgabe – hier noch mal deutlich zum Anschauen. Die Alternativausgabe muss in Anführungszeichen stehen, wenn sie aus einem Text besteht. Textanteile in Formeln werden immer in Anführungszeichen gesetzt. Hätte ich lediglich eine Null haben wollen, hätte ich auf die Anführungszeichen verzichten können.

Vergleichsmodus

 

Nun hatten sie allerdings sicherlich schon bemerkt, dass es noch deutlich mehr Argumente gibt. Und genau genommen fehlen uns noch zwei, die ich auch hier kurz darstellen möchte. Den Vergleichsmodus und den Suchmodus stelle ich in einer neuen Beispieltabelle vor.

 

Bezüge, die Mitarbeiter erhalten und Steuersätze, die darauf angewendet werden. Auch hier stellen sie fest, es wird von rechts nach links gesucht.

 

Ich beginne mit meinem Xverweis. Gebe einen Bezug an, der natürlich nicht bis auf Euro und Cent innerhalb der Liste wieder auftauchen muss. Trage meine Suchmatrix ein. Anschließend den Ergebnisbereich. Wenn nicht gefunden, könnte ich einfach eine Null eintragen.

 

Ich möchte nun die letzten beiden Argumente vorstellen. Verzichte ich auf das Argument NULL, muss ich die leere Position mit einem Semikolon vorgeben.

 

Wir erhalten den Vergleichsmodus.

Eine 0 erfordert die exakte Übereinstimmung, die in der Regel in einer solchen Liste nicht gegeben ist.

 

Eine -1 erfordert die exakte Übereinstimmung oder nächst kleineres Element

Eine 1 erfordert die exakte Übereinstimmung oder nächst größeres Element

Eine 2 Platzhalterzeichenübereinstimmung.

 

Suchmodus

 

Dieses letzte Argument -Platzhalterzeichenübereinstimmung- ist zweifellos in besonders langen Listen praktisch. Ich kann den Suchmodus vorgeben, d. h. die Reihenfolge, in der die Liste durchsucht werden soll.

Vom ersten bis zum letzten Element. Das wird allgemein der Fall sein, unter Umständen auch mal von unten nach oben.

Man kann auch eine Binärsuche durchführen. Die Binärsuche erfordert eine spezielle Sortierung.

Wenn wir das jetzt nach dem Gedächtnis nachtragen, wäre das eine NULL für nicht gefunden, eine EINS für den Vergleichsmodus, denn ich möchte entweder die exakte Übereinstimmung, oder den nächsthöheren Steuersatz und schließlich eine EINS für den Suchmodus und stelle sicher, dass in jedem Fall auch eine sehr lange Liste komplett von oben nach unten durchsucht wird.

Zusammenfassung sämtlicher Argumente, die der XVerweis für sie bereithält.

  • Suchwert
  • Suchbereich
  • Ergebnisbereich
  • Alternativausgabe (lässt man sie weg, ersetzt man sie durch ein leeres Semikolon. Es wird automatisch eine Null gesetzt)
  • Vergleichsmodus (was soll geschehen, wenn der exakte Eintrag nicht zu finden ist?)
  • Suchmodus

Vielen Dank fürs Zuschauen.

Viel Spaß und Erfolg mit der neuen Funktion Xverweis in Excel 365

Cornelia Volk

Das könnte Sie auch interessieren

Corona Fälle in Wiesbaden und anderen Städten berechnen

Lesedauer 4 Minuten

Corona Fälle in Wiesbaden und anderen Städten berechnen

Corona Fälle in Wiesbaden und anderen Städten berechnen Sie mit unterschiedlichen konkreten Zahlen aus Ihrer eigenen Stadt bzw. Gemeide.

In diesem Video will ich an konkreten Zahlen von Wiesbaden zeigen, wie schnell sich der Corana-Virus ausbreiten kann, wenn man die Steigerungsraten über einen bestimmten Zeitraum fortschreibt.

Ausgangspunkt ist der 8. April mit 220 infizierten Personen mit Corana. Einen Tag später wurden 232 Personen als infiziert gemeldet. Daraus folgt ein Steigerungsfaktor von 1,05 an Corona-Infizierten.

Rechnet man mit diesem Faktor die Zahlen hoch – bis zum 30. Mai – ergeben sich 3.482 Fälle in Wiesbaden, bei einer Einwohnerzahl von ca. 295.000 Personen. Das ergibt eine Quote von Infizierten Personen von 1,18 Prozent die an Corona erkrankt sind.

Wie wirken sich 18 zusätzlich an Corona erkrankte Personen aus? 

Unterstellt man jetzt eine Zunahme von 18 Personen mehr  – vom 8. auf 9. April –  also von 232 + 18 Personen = 250 Personen, dann ergibt sich folgendes Bild:

Der Steigerungsfaktor erhöht sich nur leicht von 1,05 auf 1,14  – ABER: am 30 Mai wären ca. 170.000 Personen in Wiesbaden infiziert, also 57 %.

Daher ist für die Entscheider der tägliche Zuwachs an infizierten Personen ein wichtiges Kriterium, um die Ausgangsbeschränkungen zu überdenken.

Bleiben Sie gesund und halten Sie Abstand.
Freundliche Grüße

Othmar Wrana

Zum Video Corona Fallzahlen berechnen mit Excel

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

Das könnte Sie auch interessieren

6 neue Diagramme in Excel 2016

Lesedauer 6 Minuten

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 wichtigsten neue Diagramme:

 

  1. Kombi Diagramme
  2. Wasserfall
  3. Treemap
  4. Sunburst
  5. Trichter
  6. Info Grafik

Zunächst eine generelle Neuerung in Excel 2016

Sobald Daten markiert sind, wird im Befehl „EINFÜGEN“ der Punkt: „Empfohlene Diagramme“ gezeigt. Hier schlägt das Programm verschiedene Diagramm-Typen vor. Nach Auswahl eines Diagramms können im nächsten Schritt weitere Änderungen vorgenommen werden.

Zum Video – 6 neue Diagramme in Excel 2016

 

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

Kombi-Diagramm

Zur Darstellung von Datenreihen mit stark unter­schiedlichen Wertebereichen ist das Kombi-Diagramm besonders gut geeignet. Damit wird das Erstellen einer sekundären Y-Achse deutlich komfortabler gemacht. Hier sollen die Umsätze pro Monat und die Verkäuferzahl in einem Diagramm  dargestellt werden.

 

Umsatz                Verkäufer

Jan            122.360 €           28
Feb            114.202 €           27
Mrz            163.146 €           55
Apr             122.360 €          28

Wasserfall-Diagramm

Mit Excel 2016 ist es einfacher geworden ein Wasserfall Diagramm zu erstellen. Das Wasserfall Diagramm ist ein spezielles Säulendiagramm, mit dem man sehr transparent darstellen kann, wie sich ein Startwert durch eine Serie von positiven und negativen Werten verändert. Hierzu sind 2 Schritte notwendig. Zunächst wird aus den markierten Daten ein Wasser-Fall Diagramm erzeugt.

Zu- und Abgänge im Lager
Monat            Bestand

Jan

4.699 Stück

Feb

3.358 Stück

März

-600 Stück

April

-1.200 Stück

Mai

-300 Stück

Im ersten Schritt wird für jeden Datenpunkt eine Säule erstellt. Die Gesamt-Säule muss nun als Summe festgelegt werden.

Mit einem Rechtsklick auf die Säule GESAMT kann dieser Datenpunkt als Summe festgelegt werden.

Fazit

Diese neuen Typen sind interessante Alternativen für besondere Daten, z. B. beim Vergleich von verschiedenen Dimensionen. Also Verkäufer-Anzahl und deren Umsatzzahlen. Insbesondere Kombi und Wasserfall-Diagramme wurden finden deshalb auch besonderen Zuspruch.

Viel Erfolg mit diesen Diagrammen wünscht
Othmar Wrana

 

Das könnte Sie auch interessieren

Excel Formel Einkommensteuer 2018

Lesedauer 4 Minuten

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

Lesedauer 5 Minuten

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.

 

YouTube

Mit dem Laden des Videos akzeptieren Sie die Datenschutzerklärung von YouTube.
Mehr erfahren

Video laden

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