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 58392 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 22 avril 2024 - 10 oct. 2017 à 18:38
Raymond PENTIER Messages postés 58392 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 22 avril 2024 - 10 oct. 2017 à 18:38
A voir également:
- Calculer taux d'occupation excel
- Liste déroulante excel - Guide
- Calculer une moyenne sur excel - Guide
- Formule excel - Guide
- Si et excel - Guide
- Aller à la ligne excel - Guide
6 réponses
Hydr0s
Messages postés
1678
Date d'inscription
lundi 24 janvier 2011
Statut
Membre
Dernière intervention
24 août 2022
672
14 mai 2017 à 21:45
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 :
En résumé, il faut faire les calcul en utilisant les 6 conditions. J'ai pris les fonctions suivantes
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
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
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...
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 avant | 0 | Sortie - 01/01/XX + 1 | 31/12/XX - 01/01/XX +1 |
| Entrée pendant | Impossible | Sortie - Entrée | 31/12/XX - Entrée + 1 |
| Entrée après | Impossible | Impossible | 0 |
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...
Raymond PENTIER
Messages postés
58392
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
22 avril 2024
17 095
15 mai 2017 à 03:27
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".
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".
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
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
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
Raymond PENTIER
Messages postés
58392
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
22 avril 2024
17 095
9 oct. 2017 à 18:56
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 ...
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.
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
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
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
Raymond PENTIER
Messages postés
58392
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
22 avril 2024
17 095
10 oct. 2017 à 18:38
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
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
