EXCEL: Wie kann ich einen Wert in eine Zellposition umwandeln?
Bitte öffnet das Bild im Anhang in einem neuen Tab, damit ich meine Frage daran einfacher erklären kann:
In den schmalen Spalten stehen jeweils kleine zweistellige Zahlen. In den breiteren Spalten Zahlen mit 6 oder 7 Ziffern, also über 100.000. Links am Rand habe ich den kleinen Zahlen jeweils große zugeordnet.
Ich möchte jetzt, dass in I5 (also rechts neben den fünf schmalen Spalten) die Werte addiert werden, die den 5 zweistelligen Zahlen links daneben zugeordnet sind (Zuordnungen ganz links). Also eine universale Formel, die für die ganze Spalte I funktionier und das tut: 1. Gehe zu Zelle der gleichen Zeile fünf Spalten links von "mir" ("Ich" bin eine Zelle aus der Spalte I) und verwende ihren Wert folgendersmaßen: /////Bei I5 wäre das beispielsweise die Zelle D5 mit dem Wert 20\\\ 2. Gehe zur Zelle der Spalte A und der Zeile "Wert aus 1. minus 12" und verwende den Wert darin folgendermaßen: /////Das wäre weiter im Beispiel dann Spalte A, Zeile 20 - 12 = 8, also A8, also 3.600.000\\\ 3. Addiere den Wer aus 2. zu (und ab hier das ganze dann noch vier mal für die anderen 4 schmalen Spalten und dann hat man die Summe)
6 Antworten
Ich weiß nicht, ob es die beste Lösung ist, aber eine Lösung ist:
=INDEX(A:A;D5-12)+INDEX(A:A;E5-12)+INDEX(A:A;F5-12)+INDEX(A:A;G5-12)+INDEX(A:A;H5-12)
DH!
Wenn der Aufbau so starr bleibt - und das wird in der Frage ja gesagt - ist das natürlich effektiver als meine Lösung.
Ich glaube so kompliziert muss man es nicht machen, v.a. arbeitet Excel nicht in solchen Teilschritten:
Probier mal ob die Formel in I5 das gewünschte tut
=INDIREKT("A"&D5-12)+D5
Wenn Du die Formel in andere Zeilen oder Spalten kopierst bzw. ziehst (unten rechts packen), dann höhen sich die Angaben von Spalte und Zeile automatisch. Der Abstand von 5 Spalten bleibt erhalten.
edit: wo ich die anderen Lösungsansätze sehe: Diese Logik soll dann auch E5, F5, G5 in gleicher Weise verwenden und in I5 die Gesamtsumme ausgeben?
Einfach "gestrickt" sieht das imho so aus für I5:
=SUMME(INDEX($A$2:$A$13;VERGLEICH($D5;$B$2:$B$13;0));INDEX($A$2:$A$13;VERGLEICH($E5;$B$2:$B$13;0));INDEX($A$2:$A$13;VERGLEICH($F5;$B$2:$B$13;0));INDEX($A$2:$A$13;VERGLEICH($G5;$B$2:$B$13;0));INDEX($A$2:$A$13;VERGLEICH($H5;$B$2:$B$13;0)))
Das lässt sich über eine Matrix wohl auch eleganter lösen, aber wenn es bei den fünf Werten bleibt, sollte obige Formel ausreichen.
Klappt es?
Da habe ich mir vorhin auch schon die Zähne dran ausgebissen, aber jetzt hast Du mich nochmal angespornt und ich denke, ich habe es doch noch hinbekommen, allerdings - wie mein Vater immer sagte - "von hinten durch die Brust ins Herz":
{=WENN(VERGLEICH(D5:H5;B1:B13;0);SUMME(INDIREKT("A"&VERGLEICH(D5:H5;B1:B13;0))))}
Ich hatte schon früher mal festgestellt, dass manche Funktionen, die sonst keine Matrix akzeptieren, wenn sie in ein WENN eingebunden werden, es doch tun. Deshalb die Idee mit dem WENN (und der Weg über INDIREKT), obwohl es eigentlich nicht wirklich logisch ist.
P.S.: Jetzt beim Erklären bin ich glatt noch auf eine Idee gekommen und siehe da, es klappt sogar so:
{=WENN(1=1;SUMME(INDIREKT("A"&VERGLEICH(D5:H5;B1:B13;0))))}
Nochmal DH!
Das sind auch solche Lösungen, die ins Excel-Kuriositätenkabinett gehören.
Da hast Du recht.
Übrigens, ETWAS kürzer geht es noch:
{=WENN(1;SUMME(INDIREKT("A"&VERGLEICH(D5:H5;B1:B13;0))))}
Mit dem WENN muss ich mir unbedingt merken.
Daraus kann man noch etwas Kürzeres entwickeln:
{=WENN(1;SUMME(INDIREKT("A"&D5:H5-12)))}
Richtig.
Aber wieder unter der Voraussetzung, dass die Liste so festgeschrieben bleibt.
So ganz verstehen wie du vorgehen willst, tu ich noch nicht, insbesondere nicht warum gerade minus 12 gerechnet werden soll, um zur Zeile zu gelangen...
Was du aber machen kannst, ist die Spalten a und b zu tauschen, so dass dein "Suchkriterium" in der Spalte A und die langen Zahlen in Spalte B ist.
Du kannst dann zum Beispiel um den zu D5 gehörigen Wert zu finden folgende Formel nutzen: =SVERWEIS(D5;A2:B13;2;FALSCH)
Viel Erfolg!
Falls ich dich richtig verstanden habe, und du einfach die Werte in A neben dem gesuchten Wert in B aufsummieren willst, ersparst du dir die Umrechnung mit den "-12":
in J5 kommt
=VERWEIS(D7;$B2:$B13;$A2:A13)+VERWEIS(E7;$B2:$B13;$A2:A13)+VERWEIS(F7;$B2:$B13;$A2:A13)+VERWEIS(G7;$B2:$B13;$A2:A13)
DH!
Aber es fehlt noch Spalte H:
=VERWEIS(D7;$B2:$B13;$A2:A13)+VERWEIS(E7;$B2:$B13;$A2:A13)+VERWEIS(F7;$B2:$B13;$A2:A13)+VERWEIS(G7;$B2:$B13;$A2:A13)+VERWEIS(H7;$B2:$B13;$A2:A13)
Oder als Code:
=VERWEIS(D7;$B2:$B13;$A2:A13)+VERWEIS(E7;$B2:$B13;$A2:A13)+VERWEIS(F7;$B2:$B13;$A2:A13)+VERWEIS(G7;$B2:$B13;$A2:A13)+VERWEIS(H7;$B2:$B13;$A2:A13)
DH! Diese Lösung wollte ich jetzt auch schreiben. Eigentlich wollte ich lieber eine Lösung à la
{=SUMME(INDEX(A$2:A$13;VERGLEICH(D5:H5;B$2:B$13;0)))}
schreiben, die aber leider nicht funktioniert. Schade eigentlich. Vielleicht kriegt das jemand hin? Denn grundsätzlich funktioniert ein Vektor als Suchkriterium im VERGLEICH schon. Denn
{=SUMME(VERGLEICH(D5:H5;B2:B13;0))}
würde in der Beispieltabelle das richtige Ergebnis 96 (20+20+20+20+16) ausgeben.