Calcular Fecha Juliana en Excel

Vamos a explicar como conseguir, a partir de una fecha dada, su equivalente Fecha Juliana...
o al menos lo que está aceptado como fecha juliana, ya que en realidad, la fecha juliana es otra cosa (ver wikipedia).

¿Qué vamos a calcular entonces?, y ¿cuál es el objetivo?.
Lo que entendemos como Fecha Juliana se utiliza a menudo en entornos de fabricación o producción como una marca de tiempo y de referencia rápida para un número de lote o similar, lo que permite al usuario final identificar cuándo se fabricó un producto, y por lo tanto la antigüedad de ese producto.

Existen diferentes variaciones en las Fechas Julianas, pero la más utilizada se compone de dos partes (aaaaddd):
1- aaaa:= un número de cuatro dígitos que representa el año, y
2- ddd:= el número de días transcurridos en dicho año.

Por ejemplo, la fecha de 27/10/2016 en Juliana sería 2016301, ya que desde el 01/01/2016 hasta el 27/10/2016 han pasado 301 días.

Veamos entonces algunas formas de conseguir esta fecha, obviamente todas responden a ese algoritmo.

Una primer fórmula, suponiendo la fecha a convertir en la celda C1:
=TEXTO(C1;"aaaa")&TEXTO((C1-FECHA(AÑO(C1);1;1)+1);"000")

una alternativa sería
=DERECHA(AÑO(C1);4)&TEXTO(C1-FECHA(AÑO(C1);1;0);"000")

una más (todas ellas muy similares):
=TEXTO(C1;"aaaa")&TEXTO((C1 -FECHANUMERO("1/1/"&TEXTO(C1;"aaaa"))+1);"000")

Como podemos comprobar todas responden al mismo cálculo...

Por supuesto también podemos replicar el algoritmo de cálculo con una función personalizada con VBA (una UDF); así insertamos en un módulo estándar de nuestro proyecto de VB el siguiente código:

Function FechaJuliana(fecha As Long) As Long
'convierte una fecha en su correspondiente Fecha Juliana
Dim anyo As Integer, dias As Integer

'obtenemos la primera parte, el año con cuatro dígitos
anyo = Year(fecha)
'calculamos la diferencia de la fecha dada y el primer día del año de estudio
dias = DateDiff("d", DateSerial(anyo, 1, 0), fecha)

'devolvemos el valor compuesto de: aaaaddd
FechaJuliana = Format(anyo, "0000") & Format(dias, "000")

End Function

Empleando esta función en cualquier celda:
=FechaJuliana(C1)

conseguimos el mismo valor...

Notemos que en los primeros casos, el valor devuelto, aparece en formato texto... Para trabajar y poder operar sobre estos resultados conseguidos con fórmulas, bastará anidar lo obtenido en la función VALOR.

Seguir leyendo...

Alternativa Matricial para Tablas Auxiliares

Hoy repasaremos un aspecto importante de las fórmulas matriciales: las Constantes Matriciales, y en concreto cómo usarlas para evitar el uso de tablas auxiliares o de apoyo.

Ya hemos empleado alguna vez estas contantes matriciales (ver ejemplo).
Sirva a modo de recordatorio lo siguiente:
Una Matriz de constantes (o constante Matricial) no es más que una matricial cuyos elementos son valores fijos.
Esta matriz la podemos construir bien dándole una orientación horizontal, algo del tipo ={1;2;3;4}; o bien con una orientación vertical ={1\2\3\4}... o por supuesto, combinando ambos signos de puntuación, tendríamos una matriz de constantes bidimensional ={1\2;3\4;5\6}.

Lo que haremos hoy será aprovecharnos de estas constantes matriciales para evitar, a modo de alternativa, el uso de rangos o tablas auxiliares de apoyo.
Veamos un ejemplo.

La idea es clara, teniendo como nexo común en ambas tablas, el nombre del comercial, queremos recuperar en la Tabla principal el porcentaje de descuento correspondiente a cada comercial.
La primera intentona sería, obviamente, aplicar un BUSCARV tomando como Matriz de búsqueda la tabla auxiliar...

Pero como pretendemos evitar este uso, y para estos casos en los que el número de datos es corto, crearemos una constante matricial de cuatro filas (separador ;) y dos columnas (separador \) por fila:
{"pepe"\0,05;"juan"\0,06;"ana"\0,04;"eva"\0,035}

Es posible trabajar directamente con esta constante en la función, añadiendo en la celda E2:
=BUSCARV([@Comercial];{"pepe"\0,05;"juan"\0,06;"ana"\0,04;"eva"\0,035};2;0)

O bien, crear un Nombre definido que corresponda a dicha constante:
Dcto ={"pepe"\0,05;"juan"\0,06;"ana"\0,04;"eva"\0,035}

para posteriormente emplear el nombre definido en vez de la constante directamente...

Esta técnica es válida especialmente cuando el número de constantes es fijo (no aumenta ni disminuye) y no son demasiados... ya que la constante hay que crearla manualmente.

Seguir leyendo...

Gráfico Cajas y Bigotes en Excel 2016

Toca analizar hoy un gráfico estadístico importante: Gráfico Cajas y Bigotes.
En esta ocasión explicaré cómo construirlo de una manera muy simple empleando la versión de Excel 2016.

Este gráfico de Cajas y Bigotes muestra en un solo gráfico las diferentes magnitudes estadísticas básicas, empleadas en los estudios de estadística descriptiva.
Hablamos en concreto de:
Mínimo: función =MIN(rango_celdas)
Cuartil 1 (al 25%): función =CUARTIL.INC(rango_celdas;1)
Mediana o cuartil 2 (al 50%): función =CUARTIL.INC(rango_celdas;2)
Cuartil 3 (al 75%): función =CUARTIL.INC(rango_celdas;3)
Máximo: función =MAX(rango_celdas)
Media aritmética: función =PROMEDIO(rango_celdas)
Rango (intervalo valores): fórmula =MAX-MIN

Lo que vamos a conseguir es ver de la siguiente forma estos datos:

Veámoslo con un ejemplo.
Disponemos de estas tres variables en el rango de celdas B1:C14:

haz clic en la imagen

Construir el gráfico es sencillo.
Seleccionamos el rango B1:B14 y desde la Ficha Insertar > grupo Gráficos > botón Insertar gráfico de estadística > Cajas y Bigotes.
Con el gráfico ya construido, y mostrando casi todas las variables, empezamos a configurarlo.
1-eliminamos el título del gráfico
2-eliminamos las etiquetas del eje horizontal
3-añadimos la Leyenda en la parte inferior
4-agregamos las etiquetas de datos.

Un paso importante para reflejar la información es hacer doble clic sobre las distintas series de datos para acceder al Formato de serie de datos y así definir las Opciones de serie, desde donde nos aseguraremos de marcar la opción de cálculo de los cuartiles al que nos interesa, en mi caso Mediana inclusiva.
La implicación de esta elección responde a:
*Mediana inclusiva:= La mediana se incluye en el cálculo si el número de valores de los datos fuera impar.
*Mediana exclusiva:= La mediana se excluye del cálculo si el número de valores de los datos es impar.

Otras posibilidades son las de incorporar los puntos intermedios, incluir las medias, añadir la linea que uno los puntos medios o de especial relevancia Mostrar valores atípicos.

Para entender el concepto de valor atípico deberemos tener ciertas nociones estadísticas y en particular del conocido como Rango Intercuartil (IQR en inglés). Dato que se calcula como diferencia del cuartil 3 y el cuartil 1.
A la pregunta de a qué distancia de la media del 50% de datos distribuidos entre esos cuartiles puede situarse un valor y aún ser considerado como un valor 'razonable' o 'atípico', los estadísticos llegaron al acuerdo en que (IQR * 1.5) puede utilizarse para establecer un margen superior e inferior razonable.
De tal forma que serían atípicos por debajo aquellos valores inferiores a:
=cuartil_1-(IQR x 1,5)
y serían atípicos por encima o superiores a:
=cuartil_3+(IQR x 1,5)

En nuestro ejemplo hemos calculado todas las magnitudes estadísticas en el rango A16:D29, como se veían en la imagen anterior.
Si nos centramos, a modo de ejemplo, en los valores de la serie de 'Ventas B' verificamos los cálculos:

haz clic en la imagen

Nos fijaremos que hay un valor en la serie (valor= 44) que aparece reflejado como 'Atípico', y fuera por tanto del rango de estudio... notemos que en dicha serie el valor máximo es el valor anterior 35,16 (NO atípico).

Este comportamiento está altamente relacionado con el tipo de cálculo del cuartil seleccionado.

Seguir leyendo...

Grafico de Cascada en Excel 2016

Antaño, en versiones anteriores a Excel 2016, para construir un gráfico de Cascada, había que hacer auténticas 'virguerias'... (ver cómo aquí)
Por suerte, con la versión Excel 2016, este tipo de gráfico de Cascada ('Waterfalls chart' o 'Flying bricks') es muy accesible y extremadamente sencillo.

Partimos de los siguientes valores a representar:

El siguiente paso es fácil, basta seleccionar el rango de datos: B1:C7 y desde la Ficha Insertar > grupo Gráficos > botón Insertar gráficos de Cascada o de Cotizaciones.
Este será el gráfico creado:

haz clic en la imagen

Si queremos, cambiamos el Título del gráfico y si nos apetece el estilo de diseño...

Lo importante es convertir el último punto de la serie (RDI en el ejemplo) en el punto final o total, que represente el último valor acumulado de todos los anteriores.

Para ello basta hacer doble clic sobre la columna de dicho punto, y en el panel de formato, dentro de sus Opciones de serie seleccionaremos dicho punto como Punto Total (Establecer como total):

haz clic en la imagen

Podríamos dar por finalizado nuestro gráfico.
Adicionalmente podemos añadir un nuevo elemento a este gráfico de cascada, como son las líneas de conexión.
Bastará marcar la opción de Mostrar líneas de conexión con la serie seleccionada:

haz clic en la imagen

Fácil y rápido.

Seguir leyendo...

VBA: Contar Ceros en Rangos Discontinuos de Excel

En la entrada anterior del blog vimos el uso matricial de algunas funciones para conseguir contar el número de ceros (una condición dada cualquiera) sobre un rango de celdas discontinuos.

En el post de hoy veremos como conseguir ese conteo empleando una función personalizada con VBA para Excel.

En un módulo estándar de nuestro proyecto de VB insertamos la siguiente UDF:

Function CuentaCeros(rng As Range) As Long
Dim celda As Range, rgArea As Range

'iniciamos el contador de ceros
contador = 0

'recorremos cada área dada en el argumento de nuestra UDF
For Each rgArea In rng.Areas
    'recorremos cada celda de cada área
    For Each celda In rgArea
        'probamos la condición
        'si es Cero
        If celda = 0 Then     'And IsEmpty(celda) = False And Not (HasFormula(celda))
            contador = contador + 1
        End If
    Next celda
Next rgArea

'devolvemos el dato a la función
CuentaCeros = contador
End Function 

ASí, para nuestro ejemplo, en una celda añadiremos:
=CuentaCeros((A2:C2;E2:G2;I2:P2))

OJO!!, es muy importante añadir el doble paréntesis; pues es la manera de indicar que estamos trabajando con un conjunto de áreas
(de igual manera que lo haríamos con la función INDICE con su segunda sintaxis, por ejemplo)

Seguir leyendo...

Trabajar con rangos discontinuos en Excel

Aprenderemos hoy un buen truco para poder trabajar sobre rangos discontinuos.., en aquellos casos que los argumentos de nuestras funciones no lo admiten.
La idea surge a partir de la pregunta planteada:

[...]a ver si me explico, cada dato separado por la coma es una celda, a  b  c   d   e  f  g   h  i  j k  l m n o  p  q (12,14,15,)16,(17,0,14,)57,(0,36,78,0,87,9,56,0),7 y requiero sacar el promedio de solo los que están dentro del paréntesis, es decir de los que está dentro que no tome los ceros y los rangos dentro.[...]

Vemos los datos desplegados en la imagen siguiente... donde necesitamos calcular el promedio de las celdas en amarillo, pero únicamente de aquellos valores distintos de cero!!!.

Por supuesto, la solución más simple sería sustituir los ceros por vacío, par luego aplicar la función:
=PROMEDIO(A2:C2;E2:G2;I2:P2)
que nos devolverá la media correcta:= 33,8

Vamos a explicar un método sin necesidad de modificar los datos originales.
Recordemos que la media aritmética se calcula como el cociente entre la suma de los valores y el número de valores.

Para ello, por separado, calculamos la suma de los tres rangos amarillos, en la celda S2:
=SUMA(A2:C2;E2:G2;I2:P2)

En la celda T2 calculamos el número total de valores, incluido los valores cero:
=CONTARA(A2:C2;E2:G2;I2:P2)

Por último, en la celda U2 (aquí está el truco), calculamos el número de ceros en esos tres rangos...
Empleamos la función matricial siguiente:
=SUMA(CONTAR.SI(INDIRECTO({"a2:C2";"e2:g2";"i2:p2"});"0"))

De otro modo no es posible contar el número de ceros (o cualquier otra condición, sobre un rango discontinuo.

El uso dentro de la función INDIRECTO de una matriz:
{"a2:C2";"e2:g2";"i2:p2"}
permite lograr nuestro objetivo, y que la función CONTAR.SI realice el cálculo correcto, matricialmente en cada rango.
Podríamos ejecutar la fórmula separadamente:
CONTAR.SI(INDIRECTO({"a2:C2";"e2:g2";"i2:p2"});"0")
y veríamos nos devuelve el conteo de ceros en los tres rangos indicados:=
{0;1;3}
conteo que luego sumaremos para conocer el número de ceros.

Ya solo nos queda sumar, restar y dividir. En la celda U3 añadimos:
=+S2/(T2-U2)

En definitiva, la suma de todos los valores, lo dividimos por el resultado de restar al número total de valores aquellos que son cero.

Una alternativa matricial más, empleando la función FRECUENCIA:
=SUMA(A2:C2;E2:G2;I2:P2)/INDICE(FRECUENCIA((A2:C2;E2:G2;I2:P2);0);2)

La clave está en que FRECUENCIA con esa configuración, devolvería un rango de dos valores:
1-primer valor: número de ceros (en el ejemplo, 4)
2-segundo valor: número de NO ceros (en el ejemplo 10)
Luego con la función INDICE recuperamos el dato que nos interesa, es decir, el número de valores NO ceros...

El resto del cálculo responde a la misma premisa que la propuesta anterior.

Seguir leyendo...

Cómo Sumar Diagonales de una Matriz en Excel

Expondré hoy la forma de sumar los valores de ambas diagonales principales de una matriz cuadrada localizada en cualquier lugar.

La idea surge de la pregunta planteada por un lector:

Agradecería que me ayudara a sumar los elementos de las diagonales principales de cualquier matriz cuadrada, es decir dada una matriz cuadrada, entonces como hallar la suma de los elementos de las diagonales principales usando excel

En un primer paso introductorio, antes de resolver la cuestión planteada, obtendremos un listado de dichos elementos de la matriz.
Para esto emplearemos la función INDICE y FILAS con rangos corridos... Veamos cómo.
Listado de la diagonal principal 1:

La fórmula añadida en F4 será:
=INDICE(B4:D4;;FILAS($4:4))
observemos como trabajamos con un rango corrido $4:4 que aplicado sobre la función FILAS,nos devuelve para cada fórmula arrastrada:
FILAS($4:4) = 1
FILAS($4:5) = 2
FILAS($4:6) = 3
llegando a nuestro objetivo para cada quedarnos con la posición de columna 1, 2 ó 3 que empleamos como argumento de la función INDICE.

De forma similar para obtener la segunda diagonal principal.
Listado de la diagonal principal 2:

En este caso la fórmula añadida en F9 será:
=INDICE(B4:D4;;FILAS(4:$6))
observemos como trabajamos con un rango corrido 4:$6 que aplicado sobre la función FILAS,nos devuelve para cada fórmula arrastrada:
FILAS(4:$6) = 3
FILAS(5:$6) = 2
FILAS(6:$6) = 1
llegando a nuestro objetivo para cada quedarnos con la posición de columna 3, 2 ó 1 que empleamos como argumento de la función INDICE.

Vista esta introducción, vayamos ahora al cálculo directo de la suma de estos valores.

Para conseguir la suma de la diagonal principal construimos la siguiente fórmula:
=SUMA(SI(FILAS(B4:D6)+FILA(B4:D6)-MAX(FILA(B4:D6))=COLUMNAS(B4:D6)+COLUMNA(B4:D6)-MAX(COLUMNA(B4:D6));B4:D6;0))
(ejecutada matricialmente!!!)

La fórmula matricial es algo larga pero muy simple en cuanto a la idea.
Se trata de sumar solo aquellos elementos de la matriz cuadrada cuando coincidan ambos lados de la igualdad:
FILAS(B4:D6)+FILA(B4:D6)-MAX(FILA(B4:D6))=COLUMNAS(B4:D6)+COLUMNA(B4:D6)-MAX(COLUMNA(B4:D6))
que vemos en detalle en la imagen siguiente (por separado):

Si nos fijamos bien, comprobamos cómo ambas matrices se cruzan/coinciden en los elementos de la diagonal principal...

Finalmente, para conseguir la suma de la segunda diagonal principal construimos la siguiente fórmula:
=SUMA(SI(FILAS(B4:D6)-FILA(B4:D6)+MIN(FILA(B4:D6))=COLUMNA(B4:D6)-MIN(COLUMNA(B4:D6))+1;B4:D6;0))
(ejecutada matricialmente!!!)

El funcionamiento es idéntico a la anterior, obviamente con matrices coincidentes distintas:

Algo interesante de estas funciones matriciales es que sirven para cualquier situación o localización de la matriz cuadrada.

Como comentario final, y objetivo práctico del post, remarcar la importancia de las funciones empleadas FILA, FILAS, COLUMNA o COLUMNAS para determinar la posición de concreta de cada elemento... numerándolos tal y como necesitábamos par lograr las coincidencias en la igualdades o pruebas lógicas de la función SI; controlando así qué sumar y qué no.

Seguir leyendo...

Los Estilos de Celda en Excel

A todos nos ha ocurrido alguna vez que, al trabajar con distintos libros de Excel, copiando y pegando información de celdas entre varios libros.. finalmente comprobamos que nuestros formatos de algunas celdas cambian misteriosamente.
El causante de este misterio suelen ser los Estilos de celdas.

Para acceder a la configuración, modificación, eliminación o duplicación de Estilos de celda navegaremos por la Ficha Inicio > grupo Estilo > botón Estilos de celda:

Hace ya algún tiempo escribí algo al respecto (ver aquí).

En definitiva, no lo olvidemos, un Estilo es una configuración que afecta únicamente al formato de nuestras celdas, según una propiedades determinadas que afecta al formato de:
1- número
2- alineación
3- fuente
4- bordes
5- relleno
6- protección
es decir, las propiedades habituales que podemos definir en un formato de celda.

¿Qué ocurre en ocasiones cuando trabajamos con estilos personalizados?, pues que al copiar y pegar contenidos de celdas o rangos entre libros, trasladamos esos estilos personalizados/modificados entre dichos libros; y en consecuencia podemos afectar a nuestros formatos sin desearlo, heredando estos estilos...
Veamos la siguiente imagen, resultado obtenido tras copiar y pegar celdas de otros libros con estilos personalizados sobre un libro SIN estilos personalizados (únicamente los estilos por defecto):

Para evitar interferencias indeseables sobre nuestro libro de trabajo, lo recomendable sería eliminar cada estilo de celda personalizado.. basta hacer clic derecho y presionar Eliminar...
Si bien, cuando existen muchos estilos personalizados, el trabajo puede resultar tedioso. Para estos casos mejor emplear esta sencilla macro de Excel insertado en un módulo estándar de nuestro proyecto VB:

Sub Elimina_Todos_Estilos_Personalizados()
Dim estilo As Style

'recorremos los estilos de este libro de trabajo
For Each estilo In ThisWorkbook.Styles
    'evaluamos si el estilo no corresponde a alguno por defecto de la aplicación
    'en cuyo caso (es estilo personalizado) y lo eliminamos
    If Not estilo.BuiltIn Then estilo.Delete
Next estilo
 
End Sub

Otro sencillo procedimiento en VBA para Excel, esta vez una Function que puede sernos útil es:

Function EstiloDeCelda(celda As Range) As String
'la propiedad .Style nos indica el nombre del estilo de celda
EstiloDeCelda = celda.Style
End Function

Otra posibilidad, quizá recomendable, antes de eliminar los estilos de celda, sería aplicar el siguiente procedimiento para localizar las celdas con un estilo concreto buscado:

Sub LocalizaEstiloCelda()
Dim rRange As Range, celda As Range
Dim comparada As Range
Dim contador As Long

'buscamos estilos coincidente con el de la celda seleccionada (solo una)
Set comparada = Selection
'en caso de selccionar más de una celda, salimos del procedimiento
If comparada.Count > 1 Then Exit Sub

contador = 0
'recorremos todas las hojas del libro
For Each sh In Worksheets
    'definimos el rango de celdas usadas en cada hoja
    Set rRange = sh.UsedRange
    'y las recorremos
    For Each celda In rRange
        'comparando su estilo con el de la celda seleccionada
        If celda.Style = comparada.Style Then
            'en caso de coincidencia incrementamos el contador
            contador = contador + 1
            'y mostramos la dirección completa
            MsgBox celda.Address(external:=True)
        End If
    Next celda
Next sh

'Acabamos mostrando un mensaje que nos dice el númer de celdas con ese estilo
MsgBox "Número de celdas con estilo coincidente en el libro:= " & contador
End Sub

Por ejemplo lanzando el proceso sobre nuestro libro:

Vemos como he asignado a las celdas B3, D3 y F3 el mismo estilo de celda: Bueno.

Si ahora lanzamos la macro LocalizaEstiloCelda con la celda B3 seleccionada, tendríamos para cada celda coincidente del Libro de Excel:

Acabará el procedimiento con el último mensaje:

Para este último procedimiento se podría sustituir el segundo loop (for each.. next) por el método .Find para agilizar la búsqueda...

Seguir leyendo...