Operacions bàsiques: fórmula SUMA, no fórmula RESTA?

No és cap broma, més d'un principiant m'ha preguntat algún cop amb quina fòrmula pot restar a l'Excel. La majoria comencen a utilitzar Excel per sumar coses i utilitzen la fòrmula SUMA i és per això que esperen trobar una fòrmula RESTA.

Els operadors bàsics a Excel són:

  • + per sumar
  • - per restar
  • * per multiplicar
  • / per dividir
Per exemple =C5+B10 suma les cel·les C5 i B10

Per el particular ús de les sumes a Excel amb grans quantitats de dades existeix la fòrmula SUMA. El exemple =SUMA(C5;B10) sumaria un altre cop les cel·les C5 i B10 perquè els arguments de la funció estan separats per un punt i coma. En canvi =SUMA(C5:B10) sumaria tot el rang de cel·les des de la B5 a la C10, és a dir, 12 cel·les. Això és així perquè és un únic argument: un rang que es defineix com la cel·la inicial i la cel·la final separats per dos punts. La suma de rangs és el veritable poder i utilitat de la fòrmula.

Els símbols + i - a més es poden utilitzar per començar a escriure fòrmules perquè Excel posarà automàticament un = davant.

Fórmules matricials i II

Ja havia tractat abans el tema de les fórmules matricials, però no n'estava content. A més, la sintaxi que vaig explicar no era la més entenedora visualment, per tant aquesta entrada és un aclariment i extensió de la primera.

Repassem els aspectes clau:

  • Les fórmules matricials són molt potents i permeten analitzar taules amb condicions complexes.
  • Les fórmules matricials consumeixen molts recursos de l'ordinador, s'han d'usar amb moderació i quan no hi hagi una fòrmula nadiua d'Excel que serveixi.
  • No es pot avaluar una columna sencera tipus "A:B", s'ha de definir un rang amb un límit definit de files com "A1:B3000".
  • Les fórmules matricials poden avaluar matrius verticals (fila a fila) o horitzontals (columna a columna).
  • Per a que una fórmula s'avalui de forma matricial s'ha d'introduïr prement "Ctrl"+"Majúscules"+"Enter".
Bé, entrem una mica en detall de com treballa Excel amb la fórmula. Tindrem una taula que pot ser tan gran com vulguem i li posarem unes condicions sobre algunes columnes o files. Cada cop que introduim una condició l'Excel es fa un mapa de la columna tipus (veritat, fals, fals, veritat, veritat, fals,...). Si volem posar concions addicionals l'Excel anirá superposant aquestes matrius de condicions:
Exemple: (veritat, fals, fals, veritat, fals) i (fals, veritat, fals, veritat, fals) = (fals, fals, fals, veritat, fals)
Cal remarcar que l'ordre de les condicions és important, ja que en una condició tipus "i" cada condició actúa com a filtre de les següents.
Exemple: (veritat, fals, fals, veritat, fals) o (fals, veritat, fals, veritat, fals) = (veritat, veritat, fals, veritat, fals)
Una condició tipus "o" fa que l'Excel faci una suma de les matrius.

Normalment posarem diverses condicions comparant-les amb una referència:
  • > més gran que
  • < menor que
  • = igual que
  • >= més gran o igual que
  • <= més petit o igual que
  • <> diferent que
El operadors que podem fer servir són:
  • * operador "i"
  • + operador "o"
  • { } com a matriu de condicions "o" (separador ; per files i \ per columnes)
La sintaxi és la següent (recordeu que els claudàtors els posa l'Excel):
{=FÓRMULA(SI(((condició 1a)+(condició 1b))*(condició 2);(rang valors)))}

{=FÓRMULA(SI((condició={1a ; 1b})*(condició 2);(rang valors)))}
Es llegeix: si es compleix la condició 1a o la condició 1b i també es compleix la condició 2 llavors aplica la fórmula sobre el rang de valors. Les dues fórmules són iguals si les dades estan en columnes i s'avalua fila a fila.
Exemple real de fórmula:
{=SUMA(SI((B5:F5="b")*(B6:F6={"blau"\"vermell"});B8:F8))}
L'avantatge de les fórmules matricials també resideix en que no només permeten fer sumes complexes sinó que admet altres funcions com promitg, totes les mitjanes, mínim, màxim, comptar, k èssim major o menor, percentil i encara me'n dec deixar alguna.

Existeix un cas particular per calcular sumes matricials on ens podem estalviar el condicional:
{=SUMA((condició={1a ; 1b})*(condició 2)*(rang valors))}

{=SUMA((condició={1a ; 1b})*(condició 2)*(rang valors<>0))}
A la primera fórmula sumem els valors del rang de valors i a la segona comptem els valors diferents a 0.

Fitxer amb exemples: Fórmules matricials II
---------------------------------------------

Comprovar si un arxiu existeix

Suposem que tenim un informe dissenyat per executar-se diàriament i genera una sèrie de fitxers de resultats amb diferents dades automàticament. Aquests fitxers es guarden a directoris específics que estan definits dins la macro d’exportació.
La realitat ens diu que al final aquest informe s’executa més d’un cop al dia i el que es vol és no tornar a generar els fitxers de resultats sinó actualitzar-los. De fet, a la primera execució es poden haver generat fitxers o no. Com sabem si un fitxer existeix per obrir-lo i actualitzar-lo?
Per fer-ho farem servir un objecte que anirem veient sovint quan volem accedir a les funcions de sistema, el FileSystemObject. Aquest objecte ens permet operar amb Windows fent servir escrits de sistema.

Exemple:
Sub obrir_fitxer_si_existeix()
Dim Arxiu As String
Arxiu = "C:\test.xls"
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(Arxiu) Then
    Workbooks.Open FileName:=Arxiu
Else
    Workbooks.Add
End If

End Sub

Aquest exemple obre l’arxiu test.xls si existeix o en crea un de nou.

Macro que esborra una altra macro

Potser algun cop voldrem una macro que es pugui executar només un sol cop. O potser voldrem que sota unes determinades condicions s’elimini una macro. Doncs bé, aquest codi us pot ajudar.

Sub EliminaMacro(macro as string, mòdul as string)

Dim liDeb, NbLi

With ThisWorkbook.VBProject.VBComponents(mòdul).CodeModule
    liDeb = .ProcStartLine(macro, 0)
    NbLi = .ProcCountLines(macro, 0)
    .DeleteLines liDeb, NbLi
End With

End Sub
La crida de la macro es fa des d’una altra macro:
EliminaMacro "[nom_de_la_macro]" , "[nom_del_mòdul]"
Aquesta macro elimina només la macro especificada dins el mòdul especificat, deixa totes les altres línies de codi intactes. Com és una subrutina amb variables la macro queda oculta i no es pot executar des del menú Macro > Macros… (Alt+F8). També podem eliminar les variables i posar el nom del mòdul i de la macro manualment als llocs especificats, d’aquesta manera la macro es faria visible. Si volem quie la macro s’elimini ella mateixa només hem de especificar el seupropi nom.

Introducció a les macros

Molts de vosaltres m’heu demanat algun cop que us ensenyi a fer macros, d’altres no voleu que macros als arxius d’Excel perquè no veieu què fan i no ho podeu controlar. En aquesta sèrie d’articles intentaré que aprengueu a dominar les macros i us hi sentiu a gust.
Hi ha diverses raons per decidir-nos a fer macros. Una tasca repetitiva en el temps, una feina puntual però amb un alt grau de repeticions o una feina que necessiti obligatòriament una macro per a poder-la fer.
Per aprendre a fer macros el que normalment es fa és gravar una sèrie d’accions. Això ho fem mitjançant el menú Herramientas > Macros > Gravar nueva macro… Això ens dona una base de treball, però no sempre és suficient. La majoria de vegades haurem de passar per l’editor de VBA [botó] (Alt + F11). Una macro és una rutina de codi de Visual Basic for Applications (és un Visual Basic adaptat a Office), per això quan haguem de modificar una macro ho farem amb Visual Basic.
La finestra d’edició de VBA és aquesta:


Aquí podreu veure el codi que s’ha escrit al gravar la macro. A l’esquerra es mostren els projectes (llibres Excel) i a la dreta hi ha el codi que pot estar als mòduls de codi, formularis, fulles o al llibre.
Si observeu detingudament el codi generat veureu que és bastant senzill d’entendre. Tot i així hi ha dos problemes que apareixen sovint. En gravar una macro les errades que fem i totes les ordres intermèdies que fem amb el cursor (com desplaçar la pàgina) també hi queden gravades i obtenim un excés de codi inútil. Això fa que el codi “brut” sembli confús i s’executi més lentament. L’altre problema és que veurem que algunes coses no queden gravades i llavors haurem d’escriure el codi nosaltres mateixos.
En resum, gairebé sempre haurem de passar per l’editor de VBA per tenir unes macros potents, flexibles i ben escrites; l’ideal es que es puguin reutilitzar i siguin entenedores.

Les macros són la solució? Macros vs. fórmules

No, les macros no són la solució als problemes que ens podem trobar amb l’Excel, només són una altra eina. Molts cops podem solucionar els problemes puntuals que tinguem amb una bona fórmula o conjunt de fórmules.
Les fórmules tenen diversos avantatges respecte les macros. Són visibles, fàcilment auditables, es calculen automàticament i molts cops són més senzilles. També hem de pensar que l’Excel està optimitzat per fer-lo servir amb les fórmules i procediments que té incorporats, si ens n’inventem de nous per fer el mateix probablement siguin més lents.
Tot i així les fórmules tenen un problema molt evident: costen de llegir. Quan tenim fórmules bastant complexes amb funcions imbricades dins altres funcions molts cops ens perdem degut a la quantitat d’opcions disponibles. Més endavant donarem consells per solucionar aquest petit inconvenient.