Curso Excel Intermedio.pdf

CURSO DE EXCEL INTERMEDIO Contenido CURSO DE EXCEL INTERMEDIO .................................................................................................................................1 1.

Recorrido por la interfaz de EXCEL 2013 y el área de trabajo ......................................................................5 La Barra de Estado ............................................................................................................................................6 Barra de herramientas de acceso rápido .........................................................................................................7

2.

Uso de Teclas Rápidas y Procedimientos Simplificados ............................................................................ 11 Atajos de una sola tecla ................................................................................................................................. 11 Atajos de teclado básicos en Excel ................................................................................................................ 11 Atajos con Ctrl y teclas de función ................................................................................................................ 12 Atajos con Mayús y teclas de función ........................................................................................................... 12 Atajos con Alt y teclas de función.................................................................................................................. 12 Atajos con las teclas Ctrl+Mayús ................................................................................................................... 12 Atajos de teclado para moverse en Excel ...................................................................................................... 13 Atajos de teclado para seleccionar datos ...................................................................................................... 13 Atajos para ingresar datos y fórmulas ........................................................................................................... 13 Otros atajos de teclado en Excel ................................................................................................................... 13 Otras funciones abreviadas ........................................................................................................................... 14 Insertar Celdas ............................................................................................................................................... 15

3.

Uso de Buscar y Reemplazar valores o textos ........................................................................................... 17 Opciones de Buscar: ...................................................................................................................................... 18 Buscar en Excel (Ctrl + B) ............................................................................................................................... 18 Buscar por tipo de texto o formato de celda ................................................................................................ 19 Elegir formato de celdas desde otra celda .................................................................................................... 20

4.

Uso del Cuadro de Nombres, nombres a celdas y a rangos de celdas ...................................................... 21 Tipos de nombres en Excel ............................................................................................................................ 21 Cómo crear un nombre en Excel ................................................................................................................... 21 El administrador de nombres en Excel .......................................................................................................... 22 Ámbito de un nombre ................................................................................................................................... 22 Cómo auditar nombres .................................................................................................................................. 23

5.

Formas de Relleno de datos (series, Fechas, etc.)..................................................................................... 24 Usar el controlador de relleno para rellenar datos ....................................................................................... 24 Usar Opciones de autorrelleno para cambiar cómo se rellena la selección ................................................. 24 Rellenar una serie de números, fechas u otros elementos de serie integrados ........................................... 24

6.

Formatos de celdas, (Número, Contabilidad, Personalizada, Fechas, etc.) .............................................. 26 Formatos en celdas:....................................................................................................................................... 26 Teclas rápidas para formato de celdas .......................................................................................................... 26 Formatos personalizados para Fechas .......................................................................................................... 27 Diferencia entre los formatos de moneda y de contabilidad ........................................................................ 27 Aplicar un estilo de celda............................................................................................................................... 28 Cambiar la fuente .......................................................................................................................................... 29 Alineación de celdas ...................................................................................................................................... 30 Relleno de una celda ..................................................................................................................................... 31 Bordes de una celda ...................................................................................................................................... 31 Combinación de celdas .................................................................................................................................. 32 Formato Contabilidad, Millares, Porcentaje y Decimales: ............................................................................ 32 Estilos de celda .............................................................................................................................................. 33

7.

Aplicación de la herramienta “Pegado Especial” (Transponer, Valores, Comentarios, Operaciones) ...... 34 Opciones de pegado en Excel ........................................................................................................................ 35 Transponer .................................................................................................................................................... 35 Sugerencias para transponer los datos ......................................................................................................... 36

8.

Referencias de Celdas ................................................................................................................................ 37 Referencias Relativas, Mixtas y Absolutas..................................................................................................... 37 Referencia a una celda en otra hoja de Excel ................................................................................................ 37 Referencia a un rango en otra hoja de Excel ................................................................................................. 38 Nombres de hojas con espacios en blanco ................................................................................................... 39

9.

Áreas de impresión .................................................................................................................................... 40 Configurar Página .......................................................................................................................................... 41

10.

Autoajuste de Celdas ............................................................................................................................. 46

Ancho de las columnas .................................................................................................................................. 46 Altura de las filas ........................................................................................................................................... 46 Cambiar ancho y alto de columnas y filas ..................................................................................................... 47 11.

Inmovilizar Paneles ................................................................................................................................ 48

Inmovilizar o dividir filas y columnas............................................................................................................. 48 Paneles de división ........................................................................................................................................ 49 Anular la división de paneles ......................................................................................................................... 49 Inmovilizar paneles para bloquear filas o columnas específicas ................................................................... 50 Movilizar paneles ........................................................................................................................................... 50 12.

Filtros (Color, valores, de texto, número y fecha, etc.) ......................................................................... 51

Filtrar Texto ................................................................................................................................................... 51

Filtrar Números.............................................................................................................................................. 54 Filtrar por Fechas ........................................................................................................................................... 54 Borrar un filtro ............................................................................................................................................... 56 Borrar un filtro para una columna ................................................................................................................. 56 Uso de Agregar la selección actual al Filtro ................................................................................................... 56 Borrar todos los filtros en una hoja de cálculo y volver a mostrar todas las filas ......................................... 57 13.

Uso de la herramienta Texto en columnas............................................................................................ 58

Quitar Duplicados .......................................................................................................................................... 58 Texto en Columnas ........................................................................................................................................ 58 14.

Formatos Condicionales (Barras de Datos, Escalas de color, conjunto de íconos, rangos) .................. 61

Formato condicional en números ................................................................................................................. 61 Formato condicional en texto ....................................................................................................................... 63 Formato condicional en fechas ..................................................................................................................... 64 Resaltar duplicados con Formato condicional............................................................................................... 64 Crear reglas de formato condicional con fórmula ......................................................................................... 66 Barras de datos, escalas de color y conjuntos de iconos para resaltar datos ............................................... 67 15.

Validación de celdas, parametrización de mensajes de entrada y de error ......................................... 71

Importancia de la validación de datos en Excel ............................................................................................ 71 El comando Validación de datos en Excel ..................................................................................................... 72 Cómo aplicar la validación de datos .............................................................................................................. 72 La opción Omitir blancos ............................................................................................................................... 73 Crear validación de datos en Excel ................................................................................................................ 74 Lista de validación de datos........................................................................................................................... 75 Lista de validación con datos de otra hoja .................................................................................................... 77 Personalizar el mensaje de error ................................................................................................................... 78 Cómo eliminar la validación de datos ........................................................................................................... 79 16.

Funciones de Texto (Izquierda, Derecha, Extrae, Nompropio, Igual, Espacios, Concatenar ................. 81

17.

Funciones Matemáticas (Suma, Sumar.si, Subtotales y Otras) ............................................................. 82

18.

Funciones Lógicas (Función SI Condicional, SI.ERROR).......................................................................... 85

19.

Funciones de Búsqueda (BuscarV, BuscarH) ......................................................................................... 87

La función BUSCARV ...................................................................................................................................... 87 La función BUSCARH ...................................................................................................................................... 88 20.

Tablas para organizar datos y Ordenamientos ...................................................................................... 90

Diferencias entre rangos y tablas de Excel .................................................................................................... 90 Crear una tabla en Excel con estilo................................................................................................................ 91 Resaltar filas alternas de una tabla ............................................................................................................... 92

El nombre de una tabla en Excel ................................................................................................................... 92 Referencias estructuradas ............................................................................................................................. 95 Especificadores de elementos especiales...................................................................................................... 96 Eliminar tabla sin afectar datos ..................................................................................................................... 96 21.

Funciones básicas para la confección de tablas dinámicas ................................................................... 97

Partes de una tabla dinámica en Excel .......................................................................................................... 99 Lista de Campos ............................................................................................................................................. 99 Dar formato a una tabla dinámica ............................................................................................................... 101 Formato de valores en una tabla dinámica ................................................................................................. 103 Segmentación de datos en tablas dinámicas .............................................................................................. 105 Escala de Tiempo ......................................................................................................................................... 107 Modificar el tipo de cálculo de una tabla dinámica .................................................................................... 108 Cambiar origen de datos de una tabla dinámica ......................................................................................... 110 22.

Gráficos básicos y de dos ejes ............................................................................................................. 111

Tipos de gráficos .......................................................................................................................................... 111 Selección de datos para crear un gráfico .................................................................................................... 113 Pasos para crear un gráfico en Excel ........................................................................................................... 115 Gráficos recomendados en Excel 2013........................................................................................................ 116 Cómo seleccionar un elemento de gráfico .................................................................................................. 117 Controles para modificar los elementos de un gráfico ............................................................................... 118 Modificar el área del gráfico ........................................................................................................................ 120 Modificar el área de trazado ....................................................................................................................... 121 Modificar el título del gráfico ...................................................................................................................... 121 Modificar la leyenda del gráfico .................................................................................................................. 122 Modificar las líneas de cuadrícula ............................................................................................................... 122 Líneas de tendencia en Excel ....................................................................................................................... 123 23.

Proteger hojas y libros ......................................................................................................................... 126

Proteger un libro.......................................................................................................................................... 126 Otras opciones de protección ..................................................................................................................... 126

1. Recorrido por la interfaz de EXCEL 2013 y el área de trabajo •

Fichas o pestañas: Está formada por un conjunto de grupos.

•

Grupo: Tenemos el grupo Portapapeles, Fuente, Alineación, Número, etc.

•

Botones de herramientas: Cada grupo está formada por un conjunto de botones de herramientas. Una herramienta es un comando representada gráficamente.

•

Botón: Nos permite activar el cuadro de dialogo correspondiente.

La Barra de Fórmulas nos muestra la(s) fórmula usada en una celda del área de trabajo (o por defecto, el valor sin procesar).

El área de trabajo de hoja electrónica • Está conformada por filas (letras), columnas (números) y celdas (intersecciones de ambas). o En ella se trabajan y manipulan los datos mencionados antes. o El área de trabajo está incluido dentro de una hoja de cálculo única. • Cada Libro puedo albergar como máximo hasta... o Hojas de cálculo ilimitadas (depende del tamaño de la memoria RAM de la computadora en la que se trabaje) o 1,048,576 filas o 16,384 columnas

La Barra de Estado Es una barra ubicada en la parte final del entorno de trabajo. Puede ser usada -opciones por defecto- para elegir el tipo de vista de la hoja de cálculo en la que se trabaja actualmente, adecuar el nivel de zoom (acercamiento) y para grabar un macro.

Presionando el clic derecho del mouse sobre la Barra de Estado la personalizaremos a nuestro gusto

Barra de herramientas de acceso rápido La barra de herramientas de acceso rápido en Excel 2013 es el lugar donde podemos colocar los comandos que más utilizamos para tener acceso a ellos en cualquier momento con un solo clic. Esta barra está localizada por arriba de la cinta de opciones en la esquina superior izquierda de la ventana. Comandos predeterminados De manera predeterminada, la barra de herramientas de acceso rápido muestra el comando Guardar y los comandos Deshacer y Rehacer. Es probable que estos dos últimos botones se muestren inactivos si es que aún no has ejecutado alguna acción en la hoja de Excel.

Personalizar la barra de herramientas de acceso rápido Es posible agregar comandos adicionales a la barra de herramientas de acceso rápido haciendo clic sobre el menú desplegable que se encuentra en el extremo derecho de la barra lo cual mostrará una lista de comandos que podemos agregar inmediatamente:

Puedes notar que los comandos Guardar, Deshacer y Rehacer tienen una marca justo al lado de su nombre lo cual indica que son los comandos mostrados actualmente dentro de la barra de herramientas de acceso rápido. Puedes seleccionar cualquiera de los 9 comandos adicionales que se muestran para agregarlos inmediatamente a la barra. Los comandos que se pueden agregar desde este menú son los siguientes: • • • • • • • •

Nuevo: Abre un nuevo libro. Abrir: Muestra el panel Abrir que nos permite abrir un libro existente. Enviar por correo electrónico: Abre el cliente predeterminado de correo electrónico en tu equipo para poder enviar el libro abierto. Impresión rápida: Envía el libro actual a la impresora predeterminada de tu equipo. Vista previa de impresión e Imprimir: Abre el panel Imprimir el cual contiene la vista previa del libro y nos permite enviar a imprimir el libro de Excel. Ortografía: Revisa los textos del libro actual en busca de algún error ortográfico. Orden ascendente: Ordena la selección de celdas actual o la columna actual de manera ascendente. Modo mouse/torque: Cambia entre modo mouse y torque (Touch) que es útil para dispositivos móviles ya que hace más grandes los botones de comando en la cinta de opciones para facilitar su uso en pantallas táctiles.

Abrir archivos: libros recientes y buscar en equipo:

Descripción de elementos de pantalla:

1.- Botón de control.

15.- Botón de insertar función.

2.- Barra de herramientas de acceso rápido.

16.- Cuadro de contenido de celda.

3.- Barra de título.

17.- Columnas.

4.- Botón de ayuda.

18.- Filas.

5.- Botón de opciones de la cinta de opciones

19.- Celda activa.

6.- Botón de minimizar.

20.- Barra de navegación de hojas.

7.- Botón de restaurar.

21- Hoja activa.

8.- Botón de cerrar.

22.- Agregar nueva hoja.

9.- Botón opción archivo.

23.- Barra de desplazamiento horizontal.

10.- Barra de menús principal.

24.- Barra de desplazamiento vertical.

11.- Iniciar sesión

25.- Barra de estado.

12.- Cinta de opciones.

26.- Modos de presentación de la hoja de cálculo

13.- Cuadro de nombres.

27.- Zoom.

14.- Barra de fórmulas.

2. Uso de Teclas Rápidas y Procedimientos Simplificados • • • • •

F1 Muestra la ayuda de Excel F2 Entra en modo de edición para la celda activa F10 Activa la barra de menús. F11 Crea una hoja de gráfico con el rango de celdas seleccionado. F12 Muestra el cuadro de diálogo Guardar como.

Atajos de una sola tecla • • • • • • • • • • • •

Alt Activa la barra de menús. Avpág Desplazarse una pantalla abajo dentro de la hoja (25 filas). Entrar Completa la entrada de una celda y selecciona la celda inferior. Esc Cancela la entrada de una celda. También cierra cualquier cuadro de diálogo mostrado. Espacio Activa o desactiva una casilla de verificación dentro de un cuadro de diálogo. Fin Activa o desactiva el Modo final. Al estar en Modo final se pueden utilizar las teclas de dirección para moverse hacia la última celda del rango actual. Inicio Moverse al inicio de la fila. Repág Desplazarse una pantalla arriba dentro de la hoja. Retroceso Elimina el contenido de una celda y entra en Modo de edición. Suprimir Elimina el contenido de una celda Tabulador Completa la entrada de una celda y selecciona la celda a la derecha. Teclas de dirección Selecciona la celda superior, inferior, izquierda o derecha de acuerdo a la tecla de dirección pulsada.

Atajos de teclado básicos en Excel • • • • • • • • • • • • • • • • • • • • •

Ctrl+A Muestra el cuadro de diálogo Abrir. Ctrl+B Muestra el cuadro de diálogo Buscar. Ctrl+C Copia las celdas seleccionadas. Ctrl+D Copia una fórmula hacia la derecha sobre el rango seleccionado. Ctrl+G Guarda el libro de trabajo. Ctrl+I Muestra el cuadro de diálogo Ir a. Ctrl+J Copia una fórmula hacia abajo sobre el rango seleccionado. Ctrl+K Aplica formato de cursiva al texto seleccionado. Ctrl+L Muestra el cuadro de diálogo Reemplazar. Ctrl+N Aplica formato de negrita al texto seleccionado. Ctrl+P Muestra el cuadro de diálogo Imprimir. Ctrl+R Cierra el libro de trabajo. Ctrl+S Subraya el texto seleccionado. Ctrl+T Muestra el cuadro de diálogo Crear tabla. Ctrl+U Nuevo libro de trabajo. Ctrl+V Pega el contenido del portapapeles. Ctrl+X Corta las celdas seleccionadas. Ctrl+Y Rehace la última acción deshecha. Ctrl+Z Deshace la última acción. Ctrl+1 Muestra el cuadro de diálogo Formato de celdas. Ctrl+2 Aplica formato de negrita al texto seleccionado.

• • • • •

Ctrl+3 Aplica formato de cursiva al texto seleccionado. Ctrl+4 Subraya el texto seleccionado. Ctrl+5 Aplica el efecto de tachado al texto. Ctrl+9 Oculta las filas seleccionadas. Ctrl+0 Oculta las columnas seleccionadas.

Atajos con Ctrl y teclas de función • • • • • •

Ctrl+F1 Oculta o muestra la Cinta de opciones. Ctrl+F2 Muestra el cuadro de diálogo Imprimir. Ctrl+F3 Muestra el Administrador de nombres. Ctrl+F4 Cierra la ventana del libro actual. Ctrl+F6 Moverse al libro abierto siguiente. Ctrl+F12 Muestra el cuadro de diálogo Abrir.

Atajos con Mayús y teclas de función • • • • • • •

Mayús+F2 Agrega o edita un comentario de celda. Mayús+F3 Muestra el cuadro de diálogo Insertar función. Al editar una fórmula muestra el cuadro de diálogo Argumentos de función. Mayús+F4 Ejecuta el comando “Buscar siguiente” de acuerdo a los términos de búsqueda indicados previamente. Mayús+F5 Muestra el cuadro de diálogo Buscar. Mayús+F10 Muestra el menú de clic derecho para la selección. Mayús+F11 Inserta una nueva hoja. Mayús+F12 Muestra el cuadro de diálogo Guardar como.

Atajos con Alt y teclas de función • • •

Alt+F1 Inserta un gráfico en la hoja actual. Alt+F2 Muestra el cuadro de diálogo Guardar como. Alt+F4 Cierra Excel.

Atajos con las teclas Ctrl+Mayús • • • • • • • • • • •

Ctrl+Mayús+F3 Muestra el cuadro de diálogo Crear nombres a partir de la selección. Ctrl+Mayús+F6 Moverse al libro abierto anterior. Ctrl+Mayús+F10 Activa la barra de menú. Ctrl+Mayús+F12 Muestra el cuadro de diálogo Imprimir. Ctrl+Mayús+F Muestra la pestaña Fuente del cuadro de diálogo Formato de celdas. Ctrl+Mayús+L Activa o desactiva los filtros en un rango. Ctrl+Mayús+O Selecciona las celdas con comentarios. Ctrl+Mayús+U Expande la barra de fórmulas. Ctrl+Mayús+Inicio Extiende la selección hasta el inicio de la hoja. Ctrl+Mayús+Fin Extiende la selección hasta la última celda utilizada en la hoja.

• •

Ctrl+Mayús+Espacio Selecciona el rango de celdas actual o la hoja completa. Ctrl+Mayús+( Muestra las filas ocultas dentro del rango seleccionado.

Ctrl+Mayús+Tecla dirección Extiende la selección a la última celda no vacía en la misma dirección de la tecla pulsada.

• • • • • • • • • •

Ctrl+Mayús+) Muestra las columnas ocultas dentro del rango seleccionado. Ctrl+Mayús+1 Aplica el formato Número con dos decimales. Ctrl+Mayús+3 Aplica el formato Fecha en la forma dd-mmm-aa. Ctrl+Mayús+4 Aplica el formato Moneda con dos decimales. Ctrl+Mayús+5 Aplica el formato Porcentaje sin decimales. Ctrl+Mayús+7 Aplica el formato de notación Científica. Ctrl+Mayús+: Aplica el formato de Hora. Ctrl+Mayús+6 Aplica un borde a la celda. Ctrl+Mayús+- Remueve los bordes de la celda. Ctrl+Máyus+2 (doble comilla) Copia el contenido de la celda superior.

Atajos de teclado para moverse en Excel • • • • • • • • •

Alt+Avpág Moverse una pantalla a la derecha en la hoja. Alt+Repág Moverse una pantalla a la izquierda en la hoja. Ctrl+. Moverse a la siguiente esquina de un rango seleccionado. Ctrl+Avpág Moverse a la hoja siguiente. Ctrl+Repág Moverse a la hoja anterior. Ctrl+Inicio Moverse a la celda A1 o a la celda superior izquierda visible en la hoja. Ctrl+Fin Moverse a la última celda utilizada del rango actual. Ctrl+Tabulador Moverse al siguiente libro abierto. Ctrl+Tecla dirección Moverse al extremo de la fila o columna actual de acuerdo a la tecla de dirección pulsada.

Atajos de teclado para seleccionar datos • • • • • •

Ctrl+Espacio Selecciona la columna actual. Mayús+Avpág Extiende la selección hacia abajo por una pantalla. Mayús+Repág Extiende la selección hacia arriba por una pantalla. Mayús+Inicio Extiende la selección hasta el inicio de la fila. Mayús+Espacio Selecciona la fila actual. Mayús+Tecla dirección Extiende la selección una celda en la misma dirección de la tecla pulsada.

Atajos para ingresar datos y fórmulas • • • •

Alt+Entrar Inserta un salto de línea dentro de una celda. Ctrl+, (coma) Insertar la fecha actual. Ctrl+: Insertar la hora actual. Ctrl+Alt+K Insertar un hipervínculo.

Otros atajos de teclado en Excel • • •

Ctrl+- Muestra el cuadro de diálogo Eliminar celdas. Ctrl++ Muestra el cuadro de diálogo Insertar celdas. Ctrl+Alt+V Muestra el cuadro de diálogo Pegado especial.

Otras funciones abreviadas

Insertar Celdas

Formato con la cinta de opciones:

3. Escribir el mismo valor en varias celdas: • Selecciona todo el rango en donde quieres escribir el mismo valor, arrastrando el cursor o usando la tecla CTRL si no están adyacentes. • Escribe el valor. • Presiona las teclas

3. Uso de Buscar y Reemplazar valores o textos

Opciones de Buscar:

Buscar en Excel (Ctrl + B) Comúnmente las funciones más usadas de la búsqueda en Excel son:

1. Buscar texto dentro de Hoja o Libro. 2. Buscar texto en fórmulas, valores o comentarios. 3. Coincidir el texto buscado por mayúsculas o minúsculas. 4. Coincidir el texto buscado con el contenido de las celdas.

•

Buscar Todos despliega una ventana con todas las referencias a celdas o comentarios que coinciden con la búsqueda.

• •

Al seleccionar en cada resultado se posiciona en la celda que coincide con la búsqueda Buscar Siguiente permite posicionar en cada uno de las coincidencias de forma secuencial cada vez que se presiona.

•

Reemplazar Todos reemplaza todas las coincidencias del campo Buscar por el valor definido en el Campo Reemplazar con. Reemplazar efectúa la misma función que Reemplazar Todos excepto que el usuario debe aceptar cada uno de los posibles cambios encontrados uno a uno.

•

Buscar por tipo de texto o formato de celda La búsqueda por tipo de texto facilita el poder encontrar y seleccionar todas las celdas con determinado tipo de texto. Para eso vamos a presionar el botón Formato… donde mostrará el cuadro de diálogo Formato de celdas y podemos elegir todas las combinaciones posibles dentro del cuadro de diálogo. En la siguiente imagen vemos cómo deseamos buscar las celdas que contengan formato de moneda.

Elegir formato de celdas desde otra celda También el cuadro de diálogo Buscar nos da la oportunidad de poder buscar por formato de celda pudiendo elegir otra celdas como referencia.

Finalmente para restablecer la búsqueda y continuar buscando por texto, sólo hay que presionar el botón Formato y elegir Borrar formato de búsqueda.

4. Uso del Cuadro de Nombres, nombres a celdas y a rangos de celdas Si utiliza nombres, sus fórmulas serán mucho más fáciles de entender y mantener. Puede definir un nombre para un rango de celdas, una función, una constante o una tabla. Una vez que haya adoptado la práctica de utilizar nombres en su libro, podrá actualizar, auditar y administrar esos nombres con facilidad.

Tipos de nombres en Excel Existen los nombres definidos para celdas o rangos de celdas y que pueden contener tanto constantes como fórmulas. Es decir, una celda que contiene una constante puede tener asignado un nombre así como una celda que contenga una fórmula puede también tener asignado un nombre. El otro tipo de nombres que existen en Excel son los nombres de tablas. Cuando creamos una tabla, Excel le asigna un nombre predeterminado como Tabla1, Tabla2, etc., pero podremos editarlo desde el administrador de nombres para que sea más descriptivo.

Cómo crear un nombre en Excel Para crear un nombre debemos seleccionar primero la celda (o rango de celdas) y posteriormente podemos utilizar dos métodos. El primero es asignar el nombre directamente en el cuadro de nombres de la barra de fórmulas:

El segundo método es utilizar el comando Asignar nombre que se encuentra en la ficha Fórmulas.

El administrador de nombres en Excel Una vez que se han creado los nombres, ya sea de celdas o de tablas, podremos administrarlos con el Administrador de nombres que se encuentra dentro del grupo Nombres definidos de la ficha Fórmulas. Al pulsar el botón Administrador de nombres se mostrará el siguiente cuadro de diálogo:

Este cuadro de diálogo contendrá la lista de todos los nombres de nuestro libro y podremos también crear desde aquí nuevos nombres o editar los existentes. Así mismo podemos eliminar cualquier nombre que deseemos pulsando el botón Eliminar.

Ámbito de un nombre Todos los nombres tienen un ámbito, ya sea una hoja de cálculo concreta (lo que también se denomina nivel de hoja de cálculo local) o el libro completo (también denominado nivel de libro global). El ámbito de un nombre es la ubicación dentro de la cual el nombre es reconocido sin cualificación. Por ejemplo: Si ha definido un nombre, como Presupuesto_AÑO08, y su ámbito es Hoja1, ese nombre, si no está cualificado, sólo se reconoce en Hoja1, pero no en otras hojas mientras no cuente con cualificación. Para utilizar un nombre de hoja de cálculo local en otra hoja de cálculo, puede cualificarlo si lo precede del nombre de la hoja de cálculo, como en el siguiente ejemplo: Hoja1!Presupuesto_AÑO08 Si ha definido un nombre, como Objetivos_Dept_Ventas, y su ámbito es el libro, ese nombre se reconocerá en todas las hojas de cálculo del libro, pero no en otros libros.

Cómo auditar nombres Si llegas a tener una gran cantidad de nombres en tu libro de Excel y prefieres tener una lista de todos ellos será muy fácil generarla. En primer lugar debes posicionarte en la celda donde se insertará el listado y utilizar el comando Pegar nombres que se encuentra dentro del menú desplegable Utilizar en la fórmula que a su vez pertenece al grupo Nombres definidos de la ficha Fórmulas:

Al mostrarse el cuadro de diálogo Pegar nombres deberás pulsar el botón Pegar lista y Excel insertará en la hoja los nombres de los rangos y su definición:

Es importante hacer notar que el comando Pegar nombres no considera los nombres de las tablas.

5. Formas de Relleno de datos (series, Fechas, etc.)

Usar el controlador de relleno para rellenar datos 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.

Usar Opciones de autorrelleno para cambiar cómo se rellena la selección Después de arrastrar el controlador de relleno, aparece el botón Opciones de autorrelleno , que permite seleccionar el modo en que se rellena la selección. Por ejemplo, puede elegir que solo se rellenen los formatos de las celdas si hace clic en Rellenar formatos solo, o bien que solo se rellene el contenido de una celda si hace clic enRellenar sin formato.

Rellenar una serie de números, fechas u otros elementos de serie integrados Con el controlador de relleno puede llenar rápidamente las celdas de un rango con una serie de números o fechas, o con una serie integrada para días, días de la semana, meses o años. Seleccione la primera celda del rango que desea rellenar. Escriba el valor inicial de la serie. Escriba un valor en la siguiente celda para establecer un modelo. Por ejemplo, si desea utilizar la serie 1, 2, 3, 4, 5,..., escriba 1 y 2 en las primeras dos celdas. Si desea usar la serie 2, 4, 6, 8,..., escriba 2 y 4. Si desea que la serie sea 2, 2, 2, 2,..., puede dejar en blanco la segunda celda. Más ejemplos de series que se pueden rellenar Al rellenar una serie, las selecciones se extienden como muestra la tabla siguiente. Esta tabla coloca los elementos separados por comas en celdas adyacentes individuales de la hoja de cálculo.

Selección inicial

Serie extendida

1, 2, 3

4, 5, 6...

9:00

10:00, 11:00, 12:00,...

Lun

El mar, el mié, Jue...

Lunes

Martes, el miércoles, el jueves...

Ene

Feb, Mar, Abr...

ene., abr.

Jul, Oct, Ene...

ene-07, abr-07

jul-07, oct-07, ene-08,...

15-ene, 15-abr

15-jul, 15-oct,...

2007, 2008

2009, 2010, 2011,...

1-ene, 1-mar

1-may, 1-jul, 1-sep,...

Trim3 (o T3 o Trimestre3)

Trim4, Trim1, Trim2...

texto1, textoA

texto2, textoA, Texto3, textoA...

Período 1

2do período, 3er período...

Producto 1

Producto 2, producto 3...

También puede especificar el tipo de serie si usa el botón derecho del mouse para arrastrar el controlador de relleno sobre el rango y, después, hace clic en el comando correspondiente del menú contextual. Por ejemplo, si el valor inicial es la fecha ENE-2007, haga clic en Rellenar meses para la obtener la serie FEB-2007, MAR-2007, etc. También puede hacer clic en Rellenar años para obtener la serie ENE-2007, ENE-2008, etc.

6. Formatos de celdas, (Número, Contabilidad, Personalizada, Fechas, etc.) Formatos en celdas:

Moneda: Formato numérico con separados de miles, decimales y con signo de moneda

Teclas rápidas para formato de celdas • • • • • • • • • • •

CTRL+MAYÚS+~ Aplica el formato de número General. Ctrl+Mayús+1 Aplica el formato Número con dos decimales. Ctrl+Mayús+3 Aplica el formato Fecha en la forma dd-mmm-aa. Ctrl+Mayús+4 Aplica el formato Moneda con dos decimales. Ctrl+Mayús+5 Aplica el formato Porcentaje sin decimales. Ctrl+Mayús+: Aplica el formato de Hora. CTRL+1 Muestra el cuadro de diálogo Formato de celdas. CTRL+2 Aplica o quita el formato de negrita. CTRL+3 Aplica o quita el formato de cursiva. CTRL+4 Aplica o quita el formato de subrayado. CTRL+5 Aplica o quita el formato de tachado.

Formatos personalizados para Fechas Para mostrar

Use este código

Los meses como 1-12

m

Los meses como 01-12

mm

Los meses como ene-dic

mmm

Los meses como enero-diciembre

mmmm

Los meses como la inicial de cada mes

mmmmm

Los días como 1-31

d

Los días como 01-31

dd

Los días como dom-sáb

ddd

Los días como domingo-sábado

dddd

Los años como 00-99

aa

Los años como 1900-9999

aaaa

Diferencia entre los formatos de moneda y de contabilidad Tanto el formato de moneda como de contabilidad se usan para mostrar valores monetarios. La diferencia entre ambos se explica en la tabla siguiente. Formato Descripción

Cuando se aplica el formato de moneda a un número, el símbolo de moneda aparece junto al primer dígito de la celda. Se puede especificar el número de posiciones decimales que se va a usar, el uso de un separador de miles y el modo en que se muestran los números negativos. Moneda Sugerencia: Para aplicar rápidamente el formato de moneda, seleccione la celda o el rango de celdas a las que desee aplicar el formato y después presione CTRL+MAYÚS+$.

Ejemplo

Formato

Descripción Ejemplo Al igual que el formato de moneda, el formato de contabilidad se usa para valores monetarios. No obstante, este formato alinea los símbolos de moneda y las posiciones decimales de los números en una columna. Además, el formato de contabilidad muestra los ceros como guiones y los números negativos entre paréntesis. Al igual que el formato de moneda, se puede especificar la cantidad de posiciones decimales que se desean usar y si se debe usar un separador de miles. No obstante, no se puede Contabilidad cambiar la presentación predeterminada de los números negativos a menos que se cree un formato de número personalizado. Sugerencia: Para aplicar rápidamente el formato Contabilidad, seleccione la celda o el rango de celdas a las que desee aplicar el formato. En la pestaña Inicio, en el grupo Número, haga clic en Formato de número de contabilidad

y después seleccione otro símbolo de moneda.

Aplicar un estilo de celda El formato de celdas puede ayudar a tener una mejor visualización y entendimiento de los datos presentados. Las herramientas de formato en Excel 2013 las podemos encontrar en tres ubicaciones: 1. En la Cinta de opciones.

2. En la mini barra que se muestra al hacer clic derecho sobre una celda

3. En el cuadro de diálogo Formato de celdas.

Cambiar la fuente De manera predeterminada, Excel 2013 utiliza el tipo de fuente Calibri de tamaño 11. Nosotros podemos utilizar una fuente y tamaño diferentes para resaltar un texto o el encabezado de alguna tabla.

Este cuadro de diálogo nos ofrece algunas configuraciones adicionales como el estilo de la fuente, su color o tipo de subrayado. Algunos atajos de comando que podemos utilizar en este sentido son los siguientes: Negrita (Ctrl + N), Cursiva (Ctrl + K), Subrayado (Ctrl + S). Para cambiar el color de la fuente tenemos el comando respectivo tanto en la Cinta de opciones como en la mini barra:

Alineación de celdas De manera predeterminada Excel alinea los valores numéricos a la derecha y el texto a la izquierda pero es posible modificar esta alineación utilizando los comandos de la ficha Inicio > Alineación.

1. Alineación vertical: Con estos tres botones podemos alinear el contenido de una celda verticalmente ya sea en la parte superior, inferior o media de la celda. 2. Alineación horizontal: Alinea el contenido de la celda a la izquierda, derecha o al centro de la misma. 3. Orientación: Nos da la oportunidad de girar el texto a un ángulo específico lo cual es de utilidad cuando necesitamos cambiar la orientación del título de una columna muy estrecha. 4. Sangría: Se aumenta o disminuye el espacio entre el borde de la celda y su contenido. 5. Ajustar texto: Si tienes un texto que no se muestra en su totalidad dentro de una celda, este comando ajustará el texto en varias líneas para mostrar todo el contenido. 6. Combinar y centrar: Combinará las celdas seleccionadas para hacer una sola celda y además centrará el contenido. Al pulsar en la flecha del menú desplegable se mostrarán algunas opciones adicionales como la de volver a separar las celdas. Estos mismos comandos de alineación los podemos encontrar en el cuadro de diálogo Formato de celdas dentro de la sección Alineación.

Relleno de una celda Podemos establecer un relleno para una celda utilizando un color específico o una trama. Para indicar el color de fondo de una celda podemos utilizar el comando Color de relleno que se encuentra en el grupo Fuente de la ficha Inicio.

Este comando también lo puedes encontrar en la mini barra y se mostrará la misma paleta de colores a seleccionar. Por otro lado, si quieres establecer como relleno una trama entonces es necesario abrir el cuadro de diálogo Formato de celdas y en la sección Relleno podrás elegir tanto el estilo de la trama como su color.

Bordes de una celda La cuadrícula que delimita cada una de las celdas de una hoja es solo una ayuda visual ya que dichas líneas no se imprimen de manera predeterminada en cambio los bordes si son impresos y de gran utilidad para agrupar un rango de celdas y distinguirlas de las demás. Existen diferentes estilos y grosores de bordes y podrás seleccionar la configuración deseada desde el comando Bordes que se encuentra en la ficha Inicio > Fuente:

Este comando también está disponibles desde la mini barra. Por otro lado el cuadro de diálogo Formato de celdas tiene una sección llamada Borde que es donde podrás hacer todas las configuraciones necesarias a los bordes de una celda.

Combinación de celdas Al combinar dos o más celdas horizontales o verticales adyacentes, las celdas se convierten en una celda más grande que se muestra en varias columnas o filas. En el ejemplo siguiente, al usar el comando Combinar y centrar, se centra el texto en la celda combinada. Importante: Al combinar varias celdas, el contenido de solo una celda (la celda superior izquierda para idiomas de izquierda a derecha, o la celda superior derecha para idiomas de derecha a izquierda) aparecen en la celda combinada. El contenido de las otras celdas que combine se eliminará. Seleccione las celdas adyacentes que desee combinar. Nota: Asegúrese de que los datos que quiere mostrar en la celda combinada se encuentren en la celda superior izquierda del rango seleccionado. Solo los datos de la celda superior izquierda permanecerán en la celda combinada. Los datos en las otras celdas del rango seleccionado se eliminarán. Copie cualquier otro dato que necesite a otra ubicación de la hoja de cálculo antes de realizar la combinación. En la pestaña Inicio, en el grupo Alineación, haga clic en Combinar y centrar.

Las celdas se combinarán en una fila o columna y su contenido se centrará en la celda combinada. Para combinar celdas sin centrar su contenido, haga clic en la flecha situada junto a Combinar y centrar y, a continuación, seleccione Combinar horizontalmente o Combinar celdas. No se pueden combinar las celdas que están dentro de una tabla de Excel. Las referencias de fórmula en otras celdas se ajustan automáticamente para usar la referencia de celda de la celda combinada. Como la ordenación necesita que todas las celdas que se ordenen tengan el mismo tamaño, no se puede ordenar un rango que contenga una combinación de celdas combinadas y celdas no combinadas.

Formato Contabilidad, Millares, Porcentaje y Decimales: Para mostrar rápidamente formato Contabilidad, el separador de miles y porcentaje se puede hacer clic en el botón correspondiente en el grupo Número de la ficha Inicio. Los botones de decimales aumentan la cantidad de decimales, mientras que el otro reduce

Estilos de celda Excel nos ofrece la posibilidad de elegir un estilo de celda predefinido de manera que no tengamos que hacer cada modificación por nuestra cuenta sino que solamente seleccionamos el estilo de nuestra preferencia desde la ficha Inicio > Estilos > Estilos de celda:

En modo de vista previa refleja un estilo de la celda predeterminado con solo posicionar el mouse sobre alguna de las opciones. Para hacer efectiva la aplicación del estilo debes hacer clic sobre la opción deseada. Si por alguna razón deseas regresar al estilo predeterminado de una celda, entonces debes seleccionar la opción Normal. También puedes notar que al final del menú se muestran las opciones Nuevo estilo de celda la cual nos da la posibilidad de crear un nuevo estilo y tenerlo disponible en todo momento. La opción Combinar estilos nos permite copiar los estilos contenidos en otro libro de Excel.

7. Aplicación de la herramienta “Pegado Especial” (Transponer, Valores, Comentarios, Operaciones) Excel normalmente copia toda la información de una selección de celdas y pega los datos en las celdas especificadas, pero puedes utilizar el Pegado especial para indicar algunas opciones alternas en el pegado de la información. El comando Pegado especial nos permite utilizar opciones diferentes a la manera tradicional de copiar y pegar como por el ejemplo: pegar el contenido de las celdas sin aplicar un formato o solamente pegar el formato de las celdas sin considerar su contenido. Para utilizar estas opciones de pegado debes hacer clic en el menú desplegable del botón Pegar, en la ficha Inicio, y seleccionar la opción Pegado especial.

Se mostrará el cuadro de diálogo Pegado especial donde tendremos varias opciones de pegado disponibles. También puede presionar Ctrl+Alt+V para abrir el cuadro Pegado especial.

Opciones de pegado en Excel Analicemos cada una de las opciones de pegado. Elija esta opción

Para

Método abreviado de teclado

Todo

Pegar todo el contenido y formato de celda.

Presione O

Fórmulas

Pegar solo las fórmulas tal y como se escribieron en la barra Presione la tecla F de fórmulas.

Valores

Pegue solo los valores (no las fórmulas).

Presione R

Formatos

Pegue solo el formato copiado.

Presione M

Comentarios

Pegar solo los comentarios adjuntos a la celda.

Presione C

Validación

Pegue solo la configuración de validación de datos de las celdas copiadas.

Presione V

Todo utilizando el tema de origen

Pegue todo el contenido y formato de las celdas copiadas.

Presione U

Todo excepto bordes

Pegue todo el contenido de la celda sin bordes.

Pulse X

Ancho de las columnas

Pegue solo los anchos de columna de las celdas copiadas.

Presione H

Formatos de números y fórmulas

Pegue solo las fórmulas y los formatos de número de las celdas copiadas.

Presione L

Formatos de números y valores

Pegue solo los valores (no las fórmulas) y los formatos de número de las celdas copiadas.

Presione A.

Todos los formatos condicionales de combinación. Pega todas las reglas de formato condicional en las celdas destino.

Transponer Seleccione el rango de datos que desee reorganizar, incluidas las etiquetas de filas o columnas, y presione Ctrl+C. Nota: Asegúrese de copiar los datos para ello. No funcionará mediante el comando Cortar o Ctrl+X. Haga clic con el botón derecho en la primera celda donde quiera pegar los datos y elija Transponer

.

Elija un lugar en la hoja de cálculo que tenga suficiente espacio para pegar sus datos. Los datos que copie sobrescribirán los datos que ya hubiese allí.

Después de girar los datos correctamente, puede eliminar los datos originales.

Sugerencias para transponer los datos Si los datos incluyen fórmulas, Excel las actualizará automáticamente para que coincidan con la nueva ubicación. Compruebe que estas fórmulas usen referencias absolutas. Si no es así, puede alternar entre referencias relativas, absolutas y mixtas antes de girar los datos. Si los datos están en una tabla de Excel, la característica Transponer no se encontrará disponible. Puede convertir la tabla en un rango en primer lugar o puede usar la función TRANSPONER para girar las filas y columnas. Si desea girar sus datos con frecuencia para verlos desde distintos ángulos, considere la creación de una tabla dinámica para poder dinamizar los datos rápidamente al arrastrar los campos desde el área Filas al área Columnas (o viceversa) en la lista de campos de tabla dinámica.

8. Referencias de Celdas Referencias Relativas, Mixtas y Absolutas

Referencia a una celda en otra hoja de Excel Cuando la información está organizada en diferentes hojas de Excel es indispensable saber cómo crear referencias a celdas que se encuentran en otras hojas. La nomenclatura es muy sencilla, solamente usamos el nombre de la hoja seguido del signo de exclamación y la dirección de la celda. Veamos el siguiente ejemplo. En una de las hojas de mi libro de Excel tengo el presupuesto para los gastos de los meses de enero y febrero.

En la hoja Resumen deseo mostrar el gasto de teléfono del mes de febrero por lo que utilizo la siguiente fórmula: =Presupuesto!C3 Observa que esta referencia sigue las reglas mencionadas anteriormente. En primer lugar coloco el nombre de la hoja que tiene la información, seguida del símbolo de exclamación y finalmente la dirección de la celda que deseo obtener de dicha hoja. Observa que Excel obtiene el valor adecuado en mi hoja Resumen:

Como puedes observar, es muy sencillo hacer referencia a celdas de otras hojas en Excel.

Referencia a un rango en otra hoja de Excel De la misma manera que hemos creado una referencia a una sola celda en otra hoja, podemos crear una referencia a un rango. Utilizando el mismo ejemplo anterior, ahora deseo mostrar en mi hoja Resumen la suma total del presupuesto del mes de Febrero. Para ello utilizaré la función SUMA especificando como argumento un rango en otra hoja: =SUMA(Presupuesto!C2:C5) La función SUMA se encargará de acceder los valores en las celdas de la otra hoja. Observa que Excel devuelve el resultado correcto:

Espero que con estos dos ejemplos tengas ya claro cómo hacer una referencia a una celda o rango en otra hoja de Excel.

Nombres de hojas con espacios en blanco Existe un caso especial al momento de crear una referencia hacia otras hojas de nuestro libro y es cuando la hoja que contiene los datos tiene un nombre con espacios en blanco. En mi libro de Excel de ejemplo tengo una hoja llamada “Gasto Real” con la información del gasto efectuado en cada mes:

Si deseo colocar en mi hoja Resumen la suma de los gastos de febrero de esta hoja debo utilizar la siguiente fórmula: =SUMA('Gasto Real'!C2:C5) Lo que debes notar de manera especial en esta fórmula es que el nombre de la hoja está rodeado por comillas sencillas que es algo que debemos hacer cuando el nombre de la hoja contenga espacios en blanco. Observa el resultado de utilizar esta referencia:

9. Áreas de impresión Después de seleccionar las celdas de Excel que deseas imprimir debes ir a la ficha Diseño de página y seleccionar el comando Área de impresión y posteriormente la opción Establecer área de impresión.

También es posible seleccionar celdas no contiguas para establecerlas como el área de impresión. Solamente debes pulsar la tecla CTRL mientras vas seleccionando los diferentes rangos de celdas. Cuando imprimas la hoja de Excel obtendrás como resultado el área de impresión previamente definida. Si antes de imprimir te has arrepentido y deseas ignorar el área de impresión establecida, entonces debes seleccionar la opción Omitir el área de impresión que se encuentra en el panel de impresión en la Vista Backstage.

Esta selección hará que Excel imprima todo el libro sin importar el área de impresión definida previamente. Para remover por completo el área de impresión debes utilizar el comando Borrar área de impresión.

Configurar Página Antes de imprimir una hoja de cálculo, es conveniente que configuremos la página, para modificar factores que afectan a la presentación de las páginas impresas, como la orientación, encabezados y pies de página, tamaño del papel.

Para ello, nos situaremos en la pestaña Diseño de página.

En la sección Configurar página encontraremos botones rápidos a muchas de las opciones: para configurar márgenes, la orientación del papel, el tamaño, etc. Pero si lo que queremos es acceder a todas las opciones de configuración, deberemos pulsar el pequeño botón de la esquina inferior derecha. Como siempre, se abrirá una nueva ventana. La combinación de teclas rápidas es F10-C-F El cuadro de diálogo Configurar página está organizado en varias pestañas: La primera de las fichas se denomina Página y permite indicar características como la orientación del papel, el tamaño del papel que utilizamos y otros parámetros.

Selecciona la orientación del papel, vertical u horizontal. (En la impresora se colocará el papel siempre de la misma forma). En el recuadro Escala nos permitirá indicarle si deseamos que la salida a impresora venga determinada por un factor de escala (100%, 50%, 200%,...) o bien ajustando automáticamente la hoja en un número de páginas específico (una página de ancho por 1 de alto, así se imprimirá en una sola hoja,...).

Para modificar los márgenes superior, inferior, derecho e izquierdo de las hojas a imprimir, utilizar la ficha Márgenes.

Si la hoja tiene encabezado: o pie de página:, también nos permite indicar a cuántos centímetros del borde del papel queremos que se sitúen. Si deseas que tu salida tenga centradas las hojas tanto horizontal como verticalmente, Excel nos lo realizará automáticamente activando las casillas Horizontalmente y/o Verticalmente respectivamente.

En la ficha Encabezado y pie de página podrás personalizarlos y ajustar diferentes parámetros.

Un encabezado es un texto impreso en la parte superior de cada una de las páginas, y un pie de página es un texto impreso en la parte inferior de cada página. Los pasos para crear un encabezado o un pie de página son los mismos.

Excel divide el encabezado y el pie de página en tres secciones, dependiendo de dónde queremos que aparezca el contenido del encabezado o pie de página, hacer clic en ésta y escribir el texto deseado. Existen unos botones que nos servirán para introducir unos códigos o modificar el aspecto del encabezado o pie de página. Para cambiar el aspecto del texto escrito y seleccionado. Al hacer clic sobre este botón aparece el cuadro de diálogo Fuente ya estudiado en este curso.

Para que aparezca el número de la página. Al hacer clic sobre este botón aparece en la sección en la que nos encontramos situados &[Página] de forma que a la hora de imprimir la hoja, aparecerá el número de página correspondiente a la hoja impresa. Para incluir el número total de páginas a imprimir del libro de trabajo. Al hacer clic sobre este botón aparece &[Páginas] y a la hora de imprimir saldrá el número total de páginas. Para poner la fecha. Al hacer clic sobre este botón aparece &[Fecha] y en la impresión saldrá la fecha real. Para poner la hora. Al hacer clic sobre este botón aparece &[Hora] y en la impresión saldrá la hora real. Para poner el nombre del libro de trabajo con su ruta de acceso (es decir su posición en el disco). Al hacer clic sobre este botón aparece [Ruta de acceso]&[Archivo] y en la impresión saldrá el nombre del libro de trabajo completo Para poner el nombre del libro de trabajo. Al hacer clic sobre este botón aparece &[Archivo] y en la impresión saldrá el nombre del libro de trabajo. Para poner el nombre de la hoja. Al hacer clic sobre este botón aparece &[Etiqueta] y en la impresión saldrá el nombre de la hoja impresa. Para insertar una imagen. Al hacer clic sobre este botón aparecerá el cuadro de diálogo para elegir la imagen a insertar y una vez elegida ésta en el recuadro del encabezado o pie de página pondrá &[Imagen] y en la impresión saldrá la imagen seleccionada. Se puede utilizar para incluir el logotipo de la empresa por ejemplo. Para cambiar el aspecto de la imagen seleccionada. Este botón solamente estará activo en caso de haber añadido una imagen en el encabezado o pie de página. Al hacer clic sobre este botón aparece el cuadro de diálogo Formato de imagen para poder elegir los cambios de aspecto de la imagen deseados. Realizar las modificaciones deseadas y hacer clic sobre el botón Aceptar.

La pestaña Hoja en el cuadro de diálogo Configurar Página provee opciones adicionales de impresión que puede ser útil en su hoja de trabajo.

Área de impresión: por default, excel imprime desde la celda A1 hasta la última celda ocupada. Pero se puede especificar un rango de celdas diferente para imprimir Imprimir Títulos: Esta opción imprime las etiquetas de filas y columnas en cada página. Especifique cuales filas o columnas deben imprimirse en los campos Repetir filas en extremo superior o Repetir columnas a la izquierda. Imprimir > Líneas de División: determina si las líneas de división de las celdas son impresas. Imprimir > Blanco y Negro: imprime en blanco y negro pese a usar colores en la hoja de trabajo. Imprimir > Calidad Borrador: imprime sin líneas de división o gráficos

Imprimir > Encabezado de filas y columnas: imprime los números de filas y las letras de columnas del excel Orden de las páginas: determina el orden en que las páginas son impresas.

10. Autoajuste de Celdas Ancho de las columnas Para modificar el ancho de las columnas se coloca el cursor sobre el encabezado de la columna, sobre la línea que está a la derecha que la separa de la columna contigua, el puntero se nos transformará en una flecha de doble sentido y la arrastramos hacia la derecha o la izquierda, dependiendo del tamaño que queramos.

Altura de las filas Se coloca el cursor en el encabezado de la fila y nos situamos sobre la línea que la separa de la fila siguiente. Cuando el cursor se transforme en una flecha de dos puntas, la arrastramos y soltamos cuando tengamos la altura deseada.

Cambiar ancho y alto de columnas y filas A veces necesitamos aumentar o disminuir el ancho de una columna para que los datos se ajusten al área de impresión o tal vez estamos creando una tabla y deseamos que las celdas sean más grandes que su tamaño predeterminado. Para cambiar el ancho de una columna debes hacer clic derecho sobre su encabezado y seleccionar la opción Ancho de columna y se mostrará un cuadro de diálogo donde podrás indicar el nuevo valor:

Al terminar de indicar el nuevo ancho debes pulsar el botón Aceptar para hacer permanentes los cambios. Otra manera de llegar a este mismo cuadro de diálogo es desde Inicio > Celdas > Formato > Ancho de columna. Por otro lado, para modificar el alto de una fila podemos seleccionar una celda de la fila deseada e ir a Inicio > Celdas > Formato > Alto de fila.

Esto mostrará el cuadro de diálogo necesario para modificar el valor del alto de la fila. Por supuesto que también podemos modificar el alto de la fila si hacemos clic derecho en su encabezado y seleccionamos la opción Alto de fila. Una última alternativa para ajustar el ancho de una columna es arrastrar el borde derecho de su encabezado hasta alcanzar el ancho deseado. También puedes arrastrar el borde inferior del encabezado de una fila para hacerla más alta. Un truco muy utilizado es el autoajuste de columna o de fila el cual podemos lograr haciendo doble clic sobre el borde derecho del encabezado de una columna o sobre el borde inferior del encabezado una fila. Esto hará que la columna se autoajuste al ancho del contenido o que la fila se autoajuste al alto de su contenido. La opción Autoajustar es muy útil y por eso encontramos ambos comandos en Inicio > Celdas > Formato:

11. Inmovilizar Paneles Para mantener un área de una hoja de cálculo visible mientras se desplaza a otra área de la hoja de cálculo, puede bloquear filas o columnas específicas inmovilizando paneles o crear varias áreas de hoja de cálculo que puedan desplazarse independientemente dividiendo paneles.

Inmovilizar o dividir filas y columnas Para inmovilizar paneles, Microsoft Excel mantiene filas o columnas específicas visibles al desplazarse por la hoja de cálculo. Por ejemplo, si la primera fila de la hoja de cálculo contiene etiquetas, puede inmovilizarla para

asegurarse de que las etiquetas de la columna permanezcan visibles a medida que se desplaza hacia abajo en la hoja de cálculo.

Una línea sólida en la fila 1 indica que la fila se inmoviliza para mantener las etiquetas de las columnas en su lugar mientras se desplaza.

Paneles de división Al dividir paneles, Excel crea dos o cuatro áreas de hoja de cálculo independientes dentro de las cuales es posible desplazarse, mientras que las filas o columnas del área no desplazable permanecen visibles. Para usar los paneles utilice:

Nota: No se pueden dividir paneles e inmovilizarlos al mismo tiempo. Cuando inmovilice paneles dentro de un panel dividido, todas las filas que se encuentran encima y las columnas que se encuentran a la izquierda de la celda seleccionada se inmovilizarán y la barra de división se quitará.

Anular la división de paneles Para restaurar una ventana que se ha dividido en dos áreas desplazables, haga doble clic en cualquier parte de la barra de división que separa los paneles. Para quitar todos los paneles divididos a la vez, en el grupo Ventana, haga clic en Dividir.

Inmovilizar paneles para bloquear filas o columnas específicas Antes de elegir inmovilizar paneles en una hoja de cálculo, es importante tener en cuenta lo siguiente: Solo es posible inmovilizar las filas de la parte superior y las columnas del lado izquierdo de la hoja de cálculo. No se pueden inmovilizar filas y columnas en el medio de la hoja de cálculo. El comando Inmovilizar paneles no está disponible en el modo de edición de celdas o cuando una hoja de cálculo está protegida Puede elegir inmovilizar solamente la fila superior de la hoja de cálculo, solamente la columna izquierda de la hoja de cálculo o varias filas y columnas simultáneamente. Por ejemplo, si inmoviliza la fila 1 y, a continuación, decide inmovilizar la columna A, la fila 1 ya no estará inmovilizada. Si desea inmovilizar filas y columnas al mismo tiempo, debe elegir inmovilizarlas simultáneamente. En la hoja de cálculo, siga uno de estos procedimientos: Para bloquear filas, seleccione la fila que se encuentra debajo de la fila o filas que desee mantener visibles mientras se desplaza. Para bloquear columnas, seleccione la columna que se encuentra a la derecha de la columna o columnas que desee mantener visibles mientras se desplaza. Para bloquear filas y columnas, haga clic en la celda que se encuentra debajo y a la derecha de las filas y columnas que desee mantener visibles mientras se desplaza. En la pestaña Vista, en el grupo Ventana, haga clic en la flecha situada debajo de Inmovilizar paneles.

Siga uno de estos procedimientos: Para bloquear solo una fila, haga clic en Inmovilizar fila superior. Para bloquear solo una columna, haga clic en Inmovilizar primera columna. Para bloquear más de una fila o columna, o para bloquear filas y columnas al mismo tiempo, haga clic en Inmovilizar paneles.

Movilizar paneles En la ficha Vista, en el grupo Ventana, haga clic en la flecha situada debajo de Inmovilizar paneles.

Haga clic en Movilizar paneles.

12. Filtros (Color, valores, de texto, número y fecha, etc.) Tecla Rápida:

Ctrl+Mayusc+L Filtrar datos con Filtro automático es una manera rápida y sencilla de buscar y trabajar con un subconjunto de datos de un rango de celdas o de una tabla. Después de filtrar datos en un rango de celdas o en una tabla, puede volver a aplicar un filtro para obtener resultados actualizados, o bien borrar un filtro para volver a mostrar todos los datos. Puede filtrar por más de un columna. Los filtros son aditivos, lo que significa que cada filtro adicional se basa en el filtro actual y además reduce el subconjunto de datos.

Para aplicar un filtro utilice la tecla rápida o las opciones de menú:

Ficha: Datos >Grupo: Ordenar y Filtrar > Botón: Filtro

Filtrar Texto

Seleccionar de una lista de valores de texto

Escribir el criterio de búsqueda para aplicar el filtro

Uso de carácter comodín Los siguientes caracteres comodines se pueden utilizar como criterios de comparación para filtros de texto. Utilice

Para buscar Cualquier número de caracteres

* (asterisco) Por ejemplo, *este buscará "Nordeste" y "Sudeste"

Agregar uno o más criterios

Filtrar Números La lista de números puede llegar a tener hasta un máximo de 10.000 entradas. Si la lista es grande, desactive (Seleccionar todo) en la parte superior y, a continuación, seleccione los números concretos por los que desea filtrar.

Filtrar por Fechas En el filtrado automático se tienen las siguientes opciones:

En el campo de Búsqueda seleccione un valor de día (número de día), mes (nombre del mes) o año para consultar los valores disponibles ó presione el botón Más para especificar el criterio exacto de búsqueda y luego ingrese el valor a Buscar:

En los Filtros por fecha se puede especificar alguno de estos criterios predefinidos:

Borrar un filtro Puede borrar un filtro para una columna específica o borrar todos los filtros.

Borrar un filtro para una columna Para borrar un filtro para una columna en un rango de celdas de varias columnas o en una tabla, haga clic en el botón Filtro

en el encabezado de columna y, después, haga clic en Borrar filtro de <"Nombre de columna">.

Uso de Agregar la selección actual al Filtro Para aplicar diversos filtros sobre la misma columna se puede marcar la casilla Agregar la selección actual al filtro

Borrar todos los filtros en una hoja de cálculo y volver a mostrar todas las filas En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Borrar.

O presione Ctrl+Mayusc+L sobre cualquiera de las columnas del rango filtrado.

13. Uso de la herramienta Texto en columnas Quitar Duplicados Para eliminar datos duplicados en una columna puede utilizar la opción Quitar Duplicados de la Ficha Datos, Grupo Herramientas de Datos

Aparecerá el siguiente cuadro de diálogo donde se debe especificar la o las columnas que contienen duplicados y se desean remover:

Texto en Columnas La opción Texto en Columnas se encuentra en la ficha Datos, Botón Texto en Columnas:

Al seleccionar esta opción se despliega el siguiente cuadro de diálogo donde se especifica entre otras cosa, si los datos están delimitados o tiene un ancho fijo; el tipo de separador entre los datos (tabulador, punto y coma, coma, espacio u otro), el formato final (entre general, fecha y texto) y la celda a partir de la cual quedarán separados los textos.

Importante Si no especifica un nuevo destino para las nuevas columnas, los datos divididos reemplazarán los datos originales.

14. Formatos Condicionales (Barras de Datos, Escalas de color, conjunto de íconos, rangos) Reglas basadas en valores de celda: Estas reglas se basan en el mismo valor de la celda (Mayor que, Menor que, Igual a, Entre, etc.). Reglas basadas en fórmulas: Estas reglas ofrecen mayor flexibilidad porque puedes aplicar un formato especial utilizando una fórmula donde podrás aplicar una lógica más compleja. Por lo mismo es un poco más complicado de aprender, pero una vez que lo hagas seré muy intuitivo de utilizar.

Formato condicional en números Cuando los datos de nuestras celdas son valores numéricos, Excel provee de varias opciones para aplicar un formato condicional rápidamente. Las primeras alternativas que analizaremos serán las que se encuentran en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas.

Aquí podemos utilizar las siguientes opciones: • • • •

Es mayor que: Se aplicará el formato a todas las celdas con un valor mayor al especificado. En menor que: El formato será aplicado a las celdas con un valor menor que el indicado. Entre: Excel evaluará las celdas para saber aquellas que tengan un valor dentro del rango indicado y se les aplicará el formato. Es igual a: Solo las celdas que sean iguales al valor indicado tendrán el formato.

Una vez que seleccionamos la opción que deseamos utilizar, Excel mostrará un cuadro de diálogo que nos permitirá indicar los valores numéricos con los cuales se realizará la comparación y también podremos proporcionar todo el detalle del formato a aplicar. En la siguiente imagen puedes notar la aplicación de cada una de las reglas mencionadas anteriormente sobre nuestros datos numéricos:

Otras reglas de formato condicional que podemos aplicar rápidamente a celdas con valores numéricos son las que se encuentran bajo la opción de menú Reglas superiores e inferiores:

A continuación describo brevemente cada una de estas opciones de formato condicional. • • •

10 superiores: Se aplicará el formato exactamente a las 10 celdas que tengan los valores más altos. Es posible modificar la cantidad de celdas superiores a las que se aplicará el formato. 10% de valores superiores: Excel aplicará el formato al 10% de las celdas que contengan los valores más altos. También es posible indicar un porcentaje diferente al 10%. 10 inferiores: El formato se aplica a las 10 celdas con los valores más bajos.

• • •

10% de valores inferiores: El formato es aplicado al 10% de las celdas con los valores más bajos dentro del rango. Por encima del promedio: Excel obtiene el promedio de todos los valores numéricos del rango y aplica el formato a las celdas que tengan un valor por encima de dicho promedio. Por debajo del promedio: Después de obtener el promedio, el formato será aplicado en las celdas que tengan un valor inferior.

En la siguiente imagen vemos aplicada cada una de las reglas de formato condicional anteriores:

Con estos ejemplos de formato condicional podemos ver lo fácil que es utilizar esta funcionalidad para resaltar los valores numéricos de nuestro interés.

Formato condicional en texto Si nuestras celdas contienen texto podemos utilizar algunas opciones de formato condicional para resaltar nuestras celdas. La primera opción que podemos utilizar es la regla que nos ayuda a saber si un valor es igual a otro, me refiero a la opción que se encuentra en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Es igual a.

Esta opción comparará el valor de cada celda con la cadena de texto especificada y en caso de ser iguales se aplicará el formato. Es importante mencionar que esta regla no es sensible a mayúsculas y minúsculas. Otra regla de formato condicional para texto que podemos utilizar es la opción Texto que contiene que se

encuentra en el mismo menú que la opción anterior, solo que en este caso, Excel buscará las celdas que contengan la cadena de texto especificada. Observa un ejemplo de ambas reglas:

Formato condicional en fechas Si los datos que tenemos en nuestra hoja son fechas, entonces existe una opción especialmente diseñada para este tipo de datos. Podemos aplicar formato condicional a celdas que contienen fechas desde Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Una fecha. Al hacer clic sobre esta opción se mostrará el siguiente cuadro de diálogo:

Esta opción nos permitirá resaltar fácilmente las celdas que contengan una fecha que cumpla con el criterio seleccionado: Hoy, Ayer, Mañana, En los últimos 7 días, Semana pasada, etc.

Resaltar duplicados con Formato condicional También es posible utilizar el formato condicional para resaltar duplicados en nuestros datos. Esta opción funciona para cualquier tipo de dato que tengamos en las celdas, ya sean números, fechas e inclusive texto. La opción que tenemos que elegir para resaltar valores duplicados se encuentra en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Duplicar valores:

Esto mostrará un cuadro de diálogo que nos permitirá elegir si queremos resaltar los valores que están duplicados o los valores únicos. A continuación puedes ver un ejemplo de estas opciones sobre nuestros datos:

La regla Duplicados se aplicará sobre todos los elementos que aparecen más de una vez y la regla Únicos será para las celdas que solamente aparecen una vez dentro del rango. Con estos ejemplos de formato condicional en Excel 2013 podemos darnos cuenta de los beneficios que tiene esta funcionalidad para ayudarnos a resaltar rápidamente celdas que cumplen con algún criterio establecido. Además de las opciones revisadas hasta ahora existen otros tipos de formato condicional que revisaremos en lecciones posteriores.

Crear reglas de formato condicional con fórmula Por ejemplo, supongamos que realiza un seguimiento de los cumpleaños de sus pacientes dentales para ver cuál está próximo y marcarlos como que han recibido una felicitación de cumpleaños de su parte. En esta hoja de cálculo, vemos la información que deseamos usando formato condicional, controlada por dos reglas en las que cada una de ellas contiene una fórmula. La primera regla, en la columna A, aplica formato a futuros cumpleaños y la regla de la columna C aplica formato a celdas tan pronto como se introduce “Y”, indicando que se ha enviado la tarjeta de cumpleaños.

Para crear la primera regla: Seleccione de la celda A2 a la A7. Realice esta acción arrastrando desde A2 a A7. A continuación, haga clic en Inicio > Formato condicional > Nueva regla. En el cuadro de diálogoNueva regla de formato, haga clic en Utilizar una fórmula para determinar en qué celdas desea aplicar el formato. En Dar formato a los valores donde esta fórmula sea verdadera, escriba la fórmula: =A2>HOY() La fórmula usa la función HOY para ver si las fechas de la columna A son posteriores a hoy (en el futuro). De ser así, se aplica formato a las celdas. Haga clic en Formato. En el cuadro Color, seleccione Rojo. En el cuadro Estilo de fuente, seleccione Negrita. Haga clic en Aceptar hasta que se cierren los cuadros de diálogo. El formato se aplica a la columna A. Para crear la segunda regla: Seleccione las celdas de la C2 a la C7. Repita los pasos 2 a 4 anteriores e introduzca esta fórmula: =C2="Y" La fórmula prueba para ver si las celdas de la columna C contienen “Y” (las comillas alrededor de Y indican a Excel que es texto). De ser así, se aplica formato a las celdas. En el cuadro Color, seleccione Blanco. En el cuadro Estilo de fuente, seleccione Negrita. Haga clic en la pestaña Relleno y seleccione Verde. El formato se aplica a la columna C.

Barras de datos, escalas de color y conjuntos de iconos para resaltar datos El conjunto de íconos en Excel 2013 es una manera de poner formato condicionado en nuestros datos, se puede aplicar estos íconos de acuerdo al número de rangos definidos, los cuales pueden llegar a ser hasta cinco. Si por ejemplo tengo una lista de sueldos, se puede aplicar el formato condicional con la opción de conjunto de íconos para destacar a los sueldos más altos, los medios y los bajos: Pasos 1)

Seleccionar el rango de datos de interés

2)

Inicio > Estilos > Formato condicional

3)

Formato condicional > conjunto de íconos > elegir íconos

De esta manera, los íconos nos indican la situación del dato, dentro del rango. Las flechas verdes resaltan los sueldos más altos, la flecha amarilla a los sueldos medios y las flechas rojas a los sueldos más bajos. Si quieres condicionar los resultados con los valores que quieras asignar, es decir si quieres definir como Excel define a que cantidades les toca determinada flecha debes hacer lo siguiente: Pasos: 1)

Inicio > Estilos > formato condicional > Administrar reglas

2)

Editar regla

Como pueden observar le cambié el valor a las flechas verdes, para que considerara como sueldos mayores a los que son mayor o igual a 3400 soles, así también asigné como mínimo de sueldo medio 2100 soles, lo cual resultó lo siguiente:

En lo que respecta a los conjuntos de íconos de cuatro y cinco se puede usar de la siguiente manera: En este ejemplo se tiene una lista con la edad de mascotas, para la elección del conjunto de íconos se debe realizar el mismo procedimiento, sólo que esta vez se eligen los de 4 o 5 íconos. También se definen los valores para cada ícono.

Por último, también se pueden ocultar los íconos que no queremos que aparezcan. De la siguiente manera: 1)

Inicio > Estilos > formato condicional > Administrar reglas

2)

Editar regla > seleccionar ícono > seleccionar “No hay ícono de celda”

Y el resultado será el siguiente:

15. Validación de celdas, parametrización de mensajes de entrada y de error La validación de datos en Excel es una herramienta 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 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:

Más adelante veremos cómo 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.

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. 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: 1. 2. 3. 4. 5.

Número entero Decimal Fecha Hora Longitud de texto

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:

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:

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.

Si tienes duda sobre cómo crear referencias de este tipo, te recomiendo leer el artículo llamado Hacer referencia a celdas de otras hojas en Excel.

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.

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.

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.

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

16. Funciones de Texto (Izquierda, Derecha, Extrae, Nompropio, Igual, Espacios, Concatenar Las funciones de texto en Excel permiten concatenar cadenas de caracteres, remover los espacios en blanco, reemplazar ciertos caracteres por otros y muchas cosas más que te permitirán manipular las cadenas de texto para obtener los resultados deseados. Encuentra el detalle y ejemplos de cada función haciendo clic sobre su nombre. FUNCIÓN

INGLÉS

DESCRIPCIÓN

CARACTER

CHAR

Devuelve el carácter especificado por el número de código a partir del juego de caracteres establecido en su PC.

CODIGO

CODE

Devuelve el número de código del primer carácter del texto del juego de caracteres usados por su PC.

CONCATENAR CONCATENATE Une varios elementos de texto en uno solo. DECIMAL

FIXED

Redondea un número al número especificado de decimales y devuelve el resultado como texto con o sin comas.

DERECHA

RIGHT

Devuelve el número especificado de caracteres del final de una cadena de texto.

ENCONTRAR FIND

Devuelve la posición inicial de una cadena de texto dentro de otra cadena de texto.

ESPACIOS

TRIM

Quita todos los espacios del texto excepto los espacios individuales entre palabras.

EXTRAE

MID

Devuelve los caracteres del centro de una cadena de texto, dada una posición y longitud iniciales.

HALLAR

SEARCH

Devuelve el número de caracteres en el cual se encuentra un carácter en particular o cadena de texto, leyendo de izquierda a derecha.

IGUAL

EXACT

Comprueba si dos cadenas de texto son exactamente iguales y devuelve VERDADERO o FALSO.

IZQUIERDA

LEFT

Devuelve el número especificado de caracteres del principio de una cadena de texto.

LARGO

LEN

Devuelve el número de caracteres de una cadena de texto.

LIMPIAR

CLEAN

Quita todos los caracteres no imprimibles del texto.

MAYUSC

UPPER

Convierte una cadena de texto en letras mayúsculas.

MINUSC

LOWER

Convierte todas las letras de una cadena de texto en minúsculas.

MONEDA

DOLLAR

Convierte un número en texto usando formato de moneda.

NOMPROPIO PROPER

Convierte una cadena de texto en mayúsculas o minúsculas, según corresponda; la primera letra de cada palabra en mayúscula y las demás letras en minúscula.

REEMPLAZAR REPLACE

Reemplaza parte de una cadena de texto por otra.

REPETIR

REPT

Repite el texto un número determinado de veces.

SUSTITUIR

SUBSTITUTE

Reemplaza el texto existente con texto nuevo en una cadena.

T

T

Comprueba si un valor es texto y devuelve el texto si lo es, o comillas dobles si no lo es.

TEXTO

TEXT

Convierte un valor en texto, con un formato de número específico.

TEXTOBAHT

BAHTTEXT

Convierte un número en texto (baht).

VALOR

VALUE

Convierte un argumento de texto que representa un número en un número.

17. Funciones Matemáticas (Suma, Sumar.si, Subtotales y Otras) Las funciones matemáticas de Excel son utilizadas para ejecutar varias operaciones aritméticas como la suma y el producto de dos números. Las funciones trigonométricas de Excel permitirán obtener el seno, coseno y tangente de un ángulo especificado. Encuentra el detalle y ejemplos de cada función haciendo clic sobre su nombre. FUNCIÓN

INGLÉS

DESCRIPCIÓN

ABS

ABS

Devuelve el valor absoluto de un número, es decir, un número sin signo.

ACOS

ACOS

Devuelve el arcoseno de un número, en radianes, dentro del intervalo de 0 a Pi.

ACOSH

ACOSH

Devuelve el coseno hiperbólico inverso de un número.

AGREGAR

AGGREGATE

Devuelve un agregado de una lista o base de datos.

ALEATORIO

RAND

Devuelve un número aleatorio mayor o igual que 0 y menor que 1.

ALEATORIO.ENTRE

RANDBETWEEN

Devuelve un número aleatorio entre los números que especifique.

ASENO

ASIN

Devuelve el arcoseno de un número en radianes, dentro del intervalo -Pi/2 a Pi/2

ASENOH

ASINH

Devuelve el seno hiperbólico inverso de un número.

ATAN

ATAN

Devuelve el arco tangente de un número en radianes, dentro del intervalo -Pi/2 a Pi/2.

ATAN2

ATAN2

Devuelve el arco tangente de las coordenadas X e Y especificadas, en un valor en radianes comprendido entre -Pi y Pi, excluyendo -Pi.

ATANH

ATANH

Devuelve la tangente hiperbólica inversa de un número.

COCIENTE

QUOTIENT

Devuelve la parte entera de una división.

COMBINAT

COMBIN

Devuelve el número de combinaciones para un número determinado de elementos.

COS

COS

Devuelve el coseno de un ángulo.

COSH

COSH

Devuelve el coseno hiperbólico de un número.

ENTERO

INT

Redondea un número hasta el entero inferior más próximo.

EXP

EXP

Devuelve e elevado a la potencia de un número determinado.

FACT

FACT

Devuelve el factorial de un número.

FACT.DOBLE

FACTDOUBLE

Devuelve el factorial doble de un número.

GRADOS

DEGREES

Convierte radianes en grados.

LN

LN

Devuelve el logaritmo natural de un número.

LOG

LOG

Devuelve el logaritmo de un número en la base especificada.

LOG10

LOG10

Devuelve el logaritmo en base 10 de un número.

M.C.D

GCD

Devuelve el máximo común divisor.

M.C.M

LCM

Devuelve el mínimo común múltiplo.

MDETERM

MDETERM

Devuelve el determinante matricial de una matriz.

MINVERSA

MINVERSE

Devuelve la matriz inversa de una matriz dentro de una matriz.

MMULT

MMULT

Devuelve el producto matricial de dos matrices, una matriz con el mismo número de filas que Matriz1 y columnas que Matriz2.

MULTINOMIAL

MULTINOMIAL Devuelve el polinomio de un conjunto de números.

MULTIPLO.INFERIOR

FLOOR

Redondea un número hacia abajo, hasta el múltiplo significativo más cercano.

MULTIPLO.INFERIOR.EXACTO FLOOR.PRECISE

Redondea un número hacia abajo, hasta el entero o múltiplo significativo más cercano.

MULTIPLO.SUPERIOR

Redondea un número hacia arriba, hasta el múltiplo significativo más cercano.

CEILING

MULTIPLO.SUPERIOR.EXACTO CEILING.PRECISE

Redondea un número hacia arriba, al entero o múltiplo significativo más cercano.

NUMERO.ROMANO

ROMAN

Convierte un número arábigo en romano, en formato de texto.

PI

PI

Devuelve el valor Pi con precisión de 15 dígitos.

POTENCIA

POWER

Devuelve el resultado de elevar el número a una potencia.

PRODUCTO

PRODUCT

Multiplica todos los números especificados como argumentos.

RADIANES

RADIANS

Convierte grados en radianes.

RAIZ

SQRT

Devuelve la raíz cuadrada de un número.

RAIZ2PI

SQRTPI

Devuelve la raíz cuadrada de (número * Pi).

REDOND.MULT

MROUND

Devuelve un número redondeado al múltiplo deseado.

REDONDEA.IMPAR

ODD

Redondea un número positivo hacia arriba y un número negativo hacia abajo hasta el próximo entero impar.

REDONDEA.PAR

EVEN

Redondea un número positivo hacia arriba y un número negativo hacia abajo hasta el próximo entero par.

REDONDEAR

ROUND

Redondea un número al número de decimales especificado.

REDONDEAR.MAS

ROUNDUP

Redondea un número hacia arriba, en dirección contraria a cero.

REDONDEAR.MENOS

ROUNDDOWN

Redondea un número hacia abajo, hacia cero.

RESIDUO

MOD

Proporciona el residuo después de dividir un número por un divisor.

SENO

SIN

Devuelve el seno de un ángulo determinado.

SENOH

SINH

Devuelve el seno hiperbólico de un número.

SIGNO

SIGN

Devuelve el signo de un número: 1, si el número es positivo; cero, si el número es cero y -1, si el número es negativo.

SUBTOTALES

SUBTOTAL

Devuelve un subtotal dentro de una lista o una base de datos.

SUMA

SUM

Suma todos los números en un rango de celdas.

SUMA.CUADRADOS

SUMSQ

Devuelve la suma de los cuadrados de los argumentos.

SUMA.SERIES

SERIESSUM

Devuelve la suma de una serie de potencias.

SUMAPRODUCTO

SUMPRODUCT

Devuelve la suma de los productos de rangos o matrices correspondientes.

SUMAR.SI

SUMIF

Suma las celdas que cumplen determinado criterio o condición.

SUMAR.SI.CONJUNTO

SUMIFS

Suma las celdas que cumplen un determinado conjunto de condiciones o criterios.

SUMAX2MASY2

SUMX2PY2

Devuelve la suma del total de las sumas de cuadrados de números en dos rangos o matrices correspondientes.

SUMAX2MENOSY2

SUMX2MY2

Suma las diferencias entre cuadrados de dos rangos o matrices correspondientes.

SUMAXMENOSY2

SUMXMY2

Suma los cuadrados de las diferencias en dos rangos correspondientes de matrices.

TAN

TAN

Devuelve la tangente de un ángulo.

TANH

TANH

Devuelve la tangente hiperbólica de un número.

TRUNCAR

TRUNC

Convierte un número decimal a uno entero al quitar la parte decimal o de fracción.

18. Funciones Lógicas (Función SI Condicional, SI.ERROR) Las funciones lógicas en Excel se utilizan en la toma de decisiones. En base al resultado de una función decidiremos si ejecutar o no cierta acción requerida. Encuentra el detalle y ejemplos de cada función haciendo clic sobre su nombre. FUNCIÓN

INGLÉS DESCRIPCIÓN

FALSO

FALSE

Devuelve el valor lógico FALSO.

NO

NOT

Cambia FALSO por VERDADERO y VERDADERO por FALSO.

O

OR

Comprueba si alguno de los argumentos es VERDADERO y devuelve VERDADERO o FALSO. Devuelve FALSO si todos los argumentos son FALSO.

SI

IF

Comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.

SI.ERROR

IFERROR Devuelve un valor si la expresión es un error y otro valor si no lo es.

VERDADERO TRUE

Devuelve el valor lógico VERDADERO.

Y

Comprueba si todos los argumentos son VERDADEROS y devuelve VERDADERO o FALSO. Devuelve FALSO si alguno de los argumentos es FALSO.

AND

Valor_si_error: Es el valor o mensaje que se devuelve si la fórmula lo evalúa como error. Los tipos de error que son evaluados en la función son los siguientes: • • • • • • •

#N/A #¡VALOR! #¡REF! #¡DIV/0! #¡NUM! #¿NOMBRE? #¡NULO!

19. Funciones de Búsqueda (BuscarV, BuscarH) La función BUSCARV BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

La misión de la función BUSCARV Excel es buscar un determinado dato (valor_buscado) en la primera columna de una tabla o matriz (matriz_buscar_en), y una vez localizada la fila en la que se encuentra dicho dato, devolver el valor que tiene en esa misma fila la columna que especifiquemos (indicador_columnas). Valor_buscado: Es el valor que se va a buscar en la primera columna de la matriz o tabla. Matriz_buscar_en: Se trata del rango que se corresponde con la tabla o matriz donde han de buscarse los datos. B2:E29 en ambos casos. Indicador_columnas: Número de columna donde se encuentra el valor que tratamos de encontrar. El valor 1 es para la primera columna. Así pues, en nuestro ejemplo nos referimos a las columnas 3 (porcentaje) y 2 (ventas) respectivamente. Ordenado: Valor lógico que especifica si la función BUSCARV va a buscar una coincidencia exacta o aproximada: •

Si escribimos 0 la función sólo le valdrá una coincidencia exacta en la primera columna con el Valor_buscado. Es la opción más recomendable, ya que no exige hacer nada más.

•

Si escribimos 1 devolverá una coincidencia exacta o aproximada.

Mejores prácticas NO usar constantes en el tercer argumento de la función porque el resultado puede variar si se agregan filas a la matriz o rango donde se busca Cuando ingreses los rangos para búsqueda (parámetro 2) utiliza el signo de $, esto hará que el rango se conserve aun cuando copies la formula en otras celdas, por ejemplo $B$1:$C$20 con esto se usará el rango desde la celda B1 hasta la C20. También puedes ingresar $B:$C con esto el rango sera todas las celdas en las columnas B y C. Si quieres hacer una búsqueda por el valor más parecido (parámetro 4 = verdadero ó 1) deberás tener ordenada de menor a mayor los valores de la primera columna de tu rango (parámetro 2). Cuando hagas búsquedas de textos, asegúrate de que los valores no tengan espacios al inicio, de lo contrario podrías recibir datos equivocados. Cuando busques por valores numéricos, asegúrate que no están ingresados en formato de texto, para estar seguros siempre coloca formato de número a las celdas y usa la opción de convertir a valor numérico. En caso de que no se encuentre ningún valor, buscarv retorna el error #N/A. Combinar el uso de SI.ERROR con BuscarV permitirá manejar este tipo de excepciones al buscar de forma más amigable para el usuario.

Errores comunes al usar buscarv Si la columna llave no tiene valores únicos para cada fila entonces la función BUSCARV regresará el primer resultado encontrado que concuerde con el valor buscado.

Si especificamos un indicador de columna mayor al número de columnas de la tabla obtendremos un error de tipo #¡REF! Si colocamos el indicador de columna igual a cero la función BUSCARV regresará un error de tipo #¡VALOR! Si configuramos la función BUSCARV para realizar una búsqueda exacta, pero no encuentra el valor buscado, entonces la función regresará un error de tipo #N/A.

La función 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.

Sintaxis de la función BUSCARH La función BUSCARH tiene tres argumentos que son los siguientes: 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.

Ejemplo de la función BUSCARH En la siguiente tabla tengo la información de los artículos que ha vendido cada uno de los vendedores en los últimos meses. Como título de columnas están los nombres de los vendedores y como filas los meses.

Ahora quiero saber los productos vendidos en el mes de Febrero por Paco y para ello puedo utilizar la función BUSCARH de la siguiente manera:

=BUSCARH("Paco", B1:D6, 3,FALSO)

Si quisiera busca la información del mismo mes para Luis, entonces la función cambiará de argumentos de la siguiente manera: =BUSCARH("Luis", B1:D6, 3,FALSO)

Si por el contrario quiero conocer los productos vendidos por Hugo en el mes de Abril, entonces la función sería la siguiente: =BUSCARH("Hugo", B1:D6, 5,FALSO)

De esta manera, la función BUSCARH nos permite hacer una búsqueda en una fila (búsqueda horizontal) y encontrar fácilmente el valor requerido.

20. Tablas para organizar datos y Ordenamientos Tecla rápida: Ctrl+T Muestra el cuadro de diálogo Crear tabla. Las tablas de Excel son un rango de celdas que contiene datos y su fila superior es una fila de encabezados que describen el contenido de cada columna. Cada fila de una tabla corresponde a una sola entidad, por ejemplo, una tabla puede contener información de los empleados de una empresa y por lo tanto cada fila hará referencia a un empleado diferente.

Las tablas tienen encabezados en la parte superior y cada uno de ellos describe la información contenida en cada columna. Así por ejemplo, nuestra tabla de empleados tiene los encabezados ID (de empleado), Nombre, Apellido, Salario, Departamento. Para crear una tabla de Excel debemos elegir la ficha Insertar y posteriormente pulsar el botón Tabla.

Esto convertirá el rango de celdas en una tabla de Excel.

Diferencias entre rangos y tablas de Excel Algunas de las características de las tablas de Excel que las hacen diferentes a los rangos de celdas son: • •

Al activar cualquier celda de la tabla se habilitará la ficha contextual llamada Herramientas de tabla la cual contendrá comandos adicionales para trabajar con la tabla. Se puede aplicar un estilo a la tabla con tan solo elegirlo de la galería de estilos.

• •

• •

Cada encabezado de columna contiene una lista desplegable la cual podemos utilizar para ordenar o filtrar los datos de la tabla. Si tenemos una tabla que contiene muchas filas y movemos la barra lateral derecha de hacia abajo, en lugar de que los encabezados de la tabla desaparezcan, Excel remplaza automáticamente las letras de las columnas por los encabezados de la tabla. Las tablas soportan referencias estructuradas lo que significa que en lugar de utilizar referencias de celdas, podemos utilizar el nombre de la tabla y de los encabezados. La esquina inferior derecha de una tabla tiene una pequeña flecha que nos permite agregar fácilmente una nueva fila o una nueva columna. Solamente debemos arrastrar dicha esquina hacia abajo o hacia la derecha.

Crear una tabla en Excel con estilo Es posible crear una tabla con un estilo de nuestra elección y para eso seleccionar una celda de los datos y en seguida ir a la ficha Inicio y dentro del grupo Estilos deberás pulsar el botón Dar formato como tabla. Esto abrirá un menú con los estilos de tablas disponibles y de los cuales deberás elegir alguno.

Resaltar filas alternas de una tabla Excel aplicará un fondo especial a las filas impares. Si por alguna razón no deseas que se aplique este formato puedes deshabilitarlo con la opción Filas con bandas dentro de la ficha Herramientas de tabla.

El nombre de una tabla en Excel Las tablas en Excel son objetos que pueden ser identificados y utilizados en un libro de Excel. Por esta razón, al momento de crear una tabla, se le asignará un nombre único automáticamente. De manera predeterminada, Excel colocará un nombre de tabla como Tabla1, Tabla2, Tabla3, etc. Una manera de conocer las tablas que contiene nuestro libro es haciendo clic en la flecha que aparece en el cuadro de nombres el cual está ubicado a la izquierda de la barra de fórmulas. Por ejemplo, la siguiente imagen indica que el libro actual tiene una tabla llamada Tabla1.

Otra alternativa que tenemos para conocer el nombre de las tablas de nuestro libro es desde la ficha Fórmulas > Administrador de nombres. Al hacer clic se mostrará un cuadro de diálogo con la lista de rangos nombrados de nuestro libro así como las tablas existentes.

El cuadro de diálogo Administrador de nombres nos dará mucha más información sobre las tablas de nuestro libro ya que podremos ver su nombre así como el rango de datos al cual hace referencia cada tabla. Si seleccionamos alguna de ellas y pulsamos el botón Editar podremos cambiar el nombre de la tabla para colocar alguno de nuestra preferencia.

Otra manera de editar el nombre de una tabla es seleccionando cualquiera de sus celdas y posteriormente ir a la ficha Herramientas de tabla > Diseño y en el extremo izquierdo encontrarás un cuadro de texto con el nombre de la tabla.

Puedes editar el nombre mostrado en el cuadro de texto y al pulsar la tecla Entrar habrás modificado su nombre. El nombre de una tabla es importante para poder crear referencias hacia ella desde nuestras fórmulas utilizando un tipo de referencia especial conocidas como referencias estructuradas. Además, podremos utilizar ese nombre de tabla en caso de querer manipularla desde código VBA. Se pueden agregar totales a una tabla con tan solo marcar la caja de selección Fila de totales:

Podemos modificar la Fila de totales especificando la operación que deseamos aplicar.

Referencias estructuradas

’

=SUMA( VentasDept[ [ #Totales] ; [Importe] ] ; VentasDept[ [ #Datos] ; [CantCom] ]) Esta fórmula tiene los siguientes componentes de referencia estructurada: Nombre de tabla VentasDept: es un nombre de tabla personalizado. Se hace referencia a los datos de tabla, sin ningún encabezado o las filas de totales. Especificador de columna: [Importe] e [CantCom] hacen referencia a los datos de la columna que representan sin tomar en cuenta el encabezado de columna o fila de totales. Debe llevar siempre los corchetes como se muestra. Especificador de elemento: [#Totales] y [#Datos] son especificadores de elementos especiales que hacen referencia a partes específicas de la tabla, como la fila Totales. Especificador de tabla: VentasDept[ [ #Totales] la parte externa (VentasDept) al especificador de elemento o especificado de columna hace referencia al nombre de la tabla. Referencia estructurada: (VentasDept[ [ #Totales] ; [Importe]] y VentasDept[ [ #Datos] ; [CantCom]] son referencias estructuradas que comienza con el nombre de la tabla y termina con el especificador de columna. Cuando cree o edite referencias estructuras, use estas reglas de sintaxis:

Especificadores de elementos especiales Para hacer referencia a partes específicas de una tabla, como la fila de totales, puede usar cualquiera de los siguientes especificadores de elementos especiales de las referencias estructuradas. Este especificador de elemento especial:

Hace referencia a:

#Todas

Toda la tabla, incluidos los encabezados de columna, datos y totales (si los hay).

#Datos

Solo las filas de datos.

#Encabezados

Solo la fila de encabezado.

#Totales

Solo la fila del total. Si no hay ninguna, devuelve un valor nulo.

#Esta Fila

Solo las celdas en la misma fila que la fórmula. Estos especificadores no se puede combinar con ningún especificador de elemento especial. Utilícela para forzar una intersección implícita de la referencia o para invalidar ese comportamiento y hacer referencia a valores individuales de una columna.

O bien @ O bien @ [Nombre de columna]

Excel cambia automáticamente de #Esta Fila a los especificadores @ en las tablas en las que tiene más de una fila de datos. Si la tabla tiene solo una fila, Excel no reemplaza los especificadores #Esta Fila, lo que puede ocasionar cambios inesperados en los resultados del cálculo al agregar más filas. Para evitar problemas de cálculo, asegúrese de escribir varias filas en la tabla antes de introducir cualquier fórmula de referencia estructurada.

Eliminar tabla sin afectar datos Si por alguna razón necesitas eliminar la tabla de Excel pero dejar intactos los datos, entonces debes seleccionar una celda de la tabla e ir a la ficha Herramientas de tabla > Diseño y pulsar el botón de comando Convertir en rango que se encuentra dentro del grupo Herramientas.

Se mostrará un cuadro de confirmación de Excel preguntando si deseas convertir la tabla en un rango normal. Al pulsar el botón Sí se eliminará la definición de la tabla pero los datos permanecerán en la hoja. Si la tabla original tenía un estilo, entonces las celdas del rango permanecerán con dicho estilo. Para removerlo puedes seleccionar el rango de celdas y en la ficha Inicio > Modificar abrir el menú del botón Borrar y elegir la opción Borrar formatos.

21. Funciones básicas para la confección de tablas dinámicas Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte. En este ejemplo tenemos una tabla de datos que contiene mucha información sobre las ventas de una compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.

Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:

Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:

Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.

Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la tabla dinámica:

Así tendrás un reporte que compara las ciudades y los productos y para cada combinación obtendrás el total de ventas. Lo más importante que quiero transmitir con este artículo es que las tablas dinámicas te permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.

Partes de una tabla dinámica en Excel Al crear una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica.

•

• • •

Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas. Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica. Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica. Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.

Lista de Campos

La lista de campos para tablas dinámicas está compuesta por todos los campos disponibles de la tabla y el configurador de la despliegue:

Dar formato a una tabla dinámica Una vez que has creado una tabla dinámica, Excel permite aplicarle formato fácilmente como si fuera una tabla de datos. La ficha Diseño incluye comandos especiales para aplicar formato a una tabla dinámica. La ficha Diseño es una ficha contextual, por lo que deberás seleccionar la tabla dinámica para que se muestre.

Esta ficha está dividida en tres grupos.

•

Diseño. Este grupo permite agregar subtotales y totales generales a la tabla dinámica así como modificar aspectos básicos de diseño.

•

Opciones de estilo de tabla dinámica. Las opciones de este grupo permiten restringir la selección de estilos que se muestran en el grupo que se encuentra justo a su derecha. Es decir, si seleccionas la opción “Filas con bandas”, entonces se mostrarán a la derecha los estilos que tienen filas con bandas.

•

Estilos de tabla dinámica. Muestra la galería de estilos que se pueden aplicar a la tabla dinámica. Con tan sólo hacer clic sobre el estilo deseado se aplicará sobre la tabla.

Puedes hacer clic en el botón Más del grupo Estilos de tabla dinámica para ver todos los estilos disponibles. Con tan sólo elegir alguno de los estilos se aplicará inmediatamente a la tabla dinámica.

Formato de valores en una tabla dinámica Para dar formato rápidamente a los valores agrupados de una tabla dinámica de manera de puedan tener el formato de número adecuado. Solamente sigue los siguientes pasos: Supongamos la siguiente tabla dinámica:

Para dar formato a los valores numéricos debes hacer seleccione uno de las celdas del campo en Valores Luego dar doble click y seleccionar Formato de número…

Se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:

Después de hacer la selección adecuada acepta los cambios y de inmediato se aplicará el nuevo formato a todos los valores de la tabla dinámica:

Segmentación de datos en tablas dinámicas La segmentación de datos en tablas dinámicas es una característica que permite hacer un filtrado de los datos dentro de una tabla dinámica. De esta manera puedes filtrar fácilmente la información por más de una columna. En primer lugar debes hacer clic sobre cualquier celda de la tabla dinámica y posteriormente en la ficha Analizar y dentro del grupo Filtrar deberás hacer clic sobre el comando Insertar Segmentación de datos.

Excel mostrará el cuadro de diálogo Insertar segmentación de datos.

En este cuadro deberás seleccionar los campos que deseas utilizar como filtros en la tabla dinámica y Excel colocará un filtro para cada campo seleccionado:

Para filtrar la información de la tabla dinámica es suficiente con hacer clic sobre cualquiera de las opciones del filtro.

Excel ajustará la información de la tabla dinámica de acuerdo a las opciones seleccionadas. Para mostrar de nuevo toda la información puedes hacer clic en el botón Borrar filtro que se encuentra en la esquina superior derecha de cada panel.

Podrás agregar tantos filtros como campos disponibles tengas en la tabla dinámica, lo cual te permitirá hacer un buen análisis de la información.

Escala de Tiempo

Modificar el tipo de cálculo de una tabla dinámica De manera predeterminada Excel utiliza la función SUMA al momento de crear los totales y subtotales de los valores de una tabla dinámica sin embargo es probable que necesites utilizar alguna otra función diferente como el promedio o la cuenta de elementos. Para cambiar la función que se utiliza en los valores resumidos debes hacer clic sobre el campo de valor y seleccionar la opción de menú Configuración de campo de valor:

En el cuadro de diálogo mostrado podrás cambiar la función que deseas utilizar:

Los valores disponibles son los siguientes: • • • • • • • • • •

Cuenta. Contabiliza el número de registros. Éstas es la opción predeterminada si el campo es de tipo texto (en lugar de la Suma). Promedio. Obtiene el promedio de los elementos. Máx. Obtiene el valor numérico más alto. Mín. Obtiene el valor numérico más bajo. Producto. Multiplica todos los valores numéricos de ese campo, si existe algún campo de texto será ignorado. Contar números. Obtiene el número de registros con valor numérico. Desvest. Obtiene la desviación estándar. Desvestp. Obtiene la desviación estándar en función de la población. Var. Calcula la varianza de una muestra. Varp. Calcula la varianza en función de la población.

Al aceptar los cambios Excel aplicará de inmediato el nuevo tipo de cálculo seleccionado. También es posible modificar la función de agregación al dar doble click sobre la columna y seleccionar Resumir valores por:

Cambiar origen de datos de una tabla dinámica Haz clic sobre la tabla dinámica y selecciona el comando Cambiar origen de datos que se encuentra en la ficha Analizar dentro del grupo Datos.

Se mostrará el cuadro de diálogo Cambiar origen de datos de tabla dinámica el cual te permitirá ampliar (o reducir) o cambiar el rango de los datos de la tabla dinámica.

Acepta los cambios y la tabla dinámica reflejará el nuevo rango de datos que has especificado.

22. Gráficos básicos y de dos ejes Los gráficos en Excel son objetos que podemos crear en el momento que lo necesitemos y que representan visualmente una o varias series de datos numéricos. Dependiendo el tipo de gráfico que utilicemos será la apariencia de cada una de las series. En la siguiente imagen puedes observar un gráfico de líneas con dos series de datos:

La información de cada serie está almacenada en una columna diferente. La línea azul representa los datos de la columna Ingresos y la línea roja los datos de la columna Egresos. Además, cada pequeño círculo sobre la línea representa el valor de cada una de las celdas en los datos. Es así como un gráfico está vinculado a la información contenida en una hoja de Excel y si modificamos los datos, entonces el gráfico se actualizará automáticamente para reflejar los cambios.

Tipos de gráficos Elegir el tipo de gráfico adecuado en Excel es de suma importancia porque de ello dependerá la claridad con la que se transmitirá el significado de los datos. Por esta razón es importante conocer todos los tipos de gráficos en Excel para poder hacer la elección adecuada Tipo Gráfico de columnas

Descripción y Uso El gráfico de columnas despliega una columna para cada uno de los datos. La altura de la columna dependerá del valor numérico asociado y la escala de dichos valores se mostrará en el eje vertical. Es muy útil para comparar valores entre diferentes categorías y Excel nos permite elegir entre 7 posibles variantes.

Muestra

Tipo Gráfico de barras

Descripción y Uso Un gráfico de barras muestra lo mismo que un gráfico de columnas solo que con los ejes invertidos. Una ventaja de utilizar este tipo de gráfico en Excel es que las etiquetas pueden ser leídas con mayor facilidad al presentarse sobre el eje vertical.

Gráfico de líneas

El gráfico de líneas es de mucha utilidad para identificar visualmente tendencias en nuestros datos a través del tiempo. Generalmente colocamos la unidad de tiempo en el eje horizontal y los valores en el eje vertical. En este tipo de gráfico se puede agregar un marcador para que sean mostrados como círculos, cuadrados o triángulos entre otros El gráfico circular o gráfico de pastel es útil cuando queremos mostrar la proporción de varias partes respecto a un todo. Utiliza solamente una serie de datos y es recomendable utilizarlo cuando dicha serie tenga como máximo cinco o seis valores o de lo contrario se comenzará a dificultar su visualización.

Gráfico circular

Gráfico de áreas

Gráfico de dispersión

El gráfico de áreas es un gráfico de líneas donde el área que se encuentra por debajo de ella se rellena con un color sólido. En el siguiente ejemplo puedes observar una gráfica de área apilada. Al igual que con otros tipos de gráficos en Excel, podremos seleccionar un gráfico de áreas para dos o tres dimensiones El gráfico de dispersión tiene como característica principal que tanto el eje horizontal como el vertical muestran valores numéricos y por lo tanto es frecuentemente utilizado para desplegar la relación que existe entre dos variables.

Muestra

Tipo Gráfico de burbujas

Descripción y Uso El gráfico de burbujas es muy similar al gráfico de dispersión ya que muestra valores numéricos en ambos ejes pero además puede representar una serie de datos adicional que será representada por el tamaño de la burbuja.

Gráfico radial

El gráfico radial es útil para mostrar valores relativos a un punto central. El gráfico radial también es conocido como gráfico de araña por su similitud visual con una telaraña.

Muestra

Gráfico de superficie Un gráfico de superficie nos permite graficar funciones matemáticas de dos variables de una manera sencilla, solo debemos generar los datos para ambas variables y obtendremos el resultado esperado.

Gráfico de cotizaciones

El gráfico de cotizaciones fue pensado para mostrar información sobre el mercado de acciones considerando valores numéricos como el volumen de acciones negociadas, el precio de apertura y de cierre así como el precio máximo y mínimo durante la sesión.

Selección de datos para crear un gráfico Si nuestros datos se componen de varias columnas de las cuales solo queremos graficar una sola de ellas, entonces será necesario hacer la selección explícita de todos los datos a graficar. En la siguiente imagen puedes notar que he hecho la selección solo de la columna Mes y de la columna Ingresos:

Después de hacer la selección anterior pulsaré el comando Insertar > Gráficos > Gráfico de líneas > Línea con marcadores y el resultado será el siguiente:

Es importante mencionar que la primera columna será considerada como las categorías de los datos siempre y cuando dicha columna tenga datos de tipo texto. Estas categorías las puedes ver reflejadas en las etiquetas del eje horizontal. Si en lugar de graficar la columna Ingresos queremos graficar la columna Egresos, entonces debemos seleccionar ambas columnas utilizando la tecla Ctrl la cual nos permite elegir múltiples rangos que no son adyacentes. Después de seleccionar la primera columna pulsa la tecla Ctrl y haz la selección de la segunda columna:

El resultado de la selección de datos anterior es una gráfica de la columna Egresos:

Es así como la selección de datos adecuada nos permite graficar la información deseada. Recuerda que si deseas graficar todo el rango de datos entonces es suficiente con seleccionar una sola celda dentro del rango.

Pasos para crear un gráfico en Excel Crear un gráfico en Excel es tan sencillo como seguir los siguientes dos pasos: 1. Selecciona una celda que pertenezca al rango donde se encuentran los valores numéricos. Nota que he mencionado que es suficiente hacer clic sobre una sola celda y no es necesario seleccionar todo el rango porque Excel incluirá automáticamente los datos de las celdas adyacentes. 2. Una vez hecha la selección, ve a la ficha Insertar > Gráficos y haz clic sobre el botón del tipo de gráfico que deseas insertar y se mostrará un menú donde deberás seleccionar el gráfico deseado.

Por ejemplo, para crear el gráfico de líneas mostrado en la sección anterior pulsé el botón Gráfico de líneas y posteriormente seleccioné la opción Línea con marcadores tal como lo ves en la siguiente imagen:

Con estos simples pasos habrás aprendido cómo hacer gráficos en Excel 2013. Tal vez la parte más complicada del proceso es saber elegir el gráfico adecuado para nuestros datos pero si te encuentras en esta situación, Excel 2013 tiene una nueva funcionalidad que nos ayudará en el proceso de selección de un gráfico.

Gráficos recomendados en Excel 2013 Cuando tienes duda sobre el gráfico que debes elegir para tus datos, puedes hacer uso del comando Gráficos recomendados de Excel 2013 el cual hará un análisis rápido de los datos para hacer una recomendación. Para utilizar este comando debes hacer clic sobre una celda dentro del rango de datos y posteriormente ir a Insertar > Gráficos > Gráficos recomendados y se mostrará un cuadro de diálogo con las recomendaciones de Excel:

En el panel izquierdo tendrás el listado de los gráficos recomendados y al seleccionar alguno de ello se mostrará una vista previa en el panel derecho. Una vez que hagas tu elección deberás pulsar el botón Aceptar para crear efectivamente el gráfico.

Cómo seleccionar un elemento de gráfico Antes de pasar al detalle sobre cómo modificar gráficos en Excel debo mencionar que existen diferentes alternativas para seleccionar los elementos que forman parte de un gráfico. Este paso es importante porque una vez que hayamos seleccionado el elemento podremos modificar sus propiedades. El primer método de selección que podemos utilizar es hacer clic directamente sobre el elemento lo cual hará que se muestre un borde alrededor con pequeños círculos en las esquinas para indicarnos que hemos seleccionado el elemento correctamente. Por ejemplo, en la siguiente imagen he seleccionado la leyenda del gráfico:

El método de selección con el ratón es el más intuitivo pero en ocasiones se dificulta seleccionar ciertos elementos del gráfico que son muy pequeños o simplemente tenemos duda sobre el elemento que hemos seleccionado. Cuando tenemos dificultad para seleccionar un elemento podemos hacer uso del control de selección que se encuentra en la ficha Formato y dentro del grupo Selección actual.

La lista mostrará todos los elementos del gráfico y será suficiente con elegir el elemento deseado para hacer la selección. Este mismo listado de elementos se mostrará cuando hacemos clic derecho sobre el gráfico.

Controles para modificar los elementos de un gráfico Una vez que hemos seleccionado un elemento de un gráfico podemos acceder a todas sus propiedades pulsando la combinación de teclas CTRL + 1 que mostrará el panel de tareas donde podremos hacer cambios al elemento seleccionado.

Otra manera de acceder al panel de tareas de una elemento es hacer clic derecho sobre él y seleccionar la última opción de menú que tiene un nombre que generalmente comienza como “Dar formato a”. Otra alternativa que tenemos para abrir el panel de tareas es utilizar el botón Aplicar formato a la selección que se encuentra en la ficha Formato y dentro del grupo Selección actual. También tenemos acceso a las configuraciones más comunes para cada elemento a través de los botones que se muestran a un lado del extremo superior derecho del gráfico seleccionado.

Los botones mostrados son los siguientes: • • •

Elementos de gráfico: permite agregar, quitar o cambiar elementos del gráfico. Estilos de gráfico: muestra diferentes esquemas de color y estilo para el gráfico. Filtros de gráficos: podemos elegir las series de datos y puntos que estarán visibles en el gráfico.

Al hacer clic sobre el botón Elementos de gráfico observaremos una lista de elementos con una caja de selección que nos permitirá ocultar o mostrar cada uno de ellos. Además, al pasar el puntero del ratón sobre la lista se mostrará una flecha a la derecha de cada elemento y al hacer clic se mostrarán más opciones de configuración.

La última opción del menú mostrado será siempre “Más opciones” la cual abrirá el panel de tareas para darnos acceso a más posibilidades de configuración. Además de los controles mencionados anteriormente, también habrá comandos ubicados tanto en la cinta de opciones como en los controles que se muestran al hacer clic derecho sobre cualquier elemento de gráfico. A continuación revisaremos los pasos para realizar algunas de las modificaciones más comunes a un gráfico de Excel.

Modificar el área del gráfico El área del gráfico es la que contiene a todos los demás elementos y podemos modificar su color de fondo o dar algún efecto de relleno. Solo selecciona el gráfico y pulsa CTRL + 1 para mostrar el panel de tareas, ve a la sección Relleno y línea para seleccionar algún tipo de relleno.

Además del relleno podemos modificar el borde del área del gráfico indicando el tipo de línea que deseamos utilizar así como su color.

Modificar el área de trazado El área de trazado es el área que contiene al gráfico, es decir, el área donde son dibujados los datos y a través del panel de tareas podemos modificar tanto su relleno como su borde. En la siguiente imagen puedes observar el área de trazado con un color de relleno sólido:

Modificar el título del gráfico Cada gráfico de Excel puede contener un título que ayude a describir el significado de los datos mostrados. Dicho título es una etiqueta de texto y podemos modificar su estilo de borde y su color de relleno. Sin embargo, la modificación más común del título del gráfico es su posición que puede ser Encima del gráfico o Superpuesto.

También es posible arrastrar el título con el ratón hasta la posición deseada dentro del gráfico. Y ya que el título del gráfico es una etiqueta de texto podemos modificar su valor haciendo clic sobre el contenido e introduciendo el nuevo texto. Para remover el título del gráfico podemos seleccionarlo y pulsar la tecla Suprimir o podemos quitar la marca de selección del listado del botón Elementos de gráfico.

Modificar la leyenda del gráfico La leyenda del gráfico es la llave que nos permite identificar por color cada una de las series de datos graficadas y el panel de tareas nos permite elegir la posición de la leyenda dentro del gráfico:

Si quitamos la selección de la opción Mostrar la leyenda sin superponerla al gráfico, entonces la leyenda se mostrará sobre el área de trazado. También es posible mover la leyenda utilizando el ratón para colocarla en la posición exacta donde la necesitamos.

Modificar las líneas de cuadrícula Algunos tipos de gráficos en Excel nos permiten agregar líneas de división para identificar fácilmente la magnitud de los valores graficados. Al ingresar a las propiedades de dichas líneas de cuadrícula podremos modificar el tipo de línea utilizado, su color, transparencia, ancho entre otros.

Líneas de tendencia en Excel Las líneas de tendencia son una herramienta de análisis que permite visualizar puntos en una gráfica que van más allá de los datos reales y que representan los posibles valores futuros de acuerdo a su tendencia. Supongamos que tenemos las ventas de los primeros meses del año:

Al graficar estos datos obtenemos el siguiente resultado:

Identificamos una tendencia a la alza, así que nos gustaría predecir las posibles ventas en los próximos 3 meses. Para lograrlo debes hacer clic derecho sobre la línea graficada y dentro del menú emergente seleccionar la opción Agregar línea de tendencia.

Se mostrará el cuadro de diálogo Formato de línea de tendencia. Solamente debes asegurarte de especificar el número de períodos que deseas pronosticar (extrapolar).

Al cerrar el cuadro de diálogo Excel habrá insertado una línea de tendencia en el gráfico.

23. Proteger hojas y libros Proteger un libro Al proteger la estructura de un libro se evita que el usuario pueda realizar tareas como agregar, eliminar, mover o renombrar hojas. Con esta herramienta nos aseguraremos que la estructura de las hojas del libro, se mantengan inalterables hasta que introduzcamos la contraseña para poder hacer modificaciones. La protección de un libro se compone de dos opciones. Se puede seleccionar una de ellas o ambas, y así únicamente bloquearemos lo que necesitemos: •

•

Estructura: permite proteger la composición de un libro impidiendo lo siguiente: o Crear o eliminar hojas y que se oculten las existentes. o Renombrar, copiar, mover o cambiar el color de sus etiquetas. o Si alguna hoja se encuentra oculta, la opción de mostrarla estará desactivada. Ventanas: no permite modificar la forma de ver las hojas de cálculo en la pantalla. Así algunas funciones del grupo Ventana, de la ficha Vista, quedarán deshabilitadas, tales como Nueva ventana, Inmovilizar, Dividir y Mostrar. Con esta opción no se puede cambiar la vista de las ventanas, que es importante si necesitamos trabajar con varias hojas.

Para proteger un libro, tenemos que ir a la ficha Revisar, y seleccionar Proteger libro. Se nos abrirá el cuadro de Proteger estructura y ventanas, con las opciones Estructura y Ventanas. Seleccionamos la que queramos proteger. Luego tenemos la opción de incluir una contraseña. Cuando hayamos terminado le damos a Aceptar.

Otras opciones de protección Se pueden realizar distintas acciones para proteger el contenido de una hoja de Excel. Se pueden especificar los elementos que se pueden modificar y cuáles no. En Excel, por defecto todas las celdas están bloqueadas, así, al seleccionar Proteger hoja, no se podrá modificar ninguna parte de la hoja. Aunque si queremos dejar algunas celdas sin protección, para que los usuarios puedan modificar datos, debemos seleccionar esas celdas, luego ir a la ficha Inicio, al grupo Celdas, y pinchar en Formato. Se nos desplegará un menú con diferentes opciones, y pincharemos en Bloquear celda, para que

quede desmarcada esta opción. Así, aunque la hoja esté protegida, estas celdas que hemos seleccionado, podemos modificarlas.

Después de seleccionar las celdas que se pueden modificar, tenemos que proteger la hoja de cálculo. Iremos a Formato y Proteger hoja. Se nos abrirá el cuadro diálogo de Proteger hoja, donde tenemos que configurar las opciones de la hoja. Tenemos que marcar la casilla Proteger hoja y contenido de celdas bloqueadas, y escribir una contraseña. Le damos a Aceptar y volvemos a introducir la contraseña y Aceptar.