Función BUSCARV en Excel

15 julio, 2017 por Fernando | 0 Comentarios

La función BUSCARV es una de las más importantes en Excel, veamos aquí cómo utilizar esta
función, dominando los detalles y consideraciones que debemos tener en cuenta al emplear la
Función BUSCARV.

La función BUSCARV forma parte de las funciones de Búsqueda y Referencia, y tiene por finalidad
realizar la búsqueda de un valor dentro de una tabla, para retornar alguno de los valores de la tabla en
la que se realiza la búsqueda.

Sintaxis de la Función BUSCARV

Comencemos por analizar los parámetros que son empleados como parte de la función, como
podemos observar en la siguiente imagen, la función BUSCARV tiene 4 parámetros, de los cuales el
cuarto es opcional.

El primer parámetro es el “Valor Buscado”, este dato corresponde al valor o elemento que tiene que
ser buscado.
Luego tenemos “Matriz Buscar en”, este segundo parámetro corresponde al cuadro o tabla en la que
se realizará la búsqueda del valor que se haya señalado como “Valor Buscado”
Como tercer parámetro tenemos el “Indicador Columnas” este dato permite identificar dentro del
cuadro o tabla definido en la “Matriz buscar en”, a la columna que tiene el dato que se está
buscando.
Finalmente el cuarto parámetro es “Ordenado” este dato hace referencia al estado (ordenado o no)
en que se encuentra la “Matriz Buscar en”, este dato tiene una influencia directa en el resultado de
la función.

Ejemplo de la Función BUSCARV

El siguiente ejemplo de la función BUSCARV nos permitirá comprender como utilizar la función
BUSCARV, veamos.
En la parte izquierda tenemos un conjunto de productos, cada producto corresponde a un tipo, el cual
puede ser A, B o C, se necesita que en la columna D aparezca la descripción de correspondiente al
tipo de tipo producto.

Se conoce que el tipo de producto A es “Crítico”, B es “Importante” y C es “General”, esta

información se encuentra en una tabla en la parte derecha, la idea es que estas descripciones puedan
aparecer en la columna D.
Fijémonos ahora en la línea 3, esa línea corresponde a un producto tipo A, tal como se señala en la
celda C3, por lo tanto lo que debe aparecer en la celda D3 es el texto “Crítico” el cual debe ser
tomado del cuadro ubicado a la derecha, este es el momento de comenzar a aplicar la función
BUSCARV.
La fórmula que utiliza la función BUSCARV sería la siguiente:
=BUSCARV(C3,$F$3:$G$5,2,FALSO)
Notemos que el primer dato o parámetro es C3, es decir el “Valor Buscado” es C3, lo cual es
comprensible porque este es el dato a ser buscado en el cuadro ubicado a la derecha.
El segundo parámetro es F3:G5, esta es la “Matriz Buscar en”, observemos que “A” (el contenido
de C3) será buscado en la primera VERTICAL de esta matriz (de ahí que la función se llame
BUSCARV), es decir como la matriz comienza en la columna F, el valor buscado (“A”) se buscará
en la columna F, si la matriz fuese H8:L20 el valor se buscaría en la columna H.
El tercer parámetro conocido como “Indicador columnas” tiene el valor 2, lo que significa que el
valor buscado se encuentra en la segunda columna de la “Matriz Buscar en”, es decir se está
buscando en la columna G la cual contiene la descripción.
El valor buscado “A” se encuentra en F3, como el valor encontrado se encuentra en la fila 3, y la
columna determinada en el tercer parámetro es la G, la celda que contiene el resultado es la G3, es
decir es el texto “Crítico”, este texto es el que aparecerá como resultado de la fórmula empleada en
la celda C3.

Finalmente el cuarto parámetro llamado “Ordenado”, sirve para identificar si se está realizando una
búsqueda exacta o no, si el valor es VERDADERO se buscará un valor cercano en la “Matríz
Buscar en”, para ello se asume que el contenido esta ordenado, por los que si nos fijamos en la
siguiente imagen podemos observar como “B” no existe en el cuadro ubicado a la derecha, por lo
tanto retorna el valor más cercano, en este caso el correspondiente a “A”, y por ende retorna crítico,
de forma similar ocurre para “X”, y se retorna el valor correspondiente a “D”, es decir se retorna
“General”.
Al utilizar la función BUSCARV si no se coloca este cuarto parámetro se asume como si se estuviese
utilizando VERDADERO.
Mientras que si observamos la siguiente imagen, en la que se utiliza FALSO, al no encontrar una
coincidencia exacta tanto para el valor “B” como para “X”, se retorna el error #N/A.

En general se recomienda que al utilizar la función se tienda a usar FALSO a fin de identificar con
exactitud los valores buscados.
Función BUSCAR en Excel
6 mayo, 2017 por Fernando | 0 Comentarios
La Función BUSCAR en Excel sirve para identificar un valor dentro de un rango de celdas, en
función de otro rango de celdas definido, veamos la sintaxis y el ejemplo de cómo usar la Función
BUSCAR.

Sintaxis de la Función BUSCAR en Excel

Sintaxis 1 – BUSCAR con tres parámetros

Valor Buscado: es el valor que requiere ser encontrado dentro una base de datos.

Vector de Comparación: este elemento corresponde al rango de celdas en el que se realizará la

búsqueda, es decir Excel intentará encontrar el “Valor Buscado” en esta lista, y determinará la
posición en la que se encuentra la coincidencia.

Vector Resultado: corresponde al rango de celdas de donde se extraerá el resultado, la posición o

elemento elegido dependerá de la búsqueda realizada en el “Vector de comparación”, si el elemento
buscado esta en la posición 8, entonces se tomará el elemento 8 del vector resultado para mostrarlo
como resultado.

Sintaxis 2 – BUSCAR con dos parámetros

Cuando la función BUSCAR en Excel recibe dos parámetros, el primer parámetro corresponde
al valor buscado, mientras que el segundo corresponde a la matriz, el cual es una especie de mezcla
del vector de comparación y del vector resultado, es decir realiza el comportamiento de ambos
parámetros por medio de uno solo.
Ejemplo de la Función BUSCAR en Excel
Notemos como en el siguiente ejemplo se busca el término “Zapatilla 3” dentro del rango A3:A10,
como el término buscado esta en la tercera posición, en el vector resultado se obtendrá el que se
encuentra en la tercera posición el cual contiene 71.

Notemos en el siguiente ejemplo que es necesario que la información se encuentre ordenada para que
se pueda encontrar correctamente el valor buscado, en este caso al buscarse “Guantes” y como el
primer elemento “Zapatilla 1” es posterior (en un orden alfabético), la función BUSCAR asume que
el término “Guantes” no se encuentra en la lista.

Notemos en el siguiente ejemplo como al buscar un término inexistente “Zapatillas R” y cuyo valor
(ordenado alfabéticamente) es superior a todos los valores existentes, es que se retorna la última
posición existente.
En el caso que se usen dos parámetros en la función BUSCAR, las restricciones son las mismas a las
descritas en los ejemplos anteriores ( se requiere que los datos se encuentren ordenados), y retorna el
valor buscado en caso se llegue a encontrar.
El uso de las tablas dinámicas en Excel
1 Introducción
¿No has sentido nunca la necesidad de manejar y analizar grandes cantidades de datos y no sabes
cómo? Las tablas dinámicas de Excel son una de las herramientas más potentes de Excel para ello. En
este curso te enseñamos todos los trucos.

Podrás girar las filas y las columnas para ver diferentes resúmenes de los datos de origen, y mostrar
todos los detalles de tus áreas de interés.

1.1 ¿Cuándo conviene utilizar un informe de tabla dinámica?

Cuando deseas comparar totales relacionados, sobre todo si tienes una lista larga de números para
resumir y deseas realizar comparaciones distintas con cada número. En el informe que mostramos,
puedes 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 los totales generales. Puesto que los informes de
tabla dinámica son interactivos, puede cambiar la presentación de los datos para ver más detalles o
calcular diferentes resúmenes, como recuentos o promedios.

1.2 ¿Cómo se organizan los datos?

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 nuestro ejemplo, la columna Deporte se convierte en el campo Deporte y
cada registro de Golf se resume en un sólo elemento Golf.

Un campo de datos, 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 Trim3.

¿Te resulta complicado? No te preocupes. Poco a poco iremos descubriendo el secreto.

2 Creación de un informe de tablas dinámicas
Selecciona la opción Datos | Informes de tablas y gráficos dinámicos en tu Libro Excel. Con ello se abre
un asistente que nos va guiando paso a paso en la creación del informe.

2.1 Fuentes de datos

El primer paso es elegir desde dónde obtenemos los datos. Hay varias opciones:

2.1.1 Lista o base de datos de Microsoft Excel.

A partir de una lista o base de datos de Excel crea tu propio informe de tabla dinámica o de gráfico
dinámico. Es importante que la primera fila tenga un nombre descriptivo (nombre de campo) para poder
identificarlo posteriormente.

2.1.2 Origen de datos externo.

Crea el informe o gráfico a partir de datos almacenados en un archivo o una base de datos externa al
libro actual de Microsoft Excel. Esto nos permite trabajar con mas de 65536 registros, que es el máximo
permitido por Excel en una única hoja. Con esta opción podemos acceder a ficheros dBase, y a bases de
Datos Access, Oracle, Interbase, etc.

2.1.3 Varios rangos de consolidación.

Crea un informe de tabla dinámica o de gráfico dinámico a partir de varios rangos (grupos de datos) de
una hoja de cálculo de Microsoft Excel.

2.1.4 Otro informe de tabla dinámica o de gráfico dinámico.

Puedes crear un informe de tabla dinámica o de gráfico dinámico a partir de otro informe de tabla
dinámica del mismo libro. De esta forma se optimiza el uso de la memoria cuando quieres trabajar sobre
datos ya procesados antes en otro informe.
2.2 Tipos de informes a crear
Tienes a tu disposición dos tipos de informes a crear: un informe o un gráfico dinámico. Estas opciones
se seleccionan en la parte inferior del asistente dentro del primer paso (Observa la figura anterior).

3 Creación de un informe a partir de una lista o base de datos Excel

Lo primero que te pide el asistente es el rango de datos del cual queremos obtener el informe de tablas
dinámicas.

Por defecto selecciona el área de datos de la hoja que esté activa en ese momento. Puedes cambiarla
con el botón  , o incluso seleccionar otro libro donde se encuentren los datos con el botón Examinar.

4 Diseño del informe

Finalmente, debes indicar dónde quieres crear el informe. Por defecto se crea en una hoja de cálculo
nueva, aunque se le puede indicar en que hoja lo quieres.

En este momento se te ofrecen dos opciones mas: Diseñar el informe y establecer opciones generales
del mismo.

Si pulsas sobre la opción Diseño, puedes dar forma personalizada al informe. Para ello se te abre la
ventana de diseño, que es la que ves a continuación:
A la derecha tienes todos los campos. Arrastra sobre la zona de datos los que quieras que se calculen.
Coloca sobre las zonas página, fila o columna los campos por los que quieres clasificar el informe. El
diseño también se puede realizar una vez terminado el asistente, directamente sobre el informe, pero
aquí tienes la ventaja de que no se trabaja directamente con los datos, por lo que es mas cómodo y
eficiente.

En nuestro ejemplo arrastra el campo sexo a la columna, y el campo sección a las filas. Finalmente,
arrastra el campo CMUN al área de datos para que nos cuente cada uno de los registros que hay para
cada tipo.

El resultado de este diseño sería el siguiente informe:

Si en el asistente seleccionas el botón Opciones se muestra la ventana de opciones del informe de tabla
dinámica, en la que puedes establecer múltiples opciones diferentes en función de los resultados que
quieras obtener.

5 Trabajando con la tabla

5.1 Barra de herramientas Tabla dinámica
Cuando se trabaja con la tabla dinámica, se activa automáticamente la barra de herramientas Tabla
dinámica, a no ser que la hayas desactivado explícitamente [1]. Esta barra de herramientas está
compuesta por un menú desde el que se pueden acceder a opciones avanzadas de la tabla y de una
serie de botones que permiten acceder de forma rápida a las opciones más comunes.

5.2 Moviendo o añadiendo campos

El sistema es muy flexible: Para cambiar un campo de posición sólo tienes que arrastrar su nombre
desde un lado al otro para que automáticamente se reestructure la tabla con la nueva información. En el
ejemplo, si el campo sexo está en cabecera, pero quieres que esté en el lado, sólo pulsa el ratón sobre el
campo   y arrástralo hasta la nueva posición. Al moverlo el icono irá cambiando para mostrar si
lo estamos colocando en la página, en la cabecera, en el lado, en el área de datos, o fuera de la tabla.

Si quieres añadir un campo nuevo lo tienes que hacer desde la

lista de campos, que aparece al colocarte sobre cualquier
celda del informe de tabla dinámica. Si no aparece la lista de
campos que ves en la imagen de la derecha es porque no está
seleccionada la opción en la barra de botones Tabla dinámica.
El botón para activar o desactivar esta ventana es el último de
la derecha.

Hay dos formas de añadir campos al informe, una es

arrastrándolo sobre el informe y otras es seleccionando en la
caja combinada de la parte inferior el área donde quieres
añadir el campo y a continuación pulsar en el botón Agregar a.

Si añades un campo en el área de página aparece una celda

con el nombre del campo y a continuación una caja combinada
con todos los valores posibles para el campo mas uno más
con el total. Esto sirve para que seleccionando cada uno de
los valores del campo puedas filtrar el informe por él.

También puedes añadir múltiples campos en cada una de las

áreas. Si añades varios campos en alguna de las dimensiones de clasificación [2] se mostrará un campo
a continuación del otro, lo que te permite cambiar el orden entre uno y otro arrastrándolos. Por ejemplo,
se puede filtrar por el sexo y a continuación por el estado civil o viceversa.
También puedes añadir campos al área de datos. E incluso puedes añadir varias veces el mismo campo,
para obtener distintos cálculos (promedio, máximo, mínimo, suma, conteo, etc.) [3].
5.3 Mostrar los elementos individuales
En cualquier momento puedes consultar los elementos individuales que corresponden con cada una de
las celdas del informe de tabla dinámica. Para ello, sólo tienes que hacer doble click sobre cualquiera de
las casillas y se creará una nueva hoja dentro del cuaderno activo con los elementos que correspondan.

5.4 Cambiar literales de los valores de cada campo

A veces, los campos que usas para la clasificación no tienen contenidos suficientemente claros y
conviene cambiarlos por otros mas sencillos de entender.

Para ello, sólo selecciona la casilla donde se encuentra el literal que quieres cambiar y modifícalo como
harías con cualquier celda en una hoja Excel: Se actualizarán todas las casillas donde estuviese este
texto anteriormente, incluso en los totales.
En la ilustración siguiente puedes ver un ejemplo en el que se sustituyen los literales del campo sexo
para que muestre Hombres y Mujeres en vez de 1 y 6. En esta ilustración también puedes ver como se
muestran dos variables en la cabecera y por supuesto como se han cambiado los literales del sexo en
los totales.

5.5 Filtrar los valores a mostrar

En ocasiones el informe es muy grande y resulta difícil ver la información de forma rápida [4].

En estos casos, lo más sencillo es filtrar la información que realmente te interesa ver. Para ello, pulsa en
la flecha hacia abajo   que hay junto al nombre del campo. Entonces se despliega una ventana con los
valores para que elijas los que quieres que aparezcan.

De esta forma, puedes conseguir un resultado como el siguiente:

Otra forma de ocultar cualquier bloque de información es seleccionar la celda que qiueres y con el botón
derecho del ratón escoger la opción ocultar.

5.6 Configuración de campo

Si tienes marcado un campo en la ventana de selección de campos o directamente en el informe de tabla
dinámica, puedes configurar este campo pulsando el botón de la barra de tareas o tecleando el botón
derecho sobre el nombre de campo y seleccionando la opción Configuración de campo.

5.6.1 Configuración de un campo de clasificación

Los campos de clasificación son los que tu eliges para organizar los resultados. Esta ventana te permite
cambiar el nombre con el que se representa el campo, como puedes ver en el ejemplo.

Además se puede cambiar la forma de calcular los subtotales cuando tenemos varios valores para el
campo elegido. Lo verás mas claro en el ejemplo:
El resultado de lo que acabas de ver será una tabla como esta:

Si pulsas en el botón Avanzado, aparece otra ventana, con mas opciones de configuración.

Aquí puedes establecer el orden con el que quieres que aparezca la información (puedes dejar que lo
haga automático o seleccionar tu propio criterio).

Otra posibilidad es que le pidas que sólo te muestre los valores que mas se repiten (por ejemplo cuando
trabajas con variables con muchos estados posibles, como la edad, municipios, etc.)

Si en el ejemplo anterior añades el año de nacimiento, la tabla alcanza un tamaño inmanejable.

La solución a este problema es pedir que te muestre sólo los 10 años de nacimientos que mas, o que
menos, sujetos tengan.

Como puedes ver, ahora es mucho mas manejable. Observa que el campo ANONA lo muestra en negrita
para que notes que está filtrado.
5.6.2 Configuración de una variable de datos
Cuando trabajas con una variable de datos, además de cambiar el nombre del campo, puedes establecer
como quieres calcular los valores. No olvides que, si la variable con la que estás operando no numérica,
sólo se puede contar las repeticiones del valor (cuenta) que tienes en los datos.

Aún así, si pulsas sobre el botón de opciones aparecen nuevas posibilidades.

  

Sí en el ejemplo eliges mostrar los datos en forma de porcentaje del total, nos enseña el porcentaje que
supone la cuenta de estos registros con respecto al total de todos ellos. El resultado sería así:

En el momento que añades un campo numérico al informe [5], ya

podrás utilizar otras operaciones diferentes como la suma, el máximo,
el mínimo, etc.
Las opciones avanzadas que veías antes con el conteo de registros,
se vuelven mucho más útiles. Por ejemplo, si incluyes el campo año
pero en formato numérico, tienes opciones mucho mas potentes,
como el cálculo de la diferencia entre este elemento con los de la
anterior sección.

Esto sirve, para analizar cómo varía el promedio del año de

nacimiento, en función de la sección en que nos encontramos.
Observa el resultado.
Como puedes ver en la tabla, la primera de las secciones aparece sin datos, mientras que las otras dos
muestran que la segunda es mucho mayor que la primera, y que la tercera, a su vez, es un poco mayor
que la segunda.

5.7 Mostrar y ocultar detalle

En ocasiones, interesa desglosar algunos elementos y otros no, bien porque no son significativos, o bien
porque se escapan al objetivo del estudio que quieres presentar.

Si quieres hacer esto, lo mas sencillo es utilizar los botones de Ocultar detalle y Mostrar detalle que
encontrarás en la barra de herramientas de Tabla dinámica.

Con estos botones, puedes ocultar el detalle de un elemento determinado.

Por ejemplo, indícale que no quieres que muestre el desglose en estados civiles de los hombres, pero sí
el de las mujeres.

5.8 Generar un nuevo campo

agrupando valores
A veces hay campos con muchos estados, y
realmente sólo te interesa analizar las
agrupaciones de los mismos.

Por ejemplo, si sustituyes la variable sección

por la variable edad, el resultado es una tabla
con muchas filas.

Si seleccionas todos los elementos con edad

inferior a quince años y con el botón de la
derecha escoges agrupar como puedes ver en
la figura de la derecha, obtienes un nuevo
campo (figura de abajo), que tiene un elemento de valor Grupo1, que agrupa a todos las edades
menores de quince años.

Luego puedes quitar el campo EDAD1 y quedarte sólo con el nuevo campo que has obtenido.

Con esta opción, te evitas tener que crear variables desde el principio.

6 Fórmulas en tablas dinámicas

Para insertar una fórmula o un elemento calculado debes seleccionar la opción correspondiente en la
barra de herramientas.

6.1 Campos calculados

Los campos calculados se crean como resultado de una fórmula entre campos que ya existen en tu
selección.

Por ejemplo, puedes definir el campo meses como resultado de multiplicar el campo año por 12. Aquí no
aporta mucha información, pero si trataras con cantidades monetarias podrías cambiar de euros a
dólares o viceversa, podrías calcular la cuenta de resultados en función de las pérdidas y ganancias, etc.
6.2 Elementos calculados
Igual que puedes crear campos calculados, puedes
generar elementos calculados, que te permitan
mostrar cálculos entre distintos grupos de una
variable de clasificación.

Por ejemplo, en la tabla anterior, puedes crear un

nuevo elemento calculado para el estado civil que sea
la diferencia entre solteros y casados.

Estas opciones son también mas útiles con variables

de carácter económico, porque puedes calcular el
margen de ganancias para un producto de una
determinada forma, pero fijar que en un determinado
mes se calcula de otra forma.

7 Gráficos dinámicos
Los informes también se pueden ver en un gráfico. Las opciones son similares a lo que ya has visto. Sólo
recuerda que, con sólo pulsar el botón de asistente para gráficos se genera un gráfico a partir del informe
de tabla dinámica.
8 Fuentes de datos externas
Al principio viste que existen diversas fuentes de las que puedes obtener datos para generar los
informes. Hasta ahora has trabajado con datos provenientes de Excel, pero a partir de ahora vas a
trabajar con datos residentes en bases de datos externas.

Si en el primer paso seleccionas la opción de Fuente de datos externa, al pulsar siguiente, verás esta
pantalla.

Pulsa la opción Obtener datos.

Verás una pantalla para seleccionar el tipo de base de datos al que vas a conectarte.
Si eliges conectar a una base de datos Access, al pulsar aceptar, verás un nuevo diálogo para buscar el
fichero al que quieres conectarte.

Una vez elegido, debes seleccionar las tablas y campos que vas a usar.

Finalmente puedes indicar criterios de selección, para discriminar aquellos registros que no sean de
interés.
Ahora te preguntará por qué campos quieres ordenar. En todo caso luego podrás cambiar el orden en el
informe.

Verás ahora una ventana que no da diversas opciones. La primera es la que normalmente usas, la de
devolver los datos a Excel, para generar el informe, incluso con mas de 65000 registros. La segunda te
permite ver los datos antes de devolvérselos a Excel y modificar la consulta con la herramienta Microsoft
Query. La tercera te permite generar un fichero con todos los datos agregados para consultarlos
posteriormente de forma eficiente.

También puedes salvar la consulta para utilizarla después y no tener que volver a definirla si los datos
son con los que habitualmente trabajas.

Si escoges la opción de crear un cubo OLAP, verás una primera pantalla en la que tienes que indicar los
campos que incluirás en el área de datos que, por defecto, son los campos numéricos de la base de
datos.
A continuación debes definir las variables de clasificación que vas a utilizar.

Por fin, debes decidir que vas a hacer con el cubo, si crearlo cada vez, almacenarlo completo en el disco
duro, o una solución intermedia.
Una vez que has obtenido los datos de cualquiera de las formas, el trabajo con el informe es igual al que
has venido viendo a lo largo del curso, con la única salvedad de alguna pequeña opción sobre cuando se
actualizan los datos, lo que siempre mejora el rendimiento del informe.

Mi recomendación es que practiques una y otra vez sobre estos temas y lograrás sacar el máximo
aprovechamiento de estos recursos que ofrece Excel.

NOTAS AL PIE.
[1]
 Si en tu sistema se encuentra desactivada, debes seleccionarla en el menú Ver y en el submenú
Barras de Herramientas.
[2]
 Se consideran dimensiones de clasificación a la cabecera, el ladillo y la página.
[3]
 Es importante tener en cuenta que para la mayoría de estos cálculos es necesario que las variables
sobre las que se está operando han de ser de tipo numérico.
[4]
Obsérva que en este ejemplo también se han cambiado los literales de los estados civiles.
[5]
 Si no tienes un campo numérico, se puede obtener mediante el uso de la función VALOR(), que
devuelve convertido en número el valor de una cadena de caracteres.

Tablas dinámicas
avanzadas: combinar datos
de varias hojas
by Bob Flisser2 Jun 2014
 Español
Difficulty:AdvancedLength:LongLanguages:

Microsoft Excel OfficeApp Training

Spanish (Español) translation by Elías Nicolás (you can also view the original English article)
Cuando desea crear una tabla dinámica, ¿qué hace si tiene datos en diferentes hojas de
cálculo? Si utiliza Excel 2013, tiene un método optimizado para hacerlo. Hay una
técnica llamada Modelo de Datos, que utiliza las relaciones de datos como lo hace una
base de datos.

En este tutorial, le mostraré todo lo que necesita para hacer una tabla dinámica en
Excel 2013 a partir de datos en varias hojas, utilizando el modelo de datos.

Screencast
Si desea seguir este tutorial con su propio archivo de Excel, puede hacerlo. O si lo
prefiere, descargue el archivo zip incluido para este tutorial, que contiene un libro de
ejemplo denominado Pivot Consolidate.xlsx.

Examen de los datos

Este libro tiene tres hojas de trabajo: información de cliente, información de pedido e
información de pago.

Haga clic en la hoja Customer Info y compruebe que contiene los números de pedido
y el nombre y estado de los clientes.
 Ho
ja de información del cliente

Haga clic en la hoja Order Info y compruebe que también contiene números de
pedido, así como los campos del mes, los productos pedidos y si los productos son
orgánicos.
 Ho
ja de información de pedido

Haga clic en la hoja Payment Info y compruebe que contiene los números de pedido,
el monto en dólares de cada venta, el método de pago y si el pedido fue colocado por
un cliente nuevo o existente.
 Ho
ja de información de pago

Al conectar todas estas hojas dentro del panel de tareas de la tabla dinámica, podemos
seleccionar datos de cada una de las hojas. Puesto que los números de pedido existen
en las tres hojas, se convertirán en los puntos de conexión. Esto es lo que una base de
datos llama a una clave primaria. Tenga en cuenta que no siempre es necesario tener
una clave primaria, pero reduce la posibilidad de error.

Crear tablas con nombre

Antes de crear la tabla dinámica, creemos la tabla de cada una de las hojas.

Haga clic de nuevo en la Customer Table y, a continuación, haga clic en cualquier

lugar dentro del área de datos. Vaya a la pestaña Insertar de la barra de la cinta de
opciones y, a continuación, haga clic en el icono Tabla.
 Co
nvertir datos en una hoja de cálculo seleccionando Insertar > Tabla

El cuadro de diálogo Crear tabla identifica correctamente el área de la tabla. La casilla

de verificación en la parte inferior también debe identificar que la primera fila de la
tabla es para los encabezados. (Si no, seleccione esa opción.)
 El
cuadro de diálogo Crear tabla debe adivinar correctamente el área de datos

Haga clic en Aceptar y ahora tiene una tabla con sombreado a rayas y botones de
filtro. Puede hacer clic dentro de ella para anular la selección, si desea verla mejor (no
haga clic fuera de la tabla). La barra de la cinta de opciones también muestra una
pestaña Diseño para la tabla. En el lado izquierdo de la cinta, el cuadro Nombre de
tabla muestra un nombre temporal de Tabla1. Bórrelo y
llámelo Customer_Info (utilice un guión bajo en lugar de un espacio). A
continuación, pulse Intro.
 Apl
ique un nombre a cada tabla

Repita este proceso en las hojas Order Info y Payment Info . Llame las
tablas Order_Info y Payment_Info, respectivamente.

Ahora estamos listos para insertar la tabla dinámica.

Insertar a la tabla dinámica

En la hoja Payment Info, asegúrese de que el cursor esté en algún lugar de la tabla.
Vuelva a la pestaña Insertar de la cinta de opciones y haga clic en el icono de
la tabla dinámica (es el primer icono).

Co
n el cursor dentro de una de las tablas, seleccione Insertar > Tabla dinámica

El cuadro de diálogo que aparece debe identificar correctamente la tabla y seleccione

que la tabla dinámica se va en una hoja de cálculo nueva. En la parte inferior, haga
clic en la casilla de verificación Agregar estos datos al modelo de datos. A
continuación, haga clic en Aceptar.
 Ag
regar los datos al modelo de datos es lo que permite que las conexiones funcionen

Ahora tendrá una tabla dinámica en una nueva hoja de cálculo, habrá un panel de
tareas en el lado derecho de la pantalla y la barra de la cinta mostrará la pestaña
Análisis.

El panel de tareas muestra la tabla y los campos de la hoja activa, así que haga clic
en Todas para ver todas las tablas que creó. Pero antes de poder usarlos, tenemos que
conectarlos entre sí, y eso significa crear relaciones. Haga clic en el
botón Relaciones en la barra de la cinta de opciones.
Configuración de relaciones de tabla
Al hacer clic en ese botón se muestra el diálogo Administrar relaciones. Haga clic en
el botón Nuevo y se mostrará el cuadro de diálogo Crear relación. Crearemos dos
relaciones utilizando el campo Order # como el conector.

En las listas desplegables, elija Payment_Info para la tabla y, junto a ella,

seleccione Order # en el menú desplegable de Columna. En la segunda fila,
seleccione Customer_Info en la lista desplegable Tabla relacionada y, junto a ella,
seleccione Order # en la lista desplegable de Columna relacionada.

Ha
y tres tablas, así que crea dos relaciones

Esto significa que las tablas Payment_Info y Customer_Info están relacionadas donde
tienen números de orden coincidentes.

Haga clic en Aceptar y veremos la relación que aparece en el cuadro Administrar

relaciones.

Repita este proceso para crear una relación que une Payment_Info a Order_Info,
también utilizando el campo Order #. El cuadro Administrar relaciones ahora debería
verse así:
 El
diálogo Administrar relaciones mostrará las relaciones que creo

Tenga en cuenta que no es necesario crear una relación entre las tablas Order_Info y
Customer_Info, ya que se unen automáticamente a través de la tabla Payment_Info.

Haga clic en el botón Cerrar en la parte inferior del cuadro. Ahora finalmente

podemos arrastrar campos a la tabla dinámica.

Insertar campos en la tabla dinámica

En la sección Todas del panel de tareas, haga clic en las pequeñas flechas para abrir
las tres tablas, para ver sus campos. Arrastre los campos en las áreas de tabla dinámica
de la siguiente manera:

 State y Month en filas
 Product en columnas
 $ Sale en valores
 Status en filtros
 Arr
astre los campos de cada una de las tres tablas en la tabla dinámica
Ahora puede utilizar y modificar esto como cualquier otra tabla dinámica.

Conclusion
Utilizando la nueva característica de modelo de datos de objetos en Excel 2013, puede
recopilar los campos de varias hojas de cálculo para crear una tabla dinámica
unificada. Tenga en cuenta que las filas de cada tabla deben estar relacionadas entre sí
de alguna manera. Tendrá las mejores posibilidades de éxito cuando las tablas tengan
un campo común con valores únicos.

Si está buscando buenas formas de presentar sus datos, Envato Market tiene una
buena selección de plantillas de Excel y PowerPoint, así como scripts y
aplicaciones para convertir los datos de Excel en formatos web y viceversa.
Cómo vincular tablas
dinámicas por un filtro de
informe
Las tablas dinámicas en Excel son independientes aun cuando hayan sido
creadas a partir de los mismos datos y se encuentren en la misma hoja, y en
caso de querer filtrarlas es necesario agregar un filtro de informepara cada
una de ellas.

A pesar de este comportamiento predeterminado de Excel, hay ocasiones en

las que deseamos  presentar diferentes perspectivas de los datos y controlar
todas las tablas dinámicas con un solo filtro. En el pasado podíamos lograr
este comportamiento solo si utilizábamos macros, pero a partir de Excel 2010
podemos apoyarnos de la segmentación de datos para obtener el mismo
resultado y olvidarnos del código VBA.

Para nuestro ejemplo utilizaremos datos de órdenes de compra y crearemos

tres tablas dinámicas dentro de la misma hoja de Excel para mostrar las
tallas, los colores y las zonas de la ciudad a donde se realizará el envío de
los productos:
Puedes notar que también he agregado un filtro de informe para controlar las
fechas de las órdenes de compra, aunque en este momento dicho filtro
solamente controla la primera tabla dinámica.

Insertar la segmentación de datos

El primer paso para vincular las tablas dinámicas es agregar la
segmentación de datos y lo haremos para la primera tabla dinámica, así que
solo debo seleccionar cualquiera de sus campos e ir a Herramientas de tabla
dinámica > Opciones > Ordenar y filtrar > Insertar segmentación de datos, y
se mostrará un cuadro de diálogo donde podré elegir los campos por los que
se hará la segmentación:
Selecciono el campo Fecha que es el mismo campo del filtro de informe y
pulso el botón Aceptar. De inmediato Excel mostrará el panel desde el cual
puedo utilizar la segmentación de datos:
En este momento, tanto el filtro de informe como la segmentación de
datos controlan la primera tabla dinámica y un cambio en cualquiera de
ambos se reflejará tanto en la tabla dinámica como en el otro control. Por
ejemplo, si selecciono la fecha 02/01/2013 en la segmentación de datos,
también el filtro de informe mostrará dicha fecha.

Tablas dinámicas vinculadas

Como ya he mencionado antes, un filtro de informe solo puede controlar una
sola tabla dinámica, pero la segmentación de datos funciona diferente y es
posible conectarla a varias tablas dinámicas para vincularlas. Para hacerlo
debemos seleccionar el panel de segmentación de datos e ir a Herramientas
de segmentación de datos > Conexiones de tabla dinámica lo cual mostrará
el siguiente cuadro de diálogo:
En este cuadro de diálogo podemos ver las tablas dinámicas que están
conectadas con la segmentación de datos así que solo debemos seleccionar
todas las tablas dinámicas de nuestra hoja y hacer clic en el botón Aceptar.
Una vez realizada la vinculación, cualquier cambio en la segmentación de
datos o en el filtro de informe afectará a todas las tablas dinámicas al mismo
tiempo.

Ocultar la segmentación de datos

Aunque con el paso anterior ya tenemos vinculadas todas nuestras tablas
dinámicas, si queremos dejar solamente visible el filtro de informe para
realizar el filtrado, entonces podemos ocultar el panel de segmentación de
datos. Para ocultarlo debemos seleccionar dicho panel e ir a Herramientas de
segmentación de datos > Opciones > Organizar > Panel de selección donde
debemos hacer clic en el botón “Ocultar todas”.
Una vez que hemos ocultado el panel de segmentación de datos solo nos
quedará en pantalla el filtro de informe y podremos modificar todas las tablas
dinámicas con el mismo. En la siguiente animación puedes notar cómo
cambian todas las tablas dinámicas con solo cambiar la opción seleccionada
del filtro de informe:

Para seguir experimentando y aprendiendo cómo vincular tablas

dinámicas puedes descargar el libro de trabajo utilizado en este artículo.