Trucos y Cursos de Excel
  • Inicio
  • Evolución de Excel
    • Origen de la hoja de cálculo
    • Antecedentes Microsoft Excel
    • Excel 1.0
    • Excel 2.0
      • Excel 2.1.
    • Excel 3.0
      • Excel 3.1
    • Excel 4.0
    • Excel 5.0
    • Excel 7.0 (Excel 95)
    • Excel 8.0 (Excel 97)
    • Excel 9.0 (Excel 2000)
    • Excel 10.0 (Excel 2002 / XP)
    • Excel 11.0 (Excel 2003)
    • Excel 12.0 (Excel 2007)
    • Excel 14.0 (Excel 2010)
    • Excel 15.0 (Excel 2013)
    • Excel 16.0 (Excel 2016)
  • Blog
  • Temarios
    • Excel Básico
    • Excel Avanzado
    • Excel Completo
    • Funciones Excel
    • Excel Macros y VBA
    • PowerPivot para Excel
    • Power View para Excel
    • Power Query para Excel
    • Power Map para Excel
    • Business Intelligence y Excel
    • Excel Financiero
    • Letras, Bonos y Obligaciones
    • Excel Bolsa
    • Excel Derivados Financieros
  • Cursos
  • Servicios
  • Demos
  • Contactar

 

Demostración curso: "Excel Avanzado" 2007, 2010, 2013, 2016 y 365

AnteriorÍndiceSiguiente
Saltar al contenido

LAS REFERENCIAS ESTRUCTURADAS

Las referencias estructuradas nos permiten trabajar con los datos de las tablas de manera fácil y más intuitivo cuando empleamos fórmulas que hacen referencia a una tabla (muchas funciones trabajan con rangos, como BUSCARV / CONSULTARV, que no asumen encabezados).

Son útiles porque los rangos de datos de la tabla cambian a menudo y las referencias de celda de referencias estructuradas se ajustan automáticamente, sin tener que volver a escribir fórmulas cuando se agregan o eliminan filas y columnas de una tabla o se actualizan los datos externos.

Trabajando con tablas (Paso a paso)

Sobre nuestra hoja con datos de las ventas a los clientes deseamos adaptarla para interpretar los resultados.

ABRIR UN NUEVO LIBRO DE EXCEL

1.1. Descargar hoja de cálculo con los datos y descomprimir el archivo .zip, o también...

1.2. Ir a: Cinta de opcionesFicha: Archivo Nuevo. Mostrará el cuadro de diálogo de archivo nuevo.

1.2.1. Ir a: Plantillas disponiblesDoble clic en Libro en blanco; o Ir a: Vista previa de Libro en blanco Pulsar sobre CREAR. Mostrará un libro nuevo.

1.2.2. Insertar los datos según se indica:

 ABCDE
1ClienteTrimestre 1Trimestre 2Trimestre 3Trimestre 4
2Cliente 1450,00625,501.200,001.200,00
3Cliente 2675,00855,00450,001.350,00
4Cliente 31.200,00427,001.500,00450,00
5Cliente 4845,00235,451.200,00855,00
6Cliente 5425,50850,00855,00210,00
Subir

CREAR UNA TABLA

Cuando deseamos convertir el rango de datos en tabla de Excel.

2.1. Ir a: Cinta de opciones Ficha: Insertar Grupo: Tablas Tabla Botón tabla.. Mostrará cuadro de diálogo de tabla.

Cinta de opciones, ficha insertar, tablas.

2.1.1. Ir a: ¿Dónde están los datos de la tabla?Seleccionar el rango A1:E6. Datos con el encabezado.

2.1.2. Activar la Casilla: La lista tiene encabezados. Al incluir la fila1 el encabezado de los datos.

2.1.3. Clic Botón: Aceptar. Cierra la ventana y crea la tabla.

Subir

NOMBRE DE LA TABLA

Podemos aplicar a la tabla el nombre descriptivo deseado.

3.1.Ir a: Cinta de opciones Ficha: Diseño (Herramientas de tabla)Grupo: PropiedadesIr al cuadro: Nombre de la tabla Escribir: Ventas. O el nombre deseado.

Cuadro de nombre de la tabla.
Subir

OPERADORES DE REFERENCIA

Podemos utilizar los operadores de referencia para combinar especificadores de columna, especificando rangos de celdas.

DOS PUNTOS

Empleando el operador de dos puntos ":" u operador de rango para especificar todas las celdas de dos o más columnas adyacentes.

4.1.Clic sobre la celda A10 Escribir: 1 Semestre. Encabezado para el resultado.

4.2. Clic sobre la celda B10 Escribir la función: =SUMA(Ventas[[Trimestre 1]:[Trimestre 2]]).

Sumará todos los datos de la columna Trimestre 1 y Trimestre 2, equivale al rango B2:C6.

PUNTO Y COMA

Empleando el operador de punto y coma ";" u operador de unión para especificar una combinación de dos o más columnas.

5.1.Clic sobre la celda A11 Escribir: 1/3 Trimestre. Encabezado para el resultado.

5.2.Clic sobre la celda B11 Escribir la función: =SUMA(Ventas[[Trimestre 1]:[Trimestre 3]]).

Sumará todos los datos de la columna Trimestre 1 y Trimestre 3, equivale a los rangos B2:B6 y D2:D6

ESPACIO

Empleando el operador de espacio u operador de intersección para especificar la intersección de dos o más columnas.

6.1.Clic sobre la celda A12 Escribir: 3 Trimestre. Encabezado para el resultado.

6.2.Clic sobre la celda B12 Escribir la función: =SUMA(Ventas[[Trimestre 1]:[Trimestre 3]] Ventas[[Trimestre 3]:[Trimestre 4]]).

Sumará todos los datos de la columna Trimestre 3, equivale al rango D2:D6 (intersección de Trimestre 1 / Trimestre 3 y Trimestre 3 / Trimestre 4.

Subir

ESPECIFICADORES DE ELEMENTOS ESPECIALES

Podemos utilizar elementos especiales que hacen referencia a las partes de una tabla, permitiendo de forma fácil referenciarlas en fórmulas.

TODA LA TABLA

Toda la tabla (encabezados, datos y totales incluidos).

7.1. Clic sobre la celda A15 Escribir: Todo. Encabezado para el resultado.

7.2. Clic sobre la celda B15 Escribir la función: =SUMA(Ventas[#Todo]).

SOLO LOS DATOS

8.1. Clic sobre la celda A16 Escribir: Venta más alta. Encabezado para el resultado.

8.2. Clic sobre la celda B16 Escribir la función: =MAX(Ventas[#Datos]).

FILA DE ENCABEZADO

Toda la tabla (encabezados, datos y totales incluidos).

9.1. Clic sobre la celda A17 Escribir: Nº Encabezados. Encabezado para el resultado.

9.2. Clic sobre la celda B17 Escribir la función: =CONTARA(Ventas[#Encabezados])

FILA TOTAL

Toda la tabla (encabezados, datos y totales incluidos).

10.1. Clic sobre la celda A18 Escribir: Total. Encabezado para el resultado.

10.2. Clic sobre la celda B18 Escribir la función: =SUMA(Ventas[#Totales]). Mostrará el error #¡REF! al no estar activada la fila de totales.

10.3. Ir a: Cinta de opciones Ficha Diseño (Herramientas de tabla) Grupo: Opciones de estilo de tabla Clic sobre Fila de totales. El valor de B18 se actualiza al total de los datos.

FILA ACTUAL

Sólo la parte de las columnas de la fila actual. #Esta fila no se puede combinar con ningún especificador de elemento especial. Permite forzar una intersección implícita de la referencia o para invalidar ese comportamiento y hacer referencia a valores individuales de una columna.

11.1. Clic sobre la celda L2 Escribir: Total Cliente 2. Encabezado para el resultado.

11.2. Clic sobre la celda M2Escribir la función: =SUMA(Ventas[@]). Mostrará la suma de los datos de la fila 2.

Subir

REFERENCIAS ESTRUCTURADAS DE COLUMNAS CALCULADAS

Cuando crea una columna calculada, normalmente utilizamos una referencia estructurada para crear la fórmula. Esta referencia estructurada puede tener un nombre no completo o completo.

Si utilizamos referencias estructuradas en una tabla, como cuando creamos una columna calculada, podemos utilizar una referencia estructurada no calificada, pero si utilizamos esta referencia fuera de la tabla, necesitamos utilizar una referencia estructurada con un nombre completo válido.

NO CALIFICADA

12.1. Clic sobre la celda F1 Escribir: 1 Trim Descuento 5%. Encabezado para la columna.

12.2. Clic sobre la celda F2Escribir la función: =[Trimestre 1]*5/100. La referencia no hace mención a la tabla ventas.

NOMBRE COMPLETO

13.1. Clic sobre la celda A19 Escribir: 1 Trim Descuento 5%. Encabezado para la columna.

13.2. Clic sobre la celda B19Escribir la función: =SUMA(Ventas[Trimestre 1])*5/100. La referencia hace mención a la tabla ventas.

Subir

AUTOCOMPLETAR

La opción de autocompletar en las tablas, permite autocompletar en una fórmula los nombres de tablas, columnas y demás estructuras.

14.1. Clic sobre la celda A20 Escribir: 1 Semestre. Encabezado para el resultado.

14.2. Clic sobre la celda B20Escribir: =SUMA(V. Mostrará ventana desplegable de autocompletar, con las entradas que comienzan por V (incluyendo fórmulas, rangos y nombres.

Celda A20 con el valor 1 Semestre.

14.3. Ir al: Desplegable Clic sobre Ventas. Insertará el nombre de la tabla.

14.4. Escribir [ (apertura de corchete). Mostrará ventana desplegable de autocompletar, con los nombres de columnas y estructuras especiales.

Celda B20 con la función =SUMA(Ventas.

14.5. Ir al: Desplegable Clic sobre Trimestre 1. Insertará el nombre de la columna.

14.6. Escribir ]:Ven (cierre de corchete, dos puntos Ven). Mostrará ventana desplegable de autocompletar.

14.7. Ir al: Desplegable Clic sobre Ventas. Insertará el nombre de la tabla.

14.8. Escribir [Trim (apertura de corchete, trim). Mostrará ventana desplegable de autocompletar.

14.9. Ir al: Desplegable Clic sobre Trimestre 3. Insertará el nombre de la columna.

14.10. Escribir ]) (cierre de corchete y cierre de paréntesis). Aplica el valor de la fórmula.

Subir

REGLAS DE SINTAXIS

Lista de reglas de sintaxis para crear y editar referencias estructuradas.

CORCHETES EN LOS ESPECIFICADORES

Todos los especificadores de tablas, columnas y elementos especiales deben ir incluidos entre corchetes ([ ]). Un especificador que contenga otros especificadores requiere corchetes externos para incluir los corchetes internos de los otros especificadores.

=SUMA(Ventas[[Trimestre 1]:[Trimestre 2]]). Muestra la suma de los datos de Trimestre 1 y Trimestre 2 en la tabla Ventas.

ENCABEZADOS DE COLUMNAS CON CADENA DE TEXTO

Los encabezados de columna son cadenas de texto, pero no es necesario que vayan entre comillas cuando se utilizan en una referencia estructurada. Si un encabezado de columna contiene números o fechas, como 2011 o 1/1/2011, se siguen considerando como cadenas de texto. Debido a que los encabezados de columna son cadenas de texto, no se pueden utilizar expresiones entre corchetes.

=Ventas[[Trimestre 1]:[Trimestre 2]]. Encabezados con espacio, Trimestre 1 y Trimestre 2.

=Ventas[[2010]:[2011]]. Encabezados con años 2010 y 2011.

=Ventas[[01/01/2011]:[01/02/2011]]. Encabezados con fechas 01/01/2011 y 01/02/2011.

CARACTERES ESPECIALES EN ENCABEZADOS DE COLUMNA

Si un encabezado de columna contiene uno de los siguientes caracteres especiales, se debe incluir todo el encabezado entre corchetes. Por lo que los corchetes dobles son necesarios en un especificador de columna con los caracteres especiales siguientes: espacio, tabulación, avance de línea, retorno de carro, coma (,), dos puntos (:), punto (.), corchete de apertura ([), corchete de cierre (]), símbolo de almohadilla (#), comillas simples ('), comillas dobles ("), llave izquierda ({), llave derecha (}), símbolo de dólar ($), acento circunflejo (^), "y" comercial (&), asterisco (*), signo más (+), signo igual (=), signo menos (-), signo mayor que (>), signo menor que (<) y signo de división (/).

=SUMA(Ventas[Trimestre$4])

=SUMA(Ventas[Trimestre"4"])

=SUMA(Ventas[Trimestre 4])

La única excepción a esta regla es cuando sólo se utiliza el carácter especial de espacio.

CARACTERES ESPECIALES EN ENCABEZADOS CON CARÁCTER DE ESCAPE

Los siguientes caracteres tienen un significado especial y requieren el uso de comillas simples (') como un carácter de escape: corchete de apertura ([), corchete de cierre (]), símbolo de almohadilla (#) y comillas simples (').

=Ventas['#c]

CARÁCTER DE ESPACIO

Los caracteres de espacio se pueden utilizar para mejorar la legibilidad de la forma siguiente:

=Ventas[[Trimestre 1]:[Trimestre 2]]

=Ventas[[#Encabezados];[#Datos];[Trimestre1]]

Subir
AnteriorÍndiceSiguiente
Barra de navegación por la página
Ref. Estructuradas
  • Crear una tabla
  • Nombre de la tabla
  • Operadores de referencia
    • Dos puntos
    • Punto y coma
    • Espacio
  • Elementos especiales
    • Toda la tabla
    • Solo los datos
    • Fila de encabezado
    • Fila total
    • Fila actual
  • Columna calculada
    • No calificada
    • Nombre completo
  • Autocompletar
  • Reglas de sintaxis
Barra de navegación: Consultar también
Consultar también
  • Insertar datos
  • Insertar fórmulas
  • Fórmulas avanzadas
  • Cálculo automático
  • Insertar funciones
  • Errores en fórmulas
  • Listas personalizadas
  • Rellenar
  • Nombres

©2016 - trucosycursos.es

Política de privacidad

Scroll Up
error:
DSGVO Logo El sitio web utiliza cookies propias y de terceros para recopilar información que ayuda a optimizar su visita a sus páginas web. No se utilizarán las cookies para recoger información de carácter personal. Usted puede permitir su uso o rechazarlo, también puede cambiar su configuración siempre que lo desee... Aceptar Rechazar Configuración