XL 2016 Recherche du nombre d'occurence dans TOUS les onglets d'un fichier xlsx 2016

[Max22]

Bonjour
je souhaite automatiser pour un graphique , l’acquisition du nbr d’occurrence d'une chaine de caractère ex " *_open* " dans tous les onglets d'un fichier xlsx 2016

1) au début j'utilisais nb.si ( plage xx ; "*open*") ça marche bien , mais pour 1onglet

2) pour plusieurs onglets : la fonction recherche (dans tout le classeur ) => mais il faut le faire manuellement au dernier moment après avoir modifié le doc

3) existe t'il une autre fonction , voire une macro ?

Merci bien de votre aide 🙂
MAX

 

[Dim.Reichart]

Bonjour,
N'ayant pas de classeur exemple, ma solution sera peut être fausse, mais je me lance tout de même.

Il faut référencer l'ensemble des plages à l'aide de la fonction Excel4 Lire.Classeur, dans le gestionnaire de nom.
C'est une fonction de macro, mais utilisable dans le gestionnaire, cela demandera donc d'activer les macros dans le classeur (format .xlsm ou .xlsb donc).
Cette fonction renvoie le nom de tous les onglets du classeur, et peut donc, en y ajoutant l'adresse des plages de somme (à supposer que la plage soit identique sur chaque onglet), vous faire la somme de l'ensemble des onglets.
Vous pouvez trouver une description de la fonction et de sa syntaxe sur le site de Boisgontier.
Je vous laisse essayer et revenir vers nous en cas de problème.

 

[Max22]

Bonjour ,

Merci de votre réponse rapide 🙂

ci joint un fichier à titre d'exemple .
L'emplacement biensûr peut etre quelconque et l'onglet créé par qq ayant son propre formalisme

Merci d'avance
Max

 

[Dim.Reichart]

Merci pour le classeur, mais dans les exemples, les mots clés semblent mis n'importe comment.
Si je crée une fonction ou une macro qui devra analyser la totalité de chaque feuille (1,048,576 lignes X 16,384 colonnes), cela va prendre un temps fou.
Il serait préferable de restreindre la zone de recherche.

 

[Dim.Reichart]

Bonsoir,
Je n'ai pas vérifié si le compte est bon. Je n'ai pas pu compter les colonnes A et B pour éviter les références circulaires.
Si tu entres ton mot clé colonne A, puis =BD avec validation matricielle, sur la meme ligne de la colonne B, ça devrait fonctionner.
S'il faut prendre en compte la totalité des feuilles, j'aurais une autre solution, mais qui demandera un nouvel onglet.

 

[Dim.Reichart]

Solution avec le nouvel onglet.

 

[Max22]

Oui effectivement on peut raisonnablement se limiter à 200 lignes et colonne AA .

Je viens de regarder la proposition #5 avec {somme(DB)} qui donne le bon résultat !!!
c'est super !! merci beaucoup !!

mais j'ai pas compris comment le refaire 🙁
{somme(BD)} ca veut dire quoi ?
et sur feuil2 il y a nbr de cas ; _open ; {DB} ; =RECHERCHE("*open*";(A2:BN508))
mais pas sur les autres feuilles ?
J'ai essayé de voir ou était la macro pour comprendre , mais je ne vois rien ?

Quelle est cette magie 🙂) ?
Si c'est pas trop compliqué, pouvez vous m'expliquer pour que j'apprenne ?

NB : pour ce qui est du message #6 avec l'onglet "recap" c'est simple mais pas adaptatif a un nombre variable d'onglet, sans vérification et mise à jour manuelle à chaque nouvel enregistrement. Je prèfère le garder en plan B et revenir sur votre proposition du message#5

Un grand merci
Max

 

[Dim.Reichart]

La solution du message 6 liste les onglets colonne A, met a plage sous forme de tableau, si tu crées un nouvel onglet, tu auras juste à agrandir le tableau. Et tu affiches la ligne de totaux pour eviter d'avoir a ajouter la formule.
Dans les deux cas, BD fait référence au gestionnaire de nom (formules, gestionnaire de nom).

En fait, Lire.classeur(1) crée un index avec le nom complet de chaque onglet, que j'extrais avec droite(Lire.classeur(1),nbcar(lire.classeur(1)-trouve("]";Lire.classeur(1)).
Ensuite, si je fais =index(BD;1); j'aurais le nom du 1er onglet et ainsi de suite.
Dans le cas présent, on ne veut pas le nom du 1er onglet (recap), donc je commence à 2, j'incremente ce nom grâce au numéro de ligne. Puis une formule nb.si(indirect()) fait le compte des occurrence sur chaque feuille.

J'ai mis sous forme de tableau, essaye. crées un nouvel onglet, puis etend le tableau de la page recap d'une ligne. Ton nouvel onglet devrait apparaitre et être comptabilisé de suite.

 

[riton00]

Bonsoir,

Un autre exemple

Slts

 

[Max22]

Grrr
j'essaye plein de truc .. mais cela me donne toujours référence circulaire !!

nbr_occurence_gauche =NB.SI(INDIRECT("'"&LIRE.CLASSEUR(1)&"'!$c$1:$bn508";VRAI);Synthèse!$A6)
ex _open =A5 sur la feuille1 (Synthèse)

je n'ai pas compris comment commencer à la "feuille 2" pour etre sure de ne pas avoir cette ref circulaire?
au quel cas on peut commencer sur toutes les feuilles à venir : A1:ZZ200

 

[Max22]

je prends le tableau en plan B => Avec le fichier en exemple c'est mieux

la version avec uniquement la "super formule" d'une ligne semblait pourtant correcte ??

 

[Max22]

je fatigue , cela fait 4 heures que je bidouille 🙁

voici le fichier

 

[Max22]

au debut je pensais au nom de la constante que j'avais donné =nbr_Open_P0

alors j'ai appelé nbr_Op0 .. mais cela ne change rien !!

 

[job75]

Bonjour Max22, le fil,

Une solution VBA assez classique dans le fichier joint :

Private Sub WorkSheet_Activate()
Dim tablo, ub&, j%, w As Worksheet, s&, i&, k%
With [B2].CurrentRegion 'à adapter
    tablo = .Resize(, Worksheets.Count + 1) 'matrice, plus rapide, au moins 2 éléments
    ub = UBound(tablo)
    j = 1
    For Each w In Worksheets
        If w.Name <> Me.Name Then
            j = j + 1
            s = 0
            tablo(1, j) = w.Name
            For i = 2 To ub - 1
                tablo(i, j) = Application.CountIf(w.UsedRange, tablo(i, 1))
                s = s + tablo(i, j)
            Next i
            If ub > 1 Then tablo(ub, j) = s
        End If
    Next w
    '---Total par ligne---
    tablo(1, j + 1) = "Total"
    For i = 2 To ub
        s = 0
        For k = 2 To j
            s = s + tablo(i, k)
        Next k
        tablo(i, j + 1) = s
    Next i
    '---restitution---
    .Resize(ub, j + 1) = tablo
    .Columns(j + 2).Resize(, Columns.Count - .Column - j).ClearContents 'RAZ à droite
End With
End Sub

La macro se déclenche quand on active la feuille "Recap".

Edit : j'ai ajouté le Total par ligne.

A+

 

[Dim.Reichart]

Salut @Max22 ,
Lire.classeur(1) crée un index de tous les onglets, une liste si tu preferes. Donc, pour t'en servir, il faut faire =index(nomdelavariable;N° d'onglet).
Si tu mets 1 dans index, tu auras ta 1e feuille, donc référence circulaire.
La formule contenant Lire.classeur doit être dans le gestionnaire de nom, cette fonction n'est pas utilisable directement dans une feuille.

Normalement, tu peux juste copier la feuille de mon exemple vers ton classeur, et copier en bloc la formule du gestionnaire de nom, a devrait fonctionner, même si je concois que ce soit frustrant de ne pas comprendre.
Essaye d'évaluer la formule pas à pas, tu devrais trouver où ca coince.
Bon courage.