Análisis de Datos 1

Vista previa del material en texto

Capítulo 
Análisis de Datos I 
En este capítulo trataremos: 
 Herramientas de datos
 Consolidación y referencias 3D
¿Qué se necesita para ser el mejor? 
Concentración. Disciplina. Una ilusión. 
F. Griffith
Medalla de oro olímpica 
SENATI-Computación e Informática 1 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
Formato como tabla 
Excel es primariamente una hoja de cálculo, pero además de ello tiene capacidad para 
analizar tablas de datos, tales como stock, clientes, planillas, monitorear cuentas y 
ventas. 
 
La combinación de una serie de funciones hace al Excel una excelente herramienta de 
análisis para negocios y administración de sistemas. 
 
Excel usa el término Lista para referirse a información almacenada en filas y columnas, 
si usted está familiarizado con versiones anteriores de Excel, entonces es probable que 
este más familiarizado con el término Base de Datos. 
 
 
 
Lista de datos 
 
 Una lista de datos es un conjunto de registro formado por filas y columnas. 
 
 Las filas representan los registros de datos 
 
 Las columnas representan los campos de datos 
 
 
 
Estructura de una lista de datos 
 
 
Campo1 Campo2 Campo3 Campo4 Campo5 
 
 
 
 
 
 
 
 
 
 
 
Registros 
 
 
 
 
 
 
 
Uso de las tablas de datos 
 
Una vez que se han ingresado los nombres de los campos y la fila de datos iníciales 
a su lista, podemos usar cualquiera de los siguientes métodos para ingresar datos: 
 
Uso de ingreso automático de datos usando la ficha de datos 
 
Ingresar los datos directamente en la hoja dentro de las celdas vacías 
 
 
 
 
 
 
 
2 SENATI-Computación e Informática 
 
 
 
 
 
 
 
 
 
 
 
 
Paso a Paso: Agregar el comando formulario a la barra de herramientas 
de acceso rápido 
 
1. Hacer clic en el botón Botón Office 
 
2. Hacer clic en el botón , se presenta el cuadro de diálogo 
“Opciones Excel”. 
 
 
 
 
 
 
 
Hacer clic en la 
opción Personalizar 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Se presenta el cuadro de diálogo “Personalizar la barra de herramientas de 
acceso rápido”. 
 
 
 
SENATI-Computación e Informática 3 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
Seleccionar “Todos los 
comandos” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Seleccionar 
“Formulario …” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4. Hacer clic en el botón Aceptar para terminar 
 
 
 
Paso a Paso: Agregar datos 
 
1. Abrir el archivo “1 BD Pedidos” 
 
2. Adicionaremos registros a la base de datos de pedidos, la cual mostramos a 
continuación. 
 
 
 
 
4 SENATI-Computación e Informática 
 
 
 
 
 
 
 
 
 
 
 s de I 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Seleccionar al celda A2 
 
4. Hacer clic en el botón Formulario 
 
 
 
 
 
 
 
5. Se presenta la ficha formulario mostrando los registros de la base de datos “1 
BD Pedidos” 
 
 
 
6. Para adicionar registros hacer clic en el botón 
 
7. Se presenta un formulario en blanco, ingrese el siguiente registro 
 
 
 
 
 
SENATI-Computación e Informática 5 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8. Hacer clic en el botón para terminar 
 
 
 
 
 
Paso a Paso: Eliminar datos 
 
1. Abrir el archivo “1 BD Pedidos” 
 
2. Eliminaremos el registro de pedido realizado por el cliente SoftPlus 
 
3. Seleccionar al celda A2 
 
4. Hacer clic en el botón Formulario 
 
 
 
 
 
 
 
5. Se presenta la ficha formulario mostrando los registros de la base de datos “1 
BD Pedidos” 
 
 
 
 
 
6 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
6. Primero localizaremos el registro, para ello hacer clic en el botón 
 
 
 
 
 
 
 
 
Ingrese “SoftPlus”, 
nombre del cliente a 
localizar 
 
 
 
 
 
 
 
 
 
Hacer clic en el botón 
“Buscar siguiente” 
 
 
 
7. Se mostrará el registro de pedido de la empresa SoftPlus 
 
 
 
8. Hacer clic en el botón , Excel preguntará si desea eliminar 
permanentemente este registro. 
 
 
 
SENATI-Computación e Informática 7 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
9. Hacer clic en el botón 
 
10. Hacer clic en el botón para terminar 
 
 
 
Ordenar datos 
 
La ordenación de los registros nos permite ver y comprender mejor los datos, así como a 
organizarlos y encontrarlos más fácilmente y a tomar decisiones más eficaces. 
 
Se puede ordenar los registros por: Texto, números, fechas u horas, color de celda, color 
de fuente o icono, por una lista personalizada, filas, por más de una columna o fila, 
ordenar una columna en un rango de celdas sin afectar a las demás. 
 
 
 
 
 
Paso a Paso: Ordenar por un campo texto 
 
1. Abrir el archivo “1 BD Pedidos” 
 
 
 
2. Ordenaremos por el campo de datos “Cliente” alfabéticamente en orden 
ascendente 
 
3. Hacer clic en la celda A2 
 
 
4. 
 
Hacer Clic en la ficha 
 
, desplace su visión al grupo Ordenar y filtrar 
 
 
Hacer clic en el botón 
“Ordenar de A a Z” 
 
 
 
5. Se mostrarán sus registros ordenados por el campo cliente 
 
 
 
 
 
8 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
, 
 
. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
I 
 
Paso a Paso: Ordenar por un campo texto distinguiendo las mayúsculas 
de las minúsculas 
 
1. Abrir el archivo “1 BD Pedidos” 
 
 
 
2. Ordenaremos por el campo de datos “NombreProducto” alfabéticamente en 
 orden ascendente 
3. Hacer clic en la celda A2 
 
 
4. 
 
Hacer clic en la ficha 
 
desplace su visión al grupo Ordenar y filtrar 
 
 
 
 
Hacer clic en 
el botón 
“Ordenar” 
 
 
 
 
5. Se muestra el siguiente cuadro de diálogo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6. Hacer clic en el botón 
 
7. Del cuadro de diálogo presentado activar 
la casilla de verificación “Distinguir 
mayúsculas de minúscula” 
 
 
 
 
 
 
 
 
SENATI-Computación e Informática 9 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
8. Hacer clic en el botón para continuar. Se mostrará el cuadro de 
diálogo ordenar 
 
 
 
 
 
 
 
 
 
 
 
 
Seleccionar columna 
“NombreProducto” 
 
 
 
 
 
 
 
 
9. Hacer clic en el botón para terminar. Se mostrarán los registros 
ordenados por el campo NombreProducto, distinguiendo las mayúsculas de las 
minúsculas. 
 
 
 
 
 
 
 
Paso a Paso: Ordenar por un campo numérico 
 
1. Abrir el archivo “1 BD Pedidos” 
 
 
 
2. Ordenaremos por el campo de datos “Cantidad” en orden descendente 
 
3. Hacer clic en la celda A2 
 
 
4. 
 
Hacer clic en la ficha 
 
desplace su visión al grupo Ordenar y filtrar 
10 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic en el botón 
“Ordenar” 
 
 
 
5. Se presenta el siguiente cuadro de diálogo 
 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic para 
seleccionar la 
columna “Cantidad” 
Seleccionar como 
criterio de ordenación 
“De mayor a menor” 
 
 
 
 
 
 
6. Hacer clic en el botón para terminar, se mostrarán los 
registros ordenados por el campo cantidad 
 
 
 
 
 
 
Paso a Paso: Ordenar por fecha u hora 
 
1. Abrir el archivo “1 BD Pedidos” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
SENATI-Computación e Informática 11 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
2. Ordenaremos por el campo de datos “Fecha Pedido” en orden descendente 
 
3. Hacer clic en la celda A2 
 
 
4. 
 
Hacer clic en la ficha 
 
desplace su visión al grupo Ordenar y filtrar 
 
 
 
Hacer clic en el botón 
“Ordenar” 
 
 
 
 
5. Se presenta el siguiente cuadro de diálogo 
 
 
 
 
 
 
 
 
 
 
 
 
 
Seleccionar la 
columna 
“FechaPedido” 
Seleccione su criterio 
de ordenación 
 
 
 
 
 
 
 
6. Para terminar hacer clic enel botón se muestran los registros 
ordenados por fecha de pedido 
 
 
 
 
 
Ordenar por color de celda, color de fuente o icono 
 
Si ha aplicado formato manual o condicionalmente a un rango de celdas o a 
una columna de tabla, por color de celda o color de fuente, también puede 
ordenar por estos colores. Además, puede ordenar por un conjunto de iconos 
creado mediante un formato condicional. 
 
 
 
12 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Paso a Paso: Ordenar por más de una columna 
 
1. Abrir el archivo “1 BD Pedidos” 
 
 
 
2. Ordenaremos por el campo: cliente, fechapedido, nombreproducto en orden 
descendente 
 
3. Hacer clic en la celda A2 
 
 
4. 
 
Hacer clic en la ficha 
 
desplace su visión al grupo Ordenar y filtrar 
 
 
 
Hacer clic en el botón 
“Ordenar” 
 
 
 
 
5. Se presenta el siguiente cuadro de diálogo 
 
 
Seleccionar columna “Cliente” como 
primer criterio de ordenación 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Seleccionar columna 
“NombreProducto” como tercer 
criterio de ordenación 
Seleccionar columna 
“FechaPedido” como segundo 
criterio de ordenación 
 
 
 
 
6. Hacer clic en el botón para terminar. Se muestran los registros 
ordenados por cliente, fechapedido, nombreproducto en orden descendente. 
 
 
 
SENATI-Computación e Informática 13 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Filtro de datos 
El filtrado de datos constituye un método fácil y rápido para encontrar subconjuntos de 
datos en una lista y trabajar con ellos. 
 
Cuando se filtra una lista sólo visualizará las filas que cumplen un conjunto de 
condiciones de búsqueda llamado criterios. 
 
A diferencia de la ordenación, la filtración no reorganiza las listas. La filtración oculta 
provisionalmente las filas que no desea mostrar. 
 
Cuando Excel filtra las filas, la hoja de cálculo se coloca en el modo de filtración. En 
este modo se podrá editar, dar formato, efectuar representaciones gráficas e imprimir la 
lista de subconjuntos sin tener que reorganizarla o moverla. 
 
 
 
Paso a Paso: Aplicar Autofiltros 
 
1. Abrir el archivo “FILTROS” 
 
2. Utilice la siguiente lista de datos para filtrar registros por sección. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Ubicarse en la celda A5. 
 
4. Hacer clic en la ficha botón 
 
5. Ahora simplemente con hacer clic en la lista desplegable podrá filtrar los 
registros de datos según sus requerimientos. 
 
 
14 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Desactive las 
casillas del 2do. 
Al 6to. grado 
 
 
 
 
 
 
 
 
 
 
 
 
 
6. Hacer clic en el autofiltro grado y desactive las casillas del 2do. Al 6to. Grado, 
de tal forma que sólo se muestre alumnos del 1er. Grado. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7. Los registros filtrados se muestran como en la gráfica. 
 
 
 
 
 
 
SENATI-Computación e Informática 15 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
Filtro personalizado 
 
Se utiliza para especificar condiciones utilizando operadores booleanos. 
 
 
 
Paso a Paso: Filtros personalizados 
 
1. Abrir el archivo “FILTROS” o diseñar la hoja de cálculo 
 
Utilice la siguiente lista de datos para filtrar registros donde el monto de la 
pensión está entre: 200 y 350. 
 
 
 
a. Ubicarse en la celda A5. 
b. Hacer clic en la ficha , botón , , 
 
 
c. De la lista de opciones presentadas elegir 
 
La opción: Mayor o igual a… 
 
Se presenta el siguiente cuadro de diálogo 
 
2. Ingrese los valores según se muestra en la gráfica, para que sólo se muestre los 
alumnos que pagan una pensión que está entre 200 y 350 nuevos soles. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Hacer clic en el botón para aplicar el filtro. 
 
 
 
16 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Pensiones de alumnos 
con valor entre 200 y 350. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Filtro múltiple 
 
Se utiliza para especificar múltiples condiciones 
 
 
 
Paso a Paso: Filtros múltiple 
 
1. Abrir el archivo “FILTROS” 
 
Utilice la siguiente lista de datos para filtrar registros donde: 
Procedencia: CIV 
Nivel: P 
Grado: 1 
 
 
 
2. Ubicarse en la celda A5. 
 
 
3. 
 
Hacer clic en la ficha 
 
botón 
 
4. De la lista de opciones presentadas elegir 
 
Procedencia: CIV 
Nivel: P 
Grado: 1 
 
 
 
 
SENATI-Computación e Informática 17 
 
, 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
Hacer clic y dejar sólo activado la 
casilla de verificación P 
 
 
Hacer clic y dejar sólo activado la 
casilla de verificación CIV 
Hacer clic y dejar sólo activado la 
casilla de verificación 1 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Quitar un Filtro 
 
Para mostrar en su tabla todos los registros, debe quitar los filtros aplicados. 
 
 
 Paso a Paso: Quitar filtros
 
Hacer clic en la ficha botón 
 
 
 
Utilizar estilos rápidos y crear estilos de formato de tabla 
 
Microsoft Office Excel proporciona un gran número de estilos de tabla (o estilos 
rápidos) predefinidos que puede utilizar para dar formato rápidamente a una tabla. Si los 
estilos de la tabla predefinida no satisfacen sus necesidades, puede crear y aplicar un 
estilo de tabla personalizado. Aunque sólo se pueden eliminar los estilos de tabla 
personalizados, puede quitar cualquier estilo de tabla para que ya no se aplique a los 
datos. 
 
 
 
 
Paso a Paso: Utilizar estilos rápidos de tabla 
 
1. Abrir el archivo “1BD pedidos”. Se muestra la siguiente hoja de cálculo 
 
 
 
18 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2. Hacer clic en la celda A2 
 
3. Hacer clic en la ficha 
 
4. Del grupo Estilos seleccionar el comando “Dar formato como tabla” 
 
 
 
 
 
 
 
 
5. Se presenta un conjunto de estilos prediseñados, categorizados en: Claro, 
medio, oscuro. 
 
 
 
 
 
 
 
 
 
 
Seleccionar uno 
de los estilos 
mostrados 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6. Luego de elegir un estilo se presenta un cuadro de diálogo 
 
 
 
SENATI-Computación e Informática 19 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7. Verifique que el rango seleccionado es el correcto, active la casilla de 
verificación “La tabla tiene encabezados”. 
 
8. Finalmente hacer clic en el botón se muestra la tabla con formato 
 
 
 
 
 
 
Paso a Paso: Crear estilos de formato de tabla 
 
1. Abrir el archivo “1BD pedidos”. Se muestra la siguiente hoja de cálculo 
 
 
 
2. Hacer clic en la celda A2 
 
3. Hacer clic en la ficha 
 
4. Del grupo Estilos seleccionar el comando “Dar formato como tabla” 
 
 
 
20 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5. Se presenta un conjunto de estilos prediseñados, categorizados en: Claro, 
medio, oscuro y al final se presentan dos botones de comando que mostramos 
a continuación. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic en el 
botón “Nuevo 
estilo de tabla …” 
 
 
 
6. Se muestra el cuadro de diálogo Nuevo estilo rápido de tabla 
 
 
Escriba la palabra “Pedidos” 
como nombre de estilo 
 
 
 
 
 
 
Aplique los formatos a cada 
uno de los elementos de la 
tabla 
 
 
 
 
 
 
 
 
Hacer clic en el 
botón “Formato” 
 
 
 
 
 
 
 
 
7. Se presenta el cuadro de diálogo formato de celda 
 
 
 
SENATI-Computación e Informática 21 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007Hacer clic en la 
ficha “Bordes” 
Elegir donde 
aplicará los 
bordes 
 
 
 
 
 
 
 
 
 
Elegir el estilo 
de línea 
 
Elegir color 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic para 
cambiar a la ficha 
“Relleno” 
 
 
 
 
 
 
Elegir color de 
fondo 
 
 
 
 
 
 
 
Elegir efecto de 
relleno 
 
 
 
 
 
 
 
 
 
8. Para terminar hacer clic en el botón 
 
 
22 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Paso a Paso: Modificar estilo rápido de la tabla de datos 
 
1. Abrir el archivo “1BD pedidos”. Se muestra la siguiente hoja de cálculo 
 
 
 
2. Hacer clic en la celda A2 
 
3. Hacer clic en la ficha 
 
4. Del grupo Estilos seleccionar el comando “Dar formato como tabla” 
 
 
 
 
 
 
 
 
 
5. Del grupo de opciones “Personalizada” hacer clic derecho sobre la que desea 
modificar 
 
 
 
Hacer clic derecho 
 
 
 
 
 
6. Del grupo de opciones presentadas elegir “Modificar” 
 
 
 
 
 
 
 
 
 
 
Hacer clic sobre la 
opción “Modificar” 
 
 
 
 
 
 
7. Realizar los cambios necesarios 
 
 
 
SENATI-Computación e Informática 23 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
 
 
 
 
 
Elegir “Primera 
franja de fila” 
 
 
 
 
 
 
 
 
 
Hacer clic sobre la 
opción “Formato” 
 
 
 
 
 
 
 
 
8. Del cuadro de diálogo “Formato de celdas”, elegir la ficha “Relleno” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Seleccionar 
color de fondo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
24 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9. Hacer clic en el botón para terminar y veamos cómo 
queda la tabla. Se muestra con colores intercalados entre fila y fila 
 
 
 
10. Repita el procedimiento para modificar cada uno de los elementos de la tabla 
 
 
 
 
Seleccione una de estas 
opciones, luego hacer clic en 
 
el botón para 
modificar cada uno de los 
elementos de la tabla 
 
 
 
 
Herramientas de datos 
Excel presenta un conjunto de herramientas de datos entre las cuales tenemos: Texto en 
columnas, validación de datos, análisis Y si. Las cuales describiremos a continuación. 
 
 
 
Texto en columnas 
 
Si copia datos de otro programa y lo pegarlo en Microsoft Excel, Excel puede 
comprimir varias columnas de datos a una sola columna. Puede utilizar el comando de 
texto en columnas para colocar cada una de las columnas de datos en una celda 
(Columna independiente) 
 
 
 
Dividir el contenido en función de un delimitador 
 
Utilice este método si los nombres tienen un formato delimitado, como "Nombre 
Apellido" (donde el espacio entre Nombre y Apellido es el delimitador) o 
"Apellido, Nombre" (donde la coma es el delimitador). 
 
 
 
 
Paso a Paso: Convertir texto en columnas separado por comas 
 
1. Abrir el archivo “Texto en columnas”. Se muestra la siguiente hoja de cálculo 
 
 
 
SENATI-Computación e Informática 25 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
 
 
 
 
 
 
 
2. Seleccione el bloque de celdas A1:A4. Hacer clic en la ficha y 
visualizar el grupo herramienta de datos. 
 
 
Hacer clic sobre la 
herramienta “Texto en 
columnas” 
 
 
 
3. Se presenta el asistente para convertir texto en columnas 
 
 
 
 
 
 
 
 
 
 
 
Debido a que el 
texto se separa 
con comas, elegir 
la opción 
“Delimitados” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4. Hacer clic en el botón 
 
5. En el siguiente cuadro de diálogo se preguntará que separador utilizará entre los 
siguientes: Tabulación, punto y coma, coma, espacio, otro. 
 
Elegir la opción “coma” 
 
 
 
 
 
26 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Elegir coma 
como separador 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6. Hacer clic en el botón 
 
 
 
 
 
 
 
 
Elegir 
“Texto” 
 
 
 
 
Celda a partir de donde 
se colocaran los datos 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7. Hacer clic en el botón para terminar, su hoja queda como se 
muestra a continuación 
 
 
 
SENATI-Computación e Informática 27 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
El texto de dividió en dos 
columnas, una para el 
nombre y la otra para el 
apellido. Se utilizo como 
delimitador de división la 
coma. 
 
 
8. Grabar el archivo con el nombre “Texto en columnas dividido 1” 
 
 
 
 
Paso a Paso: Convertir texto en columnas separado por espacios 
 
1. Abrir el archivo “Texto en columnas”. Se muestra la siguiente hoja de cálculo 
 
 
 
2. Seleccione el bloque de celdas A1:A4. Hacer clic en la ficha y 
visualizar el grupo herramienta de datos. 
 
 
 
Hacer clic sobre la 
herramienta “Texto 
en columnas” 
 
 
3. Se presenta el asistente para convertir texto en columnas 
 
 
 
 
 
 
 
 
 
 
Debido a que el 
texto se separa con 
comas, elegir la 
opción 
“Delimitados” 
 
 
 
 
 
 
 
 
 
 
 
 
 
28 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4. Hacer clic en el botón 
 
5. En el siguiente cuadro de diálogo se preguntará que separador utilizará entre los 
siguientes: Tabulación, punto y coma, coma, espacio, otro. 
 
Elegir la opción “Espacio” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Elegir “Espacio” 
como separador 
 
 
 
 
 
 
 
 
 
 
6. Hacer clic en el botón 
 
 
 
 
 
 
 
 
Elegir 
“Texto” 
 
 
 
Celda a partir de 
donde se colocaran 
los datos 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
SENATI-Computación e Informática 29 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
7. Hacer clic en el botón para terminar, su hoja queda como se 
muestra a continuación 
El texto de dividió en seis columnas 
 
 
 
 
 
 
 
 
 
 
8. Grabar el archivo con el nombre “Texto en columnas dividido 2” 
 
 
 
Quitar duplicados 
 
Es posible eliminar valores duplicados de una lista utilizando la herramienta quitar 
duplicados. 
 
 
 
 
Paso a Paso: Quitar duplicados 
 
1. Abrir el archivo “1 Quitar duplicados”. Se muestra la siguiente hoja de 
cálculo 
 
 
 
2. Ordenar la lista por el campo que desea eliminar los datos duplicados 
 
3. Hacer clic en la ficha 
 
 
 
Hacer clic en 
el botón 
Ordenar 
 
 
 
30 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4. Se presenta el cuadro de diálogo ordenar 
 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic para seleccionar 
Nombre como columna a 
ordenar 
Hacer clic para activar la 
casilla indicando que si 
contamos con 
encabezados 
 
 
 
 
 
 
5. Hacer clic en el botón “Aceptar” para terminar con la ordenación 
 
6. Hacer clic en la ficha 
 
 
 
Hacer clic en el botón 
“Quitar duplicados 
 
 
 
7. Se presenta el cuadro de diálogo “Quitar duplicados” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic para indicar que 
se eliminan duplicados de 
la columna “Nombre” 
Hacer clic para activar 
la casilla “Mis datos 
tienen encabezados” 
 
 
 
 
8. Hacer clic en el botón para terminar. Excel envía un mensaje 
que indica que se eliminaron 4 valores duplicados 
 
 
 
 
SENATI-Computación e Informática 31 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
 
 
 
 
 
 
9. Su tabla queda como se muestra a continuación. 
 
 
 
 
 
 
Validación de datos 
Si desea asegurarse de que se introducen los datos correctos en una hoja de cálculo, 
puede especificar qué datos son válidos para cada celda o cada rango de celdas. Puede 
restringir los datos a un tipo determinado (como números enteros, números decimales o 
texto) y definir límites en las entradas válidas. Puede especificar una lista de entradas 
válidas o limitar el número de caracteres en las entradas. 
 
 
 
Restringir el ingreso de datos 
 
Cuando quiera validar una celdao un conjunto de celdas tendrá que establecer un 
criterio para la validación de datos, especificar un mensaje de entrada de datos con este 
podrá indicar que tipo de datos se podrá ingresar y un mensaje de error, para indicar al 
usuario que cometió un error de ingreso de datos. 
 
Validar con intervalo de números 
 
Se puede restringir el ingreso de datos a las celdas, de tal forma que solamente acepte 
como datos un intervalo numérico. 
 
 
 
 
 
Paso a Paso: Validar intervalo numérico 
 
1. Abrir el archivo “1 Validar datos” 
 
 
 
32 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2. Seleccionar las celdas C4:C13 para agregarle una restricción, que acepte sólo 
como datos los números: 1 hasta 120. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Elegir la ficha 
 
 
 
 
Hacer clic en la 
ficha 
“Configuración” 
 
 
 
Elegir “Números 
enteros” 
 
 
 
Elegir “Entre” 
 
Ingresar “1” como mínimo y 
“120” como máximo. 
 
 
 
 
 
 
 
SENATI-Computación e Informática 33 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
a. Seleccionar la opción Permitir: “Número entero” 
 
b. Datos: Entre 
 
c. Mínimo: 1 Máximo: 120 
 
4. Cambiar a la pestaña Mensaje de entrada, para definir un mensaje que se 
mostrará cuando intente ingresar un dato. 
 
 
 
 
 
Hacer clic en la ficha 
“Mensaje de entrada” 
 
 
 
 
Escribir como título “Edad 1 a 
120” 
 
 
 
 
Escribir mensaje “Ingrese un 
número entre 1 y 120” 
 
 
 
 
 
 
 
5. Finalmente hacer un clic en el botón 
 
Ahora a propósito ingrese valor menos a 1 o mayores a 120 y observe lo que 
sucede. 
 
 
 
Paso a Paso: Validar dato fecha 
 
1. Abrir el archivo “1 Validar datos” 
 
 
 
 
 
 
34 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
, 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2. Seleccionar las celdas E4:E13 para agregarle una restricción, que acepte sólo 
como datos fechas: entre 1/1/1965 hasta 1/1/1980 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Elegir la ficha 
 
 
 
 
 
Hacer clic en la ficha 
configuración 
 
 
 
 
Elegir “Fecha” 
 
 
 
Elegir “Entre” 
 
Ingresar “1/1/1965” como mínimo y 
“1/1/1980” como máximo. 
 
 
 
 
 
 
 
4. Cambiar a la pestaña Mensaje de entrada, para definir un mensaje que se 
mostrará cuando intente ingresar un dato. 
 
 
 
 
 
 
 
Hacer clic en la ficha 
“Mensaje de entrada” 
 
 
 
SENATI-Computación e Informática 35 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
Escribir título “1/1/1965 
a 1/1/1980” 
 
 
 
 
 
 
Escribir mensaje 
 
 
 
 
 
 
 
5. Finalmente hacer un clic en el botón 
 
 
 
 
 
Paso a Paso: Eliminar una regla de validación 
 
1. Seleccionar las celdas que desee eliminar la validación de datos. 
 
 
2. Hacer clic en la ficha Hacer clic en la lista 
“Validación de datos” 
 
 
 
 
 
 
Hacer clic en el botón 
“Validación de datos …” 
 
 
 
 
 
36 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3. Del cuadro de diálogo “Validación de datos” hacer clic en el botón 
 
 
 
 
 
Paso a Paso: Rodear con círculo datos no válidos 
 
 
1. Hacer clic en la ficha Hacer clic en la lista 
“Validación de datos” 
 
 
 
 
 
 
 
 
 
 
Hacer clic en el botón 
“Rodear con un círculo 
datos no válidos” 
 
 
 
 
 
Paso a Paso: Borrar círculos de validación 
 
1. Hacer clic en la ficha 
 
 
 
Hacer clic en la lista 
“Validación de datos” 
 
 
 
 
 
 
 
 
 
 
Hacer clic en el botón 
“Borrar círculos de 
validación” 
 
 
 
Análisis Y si 
Permite crear escenarios para realizar predicciones. Por ejemplo, puede realizar análisis 
y si para crear dos presupuestos donde en cada uno de ellos se supone un cierto grado de 
ingresos. O, puede especificar un resultado que desea que genere una fórmula y, a 
continuación, determinar qué conjuntos de valores generarán dicho resultado. 
 
Excel proporciona varias herramientas diferentes para ayudar a realizar el tipo de 
análisis que se ajuste a sus necesidades. 
 
 
 
 
SENATI-Computación e Informática 37 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
Uso del administrador de escenarios 
 
Excel es ideal para el análisis Y-Si. Ud. Puede ingresar valores dentro de las celdas y 
observar que pasa dependiendo de su contenido. 
 
Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir 
automáticamente en la hoja de cálculo. Puede utilizar los escenarios para prever el 
resultado de un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de 
valores en una hoja de cálculo y, a continuación, pasar a cualquiera de estos nuevos 
escenarios para ver distintos resultados. 
 
 
 
Componentes de un escenario 
 
Un Modelo con Escenarios nombrados debe tener: 
 
- Un grupo claro de uno o más valores de entrada 
 
- Un grupo claro de uno o más valores resultantes que deberán cambiar basado 
en las entradas. 
 
 
 
Paso a Paso: Crear escenarios 
 
1. Abrir el archivo “1 Escenarios” 
 
 
 
2. Crear tres escenarios: Mejor Opción, Caso Optimista y el Caso Pesimista. 
 
3. Hacer clic en la ficha 
 
 
 
38 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic en el botón 
análisis Y si. 
 
 
4. De las opciones presentadas elegir “Administrador de escenarios” 
 
 
 
Hacer clic en el botón 
“Administrador de escenarios” 
 
 
5. Se presenta el cuadro de diálogo “Administrador de escenarios” 
 
 
 
6. Hacer clic en el botón 
 
 
 
 
Escriba “Mejor 
opción” 
 
 
 
 
 
 
Seleccione el bloque 
de celdas B15:B18 
 
 
 
 
Hacer clic en el 
botón “Aceptar” 
 
 
 
 
 
7. Del cuadro de diálogo valores del escenario especifique según la gráfica 
mostrada 
 
 
 
SENATI-Computación e Informática 39 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
Valores del escenario 
“Mejor opción” 
 
 
 
 
 
 
 
 
 
8. Hacer clic en el botón para adicionar los escenarios restantes. 
 
9. Se presenta el cuadro de diálogo “Modificar escenario” 
 
 
Escriba “caso optimista” 
como nombre de escenario 
 
 
 
Escriba B15:B18 como 
celdas cambiantes 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Del cuadro de diálogo valores del escenario especifique según la gráfica 
mostrada 
 
Hacer clic en el 
botón “Aceptar” 
 
 
 
 
 
Valores del escenario 
“Caso optimista” 
 
 
 
 
 
 
 
 
10. Hacer clic en el botón para adicionar los escenarios restantes. 
 
 
40 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Escriba “caso Pesimista” 
como nombre de escenario 
 
 
 
Escriba B15:B18 como 
celdas cambiantes 
 
 
 
 
 
 
Hacer clic en el 
botón “Aceptar” 
 
 
 
 
 
 
 
 
11. Del cuadro de diálogo valores del escenario especifique según la gráfica 
mostrada 
 
 
 
 
 
 
Valores del escenario 
“Caso Pesimista” 
 
 
 
 
 
 
 
 
12. Hacer clic en el botón para terminar 
 
13. Se presenta el cuadro de diálogo administrador de escenarios. 
 
14. Guardar el archivo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
SENATI-Computación e Informática 41 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
Paso a Paso: Mostrar escenarios 
 
1. Abrir el archivo “1 Escenarios” 
 
2. Hacer clic en la ficha 
 
 
 
 
 
Hacer clic en el botón 
análisis Y si. 
 
3. De las opciones presentadas elegir “Administrador de escenarios” 
 
 
 
Hacer clic en el botón 
“Administrador de escenarios” 
 
 
4. Se presenta el cuadro de diálogo “Administrador de escenarios” 
 
5. Seleccione el escenario a mostrar,luego hacer clic en el botón 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Seleccione escenario a mostrar, 
luego hacer clic en el botón 
 
 
 
 
 
 
 
 
 
Paso a Paso: Modificar un Escenario 
 
1. Abrir el archivo “1 Escenarios” 
 
2. Hacer clic en la ficha 
 
 
42 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Hacer clic en el botón 
análisis Y si. 
 
 
 
3. De las opciones presentadas elegir “Administrador de escenarios” 
 
 
 
Hacer clic en el botón 
“Administrador de escenarios” 
 
 
4. Se presenta el cuadro de diálogo “Administrador de escenarios” 
5. Seleccione el escenario a modificar, luego hacer clic en el botón 
 
6. Se presenta el cuadro de diálogo “Modificar escenario” 
 
 
7. Hacer clic en el botón 
8. Se presenta el cuadro de diálogo “Valores dele escenario” 
 
 
 
 
 
 
 
Escriba los nuevos valores 
de su escenario 
 
 
 
 
 
 
 
9. Hacer clic en el botón para terminar 
 
 
 
SENATI-Computación e Informática 43 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
Buscar Objetivo 
 
En el caso de que conozca el resultado deseado de una fórmula sencilla, pero no la 
variable que determina el resultado, podrá utilizar la función Buscar objetivo. Al 
realizar una búsqueda de objetivo, Microsoft Excel varía el valor de celda 
específica hasta que una fórmula dependiente de dicha celda devuelve el resultado 
deseado. 
 
 
 
Paso a Paso: Modificar un escenarios 
 
1. Diseñar la siguiente hoja de cálculo 
 
 
 
En el ejemplo se muestra el calculo de pago de un préstamo en un periodo de 
90 meses a una tasa de interés del 14%. Se utiliza la función =Pago 
 
2. Hacer clic en la ficha 
 
 
 
Hacer clic en el 
botón análisis Y si. 
 
 
 
3. De las opciones presentadas elegir “Buscar objetivo …” 
 
 
 
Hacer clic en el botón 
“Buscar objetivo” 
 
 
4. Se presenta el cuadro de diálogo “Buscar objetivo” 
 
Queremos conocer cuánto de interés se debe pagar si queremos desembolsar 
2200 mensual, para pagar los 50,000 del préstamo 
 
 
Fórmula que 
calcula el pago 
mensual Valor que 
queremos pagar 
mensualmente 
 
 
 
Valor a 
localizar 
 
 
44 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5. Hacer clic en el botón , se muestra el estado de la búsqueda de 
objetivo. 
 
 
 
6. Hacer clic en el botón , para terminar 
 
 
Tabla de datos 
 
Una tabla de datos es un rango de celdas que muestra cómo afecta el cambio de 
algunos valores de las fórmulas a los resultados de las mismas. 
 
Las tablas de datos constituyen un método abreviado para calcular varias 
versiones en una sola operación, así como una manera de ver y comparar los 
resultados de todas las variaciones distintas en la hoja de cálculo. 
 
 
 
Tabla de datos de una variable 
 
Entre uno de los mejores ejemplos de análisis sensitivo, esta una tabla de datos 
que calcula el pago de préstamo para diferentes tasas de interés. 
 
La tabla de datos de ingreso simple descrita en esta sección crea un cuadro de 
pagos mensuales para una serie de tasas de interés. 
 
 
 
 
 
Paso a Paso: Tabla de datos de una variable 
 
1. Diseñar la siguiente hoja de cálculo 
 
 
 
 
Escribir los datos 
según se muestra 
 
 
 
 
 
 
En el ejemplo se muestra el calculo de pago de un préstamo de 20,000 en un 
periodo de 20 años a una tasa de interés del 25%. Se utiliza la función =Pago 
 
 
 
 
 
SENATI-Computación e Informática 45 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
2. Diseñar un cuadro que permita conocer cuánto pagaremos si la tasa de interés 
varía entre 26% y 35%. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Escriba esta fórmula 
 
=PAGO(B4/12;B5*12;B3) 
 
 
 
 
Editar la tabla de tasa de 
interés 
 
 
 
 
 
 
 
3. Seleccionar el bloque de celdas A8:B18 (Tabla de interés) 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Seleccionar 
celdas A8:B18 
 
 
 
 
 
 
 
 
 
 
 
 
46 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4. Hacer clic en la ficha 
 
 
 
Hacer clic en el 
botón análisis Y si. 
 
 
 
5. De las opciones presentadas elegir “Buscar objetivo …” 
 
 
 
Hacer clic en el botón 
“Tabla de datos” 
 
 
6. Se muestra el cuadro de diálogo tabla de datos 
 
7. En el campo: Celda de entrada (Columna) escribir B4 que representa la tasa 
de interés del cuadro de préstamo. 
 
 
 
8. Hacer clic en el botón para terminar, se debe mostrar el siguiente 
resultado 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Montos que se deben 
pagar según la tasa de 
interés asociada 
 
 
 
 
 
 
 
 
 
 
 
 
SENATI-Computación e Informática 47 
 
 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
Tablas de datos de dos variables 
 
Continuando con nuestro ejemplo anterior, como resolveríamos para analizar 
cuanto pagaríamos, según un rango de tasas de interés y un rango de montos 
prestados. 
 
 
 
 
 
Paso a Paso: Tabla de datos de dos variable 
 
1. Modificar su hoja como se muetsra en la gráfica 
 
 
 
 
 
Agregar el siguiente 
cuadro a su hoja 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2. Seleccionar el bloque de celdas A8:G18 (Matriz: interés - monto) 
 
3. Hacer clic en la ficha 
 
 
 
Hacer clic en el 
botón análisis Y si. 
 
 
 
4. De las opciones presentadas elegir “Buscar objetivo …” 
 
 
 
Hacer clic en el botón 
“Tabla de datos” 
 
 
 
 
48 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5. Se muestra el cuadro de diálogo tabla de datos 
 
6. En el campo: Celda de entrada (Columna) escribir B4 que representa la tasa 
de interés del cuadro de préstamo. En el cuadro celda de entrada (fila) escribir 
b3 que representa al monto prestado. 
 
 
 
Monto prestado 
 
 
 
 
Tasa de interés 
 
 
 
 
7. Hacer clic en el botón para terminar, se debe mostrar el siguiente 
resultado 
 
 
 
 
 
 
 
 
Montos prestados 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Tasas 
de 
interés Monto a pagar 
mensual 
 
 
 
 
Consolidación de datos y Referencia 3D 
Cuando consolidar datos de varias hojas 
 
Cuando se tenga listas de datos con información semejante una de otra, pero escrita en 
cuadros diferentes ya sea en la misma hoja o en hojas distintas, entonces se puede hacer 
uso del menú Datos/Consolidar. Esta opción se utiliza para obtener diversos tipos de 
cálculo estadístico (suma, promedio, máximo, mínimo, varianza, etc.) en base a la 
información guardada en todos estos cuadros. 
 
 
 
 
SENATI-Computación e Informática 49 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
Si desea... 
 
Entonces… 
 
Organizar los datos de todas las hoja de cálculo en orden 
y ubicación idénticos. 
 
Consolide por posición 
 
Organizar los datos de forma diferente en las hoja de 
cálculo independientes pero utilizar los mismos rótulos 
de fila y de columna para que la hoja de cálculo maestra 
pueda hacer coincidir los datos. 
 
Consolide por categorías 
 
Utilizar fórmulas con referencias de celdas o referencias 
3D a otras hojas de cálculo que esté combinando porque 
no tiene una posición o categoría coherente en la que 
basarse. 
 
Consolide por fórmula 
 
 
 
Consolide por fórmula 
 
En la hoja de cálculo maestra, copie o escriba los rótulos de columna o fila que 
desee para los datos de consolidación. 
 
a. Haga clic en la celda en que desea incluir los datos de consolidación. 
 
b. Escriba una fórmula que incluya una referencia de celda a las celdas de 
origen de cada hoja de cálculo o una referencia 3D que contenga los 
datos que desea consolidar. En cuanto a las referencias de celda, siga uno 
de losprocedimientos siguientes: 
 
c. Si los datos que se van a consolidar están en celdas diferentes de 
otras hoja de cálculo 
 
d. Escriba una fórmula con referencias de celda a las otras hojas de cálculo, 
una por cada hoja de cálculo independiente. 
 
Por ejemplo, para consolidar datos de hojas de cálculo 
denominadas Ventas (en la celda B4), HR (en la celda F5) y 
Marketing (en la celda B9), en la celda A2 de la hoja de cálculo 
maestra, tendría que escribir lo siguiente: 
 
 
 
e. Para especificar una referencia de celda como Ventas3!B4 en una 
fórmula sin escribir, escriba la fórmula hasta el punto en el que necesite 
la referencia, haga clic en la etiqueta de la hoja de cálculo y, a 
continuación, haga clic en la celda. 
 
f. Si los datos que se van a consolidar están en las mismas celdas de 
otras hojas de cálculo 
 
 
50 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
g. Escriba una fórmula con una referencia 3D que utilice una referencia a 
un rango de nombres de hojas de cálculo. 
 
Por ejemplo, para consolidar datos en las celdas A2 desde Ventas hasta 
Marketing inclusive, en la celda A2 de la hoja de cálculo maestra tendría 
que escribir lo siguiente: 
 
 
 
 
 
 
 
Paso a Paso: Consolidación de datos 
 
1. Diseñar las siguientes 4 hojas de cálculo o abrir el archivo “CONSOLIDADO” 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
SENATI-Computación e Informática 51 
 
, 
 
 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2. Se quiere consolidar las ventas de las sucursales de: Miraflores, Surco y San 
 
Borja en la hoja de Totales. 
3. Hacer clic en la ficha 
 
 
 
 
 
 
 
 
 
 
 
 
4. Del cuadro de diálogo presentado realizar las siguientes acciones 
 
 
 
Elegir la 
función Suma 
Adicionar estas tres 
referencias y lic en 
el botón “Agregar”. 
 
 
 
 
 
 
 
Activar las casillas 
de verificación 
 
 
 
 
 
 
 
 
 
 
 
La casilla de verificación “Crear vínculos con los datos de origen” permiten que 
la hoja de totales se actualice, cuando realice cambios en las hojas orígenes. 
 
 
 
 
52 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5. Finalmente hacer clic en el botón , se obtiene el siguiente 
resultado. La consolidación del as ventas de las tres sucursales. 
 
 
 
 
 
Paso a Paso: Consolidación de datos 
 
1. Ingresar la información de acuerdo al diseño sugerido 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
SENATI-Computación e Informática 53 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
2. Cambiar el nombre de las hojas: 
 
Hoja1 por BAL2005 
 
Hoja2 por BAL2006 
 
Hoja3 por BAL2007 y 
 
Hoja4 por CONSOLIDADO. 
 
Seleccionar el área de valores numéricos a consolidar y asignarle los 
nombres de campo: TBAL2005, TBAL2006 y TBAL2007. 
 
3. EN la hoja CONSOLIDADO, ubicarse en la celda C6. 
 
4. Clic en la Ficha de Herramientas Datos; Comando Consolidar del Grupo 
Herramienta de datos 
 
 
 
 
 
 
 
 
El sistema mostrará la siguiente ventana del Comando Consolidar 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5. Seleccionar la función a realizar: Suma 
 
6. En Referencia agregar los tres nombres de campos creados sobre los valores 
a Sumar de la hojas anteriormente creadas; digitar: TBAL2005 [Agregar], 
TBAL2006 [Agregar], TBAL2007 [Agregar] 
 
7. Activar la Casilla de verificación de Crear Vínculos para que el consolidado 
siempre este actualizado así se modifiquen los datos orígenes 
 
8. Para ver el resultado, clic en Aceptar. 
 
9. Observar que ha sucedido y comentar las dudas o sugerencias con el 
instructor. 
 
 
 
54 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Paso a Paso: Consolidación por fórmula 
 
Podemos resolver el consolidado también de la 
siguiente manera; por REFERENCIA 3D. 
 
 
1. Crear una nueva hoja e ingresar la 
información de acuerdo al diseño 
sugerido en la Hoja5. 
 
Crear una nueva hoja e ingresar la 
información de acuerdo al diseño 
sugerido en la Hoja5. 
 
2. Cambiar el nombre de la Hoja5 por 
 
CONS_FORMULAS. 
 
3. Clic en la celda C6 y escribir la 
siguiente fórmula: 
 
='BAL2005'!C6+'BAL2006'!C6+'BAL2007'!C6 
 
o 
 
=SUMA('BAL2005:BAL2007'!C6) 
 
4. Luego copiar la fórmula, hasta total de ingresos. 
 
 
 
NOTAS 
 
 Al establecer fórmulas los nombres de las hojas se específica entre 
comillas y luego un signo de admiración, ejemplo: 
 
 'BAL2005'!C6 hace referencia a la celda C6 de la hoja BAL2005. 
 
 Al establecer fórmulas también puede especificarlas como rango de 
hojas, separándolos con dos puntos pero solamente especificando las 
comillas simples al inicio y final del rango de hojas, luego un signo de 
admiración que indica que son nombres de hojas, ejemplo: 
 
 =SUMA('BAL2005:BAL2007'!C6) hace referencia a la celda C6 del 
rango de hojas BAL2005 hasta BAL2007; esto quiere indicar a la suma 
del valor de las celda C6 de las hojas BAL2005, BAL2006 y BAL2007. 
 
 
 
Referencias 3D 
 
Una referencia a la misma celda o al mismo rango (rango: dos o más celdas de una hoja. 
Las celdas de un rango pueden ser adyacentes o no adyacentes.) en varias hojas se 
denomina referencia 3D. Una referencia 3D es un método útil y cómodo de hacer 
referencia a varias hojas de cálculo que siguen el mismo patrón y a las celdas de cada 
hoja de cálculo que contienen el mismo tipo de datos para, por ejemplo, consolidar los 
datos presupuestarios de diferentes departamentos de la organización. 
 
 
 
 
SENATI-Computación e Informática 55 
 
 
 
 
 
 
 
 
Microsoft Office Excel 2007 
 
 
Ejemplo 1 
 
La Empresa Corp. Perú desea realizar un consolidado de los ingresos y/o inversión de 
las áreas de ventas, marketing y recursos humanos. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
PASOS 
 
1. Crear las hojas BAL5, BAL6 y BAL7 con el diseño sugerido. 
2. Crear la hoja CONSOLIDADO con el diseño sugerido. 
3. Especificar la fórmula de referencia 3D en la celda C6 
4. =SUMA('BAL5:BAL7'!C6) 
5. Luego copiar la fórmula y observar que ha sucedido. 
 
NOTAS 
 
 Al establecer fórmulas referencias 3D debe especificarlas separándolos con 
dos puntos pero solamente especificando las comillas simples al inicio y final 
del rango de hojas, luego un signo de admiración que indica que son 
nombres de hojas, posteriormente se indica la celda a operar; ejemplo: 
 
 
56 SENATI-Computación e Informática 
 
 
 
 
 
s de datos II 
 
 
 
 
 
 
 
 
 
 
 
=SUMA('BAL5:BAL7'!C6) hace referencia a la celda C6 del rango de hojas 
BAL5 hasta BAL7; esto quiere indicar a la suma del valor de las celda C6 de las 
hojas BAL5, BAL6 y BAL7. 
 
Puede utilizar las siguientes funciones en una referencia 3D: 
 
 
Función Descripción 
 
SUMA 
 
Suma números. 
PROMEDIO Calcula el promedio (media aritmética) de números. 
PROMEDIOA Calcula el promedio (media aritmética) de números; incluye 
valores de texto y lógicos. 
CONTAR Cuenta celdas que contienen números. 
CONTARA Cuenta las celdas que no están vacías. 
MAX Busca el valor mayor de un conjunto de valores. 
MAXA Busca el valor mayor de un conjunto de valores; incluye valores 
de texto y lógicos. 
MIN Busca el valor menor de un conjunto de valores. 
MINA Busca el valor menor de un conjunto de valores; incluye valores 
de texto y lógicos. 
PRODUCTO Multiplica números. 
DESVEST Calcula la desviación estándar de una muestra. 
DESVESTA 
 
Calcula la desviación estándar deuna muestra; incluye valores de 
texto y lógicos. 
DESVESTP Calcula la desviación estándar de una población. 
DESVESTPA 
 
Calcula la desviación estándar de una población; incluye valores 
de texto y lógicos. 
VAR Calcula la varianza de una muestra. 
VARA 
 
Calcula la varianza de una muestra; incluye valores de texto y 
lógicos. 
VARP Calcula la varianza de una población. 
VARPA 
 
Calcula la varianza de una población; incluye valores de texto y 
lógicos. 
 
 
 
 
 
 
SENATI-Computación e Informática 57 
 
 
 
 
Microsoft Office Excel 2007 
 
 
 
Cuestionarios 
 
 
 
1. Si tienen una base de datos con n registros duplicados ubicados en diferentes 
lugares, cómo los eliminaría. 
 
 
 
 
 
 
 
2. Si desea ingresar datos en un campo que solo permita de acuerdo a un formato 
preestablecido, cómo lo haría. 
 
 
 
 
 
 
 
3. Cuál es la diferencia entre 3D y Consolidación de datos. 
 
 
 
 
 
 
 
4. Cuál es la utilidad del administrador de escenarios. 
 
 
 
 
 
 
 
5. Cuál es la utilidad de buscar objetivo. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
58 SENATI-Computación e Informática