Táboa de datos en Microsoft Excel

Moitas veces, cómpre calcular o resultado final para varias combinacións de datos de entrada. Así, o usuario poderá avaliar todas as opcións posibles para accións, seleccionar aquelas cuxos resultados de interacción o satisfagan e, finalmente, elixir a opción máis óptima. En Excel, para realizar esta tarefa, hai unha ferramenta especial - "Táboa de datos" (Táboa de substitucións) Descubrimos como empregalo para completar os escenarios anteriores.

Lea tamén: selección de parámetros en Excel

Usando táboa de datos

Instrumento "Táboa de datos" Preténdese calcular o resultado para diversas variacións dunha ou dúas variables definidas. Despois do cálculo, todas as opcións posibles aparecen en forma de táboa, que se denomina matriz de análise de factores. "Táboa de datos" refírese a un grupo de ferramentas "E se a análise", que se coloca na cinta da lapela "Datos" en bloque "Traballa con datos". Antes de Excel 2007, chamouse esta ferramenta Táboa de substitucións, que reflectía aínda máis precisamente a súa esencia que o nome actual.

A táboa de busca pódese usar en moitos casos. Por exemplo, unha opción típica é cando se precisa calcular o importe do pago do préstamo mensual para varias variacións do período de acreditación e do importe do préstamo, ou do período de acreditación e do tipo de interese. Así mesmo, esta ferramenta pode empregarse na análise de modelos de proxectos de investimento.

Pero tamén debes ser consciente de que o uso excesivo desta ferramenta pode levar á freada do sistema, xa que os datos cóntanse de xeito continuado. Por iso, recoméndase en pequenas matrices de tablas resolver problemas semellantes non empregar esta ferramenta, senón usar copias de fórmulas usando o marcador de recheo.

Solicitude xustificada "Táboas de datos" é só en grandes intervalos de táboas, cando copiar fórmulas pode levar moito tempo e durante o propio procedemento a probabilidade de cometer erros aumenta. Pero neste caso, recoméndase desactivar a recalculación automática de fórmulas no intervalo da táboa de substitución para evitar unha carga innecesaria no sistema.

A principal diferenza entre os diferentes usos da táboa de datos é o número de variables implicadas no cálculo: unha variable ou dúas.

Método 1: usa a ferramenta cunha variable

De seguido vexamos a opción cando se emprega a táboa de datos cun valor variable. Tome o exemplo de préstamo máis típico.

Polo tanto, actualmente ofrécenos as seguintes condicións de préstamo:

• Prazo de préstamo - 3 anos (36 meses);
• Importe do préstamo - 900.000 rublos;
• Taxa de interese: 12,5% anual.

Os pagos prodúcense ao final do período de pago (mes) segundo o esquema de anualidades, é dicir, en partes iguais. Ao mesmo tempo, ao comezo de todo o prazo de préstamo, unha parte importante dos pagamentos son os pagamentos de xuros, pero a medida que o organismo diminúe, os pagamentos de xuros diminúen e o importe de amortización do propio organismo aumenta. O pagamento total, como se mencionou anteriormente, mantense sen cambios.

É necesario calcular cal será o importe da mensualidade, incluído o reembolso do organismo de préstamo e pagamentos de xuros. Para iso, Excel ten un operador PMT.

PMT Pertence ao grupo de funcións financeiras e a súa tarefa é calcular o pago de préstamos do tipo de anualidade mensual en función do importe do corpo de préstamo, prazo de préstamo e tipo de interese. A sintaxe desta función preséntase como

= PLT (taxa; nper; ps; bs; tipo)

Poxa - un argumento que determina a taxa de interese dos pagamentos de crédito. O indicador está configurado para o período. O noso período de pago é igual a un mes. Polo tanto, a taxa anual do 12,5% debería dividirse polo número de meses nun ano, é dicir, 12.

"Nper" - un argumento que determina o número de períodos para todo o prazo de préstamo. No noso exemplo, o período é dun mes e o prazo de préstamo é de 3 anos ou 36 meses. Así, o número de períodos será anticipado 36.

"PS" - un argumento que determina o valor actual do préstamo, é dicir, é o tamaño do organismo de 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 organismo de préstamo no momento do pagamento completo. Por suposto, este indicador será igual a cero. Este argumento é opcional. Se non o saltas, suponse que é igual ao número "0".

"Tipo" - tamén un argumento opcional. Anuncia cando se realizará exactamente o pago: ao comezo do período (parámetro - "1") ou ao final do período (parámetro - "0") Como recordamos, o noso pago realízase ao final do mes natural, é dicir, o valor deste argumento será igual "0". Pero, dado o feito de que este indicador non é obrigatorio, e por defecto, se non se usa, o valor implica que sexa igual "0", entón no exemplo indicado pode omitirse totalmente.

1. Entón, procedemos ao cálculo. Seleccione unha cela na folla onde se mostrará o valor calculado. Fai clic no botón "Función de inserción".



2. Comeza Asistente de características. Pasamos á categoría "Financeiro", selecciona o nome da lista "PLT" e fai clic no botón "Está ben".



3. Tras isto, actívase a xanela de argumentos da función anterior.

   Pon o cursor no campo Poxa, despois do que facemos 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 taxa mensual e, polo tanto, dividimos o resultado por 12 (/12).

   No campo "Nper" do mesmo xeito entramos nas coordenadas das celas do prazo de préstamo. Neste caso, non necesita compartir nada.

   No campo Ps precisa especificar as coordenadas da cela que contén o valor do corpo de préstamo. Facémolo. Tamén colocamos un cartel diante das coordenadas amosadas "-". O feito é que a función PMT por defecto dá o resultado final cun signo negativo, considerando acertadamente a perda mensual do pago do préstamo. Pero para maior claridade de aplicación da táboa de datos, necesitamos que este número sexa positivo. Por iso, poñemos un cartel menos antes dun dos argumentos da función. Coñécese a multiplicación menos en diante menos ao final dá máis.

   Nos campos "Bs" e "Tipo" os datos non se introducen en absoluto. Fai clic no botón "Está ben".



4. Despois, o operador calcula e mostra o resultado do pago total mensual nunha cela previamente designada - 30108,26 rublos. Pero o problema é que o prestatario é capaz de pagar un máximo de 29.000 rublos ao mes, é dicir, debería atopar un banco que ofreza condicións cunha taxa de interese máis baixa, ou ben reducir o organismo de préstamo ou aumentar o prazo do préstamo. A táboa de busca axudaranos a descubrir as distintas opcións.



5. Primeiro, use a táboa de busca cunha única variable. Vexamos como cambiará o importe do pago mensual obrigatorio con diversas variacións da tarifa anual, a partir de 9,5% ao ano e final 12,5% por ano por incrementos 0,5%. Todas as outras condicións quedan sen cambios. Debuxamos un intervalo de táboas, cuxos nomes corresponderán a diversas variacións do tipo de interese. Con esta liña "Pagamentos mensuais" deixar como está. A súa primeira cela debe conter a fórmula que calculamos anteriormente. Para obter 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 cabeceira.



6. 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 multiplica o contido das celas "Pagamento mensual" e "Termo de préstamo". Despois diso, faga clic no botón Introduza.



7. Para calcular a cantidade total de xuro nas condicións actuais, restamos de xeito similar o importe do préstamo ao importe total do préstamo. Para mostrar o resultado na pantalla, faga clic no botón Introduza. Así, obtemos a cantidade que pagamos excesivamente ao pagar o préstamo.



8. Agora toca aplicar a ferramenta "Táboa de datos". Seleccionamos toda a matriz de táboas, agás os nomes de filas. Despois diso, vaia á lapela "Datos". Fai clic no botón da cinta "E se a análise"que está situado no grupo de ferramentas "Traballa con datos" (en Excel 2016, un grupo de ferramentas "Previsión") A continuación, ábrese un pequeno menú. Nel seleccionamos unha posición "Táboa de datos ...".



9. Ábrese unha pequena xanela, que se chama "Táboa de datos". Como podes ver, ten dous campos. Xa que traballamos cunha variable, só necesitamos unha delas. Dado que cambiamos a variable columna por columna, utilizaremos o campo Substituír os valores da columna en. Estableza alí o cursor e logo fai clic na cela do conxunto de datos orixinal que contén a porcentaxe actual. Despois de que as coordenadas da cela aparezan no campo, faga clic no botón "Está ben".



10. A ferramenta calcula e enche todo o intervalo tabular con valores que corresponden a diferentes opcións para o tipo de interese. Se coloca o cursor en calquera elemento desta área da táboa, pode ver que a barra de fórmulas non mostra a fórmula habitual para calcular o pago, senón unha fórmula especial para unha matriz inextricable. É dicir, agora é imposible cambiar os valores das células individuais. Podes eliminar os resultados de cálculo só todos xuntos e non por separado.

Ademais, pode ver que o pago mensual no 12,5% anual obtido como resultado da aplicación da táboa de busca corresponde ao valor da mesma cantidade de interese que recibimos aplicando a función PMT. Isto demostra unha vez máis a corrección do cálculo.

Despois de analizar esta táboa, debemos dicir que, como podes ver, só a un ritmo do 9,5% anual obtemos un nivel de pago mensual aceptable (menos de 29.000 rublos).

Lección: Cálculo do pagamento de anualidades en Excel

Método 2: usa a ferramenta con dúas variables

Por suposto, atopar na actualidade bancos que emiten préstamos nun 9,5% ao ano é moi difícil, se non imposible. Polo tanto, veremos que opcións existen para investir nun nivel aceptable de pago mensual para varias combinacións doutras variables: o tamaño do corpo de préstamo e o prazo de préstamo. Neste caso, o tipo de interese manterase invariable (12,5%). Na solución deste problema, unha ferramenta axudaranos. "Táboa de datos" empregando dúas variables.

1. Debuxamos unha nova matriz de táboas. Agora na columna indicaranse os prazos de préstamo (desde 2 antes 6 anos en meses en incrementos dun ano) e en liñas - o tamaño do corpo de préstamo (desde 850000 antes 950000 rublos por incrementos 10000 rublos). Neste caso, un requisito previo é que a cela na que se atopa 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á ao usar dúas variables.



2. A continuación, seleccione todo o intervalo de táboas resultante, incluídos os nomes de columnas, filas e unha cela coa fórmula PMT. Vai á lapela "Datos". Como a vez anterior, faga clic no botón "E se a análise", no grupo de ferramentas "Traballa con datos". Na lista que se abre, seleccione "Táboa de datos ...".



3. Iníciase a xanela da ferramenta "Táboa de datos". Neste caso, necesitamos os dous campos. No campo Substituír os valores da columna en indique as coordenadas da cela que contén o prazo de préstamo nos datos primarios. No campo "Substitúe os valores fila por liña en" indique a dirección da cela dos parámetros iniciais que conteñan o valor do corpo de préstamo. Despois de que se introducen todos os datos. Fai clic no botón "Está ben".



4. O programa realiza o cálculo e enche o rango da táboa con datos. Na intersección de filas e columnas agora é posible observar cal será exactamente o pago mensual, co importe correspondente de intereses anuais e o prazo de préstamo indicado.



5. Como podes ver, hai moitos valores. Para resolver outros problemas, pode haber aínda máis. Por iso, para que a saída dos resultados sexa máis visual e de inmediato determine que valores non satisfán a condición dada, pode usar ferramentas de visualización. No noso caso, isto será un formato condicional. Seleccionamos todos os valores do intervalo de táboas, excluídos os títulos de filas e columnas.



6. Mover á pestana "Casa" e fai clic na icona Formato condicional. Está situado no bloque de ferramentas. Estilos na cinta No menú que se abre, seleccione Regras de selección de celas. Na lista adicional, faga clic na posición "Menos ...".



7. Despois disto, ábrese a xanela de configuración de formato condicional. No campo esquerdo indique o valor inferior ao que se seleccionarán as celas. Como recordamos, estamos satisfeitos coa condición de que o pago do préstamo mensual sexa inferior a 29000 rublos. Escribimos este número. No campo correcto, pode seleccionar a cor destacada, aínda que pode deixala de xeito predeterminado. Despois de introducir todos os axustes necesarios, faga clic no botón "Está ben".



8. Despois diso, resaltaranse todas as celas cuxos valores correspondan á condición anterior.

Analizada a matriz de táboas, podemos extraer algunhas conclusións. Como podes ver, co prazo de préstamo existente (36 meses), para investir no importe indicado do pago mensual, necesitamos tomar un préstamo que non exceda os 860000,00 rublos, é dicir, 40.000 menos do previsto inicialmente.

Se aínda pretendemos tomar un préstamo de 900.000 rublos, o prazo de préstamo debería ser de 4 anos (48 meses). Só neste caso, o pago mensual non superará o límite establecido en 29.000 rublos.

Así, empregando este cadro de táboas e analizando os pros e os contras de cada opción, o prestatario pode tomar unha decisión específica sobre os termos do préstamo, escollendo a opción máis adecuada entre todas as posibles.

Por suposto, a táboa de busca pode usarse non só para calcular opcións de crédito, senón tamén para resolver moitos outros problemas.

Lección: Formatación condicional en Excel

En xeral, débese sinalar que a táboa de investigación é unha ferramenta moi útil e relativamente sinxela para determinar o resultado de varias combinacións de variables. Usando o formato condicional ao mesmo tempo, tamén podes visualizar a información recibida.