En combinar la funció BUSCAR V d'Excel amb la funció COLUMNA, podeu crear una fórmula de cerca que retorni diversos valors d'una sola fila d'una base de dades o taula de dades. Obteniu informació sobre com crear una fórmula de cerca que retorni diversos valors d'un sol registre de dades.
Les instruccions d'aquest article s'apliquen a Excel 2019, 2016, 2013, 2010; i Excel per a Microsoft 365.
Conclusió
La fórmula de cerca requereix que la funció COLUMN estigui imbricada dins de BUSCAR V. Nidificar una funció implica introduir la segona funció com un dels arguments de la primera funció.
Introduïu les dades del tutorial
En aquest tutorial, la funció COLUMN s'introdueix com a argument del número d'índex de columna per a BUSCAR V. L'últim pas del tutorial consisteix a copiar la fórmula de cerca a columnes addicionals per recuperar valors addicionals per a la part escollida.
El primer pas d'aquest tutorial és introduir les dades en un full de treball Excel. Per seguir els passos d'aquest tutorial, introduïu les dades que es mostren a la imatge següent a les cel·les següents:
- Introduïu l'interval superior de dades a les cel·les D1 a G1.
- Introduïu el segon interval a les cel·les D4 a G10.
Els criteris de cerca i la fórmula de cerca creats en aquest tutorial s'introdueixen a la fila 2 del full de treball.
Aquest tutorial no inclou el format bàsic d'Excel que es mostra a la imatge, però això no afecta el funcionament de la fórmula de cerca.
Creeu un interval amb nom per a la taula de dades
Un interval amb nom és una manera senzilla de fer referència a un interval de dades en una fórmula. En lloc d'escriure les referències de cel·les per a les dades, escriviu el nom de l'interval.
Un segon avantatge d'utilitzar un interval amb nom és que les referències de cel·les d'aquest interval no canvien mai fins i tot quan la fórmula es copia a altres cel·les del full de treball. Els noms d'interval són una alternativa a l'ús de referències de cel·les absolutes per evitar errors en copiar fórmules.
El nom de l'interval no inclou els encapçalaments ni els noms de camp de les dades (com es mostra a la fila 4), només les dades.
-
Res alta cel·les D5 a G10 al full de treball.
Image -
Coloqueu el cursor al quadre de nom situat a sobre de la columna A, escriviu Taula i, a continuació, premeu Retorn. Les cel·les D5 a G10 tenen el nom d'interval Taula.
Image - El nom de l'interval per a l'argument de la matriu de la taula BUSCAR V s'utilitza més endavant en aquest tutorial.
Obre el quadre de diàleg BUSCAR V
Tot i que és possible escriure la fórmula de cerca directament a una cel·la d'un full de treball, a moltes persones els costa mantenir la sintaxi recta, especialment per a una fórmula complexa com la que s'utilitza en aquest tutorial.
Com a alternativa, utilitzeu el quadre de diàleg Arguments de la funció BUSCAR V. Gairebé totes les funcions d'Excel tenen un quadre de diàleg on s'introdueixen cadascun dels arguments de la funció en una línia separada.
-
Seleccioneu cel·la E2 del full de treball. Aquesta és la ubicació on es mostraran els resultats de la fórmula de cerca bidimensional.
Image -
A la cinta, aneu a la pestanya Fórmules i seleccioneu Cerca i referència.
Image -
Seleccioneu BUSCARV per obrir el quadre de diàleg Arguments de funció.
Image - El quadre de diàleg Arguments de la funció és on s'introdueixen els paràmetres de la funció BUSCAR V.
Introduïu l'argument del valor de cerca
Normalment, el valor de cerca coincideix amb un camp de dades de la primera columna de la taula de dades. En aquest exemple, el valor de cerca fa referència al nom de la part sobre la qual voleu trobar informació. Els tipus de dades permesos per al valor de cerca són les dades de text, els valors lògics, els números i les referències de cel·les.
Referències de cel·les absolutes
Quan es copien fórmules a Excel, les referències de cel·les canvien per reflectir la nova ubicació. Si això passa, D2, la referència de cel·la per al valor de cerca, canvia i crea errors a les cel·les F2 i G2.
Les referències de cel·les absolutes no canvien quan es copien les fórmules.
Per evitar errors, convertiu la referència de cel·la D2 en una referència de cel·la absoluta. Per crear una referència de cel·la absoluta, premeu la tecla F4. Això afegeix signes de dòlar al voltant de la referència de la cel·la, com ara $D$2.
-
Al quadre de diàleg Arguments de funció, col·loqueu el cursor al quadre de text lookup_value. A continuació, al full de treball, seleccioneu cel·la D2 per afegir aquesta referència de cel·la al lookup_value. La cel·la D2 és on s'introduirà el nom de la part.
Image -
Sense moure el punt d'inserció, premeu la tecla F4 per convertir D2 a la referència de cel·la absoluta $D$2.
Image - Deixeu obert el quadre de diàleg de la funció BUSCAR V per al següent pas del tutorial.
Introduïu l'argument de la matriu de la taula
Una matriu de taula és la taula de dades que la fórmula de cerca cerca per trobar la informació que voleu. La matriu de la taula ha de contenir almenys dues columnes de dades.
La primera columna conté l'argument del valor de cerca (que es va configurar a la secció anterior), mentre que la segona columna es cerca amb la fórmula de cerca per trobar la informació que especifiqueu.
L'argument de la matriu de la taula s'ha d'introduir com un interval que conté les referències de cel·les per a la taula de dades o com un nom d'interval.
Per afegir la taula de dades a la funció VLOOKUP, col·loqueu el cursor al quadre de text table_array del quadre de diàleg i escriviu Taulaper introduir el nom de l'interval per a aquest argument.
Ned the COLUMN Function
Normalment, BUSCAR V només retorna dades d'una columna d'una taula de dades. Aquesta columna s'estableix mitjançant l'argument del número d'índex de columna. En aquest exemple, però, hi ha tres columnes i el número d'índex de la columna s'ha de canviar sense editar la fórmula de cerca. Per aconseguir-ho, niu la funció COLUMN dins de la funció BUSCARV com a argument Col_index_num.
Quan nidifiquen funcions, Excel no obre el quadre de diàleg de la segona funció per introduir els seus arguments. La funció COLUMNA s'ha d'introduir manualment. La funció COLUMN només té un argument, l'argument Referència, que és una referència de cel·la.
La funció COLUMN retorna el número de la columna proporcionat com a argument de referència. Converteix la lletra de la columna en un número.
Per trobar el preu d'un article, utilitzeu les dades de la columna 2 de la taula de dades. Aquest exemple utilitza la columna B com a referència per inserir 2 a l'argument Col_index_num.
-
Al quadre de diàleg Arguments de funció, col·loqueu el cursor al quadre de text Col_index_num i escriviu COLUMN(. (Assegureu-vos d'incloure el suport rodó obert.)
Image -
Al full de treball, seleccioneu cel·la B1 per introduir aquesta referència de cel·la com a argument de referència.
Image - Escriviu un parèntesi rodó de tancament per completar la funció COLUMNA.
Introduïu l'argument de cerca d'interval VLOOKUP
L'argument Range_lookup de VLOOKUP és un valor lògic (VERTADER o FALS) que indica si BUSCARV ha de trobar una coincidència exacta o aproximada amb el valor_de_cerca.
- TRUE o Omès: BUSCARV retorna una coincidència semblant amb el valor_de_cerca. Si no es troba una coincidència exacta, BUSCARV retorna el següent valor més gran. Les dades de la primera columna de Table_array s'han d'ordenar en ordre ascendent.
- FALSE: VLOOKUP utilitza una coincidència exacta amb el Lookup_value. Si hi ha dos o més valors a la primera columna de Table_array que coincideixen amb el valor de cerca, s'utilitza el primer valor trobat. Si no es troba una coincidència exacta, es retorna un error N/A.
En aquest tutorial, es buscarà informació específica sobre un element de maquinari concret, de manera que Range_lookup s'estableix com a FALSE.
Al quadre de diàleg Arguments de funció, col·loqueu el cursor al quadre de text Range_lookup i escriviu False per dir-li a VLOOKUP que torni una coincidència exacta per a les dades.
Seleccioneu D'acord per completar la fórmula de cerca i tancar el quadre de diàleg. La cel·la E2 contindrà un error N/A perquè els criteris de cerca no s'han introduït a la cel·la D2. Aquest error és temporal. Es corregirà quan s'afegeixin els criteris de cerca a l'últim pas d'aquest tutorial.
Copieu la fórmula de cerca i introduïu els criteris
La fórmula de cerca recupera dades de diverses columnes de la taula de dades alhora. Per fer-ho, la fórmula de cerca ha de residir en tots els camps dels quals voleu informació.
Per recuperar dades de les columnes 2, 3 i 4 de la taula de dades (el preu, el número de peça i el nom del proveïdor), introduïu un nom parcial com a valor_de_cerca.
Com que les dades es presenten en un patró normal al full de treball, copieu la fórmula de cerca a cel·la E2 a cel·les F2 i G2 A mesura que es copia la fórmula, Excel actualitza la referència de cel·la relativa a la funció COLUMNA (cel·la B1) per reflectir la nova ubicació de la fórmula. Excel no canvia la referència de cel·la absoluta (com ara $D$2) i l'interval amb nom (taula) a mesura que es copia la fórmula.
Hi ha més d'una manera de copiar dades a Excel, però la manera més senzilla és utilitzar el mànec d'emplenament.
-
Seleccioneu cel·la E2, on es troba la fórmula de cerca, per convertir-la en la cel·la activa.
Image -
Arrossegueu el mànec d'emplenament cap a cel·la G2. Les cel·les F2 i G2 mostren l'error N/A present a la cel·la E2.
Image -
Per utilitzar les fórmules de cerca per recuperar informació de la taula de dades, al full de treball seleccioneu cel·la D2, escriviu Widget i premeu Introduïu.
Image La informació següent es mostra a les cel·les E2 a G2.
- E2: 14,76 $ - el preu d'un giny
- F2: PN-98769: el número de peça d'un widget
- G2: Widgets Inc.: el nom del proveïdor dels ginys
-
Per provar la fórmula de matriu BUSCAR V, escriviu el nom d' altres parts a la cel·la D2 i observeu els resultats a les cel·les E2 a G2.
Image - Cada cel·la que conté la fórmula de cerca conté una dada diferent sobre l'element de maquinari que heu cercat.
La funció BUSCAR V amb funcions imbricades com COLUMN ofereix un mètode potent per cercar dades dins d'una taula, utilitzant altres dades com a referència de cerca.
