Summer Excel: Formación online Excel.

¿Aprovecharás este verano?, sin duda una buena base te permitirá avanzar en tu trabajo.
Aprende con los mejores: Edición de Cursos de Excel y Macros online con tutor personal de Agosto de 2014.

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

Curso Excel Avanzado

(ver más)

Curso Excel Nivel 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)

Curso Excel Financiero

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

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

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

Suma condicional tridimensional 3D sin macros.

Frecuentemente se me pregunta por la manera de realizar sumas condicionadas sobre diferentes hojas, y siempre les dirijo hacia esta entrada del blog:
VBA: Una función personalizada de fórmula 3D.
Y es que la teoría nos dice que la función SUMAR.SI o SUMAR.SI.CONJUNTO y sus hermanas para contar o promediar, no admiten el método 'tridimensional' (como se explica en el link anterior).

El trabajo a realizar en nuestra hoja de cálculo (sin macros) es, en esencia, similar al procedimiento detallado en el post de más arriba.
Lo que haremos será, mediante matriciales, recorrer y sumar de manera condicional, cada hoja que indiquemos.

Comencemos con el desarrollo viendo las hojas a sumar: tenemos en nuestro ejemplo tres hojas: DptoA, DptoB y DptoC con unos orígenes de estructura idéntica, tres columnas de datos: Mes, Comercial e Importe:

En la hoja Resumen llegaremos, con una sola fórmula a:

haz clic en la imagen

El primer paso fundamental es crear un Nombre definido con una Constante matricial que represente los nombres de las hojas que queremos sumar. En mi ejemplo:
Hojas ={"DptoA";"DptoB";"DptoC"}
puesto que son esas tres hojas sobre las que quiero trabajar.

El siguiente paso, y último, es generar nuestra fórmula.. si nos situamos en la celda C3 de la hoja 'Resumen' escribiremos:
=SUMA(SUMAR.SI.CONJUNTO(INDIRECTO(Hojas&"!C2:C13");INDIRECTO(Hojas&"!A2:A13");$B3;INDIRECTO(Hojas&"!B2:B13");C$2))
y ejecutaremos matricialmente!!! (presionando Ctrl+Mayusc+Enter en lugar de solo Enter).

Si nos fijamos tenemos una 'doble suma', ya que hemos anidado un SUMAR.SI.CONJUNTO en una función SUMA.
El truco consiste en aplicar un SUMAR.SI.CONJUNTO sobre cada hoja de las descritas en el nombre definido 'Hojas' de manera independiente, y luego al ser una fórmula matricial, el resultado de cada hoja se acumula con la función SUMA... es decir, es como si hiciéramos:
=SUMAR.SI.CONJUNTO(DptoA!$C$2:$C$13;DptoA!$A$2:$A$13;$B3;DptoA!$B$2:$B$13;C$2)+SUMAR.SI.CONJUNTO(DptoB!$C$2:$C$13;DptoB!$A$2:$A$13;$B3;DptoB!$B$2:$B$13;C$2)+SUMAR.SI.CONJUNTO(DptoC!$C$2:$C$13;DptoC!$A$2:$A$13;$B3;DptoC!$B$2:$B$13;C$2)


Interesante ver cómo hemos construido, con la función INDIRECTO, los rangos sobre los que trabajar, por ejemplo:
INDIRECTO(Hojas&"!C2:C13")
que devolvería convertido en referencia de Excel los rangos:
DptoA!C2:C13
DptoB!C2:C13
DptoC!C2:C13

Seguir leyendo...

Gráficos de columnas apiladas junto a columnas agrupadas.

Un lector planteaba una cuestión sobre gráficos algo diferente:

...cómo hacer un gráfico de columnas apiladas mezclado con uno de columnas agrupadas?; es decir, tener dos columnas que se repiten pero que a su vez están segmentadas por dentro?...

La idea, parece entonces, clara. Mostrar un Total y al lado su desagregado en las partes que lo compongan.
Partiremos entonces de unos datos totalizados, y un par de columnas auxiliares que descomponen en dos dichos totales:

El primer paso es construir un gráfico, para lo cual seleccionamos el rango de análisis A1:D5 y desde la ficha Insertar > grupo Gráficos > botón Gráficos de columnas > desplegable Columnas apiladas, obtenemos lo siguiente:

En el siguiente paso seleccionamos la serie de datos 'Total' y la llevamos al Eje Secundario (sólo la serie Total!!, lo que haremos desde la opción Dar formato a serie de datos...):

Ojo, aquí configuraremos las opciones de Superposición de series y Ancho del intervalo con los valores -100% y 0% respectivamente para la serie Total.
Para las otras series serán 100% y 200%.. así quedarán ajustadas perfectamente, no solapándose.

Muy importante el siguiente paso. Incorporamos una nueva Serie de datos VACÍA, sin información... por ejemplo marcando el rango sin valores E2:E5.
Este paso, aparentemente sencillo, es básico para visualizar como deseamos nuestro gráfico.
Podemos hacerlo de diferentes maneras, pero la más sencilla es activando el gráfico y arrastrando una columna a la derecha el rango del origen de datos de A1:D5 a A1:E5.
También desde Seleccionar datos... y Agregar una Nueva serie.
El resultado es el siguiente:

Seguir leyendo...

Cómo insertar una Imagen en el fondo de un Comentario de Excel.

Hace ya un par de años vimos cómo cambiar la caja de texto de un comentario en Excel (ver).
Hoy veremos cómo conseguir, sin macros, insertar una imagen como fondo de esta caja de texto.
En primer lugar insertaremos un comentario en una celda:
1- desde el menú contextual (botón derecho del ratón) > Insertar comentario, ó
2- desde la ficha Revisar > grupo Comentarios > botón Nuevo comentario.

A continuación seleccionaremos la caja del comentario (OJO!! no vale tener el cursor activo 'parpadeando' dentro de la caja!!!):

Seguidamente haremos clic derecho con el ratón y buscaremos la opción de Formato de Comentario.
En la ventana diálogo buscaremos la pestaña: Colores y líneas, y dentro de ella, la sección Relleno y el desplegable Color:

En el desplegable buscaremos 'Efectos de relleno'. Se abrirá una nueva ventana diálogo, en la cual seleccionaremos la pestaña 'Imagen', y en esta pestaña presionaremos el botón Seleccionar imagen, lo que abrirá un explorador donde poder buscar la imagen deseada:

Seguir leyendo...

Importar datos desde una Web.

Hoy veremos lo sencillo que puede ser trabajar con datos subidos en alguna web...
En concreto daremos respuesta a un usuario que hace unos días me planteaba como conseguir datos de cotización de algunas empresas:

...¿Sería posible importar a A1 la cotización en tiempo real, del valor BBVA, o cualquier otro, del Mercado continuo Español?. O de cualquier otro...

Lo primero es conocer cuál será la web elegida, concretamente la URL completa, donde se encuentre la información deseada. En mi caso, para mostrar el ejemplo he optado por la web oficial de la Bolsa de Madrid, y específicamente la URL donde se muestran las cotizaciones de las empresas del IBEX 35:
http://www.bolsamadrid.es/esp/aspx/Mercados/Precios.aspx?indice=ESI100000000&punto=indice
Por supuesto valdría cualquier otra.. hay miles, incluso en tiempo real (sin demora de 5-10 minutos).

Ahora llega el proceso de importación desde laWeb. Iremos a la ficha Datos > grupo Datos externos > botón Desde Web:

Se abrirá una ventana diálogo donde indicaremos nuestra URL destino, cuadro de Dirección::

haz clic en la imagen

En esta ventana buscaremos los botones amarillos con flechas... es algo automático que muestra este asistente y que identifica las tablas dentro de esa página web...
En nuestro ejemplo al no identificar ninguna nos ofrece señalar TODA la página (opción por defecto en todo caso). El botón se coloreará en verde, y cambiará la flecha por un tick... el paso siguiente será presionar el botón Importar:

En el siguiente paso se realizará la conexión (esto tardará más o menos dependiendo de nuestro ancho de banda)... una vez finalizada la conexión aparecerá un cuadro diálogo preguntando en qué lugar de nuestro Libro deseamos importar los datos:

Seguir leyendo...

Mejorando el rendimiento de nuestra hoja de cálculo.

Hoy hablaremos de cómo mejorar (o intentarlo al menos) el rendimiento de nuestras hojas de Excel... y es que en ocasiones comprobamos cómo nuestros equipos se colapsan, perdiendo información y mucho tiempo.
Lo primero que debemos tener presente y saber es la diferencia entre los métodos de cálculo disponibles, en especial, entre Automático y Manual, configurable desde la ficha Fórmulas > grupo Cálculo > botón Opciones para el cálculo:

Optando por la opción 'Manual' conseguimos que Excel vuelva a calcular todos los libros abiertos sólo cuando se solicita al presionar F9 ó Ctrl + Alt + F9 o bien al guardar un libro.
Esta posibilidad es aconsejable cuando trabajando con aquellos de nuestros libros de trabajo que 'se toman su tiempo' para volver a calcularse, evitando así un retraso y tiempos de espera innecesarios

Si optásemos por el 'Cálculo automático', que tenemos por defecto!!, Excel calcularía automáticamente todos los libros abiertos en cualquier cambio en nuestras hojas, y por supuesto al abrir el libro.

Una cuestión muy importante que no podemos olvidar es el equipo de trabajo.. una limitación importante son las características técnicas de nuestro PC, en cuanto a velocidad del procesador, número de ellos y memoria RAM son las principales, aunque versión del Sistema Operativo, del paquete Office y si es de 32 ó 64 bits.
Algo no muy conocido es que desde la versión Excel 2007 podemos configurar en Excel el número de procesadores a emplear, bastará entrar en la ficha Archivo > Opciones de Excel > Avanzadas > sección Fórmulas:

Por supuesto, cuanto más tengamos de todo, mejor y de manera más óptima recalcularemos nuestros Libros.

Salvados los primeros problemas de configuración (optamos por la forma de cálculo Manual) y conociendo las limitaciones de nuestros PC, pasamos a abordar pequeñas mejoras que agilizan los cálculos:

1- Ejercicio de síntesis: evitaremos, siempre sea posible, duplicar cálculos, referencias. Esto requiere un ejercicio mental previo.
2- Uso de las funciones/fórmulas adecuadas: Sabiendo que las fórmulas matriciales son extremadamente pesadas y lentas; algo menos las funciones personalizadas en VBA (nuestras UDF); las siguientes en el escalafón son las funciones tipo SUMAR.SI o SUMAR.SI.CONJUNTO y sus hermanas CONTAR.SI, PROMEDIO.SI, etc... Así procuraremos evitar las matriciales (cuando sea posible) y reemplazarlas por funciones personalizadas, que aunque también más lentas que las estándar, bien construidas, favorecen los procesos y la rapidez de cálculo; una alternativa más podrían ser las funciones de Base de datos: funciones BD (ejemplo).
Evitar trabajar con matriciales, apoyándonos en columnas/filas auxiliares.
En lugar de matriciales, emplear la función SUMAPRODUCTO que trabaja algo más rápido.
3- Evitar funciones 'volátiles': las funciones volátiles serían aquellas que siempre se recalculan, tras cada nueva actualización o cambio en la hoja. Abusar de estas funciones ralentiza cada nuevo cálculo.
Algunas de las funciones estándar de Excel evidentemente volátiles: ALEATORIO(), AHORA(), HOY(), o también DESREF(), CELDA(), INDIRECTO() ó INFO() entre otras.
4- Uso adecuado de las funciones de búsqueda (COINCIDIR, BUSCARV, BUSCAR): Las funciones de búsqueda, con frecuencia, ralentizan en exceso el cálculo significativamente.
Emplear el argumento de Coincidencia exacta, acelera el tiempo de cálculo de la función, siendo proporcional al número de celdas escaneadas antes de encontrar la coincidencia.
Es preferible (y recomendable) emplear la combinación INDICE y COINCIDIR en vez de BUSCAR o BUSCARV !!
En particular para estas funciones de búsqueda es más óptimo una ordenación previa de los rangos de búsqueda
5- Minimizar el tamaño de los rangos de trabajo que emplearemos en nuestros fórmulas o funciones: A mayor tamaño del rango más lentitud añadida.
6- Evitar los vínculos externos: Es bien sabido que un Libro de trabajo con vínculos a un segundo libro hace lento los cálculos y actualizaciones.
El uso de menos libros de trabajo más grandes es, generalmente, pero no siempre, mejor que el uso de muchos libros de trabajo más pequeños.
7-Tratar de no trabajar con un gran número de hojas vinculadas dentro de un mismo Libro: y es que aunque emplear muchas hojas en nuestros libros hacen que sean más fáciles de usar, por lo general harán más lento el calculo. Esto es, las referencias dentro de un mismo libro lastran el proceso de cálculo.
8- Minimizar Rango usado: Para ahorrar memoria y reducir el tamaño del archivo, Excel intenta almacenar información sobre la zona usada en cada hoja de cálculo que se utilizó. Esto se llama Rango usado.
A veces, acciones de edición y formato amplían el rango usado de manera significativa, lo que podría causar problemas de rendimiento y en el tamaño del archivo.
9- Siendo muy útiles, los Formatos condicionales, alargan los tiempos de cálculo.
10- De igual modo ocurre con la Validación de datos.
11- Uso de los Nombres definidos: Sin duda una de las características más eficaces en Excel, pero requiere de tiempo adicional de cálculo.
El empleo de nombres con referencia a otras hojas de cálculo, agrega un nivel adicional de complejidad para el proceso de cálculo. Además, debemos intentar evitar los nombres que hacen referencia a otros nombres (nombres anidados).
12- Macros: Ciertamente incluir programación en nuestros Libros aporta un retraso en el proceso de cálculo... que puede ser elevado en determinadas ocasiones.

En conclusión, todo aquello que aporta potencia y funcionalidad a nuestras hojas de cálculo requiere de un consumo de memoria que hace retrasar nuestros cálculos... por suerte, los equipos de trabajo con los que se disponen hoy en día, son lo suficientemente potentes para salvar la mayoría de las situaciones, solo saltando en momentos muy concretos con un GRAN volumen de información.

Seguir leyendo...

Encontrando vínculos externos en nuestro Libro de Excel.

Recientemente un amigo me consultaba sobre la forma de encontrar un vínculo 'perdido'... y no es baladí el asunto, ya que trabajar entre libros de Excel, puede llegar a generar problemas no esperados.
De ahí la entrada de hoy. En este post aprenderemos a localizar todo tipo de vínculos a otros libros, para cuando sea oportuno eliminarlos.
De todos es conocido el Editor de vínculos (ver), el cual identifica los vínculos existentes en cualquier parte de nuestro libro de trabajo.

El más sencillo de localizar es sin duda el vínculo creado en una celda, mediante algún tipo de referencia o fórmula:

Estos son las más sencillo de localizar, corregir o eliminar. Bastaría emplear las Opciones del Editor de vínculos; aunque también podemos usar alguna alternativa, como por ejemplo, en el cuadro de búsqueda (Ctrl+B) insertaremos el caracter [ (corchete), y luego en el botón Buscar todos desplegaremos todas las celdas con algún tipo de vínculo.

Se trata de aprovechar la forma de Excel a la hora de escribir los vínculos: [Libro.xlsx]Hoja!celda
en nuestro ejemplo:
=[cuotas.xlsx]Hoja1!$E$7

Otro lugar donde buscar vínculos externos es en los Nombres Definidos. En la imagen siguiente vemos un vínculo empleado a través de un nombre definido, en la celda D5:
=CONTARA(NombreExterno)

Logicamente en este caso ya no funcionaria el truco de Buscar corchete... pero si podremos seguir empleando el Editor de vínculos.

Seguir leyendo...

Escribiendo con superíndices (al cuadrado o al cubo).

Hace algunos años escribí un post donde trataba el tema de los superíndices, enfocado en particular para el formato personalizado (ver).

En la entrada de hoy retomaré el tema, pero haciéndolo extensivo a cualquier parte de la hoja...
Se trata en definitiva de poder escribir fácilmente ese cuadrado que, por ejemplo,aparece en los m².

Un truco muy directo es presionar:
ALT+252 para el ³
y
ALT+253 para el ².

Sin embargo, podemos gestionar más directamente esta forma de presentar los metros cuadrados o metros cúbicos, sin necesidad de recordar las 'dichosas' combinaciones de teclas.
Nuestra labor será sencilla, nos iremos a las Opciones de autocorrección, para lo cual accederemos al menú Archivo > Opciones > menú Revisión > sección Opciones de Autocorrrección:

Tras presionar dicho botón se abrirá una nueva venta diálogo, donde añadiremos en la sección Reemplazar: --- Con:
el literal
m2 Con: m²
y
m Con: m³
y cualquier otra combinación que necesitemos y usemos habitualmente...

Una vez aceptado, la próxima vez que escribamos en alguna de nuestras celdas el texto m2 o m3 se autocorregirá y cambiará por m² ym³.

Seguir leyendo...

VBA: Cómo convertir nuestras fórmulas en valores.

Hoy veremos un sencillo procedimiento para convertir cualquier fórmula (dentro de una rango concreto) en valores...
Lo primero será tener claro qué fórmulas localizadas en qué celdas son las que nos interesa convertir en valores., y para nuestro ejemplo serán las que se encuentran en las celdas C2,D5 y E4:

Con la propiedad .HasFormula detectaremos si nuestras celdas contienen algún tipo de fórmula...
En un módulo del proyecto de VBA para nuestro Libro de trabajo, dentro del Editor de VB:

Sub ConvierteFormulaenValor()
'recorremos las celdas de nuestro rango elegido
For Each rngcell In Range("C2,D5,E4")
    'añadimos el control que detecta si la celda tiene Fórmula
    If rngcell.HasFormula Then
        'cuando la tenga asignamos a la celda el valor que tenga....
        rngcell.Value = rngcell.Value
    End If
Next rngcell
End Sub

Tras ejecutar nuestra macro obtenemos el siguiente resultado...lógicamente sólo se habrá producido el cambio en las celdas indicadas!!

Seguir leyendo...