Risolto Le formule matriciali sono meno veloci di quelle non matriciali?

paoloard

Utente assiduo
Expert
18 Febbraio 2018
1.275
83
prov. BO
2016
146
Mi è capitato più volte di leggere affermazioni riguardo al fatto che le formule matriciali sono più pesanti, in termini di velocità di calcolo, rispetto a quelle non matriciali.
Diversi anni fa, con l'aiuto di un amico di altro forum, effettuammo delle prove a seguito delle quali verificammo che non era affatto così in quanto i tempi di esecuzione non differivano sostanzialmente.
Poiché non trovo più quel file e poiché causa della mia ignoranza in fatto di VBA non sono in grado di scrivere uno script che calcoli il tempo esatto di esecuzione, avrei preparato un file in cui nel Foglio1 vi sono dati elaborati con formule matriciali e nel Foglio2 gli stessi dati elaborati con formule normali. Le formule non sono altro che un'operazione di somma con una condizione.
Qualcuno potrebbe cortesemente scrivere un programmino che calcoli il tempo di esecuzione del ricalcolo a seguito della variazione di un dato nel Foglio1 e altrettanto nel Foglio2, così da verificare se effettivamente i tempi divergono oppure no?
Grazie. Saluti.
 
  • Like
Reactions: Redbeard

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
21.138
1.733
Como
2011MAC 2016WIN
449
un programmino che calcoli il tempo di esecuzione del ricalcolo a seguito della variazione di un dato nel Foglio1 e altrettanto nel Foglio2, così da verificare se effettivamente i tempi divergono oppure no?
Ciao paoloard @paoloard ,
prova intanto con questo con questo codice:
Visual Basic:
Sub ContaTempo()
    Dim StartTime As Double
    Foglio1.Activate
    StartTime = Timer
    Application.CalculateFull
    MsgBox "Tempo impiegato nel Foglio1: " & Round(Timer - StartTime, 3) & " secondi!"
    Foglio2.Activate
    StartTime = Timer
    Application.CalculateFull
    MsgBox "Tempo impiegato nel Foglio2: " & Round(Timer - StartTime, 3) & " secondi!"
End Sub
Se però i tempi sono brevissimi serve il MicroTimer
 

Bruno

Utente abituale
Expert
13 Settembre 2015
898
43
Italy
365/64 Bit W10
86
Ciao
Ecco le macro col MicroTimer Caffe_rido

Da inserire in un modulo:

Visual Basic:
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
         "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
         "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If

Function MicroTimer() As Double

' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    MicroTimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1

' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function

Sub RangeTimer()
    CalcTimer 1
End Sub
Sub SheetTimer()
    CalcTimer 2
End Sub
Sub RecalcTimer()
    CalcTimer 3
End Sub
Sub FullcalcTimer()
    CalcTimer 4
End Sub

Sub CalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = MicroTimer

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End If
       
        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calculate duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Iteration = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub
 

paoloard

Utente assiduo
Expert
18 Febbraio 2018
1.275
83
prov. BO
2016
146
Ciao a tutti. Innanzitutto grazie per le risposte.
Prima di esporre i risultati informo circa il mio sistema:
Processore Intel I5-8400 - 2,8 GH - 8 GB RAM - W10 Home 64 bit

ges @ges
facendo girare il tuo script ottengo questi risultati:
Foglio1 (ricalcolo foglio con macro): 12,539" - 12,691" - 15,473" - 12,469"
Foglio2 (ricalcolo foglio non macro): 12,562" - 12,551" - 12,559" - 12,557"
in conclusione, a parte il picco di 15,473" nel Foglio1, sembra che i tempi siano simili. Quindi nei tempi di esecuzione non vi sarebbe differenza fra formule macro e non macro.

Bruno @Bruno
diversi i risultati con il tuo codice dovuto a un diverso sistema di calcolo (che però non ho capito).
Tralasciando i tempi di apertura del file e del calcolo di singole celle mi sono soffermato sul RangeTimer selezionando le 748 celle (comprese quelle vuote intermedie) contenenti le formule, ottenendo però dei risultati diversi rispetto a quelli suggeriti da ges @ges :
Foglio1 (ricalcolo range con formule macro): 26,95" - 26,86" - 27,01" - 27,12"
Foglio2 (ricalcolo range con formule no macro): 20,61" - 20,73" - 20,56" - 20,80"
questo test, a differenza di quello precedente, dimostrerebbe invece che effettivamente le formule con macro sono più lente rispetto a quelle no macro.

Perché questa differenza fra i due test?
Secondo voi si può trarre una conclusione certa?

Edit: correggi "macro" con "matriciali".
 
Ultima modifica:

Bruno

Utente abituale
Expert
13 Settembre 2015
898
43
Italy
365/64 Bit W10
86
Ciao

Risultati medi ottenuti con Excel 365 Insider 64 Bit
Ho selezionato solo 24 celle ed ho rilevato questi tempi:

{=SOMMA(SE($A:$A=$A2;$B:$B))}
secondi 3,52764

=SOMMA(SE($A:$A=$A2;$B:$B))
secondi 3,49978 Questa formula opera solo con Excel 365

=MATR.SOMMA.PRODOTTO(--($A:$A=$A2);$B:$B)
secondi 2,17352

=SOMMA.SE(A:A;A2;B:B)
secondi 0,00664

Mentre con

Application.CalculateFull su di un singolo foglio
Ottengo un valore simile a quello di ges @ges
con secondi 4,61665
 

paoloard

Utente assiduo
Expert
18 Febbraio 2018
1.275
83
prov. BO
2016
146
Ciao Bruno. Scusa, avevo ti avevo risposto allegando un file più semplificato ma poi non avevo cliccato su "Pubblica.."
Secondo quanto riporti non è ben chiaro se le le formule matriciali impieghino più tempo rispetto a quelle non matriciali.
Infatti nelle prime due SOMMA(SE....) i tempi sono simili mentre MATR.SOMMA.PRODOTTO si prenderebbe un bel vantaggio, ma è formula ben diversa da SOMMA.
Mah! Tu cosa dici? Non mi pare che abbiamo elementi sufficienti per suffragare un'ipotesi o l'altra.
 

Bruno

Utente abituale
Expert
13 Settembre 2015
898
43
Italy
365/64 Bit W10
86
Ciao paoloard

Non mi pare che abbiamo elementi sufficienti per suffragare un'ipotesi o l'altra
Sul mio pc ho questi risultati, i valori sono simili.
Se hai una reale necessità di una formula che tu rilevi lenta puoi sempre provare a cercare una soluzione Vba.
Nel caso pubblica la richiesta, in vba si può modificare l'algoritmo.

Link
https://professor-excel.com/performance-excel-study/
 

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
21.138
1.733
Como
2011MAC 2016WIN
449
Non mi pare che abbiamo elementi sufficienti per suffragare un'ipotesi o l'altra
Qui forse no, però una formula matriciale compie più operazioni rispetto a una non matriciale quindi, per logica, una formula matriciale dovrebbe essere più lenta.
 

paoloard

Utente assiduo
Expert
18 Febbraio 2018
1.275
83
prov. BO
2016
146

paoloard

Utente assiduo
Expert
18 Febbraio 2018
1.275
83
prov. BO
2016
146
.... però una formula matriciale compie più operazioni rispetto a una non matriciale quindi, per logica, una formula matriciale dovrebbe essere più lenta.
A intuito forse hai ragione, però non mi pare che i risultati del tuo test lo dimostrino.
In ogni modo qui https://stackoverflow.com/questions/37148716/why-are-excel-array-formulas-slow si trova una convincente spiegazione.
Ciao. Grazie a tutti.

PS: ho assegnato la risposta a ges in quanto ha risposto per primo non perché quella di Bruno fosse meno significativa. D'altronde l'attribuzione è univoca.
 
Ultima modifica:

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!