Si treballes sovint amb fulls de càlcul, segur que més d'una vegada has pensat alguna cosa com: “sigues el resultat que vull, però no tinc ni idea de quin número he de posar per arribar-hi”. Just per això existeix Buscar Objectiu en Excel , una eina que t'estalvia el mètode de prova i error canviant valors una vegada i una altra fins a encertar.
Gràcies a aquesta funció, pots demanar a l'Excel que esbrini per tu quin valor ha de prendre una cel·la perquè una fórmula arribi a un resultat específic : la quota d'un préstec que t'encaixi, la nota mínima per aprovar, el benefici que vols aconseguir o el capital que pots demanar prestat amb un pagament màxim concret.
Què és Cerca Objectiu en Excel i per a què serveix?
L'eina Cerca Objectiu forma part del grup d' anàlisi “I si…” d'Excel (juntament amb l'Administrador d'escenaris i les Taules de dades). A diferència de l'ús habitual de fórmules, on parts d'unes dades d'entrada per obtenir un resultat, el procés s'inverteix aquí: defineixes el resultat desitjat i deixes que Excel calculi quin valor ha de prendre una variable per aconseguir-ho.
En termes senzills, Buscar Objectiu et permet resoldre equacions amb una sola incògnita sense necessitat de refusar a mà. La condició clau és que la cel·la el valor de la qual vulguis trobar aparegui dins de la fórmula que genera el resultat que marques com a objectiu; altrament, l'eina no pot fer el càlcul.
Dins l'ecosistema d'anàlisi “I si…”, Cerca Objectiu destaca per ser molt ràpida i fàcil quan només vols ajustar una variable. Per a situacions més complexes, amb diverses incògnites i restriccions (per exemple, maximitzar un guany modificant diversos elements alhora), l'alternativa adequada és el complement Solver , que amplia molt les possibilitats.
Conceptes clau de Cerca Objectiu
Per utilitzar aquesta eina sense embolics convé tenir clars tres elements fonamentals: cel·la objectiu, valor desitjat i cel·la que canvia . Entendre bé aquesta relació és el que marca la diferència entre que Cerca Objectiu funcioni a la primera o et torni resultats estranys.
La cel·la objectiu és la que conté la fórmula el resultat de la qual vols fixar en un valor concret. Sol ser, per exemple, la mitjana de les notes, l'import d'una quota, el benefici total d'un producte o el valor d'un indicador financer. Aquesta cel·la no la escriuràs a mà, sinó que ja té una fórmula construïda i és el “resultat final” que t'interessa.
El valor desitjat (camp “Valor per a” o “A valor”) és el nombre que voleu que prengui aquesta cel·la objectiu. Pot ser una nota mínima per aprovar, un benefici concret, una quota màxima que podeu pagar o un percentatge determinat de rendibilitat. Aquest número ho decideixes tu i l'introdueixes directament al quadre de diàleg de Cerca Objectiu.
La cel·la que canvia és la incògnita: la dada que l'Excel anirà modificant automàticament fins a aconseguir que la cel·la objectiu assoleixi el valor desitjat. Ha de ser una cel·la inclosa a la fórmula de la cel·la objectiu; si no forma part del càlcul, Cerca Objectiu no tindrà forma de relacionar un canvi en aquesta cel·la amb el resultat final.
On és Cerca Objectiu en Excel
La ubicació de l'eina és sempre la mateixa a les versions modernes d'Excel: es troba a la pestanya Dades , dins del grup o secció de Previsió (o “Eines de dades” en algunes traduccions), al menú desplegable Anàlisi d'hipòtesis . Dins aquest menú apareixen les tres opcions principals i una és “Cerca objectiu”.
Si preferiu dreceres de teclat, en moltes versions d'Excel al Windows podeu accedir amb la combinació Alt + A + W + G , que obre directament el quadre de diàleg de l'eina. Altres entorns de fulls de càlcul compatibles, com ONLYOFFICE Docs , han incorporat també una funció de Cerca Objectiu molt similar, ubicada igualment a la pestanya de Dades.
Com funciona Cerca Objectiu: paràmetres del quadre de diàleg?
Quan obris Cerca Objectiu, apareix un quadre amb tres camps que cal completar correctament perquè l'Excel faci el càlcul. Entendre què és cadascun evita la majoria d'errors típics i et permet treure partit de l'eina en tota mena de contextos.
Al camp “Establir cel·la” has d'indicar la referència de la cel·la que conté la fórmula el resultat de la qual vols fixar. És sempre una cel·la amb fórmula, no un número escrit a mà. Per exemple, pot ser la cel·la on teniu el benefici total, la mitjana de notes o la quota d'un préstec.
Al camp “Valor per” escrius el resultat que vols aconseguir en aquesta cel·la. Aquí hi ha el valor objectiu: una quantitat de diners, una nota mínima, una mica per cent… Per exemple, 50 per a un 50% de nota final, 50.000 per a un benefici, o -900 si estàs ajustant un pagament de préstec que comptablement es consideren fluxos de sortida i van amb signe negatiu.
Al camp “Canviant cel·la” selecciones la cel·la el contingut de la qual Excel modificarà automàticament. Aquesta cel·la ha d'aparèixer, de manera directa o indirecta, a la fórmula de la cel·la objectiu. Segons el cas, pot ser un nombre d'unitats venudes, un preu unitari, la quantitat del préstec, la durada en mesos o la nota d'un examen pendent.
Exemple financer 1: Calculeu la taxa d'interès amb PAGAMENT
Un ús molt habitual de Cerca Objectiu és en càlculs de préstecs quan coneixes l'import que vols demanar, el termini i la quota que et pots permetre, però desconeixes quin tipus d'interès necessitaries perquè tot quadri. Excel permet resoldre aquest problema de manera directa combinant la funció PAGAMENT (PMT en anglès) amb Cerca Objectiu.
Imagina que vols un préstec de 100.000 euros, a tornar en 180 mesos (15 anys), i saps que només pots pagar uns 900€ al mes . Et falta la taxa d'interès anual que t'hauria d'oferir el banc perquè la quota mensual sigui just aquest import o molt proper.
Primer prepares un full senzill amb etiquetes que identifiquin bé cada dada: Import del préstec , Termini en mesos , Taxa d'interès i Pagament . En una columna escrius els textos (per exemple, a A1:A4) ia la columna del costat (B1:B4) vas introduint els valors que ja coneixes: 100000 com a import, 180 com a mesos, deixes buida de moment la taxa ia la fila del pagament col·locaràs la fórmula.
A la cel·la del pagament introduïu la funció =PAGAMENT(B3/12;B2;B1) . Divideixes la cel·la de la taxa (B3) entre 12 perquè la funció PAGAMENT assumeix una taxa d'interès periòdica i estàs treballant amb pagaments mensuals a partir d'una taxa anual. B2 conté els mesos i B1 el capital inicial.
Com que B3 encara està en blanc, Excel calcula el pagament suposant una taxa del 0%, per la qual cosa veuràs un valor de quota que no s'ajusta a la realitat (al voltant de 555,56 € en aquest exemple). Aquesta dada provisional ho ignoraràs, perquè ara entra en joc Buscar Objectiu per ajustar la taxa.
Obres Dades > Anàlisi d'hipòtesis > Buscar objectiu i configures de la manera següent: a “Establir cel·la” selecciones la cel·la del pagament (on està la fórmula PAGAMENT), a “Valor per” escrius -900 (quota negativa en tractar-se d'un pagament) ia “Canviant cel·la” selecciones la cel·la de la taxa d'interès (B3). En acceptar, l'Excel realitza iteracions i et torna la taxa d'interès necessària perquè la quota sigui 900.
El més habitual és que aquesta taxa aparegui com a número decimal, per la qual cosa convé aplicar format de percentatge a la cel·la d'interès: selecciones la cel·la, vas a Inici > grup Número, prems el format de percentatge i ajustes el nombre de decimals (pujar o baixar decimals) segons el nivell de detall que vulguis mostrar.
Exemple pràctic 2: nota mínima per aprovar una assignatura
Un altre escenari clàssic en què Buscar Objectiu ve de luxe és el de les qualificacions acadèmiques . Saps quines notes tens als exàmens ja realitzats, coneixes la mitjana que t'exigeixen per aprovar o mantenir una beca, però et falta la incògnita clau: quina nota necessites treure a l'examen que et queda.
Suposa que ja has fet tres de quatre proves i necessites una mitjana de 70 punts per poder presentar-te a l'examen final o conservar una determinada condició acadèmica. Vols calcular quina puntuació has d'aconseguir a l'últim examen per assolir aquesta mitjana de 70.
Muntes una taula amb les notes obtingudes i deixes la cel·la corresponent a l'últim examen buida. En una cel·la a part calcules la mitjana general mitjançant la fórmula =MITJÀ(rang) o =AVERAGE(rang) (depenent de l'idioma del teu Excel), incloent la cel·la de l'examen pendent encara que encara no tingui valor definitiu.
Un cop preparat això, obris l'eina Cerca Objectiu. A “Establir cel·la” tries la cel·la on has calculat la mitjana total, que és el resultat sobre el qual vols actuar. A “Valor para” escrius 70, que és la mitjana desitjada. I a “Canviant cel·la” selecciones la cel·la de la nota de l'últim examen.
En prémer Acceptar, l'Excel anirà provant diferents valors per a aquesta última nota fins que la mitjana del rang sigui exactament 70 (o el més proper possible segons els paràmetres d'iteració). El resultat pot ser, per exemple, que necessitis un 85 per assolir aquesta mitjana o que n'hi hagi prou amb un 60; tot dependrà de les notes prèvies i de si hi ha ponderacions o no.
Buscar Objectiu amb ponderacions i SUMAPRODUCTE
En molts cursos les diferents activitats no pesen el mateix a la nota final: hi ha proves contínues amb pes baix, exàmens parcials que compten una mica més i un examen final amb un gran percentatge. En aquests casos, la nota final no es calcula amb una simple mitjana, sinó amb una mitjana ponderada.
Per construir aquesta mitjana ponderada a Excel és molt útil la funció SUMAPRODUCTE (SUMPRODUCT en anglès), que multiplica cada nota per la seva ponderació i suma tots aquests productes, normalment dividit entre la suma de les ponderacions.
La idea és senzilla: crees una taula on a una columna tinguis les qualificacions (examen 1, treball, parcial, final…) i en una altra columna els pesos o percentatges que correspon a cadascuna. En una cel·la a part, calcules la nota global usant alguna cosa com =SUMAPRODUCTE(rang_notes;rang_pesos)/SUMA(rang_pesos) , de manera que obtens la mitjana ponderada.
Si el que us interessa és saber quina nota mínima necessiteu en un examen concret (per exemple, el final) perquè la nota global arribi a un 50% , podeu deixar la cel·la d'aquest examen sense definir i tornar a acudir a Cerca Objectiu. A “Establir cel·la” marques la cel·la de la nota final calculada amb SUMAPRODUCTE, a “Valor para” indiques 50, ia “Canviando cel·la” selecciones la qualificació de l'examen pendent. Excel ajustarà aquesta nota fins que la mitjana ponderada arribi al 50%.
En un cas real d'aquest tipus, és possible que l'eina us indiqui un valor no rodó, com el 57,5% per a l'examen final. Amb aquest resultat sabràs exactament què necessites treure, i ja et pots fer els teus propis càlculs de com vas apurat.
Exemples dús amb préstecs i crèdits
L'àmbit financer és un dels més potents per aplicar Cerca Objectiu perquè molts problemes es redueixen a trobar un valor que fa que una fórmula de pagaments, interessos o fluxos de caixa doni un resultat concret. Amb funcions com PAGAMENT i altres càlculs financers, la combinació és molt versàtil.
Imagina't que tens un préstec a 20 anys, amb una taxa d'interès del 5% i un capital de, diguem-ne, 12.000 unitats monetàries. Heu calculat la quota anual amb la funció PAGAMENT i us surt un import al voltant de 962,91 cada any, però el vostre pressupost màxim per a la quota anual és de 900. Et preguntes llavors quin és l'import màxim de préstec que podries assumir amb una quota de 900 a l'any mantenint aquest termini i aquest tipus d'interès.
En aquest cas, pots fer servir Cerca Objectiu indicant com a “Establir cel·la” la cel·la del pagament calculat amb PAGAMENT, a “Valor per” escrius -900 (negatiu per ser un pagament) ia “Canviant cel·la” tries la cel·la que conté l'import del préstec. Excel modificarà aquesta quantitat fins que el pagament resulti exactament 900. És molt probable que obtinguis una cosa propera a 11.216 de capital màxim.
Una altra variant consisteix a fixar el capital en 12.000, mantenir la quota màxima en 900 i intentar esbrinar quants anys necessitaries per pagar el préstec en aquestes condicions. Aquí la cel·la que canvia seria el nombre de períodes (anys, mesos…) al lloc del capital. L'estructura és la mateixa: la cel·la objectiu és la del pagament, el valor desitjat –900, i la cel·la que canvia és la durada. El resultat et donarà un termini una mica més gran que l'original, per exemple uns dos anys i mig més de temps necessari.
Aplicacions de Cerca Objectiu en negocis i anàlisi
Més enllà dels exemples d'estudis i préstecs, l'eina es fa servir moltíssim en modelització de negocis i en anàlisi de dades per plantejar escenaris senzills amb una sola variable. És especialment útil quan vols fer anàlisis de sensibilitat ràpida sense muntar un model complexíssim.
Alguns usos habituals en empreses són el càlcul del punt d'equilibri (nombre d'unitats que has de vendre per no tenir pèrdues), l'ajust de preus per assolir un marge determinat, o la definició de la inversió inicial necessària per assolir una rendibilitat objectiu a partir d'una sèrie de fluxos de caixa.
Per exemple, pots tenir una cel·la de benefici calculada com a (preu unitari – cost unitari) × unitats venudes. Si vols aconseguir un benefici de 50.000 , pots definir com a cel·la objectiu la del benefici, com a valor per a 50000 i com a cel·la que canvia les unitats venudes. Buscar Objectiu et dirà quantes unitats has de vendre amb els preus i els costos actuals.
En altres casos, t'interessa fixar-ne les unitats (per capacitat productiva limitada) i veure quin preu mínim hauries de cobrar per arribar a aquests 50.000 de benefici. Canviaràs aleshores la cel·la variable, passant a ser el preu unitari, mentre que la cel·la objectiu continua sent el benefici i l'objectiu, aquests 50.000. El resultat t'indicarà, per exemple, que hauries de vendre a 200 unitats monetàries per producte per assolir el benefici marcat.
També és freqüent fer servir Cerca Objectiu per a projectes d' inversió on voleu que la TIR o la rendibilitat assoleixi un percentatge concret i modifiqueu la inversió inicial per veure quin import garanteix l'objectiu. És exactament la mateixa lògica: la cel·la objectiu és la que conté la fórmula de rendibilitat, el valor desitjat és el percentatge (per exemple, 15%) i la cel·la que canvia és la inversió o el flux inicial.
ONLYOFFICE i altres plataformes amb funció Buscar Objectiu
La utilitat de Cerca Objectiu no es limita a Excel. Suites ofimàtiques com ONLYOFFICE Docs han incorporat també aquesta eina als seus fulls de càlcul, seguint una interfície molt semblant perquè l'usuari que ve d'Excel no hagi de reaprendre el procés.
A ONLYOFFICE, per exemple, pots plantejar el típic problema de les notes: coneixes les qualificacions de diversos exàmens i vols saber quin resultat necessites a l'última prova per assolir una determinada mitjana. Crees un full amb les teves notes, calcules la mitjana amb la funció =AVERAGE(rang) i després obris l'eina Buscar Objectiu des de la pestanya Dades, on apareix un botó específic per fer-ho.
Al quadre de diàleg, el camp d'“Establir cel·la” s'omple amb la referència de la cel·la que conté la fórmula de la mitjana, a “Valor” anotes la nota final que vols aconseguir ia “En canviar la cel·la” selecciones la cel·la de l'últim examen. Després de prémer OK, el sistema mostrarà una finestra d'estat de Cerca Objectiu amb la solució trobada i us permetrà reemplaçar els valors de les cel·les implicades amb un clic.
A més, eines com ONLYOFFICE solen acompanyar aquestes funcions d'un conjunt ampli de característiques extra ( col·laboració en temps real , editor en línia, integració amb altres plataformes) que fan que la cerca d'objectius s'integri en fluxos de treball més complexos, tant en empreses com en entorns educatius.
Buscar Objectiu davant Solver: quan fer servir cadascun
Buscar Objectiu és perfecta quan tens un sol valor desconegut i una única fórmula que vols ajustar, però es queda curta en el moment que entren en joc diverses variables i restriccions . Aquí és on brilla el complement Solver , una altra eina d'optimització més avançada que també ofereix Excel.
Solver s'instal·la des de Fitxer > Opcions > Complements. A la part inferior tries “Complements d'Excel” i prem “Ir”. Actives Solver , acceptes i, després d'això, apareix a la pestanya Dades dins de la secció Anàlisi. A diferència de Buscar Objectiu, Solver permet treballar amb diverses cel·les variables, establir condicions de màxim o mínim i aplicar múltiples restriccions (major o igual, menor o igual, igual a determinats valors, etc.).
Imagina que vols saber quina nota necessites en dues assignatures (per exemple, anglès i química) perquè la teva mitjana sigui de 84. Buscar Objectiu no serveix aquí perquè només pots manipular una cel·la que canvia. En canvi, amb Solver defineixes com a cel·la objectiu la mitjana, com a valor objectiu 84 i com a cel·les que canvien les dues notes desconegudes. En absència de restriccions addicionals, Solver et proposarà una combinació de qualificacions (per exemple, 87 a les dues assignatures) que aconsegueix aquesta mitjana.
Un altre exemple típic de Solver és la maximització de guanys amb restriccions . Una empresa ven diversos serveis (A, B, C…) i sap quants ha venut de cadascú, quin preu té cada servei i quin és el guany total. El full suma les unitats venudes i calcula el guany total com a suma dunitats × guany per servei. Volen esbrinar com haurien de redistribuir les vendes entre serveis per guanyar-ne més, però respectant certes condicions.
Suposa que els requisits són: vendre almenys 25 unitats del Servei A , no vendre més de 15 del Servei C i no superar els 70 serveis venuts en total al mes. Amb Solver fixes com a cel·la objectiu el guany total (per exemple, D8), li dius que la maximitzi , tries com a cel·les variables les unitats de cada servei (un rang tipus B4:B7) i afegeixes les restriccions: B4 major o igual que 25, B6 menor o igual que 15, B8 (total d'unitats) menor o igual.
En prémer Resolver, Solver cerca una combinació d'unitats que compleixi totes les condicions i obtingui el guany màxim possible. Si trobeu solució, podeu acceptar-la i deixar els nous valors escrits al full. És una manera molt poderosa de fer anàlisis de sensibilitat empresarial i planificació de recursos.
Problemes habituals: per què Cerca Objectiu de vegades no funciona
Tot i que l'eina és molt robusta, de vegades pot semblar que Cerca Objectiu no fa res o que no troba cap solució. Molts d'aquests casos es deuen a configuracions d'iteració oa errors lògics a les fórmules i paràmetres.
D'una banda, l'Excel limita el nombre d'intents (iteracions) i el canvi mínim que accepta abans d'aturar la cerca. Si aquests límits són massa estrictes, l'eina podria aturar-se abans de trobar un resultat satisfactori o deixar el càlcul massa imprecís. Per revisar-ho, podeu anar a Fitxer > Opcions > Fórmules i ajustar tant el nombre màxim d'iteracions com el “canvi màxim”. Augmentar el nombre d'iteracions permet explorar més solucions possibles, mentre que disminuir el canvi màxim millora la precisió del resultat.
D'altra banda, és freqüent que el problema estigui en els paràmetres mateixos de Cerca Objectiu: una cel·la objectiu que no conté cap fórmula, una cel·la que canvia que no participa en el càlcul, un valor objectiu incoherent amb la lògica del model o fins i tot referències circulars que impedeixen que Excel recalculi correctament. Reviseu que la fórmula funciona normalment abans d'utilitzar Cerca Objectiu sol estalviar molts maldecaps.
També cal tenir en compte que Cerca Objectiu només està pensada per a una variable . Si en realitat necessites ajustar més d'una cel·la alhora o complir diverses condicions simultànies, és correcte passar a Solver o replantejar el model per poder aplicar l'eina dins d'un marc més senzill.
Dominar Cerca Objectiu obre la porta a resoldre en segons un munt de problemes quotidians: des de saber quina nota necessites treure en el proper examen fins a entendre quina quota pots pagar en un préstec, quin volum de vendes et fa assolir una meta de benefici o quina inversió inicial encaixa amb una rendibilitat fixa, i si algun dia et quedes curt perquè necessites diverses variables i restriccions, tindràs “I si…” més avançats. Comparteix aquesta guia perquè més usuaris aprenguin a fer servir la funció de Cerca Objectiu en Excel.