RechercheV en VBA avec PowerQuery
Bonjour à tous,
J'aurais une petite question à vous soumettre, étant plutôt habitué à Access je flanche sur une formule assez basique pour les utilisateurs réguliers d'Excel, il s'agit évidement de la fonction RechercheV.
Le souci est que la valeur à rechercher est contenu dans une table ODBC que j'ai lié à une de mes feuilles du classeur, savez-vous pourquoi la fonction RechercheV ne va pas me trouver la valeur voulue ? J''ai essayer la même formule avec une feuille "Classique" la pas de souci.
'EncourOA" est ma feuille contenant ma table réseau, Au passage je viens de découvrir que l'ont pouvez ajouter des tables sur Excel et les gerer avec PowerQuery
Voici ma formule :
=SIERREUR(RECHERCHEV($B6;'EncoursOA'!$A:$F;2;FAUX);"")
Ecce moi qui m'y prend mal ? Y-a t-il une solution avec le VBA ?J'ai essayé en mettant soit le nom de ma feuille nommé "Encours" soit en mettant le nom de ma table nommé "EncoursOA"
Dernière petite question, je trouve très jolie le jeu de couleur proposé par PowerQuery avec ce vert transparent ainsi que la police et mise en forme utilisé, savez-vous si l'ont peut reproduire la même chose sur nos feuilles ?
En vous remerciant d'avance pour vote aide
Cdt
Bonjour,
Votre formule me semble correcte, la matrice est soit votre feuille, soit le nom de la table générée par PQ, du moment que les valeurs cherchés correspondantes se trouvent dans la première colonne de la matrice, il n'y a pas d'incidence.
L'une des hypothèse que je vois serait les format de cellule. Si vous faites directement votre référence cherchée = la référence correspondante trouvée dans votre matrice, le test vous renvoi-t-il vrai ou faux ? Si c'est du format numérique il est possible qu'il y ai des conversions. A déterminer lequel est à convertir et en quoi, mais sans fichier ...
Pour les tables, il vous suffit de sélectionner votre plage et de la transformer en table structurée via les styles de tables ; Accueil > Styles > Mettre sous forme de tableau.
Cdlt,
Bonjour, merci pour votre retour, j'essai de voir ça des que possible et reviens vers vous 😊
Cordialement
Re,
Voici une copie de mon fichier Excel, avec une petite explication de la recherche à faire, en espérant que vous ayez accès à la table, sinon j'en ai fait une copie sur la feuil1
Cordialement,
Bonjour,
Comme présupposé les formats ne sont pas les mêmes. Dans votre feuille SDPM, les valeurs de la colonne B sont en format standard nombre. Si vous regardez dans la feuille Encours, les "nombres" sont alignés à gauche, donc en format texte, confirmé par la formule =CELLULE("type";B2269) qui renvoie la valeur l. De plus, vous ne pouvez pas utiliser dans ce format la RECHERCHEV. En effet la colonne de la valeur recherchée aurait dû être à gauche de votre matrice, donc les colonnes A et B de la feuille Encours permutées. Une RECHERCHEV se fait toujours dans le sens, valeur cherchée dans la première colonne de la matrice, renvoie d'une colonne à droite dans la matrice. Ici vous souhaitez partir de la colonne B où vous cherchez la valeur, et renvoyer la colonne A qui se situe à gauche.
Dans ce cas on utilise les combinaison des fonctions EQUIV qui permet de chercher une valeur dans une colonne et renvoyer son numéro de la première apparition, et la fonction INDEX qui permet de renvoyer une cellule à l'intersection d'un numéro de colonne, numéro de ligne, dans une matrice.
Donc le numéro de ligne est renvoyé par EQUIV couplé à la fonction TEXTE pour convertir B4 dans le bon format de la colonne de recherche soit du texte a 7 chiffres.
INDEX ne se limitant qu'à la colonne A donc matrice en 1D, il n'est pas nécessaire de spécifier le numéro de la colonne.
Ce qui donne au final en A4 :
=INDEX(EncoursOA[OrdreAchat];EQUIV(TEXTE(B4;"0000000");EncoursOA[ID_OF];0))Cdlt,
Bonjour,
Tout d'abord merci pour votre aide car je n'y serais pas arrivé seul
Je ne vous cache pas que la température de mon cerveau à légèrement augmenté lors de la lecture de votre message
Ecce normale qu'une fois la formule mise en place j'obtienne #N/A ? qui bien-sur se transforme en donnée lorsque je renseigne le champ n°ID
Ce n'est pas bien méchant mais tant qu'a faire si la cellule peut rester vide tant qu'elle ne contient pas de valeur ce serait cool, bien que ce soit juste esthétique.
Pour la mise en forme des tableaux je connaissait déjà l'astuce des tableaux mais c'est reproduire le même style que propose le PQ ou j'ai du mal, c'est un espèce de vert translucide assez jolie d'ailleurs,
Cordialement,
Oops,
Dernier souhait, ecce possible d'avoir le même résultat mais sans le "PZ" en début de texte ? C'est à dire garder uniquement les chiffres ?
Cordialement,
Bonjour,
Dans ce cas :
=SIERREUR(CNUM(DROITE(INDEX(EncoursOA[OrdreAchat];EQUIV(TEXTE(B4;"0000000");EncoursOA[ID_OF];0));6));"")Après vous pouvez faire la RECHERCHEV si les colonnes A et B sont inversées dans ce cas :
=SIERREUR(CNUM(DROITE(RECHERCHEV(TEXTE(B4;"0000000");EncoursOA[[ID_OF]:[OrdreAchat]];2;FAUX);6));"")Avec le SIERREUR on masque les #N/A, mais ça masque aussi les cas où la valeur n'est pas trouvée. A vous d'investiguer le #N/A par la suite.
Pour la couleur comme expliqué :
Cdlt,
Edit : Pour cette histoire de PZ, attention aux nombres de caractères, si il c'est tjs le même alors c'est OK, sinon il faudra adapter en fonction des différents cas qui se présentent.
Re,
Pour les deux dernières formules ca fonctionne au top !! ca correspond parfaitement à ma demande donc cool
Ensuite pour l'histoire de la PZ, oui c'est toujours identique donc peu de risque à ce niveau, si la formule se base sur les 6 derniers chiffres, alors je suis tranquille un bon bout de temps avant que ca passe à 7 chiffres et plus.
J'ai encore quelques modif et solution à trouver pour finaliser tout cela, car j'utilise un "RAZ" en VBA pour effacer tout le contenu des plages sélectionnées, du coup ca supprimerai aussi mes formules je suppose... a voir une prochaine fois si je peux éviter que ca efface les formules en même temps que les données.
Voici une photo de la table lorsque je l'ouvre sous POWERQUERY la mise en forme est assez différente de ma feuille excel.. c'est justement le modèle PQ que j'essai d'avoir, police et couleur.
Cordialement,
Bonjour,
Pour votre macro RAZ si vous souhaitez uniquement effacer les cellules sans formules et sans les en têtes :
Sub RAZ()
Worksheets("SDPM").UsedRange.Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
End SubPour ce qui est du style épuré PQ, il suffirait de trouver la défault font sur PQ sur internet, mais je passe mon tour car perso ça ne me choque pas, à tester peut être sur du Sergoe UI mais pas sûr, puis définir un format de table structurée personnalisé et l'appliquer.
Cdlt,
Merci je vais tester cela des que possible, par contre les cellules contenant les formules contienne également des données à effacer, justement ce sont ces même cellules qui contiennent maintenant nos données récupérer dans la table... il faudrait donc que le code supprime les données texte et ne touche pas au formule..
Cordialement,
Bonjour,
Je me permet de relancer ce sujet plutôt que d'en ouvrir d'autres, mais pour aller un peu plus loin j'aimerais trouver une solution au problème suivant:
Les formules fonctionnent parfaitement mais le souci se trouve maintenant sur Access, car étant donné que je lie mon classeur Excel à Access je me retrouve avec plein de ligne vide impossible à supprimer sur Access, pourtant elle sont vide (Sur Excel du moins), je me dis donc que malgré que ces cellules soit certes vide en donnée , elle contiennent des formules et ca doit surement être ca que Access détecte et me crée des lignes supplémentaire dans ma table.
Voyez-vous ou je veux en venir ? Du coup lorsque j'édite mes bordereau de livraison , lors du comptage du nombre de ligne Access me donne 99 lignes en résultat hors c'est faux.
Cordialement
Désole de polluer mon propre sujet
RETOUR SUR EXCEL avant de me faire kicker par un modo :/
j'aimerais maintenant apporter un peu de professionnalisme à mon éditeur de bordereau de la manière suivante ;
Actuellement, je crée autant de doublons que nécessaire pour déterminer le nombres de colis que compose une commande c'est à dire ..
Exemple , la commande XX1 comporte deux colis, alors dans les cellule A1 et A2 je met XX1 pour totaliser deux lignes soit deux colis.
J'aimerais plutôt crée en C3 la quantité à ajuster directement pour déterminer la quantité réel de colis, pour être honnête je suis capable de le faire seul mais j'aimerais compliqué un peu les choses en y ajoutant une barre de défilement, c'est super sympa et élégant mais bon le but est que cette barre se crée toute seule ou une astuce pour déterminer sur quelle cellule chaque barre de défilement doit agir sinon je vais y passer 4 heures a crée des barre de défilement pour chacun de mes tableau..
Quelqu'un aurais une astuce ou idée qui va dans le même sens que ma demande ?
Cordialement,