[Résolu] NB.Si.ENS complexe pour comparaison de listes

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.

[Résolu] NB.Si.ENS complexe pour comparaison de listes

Messagepar Dewilliam » 25 Oct 2020 11:09

Bonjour,

J'ai plusieurs fichiers nommés suivant les lettres de l'alphabet OST-A.ods, OST-B.ods et ainsi de suite jusqu'à Z.

Ces fichiers contiennent la liste de ma collection de bandes originales de films répartis dans les colonnes suivantes:
OST_id
Année
Manque-Date
GENRE
Dans-COLLECTION
TYPE
TITRE

COUVERTURE
AUTRE-TITRE
COMPOSITEUR

AUTRES-COMPOSITEURS

En vert les colonnes les plus importantes.
C'est voulu que les compositeurs soient classés alphabétiquement par leur prénom et non par leur nom!

Je fais des calculs dans un autre fichier (totals.ods) ou j'ai par ligne A, B, C, ... plusieures colonnes:
MP3 le nombre de fichiers MP3
CD le nombre de CD
33T le nombre de vinyls 33T
45T le nombre de vinyls 45t
ZERO les bandes originales que je n'ai pas
WL (whishlist) les bandes originales que je souhaite trouver

ZERO et WL ont comme résultat un nombre identique, c'est normal.
Désole, je ne puis mettre tous les fichiers alphabetiques en fichiers joints car cela représente plus de 9000 bandes originales.
Vous n'avez que le fichier A et c'est donc normal que fichier totals n'a que des résultats dans la ligne A. Merci de votre compréhension.

La formule que j'ai utilisée pour savoir le nombre de MP3 pour la lettre A =NB.SI('file:///G:/OST/OpenOFFICE/LISTS/OST-A.ods'#$Feuille1.D2:D5000;"MP3")
et le résultat est ce que j'en attends.

J'aimerais détailler les choses en faisant un éclatement de chaque fichier OST car dans chacun, il y a évidemment plusieurs compositeurs.
Si je calcule le nombre de fois que le compositeur "Alex North" apparaît dans le fichier, j'obtiens le nombre 113.
Ainsi la formule utilisée est:

=NB.SI('file:///G:/OST/OpenOFFICE/LISTS/OST-A.ods'#$Feuille1.K2:K5000;"Alex North") -> résultat dans le fichier totals onglet essais.

De plus ce qui m'est fastidieux, c'est que pour chaque compositeur dans ESSAIS, je suis obligé de retaper la formule. Je suppose qu'il y a un moyen d'automatiser cela?

Mais j'aimerais surtout y ajouter un deuxième critère qui devrait être par exemple #$Feuille1.D2:D5000;"MP3" mais je n'y arrive pas et je n'ai pas trouvé dans le forum.

Dernières formules essayées:

=NB.SI.ENS('file:///G:/OST/OpenOFFICE/LISTS/OST-A.ods'#$Feuille1.K2:K5000;"Alex North";('file:///G:/OST/OpenOFFICE/LISTS/OST-A.ods'#$Feuille1.D2:D5000;"MP3")
=NB.SI.ENS('file:///G:/OST/OpenOFFICE/LISTS/OST-A.ods'#$Feuille1.K2:K5000;"Alex North";#$Feuille1.D2:D5000;"MP3")

mais les erreurs #nom? ou 502, 509 etc. ne m'aident pas.

Peut être que le NB.SI.ENS ne convient pas ou qu'il faut travailler avec un fichier intermédiaire.

Merci d'avance de votre aide.
Bien cordialement,
William De Wulf
Pièces jointes
TOTALS.ods
(20.84 Kio) Téléchargé 14 fois
OST-A.ods
Liste des bandes originales de films par compositeurs avec prénom commençant par A
(57.56 Kio) Téléchargé 19 fois
OpenOffice AOO416m1(Build:9790) sous Windows 10 version 1909
PC DELL Inspiron 7777 AIO - I7-8700T 2.40 GHz - 16,0 Go ram
Dewilliam
Fraîchement OOthentifié
 
Message(s) : 5
Inscrit le : 29 Jan 2019 16:49

Re: NB.Si.ENS complexe

Messagepar njhub » 25 Oct 2020 15:11

Bonjour Dewilliam,

En colonne D de la feuille F2, changez le format recherché, et en colonne F de la feuille F2, changez le compositeur recherché.
Puis copiez la colonne H de la feuille F2 vers le bloc note et depuis celui-ci vers la colonne utile, au format recherché dans la feuille OST-Totals
à condition que vos fichiers OST soient bien dans le dossier LISTS, sinon corrigez l'adresse en colonnes C et E de la feuille F2

Code : Tout sélectionner   AgrandirRéduire
=NB.SI.ENS('file:///G:/OST/OpenOFFICE/LISTS/OST-A.ods'#$Feuille1.$D$2:$D$5000;"MP3";'file:///G:/OST/OpenOFFICE/LISTS/OST-A.ods'#$Feuille1.$K$2:$K$5000;"A.R.Rahman")

Code : Tout sélectionner   AgrandirRéduire
=NB.SI.ENS('file:///G:/OST/OpenOFFICE/LISTS/OST-B.ods'#$Feuille1.$D$2:$D$5000;"MP3";'file:///G:/OST/OpenOFFICE/LISTS/OST-B.ods'#$Feuille1.$K$2:$K$5000;"A.R.Rahman")

Code : Tout sélectionner   AgrandirRéduire
=NB.SI.ENS('file:///G:/OST/OpenOFFICE/LISTS/OST-C.ods'#$Feuille1.$D$2:$D$5000;"MP3";'file:///G:/OST/OpenOFFICE/LISTS/OST-C.ods'#$Feuille1.$K$2:$K$5000;"A.R.Rahman")

Etc...

Dewilliam_TOTALS.ods
(28.17 Kio) Téléchargé 16 fois


:wink:

PS: votre liste de compositeurs comporte des doublons à l'orthographe variable, qui ne seront pas comptabilisés, suivant l'orthographe utilisée, ils sont facilement décelable ce sont ceux qui apparaissent plusieurs fois dans les listes déroulantes de l'autofiltre.
LibreOffice 7.0.4.2/ (x64) FR-YT 18/12/20
OS : Windows 10.0
njhub
Membre cOOnverti
Membre cOOnverti
 
Message(s) : 305
Inscrit le : 18 Juin 2017 06:43
Localisation : 976

Re: NB.Si.ENS complexe

Messagepar OOotremer971 » 25 Oct 2020 15:19

Bonjour,

La fonction INDIRECT() te permet de récupérer avantageusement le contenu d'une cellule pour créer une URL dont une partie de la chaîne de caractère correspond au contenu de ladite cellule :

Par exemple en B2 pour que la lettre de l'alphabet contenue en colonne A soit automatiquement récupérée par la formule et ajoutée au nom du fichier :
Code : Tout sélectionner   AgrandirRéduire
=NB.SI(INDIRECT("'file:///G:/OST/OpenOFFICE/LISTS/OST-"&$A2&".ods'#$feuille1.D2:D5000");B$1)


Tu peux aussi récupérer le contenu de la ligne 1 au lieu de le saisir en dur directement dans la formule. De cette façon, tu peux étirer cette formule vers la droite et vers le bas sans avoir à modifier à chaque fois manuellement le nom du fichier ou l'item à comptabiliser.

Dewilliam a écrit:Mais j'aimerais surtout y ajouter un deuxième critère qui devrait être par exemple #$Feuille1.D2:D5000;"MP3" mais je n'y arrive pas et je n'ai pas trouvé dans le forum.

avec la fonction SOMMEPROD() en I2 :
Code : Tout sélectionner   AgrandirRéduire
=SOMMEPROD((INDIRECT("'file:///G:/OST/OpenOFFICE/LISTS/OST-"&$A2&".ods'#$feuille1.K2:K5000")="Alex North");(INDIRECT("'file:///G:/OST/OpenOFFICE/LISTS/OST-"&$A2&".ods'#$feuille1.D2:D5000")="MP3"))

ou avec NB.SI.ENS() :
Code : Tout sélectionner   AgrandirRéduire
=NB.SI.ENS(INDIRECT("'file:///G:/OST/OpenOFFICE/LISTS/OST-"&$A2&".ods'#$feuille1.K2:K5000");"Alex North";INDIRECT("'file:///G:/OST/OpenOFFICE/LISTS/OST-"&$A2&".ods'#$feuille1.D2:D5000");"MP3")

Dewilliam.png

Après pour éviter de saisir en dur Alex North et MP3 dans la formule ci-dessus tu les remplaces par :
Code : Tout sélectionner   AgrandirRéduire
GAUCHE(I$1;TROUVE("/";I$1)-1)

et
Code : Tout sélectionner   AgrandirRéduire
DROITE(I$1;NBCAR(I$1)-TROUVE("/";I$1))

soit :
Code : Tout sélectionner   AgrandirRéduire
=NB.SI.ENS(INDIRECT("'file:///G:/OST/OpenOFFICE/LISTS/OST-"&$A2&".ods'#$feuille1.K2:K5000");GAUCHE(I$1;TROUVE("/";I$1)-1);INDIRECT("'file:///G:/OST/OpenOFFICE/LISTS/OST-"&$A2&".ods'#$feuille1.D2:D5000");DROITE(I$1;NBCAR(I$1)-TROUVE("/";I$1)))

et le fichier qui va avec. J'ignore si les liens s’actualiseront sur ta machine, auquel cas il faudra remplacer dans les formules :
Code : Tout sélectionner   AgrandirRéduire
'file:///home/oootremer971/Téléchargements/

par
Code : Tout sélectionner   AgrandirRéduire
'file:///G:/OST/OpenOFFICE/LISTS/
Pièces jointes
TOTALS_Mod1.ods
(19.6 Kio) Téléchargé 21 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: NB.Si.ENS complexe

Messagepar Dewilliam » 26 Oct 2020 07:19

MERCI de tout coeur OOotremer971!

Rapidité et efficacité, c'est vraiment appréciable.

Bien cordialement.
William
OpenOffice AOO416m1(Build:9790) sous Windows 10 version 1909
PC DELL Inspiron 7777 AIO - I7-8700T 2.40 GHz - 16,0 Go ram
Dewilliam
Fraîchement OOthentifié
 
Message(s) : 5
Inscrit le : 29 Jan 2019 16:49

Re: NB.Si.ENS complexe

Messagepar Dewilliam » 26 Oct 2020 07:42

UN grand Merci aussi à njhub!
OpenOffice AOO416m1(Build:9790) sous Windows 10 version 1909
PC DELL Inspiron 7777 AIO - I7-8700T 2.40 GHz - 16,0 Go ram
Dewilliam
Fraîchement OOthentifié
 
Message(s) : 5
Inscrit le : 29 Jan 2019 16:49


Retour vers Tableur

Qui est en ligne ?

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