[Risolto] Funzione in colonna QK da semplificare

[sandro merlino]

Ciao a tutti,
Il quesito che vi pongo è parte di un file molto più complesso che mi chiedono di affinare.
Come detto nel titolo, le celle della colonna da QK125 a QK155 contengono una funzione molto più lunga che eccede la lunghezza dei caratteri che possono essere inseriti in una cella.
Per questo troverete alla fine della funzione (;"111"). La prima parte è quella da modificare e la seconda è la parte omessa che funziona.
Il file è un "planning aziendale" e noi analizzeremo un solo reparto.
Il reparto è composto da 20 dipendenti e ogni dipendente ha un orario di inizio lavoro e il numero di ore da lavorare.
Quello che devo trovare è un sistema per fare in modo che se NON inserisco l'orario di inizio lavoro ma inserisco il numero di ore da lavorare
nelle celle da QK125 a QK155 deve essere INSERIMENTO ERRATO se Q119 è italiano oppure ERROR ENTRY se Q119 è inglese

Allego il file

Grazie 1000

Sandro

[sandro merlino]

Ciao a tutti,
Ringrazio in anticipo chi sta provando a risolvere questo mio grande grattacapo.
Ho provato a risolvere il problema anche in un altro modo ma con lo stesso risultato NON FUNZIONA
Ho sostituito la formula che funziona correttamente (che avevo omesso)nella colonna da QK125 a QK128.
La formula che era prima nelle celle da QK125 a QK128 e che ora è in QK119 come esempio nella formattazione condizionale.
Speravo che al verificarsi della formula nella formattazione condizionale la formula esistente nella cella da QK125 a QK128 venisse messa in secondo piano.
Esiste qualcosa che può aiutarmi?

Allego il file

Grazie 1000

Sandro

[unlucky83]

Riferendomi al primo allegato, adopera in QK125 la formula matriciale

Codice: Seleziona tutto   Espandi visualeStringi visuale

SE(MAX( SE(RIF.COLONNA(QM125:RZ125)<>PARI(RIF.COLONNA(QM125:RZ125));0;     SE(QM125:RZ125="";0; INDIRETTO(INDIRIZZO(RIF.RIGA(QM125);RIF.COLONNA(QM125:RZ125)-1))="") ))=1;SE($Q$119="inglese";"error entry";"inserimento errato");"111")

[sandro merlino]

Ciao unlucky83,
Ti ringrazio per la risposta ma purtroppo non mi funziona. Se inserisco questa parte di funzione tra le due che trovi in QK118 e QK120
il valore che ottengo è #VALORE! come si vede in QK128.
Ti allego il file. Mi puoi vedere dove sbaglio ?

Grazie 1000
Sandro

[unlucky83]

Non l'hai inserita come formula matriciale ma come formula semplice

[sandro merlino]

Ciao unlucky83,

Si in effetti è come dici tu. Mi faresti vedere come si fa nel file che ho allegato precedentemente. Non sono esperto.

Grazie 1000
Sandro

[unlucky83]

In basso a sinistra metti la spunta su Matrice.
C'è anche la procedura mediante combinazione di tasti, ma non la ricordo

[gioh66]

Salve la combinazione da tastiera è ctrl+shift+enter. Per applicare la formula alle alle altre celle la copi e la incolli dove ti interessa (il trascinamento con le formule matriciali non funziona)

[sandro merlino]

Ciao unlucky83,
La formula ad oggi con i controlli che ho fatto sembra perfetta quindi
Prima di mettere "risolto" mi diresti queste due cose.
Ho provata a copiarla con il semplice copia e incolla nelle altre celle. Si puo fare?
Mi spiegheresti il funzionamento di questa difficilissima funzione. Anche se non sarò mai in grado di farne una
sono curioso. Ti prego una spiegazione per utenti alle prime armi

Grazie 1000

Sandro

[unlucky83]

Spiegare la formula matriciale non è molto facile, provo a fare del mio meglio aiutandomi con la suddivisione seguente:

Codice: Seleziona tutto   Espandi visualeStringi visuale

SE(MAX(`INS_ERRATO`)=1;SE($Q$119="inglese";"error entry";"inserimento errato");"111"))

`INS_ERRATO`:= SE(`1°col`;0;`controllo`)

`1°col`:= RIF.COLONNA(QM125:RZ125)<>PARI(RIF.COLONNA(QM125:RZ125))

`controllo`:= SE(QM125:RZ125="";0; `Cella_Sx_vuota`)

`Cella_Sx_vuota`:=INDIRETTO(INDIRIZZO(RIF.RIGA(QM125);RIF.COLONNA(QM125:RZ125)-1))=""

Partiamo da `1°col`: questo confrontro restituisce VERO per tutte le colonne Dispari (QM è dispari) e FALSO per tutte le colonne Pari (QN è pari). Essendo formula matriciale ottengo più di un risultato, ovvero la sequenza "VERO FALSO VERO FALSO..." (QM QN QO QP ...)

Il confronto precedente lo impiego come condizione del SE di `INS_ERRATO`: per le colonne per cui la condizione da VERO, assegno risultato 0, per le altre invece applico il `controllo`.
In `controllo` c'è un altro SE, la cui condizione `QM125:RZ125=""`riguarda solo le colonne Pari in realtà (QN QP ...), quando sono celle vuote non c'è nessun errore quindi assegno valore 0 quando restituisce VERO, altrimenti vado a controllare il contenuto della cella a sinistra per vedere se risulta vuota.

Quindi con `Cella_Sx_vuota` , vado a controllare la cella a sinistra della colonna Pari non vuota, il confronto mi da 0 se questa è non vuota e mi da 1 se questa invece è vuota.

Ottengo con queste formule un array di soluzioni di 0 e 1 (lungo quanto QM125:RZ125), la presenza di almeno un 1 è indice di errore. Estraggo questa informazione con la funzione `MAX(`INS_ERRATO`)`, così riesco a passare da un'array di soluzioni ad un solo risultato, se Max da 0 vuol dire che non c'è errore di inserimento, mentre se restituisce 1 c'è almeno un errore.

Meglio di così non credo di riuscire a spiegarmi.

Il copia e incolla funziona, solo il trascinamento non funziona per ripetere la formula nelle celle sottostanti

[sandro merlino]

Ciao unlucky83,
La tua spiegazione era fantastica ma non ci ho capito NULLA
Mi accontento del risultato che torna. Spero per i prossimi aiuti
Ancora

Grazie 1000

Sandro

[unlucky83]

Se non ci hai capito nulla non sono certo sia stata fantastica
Sono lieto che funzioni

[sandro merlino]

Ciao unlucky83,
Scusa se ti disturbo ancora per lo stesso argomento ma mi si è verificato qualcosa di anomalo.
Nel file che allego come hai detto tu ho trasformato la funzione che mi avevi detto in matrice e da QK125 a QK155 tutto torna.
Poi devo copiare la stessa formula per le colonne:
SB125 a SB155, TS125 a TS155, VJ125 a VJ155, XA125 a XA155, YR125 a YR155 etc
ed ho seguito spero le tue istruzioni facendo un copia e incolla. Vedendo che il risultato è errato ho cercato di trasformare la matrice in formula semplice
e poi utilizzando ctrl+shift+enter come ha detto gioh66 in matrice nuovamente ma senza migliorare.
Al momento da QK125 a QK155 tutto perfetto ma avrei bisogno anche di copiare le formule verso destra come elencato sopra e poi anche in basso
da QK225 a QK255, QK325 a QK355 etc
Aggiungo che nella versione che ho io OpenOffice 4.1.1 ho copiato la matrice della cella QK125 fino a QK155 mediante trascinamento mentre
utilizzando il copia e incolla nella colonna SB mi sembra che in effetti venga mantenuta la sintassi di matrice ma senza i risultati della matrice

Allego file



Grazie 1000

Sandro

[unlucky83]

Non funziona in SB perchè SD, la prima colonna, è Pari e non dispari. Quindi devi modificare una parte della formula, ovvero
...<>PARI... deve diventare ...=Pari....

Quindi dato che
QM TU XC AAK ADS sono colonne dispari, per loro vale la formula con ...<>PARI...
SD VL YT ACB sono colonne Pari, per loro vale la formula con ...=PARI...

Tornando alla tua formula, perchè non la allegeriamo un pò? Con riferimento alla colonna QK tu hai messo

Codice: Seleziona tutto   Espandi visualeStringi visuale

=SE(E(CONTA.VUOTE(QM125:RZ125)=40;$Q$119="INGLESE");"CLOSE";SE(CONTA.VUOTE(QM125:RZ125)=40;"CHIUSO";SE(E(CONTA.SE(QM125:RZ125;".*h.*")=20;CONTA.VUOTE(QM125:RZ125)=20;$Q$119="INGLESE");"NOT PLANNED YET";SE(E(CONTA.SE(QM125:RZ125;".*h.*")=20;CONTA.VUOTE(QM125:RZ125)=20);"NON ANCORA PIANIFICATA";SE(MAX( SE(RIF.COLONNA(QM125:RZ125)<>PARI(RIF.COLONNA(QM125:RZ125));0; SE(QM125:RZ125="";0; INDIRETTO(INDIRIZZO(RIF.RIGA(QM125);RIF.COLONNA(QM125:RZ125)-1))="") ))=1;SE($Q$119="inglese";"error entry";"inserimento errato");(QUOZIENTE(SOMMA(QM125:RZ125)*24;8))&","&SE(TRONCA(RESTO(SOMMA(QM125:RZ125)*24+0,000000001;8))<8;TRONCA(RESTO(SOMMA(QM125:RZ125)*24+0,000000001;8));0)&":"&TESTO((RESTO((ARROTONDA(RESTO(SOMMA(QM125:RZ125)*24*60;8*60);0));60));"00"))))))

io modifichicherei in

Codice: Seleziona tutto   Espandi visualeStringi visuale

=SE(CONTA.VUOTE(QM125:RZ125)=40;SE($Q$119="INGLESE";"CLOSE";"CHIUSO");
SE(E(CONTA.SE(QM125:RZ125;".*h.*")=20;CONTA.VUOTE(QM125:RZ125)=20);SE($Q$119="INGLESE";"NOT PLANNED YET";"NON ANCORA PIANIFICATA");
SE(MAX(SE(RIF.COLONNA(QM125:RZ125)<>PARI(RIF.COLONNA(QM125:RZ125));0; SE(QM125:RZ125="";0; INDIRETTO(INDIRIZZO(RIF.RIGA(QM125);RIF.COLONNA(QM125:RZ125)-1))="") ))=1;SE($Q$119="inglese";"error entry";"inserimento errato");(QUOZIENTE(SOMMA(QM125:RZ125)*24;8))&","&SE(TRONCA(RESTO(SOMMA(QM125:RZ125)*24+0,000000001;8))<8;TRONCA(RESTO(SOMMA(QM125:RZ125)*24+0,000000001;8));0)&":"&TESTO((RESTO((ARROTONDA(RESTO(SOMMA(QM125:RZ125)*24*60;8*60);0));60));"00"))))


[sandro merlino]

Ciao unlucky83,
Ti ringrazio per l'alleggerimento della formula e lo metterò in pratica.
Mi spieghi quale è il ragionamento che viene fatto utilizzando la tua formula. E' qualcosa di diverso rispetto al mio
Io in genere utilizzo questo:
se(vero);altro;se(vero);altro e così via
Ti prego in futuro se ci saranno altre occasioni di rendermi le formule più semplici e lineari

Grazie 1000

Sandro

[unlucky83]

Ho preso carta e penna per capire la logica della tua formula e ho notato che ripeteva delle formule che potevano essere evitate seguendo l'ordine riportato nell'immagine

[sandro merlino]

Ciao unlucky83,

Spiegazione fantastica come sempre con diagramma a blocchi che non vedevo dai tempi della scuola.
Per me il problema è risolto
Ho imparato una cosa nuova perché la tua espressione di sotto
=SE(CONTA.VUOTE(QM125:RZ125)=40;SE($Q$119="INGLESE";"CLOSE";"CHIUSO");....
l'avrei messa in questo modo:
=se(e(CONTA.VUOTE(QM125:RZ125)=40;SE($Q$119="INGLESE");"CLOSE");.....
La mia sembra più maccheronica rispetto alla tua e con più margini di errori
Quali suggerimenti mi puoi dare

Grazie 1000

Sandro

[unlucky83]

Il suggerimento che ti posso dare è di scrivere su un foglio lo schema della "formulona", così da visualizzarla e se noti parecchi rami con sotto-formule ripetute, cercare di cambiare punto di vista per ottenere un diagramma più snello e altrettanto funzionante.

Personalmente, quando giunge il momento di scrivere la formula complessa in calc, la preparo su un editor che evidenzia le parentesi corrispondenti, per aiutarmi a capire in che punto della funzione interna mi trovo e se sto rispettando il diagramma di flusso (che ho in mente o preparato su carta), poi la copio e la incollo in una cella per vedere se funziona.

Una buona conoscenza delle formule base di calc può aiutare a trovare "percorsi" originali per il tuo algoritmo.
Per esempio, tu vedi i Se annidati come se fossero funzioni consecutive, invece la funzione SE è SE(condizione;cosa fare se VERO; cosa fare se FALSO)...in ciascuno dei tre parametri ci può essere un SE, basta che nel primo parametro restituisca una condizione, nel secondo parametro un output quando condizione=VERO etc..,

[sandro merlino]

Ciao unlucky83
Scusa se ti disturbo ancora su questo argomento ma come mi hai detto tu ho fatto le modifiche sostituendo ...<>PARI... con ...=Pari.... per le colonne
SB, VJ, YR, AAI, ADQ e con le verifiche fatte tutto dovrebbe tornare.
Sono andato avanti e per le colonne BM, EU, IC, LK, OS ho mantenuto ...<>PARI e tutto dovrebbe tornare.
Con mia grande sorpresa pensavo che per le colonne V, DD, GL, JT, NB bastasse sostituire ...<>PARI... con ...=Pari.... come ho fatto in V125 e V126 (NON MI TORNA)
Ho pensato di sostituire allora ...=PARI... con ...<>Pari..... (NON MI TORNA)
Mi diresti per cortesia lo stupido errore che sto commettendo ?

Allego il file
Grazie 1000 in anticipo

Sandro

[unlucky83]

ciao...manca l'allegato

[sandro merlino]

Ciao unlucky83,
Mi scuso per aver messo il messaggio da me scritto:martedì 9 gennaio 2018, 1:08
Il problema che poi ho capito era quello di non aver considerato le formule come matrice
Successivamente ho provato a cancellarlo ma non ci sono riuscito. Qualcuno può farlo?

Grazie 1000 per il vostro costante interesse

Sandro Merlino