¡Descarga Introducción a las Tablas de Datos en Excel y más Apuntes en PDF de Informática solo en Docsity!
Excel
Índice de contenidos: Tablas de datos Referencias relativas y absolutas Funciones vinculando hojas de calculo: SUMA, CONTAR,CONTARA,CONTAR SI Función BUSCARV Función SI
Modulo 1
1. Tablas de Datos
Esta es una herramienta sumamente útil a la hora de manejar listas con un volumen elevado de datos, puesto que permite tener nuestros cálculos y operaciones con bases de datos actualizadas y sin necesidad de estar cambiando las dimensiones de las listas a medida que vamos incorporando nuevas filas o nuevas columnas. Facilitan, por tanto, el dinamismo de nuestros datos y las operaciones que realicemos sobre los mismos. Insertar Tablas: Por defecto tenemos los datos en un rango de celdas y lo que queremos es convertir dicho rango en una tabla de datos. Para ello tendremos que
posicionar la celda activa en cualquier celda de la lista que vamos a convertir en tabla Seguidamente, vamos a la ficha INSERTAR y en el grupo de botones TABLAS, pulsamos el botón TABLA El cual nos presenta el siguiente cuadro de diálogo: En el que vemos, que selecciona el rango que de la lista en la que hemos colocado la celda activa y siempre dejando activado el check : La tabla tiene encabezados. Este check indica que la primera fila del rango de celdas que vamos a convertir en tabla se convertirá la fila de los títulos, denominada fila de encabezados.
Importante: Hay que destacar que es OBLIGATORIO que todas las tablas tengan su fila de encabezados, de hecho, es la única fila de la tabla que Excel nunca nos va a permitir eliminar. De esta forma, siempre que vayamos a convertir un rango de celdas en una tabla, nos aseguraremos de que siempre la primera fila sea la de los títulos. ¿Que ocurriria si al convertir un rango en una tabla desactivamos dicho check? Como es obligatorio tener dicha fila de encabezados, Excel creará su propia fila de encabezados insertando una fila por encima de la primera, asignando a la primera columna como nombre COLUMNA 1, a la siguiente COLUMNA 2... y así sucesivamente con cada columna. ¿Como identificamos si los datos están en un rango o en una tabla? Aunque, aparentemente, lo más llamativo es el cambio del formato, podemos identificar que los datos están en una tabla cuando aparece la ficha DISEÑO DE TABLA en la cinta de opciones, a continuación siempre de la última ficha en el menú de Excel. Más adelante veremos que este formato lo podremos cambiar e incluso dejar la tabla con la apariencia de un rango. Ten en cuenta: La ficha DISEÑO DE TABLA solo se muestra siempre y cuando la celda activa esté dentro de cualquier celda de la tabla. En caso contrario, dicha ficha queda oculta, lo que indicaría que los datos forman parte de un rango.
1.1 Diferencias entre Rangos y Tablas
necesario inmovilizar paneles simplemente para visualizar el título de las columnas en la primera fila cuando nos desplazamos por las filas de datos. Recuerda, para que tengamos activadas las características de la tabla, la celda activa ha de estar en cualquier celda, pero siempre dentro de la tabla, de tal forma que tengamos visible la ficha DISEÑO DE TABLA. Insertar e Inmovilizar paneles: (escrito)
1.2 Formato de Tablas
En dicha ficha, a través del grupo de botones ESTILOS DE TABLA , podemos cambiar el formato que viene de manera predefinida cuando convertimos un rango en una tabla. Por defecto nos aplica una combinación de color en azul y blanco, pero podemos cambiar a través del grupo de botones ESTILOS DE TABLA de dicha ficha DISEÑO DE TABLA. Una vez asignado el estilo deseado, podemos cambiar levemente su configuración a través del grupo de botones OPCIONES DE ESTILO DE TABLA, de dicha ficha DISEÑO DE TABLA. Vamos a ver cada una de las opciones, exceptuando la opción FILA DE TOTALES fila de encabezado: para mostrar u ocultar en pantalla la fila de encabezados de la tabla primera columna: Si la activamos, se resalta la primera columna de la tabla con el mismo formato que la fila de encabezados
fila con bandas: si la activamos, muestra las dilas pares en un color y las impares en otro. Si esta desactivada, todas la da el mismo formato
ultima columna: Si la activamos, se resalta la ultima columna de la tabla con el mismo formato que la fila de encabezados
columnas con bandas: Si la activamos, las columnas impares se presentan en un color y las pares en otro
1.3 Filas Totales
La fila de totales es una fila exclusiva de las tablas, los rangos no la tienen. Es una fila que por defecto está oculta y, si se muestra, siempre aparece por debajo de la última fila de la tabla.
Para activarla, tenemos que marcar el check de Fila de totales del grupo de botones OPCIONES DE ESTILO DE TABLA de la ficha DISEÑO DE TABLA. Una vez marcado, Excel se desplaza por debajo de la última fila de la tabla, mostrando dicha fila de totales, y además
En la primera columna muestra el texto “TOTALˮ. En la última columna de la tabla: Si es texto cuenta los datos Si es numérico los suma por defecto
En dicha fila de totales, podemos asignar a cada columna de la tabla la operación de totales con la que la queramos resumir. Para ello, colocamos la celda activa en la ficha de totales de una columna, en la parte derecha muestra un botón que, al hacer clic, despliega una lista con las diferentes funciones con las que podemos totalizar la columna. Basta con pulsar sobre la que queramos definir.
Por ejemplo, en este caso, la última columna de la tabla PAGADO COMERCIAL la está contando por defecto (ya que es texto). En este caso no queremos que aplique ninguna función a dicha columna. Para ello, colocamos la celda activa en dicha celda de totales de la columna y pulsamos el botón que muestra la lista desplegable. Si seleccionamos la opción NINGUNO, que viene seleccionada en la imagen mostrada, deja de totalizar dicha columna.
Sin embargo, la columna IMPORTE COMERCIAL vamos a totalizarla por la función SUMA. Para ello, nos colocamos en la fila de totales en la columna IMPORTE COMERCIAL y, pulsando el botón que muestra la lista desplegable, seleccionamos la función SUMA y pasa a totalizar dicha suma
ten en cuenta: La gran cualidad que tiene la fila de totales es que reacciona ante los filtros, de tal manera que se va actualizando con los datos que mostramos en la tabla cuando emitimos filtros. Lo veremos en el siguiente punto.
La fila de totales siempre se muestra por debajo de la última fila de la tabla. Esto quiere decir que las tablas no admiten filas por debajo de la fila de totales. Por tanto, la fila de totales es una fila que vamos a consultar para
Si a continuación consultamos la fila de totales, veremos cómo los resultados se habrán actualizado al de las filas que nos está mostrando dicho filtro.
Sobre los datos filtrados podemos seguir aplicando más filtros en otras columnas.
Cuando tenemos aplicado un filtro, los rótulos de fila se colocan en color azul; sin embargo, cuando la tabla no está filtrada, los rótulos de fila se mantienen en color negro.
Para quitar los filtros que hemos aplicado, podemos hacerlo bien uno a uno en cada una de las columnas filtradas o bien quitar todos al mismo tiempo. En primer lugar, sabemos las columnas en las que hemos filtrado porque el botón de filtro tiene el dibujo del icono del filtro
Pulsando el botón de filtro de un campo filtrado, podemos seleccionar la opción de Borrar filtro. Por ejemplo, si queremos quitar el filtro del campo CURSO, pulsamos el botón de dicha columna y en el desplegable mostrado seleccionamos la opción Borrar filtro de "CURSO". Y de esta manera solo se elimina el filtro aplicado a la columna curso, pero el resto de las columnas siguen filtradas.
Si lo que queremos es quitar el filtro de todas las columnas al mismo tiempo, lo hacemos desde la ficha DATOS, en el grupo de botones ORDENAR Y FILTRAR, pulsando el botón BORRAR. De esta manera se eliminan los filtros de todas las columnas, lo sabremos porque los rótulos de fila se vuelven a poner en color negro.
Los filtros que hemos estado realizando, han sido seleccionando diferentes elementos de las columnas, pero también podemos filtrar por tipo de datos, ya que el botón de filtro de cada columna detecta el tipo de dato de esta.
Por ejemplo, si pulsamos nuevamente el botón de filtro del campo CURSO, por encima de los elementos está la opción de filtrar por tipo de dato. Al ser una columna texto, nos muestra la opción de Filtros de texto. Si nos colocamos sobre dicha opción nos despliega las siguientes opciones para filtrar por una columna texto
or ejemplo, vamos a seleccionar la opción termina con para filtrar aquellos cursos que terminan con la letra “Sˮ Pulsamos el botón ACEPTAR de la ventana, nos mostrará los cursos que terminan por dicha letra “Sˮ. De la misma forma, tenemos en un campo numérico los filtros de número. Por ejemplo, si pulsamos el botón de filtro del campo DURACION veremos que nos muestra la opción de Filtros de número , el cual nos muestra las siguientes opciones para poder hacer comparaciones numéricas
Vamos a filtrar las duraciones entre 20 y 35. Para ello seleccionamos el valor Entre... y hacemos la configuración. Pulsando el botón ACEPTAR nos muestra los cursos con duración comprendida en el intervalo indicado.
1.4 Filtrar Datos II
Los campos fecha son campos especiales para los filtros, ya que, si pulsamos el botón de filtro de un campo fecha, en vez de mostrar las diferentes fechas, nos las muestra agrupadas por años, meses y días. Para verlo, si tenemos aplicado algún filtro vamos a eliminar todos los filtros y vamos a pulsar el botón de filtro del campo FECHA CURSO, el cual nos mostrará Como vemos se muestran los años del 2019 al 2021. Esto quiere decir que todas las fechas registradas en la tabla pertenecen a estos tres años. A la izquierda de cada año vemos el botón +, el cual muestra los meses registrados en dicho año, y a su vez a la izquierda de cada mes tenemos otro botón +, que muestra los días de ese mes del año indicado en el que se han registrado cursos. Estas opciones vienen muy bien para filtrar por períodos de tiempo. Por ejemplo, queremos mostrar los cursos cuya fecha corresponde al primer cuatrimestre del año 2020, tendríamos que configurarlo de la forma que se muestra a continuación. Pulsando el botón ACEPTAR nos mostraría solo fechas de los cuatro primeros meses del año 2020. Por otro lado, el botón de filtro de un campo fecha detecta dicha columna como fecha, mostrando de esta forma la opción de Filtros de fecha , donde
botón izquierdo del ratón y sin soltarlo lo arrastramos hasta colocarlo en la posición de la pantalla donde queremos colocarla.
cualquiera de sus puntos circulares de selección, al igual que haríamos con una imagen.
Una vez que tenemos colocadas las segmentaciones en la posición de la pantalla que más cómodo sea y con el tamaño adecuado, pulsamos el botón de un elemento de la segmentación y se emite el filtro sobre la tabla.
Para quitar el filtro de una segmentación pulsamos el botón Borrar filtro situado en la parte superior a la derecha del nombre de la segmentación
Lógicamente, para que esté activado dicho botón Borrar filtro , en la segmentación debemos tener aplicado un filtro.
De una misma segmentación podemos seleccionar más de un elemento para filtrar, seleccionando de manera normal el primero y a continuación seleccionando el resto de los elementos manteniendo pulsada la tecla CONTROL en el teclado.
Ten en cuenta: A la hora de realizar filtros en la tabla se pueden combinar filtros en unos campos a través de segmentaciones y filtros en otros a través de los botones de filtro. Generalmente, los campos numéricos o fecha no se suelen segmentar, ya que en las segmentaciones solo se puede filtrar por elemento y no por tipo de dato, y lo habitual en un campo numérico es filtrar realizando comparaciones a mayor, menor, etc. como vimos en el punto anterior.
Si hemos agregado alguna segmentación que posteriormente queremos quitar, colocando el ratón sobre la segmentación que vamos a quitar, pulsamos el botón derecho sobre ella y en el menú emergente seleccionamos la opción Quitar "nombre del campo segmentado". Por ejemplo, si queremos quitar la segmentación del campo CURSO, pulsamos el botón derecho sobre dicha segmentación y seleccionamos la opción Quitar "CURSO"
Y de forma automática esta desaparecerá de la pantalla.
En cualquier momento podemos volver a mostrarla o bien cualquier otro campo, realizando de nuevo la opción INSERTAR SEGMENTACIÓN DE DATOS explicada anteriormente. Dejamos la tabla sin segmentaciones, quitando también la segmentación del campo JORNADA CURSO
1.6 Ordenar Datos
A la hora de ordenar los datos de las columnas de la tabla, se puede realizar de manera similar a como hacemos en un rango. Podemos ordenar los datos, bien por una columna de la tabla o bien por más de una columna. ordenar datos de tabla por una sola columna: Sobre nuestro fichero BD CURSOS quitamos cualquier filtro que pueda estar aplicado, las segmentaciones que estén mostradas y, en caso de estar mostrada la fila de totales la ocultamos. Si queremos ordenar los cursos por el campo FECHA CURSO, por ejemplo, una opción es hacerlo a través del botón de filtro de la columna que vayamos a ordenar. En este caso, pulsamos el botón de filtro de la columna FECHA CURSO, y en las dos primeras opciones, indicamos si queremos un orden ascendente (de más antiguas a más recientes) o descendente (de más recientes a más antiguas). Como ejemplo, seleccionamos la primera: de más antiguo a más reciente. Y, si nos fijamos en los datos, vemos como las filas de la tabla se han ordenado por el campo FECHA CURSO de la más antigua a la más reciente tra opción para ordenar por una columna de la tabla, en caso de no tener los botones de filtro mostrados, es hacerlo a través de la ficha DATOS. Para ello, lo primero es colocar la celda activa en cualquier celda de la columna que vayamos a ordenar. Por ejemplo, si queremos ordenar por el campo DURACION, posicionamos la celda activa en cualquier celda de dicha columna DURACION y a continuación vamos a la ficha DATOS, grupo de botones ORDENAR Y FILTRAR y tenemos los botones o bien de orden ascendente (de menor a mayor) o bien el de orden descendente (de mayor a menor)
Además, en esta ventana: Si queremos quitar un nivel del orden, lo seleccionamos y pulsamos el botón ELIMINAR NIVEL. Si queremos duplicar un nivel de orden, lo seleccionamos y pulsamos el botón COPIAR NIVEL. Si queremos aumentar prioridad de orden de un nivel lo seleccionamos y pulsamos el botón SUBIR Si pretendemos quitar prioridad de un nivel en el orden, lo seleccionamos y pulsamos el botón BAJAR Una vez que tenemos todas las opciones configuradas, pulsamos el botón ACEPTAR y la tabla, en este caso, tiene que quedar ordenada por CURSO y a su vez cada curso ordenado por la DURACION de mayor (la más alta) a menor (hasta la más baja).
1.7 Convertir una Tabla en Rango
Al igual que podemos convertir un rango en una tabla, Excel también nos da la posibilidad de convertir la tabla en un rango. Para realizar dicha tarea, colocaremos la celda activa dentro de la tabla que vamos a convertir en rango e iremos a la ficha DISEÑO DE TABLA, a continuación, al grupo de botones HERRAMIENTAS y pulsaremos el botón CONVERTIR EN RANGO. Si pulsamos el botón SÍ, convertimos la tabla en un rango, de tal manera que lo que ocurría sería:
Si hubiese alguna segmentación, esta desaparecería
Los botones de filtro se ocultan
Desaparece la ficha DISEÑO DE TABLA
Pero el estilo de formato de tabla quedaría aplicado al rango Si en el cuadro de diálogo anteriormente mostrado, de convertir la tabla en rango, pulsamos el botón NO, lógicamente anulamos la orden de convertir la tabla en un rango.
1.8 Cambiar tamaño de Tabla
Si en un momento dado se han agregado filas o columnas a la tabla que por algún motivo la tabla no las ha admitido, lógicamente tenemos que tratar que la tabla las admita. Para ello, lo que haremos será cambiar el tamaño de la tabla, seleccionando el nuevo rango de celdas que incluyen las nuevas filas y/o columnas incorporadas. Para realizar dicha operación, colocaremos la celda activa dentro de la tabla e iremos a la fila DISEÑO DE TABLA y al grupo de botones PROPIEDADES y ahí pulsaremos el botón CAMBIAR TAMAÑO DE LA TABLA. El cual mostrará el siguiente cuadro de diálogo, con la dirección del rango que actualmente tiene la tabla. Lo que tendremos que hacer, será borrar el rango mostrado y seleccionar el nuevo rango de celdas que incluyen las nuevas filas y/o columnas incorporadas.
1.9 Nombrar una Tabla
Cada tabla que insertamos en un libro, Excel siempre le asigna un nombre, de tal manera que la primera tabla que insertemos será Tabla1, la segunda Tabla2 y así sucesivamente. En un libro podemos tener diferentes tablas. También en una misma hoja podemos tener más de una tabla, siempre y cuando separemos una tabla de otra por alguna fila y/o columna en blanco. Importante: El nombre de la tabla nos sirve para poder llamar a la tabla desde cualquier celda de cualquier hoja del libro en el que estamos trabajando, sin necesidad de cambiar de hoja y seleccionar los datos (como hacemos en los rangos). Basta con escribir el nombre de la tabla a la que queremos llamar y Excel de manera automática la identifica. Por ello, es muy importante dar a cada tabla el nombre adecuado, para que cuando queramos llamar a dicha tabla la reconozcamos por su nombre (como haremos más adelante). Para cambiar el nombre de la tabla tenemos que ir a la ficha DISEÑO DE TABLA, al grupo de botones PROPIEDADES y en el área NOMBRE DE LA TABLA, introducir el nombre con la cual queremos identificarla. En este caso, vemos que figura el nombre Tabla1, para cambiar el nombre lo
tenemos un rango de diferentes productos que compramos, con el precio de compra y el precio al que lo vendemos. Queremos calcular, inicialmente, el margen de beneficio que obtenemos por la venta de cada producto, es decir, que la operación que haremos de cada producto será: PRECIO VENTA
Las celdas que tienen referencia relativa, al copiar una fórmula o cálculo, se van a desplazar o mover tantas columnas y tantas filas como nos desplacemos nosotros desde la celda que copiamos (la que tiene la fórmula) hasta la celda en la que peguemos dicha fórmula.
Es decir, la referencia relativa la utilizamos en los casos donde llamamos a datos que vayan cambiando de celda, como es el ejemplo que estamos planteando
PRECIO VENTA El PRECIO VENTA de cada producto va cambiando para cada uno de ellos, es decir, el PRODUCTO A tiene un precio de venta, el PRODUCTO B otro precio de venta, y así sucesivamente para cada producto. Por tanto, la referencia que tenemos que hacer a C2 (la celda que representa el PRECIO VENTA es relativa, para que cuando copiemos la fórmula al resto de productos, vaya cambiando y seleccionando el precio venta del producto correspondiente.
PRECIO COMPRA El PRECIO COMPRA, al igual que el PRECIO VENTA, es diferente para cada producto, de tal manera que, como vamos a querer copiar la fórmula para el resto de los productos, tendremos que dejar a la celda B también una referencia relativa
Por tanto, dejamos tanto C2 como B2 con dicha referencia relativa y pulsamos la tecla INTRO o ENTER para validar la fórmula y que Excel nos muestre el resultado.
Al tener bien ajustada ya la referencia de ambas celdas en la fórmula escrita, podemos copiarla al resto de celdas de la columna para calcular automáticamente el margen de beneficio del resto de los productos.
Para copiar la fórmula podemos utilizar los comandos de copiar y pegar, o bien utilizar el selector de autorrelleno de la celda activa, que es el punto situado en la esquina inferior derecha de la celda activa Posicionando el ratón en dicho selector, hacemos dos clics rápidos al botón izquierdo del ratón y la fórmula se copia hasta el último producto. También, podemos colocarnos en dicho selector de autorrelleno y arrastrar con el botón izquierdo del ratón hasta llegar al margen de beneficio del último producto que vayamos a copiar.
2. Referencias Relativas y Absolutas II
La referencia absoluta consiste en blindar o bloquear una celda para que cuando vayamos a copiar la fórmula, dichas celdas que tienen referencia absoluta nunca cambien, es decir, permanezcan constantes. Se aplica en las fórmulas o cálculos a celdas que nunca cambian para ninguno de los datos. Para blindar una celda o hacer referencia absoluta, tenemos que bloquear su columna y bloquear la fila de la propia celda. Para ello, ponemos el símbolo del $ delante de la letra de la columna, y también ponemos el símbolo del $ delante del número de la fila. Es decir, que si queremos dejar blindada la celda B1 tendrá que quedar escrita como: $B$1. Un método abreviado para blindar o hacer referencia absoluta a una celda, sería teniendo el cursor pegado en la fórmula a la dirección de la celda que queremos blindar, pulsamos en el teclado la tecla de función F4 (en caso de tener portátil, puede que al pulsar la tecla F4 no se ponga el símbolo del $ en la celda, en ese caso tendremos que localizar la tecla FN y con ella pulsada apretamos la tecla F4.
2. Referencias Relativas y Absolutas III
Calcular columnas en una tabla: Los cálculos explicados en el punto anterior, los hemos realizado sobre un rango y en ellos se ha hecho referencia a cada celda que formaba parte del cálculo a través de su dirección. Sin embargo, cuando lo hacemos dentro de una tabla, la sintaxis cambia, de tal manera que, como a continuación vamos a ver, no se va a reflejar la
