Discussion :

[rag83]

Bonjour à tous,

J'aurais besoin de votre aide pour créer un graphique de fréquentation d'un établissement par semaine et par mois.

J'ai un tableau qui comporte une feuille par mois, avec des rangées pour les clients et des colonnes pour les jours. Le total de fréquentation par semaine est présent, ainsi que le total du mois.

Le problème est que, comme une semaine peut commencer sur un mois et se terminer sur le suivant, je ne sais pas comment récupérer le nombre total de clients de cette semaine pour l'insérer dans le graphique.

Faut-il utiliser du code VBA pour le faire ? Si oui, comment ?

Je mets en exemple un tableau avec deux mois et quelques clients pour illustrer le problème, le problème est présent avec le jeudi 31 octobre et le vendredi 1er novembre, il faut que je puisse récupérer la valeur 13 correspondant au total de fréquentation de cette semaine.

Si quelqu'un peut m'aider, je l'en remercie.

[Menhir]

Tes données sont mal organisées.
Essaimer des données équivalentes sur plusieurs onglets, c'est une mauvaise solution.

Fais un tableau de données sources avec les colonnes : Date, client a, client b, client c, etc.
Ce tableau servira pour tous les mois.

Si tu veux avoir graphiquement des tableaux comme les tiens, tu pourras les générer depuis ces données sources avec des fonctions DECALER().
Personnellement, je ne ferais qu'une seule feuille "Mois" dans laquelle les données s'afficherait en fonction du mois et de l'année renseignés.

Et tu pourras faire tes bilans par semaine et par mois avec de simples fonctions SOMME.SI.ENS().

[rag83]

Bonjour Menhir,

Je te remercie de m'aider.

Je vais modifier mon tableau pour lui faire adopter la structure que tu me conseilles.

Cependant, est-ce que je peux te demander à l'aide de mon exemple de me montrer comment je dois fonctionner avec DECALER() et SOMME.SI.ENS() car je ne sais pas faire ça

[Menhir]

Pour SOMME.SI.ENS(), tu mets en premier paramètre la liste des données à sommer.
Les autres paramètres sont, alternativement, la liste des valeur à scruter pour la condition et le critère.
Dans ton cas, il y aura deux conditions : les bornes supérieures et inférieures de la date.
Lire ça : https://support.office.com/fr-FR/art...6-611cebce642b

Par exemple, si tu veux faire la somme de données en B1:B10 avec des dates en A1:A10 pour un numéro de mois se trouvant en D1 et une année en D2.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI.ENS(B1:B10;A1:A10;">="&DATE(D2;D1;1);A1:A10;"<"&(FIN.MOIS(DATE(D2;D1;1))+1))

Pour la formule qui rempliera tes planning à partir de la liste source, il faudrait savoir plus précisément comment tu vas structurer tes données en terme d'adresse (n° de lignes et de colonnes).
Je pense maintenant qu'un RECHERCHEV() pourrait être suffisant plutôt qu'un DECALER().

[rag83]

J'ai essayé de rentrer les valeurs comme dans l'exemple que tu m'as donné pour appliquer la formule.

Cependant, il y a un message disant que j'ai tapé un nombre d'arguments insuffisant pour cette fonction.

Peux-tu me dire ce qui ne va pas dans le fichier ci-joint sur le 3e onglet ?

[Menhir]

J'ai oublié le second argument de FIN.MOIS.

[rag83]

Ah d'accord, mais quel est cet argument ?

[Menhir]

0

Prends la peine de lire l'aide Excel : https://support.office.com/fr-FR/art...6-f49db127d628

[rag83]

Ok pour l'argument et la page sur la fonction FIN.MOIS.

J'ai réorganisé mon tableau pour lui faire adopter la structure que tu m'as conseillée. Peux-tu me confirmer à l'aide du fichier ci-joint que c'est bien ce qu'il faut faire (onglet 2019) ?

Il faut bien faire figurer tous les jours de l'année dans la même feuille ? Et toutes les années aussi ? Avec cette structure il sera possible de faire un graphique de fréquentation par semaine, par mois et par années ?

Dans la cellule Y2 j'ai adapté ta formule pour afficher la fréquentation du client I en janvier (j'ai essayé en Z2 de compter tous les clients du mois mais il y a une erreur ?)

Par contre je ne vois pas comment utiliser cette formule pour le graphique, je veux dire, est-ce que je dois la répéter pour chaque semaine, pour chaque mois et pour chaque année ? Par exemple, est-ce que je peux sommer la semaine du 28 au 31 janvier avec la ligne du total intercalée au milieu ou il faut l'enlever ?

[Menhir]

Peux-tu me confirmer à l'aide du fichier ci-joint que c'est bien ce qu'il faut faire (onglet 2019) ?

Supprime tes lignes "TOTAL CLIENT".
On ne mélange pas les données et les résultats.

Il faut bien faire figurer tous les jours de l'année dans la même feuille ?
Et toutes les années aussi ?
Avec cette structure il sera possible de faire un graphique de fréquentation par semaine, par mois et par années ?

Oui aux trois

Dans la cellule Y2 j'ai adapté ta formule pour afficher la fréquentation du client I en janvier (j'ai essayé en Z2 de compter tous les clients du mois mais il y a une erreur ?)

En A1:W1, tu recopies le A1:W1 de ta feuille 2019.
En C1, mets "TOTAL CLIENT" parce qu'il est visuellement plus pratique d'avoir le total proche du début qu'au fin fond des données.

Dans une autre feuille (toujours le principe de ne pas mélanger données et résultats), en A2 mets 2019 et en B2 1.
En A3 : =A2+SI(B3=1;1;0).
En B3 = =MOD(B2;12)+1Copie A3:B3 vers le bas autant que nécessaire.
Ca te fera une liste des mois et des années.

En D2, mets la formule.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI.ENS('2019'!D:D;'2019'!$C:$C;">="&DATE($A2;$B2;1);'2019'!$C:$C;"<"&(FIN.MOIS(DATE($A2;$B2;1);0)+1))

Copie D2 vers la droite jusqu'au dernier client.
Copie cette ligne vers le bas jusqu'à la dernière date.

Je te laisse faire la fonction de la colonne C.

Et change le nom de la feuille "2019" puisqu'elle pourra accueillir les données de toutes les années.
Pour éviter d'avoir une liste visuellement trop longue, je te conseille de placer dessus un filtre (Ruban Données > Filtrer) qui te permettra de n'afficher les années que d'une année ou d'un mois souhaité.

Autre conseil : évite les fusions de cellules, ça n'apporte que des problèmes.

[rag83]

En A1:W1, tu recopies le A1:W1 de ta feuille 2019.
En C1, mets "TOTAL CLIENT" parce qu'il est visuellement plus pratique d'avoir le total proche du début qu'au fin fond des données.

Je ne comprends pas ça. Je crée une nouvelle feuille ?

et ensuite encore une autre pour ça :

Dans une autre feuille (toujours le principe de ne pas mélanger données et résultats), en A2 mets 2019 et en B2 1.

?

EDIT : Je mets tout sur une feuille

[Menhir]

Je ne comprends pas ça. Je crée une nouvelle feuille ?
et ensuite encore une autre pour ça :

Non, tu ne crées qu'une seule autre feuille.
J'ai fais des couper/coller qui se sont un peu mélangés dans mon message.

Dans une autre feuille (toujours le principe de ne pas mélanger données et résultats),

En A1:W1, tu recopies le A1:W1 de ta feuille 2019.
En C1, mets "TOTAL CLIENT" parce qu'il est visuellement plus pratique d'avoir le total proche du début qu'au fin fond des données.

En A2 mets 2019 (ou la plus ancienne année de tes données).
En B2 mets 1.
En A3 : =A2+SI(B3=1;1;0).
En B3 = =MOD(B2;12)+1Copie A3:B3 vers le bas autant que nécessaire.
Ca te fera une liste des mois et des années.

En D2, mets la formule.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI.ENS('2019'!D:D;'2019'!$C:$C;">="&DATE($A2;$B2;1);'2019'!$C:$C;"<"&(FIN.MOIS(DATE($A2;$B2;1);0)+1))

Copie D2 vers la droite jusqu'au dernier client.
Copie cette ligne vers le bas jusqu'à la dernière date.

Je te laisse faire la fonction de la colonne C.

Et change le nom de la feuille "2019" puisqu'elle pourra accueillir les données de toutes les années.
Pour éviter d'avoir une liste visuellement trop longue, je te conseille de placer dessus un filtre (Ruban Données > Filtrer) qui te permettra de n'afficher les années que d'une année ou d'un mois souhaité.

Autre conseil : évite les fusions de cellules, ça n'apporte que des problèmes.

[rag83]

J'avais bien mis tout sur la même feuille et ça fonctionne

J'ai mis la somme de toutes les colonnes clients dans la colonne C.

Maintenant pour faire le graphique par mois, j'ai juste à créer un graphique vide et à mettre comme source de données les valeurs des cellules C2 à C13 pour 2019 c'est bien ça ? Où est-ce qu'il y a une autre façon de faire ?
Le graphique va afficher 1 à 12 pour les mois, est-ce qu'on peut afficher les mois en toutes lettres au lieu des chiffres ?

Ça c'est pour les mois, mais comment faire pour avoir les semaines ? (pour la semaine du 28 janvier au 3 février comment on fait ?)

[Menhir]

Maintenant pour faire le graphique par mois, j'ai juste à créer un graphique vide et à mettre comme source de données les valeurs des cellules C2 à C13 pour 2019 c'est bien ça ? Où est-ce qu'il y a une autre façon de faire ?

J'ai conçu le nouvelle feuille en partant du principe que tu voulais faire les sommes sur plusieurs années.
Mais si tu veux n'afficher les données que pour une seule année, tu peux mettre simplement en A3 la formule =A2 et la copier jusqu'à C13.
Il suffira alors de modifier A2 pour avoir les données de l'année désirée.
Dans la mesure où l'ensemble des données sources sont présente sur plusieurs années, les totaux se recalculeront tous seuls.

est-ce qu'on peut afficher les mois en toutes lettres au lieu des chiffres ?

Crée une nouvelle colonne sur la nouvelle feuille, par exemple en insérant une colonne en C.
En C2, mets la formule =DATE(A2;B2;1).
Sélectionne C2 > Clic droit > Format de cellule > Onglet Nombre > Catégorie Personnalisée > Type : "mmmm"
Copie C2 vers le bas autant que nécessaire.

Ça c'est pour les mois, mais comment faire pour avoir les semaines ? (pour la semaine du 28 janvier au 3 février comment on fait ?)

Les semaines, c'est toujours un peu compliqué à gérer.
Je vais te proposer une solution qui n'est pas la plus élégante mais qui est simple.

Dans ta feuille de données sources, insère une nouvelle colonne en C (donc la colonne DATE devient D).
En C2, mets la formule =NO.SEMAINE(D2;2). Attention, le code "2" dans la formule part du principe que le 1er janvier est en semaine 1. C'est un principe anglophone. En France, on considère que la semaine 1 est la première semaine de l'année à avoir au moins 4 jours partant du lundi et donc le 1er janvier peut être considéré en semaine 52 ou 53. Si c'est ce principe dans ton entreprise, il faudra changer ce code.
Lire ça : https://support.office.com/fr-FR/art...1-b18c13c75340

Tu supprimes la fusion de cellules en colonne A (de toute façon, la fusion de cellule, ça n'apporte que des problèmes).
En A2 tu mets la formule : =ANNEE(D2).

Tant qu'on y est, fais pareil pour la colonne B (même si ce n'est pas indispensable) en utilisant la fonction MOIS().

Copie A2:C2 vers le bas autant qu'il y a de données.

Tu copies la feuille que l'on a créé pour les mois.

Dans cette nouvelle feuille, en colonne B, tu mets les numéros de semaine.

En D2, tu mets la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI.ENS('2019'!E:E;'2019'!$C:$C;$B2;'2019'!$A:$A;$A2)

Tu copies D2 pour l'ensemble des clients et des semaines.

Intéro surprise :
Si tu as compris le principe de cette nouvelle formule, tu devrais pouvoir simplifier de la même façon la formule crée dans la feuille mois grace à ce qu'on a fait à la colonne B de la feuille de données sources.
La formule serait quasiment identique à deux caractères près.

Entraine-toi.

[rag83]

J'ai conçu le nouvelle feuille en partant du principe que tu voulais faire les sommes sur plusieurs années.

C'est le cas, c'est juste que je ne peux m’empêcher de me poser tout un tas de questions

OK pour le mois en lettres.

Avant de passer à l'interro surprise, je ne comprends pas ceci :

Tu copies la feuille que l'on a créé pour les mois.

Dans cette nouvelle feuille, en colonne B, tu mets les numéros de semaine.

Je mets les semaines à la place des mois, ou je fais une colonne en plus ? car il y a plusieurs semaines par mois.

[Menhir]

Je mets les semaines à la place des mois, ou je fais une colonne en plus ? car il y a plusieurs semaines par mois.

Il y aura une feuille de bilan par mois et une autre par semaine.
La première qui a été réalisée était pour les mois.
Pour celle des semaines, copie celle des mois et remplace la colonne Mois par une colonne Semaine. Elle ne vient pas en plus mais en lieu et place.

[rag83]

Désolé mais je ne vois toujours pas...

Je duplique la feuille mois et je remplace la colonne mois par la colonne semaine dans la nouvelle feuille.

Mais elle contient quoi la colonne semaine ? les numéros de semaine que je mets à la main ??? Il devrait y avoir une ligne par semaine non ? Mais je n'ai ça nulle part. Il y a une formule du même genre que =MOD(B2;12)+1 pour les semaines ?

[Menhir]

Mais elle contient quoi la colonne semaine ? les numéros de semaine que je mets à la main ???

Oui.
Ou tu mets une formule simple : "1" dans la première ligne et "ligne précédente + 1" dans les lignes suivantes.

Il y a une formule du même genre que =MOD(B2;12)+1 pour les semaines ?

Etant donné que le nombre de semaines par an est différent d'une année sur l'autre (51, 52 ou 53), il est difficile de faire une telle formule.
Sans compter que, contrairement aux mois, une semaine peut appartenir à deux années différentes.

Donc, plutôt que de partir sur des formules extrêmement compliquées pour peu de gain, Je te conseille de mettre la formule que je cite ci-dessus et de corriger à la main les cas particuliers.
Après tout, faire une liste de semaine une fois par an, ça ne sera pas la mer à boire.

[rag83]

Ok pour les semaines, c'est fait.

Par contre j'ai une erreur dans la case D2 avec la formule pour les semaines. Qu'est ce qui ne va pas ?

EDIT : c'est bon, j’avais modifié le nom d'une feuille

[rag83]

Avant de passer à quelques questions sur la mise en forme des graphiques, j'ai lu ce que tu as écrit à propos des numéros de semaine mais j'ai un peu de mal à comprendre car je vois que le lundi 30 et le mardi 31 décembre 2019 font partie de la semaine 53 et que le mercredi 1er janvier 2020 est dans la semaine 1. Est-ce que c'est normal que la semaine 53 fasse 2 jours ? Même sans tenir compte de la numérotation, elle ne devrait pas faire 7 jours dans tous les cas ?

Sur les graphiques que j'ai commencé à faire, dans le fichier ci-joint, sur la feuille "bilan mois", il y a les mois sur l'axe horizontal. Je les ai mis en lettres avec la colonne qui les affiche comme ça, mais comment faire pour afficher aussi l'année (janv 2019) ? car dans le cas d'un graphique sur plusieurs années comme c'est le cas ici c'est nécessaire.

Même chose pour le graphique des semaines sur la feuille "bilan semaine", au lieu des numéros de semaine, est-ce qu'on peut afficher "semaine du 7 au 14" ? Je pense qu'il faut faire comme sur la feuille des mois, c'est-à-dire insérer une colonne supplémentaire qui affichera ça en toutes lettres mais je ne vois pas comment.