problème de calcul sur excel mac 2011

[invite873be070]

Bonjour à tous, et tout d'abord je vous souhaite mes meilleurs voeux pour cette nouvelle année 2011.
Je suis assez novice dans le monde excel, mais j'avais toujours réussi à me débrouiller... mais là, je sèche... Je vais essayer de vous expliquer le problème que je n'arrive pas à résoudre : J'utilise deux listes déroulantes pour remplir deux cellules (E4 et E5), m'indiquant un point de départ et un point d'arrivée (relatifs à la colonne R), et une cellule pour additionner les valeurs comprises entre ces deux points, avec la combinaison de "sommeprod" et "recherchev". Cela fonctionne très bien pour les colonnes S,T,U, mais hélas pas pour V et W où elle me donne des résultats incohérents. J'ai un tableau qui débute en colonne R avec une liste allant de 0 à 70, puis en S,T,U, des valeurs correspondantes à chaque numéro de la liste R, et en V,W, des valeurs correspondantes à différents rapports entre S et U. Les valeurs en R,S,T,U sont toutes dans un ordre croissant, mais V,W, étant des rapports sont décroissant sur les 15 premières lignes et deviennent croissant jusqu'à la 70ième ligne.
ex: R6=1; S6=0; T6=3900; U6=1555,2; V6=moyenne(S6/U6)donc 0; W=(S7/U6)donc 4,12808642
R7=2; S7=6420; T7=4173; U7=3110,4; V7=moyenne(S7/U7) donc 2,06404321; U7=moyenne(S8/U7)donc 2,208397634

La formule utilisée est:
=sommeprod((plage1 à additionner>=critère1)*(plage1 à additionner<=critère2)*plage1 à additionner) soit :
=SOMMEPROD(($S$6:$S$75>=RECHER CHEV($E$4;$R$6:$S$75;2;FAUX))* ($S$6:$S$75<=RECHERCHEV($E$5;$ R$6:$S$75;2;FAUX))*$S$6:$S$75) ceci fonctionne très bien pour la somme des valeurs de la colonne S, comprises entre E4 et E5 (représentant 2 cellules de dans la colonne R).
=SOMMEPROD(($W$6:$W$75>=RECHER CHEV($E$4;$R$6:$W$75;6;FAUX))* ($W$6:$W$75<=RECHERCHEV($E$5;$ R$6:$W$75;6;FAUX))*$W$6:$W$75) ne fonctionne pas.
J'aimerais bien comprendre pourquoi cette formule ne fonctionne pas avec des valeurs décimales qui ne sont pas dans un ordre croissants (j'ai essayé la formule =Arrondi(--;5), puis en multipliant par 1000, mais sans succès). Je cherche à indiquer, dans une cellule, la somme des valeurs de la colonne U se trouvant entre le point de départ (déterminé par E4 et correspondant à une des cellules de R) et le point d'arrivée (E5...), soit retrouver 6,33649 quand j'entre 1 en E4 et 2 en E5. La sélection dans E pouvant être aléatoire comme 11 et 15, ou, 4 et 69.
Merci de m'apporter votre aide.
Désolé du pavé mais c'est pas facile d'expliquer, et encore merci à tous d'avoir lu...
-----

[JPL]

Merci de m'apporter votre aide.
Désolé du pavé mais c'est pas facile d'expliquer, et encore merci à tous d'avoir lu...

Et merci de faire un effort pour rendre la message lisible : quelques espaces auraient été bienvenus.

[invite873be070]

Désolé, j'y penserai à l'avenir

[invite873be070]

Voilà JPL, c'est chose faite.
Bonjour à tous, et tout d'abord je vous souhaite mes meilleurs voeux pour cette nouvelle année 2011.

Je suis assez novice dans le monde excel, mais j'avais toujours réussi à me débrouiller... mais là, je sèche... Je vais essayer de vous expliquer le problème que je n'arrive pas à résoudre : J'utilise deux listes déroulantes pour remplir deux cellules (E4 et E5), m'indiquant un point de départ et un point d'arrivée (relatifs à la colonne R), et une cellule pour additionner les valeurs comprises entre ces deux points, avec la combinaison de "sommeprod" et "recherchev". Cela fonctionne très bien pour les colonnes S,T,U, mais hélas pas pour V et W où elle me donne des résultats incohérents.

J'ai un tableau qui débute en colonne R avec une liste allant de 0 à 70, puis en S,T,U, des valeurs correspondantes à chaque numéro de la liste R, et en V,W, des valeurs correspondantes à différents rapports entre S et U. Les valeurs en R,S,T,U sont toutes dans un ordre croissant, mais V,W, étant des rapports sont décroissant sur les 15 premières lignes et deviennent croissant jusqu'à la 70ième ligne.

exemple:
R6=1; S6=0; T6=3900; U6=1555,2; V6=moyenne(S6/U6)donc 0; W=(S7/U6)donc 4,12808642
R7=2; S7=6420; T7=4173; U7=3110,4; V7=moyenne(S7/U7) donc 2,06404321; W7=moyenne(S8/U7)donc 2,208397634

La formule utilisée est:
=sommeprod((plage1 à additionner>=critère1)*(plage1 à additionner<=critère2)*plage1 à additionner)
soit :
=SOMMEPROD(($S$6:$S$75>=RECHER CHEV($E$4;$R$6:$S$75;2;FAUX))* ($S$6:$S$75<=RECHERCHEV($E$5;$ R$6:$S$75;2;FAUX))*$S$6:$S$75)
ceci fonctionne très bien pour la somme des valeurs de la colonne S, comprises entre E4 et E5 (représentant 2 cellules de dans la colonne R).

=SOMMEPROD(($W$6:$W$75>=RECHER CHEV($E$4;$R$6:$W$75;6;FAUX))* ($W$6:$W$75<=RECHERCHEV($E$5;$ R$6:$W$75;6;FAUX))*$W$6:$W$75) ne fonctionne pas.

J'aimerais bien comprendre pourquoi cette formule ne fonctionne pas avec des valeurs décimales qui ne sont pas dans un ordre croissants (j'ai essayé la formule =Arrondi(--;5), puis en multipliant par 1000, mais sans succès). Je cherche à indiquer, dans une cellule, la somme des valeurs de la colonne U se trouvant entre le point de départ (déterminé par E4 et correspondant à une des cellules de R) et le point d'arrivée (E5...), soit retrouver 6,33649 quand j'entre 1 en E4 et 2 en E5. La sélection dans E pouvant être aléatoire comme 11 et 15, ou, 4 et 69.
Merci de m'apporter votre aide.
Désolé du pavé mais c'est pas facile d'expliquer, et encore merci à tous d'avoir lu...
... quelque chose de plus lisible... j'espère...
Espérant surtout trouver de l'aide pour m'aiguiller sur la bonne formule ou une configuration permettant de résoudre ce problème.
Encore merci à tous.

[A voir en vidéo sur Futura]

[inviteccac9361]

Bonjour,

La fonction RECHERCHEV necessite d'avoir obligatoirement une liste triée par ordre croissant.
Donc cette fonction n'est probablement pas celle que tu devrais utiliser.

Pour ma part, je prefere programmer une fonction macro qui verifie le contenu d'une colonne en parcourant toutes les lignes (une ou plusieurs cellules à blanc definissant la fin de la liste)
Lorsque le contenu de la colonne 1 par exemple est identique au critere de recherche (prendre un ucase par precaution) je lis le resultat en colonne 2 de la même ligne et j'arrete la recherche.

Pour recuperer dans la variable s le contenu d'une cellule en enlevant les blancs et en mettant en majuscule (le critere de recherche etant aussi en majuscule) :

s=trim(ucase(thisworkbook.sheets("nom de ma feuille").cells(ligne, colonne)))

[inviteccac9361]

Petit rectificatif, il faut inverser colonnes et lignes.

s=trim(ucase(thisworkbook.sheets("nom de ma feuille").cells(colonne, ligne)))

[invite873be070]

merci de ces réponses, mais je suis novice et n'ai donc pas de base digne de ce nom. Il s'avère exacte que mon problème vient des valeurs désordonnées de la colonne W.
Je ne suis pas sûr de savoir utiliser correctement la macro, n'y connaissant rien du tout, et le langage a l'air plus compliqué...
Qu'est qu'un ucase ?
Comment faire une macro ?

Ensuite, S est une donnée, comme la colonne R. R qui sert de plage de recherche et c'est celle-ci qui peut être variable : E4 (correspond à un numéro de la colonne R) détermine le point de commencement de la plage, E5 (correspond à un numéro de la colonne R) détermine le point de cloture de la plage.
Je cherche à additionner les valeurs, comprises et délimitées par E4 et E5, contenues dans la colonne W (chaque cellule de W correspondant au rapport de chaque cellule de U/chaque cellule de S; ex: W20=moyenne(U20/S21). ), ce qui revient à choisir deux cellules de la colonne R et de faire additionner toutes les valeurs de la colonne W comprises entre ces deux cellules.
Exemple: le choix R1:R10 (déterminé par E4=R1 et E5=R10) correspond à la somme W1:W10.

[inviteccac9361]

ucase est une fonction qui demande en entrée une chaine et met en Majuscule, en anglais UpperCase.
Exemple:
ucase(poLy-morphe5) donne la valeur "POLY-MORPHE5"

Tu dois avoir la même dans les fonctions Excel comme le SOMMEPROD, mais avec le nom francisé, je n'ai pas Excel sous les yeux donc je pense que c'est la fonction 'Majuscule'

Une Macro, ethymologiqement c'est un regroupement de fonctions elementaires dans un même groupe d'excecution.
Une macro sous Excel, c'est un ensemble de lignes textes mises à la suite dans un listing.
Quand on demande à Excel d'Executer une macro il va lire le listing Macro en commencant à la premiere ligne.
Chaque fois qu'il reconnait la demande, il l'effectue. Donc à chaque fois qu'il voit une fonction, il l'execute.

Il est vrai que si tu n'as aucune notion de programmation, ce qu'est une variable et une boucle au minimum, ça va être difficile d'utiliser des macros.

Mais, bonne nouvelle, Microsoft a intégré un generateur automatique de macros sous Excel.
Tu le trouve quelque-part dans les menus. Creer une nouvelle macro quelque-chose comme ça. Ou même tu as des boutons dans la barre des menus que tu peut faire apparaitre pour "Enregistrer" une nouvelle macro.
Tout ce que tu fait à partir du moment ou l'enregistrement a commencé est écrit dans le listing. Tu peux ensuite l'editer et le modifier pour en faire un vrai programme.
Tu peux remplacer les ranges ($:$) par des variables ou constantes colonne, ligne par exemple. Faire avancer la colonne, aditionner, multiplier etc. C'est plus souple.

[invite873be070]

Merci de tes explications, je pense que la macro doit être très utile et plus simple à utiliser quand on sait comment faire (ce qui n'est pas mon cas), et je serais frillant d'apprendre à le faire pour me simplifier la vie...
Maintenant que j'ai trouver ce que je cherchais, il y a surement manière de développer...

Eurêka, j'ai enfin fini par trouver...

Quelques explications ne seront pas de trop...
Du fait que les valeurs de la colonne W étaient décroissante sur 15 lignes puis croissantes jusqu'à la fin, j'ai fait plusieurs essais pour trouver le bon résultat, et quelque soit la recherche proposée en E4 et E5, uniquement pour la partie décroissante, donc en me limitant à une recherche comprise entre 1 et 15.
1° étape: En limitant la rechercheV aux cellules correspondantes à toute la plage décroissante et en inversant les signes < et >, le résultat est correct.
D'où la formule:
=SOMMEPROD(($W$6:$W$20<=RECHER CHEV($E$4;$R$6:$W$20;6;FAUX))* ($W$6:$W$20>=RECHERCHEV($E$5;$ R$6:$W$20;6;FAUX))*$W$6:$W$20)

2° étape: Faire la même chose pour toute la plage croissante, et là, la formule initiale fonctionne très bien, juste en adaptant la plage de recherche.
D'où la formule:
=SOMMEPROD(($W$20:$W$47>=RECHE RCHEV($E$4;$R$20:$W$47;6;FAUX) )*($W$20:$W$47<=RECHERCHEV($E$ 5;$R$20:$W$47;6;FAUX))*$W$20:$ W$47)

Restait à combiner les deux pour répondre à une recherche comprise entre 1 et 30 par exemple. Et là, se fût très long et fastidieux pour un débutant comme moi...
J'ai fini par trouver comment additionner les deux formule, après un certain nombre d'essais:
En remplaçant "le résultat de recherche en E5" pour la plage décroissante et "le résultat de recherche en E4" pour la plage croissante, par la cellule commune à la liste décroissante et à la liste croissante (soit: W20). Et là, miracle, ça marche.
D'où la formule:
=SOMMEPROD(($W$6:$W$20<=RECHER CHEV($E$4;$R$6:$W$20;6;FAUX))* ($W$6:$W$20>=W20)*$W$6:$W$20)+ SOMMEPROD(($W$20:$W$47>W20)*($ W$20:$W$47<=RECHERCHEV($E$5;$R $20:$W$47;6;FAUX))*$W$20:$W$47 )

Mais je n'avais pas de réponse pour une recherche comprise de 16 à 30.
Plusieurs heures de lecture m'ont permis d'essayer une multitude de possibilités avant de tenter "les conditions" dans la formule, et en passant par: "la formule entrée contient une erreur", "parenthèse ouvrante ou fermante manquante", "#nom", "#valeur", "#N/A", "trop arguments ont été entrés pour cette fonction"... bref, de quoi perdre patience et péter les plombs... j'ai fini par trouver le bon ordre et tout ce qu'il fallait pour que le résultat, si durement recherché, soit correct.

D'où la formule (à rallonge, je dois l'admettre):
=SOMME(SI(E5<=15;SOMMEPROD(($W $6:$W$20<=RECHERCHEV($E$4;$R$6 :$W$20;6;FAUX))*($W$6:$W$20>=R ECHERCHEV($E$5;$R$6:$W$20;6;FA UX))*$W$6:$W$20);(SI(E4>=15;SO MMEPROD(($W$20:$W$45>=RECHERCH EV($E$4;$R$20:$W$45;6;FAUX))*( $W$20:$W$45<=RECHERCHEV($E$5;$ R$20:$W$45;6;FAUX))*$W$20:$W$4 5);(SOMMEPROD(($W$6:$W$20<=REC HERCHEV($E$4;$R$6:$W$20;6;FAUX ))*($W$6:$W$20>=W20)*$W$6:$W$2 0)+SOMMEPROD(($W$20:$W$47>W20) *($W$20:$W$47<=RECHERCHEV($E$5 ;$R$20:$W$47;6;FAUX))*$W$20:$W $47))))))

Il y a certainement plus simple, mais pour le moment, je m'en contenterait très bien... tant qu'elle fonctionne...
Merci à tous les participant de ce forum, car sans l'aide de vos réponses aux questions posés sur ce forum, cette solution ne me serait jamais venu à l'esprit ...
Un grand MERCI à tous.

[inviteccac9361]

Il a résisté Excel, mais tu as fini par le mettre au boutot
Franchement bien.
Et je pense que faire un programme c'est beaucoup plus simple que ce que tu vient de faire là. Donc à mon avis si tu te met un peu à la programmation VB Excel tu devrais y arriver assez facilement.

Tu pourras aussi poser tes questions en creant une nouvelle discussion quand tu t'y mettra.

Bien à toi.