[Issue] Nombre valeurs particulières après filtrage

[ThierryM]

Adieu a totes,
Je suis en train de réaliser un classeur à destination d'autres personnes qui devront importer un fichier .csv. Une fois ce fichier CSV importé, une page servant à faire un recensement journalier est automatiquement créée. Jusque là tout va bien car je passe par des macros pour automatiser tout cela mais les utilisateur⋅rice⋅s finaux⋅ales travailleront directement dans le classeur sans voir toute cette partie technique. Pour éviter d'avoir à lancer manuellement d'autres macros, toutes les données qu'elles obtiendront se feront par utilisation de formules pour avoir une lecture directe automatique même après filtrage (je veux éviter que les résultats dépendent d'une macro).

Or pour leur besoin, elles peuvent être amener à filtrer les données et connaître le nombre de réponses correspondant à différentes valeurs selon leur filtrage. La fonction SOUS.TOTAL(3;plage) ne permet pas apparemment de distinguer les différentes valeurs. Or existerait-il une formule qui permettrait d'afficher le nombre de valeurs particulières et tenant compte du filtrage.
Je vous joins un fichier essayant d'expliquer ce que je voudrais obtenir.
J'aimerais que dans les cellules jaunes s'affichent le nombre de valeurs A, de valeurs D et de valeurs P après filtrage selon la zone de couleur portant sur la colonne A du tableau en A1:B13. J'ai indiqué les résultats attendus à droite des cellules jaunes.
Auriez-vous une solution ?
En vous remerciant pour vos éclairages, cordialement,

Thierry

[Noonours]

Bonjour,
As-tu essayé d'autres paramètres pour le premier argument de la fonction?
Avec 103 au lieu de 3 par exemple, cela ne comptabilise pas les cellules cachées.

Code : Tout sélectionner

=SOUS.TOTAL(103;plage)

ferait-elle l'affaire?

Ajout :  SOUS.TOTAL Calcule les sous-totaux. Si une plage donnée contient déjà des sous-totaux, ceux-ci ne sont pas utilisés pour des calculs ultérieurs. Pour que le calcul ne porte que sur les enregistrements filtrés, associez cette fonction aux AutoFiltres. Syntaxe SOUS.TOTAL(fonction;plage) fonction est un nombre qui représente l'une des fonctions suivantes : Annotation 2020-05-29 181728.png Utilisez les nombres 1-11 pour inclure manuellement les lignes masquées ou 101-111 pour les exclure ; les cellules filtrées sont toujours exclues. plage correspond à la plage dont les cellules sont incluses. Exemple Il y a un tableau dans la plage de cellule A1:B6 contenant une facture de matériel pour 10 élèves. La ligne 2 (stylos) est masquée manuellement. Vous voulez voir la somme des nombres qui sont affichés ; c'est-à-dire le sous-total des lignes filtrées. Dans ce cas, la formule correcte est : Annotation 2020-05-29 181315.png =SOUS.TOTAL(9;B2:B6) renvoie 50. =SOUS.TOTAL(109;B2:B6) renvoie 40.

Cordialement,

[ThierryM]

Merci Noonours,
SOUS.TOTAL(103;plage) donne le même résultat que SOUS.TOTAL(3;plage). En fait la différence de traitement porte sur les cellules masquées manuellement, ce qui n'est pas le cas pour un filtrage (source : https://help.libreoffice.org/6.4/fr/tex ... _id3165633).
En fait, j'ai testé une formule du type "=SOUS.TOTAL(3;NB.SI(B2:B13;"P")" en agissant sur le deuxième paramètre qui me retourne 1 quel que soit le filtrage.
Cordialement,

Thierry

[Noonours]

Re-Bonjour ThierryM
Juste une question: est-ce que l'utilisateur doit obligatoirement passer par un auto-filtre pour sélectionner une zone? (bleue, verte, rouge etc)
Si la personne peut choisir dans une cellule (par une liste de validité par exemple) une zone, il devient aisé de croiser les données et comptabiliser le nombre de types en fonction de la zone, grâce à SOMMEPROD. Un tableau croisé dynamique est possible aussi.

Ajout : Ton fichier avec en cellule A15 une liste de validité dont l'utilisateur peut sélectionner une valeur, et en fonction des deux plages que j'ai nommées PlageZone et PlageType, SOMMEPROD fait le reste

Classeur_Filtrage_Mod_Noonours.ods

Cordialement,

[ThierryM]

Re-Adieu Noonours,
L'autofiltre est obligatoire car dans le fichier final, les valeurs ainsi que le nombre de valeurs distinctes dans la colonne "zone" de mon fichier sont différentes selon les répondants : je ne peux pas les anticiper ou sinon passer un développement de macro très lourd (manque de temps).
La fonction SOUS.TOTAL ne porte apparemment que sur le nombre de valeurs ou des calculs sur des nombres.
Cordialement,

Thierry

[OOotremer971]

Bonjour,

Une éventuelle solution avec une colonne supplémentaire (à masquer si besoin ou à dissimuler loin quelque part dans le fichier)

Colonne C (à masquer donc):

Code : Tout sélectionner

=SI(SOUS.TOTAL(3;B2);B2;"") 

à étirer vers le bas. SOUS.TOTAL() renverra un résultat uniquement si la ligne n'est pas masquée. Du coup on peut utiliser un NB.SI() sur la colonne C car toutes les lignes masquées renverront une cellule vide ("")


En B16 :

Code : Tout sélectionner

=NB.SI($C$2:$C$13;"A")

J'ai essayé en une seule formule à validation matricielle, sans succès

[ThierryM]

Merci OOotremer971 !
Super astuce de masquer les cellules qui seront masquées !
J'avais pensé à rajouter une colonne masquée pour des calculs intermédiaires mais je voulais éviter car en fait dans mon fichier, j'ai une trentaine de colonnes (il s'agit de jours d'école). Je pense que je vais retenir cette astuce avec rajout des colonnes et des formules par macro mais j'avais espoir qu'une formule "magique" existait déjà pour cela (c'est très rare quand LibreOffice est pris en "défaut" ).
Est-ce que, par curiosité, savez-vous s'il existe une fonction dans Excel arrivant à faire ce que je voulais ? Non pas que je veuille abandonner LibreOffice (loin de là, vu mes convictions et mon niveau de maîtrise plutôt "experte" notamment dans les macros) mais pour soumettre une demande d'amélioration en m'appuyant sur de l'existant (même si je peux m'appuyer sur ce fil de discussion).
Cordialement,

Thierry

[OOotremer971]

savez-vous s'il existe une fonction dans Excel

je ne connais Excel que de nom. Désolé.

A+

[ThierryM]

Re-Adieu,
Bon, du coup, je viens de faire la demande d'amélioration n° 133506 dans LibreOffice pour la fonction SOUS.TOTAL ici : https://bugs.documentfoundation.org/sho ... ?id=133506.
Pour le balisage, j'ai choisi [Résolu] avec l'icône verte car c'est le principal ET [Issue] pour indiquer la demande d'amélioration.
Cordialement,

Thierry

[OOotremer971]

Re,

Malgré le résolu, j'ai finalement trouvé une solution par formule à validation matricielle :

Code : Tout sélectionner

=NB.SI(SIERREUR(INDEX($B$1:$B$13;SI(SOUS.TOTAL(3;INDIRECT("B"&LIGNE($A$2:$A$13)));LIGNE($A$2:$A$13);""));"");"A")

remplacer "A" en bout de formule par "D" ou "P" selon ce qui doit-être compter

et le fichier qui va avec :

Ajout : ne fonctionne pas sous AOO

[ThierryM]

Alors là, OOotremer971 : Chapeau bas l'artiste !!!
Je ne maîtrise pas le calcul matriciel pour comprendre la formule mais elle est efficace : c'est tout à fait ce que je "cherchais" avec une formule de calcul uniquement. Je pense que tu as dû certainement galérer pour y arriver. C'est là où je me rends compte qu'il va falloir que j'investisse le champs du calcul matriciel (ce n'est pas la première fois que je le constate).
Il me reste plus qu'à trouver comment saisir une formule matricielle dans une macro .
Encore merci pour le temps passé à m'aider.
Cordialement,

Thierry

[OOotremer971]

Bonjour,

Promis après j’arrête. Un peu plus concis :

Code : Tout sélectionner

=NB.SI(SI(SOUS.TOTAL(3;INDIRECT("B"&LIGNE($A$2:$A$13)));$B$2:$B$13;"");"A") 

Pour mieux comprendre, il faudrait faire l'exercice suivant :

1) Dans une colonne quelconque :

Code : Tout sélectionner

=SOUS.TOTAL(3;B2)

à propager vers le bas.

2) Dans la colonne à coté, sur la même ligne de départ :

Code : Tout sélectionner

=SOUS.TOTAL(3;INDIRECT("B"&LIGNE($A$2:$A$13)))

à valider en maintenant enfoncées les touches Ctrl+Maj lors de l’appui sur la touche Entrée .

3) Dans la colonne suivante sur la même ligne de départ :

Code : Tout sélectionner

=SI(SOUS.TOTAL(3;INDIRECT("B"&LIGNE($A$2:$A$13)));$B$2:$B$13;"")

à valider "matriciellement"

La fonction NB.SI() retournant une valeur unique, sa validation matricielle n'occupe alors qu'une seule cellule alors que l'ensemble de la formule a virtuellement créer les deux matrices vues précédemment pour en extraire l'unique résultat attendu.

A+

[ThierryM]

Merci OOotremer971 pour la patience et la pédagogie dont tu fais preuve.
J'ai reconstitué les différentes étapes (en dehors des lignes qui seront masquées pour voir les résultats) pour comprendre l'évolution des résultats (comme si on les passait au crible en fait). J'y vois un peu plus clair.
Je vais regarder aussi la documentation pour mieux comprendre https://help.libreoffice.org/6.4/fr/tex ... 60107.html.
EDIT 1 : pour mes macros, je pense que je vais utiliser la notation L1C1 pour INDIRECT (mais à creuser et c'est une autre histoire...).

EDIT 2 : En fait dans la formule matricielle, je viens de me rendre compte que n'importe quelle colonne de la zone concernée pour la fonction INDIRECT convenait. Ceci m'a bien arrangé pour ma macro pour ne pas avoir à retranscrire le n°de colonne en lettre pour la partie "INDIRECT("B"&LIGNE....).

Code : Tout sélectionner

=NB.SI(SIERREUR(INDEX($B$1:$B$13;SI(SOUS.TOTAL(3;INDIRECT("B"&LIGNE($A$2:$A$13)));LIGNE($A$2:$A$13);""));"");"A")

Cordialement,

Thierry

[Jean-Louis Cadeillan]

Bonjour,
Voir aussi ici.
Cordialement,
Jean-Louis

[ThierryM]

Merci Jean-Louis pour le complément d'informations.
Cordialement,

Thierry