Concepto y ventajas de la hoja de cálculo

Informática II Unidad II

Hojas de cálculo

Tema II: Funciones y fórmulas

de la hoja de cálculo

Funciones y fórmulas de la hoja de cálculo

Existen siete fichas clasificadas por sus características y encabezan la cinta de

opciones. Estas fichas son:

Inicio

Insertar

Diseño de página

Fórmulas

Datos

Revisar

Vista

Las fichas inicio, insertar, diseño de página, revisar y vista tienen opciones muy

similares a las de Word.

Hay dos fichas exclusivas de Excel: fórmulas y datos. Aunque ambas son

importantes, la primera contiene las herramientas propias de una hoja de cálculo por lo

que se describirá con mayor detalle.

Las fórmulas son ecuaciones que realizan cálculos con los valores de la hoja de Excel. Una fórmula comienza con un signo de igual (=).

Por ejemplo: = 4+3*2 La fórmula anterior realiza el siguiente cálculo: multiplica 3 por 2 y al resultado le suma

4.

En las celdas de Excel solo aparece la fórmula mientras se escribe. Una vez terminada

se pulsa la tecla Enter y automáticamente en la celda se muestra el resultado.

En la imagen se muestra la fórmula y el resultado obtenido. En la barra de fórmulas

siempre aparece la ecuación original. Una fórmula puede contener:

Funciones

Referencias

Operadores

Constantes

Las funciones son fórmulas previamente guardadas en Excel para realizar diferentes

operaciones lógicas y matemáticas. Más adelante se describirán las funciones más

usadas en una hoja de cálculo.

Las referencias indican qué celdas se

usaran en la fórmula. Existen dos tipos de

referencias que se describirán

posteriormente.

Un operador es un signo o símbolo que

especifica el tipo de cálculo que se debe

llevar a cabo en una ecuación. Hay

operadores matemáticos, comparativos,

lógicos y referenciales.

Una constante es un valor que no ha sido

calculado y que, por tanto, no varía. Por

ejemplo, el número 210 y el texto "Ingresos

trimestrales" son constantes. Las

expresiones, o los valores resultantes de

ellas, no son constantes.

En la imagen se muestra una tabla de multiplicar. En la primera columna se escribe el

número 2, en la siguiente columna el signo “x”, en la tercera el número consecutivo

correspondiente del 1 al 10 y en la cuarta columna el signo “=”. Posteriormente en la

última columna (columna F) se escribe la fórmula: =B2*D2. Al oprimir la tecla Enter

aparece el resultado de la primera fila (2 x 1 = 2).

Para repetir la fórmula en todas las filas se realiza el siguiente procedimiento: Colocar

el puntero en la esquina inferior derecha de la celda donde se encuentra la fórmula, dar

un clic sostenido (mantener apretado el botón del mouse) y arrastrar la fórmula hacia

abajo hasta llegar a la última fila (fila 11). De esta forma se consigue crear una tabla de

multiplicar.

Las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores

específicos, denominados argumentos, en un determinado orden o estructura. Las

funciones pueden utilizarse para ejecutar operaciones simples o complejas.

En la ficha fórmulas se encuentra la opción insertar función. Esta opción despliega

una ventana en donde se puede elegir la función necesaria de acuerdo a la operación

que se desea realizar.

Las funciones están agrupadas en categorías como:

Matemáticas

Lógicas

Estadísticas

Financieras

Información

Ingeniería

La estructura de una función comienza por el signo igual (=), seguido por el nombre de

la función, un paréntesis de apertura, los argumentos de la función separados por

signos de punto y coma y un paréntesis de cierre.

Por ejemplo para usar la función suma se debe escribir la fórmula como se

muestra en la siguiente imagen:

En este caso, el nombre de la función es SUMA y el argumento es (B4:B8). Esto

significa que la fórmula realizará una suma de los valores que existen en las celdas de

la columna B, desde la fila 4 hasta la fila 8 y el resultado se mostrará en la celda B9.

También es posible emplear esta función para sumar valores en una misma fila. La siguiente imagen muestra un ejemplo de esto.

La función empleada es nuevamente SUMA pero el argumento ha cambiado, ahora es (A3:E3). En este caso se sumaran todos los valores de la fila 3 entre la columna A y la columna E. Se debe observar que las celdas involucradas son marcadas por un borde azul que ayuda a identificarlas. Otras funciones comunes son:

Promedio

Contar número

Si

No

Falso

Verdadero

Ejemplo, se puede calcular el promedio de las calificaciones de los alumnos de

una clase empleando Excel.

La función es PROMEDIO y el argumento es (B3:D3), es decir, calcular el promedio de

las tres calificaciones almacenadas en las celdas B3, C3 y D3. El resultado se mostrará

en la celda E3 correspondiente al promedio de “Luis Pérez”.

Para calcular los restantes promedios se debe “arrastrar” la fórmula como se explicó en

el ejemplo de la tabla de multiplicar. Entonces se obtiene el siguiente resultado:

Emplear este método en una escuela que puede tener cientos de alumnos reduce el

trabajo y el tiempo para calcular los promedios de cada uno de los estudiantes.

Además, no es posible que exista un error en la operación realizada por la

computadora.

Un aspecto importante cuando se realizan cálculos es la jerarquía de las operaciones.

A continuación se muestra el orden en que Excel realiza las operaciones contenidas en

una celda:

1. Operaciones agrupadas en paréntesis “( )” 2. Porcentaje “%” 3. Exponenciación “^” 4. Multiplicación y División “*” y “/” 5. Suma y resta “+” y “-” 6. Comparación “=”, “>”, “<”, etc.

En lo que se refiere a funciones lógicas se puede plantear el siguiente ejemplo:

Además, de calcular el promedio, se desea que aparezca en la siguiente celda

la palabra “Aprobado” o “Reprobado” según sea el caso. Para lograr esto, es necesario

emplear la función lógica “SI”. El procedimiento es el siguiente:

1. Seleccionar la celda donde se colocará la fórmula. En la ficha Fórmulas, hacer

clic en la opción “Insertar función”.

Celda donde se

insertará la fórmula

2. Aparecerá la siguiente ventana:

3. Se debe seleccionar la categoría “lógicas” y en la lista de funciones hacer doble-

clic en “SI” o simplemente seleccionarla con el mouse y dar clic en “aceptar”.

Entonces, aparecerá otra ventana.

4. Es la ventana de argumentos de función. En ella se deben escribir tres datos

muy importantes. Primero Prueba _lógica, es decir, la condición que se desea

evaluar. En el ejemplo del promedio, la condición para que el alumno esté

aprobado es que su calificación sea igual o mayor que seis. La siguiente casilla

es Valor _si _verdadero, en ella se escribe lo que se desea que se muestre en

la celda en caso de que la condición se cumpla, en este caso, si la calificación

del alumno es igual o mayor que 6, debe aparecer la palabra “Aprobado”.

Finalmente la casilla Valor _si _falso, se refiere a los casos en que la

calificación es menor a 6, entonces se mostrará la palabra “Reprobado”. La

siguiente imagen muestra la ventana con los argumentos escritos.

Nota: Se debe observar que en la prueba lógica E3>=6, primero se indica la celda donde se

encuentra el valor a comparar, en este caso la celda E3 que contiene el promedio de uno de

los estudiantes. Posteriormente, se escriben los signos de comparación, es importante señalar

que el signo > (mayor que) se escribe antes que = (igual), debido a las reglas de programación

de Excel. Finalmente se escribe el segundo dato a comparar, en este caso es el número 6, la

calificación mínima.

5. Una vez que los argumentos están escritos se da clic en aceptar para aplicar la

fórmula.

6. Finalmente se “arrastra” la fórmula para que se aplique al resto de los

estudiantes.

Este es solo un ejemplo de lo que se puede realizar empleando funciones lógicas. Se

recomienda realizar las actividades de aprendizaje que se encuentran al final de esta

unidad para reforzar el uso de las funciones.

Referencias relativas y absolutas Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la

celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la

posición de la celda que contiene la fórmula, cambia la referencia. Si se copia o se

rellena la fórmula en filas o columnas, la referencia se ajusta automáticamente. De

forma predeterminada, las nuevas fórmulas utilizan referencias relativas.

Por ejemplo, si copia o rellena una referencia relativa de la celda B2 en la celda

B3, se ajusta automáticamente de =A1 a =A2.

Las referencias relativas son de gran utilidad para realizar cálculos similares al del

ejemplo de la tabla de multiplicar, ya que basta con escribir la fórmula una vez y

copiarla las veces que sea necesario.

Sin embargo, hay ocasiones en las que se desea fijar el valor de una celda en una

fórmula, entonces es necesario emplear referencias absolutas.

Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia

a una celda en una ubicación específica. Si cambia la posición de la celda que contiene

la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o

columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas

fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias

absolutas.

Por ejemplo, si copia una referencia absoluta de la celda B2 en la celda B3,

permanece invariable en ambas celdas: =$A$1

Un ejemplo de la aplicación de referencias absolutas es una lista de precios en

moneda extranjera. Para expresar el precio en pesos se debe multiplicar el precio

original por el tipo de cambio. En este caso se escribirá en una celda el tipo de cambio

de la moneda extranjera y se usará una referencia absoluta para fijar ese valor en la

fórmula para convertir el precio de una moneda a otra.

En la imagen se observa que el tipo de cambio del dollar se encuentra en la celda D3, mientras que en la columna B se encuentran los precios de varios aparatos de diferentes departamentos. Cada uno de esos precios esta en dólares y se debe multiplicar por el tipo de cambio. Por lo tanto, la referencia absoluta es la celda D3. La fórmula se escribe: =$D$3*B6. La celda B6 corresponde al primer precio de la lista. Para aplicar la fórmula a todos los precios se debe arrastrar la fórmula.

Quedando de la siguiente manera:

Además, existe la posibilidad de combinar las características de las referencias

relativas y absolutas para crear referencias mixtas. En este caso pueden existir dos

alternativas: una columna absoluta y una fila relativa o una columna relativa y una fila

absoluta.

Una referencia de columna absoluta adopta la forma $A1, $B1, etc. Una referencia de

fila absoluta adopta la forma A$1, B$1, etc. Si cambia la posición de la celda que

contiene la fórmula, se cambia la referencia relativa y la referencia absoluta permanece

invariable. Si se copia o rellena la fórmula en filas o columnas, la referencia relativa se

ajusta automáticamente y la referencia absoluta no se ajusta.

Por ejemplo, si se copia o rellena una referencia mixta de la celda A2 en B3, se

ajusta de =A$1 a =B$1.

Las referencias mixtas pueden ser de gran utilidad, aunque para usarlas se necesita un

poco más de experiencia y habilidad para aprovechar al máximo sus características.

Para hacer referencia (relativa, absoluta o mixta) a cualquier celda de la hoja en una

fórmula se realiza el siguiente procedimiento:

1. Colocarse en la celda donde se escribirá la fórmula

2. Teclear el signo “=”

3. Insertar la función deseada (opcional)

4. Seleccionar la celda de referencia

5. Establecer si es referencia absoluta o mixta (la relativa está predeterminada)

6. Escribir los operadores o argumentos de la función (opcional)

7. Oprimir la tecla Enter para obtener el resultado

Finalmente, existe una referencia más llamada estilo de referencia 3D. Sirve para

emplear valores o fórmulas almacenadas en celdas de diferentes hojas de cálculo.

Por ejemplo, la fórmula: =Hoja1!A1, muestra el valor contenido en la celda A1

de la hoja 1, este valor aparece en la hoja donde se escribió la fórmula.