Trouver les prix proches 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 :


Trouver les prix proches

Extraire les données appartenant à une fourchette de valeurs et plus précisément de prix dans notre cas, est la solution que nous proposons d'apporter dans ce cas pratique.



Sur l'exemple illustré par la capture ci-dessus, nous travaillons sur une base de données de biens immobiliers. A l'instant T, le client possède un budget bien défini ou limité. En fonction de cette valeur seuille, nous réalisons l'extraction des biens dont les prix sont les plus proches, aussi bien en-dessous qu'au-dessus.

Source et présentation de la problématique
Pour réaliser cette étude, nous devons tout d'abord réceptionner cette base de données des biens immobiliers.

• Télécharger le classeur valeurs-proches-max-min.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Les biens immobiliers sont référencés dans un tableau s'étendant de la colonne B à la colonne I. Parmi les informations, on trouve pour chacun le détail sur la superficie et le prix notamment.



Sur la droite de cette source, figurent des petits tableaux de synthèse. L'agent immobilier saisit en cellule L6, le budget plafond mentionné par le client. Instantanément, nous devons produire l'extraction des trois biens aux prix directement inférieurs mais aussi des trois biens aux prix directement supérieurs. Sur un coup de coeur, on peut estimer que le client est capable d'ajouter quelques milliers d'Euros. La technique que nous allons démontrer ne se limite pas à trois biens. Il s'agit d'un exemple. Elle est extensible à souhait.

• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Comme vous le constatez, chaque colonne de la base de données est nommée en fonction de son propre titre. Nous exploiterons ces noms pour simplifier la construction des formules.

Extraire les prix directement inférieurs
Nous connaissons la fonction Excel Grande.Valeur. Elle permet d'extraire l'une des plus grandes données numériques d'une plage selon un rang mentionné en deuxième argument de la fonction. Il peut s'agir de la première comme de la deuxième ou de la troisième etc...

Mais dans notre cas, il s'agit des trois premières à la fois. Seul un calcul matriciel, capable de raisonner sur l'ensemble des lignes des matrices impliquées, peut produire ce résultat. De plus, il ne s'agit pas des trois plus grandes valeurs dans l'absolu. Elles ne doivent pas dépasser le seuil défini. Donc la fonction conditionnelle Si est nécessaire pour amorcer la condition.

• Sélectionner les trois premières cellules vides du tableau de synthèse, soit la plage K9:K11,
• Taper le symbole égal (=) pour initier la construction du calcul matriciel,
• Saisir la fonction des grandes valeurs suivie d'une parenthèse, soit : Grande.Valeur(,

Dans la version classique de cette fonction, le premier argument requis est la plage de cellules à analyser. Mais celle-ci est soumise à la contrainte du budget imposé par le client.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Ouvrir une nouvelle parenthèse pour accueillir la matrice conditionnelle,
• Désigner la colonne des prix par son nom, soit : Prix,
• Taper le symbole moins (-) pour annoncer l'opération à suivre,
• Cliquer sur la case du budget client, soit la cellule L6,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $L$6,

En effet, pour les deux prix les plus proches du dessous, la même contrainte doit être observée. Donc la cellule ne doit pas suivre le mouvement de la réplication de la formule.

• Fermer la parenthèse de la matrice,
• Puis, taper l'inégalité suivante : <0,

Le critère matriciel est ainsi explicite. Nous souhaitons extraire les plus grandes valeurs sur la plage, uniquement pour les prix inférieurs au budget.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner de nouveau la plage des prix par son nom, soit : Prix,

Il s'agit d'un raisonnement matriciel. Seuls les prix correspondant au critère précédent seront conservés sur cette plage.

• Fermer la parenthèse de la fonction Si,

La méthode n'est pas habituelle. Nous ne renseignons pas la branche Sinon de la fonction Si. Ce cas ne nous intéresse pas. Les erreurs doivent plutôt être gérées par la fonction dédiée SiErreur.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,
• Saisir la syntaxe suivante : {1;2;3},

Les accolades permettent d'annoncer l'énumération matricielle des trois plus grandes valeurs que nous souhaitons conserver sur cette matrice, elle-même soumise à condition.

• Fermer la parenthèse de la fonction Grande.Valeur,
• Enfin, valider nécessairement le calcul par le raccourci clavier CTRL + MAJ + Entrée,

Les résultats tombent et semblent parfaitement cohérents. Nous réalisons l'extraction des prix à la fois les plus proches du budget et inférieurs à ce dernier. Si vous changez le budget, vous remarquez que les extractions s'ajustent automatiquement. Mais lorsque ce dernier est défini trop bas, en l'absence de concordance, la fonction Grande.Valeur retourne des erreurs. C'est pourquoi et comme nous le disions, il est nécessaire d'encadrer la syntaxe dans la fonction SiErreur, comme suit :

{=SIERREUR(GRANDE.VALEUR(SI((Prix-$L$6)<0; Prix); {1;2;3}); '')}

Pour l'extraction des prix directement supérieurs, la technique est similaire. Il convient de :

• Sélectionner la plage de cellule K14:K16,
• Coller la précédente syntaxe,
• Remplacer la fonction Grande.Valeur par la fonction Petite.Valeur,
• Modifier l'inégalité inférieure par l'inégalité supérieure selon la syntaxe suivante :

{=SIERREUR(PETITE.VALEUR(SI((Prix-$L$6)>0; Prix); {1;2;3}); '')}

Bien sûr, il ne faut pas oublier de valider la formule par le raccourci clavier CTRL + MAJ + Entrée.

Extraire les biens associés aux prix
Pour réaliser l'extraction du détail des biens immobiliers correspondant aux prix rapatriés, il suffit d'exploiter les fonctions Index et Equiv dans leur version classique. La fonction Index extrait l'information située au croisement d'une ligne et d'une colonne dans une source de données :

=Index(Tableau_de_recherche; Indice_de_ligne; Indice_de_colonne)

Le tableau de recherche est la rangée mentionnée par chaque titre des tableaux de synthèse. Nous pourrons donc utiliser ces cellules dans la formule. Mais pour qu'elles soient interprétées comme des plages, nous devrons les encapsuler dans la fonction Indirect. Donc, l'indice de colonne coule de source. Il s'agit de la première. L'indice de ligne est variable quant à lui. Il dépend de la position du prix cherché dans la base. C'est la fonction Equiv qui permet de trouver cette position :

=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

La valeur cherchée est le prix. La colonne de recherche est celle qui les contient dans la base de données, reconnue par son nom : Prix. Là aussi et pour les raisons évoquées précédemment, il est préférable d'inclure la syntaxe de la fonction de gestion des anomalies.

• Sélectionner la première ville à extraire en cliquant sur sa cellule L9,
• Taper le symbole égal (=) pour initier la formule d'extraction,
• Saisir la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Cliquer sur le titre juste au-dessus pour désigner la cellule L8,

De cette manière et grâce à la fonction Indirect, nous désignons le champ de recherche dans la base de données.

• Enfoncer deux fois la touche F4 du clavier, ce qui donne : L$8,

Ainsi, nous la figeons en ligne et la libérons en colonne. De la sorte, le calcul est réplicable pour toutes les autres extractions. Les lignes du dessous doivent se nourrir de cette information, qui doit donc rester figée dans sa ligne. Les cellules sur la droite doivent considérer l'information du titre respectif. Celui-ci doit suivre le mouvement. Donc, nous libérons la colonne.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction trouvant la position variable, suivie d'une parenthèse, soit : Equiv(,
• Désigner le premier prix extrait en cliquant sur sa cellule K9,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $K9,

Ainsi, nous la libérons en ligne et la conservons figée en colonne. Pour les lignes du dessous, ce sont en effet les autres prix extraits qui doivent être considérés. Pour les extractions sur la droite en revanche, la recherche doit toujours être réalisée à partir de ce prix figé dans sa colonne.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne des prix par son nom, soit : Prix,
• Taper ensuite un point-virgule suivi du chiffre zéro : ;0, pour une recherche exacte du prix,
• Fermer la parenthèse de la fonction Equiv,

Nous sommes de retour dans les arguments de la fonction Index. Nous venons de lui indiquer l'indice dynamique de la ligne qui est fonction du prix cherché. Nous devrions normalement lui indiquer l'indice de colonne. Mais comme la zone de recherche n'est constituée que d'une colonne, nous pouvons ignorer cet argument. La fonction Index en déduira implicitement que la recherche doit être réalisée dans cette unique colonne mentionnée en premier paramètre.

• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans la gestion d'erreur de la fonction SiErreur,
• Inscrire deux guillemets ('') pour garder la cellule vide en cas d'échec,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider la formule par le raccourci CTRL + Entrée pour garder le résultat sélectionné,

La ville du bien dont le prix est directement inférieur au seuil mentionné apparaît automatiquement. Si vous consultez la base de données, vous pouvez confirmer sa cohérence. A ce titre, une mise en forme conditionnelle serait judicieuse. Elle serait destinée à repérer dans la base de données, tous les biens dont les prix concordent avec les extractions.

• Tirer la poignée de la cellule sur les deux lignes du dessous,
• Tirer la poignée de la sélection sur les deux colonnes à droite,
• Copier (CTRL + C) la sélection et la coller (CTRL + V) en L14 pour le second tableau de synthèse,

Le détail des six biens immobiliers dont les prix se rapprochent le plus du budget client sont parfaitement extraits. Si vous modifiez ce budget en cellule L6, toutes les extractions s'ajustent automatiquement. Voilà donc un outil fort précieux pour l'agent immobilier. Avec une simple indication, il peut instantanément faire des offres à ses clients.

Une anomalie persiste néanmoins et elle est tout à fait naturelle. Lorsque des prix sont identiques, les extractions s'arrêtent sur la première valeur trouvée. Il en résulte un doublon. Mais dans les formations précédentes, nous avons appris à les différencier explicitement. Pour cela, nous avons bâti des calculs intermédiaires capables de les concaténer à des indices de répétition incrémentés. Ces indices permettent alors de pointer précisément sur le bien à extraire, malgré son prix redondant. Libre à vous donc de peaufiner cette application grâce à ces techniques.