-
Articoli
-
Ivan BolzoniBuongiorno 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
Ciao
Prova ad allegare il file (senza dati sensibili) perchè così io non so cosa dirti.
Ciao,
Mario
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
Ivan BolzoniBuonasera 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
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
Ivan Bolzonibuogiorno,
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.
Ivan BolzoniHo 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
Ciao
Controlla la formattazione della cella. A me dà il risultato corretto.
Ciao,
Mario
Ivan BolzoniSi, 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
Mister_xciao
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
Ivan BolzoniBuongiorno,
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
Mister_xciao
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
Ivan BolzoniScusate 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
Mister_xciao
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
Ivan BolzoniProvando 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)
Allego il fantomatico file
La formula è in B13 e l'ho aggiornata all'ultima proposta
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 tempociao
Ivan BolzoniNon 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 .
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
Ciao A tutti.
Consiglierei anche di provare con subtotale che puo calcolare sia sui dati filtrati che non filtrati.
Ciao
Luca
Ivan BolzoniBuongiorno,
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 😎.
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
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 Functionaspettando 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
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....
Ivan BolzoniEsatto 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.
Login
Registrati
