PDF de programación - Guía Teórico-Práctica 2 - Análisis de datos

MICROSOFT EXCEL 2007 – NIVEL AVANZADO

GUÍA TEÓRICO-PRÁCTICA N° 2 – ANALISIS DE DATOS



Contenido

INTRODUCCIÓN ...............................................................................................................................2
CONCEPTOS BÁSICOS ....................................................................................................................2
Formato Condicional........................................................................................................................2
Novedades Excel 2007 en Formato Condicional.........................................................................5
Actividad 1.......................................................................................................................................6
Validación de Datos .........................................................................................................................7
Actividad 2.......................................................................................................................................9
Subtotales .........................................................................................................................................9
Actividad 3..................................................................................................................................... 11
Autofiltros y Filtros Avanzados .....................................................................................................12
Autofiltros...................................................................................................................................12
Filtros Avanzados.......................................................................................................................13
Actividad 4.....................................................................................................................................14
Escenarios ......................................................................................................................................15
Actividad 5.....................................................................................................................................16
Formularios ....................................................................................................................................16
¿Que es un formulario en Excel?...............................................................................................16
Tipos de formularios en Excel....................................................................................................17
Formulario de datos...................................................................................................................17
Actividad 6.....................................................................................................................................18
EJERCICIO COMPLEMENTARIO: ................................................................................................18





Área de Servicios

Dpto. de Informática

U.N.S.L.

“MICROSOFT EXCEL 2007 – NIVEL AVANZADO”

INTRODUCCIÓN

El analisis de datos permitira utilizar Excel no solo como una planilla para realizar calculos sino
que tambien se podran aplicar formatos a las celdas según condiciones predeterminadas, poder
hacer controles de los datos al momento que estos se van ingresado, agrupar los datos según
algun criterio y totalizar las columnas seleccionadas, realizar filtros combinados, escenarios que
nos permitiran analizar un resultado generado en base a un conjunto de celdas variable y diseñar
formularios para realizar el ingreso de datos de una manera mas elegante.

CONCEPTOS BÁSICOS

Formato Condicional
El formato condicional en Excel es una funcionalidad de gran utilidad al momento de realizar el
análisis de datos ya que permite dar un formato especial a un grupo de celdas en base al valor de
otra celda. Esto permitirá aplicar un tipo de fuente específico o un color de relleno diferente para
aquellas celdas que cumplan con ciertas reglas y así poder identificarlas fácilmente en pantalla.
Razón para utilizar el formato condicional
Siempre se tiene la opción de aplicar manualmente un formato a cada una de las celdas que
cumplen con una condición, pero eso se puede convertir en un proceso largo y repetitivo,
especialmente si se cuenta con una tabla de datos muy grande y que cambia frecuentemente. Es
por eso que el formato condicional puede hacer más fácil la tarea de cambiar automáticamente el
formato de la celda que cumple con ciertos criterios.
Ejemplos de formato condicional

Formato especial para todos los valores entre 20 y 30

Resaltar los valores por debajo del promedio.

(Para este ejemplo el promedio es 22.2)



¿Cómo se aplica el formato condicional?
El primer paso para aplicar un formato condicional es seleccionar las celdas a las que se le
aplicara dicho formato. Una vez seleccionadas hacer clic en el comando Formato condicional que
aparece en el grupo Estilos de la ficha Inicio.
Como se menciono previamente, al seleccionar alguna de las opciones del menú, el formato se
aplicará para aquellas celdas que cumplan con cierta condición o regla. Un formato condicional en
Excel está siempre basado en una regla que posteriormente se podrá editar en caso de ser

MICROSOFT EXCEL - AVANZADO

GUÍA N° 2

2




Área de Servicios

Dpto. de Informática

U.N.S.L.

“MICROSOFT EXCEL 2007 – NIVEL AVANZADO”

necesario. Las reglas que se crean para los formatos condicionales
se pueden dividir en dos grandes grupos:

• Reglas basadas en valores de celda: Estas reglas se
basan en el mismo valor de la celda (Mayor que, Menor que,
Igual a, Entre, etc.).

• Reglas basadas en fórmulas: Estas reglas ofrecen mayor
flexibilidad porque se puede aplicar un formato especial
utilizando una fórmula donde se podrá aplicar una lógica más
compleja. Este tipo de reglas es un poco más complicado de
comprender pero muy útil al momento de utilizar.

Resaltar celdas con formato condicional

Esta opción de menú dará la oportunidad de destacar celdas
que cumplan con
formato condicional
especificada. Cada opción muestra su propio cuadro de
diálogo solicitando los argumentos necesarios para crear la
regla.

regla de

la

Reglas de formato condicional
La opción Es mayor que… muestra el siguiente cuadro de diálogo:

En el primer cuadro de texto se deberá colocar un número a partir
del cual se aplicará el formato especificado. Para este ejemplo se
coloco el número 50 en la caja de texto y se dejo el formato
predeterminado y el resultado fue el siguiente:



Las opciones Es menor que…, Es igual a…, Texto que contiene…, esperan que se ingrese un
único valor con el cual comparar las celdas
a las que se aplicará el formato. La opción
Entre… muestra un cuadro de diálogo
diferente porque solicita 2 valores que
delimitarán el rango de valores a buscar:



MICROSOFT EXCEL - AVANZADO

GUÍA N° 2

3




Área de Servicios

Dpto. de Informática

U.N.S.L.

“MICROSOFT EXCEL 2007 – NIVEL AVANZADO”

La opción Una fecha… muestra una
lista de
opciones de donde se podrá seleccionar la que
mejor se adapte al problema que haya que resolver.
Por supuesto, es necesario que
las celdas
seleccionadas tengan datos de tipo fecha de manera
que esta regla de
formato condicional pueda
encontrar valores coincidentes.


Finalmente la opción Duplicar valores… permitirá
resaltar ya sea los valores únicos ó los valores
duplicados dentro de los datos.
Solamente habrá que elegir la opción adecuada de
la lista de selección y se aplicará la regla de
formato condicional adecuada.



Reglas superiores e inferiores para analizar datos

los

introducido

conceptos
Después de haber
formato condicional, ahora se
fundamentales de
analizará con un poco más de detenimiento algunos
tipos de reglas de formato condicional especiales. Estas
reglas se muestran dentro del menú Formato
condicional y dentro de la opción Reglas superiores e
inferiores.

Reglas superiores e inferiores de formato condicional
La regla 10 superiores… permitirá resaltar de manera inmediata las celdas que contengan los 10
valores con mayor valor dentro del rango de celdas. El cuadro de diálogo de esta regla es el
siguiente:

lugar de

Desde este cuadro de dialogo se podrá cambiar
el número de elementos a seleccionar, por lo que
en
los 10 superiores se podrían
seleccionar los 20 superiores de ser necesario.
La regla 10 inferiores… funciona de manera
similar, solo que Excel obtendrá las celdas con
menor valor dentro del rango.



La regla 10% de valores superiores… es similar a las reglas anteriores, pero en lugar de indicar
un número específico de celdas se introduce un porcentaje de celdas a seleccionar. Por ejemplo,
dentro de un rango que contiene 20 celdas, si se especifica un 10% entonces el formato

MICROSOFT EXCEL - AVANZADO

GUÍA N° 2

4




Área de Servicios

Dpto. de Informática

U.N.S.L.

“MICROSOFT EXCEL 2007 – NIVEL AVANZADO”

condicional se aplicará a las 2 celdas con mayor valor porque el 10% de 20 celdas son 2 celdas.
El cuadro de diálogo es el siguiente:

De la misma manera se podrá aumentar o
disminuir el porcentaje. Si se especifica un 40%
para un rango de 20 celdas, entonces el formato
condicional se aplicará a 8 celdas. La regla 10%
de valores inferiores… selecciona el 10% de
celdas del rango que tenga el menor valor.



Finalmente la regla Por encima del promedio… realiza dos tareas. En primer lugar calculará el
valor promedio de la