Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
2019 Miguel Angel Franco Garcia 15-6-2019 Tablas en Excel M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 1 | 20 Tablas Podemos crear una tabla y aplicarle formato para agrupar de forma visual los datos y analizarlos. Las tablas de Excel no se deben confundir con las tablas de datos. Para facilitar la administración y el análisis de un grupo de datos relacionados, podemos convertir una intervalo de celdas en una tabla de Excel. No podemos crear o insertar tablas en un libro compartido. Crear una tabla Podemos crear todas las tablas que deseemos en una hoja de cálculo. Para crear rápidamente una tabla en Excel, hacemos lo siguiente: Seleccione la celda o el rango en los datos. Hacemos clic en Insertar – Tabla, o, pulsamos <CTRL> <T>. Hacemos clic en “La tabla tiene encabezados”. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 2 | 20 Elementos de una tabla Una tabla puede incluir los elementos siguientes: Fila de encabezado: De manera predeterminada, una tabla tiene una fila de encabezado. Cada columna de la tabla tiene habilitado el filtrado en la fila de encabezado para que pueda filtrar u ordenar los datos de la tabla rápidamente. Puede desactivar la fila de encabezado en una tabla con el atajo de teclado <CTRL> <SHIFT> <L>. Filas con bandas: El sombreado alternativo o la agrupación en bandas ayudan a distinguir mejor los datos. Columnas calculadas: Al escribir una fórmula en una celda de una columna de tabla, puede crear una columna calculada en la que esa fórmula se aplica de forma instantánea a todas las demás celdas de la columna de la tabla. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 3 | 20 Fila de total: Una vez que agregue una fila de totales a una tabla, Excel le proporcionará una lista desplegable para seleccionar funciones como suma, promedio, etc. Al seleccionar una de estas opciones, la tabla las convertirá automáticamente a una función de subtotales, que omitirá las filas que se han ocultado de forma predeterminada con un filtro. Si desea incluir las filas ocultas en los cálculos, puede cambiar los argumentos de la función subtotales. Para activar la fila de total, en la pestaña Diseño de tabla, clic en Fila de totales. Añadir filas de totales Teniendo los siguientes datos. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 4 | 20 Clic en Diseño de tabla y Filas de totales. Hacemos clic en la flecha hacia abajo en C6, se desplegará un menú, elegimos Suma. Ya tenemos el total de la columna C, copiamos hacia la izquierda. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 5 | 20 Controlador de tamaño: Un controlador de tamaño en la esquina inferior derecha de la tabla le permite arrastrar la tabla hasta el tamaño que desee. Cambiar tamaño de una tabla Después de crear una tabla de Excel en la hoja de cálculo, puede agregar o quitar fácilmente filas y columnas de tabla. Puede usar el comando cambiar tamaño de Excel para agregar filas y columnas a una tabla: Haga clic en cualquier lugar de la tabla y aparecerá la opción herramientas de tabla . Haga clic en diseño > cambiar el tamaño de la tabla. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 6 | 20 Seleccionamos el nuevo rango. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 7 | 20 Aceptamos. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 8 | 20 Convertir una tabla en rango Después de crear una tabla de Excel, es posible que solo desee el estilo de tabla sin la funcionalidad de la tabla. Para dejar de trabajar con los datos en una tabla sin perder ningún formato de estilo de tabla que haya aplicado, puede convertir la tabla en un rango normal de datos en la hoja de cálculo. 1. Haga clic en cualquier lugar de la tabla y, a continuación, vaya a herramientas de tabla > diseño en la cinta de opciones. En un equipo Mac, vaya a la pestaña tabla . 2. En el grupo herramientas , haga clic en convertir en rango. Después de volver a convertir la tabla en un rango, las funciones de tabla dejan de estar disponibles. Por ejemplo, los encabezados de fila ya no incluyen las flechas de ordenar y filtrar y las referencias estructuradas. Usar referencias estructuradas Cuando se crea una tabla de Excel, Excel asigna un nombre a la tabla y a cada encabezado de columna de la tabla. Cuando se agregan fórmulas a una tabla de Excel, estos nombres pueden aparecer automáticamente a medida que se escribe la fórmula y se seleccionan las referencias de cada celda en la tabla en lugar de especificarlas manualmente. Este es un ejemplo de lo que hace Excel: En lugar de usar referencias de celda explícitas Excel usa nombres de tabla y columna =SUMA(C2:C7) =SUMA(Ventas[Importe de ventas]) M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 9 | 20 Dicha combinación de nombres de tabla y columna se denomina referencia estructurada, y Excel las usa porque los nombres se ajustan siempre que agregue o quite datos de la tabla. Las referencias estructuradas también aparecen cuando crea una fórmula fuera de una tabla de Excel que hace referencia a los datos de la tabla. Las referencias pueden facilitar la localización de tablas en un libro grande. Para usar referencias estructuradas en la fórmula, haga clic en las celdas de la tabla a las que desea hacer referencia en lugar de escribir su referencia de celda en la fórmula. Vamos a usar los siguientes datos de ejemplo para introducir una fórmula que usa automáticamente referencias estructuradas para calcular la cantidad de una comisión de venta. 1. Creamos una tabla, seleccione cualquier celda del rango de datos y presionamos Ctrl + T. 2. Asegurarnos de que la casilla la tabla tiene encabezados está activada y haga clic en Aceptar. 3. Haga clic en la celda L2, escriba un signo igual (=) y haga clic en la celda J2. En la barra de fórmulas, la referencia estructurada [@Total] aparece después del signo igual. 4. Escriba un asterisco (*) directamente después del corchete de cierre y haga clic en la celda G2. En la barra de fórmulas, la referencia estructurada *[@Cantidad] aparece después del asterisco. 5. Presionar Entrar. Excel crea automáticamente una columna calculada y copia la fórmula debajo de toda la columna. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 10 | 20 Si especifica referencias de celda explícitas en una columna calculada, puede resultar más difícil ver qué está calculando la fórmula. 1. En la hoja de cálculo de Ventas de celulares, haga clic en la celda L2 2. En la barra de fórmulas, escriba = G2 * DI y presione entrar. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 11 | 20 Cambiar un nombre de tabla Siempre que crea una tabla Excel, crea un nombre de tabla predeterminado (Tabla1, Tabla2, etc.). Puede cambiar el nombre de la tabla para que sea más significativo. 1. Seleccione cualquier celda de la tabla para mostrar la pestaña Diseño de tabla > Propiedades en la cinta de opciones. 2. Escriba el nombre que desee en el cuadro Nombre de la tabla y presione entrar. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 12 | 20 Reglas para los nombres de tabla: Usar caracteres válidos. Siempre empieza un nombre con una letra, un carácter de subrayado (_) o una barra diagonal inversa (\). Use letras, números, puntos y caracteres de subrayado para el resto del nombre. No puede usar "C","c", "R" o "r" para el nombre porque ya están designados como un método abreviado para seleccionar la columna o fila de la celda activa cuando las introduce en el cuadro nombre o ir a . No use referencias de celda. Los nombres no pueden coincidir con una referencia de celda, como Z 100 o F1C1. No usar un espacio para separar palabras. No se pueden usar espacios en el nombre. Puede usar el carácter de subrayado (_) y el punto (.) como separadores de palabras. No utilice más de 255 caracteres un nombre de tabla puede tener hasta 255 caracteres. Usar nombres de tabla única. Nombres duplicados no están permitidos. Excel no distingue entre mayúsculas y minúsculas en los nombres si introduces "Ventas" pero si ya tiene otro nombre llamado "VENTAS" en el mismo libro, se le pedirá que elija un nombre único. Usar un identificador de objeto. Si piensa tener una combinación de tablas, tablas dinámicas y gráficos, es una buena idea prefijar sus nombres con el tipo de objeto. Por ejemplo: tbl_Ventas hace referencia a una tabla de ventas, pt_Ventas para una tabla dinámica de ventas y chrt_Ventas para un gráfico de ventas. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 13 | 20 Reglas de sintaxis de las referencias estructuradas También puede introducir o cambiar manualmente las referencias estructuradas en una fórmula, pero para hacerlo, le ayudará a comprender la sintaxis de referencia estructurada. Pasemos por el siguiente ejemplo de fórmula: =SUMA(Venta[@Costo];[@[PVP Venta]]) Esta fórmula tiene los siguientes componentes de referencia estructurada: Nombre de tabla: Venta es un nombre de tabla personalizado. Hace referencia a los datos de la tabla, sin encabezado ni filas de totales. Especificador de columna: [@Costo] y [@PVP Venta] son especificadores de columna que usan los nombres de las columnas que representan. Hacen referencia a los datos de la columna, sin ningún encabezado de columna ni fila de total. Siempre encierra los especificadores entre corchetes, tal como se muestra. Especificador de elemento: [@Costo] y [@PVP Venta] son especificadores de elementos especiales que hacen referencia a partes específicas de la tabla, como la fila de totales. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 14 | 20 Cuando cree o edite referencias estructuras, use estas reglas de sintaxis: Usar los especificadores entre corchetes. Todos los especificadores de tabla, columna y elemento deben estar entre corchetes ([]). Los encabezados de columna son cadenas de texto. Números o fechas, como 2019 o 1/1/2019, también se consideran cadenas de texto. No puede usar expresiones con los encabezados de columna. Por ejemplo, la expresión SUMA(Venta[[Cantidad]:[PVP Venta]]) no funcionará. Especificadores de elementos especiales Para hacer referencia a partes específicas de una tabla, como la fila de totales, puede usar cualquiera de los siguientes especificadores de elementos especiales de la referencias estructuradas. #Todo Toda la tabla, incluidos los encabezados de columna, datos y totales (si los hay). Ejemplo: =Venta[#Todo] =Venta[[#Todo];[Cantidad]] En este caso, nos devuelve cada fila de la columna Cantidad. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 15 | 20 #Datos Solo las filas de datos. Ejemplo: =Venta[#Datos] #Encabezados Solo la fila de encabezado. Ejemplo: =Venta[#Encabezados] =Venta[[#Encabezados];[País]] M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 16 | 20 Devuelve para cada fila el encabezado de la columna País. #Totales Solo la fila del total. Si no hay ninguna, devuelve un valor nulo. Ejemplo: =Venta[#Totales] =Venta[[#Totales];[Cantidad]] Devuelve el total de la columna cantidad, si no hay total, devuelve nulo. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 17 | 20 #Esta Fila O bien @ O bien Solo las celdas en la misma fila que la fórmula. Estos especificadores no se pueden combinar con ningún especificador de elemento especial. Utilícela para forzar una intersección implícita de la referencia o para invalidar ese comportamiento y hacer referencia a valores individuales de una columna. Excel cambia automáticamente de #Esta Fila a los especificadores @ en las tablas en las que tiene más de una fila de datos. Si la tabla tiene solo una fila, Excel no reemplaza los especificadores #Esta Fila, lo que puede ocasionar cambios inesperados en los resultados M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 18 | 20 @ [Nombre de columna] del cálculo al agregar más filas. Para evitar problemas de cálculo, asegúrese de escribir varias filas en la tabla antes de introducir cualquier fórmula de referencia estructurada. Ejemplo: =[@Cantidad] Devuelve la cantidad fila a fila de la columna Cantidad. Calificar referencias estructuradas de columnas calculadas Cuando crea una columna calculada, a menudo usa una referencia estructurada para crear la fórmula. Esta referencia estructurada puede tener un nombre no completo o parcialmente completo. Por ejemplo, para crear la columna calculada denominada ImpCom, que calcula el importe de las comisiones (15%), puede utilizar las siguientes fórmulas: =[Total]*15% O =[Total]*[Comision] M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 19 | 20 Decidir si se generan referencias estructuradas para tablas De manera predeterminada, cuando se crea una fórmula, al hacer clic en un rango de celdas dentro de una tabla, se seleccionan las celdas y se escribe automáticamente una referencia estructurada en lugar del rango de celdas en la fórmula. Este comportamiento de semis elección hace que sea mucho más fácil introducir una referencia estructurada. Puede activar o desactivar este comportamiento activando o desactivando la casilla de verificación usar nombres de tabla en fórmulas en la > Opciones del archivo > fórmulas > trabajar con el diálogo fórmulas. Desactivar los encabezados de columna Puede activar o desactivar los encabezados de columna de una tabla en la pestaña diseño de tabla > fila de encabezado. Si desactiva los encabezados de columna de una tabla, las referencias estructuradas que usan los nombres de columna no se verán afectadas y podrá usarlos en fórmulas. Las referencias estructuradas que hacen referencia directa a los encabezados de tabla (por ejemplo =Venta[@];[Cantidad] darán lugar a #REF. M i g u e l Á n g e l F r a n c o G a r c í a . 1 5 / 0 6 / 2 0 1 9 P á g i n a 20 | 20
Compartir