“La inteligencia y conocimiento del negocio, consiste en convertir datos en
información útil en forma de indicadores, y en utilizar esos datos para conocer
y medir el grado de eficacia en las diferentes áreas o responsables de una
organización para que tomen las decisiones más adecuadas.”
Sisconges & Estrategia [email protected]
c/ Rosellón, 297 08037 Barcelona www.sistemacontrolgestion.com [email protected]
Tabla de contenido
www.sistemacontrolgestion.com [email protected]
Contenido
1. Presentación ____________________________________________________________ 1
2. Introducción _____________________________________________________________ 2
3. Prólogo ________________________________________________________________ 9
4. Índice _________________________________________________________________ 12
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Tabla de contenido
www.sistemacontrolgestion.com [email protected]
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 01
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
1. Presentación
Éste es el tercer libro sobre tablas dinámicas que escribo; después de
implementar más de cincuenta sistemas de reporting y de realizar más de
cuarenta seminarios en escuelas de negocios y en empresas, aparece este
nuevo libro que recoge el contenido del anterior pero actualizado a la nueva
versión de Excel 2013, con todas sus novedades y con un enfoque mucho más
orientado aún hacia la gestión empresarial.
Llegamos de nuevo a Excel 2013, que nos acerca todavía más a la gestión del
business intelligence o inteligencia del negocio, con la gran ventaja de que el
libro permite a usuarios de diferentes áreas de la empresa aprender, a la par
que ampliar conocimientos y practicar con los ejemplos explicados, el
funcionamiento y las ventajas de las tablas dinámicas en esta nueva versión,
que nos trae también nuevas herramientas que le otorgan así una potencia sin
igual. El libro está perfectamente estructurado para que el usuario pueda aplicar
todos los conocimientos adquiridos de forma ordenada y paulatina a la práctica.
Utiliza ejemplos, explica toda la teoría y abarca casi todo el potencial de las
tablas dinámicas que el lector tiene a su alcance. Todo ello hace que este
manual sea indispensable para que las tablas dinámicas nos ayuden en el
trabajo de cada día mejorando así nuestra gestión.
Luis Muñiz
Socio-Director de Sisconges & Estrategia [email protected] Más información en www.sistemacontrolgestion.com apartado de Publicaciones
y en Linkedin nuestro grupo: Tablas dinámicas sistemas de información
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 02
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
2. Introducción
Excel como hoja de cálculo es una de las herramientas de software de gestión
más conocidas. En su versión 2013 nos aporta nuevas prestaciones en cuanto
a su funcionalidad como herramienta de business intelligence o reporting y, por
supuesto, su potencial como sistema de información para todo tipo de usuarios.
Este nuevo libro, Tablas dinámicas con Excel aplicadas a la gestión
empresarial. Para utilizar con Excel 2007, 2010 y 2013, nos acerca mucho más
a su potencial como herramienta de trabajo en la empresa y consigue
profundizar en su funcionamiento y práctica a la vez. Todo ello exige por parte
del lector un conocimiento de la herramienta pero también un conocimiento de
lo que quiere hacer previamente y, lo más importante, qué tipo de información
quiere obtener al final del proceso.
La esencia de las tablas dinámicas
Las tablas dinámicas son una herramienta de Excel muy avanzada y potente,
con ellas se pueden analizar, mostrar y manipular los datos de diferentes
formas, más ampliados o más resumidos según se precise. Si todavía no ha
descubierto el potencial de las tablas dinámicas, este libro muestra paso a paso
cómo entender, crear y practicar con ellas (los numerosos casos prácticos le
darán una visión mucho más amplia de su utilidad y funcionamiento), y podrá
comprobar así lo fácil que es crear diferentes tipos de informes. Las tablas
dinámicas son un tipo de informes generados mediante Excel en diferentes
formatos, a partir de una base de datos inicial siempre en forma de tabla. Esta
base de datos puede estar situada en el mismo archivo de Excel que estemos
trabajando, en otro archivo de Excel o en otro archivo de datos externo. Las
tablas de datos (bases de datos) pueden poseer muchas filas y columnas que
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 03
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
contengan diferentes tipos de datos, las tablas dinámicas nos ayudarán a
analizarlos de una forma sencilla, resumida y rápida. Si tenemos diferentes
tablas de datos, para analizar la información contenida en ellas, se pueden
utilizar diferentes opciones de Excel para ordenarlos, agruparlos e incluso crear
funciones, pero las tablas dinámicas son mucho más prácticas de utilizar y nos
permiten crear, a partir de la información inicial, muchos y variados tipos de
informes que responderán a las diferentes necesidades de información que
tengamos en cada momento. Una de las principales características que poseen
las tablas dinámicas es la facilidad que tienen para analizar los datos desde una
forma resumida a una más amplia y desde diferentes ángulos o perspectivas.
Así, las tablas dinámicas desde sus diferentes opciones o vistas facilitarán la
información de distinta forma y nos sorprenderá gratamente la capacidad que
tienen de dar respuesta a las diferentes situaciones o necesidades, dado que la
información que necesitamos en un momento del tiempo no es la misma que
necesitamos en otro ya que el entorno de la empresa y su organización interna
son cambiantes. El secreto de las tablas dinámicas curiosamente no está tanto
en la funcionalidad de la herramienta y su potencialidad sino en el origen y las
características de los datos que se utilicen. Los datos de origen deben tener
siempre formato de tabla y podrán ser datos numéricos, valores calculados o
categorías que describen el significado de los datos (mes, año, zona, país,
etcétera). Y otra característica importante a tener en cuenta es conocer, antes
de confeccionar las tablas dinámicas, qué información necesitamos. Para
obtener las respuestas correctas, previamente deben hacerse las preguntas
adecuadas.
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 04
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
Cómo nos pueden ayudar las tablas dinámicas a mejorar la
gestión
Para mejorar la gestión de una empresa, es necesario tener información al
alcance de una forma rápida y completa. Antes de empezar a leer el libro,
repase las siguientes preguntas y compruebe su respuesta en su organización
actual. Las tablas dinámicas le ayudarán a mejorar, sin duda, la gestión de su
empresa y, dependiendo de cómo se analice la información, serán una
herramienta de gran ayuda.
¿Le interesa una herramienta para acelerar y facilitar la información de gestión al resto de usuarios?
¿Necesita un sistema de información para resolver los problemas de inconsistencia y calidad de los datos?
¿Necesita reducir la dependencia de técnicos para que le faciliten información?
¿Le interesa facilitar el acceso a la información a la alta dirección de forma rápida y directa?
¿Necesita mejorar la eficiencia en la elaboración y el análisis de la información?
¿Requiere facilitar la elaboración y entrega de informes a usuarios finales de forma rápida y eficaz?
¿Requiere mejorar la capacidad de análisis y toma de decisiones en la empresa?
¿Quiere eliminar los informes en papel y procesos de distribución física actuales?
¿Pasa más tiempo recolectando y preparando información que analizándola?
¿Quiere saber qué productos o servicios fueron los más rentables durante un período determinado?
¿Sabe cuáles son los patrones de compra de sus clientes dependiendo de las zonas de ventas?
¿Ha perdido oportunidades de negocio por recibir información retrasada?
¿Conoce los motivos por los que sus clientes le devuelven sus productos?
¿Dispone de un sistema de información que le permita el control de los factores clave del negocio?
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 05
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
¿Su sistema de información integra actualmente de forma eficiente todas las áreas de su empresa?
¿Controla en tiempo y contenido la evolución de sus ingresos y gastos?
¿Controla la evolución de las desviaciones presupuestarias?
¿Dedica un excesivo tiempo y recursos en el proceso y envío de información?
¿Conoce las ventas de los nuevos clientes por importes y tipos de productos o servicios?
¿Conoce la rentabilidad de las principales familias de productos o servicios?
¿Conoce el impacto que tendría una reducción de ventas en los productos o servicios que más contribuyen en el margen de explotación?
¿Conoce la relación entre los productos que más vende y el margen que aportan?
¿Conoce la relación entre los clientes que más ventas aportan y el margen que aportan?
¿Conoce los diferentes indicadores de gestión por responsables o áreas?
¿Conoce la evolución de cuáles fueron las ventas por categoría, productos, familias y por día, mes o año?
¿Conoce cuáles fueron los resultados de cada vendedor o representante?
¿Conoce cuáles han sido los clientes más importantes a nivel de ventas y rentabilidad?
¿Conoce qué productos se han vendido mejor en cada zona, ciudad, región o país?
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 06
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
Resumen del contenido de este libro
Parte 1: Introducción práctica a las tablas dinámicas
1. La esencia de las tablas
dinámicas como sistema de
información
2. Funcionamiento y contenido
de las tablas dinámicas
3. Contenido y funcionamiento
del menú de Analizar de una
tabla dinámica
4. Contenido y funcionamiento
del menú Diseño de las tablas
dinámicas
El primer capítulo introduce al
lector en la gestión de la
información como soporte de la
inteligencia del negocio. Los
capítulos 2, 3 y 4 nos explican
el funcionamiento de la
herramienta de forma muy
exhaustiva siguiendo los menús
y proporcionando información
de otras versiones.
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 07
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
Parte 2: Herramientas de reporting y business intelligence con
tablas dinámicas
5. La creación de
indicadores mediante el
formato condicional
6. Crear relaciones entre
diferentes tablas dinámicas
7. Utilizar la segmentación
para filtrar datos en las
tablas dinámicas
8. La creación de gráficos
con tablas dinámicas
Estos capítulos tratan temas muy
concretos para que el lector pueda
profundizar en ellos, le ayudan a crear
indicadores, segmentaciones, gráficos
dinámicos y, por último, la gran novedad
de Excel 2013, que es poder relacionar
tablas dinámicas entre ellas para
obtener nuevos datos, opción similar al
complemento de Powerpivot que ya
viene de serie en Excel 2013.
S
I
S
C
O
N
G
E
S
&
E
S
T
R
A
T
E
G
I
A
Pág. 08
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
Parte 3: Ejemplos y casos prácticos con tablas dinámicas
9. Caso práctico global
sobre el funcionamiento de
las tablas dinámicas
10. Ejemplos y casos
prácticos para utilizar las
tablas dinámicas
Estos dos capítulos contienen casos
prácticos de gestión empresarial en los
que el lector descubrirá la parte más
práctica de las tablas dinámicas y cómo
se pueden aplicar para mejorar la
gestión de los diferentes departamentos
o áreas de una organización.
Anexo: Diferencias y novedades entre los menús de Excel 2007-2010-2013
Pág. 09
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
3. Prólogo
Conocí a Luis Muñiz en el año 2008, cuando acababa de publicar el primer libro
de tablas dinámicas, versión 2007; un grupo de asesorías de Cataluña y otras
autonomías españolas teníamos una reunión de benchmarking en Barcelona y
uno de los asesores nos lo presentó como un profesional que podía ayudarnos
a mejorar nuestro sistema de información.
Luis era entonces un consultor reconocido en Cataluña por sus publicaciones
y sus trabajos; fue el primer autor que en España publicaba un libro sobre las
tablas dinámicas como herramienta de gestión empresarial. Compartimos con
él una reunión de trabajo para conocer las ventajas de la herramienta como
sistema de reporting y decidimos aplicarlas en nuestra labor diaria en los
trabajos del día a día que luego desglosaré.
Ese fue el comienzo de una gran amistad entre ambos y de una amplia relación
profesional con la asesoría que presido y con el resto de las asesorías. Por el
intenso trato que hemos tenido desde esa época quiero destacar aspectos
personales y profesionales que acerquen el autor al lector; he preferido aportar
esta visión en el prólogo ya que los aspectos técnicos están muy bien
desarrollados en la introducción y yo prefiero centrarme en los beneficios que
nos ha aportado a nuestra asesoría.
Luis es un trabajador infatigable, amante de su profesión, dedicado, entregado,
entusiasta; siempre dispuesto a analizar las tendencias económicas tanto
españolas como internacionales; si le buscas siempre le encuentras dispuesto
a ayudarte, como me ha demostrado en diferentes ocasiones; además es un
buen conversador, familiar, de trato cercano, con enormes ganas de aprender;
gran fuerza de voluntad, constante, metódico, gran capacidad de retención y
Pág. 10
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
comprensión; además aportando soluciones prácticas a los problemas
empresariales.
Con la ayuda de Luis, nuestra asesoría está aplicando las tablas dinámicas en
trabajos como en el análisis de costos, control de las tareas de los técnicos y su
seguimiento de trabajos, rentabilidad de cada uno nuestros clientes,
planificación de trabajo y aplicación de recursos, cuadro de mando, plan de
gestión integral, informes de ventas, por zonas, segmentos de clientes; análisis
y diagnósticos financieros, etcétera. Hemos ahorrado tiempo, conseguido
seguridad e información ágil, transcendente y necesaria, de forma automática
y fácil. Todo el personal de Pyramide estudia, practica y trabaja con las tablas
dinámicas cuyo resultado es disponer en el personal técnico y administrativo
una mentalidad de trabajo mucho más práctica y centrada en resultados.
Como ejemplo palpable de todo lo anterior y para entender la aceptación que
tiene Luis Muñiz como escritor de libros de gestión empresarial quiero
comentar algunas anécdotas vividas en primera persona. En una librería de
Pamplona quise comprar un libro de Luis y el encargado me respondió que
disponía en aquel momento de 6 títulos o cuando siendo alumno a un curso de
cuadro de mando integral, el profesor se apoyaba en sus explicaciones con
copias de su libro, recomendando el texto por su fácil lectura y facilidad de
comprensión y fácil aplicación.
En este libro Tablas Dinámicas con Excel aplicadas a la gestión empresarial, el
tercero que publica, relacionado con tablas dinámicas, versión 2013; podremos
aprender todas y las variadas ventajas de aplicarlas en la gestión diaria de la
empresa, que el lector observará son evidentes, siendo nuestra asesoría uno
ejemplo real de ello, pero no el único, muchas otras empresas también lo han
podido comprobar, gracias a la ayuda de Luis y su equipo.
Pág. 11
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
No es extraño que Luis sea uno de los autores más leídos en temas de gestión
empresarial y sobretodo es un referente a nivel internacional en la implantación
de sistemas de reporting utilizando las tablas dinámicas.
JOSÉ LOBETE CARDEÑOSO Licenciado en Derecho y Master MBA Deusto Presidente de Pyramide Asesores- Pamplona
Pág. 12
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
4. Índice
Nota del autor
Prólogo
Introducción
Parte 1: Introducción práctica a las tablas dinámicas
1. La esencia de las tablas dinámicas como sistema de información
1.1 Las necesidades de información en la empresa
1.2 Contenido y funcionamiento de un sistema de información
1.3 Ejemplo práctico: análisis comparativo entre datos en Excel y datos con
tablas dinámica
1.4 Cómo clasificar y analizar los datos
2. Funcionamiento y contenido de las tablas dinámicas
2.1 Cómo crear una tabla dinámica según el origen de datos
2.2 Principales apartados de una tabla dinámica
2.3 Funcionamiento de los campos de una tabla dinámica
2.4 Diferentes vistas según los campos de la tabla dinámica
3. Contenido y funcionamiento del menú de Analizar de una tabla dinámica
3.1 Menú Nombre y Opciones de la tabla dinámica
3.1.1 Nombre de la tabla dinámica
Pág. 13
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
3.1.2 Opciones de la tabla dinámica
3.1.3 Menú Opciones de la tabla dinámica
3.1.4 Menú Opciones: Diseño y formato
3.1.5 Menú Opciones: Totales y filtros
3.1.6 Menú Opciones: Mostrar
3.1.7 Menú Opciones: Impresión
3.1.8 Menú Opciones: Datos
3.1.9 Menú Opciones: Texto alternativo
3.1.10 Menú Opciones: mostrar páginas de filtros de informe
3.1.11 Menú Opciones: Generar GetPivotData
3.2 Campo activo
3.2.1 Descripción del campo activo
3.2.2 Funcionamiento del campo activo de tipo valor
3.2.3 Configuración de campos de filas, columnas y filtros
3.3 Agrupar selección
3.4 Filtrar
3.4.1 Segmentación de datos
3.4.2 Insertar escala de tiempo
3.5 Datos
3.5.1 Actualizar
Pág. 14
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
3.5.2 Cambiar origen de datos
3.6 Acciones
3.6.1 Borrar
3.6.2 Seleccionar
3.6.3 Mover tabla dinámica
3.7 Cálculos
3.7.1 Campos, elementos y conjuntos
3.8 Herramientas
3.9 Mostrar
3.10 Las opciones de menú directas en una tabla dinámica
3.10.1 Ordenar
3.10.2 Resto de opciones del menú desplegable
4. Contenido y funcionamiento del menú Diseño de las tablas dinámicas
4.1 Menús de Diseño
4.1.1 Subtotales
4.1.2 Totales generales
4.1.3 Diseño de informe
4.1.4 Filas en blanco
4.2 Opciones de estilo de tabla dinámica
4.3 Estilos de tabla dinámica
Pág. 15
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
Parte 2: Herramientas de reporting y business intelligence con tablas
dinámicas
5. La creación de indicadores mediante el formato condicional
5.1 Introducción al formato condicional
5.2 Resaltar reglas de celdas y tipos de reglas superiores e inferiores
5.3 Barras de datos, escalas de color y conjuntos de iconos
5.4 Introducción al formato condicional
5.5 Borrar reglas
5.6 Administrar reglas
6. Crear relaciones entre diferentes tablas dinámicas
6.1 Ejemplo práctico sobre cómo crear una relación entre tablas
6.2 Introducción a PowerPivot: complemento de Excel para importar y
relacionar varias tablas
7. Utilizar la segmentación para filtrar datos en las tablas dinámicas
7.1 Crear una segmentación de datos para filtrar datos de tablas dinámicas
7.2 Opciones de la segmentación de datos
7.3 Compartir segmentaciones de datos entre tablas dinámicas
7.4 Desconectar y eliminar segmentaciones de datos en tablas dinámicas
8. La creación de gráficos con tablas dinámicas
8.1 Diferentes formas de crear un gráfico dinámico
8.2 Resumen de las características de los menús de los gráficos
Pág. 16
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
8.3 Ejemplos de diferentes tipos de gráficos
Parte 3: Ejemplos y casos prácticos con tablas dinámicas
9. Caso práctico global sobre el funcionamiento de las tablas dinámicas
9.1 Enunciado del caso práctico
9.2 Desarrollo del caso práctico
9.2.1 Análisis de las ventas por países
9.2.2 Análisis de las ventas por ciudades
9.2.3 Análisis de las ventas por vendedores
9.2.4 Análisis de situaciones especiales
10. Ejemplos y casos prácticos para utilizar las tablas dinámicas
10.1 Análisis de la información de una TPV: terminal punto de venta en una
tienda
10.2 Análisis de la gestión de productividad de una fábrica mediante
indicadores
10.3 Seguimiento del planning de un proyecto
10.4 Análisis de las bases de datos de un club de socios
10.5 Análisis de inversiones en inmovilizado
10.6 Control de la gestión comercial de vendedores, clientes y productos
10.7 Análisis de la rentabilidad y de las ventas por productos
10.8 Análisis de ingresos y gastos
10.9 Análisis de una cuenta de explotación
Pág. 17
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
10.10 Control de la cartera a cobrar por clientes y vendedores
Anexo: Diferencias y novedades entre los menús de Excel 2007-2010-2013
Pág. 18
Tablas Dinámicas con Excel aplicadas a la gestión empresarial
www.sistemacontrolgestion.com [email protected]
Sisconges & Estrategia
c/ Rosellón, 297 08037 Barcelona
Presentación
Somos expertos en implantar herramientas de control de gestión y estrategias de reestructuración. Desde el
año 2005 Sisconges & Estrategia es una consultora que utiliza los instrumentos más eficaces de control de
gestión y los sistemas de información para mejorar la gestión empresarial y ayudar a conseguir los objetivos
estratégicos previstos de nuestros clientes.Nuestra filosofía de trabajo se fundamenta en la colaboración
constante con nuestros clientes y en el diseño soluciones a medida que satisfagan sus necesidades. Nuestro
tamaño y la excelente cualificación de nuestros profesionales nos permiten ofrecer, como valor añadido, un
servicio personalizado y de alta calidad a unos precios competitivos.
NUESTROS SERVICIOS
REPORTING
•Diseño de sistemas de reporting mediante consultas dinámicas
Mediante la utilización de las tablas dinámicas, herramienta de la que poseemos una elevada experiencia en
numerosas implantaciones, diseñamos un sistema de reporting para cada una de las áreas de la empresa mediante
informes dinámicos, indicadores y gráficos.
SISTEMA CONTROL GESTIÓN •Evaluación y mejora del sistema de control de gestión •Cuadro de mando mediante indicadores por áreas o responsables •Diseño e implantación de presupuestos
ESTRATEGIAS DE REESTRUCTURACIÓN
•Diseño y seguimiento de planes de negocio y estudios de viabilidad
•Planes de marketing y estrategias comerciales
•Cuadro de Mando Integral
PLAN DE GESTIÓN INTEGRADO
•Plan de Gestión Integrado
FORMACIÓN
•Formación en diferentes áreas de control de gestión