Gestion des annonces immobilières avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Gérer les annonces immobilières avec Excel

Dans cette formation Excel, nous allons créer une application de gestion des offres immobilières. Nous souhaitons contourner le code VBA. Il va donc s'agir de mettre en oeuvre des calculs d'extraction. L'application finalisée doit correspondre au formulaire Access de gestion immobilière, que nous avions bâti lors d'une formation.

Sources et présentation de la problématique
La source de données et la structure du classeur sont offertes au téléchargement.

• Télécharger le fichier gerer-biens-immobiliers.rar en cliquant sur son lien,
• Puis, le décompresser dans le dossier de votre choix,

La décompression conduit à la base de données annonces-immobiliere et au classeur gerer-biens-immobiliers.

• Double cliquer sur le fichier de la base de données pour l'ouvrir dans Access,
• Cliquer sur le bouton Activer le contenu du bandeau de sécurité,
• Dans le volet des objets Access, double cliquer sur la table Biens,

Nous l'affichons ainsi en mode feuille de données. Nous y consultons les offres immobilières en cours, référencées sur un certain nombre de champs. Pour que les extractions Excel, répondant aux critères recoupés des clients, s'effectuent sur des données actualisées, nous devons créer une liaison entre le classeur Excel et la base de données Access. Nous avions appris ces techniques. Elles permettent à Excel de questionner la source de données automatiquement, à échéances périodiques. De fait, les offres obsolètes disparaissent d'elles-mêmes tandis que les nouvelles sont automatiquement intégrées.

• Fermer la base de données Access,
• A la racine du dossier de décompression, double cliquer sur le classeur Excel pour l'ouvrir,

Ce classeur est constitué de deux feuilles. Elles sont respectivement nommées Annonces et Extraire. La première doit être utilisée pour établir la liaison dynamique avec la base de données Access. Elle doit donc offrir les enregistrements des annonces immobilières à manipuler. La seconde est la feuille qui permet d'établir les exigences du client à croiser.



La ligne 4 offre à l'agent immobilier des leviers sous forme de zones de saisie. Pour répondre aux recherches du client à l'instant T, il peut recouper plusieurs critères. Il s'agit de la superficie, du nombre de pièces, du prix mais aussi de la distance par rapport à la localité actuelle. Une case à cocher permet d'affiner la recherche sur les biens offrant un garage. Il s'agit d'un contrôle ActiveX que nous avons paramétré avec le ruban développeur. Une cellule réagit en fonction de son état. Lorsque nous la cochons, H4 vaut 1 et 0 sinon. C'est ce lien que nous exploiterons dans les calculs. Vous notez les opérateurs au-dessus des critères. Nous devrons les considérer dans les formules. Pour une superficie de 80 m², tous les biens proposant une surface au moins égale seront inclus. Par élimination, tous les autres seront exclus.



Comme l'illustre la capture ci-dessus, des règles de validités ont également été posées sur ces cellules de critères. Par exemple, la superficie est nécessairement un nombre entier positif. Nous sécurisons l'application et renforçons l'ergonomie. Le symbole m² comme celui de l'Euro ou du kilomètre ne doit pas être saisi. Il s'agit de formats personnalisés prédéfinis.

Dans cette feuille, l'extraction des annonces immobilières doit prendre sa source à partir de la ligne 7. Mais pour extraire des informations, faut-il encore pouvoir questionner des données.

Liaison des données entre Excel et Access
Nous allons reproduire les techniques que nous avions apprises au cours d'une formation Excel.

• Cliquer sur l'onglet Annonces en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule B3 pour indiquer le point de départ de l'importation,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Obtenir des données,
• Dans la liste, pointer sur la rubrique : A partir d'une base de données,
• Dans le sous-menu qui s'affiche, cliquer sur : A partir d'une base de données Microsoft Access,
• Dans la boîte de dialogue qui suit, accéder au dossier de décompression,
• Puis, double cliquer sur la base annonces-immobiliere.accdb pour établir la connexion,

La boîte de dialogue qui apparaît liste les tables et requêtes repérées dans la base de données source. Il n'y en a qu'une, nous l'avions constaté.

• Cliquer sur le lien de la table Biens,

Un aperçu de tous les enregistrements issus de cette table est proposé. Il s'agit bien des annonces immobilières que nous avions consultées.



Le champ Bien_id n'est pas proposé sur la feuille Excel. Il s'agit de la clé primaire. Elle est essentielle dans la construction de la table mais n'apporte aucun intérêt à l'affichage. Nous devons donc accéder à Power Query pour l'éliminer de l'équation et donc de l'importation.

• En bas de la boîte de dialogue, cliquer sur le bouton Modifier,

L'éditeur Power Query apparaît. Il offre la représentation des enregistrements avec la matérialisation des champs.

• Cliquer droit sur l'étiquette du champ Bien_id,
• Dans le menu contextuel, choisir Supprimer,

Le champ disparaît aussitôt de la représentation. Il n'a pas été supprimé de la base de données source. Il ne fait simplement plus partie de la sélection pour la restitution dynamique des informations.

Cet éditeur de requête Power Query est très puissant. Nous l'avons déjà abordé et l'étudierons plus en détail à l'avenir. Il offre l'occasion d'agir sur les données a posteriori. Bien sûr ces requêtes peuvent être construites en amont, directement depuis Access. Mais lorsque de nombreux utilisateurs exploitent les mêmes sources, c'est cet outil qui permet à chacun d'isoler les informations spécifiques au métier.



Nous devons les importer sur la feuille à partir de la cellule B3. Les colonnes sont fort heureusement organisées dans le même ordre que les champs de la base de données.

• En haut de l'éditeur Power Query, cliquer sur le bouton Fermer et charger,
• Dans la liste, choisir Fermer et Charger dans,
• Dans la boîte de dialogue qui suit, cocher la case Feuille de calcul existante.

Comme nous avions pris soin de sélectionner le point de départ pour l'importation, la cellule est mémorisée et proposée.

• Valider l'importation en cliquant sur le bouton Ok,

Comme vous le constatez, les données importées sont encapsulées dans une mise en forme automatique imposée par Excel. Un nom de plage de cellules lui a été attribuée.

• Cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis, cliquer sur le bouton Gestionnaire de noms,

Comme l'illustre la capture ci-dessus, le tableau des données importées à emprunter le nom de la table Access. Ce nom est précieux. Il permet de manipuler dynamiquement les informations, en tenant compte des évolutions. Nous devons nous en inspirer.

• Fermer cette boîte de dialogue pour revenir sur le tableau des importations dynamiques,

Les flèches de filtres automatiques, en haut du tableau, ne nous sont pas utiles. Nous proposons de les masquer.

• Cliquer dans l'une des cellules du tableau pour le désigner,
• Puis, cliquer sur l'onglet Création en haut de la fenêtre Excel pour activer son ruban contextuel,
• Décocher la case Bouton de filtre,

Les titres des colonnes ne sont désormais plus tronqués.

Il nous reste à paramétrer la connexion pour que la liaison soit actualisée à intervalles réguliers. Nous bénéficierons ainsi des données mises à jour régulièrement.

Le volet Requêtes et connexions doit être visible. Si ce n'est pas le cas, dans le ruban Données, vous devez cliquer sur le bouton Requêtes et connexions.

• Dans ce volet, cliquer droit sur la ligne de connexion Biens,
• Dans le menu contextuel, choisir Propriétés,
• Dans la boîte de dialogue qui suit, cocher la case Actualiser toutes les,

Nous pouvons de même ajuster le délai pour choisir des mises à jour plus régulières, comme par exemple toutes les 5 mn.

• Puis, cocher la case Actualiser les données lors de l'ouverture du fichier,
• Enfin, valider la boîte de dialogue pour revenir sur la feuille Excel,

Avec ces paramétrages, nous nous assurons de travailler sur des données, toujours d'actualité.

Nous proposons de tester cette liaison dynamique.

• Rouvrir la base de données Access,
• Puis, double cliquer sur la table Biens pour l'afficher en mode feuille de données,
• A la toute fin de la liste, ajouter l'enregistrement suivant :

Ville : Nyons, Superficie : 95, Nombre de pièces : 3, Prix : 165 000 et distance : 60.

• Fermer la table et fermer la base de données,
• Puis, revenir sur le classeur Excel,

Précédemment, nous avons défini une échéance périodique d'actualisation raccourcie à 5 mn. Nous pourrions patienter et constater l'intégration automatique de ce nouvel enregistrement à l'issue. Mais nous pouvons aussi forcer le mécanisme.

• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Requêtes et connexions, cliquer sur le bouton Actualiser tout,

Comme vous le remarquez, la liaison dynamique à la base de données Access s'établit. La nouvelle donnée est finalement parfaitement intégrée dans la feuille Excel, à la suite des précédentes importations.



Si vous consultiez la plage nommée grâce au gestionnaire de noms, vous remarqueriez qu'elle a évolué. Elle a déplacé sa borne inférieure jusqu'à la ligne 38 pour intégrer cette nouvelle référence.

Repérer les enregistrements concordants
Afin produire l'extraction des offres immobilières correspondant aux critères recoupés, nous devons commencer par les repérer grâce à un numéro incrémenté. C'est une technique que nous avions découverte au travers de la formation Excel pour éliminer les doublons. Et pour cela, nous choisissons d'exploiter la colonne J de la feuille Annonces. Elle servira de calculs intermédiaires pour le repérage. Mais avant cela, nous devons réaliser une manipulation importante. Les données importées sont encapsulées dans une plage nommée. Et comme nous l'avons remarqué, cette dernière est dynamique. Mais ce nom ne peut pas être exploité dans les calculs. Nous devons donc lui attribuer manuellement un nouveau nom. Ce dernier sera calqué sur celui imposé par l'importation d'Excel. En somme, ses bornes évolueront de la même façon.

Nous ne devons pas inclure les entêtes dans la sélection.

• Sélectionner la cellule B4 de la feuille Annonces,
• A l'aide de la touche MAJ (Shift) enfoncée, cliquer sur la dernière cellule du tableau, soit I38,
• En haut de la fenêtre Excel et à gauche de la barre de formule, dans la zone Noms, taper l'intitulé Offres,
• Puis, valider avec la touche Entrée du clavier,

L'indication semble disparaître au profit du nom attribué par défaut. Mais elle a bien été prise en compte.

• Cliquer sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Noms définis, cliquer sur le bouton Gestionnaire de noms,

Comme vous le constatez, le nom que nous avons attribué est bien présent. Et comme l'indique la colonne Fait référence à, il est calqué sur la plage nommée Biens. Donc, lorsque cette dernière évoluera, par le jeu des actualisations des connexions, notre plage évoluera à l'identique.

• Fermer la boîte de dialogue du gestionnaire de noms,
• Puis, enregistrer les modifications (CTRL + S),

Pour repérer les enregistrements concordants, nous proposons de recouper les quatre critères numériques. La superficie doit être supérieure ou égale à la valeur indiquée dans la zone de critères. Dans le même temps, le nombre de pièces doit être supérieur ou égal à l'information mentionnée. De plus, le prix doit être inférieur ou égal au montant désigné. Et enfin, la distance doit être inférieure ou égale à la valeur inscrite.

Pour vérifier des critères, nous devons exploiter la fonction Excel Si. Mais comme il s'agit de satisfaire quatre conditions à la fois, nous devons imbriquer une fonction Excel Et dans la zone de critère de la fonction Si.

=Si(Critère_à_vérifier ; Action_Alors ; Action_Sinon)
Et(Critère1 ; Critère2 ; ... ; CritèreN)

La fonction Et permet d'énumérer autant de critères que souhaité, les uns à la suite des autres.

Dès que les quatre conditions sont remplies, nous devons inscrire un numéro incrémenté. Pour cela, nous allons exploiter la fonction Excel Max sur une plage de cellules qui progresse en même temps que le calcul, dans la colonne de la formule.

• Sur la feuille Annonces, sélectionner la cellule J4,
• Taper le symbole = pour démarrer la formule,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit Si(,
• Taper le nom de la fonction recoupant les critères, suivi d'une parenthèse, soit Et(,
• Saisir la référence de la première superficie, soit C4,
• Taper le symbole supérieur suivi du symbole égal, soit >=, pour l'inégalité à vérifier,
• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule de référence pour la superficie, soit C4,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul soit : Extraire!$C$4,

En effet, tous les enregistrements devront être comparés à cette valeur, dont les références ne doivent pas se déplacer en même temps que le calcul est répliqué.

• Taper un point-virgule (;) pour passer au critère suivant,
• Saisir la référence de cellule pour le nombre de pièces, soit D4,
• Taper le symbole supérieur suivi du symbole égal, soit >=, pour l'inégalité à vérifier,
• Sélectionner la cellule du critère sur le nombre de pièces, soit D4,
• Puis, la figer avec la touche F4 du clavier, ce qui donne : Extraire!$D$4,
• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Saisir la référence de la cellule pour le prix, soit E4,
• Taper le symbole inférieur suivi du symbole égal, soit <=, pour l'inégalité à vérifier,
• Sélectionner la cellule correspondante du critère et la figer, ce qui donne : Extraire!$E$4,
• Taper un point-virgule (;) pour passer dans l'argument de la dernière condition,
• Taper les références de la cellule pour la distance, soit F4,
• Saisir le symbole inférieur suivi du symbole égal, soit <=, pour l'inégalité à satisfaire,
• Cliquer sur la cellule correspondante du critère et la figer, ce qui donne : Extraire!$F$4,
• Fermer la parenthèse de la fonction Et,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction donnant la plus grande valeur suivie d'une parenthèse, soit Max(,
• Cliquer sur l'onglet Annonces en bas de la fenêtre Excel pour revenir sur sa feuille,
• Sélectionner la cellule située juste au-dessus du calcul, soit J3,
• Taper le symbole deux points (:) pour générer la plage J3:J3,
• Ne figer que la borne supérieure, ce qui donne : $J$3:J3,

Ainsi le calcul évoluera vers le bas en même temps que la formule est répliquée. Donc si nous incrémentons le plus grand résultat trouvé, il se verra affecté d'une unité supplémentaire à chaque fois qu'un enregistrement correspond.

• Fermer la parenthèse de la fonction Max,
• Ajouter une unité à ce résultat, soit : +1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide lorsque la ligne ne correspond pas,
• Fermer la parenthèse de la fonction Si,
• Valider le calcul par le raccourci CTRL + Entrée,

La formule doit se répliquer automatiquement sur la hauteur du tableau d'importation grâce aux plages nommées gérées par Excel.

• Revenir sur la feuille Extraire,
• Dans le critère Superficie, saisir 60,
• Dans le critère Distance, saisir 40,
• Revenir sur la feuille Annonces.

Comme vous le remarquez, chaque annonce immobilière recoupant les quatre conditions est repérée par un numéro incrémenté, donc unique. Et c'est cette différence que nous exploiterons pour l'extraction finale.

La formule que nous avons construite est la suivante :

=SI(ET(C4>=Extraire!$C$4; D4>=Extraire!$D$4; E4<=Extraire!$E$4; F4<=Extraire!$F$4); MAX($J$3:J3)+ 1; '')



Pour prendre en compte la condition émise par la case à cocher, il s'agit de vérifier l'égalité avec la cellule H5 de la feuille Extraire. C'est en effet cette cellule qui lui est directement liée. Elle reçoit la mention Vrai ou Faux selon son état. Mais comme la couleur du texte est réglée sur la couleur du fond, elle n'apparaît pas.

La formule deviendrait donc la suivante :

=SI(ET(C4>=Extraire!$C$4; D4>=Extraire!$D$4; E4<=Extraire!$E$4; F4<=Extraire!$F$4; H4=Extraire!$H$5); MAX($J$3:J3) + 1;'')

Extraction multicritère des annonces immobilières
Sur la base de ces numéros incrémentés, nous devons produire l'extraction champ à champ, pour tous les enregistrements concordants, dans la feuille Extraire. Les techniques sont désormais bien connues. Nous les avions notamment mises en oeuvre dans la formation Excel pour extraire les données recoupées.

Il s'agit de chercher la présence de ces nombres qui s'incrémentent. Pour cela, nous allons faire une recherche sur le retour de la fonction Excel Ligne, en lui passant une cellule de la première rangée :

=Ligne(A1)

Elle renverra ainsi 1 puis 2 et 3 etc..., au fur et à mesure que la formule est répliquée sur les lignes du dessous.

C'est la fonction Excel Equiv qui permet de retourner l'indice de ligne d'une valeur trouvée dans un tableau de recherche.

=Equiv(Valeur_cherchée; Champ_de_recherche ; Faux)

Pour l'extraction, il ne restera donc plus qu'à fournir l'indice de colonne pour repérer la donnée au croisement. Ce dernier est fixe. Il dépend de l'information de champ à extraire (Ville, Superficie...). Et c'est la fonction Excel Index qui permet d'importer la donnée située au croisement d'une ligne et d'une colonne.

=Index(Tableau_de_recherche; indice_de_ligne ; indice_de_colonne)

Le tableau de recherche est reconnu sous le nom dynamique Offres que nous lui avons attribué. Au final, nous devons imbriquer ces fonctions :

=index(tableau_de_recherche; equiv(ligne(A1); champ_de_recherche; Faux); indice_de_colonne)

Une fonction d'extraction retourne un message d'erreur lorsqu'elle ne trouve aucune correspondance. Pour éviter ces inscriptions disgracieuses, nous intègrerons le tout dans une fonction SiErreur pour les intercepter.

• Cliquer sur l'onglet Extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule de départ pour l'extraction, soit B7,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit index(,
• Saisir le nom dynamique du tableau de recherche, soit Offres,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction retournant le numéro de ligne suivie d'une parenthèse, soit Equiv(,
• Taper la fonction donnant la ligne d'une cellule suivie d'une parenthèse, soit Ligne(,
• Sélectionner une cellule de la première ligne, par exemple A1,
• Fermer la parenthèse de la fonction ligne,
• Taper un point-virgule (;) pour passer dans l'argument du champ de recherche,
• Cliquer sur l'onglet Annonces en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur l'étiquette de colonne J pour désigner la colonne de recherche, ce qui donne : Annonces!J:J,
• Enfoncer la touche F4 du clavier pour figer les bornes de cette plage : Annonces!$J:$J,
• Taper un point-virgule (;) suivi du texte Faux, soit ;Faux, pour une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,
• Retrancher 3 unités à ce résultat (-3) pour déduire les trois lignes vides au-dessus du tableau,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la fonction Index,
• Saisir le chiffre 1 pour désigner la première colonne, celle de la ville,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans la gestion d'exception de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,

Comme vous le constatez, le premier résultat tombe.

• Tirer la poignée du calcul sur plusieurs dizaines de lignes vers le bas,

Toutes les villes des enregistrements concordants sont extraites au fur et à mesure grâce au système de repérage unique par les numéros incrémentés. La formule que nous avons bâtie est la suivante :

=SIERREUR(INDEX(Offres; EQUIV(LIGNE(A1); Annonces!$J:$J; FAUX)-3; 1); '')



Il ne reste plus qu'à reproduire cette formule sur les autres colonnes pour extraire les informations associées. Pour la superficie par exemple, l'indice de colonne pour l'extraction n'est plus le chiffre 1 mais le 2, pour désigner la deuxième colonne :

=SIERREUR(INDEX(Offres; EQUIV(LIGNE(A1); Annonces!$J:$J;FAUX)-3; 2); '')

Pour le nombre de pièces, il faut indiquer 3 puis 4 pour le prix etc...

Nous aurions même pu bâtir une formule unique à répliquer par la poignée en ligne et en colonne. Pour cela, l'indice de colonne doit devenir une variable qui progresse avec le calcul :

=SIERREUR(INDEX(Offres; EQUIV(LIGNE(A1); Annonces!$J:$J; FAUX)-3; COLONNE(A1)); '')

Si nous modifions les contraintes de recherche dans la zone de critères, nous constatons que l'extraction se met à jour instantanément. Tous les enregistrements, donc toutes les offres concordantes, sont parfaitement importées.



L'agent immobilier n'a plus qu'à proposer au client les biens qui répondent à sa demande. Et souvenez-vous, grâce à la liaison dynamique, nous travaillons sur des données actualisées. Donc les nouveaux biens immobiliers sont automatiquement intégrés dans les extractions, tandis que les offres obsolètes disparaissent.

Voilà un outil qui peut s'avérer fort précieux en agence immobilière. Il permet de répondre instantanément aux critères d'un nouveau client pour ne manquer aucune vente.