Manual de Excel Profesional (2016)

Otro Español
Universidad Universidad de Barcelona (UB)
Grado Administración y Dirección de Empresas - 6º curso
Asignatura Aprenentatge Computacional
Año del apunte 2016
Páginas 202
Fecha de subida 20/07/2017
Descargas 1
Subido por

Descripción

¿Quieres aprender a utilizar Excel Profesional? Aquí te dejo un manual en el cual aprenderás a utilizar Excel a un nivel Avanzado.

Vista previa del texto

OBJETIVO DEL CURSO Obtener los conocimientos básicos, características y funciones principales de una Hoja de Cálculo en Excel, así como el uso práctico que les permitirá trabajar con gran facilidad y rapidez con bases de datos y tablas de información.
Ing. Ricardo Cacique Instructor MANUAL DE EXCEL PROFESIONAL IPCE I. Contenido I.
Contenido ---------------------------------------------------------------------------------------------------------------- 1 II.
Introducción ------------------------------------------------------------------------------------------------------------ 5 III.
Familiarizándonos con Excel ---------------------------------------------------------------------------------- 6 ¿Cómo entrar al programa? ------------------------------------------------------------------------------------------------ 6 Pantalla inicial y sus componentes fundamentales ----------------------------------------------------------------- 7 Barra de herramientas de acceso rápido: ------------------------------------------------------------------------------------------------ 8 La barra de título. --------------------------------------------------------------------------------------------------------------------------------- 9 Barra de opciones ------------------------------------------------------------------------------------------------------------------------------- 10 Barra de fórmulas ------------------------------------------------------------------------------------------------------------------------------- 10 Barra de etiquetas. ------------------------------------------------------------------------------------------------------------------------------ 11 Barra de estado. --------------------------------------------------------------------------------------------------------------------------------- 11 IV.
Ficha de Archivo ------------------------------------------------------------------------------------------------- 12 Botón de Información ------------------------------------------------------------------------------------------------------ 12 Comenzar con un Nuevo Libro en Blanco ---------------------------------------------------------------------------- 12 Cerrar un libro de trabajo ------------------------------------------------------------------------------------------------- 13 Abrir un archivo previo ---------------------------------------------------------------------------------------------------- 13 Guardar un libro de trabajo ----------------------------------------------------------------------------------------------- 13 Configuración de opciones de Excel ----------------------------------------------------------------------------------- 15 Pedir Ayuda a Excel -------------------------------------------------------------------------------------------------------- 15 V.
Conceptos básicos de Excel ------------------------------------------------------------------------------------- 16 ¿Qué son los Libro de trabajo? ------------------------------------------------------------------------------------------ 16 ¿Qué son las hojas de cálculo? ------------------------------------------------------------------------------------------ 16 ¿Qué contienen las Hojas de Cálculo? -------------------------------------------------------------------------------- 17 Columnas ------------------------------------------------------------------------------------------------------------------------------------------- 17 Filas --------------------------------------------------------------------------------------------------------------------------------------------------- 18 Celdas ------------------------------------------------------------------------------------------------------------------------------------------------ 18 Movimientos rápidos en la Hoja de Cálculo ------------------------------------------------------------------------- 19 Introducción de datos en las Celdas. ---------------------------------------------------------------------------------- 20 Errores en la introducción de los datos. ------------------------------------------------------------------------------ 22 Manipulando Celdas -------------------------------------------------------------------------------------------------------- 23 Selección de celdas adyacentes y no contiguas ------------------------------------------------------------------------------------ 24 Copiar y pegar ---------------------------------------------------------------------------------------------------------------- 26 Copiar y pegar celdas utilizando el Portapapeles. --------------------------------------------------------------------------------- 27 Copiar y pegar sin utilizar el portapapeles. ------------------------------------------------------------------------------------------- 27 Pegado especial. --------------------------------------------------------------------------------------------------------------------------------- 29 Cortar o mover Celdas ------------------------------------------------------------------------------------------------------------------------- 33 VI.
Formato de Celdas. --------------------------------------------------------------------------------------------- 34 Formato de la fuente ------------------------------------------------------------------------------------------------------- 34 Alineación ---------------------------------------------------------------------------------------------------------------------- 36 Bordes. -------------------------------------------------------------------------------------------------------------------------- 39 Relleno -------------------------------------------------------------------------------------------------------------------------- 40 Formato de valores numéricos o textos ------------------------------------------------------------------------------ 42 VII.
Formato Condicional ------------------------------------------------------------------------------------------- 44 Resaltar reglas de celdas -------------------------------------------------------------------------------------------------- 45 Ejemplo Es Mayor que… ---------------------------------------------------------------------------------------------------------------------- 46 Ejemplo Es Menor que… ---------------------------------------------------------------------------------------------------------------------- 46 Ejemplo “Entre…” -------------------------------------------------------------------------------------------------------------------------------- 47 Ejemplo “Es igual a…” -------------------------------------------------------------------------------------------------------------------------- 48 Ejemplo “Texto que contiene…” ------------------------------------------------------------------------------------------------------------ 49 Ejemplo “Una Fecha…” ------------------------------------------------------------------------------------------------------------------------ 51 Ejemplo de “Duplicar valores…” ----------------------------------------------------------------------------------------------------------- 52 Reglas superiores o inferiores.------------------------------------------------------------------------------------------- 53 Ejemplo “10 superiores” ---------------------------------------------------------------------------------------------------------------------- 54 Ejemplo “10% de valores superiores” --------------------------------------------------------------------------------------------------- 55 Ejemplo “10 inferiores…” --------------------------------------------------------------------------------------------------------------------- 55 Ejemplo “10% de Valores Inferiores…” -------------------------------------------------------------------------------------------------- 56 Ejemplo “Por encima del promedio…” --------------------------------------------------------------------------------------------------- 57 Ejemplo “Por debajo del promedio…” ---------------------------------------------------------------------------------------------------- 58 Formato Condicional - Barra de Datos -------------------------------------------------------------------------------- 60 Formato Condicional – Escalas de color. ----------------------------------------------------------------------------- 61 Formato Condicional – Conjunto de Íconos. ------------------------------------------------------------------------- 62 Administrar reglas de formatos condicionales. --------------------------------------------------------------------- 63 Nueva Regla --------------------------------------------------------------------------------------------------------------------------------------- 64 Estilos de Celdas ------------------------------------------------------------------------------------------------------------- 68 VIII.
Cambios de estructura de las celdas, filas o columnas. ------------------------------------------- 70 IX.
Modificar ----------------------------------------------------------------------------------------------------------- 72 ¿Qué son las funciones? --------------------------------------------------------------------------------------------------- 72 Autosuma y sus funciones más frecuentes.------------------------------------------------------------------------- 74 Función de Contar. ------------------------------------------------------------------------------------------------------------------------------ 74 Función de SUMA. ------------------------------------------------------------------------------------------------------------------------------ 75 Función Promedio. ------------------------------------------------------------------------------------------------------------------------------ 75 Función Max--------------------------------------------------------------------------------------------------------------------------------------- 76 Función Min --------------------------------------------------------------------------------------------------------------------------------------- 76 Ordenar ------------------------------------------------------------------------------------------------------------------------- 77 Filtrar ---------------------------------------------------------------------------------------------------------------------------- 80 Buscar, Buscar y reemplazar. -------------------------------------------------------------------------------------------- 86 Ir a… ------------------------------------------------------------------------------------------------------------------------------ 88 Ir a Especial… ----------------------------------------------------------------------------------------------------------------- 89 X.
Creación de Gráficos ----------------------------------------------------------------------------------------------- 91 Iniciar a crear un gráfico -------------------------------------------------------------------------------------------------- 91 Personalización de un gráfico-------------------------------------------------------------------------------------------- 93 Agregar elemento de gráfico --------------------------------------------------------------------------------------------------------------- 93 Diseño rápido ------------------------------------------------------------------------------------------------------------------------------------- 94 Cambiar Colores---------------------------------------------------------------------------------------------------------------------------------- 96 Estilos de diseño --------------------------------------------------------------------------------------------------------------------------------- 98 Cambiar entre filas y columnas. ----------------------------------------------------------------------------------------------------------- 99 Seleccionar datos. ----------------------------------------------------------------------------------------------------------------------------- 100 Cambiar Gráfico -------------------------------------------------------------------------------------------------------------------------------- 104 Mover gráfico ----------------------------------------------------------------------------------------------------------------------------------- 106 Formato a la gráfica -------------------------------------------------------------------------------------------------------------------------- 107 XI.
Biblioteca de Funciones ------------------------------------------------------------------------------------- 112 Ejemplo de aplicación de funciones. ---------------------------------------------------------------------------------- 117 AÑO ------------------------------------------------------------------------------------------------------------------------------------------------- 117 DIA -------------------------------------------------------------------------------------------------------------------------------------------------- 118 MES ------------------------------------------------------------------------------------------------------------------------------------------------- 119 DIASEM ------------------------------------------------------------------------------------------------------------------------------------------- 120 FECHA --------------------------------------------------------------------------------------------------------------------------------------------- 121 FRAC.AÑO ---------------------------------------------------------------------------------------------------------------------------------------- 122 ABS -------------------------------------------------------------------------------------------------------------------------------------------------- 123 ALEATORIO -------------------------------------------------------------------------------------------------------------------------------------- 124 ALEATORIO.ENTRE --------------------------------------------------------------------------------------------------------------------------- 125 ENTERO ------------------------------------------------------------------------------------------------------------------------------------------- 126 REDONDEAR ------------------------------------------------------------------------------------------------------------------------------------ 126 REDONDEAR.MAS ---------------------------------------------------------------------------------------------------------------------------- 127 REDONDEAR.MENOS------------------------------------------------------------------------------------------------------------------------ 128 SUBTOTALES ------------------------------------------------------------------------------------------------------------------------------------ 129 SUMA----------------------------------------------------------------------------------------------------------------------------------------------- 136 SUMAPRODUCTO ----------------------------------------------------------------------------------------------------------------------------- 138 SUMAR.SI ----------------------------------------------------------------------------------------------------------------------------------------- 140 SUMAR.SI.CONJUNTO ----------------------------------------------------------------------------------------------------------------------- 145 TRUNCAR ----------------------------------------------------------------------------------------------------------------------------------------- 149 CONTAR ------------------------------------------------------------------------------------------------------------------------------------------- 151 MAX------------------------------------------------------------------------------------------------------------------------------------------------- 153 MIN-------------------------------------------------------------------------------------------------------------------------------------------------- 154 PROMEDIO --------------------------------------------------------------------------------------------------------------------------------------- 155 PROMEDIO.SI ----------------------------------------------------------------------------------------------------------------------------------- 157 BUSCARV ----------------------------------------------------------------------------------------------------------------------------------------- 159 CONCATENAR ---------------------------------------------------------------------------------------------------------------------------------- 163 DERECHA ----------------------------------------------------------------------------------------------------------------------------------------- 165 EXTRAE ------------------------------------------------------------------------------------------------------------------------------------------- 166 HALLAR ------------------------------------------------------------------------------------------------------------------------------------------- 168 IZQUIERDA -------------------------------------------------------------------------------------------------------------------------------------- 169 LARGO --------------------------------------------------------------------------------------------------------------------------------------------- 171 MAYUSC ------------------------------------------------------------------------------------------------------------------------------------------ 172 NOMPROPIO ------------------------------------------------------------------------------------------------------------------------------------ 174 SI ----------------------------------------------------------------------------------------------------------------------------------------------------- 175 XII.
Proteger Datos ------------------------------------------------------------------------------------------------- 177 Proteger hoja. ---------------------------------------------------------------------------------------------------------------- 177 Proteger estructura del Libro. ------------------------------------------------------------------------------------------ 178 Cifrar una con contraseña------------------------------------------------------------------------------------------------ 180 XIII.
Inmovilizar ------------------------------------------------------------------------------------------------------- 180 XIV.
Diseño de página ---------------------------------------------------------------------------------------------- 184 Temas --------------------------------------------------------------------------------------------------------------------------- 184 Configurar página. ---------------------------------------------------------------------------------------------------------- 185 XV.
Anexos------------------------------------------------------------------------------------------------------------- 197 Anexo 1. Pantalla Inicial -------------------------------------------------------------------------------------------------- 197 Anexo 2. Errores típicos -------------------------------------------------------------------------------------------------- 198 Anexo 3. Diferencia entre fórmula y función ---------------------------------------------------------------------- 199 Anexo 4. Movimientos rápidos ----------------------------------------------------------------------------------------- 200 Anexo 5. Teclas de funciones. ------------------------------------------------------------------------------------------ 201 II. Introducción ¿Qué es Excel? Es un software de Microsoft® que nos permite trabajar en hojas de cálculo, crear tablas de información, administrar base de datos de gran volumen, generar reportes concisos, crear diversos gráficos, calcular y analizar información. Hay que recordar que las hojas de cálculo son una herramienta que nos ayudan a simplificar el trabajo y optimizar el tiempo, sin embargo, debemos tener en cuenta que el usuario deberá ser creativo al utilizar las funciones y herramientas de Excel para llegar a explotar el programa de Excel.
Una vez que vayas aprendiendo a utilizar Excel, te vas a dar cuenta que las hojas de cálculo son fáciles e intuitivas. No se requiere de conocimientos complejos o específicos para poder utilizar el programa, salvo aquellas excepciones como algunas funciones financieras, estadísticas o de ingeniería que necesitas un conocimiento previo.
Cualquier persona puede utilizar el programa y se puede utilizar para un sinfín de cosas, tanto en lo personal como en lo profesional.
Para este curso conocerás las funciones principales de Excel 2016 que más se utilizan en la actualidad y serás capaz de entender el manejo de datos con las hojas de cálculo utilizando funciones y herramientas simples.
Cabe destacar que las funciones y conceptos de Excel 2016 son muy similares a las versiones anteriores, por lo que se te hará familiar identificarlas en versiones anteriores. En algunos casos resaltaremos las diferencias que puedan existir entre las versiones, que en realidad son pocas.
III. Familiarizándonos con Excel ¿Cómo entrar al programa? Existen dos opciones de entrar al programa: 1. Dando clic en el botón de Windows 10 y se desplegará un menú y aparecerá una lista con los programas instalados; busca ícono de Microsoft Excel 2016 para abrir el programa.
2. Buscar el ícono si está en la barra de tareas de Microsoft ® o en el escritorio de nuestro ordenador. Al hacer doble clic iniciaremos Microsoft Excel 2010, 2013 o 2016.
Pantalla inicial y sus componentes fundamentales Al iniciar Excel mostrara del lado izquierdo un listado de archivos recientes. Del lado derecho y la mayor parte de la pantalla observaras plantillas predefinidas por el programa, desde un libro en blanco hasta plantillas que te ayudan administrar una agenda, presupuesto personal, informes de ventas, entre otras cosas. Estas plantillas predefinidas se pueden buscar y descargar desde internet y pueden ser útiles para el usuario, sin embargo, en algunas plantillas son difíciles de adaptar o hacer modificaciones en ellas.
Para ingresar en una hoja nueva, pulsaremos con un clic libro en Blanco: Inmediatamente aparecerá la hoja de cálculo y reconoceremos la estructura de menús y barras importantes del programa. Es importante identificarlos y conocerlos los nombres de los diferentes menús y barras ya que nos será útil identificarlos en el transcurso del curso. En la siguiente imagen se muestra los componentes de la pantalla.
Barra de herramientas de acceso rápido: Contiene los íconos más habituales de Excel como Guardar, deshacer, rehacer, abrir y nuevo. Sin embargo, también podemos personalizar la barra con los botones o funciones que deseemos y utilizamos con mayor frecuencia.
Al dar clic derecho en la barra de herramientas de acceso rápido, desplegará un cuadro de diálogo y pulsamos Personalizar barra de herramientas de acceso rápido… Mostrará un cuadro de diálogo que está compuesto por dos bloques. El bloque izquierdo muestra los íconos que se desean agregar a la barra de acceso rápido, estos íconos se seleccionan y se agregan al segundo bloque. El segundo bloque está del lado derecho y muestra los íconos que se agregaron y se observaran en la barra de acceso rápido. En la siguiente figura se muestra un ejemplo de agregar botones a la barra de acceso rápido: La barra de título.
Se encuentra en la parte superior de la pantalla inicial y nos muestra el nombre del documento sobre el que se está trabajando en ese momento. Cuando abrimos la pantalla inicial, se crea un nombre provisional “Libro 1” hasta que lo guardemos y renombremos.
Barra de opciones Contiene todas las opciones separadas por pestañas. Esta cinta de opciones es uno de los elementos que más utilizamos en cualquiera de las versiones de Excel. Al hacer clic sobre la pestaña accedemos de la ficha de opciones y observamos los botones de la pestaña.
Como podemos observar cada una de las pestañas tiene botones específicos que hacen referencia a la pestaña. Es importante tenerlas identificadas. Las pestañas más importantes y utilizadas son Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar y Vista.
Barra de fórmulas Nos indica la celda que está activa, además mostrará el contenido de la celda activa. En esta barra tendremos la opción de capturar textos, variables, números, formulas y funciones.
Barra de etiquetas.
En cada libro de Excel tenemos hojas de cálculo. Cada Libro de Excel que abrimos nos muestra por default una hoja de cálculo y podemos agregar hasta 256 hojas de cálculo en un Libro de Excel. Ver ejemplo de hojas de cálculo en la barra de tiquetas: Barra de estado.
Muestra indicadores cuando seleccionamos datos (Ver siguiente imagen). En la barra de estado podemos personalizar los indicadores que deseamos ver. Para personalizar, debemos dar clic derecho sobre la barra de estado y activamos los indicadores que deseamos.
IV. Botón de Archivo En el botón de Archivo nos muestra del lado izquierdo el menú, en el cual podremos crear Nuevo Libro de Excel, Abrir un libro de Excel, Guardar, Guardar Como, Imprimir, Compartir, Exportar, Publicar y Cerrar el archivo actual. También incluye la información del Usuario y Opciones avanzadas. Del lado derecho muestra las opciones de cada menú. Ver ejemplo: Comenzar con un Nuevo Libro en Blanco Si queremos crear un nuevo libro de Excel. Esta operación se llama “Nuevo”. En la siguiente imagen observaras plantillas prediseñadas por Microsoft ® de las cuales podrás descargar de Internet. Sin embargo, para fines de este curso, utilizaremos la opción “Libro en Blanco”.
Cerrar un libro de trabajo La operación de salir de un documento recibe el nombre de “Cerrar”. Esta operación la utilizaremos cuando terminemos de trabajar un Libro de Excel. En el caso que se te haya olvidado Guardar, el programa te mandará un recordatorio preguntándote si lo deseas guardar. También puedes cerrar el libro de trabajo con el botón “X” que está en la parte superior derecha de tú pantalla.
Abrir un archivo previo Para abrir un archivo ya existente desde Excel selecciona la opción “Abrir” del menú Archivo. En el cuadro de dialogo te mostrará los archivos recientes que hayas utilizado. También tendrás la opción de buscar el archivo desde el botón “Equipo”. Cualquiera de las dos opciones podrás Abrir un documento que previamente hayas trabajado.
Guardar un libro de trabajo Cuando empezamos a trabajar en Excel, lo que necesitamos es guardar el trabajo realizado y poder recuperarlo de nueva cuenta. Existen dos formas de Guardar un archivo: • Guardar como: Este botón se utiliza cuando se guarda un archivo por primera vez o se desea guardar un archivo (previamente guardado) pero en otra carpeta o guardarlo con otro nombre. Al dar clic en el botón “Guardar como” aparecerá un cuadro de dialogo, seleccionaremos la carpeta en donde deseamos guardar el documento, asignaremos un nombre al archivo y damos clic en el botón “Guardar” del cuadro de dialogo.
• Guardar: Esta opción es para sobrescribir los cambios que hayamos realizado en el documento. Podemos dar clic en el botón “Guardar” o dar clic en el siguiente botón que se encuentra en la barra de herramientas de acceso rápido. Cualquiera de las dos opciones nos respeta la ruta de donde se guardó por primera vez.
Configuración de opciones de Excel Al dar clic en este botón que se encuentra en “Archivo”, aparecerá un cuadro de dialogo, en el cual tendrás la opción de cambiar la configuración del programa de Excel. Sin embargo, se recomienda que no se modifique si no se tiene el conocimiento preciso para evitar des configuración total del programa.
Pedir Ayuda a Excel Existe un botón que tiene un signo de interrogación cerrado “?” ubicado en la parte superior derecha, o en el botón de ayuda para versiones anteriores. También puedes teclear F1. Cualquiera de las opciones se abrirá un cuadro de dialogo, en el cual podrás hacer la consulta por medio de un buscador de Microsoft ®. Este botón es muy útil para consultar funciones o solicitar ayuda de alguna herramienta de Excel. Lo más importante de solicitar ayuda es hacer la pregunta correcta, ya que te pude dirigir otras opciones, pero menos la que te interesa. Otra de las desventajas de la “Ayuda” es que en las últimas versiones de Excel necesitas estar conectado a Internet para poder consultar los tutoriales.
V. Conceptos básicos de Excel Hasta este momento ya hemos estado familiarizándonos con la hoja de inicio de Excel y el botón de Archivo, que son importantes como conceptos simples y así poder empezar a trabajar en Excel. Sin embargo, hay que tomar en cuenta otros conceptos básicos que nos permitirán conocer la forma como trabajar una hoja de cálculo.
¿Qué son los Libro de trabajo? Se le conoce Libros de trabajo a los archivos creados por Excel. La extensión que se conoce a un libro de trabajo es .xlsx Cuando creamos o ingresamos a Excel, en la barra de título observaremos un nombre provisional llamado “Libro1”. Cada vez que abrimos un nuevo libro de trabajo, el número de Libro irá variando dependiendo cuantos hayan creado en una sección.
¿Qué son las hojas de cálculo? Cada libro de trabajo contiene hojas de cálculo. Las hojas de cálculo las identificamos en la barra de etiquetas y usualmente el programa nos presenta tres hojas de cálculo en el momento que se crea un libro de trabajo. El número de estas puede variar entre 1 a 256 hojas de cálculo, podemos agregar o eliminarlas, cambiar el nombre, cambiar el color, proteger la hoja, o esconderlas del libro de trabajo (que es diferente a eliminarlas). Las hojas de cálculo nos sirven para separar bases de datos. Ejemplos: Independientemente del tema podemos observar que las hojas de cálculo son útiles para organizar datos e información.
Para editar las etiquetas, solo se tiene que dar clic en el botón derecho del mouse y se desplegará un cuadro de dialogo, del cual es intuitivo al seleccionar cualquier opción que queramos modificar. Puedes modificar el color, nombre, crear más hojas, eliminarlas, etc.
¿Qué contienen las Hojas de Cálculo? Las hojas de cálculo son muy útiles para todas aquellas personas que trabajen con un gran volumen de datos y necesitan realizar operaciones y cálculos en ellos. Las hojas de cálculo están conformadas por FILAS y COLUMNAS que al interceptarse forman CELDAS. Cada hoja de cálculo contiene 1,048,576 filas y 16,384 columnas lo que equivale a 17,179,869,184 celdas por hoja de cálculo. Lo nos da como referencia la capacidad que tiene Excel para el manejo de datos.
Columnas Las columnas son un conjunto de celdas seleccionadas verticalmente. Cada columna se nombra de la A a la Z, de la AA a la AZ, de la BA a la BZ y así sucesivamente hasta tener la última columna XFD.
Filas Las filas son un conjunto de celdas seleccionadas horizontalmente. Cada Fila se enumera desde el 1 hasta la fila 1,048,576.
Celdas Las celdas se forman con la intercepción de una columna y una fila. Esta intercepción se nombra con el nombre de la columna y el número de la fila que intercepta. Por ejemplo, la primera celda le pertenece a la columna A y a la Fila 1, por lo tanto, la celda se llama A1.
Para identificar las celdas activas, lo podemos observar de dos maneras (ver ejemplo): 1) el cursor aparece más remarcado que las otras celdas (en este caso color verde); 2) en la barra de fórmulas nos indica que celda está activa B3.
Como se observa en la figura anterior, la celda activa es la B3. En la hoja de cálculo se remarca la celda activa y se sombrea la columna y la fila que intercepta la celda activa. También podemos ver la celda activa en la barra de fórmulas indicando el nombre de la celda activa.
Movimientos rápidos en la Hoja de Cálculo Cuando utilizamos una hoja de cálculo es porque necesitamos practicidad en hacer las operaciones con información que en ocasiones es de un volumen considerable y es complicado movernos con las barras de desplazamiento o con el mouse. Por lo que en Excel existen movimientos rápidos que nos ayudan a movernos con la combinación de teclas sin utilizar el mouse. Estos movimientos rápidos son muy comunes y utilizados por las personas que utilizan este programa. La gran ventaja: Movilidad en la(s) hoja(s) de cálculo y Libros de trabajo. Los movimientos rápidos que debemos aprendernos son los siguientes (ver anexo 4 para más movimientos rápidos): Introducción de datos en las Celdas.
En cada una de las celdas es posible introducir datos, textos, números, fórmulas, funciones u otra variable. En cualquier caso los pasos a seguir serán los siguientes: 1. Posicionarte en la celda que desees introducir datos 2. Introducir el valor en la celda. Observarás como se activa el cursor 3. Teclear “Intro” o mover el cursor. En esta acción, la hoja de cálculo validará el valor introducido, en caso de error te notificara por medio de un cuadro de texto del error.
4. Para modificar valores, se debe oprimir la tecla F2, hacer doble clic en la celda que se pretenda modificar o modificarlo desde la barra de fórmulas.
NOTA: Para introducir una formula o función, siempre se debe comenzar con el signo “=” o bien con el signo “+”, al hacer esto, la hoja de cálculo va reconocer que vas a introducir una formula o una función.
Ejemplos: NOTA: Una fórmula es diferente a una función, la fórmula puede estar constituida por valores constantes, referencia a otras celdas, nombre y funciones. Una función esta predefinida por el programa. Por ejemplo: una suma se puede hacer con una formula sumando números constantes, sumar celdas con números constantes, sumar una función con números constantes, sumar más de dos funciones, etc. La otra manera es solamente utilizar la función de SUMA. Debemos tener cuidado al diferenciar una fórmula de una función, ya que esto puede ocasionar errores. Más adelante analizaremos estos detalles.
Errores en la introducción de los datos.
Cuando introducimos por error datos o valores incorrectos en la celda y Excel lo detecta, despliega un cuadro de dialogo auxiliándonos sobre el error. El cuadro de dialogo puede variar según el error detectado por Excel.
Ejemplo de un cuadro de dialogo cuando Excel detecta un error: En otros casos Excel no despliega el cuadro de dialogo, pero si nos hace una referencia con un símbolo color verde en la esquina superior izquierda de la celda como se muestra en la siguiente figura: Al hacer clic sobre el símbolo aparece un cuadro de dialogo que nos permitirá saber más sobre el error: Este cuadro nos dice la fórmula es incorrecta y nos da opciones para evaluar el error y poder corregir el error o en su caso omitir si fuera el caso.
A continuación te mostramos ejemplos de los errores más comunes y podrás identificar para corregirlos: • Cuando la celda muestra #####: significa que el ancho de columna no es suficiente.
También cuando escribes una fecha o una hora con un signo negativo.
• Cuando se muestra #¡DIV/0: nos indica que el divisor es “0” y no se pude calcular una división con divisor 0.
• #¿NOMBRE?: Al escribir mal una función • Cuando valor no está disponible para una función o fórmula nos muestra #N/A • #¡REF! : Cuando se rompe la referencia de una celda o un vínculo entre celdas.
• #¡VALOR! : Cuando Excel no puede calcular el valor de una fórmula o función, por hacer mal referencia a una celda(s).
Manipulando Celdas Otras de las funciones básicas e importantes en Excel son la manipulación de celdas o selección de rangos de celdas. La mayoría de las funciones te pide seleccionar una celda o seleccionar un rango de celdas. A continuación encontraras algunos métodos de selección más utilizados.
Selección de celdas adyacentes y no contiguas Cuando iniciamos Excel, el puntero del mouse se ve como una cruz grande de color blanca esto nos da como referencia que podemos seleccionar celdas. Cuando seleccionamos una celda solo tenemos que llevar el mouse a la celda y dar un clic con botón izquierdo e inmediatamente activa la celda. Sin embargo, si queremos seleccionar un rango de celdas adyacentes existen diferentes maneras: • En la siguiente figura veremos un ejemplo: Con el mouse seleccionas la primera celda (B3) y mantienes pulsado el botón izquierdo mientras vas arrastrando hacia la última celda (D9), cuando sueltas el botón observarás como las celdas seleccionadas aparecen en un marco alrededor y cambian de color a un gris obscuro.
De esta misma manera lo puedes hacer con el teclado: primero seleccionas la celda B3 y con la tecla Shift (sin soltar la tecla) y ahora te mueves con el cursor del teclado hasta llegar a la celda D9 y sueltas la tecla Shift y obtenemos el mismo rango de celdas pero ahora con el teclado.
• Para seleccionar una columna completa existen dos maneras, con el mouse y con el teclado.
Primero identificar la columna y le damos un clic al identificador superior de la columna a seleccionar. Primero observaremos como cambia el mouse con una Flecha de color negro al ver esto podremos dar clic y se seleccionará la columna completa.
Con el teclado nos posicionamos en cualquier celda de la columna a seleccionar y primero oprimimos la tecla Ctrl y enseguida, sin soltar la tecla Ctrl, tecleamos la barra espaciadora y soltamos. Haciendo esta acción se seleccionará la columna donde se encuentra la celda activa.
• Para seleccionar una fila completa. Primero identificamos la fila a seleccionar y hacemos clic en el identificativo izquierdo de la fila e inmediatamente se seleccionará toda la fila.
Con el teclado nos posicionamos en cualquier celda de la fila a seleccionar y primero oprimimos la tecla Shift y en seguida, sin soltar la tecla Shift, tecleamos la barra espaciadora y soltamos.
• En ocasiones es requerido seleccionar celdas no contiguas, para estos casos tendríamos que utilizar la tecla Ctrl y sin soltar y enseguida hacer clic a las celdas no contiguas. De este mismo modo funciona para seleccionar filas o columna no contiguas.
Copiar y pegar La operación de copiar (Ctrl+C) significa que estamos dando la instrucción a la hoja de cálculo de duplicar la celda(s) y la opción de pegar (Ctrl+V) damos la instrucción en cuál celda(s) se duplicará. Sin embargo, la función de pegado tiene en algunos casos su particularidad en una hoja de Excel, principalmente en pegar celdas que contienen fórmulas o funciones. Antes de entrar al tema de pegado especial, describiremos de forma breve cómo funciona el portapapeles, ya que puede ser útil en algunos casos.
Copiar y pegar celdas utilizando el Portapapeles.
Esta opción se encuentra en la pestaña de Inicio en la cinta de opciones en la parte superior izquierda: Al hacer clic en el portapapeles se abre una ventana en la cual podemos administrar hasta 25 datos, imágenes, variables, etc. El Portapapeles es práctico para administrar valores que necesitamos pegar constantemente. Aquí no importa si la celda tiene una fórmula o una función, ya que para el portapapeles solo toma la variable constante. Por ejemplo, en la siguiente imagen observamos que hay 5 elementos copiados de los cuales podemos pegar en cualquier celda: Para pegar, seleccionamos la celda y damos clic en el elemento que se desea pegar. Para limpiar el portapapeles hay que dar clic en el botón de Borrar todo.
Copiar y pegar sin utilizar el portapapeles.
Existen diferentes maneras para copiar y pegar celdas. La más común y utilizado es con el teclado.
Para copiar una celda tecleamos Crtl+C o hacemos clic en el botón copiar en la cinta de opciones de Inicio y en ese momento la hoja de cálculo reconoce la instrucción y observamos la celda activa con líneas intermitentes señalando que celda se quiere duplicar.
El pegado se realiza desde el botón de “Pegar” desde la cinta de opciones de la pestaña Inicio. También se puede ser desde el teclado con Crtl+V. En ese instante observaremos un cuadro de texto, en el cual nos pide seleccionar el tipo de pegado especial, el cual veremos más adelante en otro inciso.
Esta opción de pegado la utilizamos para pegar valores constantes. Si la celda tiene una formula o función debemos tener cuidado de lo que deseamos que nos pegue.
Pegado especial.
Esta opción es común utilizarla cuando estamos trabajando con función y formulas. En ocasiones se presenta que necesitamos pegar una función, una formula, únicamente el formato, o la función con/sin el formato, entre otras cosas. El pegado especial está en la cinta de opciones en la pestaña de Inicio en la parte superior izquierda como se muestra en la figura: Al dar clic en “Pegado Especial” como se observa en la imagen anterior, se despliega un cuadro de texto como el siguiente: Como observamos el pegado especial tiene diferentes opciones de las cuales podemos combinar según nuestra necesidad. A continuación te explicamos el pegado especial de cada una de las opciones: • Todo: Pega todo lo que contenga la celda (formato, formula, función, valores, etc.) • Formulas: Se utiliza cuando copias un celda que contiene formula o alguna función respetándote el formato de destino. Ejemplo: • Formatos: Para copiar únicamente el formato de la celda pero no el contenido • Comentarios: Para copiar únicamente comentarios que existan en las celdas seleccionadas.
• Todo excepto bordes: Copia las formulas y valores así como todos los formatos, comentarios excepto los bordes: • Ancho de columna: Únicamente se copia el ancho de columna • Formato de números y fórmulas: Copia únicamente las fórmulas y todas las opciones de formato de número de las celdas seleccionadas. Es decir, se seleccionas celdas con fórmulas y cuando selecciones esta opción te respetará la fórmula.
• Formato de números y valores: Únicamente se copia los valores y todas las opciones de formato de números de las celdas seleccionadas. Es decir si seleccionas una columna que tenga fórmulas, con esta opción lo pega en valores.
• Pegado en Valores: Pega el resultado de una celda con fórmula respetando el formato de destino.
Para hacer el pagado especial se puede hacer dando clic con el botón derecho y observarás los botones que hacen referencia al pegado especial, los cuales los puedes utilizar cuando te familiarices más con ellos.
Cortar o mover Celdas Comúnmente se le llama cortar celdas pero en realidad lo que hace Excel es mover el valor de toda la celda a la celda que desees. El icono son unas tijeras que están en la pestaña de Inicio de la cinta de opciones, también puedes cortar celdas tecleando Ctrl+X y para pegar lo puedes hacer con Ctrl+V o hacer clic con botón derecho y dar clic en Pegar.
Existe otra opción con el mouse. 1) Selecciona la celda a mover, 2) Sitúate en el borde de la selección hasta que el puntero del ratón se convierta en una figura de cuatro flechas, 3) pulsa el botón de mouse y arrastra y observarás un recuadro que nos indica dónde se situará el rango seleccionado y 4) suelta el botón de mouse y moverás las celdas seleccionadas.
VI. Formato de Celdas.
A las celdas también se le pueden dar una buena presentación. En la cinta de opciones en la pestaña de Inicio, esta mayor parte para dar formato a las celdas. ¿Qué podemos dar formato? Básicamente a toda la celda según tus necesidades, desde el tipo fuente, tamaño de fuente, color de fuente, color de relleno, alineación, etc. Vamos a empezar con ver cada una de estas opciones.
Formato de la fuente Para hacer los cambios en el estilo de la celda, en la cinta de opciones se observan los botones principales para el cambio de la fuente, pero también podemos llamar un cuadro de dialogo “Formato de Celdas” para realizar los cambios de Fuente.
Para ingresar al “Formato de Celdas”, tienes que hacer clic en la flecha que se encuentra al pie de la sección de Fuente. En el cuadro de “Formato de Celdas” observarás lo siguiente: • Fuente: Son los tipos de letras disponible por Excel y sólo se puede elegir uno de la lista.
• Estilo: Es el estilo de letra y sólo se selecciona uno a la vez. Los estilos estándar son Negritas, Normal, Negrita Cursiva y Cursiva.
• Tamaño: Se refiere al tamaño de la escritura seleccionada.
• Subrayado: Tendrás que elegir de la lista desplegable el tipo de subrayado.
• Efectos: Se tendrá la opción de selección por el checkbox el efecto: Tachado, Superíndice y Subíndice.
• ChechBox Fuente Normal: al activarlo se restaurará los formatos por defecto del programa.
Una vez que hayas seleccionado los formatos se le da clic en el botón “Aceptar”. Sin embargo, lo recomendable es utilizar los botones que están en la sección de Fuente en la pestaña de Inicio ya que es más práctico y cada uno de los botones es intuitivo al utilizarlos.
Botones de Fuente Nombre del Botón Cuadro de botones ubicado en la cinta de opciones de la pestaña Inicio.
Lista desplegable de la Fuente. Seleccionas la fuente Lista desplegable del tamaño de la fuente Icono para aumentar o reducir el tamaño de la fuente Estilo de la fuente: Negritas, Cursiva o Subrayados Estilos de Bordes de Celdas o rangos de Celdas.
Relleno de la celda y color de la fuente Flecha de pie de sección de Fuente, sirve para llamar el cuadro de dialogo “Formato de Celdas” Alineación Se refiere a la orientación y alineación del texto de la celda. Para hacer los cambios podemos llamar al cuadro de “Formato de Celdas” desde la pestaña de Alineación.
Las opciones de la ficha son: • Alineación del texto Horizontal: Como su nombre lo indica, alinea las celdas seleccionadas de forma horizontal, es decir respecto a la anchura de las celdas. Puedes elegir en la lista desplegable el tipo de alineación. Ejemplos de alineación horizontal: • Alineación de texto vertical: Alinea el contenido de las celdas de forma vertical, es decir, respecto a la altura de la celda. Esta opción tendrá sentido si la altura de las filas se ha ampliado. Puedes elegir en la lista desplegable el tipo de alineación. Ejemplos de alineación vertical: • Orientación: Permite cambiar el ángulo del contenido de las celdas para que se muestre en horizontal (por defecto), de arriba abajo o cualquier ángulo. Ejemplo: • Ajustar el Texto: Cuando empezamos a introducir textos en la celda y estos no caben dentro de la celda, utilizamos el checkbox de “Ajustar el Texto”, y el contenido de la celda se visualizará exclusivamente en esta celda ajustando el tamaño de la celda y texto.
• Combinar celdas: Las celdas activas se unirán hasta mostrar una sola celda combina.
En la cinta de opciones disponemos de los botones más comunes para la “Alineación” de textos en la celda. A continuación de mostramos los botones más comunes: Botones para Alineación Nombre o descripción Cuadro de botones más utilizados para la alineación de celdas.
Estos botones los utilizamos para la alineación de la celda: Alinear en la parte superior, Alinear en Medio, Alinear parte inferior, Alinear la izquierda, Centrar y Alinear la derecha.
Botón de orientación. Gira el texto de a un ángulo diagonal o vertical.
Botón para disminuir Sangría y botón para aumentar sangría.
Botón para Ajustar Texto especialmente en lo largo para poder verlo todo.
Este botón contiene una lista despegable de Combinar Celdas. También para separar celdas que ya hayan sido combinadas.
Bordes.
También tenemos la opción de modificar los Bordes de la Celda. Podemos ingresar desde el pie de sección de Fuente o Alineación y aparecerá el cuadro de dialogo de “Formato de Celdas” y damos clic en la pestaña de “Borde”.
En el cuadro de dialogo observamos una representación de la celda o rango de celdas a modificar. Se puede modificar el estilo y color de línea a gusto del analista. Cuenta con diversos estilos de líneas y a su vez ponemos ir modificando el color de los bordes, por ejemplo: Obtenemos: Relleno También podemos modificar fondo o sombreado de la celda con el formato de Relleno. Normalmente utilizamos este formato para identificar visualmente alguna celda o rango de celdas. Para ingresar a Relleno, abrimos de nuevo el cuadro de dialogo “Formato de Celdas” y nos vamos a la pestaña “Relleno”.
En el cuadro de dialogo podemos seleccionar el color de fondo. Para tener más versatilidad de colores pulsamos el botón “Más colores…” y abrirá un cuadro de dialogo y seleccionar otros colores que no están por default. Al escoger el color de fondo, en la parte inferior del cuadro observamos la muestra del color. En esta misma ventana puedes seleccionar el “color de trama” y “Estilo de trama”. El botón de “Efectos de relleno…” nos abre otro cuadro de diálogo en el podemos seleccionar de uno o dos colores y el estilo de sombreado. Ejemplos: Formato de valores numéricos o textos Excel nos permite dar formato a los valores o constantes que contiene una celda o rango de celdas.
Los formatos numéricos nos permiten afectar la forma para mostrar los números para que sean más fáciles de leer en diferentes circunstancias. Contiene diferentes categorías: General, número, el tipo de moneda, Contabilidad, Fecha, Hora, Porcentaje, Fracción, Científica, Texto, Especial y Personalizada.
Primero ingresamos al cuadro de “Formato de Celdas” y pulsamos la pestaña “Número”.
El cuadro de Texto contiene una columna de Categorías, un recuadro de Muestra del formato seleccionado y otro recuadro para dar formato personalizado. A continuación te describimos a qué se refiere las categorías y la forma en personalizar los valores: • General: Es el formato por default de Excel. El valor se observa tal cómo se captura en la celda.
• Número: Da el formato de número y seleccionas el número de decimales, también permite especificar el separador de millares y la forma de visualizar los números negativos.
• Moneda: Da el formato de número pero agregando el tipo de moneda seleccionada. Permite seleccionar el número de decimales y se puede escoger el símbolo monetario. Los cambios los podemos visualizar en el recuadro de Muestra y también podemos seleccionar el formato de los números negativos.
• Contabilidad: Muestra los números con comas entre los miles, seleccionas el tipo de moneda.
La diferencia entre Moneda y Contabilidad es la alineación.
• Fecha: Puede escoger entre diferentes formatos de fechas.
• Hora: Puede escoger entre diferentes formatos de Horas y puedes configurar la región.
• Porcentaje: El valor lo muestra en porcentaje y seleccionas el número de decimales. Al seleccionar este tipo de formato, el programa multiplica por 100% el valor numérico seleccionado.
• Fracción: Permite escoger entre nueve formatos de fracción.
• Científica: Muestra el valor de la celda en formato “coma flotante” y escoger le número de decimales. Normalmente se usa para cantidades pequeñas o muy grandes que es difícil su lectura en Excel, salvo llevarlas a un exponencial.
• Texto: El valor numérico lo convierte a texto • Personalizada: Personalizar un formato nuevo Ejemplos: En la Cinta de Opciones en la pestaña de Inicio en la sección de “Número” encontraras los botones más comunes para el formato de los valores. También esta una lista desplegable de las categorías y podrás seleccionar el tipo de formato. En el siguiente cuadro te mostramos los botones de esta sección: Botón Nombre o descripción Botones más comunes para el formato de valores en la pantalla de inicio. Se encuentra en la cinta de opciones de la pestaña Inicio en la sección “Número” En este botón con lista despegable, se encuentran las categorías mencionadas anteriormente, sin embargo, no se pude personalizar como el cuadro de dialogo “Formato de Celda” Los botones más comunes para formato de valore: Contabilidad con lista desplegable, botón para cambiar a porcentaje y el botón de millares.
Los botones se utilizan para aumentar o reducir los decimales.
VII. Formato Condicional El formato condicional suele utilizarse para resaltar un rango de celdas según los valores o contenido de las celdas que cumplan con una determinada regla condicional. Esta opción la encontramos en la “cinta de opciones” en la pestaña de “Inicio” en la sección de “Estilos” en el botón de “Formato Condicional”. Al pulsar el botón nos despliega submenús de los cuales podemos utilizar según lo que necesitemos resaltar al dar una condición. En la siguiente figura observamos los formatos condicionales que podemos generar: Resaltar reglas de celdas Se utiliza para resaltar celdas cuyos valores o contenidos de celdas cumplan con la condición: Es Mayor que…, Es Menor que…, Entre, Es igual a…, Texto que contiene…, Una fecha… o Duplicar valores… Este formato condicional es el que más se llega a utilizar en la hoja de Excel, por la diversidad de opciones que nos presenta: Ejemplo Es Mayor que… Primero debemos seleccionar el rango de celdas que aplicaremos el formato condicional “Mayor que…” Segundo, pulsamos los botones de Formato Condicional, Resaltar reglas de celdas y por último pulsamos “Es mayor que…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual aplicamos el formato condicional de las celdas seleccionadas: En el ejemplo anterior observamos que de forma automática el programa rellena las celdas que cumplen con la condición de ventas mayores a 150,000. En este ejemplo la sucursal Santa Fe y Plaza Tezontle son las únicas que tuvieron ventas superiores a 150,000 del primer trimestre.
Ejemplo Es Menor que… Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Es menor que…”, en seguida pulsamos los botones de Formato Condicional, Resaltar reglas de celdas y por último pulsamos “Es menor que…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas: En el ejemplo anterior requerimos que nos resalte las ventas menores a 75,000. En este caso la sucursal Centro 1, Centro 2 y Universidad cumplen con la condición de ventas menores a 75,000 en el primer trimestre. Por lo que el programa de Excel rellena la celda a cumplir la condición que le asignamos.
Ejemplo “Entre…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Entre…”, en seguida pulsamos los botones de Formato Condicional, Resaltar reglas de celdas y por último pulsamos “Entre…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas: La sucursal Polanco, Aeropuerto D.F., Plaza Tezontle y Toluca cumplen con la condición de ventas trimestrales entre 80,000.0 y 200,000.0 Excel nos resalta la celda con color amarillo.
Ejemplo “Es igual a…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Es igual a…”, en seguida pulsamos los botones de Formato Condicional, Resaltar reglas de celdas y por último pulsamos “Es igual a…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas: En este ejemplo aplicamos una condicional con contenido diferente a un valor. Le damos la instrucción a la hoja de cálculo que nos resalte la celda que sean Igual a “Centro 1” y nos resalta la celda que contiene este condición.
Ejemplo “Texto que contiene…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Texto que contiene…”, en seguida pulsamos los botones de Formato Condicional, Resaltar reglas de celdas y por último pulsamos “Texto que contiene…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas: En el ejemplo anterior aplicamos un formato que contenga el texto “Centro”, en este caso aplico la condición a las sucursales “Centro 1” y “Centro 2”.
Ejemplo “Una Fecha…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Una Fecha…”, en seguida pulsamos los botones de Formato Condicional, Resaltar reglas de celdas y por último pulsamos “Una Fecha…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas: Para el formato condicional de una fecha solo tenemos opciones como se muestra en la figura anterior.
Ejemplo de “Duplicar valores…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Duplicar valores…”, en seguida pulsamos los botones de Formato Condicional, Resaltar reglas de celdas y por último pulsamos “Duplicar valores…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas: Cuando seleccionamos duplicar, la hoja de cálculo nos señala los valores duplicados del rango de celdas seleccionadas, en este ejemplo observamos la cantidad de 1,000. La opción “Único” la hoja de cálculo seleccionaría los valores únicos lo que no se repiten.
Reglas superiores o inferiores.
Excel nos da la opción de aplicar formato condicional a un rango de valores mostrándonos desde 10 superiores… 10% de valores superiores…, 10 inferiores, 10% de valores inferiores, Por encima del promedio y Por debajo del promedio.
Para ingresar a esta condicional, primero nos vamos a la cinta de opciones en la pestaña de Inicio, buscamos la sección Formato Condicional y pulsamos “Reglas superiores e inferiores” y nos muestra los submenús para ésta Regla: Ejemplo “10 superiores” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “10 superiores…”, en seguida pulsamos los botones de Formato Condicional, Resaltar superiores e inferiores y por último pulsamos “10 superiores”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas: Esta condición la utilizamos para que la Hoja de Cálculo nos resalte los valores superiores de un rango de celdas. En el ejemplo dimos la instrucción que nos mostrara los 3 valores superiores del trimestre de ventas y, nos mostró la sucursal Santa Fe (203,618), Aeropuerto D.F. (143,560) y Plaza Tezontle (198,098).
Ejemplo “10% de valores superiores” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “10% de valores superiores…”, en seguida pulsamos los botones de Formato Condicional, Resaltar superiores e inferiores y por último pulsamos “10% de valores superiores…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas.
En la figura anterior se muestra el porcentaje de los valores superiores. Es decir el 38% de los valores superiores equivale a tres sucursales de ocho totales.
Ejemplo “10 inferiores…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “10 Inferiores…”, en seguida pulsamos los botones de Formato Condicional, Resaltar superiores e inferiores y por último pulsamos “10 Inferiores…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas.
En la condición indicamos que nos mostrará los valores inferiores del Trimestre de ventas. En el ejemplo observamos las últimas tres sucursales en ventas por trimestre.
Ejemplo “10% de Valores Inferiores…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “10% de valores Inferiores…”, en seguida pulsamos los botones de Formato Condicional, Resaltar superiores e inferiores y por último pulsamos “10% de valores Inferiores…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas.
En el ejemplo anterior observamos el formato condicional se aplica a los valores inferiores con respecto al porcentaje.
Ejemplo “Por encima del promedio…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Por encima del promedio…”, en seguida pulsamos los botones de Formato Condicional, Resaltar superiores e inferiores y por último pulsamos “Por encima del promedio…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas.
En el ejemplo anterior observamos que la condición solo se aplica al promedio de ventas que está por encima de 1,686 que observamos en la barra de estado.
Ejemplo “Por debajo del promedio…” Primero seleccionamos el rango de celdas en las cuales se aplicará el formato “Por debajo del promedio…”, en seguida pulsamos los botones de Formato Condicional, Resaltar superiores e inferiores y por último pulsamos “Por debajo del promedio…”: Al hacer los pasos 1) y 2), Excel nos abre un cuadro de dialogo en el cual configuramos el formato condicional de las celdas seleccionadas.
En el ejemplo anterior observamos que la condición se aplica al promedio de ventas que está por debajo de 1,686 que observamos en la barra de estado.
Formato Condicional - Barra de Datos A diferencia de los anteriores en este formato condicional es más personalizado y visual, ya que genera barras en cada celda seleccionada dependiendo de los valores o cómo se llegue a personalizar. Existe por default el Relleno degradado y Relleno sólido.
Cómo podemos observar al ir pasando el mouse sobre el Relleno degradado o el Relleno sólido se va visualizando el formato de las celdas seleccionadas. El tamaño de la barra dependerá de los valores seleccionados, es decir, entre más grande sea el valor, la barra va rellenando la celda o viceversa, si el valor es pequeño, la barra no cubre a rellenar la celda.
Más adelante describiremos como personalizar la escala con barras.
Formato Condicional – Escalas de color.
Es muy similar al formato condicional de barras con la diferencia de que en este seleccionas las escalas de color según los valores del rango de celdas. Al pasar el mouse sobre las opciones observamos que el rango seleccionado va cambiando según el tipo de escala seleccionada. Ejemplo: Como observamos en el ejemplo, al seleccionar la escala de color las celdas toman el relleno de color según los valores. Los valores bajos la rellena de un color rojo anaranjado, los valores medios los rellena de un color amarillo y los valores altos empieza a rellenarlos con un color verde claro hasta un verde fuerte.
Más adelante describiremos como personalizar la escala de colores.
Formato Condicional – Conjunto de Íconos.
Excel te da la opción de caracterizar las celdas con íconos y de igual forma que las dos anteriores, el formato está basado en los valores de las celdas. Dependiendo del ícono que selecciones podrás ver el ícono por celda. Ejemplo: En el ejemplo anterior seleccionamos el conjunto de íconos en la parte de indicadores, “3 símbolos sin círculo”, e inmediatamente la hoja de cálculo coloca un símbolo según el valor. En este ejemplo, coloca una palomita verde a las ventas mayores, coloca un signo de interrogación a las ventas que están por debajo de las mayores y coloca un tache rojo a las ventas bajas.
En la siguiente sección mencionaremos como personalizar los formatos condicionales vistos.
Administrar reglas de formatos condicionales.
En esta sección puedes crear, editar y eliminar los formatos condicionales previamente vistos. Cuando ingresas al botón de “Administrar reglas” se abre un cuadro de dialogo y te mostrará las reglas de formato condicional del Libro usando el Administrador de reglas.
Al abrir el cuadro de dialogo te mostrará las reglas aplicadas actualmente, por ejemplo: En el administrador de reglas de formatos condicionales observarás tres botones: Nueva Regla, Editar Regla y Eliminar Regla.
Nueva Regla Cuando pulsas “Nueva Regla” aparecerá otro cuadro de diálogo en el cual podrás seleccionar el tipo de regla: • Aplicar formato a todas las celdas según sus valores.
Al seleccionar este tipo de regla, tenemos como opciones el “Estilo de formato”, que puede ser Escala de 2 colores, Escala de 3 colores, Barra de datos o Conjunto de íconos (previamente vistos). Podemos personalizar los mínimos, máximos y el color según el tipo de formato. Obtenemos lo siguiente: Esta misma regla se aplica para formato condicional con barras, escala de colores y conjunto de íconos.
• Aplicar formatos únicamente a las celdas que contengan.
Aquí podemos aplicar formato más específico desde un Texto especifico, fechas, celdas en blanco, sin espacios en blanco, errores y sin errores. En el siguiente ejemplo aplicaremos el formato condicional que contenga un rango de valores de 35,000 hasta 55,000 • Aplicar formato únicamente a los valores con rango inferior o superior En este formato se aplica valores superiores o inferiores, podemos seleccionar en valor o en porcentaje.
Previamente vimos como Excel calcula el formato de reglas superiores o inferiores. Para este caso mostramos la personalización de la regla.
• Aplicar formato únicamente a los valores que estén por encima o por debajo del promedio Tenemos la opción de seleccionar por encima del promedio, por debajo del promedio, mayor o igual del promedio, menor o igual del promedio, por encima o por debajo de la desviación estándar.
En el ejemplo seleccionamos el formato por debajo del promedio.
• Aplicar formato únicamente a los valores únicos o duplicados Este formato lo aplicamos para celdas duplicadas, ahora lo vamos usar para el formato único.
Estilos de Celdas Excel tiene opciones predeterminadas para dar formato a celdas e incluso se puede personalizar el estilo de celdas. Para ingresar es a partir de la cinta de opciones en la pestaña de Inicio en la sección de Estilos. Encontraremos el botón “Estilos de Celdas” y abrirá los estilos predefinidos por Excel, tal como se muestra en la siguiente figura.
Al ir pasando el mouse sobre los estilos, observamos en la hoja de cálculo el cambio de la celda según el estilo. Para personalizar celdas es en el botón “Nuevo estilo de celda…” y aparecerá un cuadro de dialogo: En el cuadro de dialogo pondremos el nombre del estilo, le damos clic en el botón de “Formato…” y podremos personalizar el estilo de celdas desde el cuadro de “Formato de Celdas”, seleccionamos con un checkbox lo que deseamos modificar y aceptamos los cambios. Una vez creado nuestro estilo personalizado podemos configurar las celdas seleccionando el estilo personalizado. Además, cualquier cambio hecho en el estilo personalizado, se hará el cambio automático para todas las celdas con el estilo. Ejemplo: VIII. Cambios de estructura de las celdas, filas o columnas.
Podemos hacer cambios en la estructura de las celdas, filas y columnas de tal manera de modificar la estructura en alto de fila, ancho de columna, autoajustar, entre otros. Los cambios de estructuras lo encontramos en la cinta de opciones, en la pestaña de Inicio y la sección de “Celdas”.
En la siguiente tabla se describe los botones de la sección de Celdas: Botón Descripción o nombre del botón Botones para cambiar la estructura de filas, columnas o celdas.
Despliega botones para insertar celdas, insertar filas de hoja, insertar columnas de hoja o insertar una hoja nueva.
Despliega botones para eliminar celdas, eliminar filas de hoja, eliminar columnas de hoja y eliminar una hoja. Hay que tener cuidado al eliminar hojas debido a que ya no se podrán recuperar los datos de esa hoja En el formato encontramos variedad de botones. Podemos modificar el alto de fila, de columna, autoajustar la fila y columna; ocultar filas y columnas; incluso tenemos opciones para cambiar el nombre de la etiqueta de la hoja, el color o proteger la hoja.
IX. Modificar ¿Qué son las funciones? Una función es una fórmula predefinida por Excel (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene.
La sintaxis de cualquier función es: =Nombre_función(argumento1;argumento2;argumentoN) Hay que considerar lo siguiente cuando demos la instrucción de una función: 1. Toda función debe comenzar con el signo “=” o signo “+”. Al escribir estos signos, Excel lo interpreta que vas utilizar una función o una fórmula.
2. Los argumentos o valores de entrada siempre van entre paréntesis.
3. Cualquier argumento puede ser valores constantes, funciones o fórmulas.
4. Los argumentos se separan por un “;” o en su caso una “,” según la versión.
5. Si en el argumento es una constante, se deberá poner la constante entre comillas. Ejemplo: =nombre_función(“Casa”;A4:A5) 6. El signo “:” nos indica un rango de celdas 7. El signo de “$” nos indica que está fijando una fila o columna o ambas.
Cómo podemos observar el programa de Excel nos va indicando cómo debemos escribir la función. En el caso que la escribamos mal, Excel nos va indicar en dónde está el error de la función.
Ejemplo de cómo utilizar el signo de “$”: Cuando queremos fijar con una fórmula con una columna utilizamos el signo de “$” antes del nombre de la columna. Cuando queremos fijar con una fórmula una fila utilizamos el signo de “$” antes del número de fila. Esto es muy importante al utilizar fórmulas y funciones debido a que su utilidad que aporta.
Diferencia entre una fórmula y una función: Cómo podemos observar el resultado es el mismo, sin embargo, la sintaxis es diferente. En las fórmulas podemos combinar constantes, funciones y celdas para obtener un resultado. En la función es un parámetro predefinido por Excel, que nos ayudan a simplificar la sintaxis y es flexible ante cualquier cambio. Cuando mezclamos constantes con fórmulas o funciones, debemos ser cuidadosos de que el valor contante no afecte el resultado si deseamos copiar o rellenar otras celdas.
Autosuma y sus funciones más frecuentes.
Cualquier función se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel dispone de botones que facilitan esta tarea. En la cinta de opciones en la pestaña de Inicio encontraremos un botón de Autosuma que nos da la facilidad de realizar la SUMA de forma más rápida sin la necesidad de escribir la función en la celda. Este botón tiene una lista despegable y nos indica que otras funciones básicas podemos utilizar: Las funciones que observamos son las más básicas como es la Suma, Promedio (calcula la medida aritmética), Contar números (cuenta valores), Máximo (obtiene el valor máximo) y Mínimo (obtiene el valor mínimo).
A continuación veremos un ejemplo de cómo utilizar estas funciones básicas.
Función de Contar.
Cuenta el número de celdas de un rango que contiene números. En el ejemplo queremos que nos cuente el número de meses: Función de SUMA.
Suma todos los números de un rango de celdas: Función Promedio.
Devuelve el promedio o media aritmética de los argumentos que contengan valores: Función Max Obtiene el valor máximo de un rango de valores: Función Min Obtiene el valor Mínimo de un rango de valores: Las funciones mencionadas anteriormente también se pueden utilizar con pulsar el botón que están en Autosuma.
Ordenar Permite organizar los datos para facilitar el análisis. Se puede ordenar los datos de orden ascendente o descendente o filtrar temporalmente valores específicos en una base de datos o matriz.
Cuando ordena información en una hoja de cálculo, se puede ver los datos de la forma que desee y encontrar valores rápidamente por medio de estos botones. Ésta función es útil para el manejo de base de datos que son de tamaña considerable. Los botones los encontramos en la cinta de opciones en la pestaña de inicio en la sección de “Modificar”: Ejemplo de cómo utilizar Ordenar personalizado…: Cuando pulsamos el botón “Orden personalizado…”, Excel abre un cuadro de dialogo para personalizar el orden: El cuadro de dialogo nos muestra opciones para ordenar la base de datos seleccionada. El botón “Agregar nivel” al pulsarlo va agregando niveles: Cuando requerimos agregar niveles es porque deseamos que Excel nos ordenes según el orden de los niveles agregados. Por ejemplo, que nos ordene primero por continente y luego por país. Para esto necesitamos decirle a Excel que columna queremos ordenar, el orden que debe seguir y el criterio al ordenar. Ejemplo: Pulsamos Aceptar y obtenemos lo siguiente: Obsérvese que en un principio estaba ordenada de una manera distinta. Ahora al dar la instrucción, Excel no ordeno la columna de Contienen de Z a A y luego nos ordenó por país de A a Z. De esta misma forma podemos hacer el ordenado con valores numéricos, textos, íconos o un dato personalizado.
Ejemplo de cómo utilizar valores numéricos, considerando la misma base de datos: Al pulsar Aceptar, obtenemos lo siguiente: Excel nos ordeno Filtrar Usamos Autofiltro para buscar u ocultar valores en una o más columnas de datos. Podemos filtrar basándonos en opciones que realizamos en una lista, buscar para encontrar los datos que deseamos ver. Cuando se filtran datos y los valores en una o más columnas no reúnen los criterios de filtrado, se ocultan las filas completas. El botón para filtrar se encuentra en la cinta de opciones de la pestaña de inicio en la sección de Modificar.
Ejemplo de cómo utilizar el Filtro: Una vez seleccionado, pulsamos el botón de “Filtro” e inmediatamente Excel agregará los filtros en cada una de los encabezados de las columnas: Al dar clic en el filtro de cualquier encabezado observamos una lista desplegable y nos mostrará el contenido de la columna: En el ejemplo al dar clic en el filtro de Continente, nos muestra una serie de opciones y el contenido de la columna. Estas opciones nos permiten filtrar valores personalizados, por ejemplo, si filtramos por el continente de Asia, obtenemos lo siguiente: AL dar Aceptar obtenemos lo siguiente: Básicamente los filtros los utilizamos para analizar o ver cierta información de una base de datos, nos permite visualizar los datos de forma conjunta. Sin embargo hay que tener cuidado al utilizar los filtros ya que copiar o pegar información podemos cometer errores en la base de datos.
En el siguiente ejemplo podemos ver otro tipo de filtro más personalizado. Al dar clic en el filtro donde existan valores, el filtro activo una opción “Filtros de número” o entras versiones “Filtro personalizado”: Al dar clic en cualquiera de estas opciones nos abre un cuadro de dialogo llamado “Autofiltro personalizado” en el cual tiene una lista desplegable para seleccionar el tipo de filtro personalizado: En el cuadro de dialogo tenemos opciones como: es igual a, no es igual a, es mayor que, es mayor o igual a, es menor que, es menor o igual a, comienza por, no comienza por, termina con, contiene y no contiene.
A continuación mostraremos un ejemplo de cómo utilizar el filtro personalizado y de igual manera se podrá utilizar con otro filtro personalizado: Primero seleccionamos el tipo de filtro y escribimos la condición. En este ejemplo utilizaremos un filtro personalizado de “es mayor que” con un valor de 150,000: Al dar clic en aceptar, obtenemos lo siguiente: Siguiendo el mismo procedimiento se podrá personalizar los filtros de todas las opciones que nos da Excel.
Para quitar los filtros se pude hacer por dos formas: ir a la celda que contiene el filtro y dar seleccionar todo u oprimir el botón de Borrar: Buscar, Buscar y reemplazar.
Se pueden usar los botones para opciones de búsqueda avanzada, para remplazar textos o saltar directamente a un punto concreto del documento o elegir otras opciones para limitar la búsqueda. El botón lo encontramos en la cinta de opciones en la pestaña de inicio en la sección de “Modificar”: En el botón de Buscar… lo podemos activar desde el botón o tecleando Ctrl+B. Nos aparecerá un cuadro de dialogo “Buscar y reemplazar” en el cual veremos dos pestañas: Buscar y reemplazar: En la pestaña de Buscar, escribiremos el contenido que deseamos buscar en la hoja de cálculo. Si pulsamos Buscar todos a parecerá como sigue: En el otro caso, si damos clic en el botón Buscar siguiente, únicamente nos va dirigir a la celda A21 y si le damos nueva cuenta Buscar siguiente, nos dirigir a la celda A22 y así sucesivamente.
Cuando pulsamos la pestaña de Reemplazar, se observa que añade un renglón con el nombre de “Reemplazar con”, lo cual nos quiere decir que del valor buscado nos va reemplazar por otro valor: Utilizaremos el ejemplo de reemplazar México por Estados Unidos Mexicanos y al dar clic en “Reemplazar todos” al hacer esta acción Excel buscará la palabra México y la reemplazará por Estados Unidos Mexicanos. También tenemos como opción primero buscar y luego reemplazar.
Como podemos observar también existe un botón “Opciones” al pulsar sobre el botón te da un cuadro de dialogo como sigue: Para este tipo de búsqueda son más específicas, ya que tenemos más opciones que nos da Excel, dentro de la lista desplegable de “Dentro de” tenemos la opción de buscar en la Hoja actual o en todo el libro. En “Buscar” tenemos dos opciones: por filas o por columnas. También podemos hacer búsquedas por Valores, fórmulas o por comentarios. Cualquiera de esas opciones debemos ser específicos en la búsqueda.
Ir a… Esta opción nos permite saltar a una determina página, línea, nota de pie, comentario u otro lugar del documento. Se personaliza esta opción y es útil si queremos guardar alguna referencia dentro del documento. Ejemplo: Hemos guardado 4 referencias y al pulsar en cada uno nos dirige a la celda.
Ir a Especial… Es esta opción nos permite ir más específico a las celdas desde los cometarios, constantes, fórmulas, celdas en blanco, Región actual, entre otros. Encontramos el botón en la cinta de opciones en la pestaña de Inicio y sección de Modificar. Pulsamos Buscar y seleccionar y enseguida el botón de “Ir a Especial…”. Una forma más fácil es teclear F5 e inmediatamente abrirá el cuadro de diálogo Ir a Especial.
Como podemos observar existen varias opciones de las cuales algunas son muy útiles en el manejo de base de datos.
• Comentarios: nos selecciona las celdas que contienen comentarios • Constantes: selecciona las celdas que contienen constantes (omite las celdas con valores) • Fórmulas: selecciona las celdas que contienen fórmulas dependiendo si la fórmula obtiene un número, un texto, un valor lógico o errores.
• Celdas en Blanco: Selecciona las celdas en blanco de un rango de celdas seleccionadas • Región actual: Selecciona la región en la base de datos • Objetos: Selecciona los objetos gráficos, botones y cuadros de texto en la hoja de cálculo.
• Diferencias entre filas: Selecciona las filas diferentes a la celda activa de un rango columnas seleccionadas. Es decir, todas las celdas que se diferencian de la celda activa en una fila seleccionada. Siempre hay una celda activa en una selección, ya sea un rango, una fila o una columna.
• Diferencias entre columnas: Selecciona las columnas diferentes a la celda activa de un rango filas seleccionadas.
• Celdas precedentes: Selecciona las celdas que hacen referencia a una fórmula precedente.
Primero debemos posicionarnos donde está la fórmula y enseguida seleccionamos esta opción y nos dirigirá a las celdas que preceden de la fórmula.
• Celdas dependientes: Selecciona las celdas que tienen alguna fórmula de la celda activa.
Primero debemos posicionarnos en la celda que dirige a una fórmula y enseguida seleccionamos esta opción y nos dirigirá a la celda que hace referencia a la celda.
• Última celda: nos lleva a la última celda de la hoja de cálculo que contiene datos o formato.
• Solo celdas visibles: Selecciona las celdas que están visibles en un rango que cruza filas o columnas.
• Celdas con formatos condicionales: Selecciona las celdas que tienen un formato condicional en la hoja de cálculo X. Creación de Gráficos ¿Qué son las gráficas? Las gráficas son una representación de datos numéricos de los cuales lo representan de forma visual, nos ayudan a comprender mejor los datos y a presentarlos de una forma más sintética. Excel nos da un sinfín de opciones para crear gráficas, tiene diferentes tipos de gráficas: graficas de columnas, graficas de línea, graficas circulares, graficas de barras, graficas de área, graficas de dispersión, grafica de cotizaciones, graficas de superficie, graficas radial y cuadro combinado.
Iniciar a crear un gráfico Primero debemos seleccionar los datos a graficar. Enseguida nos vamos a la cinta de opciones en la pestaña “Insertar” y buscamos la sección de “Gráficos” damos clic en el botón “Gráficos recomendado”: Inmediatamente nos abrirá un cuadro de dialogo “Insertar Gráfico” damos clic en “todos los gráficos” y aparecerá lo siguiente: Como podemos observar en la parte derecha del cuadro de dialogo nos muestra las opciones de gráficos que tiene Excel. Para este ejemplo, seleccionamos “Columna” y de la parte superior del recuadro observamos siete opciones para crear gráficos de columna. Para este ejemplo seleccionaremos la opción “columna agrupada” y seleccionaremos el gráfico sugerido de la parte izquierda: Como podemos observar, en la cinta de opciones añade dos pestañas: “Diseño” y “Formato” estas se van a visualizar únicamente cuando seleccionemos el grafico. Es decir, si quietamos el cursor sobre el gráfico y damos clic en cualquier parte de la hoja, estas pestañas desaparecerán. Aparecerán cuando demos clic en el gráfico o creemos uno nuevo.
También podemos observar dentro de la imagen que Excel resalta con colores los valores seleccionados previamente, esto nos indica que valores son lo que hacen referencia al gráfico.
Personalización de un gráfico Hasta entonces solo hemos creado el gráfico pero falta personalizarlo, ya que Excel por default te da la opción estándar. Para esto utilizaríamos las pestañas de “Diseño” y “Formato” que se activan cuando está activo el gráfico.
En la pestaña de “Diseño” podemos cambiar los colores, agregar elementos, cambiar el diseño del gráfico, el estilo del diseño, cambiar entre filas y columnas, cambiar los datos seleccionados, cambiar el gráfico o mover un gráfico a una hoja nueva.
Vamos a empezar a describir cada una de estas opciones: Agregar elemento de gráfico En el siguiente botón podemos personalizar una serie de opciones que nos van ayudar a presentar el gráfico: Cada una de estas opciones podemos cambiar la presentación de los ejes, el título del eje, las etiquetas, la tabla de datos, dónde queremos que vaya la leyenda, colocar etiquetas si lo deseamos.
Para el siguiente ejemplo vamos a seleccionar lo siguiente: Título del eje Título del gráfico 100,000 90,000 80,000 70,000 60,000 50,000 40,000 30,000 20,000 10,000 - Ene Feb Univer Centr sidad o1 Aerop Plaza Centr Polan Santa Toluca uerto Tezon o2 do Fe D.F.
tle Ene 17,60 22,16 17,97 33,24 26,95 53,02 86,54 78,16 Feb 11,34 22,51 35,50 27,83 29,78 41,81 69,75 90,18 Mar 11,76 26,52 18,68 26,72 71,35 48,72 41,79 35,26 Mar Cómo podemos observar de acuerdo a lo que seleccionemos en los botones de “Agregar Elementos de gráfico” nuestra gráfica se va personalizando a nuestro gusto. Esta sección es intuitiva, ya que el mismo Excel te va mostrando los cambios al instante y puedes cambiar si no te gusta cómo va quedando la gráfica.
Diseño rápido Este botón tiene diseños predefinidos por Excel y no se requiere personalizar cada elemento como los botones vistos anteriormente. Te mostramos algunos ejemplos de cómo sería cada botón predefinido.
Si seguimos seleccionando los diseños predefinidos cambiará la gráfica acorde al diseño rápido.
Cambiar Colores Estos botones tienen como finalidad cambiar los colores de las barras o del gráfico que estemos personalizando. Al dar clic en el botón “Cambiar colores” va desplegar una serie de opciones para que podamos cambiar el color de la gráfica. Por ejemplo: Excel no da una gran variedad de combinaciones de colores para las gráficas. Sin embargo, si queremos personalizar aún más el color, podemos dar clic con botón derecho sobre la barra y seleccionar el color de nuestra preferencia.
Estilos de diseño Excel también maneja estilos predefinidos para toda la gráfica. Los estilos predefinidos que maneja Excel te dan una presentación diferente desde el color de las barras, el fondo de la gráfica, tipo de fuente, etc. Te mostramos algunos estilos predefinidos que nos sugiere Excel: Podemos escoger cualquier estilo predefinido por Excel y posteriormente ir modificando el estilo del diseño tal como lo venimos haciendo.
Cambiar entre filas y columnas.
Esta opción intercambia los valores del eje. Es decir, los datos que se han colocado en el eje X se moverán al eje Y, y viceversa. Ejemplo del cambio: Gráfica Actual: Título del eje Título del gráfico 100,000 90,000 80,000 70,000 60,000 50,000 40,000 30,000 20,000 10,000 - Ene Feb Univer Centr sidad o1 Aerop Plaza Centr Polan Santa Toluca uerto Tezon o2 do Fe D.F.
tle Ene 17,60 22,16 17,97 33,24 26,95 53,02 86,54 78,16 Feb 11,34 22,51 35,50 27,83 29,78 41,81 69,75 90,18 Mar 11,76 26,52 18,68 26,72 71,35 48,72 41,79 35,26 Gráfica con el cambio de coordenadas: Mar Título del eje Título del gráfico 100,000 90,000 80,000 70,000 60,000 50,000 40,000 30,000 20,000 10,000 - Universidad Centro 1 Ene Feb Mar Universidad 17,601 11,349 11,768 Centro 1 22,163 22,519 26,522 Centro 2 17,971 35,506 18,687 Polando Toluca 33,245 27,838 26,728 Aeropuerto D.F.
Polando 26,952 29,785 71,356 Plaza Tezontle Aeropuerto D.F.
53,027 41,810 48,723 Plaza Tezontle 86,545 69,755 41,798 Santa Fe 78,165 90,185 35,268 Centro 2 Toluca Santa Fe Los datos de la gráfica siguen siendo los mismos, lo que cambia es la presentación de la gráfica. En la gráfica original observamos que las sucursales están ordenadas por mes. En cambio la segunda gráfica la presentación es por mes y por sucursal.
Seleccionar datos.
Este botón es muy importante, ya que en este botón podemos cambiar los datos originales, además de personalizar más los datos según nuestras necesidades. Al pulsar el botón se abrirá un cuadro de diálogo “Seleccionar origen de datos”: El “Rango de datos del gráfico” es donde seleccionamos las celdas o el rango de datos, en este caso nos dice que ya existe los datos y que los datos están en la hoja llamada Tutorial y que comprende de la celda H16 hasta la celda K24, lo podemos corroborar en la siguiente imagen: Sin embargo, podemos cambiar los datos si así lo deseamos. Para esto pulsamos el botón como se ve en la siguiente figura: Cuando pulsemos el botón nos indicará con líneas intermitentes los datos previamente seleccionados: Para cambiar los datos solamente necesitamos seleccionar los valores y teclear “Intro” y de nueva cuenta nos llevará al cuadro de diálogo “Seleccionar origen de datos”.
También podemos cambiar, eliminar o agregar datos del eje “Y”, para esto podemos pulsar los siguientes botones: Cuando pulsamos “Agregar” se abre un cuadro de dialogo como el que sigue: El “Nombre de la serie” se refiere al nombre de la nueva serie, en este caso vamos agregar el mes de abril. También seleccionamos los nuevos datos en “Valores de la serie”. Ejemplo: Nombre de la serie: Valores de la serie: Obtenemos: Al dar aceptar observamos que hemos agregado otro mes: Podemos editar el eje horizontal. Para este ejemplo son las sucursales. Pulsamos el botón “Editar” del recuadro derecho y no abrirá un cuadro de dialogo señalándonos el rango seleccionado previamente: Si deseamos modificarlo con solo seleccionar los datos y pulsar Aceptar, se realizarán los cambios en la gráfica. Hasta este entonces tenemos como resultado la siguiente gráfica: Título del eje Título del gráfico 100,000 90,000 80,000 70,000 60,000 50,000 40,000 30,000 20,000 10,000 - Ene Feb Unive Centr rsidad o 1 Aerop Plaza Centr Polan Santa Toluca uerto Tezon o2 do Fe D.F.
tle Ene 17,60 22,16 17,97 33,24 26,95 53,02 86,54 78,16 Feb 11,34 22,51 35,50 27,83 29,78 41,81 69,75 90,18 Mar 11,76 26,52 18,68 26,72 71,35 48,72 41,79 35,26 Abril 12,56 24,56 22,58 31,26 34,68 51,26 75,87 54,89 Mar Abril Cambiar Gráfico En el siguiente botón podemos cambiar la gráfica actual. Sin embargo hay que tener en cuenta que al cambiar la gráfica corre el riesgo de volver a realizar todos los cambios que previamente hayamos hecho.
Cuando pulsemos “Cambiar Gráfico” nos abrirá el cuadro de diálogo “Cambiar tipo de gráfico” en el cual podemos observar todos los tipos de gráficas que existen en Excel. Al dar clic sobre cada uno, en el cuadro de dialogo se observa una vista previa del gráfico seleccionado. Te mostramos algunos ejemplos: Vista previa del gráfico de línea: Vista previa del gráfico Circular: Vista previa del gráfico de Barras: Así sucesivamente podemos ir observando la vista previa al gráfico que queramos cambiar. Solo hay que considerar los posibles cambios al personalizar el gráfico.
Mover gráfico Tenemos la opción de mover el gráfico a una nueva hoja o una hoja existente. Para hacer el movimiento debemos pulsar “Mover gráfico” e inmediatamente nos abrirá un cuadro de diálogo “Mover gráfico” en el cual nos da dos opciones (Hoja Nueva o Objeto en:) Si seleccionamos “Hoja Nueva” y damos Aceptar. Inmediatamente creará una hoja nueva con el nombre de “Gráfico1”: Formato a la gráfica En la cinta de opciones en la pestaña de “Formato” tenemos una serie de botones que nos ayudan a personalizar el formato de las fuentes, color de fuente, títulos, los ejes, el fondo de la gráfica, etc. Tiene una gran variedad de opciones de los cuales muchos de ellos te van mostrando los cambios que del formato. Al crear una gráfica esta parte es una de las opciones dónde puedes invertirle bastante tiempo al personalizar una gráfica por la gran variedad de opciones que ofrece el programa. Te explicamos de forma breve cómo funciona la cinta de opciones de la pestaña formato, ya que la mayoría esta interrelacionada.
En la siguiente figura se muestra la cinta de opciones de la pestaña Formato: Es decir, todo va en función a lo que seleccionemos el formato a modificar, por ejemplo, seleccionamos “Área de gráfico” y observaremos que en la gráfica se activará el área de la gráfica la cual nos está indicando qué es lo que vamos a cambiar: La gráfica nos queda de la siguiente manera: Ahora cambiamos el “Área de trazado”: Ahora cambiamos la Leyenda: Podemos ir modificando cada uno de los formatos que nos da el programa. Hasta obtener la gráfica deseada junto con el formato deseado.
Ahora lo que nos hace falta es cambiar los títulos de los ejes y el título del gráfico: Cambiamos el “título del gráfico”: Nuestra gráfica final quedaría de la siguiente forma si seguimos buscando personalizarla: Pesos Ventas por sucursal 100,000 90,000 80,000 70,000 60,000 50,000 40,000 30,000 20,000 10,000 - Ene Feb Univer Centro Centro Poland Toluca sidad 1 2 o Aeropu Plaza Santa erto Tezontl Fe D.F.
e Ene 17,601 22,163 17,971 33,245 26,952 53,027 86,545 78,165 Feb 11,349 22,519 35,506 27,838 29,785 41,810 69,755 90,185 Mar Abril Mar 11,768 26,522 18,687 26,728 71,356 48,723 41,798 35,268 Abril 12,568 24,568 22,587 31,268 34,689 51,268 75,879 54,897 Todas las gráficas que nos presenta Excel se crean de la misma forma, hay algunas que necesitan datos diferentes para poder construir la gráfica, sin embargo, Excel te va guiando en construir toda la variedad de gráficas que contiene, la ventaja de crear gráficas es que es intuitivo al grado de crear una gráfica que no conozcas.
XI. Biblioteca de Funciones Anteriormente vimos qué es una función y cómo es la sintaxis de la función. Ahora veremos a detalle las funciones que maneja Excel. Es muy importante esta sección, ya que saber manejar funciones es la esencia de Excel y nos permite utilizar la herramienta más eficaz. Cabe destacar que en este curso no se pretende enseñar todas las funciones, porque existen funciones específicas como las financieras, las de matemáticas o trigonométricas, las de ingeniería u otras, que para entenderlas necesitamos conocimientos previos. El alcance de estas funciones va en relación a las más comunes en su utilidad como las lógicas, algunas de búsqueda y referencias, algunas de Texto, algunas de fecha y hora, algunas comunes de matemáticas, entre otras.
Además, todas las funciones tienen su tutorial en Excel, por lo que lo hace más sencillo de entenderlas.
En la barra de fórmulas encontramos un símbolo como fx al pulsar el botón, nos abre un cuadro de dialogo “Insertar función”: En el cuadro de dialogo “Insertar función” se encuentran todas funciones que maneja Excel. Podemos buscar y seleccionar la función o podemos buscar la función por categoría y después seleccionar la función buscada. Excel separa las funciones por categorías, debido a que es más práctico. Las categorías que maneja Excel son las siguientes: • Financiera: Contiene funciones específicas para realizar cálculos financieros o contables, contiene una gran variedad de alternativas y opciones para los cálculos financieros.
• Fecha y Hora: Son utilizadas para buscar fechas específicas, para conocer la hora actual, para encontrar la diferencia en días laborales entre dos fechas y muchas cosas más.
• Matemáticas y trigonométricas: Contiene funciones que nos pueden servir para ejecutar varias operaciones aritméticas, hasta operaciones más específicas como logarítmicas entre otras.
• Estadísticas: Permite calcular y analizar estadísticamente datos. Para algunas funciones hay que tener cierto conocimiento para poder aplicarlas.
• Búsqueda y referencia: Nos permite buscar referencias o valores en una hoja de cálculo o en diferentes hojas de cálculo. Son muy útiles si manejamos base de datos.
• Base de datos: Facilitan el trabajo cuando utilizamos base de datos y deseamos simplificar información y tenerla más organizada.
• Texto: Útiles para manipular textos dentro de una celda.
• Lógica: Funciones que nos ayudan a decidir si ejecuta una acción la función.
• Información: Nos ayudan a identificar errores, información de una celda u hoja de cálculo, principalmente.
• Definidas por el usuario: Son funciones predefinidas que diseña el usuario A continuación describiremos más a detalle la sintaxis de cualquier función desde el cuadro de dialogo “Insertar Función”.
Primero seleccionamos la función, en este ejemplo utilizaremos la función de PROMEDIO: Es recomendable visualizar la ayuda que nos ofrece Excel con respecto a cualquier función, una vez que seleccionaste la función y pulsas “Ayuda sobre esta función” se abre un cuadro de dialogo de la ayuda de la función seleccionada, esto es útil para ir familiarizándonos con el lenguaje que utiliza Excel.
Al principio costará trabajo comprender algunos tutoriales, sin embargo, expone de forma sencilla ejemplos sobre cómo utilizar las funciones.
Siguiendo con el ejemplo, si pulsamos aceptar, nos abrirá un cuadro de dialogo “Argumentos de función”: En el cuadro “Argumentos de función” básicamente nos pide Excel que le demos argumentos para definir el resultado de la función. ¿Qué es un argumento? Es el valor que proporciona información para una acción o resultado.
En la figura anterior observamos el nombre de la función (rojo) y su descripción de la función (azul), los argumentos y la descripción del argumento (morado) y muestra un resultado según el argumento (naranjado). Esta estructura se muestra en todas las funciones de Excel, lo que puede variar son las descripciones y el número de argumentos solicitados.
Debemos seleccionar los argumentos, para esto damos clic en el siguiente botón: En seguida te llevará a la hoja de cálculo y seleccionarás el argumento o datos para la función PROMEDIO: Una vez seleccionado el argumento, teclearas “Intro” y te llevará al cuadro de dialogo: Pulsamos “Aceptar” y obtenemos el promedio 41,959.
En los casos que nos equivoquemos en el argumento o exista un error de una celda, lo podemos visualizar en el Resultado de la fórmula como error.
Ejemplo de aplicación de funciones.
A continuación veremos las funciones más utilizadas para simplificar base de datos.
AÑO La función obtiene el año de una fecha específica: Iniciamos con el signo “=” Nos abre cuadro de diálogo y seleccionamos la fecha que deseamos obtener el año: Obtenemos como resultado en la celda D6 con la función AÑO: DIA La función obtiene el día de una fecha específica: Iniciamos con el signo “= Nos abre cuadro de diálogo y seleccionamos la fecha que deseamos obtener el día: Obtenemos como resultado en la celda B2 con la función DIA: MES Obtenemos el mes de una fecha específica. Iniciamos con el signo = Nos abre cuadro de diálogo y seleccionamos la fecha que deseamos obtener el día: Obtenemos como resultado en la celda B2 con la función MES: DIASEM Identifica el día de la semana devolviendo un número del 1 al 7: Nos abre cuadro de diálogo y seleccionamos la fecha que deseamos obtener el día de la semana. El tipo Excel no da opciones para seleccionar el día de la semana: Obtenemos como resultado en la celda B2 el día de la semana (6) equivalente a viernes de la función DIASEM: FECHA Devuelve el número de serie secuencial que representa una fecha determinada: Nos abre cuadro de diálogo y seleccionamos el año, mes y día: Como resultado obtenemos en la celda A2 la fecha con la función Fecha: FRAC.AÑO Calcula la fracción de año que representa el número de días enteros entre fecha inicial y fecha final.
Nos abre cuadro de diálogo y seleccionamos la fecha de inicio, la fecha fin y la base para calcular la fracción de año: En el ejemplo utilizamos una base de 360 días (2) y obtenemos en la celda C2 la fracción del año entre dos fechas: ABS Devuelve el valor absoluto de un número.
Nos abre cuadro de diálogo y el número que le va quietar el signo negativo: En el ejemplo nos da el valor absoluto de la celda A2 con la función ABS: ALEATORIO Devuelve un número real aleatorio mayor o igual que 0 y menor que 1, distribuido uniformemente.
Cambia al actualizar la hoja de cálculo: Nos abre un cuadro de dialogo en el cual nos dice que esta función no tiene argumentos y que el número aleatorio lo genera entre el rango de 0 a 1: Al dar clic en aceptar, nos da como resultado un número aleatorio en la celda A2 con la función ALEATORIO: ALEATORIO.ENTRE Devuelve el número entre dos rangos de números que especifiquemos: En el cuadro de dialogo, nos pide un número inferior y un número superior para generar un número aleatorio entre el número inferior y superior: Obtenemos en la celda A2 un número aleatorio entre el número inferior y superior: ENTERO Redondea un número hasta el entero inferior más próximo: En el cuadro de dialogo seleccionamos el número: Obtenemos en la celda B2 el número entero más próximo utilizando la función “ENTERO”: REDONDEAR La función REDONDEAR redondea un número a un número de decimales especificado.
El cuadro de diálogo nos pide el número a redondear y los decimales a considerar, en el ejemplo le decimos que nos redondea a 1 decimal: Por lo tanto, la función Redondear, redondea a un decimal como lo observamos en la celda B2: REDONDEAR.MAS La función redondear.mas es similar a la función redondear, excepto que siempre redondea el número superior más próximo, alejándolo del cero.
El cuadro de diálogo nos pide el número a redondear y los decimales a considerar, en el ejemplo le decimos que nos redondea a 2 decimales: Por lo tanto, la función REDONDEAR.MAS da como resultado en la celda B2 lo siguiente: REDONDEAR.MENOS La función REDONDEAR.MENOS es similar a la función de REDONDEAR, excepto que siempre redondea un número hacia abajo: El cuadro de diálogo nos pide el número a redondear y los decimales a considerar, en el ejemplo le decimos que nos redondea a 2 decimales: Por lo tanto, la función REDONDEAR.MENOS da como resultado en la celda B2 lo siguiente: SUBTOTALES Devuelve un subtotal en una lista o base de datos en función al argumento seleccionado. Usualmente se utiliza los subtotales cuando hay filas ocultas o por el resultado de un filtro. Esta función sirve para rangos verticales. La función de SUBTOTALES siempre requiere un argumento numérico como primer argumento: Función Incluye valores ocultos Pasa por alto valores ocultos PROMEDIO 1 101 CONTAR 2 102 CONTARA 3 103 MAX 4 104 MIN 5 105 PRODUCTO 6 106 DESVEST 7 107 DESVESTP 8 108 SUMA 9 109 VAR 10 110 VARP 11 111 En el siguiente ejemplo utilizaremos la función de subtotales con el argumento numérico 1 (promedio) utilizando un filtro: Enseguida nos pide el argumento de la función, para este caso queremos subtotales promedio, por lo tanto el argumento es 1: En las referencias, seleccionamos el rango de la columna C5:C30, en la cual va obtener el promedio: Al dar clic en aceptar, obtenemos primero el promedio del rango C5:C30: Sin embargo, si filtramos por África, la celda C32 de la función Subtotales nos muestra el promedio de África: Como podemos observar, al filtrar por el continente de África los valores de la función de subtotales cambia y nos da el valor promedio de la población del año 1950. Por lo tanto cada vez que cambiemos el filtro, en la celda C32 nos mostrará el valor promedio de lo que se esté filtrando.
La función SUBTOTALES es útil cuando haya filas ocultas y requerimos de forma inmediata el valor que deseamos ver en la pantalla.
Ejemplo de SUBTOTALES con el argumento 2 (contar): Al dar Aceptar, Excel nos va contar las celdas seleccionadas. En este ejemplo, la celda C32 obtenemos como recuento de 26 datos: Si filtramos por el continente América, observamos que ha cambiado el recuento, según lo visualizado en la pantalla: En la barra de estado corroboramos que efectivamente son 13 datos: Vamos a ver otro ejemplo de SUBTOTALES con el argumento 9 (SUMA). Lo que obtenemos con esta función de subtotales es sumar las celdas visibles. Primero damos la instrucción de hacer subtotales con el argumento 9 (suma): La celda C32 obtenemos la suma de las celdas visibles de la columna C: Vamos a filtrar por el continente de Europa y observamos que la función suma únicamente las celdas visibles de la columna C: Podemos comprobar el valor de subtotales de la celda C32 es igual a la suma que se observa en la barra de estado.
SUMA La función SUMA, suma todos los números especificados como argumentos. Cada argumento puede ser un rango, una referencia de celda, una matriz, una constante, una fórmula o el resultado de otra función. Por ejemplo, SUMA(A1:A5) suma todos los números que están contenidos en las celdas A1 hasta A5. Otro ejemplo, SUMA(A1; A3; A5) suma los números que están contenidos en las celdas A1, A3 y A5. Ejemplo de la función SUMA: Escribimos el argumento de la suma. En este caso sumaremos el rango de C4:C11 y damos aceptar: Obtenemos en la celda C12 el valor de la función SUMA. Esta función es de las más sencillas y utilizadas en Excel, también podemos hacer la suma de la siguiente manera: El resultado es el mismo, sin embargo, hay que considerar que Excel solo te permite hasta 255 argumentos y es más tardado, ya que agregamos argumento por argumento.
SUMAPRODUCTO Multiplica los componentes correspondientes de las matrices suministradas y devuelve la suma de esos productos. En el siguiente ejemplo deseamos obtener los gastos operativos del primer trimestre de cada una de las sucursales y se tiene un porcentaje estimado por mes con respecto a la venta.
Sin embargo, para no hacer el cálculo de registro por registro, utilizamos la función SUMAPRODUCTO, en la cual podrá hacer la operación de una sola acción. Primero buscamos la función: El cuadro de dialogo nos pide la primera matriz como referencia, en este caso, seleccionamos las celdas C5:E5 que corresponden a las ventas por mes. La segunda referencia seleccionamos las celdas $C$16:$E$16 que corresponde al porcentaje estimado de gastos operativos. En esta última referencia le indicamos con un signo de $ que nos fije las columnas y las filas Como podemos observar, el número de columnas debe ser igual en cantidad, en la matriz 1 iniciamos en la columna C y terminamos en la columna E, de igual manera sucede en la matriz 2. Al dar aceptar obtenemos en la celda I5 el resultado de la función SUMAPRODUCTO y podemos comprobar el resultado en las celdas G18 que es la suma de cada uno de los registros Ahora bien, copiamos la formula y obtenemos los gastos operativos por sucursal: SUMAR.SI La función SUMAR.SI sirve para sumar los valores en un rango que cumple los criterios especificados.
El criterio del rango podemos especificarlo con valores o contantes, es decir, la función SUMAR. SI nos ayuda a obtener una suma especifica según el criterio que deseamos que sume.
En el siguiente ejemplo podemos realizar una suma que cumpla con la condición de sumar la población por continente. Primero buscamos la función: El cuadro de dialogo, nos pide seleccionar el rango. El rango son los datos que vamos aplicar la condición o el criterio. En este ejemplo seleccionaremos el rango A5:A30 el cual es el rango de los continentes ya que deseamos que nos sume por continente. El Criterio es la condición que determina que celdas deben sumarse. El Rango de Suma son las celdas que van a sumar el criterio aplicado en el rango: Antes de dar aceptar, colocamos el signo de pesos $ para que nos fije columnas y filas de la siguiente manera: Al dar aceptar obtenemos la suma del continente “América” en la celda C32: Ahora copiamos la formula hasta obtener la suma por continente y por año: Ahora realizaremos otro ejemplo donde apliquemos la función SUMAR.SI dando un criterio de >= o <=.
Como podemos observar en el Rango seleccionamos C30:E37, en el cual aplicaremos el Criterio de las ventas mayores o iguales a 60,000, para esto último se escribe de la siguiente manera: “>=”&D41 Donde D41 es la celda criterio. Dando este criterio obtenemos en la celda G41 la suma de los valores que sean mayores o iguales a 60,000 Ahora realizaremos un ejemplo utilizando el criterio <=: Obtenemos en la celda G43 la suma de los valores menores o iguales a 35,000 y en la celda G41 la suma de los valores mayores o iguales a 60,000: Lo comprobamos con el formato condicional obtenemos lo siguiente: SUMAR.SI.CONJUNTO Es similar a la función SUMAR.SI con la diferencia que SUMAR.SI.CONJUNTO agregamos varios criterios o condiciones para especificar una suma determinada. Esta función es importante cuando tenemos varios datos y deseamos ser más específicos en las sumas.
Aplicaremos un ejemplo en una base de datos que contiene la facturación por cliente, producto, almacén, lote, etc. Por lo tanto podemos aplicar varias combinaciones como por ejemplo: determinar la facturación de un cliente en específico con un producto específico (dos condiciones) o determinar la cantidad vendida de un cliente con un producto específico y que sea degustación (tres condiciones) y, así sucesivamente podemos hacer sumas que cumplan varios criterios en conjunto.
A continuación aplicaremos la función con el fin de determinar las ventas netas que se la ha hecho a la “Tienda_9” con el producto “Panque”: En la figura anterior se muestra una base de datos de 1,4816 registros de facturas y notas de crédito.
En la columna A hace referencia al tipo de documento Fac a una factura y NCEL hace referencia a una nota de crédito. En la columna G nos dice el nombre de la tienda y en la columna K el nombre del producto. Por lo tanto, tenemos tres condiciones para utilizar la función SUMAR.SI.CONJUNTO y con esta deseamos obtener las ventas netas de la Tienda_9 del producto Panque.
Primero buscamos la función en la categoría Matemáticas y trigonométricas: Nos muestra el cuadro de diálogo, en el cual nos pide los rangos y los criterios: En el Rango_suma seleccionamos el rango que deseamos que sume, es decir son los valores que vamos a considerar en la suma. El rango de la suma es P2:P14817 que es el valor de la factura o nota de crédito. En seguida nos pide el Rango_criterio1 el cual es el primer criterio que vamos a considerar según el tipo de documento, para el ejemplo es el rango de celdas de A2:A14817 del cual contiene dos variables (Fac y NCEL). El Criterio1 es la variable que vamos a considerar en la suma del Rango_criterio1, en este caso le decimos que es la celda I148824 (que contiene la variable Fac). El Rango_criterio2 es el segundo criterio o variable a considerar en la suma, en este caso son las tiendas, el rango es G2:G14817 del cual contiene todas las tiendas que se surte el producto, para este caso definiremos el Criterio2 con la celda J14821 que contiene la variable Tienda_9. El Rango_criterio3 seleccionamos el rango de celdas K2:K14817 que hace referencia a todos los productos. El Criterio3 seleccionamos la celda K14821 que contiene la variable “Panque”.
Al dar clic en “aceptar” obtenemos la suma de las facturas de la Tienda_9 del producto Panque: Obtenemos como resultado en la celda L14821 la facturación de la Tienda_9 con un valor de 20,633.
Como podemos observar la función quedaría de la siguiente manera: Se recomienda fijar columnas y filas del Rango de Suma y de los Rangos de los criterios.
Rango_suma  $P$2:$P:14817 Rango_criterio1  $A$2:$A$14817 Criterio1  I14821 Rango_criterio2  $G$2:$G$14817 Criterio2  J4821 Rango_criterio3  $K$2:$K14817 Criterio3  K14821 Como podemos observar los criterios 1, 2 y 3 no se fijaron, ya que son las variables cambiantes y podemos utilizar la misma función para obtener notas de crédito y obtener las ventas netas: TRUNCAR Como recordarán la función de Redondear redondea un número superior o un número inferior según el valor. En la función de TRUNCAR le decimos que quite parte de los decimales pero sin redondear de más o de menos. Primero buscamos la función: Abre el cuadro de dialogo, seleccionamos el Número y le decimos que nos trunque los decimales a 2: El resultado de la celda B2 utilizando la función TRUNCAR es 5236.32 Como podemos observar, la función de TRUNCAR recorto los decimales sin redondear.
CONTAR La función CONTAR cuenta la cantidad de celdas que contienen números y cuenta los números dentro de la lista de argumentos. Use la función CONTAR para obtener la cantidad de entradas en un campo de número de un rango o matriz de números. Primero buscamos la función: En el cuadro de diálogo seleccionamos los argumentos de las celdas con valores que deseamos que cuente: Obtenemos como resultado de la función en la celda C59: MAX Devuelve el valor máximo de un conjunto de valores. Primero buscamos la función: En el cuadro de diálogo, seleccionamos el rango máximo de las celdas C48:C55 Obtenemos en la celda C59 el valor máximo del conjunto de celdas C48:C55: El valor máximo del conjunto de celdas seleccionadas es la celda C51 MIN Devuelve el valor mínimo de un conjunto de valores. Primero buscamos la función: En el cuadro de dialogo seleccionamos el rango de celdas C48:C55: Obtenemos el resultado mínimo en la celda C59 de acuerdo a las celdas seleccionadas: PROMEDIO Nos da como resultado a media aritmética de un conjunto de valores seleccionados. Primero buscamos la función: En el cuadro de dialogo seleccionamos el conjunto de celdas que contengan valores: Obtenemos en la celda C59 el promedio de ventas de enero: PROMEDIO.SI Devuelve el promedio (media aritmética) de todas las celdas de un rango que cumplen unos criterios determinados. Primero buscamos la función: El cuadro de dialogo nos pide seleccionar el Rango que son las celdas que se desea evaluar, en este ejemplo seleccionaremos el siguiente rango de celdas: $K$2:$K$14817 de las cuales hacen referencia a la columna del nombre de producto. Nos pide seleccionar la variable o Criterio a evaluar, en el ejemplo seleccionamos la celda O14821 que contiene el producto “Bolillo”. Por último nos pide seleccionar el Rango_promedio, que se refiere al rango de los valores a evaluar, seleccionaremos las celdas $P$2:$P$14817 que son los importes de las facturas y notas de crédito: Obtenemos en la celda P14821 el promedio de ventas del producto “Bolillo”: Producto Bolillo Promedio de ventas 32,798 BUSCARV Puede usar la función BUSCARV para buscar la primera columna de un rango de celdas y devolver un valor de cualquier celda de la misma fila del rango. Usualmente la utilizamos cuando tenemos que buscar un valor específico de diferentes matrices o base de datos. Debe cumplir con la condición de que la variable buscada sea iguales entre matrices.
Como ejemplo, tenemos dos bases de datos: En la pestaña verde llamada “Costo lote” tenemos el costo unitario por lote y tiene un total de 3,877 registros. En la pestaña azul llamada “BD_PANADERÍA” tenemos 14,817 registros de facturas, notas de crédito y bonificaciones y deseamos agregar el costo unitario por lote en la columna O: Primero nos posicionamos en la celda O2 y buscamos la función BUSCARV: En el cuadro de diálogo nos pide el Valor_buscado que es la variable que tenemos en ambas bases pestañas y va ser la referencia para buscar el costo unitario, seleccionamos N2 (que es el número de lote).
La Matriz_buscar_en es la base de datos (pestaña “Costo lote”) que buscaremos el dato buscado seleccionaremos primero la columna en la cual se encuentra el Valor_buscado y seleccionaremos hasta la columna donde se encuentra el dato que nos interesa, para este ejemplo es como sigue: El Indicador_columnas es el número de columnas de la Matriz_buscar_en desde la cual debe volverse el valor que coincida. En este caso son 2 columnas, y Ordenado es el valor lógico para encontrar la coincidencia más cercana en la primera columna (1) o encontrar la coincidencia Exacta (0). Para el ejemplo, escribimos “0” ya que queremos el valor Exacto.
Obtenemos en la celda O2 el valor buscado del lote: Copiamos la función en las demás filas y obtenemos el costo unitario por lote en la pestaña de BD_PANADERÍA CONCATENAR La función CONCATENAR une hasta 255 cadenas de texto en uno solo. Los elementos que se unirán pueden ser texto, números, referencias de celda o una combinación de estos elementos. Primero buscar la función: En el cuadro de texto nos pide las celdas a concatenar o juntar en una sola, por lo que cada Texto, seleccionamos las celdas a concatenar o juntar: Observamos en la celda C2 que junto la celda A2 y la celda B2: Segundo ejemplo de concatenar con constantes. Deseamos concatenar pero separado con una diagonal “/”: Obtenemos como resultado el concatenado pero separado con una diagonal “/”: Como observación todas las constantes en una función deben ir entre comillas.
DERECHA DERECHA devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres especificado. Buscamos la función: En el cuadro de dialogo, nos pide seleccionar el Texto en el cual separaremos los caracteres a la derecha y el Num_de_caracteres nos pide el número de caracteres que especifiquemos para separar del texto: Como resultado en la celda B2 únicamente extrae los 7 caracteres del texto seleccionado EXTRAE Devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición y en función del número de caracteres que especifique. Es decir, especificamos el carácter o los caracteres que deseamos extraer en una determinada posición. La función se encuentra en: En el cuadro de diálogo colocamos en Texto la celda que deseamos extraer el carácter A2; La Posición_inicial es la posición del primer carácter que se desea extraer, en el ejemplo escribimos 5, ya que deseamos extraer el símbolo “-“. Num_de_caracteres es el número de caracteres que se desea extrae, en este ejemplo escribimos 1: Como resultado obtenemos en la celda B2 el carácter extraído: HALLAR Las funciones HALLAR busca una carácter o texto dentro de una cadena de texto y devuelven el número de la posición inicial del carácter o texto. A diferencia de la función de Extraer, esta función nos dice en qué posición se encuentra el texto buscado dentro de una celda. La función se encuentra en: El cuadro de diálogo nos pide el Texto_buscado, que es el texto o carácter que se desea encontrar dentro de la celda. Dentro_texto es la celda en donde se encuentra el texto o carácter. Núm_inicial es el número de carácter en Dentro_texto donde se desea iniciar la búsqueda, si se omite, se usa 1.
En el ejemplo anterior decidimos buscar el carácter “-“dentro de la celda A2 desde la posición 1 y obtenemos en la celda B2 el número de la posición donde se encuentra el carácter “-“: El resultado obtenido (5) es la posición inicial del carácter “-“.
IZQUIERDA Devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres que especifique el usuario. Buscamos la función: En el cuadro de diálogo el Texto es la celda que extraeremos desde la izquierda. Núm_de_caracteres son los números de caracteres que se extraerán de la celda seleccionada: Obtenemos en la celda B2 los cinco caracteres de izquierda a derecha: LARGO Devuelve el número de caracteres de una cadena de texto. Buscamos la función en: En el cuadro de diálogo nos pide el Texto donde se desea conocer el número total de caracteres: Obtenemos el número de caracteres (12) de la Celda A2: MAYUSC Los textos seleccionados los convierte en mayúsculas. La función la encontramos en: Seleccionamos la celda que deseamos convertir en mayúscula: Observamos cómo cambia el texto en mayúsculas: MINUSC Los textos seleccionados los convierte en minúsculas. La función la encontramos en: Seleccionamos la celda que deseamos convertir en minúsculas: Observamos cómo cambia el texto en minúsculas: NOMPROPIO Cambia a mayúscula la primera letra del argumento texto y cualquiera de las otras letras de texto que se encuentren después de un carácter que no es una letra. Convierte todas las demás letras a minúsculas. La función la encontramos en: Seleccionamos la celda que deseamos que cambia las primeras letras a mayúsculas: Observamos que nos cambia la primera letra a mayúsculas de todo el argumento del texto: SI Es una función Lógica que devuelve un valor si una condición especificada se evalúa como VERDADERO y otro valor si se evalúa como FALSO. Es decir, evalúa una prueba lógica (<; >; =; <=; >=; <>) y devuelve el valor especifico que le indiquemos. La función la encontramos en: El cuadro de diálogo en la Prueba_lógica colocamos la condición que deseamos evaluar. En el ejemplo le decimos que la venta de la sucursal si es mayor o igual al promedio de ventas y como Valor_si_verdadero nos dé como resultado “Arriba del promedio” y como Valor_si_falso nos dé como resultado “Abajo del promedio”: Obtenemos como resultado, lo siguiente: Lo que obtenemos va ser según el resultado de la prueba lógica, en la celda I48 obtuvimos como resultado “Arriba del promedio” ya que 143,560 es mayor que el promedio de 118,158. Ahora copiamos la función y obtenemos los resultados según la prueba lógica: XII. Proteger Datos En los libros de Excel y hojas de cálculo se puede usar contraseñas para impedir que otras personas abran o modifiquen los documentos. La desventaja: si se olvida la contraseña, Microsoft Excel no tiene opción para recuperar contraseña o reestablecer contraseña, por lo que se deberá tener cuidado al proteger un libro de Excel. Primero debemos ingresar en la cinta de opciones en la pestaña de Revisar en la sección de cambios: Proteger hoja.
Con la característica Proteger hoja, puede seleccionar la protección con contraseña y permitir o impedir que otros usuarios seleccionen, apliquen formato, inserten, eliminen, ordenen o modifiquen áreas de la hoja de cálculo. Al pulsar el botón de “Proteger Hoja” con abre un cuadro de dialogo: Como podemos observar el cuadro de dialogo “proteger hoja” nos pide que escribamos una contraseña y también nos pide que seleccionemos los permisos que deseamos dar a la hoja de cálculo actual.
Seleccionamos los permisos y escribimos la contraseña, al pulsar el botón “aceptar” nos vuelve a solicitar la contraseña para validación: Excel nos pide validar contraseña: Al validar la contraseña, podemos observar que únicamente tenemos habilitados los permisos que seleccionamos en la hoja de cálculo. Para volverlos habilitar, debemos ir a la cinta de opciones en la pestaña de “Revisar” y pulsamos “Desproteger Hoja” e inmediatamente nos solicitará la contraseña.
Proteger estructura del Libro.
Podemos 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 aplican a todo el libro.
Pulsamos el botó “Proteger Libro” y nos abrirá un cuadro de diálogo “proteger estructura y ventanas” en el cual nos solicita una contraseña y validación de la contraseña: Validación de la contraseña: Al proteger la estructura del libro, estaremos restringiendo modificaciones en la creación o eliminación de hojas de cálculo.
Cifrar una con contraseña Tenemos la opción de abrir un libro con una contraseña. Por lo que podemos definir una contraseña para abrir un archivo que deseamos proteger. Generamos la contraseña en desde el botón de “Archivo” en la sección de “información” en el botón de “proteger libro” y por último “cifrar con contraseña” Excel nos solicitará una contraseña para cifrar el libro de Excel. Cada vez que abramos el archivo cifrado, nos va solicitar la contraseña para abrir el archivo de Excel. Hay que tener cuidado, ya que en Excel no se puede restablecer contraseña.
XIII. Inmovilizar Mantiene visibles las filas o columnas mientras el resto de la hoja de cálculo de desplaza a partir de la selección actual. Utilizamos esta sección para inmovilizar los títulos o columnas cuando manejamos base de datos de gran volumen y se complica cuando nos desplazamos fuera de un encabezado o una columna. El botón lo encontramos en la pestaña de “Vista” en la cinta de opciones: Cuando pulsamos el botón, observamos tres opciones: Inmovilizar paneles, Inmovilizar fila superior e Inmovilizar primera columna: Como podemos observar en los botones, la parte sombreado es la fila superior o primera columna queda inmovilizada. Antes de pulsar cualquiera de los botones, primero debemos situarnos en la fila o columna que queramos que quede inmovilizada. Por ejemplo: Para inmovilizar paneles debemos seguir lo siguiente: Pulsamos el botón “Inmovilizar” e “Inmovilizar paneles”: Obtenemos lo siguiente: Para inmovilizar la fila superior, pulsamos el botón “inmovilizar fila superior” e inmediatamente inmovilizará la primera fila de la hoja de cálculo: Obtenemos lo siguiente: Para inmovilizar la primera columna obtenemos lo siguiente: XIV. Diseño de página El diseño de página es indispensable para preparar la impresión. Antes de imprimir una hoja de cálculo de Excel que contenga una gran cantidad de datos o varios gráficos, puede ajustarse rápidamente en la vista Diseño de página. En esta pestaña se puede cambiar el diseño y el formato de los datos del mismo modo que en la vista Normal. También puede usar reglas para medir el ancho y el alto de los datos, cambiar la orientación de la página, agregar o cambiar encabezados y pies de página, establecer los márgenes para la impresión, mostrar u ocultar las líneas de cuadrícula, mostrar u ocultar los encabezados de las filas y columnas y especificar las opciones de ajuste de escala.
A continuación describiremos de forma breve los botones que nos presenta la pestaña de Diseño de página. Los botones los encontramos en la cinta de opciones en la pestaña de “Diseño de página”.
Temas Puede dar formato de manera fácil y rápida a un documento entero para proporcionarle un aspecto profesional y moderno aplicando un tema del documento. Un tema del documento es un conjunto de opciones de formato que incluyen un conjunto de colores, un conjunto de temas de fuentes (incluyendo fuentes para encabezados y texto principal) y un tema de efectos (incluidos efectos de líneas y relleno).
Al dar clic en cualquiera de los temas predefinidos por Excel, observamos que Excel nos cambia el tipo de fuente y cambia la paleta de colores de acuerdo al Tema seleccionado. Por default, el programa tiene como tema el “Office”.
Configurar página.
Antes de imprimir una página es recomendable configurar la página como los márgenes, la orientación de la hoja, el tamaño de la hoja, fondo y los títulos. En la sección de “configurar página” encontraremos botones rápidos para configurar la página de impresión o si lo preferimos abriendo el cuadro de diálogo de “configurar página”. Para ingresar al cuadro de dialogo debemos dar clic en el siguiente botón: Al dar clic nos abre el cuadro de diálogo: En “Configurar página” obtenemos todas las opciones para modificar las hojas que deseamos imprimir.
La pestaña por default que nos abre es “página” aquí podemos modificar la orientación de la hoja (vertical u horizontal), ajustar la escala y definir el tipo de papel. También están los botones de “Imprimir…”, “Vista preliminar” y “opciones”.
En la pestaña de “Márgenes” podemos modificar los márgenes de impresión de los cuatro lados, el margen del encabezado y pie de página. En el recuadro de medio se puede ir visualizando las modificaciones o si se prefiere en la vista preliminar.
En la pestaña de “Encabezado y pie de página” observamos dos botones “personalizar encabezado” y “personalizar pie de página” si pulsamos cualquiera de los dos botones nos abre un cuadro de dialogo.
En el cual podemos ir modificando el encabezado o pie de página.
Si pulsamos “personalizar encabezados” nos abre un cuadro de diálogo como sigue: El programa divide el encabezado en tres secciones (izquierda, central y derecha) y en cada una de se puede escribir el texto deseado. También podemos observar botones de los cuales no servirán para introducir códigos o modificar el aspecto del encabezado.
Botón Descripción Para cambiar el texto escrito y seleccionado. Al hacer clic sobre este botón aparece el cuadro de diálogo fuente.
Para que aparezca el número de la página. Al hacer clic sobre este botón aparece en la sección &[Página] de forma que a la hora de imprimir la hoja, aparecerá el número de página correspondiente a la hoja impresa.
Incluye el número total de páginas a imprimir del libro de trabajo. Al hacer clic sobre este botón aparece &[Páginas] y a la hora de imprimir saldrá el número total de páginas.
Incluye la fecha del ordenador. Al hacer clic sobre este botón aparece &[Fecha] y en la impresión saldrá la fecha real.
Al hacer clic sobre este botón aparece &[Hora] y en la impresión saldrá la hora real.
Al hacer clic sobre este botón aparece [Ruta de acceso]&[Archivo] y en la impresión saldrá el nombre del libro de trabajo.
Al hacer clic sobre este botón aparece &[Archivo] y en la impresión saldrá el nombre del libro de trabajo Al hacer clic sobre este botón aparece &[Etiqueta] y en la impresión saldrá el nombre de la hoja.
Al hacer clic sobre este botón aparecerá el cuadro de diálogo para elegir la imagen a insertar y una vez elegida ésta en el recuadro del encabezado o pie de página pondrá &[Imagen] y en la impresión saldrá la imagen seleccionada. Se puede utilizar para incluir el logotipo de la empresa.
Para cambiar el aspecto de la imagen seleccionada. Este botón solamente estará activo en caso de haber añadido una imagen en el encabezado o pie de página.
Al hacer clic sobre este botón aparece el cuadro de diálogo Formato de imagen para poder elegir los cambios de aspecto de la imagen deseados.
Si pulsamos “Personalizar pie de página” nos abre un cuadro de diálogo como sigue: Cómo podemos observar es muy similar al botón de personalizar encabezado. Con la diferencia de que las modificaciones se hacen en el pie de página. Los botones son los mismos descritos anteriormente.
Ejemplo de cómo quedaría el encabezado y pie de página: Pan Gourmet Sin embargo, también podemos hacer las modificaciones desde la hoja de cálculo haciendo clic en el botón de diseño de página que se observa en la barra de estado o desde la cinta de opciones en la pestaña de “Vista” y después en el botón “diseño de página”: Al hacer clic la página se va observar cómo sigue: Como podemos observar en la parte superior de la hoja se ve la siguiente leyenda “Haga clic para agregar encabezado”, o “Haga clic para agregar pie de página” al dar clic se observa que se activan tres secciones de las cuales podrás modificar o agregar texto deseado, también observarás que se activará una nueva pestaña en la cinta de opciones, llamada “Diseño” en esta observarás los botones descritos anteriormente.
En la pestaña de “Hoja” podemos seleccionar la fila superior o columnas de la izquierda de tal manera que aparezca en todo el documento cuando se imprima. También tenemos la opción de imprimir las líneas de división de las celdas, los encabezados de columnas y filas y la forma de cómo se imprimirá el documento.
Al dar clic en el botón de Repetir filas en extremo superior, nos llevará a la hoja de cálculo y nos indicará con una flecha negra qué fila deseamos que se repita en todas las hojas para imprimir: Seleccionamos la fila 1, ya que contiene los títulos del documento. Tecleamos “Intro” y aceptamos los cambios. Básicamente lo que hicimos fue seleccionar los títulos del documento para que aparezca en todas las hojas a la hora de imprimir.
De esta misma forma se puede hacer con el botón de Repetir columnas a la izquierda, nos llevará a la hoja de cálculo y nos indicará con una flecha negra qué columna deseamos que aparezca en todas las hojas para imprimir: En este ejemplo seleccionamos la columna A y tecleamos “Intro” y aceptamos los cambios.
Pasos para imprimir en Excel Primero seleccionamos todo el documento que deseamos imprimir y enseguida en la cinta de opciones en la pestaña de “Diseño de página”, damos clic en “área de impresión” y enseguida en “Establecer área de impresión”: Damos clic en el botón de “diseño de página” que está en la barra de estado o desde la cinta de opciones en la pestaña de “Vista” y luego en el botón de “diseño de página” Observamos lo siguiente: En la barra de estado observamos la cantidad de hojas sin configurar: Cómo podemos observar Excel nos muestra cómo esta nuestro documento sin configurar para imprimir, para esto necesitamos definir primero la orientación de la página (Vertical u Horizontal). Para este ejemplo seleccionaremos de forma horizontal: En la barra de estado observamos que ha cambiado el número de páginas, por el simple cambio de orientación de página: Ahora seleccionaremos el tamaño de hoja en la pestaña de “Diseño de página”, damos clic en “Tamaño” y seleccionamos la hoja deseada. Para este ejemplo seleccionaremos Oficio: Enseguida ajustamos la escala, con la finalidad de que nuestro documento quepa en una hoja oficio de forma horizontal. Para esto vamos ajustando la escala hasta considerar que es un tamaño adecuado para imprimir. En este ejemplo reducimos la escala a 76%: Como podemos observar con una escala a 76% (la cual es adecuada) podemos imprimir todo el documento, con un total de 380 hojas como se observa en la barra de estado: Ahora procedemos a configurar los encabezados. Para esto damos Clic en la parte de encabezados y agregamos del lado derecho el nombre del documento, en la parte central agregamos el nombre de la empresa y del lado izquierdo agregamos la fecha y hora: Ahora procedemos a configurar el pie de página: Para ir visualizando la impresión de la hoja, damos clic en “Archivo” luego en “Imprimir” y damos clic en “Mostrar vista preliminar”: Observamos lo siguiente: También podemos visualizar la página desde el cuadro de dialogo de “configurar página” previamente visto.
De esta manera ya podemos imprimir el documento.
XV. Anexos Anexo 1. Pantalla Inicial Anexo 2. Errores típicos Anexo 3. Diferencia entre fórmula y función Anexo 4. Movimientos rápidos Anexo 5. Teclas de funciones.
...

Tags:
Comprar Previsualizar