C
C
u
u
r
r
s
s
o
o
d
d
e
e
E
E
x
x
c
c
e
e
l
l
Excel Intermedio
FÓRMULAS Y VÍNCULOS ...4
R
EFERENCIA DE OTRA HOJA EN UN LIBRO DE TRABAJO...4
R
EFERENCIAS3D ...4
V
ÍNCULOS ENTRE LIBROS DE TRABAJO...7
F
ÓRMULAS CON REFERENCIA CIRCULAR...10
EJERCICIO I ...11
HERRAMIENTAS AUXILIARES DE CALCULO ...12
T
ABLAS DE PRONÓSTICO...12
C
ONSOLIDACIÓN...15
P
OR CATEGORÍA...16
E
SQUEMAS...18
E
SCENARIOS...19
B
USCAR OBJETIVO...22
EJERCICIO II ...24
LISTA DE DATOS ...25
F
ILTROS AVANZADOS...25
S
UBTOTALES...28
EJERCICIO III...31
TABLAS DINÁMICAS...32
C
REACIÓN DE UNAT
ABLA DINÁMICA...32
S
ELECCIONAR DATOS DE UNA TABLA DINÁMICA...35
F
ORMATO DE UNAT
ABLA DINÁMICA...35
A
CTUALIZAR UNAT
ABLA DINÁMICA...35
C
AMBIAR LOS DATOS FUENTE PARA INCLUIR DATOS O CAMPOS ADICIONALES...35
E
LIMINAR UNAT
ABLA DINÁMICA...36
EJERCICIO IV...36
FUNCIONES ... 37
F
UNCIONES PARA BASES DE DATOS...37
F
UNCIONES DE FECHA HORA...39
F
UNCIONES LÓGICAS...41
F
UNCIONESE
STADÍSTICAS...42
F
UNCIONES DE TEXTO...43
EJERCICIO V ... 44
MACROS: AUTOMATIZACIÓN DE TAREAS DE USO
FRECUENTE... 45
G
RABAR UNA MACRO...45
E
JECUTAR UNA MACRO...45
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.
Fórmulas y Vínculos
Referencia de otra hoja en un libro de trabajo
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.
= H o j a 2 ! A 1
En la celda que se escribe esta referencia, se presenta el valor que contiene la celda A1 de la Hoja2.
Existen dos métodos para introducir una referencia a otra hoja del mismo libro:
I. Escribir la fórmula directamente en la celda, y 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.
Con este procedimiento Excel pone de forma automática la sintaxis correcta.
El nombre de la hoja de cálculo es siempre una referencia
La referencia de la celda puede ser relativa, absoluta o
Referencias 3D
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
=SUMA(Hoja2:Hoja6!$A$2:$C
$5)
Un rango de hojas se especifica con dos puntos colocados entre los nombres de la primera y la última hoja 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.
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 fila.
Vínculos entre libros de trabajo
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.
Los términos siguientes se aplican a los libros de trabajo vinculados. Referencia externa: Es la referencia de una celda o rango en una hoja de otro libro de trabajo.
Libro de trabajo dependiente: 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.
Libro de trabajo fuente: 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 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.
=[EXC97INT.XLS]Maquiladora!$
D$6
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.
El nombre del libro está entre corchetes Un signo de admiración precede a la referencia Nombre de la Referencia de celda Nombre del libro de trabajo con extensión
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.
2. Activar el libro* y seleccionar la celda o celdas que se desean
vincular.
* Para facilitar el ir de un documento a otro y presentar todas las ventanas a la
vez, elegir el comando Organizar en el menú Ventana y seleccionar una de las opciones mosaico, horizontal o vertical.
3. Complementar la fórmula y presionar la tecla ENTER.
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 Guardar o Guardar como, 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 Vínculo del menú Edición.
1 2 3 4
5 6 7
1. Archivo de origen: Muestra el nombre del libro de trabajo que contiene los datos.
2. Elemento: Nombre del rango vinculado (si el rango no contiene un nombre el espacio se muestra en blanco).
3. Tipo: Muestra el nombre de la aplicación o el tipo de aplicación u objeto al que esta vinculado el libro.
4. Estado: Indica si el vínculo debe actualizarse manual o automáticamente y si está bloqueado.
5. Actualizar ahora: Actualiza todos los vínculos seleccionados en el cuadro de diálogo vínculos.
6. Abrir origen: Abre el archivo correspondiente al vínculo seleccionado.
7. Cambiar origen: 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ú Edición y seleccionar el comando Pegado especial
4. 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
Fórmulas con referencia circular
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 Opciones en el menú Herramientas y a continuación dar clic en la ficha Calcular. 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.
Esta fórmula contiene una
f i i l
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 dit i
Celdas que hacen la referencia
i l
Al seleccionar la opción de iteraciones, se realiza el calculo de la fórmula.
Ejercicio I
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.
Herramientas auxiliares de calculo
Tablas de pronóstico
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).
* 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.
2. Seleccionar el rango de la tabla sin los encabezados.
3. Seleccionar el comando Tabla del menú Datos.
4. En la celda de entrada (columna) escribir la celda variable, para el ejemplo es la celda B6.
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.
Seleccioar la tabla, ir al menú Datos y elegir el comando Tabla, indicar las celdas variables para la fila y la columna.
*Rango matricial, es un área rectangular de celdas que comparten una fórmula
común.
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 Los valores calculados
confo an na Valores para
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
Consolidación
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 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 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.
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:
Área de destino: Es el área que va a contener la información consolidada.
Área fuente: Son los rangos en donde se encuentra la información que se va a consolidar.
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).
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.
Por posición
Se utiliza este tipo, cuando los datos de todas las áreas de origen se organizan en idéntico orden y situación. Por ejemplo,
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.
Para consolidar los datos por posición:
1. Seleccionar la celda superior izquierda del área de destino
2. En ele menú Datos dar un clic en Consolidar, en el cuadro de diálogo que se presenta, definir los siguientes parámetros
En el cuadro Función, 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 Agregar.
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 Crear vínculos con los datos de origen.
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
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.
Por categoría
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.
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 Consolidar.
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 Usar rótulos en el cuadro de diálogo Consolidar.
Se han introducido en todas las áreas de origen rótulos de categoría con idéntica ortografía, mayúsculas y minúsculas.
Esquemas
Los datos de una hoja de cálculo que contienen varios niveles de subtotales pueden resumirse mediante un Esquema.
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.
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
Colocar la celda activa dentro de la tabla, ir al menú Datos, seleccionar Agrupar y esquemas, Autoesquema.
Al aplicar los Autoesquema, se presentan los símbolos del esquema y los datos adquieren un nuevo formato automáticamente.
Esquema de
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 d t
Columnas de detalle
Controla el despliegue de información por filas o
Los botones del esquema tienen la siguiente función:
Para eliminar los esquemas:. Seleccionar el rango esquematizado .
Elegir el comando Agrupar y esquemas del menú Datos. Seleccionar el comando Borrar esquemas.
Escenarios
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 menú Herramientas seleccionar el comando Escenarios, se presenta el siguiente cuadro de diálogo que indica que no hay ningún escenario definido, dar un clic en el botón Agregar.
En el cuadro de diálogo Modificar escenario, escribir un nombre para el escenario e indicar las celdas cambiantes, dar un clic en el botón Aceptar. Celdas cambiante s Celda lt t
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 Agregar, al complementar los valores dar un clic en el botón Aceptar.
Se presenta el cuadro de diálogo Administrador de escenarios, con una lista de los escenarios creados.
Dar un clic en el botón Cerrar.
Visualizar los escenarios
Una vez que se han definido los escenarios ya se pueden examinar más detenidamente.
En el menú Herramientas seleccionar Escenario, se presenta el siguiente cuadro de diálogo:
6 2 7 8 9 3
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
4 1
5. Agrega un escenario nuevo y presenta el cuadro de diálogo Agregar escenario
5 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ú Herramienta seleccionar Escenarios
2. En el cuadro de diálogo Administrador de escenarios, dar un clic en el botón Resumen, se abre el cuadro Resumen del escenario
3. Comprobar que el botón de opción Resumen está seleccionado y que el cuadro Celdas resultantes 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 Resumen de escenario. Esta hoja muestra los cambios y resultados de cada escenario.
En el lado izquierdo y en la parte superior de la hoja hay símbolos de esquema de una hoja de cálculo subtotalizada.
Buscar objetivo
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 Buscar objetivo, 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.
Por ejemplo, se desea saber cuantos dólares se pueden comprar con 32 800 pesos. Con la herramienta Buscar objetivo se puede saber de forma muy rápida.
1. En la hoja de cálculo hacer una tabla como la siguiente
Está celda contiene la fórmula =B2*A2 para calcular el costo
2. Ir al menú Herramientas y seleccionar el comando Buscar objetivo, se presenta el siguiente cuadro de diálogo
Indicar la celda que contiene la fórmula a la que se desea encontrar una solución (Celda
3. Dar un clic en el botón ACEPTAR, en la Celda cambiante se presenta el valor que produce el resultado.
Sugerencias para la búsqueda de objetivos
En los cuadros “Definir la celda” y “Para cambiar la celda” se pueden introducir las referencias o nombres.
♦ ♦
♦
Escribir el valor que se desea obtener de la fórmula
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.
Indicar la celda que contiene el valor que se desea cambiar (Celda
Ejercicio II
1. ¿Qué es una Tabla de pronóstico?
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 la Consolidación?
4. ¿Qué diferencia hay entre Consolidación por Categoría y por Posición?
5. Elabora una Tabla de consolidación con los siguientes datos
6. ¿Qué utilidad tiene un esquema?
7. ¿Qué es un escenario?
8. ¿Para qué se utiliza la herramienta Buscar objetivo?
Lista de datos
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.
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.
Filtros avanzados
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 Criterios, y las demás quedan ocultas.
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 Filtro avanzado, 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:
Criterios de comparación; 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”.
Criterios calculados; 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.
Elaborar Filtros Avanzados
1. Insertar varias filas en la parte superior de la hoja de cálculo. 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.
3. En la fila debajo de los rótulos de las columnas, escribir los criterios de filtración.
Rango de it i
4. Colocar la celda activa dentro de la tabla, ir al menú Datos y seleccionar el comando Filtros y después Filtros Avanzados.
5. Se presenta el siguiente cuadro de diálogo,
a. Acción
Filtrar la lista sin moverla; oculta las filas que no cumplen con los criterios especificados.
Copiar a otro lugar; 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 Copiar a otro lugar
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
Los datos que cumplen con los criterios se copian en el lugar que se indico.
a
b c
d
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,
d
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
Criterios de dos o más columnas
Para buscar datos que cumplan una condición en dos o más columnas, introducir todos los criterios en la misma fila del rango.
Criterios calculados
Se puede utilizar una fórmulas como rango de criterios para filtrar una lista, por ejemplo:
Con la fórmula
=C5>Promedio (C6:C8)
. La celda C5, contiene el rótulo de la columna que se va a filtrar, el signo, >, pide el valor mayor de la venta de las manzanas , comparado con el promedio de ventas de los duraznos.Para utilizar los criterios calculados, es recomendable tomar en cuenta que:
♦
♦
La fórmula debe producir los valores lógicos VERDADERO o FALSO, al filtrar la lista sólo se presentan las filas con los valores que producen VERDADERO.
La fórmula debe referirse por lo menos a una columna de la lista
Estos criterios presentan las filas de naranjas que tengan más de
Trabajo con una lista filtrada
La filtración permite realizar operaciones como edición, impresión, ordenación y gráficos con un subconjunto de datos. Cuando una lista se encuentra filtrada muchos comandos y características del programa funcionan, sólo en las celdas visibles, así se pueden excluir las filas que nos se desean trabajar.
Imprimir un lista filtrada
Cuando se imprime una lista filtrada con el comando Imprimir del menú Archivo, el programa imprime sólo los datos visibles.
Uso del botón Autosuma
Si se utiliza el botón Autosuma para calcular el total de datos de una lista filtrada, Excel presenta el total de los datos que se presentan en la pantalla, si se cambia el criterio de filtración, el valor de la fórmula cambia conforme a los datos.
Crear un gráfico a partir de una lista filtrada
Cuando se genera un gráfico a partir de una lista filtrada, Excel toma en cuenta sólo los datos visibles, Sin embargo este mantiene un vínculo con los datos fuente de la lista, de modo que si se cambia el filtro o se usa el comando Mostrar todo, el gráfico cambia para incluir los datos mostrados.
Fórmula que filtra la columna de Venta
Si se desea impedir que se actualice el gráfico cuando se cambia un filtro. Una vez que se han seleccionado los rangos de celdas para el gráfico, elegir el botón de Seleccionar sólo las celdas visibles* y generar
el gráfico.
* Si el botón Celdas visibles no se encuentra en la barra de herramientas. Ir al
menú Ver, Barra de herramientas, Personalizar, Comandos, Edición y arrastrar el botón.
Subtotales
La herramienta de Subtotales 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.
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.
1. Seleccionar una celda dentro de la lista
2. En el menú Datos seleccionar el comando Subtotales, se muestra el cuadro de diálogo en donde se definen los parámetros para el cálculo a b c g 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.
Los botones de nivel de fila se utilizan para ocultar o mostrar el detalle de todos los subgrupos.
Símbolos del Las filas de detalle se agrupa n con la fila del subtota
Funciones de los subtotales
Función Resultado Referencia para
función
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 Cada signo de “+”
permite ver el detalle de cada
Al hacer clic en el botón 2 se ocultan todas las filas de detalle
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
Ejercicio III
1. ¿Qué es un filtro?
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 Subtotales, Consolidación o Filtros?
6. ¿Por qué?
7. Con los datos de la siguiente lista, crear una tabla de resumen que contenga el total de ventas por Zona y Producto.
Tablas dinámicas
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. ♦
♦ ♦ ♦
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.
Creación de una Tabla dinámica
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ú Datos seleccionar Asistente para Tabla dinámicas.
4. En el primer cuadro de diálogo indicar en dónde se encuentran los datos que se desean analizar.
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.
Dar un clic en este botón para traer los datos que se encuentran en otras
Rango de la lista f t
6. Dar un clic en el botón SIGUIENTE, el este paso se establece la estructura de la tabla dinámica.
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.
8. Dar un clic en el botón TERMINAR.
La tabla dinámica que se genera presenta la siguiente estructura.
Elemento de campo de
Elementos: 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.
Campo de datos: 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.
Campo de página: Es un campo que se utiliza para filtrar los datos resumidos.
Elemento de campo de página: 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.
Campos de fila: Son los campos a los que se ha asignado una orientación de fila en la Tabla dinámica.
Campo de columna: Son los campos a los que se asignado una orientación de columna en la Tabla dinámica.
Rótulos de campos de la lista f Campo de Campo de Campo de datos Campo de fila Colocar los botones
de campos en los
Área de datos: 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.
La barra de herramientas de las Tablas dinámicas es la siguiente:
Nombre Botón Descripción
Tabla dinámica Presenta un menú con los
comandos de uso más frecuente en las tablas.
Asistente para Tablas
dinámicas Inicia el asistente para Tablas dinámicas que guía a través de la creación o modificación de una Tabla.
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.
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
Nombre Botón Descripción 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.
Actualizar datos Actualiza los datos de una
Tabla dinámica .
Seleccionar rótulo 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.
Seleccionar rótulos y
datos 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-
Seleccionar datos de una tabla dinámica
Se pueden seleccionar algunas partes de la Tabla dinámica a las que se desea dar formato.
Para utilizar una selección de la Tabla dinámica, activar el comando Habilitar selección, en el submenú Seleccionar del menú Tabla dinámica.
Para seleccionar: Hacer
Una tabla dinámica
completa Dar un clic en el botón Tabla dinámica
Elegir Seleccionar Toda la tabla Todos los rótulos de
elemento de un campo Dar un clic en el botón de Campo
Datos con los rótulos Dar un clic en el botón
Seleccionar rótulos y datos
Únicamente rótulos Dar un clic en el botón
Seleccionar rótulos
Datos sin rótulos Dar un clic en el botón
Seleccionar datos Todas las apariciones de un
elemento Dar clic una vez en el elemento
Formato de una Tabla dinámica
1. Seleccionar la parte de la tabla dinámica a la que se desea dar formato.
2. Con los botones de la barra de Formato o en el menú Formato, aplicar los cambios que se requieran.
3. Para configurar las opciones que afecten al formato de la Tabla dinámica, elegir una celda de la tabla, en el menú Tabla dinámica elegir Opciones y marcar las opciones que se deseen.
Actualizar una Tabla dinámica
Excel cuenta con varias opciones para controlar el momento y el modo en que se actualizan los datos que constituyen una tabla dinámica, así como para cambiar los datos que se incluyen en la misma.
Si los datos fuente se encuentran en Excel y se agregan nuevas filas o columnas de datos, se puede cambiar el rango de datos fuente para que se incluyan las filas o columnas adicionales. ♦
♦ En las Tablas dinámicas que la fuentes de datos es externa, al hacer una actualización, se incluyen las nuevas filas o registros.
Actualizar los datos en una Tabla dinámica
1. Seleccionar una celda en la Tabla dinámica
2. En la barra de herramientas Tabla dinámica, hacer clic en Actualizad datos
Para actualizar los datos, siempre que se abra el libro, dar clic en Opciones en el botón Tabla dinámica y activar la casilla Actualizar al abrir.
Para actualizar todas las Tablas dinámicas del libro, hacer clic en Actualizar datos en el botón Tabla dinámica.
Cambiar los datos fuente para incluir datos o campos
adicionales
1. Hacer clic en una celda de la Tabla dinámica.
2. En la barra de herramientas de Tabla dinámica, hacer clic en el Asistente para Tablas dinámicas.
3. En el paso 3 de 4, dar clic en el botón ATRÁS.
4. En el paso 2 de 4, seleccionar el nuevo rango de datos de origen que incluya las filas o columnas adicionales.
5. Hacer clic en SIGUIENTE.
6. En el paso 3 de 4, cambiar la Tabla dinámica para que incluya los nuevos campos, si es necesario.
Eliminar una Tabla dinámica
1. Seleccionar toda la tabla2. En ele menú Edición, elegir Borrar y dar un clic en Todo.
Ejercicio IV
1. ¿Qué es una Tabla dinámica?
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 de
página
Campo de datos
Área de datos
4. ¿Cómo se elimina una Tabla dinámica?
Funciones
Las funciones son fórmulas predefinidas que ejecutan cálculos con valores específicos, denominados argumentos, en un orden determinado. Los argumentos pueden ser números, texto, valores lógicos, como VERDADERO, FALSO, o referencias de celdas.
La sintaxis de una función inicia con el nombre de una función, seguida de un paréntesis de apertura, los argumentos de la función separados por comas (,) y un paréntesis de cierre. Si la función inicia la fórmula escribir un signo de igual (=) antes del nombre de la misma.
Funciones para bases de datos
Cuando es necesario analizar si los valores de una lista cumplen una condición determinada, o criterio, pueden utilizarse las funciones de bases de datos de la hoja de cálculo.
Por ejemplo en una lista que contiene información acerca de ventas, se pueden contar todas las filas o registros cuyo importe de ventas sea mayor a $1,000 pero menor a $2,500.
Algunas funciones de bases de datos tienen nombres que comienzan con las letras “BD”. Estas funciones también conocidas como funciones BD, tienen tres argumentos:
Sintaxis
B D S U M A ( A 1 : D 4 5 , V e n t a s , F 1 : H 2 )
Base_de_datos
Es el rango que contiene la lista. Debe incluirse la fila que contenga los rótulos de columna. La referencia se puede introducir como un
rango de celdas o como un nombre que represente el rango que contiene la lista.
Nombre_de_campo
Indica la columna que contiene los valores para realizar el calculo. La columna de datos, debe tener un rótulo que la identifique, puede ser una cadena de texto encerrado entre comillas, por ejemplo, “Ventas”, o como un número que representa la posición de la columna, 1 para la primera columna; 2 para la segunda y así sucesivamente.
Criterios
Es una referencia a un rango de celdas que especifican condiciones de una función. El rango de criterios, incluye una copia del rótulo de las columnas que se desean resumir con la función.
Sugerencias
Cualquier rango se puede usar como argumento de criterios, siempre que incluya por los menos un nombre de campo y una celda debajo del nombre para especificar un valor de comparación. Aunque el rango de criterios puede ubicarse en cualquier parte de la hoja de cálculo, se recomienda no colocarlo debajo de la lista. Asegurarse de que el rango de criterios no se superpone al de la lista.
Para realizar una operación en toda una columna de la base de datos, introducir una línea en blanco debajo de los nombres de campo en el rango de criterios.
Columna en la cual se realiza la operación (Nombre de camp Función
A continuación se describen las funciones más usuales.
Rango de it i Rango de la
lista (B d d t
BDCONTAR
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.
BDCONTARA
=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
=BDMAX(petreo,3,CRITER)
Devuelve el valor máximo de las entradas seleccionadas de una base de datos que coinciden con los criterios.
BDMIN
=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
=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
=BDPROMEDIO(petreo,3,CRITER)
Devuelve el promedio de las entradas seleccionadas de una base de datos que coinciden con los criterios.
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
Funciones de fecha hora
Mediante estas funciones se pueden analizar los valores de fecha - hora y trabajar con ellos en las fórmulas. Por ejemplo, si se necesita utilizar la fecha actual en una fórmula, con la función HOY, se presenta la fecha actual basándose en el reloj del equipo.
En Excel se utiliza el sistema de fecha 1900, un número de serie corresponde a fechas comprendidas entre el 1 de enero de 1900 y el 31 de diciembre de 9999.
Las fechas se almacenan como números secuenciales denominados valores de series y las horas como fracciones decimales, ya que la hora se considera como una porción de un día. Las fechas y horas son valores y, por tanto, se pueden sumar, restar e incluirse en otros cálculos. Por ejemplo, para determinar el número de días que hay entre dos fechas, puede restarse una fecha de otra.
En los números de serie, los dígitos a la derecha del separador decimal representan la hora; los números a la izquierda representan la fecha. Por ejemplo, el número de serie 367,5 representa la combinación de fecha y hora 12:00 p.m., 1 de enero de 1901.
AHORA()
Devuelve el número de serie de la fecha y hora actuales. Ejemplos
Si se está utilizando el sistema de fechas 1900 y el reloj integrado del equipo está ajustado a las 13:21, del 22 de noviembre de 1999.
AHORA() es igual a 36486.55677 Diez minutos más tarde:
AHORA() es igual a 36486.55625 Año
Devuelve el año correspondiente a núm_de_serie. El año se expresa como un entero entre 1900 y 9999.
=AÑO(núm_de_serie)
Núm_de_serie es el código de fecha - hora que Excel usa para los cálculos. El argumento núm_de_serie puede expresarse en forma de texto, por ejemplo "15-abr-1993" ó "15-4-93", en lugar de expresarse
como número. El texto se convierte automáticamente en un número de serie. Ejemplos AÑO("30-11-99") es igual a 1999 AÑO(0,007) es igual a 1900 AÑO(29747,007) es igual a 1981 Día =DIA(núm_de_serie)
Devuelve el día del mes correspondiente a núm_de_serie. El día se expresa como un número entero comprendido entre 1 y 31.
Ejemplos DIA("4-ene") es igual a 4 DIA("15-abr-1993") es igual a 15 DIA("11-8-93") es igual a 11 Días 360 =DIAS360(fecha_inicial;fecha_final)
Calcula el número de días entre dos fechas basándose en un año de 360 días (doce meses de 30 días) que se utiliza en algunos cálculos contables.
Fecha_inicial y fecha_final son las dos fechas entre las que se desea calcular el número de días.
Ejemplo
DIAS360("30-1-93"; "1-2-93") es igual a 1
Si la celda D10 contiene la fecha 30-1-93 y la celda D11 contiene la fecha 1-2-93, entonces:
DIAS360(D10; D11) es igual a 1 Diasem
Devuelve el día de la semana correspondiente al argumento núm_de_serie. El día se devuelve como un número entero entre 1 (domingo) y 7 (sábado).
Núm_de_serie es el código de fecha-hora que Excel usa para los cálculos de fecha y hora; núm_de_serie 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.
Ejemplos
DIASEM("2-14-90") es igual a 4 (miércoles) DIASEM(29747,007) es igual a 4 (miércoles)
Devuelve la hora correspondiente al argumento núm_de_serie. La hora se expresa como un número entero, comprendido entre 0 (12:00 A.M.) y 23 (11:00 P.M.).
Hora
=HORA(núm_de_serie)
El argumento núm_de_serie 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
Funciones lógicas
Se utilizan para ver si una condición es cierta o falsa o, para comprobar varias condiciones.
O
=O(valor_lógico1;valor_lógico2;...)
Devuelve VERDADERO si alguno de los argumentos es VERDADERO; devuelve FALSO si todos los argumentos son FALSOS.
Valor_lógico1; valor_lógico2;... 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 #¡VALOR!.
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
=SI(prueba_lógica;valor_si_verdadero;valor_si_falso)
Devuelve un valor si la condición especificada es VERDADERO y otro si dicho argumento es FALSO.
Prueba_lógica es cualquier valor o expresión que se puede evaluar como VERDADERO o FALSO.
Valor_si_verdadero es el valor que se presenta si prueba_lógica es VERDADERO.
Valor_si_falso es el valor que se presenta si prueba_lógica es FALSO. Ejemplo
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.
En este ejemplo, Si el valor de C2 es mayor que B2 se escribe la palabra SUR si es falso se presenta NORTE
Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas.
Y
=Y(valor_lógico1;valor_lógico2;...)
Devuelve VERDADERO si todos los argumentos son VERDADEROS; devuelve FALSO si uno o más argumentos son FALSOS.
Valor_lógico1; valor_lógico2;... son de 1 a 30 condiciones que se desean comprobar y que pueden ser VERDADERO o FALSO.
Ejemplo
=Y(VERDADERO;FALSO) es igual a FALSO =Y(2+2=4;2+3=5) es igual a VERDADERO Si B4 contiene un número entre 1 y 100, entonces =Y(1<B4;B4<100) es igual a VERDADERO
Funciones Estadísticas
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
=PROMEDIO(Número1;Número2...)
Devuelve la media aritmética de los argumentos.
Número1; Número2.... 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
=MODA(Número1, Número2....)
Devuelve el valor que se repite con más frecuencia en un rango de datos.
Número1, Número2... son entre uno y treinta argumentos para calcular la moda.
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 error #NA.
Ejemplo
=MODA(10,12,10,5,10,4,3,10) es igual a 10
Mediana
=MEDIANA(Número1, Número2....)
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.
Número1, Número2... son entre uno y treinta argumentos para calcular la moda.
Ejemplo
Funciones de texto
Mediante estas funciones se pueden manipular cadenas de texto en fórmulas. Por ejemplo, quitar espacios, cambiar mayúsculas por minúsculas.
Espacio
=ESPACIOS(TEXTO)
Elimina los espacios del texto, excepto el espacio normal que se deja entre palabras.
Texto es el texto en donde se desea quitar el espacio. Ejemplo
=ESPACIOS(“ Ganancias primer trimestre “) es igual a “Ganancias primer trimestre”
Mayus
=MAYUSC(TEXTO)
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 Minus
=MINUSC(TEXTO)
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
Ejercicio V
1. ¿Qué es una función?
2. ¿Qué utilidad tienen las funciones para bases de datos?
3. Indicar las partes de la siguiente función
= B D C O N T A R ( A 2 : D 6 8 , 3 , F 1 :
I 2 )
¿Qué función se debe utilizar para definir el número de días que hay entre dos fechas?
¿Qué función se utiliza para definir si una condición es FALSA o VERDADERA?
4. Mencionar algunas de las funciones estadísticas
5. ¿Qué utilidad tienen las funciones de texto?
Macros:
automatización de tareas de uso frecuenteCuando se realiza una tarea frecuentemente, en Excel, se puede automatizar mediante una macro.
Una macro consiste en una serie de comandos y funciones que se almacenan en un módulo de Visual Basic y que puede ejecutarse siempre que sea necesario realizar la tarea. Una macro se graba igual que se graba música en un casete. A continuación, se ejecuta la macro para que repita los comandos.
Antes de grabar o escribir una macro, es recomendable planificar los pasos y los comandos que va a ejecutar. Si se comete algún error al grabarla, las correcciones que se realicen se graban también, esto en ocasiones genera errores en la ejecución.
Con el Editor de Visual Basic, se pueden modificar, copiar de un módulo a otro, copiar macros entre diferentes libros, cambiar de nombre a los módulos que almacenan las macros o cambiar de nombre a las macros.
Grabar una macro
1. Seleccionar Macro en el menú Herramientas y, a continuación, hacer clic en Grabar.
2. El cuadro de diálogo es el siguiente
En el cuadro Nombre de la macro, escribir un nombre para la macro. El primer carácter del nombre debe ser una letra. Los demás caracteres pueden ser letras, números o caracteres de subrayado. No se permiten espacios; puede utilizarse un carácter de subrayado como separador de palabras.
En el Método abreviado, escribir una letra en el cuadro que se encuentra frente de CTRL, la letra puede ser cualquier tecla del teclado, excepto los números y caracteres especiales. La tecla de método abreviado sustituyen a cualquier tecla de método abreviado predeterminada en Excel mientras esté abierto el libro que contiene la macro.
En el cuadro Guardar macro en, hacer clic en la ubicación en que desea almacenar la macro. Si se desea que la macro esté disponible siempre que se utilice Excel, almacenarla en el libro de macros personales en la carpeta INICIAR.
En el cuadro Descripción, escribir lo que hace la macro. 3. Hacer clic en Aceptar.
Si al grabar una macro, se seleccionan celdas, al ejecutar la macro, se vuelven a seleccionar las mismas celdas. Ya que se graban como referencias. Si se desea tener una macro para seleccionar celdas independientemente de la posición que tenga la celda activa cuando se ejecute la macro, configurar el grabador de macros para que grabe referencias relativas de celda.
En la barra de herramientas Detener grabación, hacer clic en Referencia. Excel continua grabando macros con Referencias relativas hasta que se termine la sesión o hasta que se haga clic otra vez en Referencias relativas.
4. Ejecutar las acciones que se desean grabar.
5. Al finalizar, en la barra de herramientas Detener grabación, hacer clic en Detener grabación.
Ejecutar una macro
Una vez grabada, una macro se puede ejecutar en Excel o en el Editor de Visual Basic. Normalmente, se ejecuta en Excel.
Ejecutar una macro en Microsoft Excel
1. Abrir el libro que contiene la macro.2. Seleccionar Macro en el menú Herramientas y, a continuación, hacer clic en Macros.
3. En el cuadro Nombre de la macro, escribir el nombre de la macro que se desea ejecutar.
4. Hacer clic en Ejecutar.
Nota Para interrumpir una macro antes de que finalice las acciones, presionar ESC.
Ejecutar una macro desde un módulo de Visual Basic
1. Abrir el libro que contiene la macro.2. Seleccionar Macro en el menú Herramientas y, a continuación, hacer clic en Macros.
3. En el cuadro Nombre de la macro, escribir el nombre de la macro que se desea ejecutar.
4. Hacer clic en Modificar. 5. Hacer clic en Ejecutar sub.
Sugerencia: Si se desea ejecutar otra macro diferente mientras se está utilizando el Editor de Visual Basic, hacer clic en la opción Macros en el menú Herramientas. En el cuadro Nombre de la macro, escribir el nombre de la macro que se desea ejecutar y, a continuación, hacer clic en Ejecutar.
Estructura de un módulos de Visual Basic, esta macro pone un borde azul en el contorno de la selección.
Ejecutar una macro desde una imagen u objeto
Para evitar el ejecutar la macro, desde el menú Herramientas, 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. 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. 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.
Inicio Descripció
Desarrollo
