Excel Fonction SI ou RECHERCHE, complexe
Résolu
SLBS
Messages postés
10
Date d'inscription
Statut
Membre
Dernière intervention
-
gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention -
gbinforme Messages postés 14946 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
Je suis en train de chercher à réaliser une opération un peu complexe sur Excel, et je commence à bloquer... Vous pourrez peut être m'aider.
Mon besoin est le suivant :
A partir d'une feuille excel dans laquelle les produits en stock sont unitairement détaillés en colone 1, et le distributeur possédant ce stock figure en colonne 2, d'un autre onglet excel précisant les produits en cours de livraison (que l'on intègre virtuellement au stock), j'ai besoin de savoir quels sont les modèles manquants par distributeur. Certains modèles ont plusieurs versions, mais cela ne m'importe pas : je veux qu'ils aient en stock un exemplaire de chaque modèle
Modèles = lettres (A,B,C,D), différentes versions = chiffres (1,2,3)
Voici dont mon document :
Onglet 1 : Stock
Modèle | Distributeur
A1 | Pierre
A2 | Pierre
A2 | Pierre
B | Pierre
B | Paul
D1 | Paul
D2 | Paul
D3 | Paul
C1 | Jacques
Onglet 2 : Commandes
Modèle | Distributeur
A1 | Pierre
D1 | Pierre
A2 | Paul
C1 | Paul
(Jacques n'a rien commandé)
Onglet 3 : Contrôle (Onglet du résultat souhaîté)
Distributeur | Modèle manquant
Pierre | C
Paul | OK (il a tous les modèles)
Jacques | A, B, D
Ici, j'ai donné le résultat pour l'exercice, mais en vrai, il me faut environ 1h pour le faire à la main alors qu'une formule doit exister.
J'attire l'attention sur les éléments de complexité :
- Il faut aller "chercher" dans les cases "modèles" la lettre du modèle, indépendamment du chiffre.
- Mon tableau de résultat n'a qu'une seule ligne par distributeur, alors que mes données initiales se présentent exemplaire par exemplaire.
- Je pense qu'il faudra des onglets intermédiaires, voire une matrice avec des 1 et des 0, même si je n'arrive pas à identifier comment faire.
D'avance, merci à celui ou celle qui se penchera sur le problème... :D
Cordialement,
SLBS
Je suis en train de chercher à réaliser une opération un peu complexe sur Excel, et je commence à bloquer... Vous pourrez peut être m'aider.
Mon besoin est le suivant :
A partir d'une feuille excel dans laquelle les produits en stock sont unitairement détaillés en colone 1, et le distributeur possédant ce stock figure en colonne 2, d'un autre onglet excel précisant les produits en cours de livraison (que l'on intègre virtuellement au stock), j'ai besoin de savoir quels sont les modèles manquants par distributeur. Certains modèles ont plusieurs versions, mais cela ne m'importe pas : je veux qu'ils aient en stock un exemplaire de chaque modèle
Modèles = lettres (A,B,C,D), différentes versions = chiffres (1,2,3)
Voici dont mon document :
Onglet 1 : Stock
Modèle | Distributeur
A1 | Pierre
A2 | Pierre
A2 | Pierre
B | Pierre
B | Paul
D1 | Paul
D2 | Paul
D3 | Paul
C1 | Jacques
Onglet 2 : Commandes
Modèle | Distributeur
A1 | Pierre
D1 | Pierre
A2 | Paul
C1 | Paul
(Jacques n'a rien commandé)
Onglet 3 : Contrôle (Onglet du résultat souhaîté)
Distributeur | Modèle manquant
Pierre | C
Paul | OK (il a tous les modèles)
Jacques | A, B, D
Ici, j'ai donné le résultat pour l'exercice, mais en vrai, il me faut environ 1h pour le faire à la main alors qu'une formule doit exister.
J'attire l'attention sur les éléments de complexité :
- Il faut aller "chercher" dans les cases "modèles" la lettre du modèle, indépendamment du chiffre.
- Mon tableau de résultat n'a qu'une seule ligne par distributeur, alors que mes données initiales se présentent exemplaire par exemplaire.
- Je pense qu'il faudra des onglets intermédiaires, voire une matrice avec des 1 et des 0, même si je n'arrive pas à identifier comment faire.
D'avance, merci à celui ou celle qui se penchera sur le problème... :D
Cordialement,
SLBS
A voir également:
- Excel Fonction SI ou RECHERCHE, complexe
- Fonction si et - Guide
- Liste déroulante excel - Guide
- Word et excel gratuit - Guide
- Recherche automatique des chaînes ne fonctionne pas - Guide
- Excel cellule couleur si condition texte - Guide
11 réponses
bonjour à tous,
Le contrôle par formule pourrait donner ceci :
http://www.cijoint.fr/cjlink.php?file=cj201101/cijD57xOVo.xls
Il faut saisir en ligne 1 les modèles à controler.
Ps : les fonctionnalités excel-starter
Le contrôle par formule pourrait donner ceci :
http://www.cijoint.fr/cjlink.php?file=cj201101/cijD57xOVo.xls
Il faut saisir en ligne 1 les modèles à controler.
Ps : les fonctionnalités excel-starter
Bonjour SLBS,
Je constate que gbinforme (salutations) est plus rapide que moi et comme j'ai la même proposition je me retire du poste.
Je constate que gbinforme (salutations) est plus rapide que moi et comme j'ai la même proposition je me retire du poste.
Bonjour,
Se serait plus simple avec l'exemple de votre fichier sur https://www.cjoint.com/ et poster le lien.
Se serait plus simple avec l'exemple de votre fichier sur https://www.cjoint.com/ et poster le lien.
En effet, je ne savais pas comment vous montrer au mieux l'exemple.
(Bien entendu, je présente la chose sous la forme d'exemple pour des raisons de simplicité et de confidentialité)
Le voici :
https://www.cjoint.com/?0bmra96Qgfd
Merci :)
SLBS
(Bien entendu, je présente la chose sous la forme d'exemple pour des raisons de simplicité et de confidentialité)
Le voici :
https://www.cjoint.com/?0bmra96Qgfd
Merci :)
SLBS
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
J'ai une 20aine de modèles possible, mais je dois m'assurer de la présence de 8 modèles dans le véritable tableau
Certaines lignes de l'onglet 1 et 2 ne sont pas prises en compte dans la construction de mon résultat, mais je ne crois pas que ça porte à conséquence sur la formule. (Admettons qu'il puisse y avoir des modèles F et G, dont la présence en stock m'importe peu).
Merci pour votre aide.
SLBS
Certaines lignes de l'onglet 1 et 2 ne sont pas prises en compte dans la construction de mon résultat, mais je ne crois pas que ça porte à conséquence sur la formule. (Admettons qu'il puisse y avoir des modèles F et G, dont la présence en stock m'importe peu).
Merci pour votre aide.
SLBS
Bonsoir tout le monde,
Une proposition par macro (ça risque d'être chaud par formules...) :
SLBS.xls
Alt+F11 pour voir le code
Adapter les noms des feuilles si besoin.
Dans Contrôle!D2 saisir les modèles à contrôler, séparés par des , . Cette cellule doit être nommée Liste_modèles.
Cliquer sur 'Contrôler'
eric
Une proposition par macro (ça risque d'être chaud par formules...) :
SLBS.xls
Alt+F11 pour voir le code
Adapter les noms des feuilles si besoin.
Dans Contrôle!D2 saisir les modèles à contrôler, séparés par des , . Cette cellule doit être nommée Liste_modèles.
Cliquer sur 'Contrôler'
eric
Merci beaucoup... Mon pauvre PC est sous excel starter, donc sans macro, j'essaye demain depuis le PC d'un collègue (en espérant avoir rapidement mon propre PC pro... :D)
Affaire à suivre, et encore merci pour le temps passé :)
Affaire à suivre, et encore merci pour le temps passé :)
Bonjour SLBS,
En restant dans l'esprit de gbinforme, il faut remplacer l'argument [nb_car] de la fonction [Gauche] ici (1) par cette fonction [NBCAR(C$1)]. La formule devient :
Note: le nom du produit doit se trouver dans la liste de référence.
Salutations.
Le Pingou
En restant dans l'esprit de gbinforme, il faut remplacer l'argument [nb_car] de la fonction [Gauche] ici (1) par cette fonction [NBCAR(C$1)]. La formule devient :
=SI(SOMMEPROD((GAUCHE(stock!$A$1:$A$23;NBCAR(D$1))=D$1)*(stock!$B$1:$B$23=$A2)*1)+SOMMEPROD((GAUCHE(commandé!$A$1:$A$23;NBCAR(D$1))=D$1)*(commandé!$B$1:$B$23=$A2)*1)=0;D$1;"")
Note: le nom du produit doit se trouver dans la liste de référence.
Salutations.
Le Pingou
Bonsoir,
Ce n'est plus la peine de tester ma macro car j'ai répondu à la question initiale :
Modèles = lettres (A,B,C,D)
Il n'était pas question de mots, c'est une tout autre question...
eric
Ce n'est plus la peine de tester ma macro car j'ai répondu à la question initiale :
Modèles = lettres (A,B,C,D)
Il n'était pas question de mots, c'est une tout autre question...
eric
En effet, c'est ma première demande d'aide de ce type, et je pensais que des A B et C étaient similaires pour excel à des "pommes de terres" ou des "fraises des bois" (j'avais fait attention à ne pas mettre de chiffres...)
Bon, j'ai bien regardé le document, et je n'ai pas réussi à le transposer dans mon document réel de reporting.
Je vais me creuser un peu les méninges, faire quelques recherche, et essayer de bien comprendre la formule.
Je reviendrai vers vous si je bloque complètement, mais je suis convaincu d'avoir désormais toutes les cartes en main pour tout comprendre, et en bonus, tout apprendre :)
Un grand merci à vous 3 !
SLBS
Bon, j'ai bien regardé le document, et je n'ai pas réussi à le transposer dans mon document réel de reporting.
Je vais me creuser un peu les méninges, faire quelques recherche, et essayer de bien comprendre la formule.
Je reviendrai vers vous si je bloque complètement, mais je suis convaincu d'avoir désormais toutes les cartes en main pour tout comprendre, et en bonus, tout apprendre :)
Un grand merci à vous 3 !
SLBS
je n'ai pas réussi à le transposer dans mon document réel de reporting.
tu as sans doute une structure différente ce qui est toujours problématique : quand tu donnes un exemple il est préférable de partir de ton fichier en effaçant les données privées inutiles et en banalisant les quelques données concernées.
Comme je te l'ai mis j'ai nommé les 4 plages de recherche concernées avec une fonction DECALER pour être évolutives et si tu fais de même dans ton classeur, tes formules fonctionneront sans les changer.
tu as sans doute une structure différente ce qui est toujours problématique : quand tu donnes un exemple il est préférable de partir de ton fichier en effaçant les données privées inutiles et en banalisant les quelques données concernées.
Comme je te l'ai mis j'ai nommé les 4 plages de recherche concernées avec une fonction DECALER pour être évolutives et si tu fais de même dans ton classeur, tes formules fonctionneront sans les changer.
Je n'ai pas encore pu tester la macro d'Eriic, j'espère pouvoir e faire aujourd'hui.
Pour la solution de gbinforme confirmée par le pingou, j'ai testé sur le fichier fourni, et ça marche. J'ai essayé d'ajouter un Modèle G, et ça marche aussi. J'ai ajouté un distributeur Bob, en tirant les colones, et ça marche toujours.
Jusque là, tout va bien...
Mais si un de mes produits, au lieu de s'appeler A ou , s'appelle Flop, ça ne marche pas (ça fait une Flop :D). J'essayé avec un autre mot (j'ai pris un gros mot donc je ne le met pas là), mais ça ne marche toujours pas.
Donc faut-il nécessairement que ce soit une unique lettre ?
Par ailleurs, dans mon résultat, peut-on mettre un séparateur entre les modèles.
Admettons que mes produits soient des fruits, j'aurais des choses de ce type :
- Pomme Granny
- Pomme Golden
(Equivalent de A1 et A1 par ex)
- Pomme de terre (Pour B par exemple)
- Abricot
(Et ainsi de suite)
Donc je peux avoir des mots récurrents entre des produits totalement différents...
Promis, je regarde ASAP si la macro résout d'office des problèmes..
Merci à tous,
SLBS
SLBS quand tu fais un exemple mets des valeurs de structure de données similaires à celles que tu veux utiliser sinon comment veux-tu que le résultat soit conforme à tes attentes surtout en précisant que tu avais 20 modèles (< alphabet !).
J'ai rectifié le classeur pour remédier à tes critiques :
http://www.cijoint.fr/cjlink.php?file=cj201101/cijTd7HhUo.xls
Les plages de tests sont nommées et évolutives.
j'ai retravaillé le doc réel avec un ami à partir du document que vous avez fourni. Tout marche très très bien maintenant. Merci beaucoup, sans vous je serais encore en train de faire mon analyse des stocks "à la main" !
à la prochaine :)