Uso de funcións comunes

Traballaremos con fórmulas e funcións en distintos tipos de libros de Excel que reflicten varias situacións comúns que nos podemos atopar en numerosas ocasións:

• contar celdas en branco
• consolidar datos de varias follas
• anidar varias funcións SI para realizar distintas acciones en función de varias condiciones.
• calcular totales con fórmulas.
• totales con criterios.
• condicións.
• buscar rexistros
• pago de intereses e de capital
• rentabilidade dunha inversión

Varias formas de hacer lo mismo

Partiremos inicialmente deste exemplo:

Esta é unha folla de cálculo para reflexar un extracto dunha conta corrente bancaria. Están introducidos os conceptos e a súa data ademáis do importe que se debe reflexar na conta.

Se o importe é ingresar na conta, o Importe figurarará con signo positivo; se o importe debe extraerse da conta será con signo negativo.

ïmos introducir as fórmulas axeitadas para calcular o saldo da conta en cada momento.

Para D9, o saldo deste celda é o saldo que tiña conta na fila anterior sumada á cantidade que figure na columna Importe da fila actual (=D8+C9). Esta fórmula tamén serviría nas filas que restan unha cantidade do saldo, xa que o signo negativo do importe faría que se restara o importe do saldo. Co controlador de recheo podemos copiar esta fórmula ata a celda D18. Como as referenciasdas celdas na fórmula son relativas, o arrastrar o controlador de recheo a fórmula foise actualizando en cada fila.

Vexamos outro exemplo semellante pero con algún cambio. Neste caso, temos os importes a engadir na conta nas columnas Importe haber; e os importes a extraer da conta en Importe debe.

A fórmula que calcularía o saldo dunha fila tería que obter o saldo anterior, sumarlle o importa da columna haber actual e restarlle o importe da columna debe actual.

Para a celda E9, sería = E8 + D9 - C9. Arrastraremos o controlador de recheo ata E18. O resultado da columna Saldo é o mesmo que no caso anterior.

Unha terceira variante:

Na columna Debe/haber indicamos se o importe se debe sumar ou restar ó saldo (cunha h haber e cunha d debe).

En E9, utilzaremos a función SI para comprobar se debemos summar ou restar o dato da columna Importe ó saldo anterior. =SI(C9="h";E8+D9;E8-D9)

Co controlador de reche levamos ata E18.

AMPLIACIÓN
Podemos facer que co formato condicional, se mostren de distinto xeito as extraccións da conta.
Seleccionando D9:D17 e premento en Formato condicional e seleccionando Nueva regla.
A condición será semellante a utilizada na función SI¡¡¡¡: neste caso, comprobaremos se o importe é a extraer (haberá unha d na columna Debe/Haber)
O formato podemos indicar que sexa en cor vermella e cursiva.

Esta sería a configuración relevante para dar o formato condicional:

• 

  Formato condicional coa configuración axeitada

• 

  Resultado de aplicar o formato condicional

Cal é a conclusión? Que hai moitos enfoques e xeitos de solucionar o mesmo problema! E que cando vais solucionar un problema, é convinte darlle unha pensada o deseño para que nos sexa máis fácil obter os resultados desexados.

PREGUNTA En qué argumento da función SI temos que indicar a condición a comprobar? (a) Primeiro (b) Segundo (c) Terceiro
RESPOSTA (a)

Contar celdas en branco

Imaxinemos un libro de Excel utilizado para levar o control das pistas de tenis que reservan os socios nun club de tenis.

Hai catro pistas no club e cando unha persona reserva unha pista para un horario apúntase no libro.

Hai unha folla para cada día da semana, e un folla Resumen.

En función dos datos que temos introducido nas follas das reservas diarias queremos obter un resumo das pistas libres que temos durante á semana.

Utilizaremos a función CONTAR.BLANCO que serve para contar as celdas en branco que hai nun determinado rango de celdas que lle pasaremos coma argumento (so ten ese).

IMPORTANTE
Aparte das celdas en branco, esta función tamén conta as celdas con fórmulas que devolven texto baleiro (""). E non conta as celdas que teñen un valor cero (0).

Para B6, a formula será: =CONTAR.BLANCO(Lunes!B4:E4)

Coma o resto de horarios do lúns están distribuiídos en filas podemos extender a fórmula as celdas inferiores.

Para C6, a formula será: =CONTAR.BLANCO(Martes!B4:E4). Novamente podemos facer recheo.

PISTA'
Se o nome da folla ten algún espazo en branco ou algún carácter que non seña letra nin gúmero (signo -, signo de puntuación, etc.) hai que poñer o nome da folla entre comillas sinxelas.
Por exemplo =CONTAR.BLANCO('Hoja del martes'!B4:E4)

Para calcular o número total de pistas libres por días e por horarios, utilizaremos a función de suma automática de Excel; seleccionaremos de B6:G13 e premeremos en Suma.

Xa está lista a folla, e pode comprobarse o funcionamento facendo algunha reserva.

Hai tamén outras funcións que son útiles en moitas ocasións:

PREGUNTA Cantos argumentos ten a función CONTAR.BLANCO
RESPOSTA Un

Consolidar con referencias 3D

Analizaremos un exemplo dun libro coas notas dun exames.

• 

  Exemplo con notas

• 

  A última folla Resumen

As notas estan distribuidas do mesmo xeito nas distintas follas de resultados dos exames. Podemos empregar esta circunstancia para consolidar dunha forma sinxela os datos das notas.

Consolidar
Reunir ou xuntar nun so lugar a información que temos colocada en distintos sitios (por exemplo en distintas follas de cálculo) para poder amosar ou analizar dun vistazo toda esa información.

Podes consolidar datos de distintas follas de cálculo utilizando o botón Consolidar situado na lapela Datos da cinta de opcións. Outro método potente e flexible para consollidar datos é empregar fórmulas que fagan referencia a cada rango de celdas que imos combinar.

Neste caso, para calcular as notas medias dos alumnos, podemos utilizar a función PROMEDIO.

O primeiro que se nos pode ocurrir é ir indicando as distintas celdas que intervirán no cálculo da media. Para D7 =PROMEDIO('Examen 1'!D7;'Examen 2'!D7;'Examen 3'!D7;)

Outro xeito de facer isto mesmo e a través das Referencias 3D.

Referencia 3D
Unha referencia a unha celda ou a un rango de celdas nun rango de follas dun libro.
Índicase poñendo primeiro o rando de follas, co signo de dous puntos (:) entre a primeira e a última, seguido do signo ! e a celda ou rango de celdas a que facemos refeerencia.

Por exemplo, unha referencia á celda B8 desde Hoja1 a Hoja4 dun libro sería Hoja1:Hoja4!B8. Esto faría referencia á celda B8 das follas que estén entre as follas Hoja1 e Hoja4, ambas inclusive.

Porén, poderíamos facer para D8: =PROMEDIO('EXAMEN 1:EXAMEN 3'!D8)

A vantaxe de facelo así e que se engadimos unha folla máis entre Examen 1 e Examen 3, esta tamén se terá en conta para calcular a nota media.

Arrastrariamos de D7 a D14 para calcular o resto de notas medias.

Anidar funcións SI

Para completar a folla, fainos falla obter os datos da columna Avaliación.

Nesta columna poñeremos un texto que indique o resultado final da evaluación do alumno, en función da nota media dos exames:

• < 5 : insuficiente
• => 6 e < 6 : suficiente
• => 7 e < 9 : notable
• => 9 : Sobresaínte

Utilzaremos SI para obter o valor da Evaluación a partir da Nota media, encadenando unha función SI tras outra (podemos anidar ata 64).

En E7, teremos =SI(D7<5;"Insuficiente";SI(D7<6;"Suficiente";SI(D7<9;"Notable";"Sobresaínte")))

Con controlador de recheo, expandimos ó resto de celdas da columna Evaluación.

PISTA
Cando o numero de condicións é elevado, anidar varias funcións SI non so é complexo a hora de escribir a fórmula; tamén e propenso a erros.
Nas versións máis recentes de Excel engadíuse unha función moi interesante que nos pode axudar nestes casos: SI.CONJUNTO
Con esta función avalíanse varias condicións na orde en que se especifiquen. Se algunha se cumpre, devolve o resultado relacionado con ela.

O exemplo que vimos quedaría: SI.CONJUNTO(D7<5; "Insuficiente";D7<6; "Suficiente";D7<7; "Notable";1=1; "Sobresainte")

A condición final ten que avaliarse sempre a verdadeiro. Por iso usamos a igualdade certa (1=1), para indicar un resultado predeterminado.

PREGUNTA. A través de qué comando de Excel poderás comprobar cómo se van calculando as distintas partes dunha función SI anidadada? (a) Evaluar fórmula (b) Validación de datos (c) Rastrar precedentes
RESPOSTA (a). Esta característica está accesible desde a lapela Fórumulas premendo no botón Evaluar fórmula

Resumo

Para introducir unha fórmula nunha celda tes escribir o signo igual (=) e introducir a fórmula. Podes introducir as referencias a celdas directamente ou utilizar o rato para facer clic nelas ou pulsar e arrastrar.

Tamén podes introducir unha fórmula a través da fiestra que aparece ó pulsar o botón Insertar función: primeiro seleccionaras a función e despois os argumentos.

Para extender unha fórmula que está nunha celda das celdas contiguas podes utilizar o controlador de recheo. Se as referencias a celdas da fórmula orixinal eran relativas estas iranse actualizando en cada celda na que se copiara.

Outras funcións que poden ser útiles:

• CONTAR.BLANCO: conta as celdas en branco que hai nun rango de celdas que lle pasamos como argumento
• CONTARA: conta as celdas que non están vacías
• CONTAR: conta as celdas con números
• ESBLANCO, ESNUMERO e ESTEXTO: devolver verdadeiro se o argumento se refire a unha celda vacía, a un número ou a un texto, respectivamente
• ES.PAR e ES.IMPAR, comproban se o seu argumento refírese a un número par ou impar.

Podes consultar todas as funcións na documentación, alfabéticamente ou por categoría.

En Excel podes consolidar datos (e dicir, reunir, amosar ou resumir nun so lugar a información que se atopa en varias follas de cálculo) a través do comando Consolidar ou empregando fórmulas. Neste último caso, as distintas follas de cálculo onde se atopan os datos teñen o mesmo deseño, e podes utilizar unha referencia 3D, que é unha referencia a unha celda ou a un conxunto de celdas que están nun rango de follas dun libro.

Se queres comprobar varias condicións, para realizar unha acción ou outra dependendo da condición que se cumpra, podes anidar varias funcións SI, de xeito que nos argumentos dunha función SI existan outras funcións SI (sendo posible anidar ata 64).

Neste último caso pode ser de axuda pintar un esquema para analizar as posibles condicións.