Últimos cursos Excel y macros online con tutor del año!!!

Acabamos este año 2013, con tantas y tantas novedades personales y profesionales, y muchas ellas relacionadas con nuestra hoja de cálculo favorita: Microsoft Office Excel; sin duda una de las mejores herramientas que tenemos a nuestra disposición.
Por esto, cuanto mejor conozcamos las posibilidades de Excel, más y mejor rendimiento seremos capaces de extraer, y esto sólo se logra con formación estructurada y apoyado en todo momento por un tutor personal, con conocimientos claramente probados.. y que además ya conoces ;-)
Si necesitas aprender con los mejores te interesa la edición de Cursos de Excel y Macros online con tutor personal de Diciembre de 2013.

Los cursos de Excel y Macros abiertos para este mes de diciembre son:

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Excel Financiero

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de diciembre de 2013; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

Reestablecer Indicador de errores cometidos.

Hace un año expliqué cómo configurar el color del indicador que señalaba una celda con algún tipo de error (ver).
En el día de hoy aprenderemos la forma en que podemos reestablecer estos indicadores si por algún motivo los hubieramos omitido.
Veamos una serie de errores marcados en algunas celdas (fijémosnos en la esquina superior izquierda de las celdas):

Es sabido que seleccionado el desplegable automático de estas celdas podemos omitir la aparición del indicador:

Obviamente tras presionar esa opción para cada tipo de error, las marcas desaparecerán...

Seguir leyendo...

Versiones de Excel.

Hace algunas semanas tuve que recordar un poco de historia de Excel, ya que tenía que controlar algunas rutinas en función a la versión de Excel con la que trabajaban diferentes usuarios. Asi que decidí explicar algunas formas de obtener el número de versión de Excel (y alguna otra información) y mostrar un ejemplo sencillo de como dirigir y controlar un proceso en VBA según la versión de trabajo.
Me centraré en las versiones para Windows, ya que las versiones MAC van por otro lado...

En primer lugar veremos la forma manual de conocer la versión de tu Excel.
Para ello accederemos, en Excel 2007, al botón de Office > Opciones de Excel > Recursos > Acerca de..., para más detalle pulsaremos el botón de Acerca de:
>p align="center">

Si trabajas en Excel 2010 busca esta información en el menú Archivo > Ayuda > Acerca de:

Recordaré ahora el historial de versiones de Excel para Windows:
Versión 2.0 = Excel 2 (1987)
Versión 3.0 = Excel 3 (1990)
Versión 4.0 = Excel 4 (1992)
Versión 5.0 = Excel 5 (1993)
Versión 7.0 = Excel 95
Versión 8.0 = Excel 97
Versión 9.0 = Excel 2000 (Office XP)
Versión 10.0 = Excel 2002
Versión 11.0 = Excel 2003
Versión 12.0 = Excel 2007
Versión 14.0 = Excel 2010
Versión 15.0 = Excel 2013

Para conocer nuestra versión y otros detalles, incluso del sistema, podemos aplicar una sencilla macro:

Sub Conocer_Version()
Dim Vers As String
'controlamos qué versión es la de nuestra aplicación Excel abierta..
Select Case Application.version
    Case "2.0": Vers = "Excel 2 (1987)"
    Case "3.0": Vers = "Excel 3 (1990)"
    Case "4.0": Vers = "Excel 4 (1992)"
    Case "5.0": Vers = "Excel 5 (1993)"
    Case "7.0": Vers = "Excel 95"
    Case "8.0": Vers = "Excel 97"
    Case "9.0": Vers = "Excel 2000 (Office XP)"
    Case "10.0": Vers = "Excel 2002"
    Case "11.0": Vers = "Excel 2003"
    Case "12.0": Vers = "Excel 2007"
    Case "14.0": Vers = "Excel 2010"
    Case "15.0": Vers = "Excel 2013"
    Case Else: Vers = "Otras versiones de Excel...."
End Select
'mostramos información
With Application
    MsgBox Vers & vbCr & "Versión:= " & .version & " Número de versión:= " & .Build & vbCr & _
                "Nombre y número de versión del sistema operativo actual:= " & .OperatingSystem
End With

End Sub

En mi caso este es el mensaje mostrado:

Para emplear y poder dirigir procedimientos en nuestras macros según la versión de trabajo del usuario podríamos emplear esa propiedad .version, pero como el valor devuelto es una cadena de texto, no olvidemos anidarlo dentro de una función VAL de VBA:

Sub SegunVersion()
'en este ejemplo controlo si la versión es anterior a Excel 2007
If Val(Application.version) < 12 Then
    MsgBox ".xls"
    Else
    MsgBox ".xlsx"
End If

End Sub

Seguir leyendo...

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...