Excel Wertehäufungen Graphisch darstellen

[Pyrukar]

Hallo zusammen,

ich habe eine Beispiel Tabelle erstellt an der ich ein Problem lösen möchte (die Werte in der Angehängten Tabelle sind willkürlich da ich die Tabelle um die es wirklich geht nicht veröffentlichen möchte)

Die Tabelle hat 10 Relevante Spalten mit jeweils 100 Werten. Bei diesen Werten gibt es Wertebereiche die häufiger vorkommen als andere. Ich würde gerne diese Bereiche in einer Grafik hervorheben. Das ganze soll am Schluss in die Richtung Heatmap gehen. dass ich im prinzip eine Reihe von min bis Max habe und auf dieser die Bereiche die häufig vorkommen farblich oder anderweitig hervorgehoben sind und leicht zu erkennen sind.

Zusatzformeln wären durchaus möglich, wenn das zu einem guten Ergebnis führt. Jedoch ist die Zahl der Einträge in der eigentlichen Tabelle nicht auf 100 festgelegt und es sollte möglich sein, ohne bzw mit geringem Aufwand neue Einträge hinzuzufügen. Theoretisch würde ich auch nicht vor VBA zurückschrecken, jedoch wüsste ich nicht wie ich an dieses Problem herangehen sollte.

Mit welchen Diagramtypen würdet ihr in diesem Fall arbeiten? Hat jemand schonmal was in die Richtung versucht? Ist das mit Excel überhaupt Sinnvoll möglich?

gruß
Pyrukar

 

[Snooty]

Evtl genügt es dir, wenn du die Zellen mit einer bedingten Formatierung einfärbst?

 

[DeusoftheWired]

Häufigkeitsverteilung → Histogramm (hab vor knapp zwei Monaten was Ähnliches gesucht )

https://support.office.com/de-de/ar...in-excel-85680173-064b-4024-b39d-80f17ff2f4e8

 

[Pyrukar]

Hmmm, histogramm klingt spannend, aber soweit ich das verstanden habe muss ich das jedesmal nachdem ich werte Hinzufüge neu erstellen bzw. aktualisieren.
Gibts da ne möglichkeit das automatisiert zu gestalten? ich meine wenn ich einen neuen Eintrag in die Tabelle mache wäre es super wenn das Histogramm sich automatisch aktualisiert. Wenn jemand weis, wie man per VBA auf so etwas zugreift oder es einen Code gibt der das selbe macht dann immer her damit, ich habe kein Problem auf einen VBA Knopf zu drücken. Ich möchte nur nicht für jeden Eintrag den ich hinzufüge den ganzen Histogramm Vorgang widerholen.

Ich sollte an dieser Stelle vielleicht noch hinzufügen, dass ich mit Excel 2010 arbeite, sollte sich da was in den neueren versionen getan haben

 

[DeusoftheWired]

Du könntest unter Schritt 6. a. den Eingabebereich nicht starr mit $A$2:$A$50 festlegen, sondern mit $A:$A – dann nimmt das Diagramm so viele Werte, wie in Spalte A eingetragen sind.
Analog das ganze für den Klassenbereich, wenn sich auch die Gruppen ändern solen.

YouTube

An dieser Stelle steht ein externer Inhalt von YouTube, der den Forumbeitrag ergänzt. Er kann mit einem Klick geladen und auch wieder ausgeblendet werden.

YouTube-Embeds laden

Ich bin damit einverstanden, dass YouTube-Embeds geladen werden. Dabei können personen­bezogene Daten an YouTube übermittelt werden. Mehr dazu in der Datenschutzerklärung.

YouTube-Embeds laden

Datenschutzerklärung

 

[Pyrukar]

Soo,

ich hab mir jetzt einfach mal überlegt, was so ein Histogramm eigentlich tut: Prinzipiell ist das ja eine Zählenwenns Funktion mit >x<. Da die Datenanalyse tools jedes mal manuell aktualisiert werden müssen habe ich mir also meine Histogrammtabelle selbst erstellt und eigentlich hat das auch genau nach Plan funktioniert.

Jetzt frage ich mich, ob man das ganze nicht einen Schritt weiter führen kann. Ich möchte meine Zählenwenns Funktion dahingehend ausbauen, dass sie die Zellenwerte nicht nur auf ihre Größe hin sortiert. In meiner tatsächlichen Tabelle sind bestimmte Werte, weil sie besonders hervorstechen sollen, Fett, Unterstrichen oder Farblich hinterlegt. Kann ich eine Zählenwenns Funktion auch danach Fragen lassen? Bei der Windwoshilfe steht was von Vergleichen mit String und Vergleichen mit Werten.

Kann man sich den VBA Code von diesen Funktionen mal anschauen bzw eine Kopie davon verändern sodass sie auch die Formatierung berücksichtigen können. Grundsätzlich müsste das ja mit einer VBA Function möglich sein ich hab aber bisher keine Erfahrung im Basteln von Funktionen mittels VBA da ich bisher wenn ich VBA benutzt habe nur mit Subs gearbeitet habe.

gruß

Pyrukar

 

[DeusoftheWired]

In meiner tatsächlichen Tabelle sind bestimmte Werte, weil sie besonders hervorstechen sollen, Fett, Unterstrichen oder Farblich hinterlegt. Kann ich eine Zählenwenns Funktion auch danach Fragen lassen?

Also für eine Suche nach excel zählenwenn fett kursiv finden sich etliche brauchbare Treffer mit VBA.

Überleg dir aber am Anfang dieses Projekts zweimal, ob du wirklich optische Hilfen wie Textformatierungen als Auswertung für Daten nehmen möchtest. Für sonderlich sauber halte ich das nicht. In Datenbanken läßt sich das nicht übertragen und VBA wird bei großen Datenmengen auch langsam. Da wäre sogar noch eine Hilfsspalte besser, in der bei zu ignorierenden Werten nichts und bei auszuwertenden Werten ein x steht (dann mit ZÄHLENWENNS).

 

[Pyrukar]

hmmm, hilfsspalten haben eben immer den Faden Beigeschmack, dass man Formeln nichtmehr nach Rechts ziehen kann und sie die Übersichtlichkeit noch weiter behindern als sie sowieso schon ist.

Gibts irgendwie ne Möglichkeit das Formel-ziehen Problem zu umgehen ohne die Hilfsspalte komplett aus der Ursprungstabelle herauszuziehen und somit eine brauchbare Zuordung nahezu unmöglich zu machen?

Edit: hab da gerade eine Idee aber noch keinen Prove of Concept:
Kann man die Hilfsspalte vielleicht Formatierungsdingt automatsich erstellen? Dann könnte die Tabelle mit den Eigentlichen Werten ununterbrochen neben einer Tabelle mit Hilfswerten stehen. Irgendwie bekomme ich es nicht hin aber eigentlich müsste es doch möglich sein, über =Zelle(...) Dinge wie Formatierung oder Hintergrundfarbe der Zelle auszulesen. Damit könnte man ja dann für jedes Kriterium einzeln eine Hilfstabelle erstellen. Leider bin ich gerade wohl zu doof für die Funktion Zelle.

 

[DeusoftheWired]

hmmm, hilfsspalten haben eben immer den Faden Beigeschmack, dass man Formeln nichtmehr nach Rechts ziehen kann und sie die Übersichtlichkeit noch weiter behindern als sie sowieso schon ist.

Richtig. Einen Tod mußt du aber sterben. Ehrlich gesagt hatte ich bei meinen Tabellen – die sich auch schon mal über zwei Vierundzwanzigzoller á 1920 × 1200 ziehen können – mit mehreren Hilfsspalten noch nie dieses Problem, aber Einsatzgebiete und Anwendungsverhalten unterscheiden sich von Nutzer zu Nutzer. Die Beispieltabelle aus dem ersten Beitrag sah jetzt auch nicht unbedingt so komplex aus, daß da häufig Formeln nach rechts gezogen werden sollen.

Wie gesagt, nimm einen der Suchmaschinentreffer für excel zählenwenn fett kursiv, z. B. http://www.denisreis.com/excel-vba-fett-formatierte-werte-zaehlen/ , wenn die Vorteile der VBA-Auswertung von Schriftformatierung für dich die Nachteile mehr als aufwiegen.

 

[Pyrukar]

Okay erstmal Danke an @DeusoftheWired für deine Geduld.

...Wie gesagt, nimm einen der Suchmaschinentreffer für excel zählenwenn fett kursiv, z. B. http://www.denisreis.com/excel-vba-fett-formatierte-werte-zaehlen/ , wenn die Vorteile der VBA-Auswertung von Schriftformatierung für dich die Nachteile mehr als aufwiegen.

und gleich noch ein Entschuldigung hinterher: irgendwie hat meine selektive Wahrnehmung dieses tolle HowTo noch als Sponsored Link indetifiziert und ausgemustert Alles was ich angeklickt hatte war eher undurchsichtig bis duibios

Ich hab mir jetzt meine Funktion nach eigenen Wünschen gebastelt, sodass ich jetzt direkt ein Upper and Lower Limit sowie die überprüfung auf Fett, Kursiv, Unterstichen sowie den Color Code des Hintergrunds.

Spoiler: Fuction Code

Option Explicit
Option Base 1

Function BedingtesZählen(Bereich As Range, Optional ULimit As Double, Optional LLimit As Double, Optional FForm As Boolean, Optional UForm As Boolean, Optional KForm As Boolean, Optional Color As Integer)

Dim Zelle As Range

Dim x As Integer
Dim y As Integer

For Each Zelle In Bereich

x = 0
y = 0

If Zelle = "" Then GoTo Abbruch

    If ULimit <> 0 Then
    y = y + 1
        If Zelle < ULimit Then x = x + 1
    End If

    If LLimit <> 0 Then
    y = y + 1
        If Zelle > LLimit Then x = x + 1
    End If

    If FForm = True Then
    y = y + 1
        If Zelle.Font.Bold = True Then x = x + 1
    End If
    
    If UForm = True Then
    y = y + 1
        If Zelle.Font.Underline > 0 Then x = x + 1
    End If
    
    If KForm = True Then
    y = y + 1
        If Zelle.Font.Italic = True Then x = x + 1
    End If
    
    If Color > 0 Then
    y = y + 1
        If Zelle.Interior.ColorIndex = Color Then x = x + 1
    End If
    
    
    If x = y Then BedingtesZählen = BedingtesZählen + 1
    
Abbruch:
    
Next Zelle

End Function

Ich habe sogar noch eine Möglichkeit gefunden, wie man tatäschlich im "Formel einfügen" Dialog die Hilfe füllt, muss aber sagen, dass ich das ziemlich umständlich finde. Gibts da auser einem Makro (wie diesem hier) keine Möglichkeit das zu füllen?

Ich weis, dass das inzwischen wohl im Programmieren Forum besser aufgehoben wäre aber ich will jetzt nicht dafür einen neuen Thread dort öffnen

Edit: Hab gerade bemerkt, dass die Formel auch leere Zellen mitzählt wenn keine Kriterien ausgewählt sind. Habs mit ner GoTo Sprungmarke behoben. jetzt tut sie wirklich was sie soll