Excel - Sverweis, Vergleich und Istzahl ohne KKLEINSTE kombinieren

Computer: Software & Programmierung Office Software
#1

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

Vielen Dank
BG

#2

Hallo biofunc

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!

image

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.

Vielleicht hilf Dir das. Grüsse Niclaus

#3

Hallo Niclaus,

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.

Kannst Du damit etwas anfangen?

VG
BG

#4

Hallo BG

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.

image

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.

Grüsse Niclaus

#5

Hallo Niclaus,

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.

Vielen Dank für Deine Unterstützung.

BG

#6

Hallo BG

Das hast Du auch schon vor zwei Tagen geschrieben. Ich habe damals schon gesagt und sage es noch einmal: Sag bitte, wie diese Formel heisst.

Ich habe Dich auch gefragt wegen VBA. Ich hasse es, wenn man meine Fragen nicht beantwortet!

Grüsse Niclaus

1 Like
#7

Hallo BG
Ich habe dieses „Projekt“ noch einmal angeschaut und eine weitere Lösung:

FirmenSort
FirmenSort952×556 70.4 KB

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:

FirmenVerzeichnis
Die Firmennamen müssen in beiden Tabellen in der gleichen Reihenfolge erscheinen. Ein Firmenname darf in den Tabellen jeweils nur einmal vorkommen.

Grüsse Niclaus

#8

Hallo Niclaus,

entschuldige meine verspätete Anwort; ich bin erst seit Montag aus dem Urlaub zurück.

Dein letzter Vorschlag funktioniert hervorragend.
Die Erstellung der zusätzlichen Tabelle ist kein Problem gewesen.

Vielen Dank - auch für Deine Geduld und das Dranbleiben.

BG