Macro pouvant calculer avec des variables!

[Provence Vintage]

Bonjour le Fil

si quelqu'un veut bien jeter un coup d'oeil,
ça coince!
merci à toutes et tous

 

[Excel-lent]

Re : Macro pouvant calculer avec des variables!

Bonsoir Provence Vintage,

En [D10] écrire la formule ci-dessous :

=SOMME.SI($C$2:$C$6;C10;$D$2:$D$6)/NB.SI($C$2:$C$6;C10)

Voici pour la version basique, mais l'idéal serait de nommer tes champs :
-> Catégories
-> Période 1
-> Période 2
-> Période 3

Ta formule sera plus facile à comprendre, mais surtout, s'adaptera plus facilement à l'agrandissement ou réduction de ton tableau en ligne 1 à 7.

Bonne soirée à toi.

Edition : les $ ont été placé pour que tu puisse récopier la formule vers la bas sans soucis!

 

[Compte Supprimé 979]

Re : Macro pouvant calculer avec des variables!

Bonsoir,

Excel-lent, tu as oublié la fameuse variable en C9 😕
correpondant, me semble t'il à la colonne B

Sinon voici le fichier modifié, si j'ai bien tout compris 😀

A+

 

[Provence Vintage]

Re : Macro pouvant calculer avec des variables!

Bonsoir Excel-Lent,

Merci d'avoir jeté un coup d'oeil!
Par contre la formule ne prend pas la variable C9!

 

[Excel-lent]

Re : Macro pouvant calculer avec des variables!

Re,

Mais sinon, si tu utilise le filtre automatique, à la place de la formule donné tout à l'heure, en [D10] tu peux écrire :

=SOUS.TOTAL(1;D2:D6)

Puis :
-> mettre en filtre automatique ligne 1
-> colonne C, filtrer pour n'avoir que les catégories "standard"
-> un fois le filtre mis, la fonction SOUS.TOTAL donnera le même résultat que mon précédent post.

Pourquoi?

Car la formule SOUS.TOTAL(1;...) fais la moyenne des lignes visibles (les autres étant masqué soit par le biais du filtre automatique et/ou par l'astuce : "format" / "lignes" / "masquer"

Par contre, si tu écris SOUS.TOTAL(4;...), il te donnera la valeur la plus élevé.

Évidement ce n'est que deux exemples parmi tant d'autre, je te laisse regarder l'aide en ligne d'Excel 😉

Bonne soirée

Edition 1 : Bonsoir BrunoM45. Oups, j'ai lu trop vite! Je voulais tellement lui donner une solution simple! Ben tant pis, il va devoir passer par SOMMEPROD ou formules matricielles! Laquelle as-tu proposé BrunoM45? Par curiosité, je vais aller voir ta pièce jointe!

Edition 2 : Provence Vintage, si tu utilise la solution filtre automatique, et que tu filtre la colonne B sur B1 au lieu de C9 et catégorie en C1, ma proposition SOUS.TOTAL fonctionnera comme tu le demandais (contrairement à ma première proposition).

 

[skoobi]

Re : Macro pouvant calculer avec des variables!

Bonsoir Provence Vintage,

formule à mettre en D10 et glisser dans les autres cellules du tableau:

=SI(NB.SI($C$2:$C$6;$C10)<>0;SOMMEPROD(($C$2:$C$6=$C10)*(D$2:D$6))/NB.SI($C$2:$C$6;$C10);"")

Edit: salut bruno 🙂, salut excel-lent 🙂, on a pas compris la même chose, wait and see...

 

[Provence Vintage]

Re : Macro pouvant calculer avec des variables!

Bonsoir BrunoM45, le Fil,

Très bon, ça à l'air de fonctionner!
Peux-tu m'expliquer un peu, que je puisse apprendre au passage!
Si avec Somme.prod
Merci d'avance,
je vais tenter d'adapter!

 

[ledzepfred]

Re : Macro pouvant calculer avec des variables!

bonsoir tout le monde

à la bourre comme toujours : pour prendre en compte C9 :

=SOMME.SI($B$2:$B$6;DROITE($C$9;NBCAR($C$9)-NBCAR("x pour "));D$2:D$6)/NB.SI($C$2:$C$6;$C10)

EDIT : à la bourre et beaucoup moins efficace que Bruno et Skoobi!!

a+

 

[Compte Supprimé 979]

Re : Macro pouvant calculer avec des variables!

Re,

=SI(SOMMEPROD(($B$2:$B6=CNUM(DROITE($C$9;2)))*($C$2:$C6=$C10)*1)=0;0;SOMMEPROD(($B$2:$B6=CNUM(DROITE($C$9;2)))*($C$2:$C6=$C10)*(D$2:D6))/SOMMEPROD(($B$2:$B6=CNUM(DROITE($C$9;2)))*($C$2:$C6=$C10)*1))

Comme pour calculer la moyenne je fait une division par lnombre de lignes correspondantes

Le SI() permet d'éviter d'avoir l'erreur : #DIV/0!
que je trouve particulièrement moche 😉

Ensuite SOMMEPROD() permet de faire un calcul matricielle
Pour chaque ligne la fonction renvoie 1 ou 0 selon si les critères correspondent ou non

CNUM(), permet de convertir un texte en nombre

Si on éclate la formulle
($B$2:$B6=CNUM(DROITE($C$9;2))
Renvoie 1 si la valeur de la ligne de la colonne B est égale à valeur de fin de ta cellule C9

($C$2:$C6=$C10)
Renvoie 1 si la valeur de la ligne de la colonne C est égale à valeur de la catégorie de la ligne 10

Ce qui fait pour la cellule C10 pour : x pour 2 et Catégorie : Standard
1 * 1 * (120 + 130) = 250
que je divise par le nombre de ligne correspondant au 2 critères

Voil, voilà 😉

Edit : Salut LedZep

 

[Provence Vintage]

Re : Macro pouvant calculer avec des variables!

BrunoM45, Skoobi, Ledzepfred,Excel-Lent, le fil

Il n'y a que la formule de BrunoM45 qui réagisse bien en effet, bravo à toi, tu dois être au moins informaticien^^!
Trève de plaisanteries, merci beaucoup pour tes explications, il va falloir que je les potasses un peu plus, pour pouvoir j'espère les digérer!

Crois tu qu'une macro puisse ingurgiter tout ceci également!
Je te joins de nouveau le fichier avec des annotations!
Si tu as un moment c sympa!
merci encore

 

[Compte Supprimé 979]

Re : Macro pouvant calculer avec des variables!

Salut Provence Vintage,

Il faudra nous donner un peu plus de détail STP
Car on ne sait pas comment tu veux procéder pour tes 2 totaux à payer

A+

 

[Provence Vintage]

Re : Macro pouvant calculer avec des variables!

Bonjour BunoM45,
Bonjour le Fil

En effet, c'était pas clair!
Merci à toi

 

[Provence Vintage]

Re : Macro pouvant calculer avec des variables!

Hello BrunoM45, salut le fil;

j'essaye de trouver la combinaison en la fonction MINI MAXI, La fonction RECHERCHEV pour le critère Texte; et il me manque la fonction qui me permet d'assembler le tout pour:
en fonction de trois critères:
une catégorie,
un nombres de personnes,
le prix: le moins cher, le plus cher,
me pondre en auto:
le nombre de chambres à acheter et le coût engandré
en fonction des différentes critères renseignés!

Le tout avec la contrainte qui est, si plus de dispo dans une catégorie, passer à celle supérieur, en fonction des mêmes critères....

 

[Provence Vintage]

Re : Macro pouvant calculer avec des variables!

Hello le fil,

y a t'il quelqu'un qui veut jeter un p'tit coup d'oeil svp
Merci

 

[Provence Vintage]

Re : Macro pouvant calculer avec des variables!

Au secours...
Je vais encore passer la nuit dessus!
merci à toutes et tous!
regardez le fil!