Chi è troppo pigro o non ha tempo per leggere – guarda il video. Dettagli e sfumature sono nel testo qui sotto.
Formulazione del problema
Quindi, abbiamo due tabelle - tabella degli ordini и listino prezzi:
Il compito è quello di sostituire automaticamente i prezzi dal listino nella tabella degli ordini, concentrandosi sul nome del prodotto in modo da poter poi calcolare il costo.
Soluzione
Nel set di funzioni di Excel, sotto la categoria Riferimenti e array (Ricerca e riferimento) c'è una funzione VPR (CERCA.VERT).Questa funzione cerca un dato valore (nel nostro esempio è la parola “Mele”) nella colonna più a sinistra della tabella specificata (listino prezzi) spostandosi dall'alto verso il basso e, una volta trovato, visualizza il contenuto della cella adiacente (23 rubli). Schematicamente, il funzionamento di questa funzione può essere rappresentato Quindi:
Per facilitare l'ulteriore utilizzo della funzione, fai una cosa alla volta: assegna il tuo nome all'intervallo di celle nel listino prezzi. Per fare ciò, seleziona tutte le celle del listino ad eccezione della “testata” (G3: H19), seleziona dal menu Inserisci – Nome – Assegna (Inserisci — Nome — Definisci) oppure premere CTRL + F3 e inserisci qualsiasi nome (senza spazi) come Prezzo… Ora, in futuro, puoi utilizzare questo nome per collegarti al listino prezzi.
Ora usiamo la funzione VPR… Selezionare la cella in cui verrà inserita (D3) e aprire la scheda Formule – Inserimento di funzioni (Formule — Funzione di inserimento)… Nella categoria Riferimenti e array (Ricerca e riferimento) trova la funzione VPR (CERCA.VERT) e premere OK… Apparirà una finestra per inserire gli argomenti per la funzione:
Li riempiamo a turno:
- Valore desiderato (Valore di ricerca) – il nome del prodotto che la funzione dovrebbe trovare nella colonna più a sinistra del listino. Nel nostro caso, la parola "Mele" dalla cella B3.
- Table (Matrice di tabella) – una tabella da cui sono presi i valori desiderati uXNUMXbuXNUMXbare, ovvero il nostro listino prezzi. Per riferimento, utilizziamo il nostro nome "Prezzo" fornito in precedenza. Se non hai fornito un nome, puoi semplicemente selezionare la tabella, ma non dimenticare di premere il pulsante F4appuntare il collegamento con i segni del dollaro, perché altrimenti scorrerà verso il basso durante la copia della nostra formula nel resto delle celle nella colonna D3:D30.
- Numero_colonna (Numero di indice della colonna) – numero di serie (non una lettera!) della colonna del listino prezzi da cui prenderemo i valori dei prezzi. La prima colonna del listino con i nomi è numerata 1, quindi abbiamo bisogno del prezzo dalla colonna numerata 2.
- ricerca_intervallo (Ricerca intervallo) – in questo campo possono essere inseriti solo due valori: FALSE o TRUE:
- Se viene inserito un valore 0 or GIACENTE (FALSO), quindi in effetti questo significa che è consentita solo la ricerca corrispondenza esatta, ovvero se la funzione non trova a listino l'articolo fuori standard specificato nella tabella dell'ordine (se si inserisce “Coconut” ad esempio), genererà l'errore #N/A (nessun dato).
- Se viene inserito un valore 1 or TRUE (VERO), quindi questo significa che consenti la ricerca non dell'esatto, ma corrispondenza approssimativa, ovvero nel caso di “cocco”, la funzione cercherà di trovare un prodotto con un nome il più vicino possibile a “cocco” e restituirà il prezzo per tale nome. Nella maggior parte dei casi, una sostituzione così approssimativa può giocare un brutto scherzo all'utente sostituendo il valore del prodotto sbagliato che era effettivamente lì! Quindi, per la maggior parte dei problemi aziendali reali, è meglio non consentire la ricerca approssimativa. L'eccezione è quando cerchiamo numeri e non testo, ad esempio quando calcoliamo gli sconti per gradi.
Tutto quanto! Resta da premere OK e copia la funzione inserita nell'intera colonna.
# N/A errori e loro soppressione
Funzione VPR (CERCA.VERT) restituisce l'errore #N/D (#N / A) se una:
- Ricerca esatta abilitata (argomento Vista intervallo = 0) e il nome desiderato non è presente Table.
- Ricerca grossolana inclusa (Vista intervallo = 1), ma Table, in cui è in corso la ricerca non è ordinato in ordine crescente di nomi.
- Il formato della cella da cui deriva il valore richiesto del nome (ad esempio B3 nel nostro caso) e il formato delle celle della prima colonna (F3:F19) della tabella sono diversi (ad esempio numerico e testo ). Questo caso è particolarmente tipico quando si utilizzano codici numerici (numeri di conto, identificatori, date, ecc.) invece di nomi di testo. In questo caso è possibile utilizzare le funzioni Ч и TESTO per convertire i formati di dati. Sembrerà qualcosa del genere:
= CERCA.VERT(TESTO(B3),prezzo,0)
Puoi leggere di più su questo qui.
- La funzione non riesce a trovare il valore richiesto perché il codice contiene spazi o caratteri non stampabili invisibili (interruzioni di riga, ecc.). In questo caso, è possibile utilizzare le funzioni di testo TRIM (ORDINARE) и STAMPARE(PULIRE) per rimuoverli:
= CERCA.VERT(TRIM SPAZI(PULITO(B3)),prezzo,0)
= CERCA.VERT(TRIM(PULIZIA(B3));prezzo;0)
Per eliminare il messaggio di errore #N / A (#N / A) nei casi in cui la funzione non riesce a trovare una corrispondenza esatta, è possibile utilizzare la funzione IFERROR (SE ERRORE)… Quindi, ad esempio, questa costruzione intercetta eventuali errori generati dal VLOOKUP e li sostituisce con zeri:
= SEERRORE (VLOOKUP (B3, prezzo, 2, 0), 0)
= SEERRORE (VLOOKUP (B3; prezzo; 2; 0); 0)
PS
Se hai bisogno di estrarre non un valore, ma l'intero set in una volta (se ce ne sono diversi diversi), dovrai sciamanizzare con la formula dell'array. oppure usa la nuova funzionalità XLOOKUP di Office 365.
- Una versione migliorata della funzione CERCA.VERT (CERCA.VERT 2).
- Calcolo rapido degli sconti per gradini (range) utilizzando la funzione CERCA.VERT.
- Come creare un "VLOOKUP sinistro" utilizzando le funzioni INDEX e MATCH
- Come utilizzare la funzione CERCA.VERT per compilare i moduli con i dati dell'elenco
- Come tirare fuori non il primo, ma tutti i valori dalla tabella in una volta
- Funzioni VLOOKUP2 e VLOOKUP3 dal componente aggiuntivo PLEX