[XL-2003] Calculs des prix de loyers d'habitation 2011.

[Angélique83]

Bonjour à tous,

Je souhaite en saisissant en 2011 à n’importe quelle date au cours de l’année le prix hors charges d’un loyer à sa date de saisie en 2011(colonne E), le n° de la location auquel il correspond (colonne A), la date de signature du bail initial (colonne B), la date de saisi dans Excel du prix hors charges de ce loyer (colonne C), et dans la colonne D la date à laquelle le loyer doit être révisé en 2011 (date anniversaire de signature du bail initial) obtenir :

- Dans la colonne G le numéro du trimestre à prendre en compte pour réévaluer le loyer : pour déterminer cela, il faut rechercher dans le tableau qui se trouve entre les cellules I2 et Z60 entre quelles dates se trouve la date anniversaire de la signature du bail et renvoyer le n° du trimestre correspondant qui se trouve en L2, ou P2, ou T2 ou X2. (C’est cette recherche conditionnée par des dates qui me pose vraiment problème…)

- Dans la colonne F le prix du loyer au premier janvier de l’année 2011. Une fois le trimestre et son indice déterminés, le loyer de l’année précédente pour le loyer au 01.01.2011 est égal à :
Loyer 2011 * (IRL du trimestre concernée de l’année précédente (2010) / IRL du trimestre de l’année en cours (2011)). (Si, et seulement si le loyer a déjà été révisé en 2011… Si il n’a pas encore été révisé, le loyer au 01.01.2011 est celui recueillit à sa date de saisie). (J’ai tenté une formule avec SI(ET ;… mais cela ne fonctionne pas).

Je joins un fichier comportant les informations concernant le prix hors charges de loyers, leur date de saisie en 2011, le n° de la location auquel il correspond, la date de signature du bail initial, et la date de saisi du prix hors charges des loyers.

Merci si possible de trouver une/des solution(s) à partir de fonctions (DATES, RECHERCHEV…) plutôt que de VBA que j’aurai plus de difficultés à me réapproprier.


Merci du temps que vous aurez la sympathie d'accorder à mon problème.


Amicalement.


Angélique

[rjamin]

Bonjour,

Un proposition à tester car ce n'est pas simple votre affaire.
Je ne suis donc pas certain d'avoir bien compris et surtout tenu compte de toutes les situations possible.
J'ai ajouté une ligne (surlignée en jaune) qui permet de reporté le n° de trimestre en haut de chaque colonne du tableau I2:Z60
Cette ligne peut-être masquée.
J'ai corrigé les formules que vous aviez établie et qui contenait quelques erreurs avec les ET(Test1;Test2) parenthèse de fin qui manquait à plusieurs endroits et de ce fait trop de parenthèses à la fin.

La formule qui doit ramener le trimestre affiche des #Valeur pour des dates qui n'ont pas encore de références valables. Dans la colonne H j'ai mis la même formule mais qui n'affiche plus ces erreurs. Vous pouvez la recopier en colonne G.
Faites les tests nécéssaires pour vérifier si c'est ce que vous attendez.
Tenez nous au courant.

[Angélique83]

Bonjour Rjamin,

Merci beaucoup de cette proposition et des corrections de mes formules mal... formulées !

Cependant les résultats obtenus ne sont pas ceux attendus ; probablement que quelque chose est encore à modifier !?!

J'ai essayé ce WE de comprendre le fonctionnement des fonctions INDEX et EQUIV que vous utilisez pour modifier cela seule, mais j'ai eut beau lire leur description et lire et relire les formules que vous aviez rédigé, je n'ai pas beaucoup progressé...

J'ai donc complété mon fichier initial par les réponses attendues pour les 23 premières lignes, pour les colonnes F et G afin de vous permettre plus aisément de valider les résultats obtenus par ces formules...

J'explique comment je suis parvenue à ces résultats :

Pour savoir quel numéro de trimestre insérer pour chaque référence dans la colonne G, j'ai recherché dans mon tableau située de I2 à Z60 dans quel intervalle de date se situe la date d'entrée dans le logement (colonne B) pour chaque référence. Ayant repéré la localisation de chaque référence dans les intervalles de date, j'ai à chaque fois renvoyé en G le numéro de l'indice concerné.

En insérant la formule =SI(C2<D2;E2;"a calc"), j'ai ensuite pu trier les références déjà révisée de celles qui ne l'ont pas encore été, et renvoyer comme valeur 2011 pour celles qui n'ont pas déjà été révisées en 2011, leur valeur en colonne E.

Ensuite, pour les 4 références (lignes 2 15 16 & 23) qui s'avéraient être à réviser car ayant des dates de révision situées entre le 1er et le 26 janvier 2011 (date du recueil des données) j'ai appliqué une formule du type =E2*($P$6/$P$7) pour la référence de la ligne 2 : ceci en adaptant le numéro de ligne pour les références des lignes 15, 16 & 23, ainsi que les lettres de colonne en fonction de l'indice à appliquer.

Mais je pense que la direction que vous avez prise est la bonne... Sauf que ce n'est pas simple de se réapproprier cela !

Je vous joins mon fichier modifié en espérant que cela vous aidera à... m'aider !

Merci beaucoup du temps passé sur mon problème !

Amicalement !

Angélique

[rjamin]

Bonjour,

Etes-vous bien sûr que le n° Loc 2 au 1/05/2002 doit ramener 2, si j'ai bien compris c'est 4, où alors je n'ai rien compris, il faut me réexpliquer.

Je progresse mais il me faut confirmation de la question ci-dessus?

[rjamin]

Bonjour,
La confirmation ne venant pas je mets une proposition dans le fichier joint.
C'est relativement compliqué, les formules sont très longue et difficile à lire.
Je pourrais expliquer plus mais je manque un peu de temps pour le moment.
Les deux feuilles supplémentaires devraient donner des renseignements sur la manière de faire.
Je sens que ce n'est pas suffisant mais...

[Angélique83]

Bonjour Rjamin,

Nul besoin de vous réexpliquer les modes de détermination du trimestre à affecter à chaque référence car vous avez très bien compris. J'avais effectivement fait une erreur pour la référence 2 ainsi que pour la référence 21 concernant leur trimestre de révision.

Merci milles fois et bravo d'être si fort en matière d'Excel et généreux en matière de recherche de solutions !

Effectivement les formules que vous employés sont compliquées, voire très compliquées à lire et comprendre.

Euh, vous écriviez que cela ne vous semblait pas être suffisant, pourtant cela fonctionne et je ne vois pas ce qui manquerait...

Euh, si... Vous indiquez en H1 que "Indice sera à adapter au fur et à mesure"... Euh, oui, mais c'est-à-dire ? Que faudra t'il que je fasse à chaque sortie de nouvelle indice pour adapter l'outil ?

Pour être plus précise concernant l'usage ne cet outil, je vais l'utiliser pour traiter environ 12000 références de loyer en 2011, et probablement davantage dans les années à venir. Il est probable que j'ajoute quelques colonnes dans lesquelles je calculerai aussi le loyer au 1.01.2010 ainsi qu'au 1.01.2009, mais je pense que je devrais m'en sortir...

La partie la plus complexe était de loin la détermination du trimestre de révision...

Merci beaucoup du temps et de l'énergie que vous avez eut la sympathie de passer sur mon problème !

Amicalement !

Angélique

[rjamin]

Re,
Je parlais de l'insuffisance des explications en feuille 2 et trois.
Pour les indices l'adaptation sera au niveau des rapport entre les P6/P7 par exemple qui deviendra P8/P7 quand une nouvelle valeur viendra en colonne P même chose pour les quatres colonnes.
Je regarderai si on ne saurait pas automatiser cela, mais ça ne doit pas être facile.

Edit : je cois que j'ai trouvé une solution au moins pour jusqu'en 2012
En H3 mettre la formule

Code :

=SI(G3=1;GRANDE.VALEUR($L$4:$L$10;1)/GRANDE.VALEUR($L$4:$L$10;2);SI(G3=2;GRANDE.VALEUR($P$4:$P$10;1)/GRANDE.VALEUR($P$4:$P$10;2);SI(G3=3;GRANDE.VALEUR($T$4:$T$10;1)/GRANDE.VALEUR($T$4:$T$10;2);SI(G3=4;GRANDE.VALEUR($X$4:$X$10;1)/GRANDE.VALEUR($X$4:$X$10;2);1))))

Un copier coller devrait convenir.
Puis la tirer vers le bas.
Quand vous devrez traiter 12000 lignes ou plus une machine puissante sera de mise.

[Angélique83]

Bonjour Rjamin,

Merci une nouvelle fois !

Merci de m'avoir indiqué cette procédure de mise à jour lors de la sortie de chaque nouvel indice. J'ai bien copié la formule en H3. Je ne sais pas pourquoi, mais quand je la tire vers le bas certains indices sont modifiés et pas d'autres !?! Mais cela ne concerne pas les 3 références révisées entre le 1er et le 26 janvier...

Si en plus vous trouvez une solution pour automatiser cela, c'est formidable...

Et pourquoi cette solution ne serait-elle valable que jusqu'en 2012 ?

Au niveau Pc, j'utilise pour mes traitements sous Excel 2 portables : un Acer travelmate 5623WSMi et un Hp 6830S. Je ne sais pas si cela vous parle, mais si c'est le cas, cela vous parait il suffisant ? J'ai pas mal utilisé les matrices l'an dernier pour une partie de mes traitements statistiques, et c'est vrai que Excel était vite en rupture, à ramer 5-10 min à chaque modification...

Quoi qu'il en soit, merci beaucoup, vous êtes mon sauveur !

Amicalement !

Angélique

[rjamin]

Bonsoir,
Seulement jusque 2012 parce que votre tableau n'allait que jusque là.
Mais si vous regardez dans les fonctions GRANDEVALEUR il y a une plage déterminé entre ligne 4 et 10 T4:T10 par exemple le jour ou vous allez ajouter des lignes ce sera en haut du tableau il faudra alors vérifier que ces plages sont bien mise à jour, sinon il suffira de le faire pour avoir les 5 ou 6 première lignes dans les plages.
C'est cette solution qui est celle que j'appelle automatique. Si dans le tableau actuel vous insérez des lignes au dessus de 2011 (5 lignes par exemple) vous verrez que les plages seront mises à jour, il faudra seulement redescendre 2012. Seulement il y le tableau de à gauche (les colonnes de A à H) qui vont également être modifiée il faudra donc remonter les formules dans les lignes vides.
Pour les machines il faut prendre les processeurs les plus puissant et une quantité de mémoire d'au moins 4Go.
Pour les indices, chez moi je n'ai rien remarqué comme modifications.
A moins que votre tableau n'aie évolué par rapport à celui que vous aviez transmis.
Bon WE.

[Angélique83]

Bonjour Rjamin,

Pardon de ne vous répondre qu'aujourd'hui, j'avais pris quelques jours de congés pour aller voir une amie qui vit avec son troupeau de chèvres dans l'arrière pays varois, loin de toute connection internet...

D'accord, donc je dispose de tous les éléments pour mettre à jour l'outil lors de chaque parution d'un nouvel indice de réévaluation des loyers (IRL)... C'est super !

Ok pour le PC adapté ! Le prochain devra donc avoir un processeur parmi les plus puissants et une quantité de mémoire d'au moins 4Go.

Concernant les indices, probablement que je me trompe : je repars de votre dernier fichier envoyé et effectivement il n'y a pas ici de modification de la valeur des indices...

Vous êtes trop fort.

Hormis être retraité en Allemagne et être un Hercule d'Excel, vous faites quoi dans la vie ?

Amicalement.

Angélique

[rjamin]

Bonjour,

Hormis être retraité en Allemagne et être un Hercule d'Excel, vous faites quoi dans la vie ?

je suis retraité en Belgique (mais vu la politique belge peut-être plus pour longtemps)
J'étais enseignant de cours techniques comme électricité, mécanique, dessin industriel etc.
Maintenant je fais beaucoup d'informatique.

Heureux d'avoir pu vous aider.
A+

[Angélique83]

Bonjour Rjamin,

Je reviens vers vous car j'ai besoin de modifier en partie l'outil de calcul rétrospectif des loyers que vous m'aviez aidé à créer entre juin et juillet dernier.

En effet, après vérification, le tableau qui me permettait de déterminer quel indice s'applique de telle à telle date comportait plusieurs erreurs.

J'ai voulu insérer les corrections dans le fichier que vous aviez conçu, mais je n'y parviens pas.

Je pense que c'est en particulier le tableau de la feuille 1 compris entre les colonnes AB à AH que je dois adapter, mais très franchement, je ne comprends pas vraiment à quoi correspondent les formules qu'il contient.

Merci beaucoup si cela vous est possible de m'aider à remplacer le tableau en question par celui que je vous joinds.

Je joinds également votre dernier fichier, au cas où...

Amicalement.

Angélique

[rjamin]

Bonjour,

Essayez un peu cette formule en H3 pour le calcul de l'indice

Code :

=SI(G3=1;GRANDE.VALEUR($L$5:$L$10;1)/GRANDE.VALEUR($L$5:$L$10;2);SI(G3=2;GRANDE.VALEUR($P$5:$P$10;1)/GRANDE.VALEUR($P$5:$P$10;2);SI(G3=3;GRANDE.VALEUR($T$5:$T$10;1)/GRANDE.VALEUR($T$5:$T$10;2);SI(G3=4;GRANDE.VALEUR($X$5:$X$10;1)/GRANDE.VALEUR($X$5:$X$10;2);1))))

car je crois que la formule précédente ne tenait pas compte de la dernière valeur introduite.
GRAND.VALEUR(Matrice;k) permet de trouver la kème plus grande valeur dans la matrice = plage de cellules.
Donc pour la +garnde valeur on met 1 pour k et pour la 2ème plus grande valeur on met 2 pour k

[Angélique83]

Bonjour Rjamin,

Merci beaucoup de votre réponse presque immédiate.

Alors non, mon problème principal est que le tableau (I3 à Z61) qui me sert à déterminer, en fonction de la date de signature du bail, quel est le trimestre de l'indice à appliquer, est à modifier, voire même à remplacer, les erreurs qu'il contient étant nombreuses.
Il est à remplacer par le tableau joins dans le fichier "Copie de Tableau indice & trimestre en fonction de la date de signature du bail 10.2011".

Du coup si je comprends correctement, le tableau compris de AB2 à AH30 que vous aviez ajouté doit lui aussi être modifié, ce que j'aimerai être capable de faire, mais les formules qu'il contient me dépassent totalement !

J'ai cependant inséré la modification que vous m'indiquiez en H3, et effectivement cela modifie une partie des résultats des indices de la colonne H. Après, la non plus, je ne comprends pas le sens de cette formule.

Pour répondre à votre question, pour déterminer manuellement cet indice, je pars de la date de signature du bail, je recherche dans quel intervalle de date se situe cette date de signature dans le tableau du fichier "Copie de Tableau indice & trimestre en fonction de la date de signature du bail 10.2011".

Disposant alors de mon trimestre, et de mon indice, il ne me reste plus qu'à diviser mon prix de loyer à la date de recueil, par l'indice du trimestre concerné (cela est fait dans la colonne F, uniquement pour les références dont le prix de loyer a déjà été mis à jour pour l'année.) J'obtiens alors le prix au premier janvier de l'année.

Merci beaucoup de votre aide !

Amicalement !

Angélique

[rjamin]

Re,
J'ai fait un copier coller de la feuille corrigée dans le fichier Calcul...
J'ai adapter la formule GRANDE.VALEUR() précédente, au lieu de prendre la matrice de la ligne 5 à 10 j'ai pris de 5 à 8
Pourquoi?
Quand on regarde au trimestre 4 les valeur de la colonne X ne sont pas toujours croissantes en tout cas pour les 3 dernières ce qui fait qu'on ne doit prendre que les 3 dernières valeurs en compte pour que cela fonctionne.
Regardez et vérifiez si c'est plus fiable qu'avant.

Si nécessaire demandez pour des explications.

[Angélique83]

Bonsoir,

Comme d'habitude, vous réglez les problèmes avec une facilité déconcertante !!!

Manifestement cela fonctionne et cela me semble effectivement plus fiable qu'avant.

Je me pencherai davantage dessus demain et je vous tiens informé si j'ai encore un problème !

Merci beaucoup de votre aide !

Amicalement.

Angélique