PDF de programación - Guía Teórico-Práctica 1 - Formulas y funciones avanzadas

MICROSOFT EXCEL 2007 – NIVEL AVANZADO



GUÍA TEÓRICO-PRÁCTICA N° 1 – FORMULAS y FUNCIONES AVANZADAS

Contenido

INTRODUCCIÓN ...............................................................................................................................2
CONCEPTOS BÁSICOS ....................................................................................................................2
Fórmulas Algebraicas.......................................................................................................................2
Precedencia de los operadores en las fórmulas...........................................................................2
Combinación de paréntesis..........................................................................................................2
Uso de referencia de celdas en fórmulas .....................................................................................3
Referencias relativas, absolutas y mixtas ....................................................................................3
Referencia a otra hoja de cálculo dentro del mismo libro...........................................................4
Referencia a hojas en otros libros................................................................................................4
Formulas con Funciones ..................................................................................................................5
Funciones Matemáticas....................................................................................................................5
Actividad 1.......................................................................................................................................6
Funciones de Texto ..........................................................................................................................7
Actividad 2.......................................................................................................................................9
Funciones de Fecha........................................................................................................................10
Funciones Lógicas..........................................................................................................................10
Funciones de Búsqueda y Referencia ............................................................................................10
Actividad 3.....................................................................................................................................12
Trabajar con nombres de rangos en las funciones de búsqueda.....................................................13
EJERCICIO COMPLEMENTARIO: ................................................................................................13





Área de Servicios

Dpto. de Informática

U.N.S.L.

“MICROSOFT EXCEL 2007 – NIVEL AVANZADO”

INTRODUCCIÓN

Las formulas y funciones son fáciles de crear en Excel y, por su diseño, éstas simplifican y
automatizan las tareas que de otro modo serían un trabajo mucho más intenso. Dicho esto, hay una
serie de funciones que en la práctica requieren una cierta habilidad para resolver problemas
complejos. Parte del desafío es saber cuándo y cómo se pueden aplicar éstas y cómo diseñarlas para
un propósito específico.

CONCEPTOS BÁSICOS

Fórmulas Algebraicas

Precedencia de los operadores en las fórmulas

Las formulas en Excel son calculadas de izquierda a derecha comenzando a leer después del
signo igual (=) y calculando los valores de acuerdo a la precedencia de los operadores. El orden
en que son calculadas las operaciones es el siguiente:

1.

2.

3.

4.

Porcentaje [%]

Exponenciación [^]

Multiplicación [*] y división [/]

Suma [+] y resta [-]

Seguramente el concepto quedará más claro con un ejemplo. Considere la siguiente fórmula:

= 5+3*4-6/2

Las multiplicaciones y divisiones se calcularán primero obteniendo el siguiente resultado:

= 5+12-3

El número 12 es el resultado de multiplicar 3*4 y el número 3 es el
resultado de la división 6/2. Finalmente se realizará la suma y la
resta dando como resultado 14 que es precisamente el
resultado que obtenemos en Excel.

Combinación de paréntesis

Aun cuando existe un orden predeterminado para los operadores, se puede influir en la
precedencia de operadores al utilizar paréntesis los cuales tendrán la preferencia sobre los
operadores. Considere la siguiente fórmula:

= (5+3)*4-6/2

Esta es una fórmula similar a la anterior solamente que he
colocado un paréntesis para la suma (5+3), la cual será calculada
antes que cualquier otra cosa dejando el siguiente resultado:

= 8*4-6/2

Ahora que ya no hay paréntesis Excel aplicará el orden
predeterminado empezando con las multiplicaciones y divisiones para obtener el resultado:

MICROSOFT EXCEL - AVANZADO

GUÍA N° 1

2




Área de Servicios

Dpto. de Informática

U.N.S.L.

“MICROSOFT EXCEL 2007 – NIVEL AVANZADO”

= 32-3

Finalmente se hará la resta para obtener el resultado final de 29. Observe cómo Excel
efectivamente obtiene este resultado:

Uso de referencia de celdas en fórmulas

En Excel se puede especificar tres tipos de referencias: relativas, absolutas y mixtas. Al utilizar
el tipo de referencia adecuado se puede asegurar de que al copiar la fórmula a una nueva
ubicación seguirá funcionando adecuadamente.

Si se tiene una fórmula que hace referencia a otras celdas y se copia esa fórmula a otra ubicación,
Excel actualizará automáticamente las referencias de la fórmula para adecuarlas a la nueva
ubicación. Considere el siguiente ejemplo.

La celda C1 hace la suma de A1 y B1. Observe lo
que sucede si copiamos el contenido de la celda C1 y
se lo pega en la celda C2.

Excel ha sido tan inteligente que ha supuesto que
la fórmula de la celda C2 debe sumar los valores
de A2 y B2. Si se vuelve a copiar la fórmula y se
la pega en la celda C3 se obtendrea la fórmula:
=A3 + B3.

Referencias relativas, absolutas y mixtas

Una referencia relativa a una celda o rango es aquella que, al copiar la celda donde está escrita y
pegarla en otra ubicación o al utilizar Autorrellenar se ajusta automáticamente para hacer
referencia a otras celdas.

Son las referencias más utilizadas y las que, en la mayoría de las veces, se obtienen por defecto
en Excel al hacer click en una celda para hacer referencia a ella. Son del tipo A1.

Por ejemplo, si se tiene la expresión =A1*2 en la celda B1 y se
utiliza Autorrellenar hacia abajo, las fórmulas que se obtienen van
ajustando el número de forma que en la celda B2 se tendrá la
fórmula =A2*2, en la celda B3 se tendrá la fórmula =A3*2 y así
sucesivamente.

Se puede ver en la siguiente imagen como
se ajustan las fórmulas a medida que "se
arrastran" con Autorrellenar. El resultado
sería:

Una referencia absoluta a una celda o rango es aquella que, al copiar la celda donde está escrita y
pegarla en otra ubicación o al utilizar Autorrellenar, NO se ajusta y queda bloqueada haciendo
referencia siempre a la misma celda.

MICROSOFT EXCEL - AVANZADO

GUÍA N° 1

3




Área de Servicios

Dpto. de Informática

U.N.S.L.

“MICROSOFT EXCEL 2007 – NIVEL AVANZADO”

Para hacer una referencia absoluta a una celda, se deben introducir los símbolos pesos $ antes
de la letra (columna) y antes del número (fila) de una referencia normal del tipo A1. Es decir, una
referencia absoluta a la celda A1, sería: $A$1

Con ello, se consigue que al copiar y pegar o bien al utilizar
Autorrellenar siempre se mantiene la referencia a la misma
celda. Se puede ver en la siguiente
imagen como se ajustan las fórmulas
a medida que "se arrastran" con
Autorrellenar. El resultado sería:

Una vez que se comprendieron los conceptos de "referencia relativa" y "referencia absoluta", es
fácil entender el concepto de referencia mixta. Una referencia mixta a una celda o rango es
aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar
Autorrellenar ajusta sólo la letra (columna) o sólo el número (fila) de la referencia, quedando
bloqueado sólo el número (fila) o sólo la letra (columna) respectivamente.

Por lo tanto,si se quiere bloquear la letra (columna), se deberá colocar el símbolo pesos "$" antes
de la letra (columna) y si se quiere bloquear el número (fila), se deberá colocar el símbolo pesos
"$" antes del número (fila).

Las referencias mixtas son útiles en multitud de ocasiones, especialmente cuando se tiene que
utilizar Autorrellenar en una matriz de celdas (varias celdas x varias celdas), puesto que permite
"arrastrar" una fórmula introducida en una esquina, hasta la celda opuesta en la matriz, para
operar con
rápidamente y de una sola vez.

los encabezados de columnas y

filas

Se puede observar en
referencia mixta preparada para "arrastrar":

la siguiente

imagen una

Como resultado, una vez "arrastrada" la
fórmula de la celda C3 hasta la celda E5, se
obtendrán los siguientes valores:

Referencia a otra hoja de cálculo dentro del mismo libro

Si en una fórmula o celda se desea hacer referencia a una celda o rango de otra hoja, se puede
utilizar la referencia a otras hojas, precediendo al rango, el nombre de hoja de cálculo.

Sintaxis: NombreHoja!Referencia, por ejemplo Datos!A4:D8

Al construir la fórmula, se deberá seleccionar la hoja que contiene la referencia y luego se
seleccion1 la celda o rango. Se ingresará la referencia a la otra hoja automáticamente.

Referencia a hojas en otros libros

Si en una