jueves, 19 de abril de 2018

Filtro Avanzado Registros Únicos

Días atrás un usuario planteaba una cuestión interesante.
Preguntaba por la forma de aplicar un filtro avanzado para obtener registros únicos.
[...]Realice un filtro avanzado en Excel, en donde utilicé criterios de varias columnas, y con valores únicos.
Para comprobar si estaba correcto, concatené las columnas utilizadas para el filtro avanzado, copie y pegué como texto, y le apliqué formato condicional, duplicar valores y me aparecen 5 registros repetidos.[...]


Partiremos de la siguiente 'base de datos':

Filtro Avanzado Registros Únicos



La idea propuesta es obtener un listado de los registros únicos de acuerdo a un 'id' múltiple, que para nuestro ejemplo estará compuesto de los campos Zona+Comercial+Producto.
Es indiferente que apliquemos o no criterios sobre nuestra herramienta: Filtro Avanzado, ya que opera indistintamente como veremos.


Para el primer caso no aplicaremos criterio alguno, solo obtendremos de la totalidad de la base de datos aquellas combinaciones únicas de esos tres campos deseados: Zona+Comercial+Producto.

Así pues preparamos el destino de nuestro listado, copiando y pegando esos tres encabezados:

Filtro Avanzado Registros Únicos



Con la hoja preparada seleccionamos la base de datos (basta marcar una celda cualquiera de ella) y accedemos a la ficha Datos > grupo Ordenar y Filtrar > botón Avanzadas...
Es nos abrirá la ventana del Filtro avanzado que configuraremos como sigue:
1-Copiar a otro lugar
2-Rango de la lista: $B$2:$F$22 (esto es, la base de datos con el encabezado incluido!!)
3-Rango de criterios: vacío para este primer ejemplo (es decir, trabajamos sobre la totalidad de la base de datos)
4-Copiar a: $I$2:$K$2 (son los tres campos que componen nuestro id múltiple).
5-Solo registros únicos (marcado).

Filtro Avanzado Registros Únicos



Tras aceptar vemos en nuestro rango destino como se han volcado, para esos tres campos, las combinaciones únicas obtenidas desde la base de datos...

Filtro Avanzado Registros Únicos


Se puede comprobar fácilmente como esas doce combinaciones de tres elementos son las únicas existentes en nuestro origen...

De manera similar podríamos, además, haber aplicado unos criterios de filtro, para obtener las combinaciones únicas de solo aquellos registros que cumplan aquellos criterios...
Veamos la imagen siguiente, donde se especifica un criterio por Zona, y donde queremos listar solo las combinaciones/registros únicos de éstos.
1-Copiar a otro lugar
2-Rango de la lista: $B$2:$F$22 (esto es, la base de datos con el encabezado incluido!!)
3-Rango de criterios: $I$2:$M$3 (donde incorporamos los criterios deseados)
4-Copiar a: $I$7:$K$7 (son los tres campos que componen nuestro id múltiple).
5-Solo registros únicos (marcado).

Filtro Avanzado Registros Únicos



El resultado tras aceptar:

Filtro Avanzado Registros Únicos


Obteniendo el listado de combinaciones únicas que verifican nuestros criterios para esos tres campos Zona+Comercial+Producto.

martes, 17 de abril de 2018

Hora superior a 10.000

Un usuario preguntaba, en un comentario, por la forma de trabajar cuando la hora superara las 10.000
[...]como puedo hacer que se pueda llevar acabo una operación de mas de 10 mil horas?[...]


Es importante conocer nuestra herramienta de trabajo -Excel-, y sus limitaciones.
En concreto el que afecta al:
Período de tiempo máximo que puede escribirse:= 9999:59:59

Puedes ver algunas más aquí o aquí.


Por tanto la hora máxima que se puede escribir son las 9999:59:59.
Es importante subrayar la palabra 'escribir'.. ya que en realidad a través de operaciones o fórmulas podremos trabajar y reconocer como 'hora máxima' las:
71003183:59:59
hora que equivale a la fecha máxima reconocida por Excel:
31/12/9999 23:59:59


Y en esta parte es donde reside la solución para trabajar o escribir una hora superior a la reconocida (9999:59:59), será a través de cálculos.

Veamos un ejemplo, donde sumé varias celdas con horas, que en total exceden del límite de las 10000 horas. Como se ve en la imagen se suma correctamente y se devuelve el dato correcto:

Hora superior a 10.000



En eso consistirá nuestro trabajo, en operar sobre la hora a escribir, para obtenerla a partir de un cálculo, y luego sencillamente aplicarle un formato tipo: [h]:mm:ss.

De hecho, en una celda podríamos escribir:
=10000/24+1/24/60+1/24/60/60
y tras aplicarle el formato indicado, veríamos la hora:
10000:01:01


Extrapolamos ese cálculo, y creamos una UDF que trabajará de forma parecida a la función
NSHORA
o la función
FECHA

Así pues, en un módulo estándar del libro incluimos el siguiente procedimiento Function:

Function Horas10000(hora As Double, _
                    minunto As Double, _
                    segundo As Double) As Double
                        
Dim hr As Double, min As Double, sec As Double
'convertimos a decimal las horas, minutos y segundos...
'dividimos por 24 por que tomamos como base el formato de [h]:mm:ss
'que permite incrementar el número de horas por días completos
If hora >= 24 Then
    hr = hora / 24
    min = (minunto / 24) / 60
    seg = ((segundo / 24) / 60) / 60
Else
    'por si acaso el número de hors total no excede de las 24 horas...
    hr = hora
    min = minunto / 60
    seg = (segundo / 60) / 60
End If
'y finalmente devolvemos a la celda la suma
'que corresponderá con el valor buscado...
Horas10000 = hr + min + seg
End Function



Podemos comprobar como introduciendo en una celda, por ejemplo, B8 la fórmula:
=Horas10000(10000;1;1)
obtenemos la hora buscada:
10000:01:01

Solución encontrada...

jueves, 12 de abril de 2018

Personalizar la barra de acceso rápido para un libro en particular

Hace algún tiempo escribía sobre la forma de trabajar con la barra de herramientas de acceso rápido (ver).
En esa entrada veíamos la manera de exportar o importar nuestra configuración personalizada...

Y es que existe una idea generalizada que esa personalización de la barra de herramientas de acceso rápido es igual para cualquier libro de Excel que abramos con nuestro equipo.

Hoy veremos que esto no es del todo cierto, y que si lo consideramos, podemos personalizar nuestra barra para un libro de Excel en particular.


Lo primero que debemos hacer es acceder a la opción de Excel: Personalizar barra de herramientas de acceso rápido.
Hay varias formas, la más simple es hacer clic derecho sobre la cinta de opciones y luego seleccionar la opción buscada.

Personalizar la barra de acceso rápido para un libro en particular



Una vez en la ventana de opciones de Excel añadiríamos los botones deseados para personalizar nuestro libro. Basta buscarlos en la parte izquierda...
OJO!, importante, antes de hacer doble clic o presionar Agregar, tenemos que cambiar el desplegable de arriba a la derecha y elegir la opción:
Para 'Nombre_de_nuestro_libro'

Solo una vez desplegado podremos incorporar nuevos botones a nuestra barra personalizada...

Por ejemplo, en mi caso solo añadiré el botón de Actualizar todo.

Personalizar la barra de acceso rápido para un libro en particular



Una vez guardado el libro, y la próxima vez que lo abramos, veremos nuestra barra de herramientas de acceso rápido personalizada.

IMPORTANTE!!
Lo que veremos para ese libro es primero todos los botones de nuestra 'configuración general', y al final, a la derecha de la barra, los botones añadidos en particular para nuestro libro!.