excel nivel ii
TRANSCRIPT
CONTENIDO
CONTENIDO....................................................................................................................................... 1
OBJETIVO........................................................................................................................................... 7
CARACTERÍSTICAS NUEVAS......................................................................................................... 8
FICHAS Y GRUPOS............................................................................................................................ 9
TABLAS DE DATOS........................................................................................................................ 12
Introducción............................................................................................................................................. 12
Nomenclatura de las tablas de datos.......................................................................13
Recomendaciones para crear una tabla de datos consistente.................................14
Captura de registros................................................................................................15
Ingresar registros directamente en la Hoja de Cálculo.............................................15
Ingresar registros por medio de un Formulario........................................................15
Crear una Tabla de Datos..................................................................................................................... 18
Modificar datos de una tabla...............................................................................................................19
Modificar la estructura de la tabla....................................................................................................22
Inmovilizar Paneles..................................................................................................23
Estilo de la Tabla..................................................................................................................................... 25
Ordenar una Tabla de Datos............................................................................................................... 26
Filtrar una tabla...................................................................................................................................... 28
Utilizar el filtro (Autofiltro)........................................................................................28
Utilizar filtros avanzados..........................................................................................32
Resumen de datos...................................................................................................33
Criterios de filtrado..................................................................................................34
Subtotalizar una Base de Datos..........................................................................................................35
Eliminar Subtotales de una Base de Datos...............................................................38
TABLAS DINÁMICAS..................................................................................................................... 38
Crear una tabla dinámica..................................................................................................................... 38
Realizar cambios a una Tabla Dinámica.........................................................................................44
Aplicar Filtros a una Tabla Dinámica...............................................................................................44
Obtener promedios en una Tabla Dinámica..................................................................................45
Gráficos con Tablas Dinámicas.......................................................................................................... 46
CARACTERÍSTICAS AVANZADAS DE EXCEL 2007...............................................................47
Maneras de aplicar un formato..........................................................................................................47
Diferentes formas de cambiar un formato...............................................................47
Definir Formatos Personalizados..................................................................................................... 50
Agrupar hojas de cálculo...................................................................................................................... 53
Colocar Niveles a una Hoja de Trabajo.....................................................................53
Quitar Niveles de columnas y filas...........................................................................55
Borrar esquema.......................................................................................................55
FUNCIONES AVANZADAS...................................................................................................................... 56
Funciones Lógicas................................................................................................................................... 56
Función Lógica SI.....................................................................................................56
Función Lógica SI Y..................................................................................................58
Función Lógica SI O..................................................................................................60
El formato condicional.......................................................................................................................... 62
Borrar un formato condicional.......................................................................................................... 66
Funciones de texto.................................................................................................................................. 67
Función Concatenar.................................................................................................67
Función Mayúscula...................................................................................................69
Función Minúscula....................................................................................................71
Función Nompropio..................................................................................................73
Función espacios......................................................................................................75
Función extraer........................................................................................................76
Función encontrar....................................................................................................79
Funciones de búsqueda y referencia................................................................................................80
Función BUSCARH....................................................................................................81
Función BUSCARV....................................................................................................85
Funciones de Bases de Datos.............................................................................................................. 90
Establecer las condiciones.......................................................................................92
AUDITORÍA..................................................................................................................................... 95
Buscar Objetivo....................................................................................................................................... 95
Solver.......................................................................................................................................................... 99
Para activar el solver:............................................................................................100
Utilizando el Solver................................................................................................102
Administrador de Escenarios...........................................................................................................106
Crear un Escenario.................................................................................................107
Escenario Original..................................................................................................107
Escenario de ganancias o pérdidas........................................................................109
Visualizar los escenarios........................................................................................111
Crear un informe de resumen de datos..................................................................113
La Validación de Datos....................................................................................................................... 115
Rastrear Datos Inválidos........................................................................................118
Enlazar y Consolidar Hojas............................................................................................................... 119
Enlazar Hojas de Trabajo........................................................................................119
Vincular manualmente...........................................................................................121
Vincular con Pegado Especial.................................................................................122
Como crear formulas de referencias externas.......................................................123
Auditar fórmulas.................................................................................................................................. 125
Consolidar hojas de trabajo....................................................................................129
Excel e Internet..................................................................................................................................... 131
Convertir una hoja de Excel 2007 a una página Web.............................................131
Macros...................................................................................................................................................... 132
Grabar una macro..................................................................................................133
Guardar un archivo con macros.............................................................................135
Ejecutar una Macro................................................................................................137
Modificar una Macro...............................................................................................139
Eliminar una macro............................................................................................................................. 139
Establecer Seguridad en la macro..........................................................................142
Crear botón de macro............................................................................................143
Crear una Macro de forma Manual.........................................................................145
Guardar una área de trabajo............................................................................................................ 147
Hipervínculos........................................................................................................................................ 150
Modificar un hipervínculo.......................................................................................152
Eliminar un hipervínculo........................................................................................152
Contraseñas y bloqueos de Celdas..................................................................................................152
Establecer contraseña de apertura a un libro........................................................152
Proteger hojas de cálculo.......................................................................................156
Desproteger celdas de una hoja de cálculo............................................................158
OBJETIVO
Dar a conocer las últimas novedades introducidas por la nueva versión 2007 del programa. Modificando tanto su
aspecto externo como sus procedimientos de trabajo.
Se hará uso de las opciones avanzadas de Excel, al tratar grandes cantidades de datos, y poder realizar con ellos
análisis válidos para una adecuada gestión de la información. Además de para todos aquellos, que teniendo ya
conocimientos de Excel, deseen ponerse al día de las últimas novedades que introduce la última versión 2007.
Se comenzará a aprender el manejo de las Macros, que son herramientas que permiten la automatización de los
procesos, aprenderemos a realizar macros de forma automática y manual hasta lograr dominar su
funcionamiento básico.
CARACTERÍSTICAS NUEVAS
La más notable es la nueva interfaz gráfica llamada Office Fluent, también conocido como cinta de opciones, que
reemplaza al menú y a la barra de herramientas que fueron características desde su inicio.
Las grandes dimensiones de sus hojas de cálculo. Office 2007 ha ampliado el número de celdas disponibles,
admite hasta 1 millón de filas y 16.000 columnas por hoja de cálculo, en concreto tiene 1.048.576 filas por
16.384 columnas, ahora las columnas acaban en XFD en lugar de acabar en IV.
La agrupación de varias hojas de cálculo en un libro, lo que aumenta las posibilidades de trabajo. Podríamos
utilizar hasta 255 hojas por cada archivo de Excel.
Realización de distintos tipos de gráficos a partir de los datos introducidos en la hoja de cálculo, con la posibilidad
de insertarlos en la misma hoja de cálculo o en hojas aparte, pudiendo presentar ambas informaciones juntas o
separadas.
Trabajar con la información de una base de datos introducida en la hoja de cálculo mediante operaciones que
serían propias de un Gestor de Base de Datos como Access.
Barra de Herramientas de Acceso Rápido
El Botón de Microsoft Office
Nuevo formato de documento basado en XML
En esta versión, Office clasifica varias partes del archivo (contenido, formato, gráficos, etc.) en diversos archivos y
después los comprime en un solo archivo de XML. Puesto que un documento se salva de esta manera, un usuario
con experiencia puede modificar el formato de una serie de documentos sin afectar el contenido del texto o
viceversa. Además, la compresión hace archivos más pequeños, así que almacenar y el transmitir archivos es más
fácil.
FICHAS Y GRUPOS
En Excel contamos con una nueva forma de acceder a las opciones del programa, y esto es mediante fichas que
contienen grupos, estos grupos relacionan las funciones más utilizadas y las dejan más accesibles que en la
versión anterior del programa.
FichasMini Barra
Grupo
Existen 8 fichas básicas: Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar, Vista, Programador y Puede
contar con Complementos así como otros adicionales que dependiendo la instalación de Office 2007 y otros
programas relacionados puede o no tener.
Adicionalmente la versión 2007 cuenta con una Mini Barra en la parte superior, en la cual se pueden agregar los
comandos más utilizados
Para agregar o eliminar elementos de la Mini Barra:
Haz clic en el botón localizado a la derecha de la barra
Selecciona el o los botones que deseas aparezcan en la barra, marcándolos con una
En caso que requieras otros comandos no listados ahí, selecciona Más comandos
Seleccionar el elemento
a agregar
Agregar a
la Barra
Aquí se queda
agregado
Para agregar algún comando de la lista es necesario seleccionarlo, luego presionar el botón . De la
misma forma para eliminar algún elemento se selecciona de la parte derecha de la ventana y se presiona el
botón. . Se puede acomodar el orden de los comandos en la Barra utilizando los botones localizados
en la parte derecha de esta misma pantalla.
NOTA: Algunos de los botones solicitados se encontrarán bajo la opción de “Comandos que no se
encuentran en la cinta de opciones” de la ventana Opciones de Excel.
TABLAS DE DATOS
Introducción
Microsoft Excel 2007 por su gran capacidad en el manejo de datos es muy utilizado como plataforma de base de
datos, por lo que cuenta con algunas herramientas para cuando se usan las hojas de este modo.
Una tabla de datos, también es parte de lo que se conoce como base de datos, y es el conjunto de datos
relacionados que pertenecen al mismo contexto, almacenados sistemáticamente para su uso posterior. Ejemplos
de ello se encuentran en los directorios telefónicos, que contienen una relación de nombres, direcciones y
teléfonos lo que permiten su rápida ubicación y uso. Otros ejemplos que se pueden mencionar son las fichas
bibliográficas, catálogos e inventarios de productos, listas de calificaciones, entre otros.
El uso de las tablas de datos permite realizar consultas, ordenar la información y filtrar los registros, además de
otro tipo de operaciones.
Nomenclatura de las tablas de datos
La forma como Excel 2007 organiza la información es por medio de: Campos, Registros y Datos, que ya en
conjunto se denominan tabla de datos.
Campos: Son las categorías que se requieren en la tabla de datos. Se representan como encabezados de
la tabla y se muestran en columnas.
Registros: Es el conjunto de datos que cumplen con todos los campos para un elemento, se organizan en
filas o renglones.
Datos: Cualquier letra, número, signo o imagen que pos si solo no nos define información.
Registr
Columnas / Campos
Recomendaciones para crear una tabla de datos consistente
Antes de capturar una Tabla de Datos en Excel 2007 se recomienda organizar la información y asegurarse que
esté completa y actualizada, además de realizar una planeación sobre qué elementos vas a encontrar dentro de
ella.
Para crear una tabla de datos y manejarla lo mejor posible se recomienda:
Fijar un objetivo.- En este proceso se determina que información va a contener y cual no la tabla de
datos, estableces límites del contenido. Entre más pequeña sea la tabla de datos, más fácil será de
manejar.
Crear buenos catálogos de información.- Dentro de ese proceso tienes que verificar que la información
que vas a colocar dentro de la tabla de datos está actualizada, revisada y que es fidedigna.
Información Relacionada.- Cuando creas una tabla de datos esperas encontrar información relacionada,
de no ser así, creas otra tabla de datos, no se trata de colocar muchísimos campos, más bien la idea es
incluir aquellos que vayan acorde con la información que deseas mostrar.
Captura de registros
Para comenzar a ingresar registros en una tabla de datos puedes seguir estas recomendaciones:
1. Comienza la captura de los encabezados de los campos en la celda A1
2. Utiliza campos separados para cada dato, ejemplo: nombre, apellido paterno, materno, entre otros.
3. No es recomendable dejar campos vacios, siempre coloca información relevante. Para indicar un estatus
escribe Pendiente, o N/A, la idea es no dejar solo ningún campo.
4. Crea una tabla de datos por hoja.
5. Procura no incluir otra información adicional (gráficos, otras tablas, imágenes)
Ingresar registros directamente en la Hoja de Cálculo
Este método es el más utilizado, consiste en ubicar el cursor en la primera celda de la fila dónde vas a comenzar a ingresar registros e irlos capturando directamente en la celda de cada campo correspondiente.
Ingresar registros por medio de un Formulario
Un formulario es una herramienta que puedes también utilizar para introducir registros nuevos dentro de una base de datos, así como eliminarlos, buscarlos e ir visualizando la información a través de cada registro individual.
Cuando utilizas el formulario se pueden dar dos situaciones:
1.- Que no existan registros dados de alta en la Hoja de Cálculo.
Cuando no has capturado ningún registro y tienes solo los campos en la Hoja de Cálculo, al seleccionar la herramienta formulario para comenzar a llenar la Tabla de Datos se despliega el siguiente mensaje indicando
que no existen registros dados de alta, por lo que debes dar clic en el botón para que aparezca el formulario.
2.- Que sí existan registros dados de alta en la Hoja de Cálculo
En este caso te aparece el registro número 1, para continuar ingresando registros, da clic en el botón Nuevo del formulario.
Para activar el formulario haz lo siguiente:
1. Coloca tu cursor en una celda de la Tabla de Datos, de preferencia en el encabezado.
2. De la mini barra de menú elige el botón (ver ubicación abajo)
NOTA: Las opciones que aparecen en la mini barra de herramientas son seleccionadas y mostradas
presionando el botón , ubicado al lado derecho de la misma.
Más delante explicaremos las formas de agregar nuevos registros, buscar, eliminar y finalmente cerrar el formulario.
Botón Formulario
Agregar Botones
A la mini barra
Crear una Tabla de Datos
Para crear una tabla de datos seguimos los siguientes pasos:
Seleccionar el rango de celdas (con datos o vacías) que queremos incluir en la tabla.
Seleccionar Tabla en la ficha Insertar.
Aparecerá a continuación el cuadro de diálogo Crear Tabla
Si no habíamos seleccionado las celdas de la tabla de datos previamente, lo podemos hacer en este momento.
Si en el rango seleccionado hemos incluido los encabezados (recomendado) activaremos la casilla de verificación: La tabla tiene encabezados.
Haz clic en aceptar.
Al cerrarse el cuadro de diálogo, podemos ver que en la banda de Opciones aparece la pestaña Diseño, correspondiente a las Herramientas de tabla.
Y en la hoja de cálculo aparece el rango seleccionado con el formato propio de la tabla.
Modificar datos de una tabla
Para modificar o introducir datos en la tabla podemos teclear directamente los valores sobre ella, o también podemos utilizar un formulario de datos. Esta segunda opción viene muy bien sobre todo si la lista es muy grande.
Por ejemplo si tuviera una lista de amigos que capturar.
Un formulario de datos es un cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos (un registro).
Para abrir el formulario de datos, tenemos que posicionarnos en la lista para que esté activa y pulsar el icono
Formulario
Como esta opción no está directamente disponible en la Cinta de Opciones, podemos abrirla de la barra de acceso rápido (mini barra), de la forma mencionada en el apartado Fichas y Grupos que se encuentra al principio de este manual
Pulsando el Botón Office > Opciones de Excel > Personalizar y Agregar el icono Formulario, en la sección de comandos que no están en la cinta de opciones.
Al crear el formulario, disponemos de los siguientes botones:
Eliminar: Eliminar el registro que está activo. Restaurar: Deshace los cambios efectuados. Buscar Anterior: Se desplaza al registro anterior. Buscar Siguiente: Se desplaza al siguiente registro. Criterios: Sirve para aplicar un filtro de búsqueda. Cerrar: Cierra el formulario.
Para cambiar los datos de un registro, primero nos posicionamos sobre el registro, luego rectificamos los datos que queramos (para desplazarnos por los campos podemos utilizar las teclas de tabulación), si nos hemos equivocado y no queremos guardar los cambios, hacemos clic en el botón Restaurar, si queremos guardar los cambios pulsando la tecla Intro.
Para crear un nuevo registro, hacemos clic en el botón Nuevo, Excel 2007 se posicionará en un registro vacío, sólo nos quedará rellenarlo y pulsar Intro o Restaurar para aceptar o cancelar respectivamente.
Después de aceptar, Excel 2007 se posiciona en un nuevo registro en blanco por si queremos insertar varios registros, una vez agregados los registros, haces clic en Cerrar.
Para buscar un registro y posicionarnos en él podemos utilizar los botones Buscar anterior y Buscar Siguiente o ir directamente a un registro concreto introduciendo un criterio de búsqueda. Pulsamos en el botón Criterios con lo cual pasamos al formulario para introducir el criterio de búsqueda, es similar al formulario de datos pero encima de la columna de botones aparece la palabra Criterios.
Por ejemplo si buscamos un registro con el valor Ana en el campo Nombre, escribimos Ana en el Nombre y pulsamos el botón Buscar Siguiente, Excel 2007 vuelve al formulario de datos y nos posiciona en el registro de nombre Ana.
Modificar la estructura de la tabla
Pulsando en el icono Cambiar tamaño de la tabla, podemos seleccionar un nuevo rango de datos. Pero si la tabla contiene encabezados, estos deben permanecer en la misma posición, así que sólo podremos aumentar y disminuir filas.
Podemos modificar directamente el rango de filas y columnas, estirando o encogiendo la tabla desde su esquina
inferior derecha
NOTA: Cuando necesitemos añadir un renglón al final de la tabla para continuar introduciendo datos, solo
tendremos que pulsar la tecla Tab desde la última celda y aparecerá un renglón nuevo. Por esta razón no es
necesario conocer desde el principio cuantos renglones se requerirán.
Si necesitamos insertar filas y columnas entre las filas existentes de la tabla, nos posicionaremos en la primera celda que queremos desplazar y elegiremos la opción correspondiente desde el menú Insertar, en la pestaña de Inicio o en el menú contextual de la tabla.
Para eliminar filas o columnas, deberemos posicionarnos sobre una celda, y elegiremos Filas (renglones) o Columnas de la tabla en el menú Eliminar, disponible en la pestaña de Inicio y en el menú contextual de la tabla.
Seleccionando una celda, renglón o columna y pulsando la tecla Supr, eliminamos los datos seleccionados, pero no la estructura de la tabla.
Para eliminar la tabla completa, seleccionamos toda la tabla y pulsamos Supr. Si deseamos eliminar la estructura
de la tabla, pero conservar los datos de la hoja, entonces pulsamos en la pestaña Diseño de la tabla.
Inmovilizar Paneles
Esta opción divide la hoja en secciones que te permiten visualizar diferentes partes de la hoja sin moverse. Por ejemplo si deseas que los encabezados de la base de datos queden inmóviles realiza lo siguiente:
Colócate en la celda inmediata bajo el encabezado de la base de datos. De la ficha Vista elige la opción Inmovilizar paneles.
Desplázate hacia abajo y podrás observar que no se mueve el encabezado, aunque llegues a la última fila de la hoja.
Para movilizar los paneles selecciona la opción: del botón Inmovilizar paneles.
Estilo de la Tabla
Una forma fácil de dar una combinación de colores a la tabla que resulte elegante, es escogiendo uno de los estilos predefinidos, disponibles en la pestaña Diseño de la Tabla.
En Opciones de estilo de la tabla, podemos marca o desmarcar otros aspectos, como que las columnas o filas aparezcan remarcadas con bandas, o se muestre un resaltado especial en la primera o última columna. Las bandas y resaltados dependerán del estilo de la tabla.
Por lo demás, a cada celda se le podrán aplicar los colores de fuente y fondo, fondo condicional, etc. Que a cualquier celda de la hoja de cálculo.
En esta tabla, se ha cambiado el estilo.
NOTA: Cuando convertimos un rango de datos a tabla, automáticamente se añaden los marcadores de
autofiltro, mismos que
pueden ser removidos presionando el botón en la ficha de Datos.
Ordenar una Tabla de Datos
A la hora de ordenar una tabla. Excel 2007 puede hacerlo de forma simple, es decir, ordenar por un único campo la lista por diferentes campos a la vez.
Para hacer una ordenación simple, por ejemplo ordenar la lista anterior por el primer apellido, debemos posicionarnos en la columna del primer apellido, después podemos acceder a la pestaña Datos y pulsar sobre
Ordenar… y escoger el criterio de ordenación o bien pulsar sobre uno de los botones de la sección Ordenar y filtrar para que la ordenación sea ascendente o descendente respectivamente. Estos botones también están disponibles al despegar la lista que aparece pulsando la pestaña junto al encabezado de la columna.
Para ordenar la lista por más de un criterio de ordenación, por ejemplo para ordenar la lista por el primer apellido mas la fecha de nacimiento, en la pestaña Datos, pulsamos sobre Ordenar, luego
nos aparece el cuadro de dialogo Ordenar donde podemos seleccionar los campos por los que queremos ordenar (presionando Agregar nivel para añadir un campo), si ordenamos según el valor de la celda, o por su color o ícono (en Ordenar según), y el Criterio de ordenación, donde elegimos si el orden es alfabético (A a Z o Z a A) o sigue el orden de una lista personalizada. Por ejemplo, si en la columna de la tabla se guardan los nombres de los días de la semana o meses, la ordenación alfabética no sería correcta, y podemos escoger una lista donde se guarden los valores posibles, ordenados de la forma que creamos conveniente, y así el criterio de ordenación seguirá el mismo patrón.
Seleccionando un nivel, y pulsando las flechas hacia arriba o hacia abajo, aumentamos o disminuimos la prioridad de ordenación de este nivel. Los datos se ordenarán, primero, por el primer nivel de la lista y sucesivamente por los demás niveles en orden ascendente.
En la parte superior derecha tenemos un botón Opciones, este botón sirve para abrir el cuadro Opciones de ordenación donde podremos especificar más opciones en el criterio de la ordenación.
Filtrar una tabla
Consiste en seleccionar los registros que corresponden a algún criterio que hemos elegido.
Excel 2007 nos ofrece dos formas de filtrar una lista.
Utilizando el filtro (autofiltro) Utilizando filtros avanzados
Utilizar el filtro (Autofiltro)
Para utilizar el Filtro nos servimos de las listas desplegables asociadas a los encabezados de los
campos (podemos mostrar u ocultar el Autofiltro en la pestaña Datos marcando o desmarcando el
botón Filtro.
Si presionamos sobre la primera flecha del campo Puesto, nos aparece un
menú desplegable como este, donde se nos muestran las opciones que
permiten configurar el filtro.
Por decir, si solo elegimos Secretaria, Excel 2007 filtrará todos los registros de
Secretarias y los demás registros no estarán visibles en la Tabla de Datos.
Otra opción, es usar los filtros de texto, donde se despliegan las siguientes
opciones:
En cualquier opción, accedemos a una ventana donde podemos elegir dos condiciones de filtro de texto, y
condicionar que se cumpla una condición o las dos. Excel evaluará la condición elegida con el texto que
escribamos, y si se cumple se mostrará la el renglón. Utilizaremos el carácter ¿ para determinar que en esta
posición habrá un carácter, sea cual sea, y el asterisco * para indicar que puede haber o no un grupo de
caracteres.
En el ejemplo de la imagen, solo se mostrarán los registros que contengan Almacenista o Capturista en el campo
Puesto.
Para indicarnos que hay un filtro activo, la flecha de la lista desplegable cambia de icono.
Para quitar el filtro, volvemos a desplegar la lista y elegimos la opción (seleccionar todo), reaparecerán
todos los registros de la lista. También podemos quitar el filtro pulsando en Borrar filtro
en la pestaña Datos.
TIP: Para visualizar (contar) el número de registros
- En la barra de status (parte inferior de la ventana) se muestran algunos totales de las celdas que seleccionas, tal
y como se muestra aquí:
Registros Seleccionados
Barra de Status
Utilizar filtros avanzados
Si queremos filtrar los registros de la lista por una condición más compleja, utilizaremos el cuadro de diálogo
Filtro Avanzado. Previamente deberemos tener en la hoja de cálculo unos renglones en donde indicaremos los
criterios del filtrado.
Para abrir el cuadro de diálogo Filtro Avanzado, pulsaremos en en la sección Ordenar y filtrar.
Rango de la lista.- Aquí especificamos los registros de la lista a la que aplicaremos el filtro.
Rango de Criterios.- Aquí seleccionamos los renglones en donde se
encuentran los criterios de filtrado (la zona de criterios).
Renglón con criterios
También podemos optar por guardar el resultado del filtrado en otro lugar, seleccionando la opción Copiar a: en
ese caso indicamos el rango de celdas que recibirán el resultado del filtrado.
Si marcamos la casilla Solo registros únicos, las repeticiones de registros (renglones con los valores iguales)
desaparecerán.
Para volver a visualizar todos los registros de la lista seleccionamos del menú Datos - filtro – Mostrar todo.
Resumen de datos
Cuando hablamos de crear un resumen de los datos de una tabla nos estamos refiriendo a crear subtotales
agrupando los registros por alguno de los campos de la lista.
Por decir si contamos con una lista de niños con los campos nombre, dirección, localidad y edad; podemos
obtener un resumen de la edad promedio de los niños por localidad.
Otro ejemplo es el disponer de una lista de teléfonos celulares clasificados por marca y modelo; y deseamos
averiguar el coste total de cada marca.
Para agregar los subtotales automáticamente debemos situarnos sobre una celda cualquiera de la lista y marcar la opción Fila de totales en las Opciones de estilo de tabla, en la pestaña Diseño.Al seleccionar una celda de la fila de totales, aparece una pestaña con una lista de las
funciones que podemos usar para calcular el total de esa columna.
Resultado de total por marca
Criterios de filtrado
En algún lugar de nuestra Base de Datos, reservamos un espacio para colocar las condiciones, a lo que se llama
zona de criterios.
Para elegir los criterios primero debemos identificar el valor a buscar, por ejemplo si deseamos encontrar a
“Juan”, debemos colocar el nombre de Juan bajo el encabezado Nombre.
Filtro aplicado a Marca
Cuando la condición es una igualdad no es necesario colocar el signo = delante del valor, escribimos
directamente el valor Juan.
Si deseamos obtener los registros cuyo valor sea mayor a 3, debemos escribir >3 bajo la celda correspondiente.
Para combinar varias condiciones se emplean los operadores Y y O.
En un criterio de filtrado, si las condiciones está escritas en la misma fila, estarán unidas por el operador Y. Para
que el registro aparezca se deben cumplir todas las condiciones.
En un criterio de filtrado, si las condiciones están escritas en distintas filas, estarán unidas por el operador O.
Para que el registro aparezca se debe de cumplir al menos una de las condiciones.
Subtotalizar una Base de Datos
Por medio de los subtotales puedes realizar funciones en una base de datos en registros numéricos tales como suma, mínimo, máximo, promedio, entre otros.
Agregar subtotales a una Base de Datos
Por ejemplo, en la Base de Datos de Empleados, se requiere realizar una suma para saber el Total que se paga a la quincena en cada departamento, por mencionar alguno cuando se paga en total al
Departamento de Sistemas, así como también se podría saber cuál es el sueldo mayor, el sueldo menor y el promedio de sueldo de dicho departamento.
Para agregar Subtotales realiza lo siguiente:
1.- Ordena la columna en forma ascendente o descendente por medio de la cual vas a realizar el subtotal, en este caso debes ordenar la columna Departamento.
2.- Selecciona de la ficha Datos la opción Subtotal.
3.- Debajo de da clic en la flecha
para que te muestre la lista de los campos y elige el campo por el cual se va a crear el subtotal, en este ejemplo utilizaremos el campo Departamento.
4.- Debajo de da clic en la flecha para
ver la lista de las funciones, en este caso será la función Suma.
5.- Debajo de activa la casilla al campo donde vas a agregar el subtotal, aquí sería
6.- Activa la casilla para que te muestre un resumen de los subtotales debajo de los datos.
7.- Da clic en para que veas la Base de Datos subtotalizada como lo muestra el siguiente ejemplo:
NOTA: Puedes ir agregando subtotales con otras funciones y mostrar ahí mismo los resultados. Para esto
solo selecciona otra función distinta a la que ya está calculada y desactiva la casilla
para que respete los que ya tienes creados.
Eliminar Subtotales de una Base de Datos
Para quitar los subtotales de una Base de Datos es cuestión de que actives de nuevo el cuadro de diálogo de
Subtotales y elijas la opción
TABLAS DINÁMICAS
Las tablas dinámicas son una herramienta que simplifica el sacar reportes en Excel 2007, haciendo uso de los
campos de forma flexible y configurable inclusive al momento que se requiera esta información.
Crear una tabla dinámica
Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación,
representando como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica
porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos,
visualizando o no los datos origen, etc.
Para crear una tabla dinámica a partir de una lista de datos existente:
Supongamos que tenemos una colección de datos de artículos de almacén con el número de referencia y el mes
de compra, además sabemos la cantidad comprada y el importe del mismo.
Crearemos una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en
cada mes.
Para ello vamos a la pestaña Insertar y hacemos clic en Tabla dinámica (también
podemos desplegar el menú haciendo clic en la flecha al pie del botón para crear un
gráfico dinámico).
Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí podremos indicar el lugar donde se
encuentran los datos que queremos analizar y el lugar donde
queremos ubicarla.
Indicamos el área donde se encuentran los datos y
posteriormente elegimos colocarla en una hoja de cálculo nueva.
Es posible crear una conexión con otra aplicación externa para
obtener los datos desde una fuente diferente a Excel 2007.
En el caso de seleccionar la opción Seleccione una tabla o rango debemos seleccionar todas las celdas que vayan
a participar, incluyendo los encabezados. Luego presionamos Aceptar.
Marca los campos ubicados en el lado derecho de la base de datos para agregarlos a la Tabla dinámica.
En este ejemplo elegimos los campos: Mes como rótulos de filas, Referencia, Cantidad, Importe y Total.
En la ventana Valores podemos cambiar el tipo de resultado que obtendremos para ese campo en particular, al hacer clic en el campo adecuado y elegir:
Ahí podemos seleccionar, Suma, Promedio, Máximo, etc.
Finalmente presionamos y nuestra Tabla Dinámica queda lista.
Realizar cambios a una Tabla Dinámica
Parte de los cambios que se pueden realizar se encuentran en la barra de Herramientas de tabla dinámica ubicada en la parte superior derecha de Excel 2007 y que nos muestra múltiples opciones de modificación de la tabla.
Aplicar Filtros a una Tabla Dinámica
Otra característica útil de las tablas dinámicas es que permiten filtrar los resultados y así visualizar únicamente
los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es
importante.
Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una
flecha indicando una lista desplegable.
Por ejemplo, si pulsamos sobre la flecha del rótulo Rótulos de columna nos aparece una lista como vemos en la
imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para
indicar si los queremos ver o no, mas una opción para marcar todas las opciones, en este caso todos los meses.
Si dejamos marcados los meses de Enero y Febrero, los otros meses
desaparecerán de la tabla, pero no se pierden, en cualquier momento
podemos visualizarlos volviendo a desplegar la lista y marcando la casilla
(Seleccionar todo).
Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para
cerrar y dejarlo como estaba. Aplicando el filtro a varios campos podemos
formar condiciones de filtrado más complejas, por ejemplo podemos
seleccionar ver los artículos con referencia 1236 de Abril.
Obtener promedios en una Tabla Dinámica
Por defecto, al crear una tabla dinámica, Excel 2007 nos genera totales con sumatoria, puede interesarnos modificar estas fórmulas con otras operaciones como pueden ser el obtener máximos, mínimos, promedios, entre otros.
Para lograr esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú emergente con diferentes opciones, debemos elegir la opción
Configuración de campo de valor,
y nos aparece un cuadro de diálogo como el que vemos en la imagen.
En este cuadro de diálogo podemos elegir cómo queremos resumir el campo, ya sea con Suma, Cuenta, Promedio, Máximo, Mínimo, Producto, entre otros.
También podemos abrir el cuadro de diálogo con el botón de la pestaña Opciones.
Gráficos con Tablas Dinámicas
Para crear una gráfica de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico dinámico
de la pestaña Opciones.
Para cambiar el formato del gráfico a otro tipo de gráfico que sea mas de nuestro agrado o necesidad de acuerdo con la información que requerimos. Al presionar este botón se muestra el cuadro de diálogo de Insertar Gráfico, ahí deberemos elegir el gráfico que más nos convenga. Luego se procede a seguir los pasos para configurar el
gráfico justo igual que se ha realizado con anterioridad.
CARACTERÍSTICAS AVANZADAS DE EXCEL 2007
Para aplicar formato a las celdas en Excel 2007 existen un mayor número de herramientas que nos permiten personalizar el contenido de nuestras Hojas de Trabajo. A continuación revisaremos su funcionamiento.
Maneras de aplicar un formato
Estudiaremos algunas características avanzadas de Excel 2007 que pueden ser de utilidad cuando deseamos mejorar o cambiar la presentación de nuestro trabajo.
Diferentes formas de cambiar un formato
Para modificar el formato de una o varias celdas, podemos elegir entre las distintas opciones que Excel 2007 ofrece, es cuestión de luego elegir la que más nos agrade.
Utilizando la pestaña Inicio
Ya hemos visto, en temas anteriores que en la pestaña Inicio contamos con varios íconos para asignar de forma rápida un determinado formato a las celdas seleccionadas.
Los formatos incluidos en este grupo son los más comunes.
Utilizando las teclas de acceso rápido
También podemos escoger un formato utilizando las teclas de acceso rápido que exponemos en la siguiente tabla:
CTRL + MAYUS + $ Aplica el formato Moneda con dos decimales. (los números negativos aparecen en rojo).
CTRL + MAYUS + % Aplica el formato Porcentaje sin decimales.
CTRL + MAYUS + ˆ Aplica el formato numérico Exponencial con dos decimales.
CTRL + MAYUS + @ Aplica el formato de Hora con hora y minutos e indica si es a.m. o p.m.
CTRL + MAYUS + ! Aplica el formato Número con dos decimales, separador de miles y signo menos (-) para los valores negativos.
Este método nos permite aplicar de una sola vez un formato, e incluye formatos que no se encuentran en la barra Formato, pero es tal vez más complicado de utilizar ya que requiere que aprendamos estas combinaciones de teclas.
Utilizando el cuadro de diálogo Formato de Celdas
Esta forma es menos rápida ya que requiere realizar varias acciones como son, abrir el cuadro de diálogo, elegir la pestaña adecuada y finalmente el formato a aplicar, pero permite ver toda la gama completa de formatos que están disponibles, además de facilitarnos el crear nuestro propio formato personalizado.
Para abrir el cuadro de diálogo Formato de Celda:
Hacer clic en la flecha que aparece al pie de la sección Número. Hacer clic con el botón derecho sobre la celda y elegir del menú contextual la
opción Formato de Celdas. Presionar la combinación de teclas CTRL + 1
Al realizar cualquiera de estas operaciones nos aparece el cuadro de diálogo como el que vemos en la imagen donde podemos escoger entre los diversos formatos numéricos.
Definir Formatos Personalizados
Excel 2007 provee una gran cantidad de formatos numéricos, pero es posible que en algún momento necesitemos algún formato que no esté disponible en los estándares de Excel.
Para crear formatos personalizados Excel 2007 cuenta con una herramienta.
Por defecto todas las celdas tienen el formato general. Su funcionamiento se basa en que lo que escribimos es lo que tenemos, es decir se adapta a lo que hayamos escrito, ya sea texto, números con decimales, porcentajes, entre otros.
Para crear un nuevo formato debemos acceder al cuadro de diálogo Formato de Celdas, seleccionar la Categoría Personalizada y seguidamente en el cuadro de texto Tipo: debemos escribir el código del formato que deseamos crear.
Para crear un formato de número personalizado, primero debemos conocer las reglas de los códigos para crear un formato.
El formato se compone de cuatro secciones separadas por ; (punto y coma)
_____;_____;_____;_____
La primera sección define el formato que tendrá el número en la celda si es positivo; la segunda, si el número es negativo, la tercera, si el número vale cero; la cuarta si la celda contiene texto.
0: Reserva un dígito para un número, si no se completa el tamaño definido por el formato se completa con ceros.
#: Reserva un dígito para un número, pero si no se completa el tamaño definido por el formato se deja en blanco.
?: Reserva un dígito. Añade espacios en ceros insignificantes.
.: Punto decimal.
%: Símbolo Porcentual.
,: Separador de millares.
E- E+ e- e+: Notación científica.
$ - + / () : Muestra estos caracteres, no necesita comillas.\: Muestra el siguiente carácter hasta completar el ancho de la celda.
_: Deja un espacio.
“texto”: Muestra el texto escrito entre las comillas dobles.
@: Espacio reservado para texto.
[color]: Muestra el carácter en el color especificado. Puede ser Negro, Azul, Cian, Verde, Magenta, Rojo y Amarillo.
[color n]: Muestra el correspondiente color de la paleta de colores, n puede ser un número entre 0 y 56.
[valor condición]: Permite escoger tu propio criterio para cada sección de un formato numérico.
m: Muestra el mes como número sin ceros (1-12).
mm: Muestra el mes como número con ceros (01-12).
mmm: Muestra el nombre del mes como abreviación (Ene, Feb).
mmmm: Muestra el nombre del mes por completo (Enero, Febrero).
mmmmm: Muestra la inicial del mes (E,F).
d: Muestra el día como número sin ceros (1-31).
dd: Muestra el día como número con ceros (01-31).
ddd: Muestra el nombre del día como abreviación (Lun, Mar).
dddd: Muestra el nombre del día por completo (Lunes, Martes).
yy o yyy: Muestra el año con dos dígitos (00-99) o cuatro (1900-999).
h o hh: Muestra la hora como números sin ceros (0-23) o como números con ceros (00-23).
m o mm: Muestra los minutos como números sin ceros (0-59) o como números con ceros (00-59).
AM/PM: Muestra la hora en formato de 12 horas, si no se indica esta opción se muestra la hora en formato de 24 horas.
Por ejemplo, se puede utilizar el siguiente formato personalizado:
€#,##[Verde]; (0,00€)[Rojo];”Cero”;””
Este formato contiene cuatro secciones separadas por el signo de punto y coma y utiliza un formato diferente para cada sección.
Con este formato estamos indicando que los números positivos (1ª sección) se escriben en verde, llevan el signo de euro delante, no se muestran los ceros no significativos y solo se muestran 2 decimales (#,##); los números negativos se escriben en rojo, ente paréntesis con el € detrás y con dos decimales siempre; los valores cero se sustituyen por la palabra cero y los textos por la cadena nula, es decir que si en la celda hay texto, ese texto desaparecerá.
Agrupar hojas de cálculo
Habrá ocasiones en que solo desees mostrar cierta información de la hoja de cálculo, puede ser por cuestiones de visualización o impresión; y esto regularmente lo logras ocultando filas o columnas, pero Excel te proporciona otra manera de hacerlo y esto es esquematizando la hoja de cálculo.
Colocar Niveles a una Hoja de Trabajo
Deberás establecer cuáles columnas o filas deseas que estén visibles y cuáles no e irás creando niveles para ocultarlas temporalmente.
Para colocar niveles en la hoja realiza lo siguiente:
1.- Selecciona toda la columna o toda la fila que desees agrupar (por ejemplo: Columna o renglón
). Puedes seleccionar varias columnas o filas adyacentes.
2.- De la ficha se selecciona la opción: 3.- Repite todos los pasos anteriores hasta que esquematices las filas y columnas que desees. Luego de realizar estos pasos aparecerá la hoja como lo muestra la siguiente gráfica.
4.- Da clic en el signo para ir ocultando niveles tanto de filas como de columnas, o bien puedes dar clic en los
números para ocultar niveles en columna o clic en para ocultar niveles en fila, en ambos casos
aparecerá un signo de indicando que algún nivel está oculto.
Niveles en Columna
Niveles
En Fila
NOTA: Si activas alguna celda de la columna o fila que vas a agrupar, y no seleccionas toda la fila o columna,
al mandar llamar la función Agrupar te aparecerá un recuadro del cual bajo la opción elige o
como lo muestra la figura.
Quitar Niveles de columnas y filas
Para quitar los niveles colocados en una hoja de cálculo debes seleccionar las columnas que quieras desagrupar y de la ficha Datos eliges la opción Desagrupar.
Borrar esquema
Para borrar todos los niveles tanto de fila como de columna es cuestión de que elijas Desagrupar y luego Borrar esquema.
FUNCIONES AVANZADAS
Una de las finalidades principales de Excel 2007 es la realización de cálculos utilizando fórmulas y funciones para lograrlos.
Existen algunas maneras de introducir una función en una celda, una es escribirla directamente, solo que para ello se requiere estar familiarizado con el nombre de los parámetros, aunque esto a veces resulta un tanto complicado porque existen funciones demasiado estructuradas y complejas.
Otra manera de utilizar las funciones es por medio del asistente para funciones, el cual se activa si das clic en el botón
de donde eliges la categoría de la función que utilizarás y dentro de este asistente se te indicará la manera de establecer los parámetros para la función seleccionada.
A continuación se analizan algunos ejemplos de funciones de varias categorías y la manera de establecerlas.
Funciones Lógicas
Las funciones lógicas arrojan resultados en base a una condición dada, es decir si la condición se cumple, se dice que la condición es verdadera, pero si no se cumple entonces se dice que la condición es falsa.
Función Lógica SI
Los parámetros que se utilizan en las funciones lógicas son:
Si, entonces, si – no. Es decir:
SI(Dato a evaluar la condición, “Se cumple”,”No se cumple”).
Por ejemplo:
En la siguiente lista deseas evaluar por medio de la función lógica SI, si el empleado “Tiene Bono” o “No tiene Bono”, de acuerdo al total de sus ventas, tomando en cuenta que la venta debe ser mayor o igual a $10,000 para que pueda cumplirse la función como verdadera.
La función se realizaría manualmente y quedaría de la siguiente manera:
La condición sería: Si la venta es mayor o igual que $10,000 entonces “Tiene Bono”, de lo contrario “No tiene bono”.
Si los datos se introducen en las mismas celdas que muestra el ejemplo, Armando tiene bono porque cumple con la condición, mientras que Fernando no tiene bono por no cumplir con la condición dada y la fórmula quedaría así;
=SI(B2>=10000,"Tiene Bono","No Tiene Bono")
La explicación textual sería de la siguiente manera:
B2 Es la celda donde se encuentra el dato a evaluar (El total de venta}
Condición Se cumple No se cumple
>=10000 Es la condición que quieres evaluar (Prueba lógica)
, La coma significa “entonces”
“Tiene bono” Es el texto que aparecerá si la condición es verdadera.
, Otra coma significa “si no o de lo contrario”
“No tiene Bono”Es el texto que aparecerá si la condición es falsa.
Si la fórmula la haces por medio del asistente, este aparecerá de la siguiente manera:
Función Lógica SI Y
La función lógica SI(Y) se utiliza cuando se desea cumplir con dos o más condiciones, pero para que se logre que sea verdadera, el resultado de todas las condiciones deben cumplir con la prueba lógica, con una sola condición que no se cumpla, automáticamente se evalúa como falsa.
Por ejemplo:
Tomaremos el mismo ejemplo de la lista anterior de empleados, donde deseas evaluar por medio de la función lógica SI(Y), si el empleado “Tiene Bono” o “No tiene Bono” de acuerdo a su venta Y a la antigüedad que tenga laborando en la empresa, tomando en cuenta que la venta debe ser mayor o igual a $10,000 Y la antigüedad mayor a 4 años y para que pueda cumplirse la función como verdadera ambas deben ser verdaderas.
La condición sería: Si el total de la venta es mayor o igual que $10,000 Y la antigüedad es mayor a 4 años entonces “Tiene Bono”, de lo contrario “No tiene bono”.
Si los datos se introducen en las mismas celdas que muestra el ejemplo, Armando tiene bono porque cumple con ambas condiciones, mientras que Fernando y Manuel no tienen bono por no cumplir con una de las dos condiciones dadas y la fórmula quedaría así;
=SI(Y(B2>=10000,C2>4),"Tiene Bono","No Tiene Bono")
Condiciones Se cumplen No se
cumplen
Si la fórmula la haces por medio del asistente, este aparecerá de la siguiente manera:
Función Lógica SI O
La función lógica SI(O) se utiliza cuando se desea cumplir con dos o más condiciones, pero para que se logre que sea verdadera, al menos una condición debe cumplirse de la prueba lógica, de lo contrario si no se cumple ninguna, se evalúa como falsa.
Por ejemplo:
Tomaremos el mismo ejemplo de la lista anterior de empleados, donde deseas evaluar por medio de la función lógica SI(O), si el empleado “Tiene Bono” o “No tiene Bono” de acuerdo a su venta Y a la antigüedad que tenga laborando en la empresa, tomando en cuenta que la venta debe ser mayor o igual a $10,000 O la antigüedad mayor a 4 años y para que pueda cumplirse la función como verdadera cualquiera debe ser verdadera.
La condición sería: Si el total de la venta es mayor o igual que $10,000 O la antigüedad es mayor a 4 años entonces “Tiene Bono”, de lo contrario “No tiene bono”.
Si los datos se introducen en las mismas celdas que muestra el ejemplo, Armando tiene bono porque cumple con ambas condiciones, Fernando y Manuel Tienen bono porque ambos cumplen al menos con una condición, mientras que Víctor No tiene bono porque no cumple con ninguna de las dos condiciones y la fórmula quedaría así;
=SI(O(B2>=10000,C2>4),"Tiene Bono","No Tiene Bono")
Si usas el asistente, este aparecerá de la siguiente manera:
Condiciones Se cumplen No se
cumplen
El formato condicional
Los formatos personalizados son de demasiada utilidad cuando manejamos alguna condición y arroje el resultado de forma automática sin rodeos.
El formato condicional sirve para que dependiendo del valor de la celda. Excel 2007 aplique un formato especial o no sobre esa celda.
El formato condicional suele utilizarse para resaltar errores, para valores que cumplan una determinada condición, para resaltar las celdas según el valor contenido en ellas, etc.
Para aplicar el formato condicional a una celda:
Seleccionamos la celda a la que vamos a aplicar un formato condicional.
Accedemos al menú Formato Condicional de la pestaña Inicio.
Aquí tenemos varias opciones, como resaltar algunas celdas dependiendo de su relación con otras, o resaltar aquellas celdas que tengan un valor mayor o menor que otro.
Utiliza las opciones: Barras de Datos, Escalas de Color y Conjunto de Iconos para aplicar diversos efectos a determinadas celdas.
Nosotros nos fijaremos en la opción Nueva regla que permite crear una regla personalizada para aplicar un formato concreto a aquellas celdas que cumplan determinadas condiciones.
Nos aparece un cuadro de diálogo Nueva regla de formato como el que vemos en la imagen.
En este cuadro seleccionaremos un tipo de regla. Normalmente querremos que se aplique el formato únicamente a las celdas que contengan un valor, aunque puedes elegir uno diferente.
En el marco Editar una descripción de regla deberemos indicar las condiciones que debe de cumplir la celda y de qué forma se marcará.
De esta forma si nos basamos en el Valor de la Celda podemos escoger entre varias opciones como pueden ser un valor entre un rango mínimo y máximo, un valor mayor que, un valor menor que y condiciones de ese estilo.
Los valores de las condiciones pueden ser valores fijos o celdas que contengan el valor a comparar.
Si pulsamos sobre el botón Formato entramos en un cuadro de diálogo donde podemos escoger el formato con el que se mostrará la celda cuando la condición se cumpla. El formato puede modificar, el color de la fuente de la letra, el estilo, el borde de la celda, el color de fondo de la celda, etc.
Al pulsar sobre Aceptar se creará la regla y cada celda que cumpla las condiciones se marcará. Si el valor incluido en la celda no cumple ninguna de las condiciones, no se le aplicará ningún formato especial.
Aquí se muestra el formato
que tomará si se cumple la
regla
Si pulsamos sobre Cancelar, no se aplicarán los cambios efectuados en el formato condicional.
En el caso cuando deseamos crear más de una regla, es decir, cuando se necesita elegir distintos formatos de celda dependiendo del valor que ahí se presente entonces presionamos la opción
En donde se mostrará la siguiente ventana:
Permite crear una regla para el rango que se tiene seleccionado.
Permite modificar las condiciones de la regla existente.
Elimina la regla seleccionada.
Tabla con formato condicional
Borrar un formato condicional
Cuando ya no deseas tener condiciones dentro de un rango de celdas, selecciona Borrar reglas, luego puedes elegir: Borrar reglas de las celdas seleccionadas, o Borrar reglas de toda la hoja.
Funciones de texto
Estas funciones permiten la manipulación de texto para crear cadenas, repeticiones, cambiar formatos, etc.
Función Concatenar
Esta función une una cadena de texto de varias celdas en una sola. Por ejemplo si tienes nombre, apellido materno y apellido paterno, puedes juntar en una sola celda los tres datos como lo muestra el ejemplo a continuación la figura, el cual fue el resultado de introducir la fórmula de la siguiente manera.
1.- Activa la celda donde vas a introducir la función Concatenar.
2.- De la ficha Fórmulas elige la opción: o da clic en el
botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones como lo muestra la figura.
3.- En la opción da clic en la
flecha y elige Texto.
4.- Bajo elige
CONCATENAR donde se activará otro cuadro para que indique el argumento de la función.
5.- En Texto 1 da clic en la primera celda que desees concatenar, luego en Texto2 da clic en la segunda celda o bien pulsa la barra espaciadora para que se coloque un espacio entre cada celda que vayas a concatenar.
6.- Ve dando clic en Texto 2,3,4,5 etc., según el número de celdas que deseas concatenar.
7.- Una vez indicadas las celdas a concatenar da clic en para que la celda te muestre el resultado.
Función Mayúscula
Esta función permite cambiar el texto de una celda de minúsculas a solo mayúsculas y lo puedes realizar de la siguiente manera:
1.- Activa la celda donde vas a introducir la función Mayúscula.
2.- De la ficha Fórmulas elige la opción: o da clic en el
botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones como lo muestra la figura.
3.- En la opción da clic en la
flecha y elige Texto.
4.- Bajo elige MAYUSC donde se activará otro cuadro para que indique el argumento de la función.
5.- En Texto da clic en la celda que desees convertir a mayúsculas.
6.- Da clic en para que la celda
te muestre el resultado.
Función Minúscula
Esta función permite cambiar el texto de una celda de mayúsculas a solo minúsculas y lo puedes realizar de la siguiente manera:
1.- Activa la celda donde vas a introducir la función Minúscula.
2.- De la ficha Fórmulas elige la opción: o da clic en
el botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones como lo muestra la figura.
3.- En la opción
da clic en la flecha y elige Texto.
4.- Bajo elige
MINUSC donde se activará otro cuadro para que indique el argumento de la función.
5.- En Texto da clic en la celda que desees convertir a minúsculas.
6.- Da clic en para que la celda te muestre el resultado.
Función Nompropio
Con esta función puedes hacer que solo la primera letra de cada palabra sea cambiada a mayúsculas, y lo puedes realizar de la siguiente manera:
1.- Activa la celda donde vas a introducir la función Nompropio.
2.- De la ficha Fórmulas elige la opción: o da clic en el
botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones como lo muestra la figura.
3.- En la opción da clic en la flecha y elige Texto.
4.- Bajo
elige NOMPROPIO donde se activará otro cuadro para que indique el argumento de la función.
5.- En Texto da clic en la celda que desees convertir a Nombre Propio.
6.- Da clic en para que la
celda te muestre el resultado.
Función espacios
Esta función quita todos los espacios individuales del texto excepto los espacios individuales entre palabras. Lo cual realizas como sigue:
1.- Activa la celda donde vas a introducir la función Espacios.
2.- De la ficha Fórmulas elige la opción: o da clic en
el botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones como lo muestra la figura.
3.- En la opción da clic en la flecha y elige Texto.
4.- Bajo elige
ESPACIOS donde se activará otro cuadro para que indique el argumento de la función.
5.- En Texto da clic en la celda que desees convertir a espacios sencillos.
6.- Da clic en para que la celda
te muestre el resultado.
Función extraer
Esta función devuelve los caracteres de una cadena de texto, dada la longitud y posición iniciales.
Por ejemplo, en la figura se muestra una celda que contiene la clave del empleado, de la cual se van a extraer sólo los primeros 8 dígitos en una celda a partir de la posición del primer dígito de la clave del empleado, luego con los datos
de la misma celda se va a extraer el RFC, y que el resultado sean los últimos 13 dígitos a partir de la posición 9.
La función se utiliza de la siguiente manera:
1.- Activa la celda donde vas a introducir la función Extrae.
2.- De la ficha Fórmulas elige la opción: o da clic
en el botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones como lo muestra la figura.
3.- En la opción da
clic en la flecha y elige Texto.
4.- Bajo elige EXTRAE
donde se activará otro cuadro para que indique el argumento de la función.
5.- En Texto da clic en la celda que donde se encuentran los datos que vas a extraer.
6.- En Posición Inicial indica a partir que carácter vas a extraer el texto.
7.- En Núm. de Caracteres especifica el número de caracteres que deseas que regrese la función.
8.- Da clic en para que la celda te muestre el resultado.
Función encontrar
La función encontrar localiza en una cadena de texto, el texto que se encuentra en otra cadena y regresa el número del carácter en el que aparece por primera vez el texto buscado partiendo de la primera posición.
Por ejemplo, en la figura se muestra una celda que contiene el Nombre, otra el Departamento y el número 9 en otra celda que indica la posición donde se encontró el registro buscado, lo cual quiere decir que se desea encontrar en qué posición del departamento se encuentra la palabra sistemas y que al buscarlo empiece a partir de la primera del inicio del texto en la celda, es decir, a partir de la posición 1.
Para realizar este mismo ejemplo, haz lo siguiente:
1.- Activa la celda donde vas a introducir la función Encontrar.
2.- De la ficha Fórmulas elige la opción: o da clic en el botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones como lo muestra la figura.
3.- En la opción da clic en la flecha y elige Texto.
4.- Bajo elige
ENCONTRAR donde se activará otro cuadro para que indique el argumento de la función.
5.- En Texto buscado introduce entre comillas el texto que deseas encontrar, en este caso “Sistemas”.
6.- En Dentro del texto da clic en la celda donde deseas buscar.
7.- En Núm. inicial indica a partir de qué posición del texto de la celda donde estas buscando, comience a buscar.
8.- Da clic en para que veas el resultado en la celda.
Funciones de búsqueda y referencia
Las funciones de búsqueda y referencia permiten buscar datos dentro de matrices horizontales o verticales haciendo referencia a filas o columnas y mostrando solo el valor deseado al introducir la referencia de la función.
Función BUSCARH
La función BUSCARH busca un valor en la primera fila superior de una tabla o matriz de valores y devuelve el valor en la misma columna de una fila especificada en la tabla o matriz y se le llama así porque H significa “Horizontal”.
La sintaxis que utiliza la función BUSCARH es la siguiente:
Donde:
Valor_buscado: Es el valor que se busca en la primera fila de la tabla. Y puede ser un valor, una referencia o una cadena de texto.
Matriz_buscar_en: Es una tabla de texto, números o valores lógicos en donde se buscan los datos. Puede ser una referencia a un rango o un nombre de un rango.
Indicador_filas: Es el número de filas de la tabla o matriz, desde la cual se devolverá el valor coincidente. La primera fila de valores en la tabla es la fila 1.
Ordenado: Es un valor lógico para encontrar la coincidencia más cercana a la fila superior, el cual se evalúa como VERDADERO, para encontrar la coincidencia exacta se evalúa como FALSO.
Para comprender el manejo de esta función, se trabajará con un ejemplo de un libro de trabajo que tiene una hoja llamada Inventario donde hay datos que se están representando en una matriz horizontal, Indicando un número de artículo, la descripción del artículo y su precio unitario, los cuales se desean Mostrar de manera vertical, en otra hoja
del mismo libro llamada Factura donde al mandar llamar el número de artículo, este despliegue la descripción y el precio unitario que vendrán de la matriz de la hoja Inventario.
Las siguientes imágenes representan tanto la hoja donde se encuentra la matriz, como la hoja hacia donde irán los datos de esta matriz, mandándolos llamar con la función BUSCARH.
Hoja de factura.
Para introducir la función BUSCARH haz lo siguiente:
1.- Introduce los números de artículo de la matriz de la hoja Inventarios, a la hoja factura, bajo la columna No, de artículo.
2.- Activa la celda donde vas a realizar la función BUSCARH, en este caso en la hoja factura en la primera celda vacía bajo Descripción (en el ejemplo de la tabla sería la celda B8).
3.- De la ficha Fórmulas elige la opción: o da clic en el
botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones.
4.- En da clic en la
flecha y elige Búsqueda y referencia.
5.- Bajo elige BUSCARH donde se activará otro cuadro para que indique el argumento de la función.
6.- En Valor buscado da clic en la celda que será referencia para encontrar la fila que indiques en la matriz, en este caso será la celda A8.
7.- En Matriz buscar en da clic en la hoja Inventario y selecciona toda la matriz (Rango A1:F3), luego presiona la tecla de funciones F4 para que conviertas la referencia del rango en absoluto, pues rellenarás en filas para obtener los demás valores.
8.- En Indicador filas teclea el número 2, pues estarás indicando que vas a mandar llamar los valores de la fila 2 de la matriz que seleccionaste.
9.- En Ordenado introduce un cero (0), para que al introducir un número de artículo que no exista dentro de la matriz, te indique que no se encuentra con un #N/A de otra manera, si introduces un valor diferente de cero, por ejemplo un 1, -1, etc., el valor que te arrojaría al introducir un valor que no existe, este te arroja el resultado más cercano a ese número.
10.- Da clic en para que veas el resultado de esta búsqueda en la celda.
NOTA: Para buscar los datos correspondientes al precio unitario, sigue los mismos pasos anteriores y solo
cambia el indicador de la fila a 3.
La siguiente imagen muestra el resultado de la función BUSCARH:
Función BUSCARV
La función BUSCARV busca un valor específico en la columna más a la izquierda de una matriz y devuelve el valor en la misma fila de una columna especificada en la tabla. Puedes utilizar BUSCARV en lugar de BUSCARH cuando los valores de comparación se encuentran en una columna situada a la izquierda y se le llama así porque V significa “Vertical”.
Datos encontrado
s en la matriz de la fila 2
Datos encontrado
s en la matriz de la fila 3
La sintaxis que utiliza la función BUSCARV es la siguiente:
Donde:
Valor_buscado: Es el valor que se busca en la primera columna de la tabla. Y puede ser un valor, una referencia o una cadena de texto.
Matriz_buscar_en: Es una tabla de texto, números o valores lógicos en donde se buscan los datos. Puede ser una referencia a un rango o un nombre de un rango.
Indicador_columnas: Es el número de columna de la tabla o matriz, desde la cual se devolverá el valor coincidente. La primera columna de valores en la tabla es la columna 1.
Ordenado: Es un valor lógico para encontrar la coincidencia más cercana a la primer columna, el cual se evalúa como VERDADERO, para encontrar la coincidencia exacta se evalúa como FALSO.
Para comprender el manejo de esta función, se trabajará con el mismo ejemplo del libro que se manejó en la función BUSCAR H, el cual tiene una hoja llamada estados, donde hay datos que se están representando en una matriz vertical, en dicha matriz hay un campo llamado código, otro llamado Estado y un último llamado Impuesto, los cuales se desean mostrar en la hoja Factura del mismo libro, donde al mandar llamar el código del estado, este despliegue el nombre del estado y el impuesto, que vendrán de la matriz de la hoja Estados.
Las siguientes imágenes representan tanto la hoja donde se encuentra la matriz, como la hoja hacia donde irán los datos de esta matriz, mandándolos llamar con la función BUSCARV.
Hoja de estado.
Para introducir la función BUSCARV haz lo siguiente:
1.- Introduce algún código de la matriz de la hoja Estados en la hoja Factura a la derecha de Código (En este caso, celda B3).
2.- Activa la celda donde vas a realizar la función BUSCARV, en este caso en la hoja factura debajo de Código (Celda B4).
Dato que se va a buscar
Debe mostrar el nombre del estado al introducir el
código.
Debe mostrar impuesto al de acuerdo con el código
del estado.
3.- De la ficha Fórmulas elige la opción: o da clic en el botón ubicado junto a la barra de fórmulas donde se activará el asistente para funciones.
4.- En da clic en la
flecha y elige Búsqueda y referencia.
5.- Bajo elige BUSCARV
donde se activará otro cuadro para que indique el argumento de la función.
6.- En Valor buscado da clic en la celda que será referencia para encontrar la fila que indiques en la matriz, en este caso será la celda B3.
7.- En Matriz buscar en da clic en la hoja estados y selecciona toda la matriz (Rango B2:D11).
8.- En Indicador columnas teclea el número 2, pues estarás indicando que vas a mandar llamar los valores de la columna 2 de la matriz que seleccionaste (Nombre del Estado).
9.- En Ordenado introduce un cero (0), para que al introducir un código de estado que no exista dentro de la matriz, te indique que no se encuentra con un #N/A de otra manera, si introduces un valor diferente de cero, por ejemplo un 1, -1, etc., el valor que te arrojaría al introducir un valor que no existe, este te arroja el resultado más cercano a ese número.
10.- Da clic en para que veas el resultado de esta búsqueda en la celda.
NOTA: Para buscar los datos correspondientes al impuesto, sigue los mismos pasos anteriores y solo cambia
el indicador de la columna a 3.
La siguiente imagen muestra el resultado de la función BUSCARV:
Funciones de Bases de Datos
Código que va cambiando
Dato encontrado en la matriz en la columna 2
Dato encontrado en la matriz en la columna 3
Se utilizan cuando deseamos realizar cálculos sobre alguna columna pero añadimos una condición de selección
de las filas que serán utilizadas para el cálculo, es decir, previamente se filtran y luego se utilizan.
Por ejemplo si tenemos una columna con el beneficio obtenido por nuestros automóviles (ver figura) y queremos
saber cuál ha sido el beneficio de los Ford, no podemos utilizar la función suma porque sumaría todos los
automóviles sin distingo. En este caso utilizamos una función de Base de Datos llamada BDSUMA, que incluye la
función de filtrado automóvil = “Ford”.
Para explicar las funciones de Base de Datos que nos ofrece Excel, utilizaremos la hoja:
En esta hoja tenemos una lista con los automóviles de la empresa, incluidos datos de plazas, años, rentabilidad y
beneficio obtenido.
NOTA: Los renglones 1 a 4 se utilizan para definir los filtros.
Esta como otras de las funciones de base de datos, requiere tres argumentos: el rango de datos que define la
tabla o base de datos, el nombre del campo que se utiliza en la función y un rango de criterios para indicar la
condición que deben cumplir los renglones que serán utilizados en el cálculo de la función (el filtro)
Función BDCONTAR(rango_datos; nombre_campo; rango_criterios)
Cuenta las celdas que contienen un número en una determinada columna (campo), considerando únicamente los
renglones que cumplan una determinada condición.
rango_datos: es el rango de celdas que componen la base de datos o lista donde se quiere contar.
nombre_campo: indica el nombre del campo que contiene los valores a contar
rango_criterio: es el rango de celdas que contienen la condición que deben cumplir los registros que entran en el
recuento.
Ejemplo: BDCONTAR(A5:F14;”Años”;A2:F2) Como resultado obtenemos -->2
Establecer las condiciones
Para establecer las condiciones que se utilizarán en el parámetro rango_criterios, debemos reservar una zona en
la hoja de cálculo para dichas condiciones (puede ser antes o después de la Tabla de Datos)
En esta área escribimos un renglón con los nombres de los campos necesarios para realizar la operación en toda
una columna de la base de datos, introduzca una línea en blanco debajo de los nombres de campo en el rango de
criterios.
Función BDMAX(rango_datos; nombre_campo; rango_criterios)
Devuelve el valor máximo encontrado en una determinada columna (campo), considerando únicamente los
renglones que cumplan una determinada condición.
Función BDMIN(rango_datos; nombre_campo; rango_criterios)
Devuelve el valor mínimo encontrado en una determinada columna (campo), considerando únicamente los
renglones que cumplan una determinada condición.
Función BDPRODUCTO(rango_datos; nombre_campo; rango_criterios)
Devuelve el producto de los valores encontrados en una determinada columna (campo), considerando
únicamente los renglones que cumplan una determinada condición.
Función BDPROMEDIO(rango_datos; nombre_campo; rango_criterios)
Obtiene el promedio de los valores encontrados en una determinada columna (campo), considerando
únicamente los renglones que cumplan una determinada condición.
Función BDSUMA(rango_datos; nombre_campo; rango_criterios)
Obtiene la suma de los valores encontrados en una determinada columna (campo), considerando únicamente los
renglones que cumplan una determinada condición.
Función BDEXTRAER(rango_datos; nombre_campo; rango_criterios)
Extrae de la base de datos un único valor contenido en el campo indicado y en la fila que cumpla la condición
establecida mediante el rango de criterios.
Si ningún registro coincide con los criterios, BDEXTRAER devuelve el valor de error #¡VALOR!
Si más de un registro coincide con los criterios, BDEXTRAER devuelve el valor de error #¡NUM!
Función BDVAR(rango_datos; nombre_campo; rango_criterios)
Calcula la varianza basándose en una muestra de los valores contenidos en una determinada columna (campo),
considerando únicamente las filas que cumplan una determinada condición.
Función BDVARP(rango_datos; nombre_campo; rango_criterios)
Calcula la varianza basándose en todos los valores contenidos en una determinada columna (campo),
considerando únicamente las filas que cumplan una determinada condición.
Función BDDESVEST(rango_datos; nombre_campo; rango_criterios)
Calcula la desviación estándar basándose en todos los valores contenidos en una determinada columna (campo),
considerando únicamente las filas que cumplan una determinada condición.
AUDITORÍA
Son herramientas las cuales ayudan a la revisión y análisis de datos para poder tomar decisiones en base a ellas, además de ofrecer protección y seguridad estableciendo restricciones.
Buscar Objetivo
Buscar objetivo es parte de una serie de comandos a veces denominados herramientas de análisis. Es un método que permite buscar un valor específico para una celda ajustando el valor de otra celda. Al buscar un objetivo, Excel 2007 varía el valor de una celda hasta que una fórmula dependiente de dicha celda devuelve el resultado deseado.
Para buscar un objetivo haz lo siguiente:
1.- Activa la celda que vas a ajustar con un nuevo valor, esta debe ser una fórmula para poder realizar la función, como por ejemplo el total de ventas de Enero como lo muestra la siguiente figura:
2.- De la ficha Datos, elige Análisis Y si y posteriormente Buscar objetivo… como se muestra en la figura.
La barra de fórmulas muestra la fórmula
de la celda seleccionada
Celda Objetivo
3.- En Definir la celda: aparece la celda que será ajustada, en caso de no ser así solo actívala.
4.- En Con el valor: introduce el valor al que deseas se ajuste la celda activa.
5.- En Para cambiar la celda: da clic en la celda que va a cambiar el valor actual para ajustar la celda.
6.- Da clic en donde aparecerá un cuadro para que visualices el resultado como lo muestra la siguiente figura:
7.- Da clic en para que visualices el resultado, el cual quedaría así:
Celda en la que se ajustó el valor para llegar a la solución de la celda objetivo
Valor actual de la celda objetivo
Solver
La herramienta Solver es parte de una serie de comandos a veces denominados herramientas de análisis Y SI, y al igual que la herramienta Buscar Objetivo, Solver también permite buscar valores específicos en una fórmula ajustando los valores en las celdas cambiantes que se especifiquen, denominados celdas ajustables para generar el resultado en la fórmula de la celda objetivo.
La diferencia entre Buscar Objetivo y Solver, es que en la primera solamente se puede ajustar el valor a una celda, mientras que Solver permite hacer cambios en varias celdas para llegar a un resultado, así como también puede agregar restricciones a los valores que hacen referencia a las celdas que afectan la fórmula de la celda objetivo y finalizar mostrando un informe de respuestas.
Por ejemplo, la siguiente tabla muestra un reporte de ventas del primer semestre, donde en el último mes aún no se tienen datos, ya que estos se van a buscar por medio de la función Solver y llegar a un total Semestral:
Este total se ajustará a $1,100,000
Este total se ajustará a
$10,250,000
Se va a ajustar el total del Semestre para el departamento Carnicería, a un total de $1,100,000.00 (Celda I6), cambiando el valor del mes de junio, (celda H6), donde Solver encontrará el valor que falta y mostrará un informe de respuestas; así mismo se desea ajustar el total de todo el semestre a $10,250,000.00., cambiando las celdas H7:H11 y agregando dos restricciones, una donde se indique que el valor para el departamento de Abarrotes es igual a $192,500.00, (=192500) y el valor para el departamento de limpieza debe ser mayor o igual a $320,560.00 mostrando un informe de respuestas.
Para activar el solver:
En Excel 2007 el Solver es un complemento que requiere previa instalación, por lo que necesitaremos acudir a la mini barra de herramientas:
Y seleccionar el botón donde luego haremos clic en la opción: donde nos aparecerá la siguiente ventana:
Dentro de las opciones de este menú seleccionamos Complementos, luego en la parte derecha marcamos Solver y
finalmente hacemos clic en: , donde nos aparece la ventana que nos permite activar el complemento Solver.
Debe estar seleccionado y finalmente . Solver ha sido instalado.
Utilizando el Solver
1.- Activa una celda que contenga fórmula, pues de no ser así, Solver no encontrará resultados.
2.- De la ficha Datos elegimos el botón: donde se activará el cuadro de diálogo de Parámetros de Solver como lo muestra la figura.
3.- Activa el recuadro Celda Objetivo y da clic en la celda que se ajustará al valor que desees.
4.- En introduce el valor al que
se ajustará la celda objetivo.
5.- Debajo de Cambiando las celdas selecciona la celda o rango de celdas que ajustarán o crearán sus valores para llegar al de la celda objetivo.
6.- Debajo de Sujetas a las siguientes
restricciones: da clic en para que indiques la referencia de la celda a la que le vas a agregar una restricción, donde te aparecerá un cuadro de Agregar restricción.
7.- Activa el recuadro bajo Referencia de la celda: y da clic en la celda que vas a restringir.
8.- De la lista que está en medio da clic en la flecha donde se activará una lista para que indiques la condición de la restricción (<=,=,>=, etc.).
9.- Bajo Restricción introduce el valor al que estás restringiendo.
10.- Si vas a agregar más restricciones, da clic en el botón y sigues los mismos pasos.
11.- Al terminar de agregar restricciones da clic en para regresar al cuadro Parámetros de Solver.
12.- Del cuadro Parámetros de Solver da clic en el botón donde te va a aparecer un cuadro de Resultados de Solver.
13.- Activa para que
muestre los resultados o bien
si deseas que Solver te regrese los valores que tenías antes de empezar a crear la función.
14.- Del cuadro de Parámetros de Solver elige Respuestas y da clic en: para que visualices los resultados encontrados tanto de la celda objetivo como de las celdas cambiantes y la hoja que se inserta en el libro como informe de respuestas como lo muestran las siguientes imágenes:
Celdas Cambiantes
Resultado de la celda objetivo
Resultado de la celda objetivo
Administrador de Escenarios
Un escenario es parte de una serie de valores que representan un posible resultado, a veces denominados herramientas de análisis Y si, el cual es un proceso de cambio de los valores de celdas para ver cómo afectan el resultado de fórmulas de la hoja de cálculo. Por ejemplo, variar la tasa de interés que se utiliza en una tabla de amortización para determinar el importe de los pagos, a cada serie de variables que corren a través de este modelo se le conoce como Escenario.
Crear un Escenario
Para crear un escenario tomaremos de ejemplo una tabla que muestra un reporte de ventas del primer trimestre en varios departamentos, en la cual se van a afectar los valores originales de la tabla para crear un escenario como Pérdidas al introducir valores menores y otro escenario como Ganancias al introducir valores mayores, aunque antes de esto se creará un escenario con el nombre Original que muestre los valores antes de ser afectados.
Escenario Original
Para crear el escenario original haz lo siguiente:
1.- De la ficha Datos elige el botón Análisis Y si y posteriormente Administrador de escenarios
2.- Creamos nuestro escenario Original presionando el botón
.
3.- Bajo Nombre del escenario: Introduce el nombre con el que desees llamar el escenario, en este caso Original.
4.- Bajo Celdas cambiantes: selecciona el rango de celdas que vas a cambiar en el escenario, en este caso aun no harás cambios.
5.- Bajo Comentarios: Teclea una descripción relacionada con el contenido de tu Escenario.
6.- Da clic en donde aparecerá el siguiente cuadro:
7.- En Valores del escenario se encuentra la lista completa de los valores cambiantes dentro del rango seleccionado. En este caso dejaremos los valores originales.
8.- Damos clic en para terminar con las modificaciones a este escenario.
Escenario de ganancias o pérdidas
Para crear un escenario de ganancias o pérdidas:
1.- De la ficha Datos elige el botón Análisis Y si y posteriormente Administrador de escenarios
2.- Creamos nuestro escenario Ganancias o Pérdidas
presionando el botón .
3.- Realiza los pasos previamente vistos en la elaboración del escenario Original, toma en cuenta que incrementarás y
disminuirás algunos valores para obtener pérdidas y en su caso ganancias, con respecto al escenario original. Presiona
.
4.- Introduce valores mayores (ganancias), menores (pérdidas) de acuerdo al escenario que te encuentres modificando
o creando.
5.- Una vez realizados los cambios haz clic en: .
Visualizar los escenarios
En la ventana Administrador de escenarios selecciona previamente el
escenario que deseas visualizar: (Original, Ganancias, Pérdidas),
luego presionamos el botón y cada escenario será
mostrado en su mismo lugar.
Original
Ganancias
Pérdidas
Crear un informe de resumen de datos
Un informe de resumen de escenarios te permite comparar varios escenarios en la misma hoja.
Para crear un informe de resumen de escenarios haz lo siguiente:
1.- De la ficha Datos elige el botón Análisis Y si y posteriormente Administrador de escenarios.
2.- Haz clic en el botón
3.- En el cuadro de diálogo seleccionamos
4.- Bajo Celdas de resultado, indicamos donde comenzará a generarse el resumen, ya sea en la misma hoja o en una diferente.
5.- Da clic en
La Validación de Datos
La validación de datos es muy similar al formato condicional, salvo que esta característica tiene una función muy concreta y es validar el contenido de una celda; pudiendo incluso mostrar un mensaje de error o aviso si llegara el caso.
Para aplicar una validación a una celda:
Seleccionamos la celda que queremos validar. Accedemos a la pestaña Datos y pulsamos Validación de Datos.
Nos aparece un cuadro de diálogo Validación de Datos como el que vemos en la imagen donde podemos elegir de entre varios tipos de validaciones.
En la sección Criterio de Validación indicamos la condición para que el dato sea correcto.
Dentro de Permitir podemos encontrar Cualquier Valor, Número entero, Decimal, Lista, Fecha, Hora, Longitud de Texto y Personalizada. Por ejemplo si elegimos Número Entero, Excel 2007 solo permitirá números enteros en esa celda, si el usuario intenta escribir un número decimal, se producirá un error.
Podemos restringir más los valores permitidos en la celda con la opción Datos; donde por ejemplo, podemos indicar que los valores estén entre 2 y 8.
Si en la opción Permitir elegimos Lista, podremos escribir una lista de valores para que el usuario pueda escoger un valor de los disponibles en la lista. En el recuadro que aparecerá, Origen, podremos escribir los distintos valores separados por; (punto y coma) para que aparezcan en forma de lista.
En la pestaña Mensaje de Entrada podemos introducir un mensaje que se muestre al acceder a la celda. Este mensaje sirve para informar de qué tipos de datos son considerados válidos para esa celda.
En la pestaña Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al usuario cuando introduzca en la celda un valor incorrecto.
En la pestaña Mensaje de Error puedes establecer tres estilos de mensajes, dependiendo la importancia que deseamos dar al dato validado.
De límite: no acepta datos erróneos.
De advertencia: acepta datos erróneos.
De información: acepta datos erróneos.
Rastrear Datos Inválidos
Al establecer características de validación, se pueden rastrear los datos no válidos por medio de una barra de auditoría de fórmulas.
Para rastrear datos inválidos haz lo siguiente:
1.- De la ficha Datos Elegimos Validación de datos y luego Rodear con un círculo datos no válidos. Esto permite mostrar las celdas que contienen datos no válidos, tal y como se muestra a continuación:
Tabla con un rango validado
Tabla con datos inválidos
NOTA: Al establecer un dato válido en la celda donde existe uno erróneo, automáticamente desaparece el
círculo rojo y cada vez que guardas cambios desaparecen todos y para que aparezcan se tienen que rastrear de
nuevo.
Enlazar y Consolidar Hojas
Enlazar Hojas de Trabajo
El concepto de enlazar en Excel es el hecho de utilizar fórmulas de varias hojas para combinar datos, Al enlazar hojas de trabajo estamos creando una dependencia de una con respecto a otra, apareciendo así dos conceptos:
El libro de trabajo dependiente: es el que contiene las fórmulas. El libro de trabajo fuente: es el que contiene los datos.
La cuestión que debemos plantearnos antes de enlazar hojas de trabajo, es si realmente nos hace falta complicarnos tanto o sencillamente podemos hacerlo todo en una misma hoja.
No existe una respuesta genérica, dependerá de la envergadura de los datos y de las fórmulas; si las hojas las van a utilizar varias personas, etc.
Para crear un libro de trabajo dependiente debemos crear fórmulas de referencias externas, es decir fórmulas que hacen referencia a datos que se encuentran en una hoja externa a la que está la fórmula.Para una mejor comprensión del tema se manejará un libro que contiene una hoja que refleja un Resumen de Ventas donde se reflejarán los datos de destino y tres hojas mas etiquetadas como Enero, Febrero y Marzo, las cuales contienen los datos de Origen:
Aquí se reflejan los
Datos de destino
Estas hojas contienen los
datos de origen
Vincular manualmente
Para realizar el vínculo de una manera manual haz o siguiente:
1.- Activa la celda donde se va a reflejar el dato de destino, en el caso del ejemplo anterior en la hoja Resumen sería la celda inmediata bajo el mes de enero.
2.- Teclea el signo de =
3.- Da clic en la hoja a la que deseas vincularte (sería la hoja enero)4.- Da clic en la primera celda que vas a vincular5.- Utilizando el método de relleno arrastra para que te muestre los datos restantes
Las siguientes figuras muestran como se reflejaría el resultado de los pasos anteriores:
Vincular con Pegado Especial
Para realizar un vínculo por medio de un pegado especial haz lo siguiente:
1.- Selecciona el rango que contiene los datos de origen (todos).2.- De la ficha Inicio elige Copiar.3.- Colócate en la celda donde comenzarás a pegar los datos vinculados.
4.- Elige Pegado Especial
5.- Da clic en el botón 6.- Selecciona alguna celda de los datos de destino y observa como en la barra de fórmulas aparece el nombre de la hoja y la celda a la que te estás vinculando.7.- Realiza cambios a los datos de origen y verifica que se actualicen los datos en el destino.
Como crear formulas de referencias externas
Para crear fórmulas de referencias externas debemos seguir los siguientes pasos:
Abrir el libro de trabajo fuente (el libro que contiene los datos) Abrir el libro de trabajo dependiente y seleccionar la celda donde queremos incluir la fórmula Introducir la fórmula de la forma que hemos hecho siempre, cuando se llegue el punto de escoger las celdas
de datos, activar el libro de trabajo fuente y seleccionar las celdas necesarias. Terminar la fórmula y pulsar Intro
Las referencias al libro externo las gestiona automáticamente Excel 2007, incluso si cambiamos el nombre del archivo donde están los datos desde Archivo --> Guardar Como. Las referencias también se cambiarían.
Si en el libro de trabajo dependiente queremos ver todos los vínculos a las páginas fuente podemos hacerlo accediendo a la pestaña Datos y haciendo clic en el botón Editar Vínculos. Nos aparece el cuadro de diálogo Modificar Vínculos donde podemos hacer modificaciones sobre los vínculos.
Actualizar Valores: Actualiza la lista con los vínculos.Cambiar Origen: Podemos modificar el origen del vínculo seleccionado.Abrir Origen: Abre el libro del vínculo seleccionado.Romper vínculo: Quita los vínculos de las fórmulas externas.Comprobar estado: Comprueba el estado del origen.Pregunta inicial: Podemos incluir una advertencia al abrir el libro dependiente, indicando que tenemos enlaces externos.
Auditar fórmulas
Otra de las herramientas de auditoría es el poder rastrear si existen errores en las fórmulas, visualizando la relación que existe entre la fórmula y las celdas que están referenciadas por ella, esto se puede hacer desde la ficha Fórmulas, en donde encontramos lo siguiente:
Los botones que ahí se encuentran tienen las siguientes funciones:
BOTÓN FUNCIÓN
Comprobación de errores Activa el cuadro de diálogo Comprobación de errores que te guiará para
poderlos corregir.
Esta opción despliega por medio de flechas azules y rojas de dónde viene el
resultado de la fórmula que se está auditando.
Esta opción quita un nivel de flechas azules de los valores precedentes. (Se
encuentra dentro del menú )
Define a que resultado afecta la celda, es decir indica que de la celda
seleccionada depende que se dé un valor en una celda que contiene
fórmula.
Esta opción quita las marcas azules y flecha de dependientes .
Borra todas las flechas de dependientes y precedentes.
Se utiliza cuando una celda marca un error en la fórmula, mostrándote con
flechas las celdas involucradas.
Muestra u oculta una ventana de inspección para llevar un seguimiento de los resultados
Evaluar Fórmula Evalúa la fórmula paso a paso.
Mostrar Fórmulas Muestra la fórmula en lugar del resultado en las celdas, lo que permite una
más fácil revisión.
En el siguiente ejemplo vamos a auditar una hoja de cálculo mostrando una tabla que contiene datos erróneos en
algunas fórmulas y otras no erróneas pero que se pueden rastrear como se describió previamente.
Las celdas realzadas son las que se van a rastrear.
Para rastrear fórmulas y valores de celdas haz lo siguiente:
1.- Activa la celda que vas a auditar, es fácil localizar una celda con error, ya que muestra en la parte superior derecha
un pequeño triángulo verde. Ejemplo:
2.- Da clic en la ficha Fórmulas
3.- Activa una celda que contenga una fórmula y da clic en el botón para que visualices que
valores son los que se están evaluando en la celda.
4.- Da clic en una celda que no contenga fórmula y da clic en el botón para que te indique a
que fórmula corresponde ese valor.
5.- Activa una celda que contenga error y da clic en el botón para que te indique de que
celdas proviene el error.
6.- Al terminar de auditar la hoja, esta se muestra de la siguiente manera:
Ahora vamos a analizar por qué hay errores en las fórmulas:
En esta fórmula se rastrearon dependientes y se detectó que la fórmula de la celda C11
debería sumar el rango C5:C10, por lo que muestra un error de adyacencia al detectar
que falta un valor dentro de la celda para que la suma sea correcta.
En esta fórmula también se rastrearon dependientes y se detectó que la fórmula de la celda I5 debería sumar el rango
C5:F5. Por lo que se muestra un error de adyacencia al faltar tres valores para que la suma sea correcta.
La celda que contiene el valor 205,120.00 indica que de ella depende que se dé el resultado de las sumas de las celdas
enmarcadas con un borde.
Este es un error de división entre cero, pues la celda con el error #¡DIV/0 indica que hay una celda donde no existe
ningún valor por el que se esté dividiendo.
Consolidar hojas de trabajo
El concepto de consolidar hojas de trabajo viene muy ligado al concepto de enlace que acabamos de ver. Cuando hablamos de consolidar hojas de trabajo estamos entablando una relación entre varias hojas de trabajo, por tanto es muy posible que existan enlaces entre esas hojas de trabajo.
Por ejemplo donde se puede utilizar la consolidación de hojas de trabajo puede ser una empresa que cuenta con varios departamentos, cada uno de ellos con un presupuesto y para conocer el presupuesto total de la empresa, incluiríamos una hoja dependiente y el origen de los datos serían las hojas o libros de los departamentos con sus respectivos presupuestos.
Si elegimos sobre la pestaña Datos y presionamos el botón Consolidar obtenemos el cuadro de diálogo Consolidar como se muestra a continuación:
En este cuadro podemos elegir;
La función a realizar En Referencia elegimos las celdas de cada libro/hojas fuente Elegimos Agregar para añadir las celdas seleccionadas a la lista de todas las referencias
Repetimos los dos últimos pasos para seleccionar las celdas de los diferentes libros/hojas fuente Al final presionamos Aceptar para terminar la consolidación.
Si marcamos la casilla Crear vínculos con los datos de origen. Los datos de los libros/hojas fuente se incluirán en el libro consolidado a modo de esquema, y si cambiamos un valor en el origen, el cambio se reflejará automáticamente en el libro consolidado.
Excel e Internet
Excel nos ofrece la posibilidad de compartir nuestras hojas de trabajo en la Web y también de obtener información de Internet.
Convertir una hoja de Excel 2007 a una página Web
Para poder “subir” a Internet un documento realizado en Excel 2007, debemos convertirlo a un formato reconocible por los navegadores (html o xml). Para ello debemos realizar lo siguiente:
Presionar el botón Office. Selecciona la opción Guardar como En la opción Guardar como tipo tendremos que seleccionar el formato Página Web.
En esta pantalla disponemos de dos posibles modos de formato de página Web, el primero es el que engloba todo el contenido en un solo archivo. Por lo que puede ser enviado y compartido fácilmente y tiene un propósito más bien de revisión que de publicación, el segundo formato genera un documento html, y demás documentos relacionados con este principal y tiene un propósito principal de ser publicado en un sitio web.
Una vez elegido la opción más conveniente, podemos presionar el botón Guardar y tendremos nuestra hoja de Excel 2007 guardada como una página web con la extensión .html en lugar del ya conocido .xlsx.
Ya que la hoja (o el libro) se encuentra convertido a formato html, podremos colocarla en un servidor de Internet para que pueda ser vista de forma pública.
Macros
Las Macros en Excel son una herramienta que permite automatizar ciertos procesos, lo que permite realizar tareas de forma repetitiva rápidamente.
Las macros pueden ser creadas de forma automática, sin necesidad de tener conocimientos de programación del lenguaje Visual Basic (VB), Lenguaje que permite realizar a detalle acciones para la mayoría de los usos y funciones con que Excel 2007 cuenta. Sin embargo no es materia de este curso involucrarnos en la programación con VB, y se recomienda estudiar por separado este lenguaje, ya que entre otras aplicaciones también es utilizado para programación de Sitios en la Web, manejo de bases de datos y por supuesto Funciones Avanzadas en Excel 2007.
Grabar una macro
Seleccionas la ficha Vista y desplegar el submenú Macros y dentro de esta opción seleccionar la opción Grabar Macro… También puedes encontrar las siguientes opciones:
Ver Macros.- Donde accedemos a un listado de las macros creadas en ese libro.
Usar referencias relativas.- Con esta opción utilizaremos referencias relativas para que las macros se graben con acciones relativas a la celda inicial seleccionada.
Al seleccionar la opción Grabar Macro lo primero que vemos es el cuadro de diálogo Grabar Macro donde podemos dar un nombre a la macro, (no se permiten espacios en blanco en el nombre de la macro).
Se puede asignar un método abreviado mediante la combinación de las teclas CTRL + “Alguna otra tecla” como pueden ser las de funciones o alguna otra. Buscaremos utilizar una que no esté ya designada por Excel 2007.
En Guardar macro en: podemos seleccionar guardar la macro en el libro activo, en el libro de macros personal o en otro libro. Lo cual determinará el acceso que se pueda tener a esta macro, ya sea exclusivo para un libro o compartido para su uso en varios libros.
En Descripción: podemos describir las funciones de la macro, aplicaciones y lo que creamos útil.
Para comenzar la grabación de la macro presionamos el botón Aceptar y a continuación veremos en la barra de estado
el botón donde tenemos la opción de detener la grabación.
A partir de entonces debemos realizar las acciones a grabar. Es conveniente no seleccionar ninguna celda a partir de la grabación ya que si seleccionamos alguna celda posteriormente cuando ejecutemos de nuevo la macro, esta selección puede ocasionar problemas de celdas fuera de rango.
Al finalizar las acciones que deseamos grabar presionamos el botón Detener que se encuentra en la barra de
estado, o en el menú de Macros hacemos clic en .
TIP: Otra manera de grabar macros es seleccionar de la ficha Programador la opción:
del grupo Código.
Guardar un archivo con macros
Cuando guardamos un archivo y queremos que las macros que hemos creado se almacenen con el resto de las hojas de cálculo deberemos utilizar un tipo de archivo diferente.
Par a ello debemos ir al botón de Office y seleccionar la opción Guardar como.
Se abrirá el cuadro de diálogo Guardar como. En la opción desplegable Guardar como tipo seleccionas Libro de Excel habilitado para macros (*.xlsm).
Damos un nombre al archivo y se guardará.
Cuando abrimos un archivo que tiene macros guardadas se nos muestra un anuncio bajo la banda de opciones:
Esto ocurre porque Office no conoce la procedencia de las Macros. Como están compuestas por código podrían realizar acciones perjudiciales para nuestra computadora.Si confías en la fuente de la que proceden las macros entonces selecciona el botón Opciones para activarlas.
Aparecerá este cuadro de diálogo:
Selecciona la opción Habilitar este contenido y presiona Aceptar.
Ejecutar una Macro
Una macro que ha sido grabada puede ser ejecutada el número de veces que se desee.
Antes de dar la orden de ejecutar la macro, dependiendo del tipo de macro que sea puede ser necesario seleccionar o no las celdas que queramos queden afectadas por las acciones de la macro.
Por ejemplo; si hemos creado una macro que automáticamente da formato a las celdas seleccionadas, tendremos previamente que seleccionar las celdas a formatear antes de ejecutar la macro.
Para ejecutar la macro debemos ir al menú Macros de la pestaña Vista (También se puede acceder a esta opción en la pestaña Programador en el caso que se tenga disponible)
Ahí seleccionamos la opción Ver macros y nos aparecerá el siguiente cuadro de diálogo:
Aquí aparece una lista de macros que hemos creado. Debemos seleccionar la macro deseada y presionar el botón Ejecutar. Se cerrara el cuadro y se ejecutará la macro.
Existen otros botones en este cuadro de diálogo como se describen a continuación:
Cancelar.- Cierra el cuadro de diálogo sin ejecutar ninguna macro.Paso a Paso.- Ejecuta la macro instrucción por instrucción, abriendo el editor de programación de Visual Basic.Modificar.- Abre el editor de programación de Visual Basic para modificar el código de la macro. Estos dos últimos botones son para quienes tienen conocimientos sobre programación.Eliminar.- Borra la macro seleccionada.Opciones.- Abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado (utilizada para ejecutar la macro directamente sin el uso del menú). Y podemos cambiar también la descripción de la macro.
Modificar una Macro
Una vez grabada la o las macros pueden modificar algunos aspectos como cambiar la combinación de teclas, o bien
agregar o quitar parte de los comentarios. Para ello presionamos el
botón del cuadro Macros donde nos aparecerá de nuevo el cuadro Opciones de la macro. Ahí puedo cambiar el método abreviado y/o la descripción de la macro.
Eliminar una macro
Cuando ya no necesites una macro, o bien que exista algún error al crearla puedes dar clic en el botón del cuadro de Macros donde te aparecerá un cuadro de información preguntándote si deseas confirmar la eliminación.
NOTA: Una macro grabada en el Libro de Macros Personal no se puede eliminar dando clic en el botón
del cuadro de Macros. Para ello debes seleccionar el botón donde se mostrará la
ventana de Visual Basic mostrando el código de las macros que tienes en ese libro, de ahí debes elegir el código
que se encuentre entre: “Sub” y “End Sub” de la macro que deseas eliminar. Y de esta forma eliminas en sí la
macro.
NOTA: al abrir un libro que tiene macros almacenadas, te aparece un cuadro de Advertencia de seguridad
justo bajo las fichas indicando que el libro contiene macros, donde deberás indicar si deseas abrir el libro con o
sin las macros activadas, tal como lo muestra la siguiente imagen.
.
Al hacer clic en opciones nos aparece el siguiente cuadro donde
podemos seleccionar y luego presionar el
botón para poder utilizar las macros de ese libro.
Establecer Seguridad en la macro
La seguridad de una macro es muy importante porque al ser un programa elaborado en Visual Basic puede ser utilizado en forma de virus. Se recomienda precaución a la hora de ejecutarlas. Para ello es conveniente contar con un programa antivirus que sea constantemente actualizado en su equipo y establecer el nivel de seguridad que sea confiable.
Para establecer el nivel de seguridad de las macros realizamos lo siguiente:
1. Del la ficha Programador (misma que puede o no estar activada). De ahí se selecciona el botón
donde se activará el cuadro de Centro de Confianza como se describe a continuación:
2. En Configuración de macros se elige el nivel de seguridad con el que se podrán ejecutar las macros los cuales son 4.
3. Se recomienda utilizar la opción “Deshabilitar todas las macros con notificación” ya que las macros deberán ser activadas manualmente, lo cual representa una opción viable para la seguridad del equipo.
Crear botón de macro
Otra forma de activar las macros es por medio de asignarlas a un botón de comando que se puede colocar en alguna de las barras de herramientas, el cual funcionará como un acceso directo para correr la macro.
Para crear un botón de macro realiza lo siguiente:
1.- De la ficha Insertar elige ya sea Imagen, Imágenes prediseñadas, o formas, e inserta la imagen que desees utilizar como si fuera botón para tu macro:
2.- Una vez insertado y colocado el “botón” en su posición final se selecciona la imagen y luego se presiona el botón derecho del ratón para posteriormente seleccionar Asignar macro…
3.- En la ventana Asignar macro selecciona la macro a la cual le asignarás el botón y finalmente presionas el
botón:
NOTA: Puedes cambiar la macro asignada a ese botón, seleccionando de nuevo el botón derecho del ratón
y volviendo a hacer clic en Asignar macro…
Crear una Macro de forma Manual
Para crear una macro de forma manual es necesario tener conocimientos de programación en general, y de Visual Basic en particular, por ser el lenguaje utilizado por Excel 2007. Aquí veremos en términos generales la forma en que se trabaja con el editor de Visual Basic.
Para activarlo se presiona la combinación de teclas Alt + F11.
Un módulo de trabajo es donde se almacena el código de las funciones y procedimientos de las macros, por lo que necesitamos abrir uno accesando al menú Insertar -> Módulo.
A continuación nos plantearemos si lo que vamos a crear es una función (en el caso que devuelva algún valor), o si será un procedimiento (el cual no devuelve valor alguno).
Una vez considerado lo que se realizará, ingresamos al menú Insertar -> Procedimiento.
Nos aparecerá un cuadro de diálogo como se describe a continuación:
Aquí se establece el nombre al procedimiento/función sin insertar espacios en su nombre.
Elegimos también el tipo ya sea Procedimiento, Función o Propiedad.
Además podemos seleccionar el Ámbito de ejecución. Si se establece como Público podremos utilizar el procedimiento/función desde cualquier otro módulo, pero si lo declaramos como Privado solo podremos utilizarlo dentro de ese módulo.
Una vez seleccionado el tipo de procedimiento y el ámbito presionamos Aceptar y se muestra el editor de Visual Basic donde escribimos las instrucciones necesarias para definir la macro.
Guardar una área de trabajo
Un archivo de área de trabajo es una herramienta que permite crear un respaldo de uno o varios libros abiertos de
manera que se pueda reanudar el trabajo más adelante con el mismo ambiente en que se guardaron en el área, es
decir; mismos tamaños de ventana, áreas de impresión, ampliaciones de pantalla y configuraciones de visualización,
los mismos tamaños de ventana, entre otras características.
Así mismo, al guardar un área de trabajo se genera un vínculo entre el archivo del área de trabajo y el archivo original,
al realizar cambios ya sea en el archivo de origen o en el de área de trabajo, sea actualizan automáticamente.
Por ejemplo se va a guardar u área de trabajo de tres libros con los que se ha trabajado ya, estos serán: Auditoría,
Base de Datos de empleados y Consolidar y para almacenarlos en un área de trabajo realiza lo siguiente:
1.- Abre los libros de trabajo que quedarán almacenado en el área de trabajo, en este caso Auditoría, Base de Datos
de empleados y Consolidar.
2.- Acomoda los libros abiertos como lo prefieras, aumenta el zoom o disponlos en mosaico, organízalos de forma
horizontal o vertical o como lo prefieras.
2.- Del menú Vista selección el botón , luego se abrirá un cuadro de diálogo Guardar área de trabajo,
como se describe a continuación:
3.- Teclea el nombre del área de trabajo y finalmente presiona
4.- Cierra todos los libros que almacenaste en el área de trabajo y finalmente accede al directorio donde guardaste el
área de trabajo y busca un ícono similar a: . Ábrelo y podrás ver tu área de
trabajo tal y como la guardaste previamente.
NOTA: El archivo del área de trabajo no contiene libros en sí, por lo que si borras el archivo de área de
trabajo los libros que ahí se listan no serán borrados, esta área la puedes volver a generar. Si borras cualquiera
de los archivos originales automáticamente se borra el área de trabajo.
Hipervínculos
Un hipervínculo es un enlace que se crea en alguna parte del archivo para que abra otra ubicación al hacer clic en él, y
se muestra regularmente como un texto subrayado en color azul. El destino puede ser una celda, una hoja de cálculo,
un archivo, una página web, una imagen, una dirección de correo electrónico o un programa.
Para insertar un hipervínculo realiza lo siguiente:
1.- Selecciona la celda o imagen a la que le realizarás el hipervínculo.
2.- De la ficha Insertar haz clic en el botón
3.- Del cuadro de diálogo Insertar hipervínculo selecciona primero hacia donde será realizada la vinculación:
4.- Elige Archivo o página Web existente, Lugar de este documento, Crear nuevo documento, Dirección de correo
electrónico.
5.- En la opción de aparece la ruta y nombre del archivo al que te estás enlazando.
6.- Da clic en para finalizar la vinculación.
TIP: Otra manera de insertar hipervínculos es presionando el botón derecho del ratón una vez colocado en
la celda o imagen que será utilizada para la vinculación y luego seleccionar la opción:
Modificar un hipervínculo
Para modificar un hipervínculo da clic derecho con el ratón en donde se encuentra este y del menú emergente
elige la opción y entonces podrás realizar los cambios que necesites.
Eliminar un hipervínculo
Para romper el enlace del hipervínculo, puedes dar clic derecho con el ratón donde se encuentra este y del menú
emergente elige la opción y una vez hecho esto ya no existirá mas el vínculo.
Contraseñas y bloqueos de Celdas
La seguridad es muy importante cuando no queremos que otras personas tengan acceso a la información de
nuestros libros de trabajo, por lo que se pueden establecer contraseñas para apertura del libro o bien bloqueo de
celdas para que la información sea de solo lectura y no la puedan modificar.
Establecer contraseña de apertura a un libro
Si se establece una contraseña de apertura, existe la posibilidad de que el libro no se pueda abrir si no se conoce
la contraseña, o bien se pueda activar pero como solo lectura, por lo cual no se podrán realizar cambios al libro si
no se conoce la contraseña para acceso total al archivo.
Existen dos tipos de contraseñas:
Contraseña para abrir: para que sólo puedan acceder al libro aquellas personas que conocen la contraseña.
Contraseña de escritura: para que cualquiera pueda acceder al libro de trabajo, pero solamente lo puedan
modificar aquellas personas que conozcan la contraseña.
Las contraseñas pueden tener como máximo 15 caracteres, distinguiendo entre mayúsculas y minúsculas.
Para establecer una contraseña de apertura al libro realiza lo siguiente:
1.- Seleccionar el Botón Office y elige la opción Guardar como...
2.- Hacer clic sobre la flecha de la derecha del botón
3.- Elegir la opción Opciones generales...
4.- Escribir la contraseña en el recuadro Contraseña para abrir o en el recuadro Contraseña de escritura
dependiendo del tipo de contraseña.
Al escribir la contraseña aparecerán ***** para que nadie pueda verla.
Hacer clic sobre el botón Aceptar.
5.- Aparecerá el siguiente cuadro de diálogo para confirmar la contraseña (es una forma de asegurarnos que la
contraseña que introduzcamos es la deseada y no nos hemos equivocado).
Volver a escribir la contraseña en el recuadro.
Hacer clic sobre el botón Aceptar para salir de la confirmación.
7.- Si no hemos escrito las dos veces la misma contraseña, Excel2007 nos pedirá volver a introducirla.
Hacer clic sobre el botón Guardar para cerrar el cuadro de guardar como.
8.- Cierra el libro.
9.- Abre de nuevo el libro que almacenaste con contraseña y te aparecerá un cuadro pidiendo que introduzcas la
contraseña correspondiente como lo muestra la figura.
10.- Introduce la contraseña correspondiente y da clic en Aceptar para que se abra el libro.
NOTA: Para eliminar la contraseña se realiza el mismo proceso que se utiliza para guardar con contraseña,
solo que debe eliminarse de las casillas y/o ,
dependiendo las que se hayan establecido y volver a guardar el libro para que se reemplace ahora sin
contraseña.
Proteger hojas de cálculo
Se pueden proteger hojas de cálculo en un libro para bloquear el acceso a las celdas y solo nos sirvan para
visualizarlas, más no para modificarlas.
Para proteger una hoja de cálculo realiza lo siguiente:
1.- De la ficha Revisar elige el botón
2.- Del cuadro de diálogo Proteger hoja activa la casilla
3.- Bajo introduce una contraseña para desproteger la hoja, la cual es
opcional.
4.- Bajo activa las casillas de las opciones a las que se pueda
tener acceso al proteger la hoja.
5.- Da clic en para que la hoja quede protegida.
6.- Trata de introducir un texto en alguna celda de la hoja protegida, y te aparecerá el siguiente cuadro con un
mensaje de advertencia:
Desproteger celdas de una hoja de cálculo
Todas las celdas de una hoja de cálculo se encuentran protegidas, pero no se hace notoria esta opción hasta que
se protege la hoja, solo que puede darse el caso de que al proteger la hoja, se requiera introducir información en
ciertas celdas antes de protegerla.
Para desproteger celdas haz lo siguiente:
1.- Selecciona el rango de celdas donde si vas a poder introducir información al proteger la hoja de cálculo.
2.- De la ficha Inicio selecciona del grupo fuente el botón
3.- Da clic en la pestaña Proteger y luego desactiva la casilla para que las celdas seleccionadas no
se protejan.
4.- Da clic en .
5.- Protege la hoja e introduce información en las celdas desbloqueadas.
NOTA: Para volver a bloquear todas las celdas, selecciona toda la hoja de cálculo y a continuación activa la
casilla de la pestaña Proteger que encuentras en el cuadro de Formato de Celdas.