Condividi:        

[Excel] CONTA NUM con blocco $ mobile sul primo valore utile

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[Excel] CONTA NUM con blocco $ mobile sul primo valore utile

Postdi okcoral » 10/04/14 14:39

Ciao a tutti
Sono alle prese con un altro problemino che vado ad esporre:

esempio
In colonna A da A3 ad A30 ho una serie di numeri, sempre gli stessi (1,2,3,4,5,6,7,8 )che in base ad alcuni movimenti cambiano posizione.
Posso avere una serie cosi composta : 1,3,6,3,7,2,8,1,1,5, .............e cosi via su cui con un CONTA NUMERI(A$3:A30) conto gli stessi all'interno di A$3:A30 dove vi sono anche celle vuote, anche queste sempre diverse.

Metto il blocco con il segno $ sulla cella A3 perché conto i numeri sulla colonna di riga in riga.
Fin qui tutto bene.

Mi sono posto una regola che è quella di considerare valida la CONTA solo se i primi due numeri in COLONNA sono DIVERSI
esempio precedente : 1,3,6,3,7,2,8,1,1,5, ....

mentre quando ho i primi due numeri uguali dall'alto verso il basso , esempio : 1,1,6,3,7,2,8,1,1,5, .... la CONTA dovrebbe
partire , nell'esempio sopra dal numero 6 e non considerare i primi due che possono anche essere intervallati da celle vuote.

La mia maldestra risposta suggerirebbe di trovare la soluzione spostando il blocco $ ma chiaramente sono consapevole che la vera soluzione non passa da questo, ergo come si fa?

grazie
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Sponsor
 

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi ninai » 10/04/14 20:20

Ciao
non ho capito bene il problema, soprattutto:
"Metto il blocco con il segno $ sulla cella A3 perché conto i numeri sulla colonna di riga in riga"

Però, da quello che ho intuito , questa formula potrebbe servirti:
=CONFRONTA(1;INDICE(($A$3:$A$30<>"")*($A$3:$A$30<>$A$3););0)
Questa ti restituisce dopo quante righe si trova il primo valore diverso da quello in A3 (nel tuo esempio di prima ti restituirebbe 3, da sommare al 3 della riga iniziale, fa 6).
Potresti usare tale formula in vario modo per raggiungere il tuo conteggio. Per essere più preciso dovrei capire meglio come procedi nel conteggio dei numeri e l'uso del $.
se alleghi un file rappresentativo del problema , ne veniamo fuori prima
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 10/04/14 21:28

Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi Anthony47 » 11/04/14 00:07

Il tuo elenco e' in A3:A30
In B2 usi questa formula
Codice: Seleziona tutto
=SE(INDICE($A$1:$A$30;PICCOLO(SE(A$3:A$30<>"";RIF.RIGA(A$3:$A$30);"");1))=INDICE($A$1:$A$30;PICCOLO(SE(A$3:A$30<>"";RIF.RIGA(A$3:$A$30);"");2));2;0)
Da confermare con Contr-Maiusc-Enter, non il solo Enter.
Poi in B3 inserisci
Codice: Seleziona tutto
=MAX(0;CONTA.NUMERI(A$3:A3)-$B$2)

Formatta la cella in stile "Separatore" (in genere viene rappresentata come "000" nella barra degli strumenti di formattazione) con zero decimali, e infine copi B3 verso il basso.
In questo modo, se i primi due numeri dell' elenco sono uguali essi non saranno conteggiati nel risultato calcolato.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 11/04/14 08:57

Perfetto Anthony47
un pezzo in più per la mia conoscenza
era un punto di arresto su cui ho faticato alcuni giorni.
grazie :)
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 11/04/14 10:11

Per me è di grande utilità quella di rendere "mobile" il riferimento del blocco $ sulla funzione CONTA NUMERI.
In prima istanza ho chiesto questa cosa con la specifica "eccezione" dei primi due numeri in alto sulla COLONNA,
ho provato ad estendere il numero dei doppioni da escludere dal conteggio dai due ai tre , quattro , cinque, e cambiando i riferimenti sulla tua formula , Anthony47, funziona molto bene.

Ora vorrei fare un passo in più e chiedo gentilmente se si può modificare il numero dei doppioni in riferimento alla casualità dei numeri in COLONNA A
Esempio :
Può capitare che i doppioni siano due ma anche che gli stessi siano tre o quattro, ecco vorrei escluderli tutti a seconda dei casi
che possono capitare senza farlo manualmente sulla formula.
Naturalmente è una domanda di aiuto estesa a tutti.
grazie
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi Anthony47 » 11/04/14 13:38

In questo caso i suggerimento e' di passare al vba...
Partendo daExcel:
-Alt F11 per aprire l' editor delle macro
-Menu /inserisci /Modulo
-copia questo codice e inseriscilo nel frame di dx del file
Codice: Seleziona tutto
Function Neutral(ByRef myArea As Range)
'Vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=101754
Dim Cella, I As Long, InitVal
'
For Each Cella In myArea
    If InitVal <> "" Then
        If Cella.Value <> "" Then
            If Cella.Value = InitVal Then
                I = I + 1
            Else
                If I = 1 Then I = 0
                Neutral = I: Exit Function
            End If
        End If
    Else
        If Cella.Value <> "" Then InitVal = Cella.Value
        I = 1
    End If
Next Cella
End Function
A questo punto, seguendo lo schema dela soluzione precedente, in B2 inserisci questa formula
Codice: Seleziona tutto
=Neutral(A3:A30)

Poi procedi come prima: in B3
Codice: Seleziona tutto
=MAX(0;CONTA.NUMERI(A$3:A3)-$B$2)
Formatta, copia verso il basso

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 11/04/14 18:39

Grazie Anthony47

Straaaperfetta


oltre tutto ho visto che in questo modo posso aumentare con un solo Modulo le colonne nel foglio che operano con questi criteri.

buon weekend
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 12/04/14 18:33

Provo ancora con una ulteriore richiesta che si compone di due parti.

Oltre la colonna A che accoglie gli 8 numeri naturali dal n° 1 L n° 8, che con il codice e formule annesse di Anthony47 sono conteggiati con l’” eccezione” che se si presentano in testa alla colonna dei doppi numeri questi non vengono presi in considerazione nei calcoli, ho in COLONNA F la medesima situazione ma questa volta con solo 4 numeri naturali che vanno dal n°1 al n°4.

Questi 4 numeri si ripetono in modo casuale per tutta la lunghezza che va da F3 ad F30, esattamente come gli 8 numeri fanno in COLONNA A, quindi con celle vuote e celle piene.
Può capitare che i DOPPI numeri in testa COLONNA siano più di uno, mentre in colonna A abbiamo solo uno degli 8, che se si ripete per n volte va escluso dal conteggio, qui invece abbiamo anche più numeri
Interessati, tipo: 2,2,2,4,4,1,4,2,3………
La domanda è si può modificare il codice per escludere oltre che la semplice anche l’eccezione multipla dei doppi numeri 2,2,2,4,4?

La seconda parte chiede invece, sempre in COLONNA F se si può fermare il conteggio alla presenza in qualunque riga della colonna F di tutti e 4 i numeri.
Ogni volta e solo in quel caso che tutti e 4 i numeri si siano presentati da F3 ad F30 si ferma il conteggio
Esempio: 2,2,2,4,4,1,4, cel. Vuota, 2,1,1, cel. Vuota, 4,2,1,3 .....stop conteggio perché l’ultimo numero il 3 ha fatto in modo che in colonna vi siano presenti tutti e 4 i numeri.

Grazie e buona giornata a tutti
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi Anthony47 » 12/04/14 19:13

Quindi si tratterebbe di neutralizzare due sequenze, tipo 1,1,1,2,2 (non 1,2,1,2) eventualmente con intercalati degli spazi; vero?
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 12/04/14 20:33

si Anthony47

ma anche 1,1,1,2,2,3,3,3,3, ecc ecc..
oppure 1,1,1,2,2,4,4,1,1,3,3........
oppure anche solo 1,1,1, come nella precedente richiesta sugli 8 numeri .

in modo che qualunque eventuale serie ripetuta multipla o non venga neutralizzata.
solo se di seguito una all'altra anche con spazi vuoti
grazie
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi Anthony47 » 14/04/14 01:18

Mah, non escludo che possa andare bene questa Function:
Codice: Seleziona tutto
Function GPNeutral(ByRef myArea As Range)
'Vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=101754
Dim Cella As Range, I As Long, LVal, CCVal
Dim DiffCnt As Long
'
For Each Cella In myArea
    CCVal = Cella.Value
    If CCVal <> "" Then
        If CCVal = LVal Then
            DiffCnt = 0
            I = I + 1
        Else
            I = I + 1
            LVal = CCVal
            DiffCnt = DiffCnt + 1
            If DiffCnt = 2 Then
                GPNeutral = I - 2
                Exit Function
            End If
        End If
    End If
Next Cella
If I > 0 Then GPNeutral = I - 1 * DiffCnt
End Function

Quindi invece di calcolare =Neutral(Range) calcolerai
= GPNeutral(Range)

Prova e fai sapere...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 14/04/14 09:09

Complimenti
anche questa volta hai centrato in pieno
con questo ultimo tassello aggiunto agli altri posso completare il mio lavoro.
Provo da solo a risolvere la seconda richiesta, quella dello stop in colonna al presentarsi di tutti
e 4 i numeri , magari aggiungendo una colonna di appoggio.

Spero che questi mio topic con le tue soluzioni possano essere di aiuto ai tanti che cercano.

Grazie Anthony47, un saluto a tutti quanti i frequentatori di pc-facile.
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 14/04/14 13:44

okcoral ha scritto:Provo ancora con una ulteriore richiesta che si compone di due parti.

Oltre la colonna A che accoglie gli 8 numeri naturali dal n° 1 L n° 8, che con il codice e formule annesse di Anthony47 sono conteggiati con l’” eccezione” che se si presentano in testa alla colonna dei doppi numeri questi non vengono presi in considerazione nei calcoli, ho in COLONNA F la medesima situazione ma questa volta con solo 4 numeri naturali che vanno dal n°1 al n°4.

Questi 4 numeri si ripetono in modo casuale per tutta la lunghezza che va da F3 ad F30, esattamente come gli 8 numeri fanno in COLONNA A, quindi con celle vuote e celle piene.
Può capitare che i DOPPI numeri in testa COLONNA siano più di uno, mentre in colonna A abbiamo solo uno degli 8, che se si ripete per n volte va escluso dal conteggio, qui invece abbiamo anche più numeri
Interessati, tipo: 2,2,2,4,4,1,4,2,3………
La domanda è si può modificare il codice per escludere oltre che la semplice anche l’eccezione multipla dei doppi numeri 2,2,2,4,4?

La seconda parte chiede invece, sempre in COLONNA F se si può fermare il conteggio alla presenza in qualunque riga della colonna F di tutti e 4 i numeri.
Ogni volta e solo in quel caso che tutti e 4 i numeri si siano presentati da F3 ad F30 si ferma il conteggio
Esempio: 2,2,2,4,4,1,4, cel. Vuota, 2,1,1, cel. Vuota, 4,2,1,3 .....stop conteggio perché l’ultimo numero il 3 ha fatto in modo che in colonna vi siano presenti tutti e 4 i numeri.

Grazie e buona giornata a tutti


@Anthony47
Inizialmente avevo progettato di sviluppare in COLONNA F un reset del conteggio numeri al presentarsi di tutti e 4 i numeri
e di nuovo iniziare da F3 con celle vuote che accogliessero un riempimento progressivo da F3 ad F30.

Ora con il tuo intervento, con il codice e formule annesse,la cosa è fattibile e di fatto potrei terminare qui la parte del progettino
e iniziare a costruire un foglio vero e proprio, dove le COLONNE interessate sono circa 6 , Sei colonne che si muovono tutte in sincrono con la logica della neutralizzazione del conteggio delle DOPPIE, TRIPLE....e cosi via presenze.

Di fatto ogni colonna però fa reset quando gli pare in relazione alla presenza dei suoi 4 numeri.
Resettando e tornando in cima alla colonna (con una o più delle 6 disponibili) mi troverei con uno SFASAMENTO di RiGA tra le colonne.
Sarebbe invece più ordinato fare il RESET alla RIGA successiva alla presenza dei 4 numeri , in modo che la colonna o le colonne
continuino con il loro progressivo riempimento e con il numero di RIGA uguale.

La richiesta è:
mettere un RESET al conteggio Function GPNeutral(ByRef myArea As Range) alla presenza in colonna di ognuno dei 4 numeri ( chiaramente tre di questi potranno avere più presenze , mentre solo uno di questi con una sola presenza CHIUDERA'
alla RIGA (dove capita) il conteggio.
Alla riga successiva riprendere il conteggio con le stesse modalità, in modo che le sei colonne siano allineate alla medesima riga.

Consapevole che di richieste ne ho fatte in abbondanza
ti ringrazio molto della tua disponibilità
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi Anthony47 » 15/04/14 01:02

Non ho capito la richiesta, forse perche' non ho mai capito il formato dei dati sottostanti.
GPNeutral conta i valori da ignorare, fin qui definiti come N sequenze di numeri uguali; quindi che significato ha "mettere un RESET al conteggio" di GPNeutral alla presenza di ognuno dei 4 numeri.

Forse uno screenshot dei dati e un nuovo giro di descrizione potrebbe giovare...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 15/04/14 10:00

http://imageshack.com/a/img834/8241/c8mt.png




i ok

Ricapitolando :
con la mia penultima richiesta in relazione alla COLONNA F mi hai dato la soluzione con la formula =GPNeutral(F3:F30) che con la
Function GPNeutral neutralizza tutti i DOPPI, TRIPLI, QUADRUPLI e cosi via ,di ogni numero compreso da 1 a 4.
Il lavoro che fanno in colonna F è perfetto , perche in via normale il conteggio opera come sempre con la formula CONTA NUMERI,
quando si presentano le eccezioni me le esclude tutte dal conteggio e la formula CONTA NUMERI riparte dal primo valore utile,
quello senza doppioni che trova in colonna.

Ora seguendo sempre questa logica avrei necessità , sempre in COLONNA F al presentarsi dell'ultimo dei 4 numeri possibili
di "fermare" tutto il processo , in modo che possa ripartire dalla cella sottostante allo "STOP" con le stesse modalità ma senza tenere conto di tutti i conteggi precedenti

Allego uno screenshot
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 15/04/14 11:52

Nello screenshot che ho compilato ho fatto un esempio di quello che cerco
chiaramente ogni dato può essere diverso, sopratutto l'incolonnamento dei valori che nell'esempio allegato
ha la formula GPNeutral(F:F) cella gialla con rettangolo nero dopo ogni numero in neretto che rappresenta il quartultimo
dei (1,2,3,4) che chiude la serie . Ho messo più formule GPN. solo per far intendere quello che vorrei.

Insomma dopo ogni tot casuali di righe al presentarsi di tutti e 4 i numeri si chiude la serie e subito dopo se ne incomincia
un'altra e cosi via ( il limite di F30 l'ho messo in fase descrittiva per farla più semplice) la COLONNA F prosegue oltre.


La cosa importante è che il calcolo comprenda sia le eccezioni che il normale conteggio di una formula CONTA NUMERI .

come sempre ringrazio
Buona giornata a tutti
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi Anthony47 » 16/04/14 00:53

Pero' l' analisi e' parziale...
Se la sequenza in verticale fosse di 1, 1, 1, 4, 4, 1, 2, 3, 3, 3, 4, 4, 3, 3, etc etc (in bold sono caratteri aggiuntivi rispetto alla tua sequenza) dove si ferma la prima serie e comincia la seconda?

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 16/04/14 10:11

ciao Anthony47

Anthony47 ha scritto:Pero' l' analisi e' parziale...
Se la sequenza in verticale fosse di 1, 1, 1, 4, 4, 1, 2, 3, 3, 3, 4, 4, 3, 3, etc etc (in bold sono caratteri aggiuntivi rispetto alla tua sequenza) dove si ferma la prima serie e comincia la seconda?

Ciao


Ogni serie termina in questo modo, sempre :

1, 1, 1, 4, 4, 1, 2, 3, / stop perché contiene tutti e quattro i numeri naturali da 1 ad 4 , infatti il 3 chiude la serie, presentandosi solo una volta come deve essere / qui inizia la seconda serie 3, 3, 4, 4, 3, 3,......continuando........ ,4,3,2,2,2,2,1, / stop perché contiene tutti e quattro i n. naturali da 1 ad 4, infatti il n.1 chiude la serie, presentandosi solo una volta.

In verticale:

1
1
1
4
4
1

2

3

stop

3
3
4
4
3
3
4

3

2
2
2

2
1

stop
.
.
.
.
.
.
.

e cosi via......

ogni serie chiude al presentarsi dell' ULTIMO di uno qualsiasi dei 4 numeri e ricomincia con le medesime modalità
la serie potrebbe essere lunga anche decine di righe in verticale ma se manca uno dei 4 numeri non può chiudere.

Naturalmente ogni serie contempla nel conteggio sia le eccezioni ( numeri uguali di varia quantità ) sia la mancanza di queste.


La COLONNA F contiene più serie per tutta la sua lunghezza
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Re: [Excel] CONTA NUM con blocco $ mobile sul primo valore u

Postdi okcoral » 18/04/14 00:45

Spero di essere stato chiaro, soprattutto esaustivo,
mi rendo conto di aver usato le parole in modo un po' ridondante.
Ringrazio dell' aiuto che per un principiante come me è grande.
Windows 7 Office 2013
okcoral
Utente Junior
 
Post: 36
Iscritto il: 26/12/11 00:30
Località: Ivrea

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "[Excel] CONTA NUM con blocco $ mobile sul primo valore utile":

BTp Valore
Autore: MarioLombardi
Forum: Forum off-topic
Risposte: 2

Chi c’è in linea

Visitano il forum: Nessuno e 57 ospiti