2013-05-06

Excel 2013 – Tabellenblätter vergleichen – identische Formeln

Ausgehend von einem Master-Tabellenblatt werden jeden Monat Kopien dieses Tabellenblatts erstellt und mit Werten gefüllt. Die Tabellenblätter werden sich im Verlauf von einander unterscheiden. Doch muss auch darauf geachtet werden, dass die Formeln auf allen Blättern identisch sind. Es darf keine Formel verändert werden und nicht durch die Eingabe eines Zahlenwerts überschrieben werden.

Normalerweise würde man solche Tabellenblätter schützen. Zellschutz für alle zu beschreibenden Zellen deaktivieren und dann einen kompletten Blattschutz, der gegen weitere Veränderungen der Blatt- und Formelstruktur schützt. Doch was, wenn die Blätter nicht geschützt werden (können)? Oder man sich die Blätter aus mehreren Arbeitsmappen zusammenkopieren muss?

Folgende Formel kopiere ich in den Datenbereich A1:D9 eines Tabellenblattes. Hier stellvertretend die Formel für Zelle A1:

=WENN(ISTFORMEL(Original!A1);WENN(WENNNV(FORMELTEXT(Original!A1)=FORMELTEXT(INDIREKT($G$1&"!Z"&ZEILE()&"S"&SPALTE();FALSCH));FALSCH);"";"Z"&ZEILE()&"S"&SPALTE());"")

Die Formel prüft, ob in Zelle D1 des Tabellenblatts Original eine Formel steht. Falls ja, wird dieser Formeltext mit dem Text der entsprechenden Zelle eines anderen Blatts verglichen. Das Tabellenblatt soll aber variabel bleiben und wird hier in Zelle G18 z.B. über Datenüberprüfung angeboten.

Da entweder eine Formel im anderen Blatt steht, oder eine Zahl, habe ich WENNNV gewählt. Die Funktion FORMELTEXT für eine Zelle mit reinem Zahleninhalt ergibt #NV. Im Falle, dass beim Vergleich mit einer falschen Formel oder einem Zahlenwert verglichen wird, wird die Koordinate dieser Zelle in R1C1-Schreibweise angeboten.
Sollte z.B. die Zelle D5 abweichen, liefert "Z"&ZEILE()&"S"&SPALTE()) das Ergebnis Z5S4.

Datei zum Download:
Blattvergleich

Zum YouTube-Video:
Videolink: http://youtu.be/nlQpgUaLz3M

2013-05-05

Excel 2013 – FORMELTEXT, ISTFORMEL und WENNNV

Identifizieren, ob eine Zelle eine Formel enthält und wie diese lautet. Zwei in Excel 2013 eingeführte Funktionen unterstützen Sie dabei.

=FORMELTEXT(Zellbezug) =ISTFORMEL(Zellbezug)

Mit der Funktion =FORMELTEXT(A1) lässt sich die Formel der Zelle A1 anzeigen.
Steht dort allerdings keine Formel, zeigt das Formelergebnis #NV an.

Mit der Funktion =ISTFORMEL(A1) kann eine Zelle dahin gehend überprüft werden, ob überhaupt eine Formel in der Zelle A1 ist. Das Ergebnis lautet hier WAHR oder FALSCH.

Folglich kann mit =WENN(ISTFORMEL(A1);FORMELTEXT(A1);A1) geprüft werden, ob sich eine Formel in einer Zelle befindet. Falls WAHR, wird diese angezeigt, Falls FALSCH, wird der Zellwert angezeigt.

Statt =WENN(ISTFORMEL(A1);FORMELTEXT(A1);A1) kann aber auch die ebenfalls neue Funktion =WENNNV(FORMELTEXT(A1);A1) genutzt werden. Ähnlich der mit Excel 2010 eingeführten Funktion WENNFEHLER wird bei WENNNV geprüft, ob bei einer Formel oder Funktion der Wer #NV herauskommt. Falls nicht, wird einfach die Funktion ausgeführt. Falls doch, wird das Alternativergebnis ausgeführt.
Die Funktionen lassen sich auch dafür nutzen, Zellen mit Formeln optisch über die Bedingte Formatierung hervorzuheben. Zum Beispiel können Sie die Stellen hervorheben, wo Formeln stehen sollten aber nun manuelle Eingaben zu finden sind.

Die Funktion =ISTFORMEL(B2)=FALSCH liefert das Ergebnis FALSCH, falls sich eine Formel in Zelle B2 befindet, da hier das Ergebnis des Vergleichs gilt. Ist eine Formel in Zelle B2, kommt bei =ISTFORMEL(B2) das Ergebnis WAHR heraus und letztendlich bei =ISTFORMEL(B2)=FALSCH ein =WAHR=FALSCH, wodurch hier ein FALSCH resultiert. Entsprechend bei einer Formel in Zelle B2 ein WAHR.

Das WAHR als Formelergebnis ist bei der Bedingten Formatierung notwendig, um die Formatierung auszulösen.

Vorab wurde der Bereich B2:B5 markiert und über das Register START in der Gruppe FORMATVORLAGEN der Schalter BEDINGTE FORMATIERUNG betätigt. Wählen Sie hier Neue Regel und anschließend den untersten Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden. B2 ist bei der Markierung die hervorgehobene Zelle, in der auch Zelleingaben stattfinden würden. Die Zelle B2 wird stellvertretend für die drei anderen Zellen formatiert. Die jeweiligen Zellen beziehen die Formel entsprechen auf sich. Zum Beispiel =ISTFORMEL(B5)=FALSCH in Zelle B5.

Die Abbildung zeigt durch die Hervorhebung, dass in Zelle B3 keine Formel vorliegt.

Spannender wird es, wenn es mehrere Tabellenblätter gibt und dort auf die Zelle genau überprüft werden soll, ob in den einzelnen Zellen der jeweiligen Blätter dieselben Formeln vorliegen, wobei die manuellen Eingaben von Werten abweichen dürfen. Also =FORMELTEXT(Blatt1!A1)=FORMELTEXT(BLATT2!A1) bzw. =FORMELTEXT(A1)=FORMELTEXT(BLATT2!A1), wenn sich der Vergleich auf Blatt1 befindet.

Im nächsten Beitrag werde ich mehrere Monatsblätter mit einem Referenzblatt vergleichen…

Bei früheren Excel-Versionen kann man über =ZELLE.ZUORDNEN(48;Zellbezug) ermitteln, ob sich eine Formel in der Zelle befindet. Mehr dazu in Excel # 244 bei YouTube.

Hier gibt es das YouTube-Video:
Videolink: http://youtu.be/tnQ4DHkUjqM

2013-05-04

Excel – Dynamischen Bereich in Datenüberprüfung nutzen

Eine Liste soll als Datenbasis für eine Zelle dienen, bei der über Datenüberprüfung eine Auswahl angeboten werden soll. Die Ausgangsliste umfasst jedoch nicht nur die sichtbaren Werte, sondern auch die Zellen darunter, in denen sich Formeln befinden.

Wenn die Liste dynamisch aufgebaut werden soll, muss der Bereich der Zellen mit Inhalt <> „“ ermittelt werden.
Hierfür verwende ich einen Namen. Der Name wird über Formeln – Namens-Manager – Neu eingegeben. Darunter wird nun nicht ein Bereich mit der Maus markiert, sondern über eine Formel ermittelt.

Wenn dieser Bereich nun über den Namen definiert wurde, kann der Name des Bereichs in der Datenliste genutzt werden. Unter Daten – Datentools – Datenüberprüfung muss der Eintrag Liste ausgewählt werden. Dort kann nun über die Taste F3 auf die Namen zugegriffen werden.

Übungsdatei:Excel_0355_Datenueberpruefung

Link zum YouTube-Video:
Videolink: http://youtu.be/1QNoxjNe9Fs

2013-05-04

Excel – Letzte beschriebene Zeile ermitteln – AGGREGAT

Die Funktion =ANZAHL2(A:A) würde eigentlich die letzte Zeile zurückgeben, wenn alle Werte in Spalte A durchgängig beschrieben wären und sich unter den sichtbaren Werten nicht noch Formeln oder Funktionen befinden würden, die das Ergebnis „“ liefern.

Sollten Sich aber Formeln in Spalte A befinden, welche das Ergebnis =““ liefern, werden diese ebenso mitgezählt.

Und leere Zellen machen das Ergebnis von =ANZAHL2(A:A) direkt unbrauchbar. Man kann zwar auch die relative neue Funktion =ANZAHLLEEREZELLEN(A:A) verwenden, stolpert hier aber auch über die „“-Zellen.

Es muss also eine Funktion her, die folgende Punkte abdeckt:
– Es soll die letzte beschriebene Zelle einer Spalte ermittelt werden.
– Die letzte Zelle ist die Zeile mit der größten Zeilennummer.
– Leere Zellen sollen nicht berücksichtigt werden.
– Zellen mit dem Formelergebnis =““ sollen auch nicht berücksichtigt werden.

Seit Excel 2010 steht die Funktion AGGREGAT zur Verfügung. AGGREGAT(14;…) ermittelt wie KGRÖSSTE den k-größten Wert eines Bereichs – also den größten, zweitgrößten etc.

AGGREGAT(14) ist zudem eine Matrix- bzw. Arrayfunktion. Das bedeutet, es wird nicht generell über einen Bereich geprüft, sondern es wird jeder Wert des Bereichs einzeln betrachtet und geschaut, ob dieser spezielle Bedingungen erfüllt.

=AGGREGAT(14;4;(A:A<>"")*ZEILE(A:A);1)

Hier wird also geprüft, ob ein Wert in Spalte A ungleich leer ist. Falls ja, wird hier mit WAHR, also mit 1 multipliziert. Zusätzlich wird von jedem Wert die Zeilennummer ermittelt. Als Ergebnis von (A:A<>"")*ZEILE(A:A) resultiert eine Liste von Zeilennummern, der Zellen auch tatsächlich Werte beinhalten.
AGGREGAT(14;..;..;1) sorgt nun nur noch dafür, aus der resultierenden Liste den 1-größten Wert zu ermitteln.

Sollte eine ältere Version von Excel vorliegen, kann man auch eine Matrix-Formel mit {}-Klammern erzeugen. Das Verfahren wird in der Reihe Matrix-Funktion Teil 09 in Video Excel # 281 beschrieben.

Zum YouTube-Video Excel # 309:
Videolink: http://youtu.be/V8gne7jQp0w

2013-05-03

Excel – Liste der Einfach- und Mehrfacheinträge

Mit ZÄHLENWENN und AGGREGAT lassen sich Listen der Einfach- und Mehrfacheinträge in einer Liste erzeugen.
Die Funktion AGGREGAT gibt es erst seit Excel 2010. Man kann alternativ auch eine Matrixfunktion in Matrixschreibweise mit geschweiften Klammern erzeugen.

Die Fragestellung hat mich in den Kommentaren zu Excel # 352 erreicht. Wie identifiziert man die Einträge, die nur einmal auftreten und lässt diese gesondert neben den Daten darstellen, die mehrfach vorkommen. Da jeder Mehrfacheintrag auch irgendwann ein erstes Mal auftritt, ergibt sich eine knifflige Aufgabenstellung.

Dazu soll halt jeder Wert nur einmal in einer gesonderten Liste auftreten.
Vielleicht ein wenig unkonventionell gelöst… aber es funktioniert:

Die Formel steht neben jedem Datensatz in einer Intelligenten Tabelle. In Spalte KUNDE gibt es Kundennummern.
=(ZÄHLENWENN([Kunde];[@Kunde])=1)+(ZÄHLENWENN($A$2:[@Kunde];[@Kunde])=1)

Der erste Teil der Formel ermittelt, ob der Wert der aktuellen Zeile überhaupt nur einmal in der ganzen Liste auftritt. Falls der Vergleich mit der Zahl 1 zutrifft, kommt als Ergebnis der Wert WAHR heraus. Falls nicht, der Wert FALSCH.
=(ZÄHLENWENN([Kunde];[@Kunde])=1)+...

Im zweiten Teil der Formel wird ermittelt, ob von A2 bis zur aktuellen Zeile der Wert zum ersten Mal überhaupt auftritt. Alle nachfolgenden Zeilen werden nicht berücksichtigt. Durch den Vergleich mit der Zahl 1 wird auch hier nachher ein WAHR oder FALSCH stehen.
=...+(ZÄHLENWENN($A$2:[@Kunde];[@Kunde])=1)

WAHR erhält bei einer Addition den Wert 1, FALSCH automatisch den Wert 0. Somit ergibt sich eine 2, falls der Wert überhaupt nur einmal überhaupt in der Liste auftritt. Eine 1, falls der Wert mehrfach vorkommt und hier gerade zum ersten Mal auftritt. Eine Null, falls der Wert vorher schon einmal vorgekommen ist.

Wie eine Liste von Unikaten anzulegen ist, wird in Video Excel # 352 und in folgendem Blogbeitrag beschrieben http://thehosblog.com/2013/05/02/excel-eintrage-einer-tabelle-ohne-doppelte-auflisten/

Link zum YouTube-Video:
Videolink: http://youtu.be/LWXxj6Bv2dM

2013-05-03

Excel – Datenüberprüfung – Liste mit Nachkommastellen

Ich nutze sehr gerne über das Register Daten die Datenüberprüfung, um den Wertebereich bei Eingaben auf die erlaubten Werte einzugrenzen.

Unter Liste kann man dort auch Verweise auf Zellbereiche, benannte Bereiche oder sogar hier eingetragene Vorgaben setzen.

Eine Anfrage in einem Forum hat mich heute aber aufschrecken lassen. Excel 2010 speichert die Listen dann nicht korrekt ab, wenn man dort eine Zahlenkolonne mit Nachkommastellen einträgt.

So sind z.B. Eingaben wie ja;nein;vielleicht möglich. Auch 1;2;5;10 wäre kein Problem.

Bei der Vorgabe von z.B. Steuersätzen 0;0,07;0;19 speichert Excel 2010 die Werte im XLSX-Format allerdings als 0,0,07,0,19 ab und bietet dann statt meiner geplanten drei Wert auf einmal fünf zur Auswahl. Bestehende Eingaben werden nicht verändert. Das ganze fällt erst nach dem Speichern, Schließen und neu Öffnen der Datei auf.

Warum sollte Sie das interessieren, wenn Sie Excel 2013 nutzen? Dort gibt es diesen Bug nicht. Aber vielleicht öffnen Sie die Datei einmal unbewusst mit Excel 2010. Umgehend sind die Werte umgeschrieben.

Das Speichern im XLS-Format ist für mich keine Alternative. Statt also direkt die Werte bei Liste in die Datenüberprüfung einzutragen, sollte man bei Liste auf einen benannten Bereich verweisen. Dieser Bereich kann gut auf einem neuen Tabellenblatt liegen und zeigt somit auch transparent an, mit welchen Datengültigkeiten in dieser Datei gearbeitet wird. Wenn man diese Werte dann auch noch als Intelligente Tabelle formatiert, passt sich der Bereich in der Datenüberprüfung automatisch an. Letzteres funktioniert aber nicht bei Excel 2003 und älter, da hier noch keine Intelligenten Tabellen verfügbar waren.

Das Video bei YouTube:
Videolink: http://youtu.be/mU4Yu6vYCrc

2013-05-02

Excel – Einträge einer Tabelle ohne Doppelte auflisten

Ich möchte die Liste aller Einträge einer Spalte haben – das aber bitte ohne Doppelte.
Über Pivot oder den erweiterten Filter kann ich mir einen Schnappschuss holen, der aber ständig aktualisiert werden muss. Bei meinen Formelrecherchen bin ich dann auf viele Lösungen mit Matrixschreibweise gestoßen, muss dafür aber die {}-Klammern mit STRG + SHIFT + ENTER setzen.

Als Nutzer von Excel 2010/2013 steht mir aber auch die Funktion AGGREGAT zur Verfügung. Und mit dieser mächtigen Arrayfunktion (Typ 15), INDEX, ZEILE und WENNFEHLER kann ich mir zuverlässig die einzelnen Werte einer Intelligenten Tabelle oder eines normalen Bereichs ausgeben lassen.

Meine Daten befinden sich in den Spalten A:D und sind als Intelligente Tabelle mit dem Namen tab_Daten formatiert.
Ich möchte die Daten aus Spalte A – tab_Daten[Kunde] in Spalte F aus gewiesen bekommen.

In Spalte D mit dem Namen tab_Daten[Erstmals] befindet sich folgende Formel:
=ZÄHLENWENN($A$2:[@Kunde];[@Kunde])

Die Formel steht in F2 und wird nachher runter kopiert:
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(tab_Daten[Kunde])/(tab_Daten[Erstmals]=1);ZEILE()-1);1);"")

Die Formel aus F2 schrittweise aufgelöst:
1. ZEILE(tab_Daten[Kunde]) gibt die Zeilen der Einträge im Bereich tab_Daten[Kunde] zurück. Man hätte auch eine andere Spalte wählen können, da alle gleich lang sind.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18; 19;20;21;22;23}/(tab_Daten[Erstmals]=1);ZEILE()-1);1);"")

2. tab_Daten[Erstmals] gibt die Werte aus Spalte D zurück.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18; 19;20;21;22;23}/({1;1;1;2;1;1;1;1;2;3;2;3;1;4;4;5;3;2;2;5;2;1}=1);ZEILE()-1);1);"")

3. Der Vergleich der Wert aus Spalte D mit dem Wert 1. Bei Übereinstimmung WAHR, falls nicht FALSCH.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23}/{WAHR;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;WAHR;FALSCH; FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR};ZEILE()-1);1);"")

4. Die Zeilennummern der Tabelle werden durch WAHR (1) und FALSCH (0) geteilt. Bei einer Division durch Null kommt die Fehlermeldung #DIV/0. Der Parameter 6 bei AGGREGAT sorgt dafür, dass diese Werte ignoriert werden.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;#DIV/0!;6;7;8;9;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;14;#DIV/0!;#DIV/0!; #DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;23};ZEILE()-1);1);"")

5. Anschließend wird ganz rechts in der Formel die aktuelle Zeile ausgelesen und um 1 reduziert (da F2 in Zeile 2 steht, muss hier bei der obersten Formel eine 1 herauskommen)
.=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;#DIV/0!;6;7;8;9;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;14;#DIV/0!; #DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;23};{2}-1);1);"")

6. AGGREGAT liefert den kleinsten Wert des Arrays und ignoriert dabei die Fehler.
=WENNFEHLER(INDEX(A:A;{2};1);"")

7. Die Funktion Index nutzt den Zeilenwert, der über AGGREGAT ermittelt wurde, um die entsprechende Zeilenposition aus Spalte A:A auszulesen.
=WENNFEHLER("K-102";"")

8. Bei einem Fehler durch zu weiteres Herunterkopieren der Formel, würde WENNFEHLER die Formel einfach ausblenden. INDEX liefert das Ergebnis sauber ab. Der erste Eintrag steht.
="K-102"

–>
K-102

Datei zum Video:
Excel_03_Datenlisten_Teil_2

Link zu YouTube:
Videolink: http://youtu.be/KPGAUrWz7Fs

2013-05-01

Videos aus der Konservenfabrik

… zumindest einer ehemaligen Konservenfabrik. HUT AB – feinster Fisch aus der Dose!

Seit 2007 habe ich meinen Arbeitsplatz – insofern ich nicht auswärts beim Kunden bin – in der Bürogemeinschaft des Medienhofs 17a in Bochum – ziemlich genau zwischen Innenstadt und Jahrhunderthalle. Aktuell (Mai ’13) steht seit langer Zeit wieder ein Schreibtischplatz zur Vermietung frei.

Als TV-Verweigerer hocke ich oft abends in meiner Küche und grüble über neue Office-Themen nach. Gerade bei komplexeren Excel-Themen sind oft mehrere Stunden Vor- und Nachbereitung notwendig. Dafür sitzen die Themen dann aber auch richtig und ich kann bei der nächsten Beratung oder bei Rückfragen in den Trainings darauf zurückgreifen.

Absolute Ruhe habe ich allerdings im schalloptimierten Aufnahmeraum des Medienhofs 17a. Kein Fenster und verkleidete Wände bieten 1,5 Quadratmeter pure Konzentration bei der nächsten Aufnahme.

Neben den vielen Videos, die Ihr vielleicht von http://www.youtube.com/athehos kennt, werden hier auch viele Videos direkt für meine Kunden produziert. Diese Tutorials beschäftigen sich mit firmeninternen Prozessen und werden dann natürlich nur im Netzwerk meiner Kunden veröffentlicht.

…mehr Infos zum Medienhof 17a unter http://www.17a.de. Einen kurzen Eindruck gibt es über den Imagefilm
Videolink: http://youtu.be/7EFXFNfYQeE

Eine tolle Hofgemeinschaft!