Curso Avanzado de Ms Excel PDF

Preview

Summary

MS Excell...

Description

Curso Avanzado de Ms Excel

Managua, Noviembre 2010

Curso Avanzado de Ms Excel Tabla de contenido Introducción ...................................................................................................................................................... 3 Excel 2010 vs Excel 2007 ................................................................................................................................... 8 Elementos de Excel .......................................................................................................................................... 12 Descripción general de las tablas de Excel ...................................................................................................... 13 Crear una tabla ....................................................................................................................................... 17 Convertir una tabla en un rango de datos ............................................................................................ 17 Eliminar una tabla ................................................................................................................................. 18

Controlar la lista desplegable empleando desencadenadores de visualización................................. 20 Activar o desactivar Fórmula Autocompletar....................................................................................... 24

¿Qué es la validación de datos?............................................................................................................. 24 ¿Cuándo es útil la validación de datos?................................................................................................ 26 Mensajes de validación de datos........................................................................................................... 27 Sugerencias para trabajar con la validación de datos.......................................................................... 28 Agregar validación de datos a una celda o rango........................................................................................ 30 Lista personalizada .......................................................................................................................................... 46 Aplicar formato condicional ............................................................................................................................ 49 Fórmulas y constantes matriciales .................................................................................................................. 51 Utilizar constantes en fórmulas............................................................................................................. 53 Utilizar operadores de cálculo en fórmulas .......................................................................................... 54 Utilizar funciones y funciones anidadas en fórmulas .......................................................................... 57

Evitar errores comunes al escribir fórmulas......................................................................................... 65 Importación de datos a Excel .......................................................................................................................... 80 Importación de datos desde Microsoft SQL Server ......................................................................................... 86 Subtotales ........................................................................................................................................................ 90 Totales generales..................................................................................................................................... 90 Subtotales anidados ................................................................................................................................ 91 Subtotales exteriores............................................................................................................................... 91 Insertar subtotales individuales .............................................................................................................. 91 Introducción a análisis Y SI .............................................................................................................................. 94 Preparar previsiones y modelos empresariales avanzados ...................................................................... 96 Acelerar los cálculos en una hoja de cálculo que contiene tablas de datos ...................................................... 97 Realizar análisis de regresión con el complemento Herramientas para análisis .................................. 99 Crear un informe resumen de escenario .................................................................................................. 100

Ing. oscar perez

2

Curso Avanzado de Ms Excel Ejemplo paso a paso ................................................................................................................................... 100 Preparar la hoja de cálculo ...................................................................................................................... 100 Usar Buscar objetivo para determinar el tipo de interés ........................................................................... 101 ¿Qué es la herramienta Solver de Excel? ...................................................................................................... 104 Qué es la optimización ............................................................................................................................ 104 Definir un modelo de optimización ........................................................................................................ 105 Celda objetivo......................................................................................................................................... 105 Celdas cambiantes ................................................................................................................................... 105 Restricciones........................................................................................................................................... 106 ¿Cómo puedo especificar este modelo en Solver? ................................................................................. 114 ¿Por qué es lineal este problema de Solver? ....................................................................................................... 117 Una vez demostrado que nuestro modelo de mezcla de productos es lineal, ¿por qué preocuparse? ........ 118 ¿Un modelo Solver tiene siempre una solución? ................................................................................... 121 ¿Qué ocurre si los valores definidos no convergen? .............................................................................. 121

Introducción 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

Ing. oscar perez

3

Curso Avanzado de Ms Excel 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. 1 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.

Realice comparaciones rápidas y efectivas 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

Ing. oscar perez

4

Curso Avanzado de Ms Excel

 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

Ahorre tiempo, simplifique el trabajo y aumente la productividad Crear y administrar los libros es mucho más fácil cuando puede trabajar como desea.  Recupere las versiones no guardadas de archivos que cerró sin guardar. Eso es. La característica de recuperación de versiones es solo una de tantas características nuevas disponibles en la nueva vista Microsoft Office Backstage™. La vista Backstage reemplaza al menú Archivo tradicional en todas las aplicaciones de Office 2010 para proporcionar un espacio organizado y centralizado para todas las tareas de administración de libros.

Ing. oscar perez

5

Curso Avanzado de Ms Excel  Personalice fácilmente la cinta de opciones mejorada para que los comandos que necesita estén más

accesibles. Cree pestañas personalizadas o incluso personalice las integradas. Con Excel 2010, el control está en sus manos.

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.

 Los usuarios corporativos de compañías que ejecutan SharePoint Foundation 2010 pueden usar esta funcionalidad dentro de su firewall.  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.

Ing. oscar perez

6

Curso Avanzado de Ms Excel 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.

Ing. oscar perez

7

Curso Avanzado de Ms Excel 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 disponible.

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

Ing. oscar perez

8

Curso Avanzado de Ms Excel 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.

Ing. oscar perez

9

Curso Avanzado de Ms Excel

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 usuarios dentro de una misma empresa con diferentes versiones, lo que originaba 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

Ing. oscar perez

10

Curso Avanzado de Ms Excel 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.

Ing. oscar perez

11

Curso Avanzado de Ms Excel Elementos de Excel La ficha Archivo Haciendo clic en la pestaña Archivo que se encuentra en la parte superior izquierda de la pantalla podrás desplegar un menú desde donde podrás ver las

acciones

que

puedes

realizar

sobre

el

documento,

incluyendo Guardar, Imprimir o crear uno Nuevo. A este menú también puedes acceder desde el modo de acceso por teclado tal y como veremos en la Cinta de opciones. Contiene dos tipos básicos de elementos: - Los que muestran un panel justo a la derecha con más opciones, ocupando

toda

la

superficie

de

la

ventana

Excel.

Como Información o Imprimir. Al situar el cursor sobre las opciones de este tipo observarás que tienen un efecto verde oscuro. El color permanecerá al hacer clic para que sepas qué panel está activo. - Los que abren un cuadro de diálogo. Como Abrir, o Guardar como. Al situar el cursor sobre las opciones de este tipo observarás que tienen un efecto verde claro. El color sólo permanece mientras el cursor está encima, ya que al hacer clic, el propio cuadro de diálogo abierto muestra el nombre de la acción en su barra de título. Para cerrar la ficha Archivo y volver al documento pulsamos ESC o hacemos clic en otra pestaña.

Las barras 

La barra de título

Contiene el nombre del documento sobre el que se está trabajando en ese momento. Cuando creamos un libro nuevo se le asigna el nombre provisional Libro1, hasta que lo guardemos y le demos el nombre que queramos. En el extremo de la derecha están los botones para minimizar maximizar 

y cerrar

,

.

La barra de acceso rápido

Ing. oscar perez

12

Curso Avanzado de Ms Excel La barra de acceso rápido contiene las operaciones más habituales de Excel como Guardar , Deshacer

o Rehacer

.

Esta barra puede personalizarse para añadir todos los botones que quieras. Para ello haz clic en una opción y aparecerá marcada. De igual forma, si vuelves a hacer clic sobre ella se eliminará de la barra. Si no encuentras la opción en la lista que te propone, puedes seleccionar Más comandos....

Descripción general de las tablas de Excel Para facilitar la administración y el análisis de un grupo de datos relacionados, puede convertir un rango (rango: dos o más celdas de una hoja. Las celdas de un rango pueden ser adyacentes o no adyacentes.) de celdas en una tabla de Excel de Microsoft Office (anteriormente conocida como lista de Excel). Una tabla generalmente contiene datos relacionados en una serie de filas y columnas de hoja de cálculo (hoja de cálculo: documento principal que se utiliza en Excel para almacenar y trabajar con datos. Consta de celdas que se organizan en filas y columnas. Una hoja de cálculo se almacena siempre en un libro.) que tienen formato de tabla. Al usar las características de tabla, puede administrar los datos en las filas y columnas de la tabla de manera independiente de los datos de las otras filas y columnas de la hoja de cálculo.

Ing. oscar perez

13

Curso Avanzado de Ms Exc...