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

Calcular totales con fórmulas

Utilizaremos un libre dunha empresa de venta de material informático para levar un control das súas ventas.

• 

  listado dos artículos disponibles para a venta co seu código e precio clasificados por grupos

• 

  Información dos artigos que vendemos o longo do primeiro trimestre do ano

• 

  Posible informe trimestral sobre as ventas de artigos que poderiamos crear a partires dos datos da táboa anterior.

Queremos obter certos resumos: por exemplo, cantidade gañada vendendo artigos du grupo de impresoras ou durante o mes de xaneiro. Unha forma de lograr isto podería ser filtrando a lista ou utilizando a ferramente de subtotales que ten Excel. Pero imos ver un xeito máis flexible de facer isto mediante o uso de funcións.

Na folla Resumen, en B5 teríamos que amosar o total de ventas realizadas para todos os artigos do grupo ordenadores: utilizaremos a función SUMAR.SI.

SUMAR.SI(rango;criterio;sum_rango)
Esta función empregase para sumar só os valores dun rango de celdas que cumpren un determinado criterio.
Primer argumento. Indícase o rango de celdas a avaliar.
Segundo argumento. O criterio polo que se avalía.
Terceiro argumento. Indica o rango de celtas que ten os datos que imos sumar.
As celdas que indican no argumento sum_rango' sumaranse so se as celdas correspondentes do rango cumpren o criterio establecido.

No noso caso, para B5 será =SUMAR.SI('Articulos vendidos'!C4:C58;A5;'Articulos vendidos!G4:G58)

IMPORTANTE
Se utilizamos o controlador de recheo para copiar a fórmula nas celdas de debaixo, as referencias ás celdas e rangos de celdas se actualizarían, co que os rangos do primer e tercer argumento cambiarían cando esto non debe ser así.

Para solucionar isto, podemos transformas as referencias a estos rangos de celdas de relativas a absolutas.

PISTA
Outra forma para que as referencias as celdas non se actualicen ó copiar a fórmula pode ser asignando nomes os rangos das columnas da lista e utilizar estes nomes coma referencias na fórumla.
Por exemplo: SUMAR.SI(RangoGrupo;A5;RangoSubtotal)

PISTA
Para cambiar referencias alternando de relativa, absoluta a mixta (sucesivamente) pódese facer premendo a tecla F4.

Cambiando as referencias, a fórmula quedaría coma: =SUMAR.SI('Articulos vendidos'!$C$4:$C$58;A5;'Articulos vendidos!$G$4:$G$58). Xa podemos usar o controlador de recheo e obter o resultado agardado.

Se prememos en B10, veremos na barra de fórmulas como cambiou unicamente a referencia á celda do segundo argumento da función, que era a única relativa.

PREGUNTA. Na función SUMAR.SI, qué argumento ten os valores dos datos a sumar? (a) Primeiro (b) Segundo (c) Terceiro
RESPOSTA. (c)

Totales con criterios

No caso dos subtotais por mes, o criterio polo que temos que filtrar os rexistros a sumar é un pouco máis complexo que no caso anterior. Non se trata de sumar os rexistros que teñen un valor en certo campo igual a outro valor dado; ten que estar comprendido entre dous valores (no primeiro caso, entre o primeiro e o último día do mes de xaneiro)

Xa non nos valería a función SUMAR.SI. Temos que utilizar unha función que permita uns criterios máis complexos: BDSUMA. Podes ver esta e outras funcións:

Lembra que esta función pertence o grupo de funcións de bases de datos, que seleccionan os rexistros a través dun rango de criterios existente na folla de cálculo, similar o usado nos filtros avanzados. Imos introducir na folla de cálculo o rango de criterios.

Seleccionamos as celdas D12..E13. Nun rango de criterios temos que escribir unha fila cos títulos dos criterios, e polo menos unha fila cos criterios.

Estes títulos de criterios deben ser iguales ós titulos das columnas da lista coas que imos traballar. Neste caso imos seleccionar os artigos a sumar segundo a data.

O introducir os criterios na mesma fila indicamos que se deben cumprir todos eles: a data de venta do artigo debe estar incluida entre estas dúas datos. Aquí Fecha é o título da columna utilizada na folla Artículos vendidos.

PISTA
Se poñemos os criterios en distintas filas indicamos que se debe cumprir un ou outro: con que que se cumpra un será suficiente para que inclúa nos datos a sumar.

En E5, empregaríamos =BDSUMA('Artículos vendidos'!$A$3:$G$58;"Subtotal";D12:E13)

PISTA.
En el segundo argumento de BDSUMA también puedes introducir un número, que indicará la posición en la lista o tabla de datos de la columna: 1 para la primera columna, 2 para la segunda columna, etc.

Para obter os subtotáis para o resto de meses teremos que actuar do mesmo xeito: creando un rango de crterios e indicárllo na función BDSUMA. O único que cambia na fórmula de cada celda é o rango de criterios que temos en conta na función BDSUMA. Para E7, o último argumento sería D18:E19

PREGUNTA. Qué función permite indicar criterios máis complexos para obter os datos a sumar? (ab) SUMAR.SI  (b) BDSUMA
RESPOSTA. (b)

Condicións

Seguiremos traballando con este exempplo:

Cada artigo ten un codigo diferente, que identifica claramente o artigo en cuestión. Utilizaremos máis adiante este código para facer referencia ós artigos.

A tarefa que nos propoñemos agora é crear un modelo de factura para a nosa empresa na que podamos introducir estes artigos:

Temos que introducir as fórmulas nas celdas axeitadas.

En E10, crearemos a fórmula que multiplica a cantidade polo precio unitario do artigo para obter o total dese artigo. =A10*D10 . Pero podemos mellorar esta fórmula para que na factura non apareza un cero nesta columna cando non hai artigos.

Unha forma de arranxar este problema é utilizar a función SI. Por exemplo, podemos comprobar se hai algún valor introducido na columna da cantidade: se hai algún dato efectuamos a operación, e se non o hai non facemoes nada. =SI(A10<>"";A10*D10;"")

Tamén poderíamos facelo utilizando formato personalizado... e común que haiba varias solucións para arranxar o mesmo problema.

Co controlador de recheo copiamos a fórmula ata a celda E17.

PREGUNTA. Se introducimos algún valor na columna D (Precio), executarase a fórmula indicada no segundo argumento da función SI da columna Total?
RESPOSTA. Non. A condicion é que o valor da celda correspondente a columna A sexa distinto de vacio, isto é, que teña algún dato.

Con código podemos identificar o artigo... así que o ideal sería que introducindo o código na factura se introduza de xeito automático a descrición e o prezo. En C10, poderemos utilizar BUSCARV con este fin.

BUSCARV(valor_a_buscar;rango;columna;ordenada)
Esta función busca:
Primer argumento. O elemento buscado.
Segundo argumento. O rango de celdas desde a primeira columna.
Terceiro argumento. Devolve o valor que hai en dita fila na columna indicada.

O valor a buscar será o que indiquemos na columna Código da factura. O rango de celdas onde buscaremos será a lista de artigos que se atopa na folla de Artículos. A columna que queremos obter neste caso é a descrición do artigo, que e a terceira columna da lista de artigos. Para que sexa coincidencia exacta, o cuarto argumento será falso.

PISTA
se indicamos VERDADERO no último argumento da función, a lista cos datos a buscar debe estar ordeada segundo os valores da primeira columna; neste caso, se non se atopa exactamente o valor buscado, utlizase o valor máis grande que sexa menor ou igual o valor buscado. Se este argumento FALSO, a función buscará unha coincidencia exacta.

Así, para C10 =BUSCARV(B10;Artículos!A3:D33;3;FALSO)

PISTA 
O erro #N/D prodúcese cando o elemento que buscamos non se atopa no rango de celdas especificado. Como aínda non indicamos ningún código de artigo (celda B10), amósase este tipo de erro.
Podemos solucionar isto utilizando a fuunción SI para comprobar se temos introducido algún valor na columna do código do artículo.

Seguindo esta pista, podería quedar coma =SI(B10<>"";BUSCARV(B10;Artículos!$A$3:$D$33;3;FALSO);"")

Como xa temos cambiadas as referencias ó rango de celdas de relativas a absolutas, podemos arrastrar o controlador de recheo ata C17.

Para a columna Precio, en D10 sería =SI(B10<>"";BUSCARV(B10;Artículos!$A$3:$D$33;4;FALSO);"")

Se probamos o funcionamento introducindo a cantidade 3 en A10, daría un erro #¡VALOR! en E10; poderíamos corrixir isto facendo que só se calcule o total cando haiba datos en cantidade e en prezo do artigo.

Introducimos O¡01 en código do artigo... e as columnas Descrición e Prezo funcionan como esperabamos.

SUXERENCIA
Podeeriamos mellorar a folla protexendo as celdas das columnas Descripción, Precio e Total e a dos totales da factura para quenon as opida modificar o usuario.

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.

Máis adiante repasaronse algunhas funcións relevantes: