Formules Excel durée occupation [Fermé]

Signaler
Messages postés
4
Date d'inscription
jeudi 11 mai 2017
Statut
Membre
Dernière intervention
10 octobre 2017
-
Messages postés
52028
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
1 août 2020
-
Bonjour,

Je dois parvenir à calculer par logement, un taux d'occupation par année, mais aussi pour l'année en cours Ôô

C'est à dire la ou les durées où il y avait un résident - la ou les durées où il n'était pas occupé / 365,25 J.

Cela se complique terriblement dans mon cerveau, qu'il me faut en plus déterminer ce taux d'occupation pour l'année 2016 donc entre le 1er et le 31 décembre quelle que soit l'année d'entrée dans les lieux du locataire.
S'il est rentré en 2010, je ne dois pas prendre en compte la globalité du nombre de jours.
Puis ensuite, il me faut déterminer le taux du 1er janvier 2017 à =AUJOURDHUI() pour l'avoir à date tout au long de l'année en cours.

Quelqu'un pourrait m'éclairer sur une formule ??? J'avoue me faire des noeuds au cerveau.

Lou


6 réponses

Messages postés
1658
Date d'inscription
lundi 24 janvier 2011
Statut
Membre
Dernière intervention
22 juin 2020
452
Salut,

Pour calculer, en admettant que tu aies les dates d'entrée et de sorties de chaque résident ainsi que le logement qu'il a occupé, pour chaque année, il y a 6 cas :

Sortie avant l'année Sortie pendant Sortie après
Entrée avant0Sortie - 01/01/XX + 131/12/XX - 01/01/XX +1
Entrée pendantImpossibleSortie - Entrée31/12/XX - Entrée + 1
Entrée aprèsImpossibleImpossible0

En résumé, il faut faire les calcul en utilisant les 6 conditions. J'ai pris les fonctions suivantes
  • SI(cond;resulvrai;resultfaux) en les empilants pour remplir mes critères
  • DATE(annee;jours;mois) pour générer des dates et puis on s'en sort avec une formule de 10km (voir fichier excel).
  • SOMME.SI(cellcritère;critère;cellsomme)

Au cas où le fichier excel disparaîtrait, un aperçu des formules qui sont bien dégueu :
Pour le nombre de jours dans une année avec C5 Entree, D5 Sortie et E4 l'année
=SI($C5<DATE($E$4;1;1);SI($D5<DATE($E$4;1;1);0;SI($D5<DATE($E$4+1;1;1);$D5-DATE($E$4;1;1)+1;DATE($E$4+1;1;1)-DATE($E$4;1;1)));SI($C5>DATE($E$4;12;31);0;SI($D5<DATE($E$4+1;1;1);$D5-$C5;DATE($E$4+1;1;1)-$C5)))

Pour le taux d'occupation, avec col A le nom des logements, N5 le logement et colonne E le nombre de jour d'occupation du logement par résident et par date
=SOMME.SI($A$5:$A$34;$N$5;E$5:E$34)/365


Voici un fichier d'exemple, les valeurs d'entrée et de sorties étant aléatoires, le taux d'occupation peut être supérieur à 1... Avec l'exemple :
(Dropbox) Tableur_occupation.xlsx
(et je viens de me rendre compte que j'ai oublié les +1 partout mais la flemme de recommencer ^^)

Si quelqu'un a une solution plus simple, peut-être avec les matrices, je suis preneur également...
Messages postés
52028
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
1 août 2020
13 776
Bonjour Louloudelune, et sois la bienvenue parmi les membres de CCM.

Il faudrait, pour commencer, que tu te mettes d'accord avec toi-même : Il s'agit de 2016 ou de 2017 ?
"il me faut déterminer ce taux d'occupation pour l'année 2016 donc entre le 1er et le 31 décembre quelle que soit l'année d'entrée dans les lieux du locataire ... Puis il me faut déterminer le taux du 1er janvier 2017 à =AUJOURDHUI() pour l'avoir à date tout au long de l'année en cours".
Messages postés
4
Date d'inscription
jeudi 11 mai 2017
Statut
Membre
Dernière intervention
10 octobre 2017

Bonsoir,
Tout d'abord désolée de ne pas vous avoir répondu avant, mais ce que je suis en train de construire est "titanesque" :)
Pour vous répondre à vous 2, en fait j'ai des dates d'occupation, qui peuvent commencer en fin d'un exercice (par exemple : libéré le 24.11.2016, puis occupé en 2017 que le 22.02.2017), je ne devrais prendre pour chaque exercice, que la période comprise entre le 1er et le 31.12. de l'année.

Soit en 2016,
toutes les périodes où l'appartement n'aura pas été occupé sur tout l'exercice 2016, jusqu'à la dernière date d'occupation, si et seulement si elle commence en 2016, ou alors jusqu'au 31.12.2016 si l'appartement n'est reloué qu'en 2017)
et sur 2017, si le logement n'a été réoccupé qu'au mois de février, je ne devrais prendre la vacances qu'à partir du 1er janvier 2017.

Par ailleurs, si le logement sur 2017, a été occupé X fois, et qu'à la date d'aujourd'hui, il n'y a pas de locataire, ajouter à ces périodes cumulées de vacances, le fait que la dernière en date est évolutive,
imaginons, la dernière fois libéré le 15.09.2017 mais toujours pas loué, alors décompte avec la formule =AUJOURDHUI() pour le dernier calcul de vacances à cumuler.

J'ignore si je suis claire Ôo
Je vous mets un extrait de fichier, en guise de simulation.
Merci pour toute aide !


https://www.dropbox.com/s/lowp2scpao4jwcj/Excel%20calcul%20vacances.xlsx?dl=0
Messages postés
52028
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
1 août 2020
13 776
Bonjour.

Désolé, mais je n'ai pas tout compris !

- En H2, à la place de "VACANCE Date Sortie Résident suivant" je m'attendais à trouver "VACANCE Date Sortie Résident intermédiaire" ...

- En J, à quoi se rapporte le oui ou le non ? à la situation aujourd'hui ?

- Dans ta formule en L12, il y a vacance de l'appartement 17 du 01/01/2017 au 13/04/207; puis du 22/06/2017 à aujourd'hui. Ce n'est pas du tout ce qu'exprime ta formule ...
 --> à ta place, je ferais une ligne pour chaque mouvement d'occupation ; concrètement, la chambre 17 ferait l'objet de 3 lignes : 
une pour la période se terminant le 31/08/2016, une pour la période du 02/02 au 13/04/2017, une pour la période commençant le 22/06/2017.
Messages postés
4
Date d'inscription
jeudi 11 mai 2017
Statut
Membre
Dernière intervention
10 octobre 2017

Bonjour Raymond :)
Merci pour ta réponse, en fait je vais faire une capture écran.
Ma Base de données recense par ligne, les "occupants", je ne peux pas faire une ligne par logement.
Tu verras que j'ai parfois 3 lignes pour 1 même appartement, car il y a "X" occupants par période d'occupation.
En fait ma problématique est celle-ci.
Si le logement a été inoccupé du mois de novembre 2016 à février 2017, je dois calculer la 1ère période de vacances du 1er janvier 2017 à février 2017, puis cumuler toutes les autres vacances.

Ma seconde problématique est celle-ci, une fois ce premier calcul effectué, il faut que ma formule tienne compte aussi du fait qu'au jour "J" où j'ouvre mon fichier, si la dernière sortie remonte au 31.09.2017 par exemple, et que nous sommes le 10.10, elle devra tenir compte de ces 10 jours en plus, puisque le logement est libre.
Voilà pourquoi j'ai une colonne oui non (pour dire si le logement est libre ou non) et pouvoir mettre une autre condition, si logement libre, alors calculer en plus la dernière vacance à la date d'aujourdhui.

Mais en plus là où ça se complique encore plus, c'est qu'imaginons que nous soyons le 5 janvier 2018, que le logement libre au 31.09 le soit toujours, il faudra que mon décompte s'arrête au 31.12.2017
Tu vois le "schmilblick" ôO, ça me rend diiiiiiiiinnnnnngue .... lol...
Voici une capture image de mon fichier, car j'ai vu dans Dropbox que les dates avaient été inversées et incomplètes au moment du transfert ôO

Merci pour ton aide Raymond :)
https://www.dropbox.com/s/eg5gv6pf90x0dee/Vacances%20formule%20%C3%A0%20trouver.JPG?dl=0
Messages postés
52028
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
1 août 2020
13 776
Chère Louloudelune, ce qui rend le problème compliqué, c'est que tu fais tout pour rendre compliquée ton explication ...
Ainsi, les exemples décrits dans tes messages ne correspondent pas vraiment à tes fichiers (fichier Excel du post initial et fichier image du dernier post) !
En effet le problème est simplement :
"Pendant combien de jours l'appartement a-t'il été inoccupé en 2017 ?"
L'unique complication est qu'un même appartement pouvant avoir plusieurs occupants avec des dates différentes, ton planning peut avoir plusieurs lignes pour un seul appartement ! Ce n'est d'ailleurs pas visible sur ta capture image ...
Et il manque une information importante : Combien de séjours peuvent se succéder dans l'année, par appartement, c'est-à-dire combien de résidents à la suite l'un de l'autre ? Dans tes exemples le maximum est de 3, mais est-il envisageable d'en avoir 4 ou 5 ?
Par contre si un couple occupe un appartement, tu n'as bien qu'une seule ligne ? Et il n'arrive jamais qu'un même occupant vienne deux fois dans ton institution ?
Alors explique-moi le cas d'Alain, logement N° 29, qui sort le 15/05/2017, puis qui revient du 16/06 au 31/09 ...

Je te proposerais ce tableau, avec le nom défini "début2017" qui renvoie la date du 01/01/17 : https://www.cjoint.com/c/GJkqItcEZck