XL 2010 compter occurences d'une phrase dans plusieurs feuillets

[Micalle]

Bonjour,

J'aimerai un coup de main pour faire un comptage automatique d’occurrences sur mes onglets excel.

Je m'explique.

Je traite des envois de courriers qui sont tous identiques à une occurrence prête: la ville destinatrice. cette dernière apparaît toujours la même cellule (en H3) via un menu déroulant (j'ai une centaine de villes différentes)

mon fichier excel comporte environ 2500 onglets et j'en rajoute une 30aines tout les jours.
ils sont tous compris entre un onglet "fin" et un onglet "centralisation" et portent des noms différents qui se construisent selon un format "jour-mois (num de l'onglet)"

Aujourd'hui, j'aimerai connaitre combien de fois j'ai envoyé un courrier à paris (01), Nantes (05), bordeaux (11), champignac les trois rivières (34)...etc mais ne pas avoir à compter manuellement, et que si je rajoute un onglet mon compteur s’incrémente de 1 automatiquement.

Etant très novice dans excel, je me tourne vers vous car n'arrive pas à faire fonctionner ce que j'ai déjà trouvé sur internet.
j'ai testé une formule =SOMMEPROD(NB.SI(INDIRECT(""&centralisation:fin&"'!H3");A3))
le A3 de la formule fait référence a "paris (01)" qui se trouve en A3 de mon onglet de calcul

j'ai l'impression que ma formule ne va pas chercher dans tout les onglets qui m'interessent

Merci d'avance pour votre aide

 

[Lolote83]

Salut,
Un petit fichier joint permettrait de mieux cibler ta demande.
Juste 3, 4 onglets pour faire des tests.
Sinon, ta formule me semble incorrecte.
Essaye : =SOMMEPROD(NB.SI(INDIRECT(""& centralisation & ":" & fin &"'!H3");A3)) mais sans aucune conviction sans fichier pour tester.
@+ Lolote83

 

[chris]

Bonjour

mon fichier excel comporte environ 2500 onglets et j'en rajoute une 30aines tout les jours.

Il est plus que probable que ton fichier sera inutilisable avant la fin de l'année outre la difficulté à naviguer dans un tel nombre d'onglets

Il me parait urgent de revoir le principe de démultiplier les onglets plutôt de de gérer une table de données.

Ton problème le montre d'ailleurs...

C'est juste une mise en garde amicale pas une critique...

 

[Micalle]

Merci de votre réponse rapide à tout les deux

Voici un fichier test, j'ai repris la trame de ce que j'utilise et ai supprimé toutes les données qui ne m’intéressent pas/sont non partageables
j'aurais aimer un recap du nombre de fois ou le nom des villes apparaît dans tout mon fichier, qui se mette a jour automatiquement a chaque nouvel onglet
chaque onglet à pour but d’être imprimé et envoyé par courrier mais conservé informatiquement pour "suivi"

@chris, merci pour la mise en garde, il s'agit d'un "outil" mis en place en interne dans mon unité au travail. Je viens d’intégrer l'unité et récupère des process mis en place. Je suis très loin d’être fan mais ayant du me former aux taches autres, je me retrouve seulement maintenant à gérer des taches informatiques dans une équipe où il est déjà difficile pour certains d'allumer un écran sans appeler le service dépannage...

nous sommes en train de réfléchir a un nouveau système pour ces courriers.

 

[zebanx]

Bonjour Micalle, Chris, Lolotte83

Un essai sur une feuille "recap" avec deux propositions restituant la ville (H3) et le nom de l'onglet dans les colonnes A et B sur la feuille "recap" :
- actualisation sur toutes les feuilles
- actualisation sur bornes (commence à "Fin" et finit à "Centralisation")
et un TCD qui n'a pas de bornes à actualiser (limites automatiquement mise à jour).

Je plussoie à la remarque de Chris.
Outre des problèmes de tailles de fichiers (nombre d'onglets et taille globale du fichier), il faut au moins veiller à faire de très nombreux back-up au cas où...ça plante.

Mais voir qu'on peut travailler avec plus de 300 onglets sur un fichier, c'est remarquable (aussi) .

@+

 

[Micalle]

Bonjour Zebanx,

si j'ai bien compris, le tcd (colonne f et g) est fonction des occurences de la colonne a et b. mais comment faire remonter ces occurences ?

 

[zebanx]

Re-

???
Il faut appuyer sur les boutons pour lancer les macros... (actualise_entre_fin_et_centralisation puis tcd_refresh).

 

[Micalle]

oui j'avais vu.

mais tes colonnes a et b s'actualise avec les macros, mais les macros ont été créées comment ? ^^"

 

[job75]

Bonjour Micalle, Lolote83, chris, zebanx,

Avec une fonction VBA qui compte les villes ce n'est pas bien difficile :

Function NbVille%(v$, ref$)
Application.Volatile
Dim Fdeb As Object, Ffin As Object, n%
Set Fdeb = Sheets("centralisation")
Set Ffin = Sheets("fin")
For n = Fdeb.Index + 1 To Ffin.Index - 1
    If Sheets(n).Range(ref) = v Then NbVille = NbVille + 1
Next
End Function

Elle est à placer impérativement dans un module standard.

Fichier joint avec en B3 de la feuille "Calcul" la formule =NbVille(A3;"H3")

A+

 

[zebanx]

oui j'avais vu.

mais tes colonnes a et b s'actualise avec les macros, mais les macros ont été créées comment ? ^^"

Les étapes à réaliser :

1. Je ne suis pas certain que les versions ".XLSX" puissent contenir des macros donc il faudrait enregistrer votre fichier en "XLSM" probablement (aux utilisateurs de excel 2007 de confirmer)

2. copier la feuille RECAP sur votre fichier de départ en première position (la macro fait un balayage des sheets(2) à la fin..) sinon m'indiquer si cela vous pose problème, j'amenderais le code.

3. lancer VBE avec ALT+ F11 et dans le bandeau de gauche dit "explorateur de projet VBA" (taper "CTRL + R" s'il n'apparait pas), faire un "drag & drop" du module 1 entre le fichier "compte_ville.xls" et votre fichier. Le module 1 contient toutes les macros utilisées.

4. ALT + F11 pour revenir à votre feuille "recap" sur votre fichier. Pour affecter chaque bouton à la macro correspondante, faire un clic droit sur chaque "shape" (image) et choisir "affecter une macro" : une liste déroulante s'affiche permettant de sélectionner la macro.

5. Pour le TCD, il y a une petite manip.
Il faut revenir à la source (clic droit "assistant tableau croisé dynamique" puis "précédent" jusqu'à afficher la source et supprimer les éléments avant "tabsource") car le nom fait référence au fichier "compter_villes.xls" et non à votre fichier.

...
@+

Edit : bonjour @job75

Juste un essai avec votre fichier du #4

 

[Micalle]

ouf

alors le 1-2-3-4 fonctionnent super sur mon tableau principal de trop d'onglets. j'ai du le passer en xlsm en effet pour cela et faire une feuille recap en macro

toutefois, le point 5 me bloque car l'option de tableau croisé dynamique est grisée sur mon onglet recap, et je ne trouve nul part. quand je fais mon tcd et que je veux le faire apparaitre sur ma feuille recap (en macro) il me dit que c'est impossible.

j'ai donc fait une feuille macro avec ta formule et une feuille normale pour en extraire les données dans un TCD et ça fonctionne.

Admettons maintenant que ce fichier de 2500 onglets existe pour chacun de mes collègues et qu'on souhaite avoir ce tcd finale qui additionne tout les fichiers de tout les collègues en un seul tableau (nous sommes 4). est-ce possible ?

merci enormement en tout cas

 

[zebanx]

Re-

Tant mieux si ça fonctionne.
Je ne m'y connais pas beaucoup en synthèse de fichiers partagés sur un TCD.

Mets peut-être ta dernière ligne en gras et en couleur pour alerter quelqu'un qui serait sur le site.
Après, renseigne ta version utilisée* car peut-être avec power query **on peut facilement faire ce que tu demandes (il y a quelques spécialistes qui t'aideront) sinon prévoir un nouveau fil demain avec cette requête.

Mais :
- 1
Peut-être que sur l'étape 1 de la création du TCD, en choisissant "plages de feuille de calcul avec etiquettes" plutôt que "liste ou base de données microsoft excel" tu peux faire cette addition de tableaux facilement (création manuelle)
- 2
Il y a peut-être des tutos vidéos pour aider sur le sujet sinon ou des sujets qui ont déjà traités ce point comme celui-ci peut-être.

Je risque de te faire perdre du temps donc préfère ne pas t'induire en erreur surtout si tu as une version récente et que quelqu'un peut t'aider à faire ce TCD de manière souple, ce qui est recherché.

@+


* Sur ton profil, dans "détail personnels", il y a une ligne avec "utilise"
** disponible uniquement à partir de excel 2010 sauf erreur

 

[job75]

Re,

Ce n'est pas parce que les TCD sont performants qu'il faut les mettre à toutes les sauces.

Dès le moment où l'on utilise VBA on peut s'en passer, le code de la feuille "recap" :

Private Sub Worksheet_Activate()
Dim ad$, d As Object, n%, x$
ad = "H3" 'adresse des cellules étudiées, à adapter
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
For n = Sheets("Fin").Index + 1 To Sheets("Centralisation").Index - 1
     x = Sheets(n).Range(ad)
     If x <> "" Then d(x) = d(x) + 1
Next
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
If d.Count Then
    [A2].Resize(d.Count) = Application.Transpose(d.keys)
    [B2].Resize(d.Count) = Application.Transpose(d.items)
    [A2].Resize(d.Count, 2).Borders.Weight = xlThin 'bordures
End If
Cells(d.Count + 2, 1).Resize(Rows.Count - d.Count - 1, 2).Delete xlUp 'RAZ en dessous
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

La macro s'exécute quand on active la feuille.

Fichier joint.

A+

 

[chris]

Re à tous

D'autant que là les sources ne sont pas de forme adaptée et, même si c'était le cas, empiler plus de 2500 sources dont le nombre évolue chaque jour n'a pas de sens et dépasse certainement les limites de l'assistant TCD.

On à une seule cellule à considérer par onglet...

Compiler 4 classeurs de 2 à 3000 onglets chacun prend combien de temps en VBA ?

 

[zebanx]

Re à tous

D'autant que là les sources ne sont pas de forme adaptée et, même si c'était le cas, empiler plus de 2500 sources dont le nombre évolue chaque jour n'a pas de sens et dépasse certainement les limites de l'assistant TCD.

On à une seule cellule à considérer par onglet...

Compiler 4 classeurs de 2 à 3000 onglets chacun prend combien de temps en VBA ?

Bonsoir Chris,

Le sujet n'est peut-être plus au stade d'exploitation des 2300 onglets mais de travailler sur 4 onglets récapitulatifs utilisés pour chaque personne (#11).

Si on reprend la dernière proposition de job75, on aurait une ville et un chiffre pour chaque onglet "recap" avec 4 utilisateurs différents donc 4 sources à 2 colonnes pour un TCD final.

Mais il faut déjà connaitre la version de l'utilisateur peut-être (si power query peut traiter cela très rapidement) en considérant aussi la contrainte d'actualisation tous les jours.

@+