Report de résultats pour graphs

Bonjour à tous,

J'ai développé ce petit fichier (joint) pour ma conjointe qui gère une partie hôtellerie.

Je me heurte à trois problématiques, je souhaiterais pouvoir lui créer des graphiques et conserver certaines statistiques sur la feuille bilan, ces statistiques sont évolutives au fil de l'année.

J'arrive à avoir c'est statistiques sur la feuille planning (sauf pour le taux d'occupation où ce n'est pas toujours exact car ma formule ne s'adapte pas au nombre de jours donc mon résultat n'est pas toujours réel)

Voici mes problèmes :

- Avoir une formule qui s'adapte aux jours du mois

ex : pour janvier j'ai 31 jours donc je fais le calcul =MOYENNE($B22:$AF22) mais pour février il faudrait faire =MOYENNE($B22:$AC22) et AD22 si année bissextile.

- Reporter le calcul de nuitées de la feuille planning en K25 vers la feuille bilan en C17 pour janvier et ainsi de suite et que la mise à jour et le transfert se fasse automatiquement (formule ? VBA ?)

- Même problématique que pour les nuitées mais pour le taux d'occupation de la feuille planning vers la feuille bilan

Pour info, j'ai également un "bug" dans le rafraîchissement du VBA de la feuille planning pour faire apparaître les lignes ajoutées à partir de la feuille saisie

Dans l'idéal j'aimerais qu'elle puisse utiliser le fichier d'année en année sans avoir à modifier les formules.

Cordialement

Bonjour

je pense que pour répondre à ta problématique, tu aurais pu passer par un tcd, mais malheureusement ta base dans sa structure actuelle ne le permet pas.

Peut-être si tu changes ta strucure (jute au niveau des titres, alors tu pourras bénéficier de toute la puissance qu'offre les TCD.

Es-tu prêt à modifier la structure ?

CORDIA

bonjour

salut CORDIAS

en effet, les onglets sont des copies de feuilles de papier à remplir au crayon. Excel a beaucoup de mal à faire de l'Excel.

Bonjour à tous,

Voici une mise à jour de mon fichier en mode colonne

@CORDIA5, aucun problème pour modifier la structure et utiliser du croisé dynamique, cependant, je ne vois pas comment faire pour calculer les pourcentage et nuitées / mois

Autre question, comment afficher le total de chaque croisé dynamique dans la feuille graphiques ?

Cordialement

Avec quelques petits graphs en plus

Je cherche dans le feuille graphique à :

• Masquer les 0 si cellules vides
• Trouver un meilleur moyen d'effectuer mes calculs mensuels pour le nombre de nuitées et le taux d'occupation
• Pouvoir calculer le chiffre d'affaires par mois

Merci pour votre aide

Cdlt

Bonsoir

ton fichier semble avoir un souci.

Passes le à l'antivirus et retransmets-le .

CORDIA5

Bonsoir,

L'antivirus n'a rien trouvé

Pour info, je souhaite aussi calculer la taxe de séjour par mois

Cdlt

Bonjour,

Concernant les graphiques, pour que les 0 n'apparaissent pas, il faut les transformer en #N/A. C'est ce que j'ai fait dans l'onglet Bilan dans les formules et j'ai ajouté une MFC pour ne pas faire apparaitre ces #N/A. J'ai aussi modifié tes formules pour éviter les références circulaires. Le souci, maintenant, c'est que tes formules en D14 et H14 ne fonctionnent plus.

Pour ton problème de rafraichissement, j'ai modifié la macro :

Private Sub Worksheet_Activate()
  maj
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "$B$2" Then
For Each n In [B4:AF4]
If n = 0 Then
n.Columns.Hidden = True
Else
n.Columns.Hidden = False
End If
Next
End If
maj
End Sub

Sub maj()
Application.ScreenUpdating = False
Cells.EntireRow.Hidden = False
For Each cel In Feuil2.Range("AG6:AG20")
If cel = 0 Then cel.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub

Quant à la question du chiffre d'affaire, j'ai ajouté une colonne dans l'onglet Bilan. Je ne sais pas si c'est ce que tu attends.

PS : je ne comprends pas pourquoi dans le planning les jours de sortie ont des couleurs.

A+

Bonsoir Shakki,

Merci pour votre aide, avez-vous une idée pour le calcul de la taxe de séjour au mois ?

Pour le reste merci infiniment c'est génial !, je pense régler le problème en D14 et H14 avec une fonction décaler peut-être.

C'est étrange, de mon côté les dates de sorties sont tout à fait normales

Cdlt

Bonjour,

Concernant les dates de séjour sur l'onglet planning, je voulais dire que si on réserve du 17 au 18 janvier, on reste 1 jour. De mon point de vue, le 17 devrait donc être seulement en couleur, or sur le tableau les 2 jours sont en couleur. Maintenant, c'est pas bien grave puisque le calcul des nuitées en AG est correct.

Pour la taxe séjour, SOMMEPROD devrait faire l'affaire. Cf fichier joint.

A+

Bonjour Shakki,

Merci de nouveau pour votre aide, j'avais réussi à conditionner ma formule mais je n'avais pas eu le temps de poster, désolé ...

J'ai constaté un petit souci dans la formule de taux d'occupation dans la feuille planning pour le mois de février, en effet, le résultat devrait être 3,6% et non 3,2%, je vais essayer de conditionner la formule uniquement pour Février.

Cdlt

Re,

Pour tes formules total nuitées et taux d'occupation moyen / mois sur l'onglet planning, je te propose :

=SOMME(INDIRECT("B21:"&ADRESSE(21;JOUR(DATE(An;MOIS(B4)+1;0))+1)))

=MOYENNE(INDIRECT("B22:"&ADRESSE(22;JOUR(DATE(An;MOIS(B4)+1;0))+1)))

A+

Re,

J'avais commencé cela mais au bout d'un moment cela coince trop d'itération

=SI($B$2="Février";MOYENNE(INDIRECT("$B22:$A"&CAR(64+EQUIV(9^9;A4:AC4;1)-26)&"22"));SI($B$2="Avril";MOYENNE(INDIRECT("$B22:$A"&CAR(64+EQUIV(9^9;A4:AE4;1)-26)&"22"));SI($B$2="Juin";MOYENNE(INDIRECT("$B22:$A"&CAR(64+EQUIV(9^9;A4:AE4;1)-26)&"22"));SI($B$2="Septembre";MOYENNE(INDIRECT("$B22:$A"&CAR(64+EQUIV(9^9;A4:AE4;1)-26)&"22"));MOYENNE(INDIRECT("$B22:$A"&CAR(64+EQUIV(9^9;A4:AF4;1)-26)&"22"))))))

Je vais tester vos formules et vous tiens au courant

Merci encore

Cdlt

Bonsoir à tous,

J'ai réalisé quelques tests et j'ai constaté que notamment lorsque j'entre des données en fin de mois et en début (ex mai/juin), si les données d'un groupe sont à cheval sur deux mois, les valeurs ne sont pas correctement comptabilisées dans le bilan.

Je n'arrive pas à déterminer pourquoi, cependant, ce problème est présent sur tous les mois.

Je vous joins le fichier avec mes tests.

Cdlt

Bonjour,

Je pense avoir résolu le problème pour les nuitées et taxes séjour dans le bilan annuel. J'ai dû ajouter 2 colonnes sur l'onglet saisie réservation en AF et AG afin de déterminer les nuitées du mois et celles du mois M+1.

Par contre, je ne vois pas du tout comment faire pour le chiffre d'affaire. J'ai l'impression que tu répartis parfois les nuitées en différents régimes (exemple groupe 2, ligne 3). Si la date d'arrivée et la date de départ se situent dans le même mois, ça ne pose pas de problème, on va directement chercher la colonne AC. Mais si on est à cheval sur 2 mois, je sais pas comment procéder...

A+

Bonsoir Shakki,

Merci encore pour tout, vos formules fonctionnent à merveille !

Pour répondre à vos précédente question, au niveau de la couleur de la date du départ, si l'on prend l'exemple du groupe 2 au mois de Mai, ce groupe qui l'hôtel le samedi 06 au matin, c'est donc pourquoi ce jour est tout de même coloré mais le nombre de personne n'est pas pris en compte étant donné que le calcul se fait par nuit, ma femme a toujours travaillé de cette façon depuis 17 ans, je vais éviter de trop la perturber lol

Pour le chiffre d'affaire, en effet, un groupe qui vient par exemple 4 jours, peut décider de prendre 3 jours en pension complète et 1 jour en demi-pension, ce qui engendre donc un calcul et donne un montant en € par réservation, je pense que l'on pourrait faire un ratio du chiffre d'affaire / nuit c'est à dire si l'on prend la ligne 12 de l'onglet saisie le groupe 9 rapporte 7306 € pour 4 nuits dont 3 en mai et 1 en juin, ce qui fera donc (7627/4)*3 pour mai et (7627/4)*1 pour juin.

Autre souci, je n'arrive pas à mettre à jour automatiquement les graphs TCD en automatique dès saisie, sans doute un petit bout de VBA ferait l'affaire, j'aurais également besoin d'une message box d'alerte si la capacité d'accueil est supérieure à 60 pax/jours dans la feuille planning.

Cordialement

Re,

J'ai, comme tu l'as suggéré, fait un prorata du CA pour les cas à cheval entre 2 mois. Il y a donc 2 colonnes supplémentaires sur l'onglet Saisies réservations pour calculer le CA par mois.

Concernant tes autres demandes, sache tout d'abord que je suis plus à l'aise avec les formules qu'avec VBA. Pour le rafraichissement des TDC via VBA, je pense que c'est OK. Par contre, pour les msgbox, j'ai beau essayé, je n'arrive pas à grand chose.

J'espère que quelqu'un maitrisant VBA pourra répondre à ta demande.

A+

Bonsoir Shakki,

Merci beaucoup pour tout !

Juste une dernière chose, en faisant quelques test de fin, je me suis aperçu que dans la feuille planning la nuit du dernier jour du mois n'est pas prise en compte dans le calcul du nombre de nuit colonne AG comme par exemple pour le mois de Janvier pour le groupe 7, le résultat devrait être 2 et non 1 comme affiché, cependant, pour le reste tout est ok.

Pour ce qui est de la msgbox, je verrais cela plus tard, ce n'est pas indispensable

Milles mercis encore

Cdlt

Bonjour,

J'ai revu ta formule en AG pour qu'elle prennent en compte tous les jours. Elle va maintenant chercher le résultat dans la colonne AF de l'onglet saisie.

J'ai aussi ajouté une macro pour avoir un message si le nombre de pax est supérieur à 60. Je n'ai pas réussi à créer un seul message qui liste tous les jours problématiques en une seule fois. Il y a donc autant de messages qu'il y a de cellules > 60. J'espère que ça te conviendra.

A+

Bonjour Shakki,

Merci beaucoup pour tout, le hic c'est que la formule de calcul de nuits sur la feuille planning, n'arrive pas à prendre en compte les réservations à cheval sur deux mois, du coup sur février, certaines réservations n'apparaissent pas au planning.

Cdlt