XL 2010 (Résolu)Transfère données de plusieurs lignes vers différentes colonnes

[AreYouBush]

Bonjour,

J'ai une extraction en export.XML journalière du programme SAP qui me donne la position administrative de chacun de mes personnels sous forme de ligne par personnel. Je souhaiterais les regrouper dans un tableau (autre classeur) sous forme de colonne en fonction de leur position. J'ai pensé utilisé la fonction Find mais je ne sais pas comment mettre ça en place.
Je joins un exemple qui aidera surement à mieux comprendre ma demande.

Merci d'avance!

 

[Iznogood1]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Bonjour,

je crains que tu aies oublier de joindre l'exemple

 

[AreYouBush]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Bonjour, pourtant je le vois bien en fichier attaché.

 

[Iznogood1]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Oops,
je ne l'avais pas vu.

donc en N1 :

{=SIERREUR(INDEX($B:$B;PETITE.VALEUR(SI(C2:C104="Présent";LIGNE(C2:C104);1000000000);LIGNE(N5)-4));"")}

Formule matricielle à valider par Maj+Entrée.
Puis étendre vers le bas.


En M1, remplace $B:$B pas $A:$A dans la formule
Pour la ces colonnes K & L, remplace "Présent" par "Absence"
Etc...

 

[AreYouBush]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Bonjour Iznogood1,

Merci beaucoup, ça fonctionne parfaitement. Juste une petite question pour ma culture personnelle, a quoi sert le "1000000000" et le -4 après le "LIGNE(N5)?

De plus j'ai essayé de modifier la fonction pour avoir une condition avec OU mais elle ne me renvois pas les bons personnels sauf le premier. Faut il s'y prendre autrement?

=SIERREUR(INDEX($B:$B;PETITE.VALEUR(SI(OU(C2:C$104="Présent";C2:C$104="Mission");LIGNE(C2:C$104);1000000000);LIGNE(N5)-4));"")

Encore merci, bonne journée.

 

[Iznogood1]

Re : Transfère données de plusieurs lignes vers différentes colonnes

La minute culturelle :

L'idée est de récupérer les n° de ligne des cellules répondant à mon critères, sinon de renvoyer un grand nombre.
Ensuite, il suffit de regarder les plus petites valeurs des lignes pour savoir où est l'information cherchée.

On utilise une fonction matricielle: manipule un "tableau de valeurs".

Décortiquons la:
SI(OU(C2:C$104 ="Présent";C2:C$104="Mission");LIGNE(C2:C$104);1000000000)

Si on a "Présent" ou "Mission", alors on renvoie le n° de la ligne, sinon on renvoie une gros nombre (1000000000...)
Imaginons le contenu de C2:C104 comme suit :
C2 résent
C3 :Mission
C4 :Absent
C5 :Absent
C6 :Mission
...

On obtient alors le tableau suivant :
2
3
1000000000
1000000000
6
...

Ensuite on utilise PETITE.VALEUR(<tableau de valeurs>,<rang>)
Ainsi, appliquée à mon tableau de ci-dessus j'ai
1ère petite valeur = 2
2éme petite valeur = 3
6éme petite valeur = 6
....

Il faut donc que je passe en paramètre les rangs 1,2,3,.... à la fonction petite.valeurs.
Je le fais par LIGNE(N5)-4
En N5 : LIGNE(N5)-4 j'obtiens 5-4 = 1
En N6 : LIGNE(N6)-6 j'obtiens 6-4 = 2
En N7 : LIGNE(N7)-7 j'obtiens 7-4 = 3

Il suffit ensuite d'aller lire le contenu de la colonne B, à la ligne renvoyée par la fonction.
Ce qui se fait par Index :
Index(B:B, Petite.valeur(<mon tableau>,1)) donne Index (B:B, 2), soit B2
Index(B:B, Petite.valeur(<mon tableau>,2)) donne Index (B:B, 3), soit B3
Index(B:B, Petite.valeur(<mon tableau>,3)) donne Index (B:B, 6), soit B6

Ce qui correspond bien à ce qui est en face de C2, C3 et C6, les cellules correspondant à mon critère initial.

 

[AreYouBush]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Ok je pense avoir compris dans les grandes lignes.

Ensuite on utilise PETITE.VALEUR(<tableau de valeurs>,<rang>)
Ainsi, appliquée à mon tableau de ci-dessus j'ai
1ère petite valeur = 2
2éme petite valeur = 3
6éme petite valeur = 6

le 6 n'est pas la 3éme petite valeur?


Ensuite:

Il faut donc que je passe en paramètre les rangs 1,2,3,.... à la fonction petite.valeurs.
Je le fais par LIGNE(N5)-4
En N5 : LIGNE(N5)-4 j'obtiens 5-4 = 1
En N6 : LIGNE(N6)-6 j'obtiens 6-4 = 2
En N7 : LIGNE(N7)-7 j'obtiens 7-4 = 3

La je comprend pas! ou alors le 4 devrais être un 5?

Et je pense que avec le OU je dois faire un truc de travers parce que ça ne me renvois pas ce que je souhaite.(voir fichier)

Merci encore pour ton temps!

Petite rectification 10/06/2016: j'ai modifié ma formule comme suis et ça fonctionne.

{=SIERREUR(INDEX($B:$B;PETITE.VALEUR(SI((C$2:C$104="Présent")+(C$2:C$104="Mission");LIGNE(C$2:C$104);1000000000);LIGNE(N5)-4));"")}

 

[Iznogood1]

Re : Transfère données de plusieurs lignes vers différentes colonnes

En effet, tu as bien corrigé les fautes de frappes.
De plus,j'avais oublié de mettre un C2:C104 en absolu ($C$2:$C$104).

Tu as bien compris et tu as fais un essai logique avec 2 critères (ou Présent ou Mission) et ... ça ne marche pas.
Je suis comme toi, ça devrait marcher, mais manifestement XL interprète mal ce OU dans la formule matricielle.
(idem pour des ET d'ailleurs).

Alors, soyons plus malin qu'Excel et balançons lui la formule (toujours matricielle, Maj + Entrée) :

{=SIERREUR(INDEX($B:$B;PETITE.VALEUR(($C$2:$C$104="Présent")*LIGNE($C$2:$C$104)+($C$2:$C$104="Mission")*LIGNE($C$2:$C$104);LIGNE(F2)-1+SOMMEPROD((($C$2:$C$104="Présent")*LIGNE($C$2:$C$104)+($C$2:$C$104="Mission")*LIGNE($C$2:$C$104)=0)*1)));"")}

Et toc, prends ça

En F2 dans mon exemple, sinon, modifie le Ligne(F2) par Ligne(<ta première cellule>)

Calcul détaillé dans le fichier ci-joint

 

[AreYouBush]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Merci!
Entre temps, j'avais trouvé une solution qui est celle-ci:

{=SIERREUR(INDEX($B:$B;PETITE.VALEUR(SI((C$2:C$104 ="Présent")+(C$2:C$104="Mission");LIGNE(C$2:C$104) ;1000000000);LIGNE(N5)-4));"")}

J'ai retiré la fonction OU et l'ai remplacé par le + qui a la même utilité.

Et ça m'a l'air de fonctionner. Je vais tout de même m'interesser à ta méthode histoire d'apprendre.

 

[Iznogood1]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Bravo,

c'est encore plus élégant !

 

[AreYouBush]

Re : Transfère données de plusieurs lignes vers différentes colonnes

Merci. J'ai saigné du nez pour trouver la solution .

Encore merci de m'avoir initié aux matricielles. C'est très instructif.

Bonne journée.