En la segunda parte de nuestro tutorial sobre la función BUSCARV(BUSCARV) en Excel, analizaremos algunos ejemplos que te ayudarán a dirigir todo el poder BUSCARV para resolver las tareas de Excel más ambiciosas. Los ejemplos asumen que ya tiene un conocimiento básico de cómo funciona esta característica. Si no es así, quizás le interese comenzar con la primera parte de este tutorial, que explica la sintaxis y el uso básico. BUSCARV. Bueno, comencemos.
Buscar en Excel por múltiples criterios
Función BUSCARV en Excel es una herramienta realmente poderosa para realizar búsquedas de un valor particular en una base de datos. Sin embargo, existe una limitación significativa: su sintaxis le permite buscar solo un valor. ¿Qué sucede si desea buscar por múltiples condiciones? Encontrará la solución a continuación.
Ejemplo 1: Buscar por 2 criterios diferentes
Supongamos que tenemos una lista de pedidos y queremos encontrar Cantidad de bienes(cant.), basado en dos criterios: Nombre del cliente(Cliente) y el nombre del producto(Producto). El asunto se complica por el hecho de que cada uno de los compradores ordenó varios tipos de bienes, como se puede ver en la siguiente tabla:
función normal BUSCARV no funcionará en este escenario porque devolverá el primer valor que encuentre que coincida con el valor de búsqueda dado. Por ejemplo, si desea saber la cantidad de un artículo dulces ordenado por el comprador jeremy colina, escribe la siguiente fórmula:
BUSCARV(B1,$A$5:$C$14,3,FALSO)
=BUSCARV(B1,$A$5:$C$14,3,FALSO)
- esta fórmula devolverá el resultado 15 correspondiente al producto manzanas, porque es el primer valor que coincide.
Hay una solución simple: cree una columna adicional en la que combine todos los criterios deseados. En nuestro ejemplo, estas son las columnas Nombre del cliente(Cliente) y el nombre del producto(Producto). No olvide que la columna fusionada siempre debe ser la columna más a la izquierda en el rango de búsqueda, ya que es la columna de la izquierda que la función BUSCARV mira hacia arriba cuando busca un valor.
Entonces, agrega una columna auxiliar a la tabla y copia la siguiente fórmula en todas sus celdas: =B2&C2. Si desea que la cadena sea más legible, puede separar los valores combinados con un espacio: =B2&” “&C2. Después de eso, puedes usar la siguiente fórmula:
BUSCARV("Jeremy Hill Sweets",$A$7:$D$18,4,FALSO)
=BUSCARV("Jeremy Hill Sweets",$A$7:$D$18,4,FALSO)
BUSCARV(B1,$A$7:$D$18,4,FALSO)
=BUSCARV(B1,$A$7:$D$18,4,FALSO)
donde esta la celda B1 contiene el valor concatenado del argumento valor de búsqueda(buscar_valor) y 4 - argumento col_index_num(column_number), es decir el número de la columna que contiene los datos a recuperar.
Ejemplo 2: BUSCARV por dos criterios con la tabla que se ve en otra hoja
Si necesita actualizar la tabla principal (Tabla principal) agregando datos de la segunda tabla (Tabla de búsqueda), que se encuentra en otra hoja o en otro libro de Excel, puede recopilar el valor deseado directamente en la fórmula que inserta en la mesa principal.
Como en el ejemplo anterior, necesitará una columna auxiliar en la tabla de búsqueda con los valores combinados. Esta columna debe ser la columna más a la izquierda en el rango de búsqueda.
Entonces la fórmula con BUSCARV podría ser así:
BUSCARV(B2&" "&C2,Pedidos!$A&$2:$D$2,4,FALSO)
=BUSCARV(B2&" "Pedidos!$A&$2:$D$2;4;FALSO)
Aquí, las columnas B y C contienen nombres de clientes y nombres de productos, respectivamente, y el enlace Pedidos!$A&$2:$D$2 define una tabla para buscar en otra hoja.
Para que la fórmula sea más legible, puede darle un nombre al rango de vista y luego la fórmula se verá mucho más simple:
BUSCARV(B2&" "&C2,Órdenes,4,FALSO)
=BUSCARV(B2&" "Pedidos;4;FALSO)
Para que la fórmula funcione, los valores en la columna más a la izquierda de la tabla que se está viendo deben combinarse de la misma manera que en los criterios de búsqueda. En la figura anterior, combinamos los valores y pusimos un espacio entre ellos, tal como debe hacer en el primer argumento de la función (B2 & "" & C2).
¡Recuerda! Función BUSCARV limitado a 255 caracteres, no puede buscar un valor que tenga más de 255 caracteres. Tenga esto en cuenta y asegúrese de que la longitud del valor deseado no supere este límite.
Estoy de acuerdo en que agregar una columna auxiliar no es la solución más elegante y no siempre aceptable. Puede hacer lo mismo sin la columna auxiliar, pero eso requeriría una fórmula mucho más compleja con una combinación de funciones ÍNDICE(ÍNDICE) y JUEGO(JUEGO).
Extraemos el 2º, 3º, etc. valores usando BUSCARV
eso ya lo sabes BUSCARV puede devolver solo un valor coincidente, más precisamente, el primero encontrado. Pero, ¿qué sucede si este valor se repite varias veces en la matriz vista y desea extraer el segundo o el tercero? ¿Y si todos los valores? El problema parece complejo, ¡pero la solución existe!
Suponga que una columna de la tabla contiene los nombres de los clientes (Nombre del cliente) y la otra columna contiene los productos (Producto) que compraron. Tratemos de encontrar los artículos 2, 3 y 4 comprados por un cliente determinado.
La forma más fácil es agregar una columna auxiliar antes de la columna Nombre del cliente y rellénelo con los nombres de los clientes con el número de repetición de cada nombre, por ejemplo, Juan Pérez1, Juan Pérez2 etc. Haremos el truco con la numeración usando la función CONTAR.SI(COUNTIF), dado que los nombres de los clientes están en la columna B:
B2 Y CONTAR.SI ($ B $ 2: B2, B2)
=B2&CONTAR.SI($B$2:B2,B2)
Después de eso puedes usar la función normal. BUSCARV para encontrar el orden requerido. Por ejemplo:
- Encontramos 2do Dan Brown:
BUSCARV("Dan Brown2",$A$2:$C$16,3,FALSO)
=BUSCARV("Dan Brown2",$A$2:$C$16,3,FALSO) - Encontramos 3ro artículo pedido por el cliente Dan Brown:
BUSCARV("Dan Brown3",$A$2:$C$16,3,FALSO)
=BUSCARV("Dan Brown3",$A$2:$C$16,3,FALSO)
Si solo estás buscando 2do repetición, puede hacerlo sin la columna auxiliar creando una fórmula más compleja:
SI.ERROR(BUSCARV($F$2,INDIRECTO("$B$"&(COINCIDIR($F$2,Tabla4,0)+2)&":$C16"),2,FALSO),"")
=SI.ERROR(BUSCARV($F$2;INDIRECTO("$B$"&(COINCIDIR($F$2;Tabla4;0)+2)&":$C16");2;VERDADERO);"")
En esta fórmula:
- $F$2- una celda que contiene el nombre del comprador (no se modifica, tenga en cuenta que el enlace es absoluto);
- $B$- columna Nombre del cliente;
- Tabla4- Su mesa (este lugar también puede ser un rango regular);
- $C16- la celda final de su tabla o rango.
Esta fórmula encuentra solo el segundo valor coincidente. Si necesita extraer las repeticiones restantes, use la solución anterior.
Si necesita una lista de todas las coincidencias, la función BUSCARV esto no es un ayudante, ya que solo devuelve un valor a la vez: período. Pero Excel tiene una función. ÍNDICE(ÍNDICE), que puede hacer frente fácilmente a esta tarea. Cómo se verá esa fórmula, aprenderá en el siguiente ejemplo.
Recuperar todas las repeticiones del valor deseado
Como se ha mencionado más arriba, BUSCARV no puede extraer todos los valores duplicados del rango escaneado. Para hacer esto, necesita una fórmula un poco más compleja, compuesta por varias funciones de Excel, como ÍNDICE(ÍNDICE), PEQUEÑA(Pequeño y FILA(LÍNEA)
Por ejemplo, la siguiente fórmula encuentra todas las repeticiones del valor de la celda F2 en el rango B2:B16 y devuelve el resultado de las mismas filas en la columna C.
(=SI.ERROR(ÍNDICE($C$2:$C$16,PEQUEÑO(SI($F$2=B2:B16,FILA(C2:C16)-1,""),FILA()-3)),"") )
(=SI.ERROR(ÍNDICE($C$2:$C$16,PEQUEÑO(SI($F$2=B2:B16,FILA(C2:C16)-1,"");FILA()-3)),"") )
Ingrese esta fórmula de matriz en varias celdas adyacentes, como las celdas F4:F8 como se muestra en la siguiente figura. El número de celdas debe ser igual o superior al máximo número posible de repeticiones del valor buscado. No olvides hacer clic Ctrl+Mayús+Intro para ingresar la fórmula matricial correctamente.
Si está interesado en comprender cómo funciona, profundicemos un poco en los detalles de la fórmula:
Parte 1:
SI ($ F $ 2 = B2: B16, FILA (C2: C16) -1, "")
SI ($ F $ 2 = B2: B16; FILA (C2: C16) -1; "")
$F$2=B2:B16– comparar el valor de la celda F2 con cada uno de los valores del rango B2:B16. Si se encuentra una coincidencia, entonces la expresión CADENA(C2:C16)-1 devuelve el número de la línea correspondiente (valor -1 le permite no incluir la línea de encabezado). Si no hay coincidencias, la función SI(SI) devuelve una cadena vacía.
resultado de la función SI(SI) habrá tal matriz horizontal: (1","",3","5","","","","","","","12","","", "")
Parte 2:
FILA()-3
CADENA()-3
Aquí la función FILA(LINE) actúa como un contador adicional. Como la fórmula se copia en las celdas F4:F9, restamos el número 3 del resultado de la función para obtener el valor 1 en una celda F4(línea 4, restar 3) para obtener 2 en una celda F5(línea 5, resta 3) y así sucesivamente.
Parte 3:
PEQUEÑO(SI($F$2=B2:B16,FILA(C2:C16)-1,""),FILA()-3))
PEQUEÑO (SI ($ F $ 2 = B2: B16, FILA (C2: C16) -1, ""); FILA () - 3))
Función PEQUEÑA(PEQUEÑO) devuelve enésimo el valor más pequeño en la matriz de datos. En nuestro caso, qué posición (desde la más pequeña) devolver está determinada por la función FILA(LÍNEA) (ver Parte 2). Entonces, para una celda F4 función PEQUEÑO((matriz),1) devoluciones 1º elemento de matriz (más pequeño), es decir, 1 . para celular F5 devoluciones 2do el elemento más pequeño de la matriz, es decir 3 , y así.
Parte 4:
ÍNDICE ($ C $ 2: $ C $ 16, PEQUEÑO (SI ($ F $ 2 = B2: B16, FILA (C2: C16) -1, ""), FILA () - 3))
ÍNDICE ($ C $ 2: $ C $ 16; PEQUEÑO (SI ($ F $ 2 = B2: B16; FILA (C2: C16) -1; ""); FILA () - 3))
Función ÍNDICE(ÍNDICE) simplemente devuelve el valor de una celda específica en una matriz C2:C16. para celular F4 función ÍNDICE ($ C $ 2: $ C $ 16,1) regresará manzanas, por F5 función ÍNDICE ($ C $ 2: $ C $ 16; 3) regresará dulces y así.
Parte 5:
SI.ERROR()
SI.ERROR()
Finalmente, ponemos la fórmula dentro de la función. SI ERROR(IFERROR), porque es poco probable que esté satisfecho con el mensaje de error #N / A(#N/A) si la cantidad de celdas en las que se copia la fórmula es menor que la cantidad de valores duplicados en el rango que se está visualizando.
Búsqueda 2D por fila y columna conocidas
Realizar una búsqueda 2D en Excel implica buscar un valor por un número de fila y columna conocido. En otras palabras, está extrayendo el valor de la celda en la intersección de una fila y columna en particular.
Entonces, pasemos a nuestra tabla y escribamos una fórmula con una función BUSCARV, que encontrará información sobre el costo de los limones vendidos en marzo.
Hay varias formas de realizar una búsqueda 2D. Consulta las opciones y elige la que más te convenga.
Funciones BUSCARV y COINCIDIR
Puedes usar un montón de funciones. BUSCARV(BUSCARV) y JUEGO(COINCIDIR) para encontrar el valor en la intersección de los campos el nombre del producto(cadena) y Mes(columna) de la matriz en cuestión:
BUSCARV("Limones",$A$2:$I$9,COINCIDIR("Mar",$A$1:$I$1,0),FALSO)
=BUSCARV("Limones",$A$2:$I$9,COINCIDIR("Mar",$A$1:$I$1,0),FALSO)
La fórmula anterior es una función regular. BUSCARV, que busca una coincidencia exacta del valor "Limones" en las celdas A2 a A9. Pero como no sabe en qué columna están las ventas de marzo, no podrá establecer el número de columna para el argumento de la tercera función. BUSCARV. En su lugar, se utiliza la función JUEGO para definir esta columna.
COINCIDIR("marzo",$A$1:$I$1,0)
COINCIDIR("Mar";$A$1:$I$1;0)
Traducida al lenguaje humano, esta fórmula significa:
- Buscando personajes "Mar" - argumento valor de búsqueda(valor de búsqueda);
- Mirando en las celdas de A1 a I1 - argumento matriz_buscada(matriz_buscada);
- Devolver coincidencia exacta - argumento tipo de concordancia(tipo de concordancia).
Usando 0 en el tercer argumento dices funciones JUEGO busque el primer valor que coincida exactamente con el valor que está buscando. Esto es equivalente al valor FALSO(FALSO) para el cuarto argumento BUSCARV.
Así es como puede crear una fórmula de búsqueda bidireccional en Excel, también conocida como búsqueda bidimensional o búsqueda bidireccional.
función SUMAPRODUCTO
Función SUMAPRODUCTO(SUMAPRODUCTO) devuelve la suma de los productos de las matrices seleccionadas:
SUMAPRODUCTO(($A$2:$A$9="Limones")*($A$1:$I$1="Mar"),$A$2:$I$9)
=SUMAPRODUCTO(($A$2:$A$9="Limones")*($A$1:$I$1="Mar");$A$2:$I$9)
Funciones ÍNDICE y COINCIDIR
En el próximo artículo explicaré estas funciones en detalle, así que por ahora solo puedes copiar esta fórmula:
ÍNDICE ($ A $ 2: $ I $ 9, COINCIDIR ("Limones", $ A $ 2: $ A $ 9.0), COINCIDIR ("Mar", $ A $ 1: $ I $ 1.0))
=ÍNDICE($A$2:$I$9;COINCIDIR("Limones";$A$2:$A$9;0);COINCIDIR("Mar";$A$1:$I$1;0))
Rangos con nombre y el operador de intersección
Si no le gustan todas esas fórmulas complejas de Excel, puede que le guste esta manera visual y memorable:
Cuando ingresa un nombre, Microsoft Excel mostrará una información sobre herramientas con una lista de nombres coincidentes, al igual que cuando ingresa una fórmula.
- Hacer clic Ingresar y comprobar el resultado
En general, cualquiera que sea el método anterior que elija, el resultado de una búsqueda bidimensional será el mismo:
Uso de múltiples BUSCARV en una fórmula
Sucede que la tabla principal y la tabla de búsqueda no tienen una sola columna en común, y esto le impide usar la función habitual BUSCARV. Sin embargo, hay otra tabla que no contiene la información que nos interesa, pero tiene una columna común con la tabla principal y la tabla de búsqueda.
Echemos un vistazo al siguiente ejemplo. Tenemos una tabla principal con una columna. SKU (nuevo), donde desea agregar una columna con los precios correspondientes de otra tabla. Además, tenemos 2 tablas de búsqueda. El primero (Tabla de búsqueda 1) contiene números actualizados SKU (nuevo) y nombres de productos, y el segundo (Tabla de búsqueda 2) - nombres de productos y números antiguos SKU (antiguo).
Para agregar precios de la segunda tabla de búsqueda a la tabla principal, debe realizar una acción conocida como doble BUSCARV o anidado BUSCARV.
- escribir una función BUSCARV, que encuentra el nombre del producto en la tabla tabla de consulta 1 usando SKU, como el valor deseado:
BUSCARV(A2,Nuevo_SKU,2,FALSO)
=BUSCARV(A2;Nuevo_SKU;2;FALSO)Aquí Nuevo_SKU- rango con nombre $A:$B en la mesa tabla de consulta 1, a 2 - esta es la columna B, que contiene los nombres de los productos (ver la imagen de arriba)
- Escribir una fórmula para insertar precios de una tabla tabla de consulta 2 basado en nombres de productos bien conocidos. Para hacer esto, pegue la fórmula que creó anteriormente como el valor de búsqueda para nueva caracteristica BUSCARV:
BUSCARV(BUSCARV(A2,Nuevo_SKU,2,FALSO),Precio,3,FALSO)
=BUSCARV(BUSCARV(A2,Nuevo_SKU,2,FALSO),Precio,3,FALSO)Aquí precio- rango con nombre $A:$C en la mesa tabla de consulta 2, a 3 es la columna C que contiene los precios.
La siguiente figura muestra el resultado devuelto por la fórmula que creamos:
Sustitución dinámica de datos de diferentes tablas usando BUSCARV e INDIRECTO
Primero, aclaremos qué queremos decir con la expresión "Sustitución dinámica de datos de diferentes tablas" para asegurarnos de que nos entendemos correctamente.
Hay situaciones en las que hay varias hojas con datos del mismo formato, y es necesario extraer la información necesaria de una determinada hoja, dependiendo del valor que se ingresa en una celda determinada. Creo que es más fácil explicar esto con un ejemplo.
Imagina que tienes informes de ventas de varias regiones con los mismos productos y en el mismo formato. Quiere encontrar cifras de ventas para una región específica:
Si solo tiene dos informes de este tipo, puede usar una fórmula vergonzosamente simple con funciones BUSCARV y SI(SI) para seleccionar el informe deseado para buscar:
BUSCARV($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSO)
=BUSCARV($D$2,SI($D3="FL",FL_Sales,CA_Sales),2,FALSO)
- $D$2 es una celda que contiene el nombre del producto. Tenga en cuenta que aquí usamos referencias absolutas para evitar cambiar el valor de búsqueda al copiar la fórmula a otras celdas.
- $D3 es una celda con el nombre de la región. Estamos usando una referencia de columna absoluta y una referencia de fila relativa porque planeamos copiar la fórmula a otras celdas en la misma columna.
- FL_Sales y CA_Ventas– los nombres de las tablas (o rangos con nombre) que contienen los informes de ventas correspondientes. Por supuesto, puede usar los nombres de hoja habituales y las referencias de rango de celdas, por ejemplo 'Hoja FL'! $ A $ 3: $ B $ 10, pero los rangos con nombre son mucho más convenientes.
Sin embargo, cuando hay muchas tablas de este tipo, la función SI no es la mejor solución. En su lugar, puede utilizar la función INDIRECTO(INDIRECTO) para regresar al rango de búsqueda deseado.
Como usted probablemente sabe, la función INDIRECTO se usa para devolver un enlace dado por una cadena de texto, que es exactamente lo que necesitamos ahora. Entonces, reemplace audazmente en la fórmula anterior la expresión con la función SI vincular con la función INDIRECTO. Aquí hay una combinación BUSCARV y INDIRECTO funciona muy bien con:
BUSCARV($D$2,INDIRECTO($D3&"_Ventas"),2,FALSO)
=BUSCARV($D$2;INDIRECTO($D3&"_Ventas");2;FALSO)
- $D$2- esta es una celda con el nombre del producto, no se modifica debido al enlace absoluto.
- $D3 es la celda que contiene la primera parte del nombre de la región. En nuestro ejemplo, este Florida.
- _Ventas- la parte común del nombre de todos los rangos o tablas con nombre. Cuando se combina con el valor en la celda D3, forma el nombre completo del rango requerido. A continuación hay algunos detalles para aquellos que son nuevos en la función. INDIRECTO.
Cómo funcionan INDIRECTO y BUSCARV
Primero, déjame recordarte la sintaxis de la función. INDIRECTO(INDIRECTO):
- A1 si el argumento es CIERTO(VERDADERO) o no especificado;
- R1C1, si FALSE(FALSO).
Así que volvamos a nuestros informes de ventas. Si recuerda, cada informe es una tabla separada ubicada en una hoja separada. Para que la fórmula funcione correctamente, debe nombrar sus tablas (o rangos) y todos los nombres deben tener una parte común. Por ejemplo, así: CA_Ventas, FL_Ventas, TX_Ventas y así. Como puede ver, todos los nombres contienen "_Sales".
Función INDIRECTO conecta el valor en la columna D y la cadena de texto "_Sales", indicando así BUSCARV en qué tabla buscar. Si la celda D3 contiene el valor "FL", la fórmula buscará en la tabla FL_Ventas, si "CA" está en la tabla CA_Ventas y así.
El resultado de las funciones BUSCARV y INDIRECTO será el siguiente:
Si los datos se encuentran en diferentes libros de Excel, debe agregar el nombre del libro antes del rango con nombre, por ejemplo:
BUSCARV ($ D $ 2, INDIRECTO ($ D3 y "Libro de trabajo 1!_Ventas"), 2, FALSO)
=BUSCARV($D$2;INDIRECTO($D3&"Cuaderno1!_Ventas");2;FALSO)
Si la función INDIRECTO hace referencia a otro libro, ese libro debe estar abierto. Si está cerrado, la función informará de un error. #¡árbitro!(#¡ÁRBITRO!).
Tema: Toma de decisiones sobre indicadores de varios criterios.
En la práctica, normalmente hay que elegir decisión gerencial no por un criterio, sino por varios. Por lo tanto, sus valores en una valoración comparativa son multidireccionales, es decir, en un indicador, la alternativa gana y en otros pierde.
En estas condiciones, es necesario reducir el sistema considerado de evaluación de indicadores a uno complejo, sobre la base del cual se tomará una decisión.
Para construir una evaluación integral, se deben resolver dos problemas:
El primer problema es que los indicadores de criterio bajo consideración son de significado desigual;
El segundo problema se caracteriza por el hecho de que los indicadores se evalúan en diferentes unidades de medida y para construir evaluación integrada es necesario cambiar a un solo medidor.
El primer problema se resuelve aplicando una de las cuatro modificaciones del método. evaluaciones de expertos, a saber, el método de comparación por pares, que nos permite cuantificar el significado. La esencia del método de comparación por pares es que un experto (especialista, inversor potencial, consumidor) realiza una evaluación por pares de los indicadores de criterios en consideración, determinando por sí mismo su grado de importancia en forma de puntaje. Posteriormente, habiendo realizado el adecuado tratamiento de la información recibida, se calcula el coeficiente de significancia para cada uno de los indicadores de criterio considerados.
El segundo problema se resuelve utilizando un solo medidor para indicadores privados. La mayoría de las veces, una puntuación se usa como tal medidor. En este caso, la evaluación se lleva a cabo en dos enfoques:
- primer enfoque utilizado en ausencia de datos estadísticos sobre el valor de los indicadores bajo consideración;
- segundo enfoque se utiliza en presencia de datos estadísticos (límites de cambio) sobre el valor de los indicadores en consideración.
Cuando utilice el primer enfoque para convertir a puntos, proceda de la siguiente manera: mejor valor indicador considerado se toma igual a 1 punto, y los peores valores en acciones de este punto. Este enfoque es simple, da una evaluación objetiva, pero al mismo tiempo no tiene en cuenta los mejores logros que se encuentran fuera de las opciones consideradas.
Para eliminar esta deficiencia, se necesita información sobre los límites de cambio del indicador bajo consideración. Si está disponible, se utiliza el segundo enfoque. En este caso, se construye una escala de conversión para convertir a puntos. En este caso, el sistema de puntuación se selecciona utilizando las disposiciones de la teoría de la estadística según la fórmula de Sturges:
norte = 1 + 3,322 lgnorte, dónde
N es el número de observaciones estadísticas;
n es el sistema de puntuación aceptado obtenido mediante las reglas de redondeo.
La conversión en puntos se realiza sobre la base de la escala de conversión construida utilizando el procedimiento de interpolación de datos tabulares.
Ejercicio:
De las 6 opciones de soluciones alternativas, cada una de las cuales es evaluada por el 5º indicadores de criterios usted necesita elegir la mejor opción.
Evaluar usando 2 enfoques:
en ausencia de datos estadísticos sobre el valor de los indicadores en consideración;
si está disponible.
Se establecen límites de cambio de indicadores para el siguiente número de observaciones (N):
para variantes pares N = 8;
La evaluación de la importancia debe realizarse sobre la base de una evaluación por pares según el ejecutante.
Tabla 1.
Opciones de tareas
№ Tareas |
|||||
Nº de alternativas |
|||||
№ Tareas |
|||||
Nº de alternativas |
|||||
№ Tareas |
|||||
Nº de alternativas |
|||||
№ Tareas |
|||||
Nº de alternativas |
|||||
№ Tareas |
|||||
Nº de alternativas |
|||||
№ Tareas |
|||||
Nº de alternativas |
Tabla 2.
Datos iniciales:
Soluciones alternativas |
|||||||||||||||
indicadores |
A6 |
||||||||||||||
X 1 |
|||||||||||||||
X 2 |
|||||||||||||||
X 3 |
|||||||||||||||
X 4 |
|||||||||||||||
X 5 |
KPI es un indicador de desempeño que le permite evaluar objetivamente la efectividad de las acciones realizadas. Este sistema se utiliza para evaluar varios indicadores (las actividades de toda la empresa, estructuras individuales, especialistas específicos). Realiza no solo las funciones de control, sino que también estimula la actividad laboral. A menudo, un sistema de pago se construye sobre la base de KPI. Este es un método de formar una parte variable del salario.
Indicadores clave de rendimiento KPI: ejemplos en Excel
El factor estimulante en el sistema de motivación KPI es la recompensa monetaria. Puede ser recibido por el empleado que completó la tarea que se le asignó. El monto de la bonificación / bonificación depende del resultado de un empleado en particular en el período del informe. El monto de la remuneración puede ser fijo o expresado como un porcentaje del salario.
Cada empresa determina indicadores clave de desempeño y el peso de cada uno individualmente. Los datos dependen de las tareas de la empresa. Por ejemplo:
- El objetivo es proporcionar un plan de ventas de productos por un monto de 500,000 rublos por mes. El indicador clave es el plan de ventas. Sistema de medición: cantidad de ventas real / cantidad de ventas planificada.
- El objetivo es aumentar la cantidad de envío en el período en un 20%. El indicador clave es el monto promedio del envío. Sistema de medición: envío promedio real / envío promedio planificado.
- El objetivo es aumentar el número de clientes en un 15% en una determinada región. El indicador clave es el número de clientes en la base de datos de la empresa. Sistema de medición: número real de clientes / número previsto de clientes.
La empresa también determina la dispersión del coeficiente (pesos) de forma independiente. Por ejemplo:
- La implementación del plan por debajo del 80% es inaceptable.
- Ejecución del plan 100% - coeficiente 0,45.
- Implementación del plan 100-115% - un coeficiente de 0.005 por cada 5%.
- Sin errores - coeficiente 0,15.
- No hubo comentarios en el período del informe - coeficiente 0,15.
Es solamente variante posible determinación de coeficientes motivacionales.
El punto clave en la medición de KPI es la relación entre el indicador real y el planificado. Casi siempre salario empleado se compone de salario (parte fija) y plus (parte variable/modificable). El factor motivacional influye en la formación de la variable.
Supongamos que la proporción de las partes fijas y variables en el salario es de 50 × 50. Indicadores clave de desempeño y el peso de cada uno de ellos:
Aceptamos los siguientes valores de los coeficientes (lo mismo para el indicador 1 y el indicador 2):
Tabla de KPI en Excel:
Explicaciones:
Esta es una tabla de KPI de muestra en Excel. Cada empresa hace lo suyo (teniendo en cuenta las peculiaridades del trabajo y el sistema de bonificación).
Matriz KPI y Ejemplo en Excel
Para evaluar a los empleados sobre indicadores clave de desempeño, se elabora una matriz o acuerdo sobre objetivos. La forma general se ve así:
- Los indicadores clave son los criterios por los cuales se evalúa el trabajo del personal. Son diferentes para cada puesto.
- Los pesos son números en el rango de 0 a 1, cuya suma total es 1. Reflejan las prioridades de cada indicador clave, teniendo en cuenta los objetivos de la empresa.
- Base: el valor mínimo permitido del indicador. Por debajo de la línea de base - sin resultado.
- La norma es el nivel planificado. Lo que debe hacer un empleado. A continuación, el empleado no hizo frente a sus deberes.
- Una meta es un valor al que apuntar. Por encima de la norma, lo que permite mejorar los resultados.
- Hecho - los resultados reales del trabajo.
- El índice KPI muestra el nivel del resultado en relación con la norma.
Fórmula para calcular kpi:
Índice KPI = ((Hecho - Base) / (Norma - Base)) * 100%.
Un ejemplo de cómo completar una matriz para un gerente de oficina:
El índice de rendimiento es la suma de los productos de índices y pesos. La evaluación del desempeño de los empleados se muestra visualmente usando un formato condicional.
Formato condicional (5)
Listas y rangos (5)
Macros (procedimientos VBA) (63)
Varios (39)
Errores y fallas de Excel (4)
BUSCARV para dos o más criterios
Seguramente todos los que están familiarizados con la función BUSCARV saben que busca valores dados exclusivamente en la columna izquierda de la tabla especificada (puede encontrar más información sobre BUSCARV en el artículo: Cómo encontrar un valor en otra tabla o la fuerza de BUSCARV) . Además, muchas personas saben que BUSCARV solo busca en función de un solo valor.
¿Te ayudó el artículo? ¡Comparte el enlace con tus amigos! Lecciones en video("Barra inferior":("textstyle":"estático","textpositionstatic":"inferior","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; color de fondo:#333333; opacidad:0.6; filtro:a lpha(opacidad=60);","titlecss":"display:block; posición: relativa; font:negrita 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; posición: relativa; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; posición: relativa; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
1. En una columna adicional en la que indicaremos la calificación, inserte la función RANK (escriba en la celda =RANK y seleccione la función EXCEL propuesta de la lista, haga clic en fx en la barra de fórmulas)
2. Complete los argumentos en la ventana que se abre: "Número": indique el primer valor en nuestra tabla en la misma línea donde se encuentra la fórmula.
3. "Enlace": especifique toda la matriz de datos, es decir, rango con todos los números (valores de venta).
4. Arreglamos los límites de este rango (presione F4 en el teclado) para que al arrastrar en el futuro, la dirección del rango no se "salga" y presione OK.
5. Extendemos la fórmula a todas las celdas de la columna "calificación" hacia abajo.
Al usar esta función, la calificación se calcula automáticamente y, si cambia algún valor, la calificación se volverá a calcular automáticamente.
Si le gustó el material o incluso lo encontró útil, puede agradecer al autor transfiriendo una cierta cantidad usando el botón a continuación:
(para transferir con tarjeta, haga clic en VISA y luego en "transferir")