☰

tag-icon • tag-icon microsoft-excel worksheet-function

Ich beschäftige mich mit einer Excel-Datei ähnlich dieser (aus Datenschutzgründen geändert/vereinfacht):

Bildbeschreibung hier eingeben

Befehl # Aufgabe
Wurde im Rahmen dieses Auftrags                                                                       ein Auto gewaschen ?
1 Rezension Ja
1 Reifen wechseln Ja
1 Auto waschen Ja
2 Rezension NEIN
2 Reifen wechseln NEIN
2 Öl wechseln NEIN

AndersAufgabenkönnen die gleicheBefehl #.

Ich brauche eine Formel, um die dritte Spalte (gelb im Bild) mit "Ja" zu füllen, wenn einAufgabeist Teil einerBefehl #Dazu gehört dieAufgabe„Auto waschen“ oder geben Sie „Nein“ zurück, wenn dies nicht der Fall ist.

Ich habe online erfolglos nach einer Lösung gesucht.

Antwort1

Nachfolgend finden Sie eine Abfolge von 5 Formeln. Jede Formel basiert auf der/den vorhergehenden. Hoffentlich macht diese Vorgehensweise die späteren Formeln leichter verständlich.

[ Formel 1 von 5 ]   Grundformel

=IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "yes"),"no")
  1. WENNFunktion verwendetÜBEREINSTIMMENals sein logical_test.
    • ÜBEREINSTIMMENverwendet jede Bestellung nacheinander alssearch_key
    • Das rangeArgument wird ausgefüllt mit demFILTERFunktion zum Zurückgeben einer Liste von Aufträgen, bei denen die Aufgabe „Auto waschen“ lautet
  2. DerWENNDas Argument der Funktion value_if_trueist"Yes"
  3. value_if_falsewird weggelassen, da der logical_testverwendete nur zurückgeben kann TRUEoder#N/A
  4. WENNFEHLERersetzt Fehler durch"No"

[ Formel 1 von 5 ] Grundformel

[ Formel 2 von 5 ]   Leere Zeilen ignorieren

=IF(A2:A7<>0, IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "Yes"),"No"),"")
  1. Um leere Zeilen zu ignorieren,WENNFunktion wird mit value<>0als hinzugefügt logical_test. Sie gibt zurück:
    • TRUEfür jede Zelle, die eine von Null verschiedene Zahl sowie jede Textzeichenfolge enthält, auch solche, die Ziffern enthalten. zB.("0"<>0)=TRUE
    • FALSEfür die Zahl 0sowie leere Zellen, die Excel auf Null setzt. zB. wenn OR(ISBLANK(A1),A1=0)dann(A1<>0)=FALSE
  2. DerGrundformelwird verwendet alsvalue_if_true
  3. value_if_falseist eine leere Zeichenfolge "".
    Dieser Ansatz überspringt die Rückgabe "No"und verhindert gleichzeitig Zeilenversätze aufgrund von Lücken in den Daten.

[ Formel 2 von 5 ] Leere Zeilen ignorieren

[ Formel 3 von 5 ]   Formel in der Überschriftenzeile

Durch das Verschieben der Formel in die Überschriftenzeile kann möglicherweise ein Überschreiben verhindert werden.

=VSTACK("Wash on Order?",
  IF(A2:A7<>0, IFERROR(IF(
     MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
     "Yes"),"No"),""))
  1. VSTACKermöglicht das vertikale Stapeln von Arrays
  2. Das erste verwendete Array war der Titel"Wash on Order?"
  3. Das zweite Array ist die vorherige Formel.

[ Formel 3 von 5 ] Formel in der Überschriftenzeile

[ Formel 4 von 5 ]   LET Funktion hinzugefügt

WährendLASSENallein trägt nicht viel zur vorherigen Formel bei, wird aber in diesem Schritt angewendet, um die Transformation zu verdeutlichen. Wenn Formeln komplexer werden, wird die Nützlichkeit vonLASSENklarer werden.

=LET(rng,A2:B7,
   a,DROP(rng,,-1), b,DROP(rng,,1),
   VSTACK("Wash on Order?",
     IF(a<>0, IFERROR(IF(
       MATCH(a, FILTER(a, b="Wash car"), 0),
       "Yes"), "No"), "")))
  1. DerLASSENMit dieser Funktion können Werte und Formeln zur Wiederverwendung in Variablen gespeichert werden. Dies kann die Codewiederholung und die Formellänge reduzieren und auch komplexe Formeln leichter verständlich und handhabbar machen.
  2. Der Quellbereich wird in gespeichert rng.
  3. Spalte A aund Spalte B bwerden zurückgegeben mitFALLENum die unnötige Spalte zu entfernen.
  4. aund werden jetzt anstelle von und in der Formel bverwendet .A2:A7B2:B7

[ Formel 4 von 5 ] LET Funktion hinzugefügt

[ Formel 5 von 5 ]   Bereich automatisch dimensionieren

Ermöglicht das Wachstum der Daten, ohne dass die Formel geändert werden muss. Akzeptiert vollständige Spalten und passt dann das Array je nach Bedarf an die aufgefüllten Daten an. Der Wert vonLASSENwird vollständig angezeigt.

=LET(rng,A:B,  
  arr,DROP(FILTER(rng, ROW(rng)<=
    MAX((rng<>0)*(ROW(rng)))),1),
  a,DROP(arr,,-1), b,DROP(arr,,1),
  VSTACK("Wash on Order?",
    IF(a<>0, IFERROR(IF(MATCH(a,
      FILTER(a, b="Wash car"), 0),
      "yes"),"no"),"")))
  1. FILTERGibt ein Array arrder Zeilen zurück, deren Zeilennummer <=der letzten Zeile mit einer Bestell-ID entspricht.
  2. Die letzte Zeile wird berechnet durch AnwendenMAXzu einem Array von Zeilennummern, deren Zeilen auch einen Wert ungleich Null enthalten. Das Sternchen *ist der „und“-Operator:MAX(ROW(rng)*rng<>0)
  3. Dieses resultierende Array enthält die Überschrift (Zeile 1), die Aufträge sowie etwaige Lücken. Die Lücken werden eingefügt, um sicherzustellen, dass absichtliche oder versehentliche Lücken nicht dazu führen, dass die Ergebnisse vom ursprünglichen Bereich abweichen.
  4. DerFALLENFunktion wird verwendet, um die Überschriftenzeile zu entfernen. Alternativ kann dies erreicht werden durch Hinzufügen derFILTERZustandROW(rng)<>1

[ Formel 5 von 5 ] Bereich automatisch dimensionieren

Antwort2

Die folgende Lösung scheint die in Ihren Beispieldaten festgelegten Ziele zu erfüllen.

  1. CVerketten Sie in einer leeren Spalte für jede Zeile Adie BSpalten mit =concat(A2,B2)(in Zelle C2).

  2. Fügen Sie in eine leere Spalte Ddie folgende Formel ein, beginnend bei D2. Dies ist eine Matrixformel, Sie müssen also CTRL+SHIFT+ENTERgleichzeitig drücken, damit sie funktioniert.

    =IF(OR($A$1:$A$6=A1)*(OR($C$1:$C$6=CONCAT(A1,"Wash car"))),"Yes","No")

  3. D2In die verbleibenden Zeilen kopieren .

Wenn ich etwas übersehe (was wahrscheinlich ist, da mein Beispieldatensatz so begrenzt ist), lassen Sie es mich bitte wissen und ich werde versuchen, es zu korrigieren. Wenn diese Lösung Ihr Problem löst, markieren Sie sie bitte als Antwort.

verwandte Informationen