So findest Du mit bedingter Formatierung Übereinstimmungen

Vor einiger Zeit fragte mich ein Psychologe, ob man mit Excel feststellen kann, wieviel Übereinstimmungen es bei der Auswertung einer Umfrage zwischen zwei Auswertungsbögen gibt.

Ich habe mich über diese Anfrage gefreut, weil Psychologen üblicherweise mit dem Programm SPSS arbeiten und weniger oder gar nicht mit Excel.

Spontan fiel mir bei der Suche nach einer Lösung die bedingte Formatierung ein.

Zunächst zeige ich Dir den ersten Auswertungsbogen, den ich Matrix1 nenne.

Datensätze1

Zeile 5 zeigt den Datensatz, der zum Vergleich steht. Ich wähle ihn aus durch Eingabe einer Zeilennummer in A5. Hier wird eine Zeilen-Nr. zwischen 9 und 36 eingeben, Der Datensatz wird durch die Formel

=WENN($A$5=““;““;INDEX(B1:G36;$A$5;))

in Zeile 5 übernommen, ist A5 leer, dann wird kein Datensatz angezeigt und es erfolgt keine bedingte Formatierung.

Bedingte Formatierung

Hierzu werden Spalte B (B9:B36), Spalte C (C9:C36), Spalte D (D9:D36) usw.einzeln markiert.

Ich gehe in die Menüleiste / Start / Bedingte Formatierung  und wähle „Neue Regel“. Dort treffe ich die Auswahl „Formel der zur Ermittlung der zu formatierenden Zellen verwenden“. Dort gebe ich die Formeln, wie nachfolgend aufgelistet ein. Unter Formatieren wähle ich Schrift oder Ausfüllen und suche mir geeignete Farben aus. Dann markiere ich die nächste Spalte und gehe über Bedingte Formatierung / Neue Regel usw. den gleichen Weg.

Die Formeln für die bedingte Formatierung sind:

Spalte B =UND(B9=$B$5;C9=$C$5;D9=$D$5;E9=$E$5)
Spalte C =UND(B9=$B$5;C9=$C$5;D9=$D$5;E9=$E$5)
Spalte D =UND(B9=$B$5;C9=$C$5;D9=$D$5;E9=$E$5)
Spalte E =UND(B9=$B$5;C9=$C$5;D9=$D$5;E9=$E$5)
Spalte F =UND(B9=$B$5;C9=$C$5;D9=$D$5;E9=$E$5;F9=$F$5)
Spalte G =UND(B9=$B$5;C9=$C$5;D9=$D$5;E9=$E$5;F9=$F$5;G9=$G$5)

Mit der bedingten Formatierung Spalten B bis E werden alle Übereinstimmungen mit den ersten 4 Elementen markiert.

Mit der bedingten Formatierung Spalten B bis E und Spalte F werden alle Übereinstimmungen mit den ersten 5 Elementen markiert.

Mit der bedingten Formatierung Spalten B bis E und Spalte F und Spalte G werden alle Übereinstimmungen mit den ersten 6 Elementen markiert.

Willst Du noch wissen, ob es Übereinstimmungen gibt und wie viele, geht das mit diesen jeweils zwei Formeln:

4 Elemente
=ZÄHLENWENNS(B9:B36;$B$5;C9:C36;$C$5;D9:D36;$D$5;E9:E36;$E$5)
=SUMMENPRODUKT((B9:B36=$B$5)*(C9:C36=$C$5)*(D9:D36=$D$5)*(E9:E36=$E$5))

5 Elemente
=ZÄHLENWENNS(B9:B36;$B$5;C9:C36;$C$5;D9:D36;$D$5;E9:E36;$E$5;F9:F36;$F$5)
=SUMMENPRODUKT((B9:B36=$B$5)*(C9:C36=$C$5)*(D9:D36=$D$5)*(E9:E36=$E$5)*(F9:F36=$F$5))

6 Elemente
=ZÄHLENWENNS(B9:B36;$B$5;C9:C36;$C$5;D9:D36;$D$5;E9:E36;$E$5;F9:F36;$F$5;G9:G36;$G$5)
=SUMMENPRODUKT((B9:B36=$B$5)*(C9:C36=$C$5)*(D9:D36=$D$5)*(E9:E36=$E$5)*(F9:F36=$F$5)*(G9:G36=$G$5))

Die Formel sind hier einzuordnen:

Datensätze3

Der zweite Auswertungsbogen, Matrix2, sieht so aus:

Datensätze2

In Zeile 5 stelle ich mit dieser Formel einen Bezug zu Matrix1 her:

=Matrix1!B5

Die Formel ziehe ich bis G5 durch.

Die Zeile 5 ist hier ebenfalls notwendig, weil in der Formel in der bedingten Formatierung kein Bezug auf ein anderes Tabellenblatt genommen werden kann, das geht nicht.

Bei der bedingten Formatierung gehe ich vor, wie im anderen Tabellenblatt. Die Formeln müssen ggf. auf die Lage der Tabelle in diesem Tabellenblatt angepasst werden.

Die automatische Markierung der Datensätze bei Übereinstimmung erfolgt hier genauso, unabhängig, in welcher Zeile er / sie sich befindet / befinden.

Ich habe hier die gleichen Datensätze genommen wie im Blatt Matrix1, nur alphabetisch sortiert. Deshalb haben die Datensätze hier eine andere Position. In der Realität werden evtl. ganz andere Datensätze vorkommen.

Das Ganze sollte auch bei 2000 und mehr Datensätzen funktionieren.

Hier DatensatzVergleich kannst Du Dir die Beispieldatei herunterladen und das Ganze einmal durchspielen.

Advertisements

Autor: Gerhard Pundt

Auf meiner Site https://clevercalcul.wordpress.com geht es um die Tabellenkalkulation mit Excel. Es wird über Funktionen, Diagramme, Basiswissen u.a.m. geschrieben.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s