CERCA.VERT di Excel è potente, ma non funzionerà in situazioni specifiche. Oggi, uno sguardo a come risolvere i problemi di CERCA.VERT.
CERCA.VERT è la mia funzione preferita in Excel. Se puoi eseguire CERCA.VERT, puoi risolvere molti problemi in Excel. Ma ci sono cose che possono far scattare un CERCA.VERT. Questo argomento parla di alcuni di loro.
Ma prima, le basi di CERCA.VERT in inglese semplice.
I dati in A: C provenivano dal reparto IT. Hai richiesto le vendite per articolo e data. Ti hanno dato il numero dell'articolo. Hai bisogno della descrizione dell'oggetto. Invece di attendere che il reparto IT riesca a eseguire i dati, trovi la tabella mostrata nella colonna F: G.
Desideri che CERCA.VERT trovi l'elemento in A2 mentre esegue la ricerca nella prima colonna della tabella in $ F $ 3: $ G $ 30. Quando CERCA.VERT trova la corrispondenza in F7, si desidera che CERCA.VERT restituisca la descrizione trovata nella seconda colonna della tabella. Ogni CERCA.VERT che cerca una corrispondenza esatta deve terminare con False (o zero, che è equivalente a False). La formula seguente è impostata correttamente.
Si noti che si utilizza F4 per aggiungere quattro segni di dollaro all'indirizzo per la tabella di ricerca. Quando si copia la formula nella colonna D, è necessario che l'indirizzo della tabella di ricerca rimanga costante. Esistono due alternative comuni: è possibile specificare le intere colonne F: G come tabella di ricerca. Oppure puoi nominare F3: G30 con un nome come ItemTable. Se si utilizza =VLOOKUP(A2,ItemTable,2,False), l'intervallo denominato agisce come un riferimento assoluto.
Ogni volta che esegui un gruppo di CERCA.VERT, devi ordinare la colonna di CERCA.VERT. Ordina ZA e tutti gli errori # N / A verranno visualizzati in cima. In questo caso, ce n'è uno. L'articolo BG33-9 non è presente nella tabella di ricerca. Forse è un errore di battitura. Forse è un articolo nuovo di zecca. Se è nuova, inserisci una nuova riga in un punto qualsiasi al centro della tabella di ricerca e aggiungi il nuovo elemento.
È abbastanza normale avere alcuni errori # N / D. Ma nella figura seguente, esattamente la stessa formula non restituisce altro che # N / A. Quando ciò accade, vedo se riesco a risolvere il primo CERCA.VERT. Stai cercando il BG33-8 trovato in A2. Inizia a scorrere verso il basso attraverso la prima colonna della tabella di ricerca. Come puoi vedere, il valore di corrispondenza è chiaramente in F10. Perché puoi vedere questo, ma Excel non può vederlo?
Vai a ogni cella e premi il tasto F2. Ecco F10. Notare che il cursore di inserimento appare subito dopo l'8.
Ecco la cella A2 in modalità Modifica. Il cursore di inserimento è a un paio di spazi di distanza dall'8. Questo è un segno che a un certo punto questi dati sono stati memorizzati in un vecchio set di dati COBOL. Di nuovo in COBOL, se il campo Articolo era definito come 10 caratteri e hai digitato solo 6 caratteri, COBOL lo riempirà con 4 spazi extra.
La soluzione? Invece di cercare A2, cerca il file TRIM(A2).
La funzione TRIM () rimuove gli spazi iniziali e finali. Se hai più spazi tra le parole, TRIM li convertirà in un unico spazio. Nella figura sotto ci sono spazi prima e dopo entrambi i nomi in A1. =TRIM(A1)rimuove tutti gli spazi tranne uno in A3.
A proposito, cosa succederebbe se il problema fosse stato gli spazi finali nella colonna F invece della colonna A? Aggiungi una colonna di funzioni TRIM () a E, che punta alla colonna F. Copia quelle e incolla come valori in F per far ricominciare a funzionare le ricerche.
L'altro motivo molto comune per cui CERCA.VERT non funziona è mostrato qui. La colonna F ha numeri reali. La colonna A ha un testo che assomiglia a numeri.
Selezionare tutta la colonna A. Premere alt = "" + D, E, F. Questo fa un testo predefinito in colonne e convertirà tutti i numeri di testo in numeri reali. La ricerca riprende a funzionare.
Guarda un video
- CERCA.VERT risolve molti problemi
- Problemi comuni di CERCA.VERT:
- Se CERCA.VERT inizia a funzionare, ma # N / A diventa più evidente: dimenticato $ nella tabella di ricerca
- Alcuni # N / A: elementi mancanti dalla tabella
- Nessuno dei VLOOKUP funziona: controlla gli spazi finali
- Rimuovi gli spazi finali con TRIM
- Numeri e numeri memorizzati come testo
- Seleziona entrambe le colonne e utilizza alt = "" + DEF
- L'episodio include una battuta che sia i contabili che gli esperti di informatica trovano divertente, ma per motivi diversi
Trascrizione del video
Impara Excel dal podcast, episodio 2027 - Risoluzione dei problemi con CERCA.VERT!
Bene, durante il podcast dell'intero libro, fai clic sulla "i" nell'angolo in alto a destra per accedere alla playlist!
CERCA.VERT È la mia funzione preferita in tutto Excel, e racconto sempre questa barzelletta in uno dei miei seminari, e mi viene da ridere che tu sia o meno una persona IT. Dico sempre "Beh, guarda, abbiamo questo set di dati qui a sinistra, e posso guardare quei dati e dire che i dati provengono dal reparto IT perché è esattamente quello che ho chiesto, ma non proprio quello di cui avevo bisogno. Ho chiesto la data e la quantità dell'articolo e mi hanno fornito la data e la quantità del numero dell'articolo, ma non si sono preoccupati di darmi la descrizione, giusto? " E i contabili ridono sempre di questo, perché succede a loro tutto il tempo, e i ragazzi IT dicono "Beh, ti ho dato quello che hai chiesto, non è colpa mia!" Quindi entrambi pensano che sia divertente per motivi diversi, quindi, sai, e il tecnico IT è impegnato, non può tornare a riscrivere la query,quindi dobbiamo fare qualcosa per salvarlo da soli.
E quindi questo tavolino che ho copiato da qualche altra parte nel mio computer, in un inglese semplice, quello che fa CERCA.VERT è "Ehi, abbiamo un numero di articolo W25-6". Abbiamo una tabella qui, voglio scorrere la prima colonna della tabella fino a trovare quel numero di articolo, quindi restituire qualcosa da quella riga. Va bene, in questo caso, quello che voglio è la seconda colonna di quella riga. E poi alla fine di ogni CERCA.VERT dobbiamo mettere FALSO o 0. Ora proprio qui, dopo aver scelto l'intervallo, premerò il tasto F4, quindi voglio la seconda colonna e poi FALSO per un'esatta incontro. Non scegliere mai una corrispondenza approssimativa, mai, mai! Non è una corrispondenza approssimativa, è una cosa molto speciale, non funziona sempre. Se desideri riaffermare i tuoi numeri alla Securities and Exchange Commission, sentiti libero di usare tutti i mezzi,VERO o semplicemente lasciare, FALSO spento. Ma ogni CERCA.VERT che crei dovrebbe terminare con, FALSE o, 0, 0 è più breve di FALSE, dovresti mettere un FALSE lì, ma uno 0 è la stessa cosa di FALSE.
Va bene ora, risoluzione dei problemi, prima cosa, quando esegui un sacco di CERCA.VERT, è molto normale avere un paio di # N / A, giusto? E trovo sempre i # N / A andando su Dati, ZA, che porta il # N / A in alto, proprio lì, BG33-9, o è un errore di battitura o è un oggetto nuovo di zecca, va bene, e abbiamo per capirlo. Quindi qui a destra ho i nuovi dati, li taglierò, e poi Alt + IED, inserisco quelle celle nel mezzo, non deve essere alfabetico, questa tabella non deve essere ordinata. Quando usi la versione FALSE, la tabella non lo è: puoi semplicemente metterla dove vuoi, non l'ho messa alla fine perché non dovevo riscrivere la formula, voglio solo che la formula lavoro. Va bene, quindi un paio di # N / A, estremamente, estremamente normale, ma dai un'occhiata.
Quando inizi con risposte che funzionano, ma poi i # N / A iniziano ad apparire leggermente frequentemente, e poi alla fine appaiono fino in fondo, questo è un segno sicuro che non hai bloccato il riferimento alla tabella. Va bene vedi, quindi qui la tabella si muove mentre copio la formula verso il basso, giusto, e quindi ho avuto la fortuna di colpirne alcune che erano alla fine della lista. Ma alla fine arrivo al punto in cui sta guardando qui in celle completamente vuote, e ovviamente non viene trovato nulla. Va bene, quindi questa è la prima cosa, ottieni un paio che funzionano, alcuni # N / A, un altro paio che funzionano, e poi tutti i # N / A nel resto della strada - segno sicuro che non hai inserito il $ in.
Torna indietro, F2 per la modalità Modifica, seleziona i due punti, premi F4, che inserisce tutti i $, fai doppio clic per abbatterlo e le cose ricominceranno a funzionare, ok. Il prossimo, esattamente la stessa formula, la formula è perfetta, BG33-8 vedi, non abbiamo niente di tutto ciò giusto. Va bene, quindi vado a vedere, BG33-8, ehi, eccolo, è proprio lì, è in rosso, avrei dovuto vederlo! Quindi arrivo a questo sul lato destro, premo F2 e guardo il punto di inserimento lampeggiante, e vedo, è subito dopo l'8, così, e poi vengo qui e premo F2, ci sono alcuni spazi finali lì. Questo è molto, molto comune ai tempi di COBOL, dicevano "Sai, guarda, ti daremo 10 spazi per il numero di articolo e se non digiti tutti e 10 gli spazi riempiranno gli spazi . " E quindi questo è molto comune,e la grande soluzione qui è sbarazzarsi di quegli spazi iniziali e finali con la funzione TRIM. Invece di A2 usa il TRIM (A2), fai doppio clic per abbatterlo, va bene, ma il BG33-9 è ancora nell'altro tavolo.
Va bene, solo così capisci come funziona TRIM, quindi ho digitato un mucchio di spazi, John, un mucchio di spazi, Durran e un mucchio di spazi, e poi ho concatenato un * prima e dopo in modo che tu possa vedere come appare . Quando chiedo il TRIM (P4), ci liberiamo di tutti gli spazi iniziali, tutti gli spazi finali e quindi gli spazi interni multipli vengono ridotti a uno spazio. Va bene, così puoi vedere, in un certo senso visualizzare lì, che si stanno sbarazzando degli spazi iniziali e finali, qualsiasi spazio raddoppiato del centro diventa un unico spazio come quello. Quindi TRIM, ottimo, ottimo strumento nel tuo arsenale, va bene, eccone un altro molto comune.
Se non sono gli spazi finali, la cosa più comune che ho visto è dove qui abbiamo i numeri veri e qui abbiamo i numeri memorizzati come testo. E CERCA.VERT non lo vedrà come una corrispondenza, anche se 4399, questo è un numero, questo è testo, non funziona. Il modo più veloce per convertire una colonna di testo in numeri, seleziona la colonna, 3 lettere in sequenza, alt = "" DEF, e all'improvviso i nostri CERCA.VERT ricominciano a funzionare, ottimo, ottimo suggerimento da circa 1500 podcast fa. Va bene, quindi questi sono i problemi di CERCA.VERT più comuni, o hai dimenticato $, o hai spazi finali, o hai numeri e numeri memorizzati come testo. Tutti questi suggerimenti sono in questo libro "MrExcel XL", fare clic su "i" nell'angolo in alto a destra, è possibile acquistare il libro.
Perfetto, breve riepilogo: CERCA.VERT risolve molti problemi, se un CERCA.VERT inizia a funzionare ma # N / A! diventa più prominente, ti sei dimenticato di mettere $ nella tabella di ricerca. Se ci sono alcuni # N / A, sono solo gli elementi mancanti dalla tabella. Se nessuno dei CERCA.VERT funziona ed è testo, controlla gli spazi finali, puoi usare la funzione TRIM. Se hai numeri e numeri memorizzati come testo, seleziona una delle colonne, quella che contiene il testo, quindi alt = "" DEF riporta tutti quelli ai numeri.
Va bene ehi, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2027.xlsx
