regroupement de données par référence

[papyone]

Bonjour,(.......message précedent modifié pour meilleur compréhension et modif fich joint)

1) sur le 1er feuillet j'ai un tableau par journée d'activité (au total il y en aura une 50 aine)avec des agences( 85 au total), leur N°, leur nom, le nom de participants (20 par session maxi)...
2) Ce tableau d'activité est saisi manuellement, il peut contenir + fois la même agence sur la même journée, mais avec des participants différents .
3)J'ai aussi une liste de référence (éxistante)avec ces agences classées par N° et par secteur (7 au total, une agence ne peut faire parti que d'un secteur)).
4) Je souhaite dans un second feuillet regrouper ces agences par secteurs avec les lignes complêtes de données qui leur sont attachées .
Je souhaite un traitement pasr formule, j'ai du mal à comprendre/traduire lorsque les macro sont livrées direct
Merci pour votre aide,
Bonne journée

 

[job75]

Re : regroupement de données par référence

Re,

Avant de poster ce nouveau fil, peut-être auriez-vous pu répondre sur le fil

https://www.excel-downloads.com/threads/regrouper-des-informations.104359/

A+

 

[Tibo]

Re : regroupement de données par référence

Bonjour,

Je rejoins le propos de job75, à savoir qu'il est fortement déconseillé de multiplier les fils pour un même problème.

Ca complique le suivi des réponses aussi bien pour toi que pour nous.

Cela dit, je te propose une solution à base de formules matricielles.

Voir dans le fichier joint.

Les formules matricielles sont à valider par CTRL + MAJ + ENTREE

Teste et dis nous

@+

 

[papyone]

Re : regroupement de données par référence

avec mes excuses mour le nouveau fil et tous mes remerciements pour vos réponses , je vais m'y mettre ce soir et vous tenir informé.Merci encore

 

[Monique]

Re : regroupement de données par référence

Bonjour,

Par formules encore
Formules matricielles (3 sur 6)
Plages nommées par formule
Si on veut : formules nommées si le fichier final est lourd ou lent.

 

[papyone]

Re : regroupement de données par référence

Merci à tous, audelà de l'aide que vous m'apportez pour mon job présent, j'apprécie vivement votre formation.
Bonne journée

 

[papyone]

Re : regroupement de données par référence

Bjr,je n'arrive pas à faire fonctionner le fichier de Monique, aprés y avoir apporté les modifs suivantes + questions :
Dans l'onglet "Liste" j'ai joint l'ensemble des points de vente (84) et modifié les plages concernées . J'ai modifié les plages en prenant le N° du point de vente (l'ortographe pouvant être modifié ou mal saisi) au lieu du nom
Dans le feuillet "Jour" j'ai saisie en test 2 journée de travail (il peut y avoir 40 participants maw par jour) je peux avoir jusqu'à 120 journées saisies par an.
A quoi correspond la plage nomée Hauteur?
Dans l'onglet "tableau",j'ai modifié les listes déroulantes en prenant les secteurs comme intitulé, comment les demultiplier jusqu'au nombre de (7) secteurs ?
Sur l'existant est ce que les trois tableaux que Monique a réalisé sont trois manières de faire différentes? ou fait partie de l'ensemble?
Merci de votre attention...j'ai encore bcp de chemin à faire pour comprendre....mais je ne désespère pas !!!

 

[Monique]

Re : regroupement de données par référence

Bonjour,

Dans l'onglet "Liste" j'ai joint l'ensemble des points de vente (84) et modifié les plages concernée . J'ai modifié les plages en prenant le N° du point de vente (l'orthographe pouvant être modifié ou mal saisi) au lieu du nom
C'est là que ça clochait
Dans le fichier précédent, ce sont les noms BA et non les n° BA qui étaient extraits par formule matricielle

Ce fichier-ci :
=INDEX(ColNoBa;PETITE.VALEUR(SI(NB.SI(INDIRECT(D$1);NoBa);LIGNE(Date));LIGNES(C$3:C3)))
Le précédent :
=INDEX(ColNomBa;PETITE.VALEUR(SI(NB.SI(INDIRECT(J$1);NomBa);LIGNE(Date));LIGNES(K$3:K3)))

Sont extraits par formule matricielle :
date, n° BA et nom du participant
Nom BA est renvoyé par Index Equiv, à partir des données de la feuille "Listes"
Montant et nombre sont calculés par SommeProd, à partir des données de la feuille "Jour"

A quoi correspond la plage nommée Hauteur?
Ce n'est pas une plage, c'est une formule nommée "Hauteur",
elle donne le n° de ligne de la dernière cellule saisie en A:A
=MAX(EQUIV("zz";Jour!$A:$A);EQUIV(9^9;$A:$A))
EQUIV("zz";$A:$A) pour le texte
EQUIV(9^9;$A:$A) pour les valeurs numériques

Sur l'existant est ce que les trois tableaux que Monique a réalisé sont trois manières de faire différentes? ou fait partie de l'ensemble?
C'était… à choisir !
Il te reste à choisir entre deux méthodes seulement, elles sont dans deux feuilles distinctes.

Dans l'onglet "tableau", j'ai modifié les listes déroulantes en prenant les secteurs comme intitulé, comment les demultiplier jusqu'au nombre de (7) secteurs ?
En faisant un copié-collé d'un des tableaux

Le temps de recalcul reste long, je me demande si ce n’est pas =MAX(EQUIV("zz";Jour!$A:$A);EQUIV(9^9;$A:$A)) qui fait ça

 

[papyone]

Re : regroupement de données par référence

Encore merci Monique, et les autres.., je continue l'apprentissage, mais la montagne est bien haute !!
demain je me remet sur ce job et vous tiens informé
Bonne soirée

 

[papyone]

Re : regroupement de données par référence

Bonjour Monique, Tibo, Job75
Tout fonctionne comme je le souhaite.Par contre pouvez vous m'indiquer la raison de l'utilisation dans une des formulesde la fonction PETITE.VALEUR en relation avec INDIRECT ainsi que celle de MAX avec EQUIV. J'ai tenté de comprendre via mon livre Excel mais sans succés. Pour le reste je crois avoir compris le fonctionnement, mais les formules matricielles restent encore confuses dans mon esprit...
Merci de vos aides et
Bonne journée

 

[Monique]

Re : regroupement de données par référence

Bonjour,

=INDIRECT(D$1) fait référence à la plage dont le nom est saisi en D1
Si on tape Antony en D1,
écrire NB.SI(INDIRECT(D$1);NoBa) équivaut à écrire NB.SI(Antony;NoBa)
Avantage : la même formule dans tous les tableaux
et, si on veut, un seul tableau en modifiant seulement le contenu de D1

Petite.Valeur parce qu'il y a plusieurs réponses
Cette partie renvoie le n° des lignes, la 1ère puis la 2ème puis la 3ème, etc
PETITE.VALEUR(SI(NB.SI(INDIRECT(D$1);NoBa);LIGNE(Date));1 puis 2 puis 3, etc)
LIGNES(E$3:E3) copié vers le bas donne 1 puis 2 puis 3, etc

L'utilisation de Max et Equiv, c'est pour nommer les plages
C'est pour avoir la longueur des plages à prendre en compte
Ne pas faire travailler la formule sur 10 000 lignes alors qu'une centaine suffit

EQUIV("zz";Jour!$A:$A) renvie la position de la dernière cellule contenant du texte
EQUIV(9^9;Jour!$A:$A) renvie la position de la dernière cellule contenant du texte
9^9, c'est un nombre quelconque mais très grand que la formule ne risque pas de trouver
par défaut, elle renvoie la position du dernier nombre de la plage
Idem pour le texte avec "zz"

MAX(EQUIV("zz";Jour!$A:$A);EQUIV(9^9;Jour!$A:$A)) renvoie la + grande de ces deux valeurs donc la hauteur de la plage à prendre en compte

Les plages sont nommées en utilisant cette valeur, nommée "Hauteur"
=DECALER(Jour!$D$3;;;Hauteur)
Decaler(Réf ; nb de lignes ; nb de colonnes ; Hauteur ; Largeur)
On peut l'écrire comme ça aussi
=DECALER(Jour!$D$3;0;0;Hauteur;1)