Lister les valeurs unique de 2 colonnes sans macro
Bonjour,
je souhaite lister (en ligne ou en colonne) les valeurs uniques d´une colonne 1, puis ensuite en dessous ou a droite mettre toutes les valeurs de la colonne 2 (potentiellement des valeurs vide) correspondantes aux valeurs de cette colonne 1. J'ai récupérer un sujet sur le forum qui en traitait.
Le souci est que je ne comprend pas ses formules. Il y a des si sans instructions par la suite.. Une plage dynamique utilisé.. J'ai l'impression qu'il y a du supperflu, si quelqu'un pourrait m'expliquer ce que fait chaque formule (2 en fait), sa fait 4h que je planche.
SI(COLONNE(A:A)>$F$10;"";INDEX(cola;MIN(SI(NB.SI($D$11:D11;cola)=0;LIGNE(cola)-3))))
et
=SI(LIGNE(A1)>NB.SI(cola;E$11);"";INDEX(colb;PETITE.VALEUR(SI(cola=E$11;LIGNE(cola)-3;"");LIGNE(A1))))
Je joint le fichier pour être plus clair et le fichier sur lequel j'aimerais réaliser l'opération.
Des explications seraient la bienvenu!
ps : en sachant que maximum de toutes manière je ne prendrais pas de fichiers qui comporterais plus de 6 valeurs en colonne 1 et 6 autre valeurs en colonne 2 à celle associé à la valeur de la colonne 1
Yes, merci !
J'aurais aimé plus de détails mais merci. Je vais chercher fonction par fonction ce qu elle font.
les plages dynamique sont elles une necessité ? (pas de possibilité de prendre les 30 000 première lignes mais si certaine seront vide?-->la base est amené à changer régulièrement)
bonjour
1) pour 30 000 ça va mouliner 2 plombes
2) j'ai néanmoins modifié la formule qui nomme" étab" de maniere qu'elle soit toujours dynamique meme avec des vides
3) le mieux est de toujours nommer (ça ne coute rien, ça allege la saisie,ça limite les erreurs )
cordialement
bonjour
1) pour 30 000 ça va mouliner 2 plombes
en VB ce serait mieux 2) j'ai néanmoins modifié la formule qui nomme" étab" de maniere qu'elle soit toujours dynamique meme avec des vides
3) le mieux est de toujours nommer (ça ne coute rien, ça allege la saisie,ça limite les erreurs )
Kdurteste 2.xlsx
cordialement
Bonjour (Hello tulipe)
entièrement d'accord, avec bcp de lignes, ça va ramer..
et au fait , pourquoi pas de macro ? ta religion te l'interdit ?
ton tableau de base est il réellement comme celui présenté ? ...comme un doute là
P.
Re
Sur la plupart des dossiers j'ai moins de 500 lignes.. Donc je vais pas m'embeter avec du VBA, d'autant plus que déjà avec les formules excel je ne comprend pas tout à fait, alors en VBA n'en parle on pas.
Mais si je vois que c'est moins fiable que du vba (activer les cellules à chaque fois), je choisirais cette solution et ne manquerais pas de vous solliciter..
Mon fichier 2 bases ne ressemble pas à ces 2 colonnes, mais c'est uniquement sur elles que je souhaite réaliser l'opération, donc c'est pour sa que je n'ai transmis que cela..
Merci bcp!
Si je comprend bien cette formule
DECALER(BD!$D$5;;;EQUIV(9^9;BD!$D:$D;1)-4)
Equiv va me chercher la position de la valeur la plus élevée qui est inférieure ou égale à celle de 9^9. On retire 4 lignes à cette position. Puis on dit que de b5 au nombre qu'on a calculé, c'est notre plage.
Le problème c'est que établissement il s'agit de nom propre normalement, et pas de nombre comme je l'ai écrits dans l'exemple (j'espère que cela pose pas de pb, normalement non;j'ai tenté de rentrer un nom d'établissement et il était pris en compte). Mais ducou je me demande comment la formule fait pour trouver l'equivalence?
Pour être plus précis, il y a toujours une société au minimum. Et chaque société peut potentiellement avoir 1 ou plusieurs étblissement mais pas nécessairement.
Ducou j'ai tenté d'avoir une société sans établissement, et le tableau me renvoie 0 comme première valeur.
Suis je obligé de passé par cette formule pour forcé la valeur ""!
=SI(SIERREUR(INDEX(Etab;PETITE.VALEUR(SI(SIERREUR(EQUIV(F$7&Etab;société&Etab;0);"")=LIGNE(Etab)-4;LIGNE(Etab)-4;"");LIGNE($A1)));"")=0;"";SIERREUR(INDEX(Etab;PETITE.VALEUR(SI(SIERREUR(EQUIV(F$7&Etab;société&Etab;0);"")=LIGNE(Etab)-4;LIGNE(Etab)-4;"");LIGNE($A1)));""))
bonjour
dans ce cas tu nommes Etab avec
DECALER(BD!$D$5;;;SOMMEPROD(MAX((BD!D:D<>"")*LIGNE(BD!D:D)))-4)ainsi tu peut y mettre n'importe quoi meme des vides
cordialement
Si je comprend bien cette formule :
=SIERREUR(DECALER(dept;EQUIV(0;NB.SI($B$7:B$7;société);0););"")
On va chercher la position de la première valeur 0 dans la bd matrice crée par nb si selon le critère des élément présent dans la base société ; cela renvoi {0;0;0;0;;;;;0}. On va prendre la position du premier 0..
Ensuite sa se complique . Parce que logiquement en argument elle contient un nombre (la position précédemment calculé). Et on part de la position C4 plus la position calculé, qui renvoie la valeur de cette position. C'est sa ?
Et cette formule, peut on me l'expliquer?
SIERREUR(INDEX(Etab;PETITE.VALEUR(SI(SIERREUR(EQUIV(C$7&Etab;société&Etab;0);"")=LIGNE(Etab)-4;LIGNE(Etab)-4;"");LIGNE($A1)));"")
re
pour le Decaler qui trie sans doublon c'est +/- ça , tu noteras que la formule evolue de cases en cases ,c'est pour cela que l'on demarre d'une cel vide , et comme il n'y a pas de $ a un B dans le NB.si ;ce dernier deviendra C pour prendre en compte dans ce nouveau calcul le resultat du pécédent
Re,
Sa ne semble pas fonctionner, excel me dit que j'ai des références circulaires. En plus cela semble ralentir excel..
Ne vaudrais t il pas mieux explicité la colonne société (jamais de vides à part les cellules en bas de la BD), et l'appliquer ensuite à etablissement qui lui peut contenir des vide.
Sinon je considérerais que lorsque j'ai 0, j'ai vide en fait (je reprend ensuite ces valeurs dans un autre tableau qui lui utilise somme.si avec les valeurs recherché...)
re
pour faire
1) il faut imperativement commencer par extraire les sociétes sans double
la formule décaler marche très bien : les ref circulaire c'est parce tu as mal démarrer (il doit y avoir une cell vide en amont de la formule)
si il y a des cel vides dans la colonne des sociétés fait le savoir ,je modifierai le decaler ....
pour la suite du tablo
mais pense quand meme au VB de patrick
Re
Alors jai pas fais bcp de modif, juste celle ci (car etablissement peut comporter des cellules vides)
société=DECALER(BD!$C$5;;;nbval(BD!$C:$C)) 'les sociétés sont des nom (ainsi que les
établissement)
Etab=DECALER(société;;1;) 'le plus svt vide complétement ou partiellement
Mon seul souci est que j'ai des 0 dans mon tableau lorsque je supprime des établissements. Je voudrais que le tableau lorsqu'il ne rencontre pas d'etablissement renvoit la valeur vide.
Je pense qu'il faut modifier cette formule pour forcer à "" :
=SIERREUR(INDEX(Etab;PETITE.VALEUR(SI(SIERREUR(EQUIV(C$7&Etab;société&Etab;0);"")=LIGNE(Etab)-4;LIGNE(Etab)-4;"");LIGNE($A1)));"")
Quelqu'un a une idée?
Et sur les fichiers envoyé (même le modèle que j'ai envoyé), en bas à gauche sur le ruban, excel indique référence circulaire
Et effectivement, je devrais peut être passer par vba mais sa ca si les managers ne trouve pas le fichier assez performant
re
comme visiblement tu rames , je t'ai fais un truc plus simple
cordialement
Ok, je vais essayé de comprendre tous sa.
J'aurais aimé idéalement ne pas avoir à ajouter de colonnes supplémentaire (je dois tirer sur 30 000 lignes ducou). Je vais faire un mix des 3 fichiers , et espère arriver au résultats souhaité.
Merci beaucoup pour l'implication!