Mettre à jour une liste excel dynamiquement sans utiliser de scripts

Aug 15 2020

Je suis un peu coincé et j'aurais besoin d'aide.

J'essaie de garder une liste dans Excel mise à jour dynamiquement, en fonction des conditions dans d'autres cellules. Quelque chose dans le sens de (SI les deux valeurs de cellule dans A1 et A2 CORRESPONDENT aux valeurs de cellule dans A3 et A4, renvoient A5). J'aimerais idéalement utiliser uniquement des formules et aucun script, mais je ne sais pas si c'est possible.

Voici un scénario. Gina (A3) a un panier contenant 6 articles (B3:B8) : citron, dentifrice, brownie, brosse à cheveux, raisins, sandwich -

Le panier de Gina

Gina et ses amis aiment échanger des choses entre eux, et quand ils le font, ils enregistrent chaque échange (D3:F5) -

registre des échanges

Ce que j'essaie de faire, c'est de trouver comment mettre à jour le panier de Gina à chaque fois qu'elle enregistre une nouvelle transaction.

Et en plus, il doit permettre d'échanger le même article deux fois (pour des articles différents à chaque occasion) en ne comptabilisant que l'entrée la plus récente échangée pour cet article - dans ce cas, elle reçoit une orange dans son panier -

dans ce cas elle reçoit une orange dans son panier pas, pas un cupcake

J'ai essayé diverses combinaisons de IF, AND, MATCH et INDEX avec une deuxième colonne en double, ou je suis coincé dans des dépendances circulaires, et je n'arrive pas à aller au fond des choses.

Voici un lien vers la feuille de travail :https://docs.google.com/spreadsheets/d/17J-lX2V1Zs-K7WmsfruqcEJtmElM5rCQTeCLFh8FX1U/edit?usp=sharing

Si quelqu'un a des idées sur la façon de résoudre ce problème, je serais extrêmement reconnaissant!

Merci Jimmy

Réponses

1 RajeshS Aug 16 2020 at 15:12

:: Mise en garde ::

Étant donné que OP n'a pas besoin d'une solution basée sur VBA MACRO, dans ces circonstances, la possibilité que j'ai trouvée est d'utiliser quelques formules ARRAY (CSE) et le formatage conditionnel. Peut-être que l'autre peut suggérer un meilleur.


Comment ça fonctionne:

  • Les articles échangés et la liste de l'épicier sont TABLE.

Dès que vous ajoutez ou supprimez un nom ou un élément de TABLE, Excel mettra automatiquement à jour les données associées.

  • Outre le panier d'achat et les articles échangés , vous devez également créer une liste d'épicerie .

  • Dès que quelqu'un achète un article, vous devez METTRE À JOUR la table de l'épicier .

Consultez la liste des anciens articles, sont achetés par GINA et d'autres.

  • Créez une liste déroulante dans la cellule P17, n'a actuellement que 3 noms, vous pouvez en ajouter d'autres.

Si vous avez une énorme liste d'acheteurs, pour obtenir une liste unique, vous pouvez utiliser l'une ou l'autre des formules.

  • Une formule matricielle (CSE) dans la cellule P28 :

     {=IFERROR(INDEX($U$17:$U$30, MATCH(0, INDEX(COUNTIF($P$27:P27, $U$17:$U$30)+($U$17:$U$30=""), ), 0)), "")}
    
  • Pour les versions supérieures, vous pouvez utiliser celui-ci dans P28 :

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

Utilisez ensuite la liste des acheteurs à usage unique pour la liste déroulante dans la cellule P17.

  • Utilisez maintenant cette formule matricielle (CSE) dans la cellule 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)),"")}
    
  • Une formule matricielle (CSE) dans la cellule 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)),"")}
    
  • Appliquez la mise en forme conditionnelle, en utilisant la formule ci-dessous comme nouvelle règle, sur Q17 : Q25 :

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


N.-B.

  • Terminez la formule matricielle (CSE) avec Ctrl + Maj + Entrée et remplissez dans la direction requise.

  • La liste des nouveaux articles affiche les articles échangés (échanges).

  • L'ancienne liste a des articles en ROUGE, ce sont des articles non échangés, car vous pouvez trouver CINQ articles achetés par GINA (vérifiez la liste des clients dans la colonne U), et échangés seulement DEUX, sont des avocats et des cigares.

  • Continuez à changer les noms dans la cellule P17, vous obtenez des résultats.

  • Ajustez les références de cellule dans la formule selon vos besoins.