• Author / Uploaded
• karen

• Categories
• Hoja de cálculo
• Tabla (Base de datos)
• Fórmula
• Microsoft Excel
• Argumento

Citation preview

     



Junio 2012





EXCEL 2010 NIVEL INTERMEDIO   

Contenido EXCEL 2010 .......................................................................................................................................... 1  VALIDACION DE DATOS ................................................................................................................... 2  CONSULTAV ..................................................................................................................................... 4  Sintaxis ........................................................................................................................................ 4  FUNCION SI ...................................................................................................................................... 6  Sintaxis ........................................................................................................................................ 6  SUBTOTALES .................................................................................................................................... 8  FILTROS .......................................................................................................................................... 10  FILTROS AVANZADOS .................................................................................................................... 11  Información general .................................................................................................................. 11  Operadores de comparación ..................................................................................................... 12  Explicación con un ejemplo: ...................................................................................................... 20  TABLAS DINÁMICAS ....................................................................................................................... 21  Qué es un informe de tabla dinámica ....................................................................................... 21  Formas de trabajar con un informe de tabla dinámica ............................................................. 23  Qué es un informe de gráfico dinámico .................................................................................... 24  Realizando una tabla Dinámica ................................................................................................. 25               

MAE. Oscar Matute  

1

VALIDACION DE DATOS    La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios  pueden escribir en una celda. Por ejemplo, es posible que desee restringir la entrada de  datos a un intervalo determinado de fechas, limitar las opciones con una lista o asegurarse  de que sólo se escriben números enteros positivos.  En  este  artículo  se  describe  el  funcionamiento  de  la  validación  de  datos  en  Excel  y  las  diferentes  técnicas  de  validación  de  datos  existentes.  No  analiza  la  protección  de  celdas  que es una característica que permite "bloquear" u ocultar ciertas celdas de una hoja de  cálculo para que no se puedan editar ni sobrescribir.  La  validación  de  datos  nos  permite  asegurarnos  de  que  los  valores  que  se  introducen en las celdas son los adecuados; pudiendo incluso mostrar un mensaje de  error o aviso si nos equivocamos.  Para aplicar una validación a una celda.  ‐ Seleccionamos la celda que queremos validar.  ‐ Accedemos a la pestaña Datos y pulsamos Validación de datos. En  él podremos escoger remarcar los errores con círculos o borrar estos  círculos  de  validación.  Pero  nos  vamos  a  centrar  en  la  opción  Validación de datos.... 

     Nos  aparece  un  cuadro  de  diálogo  Validación  de  datos  como  el  que  vemos  en  la  imagen donde podemos elegir entre varios tipos de validaciones. 

MAE. Oscar Matute  

2

  En  la  sección  Criterio  de  validación  indicamos  la  condición  para  que  el  datos  sea  correcto.  Dentro de Permitir podemos encontrar Cualquier valor, Número entero, Decimal,  Lista,  Fecha,  Hora,  Longitud  de  texto  y  personalizada.  Por  ejemplo  si  elegimos  Número  entero,  Excel  sólo  permitirá  números  enteros  en  esa  celda,  si  el  usuario  intenta escribir un número decimal, se producirá un error.  Podemos  restringir  más  los  valores  permitidos  en  la  celda  con  la  opción  Datos,  donde, por ejemplo, podemos indicar que los valores estén entre 2 y 8.  Si en la opción Permitir: elegimos Lista, podremos escribir una lista de valores para  que  el  usuario  pueda  escoger  un  valor  de  los  disponibles  en  la  lista.  En  el  recuadro  que aparecerá, Origen: podremos escribir los distintos valores separados por ; (punto  y coma) para que aparezcan en forma de lista.   En la pestaña Mensaje de entrada podemos introducir un mensaje que se muestre  al  acceder  a  la  celda.  Este  mensaje  sirve  para  informar  de  qué  tipos  de  datos  son  considerados válidos para esa celda.   En  la  pestaña  Mensaje  de  error  podemos  escribir  el  mensaje  de  error  que  queremos se le muestre al usuario cuando introduzca en la celda un valor incorrecto.     MAE. Oscar Matute  

3

CONSULTAV Puede usar la función CONSULTAV para buscar la primera columna de un rango de celdas  y,  a  continuación,  devolver  un  valor  de  cualquier  celda  de  la  misma  fila  del  rango.  Por  ejemplo,  si  tiene  una  lista  de  empleados  contenida  en  el  rango  A2:C10,  los  números  de  identificación  de  los  empleados  se  almacenan  en  la  primera  columna  del  rango,  como  muestra la siguiente ilustración. 

  Si  conoce  el  número  de  identificación  del  empleado,  puede  usar  la  función  CONSULTAV  para devolver el departamento o el nombre de dicho empleado. Para obtener el nombre  del  empleado  número  38,  puede  usar  la  fórmula  =CONSULTAV(38;  A2:C10;  3;  FALSO).  Esta fórmula busca el valor 38 en la primera columna del rango A2:C10 y, a continuación,  devuelve el valor contenido en la tercera columna del rango y en la misma fila que el valor  de búsqueda ("Axel Delgado").  La  V  de  CONSULTAV  significa  vertical.  Use  CONSULTAV  en  lugar  de  CONSULTAH  si  los  valores de comparación se encuentran en una columna situada a la izquierda de los datos  que desea buscar.  Sintaxis

CONSULTAV(valor_buscado, matriz_buscar_en, indicador_columnas, [ordenado])  La sintaxis de la función CONSULTAV tiene los siguientes argumentos:  



valor_buscado    Obligatorio. Valor que se va a buscar en la primera columna de la tabla o  rango. El argumento valor_buscado puede ser un valor o una referencia. Si el valor que  proporcione  para  el  argumento  valor_buscado  es  inferior  al  menor  valor  de  la  primera  columna del argumento matriz_buscar_en, CONSULTAV devuelve al valor de error #N/A.  matriz_buscar_en    Obligatorio.  El  rango  de  celdas  que  contiene  los  datos.  Puede  usar  una referencia a un rango (por ejemplo, A2:D8) o un nombre de rango. Los valores de la  primera  columna  de  matriz_buscar_en  son  los  valores  que  busca  valor_buscado.  Estos  MAE. Oscar Matute  

4



valores  pueden  ser  texto,  números  o  valores  lógicos.  Las  mayúsculas  y  minúsculas  del  texto son equivalentes.   indicador_columnas    Obligatorio.  Un  número  de  columna  del  argumento  matriz_buscar_en  desde  la  cual  debe  devolverse  el  valor  coincidente.  Si  el  argumento  indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del  argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve  el valor de la segunda columna de matriz_buscar_en y así sucesivamente.  Si el argumento indicador_columnas es:   



Inferior a 1, CONSULTAV devuelve al valor de error #¡VALOR!.  Superior al número de columnas de matriz_buscar_en, CONSULTAV devuelve el valor  de error #¡REF!. 

ordenado    Opcional.  Un  valor  lógico  que  especifica  si  CONSULTAV  va  a  buscar  una  coincidencia exacta o aproximada:  Si  ordenado  se  omite  o  es  VERDADERO,  se  devolverá  una  coincidencia  exacta  o  aproximada.  Si  no  encuentra  ninguna  coincidencia  exacta,  devolverá  el  siguiente  valor más alto inferior a valor_buscado.    IMPORTANTE    Si  ordenado  se  omite  o  es  VERDADERO,  los  valores  de  la  primera  columna  de  matriz_buscar_en  deben  estar  clasificados  según  un  criterio  de  ordenación  ascendente;  en  caso  contrario,  es  posible  que  CONSULTAV no devuelva el valor correcto.  Para obtener más información, vea Ordenar datos en un rango o tabla.  Si ordenado es FALSO, no es necesario ordenar los valores de la primera columna  de matriz_buscar_en.  

Si  el  argumento  ordenado  es  FALSO,  CONSULTAV  sólo  buscará  una  coincidencia  exacta.  Si  hay  dos  o  más  valores  en  la  primera  columna  de  matriz_buscar_en  que  coinciden con el argumento valor_búsqueda, se usará el primer valor encontrado. Si  no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A. 

          MAE. Oscar Matute  

5

FUNCION SI Devuelve  un  valor  si  la  condición  especificada  es  VERDADERO  y  otro  valor  si  dicho  argumento es FALSO.  Utilice SI para realizar pruebas condicionales en valores y fórmulas.  Sintaxis

SI(prueba_lógica, valor_si_verdadero, valor_si_falso)  Prueba_lógica     es cualquier valor o expresión que pueda evaluarse como VERDADERO o  FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a  100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como FALSO.  Este argumento puede utilizar cualquier operador de comparación.  Valor_si_verdadero      es  el  valor  que  se  devuelve  si  el  argumento  prueba_lógica  es  VERDADERO.  Por  ejemplo,  si  este  argumento  es  la  cadena  de  texto  "Dentro  de  presupuesto"  y  el  argumento  prueba_lógica  se  evalúa  como  VERDADERO,  la  función  SI  muestra el texto "Dentro de presupuesto". Si el argumento prueba_lógica es VERDADERO  y  el  argumento  valor_si_verdadero  está  en  blanco,  este  argumento  devuelve  0  (cero).  Para  mostrar  la  palabra  VERDADERO,  utilice  el  valor  lógico  VERDADERO  para  este  argumento. Valor_si_verdadero puede ser otra fórmula.  Valor_si_falso      es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por  ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento  prueba_lógica  se  evalúa  como  FALSO,  la  función  SI  muestra  el  texto  "Presupuesto  excedido".  Si  el  argumento  prueba_lógica  es  FALSO  y  se  omite  valor_si_falso,  (es  decir,  después de valor_si_verdadero no hay ninguna coma), se devuelve el valor lógico FALSO.  Si  prueba_lógica  es  FALSO  y  valor_si_falso  está  en  blanco  (es  decir,  después  de  valor_si_verdadero hay una coma seguida por el paréntesis de cierre), se devuelve el valor  0 (cero). Valor_si_falso puede ser otra fórmula.  Observaciones  

 

Es  posible  anidar  hasta  64  funciones  SI  como  argumentos  valor_si_verdadero  y  valor_si_falso  para  crear  pruebas  más  complicadas.  (Vea  el  ejemplo  3  para  ver  una  muestra  de  funciones  SI  anidadas).  Como  alternativa,  para  comprobar  muchas  condiciones, plantéese usar las funciones BUSCAR, BUSCARV o BUSCARH. (Vea el ejemplo  4 para obtener una muestra de la función BUSCARH.)  Cuando  los  argumentos  valor_si_verdadero  y  valor_si_falso  se  evalúan,  la  función  SI  devuelve el valor devuelto por la ejecución de las instrucciones.  Si  uno  de  los  argumentos  de  la  función  SI  es  una  matriz,  cada  elemento  de  la  matriz  se  evaluará cuando se ejecute la instrucción SI.  MAE. Oscar Matute  

6



Microsoft Excel proporciona funciones adicionales que se pueden utilizar para analizar los  datos en función de una condición. Por ejemplo, para contar el número de veces que una  cadena de texto o un número aparecen dentro de un rango de celdas, utilice las funciones  de hoja de cálculo CONTAR.SI y CONTAR.SI.CONJUNTO. Para calcular una suma basándose  en  una  cadena  de  texto  o  un  número  de  un  rango,  utilice  las  funciones  SUMAR.SI  y  SUMAR.SI.CONJUNTO.  Ejemplo 1  Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco.  Cómo copiar un ejemplo  A  Datos  50     

Fórmula 

1 

=SI(A2B2;"Presupuesto  excedido";"Aceptar") 

Comprueba  si  la  primera  fila  sobrepasa  el  presupuesto (Presupuesto excedido) 

=SI(A3>B3;"Presupuesto  excedido";"Aceptar") 

Comprueba  si  la  segunda  fila  sobrepasa  el  presupuesto (Aceptar) 

 

         

SUBTOTALES  

Con  Excel  2010  podemos  hacer  que  se  muestren los subtotales de una columna, para  ello ordenaremos la lista por la columna cuyos  subtotales desee calcular.   Por  ejemplo,  para  resumir  las  unidades  vendidas por cada vendedor y el importe de las  ventas,  ordenaremos  la  lista  por  la  columna  Vendedor, como vemos en la imagen.  

MAE. Oscar Matute  

8

Después  situaremos  el  cursor  en  una  celda  de  la  tabla  de  datos  y  haremos  click  en  el  icono  Subtotal de la pestaña Datos.   En  Para  cada  cambio  en  seleccionaremos  la  columna  que  contenga  los  grupos  cuyos  subtotales  deseemos  calcular  (la  misma  columna por la que ordenamos la lista).   En  Usar  función  seleccionaremos  la  función  que deseemos usar para calcular los subtotales  (Suma, Promedio, etc.).   En  Agregar  subtotal  a  activaremos  las  casillas  de  verificación  correspondientes  a  las  columnas  que  contengan  los  valores  cuyos  subtotales queramos extraer.  

 

Con los iconos  y  podremos ampliar y contraer la lista, haciendo que se muestren o no  cada una de las filas, además de los subtotales. 

  También  es  posible  anidar  o  insertar  subtotales  de  grupos  más  pequeños  dentro  de  los  grupos de subtotales existentes.  Para eliminar los subtotales tenemos que hacer click con el botón derecho en una celda  de  la  tabla  de  datos  y  nuevamente  en  el  icono  Subtotal:  en  el  cuadro  de  diálogo  pincharemos en el botón Quitar todos.      MAE. Oscar Matute  

9

FILTROS  Los filtros de Microsoft Excel 2010 podemos son usados para mostrar únicamente los que  contengan los valores elegidos.  El  filtro  se  puede  crear  seleccionando  un  rango de  celdas  o  seleccionando  una  de  las  que  componen la tabla de datos (el encabezado, por  ejemplo),  y  haciendo  después  click  en  el  icono  Filtro  de  la  pestaña  Datos:  veremos  una  en  cada columna de la fila de encabezado.   Posteriormente,  para  establecer  una  condición tenemos que hacer click en la flecha para que se  muestren  los  valores  que  podemos  elegir  (que  serán  los  que  haya  en  toda  esa  columna)  y  seleccionar uno de ellos.  

 

También podemos realizar el filtrado por más de  una  condición,  seleccionando  la  opcion  Filtro  personalizado  que  se  halla  dentro  del  menú  Filtros de texto.  

En este ejemplo se mostrarían sólo las filas cuyo nombre de vendedor comience por J o M.  Para eliminar el filtro volveremos a hacer click click en el icono Filtro de la pestaña Datos.  

MAE. Oscar Matute  

10

       

FILTROS AVANZADOS Filtrar utilizando criterios avanzados  Corresponde a: Microsoft Excel 2010 Si los datos que desea filtrar requieren criterios complejos (como Tipo = "Alimentos" O Vendedor = "Davolio"), puede usar el cuadro de diálogo Filtro avanzado.

FILTRO AVANZADO

EJEMPLO

Información general Varios criterios, una columna, cualquier criterio verdadero

Vendedor = "Davolio" O Vendedor = "Buchanan" Varios criterios, varias columnas, todos los criterios verdaderos Tipo = "Alimentos" Y Ventas > 1000 Varios criterios, varias columnas, cualquier criterio verdadero Tipo = "Alimentos" O Vendedor = "Buchanan" Varios conjuntos de criterios, una columna en todos los (Ventas > 6000 Y Ventas < 6500 ) O conjuntos (Ventas < 500) Varios conjuntos de criterios, varias columnas en cada ( Vendedor = "Davolio" Y Ventas conjunto >3000) O (Vendedor = "Buchanan" Y Ventas > 1500) ) Criterios de carácter comodín Vendedor = un nombre con la letra "u" en segundo lugar Texto que coincide con una búsqueda que distingue entre Tipo = una coincidencia exacta de mayúsculas y minúsculas (fórmula) "Alimentos" Un valor en una columna mayor que el promedio de todos los Ventas > el promedio de todas las valores de dicha columna (fórmula) ventas

  Información general El comando Avanzadas funciona de forma diferente del comando Filtrar en varios aspectos importantes.

MAE. Oscar Matute  

11

 

Muestra el cuadro de diálogo Filtro avanzado en vez del menú de filtro automático. 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 filtrar. Microsoft Office Excel usa el rango de criterios independiente del cuadro de diálogo Filtro avanzado como el origen de los criterios avanzados. Ejemplo: rango de criterios (A1:C4) y rango de la lista (A6:C10) que se usan en los siguientes procedimientos Posiblemente sea más sencillo comprender el ejemplo si lo copia en una hoja de cálculo en blanco.

A

B

C

1 Tipo

VendedorVentas

6

Tipo

VendedorVentas

7

Bebidas Suyama 5122 $

8

Carnes

9

AlimentosBuchanan 6328 $

2 3 4 5

Davolio

10 AlimentosDavolio

450 $

6544 $

 

Operadores de comparación Se pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores usando estos operadores, el resultado es un valor lógico: VERDADERO o FALSO.

OPERADOR DE COMPARACIÓN = (signo igual)

MAE. Oscar Matute  

SIGNIFICADO Igual a

EJEMPLO A1=B1

12

> (signo mayor que)

Mayor que

A1>B1

< (signo menor que)

Menor que

A1= (signo mayor o igual que)

Mayor o igual que

A1>=B1

1000

Haga clic en una celda del rango de la lista. Siguiendo el ejemplo, haga clic en cualquier celda del rango, A6:C10. En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Opciones avanzadas.

MAE. Oscar Matute  

17

5.

Siga uno de los siguientes procedimientos: Para filtrar el rango de la lista ocultando las filas que no cumplen los criterios, haga clic en Filtrar la lista sin moverla a otro lugar. Para filtrarlo copiando las filas que cumplen los criterios a otra área de la hoja de cálculo, haga clic en Copiar a otro lugar, después en la casilla Copiar a y, por último, en la esquina superior izquierda del área donde desea pegar las filas.

 

Sugerencia Al copiar filas filtradas en otra ubicación, se pueden especificar las columnas que se deben incluir en la operación de copia. Antes de filtrar, copie las etiquetas de columna de las columnas deseadas en la primera fila del área donde va a pegar las filas filtradas. Cuando filtre, escriba una referencia a las etiquetas de columna copiadas en el cuadro Copiar en. De este modo, las filas copiadas incluirán solo las columnas cuyas etiquetas se hayan copiado. 6.

En el cuadro Rango de criterios escriba la referencia, incluidos los rótulos de criterios. Siguiendo el ejemplo, escriba $A$1:$C$2. Para ocultar temporalmente el cuadro de diálogo Filtro avanzado mientras selecciona el rango de criterios, haga clic en Contraer diálogo

7.

.

Siguiendo el ejemplo, el resultado filtrado para el rango de la lista es:

A

B

C

6

Tipo

Vendedor

Ventas

9

Alimentos

Buchanan

6328 $

10

Alimentos

Davolio

6544 $

 

Varios criterios, varias columnas, cualquier criterio verdadero  Lógica booleana: 1.

(Tipo = "Alimentos" O Vendedor = "Buchanan")

Inserte al menos tres filas vacías sobre el rango de la lista que puede usarse como rango de criterios. El rango de criterios debe tener rótulos de columna. Compruebe que existe al menos una fila vacía entre los valores de criterios y el rango de la lista. Posiblemente sea más sencillo comprender el ejemplo si lo copia en una hoja de cálculo en blanco.

A

B

MAE. Oscar Matute  

C

18

1

Tipo

VendedorVentas

Tipo

VendedorVentas

2 3 4 5 6 Bebidas Suyama 5122 $

7 Carnes

Davolio

450 $

8 AlimentosBuchanan 6328 $

9 AlimentosDavolio

6544 $

10

2.

Para buscar filas que cumplen varios criterios en varias columnas en las que cualquier criterio puede ser verdadero, escriba los criterios en las diferentes columnas y filas del rango de criterios. Siguiendo el ejemplo, escriba:

A 1

Tipo

2

="=Alimentos"

3

3.

B Vendedor

C Ventas

="=Buchanan"

4.

Haga clic en una celda del rango de la lista. Siguiendo el ejemplo, haga clic en cualquier celda del rango de la lista, A6:C10. En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Opciones avanzadas.

5.

Siga uno de los siguientes procedimientos:

MAE. Oscar Matute  

19

Para filtrar el rango de la lista ocultando las filas que no cumplen los criterios, haga clic en Filtrar la lista sin moverla a otro lugar. Para filtrarlo copiando las filas que cumplen los criterios a otra área de la hoja de cálculo, haga clic en Copiar a otro lugar, después en la casilla Copiar a y, por último, en la esquina superior izquierda del área donde desea pegar las filas.

 

Sugerencia Al copiar filas filtradas en otra ubicación, se pueden especificar las columnas que se deben incluir en la operación de copia. Antes de filtrar, copie las etiquetas de columna de las columnas deseadas en la primera fila del área donde va a pegar las filas filtradas. Cuando filtre, escriba una referencia a las etiquetas de columna copiadas en el cuadro Copiar en. De este modo, las filas copiadas incluirán solo las columnas cuyas etiquetas se hayan copiado. 6.

En el cuadro Rango de criterios, escriba la referencia, incluidos los rótulos de criterios. Siguiendo el ejemplo, escriba $A$1:$B$3. Para ocultar temporalmente el cuadro de diálogo Filtro avanzado mientras selecciona el rango de criterios, haga clic en Contraer diálogo

7.

.

Siguiendo el ejemplo, el resultado filtrado para el rango de la lista es:

A

B

C

6

Tipo

Vendedor

Ventas

9

Alimentos

Buchanan

6328 $

10

Alimentos

Davolio

6544 $

  Explicación con un ejemplo:  

http://www.youtube.com/watch?v=sixOTbT5Bi8 

      MAE. Oscar Matute  

20

TABLAS DINÁMICAS Los informes de tabla dinámica se usan para resumir, analizar, explorar y presentar datos de resumen. Los informes de gráfico dinámico pueden ayudarlo a visualizar los datos de resumen de los informes de tabla dinámica para poder ver fácilmente las tendencias, los modelos y las comparaciones. Ambos informes permiten tomar decisiones fundamentadas sobre datos críticos de la empresa. En las siguientes secciones se proporciona información general sobre los informes de tabla dinámica y de gráfico dinámico. Sugerencia Puede encontrar los comandos Tabla dinámica y Gráfico dinámico en la Cinta de opciones (pestaña Insertar, grupo Tabla). Para obtener instrucciones sobre cómo crear una tabla dinámica o un gráfico dinámico, vea el tema de Inicio rápido sobre cómo crear un informe de tabla dinámica o crear o eliminar una tabla dinámica o un gráfico dinámico.

En este artículo   

Qué es un informe de tabla dinámica Formas de trabajar con un informe de tabla dinámica Qué es un informe de gráfico dinámico

 

Qué es un informe de tabla dinámica 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 detenidamente datos numéricos y responder a preguntas no anticipadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:      

Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para el usuario. Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y fórmulas personalizados. Expandir y contraer los niveles de datos para destacar los resultados y ver los detalles de los datos de resumen de las áreas de interés. Mover filas a columnas y columnas a filas para ver diferentes resúmenes de los datos de origen. Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder centrarse en la información que le interesa. Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.

MAE. Oscar Matute  

21

A menudo utiliza un informe de tabla dinámica cuando desea comparar totales relacionados, sobre todo si tiene una lista larga de números para sumar y desea realizar comparaciones distintas con cada número. En el informe de tabla dinámica mostrado a continuación, puede ver fácilmente cómo se comparan las ventas de golf del tercer trimestre en la celda F3 con las ventas de cualquier otro deporte o trimestre, o con las ventas totales.

Datos de origen; en este caso, de una hoja de cálculo Valores de origen del resumen del Trim3 de Golf en el informe de tabla dinámica Informe de tabla dinámica Resumen de los valores de origen en C2 y C8 desde los datos de origen

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 filas 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 fila de los datos de origen para la cual la columna Deporte contiene Golf y la columna Trimestre contiene Trimestre3. De forma predeterminada, los datos del área Valores resumen los datos de origen subyacentes en el informe de gráfico dinámico de la siguiente forma: los valores numéricos usan la función SUMA, y los valores de texto la función CONTAR. Para crear un informe de tabla dinámica, debe definir los datos de origen, especificar una ubicación en el libro y diseñar los campos. Para obtener más información, vea el tema sobre cómo crear o eliminar un informe de tabla dinámica o de gráfico dinámico e incluir datos dinámicos en un informe de tabla dinámica o de gráfico dinámico. MAE. Oscar Matute  

22

Formas de trabajar con un informe de tabla dinámica Después de crear el informe de tabla dinámica inicial definiendo los datos de origen, organizando los campos en la lista de campos de tabla dinámica y eligiendo un diseño inicial, podrá llevar a cabo las siguientes tareas en un informe de tabla dinámica: Explorar los datos   

de la siguiente manera:

Expandiendo y contrayendo los datos, y mostrando los detalles subyacentes de los valores. Ordenando, filtrando y agrupando los campos y elementos. Cambiando las funciones de resumen y agregando cálculos y fórmulas personalizados. Cambiar el diseño de formulario y la organización de campos

  

Cambiando el formato del informe de tabla dinámica: compacto, esquemático o tabular. Agregando, reorganizando y quitando campos. Cambiando el orden de los campos o elementos. Cambiar el diseño de las columnas, las filas y los subtotales

    

de la siguiente manera:

de la siguiente manera:

Activando o desactivando los encabezados de campo de las columnas y filas, o bien, mostrando u ocultando las líneas en blanco. Mostrando los subtotales encima o debajo de sus filas. Ajustando los anchos de columna al actualizar. Moviendo un campo de columna al área de fila o un campo de fila al área de columna. Combinando o separando las celdas de los elementos de fila y columna externos. Cambiar la presentación de las celdas en blanco y de los errores manera:

  

Cambiando la forma en que se muestran los errores y las celdas vacías. Cambiando la forma en que se muestran los elementos y rótulos sin datos. Mostrando u ocultando las líneas en blanco. Cambiar el formato

   

de la siguiente

de la siguiente manera:

Aplicando formato manual y condicional a las celdas y los rangos. Cambiando el estilo de formato de tabla dinámica global. Cambiando el formato numérico de los campos. Incluyendo el formato de servidor procesamiento analítico en línea (OLAP).

 

MAE. Oscar Matute  

23

Qué es un informe de gráfico dinámico 



Un informe de gráfico dinámico representa gráficamente los datos de un informe de tabla dinámica, que en este caso se denomina el informe de tabla dinámica asociado. Al igual que un informe de tabla dinámica, un informe de gráfico dinámico es interactivo. Cuando se crea un informe de gráfico dinámico, se muestran los filtros del informe de gráfico dinámico en el área del gráfico para poder ordenar y filtrar los datos subyacentes del informe de gráfico dinámico. Los cambios que realice en el diseño de campo y los datos del informe de tabla dinámica asociado se reflejarán inmediatamente en el informe de gráfico dinámico. Un informe de gráfico dinámico muestra series de datos, categorías, marcadores de datos y ejes al igual que los gráficos estándar. Puede cambiar asimismo el tipo de gráfico y otras opciones como los títulos, la ubicación de la leyenda, las etiquetas de datos y la ubicación del gráfico.





Informe de gráfico dinámico de las ventas deportivas por trimestre

 



Puede crear un informe de gráfico dinámico cuando primero crea un informe de tabla dinámica, o bien puede crear un informe de gráfico dinámico desde un informe de tabla dinámica existente. Para obtener más información, vea el tema sobre cómo crear o eliminar un informe de tabla dinámica o de gráfico dinámico e incluir datos dinámicos en un informe de tabla dinámica o de gráfico dinámico.

  MAE. Oscar Matute  

24

Realizando una tabla Dinámica Una tabla dinámica nos permite realizar síntesis de los datos de que disponemos para poder analizarlos. De hecho en muchas empresas se malgastan recursos para obtener unos resultados que tienen muy a mano sabiendo utilizar las tablas dinamicas. Para ir conociendo en profundidad la enorme potencia de las tablas dinámicas, vamos a ver un sencillo ejercicio. Poco a poco ya lo iremos complicando. Partiremos de la siguiente tabla con los datos de una serie de conferenciantes, con las distintas ciudades, sueldo percibido y horas trabajadas.

En esta tabla están los datos que necesitamos para responder a las siguientes preguntas: ¿Cuantas conferencias se han dado en Madrid?, ¿Cuánto ha ganado Nadia Vidal y Cristina Garcia? ¿Cuál es el número de horas que Nadia Vidal ha dado en Barcelona? ¿Cuál es el sueldo promedio de cada conferenciante? ¿ Y el sueldo medio por ciuidad? Y así, con este tipo de preguntas podíamos seguir y seguir. Puede hallarse la solución sin emplear tablas dinámicas que, como ya hemos dicho, es la solución que emplean en muchas empresas, pero es muy complicada, laboriosa y, sobre todo, de un sólo uso, esto es, podemos diseñar complicadas fórmulas empleando funciones lógicas, por ejemplo, pero sólo valdrán para el caso concreto que estoy usando. Por contra, la solución mediante tablas dinámicas, como su propio nombre indica, es eso dinámica, con apenas unos cambios que tardamos 10 segundos en hacer podremos responder a varias preguntas disitintas. Vamos a responder una a una a cada una de las preguntas que hemos hecho utilizando tablas dinámicas.

MAE. Oscar Matute  

25

1) ¿Cuantas conferencias se han dado en Madrid? Lo primero que haremos será, ahora y siempre, seleccionar el rango de datos que queremos usar para la tabla dinámica. En este caso sería el rango A1:D15. Es buena práctica seleccionar las columnas completas A, B, C y D, pulsando [CTRL] + el botoncito de su rótulo, para que, en el caso que la tabla se agrande, tenga más valores, las tablas dinámicas siguen valiéndonos. Una vez seleccionado el rango, en nuestro caso, las columnas A, B, C y D. Nos vamos al menú Insertar, opción Tabla dinámica.

En principio, dejaremos las opciones que hay seleccionadas: dos para el rango de datos, que ya hemos seleccionado previamente, y otras dos para la ubicación de la tabla dinámica, que por defecto es en una nueva hoja. Le damos a aceptar, y nos aparece, ya en la hoja nueva, una serie de opciones y menús para generar nuestra tabla dinámica.

MAE. Oscar Matute  

26

Elegiremos el campo delegación (la columna de las ciudades) y, para saber cuántas conferencias ha habido, cualquier otro campo, ya que dependerá de la función que le asignemos, nosotros hemos elegido el campo ponente. Arrastramos el campo Delegación al área Etiqueta de la Fila y el campo Ponente al área Valores.

MAE. Oscar Matute  

27

Vemos que en el área Valores, pone Cuenta de Ponentes, en un desplegable (marcado en rojo en la imagen). En este caso lo dejaremos como está porque lo que queremos es que cuente el número de ponentes, pero ahí podríamos elegir otras funciones como la función SUMA, PROMEDIO, etc. Llegados a este punto se nos ha generado la siguiente tabla dinámica:

En la que se ve el número de conferencias por cada ciudad y el total. Como quiera que en el ejercicio nos pidan sólo las de Madrid, elegiríamos Madrid del desplegable rotulado como Etiquetas de Fila.

MAE. Oscar Matute  

28

El resultado es una tabla similar a la anterior, pero en la que sólo aparece el dato de Madrid:

De este modo, en un instante podemos obtener la información por las localidades que queramos. En este ejemplo, hemos visto claramente porque se llaman tablas dinámicas, porque son tablas que se modifican muy fácilmente en función de lo que nos interese.

 

MAE. Oscar Matute  

29