Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[EXCEL] No macro - Estrarre righe in base a contenuto cella

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[EXCEL] No macro - Estrarre righe in base a contenuto cella

Postdi pl1957 » 29/09/12 08:43

Buona giornata.
Avrei bisogno di aiuto per realizzare quanto segue facendo uso solo di formule, non di macro.

In G29 ho una cella all'interno della quale inserisco un valore numerico.

Nelle righe da 31 a 60 ho questa serie di dati:
A31:A60 nome
B31:B60 cognome
C31:G60 valori ininfluenti al fine di questa elaborazione
H31:H60 importo da pagare
I31:N60 valori ininfluenti al fine di questa elaborazione

Devo estrarre le sole righe per cui si verificano entrambe le seguenti condizioni:

- nome e cognome non devono essere vuoti
- importo da pagare deve essere uguale o maggiore del valore in G29

Sono riuscito ad ottenere quanto richiesto ma con una serie di righe vuote inframezzate (quelle per le quali le due condizioni non si verificavano). Per cui avevo due righe vuote, una copiata, altre 4 vuote, un'altra copiata e così via.

Ho provato ad usare questo primo output come insieme di celle da sottoporre alla formula di cui parla Antony47 nel post http://www.pc-facile.com/forum/viewtopic.php?t=95476#p547133 adattando le coordinate, ma devo aver combinato un po' di casini perché come risultato ottenevo solo celle vuote.

Chi mi sa dare una mano?
TIA
Avatar utente
pl1957
Utente Junior
 
Post: 29
Iscritto il: 29/09/12 08:14
Località: Marche

Sponsor
 

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi CANAPONE » 29/09/12 17:04

Ciao,

che versione di Excel hai a disposizione?

Saluti
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi CANAPONE » 29/09/12 17:25

Ciao di nuovo,

se usi Excel 2007 o versioni successive

Codice: Seleziona tutto
=SE.ERRORE(INDICE($A$31:$A$60;PICCOLO(SE($H$31:$H$60>=$G$29;RIF.RIGA($A$31:$A$60)-30);RIGHE($A$1:A1)));"")


Codice: Seleziona tutto
=SE.ERRORE(INDICE($B$31:$B$60;PICCOLO(SE($H$31:$H$60>=$G$29;RIF.RIGA($A$31:$A$60)-30);RIGHE($A$1:A1)));"")


Codice: Seleziona tutto
=SE.ERRORE(INDICE($H$31:$H$60;PICCOLO(SE($H$31:$H$60>=$G$29;RIF.RIGA($A$31:$A$60)-30);RIGHE($A$1:A1)));"")


Formule che na volta copiate devono essere confermate con control+maiusc+invio.

Per le versioni precedenti SE.ERRORE non è disponibile.

Stesse formule matrice nella forma

Codice: Seleziona tutto
=SE(VAL.ERRORE(INDICE($A$31:$A$60;PICCOLO(SE($H$31:$H$60>=$G$29;RIF.RIGA($A$31:$A$60)-30);RIGHE($A$1:A1))));"";INDICE($A$31:$A$60;PICCOLO(SE($H$31:$H$60>=$G$29;RIF.RIGA($A$31:$A$60)-30);RIGHE($A$1:A1))))



In rete troverai tantissimi esempi sulle formule da confermare con control+maiusc+invio

Esempio:
http://lucafranchi.wordpress.com/2008/1 ... con-excel/


Saluti
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi CANAPONE » 29/09/12 17:38

Ciao,

se la formula deve controllare anche che nelle A e B ci siano dei nomi prima del controllo della cifra in H

Stesso tipo di formule, sempre matriciali, da copiare confermare control+maiusc+invio e trascinare in basso.

Codice: Seleziona tutto
=SE.ERRORE(INDICE($A$31:$A$60;PICCOLO(SE($A$31:$A$60<>"";SE($B$31:$B$60<>"";SE($H$31:$H$60>$G$29;RIF.RIGA($A$31:$A$60)-30)));RIGHE($A$1:A1)));"")


Basta cambiare i riferimenti dell'indice per ottenere nome cognome e cifra.

Saluti
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi pl1957 » 29/09/12 20:12

Grazie.
Ho il 2010 (in ufficio). Prossimi giorni provo e poi faccio sapere.
Avatar utente
pl1957
Utente Junior
 
Post: 29
Iscritto il: 29/09/12 08:14
Località: Marche

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi pl1957 » 02/10/12 09:20

Non funziona.

O, meglio, sono io che sono "de coccio" e non riesco a implementare le formule come dovrei. :oops: :oops: :oops:

Ho lavorato sul 2003 che ho a casa (quindi niente se.errore) e ottengo solo una matrice di 30*14 valori tutti uguali contenenti il primo nome della tabella che dovrebbe risultare.

Inoltre speravo di poter ottenere uno "schema" da poter poi estendere anche ad altre tabelle. Evidententemente mi sopravvalutavo.

A questo punto, ho ripiegato sul "piano B" originario, cioè ottenere una serie di tabelle intermedie sulle quali applicare la formula di Antony47 (http://www.pc-facile.com/forum/viewtopic.php?t=95476#p547133).

Ho però dovuto spostare le varie tabelle (una decina) in modo che iniziassero tutte da riga 1. Infatti la formula di Antony prescinde dalla colonna, ma non dalla riga iniziale.

Ho provato a modificarla come segue
Codice: Seleziona tutto
=SE(RIF.RIGA(A31)-30<=CONTA.VALORI($A$31:$A$60);INDICE(A$31:A$60;PICCOLO(SE($A$31:$A$60<>"";RIF.RIGA($A$31:$A$60);"");RIF.RIGA(A31)));"")

e anche
Codice: Seleziona tutto
=SE(RIF.RIGA(A31)-30<=CONTA.VALORI($A$31:$A$60);INDICE(A$31:A$60;PICCOLO(SE($A$31:$A$60<>"";RIF.RIGA($A$31:$A$60);"");RIF.RIGA(A31)-30));"")

ma senza successo, ottenendo l'errore #NUM nel primo caso e #RIF nel secondo.

Sulla scorta di quel proverbio che dice "Se un povero ha fame, donagli un pesce e l'avrai sfamato per un giorno, insegnagli a pescare e l'avrai sfamato per la vita", chi ha pazienza e voglia :aaah di spiegarmi come universalizzare la formula di Antony? :roll: :roll: :roll:

Ovvio che, allo stato attuale, tutto il meccanismo, con le tabelle ricollocate in riga 1, funziona alla stragrande. Ma mi piacerebbe riuscire a capire il meccanismo della faccenda.
Avatar utente
pl1957
Utente Junior
 
Post: 29
Iscritto il: 29/09/12 08:14
Località: Marche

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi CANAPONE » 02/10/12 11:05

Ciao,

stai lavorando con formule matriciali da confermare con control+maiusc+invio

Come prima impressione è tarata male la mtraice di numeri prodotta di RIF.RIGA($A$31:$A$60)

Prova cortesemente

Codice: Seleziona tutto
=SE(RIF.RIGA(A1)<=CONTA.VALORI($A$31:$A$60);INDICE(A$31:A$60;PICCOLO(SE($A$31:$A$60<>"";RIF.RIGA($A$31:$A$60)-30;"");RIF.RIGA(A1)));"")



Codice: Seleziona tutto
=SE(RIF.RIGA(A1)<=CONTA.VALORI($A$31:$A$60);INDICE(A$31:A$60;PICCOLO(SE($A$31:$A$60<>"";RIF.RIGA($A$1:$A$30);"");RIF.RIGA(A1)));"")


Ripeto sono formule da confermare con control+miausc+invio.

Appena trovo il tempo provo a spiegarne il funzionamento: sempre che vadano bene.


Saluti
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi CANAPONE » 02/10/12 14:58

Ciao,

puoi considerare la prima parte

SE(RIF.RIGA(A1)<=CONTA.VALORI($A$31:$A$60)…

come l’interruttore della formula: la formula si attiva finchè il valore prodotto da rif.riga è inferiore od uguale al conta valori.

RIF.RIGA(A1) copiato verso il basso produce una serie continua di numeri: 1,2,3,4,5…

Quindi se il conta.valori restituisce 5, copiando la formula verso il basso, quando rif.riga diventa 6 -cioè rif.riga(a6) - la formula restituisce la cella vuota.

Su Excel 2007 e versioni successive è più pratico usare SE.ERRORE.


Il processore vero della formula è il secondo segmento


INDICE(A$31:A$60;PICCOLO(SE($A$31:$A$60<>"";RIF.RIGA($A$31:$A$60)-30;"");RIF.RIGA(A1)))


INDICE(A$31:A$60;….

Ha bisogno di un secondo argomento che indichi quale dei numeri o stringhe fra A31:A60 voglio recuperare
Il secondo argomento è un numero che indica una posizione.


INDICE(A$31:A$60;2) avrebbe come risultato il contenuto della seconda cella (A32)

INDICE(A$31:A$60;5) avrebbe come risultato il contenuto della quinta cella (A35)


Si tratta di fare una selezione fra posizioni, le 30 posizioni (1-2-3-4….) di A31:A60

Le trenta posizioni fra le quali faremo la selezione sono prodotte da

RIF.RIGA(A$1:A$30)

Questo segmento lo puoi trovare scritto in molti modi

RIF.RIGA(A$31:A$60)-30 , che, forse, nel tuo caso sarebbe stato il modo più pratico.

Ancora un esempio: da RIF.RIGA(X$1:X$30) otterresti esattamente la stessa matrice (grazie al control+maiusc+invio)

{1;2;3;……..30}

Le 30 posizioni: quali sono quelle valide?

Entra in gioco

SE($A$31:$A$60<>""

Che la formula trasforma in un’altra matrice del tipo

{VERO;FALSO;FALSO;VERO;……..VERO}

VERO se trova un valore, FALSO per la cella vuota.

Questa matrice moltiplicata riga per riga con la prima matrice restituisce la matrice finale sulla quale va a pescare PICCOLO

{“”;2;3;””;5;6;””;””;””;””;11…….;””;29;””)

L’ultimo rif.riga della formula insieme a PICCOLO seleziona la prima posizione valida (2 nell’esempio), poi la seconda (3) e così via finchè parallelamente il conta.valori disattiva la ricerca fatta da PICCOLO(SE dentro l'INDICE


Spero sia d'aiuto
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi pl1957 » 02/10/12 15:44

Grazie per la disponibilità.
Mi sa che dovrò pagare una cena a qualcuno :lol: :lol:

Mi ha mandato fuori di testa quel riferimento alla cella A1 così com'è, perché è fuori dall'intervallo... Ma la tua spiegazione è stata chiarissima!


---- Edit --- corretto errore di battitura
Ultima modifica di pl1957 su 02/10/12 15:55, modificato 1 volte in totale.
Avatar utente
pl1957
Utente Junior
 
Post: 29
Iscritto il: 29/09/12 08:14
Località: Marche

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi CANAPONE » 02/10/12 15:50

Ciao a te del riscontro,

mi sono dimenticato una cosa - banale - che forse aiuta a digerire meglio queste formule.

Se fai fare la selezione a GRANDE -al posto di PICCOLO- riproduci lo stesso elenco partendo dalla posizione più in basso.

Alla prossima
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi pl1957 » 02/10/12 16:01

OK, rigrazie.
La mia formazione su Excel è moltissimo da autodidatta, molto internet, e un vecchio "for dummies" della versione 97 (!!!) che ha ormai le pagine distrutte.
Avatar utente
pl1957
Utente Junior
 
Post: 29
Iscritto il: 29/09/12 08:14
Località: Marche

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi Anthony47 » 03/10/12 01:38

Innanzitutto complimenti a canapone per la sua didattica.
Per adattare la formula presentata su viewtopic.php?t=95476#p547133 forse il seguente approccio potrebbe essere piu' elementare:
Se la prima colonna della tua tabella e' in, mettiamo, D20:D50 allora la formula puo' diventare
Codice: Seleziona tutto
=SE(RIF.RIGA(A1)<=CONTA.VALORI($D$20:$D$50);INDICE(D$1:D$50;PICCOLO(SE($D$20:$D$50<>"";RIF.RIGA($A$20:$A$50);"");RIF.RIGA(A1)));"")

Nota come in INDICE ho usato l' intervallo partendo da riga 1 per poterlo usare direttamente con il numero di riga restituito da PICCOLO (da 20 a 50); quindi l' intervallo di INDICE parte da riga1, gli altri intervalli sono riferiti alla tua tabella, Rif.Riga(A1) rimane sempre Rif.Riga(A1) perche' serve per creare la sequenza 1, 2, 3 etc

Ciao a tutti
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [EXCEL] No macro - Estrarre righe in base a contenuto ce

Postdi pl1957 » 03/10/12 06:18

Anthony47 ha scritto:Innanzitutto complimenti a canapone per la sua didattica.

Sì, infatti! È stato chiarissimo!

Inoltre, devo osservare che la soluzione di Canapone già nativamente non genera l'errore #NUM a partire dalla prima riga non copiata fino alla fine del rettangolo 30*14. In questo modo non si è costretti ad usare la funzione se.errore(), anche se inserirla mette sempre al riparo da imprevisti.

Anthony47 ha scritto:Nota come in INDICE ho usato l' intervallo partendo da riga 1 per poterlo usare direttamente con il numero di riga restituito da PICCOLO (da 20 a 50)

Ed in questo modo, se tra le righe 1 e la 20, alla quale iniziano i dati, inserisco o elimino righe il risultato non cambia...
Grandioso! :)

Mi siete stati veramente d'aiuto! Grazie!
Avatar utente
pl1957
Utente Junior
 
Post: 29
Iscritto il: 29/09/12 08:14
Località: Marche


Torna a Applicazioni Office Windows


Topic correlati a "[EXCEL] No macro - Estrarre righe in base a contenuto cella":


Chi c’è in linea

Visitano il forum: Nessuno e 4 ospiti