Come eseguire un calcolo (come CERCA.VERT) per ogni elemento che è stato Alt + Invio in una cella.
Guarda un video
- Un visualizzatore scarica i dati da un sistema in cui ogni elemento è separato da Alt + Invio
- Bill: Perché lo fai? Visualizzatore: è il modo in cui eredito i dati. Voglio mantenerlo così.
- Bill: cosa vuoi fare con il 40% dei valori non nella tabella? Visualizzatore: nessuna risposta
- Bill: C'è un modo complicato per risolvere questo problema se si dispone degli ultimi strumenti di Power Query.
- Invece, una macro VBA per risolverlo: la macro dovrebbe funzionare fino a Excel 2007
- Invece di fare CERCA.VERT, esegui una serie di Trova e sostituisci con VBA
Trascrizione del video
Impara Excel da, Podcast Episodio 2150: CERCA.VERT Ogni Alt + Valore inserito in ogni cella.
Hey. Bentornato al netcast. Sono Bill Jelen. Oggi, una delle domande più bizzarre. Qualcuno ha detto, ehi, voglio fare un CERCA.VERT per ogni valore nella cella e quando ho aperto il file Excel, i dati sono stati ALT + Invio. Quindi, ci sono 4 elementi in questo ordine e sono tutti separati da ALT + INVIO, quindi solo 2 qui e 6 qui e così via.
Sono tornato dalla persona che ha inviato questo messaggio. Ero tipo, beh, questo è un modo davvero brutto per memorizzare questi dati. Perché stai facendo questo? E lui era come se non lo stessi facendo. Questo è il modo in cui vengono scaricati i dati. Ho detto, va bene se lo divido in righe separate? No, devi mantenerlo in questo modo.
Tutto a posto. Quindi, non c'è un buon modo per fare un CERCA.VERT per ogni singolo elemento e domani, nell'episodio di domani, 2151, ti mostrerò come possiamo usare una nuova funzionalità in Power Query per farlo, ma avresti avere Office 365 per averlo.
Quindi, oggi, voglio usare un metodo che tornerà indietro e quello che ho fatto qui è che ho creato un nuovo foglio di lavoro con LOOKUPTABLE, quindi questi sono gli elementi. Ho anche notato che ci sono un sacco di cose, circa il 40% delle cose qui, non sono in CERCA. Ho detto, cosa vuoi fare lì, e nessuna risposta a quella domanda, quindi li lascerò come sono se non trovo una corrispondenza.
Va bene, quindi, quello che ho qui è che ho un foglio chiamato LOOKUPTABLE e vedrai che il mio file in questo momento è archiviato come xlsx e userò una macro VBA. Per poter utilizzare una macro VBA, non puoi averla come xlsx. È contro le regole. Quindi, devi SALVARE COME e salvare questo è xlsm. FILE, SALVA CON NOME e modificalo da WORKBOOK a WORKBOOK XLSM CON MACRO ABILITATO oppure a WORKBOOK BINARIO (uno di questi funzionerà) e fai clic su SALVA.
Va bene, quindi, ora possiamo eseguire le macro. ALT + F11 per accedere al registratore di macro. Inizi con questo grande schermo grigio. INSERT, MODULE, e c'è il nostro modulo, ed ecco il codice. Quindi, l'ho chiamato ReplaceInPlace e ho definito un foglio di lavoro. Questo è il LookupTable. Dovresti inserire il tuo vero nome del foglio di lavoro della tabella di ricerca, quindi la mia tabella di ricerca inizia nella colonna A, che è la colonna 1. Quindi, vado all'ultima riga nella colonna 1, premo il tasto FINE e la freccia SU, o , ovviamente, CONTROL + freccia SU farebbe la stessa cosa, capirebbe di che riga è, e poi andremo da ogni riga da 2 a FinalRow. Perché 2? Bene, perché le intestazioni sono nella riga 1. Quindi voglio sostituire, a partire dalla riga 2 fino all'ultima riga, quindi, per ogni riga da 2 a FinalRow, il FromValue è ciò 's nella colonna A e ToValue è ciò che è nella colonna B.
Ora, se, per qualche motivo, i tuoi dati fossero in J e K, allora questa J sarebbe la decima colonna, quindi inserisci un 10 lì e K sarebbe l'undicesima colonna, quindi, nella Selezione, sostituiremo da FromValue a ToValue. Questo è davvero importante qui. xlPart, xlPart - e questa è una L, non un numero 1 - xlPart che dice che ci consentirà di sostituire parte della cella perché quei numeri di parte sono tutti separati da un carattere di avanzamento riga. Anche se non puoi vederlo, è lì. Quindi, questo dovrebbe consentirci di non aggiornare accidentalmente la cosa sbagliata, quindi xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Tutto a posto. Quindi, questa è la nostra piccola macro qui. Proviamolo. Prenderemo questi dati e non voglio distruggere nulla, quindi prenderò i dati originali e li copierò a destra. Tutto a posto. Quindi, abbiamo la nostra selezione lì. In realtà, comincerò da questo punto. CONTROL + BACKSPACE e quindi ALT + F8 per ottenere un elenco di tutte le macro. C'è il nostro REPLACEINPLACE. Faccio clic su ESEGUI e, ovunque abbia trovato un elemento nella CERCA.VERT, ha sostituito il numero di tale oggetto con l'elemento, apparentemente eseguendo un CERCA.VERT, sebbene non lo stiamo risolvendo affatto con CERCA.VERT.
Tutto a posto. Quindi, ehi, il nuovissimo libro che è uscito - Power Excel With, l'edizione 2017, 617 Excel Mysteries Solved - contiene tutti i tipi di nuovi fantastici suggerimenti.
Riepilogo di oggi: il visualizzatore scarica i dati da un sistema in cui ogni elemento è separato da ALT + INVIO, quindi deve eseguire un CERCA.VERT su ogni elemento e, sai, perché lo sto facendo; quindi, ha detto la persona, non lo sto facendo ma devo mantenerlo così; e poi il 40% dei valori non sono nella tabella, beh, nessuna risposta; quindi immagino che aggiungeranno quegli elementi al tavolo; ora, domani parleremo di come risolvere questo problema con Power Query, ma, oggi, questa macro funzionerà completamente in tutte le versioni Windows di Excel, almeno tornando a Excel 2007; quindi, invece di un CERCA.VERT, solo una serie di trova e sostituisci con VBA.
Beh ciao. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2150.xlsm
