Matrixformel Anzahl Max-Werte über mehrere Spalten
Christian G
ich m�chte folgendes Problem mit Hilfe einer Formel und dieser NG l�sen ;-),
da ich selbst aktuell nicht mehr weiterkomme und nicht wei�, ob das
�berhaupt funktioniert.
Aus einer Tabelle N:M mit ganzzahligen Werten m�chte ich f�r jede Zeile N
ermitteln, wie oft ihr Wert pro Spalte das Maximum darstellt.
Soweit bin ich gekommen f�r eine Tabelle mit :
{=SUMME((B1:R1=MAX(INDIREKT("Z1S"&SPALTE(B1:R1)&":Z19S"&SPALTE(B1:R1);0)))*1)}
Damit bekomme ich aber nur eine 1 in der Zeile, in der der Maximalwert �ber
alle Werte steht.
Kann mir jemand helfen?
Danke + Gru�
Christian
Claus Busch
Am Sun, 17 Jan 2010 12:43:37 +0100 schrieb Christian G:
> Aus einer Tabelle N:M mit ganzzahligen Werten m�chte ich f�r jede Zeile N
> ermitteln, wie oft ihr Wert pro Spalte das Maximum darstellt.
>
> Soweit bin ich gekommen f�r eine Tabelle mit :
>
> {=SUMME((B1:R1=MAX(INDIREKT("Z1S"&SPALTE(B1:R1)&":Z19S"&SPALTE(B1:R1);0)))*1)}
reicht dir nicht:
=Z�HLENWENN(B1:R1;MAX(B1:R1)) ?
Mit freundlichen Gr�ssen
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Klaus "Perry" Pago
> Aus einer Tabelle N:M mit ganzzahligen Werten m�chte ich f�r jede Zeile N
> ermitteln, wie oft ihr Wert pro Spalte das Maximum darstellt.
> {=SUMME((B1:R1=MAX(INDIREKT("Z1S"&SPALTE(B1:R1)&":Z19S"&SPALTE(B1:R1);0)))*1)}
du stellst eine Menge von Z1S1-Bez�gen her. Hierbei ist es wichtig, in
welche Zelle diese Formel eingegeben wird.
Bei Eingabe in A1 ergibt sich ein Vergleich von Z1S2:Z19S2 mit B1.
Z1S2:Z19S2 ergibt aber C2:C20. B1 soll also mit dem Maximalwert von C2:C20
verglichen werden? Als N�chstes wird C1 mit dem Maxumalwert von D2:D20
verglichen usw.
Die �bereinstimmungen werden dann in A1 aufsummiert.
Vermutlich soll die jeweilige Zelle in Zeile 1 mit dem entsprechenden
Bereich in der gleichen Spalte verglichen werden. Da gibt es mehrere
M�glichkeiten:
a) aus B1:R1=MAX wird C1:S1 (wenn der Bereich von C bis S gehen soll)
b) aus SPALTE(B1:R1) wird SPALTE(A1:Q1) wenn der Bereich von B bis R gehen
soll
Gru�
Klaus
Christian G
danke f�r eure Antworten. Entweder verstehe ich es nicht oder ich habe das
Prolem schlecht beschrieben. Daher noch einmal ein kleines Beispiel:
S1 S2 S3 S4 E
Z1 5 7 8 9 2
Z2 7 8 7 6 1
Z3 8 5 8 5 2
Es handelt sich um eine Tippgemeinschaft.
An Spieltag 1 (S1) hat Spieler 3 (Z3) die meisten Punkte usw. Die Anzahl der
meisten Punkte pro Spieler soll in Spalte E angezeigt werden.
Christian
(Sorry, erste Antwort ging nicht an Newsgroup)
Alexander Wolff
> Hallo,
>
> ich m�chte folgendes Problem mit Hilfe einer Formel und dieser NG
> l�sen ;-), da ich selbst aktuell nicht mehr weiterkomme und nicht
> wei�, ob das �berhaupt funktioniert.
>
> Aus einer Tabelle N:M mit ganzzahligen Werten m�chte ich f�r jede
> Zeile N ermitteln, wie oft ihr Wert pro Spalte das Maximum darstellt.
>
>
> {=SUMME((B1:R1=MAX(INDIREKT("Z1S"&SPALTE(B1:R1)&":Z19S"&SPALTE(B1:R1);0)))*1)}
>
> Damit bekomme ich aber nur eine 1 in der Zeile, in der der
Mit Matrixformeln (1 pro Zeile) wahrscheinlich nicht, denn eine Matrixformel
kann nur 2 Dimensionen abklappern, wenn auf einem Blatt. Du aber ben�tigst
a) jede Spalte einer Zeile (Spaltenindex)
b) MAX muss �ber alle Zeilen einer Spalte gehen (Zeilenindex)
c) und muss dann nochmals mit jeder Zeile einer Spalte verglichen werden
Dies ist ein Index zuviel. Oder mit anderen Worten: MAX kann aus einem
2dimensionalen Bereich kein lokales MAX einer Teildimension herausfischen.
Benannte Formeln habe ich hier nicht betrachtet.
- Ich bin mir da allerdings nicht ganz sicher, ob diese Situation hier
vorliegt! -
Daher kann ich momentan nur in 2 Schritten anbieten (anhand Deines Beispiels
von A1:E4 (mit Vorspalte und Kopfzeile)):
G2: =B2=MAX(B$2:B$4) kopieren bis J4
F2: =SUMMENPRODUKT(--G2:J2) runterkopieren bis F4
In K2 w�re letztere Formel besser aufgehoben, denn Zellen sollen sich
m�glichst nur auf Zellen links und oberhalb davon beziehen.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Alexander Wolff
> Daher kann ich momentan nur in 2 Schritten anbieten (anhand Deines
> Beispiels von A1:E4 (mit Vorspalte und Kopfzeile)):
>
> G2: =B2=MAX(B$2:B$4) kopieren bis J4
> F2: =SUMMENPRODUKT(--G2:J2) runterkopieren bis F4
>
> m�glichst nur auf Zellen links und oberhalb davon beziehen.
Eine Verbesserung geht so (noch mal neu anfangen):
B5: =MAX(B2:B4) kopieren bis E5
F2: =SUMMENPRODUKT(--($B$5:$E$5=B2:E2)) runterkopieren bis F4
Warum Verbesserung? Hier brauchst Du zum Datenfeld X nur eine zus�tzliche
Zeile Z, statt noch einmal das ganze Datenfeld Y.
XXXX YYYY
XXXX YYYY
XXXX YYYY
ZZZZ
Christian G
den Ehrgeiz es mit einer Formel zu schaffen. Jedoch scheine ich Excel
�bersch�tzt zu haben oder ich habe etwas nicht ganz verstanden:
Es ist mir gelungen eine Formel zu erstellen, die mir f�r jede Zeile eines
N:M (Zeilen:Spalten) Bereichs eine 1:M-Matrix zur�ckgibt, die eine 1 an der
m-ten Stelle aufweist, wenn in der Zeile ein Maximum f�r die Spalte m
vorliegt, ansonsten 0.
bspw. f�r die 10-te Zeile des Bereiches B4:R22:
={WENN(MAX(INDEX(B$4:R$22;0;SPALTE(A10:Q10)))=INDEX(B10:R$22;1;SPALTE(A10:Q10));1;0)}
Die kann ich mir in einem Bereich entsprechender Gr��e (1xM) auch ausgeben
lassen.
Naiverweise dachte ich, das Excel das Ergebnis dieser Formel nun als Matrix
betrachten w�rde und eine Matrixmultiplikation mit dem Einheitsvektor mich
ans Ziel f�hren w�rde:
=MMULT(WENN(MAX(INDEX(B$4:R$22;0;SPALTE(A10:Q10)))=INDEX(B10:R$22;1;SPALTE(A10:Q10));1;0);MTRANS(SPALTE(A10:Q10)/SPALTE(A10:Q10)))
Aber leider: #WERT! wenn man es als Matrixformel eingibt oder das erste
Element der Egebnismatrix von WENN multiplziert mit 1, wenn als normale
Formel.
Wenn ich die WENN-Matrixformel in einen eigenen Tabellenbereich schreibe,
und diesen in die Formel substituiere funktioniert es.
Excel verwendet wohl bei der Weiterverwendung des Ergebnisses nur das erste
Element. Eigentlich doof :-(
Gibt es da keinen Trick? Kann man Excel nicht beibringen das Ergebnis von
WENN als Matrix zu erkennen?
Gru�
Christian
"Alexander Wolff" <oo...@gmx.de> schrieb im Newsbeitrag
news:4b538f8e$0$3287$8e6e...@newsreader.ewetel.de...
Klaus "Perry" Pago
"Christian G" <ch...@stw-bonn.de> schrieb im Newsbeitrag
news:%23fTxn8i...@TK2MSFTNGP05.phx.gbl...
> =MMULT(WENN(MAX(INDEX(B$4:R$22;0;SPALTE(A10:Q10)))=INDEX(B10:R$22;1;SPALTE(A10:Q10));1;0);MTRANS(SPALTE(A10:Q10)/SPALTE(A10:Q10)))
> Gibt es da keinen Trick? Kann man Excel nicht beibringen das Ergebnis von
> WENN als Matrix zu erkennen?
meine L�sungsversuche mit zu der von dir gestellten Aufgabe lassen vermuten,
dass die MAX-Funktion die Schwachstelle ist. Damit scheint der
Matrix-Charakter zerst�rt zu sein und MMULT kommt nicht mehr klar. Die
WENN-Funktionen scheint jedoch alle "Schandtaten" mitzumachen. Statt INDEX
geht auch BEREICH.VERSCHIEBEN - mit dem gleichen Ergebnis. Das Ersetzen von
MAX durch (B$4:R$22>=MTRANS(B$4:R$22)=19 klappt nur mit einem Spaltenvektor
und l�sst sich nicht auf das gesammte Array ausdehnen.
Ich bef�rchte, die Nur-1-Formel-L�sung scheint es nicht zu geben. Schade
eigentlich.
Gru�
Klaus
Bernd P
> ...
> meine Lösungsversuche mit zu der von dir gestellten Aufgabe lassen vermuten,
> dass die MAX-Funktion die Schwachstelle ist. Damit scheint der
> Matrix-Charakter zerstört zu sein und MMULT kommt nicht mehr klar.
> ...
Nein. Gib zum Beispiel einmal ={1.2;3.4} als Matrixformel in die
Zellen A1:B2 ein. (Du kannst auch einfach 1 in A1, 2 in B1, 3 in A2
und 4 in B2 eingeben.)
Wenn Du nun in einen beliebigen anderen 2x2 Zellbereich als
Matrixformel =INDEX(A1:B2;{1;2};{1.2}) eingibst, dann erhältst Du die
Eingabematrix, aber falls Du im Formeleditor einmal F9 drückst, dann
bekommst Du lediglich die Auswertung 1 (keine Matrix!).
Bereich.Verschieben an Stelle von Index oder Rang statt Max helfen
auch nicht. Excel erwartet offensichtlich intern explizit eine Range
(einen Zellbezug) und kein Array.
Viele Grüße,
Bernd
Klaus "Perry" Pago
"Bernd P" <bplu...@gmail.com> schrieb im Newsbeitrag
news:5cde66a5-0750-4c5a...@h34g2000yqm.googlegroups.com...
> Bereich.Verschieben an Stelle von Index oder Rang statt Max helfen
> auch nicht. Excel erwartet offensichtlich intern explizit eine Range
> (einen Zellbezug) und kein Array.
Damit magst du richtig liegen.
Wenn man das Beispiel des OP nimmt,
S1 S2 S3 S4 E
Z1 5 7 8 9 2
Z2 7 8 7 6 1
Z3 8 5 8 5 2
f�hren mehrere Wege (INDEX, BEREICH.VERSCHIEBEN, ...)
aber alle �ber MAX zu dem folgenden Ergebnis
0 0 1 1
0 1 0 0
1 0 1 0
Der letzte Schritt wurde vom OP auch schon vorgeschlagen.
Die Zeillen-Summe dieser Matrix mittels MMULT(matrix,ZEILE(1:4)^0)
Wenn man die Matrix als Werte vorliegen hat, dann funkrioniert
es. Innerhalb einer Matrixformel klappt es leider nicht
Um die Ergebnismatrix zu ermitteln habe ich neben INDEX des OP
folgende Wege ausprobiert:
{=N((KGR�SSTE(BEREICH.VERSCHIEBEN($A$2;;SPALTE($1:$4);3;1);1)=$B$2:$E$4))}
{=(MAX(BEREICH.VERSCHIEBEN($A$2;;SPALTE($1:$4);3;1))=B2:E4)*1}
Das Zusammenbauen der vorher in Spaltenvektoren zerlegten Matrix
scheint Excel nicht zu m�gen
Gru�
Klaus
Bernd P
Die eingebauten Tabellenfunktionen haben wie gesagt (bekannte)
Grenzen.
Man kann aber eigene Funktionen entwickeln, die individuelle Wünsche
erfüllen, und diese in einem Add-in zur Verfügung stellen.
Dann geht es auch mit einer "Ein-Zellen-Formel", zum Beispiel in F2:
=SUMMENPRODUKT(--($B2:$E2=sbMax($B$2:$E$4;FALSCH;2)))
und dann hinunterkopieren.
sbMax berechnet die Spaltenmaximalwerte. Um diese Funktion verfügbar
zu haben, muss man mein Add-in Sulprobil.xll in Excel laden:
http://sulprobil.com/html/excel_addin.html
(Bitte beachten: Das Herunterladen und die Verwendung des Add-ins
erfolgt auf eigene Gefahr - bitte meinen Disclaimer lesen:
http://sulprobil.com/html/disclaimer.html
)
Viele Grüße,
Bernd
Peter Schleif
>
> Man kann aber eigene Funktionen entwickeln, die individuelle Wünsche
> erfüllen, und diese in einem Add-in zur Verfügung stellen.
> Dann geht es auch mit einer "Ein-Zellen-Formel", zum Beispiel in F2:
> =SUMMENPRODUKT(--($B2:$E2=sbMax($B$2:$E$4;FALSCH;2)))
Dann kann man auch gleich eine eigene Matrixfunktion schreiben.
F2-F4: {=AnzahlMax(B2:E4)}
Peter
Function AnzahlMax(r As Range) As Variant
Dim arr() As Integer
Dim z As Long
Dim s As Integer
ReDim arr(1 To Application.Caller.Rows.Count, 1 To 1)
For s = 1 To r.Columns.Count
For z = 1 To UBound(arr)
If r(z,s) >= WorksheetFunction.Max(r.Columns(s)) Then
arr(z,1) = arr(z,1) + 1
End If
Next
Next
AnzahlMax = arr
End Function
Bernd P
Das geht auch, aber wenn Du die benutzerdefinierte Funktion derart
speziell entwickelst, dann wirst Du sie später für ähnliche
Aufgabenstellungen mit hoher Wahrscheinlichkeit wieder anpassen
müssen.
Darüberhinaus haben vertrauenswürdige (getestete, freigegebene) Add-
ins den Vorteil, dass der Benutzer keine VBA Kenntnisse benötigt.
NB: WorksheetFunction.Max empfehle ich in der äußeren Schleife
auszuwerten, nicht in der inneren.
Viele Grüße,
Bernd