☰

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

Estoy tratando con un archivo de Excel similar a este (modificado/simplificado por motivos de privacidad):

ingrese la descripción de la imagen aquí

Orden # Tarea ¿Se lavó el coche como
parte de este pedido?                                                                      
1 Revisar Sí
1 cambiar llantas Sí
1 Lavado de coche Sí
2 Revisar No
2 cambiar llantas No
2 Cambio de aceite No

DiferenteTareaspuede compartir lo mismoOrden #.

Necesito una fórmula para completar la tercera columna (amarilla en la imagen) con "Sí" siTareaes parte de unOrden #eso incluye elTarea"Lavar el auto", o responder "No" si no es así.

He buscado una solución en línea sin éxito.

Respuesta1

A continuación se muestra una progresión de 5 fórmulas. Cada fórmula se basa en las que la precedieron. Esperemos que progresar de esta manera haga que los posteriores sean más fáciles de entender.

[ Fórmula 1 de 5 ]   Fórmula fundamental

=IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "yes"),"no")
  1. SIusos de la funciónFÓSFOROcomo su logical_test.
    • FÓSFOROutiliza cada orden por turno comosearch_key
    • El rangeargumento se completa utilizando elFILTRARfunción para devolver una lista de pedidos donde la tarea es "Lavar coche"
  2. ElSIEl argumento de la función value_if_truees."Yes"
  3. value_if_falsese omite ya que el logical_testusado solo puede devolverse TRUEo#N/A
  4. SI ERRORreemplaza los errores por"No"

[ Fórmula 1 de 5 ] Fórmula fundamental

[Fórmula 2 de 5]   Ignorar filas en blanco

=IF(A2:A7<>0, IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "Yes"),"No"),"")
  1. Para ignorar las filas en blanco, unSILa función se agrega value<>0como su logical_test. Volverá:
    • TRUEpara cualquier celda que contenga un número distinto de cero, así como cualquier cadena de texto, incluso aquellas que contengan dígitos. p.ej.("0"<>0)=TRUE
    • FALSEpara el número 0y las celdas vacías que Excel reduce a cero. p.ej. si OR(ISBLANK(A1),A1=0)entonces(A1<>0)=FALSE
  2. ElFórmula básicase utiliza como elvalue_if_true
  3. value_if_falsees una cadena vacía "".
    Este enfoque omite el retorno "No"y al mismo tiempo evita el desplazamiento de filas debido a espacios en los datos.

[Fórmula 2 de 5] Ignorar filas en blanco

[Fórmula 3 de 5]   Fórmula en la fila de encabezado

Mover la fórmula a la fila del encabezado puede evitar que se sobrescriba.

=VSTACK("Wash on Order?",
  IF(A2:A7<>0, IFERROR(IF(
     MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
     "Yes"),"No"),""))
  1. VSTACKpermite apilar matrices verticalmente
  2. La primera matriz utilizada fue el título."Wash on Order?"
  3. La segunda matriz es la fórmula anterior.

[Fórmula 3 de 5] Fórmula en la fila de encabezado

[Fórmula 4 de 5]   Función LET agregada

MientrasDEJARPor sí solo no aporta mucho a la fórmula anterior, se aplica en este paso para aclarar la transformación. A medida que las fórmulas se vuelven más complejas, la utilidad deDEJARvolverse más claro.

=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. ElDEJARLa función permite almacenar valores y fórmulas en variables para su reutilización. Esto puede reducir la repetición del código y la longitud de las fórmulas, y también puede hacer que las fórmulas complejas sean más fáciles de entender y administrar.
  2. El rango de origen se almacena en rng.
  3. La columna A ay la columna B bse devuelven usandoGOTApara enviar la columna innecesaria.
  4. ay bahora se utilizan en lugar de A2:A7y B2:B7en la fórmula.

[Fórmula 4 de 5] Función LET agregada

[Fórmula 5 de 5]   Dimensionar automáticamente el rango

Permite que los datos crezcan sin necesidad de modificar la fórmula. Acepta columnas completas y luego dimensiona la matriz según sea necesario en función de los datos completados. El valor deDEJARestá en plena exhibición.

=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. FILTRARdevuelve una matriz arrde filas cuyo número de fila es <=el de la última fila que contiene un ID de pedido.
  2. La última fila se calcula aplicandoMÁXIMOa una matriz de números de fila cuyas filas también contienen un valor distinto de cero. El asterisco *es el operador 'y':MAX(ROW(rng)*rng<>0)
  3. Esta matriz resultante incluye el encabezado (fila 1), los pedidos y los espacios en blanco. Los espacios se incluyen para garantizar que los espacios intencionados o accidentales no provoquen que los resultados se desvíen del rango original.
  4. ElGOTALa función se utiliza para eliminar la fila del encabezado. Alternativamente, esto se puede lograr agregando elFILTRARcondiciónROW(rng)<>1

[Fórmula 5 de 5] Dimensionar automáticamente el rango

Respuesta2

La siguiente solución parece cumplir los objetivos establecidos en los datos de muestra.

  1. En una columna en blanco C, concatene columnas Ay con (en la celda ) para cada fila.B=concat(A2,B2)C2

  2. En una columna en blanco D, agregue la siguiente fórmula comenzando en D2. Esta es una fórmula matricial, por lo que deberás presionar CTRL+SHIFT+ENTERal mismo tiempo para que funcione.

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

  3. Copie D2a las filas restantes.

Si estoy pasando por alto algo (lo cual creo que puedo estar haciendo ya que mi conjunto de datos de muestra es muy limitado), hágamelo saber e intentaré corregirlo. Si esta solución termina resolviendo su problema, márquela como respuesta.

información relacionada