ALGUNAS FÓRMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL

Hola a todos:

En muchas de las consultas que recibo, un tema recurrente es el de la confección de informes. Normalmente siempre preguntas y referencias a las tablas dinámicas (campos calculados, orden de etiquetas, etc…).

Y es que realmente la función de las tablas dinámicas es esa: mostrar información dinámica en la confección informes.

Sin embargo, una buen alternativa a las tablas dinámicas es hacer nuestro informe utilizando fórmulas y siempre con la ventaja de que no vamos a depender de las limitaciones típicas de las tablas dinámicas (colocación de información, límites, etc).

Por ello, en el post de hoy me gustaría trabajar con algunas funciones que nos van a permitir realizar interesantes informes y sin necesidad de usar tablas dinámicas.

Utilizaremos una de las bases de datos que uso habitualmente como ejemplo, una plantilla ficticia de unos grandes almacenes (si quereis saber cómo he generado los nombres, podeís visitar este post: COMBINAR LA FUNCIÓN HALLAR Y LA FUNCIÓN LARGO)

Imaginad que tenemos esta base de datos:

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL

Y necesitamos utilizar las fórmulas adecuadas para completar los datos de este informe (que ahora está vacío):

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_1

Para los campos de Sexo, Idioma y Estudios necesitamos funciones que cuenten y a su vez que admitan varios criterios de condición.

Para el campo de la edad necesitamos funciones que puedan obtener promedios, máximos y mínimos.

He creado cuatro hojas con el mismo informe, en cada una trabajaremos una fórmula distinta para contar.

En la hoja INFORME_SUMAPRODUCTO utilizaremos la función SUMAPRODUCTO, la cual vamos a modificar para que cuente los datos que necesitamos. Por ejemplo el número de Hombres en la sección de Deportes:

Solo tenemos que seleccionar un rango de la columna “C” (SECCION) de la Hoja BBDD y que sea igual a la celda A3 de nuestro informe (Deportes) e introducimos un segundo criterio, seleccionando la columna E de la hoja BBDD (SEXO) y la celda B2 de nuestro informe (Hombre).

=SUMAPRODUCTO((BBDD!$C$2:$C$65000=A3)*1;(BBDD!$E$2:$E$65000=$B$2)*1)

Para el resto de campos va a ser igual, excepto en IDIOMA (OTROS), donde debemos especificar que el idioma debe ser diferente de INGLES, FRANCES Y ALEMAN:

=SUMAPRODUCTO((BBDD!$C$2:$C$65000=A3)*1;(BBDD!$F$2:$F$65000<>$E$2)*1;(BBDD!$F$2:$F$65000<>$F$2)*1;(BBDD!$F$2:$F$65000<>$D$2)*1)

Como podéis observar, vamos introduciendo las condiciones de forma anidada y vinculando con el idioma que queremos excluir del proceso de contar empleados.

En la hoja INFORME_CONTAR.SI.CONJUNTO utilizaremos la función CONTAR.SI.CONJUNTO, presente desde Excel 2007 y es una buena opción para contar los elementos de nuestro informe. Siguiendo el ejemplo anterior (número de Hombres en la sección de Deportes) sería así:

=CONTAR.SI.CONJUNTO(BBDD!$C$2:$C$65000;A3;BBDD!$E$2:$E$65000;$B$2)

En la sintaxis seleccionamos rango de la columna SECCIÓN de la hoja BBDD y que sea igual a A3 y también seleccionamos la columna SEXO y que sea igual a B2 (Hombre).

Para el resto de campos va a ser igual, excepto en el IDIOMA (OTROS) donde usaremos la misma fórmula pero indicando los idiomas que no queremos que se tengan en cuenta:

=CONTAR.SI.CONJUNTO(BBDD!$C$2:$C$65000;A3;BBDD!$F$2:$F$65000;”<>” & $E$2;BBDD!$F$2:$F$65000;”<>” & $F$2;BBDD!$F$2:$F$65000;”<>” & $D$2)

Efectivamente usamos las clausulas <> para indicar que cuente aquellos que sea distintos de INGLES, FRANCES Y ALEMAN.

Pues bien, con cualquiera de estas fórmulas podríais realizar el informe perfectamente. Pero me gustaría dejaros otras dos fórmulas (matriciales) para realizar el mismo proceso:

En la hoja INFORME_MATRICIAL utilizaremos una matricial muy sencilla que realiza prácticamente el mismo trabajo que SUMAPRODUCTO (pero en matricial).

Usamos el mismo ejemplo que el de las fórmulas anteriores: Número de hombres en la sección “Deportes”:

{=SUMA((BBDD!$C$2:$C$65000=A3)*(BBDD!$E$2:$E$65000=$B$2))}

Y el resultado será el mismo que en las fórmulas anteriores, es decir 7.

El resto de campos será igual pero cambiando los parámetros, excepto en IDIOMA (OTROS), aquí tendremos que ampliar la fórmulas con más criterios:

{=SUMA((BBDD!$C$2:$C$65000=A3)*(BBDD!$F$2:$F$65000<>$E$2)*(BBDD!$F$2:$F$65000<>$F$2)*(BBDD!$F$2:$F$65000<>$D$2))}

Indicando qué idioma no queremos contar. Esta matricial, me parece muy sencilla y a la vez efectiva, es una de mis favoritas 🙂

En la hoja INFORME_MATRICIAL_2 utilizaremos otra matricial pero en la que usaremos la función CONTAR.

Para el cálculo del número de hombres en la sección “Deportes”, sería así:

{=CONTAR(SI((BBDD!$C$2:$C$65000=A3);SI(BBDD!$E$2:$E$65000=$B$2;BBDD!$A$2:$A$65000)))}

Sin embargo, esta fórmula tiene una peculiaridad, solo va a contar si el rango sobre el que se cuenta es numérico, en nuestro ejemplo podríamos hacer referencia a la columna A o a la D, en nuestro caso, he marcado la A en rojo en la fórmula. Por ello no la podréis usar si vuestra base de datos no tiene una columna con datos numéricos, tipo edad, ID, etc.

Para el contar las celdas de IDIOMA (OTROS), tendremos que incluir los criterios en la función, así:

{=CONTAR(SI((BBDD!$C$2:$C$65000=A3);SI(BBDD!$F$2:$F$65000<>$E$2;SI(BBDD!$F$2:$F$65000<>$F$2;SI(BBDD!$F$2:$F$65000<>$D$2;BBDD!$A$2:$A$65000)))))}

Y el resultado es el mismo para todos los casos:

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_2

Aunque no era el objeto de este post, os he incluido tres fórmulas más (matriciales) para calcular el promedio, el máximo y el mínimo de la edad por cada sección.

Para calcular el Promedio:

{=REDONDEAR(PROMEDIO(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}

y usamos la función redondear para eliminar todos los decimales (podríamos usar entero).

Para el cálculo de Max:

{=REDONDEAR(MAX(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}

Para el cálculo de Min:

{=REDONDEAR(MIN(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}

El resultado es este (el mismo para las cuatro pestañas):

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_3

Y las cuatro pestañas tienen que mostrar la misma información.

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_4

Hemos utilizado fórmulas distintas para contar numero de empleados por sección o departamento, según sexo, idioma o estudios. Y también hemos calculado algunos estadísticos para enriquecer la información.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Descarga el archivo de ejemplo pulsando enALGUNAS FÓRMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL

 

Anuncios

CÁLCULO DE DEPRECIACIONES CON EXCEL

Hola a todos 🙂

Para el post de hoy no voy a utilizar programación, ni macros ni VBA. Hoy vamos a trabajar solo con fórmulas. ¿y sobre qué tema trataremos hoy?, pues sobre algo que ya me han pedido más e una vez y que no he tenido tiempo de implementar y preparar para una publicación, el cálculo de las depreciaciones con las funciones programadas en Excel.

En Excel contamos con varias funciones para calcular la depreciación de un bien, cada una con su tipo de cálculo y su utilidad.

Para ilustrar el ejercicio e ir mostrando cada función, podré un ejemplo. Imaginad que os habéis comprado un coche y que os habéis gastado 70.000 euros, evidentemente es un gran coche, deportivo, con buenos acabados, asientos de cuero, muchos caballos de potencia, etc.  Y queremos calcular la depreciación de ese bien, según unos parámetros que vamos a ir indicando y modificando.

Como parámetros generales para todas las fórmulas, serán:

  • Coste del bien: 70.000 euros.
  • Valor residual: 10.000 euros.
  • Vida útil: 10 años.

Pues bien comenzaremos con la primera:

Función SLN o Método de Depreciación Directo: Devuelve la depreciación por método directo de un bien en un período dado.

Los elementos que debemos tener en cuenta en su sintaxis son el coste del bien, el valor residual, y la vida útil. El resultado nos dará una columna con el mismo importe, es decir, 6.000 euros. Y si queremos obtener como va descendiendo el valor por cada periodo solo tenemos que restarlo al valor anterior.

Más información en: Función SLN

El resultado es este:

CaLCULO DE DEPRECIACIONES CON EXCEL

Función SYD o Método de Depreciación por Anualidades: Devuelve la depreciación por suma de dígitos de los años de un bien durante un período específico.

Los elementos que debemos tener en cuenta en su sintaxis son el coste del bien, el valor residual, la vida útil y el periodo en el que nos encontramos, (siempre empezaremos por 1, el cero se utiliza en otra función que veremos más adelante).

Más información en: Función SYD

El resultado es el siguiente:

CaLCULO DE DEPRECIACIONES CON EXCEL2

 

Función DB o Método de Depreciación por Saldo Fijo: Devuelve la depreciación de un bien durante un período específico usando el método de depreciación de saldo fijo.

Los elementos que debemos tener en cuenta en su sintaxis son el coste del bien, el valor residual, la vida útil y el periodo en el que nos encontramos, (siempre empezaremos por 1, el cero se utiliza en otra función que veremos más adelante) y el mes (que es el número de meses del primer año, si lo omitimos tomará como referencia el 12).

Más información en: Función DB

El resultado es el siguiente:

CALCULO DE DEPRECIACIONES CON EXCEL4

 

Función DDB o Método de Depreciación por doble disminución de saldo Devuelve la depreciación de un activo en un período específico con el método de depreciación por doble disminución de saldo u otro método que se especifique.

Los elementos que debemos tener en cuenta en su sintaxis son el coste del bien, el valor residual, la vida útil y el periodo en el que nos encontramos, (siempre empezaremos por 1, el cero se utiliza en otra función que veremos en la siguiente función) y el factor: que es la tasa de depreciación del saldo. Si omite este valor, se supone automáticamente que es 2 (el método de depreciación por doble disminución de saldo).

Más información en: Función DDB

El resultado es el siguiente:

CALCULO DE DEPRECIACIONES CON EXCEL5

 

Función DVS o Método de Depreciación por doble disminución de saldo Devuelve la depreciación de un bien durante un período especificado, inclusive un período parcial, usando el método de disminución doble del saldo o cualquier otro que especifique y según el coeficiente que usted decida. Las iniciales DVS corresponden a disminución variable del saldo.

Los elementos que debemos tener en cuenta en su sintaxis son el coste del bien, el valor residual, la vida útil, el periodo que queremos calcular (inicial y final) el primer periodo será cero, y el factor (que ya lo explicamos en la función anterior).

Más información en: Función DVS

El resultado es el siguiente (en este ejemplo obtengo en el periodo 6 el resultado acumulado hasta el periodo 10).

CALCULO DE DEPRECIACIONES CON EXCEL6
Y una vez que ya tenemos todas las variantes calculadas, hemos acabado el ejercicio. Aquí os dejo el resultado de todos los cálculos.

No he incluido ninguna automatización ni macros, es cálculo puro y aplicación de funciones. Si necesitáis ampliar periodos debéis incluirlos en le rango y aplicar las fórmulas.

Por cierto, que rápido pierden valor algunos objetos …al contrario de otros, que supongo que la función en lugar de depreciar debería reevaluar el objeto, por ejemplo, el arte.

CALCULO DE DEPRECIACIONES CON EXCEL7

 

Descarga el archivo de ejemplo pulsando en: CÁLCULO DE DEPRECIACIONES CON EXCEL

FUNCIONES MATRICIALES PARA OBTENER MEDIANA, MAX, MIN Y OTRAS FÓRMULAS.

Hola  a todos:

Hoy quiero escribir acerca de varios temas en este post, uno de ellos es la utilización de las fórmulas matriciales para obtener datos relevantes en conjuntos de información elevados.

Para poder ilustrar el ejercicio, vamos a realizar un ejemplo, imaginad que tenéis a los alumnos de dos clases (1º -A y 1º-B) y que os han enviado las notas por alumno de las siguientes asignaturas, de Física, Química y Matemáticas:

FUNCIONES MATRICIALES PARA OBTENER MEDIANA, MAX, MIN Y OTRAS FORMULAS

Como podéis ver, hay de todo … ¡desde un 10 hasta un 1!, (no he querido poner un cero, creo que todo el mundo, por el mero hecho de estar en un aula ya merece ese punto de cortesía).

Ahora resulta que necesitáis saber cual es la mediana, pero no el dato general, para este ejemplo, la mediada de todos los estudiantes de 1ªA en Física, esto lo podéis hacer mediante las siguientes fórmulas matriciales:

Hallando el PERCENTIL 50 que equivale a la mediana:

{=PERCENTIL(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000));50%)}

O directamente usando la función MEDIANA

{=MEDIANA(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000)))}

En ambos casos el resultado será el mismo, particularmente me gusta usar la fórmula de los percentiles 🙂

El resultado es el siguiente:

FUNCIONES MATRICIALES PARA OBTENER MEDIANA, MAX, MIN Y OTRAS FORMULAS1

Efectivamente, el resultado es un 6,5. Como se puede apreciar en ambas fórmulas matriciales se combinan los criterios de la CLASE y de la ASIGNATURA, para obtener el resultado específico de los datos seleccionados.

Ahora que tenemos la mediana, queremos obtener en las dos columnas siguientes el resultado mínimo y el máximo para el mismo colectivo, es decir que el máximo debería ser el 10 y el mínimo el 4. Las fórmulas a utilizar serían las siguientes:

Para hallar el valor máximo de las notas de los estudiantes de 1:

{=MAX(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000)))}

O directamente usando la función MEDIANA

{=MIN(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000)))}

El resultado es el esperado:

FUNCIONES MATRICIALES PARA OBTENER MEDIANA, MAX, MIN Y OTRAS FORMULAS2

Y como resulta que queremos automatizar esta información cada cierto tiempo, hemos decidido programar estos cálculos en VBA.

Como sabéis, en VBA podemos utilizar la propiedad  .Formula que representa la fórmula del objeto en notación del estilo A1. Esto es muy útil, dado que podemos expresar el resultado del cálculo, no como valores, sino como la propia fórmula, es decir que dejará el resultado con la fórmula introducida.

Para el caso de las fórmulas matriciales, debemos especificar en el código .FormulaArray =
Teniendo en cuenta esto, ya podemos automatizar el cálculo, esta sería la macro:

Sub VARIAS_MATRICIALES()
'Definimos variables
Dim i As Integer, fin As Integer, elimina As Integer
With Sheets("DATOS")
elimina = Application.CountA(Worksheets("DATOS").Range("A:A"))
If elimina > 0 Then Worksheets("DATOS").Range("F2:H" & elimina).ClearContents
fin = Application.CountA(.Range("A:A"))
'Aplicamos fórmula matricial para hallar la mediana, el valor máximo y el mínimo según criterios indicados
For i = 2 To fin
.Cells(i, 6).FormulaArray = "=percentile(if($C$2:$C$65000=C" & i & ",if($D$2:$D$65000=D" & i & " , $E$2:$E$65000)),50%)"
.Cells(i, 7).FormulaArray = "=max(if($C$2:$C$65000=C" & i & ",if($D$2:$D$65000=D" & i & " , $E$2:$E$65000)))"
.Cells(i, 8).FormulaArray = "=min(if($C$2:$C$65000=C" & i & ",if($D$2:$D$65000=D" & i & " , $E$2:$E$65000)))"
Next
End With
End Sub

Obviamente, para este ejemplo, he utilizado unas fórmulas determinadas, pero se pueden usar múltiples funciones, por ejemplo, la desviación estándar, la suma, el promedio, cuartiles,  etc.

Me ha parecido útil este post por la versatilidad que nos ofrecen las matriciales a la hora de obtener datos relevantes para nuestros análisis y por compartir el uso de la propiedad .Formula en vuestros proyectos VBA.

Estas fórmulas pueden ser ideales para multitud de tareas, análisis de bandas salariales, ventas, etc y además se pueden introducir en tablas o gráficos dinámicos que agregarán valor añadido a nuestros análsis.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Descarga el archivo de ejemplo pulsando en: FUNCIONES MATRICIALES PARA OBTENER MEDIANA, MAX, MIN Y OTRAS FÓRMULAS

 

CALCULAR DÍAS, MESES Y AÑOS ENTRE DOS FECHAS Y DIFERENTES PERIODOS

Hola a todos!!

Hace unos días recibí una consulta sobre el cálculo de días, meses y años entre dos fechas, pero aplicado a varios periodos, es decir, el obtener los días, los meses y los años entre dos fechas es sencillo cuando aplicamos la función SIFECHA(), podéis profundizar un poco más en este post: CALCULAR AÑOS ENTRE DOS FECHAS EN EXCEL.

Es aplicar la función sifecha() para obtener los días, los meses y los años entre las dos fechas, y donde los elementos se relacionan entre sí, es decir, no son datos absolutos, sino que los meses van acumulando los días y los años van acumulando los meses, este sería un ejemplo:

calcular-dias-meses-y-anos-entre-dos-fechas-y-diferentes-periodos

Las fórmulas usadas para obtener este resultado con:

Años: =SIFECHA(A2;B2; "Y")
Meses: =SIFECHA(A2;B2; "YM")
Días:=SIFECHA(A2;B2; "MD")
Antigüedad General: =SIFECHA(A2;B2; "y")& " años  " & SIFECHA(A2;B2; "ym")& " meses  " &SIFECHA(A2;B2; "md")& " dias "

Hasta ahora esto es sencillo, pero ¿cómo hacemos para calcular el sumatorio de los años, los meses y los días? teniendo muy en cuenta que no podemos sumar simplemente, dado que los días no pueden ser más que los días que tiene un mes.

Lo que estamos buscando sería esto (siguiendo nuestro ejemplo):

calcular-dias-meses-y-anos-entre-dos-fechas-y-diferentes-periodos2

Como podéis observar, el resultado no es la simple suma de conceptos por columna, sino que existe una relación entre días, meses y años, de forma que los días sobrantes se van trasladando a los meses y los meses sobrantes se van trasladando a los años (en este la suma de los periodos no llegan a un año).

Para conseguir este resultado vamos a introducir la siguiente fórmula:

Para los días:
=SI(SUMA($E$2:E5)>30,5;SUMA($E$2:E5)-(ENTERO(SUMA($E$2:E5)/30,5)) *(30,5);SUMA($E$2:E5))

Para los meses:
=SI(SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5)>11;((SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5))-(ENTERO((SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5))/12))*12);SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5))

Para los años:
=SUMA($C$2:C5) +ENTERO((SUMA($D$2:D5) +((SUMA($E$2:E5)-E6)/12)-D6)/12)

Esta fórmula es bastante conocida en manuales, foros, etc… y me ha parecido interesante dejarla en la web. He realizado algún ajuste que creía interesante para reducir el contenido de la fórmula.

Descarga el archivo pulsando en: CALCULAR DÍAS, MESES Y AÑOS ENTRE DOS FECHAS Y DIFERENTES PERIODOS

CALCULAR PROMEDIO SIN TENER EN CUENTA LOS CEROS

Hola a todos,

Espero que hayáis disfrutado del verano y de las vacaciones 🙂 seguro que sí!!. Bien, hoy voy a realizar una entrada muy corta pero que se presenta muy habitualmente en nuestras hojas de cálculo: el cálculo de los promedios sin tener en cuenta los ceros.

La función “Promedio” en Excel calcula el promedio en un rango definido de datos, es una fórmula sencilla y muy útil. No tiene en cuenta las celdas que no contienen datos (vacías) pero sí que tiene en cuenta las celdas que contienen un “0”.

Normalmente esto no representa un problema, el cero se asume con un número más y se realiza el promedio. Pero ¿qué sucede si no debemos tener en cuenta los ceros en nuestros cálculos?, por ejemplo, en los resultados de facturación de un grupo de vendedores en el primer semestre del año, imaginad que se han ido de vacaciones (y que todos han tomado el mes que le corresponde). Veamos el cálculo del promedio con la fórmula habitual:

calcular-promedio-sin-tener-en-cuenta-ceros1

Así es, el promedio por línea se calcula así:

=PROMEDIO(C3:I3)

Como podéis observar en el primer caso, el comercial Arturo ha facturado un promedio de 1.352,14 euros en el primer semestre. Pero dado que en Julio se fue de vacaciones y en nuestra empresa tenemos este dato muy en cuenta, el promedio lo queremos obtener de aquellos meses en los que ha trabajado. En realidad Arturo ha obtenido un promedio de 1.557,50 euros (fruto de los meses de Enero a Junio).

Esto lo vamos a calcular con una fórmula matricial que va a omitir del cálculo del promedio aquellas celdas que contengan un “0”, ya sabéis que para introducir una fórmula matricial es necesario que seleccionéis la fórmula y luego pulséis: Ctrol + Alt + Enter.

{=PROMEDIO(SI(C17:I17<>0;C17:I17))}

En el cuadro de abajo tenemos los cálculos correctamente realizados:

calcular-promedio-sin-tener-en-cuenta-ceros2

Como se puede ver, la diferencia es importante y es algo que se debería tener en cuenta a la hora de realizar promedios.

Descarga el archivo de ejemplo pulsando en: CALCULAR PROMEDIO SIN TENER EN CUENTA LOS CEROS

GENERAR DIAGRAMA DE GANTT EN UN GRÁFICO CON VBA

Hace unas semanas escribí un post acerca de cómo confeccionar un diagrama de Gantt usando únicamente nuestra hoja Excel, fórmulas y vba: CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA (Y FÓRMULAS)

Siguiendo con esta temática, y dadas algunas consultas que he recibido, donde me preguntaban si podría realizarse este mismo trabajo pero empleado un gráfico (de barras) donde se pueda mostrar el tiempo consumido y el tiempo pendiente, me he decidido a realizar otro post complementario del anterior respondiendo a esas consultas.

Veamos, vamos a seguir con mismo ejemplo de proyecto que el post anterior, pero explicaremos dos maneras de realizar el ejemplo, uno donde tendremos que introducir los datos manualmente para tener actualizado nuestro diagrama y otro donde una vez introducidas las fechas programadas, Excel lo hará todo automáticamente.

Primero os mostraré la parte de fórmulas (en la hoja) y luego vemos la macro, que será la misma para ambos casos.

Para el ejemplo de forma manual vamos a realizar en nuestra hoja el siguiente cuadro, con cuatro columnas: Detalle de las tareas, fecha de inicio, tiempo consumido (días) y tiempo restante (días):

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA1

Para este tipo de informe, vamos a necesitar que cuando un valor sea “0” no aparezca, que quede en blanco (en las columnas de tiempo consumido y tiempo restante). Esto lo podemos hacer con una macro, pero me ha parecido interesante introducir un pequeño truco para lograrlo de una forma igual de eficiente. Tan solo tenéis que seleccionar las dos columnas y en formato de celda > personalizada > introducís el siguiente formato: #"" de esta manera el cero no aparecerá.

El resultado, una vez aplicada la macro sería este:

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA

Como podéis observar, aquí seremos nosotros los encargados en ir modificando los tiempos de forma manual, es decir que a medida que avance el proyecto el tiempo consumido tenderá a aumentar y el tiempo restante a disminuir, de manera que cuando finalicemos todas las barras de progreso serán de color azul.

Sin duda, el tener que ir modificando los datos de forma manual puede llegar a ser (si el proyecto es muy grande) en un trabajo bastante tedioso, para ello, realizaremos las siguientes modificaciones añadiendo algunas columnas y fórmulas de apoyo. Esta es la segunda forma que os comentaba anteriormente:

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA2

Añadimos una columna para determinar la fecha final estimada.
Añadimos otra columna denominada “Control” en la que vamos a introducir la siguiente fórmula:
=SI(E2<HOY();E2;HOY()) de manera que si la fecha fin es menor que hoy() ponemos la fecha fin, de lo contrario indicamos la fecha actual

Añadimos otra columna denominada “Tiempo Total” indicando el tiempo en días desde la fecha inicio hasta la fecha fin, con la siguiente fórmula:
=SIFECHA(B2;E2;"D")

En la columna de “Tiempo Consumido” introducimos la siguiente fórmula:
=SIFECHA(B2;F2;"D") que es la diferencia en días desde la fecha de inicio a la fecha de la columna “Control”, y nos devolverá los días que hemos consumido hasta hoy().

En la columna “Tiempo Restante” introducimos la siguiente fórmula:
=SI(C2>G2;"0";G2-C2) donde si el Tiempo Consumido es mayor que el Tiempo Total ponemos un cero (pero al tener formateada la columna aparecerá vacío), y si no lo es extraemos la diferencia en días entre el tiempo Consumido y el Tiempo Total, obteniendo el tiempo restante.

Puede parecer un poco complejo, pero el resultado es interesante, ya que ahora siempre tendremos actualizado el progreso de nuestro proyecto al día en el que nos encontramos. Si se producen modificaciones, tan solo tendríamos que modificar la fecha fin a mano y listo.

Si confeccionáis el proyecto a futuro, el gráfico os saldrá completamente en blanco (es obvio) ya que todavía no habéis empezado, y en el momento de finalizar estará completamente en azul.

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA3

Esta imagen dependiendo del momento en el que descarguéis el archivo no se corresponderá con lo que estáis viendo ahora, ya que los días han pasado y seguramente esté todo en azul.

Ahora os dejo la macro comentada, que debéis pegar en un módulo estándar:

Sub Gantt()
'Definimos variables
Dim Chart As Excel.ChartObject
Dim gGrafic As Chart
'Con la hoja activa
With ActiveSheet
'Si existe algún tipo de gráfico, lo borramos
For Each Chart In .ChartObjects
Chart.Delete
Next
'Guardamos longitud de datos
fin = Application.CountA(.Range("A:A"))
'insertamos gráfico
.Shapes.AddChart.Select
'Seleccionamos área de gráfico
ActiveChart.ChartArea.Select
With ActiveChart
'Configuramos gráfico con ChartWizard
.ChartWizard Source:=ActiveSheet.Range("A1:D" & fin), Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=1
'Indicamos posición del gráfico
.ChartArea.Left = Sheets(2).Cells(11, 1).Left
.ChartArea.Top = Sheets(2).Cells(11, 1).Top
'Indicamos el largo del gráfico
.ChartArea.Width = 800
'Indicamos el ancho
.ChartArea.Height = 200
'Ancho de intervalo que sea igual a cero
'de forma que las barras ocupen todo el ancho
.ChartGroups(1).GapWidth = 0
'La primera colección es la columna de las fechas de inicio
'debemos hacerlas invisibles en el gráfico, y lo configuramos así:
With .SeriesCollection(1)
With .Border
.LineStyle = xlNone
End With
.InvertIfNegative = True
.Interior.ColorIndex = xlNone
End With
'la segunda colección (tiempo consumido)
With .SeriesCollection(2)
'Damos color azul (dejo formato RGB para mayor elección de color)
.Interior.Color = RGB(0, 153, 255)
'Mostramos etiquetas
.ApplyDataLabels
End With
'la tercera colección (tiempo restante)
With .SeriesCollection(3)
'Damos color rojo (dejo formato RGB para mayor elección de color)
.Interior.Color = RGB(255, 0, 0)
'Mostramos etiquetas
.ApplyDataLabels
End With
'Eje de los valores
With .Axes(xlValue)
'Indicamos la fecha mínima
.MinimumScale = Application.WorksheetFunction.Min(ActiveSheet.Range("B2:B" & fin))
'Ocultamos las barras divisorias principales en vertical
.HasMajorGridlines = False
End With
'Eje de categorías
With .Axes(xlCategory)
'Invertimos el orden
.ReversePlotOrder = True
'Mostramos las barras divisorias principales en horizontal
.HasMajorGridlines = True
End With
End With
End With
End Sub

Además en el módulo de cada hoja que contenga los datos, debéis pegar lo siguiente, así con cada cambio que hagáis en la hoja, los datos se actualizarán.

Private Sub Worksheet_Change(ByVal Target As Range)
Call Gantt
End Sub

También podéis utilizar solo la macro principal y vincular a un botón en la hoja, pero debéis cambiar activesheet por la hoja en la que tengáis los datos.

Por supuesto, el gráfico lo podéis situar en el lugar de la hoja que mejor consideréis, la instrucción está en estas líneas de la macro, donde situamos el gráfico según la posición de las celdas, en este caso fila 11, columna 1.

.ChartArea.Left = Sheets(2).Cells(11, 1).Left
.ChartArea.Top = Sheets(2).Cells(11, 1).Top

La clave de todo este proceso consiste en ocultar (transparentes) las barras que hacen referencia a las fechas de inicio y dar color a las otras dos columnas (además de invertir posición y configurar el ancho).

Y aquí finalizamos este pequeño ejercicio. Sin duda una buena opción para controlar diariamente y de forma automática el progreso de nuestros proyectos.

Espero que os sea de utilidad 🙂

Descarga el archivo de ejemplo pulsando en: GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA

 

USAR COMODINES EN LA FUNCIÓN SUMAPRODUCTO

Uno de los post más visitados de la web es el uso de la función SUMAPRODUCTO para contar valores, y como una alternativa a Contar.si.conjunto. Además de ser una fórmula muy interesante (naturaleza matricial), lo importante es que muestra el dato cuando hacemos referencias a libros cerrados, cosa que con funciones como Constar.si.Conjunto o Sumar.si.conjunto sí sucede.

El único “problemilla” que tiene esta función es que no admite comodines, ni el asterisco (*), ni el dólar ($), la interrogación (?)… Y esto puede ser un problema cuando trabajas con grandes cadenas de datos y deseas contar valores que pueden tener varios nombres, ejemplo “alimentación”, “aliment.”, “alimentos” para referirse al mismo concepto.

Pues bien, existe una solución a este problema (más bien 4), aunque podrían implementarse más fórmulas. La clave está en combinar diferentes funciones con SumaProducto para obtener lo que estamos buscando.

Veamos un pequeño ejemplo de una base de datos de trabajadores de unos grandes almacenes (respecto a crear nombres para hacer pruebas tenéis aquí un post interesante):

USAR COMODINES EN SUMAPRODUCTO

Y ahora imaginad que queremos contar el número de trabajadores en base a dos criterios: que trabajen en la sección “ALIMENTACIÓN” y que sepan hablar “CHINO”. Bien, para esto podremos utilizar la fórmula SumaProducto e introducirla de la siguiente forma:

=SUMAPRODUCTO(($C$2:$C$65000="ALIMENTACIÓN")*1;($F$2:$F$65000="CHINO")*1)

El resultado son 4 personas, pero fijaros que en la fórmula, el criterio es exacto, Alimentación y Chino, de forma que si en nuestra base de datos alguien introduce una abreviatura de por ejemplo Chino (CH), el resultado de la cuenta no sería el mismo, o ALIM de Alimentación. Pues bien, para prevenir estos problemas, os muestro 4 soluciones:

1. La primera consiste en combinar SumaProducto con la función Izquierda. De manera que podamos buscar coincidencias por las primeras letras y contar los resultados:

=SUMAPRODUCTO((IZQUIERDA($C$2:$C$65000;4)="ALIM")*1;(IZQUIERDA($F$2:$F$65000;2)="CH")*1)

El resultado será 4, (importante, en la función Izquierda, además del nombre del criterio hay que indicar el número de caracteres, ejemplo ALIM=4 o CH=2

2. La otra alternativa (y supongo que ya sabréis a la que me refiero) es utilizar Derecha en lugar de Izquierda, de forma que buscaremos coincidencias por las últimas letras o caracteres.

=SUMAPRODUCTO((DERECHA($C$2:$C$65000;9)="MENTACIÓN")*1;(DERECHA($F$2:$F$65000;3)="INO")*1)

Al igual que en la fórmula anterior es necesario indicar el número de caracteres.

3. La tercera alternativa sería combinar SumaProducto con la función Encontrar, pero en este caso será necesario introducir la función de forma matricial (Ctrol + Alt + Enter).

{=SUMAPRODUCTO(((SI.ERROR(ENCONTRAR("ALIM";$C$2:$C$65000);"0")))*1;(SI.ERROR(ENCONTRAR("CH";$F$2:$F$65000);"0")))*1}

Es necesario también hacer uso de la función Si.Error para eliminar los error y pasarlos a valor “0”.

4. Por último, podremos combinar la función SumaProducto con la función Hallar, y de la misma forma que el caso anterior, será necesario introducir el cálculo de forma matricial:

{=SUMAPRODUCTO(((SI.ERROR(HALLAR("ALIM";$C$2:$C$65000);"0")))*1;(SI.ERROR(HALLAR("CH";$F$2:$F$65000);"0")))*1}

Como podéis observar sí que existen alternativas a SumaProducto con uso de comodines, simplemente hacemos que algunas funciones trabajen como si fuesen un comodín. Aquí tenéis todas las fórmulas con los mismos resultados:

USAR COMODINES EN SUMAPRODUCTO1

Descarga el archivo de ejemplo pulsando en: USAR COMODINES EN LA FUNCIÓN SUMAPRODUCTO