XL 2016 Fusion de deux tableaux dans un fichier unique

jpb388

XLDnaute Accro

• 27 Décembre 2017

• #2

Bonjour à tous
macro modifiée dans feuille 2
regarde et dis moi si cela te va
pour les somprod je ne suis pas compétant
bonne journée

 

Pièces jointes

• Planning Charge Hybride.xlsm

  197.3 KB · Affichages: 63

Vote positif 0 Vote négatif

Fabien62

XLDnaute Occasionnel

• 27 Décembre 2017

• #3

Bonsoir jpb388,

Tout d'abord merci beaucoup pour votre aide !

C'est exactement ce qu'il me faut en VBA, juste deux petites choses à corriger, la couleur des statuts "Convention" apparaît en bleu dans l'onglet planning alors qu'il devrait être vert et aussi les lignes vides qui ne se masquent pas.

J'ai essayé par moi même mais je ne maîtrise pas le VAB à ce niveau, pour les sommeprod, j'essai pas mal de chose mais sans succès pour le moment.

Cordialement

 

Vote positif 0 Vote négatif

jpb388

XLDnaute Accro

• 27 Décembre 2017

• #4

Re
rectification effectuée

 

Pièces jointes

• Planning Charge Hybride.xlsm

  198.5 KB · Affichages: 58

Vote positif 0 Vote négatif

Fabien62

XLDnaute Occasionnel

• 27 Décembre 2017

• #5

Super, encore merci !

Je suis désolé vraiment, je viens de m'apercevoir que dans l'onglet planning les colonnes des jours inexistants par exemple 30 Février, 31 Avril ne se masquent plus comme avant, cela fonctionnait aussi pour les années bisextile.

J'avais aussi un screenupdate (Application.ScreenUpdating = True), je ne sais pas si on peux la réintégrer, cela m'éviter de changer feuille par exemple lorsque je changeais de mois dans le planning.

Ceci était géré par ce bout de code :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
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()

End Sub
Application.ScreenUpdating = False

Cells.EntireRow.Hidden = False
For Each Cel In Feuil2.Range("AG6:AG30")
If Cel.Text = 0 Then Cel.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True

End Sub

J'ai tenté de le réactiver, malheureusement, j'ai une erreur, je vais essayé de trouver

Cordialement

 

Dernière édition: 27 Décembre 2017

Vote positif 0 Vote négatif

jpb388

XLDnaute Accro

• 28 Décembre 2017

• #6

Bonjour à tous
maj fait
pour ton bilan si tu me dis exactement ou trouver les données je peux essayer de remplacer tes sommeprod par une macro
Bonne journée

 

Pièces jointes

• Planning Charge Hybride.xlsm

  198.7 KB · Affichages: 88

Vote positif 0 Vote négatif

Fabien62

XLDnaute Occasionnel

• 28 Décembre 2017

• #7

Bonjour jpb388,
Un grand merci pour tout en tout cas déjà !

Pour les sommeprod tout est déclaré dans le gestionnaire des noms

Par exemple, pour cette formule située sur l'onglet "Planning" =SOMMEPROD((B$5>=Debut)*(B$5<Fin)*(Profil<>"Annulé")*(Profil<>"Sans suite")*Pax)

Toutes les données viennent de l'onglet "Saisie réservations"

Debut fait référence à :
=DECALER('Saisie réservations'!$B$2;;;NBVAL('Saisie réservations'!$B:$B)-1)

Fin à :
=DECALER('Saisie réservations'!$C$2;;;NBVAL('Saisie réservations'!$C:$C)-1)

Et ainsi de suite


Pour l'onglet "Bilan annuel", Les SOMMEPROD récupèrent les données de l'onglet "Saisie réservations"


Est-il possible de protéger les formules de l'onglet "Saisie réservation" et "Planning" avec une macro afin d'éviter un effacement accidentel ?

Merci beaucoup

Cordialement

 

Vote positif 0 Vote négatif

jpb388

XLDnaute Accro

• 28 Décembre 2017

• #8

Re
les nuitées correspondent aux critères Convention et demi convention
les taxes correspondent à la colonne Coût taxe séjour selon les critères ci dessus
le chiffre d'affaire correspond à la colonne Montant total en € avec les même critères
Ai je bien compris ?

 

Pièces jointes

• Planning Charge Hybride.xlsm

  200.8 KB · Affichages: 58

Vote positif 0 Vote négatif

Fabien62

XLDnaute Occasionnel

• 28 Décembre 2017

• #9

Bonsoir,

Sont comptabilisé dans la tableau annuel les nuitées qui ne sont pas annulées ou sans suite, attention si une réservation est à cheval sur 2 mois il faut faire le prorata (ex pour le client 4, du 31/01 au 03/02 = 1/3 des nuitées de 36 soit 12 doit être mise en janvier et le reste 24 en février, les colonnes AF et AG sont utilisées pour la division.

Les taxes de séjours correspondent bien au coût taxe de séjour, la encore il faut faire le prorata (ex : pour le client 4 1/3 de 18€ soit 6€ en janvier et 2/3 soit 12€ sur février, utilisation des colonnes AF et AG.

Même principe pour le chiffre d'affaire qui correspond bien à la colonne AC Montant total en €, là encore il faut faire le prorata sur les mois, celui-ci est fait en colonne AH et AI

Il faut faire le cumul des 2 tableaux, je devrais avoir :

JANVIER 304 nuitées (100+110+82+12) 13€ de taxe (2+5+6) Montant 11839€ (2784+4394+4043+618)
FEVRIER 24 nuitées 12€ de taxe Montant 1236€

Dans l'onglet planning, on répercute juste le nombre de pax/jour de réservation et on les cumules si il y a plusieurs réservations le même jour.

Merci beaucoup

Cordialement

 

Vote positif 0 Vote négatif

jpb388

XLDnaute Accro

• 29 Décembre 2017

• #10

Bonjour à tous
ton explication + ton exemple c'est parfait
pour le taux d'occupation je t'ai fait une fonction personnalisée (prise en charge 28 ou 29 février)
Bonne journée

 

Pièces jointes

• Planning Charge Hybride.xlsm

  208.1 KB · Affichages: 56

Vote positif 0 Vote négatif

Fabien62

XLDnaute Occasionnel

• 29 Décembre 2017

• #11

Bonjour,

Super merci beaucoup, je vais faire quelques test et je vous tiens informé, concernant la partie planning, est qu'il est possible de faire quelque chose pour le nombre de Pax/jour ?

Cordialement

 

Vote positif 0 Vote négatif

jpb388

XLDnaute Accro

• 29 Décembre 2017

• #12

Re
en principe oui si tu me fais la même explication car comme déjà dis plus haut je ne maitrise pas les formules un peu évoluées alors les sommeprod c'est de l'hébreu pour moi
Bonne soirée

 

Vote positif 0 Vote négatif

Fabien62

XLDnaute Occasionnel

• 29 Décembre 2017

• #13

Re,

Alors pour moi c'est le VBA qui est trop compliqué

Donc pour l'onglet planning concernant les Pax/jour le calcul se fait en ligne 31, on prend le nombre de pax en colonne J puis on le répercute sur chaque nuit, si il y a plusieurs réservations à cheval sur le même jour on cumul les pax/jour de chaque réservation.

Ex pour le client 1 j'ai 50 pax pour 2 nuits du 01/1 au 03/1, j'ai donc 50 qui se met le 1 et 2 (pas le 3 car c'est le jour du départ, ils ne seront pas là la nuit de ce jour)

Même chose avec le client 2 sauf que là j'ai 55 pax, ils se cumulent au 50 du client 1 car ce sont les même dates, si il y avait un jour de décalage ex du 2 au 4, j'aurais eu 105 pax le 2 et 55 pax le 3 mais rien le 4.


Je souhaiterais faire une version avec un onglet par tableau, j'ai commencé à mettre en forme, pour l'onglet planning j'ai modifié votre macro pour qu'elle fonctionne sur les deux onglets cumulés, or au niveau du bilan je ne sais pas modifié et pour les onglets de taxes, je ne sais pas faire.

Je fais cette seconde version, je laisserais choisir ma femme, celle qui lui convient le mieux (je vous joins le fichier).

Merci infiniment pour tout

Cordialement

 

Pièces jointes

• Planning Charge Hybride Test 2 Onglets - Copie.xlsm

  204.8 KB · Affichages: 54

Vote positif 0 Vote négatif

jpb388

XLDnaute Accro

• 30 Décembre 2017

• #14

Bonsoir à tous
pour les bilan c'est OK
pour les taxes besoin de renseignement
séjour du=colonne 2
séjour au =colonne 3
Nb Personne= ??
Nb Nuitée= colonne 4
Nb de personne totale =??
Bonne soirée

 

Vote positif 0 Vote négatif

Fabien62

XLDnaute Occasionnel

• 30 Décembre 2017

• #15

Bonsoir jpb388,

Nb Personne= colonne AA
Nb de personne totale = colonne J

J'ai réussi à résoudre les sommeprod, y compris pour l'onglet Planning et l'onglet Bilan j'ai quand même cherché de mon côté, cela me fait de l'entraînement mais j'utiliserais le VBA

Merci beaucoup

Cordialement

 

Pièces jointes

• Planning Charge Sommeprod OK.xlsm

  207.2 KB · Affichages: 58

Vote positif 0 Vote négatif