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

Discussions à propos du tableur Calc.
Les questions sur les macros doivent être postées dans la section dédiée en dessous.

Modérateur: Vilains modOOs

Règles du forum
Cette section est uniquement dédiée au tableur Calc. Vous ne devez pas poster ici de questions sur les macros mais utiliser la section éponyme.

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

Messagepar ThierryM » 29 Mai 2020 16:27

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
Pièces jointes
Classeur_Filtrage.ods
Classeur exemple
(12.77 Kio) Téléchargé 46 fois
Dernière édition par ThierryM le 29 Mai 2020 22:39, édité 1 fois.
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières

Re: Nombre de valeurs particulières après filtrage

Messagepar Noonours » 29 Mai 2020 17:00

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   AgrandirRéduire
=SOUS.TOTAL(103;plage)
ferait-elle l'affaire?

 Ajout : 
L'aide F1 a écrit: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
Annotation 2020-05-29 181315.png (8.49 Kio) Consulté 1128 fois

=SOUS.TOTAL(9;B2:B6) renvoie 50.

=SOUS.TOTAL(109;B2:B6) renvoie 40.
 


Cordialement,
Dernière édition par Noonours le 29 Mai 2020 17:19, édité 1 fois.
Noonours plantigrade râleur

Pour obtenir la réponse la plus précise possible, N’HÉSITEZ SURTOUT PAS À JOINDRE UN FICHIER

LibO 7.0.3.1 et AOO 4.1.8 sous Windows 10
LibO 6.1.6.3_M14 MIMO (obligation de version) sous Win 7
Avatar de l’utilisateur
Noonours
Membre cOOnverti
Membre cOOnverti
 
Message(s) : 396
Inscrit le : 11 Mai 2013 01:11
Localisation : Aix en Provence

Re: Nombre de valeurs particulières après filtrage

Messagepar ThierryM » 29 Mai 2020 17:12

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
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières

Re: Nombre de valeurs particulières après filtrage

Messagepar Noonours » 29 Mai 2020 18:03

Re-Bonjour ThierryM :wink:
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 :lol: 


Classeur_Filtrage_Mod_Noonours.ods
(15.42 Kio) Téléchargé 60 fois

Cordialement,
Noonours plantigrade râleur

Pour obtenir la réponse la plus précise possible, N’HÉSITEZ SURTOUT PAS À JOINDRE UN FICHIER

LibO 7.0.3.1 et AOO 4.1.8 sous Windows 10
LibO 6.1.6.3_M14 MIMO (obligation de version) sous Win 7
Avatar de l’utilisateur
Noonours
Membre cOOnverti
Membre cOOnverti
 
Message(s) : 396
Inscrit le : 11 Mai 2013 01:11
Localisation : Aix en Provence

Re: Nombre de valeurs particulières après filtrage

Messagepar ThierryM » 29 Mai 2020 19:42

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
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières

Re: Nombre de valeurs particulières après filtrage

Messagepar OOotremer971 » 29 Mai 2020 20:35

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   AgrandirRéduire
=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   AgrandirRéduire
=NB.SI($C$2:$C$13;"A")


J'ai essayé en une seule formule à validation matricielle, sans succès :(
Pièces jointes
Classeur_Filtrage_Mod1.ods
(8.52 Kio) Téléchargé 47 fois
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10
Ubuntu 18.04.4 LTS
OpenSuse Leap
Avatar de l’utilisateur
OOotremer971
ManitOOu
ManitOOu
 
Message(s) : 2503
Inscrit le : 16 Avr 2010 14:31

Re: Nombre de valeurs particulières après filtrage

Messagepar ThierryM » 29 Mai 2020 21:20

Merci OOotremer971 !
Super astuce de masquer les cellules qui seront masquées :bravo: !
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" :lol: ).
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 :wink: (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
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières

Re: Nombre de valeurs particulières après filtrage

Messagepar OOotremer971 » 29 Mai 2020 22:30

ThierryM a écrit:savez-vous s'il existe une fonction dans Excel

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

A+
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10
Ubuntu 18.04.4 LTS
OpenSuse Leap
Avatar de l’utilisateur
OOotremer971
ManitOOu
ManitOOu
 
Message(s) : 2503
Inscrit le : 16 Avr 2010 14:31

Re: Nombre de valeurs particulières après filtrage

Messagepar ThierryM » 29 Mai 2020 22:34

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
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières

Re: [Résolu] [Issue] Nombre valeurs particulières après filt

Messagepar OOotremer971 » 30 Mai 2020 01:57

Re,

Malgré le résolu, j'ai finalement trouvé une solution par formule à validation matricielle :
Code : Tout sélectionner   AgrandirRéduire
=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 
Pièces jointes
Classeur_Filtrage_Mod2.ods
(8.56 Kio) Téléchargé 48 fois
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10
Ubuntu 18.04.4 LTS
OpenSuse Leap
Avatar de l’utilisateur
OOotremer971
ManitOOu
ManitOOu
 
Message(s) : 2503
Inscrit le : 16 Avr 2010 14:31

Re: [Résolu] [Issue] Nombre valeurs particulières après filt

Messagepar ThierryM » 30 Mai 2020 02:11

Alors là, OOotremer971 : Chapeau bas l'artiste !!! :bravo:
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 :wink:.
Encore merci pour le temps passé à m'aider.
Cordialement,

Thierry
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières

Re: [Résolu] [Issue] Nombre valeurs particulières après filt

Messagepar OOotremer971 » 30 Mai 2020 04:26

Bonjour,

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

Code : Tout sélectionner   AgrandirRéduire
=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   AgrandirRéduire
=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   AgrandirRéduire
=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   AgrandirRéduire
=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+
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10
Ubuntu 18.04.4 LTS
OpenSuse Leap
Avatar de l’utilisateur
OOotremer971
ManitOOu
ManitOOu
 
Message(s) : 2503
Inscrit le : 16 Avr 2010 14:31

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

Messagepar ThierryM » 30 Mai 2020 11:41

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   AgrandirRéduire
=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
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières

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

Messagepar Jean-Louis Cadeillan » 04 Juin 2020 14:18

Bonjour,
Voir aussi ici.
Cordialement,
Jean-Louis
LibO 6.4.7.2 (x64 avec Java 1.8.0_281) et AOO 4.1.8 (avec Java x32 1.8.0_241), Windows 7 Édition Intégrale 64 SP1, (Domicile)
LibO 5.4.1.2 (x86) sous Ubuntu LTS 16.04.1, noyau 4.4.0-93 et Xfce 4.12, Java (x86) 1.8.0_131 (Travail) [obligation de version]
Avatar de l’utilisateur
Jean-Louis Cadeillan
GourOOu
GourOOu
 
Message(s) : 5030
Inscrit le : 03 Jan 2009 00:56

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

Messagepar ThierryM » 04 Juin 2020 16:32

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

Thierry
LibreOffice Version: 7.0.1.2 version ubuntu / Ubuntu Trusty 18.04 / Pourquoi privilégier les formats de fichiers ouverts ?
ThierryM
Membre enthOOusiaste
Membre enthOOusiaste
 
Message(s) : 404
Inscrit le : 26 Nov 2006 12:29
Localisation : Les Corbières


Retour vers Tableur

Qui est en ligne ?

Utilisateur(s) parcourant ce forum : Aucun utilisateur inscrit et 18 invité(s)