Moitas veces, é necesario calcular o resultado final para varias combinacións de datos de entrada. Así, o usuario poderá evaluar todas as posibles opcións de acción, seleccionar aquelas cuxa interacción resulte satisfeita e, finalmente, elixir a opción máis óptima. En Excel, hai unha ferramenta especial para esta tarefa - "Táboa de datos" ("Táboa de busca"). Descubrimos como usalo para realizar os escenarios anteriores.
Vexa tamén: Selección de parámetros en Excel
Usando a táboa de datos
Ferramenta "Táboa de datos" está deseñado para calcular o resultado con diferentes variacións dunha ou dúas variables definidas. Despois do cálculo, todas as opcións posibles aparecerán en forma de táboa, que se chama matriz de análise de factores. "Táboa de datos" refírese a un grupo de ferramentas Análise "si"que se coloca na cinta na pestana "Datos" en bloque "Traballar con datos". Antes de Excel 2007, esta ferramenta usaba un nome. "Táboa de busca"que reflicte aínda máis exactamente a súa esencia que o nome actual.
A táboa de busca pode usarse en moitos casos. Por exemplo, unha opción típica é cando precisa calcular o importe dun pagamento mensual do préstamo con diferentes variacións do período de acreditación e do importe do préstamo, ou o período de crédito e o tipo de interese. Esta ferramenta tamén se pode empregar ao analizar modelos de proxectos de investimento.
Pero tamén debe ser consciente de que o uso excesivo desta ferramenta pode provocar a freada do sistema, xa que os datos son recalculados constantemente. Polo tanto, recoméndase non usar esta ferramenta en pequenas matrices tabulares para resolver problemas similares, senón aplicar a copia de fórmulas usando o marcador de enchemento.
Aplicación xustificada "Táboas de datos" só se atopa en intervalos tabulares grandes, cando as fórmulas de copia poden levar moito tempo e durante o procedemento en si, a probabilidade de erros aumenta. Pero ata neste caso, recoméndase desactivar o recálculo automático das fórmulas no rango da táboa de busca para evitar cargas innecesarias no sistema.
A principal diferenza entre os distintos usos dunha táboa de datos é o número de variables implicadas no cálculo: unha variable ou dúas.
Método 1: use a ferramenta cunha variable
Inmediatamente consideremos a opción cando se usa unha táboa de datos cun valor de variable. Tome o exemplo máis típico de préstamos.
Polo tanto, actualmente ofrécennos as seguintes condicións de crédito:
- Período de préstamo: 3 anos (36 meses);
- Importe do préstamo - 900000 rublos;
- Tipos de xuros: 12,5% anual.
Os pagamentos realízanse ao final do período de pagamento (mes) utilizando o réxime de renda vitalicia, é dicir, en partes iguais. Ao mesmo tempo, ao comezo de todo o período de préstamo, os pagamentos de xuros constitúen unha parte significativa dos pagamentos, pero a medida que o corpo se contrae, os pagamentos de intereses diminúen e aumenta a cantidade de devolución do propio corpo. O pagamento total, como se mencionou anteriormente, permanece inalterado.
É necesario calcular cal será o importe do pagamento mensual, que inclúe o reembolso do corpo do préstamo e os pagamentos de xuros. Para iso, Excel ten un operador PMT.
PMT Pertence a un grupo de funcións financeiras ea súa tarefa é calcular o pagamento mensual do tipo de renda en función do importe do corpo do préstamo, o prazo do préstamo e o tipo de interese. A sintaxe para esta función é a seguinte.
= PMT (tipo; nper; ps; bs; tipo)
"Aposta" - O argumento que determina a taxa de interese dos pagamentos de crédito. O indicador está definido para o período. O noso período de pagamento é dun mes. Polo tanto, a taxa anual do 12,5% debería dividirse no número de meses dun ano, é dicir, 12.
"Kper" - O argumento que determina o número de períodos durante todo o período do préstamo. No noso exemplo, o período é dun mes eo período de préstamo é de 3 anos ou 36 meses. Así, o número de períodos será o inicio de 36.
"PS" - o argumento que determina o valor actual do préstamo, é dicir, o tamaño do corpo do préstamo no momento da súa emisión. No noso caso, esta cifra é de 900.000 rublos.
"BS" - un argumento que indica o tamaño do corpo do préstamo no momento do pagamento integral. Por suposto, este indicador será igual a cero. Este argumento é opcional. Se o omite, asúmese que é igual ao número "0".
"Tipo" - tamén argumento opcional. Informa sobre cando se fará o pagamento: ao comezo do período (parámetro - "1") ou ao final do período (parámetro - "0"). Como recordamos, o noso pago faise ao final do mes natural, é dicir, o valor deste argumento será igual a "0". Pero, dado que este indicador non é obrigatorio, e por defecto, se non se usa, asúmese o valor "0", entón no exemplo indicado non se pode empregar en absoluto.
- Entón procedemos ao cálculo. Seleccione a cela na folla onde se amosará o valor calculado. Facemos clic no botón "Inserir función".
- Inicia Asistente de funcións. Fai a transición á categoría "Financeira", escolla entre a lista o nome "PLT" e prema no botón "OK".
- Despois diso, hai unha activación da ventá de argumentos da función anterior.
Coloca o cursor no campo "Aposta"a continuación, faga clic na cela da folla co valor da taxa de interese anual. Como podes ver, as súas coordenadas móstranse inmediatamente no campo. Pero, como recordamos, necesitamos unha tarifa mensual e, polo tanto, dividimos o resultado por 12 (/12).
No campo "Kper" do mesmo xeito, introducimos as coordenadas das termas de crédito. Neste caso, non hai que dividir nada.
No campo "Ps" ten que especificar as coordenadas da cela que contén o valor do corpo de crédito. Nós o facemos. Tamén poñemos un sinal diante das coordenadas mostradas. "-". O punto é que a función PMT de xeito predeterminado, dá o resultado final cun sinal negativo, considerando bastante a perda do pago do préstamo mensual. Pero, por claridade, necesitamos que a táboa de datos sexa positiva. Polo tanto, poñemos unha marca "menos" antes dun dos argumentos da función. Como se sabe, a multiplicación "menos" en "menos" eventualmente dá máis.
Nos campos "Bs" e "Tipo" Non introducimos datos. Facemos clic no botón "OK".
- Despois diso, o operador calcula e mostra na cela predefinida o resultado do pagamento mensual total. 30108,26 rublos. Pero o problema é que o prestatario pode pagar un máximo de 29.000 rublos ao mes, é dicir, debería atopar un banco que ofreza condicións cun tipo de interese máis baixo ou reducir o corpo do préstamo ou estender o prazo do préstamo. Calcúlase as distintas opcións de acción que nos axudará a táboa de busca.
- Para comezar, use a táboa de busca cunha variable. Vexamos como o valor do pagamento mensual obrigatorio variará segundo as variacións da taxa anual, que van desde 9,5% anual e final 12,5% pa con paso 0,5%. As outras condicións non se modifican. Debuxe un rango de táboa, cuxos nomes das columnas corresponderán a diferentes variacións da taxa de interese. Con esta liña "Pagamentos mensuais" deixe como está. A súa primeira cela debería conter a fórmula calculada anteriormente. Para máis información, pode engadir liñas "Importe total do préstamo" e "Interese total". A columna na que se atopa o cálculo faise sen unha cabeceira.
- A continuación, calculamos o importe total do préstamo nas condicións actuais. Para iso, selecciona a primeira cela da fila. "Importe total do préstamo" e multiplicar o contido da cela "Pagamento mensual" e "Termo de préstamo". Despois disto prema Intro.
- Para calcular o importe total dos intereses nas condicións actuais, restamos o valor do corpo do préstamo do importe total do préstamo. Para mostrar o resultado na pantalla, fai clic no botón. Intro. Así, obtemos a cantidade que pagamos ao pagar o préstamo.
- Agora é hora de aplicar a ferramenta. "Táboa de datos". Seleccione a matriz completa de táboas, agás os nomes de filas. Despois vaia á pestana "Datos". Fai clic no botón da cinta Análise "si"que se coloca nun grupo de ferramentas "Traballar con datos" (en Excel 2016, un grupo de ferramentas "Previsión"). Despois ábrese un pequeno menú. Nel seleccionamos a posición "Táboa de datos ...".
- Ábrese unha pequena ventá chamada "Táboa de datos". Como podes ver, ten dous campos. Como traballamos cunha variable, só precisamos un deles. Xa que as nosas modificacións de variables ocorren en columnas, usaremos o campo "Substitúe os valores por columnas". Poñemos o cursor alí e, a continuación, fai clic na cela no conxunto de datos inicial, que contén o valor actual de por cento. Despois de mostrar as coordenadas da cela no campo, faga clic no botón "OK".
- A ferramenta calcula e enche todo o rango de táboas con valores que corresponden a diferentes opcións de tipos de interese. Se colocas o cursor en calquera elemento deste espazo de táboas, podes ver que a barra de fórmulas non amosa unha fórmula de cálculo de pagamentos normal, senón unha fórmula especial dunha matriz sen ruptura. É dicir, xa non é posible cambiar os valores en células individuais. Eliminar os resultados do cálculo só poden estar todos xuntos e non por separado.
Ademais, cabo sinalar que o valor do pagamento mensual do 12,5% anual, obtido mediante a aplicación da táboa de busca, corresponde ao valor co mesmo tipo de interese que recibimos aplicando a función PMT. Isto volve demostrar a corrección do cálculo.
Despois de analizar esta matriz tabular, hai que dicir que, como vemos, só a un ritmo do 9,5% anual, obtense o nivel de pagamento mensual aceptable (menos de 29.000 rublos).
Lección: Cálculo do pagamento de renda vitalicia en Excel
Método 2: use unha ferramenta con dúas variables
Por suposto, é moi difícil, se é realista, atopar bancos que emiten préstamos ao 9,5% anual. Polo tanto, imos ver que opcións hai para investir nun nivel aceptable de pagamento mensual para varias combinacións doutras variables: o tamaño do corpo do préstamo eo período de préstamo. Ao mesmo tempo, o tipo de interese permanecerá sen cambios (12,5%). A ferramenta axudarannos a esta tarefa. "Táboa de datos" usando dúas variables.
- Debuxa unha nova matriz de táboas. Agora o termo de créditos será indicado nos nomes das columnas (de 2 ata 6 anos en meses por pasos dun ano) e nas filas: o tamaño do corpo do préstamo (de 850000 ata 950000 rublos en incrementos 10000 rublos). Neste caso, é imperativo que a cela na que se localice a fórmula de cálculo (no noso caso.) PMT), situado no bordo dos nomes de filas e columnas. Sen esta condición, a ferramenta non funcionará cando se utilicen dúas variables.
- A continuación, selecciona todo o intervalo de táboa resultante, incluíndo os nomes das columnas, as filas e a cela coa fórmula PMT. Vaia á pestana "Datos". Como no anterior, fai clic no botón. Análise "si"nun grupo de ferramentas "Traballar con datos". Na lista que se abre, selecciona o elemento "Táboa de datos ...".
- Iníciase a ventá da ferramenta. "Táboa de datos". Neste caso, necesitamos ambos campos. No campo "Substitúe os valores por columnas" especificamos as coordenadas da cela que contén o termo do préstamo nos datos primarios. No campo "Substitúe os valores por filas" especifique a dirección da cela dos parámetros iniciais que conteñan o valor do corpo do préstamo Despois de introducir todos os datos. Facemos clic no botón "OK".
- O programa realiza o cálculo e enche o rango de táboas con datos. Na intersección de filas e columnas, agora é posible observar como será exactamente o pagamento mensual, cun importe correspondente de interese anual e un período de crédito especificado.
- Como podes ver, hai moitos valores. Para resolver outros problemas pode haber aínda máis. Polo tanto, para facer a saída dos resultados máis visual e determinar inmediatamente cales valores non satisfagan a condición dada, pode usar ferramentas de visualización. No noso caso, será un formato condicional. Seleccione todos os valores do intervalo de táboas, excluíndo as cabeceiras de fila e columna.
- Mover á pestana "Fogar" e fai clic na icona "Formato condicional". Está situado na caixa de ferramentas. "Estilos" na cinta. No menú que se abre, selecciona o elemento "Regras para a selección de celas". Na lista adicional faga clic na posición "Menos ...".
- Despois, ábrese a ventá de configuración de formato condicional. No campo esquerdo especifícase o valor inferior ao que serán seleccionadas as celas. Como recordamos, estamos satisfeitos coa condición en que o pagamento mensual do préstamo será menor 29000 rublos. Introduza este número. No campo da dereita é posible seleccionar a cor da selección, aínda que pode deixala por defecto. Despois de introducir todos os axustes necesarios, prema no botón. "OK".
- Despois diso, todas as celas cuxos valores corresponden á condición anterior serán resaltadas en cor.
Despois de analizar a matriz de táboas, pode sacar algunhas conclusións. Como podes ver, co actual período de préstamo (36 meses), para investir no importe indicado dun pagamento mensual, debemos tomar un préstamo non superior a 8.600.000,00 rublos, é dicir, 40.000 menos do previsto inicialmente.
Se aínda pretendemos tomar un préstamo por importe de 900.000 rublos, entón o prazo do préstamo debe ser de 4 anos (48 meses). Só neste caso o importe do pagamento mensual non superará o límite establecido de 29.000 rublos.
Así, aproveitando esta matriz tabular e analizando os pros e contras de cada opción, o prestatario pode tomar unha decisión específica sobre os termos do préstamo, elixindo a opción que mellor se adapte ás súas necesidades.
Por suposto, a táboa de busca pode usarse non só para calcular as opcións de crédito, senón tamén para resolver moitos outros problemas.
Lección: formatado condicional en Excel
En xeral, débese notar que a táboa de busca é unha ferramenta moi útil e relativamente sinxela para determinar o resultado de varias combinacións de variables. Aplicando o formato condicional xunto con el, tamén pode visualizar a información recibida.
