La semaine dernière chez Ignite, l'équipe d'Excel a présenté les tableaux dynamiques. Aujourd'hui, regardons de plus près la fonction RANDARRAY.
Récemment, dans mon entrée dans le jeu Excel Hash, j'ai créé un modèle pour calculer la chance que la Terre ait une nouvelle attraction touristique, le cratère Bennu d'ici 2196. Ce modèle a effectué trente millions de calculs et a nécessité 200 001 formules avec un 100- table de données de ligne. Voici les formules utilisées dans 200 001 cellules:
Pour simplifier le modèle, vous utiliseriez RANDARRAY (100000) au lieu de la fonction RAND. Cela entraînera le calcul de la formule 100 000 fois.
-
Vous commencez par remplacer RAND () par RANDARRAY (100000) pour générer 100000 réponses:
RANDARRAY(100000) -
Envoyez le RANDARRAY à NORM.INV pour calculer 100000 emplacements
NORM.INV(RANDARRAY(100000),$H$4,$H$5) -
Envoyez le NORM.INV dans RECHERCHEV pour déterminer si Bennu a un impact sur la Terre:
VLOOKUP(NORM.INV(RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE) -
Et enfin additionner les 100K résultats
=SUM(VLOOKUP(NORM.INV(_xlfn.RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE))
Le modèle final pour exécuter 100 000 essais est contenu dans une seule formule:
La taille du fichier diminue considérablement: de 3 270 979 octets à 37 723 octets. Le temps de recalcul est réduit de moitié. Regardez les temps de recalcul dans la vidéo ci-dessous.
Regarder la vidéo
Télécharger le fichier Excel
Pour télécharger le fichier excel: rationaliser-le-modèle-bennu-avec-randarray.xlsm
À partir de maintenant jusqu'à la fin de 2018, je crée gratuitement mon nouveau livre électronique Excel Dynamic Arrays Straight To The Point.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Commencez toujours le nom de votre table par" tbl ""
Dietmar Gieringer
