Material de Excel Intermedio - New2
Material de Excel Intermedio - New2
Material de Excel Intermedio - New2
Excel
Instructor:
MAE. Nelly Bravo González
Pertenece a:
Nota: Si comienza una fórmula pegando un nombre o una función, Microsoft Excel
insertará automáticamente el signo igual.
Excel usa siempre referencias relativas para las direcciones de celdas introducidas
en las fórmulas. Esto significa que las referencias usadas cambiarán de modo acorde
tras copiar la fórmula de una celda a otra. Con mucha frecuencia lo que se desea es
que las referencias a celdas sean relativas.
El método más seguro para definir referencias a celdas consiste en utilizar el ratón
para apuntar a la celda cuya referencia se desea insertar. Cuando se desea introducir
la referencia a una celda en una fórmula, basta clicar en dicha celda. De esta forma,
se minimizan también los errores en la inserción de referencias a celdas.
Para introducir una fórmula en una celda mediante el sistema anterior, con una
referencia a otra celda, basta seguir los siguientes pasos:
1. Seleccionar la celda donde se desea introducir la fórmula.
2. Teclear un signo igual (=).
3. Señalar con el ratón la celda cuya referencia desea introducir y hacer clic. La
dirección de la celda apuntada aparece en la posición del punto de inserción, en la
barra de fórmulas.
4. Introducir un operador, por ejemplo el operador suma (+).
5. Si se desea introducir más referencias celdas en la fórmula, basta repetir los pasos
3 y 4 cuantas veces sea necesario. Para dar la fórmula por terminada se pulsa Intro.
En ciertos casos hay que evitar que las referencias a celdas cambien cuando se copia
la fórmula a una nueva posición. Para ello hay que utilizar referencias absolutas. Es
posible utilizar referencias absolutas para filas y relativas para columnas, o
viceversa. Las referencias relativas se convierten en absolutas introduciendo el
carácter dólar ($) antes de la letra de la columna o el número de fila, que se quieren
mantener invariables. Si se desea que no cambie ni el índice de filas
ni de columnas, hay que poner el carácter $ delante de cada uno de los dos índices.
Una referencia absoluta se puede insertar de dos formas diferentes:
1. Conforme se introduce la fórmula, se teclea el carácter $ delante del índice de
fila y/o de columna que se quiere mantener constante.
2. Colocando el punto de inserción en la barra de referencias de manera que esté
dentro de la referencia a la celda, pulsando la tecla F4 se pasa cíclicamente por
referencias relativas, absolutas y por los dos casos mixtos.
En efecto, en algunos casos, se pretende que tan sólo una de las dos dimensiones, fila
o columna, permanezca constante. En tales casos, se utiliza una referencia mixta, es
decir, una referencia que contenga a la vez referencias absolutas y relativas. Por
ejemplo, la referencia $B5 evita que cambie la columna, mientras que la fila se
Las celdas normalmente son identificadas por su ubicación dentro de la hoja, respecto
de la columna y renglón que la forman, por ejemplo, B5 o B3. Sin embargo, es posible
también asignarles un nombre que represente el dato que contienen, por ejemplo: IVA
o DESCUENTO.
Los nombres pueden ser asignados a una sola celda o a un rango de celdas, por
ejemplo: H5 puede llamarse BONO y el rango A1:A5 puede llamarse PRECIOS.
El dar nombre a una celda o rango de celdas tiene dos objetivos o ventajas:
1. Facilitar su uso dentro de las fórmulas, permitiendo que éstas sean más fáciles al
momento de su escritura y más claras en su lectura, es decir, es fácil saber el
origen de un cálculo sin tener que localizar primero las celdas para poder
identificar el sentido del cálculo realizado.
Crear nombres
Es posible asignar a una serie de celdas que contienen datos, los nombres que se
encuentran en las celdas, a su izquierda, arriba, abajo o a su derecha, según el diseño
de la hoja de cálculo. Esto permite ahorrar tiempo, ya que se crean nombres para
varias celdas con un solo procedimiento:
Primero se deben seleccionar tanto las celdas que
contienen el texto que será usado como nombre, como
las celdas que contienen los datos. Estas celdas deben
ser adyacentes.
Seleccionar el comando nombre del menú insertar.
Elegir la opción crear.
Excel muestra el cuadro de diálogo crear nombres,
donde es posible elegir si los nombres serán tomados de
la fila superior o inferior o de la columna izquierda o derecha.
Presionar el botón aceptar.
Para hacer referencia a celdas de otros libros debemos indicar el nombre del libro
entre corchetes y el resto como acabamos de ver.
Por ejemplo: '[presupuesto 2003]Hoja1'!B2 esta referencia indica que la celda se
encuentra en el libro "Presupuesto 2003", en la Hoja1 y en la celda B2.
Muy importante: Fíjense bien que al escribir una cadena de caracteres que incluya
espacios debemos ponerlo siempre entre comillas simples ' '.
Vínculo
=SUMAR([Presupuesto.xls]Anual!...
Si el libro de origen está cerrado, el vínculo incluye toda la ruta de acceso.
Vínculo
=SUMAR('C:\Informes\[Presupues...
Nota Si el nombre de la otra hoja de cálculo o del otro libro contiene caracteres no
alfabéticos, se deberá poner el nombre (o la ruta de acceso) entre comillas sencillas.
Si utiliza una fórmula para vincular datos de otros programas, estos datos se
actualizan automáticamente en Microsoft Excel cada vez que se cambian en el otro
programa.
Haga clic en la celda que contiene los datos a los que desee vincular y, a
continuación, haga clic en Copiar en la barra de herramientas Estándar.
Haga clic en la celda desde la que desee vincular y, a continuación, haga clic en
Pegar .
Haga clic en Opciones de pegado y después en Vincular celdas.
Nota No es posible utilizar el comando Objeto del menú Insertar para insertar
gráficos y algunos tipos de archivos. Para insertar un gráfico, elija Imagen en el menú
Insertar y, a continuación, haga clic en Desde archivo.
Funciones BUSQUEDA:
Filtros especiales
Para filtrar la lista según un valor de la columna, seleccione dicho valor.
Para filtrar la lista según dos o más valores de la columna, o para aplicar
operadores de comparación distintos de Y, haga clic en "Personalizadas".
Notas
• Se pueden quitar subtotales de una lista sin que afecte a los datos originales,
con el botón “Quitar Todos”.
Para cambiar el diseño de una tabla dinámica arrastrando un campo o una celda de
datos, haga lo siguiente:
Señale el borde de la celda o el botón de campo del elemento o del campo
de la tabla dinámica que desee mover.
Campos de Página
Un campo de página de tabla dinámica, es un campo en
donde las ocurrencias se muestran de una en una en una
tabla dinámica, es como un filtro de la tabla dinámica,
pueden utilizarse para mostrar datos de campo
independientes de otros campos de la tabla dinámica.
Puede situar cada página en una hoja individual para
imprimir datos o trazar gráficos independientes de otros
datos de la tabla dinámica.
En ocasiones, tenemos que realizar acciones repetitivas y rutinarias una y otra vez.
En vez de hacerlas manualmente, podemos crear una macro que trabaje por
nosotros. Las macros son funciones que ejecutan instrucciones automáticamente y
que nos permiten ahorrar tiempo y trabajo.
Los pasos para crear una macro son:
Acceder a Herramientas – Macro – Grabar macro
Pulsar las teclas o tareas una tras otra teniendo cuidado en no
equivocarnos.
Detener la grabación de la macro.
Depurar posibles errores o modificar la macro.
Ejecutar Macro
Colócate en la Hoja2
Accede a Herramientas – Macro – Macros.
Elige tu macro y pulsa el botón Ejecutar.
Observa su comportamiento.
La macro ha ido realizando paso a paso todas las acciones que hemos
preparado.
Nota:
Las asignaciones de método abreviado reemplazarán a las equivalente de
Microsoft Excel mientras esté abierto el libro que contiene la macro.
Para que una macro esté disponible todo el tiempo, almacénela en el Libro de
macros personal. Este libro siempre está abierto. En el cuadro de diálogo
Grabar nueva macro, haga clic en "Opciones" y luego en "Libro de macros
personal".
AUDITORÍA
Rastrear Precedentes
Si la celda que ha seleccionado contiene una fórmula, puede buscar todas las celdas
que proporcionan datos a esa fórmula. Las celdas que proporcionan datos se conocen
como celdas precedentes. La fórmula de la celda seleccionada calcula un nuevo
resultado cuando cambian los valores de las celdas precedentes.
Libro, una flecha de rastreo negra une el icono de la hoja de cálculo con la celda
seleccionada.
El cuadro azul que rodea el rango D9:D11 y la flecha de rastreo azul que señala a la
celda B5 indican que D9:D11 proporciona datos a la fórmula de la celda B5.
Sin embargo, el otro libro debe estar abierto antes de que Excel pueda rastrear las
dependencias.
La flecha de rastreo azul situada entre B9 y D9 muestra que el costo total de las
acciones de Líneas Aéreas Cielo Azul depende del precio de compra. La flecha de
rastreo azul situada entre D9 y B5 muestra que el costo total de todas las acciones
depende del costo total de las participaciones de Líneas Aéreas Cielo Azul.
Rastrear Errores
Si una fórmula muestra un error, como #¡DIV/0!, puede usar Rastrear error para
Las flechas de rastreo rojas muestran que el error #¡DIV/0! de la celda G11 provoca
el error de la celda H11 que, a su vez, da lugar a un error en B3. La celda G11
contiene la fórmula =E11/B11; Microsoft Excel interpreta la celda B11 como cero (0)
porque está vacía.
PLANTILLAS
Una plantilla es esencialmente un archivo con ciertas características que sirven como
base para crear otros archivos. Esto proporciona un aspecto uniforme entre
documentos y significa ahorro de tiempo.
Las plantillas pueden ser usadas para una variedad de propósitos, que van desde
libros de trabajo de “rellenar los huecos en blanco”, hasta cambiar cualquier valor
predeterminado de Excel que se desee.
La plantilla normal de Excel se llama Libro, por esa razón los archivos nuevos antes
de ponerles su nombre se llaman Libro1, Libro2, etc. Excel cuenta con otro tipo de
plantillas, por ejemplo, facturas, informes de gastos y pedidos; y también brinda la
posibilidad de crear plantillas personalizadas.
Si se abre un nuevo archivo basado en una plantilla diferente a la plantilla normal, el
nuevo archivo se nombrará: nombre de la plantilla1, nombre de la plantilla2, etc.
1. Crear un libro que contenga las hojas, texto predeterminado (como los
encabezados de página y los rótulos de filas y columnas), fórmulas, macros,
estilos y otro formato que se desee en los nuevos libros que se basen en la
plantilla.
2. En el menú ARCHIVO, hacer clic en Guardar como.
3. En el cuadro Guardar como tipo, hacer clic en Plantilla (*.xlt).
4. En el cuadro Guardar en, aparecerá la carpeta Plantillas que es donde se
debe guardar para tener acceso a ella con el comando Nuevo archivo.
Para modificar una plantilla se sigue el mismo procedimiento que con un archivo
normal, sólo hay que tener cuidado de volverla a guardar como tipo .xlt y con el mismo
nombre.
VALIDACION DE DATOS
Las siguientes técnicas simplifican el proceso de captura de datos
La función Auto Completar hace fácil introducir el mismo texto en varias celdas, al
teclear las primera letras de un texto que ya se haya tecleado en esa
misma columna, Excel completa la entrada automáticamente. Esto
además de ahorrar tiempo, asegura que las entradas iguales, sean
escritas correctamente y sean coherentes.
Para introducir la fecha del día actual en la hoja de trabajo basta con seleccionar la
celda y utilizar la combinación de teclas Control+Shift+; Esta fecha es estática.
Para forzar que el texto aparezca en una nueva línea dentro de la misma celda se
usa la combinación de teclas Alt+Enter. Esto es similar a usar el formato de
alineación “ajustar texto”, pero tiene la ventaja de ejercer más control sobre la
apariencia del texto ya que uno decide donde será el cambio forzado de línea.
SEGURIDADES
Para conseguir una seguridad óptima, debe proteger todo el archivo de libro mediante
una contraseña (contraseña: modo de restringir el acceso a una hoja de cálculo, una
hoja de datos o parte de una hoja de cálculo. Las contraseñas de Excel pueden tener
hasta 255 letras, números, espacios y símbolos. Debe escribir correctamente las letras
mayúsculas y minúsculas cuando defina y especifique contraseñas.), lo cual permite
que sólo los usuarios autorizados puedan ver o modificar los datos.
La seguridad mediante contraseña en el nivel del archivo de libro usa cifrado avanzado
(un método estándar para proteger el contenido de un archivo) para proteger el libro
frente a accesos no autorizados. Se puede establecer una contraseña en la ficha
Seguridad del cuadro de diálogo Opciones (menú Herramientas, comando Opciones).
Puede especificar dos contraseñas independientes que deben escribir los usuarios
para:Abrir y ver el archivo Esta contraseña está cifrada para proteger los datos frente
a accesos no autorizados.
Modificar el archivo Esta contraseña no está cifrada y sólo está destinada a conceder
permiso a usuarios específicos para que editen los datos del libro y guarden los
cambios en el archivo.
Estas contraseñas se aplican a todo el archivo del libro. Para conseguir una seguridad
óptima, es mejor asignar siempre una contraseña para abrir y ver el archivo, y
especificar que los usuarios con permisos para modificar los datos escriban ambas
contraseña.
Nota La protección mediante contraseña de un archivo de libro es diferente de la
protección de la estructura y la ventana del libro que puede establecer en el cuadro de
diálogo Proteger libro (menú Herramientas, submenú Protección, comando Proteger
libro).
Tipos de cifrado
Para conseguir una protección mediante contraseña más segura del archivo de libro y
de sus propiedades, puede elegir entre varios tipos de cifrado que puede usar con los
archivos de libro de Excel. El cifrado hace el texto ilegible para todos menos para los
usuarios autorizados que dispongan de una clave pública que coincida con el tipo de
cifrado y que les permita descifrar el texto.
Para obtener acceso a las opciones de cifrado, haga clic en el botón Avanzadas de la
ficha Seguridad del cuadro de diálogo Opciones (menú Herramientas, comando
Opciones).
Si no desea impedir que los usuarios abran un archivo de libro como lectura/escritura,
pero desea recordarles que los datos son importantes y no deben cambiarse, puede
hacer que Excel recomiende abrir el libro como sólo lectura. Puede hacerlo sin solicitar
una contraseña para abrir el archivo.
Si activa la casilla de verificación Recomendado sólo lectura de la ficha Seguridad del
cuadro de diálogo Opciones (menú Herramientas, comando Opciones), los usuarios
obtendrán una recomendación de sólo lectura al abrir el archivo. Sin embargo, esto no
impide que los usuarios abran el archivo como lectura/escritura para poder cambiar el
archivo y guardar sus cambios.
Antes de proteger una hoja de cálculo, puede desbloquear los rangos en los que
desea que los usuarios puedan cambiar o introducir datos. Puede: Desbloquear celdas
para todos los usuarios en la ficha Protección del cuadro de diálogo Formato de celdas
(menú Formato, comando Celdas).
Desbloquear celdas para usuarios específicos en el cuadro de diálogo Permitir que los
usuarios modifiquen rangos (menú Herramientas, submenú Protección, comando
Permitir que los usuarios modifiquen rangos).
Los usuarios que especifique en el cuadro de diálogo Permisos para rango (botón
Permisos) pueden modificar el rango automáticamente sin escribir la contraseña. A los
demás usuarios se les pedirá la contraseña cuando deseen modificar el rango.
Si una celda pertenece a más de un rango, los usuarios que tengan autorización para
modificar cualquiera de los rangos podrán modificar la celda.
Si un usuario intenta modificar varias celdas y sólo tiene autorización para modificar
algunas de ellas, aparecerá un mensaje indicándole que debe seleccionar y modificar
las celdas una a una.
Si especifica rangos en el cuadro de diálogo Permitir que los usuarios modifiquen
rangos sin asignar una contraseña, dichos rangos se desbloquearán para todos los
usuarios.
Al proteger un libro u hoja de cálculo para bloquear sus elementos, agregar una
contraseña es opcional. En este contexto, la contraseña sólo sirve para permitir el
acceso a ciertos usuarios e impedir que realicen cambios otros. Este nivel de
protección mediante contraseña no asegura que todos los datos confidenciales del
libro estén protegidos. Para lograr una seguridad óptima, debería usar una contraseña
para proteger el propio archivo de libro frente a accesos no autorizados.
Nota Es muy importante que recuerde la contraseña especificada. Sin la contraseña,
no hay forma de desproteger el libro o la hoja de cálculo.
Puede bloquear la estructura de un libro para impedir que los usuarios agreguen o
eliminen hojas de cálculo, o que muestren hojas de cálculo ocultas. También puede
impedir que los usuarios cambien el tamaño o la posición de las ventanas que ha
configurado para mostrar un libro. La protección de la estructura y la ventana del libro
se aplica a todo el libro.
Para proteger un libro, seleccione Protección en el menú Herramientas y, a
continuación, haga clic en Proteger libro.
Puede elegir qué elementos desea proteger (estructura, ventanas del libro, o ambos)
activando o desactivando las siguientes casillas de verificación.
FORMATO CONDICIONAL
El formato condicional permite determinar el formato de una celda en función de su
contenido. Por ejemplo, se puede presentar la información sobre las ventas de este
año en azul y negritas si son más altas que las del año pasado y en rojo y cursiva si
son más bajas.
Al hacer una modificación a un estilo, todas las celdas a las que se les haya aplicado
ese estilo serán modificadas automáticamente (siempre y cuando primero se haya
escrito el texto y después aplicado el estilo).
Los estilos que se crean en determinado Libro, solo están disponibles para dicho
Libro. Si se quieren usar en otro Libro se debe usar la opción Combinar del cuadro de
diálogo Estilo. Para utilizar esta opción debe estar abierto el Libro que contiene los
estilos a combinar.
Nota: Si el libro activo contuviera estilos con el mismo nombre que los estilos que
desean copiar, se deberá confirmar que se desea combinar los estilos que tienen los
mismos nombres. Para reemplazar los estilos del libro activo por los estilos copiados,
hacer clic en Sí. Para mantener los estilos del libro activo, hacer clic en No. Este
mensaje de advertencia aparecerá una sola vez, independientemente del número de
nombres de estilos en conflicto.