Clever-Excel-Forum

Hallo,

ich suche eine Formel, die mit die Zeilennummer zurückgibt, die in der ungeordneten Spalte C ="x" ist. Gibt es sie nicht, wird die Zeilennummer gesucht die in der ungeordneten Spalte A "x" enthält und in der ungeordneten Spalte B = y ist. Dazu habe ich mit =VERGLEICH("*"&E3&"*";A1:A10;0) gestartet und mit Index verbunden.... habe aber irgendwie das Gefühl, auf dem Holzweg zu sein, weil ich kein zuverlässige Ergebnis bekomme. Ich hänge mal eine Datei mit Beispieldaten und dem gewünschten Ergebnis an. 

Viele Grüße
Frank

Moin,

teste mal so:

=WENNFEHLER(VERGLEICH("x";C1:C10;0);WENN(ISTFEHLER(UND(VERGLEICH("x";E1:E10;0);VERGLEICH("y";F1:F10;0)));"kein Treffer";VERGLEICH("x";E1:E10;0)))

Ist ein ziemliches Formelmonster, aber etwas kürzeres ist mir gerdae nicht eingefallen.

Gruß
Regina

Hallo Frank,

z.B. mit folgender Formel in G3:


und diese nach unten kopieren.

Hi,

für meine Begriffe sollte das reichen:

Hallo,

zunächst einmal ganz herzlichen Dank für eure Hilfe. Die Formeln von Werner und Edgar funktionieren prima, die von Regina habe ich aber nicht für alle Beispiele zuverlässig zum laufen gebracht, obwohl ich sie wenigstens halbwegs verstanden habe. 

Wenn ihr noch Lust habt:
Damit ich vielleicht mal selber auf sowas komme, würde ich die Formeln gerne verstehen. Die Aggregat-Formel nimmt jede Zeilennummer und dividiert diese Zahl durch "C:C=E3". Normalerweise zerlege ich mir komplexere Formeln in ihre Teile, um sie zu verstehen. Das geht hier natürlich nicht weil es einen Überlauf-Fehler gibt. Ich gehe davon aus, dass dieser Ausdruck für jede Zeile einen Fehler gibt - außer für die, wo der Wert übereinstimmt. 
Dann bricht mein Versuch der "Dekonstruktion" allerdings zusammen. Beispielsweise ergibt =AGGREGAT(15;6;ZEILE(A:A)/SUCHEN(E3;A:A);1) in dem Beispiel den Wert 0,040816327. Der Ausdruck  SUCHEN(E3;A:A) müsste in Zeile 2 wahr sein - vermute ich, weil ich nicht weiß, wie man so etwas testen kann . 

Bei der Formel von Werner kann ich auch nicht richtig schrittweise testen. Ich verstehe sie so: FINDEN(E3;A$1:A$99;1)^0 ergibt bei jeder Fundstelle eine 1. Für diese wird dann durch die zweite Bedingung dividiert und über die Option 6 bleiben dann alle Fehler von Aggregat unbeachtet. Ist diese " Aggregat Methode" für Excel weniger rechenaufwändig als "wenn" Verschachtelungen und "Wennfehler"-Konstruktionen?

Viele Grüße
Frank

Hi,


verkürze mal die Bereich in den Formeln auf ca. 20 Zeilen und betrachte das dann mit der Formelauswertung. Außerdem solltest Du die Hilfe zu Aggregat durcharbeiten, dann wird manches klarer:

https://support.microsoft.com/de-de/office/aggregat-funktion-43b9278e-6aa7-4f17-92b6-e19993fa26df
https://excelhero.de/funktionen/excel-aggregat-funktion/
https://www.tabellenexperte.de/viele-funktionen-in-einer-aggregat/
https://www.office-kompetenz.de/aggregat-neue-maechtige-funktion-in-excel-2010/

Übrigens, SUCHEN(E3;A:A) funktioniert nicht. Es muß lauten (SUCHEN(E3;A:A)>0) bzw. ISTZAHL(SUCHEN(E3;A:A)). Damit wird ein Wert WAHR bzw 1 gebildet, durch den dividiert werden kann, ohne den Divisor zu verändern.  SUCHEN(E3;A:A)  ergibt z.B. 5, bei Zeile 2 ergibt die Division dann 0,4

Hallo Frank,

Edgar hatte mit seinem Formelvorschlag scheinbar eine Verkürzung meiner Formel eingestellt. Auch wenn das von ihm gut gemeint war, würde ich Dir, sorry Edgar, von dieser abraten, wie ich nachfolgend versuche zu erklären:

Zunächst "formelles": Es handelt sich bei derartigen Formeln wie von Edgar und mir aufgezeigt, grundsätzlich um Matrixformeln, die auch in älteren Excelversionen zwar ganz ohne den spezifischen Eingabeabschluss auskommen, aber eben trotzdem nicht ganz unerheblichen Ressourcenbedarf an Rechenkapazität bedürfen (dies nicht wegen der Funktion AGGREGAT() an sich, sondern vor allem wegen der in der Formel definierten Bedingungsprüfungen als Teilformeln). Dies vor allem dann, wenn in der Formel der gesamte Zeilenbereich als Auswertungsbereich definiert wird, wie es Edgar getan hat.

Man sollte grundsätzlich bei derartigen Formeln nur einen zumindest nicht viel größeren Auswertungsbereich vorgegeben, als wirklich max. zu erwartend erforderlich ist, wenn man keine "intelligente" Datentabelle auswerten kann. Eine "intelligente" Tabelle auszuwerten ist zu bevorzugen, weil eine Formel die auf eine solche bezugnehmend definiert wird, immer exakt genau den vorhandenen Datenbereich auswertet, weil die Formel dann  sich automatisch auch immer diesen sich evtl. auch nachträglich erweiterten Datenbereich automatisch anpasst.

Ich hatte meinen Auswertungsbereich in der Formel, für die (noch) nicht "intelligent" formatierte Datenliste, auf einen willkürlichen Datenbereich (bis Zeile 99) begrenzt. Diesen müsstest Du natürlich dann entsprechend anpassen, wenn ein paar mehr Datensätze auszuwerten sind. Oder eben man formatiert die auszuwertende Datenliste zuvor gleich in eine "intelligente" Tabelle und definiert die Formel wie geschrieben gleich entsprechend dafür.  (Bei evtl. notwendiger Massendatenauswertung empfiehlt sich dagegen zumindest alternativ eine ganz andere Auswertungsmethode. VBA oder Power Query)

Jedenfalls bedarf meine Formel wie auch eine auf für einen "intelligente" Tabelle definierte schon wesentlich weniger PC-Ressourcen als die Formel von Edgar. Bei dieser musst Du mit dem Hinweis "Überlauf" bzw. evtl. auch den Hinweis "... nicht genügend Ressourcen  vorhanden" rechnen, was man natürlich nicht haben möchte.

Und "inhaltlich" gesehen, liefert die Formel von Edgar für die vorhandenen Beispieldaten zwar ein Ergebnis, wie von Dir vorgegeben. Aber dieses entspricht nicht mehr Deiner verbalen Vorgaben, wenn die auszuwertenden Daten ein klein wenig anders vorliegen sollten/können. Das kannst Du leicht überprüfen. Wenn Du z.B. mal nur im vorliegenden Beispieldatensatz in B2 die vorhandene Zahl durch eine 12 ersetzt, gibt meine Formel noch wie von Dir beschrieben als Ergebnis eine korrekte 6 aus. Aber die Formel von Edgar eben eine 2. Was damit mE aber nicht Deiner beschriebenen Vorgabe entspräche. Deshalb mein eingangs beschriebener Ratschlag.

Nun zu Deiner Nachfrage. Du hast die Funktionsweise der AGGREGAT()-Formel prinzipiell schon richtig erkannt.
WENNFEHLER() ist, wie Du auch hier sehen kannst, auch und gerade bei AGGREGAT()-Formeln notwendig und unumgänglich und auch völlig unproblematisch. Anderenfalls bliebe nur Konstruktionen mit ISTFEHLER() für die Bedingungsprüfungen (egal ob mit SUCHEN() oder FINDEN() oder ...) und mit WENN()-Konstrukten. Diese bedürfen dann in älteren XL-Versionen zwingend einen Matrixformeleingabeabschluss, was quasi eine zusätzliche übergestülpte Funktionalität entspricht. Meine früher durchgeführten Vergleiche hatten mir gezeigt, dass die AGGREGAT()-Formeln meist performanter als vergleichbare Formelkonstrukte mit {} waren/sind.

Hallo Werner,

ich weiß, dass meine Formel eine Priorisierung von C="x" nicht durchführt. Ob das tatsächlich relevant ist, mag nur der TE entscheiden.
Was den, von Dir zu Recht erwähnten,  Rechenaufwand angeht, das dürfte m.E. bei den Dateien von Otto Normalverbraucher eher vernachlässigbar sein. Ansonsten geh ich davon aus, dass ein Fragsteller in der Lage ist, den Bereich auf seine Bedürfnisse zuzuschneiden.

Unter der Voraussetzung, dass C leer ist, wenn A&B zutreffen sollen, dann geht es auch so:

Hallo Edgar,

vielen Dank für deine Mühe und deine Erläuterungen. Ich muss mich in der Tat noch deutlich intensiver mit  Aggregat beschäftigen und werde mir die Quellen von dir dazu genauer anschauen. Die genannte Voraussetzung, dass C leer ist, wenn A&B zutreffen, ist tatsächlich korrekt - das hatte ich vergessen in meiner ursprünglichen Beschreibung zu erwähnen. Nun mache ich mich mal an den Versuch, deine letzte Formel zu verstehen. In der Tat ist es diese erstaunliche Verbindung von Hilfsfunktionen wie "($C$1:$C$10=E3)+($C$1:$C$10="")*.... " wo ich meine Zweifel habe, ob ich jemals auf so etwas kommen würde... . Das ist einfach eine andere Excel-Liga...

Viele Grüße
Frank

Hallo Werner,

ich bin wirklich beeindruckt, wieviel Mühe du dir hier gibst, deine genialen Lösungen auch noch zu erklären! Du hast mir da in mehreren Punkten ganz entscheidende Hinweise gegeben. In der Tat ist "Ressourcenbedarf an Rechenkapazität" ein ganz wichtiger Aspekt bei der betroffenen Datei. Sie ist bei einigen Operationen im Laufe der Zeit schon merklich langsamer geworden und ich suche deshalb immer die "ressourcensparenste Lösung". 

Aggregat kam mir schon immer wie eine Matrixformel ohne geschweifte Klammern vor, weil da im Hintergrund so viel gerechnet werden muss. Ich habe das Problem mit Index, Vergleich, Wenn, Istfehler ... versucht zu lösen, bin aber daran gescheitet. Und vermutlich ist ja so ein  =VERGLEICH("*"&E3&"*";A1:A10;0) als "Substring-Search" ebenfalls ein Ressourcenfresser. 

Mein Sheet greift auf ein anderes Sheet 2 (ca. 6700 Zeilen) zu, was permanent um ca. 1000 Zeilen pro Jahr wächst. Sie werden von Zeit zu Zeit einfach unten angefügt. Bereiche aus dem Beispiel wie $A$1:$A$10 sind in der Realität also "benannte Bereiche" in Sheet 2. Allerdings sind die ersten 6600 Zeilen für den Abgleich in meinem Sheet völlig irrelevant. Abgeglichen werden müssen also immer nur die letzten ca. 100 Zeilen. Ich habe nun nach deinem Hinweis die  "benannten Bereiche" entsprechend verkleinert und damit waren die Formeln schon deutlich schneller. Nun überlege ich, ob ich versuchen soll, die  "benannten Bereiche" über die INDEX-Funktion daraus dynamische Bereiche zu erstellen. Oder über INDIREKT einen Bereich zu erhalten, der mit die letzte benutzte Zeile -100 liefert... Aber das ist ein anderes Thema. 

Dein Beitrag hat mich auf die Idee gebracht, einen anderen blinden Fleck von mir (neben Aggregat und Matrixformeln) nämlich die  "intelligente" Datentabelle in Erwägung zu ziehen. Ich habe das bislang immer als weiteren Ressourcenfresser betrachtet, weil vieles im Hintergrund gerechnet werden muss. Aber die eingebaute Dynamik würde ev. den Ansatz mit dynamischen Bereichen obsolet machen. Andererseits müssen die Formeln aus meinem Sheet dann ja wieder die ganze Tabelle in Sheet 2 durchrechnen... . 

Bislang versuche ich Ressourcen auch dadurch zu sparen, dass ich alle Formeln in ihre Werte umwandle, sobald sich am Formelergebnis nie mehr etwas ändern kann und soll. In einer normalen Tabelle ist das kein Problem, bei einer  "intelligenten" Datentabelle gibt Excel mir aber immer einen Fehlerhinweis, den ich erst wegklicken muss. Und so gibt es viele "Automatismen" die mich bislang etwas abgeschreckt haben... . Wie würdest du denn die Tabelle in Sheet 2 möglichst ressourcenschonend machen, als  "intelligente" Datentabelle, über "dynamische Bereiche" oder ganz anders?
(nur wenn du noch Lust hast - die eigentliche Frage ist ja längst gelöst)

Und ja, du hattest Recht mit "den verbalen Vorgaben" dich ich wohl im Beispiel vergessen hatte. Es sollte tatsächlich die Auswertung von Spalte 3 die oberste Priorität haben. 

Viele Grüße
Frank