Actualice una lista de Excel dinámicamente sin usar scripts

Aug 15 2020

Estoy un poco atascado y me vendría bien un poco de ayuda.

Estoy tratando de mantener una lista en Excel actualizada dinámicamente, según las condiciones en otras celdas. Algo similar a (SI ambos valores de celda en A1 y A2 COINCIDEN con los valores de celda en A3 y A4, devuelve A5). Idealmente, me gustaría usar solo fórmulas y no scripts, pero no estoy seguro de si es posible.

Aquí hay un escenario. Gina (A3) tiene una cesta de la compra con 6 artículos (B3:B8): Limón, Pasta de dientes, Brownie, Cepillo para el cabello, Uvas, Sándwich –

Cesta de Gina

A Gina y sus amigas les gusta intercambiar cosas entre ellas, y cuando lo hacen, hacen un registro de cada intercambio (D3:F5) –

registro de operaciones

Lo que intento hacer es descubrir cómo actualizar la cesta de la compra de Gina cada vez que registra una nueva operación.

Y además, debe permitir intercambiar el mismo artículo dos veces (para diferentes artículos en cada ocasión) teniendo en cuenta solo la entrada más reciente intercambiada por ese artículo; en este caso, recibe una naranja en su cesta.

en este caso ella esta recibiendo una naranja en su canasta no, no un cupcake

Probé varias combinaciones de IF, AND, MATCH e INDEX con la segunda columna duplicada, o me quedé atascado en dependencias circulares, y no puedo llegar al fondo.

Aquí hay un enlace a la hoja de trabajo:https://docs.google.com/spreadsheets/d/17J-lX2V1Zs-K7WmsfruqcEJtmElM5rCQTeCLFh8FX1U/edit?usp=sharing

Si alguien tiene alguna idea de como solucionar esto, se lo agradeceria enormemente!

gracias jimmy

Respuestas

1 RajeshS Aug 16 2020 at 15:12

:: Advertencia ::

Dado que OP no necesita una solución basada en VBA MACRO, en estas circunstancias, la posibilidad que encontré es usar pocas fórmulas ARRAY (CSE) y formato condicional. Posiblemente, ese otro puede sugerir uno mejor.


Cómo funciona:

  • Los artículos comercializados y la lista de comestibles son TABLA.

Tan pronto como agregue o elimine un nombre o elemento de la TABLA, Excel actualizará automáticamente los datos relacionados.

  • Además de la cesta de la compra y los artículos intercambiados , también debe crear una lista de comestibles.

  • Tan pronto como alguien compre cualquier artículo, debe ACTUALIZAR la mesa del tendero .

Consulte la lista de artículos antiguos, son comprados por GINA y otros.

  • Cree una lista desplegable en la celda P17, actualmente solo tiene 3 nombres, puede agregar más.

Si tiene una gran lista de compradores, entonces, para obtener una lista única, puede usar cualquiera de las fórmulas.

  • Una fórmula de matriz (CSE) en la celda P28:

     {=IFERROR(INDEX($U$17:$U$30, MATCH(0, INDEX(COUNTIF($P$27:P27, $U$17:$U$30)+($U$17:$U$30=""), ), 0)), "")}
    
  • Para versiones superiores, puede usar este en P28:

    =UNIQUE(FILTER(U17:U30,U175:U30<>""))

Luego use la lista de compradores de uso único para el menú desplegable en la celda P17.

  • Ahora use esta fórmula de matriz (CSE) en la celda Q17:

    {=IFERROR(INDEX($T$17:$T$30, SMALL(IF(COUNTIF($P$17, $U$17:$U$30)*COUNTIF($U$17:$U$30,"<>"), ROW($T$17:$U$30)-MIN(ROW($T$17:$U$30))+1), ROW(A1)), COLUMN(A1)),"")}
    
  • Una fórmula de matriz (CSE) en la celda R17:

    {=IFERROR(INDEX($N$17:$N$22, SMALL(IF(COUNTIF($P$17, $L$17:$L$22)*COUNTIF($N$17:$N$22,"<>"), ROW($L$17:$N$22)-MIN(ROW($L$17:$N$22))+1), ROW(A1)), COLUMN(A1)),"")}
    
  • Aplique el formato condicional, utilizando la fórmula que se muestra a continuación como nueva regla, en P17: P25:

    =COUNTIF($N$17:$N$22,Q17)=0


nótese bien

  • Termine la fórmula de matriz (CSE) con Ctrl+Shift+Enter y complete la dirección requerida.

  • La lista de artículos nuevos muestra los artículos intercambiados (intercambiados).

  • La lista anterior tiene artículos en ROJO, son artículos sin intercambiar, ya que puede encontrar que GINA compró CINCO artículos (consulte la lista de Clientes en la Columna U) e intercambió solo DOS, son Aguacate y Cigarros.

  • Siga cambiando nombres en la celda P17, obtendrá resultados.

  • Ajuste las referencias de celda en la fórmula según sea necesario.