Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1796to1800
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1796to1800
1796to1800
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Matrix SPALTE statt ZEILE

Matrix SPALTE statt ZEILE
28.11.2020 20:50:02
Michael
Hallo zusammen,
ich habe folgende Frage:
Warum funktioniert folgende Formel:
{=SUMME(KGRÖSSTE(B2:B79;ZEILE(1:5)))}

und diese nicht:
{=SUMME(KGRÖSSTE(B2:B79;SPALTE(1:5)))}

Der einzige Unterschied ist, dass ich einmal "ZEILE" und einmal "SPALTE" verwende. Ob ich Spalte(1) = 1 oder Zeile(1) = 1 usw. habe sollte doch egal sein. Was übersehe ich?
Hintergrund:
In Spalte B sind Zahlen vorhanden. Ich möchte die 5 größten auslesen und summieren.
Anbei die Beispieldatei:
https://www.herber.de/bbs/user/141917.xlsx
Beste Grüße und vielen Dank im Voraus
Michael

41
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Der Unterschied:
28.11.2020 21:06:56
lupo1
=ZEILE(1:5) lässt eine Variable von 1 bis 5 laufen
=SPALTE(1:1) lässt eine Variable von 1 bis 16384 laufen
Wenn Du den 15000st-größten von nur 70 Einträgen haben willst, kommt Murks dabei raus.
=SPALTE(1:1) entspricht SPALTE(A:XFD)
=SPALTE(1:5) habe ich noch nie ausprobiert.
AW: Der Unterschied:
28.11.2020 21:10:46
Michael
Hallo lupo1,
danke für deine schnelle Antwort. Was ich dann nicht verstehe: Wieso hat es bei mir in den Zellen F2 bis J2 dann geklappt? Hier habe ich ja die Formel: {=KGRÖSSTE(B2:B79;SPALTE(1:5))} verwendet, was funktioniert.
=SUMME(KGRÖSSTE(B2:B79;SPALTE(A:E)))
28.11.2020 21:16:41
lupo1
wäre korrekt.
AW: =SUMME(KGRÖSSTE(B2:B79;SPALTE(A:E)))
28.11.2020 21:25:07
Michael
ok, was ich dann aber nicht verstehe: Warum funktioniert es hier? Ich denke SPALTE funktioniert, da die Matrixformel in verschiedenen Spalten ausgegeben wird. Wären es verschiedene Zeilen, wäre ZEILE korrekt gewesen. Soweit so gut, aber wenn es sich auf eine Zelle bezieht (wie im Ausgangsbeispiel): Warum funktioniert das Ganze nur mit ZEILE?
Anzeige
Was Du machst, ist ...
28.11.2020 21:35:25
lupo1
... entweder fehlerbehaftet oder eben nicht, weil KGRÖSSTE vielleicht keinen Fehler für die Bundesligamannschaft auf dem 47. Tabellenplatz ausgibt. Den es gar nicht gibt.
Mit Deiner Datei könnte man jetzt verstehen, was Du gerade machst.
AW: Was Du machst, ist ...
28.11.2020 21:47:17
Michael
Danke lupo1, das mit A:E macht natürlich Sinn, das verstehe ich.
Dann kann ich anscheinend bezogen auf eine Matrixformel, die in mehreren Zellen ausgegeben wird "1:5" verwenden (oder "A:E"), aber wenn ich das Ganze in einer Zelle mache nur "A:E".
Das soll einer verstehen ;)
PS: Wenn ich es richtig verstanden habe, fragst du nach der Beispieldatei. Das wäre folgende: https://www.herber.de/bbs/user/141917.xlsx.
Anzeige
In Deiner Datei ...
28.11.2020 22:37:44
lupo1
... habe ich in der KGRÖSSTE-SUMME SPALTE(A:E) eingesetzt, und es kommt das Gleiche wie mit ZEILE(1:5) raus: Weil die Ziel-Richtung der Laufvariable hier egal ist (!).
Du hast übrigens in F:J anstelle von Arrayformeln Formelarrays verwendet (wie man an Deiner Formel auch von vornherein sehen kann). Das ist die seltenere Variante. Ab Excel 365 wird das übrigens zur Standardvariante, da Arrays dort dynamisch gebildet werden. Man schließt dort nicht mehr gesondert mit STRG-UMSCH-EING ab.
Wenn Du Excel 365 nicht hast, kannst Du Dich mit kostenlosem MS-Account (Hotmail oder ähnlich) bei der Webversion von Excel austoben:
https://office.live.com/start/Excel.aspx
Bei meinen "Tutorials" (etwas hochtrabend)
http://www.office-hilfe.com/support/forumdisplay.php/18-Microsoft-Excel-Tutorials
wird anscheinend gern der Gesamt-Tilgungsplan mittels eines einzigen dynamischen Formelarrays angeschaut:
http://www.office-hilfe.com/support/showthread.php/31942-Variabel-lange-Formelbereiche-mit-xl365-am-Beispiel-annuitaetischer-Tilgungen
Da "Excel für Web" noch keine Iterationseinstellung wie bei Excel unter Datei Optionen beherrscht, klappt das leider momentan nur in xl365.
Anzeige
AW: In Deiner Datei ...
28.11.2020 22:48:28
Michael
Hallo lupo1,
schaue ich mir gerne an, danke!
Genau das verwirrt mich noch: Die Logik scheint ja bei Arrayformeln wo entweder SPALTE(A:E) oder ZEILE(1:5) funktioniert, anders zu sein als bei den Formelarrays, wo auch Spalte(1:5) geht.
Genau diesen Unterschied verstehe ich noch nicht. Wenn du mir da noch helfen könntest, bin ich wunschlos glücklich :)
Du sitzst da einem beharrlichen Irrtum auf
28.11.2020 22:58:10
lupo1
Ich habe Dir schon erklärt, dass SPALTE(1:5) sinnlos ist, da es nichts anderes ist, als SPALTE(1:1) mit dem dahinterstehenden SPALTE(A:XFD). 1:5 ändert daran nichts.
Wenn Du nun SPALTE(1:5) - sinnlos - oder SPALTE(1:1) - korrekt, aber: Vorsicht! - verwendest, werden Dir bis zu 16384 Spalten zurückgegeben. Natürlich nur dann, wenn Du sie auch so abgeschlossen hast als Array. Du aber hast nur 5 Spalten abgeschlossen, und daher kommen nur die ersten 5 Einträge zum Tragen, bei Spalten mithin SPALTE(A:E).
Anzeige
AW: Du sitzst da einem beharrlichen Irrtum auf
29.11.2020 11:09:03
Michael
Hallo lupo1,
vielen vielen Dank dafür. Ich glaube es hat "Klick" gemacht.
Zusammenfassend, stimmt es so?:
Wenn in einer Zelle:
Entweder Zeile(1:5) oder Spalte(A:E). Lt. MSO-Dokumentation wird einfach wenn eine Arrayformel eingegeben wird, jede Spaltennummer/Zeilennummer des Arrays zurückgegeben. https://support.microsoft.com/de-de/office/spalte-funktion-44e8c754-711c-4df3-9da4-47a55042554b)
Wenn in mehreren Zellen (Formelarrays):
Spalte(1:5) es folgt (genauso wie bei 1:1), dass alle Spalten genommen werden (ein komplette Zeile hat ja alle Spalten). Ich benutze aber nur die ersten 5, weil ich die Formelarrays nur in 5 Spalten nutze. Spalte(A:E) gibt direkt nur 5 Werte zurück, das passt perfekt.
Bei Zeile(1:5) hätte ich 5 Werte, bei Zeile(A:E) 1.048.576, aber ich nutze nur eine Zeile, daher wird immer der erste Wert (der höchste Wert) zurückgegeben).
Falls es unverständlich war: Anbei die Datei mit den 4 Möglichkeiten in Spalte K.
https://www.herber.de/bbs/user/141920.xlsx
Anzeige
Ohne die Datei diesmal zu öffnen:
29.11.2020 11:20:02
lupo1
Anscheinend verstanden.
Matrizenumfänge können aber auch m*n statt nur m*1 oder 1*n sein. Dann beginnen die Unterschiede zwischen ZEILE(1:5) und SPALTE(A:E).
AW: Ohne die Datei diesmal zu öffnen:
29.11.2020 12:22:37
Michael
Danke lupo1!
Hast du für den zweiten Absatz zufällig ein Beispiel zum Verständnis? ;)
AW: Ohne die Datei diesmal zu öffnen:
29.11.2020 12:30:25
lupo1
F1:J4: {=INDEX(A1:D5;SPALTE(A:E);ZEILE(1:4))}
mit A1:D5:
01 02 03 04
05 06 07 08
09 10 11 12
13 14 15 16
17 18 19 20
AW: Ohne die Datei diesmal zu öffnen:
29.11.2020 13:17:07
Michael
Top, besten Dank! Das Beispiel habe ich nachvollzogen.
"Excel gut" ?
28.11.2020 21:17:52
onur
Ich verstehe nicht, was der Quatsch mit Zeile bzw Spalte usw überhaupt soll.
Abgesehen davon - wozu denn eine Matrixformel?
Siehe hier:
https://www.herber.de/bbs/user/141918.xlsx
Anzeige
AW: "Excel gut" ?
28.11.2020 21:23:10
Michael
@onur:
1. Was soll diese herablassende Frage "Excel gut"? Ich arbeite sind über 10 Jahre mit Excel und konnte bisher jedes Problem lösen, ich glaube nicht, dass man nicht "gut" wenn man eine Frage hat, die sicherlich 99% aller Anwender nicht beantworten könnten.
2. Deine Lösung ist offensichtlich und mir natürlich bekannt, aber danach hatte ich nie gefragt. Mir ging es offensichtlich darum das Ganze direkt mit einer Formel umzusetzen. Hintergrund ist, dass ich mich gerade in das Thema Matrix-Formeln einarbeite.
Also bitte: Helfen oder schweigen, aber nicht herablassend und wenig hilfreich daherschreiben.
Anzeige
AW: "Excel gut" ?
28.11.2020 21:24:32
onur
Nach zehn Jahren schon ? Alle Achtung.
AW: "Excel gut" ?
28.11.2020 21:27:35
Michael
Muss toll sein von so einem hohen Ross auf andere herabzuschauen. Es gibt auch Funktionen/Tools, die ich erst in 20 Jahren brauche und wenn man sie bisher daher nicht genutzt hat, muss man sich trotzdem eindenken, dann bringen ich 30 Jahre Erfahrung mit völligen anderen Themen (VBA, Pivot etc.) nichts.
AW: "Excel gut" ?
28.11.2020 21:37:02
onur
Du solltest dir angewöhnen, etwas bescheidener zu sein. Wenn du dich mit Matrixformeln nicht auskennst, bist du nun mal NICHT Excel gut.
Das Problem kommt nun mal vom völlig überflüssigen Verwenden von Zeile bzw Spalte (da du schon 1-5 in Z1S6 bis Z1S10 stehen hast). Zeile benutzt man in den Beispielen, die du kopiert und "modifiziert" hast, nur, um an eine Zahl von z.B. 1 bis 5 zu kommen, aber die liegen hier schon vor.
Wenn schon Matrixformel, dann Diese hier: 
=SUMME(KGRÖSSTE(Z2S2:Z79S2;Z1S6:Z1S10))

Anzeige
AW: "Excel gut" ?
28.11.2020 21:46:07
Michael
Ok, danke für deine (diesmal sachliche) Antwort, auch wenn ich denke, dass wenn man sehr viel in Excel kann (Sehr viele Funktionen, Pivot, VBA, Diagramme etc.) man durchaus in Excel gut sein kann, vor allem, da die wenigsten Anwender, die ich kenne, mit Matrix-Formeln gut umgehen können (Copy&Paste zählt für mich nicht;)
Zum Thema:
Mein Ziel hatte ich bereits beschrieben, es handelt sich um eine Übungsdatei und in der Zelle K2 möchte ich eben direkt mit meiner Funktion die Aufgabe lösen, denk dir einfach die Werte in F2 bis J2 weg.
Meine Frage bleibt allerdings: Wieso funktioniert Zeile aber nicht Spalte. Bzw. (und siehe hier Antwort auf Lupo1) wieso funktioniert Spalte einmal nur mit "A:E" und im anderen Fall mit "1:5"?
Anzeige
AW: "Excel gut" ?
28.11.2020 21:54:53
onur
Weil 
SPALTE(Z(-1):Z(3))

nun mal nicht 1-5 ausspuckt, sondern nur 2:2, weil das nun mal die Spaltennummer der Zelle ist, in der die Formel steht. DEUTSCH: Spalten der Zellen, die eins höher bis 3 tiefer stehen, aber in der gleichen Spalte. 
ZEILE(Z(-1):Z(3)
spuckt die Zeilennummern 1-5 aus (eine Zeile höher bis 3 zeilen tiefer) - also deine Zahlen 1 bis 5.
AW: "Excel gut" ?
28.11.2020 22:03:43
Michael
Ok das macht Sinn, danke für die gute Erklärung onur. So kann ich mir dich deutlich besser als Schulungsleiter vorstellen ;)
Gerne!
28.11.2020 22:04:31
onur
AW: "Excel gut" ?
28.11.2020 22:01:21
onur
Nimm es nicht wieder persönlich, aber ich rate dir, bevor du mit Matrixformeln experimentierst, erst mal bei Microsoft-Hilfe die Befehle ZEILE() und SPALTE() genau durchzulesen und dann auch noch die Hilfe für KGRÖSSTE().
AW: "Excel gut" ?
28.11.2020 22:12:27
Michael
Werde ich machen, danke!
mach Dir nichts draus, der kommt gern "pampig"
28.11.2020 21:47:11
Matthias
Hallo Michael,
mich hat er auch bei seiner absolut ersten Antwort auf eine Frage meinerseits voll angemotzt.
Er hatte zwar keine Antwort auf meine Frage, aber dumm quatschen kann er gut.
Er hat mich nur beschimpft: ich würde ihm "pampig" kommen obwohl seine Antwort nicht Zielführend war.
Dabei kommen die "pampigen" Antworten eigentlich nur von ihm selbst.
Also mach Dir nichts draus!
Gruß Matthias
AW: mach Dir nichts draus, der kommt gern "pampig"
28.11.2020 21:48:58
Michael
Danke Matthias, finde so etwas immer schade. Vor allem von jemanden, der anscheinend Schulungen gibt. Aber vielleicht kommt noch eine hilfreiche Antworte, ich reiche onur gerne die Hand, fand den Start aber etwas "unglücklich".
AW: "Excel gut" ?
28.11.2020 21:36:00
Michael
Achja und habe gerade gesehen, dass du Schulungen gibst. Die armen Teilnehmer...
AW: "Excel gut" ?
28.11.2020 21:42:34
onur
Brauchst jetzt nicht, asozial zu werden bzw dein wahres Gesicht zu zeigen.
"Excel gut" bist du vielleicht in deinem kleinen Mikrokosmos (verglichen mit den Anderen), aber hier wundert man sich halt, warum man dann noch Hilfe aus einem Forum braucht, wenn man doch "gut" ist.
Alles ist relativ.
28.11.2020 21:47:05
lupo1
AW: Alles ist relativ.
28.11.2020 22:10:14
Michael
Ich schätze deine Beiträge sehr lupo1, finde allerdings auch, dass es mir onur nicht leicht gemacht hat. Wie man in den Wald hineinruft, so kommt es halt heraus. Das ist auch vollkommen mein wahres Gesicht, wenn mir jemand mit dem ich nie zu tun hatte direkt blöd kommt, dann darf ich auch zurückschießen. Und jemand der so reagiert ist für mich niemand den ich gerne als Schulungsleiter hätte (seine letzte Antwort fand ich aber gut, was das Ganze relativiert).
Mir geht es auch hier nicht darum mich mit dem Titel "gut" zu schmücken, das war einfach eine ehrliche Einschätzung. Das Thema Matrixformeln ist sicherlich meine Achillesverse (und habe Zeile/Spalte bisher nicht gebraucht), deswegen möchte ich auch besser darin werden. In meinem Umfeld (und war schon in paar Unternehmen) war ich meist einfach der Beste in Excel und glaube, dass sehr viele, die deutlich weniger können als ich "gute Kenntnisse" in den Lebenslauf packen würden. Wenn ich aber gewusst hätte, dass es deswegen hier Anfeindungen gibt, dann hätte ich nicht "gut" gewählt.
AW: Alles ist relativ.
28.11.2020 22:11:48
Michael
Ums kurz zu machen: Habe meine Antwort und bin happy. Danke an euch beide, dass ihr euch die Zeit genommen habt.
AW: "Excel gut" ?
28.11.2020 21:36:02
Michael
Achja und habe gerade gesehen, dass du Schulungen gibst. Die armen Teilnehmer...
Ein Tipp zum Schmökern:
28.11.2020 21:40:48
lupo1
https://www.herber.de/excelformeln
Da gibt es Matrixformeln von leicht bis sehr schwierig.
Die meisten von uns (Antwortern) sind vermutlich über diese Sammlung von WF et. al. gegangen. Vieles versteht man da halt nicht auf Anhieb. Mit der Suchfunktion dort kannst Du nach ZEILE( oder SPALTE( suchen, und Dir werden die zugehörigen Titel angezeigt.
Übrigens ist in neuen Versionen jetzt alternativ möglich:
=ZEILE(1:5) als =SEQUENZ(5) und
=SPALTE(A:Z) als =SEQUENZ(;26)
Man kann die Bereiche also nun direkt variabel belegen, statt wie vorher etwas umständlicher.
AW: Ein Tipp zum Schmökern:
28.11.2020 22:04:18
Michael
Danke für den Link, ich schaue gerne rein!
AW: Matrixformeln besser verstehen ...
29.11.2020 10:07:49
neopa
Hallo Michael,
... die Grundlagen dazu sind mE hier: https://www.herber.de/forum/messages/1796139.html und ff sehr gut dargelegt. Nur das letzte Beispiel in der dortigen "Station (7)" ist etwas unglücklich. Zu der Zeit als es von Peter geschrieben wurde, vor geschätzt mind. ein Dutzend Jahren, war eine günstiger Lösung dafür aber noch nicht bekannt. Aber ansonsten gilt das meiste in den 7 Kapiteln auch heute prinzipiell noch, auch wenn es mittlerweile durch neuere Funktionen auch andere günstigere Lösungsmöglichkeiten gibt.
Gruß Werner
.. , - ...
Richtiger Link?
29.11.2020 10:18:45
lupo1
AW: leider nein, danke Dir owT
29.11.2020 10:23:20
neopa
Gruß Werner
.. , - ...
AW: sorry, nun der richtige Link ...
29.11.2020 11:09:44
Michael
Besten Dank, das sieht sehr gut aus Werner!
AW: gerne owT
29.11.2020 15:30:21
neopa
Gruß Werner
.. , - ...

93 Forumthreads zu ähnlichen Themen

Spalten statt Zeilen filtern
Steffen

Hallo Forum,
den Autofilter kenne ich, aber ich brauche einen Filter der Spalten anstatt der Zeilen und von VBA habe ich leider keine Ahnung.
ich habe eine Tabelle mit Namen in A2:A10. Im Bereich B2:X10 steht in verschiedenen Zellen entweder ein "x" oder die Zellen sind leer.
Nun hä...
29.10.2008 16:41:00
Thread-ID: 1020355
13 Beiträge
Anzeige
Sverweis mit Suchbegriff statt Spaltenindex
uteh

Hallo zusammen,
ich weiss, dass der SVERWEIS aus Suchkriterium, Matrix, Spaltenindex und Bereich-Verweis aufgebaut ist.
Ich habe hier aber eine Exceltabelle, die aus mehreren 100 Spalten besteht, die ich mit SVERWEIS durchsuchen will. Ich könnte natürlich über jede Spalte eine laufende...
17.07.2013 17:49:00
Thread-ID: 1322966
3 Beiträge
Zeile eines Formelergebnis ermitteln
Steffen

Liebe Excel Freunde,
ich habe folgendes Problem (siehe die angehängte xls).
https://www.herber.de/bbs/user/102899.xlsx
Es gibt Lager in mehreren Städten (Köln, Wiesbaden, Berlin - Spalte A) von denen ich zwei Orte anliefern kann (in DE und NL - Spalte B). In Splate C steht die PLZ d...
20.01.2016 10:41:03
Thread-ID: 1469946
2 Beiträge
Anzeige
Formel "ziehen" spalte statt zeile
Kramer


ich habe in einem Tabellenblatt namen in einer Zeile Stehen
zB. in J99 - R99
Jetzt möchte ich in einem anderen Tabellenblatt, die Namen nicht neben einander sondern untereinander stehen haben. zB. in K3-K12
Ich könnte einen Zwischenschritt einbauen und die zellen Transponieren,...
11.10.2018 12:49:42
Thread-ID: 1650765
3 Beiträge
Übertrag in Zeile statt Spalte + Runden ...
Andreas

Hallo auch,
kann mir bitte jemand sagen, was ich an folgendem Code ändern muss,
so dass die Werte wie von mir gewünscht in die Zellen der
Zeile "4" geschrieben werden (siehe "Liste_Ziel")?
Irgendwas stimmt noch nicht, weil bislang werden sie noch in die Spalte "B", also B4, B5,...
01.01.1970 00:00:00
Thread-ID: 405359
7 Beiträge
Rangliste aus Zeile statt aus Spalte
Erich

Hallo EXCEL-Freunde,
habe folgende Lösung entdeckt, mit der eine Rangliste aus einer Spalte ermittelt
wird:
http://www.excelformeln.de/formeln.html?welcher=307
Wie kann ich das ganze so übertragen dass die Werte in einer Zeile nach rechts
stehen und auch die Ergebnisse in einer...
22.05.2005 14:46:21
Thread-ID: 614647
4 Beiträge
Daten nach Import in Zeilen statt in Spalten
Thomas

Hallo
nun bin ich nach einigem Suchen in diesem Forum gelandet.
Ich habe folgendes Problem:
Ich habe anhand einer Textdatei Strassennamen in Exel geladen.
Nun erscheinen die Daten Waagerecht also Spaltensortiert in Exel.
Ich benötige die Daten allerdings Zeilensortiert also...
11.01.2007 11:45:24
Thread-ID: 836144
3 Beiträge
Spalte statt Zeile
Lili

Hallo zusammen,
ich habe unten ein Code, der mir verschiedene Daten aus mehreren Dateien eines Verzeichnises herausliest. Soweit so gut, das funzt ganz anständig.
Nun möchte ich aber die Daten nicht untereinander auflisten, sondern nebeneinander, d.h. jeder datensatz in einer neuen sp...
08.02.2007 21:27:27
Thread-ID: 844795
4 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
12
19
2
1
15
2
6
3
2
3
18
12
4
2
6
4
7
1
2
19
7
5
1
3
5