Signaler

Formule/fonction dynamique

Posez votre question Jean - Dernière réponse le 14 sept. 2017 à 15:11 par Vaucluse
Bonjour,

Comment faire pour modifier la fonction suivante de manière à ce que son champ de recherche soit dynamique?

=SUMPRODUCT((DATA!B4:B5=B6)*(DATA!C4:C5=D2)*(DATA!D4:D5))

Cette fonction va chercher dans la feuille DATA le contenu de la cellule de la colone D qui correspond aux contenus combinés des colones B et C, selon indications fournies sur la feuille active en B6 et D2.

Le problème, c'est qu'elle ne va chercher le contenu que dans D4 à D5 dans la feuille DATA. Bien sûr je pourrais lui dire d'aller chercher de D4 à D9000, pour qu'au fur et à mesure que j'ajoute des lignes celles-ci soient prises en compte, mais je me demande s'il n'y a pas moyen de lui dire de simplement aller regarder sur toute la colonne.

J'ai déjà essayé de lui dire d'aller regarder dans DATA!D:D mais ça ne marche pas.

Utile
+1
plus moins
Bonjour

Il te faut nommer tes plages de façon dynamique
Par exemple la plage B4:Bxx (attention B:B3 ne contient aucun nombre)
nom : plage1
formule : =DECALER(DATA!$B$4;0;0;NB(DATA!$B:$B);1)
et utiliser plage1, ... dans ta formule SOMMEPROD

DECALER > voir OFFSET pour la version anglaie

Cdlmnt
Donnez votre avis
Utile
+1
plus moins
Bonjour

vous parlez de l'onglet "formulas" qui sauf erreur n'existe pas dans votre fichier??

toutefois, votre formule ne peut pas fonctionner avec des champs de hauteurs différentes
conseil:
nommer les trois champs de la formule sur le même principe, avec strictement le même code DECALER pour les trois, en changeant simplement la cellule de départ D4 en B4 et C4 et utiliser ces noms dans la formule


Pour info: sur des Excel récents, SOMMEPROD arrive à fonctionner sur des colonnes complètes, mais pas sur les plus anciens
Dans tous les cas il est préférable de limiter les champs, la formule est "semi" matricielle et assez lourde sur le + de un million de lignes d'Excel aujourd'hui

crdlmnt

Jean- 14 sept. 2017 à 10:22
Bonjour Vaucluse et merci pour votre aide.

Pour "Formulas" je parlais de l'onglet du logiciel et non pas de ma feuille Excel, je n'ai pas pensé à préciser.

J'ai essayé votre technique d'utiliser des noms pour tous les champs mais je n'arrive malheureusement pas non plus à faire fonctionner la formule ainsi.

Et comme vous le soulignez justement, une formule qui étend la recherche sur toute la colone est assez lourde et je le vois quand je change de nom sur la fiche de salaire, le changement des données n'est pas immédiat, on se que ça mouline. C'est d'ailleurs pour ça que j'aurais souhaité avoir une formule qui s'adapte automatiquement aux nombre de lignes qui ont du contenu uniquement.
Répondre
Vaucluse 20342Messages postés lundi 23 juillet 2007Date d'inscription ContributeurStatut 19 septembre 2017 Dernière intervention - 14 sept. 2017 à 10:53
C'est bien ce qu'il faut faire, mais pour TOUS les champs dans la formule, avec un code hauteur identiques pour chacun des champs.(Un seul est nommé avec la fonction décaler)
ce n'est pas le cas dans la formule que vous affichez dans votre message (disparu?).
Mais ça devrait aussi fonctionner avec les lignes 4 à 10000 pour tous les champs

cela fonctionne très bien avec la feuille DATA dans votre fichier

pour info dans la feuille Fiche de salaire, vous pouvez allèger en B22 avec:
=MIN(12500;B21)
et en régle générale, mieux vaut alléger en faisant référence à la 1°cellule du tableau qui utilise les code SOMMEPROD B21 en l’occurrence) plutôt que de le répéter à tous les étages, ce qui demande à Excel de recalculer à chaque fois!

si autres problèmes, dites nous ce qui ne va pas dans votre fichier
crdlmnt
Répondre
Vaucluse 20342Messages postés lundi 23 juillet 2007Date d'inscription ContributeurStatut 19 septembre 2017 Dernière intervention - 14 sept. 2017 à 11:22
... et pour suivre et peut être expliquer
il ya dans votre fichier des problèmes que je n'arrive pas à comprendre
j'ai de temps en temps beaucoup de mal à écrire dans certaines cellules qui refusent les écritures
j'ai aussi du mal à effacer des entrées
etc.....
les formules refusent d'utiliser les noms que l'on enregsitre
C'est tout à fait anormal, je creuse un peu et je vous en reparle
à+ crdlmnt
Répondre
Vaucluse 20342Messages postés lundi 23 juillet 2007Date d'inscription ContributeurStatut 19 septembre 2017 Dernière intervention - 14 sept. 2017 à 15:11
Re... je n'arrive pas à voir ce qui'il se passe dans votre fichier pour qu'il réagisse comme ça, ce qui est certain c'est qu'il y a une énorme collection de sommeprod qui n'arrange pas les choses

je vous invite à revoir votre fiche salaire selon cette proposition, en utilisant la numérotation en A de la feuille DATA qui permet bien des simplifications:
en cellule hors champ, de cette feuille (par exemple en A1), cette formule matricielle:

=SIERREUR(INDEX(DATA!$A$4:$A$1000°;EQUIV(B6&D2;DATA!$B$4:$B$10000&DATA!$C$4:$C$10000;0))

attention matricielle a entrer avec la touche enter en maintenant les touches ctrl es shift enfoncées
elle s'affiche automatiquement entre accolades dans la barre de formule

cette formule vous ressortira le N° de ligne de la feuille DATA ou se trouvent les valeurs cherchées*vous pourrez donc ensuite utiliser RECHERCHEV avec $A$1
la colonne dans DATA!$A$4:$P$10000 sans refaire tous les calculs déjà établis en DATA.

ou même encore plus simplement:

=INDEX(DATA!$A$4:$P$10000;A1;N° de cdolonne à ressortir)

Ca devrait être nettement moins lourd

en prime sur la feuille DATA:

vous pouvez numéroter automatiquement les lignes de A4 à A10000 en plaçant une formule =SI(B4<>"";LIGNE(A1);"")
et en tirant vers le bas

et aussi: dans cette feuille:
ne compliquez pas les écritures avec des signes + inutiles dans les formules à conditions, ça fait de la lecture enp lus pour Excel
=+TAUX!$A$2*D4 peut s'écrire =TAUX!$A$2*D4
=SI(D5>12350;(D5-12350)*+TAUX!$E$2;0) peut s'écrire =SI(D5>12350;(D5-12350)*TAUX!$E$2;0)
etc...
et enfin simplifier un peu en P4
=SOMME(D4-E4-F4-G4-H4-I4-J4-K4-L4-M4-N4+O4)
peut s'écrire:
=D4+O4-SOMME(E4:M4)

bon courage

crdlmnt
Répondre
Donnez votre avis
Utile
+0
plus moins
Bonjour ccm81 et merci pour votre réponse!

J'ai essayé de faire comme vous m'indiquez mais je n'y arrive pas, pourriez-vous m'aider d'avantage ?

Ce que je fais :

- Je vais dans l'onglet Formulas (désolé j'ai excel en anglais) > Name Manager > New
- Je crée un nouveau nom comme suit
- Name : brut
- Scope : DATA
- Comment : -vide-
- Refers to : =OFFSET(DATA!$D$4;0;0;NB(DATA!$D:$D);1)
- une fois ceci fait, je remplace ma formule
=SUMPRODUCT((DATA!B4:B10000=B6)*(DATA!C4:C10000=D2)*(DATA!D4:D10000))
PAR
=SUMPRODUCT((DATA!B4:B10000=B6)*(DATA!C4:C10000=D2)*("brut"))

Mais Excel me renvoit une erreur #VALUE!

Est-ce que sur la base de ces informations vous auriez une idée du problème ?

Cordialement
Donnez votre avis
Utile
+0
plus moins
J'ai déposé mon fichier sur cjoint.com si vous souhaitez regarder

http://www.cjoint.com/c/GImpXm2mll5
Donnez votre avis

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !