caracterÍsticas de excel vistas en las … · lo que en realidad se almacena en b23 es 0,16....
TRANSCRIPT
El documento Introducción a Excel 2003.pdf contiene la teoría básica de Excel.
En el siguiente documento, se especifica para cada una de las prácticas realizadas en clase, las
opciones, funciones, … de Excel que hemos tenido que utilizar. Como muchas de ellas se utilizan en
múltiples prácticas (ej: aplicación de formatos, bordes, fórmulas, funciones, …), se estudiará en
profundidad en la primera en la que aparece y en las siguientes, sólo se hará referencia a la misma ó sólo se
verá lo que la práctica tiene de novedoso.
PRÁCTICA 1: FORMATOS DE CELDA Y RELLENAR SERIES.
1.- Formatos de celda.
Al seleccionar determinadas celdas y elegir Formato Celdas…, obtenemos el cuadro de diálogo
de la figura pudiendo modificar el formato de los números que se representan en las celdas, la alineación de
su contenido, las características de la fuente, los bordes, los colores de relleno (tramas) y la posibilidad de
proteger u ocultar las celdas.
a) Número.
En cuanto a las características del formato de los números, se puede elegir alguna de las categorías
de las mostradas en la figura: General,
Número, Moneda, Contabilidad, etc. Según
la categoría, se pueden modificar distintos
aspectos. En la figura, p.ej., en la categoría
Número se puede indicar cuántas
posiciones decimales tendrán los números
representados, si se usará ó no el separador
de miles (.) y cómo se representarán los
números negativos. En la barra de
herramientas Formato tenemos el botón
para fijar formato de miles y los botones
, para aumentar o disminuir
decimales.
Al elegir las categorías Moneda ó
Contabilidad, se puede elegir igualmente
número de posiciones decimales y el
símbolo monetario que se representará. En
la barra de herramientas Formato, existe el
botón , que aplica el estilo Moneda a las
celdas y el botón para fijar el formato
de euros.
Al elegir las categorías Fecha u Hora¸ se puede elegir de una lista el modo de representar éstas, si el
contenido de las celdas son fechas u horas, para una configuración regional determinada que también se
puede modificar (en nuestro caso: Español).
Al elegir la categoría Porcentaje, indicaremos las posiciones decimales. En este caso, el contenido
de las celdas se multiplicará por 100 y se añade el símbolo %. En la barra de herramientas también existe el
botón , que aplica dicho formato a las celdas seleccionadas. Hay que tener cuidado al utilizar estas
celdas en las fórmulas ya que, al calcular un tanto por ciento, si éstas celdas ya tienen el formato de
porcentaje, no será necesario dividir por 100.
Al elegir la categoría Fracción, se pueden escribir fracciones en las celdas, con un dígito en el
numerador y denominador, con dos dígitos, con tres, ó según otros tipos de los posibles que se pueden
elegir.
CARACTERÍSTICAS DE EXCEL VISTAS EN LAS PRÁCTICAS
Al elegir la categoría Científica, podemos indicar el número de decimales.
En la categoría Texto no se define ninguna característica para los datos tipo texto.
En la categoría Especial, se pueden indicar tipos característicos para datos en las celdas (código
postal, teléfono, …).
En la categoría Personalizada, se puede definir un formato partiendo de alguno ya definido, usando
unos caracteres especiales para indicar la máscara que deben seguir los datos.
b) Alineación.
En la ficha Alineación se puede
elegir la alineación, horizontal y vertical
del contenido de las celdas, dentro de las
mismas, ajustar el texto a la celda, reducir
ésta hasta ajustar, combinar varias celdas
seleccionadas, elegir la dirección del texto
y la orientación del mismo. En la barra de
herramientas Formato tenemos el botón
que permite combinar las celdas y
centrar el contenido en las mismas.
También tenemos otros botones para
modificar la alineación (horizontal) de las
celdas. Todas estas opciones aparecen en
la figura adjunta.
c) Fuente.
En la ficha Fuente, tenemos
opciones para cambiar las características
de caracteres escritos en las celdas, según
las opciones que aparecen en la figura:
tipo de letra, tamaño, estilo, tipo de
subrayado, color y efectos (tachado,
superíndice ó subíndice). También se
pueden realizar muchas de estas opciones
a través de la barra de herramientas
Formato.
d) Bordes.
En la ficha Bordes podemos añadirle bordes a las celdas seleccionadas, pudiendo elegir la posición
del borde, estilo y color.
e) Tramas.
Aquí podemos elegir el color de relleno de las celdas seleccionadas o el tipo de trama.
f) Proteger.
Excel permite proteger un gran número de características de las hojas de cálculo, de modo que los
usuarios que las utilicen no puedan realizar cambios en las fórmulas, formato, etc. ya sea de manera
intencionada o por error.
Para proteger una hoja ejecutamos la opción de menú Herramientas Proteger Proteger hoja y
veremos la ventana que muestra la figura. Es necesario
marcar la opción Proteger hoja y contenido de celdas
bloqueadas y elegir una contraseña para que no pueda
desbloquearla cualquiera, aunque no es obligatorio.
También podemos seleccionar un gran número de
opciones que permitan o denieguen el efectuar
determinadas acciones a los usuarios.
Una vez protegida la hoja, podemos desprotegerla
si conocemos la contraseña y ejecutamos Herramientas
Proteger Desproteger hoja.
Antes de proteger una hoja podemos introducir dos
tipos de protección en las celdas, desde la ficha Proteger
de la ventana Formato de celdas. Como podemos observar
tenemos dos posibilidades:
Celda bloqueada. Evita que la celda se
modifique, desplace, cambie de tamaño o
elimine.
Celda oculta. Oculta una
fórmula en una celda de modo
que no aparezca en la barra de
fórmulas al seleccionar la
celda.
Ambas opciones no serán efectivas
a menos que, con posterioridad a su activación, protejamos la hoja como se ha visto antes.
2.- Rellenar series.
A veces es interesante, cuando se trata de escribir series predefinidas (p.ej., los días de la semana,
los meses, …) utilizar las ventajas que ofrece Excel en este sentido. En
algunos casos, escribimos el primer término de la serie (ej: Lunes) y
arrastrando del botón de autollenado (cuadradito en la esquina inferior derecha
de la celda), conseguimos rellenar el rango deseado.
En otros casos, cuando se trata de series numéricas, escribimos los dos
o tres primeros términos de la serie y, habiéndolos seleccionado, arrastramos
en el sentido deseado del botón de autollenado. Ej. para crear una serie con los
números impares :
La series numéricas que se crean por defecto de esta forma, son progresiones aritméticas o lineales,
es decir a cada número se le suma la diferencia que hay entre los dos primeros que hemos escrito.
Para crear series geométricas (cada número multiplicado por una cantidad) u otro tipo de series, es
necesario acceder al menú Edición Rellenar Serie… Obtenemos el cuadro de la figura:
Es conveniente escribir el primer término de
la serie. En caso de que sea un número, aparecerá el
cuadro tal como en la figura. Si se trata de una
fecha, elegiremos el tipo de serie cronológica y
podemos elegir escribir días laborables, meses o
años.
Podemos elegir filas o columnas para
rellenar, el tipo de serie, el incremento de una celda
a otra y el límite (último dato de la serie).
En caso de que la serie no siga ninguno de
los tipos que aparecen en la figura, escribiremos los dos o tres primeros datos y elegimos Tendencia. Excel
adaptará los datos a la tendencia que siguen los primeros, ej:
En este caso, la tendencia es dividir por 5 por lo que hay que escoger el tipo Geométrica y Tendencia.
3.- Eliminar líneas de división.
Como otras muchas opciones de Excel, en Herramientas Opciones Ficha ver podemos elegir
qué elementos
queremos ver
en las hojas u
ocultarlos. Uno
de ellos es las
líneas de
división.
Desmarcando
esta opción,
éstas no
aparecerán.
PRÁCTICA 2: RELLENAR SERIES.
Existen series o listas que no están definidas en Excel. Por ese motivo, cuando escribimos los
primeros términos y arrastramos, no se rellenan las celdas de modo automático. El usuario puede añadir
nuevas listas para tenerlas disponibles en todo momento. Accediendo a Herramientas Opciones, en la
ficha Listas personalizadas tenemos la posibilidad de Agregar una nueva lista a las ya disponibles, p.ej.,
Fecha, Concepto, Debe, Haber, Importe, IVA.
PRÁCTICA 3: AUTOSUMAS.
Seleccionando un rango de datos y pulsando el botón de Autosuma disponible en la barra de
herramientas Estándar, se suman los elementos del rango. Automáticamente se inserta en la celda contigua
la función =SUMA(rango).
También podemos situarnos en la celda donde queremos obtener la suma y pulsar el botón
Autosuma. Posteriormente seleccionamos el rango a sumar.
La sintaxis de la función SUMA es SUMA(número1; número2; …), es decir el número de
argumentos que admite no está limitado a un único rango. De esta forma podemos sumar varias celdas
(separadas por punto y coma) ó sumar varios rangos, aunque no estén contiguos. Sería un error escribir
SUMA(A1+B2+B4+…), aunque Excel lo permite porque al final lo que tenemos es SUMA(un número).
PRÁCTICA 4: FÓRMULAS Y FUNCIONES.
En esta práctica se utiliza la función SUMA explicada anteriormente y fórmulas para calcular
importes (productos de celdas), ej: =B9*A10 (suponiendo en A10) el precio unitario de un producto y en
B9 el número de unidades.
Para calcular importe de IVA suponiendo éste en una celda, ej: B23 en formato de porcentaje, de la
forma 16%, hay que tener en cuenta que hay que multiplicar el importe por B23 (y no dividir por 100, pues
eso ya va implícito en el formato de porcentaje). Lo que en realidad se almacena en B23 es 0,16.
También hay que considerar si al utilizar alguna celda en la fórmula, ésta debe permanecer fija para
poder arrastrar la fórmula a celdas adyacentes. Si es así, habrá que utilizar referencia absoluta a esa celda,
ej., $B$23.
PRÁCTICA 5: FUNCIONES Y FÓRMULAS.
En esta práctica se utiliza la función SUMA y fórmulas para sumar, restar, multiplicar o dividir
celdas. En esta caso, será necesario utilizar los operadores adecuados (+, -, * y /).
La función SI
Esta función es utilizada en otras prácticas más complejas. En ésta, se utiliza para comparar dos
celdas e indicar VERDADERO o FALSO en el caso de que la comparación sea verdad ó no.
La sintaxis de la función SI es: SI(prueba_lógica; valor_si_verdadero; valor_si_falso). Esta
función comprueba la condición indicada en el primer argumento y devolverá el valor indicado en el
segundo argumento si la condición es verdadera ó el valor indicado en el tercer argumento si aquella era
falsa.
Para la prueba lógica se utilizan los operadores de comparación: > (mayor), < (menor), = (igual), <>
(distinto), <= (menor o igual) y >= (mayor o igual). P.ej., =SI(A3>=B3; Verdadero; Falso), comprueba si el
valor de la celda A3 es mayor o igual que el de la celda B3. En caso de que sea cierto, la función devolverá
Verdadero. En otro caso, devolverá Falso.
PRÁCTICA 6: SUMAS.
En esta práctica se realizan sumas (función SUMA) sobre distintos rangos, a veces no contiguos.
También se le asignan nombres a los rangos:
Nombrar rangos
Podemos nombrar un rango, ej., A3:B7 mediante un nombre como DATOS y al utilizarlo en las
fórmulas, indicar el nombre en vez del rango, ej., =SUMA(DATOS) en vez de =SUMA(A3:B7).
Para nombrar un rango, se selecciona éste y en el cuadro de nombres se
indica el nombre para el mismo.
También en Insertar Nombre Definir, podemos realizar la
operación anterior indicando el nombre y el rango de celdas al que se la aplica.
Pulsando el botón Eliminar en este cuadro,
podemos borrar un nombre elegido.
PRÁCTICA 7: SUMAS (Presupuesto para empresa)
En esta práctica se utiliza la función SUMA y otras fórmulas para calcular porcentajes, los cuales se
encuentran en otras celdas. Estas celdas deben permanecer fijas al arrastrar las fórmulas por lo que al hacer
referencia a ellas, se utilizarán referencias absolutas (de la forma $B$13), no relativas.
PRÁCTICA 8: SUMAS, FORMATO DE FILAS Y COLUMNAS Y ALINEACIÓN DE CELDAS.
En esta práctica se utiliza la función SUMA y la alineación de celdas ya vista anteriormente en
Formato Celdas.
Formato de filas y columnas
Si accedemos a Formato Fila ó Formato Columna, tenemos la posibilidad de modificar el
Alto de la fila ó el Ancho de la columna, además de otras opciones como: Autoajustar la fila o columna al
contenido, Ocultar ó Mostrar la fila o columna elegida.
Una vez que se oculta una fila o columna, para mostrarla es necesario seleccionar al menos una
celda de la misma. Lo más fácil es escribir una celda de la misma en el cuadro de nombres, con lo cual
accedemos a ella, y una vez ahí, seleccionar la opción Mostrar.
PRÁCTICA 9: FACTURA.
En esta práctica (Factura Valtronic) se utilizan muchas de las características vistas anteriormente:
formato de celdas, combinar celdas, ocultar líneas de división, alineación de celdas, alto de filas, ancho de
columnas, fórmulas que calculan importes (precio * cantidad), sumas, cálculo de IVA haciendo una
referencia absoluta a la celda donde está el tanto por ciento a aplicar, …
Lo novedoso de esta práctica es:
1. Inserción de imágenes.
Igual que en Word, para insertar una imagen elegimos Insertar Imagen Imagen prediseñada…
(en este caso). La imagen no se ajusta a ninguna celda si no que se puede colocar donde se desee.
2. Aplicar formato condicional.
Para que los datos de un rango se muestren con un formato u otro según una condición establecida
(ej: su valor menor o igual que otro), hay que definir un formato condicional. En Formato Formato
condicional tenemos el cuadro que se muestra en la figura.
Pulsando el botón Agregar>> podemos definir las condiciones que queramos. Una condición se
suele expresar de la forma Valor de la celda Operador de comparación Valor. Las opciones disponibles
aparecen al desplegar la lista correspondiente. Una vez que hemos definido la condición, pulsamos el botón
Formato… y obtenemos un cuadro donde podemos modificar las características de la fuente, los bordes o
las tramas.
Cuando los valores en las celdas cumplan alguna de las condiciones especificadas, se aplicará el
formato definido.
3. Inserción y modificación de gráficos.
Los gráficos son herramientas muy útiles, ya que, con un simple vistazo, permiten sacar
conclusiones acerca de una serie de valores. Excel permite crear una amplia gama de gráficos diferentes
para representar cualquier conjunto de datos introducidos en un libro.
Aunque la creación de gráficos se realiza con ayuda de un Asistente que suministra la información
necesaria, es conveniente conocer algunos conceptos relacionados con los gráficos:
Se denomina Serie a cada conjunto de datos de la misma naturaleza. En la mayoría de los gráficos
pueden existir varias series: una de ellas se representará en el eje X, y el resto, en el eje Y, de modo que
proporcionarán los valores necesarios para dibujar los elementos del gráfico (barras, cilindros, conos,
líneas, etc). Los elementos de un gráfico son:
Título del gráfico
Líneas de división
Título del eje
Eje vertical Leyenda
Eje horizontal
Elementos que representan las series de valores
Para crear un gráfico, se seleccionan los datos a representar y se elige Insertar Gráfico ó bien, se
pulsa el botón de la barra de herramientas. También se puede insertar el gráfico y elegir los datos en el
momento de su creación (paso 2 del asistente).
En el primer paso del asistente se elige el tipo de gráfico.
En el segundo paso se eligen los datos de origen (si previamente no se han escogido):
Se puede modificar de
forma global el rango de datos
e invertir la situación de las
series de datos (filas o
columnas).
La ficha Serie permite modificar de modo individual el rango de datos. Se pueden agregar o
eliminar series de valores, escribir o seleccionar un dato para que se utilice como nombre de la serie activa
(leyenda), modificar los valores para la serie activa, o modificar la serie de categorías para el eje X.
En el paso 3 del asistente, se pueden escribir los títulos del gráfico y los ejes, así como otras
opciones:
La ficha Eje permite dibujar u ocultar los ejes del gráfico.
La ficha Líneas de división, agrupa opciones para activar o desactivar las diferentes líneas de
división.
La ficha Leyenda permite activar o desactivar la visualización de las leyendas, y en caso de estar
activada, elegir su posición.
La ficha Rótulos de datos permite mostrar rótulos dentro del gráfico, ya sea el nombre de la
serie, la categoría o el valor.
La ficha Tabla de datos permite mostrar una tabla adyacente al gráfico con los datos que
corresponden al mismo.
En el último paso del asistente, se elige la ubicación para el gráfico (la misma hoja u otra nueva).
Una vez esté el gráfico insertado, se pueden modificar sus características de formato, datos, tipo, …
a través de las opciones del menú Gráfico o bien a través del menú contextual de cada uno de los
elementos del gráfico (ejes, leyenda, títulos, series, etc.). Así, p.ej., podemos cambiar las formas de las
barras, la perspectiva (si se trata de un gráfico en 3D), la fuente de los títulos, tamaño, …, los colores, la
alineación de los ejes, agregar más datos, modificar los datos de origen, etc. Las modificaciones pueden ser
múltiples.
PRÁCTICA 10: RESOLUCIÓN DE ECUACIONES DE SEGUNDO GRADO.
En esta práctica se utiliza la fórmula para resolver una ecuación de segundo grado. Si tenemos la
ecuación 02 cbxax , ésta tiene dos soluciones según la siguiente fórmula:
a
acbbx
2
42
En la práctica se calcula primero acb 42 (radicando), en otra celda se calcula la raíz y en otras dos
se calculan las dos soluciones, respectivamente.
También se utiliza la función SI para comprobar si el radicando es <0 ó no. En caso de que sea <0
(negativo) aparecerá el mensaje “La ecuación tiene solución compleja”. En caso contrario, aparecerá “La
ecuación tiene solución real”.
Como novedad en esta práctica, tenemos:
Configurar páginas. Definir encabezados y pies de página.
Al elegir Archivo Configurar página, obtenemos el cuadro:
Se pueden definir características para la impresión de las páginas: orientación, escala, tamaño del
papel, calidad de impresión, primer número de página.
Si se elige la ficha Márgenes, podemos configurar los márgenes superior, inferior, izquierdo y
derecho, así como si se desea centrar la página horizontal o verticalmente.
Si se elige la ficha Encabezado y pie de página, podemos escribir encabezados o pies, pudiéndolos
elegir ambos de una lista definida o bien personalizarlos a nuestro gusto pulsando en el botón Personalizar
correspondiente. En este caso, aparece el cuadro de la segunda figura que se muestra a continuación.
Aquí se definen 3 secciones: izquierda, central y derecha, pudiendo el usuario escribir lo que desee
en cada una de las secciones, o bien, pulsar alguno de los botones disponibles que nos permitirán insertar
distintos elementos en los encabezados y pies: número de página, número de páginas, fecha, hora, nombre
del archivo, etc. También aparece un botón (el primero) para definir las características de los caracteres que
se escriban en los encabezados y los pies de página.
PRÁCTICA 11: REPRESENTACIONES GRÁFICAS (Venta de ordenadores).
En esta práctica se crean y modifican gráficos generado a partir de unos datos referentes a la venta
de ordenadores. Ya se ha visto anteriormente lo referente a la creación de gráficos.
PRÁCTICA 12: FUNCIONES ESTADÍSTICAS (Incendios forestales).
En esta práctica se utilizan las siguientes funciones de la categoría estadísticas:
CONTAR(ref1; ref2; …): si indicamos una lista de números, los cuenta. Si se indica un rango,
cuenta las celdas que contienen números. En la práctica, contaríamos las celdas que contienen números en
el rango de datos dado en un principio.
MIN(número1; número2; …): Devuelve el valor mínimo de una lista de valores (omite los
textos o valores nulos). Si se indica un rango, devuelve el valor mínimo del mismo.
MAX(número1; número2; …): Devuelve el valor máximo de una lista de valores o de un rango.
SUMA(…)
PROMEDIO(número1; número2; …): Devuelve la media aritmética de los valores o rango de
valores pasados como argumentos.
MEDIANA(número1; número2; …): Devuelve la mediana o el valor central de un conjunto de
valores o un rango.
MODA(número1; número2; …): Devuelve el valor más frecuente o que más se repite en un
rango de datos.
VAR(número1; número2; …): Devuelve la varianza de un conjunto de valores. Tanto la
varianza como la desviación típica o estándar, son medidas de la distancia que hay entre los datos y su
media aritmética.
DESVEST(número1; número2; …): Devuelve la desviación estándar de un conjunto de
valores.
Aparte de estas funciones estadísticas, en esta práctica también es necesario realizar distintos
gráficos.
PRÁCTICA 13: REFERENCIAS MIXTAS.
Las referencias a las celdas de la forma A2 ó B5 (las más habituales), son referencias relativas. Al
copiar una fórmula tal como =A3*B4, se modifican fila o columna según hacia dónde arrastramos el botón
de autollenado o dónde copiemos la fórmula.
Para mantener fija una celda (fila y columna), hemos de usar referencias absolutas. Por ejemplo,
=$A$3*B4, hace que la celda A3 no varíe aunque copiemos esa fórmula en otras celdas. El $ delante del
nombre de la columna indica que esa columna no variará y el $ delante de la fila indica que esa fila
tampoco variará.
Si queremos mantener fija una columna y variar la fila (o mantener fija una fila y variar la
columna), hemos de usar referencias mixtas. Por ejemplo, $B2 indica que la columna B no variará pero la
fila sí puede hacerlo; B$2 indica que la columna puede variar pero la fila 2 no (estará fija).
Usando referencias mixtas, se pide realizar en esta práctica una tabla de multiplicar. La fórmula a
utilizar es =$A4*B$3.
PRÁCTICA 14: BÚSQUEDA DE OBJETIVOS.
de las funciones que hace pensar en las posibilidades que ofrece una hoja de cálculo es la búsqueda
de objetivos. Bajo este título se encuentra aquella operación que busca el valor de una variable que permite
alcanzar un resultado determinado en alguna expresión en la que participe.
En la práctica, se pide introducir los datos que se muestran en la figura. Sabemos que el número de
bacterias de cólera finales se obtiene a partir de la fórmula que figura en la celda B5 (número de bacterias
iniciales) por el número e (2,718282) elevado a 1,385 por el tiempo transcurrido. La función EXP devuelve
el resultado de elevar el número e a lo que se indique como argumento de la misma. Si queremos saber
cuánto tiempo tiene que transcurrir para que 5 bacterias se transformen en 82.593.200 bacterias, conocemos
el resultado y tenemos una incógnita que es el tiempo. Usamos la opción Herramientas Buscar objetivo,
como en el ejemplo para encontrar el valor que no conocemos:
En Definir la celda indicamos la celda en la que está la fórmula.
En Con el valor indicamos el valor que se quiere conseguir con la fórmula.
En Para cambiar la celda indicamos qué celda contiene el dato que el programa debe calcular (la
incógnita).
Aceptamos y el programa introducirá automáticamente en la celda B4 el dato a buscar.
Aparte de esta ecuación, en la práctica se pide resolver otras (sumas, productos, etc) utilizando
siempre la búsqueda de objetivos).
PRÁCTICA 15: LA FUNCIÓN SI.
Esta función ya ha aparecido en prácticas anteriores: SI(prueba_lógico; valor_si_verdadero;
valor_si_falso). En la práctica se utiliza para calcular el saldo en las cuentas de partida doble, sumando la
cantidad del haber o restando la cantidad del debe al saldo anterior de la forma:
=SI(A6<>” “; C5-A6; C5+B6), siendo A6 la celda del debe, B6 la celda del haber y C5 el saldo anterior.
PRÁCTICA 16: LA FUNCIÓN SI (Precios, Modelos de coches y Problemas)
En esta práctica se utiliza la función SI ya comentada anteriormente, para mostrar unos resultados u
otros en función de los valores de determinadas celdas.
PRÁCTICA 17: FUNCIONES BUSCAR, BUSCARV y BUSCARH.
Las funciones de la categoría búsqueda y referencia que se deben utilizar en esta práctica son:
BUSCAR(valor_buscado; matriz): Busca un determinado valor indicado como primer
argumento en la matriz indicada como segundo argumento, devolviendo el valor que corresponda al valor
buscado (recordar: premio para un número de puntos). Esta función es antigua. Se utilizan las dos
siguientes.
BUSCARV(valor_buscado; matriz; indicador_de_columna; ordenado): Busca el primer
argumento en la matriz que se indica como segundo argumento, devolviendo la expresión que corresponda
de la columna de la matriz que se indique en el tercer argumento (valor numérico para la columna: 1, 2, 3,
…). El último argumento es opcional. Por defecto se considera este argumento como VERDADERO, lo
cual quiere decir que los valores de la matriz donde vamos a buscar están ordenados. Si estuvieran
desordenados, habría que indicar FALSO en este argumento.
BUSCARH(valor_buscado; matriz; indicador_de_fila; ordenado): Busca el primer
argumento en la matriz que se indica como segundo argumento, devolviendo la expresión que corresponda
de la fila de la matriz que se indique en el tercer argumento (valor numérico para la fila: 1, 2, 3, …). El
último argumento es opcional. Por defecto se considera este argumento como VERDADERO, lo cual
quiere decir que los valores de la matriz donde vamos a buscar están ordenados. Si estuvieran
desordenados, habría que indicar FALSO en este argumento.
PRÁCTICA 18: FUNCIÓN BUSCARV.
Se utiliza esta función ya comentada anteriormente.
PRÁCTICA 19: FUNCIÓN BUSCARV.
Nuevamente utilizamos la función anterior, en su versión simple y combinada con la función SI de
la forma: =SI(B2=”SI”; BUSCARV(B1; …); 0).
Las funciones se pueden anidar, teniendo en cuenta que cuando utilicemos una función como
argumento de otra, a la que va anidada no hay que precederla de =).
Por último, en esta práctica se pide la realización de algunos gráficos.
PRÁCTICA 20: MÁS GRÁFICOS.
Partiendo de diversas tablas, en esta práctica se realizan diversos gráficos (de varios tipos). En
algunos casos habrá que modificar éstos.
PRÁCTICA 21: EL SUPERMERCADO SERRANO.
Esta práctica está dividida en diversas hojas. En la primera se registran los ingresos del
supermercado por semanas y sectores (es necesario utilizar la función SUMA). En la segunda, se registran
los gastos y en la tercera los beneficios (ingresos – gastos). Para este último cálculo, será necesario utilizar
referencias a celdas que están en otras hojas con lo cual hay que preceder el nombre de la celda por el
nombre de la hoja de la siguiente forma: =Ingresos!F26 – Gastos!B13 (es decir Nombre_hoja!celda).
Para finalizar la práctica, se pide realizar la representación gráfica de diversos datos.
PRÁCTICA 22: FUNCIONES.
En esta práctica se utilizan funciones matemáticas y estadísticas vistas anteriormente: SUMA,
MAX, MIN, PROMEDIO, CONTAR, y la función SI de la categoría lógicas.
Las funciones nuevas que se incluyen en la práctica son:
CONTAR.SI(rango; criterio): Cuenta las celdas en el rango indicado que cumplen el criterio
que se indica como segundo argumento. P.ej., =CONTAR(B3:B17; >600).
SUMAR.SI(rango; criterio; rango_suma): Suma las celdas del rango indicado como tercer
argumento, siempre que las celdas del rango indicado como primer argumento cumplan el criterio. Habrá
ocasiones en que ambos rangos coincidan y otras en que no.
Además de las funciones ya mencionadas, en esta práctica se utiliza el Formato Condicional (ya
visto en prácticas anteriores) y la creación de gráficos.
PRÁCTICA 23: CÁLCULO FINANCIERO.
En esta práctica se utilizan las fórmulas para calcular el capital final en el interés compuesto:
El capital final, para cualquier capital inicial, tanto por ciento y tiempo se calcula utilizando la
fórmula t
RcC
1001 , donde C = capital final, c = capital inicial, R = tanto por ciento y t = nº de años.
Capital compuesto revalorizado anualmente
Capital inicial Tanto por ciento Tiempo Capital final
c R t C
6.000,00 € 3 10
El interés compuesto es una forma de capitalización en la que los intereses que obtenemos al
finalizar un período se acumulan al capital, para producir nuevos intereses en el período siguiente.
El período de capitalización es el intervalo de tiempo al final del cual los intereses se acumulan al
capital. Este período de capitalización puede ser anual, semestral, trimestral o mensual.
Si la capitalización es anual, aplicamos la fórmula anterior para calcular el capital final.
Si la capitalización es n veces al año, la fórmula es:
nt
n
RcC
1001 , donde n es el número de
periodos de capitalización en un año.
Capital compuesto revalorizado n veces al año
Capital inicial Tanto por ciento Nº de capitalizaciones anuales Tiempo Capital final
c R n t C
30.000,00 € 3 12 10
En la práctica se calculará determinados capitales finales a partir de los demás datos y, en otros
casos, se indicará el capital final a alcanzar y se pedirá calcular el capital inicial, mediante búsqueda de
objetivos.
La segunda parte de la práctica consiste en el cálculo de Créditos e Hipotecas. Dado un crédito de
X euros, a un Y% de interés anua durante Z años, tenemos que determinar la cuota mensual que hay que
pagar, la cantidad de la misma que corresponde a capital amortizado y la que corresponde a intereses. Excel
dispone de tres funciones financieras que nos devuelven estos valores. Estas son, respectivamente:
=PAGO(Interés;Periodos;Capital); calcula la cantidad que debe pagarse por un préstamo
bancario de una cantidad de dinero (capital), concedido a un interés fijo, del que deben realizarse un
número de pagos (periodos). El interés debe corresponder a un solo periodo de pago; así, si el periodo fuera
mensual, debería dividirse el interés anual entre 12.
=PAGO(5%/12;12*2;12000) da como resultado -526,46 euros, correspondientes a la mensualidad
de un crédito de 12000 euros, al 5% de interés anual durante 2 años. El resultado sale negativo porque es el
modo que tiene el programa de indicar que es dinero que hay que pagar.
=PAGOPRIN(Interés;Periodo;Periodos;Capital); calcula el importe del capital que se amortiza
en un periodo concreto (mensualidad) de un crédito hipotecario.
=PAGOPRIN(5%/12;1;12*2;12000) da como resultado -476,46 euros, que corresponde al capital
amortizado en el primer pago (mensualidad) de un crédito de 12000 euros, al 5% de interés anual durante 2
años. El resultado sale negativo porque es el modo que tiene el programa de indicar que es dinero que hay
que pagar.
=PAGOINT(Interés;Periodo;Periodos;Capital); calcula el importe de los intereses en un periodo
concreto (mensualidad) de un crédito hipotecario.
=PAGOINT(5%/12;1;12*2;12000) da como resultado -50,00 euros, que corresponde al importe de
los intereses del primer pago (mensualidad) de un crédito de 12000 euros al 5% de interés anual durante 2
años. El resultado sale negativo porque es el modo que tiene el programa de indicar que es dinero que hay
que pagar.
PRÁCTICA 24: VALIDACIONES DE DATOS.
En el menú Datos tenemos la opción Validación…, la cual permite establecer un criterio de
validación para los datos introducidos en cada celda. De este modo, el usuario de la hoja de cálculo, no
podrá introducir ningún valor que no cumpla dicho criterio:
Igualmente, en dicha opción se puede personalizar el mensaje que aparecerá cuando el usuario
active la celda para la que se ha definido la validación (ficha Mensaje entrante) , y el mensaje que
aparecerá cuando el usuario introduzca un valor no permitido (Mensaje de error).
PRÁCTICA 25: FUNCIONES LÓGICAS Y FUNCIONES DE INFORMACIÓN.
Funciones Lógicas
Excel ofrece al usuario 6 funciones que trabajan con valores lógicos (VERDADERO y FALSO):
VERDADERO(): devuelve el valor lógico VERDADERO.
FALSO(): devuelve el valor lógico FALSO.
Y(valor_lógico1; valor_lógico2;…): devuelve VERDADERO si todos sus argumentos son
verdaderos y FALSO en caso contrario.
O(valor_lógico1; valor_lógico2;…): devuelve VERDADERO si algún argumento es verdadero
y FALSO si todos son falsos.
NO(valor_lógico): devuelve VERDADERO si el argumento se evalúa a FALSO, y FALSO si el
argumento se evalúa a verdadero.
SI(condición; valor_si_verdadero; valor_si_falso): si la condición es verdadera devuelve el
resultado de evaluar el argumento valor_si_verdadero, y en caso contrario devuelve el valor_si_falso.
Funciones de Información
Esta categoría de funciones obtiene información acerca de diferentes aspectos de las hojas de
cálculo como tipos de datos en las celdas, tipos de error, entorno operativo, número de celdas en blanco
dentro de un rango, etc.
Algunas de ellas pueden ser utilizadas junto con las funciones lógicas, ya que devuelven como
resultado un valor lógico. Excel ofrece 18 funciones de información. Algunas de ellas son:
ESNUMERO(…), ESTEXTO(…), ESERROR(…), ESBLANCO(…), ESLOGICO(…), …
Las funciones anteriores devuelven VERDADERO si la celda que llevan como argumento es del
tipo que especifica su nombre.
En la práctica se pide utilizar funciones SI() anidadas para ir consultando si la celda es o no de cada
uno de los tipos anteriores. Es caso afirmativo, se debe mostrar el texto que indica el tipo de cada dato. La
fórmula a utilizar es:
=SI(ESNUMERO(A3); ”UN NÚMERO”; SI(ESTEXTO(A3); ”UN TEXTO”; SI(ESERROR(A3);
“UN ERROR”; SI(ESBLANCO(A3); “UN BLANCO”; SI(ESLOGICO(A3); “UN VALOR LÓGICO”;
“OTRA COSA”)))))
PRÁCTICA 26: GESTIÓN DE DATOS. LISTAS O BASES DE DATOS.
Excel permite la creación de bases de datos no demasiado complejas, formadas por:
Registros: cada uno de los elementos o entidades sobre los que la base de datos muestra
información. En una base de datos sobre los empleados de una empresa, cada empleado ocupará un
registro; en una base de datos que recoja las facturas expedidas, cada factura será un registro, etc.
En una base de datos confeccionada en Excel (o en cualquier programa de hoja de cálculo), los
registros se dispone en filas contiguas: cada registro en una fila diferente.
Campos: cada uno de los datos o unidades de información que la base de datos incluye en
relación con las entidades o elementos de que se trate. En el caso de la base sobre empleados de una
empresa, podrían ser campos a incluir: nombre, apellidos, DNI, nº de afiliación a la SS, etc.
En estas bases de datos, los campos se disponen en columnas. En la primera celda de cada
columna se escribe el nombre del campo (DNI, Nº de afiliación, etc).
Las principales ventajas de realizar bases de datos en Excel y no en Access (o en otro programa de
gestión de bases de datos) son:
- Resulta más fácil crear la base de datos en Excel que en Access.
- A efectos de realización de cálculos y de análisis numérico de los datos, Excel dispone de más
herramientas.
No obstante, cuando la base de datos a crear es más compleja es preferible utilizar el Access (u otro
gestor de bases de datos) e importar sus datos desde Excel cuando se quieran analizar.
En esta práctica, se pide crear una base de datos y realizar operaciones sobre los mismos. Entre las
operaciones a realizar: ordenar por algún campo o campos y filtrar aquellos datos que cumplan una
determinada condición.
En el menú Datos Ordenar…, se pueden elegir diversas columnas por las que ordenar, de modo
ascendente (alfabético) o descendente:
El sistema ordenara por la primera columna
indicada y en caso de encontrar dos valores iguales para la
misma, ordenará por la segunda que se haya indicado, y así
sucesivamente.
Al elegir Datos Autofiltro, aparecerá en el encabezado de cada columna
una lista desplegable, tal como en la figura, de forma que podemos mostrar Todos
los elementos de la columna seleccionada, ó Los diez registros más altos más bajos
de la lista (si se trata de números), ó alguno de los mostrados en la lista.
PRÁCTICA 27: APLICACIÓN DE FILTROS A UNA BASE DE DATOS.
Los conocimientos adquiridos en la práctica anterior sobre creación de filtros a una base de datos, se
aplican aquí, creando varios de ellos sobre una base de datos dada de ejemplo.
PRÁCTICA 28: ORDENACIÓN Y APLICACIÓN DE FILTROS A UNA BASE DE DATOS.
Se realizan las operaciones indicadas sobre una base de datos dada.
PRÁCTICA 29: LISTAS Y TABLAS DINÁMICAS.
Las tablas dinámicas son una herramienta relacionada con las listas o bases de datos creadas en
hojas de cálculo. Sirven para organizar de manera sencilla y rápida grandes cantidades de datos procedentes
de dichas listas. Permiten agrupar y filtrar la información por cualquiera de los campos de la lista, creando
una tabla-resumen.
En la práctica se pide crear la siguiente tabla. En ella aparecen las horas trabajadas durante una
semana por los programadores de una empresa en unos determinados proyectos:
Queremos crear una tabla dinámica que muestre, para cada programador empleado de la empresa, el
total de horas trabajadas en cada uno de los proyectos durante la última semana.
Seleccionando el rango A1:D10 y escogiendo Datos, Informe de tablas y gráficos dinámicos, se
inicia un asistente que nos muestra una serie de cuadros de diálogo consecutivos.
En el primer cuadro de diálogo, se solicita el origen de los datos a organizar en forma de tabla
dinámica. En este caso, dejamos la opción preseleccionada (Lista o base de datos de Microsoft Excel) y
Tabla dinámica.
En el siguiente cuadro se selecciona el rango de celdas en el que están situados los datos a
organizar. Si el propio asistente indica el rango correcto ($A$1:$D$10), se continúa con el Siguiente.
También es posible realizar tablas dinámicas con datos existentes en otros libros de Excel. En tales casos,
pulsaríamos el botón Examinar… y buscaríamos el libro en el disco y la carpeta correspondiente.
En el cuadro siguiente (paso 3) se
decide si la tabla dinámica estará en otra hoja o
en la hoja existente. Elegiremos esta opción y
en este caso, aparecerá un cuadro para elegir a
partir de qué celda aparecerá la tabla:
Al pulsar el botón Diseño…, aparece el
cuadro que figura a continuación. Aquí se
diseña la distribución de los campos en la tabla
a crear:
La tabla estará dividida en
cuatro secciones (PÁGINA,
FILA, COLUMNA y
DATOS), en las que se
pueden colocar los distintos
campos, pulsando sobre el
botón del campo (a la
derecha) y arrastrándolo a
una sección.
A la hora de organizar los
datos en nuestro ejemplo
deberá tenerse en cuenta lo
siguiente:
El campo que se coloque en la sección PÁGINA aparecerá en forma de una lista desplegable desde
la que se podrá seleccionar aquel elemento del que se desee mostrar el resumen. Existirá además la
posibilidad de mostrar el resumen correspondiente a cada empleado en una hoja diferente.
El campo que se coloque en la sección FILA mostrará sus elementos como encabezados o títulos de
las filas en la tabla.
El campo que se coloque en la sección COLUMNA, mostrará sus elementos como encabezados de
las columnas de la tabla.
En cuanto al campo que se coloque en la sección DATOS, sus datos se someterán a una
determinada operación de cálculo: Suma (es la que se ofrece por defecto cuando los datos de este
campo son todos numéricos), Contar (la que se ofrece por defecto en los demás casos), Promedio,
Mínimo, Máximo, Producto, etc.
En nuestro ejemplo, colocaremos Empleado en la sección PÁGINA, Proyecto en la sección FILA,
Fecha en la sección COLUMNA y Horas (que contiene los datos que queremos sumar) en la sección
DATOS (aceptamos la función SUMA que Excel propone por defecto).
Al hacer doble clic sobre la “Suma de Horas”, tenemos opción a cambiar el tipo de operación a realizar.
Tras Aceptar, pulsamos Finalizar y el resultado es
una tabla-resumen tal como la siguiente:
Al pulsar en la lista desplegable que corresponde
al empleado, aparecen todos los posibles. Se selecciona
el que se desee y aparecerá la tabla-resumen
correspondiente a sus horas trabajadas en cada proyecto,
ej: Pérez:
Se pueden mostrar u ocultar datos (ej: Proyectos
ó Fechas desmarcándolos cuando se despliegan las
listas deplegables Proyecto y Fecha, respectivamente).
Por defecto se mostrarán todos los datos.
Notas:
a) La forma elegida aquí para organizar los datos sólo es una de entre todas las posibles. Cabe
organizar los datos de otra manera; no obstante, hemos de procurar que la forma elegida sea la más clara y
fácil de interpretar.
b) Aunque los datos de una tabla dinámica tienen el mismo aspecto que cualquier hoja de cálculo,
no se pueden introducir ni editar los datos directamente en ella. Para modificar sus resultados deberán
modificarse forzosamente los datos a partir de los cuales se ha creado.
c) No obstante, las tablas dinámicas no se actualizan automáticamente cuando los datos de origen
cambian, sino que, es necesario seleccionar con el botón derecho del ratón una celda cualquiera de la tabla
y elegir la opción Actualizar datos del menú contextual correspondiente. Esa opción también está
disponible en la barra de herramientas Tabla dinámica, mediante el botón .
d) Una vez creada la tabla dinámica, se puede cambiar fácilmente su diseño arrastrando los botones
sombreados con los nombres de los campos a otras posiciones de la tabla (por esta razón se llaman
“dinámicas”, precisamente). Por ejemplo, Fecha a PÁGINA, Empleado a FILA, Proyecto a COLUMNA y
en DATOS dejamos Suma de Horas. Con esto tenemos el total de horas que cada empleado trabaja en cada
proyecto, pudiendo seleccionar la fecha:
e) Se puede modificar además el aspecto de la tabla o informe usando el botón de la barra de
herramientas Tabla Dinámica:
f) Se puede generar un gráfico a partir de la tabla dinámica
con tan sólo pulsar el botón de la barra de herramientas Tabla
Dinámica. Será un gráfico “dinámico”, por lo que se puede
modificar la forma de visualizar los datos, p.ej:
PRÁCTICA 30: FUNCIONES DE BASES DE DATOS.
Existen 12 funciones dedicadas al tratamiento de las bases de datos: BDSUMA, BDPROMEDIO,
BDMIN, BDMAX, BDCONTAR, BDPRODUCTO, … Los argumentos que tienen estas funciones son tres
y tienen el siguiente significado:
Base_de_datos. Es el rango de celdas que forma a tabla de la base de datos, incluyendo los títulos
de los campos o columnas.
Campo. Es el nombre del campo sobre el cual se desea operar mediante la función. Este nombre
debe ser uno de los existentes en la base de datos indicada en el primer argumento, y se coloca entre
comillas (“ “). También podemos indicar un número que indicará la posición del campo en la tabla.
Criterios. Son condiciones sobre los datos de la tabla y se especifican mediante un rango de celdas
en el que se deben incluir nombres de campos de la base de datos y la condición que deben cumplir
los registros seleccionados.
En la práctica se pide crear una hoja de cálculo con los siguientes datos:
Queremos calcular el promedio de las alturas de los alumnos nacidos en el segundo semestre del año
1980, y la fecha de nacimiento del mayor alumno cuya altura esté comprendida entre los 165 y 180 cm.
Empezaremos con la función que calcula el promedio. Antes que nada hay que establecer los
criterios de selección.
Hemos de seleccionar los registros cuya fecha de nacimiento se encuentre comprendida entre el 1 de
julio y el 31 de diciembre de 1980 (segundo semestre del año). Para ello, rellenaremos las celdas tal como
muestra la figura:
Ahora ya podemos introducir la función, por ejemplo en la celda C16:
=BDPROMEDIO(A2:E7;”Altura”;A9:C10), donde A2:E7 es la base de datos, “Altura” es el dato
del cual calculamos el promedio (podíamos haber indicado 4 en vez del nombre del campo) y A9:C10 es el
rango de celdas que contiene los criterios de selección para la función.
Para la segunda función, los criterios de selección (altura entre 165 y 180 cm) se escribirán en el
rango A12:C13:
y en C18 escribiremos la función:
=BDMIN(A2:E7;5;A12:C13)
Los resultados son:
En el resto de la práctica, es necesario utilizar las funciones que se mencionaron al principio para
calcular una serie de datos:
PRÁCTICA 31: FUNCIONES DE BASES DE DATOS.
En esta práctica, igual que en la anterior hemos de utilizar las funciones BDSUMA,
BDPROMEDIO, BDMIN, BDMAX, BDCONTAR y BDPRODUCTO, para obtener esos resultados a
partir de una base de datos de vehículos dada.
PRÁCTICA 32: OBJETOS, COMENTARIOS, ESTILOS, OPCIONES, MACROS.
En esta última práctica, se pide probar una serie de operaciones (comunes a otras aplicaciones del
paquete Office):
Insertar una imagen, un hipervínculo y diagramas. En el menú
Insertar … tenemos disponibles todas estas opciones.
Insertar un comentario para una celda. En Insertar
Comentario, podemos realizar esta operación. En Ver Comentarios
podemos ver u ocultar los mismos y en la barra de herramientas de
Comentarios..
Crear un estilo (en Formato Estilo…) que aplique formato determinado a las celdas que se
seleccionen (formato de número, fuente, alineación, bordes, tramas, …).
En Herramientas Opciones tenemos la posibilidad de configurar distintas opciones de Excel:
elementos que se verán en las hojas, opciones de cálculo, creación de listas personalizadas, opciones para
guardar, de ortografía, de seguridad, etc.
Igual en Word, en Excel se pueden crear macros. Las macros son instrucciones escritas mediante
un lenguaje de programación. Aquellos usuarios con conocimientos de programación en Visual Basic,
pueden aprovechar las oportunidades que ofrece Excel para la creación de macros.
Utilizando Herramientas Macro Grabar macro, podemos grabar una macro asignándole un
método abreviado de teclado. Las acciones que se realicen desde el momento que empezamos a grabar, se
guardarán hasta que detengamos la grabación .