Fonction Decaler

[gnina1973]

Bjr

Dans mon fichier joint l'onglet Bdd Fournitures est ma base de données qui va s'enrichir au fil du temps ( Colonne A+B+C+D+E).
Sur l'onglet Nouveau Rapport la partie en jaune contient mes formules de recherche matricielles en fonction de mes critères choisies.
Mon pb est que je souhaiterais optimiser au maximum ces formules en creant des plages dynamiques , de facon à ce que ces formules tiennent comptent des plages dynamiques, en d'autres termes je ne sais pas comment conceptualiser la formule Decaler qui je connais mais qui est bcp trop abstrait dans mon esprit même avec un TUTO !!
Merci de m'aider mais surtout de m'expliquer clairement votre demarche

Bien à vous !!!

 

[mth]

Re : Fonction Decaler

Bonjour,

Pour la zone nommée "Type" en utilisant la fonction DECALER():

=DECALER('Bdd Fournitures'!$A$2;;;NBVAL('Bdd Fournitures'!$A:$A)-1)

'Bdd Fournitures'!$A$2 est la cellule de départ

Pas de décalage de ligne ni de colonne, au lieu de 'Bdd Fournitures'!$A$2;;; j'aurais pu écrire 'Bdd Fournitures'!$A$2;0;0;

Pour la hauteur: NBVAL('Bdd Fournitures'!$A:$A)-1, soit le nombre de cellules remplies en colonne A, moins 1 pour ne pas compter l'entête de la colonne.

La largeur de la zone par défaut est 1 donc je ne l'inscris pas, mais j'aurais pu écrire:
DECALER('Bdd Fournitures'!$A$2;;;NBVAL('Bdd Fournitures'!$A:$A)-1;1)

Tu peux appliquer le même raisonement pour tes autres zones.

Bien à toi,

mth

Edit: : Bonjour poulie 🙂
🙂 Edit 2 :Hello Hoerwind 🙂

 

[poulie]

Re : Fonction Decaler

Bonjour gnigna1973, Bonjour à tous,

Si j'ai bien compris tu souhaites nommer tes plages avec la fonction DECALER.
Vois cette proposition.
Je te laisse nommer la plage désignation.

Bonne réception

Un peu de retard, Bonjour mth

 

[hoerwind]

Re : Fonction Decaler

Bonjour, salut mth et poulie,

Vois la proposition sous la pièce jointe,

Les formules des plages nommées Type, Fournisseur et Designation ont été adaptées de sorte qu'elles fonctionnent de façon dynamique.
Une nouvelle plage prix a été nommée
En 'Nouveau rapport'!H:H: une variante de formule qui ne doit pas être validée par Ctrl+Maj+Entrée.

Petite astuce : pour ne pas commettre de fautes d'orthographe en saisissant le nom attribué à des plages nommées, la touche clavier F3 permet d'insérer les noms par simple clic.

 

[gnina1973]

Re : Fonction Decaler

Merci, une question pour Mth , qu'entends par largeur de la zone ?

de plus cela ne fonctionne pas , j'ai pourtant suivi je pense toute les instructions

merci

 

[mth]

Re : Fonction Decaler

re 🙂

la largeur de la zone est le nombre de colonnes, largeur 1 pour une colonne, 2 pour deux colonnes etc.

Bien à toi,

mth

 

[gnina1973]

Re : Fonction Decaler

Salut Mth

Merci, mais en revanche cela ne fonctionne pas , et je ne vois pas pourquoi ?

Voir fichier modifié dans mon dernier poste

 

[mth]

Re : Fonction Decaler

re,

Pour Fournisseurs tu as saisi NBV('Bdd Fournitures'!$A:$A)-1, c'est NBVAL('Bdd Fournitures'!$A:$A)-1
Pareil pour type

@ +

mth

 

[gnina1973]

Re : Fonction Decaler

OK c'est mieux comme ca en revanche dans ma formule :

=SI(D4="";"";INDEX('Bdd Fournitures'!$D$2:$D$39;EQUIV(A4&" "&B4&" "&D4;Type&" "&Fournisseur&" "&Designation;0)))

Si je saisis des informations suplémentaire apres la ligne 39 onglet Bld fournitures , je ne comprends ma formule se met à jour automatiquement , en fonction des plages dynamiques

merci

 

[mth]

Re : Fonction Decaler

re 🙂

Tu devrais rendre dynamique ta plage 'Bdd Fournitures'!$D$2:$D$39, si non tu peux-être à peu près sûre que la formule te renvoie un résultat erroné ou une valeur d'erreur.
(PS, j'espère que tu n'as pas de doublons dans ton fichier réel ...)

@ +

mth

 

[gnina1973]

Re : Fonction Decaler

Le pb concernant les valeurs érronnées renvoyées a été résolu grace à toi dans le mesure

ou j'ai ecrit NBV au lieu de NBVAL ....

Mon pb actuellement est que je pensais créer ces plages dynamiques de telle sorte que j'ai plu à changer ma formule :

=SI(D7="";"";INDEX('Bdd Fournitures'!$D$2:$D$39;EQUIV(A7&" "&B7&" "&D7;Type&" "&Fournisseur&" "&Designation;0)))

hors je me rends en compte lorsque de nouvelles info sont saisis en ligne 40 ma formule ci dessus ne s'actualise pas automatiquement

Je ne comprends plus rien ?

 

[hoerwind]

Re : Fonction Decaler

Re,

As-tu regarder le fichier que je t'ai proposé ?
Il faut également nommer de façon dynamique la colonne prix.
Ta formule deviendra donc :
=SI(D7="";"";INDEX(Prix;EQUIV(A7&" "&B7&" "&D7;Type&" "&Fournisseur&" "&Designation;0)))

 

[gnina1973]

Re : Fonction Decaler

Merci Hoerwin , desole j'ai un peu zappé ton post, maintenant je comprends mieux

Bien à vous tous

 

[Misange]

Re : Fonction Decaler

Bonjour

J'ai une proposition beaucoup plus simple puisque
1) tu as du mal avec la fonction décaler
2) tu as xl2007

Utilise la facilité de création de noms dynamique AUTOMATIQUE des tables.
Tu sélectionnes la première cellule en haut à gauche sur ta feuille bdd fournitures et dans l'onglet accueil tu cliques sur transformer en table. Tu vois un nouvel onglet qui apparait. Clique dedans et à gauche à la place de tableau1, tu nommes ta base par exemple MaBase
Fais la même chose pour ton nouveau rapport

Plutot que de longues explications incomplètes, je te renvoie pour plus de détails sur tout ce qu'on peut faire avec les tables sur cette page
Ce lien n'existe plus

4 énormes avantages pour toi (et tout le monde !) :
- quand tu tapes quelque chose juste sous la table, cela crée une ligne qui est automatiquement prise en compte.
- si il y a des formules dans les colonnes de la table, par défaut la nouvelle ligne recopie les formules de la ligne du dessus. Plus besoin donc de gérer des lignes vides avec des formules du genre si(D2<>"";...
- et surtout ... excel crée automatiquement des noms dynamiques. Mabase[type] fait référence à la première colonne de ta table et y incorpore toute nouvelle donnée.
- enfin avec la fonction décaler, si tu oublies et que tu tapes (ou quelqu'un d'autre) un truc dans la feuille dans une colonne contenant tes données, bonjour le bazar. Ici pas de problème et tu peux même mettre plusieurs tables les unes en dessous des autres.
Et un 5° avantage non négligeable, tu peux mettre une ligne de total automatique (tu peux choisir entre plusieurs types d'opérations à faire dans ce "total")
et un 6° en prime, les fonctions de tri sont automatiquement dispo sans avoir besoin de sélectionner tes données. Pas mal non ?

Du coup voilà les formules que je te propose pour trouver l'unité et le montant
Unité :
=INDEX(MaBase[Unité];EQUIV(1;(MaBase[Type]=[@Type])*(MaBase[Fournisseur]=[@Fournisseur])*(MaBase[Désignation]=[@Désignation]);0))
validation matricielle.

montant :
=SOMMEPROD((MaBase[Type]=[@Type])*(MaBase[Fournisseur]=[@Fournisseur])*(MaBase[Désignation]=[@Désignation])*MaBase[Prix U])

Comme tu as excel 2007 et moi 2010 dans le fichier attaché tu verras une petite différence de notation, la version 2010 ayant raccourci les références, @ remplaçant "cetteligne" que tu trouveras dans 2007.


Une fois que tu as touché aux tables crois moi tu ne peux plus t'en passer et tu t'évites bien du boulot fastidieux de création de noms.

 

[hoerwind]

Re : Fonction Decaler

Merci Misange,

Tu confirmes ce que je t'avais dit sur un fil précédent : il me reste encore beaucoup à apprendre !
Mon weekend commence bien grâce à toi.