XL 2013 RechercheV et Somme (SommeProd ?)

Celyne

XLDnaute Nouveau
Bonjour,

J'ai trouvé de nombreux posts sur une somme dans une RechercheV et la réponse est pratiquement toujours un SommeProd, sauf que je n'arrive pas à associer ce que je vois à ce que je recherche.

Disons ma liste de produits (exagéré ici bien entendu) dans la feuille "Articles"
Colonnes 1 (le nº), 2 (Le nom de l'article), 3 (le prix) et 4 (rabais accordé si paiement comptant) :
101 - A - 1$ - 0,50$
102 - B - 2$ - 1,00$
103 - C - 3$ - 1,50$
104 - D - 4$ - 2,00$

Ma feuille "Clients" détaille les achats (exemple : client no 5 a commandé les no 102 et no 104). La facture va donc chercher le numéro d'article commandé par le client (dans la feuille Clients) et le croise avec la feuille d'articles pour aller en chercher les détails.

Dans la feuille "Facture" j'ai donc :
Achats :
(premier article sur la ligne 20) : no 102 - Article B - 2$
(2e article sur la ligne 21) : no 104 - Article D - 4$
(3e article sur la ligne 22, etc.)

Jusqu'ici, tout fonctionne très bien.

Ce que je cherche, c'est de quelle façon je pourrais écrire sur la facture : Rabais de 3$ accordé sur paiement comptant.
Comme je sais qu'on ne peut faire une somme dans une recherche V, je n'arrive pas à trouver la formule qui dirait : Prend le nº d'article inscrit sur la ligne 20 ci-dessus, va dans la feuille "Articles" et cherche la 4e colonne de cet article. Fais ensuite la même chose avec la ligne 21, puis 22, et additionne les résultats.

Cette formule est sûrement possible mais rien à faire je ne trouve pas comment. Votre aide sera grandement appréciée et je vous en remercie à l'avance !

Celyne
 

Chris401

XLDnaute Accro
Re : RechercheV et Somme (SommeProd ?)

Bonjour

Peux-tu essayer :
Code:
=SOMMEPROD((NB.SI(A20:A25;Articles!A2:A50)>0)*Articles!D2:D50)
Adapte les plages
A20:A25 : Plage des codes dans Facture
Articles!A2:A50 :plage des codes dans Articles
Articles!D2:D50 :plage des remises dans Articles


Cordialement
Chris
 

JHA

XLDnaute Barbatruc
Re : RechercheV et Somme (SommeProd ?)

Bonjour à tous,

Avec ce que j'ai compris, voir fichier joint pour commentaires
Exemple avec la fonction index() en mode tableau, il suffit d'utiliser la touche tabulation en "C33" pour ajouter une ligne et les formules.

JHA
 

Pièces jointes

  • Comptant ou non.xlsx
    11.1 KB · Affichages: 40

job75

XLDnaute Barbatruc
Re : RechercheV et Somme (SommeProd ?)

Bonjour Celyne, Chris401, JHA, le forum,

En supposant que le tableau en feuille "Articles" ne dépasse pas la ligne 1000 et que la plage prévue pour les n° d'articles dans la facture soit A20:A40 :

Code:
="Rabais de "&TEXTE(SOMME((Articles!A2:A1000=TRANSPOSE(A20:A40))*Articles!D2:D1000);"0,00$")&" accordé sur paiement comptant"
C'est une formule matricielle à valider par Ctrl+Maj+Entrée.

Edit : bien entendu s'il n'y a jamais de décimales dans les rabais :

Code:
="Rabais de "&SOMME((Articles!A2:A1000=TRANSPOSE(A20:A40))*Articles!D2:D1000)&"$ accordé sur paiement comptant"

Bonne journée.
 
Dernière édition:

Celyne

XLDnaute Nouveau
Re : RechercheV et Somme (SommeProd ?)

Bonjour à tous et merci pour vos précieux conseils !

Job75, ta formule ne rapporte pas d'erreur sauf qu'elle reste à 0,00$ même si j'ajuste la plage de ma feuille Articles. Jusqu'à ce que je modifie la plage de ma feuille de facturation, soit A20:A28 plutôt que A20:A40 - alors ça devient $VALEUR.

JHA, j'ai malheureusement un bug qui m'empêche d'ouvrir tous les documents que je n'ai pas créés moi-même depuis l'installation de Windows 10. Ça saoule grave mais je n'ai pas le temps de m'en occuper. Je suis très déçue de ne pouvoir vérifier ton document et je m'en excuse.

Chris401, ta formule fonctionne très bien et elle retourne même les décimales!

Je suis désolée mais j'ai fait une erreur dans ma demande, puisque j'aurais dû penser au nombre d'articles (3e colonne dans la facture - le a20:a28, sinon c'est plutôt compliqué dans la feuille clients) mais j'avais oublié. Du coup, si j'ai 8 articles à 1$ de rabais, ça écrit 1$ alors que ça devrait écrire 8. Est-ce que c'est quelque chose qu'on peut ajuster dans ta formule, Chris401 ?

Merci encore. Heure du Québec, je reviendrai après quelques heures de sommeil.

Celyne
 

job75

XLDnaute Barbatruc
Re : RechercheV et Somme (SommeProd ?)

Re,

Par curiosité j'ai mesuré les durées d'exécutions des 2 solutions post #2 et post #5 :

Code:
="Rabais de "&SOMMEPROD((NB.SI(A20:A40;Articles!A2:A1000)>0)*Articles!D2:D1000)&"$ accordé sur paiement comptant"

="Rabais de "&SOMME((Articles!A2:A1000=TRANSPOSE(A20:A40))*Articles!D2:D1000)&"$ accordé sur paiement comptant"
Plages entièrement remplies, les durées sont les mêmes => 15 µs sur Win 8 - Excel 2013.

Edit : (post #6 de Celyne) je rappelle que les formules du post #5 sont matricielles.

Cliquez dans la barre de formule puis simultanément les 3 touches Ctrl + Maj (ou Shift) + Entrée.

A+
 
Dernière édition:

job75

XLDnaute Barbatruc
Re : RechercheV et Somme (SommeProd ?)

Re,

Job75, ta formule ne rapporte pas d'erreur sauf qu'elle reste à 0,00$ (...)

A condition que la formule soit bien validée matriciellement, cela se produit si des n° d'articles sont des valeurs textes, par exemple dans la feuille "Articles", et des nombres dans la facture (ou l'inverse).

Il suffit alors de les convertir en nombres en ajoutant 2 signes "-" dans la formule :

Code:
="Rabais de "&TEXTE(SOMME((-Articles!A2:A1000=-TRANSPOSE(A20:A40))*Articles!D2:D1000);"0,00$")&" accordé sur paiement comptant"

Il n'y a pas ce problème avec NB.SI (post #2).

A+
 

Celyne

XLDnaute Nouveau
Re : RechercheV et Somme (SommeProd ?)

Bonjour Job75, c'est la première fois que j'entends parler d'une formule matricielle. En vérifiant rapidement sur Office, je vois que le matriciel rapporte un résultat plus rapidement et peut aussi simplifier les calculs dans une formule et ça me semble très intéressant à étudier. Par contre je me demande si c'est à cause de mon problème de Windows 10, mais ça ne fonctionne toujours pas. J'ai cliqué dans la barre de formule pour faire Ctrl-Maj-Entrée (#valeurs) et j'ai aussi tenté avec un F2 mais c'est pareil.

Du coup, j'ai l'impression de passer à côté d'une formule très intéressante et qui pourrait m'être utile à plusieurs autres choses... mais je ne vois pas de solution immédiate.

Il ne reste plus qu'à insérer le nombre d'articles pour le multiplier avec le rabais, est-ce que c'est quelque chose qui est possible avec le sommeprod ?

Merci encore pour votre aide, c'est vraiment apprécié.

Celyne
 

JHA

XLDnaute Barbatruc
Re : RechercheV et Somme (SommeProd ?)

Bonjour à tous,

Peux tu essayer de valider avec Ctrl+la flèche du haut (au dessus de Ctrl) + entrée
Avant cela, tu te mets sur la cellule qui comporte la formule puis "F2" et ensuite valider par les 3 touches.

JHA
 

Celyne

XLDnaute Nouveau
Re : RechercheV et Somme (SommeProd ?)

Bonjour à tous,

Je ne constate aucune différence.

Mon clavier a plutôt cette configuration (Les raccourcis clavier de Windows - Tutoriels - Le site de DeD) alors j'ai tenté avec la flèche et avec celle du clavier numérique sans le "num lock".

Merci
 

Discussions similaires

Membres actuellement en ligne

Statistiques des forums

Discussions
311 725
Messages
2 081 941
Membres
101 847
dernier inscrit
Djigbenou