Liebe/-r Experte/-in,
besten Dank an Euch Profis im voraus.
Wie häufig kommt in der Matrix1 (zwei Spalten) die Kombination 12EK002 + E06 vor ???
Lösung: 3x Diese Anzahl soll eine Formel in die Zelle eintragen usw.
Matrix1:
12EK002 E09
12EK002 E10
12EK002 E06
11CO018 E07
14FO003 E06
14FO003 E06
12EK002 E06
12EK002 E06
12EK002 E09
12EK002 E09
01MA007 E09
01MA007 E09
01MA007 E10
Matrix 2
…E06… E07… E08… E09… E10
12EK002 … 3
14FO003 … 2
11CO018
01MA007
Hallo Jan-Erik,
ich würde mal die Funktionen ANZAHL() oder ANZAHL2() ausprobieren.
Ciao
Wolfgang
Das geht egentlich mit ‚ZÄHLENWENN‘, aber das lässt nur in Suchkriterium zu. Daher bedient man sich der Binärschreibweise von Boolean-Variablen (WAHR=1, FALSCH=0) und macht das mit ‚SUMMENPRODUKT‘. In der ersten Zelle von Matrix 2 steht dann z.B.:
=SUMMENPRODUKT((‚Matrix 1‘!$A$1:blush:A$13=$A2)*(‚Matrix 1‘!$B$1:blush:B$13=B$1))
bzw. mit deinem Beispiel:
3=SUMMENPRODUKT((‚Matrix 1‘!$A$1:blush:A$13=„12EK002“)*(‚Matrix 1‘!$B$1:blush:B$13=„E06“))
Grüße
Felix
Hallo Jan-Erik,
da wirst Du m.E. um eine Hilfsspalte nicht herum kommen. In der dritten Spalte könntest Du die Verknüpfung mit „&“ bilden. Z. B. in Spalte C: =A2&B2.
Wenn Du dann die Spalte C mit „Matrix1“ benennst, dann kannst Du in der Auswertungstabelle z. B. von A17:F21 die Formel verwenden: =ZÄHLENWENN(Matrix1;$A18&B$17). Hilft Dir das weiter? Damit erhalte ich unten stehendes. (Die Leerzeichen müssen mit TABs ersetzt werden) Gruß Michael
Matrix2
A B C D E F
17 E06 E07 E08 E09 E10
18 12EK002 3 0 0 3 1
19 14FO003 2 0 0 0 0
20 11CO018 0 1 0 0 0
21 01MA007 0 0 0 2 1
Liebe/-r Experte/-in,
besten Dank an Euch Profis im voraus.
Wie häufig kommt in der Matrix1 (zwei Spalten) die
Kombination 12EK002 + E06 vor ???Lösung: 3x Diese Anzahl soll eine Formel in die Zelle
eintragen usw.
Hallo Jan-Erik,
ich gehe davon aus, dass Du die erste Matrix in der Tabelle „Werte“ in den Spalten A und B stehen hast.
Dann habe ich in einer neuen Tabelle in der Zeile 1 die Überschriften („E0x“) stehen, in der Spalte A die anderen Werte („12EK002“…). Die Werte landen somit in den Zellen B2, C2, …; B3, C3, …
Als Formel für die B2 nimmst Du:
=SUMME(($A2=Werte!$A$1:blush:A$13)*(B$1=Werte!$B$1:blush:B$13))
Diese Formel ist eine Matrixformel, d. h. Du schließt sie nicht nur mit Return, sondern mit Strg+Shift+Return ab. Dann stehen geschweifte Klammern um diese Formel.
(Das $13 kannst Du natürlich anpassen an die gesamte Datenmenge, oder am besten mit dynamischen Namen arbeiten.)
Diese Formel kannst Du dann nach rechts ausfüllen für alle benötigten Spalten. Und das ganze dann nach unten für alle benötigten Zeilen. (Erst nach unten, dann nach rechts geht auch.)
Wenn Du willst, kann ich Dir kurz die Excel-Tabelle dazu schicken.
Gruß, Manfred
… kleine Ergänzung: Das ist eine typische Pivot-Tabelle. Dann kannst Du Dir alles sparen, inklusive der Zeilen- und Spaltenköpfe… Geht schneller und zuverlässiger.
Gruß, Manfred
Hallo Jan,
also die Funktion zählenwenn wäre hier angebracht.
=ZÄHLENWENN(D1:smiley:13;„12EK002 E09“)
allerdings müsstes Du für das obige Beispiel eine Hilfsspalte anlegen, die die inhalte von Matrix 1 in einer Spalte zusammenführt.
Gruß
Fronk
Hallo,
ich würde das mit einer einfachen Pivot-tabelle lösen. Habe die auch schon erstellt, habe aber keine E-Mail-Adresse.
Also bei Interesse E-Mail an [email protected] schicken, dann schicke ich die Tabelle in der Antwort zurück.
Viele Grüße
Christian Tschoepke
Hallo,
Deine Matrix1 steht in A1 bis B13
Der Wert 12EK002 aus Matrix 2 steht in Zelle A16 (die anderen darunter, der Wert E06 steht in B15 die anderen daneben.
In der Zelle B16 steht die Formel: =SUMMENPRODUKT(($A$1:blush:A$13=$A16)*($B$1:blush:B$13=B$15))
Diese Formel nach rechts und nach unten kopieren.
lgg
Hallo,
der Einfacheit halber habe ich mal eine Tabelle angeführt:
http://rapidshare.com/files/448625537/Excel_Spa___VI…
Zuerst erstellst du eine Hilfsspalte, in der du die Inhalte der 2 Spalten in eine Spalte bringst.
Das Zauberwort heißt dann ZÄHLENWENN. Als Bereich nimmst du die Hilfsspalte, für die Suchkriterien verknüpfst du Zeile und Spalte der Matrix2.
LG und viel Erfolg!
Entweder durch eine Hilfsspalte oder eine eigene VBA-Funktion.
Hier steht jetzt der komplette Ausdruck. Diesen kannst du Abfragen über: =Zählenwenn(Bereich=Dritte Spalte;Verketten(Zeilenname;Spaltenname)
Kopiere folgenden Text in ein VBA-Modul:
'*********
Function Matrix(Bereich As Range, Zeile As Variant, Spalte As Variant) As Integer
'Variablendeklaration
Dim ErsteZeile As Integer 'Zeile
Dim ErsteSpalte As Integer 'Spalte
Dim I As Integer 'Laufvariable
Dim Zeilenzahl As Integer 'Anzahl der Zeilen
Dim Zaehler As Integer 'Zähler
'Variablen setzen
ErsteZeile = Bereich.Row
ErsteSpalte = Bereich.Column
Zeilenzahl = Bereich.Cells.Rows.Count
I = 0
Zaehler = 0
'Zellen prüfen
For I = ErsteZeile To ErsteZeile + Zeilenzahl
If Cells(I, ErsteSpalte) = Zeile Then
If Cells(I, ErsteSpalte + 1) = Spalte Then
Zaehler = Zaehler + 1
End If
End If
Next I
Matrix = Zaehler
End Function
'*************
Wie geht das?:
In 2007 mußt du zunächst unter Excel-Optionen das Häkchen bei ‚Entwicklerregisterkarte anzeigen‘ setzen
Dann unter Entwicklertools auf VisualBasic klicken.
Oben links findest du ein VBA-Projekt mit dem Namen deiner Datei.
Ist kein Modul vorhanden -> rechte Maustaste - Einfügen- Modul.
In das Modul den Code einfügen - Fertig
Du findest dann in den benutzerdefinierten Funktionen in Excel ein Funktion Matrix die drei Eingaben erfordert: Den Bereich deiner Matrix, Zeile (In deinem Beispiel die Zelle in der ‚12EK002‘ steht) und Spalte (=Zelle in der E06 steht)
Bitte schön sorgfältig testen. Ich bitte um Verständnis das ich für die Funktion keine Garantie geben kann.
Viel Spaß
Hallo,
es gibt zwei Möglichkeiten.
-Zählenwenn: Dieser Formel gibt man einen Bereich vor und ein Vergleichsmuster. Dann ergibt sie die anzahl der Treffer die mit dm Vergleichsmuster übereinstimmen. Da hier in Matrix1 zwei unterschiedliche Spalten vorliegen, muss man sie in einer dritten Spalte mit dem &-Operator zusammenfassen. In Matrix zwei baut man mit dem selben Operator das Vergleichsmuster aus Zeilen- und Spaltenkopf des Schnittpunktes (also wenn Matrix1 in der Zelle A1 beginnt und Matrix2 in der Zelle A18, dann steht in und unter C1 folgende Formel: =A1&„-“&B1
In B19 - dem Schnittpunkt von B18 und A19 - stünde dann: =ZÄHLENWENN($C$1:blush:C$13;$A19&„-“&B$18) ) Nachteil dieser Methode ist aber dass man Spalten- und Zeilenköpfe der Matrix2 „von hand“ aus der Matrix1 generieren muss.
-Pivot Tabelle: die weitaus elegantere und flexiblere Lösung. Es hier zu erklären fällt schwer da sich auch noch Unterschiede in den versch. Excel-Versionen ergebn. Ich schlage vor hierzu einen Blick in die Hilfe zu werfen und ein wenig zu experimentieren. Ich helfe dann gerne weiter wenn es irgendwo klemmt.
Gutes Gelingen und offen für weitere Fragen
Hans Bartelme
Hallo Jan-Erik,
ich denke, die Lösung ist leichter als Du denkst „*“
Ich nehme jetzt mal an, in Spalte A1 bis A500 stehen Deine Werte für den 1. Vergleich und in Spalte B1 bis B500 die Werte für den 2. Vergleich.
Jetzt legen wir in Spalte D2 das Suchmuster für Spalte A und in E2 das Suchmuster für Spalte B.
Jetzt lösen wir uns von dem Gedanken Spalten und nennen den Bereich Matrix und mit der Formel :
=SUMMENPRODUKT((A1:A500=D2)*(B1:B500=E2))
Und bekommen als Ergebnis der Matrixberechnung die Anzahl alle Zeilen, in den die Werte als Paar gefunden werden.
Wichtig dabei ist, das es eine richtige Matrix ist, also Spalte A und Spalte B gleich lang sind.
So, ich hoffe, es hilft Dir weiter, Gruß,
Burkhard
Das ist eine Aufgabe, die sich auf unterschiedliche Arten lösen lässt:
a) Pivot-Tabelle
b) Zählenwenn - Formel
Wenn es Dir nichts ausmachte, sende bitte die Original vorlage an mich alexander.meinhard(at)web.de und ich erstelle die Lösung.
LG
Alexander
Hallo Jan-Erik,
ich ahbe eine Lösung (leider erst jetzt, da ich nicht am Rechner war) ohne Programmierung.
Methode funktioniert mit allen Excel-Versionen und notfalls sogar über viele Spalten!
Grüße aus Nürnberg
Jürgen
Hallo
Erst mal sorry für die späte Antwort. War übers WE zu Ski fahren weg.
Die Funktion ZÄHLENWENN fällt flach.
Es kommt eigentlich nur SUMMENPRODUKT in Frage.
Die Matrix1 solltest Du in 2 Bereiche teilen. („Ber1“=linke Spalte, „Ber2“=rechte Spalte)
12EK002 steht in E2 und dann alles weitere nach unten.
E06 steht in F1 und alles weitere nach rechts (G1 usw.)
erste Formel in „F2“:
=SUMMENPRODUKT((Ber1=$E2)*(Ber2=F$1))
Dann Formel nach rechts und unten kopieren.
Wichtig: Die Dollar-Zeichen richtig setzen, damit beim kopieren die Zeilen bzw. Spalten absolut bleiben.
Gruss
Matthias
Leider kann ich da momentan nicht helfen.
Hallo!
Wahrscheinlich wurde die Frage schon anderweitig beantwortet. Ich war außer Haus, daher kommt meine Antwort verspätet…
Angenommen, die erste Matrix aus dem Beispiel steht in den Spalten A und B (Zeilen 1 bis 13) und die zweite Matrix steht weiter unten (Wert „12EK002“ steht in Zelle A21; „14FO003“ in A22; „E06“ in B20, „E07“ in C20 etc. etc.).
In die Zelle B21 (entspricht der Kombination „12EK002“-„E06“) folgende Formel eintragen:
=SUMMENPRODUKT(($A$1:blush:A$13=$A21)*($B$1:blush:B$13=B$20))
diese Formel einfach nach unten und rechts ziehen.
Bei mir klappt es.
Viel Erfolg!
flocko
Hallo Jan,
wie ist die Matrix 1 denn aufgebaut? Stehen die Werte in einer Zelle oder über 2 Spalten verteilt?
Beste Grüße
Hallo nochmal.
Hat es geklappt?
Bitte um kurze Rückmeldung / Erfolgsmeldung.
Das freut uns Authoren auch.
Gruss
Matse