[Risolto] Ricerca secondo tre criteri VERS2

[Francesco90]

Sempre io con il file che mi ossessiona da giorni.
Andando nel foglio mensile_stampa.
In cella B1 si sceglie il mese di interesse.
In cella B2 si sceglie l'attività di interesse. (C'è il foglio legenda_servizi che chiarisce gli acronimi usati nel file)
Quindi giorno per giorno si avranno i quattro nomi(saranno sempre e solo 4) che comporranno il servizio del giorno stesso.
Come si può avere una soluzione a ciò

file_per_venu_da_zero.rar

(82.55 KiB) Scaricato 126 volte

edit1
Da stamattina che provo ma mi sa che è troppo difficile... Si potrà mai fare sta cosa??

edit2:
Cerco di spiegarmi meglio. Nel foglio "mensile_stampa" vorrei che, selezionando il mese di interesse e il servizio di interesse si compili per ogni giorno i nominativi di chi è interessato da quel servizio. VI allego un altro esempio in cui ho compilato a mano i risultati che vorrei.
ps: ho eliminato tutti i mesi dell'anno da aprile in poi poiché il file, anche zippato, occupava troppo.

esempio_per_forum_quadris.rar

(147.43 KiB) Scaricato 127 volte

[gioh66]

Buonasera, un sistema, un po' macchinoso, ci sarebbe: aggiungi una colonna, io l'ho messa a sinistra per cui è A, e in A6 metti =$A$6 e tiri in basso per quattro celle, poi in A10 metti =$A$10 e tiri per quattro celle e così via. Questo si rende necessario perchè le celle unite non vanno d'accordo con le formule (ne con le macro). Una volta completato puoi nascondere la colonna e in C6 metti

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&"!A5:A301");PICCOLO(SE(INDIRETTO(SINISTRA($D$1;3)&"!R5C"&CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&"!D4:AH4");0)+3&":R301C"&CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&"!D4:AH4");0)+3;0)=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA(A1);4)=0;4;RESTO(RIF.RIGA(A1);4))));"")

la confermi matriciale con ctrl+maiuscolo+invio e la copi in basso. In D6 metti

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&"!B5:B301");PICCOLO(SE(INDIRETTO(SINISTRA($D$1;3)&"!R5C"&CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&"!D4:AH4");0)+3&":R301C"&CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&"!D4:AH4");0)+3;0)=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA(A1);4)=0;4;RESTO(RIF.RIGA(A1);4))));"

")
e in E6

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&"!C5:C301");PICCOLO(SE(INDIRETTO(SINISTRA($D$1;3)&"!R5C"&CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&"!D4:AH4");0)+3&":R301C"&CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&"!D4:AH4");0)+3;0)=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA(A1);4)=0;4;RESTO(RIF.RIGA(A1);4))));"")

anche queste da confermare come matriciali e copiare in basso.
Inoltre per far funzionare queste formule devi modificare la sintassi delle formule e passarle da Calc A1 a Excel A1.
Per farlo vai in strumenti>opzioni>libreofficecalc>formula> e a sinistra in alto fai il cambio.

[Francesco90]

Ancora grazie... spero non mi mandi a quel paese.... mi hai risolto il problema e potrei quindi a questo punto fare un foglio per ogni mese. Peò sarebbe più comodo poter scegliere, nel menu a tendina anche il mese. in modo da avere in quel foglio e in quella sola tabella, tutto. Anche perché ho bisogno che la colonna B quella con tutti i giorni del mese sia in armonia con tutto il file. Deve potersi autocompilarsi in base all'anno che imposto

[gioh66]

Allora, se vuoi automatizzare il foglio, in B6 metti

Codice: Seleziona tutto

=DATA(anno_corrente.$A$1;CERCA.VERT($D$1;anno_corrente.$D$2:$E$13;2;0);1)

cambiando il mese in D1 si aggiornano le date della colonna A e B
Per fare questo ho aggiunto una tabellina nel foglio anno_corrente con il nome e numero dei mesi.
Inoltre studiandoci un po' ho trovato una formula che non necessita di modificare la sintassi della formula, per cui in C6 diventa

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".A5:A301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"") 

in D6

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".B5:B301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"")

e in E6

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".C5:C301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"")

sempre matriciali, da confermare con ctrl+maiuscolo+invio e da copiare e incollare in basso.

[Francesco90]

Ciao, ti ringrazio ancora per il tempo che mi stai dedicando...
Mi sono dato come esercizio quello di modificare il menu a tendina in D2 al foglio mensile_stampa. Volevo metterci, invece che gli acronimi, i nomi per steso delle attività che son nella tabella al foglio legenda_servizi.
Alla formula che mi hai proposto ho sostituito questo

Codice: Seleziona tutto

$D$2

con questo:

Codice: Seleziona tutto

CERCA.VERT($D$2 ; legenda_servizi!A5:B23 ; 2 ; 0)

Ovviamente era troppo facile risolverla così...
Saluti

esempio_per_forum_quadris (2bis).rar

(151.75 KiB) Scaricato 121 volte

[gioh66]

Caio non hai sbagliato di tanto, ti sei solo dimenticato gli assoluti nel cerca verticale (i $ prima del riferimento di riga e colonna)
CERCA.VERT($D$2 ;legenda_servizi.$A$2:$B$20;2;0)

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".A5:A301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=CERCA.VERT($D$2 ;legenda_servizi.$A$2:$B$20;2;0);RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"")

[Francesco90]

Capito, grazie
Più tardi me la studio bene sta formula che è bella corposa. Siamo quasi alla fine di quest'opera megagalattica xD Diciamo che manca il dover bloccare tutto in modo che le scimmiette che useranno il file non lo facciano esplodere. e poi due fogli andranno stampati, quindi devo renderli "carini" e impaginarli per bene.
COmplimenti sei proprio bravo

[gioh66]

Grazie dei complimenti...anche se per come la vedo io devo ancora imparare molto ancora!

[Francesco90]

Allora, se vuoi automatizzare il foglio, in B6 metti

Codice: Seleziona tutto

=DATA(anno_corrente.$A$1;CERCA.VERT($D$1;anno_corrente.$D$2:$E$13;2;0);1)

cambiando il mese in D1 si aggiornano le date della colonna A e B
Per fare questo ho aggiunto una tabellina nel foglio anno_corrente con il nome e numero dei mesi.
Inoltre studiandoci un po' ho trovato una formula che non necessita di modificare la sintassi della formula, per cui in C6 diventa

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".A5:A301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"") 

in D6

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".B5:B301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"")

e in E6

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".C5:C301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"")

sempre matriciali, da confermare con ctrl+maiuscolo+invio e da copiare e incollare in basso.

Vorrei modificare di poco questa formula ma non sto riuscendo... Nel file queste tre formule andavano a cercare i 4 nominativi delle persone che eseguivano un certo tipo di servizio scelto sul menù a tendina. Il problema è che è sorta l'esigenza di non limitarsi più a 4 nominativi ma a 12. Vorrei quindi ampliare la tabella fino a 20... Il problema è che non so quale parte della formula modificare. Allego screenshot così ci capiamo meglio di cosa sto parlando

[Francesco90]

Allora, se vuoi automatizzare il foglio, in B6 metti

Codice: Seleziona tutto

=DATA(anno_corrente.$A$1;CERCA.VERT($D$1;anno_corrente.$D$2:$E$13;2;0);1)

cambiando il mese in D1 si aggiornano le date della colonna A e B
Per fare questo ho aggiunto una tabellina nel foglio anno_corrente con il nome e numero dei mesi.
Inoltre studiandoci un po' ho trovato una formula che non necessita di modificare la sintassi della formula, per cui in C6 diventa

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".A5:A301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"") 

in D6

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".B5:B301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"")

e in E6

Codice: Seleziona tutto

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$1;3)&".C5:C301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$1;3)&".C4");1;CONFRONTA($A6;INDIRETTO(SINISTRA($D$1;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$1;3)&".A5:A301")))=$D$2;RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);4)=0;4;RESTO(RIF.RIGA($A1);4))));"")

sempre matriciali, da confermare con ctrl+maiuscolo+invio e da copiare e incollare in basso.

Vorrei modificare di poco questa formula ma non sto riuscendo... Nel file queste tre formule andavano a cercare i 4 nominativi delle persone che eseguivano un certo tipo di servizio scelto sul menù a tendina. Il problema è che è sorta l'esigenza di non limitarsi più a 4 nominativi ma a 12. Vorrei quindi ampliare la tabella fino a 20... Il problema è che non so quale parte della formula modificare. Allego screenshot così ci capiamo meglio di cosa sto parlando

file_servizi_300_posti_mens_20_posti - Copia.part02.rar

parte2

(248.38 KiB) Scaricato 116 volte

Allego il file...
andando su uno qualsiasi dei mesi si può assegnare un servizio alla persona cliccando in una cella.
Andando poi sul foglio mensile_stampa si sceglie il mese e il servizio e dovrebbero comparire le persone impegnate in quel servizio.
Allego il file in due parti vista la dimensione di 2.5mb scompattato.

file_servizi_300_posti_mens_20_posti - Copia.part01.rar

parte1

(250 KiB) Scaricato 112 volte

file_servizi_300_posti_mens_20_posti - Copia.part02.rar

parte2

(248.38 KiB) Scaricato 116 volte

[gioh66]

Ciao modifica le formule così
in C13

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$8;3)&".A5:A301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$8;3)&".C4");1;CONFRONTA($A13;INDIRETTO(SINISTRA($D$8;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$8;3)&".A5:A301")))=CERCA.VERT( $D$9 ; legenda_servizi!$A$2:$B$20 ; 2 ; 0 );RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);20)=0;20;RESTO(RIF.RIGA($A1);20))));"")

in D13

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$8;3)&".B5:B301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$8;3)&".C4");1;CONFRONTA($A13;INDIRETTO(SINISTRA($D$8;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$8;3)&".A5:A301")))=CERCA.VERT( $D$9 ; legenda_servizi!$A$2:$B$20 ; 2 ; 0 );RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);20)=0;20;RESTO(RIF.RIGA($A1);20))));"")

e in E13

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$8;3)&".A5:A301");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$8;3)&".C4");1;CONFRONTA($A13;INDIRETTO(SINISTRA($D$8;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$8;3)&".A5:A301")))=CERCA.VERT( $D$9 ; legenda_servizi!$A$2:$B$20 ; 2 ; 0 );RIF.RIGA($A$5:$A$301)-4);SE(RESTO(RIF.RIGA($A1);20)=0;20;RESTO(RIF.RIGA($A1);20))));"")

In rosso la parte da modificare se aumenti il numero degli addetti.
Inoltre avevi sbagliato il RIF.RIGA($A$5:$A$301)-4), scrivendo 333 invece di 301

[Francesco90]

Ti ringrazio infinitamente... Ti devo chiedere un'altra cortesia... Non mi piace avere in un file che uso o che faccio usare delle formule che non comprendo appieno. Vorrei quindi capirla ma non ci ho capito niente... Potresti se hai la pazienza, dirmi cosa fa passo per passo? Inoltre il fatto di confermarla matriciale, in cosa la differenzia se non l'avessi confermata? Grazie mille

[gioh66]

Ciao, provo a spiegartela. Allora la formula è una formula di tipo matriciale, perchè tu hai bisogno di una formula che ti indichi chi nelle colonne fa un determinato servizio, ad esempio "s1". Se tu usassi formule normali come CERCA.VERT o INDICE e CONFRONTA otterresti solo il primo risultato, per cui è necessario usare la funzione INDICE con PICCOLO SE.
Partiamo dalla funzione PICCOLO. Questa funzione si compone di due argomenti una sono i dati e l'altro è il k che indica che valore più piccolo vogliamo conoscere di un insieme: ad esempio se vogliamo sapere qual'è il primo mettiamo come k 1, per il secondo 2 e così via. Usando come k una funzione come RIF.RIGA possiamo creare una classifica crescente dei dati in nostro possesso.
Nella formula che ti ho indicato il PICCOLO tramite la condizione SE mi ricava quali sono le righe del range che contengono la condizione "s1". Questa condizione rappresenta l'argomento dati della funzione.
Come k ho usato la funzione RESTO(RIF.RIGA($A1);20), in modo che una volta arrivato a 20, il conteggio ricominci da 1.
Poi per rendere dinamico il range su cui la funzione SE fa il confronto ho usato la funzione SCARTO la quale a partire da un rifermento spostato (INDIRETTO(SINISTRA($D$8;3)&".C4")) cerca da quale riga (1) e colonna (ricavata con la formula CONFRONTA($A13;INDIRETTO(SINISTRA($D$8;3)&".D4:AH4");0)) e per quale altezza con (CONTA.VALORI(INDIRETTO(SINISTRA($D$8;3)&".A5:A301"))).
In pratica al variare della data cerca su quale range si trova "s1": il primo del mese è D5:D301, il 2 è E5:E301 e così via man mano che copi la formula in basso.
Inoltre per rendere adattabile la formula ai vari fogli ho usato la funzione INDIRETTO dove con la funzione SINISTRA ho ricavato il nome del foglio che ho legato ai vari range.
Chiedo venia se non dovessi essere stato sufficientemente esaustivo ma l'argomento è complesso.

[Francesco90]

Ciao modifica le formule così
in C13

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$8;3)&".A5:A600");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$8;3)&".C4");1;CONFRONTA($A13;INDIRETTO(SINISTRA($D$8;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$8;3)&".A5:A600")))=CERCA.VERT( $D$9 ; legenda_serv!$A$2:$B$20 ; 2 ; 0 );RIF.RIGA($A$5:$A$600)-4);SE(RESTO(RIF.RIGA($A1);20)=0;20;RESTO(RIF.RIGA($A1);20))));"")

in D13
=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$8;3)&".B5:B600");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$8;3)&".C4");1;CONFRONTA($A13;INDIRETTO(SINISTRA($D$8;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$8;3)&".A5:A600")))=CERCA.VERT( $D$9 ; legenda_serv!$A$2:$B$20 ; 2 ; 0 );RIF.RIGA($A$5:$A$600)-4);SE(RESTO(RIF.RIGA($A1);20)=0;20;RESTO(RIF.RIGA($A1);20))));"")

e in E13

=SE.ERRORE(INDICE(INDIRETTO(SINISTRA($D$8;3)&".C5:C600");PICCOLO(SE(SCARTO(INDIRETTO(SINISTRA($D$8;3)&".C4");1;CONFRONTA($A13;INDIRETTO(SINISTRA($D$8;3)&".D4:AH4");0);CONTA.VALORI(INDIRETTO(SINISTRA($D$8;3)&".A5:A600")))=CERCA.VERT( $D$9 ; legenda_serv!$A$2:$B$20 ; 2 ; 0 );RIF.RIGA($A$5:$A$600)-4);SE(RESTO(RIF.RIGA($A1);20)=0;20;RESTO(RIF.RIGA($A1);20))));"")

In rosso la parte da modificare se aumenti il numero degli addetti.
Inoltre avevi sbagliato il RIF.RIGA($A$5:$A$301)-4), scrivendo 333 invece di 301

gioh66 sei sicuro che funziona? a me non sta funzionando...
edit1: che strano... alla fine è tutta uguale non cambia nulla, l'ho controllata. cambia solo la parte finale...
edit3: ok corretto , ho evidenziato il quotato in colorato per una mia futura consulatione del topic