Excel : la rapidité de calcul dans des classeurs volumineux

Parmi les différentes fonctions d’Excel, il y a aussi la possibilité de gagner du temps. Ces cinq conseils vous montrent comment formater, visualiser et calculer rapidement et correctement vos tableaux.

Astuces Excel / Tutoriel Excel / Formation Excel / cours gratuit Excel

Formater rapidement un tableau

Les tableaux Excel sont souvent anonymes et triviaux – une impression que vous pouvez réfuter avec un formatage soigné. Pour formater rapidement un tableau existant selon un style prédéfini, mettez en surbrillance la zone du tableau et sélectionnez “Insérer> Tableau” dans le ruban. Cette fonction n’insère pas de nouvelles valeurs, mais formate une table existante.

Dans la boîte de dialogue, spécifiez si le tableau doit avoir des en-têtes ou non. Cliquez ensuite sur “OK” pour obtenir un joli tableau avec fonction d’orientation pour les différentes rubriques.

Remplissez les cellules avec des valeurs aléatoires
Après avoir créé un tableau, il est parfois nécessaire de le remplir avec des valeurs aléatoires pour tester les formules. Dans ce cas, la fonction “RANDOM.TRA” est utile, qui génère une valeur aléatoire à partir d’une plage prédéterminée :

La formule “= RANDOM.TRA (1 ; 1000)” renvoie par ex. une valeur comprise entre 1 et 1000.

Avec la fonction “random.tra”, vous pouvez rapidement remplir un tableau avec des valeurs aléatoires.

Entrez la formule dans la cellule supérieure gauche de la zone souhaitée. Faites ensuite glisser le petit carré situé dans le coin inférieur droit de la cellule avec la souris et élargissez la sélection pour inclure toutes les cellules qui doivent être remplies avec des valeurs causales.

Calculer des périodes de temps

Pour des raisons de compatibilité, Excel propose certaines formules qui dérivent d’un ancien programme qui n’existe plus : Lotus-1-2-3. Par conséquent, ceux-ci n’apparaissent même pas dans la liste des fonctions. La fonction “DATA.DIFF” est très pratique pour calculer la différence entre deux dates en années, mois ou jours.

La formule «= DATA.DIFF (“ 1/1/2020 ″; TODAY (); ”d”) » calcule par ex. combien de jours se sont écoulés depuis le début de cette année. La date de début et de fin peut bien sûr également se trouver dans une cellule. L’unité de temps du troisième

Diagrammes de foudre avec sparklines

Pour visualiser rapidement l’évolution des valeurs et donc faciliter la lecture, vous pouvez utiliser les “graphiques sparkline”. Ce sont des “schémas en cellule” que vous pouvez accrocher sur votre table.

Sélectionnez une colonne adjacente au tableau et dans le ruban “Insérer” l’un des graphiques sparkline proposés. Dans la boîte de dialogue suivante, sélectionnez la zone du tableau dont les cellules ou les colonnes doivent être analysées graphiquement. Cliquez ensuite sur « OK » pour insérer ces mini-diagrammes.

Pour formater les sparklines, sélectionnez une cellule contenant les sparklines que vous souhaitez modifier et ouvrez le ruban “Conception”.

doit être indiquée en anglais : “y” pour l’année, “m” pour le mois et “d” pour le jour.

Problème de condition imbriquée résolu (enfin !)

Microsoft a enfin résolu le problème des conditions imbriquées de la fonction SE. Imaginez la situation suivante : Dans une colonne il y a les chiffres de 1 à 7 qui correspondent aux jours de la semaine (1 = lundi, 2 = mardi, etc.). Si vous souhaitez afficher les jours de la semaine au format texte dans la colonne de droite, vous deviez jusqu’à présent imbriquer de nombreuses conditions SE. Maintenant, Excel a introduit la fonction “SWITCH”, dérivée de nombreux langages de programmation, également appelée ainsi dans la version italienne. Pour convertir un chiffre en un jour de la semaine, vous pouvez utiliser la syntaxe suivante (qui pour des raisons de lisibilité est limitée aux seuls trois premiers jours de la semaine) :

= SWITCH (B13 ; 1 ; “lundi” ; 2 ; “mardi” ; 3 ; “mercredi” ; “pas de correspondance”)

Cette syntaxe renvoie “lundi” si la valeur “1” est présente dans la cellule B13, “mardi” si la valeur “2” est présente et “mercredi” si la valeur “3” est présente. S’il existe d’autres valeurs, “Aucune correspondance” s’affiche.

Montag = Lundi Dienstag=mardi….

Calcul dans Excel : Avis des utilisateurs

Les calculs dans Excel sont vraiment lents pour plus d’un million de lignes. Comment puis-je accélérer les choses pour que les utilisateurs n’aient pas à attendre 10 minutes à chaque fois ?
En tant qu’amateur d’Excel, je travaille fréquemment avec des classeurs volumineux et je m’attends à des performances rapides. Si vous ne l’obtenez pas, les raisons sont probablement auto-infligées et tout à fait réparables. Deux causes très courantes de temps de recalcul lents sont les grandes tables de recherche et un million de formules volatiles.

Si vous effectuez une RECHERCHEV sur une table de recherche d’un million de lignes, l’ancienne méthode consistait à rechercher une correspondance dans chaque ligne. En moyenne, cela peut prendre 500 000 tests, c’est-à-dire assez lent. La meilleure solution au problème consiste à effectuer une mise à niveau vers Microsoft 365, qui indexe la colonne de recherche afin que votre formule puisse accéder directement à la bonne ligne. Cela vous donne des performances de recherche plus de mille fois plus rapides dans les tests de référence.

Si vous êtes bloqué avec une ancienne version d’Excel, une alternative consiste à trier votre table de recherche par la colonne de recherche. Cette étape unique ne prend que quelques secondes, puis vous permet d’utiliser la méthode de recherche binaire pour trouver la bonne ligne dans 20 tests. Mais comme la méthode de recherche binaire dans VLOOKUP ou MATCH renverra avec plaisir une non-correspondance stockée à l’emplacement attendu, vous devez vérifier cette possibilité. Pour renvoyer une valeur de la colonne B où la colonne A correspond à la valeur de Z2, vous utiliseriez :

=IF(VLOOKUP(Z2,A:A,1)<>Z2,"",VLOOKUP(Z2,A:G,2)) 

Les formules volatiles

Les formules volatiles sont recalculées chaque fois que l’utilisateur ou une macro modifie une valeur dans un classeur ouvert. Si vous avez quelques dizaines de formules de ce type, vous ne remarquerez pas le ralentissement. Mais si vous en avez des milliers, vous obtiendrez un retard ennuyeux chaque fois que vous appuyez sur Entrée.

Alors, qu’est-ce qui rend les formules volatiles ? S’ils utilisent certaines fonctions (OFFSET, INDIRECT, RAND, TODAY, NOW, CELL étant les principaux coupables), la formule les contenant est volatile. Les formats conditionnels sont également volatiles. Le remède aux performances médiocres est d’arrêter d’utiliser des formules volatiles. Utilisez INDEX au lieu de OFFSET. Utilisez une macro au lieu de INDIRECT ou RAND. Utilisez des formats statiques au lieu de formats conditionnels et mettez à jour ces formats statiques avec une macro.

Si vous vous débarrassez des formules volatiles et utilisez Microsoft 365, il n’y a aucune raison pour que votre classeur d’un million de lignes ne puisse pas mâcher de la gomme pendant qu’il danse sur toutes vos données.