Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

Kriterium in matrix suchen und ausgeben.

Betrifft: Kriterium in matrix suchen und ausgeben. von: Christian
Geschrieben am: 26.09.2014 09:38:35

Hallo zusammen,

folgender Vorgang bereitet mir Kopfschmerzen:

Ich habe eine Firmen-ID [A:A] die in einer Tabelle [A:DN] als Primärschlüssel dient.
Beginnt absteigend in A2.

Zum Ende der Tabelle werden IDs in verschiedene Bereiche eingeteilt.
CC: Bereich A
CD: Bereich B
...
CU: Bereich S

Die Einteilung in die Bereiche erfolgt also in dem Abschnitt [CC:CU]
Bewertet wird über WAHR und FALSCH, das lässt sich leider nicht mehr ändern.

Ist eine ID Teil des Bereiches B, steht also unter der entsprechenden ID, die in A zu finden ist, die Aussage WAHR. Ein ID kann in mehreren Bereichen WAHR sein.

Für ein Auswertungssystm erfolgt eine Abfrage, in der auch die Bereiche aufgeführt werden sollen.

Ich versuche gerade eine WENN-Funktion zu basteln die diese Abfrage beinhalten.
Der Aufbau sollte wie folgt sein.

=WENN(ID XY = WAHR in einem Bereich [CC:CU]; DANN 1; SONST 0]

Leider komm ich genau hier nicht weiter. Wie schaffe ich es die Abfrage in einem ganzen Bereich ausgeben zu lassen. Ich hoffe ihr könnt mir helfen.

Mit freundlichen Grüßen,

Christian

  

Betrifft: AW: Kriterium in matrix suchen und ausgeben. von: Michael
Geschrieben am: 26.09.2014 16:46:29

Hallo Christian,

wenn Du sooo viele Spalten hast, liegt die Vermutung nahe, daß Du Excel für eine Datenbank "mißbrauchst", für die etwa Access besser geeignet wäre.

Man KANN in E natürlich größere Datenmengen verwalten, sollte aber von Datenbanken (Stichwort: Normalisierung) eine gewisse Ahnung haben. A als Primärschlüssel und Daten von B bis DN hört sich schlicht nicht "normalisiert" an, eher wie die eierlegende Wollmilchsau. Das gibt früher oder später Komplikationen. Und in A bist Du eher gezwungen als in E, Dir während der Entwicklung Gedanken über die Datenstrukturen zu machen. Aber das nur grundsätzlich.

Was gehen könnte, ist: =ZÄHLENWENN(CC2:CU2;WAHR)>0

Das spuckt WAHR aus, wenn mindestens 1 mal WAHR vorkommt.

Falls Dir das weiterhilft, ist es gut, falls nicht, wäre es hilfreich, eine anonymisierte Tabelle zu haben, um Dein Problem exakter eingrenzen zu können. [was soll eigentlich wie ausgegeben werden?]

Schöne Grüße,

Michael


  

Betrifft: AW: Kriterium in matrix suchen und ausgeben. von: Christian
Geschrieben am: 29.09.2014 12:01:01

Hallo Michael,

danke für deine Antwort. Das betreffende Sheet enthält eine Verknüpfung zu einer Access Datenbank.

Vielleicht mal zum Hintergrund:

Es gibt eine relativ große Datenbank in Access, die täglich erneuert wird.
Ich baue gerade ein Auswertungssystem über Excel.
Dazu habe ich eine Verknüpfung zwischen der Datenbank und der Excel Datei erstellt.
Das diese Konstruktion sinngemäß nicht ganz sauber ist, hab ich mir schon gedacht, kann/darf ich leider nicht ändern....:(

In dem Oben beschriebenen Schritt, werden Kunden in Branchen eingeteilt.
CC ist beispielsweise Maschinenbau, CU ist medizinischer Bereich etc.

CC=WAHR wenn Kunde =Maschinenbau.

Habe deinen Vorschlag mal verwendet und glaube das geht in die richtige Richtung.
Wenn ich das jetzt richtig verstehe, sucht er jetzt nur nach irgendeinem Wahr in diesem Bereich. Da fehlt der Bezug zur Kunden-ID. In der Tabelle sind leider tausende Firmen...
Gibt es also eine Möglichkeit jetzt noch den Bezug zur ID herzustellen? Steht in Spalte A:A


  

Betrifft: AW: Kriterium in matrix suchen und ausgeben. von: Michael
Geschrieben am: 29.09.2014 17:05:42

Hallo Christian,

nur ganz kurz, ich bin auf dem Sprung: sei so gut und lade mal eine anonymisierte Tabelle mit ein paar Testdaten rein, das Problem sollte dann mit einer überschaubaren Formel zu erledigen sein.

Schöne Grüße,

Michael


  

Betrifft: AW: Kriterium in matrix suchen und ausgeben. von: Christian
Geschrieben am: 30.09.2014 10:55:06

Hallo Michael,

habe mal eine stark vereinfachte Datei hochgeladen, die aber genau das zeigt was ich nicht hinkriege.

https://www.herber.de/bbs/user/92900.xlsx

Bin sehr gespannt auf deine Antwort!

Gruß,

Christian


  

Betrifft: AW: Kriterium in matrix suchen und ausgeben. von: Michael
Geschrieben am: 01.10.2014 02:24:38

Hallo Christian,

ich hab mal einen ganz simplen Lösungsansatz, der ganz ohne große Programmierung auskommt, und einen weiteren nach Deinen Vorgaben in die Datei geschrieben: https://www.herber.de/bbs/user/92921.xlsm

Erlaube mir noch einen Sermon zur Struktur: wenn in Access schon alles so "breit" (mit irre viel Spalten) aussieht, ist das Ding nicht vernünftig normalisiert.

Das steckt dahinter: wozu tausend "falsch" abspeichern (oder "neins" zu vorgegebenen Branchen), wenn man *nur* die zur jeweiligen Firma passende(n) Branche(n) bzw. die Verknüpfung dazu abspeichern könnte?

Tabelle Stammdaten
Schlüssel(n) Firma 
 1           AAA
..
 n           ZZZ

Die zweite Tabelle enthält Branchen:

Tabelle Branchen
Schlüssel(m) Branche
 1           Auto
..
 m           Zubehör
Eine dritte Tabelle enthält die Zuordnungen; Stichwort n:m Relation.
Schlüssel(n) Schlüssel(m)
 1           2
..
 n           4
 n           8   
Der Punkt ist: so, wie es jetzt ist, kann man nicht einfach eine weitere Branche eingeben, ohne die STRUKTUR des ganzen Dings zu ändern.

Das ist kein sauberes Datenbankdesign.

Oder so: wenn die DB sauber strukturiert ist, ist die ganze Auswerterei direkt in ACCESS ein Klacks.

Na denn, viel Spaß,

Michael


  

Betrifft: AW: Kriterium in matrix suchen und ausgeben. von: Christian
Geschrieben am: 01.10.2014 08:44:25

Hallo Michael,

wow danke für die ausführliche Antwort!

Leider kann/darf ich die Struktur der Datenbank nicht antasten.
Ich weiß ja, was das für ein Himmelsfahrtkommando ist ;)
Irgendwie muss ich diese Auswertung aber leider trotzdem bauen.

Lösungsansatz 1 ist leider nicht auf das System anwendbar, wäre aber eine entspannte Lösung gewesen :)

Lösungsansatz 2 scheint in die richtige Richtung zu gehen, zerschießt mir aber leider den Auswertungsvorgang im nächsten Schritt #Bezug taucht dann auf, vereinzelt auch #NV

Woran das liegt kann ich jetzt direkt nicht erkenne, da mir auch Bereich.Verschieben nicht geläufig ist. Werde mich jetzt damit auseinander setzen.

Vielen Dank schonmal Michael, wirklich tolle Arbeit und besonders auch nachvollziehbar. Das ist ein wertvoller Ansatz für mich


  

Betrifft: INDEX() anstelle BEREICHS.VERSCHIEBEN() ... von: neopa C (paneo)
Geschrieben am: 01.10.2014 09:33:28

Hallo Christian,

... verursacht Dir vielleicht weniger Kopfschmerzen.
Ich hab Dein gesamtes Anliegen jetzt nicht untersucht sondern lediglich Deinen Bemerkungen zu BEREICHS.VERSCHIEBEN() aufgegriffen.

Das gleich wie

=(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Stammdaten!CC1:CU1;B7;0);WAHR)>0)
erledigt die Formel:
=ZÄHLENWENN(INDEX(Stammdaten!CC2:CU99;B7;);WAHR) >0 
(für bis zur Zeile 99 und alternativ dazu wäre auch

=SUMMENPRODUKT((Stammdaten!CC2:CU99=WAHR)*(ZEILE(A1:A98)=B7))>0
möglich)

Die Multiplikation mit 1 ist übrigens nicht notwendig, weil in C4 bereits eine Multiplikation ausgeführt wird und das Ergebnis in B4 ja nur ein Zwischenwert ist.

Mehr dazu sieh auch mal hier:
http://www.online-excel.de/excel/singsel.php?f=180 und http://www.online-excel.de/excel/singsel.php?f=41

Vorangehendes ist jedoch noch keine Lösung Deiner Probleme, das Du teils Fehlermeldungen erhältst. Dazu müsste man etwas mehr wissen. Wann und wo tauchen diese auf und welche Datenarten werden von Dir ausgewertet.


Gruß Werner
.. , - ...



  

Betrifft: AW: INDEX() anstelle BEREICHS.VERSCHIEBEN() ... von: Christian
Geschrieben am: 01.10.2014 12:19:48

Hallo Werner,

danke für deinen Input, leider taucht hier durchgehend #Wert auf.

Ausgewertet wird folgendes:

Jeder Kunde erhält eine Nummer, die Firmen ID, nur zahlen, negativ wie positiv.
In Sheet 1, befinden sich alle Kundendaten.
Spalte A1: Firmen-ID,
Spalte A2: 0000000001
Spalte A2: 000000003 usw.

in Spalte CC bis CU

sind mögliche Branchen.

Spalte CC1: IST-Medizin
Spalte CC2: WAHR
Spalte CD1: IST-Maschinenbau
Spalte CD2: FALSCH usw.

Es also abgefragt werden, zu welcher Branche die ID gehört.

In einem Bewertungssheet gibt es eine Zelle in der jede ID einmal eingefügt wird über ein makro.
In diesem Fall B7.

Darauf hin erfolgen verschiedene Abfragen, unter anderem die aktuelle Branchen-Abfrage.
Alle Abfragen werden mit 1/0 bewertet, so dass in einer weiteren Spalte eine Endpunktzahl ausgespuckt wird, welche weiterkopiert wird.

Ich hoffe, dass hat ein wenig licht ins dunkel gebracht.

Über Sinn brauch man gar nicht sprechen, mir sind die Hände gebunden ;)


  

Betrifft: AW: INDEX() anstelle BEREICHS.VERSCHIEBEN() ... von: Christian
Geschrieben am: 01.10.2014 12:27:28

Hallo Werner,

danke für deinen Input, leider taucht hier durchgehend #Wert auf.

Ausgewertet wird folgendes:

Jeder Kunde erhält eine Nummer, die Firmen ID, nur zahlen, negativ wie positiv.
In Sheet 1, befinden sich alle Kundendaten.
Spalte A1: Firmen-ID,
Spalte A2: 0000000001
Spalte A2: 000000003 usw.

in Spalte CC bis CU

sind mögliche Branchen.

Spalte CC1: IST-Medizin
Spalte CC2: WAHR
Spalte CD1: IST-Maschinenbau
Spalte CD2: FALSCH usw.

Es also abgefragt werden, zu welcher Branche die ID gehört.

In einem Bewertungssheet gibt es eine Zelle in der jede ID einmal eingefügt wird über ein makro.
In diesem Fall B7.

Darauf hin erfolgen verschiedene Abfragen, unter anderem die aktuelle Branchen-Abfrage.
Alle Abfragen werden mit 1/0 bewertet, so dass in einer weiteren Spalte eine Endpunktzahl ausgespuckt wird, welche weiterkopiert wird.

Ich hoffe, dass hat ein wenig licht ins dunkel gebracht.

Über Sinn brauch man gar nicht sprechen, mir sind die Hände gebunden ;)


  

Betrifft: Ursache für die Fehlermeldung liegt anderweitig .. von: neopa C (paneo)
Geschrieben am: 01.10.2014 13:49:41

Hallo Christian,


... die von Dir bezeichnet Fehlermeldung kann normalerweise nicht als Ergebnis von INDEX() auftreten.

Kannst Du Deine Mappe wieder so reduzieren und hier einstellen, dass Dein Problematik nachvollziehbar ist?


Gruß Werner
.. , - ...


  

Betrifft: Fehlermeldung von: Michael
Geschrieben am: 01.10.2014 22:04:48

Hallo Christian,

erst Mal danke für das positive Feedback!

Ich möchte mich Werner anschließen und um eine neue Datei bitten.

Hab mir nämlich das erste Mal schon gewünscht, zu sehen, was Du makromäßig treibst.

Muß ja nicht "zu viel" sein, nur das Makro, das die Werte schreibt und liest - vielleicht ist da irgendwo ein Hund versteckt.

- Denkpause -

Also, ich hab Dir das Makro skizziert: ich habe alles unverändert gelassen, insbesondere das Bereich.verschieben, derweil ich INDEX zwar liebe, aber nicht in diesem Fall.

Meine Vermutung ist, daß Du als Schleifenzähler nicht die Zeilennummer verwendet hast, sondern evtl. eine Nr., die Du aus dem Primärschlüssel generiert hast. Der ist zwar eindeutig, sonst wäre er ja nicht brauchbar, aber das heißt nicht, daß da keine "Löcher" in der Nummerierung sein können, so daß die enthaltenen Zahlen irgendwann höher sind als die verfügbaren Zeilen.

Bei mir funktioniert es jedenfalls mit Deinen gestrigen Beispielswerten, und welche Werte EXCEL nun in eine Zelle schreibt (ob Zahlen oder Text oder bla), das muß einfach egal sein.

Das Makro:

Option Explicit

Sub Auswerten()
 Dim i, zeilen As Long
 zeilen = Sheets("Stammdaten").Range("A1").End(xlDown).Row
 Sheets("Auswertung").Activate
 For i = 2 To zeilen
   Range("b7").Value = i - 1
   ' weil Beginn ab Zeile 2, Formel
   ' in B2, B4 und B5 aber unverändert
   Range("L" & (i + 20)).Value = Range("B2").Value
   Range("M" & (i + 20)).Value = Range("D4").Value
   Range("N" & (i + 20)).Value = Range("D5").Value
   Range("O" & (i + 20)).Value = Range("D9").Value
 Next i
 MsgBox (zeilen - 1) & " Werte ausgewertet."
End Sub
Die Tabelle: https://www.herber.de/bbs/user/92937.xlsm

Schöne Grüße,

Michael


  

Betrifft: P.S.: Fehlermeldung von: Michael
Geschrieben am: 01.10.2014 22:13:56

Hallo Werner,

nur um keine schlechte Stimmung aufkommen zu lassen: gegen INDEX ist prinzipiell ja nix zu sagen, ich war schlicht zu faul, meine Version der Tabelle in dem Punkt zu ändern und hab damit halt weitergemacht.

Neugierig war ich natürlich auch, ob es so funktioniert.

Schöne Grüße,

Michael


  

Betrifft: AW: P.S.: Fehlermeldung von: Christian
Geschrieben am: 02.10.2014 08:28:28

Hallo Hallo,

danke für die Zahlreichen Vorschläge, ich bin im Moment noch am begutachten.
Ich habe mir überlegt zum Wochenende das von mir gebastelte System zu kopieren und eine anonyme Version davon herzustellen und wahrscheinlich Sonntag Abend hochzuladen.

Das ganze System ist sehr unfangreich und besteht aus vielen Makros etc die ich mir selber zusammeln gebastelt.

Besonders den erfahrenen Hasen unter euch werden wahrscheinlich die Haare zu berge stehen.
Naja es funktioniert zumindestens - größten teils.

"If it looks stupid, but it works, it aint stupid"

Das dürfte das Motto für mein System sein.

Würde mich sehr freuen wenn da mal jemand drauf guckt, dürft auch interessant sein, kann mir nicht vorstellen das man so etwas häufig zu sehen kriegt.

Das wird wahrscheinlich nicht funktionieren, aber auf grund das Umfanges würde ich mich am liebsten mal mit jemand dazu zusammensetzen, Raum NRW.(Natürlich kann man da auch über eine Aufwandsentschädigung sprechen :D) [Sollte das in irgendeiner Form gegen die Regeln verstoßen, bitte ich das entschuldigen]

Mit freundlichen Grüßen,

Christian


  

Betrifft: Anmerkung ... von: neopa C (paneo)
Geschrieben am: 02.10.2014 09:51:31

Hallo Christian,

... da der Thread von Dir schon am vorigen Freitag gestartet wurde, wird dieser am WE hier im Forum nicht mehr aktiv sondern ins "Archiv gewandert" sein. Dort kann nur noch lesend zugriffen werden. Deshalb stelle Deine evtl. neue Fragen in einem neuen Thread und nimm dort eine Link auf diesen Thread auf und vielleicht einen Vermerk im Betreff, dass Michael und ich diesen auch gleich erkennen.

@ Michael,

warum sollte hier bei mir: "schlechte Stimmung aufkommen" ?. Die kommt bei mir nur auf, wenn ich mich über mich selbst ärgere.

Euch beiden nun eine schönes verlängertes WE.

Gruß Werner
.. , - ...


  

Betrifft: AW: Anmerkung ... von: Michael
Geschrieben am: 03.10.2014 15:44:45

@ Werner: dann is ja alles ok - über mich selbst ärgere ich mich auch oft genug, das kenn ich zur Genüge.

Hi Christian,

ich würde Dein Angebot gerne annehmen, sitze aber zu weit weg: is nich.

Sehn wir mal, was die nächste Woche bringt...

Ich leg mich in die Sonne,

schönes Wochenende,

Michael


 

Beiträge aus den Excel-Beispielen zum Thema "Kriterium in matrix suchen und ausgeben."