Universidad Nacional de Ingeniería
Programa de Administración de Unidades de ServiciosIng. Oscar Issac Pérez Aráuz
Microsoft Excel 2010 permite analizar, administrar y compartir información mejor que nunca, lo que le ayuda a tomar decisiones mejores y más inteligentes. Las nuevas herramientas de análisis y visualización le ayudan a realizar un seguimiento y resaltar importantes tendencias de datos. Obtenga acceso fácilmente a datos
importantes dondequiera que vaya desde
prácticamente cualquier explorador web o Smartphone, Incluso puede cargar sus archivos en Internet y trabajar simultáneamente con otras personas en línea. Tanto como si produce informes financieros o administra sus gastos personales, Excel 2010 le brinda más eficiencia y flexibilidad para lograr sus objetivos.
Microsoft
Contenido
INTRODUCCIÓN ... 4
1.1 Historia Y Versiones ... 4
1.2 Especificaciones, Límites Y Problemas ... 5
1.3 Las Novedades De Excel 2010 ... 7
1.4 Excel 2010 Vs Excel 2007 ... 10
LISTAS Y TABLAS ... 14
2.1 Listas... 14
2.1.2 Listas Personalizadas ... 14
2.1.2Listas Desplegables / Validación de Datos ... 17
2.2 Tablas ... 22
2.3 Tablas Dinámicas ... 25
2.4 Ejercicios Propuestos ... 29
EL FORMATO CONDICIONAL Y LA SEGURIDAD EN EXCEL 2010 ... 31
3.1 Formato Condicional... 31 3.2 Seguridad ... 36 3.3 Ejercicios Propuestos ... 38 FORMULAS Y FUNCIONES ... 40 4.1 Referencias en fórmulas ... 43 4.2 Fórmulas matriciales ... 46 4.3 Funciones Lógicas Y, O, Si ... 51
4.4 Funciones de Bases de Datos ... 58
4.5 Funciones de Búsqueda ... 60 4.6 Auditoria de Formulas ... 65 4.7 Ejercicios Propuestos ... 67 GRAFICOS Y SUBTOTALES ... 70 5.1 Tipos de Gráficos ... 71 5.2 El eje secundario ... 82 5.3 Líneas de Tendencia ... 83 5.4 Subtotales ... 87
5.5 ¿Cómo se insertan los subtotales? ... 87
5.6 Ejercicios Propuestos ... 88
IMPORTACION DE DATOS ... 89
6.2 Importación desde la Web ... 92
6.3 Importación desde Access ... 93
6.4 Microsoft Query ... 95
6.4.1 La Base de Datos ... 95
6.4.2 El Archivo de Origen de Datos ... 97
6.4.3 Microsoft Query ... 99
6.4.4 Regresar los Datos a Excel ... 103
6.5 Ejercicios Propuestos ... 104
PRESENTACIÓN
El presente manual de Excel Avanzado ha sido diseñado para usarse en cursos de Microsoft Office Excel 2010 impartidos en el Programa de Administración de Unidades de Servicios de la Universidad Nacional de Ingeniería para usuarios con conocimientos previos de Excel. El texto empieza con una Introducción a Excel y sus versiones anteriores, además de hacer una comparación con su antecesor (Excel 2007) y analizar las novedades principales de la versión 2010. Los acápites siguientes se enfocan en el uso de ciertos comandos considerados necesarios para el uso y la administración de datos en un libro de calculo, reducción de errores, resaltar información en dependencia de los requerimientos del usuario, fundamentos de gráficos y macros para el desarrollo de aplicaciones automatizadas que contribuyan a la reducción de tiempo en procesos monótonos de trabajo.
Aunque Excel fue diseñado como un programa para operaciones financieras y contables hoy por hoy este programa es usado además por Estadistas, Ingenieros, Administradores de proyectos, matemáticos, investigadores, médicos e incluso abogados e historiadores, puesto que por su gran gama de comandos y funciones de todo tipo a sido de gran utilidad en el desarrollo de hojas de calculo que contribuyen a reducir la carga de trabajo y aumentar la rapidez en el desarrollo de informes especializados.
En el moderno mundo de hoy en día son muchas las empresas que adquieren software especializado para la administración de información y la ejecución de procesos que facilitan en gran cantidad el trabajo diario, sin embargo muchos de estos están diseñados bajo un marco fijo de trabajo y no están abiertos a posibles cambios en los procesos ya sea para mejorar o por factores externos como los legales, es ahí donde nos vemos en la necesidad de hacer uso de aquellas herramientas que ya poseemos pero que no explotamos, y que podrán hasta cierto punto resolver muchas limitaciones que los software mas caros no pueden.
El curso y manual de Excel avanzado esta estructurado para poder aprovechar al máximo las ventajas que ofrece una de las herramientas mas poderosas en el mundo de las Tecnologías de Información, facilitando las operaciones a través de comandos y funciones poco conocidos y de gran utilidad.
Capitulo 1:
INTRODUCCIÓN
Microsoft Excel es una aplicación para manejar hojas de cálculo. Este programa es desarrollado y distribuido por Microsoft, y es utilizado normalmente en tareas financieras y contables.
1.1 Historia Y Versiones
Microsoft comercializó originalmente un programa de Hoja de cálculo llamado Multiplan en 1982, que fue muy popular en los sistemas CP/M, pero en los sistemas MS-DOS perdió popularidad frente al Lotus 1-2-3. Microsoft publicó la primera versión de Excel para Mac en 1985, y la primera versión de Windows (numeradas 2-05 en línea con el Mac y con un paquete de tiempo de ejecución de entorno de Windows) en noviembre de 1987. Lotus fue lenta al llevar 1-2-3 para Windows y esto ayudó a Microsoft a alcanzar la posición de los principales desarrolladores de software para hoja de cálculo de PC. Este logro solidificó a Microsoft como un competidor válido y mostró su futuro de desarrollo como desarrollador de software GUI. Microsoft empujó su ventaja competitiva lanzando al mercado nuevas versiones de Excel, por lo general cada dos años. La versión actual para la plataforma Windows es Excel 14.0, también denominada Microsoft Excel 2010. La versión actual para Mac OS X es Microsoft Excel 2011.
La lista de versiones de Microsoft Excel que han sido lanzadas al mercado para Microsoft Windows es:
En el año 1987 Excel 2.0. En el año 1990 Excel 3.0. En el año 1992 Excel 4.0.
En el año 1993 Excel 5.0 (Office 4.2 & 4.3). En el año 1995 Excel 7.0 (Office ‘95). En el año 1997 Excel 8.0 (Office ‘97). En el año 1999 Excel 9.0 (Office 2000). En el año 2001 Excel 10.0 (Office XP). En el año 2003 Excel 11.0 (Office 2003). En el año 2007 Excel 12.0 (Office 2007). En el año 2010 Excel 14.0 (Office 2010).
Excel fue la primera hoja de cálculo que permite al usuario definir la apariencia (las fuentes, atributos de carácter y celdas). También introdujo re computación inteligente de celdas, donde celdas dependientes de otra celda que han sido modificadas, se actualizan al instante (programas de hoja de cálculo anterior recalculaban la totalidad de los datos todo el tiempo
gráfica, y permite a los usuarios realizar, entre otras muchas aplicaciones, listados usados en combinación de correspondencia.
Cuando Microsoft primeramente empaquetó Microsoft Word y Microsoft PowerPoint en Microsoft Office en 1993, rediseñó las GUI’s de las aplicaciones para mayor coherencia con Excel, producto insigne de Microsoft en el momento.
Desde 1993, Excel ha incluido Visual Basic para Aplicaciones (VBA), un lenguaje de programación basado en Visual Basic, que añade la capacidad para automatizar tareas en Excel y para proporcionar funciones definidas por el usuario para su uso en las hojas de trabajo. VBA es una poderosa anexión a la aplicación que, en versiones posteriores, incluye un completo entorno de desarrollo integrado (IDE). La grabación de macros puede producir código (VBA) para repetir las acciones del usuario, lo que permite la automatización de simples tareas. (VBA) permite la creación de formularios y controles en la hoja de trabajo para comunicarse con el usuario. Admite el uso del lenguaje (pero no la creación) de las DLL de ActiveX (COM); versiones posteriores añadieron soporte para los módulos de clase permitiendo el uso de técnicas de programación básicas orientadas a objetos.
La funcionalidad de la automatización proporcionada por (VBA) originó que Excel se convirtiera en un objetivo para virus en macros. Este fue un grave problema en el mundo corporativo hasta que los productos antivirus comenzaron a detectar estos virus. Microsoft tomó medidas tardíamente para prevenir el uso indebido de Excel mediante la adición de la capacidad para deshabilitar la ejecución automática de las macros al abrir un archivo.
1.2 Especificaciones, Límites Y Problemas
Las características, especificaciones y límites de Excel han variado considerablemente de versión en versión, exhibiendo cambios en su interfaz operativa y capacidades desde el lanzamiento de su versión 12.0 mejor conocida como Excel 2007. Se puede destacar que mejoró su límite de columnas ampliando la cantidad máxima de columnas por hoja de cálculo de 256 a 16.384 columnas. De la misma forma fue ampliado el límite máximo de filas por hoja de cálculo de 65.536 a 1.048.576 filas por hoja. Otras características también fueron ampliadas, tales como el número máximo de hojas de cálculo que es posible crear por libro que pasó de 256 a 1.024 o la cantidad de memoria del PC que es posible emplear que creció de 1 GB a 2 GB soportando además la posibilidad de usar procesadores de varios núcleos. Uno de los problemas conocidos y relevantes de esta hoja de cálculo, es el hecho de su incapacidad para manejar fechas anteriores a 1900 (incluyendo versiones para Mac OS X), es decir, no puede manejar campos en formato de fecha anteriores a dicho año (como acontecimientos históricos). Este problema se ha venido presentando desde versiones más antiguas de 16 bits, persistiendo aún en la versión actual.
El 22 de septiembre del 2007 se informó que la hoja de cálculo Excel 2007 mostraba resultados erróneos bajo ciertas condiciones. Particularmente para algunas parejas de
números, para los que el producto sea 65535 (tales como 850 y 77.1), Excel muestra como resultado de la operación 100000. Esto ocurre con alrededor del 14.5% de tales pares. Además, si se suma uno a este resultado Excel lo calcula como 100001. No obstante, si se resta uno al resultado original, entonces muestra el valor correcto 65534 (también si se multiplica o divide por 2, muestra los valores correctos 131070 y 32767.5, respectivamente). Microsoft informó en el blog de Microsoft Excel, que el problema existe al mostrar seis puntos flotantes específicos entre 65534.9995 y 65535, y seis valores entre 65535.99999999995 y 65536 (no incluye los enteros). Cualquier cálculo realizado con uno de estos valores se mostrará de modo incorrecto. El valor del cálculo almacenado y pasado a otra celda es correcto, sólo el valor mostrado estaría errado. Sin embargo, en algunas instancias, como al redondear el valor con cero dígitos decimales, almacenará un valor incorrecto en memoria. Este error se introdujo con los cambios realizados a la lógica de información en pantalla de la versión 2007, y que no existe en las versiones anteriores. El 9 de octubre de 2007 Microsoft lanzó un parche para este bug. Este problema también queda subsanado con la instalación del Service Pack 1, y desapareció por completo en todas las versiones de Excel lanzadas al mercado posteriormente.
Dada la importancia que posee el estudio de las finanzas en ámbito académico, toma especial interés el manejo de dichos conceptos de forma ágil, para conseguir rapidez y precisión en el análisis. La forma más rápida de conseguir dicho objetivo es a través de la aplicación de finanzas en una hoja de cálculo, para el caso, Excel.
Las hojas de cálculo proporcionan una solución rápida y sencilla a las nuevas exigencias que se presentan en el mundo actual. Excel a través de un ambiente gráfico permite realizar operaciones matemáticas básicas y complejas y desarrollar diferentes funciones (financieras, lógicas, estadísticas, matemáticas, etc.).
El objetivo del curso es servir de guía en la aplicación de los conceptos financieros básicos en Microsoft Excel, ya que este facilita la realización de operaciones financieras a través de fórmulas y funciones; permite una excelente presentación para los datos con una buena representación gráfica y el intercambio de información con otras aplicaciones.
La aplicación de Finanzas en Excel puede llegar a ser tan buena como las personas que lo apliquen puedan hacerlo. El reto es conseguir manejar las herramientas básicas, intermedias y avanzadas de Excel, para poder aplicarlas en el desarrollo de nuestro trabajo cotidiano. Microsoft Excel 2010 permite analizar, administrar y compartir información de más formas que nunca, lo que le ayuda a tomar decisiones mejores y más inteligentes. Las nuevas herramientas de análisis y visualización le ayudan a realizar un seguimiento y resaltar importantes tendencias de datos. Obtenga acceso fácilmente a datos importantes dondequiera que vaya desde prácticamente cualquier explorador web o Smartphone. Incluso puede cargar sus archivos en Internet y trabajar simultáneamente con otras personas en línea. Tanto como si produce informes financieros o administra sus gastos
1.3 Las Novedades De Excel 2010
Excel 2010 ofrece nuevas características y herramientas eficaces para ayudarle a descubrir patrones o tendencias que pueden llevar a decisiones más informadas y mejorar la capacidad de analizar grandes conjuntos de datos.
Obtenga un resumen visual de los datos mediante pequeños gráficos que caben dentro de una celda junto a los datos del texto con los nuevos mini gráficos.
Filtre grandes cantidades de información de forma rápida e intuitiva con la nueva funcionalidad Segmentación de datos y mejore el análisis visual de tablas dinámicas y gráficos dinámicos.
Obtenga análisis eficaces desde el escritorio
Los refinamientos de Excel 2010 y las mejoras en su rendimiento le permiten llevar a cabo su trabajo con mayor rapidez y facilidad.
Use el nuevo Filtro de búsqueda para restringir rápidamente las opciones de filtro disponibles en las tablas y las vistas Tabla dinámica y Gráfico dinámico. Encuentre exactamente lo que busca entre un millón o más elementos, al instante.
PowerPivot para Excel 2010, un complemento gratuito, le permite experimentar una manipulación rápida de grandes conjuntos de datos (generalmente en millones de filas) y una integración de datos simplificada. Además, podrá compartir sus análisis sin esfuerzos a través de SharePoint Server 2010.2
Trabaje con cantidades masivas de información (más de 2 gigabytes) y saque el máximo partido a las inversiones en hardware nuevas y existentes usando la versión de 64 bits de Office 2010.3
Rompa las barreras y trabaje en equipo de nuevas maneras
Excel 2010 ofrece formas simples de permitir que las personas trabajen conjuntamente en libros, lo cual sirve para mejorar la calidad de su trabajo. Lo mejor de todo es que incluso las personas con versiones anteriores de Excel pueden participar sin problemas.
Ahora puede trabajar con otras personas simultáneamente en el mismo libro en casi cualquier explorador web mediante Excel Web App.
Si trabaja en una compañía pequeña o por su cuenta, lo que necesita es una cuenta gratuita de Windows Live ID para crear libros simultáneamente con otros usuarios.
Servicios de Excel de SharePoint le permite compartir los libros fáciles de leer en un explorador web con su equipo conservando una sola versión del libro.
Obtenga acceso a los libros en cualquier momento y lugar
Obtenga la información que necesita, en el lugar y del modo en que la desea. Ahora puede obtener acceso fácilmente a los libros llevando la experiencia de Excel con usted y mantenerse al día dondequiera que esté.
Microsoft Excel Web App: edite prácticamente en cualquier lugar. Vea y edite sus
libros en un explorador web cuando no está en su hogar, escuela u oficina.4
Microsoft Excel Mobile 2010: mejore enormemente la eficacia de sus pequeños
dispositivos. Manténgase actualizado al instante usando una versión móvil de Excel que se ajusta especialmente a su teléfono de Windows.5
Tanto si está trabajando en su presupuesto personal o en los gastos de viaje como colaborando con un equipo en proyectos escolares o laborales e incluso cuando los libros
superan el millón de filas, Excel 2010 le permite llevar a cabo las tareas pendientes fácil y rápidamente, con más flexibilidad y mejores resultados.
1.4 Excel 2010 Vs Excel 2007
Parece ser que las diferencias entre Excel 2007 y Excel 2010 no serán tan significativas como las que se produjeron con el cambio entre la versión de Excel 2003 y Excel 2007. Así es los usuarios de Excel 2007 tendrán una adaptación prácticamente inmediata, y los de versiones anteriores no deberán angustiarse sobre la versión a la que adaptarse.
Desde hace tiempo es posible descargarse y utilizar una versión proporcionada por Microsoft para que sean los propios usuarios los que aporten mejoras.
Desde el punto de vista formal se pueden apreciar diferencias a nivel de la interface. Aunque la cinta de opciones permanece prácticamente igual que en la versión anterior, se sustituye el botón de office redondo por una especie de pestaña de color verde como se aprecia en la siguiente imagen.
En la cinta de opciones dispondremos de la nueva pestaña Addins. Según parece, habrá más posibilidades de personalización en la interface de Excel 2010. Siendo posible recuperar las opciones predeterminadas de manera sencilla.
Desplegando esta pestaña que sustituye al anterior Botón de Office se observa cómo parece que funcionará de una manera similar si bien con diferencias en cuanto a la forma y con mayor información
Otra de las mejoras curiosas es la presencia de unos gráficos situados en una celda llamados Sparklines. Con Excel 2003 utilizaba una solución más rudimentaria pero conceptualmente idéntica que os comentaba en la entrada Gráficos sobre celdas. Esta utilidad nos permitirá ver tendencias y picos en series de datos de forma inmediata. Como digo era una necesidad que muchos reclamábamos.
Hay una variación a la hora de trabajar con tablas dinámicas “Slicer” de forma que la selección y filtrado de datos funciona de manera diferente. Según la página de Microsoft supondrá un ahorro de tiempo y mayor eficiencia en el filtrado.
Una nueva vista llamada Microsoft Office Backstage cambia la manera de imprimir, guardar, compartir y publicar las hojas de cálculo simplificando los pasos a seguir respecto a versiones anteriores. Por otro lado, aparecen opciones de pegado en el menú contextual.
Incremento de las capacidades del formato condicional y los gráficos dinámicos.
Guarda automáticamente los documentos no guardados durante 4 días. Siempre que hayamos estado trabajando con ellos al menos 10 minutos. Esto es útil para subsanar las catástrofes que se producen con los pequeños descuidos.
Opción de imprimir pantallazos lo que es útil para evitar tener que abrir nuevos programas tipo snagit o la opción Imprimir Pantalla y tratar la imagen de la que como opción. Otra mejora es la posibilidad de trabajar con formas en las macros.
Una mejora que parece que se va a producir estriba en la mejora de la compatibilidad de los ficheros xls. Al parecer, los documentos creados con Excel 2010 se podrán abrir con versiones anteriores. Si esta mejora es cierta, será un gran avance ya que no todas las empresas utilizan la misma versión de Excel. De hecho me he encontrado con grupos de
problemas no solo de compatibilidad sino también en cuanto a formatos y utilidades, fórmulas.
Posibilidad de trabajar en equipo en una misma hoja de cálculo. Se puede ver quién está trabajando con la misma hoja de cálculo al mismo tiempo. Todas las modificaciones se controlan y marcan inmediatamente para mantenerlo al tanto de cuándo y dónde se realiza cada cambio. Personalmente tengo curiosidad por probar esta propiedad, entiendo que tendrá utilidad para cuestiones como rellenado de datos o trabajo con aplicaciones creadas, pero en cuanto a programación o creación de aplicaciones tengo mis dudas.
La versión de 64bits aumenta el límite que anteriormente se situaba en 2 GB. Realmente una capacidad más que suficiente para las necesidades habituales. Mis libros con más peso no llegan a las 100 Megas.
Posibilidad de almacenar cualquier hoja de cálculo en la web para que esté disponible en cualquier momento y lugar del mundo, inclusive desde equipos de telefonía móvil que posean Windows Mobile como sistema operativo y la herramienta Excel Web App.
Capitulo 2:
LISTAS Y TABLAS
2.1 Listas
En versiones anteriores a Excel 2010 y 2007, una lista era una sección de una hoja de cálculo que se podía manejar por separado de la hoja de cálculo mayor, termino que actualmente es utilizada para TABLAS. Hoy en día, en el ámbito de Microsoft Excel, una lista es una palabra reservada para definir una serie de ítems reunidos en un mismo grupo, algunos ejemplos de estas son:
2.1.2 Listas Personalizadas
Las Listas Personalizadas son un conjunto de nombres que forman parte de un grupo de valores reconocidos por Excel y que ofrecen al usuario la facilidad de poder autocompletar los ítems de dicha lista, con la ayuda del mouse. Algunos ejemplos de listas personalizadas son:
Días de la Semana (Lunes, Martes, Miércoles…) Meses del Año (Nero, Febrero, Marzo, Abril…) Números (1, 2, 3, 4, 5…)
Patrones. (1, 5, 9, 13, 17…)
Sin embargo, además de estas Listas, en Excel usted es capaz de agregar sus propias listas personalizadas a partir de los siguientes pasos:
1. Clic en el menú Archivo --- Opciones.
2. Del menú de la ventana desplegable, seleccione la llamada “Avanzadas”. Listas Personalizadas Listas Desplegables
3. Con ayuda de la barra de desplazamiento, alcance la parte final de las diferentes opciones presentadas, hasta visualizar el botón de opción llamado “Listas Personalizadas” y dé clic sobre él.
4. Aparecerá la ventana “Lista Personalizada”, en la cual fácilmente identificara que esta dividida en 2 partes, la parte izquierda contiene todas las Listas personalizadas que por defecto utiliza Excel. En la parte derecha encontrara una ventana en blanco, en la cual deberá escribir los ítems de su lista separados uno del otro por ENTER. Una vez listo dar clic en el botón Aceptar.
En el ejemplo, se esta agregando una lista personalizada con los departamentos de Nicaragua.
NOTA: Si usted ya tiene la lista digitada en su hoja de EXCEL podrá hacer uso del botón para poder ir a su hoja y seleccionar las celdas que contienen los elementos de su lista; vuelva a dar clic en el botón y presiones Importar.
6. Presione Aceptar para salir de la ventana de Lista Personalizada y Aceptar (o Cancelar) para salir de la ventana de opciones.
7. Escribe uno de los ítems de su Lista Personalizada y con ayuda del mouse arrastre el valor en las celdas contiguas para poder autocompletar su nueva Lista.
2.1.2Listas Desplegables / Validación de Datos
La validación de datos es una función de Excel que permite establecer restricciones respecto a los datos que se pueden o se deben escribir en una celda. La validación de datos puede configurarse para impedir que los usuarios escriban datos no válidos. Si lo prefiere, puede permitir que los usuarios escriban datos no válidos en una celda y advertirles cuando intenten hacerlo. También puede proporcionar mensajes para indicar qué tipo de entradas se esperan en una celda, así como instrucciones para ayudar a los usuarios a corregir los errores.
Por ejemplo, en un libro de estadísticas, puede configurar una celda para permitir únicamente números de cuenta de tres caracteres, o para seleccionar entre Hombre y Mujer.
En otras palabras, la Validación de Datos prepara su hoja de cálculo para condicionar el tipo de información que debe ingresarse en una celda determinada. Para efectuarla asegúrese de realizar los siguientes pasos:
1. Seleccionar la(s) celda(s) a las que aplicara la validación de Datos. Seleccione la opción “Validación de Datos” que se encuentra en el Menú Datos.
La Ventana de Validación de Datos esta compuesta por 3 Pestañas: Configuración.
Mensaje de Entrada. Mensaje de Error.
2. En la Ventana “Configuración”, deberá seleccionar la Regla o restricción que limitara el ingreso de los Datos a través del Criterio de Validación.
a. Cualquier Valor: No existen Limitaciones, el usuario de la hoja podrá escribir cualquier tipo de Dato.
b. Numero Entero: Con este criterio asegurara que las personas únicamente ingresen Números Enteros (Negativos, Positivos o Cero) en las celdas
seleccionadas, sin embargo, usted deberá de seleccionar el rango posible para dichos números.
Ejemplo: Edades, Cantidad de Libros vendidos, etc.
c. Decimal: Este criterio es similar al criterio de Números Enteros, a diferencia que con este criterio el usuarios podrá escribir números Decimales.
Ejemplo: Salario, Distancias, Libras.
d. Lista: El criterio Lista guarda un conjunto de ítems, que son visibles a través del uso de una Lista Desplegable posicionada sobre la celda seleccionada. Usted deberá escribir cada uno de los elementos de la Lista separados por coma.
Ejemplo: Departamentos del País, Sexo, Escolaridad.
e. Fecha: Con este criterio asegurara que las personas únicamente ingresen Fechas específicas ubicadas en un determinado rango.
f. Hora: Las personas únicamente pueden agregar Horas especificas ubicadas en un determinado rango.
g. Longitud de Texto: Este criterio de Validación determina la cantidad de caracteres que podrá escribir en la celda seleccionada, cabe señalar que usted podrá agregar cualquier tipo de carácter siempre y cuando cumpla con la
h. Personalizada: Esta opción permite a usted, crear sus propias reglas de Validación a través del uso de una función o de una formula.
3. En la ventana “Mensaje de Entrada”, usted será libre de escribir un mensaje al usuario, apenas este toque la celda con el cursor y que servirá como aviso para notificar lo que se deberá escribir.
4. Los mensajes de Error, surgen cuando un usuario a ingresado información “NO VALIDA” o “NO PERMITIDA” a la celda seleccionada; es decir, no esta cumpliendo el criterio de Validación escrito en “Configuración”.
Existen 3 tipos de mensa de Error:
Icono Tipo Se Usa Para
Detener Evitar que los usuarios escriban datos no válidos en una celda. Un mensaje de alerta Detener tiene dos opciones: Reintentar o Cancelar Advertencia Advertir a los usuarios que los datos que han escrito no son válidos,
pero no les impide escribirlos. Cuando aparece un mensaje de alerta Advertencia, los usuarios pueden hacer clic en Sí para aceptar la entrada no válida, en No para editarla o en Cancelar para quitarla Información Informar a los usuarios que los datos que han escrito no son válidos,
pero no les impide escribirlos. Este tipo de mensaje de error es el más flexible. Cuando aparece un mensaje de alerta Información, los usuarios pueden hacer clic en Aceptar para aceptar el valor no válido o en Cancelar para rechazarlo
6. Clic en Aceptar. Si todo ha ido bien, en las celdas previamente seleccionadas podrá únicamente ingresar los datos que apliquen el criterio de validación definido en la pestaña Configuración.
¿Cuándo es útil la validación de datos?
La validación de datos es sumamente útil cuando desea compartir un libro con otros miembros de la organización y desea que los datos que se escriban en él sean exactos y coherentes. Puede usar la validación de datos para lo siguiente, entre otras aplicaciones:
Restringir los datos a elementos predefinidos de una lista. Por ejemplo, puede limitar los tipos de departamentos a Ventas, Finanzas, Investigación y desarrollo y TI.
Restringir los números que se encuentren fuera de un intervalo específico. Por ejemplo, puede especificar un salario mínimo para el cálculo de deducciones de un empleado de su empresa.
Restringir las fechas que se encuentren fuera de un período de tiempo específico. Por ejemplo, puede especificar un período de tiempo entre la fecha actual y los 3 días siguientes.
Restringir las horas que se encuentren fuera de un período de tiempo específico. Por ejemplo, puede especificar un período de tiempo para servir el desayuno entre la hora en que abre el restaurante y cinco horas después.
Limitar la cantidad de caracteres de texto. Por ejemplo, puede limitar el texto permitido en una celda a 10 caracteres o menos.
Validar datos según fórmulas o valores de otras celdas. Por ejemplo, puede usar la validación de datos para establecer un límite máximo para comisiones y bonificaciones de 3.600 dólares, según el valor de nómina proyectado general. Si los usuarios escriben un valor de más de 3.600 dólares en la celda, aparecerá un mensaje de validación.
Sugerencias para trabajar con la validación de datos
En la siguiente lista, encontrará sugerencias para trabajar con la validación de datos en Excel. Si tiene previsto proteger la hoja de cálculo o el libro, hágalo después de haber terminado de configurar la validación. Asegúrese de desbloquear cualquier celda validada antes de proteger la hoja de cálculo. De lo contrario, los usuarios no podrán escribir en las celdas.
Si tiene previsto compartir el libro, hágalo únicamente después de haber configurado la validación y la protección de datos. Después de compartir un libro, no podrá cambiar la configuración de validación a menos que deje de compartirlo pero Excel continuará validando las celdas que haya designado mientras el libro esté compartido.
Puede aplicar la validación de datos a celdas en las que ya se han escrito datos. No obstante, Excel no le notificará automáticamente que las celdas existentes contienen datos no válidos. En este escenario, puede resaltar los datos no válidos indicando a Excel que los marque con un círculo en la hoja de cálculo.
Para quitar rápidamente la validación de datos de una celda, seleccione la celda y a continuación abra el cuadro de diálogo Validación de datos (ficha Datos, grupo Herramientas de datos). En la ficha Configuración, haga clic en Borrar todos.
Para buscar las celdas de la hoja de cálculo que tienen validación de datos, en la ficha Inicio en el grupo Modificar, haga clic en Buscar y seleccionar y a continuación en Validación de datos. Una vez que haya encontrado las celdas que tienen validación de datos, puede cambiar, copiar o quitar la configuración de validación.
Si la validación de datos no funciona, asegúrese de que los usuarios no
están copiando datos ni rellenando celdas. La validación de datos está diseñada para mostrar mensajes y evitar entradas no válidas sólo cuando los usuarios escriben los datos directamente en una celda. Cuando se copian datos o se rellenan celdas, no aparecen mensajes.
Para impedir que los usuarios copien datos y rellenen celdas mediante la operación de arrastrar y colocar, desactive la casilla de verificación Permitir arrastrar y colocar el controlador de relleno y las celdas, en la categoría Avanzadas del cuadro de diálogo Opciones de Excel (pestaña Archivo, comando Opciones) y, a continuación, proteja la hoja de cálculo.
2.2 Tablas
En Excel no debe confundirse el termino Rango de Datos con el termino Tabla de Datos. Un Rango de Datos son un conjunto de celdas independientes, sin relación alguna entre si. Por el contrario una Tabla de Datos contiene datos en celdas que forman parte de un conjunto, en este caso una Tabla. Una tabla en Excel es un conjunto de datos organizados en filas o registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados. Es como una tabla de base de datos, de hecho también se denominan listas de base de datos.
Para poder diferenciar una Tabla de un Rango, se deberán identificar las siguientes características:
Al seleccionar una de las celdas de la Tabla, podrá visualizar el menú Diseño en la cinta de menús de la parte superior.
En la última celda de la Tabla de Datos, podrá ver una línea de color azul que marca el final de la tabla y que sirve para agregar filas y/o columnas a la misma.
A diferencia de los Rangos, los encabezados de su Tabla siempre serán visibles sin importar en que celda o fila de su tabla se encuentra, quedando anclados en la parte superior.
Como convertir un Rango en una Tabla
1. Seleccione las celdas que desea convertir en Tablas. 2. En el menú Insertar seleccione la opción Tabla.
3. En la Ventana que se muestra, confirme que las celdas
seleccionadas son las que desea convertir en Tabla, en caso contrario vuelva a seleccionar las celdas. Asegúrese que la opción “La Tabla tiene encabezados”, esta seleccionada para garantizar que los datos de la primera fila se conviertan en los encabezados de su nueva tabla. Si esta opción no es seleccionada Excel ingresara sus propios encabezados (Columna1, Columna2, Columna3, etc.)
Herramientas de la Tabla. El menú Diseño.
El menú “Diseño”, únicamente será visible si usted selecciona una de las celdas dentro de su Tabla de Datos. El menú contiene una serie de comandos funcionales para las Tablas de Excel, y se divide en:
Propiedades: Dentro del grupo Propiedades se podrá dar un nombre a la Tabla que sirva para hacer referencia a todos los datos que en ella se contengan. Así mismo contiene una herramienta para poder cambiar el tamaño a
la tabla, a través de la adición de filas y columnas o eliminando las mismas.
Herramientas: El Grupo Herramientas contiene 3 comandos que permiten al usuario realizar:
o Resumen con Tabla Dinámica.
podrá seleccionar criterios que indiquen a Excel si un dato es o no Duplicado, eliminándolo(s) de la Tabla, sin embargo tome en cuenta que Ud. no podrá controlar cual de los registros es el que se eliminara.
o Convertir en Rango. Si usted ha decidido no Trabajar mas sus datos en una Tabla, esta opción le permitirá convertirlos nuevamente a Rango.
Datos Externos de la Tabla: El grupo de Datos Externos, le proporciona herramientas para enviar sus datos a programas como Sharepoint o Diagramas de Visio. Así mismo, en caso que usted haya importado la información desde un origen de
datos externo y dicha información fue actualizada, no será necesario importar nuevamente, únicamente deberá “Actualizar” los datos.
Opciones de estilo de la Tabla: Marcan en la Tabla una forma especial de cada una de sus partes identificándolas fácilmente, así mismo permite Mostrar una Fila de Totales en la cual es posible realizar operaciones matemáticas de manera automática.
Estilos de tabla: Los estilos de Tabla, son formatos previamente definidos en Excel que
dan al usuario una manera rápida de poder crear la Tabla con un estilo vistoso.
Ventajas de trabajar con una Tabla.
Dentro de algunas de las ventajas que ofrece la administración de datos en Tablas tenemos: Ordenar la los registros: Aunque esta opción se puede hacer tanto con Rangos como
en Tablas, la diferencia radica en que al realizar el ordenamiento en un Rango se debe ser cuidadoso al seleccionar las columnas con las que se trabajara, puesto que podría ocasionar una combinación entre los registros y sus campos, sin embargo en una Tabla no deberá preocuparse, debido a que todas las celdas son tratadas como parte de un mismo conjunto, ayudando a mantener el orden lógico entre los datos.
A través de la Fila de Totales usted podrá realizar operaciones matemáticas sin necesidad de escribir una sola función, únicamente deberá seleccionarla de la lista desplegable en la celda de interés.
Al escribir una función en una celda, esta automáticamente será copiada en las celdas restantes de realizando el calculo en dependencia del registro de la formula. Las funciones de la Tabla son Dinámicas y no están amarradas a una celda especifica,
sino a un registro de datos, por ejemplo:
2.3 Tablas Dinámicas
Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado 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...
Con un informe de tabla dinámica puede resumir, analizar, explorar y presentar un resumen de los datos de la hoja de cálculo o un origen de datos externos (datos externos: datos que se almacenan fuera de Excel; por ejemplo, bases de datos creadas en Access, dBASE, SQL Server o en un servidor Web.). Un informe de tabla dinámica es especialmente útil cuando tiene una larga lista de cifras para sumar y los datos agregados o subtotales podrían servir para mirar los datos desde perspectivas diferentes y comparar las cifras de datos similares. El diseño de una Tabla Dinámica radica en conocer su estructura e identificar como queremos mostrar la información de interés, para lo cual podemos hacer uso de 3 preguntas clave ¿Qué queremos ver?, ¿Cómo lo queremos ver?, ¿Cuales queremos ver?. Las Tablas Dinámicas se conforman de 3 partes:
Filtros: Como todo Filtro, se encarga de Restringir la información, de mostrar solamente los datos de interés. Fácilmente podemos identificar si necesitamos un Filtro a través de la pregunta ¿Cuáles queremos ver?
Encabezados de Fila/ Columna: Conforman el marco bajo el cual se creara la Tabla Dinámica, separan la información de acuerdo a ¿Como queremos visualizarla?
Formulas en un Rango de Datos
Valores: Es la información de interés que será colocada en las celdas centrales de la Tabla Dinámica; es decir los datos numéricos que realizan el resumen de nuestra Tabla de Datos y que conforman nuestro reporte.
Insertar una Tabla Dinámica
1. Seleccione las celdas que desea Resumir con Tabla Dinámica; estas celdas deben contener información y todos sus encabezados.
2. En el menú Insertar seleccione la opción Tabla Dinámica. 3. En la ventana que aparece, servirá para:
a. Confirmar que las celdas seleccionadas son las que desea convertir en Tabla o utilizar una fuente de datos diferente a la hoja de cálculo actual.
b. Indicar a Excel donde deberá aparecer el resumen de la Tabla Dinámica, en una Hoja de cálculo nueva o en un lugar especifica que usted seleccionara con la ayuda del botón . Clic en Aceptar.
4. En el lado derecho de la Hoja de Calculo se mostrara 1 panel que FILTRO ¿Cuáles…? ENCABEZA DO DE FILA ¿Como…?
ENCABEZADO DE COLUMNA ¿Como…?
VALORES ¿Qué…?
en la parte inferior identificara 4 paneles rotuladas con las diferentes partes que conforman la Tabla Dinámica: Filtros, Encabezado de Columna, encabezado de Fila y Valores.
5. Arrastre a cada uno de los paneles inferiores, los encabezados de su Tabla de Información y lograra ver como se forma la Tabla Dinámica.
Una vez que la Tabla Dinámica ha sido creada, usted podrá fácilmente ver un resumen de los datos de interés intercambiando la forma de esta, de manera rápida y sencilla. Ubicándose en cualquiera de las celdas de una Tabla Dinámica, observara el despliegue de 2 nuevas pestañas en el menú: Opciones y Diseño.
A través del menú de Opciones, la Tabla Dinámica podrá cambiar los valores presentados en función de operaciones matemáticas mostradas, calcular campos que no existen en la tabla original, insertar Gráficos, etc.
En el menú Diseño, fácilmente podrá cambiar el formato de la Tabla, así como los campos de Totales y subtotales de la misma.
Configuración de Campo
La configuración de Campo de Valor, disponible dentro del menú Opciones de una Tabla Dinámica, le permite cambiar el tipo de operación matemática a realizar en el campo de Valores de su Tabla Resumen:
Además de esto, existe la posibilidad de Mostrar los Valores como Porcentaje, tomando de Referencia uno de los valores existentes en su Tabla de Datos Original o en relación a un valor especifico de Filas o Columnas de la Tabla Pivote.
Los Campos Calculados permiten crear campos adicionales a los existentes a la Tabla de Datos original, a partir de operaciones matemáticas o funciones creadas a partir de los valores existentes en el origen de la información.
El Diseño de una Tabla Dinámica
Conforme se agregan encabezados de Filas o Encabezados de Columna en su Tabla de Resumen, encontrara que cada vez podrá hacerse más complicada la presentación de los datos, sin embargo Excel le permite poder cambiar el estilo de Diseño de los encabezados de Columnas agregando subtotales, totales, esquematizando los campos, compactando la información e incluso con la inserción de bandas de colores que permiten separar los datos.
2.4 Ejercicios Propuestos
A. Doodle Co. Es una empresa encuestadora que opera en Nicaragua y que oferta sus servicios en estudios de mercado a las empresas de bienes y servicios del país. Están fuertemente convencidos que no necesitan adquirir programas de software costosos para poder llevar a cabo el proceso de depuración de encuestas utilizadas en sus estudios, por lo cual han decidido utilizar el software Microsoft Excel 2010 para poder diseñar hojas de calculo que contengan la información general del encuestado para la captura de información.
i. En vista que en muchas ocasiones la muestra de datos será aleatoria (no sabemos cuantas serán las encuestas a realizar) convierta los campos de la planilla en una Tabla.
ii. Utilizando las herramientas de Validación de Datos asegúrese que los digitadores cometan la menor cantidad de errores posibles, restringiendo las celdas de entrada a aceptar solamente la información necesaria, de acuerdo a cada pregunta:
Encuesta N°: Número entero positivo. Sexo: Masculino; Femenino
Edad: Numero entero positivo mayor a 16 y menor a 60. ¿Usted usa jeans?: Si; No; No Sabe/No Responde
¿Qué marcas de jeans usa o prefiere?: Fiorucci; Sibilla; Pioner; Milk; Tayssir; Otras; Ninguna
¿Al momento de comprar una prenda de jean, qué es lo primero que toma en cuenta?: La marca; La calidad de la tela; El modelo; Los acabados; El precio; Lo bien que le pueda quedar; Otros
¿Qué modelos de jeans prefiere?: Clásicos; Pitos; Acampanados; Capri; El que esté de moda; Otros
¿Cuáles de estos adornos prefiere en la confección de un jean?: Doble bolsillo; Dobleces en piernas; Bolsillos en piernas; Bolsillos delanteros con cierre; Bolsillos traseros con cierre; Sin muchos adornos; Otros
¿Cuáles de estos tipos de aplicaciones prefiere en un jean?: Bordados en bolsillos delanteros; Bordados en bolsillos traseros; Greviches (piedras de colores) en bolsillos delanteros; Greviches en bolsillos traseros; Tachas en bolsillos traseros; Tachas en bolsillos delanteros; Sin muchas aplicaciones; Otros
¿A cuál de estos lugares suele acudir para comprar sus jeans?: Centros comerciales; Tiendas por departamento; Bazares o galerías; Supermercados; Mercados mayoristas; Otros
¿Con qué frecuencia compra usted la prenda del jean?: Semanal; Quincenal; Mensual; Cada 3 meses; Cada 6 meses; Mayor a un año; Otros
¿Cuánto suele gastar en promedio al comprar un jean?: Menos de C$200; Entre C$200 y C$400; Entre C$400 y C$600; Entre C$600 y C$1000; Más de C$1000
¿Estaría dispuesta a probar una nueva marca de jeans?: Si; No; No Sabe/ No Responde. iii. Con la ayuda de las Tablas Dinámicas, realice un resumen de la información
que le permita categorizar al encuestado, contabilizando la cantidad de personas encuestadas por SEXO y EDAD.
B. GMM se ha convertido en la empresa recuperadora de carteras vencidas más eficiente del país, esto debido al equipo de cobradores telefónicos que laboran en la empresa. GMM ha diseñado un proceso de cobro telefónico en el cual realiza una distribución equitativa de los clientes deudores a sus empleados en una hoja de Excel, en la que deben de agregar la
información recopilada del cliente. Usted será el encargado de diseñar esas hojas de información asegurándose que el cobrador cometa la menor cantidad de errores posibles.
i. Con la Hoja de cálculo proporcionada, la cual contiene una parte de los deudores a cobrar en la semana, aplique validación de datos para contribuir a que el cobrador reduzca la cantidad de errores al escribir la información de interés.
Gestor de Cobro: Alan Aragón; Bertha Baca; Camilo Casco; Donald Díaz. Fecha de Asignación: Posterior a 01/02/2011.
Teléfono de Contacto: Numero de 8 dígitos
Tramite: Pagado, Promesa de Pago, Cancelo Servicio, No Pagará, Otro Abono: Numero decimal igual a superior a 0.
Dirección: Una descripción del caso menor a 100 caracteres.
ii. En una Tabla Dinámica, refleje la cantidad de personas que se les cobro por día, visualizando únicamente aquellos clientes comprometidos a pagar.
iii. A los cobradores telefónicos se les entrega una comisión de 2% de la deuda por cada cliente comprometido a pagar. Visualice este valor de comisión en la Tabla Resumen.
Capitulo 3:
EL FORMATO CONDICIONAL Y LA SEGURIDAD
EN EXCEL 2010
3.1 Formato Condicional
Como su nombre lo indica el formato condicional permite poder establecer el formato (estilo, color, fondo, etc.) basado en una restricción o condición dada. Se puede aplicar formato condicional a un rango de celdas, a una tabla de Microsoft Excel o a un informe de tabla dinámica.
Ventajas del Formato Condicional
Cuando se analizan datos, es frecuente que surjan preguntas como:
¿Dónde están las excepciones en un resumen de beneficios de los últimos cinco años?
¿Cuáles son las tendencias en una encuesta de opinión de marketing durante los dos últimos años?
¿Quién ha vendido más de 50.000 dólares este mes?
¿Cuál es la distribución de antigüedad general de los empleados?
¿De qué productos han aumentado los ingresos más del 10% de año en año?
¿Cuáles son los estudiantes con mejores resultados y cuáles los de peores resultados en la clase de 4º de secundaria?
El formato condicional ayuda a responder estas preguntas porque facilita el proceso de resaltar celdas o rangos de celdas interesantes, de destacar valores inusuales y de ver datos empleando barras de datos, escalas de colores y conjuntos de iconos. Un formato condicional cambia el aspecto de un rango de celdas en función de condiciones (o criterios). Si la condición es verdadera, el rango de celdas basa el formato en dicha condición; si la condición es falsa, el rango de celdas no tiene formato basado en dicha condición.
Agregar Formato Condicional
1. Seleccione las celdas alas que desea agregar el Formato.
2. En el menú Inicio seleccione la opción “Formato Condicional”.
3. En el menú desplegado, seleccione cualquiera de las reglas previamente definidas por Excel o puede seleccionar la opción “Nueva Regla…”, para poder personalizar sus propias restricciones:
A. Aplicar Formato a todas las celdas según sus valores:
Con el uso de escalas de colores, relleno de celdas e iconos, esta regla permite al usuario clasificar sus datos numéricos de acuerdo a valores mínimos y máximos. Estas escalas de colores son guías visuales que ayudan a comprender la variación y la distribución de datos.
Una escala de dos colores permite comparar un rango de celdas utilizando una gradación de dos colores. El tono del color representa los valores superiores o inferiores. Por ejemplo, en una escala de colores verde y rojo, se puede especificar que las celdas de valor superior tengan un color más verde y las celdas de valor inferior tengan un color más rojo.
Una escala de tres colores permite comparar un rango de celdas utilizando una gradación de tres colores. El tono de color representa los valores superiores, medios o inferiores.
Una barra de datos le ayuda a ver el valor de una celda con relación a las demás. La longitud de la barra de datos representa el valor de la celda. Una barra más grande representa un valor más alto y una barra más corta representa un valor más bajo. Las barras de datos son útiles para encontrar números más altos y más bajos especialmente con grandes cantidades de datos, como las mayores y menores ventas de juguetes en un informe de ventas.
Utilice un conjunto de iconos para comentar y clasificar los datos en tres y hasta cinco categorías separadas por un valor de umbral. Cada icono representa un rango de valores. Por ejemplo, en el conjunto de iconos de 3 flechas, la flecha verde hacia arriba representa los valores más altos, la flecha amarilla hacia los costados representa valores medios y la flecha roja hacia abajo representa los valores más bajos.
B. Aplicar Formato únicamente a las celdas que contengan:
Con esta regla usted podrá encontrar más fácilmente celdas específicas dentro de un rango de celdas que contengan valores numéricos, texto, Fechas, Horas, errores, celdas en blanco, etc., basándose en un operador de comparación. Por ejemplo, en una hoja de cálculo de inventario ordenada según categorías, puede resaltar los productos con menos de 10 artículos disponibles en amarillo o bien, en una hoja de cálculo de resumen de almacén al por menor, puede identificar todos los almacenes con beneficios superiores al 10%, volúmenes de ventas menores de USD 100.000, y región igual a "Sudeste".
C. Aplicar Formato a los valores con rango inferior o superior:
Puede buscar los valores más altos y más bajos en un rango de celdas según un valor de corte que especifique. Por ejemplo, puede buscar los 5 productos más vendidos en un informe regional, el 15% de los productos del final de una encuesta al cliente o los 25 mejores salarios de un análisis de personal de departamento.
D. Aplicar Formato a los valores que estén por encima o por debajo del promedio: Puede buscar valores por encima o por debajo del promedio o desviación estándar en un rango de celdas. Por ejemplo, puede buscar los ejecutores medios anteriores en una evaluación del rendimiento anual o puede buscar materiales fabricados que se encuentran por debajo de dos desviaciones estándar de una calificación de calidad.
E. Aplicar Formato a los valores únicos o duplicados:
Esta regla le proporciona una manera fácil de resaltar valores (numéricos, texto, fechas u horas) únicos en un rango de datos seleccionado.
F. Utilice una formula que determine las celdas para aplicar formato:
Si el formato condicional tiene que ser más complejo, puede usar una fórmula lógica (debe contener operadores lógicos que garanticen una respuesta de Verdadero o Falso) para especificar los criterios de formato. Por ejemplo, puede que desee comparar valores con un resultado devuelto por una función o evaluar datos de celdas que se encuentran fuera del rango seleccionado, que pueden estar en otra hoja de cálculo del mismo libro.
3.2 Seguridad
En muchas ocasiones interesa proteger contra escritura una hoja de cálculo o ciertas celdas de ella, de forma que sea imposible borrar o cambiar accidentalmente la información contenida en ella. También puede interesar ocultar las formulas con los que se han calculado los valores mostrados en algunas celdas que se consideran relevantes.
Excel ofrece distintas formas para controlar y restringir el acceso de los usuarios, para ver o modificar los datos en los libros y en las hojas de cálculo. Entre las distintas alternativas que se pueden implementar se hará énfasis en:
Proteger Hojas.
Proteger estructura del libro. Cifrar con Contraseña.
Proteger Hojas
La Protección de Hojas se da para evitar que los usuarios puedan modificar únicamente las celdas de interés y evitar que puedan alterar información de importancia.
Sin embargo al hablar de Protección de una Hoja de calculo es necesario primeramente mencionar que todas las celdas de un libro de Excel están protegidas o bloqueadas, opción que se habilita únicamente cuando se protege toda la hoja.
Como proteger las celdas de una Hoja:
1. Seleccione las celdas que desea sean editables y oprima clic derecho del mouse para desplegar el menú contextual, en donde seleccionara la opción “Formato de Celdas”.
2. En la ventana de formato de celdas ubique la pestaña “Proteger” y deshabilite la casilla de Bloqueada.
3. NOTA: Si usted desea que las formulas que realice en su hoja no sean visibles, habilite la casilla llamada “Oculta”.
Como proteger la Hoja de cálculo:
1. Una vez que ha desbloqueado las celdas editables, en el menú Revisar haga clic en “Proteger Hoja”.
2. En la ventana que aparece, seleccione una contraseña que para evitar que la hoja pueda ser desprotegida (si así lo desea). 3. La lista de opciones muestran lo que usted
permite que los usuarios de su hoja de cálculo puedan hacer.
4. Una vez que ha seleccionado las opciones deseadas finalice con Aceptar.
5. En caso de haber ingresado una contraseña Excel le pedirá confirmar la misma.
6. Si usted desea desproteger la hoja,
seleccione del menú Revisar la opción “Desproteger Hoja”.
NOTA: Si desea proteger la hoja utilizando contraseña, tome en cuenta que si esta es olvidada no habrá manera de recuperar la misma.
NOTA: Cuando usted habilite la opción de “Seleccionar celdas bloqueadas”, permitirá al usuario que este pueda seleccionar celdas bloqueadas, sin embargo no podrá editar las mimas.
Proteger estructura del Libro
La opción de Protección del Libro, esta orientada a proteger la estructura (evitar agregar hojas, eliminar hojas, cambiar nombre de la hoja, etc.) y el tamaño del área de trabajo. Para esto se debe realizar lo siguiente:
1. En el menú Revisar haga clic en “Proteger Libro”. 2. En la ventana seleccione Proteger la Estructura del
libro y/o proteger las ventanas.
3. Agregue una contraseña si así lo desea, evitando que pueda desprotegerse el libro y presiones Aceptar. 4. Si desea desproteger el libro, seleccione del menú
Permisos
Dentro de la pestaña Archivo, Excel también es capaz de administrar los permisos de hojas y libros de cálculo, entre otras opciones:
Cifrar con contraseña: Con el cifrado con contraseña se habilitara la opción para que sea necesario introducir una contraseña o password, si desea abrir el libro.
Marcar como Final: Indica que las hojas de cálculo del libro no serán modificadas más, convirtiendo al mismo como la versión final y protegiéndolo contra escritura.
Restringir permisos por personas: Al trabajar las hojas de cálculo en una red de trabajo internet, se puede compartir la información contenida en las mismas y otorgar permisos de edición por cada hoja a los miembros de la red.
Agregar Firma Digital: Las firmas digitales dan garantía de que el libro de Excel ha sido creado por una persona confiable y cuyo contenido esta garantizado.
3.3 Ejercicios Propuestos
A. La librería y biblioteca “El Búho Lector” recientemente abrió sus puertas a la población, no cuentan con sistemas informáticos sofisticados de administración de libros aun, así que llevan su inventario en Excel 2010.
i. Utilizando las opciones de formato condicional, en el campo de «AÑO», coloque iconos que le ayude a identificar los libros
Menores de 1930 Entre 1930 y 1980
Mayores o iguales a 1980.
ii. El campo Disponibilidad contiene la cantidad de libros que pueden alquilarse (Cantidad Disponible – Alquilados), coloque un formato de color verde al campo Disponibilidad cuando existen libros disponibles par alquilar y color Rojo cuando la Disponibilidad sea 0.
iii. Recordando los conceptos de la Unidad anterior, asegúrese que no puedan alquilarse más libros de los disponibles.
B. Los cobradores telefónicos de GMM están categorizando a los deudores de la siguiente manera por colores:
i. La empresa dueña de la cartera a autorizado un descuento del 12% a todos aquellos clientes con deudas superiores a los C$20’000, por lo cual los cobradores aplicaran un formato de color rojo en el campo deudas a todos lo clientes que cumplan con el criterio.
ii. Luego de mucho tiempo en el mercado, GMM conoce que los clientes con deudas más antiguas son más difíciles de cobrar y han categorizado a los clientes por colores. En el campo nombre del deudor aplique:
Color Rojo si la deuda del cliente es anterior a Junio 2011. Color Verde si la deuda del cliente es posterior a Junio 2011.
Capitulo 4:
FORMULAS Y FUNCIONES
Las fórmulas son ecuaciones que pueden realizar cálculos, devolver información, manipular el contenido de otras celdas, comprobar condiciones, etc. Una fórmula siempre comienza con el signo igual (=). A continuación, se ofrece una muestra de los tipos de fórmulas que se pueden escribir en una hoja de cálculo.
=5+2*3 Suma 5 al resultado de multiplicar 2 por 3. =A1+A2+A3 Suma los valores de las celdas A1, A2 y A3.
=RAIZ (A1) Usa la función RAIZ para devolver la raíz cuadrada del valor contenido en A1.
=HOY () Devuelve la fecha actual.
=MAYUSC ("hola") Convierte el texto "hola" en "HOLA" mediante la función de hoja de cálculo MAYUSC.
=SI (A1>0) Comprueba si la celda A1 contiene un valor mayor que 0.
Una fórmula también puede contener funciones, referencias, operadores y constantes. Los operadores especifican el tipo de cálculo que desea ejecutar en los elementos de una formula. Existe un orden predeterminado en el que tienen lugar los cálculos (que sigue las reglas matemáticas generales), pero puede cambiar este orden utilizando paréntesis.
Tipos de operadores
Existen cuatro tipos de operadores de cálculo: aritmético, comparación, concatenación de texto y referencia.
Operadores aritméticos
Para ejecutar las operaciones matemáticas básicas como suma, resta, multiplicación o división, combinar números y generar resultados numéricos, utilice los siguientes operadores aritméticos.
Operadores de comparación
Se pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores usando estos operadores, el resultado es un valor lógico: VERDADERO o FALSO.
Operador de concatenación de texto
Utilice la y comercial (&) para concatenar (unir) una o varias cadenas de texto con el fin de generar un solo elemento de texto.
Operadores de referencia
Combine rangos de celdas para los cálculos con los siguientes operadores.
Orden en que Excel ejecuta las operaciones en las fórmulas
En algunos casos, el orden en el que se ejecuta el cálculo puede afectar al valor devuelto de la fórmula. Por tanto, es importante comprender cómo se determina el orden y cómo puede cambiar el orden para obtener los resultados deseados.
Las fórmulas calculan los valores en un orden específico. Las fórmulas de Excel siempre comienzan por un signo igual (=). Excel interpreta los caracteres detrás del signo igual como una fórmula. Tras el signo igual están los elementos que se van a calcular (los operandos); por ejemplo, constantes o referencias a celdas. Éstos se encuentran separados por operadores de cálculo. Excel calcula la fórmula de izquierda a derecha, según el orden específico de cada operador de la fórmula.
Si se combinan varios operadores en una única fórmula, Excel ejecutará las operaciones en el orden que se indica en la siguiente tabla. Si una fórmula contiene operadores con la misma
prioridad (por ejemplo, si una fórmula contiene un operador de multiplicación y otro de división), Excel evaluará los operadores de izquierda a derecha.
Para cambiar el orden de evaluación, escriba entre paréntesis la parte de la fórmula que se calculará en primer lugar. Por ejemplo, la siguiente fórmula da un resultado de 11 porque Excel efectúa la multiplicación antes que la suma. La fórmula multiplica 2 por 3 y, a continuación, suma 5 al resultado.
=5+2*3
Por el contrario, si se utilizan paréntesis para cambiar la sintaxis, Excel sumará 5 y 2 y, a continuación, multiplicará el resultado por 3, con lo que se obtiene 21.
= (5+2)*3
En el siguiente ejemplo, los paréntesis que rodean la primera parte de la fórmula indican a Excel que calcule B4+25 primero y después divida el resultado por la suma de los valores de las celdas D5, E5 y F5.
= (B4+25)/SUMA (D5:F5)
Utilizar funciones y funciones anidadas en fórmulas
Las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un determinado orden o estructura. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas.
Cuando cree una fórmula que contenga una función, puede usar el cuadro de diálogo Insertar función como ayuda para especificar las funciones de la hoja de cálculo. A medida que se escriba una función en la fórmula, el cuadro de diálogo Insertar función irá
función y de cada argumento, el resultado actual de la función y el resultado actual de toda la fórmula.
Para facilitar la creación y edición de fórmulas, y minimizar los errores de escritura y sintaxis, utilice la característica Fórmula Autocompletar. Después de escribir un = (signo igual) y las letras iniciales o un desencadenador de visualización, Excel muestra debajo de la celda una lista desplegable dinámica de funciones, argumentos y nombres válidos que coinciden con las letras o con el desencadenador de visualización. A continuación, puede insertar un elemento de la lista desplegable en la fórmula.
En algunos casos, puede que deba utilizar una función como uno de los argumentos (argumento: valores que utiliza una función para llevar a cabo operaciones o cálculos. El tipo de argumento que utiliza una función es específico de esa función. Los argumentos más comunes que se utilizan en las funciones son números, texto, referencias de celda y nombres.) de otra función. Por ejemplo, la siguiente fórmula utiliza una función anidada PROMEDIO y compara el resultado con el valor 50.
4.1 Referencias en fórmulas
Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Excel dónde debe buscar los valores o los datos que desea utilizar en una fórmula. Las referencias permiten utilizar datos de distintas partes de una hoja de cálculo en una fórmula, o bien utilizar el valor de una celda en varias fórmulas. También puede hacerse referencia a las celdas de otras hojas en el mismo libro y de otros libros. Las referencias a celdas de otros libros se denominan vínculos o referencias externas (referencia externa: referencia a una celda o a un rango en una hoja de otro libro de Excel o una referencia a un nombre definido en otro libro.)
Diferencia entre referencias absolutas, relativas y mixtas
Referencias relativas Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, cambia la referencia. Si se copia o se rellena la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia o rellena una referencia relativa de la celda B2 en la celda B3, se ajusta automáticamente de =A1 a =A2.
Referencias absolutas Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada,
