Excel avancé 2010 étude de cas

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

3

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 1

Cas 1 – Analyse de sensibilité 1.1 Tableau de données à 1 variable Les cas de tableaux de données à 1 variable portent sur l’analyse du paiement hypothécaire fourni Cas 1.1 analyse de sensibilité 1 variable - données de base.

Dans la cellule H4, tapez le titre « Analyse de sensibilité au taux d'intérêt ». Dans la cellule I5, tapez la formule du paiement hypothécaire suivante « = VPM(TAUX.NOMINAL(TAUX.EFFECTIF(H5;2);12)/12;D6;-D2)» 1. Cette formule retournera une erreur. Poursuivez pour le moment.

=H7-$K$4 =H8-$K$4 =D3 =H8+$K$4 =H9+$K$4

Dans la cellule K4, tapez le nombre qui représente la variation positive et négative du taux d’intérêt que vous compléterez à l’étape suivante, exemple 0.01%.

Dans les cellules H6 à H10, saisissez les formules ci-contre Ces valeurs sont celles que prendront la cellule I5 lors du

calcul de la table de données.

Sélectionnez les cellules H5 à I10. Cliquez sur l’onglet menu DONNÉES et cliquez sur ANALYSE DE SCÉNARIO afin d’obtenir le menu déroulant. Cliquez sur TABLES DE DONNÉES dans le menu déroulant. Dans le champ « Cellule d’entrée en colonne », tapez H5 et OK. Formatez les résultats au besoin. (format de nombre pour les cellules I6 à I10, la cellule I5 peut-être formatée avec des caractères blancs.)

7,48% 7,49% 7,50% 7,51% 7,52%

La fonction de TABLE DE DONNÉES remplace la valeur de H5 par les nombres de la colonne H6 à H10 et par conséquent calcule une nouvelle valeur pour le paiement qui est alors écrit dans les cellules I6 à I10 correspondante.

Remplacez la valeur de K4 par 0,5 % et observez les résultats. Remplacez le taux d’intérêt initial de 7,5 % par 6 % et observez les résultats.

1.2 Tableau de données à 2 variables Le cas d’analyse de sensibilité porte la valeur actuelle d’une projection d’encaisse nette et la revente. Pour déterminer la valeur actuelle, nous devons actualiser le paiement périodique avec le TRI et actualiser la valeur de réversion au même taux. Pour sa part, la valeur de réversion est basée sur l’encaisse de l’année suivant la dernière année de la projection et actualisée avec le TGA. Les données de base sont fournies dans le fichier cas 1.2 - analyse de sensibilité 2 variables - données de base.

Dans l’exemple, les encaisses nettes sont fournies dans les cellules A2 à M2.

1 Pour saisir une fonction complexe, cliquez sur l’outil d’insertion de fonction (ƒx) et recherchez la première fonction à insérer (vpm dans les fonctions finances). Pour insérer la seconde fonction, cliquez sur la flèche vers le bas située à gauche de la barre de formule (où se trouve normalement le nom de la cellule) et recherchez la seconde fonction (taux.nominal dans l’exercice 1.1). Pour insérer la troisième fonction (taux.effectif dans le cas 1.1), suivez les mêmes instructions que pour la seconde fonction. À cette étape, complétez les arguments de la 3 ième fonction. Pour compléter les arguments de la seconde fonction, cliquez sur le nom de la fonction dans la barre de formule pour faire apparaître la boîte de dialogue.

Page 4

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 1

Étape - 1 Dans les cellules A6 et A7, tapez les titres TGA et TRI, les cellules B6 et B7 contiendront ces paramètres.

Inscrivez un TGA de 7 % et un TRI de 9 %.

Dans la cellule C5, tapez la formule suivante : = VAN(B7;C2:L2)+VA(B7;10;0;-M2/B6). Cette formule calcule la valeur actuelle du paiement [=VAN(B7;C2:L2) ] et la valeur actuelle de la revente (calculée par l’actualisation directe du flux d’encaisse de l’année 11) [VA(B7;10;0;-M2/B6)]. Étape 2 – Table à deux variables Dans les cellules D5 à J5, tapez les TGA de l’analyse de sensibilité. Pour l’exemple, dans la cellule D5 tapez 6%, dans la cellule E5 tapez =D5+0.0025 et recopiez jusqu’à la cellule J5. Formatez cette plage en pourcentage avec 2 décimales.

Dans les cellules C6 à C13, tapez les TRI de l’analyse de sensibilité. Pour l’exemple, dans la cellule C6 tapez 8%, dans la cellule C7 tapez =C6+0.0025 et recopiez jusqu’à la cellule C13. . Formatez cette plage en pourcentage avec 2 décimales.

Sélectionnez les cellules C5 à J13, sélectionnez l’onglet DONNÉES et cliquez sur ANALYSE DE SCÉNARIO afin d’obtenir le menu déroulant. Cliquez sur TABLES DE DONNÉES dans le menu déroulant. Dans le champ de ligne, tapez B6 (le TGA) et dans le champ colonne tapez B7 (le TRI). Cliquez sur OK. Le tableau d’analyse est produit immédiatement en utilisant les paramètres de TGA et de TRI du tableau d’analyse de sensibilité et la formule de calcul. Vous pouvez aussi formater le tableau pour des résultats plus professionnels. Étape 3 - Modification Remplacez les nombres saisis dans les cellules D5 et C6 et observez le résultat.

Modifiez la formule de la cellule C5 en ajoutant à la formule existante une fonction d’arrondie pour supprimer les unités. Ajoutez les éléments soulignés de la formule suivante : =ARRONDI(VAN(B7;C2:L2)+VA(B7;10;0;M2/B6);-1) (les nombres négatifs dans le dernier paramètre de la formule permettent d’arrondir avant les décimales).

1.3 Tableau de données à 2 variables – Hypothèque maximale + mise en forme conditionnelle Pour cet exemple, utilisez le fichier d’hypothèque créé au cas 1.1. Sélectionnez une nouvelle feuille de calcul.

Tapez les titres suivants à partir de la cellule A1 :

Paiement mensuel maximum Taux hypothécaire Taux hypothécaire mensuel effectif Montant de départ de l'analyse Incrément de l'anlayse

Page 5

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation Complétez les saisies suivantes à partir de la cellule D1 :

Cas 1

$1,000.00 7.50% $120,000 $2,500

Dans la cellule D3, utilisez l’équation suivante : =TAUX.NOMINAL(TAUX.EFFECTIF(D2;2);12)/12. La fonction Taux.nominal calcul le taux d’intérêt nominal à partir des valeurs du taux d’intérêt effectif et du nombre de période de composition. Notez l’imbrication de fonctions, puisque le taux d’intérêt effectif n’est pas fourni, la seconde partie de l’équation (Taux.effectif) calcule ce taux à partir des valeurs du taux d’intérêt nominal et du nombre de période de composition. L’équation proposée transforme un taux nominal annuel composé semestriellement en taux nominal équivalent composé mensuellement.

Dans la cellule D7, tapez « Amortissement », fusionnez et centrez les cellules D7 à H7.

Dans les cellules D8 à H8, Saisissez les formules suivantes

10

=D8+5

=E8+5

=F8+5

=G8+5

Dans la cellule B9, tapez « Montant du prêt », fusionnez les cellules B9 à B17 et centrez (horizontalement et verticalement). Utilisez l’orientation du texte « Rotation du texte vers le haut ».

Complétez les formules suivantes à partir de la cellule C9 :

=C10-$D$5 =C11-$D$5 =C12-$D$5 =C13-$D$5 =D4 =C13+$D$5 =C14+$D$5 =C15+$D$5 =C16+$D$5 Dans la cellule C8, tapez la formule suivante : « =VPM(D3;A8*12;-A9) ». La formule retournera une erreur, poursuivez. Formatez cette cellule avec un remplissage noir afin de masquer le contenu.

Sélectionnez les cellules C8 à H17. De l’onglet DONNÉES, ANALYSES DE SCÉNARIOS, sélectionnez TABLES DE DONNÉES. Complétez comme suit :

Page 6

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 1

Et cliquez OK. Sélectionnez les cellules D9 à H17. De l’onglet ACCUEIL, cliquez sur MISE EN FORME CONDITIONNELLE, RÈGLES DE MISE EN SURBRILLANCE DES CELLULES, SUPÉRIEURE À. Dans le premier champ, sélectionnez la cellule D1 et OK. De l’onglet ACCUEIL, cliquez sur MISE EN FORME CONDITIONNELLE, RÈGLES DE MISE EN SURBRILLANCE DES CELLULES, INFÉRIEURE À. Dans le premier champ, sélectionnez la cellule D1, dans le second champ, sélectionnez « remplissage vert avec texte vert » et OK. Remplacez les variables du tableau (D1, D2, D4 et D5) et observez les résultats dans la grille.

Page 7

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 2

Cas 2 – Gestionnaire de scénarios et Affichages personnalisés Étape 1 – Gestionnaire de scénarios : Cet exercice vous familiarisera avec le concept des scénarios. Cet outil permet de créer de multiples scénarios à partir de différentes valeurs de variables dans une même feuille de calcul.

Ouvrez le fichier cas 1.2 - analyse de sensibilité 2 variables - complété et nous sauvegarderons quelques scénarios. Sélectionnez la feuille de calcul Étape 2 si vous utilisez le fichier cas 1.2 complété fourni.

Sélectionnez l’onglet « Données ». Cliquez sur « Analyse de scénarios » afin d’ouvrir le menu déroulant et sélectionnez « gestionnaires de scénarios ». La boîte de dialogue suivante apparaîtra.

Cliquez sur « ajouter » pour ouvrir la boîte de dialogue. Compléter la boîte de dialogue avec le nom et les cellules variables ci-dessous (cliquez dans la cellule D5 et, en tenant la touche CTRL, cliquez sur la

Page 8

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 2

cellule C6). Cliquez OK pour fermer la boîte de dialogue.

La prochaine Boîte de dialogue vous offrira de saisir les valeurs (nombre ou texte) qui devront être utilisées pour ce scénario. Dans notre exemple, tapez 0,08 et 0,10. Cliquez sur « ajouter » pour créer un second scénario. Nommez ce scénario, « optimiste », tapez les valeurs 0,06 et 0,08 et le commentaire « Scénario calculé avec un TGA de 6% et un TRI de 8% ». Cliquez sur « ajouter » à nouveau et nommez le scénario « Réaliste », tapez les valeurs 0,07 et 0,09 et le commentaire « Scénario calculé avec un TGA de 7% et un TRI de 9% ». Cliquez sur OK pour fermer la Boîte de dialogue. La Boîte de dialogue suivante devrait apparaître à l’écran.

Cliquez sur le nom du scénario de votre choix (optimiste, pessimiste ou réaliste) et « afficher ».

Observez les résultats à l’écran.

Sélectionnez le scénario « Pessimiste » et cliquez sur « Modifier ». Ajoutez la cellule B4 à la liste des « cellules variables » (cliquez dans la boîte « cellules variables » et cliquez sur la cellule B4 en tenant la touche CTRL).

Cliquez sur OK pour fermer cette boîte de dialogue.

La Boîte de dialogue suivante vous permettra de saisir les valeurs des scénarios, tapez « Pessimiste »

Page 9

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 2

pour la valeur de la cellule B4. Cliquez sur OK pour fermer la Boîte de dialogue. Sélectionnez maintenant le scénario pessimiste et afficher.

Répétez cette opération pour chacun des deux autres scénarios en ajoutant le nom du scénario dans la cellule B4. Étape 2 –Affichage personnalisé : Dans cette étape, nous produirons différents formats d’affichages.

Dans l’onglet « affichage », cliquez sur « affichages personnalisés » pour ouvrir la boîte de dialogue suivante.

Ajoutez le nom « Affichage Complet » et fermez la boîte de dialogue.

Sélectionnez la ligne 4 en cliquant sur l’en-tête de ligne avec le bouton de droite de la souris et sélectionnez « masquer ».

Dans l’onglet « affichage », cliquez sur « affichages personnalisés » et sur « ajouter » et puis nommer le nouvel affichage « Affichage sans titre ». Cliquez à nouveau sur « affichages personnalisés » pour ouvrir la Boîte de dialogue et sélectionnez un des affichages et cliquez sur « afficher ». Vous avez maintenant 2 versions d’affichage et 3 scénarios.

Page 10

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 3

Cas 3 - Tableaux croisés dynamiques Les tableaux croisés dynamiques permettent de synthétiser les informations contenues dans une table de données. Le rapport de tableau croisé dynamique est un outil très puissant pour analyser de grandes quantités de données.

Lorsque vous créez un rapport de tableau croisé dynamique, chaque colonne de vos données sources devient un champ que vous pouvez utiliser dans le rapport. Les noms des champs du rapport proviennent des titres des colonnes de vos données sources. Vérifiez que chaque colonne porte un nom dans la première ligne de la feuille de calcul des données sources.

Les données que vous utilisez pour le rapport de tableau croisé dynamique ne doivent contenir aucune colonne ni ligne vide.

3.1 Synthèse somme — 3 critères L’exercice proposé produira un tableau synthèse illustrant la somme des honoraires gagnés par type de rapport, par évaluateur et par année (ou trimestre).

Ouvrez le fichier Cas 3.1 - tableaux croises dynamiques - données de base. Dans l’onglet sélectionné, placez le curseur n'importe où dans les données. (Toutes les données de la feuille de calcul seront incluses dans le rapport). Sous l'onglet Insertion, dans le groupe Tableaux, cliquez sur Tableau croisé dynamique, puis de nouveau sur Tableau croisé dynamique. La boîte de dialogue « Créer un tableau croisé dynamique » s'ouvre.

L'option « Sélectionner un tableau ou une plage » est déjà sélectionnée. La zone « Tableau/Plage » indique la plage de données sélectionnées. L'option « Nouvelle feuille de calcul » est également sélectionnée comme emplacement pour le rapport (vous pouvez cliquer sur « Feuille de calcul

Page 11

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 3

existante » si vous ne souhaitez pas placer le rapport dans une nouvelle feuille de calcul). Cliquez sur OK pour fermer la boîte de dialogue.

La page suivante apparaîtra.

Sur le côté gauche se trouve la zone de disposition prête pour le rapport de tableau croisé dynamique et sur le côté droit se trouve la liste de champs de tableau croisé dynamique. Cette liste affiche les titres des colonnes des données sources.

Commençons par afficher le volume de dossier de chaque évaluateur. Pour obtenir ce résultat, vous avez besoin des données sur les évaluateurs. Dans la Liste de champs de tableau croisé dynamique, vous devez donc activer la case à cocher du champ Évaluateur. Vous avez également besoin du volume, il vous faut donc activer la case à cocher du champ Montant.

Remarquez que vous n'avez pas besoin d'utiliser tous les champs de la liste de champs pour créer un rapport.

Lorsque vous sélectionnez un champ, Excel le place dans une zone des quatre zones par défaut. Vous pouvez déplacer le champ vers une autre zone si vous le souhaitez Le tableau suivant sera automatiquement produit. Par Excel produira la somme des valeurs du champ numérique Montant.

défaut, Étiquettes de lignes Somme de Montant Evaluateur 1 106474,87 Evaluateur 2 122549,84 Evaluateur 3 141561,65 Evaluateur 4 138720,08 Evaluateur 5 150733,38 Ajoutez le champ Type à la section étiquette de lignes avant Evaluateur 6 136090,36 évaluateur en glissant le titre du champ avec la souris et Evaluateur 7 181076,09 observez le changement. Le rapport de tableau Evaluateur 8 139684,66 dynamique a produit un sous-total par type et un total par évaluateur pour chaque type.

Evaluateur 9 111436,47 Total général 1228327,4 Page 12 Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation Étiquettes de lignes Autres Evaluateur 1 Evaluateur 2 Evaluateur 3 Evaluateur 4 Evaluateur 5 Evaluateur 6 Evaluateur 7 Evaluateur 8 Evaluateur 9 Commercial Evaluateur 1 Evaluateur 2 Evaluateur 3 Evaluateur 4

Cas 3 Somme de Montant 82312,22 6050,87 2144,64 10692,9 10497,2 6381,96 21734,95 9809,85 6306,4 8693,45 55311,86 8814,5 3793,48 3185,96 7819,1

Étiquettes de lignes Somme de Montant Placez votre souris dans la cellule B5 et Autres 82312,22 sélectionnez l’icône TRIER de l’onglet Options.

Evaluateur 2 2144,64 Dans la nouvelle fenêtre, sélectionnez « du plus Evaluateur 1 6050,87 petit au plus grand » dans les options du tri. Les Evaluateur 8 6306,4 résultats du champ sont automatiquement Evaluateur 5 6381,96 réorganisés en ordre croissant, et ce, pour chaque Evaluateur 9 8693,45 type.

Evaluateur 7 9809,85 Evaluateur 4 10497,2 Evaluateur 3 10692,9 Sélectionnez l’onglet création et choisissez l’option Evaluateur 6 21734,95 afficher tous les sous-totaux au bas des groupes Commercial 55311,86 du bouton sous-totaux. Les sous-totaux pour Evaluateur 9 1942,54 chaque groupe seront réaffichés à la fin de chaque Evaluateur 3 3185,96 groupe.

Evaluateur 2 3793,48 Evaluateur 7 4954,06 Evaluateur 6 6589,73 Evaluateur 8 6742,3 Pour changer l’opération mathématique appliquée Evaluateur 4 7819,1 sur les valeurs, placez la souris dans la cellule B5, Evaluateur 1 8814,5 sélectionnez l’onglet « Options » et sélectionnez Evaluateur 5 11470,19 « calculs». Sélectionnez le menu « Résumé les Entreprise 77716,76 valeurs par » pour choisir une nouvelle opération Evaluateur 7 3952,5 mathématique à appliquer. Nous choisirons le « nombre » (NB) pour l’exercice. Vous pouvez aussi faire des expériences avec d’autres opérations.

Page 13

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Étiquettes de lignes Autres Evaluateur 2 Evaluateur 5 Evaluateur 8 Evaluateur 9 Evaluateur 3 Evaluateur 4 Evaluateur 7 Evaluateur 6 Evaluateur 1 Total Autres Commercial Evaluateur 2 Evaluateur 3 Evaluateur 9 Evaluateur 4 Evaluateur 7 Evaluateur 8 Evaluateur 5 Evaluateur 6 Evaluateur 1 Ajoutons une autre Total Commercial

Cas 3

Valeurs Ajoutons nouvelle Nombre de Montant une Somme de Montantcolonne.

Sélectionnez le champ Montant de la liste de champs et faites le glisser dans la section «  des valeurs ». Une nouvelle 2 2144,64 colonne s’ajoutera au rapport du tableau croisé dynamique illustrant les 4 6381,96 5 6306,4 par type et par évaluateur. totaux du champ Montant 5 6 7 8 9 9 55

3 4 4 5 6 6 7 7 11 Sélectionnez 53

8693,45 10692,9 10497,2 9809,85 21734,95 6050,87 82312,22

3793,48 3185,96 1942,54 7819,1 4954,06 6742,3 11470,19 6589,73 8814,5 champ 55311,86date

colonne. le de la liste de champs et faites la glisser dans la section « étiquettes de lignes » après le champ évaluateur. Une nouvelle colonne s’ajoutera au rapport du tableau croisé dynamique illustrant la date de chaque vente. Afin de synthétiser l’information, nous grouperons ce champ en trimestre. Sélectionnez une cellule contenant une date, cliquez sur l’icône « Grouper la sélection » de l’onglet options. Choisissez par années et trimestre et cliquez sur OK pour fermer la boîte de dialogue. Le tableau suivant sera produit. Notez que vous pourriez limiter les dates retenues pour produire ce tableau par l’option de groupe.

Page 14

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Étiquettes de lignes Autres Evaluateur 2 2009 Trimestre4 2011 Trimestre1 Total Evaluateur 2 Evaluateur 5 2010 Trimestre1 2011 Trimestre1 Total Evaluateur 5 Evaluateur 8 2009 Trimestre3 2010 Trimestre1 Trimestre3 2011 Trimestre2

Cas 3

Valeurs Nombre de Montant Somme de Montant

1

2046,24

1 2

98,4 2144,64

2

4886,46

2 4

1495,5 6381,96

2

3771

1 1

230,4 72

1

2233

Dans une dernière étape, nous utiliserons les styles pour rapidement formater le rapport de tableau croisé dynamique. Dans l’onglet création, sélectionnez le style moyen 2 de la liste des styles. Étiquettes de lignes Autres Evaluateur 2 2009 Trimestre4 2011 Trimestre1 Total Evaluateur 2 Evaluateur 5 2010 Trimestre1 2011 Trimestre1 Total Evaluateur 5 Evaluateur 8 2009 Trimestre3 2010 Trimestre1 Trimestre3 2011 Trimestre2

Valeurs Nombre de Montant Somme de Montant

1

2046,24

1 2

98,4 2144,64

2

4886,46

2 4

1495,5 6381,96

2

3771

1 1

230,4 72

1

2233

Le rapport du tableau croisé dynamique peut aussi présenter l’information sous d’autres formes. Pour compléter la nouvelle présentation, faites glisser le titre du champ Type de la section Étiquette de lignes vers la section Étiquettes de colonnes et supprimer le champ Nombre de montant de la section «  des valeurs ».

Page 15

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 3

Vous pouvez aussi transformer l’information en forme graphique. De l’onglet options, choisissez Graphique croisé dynamique de la section Outils. Sélectionnez le type de graphique recherché (secteur pour notre cas).

Vous obtiendrez le graphique suivant. Utilisez les filtres du rapport pour isoler les années voulues et le type de dossier.

Filtrez les années en n’incluant que 2011, les trimestres en n’incluant que le trimestre 1 et le type en ne retenant que commercial

Page 16

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 3

3.2 Synthèse moyenne - 4 critères Cet exercice produira un tableau synthèse des TGA répertoriés et publiés depuis 2009 par la firme CB Richard Ellis. Ouvrez le fichier Cas 3.2 - tableaux croises dynamiques - données de base.

Pour débuter un tableau croisé dynamique, il suffit de sélectionner les données sources. Pour ce faire, sélectionnez une cellule à l’intérieur de la plage de données et cliquez sur « tableau croisé dynamique » de l’onglet « Insertion ». Une boîte de dialogue apparaîtra dans laquelle la plage de cellule contenant les données a été identifiée.

Par défaut, le tableau sera affiché sur une nouvelle feuille de calcul.

Une nouvelle feuille de calcul est insérée dans le classeur Excel. Activez les champs que vous désirez intégrer dans le tableau croisé dynamique. Vous pouvez aussi faire glisser avec la souris le nom du champ désiré dans la section de votre choix. Pour notre exemple, nous désirons produire un tableau contenant les TGA par Ville, Année et trimestre. Par contre, l’information TGA du tableau de données sources est fournie en deux champs soient Min et Max. Nous devrons créer un champ calculé qui produira la moyenne des deux observations.

Sélectionnez l’onglet « Option » et puis cliquez sur « calculs », « Champs, éléments et jeux » et « champ calculé ». Dans la boîte nom Tapez « TGA moyen ». Dans la boîte formule, il suffit de taper la formule pour la moyenne en indiquant les champs à utiliser dans le calcul.

Page 17

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 3

Le champ TGA moyen apparaîtra maintenant comme un champ disponible. Il suffit de le glisser dans la section «  des valeurs ». Glissez le champ Ville dans la section « étiquettes de colonne », les champs Année et Trimestre dans la section « Étiquettes des lignes » et le champ Type dans la section « Filtre ».Le filtre apparaîtra dans le haut du tableau et permettra de choisir le Type de propriété pour laquelle les données seront affichées.

Dans l’onglet « création », sélectionnez sous-totaux et ne pas afficher les sous-totaux. Sélectionnez et désactiver pour les lignes et les colonnes. Utilisez le filtre pour sélectionner « Office dtown A » (n’oubliez pas d’appliquer le filtre en haut à gauche sinon les TGA sont additionnés pour tous les types d’immeubles).

Page 18

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 3

Pour insérer un graphique, sélectionnez l’onglet « options » et « Graphique croisé dynamique » de la section « Outils ». Sélectionnez un graphique courbe et cliquez sur OK pour fermer la boîte de dialogue.

Page 19

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 4

Cas 4 – Fonctions de base de données et logiques Dans ce cas, nous effectuerons des recherches horizontales et appliquerons des fonctions logiques afin de produire un tableau synthèse des données TGA répertoriées et publiées depuis 2009 par la firme CB Richard Ellis. Le tableau synthèse sera constitué des valeurs de TGA pour 8 trimestres consécutifs à partir d’une date donnée, et ce, pour une ville et un type de propriété. Ouvrez le fichier Cas 4 - outils base de données et fonctions logiques - données de base.

La fonction RECHERCHEV d'Excel permet de faire exécuter au tableur une recherche dans un tableau de valeurs et de renvoyer la valeur trouvée dans une cellule située à sa droite. Notez que cette fonction ne peut faire la recherche que sur une colonne (exemple = : Trimestre ou année ou type ou Ville). Notre objectif est de produire une recherche sur les quatre colonnes simultanément afin d’isoler une valeur spécifique au trimestre d’une année, pour un type de propriété et dans une ville. Nous devrons donc construire un champ de recherche complexe. Étape 1 : Pour compléter cette première étape, placez le curseur dans la cellule A1 et Tapez « Code_recherche ». Dans la cellule A2, tapez « =B2&C2&D2&E2 ». Remarquez la syntaxe des signes « & ». Dans cette formule, les valeurs des cellules B2, C2, D2 et E2 sont simplement ajoutées les unes aux autres. Ce nouveau champ « code » permettra à la fonction RECHERCHEV de repérer une valeur de trimestre, d’année, de type et de ville précise dans le tableau. Recopiez cette formule jusqu’à la ligne A1101. Étape2 : Afin de faciliter la saisie des formules dans les prochaines étapes, nous nommerons la plage de cellules A1 à G1101. Sélectionnez cette plage de cellules et cliquez sur l’onglet « Formules ». Ensuite, sélectionnez « Définir un nom » et tapez le nom « CBRE_TGA » dans la boîte appropriée. Nous utiliserons ce nom dans les étapes suivantes pour faire référence à la plage A1 à G1101

Étape 3 : Dans les cellules N2 à Q3, tapez ceci :

Type Bureau CV A

Ville Ottawa

Trimestre 1

2009 Ce seront les paramètres de notre première recherche, soit les TGA inférieur et supérieur pour le premier trimestre 2009 dans les immeubles de bureaux de catégorie A au centreville d’Ottawa. Étape 4 : Ici nous débuterons la construction du tableau synthèse sur 8 trimestres consécutifs. Dans la cellule K4, tapez « =P3 » et dans la cellule L4 tapez « =Q3 ». Dans la cellule M4, tapez la formule suivante « = "T"&K4&L4&$N$3&$O$3». Cette formule produit la lettre T suivi de la valeur en K4 (1), suivi de la valeur en L4 (2009), suivi de la valeur en N3 (Bureau CV A), suivi de la valeur en O3 (Ottawa). Ce champ reproduit celui créé dans la colonne code_recherche du tableau des TGA. Notez les signes $ placé dans Page 20

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 4

les références N3 et O3. Les références fixes permettront de recopier cette formule vers le bas sans altérer les valeurs de ces variables. Dans la cellule N4, tapez ceci « =RECHERCHEV(M4;CBRE_TGA;6;FAUX) »2. Cette formule effectue l’opération suivante : Elle recherche la valeur en M4 dans le tableau CBRE_TGA. Une fois cette valeur identifiée dans le tableau, la formule retourne la valeur située dans la 6 ième colonne de cette ligne, soit le TGA minimum. Le dernier paramètre (faux) informe la formule que la valeur exacte doit être recherchée et non une valeur proche.

Dans la cellule O4, tapez « =RECHERCHEV(M4;CBRE_TGA;7;FAUX) » pour retourner la valeur de la 7ième colonne, soit le TGA maximum. Étape 5 : Pour compléter le tableau, nous devrons reproduire les mêmes RECHERCHEV en prenant en compte les 7 prochains trimestres. Les cellules K5 à K11 doivent produire une séquence numérique de 1 à 4 en boucle; pour réussir ceci, nous utiliserons une fonction logique. Tapez la formule suivante « =SI(K4=4;1;K4+1) ». Cette formule effectue ceci : Si la valeur en K4 = 4, alors la formule retourne 1, sinon, elle fait la somme de K4 + 1. Recopiez cette formule jusqu’à la cellule K11 et observez, vous devriez voir une séquence répétitive de 1 à 4.

Les cellules L5 à L11 devront produire l’année en cours jusqu’au trimestre 4 et ajouter une année par la suite; nous utiliserons encore la fonction SI. Tapez « =SI(K5=1;L4+1;L4) ». Cette formule effectue ceci : Si la valeur en K5 = 1, alors faire la somme L4+1, sinon utilise la valeur K4. Recopiez cette formule jusqu’à L11.

Sélectionnez les cellules M4 à O4 et recopiez jusqu’à la ligne 11. Le tableau des 8 trimestres est complet. Étape 6 : Les recherches dans les plages de données demandent de la rigueur dans la saisie des critères de recherche afin de produire les résultats escomptés, nous appliquerons alors des limites aux cellules contenant les paramètres.

Dans les cellules T2 à T10, tapez les valeurs suivantes.

2 Pour saisir cette fonction, cliquez sur l’outil d’insertion de fonction (ƒx) et recherchez la fonction à insérer, recherchev. Une fois sélectionnée, complétez la boîte de dialogue avec la valeur à rechercher (M4), la table source (cbre_TGA), le nombre qui correspond au # de la colonne contenant la valeur à retourner (6) et la valeur qui représente si vous désirez une correspondance exacte à votre recherche ou non (faux). Cliquez sur OK pour fermer la boîte de dialogue.

Page 21

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 4

Ind Multi A Ind Multi B Bureau CV A Bureau CV AA Bureau CV B CCommercial Communautaire CCommercial Power CCommercial régional CCommercial strip Dans les cellules U2 à U9, tapez les valeurs

Sélectionnez la cellule N3. À partir de l’onglet « Données » sélectionnez validation de données et complétez la Boîte de dialogue tel qu’illustré ici : Assurez-vous de cocher la case « liste déroulante dans la cellule ».

Dans le champ « Source », sélectionnez cellules T2 à T10. Cliquez sur OK pour fermer la Boîte de dialogue.

les

Répétez les opérations de validation de données dans la cellule O3 en faisant référence à la source U2 à U9.

Page 22

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 4

Sélectionnez la cellule P3 et « validation de données ». Sélectionnez « liste » dans le champ « autoriser » et tapez les nombres « 1, 2, 3, 4 » dans le champ « source ». Cliquez sur OK pour fermer la Boîte de dialogue. Les virgules indiquent les éléments d’une liste.

Utilisez maintenant les menus déroulant situés dans les cellules N3, O3 et P3 pour sélectionner les paramètres suivants :CCommercial Communautaire, Montréal, 3, et 2009. Observez les résultats.

Étape 7 : Nous voudrons maintenant faciliter la saisie de nouvelles données dans la base de données.

Nous afficherons une boîte de dialogue représentant les entrées de la banque de données et nous permettant de compléter de nouvelles entrées.

Cette boîte de dialogue n’est pas disponible par défaut avec Excel 2010, vous devrez faire les opérations suivantes une seule fois afin de rendre cette boîte de dialogue disponible.

Page 23

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 4

À droite de barre d’accès rapide, cliquez sur le flèche vers le bas afin d’ouvrir le menu de personnalisation; sélectionnez « autres commandes ». Sélectionnez « toutes les commandes ».

Dans cette liste, trouvez la commande « formulaire de données » et cliquez sur ajouter. L’outil formulaire de données apparaîtra maintenant à votre barre d’outils d’accès rapide.

Sélectionnez la plage CBRE_TGA (cliquez sur la flèche vers le bas située au côté du nom de la cellule et sélectionnez CBRE_TGA de la liste déroulante) et cliquez sur l’icône de formulaire de votre barre d’accès rapide. La boîte de dialogue suivante deviendra active. Vous pourrez maintenant utiliser la boîte de dialogue pour consulter les données, ajouter de nouvelles inscriptions.

Utilisez le bouton « critères » pour limiter les inscriptions affichables.

Une fois vos critères établis, cliquez sur suivant pour afficher la prochaine inscription satisfaisant vos critères.

Cliquez sur « grille » pour revenir à l’affichage complet.

Page 24

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 5

Cas 5 – TRI, Groupe et Sous-totaux Pour compléter l’exercice, ouvrez le fichier cas 5 - groupe et sous-totaux - données base et sélectionnez l’onglet DONNÉES. Cette base de données contient des transactions fictives de terrains dans différents secteurs et de différents types.

Sélectionnez l’onglet « Données » et sélectionnez les cellules A1 à F10. Dans la section « trier et filtrer », cliquez sur l’icône TRIER, la boîte de dialogue suivante apparaîtra.

Assurez-vous que l’option « mes données ont des en-têtes » est sélectionnée. Cette option retirera la première ligne des données à trier et utilisera ces informations pour générer des titres de colonnes.

Dans le menu déroulant « trier par », choisissez le titre « type » (remarquez les titres des colonnes offerts). Cliquez sur « ajouter un niveau », ceci permettra de faire un second niveau de tri. Trier le second niveau par « localisation ». L’ordre « de A à Z » indique un tri croissant des valeurs, nous conserverons cette option.

Voici la boîte de dialogue complétée. Cliquez sur OK pour effectuer le tri.

Avec le TRI, les données sont déplacées dans le tableau de façon permanente.

ATTENTION : Si des données sont situées à l’extérieur du champ sélectionné pour exécuté le tri, est ne sont pas triées et demeurent à l’emplacement initial.

Page 25

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 5

Cliquez maintenant sur l’icône « filtre ». Vous remarquerez l’ajout de bouton contenant un triangle au côté des titres de colonnes. Cliquez sur le bouton de filtre du champ « localisation » pour ouvrir la boîte de dialogue suivante. L’option « sélectionner tout » est cochée. Décochez cette sélection et cochez « banlieue ». Seules les entrées ayant la valeur « banlieue » sont affichées. Les autres entrées existent toujours, mais ne sont plus visibles. Sélectionnez maintenant l’icône de filtre du type. La boîte de dialogue n’affiche que les types « Industriel » et « résidentiel ».

Le type « commercial » n’est pas disponible, car aucun terrain de ce type n’est disponible dans la plage déjà triée. Cochez « industriel » et voyez le résultat. Seules les deux entrées satisfaisant les deux critères sont affichées.

Pour retirer les filtres, cliquez sur l’icône de filtre et cliquez sur « effacer le filtre… ». Dans la section « trier et filtrer » de l’onglet « données », vous pouvez aussi retirer tous les filtres en cliquant sur l’icône « effacer ». Les données sont à nouveau visibles.

Une fois les données triées, nous chercherons à calculer la moyenne des prix unitaires pour chaque type.

Pour compléter cette opération, sélectionnez une cellule à l’intérieur de la plage de données. Dans la section « plan » de l’onglet « données », cliquez sur « sous-total » pour activer la boîte de dialogue suivante.

Le premier champ à compléter gère la fréquence à laquelle faire une opération mathématique. Nous choisirons de calculer un « sous-total » après chaque changement de type.

Le second champ gère l’opération mathématique à employer, nous choisirons ici une moyenne.

Le troisième champ gère le champ numérique sur lequel effectuer l’opération choisie. Assurez-vous que le champ prix unitaire soit cochés. données » pour que le mathématiques soit

Cochez l’option « synthèse sous les résultat des opérations situé sous les plages de données.

La boîte de dialogue

devrait apparaître tel que suit.

Cette fonction introduit le document. Les

automatiquement des groupes dans groupes se présentent sous la forme

Page 26

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 5

d’un plan de travail situé à la gauche de l’écran (1 2 et 3) accompagné de signes -. Les nombres 1, 2 et 3 représentent les niveaux du plan de travail, le niveau 1 est la moyenne générale de toutes les observations, le niveau 2 les moyennes par type et le niveau 3 les observations individuelles. En cliquant sur les signes moins, les données apparaissant seront modifiées. Observez les résultats en cliquant successivement sur les signes + et – ou sur les niveaux du plan 1, 2 ou 3.

Page 27

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 6

Cas 6 – Régression linéaire Avant de pouvoir effectuer une régression linéaire avec Excel, vous devez vous assurer que le complément (add-in) est installé. Pour installer le complément, sélectionnez l’onglet fichier, option Excel, Compléments, Analysis ToolPak – VBA et cliquez sur le bouton ATTEINDRE. Dans la boîte de dialogue suivante, cochez Analysis tool pak et Analysis toolpak – VBA pour les activés. S’ils sont déjà activés, quittez la boîte de dialogue sans faire de changement.

L’analyse de régression linéaire utilise la méthode des « moindres carrés » pour tracer une droite sur l’ensemble des observations et analyse l’incidence des variables indépendantes sur la variable dépendante unique. Excel peut prendre en compte jusqu’à 16 variables indépendantes.

6.1 Salaires – Régression linéaire simple Le cas de régression des salaires est contenu dans la page de calcul « salaire » du fichier cas 6.1-6.2 régression linéaire - données base.

L’étude de cas pour la régression porte sur les salaires relativement aux années de scolarité. Nous voulons déterminer la relation entre ces deux variables.

Cliquez sur l’onglet DONNÉES et sélectionnez L’UTILITAIRE D’ANALYSE. Dans le menu déroulant, sélectionnez RÉGRESSION LINÉAIRE. La variable Y représente la variable dépendante, soit celle pour laquelle nous tentons d’expliquer les variations en fonction de la ou des variables indépendantes. La ou les variables X représentent les variables indépendantes de notre fonction linéaire. Dans la section Options de sortie, déterminez l’emplacement des tableaux d’analyse et les graphiques de soutien.

Complétez la saisie telle qu’illustrée ci-dessous et cliquez OK.

Page 28

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 6

Excel génère un tableau d’analyse de la régression à partir de la cellule F1. Les graphiques représentant la courbe des résidus et la courbe de régression sont situés autour de la cellule O1.

Le tableau d’analyse : Ce dernier comporte un nombre d’analyses statistiques pertinentes relatives à la régression linéaire.

Les résultats affichés sont : - le coefficient de détermination multiple (dans le cas à deux variables, cela correspond simplement au coefficient r de corrélation de Pearson).

- le coefficient de détermination R² : il donne une idée du % de variabilité de la variable à modéliser, et plus le coefficient R² est proche de 1, plus il y a une corrélation et meilleur est le modèle.

- les coefficients : sont les coefficients de la droite de régression. Ces coefficients sont présentés sous la forme d’une constante et des facteurs X. Dans l’exemple, la constante est 12 586.56 et le facteur X est 1 755.31, soit l’équation linéaire Y=12 586.56 + (X * 1 755.31).

Il ne faut pas oublier de prêter attention à la colonne des probabilités. Surtout celle de la variable X. Si cette dernière est < 0.05, alors la variable X est significative. Dans l’exemple, nous obtenons une probabilité de 0,001, donc la variable est significative.

L’analyse des résidus nous présente l’évaluation de la valeur de Y selon la fonction linéaire produite ainsi que la différence entre l’estimation et la valeur réelle. Le graphique des résidus illustre la même information sous forme graphique.

Page 29

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 6

Le dernier graphique illustre la droite de régression, soit les valeurs estimées par l’équation linéaire et les valeurs réelles.

(Notez qu’Excel peut réaliser une une variable en utilisant un Sélectionnez la plage de cellules B2 à insérez un graphique de type points ». Dans l’onglet Disposition, courbe de tendance linéaire ainsi que courbe « Afficher l’équation sur le

régression à graphique.

C14 et « nuages de sélectionnez l’option de graphique ».)

6.2 TRI– Régression linéaire multiple Le cas de régression des TRI est contenu dans l’onglet « TRI » du fichier cas 6.1-6.2 - régression linéaire données base.

Dans le second cas, l’étude est purement fictive et porte sur la prime immobilière en fonction de caractéristiques d’un immeuble. Pour compléter l’exemple, nous nous baserons sur les TRI analysés de transactions. Puisque ces taux fluctuent dans le temps sans égard aux caractéristiques de l’immeuble, nous porterons notre analyse plutôt sur l’écart entre notre base de référence, soit les obligations 10 ans au moment de la transaction, et le TRI observé. Les variables indépendantes retenues seront : Immeuble non résidentiel (0 - multirésidentiel, 1 - non résidentiel) Risque du revenu (0 = revenu stable, 1 = revenu variable) Risque de locataire (0 = locataire renommé, 1 = locataire inférieur) Localisation (0= excellente localisation, 1 = localisation faible) Notez encore que ces variables sont fictives et ne sont présentées que pour des fins explicatives.

Suivre les indications du cas 6.1 pour atteindre l’utilitaire d’analyse et compléter tel qu’illustré cidessous :

Page 30

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 6

L’équation résultante explique bien la relation (Coefficient de détermination multiple = 0.86). L’équation est Y= 0.01386429 + (0.00405514581 *x1) + (0.032621521* x2) + (0.0109489986* x) + (0.00948669030 * x4).

Maintenant, poursuivons tout de même en utilisant l’équation pour déterminer le TRI du sujet. Les valeurs des variables du sujet sont contenues dans les cellules C15, E15 à H15 Dans la cellule D15, saisissez la formule suivante (résultat de la régression) : =J17+(E15*J18)+(F15*J19)+ (G15*J20)+(H15*J21). Ceci recalcule la prime immobilière du sujet en fonction de ses caractéristiques propres.

Pour compléter le calcul du TRI, additionnez la prime calculée à l’indice de base actuel =C15+D15 dans la cellule B15.

Selon notre formulation, le TRI résultant pour notre sujet, en fonction des caractéristiques notées, serait de 8.61%.

Des exemples d’applications pratiques à l’immobilier seraient de déterminer la valeur d’une propriété en fonction de caractéristiques physiques des comparables ou de déterminer la prime immobilière comprise dans le TRI ou le TGA en fonction de caractéristiques d’un immeuble, etc.

6.3 Vente résidentielle – Régression linéaire multiple Dans ce cas, les données sont tirées de la banque de données municipales des ventes résidentielles. Afin de conserver la confidentialité des données, toutes références aux transactions ont été supprimées.

Nous pratiquerons une régression linéaire multiple afin d’expliquer le prix de vente des résidences. Les Page 31

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 6

données ont été validées au préalable et transformées en forme numérique afin d’être compatibles avec le modèle de régression linéaire. Ouvrez le fichier Cas 6.3 - régression linéaire multiple - données de base. L’onglet « Données originales » contient les données sous la forme textuelle consignée par la municipalité (pour des fins de compréhension seulement). L’onglet « données transformées » illustre la représentation mathématique adéquate de ces mêmes données pour utiliser une régression.

Nous ferons quelques essais dans cet exemple, donc conservez les données transformées dans l’état actuel et recopiez l’onglet. (Utilisez le bouton de droite sur le nom de l’onglet et sélectionnez « déplacer et copier ». Sélectionnez « en dernier » et cocher « créer une copie ».

Pour effectuer la régression, débutez par supprimer le contenu des colonnes T à AO. Sélectionnez l’onglet « données » et ensuite sur « Data analysis » ou « Analyse de données ». Dans la boîte de dialogue, sélectionnez « régression ». Dans la boîte contenant la variable Y, sélectionnez les données de la colonne « prix de vente » incluant la ligne de titre. Dans la boîte de contenant les variables X, sélectionnez les 16 colonnes suivants le « prix de vente » (colonnes D à S). Assurez-vous que les cases de titre et « résiduels » sont cochées. Dans la case contenant la plage de sortie, utilisez U1.

Cliquez sur OK pour fermer la boîte de dialogue et générer les résultats. Les résultats suivants sont calculés et insérés à partir de la cellule U1.

Page 32

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 6

SUMMARY OUTPUT Regression Statistics Multiple R 0,818366221 R Square 0,669723272 Adjusted R Square 0,643819215 Standard Error 13862,4135 Observations 221 ANOVA df Regression Residual Total

Intercept

VENDU _AGENT AGE APP AIRE HAB AIR FINSS SUP TER

SS 16 79492340436 204 39201967664 220 1,18694E+11

MS 4968271277 192166508,2

F Significance F 25,85399155 1,56934E-40

Coefficients Standard Error 135905,3484 8895,853947

t Stat 15,27738081

P-value 1,24156E-35

8716,874228

2114,208062

4,122997345

5,44021E-05

4548,372979 12885 4548,4 12885

-1706,516249

143,1447427

-11,92161316

3,29178E-25

-1988,749133 -1424 -1989 -1424

525,9343495

88,48299502

5,943903112

1,18895E-08

351,475893 700,4 351,48 700,39

179,9668647

37,45646072

4,80469487

2,99974E-06

106,1154278 253,8 106,12 253,82

7,504021066

3,189647477

2,352617686

0,019593895

1,215118035 13,79 1,2151 13,793

Lower 95% Upper 95% Lower 95,0% Upper 95,0% 118365,7415 2E+05 118366 153445

NB FOYER Nb_SB CH_ Huile THERMO Remise GarAtt GarDét AbriAuto PISCINE

9996,312759 1491,685442

3213,438241 2040,96309

3,110784154 0,730873306

0,002132998 0,465695141

3660,502409 16332 3660,5 16332 -2532,401563 5516 -2532 5515,8

5020,538748 7048,903338 -2832,70334 7696,892389 5266,197674 -2138,172783 1890,247569

3204,95219 3390,994313 2401,61105 4329,109133 3416,585233 5211,487115 2002,830931

1,566494116 2,078712816 -1,179501293 1,777939098 1,541362886 -0,410280739 0,943787885

0,118782841 0,038894466 0,239572333 0,076903459 0,12477823 0,682030583 0,34639446

-1298,539986 363,0126334 -7567,865807 -838,642572 -1470,149645 -12413,45786 -2058,655752

Stat PavéBét

-6000,587343

4613,296877

-1,300715628

0,194823189

-15096,44415

3095 -15096 3095,3

Stat CONCASSÉ

-2827,405138

2273,002797

-1,243907461

0,214961053

-7308,995748

1654 -7309 1654,2

11340 13735 1902 16232 12003 8137 5839

-1299 363,01 -7568 -838,6 -1470 -12413 -2059

11340 13735 1902,5 16232 12003 8137,1 5839,2

L’analyse des résidus produit après le tableau présente la valeur prédite par le modèle pour chaque transaction et l’écart entre la valeur réelle et la valeur prédite (le résidu).

Page 33

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 6

Afin de générer un second essai, nous retrancherons 4 observations, soient celles qui ont les plus grands résidus. (ventes 14, 67, 126, 131). Supprimez ces transactions de la plage de données et recommencez l’analyse. Vous remarquerez que les plages sources et destinations sont déjà inscrites dans la Boîte de dialogue, remplacez les lignes 222 par 218 et cliquez OK. La prochaine Boîte de dialogue vous mentionnera que la plage de sortie contient des données, acceptez de récrire les résultats et observez. À cette étape, nous pourrions décider de remplacer certaines variables afin d’observer les résultats.

SUMMARY OUTPUT Regression Statistics Multiple R 0,84759633 R Square 0,71841953 Adjusted R Square 0,6958931 Standard Error 12221,7601 Observations 217 ANOVA df Regression Residual Total

Intercept VENDU _AGENT AGE APP AIRE HAB AIR FINSS SUP TER NB FOYER Nb_SB CH_ Huile THERMO Remise GarAtt GarDét AbriAuto PISCINE Stat PavéBét Stat CONCASS É

Page 34

SS MS F Significance F 16 7,6221E+10 4763795735 31,8922837 2,4157E-46 200 2,9874E+10 149371421 216 1,061E+11

CoefficientsStandard Error t Stat P-value Lower 95% Upper 95% Lower 95,0% Upper 95,0% 124813,341 8166,48402 15,283608 1,8992E-35 108709,882 140916,8 108709,882 140916,8 6598,34604 1887,86521 3,49513621 0,00058348 2875,67185 10321,0202 2875,67185 10321,0202 -1687,9527 126,566159 -13,336525 1,9129E-29

-1937,528 -1438,3774

-1937,528 -1438,3774

676,785764 82,0913994 8,24429562 2,1742E-14 514,910048 838,661481 514,910048 838,661481 152,120323

33,710155 4,51259636 1,0915E-05

85,647397 218,593249

85,647397 218,593249

5,17420004 2,83521578 1,82497575 0,06949646 -0,4165511 10,7649512 -0,4165511 10,7649512 7462,67321 2882,1138 2,58930553 0,01032377 1779,44401 13145,9024 1779,44401 13145,9024 1208,93219 1808,45764 0,66848798 0,50459295 -2357,1585 4775,02289 -2357,1585 4775,02289 5858,84506 3168,32871 16,7142978 10469,8644 7411,08121 -2531,6444 1054,54036 -3907,9022

2843,00688 3074,51012 2246,27742 4006,06208 3126,30133 4605,96898 1787,07164

2,06079173 1,03051497 0,00744089 2,61350527 2,37055883 -0,5496443 0,59009406

0,0406169 0,30401292 0,9940705 0,00964324 0,01871188 0,58317663 0,5557937

252,73067 -2894,286 -4412,7116 2570,32586 1246,33957 -11614,137 -2469,3794

11464,9595 9230,94346 4446,14015 18369,4029 13575,8228 6550,84824 4578,4601

252,73067 -2894,286 -4412,7116 2570,32586 1246,33957 -11614,137 -2469,3794

11464,9595 9230,94346 4446,14015 18369,4029 13575,8228 6550,84824 4578,4601

4362,8558 -0,8957211 0,37147851 -12511,001 4695,19652 -12511,001 4695,19652

-1983,9934 2008,49787 -0,9877996 0,32444432 -5944,5427 1976,55586 -5944,5427 1976,55586

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

Cas 7 – Interaction Les cas suivants portent sur l’interaction entre Excel et le Web, Access et Word.

7.1

Excel et Web

7.1.1 Banque du Canada Dans cet exercice, nous importerons des données provenant d’un site internet tout en conservant un lien automatisé avec les données à jour. Étape 1 – Création du lien.

Naviguez sur Internet pour trouver les données recherchées. Pour le cas, nous recherchons les données relatives aux taux d’intérêt de la Banque centrale du Canada, obligations 10 ans. Vous trouverez ces données à l’adresse suivante http://www.banqueducanada.ca/stats/bond_yields_five_html (voir le fichier raccourci Cas 7.1.1 - Lien web Banque du Canada) Ouvrir un nouveau fichier Excel. De l’onglet DONNÉES, cliquez sur DONNÉES EXTERNES et sur A PARTIR DU SITE WEB. Dans le navigateur web qui apparaît, insérez l’adresse : http://www.banqueducanada.ca/stats/bond_yields_five_html et OK. La page de la Banque du Canada apparaîtra. Remarquez les flèches jaunes. Celles-ci présentent les données pouvant être sélectionnées. Naviguez jusqu’à la section des obligations 10 ans et sélectionnez les informations. La flèche jaune sera transformée en crochet vert.

Appuyez sur le bouton IMPORTER et sélectionnez la plage d’importation dans MS Excel (A1 pour l’exemple) et OK. L’information a été importée à votre fichier Excel. Étape 2 – Mise à jour Maintenant, de l’onglet DONNÉES, cliquez sur ACTUALISER TOUT. Les données sont immédiatement téléchargées du site web. (Sauvegardez le fichier et remettre à jour plus tard puisque les données ne changent pas à un rythme rapide).

Page 35

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

7.1.2 ISQ répertoire municipal Pour obtenir un répertoire des municipalités du Québec, nous construirons un lien avec une base de données de l’institut de Statistique du Québec.

Pour se faire, nous utiliserons un fichier vierge pour débuter. De l’onglet DONNÉES, cliquez sur DONNÉES EXTERNES et sur A PARTIR DU SITE WEB. Dans le navigateur web qui apparaît, insérez l’adresse http://www.stat.gouv.qc.ca/clacon/abonnem ent/municipalites_officielles_fr.htm (voir le fichier raccourci Cas 7.1.2 - lien web ISQ) et OK. Trois flèches jaunes apparaissent à l’écran, activez la troisième (elle devient verte lorsqu’elle est activée) et cliquez « importer ». La boîte de dialogue suivante apparaît.

Vous devez choisir où seront insérées les nouvelles données. Cliquez sur OK pour fermer la boîte de dialogue et compléter l’importation.

Dans la section « connexions » de l’onglet « données », cliquez sur « connexions » pour accéder aux options de la connexion. La connexion que vous avez créée se nomme simplement connexion.

Cliquez sur « Propriétés » et dans la case « nom de la connexion » remplacez le texte par défaut par « Répertoire municipal ». Remarquez les autres options disponibles. Activez l’option « Actualiser les

Page 36

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

données lors de l’ouverture du fichier ». Fermez le fichier et ouvrez-le de nouveau. Si le message d’avertissement de sécurité apparaît, vous devrez activer le contenu.

7.2 Excel et Access Dans le prochain cas, nous établirons un lien entre un fichier Excel et un fichier Access, plus précisément une requête Access.

Un fichier Access a été créé contenant les données des enquêtes des TGA publiés par CBRE et de Colliers (cas 7.2 - Access et Excel). Une requête nommée CBRE 2009 contient un sous-ensemble des données de CBRE pour l’année 2009. Pour ce faire, nous utiliserons un fichier vierge pour débuter. De l’onglet DONNÉES, cliquez sur DONNÉES EXTERNES et sur A PARTIR D’UN FICHIER ACCESS. Dans la fenêtre suivante, choisissez le fichier Access de votre choix (cas 7.2 - Access et Excel pour l’exemple).

Idéalement, le fichier Access est fermé. La prochaine boîte de dialogue permet de sélectionner la table de données ou la requête à importer, sélectionnez CBRE_2009 et OK. La prochaine boîte de dialogue détermine le mode d’affichage des données importées et la localisation dans le fichier. Les propriétés sont identiques à celles identifiées dans l’exercice précédent.

L’importation se terminera par un tableau de données en Excel. Vous pouvez convertir les données en format Excel standard en utilisant l’outil Convertir en plage dans la section outils de l’onglet Outils de tableau.

Par contre, cette opération éliminera le lien entre les fichiers. À ce point, vous pouvez aussi choisir de créer un tableau croisé dynamique ou un graphique et un tableau croisé dynamique sans passer par le tableau. Aux fins de l’exemple, nous importerons un tableau et créerons le tableau croisé dynamique par la suite.

Page 37

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

Pour créer un tableau croisé dynamique à partir des données importées, sélectionnez une cellule du tableau et cliquez sur « Synthétiser avec un tableau croisé dynamique » de la section Outils. La boîte de dialogue suivante permet de choisir les données à analyser et l’emplacement du tableau croisé dynamique. Le tableau « tableau CAP.accdb_1 » est la source par défaut. Nous remplacerons cette source par la connexion CAP cbre_2009 que nous avons établie à l’étape précédente. Conservons la nouvelle feuille de calcul pour l’emplacement du tableau croisé dynamique. Cliquez sur OK pour générer le tableau. Les étapes suivantes pour créer un tableau croisé dynamique sont les mêmes que celles identifiées dans le cas 3.

7.3 Excel et Word Étape 1 : Pour transférer des données entre Excel et Word, nous utiliserons la fonction de publipostage.

La fonction de publipostage utilisera un fichier Excel comme source de données qui alimentera la base de données de publipostage de Word.

Afin de simplifier l’exemple, le fichier de données Excel a été créé Cas 7.3 - Données de fusion. Il consiste en une fenêtre de saisie des données de base d’un rapport d’évaluation. (A1 à B9)

Adresse civique Rue Ville Type de propriété Superficie du bâtiment Superficie du terrain Forme du terrain Orientation

33 Chemin des Passereaux Lac-Beauport résidentiel 3 000 15 000 régulière ouest

Pour les fins de l’exemple, le nombre de données a été limité. Par contre, il n’existe pas de limite pratique du nombre de données pouvant être saisies dans la plage d’entrée.

Ces données sont aussi conservées dans le fichier Excel sous une forme linéaire (base de données).

Chaque colonne représente un champ, la première ligne sera le titre du champ et la seconde ligne la valeur du champ. Cette section servira à l’exportation vers Word. Pour faciliter le lien, nommez la plage A87 à H88, « data » en saisissant le terme « data » dans la boîte de nom. (Voir ci-dessous).

Page 38

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

Le fichier source Excel est prêt pour la fusion. Sauvegardez votre fichier afin que la version sur disque contienne la plage nommée.

Page 39

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

Étape 2 : Dans un fichier Word vierge, sélectionnez l’onglet « Publipostage ». Cliquez sur l’icône « Sélection des destinataires » et « utiliser une liste existante ». La boîte de dialogue suivante permet de choisir le fichier source, repérez et sélectionnez le fichier « données_fusion.xlsx » préparé à l’étape précédente. La prochaine boîte de dialogue permet de choisir la localisation des données à importer à l’intérieur du fichier Excel.

Remarquez le nom des trois feuilles de calcul (feuil1, feuil2 et feuil 3) ainsi que la plage nommée (data).

Sélectionnez « data ».

Assurez-vous que l’option «la première ligne de données contient les entêtes de colonnes » est activée. Cliquez sur OK pour fermer la boîte de dialogue. Cliquez sur « modifier la liste de destinataires » pour voir les données importées. Remarquez au bas de cette boîte de dialogue que vous pouvez actualiser les données si elles ont été modifiées dans le fichier source. Cliquez sur OK pour fermer la Boîte de dialogue.

Tapez le texte suivant : « Le présent rapport d’évaluation porte sur une propriété de type. ». Assurezvous d’insérer un espace vide à la suite du mot type. Cliquez sur la flèche vers le bas situé au côté du mot fusion sur l’icône « Insérer un champ de fusion » et sélectionnez le champ TYPE. Un champ est une boîte vide portant le titre du champ. Cette boîte contient chaque observation de ce champ contenu dans la base de données.

Cliquez sur « aperçu des résultats » pour voir comment la valeur de la base de données apparaît dans le champ. Cliquez à nouveau sur « aperçu des résultats » afin de retourner à l’affichage précédent.

Tapez le texte suivant : « situé au » en vous assurant d’insérer un espace vide avant et après le texte Insérez le champ de fusion Adresse_civique, un espace vide et le champ de fusion rue Tapez le texte suivant : « dans la municipalité de » en vous assurant d’insérer un espace vide avant et après le texte Insérez le champ de fusion Ville et tapez un point Tapez le texte suivant : « Le terrain d’une superficie de » en vous assurant d’insérer un espace vide avant et après le texte Insérez le champ de fusion terrain_taille Tapez le texte suivant : « pieds carrés est de forme » en vous assurant d’insérer un espace vide avant et après le texte

Page 40

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

Insérez le champ de fusion terrain Tapez le texte suivant : « et possède une orientation » en vous assurant d’insérer un espace vide avant et après le texte insérez le champ de fusion orientation et tapez un point.

Cliquez sur « aperçu des résultats » pour voir comment la valeur de la base de données apparaît dans le champ.

Nous ajouterons un commutateur aux champs Taille_terrain afin qu’il soit formaté correctement, soit avec un espace vide entre les milliers et les centaines. Aussi, nous formaterons les champs rue et ville avec une majuscule.

Page 41

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

Il existe trois types de commutateurs. \*

pour la mise en forme du texte

\#

pour le format des nombres

\@

pour la mise en forme des dates

L'utilisation est assez simple, il suffit d'ajouter au champ le commutateur souhaité.

ALT + F9 pour afficher les codes de champs (commutateurs) Pour le texte Les commutateurs de mise en forme de texte sont au nombre de quatre.

Commutateur

Résultat

\* Caps

Affichera toutes les premières lettres en majuscule

\* FirstCap

Affichera la première lettre en majuscule

\* Upper

Affichera toutes les lettres en majuscule

\* Lower

Affichera toutes les lettres en minuscule

Pour les nombres Si l'on souhaite obtenir l'affichage de deux décimales pour un nombre, il suffit d'ajouter un masque au champ. \# "#.###,00". donnera comme affichage pour 1234,4 : 1.234,40 Si vous avez plus de deux décimales à votre nombre, ce commutateur agira comme un arrondi.

Vous obtiendrez pour 1234,567 : 1.234,57 Pour les dates Il est également possible de mettre une date en forme. \@ "dddd, dd MMMM yyyy" donnera comme résultat jeudi, 2 février 2010.

Commutateur

Résultat

d, dd, dddd

Jour avec un chiffre, deux chiffres, en toutes lettres

M, MM, MMMM

Mois avec un chiffre, deux chiffres, en toutes lettres

yy, yyyy

L'année en deux chiffres, en quatre chiffres

hh, HH

L'heure de 0 à 12 heures, de 0 à 24 heures

Mm

Les minutes

Il est également possible de permuter les différents éléments : "yyyy MM/dd" affichera 2010 02/04.

Page 42

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 7

Nous ajouterons donc le commutateur \# "# ###" au champ Taille_terrain, et le

commutateur \* Caps aux champs rue et ville.

Appuyez sur ALT et-F9 pour retourner à l’affichage normal des champs et « aperçu des résultats » pour afficher les valeurs. L’image suivante représente l’affichage final du texte produit.

Page 43

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 8

Cas 8 – Création d’un modèle Dans cet exercice nous introduirons des notions de contrôle des saisies et de protection de documents.

Commencez l’exercice avec le fichier créez dans le cas 7.1.2 ISQ répertoire municipal (ou le fichier Cas 7.1.2 - lien web ISQ – complété fourni) Étape 1 : Pour commencer, tapez les titres suivants dans les cellules G1, G4, G5, G6 et G7 de la feuille de calcul contenant la liste des municipalités.

Formulaire

Champ numérique Champ texte Champ date Liste déroulante

Étape 2: Avec la cellule H4 sélectionnée, cliquez sur l’onglet « Données », et « Validation de Données ». La boîte de dialogue suivante nous permet de limiter les données saisies de la cellule sélectionnée, de saisir un message à l’éditeur et un message en cas d’erreur.

Assure-vous que l’onglet « Options » soit sélectionné. Dans la boîte « Autoriser », sélectionnez « Nombre entier ». Cette sélection ne permettra aucune saisie autre qu’un nombre entier dans la cellule. Dans la boîte « données » sélectionnez « comprise entre » et dans les boîtes suivantes saisissez les valeurs minimum et maximum autorisées (0 – 1 000 000). Notez que ces deux boîtes pourraient aussi contenir des références.

Sélectionnez maintenant sur l’onglet « Message de saisie » de la boîte de dialogue et compléter avec les textes de l’illustration suivante. Activé l’option

Page 44

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 8

« Quand la cellule est sélectionnée » pour que le message que vous avez tapé dans « Message de saisie » apparaisse aussitôt que l’utilisateur sélectionne cette cellule.

Sélectionnez l’onglet « Alerte d’erreur » de la Boîte de dialogue et tapez les textes suivants. Activez l’option « Quand des données non valides sont tapées» pour que le message que vous avez tapé dans « Message d’erreur » apparaisse aussitôt que l’utilisateur tente de saisir une donnée non autorisée.

Cliquez sur OK pour fermer la Boîte de dialogue.

Sélectionnez la cellule H5 et répétez la validation de données. Complétez les Boîtes de dialogue avec les informations contenues dans les illustrations suivantes.

Page 45

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 8

Sélectionnez la cellule H6 et répétez la validation de données. Complétez les Boîtes de dialogue avec les informations contenues dans les illustrations suivantes.

Page 46

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 8

Sélectionnez la cellule H7 et répétez la validation de données. Complétez les Boîtes de dialogue avec les informations contenues dans les illustrations suivantes. La plage source étant la plage contenant les noms de municipalités. Remarquez que la sélection (jusqu’à la ligne 1350) excède la plage des municipalités existantes. Puisque l’option « ignoré si vide » est cochée, les cellules vides n’apparaissent pas dans la liste déroulante. Par contre, si la liste était allongée lors d’une mise à jour, les derniers noms seraient accessibles sans modifier la programmation.

Élargissez les colonnes G et H aux besoins. Étape 3 : Sélectionnez les cellules G1 à J12. Sélectionnez le bouton « Format » de la section « Cellules » dans l’onglet « Accueil » et assurez-vous que l’option « Verrouiller la cellule » est active (l’icône du cadenas sera affiché en surbrillance et encadré d’un trait de couleur). Si elle n’est pas active, activez cette option. Sélectionnez les cellules H4 à H6 et désactivez « verrouiller la cellule » (l’icône du cadenas n’est pas encadré). En déverrouillant seulement ces cellules, vous limiterez l’accès aux utilisateurs à ces seules cellules lorsque vous protégerez la feuille dans une étape subséquente.

Page 47

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 8

Sélectionnez les colonnes K jusqu’à XFD (la dernière colonne à droite).

Astuce : Pour facilement sélectionner ces colonnes, commencez par cliquer sur l’en-tête de la colonne E et puis sur CTRL+SHIFT+ flèche droite.

Sélectionnez le bouton « Format » de la section « Cellules » dans l’onglet « Accueil ». Cliquez sur « masquer et afficher » et « masquer les colonnes » Répétez l’opération sur les colonnes A à F. Sélectionnez les lignes 12 jusqu’à 1 048 576 (la dernière ligne du bas).

Astuce : Pour facilement sélectionner ces lignes, commencer par cliquez sur l’en-tête de la ligne 12 et puis sur CTRL+SHIFT+ flèche bas.

Sélectionnez le bouton « Format » de la section « Cellules » dans l’onglet « Accueil ». Cliquez sur « masquer et afficher » et « masquer les lignes ». La feuille de calcul visible se limite maintenant aux cellules G1 à J11. Étape 4 : Sélectionnez l’onglet « révision : et cliquez sur “protéger la feuille”, pour afficher la Boîte de dialogue suivante.

Assurez-vous que l’option “protéger la feuille et le contenu des cellules verrouillées” est cochée.

Aussi, cochez seulement l’option “sélectionner les cellules déverrouillées” est active. Et cliquez OK pour fermer la Boîte de dialogue. (Notez que vous pouvez aussi inscrire un mot de passe afin d’enlever la protection. La feuille est maintenant protégée, seules les cellules déverrouillées dans l’étape précédente sont accessibles. Si nous avions introduit des calculs dans les autres cellules de la feuille, ils seraient toujours actifs, mais inaccessibles pour l’utilisateur.

Avant de retirer la protection, tentez de sélectionner une cellule verrouillée et saisissez des données dans les cellules déverrouillées.

Pour ôter la protection, cliquez sur “ôter la protection” de l’onglet “révision”.

Attention : La protection de la feuille empêchera l’actualisation du répertoire des municipalités, vous devrez ôter la protection pour effectuer une mise à jour.

Page 48

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 8

Étape 5 : Si vous désirez que le fichier soit sécurisé et que personne ne puisse sauvegarder des données dans le fichier modèle original, sauvegardez le modèle en format “Modèle Excel ”. Pour ce faire, cliquez sur “Enregistrer sous” et sélectionnez “autres format”. La boîte de dialogue suivante vous invitera à nommer le fichier et à choisir un type. Dans la liste déroulante du type, choisissez “Modèle Excel”. Le fichier sera automatiquement dirigé vers le répertoire “templates” de Microsoft. Cette option permet d’ouvrir le fichier modèle sans toutefois être capable de sauvegarder sous le même nom, un nouveau nom est assigné au fichier pour éviter de corrompre le fichier d’origine. Aussi, la localisation dans le répertoire “templates” vous permet de choisir ce modèle lors de la création d’un nouveau fichier. Au moment de créer un nouveau fichier basé sur le modèle, sélectionner nouveau à partir du de l’onglet Fichier et sélectionnez “mes modèles”. Les fichiers modèles sauvegardés dans le répertoire “templates” apparaîtront dans une Boîte de dialogue. Dès la création, le nouveau fichier se voit assigner un nouveau nom sauvegardant l’original du modèle.

Page 49

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 9

Cas 9 – Macros Dans cet exercice nous introduirons quelques notions d’introduction au macros Pour créer une macro, l’onglet “développeur” doit être activé. Procédez comme suit pour activer l’onglet développeur.

Sélectionnez l’onglet Fichier et Options. Dans la boîte de dialogue suivante, sélectionnez Personnalisez le ruban. Dans la colonne de droite (Onglet principaux), cochez Développeur. Étape 1 : Dans l’onglet “développeur”, cliquez sur “enregistrer une macro”. La boîte de dialogue suivante permet de nommer la macro (tapez modele1 dans cette boîte) et de lui assigner un raccourci clavier.

Assurez-vous que la macro est enregistrée dans ce classeur. Cliquez OK pour commencer l’enregistrement de la macro.

Attention : À partir de ce point, la macro enregistre chacune des commandes que vous exécutez et les code afin de les reproduire lors de l’exécution de la macro.

Sélectionnez la cellule A1 et tapez : “Revenu de location“, Sélectionnez la cellule A3 et tapez : “Récupération des frais d'exploitation“, Sélectionnez la cellule A5 et tapez “Inoccupation“, Sélectionnez la cellule A7 et tapez “Revenu net d’exploitation“`.

Dans la cellule D5, tapez l’équation =(D1+D3)*C5 Dans la cellule D7, tapez l’équation =D1+D3-D5 Cliquez sur “arrêter l’enregistrement” Étape 2 : Assurez-vous du code VBA saisi. Cliquez sur visual basic pour activer l’éditeur VBA et sélectionnez le module 1 (double cliquez pour activer). Le code suivant devrait s’afficher :

Page 50

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 9

Note : Assurez vous que la première ligne est identique, cette dernière choisi la cellule A1 comme point de départ. Si est elle n’est pas identique, simplement tapez le code tel qu’il apparaît à l’image. Étape 3 : Fermer l’éditeur VBA et supprimer ce que vous avez tapé sur la feuille Excel. Pour utiliser la macro, cliquez sur Macro et sélectionnez Modele1 et RUN (ou double cliquez sur modele1). La macro complétera le contenu de la page excel. Étape 4 : Cliquez sur “enregistrer une macro”. La boîte de dialogue suivante permet de nommer la macro (tapez IMPRIM dans cette boîte) et de lui assigner un raccourci clavier. Assurez-vous que la macro est enregistrée dans ce classeur. Cliquez OK pour commencer l’enregistrement de la macro.

Sélectionnez l’onglet Fichier et complétez les étapes pour imprimer. Après avoir imprimé, retournez à l’onglet “Développeur” et cliquez sur “arrêter l’enregistrement”.

Le code VBA (visual basic) que vous avez généré par l’enregistrement de la macro est le suivant:

Page 51

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 9

Étape 5 : De l’onglet “développeur” cliquez sur “insérer” et sélectionnez dans les Contrôles de formulaire le bouton. Tracez le bouton sur la feuille de calcul en cliquant à un endroit quelconque et en dessinant la taille requise, relâchez le bouton de la souris lorsque vous avez terminé. Le bouton sera alors tracé. Une boîte de dialogue apparaîtra vous demandant d’assigner une macro à ce bouton, sélectionnez la macro IMPRIM que vous avez créée à l’étape précédente. Sélectionnez le nom par défaut apparaissant sur le bouton et remplacez-le par le texte “IMPRIMER”. Si la taille du bouton ne permet pas ce texte, sélectionnez le bouton avec le bouton de droite de la souris afin de faire apparaître les points d’étirement et redimensionnez le bouton. Enfoncez la touche ESC pour terminer. En cliquant le bouton, vous activerez la macro et imprimerez la page. Étape 6 : Pour sauvegarder et conserver les fonctionnalités de la macro, vous devrez sauvegarder sous le format “Classeur Excel (prenant en charge macro)”.

Voici un lien intéressant pour en apprendre plus sur les macros. http://www.excel-pratique.com/fr/vba.php

Page 52

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 10

Cas 10 – Applications Web Cas 10.1 – Google documents Si vous avez déjà un compte Google (Gmail) en vigueur, connectez-vous. Si vous n’avez toujours pas de compte Google, il faut compléter les étapes suivantes afin d’ouvrir un compte gratuit. Vous aurez besoin d’une adresse de courriel valide à laquelle vous avez accès.

Naviguez au site Google (www.google.ca) et cliquez sur le bouton Connexion situé en haut à droite de l’écran. Ensuite, sélectionnez “Créez un compte gratuitement” afin de démarrer la création d’un compte gratuit. Suivez les instructions à l’écran. Une fois votre compte activé et que vous vous soyez connecté, vous remarquerez que votre identifiant de compte apparaît en haut de l’écran à droite vous confirmant votre connexion. Sélectionnez la flèche au côté du mot PLUS pour avoir accès aux autres services Google et sélectionnez “Documents”.

La fenêtre suivante apparaîtra.

Cliquez sur “importer” et sélectionnez un fichier de votre choix à télécharger vers Google document.

Confirmez l’importation dans la prochaine Boîte de dialogue.

Une fois le téléchargement complété, double cliquez sur le nom du fichier afin de l’ouvrir à partir de l’application en ligne “Feuille de calcul”. Pour quitter le document, fermer la fenêtre active.

Page 53

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

Cas 10

Pour créer un document, cliquez sur “Créer” pour ouvrir.la Boîte de dialogue vous demandant de confirmer le type de document à créer. Sélectionnez “formulaire”. Cette application vous permet de créer un questionnaire en ligne, de le distribuer et de colliger et d’analyser les réponses reçues.

Cliquez sur “Formulaire sans titre” et remplacez par un titre de votre choix. Dans la boîte intitulé “question”, remplacez “exemple de question 1” par votre première question. Sélectionnez le type de question (texte, texte de paragraphe, choix de réponses, etc.). Une fois la question 1 terminée, cliquez sur OK. Si vous désirez faire une correction à la question 1, cliquez sur l’icône de crayon (édition) pour activer le mode édition dans la question 1.

Remarquez le bouton “Consulter les réponses” en haut de la page. Ce bouton vous ouvrira la feuille de calcul associée au formulaire dans laquelle les réponses sont colligées où la seconde option permet de consulter le résumé des réponses.

Cas 10.2 – Applications Web Office Si vous avez déjà un compte Windows.live (hotmail) en vigueur, connectez-vous. Si vous n’avez toujours pas de compte Windows.live, il faut compléter les étapes suivantes afin d’ouvrir un compte gratuit. Vous aurez besoin d’une adresse de courriel valide à laquelle vous avez accès.

Naviguez au site Windows live.com (http://explore.live.com/), sélectionnez “commencer” dans le menu Accueil et inscrivez-vous maintenant. Une fois le compte créé, connectez-vous à votre compte et choisissez le bouton Skydrive. Vous devrez vous identifier à ce service avec les identifiants Windows.live que vous avez créés dans l’étape précédente afin d’accéder au compte Skydrive. Ce service vous offre 25G d’espace disque via Internet qui sera accessible de n’importe quel ordinateur ayant une connexion Internet. De plus, ce service de Microsoft a été intégré à la version Office 2010 et vous permet de sauvegarder les documents des applications Office sur votre ordinateur directement sur l’espace Skydrive. Remarquez dans la fenêtre principale de Skydrive les icônes pour MS Excel, MS Word, MS Powerpoint à côté du bouton Create et le bouton Add Files. Vous pouvez donc télécharger des fichiers existants créés par des applications Microsoft ou créer des fichiers Office en ligne. Cliquez sur l’icône Excel. Nommez le fichier Fichier_test et cliquez sur Save.

Vous avez maintenant accès à une application Excel en ligne (ExcelWebApp). Vous remarquerez que le graphisme de l’application est semblable à Excel 2010. Par contre, les commandes sont restreintes.

Créez quelques calculs pour compléter un test. Cliquez sur “Open in Excel” pour télécharger le fichier sur votre poste de travail et compléter l’édition sur votre application Office. Apportez de nouveau des modifications et fermez le fichier. Retournez à votre fenêtre de fureteur. Vous serez de retour à la fenêtre Skydrive et votre fichier apparaît dans la liste des documents. Double cliquez sur le nom du fichier. Un aperçu du fichier apparaît dans la nouvelle fenêtre avec les commandes “Open in Excel” pour ouvrir le fichier sur votre poste ou “edit in browser” pour ouvrir le fichier à partir de l’application web.

Page 54

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.

Microsoft Excel Avancé 2010 pour l’immobilier et l’évaluation

DROITS D’AUTEUR Les droits d’auteur relatifs au présent document appartiennent à l’Ordre des évaluateurs agréés du Québec. Toute utilisation, reproduction, diffusion, publication ou retransmission du présent document, sous quelque forme que ce soit, en tout ou en partie, est strictement interdite sans l’autorisation écrite et préalable de l’Ordre des évaluateurs agréés du Québec.

Page 55

Activité de formation continue organisée par l'Ordre des évaluateurs agréés du Québec.