“CONSOLIDAR DATOS Y TABLAS DINÁMICAS EN
EXCEL”
“CONSOLIDAR DATOS
EN EXCEL”
IMAGINA QUE DESPUÉS DE RECOPILAR LA INFORMACIÓN SOBRE
LAS VENTAS DE NUESTROS PRODUCTOS EN DIFERENTES PAÍSES TENEMOS UN LIBRO DE EXCEL
DONDE CADA HOJA TIENE LA INFORMACIÓN DE UN MES EN ESPECÍFICO.
NECESITO CONSOLIDAR LA INFORMACIÓN EN UNA SOLA HOJA
PARA PODER CREAR MI REPORTE, PERO ¿CÓMO PUEDO HACERLO DE UNA MANERA SENCILLA?
PARA CONSOLIDAR DATOS EN EXCEL TENEMOS EL COMANDO CONSOLIDAR QUE NOS AYUDA A
OBTENER EL RESULTADO DESEADO DE UNA MANERA FÁCIL. SUPONGAMOS QUE LA INFORMACIÓN
DE LAS 3 HOJAS DE EXCEL ES LA SIGUIENTE.
“CONSOLIDAR
DATOS EN HOJAS DIFERENTES”
-COMANDO CONSOLIDAR EN EXCEL
PARA CONSOLIDAR LOS DATOS CREARÉ UNA NUEVA HOJA Y HARÉ
CLIC EN EL BOTÓN CONSOLIDAR QUE SE ENCUENTRA EN LA FICHA DATOS DENTRO DEL GRUPO
HERRAMIENTAS DE DATOS.
-BOTÓN CONSOLIDAR
SE MOSTRARÁ EL CUADRO DE DIÁLOGO CONSOLIDAR EN DONDE
SE DEBERÁ ESPECIFICAR CADA UNO DE LOS RANGOS DE LAS DIFERENTES HOJAS. PUEDES
HACER CLIC EN EL BOTÓN DE SELECCIÓN DE REFERENCIA PARA SELECCIONAR LOS RANGOS
FÁCILMENTE.
-CUADRO DE DIÁLOGO CONSOLIDAR
UNA VEZ ESPECIFICADA LA REFERENCIA SE DEBE PULSAR EL
BOTÓN AGREGAR. DE LA MISMA MANERA SE DEBEN AGREGAR CADA UNA DE LAS HOJAS HASTA
TENER TODAS LAS REFERENCIAS ENLISTADAS. FINALMENTE DEBO MARCAR LOS CUADROS DE
SELECCIÓN DE RÓTULOS EN LA FILA SUPERIOR Y EN COLUMNA IZQUIERDA DE MANERA QUE
LOS DATOS SEAN PRESENTADOS ADECUADAMENTE.
-AGREGAR TODAS LAS REFERENCIAS A
CONSOLIDAR
AL PULSAR EL BOTÓN ACEPTAR, EXCEL REALIZARÁ LA
CONSOLIDACIÓN DE LOS DATOS Y LOS COLOCARÁ EN LA NUEVA HOJA DE EXCEL.
-DATOS CONSOLIDADOS EN EXCEL
DE ESTA MANERA LA INFORMACIÓN QUE ANTES ESTABA EN
DIFERENTES HOJAS DE EXCEL HA SIDO CONSOLIDADA CORRECTAMENTE EN UNA SOLA HOJA.
“CÓMO
CONSOLIDAR DATOS”
1. EN PRIMER
LUGAR, EXAMINA LOS DATOS Y DECIDE SI DESEAS CONSOLIDARLOS POR POSICIÓN O POR
CATEGORÍA.
POSICIÓN SI
VAS A COMBINAR DATOS QUE ESTÁN EN LA MISMA CELDA EN VARIOS RANGOS, PUEDES
CONSOLIDAR POR POSICIÓN.
CATEGORÍA SI
TIENES VARIOS RANGOS CON DISEÑOS DIFERENTES Y VAS A COMBINAR DATOS DE FILAS O
COLUMNAS QUE TENGAN RÓTULOS (NOMBRES DE FILA Y/O COLUMNA) COINCIDENTES, PUEDES
CONSOLIDAR POR CATEGORÍA.
2. CONFIGURA
LOS DATOS QUE VA A CONSOLIDAR.
ASEGÚRATE DE QUE CADA RANGO DE DATOS ESTÁ EN FORMATO
DE LISTA: CADA COLUMNA TIENE UN RÓTULO EN LA PRIMERA FILA, CONTIENE DATOS
SIMILARES Y NO TIENE FILAS O COLUMNAS EN BLANCO.
COLOCA CADA RANGO EN UNA HOJA DE CÁLCULO DIFERENTE.
EJ: UNA HOJA DE CÁLCULO DIFERENTE PARA CADA VENDEDOR.
NO PONGAS NINGÚN DATO EN LA HOJA DE CÁLCULO DONDE VAYAS
A COLOCAR LA CONSOLIDACIÓN.
SI REALIZAS LA CONSOLIDACIÓN POR POSICIÓN, ASEGÚRATE
DE QUE CADA RANGO TIENE EL MISMO DISEÑO. SI REALIZAS LA CONSOLIDACIÓN POR
CATEGORÍA, ASEGÚRATE DE QUE LOS RÓTULOS DE LAS COLUMNAS O FILAS QUE DESEAS
COMBINAR TIENEN IDÉNTICA ORTOGRAFÍA Y COINCIDENCIA DE MAYÚSCULAS Y MINÚSCULAS. SI
DESEAS, PUEDES ASIGNAR UN NOMBRE A CADA RANGO.
3. HAZ CLIC EN
LA CELDA SUPERIOR IZQUIERDA DEL ÁREA DONDE DESEES QUE APAREZCAN LOS DATOS
CONSOLIDADOS.
4. EN EL MENÚ
DATOS, HAZ CLIC EN CONSOLIDAR.
5. EN EL CUADRO
FUNCIÓN, HAZ CLIC EN LA FUNCIÓN DE RESUMEN QUE DESEAS QUE UTILICE MICROSOFT
EXCEL PARA CONSOLIDAR LOS DATOS. EJ: SUMAR, CONTAR O PROMEDIAR.
6. HAZ CLIC EN
EL CUADRO REFERENCIA, ELIGE LA ETIQUETA DE HOJA DEL PRIMER RANGO QUE VAS A CONSOLIDAR,
ESCRIBE EL NOMBRE QUE ASIGNASTE AL RANGO O SELECCIONA EL RANGO Y, A
CONTINUACIÓN, HAZ CLIC EN AGREGAR. REPITE ESTE PASO PARA CADA RANGO.
7. SI DESEAS
ACTUALIZAR LA TABLA DE CONSOLIDACIÓN AUTOMÁTICAMENTE CADA VEZ QUE CAMBIEN LOS
DATOS EN CUALQUIERA DE LOS RANGOS DE ORIGEN Y NO ESTÁS SEGURO DE SI MÁS TARDE
DESEARÁS INCLUIR RANGOS DIFERENTES O ADICIONALES EN LA CONSOLIDACIÓN, ACTIVA LA
CASILLA DE VERIFICACIÓN CREAR VÍNCULOS CON LOS DATOS DE ORIGEN.
8. ACTIVA LAS
CASILLAS DE VERIFICACIÓN BAJO USAR RÓTULOS EN QUE INDICAN DÓNDE ESTÁN
LOCALIZADOS LOS RÓTULOS EN LOS RANGOS DE ORIGEN: EN LA FILA SUPERIOR, LA
COLUMNA IZQUIERDA O AMBAS. LOS RÓTULOS QUE NO COINCIDAN CON LOS DE LAS OTRAS
ÁREAS DE ORIGEN PRODUCIRÁN FILAS O COLUMNAS INDEPENDIENTES EN LA CONSOLIDACIÓN.
PARA MAS INFORMACION EN:
“CONSOLIDAR TABLAS DINAMICAS”
LA CONSOLIDACIÓN DE DATOS ES UNA FORMA ÚTIL DE COMBINAR DATOS DE ORÍGENES
DIFERENTES EN UN MISMO INFORME. POR EJEMPLO, SI TIENE UNA TABLA DINÁMICA DE
CIFRAS DE GASTOS PARA CADA UNA DE LAS OFICINAS REGIONALES, PUEDE USAR LA
CONSOLIDACIÓN DE DATOS PARA REUNIR ESTAS CIFRAS EN UN INFORME DE GASTOS
CORPORATIVOS. ESTE INFORME TAMBIÉN PUEDE CONTENER LAS VENTAS TOTALES Y LOS
PROMEDIOS, LOS NIVELES DE INVENTARIO ACTUALES Y LOS PRODUCTOS MÁS VENDIDOS DE
TODA LA EMPRESA. OBTENER INFORMACIÓN SOBRE LA CONSOLIDACIÓN DE VARIOS
RANGOS
“CONFIGURAR EL ORIGEN DE DATOS”
SE DEBERÍA ORGANIZAR CADA RANGO DE DATOS
EN FORMATO DE TABLAS DE REFERENCIAS CRUZADAS, CON NOMBRES DE FILA Y COLUMNA
COINCIDENTES PARA LOS ELEMENTOS QUE QUIERA RESUMIR EN CONJUNTO. AL ESPECIFICAR
LOS DATOS PARA EL INFORME, NO INCLUYA FILAS NI COLUMNAS TOTALES DE LOS DATOS DE
ORIGEN. EL EJEMPLO SIGUIENTE MUESTRA CUATRO INTERVALOS EN FORMATO DE TABLAS DE
REFERENCIAS CRUZADAS.
“CAMPOS DE PÁGINA EN CONSOLIDACIONES DE
DATOS”
LA CONSOLIDACIÓN DE DATOS PUEDE USAR LOS
CAMPOS DE PÁGINA QUE CONTENGAN ELEMENTOS QUE REPRESENTAN UNO O VARIOS RANGOS DE
ORIGEN. POR EJEMPLO, SI ESTÁ CONSOLIDANDO DATOS DE PRESUPUESTOS DE LOS
DEPARTAMENTOS DE FABRICACIÓN, VENTAS Y MARKETING, PUEDE INCLUIR UN CAMPO DE
PÁGINA EN UN ELEMENTO PARA MOSTRAR LOS DATOS DE CADA DEPARTAMENTO, ADEMÁS DE UN
ELEMENTO PARA MOSTRAR LOS DATOS COMBINADOS. EN EL SIGUIENTE EJEMPLO SE MUESTRA
UNA TABLA DINÁMICA CONSOLIDADA QUE TIENE UN CAMPO DE UNA PÁGINA Y VARIOS
ELEMENTOS SELECCIONADOS.
USAR RANGOS CON NOMBRE
SI ES PROBABLE QUE EL RANGO DE DATOS
CAMBIE LA PRÓXIMA VEZ QUE CONSOLIDE LOS DATOS (ES DECIR, PODRÍA CAMBIAR EL NÚMERO
DE FILAS), CONSIDERE LA POSIBILIDAD DE DEFINIR UN NOMBRE PARA CADA RANGO DE
ORIGEN EN LAS HOJAS DE CÁLCULO INDEPENDIENTES. DESPUÉS, USE ESOS NOMBRES CUANDO
CONSOLIDE LOS INTERVALOS EN LA HOJA DE CÁLCULO MAESTRA. SI SE AMPLÍA UN RANGO
DE ORIGEN, PUEDE ACTUALIZAR EL RANGO PARA EL NOMBRE EN LA HOJA DE CÁLCULO
INDEPENDIENTE PARA QUE INCLUYA LOS NUEVOS DATOS ANTES DE ACTUALIZAR LA TABLA
DINÁMICA.
OTRAS MANERAS DE CONSOLIDAR DATOS
EXCEL TAMBIÉN PROPORCIONA OTRAS MANERAS
DE CONSOLIDAR DATOS QUE FUNCIONAN CON DATOS EN VARIOS FORMATOS Y DISEÑOS. POR
EJEMPLO, PUEDE CREAR FÓRMULAS CON REFERENCIAS 3D Y PUEDE USAR EL COMANDO CONSOLIDAR (EN
LA PESTAÑA DATOS, EN EL GRUPO HERRAMIENTAS DE DATOS).
PUEDE USAR EL ASISTENTE PARA TABLAS Y
GRÁFICOS DINÁMICOS PARA CONSOLIDAR VARIOS RANGOS. EN EL ASISTENTE, PUEDE ELEGIR
ENTRE NO USAR NINGÚN CAMPO DE PÁGINA, USAR UN ÚNICO CAMPO DE PÁGINA O VARIOS
CAMPOS DE PÁGINA.
“CONSOLIDAR LOS DATOS SIN USAR CAMPOS DE PÁGINA”
PARA COMBINAR LOS DATOS DE TODOS LOS
RANGOS Y CREAR UNA CONSOLIDACIÓN SIN CAMPOS DE PÁGINA, SIGA ESTOS PASOS:
1. AGREGUE EL ASISTENTE PARA TABLAS Y GRÁFICOS DINÁMICOS A LA BARRA DE
HERRAMIENTAS DE ACCESO RÁPIDO. SIGA ESTOS PASOS:
a. HAGA CLIC EN LA FLECHA QUE APARECE JUNTO A LA BARRA DE HERRAMIENTAS Y,
DESPUÉS, HAGA CLIC EN MÁS COMANDOS.
b. EN COMANDOS DISPONIBLES EN, SELECCIONE TODOS LOS
COMANDOS.
c. EN LA LISTA, SELECCIONE ASISTENTE PARA TABLAS Y GRÁFICOS DINÁMICOS,
HAGA CLIC EN AGREGAR Y, DESPUÉS, HAGA CLIC EN ACEPTAR.
2. HAGA CLIC EN UNA CELDA EN BLANCO DEL LIBRO (QUE NO FORME PARTE DE UNA TABLA
DINÁMICA).
3. HAGA CLIC EN EL ICONO DEL ASISTENTE PARA TABLAS Y GRÁFICOS DINÁMICOS EN LA
BARRA DE HERRAMIENTAS DE ACCESO RÁPIDO.
4. EN LA PÁGINA DEL ASISTENTE PASO 1, HAGA CLIC EN VARIOS
RANGOS DE CONSOLIDACIÓN Y, DESPUÉS, HAGA CLIC EN SIGUIENTE.
5. EN LA PÁGINA DEL ASISTENTE PASO 2A, HAGA CLIC EN CAMPOS
DE PÁGINA PERSONALIZADOS Y LUEGO HAGA CLIC EN SIGUIENTE.
6. EN LA PÁGINA DEL ASISTENTE PASO 2B, HAGA LO SIGUIENTE:
a. VUELVA AL LIBRO Y SELECCIONE EL RANGO DE CELDAS. DESPUÉS, EN EL ASISTENTE
PARA TABLAS Y GRÁFICOS DINÁMICOS, HAGA CLIC EN AGREGAR.
SUGERENCIA: SI EL RANGO DE CELDAS SE ENCUENTRA
EN OTRO LIBRO, ABRA EL LIBRO EN PRIMER LUGAR PARA QUE SEA MÁS FÁCIL SELECCIONAR
LOS DATOS.
b. EN ¿CUÁNTOS CAMPOS DE PÁGINA DESEA?, HAGA CLIC EN 0 Y,
DESPUÉS, HAGA CLIC EN SIGUIENTE.
7. EN LA PÁGINA DEL ASISTENTE PASO 3, SELECCIONE ENTRE AGREGAR LA
TABLA DINÁMICA A UNA HOJA DE CÁLCULO EXISTENTE O UNA NUEVA Y, DESPUÉS, HAGA
CLIC EN FINALIZAR.
“CONSOLIDAR DATOS CON UN SOLO CAMPO DE
PÁGINA”
PARA INCLUIR UN ÚNICO CAMPO DE PÁGINA
QUE TENGA UN ELEMENTO PARA CADA RANGO DE ORIGEN MÁS UN ELEMENTO QUE CONSOLIDE
TODOS LOS RANGOS, SIGA ESTE PROCEDIMIENTO:
1. AGREGUE EL ASISTENTE PARA TABLAS Y GRÁFICOS DINÁMICOS A LA BARRA DE
HERRAMIENTAS DE ACCESO RÁPIDO. SIGA ESTOS PASOS:
a. HAGA CLIC EN LA FLECHA QUE APARECE JUNTO A LA BARRA DE HERRAMIENTAS Y,
DESPUÉS, HAGA CLIC EN MÁS COMANDOS.
b. EN COMANDOS DISPONIBLES EN, SELECCIONE TODOS LOS
COMANDOS.
c. EN LA LISTA, SELECCIONE ASISTENTE PARA TABLAS Y GRÁFICOS DINÁMICOS,
HAGA CLIC EN AGREGAR Y, DESPUÉS, HAGA CLIC EN ACEPTAR.
2. HAGA CLIC EN UNA CELDA EN BLANCO DEL LIBRO (QUE NO FORME PARTE DE UNA TABLA
DINÁMICA).
3. EN LA PÁGINA DEL ASISTENTE PASO 1, HAGA CLIC EN VARIOS
RANGOS DE CONSOLIDACIÓN Y, DESPUÉS, HAGA CLIC EN SIGUIENTE.
4. EN LA PÁGINA DEL ASISTENTE PASO 2A, HAGA CLIC EN CREAR
UN SOLO CAMPO DE PÁGINA Y LUEGO HAGA CLIC EN SIGUIENTE.
5. EN LA PÁGINA DEL ASISTENTE PASO 2B, HAGA LO SIGUIENTE:
a. VUELVA AL LIBRO Y SELECCIONE EL RANGO DE CELDAS. DESPUÉS, EN EL ASISTENTE
PARA TABLAS Y GRÁFICOS DINÁMICOS, HAGA CLIC EN AGREGAR.
SUGERENCIA: SI EL RANGO DE CELDAS SE ENCUENTRA
EN OTRO LIBRO, ABRA EL LIBRO EN PRIMER LUGAR PARA QUE SEA MÁS FÁCIL SELECCIONAR
LOS DATOS.
6. HAGA CLIC EN SIGUIENTE.
7. EN LA PÁGINA DEL ASISTENTE PASO 3, SELECCIONE ENTRE AGREGAR LA TABLA
DINÁMICA A UNA HOJA DE CÁLCULO EXISTENTE O UNA NUEVA Y, DESPUÉS, HAGA CLIC
EN FINALIZAR.
“CONSOLIDAR DATOS
CON VARIOS CAMPOS DE PÁGINA”
PUEDE CREAR VARIOS CAMPOS DE PÁGINA Y
ASIGNAR SUS PROPIOS NOMBRES DE ELEMENTO A CADA RANGO DE ORIGEN. ESTO LE PERMITE
CREAR CONSOLIDACIONES COMPLETAS O PARCIALES. POR EJEMPLO, UN CAMPO DE PÁGINA
QUE CONSOLIDE MARKETING Y VENTAS INDEPENDIENTEMENTE DE FABRICACIÓN Y OTRO CAMPO
DE PÁGINA QUE CONSOLIDE LOS TRES DEPARTAMENTOS. PARA CREAR UNA CONSOLIDACIÓN
QUE USE VARIOS CAMPOS DE PÁGINA, HAGA LO SIGUIENTE:
1. AGREGUE EL ASISTENTE PARA TABLAS Y GRÁFICOS DINÁMICOS A LA BARRA DE
HERRAMIENTAS DE ACCESO RÁPIDO. SIGA ESTOS PASOS:
a. HAGA CLIC EN LA FLECHA QUE APARECE JUNTO A LA BARRA DE HERRAMIENTAS Y,
DESPUÉS, HAGA CLIC EN MÁS COMANDOS.
b. EN COMANDOS DISPONIBLES EN, SELECCIONE TODOS LOS
COMANDOS.
c. EN LA LISTA, SELECCIONE ASISTENTE PARA TABLAS Y GRÁFICOS DINÁMICOS,
HAGA CLIC EN AGREGAR Y, DESPUÉS, HAGA CLIC EN ACEPTAR.
2. HAGA CLIC EN UNA CELDA EN BLANCO DEL LIBRO (QUE NO FORME PARTE DE UNA TABLA
DINÁMICA).
3. EN LA PÁGINA DEL ASISTENTE PASO 1, HAGA CLIC EN VARIOS
RANGOS DE CONSOLIDACIÓN Y, DESPUÉS, HAGA CLIC EN SIGUIENTE.
4. EN LA PÁGINA DEL ASISTENTE PASO 2A, HAGA CLIC EN CAMPOS
DE PÁGINA PERSONALIZADOS Y LUEGO HAGA CLIC EN SIGUIENTE.
5. EN LA PÁGINA DEL ASISTENTE PASO 2B, HAGA LO SIGUIENTE:
a. VUELVA AL LIBRO Y SELECCIONE EL RANGO DE CELDAS. DESPUÉS, EN EL ASISTENTE
PARA TABLAS Y GRÁFICOS DINÁMICOS, HAGA CLIC EN AGREGAR.
SUGERENCIA: SI EL RANGO DE CELDAS SE ENCUENTRA
EN OTRO LIBRO, ABRA EL LIBRO EN PRIMER LUGAR PARA QUE SEA MÁS FÁCIL SELECCIONAR
LOS DATOS.
b. EN ¿CUÁNTOS CAMPOS DE PÁGINA DESEA?, HAGA CLIC EN EL NÚMERO DE
CAMPOS DE PÁGINA QUE QUIERA USAR.
c. EN ¿QUÉ RÓTULOS DE ELEMENTO DESEA QUE USE CADA CAMPO DE PÁGINA PARA
IDENTIFICAR EL RANGO DE DATOS SELECCIONADO?, SELECCIONE PARA CADA CAMPO DE
LA PÁGINA EL RANGO DE CELDAS Y, DESPUÉS, ESCRIBA UN NOMBRE PARA ESE INTERVALO.
EJEMPLO
§
SI HA SELECCIONADO 1 EN ¿CUÁNTOS
CAMPOS DE PÁGINA DESEA?, SELECCIONE CADA RANGO Y LUEGO ESCRIBA UN NOMBRE EN
EL CUADRO CAMPO UNO. SI TIENE CUATRO INTERVALOS Y CADA UNO
CORRESPONDE A UN TRIMESTRE DIFERENTE DEL AÑO FISCAL, SELECCIONE EL PRIMER
RANGO, ESCRIBA T1, SELECCIONE EL SEGUNDO RANGO, ESCRIBA T2 Y REPITA EL PROCESO
PARA T3 Y T4.
§
SI HA SELECCIONADO 2 EN ¿CUÁNTOS
CAMPOS DE PÁGINA DESEA?, HAGA LO MISMO QUE EL EJEMPLO ANTERIOR EN EL
CUADRO CAMPO UNO. DESPUÉS, SELECCIONE DOS RANGOS Y ESCRIBA EL MISMO
NOMBRE, COMO H1 Y H2, EN EL CUADRO CAMPO DOS. SELECCIONE EL PRIMER
RANGO, ESCRIBA H1, SELECCIONE EL SEGUNDO RANGO, ESCRIBA H1, SELECCIONE EL
TERCER RANGO, ESCRIBA H2, SELECCIONE EL CUARTO RANGO Y ESCRIBA H2.
d. HAGA CLIC EN SIGUIENTE.
6. EN LA PÁGINA DEL ASISTENTE PASO 3, SELECCIONE ENTRE AGREGAR LA
TABLA DINÁMICA A UNA HOJA DE CÁLCULO EXISTENTE O UNA NUEVA Y, DESPUÉS, HAGA
CLIC EN FINALIZAR.
PARA MAS INFORMACION:
https://support.office.com/es-es/article/Consolidar-varios-or%C3%ADgenes-de-datos-en-una-tabla-din%C3%A1mica-8f476919-40b3-4133-9870-26f4d9f21ad6
