Manua Excel Intermedio

Centro de Desarroll sarrollo o Inform Informáti ático co Arturo Rosenblueth

Curso d e Excel INTERMEDIO

C entr entro o d e Des Desa a rrollo Informátic Informático o “A “Arrtur turo o Rosenblueth”



Centro de Desarroll sarrollo o Inform Informáti ático co Arturo Rosenblueth

OBJETIVO

Al finalizar el curso los participantes contarán con los conocimientos necesarios que les permitan automatizar las tareas más comúnes de una hoja de cálculo, al utilizar vínculos, funciones, tablas dinámicas, consolidación de datos y macros.

Centro de Desarrollo Informático Arturo Rosenblueth


Existen dos métodos para introducir una referencia a otra hoja del mismo libro: I. Escribir la fórmula directamente en la celda, y Hay ocasiones en que se requiere tener el valor que contiene una celda o rango de celdas ubicadas en otra hoja del mismo libro de trabajo, estas referencias pueden utilizarse para definir nombres y crear fórmulas utilizando las siguientes funciones. SUMA, PROMEDIO, PROMEDIO, CONTAR, CONTARA, MÁX, MÁXA, MÍN, MÍNA, PRODUCTO, DEVEST, DEVESTA, DEVESTP, DEVESTPA, VAR, VARA, VARP, y VARPA. A continuación se muestra la sintaxis requerida para la creación de una referencia en otra hoja dentro del mismo libro de trabajo.

II. mediante el ratón, de la siguiente manera 1. Ubicar la celda activa en donde se desea que aparezca la referencia. 2. Escribir un signo de igual u operador en una fórmula. 3. Dar un clic en la etiqueta de la hoja en donde se encuentra el dato que se desea. 4. Seleccionar la celda o rango de celdas que se requieren introducir. Oprimir la tecla de ENTER.

El nombre de la hoja de cálculo es siempre una referencia

La referencia de la celda puede ser relativa, absoluta o

Con este procedimiento Excel pone de forma automática la sintaxis correcta.

También se pueden utilizar referencias que abarquen dos o más hojas de un libro de trabajo, estas referencias se llaman 3Del siguiente ejemplo muestra una fórmula que utiliza un rango de celdas, en un rango de hojas. Un signo de exclamación separa la la hoja de la referencia de celda

En la celda que se escribe esta referencia, se presenta el valor que contiene la celda A1 de la Hoja2.

Un rango de hojas se especifica con dos puntos colocados entre los nombres de la primera y la última ho ja del rango.

Esta fórmula suma los valores contenidos en el rango $A$2:$C$5 de la Hoja2 a la Hoja6, incluyéndolas.


Para introducir la referencia de un rango de hojas de cálculo en una fórmula, se puede escribir o seleccionar las hojas y rango de celdas con el ratón. Ejemplo: 1. Seleccionar la hoja y celda en donde se va a poner la fórmula, escribir un signo de igual (=) y la función.

2. Seleccionar la Hoja2 presionar la tecla SHIFT y seleccionar la Hoja6, en la barra de fórmulas se presenta el inicio de la fórmula.

3. Seleccionar el rango de celdas, al marcarlo en una hoja se aplica a toda la selección, en la barra de fórmulas se presenta la sintaxis completa.

4. Para finalizar presionar ENTER.


Las referencias 3D no se pueden utilizar en: Fórmulas matriciales * . Con el operador de intersección (un solo espacio) o en fórmulas que utilicen una intersección implícita ** Qué ocurre con una referencia 3D al mover, insertar o eliminar hojas.

Para explicar lo que ocurre al ejecutar cualquiera de los comandos de edición, se va a retomar como ejemplo la fórmula anterior. ♦

♦

♦

♦

♦

Si se insertan o copian hojas entre la Hoja2 y la Hoja6, Excel incluye en los cálculos todos los valores del rango de celdas A2:C5 de las hojas que se agreguen. Si se eliminan hojas entre la Hoja2 y la Hoja 6, Excel elimina los valores del cálculo. Si se mueven hojas situadas entre la Hoja2 y la Hoja6 a una ubicación fuera del rango al que se hace referencia, Excel elimina del cálculo sus valores. Si se mueve la Hoja2 o la Hoja6 a otra ubicación en el mismo libro, Excel ajusta el cálculo para que se integre el nuevo rango de hojas que existe entre ellas. Si se mueve la Hoja6 y se coloca antes de la Hoja2, la fórmula del ejemplo quedaría: =SUMA(Hoja2:Hoja5!A2:A5).

Fórmula matricial.- ejecuta varios cálculos y devuelve un único resultado o varios resultados. *

**

Es una fórmula, una referencia a una columna o a una fila de celdas en vez de una celda Excel selecciona como fórmula una celda de la referencia que éste en la misma fila o columna. Por ejemplo, si la celda C10 contiene la fórmula =B5:B15*5, Excel multiplicará el valor de la celda C10 por 5, ya que las celdas B10 y C10 están en la misma


Un vínculo es un fórmula que establece una relación dinámica con datos de otros libros de trabajo. Esto es, cualquier cambio que se realice en los datos se refleja inmediatamente en el valor resultante de la fórmula. Usar la vinculación es particularmente útil cuando se trabaja con modelos de hojas de cálculo en los que sería poco práctico tratar de mantenerlos en el mismo libro de trabajo. Al subdividir un modelo complicado en una serie de libros interdependientes se ahorra memoria, ya que es posible trabajar en el modelo sin tener que abrir al mismo tiempo todas las hojas relacionadas al mismo.

El libro Informe de gastos depende de la información de los libros de Ventas y Nómina, las celdas que contienen los valores son las referencias externas. La siguiente fórmula muestra la sintaxis para crear un vínculo.

Nombre del libro de trabajo con extensión

Nombre de la

Referencia de celda

Los términos siguientes se aplican a los libros de trabajo vinculados. : Es la referencia de una celda o rango en una hoja de otro libro de trabajo. : Un libro de trabajo que contiene un vínculo con otro libro de trabajo y que por lo tanto depende de la información que contiene el otro libro. : Es el libro de trabajo que contiene la información a la que se hace referencia en una fórmulas de vínculos. La siguiente ilustración muestra un sistema de presentación de informes sobre ingresos y gastos.

El nombre del libro está entre corchetes

Un signo de admiración precede a la referencia

Un vínculo se puede crear escribiendo la fórmula directamente en la celda, en este caso es necesario escribir la ruta de acceso indicando la unidad de disco y carpetas.


3. Complementar la fórmula y presionar la tecla ENTER.

Ejemplo 1. En el libro que va a contener la fórmula, seleccionar la celda en donde se va a introducir la referencia externa. Si se crea una nueva fórmula escribir un signo de igual (=). Si se introduce la referencia en otro lugar de la fórmula escribir un operador.

Cómo guardar libros de trabajo vinculados

De forma general, se deben guardar los libros de trabajo fuente antes de guardar el libro de trabajo dependiente. Lo anterior garantiza que las fórmulas en el libro de trabajo fuente han sido calculadas y que los nombres de las hojas en las referencias externas son actuales. Si se nombra o vuelve a nombrar un libro de trabajo fuente con el comando o , el libro de trabajo dependiente debe estar abierto para que el nombre se actualice en la referencia externa. En el caso de que se cambie el nombre de un libro de trabajo fuente, cuando el libro de trabajo dependiente está cerrado, se deberán cambiar los vínculos en el libro de trabajo dependiente, para incluir el nuevo nombre o la ruta de acceso de los libros de trabajo fuente. Actualizar y cambiar vínculos

Para actualizar los datos fuente, en el libro de trabajo activo; utilizar el comando del menú . 1

2. Activar el vincular.

libro *

2

3

4

y seleccionar la celda o celdas que se desean

5 6 7

Para facilitar el ir de un documento a otro y presentar todas las ventanas a la vez, elegir el comando en el menú y seleccionar una de las opciones mosaico, horizontal o vertical. *

1.

contiene los datos.

: Muestra el nombre del libro de trabajo que


2.

: Nombre del rango vinculado (si el rango no contiene un nombre el espacio se muestra en blanco).

3.

: Muestra el nombre de la aplicación o el tipo de aplicación u objeto al que esta vinculado el libro.

4.

: Indica si el vínculo debe actualizarse manual o automáticamente y si está bloqueado.

5.

: Actualiza todos los vínculos seleccionados en el cuadro de diálogo vínculos.

6. 7.

seleccionado.

: Abre el archivo correspondiente al vínculo

: Especifica un archivo de origen diferente para un vínculo seleccionado.

Eliminar vínculos entre los libros de trabajo

La fórmula de un vínculo puede ser reemplazada por su valor * . 1. Poner la celda activa en donde se encuentra la fórmula del vínculo 2. Copiar la celda 3. Sin mover la celda, ir al menú 4.

y seleccionar el comando

Seleccionar la opción Valores, sólo pega el valor actual de la fórmula copiada.

Al reemplazar una fórmula con su valor, se elimina la fórmula de manera permanente. *


Esta fórmula contiene una

Cuando una fórmula hace referencia a su propia celda, directa o indirectamente, se denomina Referencia Circular. Para calcular esta fórmula Excel debe calcular cada celda implicada en la referencia circular utilizando los resultados de Iteración * anterior. Si no se cambia el valor predeterminado de la iteración, Excel detendrá los cálculos tras 100 iteraciones o después de que todos los valores en la referencia circular cambien menos de 0,001 entre iteraciones, lo que ocurra primero. Excel no puede resolver fórmulas con referencia circular mediante el cálculo normal. Cuando se hace una referencia circular, aparece un mensaje indicando que existe una referencia circular, si se produjo la referencia de forma accidental dar clic en el botón ACEPTAR. Sin embargo, algunas formulas científicas y técnicas requieren referencias circulares. Si es necesario cambiar el número de iteraciones elegir en el menú y a continuación dar clic en la ficha . Activar la casilla de verificación Iteración e indicar el número máximo de iteraciones y el grado de cambio que se desea utilizar. El siguiente ejemplo muestra la forma en que se puede utilizar la herramienta de iteraciones. En el departamento de Recursos Humanos, se tiene que calcular la bonificación de un empleado que es el 10% de la ganancia neta, que a su vez depende de la cantidad de la bonificación. La ganancia bruta es de $1,000 la neta es igual a la bruta menos la bonificación, y esta es el 10% de la ganancia neta. Al introducir la fórmula se presenta el cuadro de diálogo de las referencias circulares.

Iteración: cálculo repetido en una hoja de cálculo hasta que se cumple una determinada condición numérica. *

Dar un clic en el botón ACEPTAR Sobre la hoja de cálculo se presentan unas flechas señalando las celdas que hacen la referencia circular.

Flechas de

Celdas que hacen la referencia

Al seleccionar la opción de iteraciones, se realiza el calculo de la fórmula.


1. ¿Qué es una referencia 3D?

2. Describir el procedimiento para insertar una referencia 3D, dentro de un fórmula, utilizando el ratón.

3. Definir los siguientes conceptos Referencia externa Libro fuente Libro dependiente

4. ¿Qué es una Referencia Circular?

5. Elaborar las siguientes tablas, una en cada hoja del libro. Utilizando las referencias 3D, hacer una suma de los libros vendidos y el total de ventas de enero, febrero y marzo.


2. Seleccionar el rango de la tabla sin los encabezados.

Una tabla de datos, es un rango de celdas que muestran cómo al cambiar determinados valores de las formulas afecta a los resultados de la misma. En el siguiente ejemplo se desea saber el costo de los boletos de avión al aplicarles diferentes descuentos. 1. Escribir la fórmula =7000-B6*7000, en donde 7000 es el costo del boleto, en cada celda cambia el valor, B6 es una celda variable * que se utiliza por Excel para substituir los valores de la variable (el porcentaje de descuento). 3. Seleccionar el comando

del menú

.

4. En la celda de entrada (columna) escribir la celda variable, para el ejemplo es la celda B6.

Celda en la que se sustituye una lista de valores procedente de una tabla de datos. Es recomendable que la celda se encuentre fuera de la tabla de datos. *


5. Dar un clic en el botón ACEPTAR, Excel llena la tabla con los resultados del cálculo. Y ahora la tabla se convierte en un rango matricial*. Uso de una tabla de dos variables

Es una herramienta para ver la forma en que los cambios realizados en dos variables afectan el resultado de una fórmula. En este tipo de operación se pueden incluir varios valores para cada una de las variables, pero sólo se puede utilizar una fórmula. En el siguiente ejemplo, se presenta la forma en que los distintos tipos de interés y plazos del préstamo influyen en el pago de una hipoteca. La celda A8 contiene la fórmula de pago, =PAGO(D4/12,D4,-D5), que utiliza dos celdas variables D4 y D5.

Dar un clic en el botón ACEPTAR.

Fórmula que contiene las dos celdas variables Celda variable para cálculo del % Celda variable para cálculo de plazo (fila) Valor es para Valores para el % del

Seleccioar la tabla, ir al menú y elegir el comando las celdas variables para la fila y la columna.

*Rango

común.

, indicar

matricial, es un área rectangular de celdas que comparten una fórmula

Los valores calculados


Edición de una tabla de datos

En una tabla de datos nada más se pueden editar las variables, ya que los valores resultantes forman parte de una matriz * no es posible editarlos de forma individual. Los comandos de edición que se pueden utilizar son Copiar, Pegar y Borrar; considerando lo siguiente: ♦

♦

♦

Cuando se copia una matriz resultante de una tabla de datos, nada más se pegan los valores. Para convertir una matriz en valores constantes se hace un Pegado especial. Para borrar una matriz de valores, seleccionar los datos y oprimir la tecla de SUPRIMIR.

*Una

matriz es una fórmula simple que produce resultados múltiples o que operan sobre un grupo de argumentos organizados en filas y columnas.


: Son los rangos en donde se encuentra la información que se va a consolidar. En la forma más básica los datos, constan de una serie de información detallada. Por ejemplo, listas de gastos, registros de ventas, cantidades de artículos de un inventario. Para que esta información sea de utilidad en un proceso de toma de decisiones, los datos deben estar organizados y resumidos, Excel proporciona varias formas de resumir los detalles, como son las órdenes Consolidar, Subtotales y Tablas Dinámicas. En ciertas ocasiones un método es más recomendable que otro.

La mayoría de las hojas de cálculo contienen una o varias categorías de datos. Por ejemplo, cada uno de los cuatro informes trimestrales contiene datos agrupados por meses. Cada una de las tres categorías mensuales de cada consolidación trimestral se identifica con un rótulo (un encabezado de fila o columnas).

La Consolidación es el método más sencillo de ejecutar, funciona mejor sobre listas simples que contienen una sola columna de rótulos de texto en la parte izquierda de la lista. Una de las ventajas de utilizar la consolidación es que se pueden resumir datos almacenados en hojas de cálculo y libros de trabajo diferentes.

Por posición

Se puede elegir consolidar los datos según sus categorías o según su posición relativa en las hojas de cálculo fuente.

Se utiliza este tipo, cuando los datos de todas las áreas de origen se organizan en idéntico orden y situación. Por ejemplo,

Por ejemplo, se pueden consolidar datos de hojas de cálculo que contienen informes mensuales y concentrarlos en informes trimestrales y posteriormente consolidarlos para crear un informe anual, como lo muestra la imagen siguiente.

Cuando se realiza una consolidación por posición, los rótulos de categorías, por ejemplo los encabezados de fila en los tres informes mensuales se ignoran y sólo se consolidan los valores que se encuentran en la misma posición de cada área fuente. Los datos que se consolidan pueden encontrarse en libros de trabajo independientes, en el mismo libro, en la misma hoja, incluso en otras hojas de cálculo como Lotus 123. Para realizar un consolidación es necesario contar con: consolidada.

: Es el área que va a contener la información


Para consolidar los datos por posición: 1. Seleccionar la celda superior izquierda del área de destino 2. En ele menú dar un clic en en el cuadro de diálogo que se presenta, definir los siguientes parámetros

encabezados de fila y columna escribir los rótulos por encima y a la izquierda del área de destino seleccionada. Todas las áreas fuente y el área de destino deben estar organizadas de idéntica manera, con ubicaciones absolutas idénticas en cada hoja de cálculo. Si en cuadro de diálogo se activan las casillas de verificación Usar rótulos en, al activar el botón de ACEPTAR se presenta un mensaje indicando que no se ha consolidado ningún dato.

Se recomienda para resumir un conjunto de hojas de cálculo que tienen los mismo rótulos pero la organización de los datos diferente. Este método combina los datos que tienen rótulos que coinciden en cada hoja de cálculo. Por ejemplo, si se tiene un conjunto de datos con la siguiente estructura.

En el cuadro , dar un clic en la función de resumen que se desea utilizar para consolidar los datos. En el cuadro Referencia, escribir o seleccionar el área de origen de los datos que se desean consolidar. Dar un clic en el botón

.

Repetir los pasos 4 y 5 en cada área de origen que se desea consolidar. Para actualizar automáticamente la tabla de consolidación cuando cambien los datos de origen, seleccionar la casilla e verificación Para crear vínculos, el área de origen y el área de destino deben estar en diferentes hojas de cálculo. Una vez creados los vínculos, no se pueden agregar nuevas áreas de origen, ni cambiar las que se han incluido en la consolidación. Al seleccionar el área de destino para los datos que se consolidan por posición, es necesario tomar en cuenta: Los rótulos de categorías no se copian en el área de destino, aún cuando se seleccionen en las áreas fuente. Si se desean incluir

Excel utiliza los rótulos de las áreas fuente para determinar las categorías y se copian de forma conjunta con los datos, al área de destino, en la imagen anterior se consolidan todos los rangos titulados “Producto A”, sin tener en cuenta su ubicación en las áreas fuente. La consolidación se realiza con el mismo procedimiento.


Si una consolidación no genera los resultados esperados, es recomendable verificar que: ♦

♦

♦

♦

Se han introducido correctamente todas las referencias de las áreas de origen. Se ha seleccionado la función de resumen correspondiente en el cuadro de diálogo . Se ha especificado un área de destino suficiente para mantener los datos de consolidación. Si se realiza una consolidación por posición, asegurarse de que cada área de origen contiene el mismo rango de datos en el mismo orden.

Si se realiza una consolidación por categorías, asegurarse también de que: ♦ ♦

♦

Se incluyan los rótulos de las filas y columnas en el área de origen. Se han seleccionado las casillas de verificación cuadro de diálogo .

en el

Se han introducido en todas las áreas de origen rótulos de categoría con idéntica ortografía, mayúsculas y minúsculas.


Colocar la celda activa dentro de la tabla, ir al menú , . Los datos de una hoja de cálculo que contienen varios niveles de subtotales pueden resumirse mediante un .

, seleccionar

Al aplicar los Autoesquema, se presentan los símbolos del esquema y los datos adquieren un nuevo formato automáticamente.

Al crear un Esquema , se definen rangos de filas o de columnas como grupos o niveles, cada nivel consta de datos detallados y de subtotales en la fila o columna adyacente. El Esquema puede contener hasta ocho niveles verticales y horizontales, solamente se puede crear uno por hoja de cálculo.

Es uema de

Elaboración automática de un Esquema

Se recomienda utilizar los Esquemas cuando los datos de la hoja de cálculo están organizados en filas o columnas de detalle Hoja de cálculo antes de ser esquematizada

Filas de detall

Esquema

de

Los símbolos del esquema se usan para mostrar u ocultar detalles de los datos Muestra un nivel específico del esquema para toda la tabla de Columnas de detalle


♦

Los botones del esquema tienen la siguiente función: ♦ ♦

Para eliminar los esquemas:. Seleccionar el rango esquematizado . Elegir el comando Seleccionar el comando

del menú .

Un escenario es un rango de variables llamadas Celdas Cambiantes * que se guardan con un nombre. Cada conjunto de Celdas Cambiantes representa un grupo de supuestos “Y si...” que se pueden aplicar a una hoja de cálculo modelo para ver los efectos producidos en otras partes del modelo, se pueden definir hasta un máximo de 32 Celdas Cambiantes por escenario. Esta herramienta permite: ♦

♦

Crear varios escenarios con distintos conjuntos de celdas cambiantes. Ver los resultados de cada escenario en la hoja de cálculo.

Las celdas cambiantes en la hoja de cálculo contienen los valores que se desean modificar. Estos valores se encuentran dentro de una fórmula clave, pero estas celdas cambiantes no pueden contener una fórmula. *

Cambiar escenarios de un grupo en un modelo de un solo escenario. Proteger los escenarios contra modificaciones. Seguir las modificaciones con una historia de escenario automático.


La siguiente imagen muestra un modelo de escenario.

Crear un escenario

En la siguiente tabla se desea saber la forma en que afectan diferentes montos en el Ingreso de ventas y el Costo de ventas en la Utilidad bruta . Para lo cual se genera un escenario con diferentes valores, de la siguiente manera:

En el cuadro de diálogo , escribir un nombre para el escenario e indicar las celdas cambiantes, dar un clic en el botón .

Celdas cambiante s Celda

En el menú seleccionar el comando , se presenta el siguiente cuadro de diálogo que indica que no hay ningún escenario definido, dar un clic en el botón . En el cuadro Valores del escenario, escribir los nuevos datos que van a reemplazar los existentes. Si se requiere agregar más valores dar un clic en el botón , al complementar los valores dar un clic en el botón .


6

2

7 8 9 3

Se presenta el cuadro de diálogo Administrador de escenarios, con una lista de los escenarios creados. Dar un clic en el botón

.

Visualizar los escenarios

Una vez que se han definido los escenarios ya se pueden examinar más detenidamente. En el menú siguiente cuadro de diálogo: 1

seleccionar

, se presenta el 4 5

1.

Presenta una lista de los Escenarios creados.

2.

Escenario seleccionado

3.

Celdas cambiantes que son parte del escenario

4.

Muestra en la hoja de cálculo los valores del escenario seleccionado

5.

Agrega un escenario nuevo y presenta el cuadro de diálogo Agregar escenario

6.

Elimina el escenario seleccionado

7.

Cambia el nombre, las referencias de las celdas variables o los comentarios del escenario seleccionado

8.

Combina los escenarios de las hojas seleccionadas en cualquier libro abierto

9.

Crea un informe de resumen de escenarios como una hoja nueva dentro del libro activo.


Crear un resumen de escenario

Mediante el cuadro de diálogo anterior, es difícil valorar el impacto de los diferentes escenarios de la hoja porque sólo se puede ver un escenario a la vez. Una mejor forma de comparar los resultados es mediante un resumen que presente todos los escenarios juntos en una sola hoja de cálculo. Para generar un resumen hacer lo siguiente: 1.

En el menú

seleccionar

2.

En el cuadro de diálogo Administrador de escenarios, dar un clic en el botón se abre el cuadro En caso de conocer el resultado de una fórmula sencilla, pero no la variable que determina el resultado, se puede utilizar la función , que sustituye diferentes valores dentro de la celda cambiante hasta que el valor de la celda objetivo sea igual al valor que se desea, con está herramienta se evita perder tiempo realizando análisis de ensayo y error.

3.

Comprobar que el botón de opción está seleccionado y que el cuadro indica la referencia de la celda que contiene la fórmula y luego dar un clic en el botón ACEPTAR

En el libro de trabajo se agrega una hoja nueva denominada . Esta hoja muestra los cambios y resultados de cada escenario.

Por ejemplo, se desea saber cuantos dólares se pueden comprar con 32 800 pesos. Con la herramienta se puede saber de forma muy rápida. 1. En la hoja de cálculo hacer una tabla como la siguiente

En el lado izquierdo y en la parte superior de la hoja hay símbolos de esquema de una hoja de cálculo subtotalizada.

Está celda contiene la fórmula =B2*A2 para calcular el costo

2. Ir al menú y seleccionar el comando se presenta el siguiente cuadro de diálogo Indicar la celda que contiene la fórmula a la que se desea

,


Sugerencias para la búsqueda de objetivos ♦

Escribir el valor que se desea obtener de la fórmula

♦

♦

Indicar la celda que contiene el valor que se desea cambiar (Celda

3. Dar un clic en el botón ACEPTAR, en la presenta el valor que produce el resultado.

se

En los cuadros “Definir la celda” y “Para cambiar la celda” se pueden introducir las referencias o nombres. Una Celda cambiante debe contener un valor del que dependa, directa o indirectamente, la fórmula contenida en el cuadro “Definir la celda”. Una Celda cambiante no puede contener una fórmula.


1. ¿Qué es una pronóstico?

Tabla

de

2. Elaborar la siguiente tabla, para calcular el costo de los productos considerando los diferentes porcentajes de incremento. Utilizar la herramienta de Tabla

3. ¿Qué es Consolidación?

6. ¿Qué utilidad esquema?

tiene

7. ¿Qué es escenario?

un

un

la

4. ¿Qué diferencia hay entre Consolidación por ?

y por 8. ¿Para qué se utiliza la herramienta Buscar objetivo?

5. Elabora una Tabla de consolidación con los siguientes datos


Elaborar Filtros Avanzados

Una lista, es una forma de guardar datos en una hoja de cálculo, consta de una serie de filas rotuladas que contienen datos similares. La lista se puede considerar como una simple base de datos, donde las filas constituyen los registros y las columnas los campos . Por ejemplo, un listado de clientes y sus números de teléfono.

1. Insertar varias filas en la parte superior de la hoja de cálculo.

Excel cuenta con diferentes herramientas para consultar, ordenar, buscar y crear informes con los datos almacenados en una lista. A continuación se presentan las de mayor uso.

3. En la fila debajo de los rótulos de las columnas, escribir los criterios de filtración.

La filtración constituye un método fácil y rápido para encontrar subconjuntos de datos en una lista. Cuando se filtra una lista, Excel muestra sólo las filas que contienen cierto valor o que cumplen un conjunto de condiciones de búsqueda llamados , y las demás quedan ocultas.

2. En una de las filas que se insertaron, escribir los rótulos de las columnas que se desean filtrar. Es recomendable que se haga una copia de los rótulos para que sean idénticos y la filtración se realice sin problema.

Rango de

Los filtros avanzados se utilizan para buscar datos basados en criterios complejos o calculados, los cuales se pueden copiar automáticamente en otra área del libro de trabajo. El comando , filtra la lista en su sitio, tal como lo hace el filtro automático, pero no muestra las listas desplegables de las columnas. En cambio es necesario introducir las condiciones en un rango de criterios * de la hoja de cálculo. El definir un rango permite filtrar una lista usando dos tipos de criterios: ; muestran las filas que contienen valores especificados o valores que corresponden a los límites establecidos. Los criterios de comparación pueden ser una serie de caracteres que deben de coincidir. Por ejemplo, “Sur” o una expresión como “>=600”. ; evalúan una columna seleccionada de la lista contra valores no contenidos en la misma.

*Rango

de criterios, es un rango de celdas que contiene un grupo de condiciones de búsqueda.

4. Colocar la celda activa dentro de la tabla, ir al menú seleccionar el comando y después .

y


5. Se presenta el siguiente cuadro de diálogo,

Los datos que cumplen con los criterios se copian en el lugar que se indico.

a

b c d d a. Acción los criterios especificados.

; oculta las filas que no cumplen con

; copia los datos filtrados en otra hoja de cálculo o en la misma. b. Rango de la lista, indicar el rango de celdas que comprende la lista que se desea filtrar c.

Rango de criterios, celdas que contiene los criterios de filtración

d. Copiar a, especificar un rango de celdas para copiar las filas que reúnan los criterios especificado. Este cuadro sólo esta activo cuando se elige la opción e.

Sólo registros únicos, muestra las filas únicas que cumplen los criterios y excluye filas que contienen elementos duplicados

6. Dar un clic en el botón ACEPTAR

Para especificar más de un criterio en la misma columna, o un rango de valores, introducir el rótulo de esa columna más de una vez. Por ejemplo, Si se incluyen dos o más condiciones en una sola columna, escribir los criterios en filas independientes, una directamente bajo otra.

Zona de criterios Nada más presenta las filas de los duraznos y mangos


1. Seleccionar una celda dentro de la lista La herramienta de es útil para resumir los datos de una lista, no es necesario introducir fórmulas en la hoja, el cálculo se realiza de forma automática. Cuando se desea resumir una lista que tiene varias columnas o campos de entrada, de texto, como por ejemplo el inventario de una empresa, la subtotalización es mejor que la consolidación, ya que incluye todos los campos de texto y se pueden crear subtotales para grupos dentro de cada campo de texto. Además, el resumen subtotalizado contiene todos los datos detallados y permite mostrar cualquier nivel de detalle.

2. En el menú seleccionar el comando , se muestra el cuadro de diálogo en donde se definen los parámetros para el cálculo a

b c

Calcular los subtotales

Antes de crear los subtotales es necesario ordenar la lista de datos, tomando como criterio de ordenación el campo del que se desea hacer el resumen. Por ejemplo, en la siguiente lista se desea saber el total de ventas por producto. La lista se encuentra ordenada por producto.

d

e

f

a. Especificar la columna que contienen los grupos para los subtotales, la columna seleccionada es la que se tomo para la ordenación b. Seleccionar la función que se desea para calcular los subtotales c. Activar una o más casillas, para especificar las columnas que contienen los valores de los que se desea realizar el subtotal d. Reemplaza todos los subtotales de la lista con los actuales e. Inserta saltos de página automáticamente después de cada grupo de subtotales f. Inserta las filas de subtotal y de total general bajo los datos de detalle g. Elimina todos los subtotales de la lista 3. Dar un clic en el botón ACEPTAR


Cuando se muestran los subtotales Excel esquematiza la lista agrupando las filas de detalle con cada fila de subtotal asociada y las filas de subtotales con la fila de total general. Símbolos del

Las filas de detalle se agrupa n con la fila del

Los botones de nivel de fila se utilizan para ocultar o mostrar el detalle de todos los subgrupos. Al hacer clic en el botón 2 se ocultan todas las filas de detalle

Cada signo de “+” permite ver el detalle de cada

Suma

Realiza la suma de los elementos

9

Cuenta

La cantidad de los valores que no son un espacio en blanco

2

Promedio

El promedio de los elementos en el grupo del subtotal

1

Max

El valor más grande en el grupo del subtotal

4

Min

El valor más pequeño en el grupo del subtotal

5

Producto

La multiplicación de todos los valores del grupo del subtotal

6

Cuenta num

La cantidad de filas que contienen datos numéricos en el grupo del subtotal

3

Desvest

Una estimación de la desviación estándar en una población basada en una muestra donde el grupo del subtotal es la muestra

7

Desvestp

La desviación estándar de una población donde el grupo del subtotal es toda la población

8


Var

Una estimación de la varianza de una población basada en una muestra donde el grupo del subtotal es la muestra

10

Varp

La varianza de una población donde el grupo del subtotal es toda la población

11


7. Con los datos de la siguiente lista, crear una tabla de resumen que contenga el total de ventas por Zona y Producto. 1. ¿Qué es filtro?

un

2. ¿Qué ventaja tienen los filtros avanzados sobre los automáticos?

3. ¿Cómo se elabora un gráfico con una lista filtrada?

4. ¿Para qué se utiliza la herramienta de Subtotales?

5. De una lista de ventas, se desea saber cuánto se vendió de cada producto. ¿Qué herramienta es la más adecuada utilizar , o ?

6. ¿Por qué?


4. En el primer cuadro de diálogo indicar en dónde se encuentran los datos que se desean analizar. Una Tabla dinámica combina lo mejor de la Consolidación y de los Subtotales y va más allá de estas dos herramientas al proporcionar una mayor flexibilidad en la presentación de los datos. Con una Tabla dinámica, se pueden organizar los datos almacenados en varías hojas de cálculo o libros así como mostrar u ocultar los detalles que se deseen. De forma adicional se puede cambiar la presentación de la Tabla dinámica cambiando la disposición o el nivel de detalle que aparezca visualizado sin tener que volver a crearla, nada más arrastrando los campos a otra posición de la hoja de cálculo. Una tabla puede actualizarse cada vez que cambian los datos fuente, estos datos permanecen intactos y la Tabla dinámica permanece en la hoja donde fue creada. Algunas ventajas que tiene utilizar una tabla, son: ♦

♦

♦ ♦

Analizar información resumida de los datos de la lista, ya sea de Excel u otra aplicación, utilizando métodos de cálculo o funciones de resumen, como por ejemplo, suma o promedio. También se puede controlar la forma de calcular los totales y subtotales.

5. Dar un clic en el botón SIGUIENTE, se presenta el cuadro de diálogo que indica la ubicación y rango de los datos fuente.

Mostrar solamente los detalles que se desean extraer de la lista de datos fuente. Cambiar fácilmente la estructura de los datos. Crear gráficos que muestren los distintos niveles de detalle. A medida que se cambie la presentación de la Tabla dinámica, el gráfico cambiará automáticamente.

Una Tabla dinámica se crea en cualquier hoja de cálculo y puede haber más de una tabla dentro de la misma hoja. 1. Abrir el libro en donde se desea crear la Tabla dinámica. 2. Si se basa la Tabla dinámica en una lista de Excel, colocar la celda activa dentro de la lista. 3. En el menú

seleccionar

.

Rango de la lista

Dar un clic en este botón para traer los datos que se encuentran en


6. Dar un clic en el botón SIGUIENTE, el este paso se establece la estructura de la tabla dinámica.

La tabla dinámica que se genera presenta la siguiente estructura.

Elemento de campo de Campo de

Rótulos de campos de la lista

Cam o de

Campo de datos

Campo de fila Colocar los botones de campos en los Área de datos

7. Dar un clic en el botón SIGUIENTE, en el cuadro de diálogo que se muestra indicar en dónde se va a colocar la Tabla dinámica.

: Subcategoría de un campo de la Tabla dinámica. Los elementos que aparecen como rótulos de columna o de fila o en las listas desplegables de los campos. : Es un campo de una lista o de una tabla de origen que contiene datos. Normalmente resume datos numéricos como estadísticas o importe de ventas, pero también puede contener texto. Los datos de texto se resumen con la función CONTAR. resumidos.

: Es un campo que se utiliza para filtrar los datos

: Cada entrada o valor exclusivo del campo o columna de la lista de origen es un elemento en la lista de un campo de página. 8. Dar un clic en el botón TERMINAR.

: Son los campos a los que se ha asignado una orientación de fila en la Tabla dinámica. : Son los campos a los que se asignado una orientación de columna en la Tabla dinámica.


: Es la parte de la Tabla dinámica que contiene los datos resumidos. Las celdas del área de datos muestran los datos resumidos de los elementos de los campos de fila y de columna. Los valores de cada celda del área de datos representan un resumen de los datos procedentes de filas o registros de origen.

o impresiones. Ocultar detalle

Oculta los datos de detalle de un elemento externo en un campo de filas o columnas.

Mostrar detalle

Muestra los datos de detalle de un campo de fila o de columna ocultos.

Presenta un menú con los comandos de uso más frecuente en las tablas.

Actualizar datos

Actualiza los datos de una Tabla dinámica .

Tablas

Inicia el asistente para Tablas dinámicas que guía a través de la creación o modificación de una Tabla.

Seleccionar rótulo

Campo de Tabla dinámica

Dependiendo de cuál sea la celda activa de la Tabla dinámica, cambia los cálculos de subtotales y elementos de un campo, o modifica las propiedades de un área de datos.

Selecciona sólo los rótulos al hacer clic en un rótulo de campo de la Tabla dinámica o en un rótulo de elemento. Los datos asociados no se seleccionan.

Seleccionar datos

Selecciona sólo los datos asociados al hacer clic en un rótulo de campo de la Tabla dinámica o un rótulo de elemento. Los rótulos de campo o de elemento no se seleccionan.

La barra de herramientas de las Tablas dinámicas es la siguiente:

Tabla dinámica

Asistente para dinámicas

Mostrar páginas

Muestra los datos de un campo de página, en una nueva hoja de cálculo, dentro del mismo libro.

Desagrupar

Divide cada repetición de un grupo en los elementos que forman parte de él; por ejemplo, divide los trimestres en las fechas individuales.

Agrupar

Agrupa elementos por categorías para crear un solo elemento a partir de varios,; por ejemplo, se pueden agrupar días, semanas, meses u otras fechas en trimestres para realizar análisis, gráficos

Seleccionar datos

rótulos

y

Selecciona los datos asociados y los rótulos al hacer clic en un rótulo de campo de la Tabla dinámica o un rótulo de elemento-


1. Seleccionar toda la tabla 2. En ele menú

1. ¿Qué es dinámica?

, elegir

una

y dar un clic en

Tabla

2. Mencionar las ventajas de utilizar una Tabla dinámica para resumir los datos de una lista.

3. Definir las siguientes partes de una Tabla dinámica. Campo página

de

Campo de datos Área de datos

4. ¿Cómo se elimina una Tabla dinámica?


Cuenta las celdas que contienen un número en una columna de una lista o base de datos y que concuerdan con los criterios especificados. El rango de celdas E1:F2 son los criterios y se

Esta función cuenta los registros que tienen un número en la columna de MONTO, que son PRODUCTO gasolina y de

=BDCONTARA(petreo,3,CRITER)

Cuenta el número de celdas que no están en blanco dentro de los registros de la base de datos que cumplen con los criterios especificados. =BDMAX(petreo,3,CRITER)

Devuelve el valor máximo de las entradas seleccionadas de una base de datos que coinciden con los criterios. =BDMIN(petreo,3,CRITER)

Devuelve el valor mínimo de una columna en una lista o base de datos que concuerde con las condiciones especificadas. =BDSUMA(petreo,3,CRITER)

Suma los números de una columna de una lista o base de datos que concuerden con las condiciones especificadas.

=BDPROMEDIO(petreo,3,CRITER)

Devuelve el promedio de las entradas seleccionadas de una base de datos que coinciden con los criterios.


Devuelve el día de la semana correspondiente al argumento . El día se devuelve como un número entero entre 1 (domingo) y 7 (sábado).

Ejemplos

es el código de que Excel usa para los cálculos de fecha y hora; puede expresarse como texto, por ejemplo "15-abr-1993" o "15-4-93", en lugar de expresarse como un número. El texto se convierte automáticamente en un número de serie.

DIASEM(29747,007) es igual a 4 (miércoles)

DIASEM("2-14-90") es igual a 4 (miércoles) Devuelve la hora correspondiente al argumento . La hora se expresa como un número entero, comprendido entre 0 (12:00 A.M.) y 23 (11:00 P.M.). =HORA(

)

El argumento puede introducirse como texto, por ejemplo "16:48:00" ó "4:48:00 PM.", en lugar de como un número. El texto se convierte automáticamente en un número de serie. Ejemplos HORA(0,7) es igual a 16 HORA(29747,7) es igual a 16 HORA("3:30:30 PM") es igual a 15


Se utilizan para ver si una condición es cierta o falsa o, para comprobar varias condiciones. =O(

.)

Devuelve VERDADERO si alguno de los argumentos es VERDADERO; devuelve FALSO si todos los argumentos son FALSOS. ; ;... son entre 1 y 30 condiciones que se desean comprobar y que puede ser VERDADERO o FALSO. Los argumentos deben ser valores lógicos como VERDADERO o FALSO, si el rango especificado no los contiene, está función presenta el valor de error

En este ejemplo, Si el valor de C2 es mayor que B2 se escribe la palabra SUR si es falso se resenta NORTE

Ejemplo =O(1+1=1;2+2=5) es igual a falso Si el rango A1:A3 contiene los valores VERDADERO, FALSO Y VERDADERO, entonces: =O(A1:A3) es igual a VERDADERO. =SI(

Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas. =Y(

)

Devuelve un valor si la condición especificada es VERDADERO y otro si dicho argumento es FALSO. es cualquier valor o expresión que se puede evaluar como VERDADERO o FALSO.

;...)

Devuelve VERDADERO si todos los argumentos son VERDADEROS; devuelve FALSO si uno o más argumentos son FALSOS. ;... son de 1 a 30 condiciones que se desean comprobar y que pueden ser VERDADERO o FALSO. Ejemplo

Valor_si_verdadero es el valor que se presenta si prueba_lógica es VERDADERO.

=Y(VERDADERO;FALSO) es igual a FALSO

Valor_si_falso es el valor que se presenta si prueba_lógica es FALSO.

Si B4 contiene un número entre 1 y 100, entonces

Ejemplo

=Y(1
En el siguiente ejemplo, si el valor de la celda B8 es igual al de la celda C2, se presenta la palabra SUR, si es falso se presenta la palabra CENTRO, y si B8 es igual a B2 entonces se escribe la palabra NORTE.

=Y(2+2=4;2+3=5) es igual a VERDADERO


=MEDIANA(

Estas funciones ejecutan análisis estadísticos de rangos de datos. Por ejemplo, proporcionar información estadística acerca de una línea recta trazada como resultado de un conjunto de valores, como la pendiente de la línea y la intersección “Y”, o, a cerca de los puntos reales que componen la línea. =PROMEDIO(

...)

Devuelve la media aritmética de los argumentos. ; .... son de uno a treinta argumentos numéricos para calcular el promedio. Al calcular el promedio, es conveniente tomar en cuenta que las celdas vacías no se toman en cuenta pero los valores cero sí. Ejemplo Si el rango B1:B5 contiene los valores 15, 18, 23, 34, 48: =PROMEDIO(A1:A5) es igual a 27.6 =PROMEDIO(A1:A5,5) es igual a 23.8 =MODA(

....)

Devuelve el valor que se repite con más frecuencia en un rango de datos. , calcular la moda.

... son entre uno y treinta argumentos para

Si dentro del rango de datos hay celdas vacías esos valores no se toman en cuenta, sin embargo, se incluyen las celdas cuyo valor es cero. Si el conjunto de valores no contiene datos duplicados, MODA devuelve el valor de . Ejemplo =MODA(10,12,10,5,10,4,3,10) es igual a 10

....)

La mediana es el número que se encuentra en medio de un conjunto de números, es decir, la mitad de los números es mayor que la mediana y la otra mitad es menor calcular la moda.

... son entre uno y treinta argumentos para

Ejemplo =MEDIANA(1,2,3,4,5) es igual a 3


Mediante estas funciones se pueden manipular cadenas de texto en fórmulas. Por ejemplo, quitar espacios, cambiar mayúsculas por minúsculas. =ESPACIOS(

)

Elimina los espacios del texto, excepto el espacio normal que se deja entre palabras. es el texto en donde se desea quitar el espacio. Ejemplo =ESPACIOS(“ Ganancias primer trimestre “) es igual a “Ganancias primer trimestre” =MAYUSC(

)

Convierte el texto en mayúsculas El argumento texto puede ser una referencia o una cadena de texto. Ejemplo =MAYUSC(“total”) es igual a “TOTAL” Si F10 contiene “producción”, entonces =MAYUSC(F10) es igual a PRODUCCIÓN =MINUSC(

)

Convierte todas las mayúsculas de una cadena de texto en minúsculas. El argumento texto puede ser una referencia o una cadena de texto. Ejemplo =MINUSC(“ADELA SILVA CASTRO”) es igual a “adela silva castro”


¿Qué función se utiliza para definir si una condición es FALSA o VERDADERA? 1. ¿Qué es función?

una

4. Mencionar algunas de las funciones estadísticas 2. ¿Qué utilidad tienen las funciones para bases de datos? 5. ¿Qué utilidad tienen las funciones de texto? 3. Indicar las partes de la siguiente función

=BDCONTAR(A2:D68,3,F1: I2)

¿Qué función se debe utilizar para definir el número de días que hay entre dos fechas?


4. Hacer clic en

.

Nota Para interrumpir una macro antes de que finalice las acciones, presionar ESC.


Ejecutar una macro desde una imagen u objeto Ejecutar una macro desde un módulo de Visual Basic

1. Abrir el libro que contiene la macro. 2. Seleccionar hacer clic en

en el menú .

3. En el cuadro que se desea ejecutar. 4. Hacer clic en 5. Hacer clic en

y, a continuación,

Para evitar el ejecutar la macro, desde el menú , se puede ejecutar desde una imagen u objeto que se inserte en la hoja. 1. Grabar la macro. 2. Insertar una imagen prediseñada o cualquier autoforma.

, escribir el nombre de la macro

3. Colocar el puntero del ratón y dar un clic en el botón derecho. 4. En el menú contextual seleccionar el comando Asignar macro.

. .

: Si se desea ejecutar otra macro diferente mientras se está utilizando el Editor de Visual Basic, hacer clic en la opción en el menú . En el cuadro , escribir el nombre de la macro que se desea ejecutar y, a continuación, hacer clic en . Estructura de un módulos de Visual Basic, esta macro pone un borde azul en el contorno de la selección.

5. En el cuadro de diálogo Asignar macro, dar un clic en el nombre de la macro. 6. Dar un clic en el botón ACEPTAR. 7. Dar un clic fuera de la imagen. 8. Colocar el puntero del ratón, se convierte en una manita, dar un clic y la macro se ejecuta. Cada vez que se desee ejecutar la macro, nada más es necesario dar clic sobre la imagen.


Manua Excel Intermedio

Recommend Documents