PROGRAMAR BUSCARV EN VBA

Hola a todos:

En esta web hay dos post dedicados a cómo programar la función Buscarv en VBA.

En el primero de ellos utilizo la notación RC en la fórmula (prácticamente como aparece después de utilizar la grabadora de macros) y aplico la fórmula a un rango determinado de celdas: FUNCIÓN BUSCARV EN VBA

La ventaja de utilizar este método es la rapidez, sin embargo el uso de la notación RC hace que sea complicado el realizar modificaciones de las columnas y celdas que intervienen en el proceso. Además de dejar poco margen para la introducción de condicionales.

En el segundo, utilizo el objeto “WorksheetFunction” para llamar desde VBA a la función Buscarv.  UTILIZAR CUADRO DE DIÁLOGO PARA SELECCIONAR ARCHIVO Y BUSCARV , aquí además utilizo una instrucción for-next para recorrer las celdas en las que necesitamos encontrar y en las que queremos incluir el valor encontrado. Este método es más lento, pero permite introducir otras funciones al proceso de forma muy sencilla, al estar utilizando un bucle y hacer referencias a las celdas.

En el post de hoy os mostraré otra alternativa, muy parecida a la primera, pero que permite que la función sea más fácil de utilizar. Usaré la misma plantilla que para el primer post:

BUSCARV EN VBA_3

Y la macro es la siguiente:

Option Explicit
Sub BUSCARV()
'DECLARAMOS VARIABLES
Dim Fin As Long, Final As Long, i As Long
Dim Titulos As Variant, T_rango As Object
Dim Cl_datos As Long
'Seleccionamos la hoja listado y limpiamos las celdas que tengan contenido
Worksheets("LISTADO").Select
Cl_datos = Application.CountA(Worksheets("LISTADO").Range("a:a"))
Sheets("LISTADO").Range("C2:D" & Cl_datos).Clear
'Determinamos la longitud del rango de los datos con un contarA
Fin = Application.CountA(Worksheets("LISTADO").Range("A:A"))
Final = Application.CountA(Worksheets("DATOS").Range("A:A"))
'Aplicamos la función buscarv para buscar el nombre y si no está que el resultado sea vacío, situamos el resultado en el rango
'B2 en adelante
With Worksheets("LISTADO").Range("B2:B" & Fin)
.Formula = "=IF(ISERROR(VLOOKUP(A2,DATOS!$A$2:$D$" & Final & ",2,0)),"""",VLOOKUP(A2,DATOS!$A$2:$D$" & Final & ",2,0))"
'.Formula = .Value
End With
'Aplicamos la función buscarv para buscar los estudios y si no están que el resultado sea vacío, situamos el resultado en el rango
'C2 en adelante
With Worksheets("LISTADO").Range("C2:C" & Fin)
.Formula = "=IF(ISERROR(VLOOKUP(A2,DATOS!$A$2:$D$" & Final & ",3,0)),"""",VLOOKUP(A2,DATOS!$A$2:$D$" & Final & ",3,0))"
.Formula = .Value
End With
'Aplicamos la función buscarv para buscar si sabe o no inglés y si no está que el resultado sea vacío, situamos el resultado en el rango
'D2 en adelante
With Worksheets("LISTADO").Range("D2:D" & Fin)
.Formula = "=IF(ISERROR(VLOOKUP(A2,DATOS!$A$2:$D$" & Final & ",4,0)),"""",VLOOKUP(A2,DATOS!$A$2:$D$" & Final & ",4,0))"
.Formula = .Value
End With
'Nombramos el encabezado de cada columna
With Worksheets("LISTADO")
Titulos = Array("Nombre", "Estudios", "Ingles")
Set T_rango = Worksheets("LISTADO").Range("B1:D1")
T_rango.Value = Titulos
'Coloreamos de rojo
T_rango.Interior.Color = vbRed
End With
End Sub

Como podéis observar, utilizamos la propiedad “.formula” del rango establecido y la escribimos sin utilizar la notación RC. Así resulta mucho más sencillo determinar qué rango es el que debemos seleccionar.

Este método al igual que el primero permite una mayor rapidez en comparación con el uso de bucles. Y nos aseguramos en pasar a valores el resultado de la fórmula. Si queremos que se quede con la fórmula, simplemente eliminamos esta igualación en todas las líneas del código:

.Formula = .Value

Y eso es todo, simplemente quería dejar en esta web otras forma de hacer el mismo procedimiento.

Espero que os resulta de interés.

Descarga el archivo de ejemplo pulsando en: PROGRAMAR BUSCARV EN VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

EXTRAER PARTE ENTERA Y DECIMAL DE UN NÚMERO

Hola a todos:

El post de hoy va a ser muy breve, y es que ya lo hemos tratado en alguna ocasión pero desde VBA. Se trata de cómo extraer la parte entera y decimal de un número.

Aunque parece un tema sencillo, no está de más refrescarlo y proponer un ejemplo. Veamos la siguiente lista de números decimales:

EXTRAER PARTE ENTERA Y DECIMAL DE UN NÚMERO

Para obtener la parte entera (sin posibilidad de redondeo), debemos utilizar la función Truncar. Simplemente la aplicaremos al número:

=TRUNCAR(A2)

Y para el primer registro el resultado será: 25

Para obtener la parte decimal, lo haremos obteniendo la diferencia entre el número inicial y el resultado de haber aplicado la función truncar es:

=A2-TRUNCAR(A2)

Por lo que el resultado será: 0,5

EXTRAER PARTE ENTERA Y DECIMAL DE UN NÚMERO_1

Y esto es todo, un post muy breve, pero seguro que os ayuda en algún momento : )

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

CONTAR REGISTROS ÚNICOS CON VARIOS REGISTROS II

Hola a todos:

Hace un tiempo escribí un post en el que, mediante fórmulas, contaba registros únicos teniendo en cuenta varios registros: CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS.

Pero lo cierto es que en el post, solamente utilizaba un criterio activo en la fórmula. Por dicho motivo he recibido varias consultas solicitando una ampliación de fórmula con un nuevo criterio.

Pues bien, siguiendo el post anterior, he añadido un nuevo criterio al Excel (Criterio2):

contar registros unicos con varios registros ii

Pues bien, el objetivo es contar aquellas áreas donde el criterio y criterio2 sea >=1

La fórmula que voy a utilizar es la siguiente (matricial):

{=CONTAR(1/FRECUENCIA(SI(Hoja1!$A$2:$A$19=F1;SI(Hoja1!$D$2:$D$19>=1;SI(Hoja1!$B$2:$B$19>=1;COINCIDIR(Hoja1!$C$2:$C$19;Hoja1!$C$2:$C$19;0))));FILA($A$2:$A$19)-FILA(DESREF($A$2:$A$19;;;1;))+1))}

Esta fórmula la podemos aplicar para obtener los tres conceptos que necesitamos. Este es el resultado:

contar registros unicos con varios registros ii_

Efectivamente, para el primer caso, tenemos 3 únicos, lo podemos ver utilizando el filtro:

contar registros unicos con varios registros ii_1

Contariamos cada color como un registro único, recordando que los criterios son >=1 en la columna B y D. El registro en blanco no se tiene en cuenta dado que B es igual a 0.

Os recuerdo que las matrices se introducen: seleccionando la fórmula y luego presionando CTRL + MAYUS + ENTRAR.

Y esto es todo, espero que con esta ampliación sea de mayor utilidad.

Descarga el archivo de ejemplo pulsando en: CONTAR REGISTROS ÚNICOS CON VARIOS REGISTROS II

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

OBTENER SUMA DE UN RANGO QUE CONTIENE ERRORES

Hola a todos!:

Últimamente recibo muchos correos preguntando por pequeños problemas cotidianos cuando usamos Excel, uno muy recurrente es el de obtener una suma de un rango que contiene errores.

Por ejemplo:

obtener suma de un rango que contiene errores

Como podéis observar, en el rango tenemos un error y por lo tanto el resultado de la función suma devuelve nos devuelve error.

Para poder solucionar este problema podemos recurrir a la función SI.ERROR y combinarla con SUMA y ejecutarla matricialmente. Esta sería la fórmula:

{=SUMA(SI.ERROR(B2:B20;""))}

Este es el resultado:

obtener suma de un rango que contiene errores1

Os recuerdo que las matrices se introducen: seleccionando la fórmula y luego presionando CTRL + MAYUS + ENTRAR.

Y eso es todo, un truco sencillo que nos puede ayudar en nuestros reportes, sobre todo cuando los datos que nos llegan contiene errores.

Descarga el archivo de ejemplo pulsando en: OBTENER SUMA DE UN RANGO QUE CONTIENE ERRORES

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

FUNCIONES MAX.SI.CONJUNTO Y MIN.SI.CONJUNTO

Hola a todos!.

Feliz año!, os deseo para este 2019 lo mejor.

Con el nuevo Excel 2019 y también con la suscripción de office 365. Se han publicado una serie de funciones que tienen como objetivo facilitarnos nuestro trabajo.

En este post quiero detenerme en estas dos funciones, MAX.SI.CONJUNTO y MIN.SI.CONJUNTO. Realmente no dejan de ser un concepto muy parecido a las conocidas funciones SUMAR.SI.CONJUNTO o CONTAR.SI.CONJUNTO, solo que lo que buscamos es obtener el valor máximo o mínimo de un rango en función de varias condiciones.

Veamos un ejemplo:

funciones max.si.conjunto y min.si.conjunto

Imaginad que tenéis estas cantidades y necesitáis obtener el máximo en los casos en los que se cumpla una determinada condición (para este ejemplo, que los parámetros 1 y 2 sean igual a 1).

Pues bien, si realizamos un filtro veremos esto:

funciones max.si.conjunto y min.si.conjunto_1

Efectivamente, el valor máximo es 14754 y el mínimo es 2676. Esto mismo es lo que obtendremos si aplicamos la fórmula, así:

funciones max.si.conjunto y min.si.conjunto_2

Como podéis observar, los resultados son correctos y podríamos seguir utilizando nuevas condiciones.

Si alguna de las condiciones es texto, debemos expresarlo entre comillas dobles, y también podremos utilizar los signos de mayor o menor como criterios.

Creo que son fórmulas muy interesantes que facilitan nuestras tareas sin necesidad de recurrir a código o a funciones matriciales.

Descarga el archivo de ejemplo pulsando en: FUNCIONES MAX.SI.CONJUNTO Y MIN.SI.CONJUNTO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

OBTENER VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ

Hola a todos!

El post de hoy surge como respuesta a una consulta de un lector, dice así:

Tengo el siguiente cuadro de inventario:
Columna A códigos que se repiten en varias oportunidades, podrían estar ordenados o no.
Columna B cantidad de unidades que trae una caja de ese producto.
Columna C cantidad de cajas del producto.
Necesitaría obtener el valor de B, pero del valor mas alto de C.
O sea:
A1 = 11809 ; B1 = 10 ; C1 = 2
A2 = 11809 ; B2 = 5 ; C2 = 5
A3 = 11809 ; B3 = 30 ; C3 = 1
El resultado debería ser igual a 11809 = 5

Este ejercicio se puede resolver con código o con fórmulas, para este caso la solución se la envié en forma de función, en realidad una combinación de varias.

obtener-valor-de-una-celda-segc3ban-el-valor-maximo-de-una-matriz

Pues bien, para obtener la solución vamos a comenzar con una fórmula matricial que nos va a permitir detectar el valor máximo de la columna C en función de un ID concreto, por ejemplo el 1321 y cuyo resultado será: 777. La fórmula utilizada es la siguiente (recordad que es matricial):

{=MAX(SI(A:A=F1;C:C))}

Ahora que ya conocemos el número más alto, debemos obtener la fila en la que se encuentra, que es la 6. Para ello vamos a utilizar la siguiente fórmula, COINCIDIR:

{=COINCIDIR(MAX(SI(A:A=F1;C:C));C:C;0)}

Una vez que tenemos este dado, debemos obtener las coordenadas de la celda a la izquierda (B6 o $B$6), para ello debemos añadir la función dirección en la que especificaremos que queremos la dirección de la celda 6 en la columna 2, es decir B6:

{=DIRECCION(COINCIDIR(MAX(SI(A:A=F1;C:C));C:C;0);2)}

Finalmente, debemos obtener el valor de esta celda (62), para ello solo nos queda una última función, INDIRECTO:

{=INDIRECTO(DIRECCION(COINCIDIR(MAX(SI(A:A=F1;C:C));C:C;0);2))}

 

El resultado final es el siguiente:

obtener-valor-de-una-celda-segc3ban-el-valor-maximo-de-una-matriz_1

Y finalmente lo hemos conseguido, el resultado es el que el lector ha indicado y funciona perfectamente. Hemos combinado 5 funciones para crear una que resuelve nuestra necesidad.

Recordad que para introducir las fórmulas matriciales debemos seleccionar la fórmula y presionar: CTRL + MAYUS + ENTRAR

Y esto es todo, espero que os sea de utilidad : )

Descarga el archivo de ejemplo pulsando en: OBTENER VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.


Donate Button with Credit Cards
¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL, DISCRIMINAR MAYÚSCULAS Y MINÚSCULAS.

Hola  a todos:

Hoy seguiré hablando sobre fórmulas, aunque muchas de las consultas que me envían son sobre programación, lo cierto es que también me llegan muchas dudas sobre fórmulas y cómo utilizarlas, por lo que creo que es interesante darle visibilidad para que todos nos podamos beneficiar : )

Esta es la consulta que me enviaban:

“Hola, tengo una consulta. El countif no diferencia entre mayusculas y minusculas. Si tengo el nombre “CASA” y en otra celda “casa” dirá que es duplicado pero yo necesito que lo diferencie. Alguna idea?

muchas gracias”

 

Pues bien, el tema de la discriminación de mayúsculas y minúsculas cuando usamos fórmulas suele ser bastante recurrente y en el caso que nos ocupa, el lector necesita contar y por lo tanto diferenciar las minúsculas y mayúsculas.

Para solucionar esto, tenemos varias posibilidades y aunque algunas de ellas requieren utilizar columnas auxiliares (algo que suelo utilizar solo cuando no queda más remedio), lo más eficiente es trabajar con una única celda.

Vamos con un ejemplo:

USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL

En la columna C debemos contar las veces que se repite “cultura y ocio” en minúscula. Pues bién para hacerlo utilizaremos la combinación de la función SUMAPRODUCTO e IGUAL:

=SUMAPRODUCTO(IGUAL($C$2:$C$64980;"cultura y ocio")*1)

También se podría omitir la multiplicación del 1 del final (necesario para pasar los datos a número de la función sumaproducto, utilizando el siguiente operador “–“:

=SUMAPRODUCTO(--IGUAL($C$2:$C$64980;"cultura y ocio"))

En ambos casos el resultado es de “3”

Y si necesitamos introducir otra condición, también lo podemos hacer. Por ejemplo las veces que se repite “cultura y ocio” y el sexo es MUJER:

=SUMAPRODUCTO(IGUAL($C$2:$C$64980;"cultura y ocio")*1;(IGUAL($E$2:$E$64980;"MUJER")*1))

O también:

=SUMAPRODUCTO(--IGUAL($C$2:$C$64980; "cultura y ocio");--(IGUAL($E$2:$E$64980;"MUJER")))

El resultado es 1 en los dos casos.

USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL1

Os dejo el ejercicio con las fórmulas utilizadas. Por cierto, para mostrarlas, he utilizado la función =formulatexto() que sirve para mostrar las fórmulas que contiene una celda.

Descarga el archivo de ejemplo pulsando en: USO COMBINADO DE LAS FUNCIONES SUMAPRODUCTO E IGUAL

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies