別シートセルの数式参照

SPREADに複数のシートが存在する場合、異なるシートのセルを数式で参照することができます。 この機能を利用し、異なるシートの数値を集計することも可能です。

このサンプルでは、「製品在庫」シートの在庫数を「製品種別集計」シートにて、 数式を使用して、製品種別毎に集計しています。
 製品ID製品種別製品名原産地製品内容量製品単価在庫
110001乳製品酪農ミルク北海道180ml×12本400820
220001清涼飲料水いよかんドリンク愛媛720ml×10本800560
320002清涼飲料水ぶどうジュース宮城500ml×10本200086
420003清涼飲料水マンゴードリンク沖縄500ml×10本85060
530001ビール激辛ビール北海道350ml×6本800100
630002ビールモルトビール宮城500ml×6本1000100
720004清涼飲料水ぶどうの街宮城750ml×6本18000120
830003ビールオリエントの村宮城750ml×6本720050
940002焼酎吟醸 ほめごろし静岡1升瓶×6本3600100
1040003焼酎大吟醸 オリエント宮城1升瓶×6本4800100
1140005焼酎麦焼酎 ちこちこ大分1升瓶×6本480050
1210002乳製品酪農ミルク(低脂肪)北海道180ml×12本400500
 製品在庫  製品種別集計 

ソースコード

別ウィンドウで表示
using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;

public partial class differentsheetformula : System.Web.UI.Page
{

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

        // シート追加
        FarPoint.Web.Spread.SheetView sht = new FarPoint.Web.Spread.SheetView();
        FpSpread1.Sheets.Add(sht);

        // シート名設定
        FpSpread1.Sheets[0].SheetName = "製品在庫";
        FpSpread1.Sheets[1].SheetName = "製品種別集計";

        FpSpread1.ClientAutoCalculation = true;

        // セル型の自動設定を無効化
        FpSpread1.ActiveSheetView.DataAutoCellTypes = false;

        // データ連結
        DataSet ds = new DataSet();
        ds.ReadXml(MapPath("../App_Data/datanum1.xml"));
        FpSpread1.DataSource = ds;

        FpSpread1.Sheets[0].ColumnCount = 7;
        FarPoint.Web.Spread.SheetView sheet1 = FpSpread1.Sheets[0];
        FarPoint.Web.Spread.SheetView sheet2 = FpSpread1.Sheets[1];
        
        // SPREAD初期化
        InitSpread(sheet1);

        // 集計シート設定
        setsumsheet(sheet2);

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

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

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

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

        // 列幅の設定
        sheet.Columns[0].Width = 60;
        sheet.Columns[1].Width = 90;
        sheet.Columns[2].Width = 151;
        sheet.Columns[3].Width = 70;
        sheet.Columns[4].Width = 90;
        sheet.Columns[5].Width = 63;
        sheet.Columns[6].Width = 70;

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

        // 各列のセル型を設定
        FarPoint.Web.Spread.LabelCellType lbl = new FarPoint.Web.Spread.LabelCellType();

        sheet.Columns[5].HorizontalAlign = HorizontalAlign.Right;
        sheet.Columns[0, 5].CellType = lbl;

        FarPoint.Web.Spread.IntegerCellType intcell = new FarPoint.Web.Spread.IntegerCellType();
        sheet.Columns[6].CellType = intcell;        
    }

    private void setsumsheet(FarPoint.Web.Spread.SheetView sheet)
    {
        sheet.RowCount = 2;
        sheet.ColumnCount = 4;

        // 数式 製品数
        sheet.Cells[0, 0].Formula = "COUNTIF(製品在庫!B1:B12,\"乳製品\")";
        sheet.Cells[0, 1].Formula = "COUNTIF(製品在庫!B1:B12,\"清涼飲料水\")";
        sheet.Cells[0, 2].Formula = "COUNTIF(製品在庫!B1:B12,\"ビール\")";
        sheet.Cells[0, 3].Formula = "COUNTIF(製品在庫!B1:B12,\"焼酎\")";

        // 数式 在庫計
        sheet.Cells[1, 0].Formula = "SUMIF(製品在庫!B1:B12,\"乳製品\",製品在庫!G1:G12)";
        sheet.Cells[1, 1].Formula = "SUMIF(製品在庫!B1:B12,\"清涼飲料水\",製品在庫!G1:G12)";
        sheet.Cells[1, 2].Formula = "SUMIF(製品在庫!B1:B12,\"ビール\",製品在庫!G1:G12)";
        sheet.Cells[1, 3].Formula = "SUMIF(製品在庫!B1:B12,\"焼酎\",製品在庫!G1:G12)";
        
        // 列ヘッダ
        sheet.ColumnHeader.Cells[0, 0].Text = "乳製品";
        sheet.ColumnHeader.Cells[0, 1].Text = "清涼飲料水";
        sheet.ColumnHeader.Cells[0, 2].Text = "ビール";
        sheet.ColumnHeader.Cells[0, 3].Text = "焼酎";
        
        // 行ヘッダ
        sheet.RowHeader.Cells[0, 0].Text = "製品数";
        sheet.RowHeader.Cells[1, 0].Text = "在庫計";

        sheet.DefaultStyle.HorizontalAlign = HorizontalAlign.Right;

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

        sheet.RowHeader.Width = 52;
    }
}

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

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

        ' シート追加
        Dim sht As New FarPoint.Web.Spread.SheetView()
        FpSpread1.Sheets.Add(sht)

        ' シート名設定
        FpSpread1.Sheets(0).SheetName = "製品在庫"
        FpSpread1.Sheets(1).SheetName = "製品種別集計"

        FpSpread1.ClientAutoCalculation = True

        ' セル型の自動設定を無効化
        FpSpread1.ActiveSheetView.DataAutoCellTypes = False

        ' データ連結
        Dim ds As New System.Data.DataSet()
        ds.ReadXml(MapPath("../App_Data/datanum1.xml"))
        FpSpread1.DataSource = ds

        FpSpread1.Sheets(0).ColumnCount = 7
        Dim sheet1 As FarPoint.Web.Spread.SheetView = FpSpread1.Sheets(0)
        Dim sheet2 As FarPoint.Web.Spread.SheetView = FpSpread1.Sheets(1)

        ' SPREAD初期化
        InitSpread(sheet1)

        ' 集計シート設定
        setsumsheet(sheet2)

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

        ' フォントサイズの設定
        sheet2.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet2.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet2.RowHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet2.SheetCorner.DefaultStyle.Font.Size = FontUnit.Parse("80%")
    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.PageSize = sheet.RowCount

        ' 列幅の設定
        sheet.Columns(0).Width = 60
        sheet.Columns(1).Width = 90
        sheet.Columns(2).Width = 151
        sheet.Columns(3).Width = 70
        sheet.Columns(4).Width = 90
        sheet.Columns(5).Width = 63
        sheet.Columns(6).Width = 70

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

        ' 各列のセル型を設定
        Dim lbl As New FarPoint.Web.Spread.LabelCellType()

        sheet.Columns(5).HorizontalAlign = HorizontalAlign.Right
        sheet.Columns(0, 5).CellType = lbl

        Dim intcell As New FarPoint.Web.Spread.IntegerCellType()
        sheet.Columns(6).CellType = intcell
    End Sub

    Private Sub setsumsheet(ByVal sheet As FarPoint.Web.Spread.SheetView)
        sheet.RowCount = 2
        sheet.ColumnCount = 4

        ' 数式 製品数
        sheet.Cells(0, 0).Formula = "COUNTIF(製品在庫!B1:B12,""乳製品"")"
        sheet.Cells(0, 1).Formula = "COUNTIF(製品在庫!B1:B12,""清涼飲料水"")"
        sheet.Cells(0, 2).Formula = "COUNTIF(製品在庫!B1:B12,""ビール"")"
        sheet.Cells(0, 3).Formula = "COUNTIF(製品在庫!B1:B12,""焼酎"")"

        ' 数式 在庫計
        sheet.Cells(1, 0).Formula = "SUMIF(製品在庫!B1:B12,""乳製品"",製品在庫!G1:G12)"
        sheet.Cells(1, 1).Formula = "SUMIF(製品在庫!B1:B12,""清涼飲料水"",製品在庫!G1:G12)"
        sheet.Cells(1, 2).Formula = "SUMIF(製品在庫!B1:B12,""ビール"",製品在庫!G1:G12)"
        sheet.Cells(1, 3).Formula = "SUMIF(製品在庫!B1:B12,""焼酎"",製品在庫!G1:G12)"

        ' 列ヘッダ
        sheet.ColumnHeader.Cells(0, 0).Text = "乳製品"
        sheet.ColumnHeader.Cells(0, 1).Text = "清涼飲料水"
        sheet.ColumnHeader.Cells(0, 2).Text = "ビール"
        sheet.ColumnHeader.Cells(0, 3).Text = "焼酎"

        ' 行ヘッダ
        sheet.RowHeader.Cells(0, 0).Text = "製品数"
        sheet.RowHeader.Cells(1, 0).Text = "在庫計"

        sheet.DefaultStyle.HorizontalAlign = HorizontalAlign.Right

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

        sheet.RowHeader.Width = 52
    End Sub
End Class
<%@ Page MasterPageFile="~/MasterPage.master" Language="c#" AutoEventWireup="true" 
         Inherits="differentsheetformula" CodeFile="differentsheetformula.aspx.cs" %>

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

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <farpoint:FpSpread ID="FpSpread1" runat="server">
        <CommandBar BackColor="Control" ButtonFaceColor="Control" ButtonHighlightColor="ControlLightLight"
            ButtonShadowColor="ControlDark" />
        <Sheets>
            <farpoint:SheetView SheetName="Sheet1">
            </farpoint:SheetView>
        </Sheets>
    </farpoint:FpSpread>
</asp:Content>