OBJETIVOS

El objetivo de este blog es brindar aporte de ideas practicas para dar información acerca de los temas tocados en èl.

EJERCICIO PRÁCTICO DE FORMATO CONDICIONAL

Descargamos el archivo con los datos necesarios para la práctica.
1. Archivo de formato condicional: descarga el fichero.2. Guardar el archivo: FormatoCondicional.xls en el escritorio.
3. Abrir el libro de Excel.


DESCRIPCION

La columna B, muestra el nombre del delegado.

La columna C, muestra el porcentaje de los objetivos de las ventas, el valor se introducirá directamente en la celda, pudiendo obtenerse de una fórmula calculada.

Las columnas E hasta N, mostrará un color según el porcentaje de su valor en la columna C:

• La columna E, mostrará un color si el porcentaje es hasta un 10%.
• La columna F, mostrará un color si el porcentaje es hasta un 20%.
• La columna G, mostrará un color si el porcentaje es hasta un 30%.
• La columna H, mostrará un color si el porcentaje es hasta un 40%.
• La columna F, mostrará un color si el porcentaje es hasta un 50%.
• La columna G, mostrará un color si el porcentaje es hasta un 60%.
• La columna H, mostrará un color si el porcentaje es hasta un 70%.
• La columna F, mostrará un color si el porcentaje es hasta un 80%.
• La columna G, mostrará un color si el porcentaje es hasta un 90%.
• La columna H, mostrará un color si el porcentaje es hasta un 100%.

La columna B, Nombre deseado

La columna C, Porcentaje deseado.

• Celda E3. =$C$3<100% y color deseado.
• Celda F3. =$C4>=20% y color deseado.
• Celda G3. =$C5>=30% y color deseado.
• Celda H3. =$C6>=40% y color deseado.
• Celda I3. =$C7>50% y color deseado.
• Celda J3. =$C8>60% y color deseado.
• Celda K3. =$C9>70% y color deseado.
• Celda L3. =$C10>80% y color deseado.
• Celda M3. =$C11>90% y color deseado.
• Celda N3. =$C13>100% y color deseado.

Tips de "SUBTOTALES" en excel

Con Excel es posible calcular subtotales de los diferentes elementos o componentes de una base de datos, de manera tal que podemos agrupar elementos para así identificar la suma de sus valores.

Supongamos un listado de vendedores, de productos y sus respectivas ventas. Podemos agrupar por vendedores y por productos, de manera que Excel puede calcular cuánto vendió cada trabajador, ya sea de un producto determinado o de todos los productos. Igualmente agrupar por productos de tal forma que Excel pueda calcular cuánto se vendió  de cada producto y cuánto vendió cada empleado.

Si quisiéramos agrupar y subtotalizar los productos, ordenamos la columna de producto y en las opciones de subtotales en Excel aparecerán marcadas por defecto las opciones productos y ventas, y tendríamos lo siguiente:

Este un ejemplo muy sencillo que muestra la bondades de los subtotales en Excel, ya que nos presenta un esquema gráfico de fácil comprensión.

Los subtotales también se pueden lograr con tablas dinámicas y con funciones como SUMAR.SI o BDSUMA.

Tips de "FORMATO CONDICIONAL" en Excel

Voy a realizar una adición en el uso del conjunto de iconos del formato condicional de Excel para representar los cumplimientos de las metas propuestas; se trata de adicionar una alerta más, no es una regla más es decir va a ser un conjunto de 4 alertas. Esto a manera de seguimiento de los resultados puede ser una herramienta muy valiosa. Tenemos la siguiente información:

En la primera matriz tenemos las ventas de 10 personas en los años 2011, 2012 y 2013. A la derecha se encuentran las metas propuestas para las ventas realizadas. Voy a calcular el porcentaje de cumplimiento obtenido por cada venta según las metas:

Manual de Excel Avanzado. Aproveche el potencial de Excel para hacer más productivo su trabajo.

Ahora voy a insertar el conjunto de íconos de indicadores de la siguiente manera:

• Alerta de Color Azul: para representar cumplimientos superiores a 120%
• Alerta de color Verde representará un cumplimiento entre 100% y 120%.
• Alerta de color Amarillo representará un cumplimiento entre 80% y 100%.
• Alerta de color Rojo representará un cumplimiento menor a 80%.

Primero seleccionamos el rango al cual necesitamos agregar las alertas, es decir en este caso sería el rango C4:E13. Luego nos ubicamos en la ficha INICIO, grupo de herramientas Estilos, encontramos la herramienta Formato condicional seleccionamos Conjunto de íconos y damos clic en Más reglas:

Con esto se abre la ventana de Regla de formato condicional. Primero seleccionamos el conjunto de íconos de Indicadores X4:

Como no se encuentra una alerta de color azul para el primer indicador, valor a seleccionar uno de los íconos de Valoración:

Luego ajustamos las alertas para los demás íconos:

Ahora solo falta indicarlos valores a partir del cual se definen las alertas de los indicadores:

I

Es muy importante que la variable Tipo seleccione Número. El resultado es el siguiente:

Descargar archivo de trabajo.

Tips de "CONSOLIDAR DATOS" En exce

Para consolidar datos nos ubicamos en la celda en la que queremos colocar los datos consolidados, vamos  a la opción “Datos” y luego elegimos “Consolidad” y tendremos el siguiente cuadro de diálogo:

Allí asignamos las referencias o rangos de los datos que deseamos consolidar. En este caso las hojas que corresponden al primer trimestre, donde los dados Ventas y Comisiones están en los rangos B2:C6, y tendremos lo siguiente:

Aquí tenemos el consolidado por cada empleado. Si damos clic en el signo + que está a la izquierda, se mostrarán los datos que fueron consolidados, es decir los valores individuales que fueron objeto de consolidación (para que estos valores desplegables aparezcan, al consolidad se marca la opción Crear vínculos con los datos de origen”.

Tipa de "MATRICES" en Excel

Si ha hecho un poco de programación, es probable que se haya encontrado con el término matriz. A efectos de este artículo, una matriz es una colección de elementos. En Excel, esos elementos pueden residir en una única fila (lo que se denomina una matriz horizontal unidimensional), una columna (una matriz vertical unidimensional) o varias filas y columnas (una matriz bidimensional). En Excel no es posible crear matrices ni fórmulas de matriz tridimensionales.

Una fórmula de matriz es una fórmula que puede realizar varios cálculos en uno o varios de los elementos de una matriz. Las fórmulas de matriz pueden devolver varios resultados o un único resultado. Por ejemplo, se puede colocar una fórmula de matriz en un rango de celdas y utilizarla para calcular una columna o fila de subtotales. También se puede colocar en una sola celda y calcular una cantidad única. Una fórmula de matriz que reside en varias celdas se denomina fórmula de varias celdas, mientras una que reside en una sola celda se denomina fórmula de una celda.

Este es un ejemplo:

Manual de Excel Avanzado. Aproveche el potencial de Excel para hacer más productivo su trabajo.

1. Creamos el siguiente cuadro en desde la celada A1.

2. Para multiplicar los valores de la matriz (el rango de celdas comprendido entre C2 y D11), escriba en la celda E2 la siguiente fórmula:

=C2:C11*D2:D11

3. Pulsar la tecla Enter.

4. Seleccionar desde E2: E11

5. Pulsar la tecla de función F2

6. Presione CTRL+MAYÚS+ENTRAR.

Excel incluye la fórmula entre llaves ({ }) y coloca una instancia de la misma en cada celda del rango seleccionado. Eso sucede con mucha rapidez, así que lo que verá en la columna E es la cifra de ventas total de cada tipo de vehículo por vendedor.

Tips de "VALIDACIONES" en excel

Cuando ingresamos información en cualquier celda de un libro nuevo de Excel, no hay impedimentos para colocar cualquier número (entero o decimal), fecha, hora o desde una simple palabra hasta un párrafo completo (lo cual no es muy recomendable en Excel). Sin embargo, en algunos casos donde se pretenda desarrollar un pequeño sistema de información donde por ejemplo tengamos diseñado una columna para ingresar la edad de un listado de clientes; no esperamos que quien vaya a ingresar esos datos, coloque en la columna edad, el nombre o el apellido o cualquier otro dato distinto a la edad ya que supongamos tenemos una fórmula que nos calcula la edad promedio de los clientes:

Pero si alguien por error no ingresa la edad sino la estatura o la fecha de nacimiento o el apellido lo podrá hacer sin problema. El problema es que el cálculo de la EDAD PROMEDIO no será correcto:

Manual de Excel Avanzado. Aproveche el potencial de Excel para hacer más productivo su trabajo.

Bien, para evitar estos errores Excel nos permite utilizar una herramienta muy práctica, Validación de Datos. Ubicada en la Ficha DATOS, en el grupo de Herramientas de Datos, esta herramienta nos permite configurar las celdas que necesitemos, para permitir la entrada de los datos de acuerdo a nuestra necesidad. De esta manera, si necesitamos que solo se ingresen números enteros o fechas o un texto específico, esta es la herramienta para ello.

Para utilizar esta herramienta, lo primero es ubicarnos en la celda o rango de celdas que necesitamos configurar los datos que ingresemos en ellas. Para nuestro ejemplo, seleccionamos el rango que corresponde a la columna Edad. Lo segundo es abrir la herramienta de Validación de datos (Ficha DATOS, Grupo Herramientas de datos. Ver imagen anterior); con esto se nos abre la ventana de Validación de datos con unas opciones. Tres pestañas: Configuración, Mensaje de Entrada y Mensaje de error:

Pestaña Configuración:

Esta es la pestaña principal para configurar una validación de datos pues es aquí donde le indicamos que tipo de datos vamos a permitir que se ingresen en el rango previamente seleccionado. En la opción Permitir, observemos que por defecto esta seleccionada la opción Cualquier valor, por esta razón es que podemos ingresar fechas, números enteros, decimales, texto. Si damos clic, se despliegan los tipos de datos que se pueden aplicar como criterio de validación

Así que solo debemos seleccionar el tipo de datos que queremos que se puedan introducir en el rango, para nuestro ejercicio, ya que la edad se da en números enteros (generalmente), seleccionamos la opción Número entero. Pero si los datos de la columna que estemos configurando corresponde a fecha de nacimiento, deberíamos aplicar como tipo de datos en el criterio de validación la opción Fecha o si se trata de la estatura deberíamos aplicar Decimal.

Luego de seleccionar la opción Número entero. podemos configurar si los números enteros que se pueden ingresar en las celdas, tienen alguna condición:

Para el ejercicio que estamos desarrollando, recordemos que se trata de la edad, no debemos permitir que se ingresen números enteros negativos, asi que configuramos que los datos que se vayan a ingresar sean mayor o igual que 0:

Pestaña Mensaje de entrada:

En esta pestaña podemos configurar un mensaje que pueda servir para la persona que vaya a ingresar la información donde pueda entender que en la celda debe ingresar valores de un tipo en específico. Para nuestro ejercicio indicamos lo siguiente:

Pestaña Mensaje de error:

En esta pestaña indicamos el mensaje de error personalizado que queramos que se muestre cuando se ingrese un dato que no es válido en la celda:

Damos Aceptar y veamos cómo queda nuestro rango en Excel:

Veamos que cuando estamos ubicados en alguna celda del rango al que aplicamos Validación de Datos, nos muestra el mensaje de entrada que configuramos previamente

Si ingresamos un dato diferente al configurado (Numero entero), nos sale el mensaje de error:

De esta manera ya podemos estar tranquilos de que la información que se va a ingresar en la columna Edad, serán solo números enteros y podremos realizar los cálculos sin inconvenientes.

Nota: La configuración de las pestañas 2 y 3 de la validación de datos son opcionales.