contingut
Recentment hem dedicat un article a una de les funcions d'Excel més útils anomenades VPR i va mostrar com es pot utilitzar per extreure la informació necessària d'una base de dades a una cel·la del full de treball. També hem esmentat que hi ha dos casos d'ús per a la funció VPR i només un d'ells tracta les consultes de bases de dades. En aquest article, aprendràs una altra manera menys coneguda d'utilitzar la funció VPR a Excel.
Si encara no ho heu fet, assegureu-vos de llegir l'últim article sobre la funció VPR, perquè tota la informació següent suposa que ja esteu familiaritzat amb els principis descrits al primer article.
Quan es treballa amb bases de dades, funcions VPR es passa un identificador únic, que s'utilitza per identificar la informació que volem trobar (per exemple, un codi de producte o un número d'identificació de client). Aquest codi únic ha d'estar present a la base de dades, en cas contrari VPR informarà d'un error. En aquest article, veurem aquesta manera d'utilitzar la funció VPRquan l'identificador no existeix a la base de dades. Com si la funció VPR canviem al mode aproximat i tria quines dades ens proporcionarà quan volem trobar alguna cosa. En determinades circumstàncies, això és exactament el que es necessita.
Un exemple de la vida. Ens posem la tasca
Il·lustrem aquest article amb un exemple real: càlcul de comissions en funció d'una àmplia gamma de mètriques de vendes. Començarem amb una opció molt senzilla, i després la complicarem a poc a poc fins que l'única solució racional al problema sigui utilitzar la funció VPR. L'escenari inicial per a la nostra tasca fictícia és el següent: si un venedor guanya més de 30000 dòlars en vendes en un any, la seva comissió és del 30%. En cas contrari, la comissió és només del 20%. Posem-ho en forma de taula:
El venedor introdueix les seves dades de vendes a la cel·la B1 i la fórmula de la cel·la B2 determina la taxa de comissió correcta que el venedor pot esperar. Al seu torn, la taxa resultant s'utilitza a la cel·la B3 per calcular la comissió total que hauria de rebre el venedor (simplement multiplicant les cel·les B1 i B2).
La part més interessant de la taula es troba a la cel·la B2: aquesta és la fórmula per determinar la taxa de comissió. Aquesta fórmula conté una funció d'Excel anomenada IF (SI). Per a aquells lectors que no estiguin familiaritzats amb aquesta funció, explicaré com funciona:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Condició és un argument de funció que pren el valor de qualsevol CODI VERITAT (VERTADER), o FALS (FALS). A l'exemple anterior, l'expressió B1
És cert que B1 és menor que B5?
O pots dir-ho diferent:
És cert que l'import total de les vendes de l'any és inferior al valor llindar?
Si responem a aquesta pregunta SÍ (TRUE), aleshores la funció torna valor si és cert (valor si és TRUE). En el nostre cas, aquest serà el valor de la cel·la B6, és a dir, la taxa de comissió quan les vendes totals estiguin per sota del llindar. Si responem a la pregunta NO (FALSE) després torna valor si és fals (valor si és FAL). En el nostre cas, aquest és el valor de la cel·la B7, és a dir, la taxa de comissió quan les vendes totals estan per sobre del llindar.
Com podeu veure, si agafem les vendes totals de 20000 dòlars, obtenim una taxa de comissió del 2% a la cel·la B20. Si introduïm un valor de 40000 dòlars, la taxa de comissió canviarà un 30%:
Així funciona la nostra taula.
Ens compliquem la tasca
Posem les coses una mica més difícils. Establim un altre llindar: si el venedor guanya més de 40000 dòlars, la taxa de comissió augmenta fins al 40%:
Sembla que tot és senzill i clar, però la nostra fórmula a la cel·la B2 es fa notablement més complicada. Si observeu de prop la fórmula, veureu que el tercer argument de la funció IF (IF) es va convertir en una altra funció de ple dret IF (SI). Aquesta construcció s'anomena nidificació de funcions entre si. Excel permet feliçment aquestes construccions, i fins i tot funcionen, però són molt més difícils de llegir i entendre.
No aprofundirem en els detalls tècnics: per què i com funciona, i no entrarem en els matisos d'escriure funcions imbricades. Després de tot, aquest és un article dedicat a la funció VPR, no és una guia completa d'Excel.
Sigui com sigui, la fórmula es fa més complicada! Què passa si introduïm una altra opció per a una comissió del 50% per als venedors que guanyen més de 50000 dòlars en vendes. I si algú ha venut més de 60000 dòlars, pagarà el 60% de comissió?
Ara la fórmula de la cel·la B2, encara que s'ha escrit sense errors, s'ha tornat completament il·legible. Crec que són pocs els que volen utilitzar fórmules amb 4 nivells de nidificació en els seus projectes. Hi deu haver una manera més fàcil?!
I hi ha una manera així! La funció ens ajudarà VPR.
Apliquem la funció BUSCAR V per resoldre el problema
Canviem una mica el disseny de la nostra taula. Mantendrem tots els mateixos camps i dades, però els organitzarem d'una manera nova i més compacta:
Preneu-vos un moment i assegureu-vos que la nova taula Taula de tarifes inclou les mateixes dades que la taula de llindars anterior.
La idea principal és utilitzar la funció VPR per determinar la tarifa aranzelària desitjada segons la taula Taula de tarifes en funció del volum de vendes. Tingueu en compte que el venedor pot vendre béns per un import que no sigui igual a un dels cinc llindars de la taula. Per exemple, podria vendre per 34988 dòlars, però no hi ha aquesta quantitat. Vegem com funciona VPR pot fer front a una situació així.
S'està inserint una funció BUSCARV
Seleccioneu la cel·la B2 (on volem inserir la nostra fórmula) i cerqueu BÚSQUEDA V (VLOOKUP) a la biblioteca de funcions d'Excel: Fórmules (fórmules) > Biblioteca de funcions (Biblioteca de funcions) > Cerca i referència (Referències i matrius).
Apareix un quadre de diàleg Arguments de la funció (Arguments de funció). Omplim els valors dels arguments un per un, començant per Cerca_valor (Valor_de_cerca). En aquest exemple, aquesta és la quantitat total de vendes de la cel·la B1. Posa el cursor al camp Cerca_valor (Lookup_value) i seleccioneu la cel·la B1.
A continuació, heu d'especificar les funcions VPRon buscar dades. En el nostre exemple, aquesta és una taula Taula de tarifes. Posa el cursor al camp Matriu_taula (Taula) i seleccioneu tota la taula Taula de tarifesexcepte les capçaleres.
A continuació, hem d'especificar de quina columna extreure les dades mitjançant la nostra fórmula. Ens interessa la taxa de comissió, que es troba a la segona columna de la taula. Per tant, per l'argument Col_index_num (Column_number) introduïu el valor 2.
I finalment, introduïm l'últim argument: Cerca_interval (Interval_lookup).
Important: és l'ús d'aquest argument el que marca la diferència entre les dues maneres d'aplicar la funció VPR. Quan es treballa amb bases de dades, l'argument Cerca_interval (range_lookup) sempre ha de tenir un valor FALS (FALSE) per cercar una coincidència exacta. En el nostre ús de la funció VPR, hem de deixar aquest camp en blanc o introduir un valor CODI VERITAT (CERTADER). És molt important triar aquesta opció correctament.
Per aclarir-ho, us presentarem CODI VERITAT (VERTADER) al camp Cerca_interval (Interval_lookup). Encara que, si deixeu el camp en blanc, això no serà un error, ja que CODI VERITAT és el seu valor per defecte:
Hem omplert tots els paràmetres. Ara premem OK, i Excel ens crea una fórmula amb una funció VPR.
Si experimentem amb diversos valors diferents per a l'import total de les vendes, ens assegurarem que la fórmula funcioni correctament.
Conclusió
Quan la funció VPR treballa amb bases de dades, argument Cerca_interval (range_lookup) ha d'acceptar FALS (FALS). I el valor introduït com a Cerca_valor (Lookup_value) ha d'existir a la base de dades. En altres paraules, busca una coincidència exacta.
En l'exemple que hem vist en aquest article, no cal obtenir una coincidència exacta. Aquest és el cas quan la funció VPR ha de canviar al mode aproximat per retornar el resultat desitjat.
Per exemple: Volem determinar quina tarifa cal utilitzar en el càlcul de la comissió per a un venedor amb un volum de vendes de 34988 $. Funció VPR ens retorna un valor del 30%, que és absolutament correcte. Però, per què la fórmula va seleccionar la fila que conté exactament el 30% i no el 20% o el 40%? Què s'entén per cerca aproximada? Siguem clars.
Quan l'argument Cerca_interval (interval_lookup) té un valor CODI VERITAT (VERTADER) o s'ha omès, funció VPR itera per la primera columna i selecciona el valor més gran que no supera el valor de cerca.
Punt important: Perquè aquest esquema funcioni, la primera columna de la taula s'ha d'ordenar en ordre ascendent.