Sviluppare funzionalita su Microsoft Office con VBA Eliminare Errore #VALORE! in MATRICE PRODOTTO a causa di stringhe

LoginRegistrati
Stai vedendo 25 articoli - dal 1 a 25 (di 31 totali)
  • Autore
    Articoli
  • #21432 Risposta

    Ivan Bolzoni

      Buongiorno a tutti,

      ho un problema nel calcolo del numero di voci in un range.

      Vorrei implementare una funzione che conta gli orari in una colonna che poi verrà filtrata. Il problema è che la colonna contiene sia numeri che stringhe , e quando valuto la colonna usando la conversione per le ore: ad esempio (A1:A10)*24 > 12   mi da errore.

      Purtroppo anche usando la funzione IFERROR non riesco a risolvere il problema perché questa funzione mi resituisce solo uno 0, mentre dovrebbe restituire un array di 1 e 0. 

      Nel codice uso MATRICE.SOMMA.PRODOTTO, SUBTOTALE e SCARTO per filtrare la colonna.

       

        =MATR.SOMMA.PRODOTTO(SUBTOTALE(3;SCARTO(D5:D404;RIF.RIGA(D5:D404)-MIN(RIF.RIGA(D5:D404));;1));--(D5:D404*24 >= 6);--(D5:D404*24 < 12 ))

      il codice :   --(D5:D404*24 >= 6)   restituisce 0 o 1  a seconda che l'ora sia maggiore uguale o minore di 6, però mi restituisce errore quando valuta una stringa.

      Usando anche  SE.ERRORE(--(D5:D404*24 >= 6) ;0) questo mi restituisce solo un valore pari a 0 quando trova una stringa, invece vorrei che cambiasse solo l'errore in 0 nella cella corispondente, restituendo l'array di 0 e 1

       

      #21437 Risposta
      Marius44
      Marius44
      Moderatore
      • Sfida #4
        11 pts

        Ciao

        Prova ad allegare il file (senza dati sensibili) perchè così io non so cosa dirti.

         

        Ciao,

        Mario

        #21438 Risposta
        Marius44
        Marius44
        Moderatore
        • Sfida #4
          11 pts

          Ciao

          Prova ad allegare il file (senza dati sensibili) perchè così io non so cosa dirti.

          Cosa intendi quando dici "incontra una Stringa" (non si tratta solo di orari?)

          A lume di naso la butto lì: e con una colonna d'appoggio?

           

          Ciao,

          Mario

          #21442 Risposta

          Ivan Bolzoni

            Buonasera Mario,

            La colonna in questione ospita gli orari di lavoro degli operai e anche stati di inattività come malattie e ferie. Ad esempio contiene i seguenti dati

            1   Mario Rossi               06:00

            2 Giovanni pascoli        07:30

            3 Friedrich Nietzsche   Malattia

            4 Karl Marx                    12:00

             

            Per controllare se lorario è nel turno del mattino moltoplic i valori per 24 e vedo se sono compresi tra 6 e 12 come nel codice sopra ma questo procedimento mi da errore perché non si può moltiplicare la stringa " Malattia" per 24

            #21446 Risposta
            Marius44
            Marius44
            Moderatore
            • Sfida #4
              11 pts

              Buongiorno

              Come ti ho già detto con una colonna d'appoggio risolvi.

              In detta colonna (per es. la Z) inserisci questa formula: =SE(VAL.NUMERO(D3);D3;0) 

              e la copi in basso. Quindi cambia la tua formula sostituendo alla col.D la col.Z; per esempio così:

              =MATR.SOMMA.PRODOTTO(SUBTOTALE(3;SCARTO(Z5:Z404;RIF.RIGA(Z5:Z404)-MIN(RIF.RIGA(DZ:Z404));;1));--(Z5:Z404*24 >= 6);--(Z5:Z404*24 < 12 ))

               

              La formula non "incontrerà" più stringhe ma solo numeri (cioè la prima formula trasforma la stringa in zero).

              Fai sapere. Ciao,

              Mario

               

              #21451 Risposta

              Ivan Bolzoni

                buogiorno,

                Mi servirebbe una formula che possibilmente faccia tutto in un'unica formula senza usare colonne aggiuntive perché potrei non avere i permessi per modificare altre colonne oltre quelle che filtro, e comunque se filtrassi la colonna D non credo si filtrerebbe automaticamente anche la colonna Z.

                Insomma cerco una soluzione che non aggiunga colonne.

                #21453 Risposta

                Ivan Bolzoni

                  Ho provato con questa formula che non mi da più errore:

                    MATR.SOMMA.PRODOTTO(SUBTOTALE(3;SCARTO(A1:A10;RIF.RIGA(A1:A10)-MIN(RIF.RIGA(A1:A10));;1));(-- VAL.NUMERO((A1:A10)*24))*SE(--VAL.NUMERO(A1:A10);((A1:A10)*24)>6;0))

                  però la formula = ((A1:A10)*24)>6     non riesce a restituirmi dei numeri, ma mi restituisce degli orari, ad esempio invece di risultare 6, mi da 00:00

                  #21454 Risposta
                  Marius44
                  Marius44
                  Moderatore
                  • Sfida #4
                    11 pts

                    Ciao

                    Controlla la formattazione della cella. A me dà il risultato corretto.

                     

                    Ciao,

                    Mario

                    #21457 Risposta

                    Ivan Bolzoni

                      Si, se cambio la formattazione esce un numero.

                      Però rimane sempre un problema : la formula sopra 

                      ((A1:A10)*24)>6 

                      non mi restituisce uno 0 o un 1 in base ad ogni cella, ma mi da 1 se tutti gli orari sono maggiori di 6, 0 altrimenti . Quindi come output mi da un solo valore,  mentre io ne vorrei tanti quante sono le celle. Per Intenderci:

                      06:00

                      07:00 

                      08:00

                      09:00 

                      Ferie

                      Vorrei che restituisse {0,1,1,1,0} e non 0 ( perché non tutti sono maggiori di 6) e non vorrei il fastidioso errore #VALORE in corrispondenza di FERIE che mi blocca la valutazione della formula totale 

                      #21458 Risposta

                      Mister_x

                        ciao

                        prova ad usare il conta.se

                        =CONTA.SE(A1:A10;">0,25")

                        ps  le ore vanno espresse in valore numerico   ess 6:00  = 0,25  12:00=0,5

                        06:00
                        07:00
                        08:00
                        pippo
                        10:00
                        11:00
                        pluto
                        12:00
                        05:00
                        06:02

                        =CONTA.SE(A1:A10;">0,25")     risultato  ( 6 )  formato cella    Generale

                        stesso dicasi per il Somma.se() 

                        =SOMMA.SE(A1:A10;">0,25")   risultato ( 54:02 ) formato cella [h]:mm

                        ciao

                        #21464 Risposta

                        Ivan Bolzoni

                          Buongiorno,

                          CONTA.SE, SOMMA.SE non mi  servono in una cella , ma all'interno di una formula con MATR.SOMMA.PRODOTTO e quindi la formattazione non è il problema.  Il problema è che quando inserisco quelle 2 funzioni nella formula  mi esce un numero,  mentre a me serve un vettore con 0 o 1 a seconda che la condizione sia soddisfatta o meno.

                          Se vai sopra trovi la formula con MATR.SOMMA.PRODOTTO

                           

                          #21468 Risposta

                          Mister_x

                            ciao

                            come si fa a provare tale funzione??  ti era stato detto da Mario ( che saluto ) alcuni giorni fa di allegare un file con dati non sensibili , ma che  rispecchiasse il tuo foglio di lavoro, ma a tuttora non si e' visto nulla di questo , quindi non possiamo ipotizzare cosa si vuol ottenere con dei dati che non sono in nostro possesso

                            comunque ipotizzo un soluzione se tu vuoi avere un vettore = 0 o 1 in base alla tua formula SE(--VAL.NUMERO(A1:A10);((A1:A10)*24)>6;0))

                            SE(CONTA.SE(A1:A10;">0,25")>0;1;0)     questa e' una soluzione di sostituzione al tuo se() 

                            ciao

                             

                            #21472 Risposta

                            Ivan Bolzoni

                              Scusate se non ho ancora allegato il file, ma mi sembrava una domanda comprensibile abbastanza. Stasera provvedo ad ad allegare un file che proponga il mio problema .

                              Mister_x, ho già provato la formula che hai proposto e questa funziona bene quando non ci sono stringhe, mentre quando ne incontra una non mi restituisce un vettore di 0 e 1, ma solo 0... in pratica fa collassare il vettore ad un solo valore 

                              #21474 Risposta

                              Mister_x

                                ciao

                                non ci siamo, partiamo dal presupposto che tu conosca la sintassi delle funzioni, quindi possiamo dedurre che da una funzione() dati tanti parametri di calcolo restituisce un solo ( VALORE )

                                ess di una funzione scritta in VBA

                                Function Calcola( Val1,Val2,val3,val4)(RitornaValore as Variant)

                                Calcola = val1+val2+val3+val4

                                end function

                                prendi come esempio la Funzione Somma()

                                quindi come vedi ritorna un solo valore anche se noi ne passiamo 4 , detto questo non penso che si possa far ritornare una stringa di vettori da una funzione ( 0,1,1,0,1,0,1 ) ecc ecc  ma puoi far ritornare solo un valore   o ( 0 =Falso ) o  ( 1=Vero ) 

                                comunque come si dice prova a postare un file e nella cella ( dove vi e' la formula ) cosa ti vuoi trovare

                                #21476 Risposta

                                Ivan Bolzoni

                                  Provando VAL.NUMERO( range) mi restituisce un vettore di 0 e 1 e nella formula MATR.SOMMA.PRODOTTO non ci sono problemi perché moltiplicherà il primo vettore (SCARTO ecc..) per il vettore restituito da VAL.NUMERO.

                                  Credo solo sia un problema di sapere quali funzioni possano avere come output un vettore pari alla dimensione del vettore di input (e che non diano un solo output quando viene passato un array)

                                  #21478 Risposta

                                  Allego il fantomatico file

                                  La formula è in B13 e l'ho aggiornata all'ultima proposta

                                  Errore di valutazione

                                  Allegati:
                                  You must be logged in to view attached files.
                                  #21482 Risposta

                                  Mister_x
                                  Partecipante
                                    2 pts

                                    ciao

                                    ma alla fine quanto e' il valore che devi trovare?????  per caso nel tuo esempio da B2 a B10 > delle ore 6 sono , se faccio il conto a mente sul monitor  sono 6  , perché insisti con quella formula quando ti ho detto di usare CONTA.SE()  ??????

                                    io ho usato CONTA.SE() e la mia formula mi da 6  sia che uso il filtro in colonna A:A e no

                                    =CONTA.SE(B2:B10;">0,25")

                                    ps  le ore per excel sono numeri decimali  i Giorni numeri interi

                                    ess ammettiamo il giorno di oggi e l'ora di adesso

                                    03/12/2019 00:27   tu vedi questo ma in realta sotto e' un valore  43802,01878032410000 dove 43802 sono i giorni trascorsi dalla data 0/0/1900 o 31/12/1899
                                    01878032410000  questo e il tempo

                                    ciao

                                     

                                    #21483 Risposta

                                    Ivan Bolzoni

                                      Non hai capito, perché quando non filtro sono 6, ma quando FILTRO per squadre sicuramente sarà minore di 6. Ora sono a letto e non ho il file davanti, ma se filtro ad esempio squadra 1 gli orari della squadra 1 maggiori di 6 non possono essere 6 dal momento che quel 6 comprende anche i turni della squadra 2.

                                      Io voglio contare GLI ORARI MAGGIORI DI 6 CHE COMPAIONO nella colonna filtrata  : se non è filtrata sono 6, se è filtrata saranno meno. 

                                      Dovrei ottenere una formula che si aggiorna automaticamente ad ogni applicazione del filtro .

                                      #21485 Risposta
                                      Marius44
                                      Marius44
                                      Moderatore
                                      • Sfida #4
                                        11 pts

                                        Buongiorno a tutti

                                        Dire a Mister X "non hai capito" mi sembra eccessivo. Cerchiamo di tenere i tono bassi (non copiamo dai politici).

                                        Hai provato con la Funzione AGGREGA?

                                        Metti in una cella visibile del tuo file d'esempio questa formula (che poi vedremo di adattare):

                                        =AGGREGA(2;1;B2:B10)

                                        e poi filtra, sia per colonna A sia per B

                                        Fai sapere. Ciao,

                                        Mario

                                        #21488 Risposta
                                        Luca73
                                        Luca73
                                        Partecipante
                                          17 pts

                                          Ciao A tutti.

                                          Consiglierei anche di provare con subtotale che puo calcolare sia sui dati filtrati che non filtrati.

                                          Ciao

                                          Luca

                                          #21490 Risposta

                                          Ivan Bolzoni

                                            Buongiorno,

                                            Non c'è nulla di male nel non capire una cosa, molto probabilmente mi sono espresso male io e quindi ogni volta cerco di formulare il problema anche perché più di così non si cosa fare.

                                            Mario: cerco una formula che non usi celle aggiuntive di appoggio 

                                            Luca73: se leggi il primo messaggio della discussione ho postato la formula. È per questo motivo che uso MATR.SOMMA.PRODOTTO con innestato un subtotale,  proprio per lavorare solo su celle filtrate, altrimenti sarebbe stato troppo facile 😎.

                                            #21491 Risposta
                                            Marius44
                                            Marius44
                                            Moderatore
                                            • Sfida #4
                                              11 pts

                                              Ciao

                                              IVAN - Non ho detto di aggiungere alcunchè. Ho detto prova con quella formula "al posto" della tua. Vedrai che conta solo quelli filtrati. Dopo, se va bene, vedremo di adattarla alla tua formula.

                                              Anche Subtotale serve allo scopo (un saluto a Luca73)

                                               

                                              Ciao,

                                              Mario

                                              #21493 Risposta

                                              Mister_x
                                              Partecipante
                                                2 pts

                                                ciao

                                                Option Explicit
                                                Function Sub_Tot(Celle As Range, ora As Double) As Long
                                                Dim cella As Variant
                                                Dim conta As Long
                                                conta = 0
                                                For Each cella In Celle
                                                If IsNumeric(cella) And cella > ora And cella.Rows.Hidden = False Then
                                                conta = conta + 1
                                                End If
                                                Next
                                                Sub_Tot = conta
                                                End Function
                                                

                                                aspettando una formula in excel che faccia questo lavoro  ,  ne dubito e non sono in grado ,in quanto ne Subtotale ne Aggrega hanno la funzione Conta.se() ti passo una UDF che fa questo lavoro

                                                utilizzo , sempre con valori numerici  

                                                =sub_tot(B2:B10;1/24*6)         o     =sub_tot(B2:B10;0,25)

                                                ti riposto il tuo file con inserita questa funzione

                                                PS  scritta aspettando  la pasta che quoce

                                                ciao

                                                Allegati:
                                                You must be logged in to view attached files.
                                                #21495 Risposta
                                                Luca73
                                                Luca73
                                                Partecipante
                                                  17 pts

                                                  Ciao

                                                  Ho fatto qualche prova e secondo me non ne esci con le formule in quanto:

                                                  SUBTOTALE genera un numero che sara la somma o altro dei range mentre MATR.SOMMA.PRODOTTO vuole dei vettori.

                                                  inoltre SUBTOTALE vuole un vettore di range non un vettore di valori pertanto non si riesce a manipolare i dati....

                                                   

                                                   

                                                  #21498 Risposta

                                                  Ivan Bolzoni

                                                    Esatto Luca73, la mia domanda consiste nel trovare una formulazione che riesca a risolvere tale problema....

                                                    Magari senza usare MATR.SOMMA.PRODOTTO ma l'importante è che sia una formula e non un programmino VBA.

                                                  LoginRegistrati
                                                  Stai vedendo 25 articoli - dal 1 a 25 (di 31 totali)
                                                  Rispondi a: Eliminare Errore #VALORE! in MATRICE PRODOTTO a causa di stringhe
                                                  Gli allegati sono permessi solo ad utenti REGISTRATI
                                                  Le tue informazioni:



                                                  vecchio frac - 2748 risposte

                                                  albatros54
                                                  albatros54 - 735 risposte

                                                  patel
                                                  patel - 636 risposte

                                                  Marius44
                                                  Marius44 - 557 risposte

                                                  Luca73
                                                  Luca73 - 523 risposte