Estrai e somma in formato ore e minuti

Question

Ciao a tutti,

Chiedo il vostro aiuto per capire se esiste la possibilità di avere una formula che estrae da una stringa solo i numeri e li somma formando delle ore. Cerco di spiegare meglio quello che mi servirebbe facendo un esempio. Dalla cella B2:M2 deve sommare nella cella N2 i valori numerici (nota che nelle celle da B2:M2 vengono riportate delle formule che fanno riferimento ad un altro foglio. Possono anche restare senza nessun valore e quando il valore viene riportato è sempre lo stesso iniziando ad esempio con min. 15 o min. 10 o altro). A questo punto deve sommare i minuti, nella somma invece di visualizzare il totale dei minuti ad esempio  90 uscirà 1,30 (sarebbe 1 ora e 30 minuti). Spero di essere stato chiaro nella mia esposizione. Grazie

Answer 1

Ciao Geacs,

Prova la seguente UDF (funzione utente):

• Alt+F11 per aprire l'editor di VBA
• Alt+IM per inserire un nuovo modulo di codice
• Nel nuovo modulo vuoto, incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Public Function SommaMinuti(Rng As Range)

    Dim rCell As Range

    Dim arrTemp As Variant

    Dim aStr As String, sStr As String

    Dim i As Long

    Dim mySum As Long

    aStr = Chr(160) & Chr(111)

    For Each rCell In Rng.Cells

        sStr = rCell.Value

        sStr = Replace(sStr, "min. ", vbNullString)

        arrTemp = Split(sStr, aStr)

        For i = LBound(arrTemp) To UBound(arrTemp)

            If IsNumeric(arrTemp(i)) Then

                mySum = mySum + arrTemp(i)

            End If

        Next i

    Next rCell

    SommaMinuti = mySum / 24 / 60

End Function

'<<========= 

• Alt+Q per chiudere l'editor di VBA e tornare a Excel.

La funzione viene utilizzata come una funzione nativa di excel. Nel tuo esempio verrebbe utilizzata, ad esempio, nel seguente modo:

          =sommaMinuti(B2)

oppure

     =sommaMinuti(B2:D2)

Nota che sarà necessario formattare la cella di interesse nel modo desiderato.

===

Regards,

Norman

Answer 2

Ciao Paolo, Ciao Geacs,

Non avrei suggerito una funzione utente se non avessi interpretato erroneamente che il contenuto delle celle B2:M2 fosse della forma

min. 15 o min. 10 o altro

anzichè min. 15 oppure min. 10 ecc, come si può vedere dal mio file di esempio.

===

Regards,

Norman

Answer 3

Una soluzione alternativa al codice.

Prova con questa formula matriciale:

=SOMMA(1*SE((B2:M2=0)*(B2:M2="");0;DESTRA(B2:M2;LUNGHEZZA(B2:M2)-TROVA(".";B2:M2))))/60/24

una volta copiata nella barra delle formule conferma con Ctrl+Maiusc+Invio.

Ovviamente la cella N2 va formattata come ora, in particolare, essendo il risultato di una somma, formato personalizzato [h]:mm

Answer 4

Ciao Geacs,

Potresti scaricare il mio file di prova Geacs20151008.xlsm a:

                               **http://1drv.ms/1LjNRyw**

===

Regards,

Norman

Answer 5

Ciao Geacs,

Preferibile, più flessibile e più robusta sarebbe la seguente versione che permette la sommazione anche di intervalli discontinui:

'=========>>

Option Explicit

'--------->>

Public Function SommaMinuti(ParamArray r() As Variant) As Double

    Dim Rng As Range, rCell As Range

    Dim arrTemp As Variant

    Dim aStr As String, sStr As String

    Dim i As Long, j As Long

    Dim mySum As Long

    aStr = Chr(160) & Chr(111)

    If IsArray(r) Then

        For i = LBound(r) To UBound(r)

            For Each rCell In r(i).Cells

                sStr = rCell.Value

                sStr = Replace(sStr, "min. ", vbNullString)

                arrTemp = Split(sStr, aStr)

                For j = LBound(arrTemp) To UBound(arrTemp)

                    If IsNumeric(arrTemp(j)) Then

                        mySum = mySum + arrTemp(j)

                    End If

                Next j

            Next rCell

        Next i

    End If

   SommaMinuti = mySum / 24 / 60

End Function

'<<=========

===

Regards,

Norman