Nuevo Curso preparación MOS Excel 2010.

Comienza una nueva edición de Cursos de Excel y Macros en modalidad elearning (online).
En este mes que comienza, el 1 de octubre, además de los cursos de Excel avanzado y macros (nivel básico y medio), ofrezco un nuevo Curso que te prepara para conseguir el certificado Microsoft Office Specialist Excel 2010, esto es, te prepara para que te puedas presentar con éxito al examen 77-882 MOS Excel 2010 (ver).

Los cursos disponibles en Excelforo son:

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Curso Preparación MOS Excel 2010 (Nuevo!!!)

(ver más)

Esta nueva edición de Cursos de Excel por Excelforo dará comienzo el próximo día 1 de octubre de 2012, cada curso con una duración determinada..
Con la confianza de siempre....Anímate!!, y no lo dejes pasar.

El programa del nuevo curso de Preparación al MOS Excel 2010 es el siguiente, pero puedes informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com:
TEMA 1 - INTRODUCCIÓN AL MOS EXCEL 2010
1. Introducción.
2. Para qué me prepara este curso?.
3. Contenido del curso.
3.1. Administrar el entorno de la hoja de cálculo.
3.2. Creación de datos en las celdas.
3.3. Formato de las celdas y hojas de cálculo.
3.4. Gestión de hojas de cálculo y libros.
3.5. Aplicación de fórmulas y funciones.
3.6. Presentación de datos.
3.7. Compartir datos de la hoja con otros usuarios.
3.8. Análisis y organización de datos.
TEMA 2 - ADMINISTRAR EL ENTORNO DE LA HOJA DE CÁLCULO
1. Introducción.
2. Navegar por una hoja de cálculo.
3. Imprimir hoja de cálculo o un libro.
3.1. Impresión de parte o la totalidad de un libro.
3.2. Configurar saltos de página.
3.3. Imprimir líneas de cuadrícula y encabezados.
3.4. Imprimir encabezados y pies de página.
4. Personalizar el entorno de trabajo.
4.1. Personalizar la cinta de opciones y la barra de herramientas de acceso rápido.
4.1.1. Personalizar la cinta de opciones (Ribbon).
4.1.2. Personalizar la barra de herramientas de acceso rápido.
4.2. Configurando los libros trabajo.
4.2.1. Trabajando con las propiedades de un libro.
4.2.2. Trabajando con versiones de libros.
TEMA 3 - CREACIÓN DE DATOS EN LAS CELDAS
1. Introducción.
2. Insertar datos en las celdas.
2.1. Pegar estructuras de datos.
2.2. Insertar y borrar datos.
3. Aplicar rellenar.
3.1. Completar una serie de datos.
3.2. Copiar datos.
4. Aplicar y modificar hipervínculos.
4.1. Crear hipervínculos.
4.2. Modificar hipervínculos.
TEMA 4 - FORMATO DE LAS CELDAS Y HOJAS DE CÁLCULO
1. Introducción.
2. Aplicar y modificar formatos de celda.
2.1. Formatear el contenido de una celda.
2.2. Formatear el relleno/trama y los bordes de una celda.
2.3. Formatear la altura y ancho de una celda.
2.4. Formatear números.
3. Combinar o dividir celdas.
4. Crear títulos de fila y columna.
5. Ocultar y mostrar filas y columnas.
6. Manipular las opciones de configuración de página para hojas de cálculo.
7. Crear y aplicar estilos de celda.
TEMA 5 - GESTIÓN DE HOJAS DE CÁLCULO Y LIBROS
1. Introducción.
2. Crear y formatear hojas.
3. Manipular vistas de las hojas.
3.1. Inmovilizar y dividir ventanas de trabajo (hojas de cálculo).
3.2. Organizar las ventanas de trabajo.
4. Manipular vistas de los libros.
4.1. Personalizar las ventanas de trabajo.
4.2. Personalizar la vista de la hoja.
4.3. Cambiar el zoom.
TEMA 6 - FÓRMULAS Y FUNCIONES
1. Introducción.
2. Crear formulas.
3. Prioridades en las operaciones.
4. Referencias absolutas y relativas.
5. Lógica condicional en las fórmulas.
5.1. Fórmula condicional.
5.2. Anidar funciones.
6. Rangos con nombre en las formulas.
7. Rangos de celdas en las formulas.
TEMA 7 - PRESENTACION DE DATOS
1. Introducción.
2. Crear gráficos.
2.1. Generar un gráfico.
2.2. Diseños y estilos de gráficos.
2.3. Mover y dimensionar gráficos.
2.4. Editar datos de un gráfico.
2.5. Elementos de un gráfico.
3. Aplicar y modificar ilustraciones.
3.1. Insertar imágenes prediseñadas (clip art).
3.2. Insertar un diagrama SmartAart.
3.3. Insertar formas o autoformas.
3.4. La captura de pantalla.
4. Trabajar con imágenes.
5. Crear minigráficos (Sparklines).
TEMA 8 - COMPARTIR DATOS DE LA HOJA CON OTROS USUARIOS
1. Introducción.
2. Compartir hojas de cálculo.
2.1. Guardar libros en formatos específicos.
2.2. Enviar libros de trabajo.
3. Administrar comentarios.
TEMA 9 - ANÁLISIS Y ORGANIZACIÓN DE DATOS
1. Introducción.
2. Filtro de datos.
3. Ordenación de datos.
4. Formato condicional.

Seguir leyendo...

Aleatorios sin repetición con macros en Excel.

En la anterior entrada del blog explicaba como conseguir cierta cantidad de números aleatorios sin repetición para un intervalo de valores dado.
En el ejemplo que plantearé obtendremos 20 números aleatorios sin repetición de un rango de valores entre 1 y 100.
En esta ocasión aplicaremos una macro que nos lleva a ese mismo resultado, aleatorios sin repetición; el mecanismo que emplearemos será el de crear una Collection, generando números aleatorios hasta alcanzar (en el ejemplo que describo) 20 elementos de la colección, es decir, 20 números aleatorios no repetidos.
Otra de las instrucciones de VBA que usaré es el DO UNTIL...LOOP, que permite incrementar el tamaño de nuestra Collection hasta 20.

El código que insertaremos en un Módulo del Explorador de proyectos del Editor de VBA, formando nuestra macro de Excel:

Sub AleatoriosNoRepetidos()
Dim i As Integer

'generamos la coleccion
Set unicos = New Collection

x = 1 'núm inicial del rango de valores
y = 100 'núm final del rango de valores
z = 20 'numero de elementos no repetidos que queremos

'Inicializamos el generador de números aleatorios
Randomize

'loop hasta conseguir Z elementos de la Collection (20 elementos)
Do Until unicos.Count = z
    'generamos nuevos aleatorios entre x e y (entre 1 y 100)
    ale = Int((y - x + 1) * Rnd + x)
    'cuando encuentre un item repetido, daría un error
    'que salvamos con la instrucción On Error Resume Next
    On Error Resume Next
    'por tanto, nuestra coleccion solo agrega elementos no repetidos
    'objeto.Add item, key, before, after
    'ocurre un error si una key especificada duplica la key de un miembro existente de la colección
    unicos.Add ale, CStr(ale)
    On Error GoTo 0
Loop

'escribir los datos unicos en la Hoja de cálculo
For i = 1 To unicos.Count
    Sheets("Macro").Range("B2").Offset(i - 1, 0).Value = unicos(i)
Next i

End Sub

Si asginamos la macro a un botón en nuestra Hoja de Excel, y ejecutamos la macro, obtendremos en el rango B2:B21:

He añadido en C2:C21 un CONTAR.SI para comprobar que efectivamente no hay valores repetidos.
Podemos comprobar como hemos conseguido 20 números aleatorios entre 1 y 100 sin repetición.

Seguir leyendo...

Aleatorios sin repetición en Excel, sin macros.

Dos días atrás me llegó una consulta sobre la manera de conseguir ciertos números de manera aleatoria entre valores de un rango, pero sin repetir. Como es una pregunta que se me hace con cierta frecuencia, por fin me he decidido a publicar algunas soluciones al tema.
En esta primera ocasión veremos cómo hacerlo sin emplear macros.

...Quisiera hacerte esta consulta: deseo aplicar la función Aleatorio (no dispongo de la versión de excel Aleatorio.entre) por ejemplo para que me dé números enteros del 1 al 100. Sería: =REDONDEAR.MAS(ALEATORIO()*100;) Mi intención es dar con 30 números, por lo que aplico la fórmula en 30 celdas (salen repes)... Empero: tendrían que ser 30 números diferentes. Ese es mi dilema...

Para dar solución a esta cuestión deberemos recordar un post publicado en este blog hace tres años (ver), es esa entrada se explicaba el uso de la función JERARQUIA, combinada a la función CONTAR.SI para conseguir una ordenación de ciertos valores; una ordenación única, esto es, sin repeticiones de posición. Esta es la esencia de la explicación siguiente.

Lo primero que tenemos que hacer es dotar de 'aleatoriedad' a nuestro modelo, por lo que comenzamos formulando en el rango A2:A21 veinte fórmulas =ALEATORIO(), que recuerdo que devuelve un número entre 0 y 1 (para el ejemplo que plantea arrastraríamos desde A2 hasta A101):

Las posibilidades de obtener dos valores iguales en el rango deben ser bastante próximas a cero, pero lo tendremos en cuenta para nuestra formulación...
El siguiente paso es fórmular, sobre el rango anterior A2:A21, la fórmula que devolverá números aleatorios sin repetir entre 1 y 20 (para mi ejemplo), a partir de la celda C2:
=JERARQUIA(A2;$A$2:$A$21)+CONTAR.SI($A$2:A2;A2)-1
fórmula idéntica a la vista en ver.
Esta fórmula JERARQUIA junto al CONTAR.SI nos asegura que los valores devueltos nunca serán iguales, es decir, que el orden dado no repetiría posiciones.

Seguir leyendo...

Posibilidades del gráfico dinámico en Excel.

Hoy trataré la manera de trabajar con datos para construir un gráfico dinámico en nuestra hoja de Excel, gráfico que nos permitirá mostrar la información por elementos y por series sin necesidad de macros.
Se trata también en esta entrada de reconocer cuál es la mejor manera de distribuir los datos en nuestras tablas origen, ya que en ocasiones la manera en que tenemos nuestros datos, en forma de tablas, no es la mejor para posteriores trabajos. Veámoslo con un ejemplo.
Tenemos un distribución de Ventas por meses y años; a modo de ejemplo he construido un gráfico de columnas que representan esas Series de datos por Años para cada elemento (mes):

En principio podría ser un gráfico que represente adecuadamente la información de la tabla origen de datos (tabla de referencia cruzada por año y Meses); sin embargo, si quisieramos interactuar con el gráfico para filtrar la información por Series(Años) y Elementos(Meses) tendríamos que habilitar algunos de los trucos de gráficos conocidos o bien contruir una macro que actúe sobre el gráfico (ver)
Y esa es la cuestión importante en esta entrada. ¿Cómo conseguir mostrar en un gráfico algunas de las Series de datos (Años) para algunos de los valores del Eje horizontal (Meses)?.

El asunto es muy sencillo, sólo debemos reestructurar el origen de datos, para ello generaremos una Tabla de tres columnas, una para los Meses, otra para los Años y otra para las Ventas; podemos recomponer el origen copiando y pegando los datos. Debe quedar de esta manera:

El siguiente paso es fácil, sólo hay que crear un Informe de gráfico dinámico, con el rango de datos seleccionado, navegamos hasta la ficha Insertar > grupo Tablas > botón desplegable Tablas dinámicas > Gráfico dinámico:

Seguir leyendo...

VBA: El evento WorkBook open en macros de Excel.

Hace unos días un usuario me consultaba sobre la forma de construir una macro que marcara el color de la etiqueta de la hoja cuyo nombre coincidiera con el año corriente.

...Tengo un libro con varias hojas llamadas 2011-2012-2013-2014-2015 y quiero que la pestaña de la hoja del año en curso se remarque con un color,por ejemplo Rojo, pero de forma automatica, como si le asignaramos un formato condicional., para indicar al usuario de forma visual la hoja a utilizar....

Suponiendo un libro de trabajo, con seis hojas llamadas '2010', '2011', '2012', '2013', '2014' y '2015':

Para ejecutar la acción solicitada insertaremos un evento WorkBook_Open() en la Hoja de trabajo (ThisWorkBook), que recorra los nombres de todas las hojas existentes en el libro, para que cuando encuentre la coincidencia Nombre Hoja con Año presente, le de un color rojo y la deje activa.

El código que insertaremos en ThisWorkBook del Explorador de proyectos del Editor de VBA, formando nuestra macro de Excel, con un evento Open, quedando:

Private Sub Workbook_Open()
'definimos las variables
'para comparar con el nombre de la etiqueta de cada hoja
'definimos el año como cadena de texto - String
Dim numhojas As Long, anyo As String
numhojas = Sheets.Count
anyo = Year(Now())

'recorremos todas las hojas buscando el nombre de la etiqueta
'que coincide con el año presente
For i = 1 To numhojas
    Set etiqueta = ActiveWorkbook.Sheets(i).Tab
    'cuando coincida le damos un color rojo y
    'activamos la hoja
    If Sheets(i).Name = anyo Then
    etiqueta.Color = vbRed
    Sheets(i).Activate
    Else
    etiqueta.Color = xlNone
    End If
Next i
End Sub

Queda una macro sencilla que cada vez que abramos nuestro libro de trabajo la rutina revisará los nombres de la hoja, y marcarña de color rojo aquella que coincida con el año actual.

Seguir leyendo...

La herramienta de Excel Ir a especial... Diferencias entre filas.

Hablaré hoy de una herramienta poco empleada en general Ir a especial..., y particularmente la facilidad que nos ofrece esta herramienta para seleccionar celdas que difieran, que sean distintas, por filas respecto a un patrón, es decir, que marque las Diferencias entre filas.
Veamos la sencillez del tema. Tenemos tres columnas de datos que debemos comparar fila a fila, para localizar aquellas que difieran del modelo inicial.

Observamos como efectivamente, al comparar los registros de las columnas B:C con la primera columna A, fila a fila, existen diferencias. Nuestro objetivo es seleccionar precisamente las celdas diferentes.
Para ello, con el rango A1:C6 seleccionado, desde la ficha Inicio > grupo Modificar > botón Buscar o Seleccionar, y del desplegable elegir la opción Ir a Especial..., o más sencillo presionar la tecla de función F5 y después el botón Especial, accedemos al cuadro diálogo Ir a especial:

Seguir leyendo...

Ejemplo de Gráfico Radial en Excel.

Siguiendo con el ciclo de entradas de gráficos en Excel. explicaré y mostraré un ejemplo del Gráfico Radial.
Este tipo de gráfico tan especial suele ser empleado para ver de manera continua el comportamiento de una serie de datos durante el ciclo completo de un día (por ejemplo).

Para desarrollar esta gráfica partiremos de una lectura histórica de datos que resumen durante un año el número de visitantes a una web o blog. Empleando el gráfico radial llegaremos a:

La cosntrucción es bien sencilla, y únicamente existen diferencias con otros tipos de gráficos, a la hora de formatear las diferentes áreas que componen el gráfico.

Empezamos creando el Gráfico Radial, para ello, con el rango A2:B26 seleccionado, nos iremos a la Ficha Insertar > grupo Gráficos > botón Otros > Radial > Radial con marcadores. Ampliamos el tamaño del gráfico hasta un alto de 13,14 cm y un ancho de 21,22 cm. Además eliminamos la Leyenda (para conseguir más espacio); desplazamos el Titulo hacia arriba y la izquierda, cambiando el texto por 'Visitas por hora'; y ampliamos el área del gráfico. Con lo que tendríamos el siguiente gráfico:

Seguir leyendo...

Gráficos de Cotización en Excel.

Hace algunos días subí una explicación de cómo poder representar desviaciones por cada punto de una serie de datos, cuando estas desviaciones no eran generales, si no particulares para cada punto, y por tanto no podíamos emplear en los gráficos de Excel una barras de error con desviación estándar. Podemos ver esta entrada anterior en Desviaciones por puntos.
En la entrada de hoy veremos como aplicando un tipo de gráfico muy poco habitual en Excel (gráficos de Cotización) podremos representar de manera muy sencilla estas desviaciones.
Desde luego, debe quedar claro que este tipo de gráfico está pensado precisamente para mostrar las fluctuaciones 'diarias' de valores/acciones en mercados secundarios de cotización.

La clave para representar graficamente estos gráficos es la posición de las columnas de datos (de nuestras series de datos) en la hoja de cálculo, ya que este gráfico se debe componer de tres series de datos (uno para los valores mínimos, otro para los máximos y un último para los valores de cierre).
Trabajaremos sobre un ejemplo diferente al de la entrada comentada anterior, pero igualmente sencillo. Tenemos valores para varios días, y para cada día tenemos un valor final (será nuestro valor de cierre) y un valor por encima y otro por debajo representando las desviaciones por punto (los valores de cotización mínimo y máximo por día aplicados a este tipo de gráfico). Fundamental es colocar la Serie con datos de cierre en la última columna.

haz clic en la imagen

Para construir el gráfico seleccionamos el rango A1:D7 y desde la Ficha Insertar > Gráficos > Otros > Cotizaciones y de la lista seleccionamos Máximos, Mínimos y Cierre:

Vemos como el orden recomendado es primera serie los 'Máximos', segunda serie 'Mínimos' y última los 'Cierres', realmente lo único importante es que la última serie sea la de 'Cierres'.

Con el gráfico ya creado elegiremos

• desde la barra de Herramientas de gráficos > Diseño > grupo Diseños de gráfico el Diseño 2,
• también agregaremos Líneas horizontales de la cuadrícula primarias desde la barra de Herramientas de gráficos > Presentación > grupo Ejes > botón Líneas de cuadrícula,
• y por último, para resaltar un poco más el Valor de cierre, desde barra de Herramientas de gráficos > Diseño > grupo Estilo de diseño > Estilo 18.

Resultando nuestro gráfico como sigue:

Seguir leyendo...

Gráficos de Anillos en Excel.

Repasaremos hoy un gráfico de Excel bastante útil, el Gráfico de anillo. Este gráfico puede representar datos organizados únicamente en columnas o en filas de una hoja de cálculo; y al igual que un gráfico circular, un gráfico de anillos mostrará la relación de las partes con un todo, aunque puede contener más de una serie de datos (los gráficos circulares sólo tienen una serie de datos.).
Para repasar las opciones que este tipo de gráfico nos ofrece, trabajaremos sobre un origen de datos con dos series de datos hasta conseguir el siguiente gráfico:

haz clic en la imágen

El primer paso es seleccionar el rango A1:C6 y desde la Ficha Insertar > grupo Gráficos > botón Otros > Anillo:

Con el gráfico creado le damos algo de formato:

• cambiando desde las Herramientas de gráficos > Diseño > Diseño de gráficos al Diseño 3,
• al Estilo 26 desde Herramientas de gráficos > Diseño > Estilos de diseño,
• y también desde Herramientas de gráficos > Formato > Estilos de forma seleccionamos el Efecto intenso-Anaranjado, Énfasis 6

quedando de la siguiente manera:

Seguir leyendo...