JLD de vacaciones

Después de varios años de ausencia, ha llegado el momento de volver a visitar el terruño y como Octubre es el mejor mes del año para visitar Buenos Aires, ahí voy.

Desde el próximo viernes y por tres semanas estaré gozando de unas vacaciones en la Argentina. El blog estará inactivo y no contestaré consultas ni comentarios. A mi vuelta trataré de ponerme al día.

Aprovecho la oportunidad para agradecer a los lectores de este blog y a todos los que me alientan a seguir adelante con su cálido agradecimiento.

Hasta la vuelta!

Jorge

Ocultar filtros en tablas dinámicas

Cuando creamos una tabla dinámica Excel instala filtros en los distintos campos (filas, columnas y filtro del informe)

Si por algún motivo queremos ocultar las flechas de los filtros, por ejemplo para evitar que un usuario cambie el contenido del informe, podemos usar la opción "Encabezados de campos" en el grupo Mostrar en las Opciones de las tablas dinámicas

Pero esta opción tiene un problema: no sólo quita las flechas sino también los encabezados

y como bono adicional, no quita el encabezado ni la flecha del filtro del informe. Los encabezados Ciudad, Agente y Año han desaparecido del informe dejando a nuestro desprevenido y poco informado usuario cavilando sobre si Janet Leverling es un agente de ventas o tal vez un cliente.

Para quitar las flechas de los filtros sin quitar los encabezados podemos usar esta macro

Sub ocultar_Filtros()
Dim ptbl As PivotTable
Dim pfld As PivotField
    Set ptbl = ActiveSheet.PivotTables(1)
      For Each pfld In ptbl.PivotFields
          pfld.EnableItemSelection = False
      Next pfld
End Sub

Esta animación muestra el resultado

Para volver a mostrar los filtros usamos esta macro, igual a la anterior pero con la propiedad EnableItemSelection con el valor True

Sub mostrar_Filtros()
Dim ptbl As PivotTable
Dim pfld As PivotField
    Set ptbl = ActiveSheet.PivotTables(1)
      For Each pfld In ptbl.PivotFields
          pfld.EnableItemSelection = True
      Next pfld
End Sub

De la misma manera podemos ocultar el filtro de un determinado campo. Para evitar que el usuario pueda filtrar el informe por Agente usamos esta macro

Sub ocultar_Item()
Dim ptbl As PivotTable
Dim pfld As PivotField

    Set ptbl = ActiveSheet.PivotTables(1)
    ptbl.PivotFields("Agente").EnableItemSelection = False
  
End Sub

Para volver a mostrar el filtro del campo cambiamos el valor de la propiedad EnableItemSelection a True.

Guía de tipo de errores en Excel

Cuando Excel no puede resolver una fórmula el resultado es un valor de error. Según el valor (#N/A, por ejemplo) Excel trata de indicarnos qué es lo que está causando el error. Conocer el significado de cada uno de estos valores es el primer paso para corregir el error.

Los posibles valores son:

#¡DIV/0! - El más obvio de los errores, se produce cuando nuestra fórmula intenta dividir algún valor por 0

#¡VALOR! - Ocurre cuando alguno de los valores introducidos, o la referencia que los contiene, no es del tipo requerido. Por ejemplo, si queremos sumar un número y un texto

#N/A - Cuando usamos funciones de búsqueda (BUSCARV, INDICE, etc.), si el valor de búsqueda no se encuentra dentro del rango de búsqueda, el resultado es "no disponible" (#N/A en inglés: not available). En este ejemplo, Carlos no figura en la lista de nombres, por lo que el resultado en la celda F3 es #N/A

#¡REF! - Cuando una referencia en una fórmula no existe, Excel crea un error de referencia. Esta situación puede darse cuando eliminamos una fila o columna o cuando movemos o copiamos una fórmula con referencias relativas de manera que se crea una dirección inexistente (referencia no válida), En este ejemplo copiamos la fórmula de la C4 a la celda A2; como las referencias son relativas, C2+C3 en la celda C4 debe transformarse en A0+A1, pero A0 no existe, por lo que obtenemos #¡REF!

#¡NULO! - Ocurre cuando Excel no puede resolver el rango emplpeado en una fórmula, como en este ejemplo, donde hemos puesto un espacio entre las celdas de la referencia

#¿NOMBRE? - Cuando Excel no puede reconocer un texto como nombre definido, vemos este valor de error. En este ejemplo el nombre "IVA" no ha sido definido

#¡NUM! - Aparece cuando nuestra fórmula produce un número mayor del límite de Excel (9.99999999999999E+307), como en este ejemplo

En esta nota tratamos el tema de las funciones para manejar errores.

La función HIPERVINCULO con direcciones de correo electrónico

Cuando introducimos en una celda un texto con un patrón dirección de correo eléctronico, Excel crea un hipervínculo

Al pinchar este hipervínculo Excel abre el Outlook y crea un correo electrónico

También podemos crear hipervínculos a correos electrónicos usando la función HIPERVINCULO, como ya hemos mostrado en otros posts de este blog.
Por ejemplo, si tenemos una tabla con nombres y sus repectivos correos electrónicos, podemos crear una hipervínculo dinámico combinando las funciones HIPERVINCULO y BUSCARV. Sin embargo veremos que la creación del hipervínculo no es trivial.

Veamos este ejemplo:

La fórmula "=HIPERVINCULO(BUSCARV(C15,$B$9:$C$12,2,0))" en la celda C16 crea un hipervínculo. Sin embargo si pinchamos el vínculo en la celda veremos el mensaje: la dirección de este sitio no es válida.

El problema es que cuando Excel reconoce un patrón de correo electrónico, al crear el hipervínculo agrega el identificador "mailto:", lo que no sucede cuando lo creamos con la función HIPERVINCULO

Para poder crear hipervínculos al correo electrónico tenemos que agregar "mailto:" a la dirección

Si queremos que "mailto:" no aparezca en el contenido de la celda (C16 en nuestro ejemplo), modificamos la fórmula de esta manera:

=HIPERVINCULO("mailto:"&BUSCARV(C15,$B$9:$C$12,2,0),BUSCARV(C15,$B$9:$C$12,2,0))

Las funciones ESERR, ESERROR, ESNOD de Excel

Bajo la categoría Información encontramos en Excel varias funciones para administrar errores. Estas funciones evalúan si una celda contiene un valor de error. Estas funciones dan como resultado un valor VERDADERO o FALSO (funciones booleanas) y por lo general las usamos combinadas con la función SI.

• ESERROR: Valor se refiere a uno de los valores de error (#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!)

• ESERR: Valor se refiere a cualquier valor de error excepto #N/A.

• ESNOD: Valor se refiere al valor de error #N/A (el valor no está disponible).

A partir de Excel 2007 disponemos también de la función SI.ERROR.

A primera vista parece superfluo que Microsoft haya desarrollado tres funciones que hacer prácticamente lo mismo. Pero no todos los errores son iguales y de hecho, algunos de ellos no son errores en el sentido estricto de la palabra.

Consideremos el error #¡DIV/0! que nos indica que se ha tratado de dividir un número por cero. Esto es obviamente un error.

Consideremos ahora #N/A. Este "error" indica que el valor buscado no está disponible, como en este ejemplo

Las fórmulas en la columna F calculan la inversa (1/n) del valor correspondiente al color según la tabla B2:C8. Si el valor es cero, como en el caso de los colores blanco o negro, en lugar del error #DIV/0, queremos que aparezca 0. Lo logramos, por ejemplo, con la fórmula

=SI.ERROR(1/BUSCARV(F2,tblColorValor[#Datos],2,0),0)

Pero si el color no figura en la tabla, como en el caso de "Naranja", queremos que aparezca #N/A para saber que el color falta.

En este sentido tampoco #¡VALOR! o #¡NUM! son errores en todos los casos.

Otra función digna de mención es TIPO.DE.ERROR

Esat función da como resultado un número que corresponde a uno de los valores de error de Microsoft Excel o da el error #N/A si no existe ningún error. Combinada con la función SI nos permite dar resultados distintos, como un texto informativo, de acuerdo al tipo de error,