SPREAD for ASP.NETデモアプリケーション

ゴールシークは数式の逆算機能です。数式の結果が目的の値になるように、数式が参照している値を逆算することができます。

このサンプルでは毎月の希望返済額を入力することで、購入時に必要な頭金を計算します。
「毎月返済額」の数式結果が「毎月希望返済額」で入力された値となるために、「頭金」を逆算します。

【使用関数の解説】
・PMT
現在価値、指定の利率、および支払回数に基づき、借入金返済での定期支払額を算出します。

書式
PMT(rate,nper,pval,fval,type)

引数
有効な引数は次のとおりです。

引数	説明
rate	期間あたりの利率
nper	合計支払回数
pval	現在価値
fval	（オプション）将来価値。最後の支払を行った後に残る現金収支です。省略すると０とみなされます。
type	（オプション）支払期日。支払が期末に行われる場合は０、期首に行われる場合は１を指定します。省略すると０とみなされます。

毎月返済希望額：

	A	B	C
1	マイカーローン・シミュレーション
2
3	代金:	¥1,300,000	購入代金（変更可）
4
5	頭金:	¥100,000	計算対象（ゴールシークにより算出）
6
7	借入金額:	¥1,200,000	=B3-B5
8	借入月数:	12	返済する月数（変更可）
9	年利(%):	3%	年利率（変更可）
10
11	毎月返済額:	¥101,632	=INT(PMT(B9/12,B8,-B7))

ソースコード

C#
VB
ASPX

別ウィンドウで表示

using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;

public partial class goalseek : System.Web.UI.Page
{
    const int ROW_PR = 2;   //代金
    const int ROW_DP = 4;   //頭金
    const int ROW_AR = 8;   //年利
    const int ROW_BR = 6;   //借入金額
    const int ROW_BM = 7;   //借入月数
    const int ROW_RP = 10;   //毎月返済額

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack) return;

        // データ設定
        SetData(FpSpread1.Sheets[0]);
        
        // SPREAD初期化
        InitSpread(FpSpread1.Sheets[0]);

        // クライアント側スクリプトの設定
        string clientScript = "<script language=\"JavaScript\">";
        clientScript += "function GoalSeek()";
        clientScript += "{";
        clientScript += "var spread = document.getElementById(\"" + FpSpread1.ClientID + "\");";
        clientScript += "spread.CallBack(\"GoalSeek\");";
        clientScript += "}";
        clientScript += "</script>";

        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "goalseekScript", clientScript, false);
    }

    private void InitSpread(FarPoint.Web.Spread.SheetView sheet)
    {
        // SPREAD設定
        sheet.FpSpread.CommandBar.Visible = false;
        sheet.FpSpread.CssClass = "spreadStyle";
        sheet.FpSpread.UseClipboard = false;

        // フォントサイズの設定
        sheet.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.RowHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.SheetCorner.DefaultStyle.Font.Size = FontUnit.Parse("80%");

        // シート設定
        sheet.PageSize = sheet.RowCount;

        // 列幅の設定
        sheet.Columns[0].Width = 150;
        sheet.Columns[1].Width = 150;
        sheet.Columns[2].Width = 348;

        // 縦方向の揃え位置を中央に設定
        sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle;

        // クライアントで値が変更された場合、数式に反映
        sheet.FpSpread.ClientAutoCalculation = true;
    }

    private void SetData(FarPoint.Web.Spread.SheetView sheet)
    {
        sheet.ColumnCount = 3;
        sheet.RowCount = 11;

        sheet.Columns[0].HorizontalAlign = HorizontalAlign.Center;

        sheet.SetValue(0, 0, "マイカーローン・シミュレーション");
        sheet.AddSpanCell(0, 0, 1, 3);

        sheet.DefaultStyle.Font.Size = FontUnit.Point(14);

        sheet.SetValue(ROW_PR, 0, "代金:");
        sheet.SetValue(ROW_DP, 0, "頭金:");
        sheet.SetValue(ROW_BR, 0, "借入金額:");
        sheet.SetValue(ROW_BM, 0, "借入月数:");
        sheet.SetValue(ROW_AR, 0, "年利(%):");
        sheet.SetValue(ROW_RP, 0, "毎月返済額:");

        sheet.SetValue(ROW_PR, 1, "1300000.");
        sheet.SetValue(ROW_DP, 1, "100000.");
        sheet.SetValue(ROW_AR, 1, "0.03");
        sheet.SetValue(ROW_BM, 1, "12");

        sheet.SetValue(ROW_PR, 2, "購入代金（変更可）");
        sheet.SetValue(ROW_DP, 2, "計算対象（ゴールシークにより算出）");
        sheet.SetValue(ROW_BR, 2, "=B3-B5");
        sheet.SetValue(ROW_BM, 2, "返済する月数（変更可）");
        sheet.SetValue(ROW_AR, 2, "年利率（変更可）");
        sheet.SetValue(ROW_RP, 2, "=INT(PMT(B9/12,B8,-B7))");

        // セル型
        sheet.Cells[ROW_PR, 1, 4, 1].CellType = new FarPoint.Web.Spread.CurrencyCellType();
        sheet.Cells[ROW_AR, 1].CellType = new FarPoint.Web.Spread.PercentCellType();
        sheet.Cells[ROW_BR, 1].CellType = new FarPoint.Web.Spread.CurrencyCellType();
        sheet.Cells[ROW_BM, 1].CellType = new FarPoint.Web.Spread.IntegerCellType();
        sheet.Cells[ROW_RP, 1].CellType = new FarPoint.Web.Spread.CurrencyCellType();

        // タイトル
        sheet.Cells[0, 0].ForeColor = System.Drawing.Color.Blue;
        sheet.Cells[0, 0].Font.Underline = true;
        sheet.Cells[0, 0].Font.Italic = true;
        sheet.Cells[0, 0].Font.Size = FontUnit.Point(18);
              
        // 代金-罫線
        sheet.Cells[ROW_PR, 0, ROW_PR, 2].Border = 
            new FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2);
        sheet.Cells[ROW_PR, 0, ROW_PR, 0].ForeColor = System.Drawing.Color.White;
        sheet.Cells[ROW_PR, 0, ROW_PR, 0].BackColor = System.Drawing.Color.DarkBlue;

        // 頭金-罫線
        sheet.Cells[ROW_DP, 0, ROW_DP, 2].Border = 
            new FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2);
        sheet.Cells[ROW_DP, 0, ROW_DP, 0].ForeColor = System.Drawing.Color.White;
        sheet.Cells[ROW_DP, 1, ROW_DP, 2].ForeColor = System.Drawing.Color.Red;
        sheet.Cells[ROW_DP, 0, ROW_DP, 0].BackColor = System.Drawing.Color.DarkBlue;

        // 借入-罫線
        sheet.Cells[ROW_BR, 0, ROW_AR, 2].Border = 
            new FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2);
        sheet.Cells[ROW_BR, 0, ROW_AR, 0].ForeColor = System.Drawing.Color.White;
        sheet.Cells[ROW_BR, 0, ROW_AR, 0].BackColor = System.Drawing.Color.DarkBlue;

        // 毎月返済額-罫線
        sheet.Cells[ROW_RP, 0, ROW_RP, 2].Border = 
            new FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2);
        sheet.Cells[ROW_RP, 0, ROW_RP, 0].ForeColor = System.Drawing.Color.White;
        sheet.Cells[ROW_RP, 0, ROW_RP, 0].BackColor = System.Drawing.Color.DarkBlue;

        // ロック
        sheet.DefaultStyle.Locked = true;
        sheet.Cells[ROW_PR,1].Locked = false;
        sheet.Cells[ROW_BM,1].Locked = false;
        sheet.Cells[ROW_AR, 1].Locked = false;

        sheet.Cells[ROW_DP, 1].BackColor = System.Drawing.Color.Cornsilk;
        sheet.Cells[ROW_BR, 1].BackColor = System.Drawing.Color.Cornsilk;
        sheet.Cells[ROW_RP, 1].BackColor = System.Drawing.Color.Cornsilk;

        // 計算式設定
        sheet.SetFormula(ROW_BR, 1, "B" + Convert.ToString(ROW_PR + 1) + "-B" + Convert.ToString(ROW_DP +1));
        sheet.SetFormula(ROW_RP, 1, 
            "INT(PMT(B" + Convert.ToString(ROW_AR + 1) + "/12,B" + 
            Convert.ToString(ROW_BM + 1) + ",-B" + Convert.ToString(ROW_BR + 1) + "))");
    }

    protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
    {
        if (e.CommandName == "GoalSeek")
        {
            // 入力額取得
            int rp;
            int.TryParse(TextBox1.Text, out rp);

            if (rp == 0)
            {
                return;
            }

            // ゴールシーク
            FpSpread1.GoalSeek(0, ROW_DP, 1, 0, ROW_RP, 1, rp);  
        }
    }
}


Partial Public Class goalseek
    Inherits System.Web.UI.Page

    Const ROW_PR As Integer = 2    '代金
    Const ROW_DP As Integer = 4    '頭金
    Const ROW_AR As Integer = 8    '年利
    Const ROW_BR As Integer = 6    '借入金額
    Const ROW_BM As Integer = 7    '借入月数
    Const ROW_RP As Integer = 10   '毎月返済額

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack Then
            Return
        End If

        ' データ設定
        SetData(FpSpread1.Sheets(0))

        ' SPREAD初期化
        InitSpread(FpSpread1.Sheets(0))

        ' クライアント側スクリプトの設定
        Dim clientScript As String = "<script language=""JavaScript"">"
        clientScript += "function GoalSeek()"
        clientScript += "{"
        clientScript += "var spread = document.getElementById(""" + FpSpread1.ClientID & """);"
        clientScript += "spread.CallBack(""GoalSeek"");"
        clientScript += "}"
        clientScript += "</script>"

        ScriptManager.RegisterClientScriptBlock(Me, Me.GetType(), "goalseekScript", clientScript, False)
    End Sub

    Private Sub InitSpread(ByVal sheet As FarPoint.Web.Spread.SheetView)
        ' SPREAD設定
        sheet.FpSpread.CommandBar.Visible = False
        sheet.FpSpread.CssClass = "spreadStyle"
        sheet.FpSpread.UseClipboard = False

        ' フォントサイズの設定
        sheet.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.RowHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.SheetCorner.DefaultStyle.Font.Size = FontUnit.Parse("80%")

        ' シート設定
        sheet.PageSize = sheet.RowCount

        ' 列幅の設定
        sheet.Columns(0).Width = 150
        sheet.Columns(1).Width = 150
        sheet.Columns(2).Width = 348

        ' 縦方向の揃え位置を中央に設定
        sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle

        ' クライアントで値が変更された場合、数式に反映
        sheet.FpSpread.ClientAutoCalculation = True
    End Sub

    Private Sub SetData(ByVal sheet As FarPoint.Web.Spread.SheetView)
        sheet.ColumnCount = 3
        sheet.RowCount = 11

        sheet.Columns(0).HorizontalAlign = HorizontalAlign.Center

        sheet.SetValue(0, 0, "マイカーローン・シミュレーション")
        sheet.AddSpanCell(0, 0, 1, 3)

        sheet.DefaultStyle.Font.Size = FontUnit.Point(14)

        sheet.SetValue(ROW_PR, 0, "代金:")
        sheet.SetValue(ROW_DP, 0, "頭金:")
        sheet.SetValue(ROW_BR, 0, "借入金額:")
        sheet.SetValue(ROW_BM, 0, "借入月数:")
        sheet.SetValue(ROW_AR, 0, "年利(%):")
        sheet.SetValue(ROW_RP, 0, "毎月返済額:")

        sheet.SetValue(ROW_PR, 1, "1300000.")
        sheet.SetValue(ROW_DP, 1, "100000.")
        sheet.SetValue(ROW_AR, 1, "0.03")
        sheet.SetValue(ROW_BM, 1, "12")

        sheet.SetValue(ROW_PR, 2, "購入代金（変更可）")
        sheet.SetValue(ROW_DP, 2, "計算対象（ゴールシークにより算出）")
        sheet.SetValue(ROW_BR, 2, "=B3-B5")
        sheet.SetValue(ROW_BM, 2, "返済する月数（変更可）")
        sheet.SetValue(ROW_AR, 2, "年利率（変更可）")
        sheet.SetValue(ROW_RP, 2, "=INT(PMT(B9/12,B8,-B7))")

        ' セル型
        sheet.Cells(ROW_PR, 1, 4, 1).CellType = New FarPoint.Web.Spread.CurrencyCellType()
        sheet.Cells(ROW_AR, 1).CellType = New FarPoint.Web.Spread.PercentCellType()
        sheet.Cells(ROW_BR, 1).CellType = New FarPoint.Web.Spread.CurrencyCellType()
        sheet.Cells(ROW_BM, 1).CellType = New FarPoint.Web.Spread.IntegerCellType()
        sheet.Cells(ROW_RP, 1).CellType = New FarPoint.Web.Spread.CurrencyCellType()

        ' タイトル
        sheet.Cells(0, 0).ForeColor = System.Drawing.Color.Blue
        sheet.Cells(0, 0).Font.Underline = True
        sheet.Cells(0, 0).Font.Italic = True
        sheet.Cells(0, 0).Font.Size = FontUnit.Point(18)

        ' 代金-罫線
        sheet.Cells(ROW_PR, 0, ROW_PR, 2).Border = New FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2)
        sheet.Cells(ROW_PR, 0, ROW_PR, 0).ForeColor = System.Drawing.Color.White
        sheet.Cells(ROW_PR, 0, ROW_PR, 0).BackColor = System.Drawing.Color.DarkBlue

        ' 頭金-罫線
        sheet.Cells(ROW_DP, 0, ROW_DP, 2).Border = New FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2)
        sheet.Cells(ROW_DP, 0, ROW_DP, 0).ForeColor = System.Drawing.Color.White
        sheet.Cells(ROW_DP, 1, ROW_DP, 2).ForeColor = System.Drawing.Color.Red
        sheet.Cells(ROW_DP, 0, ROW_DP, 0).BackColor = System.Drawing.Color.DarkBlue

        ' 借入-罫線
        sheet.Cells(ROW_BR, 0, ROW_AR, 2).Border = New FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2)
        sheet.Cells(ROW_BR, 0, ROW_AR, 0).ForeColor = System.Drawing.Color.White
        sheet.Cells(ROW_BR, 0, ROW_AR, 0).BackColor = System.Drawing.Color.DarkBlue

        ' 毎月返済額-罫線
        sheet.Cells(ROW_RP, 0, ROW_RP, 2).Border = New FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Gray, 2)
        sheet.Cells(ROW_RP, 0, ROW_RP, 0).ForeColor = System.Drawing.Color.White
        sheet.Cells(ROW_RP, 0, ROW_RP, 0).BackColor = System.Drawing.Color.DarkBlue

        ' ロック
        sheet.DefaultStyle.Locked = True
        sheet.Cells(ROW_PR, 1).Locked = False
        sheet.Cells(ROW_BM, 1).Locked = False
        sheet.Cells(ROW_AR, 1).Locked = False

        sheet.Cells(ROW_DP, 1).BackColor = System.Drawing.Color.Cornsilk
        sheet.Cells(ROW_BR, 1).BackColor = System.Drawing.Color.Cornsilk
        sheet.Cells(ROW_RP, 1).BackColor = System.Drawing.Color.Cornsilk

        ' 計算式設定
        sheet.SetFormula(ROW_BR, 1, "B" & Convert.ToString(ROW_PR + 1) & "-B" & Convert.ToString(ROW_DP + 1))
        sheet.SetFormula(ROW_RP, 1, "INT(PMT(B" & Convert.ToString(ROW_AR + 1) & "/12,B" & Convert.ToString(ROW_BM + 1) & ",-B" & Convert.ToString(ROW_BR + 1) & "))")
    End Sub

    Protected Sub FpSpread1_ButtonCommand(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles FpSpread1.ButtonCommand
        If e.CommandName = "GoalSeek" Then
            ' 入力額取得
            Dim rp As Integer
            Integer.TryParse(TextBox1.Text, rp)

            If rp = 0 Then
                Return
            End If

            ' ゴールシーク
            FpSpread1.GoalSeek(0, ROW_DP, 1, 0, ROW_RP, 1, rp)
        End If
    End Sub
End Class

<%@ Page MasterPageFile="~/MasterPage.master" Language="c#" AutoEventWireup="true" 
         Inherits="goalseek" CodeFile="goalseek.aspx.cs" %>

<%@ Register Assembly="FarPoint.Web.SpreadJ" Namespace="FarPoint.Web.Spread" TagPrefix="FarPoint" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <asp:Label ID="Label" runat="server" Text="毎月返済希望額：" 
        AssociatedControlID="TextBox1" Font-Size="12pt"></asp:Label>
    <asp:TextBox ID="TextBox1" runat="server" MaxLength="7" Wrap="False" 
    style="ime-mode: disabled; text-align: right" Width="100px">30000</asp:TextBox>
    <input id="Button1" type="button" value="試算" onclick="GoalSeek();" />
    <farpoint:FpSpread ID="FpSpread1" runat="server" ActiveSheetViewIndex="0" 
        DesignString="&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;Spread /&gt;" 
        onbuttoncommand="FpSpread1_ButtonCommand">
        <CommandBar BackColor="Control" ButtonFaceColor="Control" ButtonHighlightColor="ControlLightLight"
            ButtonShadowColor="ControlDark" />
        <Sheets>
            <farpoint:SheetView SheetName="Sheet1">
            </farpoint:SheetView>
        </Sheets>
    </farpoint:FpSpread>
</asp:Content>

このページの先頭へ戻る