TABLAS DINAMICAS EXCEL, Resúmenes de Sistemas Operativos

¡Descarga TABLAS DINAMICAS EXCEL y más Resúmenes en PDF de Sistemas Operativos solo en Docsity! Análisis eficaz de datos con tablas dinámicas (2ª edición) Destinado a todas aquellas personas que tienen que analizar datos a partir de diferentes orígenes, este libro sobre la  creación  de  tablas dinámicas  con  Excel  le  permitirá  convertirse  en  un  verdadero  experto  en  la  creación, explotación y análisis de las tablas dinámicas. Cubre las versiones 2010, 2013 y 2016 de Excel;  las capturas de pantalla se han realizado con la última versión, Excel 2016. Gracias a los numerosos ejemplos que le proponemos realizar,  análisis,  agrupamientos,  filtros,  segmentos,  cronologías,  campos  y  elementos  calculados, gráficos dinámicos... no tendrán secretos para usted. A lo largo de estos capítulos irá creando tablas de análisis, funcionales y variadas utilizando diferentes tipos de orígenes  de  datos  (una  o  más  hojas  de  Excel,  una  tabla  o  consulta  Access...).  Al  final,  será  capaz  de aplicar formatos condicionales a sus tablas dinámicas y automatizar tareas repetitivas empleando los numerosos trucos  y  consejos que proporcionamos  (sobre  todo en  la  utilización de  funciones de  cálculo  avanzadas de Excel 2016 y en la realización de procedimientos VBA). Al final del libro hay un ejemplo de utilización de Power Pivot. Los ejemplos utilizados en esta obra provienen de casos concretos y variados. Los ficheros necesarios para llevarlos a cabo se pueden descargar en esta página. Pierre RIGOLLET Informático y consultor, Pierre RIGOLLET  desarrolla desde 1998 aplicaciones  informáticas personalizadas para diversas  organizaciones.  También es  profesor  de  informática  en  empresas  y  en  escuelas  superiores,  y  autor  de numerosas obras sobre la suite Microsoft Office. En este libro comparte con usted toda su experiencia pedagógica adquirida ante un público múltiple y variado. Introducción 1. Ideas preliminares Esta obra le va a enseñar a diseñar y crear tablas dinámicas con Excel 2016 a partir de datos múltiples y variados. En primer lugar, va a estudiar los elementos básicos que intervienen en la planificación de este tipo de tablas a partir de ejemplos sencillos, y a continuación, será capaz de crear con rapidez informes de tablas dinámicas complejas. Todos los ejemplos que aparecen en el libro se pueden descargar desde la página Información. Esto le evitará tener que crear sus propios archivos y le permitirá corregir sus resultados. Le recomendamos que siga el orden del libro, que ha sido diseñado para que el lector pueda avanzar de forma gradual. Los ejercicios del nivel superior hacen referencia a los elementos trabajados en los ejercicios anteriores. Este método le permitirá consolidar lo aprendido a medida que vaya avanzando. Los ejemplos del libro son variados y le van a permitir implementar las técnicas de creación de tablas dinámicas en numerosos ámbitos. Cuando  haya  realizado  todos  los  ejemplos  que  se  proponen  en  el  libro,  será  capaz  de  crear  sus  propias  tablas dinámicas complejas adaptadas a lo que necesite. Si  ya  utiliza  las  tablas  dinámicas  con  versiones  anteriores,  Excel  2016  le  permitirá  importar  y  relacionar  grandes cantidades de datos que provengan de diferentes orígenes con la ayuda de un modelo de datos integrado. De este modo, podrá generar modelos de datos que le servirán de base para sus tablas o gráficos dinámicos. El modelo de datos de Excel tiene como puntos fuertes: El tratamiento rápido de un gran volumen de datos. Una gestión de tamaño de archivos mejorada. Portabilidad de los datos, ya que se almacenan dentro del libro. Excel 2016 tiene integrado el componente PowerPivot, un espacio de trabajo que permite tratar los datos de varios millones  de  líneas  de  orígenes múltiples  y  diversos  (bases  de  datos,  cubo OLAP...).  PowerPivot  tiene  un  lenguaje específico DAX (Data Analysis Expressions) para establecer relaciones, cálculos y sumatorios. 2. Objetivo Una  tabla  dinámica  permite  obtener  con  rapidez  un  resumen  a  partir  de  una  base  de  datos.  Se  trata  de  una herramienta con muchas prestaciones,  integrada en Microsoft Excel, que  le ayudará a  idear análisis eficaces y con resultados a partir de sus tablas de datos. Una tabla dinámica es una tabla cruzada porque permite realizar resúmenes que  incluyan dos,  tres, cuatro o más dimensiones. Por ejemplo, puede obtenerse el importe total de los gastos mensuales por servicio (2 dimensiones), o el total de los gastos mensuales por departamento y para un puesto contable en particular (3 dimensiones)... A continuación, puede ver tres ejemplos de tablas dinámicas simples. Base de datos Conjunto  de  información  o  datos  estructurados  guardados  en  un disco. Este conjunto de datos se puede consultar y modificar. Registro Cada registro está compuesto por un conjunto de datos relativos a un elemento de la base de datos. Campo Un  campo  contiene  uno  de  las  datos  del  registro.  Se  necesitan varios campos para completar un registro. Por ejemplo, en el caso de  un  empleado:  Número  de  identificación,  Nombre,  Apellidos, Sexo, Cargo, Salario... Filtro de informe Permite  filtrar  los  datos  que  se  desean  mostrar  en  función  del elemento seleccionado en el filtro del informe. Un filtro de informe sirve para representar con rapidez un subconjunto de datos en un informe  de  tabla  dinámica.  Cuando  los  datos  de  origen  son abundantes, el filtro permite trabajar con una parte de los datos de origen  del  informe.  También  es  posible  mostrar  los  resúmenes relativos a una  familia de productos, un periodo de tiempo o una categoría específica de personal. Segmentos Permite utilizar  botones para  segmentar  y  filtrar  rápidamente  los datos. Modelo de datos Colección de tablas y de sus relaciones entre ellas. Rótulos de fila Permiten mostrar los campos en forma de filas en el lado izquierdo del  informe.  Una  fila  de  posición  inferior  queda  anidada  en  la columna que se aparece inmediatamente por encima de ella. Rótulos de columna Permite  mostrar  los  campos  en  forma  de  columnas  en  la  parte superior  del  informe.  Una  columna  de  posición  inferior  queda anidada  en  la  columna  que  se  aparece  inmediatamente  a  la izquierda. Campos de valores Sirven  para  mostrar  datos  de  resumen  numéricos.  Se  pueden utilizar diversas  funciones de resumen (suma, promedio, mínimo, máximo, contar...). Funcionalidad Límite máximo Número máximo de tablas dinámicas por hoja En función de la cantidad de memoria disponible Número máximo de elementos únicos por campo 1048576 Número máximo de campos de fila o de columna En función de la cantidad de memoria disponible Número máximo de filtros de informe en una la tabla dinámica 256 (limitación posible en función de la memoria disponible) Número máximo de campos de valor en una la tabla dinámica 256 Las tablas dinámicas 1. Definiciones 2. Los límites de las tablas dinámicas Funcionalidad Límite máximo Número máximo de fórmulas en una la tabla dinámica En función de la cantidad de memoria disponible Número máximo de filtros 256 (limitación posible en función de la memoria disponible) Número máximo de campos de valor 256 Fórmulas de elementos calculados en un informe de gráfico dinámico En función de la cantidad de memoria disponible Número máximo de elementos en una lista de filtro 10000 3. Los datos de origen Los datos origen de una tabla dinámica deben tener siempre la misma estructura:   Una fila, que representa un registro de la base de datos.   Una columna, que representa un campo Vea un ejemplo con las primeras filas de los datos de origen, extraídas de una hoja de cálculo de Excel: La primera  fila debe contener  los  títulos  (nombres de  los campos). Para que se puedan cruzar  los datos. La base de datos debe contener al menos dos campos para cruzar, más un campo de datos numéricos. 4. Recomendaciones Para evitar problemas a la hora de crear tablas dinámicas, tenga en cuenta los siguientes consejos: Objetivo del análisis Resultados que se desea obtener Origen Datos que deben indicarse   Las celdas de la fila de los títulos no se deben combinar.   No puede haber dos campos con el mismo nombre.   Un nombre de campo nunca debe estar vacío.   La base de datos no debe contener ninguna fila o columna vacía ni oculta.    No se deben introducir filas de subtotales en la base de datos.   En las columnas de valores numéricos, es preferible rellenar las celdas vacías con ceros. 5. Análisis A menudo,  tendrá que definir  cuáles  son  los datos que  se  tienen que utilizar  antes de  crear un  informe de  tabla dinámica.  Por  ejemplo,  si  exporta  información  desde  un  programa  de  gestión  comercial,  deberá  seleccionar  la información (campos) que se deba tener en cuenta. Si desea crear con Excel una aplicación partiendo de cero, debería efectuar previamente un análisis preciso y completo de las estadísticas que desee obtener más adelante. Imaginemos, por ejemplo, que es responsable de una unidad de producción de barritas  energéticas  para deportistas.  Para  cada operación  sobre  el  stock,  introducirá  la  información cronológicamente en una hoja de cálculo de Excel. La tabla en la que introducir los datos se podría parecer a la siguiente: La columna PRODUCTO tiene un área de lista desplegable que facilita la introducción de información para evitar que la misma información se escriba de varias maneras distintas. Una tabla de introducción como esta le permite obtener: El stock teórico por producto y por número de lote. Las cantidades de entrada y salida por producto. Las cantidades de entrada y salida por producto al mes. La siguiente tabla muestra algunos ejemplos de datos que hay que integrar en función de las necesidades. Esta zona (opcional) aparece siempre en la parte superior del informe. Puede incluir una o varias filas que permiten filtrar los datos de la tabla de origen y que se usarán para efectuar los cálculos de resumen. Es posible seleccionar todas las filas de la tabla de origen (Todas ­ opción por defecto) o bien seleccionar uno o más elementos. Zona de rótulos de fila Excel cataloga aquí de forma única la información que contiene un campo de la fuente. Es primordial que los datos relativos  a  una  misma  información  se  escriban  siempre  de  la  misma  forma  en  la  tabla  de  origen.  Si  el  tipo INFORMÁTICA se hubiera escrito a veces en mayúsculas  sin acentos y a veces en minúsculas  con acentos, Excel crearía dos filas en la tabla dinámica aunque ambas se refieran a la misma información. Esta zona permite filtrar la información que se va a visualizar. Se podrán visualizar solo algunos tipos de formación. Zona de rótulos de columna Al igual que sucede con la zona de los rótulos de fila, Excel cataloga aquí de forma única la información que contiene un  campo  del  origen.  También  se  puede  utilizar  un  filtro.  En  nuestro  ejemplo,  sería  posible  mostrar  solo  las estadísticas relativas al comercial cárdenas. Zona de valores En esta zona, Excel presenta  los cálculos de resumen. Dispone de varias funciones para efectuar cálculos: Excel  le propone  utilizar  la  función  Suma  si  el  campo  integrado  es  un  campo  numérico,  pero  también  puede  utilizar: Promedio, Máx, Mín, Contar... en función de sus necesidades. Zona de totales En esta parte se efectúa un resumen por fila y por columna de los datos de la zona de valores. Si ha decidido utilizar la función de promedio para calcular la zona de valores, en la zona de totales también se calculará el promedio. 7. ¿En qué caso utilizar una tabla dinámica? Cuando tenga que trabajar con un gran número de datos, una tabla dinámica le permitirá haría falta efectuar análisis rápidos y eficaces. Si las tablas dinámicas no existieran, mucho tiempo para idear tablas de resumen que invocaran a las  funciones  de  cálculo  avanzadas  de  Excel.  Gracias  a  las  tablas  dinámicas,  aumentará  su  productividad  y  su capacidad de análisis, y será capaz de reaccionar rápidamente ante un problema que haya puesto de relieve la tabla dinámica. En resumen, utilice las tablas dinámicas en los siguientes casos: Cuando deba tratar una cantidad de información elevada que evolucione con el tiempo. Cuando  desee  obtener  rápidamente  (o  se  lo  pida  su  jefe)  distintas  estadísticas:  coste  total, promedio  de  los  costes  por  equipo,  gasto  mayor,  número  de  clientes  que  han  comprado  un producto en particular... Cuando deba efectuar estadísticas sobre periodos determinados o cuando desee comparar las cifras de diferentes periodos. Cuando deba obtener gráficos de resumen simples y fiables a partir de numerosas filas de datos de origen. Cuando deba obtener cifras claves (total, promedio, número de elementos...) sobre un gasto, un departamento, un empleado, un sector o bien una familia de productos. 8. Una buena base para comenzar Hemos  insistido  en  la  importancia  de  la  estructura  de  los  datos  de  origen.  Para  terminar  este  capítulo,  vamos  a mostrar un ejemplo tipo de datos de origen que no son idóneos para crear una tabla dinámica. La siguiente tabla muestra el detalle de las ventas semanales realizadas por las tres comerciales. Utilizar los filtros 1. Preliminares Una tabla dinámica representa un resumen en el que intervienen muchos campos que se cruzan para obtener  las estadísticas. Puede que no siempre tenga necesidad de crear tablas de resumen complejas a partir de una base de datos. En algunos casos, quizá solo necesite filtrar determinada información con el fin de obtener una lista con los registros que respondan a uno o más criterios, o bien de obtener los totales de algún dato numérico en función de un parámetro. A continuación, presentamos algunos ejemplos de información que se puede obtener de forma sencilla a partir de una base de datos de Excel: Lista de facturas para una familia de clientes. Lista de empleados que tengan más de diez años de antigüedad y que pertenezcan a un servicio. Importe total de las ventas de una familia de productos. Lista de los abonados cuyo nombre comience por la letra L. Para  aprender  estos  conceptos,  trabajaremos  con  el  archivo VentasWEB.xlsx,  que  se  puede  descargar  desde  la página Información. Este  libro  contiene  la  lista  de  todas  las  ventas  realizadas  por  una  web  de  comercio  electrónico.  El  responsable comercial del sector «Sureste (SE)» de España ha extraído del archivo de ventas globales las ventas relativas a esta zona. Los datos que se muestran en la hoja ListaVentas son los siguientes. El responsable comercial ha puesto algunas filas en azul manualmente. 2. Los filtros Un filtro se define por un criterio aplicado a un campo (columna). Existen tres tipos de filtros: Por valor de lista: Excel cataloga automáticamente los valores presentes en una columna y los muestra en una lista sin duplicados. Basta con marcar el valor o los valores deseados para visualizar solo las filas cuyos valores del campo son iguales a los elementos seleccionados. Por colores: esta opción permite definir  filtros por  color de  fuente,  color de celda... El  filtro por color funciona tanto con un formato condicional como con un formato definido manualmente. De texto, numéricos o cronológicos: el usuario establece un criterio personalizado, por ejemplo texto que contenga una cadena de caracteres o bien importes comprendidos entre dos valores. Excel  permite  aplicar  un  filtro  en  cada  campo  de  una  base  de  datos.  Por  cada  filtro  definido,  las  filas  que  no corresponden  a  los  criterios  se  ocultan  automáticamente.  Los  filtros  son  acumulativos.  Se  puede  reducir  el subconjunto de los datos que se muestran aplicando filtros sucesivos. Filtrar una base de datos:   Abra el libro VentasWEB.xlsx.   Coloque el cursor en una celda cualquiera, por ejemplo A1.   En la pestaña Datos ­ grupo Ordenar y filtrar ­ haga clic en Filtro: Aparece una lista en el extremo derecho de cada campo: a. Filtros de texto Lista de ventas efectuadas por clientes de la región ISLAS CANARIAS:   Abra la lista del campo REGIÓN CLIENTE.   Desmarque la opción Seleccionar todo, marque ISLAS CANARIAS y haga clic en Aceptar. Excel solo muestra  los registros filtrados. En  la parte  inferior  izquierda de  la ventana se puede ver el número de registros que se muestran, en este caso 219 de un total de 942. La pequeña flecha a la derecha de REGIÓN CLIENTE ha sido sustituida por el botón  , lo que indica que se ha aplicado un filtro a este campo. Marcar varios elementos es como utilizar la función O; por ejemplo, si marca solo ANDALUCIA yEUSKADI, le indica a Excel  que  quiere  visualizar  todas    las  filas  que  contienen  en  la  columna  REGIÓN  CLIENTE ANDALUCIÁ o EUSKADI. Si define filtros en otros campos, estará utilizando la función Y. Puede  anular  un  filtro  en  cualquier  momento,  haciendo  clic  en  el  botón    del  campo,  y  a continuación en la opción Borrar filtro de, o bien marcando la opción Seleccionar todo y haciendo clic en Aceptar. Si ha definido filtros en varios campos, es más rápido anularlos todos haciendo clic en el botón Borrar del grupo Ordenar y filtrar.   En nuestro ejemplo, haga clic en el botón   del campo REGIÓN CLIENTE  y,  a  continuación, en Seleccionar todo. Lista de ventas EQUITACIÓN pagados en 4 plazos sin cargo: Lista de las 10 ventas más importantes:   Para volver a ver todos los registros, anule el filtro anterior.   Abra  la  lista  del  campo  IMPORTE,  coloque  el  cursor  sobre  la  opción  Filtros  de  número  y seleccione Diez mejores.   Haga clic en Aceptar. Se muestran las 10 ventas más importantes. Si es necesario, puede ordenar por orden decreciente los  importes. Seleccionando la opción inferiores en el cuadro de diálogo Autofiltro de las diez mejores, habríamos obtenido los 10 importes más pequeños. c. Filtros por color Lista de ventas en azul: En el archivo que ha descargado, se ha definido manualmente un color de fuente azul en algunos registros.   Para volver a la ver todos los registros, haga clic en el botón Borrar del grupo Ordenar y Filtrar.   Los colores se han aplicado en todos los campos de la base, el filtro por color se puede aplicar a cada uno de los campos. Abra la lista de uno de los campos, coloque el cursor sobre Filtrar por color y seleccione el color azul. 10 registros corresponden con el criterio: d. Filtros cronológicos Excel  le  permite  aplicar  filtros  estáticos  y  dinámicos.  Los  filtros  estáticos  se  aplican  a  datos  fijos  que  introduzca usted. Los filtros dinámicos se aplican teniendo en cuenta la fecha del día. Lista de las ventas realizadas entre el 15/01/2016 y el 15/02/2016 (filtro estático):   Para volver a ver todos los registros, anule el filtro anterior.   Abra  la  lista  del  campo  FECHA,  coloque  el  cursor  sobre  la  opción  Filtros  de  fecha  y seleccione Entre.   Introduzca los datos deseados y haga clic en Aceptar: 89 registros corresponden con el criterio. Lista de ventas realizadas la última semana (filtro dinámico):   Para volver a la lista completa, anule el filtro anterior.   Abra  la  lista  del  campo  FECHA,  coloque  el  cursor  sobre  la  opción  Filtros  de  fecha  y seleccione Semana pasada. Hay numerosas opciones de filtro disponibles (Esta semana, Próximo mes, Este mes, Trimestre pasado...). En los filtros anuales ofrecidos por Excel, verá: Este año y Hasta la fecha. Este año muestra todas las fechas del año en curso y Hasta la fecha solo muestra las fechas entre el uno de enero del año en curso y la fecha actual. Filtros avanzados 1. Definiciones Configurar un filtro avanzado permite extraer los datos filtrados. Para  efectuar  esta  operación,  debe  disponer  de  dos  rangos  diferentes:  una  zona  de  criterio  y  una  zona  de extracción. Estas dos zonas deben contener en su primera fila los campos que se deben filtrar y/o visualizar. No es necesario  que  tengan  los mismos  campos.  En  contraposición,  solo  es  posible  copiar  los  datos  filtrados  en  la  hoja activa. 2. Proceso de creación Por ejemplo, vamos a considerar que debe enviar al responsable de la zona (CATALUNYA / EUSKADI) la lista de ventas relativas a su zona en las que el pago se haya efectuado en cuatro plazos sin cargo. La zona de criterios solo debe contener dos campos (REGIÓN CLIENTE y TIPO PAGO), y la zona de extracción, los siete campos deseados. Se han preparado estas dos zonas en el libro VentasWEB.xlsx (hoja ListaVentas):   En primer lugar, configure los elementos de la zona de criterios.   A continuación, haga clic en cualquier celda de la lista de datos.   Pestaña Datos ­ grupo Ordenar y filtrar ­ Avanzadas.   Marque la opción Copiar a otro lugar.   En el campo Rango de criterios, seleccione el rango P5:Q7.   En el campo Copiar a, seleccione el rango T5:Z5.   Haga clic en Aceptar.   Configure las opciones del cuadro de diálogo Subtotales como se muestra a continuación.   Haga clic en el botón Aceptar. Excel pasa automáticamente al modo Esquema: Los símbolos del esquema se pueden ver en la esquina superior izquierda de la ventana. Si hace clic en el nivel 1, Excel muestra solo el total general de las ventas. Si se hace clic en el signo +, se muestran de nuevo todas las filas. Tras cada región aparece un subtotal. Si hace clic en el nivel 2, Excel muestra solo los subtotales por región, y a continuación, el total general. Si se hace clic en un signo +, se muestra el detalle de las filas de la región en cuestión. Si hace clic en la celda G225, puede ver la fórmula =SUBTOTALES(9;G2:G224); el 9 corresponde al número de la función suma. 4. Calcular el número de ventas por familia de productos   Elimine  los  subtotales:  pestaña Datos  ­  grupo  Esquema,  haga  clic  en  el  botón  Subtotal  y  a continuación, en Quitar todos.   Haga  clic  derecho  en  una  celda  de  la  columna FAMILIA PRODUCTO,  coloque  el  cursor  sobre  la opción Ordenar y haga clic en Ordenar de A a Z.   En la pestaña Datos, en el grupo Esquema, haga clic en el botón Subtotal.   Configure las opciones de la ventana Subtotales como se muestra a continuación.   Haga clic en el botón Aceptar. Si hace clic en el nivel 2, Excel muestra el número de ventas por familia de productos. En este caso hemos utilizado  la  función Suma en el campo IMPORTE. Si hubiéramos  realizado el subtotal  en  el  campo MODO  PAGO,  los  valores  hubieran  sido  idénticos,  pero  los  subtotales  se habrían insertado en la columna MODO PAGO. Los subtotales nos permiten, pues, realizar resúmenes por un parámetro. Para cruzar dos, tres o más elementos, es necesario utilizar los informes de tablas dinámicas. El rango de datos que hay que analizar se selecciona automáticamente y aparece rodeado por una línea de puntos que parpadean. La opción Nueva hoja de cálculo aparece seleccionada por defecto.   Haga clic en Aceptar. Excel crea una nueva hoja de cálculo en la que aparece, en la parte izquierda, la zona del informe. El cuadro de diálogo Campos de tabla dinámica aparece en la parte derecha de la ventana. Todas las modificaciones que se hagan en el cuadro de diálogo Campos de tabla dinámica se mostrarán al instante en la zona del informe si no está marcada la opción Aplazar actualización del diseño. El cuadro de diálogo se puede personalizar y desplazar. El botón que aparece arriba a la derecha permite modificar la visualización y la disposición de los diferentes elementos. La presentación por defecto, llamada Sección de campos y sección de áreas apiladas, es la más lógica, ya que representa exactamente la disposición de su tabla dinámica. Vamos a calcular el importe total de los gastos por tipo y por pagador. La tabla dinámica se va a crear a medida que vayamos colocando los campos en las diferentes zonas.   Arrastre el  campo GASTOS  a  la  sección FILAS,  el  campo PAGADOR  a  la  zona COLUMNAS  y  el campo IMPORTE a la zona Σ VALORES. Al campo IMPORTE se le ha aplicado la función Suma por defecto. La tabla dinámica se crea al instante en la hoja: Excel ha creado una tabla con dos entradas y ha calculado automáticamente los totales correspondientes.  Dar formato a la tabla dinámica 1. Modificar la disposición de la tabla dinámica En general, es preferible presentar un informe de tabla dinámica poniendo en el encabezado de fila el concepto que tenga más datos. Así, la tabla podrá imprimirse sin problemas en sentido vertical. En este caso, tenemos más tipos de pagos que pagadores; por esta razón colocaremos los tipos de pago en las filas y los pagadores en las columnas. Vamos a ver cuál habría sido la presentación de la tabla si los campos se hubieran invertido.   En  el  cuadro  de  diálogo  Campos  de  tabla  dinámica,  arrastre  el  campo  GASTOS  a  la sección COLUMNAS y PAGADOR a la sección FILAS. La tabla dinámica se reorganiza, pero es menos funcional: Una  de  las  ventajas  de  Excel  es  que  nos  permite  hacer  pruebas  de  formato.  Si  durante  la  creación  de  su  tabla dinámica se da cuenta de que no es funcional, bastarán algunos segundos para organizarla de manera diferente.   En nuestro ejemplo, vuelva a la situación inicial de la tabla dinámica. Cuando  hace  clic  en  una  celda  situada  fuera  de  la  tabla  dinámica,  el  panel  desaparece.  Para  que aparezca de nuevo, basta con hacer clic en una celda de la tabla dinámica. Si se ha cerrado el panel y quiere volver a verlo, haga clic derecho en una celda de  la tabla dinámica y escoja  la opción Mostrar lista de campos. 2. Modificar el formato de los números En función del tipo de valores que analice, puede ser útil aplicar a los números el formato de miles para mostrar los números con un punto como separador de miles.   Haga clic derecho en uno de los valores de la tabla.   Haga clic en la opción Formato de número. En la categoría Número, marque Usar separador de miles y configure el número de decimales que desea mostrar.   Haga clic en el botón Aceptar. 3. Modificar el título de la tabla   En  la  tabla  dinámica,  coloque  el  cursor  en  la  celda  Suma  de  IMPORTE  e introduzca TOTAL GASTOS. El nuevo nombre del campo aparecerá en el cuadro de diálogo: 4. Modificar las etiquetas de filas y columnas Para hacer más legible nuestra tabla, sería conveniente modificar los rótulos de filas y columnas.   Coloque el  cursor en  las  celdas que quiera modificar y a continuación,  introduzca directamente  los nuevos rótulos.   En la pestaña Inicio, grupo Portapapeles, haga clic en la herramienta Copiar formato.   Seleccione los valores de la columna IMPORTE Max. No olvide sustituir el título Total General por un texto más adecuado, IMPORTE MIN y MAX por ejemplo. 6. Actualizar los datos Cuando  se  modifica  un  dato  en  una  tabla  de  Excel  que  contiene  fórmulas,  esas  fórmulas  se  vuelven  a  calcular automáticamente. Pero, en una tabla dinámica, si se modifica la información de la base de datos, las tablas dinámicas que se han creado a partir de dicha base no se actualizan automáticamente. Por  lo  tanto, debemos actualizar  las tablas dinámicas si los datos de origen se han modificado.   Para  simular  este  cambio  de  datos,  modifique  las  tres  primeras  filas  de  datos  de  la hoja GASTOS tomando como referencia esta pantalla. Si  volvemos  a  la  hoja  que  contiene  las  tablas  dinámicas,  veremos  que  los  totales  no  han  cambiado.  Debemos proceder, por tanto, a la actualización.   Haga clic en una de las celdas de las tablas dinámicas.   En la pestaña Herramientas de tabla dinámica ­ Analizar, abra  la  lista del botón Actualizar  y haga clic en la opción Actualizar todo, o utilice el atajo de teclado [F5]. Las dos tablas ahora se han modificado. Acabamos de probar la modificación de valores. Para ir más lejos en el análisis, ahora vamos a modificar los tipos de gasto por dos nuevos tipos, así como los importes correspondientes.   Proceda a la modificación de los datos en la hoja GASTOS.   Realice la actualización de las tablas. Se han modificado los totales y los nuevos tipos de pago se han añadido al final de las tablas. En contrapartida, a las dos tablas, que al principio estaban separadas por tres filas, ahora solo las separa una. Si  la tabla dinámica solo halla filas vacías al extenderse, no se insertará ninguna fila nueva para desplazar  la parte situada debajo. Si  la  tabla  dinámica  encuentra  otra  tabla  dinámica  al  extenderse,  aparece  el  siguiente  mensaje.  Si  se  hace  clic en Aceptar, se insertan las filas para desplazar hacia abajo la tabla dinámica situada debajo. Intente dejar un número suficiente de filas entre dos tablas dinámicas. Si  añadimos  filas  al  final  de  nuestra  base  de  datos,  estas  no  se  tendrán  en  cuenta  en  las actualizaciones.  Más  adelante  veremos  cómo  crear  rangos  de  origen  dinámicos  cuando  varía  el número de filas. 7. Ordenar los campos Los tipos de pago añadidos no se han ordenado por orden alfabético en nuestras dos tablas dinámicas. La ordenación se efectúa automáticamente cuando se crea una tabla dinámica, pero no cuando se actualiza.   Haga clic derecho en uno de los tipos de pago de la primera tabla.   Coloque el cursor sobre la opción Ordenar y a continuación, haga clic en Ordenar de A a Z:   Proceda del mismo modo para ordenar la segunda tabla. 8. Calcular porcentajes Puede resultar interesante conocer la parte de cada tipo de pago respecto al total de gastos. Las tablas dinámicas de Excel nos permiten obtener rápidamente este tipo de información. A partir de nuestra lista de gastos, cree en una nueva hoja la tabla dinámica como se muestra a continuación: Esto no corresponde a lo que esperábamos. De hecho, los porcentajes que se muestran están calculados respecto al total general.   En  el  panel  de  la  derecha,  despliegue  la  lista  del  campo  %  del  TOTAL,  y  a  continuación, seleccione Configuración de campo de valor.   Accede a  la pestaña Mostrar valores como; en  la  sección Mostrar valores como,  seleccione  la opción % del total de filas.   En la sección Nombre personalizado, introduzca el texto % / PAGADOR. Solo queda eliminar la visualización de la columna Total general.   Realice  un  clic  derecho  en  la  tabla  dinámica  y  haga  clic  en Opciones  de  tabla  dinámica.  En  la pestaña Totales y filtros, desmarque la opción Mostrar totales generales de las filas.   Modifique el título de las etiquetas de columnas. Su tabla ahora debe ser como esta: 6/8/85 /5/8 [8 8/8 [8 [0/+[= [0/02 A B Cc [%delTOTAL — PAGADORES|“ GASTOS + LUIS SANDRA AHORRO 2,99% 0,00% [ALIMENTACIÓN 11,20% 6,05% [COCHE 2,70% 0,70% DIVERSOS 1,79% 2,13% IMPUESTOS 13,06% 0,00% [MUTUA 5,43% — 0,00% NIÑOS 0,20% 9,94% [Ocio 8,05% 1,35% [ROPA 1,29% 1,12% | [SEGUROS 2,01% 0,92% VIVIENDA 26,49% — 2,34% [CINE 0,00% 0,12% TEATRO 0,17% 0,00% [Total general 75,33% 24,67% Las tablas dinámicas recomendadas Esta funcionalidad apareció con Excel 2013. 1. Objetivo Excel le propone diferentes modelos de tablas dinámicas para resumir sus datos. En cada uno de ellos, se mostrará una breve descripción de  la disposición de  los campos para permitirle elegir aquel que  le parezca más adecuado en función de lo que desee analizar. En  todo momento,  puede  utilizar  este método  de  creación  simple  y  rápido  para  hacer  pruebas  y  así  explorar  las diferentes posibilidades de presentación propuestas. Las sugerencias de tablas dinámicas a veces permitirán al principiante crear una tabla dinámica que quizá no se le habría ocurrido. 2. Utilización A fin de probar este método, vamos a utilizar los datos de nuestra hoja GASTOS.   Haga clic en la lista de datos.   En la pestaña Insertar, haga clic en el botón Tablas dinámicas recomendadas. Excel muestra una ventana de recomendaciones. Cruzar más de dos datos 1. Los datos de origen Para disponer de información variada, vamos a utilizar el archivo AlquilerMaterial.xlsx que se puede descargar desde la página Información. Los datos que se muestran en la hoja Alquiler son como los que puede ver a continuación. La última fila introducida es la fila 305. La tabla representa la lista de los alquileres realizados por una empresa de alquiler de material para eventos. Las facturas se emiten según las siguientes reglas: Particular: facturación el día que se devuelve material Otros clientes: facturación el último día del mes Si el material devuelto necesita una reparación o rehabilitación, los gastos de reparación se pueden facturar al cliente. En este caso, el importe de los gastos se suma al importe del alquiler para obtener el importe total que se ha de facturar. Todos los importes mostrados son sin impuestos. Los tipos de material que se pueden alquilar son: CARPAS ESCENARIO MESAS PLATAFORMAS Los tipos de cliente son: PARTICULAR CATERING EMPRESA COMPAÑERO HOTEL­RESTAURANTE ASOCIACIÓN Los comerciales (en el mostrador) son: SANDRA NICOLÁS TOMAS 2. Creación de la tabla dinámica Nuestro primer objetivo es calcular el importe total de los alquileres por tipo de material y por tipo de cliente. Queremos poder afinar nuestra estadística mostrando también (por tipo de material y por tipo de cliente) las ventas por comercial.   Abra el libro AlquilerMateriales.xlsx.   Inserte el cursor en una celda del origen, por ejemplo A1.   En la pestaña Insertar ­ grupo Tablas, haga clic en el botón Tabla dinámica. Aparece la ventana Crear tabla dinámica:   Haga clic en Aceptar.   Arrastre el campo TIPO CLIENTE a la sección FILAS.   Arrastre el campo TIPO MATERIAL a la sección COLUMNAS y a continuación, haga lo mismo con el campo COMERCIAL y sitúelo debajo de TIPO CLIENTE en la sección FILAS.   Por último, coloque el campo TOTAL FACTURA SIN IVA en la sección Σ VALORES. A medida que coloque los campos en las diferentes secciones, la tabla dinámica se va creando y adaptando automáticamente a la inserción de nuevos campos. La lista de campos de la tabla dinámica debe quedar como se muestra a continuación. Nuestra tabla dinámica tiene varias filas por material; cada una corresponde a un comercial. Aplique formato a su tabla dinámica:   Modifique el nombre los títulos y etiquetas.   Dé formato a los números con el separador de miles. 3. Primer análisis Podemos observar que el comercial Tomás no ha hecho ningún alquiler de escenarios, plataformas ni carpas a particulares. La tabla dinámica muestra también que el volumen de negocio más grande se ha realizado por el alquiler de carpas por parte de clientes de Catering. En pocos segundos, Excel nos permite visualizar elementos muy difíciles de ver cuando se trabaja con una lista de datos. El interés de una tabla dinámica es poder analizar en tiempo real información relacionada con la gestión. Una vez  se ha  creado  la  tabla, puede actualizarla  semanalmente para  reaccionar  con  rapidez  si  fuera necesario. Cuando nos acercamos al  cierre del ejercicio contable es cuando se pide a un comercial que aumente las ventas de una familia de productos o a un tipo de cliente. Las tablas dinámicas son una de las funcionalidades de Excel que debería usarse diariamente. Se trata de herramientas que ayudan a tomar decisiones y que permiten efectuar un seguimiento de  la  información específica relacionada con cada tipo de actividad: costes de producción, horas trabajadas por persona, seguimiento de ventas... La fiabilidad y la exactitud de los datos de origen son fundamentales. Si estos datos se escriben directamente en una tabla de Excel, aconsejamos que se incluyan sistemas de control con el fin de evitar, en lo posible, errores al escribirlos. Para que nuestro análisis  sea más preciso, vamos a  calcular  cuál  es el  importe de  los gastos de  reparación que quedan a nuestro  cargo por  tipo de material y por tipo de cliente para cada comercial. 4. Insertar un filtro de informe   Haga clic en la tabla dinámica para que aparezca el panel Campos de tabla dinámica.   En  la  lista  de  campos,  desmarque  el  campo TOTAL FACTURA  y  arrastre  el  campo GASTOS  DE REPARACIÓN a la zona Σ VALORES para que el resumen se realice ahora con respecto al importe de las reparaciones.   Arrastre el campo GASTOS FACTURADOS AL CLIENTE a la sección FILTROS. Los campos deben repartirse de la siguiente manera: Esta tabla está bien para hacernos una primera idea.   Modifique el nombre de la tabla por GastosNoFact. Sin embargo, estas cifras se deben comparar con el  importe  total de  los alquileres  facturados por cada uno de  los comerciales. Podríamos caer en  la tentación de añadir el campo IMPORTE ALQUILER  en  la  sección Σ VALORES,  pero nuestra  tabla no  resumiría más que  las  facturas en  las que el campo GASTOS FACTURADOS AL CLIENTE sea igual a N. La solución consiste en crear otra tabla dinámica bajo la primera para poder visualizar/imprimir las dos tablas juntas.   Inserte el cursor en una celda del origen.   Pestaña Insertar ­ grupo Tablas, haga clic en el botón Tabla dinámica.   En  el  cuadro  de  diálogo  Crear  tabla  dinámica,  marque  la  opción Hoja  de  cálculo  existente, seleccione la hoja GastosNoFact, haga clic en la celda A12 y haga clic en Aceptar.   Arrastre  el  campo  COMERCIAL  a  la  sección  FILAS  y  el  campo  IMPORTE  ALQUILER  a  la sección Σ VALORES.   Modifique el nombre de las etiquetas y dé formato a los números. Nuestras dos tablas dinámicas se muestran una debajo de otra. Para  poder  analizar  realmente  nuestras  dos  tablas  en  paralelo,  sería  razonable  insertar  en  la  hoja  de  cálculo  el  porcentaje  de  las  reparaciones  no facturadas de cada comercial respecto al importe total de sus facturas. Esto nos permitirá utilizar los datos de una tabla dinámica en fórmulas de cálculo. Vamos a poner en práctica dos métodos. Método 1   Haga clic en la celda C4.   Escriba la fórmula =B4/B13 con el teclado y copie hacia abajo hasta C6. Método 2   Haga clic en la celda D4.   Escriba la misma fórmula que antes, pero haciendo clic en las celdas B4 y B13 en lugar de escribirlas con el teclado. Excel escribe la fórmula: =IMPORTARDATOSDINÁMICOS("GASTOS  DE REPARACIÓN";$A$3;"COMERCIAL";"NICOLAS")/IMPORTARDATOSDINÁMICOS("IMPORTE ALQUILER";$A$12;"COMERCIAL";"NICOLÁS")   Esta fórmula no se puede copiar hacia abajo; repita los cálculos en D5 y D6. Esta última fórmula es más compleja que la primera, ya que utiliza la función IMPORTARDATOSDINÁMICOS. La gran ventaja de esta función es que continúa resumiendo los datos correctamente aunque se modifique la disposición del informe, lo que no ocurre con la fórmula del método 1.   Para terminar, muestre a los cálculos como porcentajes con dos decimales. Podemos comprobar que Sandra es la comercial tiene el porcentaje más bajo de reparaciones no facturadas. 5. Expandir/contraer el detalle Para poner en práctica esta técnica, vuelva a  la hoja GastosNoFactDetalles. Observará un botón para contraer ( ) a  la  izquierda de cada  tipo de cliente. Esto permite ocultar detalles relativos a la sección.   Haga clic en el botón   asociado a cada uno de los clientes: las filas de los comerciales desaparecen y solo se muestra el total. El botón   se ha reemplazado por el botón expandir ( ). Así, es posible mostrar solo los totales por tipo de cliente.   Haga clic en el botón   asociado al tipo de cliente CATERING para expandir los detalles y visualizar las filas de cada uno de los comerciales. Así puede mostrar el detalle de uno o varios clientes. 6. Expandir/contraer rápidamente la información de resumen Nuestra base de datos de ejemplo solo tiene tres comerciales, cinco tipos de cliente y cuatro familias de materiales, lo que nos permite tener una tabla dinámica  relativamente  sencilla.  Un  número más  elevado  de  datos  en  filas  y  en  columnas  entorpecería  la  presentación  y  conllevaría  problemas  de impresión. En función del número de datos resumidos, puede ser largo y fastidioso expandir o contraer los elementos uno por uno. Para expandir todo el campo:   Haga un clic derecho en una de las celdas de la columna A.   Coloque el cursor sobre la opción Expandir o contraer y a continuación haga clic en Expandir todo el campo.  Para contraer todo el campo:   Haga clic derecho en una de las celdas de la columna A.   Coloque el cursor sobre la opción Expandir o contraer y a continuación, haga clic en Contraer todo el campo. Los botones de expandir y contraer han desaparecido. Para ocultar/mostrar el detalle de un tipo de material, basta con hacer doble clic en el nombre del tipo de material. Ocultar los encabezados de fila y columna:   Pestaña  Herramientas  de  tabla  dinámica  ­  Analizar  ­  grupo  Mostrar,  haga  clic  en  el botón Encabezados de campo. Con este tipo de presentación más sobrio, es más fácil identificar los datos. No se molesta al lector con información que no es esencial para la compresión de la tabla dinámica. Si, en la creación de sus propias tablas dinámicas, tiene un número importante de filas y columnas, conserve  la  visualización  de  los  rótulos  de  campo,  que  le  permitirá  definir  filtros  para  limitar  la visualización de algunos datos. 3. Mostrar los detalles Una  tabla dinámica muestra  resúmenes  calculados a partir de  funciones  como  la  suma,  contar, promedio... Si un valor de  resumen  le parece  sorprendente, puede hacer que, de  forma muy sencilla,  se muestren en una hoja de cálculo separada los detalles de los datos utilizados para ese resumen. Para ver este detalle puede utilizar dos métodos:   Haga doble clic en el valor cuyos detalles quiere ver. O   Haga clic derecho en el valor y seleccione Mostrar detalles.   Para ver esta  técnica, haga doble  clic  en el  importe de NICOLÁS en ESCENARIOS para EMPRESAS (809,64). Se  crea  una  nueva  hoja  con  las  filas  utilizadas  para  el  resumen  elegido.  La  primera  fila  incluye  filtros automáticamente. Para facilitar la lectura hemos aplicado Ajustar texto en la primera fila. Si por ejemplo se ha cometido un error al escribir los datos, será más fácil determinar el origen del error. 4. Utilizar fechas en una tabla dinámica A menudo, puede necesitar resumir fechas en sus tablas dinámicas. Ahora veremos que Excel ofrece herramientas para ayudarnos en nuestras tareas. Excel trata las fechas como los números. Por defecto, el 1 de enero de 1900 es el día 1. El día 1 de enero de 2017 es el número 42736. Lo que vemos en una celda que contiene la fecha es, de hecho, un formato de fecha aplicado a un número. Gracias a esto, podemos realizar cálculos sobre fechas. Nuestro objetivo será calcular las ventas mensuales realizadas por cada comercial. Nuestra tabla dinámica tendrá un máximo de doce filas y tantas columnas como comerciales.   A partir de los datos de la hoja Alquiler, cree la tabla dinámica como se indica a continuación: COMERCIAL en COLUMNAS FECHA FACTURA en FILAS IMPORTE ALQUILER en Σ VALORES Puede observar que Excel ha añadido automáticamente el campo Mes al área de FILAS. De hecho, Excel ha reconocido el campo de fecha y ha creado automáticamente una estructura con los meses. En versiones anteriores de Excel, esto se debía hacer manualmente. La tabla dinámica debe ser como se ve a continuación:   Si tiene una versión anterior a la 2016, utilice este procedimiento para agrupar sus fechas.    Haga clic derecho en una fecha cualquiera de la columna A.   En el menú contextual, seleccione la opción Agrupar.   En  el  cuadro  de  diálogo  que  aparece,  la  opción Meses  está  seleccionada  por  defecto.  Haga  clic directamente en Aceptar. 5. Aplicar un estilo predefinido Cuando crea una tabla dinámica, Excel aplica una presentación estándar a la tabla. Si este formato no le convence, puede aplicar le entonces uno de los estilos predeterminados de Excel.   En  la  pestaña Herramientas  de  tabla  dinámica  ­  Diseño,  despliegue  el  menú Más    del grupo  Estilos  de  tabla  dinámica  para  visualizar  los  estilos  disponibles.  Se  dividen  en  tres grupos: Claro, Medio y Oscuro.   Coloque  el  cursor  sobre  los  estilos  ofrecidos  para  visualizar  automáticamente  las modificaciones  de formato aportadas por el estilo en la tabla dinámica.   Para aplicar un estilo, haga clic en él. 6. Los estilos personalizados Para  personalizar  sus  tablas,  tiene  la  posibilidad  de  crear  sus  propios  estilos.  El  formato  de  cada  elemento  es personalizable y puede crear el estilo a partir de cero o bien duplicar y después modificar un estilo existente. a. Crear un estilo a partir de uno ya existente Para delimitar mejor cada una de las celdas de nuestras tablas dinámicas, vamos a modificar un estilo añadiendo bordes exteriores e  interiores gris oscuro. A continuación, aplicaremos a  los subtotales con fuente de color rojo y negrita.   Haga clic en la tabla dinámica.   En la pestaña Herramientas de  tabla dinámica  ­ Diseño,  grupo Estilos de  tabla dinámica, despliegue el menú Más  .   En  la  categoría Medio,  haga  clic  derecho  en  el  estilo  Estilo  de  tabla  dinámica  medio  1  y seleccione Duplicar. Aparece el cuadro de diálogo Modificar estilo de tabla dinámica.   Aplique el formato definido a nuestra tabla dinámica haciendo clic en el estilo Marcos 1. b. Crear un estilo a partir de cero Recuerde que la función principal de una tabla dinámica es proporcionar información sobre estadísticas numéricas. La presentación es un elemento visual que debe mantener la transparencia del contenido. Su estilo de presentación debe facilitar la lectura de los números, y no dificultarla. Cuando cree estilos, conviene que siga algunas reglas sencillas: No utilice demasiados colores. No utilice más de dos fuentes distintas. Defina  bordes  interiores  y  exteriores  para  delimitar  bien  los  valores,  o  defina  trazos  verticales entre las columnas. Aplique el formato negrita a los campos importantes. Vamos a crear un ejemplo sencillo: Formato de tabla Borde exterior doble y borde interior punteado simple. Formato  de  título  y totales Fuente en negrita, color azul para las etiquetas de columnas   Haga clic en la tabla dinámica.   En la pestaña Herramientas de  tabla dinámica  ­ Diseño,  grupo Estilos de  tabla dinámica, despliegue el menú Más  , y haga clic en Nuevo estilo de tabla dinámica.   Introduzca el nombre de su nuevo estilo: Marco 2.   En  la  sección  Elemento  de  tabla,  haga  clic  en  Toda  la  tabla  y  a  continuación,  en  el botón Formato.   En  la  pestaña  Borde,  seleccione  el  estilo  de  trazo  doble  y  a  continuación,  haga  clic  en  el botón Contorno.  Seleccione  a  continuación  el  estilo  de  trazo  punteado  simple  y  haga  clic  en  el botón Interior.   Haga clic en Aceptar.   Seleccione  Fila  de  encabezado  en  la  sección  Elemento  de  tabla,  y  haga  clic  en  el botón Formato.   En  la  pestaña Fuente,  en  la  sección  Estilo,  haga  clic  en Negrita  y  a  continuación,  seleccione un Color de fuente azul claro.   Haga clic en Aceptar.   Seleccione  Columna  de  totales  generales  en  la  sección  Elemento  de  tabla,  aplique  el formato Negrita a la fuente y haga clic en Aceptar.   Seleccione  la  Fila  de  totales  generales,  aplique  el  formato Negrita  a  la  fuente  y  haga  clic en Aceptar.   Despliegue el menú Más   del grupo Estilos de tabla dinámica. Nuestro estilo se ha añadido a la categoría Personalizado. Si  desea  que  un  estilo  sea  el  estilo  predeterminado,  en  la  pestaña Herramientas  de  tabla dinámica ­ Diseño, grupo Estilos de tabla dinámica, haga clic derecho en la miniatura del estilo y seleccione Establecer como predeterminado. c. Modificar un estilo Si la presentación obtenida con un estilo personalizado no le satisface, es posible modificarla.   En  la pestaña Herramientas de  tabla dinámica  ­ Diseño,  grupo Estilos de  tabla dinámica, despliegue el menú Más  ,  y a  continuación, haga clic derecho en el  estilo personalizado que desea modificar.   Haga clic en la opción Modificar. Observe que los elementos que tienen formato aparecen en negrita.   Para modificar  el  formato  de  un  elemento,  haga  clic  en  su  nombre  en  la  sección Elemento  de tabla y haga clic en el botón Formato. Para eliminarlo, haga clic en el botón Borrar. 7. Aplicar un formato condicional Excel  ofrece  la  posibilidad  de  aplicar  un  formato  condicional  a  los  datos  de  las  tablas  dinámicas.  Esta  opción  es independiente de los estilos y permite configurar un formato que se aplicará a los valores en función de uno o varios criterios. También es posible combinar los estilos y formatos condicionales en los informes de tabla dinámica. En nuestro ejemplo, queremos clasificar las ventas mensuales en tres categorías: Cruz roja cuando el total es inferior a 5.000 €. Signo de exclamación amarillo cuando el total está entre 5.000 € y 10.000 €. Marca verde cuando el total es superior a 10.000 €. Los segmentos: convertir sus tablas dinámicas en interactivas 1. Utilidad Los segmentos son elementos visuales que permiten filtrar los datos de las tablas dinámicas de manera interactiva e intuitiva. Insertando un segmento, puede utilizar botones para filtrar y segmentar rápidamente  los datos de modo que se muestre solo la información deseada. Mediante la combinación de segmentos puede afinar sus análisis. 2. Técnica Vamos a utilizar nuestra última tabla dinámica para poner en práctica la técnica de los segmentos.    Haga clic en la tabla dinámica.   En la pestaña Herramientas de tabla dinámica ­ Analizar, grupo Filtrar (Ordenar y  filtrar  en Excel 2010), haga clic en el botón Insertar Segmentación de datos. Aparece la ventana Insertar segmentación de datos.   Marque solo TIPO MATERIAL y haga clic en Aceptar. El segmento definido con el tipo de material aparece al lado de la tabla dinámica. Por defecto, se seleccionan todos los elementos y la tabla dinámica no se filtra. Aplicar un filtro a una tabla dinámica es muy sencillo; basta con seleccionar el elemento deseado.   Para obtener las estadísticas de los alquileres de carpas, haga clic en el botón correspondiente. La tabla dinámica se actualiza instantáneamente.   Para que la tabla dinámica resuma los datos sumados relativos a las mesas y a los escenarios, haga clic en los botones mientras mantiene pulsada la tecla [Ctrl].   Para borrar el filtro, haga clic en el botón Borrar filtro   o utilice el atajo de teclado [Alt] C. La utilidad de los segmentos reside en que se pueden combinar. Para comprobarlo, añada un nuevo segmento relativo a los tipos de cliente:   En  la  pestaña  Herramientas  de  tabla  dinámica  ­  Analizar,  grupo  Filtrar,  haga  clic  en  el botón Insertar Segmentación de datos.   En  el  cuadro  de  diálogo Insertar  segmentación  de  datos,  marque  TIPO  CLIENTE  y  haga  clic en Aceptar. La escala de tiempo FECHA FACTURA se inserta al lado de la tabla dinámica. Por defecto, los periodos de tiempo se miden en meses. No dude en arrastrar una de las esquinas para hacer más grande su escala de tiempo. A continuación, se representan los diferentes elementos de una escala de tiempo: Una vez ha definido su escala de tiempo, ya está listo para filtrar por periodo en uno de los cuatro niveles de tiempo (años, trimestres, meses o días).   Para modificar el periodo de tiempo, haga clic en la pequeña flecha situada a la derecha del nivel de tiempo y seleccione el nivel deseado.   Para  aumentar  el  periodo  de  tiempo  que  hay  que  analizar,  haga  clic  en  un mosaico  de  periodo  y arrastre el ratón para incluir otros mosaicos y seleccionar el rango de fechas deseado.   También puede arrastrar  los extremos de cada  lado del  intervalo ya seleccionado para aumentar o reducir el rango.   Para borrar la escala de tiempo aplicada, haga clic en el botón Borrar filtro   o utilice el atajo de teclado [Alt] C. Si quiere asociar una escala de tiempo con segmentos para definir filtros sobre un mismo campo de fecha, hay que activar el campo Permitir varios filtros por campo en el cuadro de diálogo Opciones de tabla dinámica:   Haga clic derecho en la tabla dinámica.   Haga clic en la opción Opciones de tabla dinámica.   En la pestaña Totales y filtros, marque la opción Permitir varios filtros por campo.   Haga clic en Aceptar. Por supuesto,  también puede combinar segmentos y escalas de  tiempo. A continuación, puede ver un ejemplo de asociación de dos segmentos y de una escala de tiempo. Si ha seleccionado dos periodos consecutivos en su filtro, arrastre uno de los mosaicos mientras mantiene pulsada la tecla [Ctrl]. Si había seleccionado dos meses (por ejemplo, enero y  febrero), puede arrastrar el  filtro de escala de Seguimiento de stocks de materias primas 1. Resúmenes y cálculos   Abra el archivo GestionStocks.xlsx. La hoja SeguimientoStock de este libro registra todos los movimientos de stocks (en kgs) relativos a las materias primas utilizadas en una fábrica de pasteles con frutos secos. La gestión de los stocks se realiza por materia prima con un subtotal por número de lotes. a. Total de entradas­salidas por materia y número de lote   Cree  la  tabla  dinámica  a  partir  de  la  hoja  Seguimientostock,  utilizando  el  cuadro  de diálogo Campos de tabla dinámica. La tabla muestra los movimientos de stock por producto, detallados por lote. Al arrastrar los campos CANT. ENTRADA y CANT. SALIDA a la zona Σ VALORES, Excel utiliza automáticamente la función Cuenta porque estos campos tienen celdas vacías. Si las celdas vacías se hubieran sustituido por ceros, se habría aplicado la función Suma.   Modifique  en  estos  dos  campos  la Configuración  de  campo  de  valor,  abra  la  lista  del  primer campo y haga clic en la opción Configuración de campo de valor.   Seleccione  la  función  Suma  e  introduzca  CANT.  ENTRADA  TOTAL  en  la  sección  Nombre personalizado.   Modifique el segundo campo como en la siguiente captura.   Finalmente, modifique el texto de la etiqueta de las filas: Productos ­ Nº Lote. Este es el aspecto de las primeras filas de la tabla insertada: Para insertar los campos CANT. ENTRADA y CANT. SALIDA en la sección Fórmula, puede hacer doble clic en el nombre del campo, en la lista de campos.   Haga clic en el botón Sumar y haga clic en Aceptar. El campo calculado CANT. EN STOCK se ha añadido a la lista de campos; el panel tiene ahora la cantidad en stock en la sección Σ VALORES.   Modifique la etiqueta de la nueva columna. La tabla dinámica muestra la cantidad de stock por materia y por lote. Cuando  no  quede  ninguna  cantidad  para  un  lote,  no  hay  que  no  mostrar  las  líneas  de  los  lotes  que  se  han consumido completamente. Podría pensar en aplicar un filtro en la columna CANTIDAD EN STOCK, pero esto no es posible directamente. Hay que utilizar la solución que se detalla a continuación.   Haga  clic  en  la  primera  columna de  la  tabla  dinámica  (en  el  nombre de un número de  lote,  por ejemplo en A5).   Despliegue la lista situada a la derecha de la etiqueta Productos ­ N° Lote.   Seleccione la opción Filtros de valor.   Haga clic en la opción Mayor que.... Num Fila  corresponde  al  número de  filas  hacia  abajo  (si  es  positivo)  o  hacia  arriba  (si  es  negativo)  que  se  debe desplazar la celda. Num Col corresponde al número de columnas hacia la derecha (si es positivo) o hacia la izquierda (si es negativo) que se debe desplazar la celda. Alto es el número de filas del rango devuelto. Ancho es el número de columnas del rango devuelto. La función DESREF no desplaza físicamente las celdas, sino que devuelve solo las coordenadas de un rango de celdas cuyas dimensiones se han modificado o desplazado respecto al rango definido en el parámetro Celda. Para ilustrar el uso de esta función, vamos utilizar nuestro libro GestionStocks.xlsx. Por definición, el número de filas aumenta cuando un nuevo lote de materias primas entra en stock. Vamos a construir una segunda tabla dinámica a partir de la misma lista de datos que antes. Así podremos comparar los dos métodos de creación cuando se añadan filas al origen de los datos. a. Método de definición de un rango dinámico (número de filas variables)   Active la hoja SeguimientoStock.   En la pestaña FÓRMULAS, grupo Nombres definidos, haga clic en el botón Administrador de nombres, o bien utilice el atajo [Ctrl][F3].   En el cuadro de diálogo Administrador de nombres, haga clic en el botón Nuevo.   Introduzca  el  nombre  del  rango  MovimientosStocks  e  introduzca  la  siguiente  fórmula  en  la sección  Se  refiere a: =DESREF(SeguimientoStock!$A$1;;;CONTARA(SeguimientoStock!$A$1:$A$2000);5) El rango se ha limitado a 2000 filas; hemos considerado que este año no habrá más de 1999 movimientos. Cuando defina un nuevo nombre, no inserte espacios en él y evite utilizar nombres que puedan ser nombres de celda. Ej.: JUN2017 si quiere hacer referencia a junio de 2017, ya que esta es la celda JUN2017.   Haga clic en Aceptar. El nombre se ha añadido a la lista del Administrador de nombres.   Haga clic en Cerrar para terminar. La  fórmula  DESREF(SeguimientoStock!$A$1;;;NBVAL(SeguimientoStock!$A$1:$A$2000);5)  es equivalente  a  DESREF(SeguimientoStock!$A$1;0;0;NBVAL(Seguimiento­ Stock!$A$1:$A$2000);5). Los dos parámetros Num Fila y Num Col están a cero ya que no queremos realizar ningún desplazamiento del rango.   Construyamos ahora la tabla dinámica: haga clic en la lista origen y, en la pestaña Insertar, haga clic en el botón Tabla dinámica. Excel selecciona automáticamente el rango SeguimientoStock!$A$1:$E$216. Este conjunto de celdas es fijo, vamos a remplazarlo por nuestro rango dinámico: introduzca la fórmula =MovimientosStocks en el lugar de las coordenadas y haga clic en Aceptar.   Cree la misma tabla dinámica que anteriormente.   Modifique los parámetros de los campos de la sección Σ VALORES.   Inserte el campo calculado CANT. En STOCK (T2) (T2 para tabla 2).   Modifique el nombre de las etiquetas.   Aplique un filtro que permita mostrar las "CANT.EN STOCK" mayores que 0.   Para  comprobar  si  el  rango  dinámico  funciona,  añada  manualmente  tres  filas  al  final  de  la hoja SeguimientoStock (filas 217 a 219). b. Actualizar la tabla   Haga clic en la primera tabla dinámica y actualice todo ([Ctrl][Alt][F5]). Esta tabla no ha cambiado y los nuevos lotes no se han añadido. Compruebe ahora la tabla creada con la ayuda del rango dinámico: los nuevos lotes se han insertado automáticamente en la tabla dinámica. Este método permite seleccionar solamente los elementos que quiera analizar. Si hace clic en los elementos mientras mantiene pulsada la tecla [Ctrl], podrá seleccionar también elementos no consecutivos.  También  puede  usar  esta  funcionalidad  activando  el  botón  Selección  múltiple  para  realizar selecciones discontinuas. b. Con la ayuda de una nueva columna calculada Para simplificar nuestro sistema de filtros, puede ser interesante filtrar solo la primera palabra de la descripción del producto. Uno de los métodos posibles consiste en utilizar el filtro Comienza por, en los filtros que se aplican en las etiquetas de la tabla dinámica. Esto nos obliga a introducir cada vez un texto con el teclado. Vamos a crear una fórmula que permita extraer la primera palabra de una cadena de caracteres. Su  primera  intención  será  quizás  insertar  un  campo  calculado  en  la  tabla  dinámica.  Sin  embargo,  un  campo calculado no se puede insertar en la sección FILTROS de una tabla dinámica. La solución es añadir una columna a nuestra lista de datos de origen.   Añada la columna PROD en la hoja SeguimientoStock con la fórmula: =IZQUIERDA(B2;ENCONTRAR(" ";B2)­1)   Copie la fórmula hacia abajo. La función ENCONTRAR devuelve la posición del elemento buscado (en este caso, un espacio) en una cadena de caracteres. La función IZQUIERDA devuelve los n primeros caracteres de una cadena. Nuestra hoja tiene ahora seis columnas. Cuando se creó el nombre MovimientosStocks, nuestra  lista de datos estaba compuesta por cinco columnas solamente. El ancho de nuestra lista se define con el último parámetro de la función DESREF. =DESREF(SeguimientoStock!$A$1;;;CONTARA(SeguimientoStock!$A$1:$A$2000);5) Se debe actualizar este parámetro en nuestra fórmula.   En  la pestaña Fórmulas,  grupo Nombres definidos,  haga  clic  en  el  botón Administrador  de nombres.   En la sección Se refiere a, sustituya el 5 por un 6.   Actualice los datos ([Ctrl][Alt][F5]). El campo PROD se ha añadido a la lista de campos de la segunda tabla dinámica.   Arrastre el campo PROD a la sección FILTROS. Ahora es posible filtrar por nombre genérico de producto.   Despliegue el filtro PROD.   Para seleccionar las almendras y las nueces, marque la opción Seleccionar varios elementos  y seleccione solo estos dos productos.