Hallo,
ich habe in der Spalte „A“ Text, Zahlen und leere Zellen.
Ich benötige eine Formel, die mir im Bereich A1:A20 die erste, zweite, dritte usw. Zahl sucht und mir dann den entsprechenden Wert aus dem Bereich B1:B20 in der Spalte C ausgibt.
Die Reihenfolge der Treffer in Spalte A soll aber beibehalten werden (also keine Sortierung mit KGRÖSSTE oder KKLEINSTE)
A______B_____ C
20____Text1___Text1
15____Text2___Text2
Text__________Text3
25____Text3___Text4
Text
Leere Zelle
16____Text4
Ich habe da etwas. Allerdings komme ich dabei nicht ohne Hilfsspalte (HiSp) aus! – Diese HiSp kann man ausblenden, wenn sie stört. Das ist aber nicht empfehlenswert!
Die Formel in C2:
=WENN(ISTZAHL(A2);ZEILE();"")
Die Formel in D2:
=WENNFEHLER(INDIREKT("B"&INDEX($C$1:$C$8;KKLEINSTE($C$1:$C$8;ZEILE(C1))));"")
Diese Formeln kann man in beiden Spalten nach unten kopieren.
Entgegen Deinen Intentionen verwende ich KKLEINSTE(). Diese Formel bezieht sich hier nur auf die Zeilennummern in der Hilfsspalte. Sie bewirkt keine Sortierung des Inhalts der Ergebnis-Spalte.
vielen dank, dass ist schon ein erster Schritt und ich sehe, dass meine Angaben nicht vollständig waren. Die Tabelle hat ca. 1000 Zeilen. Es sind die Daten von 50 Unternehmen hinterlegt. A______B ______ C_________D________________E____________________F Firma1_________________„Firma1“___Zeile(Beginn Firma1)___Zeile(Ende_Firma1)
20____Text1___Text1
15____Text2___Text2
Text__________Text3
25____Text3___Text4
Text
Leere Zelle
16____Text4 Firma 2
15___Text1
usw.
Ich in der Zelle D1 wähle ich die Firma und berechne, in welchen Zeilen die Daten der entsprechenden Firma liegen (bei der Firma1 wäre das der Bereich von 2(Wert steht dann in E1) bis 7(Wert steht dann in F1)).
Mit Bereich.Verschieben wähle ich den Bereich von Firma1 ($A$2:$B$7) aus.
Nun möchte ich Innerhalb dieses Bereiches die erste, zweite, dritte …Zahl (Text und Leerzellen sollen ignoriert werden) suchen und den entsprechenden Wert der Spalte B (Sverweis) in den Spalten C1 bis C20 übernehmen.
Wähle ich die Firma2 (z.B. Bereich A8 bis B20), sollen die Werte dieser Firma ebenfalls in C1 bis C20 ausgegeben werden.
in der Zelle D1 wähle ich die Firma und berechne, in welchen Zeilen die Daten der entsprechenden Firma liegen
Kannst Du mir verraten, wie Du das berechnest?
Ich habe folgenden Vorschlag. Deine Tabelle muss dabei umgestaltet werden. Die Firma muss in einer eigenen Spalte aufgeführt werden (hier Spalte A) - und zwar in allen Zeilen.
Mit bedingter Formatierung kannst Du in Spalte A die Felder ausblenden, die (hier) nicht grün sind. Aber das kann heikel werden, wenn z. B. neue Zeilen eingefügt werden!
In E1 gibst Du den Firmennamen ein, den Du auswerten willst. - Die Formeln:
in D2
=WENN(UND(A2=$E$1;ISTZAHL(B2));ZEILE();"")
in E2
=WENNFEHLER(INDIREKT("C"&INDEX(D:D;KKLEINSTE(D:D;ZEILE(D1))));"")
Uebrigens: Hast Du auch schon an eine VBA-Lösung gedacht? So wie ich es sehe, ist das ohne grossen Aufwand machbar. Die Hilfsspalte und alle Formeln würden entfallen. Spalte A wäre auch da nötig. Ich glaube, es müssten aber nur die hier grünen Felder aufgeführt werden.
vielen Dank - genauso wie in Deinem Beispiel soll das Ergebnis aussehen; aber die Firma seht nur in einer Zeile und ich kann das nicht beinflussen.
Was mache ich: In Access habe ich die Firmen sowie in verschiedenen Tabellen deren Beteiligungen und Einrichtungen angelegt. In einem Bericht mit Unterberichten bringe sich die verschiedenen Teile zusammen und exportiere den Bericht nach Excel. Eine andere Tabelle bezieht sich auf diesen Export und erstellt pro Firma ein Unternehmensorganigramm mit dem add in von Visio. Das alles funktioniert hervorragend.
Ich kann mit einer Formel ja den Bereich bestimmen, in dem die eine Firma beginnt und in dem sie endet. Es müsste doch irgendwie möglich sein, innerhalb dieses Bereiches der erste Zahl, die zweit usw. zu bestimmten. Das Ergebenis soll dann so aussehen, wie in deinem Beispiel beschrieben.
Die Formeln in C2 und D2 kann man nach unten kopieren. - In E1 und F1 wird der Bereich bestimmt, in dem die eine Firma beginnt und in dem sie endet.
Das geht aber nur mit einer zusätzlichen Tabelle mit dem Namen „FirmenVerzeichnis“ in der gleichen Excel-Datei. Diese Tabelle enthält alle Firmen, die in der ersten Tabelle vorkommen:
Die Firmennamen müssen in beiden Tabellen in der gleichen Reihenfolge erscheinen. Ein Firmenname darf in den Tabellen jeweils nur einmal vorkommen.
