Formules Excel durée occupation

Fermé
Louloudelune Messages postés 4 Date d'inscription jeudi 11 mai 2017 Statut Membre Dernière intervention 10 octobre 2017 - Modifié le 9 oct. 2017 à 09:13
Raymond PENTIER Messages postés 56865 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 9 décembre 2022 - 10 oct. 2017 à 18:38
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

Hydr0s Messages postés 1667 Date d'inscription lundi 24 janvier 2011 Statut Membre Dernière intervention 24 août 2022 666
14 mai 2017 à 21:45
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...
0
Raymond PENTIER Messages postés 56865 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 9 décembre 2022 17 187
15 mai 2017 à 03:27
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".
0
Louloudelune Messages postés 4 Date d'inscription jeudi 11 mai 2017 Statut Membre Dernière intervention 10 octobre 2017
8 oct. 2017 à 19:32
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
0
Raymond PENTIER Messages postés 56865 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 9 décembre 2022 17 187
9 oct. 2017 à 18:56
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.
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Louloudelune Messages postés 4 Date d'inscription jeudi 11 mai 2017 Statut Membre Dernière intervention 10 octobre 2017
10 oct. 2017 à 10:25
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
0
Raymond PENTIER Messages postés 56865 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 9 décembre 2022 17 187
10 oct. 2017 à 18:38
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
0