QUIZ n.6
Scossa
Parte 1:
Dato un foglio "STRUTTURE" e la seguente struttura di dati, ordinati
per codice in colonna A crescente:
A B C D E
1 Sede Filiale DA A INDIRIZZO
--------------------------------------------------------------------
2 5301 5351
3 5301 5369
4 5301 5402
5 5301 5431
6 5301 5460
7 5301 5470
8 5301 5813
9 5301 5870
10 5301 5872
11 5301 5876
12 5301 5880
13 5301 12013
14 5301 15054
15 5301 15056
16 5301 15059
17 5307 5322
18 5307 5333
19 5307 5338
20 5307 5342
21 5307 5434
22 5307 5462
23 5307 5503
24 5307 5506
25 5307 5520
26 5307 12018
27 5307 15000
28 5307 15002
29 5307 15003
30 5307 15004
31 5307 15007
32 5307 15008
33 5307 15013
34 5309 5320
35 5309 5346
36 5309 5360
37 5309 5364
38 5309 5498
39 5309 5504
Problema n. 1:
trovare le tre formule che, digitate rispettivamente in C2 in D2 e in
E2 e poi copiate in basso, permettano
di compilare in automatico:
- in C l'indirizzo assoluto della prima cella di ogni Sede,
relativamente alla colonna B
- in D l'indirizzo assoluto dell'ultima cella di ogni Sede,
relativamente alla colonna B
(ad esempio la Sede 5301 va da A2 a A16 pertanto le Filiali che
apppartengono
alla Sede 5301 vanno da $B$2 a $B$16, etc)
- in E l'indirizzo completo dei singoli gruppi di Filiali per ogni
riga
come da seguente risultato atteso:
A B C D E
1 Sede Filiale DA A INDIRIZZO
---------------------------------------------------------------------------------------------------------
2 5301 5351 $B$2 $B$16 STRUTTURE!$B$2:$B$16
3 5301 5369 $B$2 $B$16 STRUTTURE!$B$2:$B$16
4 5301 5402 $B$2 $B$16 STRUTTURE!$B$2:$B$16
5 5301 5431 $B$2 $B$16 STRUTTURE!$B$2:$B$16
6 5301 5460 $B$2 $B$16 STRUTTURE!$B$2:$B$16
7 5301 5470 $B$2 $B$16 STRUTTURE!$B$2:$B$16
8 5301 5813 $B$2 $B$16 STRUTTURE!$B$2:$B$16
9 5301 5870 $B$2 $B$16 STRUTTURE!$B$2:$B$16
10 5301 5872 $B$2 $B$16 STRUTTURE!$B$2:$B$16
11 5301 5876 $B$2 $B$16 STRUTTURE!$B$2:$B$16
12 5301 5880 $B$2 $B$16 STRUTTURE!$B$2:$B$16
13 5301 12013 $B$2 $B$16 STRUTTURE!$B$2:$B$16
14 5301 15054 $B$2 $B$16 STRUTTURE!$B$2:$B$16
15 5301 15056 $B$2 $B$16 STRUTTURE!$B$2:$B$16
16 5301 15059 $B$2 $B$16 STRUTTURE!$B$2:$B$16
17 5307 5322 $B$17 $B$33 STRUTTURE!$B$17:$B$33
18 5307 5333 $B$17 $B$33 STRUTTURE!$B$17:$B$33
19 5307 5338 $B$17 $B$33 STRUTTURE!$B$17:$B$33
20 5307 5342 $B$17 $B$33 STRUTTURE!$B$17:$B$33
21 5307 5434 $B$17 $B$33 STRUTTURE!$B$17:$B$33
22 5307 5462 $B$17 $B$33 STRUTTURE!$B$17:$B$33
23 5307 5503 $B$17 $B$33 STRUTTURE!$B$17:$B$33
24 5307 5506 $B$17 $B$33 STRUTTURE!$B$17:$B$33
25 5307 5520 $B$17 $B$33 STRUTTURE!$B$17:$B$33
26 5307 12018 $B$17 $B$33 STRUTTURE!$B$17:$B$33
27 5307 15000 $B$17 $B$33 STRUTTURE!$B$17:$B$33
28 5307 15002 $B$17 $B$33 STRUTTURE!$B$17:$B$33
29 5307 15003 $B$17 $B$33 STRUTTURE!$B$17:$B$33
30 5307 15004 $B$17 $B$33 STRUTTURE!$B$17:$B$33
31 5307 15007 $B$17 $B$33 STRUTTURE!$B$17:$B$33
32 5307 15008 $B$17 $B$33 STRUTTURE!$B$17:$B$33
33 5307 15013 $B$17 $B$33 STRUTTURE!$B$17:$B$33
34 5309 5320 $B$34 $B$39 STRUTTURE!$B$34:$B$39
35 5309 5346 $B$34 $B$39 STRUTTURE!$B$34:$B$39
36 5309 5360 $B$34 $B$39 STRUTTURE!$B$34:$B$39
37 5309 5364 $B$34 $B$39 STRUTTURE!$B$34:$B$39
38 5309 5498 $B$34 $B$39 STRUTTURE!$B$34:$B$39
39 5309 5504 $B$34 $B$39 STRUTTURE!$B$34:$B$39
in modo tale che, se si variasse il valore di un codice Sede (p.e.:
cella A17 da 5307 a 5301)
riordinando per valore di A crescente, si aggiornino automaticamente
le colonne C D E.
Parte 2:
Definito il seguente nome:
nome:: Stru
Riferito a:: =STRUTTURE!$A$2:$E$39
scrivere una formula che, digitata in una qualsiasi cella esterna a
Stru, restituisca il numero
complessivo delle filiali presenti in Stru (38), con il vincolo di
utilizzare nella formula il nome Stru.
Bye!
scossa
Scossa
Riprovo sperando che la formattazione sia mantenuta:
QUIZ 6
Parte 1:
Dato un foglio "STRUTTURE" e la seguente struttura di dati, ordinati
per codice in colonna A crescente:
A B C D E
1 Sede Filiale DA A INDIRIZZO
Problema n. 1:
trovare le tre formula che digitate rispettivamente in C2 in D2 e in
E2 e poi copiate in basso permettano
di compilare in automatico:
- in C l'indirizzo assoluto della prima cella e
- in D l'indirizzo assoluto dell'ultima cella di ogni Sede,
relativamente alla colonna B
(ad esempio la Sede 5301 va da A2 a A16 pertanto le Filiali che
apppartengono
alla Sede 5301 vanno da $B$2 a $B$16, etc)
- in E l'indirizzo completo dei singoli gruppi di Filiali per ogni
riga
vedasi seguente risultato atteso:
A B C D E
1 Sede Filiale DA A INDIRIZZO
Parte 2:
Rifrito a:: =STRUTTURE!$A$2:$E$39
scrivere una formula che, digitata in una qualsiasi cella esterna a
Stru, restituisca il numero
complessivo delle filiali presenti in Stru, con il vincolo di
Andrea.9
> trovare le tre formula che digitate rispettivamente in C2 in D2 e in
> E2 e poi copiate in basso permettano
> di compilare in automatico:
> - in C l'indirizzo assoluto della prima cella e
> - in D l'indirizzo assoluto dell'ultima cella di ogni Sede,
in C2: ="$B$"&CONFRONTA(A2;A$2:A$39;0)+1
in D2: ="$B$"&CONFRONTA(A2;A$2:A$39)+1
in E2:
=CONCATENA(STRINGA.ESTRAI(CELLA("filename");TROVA("]";CELLA("filename"))
+1;255);"!";C2;":";D2)
(la terza scoppiazzata)
per il problema 2 ora provo
ciao
Andrea.9
>
> Definito il seguente nome:
> nome:: Stru
> Rifrito a:: =STRUTTURE!$A$2:$E$39
>
> scrivere una formula che, digitata in una qualsiasi cella esterna a
> Stru, restituisca il numero
> complessivo delle filiali presenti in Stru, con il vincolo di
> utilizzare nella formula il nome Stru.
presupponendo il foglio STRUTTURE come indicato:
=CONTA.VALORI(Stru)/5
........ non ci posso credere
r
beh se le filiali sono univoche (non ci sono doppioni) direi che va
bene ... però aspettiamo il Quisitore :-)
r
r
però per la 3 quanta fatica ...
stando al quesito bastava:
="STRUTTURE!"&C2&":"&D2
:-)
r
r
e in questo caso basta:
=RIGHE(stru)
r
r
ho messo qui il file col quesito:
https://sites.google.com/site/e90e50/documento-plinius/quizzone-di-excel/quesiti
r
r
se non fossero univoci ...
=SOMMA(--(FREQUENZA(INDICE(stru;;2);INDICE(stru;;2))>0))
che non è la più corta ... ma credo la più efficiente.
saluti
r
Scossa
Direi che per me è OK. PROCLAMO VINCITORE Andrea.9
Due precisazioni.
Per la prima parte:
in realtà per la formula in E non avevo chiesto la flessibilità di
gestire il nome del foglio dinamicamente, per cui bastava un banale
="STRUTTURE!"&C2&":"&D2
Per la 2a parte:
usando la tabella dell'esempio la risposta di Andrea.9 è valida, anche
se in realtà non conta effettivamente i valori presenti nella colonna
B: se una cella fosse vuota (filiale mancante) il risultato sarebbe
errato.
Eventualmente si potrebbero assegnare 3 punti a chi propone una
soluzione migliore a questa parte, che dici?
Bye!
Scossa
Scossa
Ok, però i valori (codici filiali) sono comunque univoci, al massimo
potrebbero essere assenti (cella vuota).
Bye!
scossa
r
no direi di no ... anche perchè sarebbero già miei :-)
comunque ... ottimo lavoro Elettrico! ... sei stato veloce ...
fulmineo ... rapido come una scossa :-)
vado ad aggiornare la classifica ... mi sono accorto d'aver postato
prima delle 2 ore da regolamento ... per quanto riguarda la formula
breve ... quindi aspetto ad aggiornare il punteggio (3 punti) per i
quali comunque la partita è sempre aperta.
r
p.s.
Andrea!
EVVVVVVAIIIIII !!!!!
ora hai tempo 2 giorni per metterci alla prova
r
la formula funzionerebbe ugualmente ...
comunque terrei buona la risposta di andrea e conseguentemente la
specifica che valori presenti e univoci ... sei daccordo?
poi eventualmente si gioca con altre formule ...
ciao
r
p.s.
classifica aggiornata
p.s.2
sarebbe il caso di postare tutte le risposte in un solo post ...
io per primo visto che ho seminato le mie ... ma anche andrea che a
maggior ragione ha rischiato di dare suggerimenti e vedersi soffiare
la vittoria :-) ... dai ti è andata bene :-)
Scossa
>
> no direi di no ... anche perchè sarebbero già miei :-)
>
Eh no, leggi bene la domanda:
"... restituisca il numero complessivo delle filiali presenti in Stru,
con il vincolo di
utilizzare nella formula il nome Stru."
dove numero complessivo non vuol dire univoco, per cui si può
semplificare fregandosene di eventuali ripetizioni ma non di eventuali
celle vuote ....
Fra quante ore posso postare la mia soluzione? :-))
Bye!
scossa
r
> On 7 Mag, 00:26, r <robb....@gmail.com> wrote:
>
>
>
> > no direi di no ... anche perchè sarebbero già miei :-)
>
> Eh no, leggi bene la domanda:
>
> "... restituisca il numero complessivo delle filiali presenti in Stru,
> con il vincolo di
> utilizzare nella formula il nome Stru."
>
> dove numero complessivo non vuol dire univoco, per cui si può
> semplificare fregandosene di eventuali ripetizioni ma non di eventuali
> celle vuote ....
tu sei il giudice ... tu decidi ... DITTATORE!
:-)
>
> Fra quante ore posso postare la mia soluzione? :-))
2 lunghi giorni!
>
> Bye!
> scossa
ciao
r
Scossa
Quale formula funzionaerebbe ugualmente con alcune celle vuote?
Non CONTA.VALORI(Stru)/5
> comunque terrei buona la risposta di andrea e conseguentemente la
> specifica che valori presenti e univoci ... sei daccordo?
Sì sì!
> poi eventualmente si gioca con altre formule ...
Certo!
Bye!
scossa
r
> > dove numero complessivo non vuol dire univoco, per cui si può
> > semplificare fregandosene di eventuali ripetizioni ma non di eventuali
> > celle vuote ....
quindi?
pignolino ... se vuoi le celle vuote escluse ... che ffai ti rimangi
la parola sulla vittoria di andrea?
eh eh ... sarai mica così b ... rdo :-)
e nel caso preparati a una montagna di ricorsi!
r
r
do le mie per la breve:
="$B$"&CONFRONTA(A2;A$1:A$39;0)
="$B$"&CONFRONTA(A2;A$1:A$39)
="STRUTTURE!"&C2&":"&D2
=RIGHE(stru)
le prime due potrebbero diventare anche
="$B$"&CONFRONTA(A2;A:A;0)
="$B$"&CONFRONTA(A2;A:A)
saluti
r
Scossa
NO NO la risposta di Andrea va benissimo.
éerò si può scrivere qualche altro post (Plinius doc docet)
tergiversando su una variante alla formula che tenga conto di
eventuali celle vuote (in realtà è abbastanza banale)
Bye!
scossa
r
=CONTA.VALORI(INDICE(stru;;2))
servito
:-)
Scossa
>
> do le mie per la breve:
> ="$B$"&CONFRONTA(A2;A$1:A$39;0)
> ="$B$"&CONFRONTA(A2;A$1:A$39)
> ="STRUTTURE!"&C2&":"&D2
> =RIGHE(stru)
>
> le prime due potrebbero diventare anche
> ="$B$"&CONFRONTA(A2;A:A;0)
> ="$B$"&CONFRONTA(A2;A:A)
>
Credo che ti puoi segnare altri 3 punti per le formule più brevi.
Però vista la velocità con cui si è risoloto e per dare modo ai non
nottambuli di partecipare, metterei in palio 3 punti a chi fornisce
la formula per il conteggio delle celle non vuote della colonna
filiali (sempre col vincolo di usare Stru).
Bye!
scossa
Scossa
NON è la più breve :-))
Bye!
Scossa
Scossa
> Però vista la velocità con cui si è risoloto e per dare modo ai non
> nottambuli di partecipare, metterei in palio 3 punti a chi fornisce
> la formula per il conteggio delle celle non vuote della colonna
> filiali (sempre col vincolo di usare Stru).
P.S.: un'altra volta posto il Quiz alle 9 di mattina, così taglio
fuori CERTI nottambuli :-))
Bye!
scossa
r
quanto rompi! :-)
per la più breve così ... è fuori concorso ... quindi postala pure che
poi te la sego! :-)
vado a letto ...
ciao
r
r
non sarai mai più così fortunato come in quesito 5 :-D
vai a letto va ...
:-)
notte
r
p.s.
comunque belli anche i quiz lampo
Scossa
> quanto rompi! :-)
> per la più breve così ... è fuori concorso ... quindi postala pure che
> poi te la sego! :-)
> vado a letto ...
=CONTA.VALORI(Stru B:B)
e con questa spengo il pc ... tu tagliala:-)
'notte!
Bye!
scossa
plinius
"r" <robb...@gmail.com> ha scritto nel messaggio
news:d48c90e4-81ad-4570...@u16g2000yqj.googlegroups.com...
*************************************
Qualcuno parla di nottambuli, vedo!
Ho letto tutto e mi pare che non ci sia più nulla da aggiungere, salvo
provare a rubare subdolamente i 3 punti di r... gh gh gh
Accorcio la prima formula (quella di colonna C):
=SE(A2=A1;C1;"$B$"&RIF.RIGA())
sono 30 caratteri invece di 33... e adesso vediamo se r dorme!!
'notte,
E. ;-)
paoloard
news:c3635a59-fabf-4418...@p13g2000yqh.googlegroups.com...
Bye!
scossa
-------------------------------------------------
Eh noooo! Questa ve la siete fatta tutta di notte! Peggio dei navy seals.
Bisogna aggiornare il regolamento!! Quiz solo dalle 9 di mattina :-)
Se sto al PC anche di notte il mattino dopo mi ritrovo con le mie cose in
strada :-)
Non vorrete essere la causa di una crisi matrimoniale! Vero? O no?
Altrimenti capeggio una rivolta con i "giornalieri" per un trust di cervelli
diurno
al motto di:
"un volgo disperso repente si desta
intende l'orecchio, solleva la testa
percosso da novo crescente romor
................................................"
ciao paoloard
http://www.riolab.org
Mauro Gamberini
strada :-)
Non vorrete essere la causa di una crisi matrimoniale! Vero? O no?
Ti rimane sempre Eliano....
---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
Andrea.9
> Andrea!
> EVVVVVVAIIIIII !!!!!
> ora hai tempo 2 giorni per metterci alla prova
..... ma solo perchè sono arrivato al momento giusto e ho trovato un
compito non troppo difficile.
comunque ho visto che è limitativa la formula della seconda parte del
problema, ma "sposava" l'elenco.
comunque cercherò di impegnarmi a trovare un problema "degno". :-)
ciao a tutti
plinius
"plinius" <punto.l...@LEVARElibero.it> ha scritto nel messaggio
news:iq2276$rmt$1...@dont-email.me...
Buongiorno a tutti, nottambuli e diurni... (che brutta qualifica, però!)
Accorcio anche la seconda (quella di col. D) prima che si svegli r...
=SE(A2=A3;D3;"$B$"&RIF.RIGA())
(lunga 30 invece di 31)
Ciao,
E.
r
la fortuna è parte predominante del quizzone ... e come ogni gioco ce
ne vuole tanta per vincere!
aspettiamo ...
ciao
r
r
> "plinius" <punto.linea...@LEVARElibero.it> ha scritto nel messaggionews:iq2276$rmt$1...@dont-email.me...
>
>
>
>
>
>
>
>
>
>
>
> > "r" <robb....@gmail.com> ha scritto nel messaggio
buona E!
per le risposte 1 e 2 io però propongo:
="$B$"&CONFRONTA($A2;$A:$A;COLONNE($A:A)-1)
da trascinare a destra così risparmio una formula
no eh? ... o si? :-)
... dai che nel caso è accorciabile :-)
r
giovanna
> QUIZ 6
> .............
Per un puro caso posso leggere a quest'ora (normalmente sono ancora al
lavoro).
Giuro di:
- aver aperto solo il post di presentazione del quiz; (anzi, io mi vedo
un doppione, quindi ho aperto solo i primi 2 targati "Scossa" :-)
- ho aperto poi gli ultimi due: prima Mauro, che sapevo non
partecipare, poi Paolone, che mi ha rivelato che ... tutti i giochi
erano ormai fatti!
Per mia curiosità mi riprometto di (cercare di) risolvere questo
pomeriggio.
ciao campioni :-)
--
ciao
giovanna
.......................
www.riolab.org
.........................
Scossa
o, volendola rendere assoluta:
=CONTA.VALORI(Stru $B:$B)
Bye!
scossa
r
> Eh noooo! Questa ve la siete fatta tutta di notte! Peggio dei navy seals.
> Bisogna aggiornare il regolamento!! Quiz solo dalle 9 di mattina :-)
chi dorme non prende ... punti :-)
r
r
cavoli bella!
forse se ne era già parlato ... ma giuro non ricordavo l'intersezione!
bravo!
r
plinius
"r" <robb...@gmail.com> ha scritto nel messaggio
news:7d0e0360-085a-4748...@28g2000yqu.googlegroups.com...
***********************+
L'intersezione è affascinante, ma invano ho cercato un modo per renderla
anche utile.
Niente!! Non ci sono riuscito! :-(
In questo caso "sembra" utile, ma solo perché ci siamo autoimposto l'obbligo
di usare il nome "Stru". Diversamente avremmo usato $B:$B o, se opportuno
$B$2:$B$39
Prossimo quizzone: trovare un impiego utile dell'operatore "spazio" eh eh
:-))
Ciao,
E.
Scossa
> Prossimo quizzone: trovare un impiego utile dell'operatore "spazio" eh eh
> :-))
Io lo trovo utilissimo con dati ricavati da query e in abbinamento a
SUBTOTALE().
Prendi un foglio excel in cui nella riga 3 hai le intestazioni di
colonna, dalla riga 4 i dati, importati da un mdb access via query
(Dati -> importa dati esterni -> importa dati selezioni
l'archivio .mdb e selezioni la query), nelle proprietà di importazione
dai il nome <dati> all'intervallo e togli il flag da "includi nomi di
campo".
Ora abbiamo il nostro intervallo per esempio $A$4:$M$5000 e si chiama
Dati.
La colonna A contiene dei codici variamente ripetuti;
La colonna B contiene dei codici univoci;
La colonna F contiene degli importi;
In B2:: =subtotale(103; Dati B:B) copi ed incolli in F2 e ti trovi
in F2:: =subtotale(109; Dati F:F)
Aggiorni la query e le righe doventano 6000 .... e tu non devi fare
niente :-)
Vuoi creare un nome per i dati in colonna F?
nome:: DatiF
Riferito a: =Dati $F:$F
etc....
Bye!
scossa
plinius
"Scossa" <scos...@gmail.com> ha scritto nel messaggio
news:009fde13-05e4-4d6d...@w10g2000yqa.googlegroups.com...
etc....
Bye!
scossa
*******************
Sì, d'accordo, ma niente di diverso che dire
=INDICE(dati;;n)
dove n è la colonna che ti interessa.
Oppure anche usare direttamente $F:$F
In ogni caso hai ragione, talvolta può essere utile a risparmiare qualche
carattere... niente di più, secondo me!
Ciao,
E.
Scossa
> "Scossa" <scossa...@gmail.com> ha scritto nel messaggionews:009fde13-05e4-4d6d...@w10g2000yqa.googlegroups.com...
F2:: =Subtotale(109; $F:$F) ????? riferimento circolare!
F2:: Subtotale(109;INDICE(dati;;6)) ? perchè usare due funzioni
anzichè una sola?
A parte starti antipatico, l'operatore spazio che ti ha fatto?
confessa! :-)
Bye!
scossa
plinius
F2:: =Subtotale(109; $F:$F) ????? riferimento circolare!
F2:: Subtotale(109;INDICE(dati;;6)) ? perchè usare due funzioni
anzichè una sola?
A parte starti antipatico, l'operatore spazio che ti ha fatto?
confessa! :-)
Bye!
scossa
********************************
Con il sovraffollamento del pianeta bisogna adattarsi ad usarne sempre meno,
no?
:-))
Scherzi a parte, non mi è mai capitato di trovarlo utile... sarà anche per
il tipo di cose che mi capita di fare!
In VBA è diverso: lì hai a che fare con range che devi trattare e a priori
non li conosci: da qui l'esigenza di testarli, ma sul foglio?
In effetti quando non succede che ti ritrovi con dei nomi più o meno
volontariamente creati e hai il foglio davanti, che motivo avresti per
intersecare due range?
Ciao,
E.
Scossa
>
> Scherzi a parte, non mi è mai capitato di trovarlo utile... sarà anche per
> il tipo di cose che mi capita di fare!
Ecco, probabilmente per questo.
> In effetti quando non succede che ti ritrovi con dei nomi più o meno
> volontariamente creati e hai il foglio davanti, che motivo avresti per
> intersecare due range?
Bhe' il caso dell'intervallo dati importati è un esempio concreto con
cui ho a che fare tutti i giorni insieme a subtotale ed utilizzare
Subtotale(109; Dati $F:$F) non a prezzo! per tutto il resto c'è
Mas..... :-))
Bye!
Scossa