Qui és massa mandrós o no té temps per llegir: mira el vídeo. Els detalls i els matisos es troben al text següent.
Formulació del problema
Així doncs, tenim dues taules: taula de comandes и llistat de preus:
La tasca consisteix a substituir els preus de la llista de preus a la taula de comandes de manera automàtica, centrant-se en el nom del producte per poder calcular el cost posteriorment.
Solució
Al conjunt de funcions d'Excel, sota la categoria Referències i matrius (Cerca i referència) hi ha una funció VPR (VISUALITZACIÓ).Aquesta funció busca un valor determinat (en el nostre exemple, aquesta és la paraula "Pomes") a la columna més esquerra de la taula especificada (llista de preus) movent-se de dalt a baix i, després de trobar-lo, mostra el contingut de la cel·la adjacent. (23 rubles). Esquemàticament, el funcionament d'aquesta funció es pot representar Així:
Per facilitar l'ús posterior de la funció, feu una cosa alhora: doneu el vostre nom al rang de cel·les de la llista de preus. Per fer-ho, seleccioneu totes les cel·les de la llista de preus excepte la "capçalera" (G3: H19), seleccioneu al menú Insereix – Nom – Assigna (Insereix — Nom — Definiu) o premi CTRL + F3 i introduïu qualsevol nom (sense espais) com preu… Ara, en el futur, podeu utilitzar aquest nom per enllaçar a la llista de preus.
Ara fem servir la funció VPR… Seleccioneu la cel·la on s'introduirà (D3) i obriu la pestanya Fórmules – Inserció de funcions (Fórmules: funció d'inserció)… A la categoria Referències i matrius (Cerca i referència) trobar la funció VPR (VISUALITZACIÓ) i premi OK... Apareixerà una finestra per introduir arguments per a la funció:
Els omplim al seu torn:
- Valor desitjat (Valor de cerca) – el nom del producte que la funció hauria de trobar a la columna més esquerra de la llista de preus. En el nostre cas, la paraula "Pomes" de la cel·la B3.
- Taula (Matriu de taula) – una taula de la qual es treuen els valors desitjats uXNUMXbuXNUMX, és a dir, la nostra llista de preus. Com a referència, utilitzem el nostre propi nom "Preu" donat anteriorment. Si no heu donat un nom, només podeu seleccionar la taula, però no us oblideu de prémer el botó F4per fixar l'enllaç amb signes de dòlar, perquè en cas contrari, es lliscarà cap avall quan copieu la nostra fórmula a la resta de cel·les de la columna D3:D30.
- Número_columna (Número d'índex de la columna) – número de sèrie (no una lletra!) De la columna de la llista de preus de la qual agafarem els valors dels preus. La primera columna de la llista de preus amb els noms està numerada 1, per tant necessitem el preu de la columna numerada 2.
- interval_lookup (Cerca d'interval) – només es poden introduir dos valors en aquest camp: FALSE o TRUE:
- Si s'introdueix un valor 0 or ESTIRAT (FALS), de fet, això vol dir que només es permet la cerca coincidència exacta, és a dir, si la funció no troba l'article no estàndard especificat a la taula de comandes a la llista de preus (si s'introdueix "Coco", per exemple), generarà l'error #N/A (sense dades).
- Si s'introdueix un valor 1 or VERITABLE (VERTADER), això vol dir que permeteu la cerca no per l'exacta, sinó coincidència aproximada, és a dir, en el cas de “coco”, la funció intentarà trobar un producte amb un nom el més proper possible a “coco” i tornarà el preu d'aquest nom. En la majoria dels casos, aquesta substitució aproximada pot jugar una mala passada a l'usuari substituint el valor del producte equivocat que hi havia realment! Per tant, per a la majoria dels problemes empresarials reals, és millor no permetre la cerca aproximada. L'excepció és quan busquem números i no text, per exemple, quan calculem els descomptes per passos.
Tot! Queda per pressionar OK i copieu la funció introduïda a tota la columna.
# N/A errors i la seva supressió
function VPR (VISUALITZACIÓ) retorna #N/A error (#N/A) si a:
- Cerca exacta activada (argument Vista d'interval = 0) i el nom desitjat no hi és Taula.
- Cerca aproximada inclosa (Vista d'interval = 1), però Taula, en què s'està duent a terme la cerca no s'ordena en ordre ascendent de noms.
- El format de la cel·la d'on prové el valor requerit del nom (per exemple, B3 en el nostre cas) i el format de les cel·les de la primera columna (F3: F19) de la taula són diferents (per exemple, numèrics i de text). ). Aquest cas és especialment típic quan s'utilitzen codis numèrics (números de compte, identificadors, dates, etc.) en lloc de noms de text. En aquest cas, podeu utilitzar les funcions Ч и TEXT per convertir formats de dades. Es veurà com això:
=CERCA V(TEXT(B3),preu,0)
Podeu llegir més sobre això aquí.
- La funció no pot trobar el valor necessari perquè el codi conté espais o caràcters invisibles no imprimibles (salts de línia, etc.). En aquest cas, podeu utilitzar funcions de text TRIM (TRIM) и IMPRIMIR(NET) per eliminar-los:
=CERCVA V(TALTRES (NETE(B3)), preu, 0)
=CERCA V(TALLADA(NETEJA(B3));preu;0)
Per suprimir el missatge d'error # N / A (#N/A) en els casos en què la funció no trobi una coincidència exacta, podeu utilitzar la funció SI ERROR (SI ERROR)… Així, per exemple, aquesta construcció intercepta qualsevol error generat per la BUSCAR V i els substitueix per zeros:
= SI ERROR (BUSCAR V (B3, preu, 2, 0), 0)
= SI ERROR (CERCA V (B3; preu; 2; 0); 0)
PS
Si necessiteu extreure no un valor, sinó tot el conjunt alhora (si n'hi ha diversos de diferents), haureu de xamanitzar amb la fórmula de matriu. o utilitzeu la nova funció XLOOKUP d'Office 365.
- Una versió millorada de la funció BUSCARV (LOOKUP 2).
- Càlcul ràpid dels descomptes de pas (interval) mitjançant la funció BUSCARV.
- Com fer una "CERCA V esquerra" mitjançant les funcions INDEX i MATCH
- Com utilitzar la funció BUSCAR V per omplir els formularis amb dades de la llista
- Com treure no el primer, sinó tots els valors de la taula alhora
- Funcions VLOOKUP2 i VLOOKUP3 del complement PLEX