CERCA.VERT di Excel può restituire tutti i risultati e unirli con una virgola in mezzo?
Guarda un video
- L'obiettivo è concatenare tutte le risposte di testo da un CERCA.VERT
- Metodo di Bill: usa una funzione VBA chiamata GetAll
- Elenco univoco utilizzando Rimuovi duplicati
- Il metodo di Mike:
- Elenco univoco utilizzando il filtro avanzato
- Funzione TEXTJOIN aggiunta in Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- A causa della funzione SE, la formula richiede Ctrl + Maiusc + Invio ogni volta che si modifica la formula
- Alt AQOR Enter eseguirà nuovamente il filtro avanzato!
Trascrizione del video
Episodio 183: Partecipa a tutte le partite CERCA.VERT
Bill Jelen: Ehi, bentornato. È il momento per un altro Podcast Dueling Excel. Sono Bill Jelen di, sarò affiancato da Mike Girvin per Excel Is Fun. Questo è il nostro episodio 183: Partecipa a tutte le partite CERCA.VERT.
(Musica)
Bene, la domanda di oggi di Matt. CERCA.VERT può restituire tutti i risultati e unirli con uno spazio virgola tra ciascuno. Ad esempio, 109876 che sono questi due qui, può restituire lo spazio virgola olio basso controllato il 12/12. E ovviamente se ce ne fossero di più, tornerebbero di più. Va bene, quindi la mia soluzione qui utilizzerà alcuni VBA. Va bene, quindi assicurati che sia salvato come xlsm o non puoi eseguire VBA o xlsb, ma non xlsx: xlsx è l'unico file che non può eseguire VBA. Premi Alt + F11, assicurati di essere su Dual183 o qualunque sia il nome della tua cartella di lavoro. Inserisci il modulo nel modulo vuoto e incolliamo questo codice, va bene.
Diamo un'occhiata a questa funzione GetAll, ed ecco il numero ID che stiamo cercando e quindi l'intervallo che vogliamo cercare. E iniziamo, restituiremo una variabile chiamata GetAll, quindi iniziamo con che è uguale allo spazio vuoto. Per ogni cella nel mio intervallo, se il valore della cella è quello che stiamo cercando, prenderemo GetAll = GetAll & "" e poi Cell.Offset (0 righe, 1 colonna), in altre parole il valore è proprio accanto a quel numero ID perché di nuovo in VBA, ecco il numero ID. Se troviamo il numero ID corrispondente, vogliamo andare oltre di 1 colonna. Ora, e se volessi andare 2 colonne sopra o 3 colonne sopra, beh, allora cambi queste 0 righe e 1 colonna in 2. Va bene, controlla anche se - non mettiamo una virgola se questo è il primo.Quindi se la variabile GetAll è attualmente "", non metteremo la virgola, va bene?
Quindi ora che abbiamo questa funzione qui, guarda quanto è facile risolvere il problema di Matt. Verremo qui e prendiamo i suoi ID, Ctrl + C e incolliamo Ctrl + V in questo modo. Dati, Rimuovi duplicati, fare clic su OK. Quindi c'è un elenco univoco di ID e quindi vogliamo dire = getall e stiamo cercando quel valore nella virgola E2. Guardando attraverso questo intervallo qui, premerò F4. F4 funziona proprio come una normale funzione. E di nuovo spostando la domanda di Matt fuori mano, fai doppio clic per abbatterla. Funzionerà.
E proviamo, proviamo qualcosa di pazzo qui. Facciamo una frase 1 e mettiamone un gruppo come da frase 1 a 10. Le firmeremo tutte con 109999. Incolla e poi incolla qui. Copia quella formula, modifica la formula in modo che vada fino in fondo, ovviamente. Sì. E restituirà tutte quelle frasi. Va bene, quindi questa è la mia soluzione, VBA, una piccola funzione lì. Mike, vediamo cosa hai.
Mike Girvin: Grazie ,. GetAll, questa è una fantastica funzione VBA. Va bene, vado al foglio proprio qui. L'ho già convertito in una tabella Excel in modo che quando aggiungiamo record di seguito, si spera che le cose si aggiornino.
Ora la prima cosa che farò in due parti. Potrei fare una formula qui per estrarre un elenco univoco, ma voglio guardare un'altra opzione: il filtro avanzato ha un'opzione di estrazione elenco univoco e può essere aggiornato. Evidenzierò solo i dati della colonna ID, su Filtro avanzato o userò la tastiera Alt, A, Q. Ora, elenco dei filtri sul posto, in nessun modo. Voglio copiarlo in un'altra posizione. Ha solo la colonna A e perché è una tabella di Excel che si espanderà in seguito. Non ho alcun criterio, voglio copiarlo in D1 e controllare Solo record unici. Fare clic su OK.
Ora, vengo qui, entrano tutti i commenti e userò una funzione che funziona solo in Excel 2016 Office 365: = TEXTJOIN function. Questa funzione da sola vale la pena ottenere l'ultima versione di Excel. Questo è un compito così comune che le persone vogliono fare, unire molte cose insieme. Ora il nostro delimitatore in "," e la cosa grandiosa di questa funzione è che possiamo dirle di ignorare le celle vuote. Ora posso mettere TRUE, 1 o Leave it, ometterlo. Quindi, ho intenzione di lasciarlo, ometterlo. Ed è qui che abbiamo bisogno del nostro testo. Useremo la funzione IF per filtrare e ottenere solo gli elementi che vogliamo. Dirò di guardare l'intera colonna qui: Nome tabella e poi in () il nome del campo, qualcuno di voi = a questo riferimento di cella relativo, questo è il test logico. Se dovessi fare clic su questo e premere il tasto F9 per valutare,puoi vedere che in questo momento abbiamo solo 2 TRUES, Ctrl + Z ora digito una virgola e con l'array di True e Falses, ora posso dargli gli elementi da scegliere. Quindi ora selezioneremo solo gli articoli che hanno un VERO qui da questa gamma. Virgola e io vogliamo assicurarci di mettere "" - che apparirà come una cella vuota rispetto al secondo argomento in TEXTJOIN.
Ora, chiuderò la parentesi e ora la funzione IF creerà quella stringa di Veri e Falsi, gli elementi effettivi di questo intervallo verranno raccolti se lo vede Vero e tutti gli altri elementi avranno quella cella vuota. E indovina cosa? TEXTJOIN ignorerà completamente tutte quelle celle vuote e restituirà solo gli elementi che corrispondono a questo ID, quindi li unirà con quel delimitatore. Ora questa è sicuramente una formula Array che richiede la combinazione di tasti speciale Ctrol + Maiusc + Invio. L'argomento logico del test contiene la nostra operazione Array e tale argomento non può calcolare correttamente questa operazione Array a meno che non usiamo la tastiera Ctrl + Maiusc + Invio. Adesso chiudo le parentesi. In realtà potremmo provare 1 proprio qui nel Testo 1 se F9 tutto questo, potremmo vedere che otteniamo i 2 elementi, il resto di quelle celle vuote verrà ignorato. Ctrl + Z. Adesso molla'inseriscilo nella cella con Ctrl + Maiusc + Invio. Alza immediatamente lo sguardo alla barra della formula. Quelle parentesi graffe sono Excel che ti dice di aver capito e calcolato questo come una formula di matrice. Ora posso fare doppio clic e inviarlo. Sembra buono.
Vado all'ultima cella e premo F2 per verificare che tutti gli intervalli siano visualizzati correttamente. Quello che non voglio fare è non premere Invio perché quella formula dopo averla messa in modalità Modifica calcolerà correttamente solo se usiamo Ctrl + Maiusc + Invio; oppure, poiché abbiamo già inserito la formula, possiamo semplicemente usare il tasto Esc per tornare a ciò che è nella cella prima di metterlo in modalità di modifica.
Ora, proviamo questo. Farò clic nell'ultima cella qui sotto e premerò Tab, quindi digiterò un nuovo ID, Tab, Tab. Un altro nuovo record, Tab, e posso già vedere che non avevo abbastanza lavoro qui. Lo sono, metteremo - Perfetto e poi Invio. Ora, questo non si aggiornerà automaticamente come se avessimo un sacco di formule che contiamo elementi unici e quindi estraiamo elementi unici, ma nessun problema. Guarda questo. Possiamo aggiornare questo elenco di record univoci perché abbiamo utilizzato il filtro avanzato e non importa da quale cella inizi perché quando viene richiamato il filtro avanzato, memorizza l'intervallo di estrazione e gli intervalli che stava inizialmente guardando. Puoi fare clic su Filtro avanzato o utilizzare la tastiera Alt + A + Q. Dobbiamo selezionare Copia in un'altra posizione, ma guarda quello.È stato totalmente ricordato ed espanso ad A13 grazie alla funzione Tabella Excel. Ricordava la gamma di estratti. Devo controllare solo i record unici ma fare clic su OK.
Ora devo venire e copiare questa formula. Ed ecco fatto, usando il filtro avanzato e la straordinaria funzione TEXTJOIN con, nell'operazione Array per ottenere solo gli elementi che corrispondono. Va bene, torna a.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Oh hey, voglio ringraziare tutti per essere passati. Ci vediamo la prossima volta per un altro podcast di Dueling Excel da ed Excel Is Fun.
Download file
Scarica il file di esempio qui: Duel183.xlsm
