Condividi:        

Funzione MEDIA.DEV

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

Funzione MEDIA.DEV

Postdi bibbaste » 03/06/15 15:49

Salve a tutti e complimenti per il supporto che fornite. Ho trovato in pochi giorni molte risposte a quesiti che avevo in stand by da diverso tempo.

Avrei però bisogno di un aiuto:

Come posso fare ad utilizzare la funzione MEDIA.DEV che funzioni anche su una serie di dati filtrati?

Ho utilizzato la funzione =SUBTOTALE(7;A1:A100) ma ovviamente non restituisce lo stesso valore trattandosi di due conteggi diversi.

Un grazie a tutti.
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Sponsor
 

Re: Funzione MEDIA.DEV

Postdi Anthony47 » 03/06/15 21:18

Ciao bibbaste, benvenuto nel forum.
Non sono un esperto di statistica per poter tradurre MEDIA.DEV in una formula che usi funzioni in grado di riconoscere i dati filtrati; quindi suggerisco l' uso di una colonna di appoggio.
Ad esempio, se i dati di cui calcolare la MEDIA.DEV sono in colonna B, usi in una colonna libera, ad esempio a partire da G2, la formula
Codice: Seleziona tutto
=SE(E(SUBTOTALE(3;B2)=1;B2<>"");B2;"")

Poi copi G2 verso il basso, fino a oltre la tua serie di dati; es fino a G1000
A questo punto calcolerai Meda.Dev dei dati filtrati con la formula
Codice: Seleziona tutto
=MEDIA.DEV(G2:G1000)


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

Re: Funzione MEDIA.DEV

Postdi bibbaste » 04/06/15 19:49

Ciao Anthony, ti ringrazio per la risposta, purtroppo non riesco ad attuarla perché la struttura del foglio su cui sto lavorando è pesantuccia: sono oltre 1000 colonne - numerate da 1 a 1024 - per oltre 37000 righe (fortunatamente dentro ci sono solo numeri, che però devono essere perlopiù sottoposte a filtro). Il file adesso pesa 180mb (se hai qualche suggerimento per renderlo più leggero, sono tutto orecchie :-? :lol: )

In cima a queste (il DB parte effettivamente dalla riga 12) ho invece le varie righe (circa una decina appunto) dove analizzo in vario modo (ecco la necessità che ho richiesto prima) la colonna sottostante. Queste analisi poi vengono ripetute in modo identico sulle 1000 colonne.

Perdonami se non riesco ad essere più chiaro :oops: ma spero tu riesca a seguirmi....

Stavo pensando ad una soluzione alternativa e cioè procedere ad un copia\incolla.

Potrei, invece che calcolare prima la media.dev estrapolare la colonna o le colonne che mi servono (quindi già filtrate) e copiarle in un foglio nuovo.

Ora però non so come procedere... l'idea sarebbe questa....

Io ho un foglio (che ho chiamato "Analisi") dove ho nella colonna - diciamo G - il numero di colonna che mi interessa analizzare (riferendomi alle 1024 che dicevo prima nella scheda "Base" quella dove è contenuto tutto il DB).

Ora mi servirebbe una macro che andasse a trovare il numero che ho in pagina Analisi - nella colonna colonna G dentro il foglio "Base" riga 10 (M10:AMV10) e mi copiasse in un foglio nuovo l'intera colonna.

es:

Foglio.... Analisi - G1 contiene il valore 355
cerca in foglio.... Base M10:AMV10 il valore 355 (supponiamo si trovi nella colonna AS10) ... bene questa colona AS deve essere copiata per intero in un foglio nuovo (anche nominandolo "Verifica") nella colonna A

questa procedura dovrei poterla fare per 3 o 4 (max 5) volte quindi le colonne dovrebbero essere copiate nel foglio "Verifica" rispettivamente in colonna A,B,C,D,E


Spero non averti tediato troppo ma non sapevo come altro spiegare la necessità che ho. :oops: :oops:

Spero in un tuo prezioso aiuto.

Ciao a presto
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Re: Funzione MEDIA.DEV

Postdi Anthony47 » 05/06/15 00:36

Tecnicamente la cosa non e' complessa, ma mi permetto di pensare che se analizzi a occhio 1000 colonne, con un tasso di errore (tedesco) dell' 1% alla fine avrai commesso 10 errori e la conclusione avra' la stessa affidabilita' delle estrazioni del lotto.
Secondo me devi farti costruire un reporting system ad hoc.

Comunque ti do dei componenti che devi assemblare per ottenere quello che cerchi.
Per sapere in quale colonna compare un valore puoi usare questa funzione:
Codice: Seleziona tutto
Function FindInCol(ByRef mySh As Worksheet, ByVal myRows As String, ByVal Cosa As Variant) As Long
Dim Last
With mySh.Range(myRows)
  Set Last = .Find(What:=Cosa, After:=.Cells(1, 1), lookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlValues)
End With
If Last Is Nothing Then FindInCol = 0 Else FindInCol = Last.Column
End Function
Mettila in un "Modulo standard" (es Modulo1) del vba e poi la potrai richiamare da un'altra macro ad esempio con
Codice: Seleziona tutto
Xyz=FindInCol(ActiveSheet, "1:37000", Range("G1").Value)

Nella variabile xyz avrai il numero di colonna piu' a destra in cui e' presente il valore contenuto in G1; se il risultato e' 0 devi assumere che il valore non e' presente.

Per copiare la colonna e incollarla in colonna K del foglio Verifica (che deve gia' esistere...) userai
Codice: Seleziona tutto
Columns(xyz).Copy Destination:=Sheets("Verifica").Range("K1")


In mezzo tra la FindInCol e la Columns.Copy inserirai l' eventuale filtro delle colonne (FindInCol non trova un valore presente in una riga nascosta o filtrata).

Per cancellare tutto il contenuto di Verifica, salvo le prime 10 colonne che immaginiamo sino destinate alle formule, userai
Codice: Seleziona tutto
Sheets("Verifica").Range("K1").Resize(Rows.count-10, Column.Count-11).Clear

(Verifica e' da cancellare prima di cominciare a copiarvi dentro i dati del foglio principale)

Con un po' di pazienza potrai comporre la macro che copia le colonne che ti interessano nelle posizioni che interessanno, pronte per essere elaborate dalla formule.

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

Re: Funzione MEDIA.DEV

Postdi bibbaste » 05/06/15 09:22

Antonhy sei gentilissimo ma sono io che evidentemente purtroppo ho delle conoscenze limitate che non mi permettono di attuare le indicazioni che mi hai fornito, che cmq mi riprometto di studiare per utilizzarle. :eeh: Al momento cerco di rendere più semplice la richiesta di aiuto... allora...

nel Foglio1 ho una serie di numeri..... nel Foglio2 cella A1 ho un numero (es. 355)
Quello che mi serve è un modo per trovare nel foglio1 il numero 355 (la ricerca cmq avverrebbe su una singola riga - la #10) e copiare tutta la colonna in cui si trova (es. AS) e incollarla in un altro foglio.

Se la risposta non è diversa da quella che mi hai già fornito (cioè la prima funzione).... in questo caso io l'ho copiata nel modulo1 ma fatto questo non capisco poi come procedere.... :oops: :oops:

Ti ringrazio nuovamente
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Re: Funzione MEDIA.DEV

Postdi Anthony47 » 05/06/15 14:25

Volendo usare la stessa FindInCol devi usare una macro come questa:
Codice: Seleziona tutto
Sub pippo()
Dim FoundIn As Long
'
FoundIn = FindInCol(ActiveSheet, "10:10", Sheets("Foglio2").Range("A1").Value)          '*1
Sheets("Foglio3").Range("K1").Resize(Rows.Count - 10, Columns.Count - 12).Clear         '*2
If FoundIn > 0 Then Columns(FoundIn).Copy Destination:=Sheets("Foglio3").Range("K1")    '*3
'
End Sub

Mettila nello stesso modulo in cui ha inserito la FindInCol (ma non e' obbligatoro)
La riga *1 cerca in quale colonna delle righe indicate (10, nell' esempio) si trova il valore indicato in Foglio2!A1
La riga *2 azzera Foglio3, da colonna K in avanti
La riga *3 copia la colonna individuata in *1 e la incolla in K1 di foglio3

Spero che diventi sempre meno tenebroso...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Funzione MEDIA.DEV

Postdi bibbaste » 05/06/15 14:56

Anthony.... grazie!! Funziona alla perfezione.... adesso la adatto leggermente ai miei fogli ma devo dire che fa esattamente quello che mi serve!!! :lol: :lol:

Spero tu possa darmi ancora del supporto, procedendo un pezzetto alla volta..... :oops:

ciao

Grazie anche per la velocità con cui rispondi....
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Re: Funzione MEDIA.DEV

Postdi bibbaste » 06/06/15 15:41

Ciao Anthony e ciao a tutti.... avrei la necessità di far ripetere una macro fino a quando una determinata cella non assume valore zero o null ??

Come posso fare?

un ringraziamento.

:idea: :idea: Un suggerimento.... c'è un modo per ridurre le dimensioni del file? sta arrivando al limite delle capacità del mac (adesso pesa 326mb) :eeh: :cry:
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Re: Funzione MEDIA.DEV

Postdi bibbaste » 07/06/15 12:06

Ciao a tutti... mi permetto di richiedere ancora un vostro supporto per un'altra difficoltà su excel....
il progetto che sto portando avanti sta girando molto bene anche grazie alle soluzioni trovate su questo forum :D :D ) solo che adesso sono davanti ad un problema (anzi due) che non riesco ad aggirare:

1. ho la colonna A con una scala oraria e la colonna B con valori numerici, sia positivi che negativi e ci sono degli zero (0).... come posso sapere quante righe (quindi quante ore) intercorrono tra uno zero e l'altro? mi potrebbe andare bene una sequenza (meglio se messa su un foglio nuovo) che rappresenti nelle varie colonne quindi A1 troverò "3" A2 troverò "4" A3 troverò "5", B1 ecc ecc i vari intervalli orari

2. sto usando il risolutore.... ma ha un limite di analisi di 200 variabili.... ne conoscete uno che arrivi almeno a 600??

grazie del supporto e buona domenica a tutti
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Re: Funzione MEDIA.DEV

Postdi bibbaste » 07/06/15 22:11

bibbaste ha scritto:Ciao Anthony e ciao a tutti.... avrei la necessità di far ripetere una macro fino a quando una determinata cella non assume valore zero o null ??

Come posso fare?



RISOLTO.

Invece di ripetere la macro fino a quando la cella era vuota, ho combinato la ripetizione n volte (quelle di cui ho bisogno) con un ciclo for ... next
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Re: Funzione MEDIA.DEV

Postdi Anthony47 » 08/06/15 22:38

Bene per la soluzione che hai trovato da solo...

Per quanto riguarda la dimensione del file...
Hai parlato di 1000 colonne * 35000 righe; se ogni cella contiene un valore numerico (ognuno occupa 8 byte) siamo a 280 MByte, per quel foglio; se usi formattazione /formattazione condizionale la dimensione aumenta; se formatti l' intero foglio la dimensione aumenta a dismisura.
Se usi tabelle pivot le dimensioni aumentano alla grande, con le impostazioni di default.
Se usi formule in forma di matrice esse occupano spazio in proporzione alle dimensioni delle matrici coinvolte.
Se il file e' corrotto al suo interno possono essere rimaste strutture dati inutili.
I formati xlsx e xlsm comprimono i dati, ma sui numeri e sui valori logici (formattazione) la compressione e' modesta (sui testi e' piu' evidente).
Il primo tentativo e' di copiare i soli dati in un nuovo file e confrontarne la dimensione col file attuale; se la differenza e' notevole allora io continuerei col nuovo foglio, controllando come cambiano le dimensioni man mano che vi trasferisci l'indispensabile dal vecchio file.

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

Re: Funzione MEDIA.DEV

Postdi bibbaste » 09/06/15 07:15

Anthony, grazie del suggerimento.... tenendo presente quello che mi hai detto, adesso farò attenzione a come sviluppare questo progetto.

Invece non sono riuscito a trovare ancora una soluzione per il problema degli intervalli tra uno zero e l'altro (che ho indicato nel post precedente.

bibbaste ha scritto:1. ho la colonna A con una scala oraria e la colonna B con valori numerici, sia positivi che negativi e ci sono degli zero (0).... come posso sapere quante righe (quindi quante ore) intercorrono tra uno zero e l'altro? mi potrebbe andare bene una sequenza (meglio se messa su un foglio nuovo) che rappresenti nelle varie colonne quindi A1 troverò "3" A2 troverò "4" A3 troverò "5", B1 ecc ecc i vari intervalli orari



Hai qualche suggerimento in merito su cui poi posso poi lavorare?

Grazie





bibbaste ha scritto:
2. sto usando il risolutore.... ma ha un limite di analisi di 200 variabili.... ne conoscete uno che arrivi almeno a 600??

grazie del supporto e buona domenica a tutti



Mi rispondo da solo..... :) :) potete caricare la risorsa gratuita "OPENSOLVER" (per trovarla basta fare una ricerca su google) che lavora molto bene e supera il limite di 200 variabili che ha il risolutore standard di excel

Saluti
bibbaste
Utente Junior
 
Post: 32
Iscritto il: 03/06/15 15:39

Re: Funzione MEDIA.DEV

Postdi Anthony47 » 09/06/15 12:59

Invece non sono riuscito a trovare ancora una soluzione per il problema degli intervalli tra uno zero e l'altro (che ho indicato nel post precedente.

bibbaste ha scritto: 1. ho la colonna A con una scala oraria e la colonna B con valori numerici, sia positivi che negativi e ci sono degli zero (0).... come posso sapere quante righe (quindi quante ore) intercorrono tra uno zero e l'altro? mi potrebbe andare bene una sequenza (meglio se messa su un foglio nuovo) che rappresenti nelle varie colonne quindi A1 troverò "3" A2 troverò "4" A3 troverò "5", B1 ecc ecc i vari intervalli orari


Vedi che succede a inserire un secondo problema in una discussione in corso...

Purtroppo i risultati indicati in A1, A2, A3 non si capisce come dovrebbero essere calcolati; mentre per la colonna B l' indicazione "ecc ecc" e' proprio vaga.
Pertanto l'ho interpretata a modo mio; nell' ipotesi che i dati di partenza siano in Foglio1, allora in A2, B2 e C2 di Foglio2 metti rispettivamente le formule
Codice: Seleziona tutto
=PICCOLO(SE(Foglio1!$B$1:$B$1000=0;RIF.RIGA(Foglio1!$B$1:$B$1000);"");RIF.RIGA(Foglio1!A2))-PICCOLO(SE(Foglio1!$B$1:$B$1000=0;RIF.RIGA(Foglio1!$B$1:$B$1000);"");RIF.RIGA(Foglio1!A1))

=INDICE(Foglio1!$A$1:$A$1000;PICCOLO(SE(Foglio1!$B$1:$B$1000=0;RIF.RIGA(Foglio1!$B$1:$B$1000);"");RIF.RIGA(Foglio1!A1)))

=INDICE(Foglio1!$A$1:$A$1000;PICCOLO(SE(Foglio1!$B$1:$B$1000=0;RIF.RIGA(Foglio1!$B$1:$B$1000);"");RIF.RIGA(Foglio1!A2)))

Tutte le formule sono da confermare con Contr-Maiusc-Enter, non il solo Enter.
Se l' elenco finisce oltre riga 1000, allora modifica quei "1000" in modo da includere certamente tutt i tuoi dati; senza esagerare pero', tenendo a mente che "Se usi formule in forma di matrice esse occupano spazio in proporzione alle dimensioni delle matrici coinvolte."
Poi copia A2, B2, C2 verso il basso, fino ad avere risultati inesistenti (errore #NUM)

Volendo in A1, B1 e C1 metti le intestazioni "Lunghezza", "Da", "A".

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


Torna a Applicazioni Office Windows


Topic correlati a "Funzione MEDIA.DEV":


Chi c’è in linea

Visitano il forum: systemcrack e 31 ospiti