Ron vuole fare un mucchio di CERCA.VERT e sommare i risultati. Esiste una soluzione a formula unica a questo problema.
Ron chiede:
Come puoi sommare tutti i CERCA.VERT senza eseguire ogni singola ricerca?
Molte persone hanno familiarità con:
=VLOOKUP(B4,Table,2,True)
Se stai eseguendo la versione con corrispondenza approssimativa di CERCA.VERT (dove specifichi True come quarto argomento), puoi anche fare RICERCA.
La ricerca è dispari perché restituisce l'ultima colonna della tabella. Non specifichi un numero di colonna. Se la tua tabella va da E4 a J8 e vuoi il risultato dalla colonna G, devi specificare E4: G4 come tabella di ricerca.
Un'altra differenza: non specifichi Vero / Falso come quarto argomento come faresti in CERCA.VERT: la funzione CERCA esegue sempre la versione con corrispondenza approssimativa di CERCA.VERT
Perché perdere tempo con questa antica funzione? Perché Lookup ha un trucco speciale: puoi cercare tutti i valori contemporaneamente e li sommerà. Invece di passare un singolo valore come B4 come primo argomento, puoi specificare tutti i tuoi valori =LOOKUP(B4:B17,E4:F8). Poiché questo restituirebbe 14 valori diversi, è necessario racchiudere la funzione in una funzione wrapper come =SUM( LOOKUP(B4:B17,E4:F8))o =COUNT(LOOKUP(B4:B17,E4:F8))o =AVERAGE( LOOKUP(B4:B17,E4:F8)). Ricorda, hai bisogno di una funzione wrapper, ma non di una funzione rapper. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))non funzionerà.
C'è un errore molto comune che vorresti evitare. Dopo aver digitato o modificato la formula, non premere Invio! Invece, fai questo trucco con la tastiera a tre dita. Tieni premuto Ctrl e Maiusc. Tenendo premuti Ctrl e Maiusc, premere Invio. Ora puoi rilasciare Ctrl e Maiusc. Lo chiamiamo Ctrl + Maiusc + Invio, ma deve essere programmato correttamente: premi e tieni premuto Ctrl + Maiusc, premi Invio, Rilascia Ctrl + Maiusc. Se lo hai fatto correttamente, la formula apparirà nella barra della formula circondata da parentesi graffe:(=SUM(LOOKUP(B4:B17,E4:F8)))
Nota a margine
LOOKUP può anche fare l'equivalente di HLOOKUP. Se la tabella di ricerca è più larga che alta, CERCA passerà a CERCA ORIZZ. In caso di pareggio … un tavolo di 8x8 o 10x10, LOOKUP tratterà il tavolo come verticale.
Guarda il video qui sotto o studia questo screenshot.
Guarda un video
Trascrizione del video
Impara Excel da Podcast, episodio 2184: Sum All Lookups.
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi, da Ron, sull'utilizzo del vecchio, vecchio programma LOOKUP. E questo è dal mio libro, Excel 2016 In Depth.
Diciamo che avevamo un sacco di prodotti qui e dovevamo usare una tabella di ricerca. E per ogni prodotto, dovevamo, sai, ottenere il valore dalla tabella di ricerca e sommarlo. Bene, il modo tipico è che usiamo un CERCA.VERT-- = CERCA.VERT per questo prodotto in questa tabella. Premerò F4, lo bloccherò e con il secondo valore. E in questo caso particolare, poiché ogni singolo valore che stiamo cercando è nella tabella e la tabella è ordinata, è sicuro usare TRUE. Normalmente, non userei mai TRUE, ma in questo episodio useremo TRUE. Quindi ottengo tutti quei valori e poi quaggiù, Alt + =, ne otteniamo il totale, giusto? Ma cosa succederebbe se il nostro obiettivo fosse solo quello di ottenere il 1130? Non abbiamo bisogno di tutti questi valori. Abbiamo solo bisogno di questo risultato.
Bene, okay, ora, c'è una vecchia, vecchia funzione che esiste dai tempi di Visical, chiamata LOOKUP. Non CERCA.VERT. Non CERCA.ORIZZ, solo CERCA. E sembra, all'inizio, simile a CERCA.VERT: specifichi quale valore stai cercando e la tabella di ricerca, premi F4, ma poi abbiamo finito. Non dobbiamo specificare quale colonna perché CERCA va solo all'ultima colonna della tabella. Se si dispone di una tabella a sette colonne e si desidera cercare il quarto valore, è sufficiente specificare le colonne da una a quattro. Tutto a posto? E quindi, qualunque sia l'ultima colonna, è quello che cercherà. E non dobbiamo specificare FALSE o TRUE perché utilizza sempre TRUE; non esiste una versione FALSE. Tutto a posto?
Quindi devi capire, se stai facendo un CERCA.VERT, io uso sempre, FALSE alla fine, ma in questo caso è un breve elenco - sappiamo che tutto nell'elenco è nella tabella. Non manca nulla e la tabella è ordinata. Tutto a posto? Quindi, questo ci darà lo stesso identico risultato che abbiamo per CERCA.VERT.
Fantastico, voglio copiarlo: Alt + =. Tutto a posto. Ma questo non ci guadagna nulla perché dobbiamo ancora inserire tutte le formule e poi la funzione SOMMA. La cosa bella è che CERCA può fare un trucco che CERCA.VERT non può fare, va bene? E questo è fare tutte le ricerche in una volta. Quindi, dove lo invio alla funzione SOMMA, quando dico CERCA, qual è l'elemento di ricerca? Vogliamo cercare tutte queste cose, virgola, e poi ecco la tabella - e non dobbiamo premere F4, perché non la copieremo da nessuna parte, c'è solo una formula - chiudi LOOKUP, chiudi la somma.
Bene, ora, ecco il punto in cui le cose possono complicarsi: se premi semplicemente Invio qui, otterrai 60, va bene? Perché andrà solo a fare il primo. Quello che devi fare è tenere premuti i tre tasti magici, e questo è Ctrl + Maiusc - Sto tenendo premuto Ctrl + Maiusc con la mano sinistra, continuo a tenerli premuti e premo INVIO con la mano destra, e farà tutti i calcoli di CERCA.VERT. Non è fantastico? Notare nella barra della formula qui sopra, o nel testo della formula, viene inserito tra parentesi graffe. Non digiti quelle parentesi graffe, Excel inserisce quelle parentesi graffe, per dire "Ehi, hai premuto Ctrl + Maiusc + Invio per questo".
Ora, ehi, questo argomento e molti altri argomenti sono in questo libro: Power Excel con, l'edizione 2017. Fai clic sulla "I" in alto a destra per leggere di più sul libro.
Oggi, domanda di Ron: come puoi sommare tutti i CERCA.VERT? Ora, la maggior parte delle persone conosce CERCA.VERT in cui si specifica il valore di ricerca, la tabella, la colonna e quindi VERO o FALSO. E se stai facendo, se sei idoneo, per la versione TRUE di CERCA.VERT. allora puoi anche fare questo vecchio CERCA. È strano, perché restituisce l'ultima colonna della tabella, non specifichi il numero di colonna e non dici VERO o FALSO. È sempre VERO. Perché dovremmo usarlo? Perché ha un trucco speciale: puoi eseguire tutti i valori di ricerca contemporaneamente e li sommerà. Devi premere Ctrl + Maiusc + Invio dopo aver digitato quella formula o non funzionerà. E poi nell'outtake, ti mostrerò un altro trucco per CERCA.
Beh, ehi, voglio ringraziare Ron per avermi inviato questa domanda, e voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Bene, mentre stiamo qui parlando di CERCA, l'altra cosa che CERCA può fare: sai, abbiamo CERCA.VERT per una tabella verticale come questa o CERCA.ORIZZ per una tabella orizzontale come questa; LOOKUP può andare in entrambi i modi. quindi possiamo dire hey vogliamo = CERCA questo valore, D, in questa tabella, e poiché la tabella è più larga che alta, CERCA passa automaticamente alla versione CERCA.ORIZZ, giusto? Quindi, in questo caso, poiché stiamo specificando 3 righe per 5 colonne, eseguirà CERCA.ORIZZ. E poiché l'ultima riga qui sono i numeri, ci porterà quel numero. Quindi abbiamo D, Date, ci fa 60. Va bene. Se dovessi specificare una tabella che va solo alla riga 12, otterrò invece il nome del prodotto. Tutto a posto? Quindi è una specie di piccola funzione interessante. Penso che la Guida di Excel diceva "Ehi, non utilizzare questa funzione", ma "in certi momenti è possibile utilizzare questa funzione.
Foto del titolo: grandcanyonstate / pixabay
