Iconos de Formato Condicional para aumento o disminución.

Veremos en el día de hoy alternativas para representar mediante iconos el aumento/disminución de un valor frente a otro. En particular cómo conseguirlo con los Conjuntos de iconos de tres flechas (de color).
Esta explicación trata de dar respuesta a la cuestión planteada por un lector:

De las dos columnas necesito poner las flechas en la columna del 2013, según si aumenta ( flecha hacia arriba ) o disminuye ( flecha hacia abajo ) respecto a la columna del 2012. He hecho algo pero no acaba de funcionar correctamente…

Veamos en la siguiente imagen el origen de datos y el resultado que perseguimos:

La idea es clara, queremos mostrar una flecha hacía abajo (roja) cuando el dato del 2013 es menor que el de 2012; una verde hacía arriba cuando 2013 es mayor que 2012 y amarilla cuando coincidan.

Comenzaremos mostrando la solución más sencilla, que será sacando fuera del origen de datos, en el ejemplo rango D2:D12, una fórmula para que se refleje en sobre ella ese aumento o disminución.
En este rango D2:D12 añadiremos la función:
=SIGNO(B2-C2)
lo cual devolverá un conjunto de +1, 0 ó -1 según el valor de 2012 sea, respectivamente, mayor, igual o menor que 2013.
El siguiente paso sería configurar un formato condicional con conjunto de iconos de tres flechas (de color) de la siguiente manera:

haz clic en la imagen

Es un sencillo Formato Condicional estándar, sin más... excepto por el detalle de haber seleccionado la opción de Mostrar Icono únicamente...lo que hace desaparecer los valores 1+,0 ó -1.
Sin duda este será el método más sencillo.

La siguiente alternativa, igual que la anterior requiere anexar en una columna auxiliar una fórmula, en el ejemplo en el rango E2:E12 (o también en F2:F12 de igual manera):
=SI(B2 > C2;CARACTER (234);SI(B2 < C2;CARACTER(233);CARACTER(232))) donde configuramos y añadimos el carácter que representa Flecha arriba (carácter 234), Flecha abajo (carácter 233) y flecha horizontal (carácter 232). A continuación cambiaremos el tipo de fuente, del rango E2:E12 (o también en F2:F12) a: Wingdings.
En una segunda etapa, si pretendemos que las flechas tomen un color similar al del conjunto de iconos, deberemos aplicar tres reglas de formato condicional, para los tres casos posibles:
1. B2>C2 => formato color fuente: Rojo
2. B2 formato color fuente: Verde
3. B2=C2 => formato color fuente: Amarillo

Seguir leyendo...

La mejor formación en Excel online con tutor.

Porque necesitas Excel, porque 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 Abril de 2014.

Los cursos de Excel y Macros abiertos para este mes de Abril 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 Tablas dinámicas en Excel

(ver más)

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

(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 abril de 2014; 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...

BUSCARV sobre rango con condiciones.

Me explico, trataré hoy la manera de aplicar una función BUSCARV sobre un rango de celdas... pero sólo sobre aquellas que cumplan unas condiciones.
Dicho de otro modo, tenemos un rango de celdas (o una Tabla) y queremos realizar una búsqueda vertical sólo sobre aquellos registros que cumplan ciertas condiciones.
Imaginemos el siguiente origen de datos A1:D11:

La idea es realizar la búsqueda sólo sobre aquellos registros que cumplan nuestras restricciones, en el ejemplo que el campo 'Cond1' sea igual a rojo y que al tiempo el campo 'Cond2' sea menor estricto a 100.. es decir, realizar la búsqueda vertical exclusivamente sobre los registros de las filas 3,6 y 10.
Sobre esas filas será sobre las que trabajará nuestras función BUSCARV para encontrar el elemento buscado, en el ejemplo el valor de la celda F3.
La función buscada de la celda G3, que ejecutaremos matricialmente, es:
=BUSCARV(F3;SI(B2:B11="rojo";SI(C2:C11<100;A2:D11));4;0)

donde indicamos que busque entre los registros que cumplen nuestras dos condiciones en la primera columna el elemnto x1 y devuelva su correspondiente de la cuarta columna (campo 'Importe').
En la imagen anterior vemos cómo nuestra función devuelve el valor esperado, es decir, de entre los tres registros, retorna el 'Importe' que corresponde al elemento 'x1'.

La clave de este resultado es el rango matricial que obtenemos con la fórmula:
=SI(B2:B11="rojo";SI(C2:C11<100;A2:D11))

que podemos ver en la imagen siguiente:

Observamos cómo sólo los tres registros comentados anteriormente aparecen en ese rango matricial.
En conclusión, hemos conseguido aplicar un BUSCARV sobre un rango filtrado con éxito.

Seguir leyendo...

Validación de datos sobre un rango con celdas vacías.

Atacaremos hoy un tema bastante recurrente, conseguir una Validación de datos sobre un rango de celdas con algunas de ellas sin valores o vacías.
Esta explicación da respuesta a la cuestión planteada por un lector:

...Yo selecciono el rango para la lista A1:A5, pero solo existen datos en las celdas A1, A3 y A5, hay alguna forma de que al momento de desplegar la lista solo me muestre las celdas con valores y excluya las celdas vacías (A2 y A4)?...

La respuesta que daremos, probablemente no sea la más directa, pero al menos conseguiremos nuestro objetivo, que es conseguir una validación de datos sin esas celdas vacías.
Partimos de la situación propuesta por el lector:

La idea es clara, mostrar una Validación de datos únicamente con los valores de las celdas con datos.
Para ello generaremos un primer Nombre definido con fórmula:
lista =SI(Hoja1!$A$1:$A$5="";Hoja1!$A$1;Hoja1!$A$1:$A$5)
con el que conseguimos un 'rango virtual' donde las celdas vacías han sido reemplazadas por el valor de la primera celda del rango (celda A1).
No es necesario, pero si quisiéramos visualizar ese rango podríamos ejecutar sobre nuestra hoja de cálculo, en C1:C5 lo vemos:

Creamos un segundo Nombre definido que empleará el anterior. Con este Nombre definido, conseguimos un listado de registros únicos:
listado =SI.ERROR(INDICE(lista;K.ESIMO.MENOR(SI(COINCIDIR(lista;lista;0)=FILA(INDIRECTO("1:"&FILAS(lista)));COINCIDIR(lista;lista;0);"");FILA(INDIRECTO("1:"&FILAS(lista)))));"")
En este caso SÍ es necesario mostrarlo, ejecutándola matricialmente (al igual que la anterior) en el rango E1:E5 veríamos:

El último paso, nuevamente generando un Nombre definido con la siguiente fórmula:
ListFinal =DESREF(Hoja1!$E$1;;;CONTARA(Hoja1!$E$1:$E$5)-CONTAR.BLANCO(Hoja1!$E$1:$E$5);1)
Con éste conseguimos tener un rango dinámico que se adapta exactamente al número de elementos únicos a mostrar.

En la celda E8 configuramos una Validación de datos tipo Lista con referencia a nuestro último Nombre definido: '=ListFinal':

Seguir leyendo...

Personalizar etiquetas de datos en un gráfico de Excel 2013

Recientemente vimos un truco para incorporar a nuestros gráficos de Excel unas etiquetas personalizadas sin emplear macros, pero engañando a Excel (ver entrada1), y más tiempo atrás aún, como conseguíamos lo mismo empleando una macro (ver entrada2)
Hoy lo que veremos es el avance que ha hecho Excel en su última versión -Excel 2013- donde habilita la posibilidad de editar y configurar el origen de las Etiquetas de datos a mostrar, de una manera algo más completa y personalizada que en versiones anteriores.

Para ello veremos el siguiente ejemplo propuesto por una lectora, donde pretendía incorporar unos valores porcentuales como parte de la etiqueta de datos de un gráfico de columnas apiladas. Veamos nuestro gráfico de partida y nuestro origen de datos donde debemos incorporar ese dato porcentual (rangos en amarillo):

El objetivo es claro, debemos añadir a nuestro gráfico, para cada punto de cada serie de datos, su Etiqueta de datos con el valor porcentual correspondiente... como a modo de ejemplo propuso la lectora.

Haremos uso de la nueva funcionalidad, muy sencilla de emplear.
Para ello seleccionaremos las etiquetas de datos en el gráfico de cada una de las cuatro series (Vend1, Vend2, Vend3 y Vend4) y con éstas seleccionados accederemos al Formato de Etiqueta de datos > Sección Opciones de etiqueta > Contenido de etiqueta > Valor de las celdas:

haz clic en la imagen

Al seleccionar la opción Valor de las celdas se abrirá una ventana diálogo donde indicaremos dónde están los datos a mostrar; en nuestro ejemplo y para la selección en:
=Hoja1!$C$6;Hoja1!$E$6;Hoja1!$G$6

Seguir leyendo...

VBA: Método SendKeys

En una entrada anterior vimos qué formas teníamos a nuestro alcance para ver el resultado de la celda en lugar de la fórmula o incluso en algunos casos un error de #¿NOMBRE? por que la versión no detecte nuestra función correctamente (ver entrada).
Al hilo de esta entrada anterior, un lector planteaba la posibilidad de automatizar el proceso manual, celda por celda, de ir presionando la tecla de función F2 (edita celda) más ENTER a continuación:

...Tengo seleccionado un rango de celdas, y necesito una macro que para cada celda seleccionada me haga las funciones de "F2+ENTER"...

Para tal fin necesitaremos emplear en una macro que crearemos el método .SendKeys que envía pulsaciones de la tecla que indiquemos.
La sintaxis es
expresión.SendKeys(Keys, Wait)
y el argumento Keys, esto es, la tecla sobre la que podemos forzar pulsaciones puede ser entre otras:
Tecla y Código
BORRAR := {CLEAR}
SUPRIMIR o SUPR := {DELETE} o {DEL}
FLECHA ABAJO := {DOWN}
FLECHA ARRIBA := {UP}
FLECHA IZQUIERDA := {LEFT}
FLECHA DERECHA := {RIGHT}
FIN := {END}
INTRO (teclado numérico) := {ENTER}
ENTRAR := ~ (tilde)
AV PÁG {PGDN}
RE PÁG {PGUP}
F1 a F15 := {F1} a {F15}

Admitiendo además las combinaciones provistas por Excel con MAYÚS o CTRL o ALT.
Para especificar una tecla combinada con otra u otras teclas:
MAYÚSCULAS := + (signo más)
CONTROL := ^ (signo exponencial)
ALT := % (signo de porcentaje)

Una solución al problema, además de alguna alternativa más, era manualmente ir editando la celda y validándola, esto es, presionando celda a celda F2 + ENTER... lo que para cientos de datos podría ser un engorro.
La siguiente macro realiza la misma acción empleando .SendKeys.
Accederemos a la ventana de código del explorador del editor de VBA, donde insertaremos el siguiente código VBA:

Sub Macro2()
Dim celda As Range
For Each celda In Range("B2:B12")
    celda.Select
    SendKeys "{F2}+{ENTER}", True
Next celda
End Sub

Ojo por que la condición inicial de formato de celda a Número o General se mantiene.
Fijémonos en la combinación de teclas ordenada
{F2}+{ENTER}
tecla función F2 y Mayúscula y ENTER, lo que recorrerá en sentido inverso (mayúscula y Enter cambia celda hacía arriba, y no hacía abajo como es normal).
Podemos ver el efecto en el vídeo siguiente:

Seguir leyendo...

Relleno rápido automático para Excel 2013.

Excel sigue mejorando cada versión, aunque siempre hay peticiones que parecen no llegar nunca... y otras nunca pedidas que aparecen.
En esta línea en la versión Excel 2013 surge el Relleno rápido, que funciona como un asistente de datos que termina el trabajo por nosotros, en general, tan pronto como detecta Excel lo que pretendemos escribir, la herramienta Relleno rápido introduce el resto de los datos de una sola vez, siguiendo el patrón que Excel reconoce en los datos.
Tengamos presente que esta herramienta Relleno Rápido puede que no siempre empiece a rellenar nuestros datos, por que no detecte la regla empleada. Funcionará mejor cuando los datos tengan algún tipo de coherencia. Si los datos tienen muchas incoherencias, siempre podremos optar por la herramienta Texto en columnas para dividir texto en celdas diferentes, o usar funciones de texto.

Lo que hace esta funcionalidad es, básicamente, autodetectar, a partir de un listado dado parte de cada valor de las celdas, independientemente del comienzo, final o incluso caracteres intermedios; además Relleno Rápido distingue mayúsculas y minúsculas, por tanto, si especificásemos una parte en minúsculas, el resultado devuelto se adaptará del original a las minúsculas.
Sobre todo está pensado para trabajar con Texto.

En primer lugar, antes de comenzar, nos aseguraremos que tenemos habilitada la funcionalidad. Desde las Opciones de Excel > Avanzadas > Sección Opciones de Edición, buscaremos Relleno rápido automático:

Veamos un ejemplo, en el que tenemos un listado de cursos:

En una celda anexa, cualquiera que tenga una continuidad de datos con el listado original, escribimos una parte del texto original correspondiente a la celda de la misma fila... en mi ejemplo, 'avanzado' (todo en minúscula).
En la celda siguiente, al empezar a escribir, veremos como nos muestra un listado autorellenado:

Vemos como Excel 2013 abre un desplegable autocompletable con las diferentes opciones que podrían corresponder. En particular y a pesar que el original tiene la primera en mayúscula, da prioridad a cómo lo estoy escribiendo yo, es decir, en minúscula.

Importante remarcar que NO funcionará si las celdas finales están disociadas del listado original... por ejemplo, si intentamos escribir dos columnas a la derecha:

Seguir leyendo...

Resaltar celda activa sin macros.

Hace ya algunos años mostré cómo, con una macro, podíamos resaltar la celda activa en cada momento (ver).
En la entrada veremos como llegar a un resultado similar empleando el Formato Condicional y la función CELDA, FILA y COLUMNA.
En particular conseguiremos resaltar nuestra celda activa dentro de un rango concreto de celdas, por ejemplo C2:E11.
En primer lugar seleccionaremos dicho rango y accederemos a la herramienta Formato Condicional con fórmula, donde introduciremos:
=Y(FILA()=CELDA("fila");COLUMNA()=CELDA("columna"))

Con la función CELDA("fila") o CELDA("columna") conseguimos información, al omitir su segundo argumento, especificada en el primer argumento 'tipo_de_info' (fila o columna en nuestro ejemplo) para la última celda cambiada.
Mientras que con FILA() y COLUMNA() obtenemos datos sobre la celda activa...

La lectura de la condición del formato condicional sería entonces que cuando la celda activa coincida en su FILA y COLUMNA con la última celda cambiada, resalte con el formato dado... precisamente lo que pretendemos.

Ya hemos acabado, podemos probar a seleccionar una celda dentro de nuestro rango formateadas.. veremos que tras cada selección y posterior actualización (presionando tecla de función F9) nuestra celda activa resalta con el formato dado...

Claro está que esta forma de trabajar no es la más cómoda, y para evitar ser nosotros manualmente quienes actualicemos nuestra hoja, para que refresque, y el formato condicional actué automáticamente, podríamos incluir una simple línea de código asociado a nuestra hoja de trabajo.
Por tanto accedemos al editor de VB desde el botón derecho del ratón sobre la etiqueta de hoja, seleccionando Ver código, donde seleccionaremos el evento _SelectionChange y el código a añadir será:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = True
End Sub

Seguir leyendo...