Administrar rangos de datos externos y sus propiedades

Nota:  Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.

Puede cambiar las propiedades de un rango de datos externos para controlar los datos que se ha importado en una hoja de cálculo mediante una conexión a un origen de datos externo, como una base de datos, una consulta Web o un archivo de texto.

Un rango de datos externos (también llamado una tabla de consulta) es un nombre definido o el nombre de la tabla que define la ubicación de los datos que entra en una hoja de cálculo. Cuando se conecta a datos externos, Excel crea automáticamente un rango de datos externos. La única excepción a esto es un informe de tabla dinámica que está conectado a un origen de datos, un informe de tabla dinámica no utiliza un rango de datos externos. En Excel, puede dar formato y disponer de un rango de datos externos o usar en los cálculos, como ocurre con cualquier otro dato.

Propiedades del rango de datos externos

Un rango de datos externo tiene propiedades adicionales (no confundir con las propiedades de conexión) que puede usar para controlar los datos, como la conservación de ancho de columna y el formato de celda. Puede cambiar las propiedades del rango de datos externos haciendo clic en Propiedades en el grupo conexiones, en la pestaña datos. A continuación, realice los cambios mediante el procedimiento siguiente:

  • Para los rangos de datos externos que se crean desde Microsoft Query y el Asistente para la conexión de datos, use el cuadro de diálogo de Propiedades de datos externos.

  • Para los rangos de datos externos que se crean desde un archivo de texto importado o una consulta Web que recupera datos HTML, use el cuadro de diálogo Propiedades del rango de datos externos.

  • Para los rangos de datos externos que se crean a partir de una consulta Web que recupera datos XML, use el cuadro de diálogo Propiedades de la asignación XML.

Uso de plantillas con rangos de datos externos

Si desea compartir un resumen o un informe que se basa en datos externos, puede dar a otros usuarios un libro que contenga un rango de datos externos o puede crear una plantilla de informe. Un plantilla de informe le permite guardar el resumen o informe sin guardar los datos externos para que el archivo es más pequeño. Cuando un usuario abre la plantilla de informe, se recuperan los datos externos.

  1. Seleccione la hoja de cálculo en la que desea buscar un rango de datos externos.

  2. En el barra de fórmulas, haga clic en la flecha situada junto al Cuadro Nombre y, a continuación, haga clic en el nombre del rango de datos externos que desee.

  1. En el grupo Nombres definidos de la pestaña Fórmulas, haga clic en Administrador de nombres.

    Imagen de la cinta de Excel

  2. En el cuadro de diálogo Administrador de nombres, haga clic en el nombre del rango de datos externos y, a continuación, haga clic en Editar. También puede haga doble clic en el nombre.

  3. En el cuadro de diálogo Editar nombre, escriba el nuevo nombre de la referencia en el cuadro nombre.

Nota: El botón Cerrar cierra del cuadro de diálogo Administrador de nombres. No tiene que hacer clic en Cerrar para guardar los cambios que haya hecho ya.

Puede cambiar la consulta subyacente para un rango de datos externos que se creó a partir de Microsoft Query, un archivo de texto importado, una consulta Web o el Asistente para la conexión de datos.

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  3. En el cuadro de diálogo Propiedades de datos externos, haga clic en Propiedades de conexión Imagen del botón .

  4. En el cuadro de diálogo Propiedades de conexión, haga clic en la pestaña definición y, a continuación, haga clic en Editar consulta.

Para obtener más información sobre la creación y modificación de consultas en Microsoft Query, vea la Ayuda de Microsoft Query.

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña Datos, en el grupo Conexiones, haga clic en Conexiones.

    Imagen de la cinta de opciones de Excel

  3. En el cuadro de diálogo Conexiones del libro, haga clic en Propiedades.

  4. En el cuadro de diálogo Propiedades de conexión, haga clic en la pestaña definición y, a continuación, haga clic en Editar consulta.

  5. En el cuadro de diálogo Importar archivo de texto, haga clic en Importar.

  6. Realizar cambios en el archivo de texto importado en el Asistente para importar texto y, a continuación, haga clic en Finalizar.

    Para obtener más información acerca de cómo importar archivos de texto, consulte importar o exportar archivos de texto.

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  3. En el cuadro de diálogo Conexiones del libro, haga clic en Propiedades.

  4. En el cuadro de diálogo Propiedades de conexión, haga clic en la pestaña definición y, a continuación, haga clic en Editar consulta.

  5. Realizar cambios en la consulta Web en el cuadro de diálogo Modificar consulta Web y, a continuación, haga clic en Finalizar.

Para obtener más información sobre la creación y modificación de consultas Web, vea conectarse a una página web (Power Query).

  1. Haga clic en una celda del rango de datos externos para el que desea cambiar la consulta subyacente.

  2. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  3. En el cuadro de diálogo Propiedades de datos externos, haga clic en Propiedades de conexión Imagen del botón .

  4. En el cuadro de diálogo Propiedades de conexión, haga clic en la pestaña definición.

  5. Siga uno de estos procedimientos:

    • En el cuadro tipo de comando, haga clic en tabla y, a continuación, en el cuadro de texto de comando, cambie el valor en el nombre de una tabla, vista o consulta.

    • En el cuadro tipo de comando, haga clic en SQL o predeterminado y, a continuación, en el cuadro de texto del comando editar la instrucción SQL.

      Nota: Dependiendo de cómo se ha definido la conexión, el cuadro tipo de comando puede no estar disponible (aparece atenuada).

  1. En la barra de fórmulas, haga clic en la flecha situada junto al Cuadro Nombre y seleccione el nombre del rango de datos externos que desee copiar.

    Para una tabla de Excel, seleccione el nombre del rango y, a continuación, presione CTRL + A para seleccionar los encabezados de tabla.

    Si desea incluir rótulos de columna o fórmulas que no forman parte del rango de datos externos, seleccione las celdas que contienen las etiquetas de columna o fórmulas que desea copiar. Haga clic en la flecha situada junto al Cuadro Nombre en la barra de fórmulas y haga clic en el nombre del rango de datos externos que desee copiar.

  2. En el grupo Portapapeles de la pestaña Inicio, haga clic en Copiar.

    Imagen de la cinta de Excel

  3. Cambie al libro en el que desea pegar el rango de datos externos.

  4. Haga clic en la celda superior izquierda de la área de pegado.

    Para asegurarse de que el datos externos no reemplaza los datos existentes, asegúrese de que la hoja de cálculo no contiene datos debajo o a la derecha de la celda que hace clic en.

  5. En el grupo Portapapeles de la pestaña Inicio, haga clic en Pegar.

Nota: Si copia solo una parte de un rango de datos externos, no se copiará la subyacentes consulta y no se pueden actualizar los datos copiados.

Puede controlar cómo tratar un conjunto de datos mayor o menor que se devuelven a Excel cuando se actualizan los datos.

  1. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  2. En el cuadro de diálogo Propiedades del rango de datos externos, Si cambia el número de filas en el rango de datos tras la actualización, haga clic en uno de estos procedimientos:

    • Insertar celdas para los nuevos datos, eliminar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas en el origen de datos, bajar celdas directamente en el rango de datos externos, pero no mover celdas a la derecha del rango de datos externos.

      • Cuando se eliminan una o varias filas en el origen de datos, Subir celdas directamente en el rango de datos externos, pero no mover celdas a la derecha del rango de datos externos.

    • Insertar filas para los datos nuevos, borrar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas en el origen de datos, mover celdas directamente debajo y a la derecha del rango de datos externos hacia abajo.

      • Cuando se eliminan una o varias filas en el origen de datos, no mover celdas directamente debajo y a la derecha del rango de datos externos.

    • Sobrescribir celdas existentes con los datos nuevos, borrar celdas sin usar

      Notas: 

      • Cuando se agregan una o más filas en el origen de datos, se sobrescriben celdas directamente en el rango de datos externos, pero no mover celdas a la derecha del rango de datos externos.

      • Cuando se eliminan una o varias filas en el origen de datos, no mover celdas directamente debajo y a la derecha del rango de datos externos.

  1. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  2. En Diseño y formato de datos, siga uno o varios de estos procedimientos:

    • Para incluir los nombres de campo como la primera fila, active la casilla de verificación incluir nombres de campos.

      Nota: Esta casilla solo está disponible para un rango de datos externos que se convierte desde un programa de Microsoft Office 2003, o a un rango de datos externos que se crea mediante programación.

    • Para agregar una columna de números de fila, seleccione la casilla de verificación incluir números de fila.

      Nota: Esta casilla de verificación no está disponible para un archivo de texto importado, un archivo XML o una consulta Web.

    • Para conservar el formato que aplique de celda, seleccione la casilla de verificación mantener el formato de celda.

      Nota: Una consulta Web, esta opción se desactiva automáticamente cuando se selecciona Formato HTML completo en el cuadro de diálogo de Opciones. Obtener acceso a cuadro de diálogo Opciones del cuadro de diálogo Editar consulta.

    • Para conservar los anchos de columna que ha configurado, seleccione la casilla de verificación ajustar el ancho de columna.

    • Para conservar la columna de ordenación, filtrado y el diseño que aplicar, seleccione la casilla de verificación Mantener orden, filtro y diseño de columna.

      Nota: Esta casilla de verificación no está disponible para una consulta Web.

    • Para conservar el filtrado de columna que aplicar, seleccione la casilla de verificación Mantener filtro de columna.

      Nota: Esta casilla de verificación sólo está disponible para una consulta Web que se basa en datos XML.

Nota: El procedimiento siguiente se aplica únicamente a un rango de datos externos que se ha convertido desde un programa de Microsoft Office 2003, o en un rango de datos externos que se crea mediante programación. Todos los rangos de datos externos se crean mediante la interfaz de usuario se crean como tablas de Excel. Estas tablas se expanden automáticamente cuando nuevas filas se agregan y rellenar hacia abajo columnas calculadas.

  1. Escribir una fórmula en una celda adyacente a la primera fila de datos en el rango de datos externos.

    Nota: La primera fila de datos puede ser la primera o la segunda fila del rango de datos externos, dependiendo de si la primera fila contiene encabezados.

  2. Seleccione la celda y haga doble clic en el controlador de relleno para copiar la fórmula a todas las filas en el rango de datos externos.

  3. Haga clic en una celda en el rango de datos externos.

  4. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  5. En el cuadro de diálogo Propiedades del rango de datos externos, active la casilla de verificación Autorrellenar fórmulas en columnas adyacentes.

    Nota: Si el rango de datos externos se expande al que actualizar, Excel copia únicamente las fórmulas inmediatamente adyacentes o dentro del rango de datos externos.

Al inmovilizar un rango de datos externos, conserva los datos pero no la consulta subyacente, de modo que no se puede actualizar un rango de datos externos inmovilizado.

  1. Haga clic en la hoja de cálculo que contiene la tabla de Excel desde el que desea quitar la conexión de datos.

  2. En la barra de fórmulas, haga clic en la flecha situada junto al Cuadro Nombre y, a continuación, haga clic en el nombre del rango de datos externos del que desea quitar la conexión de datos. A continuación, se selecciona el rango de datos.

  3. En la ficha Herramientas, en el grupo Datos externos de tabla, haga clic en Desvincular. El rango de datos permanece y aún tiene el mismo nombre, pero se elimina la conexión.

Nota: El procedimiento siguiente se aplica únicamente a un rango de datos externos que se ha convertido desde un programa de Microsoft Office 2003, o en un rango de datos externos que se crea mediante programación. Todos los rangos de datos externos se crean mediante la interfaz de usuario se crean como tablas de Excel. Estas tablas se expanden automáticamente cuando nuevas filas se agregan y rellenar hacia abajo columnas calculadas.

  1. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  2. En la definición de la consulta, desactive la casilla de verificación Guardar definición de consulta.

Nota: Cuando guarde el libro, la consulta subyacente se elimina del libro. Sin embargo, no se eliminan los archivos de consulta guardada de la base de datos (archivos .dqy o .odc). Si ha guardado la consulta cuando creó en el Asistente para consultas o en Microsoft Query, se guarda el archivo de consulta en el equipo y puede utilizar la consulta de nuevo para recuperar datos externos. Para obtener más información, consulte la Ayuda de Microsoft Query.

Nota: El procedimiento siguiente aplica solo a un rango de datos externos que se crea mediante el Asistente para la conexión de datos o Microsoft Query (y no a un archivo de texto importado o una consulta Web), un rango de datos externos convertido a partir de un programa de Microsoft Office 2003 o una referencia externa rango de datos creado mediante programación. Todos los rangos de datos externos se crean mediante la interfaz de usuario se crean como tablas de Excel.

  1. Insertar una fila en blanco encima del rango de datos externos, realice uno de estos procedimientos:

    • Seleccione una celda anterior a la que desea insertar la nueva fila. A continuación, en la ficha Inicio, en el grupo celdas, haga clic en la flecha situada junto a Insertar y, a continuación, haga clic en Insertar filas de hoja. Dado que seleccionó una celda, se inserta una nueva fila encima de la fila actual.

    • Haga clic en el selector de fila de la fila encima de la cual desea insertar una nueva fila. A continuación, en la ficha Inicio, en el grupo celdas, haga clic en Insertar. Dado que ha seleccionado toda una fila, se inserta una nueva fila encima de la fila seleccionada.

  2. Escriba las etiquetas que desee en las celdas de la fila en blanco.

  3. Haga clic en una celda en el rango de datos externos.

  4. En la pestaña datos, en el grupo conexiones, haga clic en Propiedades.

    Imagen de la cinta de opciones de Excel

  5. En el cuadro de diálogo Propiedades del rango de datos externos, en Diseño y formato de datos, desactive la casilla de verificación incluir nombres de campo y, a continuación, haga clic en Aceptar.

  6. Para quitar los nombres de campo existentes y actualizar el rango de datos externos, haga clic en una celda del rango de datos externos y, a continuación, haga clic en Actualizar datos Imagen del botón .

Nota: Al recuperar datos de una base de datos, los cambios de nombres de columna en Microsoft Query se mantienen en el rango de datos externos. Para obtener información acerca de cómo cambiar los nombres de columna en la consulta, vea la Ayuda de Microsoft Query.

  1. Haga clic en la hoja de cálculo que contiene el rango de datos externos que desee eliminar.

  2. En la barra de fórmulas, haga clic en la flecha situada junto al Cuadro Nombre y, a continuación, haga clic en el nombre del rango de datos externos que desee eliminar.

    Si el rango de datos externos es una tabla de Excel, presione CTRL+A para seleccionar toda la tabla.

  3. Para eliminar el rango de datos externos, presione la tecla Suprimir.

  4. Para eliminar la consulta subyacente, haga clic en cuando Excel le pide que.

¿Necesita más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×