Gráficos Interactivos con Controles en Excel

¿Qué son los Gráficos Interactivos con Controles?

Para nosotros son una frecuente y útil funcionalidad que podemos encontrar en la mayoría de las herramientas de visualización de información que nos permite interactuar con los gráficos a través de sencillos controles que nos permite determinar, por ejemplo, que período de datos deseamos ver.

Aunque por defecto en Excel no contamos con la posibilidad de crear de forma directa graficos interactivos con controles, es muy sencillo incorporar a nuestras gráficas controles básicos que nos provean valor agregado a las labores de análisis incorporando mayor interactividad y flexibilidad, obteniendo resultados como este:

Gráficos interactivos con controles – Caso barras de desplazamiento.

Supongamos que disponemos de datos de un año de las ventas totales de nuestra empresa, pero para no saturar la visual solo queremos graficar las ventas de seis meses de los productos A,B,C,D,E, y F, dejando abierta la posibilidad de consultar datos históricos interactuando con una sencilla barra de desplazamiento.

Básicamente utilizaremos rangos dinámicos con nombre para crear interfaces que controlen que datos deben ser dibujados por nuestros gráficos, vinculando los rangos dinámicos con controles personalizados para permitir al usuario cambiar los datos de los gráficos mediante dichos controles que le permitirán actualizar de forma automática los datos.

C59 - Crear un Gráfico Interactivo

E1: Organizar nuestros datos en columnas.

Como toda buena historia para elaborar nuestros gráficos interactivos con controles debemos comenzar por preparar los datos.

Por ello el primer paso a seguir será tener organizados nuestros datos en columnas como en la imagen anexa.

E2: Crear rangos dinámicos: SCROLL_MESES y SCROLL_PRODUCTOS.

Una vez organizados nuestros datos nuestro siguiente paso será crear dos rangos dinámico con nombre a partir de nuestros datos de partida, es decir, los datos que deseamos graficar. Para ello debemos seleccionar Fórmulas > Nombres definidos > Asignar nombre a un rango >Definir nombre (en versiones anteriores a Excel 2007 la ruta a seguir será Insertar > Nombre > Definir).

En nuestro caso le daremos los nombres SCROLL_MESES, que nos permitirá controlar el cambio progresivo de las leyendas y SCROLL_PERIODO, que nos permitirá establecer los datos a visualizar en nuestros gráficos interactivos con controles.

Para el rango dinámico SCROLL_MESES en el cuadro de texto Se refiere a, escribiremos lo siguiente, empleando la función DESREF:

=DESREF(Hoja1!$A$1;Hoja1!$E$5;0;Hoja1!$D$5;1)

Luego damos clic en Aceptar.

Para el rango dinámico SCROLL_PRODUCTOS en el cuadro de texto Se refiere a, escribiremos lo siguiente, empleando la función DESREF:

=DESREF(Hoja1!$B$1;Hoja1!$E$5;0;Hoja1!$D$5;1)

Para finalizar volvemos a dar clic al botón Aceptar.

La función DESREF nos permitirá usar las celdas $D$5 y $E$5 para forzar los rangos referenciado en SCROLL_PRODUCTOS y SCROLL_MESES a expandirse tanto arriba como abajo en función del número que aparezca en estas celda. $D$5 nos permitirá controla la cantidad de períodos que deseamos visualizar en nuestra gráfica, conforme la celda $E$5 nos permitirá controlar el rango de tiempo que deseamos visualizar.

Por ejemplo, si cambiamos el número que hay en la celda $D$5 por 6, estaremos obligando a que el rango dinámico incorpore las celdas B6:B11.

Si no deseamos que los usuarios vean el contenido de las celda $D$5 y $E$5 siempre podemos ocultar su contenido haciendo clic derecho en el ratón sobre ella y seleccionando la opción Formato de celdas. Luego tan solo nos basta en seleccionar la categoría Personalizado e introducir el formato. Hacemos clic en Aceptar y listo. ¡El contenido de la celda estará oculto!

E3: Crear un gráfico de columnas.

Una vez definido nuestros rangos dinámicos procederemos a insertar un gráfico de columnas. Para ello basta que vayamos a nuestra ficha Insertar >Cuadro Gráficos > Columna.

Luego Vamos a nuestra ficha Herramientas de gráficos > Diseño y se elije Datos > Seleccionar datos, y elegimos nuestra columna de datos de Productos y pulsar Editar.

En el cuadro de texto Valores debemos cambiar a:

=Graficos_interactivos_con_controles.xlsx!SCROLL_PRODUCTOS
Luego cambiamos las etiquetas del eje horizontal, haciendo clic en Editar, y cambiando el rango de rótulos del eje por:

=Graficos_interactivos_con_controles.xlsx!SCROLL_MESES
Con eso ya elaboramos un gráfico interactivo. Sólo nos falta incorporar las funciones de interactividad para culminar este post sobre gráficos interactivos con controles.

E4: Agregar interactividad a nuestros graficos interactivos con controles – barra de desplazamiento.

El último paso es muy sencillo. Iremos a nuestra ficha Programador > Controles > Insertar > Controles de Formulario > Barra de desplazamiento.

Seleccionamos la barra de desplazamiento insertada le damos forma para ajustarla a la gráfica (aunque por defecto esta sea vertical, si dibujamos el control en forma horizonal, Excel interpretará que ésta es la orientación que queremos) y procedemos a configurarla haciendo clic derecho sobre la misma y seleccionando la opción Formato de Control. Luego elegimos la ficha Control y configuramos elvalor mínimo (para este ejemplo 1), el valor máximo (para este ejemplo 60), el valor de incremento (para este ejemplo 1) y la celda con la que lo vamos a vincular, en nuestro caso $E$5.

¡Listo! ¡Siguiendo estos pasos e incorporando tus propias variaciones es posible que configures otros tipos de graficos interactivos con controles!

Reflexión: 

Existen múltiples aplicaciones que podemos darle a los graficos interactivos con controles. Este tipo de técnica puede resultar particularmente útil cuando se trata de elaborar tableros de control, paneles de control o dashboards. Con tan solo cambiar el tipo de gráficos ya es posible crear gráficos interactivos con controles de líneas, por ejemplo.

Fuente

Excellentias - URL: http://www.excellentias.com/tutorial-excel-graficos-interactivos-con-controles/ - Consultada 30/09/2014

Combinar Correspondencia

Word te permite incluir en un documento los datos que tienes en una hoja de cálculo de Excel, también los de Access y de Outlook.

Así obtendrás a partir de un mismo documento diferentes, cartas, emails, etiquetas, sobres, facturas, recibos, etc. con un texto adaptado o personalizado para cada destinatario.

Partimos de dos herramientas fundamentales:

Documento principal de Word que contiene el texto fijo y una serie de campos que irán variando en función del origen de datos que tomes.

Origen de datos de Excel contiene una lista de registros por ejemplo: empresa, dirección, persona de contacto, ect.

C58 - Combinar Correspondencia

Un ejemplo de combinar correspondencia es enviar una carta modelo con una invitación a un evento en tu empresa: la presentación del nuevo producto.Quieres hacérselas llegar a 100 personas, has creado la carta en Word y tienes los datos de los invitados en Excel. Con la combinación de correspondencia lo podrás hacer rápido y fácil.

Lo primero tienes que crear o especificar cual va a ser el documento principal para luego seleccionar la base de origen de datos en Excel.

Estos son los pasos a seguir:

E1: Abre el programa Microsoft Word con un documento nuevo, también puedes optar por seleccionar un documento Word que ya tengas.

E2: Según la versión de Excel que tengas has de pulsar en los siguientes menús o comandos. En Excel 2010 y 2007: Correspondencia - Iniciar combinación de correspondencia -Paso a paso por el Asistente para combinar correspondencia. En Excel 2003: Herramientas - Cartas y correspondencia - Combinar correspondencia.

E3: En la parte derecha de la pantalla aparece una ventana llamada Combinar correspondencia.
Nos pide seleccionar el tipo de documento: Cartas, mensajes de correo electrónico, sobres, etiquetas y lista de direcciones, veras una breve descripción si te vas situando en cada una de las opciones, elige Cartas.

E4: Pulsa en la parte inferior donde pone Siguiente: Inicie el documento.
En la nueva pantalla tienes que seleccionar el documento inicial, elige Utilizar el documento actual y escribe el texto que tienes en la imagen, se trata de una carta invitando a nuestros clientes a una presentación del lanzamiento de nuestro nuevo producto.

E5: Pulsa al final de la pantalla donde pone Siguiente: Seleccione los destinatarios.En esta pantalla los pasos a seguir son los siguientes:

E6: En la ventana de la derecha aparecen tres opciones, vas a pulsar en Utilizar una lista existente, aunque también te permite otras dos opciones como son Seleccionar los contactos de Outlook o Escribir una lista nueva. Pulsa donde dice Examinar.

   

E7: Busca en tu equipo una de las hojas de cálculo de Excel en las que tengas datos de clientes. Haz clic en Aceptar.

    

Un campo de combinación es un texto variable que ira cambiando en cada carta en función del destinatario.

Los campos de combinación tienen los mismos nombres que los que aparecen en la primera fila de tu hoja de Excel donde tienes los datos de los clientes: empresa, persona de contacto, dirección.

E8: En Word 2010 y 2007 haz clic en la pestaña Correspondencia y luego en Insertar campos combinados, ahí te aparecerán los nombres de cada una de las celdas a combinar.
En Word 2003 Tendrás la barra de tareas Combinar correspondencia, que aparece debajo de la barra de formato (si no aparece pulsa en Ver - Barras de herramientas y marca Combinar correspondencia).

E9: Coloca el cursor en la parte de la carta donde quieras reflejar el nombre de la empresa e inserta el campo de combinación en ese punto haciendo clic encima de nombre. Verás que en la carta te aparece Empresa. Análogamente con contacto y dirección.
En la imagen puedes ver el resultado en la parte izquierda de la carta.

Además sitúa el cursor al final de la palabra Estimad@ e inserta el campo persona de contacto para personalizar aún mas el documento.


Una vez ejecutados las acciones del paso anterior, has de seguir con:

E10: En la parte inferior de la ventana izquierda haz clic en Siguiente: Vista previa de las cartas.
Puedes desplazarte por cada uno de los destinatarios según su número de orden y visualizar como quedara la carta que enviaras a cada uno de ellos. Pulsa abajo en Siguiente: Complete la combinación.

                                    

Elige Editar cartas individuales en la parte central de la pantalla, también podrías imprimir directamente.
En la ventana que aparece te pregunta que registros combinar en nuestro caso Todos y Aceptar.


Se abrirá un nuevo documento de Word que tendrá tantas páginas como el número de empresas existentes en la hoja de cálculo Excel.

Podrás imprimirlo, modificar algo, resaltar alguna característica.

Ese documento aparece con el nombre genérico Cartas1 que puedes guardar en tu computador con el comando Guardar como e indicarle donde quieres guardarle y también puedes darle otro nombre como sueles hacer con tus archivos normalmente.

De la misma manera se guarda el documento original donde has realizado combinar correspondencia por si en un futuro vas a necesitar esta carta combinada de nuevo.

En otro momento puede que te interese cambiar el origen de datos, modificar la carta, especificar registros concretos a combinar,... Atrévete a usar combinar correspondencia siempre que puedas. Te recuerdo: cartas, etiquetas,catálogos, sobres, facturas, actividades promocionales, correos electrónicos, recibos y un largo etcétera.

Reflexión

Las combinaciones de correspondencia te auxilian para el ahorro de tiempo y esfuerzo en la captura de datos, como ejemplo cuando tienes que trabajar al mismo tiempo una base de datos con información de acces a excel o word, y viceversa, imprimiendo información de ambos e incluso de los tres programas sin tener que capturar en todos ellos la misma información, mediante algunas plantillas y combinaciones sencillas; las macros igual te ahorran trabajo, sobre todo en la repetición de tantos comandos.

Fuente

http://hojasdecalculo.about.com/od/Excel_en_los_negocios/ss/C-Omo-Realizar-Combinar-Correspondencia.htm - Consultada 

Tablas y Gráficos Dinámicos en Excel 2007

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc.

C54 - Crear una Tabla Dinámica

Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos.

Para crear una tabla dinámica, Excel nos proporciona las tablas y gráficos dinámicos.

Supongamos que tenemos una colección de datos de los artículos del almacen con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo.

Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.

E1: Para ello vamos a la pestaña Insertar y hacemos clic en Tabla dinámica (también podemos desplegar el menú haciendo clic en la flecha al pie del botón para crear un gráfico dinámico).

Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí podremos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla.

E2: En nuestro caso indicamos que vamos a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de cálculo nueva.

Podríamos crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel.

En el caso de seleccionar la opción Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras.

Pulsamos Aceptar para seguir.

Se abrirá un nuevo panel en la derecha de la pantalla:

E3: Desde este panel podemos personalizar la forma en que van a verse los datos en la tabla dinámica.

Con esta herramienta podríamos construir una tabla dinámica con la siguiente estructura:

• Una fila para cada una de las Referencias de la tabla.

• Una columna para cada uno de los Meses de la tabla.

• En el resto de la tabla incluiremos el total del Importe para cada Referencia en cada Mes.

Para ello simplemente tendremos que arrastrar los elementos que vemos listados a su lugar correspondiente al pie del panel.

En este ejemplo deberíamos arrastrar el campo REF a , el campo MES a y finalmente el campo IMPORTE a la sección .

Tras realizar la tabla dinámica este sería el resultado obtenido.

Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes.

Con esta estructura es mucho más fácil analizar los resultados.

E4: Una vez creada la tabla dinámica nos aparece la pestaña Opciones:

El panel lateral seguirá pudiéndose utilizar, así que en cualquier momento podremos quitar un campo de un zona arrastrándolo fuera.

Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos.

Si arrastrásemos a la zona de datos los campos cantidad y total, obtendríamos la siguiente tabla, más compleja pero con más información:

Puede que no visualices la tabla de la misma forma, al añadir varios campos en la sección Valores el rótulo aparecerá en una las secciones de rótulos, si te aparece en Rótulos de columna despliega la lista asociada a él y selecciona la opción Mover a rótulos de fila.

E5: Eliminar una tabla dinámica.

Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su totalidad y presionar la tecla Supr.

C55 - Aplicar filtros a una tabla dinámica

 Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar únicamente lo que nos interese en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.

Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable.

E1: Por ejemplo, si pulsamos sobre la flecha del rótulo Rótulos de columna nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones en este caso todos los meses.

E2: Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecerán de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Seleccionar todo).

E3: Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba.

Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas, por ejemplo podemos seleccionar ver los articulos con referencia 1236 de Abril.

C56 - Obtener promedios en una tabla dinámica

Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc.

E1: Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú emergente con diferentes opciones, debemos escoger la opción Configuración de campo de valor... y nos aparece un cuadro de diálogo como el que vemos en la imagen.

En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.

También podemos abrir el cuadro de diálogo con el botón de la pestaña Opciones.

C57 - Gráficos con tablas dinámicas

E1: Para crear una gráfica de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico dinámico de la pestaña Opciones.

E2: Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos.

Al pulsar este botón se abrirá el cuadro de diálogo de Insertar gráfico, allí deberemos escoger el gráfico que más nos convenga.

Reflexión

Las tablas dinámicas son muy útiles para estructurar datos que se presentan en bruto, resumirlos y crear informes. Normalmente, una tabla de datos de Excel no puede cambiar de estructura. Podemos ordenar los datos, filtrarlos, añadir subtotales, pero la estructura en filas y columnas es inalterable. Uno de los objetivos de las tablas dinámicas es la posibilidad de alterar esa estructura y a la vez resumir datos. Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas.

Fuentes

Aula Clic - URL:http://www.aulaclic.es/excel2007/t_16_1.htm - Consultada 28/09/2014

Aula Clic - URL:http://www.aulaclic.es/excel2007/t_16_2.htm - Consultada 28/09/2014

Aula Clic - URL: http://www.aulaclic.es/excel2007/t_16_3.htm - Consultada 28/09/2014

Funciones MAX y MIN en Excel

Las funciones MAX y MIN nos mostrarán el valor máximo y mínimo de los valores de una lista de números (Rango de celdas).

Estas dos funciones actúan en una forma muy parecida. Veamos la sintaxis:

MAX(número1;número2; ...) - MIN(número1;número2; ...)

Número1, número2... son de 1 a 255 números de los que desea encontrar el valor máximo y mínimo. 

A considerar:

• Los argumentos pueden ser números, o nombres, matrices o referencias que contengan números. 
• Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos. 
• Si el argumento es una matriz o una referencia, sólo se utilizarán los números contenidos en la matriz o en la referencia. Se pasarán por alto las celdas vacías, los valores lógicos o el texto contenidos en la matriz o en la referencia. 
• Si el argumento no contiene números, max o min devuelve 0. 
• Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores. 

Veamos un ejemplo en el que usamos estas funcionas: 

C52 - Función MAX

E1: Nos posicionamos sobre la celda en la que queremos que aparezca el resultado deseado, en este caso es E13. 

E2: Colocamos  =MAX( y luego el rango de celdas que contienen los datos de los cuales queremos saber cuál es el mayor y cerramos el paréntesis, quedando entonces la sintaxis así: =MAX(D4:D11) y nos da por resultado 15000.

C53 - Función MIN

E1: De igual modo que en el caso anterior nos posicionamos sobre la celda en la que queremos que aparezca el resultado deseado, en este caso es E14. 

E2: Colocamos  =MIN( y luego el rango de celdas que contienen los datos de los cuales queremos saber cuál es el mayor y cerramos el paréntesis, quedando entonces la sintaxis así: =MIN(D4:D11) y nos da por resultado 420.

Reflexión

Este tipo de funciones no presentan mayor dificultad a la hora de ser utilizadas, son muy simples y sencillas. Nos proporcionan saber de manera rápida conocer un número dentro de muchos datos alineados en una fila o columna que si los tuviéramos que mirar para reconocer cuál es el más grande o más chico tal vez nos llevaría un tiempo.

Fuente

Taringa! - URL: http://www.taringa.net/posts/hazlo-tu-mismo/15004596/Funcion-max-y-min-en-Excel.html - Consultada 13/09/14

Función K.ESIMO.MAYOR y K.ESIMO.MENOR en Excel

La función K.ESIMO.MAYOR en Excel nos permite encontrar el valor más grande dentro de un rango de datos o podemos especificar una posición específica dentro los datos que nos devolverá la función.

Sintaxis de la función K.ESIMO.MAYOR

La función K.ESIMO.MAYOR tiene dos argumentos:

• Matriz (obligatorio): El rango de datos donde deseamos buscar.
• K (obligatorio): La posición que ocupa el elemento de la Matriz que deseamos obtener.

Una ventaja de esta función es que la Matriz de datos no tiene que estar ordenada de ninguna manera y aun así obtendremos el resultado correcto. Sin embargo, si la Matriz especificada está vacía, la función K.ESIMO.MAYOR devolverá el error #¡NUM!

C50 - Función K.ESIMO.MAYOR

Para probar la función K.ESIMO.MAYOR haremos un ejemplo muy sencillo. Tengo una lista de números en la columna A que no están ordenados. Necesito saber cuál de todos los números es el mayor para lo cual utilizaré la siguiente fórmula:

E1: Introducimos en la celda en la que queremos que aparezca el resultado la siguiente expresión: 

=K.ESIMO.MAYOR(A1:A10, 1)

El resultado de esta fórmula lo puede observar en la celda C1 que correctamente devuelve el valor 50 que es precisamente el valor más grande de la lista:

E2: Si quisiera saber cuál es el segundo número más grande de la lista utilizo la siguiente fórmula:

=K.ESIMO.MAYOR(A1:A10, 2)

El resultado lo puedes observar en la celda C2:

El segundo argumento de la función nos sirve para indicar la posición que deseamos obtener.

La función K.ESIMO.MENOR en Excel devuelve el valor más pequeño dentro de un rango de celdas pero también podemos especificar alguna posición relativa al conjunto de datos que deseamos que la función nos devuelva.

Sintaxis de la función K.ESIMO.MENOR

La función K.ESIMO.MENOR tiene dos argumentos:

• Matriz (obligatorio): La matriz o rango de datos donde deseamos buscar.
• K (obligatorio): La posición dentro de la Matriz que ocupa el elemento que deseamos obtener a partir del menor de los valores.

Si el argumento K es superior al número de elementos de la Matriz, la función K.ESIMO.MENOR devolverá el error #¡NUM!.

C51 - Función K.ESIMO.MENOR

Tengo un conjunto de números en la columna A y de los cuales necesito saber cuál de ellos es el número más pequeño. Podemos utilizar la función K.ESIMO.MENOR para obtener el menor de todos los valores de la siguiente manera:

E1: Introducimos en la celda en la que queremos que aparezca el resultado la siguiente expresión:

=K.ESIMO.MENOR(A1:A10, 1)

Observa el resultado de esta fórmula en la siguiente imagen:

E2: Con solo modificar el segundo argumento de la función puedo conocer los 3 valores más pequeños del rango de datos. En la siguiente imagen puedes observar cómo la función K.ESIMO.MENOR me permite hacer esto fácilmente:

Lo único que debemos cambiar en cada una de las fórmulas anteriores es el valor del argumento K el cual hará que lafunción K.ESIMO.MENOR devuelva el valor que ocupe dicha posición (de menor a mayor) dentro del rango de datos.

Reflexión

La verdad que desconocía esta función de Excel, pero al ir estudiándola y ver cuál es su funcionalidad resulta práctica para utilizarla de manera anidada, es decir, como complemento de otra función para lograr deducir cálculos o resolver operaciones que de manera sencilla no se podría, por eso requieren un grado de comlejidad.

Fuentes:

- Excel Total - URL: http://exceltotal.com/funcion-k-esimo-mayor-en-excel/ - Consultada 13/09/14

- Excel Total - URL: http://exceltotal.com/la-funcion-k-esimo-menor-en-excel/ - Consultada 13/09/14

EXCEL: INDICE Y COINCIDIR

Las funciones INDICE y COINCIDIR se usan para buscar datos en una tabla como resultado de la intersección de una fila y una columna específicas.

Con la función COINCIDIR averiguamos el número correspondiente de la fila y de la columna y después con la función INDICE especificamos la tabla y el nº de fila y columna.

La sintaxis correcta de las funciones es la siguiente:

• COINCIDIR(valor buscado;tabla en la que se busca;tipo de coincidencia)
• INDICE(tabla;nº fila;nº columna)

Vamos a ver el uso de las funciones primero por separado y después de forma conjunta.

C47 - Función COINCIDIR

COINCIDIR(valor buscado;tabla en la que se busca;tipo de coincidencia).

El tipo de coincidencia es opcional y puede ser:

• 1: COINCIDIR encontrará el valor más grande que sea menor o igual al valor buscado. El rango debe estar ordenado de manera ascendente.
• 0: COINCIDIR encontrará el primer valor que sea exactamente igual al valor buscado. El rango puede estar en cualquier orden.
• -1: COINCIDIR encontrará el valor más pequeño que sea mayor o igual que el valor buscado. El rango debe estar ordenado de manera descendente.

E1: El siguiente ejemplo devuelve el lugar que ocupa España (celda C2) dentro de la lista de paises (A1:A6), el tipo de coincidencia es exacta, de ahí el cero. El resultado es 2

E2: El siguiente ejemplo devuelve el lugar que ocupa el valor 55 (celda D4) dentro de la lista de valores (A1:J1), el tipo de coincidencia es 1. El resultado es 4

C48 - Función INDICE

INDICE(tabla;nº fila;nº columna)

E1: El siguiente ejemplo busca en la tabla y devuelve el valor que se encuentra en la intersección de la fila 2 y de la columna 3.

C47 - Fórmula Indice y Coincidir anidadas en Excel

La función BUSCARV tiene el problema de que en la matriz de búsqueda la columna del valor a buscar tiene ser la misma o estar a la izquierda del valor a devolver , esta función no nos sirve si queremos devolver un valor hacia la izquierda, veamos la siguiente tabla:

Si quisiéramos saber en que mes la venta fue de 80.230 no podríamos usar BUSCARV, pero el problema se resuelve con el adecuado anidamiento de INDICE y COINCIDIR, a este anidamiento se le llama FORMULA, veamos como:

E1: INDICE puede extraer el valor de una matriz si le damos los datos de fila y columna, pues el valor estará en la intersección de ellos, el valor de la columna lo tenemos, ya que este debe estar en la columna nº1 que es la del mes, solo nos falta el valor de la fila, que muy amablemente nos lo entrega la función COINCIDIR quedando la siguiente fórmula

E2: Si la referencia en COINCIDIR es B15, la tabla en la que se busca la posición C2:C13, en INDICE la matriz de la cual se saca el valor B2:C13 y la columna es la nº1.

Si ponemos los datos en una Hoja de Excel

Y si se introduce la fórmula en la celda E15, obtenemos el mes de Agosto, como se puede ver en la tabla.

Reflexión

La flexibilidad que nos ofrece la función INDICE nos permite utilizarla en otros escenarios para buscar un valor y regresar múltiples resultados. Por otro lado, la función COINCIDIR combinada con la función INDICE es una excelente alternativa cuando deseamos hacer una búsqueda sobre una tabla y elegir la columna que necesitamos como resultado a partir de una lista desplegable.

Fuente

- AllExcel - URL: http://www.allexcel.com.ar/anidamiento%20de%20funciones/tutoriales/INDICE%20Y%20COINCIDIR%20POR%20BUSCARV/indice%20y%20coincidir.html - Consultada 13/09/2014

- Cien por Cien Office - URL: http://cienporcienoffice.wordpress.com/2013/05/21/funciones-indice-y-coincidir/ - Consultada 13/09/2014

Formato Condicional en Excel

El Formato Condicional es un herramienta muy útil como información gráfica adicional para los datos numéricos que están en celdas o en rangos, con mas razón si hablamos de Excel 2007, que ha mejorado enormemente las posibilidades en este aspecto. Este tipo de formato tiene un comportamiento similar al de la función SI ya que otorga un formato a una celda o rango, dependiendo del cumplimiento de una condición, que es en definitiva, el cumplimiento de una proposición lógica.


Los formatos condicionales mas novedosos son:

• Barra de datos: Es semejante a un gráfico de barras proporcional al numero en la celdas. 
• Escala de color: Da un color a una celda, de acuerdo al valor de esta. 
• Conjunto de iconos: Permite según el valor de un celda compararla con otro de referencia y añadirle un icono. 

Aunque hay muchas mas opciones; entre ellas se pueden marcar celdas que cumplan con determinadas condiciones y dar colores a los números.

Se puede también aplicar mas de un formato a una celda, o sea, si una condición cambia el tamaño de la fuente de una celda y otra cambia el color del fondo, cuando se cumplan ambas condiciones , también se aplicaran ambos formatos.


La tabla1 que se ve a continuación contiene simultáneamente los formatos Barra de datos, Escala de colores y Conjunto de íconos

Para aplicar estos formatos el método es muy parecido.

C46 - Aplicar Formato Condicional

E1: En la cinta de opciones vamos a la pestaña "Inicio" y luego al panel "Estilos"

Al pulsar en este aparece otro panel también llamado "Estilos"

E2: Pulsamos "Formato condicional" que cambia de color y despliega un menú de opciones, donde están destacados en rojo los formatos mencionados al principio mas otros dos en la parte superior

Los triángulos negros nos indican que hay mas opciones de formato como se puede ver al oprimir, por ejemplo en "Barra de datos".

E3: Luego dada la siguiente tabla igual a la tabla1 pero sin formato

E4: Para aplicar el formato Barra de datos, a la etiqueta venta, se selecciona la columna donde están los datos y se pulsa en el modelo señalado por la flecha roja

Quedando como la columna VENTAS de la tabla1

Reflexión

El formato condicional permite responder visualmente preguntas específicas sobre los datos. Se puede aplicar formato condicional a un rango de celdas, a una tabla de Microsoft Excel o a un informe de tabla dinámica. El formato condicional facilita el proceso de resaltar celdas o rangos de celdas interesantes, de destacar valores inusuales y de ver datos empleando barras de datos, escalas de colores y conjuntos de iconos. Un formato condicional cambia el aspecto de un rango de celdas en función de condiciones (o criterios). Si la condición es verdadera, el rango de celdas basa el formato en dicha condición; si la condición es falsa, el rango de celdas no tiene formato basado en dicha condición. Se puede ordenar y filtrar los datos por formato, incluidos el color de celda y el color de fuente, independientemente de que las celdas tengan un formato manual o condicional.

Fuente

- Allexcel - http://www.allexcel.com.ar/formato-condicional/formato-condicional.html - Consultada 09/09/2014