Microsoft 365 Soustraction cellule visible

[adcom81]

Bonjour,

je me retrouve face a un probleme un peu plus complique que je ne pensais.
j ai une colonne remplie (avec application de filtre) je souhaite afficher la difference de 2 cellules qui se suivent dans une autre colonne.


par exemple :
j ai Q5
Q6 P6=Q6-Q5
Q7 P7=Q7-Q6
etc
la formule doit tenir compte que des valeurs visibles (filtrees)....d ou mon probleme

Il s agit de chiffre et je souhaiterai aussi l appliquer pour une autres series avec des dates.
avec les filtres, je n arrive a pas a trouver la fonction excel (la fonction soustotal doit avoir une reference tjrs visible) ou une base macro a modifier pour obtenir ce que je souhaite.

merci pour votre aide.

Adrien

 

[JHA]

Bonjour à tous,

Sans un petit fichier exemple, je pense que tu n'auras pas beaucoup de réponse.

JHA

 

[adcom81]

Bonjour,

merci pour ton retour, c est vrai qu avec un petit fichier c est plus explicite.
dans le fichier joint j applique des filtres :
colonne "AE" (par ex 1)
colonne "S" (par ex engine service).

et pour chaque ligne i de la colonne "Q", je souhaite faire une soustraction = celulle (Q,i)- celulle (Q, i-1)
le resultat est affiche en colonne O celulle i,
idem pour la date colonne "P", resultat de la soustraction a afficher en colonne N.

la finalite est de faire un graphique dynamique en via des slicers (excel en anglais).


Adrien

 

[mapomme]

Bonjour @adcom81,

Une piste dans le fichier joint:

• retirer tous les filtres du tableau
• ajouter une colonne à la fin (colonne AF "Masqué")
• saisir dans la cellule AF4 la formule =SOUS.TOTAL(3;P4)
• la copier vers le bas (on considère que la cellule date est toujours remplie pour les lignes utiles)
• en O4 saisir la formule matricielle =SIERREUR(Q4-INDEX(Q:Q;GRANDE.VALEUR((AF$4:AF4)*LIGNE(AF$4:AF4);2));"") en la validant par la combinaison des trois touches Ctrl+Maj+Entrée
• la copier vers le bas

Si maintenant, on filtre le tableau, la colonne O(ligne visible i) doit faire la soustraction Q(ligne i)-Q(dernière ligne visible précédant la ligne i)

 

[chris]

Bonjour mapomme le forum

A noter que ce tableau présente des anomalies :

• 1 million de lignes alors qu'un tableau structuré ne doit JAMAIS contenir de ligne vide (rectifié par mapomme)
• une formule en colonne N qui n'est pas reprise sur toute la colonne : non recopie et risque d'erreur. Remplacer par
  JOURS360(SI(ESTTEXTE(P3);P4;P3);P4;1) ou équivalent et recopier de la ligne 4 à la fin pour rétablir l'automatisme
• mapomme a corrigé une anomalie équivalente en colonne O

A noter que j'ai déjà observé des anomalies sur les plages élastiques dans les tableaux structurés et qu'il est plus fiable de passer par INDIRECT avec LIGNE()
Cela ne semble pas de produire ici mais...

 

[mapomme]

Bonjour @chris ,

Je ne connaissais pas l'anomalie que tu as rencontrée et décrite.

 

[chris]

Re

J'ai eu le souci il y a peu, je vais essayer de retrouver le fil et la formule qui posait problème

Edit : j'ai retrouvé https://www.excel-downloads.com/thr...-a-un-nom-et-une-date.20032056/#post-20235472

j'avais proposé une formule au #9 mais voyant qu'elle posait problème si on ajoutait une ligne avec Tab, j'en ai proposé une autre plus loin

 

[adcom81]

Merci

Bonjour @adcom81,

Une piste dans le fichier joint:

• retirer tous les filtres du tableau
• ajouter une colonne à la fin (colonne AF "Masqué")
• saisir dans la cellule AF4 la formule =SOUS.TOTAL(3;P4)
• la copier vers le bas (on considère que la cellule date est toujours remplie pour les lignes utiles)
• en O4 saisir la formule matricielle =SIERREUR(Q4-INDEX(Q:Q;GRANDE.VALEUR((AF$4:AF4)*LIGNE(AF$4:AF4);2));"") en la validant par la combinaison des trois touches Ctrl+Maj+Entrée
• la copier vers le bas

Si maintenant, on filtre le tableau, la colonne O(ligne visible i) doit faire la soustraction Q(ligne i)-Q(dernière ligne visible précédant la ligne i)

Bonjour @adcom81,

Une piste dans le fichier joint:

• retirer tous les filtres du tableau
• ajouter une colonne à la fin (colonne AF "Masqué")
• saisir dans la cellule AF4 la formule =SOUS.TOTAL(3;P4)
• la copier vers le bas (on considère que la cellule date est toujours remplie pour les lignes utiles)
• en O4 saisir la formule matricielle =SIERREUR(Q4-INDEX(Q:Q;GRANDE.VALEUR((AF$4:AF4)*LIGNE(AF$4:AF4);2));"") en la validant par la combinaison des trois touches Ctrl+Maj+Entrée
• la copier vers le bas

Si maintenant, on filtre le tableau, la colonne O(ligne visible i) doit faire la soustraction Q(ligne i)-Q(dernière ligne visible précédant la ligne i)

Bonjour ma pomme,

merci beaucoup, ca marche parfaitement.
la formule est egalement applicable pour les dates.
j avais deja utilise une fois les formes matricielles ca prenait bcp de temps de calcul. la c est tres rapide.

encore merci !

bon week end

Adrien

 

[adcom81]

Bonjour mapomme le forum

A noter que ce tableau présente des anomalies :

• 1 million de lignes alors qu'un tableau structuré ne doit JAMAIS contenir de ligne vide (rectifié par mapomme)
• une formule en colonne N qui n'est pas reprise sur toute la colonne : non recopie et risque d'erreur. Remplacer par
  JOURS360(SI(ESTTEXTE(P3);P4;P3);P4;1) ou équivalent et recopier de la ligne 4 à la fin pour rétablir l'automatisme
• mapomme a corrigé une anomalie équivalente en colonne O

A noter que j'ai déjà observé des anomalies sur les plages élastiques dans les tableaux structurés et qu'il est plus fiable de passer par INDIRECT avec LIGNE()
Cela ne semble pas de produire ici mais...

Bonjour chris,

merci pour linfo et comme j ai essaye pas mal de truc, je ne suis aps surpris qu il ne soit pas tres propre, je vais enttoyer tout ca.

bonne journee