Marcar doce filas por encima con un formato condicional.

No recuerdo muy bien cómo se me planteó la cuestión... pero el trabajo consistía en marcar con un formato doce filas por encima del último dato introducido (esto es, un año antes).
Veamos el planteamiento y estructura de datos:

El asunto es claro, según completamos la tabla de meses con información, se debería 'formatear' el valor correspondiente a 12 meses antes, esto es, doce filas por encima.

Comenzamos entonces seleccionando el rango que queremos formatear, en nuestro ejemplo B1:B24, como siempre nos aseguramos qué celda está activa, para nosotros la celda B1.
Accederemos a la herramienta Foramto condicional tipo fórmula, e insertaremos:
=Y(CONTARA($B:$B)-12=FILA($B1))
dando a continuación el formato deseado, tal como muestra la imagen:

La clave como siempre en este tipo de formatos es el valor lógico devuelto de nuestra fórmula, que como vemos compara un conteo de datos sobre la columna B (restándole 12) con el valor de la fila de la celda evaluada...
En la siguiente imagen vemos trasladada la fórmula a la hoja de cálculo y que valores nos devuelve. Recordemos que el formato se aplicará en aquellas celdas cuyo valor sea VERDADERO:

Seguir leyendo...

VBA: El control Slider o Deslizador - Microsoft Slider control, version 6.0

Trabajaré con versión Excel 2010, aunque para versiones anteriores funciona exactamente igual... también existe tanto para insertar en la hoja de cálculo como en un Userform.
Se trata de añadir un Control adicional en particular, el Microsoft Slider control, version 6.0 que tendrá este aspecto:

Para llegar a este control seguiremos cuatro sencillos pasos:
Paso 1: Iremos a la ficha Programador > grupo Controles > botón Insertar > Controles ActiveX > Más controles

Paso 2: En la ventana diálogo que se abra buscaremos el control en cuestión: Microsoft Slider control, version 6.0

Paso 3: Insertar el control en nuestra hoja de cálculo (o UserForm), tra lo cual cambiaremos a Modo diseño en la ficha Programador > grupo Controles > botón Modo diseño.
Con modo diseño activado presionaremos el botón Propiedades:

Paso 4: En la venta Propiedades abierta nos fijaremos en las siguientes propiedades en particular:
Min
Max
SmallChange
LargeChange
TickStyle
y
en algunos casos también
SelectRange

Para nuestro ejemplo estas son las propiedades activadas:
Min = 0
Max = 25
SmallChange = 1
LargeChange = 5
TickStyle = 2-sldBoth
SelectRange = True

Como definiciones interesantes que emplearemos a continuación sirvan las siguientes:
SelStart : Devuelve o establece el valor de inicio de una selección.
SelLength : Devuelve o establece la longitud de una selección.
SelectRange : Devuelve o establece si un control Slider puede tener un intervalo de selección.

Una vez realizada esta configuración pasamos a la parte de código. Para ello accederemos a la ventana de código desde el control presionando botón derecho del ratón y luego Ver código; ya en la ventana de código insertaremos los siguientes procedimientos y eventos:

Private Sub Slider21_scroll()
'con el evento específico de _scroll llamamos a procedimiento Sub SliderHasta
Call SliderHasta
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub SliderHasta()
'situamos la posición de partida en el mínimo configurado en Propiedades
    Slider21.SelStart = Slider21.Min
'damos longiutd a la selección
    Slider21.SelLength = Slider21.Value
'Devolvemos el valor a una celda de la hoja
    Range("C6").Value = Slider21.Value
End Sub

Merece especial atención mencionar los eventos de los que que dispone este control; en general son los que posee la mayoría de los controles: Click, GotFocus, LostFocus, KeyDown, KeyUp, KeyPress, MouseDown, MouseMove etc... Pero también pose otros dos, llamados _Scroll y _Change.

El evento _Scroll se ejecuta cada vez que desplazamos el cursor (el slider); mientras que el evento _Change cada vez que la propiedad Value cambie de valor.

Para cambiar el valor de este control podemos optar por:
1- hacer clic en la barra del control, o manteniendo presionado el botón izquierdo del ratón hasta el punto deseado.
2- usar la rueda del ratón, con el control activo.
3- usar el teclado (flechas del cursor, Avance o Retroceso página, Inicio, Fin...)

El efecto final es el mostrado al inicio de la entrada...

Seguir leyendo...

Contando días laborables.. incluídos sábados.

Trabajaremos hoy un poco combinando algunas funciones matriciales con otras funciones de Fechas como DIASEM y DIAS.LAB que ya conocemos (ver), y aprenderemos una nueva que aparece ne versiones 2010+ que es DIAS.LAB.INTL.
El post de hoy sirve en respuesta a una consulta recibida de un usuario ya avanzado:

...necesito saber los días laborables,incluyendo sabados entre dos fechas, pero descontando los días festivos. ejemplo; del 01/11/203 al 30/11/2013 saber cuantos dias laborables ( de lunes a sabado) hay pero descontando los festivos de ese mes. he probado con =SUMA(SI(DIASEM(A12+FILA(INDIRECTO("1:"&B12-A12-1));2)<>7;1;0);1), donde A12 y B12 son 1/11/13 y 30/11/13 y no me cuenta el dia 1/11/13 pero tampoco el 9/11/13 que es festivo. Tengo un rango D2:D6 con los feriados...

Siguiendo las indicaciones dadas replicamos el ejercicio, he anexado además el detalle del mes de estudio para verificar y ver mejor el planteamiento:

Como observamos el planteamiento es claro, necesitamos contar los dias del mes de Lunes a Sábado que no sean festivos. Para el mes de Noviembre con festivos el 01/11/2013 y 9/11/2013 nos saldrían 24 días laborables.

No podemos emplear directamente la función DIAS.LAB ya que esta función no tiene en cuenta los sábados y domingos como laborables; pero en cambio en versiones Excel 2010 + podemos emplear la función
=DIAS.LAB.INTL(fecha_inicial; fecha_final; [fin_de_semana]; [festivos])
estando la clave en el tercer argumento, que nos permite indicar qué entendemos como fin de semana:
Número de fin de semana /Días de fin de semana
1 u omitido := Sábado, Domingo
2 := Domingo, Lunes
3 := Lunes, Martes
4 := Martes, Miércoles
5 := Miércoles, Jueves
6 := Jueves, Viernes
7 := Viernes, Sábado
11 := Solo domingo
12 := Solo sábado
13 := Solo martes
14 := Solo miércoles
15 := Solo jueves
16 := Solo viernes
17 := Solo sábado

en nuestro caso aplicaremos el valor 11, quedando nuestra fórmula para el ejemplo planteado:
=DIAS.LAB.INTL(A12;B12;11;D2:D6)
lo que nos devuelve efectivamente el valor buscado de 24 días no festivos en el mes.

El problema viene cuando trabajamos con versiones anteriores de Excel, es entonces cuando no queda más remedio que emplear matriciales, combinadas con otras funciones.
Desglosaré la fórmula para verla mejor. En primer lugar calculo el número de días laborables con DIAS.LAB sin incluir ni sábados ni domingos en la celda D9:
=DIAS.LAB(A12;B12;D2:D6)
lo que devuelve 20 días, esto son todos los lunes a viernes del mes excepto el 01/11/2013 que es viernes festivo.

Por otro lado en la celda D12 añadimos la fórmula matricial
=SUMA(SI(DIASEM(A12+FILA(INDIRECTO("1:"&B12-A12-1));2)=6;1;0);1)
que construye un rango 'virtual' con todos los días del mes, a los que aplica una condición de si 'es sábado' (DIASEM = 6) entonces valor 1 en otro caso 0, tras sumar el resultado matricial obtenemos el número de sábados total del mes (incluidos festivos)

A este último valor de sábados totales solo nos queda restar los festivos que sean sábado, por lo que aplicaremos enla celda F11 una nueva fórmula matricial aplicada sobre nuestro rango de Festivos D2:D6:
=SUMA(SI(SI(DIASEM(D2:D6;2)=6;D2:D6;0)>0;1;0))
lo que obviamente nos devuelve un resultado de 1 festivo que es sábado.

Seguir leyendo...

Seleccionar datos en una Tabla dinámica: La opción Habilitar selección.

Muy frecuentemente trabajamos con Tablas dinámicas en Excel, y durante esos trabajos debemos acertar a la hora de Seleccionar datos dentro de ella; bien para aplicar algún tipo de formato o estilo o para realizar alguna agrupación o subtotal, etc...
Quizá nunca hayamos prestado atención a un detalle, y es que cunado queremos seleccionar una parte de la tabla dinámica nuestro cursor se convierte en una flecha 'gorda' que apunta hacía la derecha o hacia abajo. Esta es una opción normalmente habilitada... pero y si algún usuario 'sin quererlo' lo deshabilitara ¿sabríamos recuperar la funcionalidad?.
Antes de comenzar recordaremos algunas de las ventajas o posibilidades de esta opción.
Podemos seleccionar:
- celdas individuales
- un informe completo
- todos los elementos de un campo
- las etiquetas de elementos, datos o ambos
- todas las apariciones de un único elemento
- una aparición de un elemento o varios
- o seleccionar subtotales y totales.

Para encontrar esta opción y sus diferentes herramientas activaremos la barra de herramientas de tabla dinámica > pestaña Opciones > grupo Acciones > botón Seleccionar:

Si desplegamos el botón Seleccionar veremos algunas opciones para marcar las áreas de Etiquetas y valores, sólo Etiquetas o sólo Valores o bien Toda la tabla dinámica; en la parte de abajo, en un a sección independiente del desplegable del botón, tenemos activado/desactivada la opción buscada Habilitar selección:

Con la opción activa para seleccionar un único elemento o área, y no todos los elementos correspondientes haremos doble clic sobre dicho elemento, ya que con un solo clic marcaremos todas las apariciones de un único elemento.
También podremos emplear la combinación de teclas Ctrl ó Mayúscula para seleccionar elementos adicionales dentro del mismo campo. Para cancelar la selección de un elemento, presionaremos la tecla Ctrl y clic en el elemento.

Asi que ya sabes donde reactivar Habilitar selección en un a tabla dinámica si alguna vez pierdes esta opción:

Seguir leyendo...

VBA: Las dobles comillas en las cadenas de programación VBA para Excel.

Recientemente una lectora consultaba cómo podía solucionar un problema para incluir en una .Formula un entrecomillado. Esto me hizo recordar un par de formas habituales para combatir esta situación.
En particular la lectora preguntaba por la forma de escribir la siguiente instrucción para introducir una fórmula, mediante programación, en una celda, empleando la propiedad .Formula:
Sub formula()
Sheets("Datos").Select
Range("e2").formula = "=SI(A2="","",SI(B2="x",0,SI(C2="",0,Datos!$D$2)))"
End Sub
Escrito así el depurador se dentendría en las primeras comillas...
¿Cuál es entonces la manera correcta de saltar este inconveniente???.

La primera y más sencilla es emplear en método de doble comillado, cambiando las comillas simples por unas dobles. En el ejemplo se vé mejor:

Sub formula1()

Sheets("Datos").Range("E2").FormulaLocal = _
    "=SI(A2="""";"""";SI(B2=""x"";0;SI(C2="""";0;Datos!$D$2)))"

End Sub

pero este método podría generar algún fallo, por lo que la alternativa sería usar el caracter 34 (Chr(34) que representa las dobles comillas), pero con igual forma de trabajo:

Sub formula2()

Sheets("Datos").Range("E3").FormulaLocal = _
    "=SI(A2=" & Chr(34) & Chr(34) & ";" & Chr(34) & Chr(34) & ";SI(B2=" & Chr(34) & "x" & Chr(34) & ";0;SI(C2=" & Chr(34) & Chr(34) & ";0;Datos!$D$2)))"

End Sub

Una manera adicional, algo más cómoda que la anterior, sería construir una función personalizada que replique la contrucción de esa parte del doble comillado. La UDF sería:

Function comillas(Texto As String) As String
  comillas = Chr(34) & Texto & Chr(34)
End Function

y el código de la fórmula sería entonces:

Sub formula3()

Sheets("Datos").Range("E4").FormulaLocal = _
    "=SI(A2=" & comillas("") & ";" & comillas("") & ";SI(B2=" & comillas("x") & ";0;SI(C2=" & comillas("") & ";0;Datos!$D$2)))"
End Sub

La idea de las dobles comillas es ir acotando secciones de la cadena de texto que componemos con la fórmula y 'variables' que concatenamos con el resto

Nota: el caracter de comilla simple es Chr(39).

Seguir leyendo...