Uproszczenie modelu Bennu dzięki RandArray - porady dotyczące programu Excel

Spisie treści

Obejrzyj wideo

W zeszłym tygodniu podczas Ignite zespół Excela przedstawił tablice dynamiczne. Dzisiaj przyjrzyjmy się bliżej funkcji RANDARRAY.

Niedawno, w moim wpisie do gry Excel Hash, stworzyłem model do obliczania prawdopodobieństwa, że Ziemia będzie miała nową atrakcję turystyczną, krater Bennu do 2196 roku. Model ten wykonał trzydzieści milionów obliczeń i wymagał 200 001 formuł wraz z 100- tabela danych wiersza. Oto formuły użyte w 200001 komórkach:

Ten model obliczył w 10-12 sekund

Aby uprościć model, należy użyć funkcji RANDARRAY (100000) zamiast funkcji RAND. Spowoduje to, że formuła obliczy 100 000 razy.

Zaczynasz od zamiany RAND () na RANDARRAY (100000), aby wygenerować 100 000 odpowiedzi:

RANDARRAY(100000)
Wyślij RANDARRAY do NORM.INV, aby obliczyć 100 000 lokalizacji

NORM.INV(RANDARRAY(100000),$H$4,$H$5)
Wyślij NORM.INV do funkcji WYSZUKAJ.PIONOWO, aby określić, czy Bennu wpływa na Ziemię:

VLOOKUP(NORM.INV(RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE)
I na koniec zsumuj wyniki 100K

=SUM(VLOOKUP(NORM.INV(_xlfn.RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE))

Ostateczny model do uruchomienia 100 000 prób jest zawarty w jednej formule:

200 000 komórek zastąpionych 1 formułą

Rozmiar pliku dramatycznie się zmniejsza: z 3270979 bajtów do 37723 bajtów. Czas przeliczenia skraca się o połowę. Obejrzyj czasy przeliczania na poniższym filmie.