Función BUSCARV con datos dinámicos de table_array en Excel

Por ejemplo, hay datos como la imagen de abajo.

fórmula de la función vlookup con datos dinámicos de table_array 01

¿Cuál es el descuento por cada venta? Para resolver esto, use la función BUSCARV.

Coloque el cursor en la celda H3.

Introduzca la siguiente fórmula:

=BUSCARV(G3,$A$3:$C$4,3,FALSO)

Copie la celda H3 y luego pegue el rango H3:H8. El resultado es como la imagen de abajo.

Fórmula de la función Vlookup con datos dinámicos Table_Array 02

Se conoce el descuento por cada venta.

Si hay nuevos datos de ventas, el descuento se puede calcular copiando la fórmula anterior. Los problemas surgen cuando hay ventas en una categoría que aún no se ha registrado para un descuento.

Fórmula de la función Vlookup con datos dinámicos Table_Array 03

Los descuentos de ventas para nuevas categorías producen el error #N/A, agregar datos de descuento para nuevas categorías tampoco resuelve el problema porque la fórmula de matriz de tabla apunta al rango $A$3:$C$4 mientras que la información de descuento para nuevas categorías es fuera de este rango.

La solución es cambiar los datos de table_array para incluir el descuento para la nueva categoría.

Edite la fórmula en la celda H3, cambie la fórmula a:

=BUSCARV(G3,$A$3:$C$5,3,FALSO)

Copie la celda H3 y luego pegue el rango H3:H10. El resultado es como la imagen de abajo.

fórmula de la función vlookup con datos dinámicos de table_array 04

Se pueden encontrar descuentos de ventas para nuevas categorías.

Este problema ocurre cada vez que hay una venta para una nueva categoría. Cada vez que hay un descuento para una nueva categoría, siempre hay que cambiar la fórmula.

Hay una forma de evitar cambiar la fórmula cada vez que hay un descuento para una nueva categoría, y es mediante el uso de un rango dinámico con nombre.

Esta solución convierte los datos de table_array en dinámicos. Si solo hay 2 fechas de descuento, el rango solo tendrá 2 filas. Si hay datos de descuento adicionales, el panel cambia el número de filas según la cantidad de nuevos datos ingresados.

Esta solución utiliza la ayuda de la función OFFSET y la función COUNTA. La función OFFSET se utiliza para crear un rango. La función CONTARA se encarga de dinamizar el rango comprobando si hay datos de descuento adicionales o no.

Rango con nombre dinámico

Estos son los pasos para crear un rango dinámico con nombre

Crear rango A3:C5

Nombra el área

Para hacer esto, haga clic en el campo de nombre en la esquina superior izquierda, luego escriba TABDISCON y luego presione ENTER.

fórmula de la función vlookup con datos dinámicos de table_array 05

El resultado es un panel denominado TABDISKON. Cuando el nombre del rango se usa en la fórmula, corresponde al rango A3:C5. En el caso de datos adicionales, el rango de dimensiones sigue siendo fijo; la función OFFSET y la función COUNTA se utilizan para la dinamización.

Área de edición TABDISKON

Cómo al presionar CTRL + F3 aparece el cuadro de diálogo de administración de nombres. Muestra todos los nombres de espacios que se hayan creado.

Fórmula de la función Vlookup con datos dinámicos Table_Array 06

Seleccione la sección TABDISKON y luego haga clic en Editar. Aparece el cuadro de diálogo de edición de nombre.

Fórmula de la función Vlookup con datos dinámicos Table_Array 07

Para hacer que el rango sea dinámico, cambie la actualización a la sección de funciones OFFSET y COUNTA.

=DESPLAZ(‘Función BUSCARV’!$A$3,0,0,CONTARA(‘Función BUSCARV’!$A:$A)-2,3)

*’Función BUSCARV’ es el nombre de la hoja de trabajo, puede ser diferente para cada archivo.

Haga clic en el botón Aceptar. Si la región es dinámica, las dimensiones de la región cambiarán automáticamente cuando haya datos adicionales presentes.

Para usarlo en la celda de edición de fórmula H3, cambie la fórmula a:

=BUSCARV(G3,TAB DESCUENTOS,3,FALSO)

Copie la celda H3 y luego pegue el rango H3:H10.

Si hay datos de descuento adicionales, se pueden identificar inmediatamente agregando datos de descuento de categoría y copiando la fórmula anterior sin cambiar la fórmula.

Fórmula de la función Vlookup con datos dinámicos Table_Array 08

Más artículos sobre estos consejos y trucos

  • Función BUSCARV
  • Función de DESPLAZAMIENTO
  • Función CONTARA
  • referencia absoluta
  • Rango con nombre dinámico

¿ALGÚN COMENTARIO? #wpdevar_comment_1 intervalo,#wpdevar_comment_1 iframe{ancho:100% !importante;}

Leave a Reply