Suppression contenu cellule selon nom et empillage
bonjour,
sur le fichier exemple joint, 2 tableaux de données d'entrée
celui en (c) et celui en (h)
on ne doit pas modifier les cellules autour de ces tableau (d'où les chiffres qui encadrent)
le but est d'obtenir celui en (r) avec:
suppression du nom affiché en j4
empilage des noms avec suppression des cellules vides
ajout à la suite des noms du tableau (c)
si cela peut se faire sur une macro .....
merci à vous
Bonjour,
En M10 :
=SIERREUR(SI(LIGNE(1:1)<=NBVAL($H$10:$H$25)-NB.SI($H$10:$H$25;$J$4);INDEX($H$1:$H$25;PETITE.VALEUR(SI($H$10:$H$25<>"";SI($H$10:$H$25<>$J$4;LIGNE($H$10:$H$25);9^9);9^9);LIGNE(1:1)));INDEX($C$1:$C$25;PETITE.VALEUR(SI($C$10:$C$25<>"";LIGNE($C$10:$C$25);9^9);LIGNE(1:1)-(NBVAL($H$10:$H$25)-NB.SI($H$10:$H$25;$J$4)))));"")Formule matricielle (valider par Ctrl+Maj+Entrée)
Tirer vers le bas.
Pour se mettre en train
Cordialement
salut,
je vois qu'il y a du niveau de bon matin
merci, les 2 solutions me conviennent bien
à+ et bonne journée
salut,
je vois qu'il y a du niveau de bon matin
merci, les 2 solutions me conviennent bien
est ce que vous pouvez m'appliquer la solution matricielle sur le fichier suivant joint ?
sachant que cette fois:
y4 = mot à supprimer dans liste
("y7 à y34") remplace la liste en h du 1er fichier (plage entrée 1)
("e7 à e102") remplace la colonne en e du 1er ficher (plage entrée 2)
("x7 à x102) = remplace la colonne en m du 1er ficher (plage de sortie )
onglet ajustement qtés
merci beaucoup
S'il y a les équivalences que tu indiques, tu peux transposer ! Il n'y a alors qu'à modifier les références.
re...salut,
je viens de tenter de d'adapter cela à mon fichier, mais sans résultat
si jamais tu as un peu de temps, le voici.
la colonne de sortie est la x en jaune ("x7:x102")
je te remercie
J4 était à supprimer, donc remplacer par $Y$4 (3fois)
1re entrée en col. H vient en Y, donc remplacer H10:H25 par $Y$7:$Y$34 (7fois)
et remplacer H1:H25 par $Y$1:$Y$34 (1fois)
2e entrée n'était pas en E mais en C, elle vient en X, donc remplacer C10:C25 par $X$7:$X$102 (2fois)
et remplacer C1:C25 par $X$1:$X$102 (1fois)
Evidemment ne pas modifier LIGNE(1:1)
Cela donnera la formule de la première ligne (à valider en matricielle). Et à tirer pour la recopier sur le nombre de lignes voulues.
Cordialement.
salut,
je suis complètement désolé de t'avoir fait bosser sur ce fichier, je viens de voir que les conditions à respecter sont différentes !
même si le principe reste le même.
si je peux abuser un peu plus de ta patience, on va le voir directement sur le bon fichier et non un exemple.
en colonne y , plage ("y7:y34"), les données qui doivent aller en haut de ma colonne x, à partir de x7 avec les ligne vides supprimées.
en colonne z, plage ("z7:z102"), les données qui doivent aller sous ma 1ere liste copiée, mais en ayant supprimé la ligne contenant le mot "saccharose" (on remplace ce saccharose par les 3 types de sucre du 1er tableau )( en z16 en ce moment mais qui varie en fonction de la recette).
je donne le résultat voulu en colonne ak
encore merci si t'arrives à me dépatouiller de ça
Tu inverses tes 2 entrées. La suppression est maintenant à faire dans la seconde, et il n'y a plus de correspondance directe avec la ligne à ignorer.
On va attendre le prochain changement
Une piste : Pour la 1re matrice qui était la seconde, il n'y a qu'à la faire passer avant sans décalage de ligne (on va de 1 à x), la transposition devrait donc être aisée, on n'élimine que les lignes vides ; pour la seconde qui était la 1re, il faut chercher le mot proscrit, donc toujours double condition, lignes non vide et lignes renvoyant une erreur sur le mot cherché ; la condition pour faire se succéder les 2 entrées n'utilise plus NB.SI mais simplement le nombre de valeurs de la 1re entrée, condition pour basculer d'une entrée à l'autre et rétablir le rang des valeurs dans la 2e entrée. Elle est faite là ! Plus qu'à l'écrire !!
salut,
j'avais déjà du prendre ma respiration pour lire la 1ère formule jusqu'au bout, tellement elle était longue !
Sur ce coup là, tu enfonces le clou et je vois que l'on ne joue pas du tout dans la même division !
Je suis complètement largué et je ne maitrise pas suffisamment pour faire ça
je vais tenter de bidouiller quelque chose.... mais ?...
Construction pas à pas :
Définitions :
Entrée1 = la plage d'entrée n° 1
ColEntrée1 = (pour distinguer de la précédente) la plage Entrée1 étendue vers le haut pour la faire démarrer de la ligne 1 (on va en effet extraire le numéro de ligne des éléments à lister : pour que ce numéro de ligne corresponde à l'index, d'une fonction INDEX, la plage d'extraction doit partir de 1, ce qui permettra d'utiliser le numéro de ligne sans correction.
Entrée2 = la plage d'entrée n° 2
ColEntrée2 = la plage Entrée2 démarrée à la ligne 1
ValNon = valeur contenue dans des éléments d'Entrée2 à exclure
On doit lister Entrée1 puis à la suite Entrée2 (épurée de ValNon).
Lister Entrée1 = recueillir les numéros de lignes des éléments de la plage (qui comporte des vides) pour extraire successivement ces élément avec INDEX.
Si l'on définit une matrice de ces lignes correspondant aux éléments à extraire, on peut utiliser PETITE.VALEUR(arguments: matrice, rang) pour renvoyer successivement les numéros de ligne, du rang 1 au rang du dernier éléments.
L'argument rang de PETITE.VALEUR sera LIGNE(1:1) sur la ligne de départ de la formule, soit le rang 1, qui va s'incrémenter sur les lignes suivantes lors de la recopie...
La matrice : c'est Entrée1 dont on aura expurgé les lignes vides, soit :
SI(Entrée1<>"";LIGNE(Entrée1);... jusque là pas de problème, pour tout élément non vide de Entrée1 on revoir la ligne, mais s'il est vide que doit on renvoyer : utilisant PETITE.VALEUR (pour garder les éléments de Entrée1 dans l'ordre) on ne va pas renvoyer 0 ; renvoyer "" peut parfois poser problème ; on va donc renvoyer une valeur élevée, on un large choix mais : 9^9 (9 puissance 9) présente l'avantage d'être élevée (on manipule rarement des valeurs supérieures), court à écrire (3 caractères), et de renvoyer un nombre qui à l'évidence ne pourra être un numéro de ligne (qui lorsqu'on aura épuisé les éléments à extraire renverra une erreur, que l'on récupère alors avec SIERREUR de façon que si la formule n'a plus d'élément à renvoyer elle ne renvoie plus rien).
Donc : SI(Entrée1<>"";LIGNE(Entrée1);9^9) voilà la matrice.
=PETITE.VALEUR(SI(Entrée1<>"";LIGNE(Entrée1);9^9);LIGNE(1:1)) voilà qui va nous renvoyer le numéro de ligne du premier élément à extraire. A ce stade, on valide matriciellement, on tire, pour vérifier que l'on a bien les numéros de ligne souhaités.
On passe à l'extraction des éléments : fonction INDEX(vecteurLigne;indexLigne)
=INDEX(ColEntrée1;PETITE.VALEUR(SI(Entrée1<>"";LIGNE(Entrée1);9^9);LIGNE(1:1))
Tester qu'on extrait bien tous les éléments d'Entrée1.
A la suite, il faut extraire les éléments d'Entrée2 ; le même raisonnement nous amènerait à :
INDEX(ColEntrée2;PETITE.VALEUR(SI(Entrée2<>"";LIGNE(Entrée2);9^9);LIGNE(1:1))
Mais d'une part on ne sera plus sur la première ligne, on aura extrait tous les éléments d'Entrée1, donc occupé autant de ligne, et on sera sur la ligne suivante.
Donc correction du rang : LIGNE(1:1)-NBVAL(Entrée1) cela paraît sans doute curieux mais ne pas perdre de vue qu'on écrit sur la première ligne d'extraction et que lorsque cette expression prendra effet LIGNE(1:1) aura la valeur NBVAL(Entrée1)+1, d'où la différence renverra 1 rang du premier élément de Entrée2 à extraire, et s'incrémentera sur les lignes suivantes pour extraction des autres éléments.
D'autre part il faut exclure les éléments contenant ValNon : CHERCHE(ValNon;Entrée2) renverra une valeur si ValNon figure dans l'élément testé et une erreur s'il n'y figure pas.
ESTERREUR(CHERCHE(ValNon;Entrée2)) reverra VRAI si l'élément ne contient pas ValNon et FAUX dans le cas contraire. On va avec ce test rectifier la matrice d'Entrée2 à prendre en compte : LIGNE(Entrée2) va donc devenir :
SI(ESTERREUR(CHERCHE(ValNon;Entrée2));LIGNE(Entrée2);9^9)
Expression finale d'extraction d'Entrée2 :
INDEX(ColEntrée2;PETITE.VALEUR(SI(Entrée2<>"";SI(ESTERREUR(CHERCHE(ValNon;Entrée2));LIGNE(Entrée2);9^9);9^9);LIGNE(1:1)-NBVAL(Entrée1))
Il reste enfin à articuler les 2 extractions pour qu'elles se succèdent : Extraire Entrée1 jusqu'à épuisement et passer à Entrée2 à la suite. Soit tant qu'on ne dépasse pas la ligne correspondant au nombre d'éléments d'Entrée1 on extrait Entrée1, dès la ligne dépassée on extrait Entrée2.
=SI(LIGNE(1:1)<=NBVAL(Entrée1); extraction Entrée1 ; extraction Entrée2 )
Plus qu'à compléter et encadrer par SIERREUR pour finir.
=SIERREUR(SI(LIGNE(1:1)<=NBVAL(Entrée1);INDEX(ColEntrée1;PETITE.VALEUR(SI(Entrée1<>"";LIGNE(Entrée1);9^9);LIGNE(1:1));INDEX(ColEntrée2;PETITE.VALEUR(SI(Entrée2<>"";SI(ESTERREUR(CHERCHE(ValNon;Entrée2));LIGNE(Entrée2);9^9);9^9);LIGNE(1:1)-NBVAL(Entrée1));"")
Sous réserve que je n'ai pas loupé de parenthèse dans le montage...
Merci pour cette leçon
Je tente de l appliquer sur le fichier dès que je rentre.
Je te tiens au courant
A +
j'ai tenté d'appliquer tes consignes, mais il y a bien un problème de parenthèses.
j'ai du en mettre plusieurs à la fin pour que la formule reste en place
ce que j'ai fais est faux ?