Molt sovint, cal calcular el resultat final de diverses combinacions de dades d’entrada. Així, l’usuari serà capaç d’avaluar totes les opcions possibles d’acció, seleccionar aquells que el seu resultat d’interacció li satisfaci i, finalment, triar l’opció més òptima. A Excel, hi ha una eina especial per a aquesta tasca: "Taula de dades" ("Taula de cerca"). Descobrim com utilitzar-lo per realitzar els escenaris anteriors.
Vegeu també: Selecció de paràmetres a Excel
Ús de la taula de dades
Eina "Taula de dades" està dissenyat per calcular el resultat amb diferents variacions d’una o dues variables definides. Després del càlcul, apareixeran totes les opcions possibles en forma de taula, que s'anomena matriu de l'anàlisi de factors. "Taula de dades" es refereix a un grup d’eines Anàlisi "Què-si"que es col·loca a la cinta de la pestanya "Dades" en bloc "Treballant amb dades". Abans d’Excel 2007, aquesta eina portava un nom. "Taula de cerca"que reflectia encara més exactament la seva essència que el nom actual.
La taula de cerca es pot utilitzar en molts casos. Per exemple, una opció típica és quan necessiteu calcular l’import d'un pagament del préstec mensual amb diferents variacions del període d’acreditació i l’import del préstec, o el període d’acreditació i el tipus d’interès. Aquesta eina també es pot utilitzar quan s'analitzen models de projectes d'inversió.
Però també haureu de ser conscient que l’ús excessiu d’aquesta eina pot provocar una frenada del sistema, ja que les dades es recalculen constantment. Per tant, es recomana no utilitzar aquesta eina en petits taulers tabulars per resoldre problemes similars, sinó aplicar la còpia de fórmules mitjançant el marcador de farciment.
Aplicació justificada "Taules de dades" només es troba en grans rangs tabulars, quan la còpia de fórmules pot trigar una gran quantitat de temps, i durant el procediment en si, augmenta la probabilitat d’errors. Però fins i tot en aquest cas, es recomana desactivar el càlcul automàtic de les fórmules de l’interval de la taula de cerca, per evitar una càrrega innecessària al sistema.
La diferència principal entre els diferents usos d'una taula de dades és el nombre de variables implicades en el càlcul: una variable o dues.
Mètode 1: utilitzeu l'eina amb una variable
Immediatament considerem l’opció quan s’utilitza una taula de dades amb un valor de variable. Prengui l’exemple més típic de préstecs.
Per tant, actualment se'ns ofereixen les següents condicions de crèdit:
- Període de préstec: 3 anys (36 mesos);
- Import del préstec: 900.000 rubles;
- Tipus d’interès: 12,5% anual.
Els pagaments es fan al final del període de pagament (mes) mitjançant el sistema de rendes anuals, és a dir, en parts iguals. Al mateix temps, al començament de tot el període de préstec, els pagaments d'interessos representen una part significativa dels pagaments, però a mesura que es redueix el cos, els pagaments d'interessos disminueixen i la quantitat d'amortització del propi cos augmenta. El pagament total, com es va esmentar anteriorment, no es modifica.
Cal calcular quin serà l’import del pagament mensual, que inclou l’amortització del cos del préstec i els pagaments d’interessos. Per això, Excel té un operador PMT.
PMT Pertany a un grup de funcions financeres i la seva tasca és calcular el pagament del préstec mensual del tipus de rendes en funció de l'import del cos del préstec, el termini del préstec i el tipus d'interès. La sintaxi per a aquesta funció és la següent.
= PMT (tipus; nper; ps; bs; tipus)
"Aposta" - L'argument que determina el tipus d'interès dels pagaments del crèdit. L’indicador està definit per al període. El nostre període de pagament és d'un mes. Per tant, la taxa anual del 12,5% s'hauria de desglossar en el nombre de mesos d'un any, és a dir, 12.
"Kper" - L’argument que determina el nombre de períodes del període sencer del préstec. En el nostre exemple, el període és d'un mes i el període de préstec és de 3 anys o 36 mesos. Així, el nombre de períodes serà primerenc.
"PS" - l’argument que determina el valor actual del préstec, és a dir, la mida del cos del préstec en el moment de la seva emissió. En el nostre cas, aquesta xifra és de 900.000 rubles.
"BS" - un argument que indiqui la mida del cos del préstec en el moment del pagament complet. Naturalment, aquest indicador serà igual a zero. Aquest argument és opcional. Si el saltem, se suposa que és igual al nombre "0".
"Tipus" - també argument opcional. Informa sobre quan es farà el pagament: al començament del període (paràmetre - "1") o al final del període (paràmetre - "0"). Com recordem, el nostre pagament es realitza al final del mes natural, és a dir, el valor d'aquest argument serà igual a "0". Però, atès que aquest indicador no és obligatori, i per defecte, si no s’utilitza, se suposa que el valor és "0", llavors en l’exemple especificat no es pot utilitzar en absolut.
- Per tant, procedim al càlcul. Seleccioneu la cel·la del full on es mostrarà el valor calculat. Fem clic al botó "Insereix la funció".
- Comença Auxiliar de funcions. Feu la transició a la categoria "Financer", escolliu entre la llista el nom "PLT" i feu clic al botó "D'acord".
- A continuació, hi ha una activació de la finestra d’arguments de la funció anterior.
Poseu el cursor al camp "Aposta"feu clic a la cel·la del full amb el valor del tipus d'interès anual. Com podeu veure, les coordenades es mostren immediatament al camp. Però, com recordem, necessitem una tarifa mensual i, per tant, dividim el resultat per 12 (/12).
Al camp "Kper" de la mateixa manera, introduïm les coordenades de les cel·les del terme de crèdit. En aquest cas, no cal dividir res.
Al camp "Ps" heu d’especificar les coordenades de la cel·la que continguin el valor del cos de crèdit. Ho fem. També posem un signe davant de les coordenades que es mostren. "-". El punt és que la funció PMT de manera predeterminada, dóna el resultat final amb un signe negatiu, tenint en compte la pèrdua del pagament del préstec mensual. Però per claredat, necessitem que la taula de dades sigui positiva. Per tant, posem una marca "menys" abans d’un dels arguments de la funció. Com és sabut, la multiplicació "menys" on "menys" finalment dóna més.
Als camps "B" i "Tipus" No introduïm dades en absolut. Fem clic al botó "D'acord".
- Després d'això, l'operador calcula i mostra a la cel·la prèviament designada el resultat del pagament mensual total: 30108,26 rubles. Però el problema és que el prestatari pot pagar un màxim de 29.000 rubles al mes, és a dir, haurà de trobar un banc que ofereixi condicions amb un tipus d'interès més baix, o bé reduir el cos del préstec o allargar el termini del préstec. Calculeu les diferents opcions d’acció que ens ajudaran a la taula de cerca.
- Per començar, utilitzeu la taula de cerca amb una variable. Vegem com el valor del pagament mensual obligatori variarà amb diferents variacions de la taxa anual, que oscil·la entre 9,5% anual i final 12,5% pa amb pas 0,5%. Totes les altres condicions no es modifiquen. Dibuixeu un rang de taula, els noms de les quals es corresponguin amb diferents variacions del tipus d’interès. Amb aquesta línia "Pagaments mensuals" deixeu-ho tal com és. La seva primera cel·la hauria de contenir la fórmula calculada anteriorment. Per obtenir més informació, podeu afegir línies "Import total del préstec" i "Interès total". La columna en què es troba el càlcul es fa sense capçalera.
- A continuació, calculem l’import total del préstec en les condicions actuals. Per fer-ho, seleccioneu la primera cel·la de la fila. "Import total del préstec" i multipliqueu el contingut de la cel·la "Pagament mensual" i "Termini de préstec". Després d'això, feu clic a Introduïu.
- Per calcular l’import total dels interessos en les condicions actuals, restem el valor del cos del préstec de l’import total del préstec. Per mostrar el resultat a la pantalla, feu clic al botó. Introduïu. Així, obtenim l’import que paguem al retornar el préstec.
- Ara és hora d’aplicar l’eina. "Taula de dades". Seleccioneu tota la matriu de taules, excepte els noms de les files. Després, aneu a la pestanya "Dades". Feu clic al botó de la cinta Anàlisi "Què-si"que es col·loca en un grup d’eines "Treballant amb dades" (en Excel 2016, un conjunt d'eines "Previsió"). Després s'obre un petit menú. En ella seleccionem la posició "Taula de dades ...".
- S'obrirà una petita finestra, que es diu "Taula de dades". Com podeu veure, té dos camps. Com que treballem amb una variable, només necessitem un. Com que els nostres canvis de variables es produeixen en columnes, utilitzarem el camp "Substituïu valors per columnes a". Posem el cursor allà i després feu clic a la cel·la del conjunt de dades inicial, que conté el valor actual del percentatge. Després de mostrar les coordenades de la cel·la al camp, feu clic al botó "D'acord".
- L'eina calcula i emplena tot el rang de taules amb valors que corresponen a diferents opcions de tipus d'interès. Si col·loqueu el cursor en qualsevol element d’aquest espai de taules, podeu veure que la barra de fórmules no mostra una fórmula de càlcul de pagaments regular, sinó una fórmula especial d’una matriu sense ruptura. És a dir, ja no és possible canviar els valors de cèl·lules individuals. Esborreu els resultats del càlcul només es poden fer junts i no separadament.
A més, es pot destacar que el valor del pagament mensual del 12,5% anual, obtingut mitjançant l'aplicació de la taula de cerca, correspon al valor del mateix tipus d'interès que hem rebut aplicant la funció. PMT. Això demostra una vegada més la correcció del càlcul.
Després d’analitzar aquest quadre tabular, cal dir que, com veiem, només a un ritme del 9,5% anual, s’obté el nivell de pagament mensual acceptable (menys de 29.000 rubles).
Lliçó: Càlcul del pagament de rendes a Excel
Mètode 2: utilitzeu una eina amb dues variables
Per descomptat, és molt difícil, si escau realista, trobar bancs que emetin préstecs al 9,5% anual. Per tant, vegem quines opcions hi ha per invertir en un nivell acceptable de pagament mensual per a diverses combinacions d'altres variables: la mida del cos del préstec i el període de préstec. Al mateix temps, el tipus d'interès es mantindrà sense canvis (12,5%). L'eina ens ajudarà amb aquesta tasca. "Taula de dades" utilitzant dues variables.
- Dibuixa una nova matriu de taules. Ara el terme de crèdits s'indicarà als noms de columna (de 2 fins a 6 anys en mesos en passos d’un any) i en les files: la mida del cos del préstec (de 850000 fins a 950000 rubles en increments 10000 rubles). En aquest cas, és imprescindible que la cel·la en què es troba la fórmula de càlcul (en el nostre cas PMT), situat a la vora dels noms de fila i columna. Sense aquesta condició, l’eina no funcionarà quan utilitzeu dues variables.
- A continuació, seleccioneu tot l’interval de taula resultant, inclosos els noms de les columnes, les files i la cel·la amb la fórmula PMT. Aneu a la pestanya "Dades". Com en el cas anterior, feu clic al botó. Anàlisi "Què-si"en un grup d’eines "Treballant amb dades". A la llista que s’obre, seleccioneu l’element "Taula de dades ...".
- S'inicia la finestra de l’eina. "Taula de dades". En aquest cas, necessitem els dos camps. Al camp "Substituïu valors per columnes a" especifiquem les coordenades de la cel·la que conté el termini del préstec a les dades primàries. Al camp "Substituïu els valors per files" especifiqueu l'adreça de la cel·la dels paràmetres inicials que continguin el valor del cos del préstec Després d’introduir totes les dades. Fem clic al botó "D'acord".
- El programa realitza el càlcul i omple el rang de taules amb dades. A la intersecció de files i columnes, ara es pot observar com serà exactament el pagament mensual, amb una quantitat corresponent d'interessos anuals i un període de crèdit especificat.
- Com podeu veure, molts valors. Per solucionar altres problemes, pot haver-hi encara més. Per tant, per tal de fer més visual la sortida dels resultats i determinar immediatament quins valors no satisfan la condició donada, podeu utilitzar eines de visualització. En el nostre cas, serà un format condicional. Seleccioneu tots els valors de l’abast de la taula, excloent-hi les capçaleres de fila i columna.
- Aneu a la pestanya "Inici" i feu clic a la icona "Format condicional". Es troba a la caixa d’eines. "Estils" a la cinta. Al menú que s'obre, seleccioneu l’element "Regles per a la selecció de cel·les". A la llista addicional, feu clic a la posició "Menys ...".
- A continuació, s'obrirà la finestra de configuració de format condicional. Al camp de l’esquerra s’especifica el valor, menys del que s’escolliran les cel·les. Com recordem, estem satisfets amb la condició en què el pagament mensual del préstec serà menor 29000 rubles. Introduïu aquest número. Al camp de la dreta és possible seleccionar el color de la selecció, tot i que podeu deixar-lo per defecte. Un cop introduïdes tots els paràmetres necessaris, feu clic al botó. "D'acord".
- Després d'això, totes les cel·les els valors corresponents a la condició anterior es ressaltaran en color.
Després d’analitzar l’array de taules, podeu treure algunes conclusions. Com podeu veure, amb el període de préstec actual (36 mesos), per tal d’invertir en l’import indicat d’un pagament mensual, necessitem un préstec que no excedeixi els 8.600.000,00 rubles, és a dir, menys de 40.000 del previst.
Si seguim pensant en un préstec per un import de 900.000 rubles, el termini del préstec hauria de ser de 4 anys (48 mesos). Només en aquest cas, l'import del pagament mensual no superarà el límit establert de 29.000 rubles.
Així, aprofitant aquesta taula tabular i analitzant els pros i els contres de cada opció, el prestatari pot prendre una decisió específica sobre els termes del préstec, triant la opció que millor s'adapti a les seves necessitats.
Per descomptat, la taula de cerca es pot utilitzar no només per calcular les opcions de crèdit, sinó també per resoldre molts altres problemes.
Lliçó: Format condicional a Excel
En general, cal assenyalar que la taula de cerca és una eina molt útil i relativament senzilla per determinar el resultat de diverses combinacions de variables. Mitjançant l’aplicació d’un format condicional amb ell, a més, podeu visualitzar la informació rebuda.
