Cómo conseguir que BUSCARV devuelva vacío y no cero

Vamos a solucionar un problema muy habitual al trabajar con la función más empleada (posiblemente):
BUSCARV.
Cuantas veces al recuperar un valor buscado, la función BUSCARV, nos devuelve un cero cuando la realidad es que el valor correspondiente es un blanco, i.e., una celda vacía.
Esto puede llevarnos a confusión con valores que realmente si tengan un dato, un dato con valor cero.

Veamos nuestro planteamiento:

Vemos nuestra tabla de información llamada 'TblDatos' y cómo en la celda F4 hemos construido una forma elegante de controlar el problema que nos atañe:
=SI(ESBLANCO(BUSCARV(E4;TblDatos;2;0));"";BUSCARV(E4;TblDatos;2;0))

comprobamos como mediante un condicional SI gestionamos el valor devuelto con BUSCARV, para que en caso sea un valor vacío, nos devuelva 'nada' (o doble comillas), en caso contrario, esto es, que no sea vacío, el condicional nos devolverá el valor correspondiente que retorne BUSCARV.

Esta, desde mi punto de vista es la mejor opción, ya que el valor devuelto será del mismo tipo que en el origen.

No ocurre lo mismo con otra posible solución, más corta pero también más limitada.
En G4:
=""&BUSCARV(E4;TblDatos;2;0)

observamos que simplemente hemos concatenado antes del valor devuelto por BUSCARV unas dobles comillas...
El problema de este ejemplo es que todos los valores devueltos los tratará siempre como texto!!!

Otra forma habitual es empleando la función LARGO, que permite controlar el número de caracteres retornado por BUSCARV... en caso de ser cero, equivaldría a no tener caracteres, es decir, ser una celda vacía.
=SI(LARGO(BUSCARV(E4;TblDatos;2;0))=0;"";BUSCARV(E4;TblDatos;2;0))

Existen, por supuesto, otras variantes.

No quiero terminar esta sencilla entrada sin mostrar una manera de controlar dos (o más) problemas de manera simultánea.
En el ejemplo, en la celda F5 añadimos:
=SI(ESNOD(BUSCARV(E5;TblDatos;2;0))+ESBLANCO(BUSCARV(E5;TblDatos;2;0));"-";BUSCARV(E5;TblDatos;2;0))

Nuevamente controlamos con un SI condicional nuestros problemas, en este caso, dos posibles errores:
1-que la celda devuelta esté vacía
2-que la celda devuelta contenga un error #N/A

Lo interesante de este último ejercicio es la construcción de la prueba lógica de la función SI:
ESNOD(BUSCARV(E5;TblDatos;2;0))+ESBLANCO(BUSCARV(E5;TblDatos;2;0))

empleando dos funciones lógicas tipo ES (otros ejemplos) controlamos si BUSCARV devuelve un error #N/A o una valor 'blanco' o vacío.
En caso de que ocurra solo uno de ellos la suma devolverá 1 (algo diferente de cero), lo que en el contexto de una prueba lógica, se leerá como VERDADERO, en caso de no cumplirse ninguna de las dos funciones, la suma devolverá 0 lo que se interpreta como FALSO

Recuerda esa interpretación general en Excel (y en otros aspectos):
1 es VERDADERO
0 es FALSO.

Seguir leyendo...

Localizar última celda con texto

Al hilo de esta entrada del blog publicada hace algún tiempo, donde se veía como identificar la última celda con un dato numérico, un lector consultaba la manera de lograr lo mismo en caso de que la celda contuviera un texto.

Aprovecharemos para ver diferentes formulaciones que me permitirán localizar (y recuperar) las últimas celdas ocupadas, ya sena con texto o con número.

Empezaremos respondiendo a lector.
Para localizar la última celda con texto, obviando valores numéricos, podríamos aplicar la fórmula:
=COINCIDIR(REPETIR("z";255);A:A)
al omitir en COINCIDIR el tercer argumento '[tipo_coincidencia]' estamos indicando, en realidad, que busque cualquier cosa similar a 255 'zetas', ya que damos por sentado no existe ningún tipo de orden en los datos distribuidos.

Con un sencillo INDICE, a partir del dato anterior, conseguimos el valor de la celda con texto:
=INDICE(A:A;COINCIDIR(REPETIR("z";255);A:A))

Para localizar la última celda con valore, además de los ya visto en el post referido al inicio:
-para identificar la fila: =COINCIDIR(9,9E+307;A:A;1)
-para recuperar el valor:=INDICE(A:A;COINCIDIR(9,9E+307;A:A;1))

Pero también:
-para identificar la fila: =BUSCAR(2;1/(A:A<>"");FILA(A:A))
-para recuperar el valor: =BUSCAR(2;1/(NO(ESBLANCO(A:A)));A:A)

O estas matriciales:
-para identificar la fila {=MAX((A:A<>"")*(FILA(A:A)))}
-para recuperar el valor: {=INDICE(A:A;MAX((A:A<>"")*(FILA(A:A))))}

Tal como se veía en la primera imagen del post.

En el caso que la última celda tuviera un dato tipo texto, todos excepto el primer método son capaces de recuperar dicho dato:
Solo recupera la última celda con número, obviando celdas con texto...
-para identificar la fila: =COINCIDIR(9,9E+307;A:A;1)
-para recuperar el valor:=INDICE(A:A;COINCIDIR(9,9E+307;A:A;1))

Seguir leyendo...

Marcar como final para evitar cambios en tu versión

Es normal que trabajemos sobre un mismo fichero o libro de Excel, sometiéndolo a constantes cambios o ajustes, hasta que llegue un momento en que queramos hacer saber a los diferentes posibles usuarios que la versión que acaban de abrir de nuestro de libro de trabajo es la final...
Aquí nace la opción de Marcar como final.

Es importante recordar que cuando un libro lo marcamos como final, ciertas acciones quedan deshabilitadas (como escritura, edición, revisión ...); además el archivo quedará como sólo lectura, y la propiedad Status del documento está definida como Final.

Cualquier usuario de un libro de trabajo marcado como final podrá 'desactivar' esta opción. Le bastará hacer clic y marcar de nuevo en el comando Marcar como Final; esto significa que no debemos entender esta herramienta como medida de seguridad y protección de nuestros libros, solo como señal informativa.

Veamos como marcar como final un libro de Excel.
Lo primero es acceder a la ficha Archivo > menú Información > botón Proteger Libro > opción Marcar como final

Se nos pedirá confirmación de la acción:

Tras confirmar nos indica en una sencilla ventana, lo que ha ocurrido y sus consecuencias:
Este documento se marcó como final para indicar que terminó de editarse y que se trata de la versión final.
Cuando un documento se marca como final, la propiedad estado se establece como 'Final' y se desactivan los comandos de escritura y edición, y las marcas de corrección.
Puede reconocer que un documento está marcado como final cuando el icono Marcar como final aparece en la barra de estado.

Este será el aspecto de un libro marcado como final.. con los comandos deshabilitados como indicaba el mensaje anterior

Otro lugar donde identificar el Estado sería la ficha Archivo > menú Información

No pases por alto como el fichero está en modo [solo lectura]...

Seguir leyendo...

Atajos imprescindibles con el ratón en Excel

Repasaremos hoy algunos de los atajos con el ratón que, sorprendentemente, son desconocidos por la mayoría de usuarios...
Estamos muy acostumbrados a buscar y emplear los atajos de teclado (método abreviado), dejando un poco apartado las posibilidades del uso de ratón.
En este post veremos los que , desde mi perspectiva, deben ser un básico.
Empecemos.

El primer atajo fundamental es el doble clic en la esquina inferior derecha de la celda o rango,
lo que permitirá arrastrar el contenido de las celdas hasta que haya un saldo en la continuidad de las celdas adyacentes.
Al posicionar el cursor sobre la esquina indicada, el curso cambiará a una cruz estrecha, señal clara del momento en el que podemos hacer el doble clic.

Un segundo atajo nos permite desplazarnos hacia los cuatro sentidos. Será el equivalente a emplear el modo final con el teclado.
Solo debemos situar el cursor en el lado de la celda hacia que queramos desplazar la celda activa y hacer doble clic, esto es, parte inferior si queremos llevar la celda activa hacia abajo dentro del rango de datos; cursor en el lado derecho de la celda lleva al final del rango en su dercha.

Un añadido a este efecto es mantener presionada la tecla mayúscula mientras realizamos dicha acción, lo que provoca la selección del rango desde la celda en cuestión hasta el fin donde nos lleve el doble clic.

Y un último atajo podría ser el que nos permite realizar hasta diez acciones diferentes, como copiar, pegar, pegar como valores, formatos, vínculos, etc...
El atajo consiste en seleccionar un rango y haciendo clic derecho (sin soltar), desplazar el rango a un destino cualquiera... al soltar el botón del ratón, aparecerá un menú contextual con las opciones disponibles:

Un uso frecuente de esta acción es copiar y pegar como valores sobre el mismo rango.

Vemos en el gif siguiente los distintos atajos comentados:

haz clic en la imagen

Seguir leyendo...

Gráfico de Termómetro en Excel

Vamos a ver un clásico de los gráficos en Excel: el gráfico de Termómetro.

Hay muchos ejemplos en Internet al respecto, muchos con formas e imágenes añadidas para decorar este sencillo gráfico.. pero nosotros le daremos una vuelta para construir un gráfico de termómetro solo con las herramientas que nos ofrecen los gráficos.

Lo primero que debemos entender es que este tipo de gráfico es muy especifico para comparar una magnitud o valor frente a unos parámetros... Muy típicos en ratios o indicadores (KPI's).

En nuestro ejemplo vamos generar un gráfico para analizar un importe frente a una escala de valoración.

Bajo 0 600
Medio 600 1.100
Alto 1.100 1.750
Extremo 1.750 2.000

Vemos nuestra escala y formulación en la imagen siguiente:

Pasamos a construir nuestro gráfico.
Primero seleccionamos el rango múltiple: A2:A6 y D2:D6
e insertamos un gráfico de columnas 100% apiladas, y a continuación Intercambiamos Filas por Columnas.
Esto es lo que obtenemos:

En el siguiente paso procedemos a seleccionar el punto que corresponde al 'Dato' y lo trasladamos al Eje secundario, cambiando además el tipo de gráfico a Columna agrupada:

haz clic en la imagen

Tal como veíamos en la distribución posible del valor, nuestra cantidad de estudio puede ir desde 0 hasta 2000, por tanto, en el siguiente paso configuramos y personalizamos el Eje secundario recién creado para que tome valores fijo en los límites de las opciones del eje, y el límite máximo sean esos 2.000 euros.

Seguidamente seleccionamos el punto 'Dato' (el único trazado en el eje secundario) y le damos formato con un Ancho del intervalo del 400%

Y ya podríamos darlo por finalizado.
En nuestro gráfico podemos ver la distribución y la división entre los parámetros de estudio, y por encima, ajustado a su valor real,
a modo de termómetro moviéndose entre esos puntos, el dato de estudio.
Lo interesante es que de manera muy simple, sobre el rango B2:B5, podemos variar la división entre intervalos de valoración.

Con un poco más de trabajo, quitando elementos, añadiéndole estilos, efectos de forma, colores, etc...
llegamos a un gráfico como el del principio.

Seguir leyendo...

SUMAPRODUCTO y los ranking con multicriterio.

Haremos un uso de una función muy potente en Excel como es SUMAPRODUCTO, que por su comportamiento 'matricial' a la hora de trabajar con rangos nos facilitará el objetivo del post del día: Obtener el ranking de un listado basado en varios criterios.

Partiremos del listado de la imagen:

Vemos un listado de años divididos por trimestres, a partir del cual queremos obtener, para cada año, el ranking de las ventas...

Nuestra fórmula buscada a incluir en D2 y siguientes será:
=SUMAPRODUCTO(--($B$2:$B$17=B2);--(C2>$C$2:$C$17))+ CONTAR.SI.CONJUNTO($B$2:$B2;B2;$C$2:$C2;"="&C2)

haz clic en la imagen

La explicación de nuestra fórmula corresponde a lo siguiente.
El primer argumento de SUMAPRODUCTO (--($B$2:$B$17=B2)) nos devolverá un conjunto de unos y ceros, con 1 para el año de estudio.. por ejemplo, para la celda D2 (el año 14) tendríamos:
{1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}

El segundo argumento de SUMAPRODUCTO (--(C2>$C$2:$C$17)) nos devolverá un conjunto de unos y ceros, con 1 para los importes superiores al del estudio (al de la fila de la fórmula)... por ejemplo, para la celda D2 (el año 14) tendríamos:
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Aquí es importante tener claro el tipo de ranking u ordenación que deseemos:
1-Ascendente: --(C2>$C$2:$C$17)
2-Descendente: --(C2<$C$2:$C$17)

El resultado de multiplicar uno a uno ambos rangos nos sumará la posición parcial para cada año...

Finalmente para corregir aquellos posible casos en que se den repeticiones de importes de ventas incluimos una función CONTAR.SI.CONJUNTO a nuestra fórmula que 'desempatará' solo en los casos de importes repetidos.
OJO que debemos emplear rangos corridos en sus argumentos!!:
CONTAR.SI.CONJUNTO($B$2:$B2;B2;$C$2:$C2;"="&C2)

Como curiosidad final. Si quisiéramos obtener un ranking absoluto, solo basado en los importes, podríamos plantear la siguiente fórmula:
=SUMAPRODUCTO(--(C2>$C$2:$C$17))+CONTAR.SI($C$2:$C2;"="&C2)

Esta fórmula corrige el defecto de la función
=JERARQUIA.EQV(C2:C17;C2:C17;1)
que por si sola no ordena los elementos repetidos...

Seguir leyendo...

VBA: el método .ConvertFormula

Hablaremos hoy de los estilos de referencia A1 y F1C1 / R1C1 en Excel... pero desde la perspectiva de nuestras macros.

Expondré hoy el método .ConvertFormula que con sus sintaxis:
.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
que nos permitirá convertir las referencias de celda en una fórmula entre los estilos de referencia A1 y F1C1/R1C1, y entre referencias relativas y absolutas, o ambas...

Veamos el siguiente ejemplo:

Se observa como en el rango B7:D8 aparecen nuestras fórmulas SUMA y PROMEDIO en forma relativa:
=SUMA(B3:B6)
=PROMEDIO(B3:B6)

Aplicando el método .ConvertFormula a ese rango B7:D8 veremos el aspecto que tendrían dichas fórmulas cambiando a un estilo de referencia R1C1 y en modo absoluto.

Abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub ConvirtiendoEstilosReferencia()
Dim celda As Range
Dim TextoFormula As String, NuevaFormula As String

For Each celda In Range("B7:D8")
    If celda.HasFormula = True Then
        TextoFormula = celda.FormulaLocal
        'Sintaxis método:
        '.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
        'Convierte las referencias de celda en una fórmula entre los estilos de referencia A1 y F1C1,
        'entre referencias relativas y absolutas, o ambos.
        
        'Al aplicar el método, en este ejemplo, convertimos a referencia R1C1 y en tipo Absoluta
        NuevaFormula = Application.ConvertFormula(TextoFormula, _
          fromReferenceStyle:=xlA1, _
          toReferenceStyle:=xlR1C1, _
          ToAbsolute:=xlAbsolute)
        'mostramos la fórmula en estilo R1C1 cambiado
        MsgBox NuevaFormula
    End If
Next celda
End Sub

Al ejecutar nuestra macro vemos una ventana para cada celda del rango y cómo ha sido convertida:

Por ejemplo:
=SUMA(B3:B6) queda convertido a =SUMA(R3C2:R6C2)
y
=PROMEDIO(B3:B6) queda convertido a =PROMEDIO(R3C2:R6C2)

Otro ejemplo consiste en aplicar un cambio previo, desde nuestra macro, del estilo de referencia empleado (A1 o F1C1 / R1C1).
Abrimos nuestra ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub Convirtiendo2()
Dim TxtFormula As String
Dim originalReferenceStyle
'Para aplicar un estilo u otro de referencia...
'Application.ReferenceStyle = xlA1           'xlR1C1     'xlA1

'guardamos el estilo de referencia definido (A1 o R1C1)
EstiloReferencia = Application.ReferenceStyle

TxtFormula = "=SUM(R2C2:R6C4)"
MsgBox Application.ConvertFormula( _
    Formula:=TxtFormula, _
    fromReferenceStyle:=xlR1C1, _
    toReferenceStyle:=xlA1, _
    ToAbsolute:=xlRelative)
 
'retornarmos el estilo predefinido
Application.ReferenceStyle = EstiloReferencia
End Sub

Con este ejemplo vemos como empleamos el cambio de estilo:
Application.ReferenceStyle = xlA1
o con
Application.ReferenceStyle = xlR1C1

o como recuperamos el existente asociándolo a una variable:
EstiloReferencia = Application.ReferenceStyle

En el ejemplo pasamos una fórmula escrita en forma R1C1 y modo absoluto:
"=SUM(R2C2:R6C4)"
a estilo A1 en modo relativo!.

Seguir leyendo...

VBA: Las matriciales en las macros de Excel - FormulaArray

Un lecto dejaba un comentario en el blog preguntando sobre el uso de las fórmulas matriciales desde nuestras macros en Excel.
Para dar respuesta al lector emplearemos la propiedad .FormulaArray asociado a un objeto Range.

Para evitar errores en la aplicación de esta propiedad deberemos recordar algunas limitaciones de las funciones matriciales (dentro de VBA y en la hoja de cálculo):
1-No funcionan sobre Celdas Combinadas.
2-No podemos sobrepasar, al construir nuestra matricial, los 255 caracteres!!!.
3-En nuestras macros se admiten los dos estilos de referencia: A1 y R1C1 !!! (a pesar de lo que indica la ayuda de Excel).
4-No se puede modificar/eliminar la parte de una matricial aplicada sobre un mismo rango.
5-No todas las funciones de Excel admiten su forma matricial.
6-No debemos escribir las llaves {} que delimitan nuestra fórmula matricial... aparecen al validar las fórmulas presionando Ctrl+Mayusc+Enter, o bien desde las macros al mostrarlas con la propiedad .FormulaArray.

Trabajaremos sobre nuestro rango de celdas de la siguiente imagen:

Abrimos la ventana de código de nuestro módulo estándar y añadimos los siguientes códigos:

Sub Matriciales1()
'matricial en una celda
Range("H3").FormulaArray = "=SUM(IF(B2:B13=G3,C2:C13,0))"
End Sub
''''''''''''''''''''''''''''''''''''
Sub Matriciales2()
'misma matricial sobre un rango
Range("D2:D13").FormulaArray = "=IF(B2:B13=""Excel"",C2:C13,0)"
End Sub
''''''''''''''''''''''''''''''''''''
Sub Matriciales3()
'matricial sobre rango corrido
With Range("H6:H9")
    'paso 1
    .Formula = "=MAX(IF($B$2:$B$13=G6,$C$2:$C$13,0))"
    'paso 2
    .FormulaArray = .FormulaR1C1
End With
End Sub

Ejecutando las tres macros vemos cómo se muestran los resultados en los rangos indicados...
Tal como se quería mostrar a modo de ejemplo.

Seguir leyendo...