Frage von testestestest12, 53

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)

Expertenantwort
von Ninombre, Community-Experte für Excel, 33

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?

Antwort
von Gerste94, 39

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!

Antwort
von testestestest12, 22

Vielen Dank für die Anregungen. Mit nur noch wenigen Anpassungen von mir, kann ich es dann perfekt passend für meinen Zweck verwenden.

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 35

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)
Kommentar von Oubyi ,

DH!
Wenn der Aufbau so starr bleibt - und das wird in der Frage ja gesagt -  ist das natürlich effektiver als meine Lösung.

Antwort
von gfntom, 21

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)
Kommentar von Oubyi ,

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)
Expertenantwort
von Oubyi, Community-Experte für Excel, 30

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?

Kommentar von DeeDee07 ,

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.

Kommentar von Oubyi ,

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))))}

Kommentar von DeeDee07 ,

Nochmal DH!
Das sind auch solche Lösungen, die ins Excel-Kuriositätenkabinett gehören.

Kommentar von Oubyi ,

Da hast Du recht.
Übrigens, ETWAS kürzer geht es noch:

{=WENN(1;SUMME(INDIREKT("A"&VERGLEICH(D5:H5;B1:B13;0))))}

Kommentar von Suboptimierer ,

Mit dem WENN muss ich mir unbedingt merken.

Daraus kann man noch etwas Kürzeres entwickeln:

{=WENN(1;SUMME(INDIREKT("A"&D5:H5-12)))}
Kommentar von Oubyi ,

Richtig.
Aber wieder unter der Voraussetzung, dass die Liste so festgeschrieben bleibt.

Kommentar von Iamiam ,

sagenhaft!

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten