[Risolto]Matrice di dati e righe corrispondenti

Discussioni sull'applicazione per i fogli di calcolo
Rispondi
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

[Risolto]Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

Ciao a tutti,
ho un problema su un foglio di calcolo e mi ci sto sbattendo letteralmente la testa!!!
In allegato vi mando il foglio di calcolo dove ho una serie di dati che formano una matrice 3x19 cioè tre colonne A,B,C e 19 righe.
Il mio quesito è il seguente:
Vorrei colorare le celle che contengono un determinato numero (in questo caso il numero 64) e fino a qui ci sono arrivato grazie alla "formattazione condizionata".
Poi vorrei fare la differenza tra il numero di righe che contengono il numero 64 e il risultato metterlo nella colonna E.

N.B. la formula dovrebbe essere in grado di eseguire la stessa cosa anche se il numero 64 dovesse cambiare posizione nella matrice: anzichè trovarsi nelle celle C1, B9, C14, A19 come nel foglio allegato potrebbe trovarsi in qualsiasi cella, esempio (C3, A6, A9, B13).

E' un quesito un pò difficile, lo so....grazie mille!!!
Allegati
Prova.ods
(10.59 KiB) Scaricato 78 volte
Ultima modifica di paoletto75 il lunedì 2 gennaio 2023, 12:58, modificato 2 volte in totale.
OpenOffice 4.1.7
patel
Volontario attivo
Volontario attivo
Messaggi: 4019
Iscritto il: venerdì 30 aprile 2010, 8:04
Località: Livorno

Re: Matrice di dati e righe corrispondenti

Messaggio da patel »

temo occorra una macro, per es. questa

Codice: Seleziona tutto

Sub Cercaperrighe
  Dim oSheet, oRange, oFoundCell, oDescriptor, oFound
  Doc = ThisComponent
  oSheet = Doc.Sheets(0)
  tofind = 64
  c = oSheet.createCursor
  c.gotoEndOfUsedArea(false)
  LastRow = c.RangeAddress.EndRow
  for r = 0 to LastRow
    for col = 0 to 2 ' colonna C = 2
      if oSheet.GetCellbyPosition(col,r).value = tofind then
         oSheet.GetCellbyPosition(4,rr).value= r+1
         rr=rr+1
      end if
    next
   next
End Sub
-------------------
Libre Office 7.5.3.2 su Windows 11
allega un file di esempio, guadagnerai tempo tu e lo farai risparmiare a chi ti aiuta
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1723
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Matrice di dati e righe corrispondenti

Messaggio da gioh66 »

Buondì! Non capisco la logica delle operazioni che vuoi fare, ma per sapere quante volte è presente un dato numero nella tabella puoi usare il CONTA.SE

Codice: Seleziona tutto

=CONTA.SE(A1:C19;64)
Per sapere poi in quali righe si trova il numero cercato, oltre alla soluzione di patel, con le formule puoi fare così: in una cella metti

Codice: Seleziona tutto

=SE(VAL.ERRORE(PICCOLO(SE($A$1:$C$19=64;RIF.RIGA($A$1:$A$19));RIF.RIGA($A1)));"";PICCOLO(SE($A$1:$C$19=64;RIF.RIGA($A$1:$A$19));RIF.RIGA($A1)))
La confermi con ctrl+maiuscolo+invio e non col solo invio, dato che si tratta di una formula matriciale e la copi in basso fin dove serve.
Allegati
calcolo.ods
(13.79 KiB) Scaricato 73 volte
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
Avatar utente
lucky63
Volontario assiduo
Volontario assiduo
Messaggi: 2992
Iscritto il: martedì 18 maggio 2010, 17:01

Re: Matrice di dati e righe corrispondenti

Messaggio da lucky63 »

.
Possibile soluzione usando due colonne di appoggio (che possono essere nascoste) e formule normali.
01.png
Allegati
Prova-01.ods
(14.18 KiB) Scaricato 73 volte
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

Grazie mille a tutti!!!
Siete stati molto chiari e esaistivi e anche tempestivi!! :P :D

Grazie davvero!!
Un saluto a tutti.
OpenOffice 4.1.7
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

lucky63 ha scritto:.
Possibile soluzione usando due colonne di appoggio (che possono essere nascoste) e formule normali.
L’allegato 01.png non è più disponibile
Ho usato il tuo metodo e funziona alla grande!
Volevo porti un ultimo quesito...se volessi fare la stessa cosa, ma anzichè con il numero 64, con i numeri da 1 a 10, cosa dovrei aggiungere alle tue formule?
Mi spiego meglio:
Prendendo come riferimento un altro allegato (simile a quello che vi ho già inviato), vorrei fare la stessa cosa che ho fatto per il numero 64, però considerando i numeri che vanno da 1 a 10; cioè per ogni riga della mia matrice riportare (su colonne diverse possibilmente) il numero della riga (se ci fossero i numeri 1,2,3,4,5,6,7,8,9,10 anzichè solo il 64).
Vi mando un secondo allegato per spiegarmi meglio...

Grazie!!
Allegati
Prova open office.ods
(9.11 KiB) Scaricato 60 volte
OpenOffice 4.1.7
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1723
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Matrice di dati e righe corrispondenti

Messaggio da gioh66 »

Ciao scimiottando quanto fatto da @lucky63, prova così: in F2 da copiare e incollare a destra e in basso metti la seguente formula matriciale

Codice: Seleziona tutto

=SE(VAL.ERRORE(PICCOLO(SE($A$2:$C$10=F$1;RIF.RIGA($A$2:$A$10)-1);RIF.RIGA($A1)));"";PICCOLO(SE($A$2:$C$10=F$1;RIF.RIGA($A$2:$A$10)-1);RIF.RIGA($A1)))
In Q2

Codice: Seleziona tutto

=SE(VAL.ERRORE(PICCOLO($F$2:$O$21;RIF.RIGA(A1)));"";PICCOLO($F$2:$O$21;RIF.RIGA(A1)))
e in R2

Codice: Seleziona tutto

=SE(O(Q2="";Q3="");"";Q3-Q2)
Ps.: le formule matriciali vanno copiate nella cella (o nella barra della formula) e confermate con la combinazione di tasti ctrl+maiuscolo+invio e non col solo invio
Allegati
Prova open office(bis).ods
(12.01 KiB) Scaricato 67 volte
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

gioh66 ha scritto:Ciao scimiottando quanto fatto da @lucky63, prova così: in F2 da copiare e incollare a destra e in basso metti la seguente formula matriciale

Codice: Seleziona tutto

=SE(VAL.ERRORE(PICCOLO(SE($A$2:$C$10=F$1;RIF.RIGA($A$2:$A$10)-1);RIF.RIGA($A1)));"";PICCOLO(SE($A$2:$C$10=F$1;RIF.RIGA($A$2:$A$10)-1);RIF.RIGA($A1)))
In Q2

Codice: Seleziona tutto

=SE(VAL.ERRORE(PICCOLO($F$2:$O$21;RIF.RIGA(A1)));"";PICCOLO($F$2:$O$21;RIF.RIGA(A1)))
e in R2

Codice: Seleziona tutto

=SE(O(Q2="";Q3="");"";Q3-Q2)
Ps.: le formule matriciali vanno copiate nella cella (o nella barra della formula) e confermate con la combinazione di tasti ctrl+maiuscolo+invio e non col solo invio
Ho provato un attimo a studiare un pò le formule che mi hai mandato, ma non ho capito un granchè.
Mi chiedo, se volessi fare la stessa cosa con una matrice simile (vedi allegato), i numeri in questione però sono 64, 56, 66, 33, 97, 99, 12, 9, 14 come dovrei impostare le formule??

Grazie ancora.
Allegati
Prova open office(bis).ods
(12.32 KiB) Scaricato 56 volte
OpenOffice 4.1.7
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1723
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Matrice di dati e righe corrispondenti

Messaggio da gioh66 »

Buondì! Allora le prime due formule si basano sulla funzione PICCOLO, la quale trova in una matrice di dati il valore che corrisponde all'argomento K che è un numero che rappresenta il 1° il 2
°, il 3° ecc...valore più piccolo.
Nel tuo file con la prima funzione PICCOLO abbinata al SE creo una formula (matriciale da confermare con la combinazione di tasti ctrl+maiuscolo+invio) che mi indica qual'è la posizione di riga che occupano i valori da F1 a O1 all'interno della tabella A2:C10.
Ad esempio per l'1 (in F1) quando la condizione $A$2:$C$10=F$1 è vera mi devi dire qual'è la riga dell'intervallo RIF.RIGA($A$2:$A$10)-1 che corrisponde al primo valore trovato indicato da RIF.RIGA($A1) . Copiando in la formula in basso quel RIF.RIGA($A1) diventa RIF.RIGA($A2), RIF.RIGA($A3) ecc... e indicherà di volta in volta automaticamente qual'è la 1°, 2° ecc...riga che corrisponde al criterio di ricerca (quel $A$2:$C$10=F$1).
Le righe sono indicate con la formula RIF.RIGA($A$2:$A$10)-1 cioè è come se ci fosse scritto 1,2,3,4,5,6,7,8,9. Il -1 alla fine serve ad allineare il conteggio delle righe in quanto la tua tabella (A2:C10) inzia dalla riga 2. Se come nell'esempio che ti riallego con i numeri che hai indicato inizia dalla posizione A26 per riallineare il conteggio delle righe diventa così: RIF.RIGA($A$26:$A$34)-25.
La seconda formula PICCOLO (questa è normale e funziona col solo invio) ,quella in Q2, mette in ordine crescente i valori della tabella F2:O21 ricavati con la precedente formula
L'ultima in R2 fa la sottrazione tra le posizioni.
Spero di essere stato sufficientemente chiaro :mrgreen:
Allegati
Prova open office (bis1).ods
(14.66 KiB) Scaricato 66 volte
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

gioh66 ha scritto:Buondì! Allora le prime due formule si basano sulla funzione PICCOLO, la quale trova in una matrice di dati il valore che corrisponde all'argomento K che è un numero che rappresenta il 1° il 2
°, il 3° ecc...valore più piccolo.
Nel tuo file con la prima funzione PICCOLO abbinata al SE creo una formula (matriciale da confermare con la combinazione di tasti ctrl+maiuscolo+invio) che mi indica qual'è la posizione di riga che occupano i valori da F1 a O1 all'interno della tabella A2:C10.
Ad esempio per l'1 (in F1) quando la condizione $A$2:$C$10=F$1 è vera mi devi dire qual'è la riga dell'intervallo RIF.RIGA($A$2:$A$10)-1 che corrisponde al primo valore trovato indicato da RIF.RIGA($A1) . Copiando in la formula in basso quel RIF.RIGA($A1) diventa RIF.RIGA($A2), RIF.RIGA($A3) ecc... e indicherà di volta in volta automaticamente qual'è la 1°, 2° ecc...riga che corrisponde al criterio di ricerca (quel $A$2:$C$10=F$1).
Le righe sono indicate con la formula RIF.RIGA($A$2:$A$10)-1 cioè è come se ci fosse scritto 1,2,3,4,5,6,7,8,9. Il -1 alla fine serve ad allineare il conteggio delle righe in quanto la tua tabella (A2:C10) inzia dalla riga 2. Se come nell'esempio che ti riallego con i numeri che hai indicato inizia dalla posizione A26 per riallineare il conteggio delle righe diventa così: RIF.RIGA($A$26:$A$34)-25.
La seconda formula PICCOLO (questa è normale e funziona col solo invio) ,quella in Q2, mette in ordine crescente i valori della tabella F2:O21 ricavati con la precedente formula
L'ultima in R2 fa la sottrazione tra le posizioni.
Spero di essere stato sufficientemente chiaro :mrgreen:
Ciao gioh66,
innanzitutto ti vorrei ringraziare.
Poi vorrei farti i miei complimenti...davvero!
Credo grosso modo di aver capito, comunque.
Grazie ancora...grande!!
un saluto! :bravo: :super:
OpenOffice 4.1.7
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1723
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Matrice di dati e righe corrispondenti

Messaggio da gioh66 »

Bene mi fa piacere! :D :super:
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

gioh66 ha scritto:Bene mi fa piacere! :D :super:
Credevi di esserti liberato di me....invece...

Ciao a tutti.
Ho provato ad applicare le formule che mi hai mandato su altre matrici, ma non ho avuto il risultato sperato. La matrice questa volta (5x19) l'ho fatta partire dalla cella A2 e finire nella E20. Questa volta è una matrice piu' grande rispetto a quella di prima.
Quale variazione devo applicare alle formule precedenti?
Inoltre, se la mia matrice fosse ancora più grande, per esempio 5x35 e i numeri che voglio cercare fossero di piu'(18,64,57,93,70,95,20,152,182,24,66,96,.........) immagino che le formule si potrebbero adattare, ma in che modo?

Grazie e speriamo bene... :crazy:
Allegati
Open office forum esempio.ods
(11.12 KiB) Scaricato 67 volte
OpenOffice 4.1.7
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1723
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Matrice di dati e righe corrispondenti

Messaggio da gioh66 »

Ti basta aumentare i riferimenti delle tabella e del rif.riga nella prima formula.
Nel nuovo esempio la formula diventa così

=SE(VAL.ERRORE(PICCOLO(SE($A$2:$E$20=H$1;RIF.RIGA($A$2:$A$20)-1);RIF.RIGA($A1)));"";PICCOLO(SE($A$2:$E$20=H$1;RIF.RIGA($A$2:$A$20)-1);RIF.RIGA($A1)))

Poi la confermi come matriciale con ctrl+maiuscolo+invio e la copi e in colli a destra e in basso.
La seconda poi ti basta adattare il riferimento alla nuova tabella generata
Nell'esempio diventerebbe così

Codice: Seleziona tutto

=SE(VAL.ERRORE(PICCOLO($H$2:$S$20;RIF.RIGA($A1)));"";PICCOLO($H$2:$S$20;RIF.RIGA($A1)))
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

gioh66 ha scritto:Ti basta aumentare i riferimenti delle tabella e del rif.riga nella prima formula.
Nel nuovo esempio la formula diventa così

=SE(VAL.ERRORE(PICCOLO(SE($A$2:$E$20=H$1;RIF.RIGA($A$2:$A$20)-1);RIF.RIGA($A1)));"";PICCOLO(SE($A$2:$E$20=H$1;RIF.RIGA($A$2:$A$20)-1);RIF.RIGA($A1)))

Poi la confermi come matriciale con ctrl+maiuscolo+invio e la copi e in colli a destra e in basso.
La seconda poi ti basta adattare il riferimento alla nuova tabella generata
Nell'esempio diventerebbe così

Codice: Seleziona tutto

=SE(VAL.ERRORE(PICCOLO($H$2:$S$20;RIF.RIGA($A1)));"";PICCOLO($H$2:$S$20;RIF.RIGA($A1)))
Credo di aver fatto tutto bene....tranne che confermare come matriciale, ma non ho capito il procedimento ctrl+maiuscolo+invio....ecc.....
:roll:
Allegati
xyz.ods
(13.27 KiB) Scaricato 59 volte
OpenOffice 4.1.7
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1723
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Matrice di dati e righe corrispondenti

Messaggio da gioh66 »

Buondì! Per inserire una formula matriciale apri la cella con un doppio click o premendo il tasto F2, copi la formula (o la scrivi) e poi invece di confermarla col solo invio premi assieme ctrl e maiuscolo e dai invio.
Una volta fatto per estenderla alle altre celle interessate copi e incolli la cella in cui hai già inserito la formula (non puoi trascinarla come si fa con le formule normali).
Ps.: per essere sicuro di aver inserito la formula correttamente ti basta guardare la barra della formula e noterai che all'inizio e alla fine sono comparse delle parentesi graffe.
Allegati
formula matriciale.png
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

gioh66 ha scritto:Buondì! Per inserire una formula matriciale apri la cella con un doppio click o premendo il tasto F2, copi la formula (o la scrivi) e poi invece di confermarla col solo invio premi assieme ctrl e maiuscolo e dai invio.
Una volta fatto per estenderla alle altre celle interessate copi e incolli la cella in cui hai già inserito la formula (non puoi trascinarla come si fa con le formule normali).
Ps.: per essere sicuro di aver inserito la formula correttamente ti basta guardare la barra della formula e noterai che all'inizio e alla fine sono comparse delle parentesi graffe.
Grazie gioh66
finalmente ci sono riuscito...!
Ora mi toccherà fare un bel "copia e incolla" per ogni cella della mia bella matrice mega gigante...ci metterò una giornata!!
Non c'è un modo più veloce per fare il "copia e incolla"...? :D

Grazie.
OpenOffice 4.1.7
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1723
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Matrice di dati e righe corrispondenti

Messaggio da gioh66 »

Ciao una volta che hai inserito la formula nella prima cella selezioni tutte le celle in una volta, compresa quella dove c'è già la formula e dai incolla. Non serve che lo fai una colonna alla volta...men che meno cella per cella :knock: :mrgreen:
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
paoletto75
Messaggi: 118
Iscritto il: martedì 5 maggio 2015, 23:11

Re: Matrice di dati e righe corrispondenti

Messaggio da paoletto75 »

gioh66 ha scritto:Ciao una volta che hai inserito la formula nella prima cella selezioni tutte le celle in una volta, compresa quella dove c'è già la formula e dai incolla. Non serve che lo fai una colonna alla volta...men che meno cella per cella :knock: :mrgreen:
Ovviamente...che scemo che sono!!!
Ahahahahahaha.

Grazie mille gioh66 e complimenti per le vostre competenze!!

Un saluto... :knock:
OpenOffice 4.1.7
Rispondi