Unidad 5 excel

99
Tabla de contenido UNIDAD 5 – MS OFFICE EXCEL 2010..................................1 INTRODUCCIÓN....................................................1 RECONOCIMIENTO DEL AMBIENTE DE TRABAJO........................1 EDICIÓN DE CELDAS.............................................5 OPCIONES DE AUTORRELLENO......................................6 LISTAS PERSONALIZADAS.........................................9 ADMINISTRACIÓN DE LA HOJA......................................12 SEGURIDAD DE DATOS...........................................12 MANEJO DE FILTROS Y ORDENACIÓN DE DATOS......................19 FORMATO CONDICIONAL..........................................25 VALIDACIÓN DE DATOS..........................................28 MANEJO DE DATOS................................................37 TIPOS DE DATOS...............................................37 TIPOS DE OPERADORES Y PRECEDENCIA............................39 TIPOS DE ERRORES.............................................42 FÓRMULAS.......................................................44 FORMULACIÓN SIMPLE CON VALORES CONSTANTES....................44 REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS....................46 FUNCIONES BÁSICAS..............................................47 DESCRIPCIÓN, SINTÁXIS........................................47 FUNCIONES DE BÚSQUEDA..........................................50 BUSCARV......................................................50 BUSCARH......................................................52 FUNCIONES LÓGICAS..............................................54 SI CONDICIONAL SIMPLE........................................54 SI CONDICIONAL ANIDADO.......................................57 FUNCIÓN Y....................................................59 FUNCIÓN O....................................................61 GRÁFICOS ESTADÍSTICOS..........................................63

Transcript of Unidad 5 excel

Page 1: Unidad 5 excel

Tabla de contenidoUNIDAD 5 – MS OFFICE EXCEL 2010...................................................................................................1

INTRODUCCIÓN..............................................................................................................................1

RECONOCIMIENTO DEL AMBIENTE DE TRABAJO.......................................................................1

EDICIÓN DE CELDAS...................................................................................................................5

OPCIONES DE AUTORRELLENO.........................................................................................6

LISTAS PERSONALIZADAS...........................................................................................................9

ADMINISTRACIÓN DE LA HOJA.....................................................................................................12

SEGURIDAD DE DATOS.............................................................................................................12

MANEJO DE FILTROS Y ORDENACIÓN DE DATOS.....................................................................19

FORMATO CONDICIONAL.........................................................................................................25

VALIDACIÓN DE DATOS............................................................................................................28

MANEJO DE DATOS......................................................................................................................37

TIPOS DE DATOS.......................................................................................................................37

TIPOS DE OPERADORES Y PRECEDENCIA..................................................................................39

TIPOS DE ERRORES...................................................................................................................42

FÓRMULAS...................................................................................................................................44

FORMULACIÓN SIMPLE CON VALORES CONSTANTES..............................................................44

REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS.....................................................................46

FUNCIONES BÁSICAS....................................................................................................................47

DESCRIPCIÓN, SINTÁXIS...........................................................................................................47

FUNCIONES DE BÚSQUEDA..........................................................................................................50

BUSCARV..................................................................................................................................50

BUSCARH..................................................................................................................................52

FUNCIONES LÓGICAS....................................................................................................................54

SI CONDICIONAL SIMPLE..........................................................................................................54

SI CONDICIONAL ANIDADO......................................................................................................57

FUNCIÓN Y...............................................................................................................................59

FUNCIÓN O...............................................................................................................................61

GRÁFICOS ESTADÍSTICOS..............................................................................................................63

INTRODUCCIÓN AL DISEÑO......................................................................................................63

CONFIGURACIÓN DE GRÁFICOS ESTADÍSTICOS........................................................................67

FUENTE:............................................................................................................................................71

Page 2: Unidad 5 excel
Page 3: Unidad 5 excel

Tabla de ilustracionesIlustración 1: Hoja de Cálculo de Excel.............................................................................................1Ilustración 2: Ventana de Microsoft Excel 2010................................................................................2Ilustración 3: Edición de celdas, en la barra de fórmulas de Excel.....................................................5Ilustración 4: Autorrelleno en Excel 2010..........................................................................................6Ilustración 5: Comando rellenar.........................................................................................................7Ilustración 6: Cuadro de listas personalizadas....................................................................................9Ilustración 7: Cuadro de listas personalizadas..................................................................................10Ilustración 8: Cifrar un archivo de Excel...........................................................................................12Ilustración 9: Protección de libro de Excel.......................................................................................15Ilustración 10; Comando Proteger hoja, de la ventana de Excel......................................................17Ilustración 11: Contraseña de Protección de Hoja de Cálculo en Excel............................................18Ilustración 12: Ordenar una tabla de Excel.......................................................................................19Ilustración 13: Ordenar datos en Excel.............................................................................................20Ilustración 14: Filtrar datos en una tabla de Excel............................................................................21Ilustración 15: Filtros de fechas........................................................................................................22Ilustración 16: Filtro de números.....................................................................................................23Ilustración 17: Quitar filtro de una tabla..........................................................................................24Ilustración 18: Comando: Formato Condicional de Excel.................................................................25Ilustración 19: Ejemplo de formato condicional en Excel.................................................................26Ilustración 20: Quitar formato condicional......................................................................................27Ilustración 21: Mensaje de advertencia tras aplicación de validación de datos...............................28Ilustración 22: Comando validación de datos...................................................................................29Ilustración 23: Omitir blancos..........................................................................................................30Ilustración 24: Lista de validación de datos de otra hoja.................................................................34Ilustración 25: Personalizar mensajes de error................................................................................34Ilustración 26: Eliminar validación de datos.....................................................................................36Ilustración 27: Operadores Aritméticos de Excel..............................................................................39Ilustración 28: Operadores de Comparación de Excel......................................................................40Ilustración 29: Operadores de texto de Excel...................................................................................40Ilustración 30: Operadores de referencia de Excel...........................................................................40Ilustración 31: Precedencia de operadores en Excel........................................................................41Ilustración 32: Control de operadores de precedencia, mediante uso de paréntesis......................41Ilustración 33: Tipos de errores en Excel..........................................................................................42Ilustración 34: Parte de una fórmula de Excel..................................................................................44Ilustración 35: Referencias en Excel.................................................................................................46Ilustración 36: Sintaxis de las funciones de Excel.............................................................................47Ilustración 37: Funciones básicas de Excel.......................................................................................47Ilustración 38: Organización de datos previo a utilizar la función BUSCARV....................................50Ilustración 39: Argumentos de la función BUSCARV........................................................................51Ilustración 40: Diferencias para la aplicación de la función BUSCARH y BUSCARV...........................52Ilustración 41: Argumentos de la función BUSCARV........................................................................52Ilustración 42: Argumentos de la función SI.....................................................................................54Ilustración 43: ejemplo de la función SI como prueba lógica...........................................................56

Page 4: Unidad 5 excel

Ilustración 44: Ejemplo 1 de función SI anidada...............................................................................57Ilustración 45: Ejemplo 2 de función SI anidada..............................................................................58Ilustración 46: Argumento 1 de la función Y....................................................................................59Ilustración 47: Argumento 2 de la función Y....................................................................................59Ilustración 48: Argumento 3 de la función Y....................................................................................60Ilustración 49: Comportamiento de la función Y..............................................................................60Ilustración 50: Argumentos de la función O.....................................................................................61Ilustración 51: Funciones como argumentos de la función O - Verdadero.......................................62Ilustración 52: Funciones como argumentos de la función O - Falso...............................................62Ilustración 53: Elementos del gráfico estadístico.............................................................................64Ilustración 54: Paso 1 en la creación de gráficos estadísticos en Excel............................................64Ilustración 55: Paso 2 en la creación de gráficos estadísticos en Excel............................................65Ilustración 56: Gráfico estadístico circular.......................................................................................65Ilustración 57: Diseños de gráficos estadísticos...............................................................................65Ilustración 58: Comando Columna para gráficos estadísticos..........................................................66Ilustración 59: Gráfico estadístico de barras en 3D..........................................................................66Ilustración 60: Gráfico combinado que utiliza uno de columnas y uno de filas................................67Ilustración 61: Paso 1 en la creación de gráficos dinámicos.............................................................69Ilustración 62: Paso 2 en la creación de gráficos dinámico..............................................................69Ilustración 63: Paso 3 en la creación de gráficos dinámicos.............................................................69

Page 5: Unidad 5 excel

1

UNIDAD 5 – MS OFFICE EXCEL 2010 INTRODUCCIÓNRECONOCIMIENTO DEL AMBIENTE DE TRABAJO¿Qué es y para qué sirve Excel?

Excel es un programa del tipo Hoja de Cálculo que permite realizar operaciones con números organizados en una cuadrícula. Es útil para realizar desde simples sumas hasta cálculos de préstamos hipotecarios y otros mucho más complejos.

Excel es una hoja de cálculo integrada en Microsoft Office. Esto quiere decir que si ya conoces otro programa de Office, como Word, Access o PowerPoint… te resultará familiar utilizar Excel, puesto que muchos iconos y comandos funcionan de forma similar en todos los programas de Office.

¿Qué es una hoja de cálculo de Excel?Una hoja de cálculo es un programa que es capaz de trabajar con números de forma sencilla e intuitiva. Para ello se utiliza una cuadrícula donde, en cada celda de la cuadrícula, se pueden introducir números, letras y gráficos.

Ilustración 1: Hoja de Cálculo de Excel.

Por ejemplo, para sumar una serie de números sólo tienes que introducirlos uno debajo de otro, como harías en un papel, colocarte en la celda donde irá el resultado y decirle a Excel que quieres hacer la suma de lo que tienes encima (ya veremos más adelante lo sencillo que es hacerlo).

Pensarás que para una simple suma mejor usamos una calculadora que andar abriendo Excel e introducir los datos y fórmulas pero piensa qué ocurre si te equivocas al introducir un número en una suma de 50 números, tienes que volver a introducirlos todos mientras que en Excel no importa si te equivocas al introducir un dato, simplemente corriges el dato y automáticamente Excel vuelve a calcularlo todo. (“¿Qué es y para qué sirve Excel?”, 2012)

Elementos de la ventana de Microsoft Excel 2010:

Los elementos de la ventana de la aplicación Microsoft Excel 2010 son los siguientes:

Page 6: Unidad 5 excel

2

Ilustración 2: Ventana de Microsoft Excel 2010.

1. Menú de Control o Menú Ventana. Permite cerrar la ventana, cambiarla de tamaño y pasar a otra ventana.

2. Barra de Títulos. Indica el nombre del programa (Excel) y el libro con el que se está trabajando.

3. Botón minimizar. Reduce la ventana a un botón que aparece en la Barra de tareas del escritorio de Windows. Para convertir una ventana de aplicación en un botón hacer clic en el botón Minimizar.

4. Botón maximizar/restaurar. Amplía la ventana al tamaño máximo o la restaura a su tamaño original.

5. Barra de herramientas de acceso rápido. Por defecto, tenemos disponibles en ella los comandos Guardar, Deshacer y Rehacer, aunque se puede personalizar para añadir a ella los que deseemos.

6. Menú Archivo. En él podemos encontrar la mayoría de comandos para el trabajo con documentos (libros de cálculo), como abrir, guardar, imprimir, obtener una vista preliminar, etc. Al hacer clic sobre él se despliega un menú con todas sus opciones y sub-menús.

7. Fichas. Las fichas contienen botones y controles agrupados en categorías. La estructuración en fichas es la conjunción de dos formas de acceso a las herramientas que hasta la versión 2003 de Office eran independientes: los menús y las barras de herramientas.

8. Grupo de herramientas. Las herramientas de las fichas están organizadas en grupos de herramientas formando una cierta unidad lógica. En muchas ocasiones, en la esquina inferior derecha del grupo de herramientas encontramos una flechita que da acceso al cuadro de diálogo asociado a esa categoría de herramientas.

Page 7: Unidad 5 excel

3

9. Cinta de opciones. Es el espacio en el que se despliegan los botones, menús y controles de cada una de las fichas. Para aumentar el espacio destinado a la hoja de cálculo, puede minimizarse haciendo clic con el botón derecho sobre cualquiera de los nombres de las fichas y activando la casilla de verificación Minimizar cinta de opciones.

10. Minimizar cinta de opciones. Pulse este botón para ocultar y mostrar la cinta de opciones.

11. Ayuda. Al hacer clic sobre este botón se activa la ayuda de Microsoft Excel.12. Barra de fórmulas. En ella se introduce y edita la información en las hojas de

Excel. Está situada bajo la cinta de opciones y, si esta se encuentra minimizada, bajo los nombres de las fichas. Para visualizarla u ocultarla, se activa o desactiva la casilla Barra de fórmulas en la ficha Vista (grupo de herramientas Mostrar). Los diferentes elementos de la barra de fórmulas son:

o En el primer recuadro es el Cuadro de nombres, muestra la dirección de la celda activa.

o Botones de Cancelar e Introducir de la entrada actual (aparecerán sólo al editar el contenido de una celda).

o El botón Insertar Función, para abrir el cuadro de diálogo Insertar función.

o Área de edición, donde se introduce y edita la información de la celda activa.

11. Encabezados de las columnas (A-XFD). Permiten la selección de la(s) columna(s).12. Encabezados de las filas (1-1.048.576). Permite la selección de la(s) fila(s).13. Botón de selección de la hoja completa. Al hacer clic sobre dicho botón

seleccionará toda la hoja de cálculo.14. Botón de división vertical/horizontal de la pantalla. Divide la ventana de la hoja

de cálculo en varias ventanas, pudiendo con ello ver diferentes partes de una hoja.15. Barras, flechas y botones de desplazamiento. Permiten desplazarse por la hoja.16. Área de trabajo. Es el espacio interior de la ventana, donde el usuario realizará su

trabajo con las hojas.17. Etiquetas de la hoja. Muestra los nombres de las distintas hojas de un libro de

trabajo y los botones para desplazarse por las hojas.18. Barra de estado. Situada en la parte inferior de la pantalla, en su configuración por

defecto nos mostrará información sobre: o El indicador de modo para señalar qué está haciendo Excel.o En el recuadro central mostrará el resultado de las operaciones

Promedio, Cuenta y Suma.o En la parte derecha, información sobre el tipo de visualización activo

(Normal, Diseño de página, etc.).o En la esquina derecha, el control de zoom, que permite ajustar la escala

de visualización del documento.

Haciendo clic con el botón derecho sobre la barra de estado se activa el menú contextual para configurarla. (“1.2. Entorno de trabajo de Excel”, 2014)

Page 8: Unidad 5 excel

4

Aquí podremos encontrar un video sobre el ambiente de trabajo de Excel: https://www.youtube.com/watch?v=rpspj09I9tM

Page 9: Unidad 5 excel

5

EDICIÓN DE CELDASEs posible que después de haber ingresado todos tus datos a una hoja de Excel te des cuenta de que existen algunos errores en las celdas por lo que será necesario realizar alguna modificación y edición de su contenido.

Para editar las celdas podemos utilizar varios métodos.

Reemplazo total del contenido

Este método sugiere reemplazar todo el texto de una celda por uno nuevo. Para realizar es suficiente con posicionarse sobre la celda que será editara e introducir el nuevo texto lo cual reemplazará por completo el contenido anterior.

Modo de edición de una celda

Si solamente necesitas modificar alguna letra o símbolo del texto de una celda y no deseas hacer un reemplazo total, entonces puedes entrar al modo de edición haciendo doble clic sobre la celda a modificar. De inmediato se mostrará un cursor intermitente que te permitirá editar el contenido de la celda sin necesidad de eliminarlo todo.

Otra manera de entrar al modo de edición de una celda es seleccionándola y pulsando la tecla F2.

Editar una celda desde la barra de fórmulas

Una tercera opción para editar el contenido de una celda es a través de la barra de fórmulas. Solo necesitas seleccionar la celda a modificar y hacer las correcciones directamente en la barra de fórmulas.

Ilustración 3: Edición de celdas, en la barra de fórmulas de Excel.

Si por alguna razón cometes alguna equivocación al estar editando las celdas puedes hacer uso del comando Deshacer que se encuentra en la barra de herramientas de acceso rápido. (Ortíz, 2011)

Aquí podrás encontrar un video sobre Edición de Celdas:

https://www.youtube.com/watch?v=d38JUwTXPcc

Page 10: Unidad 5 excel

6

OPCIONES DE AUTORRELLENO

Este tipo de funciones especiales permiten que puedas introducir los datos de la manera más sencilla y facilitando al máximo el trabajo de rellenar datos.

Ilustración 4: Autorrelleno en Excel 2010.

Activar o desactivar la finalización automática de valores de celda

Si no desea que Excel complete automáticamente los valores de celda, puede desactivar esta característica.

1. Haga clic en la pestaña Archivo y seleccione Opciones.2. Para activar o desactivar la finalización automática de valores de celda, haga clic en

Avanzadas y, bajo Opciones de edición, active o desactive la casilla Habilitar Autocompletar para valores de celda.

Rellenar datos en celdas adyacentes con el controlador de relleno

Para rellenar rápidamente varios tipos de series de datos, puede seleccionar las celdas y arrastrar el controlador de relleno . Para usar el controlador de relleno, seleccione las celdas que desee usar como base para rellenar celdas adicionales y arrastre el controlador de relleno en sentido vertical u horizontal para rellenar las celdas que desee.

El controlador de relleno se muestra de forma predeterminada, pero lo puede ocultar, o mostrar si está oculto.

Page 11: Unidad 5 excel

7

Después de arrastrar el controlador de relleno, se muestra el botón Opciones de autorrelleno. Si no desea que se muestre el botón Opciones de autorrelleno cada vez que arrastre el controlador de relleno, puede desactivarlo. Del mismo modo, si el botón Opciones de autorrelleno no aparece al arrastrar el controlador, puede activarlo.

Rellenar datos en celdas adyacentes mediante el comando Rellenar

Puede usar el comando Rellenar para rellenar un rango seleccionado o la celda activa con el contenido de un rango o celda adyacente.

1. Siga uno de estos procedimientos:o Para rellenar la celda activa con el contenido de una celda adyacente,

seleccione una celda vacía situada justo debajo, a la derecha, encima o a la izquierda de la celda que contiene los datos con los que desea rellenar la celda vacía.

o Para rellenar varias celdas adyacentes, seleccione la celda con el contenido que desea usar y las celdas adyacentes en las que desea insertarlo.

2. En la ficha Inicio, en el grupo Modificar, haga clic en Rellenar y en la opción Hacia abajo, Hacia la derecha, Hacia arriba o Hacia la izquierda que desee.

Ilustración 5: Comando rellenar.

Método abreviado de teclado    Si desea rellenar rápidamente una celda con el contenido de una celda adyacente, presione Ctrl+J para rellenar con el contenido de la celda superior o CTRL+D para rellenar con el de la celda de la izquierda.

Rellenar celdas adyacentes con fórmulas

1. Seleccione la celda que contiene la fórmula con la que desea rellenar las celdas adyacentes.

2. Arrastre el controlador de relleno por las celdas que quiere rellenar.3. Para elegir la forma de rellenar la selección, haga clic en Opciones de autorrelleno

y en la opción que desea usar.4. Haga clic en la pestaña Archivo.5. En Excel, haga clic en Opciones y en la categoría Fórmulas.6. En Opciones de cálculo, busque en Cálculo de libro.

Automático    Vuelve a calcular las fórmulas automáticamente. Automático excepto para tablas de datos    Vuelve a calcular las fórmulas,

excepto si se encuentran en una tabla de datos.

Page 12: Unidad 5 excel

8

Manual    No vuelve a calcular las fórmulas automáticamente. Volver a calcular libro antes de guardarlo    Esta opción solo está disponible si

establece Cálculo de libro en Manual. Si esta casilla está activada, las fórmulas no se vuelven a calcular automáticamente hasta que guarde el libro. Tenga en cuenta que hay muchas otras acciones que pueden hacer que el libro se guarde, por ejemplo si usa el comando Enviar a. (“Rellenar datos automáticamente en celdas de hojas de cálculo - Excel”, s/f)

Aquí podrás encontrar un video de Autorrelleno en Excel 2010: https://www.youtube.com/watch?v=0cpB1tT8Vsk

Page 13: Unidad 5 excel

9

LISTAS PERSONALIZADASPuede usar una lista personalizada para ordenar o de relleno en un orden definidas por el usuario. Excel proporciona integrado de día de la semana y mes del año listas, pero también puede crear su propia lista personalizada.

Crear una lista personalizada

Existen dos maneras de crear una lista personalizada. Si es corta, puede escribir los valores directamente en el cuadro de diálogo. Si es larga, puede importarla desde un rango de celdas.

Crear una lista personalizada escribiendo en valores   

1. Para Excel 2010 y versiones posteriores, haga clic en archivo > Opciones > Avanzadas > General > Modificar listas personalizadas.

2. En Excel 2007, haga clic en el Botón de Microsoft Office > Opciones de Excel > Popular >Opciones principales para trabajar con Excel > Modificar listas personalizadas.

3. En el cuadro Listas personalizadas, haga clic en NUEVA LISTA y, a continuación, escriba las entradas en el cuadro Entradas de lista, empezando desde la primera.

Presione ENTRAR después de cada entrada.

4. Cuando la lista esté completa, haga clic en Agregar.

Ilustración 6: Cuadro de listas personalizadas.

Page 14: Unidad 5 excel

10

Los elementos de la lista que ha seleccionado se agregan al cuadro Listas personalizadas.

5. Haga dos veces clic en Aceptar.

Crear una lista personalizada de un rango de celdas   

1. En un rango de celdas, escriba los valores que desea ordenar o rellenar, en el orden que desee, de arriba a abajo. Seleccione el rango de celdas que acaba de escribir y siga las instrucciones anteriores para mostrar el cuadro de diálogo Modificar listas personalizadas.

2. En el cuadro de diálogo Listas personalizadas, compruebe que la referencia de celda de la lista de elementos que ha seleccionado aparece en el cuadro Importar lista desde las celdas y, a continuación, haga clic en Importar.

3. Los elementos de la lista que ha seleccionado se agregan al cuadro Listas personalizadas.

Ilustración 7: Cuadro de listas personalizadas.

4. Haga clic dos veces en Aceptar.

Cómo se almacenan las listas personalizadas

Una vez que se ha creado una lista personalizada, ésta se agrega al registro del equipo, con lo que se puede utilizar en otros libros. Si utiliza una lista personalizada cuando ordena datos, también se guarda con el libro, así que se puede usar en otros equipos, incluidos los servidores en los que pueda publicarse el libro en Excel Services y en los que desee depender de la lista personalizada para ordenar.

Page 15: Unidad 5 excel

11

No obstante, si abre el libro en otro equipo o servidor, no verá la lista personalizada almacenada en el archivo del libro en el cuadro de diálogo Listas personalizadas disponible en Opciones de Excel; sólo la verá en la columna Orden del cuadro de diálogo Ordenar. La lista personalizada almacenada en el archivo del libro tampoco está disponible en el comando Rellenar.

Si lo desea, puede agregar la lista personalizada almacenada en el archivo del libro al registro del otro equipo o servidor para que esté disponible en el cuadro de diálogo Listas personalizadas de Opciones de Excel. En el cuadro de diálogo Ordenar, en la columna Orden, seleccione Listas personalizadas para abrir el cuadro de diálogo Listas personalizadas, seleccione la lista personalizada y, a continuación, haga clic en Agregar. (“Crear o eliminar una lista personalizada para ordenar y rellenar datos - Excel”, s/f)

Page 16: Unidad 5 excel

12

ADMINISTRACIÓN DE LA HOJASEGURIDAD DE DATOSExcel le ofrece la capacidad para proteger su trabajo, ya sea para evitar que alguien abre un libro sin una contraseña, conceder acceso de solo lectura a un libro o incluso proteger una hoja de cálculo para que no eliminar accidentalmente las fórmulas. En este tema se explica las distintas maneras en que puede utilizar principal opciones para proteger y distribuir los archivos de Excel.

Advertencia: 

Si olvida o pierde la contraseña, Microsoft no puede recuperar por usted. No debe asumir que simplemente porque proteger un libro o una hoja de cálculo con

una contraseña que es seguro: siempre debe considerar dos veces antes de distribuir libros de Excel que podrían contener información personal confidencial, como la identificación del empleado de números, el número de la seguridad Social de tarjeta de crédito, entre otros.

Cifrar un archivo de Excel

Cuando se cifra un archivo de Excel, bloquea con una contraseña. Una vez cifrar un archivo de Excel, nadie más podrá abrirlo. Esta es la técnica más comunes y recomendada para bloquear un archivo de Excel.

Siga estos pasos para cifrar un archivo de Excel:

1. Haga clic en archivo > información > Proteger libro > cifrar con contraseña.2. Escriba una contraseña y haga clic en Aceptar.

Ilustración 8: Cifrar un archivo de Excel.

3. En el cuadro de diálogo Confirmar contraseña , vuelva a escribir la contraseña que escribió en el paso anterior.

Page 17: Unidad 5 excel

13

Cuando usted u otro usuario intenta abrir el archivo, aparecerá la pantalla siguiente:

Establecer una contraseña para permitir que lea o modifique

Puede establecer dos contraseñas en el archivo: uno para abrir el archivo como de sólo lectura y otro para modificar. A continuación, puede compartir las contraseñas correspondientes con los usuarios según el nivel de acceso que se deben tener.

Acceso de solo lectura no impide que un usuario realice cambios en un archivo, pero impedir que les guardar los cambios a menos que el nombre del archivo y guárdelo como un número distinto del original.

Para los usuarios que necesitan poder modificar el archivo, tendrá que comparten ambos abrir y modificar las contraseñas.

Siga estos pasos para establecer una contraseña para abrir o modificar un archivo de Excel:

1. En el archivo de Excel, haga clic en archivo > Guardar como.2. Haga clic en una ubicación, como el equipo o la página de Mi sitio web.3. Haga clic en una carpeta, como documentos o en una de las carpetas de OneDrive,

o haga clic en Examinar.4. En el cuadro de diálogo Guardar como , vaya a la carpeta que desea usar, a

continuación, haga clic en Herramientas y, a continuación, haga clic en Opciones generales.

5. Puede especificar una o ambas contraseñas aquí, uno para abrir el archivo y otra para modificar el archivo, dependiendo de los requisitos.

Page 18: Unidad 5 excel

14

6. Haga clic en Guardar.

Cuando alguien intenta abrir este archivo, Excel le pide una contraseña.

Una segunda pantalla muestra si hay una contraseña para modificar el archivo. Los usuarios que no tengan la contraseña de modificación pueden haga clic en Sólo lectura y comenzar a ver el contenido del archivo.

Proteger un libroSe aplica a: Excel 2016 Excel 2013 Excel 2010 Excel 2007

Para impedir que otros usuarios puedan ver hojas de cálculo ocultas, agregar, mover, eliminar u ocultar hojas de cálculo o cambiarles el nombre, puede proteger la estructura de un libro de Excel con una contraseña.

Ejemplo de estructura de un libro

Page 19: Unidad 5 excel

15

Notas: Proteger un libro no es lo mismo que proteger una hoja de cálculo o un archivo de Excel con una contraseña. Para más información, vea lo siguiente:

Para bloquear el archivo de modo que otros usuarios no pueden abrirlo, vea Proteger un archivo de Excel.

Para proteger de otros usuarios determinadas áreas de los datos en la hoja de cálculo, debe proteger la hoja de cálculo. Para más información, vea Proteger una hoja de cálculo.

Para saber la diferencia entre proteger el archivo, un libro o una hoja de cálculo de Excel, vea Protección y seguridad en Excel.

Ejemplo de la estructura del libro

Para proteger la estructura del libro, siga estos pasos:

1. Haga clic en Revisar > Cambios > Proteger libro.

Ilustración 9: Protección de libro de Excel.

Nota: Si usa Excel 2010 y versiones anteriores, también puede seleccionar la opción Ventanas si quiere impedir que los usuarios puedan mover, cambiar el tamaño o cerrar la ventana del libro, o mostrar u ocultar ventanas. La opción Ventanas no está disponible en Excel 2013 ni en versiones posteriores.

2. Escriba una contraseña en el cuadro Contraseña.3. Haga clic en Aceptar y vuelva a escribir la contraseña para confirmarla.

Page 20: Unidad 5 excel

16

Proteger una hoja de cálculoSe aplica a: Excel 2016 Excel 2013 Excel 2010 Excel 2007

Para evitar que, accidental o deliberadamente, otros usuarios cambien, muevan o eliminen datos de una hoja de cálculo, puede bloquear las celdas de la hoja de cálculo de Excel y luego proteger la hoja con una contraseña. Pongamos que usted es propietario de la hoja de cálculo del informe de estado del grupo, en la que desea que los miembros del equipo solo agreguen datos en celdas específicas y no puedan modificar nada más. Con la protección de la hoja de cálculo, puede hacer que solo determinadas partes de la hoja puedan editarse y que los usuarios no puedan modificar los datos de cualquier otra región de la hoja.

Importante: Proteger una hoja de cálculo no es lo mismo que proteger un libro o un archivo de Excel con una contraseña. Para más información, vea lo siguiente:

Para bloquear el archivo de modo que otros usuarios no pueden abrirlo, vea Proteger un archivo de Excel.

Para impedir que los usuarios agreguen, modifiquen, muevan, copien u oculten y muestren hojas en un libro, vea Proteger un libro.

Para saber la diferencia entre proteger la hoja de cálculo, el libro o el archivo de Excel, vea Protección y seguridad en Excel.

Elegir los elementos de celda que quiere bloquear

Vea lo que puede bloquear en una hoja desprotegida:

Fórmulas: Si no desea que otros usuarios vean sus fórmulas, puede ocultarlas para que no se vean en las celdas o la barra de fórmulas. Para más información, vea Ocultar y proteger fórmulas.

Rangos: Puede permitir que los usuarios trabajen en rangos concretos dentro de una hoja protegida. Para más información, vea Bloquear o desbloquear áreas específicas de una hoja de cálculo protegida.

Nota: Los controles ActiveX, los controles de formulario, las formas, los gráficos, los gráficos SmartArt, los minigráficos, las segmentaciones de datos, las escalas de tiempo, entre otros, ya están bloqueados cuando se agregan a una hoja de cálculo. Pero el bloqueo solo funcionará cuando se habilite la protección de la hoja. Vea las secciones siguientes para más información sobre cómo habilitar la protección de la hoja.

Habilitar la protección de hojas de cálculo

La protección de hojas de cálculo es un proceso de dos pasos: el primer paso consiste en desbloquear las celdas que otros usuarios pueden editar y entonces puede proteger la hoja de cálculo con o sin contraseña.

Paso 1: Desbloquear las celdas que tienen que ser editables

Page 21: Unidad 5 excel

17

1. En el archivo de Excel, seleccione la pestaña de la hoja de cálculo que quiere proteger.

2. Seleccione las celdas que otros usuarios pueden editar.

Sugerencia: Para seleccionar varias celdas no contiguas, presione Ctrl+clic izquierdo.

3. Haga clic con el botón derecho en cualquier lugar de la hoja y seleccione Formato de celdas (o use Ctrl+1 o Comando+1 en el equipo Mac). Luego vaya a la pestaña Protección y desactive Bloqueada.

Paso 2: Proteger la hoja de cálculo 

Después, seleccione las acciones que los usuarios deben poder realizar en la hoja, como, entre otras, insertar o eliminar columnas o filas, editar objetos, ordenar o usar el filtro automático. Además, también puede especificar una contraseña para bloquear la hoja de cálculo. Una contraseña impide que otras personas quiten la protección de la hoja de cálculo: es necesario escribirla para desproteger la hoja.

Vea abajo los pasos necesarios para proteger la hoja.

1. En la pestaña Revisar, haga clic en Proteger hoja.

Ilustración 10; Comando Proteger hoja, de la ventana de Excel.

2. En la lista Permitir a los usuarios de esta hoja de cálculo, seleccione los elementos que quiera que los usuarios puedan cambiar.

Page 22: Unidad 5 excel

18

Ilustración 11: Contraseña de Protección de Hoja de Cálculo en Excel.

3. Si lo desea, escriba una contraseña en el cuadro Contraseña para desproteger la hoja: y haga clic en Aceptar. Vuelva a escribir la contraseña en el cuadro de diálogo Confirmar contraseña y haga clic en Aceptar.

(“Proteger una hoja de cálculo - Excel”, s/f)

Page 23: Unidad 5 excel

19

MANEJO DE FILTROS Y ORDENACIÓN DE DATOS

Cada encabezado de columna de una tabla en Excel tiene un botón de flecha en su extremo derecho el cual es conocido como el botón de filtro y cuando hacemos clic sobre él se muestran diferentes opciones para ordenar y filtrar datos en Excel.

Ordenar una tabla en Excel

Al pulsar el botón de filtro en el encabezado de una columna veremos algunas opciones de ordenación de datos. Al aplicar un criterio de ordenación a una tabla de Excel las filas se reorganizarán de acuerdo al contenido de la columna por la cual estamos ordenando. Si la columna contiene datos de tipo texto, entonces podremos ordenar los valores de A a Z (alfabéticamente) o de Z a A.

Ilustración 12: Ordenar una tabla de Excel.

Por el contrario, si la columna contiene datos numéricos, entonces podremos ordenar de menor a mayor o también podremos hacerlo de mayor a menor. En el caso de que tengamos fechas podremos ordenar de más antiguo a más reciente o viceversa.

Page 24: Unidad 5 excel

20

Otra opción de ordenación que nos da Excel es la de ordenar por color y eso hará que las celdas se ordenen de acuerdo al color de fondo de las celdas. Esta opción es relevante solamente cuando hemos aplicado algún formato condicional a las celdas para modificar su color de fondo o el color de la fuente.

Múltiples criterios de ordenación

Es posible ordenar una tabla indicando diferentes criterios de ordenación. Por ejemplo, es posible ordenar los datos primero por fecha, después por país y posteriormente por región. Para lograr este tipo de ordenación haremos uso del comando Inicio > Ordenar y filtrar > Orden personalizado lo cual mostrará el siguiente cuadro de diálogo:

Ilustración 13: Ordenar datos en Excel.

La parte superior del cuadro de diálogo Ordenar contiene botones que nos permiten establecer múltiples niveles de ordenación. A continuación explico cada uno de esos botones:

Agregar nivel: Nos permite agregar un nuevo nivel de ordenación. Eliminar nivel: Si ya no deseamos que se aplique un criterio de ordenación

podemos eliminarlo de la lista. Copiar nivel: Hace una copia del nivel seleccionado. Flechas arriba y abajo: Nos permiten mover los niveles de ordenación hacia arriba

o hacia abajo para establecer un orden preferido. Opciones: Con este botón podemos indicar si la ordenación de datos va a distinguir

entre mayúsculas y minúsculas.

La ordenación de los datos se hará comenzando por el nivel superior y hacia abajo tomando en cuenta tanto la columna como el criterio de ordenación especificado en cada nivel.

Filtrar datos en una tabla de Excel

Cuando hablamos de filtrar datos en una tabla de Excel nos referimos a desplegar solamente aquellas filas que cumplen con los criterios especificados. Los datos de la tabla

Page 25: Unidad 5 excel

21

no son eliminados ni alterados sino que solamente se ocultan a la vista. Las opciones de filtrado se muestran también al pulsar el botón de flecha que se encuentra al lado de los encabezados de columna.

Ilustración 14: Filtrar datos en una tabla de Excel.

En la imagen anterior observamos las opciones de filtrado que Excel provee cuando la columna tiene datos de tipo texto. La opción Filtros de texto muestra varias opciones a elegir como Es igual a, Comienza por, Contiene, etc. Cualquiera de estas opciones mostrará el cuadro de diálogo Autofiltro personalizado que nos permitirá indicar el detalle del filtro que deseamos aplicar.

Page 26: Unidad 5 excel

22

En este cuadro de diálogo podemos especificar dos condiciones para una misma columna y elegir si deseamos que se cumplan ambas o solamente una de ellas. Al hacer clic en el botón Aceptar se aplicará el filtro a los datos.

En la parte inferior del menú de filtrado observamos una lista de los valores únicos de la columna y cada opción tiene a su lado una caja de selección que podemos marcar o desmarcar indicando si deseamos ver u ocultar aquellas filas que contienen dicho valor. Si la lista tiene tantos valores únicos que se nos dificulta encontrar la opción que necesitamos, entonces podemos utilizar el cuadro de búsqueda de manera que podamos encontrar los valores adecuados y seleccionarlos.

Filtros de números y fecha

En la sección anterior revisamos los Filtros de texto que provee Excel, pero las opciones que se muestran cuando el tipo de datos es un número o una fecha serán diferentes. Por ejemplo, en la siguiente imagen puedes observar las opciones de Filtros de fecha.

Ilustración 15: Filtros de fechas.

Excel reconoce el tipo de dato contenido en una columna y muestra los criterios de filtrado correspondientes. En la siguiente imagen puedes observar las opciones de Filtros de número:

Page 27: Unidad 5 excel

23

Ilustración 16: Filtro de números.

Algunas de las opciones de filtros mostrarán el cuadro de diálogo Autofiltro personalizado para permitirnos indicar el detalle del criterio de filtrado a aplicar, pero otras opciones como el filtro de fecha Hoy se aplicará de inmediato sobre los datos. Lo mismo sucederá con el filtro de número Diez mejores que no necesita de ningún parámetro adicional y se aplicará de inmediato sobre la columna.

Quitar el filtro de una tabla

Una vez que hemos aplicado un filtro en una columna, el icono del botón de filtro cambiará su apariencia para indicarnos que dicha columna tiene un filtro aplicado. Para quitar un filtro de una tabla de Excel tenemos dos opciones:

Hacer clic sobre el botón de filtro y seleccionar la opción Borrar filtro de y se eliminará el filtro de dicha columna.

Si queremos borrar con un solo clic todos los filtros aplicados a una tabla entonces debemos ir a Inicio > Modificar > Ordenar y filtrar > Borrar.

Page 28: Unidad 5 excel

24

Ilustración 17: Quitar filtro de una tabla.

Una vez eliminado, la apariencia del botón de filtro se volverá a mostrar como una flecha hacia abajo. (Ortíz, 2013d)

Aquí podrá encontrar un video sobre ordenar y filtrar datos en Excel 2010:

https://www.youtube.com/watch?v=O0y-aNygvgo

Page 29: Unidad 5 excel

25

FORMATO CONDICIONALEl formato condicional es una herramienta útil para identificar patrones o tendencias en una hoja de cálculo. Por ejemplo, una regla podría ser Si el valor es mayor que 5.000, que la celda sea amarilla. Así, podrás ver de un vistazo las celdas cuyo valor es superior a 5.000.

Crear reglas condicionalesPaso 1: Selecciona las celdas a las que quieres agregar el formato.  

Paso 2: En la ficha Inicio, haz clic en el comando Formato condicional. Un menú desplegable aparecerá.

Paso 3: Selecciona Resaltar reglas de celdas o Reglas superiores e inferiores. Aquí, vamos a elegir la primera opción. Verás un menú con varias reglas.   

Paso 4: Selecciona la regla que quieras (Mayor que, por ejemplo).

Paso 5: En el cuadro de diálogo, introduce un valor en el espacio correspondiente. En este ejemplo, queremos dar formato a las celdas cuyo valor es superior a $ 3.500.000, así que vamos ingresar ese número. Si quieres, hacer clic en una celda en lugar de digitar un número.  

Paso 6: Selecciona un estilo de formato en el menú desplegable. Verás que éste se aplica a las celdas que seleccionaste.   

Ilustración 18: Comando: Formato Condicional de Excel.

Page 30: Unidad 5 excel

26

Ilustración 19: Ejemplo de formato condicional en Excel.

Formatos condicionales predeterminados Excel tiene una serie de predeterminados que se pueden utilizar para aplicar formatos condicionales a las celdas. Se agrupan en tres categorías: barras de datos, escalas de color y conjuntos de íconos. A continuación, fíjate en qué consiste cada uno.

Categorías de formatos condicionales predeterminadosBarras de datos

Las barras de datos son barras horizontales a que aparecen de izquierda a derecha en cada celda, como un gráfico de barras.

Escalas de color

Cambian el color de cada celda en función de su valor. Cada escala de color utiliza un gradiente de dos colores o tres.

Conjuntos de Iconos

Añaden un icono específico para cada celda en función de su valor.

Utiliza los formatos condicionales predeterminados Paso 1: Selecciona las celdas a las que quieres agregar el formato.  

Paso 2: En la ficha Inicio, haz clic en el comando Formato condicional. Verás un menú desplegable.  

Paso 3: Selecciona Barras de datos, Escalas de color o Conjuntos de iconos (barras de datos, por ejemplo). A continuación, selecciona el predeterminado que quieras.

Page 31: Unidad 5 excel

27

Quitar las reglas de formato condicional Los formatos condicionales predefinidos son una herramienta muy útil para ver los patrones o tendencias en una hoja de cálculo.

Quitar las reglas de formato condicional es igual o mucho más sencillo que ponerlas. Veamos cuáles son los pasos que debes serguir para hacerlo.

Pasos para quitar las reglas de formato condicional   Paso 1: Selecciona las celdas que tienen formato condicional.   

Paso 2: En la ficha Inicio, haz clic en el comando Formato condicional. 

Paso 3: Selecciona la opción Borrar reglas del menú desplegable.    

Paso 4: Aparecerá un menú que te muestra varias opciones. En este ejemplo, vamos a elegir la opción Borrar reglas de toda la hoja.

(“Microsoft Excel 2010 - Formatos condicionales predeterminados”, s/f)

Aquí podemos encontrar un video sobre formatos condicionales de Excel: https://www.youtube.com/watch?v=TkKMUkC6ZDo&feature=youtu.be

Ilustración 20: Quitar formato condicional.

Page 32: Unidad 5 excel

28

VALIDACIÓN DE DATOS

La validación de datos en Excel es una herramienta que no puede pasar desapercibida por los analistas de datos ya que nos ayudará a evitar la introducción de datos incorrectos en la hoja de cálculo de manera que podamos mantener la integridad de la información en nuestra base de datos.

Importancia de la validación de datos en Excel

De manera predeterminada, las celdas de nuestra hoja están listas para recibir cualquier tipo de dato, ya sea un texto, un número, una fecha o una hora. Sin embargo, los cálculos de nuestras fórmulas dependerán de los datos contenidos en las celdas por lo que es importante asegurarnos que el usuario ingrese el tipo de dato correcto.

Por ejemplo, en la siguiente imagen puedes observar que la celda C5 muestra un error en el cálculo de la edad ya que el dato de la celda B5 no corresponde a una fecha válida.

Este tipo de error puede ser prevenido si utilizamos la validación de datos en Excel al indicar que la celda B5 solo aceptará fechas válidas. Una vez creada la validación de datos, al momento de intentar ingresar una cadena de texto, obtendremos un mensaje de advertencia como el siguiente:

Ilustración 21: Mensaje de advertencia tras aplicación de validación de datos.

Page 33: Unidad 5 excel

29

Más adelante veremos que es factible personalizar los mensajes enviados al usuario de manera que podamos darle una idea clara del problema, pero este pequeño ejemplo nos muestra la importancia de la validación de datos en Excel al momento de solicitar el ingreso de datos de parte del usuario.

El comando Validación de datos en Excel

El comando Validación de datos que utilizaremos a lo largo de este artículo se encuentra en la ficha Datos y dentro del grupo Herramientas de datos.

Ilustración 22: Comando validación de datos.

Al pulsar dicho comando se abrirá el cuadro de diálogo Validación de datos donde, de manera predeterminada, la opción Cualquier valor estará seleccionada, lo cual significa que está permitido ingresar cualquier valor en la celda. Sin embargo, podremos elegir alguno de los criterios de validación disponibles para hacer que la celda solo permita el ingreso de un número entero, un decimal, una lista, una fecha, una hora o una determinada longitud del texto.

Cómo aplicar la validación de datos

Para aplicar la validación de datos sobre una celda específica, deberás asegurarte de seleccionar dicha celda y posteriormente ir al comando Datos > Herramientas de Datos > Validación de datos.

Por el contrario, si quieres aplicar el mismo criterio de validación a un rango de celdas, deberás seleccionar dicho rango antes de ejecutar el comando Validación de datos y eso hará que se aplique el mismo criterio para todo el conjunto de celdas.

Page 34: Unidad 5 excel

30

Ya que es común trabajar con una gran cantidad de filas de datos en Excel, puedes seleccionar toda una columna antes de crear el criterio de validación de datos.

Para seleccionar una columna completa será suficiente con hacer clic sobre el encabezado de la columna. Una vez que hayas hecho esta selección, podrás crear la validación de datos la cual será aplicada sobre todas las celdas de la columna.

La opción Omitir blancos

Absolutamente todos los criterios de validación mostrarán una caja de selección con el texto Omitir blancos. Ya que esta opción aparece en todos ellos, es conveniente hacer una breve explicación.

De manera predeterminada, la opción Omitir blancos estará seleccionada para cualquier criterio, lo cual significará que al momento de entrar en el modo de edición de la celda podremos dejarla como una celda en blanco es decir, podremos pulsar la tecla Entrar para dejar la celda en blanco.

Sin embargo, si quitamos la selección de la opción Omitir blancos, estaremos obligando al usuario a ingresar un valor válido una vez que entre al modo de edición de la celda. Podrá pulsar la tecla Esc para evitar el ingreso del dato, pero no podrá pulsar la tecla Entrar para dejar la celda en blanco.

La diferencia entre dejar esta opción marcada o desmarcada es muy sutil y casi imperceptible para la mayoría de los usuarios al momento de introducir datos, así que te recomiendo dejarla siempre seleccionada.

Crear validación de datos en Excel

Para analizar los criterios de validación de datos en Excel podemos dividirlos en dos grupos basados en sus características similares. El primer grupo está formado por los siguientes criterios:

Número entero Decimal Fecha Hora Longitud de texto

Ilustración 23: Omitir blancos

Page 35: Unidad 5 excel

31

Estos criterios son muy similares entre ellos porque comparten las mismas opciones para acotar los datos que son las siguientes: Entre, No está entre, Igual a, No igual a, Mayor que, Menor que, Mayor o igual que, Menor o igual que.

Para las opciones “entre” y “no está entre” debemos indicar un valor máximo y un valor mínimo pero para el resto de las opciones indicaremos solamente un valor. Por ejemplo, podemos elegir la validación de números enteros entre los valores 50 y 100 para lo cual debemos configurar del criterio de la siguiente manera:

Por el contrario, si quisiéramos validar que una celda solamente acepte fechas mayores al 01 de enero del 2015, podemos crear el criterio de validación de la siguiente manera:

Page 36: Unidad 5 excel

32

Una vez que hayas creado el criterio de validación en base a tus preferencias, será suficiente con pulsar el botón Aceptar para asignar dicho criterio a la celda, o celdas, que hayas seleccionado previamente.

Lista de validación de datos

A diferencia de los criterios de validación mencionados anteriormente, la Lista es diferente porque no necesita de un valor máximo o mínimo sino que es necesario indicar la lista de valores que deseamos permitir dentro de la celda. Por ejemplo, en la siguiente imagen he creado un criterio de validación basado en una lista que solamente aceptará los valores sábado y domingo.

Puedes colocar tantos valores como sea necesario y deberás separarlos por el carácter de separación de listas configurado en tu equipo. En mi caso, dicho separador es la coma (,) pero es probable que debas hacerlo con el punto y coma (;). Al momento de hacer clic en el botón Aceptar podrás confirmar que la celda mostrará un botón a la derecha donde podrás hacer clic para visualizar la lista de opciones disponibles:

Page 37: Unidad 5 excel

33

Para que la lista desplegable sea mostrada correctamente en la celda deberás asegurarte que, al momento de configurar el criterio validación de datos, la opción Celda con lista desplegable esté seleccionada.

En caso de que los elementos de la lista sean demasiados y no desees introducirlos uno por uno, es posible indicar la referencia al rango de celdas que contiene los datos. Por ejemplo, en la siguiente imagen puedes observar que he introducido los días de la semana en el rango G1:G7 y dicho rango lo he indicado como el origen de la lista.

Lista de validación con datos de otra hoja

Muchos usuarios de Excel utilizan la lista de validación con los datos ubicados en otra hoja. En realidad es muy sencillo realizar este tipo de configuración ya que solo debes crear la referencia adecuada a dicho rango.

Supongamos que la misma lista de días de la semana la he colocado en una hoja llamada DatosOrigen y los datos se encuentran en el rango G1:G7. Para hacer referencia a dicho rango desde otra hoja, debo utilizar la siguiente referencia:

=DatosOrigen!G1:G7

Para crear una lista desplegable con esos datos deberás introducir esta referencia al momento de crear el criterio de validación.

Page 38: Unidad 5 excel

34

Ilustración 24: Lista de validación de datos de otra hoja.

Personalizar el mensaje de error

Tal como lo mencioné al inicio del artículo, es posible personalizar el mensaje de error mostrado al usuario después de tener un intento fallido por ingresar algún dato. Para personalizar el mensaje debemos ir a la pestaña Mensaje de error que se encuentra dentro del mismo cuadro de diálogo Validación de datos.

Ilustración 25: Personalizar mensajes de error.

Para la opción Estilo tenemos tres opciones: Detener, Advertencia e Información. Cada una de estas opciones tendrá dos efectos sobre la venta de error: en primer lugar realizará un cambio en el icono mostrado y en segundo lugar mostrará botones diferentes. La opción Detener mostrará los botones Reintentar, Cancelar y Ayuda. La opción Advertencia mostrará los botones Si, No, Cancelar y Ayuda. La opción Información mostrará los botones Aceptar, Cancelar y Ayuda.

Page 39: Unidad 5 excel

35

La caja de texto Título nos permitirá personalizar el título de la ventana de error que de manera predeterminada se muestra como Microsoft Excel. Y finalmente la caja de texto Mensaje de error nos permitirá introducir el texto que deseamos mostrar dentro de la ventana de error.

Por ejemplo, en la siguiente imagen podrás ver que he modificado las opciones predeterminadas de la pestaña Mensaje de error:

Como resultado de esta nueva configuración, obtendremos el siguiente mensaje de error:

Cómo eliminar la validación de datos

Si deseas eliminar el criterio de validación de datos aplicado a una celda o a un rango, deberás seleccionar dichas celdas, abrir el cuadro de diálogo Validación de datos y pulsar el botón Borrar todos.

Page 40: Unidad 5 excel

36

Ilustración 26: Eliminar validación de datos.

Al pulsar el botón Aceptar habrás removido cualquier validación de datos aplicada sobre las celdas seleccionadas.

Espero que con esta guía tengas una mejor y más clara idea sobre cómo utilizar la validación de datos en Excel. Esta funcionalidad será de gran ayuda para controlar el ingreso de datos por parte de los usuarios y disminuir en gran medida la probabilidad de que se ingrese información incorrecta lo cual podría tener un efecto catastrófico sobre nuestras fórmulas y el análisis de los datos. (Ortíz, 2015)

Aquí podrás encontrar un video de validación de datos en Excel: https://www.youtube.com/watch?v=Cc4gHT-hJsU

Page 41: Unidad 5 excel

37

MANEJO DE DATOSTIPOS DE DATOS

Existen dos tipos de valores para ingresar en las celdas:

* Valores constantes: Son datos que se ingresan directamente en una celda. Pueden ser texto, numéricos, fechas, horas, signos, etc. Para cambiar el valor constante se lo modifica manualmente.

* Fórmulas: son referencias de celdas, nombres de celdas o rangos, funciones u operadores que dan como resultado un valor a partir de otros valores existentes.

Excel permite ingresar diferente tipos de datos:

1. Texto2. Números3. Fechas4. Horas5. Valores Lógicos (Verdadero y Falso)6. Fórmulas y funciones7. Valores de error8. Mandatos u Órdenes.

1. Texto

Son aquellos textos donde se involucran letras, números y símbolos.

Los textos se acomodan, generalmente y en forma automática, a la izquierda de la celda.

Para escribir el título de la columna solo debe posicionarse en la celda correspondiente y digitarlo. 

2. Números

 Corresponden exclusivamente a números.

 Para los números se pueden seleccionar formatos predefinidos o existe la posibilidad de crear nuevos formatos.

Si selecciona una celda que contenga números, y pulsa el botón derecho del Mouse, se desplegará un menú contextual; seleccione FORMATO DE CELDAS. La opción “Números” traerá los formatos predefinidos. También en esta opción  podrá crear los nuevos formatos.

El símbolo # significa que la cifra puede o no estar:

Page 42: Unidad 5 excel

38

#.### miles con punto

#.##0,00  si no aparece ningún el número colocará 0,00 en caso contrario colocará los decimales.

Para reemplazar al símbolo de moneda que trae predefinido el Excel, ingrese en WINDOWS en el PANEL DE CONTROL  y en el icono Internacional lo podrá modificar.

Cuando en Excel pulse el botón Moneda, automáticamente coloca el signo $ pero alineado a la izquierda.

3. Fechas

ej. 12/01/12. en La opción “Formato de las celdas” se presentas distintas formas de visualizar las fechas.

4. Horas

ej. 09:23 en La opción “Formato de las celdas” se presentas distintas formas de visualizar la hora.

5. Valores Lógicos (Verdadero y Falso)

Para ingresar este tipo de datos simplemente tipee VERDADERO o FALSO, según lo que necesite.

6. Fórmulas y funciones:

 Una fórmula puede ser una operación matemática, una función o una referencia a celdas de la hoja o de otra hoja y comienza con un signo = (igual).

7. Valores de error

Excel al evaluar una fórmula si encuentra algún error, muestra un valor de error.

Ej.: #####  el valor numérico introducido en la celda o el resultado de la fórmula es demasiado ancho para poder visualizarlo

#¡REF!(#REF!) en la fórmula se está haciendo referencia a una celda inexistente. (nlm48, 2010)

Page 43: Unidad 5 excel

39

TIPOS DE OPERADORES Y PRECEDENCIALos operadores son un elemento básico de las fórmulas en Excel. Un operador es un símbolo que representa una determinada operación. En esta ocasión haremos una revisión detallada de todos los tipos de operadores en Excel así como su precedencia, es decir, el orden en que se realizan las operaciones.

Podemos clasificar las fórmulas de Excel en cuatro grupos principales:

1. Fórmulas aritméticas2. Fórmulas de comparación3. Fórmulas de texto4. Fórmulas de referencia

1. Operadores aritméticos

Las fórmulas aritméticas son las más comunes y combinan números, referencias de celda, funciones y operadores aritméticos para realizar cálculos matemáticos. La siguiente tabla muestra los operadores aritméticos de Excel:

Ilustración 27: Operadores Aritméticos de Excel.

2. Operadores de comparación

Los operadores de comparación nos permiten comparar dos o más números o cadenas de texto. Si el resultado de la comparación es positivo, obtendremos como resultado en valor lógico VERDADERO. De lo contrario obtendremos como resultado el valor FALSO. A continuación tenemos la lista de operadores de comparación:

Page 44: Unidad 5 excel

40

Ilustración 28: Operadores de Comparación de Excel.

3. Operadores de texto

Las fórmulas de Excel también pueden manipular texto y pueden hacer uso del operador de concatenación para unir el valor de dos cadenas de texto.

Ilustración 29: Operadores de texto de Excel.

4. Operadores de referencia

Los operadores de referencia nos ayudan a combinar dos o más referencias de celda para crear una sola referencia.

Ilustración 30: Operadores de referencia de Excel.

Precedencia de operadores en Excel

Cuando creamos fórmulas que contienen más de un operador, será necesario conocer el orden en que dichas operaciones serán calculadas por Excel. Por esta razón existe un orden de precedencia que determina la manera en que Excel hace los cálculos:

Page 45: Unidad 5 excel

41

Ilustración 31: Precedencia de operadores en Excel.

Si tenemos una fórmula con varios operadores con la misma precedencia, entonces Excel realizará los cálculos de izquierda a derecha.

Cómo controlar el orden de precedencia

La única manera en que podemos influir en el orden de precedencia de operadores en Excel es utilizando paréntesis. Al colocar paréntesis alrededor de un cálculo estaremos diciendo a Excel que deseamos otorgar una alta prioridad a dicho cálculo y por lo tanto se realizará primero. En la siguiente imagen puedes observar cómo se modifica el resultado de una fórmula al incluir paréntesis.

Ilustración 32: Control de operadores de precedencia, mediante uso de paréntesis.

Cuando existen paréntesis dentro de una fórmula, Excel comenzará los cálculos con el paréntesis que tenga el mayor nivel de anidación. Y si dentro de un mismo paréntesis existen varios operadores, entonces se aplicarán las reglas de precedencia antes vistas. De esta manera los paréntesis nos permiten controlar el orden de precedencia de los cálculos es una fórmula de Excel. (Ortíz, 2013a)

Aquí podemos encontrar un video sobre tipos de operadores de Excel:

https://www.youtube.com/watch?v=RYTUVnd3kyA&feature=youtu.be

Page 46: Unidad 5 excel

42

TIPOS DE ERRORES

El conocimiento de cada uno de estos errores nos permitirá conocer su origen y solucionarlo.

Ilustración 33: Tipos de errores en Excel.

#¿NOMBRE? – Este error se produce cuando Excel no reconoce el texto de la fórmula introducida en la celda, bien sea porque no está bien escrita la fórmula o porque no existe.

Ejemplo - si queremos calcular la potencia de 3,4 elevado a 15 deberemos introducir la siguiente fórmula o función en la celda correspondiente "=POTENCIA(3,4;15)" en el caso que escribiésemos "=POTEN(3,4;15)" Excel nos mostraría el error #¿NOMBRE? dado a que no tiene registrado la función POTEN.

#¡VALOR! – Este tipo de error se produce cuando Excel detecta que se está realizando cálculos con tipos de datos distintos entre sí, como datos numéricos, texto o fecha y hora.

Ejemplo - La potencia de 4 elevado a una letra o texto "=POTENCIA(4;A)" dará como resultado el error #¡VALOR!, puesto que no es posible ni tiene sentido la realización de dicho cálculo.

#¡NUM! – Este error se produce cuando Excel detecta cuando una fórmula o función requiere un dato numérico y se ha introducido un dato no numérico como una letra o una fecha.

También puede ser que el resultado del cálculo resulta tan grande o pequeño que Excel no pueda mostrarlo.

Ejemplo – El cálculo de la potencia de 1000 elevado a 103 da como resultado el error #¡NUM! dado a que Excel no puede mostrar el valor tan elevado de dicho cálculo.

Page 47: Unidad 5 excel

43

#¡DIV/0! – Este error se produce cuando Excel detecta que se ha realizado un cálculo de un número dividido por 0 o por una celda que no contiene ningún valor.

Ejemplo – El cálculo de una celda que contenga la siguiente fórmula "=5/0" dará como resultado el error #¡DIV/0!

#¡REF! – Este error se produce cuando Excel detecta el uso de una función o de un cálculo con referencias de celdas no válidas.

Ejemplo – Este error se produce generalmente cuando se borran columnas o filas que contienen datos vinculados a unas fórmulas, al desaparecer dichos datos las fórmulas no pueden calcular y aparece el error #¡REF!

#¡NULO! – Este error se genera cuando queremos relacionar mediante algún cálculo o función rangos de datos o celdas que no interseccionan.

Ejemplo – La suma de 2 rangos de datos separados, tal y como muestra la imagen dá como resultado el error #¡NULO!

#N/A –Este error se genera en las hojas de cálculo de Excel cuando se utilizan funciones de búsqueda o coincidencia de datos los cuales no se existen en el rango de búsqueda especificado.

Ejemplo – usar la función BuscarV para encontrar un valor que no existe en el rango de búsqueda especificado.

##### - Este error se muestra cuando:

El valor introducido o calculado en la celda es más grande que el ancho de la columna

Se ha calculado o introducido un valor negativo de una fecha.

(“Errores de Excel - Error en Excel”, s/f)

Page 48: Unidad 5 excel

44

FÓRMULASFORMULACIÓN SIMPLE CON VALORES CONSTANTESPuede crear una fórmula sencilla para sumar, restar, multiplicar o dividir los valores de la hoja de cálculo. Las fórmulas sencillas siempre comienzan con un signo igual (=), seguido de constantes que son valores numéricos y operadores de cálculo, como el signo más (+), el signo menos (-), el asterisco (*) o la barra diagonal (/).

1. En la hoja de cálculo, haga clic en la celda en que desea introducir la fórmula.2. Escriba el = (signo igual), seguido de las constantes y los operadores que desea

utilizar en el cálculo.

Puede introducir en una fórmula tantas constantes y tantos operadores como necesite, hasta un máximo de 8.192 caracteres.

3. Presione Entrar.

Para agregar valores rápidamente, puede usar Autosuma en lugar de introducir la fórmula manualmente (pestaña Inicio, grupo Edición).

También puede usar las funciones (como la función suma) para calcular valores en la hoja de cálculo.

Para ir un paso más allá, puede usar referencias de celdas y nombres en lugar de los valores en una fórmula simple. Para más información, vea Usar referencias de celdas en fórmulas y Definir y usar nombres en fórmulas.

Usar constantes en fórmulas de ExcelUna constante es un valor que no se calcula, sino que siempre permanece igual. Por ejemplo, la fecha 9-10-2008, el número 210 y el texto "Ganancias trimestrales" son constantes. Una expresión o un valor obtenido como resultado de una expresión, no son constantes. Si usa constantes en la fórmula en vez de referencias a celdas (por ejemplo, =30+70+110), el resultado solo cambia si modifica la fórmula. En general, es mejor insertar constantes en celdas individuales donde se puedan cambiar fácilmente si fuera necesario y luego hacer referencia a esas celdas en las fórmulas.

Partes de una fórmula   

Ilustración 34: Parte de una fórmula de Excel.

1. Funciones: la función PI() devuelve el valor de Pi: 3,142...

2. Referencias: A2 devuelve el valor de la calda A2.

3. Constantes: números o valores de texto escritos directamente en una fórmula como, por ejemplo, 2.

Page 49: Unidad 5 excel

45

4. Operadores: el operador ^ (acento circunflejo) eleva un número a una potencia y el operador * (asterisco) multiplica números. (“Información general sobre fórmulas en Excel - Excel”, s/f)

Page 50: Unidad 5 excel

46

REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS

De forma predeterminada, una referencia de celda es relativa. Por ejemplo, al hacer referencia a la celda A2 de la celda C2, se realmente hace referencia a una celda que tiene dos columnas a la izquierda (C menos A) y, en la misma fila (2). Una fórmula que contenga una referencia de celda relativa cambia al copiar una celda a otra.

Por ejemplo, si copia la fórmula = B2 + A2 de la celda C2 en D2, la fórmula en D2 se ajusta a la derecha por una columna y se convierte en = B2 + C2. Si desea mantener la referencia de celda original en este ejemplo, cuando lo copie, realice la referencia de celda absoluta anterior a la fila (2) con un signo de dólar ($) y columnas (A y B). A continuación, cuando copie la fórmula = $A$ 2 + $B$ 2 de C2 en D2, la fórmula sea la misma.

En casos menos frecuentes, tal vez desee hacer una referencia de celda “mixta”, para lo cual debe colocar el signo dólar delante de la columna o del valor de fila para “bloquear” la columna o la fila (por ejemplo, $A2 o B$3). Para cambiar el tipo de referencia de celda:

1. Seleccione la celda que contenga la fórmula.2. En la barra de fórmulas , seleccione la referencia que desee cambiar.3. Presione F4 para cambiar entre los tipos de referencia.

En la tabla siguiente se indica cómo se actualiza un tipo de referencia si la fórmula que la contiene se copia dos celdas hacia abajo y dos hacia la derecha.

Ilustración 35: Referencias en Excel.

(“Alternar entre referencias relativas, absolutas y mixtas - Excel”, s/f)

Page 51: Unidad 5 excel

47

FUNCIONES BÁSICASDESCRIPCIÓN, SINTÁXIS

Ilustración 36: Sintaxis de las funciones de Excel.

Primero que todo tenemos que saber que es una función, las funciones son formulas predefinidas que ejecutan cálculos por medio de valores específicos, denominados argumentos y que en general devuelven un resultado.

La sintaxis de una función para Excel es de la siguiente manera =nombre de la función (argumento;argumento;argumento). Cada función tiene un conjunto de argumentos válidos pueden ser valores numéricos, alfanuméricos, direcciones de celdas, valores lógicos, etc. e incluso otras funciones.

-si se conoce la sintaxis de la función, puede ingresarla manualmente a la celda respectiva.

-de lo contrario se hará uso del asistente desde el menú insertar y función que está representada con una f   seleccionado de la barra de herramientas. 

Ilustración 37: Funciones básicas de Excel.

FUNCIÓN SUMA:

=SUMA(ARGUMENTO;ARGUMENTO) función matemática que devuelve el resultado de la adición de los argumentos que pueden ser valores, direcciones de celdas o rangos de celdas si el contenido de la celda es alfanumérico se ignora.

Page 52: Unidad 5 excel

48

FUNCIÓN PROMEDIO:

=PROMEDIO(ARGUMENTO;ARGUMENTO;ARGUMENTO) esta es una función estadística que devuelve el promedio o media aritmética de los argumentos especificados, estos pueden ser valores, referencias de celdas o rangos de celdas.

FUNCIÓN CONTAR:

=CONTAR(ARGUMENTO;ARGUMENTO;ARGUMENTO) función estadística que cuenta el número de celdas que contienen valores numéricos, los argumentos que son valores de error o bien un texto que no puede traducirse en números, se pasan por alto.

FUNCIONES MÁXIMO Y MÍNIMO 

=MAX(ARGUMENTO;ARGUMENTO)   –   =MIN(ARGUMENTO;ARGUMENTO) funciones estadísticas que devuelven respectivamente, el mínimo y el máximo de un conjunto de valores o contenidos de celdas consignados en los argumentos, si los argumentos no contienen números, ambas funciones devuelven a 0.

FUNCIONES LÓGICAS: 

Excel tiene la posibilidad de incluir condicionales en las formulas, esto significa que permite elegir entre dos acciones según que la condición sea verdadera o falsa =SI(CONDICIÓN;ARGUMENTO_SI_ES_V;ARGUMENTO_SI_ES_F). (“SINTAXIS DE LAS FUNCIONES EN EXCEL”, 2013)

BUSCARH:

Descripción: Busca valor_buscado en la primera fila de la matriz_buscar_en y devuelve el elemento de la misma fila especificado en subindice_elto.Sintaxis: =BUSCARH(valor_buscado;matriz_buscar_en,subindice_elto)

FUNCIÓN BUSCARV:

Descripción: Busca valor_buscado en la primera columna del la matriz_buscar_en y devuelve el elemento de la misma fila especificado en subindice_elto.Sintaxis: =BUSCARV(valor_buscado;matriz_buscar_en,subindice_elto)

FUNCIÓN CONTAR.SI:

Descripción: Cuenta las celdas en la matriz que coinciden con la condición dada. Sintaxis:=CONTAR.SI(matriz;condicion)

FUNCIÓN HOY:

Descripción: Devuelve la fecha actual.Ej:=HOY()

Page 53: Unidad 5 excel

49

FUNCIÓN REDONDEAR:

Descripción: Redondea un número al número_de_decimales especificado.Sintaxis: =REDONDEAR(numero,numero_de_decimales)

FUNCIÓN SI:

Descripción: Calcula una condición, y si ésta es cierta devuelve el primer valor y si no el segundo.Sintaxis:=SI(condicion;primer_valor;segundo_valor) (“Funciones de Excel”, s/f)

Aquí podemos encontrar un video sobre la aplicación de las funciones básicas de Excel:

https://www.youtube.com/watch?v=V2sWe-Fn7X8

Page 54: Unidad 5 excel

50

FUNCIONES DE BÚSQUEDABUSCARV

La función BUSCARV en Excel nos permite encontrar un valor dentro de un rango de datos, es decir, podemos buscar un valor dentro de una tabla y saber si dicho valor existe o no. Esta función es una de las más utilizadas para realizar búsquedas en Excel por lo que es importante aprender a utilizarla adecuadamente.

Preparar los datos para la función BUSCARV

Para utilizar la función BUSCARV debemos cumplir con algunas condiciones en nuestros datos. En primer lugar debemos tener la información organizada de manera vertical, es decir organizada por columnas.

Ilustración 38: Organización de datos previo a utilizar la función BUSCARV.

Algo que nunca debes olvidar es que la función BUSCARV siempre realizará la búsqueda sobre la primera columna de los datos. En el rango mostrado en la imagen anterior (A2:B11), la función BUSCARV realizará la búsqueda sobre la columna A.

Algo que también debemos cuidar con la tabla de búsqueda es que, si existen otras tablas de datos en la misma hoja de Excel debemos dejar al menos una fila y una columna en blanco entre nuestros datos de búsqueda y las otras tablas de manera que la función detecte adecuadamente el rango donde se realizará la búsqueda.

Una vez que nuestros datos cumplen con estas condiciones estaremos listos para utilizar la función BUSCARV y realizar búsquedas en Excel.

Sintaxis de la función BUSCARV

La gran mayoría de las funciones de Excel tienen argumentos que son la manera en cómo le indicamos los datos con los que trabajará así como ciertos criterios de ejecución. En el caso de la función BUSCARV tenemos cuatro argumentos que describo a continuación:

Page 55: Unidad 5 excel

51

Ilustración 39: Argumentos de la función BUSCARV.

Valor_buscado (obligatorio): Este es el valor que queremos encontrar y el cual será buscado en la primera columna del rango de datos. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. La función BUSCARV no hará diferencia entre mayúsculas y minúsculas.

Matriz_buscar_en (obligatorio): El segundo argumento es una referencia al rango de celdas que contiene los datos.

Indicador_columnas (obligatorio): El Indicador_columnas es el número de columna que deseamos obtener como resultado. Una vez que la función BUSCARV encuentra una coincidencia del Valor_buscado nos devolverá como resultado la columna que indiquemos en este argumento.

Ordenado (opcional): Este argumento es un valor lógico, es decir falso o verdadero. Con este argumento indicamos a la función BUSCARV el tipo de búsqueda que realizará y que puede ser una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). Si este argumento se omite se supondrá un valor VERDADERO.

Como puedes ver, la función BUSCARV tiene tres argumentos obligatorios y uno opcional. Sin embargo, te recomiendo siempre utilizar los cuatro argumentos indicando en el último de ellos el valor FALSO para asegurar una búsqueda exacta.

(Ortíz, 2012a)

Page 56: Unidad 5 excel

52

BUSCARH

La función BUSCARH en Excel busca un valor dentro de una fila y devuelve el valor que ha sido encontrado o un error #N/A en caso de no haberlo encontrado. Esta función es similar, en cierto sentido, a la función BUSCARV.

Cuando utilizar la función BUSCARH

Debemos utilizar la función BUSCARH cuando el valor que estamos buscando se encuentra en una fila de alguna tabla de datos. Por el contrario, la función BUSCARV realiza la búsqueda en una columna.

Ilustración 40: Diferencias para la aplicación de la función BUSCARH y BUSCARV.

Sintaxis de la función BUSCARH

La función BUSCARH tiene tres argumentos que son los siguientes:

Ilustración 41: Argumentos de la función BUSCARV.

Page 57: Unidad 5 excel

53

Valor_buscado (obligatorio): El valor que estamos buscando. Matriz_buscar_en (obligatorio): El rango que contiene los valores y que debe ser

una fila. Indicador_filas (obligatorio): El número de fila que contiene el valor que regresará

la función. Ordenado (opcional): El valor debe ser FALSO si queremos una coincidencia

exacta o VERDADERO para una coincidencia aproximada.

Si la función BUSCARH no encuentra el valor que está siendo buscado regresará el valor de error #N/A.

(Ortíz, 2012a)

Aquí podrá encontrar un video sobre la aplicación de la función BUSCARH y BUSCARV: https://www.youtube.com/watch?v=QwVQ2zARqhs

Page 58: Unidad 5 excel

54

FUNCIONES LÓGICAS SI CONDICIONAL SIMPLE

La función SI en Excel es parte del grupo de funciones Lógicas y nos permite evaluar una condición para determinar si es falsa o verdadera. La función SI es de gran ayuda para tomar decisiones en base al resultado obtenido en la prueba lógica.

Sintaxis de la función SI

Además de especificar la prueba lógica para la función SI también podemos especificar valores a devolver de acuerdo al resultado de la función.

Ilustración 42: Argumentos de la función SI.

Prueba_lógica (obligatorio): Expresión lógica que será evaluada para conocer si el resultado es VERDADERO o FALSO.

Valor_si_verdadero (opcional): El valor que se devolverá en caso de que el resultado de la Prueba_lógica sea VERDADERO.

Valor_si_falso (opcional): El valor que se devolverá si el resultado de la evaluación es FALSO.

La Prueba_lógica puede ser una expresión que utilice cualquier operador lógico o también puede ser una función de Excel que regrese como resultado VERDADERO o FALSO.

Los argumentos Valor_si_verdadero y Valor_si_falso pueden ser cadenas de texto, números, referencias a otra celda o inclusive otra función de Excel que se ejecutará de acuerdo al resultado de la Prueba_lógica.

Page 59: Unidad 5 excel

55

Ejemplos de la función SI

Probaremos la función SI con el siguiente ejemplo. Tengo una lista de alumnos con sus calificaciones correspondientes en la columna B. Utilizando la función SI desplegaré un mensaje de APROBADO si la calificación del alumno es superior o igual a 60 y un mensaje de REPROBADO si la calificación es menor a 60. La función que utilizaré será la siguiente:

=SI(B2>=60,"APROBADO","REPROBADO")

Observa el resultado al aplicar esta fórmula en todas las celdas de la columna C.

Utilizar una función como prueba lógica

Es posible utilizar el resultado de otra función como la prueba lógica que necesita la función SI  siempre y cuando esa otra función regrese como resultado VERDADERO o FALSO. Un ejemplo de este tipo de función es la función ESNUMERO la cual evalúa el contenido de una celda y devuelve el valor VERDADERO en caso de que sea un valor numérico. En este ejemplo quiero desplegar  la leyenda “SI” en caso de que la celda de la columna A efectivamente tenga un número, de lo contrario se mostrará la leyenda “NO”.

=SI(ESNUMERO(A2), "SI", "NO")

Este es el resultado de aplicar la fórmula sobre los datos de la hoja:

Page 60: Unidad 5 excel

56

Ilustración 43: ejemplo de la función SI como prueba lógica.

(Ortíz, 2012b)

Aquí podemos encontrar el link de un video sobre la función SI:

https://www.youtube.com/watch?v=3PdIJwbqrqs&feature=youtu.be

Page 61: Unidad 5 excel

57

SI CONDICIONAL ANIDADO

La función SI nos permite evaluar una condición y ejecutar una acción dependiendo el resultado, ya sea verdadero o falso. Esto funciona muy bien cuando solo estamos comparando dos valores pero ¿Cómo podemos utilizar la función SI al tener más de dos posibles opciones? La respuesta es utilizar la función SI anidada.

La función SI anidada

Para utilizar correctamente la función SI anidada debemos utilizar una función SI como el tercer argumento de la función principal. Por ejemplo, para evaluar si una celda tiene alguna de las tres letras posibles (A, B, C) podemos utilizar la siguiente fórmula:

=SI(A1="A",100,SI(A1="B",80,60))

Si el valor de la celda A1 tiene la letra A obtendremos el valor 100. Si la celda A1 tiene la letra B, entonces obtendremos el valor 80, de lo contrario obtendremos el valor 60. En este ejemplo tenemos dos pruebas lógicas que nos ayudan a decidir correctamente sobre las tres opciones posibles. Observa cómo esta fórmula regresa el equivalente adecuado para cada letra de la columna A:

Ilustración 44: Ejemplo 1 de función SI anidada.

Si por el contrario, en lugar de tres opciones necesitamos considerar cuatro, solo debemos agregar otra función SI anidada a nuestra fórmula de la siguiente manera:

=SI(A1="A",100,SI(A1="B",80,SI(A1="C",60,40)))

Observa el resultado de esta fórmula, especialmente para los valores que tienen la letra “D”:

Page 62: Unidad 5 excel

58

Ilustración 45: Ejemplo 2 de función SI anidada.

Si tuviéramos una quinta opción, entonces remplazaría el último parámetro de la función SI que tiene el mayor nivel de anidación para insertar una nueva función SI de la siguiente manera:

=SI(A1="A",100,SI(A1="B",80,SI(A1="C",60,SI(A1="D",40,20))))

Como podrás ver, con cada función SI que agregamos a nuestra fórmula su complejidad irá en aumento y es muy común que muchos usuarios de Excel comiencen a tener problemas para escribir correctamente múltiples funciones SI anidadas.

Mi recomendación es no exceder el máximo de 4, o a lo mucho 5, funciones SI anidadas de manera que se pueda comprender fácilmente la intención de la fórmula y detectar cualquier error en su escritura. Pero si decides utilizar varias funciones SI anidadas debes recordar que nunca podrás exceder el máximo de 64 funciones anidadas que permite Excel 2010. (Ortíz, 2013b)

Aquí podemos encontrar el link de un video sobre la función SI anidada:

https://www.youtube.com/watch?v=rJ2JsgYpx5k&feature=youtu.be

Page 63: Unidad 5 excel

59

FUNCIÓN Y La función Y en Excel recibe como argumentos una serie de expresiones lógicas. Si todas ellas son verdaderas, entonces la función Y devolverá el valor verdadero. Si alguna de las expresiones lógicas es falsa, entonces la función Y devolverá el valor falso.

Sintaxis de la función Y

=Y(valor_lógico1, [valor_lógico2], …)

valor_lógico1 (obligatorio): La primera condición a evaluar como VERDADERO o FALSO.

valor_lógico2 (opcional): Las condiciones adicionales que se desean evaluar hasta un máximo de 255 condiciones.

Argumentos de la función Y

Todos los argumentos de la función Y deben ser expresiones que devuelven un valor lógico, es decir, VERDADERO o FALSO. Los argumentos pueden ser las expresiones mismas como se muestra en la siguiente imagen:

Ilustración 46: Argumento 1 de la función Y.

También pueden ser referencias a otras celdas que contienen los valores lógicos a considerar:

Ilustración 47: Argumento 2 de la función Y.

Y también pueden ser funciones que devuelven el valor VERDADERO o FALSO como es el caso de varias funciones de información en Excel por ejemplo la función ES.PAR:

Page 64: Unidad 5 excel

60

Ilustración 48: Argumento 3 de la función Y.

Independientemente del tipo de argumentos que decidas utilizar debes recordar que la función Y acepta un máximo de 255 argumentos y cada uno de ellos debe devolver el valor VERDADERO o FALSO. La función Y devolverá VERDADERO solamente si todos y cada uno de los argumentos también devuelve el valor lógico VERDADERO.  Podemos resumir el comportamiento de la función Y con la siguiente tabla:

Ilustración 49: Comportamiento de la función Y.

(Ortíz, 2013c)

Page 65: Unidad 5 excel

61

FUNCIÓN O

La función O es una de las funciones lógicas de Excel y como cualquier otra función lógica solamente devuelve los valores VERDADERO o FALSO después de haber evaluado las expresiones lógicas que se hayan colocado como argumentos.

Sintaxis de la función O

La función O en Excel nos ayudará a determinar si al menos uno de los argumentos de la función es VERDADERO.

Ilustración 50: Argumentos de la función O

Valor_lógico1 (obligatorio): Expresión lógica que será evaluada por la función. Valor_lógico2 (opcional): A partir del segundo argumento las expresiones lógicas a

evaluar con opcionales hasta un máximo de 255.

La única manera en que la función O devuelva el valor FALSO es que todas las expresiones lógicas sean falsas. Si al menos una expresión es verdadera entonces el resultado de la función O será VERDADERO.

Ejemplos de la función O

Para comprobar el comportamiento de la función O haremos un ejemplo sencillo con la siguiente fórmula:

=O(1=2, 3>4, 5<>5, 7<=6, 8>=9)

Si analizas con detenimiento cada una de las expresiones verás que todas son falsas y por lo tanto la función O devolverá el valor FALSO.

Page 66: Unidad 5 excel

62

Como mencioné anteriormente, la función O devolverá un valor VERDADERO si al menos una de las expresiones lógicas es verdadera. En nuestro ejemplo modificaré solamente la primera expresión para que sea 1=1 de manera que tenga la siguiente fórmula:

=O(1=1, 3>4, 5<>5, 7<=6, 8>=9)

Esto deberá ser suficiente para que la función O devuelva un valor VERDADERO.

Funciones como argumento de la función O

Podemos utilizar funciones como argumentos de la función O siempre y cuando devuelvan VERDADERO o FALSO como resultado. En el siguiente ejemplo utilizo las funciones ESNUMERO y ESTEXTO para evaluar el tipo de dato de las celdas B1 y B2.

Ilustración 51: Funciones como argumentos de la función O - Verdadero.

Ya que la celda B1 es un número la función ESNUMERO regresa el valor VERDADERO. Por otro lado la celda B2 es efectivamente una cadena de texto y por lo tanto la función ESTEXTO devuelve el valor VERDADERO. En consecuencia la función O también regresa el valor VERDADERO. Ahora intercambiaré los valores de las celdas B1 y B2 de manera que tanto la función ESNUMERO como la función ESTEXTO devuelvan FALSO.

Ilustración 52: Funciones como argumentos de la función O - Falso.

No olvides que la función O en Excel siempre devolverá VERDADERO excepto cuando TODAS las expresiones lógicas evaluadas sean falsas. (Ortíz, 2012c)

Aquí podemos encontrar el link de un video sobre función Y, función O:

https://www.youtube.com/watch?v=xJzfAWY1Nug

Page 67: Unidad 5 excel

63

GRÁFICOS ESTADÍSTICOSINTRODUCCIÓN AL DISEÑO

Los gráficos se usan para presentar series de datos numéricos en formato gráfico y de ese modo facilitar la comprensión de grandes cantidades de datos y las relaciones entre diferentes series de datos.

Tipos de series estadísticas.

Si queremos hacer gráficos estadístico en Excel deberemos conocer la clasificación de series estadísticas para construir los datos según las característsicas que queramos evaluar. Existen 4 tipos de series estadísticas:

Serie cuantitativa: según una variable cuantitativa. Serie cualitativa: de acuerdo con una variable cualitativa. Serie geográfica: tipo de serie cualitativa donde la información se clasifica según

espacios geográficos. Serie cronológica: información clasificada por periodos de tiempo.

Tipos de gráficos estadísticos en Excel.

A la hora de crear gráficos estadísticos en Excel utilizaremos un tipo de gráfico estadístico concreto para cada serie estadística. A continuación, mostramos qué tipo de gráfico de Excel será más adecuado para cada serie:

Gráfico de barras: para series estadísticas cualitativas o geográficas. Como por ejemplo, la distribución de colegios por provinvica (serie geográfica) o distribución de personas según su sexo (serie cualitativa).

Gráfico de columnas: cuando las series son cronológicas o cuantitativas utilizaremos este tipo de gráfico. Un ejemplo sería las ventas anuales de un periodo determinado (serie cronológica) o el número de asignaturas que se han cogido los estudiantes en la universidad (serie cuantitativa discreta).

Gráfico lineales: para representar series de tiempo o cronológicas. Por ejemplo: las importaciones de un país en un periodo de tiempo.

Gráfico circular: podremos plasmar la información de forma relativa o procentual.

Elementos de los gráficos

Un gráfico consta de numerosos elementos. Algunos de ellos se presentan de forma predeterminada y otros se pueden agregar según las necesidades. Para cambiar la presentación de los elementos del gráfico puede moverlos a otras ubicaciones dentro del gráfico o cambiar su tamaño o su formato. También puede eliminar los elementos del gráfico que no desee mostrar.

Page 68: Unidad 5 excel

64

Ilustración 53: Elementos del gráfico estadístico.

1. El área del gráfico.

2. El área de trazado del gráfico.

3. Los puntos de datos de la serie de datos que se trazan en el gráfico.

4. Los eje horizontal (categorías) y vertical (valores) en los que se trazan los datos del gráfico.

5. La leyenda del gráfico.

6. Un título de eje y de gráfico que puede agregar al gráfico.

7. Una etiqueta de datos que puede usar para identificar los detalles de un punto de datos de una serie de datos.

Pasos para crear un gráfico estadístico en Excel:

Empezamos con la primera tabla seleccionamos solo los datos relevantes que son la columna Partidos y Votos los seleccionamos como muestra en la imagen de abajo:

Page 69: Unidad 5 excel

65

Ilustración 54: Paso 1 en la creación de gráficos estadísticos en Excel.

Ahora hacemos clic en  la pestaña Insertar, y escogemos Grafico Circular como  muestra en la siguiente imagen:

Ilustración 55: Paso 2 en la creación de gráficos estadísticos en Excel.

Ahora si todo va bien nos saldrá la siguiente forma:

Ilustración 56: Gráfico estadístico circular.

Si hacemos clic en la imagen en la parte superior de MS Excel 2010 nos saldrá una opción en la cual nos permite agregar etiquetas y porcentajes, como muestra en la siguiente imagen:

Page 70: Unidad 5 excel

66

Ilustración 57: Diseños de gráficos estadísticos.

Recuerda que la opción de arriba que se muestra en la imagen de arriba solo aparece cuando hacemos clic en el grafico entero.

De la misma forma escogemos para este tipo de tablas el grafico Columna Agrupada 3D de la pestaña  Insertar, como muestra en la siguiente imagen:

Ilustración 58: Comando Columna para gráficos estadísticos.

Y listo nuestro resultado será este:

Ilustración 59: Gráfico estadístico de barras en 3D.

(“Crear gráficos estadísticos. excel 2010”, s/f)

Page 71: Unidad 5 excel

67

CONFIGURACIÓN DE GRÁFICOS ESTADÍSTICOS

Después de crear un gráfico, puede modificar cualquiera de sus elementos. Por ejemplo, puede cambiar la forma en que se presentan los ejes, agregar un título al gráfico, mover u ocultar la leyenda o presentar elementos de gráfico adicionales.

Ilustración 60: Gráfico combinado que utiliza uno de columnas y uno de filas.

Para modificar un gráfico, siga uno o varios de estos procedimientos:

Cambiar la presentación de los ejes del gráfico    Puede especificar la escala de los ejes y ajustar el intervalo entre los valores o las categorías que se presentan. Para que el gráfico sea más fácil de leer, también puede agregar marcas de graduación a un eje y especificar con qué intervalo aparecerán.

Agregar títulos y etiquetas de datos a un gráfico    Para que la información que aparece en el gráfico sea más clara, puede agregar un título de gráfico, títulos de eje y etiquetas de datos.

Agregar una leyenda o una tabla de datos     Puede mostrar u ocultar una leyenda, cambiar su ubicación o modificar las entradas de la leyenda. En algunos gráficos, también puede mostrar una tabla de datos que contenga las clave de leyenda y los valores representados en el gráfico.

Aplicar opciones especiales en cada tipo de gráfico    Existen líneas especiales (líneas de máximos y mínimos, y líneas de tendencias), barras (barras ascendentes y descendentes y barras de error), marcadores de datos y otras opciones para los diferentes tipos de gráficos.

Aplicar estilos y diseños de gráfico predefinidos para crear gráficos de aspecto profesional

En lugar de agregar o cambiar manualmente los elementos o el formato del gráfico, puede aplicar rápidamente un diseño y un estilo predefinidos. Excel proporciona varios diseños y estilos predefinidos de gran utilidad. No obstante, puede adaptar un diseño o un estilo de acuerdo con sus necesidades. Para ello, cambie manualmente el diseño o el formato de los distintos elementos del gráfico, como el área del gráfico, el área de trazado, la serie de datos o la leyenda.

Page 72: Unidad 5 excel

68

Cuando se aplica un diseño de gráfico predefinido, un conjunto específico de elementos del gráfico (como los títulos, la leyenda, la tabla de datos o las etiquetas de datos) se distribuyen de una manera específica en el gráfico. Puede seleccionar entre diversos diseños para cada tipo de gráfico.

Cuando se aplica un estilo de gráfico predefinido, el gráfico adopta el formato en función del tema del documento que ha aplicado, de modo que el gráfico coincida con los colores para temas (un grupo de colores), fuentes de tema (un grupo de fuentes del texto de encabezado y cuerpo) y efectos de tema (un grupo de efectos de línea y de relleno) de su organización o con los suyos propios.

No puede crear diseños o estilos de gráfico propios, pero puede crear plantillas de gráfico que incluyan el diseño y el formato del gráfico que desee.

Agregar un formato atractivo a los gráficos

Además de aplicar un estilo de gráfico predefinido, puede aplicar fácilmente formato a distintos elementos del gráfico, como los marcadores de datos, el área del gráfico, el área de trazado y los números y el texto de los títulos y etiquetas, para dotar al gráfico de un aspecto atractivo y personal. Puede aplicar estilos de forma y estilos de WordArt específicos, y también puede dar formato manualmente a las formas y al texto de los elementos.

Para agregar formato, puede usar uno o varios de los procedimientos que se detallan a continuación:

Rellenar elementos del gráfico    Puede utilizar colores, texturas, imágenes y rellenos de degradado para enfatizar elementos específicos del gráfico.

Cambiar el contorno de los elementos del gráfico    Puede usar colores, estilos de línea y grosores de línea para resaltar elementos del gráfico.

Agregar efectos especiales a los elementos del gráfico    Puede aplicar efectos especiales, como sombras, reflejos, iluminado, bordes suaves, biselados y giros 3D a las formas de los elementos del gráfico, para pulir su aspecto.

Dar formato al texto y a los números    Puede aplicar formato al texto y a los números de los títulos, etiquetas y cuadros de texto de un gráfico del mismo modo que en una hoja de cálculo. Para resaltar el texto y los números, puede incluso aplicar estilos de WordArt. (“Crear un gráfico de principio a fin - Excel”, s/f)

Gráficos dinámicosLos gráficos dinámicos son como los gráficos normales, solo que muestran los datos de una tabla dinámica. Al igual que un gráfico normal, podrás seleccionar un tipo de gráfico, el diseño y el estilo para representar mejor los datos.

Pasos para crear gráficos dinámicos

En este ejemplo, vamos a utilizar un gráfico dinámico para que podamos visualizar las tendencias en cada región de ventas. Para ello, sigue estos pasos:  

Page 73: Unidad 5 excel

69

Paso 1:

Selecciona cualquier celda de la tabla. La pestaña Opciones aparecerá en la cinta de opciones. Allí, haz clic en el el comando Gráfico dinámico.

Paso 2:

En el cuadro de diálogo, selecciona el tipo de gráfico deseado y haz clic en Aceptar. 

Ilustración 62: Paso 2 en la creación de gráficos dinámico

Paso 3:

El gráfico dinámico aparecerá en la hoja de cálculo. Si quieres, puede moverlo haciendo clic y arrastrando.

Ilustración 63: Paso 3 en la creación de gráficos dinámicos.

(“Microsoft Excel 2010 - ¿Qué son y cómo crear gráficos dinámicos?”, s/f)

Aquí podrás encontrar un video sobre gráficas de Excel: https://www.youtube.com/watch?v=XQw6D5xMcTA

Ilustración 61: Paso 1 en la creación de gráficos dinámicos.

Page 74: Unidad 5 excel

70

Page 75: Unidad 5 excel

71

FUENTE: 1.2. Entorno de trabajo de Excel. (2014, octubre 22). Recuperado a partir de

http://www.cursoexcel.com/1-2-entorno-de-trabajo-de-excel/

Alternar entre referencias relativas, absolutas y mixtas - Excel. (s/f). Recuperado el

19 de febrero de 2017, a partir de https://support.office.com/es-es/article/Alternar-

entre-referencias-relativas-absolutas-y-mixtas-dfec08cd-ae65-4f56-839e-

5f0d8d0baca9

Crear gráficos estadísticos. excel 2010. (s/f). Recuperado el 19 de febrero de 2017, a

partir de http://www.mailxmail.com/crear-graficos-estadisticos-excel-2010_h

Crear o eliminar una lista personalizada para ordenar y rellenar datos - Excel. (s/f).

Recuperado el 18 de febrero de 2017, a partir de

https://support.office.com/es-es/article/Crear-o-eliminar-una-lista-personalizada-

para-ordenar-y-rellenar-datos-d1cf624f-2d2b-44fa-814b-ba213ec2fd61

Crear un gráfico de principio a fin - Excel. (s/f). Recuperado el 19 de febrero de

2017, a partir de https://support.office.com/es-es/article/Crear-un-gr%C3%A1fico-

de-principio-a-fin-0baf399e-dd61-4e18-8a73-b3fd5d5680c2

Errores de Excel - Error en Excel. (s/f). Recuperado el 19 de febrero de 2017, a

partir de http://www.queesexcel.net/errores-de-excel.html

Funciones de Excel. (s/f). Recuperado el 19 de febrero de 2017, a partir de

http://hojadecalculo.umh.es/conceptos_basicos/funciones.htm

Información general sobre fórmulas en Excel - Excel. (s/f). Recuperado el 19 de

febrero de 2017, a partir de https://support.office.com/es-es/article/Informaci

Page 76: Unidad 5 excel

72

%C3%B3n-general-sobre-f%C3%B3rmulas-en-Excel-ecfdc708-9162-49e8-b993-

c311f47ca173

Microsoft Excel 2010 - Formatos condicionales predeterminados. (s/f). Recuperado

el 18 de febrero de 2017, a partir de

https://www.gcfaprendelibre.org/tecnologia/curso/microsoft_excel_2010/

agregar_reglas_condicionales_a_los_datos/2.do

Microsoft Excel 2010 - ¿Qué son y cómo crear gráficos dinámicos? (s/f).

Recuperado el 19 de febrero de 2017, a partir de

https://www.gcfaprendelibre.org/tecnologia/curso/microsoft_excel_2010/

trabajar_con_tablas_y_graficos_dinamicos/7.do

nlm48. (2010, diciembre 2). Tipos de datos. Recuperado a partir de

https://nlmexcel.wordpress.com/2010/12/02/tipos-de-datos/

Ortíz, M. (2011, noviembre 10). Editar el contenido de una celda. Recuperado a

partir de https://exceltotal.com/editar-el-contenido-de-una-celda/

Ortíz, M. (2012a, enero 26). La función BUSCARV en Excel. Recuperado a partir

de https://exceltotal.com/la-funcion-buscarv-en-excel/

Ortíz, M. (2012b, febrero 29). La función SI en Excel. Recuperado a partir de

https://exceltotal.com/la-funcion-si-en-excel/

Ortíz, M. (2012c, marzo 2). La función O en Excel. Recuperado a partir de

https://exceltotal.com/la-funcion-o-en-excel/

Ortíz, M. (2013a, enero 17). Tutorial Excel: Tipos de operadores. Recuperado a

partir de https://exceltotal.com/tutorial-excel-2010-tipos-de-operadores-en-excel/

Ortíz, M. (2013b, febrero 6). Tutorial Excel: Función SI anidada. Recuperado a

partir de https://exceltotal.com/tutorial-excel-2010-funcion-si-anidada/

Page 77: Unidad 5 excel

73

Ortíz, M. (2013c, febrero 7). Tutorial Excel: Función Y. Recuperado a partir de

https://exceltotal.com/tutorial-excel-2010-funcion-y/

Ortíz, M. (2013d, marzo 2). Ordenar y filtrar datos en Excel 2013. Recuperado a

partir de https://exceltotal.com/ordenar-y-filtrar-datos-en-excel-2013/

Ortíz, M. (2015, abril 27). Validación de datos en Excel. Recuperado a partir de

https://exceltotal.com/validacion-de-datos-en-excel/

Proteger una hoja de cálculo - Excel. (s/f). Recuperado el 18 de febrero de 2017, a

partir de https://support.office.com/es-es/article/Proteger-una-hoja-de-c

%C3%A1lculo-3179efdb-1285-4d49-a9c3-f4ca36276de6

¿Qué es y para qué sirve Excel? (2012, diciembre 13). Recuperado a partir de

http://www.accessyexcel.com/que-es-y-para-que-sirve-excel/

Rellenar datos automáticamente en celdas de hojas de cálculo - Excel. (s/f).

Recuperado el 18 de febrero de 2017, a partir de

https://support.office.com/es-es/article/Rellenar-datos-autom%C3%A1ticamente-

en-celdas-de-hojas-de-c%C3%A1lculo-74e31bdd-d993-45da-aa82-35a236c5b5db

SINTAXIS DE LAS FUNCIONES EN EXCEL. (2013, mayo 4). Recuperado a

partir de https://nestordavidbarrera.wordpress.com/2013/05/04/sintaxis-de-las-

funciones-en-excel/