Señalar series por color en gráficos de Excel

jueves, noviembre 27, 2014

Supongamos esta situación:


Hemos representado en el gráfico una serie de valores que pertenecen a tres grupos (en la "vida real" podría tratarse de ventas de grupos de productos o de salarios de grupos de trabajadores, etc.).
Lo que queremos es representar cada grupo con un color distinto. Por defecto, Excel da a todos los puntos de una serie el mismo color. Podríamos cambiar el color de la columna de cada punto manualmente, pero podemos con hacerlo fácilmente usando la técnica que mostraré a continuación (y que ya he mostrado en alguna nota en el pasado).

El truco consiste en descomponer la única serie en tres series, una para cada grupo. Lo hacemos creando tres columnas auxiliares

En este ejemplo me he limitado a copiar los valores del grupo 1 a la columna D, los del grupo 2 a la columna E y los del grupo 3 a la columna F.
Para crear el nuevo gráfico seleccionamos el rango B2:B11 e inmediatamente, apretando y manteniendo apretada la tecla Ctrl. el rango D2:F11; seleccionamos Insertar-Gráfico-Columna, y obtenemos


Excel otorga automáticamente un color distinto a cada serie. Por supuesto podemos ocultar las columnas auxiliares o ponerlas en un rango oculto.

Mis perspicaces lectores habrán notado que existe un problema con las etiquetas del eje de las categorías (el eje de las X). Estas están alineadas a la izquierda, coincidiendo con el primer valor de cada grupo, cuando lo que quisiéramos es que estuvieran en el centro del grupo.

Para obligar a Excel a centrar los rótulos del eje agregamos una columna a nuestra tabla, entre las columna B y C; esta columna contiene un espacio (no está vacía, sencillamente seleccionamos la celda, apretamos la tecla de espacios e inmediatamente Enter)




Seguir leyendo...

Agregar controles en una hoja de Excel usando Vba

lunes, noviembre 24, 2014

Los controles (casillas de verificación, cuadros combinados, botones de opción, etc.) dan un "toque profesional" a la hoja pero no siempre son la mejor opción. En  general podemos encontrar soluciones más prácticas usando, por ejemplo, validación de datos y/o funciones SI.

Lo más corriente es agregar controles en la hoja en forma manual, usando el menú Desarrollador-Controles-Insertar. Existen dos colecciones de controles: Formulario y ActiveX.
En esta nota veremos como insertar controles ActiveX usando Vba.

Supongamos esta tabla de facturas con sus fechas de vencmientos


En el campo "Pagada" (columna E) anotamos "SI" cuando la factura ha sido pagada. Esto nos permite crear el informe que nos muestra los totales de facturas atrasadas, pagadas y a vencer.
Las fórmulas en el informe son:

celda H4:

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]<H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])


celda H5:

=SUMAR.SI(tblFacturas[Pagada],"SI",tblFacturas[Importe])


celda H6: 

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]>=H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])


la Tabla "tblFacturas" se refiere al rango B2:E16 (supongo que la mayoría de mis lectores ya hayan adoptado la sana costumbre de usar Tablas para organizar matrices de datos).

Y ahora vayamos a la cuestión de los controles incrustados en hojas de Excel. En nuestro ejemplo queremos usar casillas de verificación para señalar que una factura ha sido pagada  en lugar de un "plebeyo" SI.

Queremos que nuestro informe se vea así:


Si nuestra tabla tiene pocas filas podemos simplemente agregar los controles manualmente. Las casillas están definidas sin texto, ligadas a ka celda sobre la cual están ubicadas y el valor es FALSO. Todo esto tenemos que definirlo cambiando las propiedades por defecto de la casilla. Para ahorrarnos el trabajo de hacerlo cada vez que queremos agregar una casilla podemos usar esta macro:

Sub insert_one()
   
    With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
         Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
         Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
         .Object.Caption = ""
         .LinkedCell = ActiveCell.Address
         .Object.Value = False
         .Object.BackStyle = fmBackStyleTransparent
     End With
    
End Sub


El código agrega la casilla de verificación en la celda (con el método Add); luego definimos algunas propiedades:
Caption = "" para que la casilla no contenga ningún texto;

LinkedCell = Activecell.Address para ligar la casilla a la celda; esto es necesario para poder luego utilizar el valor de la casilla en nuestras fórmulas.

Value = False para que éste sea el valor por defecto en la celda vinculada a la casilla de verificación.

En la tabla de las facturas cambiamos el color de la fuente en las celdas de la columna Pagadas a blanco, para que el valor de la casilla en la celda vinculada no sea visible.

Dado que en el campo Pagada tenemos ahora valores FALSO o VERDADERO (cuando la casilla a sido marcada), tenemos que modificar nuestras fórmulas

celda H4:

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]<H3)* (tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])

celda H5:

=SUMAR.SI(tblFacturas3[Pagada],"VERDADERO",tblFacturas3[Importe])

celda H6: 

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]>=H3)*(tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])


Si tenemos una tabla con muchas filas podemos y queremos agregar las casillas en un única operación podemos usar esta macro (igual a la anterior a la que le hemos agregado un loop):

Sub insert_check()
    Dim rngCell As Range
 
    Application.ScreenUpdating = False
 
    For Each rngCell In Selection
        rngCell.Select
        With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
             Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
             Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
             .Object.Caption = ""
             .LinkedCell = ActiveCell.Address
             .Object.Value = False
         End With
     Next rngCell
    
     Application.ScreenUpdating = True
    
End Sub
Sub del_all_cb()





Seguir leyendo...

Cálculo de duración de procesos por turnos

lunes, noviembre 17, 2014

Los cálculos con fechas y horas en Excel suelen ser un tanto truculentos. Veamos por ejemplo esta consulta de una de mis lectoras:

Suponiendo que es 14-nov-14 8:00 am, tengo un proceso que dura 9 horas y mi turno dura 8 horas, cómo puedo hacer para que el día 15-nov-14 la hora de finalización sea 9:00am
Es decir, la tarea se realiza en un turno que empieza a las 08:00 AM y dura ocho horas. Si la tarea se extiende por más de ocho horas, ésta será completada el día siguiente.

La fórmula que nos permite hacer este cálculo es la siguiente

=SI(C5>C2,B5+TRUNCAR(C5/C2)+RESIDUO(C5,C2),B5+C5)

Veamos como funciona esta fórmula:

C5>C2 determina si el proceso puede completarse en las horas del turno;

B5 contiene la fecha y hora del comienzo del proceso;

TRUNCAR(C5/C2) nos calcula cuantos días debemos agregar a la fecha de iniciación del proceso para completarlo (múltiplos de la duración del turno);

RESIDUO(C5,C2) calcula cuántas horas debemos agregar a los múltiplos de ocho horas (la duración del turno).

De esta manera, si el proceso dura más de ocho horas, la función SI aplica la primer parte de la fórmula:

B5+TRUNCAR(C5/C2)+RESIDUO(C5,C2)



Las cosas se complican un poco (más) si queremos calcular la fecha de finalización del proceso tomando en cuenta sólo los días laborables.
Contamos para ello con la función DIA.LAB, es cierto, pero hay un pequeño problema. DIA.LAB no toma en cuenta las horas, sólo las fechas, como podemos ver en este ejemplo:


Dado que el 14/11/2014 cae un viernes, el próximo día laboral es el 17/11/2014 (lunes), pero podemos ver que la fórmula no toma en cuenta la hora de comienzo, sólo la fecha.
A pesar de esto podemos usar DIA.LAB para nuestro cálculo, de esta manera:

=SI(C5>C2,DIA.LAB(B5,TRUNCAR(C5/C2))+C1+RESIDUO(C5,C2),B5+C5)


donde C1 es la hora de comienzo del turno.



Seguir leyendo...

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP