excel - unapctm.cl · en sentido opuesto a las agujas de un reloj a 90º en sentido de las agujas...
TRANSCRIPT
EXCEL
EXCEL
Índice
Capítulo I: Formato de Celdas ..................................................................................... 1
1.1. Objetivo específico ............................................................................................... 1
1.2. Formato Número .................................................................................................. 1
1.3. Alineación ............................................................................................................. 3
1.4. Fuente .................................................................................................................. 7
1.5. Bordes ................................................................................................................ 10
1.6. Relleno ............................................................................................................... 12
Capítulo II Funciones Lógicas, de Búsqueda y Referencia ..................................... 14
2.1. Objetivo específico ............................................................................................. 14
2.2. Función SI .......................................................................................................... 14
2.3. Función Y ........................................................................................................... 15
2.4. Función O ........................................................................................................... 16
2.5. Función BUSCARV ............................................................................................ 16
2.6. Función BUSCARH ............................................................................................ 17
2.7. Función ÍNDICE ................................................................................................. 18
2.8. Función COINCIDIR ........................................................................................... 19
2.9. SUMAR.SI .......................................................................................................... 20
2.10. CONTAR.SI ...................................................................................................... 21
Capítulo III Herramientas de base de datos ......................................................... 22
3.1. Objetivo específico ............................................................................................. 22
3.2. Subtotales y Ordenamiento ................................................................................ 22
3.2.1. Subtotales .................................................................................................... 22
3.2.2. Ordenamiento .............................................................................................. 24
3.3. Filtros en Excel ................................................................................................... 27
3.3.1. Los Autofiltros .............................................................................................. 28
3.3.2. Filtros avanzados ......................................................................................... 29
3.4. Protección en Excel ............................................................................................ 33
3.4.1 Protección de celdas .................................................................................... 34
3.5. Formato condicional ........................................................................................... 37
EXCEL
3.6. Tablas Dinámicas ............................................................................................... 40
Introducción ............................................................................................................... 40
¿Qué es una Tabla Dinámica? .................................................................................. 40
3.6.1. Funcionamiento de las Tablas Dinámicas ................................................... 40
3.6.2. Cómo crear una Tabla Dinámica ................................................................. 43
3.6.3. Partes de una Tabla Dinámica ..................................................................... 47
3.6.4. Dar formato a una Tabla Dinámica .............................................................. 48
3.6.5. Formato de valores en una Tabla Dinámica ................................................ 50
3.6.6. Filtrar una Tabla Dinámica ........................................................................... 53
Ejemplo ..................................................................................................................... 54
3.6.7. Modificar campos de una Tabla Dinámica ................................................... 61
EXCEL
1
Capítulo I: Formato de Celdas
1.1. Objetivo específico
Aplicar formato a una planilla de datos para darle un aspecto más claro y profesional a la información.
1.2. Formato Número
Excel nos permite modificar la visualización de los números en la celda. Para ello, seguir los siguientes pasos:
Seleccionar el rango de celdas al cual queremos modificar el aspecto de los números.
Seleccionar la pestaña Inicio.
Hacer clic sobre la flecha que se encuentra bajo la sección Número.
Hacer clic en el vértice inferior derecho, como lo indica la flecha en la
imagen.
Aparecerá:
EXCEL
2
Elegir la opción deseada del recuadro Categoría:
Hacer clic sobre el botón Aceptar.
Al elegir cualquier opción, aparecerá en el recuadro Muestra un modelo
de cómo quedará nuestra selección en la celda.
A continuación pasamos a explicar las distintas opciones del recuadro Categoría.
Se elegirá de la lista una categoría dependiendo del valor introducido en la celda.
Las categorías más utilizadas son:
General: Visualiza en la celda exactamente el valor introducido. Es el formato que utiliza Excel por defecto. Este formato admite enteros, decimales, números en forma exponencial si la cifra no cabe por completo en la celda.
Número: Contiene una serie de opciones que permiten especificar el número de decimales, también permite especificar el separador de millares y la forma de visualizar los números negativos.
Moneda: Es parecido a la categoría Número, permite especificar el número de decimales, se puede escoger el símbolo monetario como podría ser € y la forma de visualizar los números negativos.
Contabilidad: Difiere del formato moneda en que alinea los símbolos de moneda y las comas decimales en una columna.
Fecha: Contiene números que representan fechas y horas como valores de fecha. Puede escogerse entre diferentes formatos de fecha.
Hora: Contiene números que representan valores de horas. Puede escogerse entre diferentes formatos de hora.
Porcentaje: Visualiza los números como porcentajes. Se multiplica el valor de la celda por 100 y se le asigna el símbolo %, por ejemplo, un formato de porcentaje sin decimales muestra 0,1528 como 15%, y con 2 decimales lo mostraría como 15,28%.
Fracción: Permite escoger entre nueve formatos de fracción.
EXCEL
3
Científica: Muestra el valor de la celda en formato de coma flotante. Podemos escoger el número de decimales.
Texto: Las celdas con formato de texto son tratadas como texto incluso si en el texto se encuentre algún número en la celda.
Especial: Contiene algunos formatos especiales, como puedan ser el código postal, el número de teléfono, etc.
Personalizada: Aquí podemos crear un nuevo formato, indicando el código
de formato.
En la Banda de opciones Formato disponemos de una serie de botones que nos permitirán modificar el formato de los números de forma más rápida:
Si se hace clic sobre el botón, los números de las celdas seleccionadas se convertirán a formato moneda (el símbolo dependerá de cómo tenemos definido el tipo moneda en la configuración regional de Windows, seguramente tendremos el símbolo €).
Para asignar el formato de porcentaje (multiplicará el número por 100 y le añadirá el símbolo %).
Para utilizar el formato de millares (con separador de miles y cambio de alineación).
Para quitar un decimal a los números introducidos en las celdas seleccionadas.
Para añadir un decimal a los números introducidos en las celdas seleccionadas.
1.3. Alineación
Se puede asignar formato a las entradas de las celdas a fin de que los datos queden alineados u orientados de una forma determinada.
Para cambiar la alineación de los datos de nuestra hoja de cálculo, seguir los siguientes pasos:
EXCEL
4
Seleccionar el rango de celdas al cual queremos modificar la alineación.
Haz clic en la flecha que se encuentra al pie de la sección Alineación.
Aparecerá:
Elegir las opciones deseadas.
Una vez elegidas todas las opciones deseadas, hacer clic sobre el botón Aceptar.
A continuación pasamos a explicarte las distintas opciones de la ficha.
EXCEL
5
Alineación del texto Horizontal
Alinea el contenido de las celdas seleccionadas horizontalmente, es decir respecto de la anchura de las celdas. Al hacer clic sobre la flecha de la derecha podrás elegir entre una de las siguientes opciones:
GENERAL: Es la opción de Excel 2007 por defecto, alinea las celdas seleccionadas dependiendo del tipo de dato introducido, es decir, los números a la derecha y los textos a la izquierda.
IZQUIERDA (Sangría): Alinea el contenido de las celdas seleccionadas a la izquierda de éstas independientemente del tipo de dato. Observa como a la derecha aparece un recuadro Sangría: que por defecto está a 0, pero cada vez que se incrementa este valor en uno, la entrada de la celda comienza un carácter más a la derecha, para que el contenido de la celda no esté pegado al borde izquierdo de la celda.
CENTRAR: Centra el contenido de las celdas seleccionadas dentro de éstas.
DERECHA (Sangría): Alinea el contenido de las celdas seleccionadas a la derecha de éstas, independientemente del tipo de dato. Observa como a la derecha aparece un recuadro de Sangría: que por defecto está a 0, pero cada vez que se incrementa este valor en uno, la entrada de la celda comienza un carácter más a la izquierda, para que el contenido de la celda no esté pegado al borde derecho de la celda.
LLENAR: Esta opción no es realmente una alineación sino que repite el dato de la celda para rellenar la anchura de la celda. Es decir, si en una celda tenemos escrito * y elegimos la opción Llenar, en la celda aparecerá ************ hasta completar la anchura de la celda.
JUSTIFICAR: Con esta opción el contenido de las celdas seleccionadas se alineará tanto por la derecha como por la izquierda.
CENTRAR EN LA SELECCIÓN: Centra el contenido de una celda respecto a todas las celdas en blanco seleccionadas a la derecha, o de la siguiente celda en la selección que contiene datos.
EXCEL
6
Alineación del texto Vertical
Alinea el contenido de las celdas seleccionadas verticalmente, es decir, respecto de la altura de las celdas. Esta opción sólo tendrá sentido si la altura de las filas se ha ampliado respecto al tamaño inicial. Al hacer clic sobre la flecha de la derecha podrás elegir entre una de las siguientes opciones:
SUPERIOR: Alinea el contenido de las celdas seleccionadas en la parte superior de éstas.
CENTRAR: Centra el contenido de las celdas seleccionadas respecto a la altura de las celdas.
INFERIOR: Alinea el contenido de las celdas seleccionadas en la parte inferior de éstas.
JUSTIFICAR: Alinea el contenido de las celdas seleccionadas tanto por la parte superior como por la inferior.
Orientación
Permite cambiar el ángulo del contenido de las celdas para que se muestre en horizontal (opción por defecto), de arriba a abajo o en cualquier ángulo desde 90º en sentido opuesto a las agujas de un reloj a 90º en sentido de las agujas de un reloj. Excel 2007 ajusta automáticamente la altura de la fila para adaptarla a la orientación vertical, a no ser que se fije explícitamente la altura de ésta.
Ajustar texto
Por defecto si introducimos un texto en una celda y éste no cabe, utiliza las celdas contiguas para visualizar el contenido introducido, pues si activamos esta opción el contenido de la celda se tendrá que visualizar exclusivamente en ésta, para ello incrementará la altura de la fila y el contenido se visualizará en varias filas dentro de la celda.
Reducir hasta ajustar
Si activamos esta opción, el tamaño de la fuente de la celda se reducirá hasta que su contenido pueda mostrarse en la celda.
Combinar celdas
Al activar esta opción, las celdas seleccionadas se unirán en una sola.
EXCEL
7
Dirección del texto Permite cambiar el orden de lectura del contenido de la celda. Se utiliza para lenguajes que tienen un orden de lectura diferente del nuestro por ejemplo árabe, hebrea, etc. En la Banda de opciones disponemos de unos botones que nos permitirán modificar algunas de las opciones vistas anteriormente de forma más rápida, como:
Al hacer clic sobre este botón la alineación horizontal de las celdas seleccionadas pasará a ser Izquierda.
Este botón nos centrará horizontalmente los datos de las celdas seleccionadas.
Este botón nos alineará a la derecha los datos de las celdas seleccionadas.
Este botón unirá todas las celdas seleccionadas para que formen una sola celda, y a continuación nos centrará los datos.
1.4. Fuente
Excel nos permite cambiar la apariencia de los datos de una hoja de cálculo cambiando la fuente, el tamaño, estilo y color de los datos de una celda.
Para cambiar la apariencia de los datos de nuestra hoja de cálculo, podemos utilizar los cuadros de diálogo o la banda de opciones, a continuación te describimos estas dos formas, en cualquiera de las dos primero deberás previamente seleccionar el rango de celdas al cual se quiere modificar el aspecto:
Utilizando los cuadros de diálogo:
En la pestaña Inicio haz clic en la flecha que se encuentra al pie de la sección Fuente.
EXCEL
8
Del cuadro de diálogo que se abre, Formato de celdas, haciendo clic sobre la pestaña Fuente, aparecerá:
Una vez elegidos todos los aspectos deseados, hacemos clic sobre el botón Aceptar.
Conforme vamos cambiando los valores de la ficha, aparece en el recuadro Vista previa un modelo de cómo quedará nuestra selección en la celda.
Esto es muy útil a la hora de elegir el formato que más se adapte a lo que queremos.
EXCEL
9
A continuación pasamos a explicarte las distintas opciones de la ficha Fuente.
Fuente: Se elegirá de la lista una fuente determinada, es decir, un tipo de
letra.
Si elegimos un tipo de letra con el identificativo delante de su nombre,
nos indica que la fuente elegida es True Type, es decir, que se usará la
misma fuente en la pantalla que la impresora, y que además es una fuente
escalable (podemos escribir un tamaño de fuente aunque no aparezca en
la lista de tamaños disponibles).
Estilo: Se elegirá de la lista un estilo de escritura. No todos los estilos son disponibles con cada tipo de fuente. Los estilos posibles son: Normal, Cursiva, Negrita, Negrita Cursiva.
Tamaño: Dependiendo del tipo de fuente elegido, se elegirá un tamaño u
otro. Se puede elegir de la lista o bien teclearlo directamente una vez situados
en el recuadro.
Subrayado: Observa como la opción activa es Ninguno, haciendo clic sobre la flecha de la derecha se abrirá una lista desplegable donde tendrás que elegir un tipo de subrayado.
Color: Por defecto el color activo es Automático, pero haciendo clic sobre la flecha de la derecha podrás elegir un color para la letra.
Efectos: Tenemos disponibles tres efectos distintos: Tachado, Superíndice y Subíndice. Para activar o desactivar uno de ellos, hacer clic sobre la casilla de verificación que se encuentra a la izquierda.
Fuente normal: Si esta opción se activa, se devuelven todas las opciones de
fuente que Excel 2007 tiene por defecto.
En la Banda de opciones disponemos de unos botones que nos permiten modificar algunas de las opciones vistas anteriormente y de forma más rápida, como:
EXCEL
10
En este recuadro aparece el tipo de fuente o letra de la celda en la que nos encontramos situados. Para cambiarlo, hacer clic sobre la flecha de la derecha para elegir otro tipo.
Al igual que el botón de Fuente anterior, aparece el tamaño de nuestra celda, para cambiarlo puedes elegir otro desde la flecha de la derecha, o bien escribirlo directamente en el recuadro.
Este botón lo utilizamos para poner o quitar la Negrita. Al hacer clic sobre éste se activa o desactiva la negrita dependiendo del estado inicial.
Este botón funciona igual que el de la Negrita, pero en este caso lo utilizamos para poner o quitar la Cursiva.
Este botón funciona como los dos anteriores pero para poner o quitar el Subrayado simple.
Con este botón podemos elegir un color para la fuente. Debajo de la letra A aparece una línea, en nuestro caso roja, que nos indica que si hacemos clic sobre el botón cambiaremos la letra a ese color. En caso de querer otro color, hacer clic sobre la flecha de la derecha y elegirlo.
1.5. Bordes
Excel nos permite crear líneas en los bordes o lados de las celdas.
Para cambiar la apariencia de los datos de nuestra hoja de cálculo añadiendo bordes, seguir los siguientes pasos:
Seleccionar el rango de celdas al cual queremos modificar el aspecto.
Seleccionar la pestaña Inicio.
Hacer clic sobre la flecha que se encuentra bajo la sección Fuente.
En el cuadro de diálogo que se abrirá hacer clic sobre la pestaña Bordes.
EXCEL
11
Elegir las opciones deseadas del recuadro.
Una vez elegidos todos los aspectos deseados, hacer clic sobre el botón
Aceptar.
Al elegir cualquier opción, aparecerá en el recuadro Borde un modelo de cómo
quedará nuestra selección en la celda.
A continuación pasamos a explicarte las distintas opciones del recuadro.
Preestablecidos
Se elegirá una de estas opciones:
Ninguno: Para quitar cualquier borde de las celdas seleccionadas.
Contorno: Para crear un borde únicamente alrededor de las celdas seleccionadas.
Interior: Para crear un borde alrededor de todas las celdas seleccionadas excepto alrededor de la selección.
EXCEL
12
Borde
Este recuadro se suele utilizar cuando no nos sirve ninguno de los botones preestablecidos. Dependiendo del borde a poner o quitar (superior, inferior, izquierdo,...) hacer clic sobre los botones correspondientes. ¡CUIDADO! Al utilizar los botones preestablecidos, el borde será del estilo y color seleccionados, en caso de elegir otro aspecto para el borde, primero habrá que elegir Estilo y Color y a continuación hacer clic sobre el borde a colocar.
Estilo
Se elegirá de la lista un estilo de línea.
Color
Por defecto el color activo es Automático, pero haciendo clic sobre la flecha de la derecha podrá elegir un color para los bordes.
En la Banda de opciones disponemos de un botón que nos permitirá modificar los bordes de forma más rápida:
Si se hace clic sobre el botón se dibujará un borde tal como viene representado en éste. En caso de querer otro tipo de borde, elegirlo desde la flecha derecha del botón. Aquí no encontrarás todas las opciones vistas desde el recuadro del menú.
1.6. Relleno
Excel nos permite también sombrear las celdas de una hoja de cálculo para remarcarlas de las demás. Para ello, seguir los siguientes pasos:
1. Seleccionar el rango de celdas al cual queremos modificar el aspecto. 2. Seleccionar la pestaña Inicio. 3. Hacer clic sobre la flecha que se encuentra bajo la sección Fuente. 4. Hacer clic sobre la pestaña Relleno.
Aparecerá:
EXCEL
13
Elegir las opciones deseadas del recuadro.
Una vez elegidos todos los aspectos deseados, hacer clic sobre el botón Aceptar.
Al elegir cualquier opción, aparecerá en el recuadro Muestra un modelo de cómo quedará nuestra selección en la celda.
Color de fondo: Se elegirá de la lista un color de fondo determinado.
Para quitar el efecto de sombreado, bastará con elegir Sin Color.
Color de trama: Se elegirá de la lista desplegable un estilo de trama, así
como el color de la trama.
En la Banda de opciones disponemos de un botón que nos permitirá modificar el relleno de forma más rápida:
Si se hace clic sobre el botón se sombreará la celda del color indicado en éste, en nuestro caso, en amarillo. En caso de querer otro color de sombreado, elegirlo desde la flecha derecha del botón. Aquí no podrás añadir trama a la celda.
EXCEL
14
Capítulo II Funciones Lógicas, de Búsqueda y Referencia
2.1. Objetivo específico
Aplicar funciones para realizar operaciones más complejas en la obtención de la información.
2.2. Función SI
La función SI devuelve un valor entre dos posibles valores, dependiendo de una condición indicada.
Sintaxis =SI(prueba lógica; [valor si verdadero]; [valor si falso])
prueba lógica: es una comparación entre dos celdas usando operadores lógicos.
Recordar que los operadores lógicos son: =(igual), <(menor), >(mayor), <>(distinto), >=(mayor o igual), <=(menor o igual).
[valor si verdadero]: es el valor, celda o texto especificado a devolver si prueba lógica es verdadera.
[valor si falso]: es el valor, celda o texto especificado a devolver si prueba lógica es falsa.
Los argumentos valor si verdadero y valor si falso son opcionales. Si se omiten la función SI devuelve directamente VERDADERO o FALSO.
Veamos el siguiente ejemplo para entenderla mejor. Ejemplo Un profesor está analizando las calificaciones de sus alumnos en el rango D17:E25. En el rango F18:F25 desea que según la calificación mínima necesaria de la celda H18, aparezca un mensaje indicando si el alumno aprobó o no. Dichos mensajes están en las celdas J18 y J21. A continuación se presenta la solución.
EXCEL
15
2.3. Función Y
La función Y evalúa una serie de condiciones. Si todas las condiciones son verdaderas devuelve VERDADERO. Si al menos una de ellas es falsa devuelve FALSO.
Sintaxis =Y(valor lógico1; [valor lógico2];...) valor lógico1; [valor lógico2];… : pueden ser hasta 30 condiciones que se desean probar en las cuales se obtenga como resultado un valor lógico tipo VERDADERO o FALSO.
Veamos el siguiente ejemplo para entenderla mejor: Ejemplo En el rango D18:E25 se tiene un listado del personal de una empresa, con su respectiva edad. El gerente desea identificar aquellas personas con edad mayor a la indicada en la celda H18 y menor o igual a la indicada en la celda K18. El resultado deber aparecer en el rango F18:F25. Veamos la solución:
EXCEL
16
2.4. Función O
La función O evalúa una serie de comparaciones. Si al menos una de ellas es verdadera, la función devuelve VERDADERO. Solo si todas ellas son falsas, devuelve FALSO.
Sintaxis =O(valor lógico1; [valor lógico2];...) valor lógico1; [valor lógico2];… : pueden ser hasta 30 condiciones que se desean probar en las cuales se obtenga como resultado un valor lógico tipo VERDADERO o FALSO. Veamos el siguiente ejemplo para entenderla mejor. Ejemplo En este ejemplo se tiene un listado del personal de una empresa, con su respectivo día de trabajo. El gerente desea identificar aquellas personas que trabajaron alguno de los días indicados en las celdas H18 y K18. El resultado deber aparecer en el rango F18:F25. Veamos la solución:
2.5. Función BUSCARV
La función BUSCARV devuelve un valor determinado de una columna indicada perteneciente a una tabla, según un índice.
Sintaxis =BUSCARV(valor buscado; matriz de comparación; indicador columnas; [ordenado])
EXCEL
17
valor buscado: es el valor buscado en la primera columna de la tabla.
matriz de comparación: es la tabla donde se efectúa la búsqueda.
indicador columnas: es un número que especifica la columna de la tabla de donde se devuelve un valor.
ordenado: es un valor lógico (VERDADERO o FALSO) que indica que la primera columna de la tabla donde se buscan los datos esta ordenada o no. Si omite este argumento se considera VERDADERO, es decir, se considera que la columna uno de la tabla esta ordenada. Si no se está seguro poner siempre FALSO. Veamos el siguiente ejemplo para entenderla mejor Ejemplo Un doctor analiza los meses de edad de bebés en el rango D17:E25. En el rango F18:F25 desea saber la cantidad de vacunas que debe tener cada bebé según la edad. En el rango H17:I21 está la tabla que indica cantidad de vacunas según edad. A continuación se presenta la solución.
2.6. Función BUSCARH
La función BUSCARH devuelve un valor determinado de una fila indicada perteneciente a una tabla, según un índice.
Sintaxis =BUSCARH(valor buscado; matriz buscar en; indicador filas; [ordenado])
EXCEL
18
valor buscado: es el valor buscado en la primera fila de la tabla.
matriz buscar en: es la tabla donde se efectúa la búsqueda.
indicador filas: es un número que especifica la fila de la tabla donde se buscará valor.
ordenado: es un valor lógico (VERDADERO o FALSO) que indica que la primera fila de la tabla donde se buscan los datos esta ordenada o no. Si se omite este argumento se considera que la fila uno de la tabla esta ordenada (VERDADERO). Veamos el siguiente ejemplo para entenderla mejor Ejemplo Un doctor analiza los meses de edad de bebés en el rango D17:E25. En el rango F18:F25 se desea saber la cantidad de vacunas que debe tener cada bebé según la edad. En el rango H17:L18 se encuentra la cantidad de vacunas para cada edad. A continuación se presenta la solución.
2.7. Función ÍNDICE
La función INDICE devuelve el valor referido a una fila y columna indicadas dentro de una tabla especificada.
Sintaxis =INDICE(matriz; núm fila; núm columna)
matriz: es la tabla donde se efectúa la búsqueda.
EXCEL
19
núm fila: es un número que especifica la fila de la tabla donde se buscará el valor. Si se omite debe estar presente núm columna.
núm columna: es un número que especifica la columna de la tabla donde se buscará el valor. Si se omite debe estar presente núm fila.
Veamos el siguiente ejemplo para entenderla mejor Ejemplo Se analiza una tabla de datos de personas en el rango D17:F25. En la celda H18 se desea saber cuál es el dato que corresponde a la fila y columna indicadas en la celda J18 y K18 respectivamente. A continuación se presenta la solución. Veamos la solución:
2.8. Función COINCIDIR
La función COINCIDIR devuelve el número de posición de un dato que coincida con un valor especificado dentro de una tabla.
Sintaxis =COINCIDIR(valor buscado; matriz buscada; tipo de coincidencia)
valor buscado: es el valor buscado dentro del rango de matriz buscada.
matriz buscada: es un rango donde efectúa la búsqueda.
tipo de coincidencia: si es 0 se busca un valor que sea igual al dato indicado. Si es 1 se busca el mayor valor en un rango que sea menor o igual al dato buscado (el rango debe estar ordenado en forma creciente). Si es -1 busca el valor mayor o igual al dato considerando el rango ordenado decrecientemente.
EXCEL
20
Veamos el siguiente ejemplo para entenderla mejor Ejemplo En el rango D17:E25 se analiza la tabla de posiciones de un torneo de fútbol. En la celda G18 se desea saber qué posición ocupa en esa tabla el equipo indicado en la celda I18. A continuación se presenta la solución.
2.9. SUMAR.SI
La función SUMAR.SI realiza la suma de un rango de acuerdo a un criterio determinado.
Sintaxis
=SUMAR.SI(rango; criterio; [rango suma])
rango: es el rango que será evaluado.
criterio: es la condición que identificará las celdas en el rango evaluado.
[rango suma]: es el rango que contiene los valores a sumar de acuerdo a lo especificado en el rango de criterio. Es opcional. Veamos el siguiente ejemplo para entenderla mejor.
Ejemplo En el rango D18:D25 se tiene un listado de comisionistas. En la celda H21 se desea calcular la cantidad de kilómetros que realizó el comisionista indicado en la celda H18. A continuación se presenta la solución.
EXCEL
21
2.10. CONTAR.SI
La función CONTAR.SI cuenta valores en un rango, de acuerdo a un criterio determinado.
Sintaxis = CONTAR.SI(rango; criterio)
rango: es el rango que será evaluado.
criterio: es la condición que identificará las celdas a contar en el rango evaluado. Veamos el siguiente ejemplo para entenderla mejor. Ejemplo Un gerente examina ingresos, salidas y faltantes que se van produciendo en un inventario día a día, en el rango D18:E25. En la celda H18 desea saber la cantidad de ingresos, salida o faltante, según lo indicado en la celda K18. A continuación se presenta la solución.
EXCEL
22
Capítulo III Herramientas de base de datos
3.1. Objetivo específico
Utilizar las diferentes herramientas de bases de datos para filtrar, ordenar y analizar los datos en una hoja de trabajo.
3.2. Subtotales y Ordenamiento
3.2.1. Subtotales
La función de subtotales en Excel, nos permite tener de manera casi inmediata filas con los totales de la información que tenemos en nuestra hoja de cálculo. Por ejemplo, suponiendo que tenemos una hoja con los gastos personal que hemos realizado podemos tener el subtotal de lo gastado por cada área de nuestro gasto. Para utilizar la función de subtotales en Excel, lo primero que requerimos es tener una hoja con datos ordenados por columnas, que tenga los datos por columnas, tal como este:
Una vez que hemos elegido nuestro archivo de datos, vamos a definir el resultado que queremos tener. Si estás utilizando el archivo de ejemplo, verás
EXCEL
23
que tiene una lista de vendedores y productos vendidos por región. Lo que queremos tener es la suma del monto vendido en cada región.
Lo primero que vamos a hacer es ordenar nuestra lista por el campo que deseamos cada corte de subtotal, es decir por la columna región.
Ya que esta ordenado este campo, lo que hacemos es seleccionar todas las celdas dónde tenga datos. Un método abreviado para hacer esto es presionar las teclas Ctrl+Inicio con lo que nos colocaremos en la primera celda de la hoja y luego presionamos las teclas Shift+Ctrl+Inicio.
Al estar seleccionados los datos, vamos la pestaña Datos y presionamos el botón Subtotal (En versiones de Excel anterior a 2007 vamos al menú Datos y seleccionamos la opción Subtotal), aparecerá un cuadro del diálogo como el de la derecha:
En este cuadro de diálogo en el cuadro de opción "Para cada cambio en:" vamos a seleccionar Región,
En el cuadro de opción "Usar función: " seleccionamos Suma
En la lista de "Agregar subtotal a:" marcamos únicamente la opción Total
Revisamos que estén seleccionadas las opciones Reemplazar subtotales actuales y Resumen debajo de los datos, y hacemos clic en Aceptar
Veremos que en nuestra hoja de cálculo aparecen 3 cuadritos numerados del 1 al 3 del lado derecho de nuestra hoja, esto es porque ha agrupado las información de nuestro archivo en 3 niveles, una (la número 1) que incluye todos los datos de nuestra hoja, la número2 es por región y por último, la número 3 que nos muestra todos los datos que tenemos.
Al hacer clic en el cuadrito marcado con el número 2 podemos ver que se ocultan las líneas con datos y nos quedan solo las de subtotales, es decir, la venta por región, de igual manera podemos hacer clic en el número 1 y nos mostrará solo el total general de ventas.
EXCEL
24
3.2.2. Ordenamiento
Excel tiene una gran capacidad de realizar cálculos. Sin embargo, también incluye funciones de bases de datos que permiten ordenar los datos por texto, números, fechas y horas en una o más columnas, usando uno o varios criterios. Planilla de ejemplo
Ordenar datos utilizando un solo criterio
1. Selecciona el rango D2:D26, incluyendo el encabezado de columna
(Horas)
EXCEL
25
2. En la ficha Datos, haz clic en Ordenar de la A a Z. . Antes de realizar el
ordenamiento, aparece un mensaje de advertencia que te pedirá ampliar la selección de datos. Debes tener presente que con sólo una columna seleccionada, los datos no se ordenarán de manera apropiada. Da clic en el botón Ordenar..., pero con la marca de verificación en "Ampliar la
selección". Los datos se ordenan por el número de horas. 3. Selecciona cualquier celda de la columna A y haz clic en Ordenar de A a
Z. Los datos se ordenaran por Apellido. 4. Ahora deberás ordenar la lista según el Cargo, por lo tanto selecciona el
rango A2:E26. Haz clic en Ordenar para abrir la caja de diálogo Ordenar. 5. En la caja de diálogo Ordenar y en el cuadro Ordenar por, selecciona
Cargo y luego en Criterio de ordenación selecciona Z a A y para terminar clic en el botón Aceptar.
6. Observa el resultado. 7. Haz clic en Ordenar. Automáticamente se selecciona el rango de datos y
se abre la caja de diálogo Ordenar. Selecciona Horas en el cuadro Ordenar por. En la lista desplegable Criterio de ordenación, selecciona De mayor a menor. Haz clic en el botón Aceptar.
Ordenar datos utilizando múltiples criterios
1) Selecciona el rango A2:E26, puedes seleccionar el rango digitándolo en el Cuadro de nombres.
2) Haz clic en la pestaña Datos y luego en Ordenar para abrir el cuadro de diálogo.
3) Selecciona Cargo en Ordenar por y como Criterio de ordenación de la A a Z.
4) Haz clic en Agregar nivel para indicar el segundo criterio de ordenación.
5) Selecciona Apellido como segundo criterio. El criterio de ordenación debe quedar la predeterminada.
EXCEL
26
6) Haz clic en Aceptar.
Ordenar datos utilizando Formato condicional
1) Te ubicas en la ficha Inicio, haz clic en Buscar y seleccionar y haz clic en Formato condicional para ver si tus datos tienen formato condicional. En este caso aparece un mensaje para informar que ninguna celda de la hoja de cálculo tiene un formato condicional. Haz clic en Aceptar para cerrar la caja o cuadro de diálogo.
2) Selecciona el rango D3:D26. Haz clic en Formato Condicional y luego haz clic en Conjunto de iconos y por último en 3 flechas (de color). Cada valor en la columna tiene ahora una fecha que indica si el valor está dentro del rango alto, medio o bajo.
3) Selecciona el rango A2:E26. En la ficha inicio, haz clic en Ordenar y filtrar del grupo Modificar y luego haz clic en Orden personalizado...
4) Selecciona Horas en el cuadro Ordenar por. Selecciona Icono de celda debajo de Ordenar según. Acepta la flecha verde debajo de Criterio de ordenación.
EXCEL
27
5) Haz clic en el botón Agregar nivel.
6) Selecciona Horas, en el cuadro Luego por. Selecciona Icono de celda
debajo de Ordenar según, selecciona la flecha amarilla y en la parte superior En el campo Criterio de ordenación. Haz clic en Aceptar. Los datos se ordenan con el conjunto de icono.
3.3. Filtros en Excel
Puedes tener libros con hojas de cálculo que pueden contener datos, pero no necesariamente vas a trabajar con todos al mismo tiempo. Con Excel puedes aislar temporalmente datos específicos en una hoja de cálculo colocando una restricción, llamada filtro. Filtrar datos te permite visualizar datos relacionados con un análisis en particular, mostrando sólo las filas que cumplan con criterios específicos y ocultando las filas que no desees ver.
EXCEL
28
3.3.1. Los Autofiltros
Planilla de datos para nuestro ejemplo:
1) Selecciona el rango A3:E28. Haz clic en Filtro de la ficha Datos en el grupo Ordenar y filtrar. Observa que se agrega una flecha hacia abajo en cada encabezado de columna.
EXCEL
29
2) Haz clic en la flecha de la columna Cargo. Observa que se abre el menú Autofiltro. 3) Haz clic en Seleccionar todo para quitar la selección a todos los títulos. 4) Haz clic en Encargado de Cuentas por Cobrar y Recepcionista. 5) Haz clic en Aceptar y verás que se muestran los datos de seis empleados. Todos los demás son filtrados.
3.3.2. Filtros avanzados
Vamos a explicar el funcionamiento de los Filtros avanzados utilizando la siguiente tabla de datos:
En los filtros avanzados se utilizan criterios lógicos para filtrar las filas, en este caso, se debe especificar el rango de celdas donde se ubican los mismos, veamos cómo se procede.
En la cinta de opciones debemos ir a la pestaña "Datos" y luego al panel "Ordenar y filtrar" donde oprimimos el botón "Avanzadas" luego aparece el panel "Filtro avanzado".
EXCEL
30
Veamos que significan cada uno de las acciones que se pueden tomar:
Filtrar la lista sin moverla a otro lugar: se filtran los datos en el mismo lugar donde se encuentra la tabla.
Copiar a otro lugar: la tabla filtrada puede aparecer en un lugar especificado de la misma Hoja o en otra Hoja de cálculo.
Rango de la lista : automáticamente Excel coloca el rango done esta la lista
Rango de criterios: es el rango elegido por el usuario para ubicar los criterios de filtrado.
Copiar a: esta opción queda habilitada cuando se marca la casilla del punto 2, en cuyo caso deberemos especificar el lugar sonde queremos que aparezca la tabla filtrada, para esto solo es necesario especificar donde estarán los rótulos.
Sólo registros únicos: en el caso de haber registros duplicados, mostrar solo uno de ellos.
Ejemplo:
Filtrar todas las filas que no tengan un porcentaje de 70% de asistencia.
Para hacer esto marcamos en la casilla de verificación de Filtrar la lista sin moverla a otro lugar y luego elegimos 2 celdas, una para el rótulo y otra para el criterio a cumplir, como se muestra en el recuadro rojo(%D%1:%D%2).
EXCEL
31
El panel queda como se muestra
Al aceptar nos queda la tabla filtrada
En este caso las filas se han ocultado como en el caso de autofiltros, para solucionar el problema debemos copiar la tabla filtrada a otro lugar, por ejemplo al rango $E$1:$F$1, quedando el panel emergente como se ve al lado derecho
EXCEL
32
y la tabla en su nuevo lugar se ve como en la figura
Aquí se puede ver que las filas están en forma correlativa y por lo tanto se pueden aplicar funciones, por ejemplo si quisiéremos contar el número de alumnos con un porcentaje de asistencia del 70%, utilizaríamos la función CONTAR y como se puede ver se obtiene el resultado correcto que es 8. Vamos a dar otro ejemplo con dos condiciones. Supongamos que queremos saber cuántos alumnos tuvieron menos de 70% y más de 85% de asistencias, en este caso debemos poner 2 condiciones, por lo que necesitaremos una celda más en el rango de criterios.
y el panel Filtro avanzado se configura como sigue:
EXCEL
33
al aceptar obtenemos la tabla:
donde al aplicar la función CONTAR vemos que los alumnos que cumplen los criterios son 6.
3.4. Protección en Excel
Para impedir que un usuario cambie, mueva o elimine por accidente o
premeditadamente datos importantes, puede proteger determinados elementos de la
hoja de cálculo o libro, con o sin una contraseña.
IMPORTANTE: La protección de elementos del libro u hoja no debe confundirse con la
seguridad mediante contraseña de nivel de libro. La protección de elementos no protege
un libro frente a usuarios malintencionados.
EXCEL
34
3.4.1 Protección de celdas
Vamos a estudiar la protección de celdas disponible en Excel para no permitir
la modificación de celdas por error o por no tener permiso para ello.
Además de la protección mediante contraseñas para los libros de trabajo,
Excel ofrece varias órdenes para proteger las celdas del libro. Para ello
tenemos que realizar dos operaciones: la primera que consiste en proteger las
celdas que no queremos que sufran variaciones, y la segunda que consiste en
proteger la hoja.
Cuando una celda está bloqueada no podrá sufrir variaciones. Realmente por
defecto todas las celdas están protegidas o bloqueadas para que no sufran
cambios, pero no nos damos cuenta ya que la hoja no está protegida, para que
realmente se bloqueen las celdas antes hay que proteger la hoja de cálculo.
Desbloquear celdas
1) Seleccionar la pestaña Inicio.
2) Hacer clic sobre la flecha que se encuentra bajo la sección Fuente.
3) Hacer clic sobre la pestaña Proteger.
4) Aparecerá la ficha de la derecha:
5) Desactivar la casilla Bloqueada y Hacer clic sobre el botón Aceptar.
Si se activa la casilla Oculta, lo que se pretende es que la fórmula o el valor de la celda no se pueda visualizar en la barra de fórmulas.
EXCEL
35
Las operaciones de la ficha Proteger no tienen efecto si no protegemos la hoja de cálculo, por lo tanto a continuación tendremos que realizar los siguientes pasos:
1) Seleccionar la pestaña Revisar.
2) Hacer clic sobre el botón Proteger hoja que se encuentra en la sección Cambios.
3) Aparecerá el cuadro de diálogo Proteger hoja:
EXCEL
36
4) Dejar activada la casilla Proteger hoja y contenido de celdas bloqueadas para proteger el contenido de las celdas de la hoja activa.
5) Activar las opciones deseadas de la casilla Permitir a los usuarios de esta hoja de cálculo para que no tenga efecto la protección para la modificación seleccionada y desactivarla para tener en cuenta la protección.
6) Si queremos asignar una contraseña para que solamente pueda desproteger la hoja la persona que sepa la contraseña, escribirla en el recuadro Contraseña.
7) Hacer clic sobre el botón Aceptar.
Si hemos puesto contraseña nos pedirá confirmación de contraseña, por lo tanto tendremos que volver a escribirla y hacer clic sobre el botón Aceptar.
A partir de ahora la hoja activa se encuentra protegida, por lo que no se podrán modificar aquellas celdas bloqueadas en un principio.
Si queremos desproteger la hoja, volveremos a realizar los mismos pasos que en la protección, es decir:
1) Seleccionar la pestaña Revisar.
2) Hacer clic sobre el botón Desproteger hoja que se encuentra en la sección Cambios.
EXCEL
37
Si habíamos asignado una contraseña nos la pedirá, por lo que tendremos que escribirla y hacer clic sobre el botón Aceptar. Si no había contraseña asignada, automáticamente la desprotege.
3.5. Formato condicional
El Formato Condicional es una herramienta muy útil como información gráfica adicional para los datos numéricos que están en celdas o en rangos, con más razón si hablamos de Excel 2007, que ha mejorado enormemente las posibilidades en este aspecto. Este tipo de formato tiene un comportamiento similar al de la función SI ya que otorga un formato a una celda o rango, dependiendo del cumplimiento de una condición, que es en definitiva, el cumplimiento de una proposición lógica.
Los formatos condicionales más novedosos son:
Barra de datos: Es semejante a un gráfico de barras proporcional al
número en la celdas.
Escala de color: Da un color a una celda, de acuerdo al valor de esta.
Conjunto de íconos: Permite según el valor de una celda compararla con
otro de referencia y añadirle un ícono.
Aunque hay muchas más opciones; entre ellas se pueden marcar celdas que cumplan con determinadas condiciones y dar colores a los números.
Se puede también aplicar más de un formato a una celda, o sea, si una condición cambia el tamaño de la fuente de una celda y otra cambia el color del fondo, cuando se cumplan ambas condiciones , también se aplicaran ambos formatos.
La tabla1 que se ve a continuación contiene simultáneamente los formatos Barra de datos, Escala de colores y Conjunto de íconos.
Tabla 1
Para aplicar estos formatos el método es muy parecido.
EXCEL
38
En la cinta de opciones vamos a la pestaña Inicio y luego al panel Estilos.
al pulsar en este aparece otro panel también llamado Estilos
donde pulsamos Formato condicional que cambia de color y despliega un menú de opciones, donde están destacados en rojo los formatos mencionados al principio mas otros dos en la parte superior.
Los triángulos negros nos indican que hay más opciones de formato como se puede ver al oprimir, por ejemplo en Barra de datos.
EXCEL
39
Luego dada la siguiente tabla igual a la tabla1 pero sin formato
para aplicar el formato Barra de datos, a la etiqueta venta, se selecciona la columna donde están los datos y se pulsa en el modelo señalado por la flecha roja.
quedando como la columna VENTAS de la tabla1
EXCEL
40
3.6. Tablas Dinámicas
Introducción
Las Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una lista, es decir, una forma de ver los datos desde puntos de vista diferentes.
El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista pueden cambiar de posición y también pueden ser filtrados.
Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la confección de gráficos.
La comprensión cabal de este tema se obtiene con la práctica y es así como se verá que es uno de los tópicos más potentes de Excel, principalmente en las versiones más recientes.
¿Qué es una Tabla Dinámica?
Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel.
Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.
3.6.1. Funcionamiento de las Tablas Dinámicas
Las tablas dinámicas permiten agrupar datos en una gran cantidad de maneras diferentes para poder obtener la información que necesitamos. En esta ocasión explicaré el funcionamiento básico de una tabla dinámica.
Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.
EXCEL
41
Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:
Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:
EXCEL
42
Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.
Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la tabla dinámica:
Así tendrás un reporte que compara las ciudades y los productos y para cada combinación obtendrás el total de ventas.
EXCEL
43
Lo más importante que quiero transmitir con este artículo es que las tablas dinámicas te permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.
3.6.2. Cómo crear una Tabla Dinámica
Las tablas dinámicas reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.
Usaremos como ejemplo la tabla que se encuentra más abajo.
Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.
EXCEL
44
Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.
Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.
EXCEL
45
En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.
Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo
EXCEL
46
una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.
Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Moveremos al área de columnas el campo Producto y como filas al campo Ciudad. Finalmente como valores colocaré el campo Ventas.
De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:
Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas.
EXCEL
47
3.6.3. Partes de una Tabla Dinámica
Ahora analizaremos con un poco más de detalle cada una de las partes que las conforman.
Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica.
Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que vez en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas.
Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica.
Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica.
Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.
Una vez especificados los campos para cada una de las áreas la tabla dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros son una herramienta para mejorar el análisis sobre los datos de la tabla dinámica.
EXCEL
48
3.6.4. Dar formato a una Tabla Dinámica
Una vez que has creado una tabla dinámica, Excel permite aplicarle formato fácilmente como si fuera una tabla de datos. La ficha Diseño incluye comandos especiales para aplicar formato a una tabla dinámica.
La dicha Diseño es una ficha contextual, por lo que deberás seleccionar la tabla dinámica para que se muestre.
Esta ficha está dividida en tres grupos.
Diseño. Este grupo permite agregar subtotales y totales generales a la tabla dinámica así como modificar aspectos básicos de diseño.
Opciones de estilo de tabla dinámica. Las opciones de este grupo permiten restringir la selección de estilos que se muestran en el grupo que se encuentra justo a su derecha. Es decir, si seleccionas la opción “Filas con bandas”, entonces se mostrarán a la derecha los estilos que tienen filas con bandas.
EXCEL
49
Estilos de tabla dinámica. Muestra la galería de estilos que se pueden aplicar a la tabla dinámica. Con tan sólo hacer clic sobre el estilo deseado se aplicará sobre la tabla.
Puedes hacer clic en el botón Más del grupo Estilos de tabla dinámica para ver todos los estilos disponibles. Con tan sólo elegir alguno de los estilos se aplicará inmediatamente a la tabla dinámica.
EXCEL
50
3.6.5. Formato de valores en una Tabla Dinámica
En esta ocasión te mostraré cómo dar formato rápidamente a los valores agrupados de una tabla dinámica de manera de puedan tener el formato de número adecuado. Solamente sigue los siguientes pasos:
Supongamos la siguiente tabla dinámica:
EXCEL
51
Para dar formato a los valores numéricos debes hacer clic sobre el campo correspondiente dentro del área Valores.
Del menú mostrado debes seleccionar la opción Configuración de campo de valor.
Se mostrará el cuadro de diálogo Configuración de campo de valor.
EXCEL
52
Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:
Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:
EXCEL
53
Después de hacer la selección adecuada acepta los cambios y de inmediato se aplicará el nuevo formato a todos los valores de la tabla dinámica:
3.6.6. Filtrar una Tabla Dinámica
Puedes filtrar y ordenar la información que se encuentra dentro de una tabla dinámica utilizando los filtros que Excel coloca de manera predeterminada en el reporte como Etiquetas de columna y Etiquetas de fila.
Al seleccionar cualquier de las opciones del filtro, la información será resumida y solamente mostrará un subconjunto de los datos de la tabla dinámica.
Una vez que se ha aplicado un filtro Excel reemplaza el icono predeterminado para indicar que ese campo está siendo actualmente filtrado.
EXCEL
54
Para mostrar de nuevo todos los valores de los campos filtrados debes hacer clic en el botón de filtrado y seleccionar la opción Borrar filtro de.
De igual manera puedes ordenar instantáneamente los valores de la tabla dinámica. Solamente haz clic sobre el botón de Etiquetas de fila o Etiquetas de columna y elige la opción Ordenar de A a Z o la opción Ordenar de Z a A.
Ejemplo
Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja de Excel 2007.donde figuran los datos del 1º trimestre del año.
EXCEL
55
A partir de ella se quiere crear una nueva tabla en la que se informe la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una.
Para crear la tabla que nos responda a estas preguntas, nos ubicamos en cualquier celda de la tabla, luego vamos a la pestaña Insertar panel Tablas.
En el que pulsamos en Tabla dinámica, al hacer esto aparece un menú en el que tenemos las opciones de Gráfico dinámico y Tabla dinámica, pulsaremos en este último como se puede en la figura del lado derecho. A continuación aparece el panel Crear tabla dinámica
EXCEL
56
En este caso el rango de la tabla base (tabla de partida) queda automáticamente seleccionado, incluyendo los rótulos, también seleccionamos "Nueva hoja de cálculo" y pulsamos en aceptar y automáticamente se crea la Hoja4 en la que se destacan: 1.. Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"
Un panel llamado "Lista de campos de tabla dinámica" que es una novedad de Excel 2007 y que tiene un rectángulo en la parte superior, donde se ubican los campos o rótulos de la tabla de origen, también hay cuatro rectángulos, en la parte inferior, denominados " Filtro de informe", "Rótulos de columna", "Rótulos de fila" y "Valores" donde irán apareciendo los rótulos de la tabla a medida que los seleccionemos en la parte suprior en forma de botones como el que se muestra. Los botones se pueden arrastrar de un rectángulo a otro aunque los rótulos que tienen valores numéricos, siempre aparecen en rectángulo "Valores". Como se ve, hasta este momento, tiene las casillas de verificación de rótulos sin marcar, pues bien, es justamente seleccionar las casillas "MAQUINA" y "CANTIDAD" lo debemos hacer en el próximo paso.
EXCEL
57
Observar que aparecen automáticamente 2 botones.
Listo ya tenemos la primera tabla con las respuestas pedidas recuadradas en rojo.
Si nos interesara saber solamente el dato de cuantas fertilizadora y sembradora se exportaron, junto con su total, tenemos un comando de filtrado en la parte superior y así obtenemos la siguiente tabla.
Luego de aplicar el filtro.
Este es un ejemplo didáctico para hacer una introducción, pero se le puede sacar mucho más el jugo a esta herramienta.
EXCEL
58
Podemos querer saber el detalle de las máquinas que fueron exportadas y por cual vendedor. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en la nueva Hoja aparece una tabla y el panel "Lista de campos de tabla dinámica"
Se ve que en los rectángulos, más precisamente en el llamado Rótulo de fila, aparece un nuevo botón, el botón VENDEDOR en forma simultánea a la selección de la casilla de verificación VENDEDOR.
La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto arrastrando el botón VENDEDOR al rectángulo "Rótulo de columna"
EXCEL
59
Y la tabla queda como la que está abajo, luego de haberle dado algo de formato
En esta tabla se puede ver, por ejemplo, que Peña vendió 16 fertilizadoras y un tractor.
Sería interesante saber el número de maquinarias exportadas a que país y por cual vendedor.
Para hacerlo, verificamos la casilla "País" y filtramos MAQUINA
y se genera la tabla
Donde se puede ver que Chuan le vendió 30 máquinas a China.
Hasta ahora nuestra tabla dinámica efectúa sumas, pero puede hacer otras operaciones tales como porcentajes, máximos, mínimo y otras más que iremos viendo.
EXCEL
60
Podemos preguntarnos cuál fue la máxima cantidad de maquinarias que vendió Peña. Para hacer esto nos ubicamos en una celda cualquiera de la tabla de arriba y apretando el botón derecho del mouse aparece el siguiente menú emergente.
En el que pulsamos en "Configuración de campo de valor", como indica la flecha, con lo que aparece el panel también llamado "Configuración de campo de valor"
En el que si vamos a la pestaña "Resumen por" están las opciones de resumen en las que elegiremos Máx y luego de aceptar, la tabla se transforma en:
EXCEL
61
Que nos dice que la cantidad Máxima de maquinarias que vendió Peña es 9, como se ve en el recuadro rojo, en forma adicional podemos ver que esta cantidad fue vendida a Brasil (verificar con la tabla de partida o tabla base).
Este resultado se puede ver con una simple inspección de los datos, que en este caso son tres, pero cuando estos aumentan es donde vemos la utilidad del cálculo de un máximo.
3.6.7. Modificar campos de una Tabla Dinámica
Las tablas dinámicas son muy fáciles de manipular y de modificar. Excel permite cambiar los campos de la tabla de una manera muy sencilla y reestructurar la información con tan solo arrastrar los nuevos campos.
Las modificaciones las haremos en la Lista de campos de la tabla dinámica que se encuentra en la parte derecha de la pantalla. Esta lista se mostrará con tan solo hacer clic en cualquier celda de la tabla dinámica.
EXCEL
62
Si por alguna razón esta lista de campos no aparece en tu libro de Excel, debes activarla con el comando Lista de campo que se encuentra dentro del grupo Mostrar de la ficha Opciones.
Para remover un campo arrástralo fuera del área donde se encuentra y cuando el puntero del ratón se convierta en una “X” suelta el botón del ratón para eliminar el campo. Otra manera de eliminar el campo es haciendo clic sobre él y seleccionar la opción Quitar campo.
EXCEL
63
Para mover un campo ya existente hacia una nueva ubicación solamente arrástralo de un área a otra o de igual manera selecciona cualquiera de las opciones de menú que permiten mover el campo a cualquiera de las áreas disponibles: Mover al filtro de informe, Mover a rótulos de fila, Mover a rótulos de columna o Mover a valores.
Con este método puedes cambiar fácilmente las columnas por filas y viceversa.
Finalmente para agregar un nuevo campo puedes arrastrarlo desde la lista de campos hacia el área deseada. También puedes marcar la caja de selección del campo lo cual hará que sea agregado a alguna de las áreas predeterminadas.
Las tablas dinámicas son un elemento de análisis de información de Excel muy poderosa y esa fortaleza proviene de la facilidad que provee para manipular la información de acuerdo a tus necesidades.