Zahlen in einer Excel Tabelle nach bestimmten Mustern markieren, damit sie in einer anderen Tabelle mit einer Formel einsortiert werden können?
Hallo liebe Experten, ich habe eine riesige Excel Tabelle mit Zahlen. Ich muss diese Zahlen, 6-8 pro Zeile und ca 4000 Zeilen, durchgehen und nach 4 Mustern und 2 Kategorien in andere Tabellen einsortieren. Ich dachte mir, vielleicht könnte ich das ganze etwas beschleunigen indem ich die Zahlen irgendwie markiere und eine Formel nach diesen Markierungen suchen lassen sodass das eintragen in die jeweiligen anderen Tabellen wegfällt und mir Zeit spart. Hat vielleicht irgendjemand eine Idee dazu wie ich dieses Problem gelöst bekomme. Für Ratschläge, Lösungen und Tipps wie immer sehr dankbar! Gruß Uwe
4 Antworten
Wenn es bei den Mustern um Zellformatierungen wie Schriftfarbe und Hintergrundfarbe geht, kommt man mit Formeln nicht ans Ziel. Farben als Unterscheidungskriterium sind wirklich nicht ratsam, das kann man nicht vernünftig auswerten. Wenn es keine andere Differenzierung gibt, muss man mit einem Makro über die Zellen gehen und die Farben auswerten.
Nach welcher Logik werden die Zahlen denn in die Kategorien einsortiert? Das müsste man ja mit einer Formel nachstellen oder geht die Einteilung aus Deiner Sicht nur von Hand?
ich könnte es versuchen dir zu erklären wie die Zahlen eingeteilt werden, aber ich bezweifle das es jemand versteht und jemand dies mit einer Formel nachstellen kann, aber ich versuche es mal. Also die Zahlen gehen von Spalte A:AO jede Spalte steht für eine Zahl diese Zahl wiederholt in den ca 4000 Zeilen, nehmen wir als Beispiel Spalte A steht für die Zahl 15, um die Zahl in eine der 4 Gruppen einzuteilen werden nun die Zeilen gezählt bis die Zahl wieder in Spalte A auftaucht, Beispiel: die Zahl 15 steht in A100 und in A101 das bedeutet die Anzahl der Zeilen dazwischen beträgt 0 das bedeutet zB Gruppe 1, Gruppe 1 steht für Abstände von 0 bis max 4 Zeilen zwischen den Zahlen, Gruppe 2 Abstände zwischen 5 und 9 Zeilen, Gruppe 3 Abstände zwischen 10 und 19 Zeilen und Gruppe 4 Abstände ab 20 Zeilen aufwärts. Das wäre mal der eine Teil der Einteilung. Ich habe es bisher so gemacht das ich in der Zeile nach der Zahl eintrage 1. und es dann runter ziehe bis es beim nächsten Eintrag der Zahl ist. Das heißt in meinem Beispiel für A100 müsste sich die Formel an A99 orientieren um die Zahl in eine der Gruppen einzuteilen.
In A1 und runter ziehen
=WENN(A1=15;VERWEIS(VERGLEICH(15;A2:A13;FALSCH)-1;{0;4;9;19};{"Gruppe 1";"Gruppe 2";"Gruppe 3";"Gruppe 4"}))
Allerdings geht es so nur den Abstand zur nächsten Zahl "abwärts" zu ermitteln. Für die andere Variante fällt mir spontan keine Formel ein. Per Makro halt notfalls
@Ninombre , deine Formel klappt super, das Problem ist jetzt nur das ich Zeile für Zeile die Gruppeneinteilung dafür habe wann die Zahl das nächste Mal auftaucht und nicht wie sie in der jeweiligen Zeile ist. Soll heißen, vom Prinzip her eine super Sache, aber die Zeilen müssten eben nach oben gezählt werden dann hätte ich in der jeweiligen Zeile auch die Gruppeneinteilung.
Hmm dann mit einer vielleicht mit einer Hilfsspalte
Falls in A1 schon die 15 steht, musst Du eine 1 manuell eintragen. Danach in die Hilfsspalte bis zum Ende
=WENN(A2=15;MAX(B$1:B1)+1;"")
Das ergibt dann für jede 15 eine Nummerierung in der Hilfsspalte.
Ab C2 dann:
=WENN(B2<>"";VERWEIS(ZEILE(B2)-VERGLEICH(B2-1;B$1:B$22;0)-1;{0;4;9;19};{"Gruppe 1";"Gruppe 2";"Gruppe 3";"Gruppe 4"});"")
Wenn Du nicht in Zeile 1 beginnst, musst Du die Differenz noch berücksichtigen, bei Beginn in Zeile 10 z.B. statt der -1 -10 rechnen.
Ich versuche es nochmal anders zu beschreiben, die Zieltabelle ist eingeteilt in 4 Gruppen für Kategorie A und 4 Gruppen Kategorie B jede Gruppe hat 5 Zellen. Das Endprodukt in jeder dieser (40) Zellen lautet "x" oder die Anzahl der Treffer. Die Einteilung in Kategorie A oder B geht nur von Hand in der Quelltabelle, da dachte ich an Hintergrundfarbe zum markieren, zB. Kategorie A ohne Hintergrundfarbe und Kategorie B mit Hintergrundfarbe zur Unterscheidung. Die Gruppeneinteilung erfolgt folgendermaßen in der Quelltabelle, nehmen wir als Beispiel Zeile 4 Zahlen in Zeile A100:AO100 : Zahl 1 steht auch in Zeile A96 und der Abstand zu A100 sind 3 Zeilen Gruppe 1 steht für Zeilenabstände von 0-4 also Zahl 1 = Gruppe 1. Gruppe 2 steht für Zeilenabstände von 5-9 Gruppe 3 von 10 -19 und Gruppe 4 ab 20 Zeilenabstände.
in einer 2. Quelltabelle könnte man die Gruppeneinteilung einfügen in der ich dann durch die Hintergrundfarbe die Zellen durch Kategorie A und B unterscheide.
Die Zieltabelle habe ich aufgeteilt so dass jede Tabelle nur nach ihren eigenen Kriterien suchen soll. Zieltabelle 1 sucht in A100:AO100 nur nach Zahlen der Gruppe 1 ohne Hintergrundfarbe , Zieltabelle 2 sucht nur nach Zahlen der Gruppe 1 mit Hintergrundfarbe usw.
Die 5 Zellen jeder Gruppe stehen dafür nur nach bestimmten Zahlengruppen zu suchen, dafür verschiebe ich aber den Suchbereich in der Quelltabelle in den jeweiligen Zahlengruppenbereich so dass hier kein Handlungsbedarf besteht. Ich würde jede Zelle ihrem Bereich zuordnen.
Ich hoffe ich konnte es einigermaßen erklären um damit was anzufangen.
ich würde ja gerne mal eine Beispieltabelle einfügen wenn mir mal jemand erklärt wie das geht
Gruß Uwe
Wird beim "Abstandsmessen" immer in der gleichen Spalte gesucht? Wenn in A100 also die 1 steht, wird dann nur in Spalte A geschaut oder auch in den Spalten B:AO? Spielt es eine Rolle zu welcher Kategorie die nächste Zahl gehört? Wenn A100 also Kat. A ist und in A96 die gleiche Zahl steht, allerdings als Kat. B: Soll diese dann gewertet werden?
In der Zieltabelle: Sollen die gefundenen Zahlen (bspw. Kategorie B Gruppe 2) einfach untereinander aufgelistet werden oder auch Zeilen bilden?
@Ninombre, danke erstmal für deine Formeln, also ich hab jetzt mit deiner letzten Formel mit Hilfsspalten das gewünschte Ergebnis erhalten. zu deiner Frage, es gibt immer nur eine Zahl in den Spalten, heißt: A zB nur 1, aber von Zeile zu Zeile kann die Kategorie wechseln, heißt: andere Zeile gleiche Zahl aber andere Kategorie, die Kategorie muss ich manuell vergeben. Da ich jetzt die Tabelle mit den Gruppen hab dank deiner Formel müsste ich diese Zahlen jetzt noch markieren um die Kategorie zu bestimmen, und dann bräuchte ich für die Zieltabelle die Formel mit der Suchabfrage, ich weiß nicht ob das ein Problem ist, dass in einer Zeile 1,2 oder 3 Zahlen der gleiche Gruppe und Kategorie sein kann, bedeutet die Formel muss auch zählen. In der Tabelle die ich mit deiner Formel erstellt habe, habe ich die Spalten mit der Nummerierung ausgeblendet, so das nur noch die Gruppierung zu sehen ist. Die Formel für die Zieltabelle sucht nur in Zeilen nicht in Spalten. Als Beispiel : Zieltabelle BA1:BX1 sucht nur in A1:AO1.
Um es mal zu verdeutlichen was dann damit passiert, durch die 40 Zellen die dann mit "x" und 1,2 oder 3 also die Anzahl der vorhanden Treffern je Zeile gefüllt sind, wird am Ende eine =VERKETTEN( Formel aus den 40 Zellen gemacht und dies ergibt ein Code den ich dann zur Weiterverarbeitung benötige. Nur um mal zu beschreiben was das ganze eigentlich soll.
Sub aufteilen()
Dim kat, gruppe, i, j, k As Long
For k = 1 To 40 'anzahl der Spalten
For i = 4000 To 1 Step -1 ' anzahl der zeilen
If Cells(i, k).Interior.Color = 65535 Then kat = 5 Else: kat = 0
For j = i - 1 To 1 Step -1
If Cells(j, k).Value = Cells(i, k).Value Then 'damit wird nur in der gleichen Spalte gesucht
Select Case (i - j - 1)
Case Is < 5: gruppe = 1
Case Is < 10: gruppe = 2
Case Is < 20: gruppe = 3
Case Is >= 20: gruppe = 4
End Select
Cells(Cells(Rows.Count, kat + gruppe).End(xlUp).Row + 1, kat + gruppe).Value = Cells(i, k).Value
Exit For
End If
Next j
Next i
Next k
End Sub
Die Hintergrundfarbe (gelb ist hier 65535) musst Du entsprechend ändern. Um den Farbcode zu ermitteln
Sub farbe()
range("A1").value=range("A1").interior.color
end sub
Das schreibt Dir den Farbcode dieser Zelle in sie hinein
Ich bin davon ausgegangen, dass die Werte in A1 beginnen, 40 Spalten umfassen und 4000 Zeilen. Wenn es nicht Spalte A, sondern bspw. C der Beginn ist, wäre es k=3 to k=43
Analog mit den Zeilen, wenn es nicht in Zeile 1 sondern 10 beginnt
i=4000 to 10
Da ich nicht weiß, wie Du die Treffer aufbereiten willst, erstmal nur eine einfache Variante:
In Spalte A (irgendwo unter den Quellwerten): Gruppe 1
B = Gruppe 2
C = Gruppe 3
D = Gruppe 4
Ab F dann das gleiche Spiel für die Kategorie B (also mit Farbe)
E bleibt leer zur deutlicheren Treffnung
Ob das Gruppe 1 oder sonst was steht ist egal, muss nur etwas in der Zelle sein, damit diese als letzte benutzte Zelle in der Spalte gefunden werden kann.
Die Zuordnung erfolgt dann über die Farbe und Gruppe, also Spaltennummer = Farbe + Gruppe
Das Beispiel wäre die Suchlogik innerhalb der gleichen Spalte. Wenn alternativ
dazu auch in den anderen Spalten gesucht werden soll, muss nur eine
Zeile ersetzt werden: Die if cells(j,k).value=cells(i,k).value raus und
an die gleiche Stelle:
If WorksheetFunction.CountIf(Range(Cells(j, 1), Cells(j, 40)), Cells(i, k).Value) > 0 Then
Hier stehen 1 und 40 auch für Spalte A bzw. AN. Ggf. anpassen
Anmerkung: Laufzeiten bei 40*4000 Zahlen habe ich nicht getestet. Wenn es zu lange wird, muss man ggf. noch optimieren (bspw. Schleife J nur durchlaufen, wenn man mit Countif überhaupt einen Treffer weiter oben erwarten kann)
ok ich werde es probieren, vielen Dank !
@Ninombre, ich hänge ein wenig fest mit dem Makro.
welche sind da die Quell und welche die Zielspalten bzw Zeilen?
was wird da berechnet und was wird ausgegeben? ich steh gerade voll auf dem Schlauch! du schreibst
"In Spalte A (irgendwo unter den Quellwerten): Gruppe 1
B = Gruppe 2
C = Gruppe 3
D = Gruppe 4
Ab F dann das gleiche Spiel für die Kategorie B (also mit Farbe)
E bleibt leer zur deutlicheren Trennung"
damit komm ich nicht ganz klar.
meinst du in A4001 =Gruppe 1B4001=Gruppe 2 usw.
ja, so hatte ich es in meinem Beispiel vorgesehen..
Allerdings sehe ich in Deinem letzten Kommentar, dass Du die Werte in den Spalten noch verketten willst. Ich glaube, dann bringt das Makro nix, da listet einfach die Zahlen untereinander.
der Quellbereich ist bei mir A1 : AN4000 , werden die Daten aus dem Makro dann von unten nach oben ab A4002 ausgeben? erkläre mir bitte nochmal was dein Makro macht vielleicht erkenne ich dann was ich falsch mache! Danke
Ich fürchte insgesamt, dass Deine Logik zu komplex für meine einfachen Beispiele und das Makro ist. Um das Makro laufen zu lassen müsstest Du in Zeile 4002 einfach irgendwas in die Zellen von A:I schreiben.
Das Makro betrachtet jede Zahl einzeln, da wird nicht berücksichtigt, was sonst noch in der Zeile stand.
In Spalte A stehen die Werte für Kategorie A, Gruppe 1.
B = Kat. A, Gruppe 2
C = Kat. A, Gruppe 3
etc.
Beginnend in Spalte 1 =A wird , von Zeile 4000 rückwärts bis Zeile 1 eine Prüfung durchgeführt:
Man nimmt den Wert in A4000 und geht rückwärts A3999,A3998 etc und sucht nach dem gleichen Wert. (das ist die for j=i-1... Schleife) Wird er irgendwo gefunden, dann wird die Anzahl der Zeilen dazwischen ermittelt.
Aus der Anzahl der Zeilen wird die Gruppe abgeleitet (die Case-Struktur). Zu Beginn wurde die Hintergrundfarbe der Zelle geprüft. Gibt es die passende Hintergrundfarbe dann in die Spalte des Ergebnisses schon mal 5 (ein Fehler im Makro, richtig wäre wohl 4). Der Wert aus Hintergrundfarbe (0 bzw. 4) + die Gruppe ergeben die Spaltenzahl, wohin der Wert geschrieben wird:
Kategorie B (=4) + Gruppe 2 (=2) wäre Spalte 6, also F
Kategorie A (=0) + Gruppe 3 (=3) wäre Spalte 3, also C
Das Endergebnis ist also, dass spaltenweise die Zahlen nach Kategorie und Gruppe gelistet sind, wenn es Treffer gab.
sorry ich muss passen ich komm damit nicht klar.
vielleicht könntest du dir folgendes nachbauen.
wir gehen nur von 19 Zeilen aus, und 40 Spalten.
Zelle A20 steht für den Bereich A:E und Kategorie A sowie Gruppe 1
Zelle F20 steht für den Bereich A:E und Kategorie B sowie Gruppe 1
in A:E befinden sich zB. Zahlen von 1-9
in F:J befinden sich zB. Zahlen von 10-19
usw.
das heißt Zelle B20 steht für den Bereich F:J Kategorie A sowie Gruppe 1
Zelle G20 steht für den Bereich F:J Kategorie B sowie Gruppe 1
jetzt markiere ich von A1:AN19 alle Zahlen in Kategorie A und B
wenn wir jetzt deine 2. Formel anwenden um die Zahlen in ihre Gruppen zu verteilen sodass in der Tabelle keine Zahlen mehr stehen sondern nur noch Gruppe 1,2,3 oder 4 und mit oder ohne Hintergrund dann könnten wir in den Zeilen suchen.
heißt: zB. Zeile 18 : in C18 steht Gruppe 1 markiert also Kategorie B , sofern jetzt keine weiteren Einträge mehr im Zahlenbereich 1-9 also A:E von Gruppe 1 und Kategorie B vorhanden sind kommt nun eine 1 in Zelle A38
in Zeile 18 : Zelle J18 steht auch Gruppe 1 markiert sofern hier auch nicht mehr Einträge sind im Bereich F:J also 10:19 kommt nun auch eine 1 in die dazugehörige Zelle B38, weil A:E Gruppe 1 markierte Zellen und A für 1-9,B für 10-19 steht
das ganze geht dann bis Spalte 40.
ich weiß es ist etwas komplex
die Zellen sind aber klar definiert und zugeordnet.
so sucht zB Zelle A20 in meinem Beispiel nur nach markierten Zellen mit dem Namen Gruppe 1 im Bereich A1:E1, woanders braucht diese Zelle nicht zu suchen weil die Zelle für markierte Zahlen von 1-9 definiert ist und nur in A:E befinden sich Zahlen von 1-9.
in meiner Tabelle in der ich schon manuell begonnen habe einzutragen habe ich folgendermaßen aufgebaut.
ich beschreibe nur mal eine Zeile: also die ersten 5 Zellen sind Kategorie B und Gruppe 1, Zelle 1 steht für 1-9, Zelle 2 für 10-19, Zelle 3 für 20-29 Zelle 4 für 30-39, Zelle 5 für 40-49.
das wiederholt sich in von Zelle 6-10 für Kategorie A und Gruppe 1
dann folgt das selbe für Gruppe 2,3,4
in den Zellen ohne Eintrag habe ich ein "x" gesetzt damit die Verkettung am Schluss besser ist.
@Ninombre, da wir ja schon mit deiner Formel die Gruppen also die Abstände erstellt haben bräuchten wir nur noch mit einer Formel in den verschiedenen Zellbereichen nach dem Namen Gruppe 1,2,3 und 4 und nach der Hintergrundmarkierung zu suchen und dann zu zählen, und wenns nix zu zählen gibt ein "x" dann hätten wir es. klingt irgendwie einfach. :-)
Mit Formeln kann man nicht wirklich auf die Hintergrundfarbe abfragen. Es gibt nur den bekannten Umweg über eine Formel im Namensmanager
Ribbon Formeln -> Namensmanager. Dort einen Namen "Farbe" anlegen und bei "bezieht sich auf" diese Formel rein:
=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-2)";FALSCH))
Wenn man jetzt irgendwo =Farbe eingibt wird der ColorIndex der Zelle 2 Spalten links davon angezeigt. Wichtig ist aber der Hinweis, dass bei Änderungen der Farbe nicht automatisch die Formel neu berechnet wird. Manuell mit F9 anstoßen.
Wie ist denn jetzt die genaue Logik für die Auswertung? Eine Spalte zeilenweise durchgehen?
Wenn die Zahlen in A stehen, in B die Gruppe berechnet wird, könnte dies in C stehen:
=WENN(B1<>"";WENN(Farbe=6;"Kat. B -"&B1;"Kat. A -"&B1);"X")
Den Wert bei Farbe (6) musst Du auf die von Dir verwendete ändern. Findest Du heraus, wenn Du in die Zelle 2 Spalten rechts der farbigen =Farbe schreibst.
Danach kannst Du die Ergebnisse filtern und ggf umkopieren.
@Ninombre, dank deiner Formel habe ich jetzt ja alle Zahlen in den 4000 Zeilen in ihre Gruppen unterteilt, also in der Hilfstabelle stehen jetzt anstatt der Zahlen Gruppe 1,2,3 und 4.
Ich werde diese nun von Hand in ihre Kategorien unterteilen, also Hintergrundfarbe wenn A ohne dann B.
jede Zelle in der ich jetzt eine Formel zum suchen setze, ist auf einen bestimmen Bereich einer bestimmten Gruppe und einer der beiden Kategorien zugeordnet.
ich beschreibe mal eine Formel in Worte für eine Zelle die ich dann für die anderen Bedingungen umschreiben will.
zB. Zelle A20: = suche und zähle im Bereich A1:E1 nach "Gruppe 1" und Farbe=6; wenn du nichts findest Dann "x"
für B20 wäre es dann: suche und zähle im Bereich F1:J1 nach "Gruppe 1" und Farbe=6; wenn du nichts findest Dann "x"
Sicherheitshalber noch eine Rückfragen:
Soll in A20 bei einem Treffer wirklich nur "Gruppe 1" stehen oder auch die Zahl, die als Gruppe 1 ausgewertet wurde? Du bekommst in Spalte A20 - Axxx sonst immer nur eine lange Liste mit Einträge Gruppe 1, aber nicht die Zahlen. Dafür muss man parallel die ursprüngliche Tabelle und die Hilfstabelle auswerten.
In welchen Spalten stehen denn die Zahlen und wo die Gruppen? Wenn die Zeilen gleich sind, sollte das schon gehen.
Also das Endprodukt soll nur die Anzahl oder bei keinem Treffer ein "x" sein. Zelle A20 wird nur auf eine spezielle Gruppe und auf "mit Hintergrundfarbe" programmiert. =suche und zähle in A1:E1 nach "Name Gruppe 1" und "Hintergrundfarbe ja", wenn in dem Zellbereich das nicht vorkommt dann "x".
Ich habe die Tabelle mit den Zahlen noch, mit deiner Formel für die Nummerierung habe ich eine 2. Tabelle angelegt die sich auf Tabelle 1 mit den Zahlen bezieht. Dann habe ich eine 3. Tabelle für die Gruppierung angelegt und diese bezieht sich auf Tabelle 2. Jetzt habe ich quasi in Tabelle 3 keine Zahlen mehr sondern nur noch "Gruppe 1,2,3, und 4 stehen statt der Zahlen, das reicht auch für die Auswertung. 4 Gruppen mal 2 Kategorien =8 Gruppen , jede Gruppe hat 5 Zellen, jede Zelle steht für einen bestimmten Zellbereich also sind das 40 Zellen. Da ich 5 Zellbereiche habe steht jede Zelle für einen Zellbereich.
Wegen der Hintergrundfarbe ist es etwas umständlich. Du brauchst zunächst 5 Varianten mit dem Namen.
Also Ribbon Ribbon Formeln -> Namensmanager.
Dort 5 Namen anlegen, weil die Abfrage der Hintergrundfarbe leider unflexibel ist. Du musst in dieser Formel angeben "in welchem Abstand" die auszuwertende Zelle von der Zelle entfernt ist, in der die Formel steht. Beim konkreten Beispiel, dass A20 die A1:E1 abfragt, sind es also immer 19 Zeilen und 0-4 Spalten. Daher leider 5 Namen.
Farbe0=ZELLE.ZUORDNEN(63;INDIREKT("Z(-19)S";FALSCH))
Farbe1=ZELLE.ZUORDNEN(63;INDIREKT("Z(-19)S(1)";FALSCH))
Farbe2=ZELLE.ZUORDNEN(63;INDIREKT("Z(-19)S(2)";FALSCH))
Farbe3=ZELLE.ZUORDNEN(63;INDIREKT("Z(-19)S(3)";FALSCH))
Farbe4=ZELLE.ZUORDNEN(63;INDIREKT("Z(-19)S(4)";FALSCH))
Wenn die Abfrage, die sich auf A1 bezieht nicht in A20, sondern A4000 steht wäre der Wert bei Z entsprechend nicht -19 sondern -3999, gleiches Prinzip bei den Spalten
In A20 steht dann:
=WENN(UND(Farbe0=6;A1="Gruppe 1")+UND(Farbe1=6;B1="Gruppe 1")+UND(Farbe2=6;C1="Gruppe 1")+UND(Farbe3=6;D1="Gruppe 1")+UND(Farbe4=6;E1="Gruppe 1")>0;UND(Farbe0=6;A1="Gruppe 1")+UND(Farbe1=6;B1="Gruppe 1")+UND(Farbe2=6;C1="Gruppe 1")+UND(Farbe3=6;D1="Gruppe 1")+UND(Farbe4=6;E1="Gruppe 1");"X")
Die Formel kannst Du dann runterkopieren.
Alternativ wäre noch eine Hilfstabelle, also dass Du in A20-E20 jeweils für A1 bis E1 prüfst und dann die Werte summierst. Dann spart man sich das Anlegen der Farbe1-4.
Also A20
=WENN(UND(A1="Gruppe 1";Farbe0=6);1;"")
das in B20 bis E20 kopieren und F20 wäre einfach =summe(A20:E20)
Vielleicht lohnenswert das zu prüfen, denn wenn Du in B20 den Bereich F1:J1 abfragen willst ist die Verschiebung der Spalten wieder eine andere. Das braucht dann zusätzliche Formeln.
Makrolösung wäre für Spalte A
Sub farbezahlen()
Dim i, j, anzahl As Long
anzahl = 0 'wert für gruppe 1 je zeile
For i = 1 To 19 'anzahl der Zeilen
For j = 1 To 5 'spalten hier a bis e
If Cells(i, j).Value = "Gruppe 1" And Cells(i, j).Interior.Color = 65535 Then anzahl = anzahl + 1
Next j
If anzahl = 0 Then Cells(i + 20, 1).Value = "X" Else Cells(i + 20, 1).Value = anzahl
anzahl = 0
Next i
End Sub
Anpassen: Anzahl der Zeilen die geprüft werden müssen und den Abstand zwischen dem Auswertungsbereich und der Zeile, wo die Ergebnisse stehen sollen. Das ist derzeit 20 (dieses if anzahl=0 then... cells(i+20).
Wenn Du 4000 Zeilen hast und die Ergebnisse ab 4010 stehen sollen:
for i=1 to 4000
und unten cells(i+4009,1)
Der Farbcode 65535 entspricht dem Gelb, das die Farb-Formel als 6 bezeichnet. Wenn die Zellen abgefragt werden sollen, die ohne Farbe sind, kannst Du einfach statt interior.color=65535 <>65535 nehmen
@Ninombre, entschuldige bitte aber ich fürchte wir haben uns da teilweise falsch verstanden, das war wohl eher mein Fehler. Also Teil 1 ist ja jetzt mal erledigt dafür bin ich dir sehr dankbar. Ich habe jetzt quasi eine 2. Tabelle in der statt Zahlen die Gruppenbezeichnungen stehen. Ich muss aber die Zellen manuell mit Hintergrundfarbe markieren, das heißt ich habe dann Zellen in denen dann steht Gruppe 1 und diese Zelle ist dann markiert oder auch nicht. Also Gruppe 1-4 unterscheiden sich nach Kat A und B
Ich brauche nun eine Formel die sucht und zählt und zwar einmal nach Zellen mit Hintergrundfarbe und nach "Gruppe 1" ,2,3 oder 4 und nach Zellen ohne Hintergrundfarbe, und ich möchte einen Bereich bestimmen können in dem gesucht wird. Ist es möglich nach Zellen mit Hintergrundfarbe zu suchen? Es müsste dann in der 2. Formel auch speziell nach Zellen ohne Hintergrundfarbe gesucht werden. Ist das vielleicht machbar? Nochmals danke für die Lösung der Gruppeneinteilung.
Ich danke dir erst mal recht herzlich und werde es morgen gleich mal ausprobieren :-)
"wie könnte ich die Zahlen dann noch markieren um sie von einer Formel einordnen zu lassen?"
Du stellst zunächst mal fest, welches die größte Zahl ist [=Max(Gesamtbereich)] und legst dann fest, wie viele Nachkommastellen Du benötigst. Nehmen wir mal an, die größte Zahl sei 54321,09876
Ergibt <10^5, benötigt seien aber immer nur max. 3 Nachkommastellen (nicht alle 5)
Gib vorsichtshalber noch je 1 Position dazu, also 6Stellen+4Dezimalen.
Nun fügst Du vor jeder der Zahlenspalten eine weitere ein für die Markierung in Buchstaben: Kriterium1(=vorgesehene Farbe1) sei 1, Muster1 sei a, also 1a. (Die eingefügten Spalten kannst Du später bei Bedarf wieder löschen oder auch nur ausblenden)
In einem weiteren Feld oder Blatt in gleicher Position , zB B2, die Formel:
=Blatt1!A2&Text(Blatt1!B2;"000000,0000"), das liefert dann
1a054321,0988
(kannst natürlich auch exakt 3 Nachkommastellen definieren, die Formel rundet.
Soll geschnitten werden, dann Kürzen(Blatt1!B2;3) als 1.Argument für Text(..;..) )
Formel runterkopieren, , dann Spalte nach Spalte D,F,H,J,L kopieren.
Spaltenweise Kopieren und in Blatt 1 an selber Stelle ALS WERTE EINFÜGEN,, Hilfsspalten ggf löschen oder wiegesagt ausblenden, falls für Erweiterung nach unten nochmals benötigt, Hilfsblatt ggf ebenfalls löschen (Die Formeln ergeben jetzt ohnehin Fehler, da Text(Text;..) Fehler ergibt, egal in welchem Format.
Die Kriterien sind nun zum Auslesen/weiteren Auswerten:
=links(B2;1) [sollte ursprünglich Farbe werden]
=Teil(B2;2;1) [sollte ursprünglich Muster werden]
oder zusammengefasst zu =Teil(B2;2)
=Teil(B2;3;99)*1 [ergibt wieder die Zahl ohne führende Nullen, im automat. Format sind die Nachkommastellen nun genau so, wie ursprünglich eingetragen (ohne Berücksichtigung von Nullen am Ende). Ansonsten Nachkommastellen vorgeben.
Es ist übrigens besser ein gemischtes Argument 1a zu kreieren als zB ein AA oder 11, weil besser merkbar=weniger Fehler-anfällig
Meine Meinung zur Farbmarkierung siehe meine AW in
das ist ein bißchen arg information um einen Algorithmus zu entwickeln. Da musst du schon konkreter werden am besten mit beispieltabelle..
ich frag erst mal allgemein, ist es möglich mit einer Formel nach Schriftfarbe (2 Kategorien) und Hintergrundfarbe (4 Muster) suchen zu lassen? Als Beispiel ich teile die Tabellen wohin die Zahlen sortiert werden sollen in 4 Hintergrundfarben auf und dann noch in 2 Schriftfarben auf , dann wäre noch das Problem dass die Formel die Zahlen auch zählen muss, weil es auch mehrere sein können. Die Tabelle in die die Zahlen sortiert werden zählt nur die Zahlen der 4 Muster und der 2 Kategorien. Beispiel: die Formel sucht im Bereich A1:AO1 nach Zahlen mit gelben Hintergrund und schwarzer Schrift und zählt diese, gibt es keine DANN "x". Ich könnte somit die verschiedenen Tabellen nach ihrem eigenen Suchkriterien programmieren.
um das sortieren der Zahlen komme ich nicht drum herum aber ich suche einen Weg mir das eintragen in die verschiedenen Tabellen zu ersparen.
Eine Pivottabelle löst dieses Problem auch nicht ? Dann muß ich passen.
wie könnte ich die Zahlen dann noch markieren um sie von einer Formel einordnen zu lassen?