Story Transcript
Microsoft Excel Mejores prácticas para manejo de Hojas de Cálculo Lista de contenido
ADMINISTRACIÓN DE HOJAS DE TRABAJO........................................................................ 1 Insertar y Eliminar Hojas dentro de un Libro de Trabajo ....................................................... 1 Seleccionar hojas de trabajo.............................................................................................. 1 Insertar hojas de trabajo .................................................................................................... 2 Eliminar hojas de trabajo dentro de un libro....................................................................... 4 Mover y Copiar las hojas de un libro de trabajo..................................................................... 5 Mover hojas dentro del mismo libro de trabajo: ................................................................. 5 Copiar hojas dentro del mismo libro de trabajo:................................................................. 7 Mover o copiar hojas de un libro de trabajo a otro:............................................................ 8 Seleccionar todas las hojas de un libro ............................................................................... 11 PROTECCIÓN DEL LIBRO DE TRABAJO ............................................................................ 12 Proteger un libro de trabajo con una contraseña de acceso ............................................... 12 Abrir un libro de trabajo protegido con una contraseña ....................................................... 14 Proteger un Libro de Trabajo contra escritura ..................................................................... 15 Abrir un libro de trabajo protegido contra escritura.............................................................. 16 Desproteger un libro de Trabajo contra escritura............................................................. 16 Eliminar contraseñas........................................................................................................ 17 Abrir un libro de trabajo como de Sólo Lectura.................................................................... 18 Guardar un libro como Sólo lectura sin contraseña ......................................................... 18
Training Pro
I
PROTEGER LIBROS DE TRABAJO ABIERTOS...................................................................20 Proteger la información de un Libro de Trabajo abierto .......................................................20 Desproteger la información de un Libro abierto ...................................................................22 Proteger las Hojas de un Libro abierto .................................................................................23 Desproteger las Hojas de un Libro abierto ...........................................................................25 Desproteger Rangos ............................................................................................................26 Ocultar y Desocultar Fórmulas .............................................................................................30 IMPORTAR ARCHIVOS CON OTROS FORMATOS ..............................................................34 Guardar Libros de Trabajo en otros Formatos .....................................................................36 CÓMO NOMBRAR RANGOS..................................................................................................37 FORMATO CONDICIONAL .....................................................................................................40 Asignar un formato condicional ............................................................................................40 Copiar formatos en otras celdas...........................................................................................41 Cambiar o quitar un formato condicional ..............................................................................41 TRABAJAR CON FÓRMULAS ...............................................................................................42 Copiar Fórmulas ...................................................................................................................43 Referencia Relativa y Absoluta.........................................................................................43 TRABAJAR CON FUNCIONES...............................................................................................45 Estructura de una función.....................................................................................................45 Asistente de Funciones ........................................................................................................46 Función Suma (Sum)............................................................................................................48 AutoSuma .............................................................................................................................52 Grandes Totales ...................................................................................................................55 Función Promedio (Average)................................................................................................56
II
Training Pro
Función MAXIMO (MAX): .................................................................................................... 58 Función MINIMO (MIN):....................................................................................................... 59 Función CONTAR (COUNT):............................................................................................... 60 Función SI (IF): .................................................................................................................... 61 Función CONTAR.SI (COUNTIF): ....................................................................................... 63 Función SUMAR.SI (SUMIF): .............................................................................................. 64 Función BUSCARV (VLOOKUP): ........................................................................................ 65 Función BUSCARH (HLOOKUP):........................................................................................ 68 Función CONCATENAR (CONCATENATE)........................................................................ 70 Función Y (AND):................................................................................................................. 71 Función O (OR):................................................................................................................... 72 Función REDONDEAR (ROUND):....................................................................................... 73 Función TRUNCAR (TRUNC):............................................................................................. 74 Autocalcular ......................................................................................................................... 75 Copiar valores de fórmulas .................................................................................................. 77 ENCADENAR INFORMACIÓN ............................................................................................... 80 Encadenar Información entre Hojas .................................................................................... 80 Encadenar Información entre Libros de trabajo................................................................... 81 Remover Ligas de Información ........................................................................................ 82 HERRAMIENTA AUDITORÍA ................................................................................................. 83 Rastrear celdas precedentes ............................................................................................... 83 Rastrear celdas dependientes ............................................................................................. 84
Training Pro
III
Mejores prácticas para manejo de Hojas de Cálculo
Administración de Hojas de Trabajo Insertar y Eliminar Hojas dentro de un Libro de Trabajo Cuando se abre un libro de trabajo nuevo dentro de Excel, se abre con tres hojas de cálculo disponibles para trabajar; en caso de que usted requiera más podrá insertarlas o eliminarlas cuando ya no las requiera.
Seleccionar hojas de trabajo Antes de insertar o eliminar hojas de un libro deberá de seleccionarlas; usted puede seleccionar hojas tanto continuas como discontinuas. Seleccionar hojas continúas: a) Dé un clic al separador de la primera hoja a seleccionar. b) Presione la tecla de Shift y sin soltarla, dé un clic a la última hoja del rango que desea seleccionar. Seleccionar hojas discontinuas: a) Dé un clic al separador de la primera hoja a seleccionar. b) Presione y mantenga presionada la tecla de Control, dé un clic al separador de la segunda hoja a seleccionar. c) Siga seleccionando los separadores de las hojas sin soltar la tecla de Control.
Training Pro
1
Microsoft Excel
Insertar hojas de trabajo Para agregar una o varias hojas dentro de un libro de trabajo en uso, siga estos pasos: 1. Si va a insertar sólo una hoja, dé clic derecho al ratón sobre el separador de la hoja que desea que quede después de la hoja insertada; es decir, si selecciona la hoja número 3 e inserta una hoja, ésta se insertará antes de la hoja 3. Si desea insertar más de una hoja, seleccione el número de hojas que desea insertar; estas hojas deberán de ser seleccionadas en forma continua. Dé clic derecho al ratón sobre el separador de la hoja que desea que quede después de las hojas insertadas.
2. Seleccione el comando de Insertar. Excel despliega la siguiente pantalla: 3. Seleccione el tipo de Hoja que desea insertar, como por ejemplo: Hoja de cálculo. 4. Elija el botón de Aceptar. Excel inserta una nueva hoja de cálculo y se convierte en la hoja de trabajo activa; dándole el nombre de Hojan, donde n es el número de hoja que le continua a la última.
2
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
9 También
puede utilizar el comando de Hoja de cálculo del menú de Insertar para agregar una nueva hoja de cálculo a un libro; siempre y cuando seleccione el número de hojas que desea insertar.
)
Otra forma de agregar una hoja de trabajo es presionando las teclas de Shift/F11.
)
No puede agregar hojas discontinuas.
Training Pro
3
Microsoft Excel
Eliminar hojas de trabajo dentro de un libro Cuando ya no necesite una hoja de trabajo o su contenido puede eliminarla, para hacerlo siga estos pasos: 1. Seleccione el separador de la hoja o de las hojas que desea borrar.
) Para eliminar varias hojas puede seleccionarlas ya sean continuas o discontinuas. 2. Dé clic derecho sobre el separador de alguna de las hojas seleccionadas. 3. Elija el comando de Eliminar. Excel elimina la(s) hoja(s) seleccionada(s) y la hoja de la derecha a la(s) eliminada(s) se convierte en la hoja activa.
) Con este comando no podrá deshacer la acción, por lo tanto asegurese que realmente desea eliminar las hojas.
9
4
También puede seleccionar el comando de Eliminar Hoja del menú de Edición.
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Mover y Copiar las hojas de un libro de trabajo Puede reorganizar las hojas de un libro de trabajo moviéndolas dentro del mismo libro de trabajo o de un libro a otro. También puede copiar hojas dentro del mismo libro o a otro libro de trabajo. Tanto para mover o copiar hojas dentro del mismo libro de trabajo o, para mover o copiar hojas de un libro a otro, deberá hacerlo por medio del comando de Mover y Copiar hojas o utilizando el método de Arrastrar y Soltar.
Mover hojas dentro del mismo libro de trabajo: 1. Seleccione la o las hojas que desea mover. 2. Puede seleccionar hojas continuas (utilizando la tecla de Shift) o discontinuas (utilizando la tecla de Control). 3. Dé un clic sostenido al separador de la hoja seleccionada o de alguna de las hojas seleccionadas.
Etiquetas de Separadores 4. Arrastre el separador de la hoja seleccionada a lo largo del renglón de las etiquetas de los separadores; ya sea hacia adelante o hacia atrás, desplazándola hacia el lugar en donde desea mover la(s) hoja(s) .
Training Pro
5
Microsoft Excel
Indicador de nueva ubicación
) Observe que mientras arrastra el ratón Excel despliega un triángulo negro que indica dónde se va a insertar la o las hojas seleccionadas.
5. Libere el ratón sobre el separador de la hoja anterior al cual desea que se mueva la o las hojas seleccionadas. Excel moverá la o las hojas seleccionadas a su nueva ubicación.
Nueva ubicación
) Si
movió hojas discontinuas, las hojas movidas se insertarán juntas en su nueva ubicación.
6
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Copiar hojas dentro del mismo libro de trabajo: 1. Seleccione la o las hojas que desea copiar. 2. Puede seleccionar hojas continuas (utilizando la tecla de Shift) o discontinuas (utilizando la tecla de Control). 3. Dé un clic sostenido en el separador de la hoja seleccionada o de alguna de las hojas seleccionadas. 4. Presione la tecla de Control. 5. Manteniendo el clic sostenido, arrastre el separador de la hoja seleccionada a lo largo del renglón de las etiquetas de los separadores de las hojas; ya sea hacia adelante o hacia atrás.
) Observe que mientras arrastra el ratón, Excel despliega un triángulo negro con un signo de + (más) que indica dónde se va a copiar la o las hojas seleccionadas.
6. Libere el ratón sobre el separador de la hoja anterior al lugar en donde desea poner el duplicado de la hoja o de las hojas seleccionadas. Posteriormente suelte la tecla de Control. Si copió hojas discontinuas, las hojas copiadas se insertarán juntas en su nueva ubicación y el nombre de las hojas duplicadas cambia. Conserva el nombre original de la o las hojas duplicadas y les agrega (2) al final de su nombre, por ejemplo: si el nombre de la hoja origen es Hoja1 el nombre de la hoja duplicada es Hoja1 (2).
Hojas Duplicadas
Training Pro
7
Microsoft Excel
Mover o copiar hojas de un libro de trabajo a otro: 1. Seleccione la o las hojas que desea mover o copiar. 2. Puede seleccionar hojas continuas (utilizando la tecla de Shift) o discontinuas (utilizando la tecla de Control). 3. Dé un clic al botón derecho del ratón sobre el separador de la hoja seleccionada o sobre alguno de los separadores de las hojas seleccionadas. 4. Seleccione el comando de Mover o Copiar.
5. Seleccione el nombre del libro de trabajo destino, dando un clic a la flecha de lista desplegable de la caja de selección de Al libro en la cual sólo se muestran los nombre de los libros de trabajo abiertos. 6. En la caja de selección de Antes de la hoja, seleccione el nombre de la hoja anterior a la cual desea la inserción de la(s) hoja(s) que desea mover o copiar. 7. Active la casilla de verificación Crear una copia, si desea copiar la(s) hoja(s) seleccionada(s). Y si sólo desea mover la(s) hoja(s) seleccionada(s) deje desactivada la casilla de verificación de Crear una copia. 8. Elija el botón de Aceptar.
) Excel mueve o copia la(s) hoja(s) seleccionada(s) al libro de trabajo seleccionado y éste se convierte en el libro de trabajo activo.
8
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
9
También puede mover o copiar la(s) hoja(s) seleccionada(s) utilizando el comando de Mover o copiar hoja del menú de Edición.
Si movió o copió hojas discontinuas se insertarán juntas en su nueva ubicación. Si el libro de trabajo destino contiene una hoja con el mismo nombre de la hoja origen, Excel le da un nuevo nombre; asignando el mismo nombre que tiene la hoja origen y agregando (2) al final del nombre de la misma. También puede mover o copiar hojas de trabajo a otro libro de trabajo utilizando el método de Arrastrar y Soltar; tan solo debe de arrastrar la etiqueta del nombre de la(s) hoja(s) seleccionada(s) de una ventana del libro de trabajo, Ventana origen, a la Ventana Destino. Antes de hacer esto, se recomienda que organice las ventanas de los libros de trabajo, origen y destino, de manera que pueda ver los nombres de las hojas de ambos libros; las puede organizar ya sea en forma horizontal, vertical o mosaico utilizando el comando de Organizar que se encuentra dentro del menú de Ventana.
Training Pro
9
Microsoft Excel
Ventanas organizadas en Mosaico Vertical
10
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Seleccionar todas las hojas de un libro Hay ocasiones que usted deseará seleccionar todas las hojas contenidas en un libro de trabajo. Para seleccionar todas las hojas de un libro: 1. Dé un clic al botón derecho del ratón sobre alguno de los separadores de hojas del libro activo. 2. Elija el comando de Seleccionar todas las hojas.
Para cancelar la selección de hojas: Dé un clic sobre la etiqueta de cualquier hoja. También puede elegir el comando Desagrupar hojas del menú contextual que aparece cuando da un clic al botón derecho del ratón sobre algún separador de las etiquetas de las hojas.
Training Pro
11
Microsoft Excel
Protección del Libro de Trabajo Puede controlar el acceso a libros de trabajo asignando una contraseña para prevenir accesos no autorizados, prevenir que alguien modifique el contenido del libro o hacer que sólo sea de lectura. Cuando asigna una contraseña de acceso y la olvida, le será imposible recuperar la información del archivo.
Proteger un libro de trabajo con una contraseña de acceso Para asignar una contraseña de acceso a un libro de trabajo: 1. Active el libro al que desea asignarle una contraseña de acceso. 2. Seleccione el comando de Guardar como del menú de Archivo. 3. Del botón Herramientas elija el botón de Opciones Generales.
Excel despliega la caja de diálogo de Opciones para Guardar: 4. Escriba la contraseña de acceso en la caja de texto de Contraseña para apertura.
) Recuerde exactamente como fue escrita la contraseña, ya que Excel distingue entre letras mayúsculas y minúsculas.
5. Seleccione Aceptar. Aparecerá la caja de diálogo de Confirmar contraseña, en donde deberá de teclear la misma contraseña nuevamente.
12
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
6. Elija Aceptar. Si la contraseña coincide con la que fue escrita inicialmente, Excel regresa a la caja de diálogo de Guardar como y si la contraseña no coincide, Excel le presentará la siguiente caja de diálogo y tendrá que volver a escribir la contraseña.
Training Pro
13
Microsoft Excel
Abrir un libro de trabajo protegido con una contraseña Cuando desee abrir un libro de trabajo que tenga asignada una contraseña, Excel le preguntará la contraseña a través de la siguiente caja de diálogo:
Si la contraseña escrita es incorrecta, Excel le negará el acceso al libro hasta que teclee la contraseña de acceso correcta, apareciendo la siguiente caja de diálogo:
14
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Proteger un Libro de Trabajo contra escritura Para prevenir modificaciones a un libro de trabajo por usuarios no deseados, debe de proteger el archivo contra escritura de la siguiente forma: 1. Active el libro al que desea asignarle una contraseña de restricción contra escritura. 2. Seleccione el comando de Guardar como del menú de Archivo. 3. Del botón Herramientas elija el botón de Opciones Generales.
4. Escriba la contraseña que desea asignarle al archivo en la caja de texto de Contraseña de escritura.
) Recuerde exactamente como fue escrita la contraseña; ya que Excel distingue entre letras mayúsculas y minúsculas.
5. Seleccione el botón de Aceptar. 6. Dentro de la caja de diálogo de Confirmar contraseña, teclee nuevamente la misma contraseña antes dada de alta. 7. Elija Aceptar; si la contraseña coincide con la que fue escrita inicialmente, Excel regresa a la caja de diálogo de Guardar como y, si la contraseña no coincide, Excel le volverá a preguntar la contraseña.
Training Pro
15
Microsoft Excel
Abrir un libro de trabajo protegido contra escritura Cuando desee abrir un libro de trabajo que tenga asignada una contraseña de restricción contra escritura, usted tendrá dos opciones: Introducir la contraseña del archivo para abrir y editar el libro de trabajo. Abrir el libro de trabajo como de sólo-lectura, permitiéndole ver el archivo pero no podrá guardar cambios realizados al archivo.
Desproteger un libro de Trabajo contra escritura Para abrir el libro de trabajo reservado contra escritura: 1. Seleccione el nombre del archivo que desea abrir desde la caja de diálogo de Abrir. 2. Excel notará que tiene asignado una contraseña de restricción; por tanto, desplegará la siguiente caja de diálogo:
3. Si conoce la Contraseña de escritura, escríbala dentro de la caja de texto de Contraseña y elija el botón de Aceptar.
16
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Si no la conoce, seleccione el botón de Sólo Lectura para abrir el archivo con restricciones de escritura. Pero si realiza cambios en el archivo abierto con restricción contra escritura, no podrá guardar el archivo con su nombre y ubicación original; más sin embargo, podrá guardar los cambios realizados con otro nombre de archivo y/u otra ubicación.
Eliminar contraseñas Cuando desee ya no tener protegido un libro de trabajo, puede remover la contraseña, tanto de acceso como de restricción de escritura. Para lograrlo haga lo siguiente: 1. Active el libro de trabajo al cual desee remover la contraseña. 2. Seleccione el comando de Guardar como del menú de Archivo. 3. Del botón Herramientas elija Opciones Generales. 4. Elimine la contraseña escrita en la caja de texto de Contraseña para abrir o la Contraseña de escritura o en ambas; dependiendo cuál contraseña desea eliminar. 5. Seleccione el botón de Aceptar. 6. Excel regresa a la caja de diálogo de Guardar como en donde debe de seleccionar el botón de Aceptar. Así se guarda el archivo ya con la contraseña eliminada.
Training Pro
17
Microsoft Excel
Abrir un libro de trabajo como de Sólo Lectura Puede abrir un libro de trabajo como Sólo lectura para prevenir guardar cualquier cambio realizado a la información del mismo, sin necesidad de asignarle una contraseña. Para esto primero debe de activar la opción de Sólo lectura al momento de guardarlo.
Guardar un libro como Sólo lectura sin contraseña 1. Una vez abierto el archivo, seleccione el comando Guardar como del menú de Archivo. 2. Del menú Herramientas elija el botón de Opciones Generales. 3. Active la opción de Se recomienda sólo lectura.
) Recuerde que no es necesario asignarle la contraseña contra escritura. 4. Elija el botón de Aceptar de la caja de diálogo de Opciones para guardar. 5. Dentro de la caja de diálogo de Guardar como, seleccione el botón de Aceptar, para que así Excel guarde el archivo con la nueva opción asignada.
18
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Abrir un libro de Sólo lectura sin contraseña 1. Abra el archivo deseado utilizando el método que prefiera. Excel al detectar que es de Sólo lectura, desplegará la siguiente caja de diálogo:
2. Si desea abrirlo como Sólo lectura para evitar hacer algún cambio innecesario, elija el botón de Sí. En caso de que desee abrir el archivo, modificarlo y guardarlo con el mismo nombre y ubicación, elija el botón de No. Si no desea abrir el archivo, seleccione el botón de Cancelar.
) Recuerde que si abre un archivo como sólo lectura y desea guardarlo con el mismo
nombre y ubicación, Excel no se lo va a permitir; por tanto deberá guardarlo con otro nombre y/u otra ubicación.
Training Pro
19
Microsoft Excel
Proteger Libros de Trabajo Abiertos Algunas ocasiones cuando está trabajando con algún libro de trabajo deseará proteger el contenido de una hoja a ser cambiada, prevenir que las hojas sean movidas, ocultadas, desocultadas, borradas, renombradas o insertadas.
Proteger la información de un Libro de Trabajo abierto Para proteger la información de una hoja de trabajo a ser borrada o modificada debe de realizar los siguientes pasos: 1. Active la hoja que desea proteger. 2. Seleccione el menú de Herramientas y el comando de Proteger. 3. Del submenú que Excel despliega, elija Proteger hoja.
Excel desplegará la siguiente caja de diálogo:
20
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
4. Introduzca una contraseña en la caja de Contraseña para desproteger la hoja, si es que desea proteger la información de la hoja con una contraseña; es decir, si nadie conoce esta contraseña no podrá desproteger la información. 5. Seleccione las acciones que desea que otro usuario realice dentro Permitir a los usuarios de esta hoja de cálculo. 6. Elija el botón de Aceptar. Si introduce una contraseña, Excel desplegará la caja de diálogo de confirmación de la contraseña; en donde deberá de introducir nuevamente la contraseña antes dada de alta. Así es como Excel protege los elementos elegidos y si intenta modificar o borrar alguno de ellos, se desplegará la siguiente caja de diálogo:
Training Pro
21
Microsoft Excel
Desproteger la información de un Libro abierto Si desea desproteger una hoja de trabajo para realizar cambios a la información, debe de realizar lo siguiente: 1. Active la hoja que desea desproteger. 2. Seleccione el menú de Herramientas. 3. Elija el comando de Proteger y dentro del submenú desplegado seleccione Desproteger hoja.
Si asignó una contraseña de protección, Excel desplegará la caja de diálogo de Desproteger hoja, para que introduzca la contraseña dada de alta; si la desconoce o la escribe incorrectamente no podrá desproteger la hoja de trabajo.
De esta forma la hoja de trabajo queda desprotegida, lista para realizar cualquier modificación.
22
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Proteger las Hojas de un Libro abierto Para prevenir que las hojas de un libro de trabajo sean movidas, ocultadas, desocultadas, borradas, renombradas o insertadas; o que la ventana de un libro de trabajo sea movida de su lugar o que se le cambie de tamaño, debe de realizar los siguientes pasos: 1. Seleccione el comando de Proteger del menú de Herramientas. 2. Dentro del submenú desplegado elija Proteger libro.
Excel despliega la siguiente caja de diálogo:
Training Pro
23
Microsoft Excel
3. Introduzca una contraseña en la caja de texto de Contraseña (opcional), si es que desea asignar una contraseña para proteger las hojas y la ventana del libro de trabajo. 4. Seleccione la opción de Estructura para así prevenir que las hojas del libro sean movidas, ocultadas, desocultadas, borradas, renombradas o insertadas. 5. Seleccione la opción de Ventana para prevenir que la ventana del libro sea cambiada de lugar o de tamaño. 6. Seleccione el botón Aceptar. Si introduce una contraseña Excel desplegará la caja de diálogo de confirmación de la contraseña, en donde deberá de introducir nuevamente la contraseña antes dada de alta. Así es como Excel protege a las hojas de trabajo y a la ventana del libro de trabajo. Si intenta borrar, insertar, renombrar, mover o copiar alguna hoja del libro no lo podrá hacer, ya que los comandos correspondientes se desactivan.
24
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Desproteger las Hojas de un Libro abierto Para desproteger las hojas de un libro de trabajo, así como la ventana del mismo: 1. Seleccione el comando de Proteger del menú de Herramientas. 2. Dentro del submenú desplegado elija Desproteger libro.
Si asignó una contraseña para proteger las hojas de un libro y la ventana del mismo, Excel le preguntará la Contraseña dada de alta; si no la conoce o la olvidó no podrá desproteger el libro de trabajo.
Training Pro
25
Microsoft Excel
Desproteger Rangos En algunas ocasiones querrá tener acceso sólo a cierto rango de información dentro de una hoja de trabajo protegida. Para poder hacer esto debe seguir dos pasos: Desbloquear un rango. Proteger una hoja. Siempre que tenga una hoja protegida y desbloquee un rango de la misma, podrá hacer cualquier modificación sólo al rango desbloqueado. Para desbloquear un rango de información: 1. Seleccione el rango de celdas que desea desbloquear, para que así pueda hacer modificaciones a las celdas seleccionadas.
2. Dé un clic derecho dentro del rango seleccionado y elija el comando de Formato de Celdas.
) También puede seleccionar el comando de Celdas que se encuentra en el menú de Formato.
26
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
3. Seleccione el separador de Proteger.
4. Desactive la opción de Bloqueada. 5. Seleccione Aceptar para que Excel desbloquee el rango seleccionado. 6. Seleccione el menú de Herramientas. 7. Elija el comando de Proteger y dentro del submenú desplegado elija Proteger Hoja. 8. Si lo desea, introduzca una contraseña en la caja de texto de Contraseña para desproteger la hoja. 9. Seleccione las acciones que desea que otro usuario realice dentro Permitir a los usuarios de esta hoja de cálculo. 10. Dé clic en el botón de Aceptar.
Training Pro
27
Microsoft Excel
Si introdujo una contraseña, Excel desplegará la caja de diálogo de Confirmación de contraseña; en donde debe volver a teclear la contraseña antes dada de alta. Así es como Excel protege los elementos que seleccionó y desprotege las celdas desbloqueadas que están disponibles para hacer cualquier modificación. Si intenta modificar las celdas que no fueron desbloqueadas, Excel desplegará la siguiente caja de diálogo:
Si desea Proteger un rango desbloqueado, deberá de hacer lo siguiente: 1. Active la hoja que contenga el rango desprotegido que desea proteger. 2. Seleccione el menú de Herramientas.
3. Elija el comando de Proteger y dentro del submenú desplegado elija Desproteger Hoja. 4. Si asignó una contraseña, Excel se la preguntará a través de la siguiente caja de diálogo:
Si no la conoce o no la recuerda, no podrá desproteger la hoja y aparecerá la siguiente caja de diálogo:
28
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
5. Seleccione el rango de celdas desprotegido que desea proteger. 6.
Dé un clic derecho sobre el rango de celdas seleccionadas y elija el comando de Formato de Celdas.
7.
Elija el separador de Proteger.
8.
Active la opción de Bloqueada.
9.
Seleccione Aceptar.
10. Vuelva a Proteger la hoja seleccionando el menú de Herramientas. 11. Elija el comando de Proteger y dentro del submenú desplegado elija Proteger Hoja. 12. Si lo desea, introduzca una contraseña en la caja de texto de Contraseña. 13. Seleccione las acciones que desea proteger y elija Aceptar.
) De esta manera protegerá todas las celdas de la hoja seleccionada. Training Pro
29
Microsoft Excel
Ocultar y Desocultar Fórmulas En algunas ocasiones querrá ocultar el cómo está creada una fórmula; más sin embargo, seguirá viendo el resultado de la misma y sobretodo, seguirá actualizándose cuando cambie algún valor que haga referencia dentro de la fórmula oculta. Para lograr esto, debe de seguir éstos pasos: 1. Seleccione el rango de celdas que contengan las Fórmulas que desea ocultar.
2. Dé un clic derecho dentro del rango seleccionado y seleccione el comando de Formato de Celdas.
) También puede seleccionar el comando de Celdas que se encuentra en el menú de Formato.
3. Active el separador de Proteger.
30
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
4. Active la opción de Oculta. 5. Seleccione el botón de Aceptar. 6. Seleccione el menú de Herramientas. 7. Elija el comando de Proteger y dentro del submenú desplegado elija Proteger Hoja.
Training Pro
31
Microsoft Excel
8. Si así lo desea, introduzca una contraseña en la caja de texto de Contraseña para que nadie que no conozca la contraseña pueda ver el contenido de la Fórmula. 9. Seleccione las acciones que desea proteger y elija Aceptar. Si introdujo una contraseña, Excel desplegará la caja de diálogo de Confirmar contraseña; en donde debe de teclear nuevamente la contraseña antes dada de alta.
De ahora en adelante, mientras esté protegida una hoja, nadie podrá ver el contenido de las Fórmulas seleccionadas.
Si desea Desocultar las celdas que contienen las Fórmulas ocultas, siga estos pasos: 1. Active la hoja que contenga las fórmulas ocultas. 2. Seleccione el menú de Herramientas. 3. Elija el comando de Proteger y dentro del submenú desplegado elija Desproteger hoja. 4. Si asignó una Contraseña, Excel le preguntará la contraseña dada de alta:
32
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Si no la conoce o no la recuerda, no podrá Desproteger la hoja y aparecerá la siguiente caja de diálogo:
Y si la Contraseña es correcta, Excel desprotegerá la hoja. 5. Seleccione el rango de celdas que contengan las Fórmulas que ocultó. 6. Dé un clic derecho sobre el rango de celdas seleccionadas y elija el comando de Formato de Celdas. 7. Elija el separador de Proteger. 8. Desactive la opción de Oculta. 9. Seleccione el botón de Aceptar.
Training Pro
33
Microsoft Excel
Importar Archivos con otros formatos Una de tantas ventajas que tiene Excel es que usted puede abrir archivos que hayan sido guardados en algún otro Formato, como archivos de texto, Bases de datos, incluso hojas de cálculo de otros programas; entre otros. Para abrir un archivo guardado con otro Formato: 1. Elija el botón de Abrir de la Barra de Herramientas Estándar o seleccione el menú Archivo – Abrir.
Excel despliega la caja de diálogo de Abrir:
2. Dé un clic a la flecha de lista desplegable de Tipo de Archivo.
34
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Excel despliega la lista de los formatos de los archivos que puede importar, por ejemplo: Hoja de cálculo XML, Datos XML, Página Web, Plantilla, Texto, alguna versión anterior de Excel, DBF, etc. 3. Seleccione el formato del archivo que desea importar. 4. Seleccione la Unidad de disco y la carpeta de donde se encuentra el archivo a abrir. 5. Elija el nombre de archivo que desea abrir en la caja de Nombre del Archivo. 6. Dé un clic sobre el botón de Aceptar.
) Normalmente
el archivo importado se abre como un Libro con una sola hoja de trabajo, indicando el nombre del archivo en el separador de la hoja; siempre y cuando el archivo que abrió no sea un libro de trabajo.
Training Pro
35
Microsoft Excel
Guardar Libros de Trabajo en otros Formatos Además de Abrir archivos creados en algún otro formato diferente a Excel, también puede guardar Libros de Trabajo con otro formato de tal forma que pueden ser abiertos por otras aplicaciones. Para Guardar un Libro de Trabajo con otro Formato: 1. Seleccione el menú de Archivo y el comando de Guardar como. 2. Dentro de la caja de diálogo de Guardar como, dé un clic a la flecha de lista desplegable de Guardar Como Tipo.
Excel despliega la lista de formatos en los cuales puede guardar el Archivo. 3. Seleccione el formato en el cual desea guardar el Libro de Trabajo. Excel modifica el tipo del archivo actual asignándole el formato correspondiente, conservando el nombre original del archivo. Teniendo la posibilidad de cambiarle el Nombre del archivo, la Unidad y la Carpeta donde desea guardar el nuevo archivo. 4. Seleccione el botón de Aceptar.
36
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Cómo Nombrar Rangos Una forma de hacer su Hoja de Trabajo más entendible y fácil de manejar, es dándole nombre a un rango. Cuando usted ve una fórmula que contiene determinado rango, podría darse el caso de que usted no se dé cuenta de lo que es. Sin embargo, si ve una fórmula que se refiere a un rango determinado, relacionará más rápidamente lo que la fórmula está calculando. Un nombre es un identificador fácil de recordar que se crea para referirse a una celda, a un grupo de celdas, a un valor o a una fórmula. Utilizar nombres de rango brinda las siguientes ventajas: ♦ Cada libro de trabajo puede compartir un solo conjunto de nombres. Los nombres que crea para hacer referencia a celdas en una hoja de trabajo de cálculo, pueden usarse en todo el libro de trabajo, eliminando la necesidad de tener que volver a crear nombres para cada hoja de trabajo nueva o escribir las referencias de las hojas en fórmulas. ♦ Las fórmulas que usan nombres son más fáciles de leer y recordar que las que utilizan referencias de celda. Por ejemplo, la fórmula =Activo-Pasivo, es más fácil de leer que la fórmula =F6-D6. ♦ Si cambia la estructura de la hoja de cálculo, puede actualizar la referencia en un sólo lugar y todas las fórmulas que usan ese nombre se actualizan automáticamente. ♦ Puede pegar nombres en las funciones rápida y fácilmente utilizando el área de referencia en la barra de fórmulas. ♦ Puede hacer que Microsoft Excel cree automáticamente nombres para las celdas basados en los títulos de las filas o de las columnas en la hoja de trabajo de cálculo, siempre y cuando dé de alta los nombres de rango a través del comando Nombre del menú de Insertar y seleccionando el submenú de Definir.
Para definir nombres, puede utilizar el Area de Referencia en la barra de fórmulas o seleccionar el menú de Insertar, comando Nombre y el submenú de Definir.
Training Pro
37
Microsoft Excel
Para nombrar un rango utilizando el Area de referencia: 1. Seleccione el rango que desea nombrar. 2. Dé un clic en la caja de Area de Referencia que se encuentra a la izquierda de la Barra de Fórmulas. 3. Teclee un nombre descriptivo para el rango seleccionado y presione la tecla de Enter.
Cuando escribe un nombre en el Area de referencia: ♦ Si el nombre no ha sido definido, se define como la celda o el rango seleccionado y se agrega a la lista del Area de referencia. ♦ Si el nombre ha sido definido, se selecciona la celda o el rango con nombre.
)
El nombre de un rango puede contener hasta 255 caracteres y puede contener letras. Números, subguión ( _ ), diagonal inversa ( \ ), puntos (.), y signos de interrogación (?). Pero siempre debe de comenzar con una letra, un subguión o una diagonal inversa y no debe de dar de alta un nombre de rango de celdas haciendo referencia a una celda (columna y renglón: A5).
Nombre del rango de celdas desde la B6 hasta la B16
* Si desea desplazarse a un rango de celdas antes nombrado, tan sólo dé un clic en la flecha de la caja de rea de referencia y seleccione el nombre del rango al cual desea desplazarse.
38
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Para nombrar un rango utilizando el comando de Insertar: 1. Seleccione el rango de celdas que desea nombrar. 2. Elija del menú de Insertar, el comando Nombre. 3. Del submenú desplegado, seleccione Definir. 4. Observe que dentro de la caja de texto de Nombres en el libro, Excel asigna automáticamente un nombre de rango basado en el contenido de la primera celda seleccionada, si ésta contiene un texto. En caso de que haya seleccionado celdas conteniendo sólo caracteres numéricos, Excel no asignará ningún nombre dejando en blanco la caja de Nombres en el libro, para que usted escriba el nombre que desea asignarle al rango de celdas seleccionadas. Si el rango de celdas seleccionadas, contienen caracteres numéricos, pero en la celda que se encuentra a su derecha o en la celda de arriba contienen caracteres alfanuméricos, Excel tomará el contenido de esa celda como nombre de rango. Si usted desea cambiar el nombre que Excel asigna automáticamente a un rango de celdas seleccionadas, escriba el nombre deseado dentro de la caja de texto de Nombres en el libro.
5. Antes de aceptar el nombre de rango, verifique que dentro de la caja de texto de Se referencia a, esté correcto el rango de celdas seleccionadas. Si es correcto elija el botón de Agregar y después el de Aceptar, o tan sólo el botón de Aceptar. Dentro de la caja de diálogo de Definir nombre, también puede eliminar un nombre de rango previamente asignado. Un nombre de rango no podrá ser renombrado; si desea cambiarle el nombre primero deberá eliminarlo y después volver a darlo de alta.
Training Pro
39
Microsoft Excel
Formato Condicional Formato condicional es aquel formato que Excel aplica automáticamente a las celdas si la condición que se especifica es cierta; por ejemplo, un sombreado de celda o un color de fuente previamente especificado.
Asignar un formato condicional 1. Seleccione las celdas cuyo formato condicional desee agregar, modificar o quitar. 2. En el menú Formato, haga clic en Formato condicional.
3. Si desea utilizar los valores de las celdas seleccionadas como el criterio de formato, haga clic en Valor de la celda, seleccione la frase de comparación e introduzca un valor constante o una fórmula. * Recuerde que si se introduce una fórmula, iníciela con un signo igual (=). 4. Para utilizar una fórmula como criterio de formato (para la evaluación de datos o condiciones que no sean los valores de las celdas seleccionadas), haga clic en Fórmula e introduzca la fórmula cuyo resultado sea un valor lógico de VERDADERO o FALSO. 5. Haga clic en Formato para asignar el formato que desee aplicar cuando el valor de la celda satisfaga la condición o la fórmula devuelva el valor VERDADERO. 6. Para agregar otra condición, haga clic en Agregar y repita los pasos del 3 al 5. * Pueden especificarse hasta tres condiciones. Si ninguna de las condiciones que se han especificado es verdadera, las celdas conservarán los formatos existentes. Nota: Si hay más una condición especificada verdadera, Microsoft Excel sólo aplicará los formatos de la primera condición verdadera, aunque haya varias que lo sean.
40
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Copiar formatos en otras celdas 1. Seleccione las celdas que contengan los formatos condicionales que desee copiar. 2. En la Barra de Herramientas Formato, haga clic en Copiar formato y, a continuación, seleccione las celdas a las que se lo desee aplicar.
Cambiar o quitar un formato condicional ¾ Para cambiar los formatos, haga clic en Formato para la condición que desee modificar. ¾ Para volver a seleccionar formatos en la ficha actual del cuadro de diálogo Formato de celdas, haga clic en Borrar y seleccione los nuevos formatos. ¾ Para quitar una o más condiciones, haga clic Eliminar y, a continuación, active la casilla de verificación de las condiciones que desee eliminar.
* Para quitar todos los formatos condicionales así como todos los demás formatos de celda de las celdas seleccionadas, elija Borrar en el menú Edición y, a continuación, haga clic en Formatos.
Training Pro
41
Microsoft Excel
Trabajar con Fórmulas Para realizar cálculos debe de utilizar fórmulas, que pueden ser desde una simple suma hasta cálculos complejos como funciones financieras, logarítmicas, estadísticas o financieras. Para que Excel reconozca que está trabajando con fórmulas, debe de contemplar los siguientes puntos: 1. Seleccione la celda en donde desea ver el resultado de su fórmula. 2. Debe de iniciar una fórmula con el signo de igualdad (=). 3. Plantear su fórmula dependiendo de sus necesidades, incluyendo alguno de las siguientes operadores:
/
División
*
Multiplicación
+
Suma
-
Resta
%
Porcentaje
^
Exponencial
Recuerde que estos signos tienen ciertas prioridades. Si incluye dentro de una misma fórmula diferentes operadores deberá indicarle a Excel la prioridad adecuada de como quiere que realice la fórmula. Esta prioridad la indicará incluyendo paréntesis. Dentro de una fórmula, puede incluir valores constantes o hacer referencia al contenido de las celdas.
42
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Ejemplos de fórmulas con valores constantes: =100+340+250 =560*15 =(810/20)*(400-240)*(30+67+33) =(100+100)/5*20 Ejemplos de fórmulas haciendo referencias al contenido de celdas: =C20+C21+C22+C24 =B1*B5*H3 =(M34*M35)/(N50^N55) Después de introducir su fórmula tan sólo presione la tecla de Enter.
Copiar Fórmulas Referencia Relativa y Absoluta Las referencias permiten indicar en qué celdas debe buscar Excel los valores que forman parte de una fórmula; ya sea dentro de una sección diferente dentro de la misma hoja de trabajo ó utilizar el valor de una celda como constante para fórmulas subsecuentes. Estas referencias se basan según la columna y el renglón que ocupa la celda activa. Existen por lo tanto dos tipos de referencias que pueden ser utilizadas dentro de una fórmula: la Absoluta y la Relativa. La Referencia Relativa es cuando el resultado de una fórmula se ajusta a la nueva posición a la que fue copiada o movida; esto es, suponga que se encuentra en la celda A6, cuando copiamos la siguiente fórmula =SUMA(A1:A5) a la celda B6, Excel automáticamente ajustará la fórmula a =SUMA(B1:B5).
Training Pro
43
Microsoft Excel
La Referencia Absoluta es cuando el valor contenido en una celda no cambia, independientemente que ésta se copie o se mueva. Significa que el valor de la celda es constante y se puede definir para un renglón, para una columna o para la celda (creando para las dos primeras un tercer tipo de referencia, la Referencia Mixta), agregando un signo de pesos ($) antes de cada parte de la dirección de la celda en la fórmula que desea copiar. Para introducir los signos de pesos pasando por las diferentes opciones del ciclo y cambiar la referencia de la celda, presione la tecla F4 cuando esté editando la fórmula.
Para crear una referencia Absoluta: 1. Seleccione la celda en la que va a introducir la fórmula. 2. Introduzca la fórmula que desee emplear. 3. Defina la(s) celda(s) que desea permanezca(n) como absoluta(s), presionando la tecla F4.
) En
el caso que desee definir una referencia mixta, presione la tecla F4 hasta que aparezca la referencia a utilizar.
4. Continúe definiendo los argumentos de la fórmula (operador matemático, siguiente celda, etc.). 5. Cuando termine de escribir la fórmula presione la tecla de ENTER.
En el caso en que desee modificar una fórmula para incluirle referencias absolutas: 1. Seleccione la celda que contiene la fórmula que desea modificar y edite la celda presionando la tecla F2 o dé doble clic sobre la celda que contiene la fórmula a editar. 2. Coloque el punto de inserción en la referencia de la celda que quiera convertir a absoluta. 3. Presione la tecla F4 hasta que aparezca la referencia a utilizar. 4. Presione la tecla de ENTER.
44
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Trabajar con Funciones Cuando trabaja con Excel tiene la ventaja de poder realizar una gran variedad de cálculos usando las Funciones interconstruídas de Excel. Puede realizar Operaciones Financieras, operaciones de Fecha y Hora, Operaciones Matemáticas y Trigonométricas, Operaciones Estadísticas, Operaciones de Revisión y Referencia, Operaciones de Bases de datos, Operaciones sobre Texto y Operaciones Lógicas. Las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden o estructura. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas.
Estructura de una función •
La estructura de una función comienza por el signo igual (=), seguido por el nombre de la función, un paréntesis de apertura, los argumentos de la función separados por comas y un paréntesis de cierre.
•
Nombre de la función, para obtener una lista de funciones disponibles, haga clic en una celda y presione SHIFT + F3.
•
Los Argumentos pueden ser números, texto, valores lógicos como Verdadero o Falso, matrices, valores de error como #N/A o referencias de celda. El argumento que se designe deberá generar un valor válido para el mismo. Los argumentos pueden ser también constantes, fórmulas u otras funciones.
•
Información sobre herramientas de argumentos. Cuando se escribe la función, aparece una información sobre herramientas con su sintaxis y sus argumentos.
=Nombre de la función (Argumentos) Ejemplo de Argumentos:
=Suma(A1:A10)
Rango en secuencia
=Promedio(B2,C6,F10)
Rango fuera de secuencia
=Producto(VENTAS)
Nombre de rango
=Contar(B5:D20,G8,COMPRAS)
Combinación de las anteriores
Training Pro
45
Microsoft Excel
Recuerde que: •
Todas las funciones deben de iniciar con un signo de igualdad (=).
•
El nombre de la función puede introducirse en mayúsculas o minúsculas indistintamente.
•
Los argumentos de las funciones van encerrados entre paréntesis.
•
No deben de contener espacios entre el nombre de la función y el paréntesis que abre antes de los argumentos.
Asistente de Funciones Para utilizar el Asistente de Funciones para introducir una función, utilice los siguientes pasos: 1. Haga clic en el botón de Asistente de Funciones que se encuentra en la Barra de Herramientas Estándar.
2. Excel despliega la primera caja de diálogo del Insertar Función:
46
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
3. Seleccione la categoría a la cual pertenezca la función que quiere aplicar. 4. Seleccione la función que desea introducir de la lista de Seleccionar una Función. 5. Dé un clic al botón de Aceptar. Excel despliega la siguiente caja de diálogo:
La apariencia de esta caja dependerá de la función seleccionada. 6. En la caja de texto número1, introduzca el valor o seleccione el rango en su hoja de trabajo para el primer argumento. Conforme introduzca un rango, Excel despliega los valores para el rango en la caja que se encuentra a la derecha de la caja de texto en la que se está introduciendo el rango. 7. Para introducir argumentos adicionales, muévase a la siguiente caja de texto que se encuentra en blanco nombrada como númeron e introduzca el valor o rango de celdas. 8. Cuando termine de introducir los argumentos haga clic en el botón de Aceptar.
¾ Si ya conoce la sintaxis correcta de la Función que vaya a utilizar en lugar de activar el Asistente de funciones, puede introducir la función en forma manual.
Training Pro
47
Microsoft Excel
Función Suma (Sum) Algunas observaciones que le diremos acerca de esta función, son que se toma en cuenta números, valores lógicos y representaciones de números que escriba directamente en la lista de argumentos. Si un argumento es una matriz o referencia, sólo se considerarán los números en esa matriz o referencia. Se pasan por alto las celdas vacías, valores lógicos, texto o valores de error en la matriz o en una referencia. Para utilizar la función suma le mostramos algunos ejemplos: 1. Capture los siguientes datos en una hoja en blanco, como se muestra en la
siguiente figura:
2. Coloque el indicador de celda activa en la celda dónde va a dejar el resultado de la
suma. 3. Dé clic en el botón de Insertar función de la Barra de fórmulas.
48
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Se despliega el siguiente cuadro de diálogo:
4. Seleccione las función Suma dentro del cuadro de Categoría, Matemáticas y trigonométricas. 5. Una vez seleccionada la función Suma, dé clic en Aceptar.
Training Pro
49
Microsoft Excel
6. Como lo muestra la figura anterior, Excel selecciona automáticamente el rango en el cual encuentra números y propone dicha rango para que sea totalizado. 7. Si efectivamente ese es el rango a suma sólo deberá dar clic en Aceptar. En caso contrario si lo desea cambiar presione el botón que se encuentra al final del cuadro de texto Número1.
Una vez presionado dicho botón éste ocultara el cuadro de diálogo Argumentos de función, para dejarle ver su hoja de cálculo y así poder hacer otra selección
8. Seleccione el nuevo rango que desee sumar.
50
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
9. Una vez selecciona presione nuevamente el botón para desplegar el cuadro completo 10. Y presione el botón de Aceptar, para terminar.
Training Pro
51
Microsoft Excel
AutoSuma Otra forma de realizar Sumas es utilizando el ícono de Autosuma que se encuentra en la Barra de Herramientas Estandar. Este botón inserta automáticamente la función de Suma y predice el rango de valores a Sumar.
Para totalizar un rango de celdas en una forma fácil y rápida a través del Autosuma, utilice los siguientes pasos: 1. Seleccione la celda al final de la columna o renglón que desea totalizar. 2. Dé un clic al botón de Autosuma.
Excel seleccionará el rango de celdas que contenga números
52
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo 3. Si el rango marcado es el que desea sumar, tan sólo presione la tecla de Enter
para aceptar el rango propuesto. Si no es así seleccione el rango de celdas que desea sumar y presione la tecla de Enter. * Recuerde que puede seleccionar rangos múltiples presionando la tecla de Control.
Si desea totalizar tanto renglones como columnas al mismo tiempo, debe de hacer lo siguiente: 1. Seleccione los renglones y columnas a totalizar incluyendo las celdas en dónde serán desplegados los resultados.
Training Pro
53
Microsoft Excel
2. Dé un clic al botón de Autosuma. Excel desplegará sus resultados tanto por renglón como por columna:
Totales por columna
Totales por renglón
54
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Grandes Totales Uno de los mayores beneficios de la característica AutoSuma es su capacidad de desplegar un gran total para varios subtotales. Cuando utiliza AutoSuma en este sentido, Excel totaliza todas las funciones SUMA del rango seleccionado y despliega un gran total. Para introducir grandes totales, siga estos pasos: 1. Seleccione el rango en el que desea introducir el Gran total.
Si aún no ha introducido las sumas de los subtotales, también puede seleccionar las diferentes celdas donde desea agregar los subtotales y el rango para el Gran total, utilizando la tecla de Control.
2. Dé un clic en el botón AutoSuma de la barra de herramientas Estándar.
Training Pro
55
Microsoft Excel
Función Promedio (Average) Devuelve el promedio (media aritmética) de los argumentos.
Sintaxis: PROMEDIO(número1, número2, ...) En donde: Número1, número2, ...
son de 1 a 30 argumentos numéricos cuyo promedio desea obtener.
Observaciones: Los argumentos deben ser números, nombres, matrices o referencias que contengan números. Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se ignoran; sin embargo, se incluyen las celdas cuyo valor sea 0. Sugerencia: Al calcular el promedio de las celdas, tenga en cuenta la diferencia entre las celdas vacías y las que contienen el valor cero, especialmente si ha desactivado la casilla de verificación "Valores cero" de la ficha Ver en la caja de diálogo Opciones del menú Herramientas. No se cuentan las celdas vacías, pero se cuentan los valores cero. Ejemplos: Si A1:A5 se llama Puntos y contiene los números 10, 7, 9, 27 y 2: =PROMEDIO(A1:A5) es igual a 11 =PROMEDIO(Puntos) es igual a 11 =PROMEDIO(A1:A5, 5) es igual a 10 =PROMEDIO(A1:A5) es igual a SUMA(A1:A5)/CONTAR(A1:A5), que es igual a 11 Si C1:C3 se llama OtrosPuntos y contiene los números 4, 18 y 7: =PROMEDIO(Puntos, OtrosPuntos) es igual a 10.5
56
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo Como el manejo de funciones es muy similar, utilizando el botón de Insertar función, para esta la función promedio utilizaremos otro método, que probablemente le resulte más sencillo cuando ya se encuentra familiarizado con el uso de las funciones. Para utilizar este método realice lo siguiente: 1. Nuevamente coloque el indicador de celda activa el donde desea dejar el resultado 2. Escriba el signo igual = 3. Enseguida del signo igual escriba la palabra PROMEDIO (no necesita ser en
mayúsculas, aquí lo escribimos, así para resaltarlo). 4. Después del nombre de la función sin dejar espacio abra un paréntesis.
)
Vera que Excel le despliega la sintaxis de la función:
5. Seleccione con el ratón el rango que desea promediar 6. Y cierre el paréntesis y presione la tecla Enter.
Training Pro
57
Microsoft Excel
Función MAXIMO (MAX): Devuelve el valor máximo de una lista de argumentos. Sintaxis: MAX(número1,número2, ...) En donde: Número1, número2, ... máximo.
son entre 1 y 30 números para los que se desea encontrar el valor
Puede especificar argumentos que sean números, celdas vacías, valores lógicos o representaciones de números en forma de texto. Los argumentos que sean valores de error o de texto que no se puedan traducir a números causan errores. Si un argumento es una matriz o referencia, se usarán sólo los números de esa matriz o referencia. Las celdas vacías, valores lógicos, de texto o valores de error que se encuentren dentro de la matriz o referencia se ignorarán. Si el argumento no contiene números, MAX devuelve 0. Ejemplo: Dentro de la siguiente tabla el valor Máximo del rango de ventas de Enero es: 3,100 el valor Máximo del rango de ventas de Febrero es: 3,100 el valor Máximo del rango de ventas de Marzo es:
58
3,400
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Función MINIMO (MIN): Devuelve el valor mínimo de una lista de argumentos. Sintaxis: MIN(número1, número2, ...) En donde: Número1, número2, ...
son entre 1 a 30 números cuyos valores mínimos desea encontrar.
Puede especificar argumentos que sean números, celdas vacías, valores lógicos o representaciones textuales de números. Los argumentos que son valores de error o texto que no se puede traducir a números, causan errores. Si un argumento es una matriz o referencia, sólo se usan los números de esa matriz o referencia. Las celdas vacías, valores lógicos, texto o valores de error que se encuentren dentro de la matriz o referencia se ignoran. Si los argumentos no contienen números, MIN devuelve 0. Ejemplo: Dentro de la siguiente tabla el valor Mínimo del rango de ventas de Enero es:
1,800
el valor Mínimo del rango de ventas de Febrero es: 2,300 el valor Mínimo del rango de ventas de Marzo es:
Training Pro
2,400
59
Microsoft Excel
Función CONTAR (COUNT): Cuenta los números que hay en la lista de argumentos. Use CONTAR para obtener el número de entradas en un campo numérico de un rango o de una matriz de números.
Sintaxis: CONTAR(ref1, ref2, ...) En donde: Ref1, ref2,... son entre 1 y 30 argumentos que pueden contener o hacer referencia a distintos tipos de datos, pero sólo se cuentan los números. Existe dos funciones relacionadas a la de Contar, una que cuenta cualquier valor dentro de una lista de argumentos, con excepción de celdas vacías (Función CONTARA o COUNTA). Y la otra cuenta sólo aquellas celdas que se encuentren vacías dentro de un rango definido (Función CONTARBLANCO o COUNTBLANK). Ejemplos:
60
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Función SI (IF): Devuelve un valor si el argumento Prueba_lógica es VERDADERO y otro valor si dicho argumento es FALSO. Use SI para realizar una prueba condicional sobre valores y fórmulas, y para que las operaciones siguientes dependan del resultado de esta prueba. El resultado de la prueba determina el valor devuelto por la función SI. Sintaxis: =SI(condición lógica,valor si es verdadero,valor si es falso)
En donde: Condición lógica.- es cualquier expresión que se pueda evaluar como VERDADERO ó FALSO. Valor si es verdadero.- es el valor que Excel retorna si la evaluación de la condición es VERDADERO. Si prueba_lógica es VERDADERO y se omite el argumento valor_si_verdadero, la función devuelve VERDADERO. Valor si es falso.- es el valor que Excel retorna si la evaluación de la condición es FALSO o no cumple con lo requerido. Si prueba_lógica es FALSO y se omite el argumento valor_si_falso, la función devuelve FALSO. Observaciones: Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas. Vea el último de los ejemplos citados a continuación. Ejemplos:
Training Pro
61
Microsoft Excel
Supongamos que desea calificar con letras los números de referencia con el nombre Promedio. Si Promedio es
La función devuelve
Mayor que 89
A
De 80 a 89
B
De 70 a 79
C
De 60 a 69
D
Menor que 60
F
Se podría utilizar la siguiente función anidada SI: =SI(Promedio>89,"A",SI(Promedio>79,"B",SI(Promedio>69,"C", SI(Promedio>59,"D","F"))))
En el ejemplo anterior, la segunda instrucción SI representa también el argumento valor_si_falso de la primera instrucción SI. De manera similar, la tercera instrucción SI es el argumento valor_si_falso de la segunda instrucción SI. Por ejemplo, si el primer argumento prueba_lógica (Promedio>89) es VERDADERO, se devuelve el valor "A". Si el primer argumento prueba_lógica es FALSO, se evalúa la segunda instrucción SI, y así sucesivamente. • =SI(A3>B3,”Fuera de Presupuesto”,”OK”), retorna “Fuera de Presupuesto” en caso de cumplirse la condición, en caso contrario retorna “OK”. • =SI(O($A1=20,$A1=19),10,SI(O($A1=18,$A1=17),9,SI(O($A1=16,$A1=15),8,SI($A1=14,7, SI(O($A1=13,$A1=12),6,"REPROBADO"))))), retorna 10 si la celda A1 contiene un 20 o 19, 9 si contiene un 18 o 17, 8 si contiene un 16 o 15, y así sucesivamente hasta que el valor sea menor a 12, donde retornará “REPROBADO”.
62
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Función CONTAR.SI (COUNTIF): Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado.
Sintaxis: CONTAR.SI(rango, criterio) En donde: Rango: es el rango dentro del cual desea contar el número de celdas que no están en blanco. Criterio: es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar. Por ejemplo, el argumento criterio puede expresarse como 32, "32", ">32" o "manzanas". Ejemplos: Supongamos que el rango A3:A6 contiene "manzanas", "naranjas", "melones", "manzanas", respectivamente. =CONTAR.SI(A3:A6,"manzanas") es igual a 2 Supongamos que el rango B3:B6 contiene 32; 54; 75 y 86, respectivamente. =CONTAR.SI(B3:B6,">55") es igual a 2 Dentro de la siguiente tabla se cuentan aquellos valores que sean mayores a 60 de cada una de las semanas:
Training Pro
63
Microsoft Excel
Función SUMAR.SI (SUMIF): Suma las celdas en el rango que coinciden con el argumento criterio.
Sintaxis: SUMAR.SI(rango, criterio, rango_suma) En donde: Rango: es el rango de celdas que desea evaluar. Criterio: es el criterio en forma de número, expresión o texto, que determina qué celdas se van a sumar. Por ejemplo, el argumento criterio puede expresarse como 32, "32", ">32", "manzanas". Rango_suma: son las celdas que se van a sumar. Las celdas contenidas en rango_suma se suman sólo si las celdas correspondientes del rango coinciden con el criterio. Si rango_suma se omite, se suman las celdas contenidas en el argumento rango. Ejemplo 1: Supongamos que el rango A1:A4 contiene los siguientes valores de propiedad para cuatro casas: $100.000; $200.000; $300.000; $400.000; respectivamente. El rango B1:B4 contiene las siguientes comisiones de venta correspondientes a estos valores de propiedad: $7.000; $14.000; $21.000; $28.000. =SUMAR.SI(A1:A4,">160000",B1:B4) es igual a $63000 Ejemplo 2:
64
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Función BUSCARV (VLOOKUP): Busca un valor específico en la columna del extremo izquierdo de una matriz y devuelve el valor de la celda indicada. Utilice BUSCARV en lugar de BUSCARH cuando sus valores de comparación se encuentren en una columna situada a la izquierda o a la derecha de los datos que desea encontrar. Sintaxis: BUSCARV(valor_buscado,matriz_de_comparación,indicador_columnas,ordenado) En donde: Valor_buscado: es el valor que se busca en la primera columna de la matriz. Valor_buscado puede ser un valor, una referencia o una cadena de texto. Matriz_de_comparación: es el conjunto de información donde se buscan los datos. Utilice una referencia a un rango o un nombre de rango. • Los valores de la primera columna del argumento matriz_de_comparación deben colocarse en orden ascendente: ...; -2; -1; 0; 1; 2; ... ; A-Z; FALSO; VERDADERO. De lo contrario, BUSCARV podría devolver un valor incorrecto. • Para colocar los valores en orden ascendente, elija el comando Ordenar del menú Datos y seleccione la opción "Ascendente". • Los valores de la primera columna de matriz_de_comparación pueden ser texto, números o valores lógicos. • El texto escrito en mayúsculas y minúsculas es equivalente. Indicador_columnas: es el número de columna de la matriz_de_comparación desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_de_comparación; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_de_comparación y así sucesivamente. Si indicador_columnas es menor que 1, BUSCARV devuelve el valor de error #¡VALOR!; si indicador_columnas es mayor que el número de columnas de matriz_de_comparación, BUSCARV devuelve el valor de error #¡REFERENCIA!. Ordenado: Es un valor lógico que indica si desea que la función BUSCARV busque un valor igual o aproximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto, devolverá el valor inmediatamente menor que valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error #N/A.
Training Pro
65
Microsoft Excel
Observaciones: • Si BUSCARV no puede encontrar valor_buscado y ordenado es VERDADERO, utiliza el valor más grande que sea menor o igual a valor_buscado. • Si valor_buscado es menor que el menor valor de la primera columna de matriz_de_comparación, BUSCARV devuelve el valor de error #N/A. • Si BUSCARV no puede encontrar valor_buscado y ordenado es FALSO, devuelve el valor de error #N/A. Ejemplo 1:
En la hoja de cálculo anterior, donde el rango A4:C12 se denomina Rango: =BUSCARV(1,Rango,1,VERDADERO) es igual a 0.946 =BUSCARV(1,Rango,2) es igual a 2.17 =BUSCARV(1,Rango,3,VERDADERO) es igual a 100 =BUSCARV(0.746,Rango,3,FALSO) es igual a 200 =BUSCARV(0.1,Rango,2,VERDADERO) es igual a #N/A debido a que 0.1 es menor menor valor de la columna A
que el
=BUSCARV(2,Rango,2,VERDADERO) es igual a 1.71
66
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Ejemplo 2:
Training Pro
67
Microsoft Excel
Función BUSCARH (HLOOKUP): Examina la fila superior de una matriz en busca de un valor particular y devuelve el valor a la celda indicada. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda o a la derecha de los datos que desee encontrar. Sintaxis: BUSCARH(valor_buscado, matriz_buscar_en, indicador_filas, ordenado) En donde: Valor_buscado: es el valor que se busca en la primera fila de matriz_buscar_en. Valor_buscado: puede ser un valor, una referencia o una cadena de texto. Matriz_buscar_en: es una tabla de información en la que se buscan los datos. Utilice una referencia a un rango o el nombre de un rango. • Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, números o valores lógicos. • Si el argumento ordenado es VERDADERO, los valores de la primera fila del argumento matriz_buscar_en deberán colocarse en orden ascendente: ...-2; -1; 0; 1; 2;..., A-Z, FALSO, VERDADERO; de lo contrario, es posible que BUSCARH no devuelva el valor correcto. • El texto en mayúsculas y minúsculas es equivalente. • Se pueden poner los datos en orden ascendente seleccionando los valores, eligiendo el comando Ordenar del menú Datos y luego los criterios "Ordenar columnas" y "Ascendente". Indicador_filas: es el número de fila en matriz_buscar_en desde el cual se deberá devolver el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en, y así sucesivamente. Si indicador_filas es menor que 1, BUSCARH devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve el valor de error #¡REF! Ordenado: es un valor lógico que especifica si desea que el elemento buscado por la función BUSCARH coincida exacta o aproximadamente. Si ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no se encuentra un valor exacto, se devuelve el mayor valor que sea menor que el argumento valor_buscado. Si ordenado es FALSO, la función BUSCARH encontrará el valor exacto. Si no se encuentra dicho valor, devuelve el valor de error #N/A.
68
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Observaciones: • Si BUSCARH no logra encontrar valor_buscado, utiliza el mayor valor que sea menor que valor_buscado. • Si valor_buscado es menor que el menor valor de la primera fila de matriz_buscar_en, BUSCARH devuelve el valor de error #N/A. Ejemplo:
Training Pro
69
Microsoft Excel
Función CONCATENAR (CONCATENATE) Concatena (une) argumentos de texto.
Sintaxis: CONCATENAR(Texto1,Texto2…..) En donde: Texto1, Texto2….. son de 1 a 30 elementos de texto que serán unidos en un elemento de texto único. Los elementos de texto pueden ser cadenas de texto, números o referencias a celdas. Observaciones: Puede usar el operador “&” en lugarde la función Concatenar para unir elementos de texto. Ejemplo 1: =CONCATENAR(“Valor ” , “Total”) es igual a “Valor Total”. Esto equivale a escribir: ="Valor"&" "&"Total" Ejemplo 2:
70
Training Pro
Mejores prácticas para manejo de Hojas de Cálculo
Función Y (AND): Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o más argumentos son FALSO.
Sintaxis: Y(valor_lógico1, valor_lógico2, ...) En donde: Valor_lógico1, valor_lógico2, ... son de 1 a 30 condiciones que desea comprobar y que pueden ser VERDADERO o FALSO. • Los argumentos deben ser valores lógicos, matrices o referencias que contengan valores lógicos. Si un argumento matricial o de referencia contiene texto o celdas vacías, esos valores se ignoran. • Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR! Ejemplos: =Y(VERDADERO, VERDADERO) es igual a VERDADERO =Y(VERDADERO, FALSO) es igual a FALSO =Y(2+2=4, 2+3=5) es igual a VERDADERO Si B1:B3 contiene los valores VERDADERO, FALSO y VERDADERO, entonces: =Y(B1:B3) es igual a FALSO Si B4 contiene un número entre 1 y 100, entonces: =Y(1