CERCA.VERT è una potente funzione. Ma spesso ricevo una domanda in uno dei miei seminari su Power Excel da qualcuno che vuole sapere se CERCA.VERT può restituire tutti i valori corrispondenti. Come saprai, CERCA.VERT con Falso come quarto argomento restituirà sempre la prima corrispondenza che trova. Nella schermata seguente, la cella F2 restituisce 3623 perché è la prima corrispondenza trovata per il lavoro J1199.
La domanda, quindi, può CERCA.VERT restituire tutte le corrispondenze?
CERCA.VERT non lo farà. Ma altre funzioni possono.
Se desideri calcolare il totale di tutti i costi dal lavoro J1199, dovresti utilizzare =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Se disponi di valori di testo e desideri unire tutti i risultati in un unico valore, puoi utilizzare =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Questa formula funziona solo in Office 365 ed Excel 2019.
In alternativa, potrebbe essere necessario restituire tutti i risultati di un singolo lavoro in un nuovo intervallo del foglio di lavoro. Una nuova =FILTER(B2:C53,A2:A53=K1,"None Found")funzione in arrivo su Office 365 nel 2019 risolverà il problema:
A volte, le persone vogliono eseguire tutti i CERCA.VERT e sommarli. Se la tua tabella di ricerca è ordinata, puoi usare =SUM(LOOKUP(B2:B53,M3:N5)).
Se è necessario sommare tutti i CERCA.VERT con la versione di corrispondenza esatta di CERCA.VERT, sarà necessario avere accesso agli array dinamici per poter utilizzare =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Per saperne di più sugli array dinamici, controlla gli array dinamici di Excel direttamente al punto.
Guarda un video
Trascrizione del video
Impara Excel da, Podcast Episodio 2247: Puoi restituire tutti i valori VLookUp?
Hey. Bentornato al netcast. Sono Bill Jelen. Durante il mio seminario ad Appleton, nel Wisconsin, la scorsa settimana sono emerse due domande, entrambe correlate. Hanno detto, ehi, come restituiamo tutti i CERCA.VERT, va bene? In questo caso, come J1199 ha un sacco di fiammiferi e loro, sai, vogliono restituirli tutti, e la mia prima domanda ogni volta che qualcuno me lo chiede è, beh, cosa vuoi fare con i fiammiferi? Sono numeri che vuoi sommare o è testo che vuoi concatenare? Ed è divertente. Le due domande nello stesso seminario, una persona voleva sommarle e l'altra persona voleva concatenare i risultati.
Quindi diamo un'occhiata a entrambi. Controlla la descrizione di YouTube per un sommario in cui puoi passare all'altro se vuoi vedere il risultato del testo.
Ok, quindi, per prima cosa, se vogliamo sommarli tutti, non useremo affatto CERCA.VERT. Useremo una funzione chiamata SUMIF o SUMIFS che sommerà tutto ciò che corrisponde a questo elemento. Quindi, SUMIFS. Ecco i valori numerici che vogliamo sommare e premerò F4 per bloccarli. In questo modo, mentre lo copio, continuerà a puntare allo stesso intervallo, e quindi vogliamo andare a controllare e vedere se il numero di LAVORO nella colonna A, di nuovo F4 lì, è = al valore alla nostra sinistra - in questo caso E2 - e mentre lo copiamo, vedremo il TOTALE per ogni elemento. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Facciamo solo un piccolo controllo qui. J1199. Il totale è 25365. Va bene. Quindi sta funzionando. Se si tratta di numeri e si desidera ottenere tutti i numeri e sommarli, passare a SOMMA.SE o SOMMA.SE, ma se è testo, va bene, ora questa funzione è nuova in Office 365 a febbraio 2017. Quindi, se si dispone di Excel 2016 o Excel 2013 o Excel 2010 o uno di quelli più vecchi, non avrai questa funzione. È una funzione chiamata TEXTJOIN. TEXTJOIN. Questa è un'altra funzione di (Joe McDade - 01:50) che ci ha appena portato tutte quelle fantastiche formule di array dinamici a Ignite nel 2018, e Joe si è assicurato che TEXTJOIN funzionasse con gli array, il che è davvero fantastico.
Quindi, il delimitatore qui sarà, SPAZIO, ignora definitivamente EMPTY. Vogliamo ignorare EMPTY qui perché genereremo molti vuoti in questa parte successiva, l'istruzione IF. SE quell'elemento su A2, F4, è = a questo numero di LAVORO qui, allora voglio l'elemento corrispondente dalla colonna C, F4, altrimenti, voglio "" così. Chiudi l'istruzione IF. Chiudi il TEXTJOIN. Devo premere CONTROL + MAIUSC + INVIO? No, io no. Mi porta tutti i prodotti che corrispondono in questo modo, va bene? Quindi, restituendo tutti i CERCA.VERT, se vogliamo sommarli, sì, se vogliamo concatenarli, sì. (= TEXTJOIN (",", True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, "")))
Va bene, ora, c'è un'altra possibilità qui quando le persone mi chiedono se possono restituire tutti i CERCA.VERT. Potrebbe essere un problema in cui vogliamo cercare ciascuno di questi costi qui e calcolare il COSTO DI GESTIONE e quindi sommarli tutti. Ad esempio, non voglio mettere un CERCA.VERT qui e un CERCA.VERT qui e un CERCA.VERT qui e un CERCA.VERT qui. Voglio solo eseguirli tutti insieme e, in tal caso, useremo la funzione SUM e poi la vecchia, vecchia funzione LOOKUP. LOOKUP dice che cercheremo tutti questi valori nella colonna B. Non ho bisogno di F4 qui perché non lo sto copiando da nessuna parte. ,. Ecco la nostra tabella di ricerca. ), chiudi la SOMMA, si spegne e fa ogni singolo CERCA.VERT e poi li somma tutti in questo modo. (= SOMMA (RICERCA (B2: B53, K3: L5)))
Beh ciao. Tutti questi argomenti sono il mio libro LIV: The 54 Greatest Tips Of All Time. Fare clic sulla i nell'angolo in alto a destra per saperne di più.
Quindi, la domanda è: puoi restituire tutti i CERCA.VERT? Bene, più o meno, ma in realtà non usando CERCA.VERT. Useremo SUMIF, TEXTJOIN o SUM o LOOKUP per risolverlo.
Beh ciao. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Sai, va bene, ho parlato di questi array dinamici per una settimana. Volevo fare un video in cui non ho toccato gli array dinamici perché so che molte persone non li hanno ancora, ma eccoci qui. È l'outtake. Sai, questi non sono alfabetici. Sarebbe molto meglio se potessimo ordinarli, e se ti capita di avere i nuovi array dinamici, potresti inviarli alla funzione SORT, SORT in questo modo, e premere INVIO, e ora i risultati saranno ordinati in questo modo.
Sai, anche questa formula potrebbe migliorare con gli array dinamici. La ricerca richiede l'utilizzo di, TRUE. E se volessi usare un, FALSE? Potremmo cambiarlo in CERCA.VERT, cercare tutto questo testo in quella tabella, 2,. In questo caso, userò TRUE ma, in un altro caso, potresti usare FALSE. CTRL + MAIUSC + INVIO. No. Funzionerà e basta, va bene? (= SOMMA (CERCA.VERT (B2: B53, K3: L5,2, Vero)))
Gli array dinamici in uscita all'inizio del 2019 risolveranno così tanti problemi.
Grazie per aver partecipato all'outtake qui. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: can-you-return-all-vlookup-values.xlsx
Quando qualcuno chiede "Può CERCA.VERT restituire tutte le corrispondenze, la risposta è No. Ma esistono molte altre funzioni che possono fare essenzialmente la stessa cosa.
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Normalizza i tuoi dati come faresti con gli altri normalizzassero i loro dati per te"
Kevin Lehrbass
