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

[Dewilliam]

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

[njhub]

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

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.

[OOotremer971]

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.

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")

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/

[Dewilliam]

MERCI de tout coeur OOotremer971!

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

Bien cordialement.
William

[Dewilliam]

UN grand Merci aussi à njhub!