Condividi:        

MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

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

MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 27/11/09 16:46

Ciao!
Devo elaborare un file xls in questo modo: ho un elenco di 20 utenti, ciascuno inserisce 4 informazioni, dalla colonna J alla M, composte sia da testo che numeri (valutazione e punteggio). In fondo a ciascuna colonna vorrei fare la Media ma nella sintassi ho bisogno di inserire la funzione Destra, in quanto da tutto il loro commento valutativo devo estrapolare (e quindi mediare) solo l'ultimo carattere (numero) che è il punteggio. Tenete presente che non necessariamente tutti gli utenti compilano tutti i campi, quindi potrei avere celle vuote all'interno della colonna.
Ho anche un'altra esigenza: il numero/punteggio va da 1 a 5: vorrei poter relazionare per ciascuna colonna i 20 punteggi per mettere in evidenza gli scostamenti superiori a 1 punto tra utente e utente. Ripeto: purtroppo le celle contengono sia testo che numeri e a me non è venuto in mente altro se non usare Destra....
Aiuuuuto!
(non ho avuto occasione di ringraziare Anthony per una precedente risposta e non voglio alzare un post inutilmente: colgo l'occasione ora: grazie per le dritte sulla protezione celle! :) )
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Sponsor
 

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi tutamimetica » 27/11/09 20:44

Prova in
N1=(SE(J1="";0;DESTRA(J1))+SE(K1="";0;DESTRA(K1))+SE(L1="";0;DESTRA(L1))+SE(M1="";0;DESTRA(M1)))/(4-CONTA.VUOTE(J1:M1))
La seconda richiesta non l'ho capita devi specificare meglio.
tuta
tutamimetica
Utente Senior
 
Post: 209
Iscritto il: 10/06/08 17:25

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 28/11/09 14:53

Ciao tuta e grazie della tua risposta: dal momento che non sono stata chiara nella spiegazione ho adeguato la tua formula in quanto:
I nomi dei 15 utenti che devono votare sono incolonnati a partire dalla riga 4 fino alla riga 18. I voti da dare sono di quattro tipi quindi compilano per la loro riga le colonne J-K-L-M (nella colonna N non mi è stato difficile mettere la media del singolo valutatore, non essendoci celle vuote).
Per ottenere invece la media della valutazione di tutti per il singolo criterio (colonna) mi sono messa in J19 e ho riscritto la tua formula in questo modo:
=(SE(J4="";0;DESTRA(J4))+SE(J5="";0;DESTRA(J5))+SE(J6="";0;DESTRA(J6))+SE(J7="";0;DESTRA(J7))+(SE(J8="";0;DESTRA(J8))+SE(J9="";0;DESTRA(J9))+SE(J10="";0;DESTRA(J10))+SE(J11="";0;DESTRA(J11))+(SE(J12="";0;DESTRA(J12))+SE(J13="";0;DESTRA(J13))+SE(J14="";0;DESTRA(J14))+SE(J15="";0;DESTRA(J15))+(SE(J16="";0;DESTRA(J16))+SE(J17="";0;DESTRA(J17))+SE(J18="";0;DESTRA(J18)))/(15-CONTA.VUOTE(J4:J18)))))
Il sistema non ha protestato ma mi ha reso il risultato 17, il che è impossibile in quanto solo sette utenti hanno compilato (con voti tra il 3 e il 4) e 8 celle sono vuote. Il risultato dovrebbe essere 3,29. Che cavolata ho scritto???
Mi spiego meglio sulla seconda domanda: dato che i punteggi a loro disposizione vanno da 1 a 5 vorrei ottenere l'evidenza di quando esistono nella medesima colonna voti troppo discordanti: il parametro per uno scostamento accettabile è di un punto, da due a quattro punti di scostamento tra qualunque dei 15 utenti è considerata discrepanza. Esiste un modo?
Spero di essere stata chiara e attendo con ansia una risposta, dato che è un lavoro enorme!!! Grazie!
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi tutamimetica » 28/11/09 20:04

Intanto in J19 =(SE(J4="";0;DESTRA(J4))+SE(J5="";0;DESTRA(J5))+SE(J6="";0;DESTRA(J6))+SE(J7="";0;DESTRA(J7))+SE(J8="";0;DESTRA(J8))+SE(J9="";0;DESTRA(J9))+SE(J10="";0;DESTRA(J10))+SE(J11="";0;DESTRA(J11))+SE(J12="";0;DESTRA(J12))+SE(J13="";0;DESTRA(J13))+SE(J14="";0;DESTRA(J14))+SE(J15="";0;DESTRA(J15))+SE(J16="";0;DESTRA(J16))+SE(J17="";0;DESTRA(J17))+SE(J18="";0;DESTRA(J18)))/(15-CONTA.VUOTE(J4:J18))
Era un problema di parentesi.
Per il secondo problema ci sto provando, ma per ora non ho trovato la soluzione.
Pensavo di utilizzare di confrontare il contenuto della cella J4 e confrontarla con J19. se il contenuto della J4> o < di una unità la cella J4 si colora in quanto mi indica una discrepanza. Ho capito il problema? Se ci riesco posto la soluzione.
ciao tuta
tutamimetica
Utente Senior
 
Post: 209
Iscritto il: 10/06/08 17:25

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 28/11/09 22:30

Ciao tuta, grazie per avermi risolto la prima parte.
Il secondo questito è complesso nel senso che tutti i 15 voti andrebbero messi in relazione reciproca, la messa in relazione con la media è meno significativa. A parte il fatto che i riferimenti finirebbero per incrociarsi, poi purtroppo tutti potrebbero essere discrepanti: prendendo ad esempio solo cinque utenti: l'utente A5 vota uno, l'utente A7 vota due, l'utente A9 vota tre, l'utente A12 vota quattro, l'utente A14 vota cinque: ecco che tutti e nessuno sono discrepanti perchè lo scarto minimo per considerarlo una discrepanza è di due punti, ma non in riferimento ad un parametro fisso, bensì "tutti contro tutti" :)
Sempre che questo tipo di controllo incrociato venga permesso dal sistema, temo che sia un'evidenza che non può essere ottenuta.
Lo scopo (forse troppo ottimistico) è mettere in evidenza le differenze importanti di esperienze del medesimo gruppo di lavoro sul medesimo soggetto....... Se non mi sono spiegata bene scusami ma non ti preoccupare, sono quasi certa che non si possa fare.... :(
Grazie mille!! :)
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi tutamimetica » 28/11/09 22:38

Io non riesco ad aiutarti. Già in riferimento alla media mi sono bloccato in quanto non essendo numeri mi creava dei problemi. Mi sa che te sei iscritta a statistica. Ti prometto che ci penso.... e speriamo in Anthony e Flash. Buon fine settimana
tuta
tutamimetica
Utente Senior
 
Post: 209
Iscritto il: 10/06/08 17:25

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 29/11/09 01:27

Grazie tutamimetica, buonanotte! :)
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi Flash30005 » 29/11/09 01:35

Mi rendo disponibile a patto che pubblichi uno schema o foglio facsimile dell'origine dei dati
A volte faccio più fatica a capire come sono distribuiti i dati e a ricreare la stessa situazione su un foglio di excel che a realizzare, se necessario, una macro che faccia l'intero "lavoro".

Comunque penso che, se hai già risolto metà del lavoro con le formule, non mancherà molto alla risoluzione dell'altra metà...

Ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 29/11/09 02:18

VOLENTIERI MA NON SO COME SI METTONO GLI ALLEGATI...
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi tutamimetica » 29/11/09 19:20

Non devi scrivere in maiuscolo, per un problema di netiquette.
Per quanto riguarda il quesito, forse ti potrebbe andare bene così:
in un nuovo foglio in J4=VALORE(DESTRA(Foglio1!J4)) copia trascinando fino a J19
Da I22 a I26 numeri da 1 a 5
In J22=SE(O($J$1=I22;$J$1=I22+1;$J$1=I22-1);"";CONTA.SE($J$4:$J$18;I22)) copia trascinando fino a J26.
In J1 puoi inserire il numero da confrontare es. 1 il test ti scarta 1 e 2 e ti evidenzia le discrepanze di 3, 4 e 5. Se te inserisci 2 ti scarta 1, 2,3 e ti segnala le discrepanze di 4 e 5 e così via. Fammi sapere.
Tuta
tutamimetica
Utente Senior
 
Post: 209
Iscritto il: 10/06/08 17:25

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi Anthony47 » 29/11/09 23:00

La formula di tuta mi pare che risolve gia' il problema; solo ad uso didattico volevo proporre la seguente formula, valida per la col J:
Codice: Seleziona tutto
=MEDIA(SE(VAL.NUMERO(VALORE(DESTRA(J4:J14;1)));VALORE(DESTRA(J4:J14;1));""))
Formula "a matrice" da confermare con Contr-Maiusc-Enter, non col solo Enter; poi copi verso dx per i dati in col K-L-M.

Il secondo quesito mi pare che verta sull' analisi dei voti per capire quanto essi sono allineati o invece divergenti; se e' cosi' allora dovrebbe aiutare una delle funzioni che calcolano la "deviazione", ad esempio DEV.ST
Se ti quadra la formula precedente (Media) una formula possibile dovrebbe essere
Codice: Seleziona tutto
=DEV.ST(SE(VAL.NUMERO(VALORE(DESTRA(J4:J14;1)));VALORE(DESTRA(J4:J14;1));""))
sempre Contr-Maiusc-Enter
Piu' i dati sono allineati e piu' la "deviazione" e' prossima a 0.

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

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 30/11/09 00:34

Ciao tuta e Anthony, intanto mi scuso, ma rendersi conto alle ore 02.18 di avere scritto con il blocca maiuscole inserito è sconfortante...
Per quanto riguarda le medie in fondo alle colonne J:M ho optato per la formula di Anthony: il risultato è uguale ma è più breve. Però adesso qualcuno dovrebbe avere la pazienza di spiegarmi questo: ho trascinato la formula anche in fondo alla colonna N, che contiene la media utente dei quattro parametri di valutazione (colonne J:M) utilizzando la formula di Anthony, ovviamente con gli intervalli J:M anzichè J:J, questo allo scopo di ottenere la media complessiva: il risultato ritornato è 3,86 con entrambe le vostre diverse formule quando la media dovrebbe essere 3,07 in quanto: quattro utenti hanno in N la media 3, uno 3,25, uno 3,50 e uno 2,75, gli altri otto utenti non hanno votato: dato che la somma dei punti fa 21,50 e gli utenti votanti sono sette, non dovrebbe ritornarmi 3,07? .... o ho fatto un'altra asinata?
Per il secondo quesito dell'allineamento dei dati scusa tuta ma sono riuscita ad applicare le tue istruzioni solo fino a un certo punto, quindi ho optato per la formula di Anthony che rende l'idea.
Grazie grazie e grazie ancora! levatemi solo il dubbio della media complessiva e poi vi libero di me :)
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi Anthony47 » 30/11/09 12:48

Il valore che ottieni in fondo alla colonna N e' la media tra i numeri 3-3-3-3-5-5-5; cosa c' entrano questi numeri con i tuoi dati? Sono l' ultima cifra (significativa) del contenuto delle varie celle. E' infatti l' ultima cifra (o meglio: l' ultimo carattere, fosse anche uno "spazio") che la mia formula (e penso anche quella di tuta) estrae dalle celle ed elabora (Nota1)
Ma se in col N hai gia' dei numeri, allora su di essi applicherai la funzione nativa Media, tipo =Media(N4:N14)

Nota1: e se le stringhe da elaborare nelle colonne J:M contengono numeri non di una sola cifra (esempio "grado di attenzione 4,5") otterrai valori errati.

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

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi tutamimetica » 30/11/09 13:13

Avevi scritto "ho bisogno di inserire la funzione Destra, in quanto da tutto il loro commento valutativo devo estrapolare (e quindi mediare) solo l'ultimo carattere (numero) che è il punteggio. ". La mia formula (e quella di Anthony) converte l'ultima cifra che quindi prevede un numero intero. Non capisco quei numeri decimali. Forse devi dirci qualcosina in più.
tuta
tutamimetica
Utente Senior
 
Post: 209
Iscritto il: 10/06/08 17:25

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 30/11/09 14:01

Giusto!! ero tanto presa da tutti questi DESTRA che mi stavo completamente dimenticando che N contiene tre caratteri...
Le colonne J:M sono corrette in quanto è proprio solo l'ultimo carattere quello da elaborare (sempre una singola cifra intera): in N la media mi torna ovviamente dei decimali.
Vado con =Media(N4:N18)
Anthony, mi aiuti anche sulla deviazione in N? ho provato a confrontare l'allineamento dei risultati medi di N con
=DEV.ST(SE(VAL.NUMERO(VALORE(N4:N18));VALORE(N4:N18);"")) ma a giudicare dalla differenza di risultato con le colonne J:M dubito di avere fatto giusto.
Grazie......................................
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi Anthony47 » 30/11/09 15:59

Come detto, il risultato sara' tanto piu' vicino a "zero" quanto piu' i dati considerati sono omogenei; poi, se in N hai dei valori non hai bisogno di usare =DEV.ST(SE(VAL.NUMERO(etc etc, bastera' =DEV.ST(N4:N14).

Se ritieni ancora che ci sia una incongruenza, posta una immagine dei tuoi dati, anche solo tramite "print screen"; le istruzioni le trovi qui: viewtopic.php?f=26&t=80395#p466013.

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

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi tutamimetica » 30/11/09 16:17

Ciao Fantina
ho preso atto della tua richiesta di aiuto e ... ho sviluppato ciò che aveva proposto Anthony. Mi sono divertito a provare i valori e ho visto che tutti 1 e un 2 mi dava risultato 0,25181 con un 3 mi dava 0,516398 con un 4 0,77. Tutti 2 con un 1 mi dava un altro risultato. Mi chiedevo la ragione e ho quindi scartato che ciò dipendeva dalla quantità di numeri diversi, ma dal numero stesso. Ho aperto la funzione DEV.ST e mi sono letto cosa faceva e ho visto che la deviazione standard è una misura che indica quanto i valori si discostino dal valore medio (media).
Ho sostituto la tua formula e ho confrontato il risultato con quello della formula di Anthony e .. i risultati non erano uguali.
Ho faticato un pò a capire il perché. Te nella colonna N hai la media e non è un numero intero ma un decimale (insomma hai dei numeri e non una stringa che noi costringevamo a diventare numero con VALORE(DESTRA(...)). Forse è qui che c'è qualcosa che non va. Ora capisco quei tuoi decimali.
Il resto mi sembra semplice se il risultato tende a 0 significa che non ci sono deviazioni dalla media; all'aumentare di questo valore più la deviazione è forte.
Tuta
tutamimetica
Utente Senior
 
Post: 209
Iscritto il: 10/06/08 17:25

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 01/12/09 01:06

Tutto giusto, non ho più avuto nessuna discrepanza.
Dato che l'analisi che sto facendo per ora è di tipo procedurale i punteggi ritornati sono sufficienti, ma tra brevissimo questa analisi qualitativa dovrà diventare di tipo effettivo ed efficace; mi spiego meglio: ho applicato il concetto dell'allineamento dei dati anche a ciascun utente, cioè delle 4 colonne J:M da lui compilate gli ho spulciato lo scostamento; mettiamo di fissare un punto accettabile a 0,50, questo valore ritorna sia sull'utente che ha messo due volte il valore sufficiente e due volte il valore buono, sia sull'utente che ha messo due volte molto scarso (1) e due volte scarso (2). il prossimo passo è attribuire un ordine di criticità ai 4 parametri valutati.
Se i livelli di criticità fossero impostati in questo modo: J1 critico, k1 molto critico, L1 importante, M1 meno importante lo scostamento numerico più vicino o più lontano dallo zero può venire relazionato ai suddetti livelli di criticità attribuiti ai quattro parametri, impostati sul foglio di lavoro a monte di tutta l'analisi?
... e questo è solo l'inizio... 'notte a tutti, grazie....
Ma grazie davvero anche solo fino a qua... :)
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi Anthony47 » 01/12/09 21:07

... e questo è solo l'inizio... 'notte a tutti, grazie....
Guarda che se vai a dormire invece di applicarti non vai lontana...

Mi pare che ora vorresti calcolarti piu' indici di deviazione, ognuno con una propria gravita'...
Mi pare che avevi gia' calcolato la Dev.St per ogni colonna (con la formula =DEV.ST(SE(VAL.NUMERO(VALORE(DESTRA(J4:J14;1)));VALORE(DESTRA(J4:J14;1));""))) ; ora vorresti calcolare un indice complessivo che prenda in considerazione anche la gravita'; giusto?
Prova a crearti un indice fatto dalla somma di questi indici moltiplicati per un coefficiente; ad esempio (se le varie Dev.St sono in J15:M15) =M15+L15*2+J15*4+K15*8

Se invece devi fare il calcolo riga per riga, teoricamente la stessa formula richiamata sopra va bene, usando l' indirizzo di una singola cella invece che l' indirizzo dell' intero range, moltiplicando per il coefficiente, sommata ai calcoli applicati alle successive tre celle (altre tre formule e altri tre coefficienti); ma se e' questo il caso forse converra' farlo con Matr.Somma.Prodotto, ma questa formula la proporro' solo se realmente ti serve.

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

Re: MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....

Postdi fantina » 02/12/09 01:19

Anthony, non sono riuscita a seguirti nella prima formula =M15+L15*2+J15*4+K15*8.
Ti confermo che le Dev.St sono in J15:M15, ma in riferimento alle colonne mi servono solo da orientamento sull'omogeneità dei giudizi dati dagli utenti sullo stesso fornitore e parametro (ripeto: un utente per riga, quattro parametri da giudicare, i famosi J:M)
Quello a cui volevo applicare anche il metro di gravità era la deviazione dell'utente stesso, quindi: per l'utente A4 esiste l'intervallo J4:M4 compilato; a fianco, in N4, la media aritmetica, in O4 ho aggiunto la precedente formula Dev.St, modificando l'intervallo in J4:M4 (ti prego di confermarmi se ho fatto qualche fesseria in merito....) A questo punto gli scostamenti dallo zero sono di più e più alti ma in questo sistema valutativo il fornitore che ha avuto una media comunque buona passa l'esame, mentre con una deviazione così alta il fornitore va approfondito. La mia intenzione è quindi aggiungere a fianco in P4 un qualcosa che faccia una media intelligente: non va bene che un punteggio basso in J che è per esempio il dato più importante si vanifichi con un punteggio alto in M dove c'è un aspetto relativamente meno importante. Forse non è più il caso di basarsi su Dev.St??
Scusa se sono stata prolississima. Vedi? non vado nemmeno a dormire aspettando la tua risposta...
(io di giorno lavoro eh! :) )
fantina
Utente Senior
 
Post: 491
Iscritto il: 05/03/07 15:24

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "MEDIA DI CELLE CHE CONTENGONO FORMULE E ALTRO.....":


Chi c’è in linea

Visitano il forum: Nessuno e 73 ospiti