Esercizo n2 somma di quadrati | Excel e gli applicativi Office

Esercizo n.2: somma di quadrati
di scossa data: 30/03/2015 12:58:23
Come promesso ecco l'esercizio per questa settimana.

Data la seguente tabella nel range A1:D5


  | A 	 B   C   D
-------------------
1 | 11  16  21  26
2 | 12  17  22  27
3 | 13  18  23  28
4 | 14  19  24  29
5 | 15  20  25  30

per i formuladipendenti:
scrivere una formula, complessa a piacere, che restituisca la somma dei quadrati delle sole celle del range C1:C5, con l'unico vincolo che la formula proposta deve utilizzare la funzione MATR.SOMMA.PRODOTTO() e fare riferimento all'intero range A1:D5, cioè deve contenere obbligatoriamente: MATR.SOMMA.PRODOTTO(A1:D5 ... nel suo corpo.

Vecchio Frac premierà la formula che riterrà più snella e/o più fantasiosa/originale.

per i codicedipendenti:
scrivere la UDF SommaQuadrati() con parametri a piacere, ma con il vincolo che almeno un parametro deve contenere un riferimento al range A1:D5. La udf devrà restituire la somma dei quadrati delle sole celle del range C1:C5

Vecchio Frac premierà la UDF che riterrà migliore.

N.B.: prego gli utenti esperti di postare le loro soluzioni solo a partire da mercoledì.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di lepat (utente non iscritto) data: 30/03/2015 18:14:25
inizio con una soluzione abbastanza banale, la formula è
=sommaq($A$1:$D$5;3)

Public Function sommaq(rng As Range, col As Long)
For r = 1 To rng.Rows.Count
  sommaq = sommaq + rng.Cells(r, col) ^ 2
Next
End Function

di Luca73 data: 30/03/2015 21:51:04
Ciao Riguardo alla UDF avrei fatto qualcosa di molto simile a Lepat
Riguardo alle formule ho trovato svariatissime funzioni tutte basate su "MATR.SOMMA.PRODOTTO($A$1:$D$5;"
Per Molte Devo ringraziare VF e il suo esercizio della settimana scorsa che mi ha aperto un nuovo orizzonte sulle matrici:
usando MATR.PRODOTTO
=MATR.SOMMA.PRODOTTO($A$1:$D$5;MATR.PRODOTTO($C$1:$C$5;{0,0,1,0}))
usando SCEGLI
=MATR.SOMMA.PRODOTTO($A$1:$D$5^2;SCEGLI({11111};{0,0,1,0}))
oppure
=MATR.SOMMA.PRODOTTO($A$1:$D$5;SCEGLI({1,1,2,1};{0};$C$1:$C$5))
o anche
=MATR.SOMMA.PRODOTTO($A$1:$D$5;$A$1:$D$5;SCEGLI({1,1,2,1};{0};{11111}))
o
=MATR.SOMMA.PRODOTTO($A$1:$D$5^2;SCEGLI({1,1,2,1};{0};{11111}))
usando SE (matriciale)
{=MATR.SOMMA.PRODOTTO($A$1:$D$5;SE(RIF.COLONNA(M1:P5)<>3;0;M1:P5))}
usando solo MATR.SOMMA.PRODOTTO e basta
=MATR.SOMMA.PRODOTTO($A$1:$D$5;$A$1:$D$5;{0,0,1,0,0,1,0,0,1,0,0,1,0,0,1,0})
altre variazioni sul tema sono possibili. Scusate ma mi stavo divertendo e venivano fuori come conigli...
Ciao
Luca

di Luca73 data: 31/03/2015 09:13:08
Ciao
aggiungo anche la UDF usando for each e generalizzando per un qualsiasi range, con indice di colonna relativo.

Public Function SommaQuadrati(ByRef MioRange As Range, ByVal MiaColonna As Long) As Variant
Dim CellaW As Range
    If ((MiaColonna > MioRange.Columns.Count) Or (MiaColonna < 1)) Then
        SommaQuadrati = "#ERRORE: colonna non inclusa#"
    Else
        For Each CellaW In MioRange
            If CellaW.Column = MiaColonna + (MioRange.Cells(1, 1).Column - 1) Then
                SommaQuadrati = sommaq + CellaW ^ 2
            End If
        Next
    End If
End Function

di mandoz (utente non iscritto) data: 31/03/2015 18:26:01
molto interessante la variante M1:P5 - non ci sarei arrivato -

ciao grande

di scossa data: 31/03/2015 21:33:24
Intanto grazie a chi è intervenuto.

Tutte proposte interessanti, ma come detto nel primo post, il giudizio spetta a Vecchio Frac, per cui mi asterrò dal dire che toglierei mezzo punto a chi nel codice non dichiara le variabili :evil:

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di lepat (utente non iscritto) data: 31/03/2015 22:25:03
Giusto, ma devi ammettere che mi sono sforzato molto, non le ho dichiarate tutte, ma alcune sì, quindi rispetto al mio standard merito mezzo punto in più invece che in meno

di Mister_x (utente non iscritto) data: 01/04/2015 00:40:30
ciao

visto che hanno gia' date molte variabili all'utilizzo di tale mi sono cimentato ach'io questi dici minuti a scriverne un paio
la prima
=MATR.SOMMA.PRODOTTO((A1:A5>0)*(B1:B5>0)*(D1:D5>0)*(C1:C5^2))

la seconda quardando i valori proposti da Scossa sulle varie righe i quali se facciamo caso il valore di C non e' nientaltro che D-B+A per ogni riga mi e' scaturita questa
=MATR.SOMMA.PRODOTTO((D1:D5-B1:B5+A1:A5)^2)

ciao

di ninai (utente non iscritto) data: 01/04/2015 07:25:11
ciao motivi familiari non mi hanno permesso di seguire e bene, riporto le due soluzioni che avevo preparato alla pubblicazione del quesito:
=MATR.SOMMA.PRODOTTO((RIF.COLONNA(A1:D5)=3)*A1:D5^2)

e

=MATR.SOMMA.PRODOTTO(INDICE(A1:D5;;3)^2)

della serie "piccolo è bello".
magari formule equivalenti sono state pure già proposte, le guarderò con calma.....

Luca73, TI SEI BRUCIUATO!! la prossima volta, aspetti pure tu

di ninai (utente non iscritto) data: 01/04/2015 07:39:53
Sempre più corta:
=SOMMA(INDICE(A1:D5;;3)^2)
matriciale

di lepat (utente non iscritto) data: 01/04/2015 07:58:57
non credo si possa fare di meglio

di scossa data: 01/04/2015 08:30:35
cit. ninai: "
=SOMMA(INDICE(A1:D5;;3)^2)
=MATR.SOMMA.PRODOTTO((RIF.COLONNA(A1:D5)=3)*A1:D5^2)
"

Non hai letto bene il vincolo dell'esercizio:
" ....cioè deve contenere obbligatoriamente: MATR.SOMMA.PRODOTTO(A1:D5 ... nel suo corpo" meno 20 punti per te

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di ninai (utente non iscritto) data: 01/04/2015 09:42:05
Scossa a parte l'ultima (mi sono fatto prendere dalla sintesi) nelle prime due non capisco dove ho trasgredito

di Textomb data: 01/04/2015 10:22:04
voglio dare anch'io il mio piccolo contributo rispettando i vincoli espressi nel quesito...
=MATR.SOMMA.PRODOTTO(A1:D5^2;{1.1.1.1.1}*{01})

di scossa data: 01/04/2015 10:38:47
cit. ninai: ".... nelle prime due non capisco dove ho trasgredito ...."

il vincol è che deve contenere MATR.SOMMA.PRODOTTO(A1:D5 ......
tu hai usato
MATR.SOMMA.PRODOTTO((RIF.COLONNA
=MATR.SOMMA.PRODOTTO(INDICE(

Sì, lo so, sono più cattivo di Cattivik

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di scossa data: 01/04/2015 10:43:49
Mi pare che Vecchio Frac sia impegnato in altri fronti, quindi - se non interviene lui - domani sera mi assumerò l'ingrato compito del "giudice".
Sarò incorruttibile, quindi non provate ad offrirmi sottobanco meno di una vacanza a Dubai in hotel a 6 stelle.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di ninai (utente non iscritto) data: 01/04/2015 10:52:46
Vabbe allora dillo che sei come quel personaggio di carlo verdone (non ricordo il nome). Almeno l ordine dei fattori di un prodotto potresti ammetterlo. Neanche il nuovo contratto di lavoro ti ha addolcito.

di Vecchio Frac data: 01/04/2015 16:50:18
Purtroppo sono in una situazione difficile... il pc di casa è saltato (capelli dritti... colpa di uno sbalzo di tensione, spero per l'hard disk) e in ufficio ho molto poco tempo in questo periodo.
Vedo che aspettavate me per il giudizio ma io credo che sia più corretto lasciare la decisione di nominare il vincitore all'autore del post... e comunque mi dispiace non partecipare con concentrazione.
Anche perchè tra poco lascio l'ufficio e il massimo che potrò fare sarà scrivere dal tablet :(

di Vecchio Frac data: 01/04/2015 16:56:22
Voglio comunque partecipare con una cosa partorita al volo... certo non è generalizzata ma possiamo accontentarci :)

Option Explicit

Function SommaQuadrati(r As Range) As Integer
' restituisce la somma dei quadrati delle sole celle del range C1:C5
' esempio di utilizzo: ?SommaQuadrati(Range("a1:d5"))

    SommaQuadrati = Application.Sum(r.Offset(, 2).Resize(, 1).EntireColumn)
    
End Function

di scossa data: 01/04/2015 20:09:10
cit. V.F.: "Purtroppo sono in una situazione difficile..."

mi spiace davvero, e ti auguro di sistemare presto e senza troppi danni.

Riguardo al tuo codice, c'è qualcosa che non quadra: infatti, quantomeno, manca il quadrato

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di Vecchio Frac data: 01/04/2015 20:55:12
:( il VF frettoloso partori' codice zoppicante

di Vecchio Frac data: 01/04/2015 21:14:07
Impazzivo se non riuscivo a produrre una soluzione...

p.s. adesso il pc si è ripreso ma si sentono delle deboli scariche quando maneggio i cavi mouse/tastiera e uscita video. Anche i colori del monitor non sono brillanti. Scheda video? Per fortuna sembra che l'hd non abbia subito danni... almeno il tempo di postare questa soluzione, per i prossimi gironi vedremo :)

Option Explicit

Function SommaQuadrati(r As Range) As Long
' restituisce la somma dei quadrati delle sole celle del range C1:C5
' esempio di utilizzo: ?SommaQuadrati(Range("a1:d5"))
Dim c1 As Long, c2 As Long

    c1 = r.Offset(, 2).Resize(, 1).Cells(1) - 1
    c2 = r.Offset(, 2).Resize(, 1).Cells(5)
    
    SommaQuadrati = ((c2 * (c2 + 1) * (2 * c2 + 1)) / 6) - ((c1 * (c1 + 1) * (2 * c1 + 1)) / 6)
    
End Function

di ninai (utente non iscritto) data: 01/04/2015 22:46:58
giusto per non farmi togliere i 20 punti, ripropongo la mia formula iniziale con l'accortezza di mettere a posto l'aspetto "ortografico"

=MATR.SOMMA.PRODOTTO(A1:D5^2*{01})

alias
=MATR.SOMMA.PRODOTTO(A1:D5^2*(RIF.COLONNA(A1:D5)=3))
alias
=MATR.SOMMA.PRODOTTO((RIF.COLONNA(A1:D5)=3)*A1:D5^2)

di scossa data: 01/04/2015 23:28:00
Ciao,

domani purtroppo difficilmente riuscirò a collegarmi per cui anticipo la conclusione di questo eserczio.

Innanzitutto grazie a tutti i partecipanti.

Ho verificato le varie proposte e mi sembrano tutte corrette.

Ovviamente affinché il "gioco" (perché di un gioco si tratta) funzioni le regole devono essere rispettate.
Quindi le prime formule proposte da ninai non sono valide (-20 punti) ma grazie all'ultima proposta se ne guadagna 20 e chiude a 0 punti

Scherzi a parte, non do punti e non faccio classifiche, quindi l'onore e l'onere di proporre il prossimo esercizio spetta a .......

Luca73

per l'entusiasmo mostrato proponendo varie soluzioni.

Svolta questa formalità, devo dire che guardavo le soluzioni proposte con interesse: in verità le avete fatte tutte più complicate del necessario

Infatti la soluzione più semplice è quella di usare il misconosciuto operatore 'spazio' (operatore di intersezione):
=MATR.SOMMA.PRODOTTO(A1:D5 C:C^2)

Chiamata simile anche per la UDF
=SommaQuadrati(A1:D5 C:C)

il cui codice è il seguente:

Function SommaQuadrati(ByRef rng As Range)
    SommaQuadrati = Application.Sum(Application.Power(rng, 2))
End Function

Ed ora aspettiamo il nuovo esercizio di Luca73!

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di ninai (utente non iscritto) data: 02/04/2015 07:58:15
Scossa, conoscendoti, lo sapevo che cera qualche diavoleria di mezzo....., "operatore spazio" "intersezione", ma dove le "peschi"??? non si può "combattere" con te?????

di lepat (utente non iscritto) data: 02/04/2015 12:09:47
soluzioni molto interessanti, degne di scossa, non avevo mai sentito parlare dello spazio come operatore

di Vecchio Frac data: 02/04/2015 15:49:37
Io conoscevo lo spazio come operatore nelle matrici, serve per esempio a restituire la cella all'incrocio tra riga e colonna ad esempio:
=pippo pluto
restituisce la cella in comune tra i due range denominati "pippo" e "pluto" rispettivamente di colonna e riga. Mai usato ma molto comodo probabilmente...

Grazie Scossa!

Complimenti anche a Luca73, aspettiamo (senza fretta) una tua proposta :)

di scossa data: 02/04/2015 17:11:32
cit. V.F.: " Mai usato ma molto comodo probabilmente...."

Si, soprattutto in Excel 2003 usando il filtro automatico.
Infatti quando si imposta il filtro automatico viene creato un named range _FilterDatabase (o _FiltroDatabase nella versione 2010 in italiano) che va a nozze con l'operatore spazio.
Ad esempio
=SUBTOTALE(109;_FiltroDatabase C:C)
si copia facilmente nelle altre colonne e soprattutto si adatta al variare delle righe del named range, al contrario di
=SUBTOTALE(109;C2:C3000)

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di ninai (utente non iscritto) data: 02/04/2015 17:18:13
ciao
prima di chiudere l'argomento, chiedo delucidazione su una delle proposte di luca:

=MATR.SOMMA.PRODOTTO($A$1:$D$5;SE(RIF.COLONNA(M1:P5)<>3;0;M1:P5))

anche matriciale, a me non "confinfera", visto che dite che è funzionante, potete chiarirmela?????

di scossa data: 02/04/2015 18:54:06
cit. ninai: " a me non "confinfera""

E c'hai ragione, c'hai!

Colpa mia, mi è sfuggita al controllo!

così funzionerebbe:
=MATR.SOMMA.PRODOTTO($A$1:$D$5;SE(RIF.COLONNA(A1:D5)<>3;0;A1:D5))

Vabbé, mi scuso per la leggerezza, Luca73 offrirà a tutti un bicchiere d'acqua di rubinetto che potrete andare a bere immediatamente nella vostra cucina alla salute del forum!!

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di Vecchio Frac data: 02/04/2015 20:51:08
cit. "Luca73 offrirà a tutti un bicchiere d'acqua di rubinetto"
---> A me questo scossa goliardico piace moltissimo... fosse femmena sarebbe una scossa goliardica, ma va bene pure masculo ^_^

di scossa data: 02/04/2015 21:09:54

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di ninai (utente non iscritto) data: 03/04/2015 07:41:59
cit.
"ma va bene pure masculo"

a parte la manifestazione di attrazione sessuale, si capisce che non sei siciliano, si dice Fimmina (no femmena) e Masculu (no masculo)