Material excel 2010 intermedio

Aprendo Excel 2010 Intermedio - Cibertec

¡Bienvenido al mundo de Excel 2010!

La hoja de cálculo, Excel 2010, ofrece una serie de herramientas para dar solución a proble-mas específi cos en el manejo de datos. Actualmente, los usuarios requieren conocer cada vez más todas las bondades de esta hoja de cálculo; por ello este libro lo ayudará paso a paso a descubrir el funcionamiento de cada una de las herramientas avanzadas del programa.

A lo largo de este libro, usted podrá encontrar herramientas que le permitirán fi ltrar sus bases de datos de manera avanzada considerando múltiples criterios; además podrá crear tablas y gráfi cos dinámicos que lo ayudarán a analizar su información.

En un capítulo de este libro usted descubrirá cómo importar datos de otras fuentes, ya sea de manera directa o través de consultas.

Uno de los benefi cios que ofrece Excel 2010 es la herramienta de funciones, el programa ya tiene confi gurado diversas categorías y el usuario sólo debe indicar los parámetros que debe considerar. A nivel básico se habló de las funciones más utilizadas en las labores diarias; sin embargo, en esta parte del curso se desarrollarán algunos categorías más, cada una con sus tipos correspondientes y orientadas a resolver problemas mucho más específi cos.

Muchas empresas manejan datos de diversas sucursales, tiendas, áreas, etc. datos que mu-chas veces deben ser consolidados en una sola hoja, un usuario no debe perder mucho tiempo en la solución de este problema; por ello Excel 2010 ofrece la herramienta de Escenarios que lo ayudará a cumplir con su objetivo de una manera sencilla.

Aprendo Excel 2010 Intermedio - Cibertec

Finalmente, el libro lo ayudará a crear formularios con controles que le permitirán realizar consultas y registros más organizados de sus bases de datos; además aprenderá cómo ejecutar procedimientos básicos con la ayuda de Macros.

Sin más preámbulos, lo invitamos a descubrir paso a paso cómo manejar las herramientas avanzadas de Excel 2010.

“Cibertec Educación para un mundo que cambia” 7

Tabla de contenido

Capítulo 1

Herramientas para base de datos

Introducción .......................................................................................................... 10............................................................................... 10

.................................................................................................... 13................................................................................ 21

..................................................................................................... 26.................................................................................... 32

.................................................................................................. 58................................................................................... 63

........................................................................................... 66.......................................................................................................... 67

........................................................................................... 72

........................................................................................... 76

Capítulo 2

Biblioteca de funciones

Introducción .......................................................................................................... 80................................................................ 81

.......................................................................... 86................................................................................. 99

..................................................... 110...................................................................................... 127

...................................................................... 131.................................................................... 141

............................................................................ 149........................................................................................ 149

Capítulo 3

Herramientas de auditoria y de análisis

Introducción ........................................................................................................ 160....................................................................... 160

.......................................................................................... 167.................................................................................. 173

“Cibertec Educación para un mundo que cambia”8

............................................................................................... 176........................................................................................ 183

Capítulo 4

Creación de formularios en la hoja

Introducción ........................................................................................................ 186.......................................................... 186

............................... 191........................................................................................ 215

Capítulo 5

Herramientas de seguridad y colaboración

Introducción ........................................................................................................ 218........................................................... 218

...................................................................... 225................................................................................................ 233

........................................................................................ 240

Capítulo 6

Grabación de macros

Introducción ........................................................................................................ 242......................................................... 242

................................................................................................ 245.............................................................................................. 247

................................................................................................ 248............................................................................................... 252

..................................................................................... 253

Apre

ndo E

xcel 2010 In

term

edio


Herramientas para

base de datos

Al fi nalizar el capítulo, el alumno:

Reconoce las operaciones para organizar la información de una base de datos para su gestión y administración.

Realiza las operaciones para fi ltrar, agrupar, consolidar, reorganizar, resumir y representar gráfi camente la información de una base de datos.

Temas:

Introducción.

Defi nición de una base de datos.

Filtros avanzados.

Esquemas.

Subtotales.

Consolidar datos.

Tablas dinámicas.

Gráfi cos dinámicos.

Convertir texto a columnas.

Quitar datos duplicados.

Validación de datos.

Obtener datos externos.

Capítulo 1Capítulo 1

“Cibertec Educación para un mundo que cambia”

Apre

ndo E

xcel

2010 I

nte

rmedio

10

INTRODUCCIÓN

En este capítulo se desarrollarán las principales operaciones con listas o base de datos, las cuales le permitirán consultar la información mediante la aplicación de fi ltros avanzados, agrupar datos creando esquemas, consolidar datos de varias hojas; así como, resumir fi ltrar y analizar la información en forma interactiva mediante el diseño de tablas, gráfi cos dinámicos y la segmentación de datos.

DEFINICIÓN DE UNA BASE DE DATOS

Una Lista o Base de datos es una colección de datos relacionados entre sí y organizados por categorías.Ejemplos comunes de base de datos lo constituyen un listado de facturas, una relación de empleados, un listado de clientes; o un listado de empleados, con sus nombres y apellidos, año de ingreso a la empresa, sueldo básico, etcétera.

En Excel una base de datos está compuesta por:

Las columnas de la lista, denominadas también categorías o campos de la base de datos, contienen un mismo tipo de información. Los títulos de columna son los nombres de campo.

Las fi las de la lista, denominadas también registros, son conjuntos de campos que pertenecen a una misma entidad.

Abra el archivo Base de datos ejemplo1.xlsx, en el cual comprobará que la lista está compuesta por 12 campos y 176 registros.

Para optimizar el manejo de una base de datos, será necesario conocer lo siguiente:

Consideraciones para la creación de una base de datos.•Examinar una base de datos empleando paneles.•

Apre

ndo E

xcel 2010 In

term

edio


Consideraciones para la creación de una base de datos

Asegúrese que entre la fi la que contiene los nombres de los campos y el primer registro no 1.exista una fi la en blanco.Aplique el mismo formato a los datos de una misma columna (excepto al nombre del 2.campo).Es preferible emplear una sola lista por hoja de cálculo.3.Asegúrese que entre registro y registro, o entre campo y campo no existan fi las o columnas 4.en blanco.

Examinar una base de datos empleando paneles

Suponga que tiene una base de datos tan grande que no cabe en la pantalla, tal como se observó en la fi gura anterior; pero tiene que revisarla. Seguramente tendrá problemas porque estará pensando qué hacer para llenar datos en un cuadro que no lo puede visualizar completamente. Por ejemplo, si se necesita revisar un dato de comisión o cantidad para el último registro; entonces le será difícil ubicarse adecuadamente sin tener la plena certeza que está justo en la celda que debería. Por ello, le será útil aprender las técnicas descritas a continuación.

Inmovilizar paneles para bloquear fi las o columnas específi cas

Consiste en examinar la lista manteniendo las primeras fi las o columnas siempre visibles. Para ello deberá realizar los pasos siguientes:

En la hoja de cálculo, realice uno de los siguientes procedimientos: 1.Para bloquear fi las, seleccione la fi la situada debajo del punto en el que desea que aparezca la división. Para el ejemplo, se necesita mantener congeladas de la fi la 1 a la 3 (que es donde fi guran los nombres de los campos). Por lo tanto, tendrá que seleccionar la celda A4.Para bloquear columnas, seleccione la columna situada a la derecha del punto en el que desea que aparezca la división. Para bloquear fi las y columnas, haga clic en la celda situada por debajo y a la derecha del punto en el que desea que aparezca la división.

En la fi cha 2. Vista, en el grupo Ventana, haga clic en Inmovilizar paneles y, a continuación, haga clic en la opción que desea. Para este caso, seleccione la primera opción.

Observe que aparecen unas 3.líneas negras dividiendo las fi las que quiso congelar, luego trate de ubicarse en la celda L52 o J153 y aprecie la diferencia. Observará que los títulos o fi las que inmovilizó siempre aparecen, por lo tanto es más fácil trabajar.

fil i l l t t d bi l ld

Not

a Al inmovilizar paneles, la opción Inmovilizar paneles cambia a Movilizar panelespara que pueda desbloquear fi las o columnas inmovilizadas.


Apre

ndo E

xcel

2010 I

nte

rmedio

12

Dividir la ventana en paneles para bloquear fi las o columnas en áreas de hoja de cálculo independientes

Suponga que ahora usted necesita revisar al mismo tiempo las cantidades del registro 4 y compararlas con las del registro 70, si lo intenta hacer seguramente tendrá problemas ya que por más que haya inmovilizado paneles en su hoja, no puede ver ambas celdas en forma simultánea. Este problema se puede evitar dividiendo la ventana en paneles de la manera siguiente:

Seleccione la celda a partir de la 1.cual desea hacer la división. Para el ejemplo, se dividirá la ventana en dos paneles horizontales, entonces seleccionará la celda A7.

En la fi cha 2. Vista, en el grupo Ventana, haga clic en Dividir.

Observe en la fi gura siguiente que cada panel tiene su propia barra de desplazamiento para 3.que pueda ir de un panel a otro y así revisar diferentes rangos de celdas en cada uno de ellos.

Not

as

Para restablecer una ventana dividida a una sola sección, haga doble clic en cualquier parte de la barra de división.

Divisiones

Puede dividirse la misma ventana incluso en cuatro secciones, dependiendo de donde se ubique el cursor al realizar el procedimiento descrito. Para este caso, se debe colocar el puntero de celda justo al centro de la ventana.

También, puede mover estos paneles para ampliarlos o reducirlos. Para ello, bastará con arrastrar con el mouse la barra de división.

Suge

renc

ia

Otro método válido para dividir una ventana en paneles:

Sitúe el puntero sobre el cuadro de división que aparece en la parte superior 1.

de la barra de desplazamiento vertical o en el extremo derecho de la barra horizontal.

Cuando el puntero adopte la forma de un puntero de división 2. o , arrastre el cuadro de división hacia abajo o hacia la izquierda, hasta la posición deseada. Para quitar la división, haga doble clic en cualquier punto de la barra de división 3.

que divide los paneles.

2

Apre

ndo E

xcel 2010 In

term

edio


FILTROS AVANZADOS

Los fi ltros avanzados consisten en la generación de consultas complejas o más elaboradas y se emplean cuando los fi ltros automáticos no pueden establecer todos los criterios o condiciones de la consulta. Esto se presenta en los siguientes casos:

Cuando la consulta a realizar incluye más de dos criterios referidos al mismo campo, y•Cuando se requiere copiar el resultado de la aplicación del fi ltro, hacia otra área de la hoja •de cálculo.

En esta sección se tratarán las siguientes operaciones con fi ltros avanzados:

¿Qué tipo de criterios se utilizan en fi ltros avanzados?•Procedimiento general para aplicar fi ltros avanzados.•Ejemplos de criterios complejos.•Ejemplos de aplicación de fi ltros avanzados.•

¿Qué tipo de criterios se utilizan en fi ltros avanzados?

Los criterios son condiciones que se especifi can para limitar los registros que se incluyen en el conjunto de resultados de una consulta. Por ejemplo, el siguiente criterio selecciona registros para los que el valor del campo Cantidad de pedidos es mayor que 300: Cantidad de pedidos > 300. El comando Avanzados del grupo Ordenar y fi ltrar de la fi cha Datos funciona de forma diferente desde el comando Filtro en varios aspectos importantes.

Muestra el cuadro de diálogo Filtro avanzado en vez del menú de autofi ltro. Los criterios avanzados se escriben en un rango de criterios independiente en la hoja de cálculo y sobre el rango de celdas o la tabla que desee fi ltrar. Microsoft Offi ce Excel utiliza el rango de criterios independiente del cuadro de diálogo Filtro avanzado como el origen de los criterios avanzados.

Procedimiento general para aplicar fi ltros avanzados

A continuación, se describe el procedimiento general para la aplicación de fi ltros avanzados:

Defi nir el denominado 1. Rango de criterios para cada consulta específi ca. Este rango consiste en escribir los criterios para la selección de los datos, copiando los nombres de cada campo, y escribiendo debajo el valor o condición que deberá cumplir el dato de dicho campo para que sea seleccionado.

Suge

renc

ia Para defi nir un rango de criterios:

Inserte al menos tres fi las vacías sobre el rango que puede utilizarse como rango de criterios. El rango de criterios debe tener rótulos de columna. Compruebe que existe al menos una fi la vacía entre los valores de criterios y el rango de la lista.

Haga clic en una celda del rango. 2.

En la fi cha 3. Datos, en el grupo Ordenar y fi ltrar, haga clic en Avanzadas.


Apre

ndo E

xcel

2010 I

nte

rmedio

14

Elegir la 4. acción a tomar, es decir, la ubicación para visualizar el resultado de la consulta. En este caso existen dos posibilidades:

Filtrar la lista sin moverla,• para visualizar el resultado en la misma lista o base de datos ocultando sólo las fi las que no cumplen los criterios.

Copiar a otro lugar,• para visualizar el resultado copiando las fi las que cumplen los crite-rios a otra área de la hoja de cálculo.

Verifi car o seleccionar el 5. Rango de la lista.Seleccionar el 6. Rango de criterios previamente defi nido. Para ocultar temporalmente el cuadro de diálogo Filtro avanzado mientras selecciona el rango de criterios, haga clic en

Contraer diálogo .Solo en el caso de que la acción elegida sea 7. Copiar a otro lugar, haga clic en la casilla Copiar a y seleccione el rango donde desea copiar los datos.

Ejemplos de criterios complejos

Las siguientes secciones proporcionan ejemplos de criterios complejos:

Varios criterios en una columnaa.

Lógica booleana: (Vendedor = “Davolio” O Vendedor = “Buchanan”)

Para buscar las fi las que cumplen varios criterios de una columna, escriba los criterios directamente debajo de los otros en distintas fi las del rango de criterios. En el siguiente rango de datos (A6:C10), se muestra en negrita las fi las que contienen “Davolio” o “Buchanan” en la columna “Vendedor” (A8:C10), que son registros que cumplen el rango de criterios (B1:B3).

Varios criterios en varias columnas en las que deben cumplirse todos los criteriosb.

Lógica booleana: (Tipo = “Alimentos” Y Ventas > 1000)

Para buscar las fi las que cumplen varios criterios en varias columnas, escriba todos los criterios en la misma fi la del rango de criterios. En el siguiente intervalo de datos (A6:C10), se muestra en negrita todos los productos que contienen “Alimentos” en la columna Tipo y un valor superior a $ 1.000 en la columna Ventas (A9:C10), que son registros que cumplen el rango de criterios (A1:C2).

Apre

ndo E

xcel 2010 In

term

edio


Varios criterios en varias columnas en las que debe cumplirse alguno de los c.criterios

Lógica booleana: (Tipo = “Alimentos” O Vendedor = “Davolio”)

Para buscar las fi las que cumplen varios criterios en varias columnas en las que puede cumplirse cualquier criterio, escriba los criterios en fi las diferentes del rango de criterios.

En el siguiente rango de datos (A6:C10), se muestra en negrita todas las fi las que contienen “Alimentos” en la columna “Tipo” o “Davolio” en la columna “Vendedor” (A8:C10), que son registros que cumplen el rango de criterios (A1:B8)

Varios criterios referidos a la misma columnad.

Lógica booleana: (Ventas > 5000 Y Ventas < 7000)

Para buscar las fi las que cumplen varios conjuntos de criterios, en los que cada conjunto incluye criterios para una columna, se deben incluir varias columnas con el mismo encabezado de columna. En el siguiente intervalo de datos (A6:C10), se muestra en negrita las fi las que contienen valores comprendidos entre 5.000 y 7.000, en la columna Ventas (A8:C10), que son registros que cumplen el rango de criterios(C2:D2).

Criterios para buscar valores de texto que incluyen algunos caracteres pero no e.otros

Para buscar valores de texto que incluyen algunos caracteres pero no otros, siga uno o varios de estos procedimientos:

Escriba uno o más caracteres sin un signo igual (=) para buscar las fi las que tengan un valor de texto en una columna que comienza con esos caracteres. Por ejemplo, si escribe el texto Dav como criterio, Excel encontrará “Davolio”, “David” y “Davis”. Utilice un caracter comodín.

¿Cómo utilizar los caracteres comodines?

Los siguientes caracteres comodines se pueden utilizar como criterios de comparación.


Apre

ndo E

xcel

2010 I

nte

rmedio

16

Utilice Para buscar

? (signo de interrogación) Un único caracter.Por ejemplo, Gr?cia buscará “Gracia” y “Grecia”

* (asterisco) Cualquier número de caracteres.Por ejemplo, *este buscará “Nordeste” y “Sudeste”

~ (tilde) seguida de ?, *, o ~ Un signo de interrogación, un asterisco o una tilde.Por ejemplo, cuál~? buscará “cuál?”.

En el siguiente rango de datos (A6:C10), el rango de criterios (A1:B3) muestra las fi las en negrita de aquellos registros cuyos primeros caracteres son “Ca” en la columna Tipo o las fi las cuyo segundo carácter sea “u” en la columna Vendedor (A7:C9).

Criterios creados como resultado de una fórmulaf.

Puede utilizar como criterio valores calculados que sean el resultado de una fórmula. Tenga en cuenta los puntos importantes siguientes:

Puesto que está utilizando una fórmula, escríbala como lo haría normalmente, comenzando •con el signo igual (=), seguido de una secuencia de valores, referencias de celda, nombres, funciones u operadores.La fórmula se debe evaluar como Verdadero o Falso. •No utilice rótulos de columnas para los rótulos de los criterios; deje estos en blanco o utilice •uno que no sea un rótulo de columna incluido en el rango. Si en la fórmula utiliza un rótulo de columna, en lugar de una referencia relativa a celda o •un nombre de rango, Excel presenta un valor de error, como #¿NOMBRE? o #¡VALOR! en la celda que contiene el criterio. Puede pasar por alto este error, ya que no afecta a la manera en que se fi ltra el rango.La fórmula que utilice con el fi n de generar los criterios debe utilizar una referencia relati-•va para hacer referencia a la celda correspondiente de la primera fi la (en los ejemplos si-guientes, C7 y A7). Todas las demás referencias usadas en la fórmula deben ser referencias absolutas.•

En el siguiente rango de datos (A6:C10), el rango de criterios (D1:D2) muestra en negrita las fi las que tienen en la columna Ventas un valor superior al promedio de todos los valores incluidos en la columna Ventas (C7:C10). En la fórmula, “C7” hace referencia a la columna fi ltrada (C) de la primera fi la del rango de datos (7).

Ejemplos de aplicación de fi ltros avanzados

Apre

ndo E

xcel 2010 In

term

edio


Los ejemplos desarrollados a continuación se realizarán con los datos del archivo fi ltros AVANZADOS Ejemplos.xlsx, el cual posee varias hojas con similar información.

Ejemplo 1Filtre la lista sin mover para seleccionar los registros del Vendedor Piero con cantidades mayores a 40, o los del Vendedor Fabián con cantidades menores a 20.

Para ello deberá realizar los pasos que se indican a continuación en la hoja Ejemplo1.

1. Defi na el Rango de criterios para la consulta. Para ello, se deberá copiar de preferencia arriba del rango de la lista los nombres de cada campo (vendedor y cantidad), y luego escribir debajo el valor o condición que deberán cumplir los datos a fi ltrar.

Para este ejemplo, el rango de criterios a partir de la celda E3 quedará asi:

2. Haga clic en una celda del rango de la lista.

3. En la fi cha Datos, en el grupo Ordenar y fi ltrar, haga clic en Avanzadas.

4. Elija la acción a ejecutar. En este caso, deberá mantener seleccionada la opción Filtrar la lista sin moverla a otro lugar.

5.Verifi que el Rango de la lista, en este caso A8:L184.(recordar que el símbolo $ en la referencia está indicando que se trata de un rango con dirección absoluta).

6. Seleccione el Rango de criterios previamente defi nido, es decir, el rango E3:F5.

7. Haga clic en el botón Aceptar.


Apre

ndo E

xcel

2010 I

nte

rmedio

18

El resultado obtenido mostrará 12 registros fi ltrados en la misma lista, habiéndose ocultado las fi las de los datos que no coincidan con los criterios especifi cados.

Ejemplo 2:Filtre la lista copiando a partir de la celda A188 el resultado de seleccionar los registros de la sucursal Lima con Ventas entre 500 y 1000, o los de la Sucursal de Surco con Ventas entre 400 y 800.

Para ello, deberá realizar los pasos que se indican a continuación en la hoja Ejemplo2.

Defi na el 1. Rango de criterios para la consulta. Para este ejemplo, el rango de criterios a partir de la celda D3 quedará de la siguiente manera:

Haga clic en una celda del rango de la lista.2.

En la fi cha 3. Datos, en el grupo Ordenar y fi ltrar, haga clic en Avanzadas.

Elija la acción a ejecutar. En este caso, deberá 4.seleccionar la opción Copiar a otro lugar.

Verifi que el 5. Rango de la lista en este caso A8:L184(recuerde que el símbolo $ en la referencia indica que se trata de un rango con dirección absoluta).

Seleccione el 6. Rango de criterios previamente defi nido, es decir, el rango D3:F5.

Ubicar el cursor dentro del cuadro 7. Copiar a, y luego hacer clic para seleccionar la celda A188(celda inicial del Rango de salida para copiar los resultados).

Apre

ndo E

xcel 2010 In

term

edio


Haga clic en el botón 8. Aceptar.

El resultado obtenido mostrará 28 registros, esta vez como una copia de los datos fi ltrados.

Ejemplo 3:Filtre la lista copiando a partir de la celda D188 el resultado de seleccionar los registros con Ventas a partir de 100, cuyo segundo caracter del Codigo sea 1 y que la Unidad de medida sea Caja. Adicionalmente para la copia debe considerar únicamente las columnas Fecha, Vendedor, Código, Artículo, Unidad y Ventas:

Para ello, deberá realizar los pasos que se indican a continuación en la hoja Ejemplo3.

El paso previo para este caso consiste 1.en que copie a partir de la celda D188 únicamente los títulos de las columnas que desea obtener como resultados.

Defi na el 2. Rango de criterios para la consulta. Para este ejemplo el rango de criterios es a partir de la celda E3, el cual quedará así:

Realice los pasos de forma similar al ejemplo anterior, considerando en el cuadro de diálogo 3.Filtro avanzado los siguientes rangos:

El resultado obtenido mostrará diez registros, tal como se aprecia en la siguiente fi gura.

á


Apre

ndo E

xcel

2010 I

nte

rmedio

20

Ejemplo 4: Filtre la lista de la Hoja Deudas, copiando a partir de la celda A9 de la hoja Ejemplo4 el resultado de seleccionar únicamente al Cliente que coincida exactamente con “NEXTEL DEL PERÚ S. A.”, y que además no fi guren datos del Distrito Lince.

Para la solución de este ejemplo, debe tener en cuenta lo siguiente:

Se emplearán los datos de la hoja Deudas que posee una lista con el rango A3:H753, al cual se le ha asignado el nombre Reporte.Esta vez el resultado del fi ltro avanzado se tendrá que copiar a otra hoja. Por ello, el procedimiento en este caso tendrá que empezar en la hoja Ejemplo4 (donde se desea obtener la copia).El rango de criterios a emplear es el siguiente:

El resultado es 17 registros fi ltrados y copiados.

Ejemplo 5:Filtre la lista de la hoja Deudas, copiando a partir de la celda A8 de la hoja Ejemplo5 el resultado de seleccionar únicamente los registros que no posean dato en la columna Teléfono (celdasvacías).

Para la solución de este ejemplo, debe tener en cuenta lo siguiente:Se emplearán los datos de la hoja Deudas que posee una lista con el rango A3:H753, al cual se le ha asignado el nombre Reporte.Esta vez el resultado del fi ltro avanzado se tendrá que copiar a otra hoja. Por ello, el procedimiento en este caso tendrá que empezar en la hoja Ejemplo5 (donde se desea obtener la copia).El rango de criterios a emplear es el siguiente:

Apre

ndo E

xcel 2010 In

term

edio


El resultado es 140 registros fi ltrados y copiados, parte de los cuales aparecen en la siguiente fi gura:

Volver a mostrar los datos ocultos

En el caso de haber aplicado fi ltros avanzados con la acción de fi ltrar la lista sin moverla, se puede restablecer la lista completa de datos realizando los siguientes pasos:

En la fi cha 1. Inicio, en el grupo Modifi car, haga clic en Borrar.

ESQUEMAS EN UNA LISTA DE DATOS

Si tiene una lista de datos que desea agrupar y resumir, puede crear un esquema de hasta ocho niveles, uno para cada grupo. Los niveles se representan por una serie de botones, los cuales organizan la información por grupos, permitiendo además mostrar u ocultar los datos distribuidos en columnas o fi las, según el nivel elegido.

Esta herramienta es particularmente útil cuando se tiene una hoja con datos en los que existen sumas parciales, es decir, subtotales y totales con fórmulas de sumatoria.Utilice un esquema para mostrar fi las o columnas de resumen o para revelar los datos de detalle para cada grupo. Puede crear un esquema de fi las (como se muestra en el ejemplo siguiente), un esquema de columnas o un esquema de fi las y columnas.

En esta sección se tratarán las siguientes operaciones con esquemas:

Crear un autoesquema.•


Apre

ndo E

xcel

2010 I

nte

rmedio

22

Crear un esquema manual.•Borrar un esquema.•

Para realizar este ejercicio, debe abrir el archivo ESQUEMAS ejemplos.xlsx

Crear un autoesquema

En este caso Excel agrupa, automáticamente en una hoja de cálculo, todas las fi las o columnas que resuman datos de detalle. Es decir, se agrupan todas las columnas y fi las que posean totales y subtotales.

En este ejemplo se empleará la hoja Ingresos para crear un esquema automáticamente, realizando los siguientes pasos:

Haga clic en una celda dentro del rango que contienen los datos con los que se creará el 1.esquema.

En la fi cha 2. Datos, en el grupo Esquema, haga clic en la fl echa al lado de Agrupar y, a continuación, haga clic en Autoesquema.

Como resultado, observará que Excel crea automáticamente un esquema de 3. tres niveles(esto dependerá de la cantidad de sumatorias parciales que posea la hoja), agrupando las columnas y fi las que contienen sumas parciales.

Si se desea comprobar el uso de los botones de nivel del esquema (los cuales aparecen 4.siempre en los márgenes superior e izquierdo de la hoja), puede optar por cualquiera de las alternativas siguientes:

Apre

ndo E

xcel 2010 In

term

edio


Al hacer clic en los botones del nivel 1, se mostrarán solo las cifras de los totales generales.

Al hacer clic en los botones del nivel 2, se mostrarán adicionalmente las cifras de los subtotales.

Y al hacer clic en los botones del nivel 3, se mostrarán todos los detalles como aparecía en la fi gura inicial.

Adicionalmente se puede hacer clic en el botón 5. (ocultar) para contraer un nivel del esquema, o en el botón (mostrar) para volver a expandir un nivel del esquema.

Crear un esquema manual

En este caso el usuario elige en una hoja de cálculo, qué fi las o columnas que resuman datos de detalle se agruparán.Este tipo de esquema se emplea para crear niveles, agrupando sólo algunas de las columnas o fi las que contienen las sumatorias parciales; es decir, debe seleccionar las columnas o fi las que forman parte de un grupo (el rango de columnas o fi las que dan origen a una sumatoria).

Para crear un nivel de esquema horizontal, entonces se debe seleccionar un grupo de columnas completas, como desde la columna C hasta la E.


Apre

ndo E

xcel

2010 I

nte

rmedio

24

Para crear un nivel de esquema vertical, entonces se debe seleccionar un grupo de fi las completas, como desde la fi la 6 hasta la 18.

Suge

renc

ia

Para formar los grupos de un esquema debe tener en cuenta los siguientes criterios:

Debe agrupar primeramente los grupos externos, y luego, de manera opcional, 1.

los grupos internos.Al formar un grupo, no se debe seleccionar la columna o fi la que posee el 2.

resumen o los totales (sumas).Cuando se agrupa manualmente un esquema, es preferible tener todos los 3.

datos mostrados para evitar formar los grupos incorrectamente.

En este ejemplo se empleará la hoja Gastos para crear un esquema manual, realizando los pasos siguientes:

Aplique un esquema al grupo externo.1.

Seleccione todas las columnas de resumen subordinadas, así como sus datos de detalle a.relacionados.En el ejemplo siguiente, la columna F contiene los subtotales de la columna C a la E, y la columna J contiene los subtotales de la columna G a la I, y la columna K contiene los totales generales. Para agrupar todos los datos de detalle para la columna K, seleccione de la columna C a la J. (Recuerde que no debe incluir la columna resumida K en la selección).

En la fi cha b. Datos, en el grupo Esquema, haga clic en Agrupar. Los símbolos del esquema aparecen en la pantalla al lado del grupo.

Aplicar un esquema a un grupo interno anidado.3.

Para grupo interno anidado, seleccione las columnas de detalle adyacentes a la columna que contiene la fi la de resumen.

En el ejemplo siguiente, para agrupar de la columnas C a la E, que tiene una columna a.resumida F, seleccione de la columna C a la E. En la fi cha Datos, en el grupo Esquema,haga clic en Agrupar. Los símbolos del esquema aparecen en la pantalla al lado del grupo.

1.

2.

3.

Apre

ndo E

xcel 2010 In

term

edio


Siga seleccionando y agrupando columnas internas hasta que haya creado todos los niveles b. que desee en el esquema.

Not

a

Un esquema puede tener como máximo ocho niveles de detalle. Cada nivel “interno” facilita detalles del nivel “externo” anterior.

Desagrupar columnas o fi las

Esta operación permite anular los grupos formados con la herramienta esquema aplicada en las secciones anteriores. Para ello, deberá realizar los pasos siguientes:

Seleccionar el intervalo de 1. columnas o de fi las que se desea desagrupar.

En la fi cha 2. Datos, en el grupo Esquema, haga clic en Desagrupar.


Apre

ndo E

xcel

2010 I

nte

rmedio

26

Suge

renc

iaCómo especifi car la ubicación de la columna de resumen:

a. En la fi cha Datos, en el grupo Esquema, haga clic en el indicador de cuadro de diálogo.

b. Para especifi car una columna de resumen a la izquierda de la columna de detalles, desactive la casilla de verifi cación Columnas resumen a la derecha del detalle. Para especifi car una columna de resumen a la derecha de la columna de detalles, active la casilla de verifi cación Columnas resumen a la derecha del detalle.

Borrar un Esquema

Esta operación permite quitar todo el esquema existente en la hoja, anulando los grupos formados con la herramienta Esquema. Empleando los datos de la hoja Gastos, se desea borrar el esquema existente. Para ello, deberá realizar los siguientes pasos:

Haga clic en la hoja 1. Gastos.

En la fi cha 2. Datos, en el grupo Esquema, haga clic en la fl echa situada junto a Desagrupary, después, en Borrar esquema.

Si las fi las o columnas todavía están ocultas, arrastre los encabezados de fi las o columnas 3.visibles a ambos lados de las fi las y columnas ocultas, elija Ocultar y mostrar en el comando Formato, en el grupo Celdas de la fi cha Inicio y, a continuación, haga clic en Mostrar fi laso en Mostrar columnas.

Not

a

Si quita un esquema mientras los datos de detalle están ocultos, las columnas o fi las de detalle permanecen ocultas. Para mostrar los datos, arrastre los números de fi la visibles o las letras de columna adyacentes a las columnas o fi las ocultas. En la fi cha Inicio, en el grupo Celdas, haga clic en Formato,elija Ocultar y mostrar y, a continuación, haga clic en Mostrar fi las o en Mostrar columnas.

CONSOLIDAR DATOS

Para resumir y registrar resultados de hojas de cálculo independientes, puede consolidar datos de cada una de estas hojas en una hoja de cálculo maestra. Las hojas pueden estar en el mismo libro que la hoja de cálculo maestra o en otros libros. Al consolidar datos, lo que se hace es ensamblarlos de modo que sea más fácil actualizarlos y agregarlos de una forma periódica o específi ca. Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada ofi cina regional, podría utilizar una consolidación para resumir estas cifras en una hoja de cálculo de gastos corporativa. Esta hoja de cálculo maestra podría contener totales de ventas y promedios, niveles de inventario actuales y los productos más vendidos de toda la organización.

Apre

ndo E

xcel 2010 In

term

edio


La consolidación de datos se puede aplicar de las tres siguientes maneras:

Consolidación por posición•Cuando los datos de todas las hojas de cálculo están organizados en orden y ubicación idénticos.

Consolidación por categorías•Cuando los datos están organizados de forma diferente en las hojas de cálculo independientes pero utilizan los mismos rótulos de fi la y de columna para que la hoja de cálculo maestra pueda hacer coincidir los datos.

Consolidación por fórmula•Cuando se utilizan fórmulas con referencias de celdas o referencias 3D (referencia a un rango que ocupa dos o más hojas de cálculo en un libro) a otras hojas de cálculo que esté combinando porque no tiene una posición o categoría coherente en qué basarse.

A continuación, se desarrollará un ejemplo de aplicación de cada método de consolidación, empleando para cada caso el archivo indicado.

Consolidar datos por posición

Para realizar este ejemplo, deberá abrir el archivo CONSOLIDAR-Ejemplo1.El ejemplo consiste en consolidar los datos de las cuatro sucursales: norte, sur, este y oeste, sumando los gastos trimestrales de cada hoja.

Realice los pasos que se indican a continuación:

Confi gure los datos que se consolidarán en cada una de las hojas de cálculo independientes. 1.

Suge

renc

ias

Cómo confi gurar los datos para consolidar por posición:

Asegúrese de que cada rango de datos está en formato de lista: cada columna tiene un rótulo en la primera fi la, contiene hechos similares y no tiene fi las o columnas en blanco. Coloque cada rango en una hoja de cálculo diferente. No ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación. Asegúrese de que cada rango tiene el mismo diseño.Asigne un nombre a cada rango.

Ubíquese en la hoja de cálculo maestra, donde consolidará la información. Para este caso, 2.en la hoja consolidado haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados.

Not

a Asegúrese de dejar sufi cientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. El comando Consolidar rellena el área según proceda.


Apre

ndo E

xcel

2010 I

nte

rmedio

28

En la fi cha 3. Datos, en el grupo Herramientas de datos, haga clic en Consolidar.

En el cuadro 4. Función, haga clic en la función de resumen que desea que utilice Microsoft Offi ce Excel para consolidar los datos. Para este ejemplo, deberá emplear la opción predeterminada Suma.

Haga clic en el cuadro 5. Referencia.

Haga clic en la etiqueta de la hoja donde se encuentra el primer rango a consolidar y 6.selecciónelo.En este ejemplo deberá hacer clic en la etiqueta de la hoja Norte y enseguida seleccionar el rango A3:F9 (incluyendo los rótulos y los datos a consolidar). Luego, debe hacer clic nuevamente en el botón selector de rango para regresar al cuadro de diálogo Consolidar.

Haga clic en el botón 7. Agregar.

Repetir los pasos 6 y 7 para cada hoja adicional (8. Sur, este y oeste).

Apre

ndo E

xcel 2010 In

term

edio


En el cuadro 9. Usar rótulos en, activar las casillas Fila superior y también Columnaizquierda. (Esto se hace en el caso de que la hoja de destino no posea los títulos o rótulos de los datos a consolidar).

Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes: 10.

Para confi gurar la consolidación, de modo que puede actualizarla manualmente cambiando •las celdas y los rangos incluidos, desactive la casilla de verifi cación Crear vínculos con los datos de origen.En este caso, obtendría como resultado en la hoja Consolidado un cuadro con similares rótulos, incluyendo los valores resultantes de sumar los datos de las hojas Este, Norte,Oeste y Sur.

Para confi gurar la consolidación de manera que se actualice automáticamente cuando cam-•bien los datos de origen, active la casilla de verifi cación Crear vínculos con los datos de origen.En este caso, obtendría como resultado en la hoja Consolidado un cuadro con similares rótulos, pero incluyendo las fórmulas resultantes de sumar los datos de las hojas Este,Norte, Oeste y Sur.

Para completar el procedimiento haga clic en 11. Aceptar, y obtendrá un resultado diferente según haya activado o no, la casilla de vínculos.

Suge

renc

ia

Para agregar un rango cuando la hoja de cálculo a consolidar se encuentra en otro libro:

a. Abra previamente los archivos a consolidar, y para seleccionarlos utilice la fi cha Vista, grupo Ventana, y haga clic en Cambiar ventanas.

b. Haga clic en Examinar para buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.

La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.

a

b


Apre

ndo E

xcel

2010 I

nte

rmedio

30

Consolidar datos por categorías

Para realizar este ejemplo, deberá abrir el archivo CONSOLIDAR-Ejemplo2.Para que esta operación resulte factible, el requisito que deben poseer las hojas que se van a consolidar es que cada una posea los mismos rótulos (títulos) en la fi la superior o columna izquierda del rango de datos (aunque no necesariamente todas las hojas deben poseer la misma cantidad de rótulos, pero sí, deben ser escritos de manera idéntica en cada una de las hojas que se consolida).

El ejemplo consiste en totalizar en la hoja Consolidado los gastos anuales para cada tienda, tanto de Lima (Tienda1, Tienda2 y Tienda3) como de Provincias (Tienda4 y Tienda5).

Los datos a consolidar se muestran en la siguiente fi gura. Tal como se puede apreciar, en este caso los títulos de columna no coinciden en ambas hojas que se desea consolidar. Por ello, la consolidación tendrá que realizarse por categorías sin considerar vínculos porque de otro modo se producirían niveles de esquema con los títulos de fi la, pero aparecería un único elemento para cada nivel.


Confi gure los datos que se consolidarán en cada hoja de cálculo independiente. 1.Asegúrese de especifi car rótulos de categoría exactamente iguales, incluidas mayúsculas y minúsculas, en todas las áreas de origen. Por ejemplo, los rótulos Tienda1 y Tda.1 son diferentes y no se consolidarán.

Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos 2.consolidados en la hoja de cálculo maestra.

En la fi cha 3. Datos, en el grupo Herramientas de datos, haga clic en Consolidar.

En el cuadro 4. Función, haga clic en la función de resumen que desea que utilice Microsoft Offi ce Excel para consolidar los datos. Para este ejemplo, deberá emplear la opción predeterminada Suma.

Apre

ndo E

xcel 2010 In

term

edio


Hacer clic en el cuadro 5. Referencia.

Haga clic en la etiqueta de la hoja donde se encuentra el primer rango a consolidar y 6.selecciónelo.En este ejemplo deberá hacer clic en la etiqueta de la hoja Lima y enseguida seleccionar el rango A3:E16 (incluyendo los rótulos y los datos a consolidar). Luego, debe hacer clic nuevamente en el botón selector de rango para regresar al cuadro de diálogo Consolidar.

Haga clic en el botón 7. Agregar.

Repita los pasos 5, 6 y 7 para la hoja 8. Provincias.

En el cuadro 9. Usar rótulos en, activar las casillas Fila superior y también Columnaizquierda. (Esto se hace en el caso de que la hoja de destino no posea los títulos o rótulos de los datos a consolidar).

Haga clic en el botón 10. Aceptar.

Como resultado de esta operación, se observará en la hoja Consolidado un cuadro con similares rótulos en la columna izquierda, pero en la fi la superior aparece cada rótulo diferente que pertenece a cada hoja agregada para la consolidación. Además, se incluyen los resultados de sumar los datos de las cinco tiendas correspondientes a cada fi la de las hojas Lima y Provincias.

Los rótulos que no coincidan con los de las otras áreas de origen, producirán fi las o columnas independientes en la consolidación.


Apre

ndo E

xcel

2010 I

nte

rmedio

32

Consolidar datos por fórmulas

Para realizar este ejemplo, deberá abrir el archivo Consolidar-Ejemplo3.El ejemplo consiste en totalizar las ventas mensuales (en nuevos soles) de las tres sucursales: norte, centro y sur, haciendo la conversión a dólares.Para que esta operación resulte factible, el requisito que deben poseer las hojas que se van a consolidar es que cada una posea los mismos rótulos (títulos) en la fi la superior o columna izquierda del rango de datos, es decir el diseño del rango sea exactamente el mismo.


Confi gure los rótulos de fi la o columna en la hoja maestra de consolidación.1.

Seleccione la celda donde va a incluir los datos de consolidación. Para el ejemplo será la celda 2.C6 de la hoja Resumen.

Escriba la fórmula que incluya una referencia de celda a las celdas de origen de cada hoja de 3.cálculo o una referencia 3D que contenga los datos que va a consolidar. Para el ejemplo la fórmula es la siguiente:

4. Copie la fórmula a las demás celdas de la hoja Resumen.

Not

a Si el libro está confi gurado para calcular fórmulas automáticamente, una consolidación por fórmula siempre se actualiza automáticamente cuando cambian los datos de las hojas de cálculo independientes.

INFORMES DE TABLAS DINÁMICAS

Los temas que se desarrollarán en esta sección son los siguientes:

Conceptos básicos sobre informes dinámicos.•Crear un informe de tabla dinámica.•Modifi car el diseño y formato de una tabla dinámica.•Modifi car los cálculos y totales de una tabla dinámica. •

Apre

ndo E

xcel 2010 In

term

edio


Ordenar y aplicar fi ltros a los datos de una tabla dinámica.•Eliminar una tabla dinámica.•Mover una tabla dinámica.•Segmentar datos en informe de tablas dinámicas.•

Para desarrollar los ejemplos demostrativos de esta sección, se empleará el archivo INFORMESdinámicos Ejemplos.xlsx

Conceptos básicos sobre informes dinámicos

Utilidad

Utilice un informe de tabla dinámica para resumir, analizar, explorar y presentar datos de resumen. Utilice un informe de gráfi co dinámico para ver esos datos de resumen contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y tendencias. Ambos informes le permiten tomar decisiones informadas sobre datos críticos de su empresa. Las secciones siguientes ofrecen información general sobre los informes de tabla dinámica y de gráfi co dinámico.

Descripción

Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos numéricos en profundidad y para responder preguntas no anticipadas sobre datos. En Excel 2010, también existe la opción de emplear la segmentación de datos para fi ltrar datos. En la segmentación de datos se proporcionan botones en los que se puede hacer clic para fi ltrar datos de tablas dinámicas. Además del fi ltrado rápido, la segmentación de datos también indica el estado actual de fi ltrado, lo que facilita el entendimiento de lo que se muestra exactamente en un informe de tabla dinámica fi ltrado.

Un informe de tabla dinámica está especialmente diseñado para:

Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para él usuario.Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y formulas personalizados.Expandir y contraer niveles de datos para destacar los resultados y desplazarse hacia abajo para ver detalles de los datos de resumen de las áreas de interés.Rellenar hacia abajo las etiquetas de las tablas dinámicas, de manera que sea más fácil usar las tablas dinámicas.Desplazar fi las a columnas y columnas a fi las para ver resúmenes diferentes de los datos de origen.Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para centrarse en la información que les interesa.Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.


Apre

ndo E

xcel

2010 I

nte

rmedio

34

Buscar elementos en las tablas dinámicas con campos y columnas con una gran cantidad de elementos. Buscar etiquetas de elementos de campos dinámicos en una sola columna y con el fi ltro automático activado o desactivado.

En los informes de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias fi las de información. En el ejemplo anterior, la columna Deporte se convierte en el campo Deporte y cada registro de Golf se resume en un solo elemento Golf.Un campo de valores, como Suma de ventas, proporciona los valores que van a resumirse. La celda F3 del informe anterior contiene la suma del valor Ventas de cada fi la de los datos de origen, para la cual la columna Deporte contiene golf y la columna Trimestre contiene Trim3.

Componentes de una tabla dinámica

Es importante comprender el modo en que funciona la lista de campos de tabla dinámica y las formas en las que puede organizar diferentes tipos de campos para obtener los resultados deseados al crear el diseño de campo de un informe de tabla dinámica o gráfi co dinámico.

Datos de Origen provienen de datos que contienen datos estructurados organizados como uno •o varios campos (denominados también columnas) que se muestran en la lista de campos.Mueva un campo al área de campo fi ltro de informe en la lista de campos, lo que a su vez •moverá el campo al área de fi ltro de informe en el informe de tabla dinámica.Mueva un campo al área de campo de columna en la lista de campos, lo que a su vez moverá •el campo al área de etiqueta de columna en el informe de tabla dinámica.Mueva un campo al área de campo de fi la en la lista de campos, lo que a su vez moverá el •campo al área de fi la de columna en el informe de tabla dinámica.Mueva un campo al área de valores en la lista de campos, lo que a su vez moverá el campo •al área de valores en el informe de tabla dinámica.

Datos de origen

Registro de fi las o bases de datos subyacentes que proporcionan los datos para un informe de tabla dinámica. Puede crear un informe de tabla dinámica a partir de una lista de Microsoft Excel, una base de datos externa, varias hojas de cálculo de Excel u otro informe de tabla dinámica.

d t bl di á i d l d l d t d i

Apre

ndo E

xcel 2010 In

term

edio


Campo de fi ltro del informe

Campo que se utiliza para fi ltrar datos por elementos específi cos. En el ejemplo, el campo Categoría Producto muestra los datos de todas las categorías de producto. Para mostrar los datos de una sola categoría Producto, puede hacer clic en la fl echa de lista desplegable junta a (todas) y seleccionar la categoría producto bebidas.

Campo de valores

Los campos de datos proporcionan los valores de los datos que van a resumirse. Generalmente, los campos de datos contienen números que se combinan mediante la función de resumen Suma, pero también puede contar texto, en cuyo caso el informe de tabla dinámica utiliza la función de resumen Contar.

En el ejemplo de la fi gura, Importe es un campo de valores.


Apre

ndo E

xcel

2010 I

nte

rmedio

36

Campo fi la

Es un campo que distribuye sus elementos por fi las en una tabla dinámica. En el ejemplo de la fi gura, País y Ciudad son campos de fi la; mientras Argentina, Brasil, España y Perú son elementos del campo fi la País; y Buenos Aires, Córdova, Campinas, Resende, Río de Janeiro, Sao Paulo, Barcelona, Madrid, Sevilla, Arequipa, Lima y Cusco son elementos del campo fi la Ciudad.

Un informe de tabla dinámica puede contener más de un campo de fi la. En este caso tendrá un campo de fi la interior (Ciudad, en el ejemplo a continuación) que es el más próximo al área de datos. El resto de los campos de fi la son exteriores (País, en el ejemplo). Los elementos situados en la parte más exterior del campo de fi la se muestran solo una vez, pero los elementos del resto de los campos de fi la se repiten tantas veces como sea necesario.

Campo columna

Es un campo que distribuye sus elementos por columnas en una tabla dinámica. En el ejemplo de la fi gura, Forma de Pago es un campo columna, mientras que Contado y Crédito, son elementos del campo columna.

Elemento

Es una subcategoría o integrante de un campo. Los elementos representan las entradas únicas del campo en los datos de origen. Por ejemplo, el elemento Contado representa a todas las columnas de datos en la lista de origen en las que el campo Forma de Pago contiene la entrada Contado.

L l t it d l t á

Apre

ndo E

xcel 2010 In

term

edio


Crear una tabla dinámica a partir de una lista de Excel

A continuación, se mostrará el procedimiento para crear un informe tipo tabla dinámica en una hoja nueva que resuma los totales de ventas para el primer trimestre, considerando sólo los vendedores que corresponden a la región Norte, tal como se aprecia en la siguiente fi gura:

Para ello, tendrá que realizar los siguientes pasos:

Seleccione una celda de un rango de celdas o coloque el punto de inserción en una tabla de 1.Microsoft Offi ce Excel. Asegúrese de que el rango de celdas tiene encabezados de columna.

En la fi cha 2. Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Tabla dinámica.

Aparecerá el cuadro de diálogo Crear tabla dinámica.


Apre

ndo E

xcel

2010 I

nte

rmedio

38

Seleccione un origen de datos. Siga uno de los siguientes procedimientos:3.

• Haga clic en Seleccione una tabla o rango.• Escriba el rango de celdas o la referencia del nombre de tabla, por ejemplo =CuadroVentas,

en el cuadro Tabla o Rango.• Si seleccionó una celda de un rango de celdas o si el punto de inserción estaba en una

tabla antes de iniciar el asistente, el rango de celdas o la referencia del nombre de tabla se muestra en el cuadro Tabla o rango. (para el ejemplo Tabla o rango: Hoja1!$A$4: $J$428)

• Opcionalmente, haga clic en Contraer cuadro de dialogo para ocultar temporalmente el cuadro de dialogo, seleccione el rango en la hoja de cálculo y, a continuación, presione

Expandir dialogo .

Not

a Si el rango se encuentra en otra hoja de cálculo del mismo libro o de otro libro, escriba el nombre del libro y de la hoja de cálculo utilizando la siguiente sintaxis:

[nombredel_Libro]nombredelaHoja!Rango).

Escriba una ubicación. Siga uno de los procedimientos siguientes: 4.

Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la •celda A1, haga clic en Nueva hoja de cálculo.Para poner el informe de tabla dinámica en una hoja de cálculo existente, seleccione • Hojade cálculo existente y, a continuación, escriba la primera celda del rango de celdas donde desee situar el informe de tabla dinámica.

O, bien, haga clic en Contraer cuadro de diálogo para ocultar temporalmente el cuadro de diálogo, seleccione la primera celda de la hoja de cálculo y, a continuación, presione

Expandir diálogo .

Haga clic en 5. Aceptar.

Un informe de tabla dinámica vacío se agregará a la ubicación que especifi có en la Lista de campos de tabla dinámica que se muestra, de modo que puede comenzar a agregar campos, crear un diseño y personalizar el informe de tabla dinámica.

Apre

ndo E

xcel 2010 In

term

edio

39“Cibertec Educación para un mundo que cambia”

Cambiar el diseño de los campos de una tabla dinámica

Después de crear un informe de tabla dinámica o gráfi co dinámico, utilice la lista de campos de tabla dinámica para agregar campos. Si desea cambiar un informe de tabla dinámica o gráfi co dinámico, utilice la lista de campos para ordenar y quitar campos. De manera predeterminada, la lista de campos de tabla dinámica contiene dos secciones: una sección de campos en la parte superior para agregar o quitar campos, y una sección de diseño en la parte inferior para volver a organizar campos y ajustar su posición. Puede acoplar la lista de campos de tabla dinámica en algún lado de la ventana y cambiarle el tamaño horizontalmente. También puede desacoplarla, en cuyo caso podrá cambiarle el tamaño tanto vertical como horizontalmente.

Agregar campos

Para agregar campos al informe, siga uno o varios de estos procedimientos:

Active la casilla de verifi cación situada junto a cada nombre de campo en la sección de campos. El campo se coloca en el área predeterminada de la sección de diseño, pero puede organizar los campos si lo desea.

Haga clic con el botón secundario del mouse (ratón) y seleccione el comando correspondiente, Agregar a fi ltro de informe,Agregar a etiqueta de columna, Agregar a etiqueta de fi la y Agregar a valores, para colocar el campo en un área específi ca de la sección de diseño.

Suge

renc

ia También, puede hacer clic y mantener presionado el botón del mouse en un nombre de campo y después arrastrar el campo entre la sección de campos y un área de la sección de diseño. Para agregar un campo varias veces, repita la operación.


Apre

ndo E

xcel

2010 I

nte

rmedio

40

Organizar los campos

Puede cambiar el orden o la posición de los campos existentes utilizando alguna de las cuatro áreas situadas en la parte inferior de la sección de diseño:

Informe de tabla dinámica Descripción

Valores Se utilizan para mostrar datos numéricos de resumen.

Etiquetas de fi la Se utilizan para mostrar campos como fi las en el lado del informe. Una fi la en una posición inferior se anida con otra fi la que está justo encima de ella.

Etiquetas de columna Se utilizan para mostrar campos como columnas en la parte superior del informe. Una columna en una posición inferior se anida con otra columna que está justo encima de ella.

Filtro de informe Se utiliza para fi ltrar todo el informe en función del elemento seleccionado en el fi ltro de informe.

Para organizar los campos, puede hacer clic en el nombre de campo en una de las áreas y, a •continuación, seleccione uno de los comandos siguientes:

Comando Función

SubirSube el campo una posición en el área.

Bajar Baja el campo una posición en el área.

Mover al fi ltro de informe

Mueve el campo al área de fi ltro de informe.

Mover a rótulos de fi la Mueve el campo al área de etiquetas de fi la.

Mover a rótulos de columna

Mueve el campo al área de etiquetas de columna.

Mover a valores Mueve el campo al área de valores.

Confi guración de campo de valor, Confi guración de campo

Muestra los cuadros de diálogo Confi guración de campo o Confi guración de campo de valor. Para obtener más información sobre cada opción, haga clic en el botón de Ayuda

situado en la parte superior del cuadro de diálogo.

Suge

renc

ia

También, puede hacer clic y mantener presionado el botón del mouse en un nombre de campo y después arrastrar el campo entre las secciones de campo y de diseño, y entre las diferentes áreas.

Para este ejemplo, debe arrastrar el campo Región al área de Filtro de informe, el campo Vendedor al área de Rótulos de fi la, el campo Mes al área de Rótulos de columna y el campo Ventas al área de Valores.

Apre

ndo E

xcel 2010 In

term

edio


La operación anterior producirá el resultado siguiente:

Quitar campos

Para quitar un campo, haga clic en el nombre del campo en una de las áreas de diseño y luego haga clic en Quitar campo, o desactive la casilla de verifi cación situada junto a cada nombre de campo en la sección de campos.

Suge

renc

ia

También, puede hacer clic y mantener presionado el botón del mouse en un nombre de campo en la sección de diseño y después arrastrar el campo fuera de la lista de campos de tabla dinámica.

Not

a

Al desactivar una casilla de verifi cación en la sección de campos se quitan todas las apariciones del campo del informe.


Apre

ndo E

xcel

2010 I

nte

rmedio

42

Diseñar la presentación y el formato de una tabla dinámica

Tras haber agregado los campos, presentado el nivel correcto de detalles, creado cálculos y ordenado, fi ltrado y agrupado los datos como desea en un informe de tabla dinámica, es posible que desee mejorar el diseño y el formato del informe para mejorar la legibilidad del texto y hacerlo más atractivo. Hay varias maneras de cambiar el diseño y el formato de un informe de tabla dinámica, tal y como se describe en las siguientes secciones.

Cambiar el estilo de formato de tabla dinámica

Puede cambiar con facilidad el estilo de una tabla dinámica con una galería de estilos. Microsoft Offi ce Excel proporciona un gran número de estilos de tabla (o estilos rápidos) predefi nidos que puede utilizar para dar formato rápidamente a una tabla dinámica. También, puede agregar o quitar bandas de fi las y columnas. Aplicar bandas puede facilitar la lectura y la búsqueda de los datos.

Aplicar estilos

Haga clic en el informe de tabla dinámica.1.

En la fi cha 2. Diseño, en el grupo Estilos de tabla dinámica, haga lo siguiente:

Haga clic en un estilo visible, desplácese por la galería o, para ver todos los estilos •disponibles, haga clic en el botón Más de la parte inferior de la barra de desplazamiento. Opcionalmente, si ha •mostrado todos los estilos disponibles y desea crear un estilo de tabla dinámica personalizado, haga clic en Nuevo estilo de tabla dinámica ubicada en la parte inferior de la galería para mostrar el cuadro de diálogo Nuevo estilo de tabla dinámica.

Apre

ndo E

xcel 2010 In

term

edio


Aplicar bandas

Haga clic en el informe de tabla dinámica. 1.

En la fi cha 2. Diseño, en el grupo Opciones de estilo de tabla dinámica, siga uno de los procedimientos siguientes:

Para alternar cada fi la con un formato de color más claro o más oscuro, haga clic en • Filascon bandas.Para alternar cada columna con un formato de color más claro o más oscuro, haga clic en •Columnas con bandas.Para incluir encabezados de fi la en el estilo de aplicar bandas, haga clic en • Encabezadosde fi la.Para incluir encabezados de columna en el estilo de aplicar bandas, haga clic en •Encabezados de columna.

Cambiar el formato de número de un campo

En el informe de tabla dinámica, seleccione el campo para el que desea cambiar el formato 1.de número.

En la fi cha 2. Opciones, en el grupo Campo activo, haga clic en Confi guración de campo.

Se muestra el cuadro de diálogo Confi guración de campo para los rótulos y los fi ltros de informe, y el cuadro de diálogo Confi guración de campo de valor para los valores.


Apre

ndo E

xcel

2010 I

nte

rmedio

44

Haga clic en 3. Formato de número en la parte inferior del cuadro de diálogo. Aparecerá el cuadro de diálogo Formato de celdas.

En la lista 4. Categoría, haga clic en la categoría de formato que desee.

Seleccione las opciones de formato que desee y, a 5.continuación, haga clic en Aceptar dos veces.

Conservar o descartar el formato


En la fi cha 2. Opciones, en el grupo Tabla dinámica, haga clic en Opciones.

Observa que se muestra el cuadro de diálogo 3.Opciones de tabla dinámica.

Haga clic en la fi cha 4. Diseño y formato y, a continuación, en la sección Formato, siga uno de los procedimientos siguientes:

Para guardar el diseño y formato del informe •de tabla dinámica de modo que pueda utilizarlo cada vez que realice una operación en la tabla dinámica, active la casilla de verifi cación Mantener el formato de la celda al actualizar.

Apre

ndo E

xcel 2010 In

term

edio


Para descartar diseño y formato del informe de tabla dinámica y volver al diseño y formato •predeterminado cada vez que realice una operación en la tabla dinámica, desactive la casilla de verifi cación Mantener el formato de la celda al actualizar.Para que las columnas del informe de tabla dinámica se ajusten automáticamente al •tamaño del valor de texto o número más ancho, active la casilla Autoajustar anchos de columnas al actualizar.Para mantener el ancho actual de las columnas del informe de tabla dinámica, desactive •la casilla Autoajustar anchos de columnas al actualizar.

Not

a El formato de gráfi co dinámico también se ve afectado por esta opción. Sin embargo, no se guardan las líneas de tendencia, los rótulos de datos, las barras de error u otros cambios realizados en las series de datos.

Cambiar el formulario del informe de tabla dinámica

Puede cambiar el formulario de un informe de tabla dinámica y de campos individuales del informe a compacto, esquemático o tabular. Para ello, debe realizar los pasos siguientes:


En la fi cha 2. Diseño, en el grupo Diseño, haga clic en Diseño de informe y, a continuación, realice uno de los procedimientos siguientes:

Mostrar de forma compacta: se usa para evitar que los datos relacionados se salgan •horizontalmente de la pantalla y minimizar la necesidad de desplazarse por ella. Los campos del lado están en una columna y tienen aplicada sangría para mostrar la relación de la columna anidada.Mostrar en forma de esquema: se usa para esquematizar los datos al estilo clásico de tabla •dinámica.Mostrar en formato tabular: se usa para ver todos los datos en un formato de tabla •tradicional y para copiar celdas en otra hoja de cálculo con facilidad.Repetir todas las etiquetas de elementos: se usa para repetir el elemento de las etiquetas •en un informe de tabla dinámica para facilitar la lectura de una tabla dinámica.No repetir las etiquetas de elementos: se usa para no repetir el elemento de las etiquetas •en un informe de tabla dinámica.


Apre

ndo E

xcel

2010 I

nte

rmedio

46

Mostrar u ocultar las líneas en blanco

Puede mostrar u ocultar las líneas en blanco después de una fi la o elemento.

Seleccione un campo de fi la y, a continuación, en la fi cha 1. Diseño, en el grupo Diseño, haga clic en Filas en blanco.

Para agregar o quitar las fi las en blanco, haga clic en una de las opciones. 2.

Suge

renc

ias También puede hacer doble clic en el campo de fi la en el formulario esquemático o tabular, para agregar fi las.

Puede aplicar formatos de caracter y celda a las líneas en blanco, pero no escribir datos en ellas.

Agrupar elementos en una Tabla dinámica

Puede agrupar los elementos de un campo de forma personalizada para aislar el subconjunto de los datos que necesita para trabajar y que no puede agrupar de otro modo, por ejemplo, con las opciones de ordenación y fi ltrado.

Apre

ndo E

xcel 2010 In

term

edio


Por ejemplo: agrupar fechas u horas

Seleccione el campo de fecha u hora en el informe de tabla dinámica que desee agrupar.1.En la fi cha 2. Opciones, en el grupo Agrupar, haga clic en Agrupar Campos.

Escriba la primera hora o fecha que desee agrupar en el cuadro 3.Comenzar en y la última en el cuadro Terminar en.En el cuadro 4. Por, haga clic en uno o más periodos de tiempo para los grupos.

Para agrupar los elementos por semanas, haga clic en Días en el cuadro Por, asegúrese de que Días es el único periodo seleccionado y, después, haga clic en 7 en el cuadro Número de días.

A continuación, puede hacer clic en otros periodos de tiempo por los que desee agrupar, como Años.

Expandir o contraer los detalles de una tabla dinámica

En un informe de tabla dinámica o de gráfi co dinámico puede expandir o contraer hasta cualquier nivel de detalle de los datos, e incluso todos los niveles en una sola operación. También, puede expandir o contraer hasta un nivel de detalle posterior al siguiente nivel. Por ejemplo, empezando en el nivel de país o región, puede expandir hasta el nivel de ciudad, que expande el nivel de


Apre

ndo E

xcel

2010 I

nte

rmedio

48

estado o provincia y el nivel de ciudad. Esto puede suponer un gran ahorro de tiempo cuando se trabaja con muchos niveles de detalle.

Mostrar u ocultar los botones para expandir o contraer en una tabla dinámica

Aunque los botones para expandir y contraer están activados de forma predeterminada, puede haberlos desactivado al imprimir un informe. Para expandir y contraer los niveles de detalle de un informe, debe activar estos botones.Para mostrar u ocultar estos botones, en el grupo Mostrarde la fi cha Opciones, haga clic en Botones +/-.

Expandir o contraer niveles de detalle en un informe de tabla dinámica

En el elemento del rotulo de columna de un informe de tabla dinámica, o serie de un informe de grafi co dinámico, realice uno de los procedimientos siguientes:

Haga clic en el botón para 1. Expandir o contraer. También, puede hacer doble clic en el elemento.

Haga clic en el botón secundario del mouse (ratón) 2.en el elemento, seleccione Expandir/Contraery, a continuación, siga uno de los procedimientos siguientes:

Para ver los detalles del elemento actual, haga •clic en Expandir.Para ocultar los detalles del elemento actual, •haga clic en Contraer.Para ver los detalles de todos los elementos •de un campo, haga clic en Expandir todo el campo.Para ocultar los detalles de todos los elementos •de un campo, haga clic en Contraer todo el campo.Para ver el nivel de detalle posterior al siguiente •nivel, haga clic en Expandir hasta “<nombre del campo>”.Para ocultar un nivel de detalle posterior al siguiente nivel, haga clic en • Ocultar hasta <nombre del campo >”.

La siguiente fi gura muestra un Informe de tabla dinámica que se agregó 4 veces el campo Importe en la sección Valores y se confi guró para que los datos del campo Importe muestre el valor de la función resumen como Suma, Cuenta, Promedio y Máximo.

Apre

ndo E

xcel 2010 In

term

edio


Modifi car los cálculos y totales en una tabla dinámica

Cambiar la función de resumen de un campo en una tabla dinámica

Los datos del área Valores resumen los datos de origen subyacentes en el informe dinámico de la siguiente forma: los valores numéricos utilizan la función SUMA, y los valores de texto la función CONTAR. No obstante, puede cambiar la función de resumen (por ejemplo: promedio, máximo, mínimo, etcétera). De manera opcional, también puede crear un cálculo personalizado.

Seleccione un campo en el área Valores cuya función de resumen del informe de tabla 1.dinámica desea cambiar. En la fi cha 2. Opciones, en el grupo Campo activo, haga clic en Confi guración de campo.Se muestra el cuadro de diálogo Confi guración de campo de valor.El Nombre del origen es el nombre del campo en el origen de datos.El Nombre personalizado muestra el nombre del campo actual en el informe de tabla dinámica, o el nombre del origen, si no hay ningún nombre personalizado. Para cambiar el Nombre personalizado, haga clic en el cuadro de texto y modifi que el nombre.

Haga clic en la fi cha 3. Resumir por valores.

En el cuadro 4. Resumir campo de valor por, haga clic en la función de resumen que desea utilizar.

Funciones de resumen que puede utilizar

Función Resumen

SumaLa suma de los valores. Es la función predeterminada de los valores numéricos.

CuentaEl número de valores. La función de resumen Cuenta funciona del mismo modo que la función de la hoja de cálculo CONTAR. Cuenta es la función predeterminada de los valores que no son numéricos.

Promedio El promedio de los valores.

Máx El valor máximo.

Mín El valor mínimo.

Producto El producto de los valores.

Contar números El número de valores que son números. La función de resumen Contar núm funciona del mismo modo que la función de la hoja de cálculo CONTAR.


Apre

ndo E

xcel

2010 I

nte

rmedio

50

También, puede utilizar un cálculo personalizado haciendo lo siguiente: 5.Haga clic en la fi cha a. Mostrar valores como.Haga clic en el cálculo que desea en el cuadro b. Mostrar valores como.

Cálculos personalizados que puede utilizar

Función Resultado

Sin cálculo Muestra el valor que se ha especifi cado en el campo.

% del total generalMuestra los valores como un porcentaje del total general de todos los valores o puntos de datos en el informe.

% del total de columnaMuestra todos los valores de cada columna o serie como un porcentaje del total de la columna o serie.

% del total de fi lasMuestra el valor de cada fi la o categoría como un porcentaje del total de la fi la o categoría.

% deMuestra los valores como un porcentaje del valor de Elemento base en Campo base.

% del total de fi las principales

Calcula los valores como se indica a continuación:(valor del elemento) / (valor del elemento primario en fi las)

% del total de columna principales

Calcula los valores como se indica a continuación:(valor del elemento) / (valor para el producto principal en las columnas)

% del total del principalCalcula los valores como se indica a continuación:(valor del elemento) / (valor del elemento primario de la seleccionada Campo base)

Diferencia de Muestra los valores como la diferencia del valor de Elemento base en Campo base.

% de la diferencia deMuestra los valores como la diferencia de porcentaje del valor de Elemento base en Campo base.

Total enMuestra el valor de elementos sucesivos en la Campo base como un total de ejecución.

% Total actual enCalcula el valor como un porcentaje de elementos sucesivos en Campo base que se muestran como un total de ejecución.

Clasifi car de menor a mayor

Muestra el rango de valores seleccionados en un campo específi co, en la lista el elemento más pequeño en el campo como 1 y cada valor más grande con un valor de rango más alto.

Clasifi car de mayor a menor

Muestra el rango de valores seleccionados en un campo específi co, en la lista el elemento más grande en el campo como 1 y cada valor más pequeño con un valor de rango más alto.

ÍndiceCalcula los valores como se indica a continuación: ((valor en celda) x (Suma total de sumas totales)) / ((Suma total de fi la) x (Suma total de columna))

Seleccione un c. Campo base y un Elemento base, si estas opciones están disponibles para el cálculo que ha elegido.

Campo base no debe ser el mismo campo que eligió en el paso 1.

Apre

ndo E

xcel 2010 In

term

edio


La siguiente fi gura muestra un Informe de tabla dinámica con dos campos Importe en la sección Valores y se confi guro un campo Importe, mostrar valores como Diferencia de campo base

Forma de pago y elemento base Contado.

Mostrar u ocultar subtotales para todo el informe

Para los rótulos exteriores en formulario compacto o esquemático, puede mostrar los subtotales encima o debajo de sus elementos, o bien ocultar los subtotales haciendo lo siguiente:

En la fi cha 1. Diseño, en el grupo Diseño, haga clic en Subtotales.

Siga uno de los procedimientos siguientes: 2.Seleccione• No mostrar subtotales.Seleccione• Mostrar todos los subtotales en la parte inferior del grupo.Seleccione• Mostrar todos los subtotales en la parte superior del grupo.


Apre

ndo E

xcel

2010 I

nte

rmedio

52

Mostrar u ocultar totales generales para todo el informe

Para mostrar u ocultar los totales del informe de tabla dinámica actual, realice los pasos siguientes:


En la fi cha 2. Diseño, en el grupo Diseño, haga clic en Totales generales y, a continuación, seleccione una de las siguientes opciones:

Desactivado para fi las y •columnas.Activado para fi las y columnas. •Desactivado solo para fi las. •Activado solo para columnas.•

Crear fórmulas en una tabla dinámica

Si las funciones de resumen y los cálculos personalizados no proporcionan los resultados que desea, puede crear sus propias fórmulas en campos calculados y elementos calculados. Por ejemplo, podría agregar un elemento calculado con la fórmula de la comisión de ventas, que podría ser diferente para cada región. Así, el informe de tabla dinámica incluiría la comisión automáticamente en los subtotales y en los totales generales.

Utilice un campo calculado si piensa utilizar los datos de otro campo en la fórmula. •Utilice un elemento calculado si desea que la fórmula utilice datos de uno o más elementos •específi cos en un campo.

Para agregar un campo calculado, realice los pasos siguientes

Haga clic en el informe de tabla dinámica.1.

En la fi cha 2. Opciones, en el grupo Cálculos, haga clic en Campos, elementos y conjuntos y, a continuación haga clic en Campo calculado.

Se muestra el cuadro de dialogo 3. Insertar campo calculado.

En el cuadro 4. Nombre, escriba un nombre para el campo, Por ejemplo Importe IGV.

En el cuadro 5. Formula, escriba la fórmula para el campo.

Apre

ndo E

xcel 2010 In

term

edio


Para utilizar los datos de otro campo en la formula, haga clic en el campo en el cuadro 6.Campos y, a continuación, haga clic en Insertar campo. Por ejemplo, para calcular el Importe adicionando el IGV para cada valor del importe, podría escribir = Importe*1.18.

Para agregar un elemento calculado, realice los pasos siguientes:

Si los elementos del campo están agrupados, en la 1.fi cha Opciones, en el grupo Agrupar, haga clic en Desagrupar.

Haga clic en el campo donde desee agregar el elemento 2.calculado. Por ejemplo seleccionar un elemento contado o crédito del campo Forma de pago

En la fi cha 3. Opciones, en el grupo Cálculos, haga clic en Campos, elementos y conjuntos y, a continuación haga clic en Elemento Calculado.

Se muestra el cuadro de dialogo Insertar elementocalculado en “Forma de Pago”.

En el cuadro 4. Nombre, escriba un nombre para el campo, Por ejemplo Crédito Incremento 10%.


Apre

ndo E

xcel

2010 I

nte

rmedio

54

En el cuadro 5. Formula, escriba la fórmula para el elemento.

Para utilizar los datos de un elemento en la formula, haga clic en el elemento en la lista 6.Elementos y, a continuación, haga clic en Insertar campo (el elemento debe ser del mismo campo que el elemento calculado). Por ejemplo, para calcular el incremento del elemento Crédito en 10% para cada valor del elemento Crédito, podría escribir = Crédito*1.10.


Para agregar un elemento calculado, realice los pasos siguientes:

Si los elementos del campo están agrupados, en la fi cha 1. Opciones, en el grupo Grupo, haga clic en Desagrupar.Haga clic en el campo donde desee agregar el elemento calculado. 2.En la fi cha 3. Opciones, en el grupo Herramientas haga clic en Fórmulas y, a continuación, haga clic en Elemento calculado.En el cuadro 4. Nombre, escriba un nombre para el elemento calculado. En el cuadro 5. Fórmula, escriba la fórmula para el elemento. Para utilizar los datos de un elemento de la fórmula, haga clic en el elemento en la lista Elementos y, a continuación, en Insertar elemento (el elemento debe ser del mismo campo que el elemento calculado).Haga clic en 6. Agregar.

Suge

renc

ia

Para los elementos calculados, puede especifi car diferentes fórmulas, celda por celda:

Por ejemplo, si el elemento calculado ZonaEste tiene la fórmula =Naranjas * 0,25 para todos los meses, puede cambiar la fórmula por =Naranjas *0,5 para junio, julio y agosto.Siga este procedimiento:

Haga clic en la celda donde desee cambiar la fórmula. 1)

Para cambiar la fórmula en varias celdas, mantenga presionada la tecla CTRL y 2)

haga clic en las celdas.

En la barra de fórmulas, escriba los cambios en la fórmula. 3)

Eliminar un informe de una tabla dinámica


En la fi cha 2. Opciones, en el grupo Acciones, haga clic en Seleccionar y, a continuación, en Toda la tabla dinámica.

1

2

Apre

ndo E

xcel 2010 In

term

edio


Presione3. [SUPR].N

ota

Al eliminar el informe de tabla dinámica asociado de un informe de gráfi co dinámico, se crea un gráfi co estático que no será posible modifi car.

Buscar elementos en las tablas dinámicas

Excel 2010 permite buscar los elementos en las tablas dinámicas cuando se trabaje con campos y columnas con una gran cantidad de elementos. Mediante la búsqueda de elementos, se puede buscar elementos pertinentes entre miles o incluso millones de fi las de la tabla dinámica.

El siguiente informe de tabla dinámica muestra los nombre de todos los productos.

Seguir los siguientes pasos para buscar los elementos que contienen la palabra chocolate en el campo Nombre Producto:

En el informe de tabla dinámica haga clic en 1.

la fl echa despegable del campo Nombre Producto.

En el recuadro Buscar, escriba chocolate. 2.Automáticamente fi ltra los elementos encontrados.


Apre

ndo E

xcel

2010 I

nte

rmedio

56

Haga clic en el 3. Aceptar

Segmentar datos en informe de tablas dinámicas

En versiones anteriores de Microsoft Excel, se pueden usar fi ltros de informes para fi ltrar los datos de un informe de tabla dinámica, aunque no es fácil ver el estado actual de fi ltrado cuando se fi ltran varios elementos. Para fi ltrar los datos en Microsoft Excel 2010, se puede usar la segmentación de datos. Esta proporciona botones en los que se puede hacer clic para fi ltrar los datos de las tablas dinámicas. Además del fi ltrado rápido, la segmentación de datos también indica el estado actual de fi ltrado, lo cual facilita el entendimiento de lo que se muestra exactamente en un informe de tabla dinámica fi ltrado.

¿Qué es la segmentación de datos?

Las segmentaciones de datos son componentes de fi ltrado fáciles de usar que contienen un conjunto de botones que le permiten fi ltrar los datos de un informe de tabla dinámica rápidamente, sin la necesidad de abrir listas desplegables para buscar los elementos que se desean fi ltrar.

Una segmentación de datos generalmente incluye los siguientes elementos:

Un encabezado de segmentación de datos indica la categoría de •los elementos que contiene.Un botón de fi ltrado que no está activado indica que el elemento no está incluido en el •fi ltro.Un botón de fi ltrado que está activado indica que el elemento está incluido en el fi ltro.•Un botón Borrar fi ltro quita el fi ltro mediante la selección de todos los elementos de la •segmentación de datos.Una barra de desplazamiento permite desplazarse cuando hay más datos de los que pueden •verse actualmente en la segmentación de datos.Los controles para mover y cambiar el tamaño de los bordes permiten cambiar el tamaño y •la ubicación de la segmentación de datos.

Crear una segmentación de datos en una tabla dinámica existente

Haga clic en cualquier lugar del informe 1.de tabla dinámica para el cual desea crear una segmentación de datos.De esta forma, se mostrarán las 2.Herramientas de tabla dinámica y se agregarán las pestañas Opciones y Diseño.

t tá i l id l

Apre

ndo E

xcel 2010 In

term

edio


En el grupo 3. Ordenar y fi ltrar de la pestaña Opciones,haga clic en Insertar Segmentación de datos.

En el cuadro de diálogo 4. Insertar Segmentación de datos, active la casilla de verifi cación de los campos de la tabla dinámica para los cuales desea crear una segmentación de datos. Para este ejemplo active la casilla País Destinatario.


Se mostrará una segmentación de datos para cada campo 6.que haya seleccionado.

En cada segmentación de datos, haga clic en los elementos 7.en los cuales desea aplicar el fi ltro.

Para seleccionar más de un elemento, mantenga presionada la tecla 8. CTRL y luego, haga clic en los elementos en los que desea aplicar el fi ltro.

El resultado fi nal es como la siguiente fi gura.9.

Aplicar formato a una segmentación de datos

Haga clic en la segmentación de datos a la que desea dar formato.1.

Se mostrarán las Herramientas de segmentación de datos y se agregará la pestaña Opciones. 2.En la pestaña Opciones, en el grupo Estilos de segmentación de datos, haga clic en el estilo que desee.


Apre

ndo E

xcel

2010 I

nte

rmedio

58

Para ver todos los estilos disponibles, haga clic en el botón Más.

Eliminar una segmentación de datos

Siga uno de los pasos que se indican a continuación:

• Haga clic en la segmentación de datos y luego presione SUPR.• Haga clic con el botón secundario en la segmentación de datos y, a continuación, haga clic en

Quitar <Nombre de la segmentación de datos>.

GRÁFICOS DINÁMICOS

Un informe de gráfi co dinámico proporciona análisis de datos interactivo, como una tabla dinámica. Puede cambiar las vistas de los datos, ver niveles de detalle diferentes o reorganizar el diseño del gráfi co arrastrando campos y mostrando u ocultando elementos de los mismos.

Los temas que se desarrollarán en esta sección son los siguientes:

Conceptos básicos sobre gráfi cos dinámicos.Crear un informe de gráfi co dinámico.Modifi car el diseño y formato de un gráfi co dinámico.

Para desarrollar los ejemplos demostrativos de esta sección, se continuará trabajando con el archivo INFORMES DINÁMICOS Ejemplos.xlsx

Apre

ndo E

xcel 2010 In

term

edio


Conceptos básicos sobre Gráfi cos dinámicos

Utilidad

Utilice un informe de gráfi co dinámico para ver los datos de resumen contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y tendencias.

Descripción

Un informe de gráfi co dinámico representa gráfi camente los datos de un informe de tabla dinámica. Puede modifi car el diseño y los datos que se muestran en un informe de gráfi co dinámico, tal y como se hace con un informe de tabla dinámica. Un informe de gráfi co dinámico siempre tiene un informe de tabla dinámica asociado que utiliza el diseño correspondiente. Ambos informes tienen campos que se corresponden. Cuando modifi ca la posición de un campo en uno de los informes, también se modifi ca el campo correpondiente del otro informe.

Además de las series, categorías, marcadores de datos y ejes de los gráfi cos estándares, los informes de gráfi cos dinámicos tienen algunos elementos especializados que se corresponden con los informes de tablas dinámicas, como se muestra en la siguiente ilustración.

Comparar un informe de tabla dinámica y un gráfi co dinámico

Al crear un informe de gráfi co dinámico a partir de un informe de tabla dinámica, el diseño del informe de gráfi co dinámico, es decir, la posición de sus campos, está determinado inicialmente por el diseño del informe de tabla dinámica. Si crea primero el informe de gráfi co dinámico, determina el diseño del gráfi co arrastrando campos de la Lista de campos de tabla dinámica hasta las áreas de la hoja de gráfi co.Los siguientes informes de ventas de tabla dinámica y gráfi co dinámico muestran la relación entre los dos.

Crear un gráfi co dinámico a partir de una tabla dinámica existente

Se tiene en la Hoja2 una tabla dinámica fi ltrada así:

A continuación, se propone como ejemplo desarrollar un gráfi co dinámico a partir de la tabla dinámica ubicada en la Hoja2, de tal manera que resulte según la siguiente muestra:


Apre

ndo E

xcel

2010 I

nte

rmedio

60

Para ello, debe realizar los siguientes pasos:

Haga clic en el informe de tabla dinámica ubicado en la 1. Hoja2.

En la fi cha 2. Insertar, en el grupo Gráfi cos, haga clic en un tipo y enseguida en un subtipo de gráfi co. Para este ejemplo, seleccione Columna y luego el subtipo Cilindro agrupado.

Not

as

Puede utilizar cualquier tipo de gráfi co excepto los gráfi cos xy (dispersión), de burbujas o de cotizaciones.

Al generar un informe dinámico así se obtendrá un gráfi co que estará vinculado con la tabla dinámica que le dio origen, de tal manera que cualquier cambio en el diseño de uno de estos objetos incidirá o se refl ejará en el otro.

Organizar los campos en un informe de gráfi co dinámico

Puede cambiar el orden o la posición de los campos existentes utilizando alguna de las cuatro áreas de la sección de diseño:

Componente Descripción

Valores Se utilizan para mostrar datos numéricos de resumen.

Campos de eje (categorías) Se utiliza para mostrar los campos como un eje en el gráfi co.

Apre

ndo E

xcel 2010 In

term

edio


Campos de leyenda Se utilizan para mostrar los campos en la leyenda del gráfi co.

Filtro de informe Se utiliza para fi ltrar todo el informe en función del elemento seleccionado en el fi ltro de informe.

Para este ejemplo, seleccione según la fi gura mostrada, los siguientes campos activos en el gráfi co dinámico: Región como Filtro de informe, Vendedor como Campo de eje, Mes comoCampo de leyenda y Ventas como Valores.

Modifi car el diseño y formato de un gráfi co dinámico

Después de crear un gráfi co, puede cambiar su aspecto de forma instantánea. En lugar de agregar o cambiar manualmente los elementos o el formato del gráfi co, puede aplicarle rápidamente un diseño y un estilo predefi nido. Aunque Microsoft Offi ce Excel proporciona diversos diseños y estilos predefi nidos (o diseños y estilos rápidos) muy útiles, si fuera necesario, puede personalizarlos aún más cambiando manualmente el diseño y el estilo de cada uno de los elementos del gráfi co.

Seleccionar un diseño de gráfi co predefi nido

Haga clic en el gráfi co al que desea dar formato. 1.Se mostrarán las Herramientas de gráfi cos con las fi chas Diseño, Presentación, Formatoy Analizar.

En la fi cha 2. Diseño, en el grupo Diseños de gráfi co, haga clic en el diseño que desee utilizar. Para este ejemplo, seleccione el Diseño 3.

Como resultado obtendrá un gráfi co cuya leyenda ha cambiado de ubicación, y al que se le ha agregado un título, el cual puede editarlo sobreescribiéndolo con el texto Reporte de Ventas.

Not

as

Cuando se reduzca el tamaño de la ventana de Excel, los diseños de gráfi co estarán disponibles en la galería Diseño rápido del grupo Diseños de gráfi co.

Para ver todos los diseños disponibles, haga clic en Más .


Apre

ndo E

xcel

2010 I

nte

rmedio

62

Seleccionar un estilo de gráfi co predefi nido

Haga clic en el gráfi co al que desea dar 1.formato. Se mostrarán las Herramientas de gráfi cos con las fi chas Diseño, Presentación, Formato y Analizar.

En la fi cha 2. Diseño, en el grupo Estilos de diseño, haga clic en el estilo que desee utilizar. Para este ejemplo, seleccione el Estilo 18.

En la fi cha 3. Analizar, en el grupo Mostrar u ocultar, haga clic en botones de campo y selecciona ocultar todos.

Como resultado obtendrá un gráfi co semejante a la siguiente fi gura:4.

Not

as

Cuando se reduzca el tamaño de la ventana de Excel, los estilos de gráfi co estarán disponibles en la galería Estilos rápidos del grupo Estilos de diseño.

Para ver todos los estilos disponibles, haga clic en Más .

Eliminar un informe de gráfi co dinámico

Seleccione el informe de gráfi co dinámico. 1.

Presione2. [SUPR].

Crear una segmentación de datos en grafi co dinámico existente

Haga clic en el grafi co dinámico.1.

De esta forma, se mostrarán las Herramientas de tabla dinámica y se agregarán las pestañas 2.Diseño, Presentación, Formato y Analizar.

En el grupo 3. Datos de la pestaña Analizar, haga clic en Insertar Segmentación de datos.

Apre

ndo E

xcel 2010 In

term

edio


En el cuadro de diálogo 4. Insertar Segmentación de datos,active la casilla de verifi cación de los campos del grafi co dinámico para los cuales desea crear una segmentación de datos. Para este ejemplo activar las casillas Vendedor y Mes.


Se mostrará una segmentación de datos para cada campo que 6.haya seleccionado

En cada segmentación de datos, haga clic en los elementos en los cuales desea aplicar el 7.fi ltro.

Para seleccionar más de un elemento, mantenga presionada la tecla 8. CTRL y luego, haga clic en los elementos en los que desea aplicar el fi ltro.

CONVERTIR TEXTO EN COLUMNAS

Utilice el asistente para convertir texto en columnas para separar el contenido de celdas simples, como nombres y apellidos, en columnas distintas. Por ejemplo:

Nombre completo Nombre Apellido

Antonio Bermejo Antonio BermejoAlmudena Benito Almudena Benito

Según los datos, puede dividir el contenido de las celdas en función de un delimitador, como un espacio o una coma, o en función de la ubicación específi ca de un salto de columna en los datos.


Apre

ndo E

xcel

2010 I

nte

rmedio

64

Para desarrollar los ejemplos demostrativos de esta sección, se empleará la Hoja1 del archivo BASE DE DATOS Ejemplos.xlsx

Dividir el contenido en función de un delimitador

Utilice este método si los nombres tienen un formato delimitado, como “Nombre Apellido” (donde el espacio entre Nombre y Apellido es el delimitador) o “Apellido, Nombre” (donde la coma es el delimitador).

Por ejemplo, para dividir contenido delimitado por espacios, realice los pasos siguientes:

Seleccione el rango de datos que desea convertir. Para este ejemplo, emplee el rango 1. A4:A20de la Hoja1.

En la fi cha 2. Datos, en el grupo Herramientas de datos, haga clic en Texto en columnas.

En el paso 1 del asistente para convertir texto en columnas, haga clic en 3. Delimitados y luego en Siguiente.

En el paso 2, active la casilla de verifi cación 4. Espacio y desactive las demás casillas de Separadores.

Apre

ndo E

xcel 2010 In

term

edio


En el cuadro Vista previa de los datos se muestra el nombre y el apellido en dos columnas distintas.

Haga clic en 5. Siguiente.

En el paso 3, si desea establecer un formato 6.para los datos de cada columna, que sea diferente al General (predeterminado); entonces tendrá que seleccionar cada columna en el cuadro Vista previa de los datos y luego elegir una opción dentro del área Formato de los datos en columnas.Para este ejemplo, se mantendrán todos los formatos en la opción General.

Si desea insertar el contenido separado en 7.las columnas en una nueva ubicación, haga clic dentro del cuadro Destino y seleccione o escriba la referencia de la celda inicial del rango destino (F4, en este ejemplo).

Importante: si no especifi ca un nuevo destino para las nuevas columnas, los datos divididos reemplazarán los datos originales.

Haga clic en 8. Finalizar y reemplace el contenido de las celdas destino. Obtendrás tres columnas de datos.


Apre

ndo E

xcel

2010 I

nte

rmedio

66

QUITAR DATOS DUPLICADOS

Cuando quita valores duplicados, solo se afecta a los valores del rango de celdas o tabla. No se modifi ca ni se mueve ningún otro valor fuera del rango de celdas o tabla.

Prec

aución Debido a que borra los datos de manera permanente, es buena idea copiar el

rango original de celdas o tabla en otra hoja de cálculo o libro antes de quitar los valores duplicados.

Para desarrollar los ejemplos demostrativos de esta sección, se empleará la Hoja2 del archivo BASE DE DATOS Ejemplos.xlsx.

Seleccione el rango de celdas o asegúrese de que la celda activa se encuentra en una tabla. 1.Para este ejemplo, emplee el rango A3:I22 de la Hoja2.

En la fi cha 2. Datos, en el grupo Herramientas de datos, haga clic en Quitar duplicados.

Seleccione la columna que posea valores duplicados (3. Contrato en este ejemplo). Para ello, puede realizar uno o varios de los procedimientos siguientes:

En Columnas, seleccione una o más columnas. Para seleccionar rápidamente todas las columnas, haga clic en Seleccionar todo.Para borrar rápidamente todas las columnas, haga clic en Anular selección.

Si el rango de celdas o tabla contiene muchas columnas y solo desea seleccionar algunas, le será más fácil hacer clic en Anular selección y, a continuación, en Columnas, seleccionar dichas columnas.

Apre

ndo E

xcel 2010 In

term

edio


Haga clic en 4. Aceptar.Aparece un mensaje indicándole cuántos valores duplicados se han quitado y cuántos valores únicos permanecen o si no se han quitado valores duplicados.

Haga clic nuevamente en 5. Aceptar para terminar la operación.

VALIDAR DATOS

En muchas hojas de cálculo que cree, los usuarios escribirán datos para obtener los resultados y cálculos que deseen. Asegurar la entrada válida de datos es una tarea importante. Puede que desee restringir la entrada de datos a un rango determinado de fechas, limitar las opciones empleando una lista o asegurarse de que solo se ingresen números enteros positivos.

También, es esencial proporcionar una ayuda inmediata para dar instrucciones a los usuarios y mensajes claros cuando se escriban datos no válidos para hacer que el proceso de entrada de datos se lleve a cabo sin problemas.

Para desarrollar los ejemplos demostrativos de esta sección, se empleará la Hoja3 del archivo BASE DE DATOS Ejemplos.xlsx

Crear una lista desplegable de un rango de celdas

Para facilitar la entrada de datos, o para limitar las entradas a determinados elementos que defi ne el usuario, puede crear una lista desplegable de entradas válidas que se compila a partir de las celdas de otra parte de la hoja de cálculo. Cuando se crea una lista desplegable para una celda, se muestra una fl echa en dicha celda. Para escribir información en esta celda, haga clic en la fl echa y, a continuación, en la entrada que deseeEste ejemplo consiste en crear una lista desplegable para validar el ingreso de la columna E (Departamento) de la Hoja3.


Apre

ndo E

xcel

2010 I

nte

rmedio

68

Para crear una lista desplegable a partir de un rango de celdas realice los pasos siguientes:

Cear una lista de entradas válidas para la lista desplegable. Para ello, escriba las entradas 1.en una fi la o columna única sin celdas en blanco. Por ejemplo:

Si desea utilizar otra hoja de cálculo, escriba la lista en dicha 2.hoja de cálculo y, a continuación, defi na un nombre para la lista.

Seleccione la celda o rango donde desee crear la lista 3.desplegable. Para este ejemplo, emplee el rango E4:E47.

En la fi cha 4. Datos, en el grupo Herramientas de datos,haga clic en Validación de datos.

Se muestra el cuadro de diálogo Validación de datos.

Haga clic en la fi cha 5. Confi guración.

En el cuadro 6. Permitir, haga clic en Lista.

Para especifi car la ubicación de la lista de entradas válidas, siga uno de los procedimientos 7.siguientes:

Si la lista está en la hoja de cálculo actual, escriba una referencia a la lista en el cuadro •Origen.Si la lista está en otra hoja de cálculo, escriba el nombre defi nido para la lista en el cuadro •Origen.

En ambos casos, asegúrese de que la referencia o el nombre está precedido del signo igual (=). Por ejemplo, escriba =Departamentos.

Asegúrese de que esté activada la casilla de verifi cación 8. Celda con lista desplegable.

Para especifi car si la celda se puede dejar en blanco, active o desactive la casilla de verifi cación 9.Omitir blancos.

Otra opción es mostrar un mensaje de entrada cuando se haga clic en la celda. Proceda para 10.ello así:

Apre

ndo E

xcel 2010 In

term

edio


Haga clic en la fi cha a. Mensaje de entrada.Asegúrese de que la casilla de verifi cación b. Mostrar mensaje de entrada al selec-cionar la celda está activada. Escriba el título y el texto del mensaje c.(máximo 225 caracteres).

11. Especifi que cómo desea que Microsoft Offi ce Excel responda cuando se especifi quen datos no válidos.

Haga clic en la fi cha a. Mensaje de error y asegúrese de que esté activada la casilla de verifi cación Mostrar mensaje de error si se escriben datos no válidos. Seleccione una de las siguientes opciones en el cuadro b. Estilo:

Para mostrar un mensaje informativo que no evite la especifi cación de datos no válidos, •haga clic en Información.Para mostrar un mensaje de advertencia que no evite la especifi cación de datos no •válidos, haga clic en Advertencia.Para evitar la especifi cación de datos no válidos, haga clic en • Grave.

Escriba el título y el texto del mensaje (máximo 225 caracteres). Si no escribe un título c.o un texto, de forma predeterminada el título será Microsoft Excel y el mensaje será: Valor no válido. El usuario solo puede escribir ciertos valores en esta celda.

12. Haga clic en Aceptar para completar la operación.

Not

as

Si el ancho de la lista desplegable está determinado por el ancho de la celda que tiene la validación de datos, puede que tenga que ajustar el ancho de esa celda para evitar truncar el ancho de las entradas válidas que son mayores que el ancho de la lista desplegable.

El número máximo de entradas que puede tener en una lista desplegable es 32.767.

Si la lista de validación está en otra hoja de cálculo y desea evitar que los usuarios la vean o realicen cambios en ella, piense en ocultar y proteger dicha hoja de cálculo.

l d il


Apre

ndo E

xcel

2010 I

nte

rmedio

70

Evitar la entrada de datos no válidos en una hoja de cálculo

Este ejemplo consiste en confi gurar el ingreso de los valores válidos para el rango D4:D47(Fecha_Ingreso) de la Hoja3, de tal manera que no permita valores posteriores a la fecha actual.

Para ello, realice los pasos siguientes:

Seleccione las celdas a validar, para este ejemplo 1. D4:D47en la Hoja3.

En la fi cha 2. Datos, en el grupo Herramientas de datos,haga clic en Validación de datos.

Se muestra el cuadro de diálogo Validación de datos.

Haga clic en el separador 3. Confi guración.

Para especifi car el tipo de validación que desea, realice una de las siguientes acciones: 4.

En el cuadro Permitir, seleccione a. Fecha.En el cuadro b. Datos, seleccione el tipo de restricción que desee. Por ejemplo, para permitir las fechas anteriores a un día determinado, seleccione menor o igual que.Indique la c. fecha fi nal que desee permitir. Por ejemplo escriba, =HOY().

5. Para especifi car cómo desea administrar los valores en blanco (nulos), active o desactive la casilla de verifi cación Omitir blancos.

Not

a

Si los valores permitidos se basan en un rango de celdas con un nombre defi nido y existe una celda en blanco en cualquier lugar del rango, la confi guración de la casilla de verifi cación Omitir blancos permite escribir cualquier valor en la celda validada. Lo mismo puede decirse de las celdas a las que se haga referencia mediante fórmulas de validación: si una celda está en blanco, la confi guración de la casilla de verifi cación Omitir blancos permite escribir cualquier valor en la celda validada.

6. Si desea, puede activar el separador Mensaje de entrada para mostrar un mensaje de entrada cuando se haga clic en la celda. (Opcional)

Apre

ndo E

xcel 2010 In

term

edio


7. Haga clic en el separador Mensaje de error para especifi car cómo desea que Microsoft Offi ce Excel responda cuando se intente ingresar datos no válidos.

8. Haga clic en Aceptar para completar la operación.

9. Pruebe la validación de datos para asegurarse de que funciona correctamente. Trate de escribir datos válidos y no válidos en las celdas para asegurarse de que la confi guración

funciona como pretende y que los mensajes están apareciendo como espera.

Eliminar la validación de un rango de celdas

Para ello, debe realizar los siguientes pasos:

Seleccione la celda o rango que posee la confi guración de validación que desea eliminar.1.

En la fi cha 2. Datos, en el grupo Herramientas de datos, haga clic en Validación de datos.

Haga clic en el botón 3. Borrar todos, y luego en Aceptar.


Apre

ndo E

xcel

2010 I

nte

rmedio

72

OBTENER DATOS EXTERNOS

Si desea importar datos de una fuente externa, necesita utilizar la herramienta de Excel para obtener datos externos, denominada Microsoft Query.

¿Qué es Microsoft Query?

Es un programa que permite incorporar datos de orígenes externos a otros programas de Microsoft Offi ce, especialmente a Microsoft Excel.

Si utiliza Query para recuperar información de las bases de datos y de los archivos corporativos, entonces:

No será necesario que vuelva a escribir en Excel los datos que desee analizar. •También, podrá actualizar los informes y resúmenes de Excel automáticamente desde la •base de datos de origen inicial, siempre que la base de datos se actualice con información nueva.

Tipos de bases de datos a los que se puede obtener acceso

Es posible recuperar datos de varios tipos de bases de datos, incluidos Microsoft Access, Dbase, FoxPro, Microsoft SQL Server y los servicios OLAP de Microsoft SQL Server. También, puede recuperar datos de las listas de Excel y de archivos de texto (Csv, Txt).

¿De qué manera obtiene Excel los datos de una base de datos?

La siguiente ilustración grafi ca la respuesta. Suponga que usted tiene datos en un archivo de formato diferente a Excel, en el cual se encuentran las ventas por Región y las ventas por Categoría. Asuma que esta es la base de datos de la compañía. Por lo tanto, la cantidad de transacciones almacenadas es bastante numerosa.

Importar datos

El siguiente ejemplo consiste en importar un archivo de texto denominado Listado_CSV, hacia una hoja existente del archivo Importar Datos Ejemplo1.xls. Para ello, deberá abrir este segundo archivo y luego realizar los pasos siguientes:

Haga clic en la celda a partir de la cual se desea poner los datos del archivo de texto. Para 1.este ejemplo, ubique el puntero de celda en Hoja2!A3.

Apre

ndo E

xcel 2010 In

term

edio


Ubíquese en la fi cha 2. Datos, haga clic sobre el grupo Obtener datos externosy, a continuación, haga clic en el comando Desde texto.

Observe queapareceunaviso deseguridad,3.donde se pregunta si se confía en el origen de dicho archivo. Haga clic en Aceptar.

En la lista 4. Buscar en, elija y haga clic en el archivo de texto que desee importar como rango de datos externos, luego haga clic en Importar. Para este ejemplo, debe abrir el archivo Listado_CSV.

Al abrir el archivo tipo texto, automáticamente se ejecutará el 5. Asistente para importar texto. Ahora deberá seguir las instrucciones para especifi car cómo desea dividir el texto en columnas. Estando en el paso 1 de dicho asistente, deberá elegir el tipo de archivo. Para este caso, mantenga la opción por efecto Delimitados (porque sus datos originales almacenan campos separados por un caracter específi co) y, a continuación, haga clic en el botón Siguiente.


Apre

ndo E

xcel

2010 I

nte

rmedio

74

Continuando con el paso 2, del 6. asistente para importar texto, debe elegir el tipo de separador apropiado para los datos. Para un archivo de texto con formato *.CSV, active la casilla correspondiente al separador Coma. Observará, entonces, cómo cambia el texto en el panel de vista previa, continúe haciendo clic en el botón Siguiente.

En el paso 3 del asistente, haga clic en cada columna y elija a continuación el 7. Formato para los datos importados. Tenga en cuenta que, al elegir General, se convierten los valores numéricos en números, los valores de fecha en fechas y todos los demás valores en texto.

Haga clic en el botón 8. Finalizar.

En el cuadro de diálogo 9. Importar datos, siga el primero de estos procedimientos:

Para devolver los datos a la ubicación seleccionada, haga clic en Hoja de cálculo existente y, a continuación, en Aceptar.Para devolver los datos a una hoja de cálculo nueva, haga clic en Nueva Hoja de cálculo y, a continuación, haga clic en Aceptar. Microsoft Excel agregará una nueva hoja de cálculo al libro y colocará de forma automática el rango de datos externos en la esquina superior izquierda de la nueva hoja de cálculo.

Apre

ndo E

xcel 2010 In

term

edio


Como resultado de la operación anterior, se obtendrán los siguientes datos importados:

ObservacionesPara evitar que los datos externos reemplacen a los datos existentes, cuando se importa en una hoja de cálculo existente, asegúrese de que la hoja no contiene datos debajo, ni a la derecha de la celda inicial para la importación.

ObPaunde


Apre

ndo E

xcel

2010 I

nte

rmedio

76

Ejercicios Propuestos Nº 1

Abra el archivo FILTROS AVANZADOS Ejercicios.xlsx, resuelva los ejercicios indicados en la hoja respectiva y grabe sus resultados con el nombre Filtros avanzados ejercicios resueltos.xlsx.

Ejercicio 1. Aplique Filtros avanzados para realizar las operaciones siguientes:

HOJA1, Filtre la lista de la hoja Cobranzas, copiando a partir de la celda A9 de la Hoja1, a.los Clientes cuyos nombres no contengan la palabra inmobiliaria, que además posean una Deuda total entre 2.500 y 5.000.

HOJA2, Filtre la lista de la hoja Cobranzas, copiando a partir de la celda B9 de la Hoja2, b. los datos cuyo tercer caracter del Código sea 5 y que además sean Clientes Corporativos. El resultado debe mostrar solo las columnas Código, Cliente, Deuda total y Cliente Corporativo.

HOJA3, Filtre la lista de la hoja Cobranzas, copiando a partir de la celda A9 de la Hoja3, c.datos que correspondan a las TARIFAS que empiezan con M y que no pertenezcan a los distritos de Lince y San Isidro; y que además no sean Clientes Corporativos.

Abra el archivo BASE DE DATOS Ejercicios.xlsx, resuelva los ejercicios indicados en la hoja respectiva y grabe sus resultados con el nombre Base de datos ejercicios resueltos.xlsx.

Ejercicio 2. En la HOJA1, aplique Subtotales para calcular la suma de las ventas y comisiones para cada número de pedido.

Ejercicio 3. En la HOJA2, aplique al rango A3:F35 el proceso para Quitar duplicados considerando la columna Nº Pedido.

Ejercicio 4. En la HOJA2, aplique al rango B4:F35 el proceso para Validar los datos de la columna Fecha, de tal manera que solo permita ingresar fechas pertenecientes al año 2008.

Ejercicio 5. HOJA3, empleando los datos del rango A3:H83 cree una Tabla Dinámica en una hoja nueva con las características que se indican y según la muestra siguiente:

El campo fi ltro de informe debe mostrar el distrito de San Isidro.•El campo rótulo de fi la debe mostrar solo las fechas en el periodo Trimestre 3.•En el área de valores muestre la suma de Deuda con formato S/. con dos decimales.•

Documents

Material excel 2010 intermedio