[Issue] Nombre valeurs particulières après filtrage
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.
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.
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
[Issue] Nombre valeurs particulières après filtrage
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
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
Vous ne pouvez pas consulter les pièces jointes insérées à ce message.
Dernière modification par ThierryM le 29 mai 2020 21:39, modifié 1 fois.
LibreOffice Version: 7.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?
-
- PassiOOnné
- Messages : 501
- Inscription : 11 mai 2013 00:11
- Localisation : Aix en Provence
Re: Nombre de valeurs particulières après filtrage
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. ferait-elle l'affaire?
Cordialement,
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)
Ajout :
|
Vous ne pouvez pas consulter les pièces jointes insérées à ce message.
Dernière modification par Noonours le 29 mai 2020 16:19, modifié 1 fois.
Noonours procrastinateur perfectionniste: "Je fais rien, mais demain je l'ferai mieux"
Pour obtenir la réponse la plus précise possible, VEUILLEZ JOINDRE UN FICHIER
LibO 7.6.5.2 Stable et OpenOffice 4.1.15 sous Windows 10
Pour obtenir la réponse la plus précise possible, VEUILLEZ JOINDRE UN FICHIER
LibO 7.6.5.2 Stable et OpenOffice 4.1.15 sous Windows 10
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
Re: Nombre de valeurs particulières après filtrage
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
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.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?
-
- PassiOOnné
- Messages : 501
- Inscription : 11 mai 2013 00:11
- Localisation : Aix en Provence
Re: Nombre de valeurs particulières après filtrage
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.
Cordialement,
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 |
Vous ne pouvez pas consulter les pièces jointes insérées à ce message.
Noonours procrastinateur perfectionniste: "Je fais rien, mais demain je l'ferai mieux"
Pour obtenir la réponse la plus précise possible, VEUILLEZ JOINDRE UN FICHIER
LibO 7.6.5.2 Stable et OpenOffice 4.1.15 sous Windows 10
Pour obtenir la réponse la plus précise possible, VEUILLEZ JOINDRE UN FICHIER
LibO 7.6.5.2 Stable et OpenOffice 4.1.15 sous Windows 10
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
Re: Nombre de valeurs particulières après filtrage
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
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.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?
-
- ManitOOu
- Messages : 2744
- Inscription : 16 avr. 2010 13:31
Re: Nombre de valeurs particulières après filtrage
Bonjour,
Une éventuelle solution avec une colonne supplémentaire (à masquer si besoin ou à dissimuler loin quelque part dans le fichier)
Colonne C (à masquer donc):
à é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 :
J'ai essayé en une seule formule à validation matricielle, sans succès
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;"")
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
Vous ne pouvez pas consulter les pièces jointes insérées à ce message.
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10 et 11
AOO
LibreOffice
Debian 10 et 11
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
Re: Nombre de valeurs particulières après filtrage
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
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
LibreOffice Version: 7.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?
-
- ManitOOu
- Messages : 2744
- Inscription : 16 avr. 2010 13:31
Re: Nombre de valeurs particulières après filtrage
je ne connais Excel que de nom. Désolé.ThierryM a écrit :savez-vous s'il existe une fonction dans Excel
A+
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10 et 11
AOO
LibreOffice
Debian 10 et 11
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
Re: Nombre de valeurs particulières après filtrage
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
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.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?
-
- ManitOOu
- Messages : 2744
- Inscription : 16 avr. 2010 13:31
Re: [Résolu] [Issue] Nombre valeurs particulières après filt
Re,
Malgré le résolu, j'ai finalement trouvé une solution par formule à validation matricielle :
remplacer "A" en bout de formule par "D" ou "P" selon ce qui doit-être compter
et le fichier qui va avec :
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")
et le fichier qui va avec :
Ajout : ne fonctionne pas sous AOO |
Vous ne pouvez pas consulter les pièces jointes insérées à ce message.
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10 et 11
AOO
LibreOffice
Debian 10 et 11
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
Re: [Résolu] [Issue] Nombre valeurs particulières après filt
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
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
LibreOffice Version: 7.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?
-
- ManitOOu
- Messages : 2744
- Inscription : 16 avr. 2010 13:31
Re: [Résolu] [Issue] Nombre valeurs particulières après filt
Bonjour,
Promis après j’arrête. Un peu plus concis :
Pour mieux comprendre, il faudrait faire l'exercice suivant :
A+
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")
1) Dans une colonne quelconque :à propager vers le bas.Code : Tout sélectionner
=SOUS.TOTAL(3;B2)
2) Dans la colonne à coté, sur la même ligne de départ :à valider en maintenant enfoncées les touches Ctrl+Maj lors de l’appui sur la touche Entrée .Code : Tout sélectionner
=SOUS.TOTAL(3;INDIRECT("B"&LIGNE($A$2:$A$13)))
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.3) Dans la colonne suivante sur la même ligne de départ :à valider "matriciellement"Code : Tout sélectionner
=SI(SOUS.TOTAL(3;INDIRECT("B"&LIGNE($A$2:$A$13)));$B$2:$B$13;"")
A+
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10 et 11
AOO
LibreOffice
Debian 10 et 11
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
Re: [Issue] Nombre valeurs particulières après filtrage
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....).
Cordialement,
Thierry
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")
Thierry
LibreOffice Version: 7.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?
-
- GourOOu
- Messages : 5753
- Inscription : 02 janv. 2009 23:56
Re: [Issue] Nombre valeurs particulières après filtrage
LibO 7.6.6.3 (x64 avec Java 1.8.0_411) et AOO 4.1.15 (avec Java x32 1.8.0_381), Windows 7 Édition Intégrale 64 SP1
-
- Membre enthOOusiaste
- Messages : 437
- Inscription : 26 nov. 2006 11:29
- Localisation : Les Corbières
Re: [Issue] Nombre valeurs particulières après filtrage
Merci Jean-Louis pour le complément d'informations.
Cordialement,
Thierry
Cordialement,
Thierry
LibreOffice Version: 7.6.2 / Linux Mint Mate Edition 21.2 / Pourquoi privilégier les formats de fichiers ouverts ?