支店別の販売実績表を作って、さらに全社計を作る（串刺し集計:3D集計)
会社でよく使いそうな「販売の実績表」をExcelCreatorを使って作成するサンプルです。
ExcelCreatorの「オーバレイ機能」を使ってひな形から支店別の販売実績シートを作成します。その後、全社計のシートも作ります。
作成するExcelファイルの概要
１．ひな形となるファイルです。
左のExcelファイルを、元となるオーバレイ元ファイルとして使います。
**BranchName とあるセル(A1)に支店名をセットします。
**Headはデータ開始行・列を取得する為に使っています。
変数名によるセルの指定も参照下さい。
２．支店別の集計表イメージ
支店別のワークシートを作成します。
B列には分類名が入ります。
売上数や金額などは適当に数字を作っています。
明細行が終わった後にsum関数を入れたりして合計行を作成します。
３．全社合計表を作成
支店のワークシートを元に全社合計ワークシートを作成します。
具体的には最初の支店のワークシートをコピーし、各セルに各支店の同一セルを合計する式をセットします。
サンプルプログラム
フォーム
作成対象となる支店を選択し実行します。
処理の大まかな流れは、
- サンプルデータを作成
- 支店別のワークシート作成
- 全社合計ワークシート作成
と、いう感じです。（ホントに大まかですね！）
ソース
Public Class frmTotalPrint
Private ds As DataSet = Nothing
'売上分類名配列
Private BunruiName() As String = {"1:ソフトウエア売上", "2:ハードウエア売上", "3:その他売上"}
'ExceleCeator
Private WithEvents xlsCr As New ExcelCreator.XlsCreator
'''
''' サンプルデータ作成
'''
'''
''' Excel 支店別販売実績表作成
'''
'''
''' Excelの列番号を英文字に変換する
'''
''' 列番号
'''
''' マイクロソフトサポートオンラインより引用して変更を加えた
''' http://support.microsoft.com/kb/833402/ja
'''
Function ConvertToLetter(ByVal iCol As Integer) As String
Dim rtnLetter As String = ""
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = CInt(Int((iCol - 1) / 26))
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
rtnLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
rtnLetter = rtnLetter & Chr(iRemainder + 64)
End If
Return rtnLetter
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim SelCount As Integer = 0
For Each item As GrapeCity.Win.Input.ListItem In List1.CheckedItems
SelCount += 1
Next
If SelCount > 0 Then
InitializeSampleData()
MakeExcel()
Else
MessageBox.Show("支店が選択されていません")
End If
End Sub
Private Sub xlsCr_Error(ByVal sender As Object, ByVal e As ExcelCreator.XlsCreatorEventArgs) Handles xlsCr.Error
MessageBox.Show(e.ErrorNo.ToString() & " : " & xlsCr.ErrorMessage, "Error")
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
xlsCr.CreateBook(Application.StartupPath & "\ExcelFile.xls", 4, ExcelCreator.xlVersion.ver2003)
xlsCr.SheetNo = 3
xlsCr.SheetName = "４月"
xlsCr.Cell("A1").Long = 400
xlsCr.SheetNo = 0
xlsCr.Cell("A1").Long = 100
xlsCr.RefSheet = "Sheet1/４月"
xlsCr.Cell("A2").Func("='Sheet1'!A1+'４月'!A1)", "")
'=札幌支店!C4+帯広支店!C4
xlsCr.CloseBook(True)
End Sub
End Class
ソースの解説
サンプルデータ作成
支店別、分類別の数字を作ります。支店データテーブル("SITEN")、販売データテーブル("URIDATA")を作成しています。
'''
''' サンプルデータ作成
'''
'''
セルに式をセットする時の備忘録
プロシジャ「MakeExcel」の先頭で「全社合計保管配列の初期化」ってのをやっています。
'全社合計保管配列の初期化
ReDim TotalSuu(BunruiName.Length)
ReDim TotalKin(BunruiName.Length)
ReDim TotalArari(BunruiName.Length)
ExcelCreatorでセルに計算式、計算結果を入れる「Funcメソッド」があります。このメソッドにあらかじめ計算結果を入れておく為に使用する変数なんですが、何でFuncメソッドで式をセットしてるのに、計算結果をあらかじめ設定するんでしょうね？
これは、過去自分はハマったんですが、Excel風に考えて式をセットすれば、再計算されるもんだとばかり思ってたんですね。ところがそんな事はなくて再計算されないのです。よって式をセットしたセルの計算結果をプログラム中で参照したくとも出来ない、と言う事になります。
確かにExcelCreatorでいちいち再計算なんてしてたらかなり遅くなるだろうな、と思います。
再計算しないので速いのかも知れないな．．．と、今思いました。
何はともあれ再計算しないので、Excelでファイルを開いた時に再計算が走るんで、自分は何も変更していなくとも終了時に「保存しますか？」と聞かれるわけです。「保存しますか？」ってのがイヤなので、合計値を集計しておいてFuncメソッドで集計値を設定してやる、と言う意味でやってます。
ExcelCreator 5.0 for.NETヘルプより引用
Func メソッドで計算結果をあらかじめ設定することで、ExcelCreator 5.0 for .NET で作成した Excel ファイルを Excel で開き、修正を行わずに終了した時に表示される保存の確認メッセージを非表示にできます。
支店のワークシートを作る
'全社合計保管配列の初期化
ReDim TotalSuu(BunruiName.Length)
ReDim TotalKin(BunruiName.Length)
ReDim TotalArari(BunruiName.Length)
'Excelオープン
Dim openSts As Integer = xlsCr.OpenBook( _
"c:\支店別販売実績表.xls", _
"c:\支店別実績表Base.xls")
If openSts < 0 Then
Exit Try
End If
'シートの変更
xlsCr.SheetNo = 0
HeadPos = xlsCr.GetVarNamePos("**Head", 0) '基準セル
ColSuu = HeadPos.Width + 1 '売上数列を保管
ColUriage = HeadPos.Width + 2 '売上金額列を保管
ColArari = HeadPos.Width + 3 '粗利列を保管
'明細行の開始行、最終行数を取得しておく
RowStart = HeadPos.Height + 1
RowEnd = HeadPos.Height + BunruiName.Length + 1
Line 7-9 : 支店別実績表Base.xls をオーバーレイ元ファイルとして、支店別販売実績表.xls を作成します。
Line 15-24 : 後々使う為に、セルの行・列位置などを取得しておきます
支店データテーブルでループしながらワークシートを作っていきます
'-------------------------------------------------------------
'
'シートコピー/実績値のセット
'
'-------------------------------------------------------------
Dim BaseSheetNo As Integer = 0
'売上データをDataViewにセット
dv = New DataView(ds.Tables("URIDATA"))
'支店データでループ
For i = 0 To ds.Tables("SITEN").Rows.Count - 1
drowSiten = ds.Tables("SITEN").Rows(i)
'シートをコピー
xlsCr.CopySheet(BaseSheetNo, BaseSheetNo + 1 + i, _
drowSiten.Item("SitenName").ToString)
'カレントシートをコピーしたシートに変更
xlsCr.SheetNo = BaseSheetNo + 1 + i
'支店名をセット
xlsCr.Cell("A1").Str = drowSiten.Item("SitenName").ToString
Dim SitenTotalSuu As Integer = 0 '数量合計用
Dim SitenTotalKingaku As Decimal = 0D '売上金額合計用
Dim SitenTotalArari As Decimal = 0D '粗利合計用
'DataView 支店でFilter
dv.RowFilter = "SitenCD = " & drowSiten.Item("SitenCD").ToString
dv.Sort = "SitenCD,Bunrui"
For j = 0 To dv.Count - 1
drowJisseki = dv.Item(j).Row
'分類名
xlsCr.Pos(HeadPos.Width, HeadPos.Height + 1 + j).Str = BunruiName(j).TrimEnd
'数量
xlsCr.Pos(ColSuu, HeadPos.Height + 1 + j).Long = CInt(drowJisseki.Item("UriageSuu"))
SitenTotalSuu += CInt(drowJisseki.Item("UriageSuu"))
TotalSuu(j) += CInt(drowJisseki.Item("UriageSuu"))
'売上金額
xlsCr.Pos(ColUriage, HeadPos.Height + 1 + j).Value = CDec(drowJisseki.Item("UriageKin"))
SitenTotalKingaku += CDec(drowJisseki.Item("UriageKin"))
TotalKin(j) += CDec(drowJisseki.Item("UriageKin"))
'粗利
xlsCr.Pos(ColArari, HeadPos.Height + 1 + j).Value = CDec(drowJisseki.Item("ArariKin"))
SitenTotalArari += CDec(drowJisseki.Item("ArariKin"))
TotalArari(j) += CDec(drowJisseki.Item("ArariKin"))
Next
'------------------------------------
'合計式
'------------------------------------
xlsCr.Pos(HeadPos.Width, RowEnd).Str = "■ 合計 ■"
'数量合計
ColNMSuu = ConvertToLetter(ColSuu + 1)
xlsCr.Cell(ColNMSuu & (RowEnd + 1).ToString).Func( _
"=sum(" & ColNMSuu & (RowStart + 1).ToString & ":" & _
ColNMSuu & RowEnd.ToString & ")", SitenTotalSuu)
'売上金額合計
ColNMUriage = ConvertToLetter(ColUriage + 1)
xlsCr.Cell(ColNMUriage & (RowEnd + 1).ToString).Func( _
"=sum(" & ColNMUriage & (RowStart + 1).ToString & ":" & _
ColNMUriage & RowEnd.ToString & ")", SitenTotalKingaku)
'粗利合計
ColNMArari = ConvertToLetter(ColArari + 1)
xlsCr.Cell(ColNMArari & (RowEnd + 1).ToString).Func( _
"=sum(" & ColNMArari & (RowStart + 1).ToString & ":" & _
ColNMArari & RowEnd.ToString & ")", SitenTotalArari)
'書式
xlsCr.Pos(HeadPos.Width + 1, HeadPos.Height, _
HeadPos.Width + 3, RowEnd).Attr.Format = "###,###,###,###" 'カンマ区切り
'罫線
With xlsCr.Pos(HeadPos.Width, HeadPos.Height, HeadPos.Width + 3, RowEnd).Attr
.LineTop = ExcelCreator.xlLineStyle.lsNormal
.LineBottom = ExcelCreator.xlLineStyle.lsNormal
.LineLeft = ExcelCreator.xlLineStyle.lsNormal
.LineRight = ExcelCreator.xlLineStyle.lsNormal
End With
Next
Line 57 -- 合計行の式作成
数量、金額、粗利の合計行に 式(sum)をセットします。AとかBなどの列名をConvertToLetter関数を使用して列番号から列名を取得しています。このとき、posクラスのx/yは0オリジンなので、１を加えた列番号を渡します。
'''''' Excelの列番号を英文字に変換する ''' ''' 列番号 '''''' ''' マイクロソフトサポートオンラインより引用して変更を加えた ''' http://support.microsoft.com/kb/833402/ja ''' Function ConvertToLetter(ByVal iCol As Integer) As String Dim rtnLetter As String = "" Dim iAlpha As Integer Dim iRemainder As Integer iAlpha = CInt(Int((iCol - 1) / 26)) iRemainder = iCol - (iAlpha * 26) If iAlpha > 0 Then rtnLetter = Chr(iAlpha + 64) End If If iRemainder > 0 Then rtnLetter = rtnLetter & Chr(iRemainder + 64) End If Return rtnLetter End Function
全社合計のワークシートを作る
'-------------------------------------------------------------
'
'全社合計シート作成
'
'-------------------------------------------------------------
'最初の支店のシートをコピーして作成
BaseSheetNo = xlsCr.SheetNo2(ds.Tables("SITEN").Rows(0).Item("SitenName").ToString)
'シートをコピー
xlsCr.CopySheet(BaseSheetNo, 0, "全社合計")
'カレントシートをコピーしたシートに変更
xlsCr.SheetNo = xlsCr.SheetNo2("全社合計")
'支店名をセット
xlsCr.Cell("A1").Str = "全社合計"
'参照するシート名を設定
Dim refSheet As String = ""
For i = 0 To ds.Tables("SITEN").Rows.Count - 1
If refSheet.Trim.Length > 0 Then
refSheet = refSheet.TrimEnd & "/"
End If
refSheet = refSheet.TrimEnd & ds.Tables("SITEN").Rows(i).Item("SitenName").ToString
Next
xlsCr.RefSheet = refSheet.Trim
'式を作成
For i = 0 To BunruiName.Length - 1
Dim wkFunc() As String = {"", "", ""} '数量,売上,粗利
For j = 0 To ds.Tables("SITEN").Rows.Count - 1
Dim wkSheetName As String = ds.Tables("SITEN").Rows(j).Item("SitenName").ToString.TrimEnd & "!"
'数量
If wkFunc(0).Trim.Length > 0 Then
wkFunc(0) &= "+"
End If
wkFunc(0) &= wkSheetName & ColNMSuu & (HeadPos.Height + 2 + i).ToString
'売上
If wkFunc(1).Trim.Length > 0 Then
wkFunc(1) &= "+"
End If
wkFunc(1) &= wkSheetName & ColNMUriage & (HeadPos.Height + 2 + i).ToString
'粗利
If wkFunc(2).Trim.Length > 0 Then
wkFunc(2) &= "+"
End If
wkFunc(2) &= wkSheetName & ColNMArari & (HeadPos.Height + 2 + i).ToString
Next
'分類iの「数量」
xlsCr.Cell(ColNMSuu & (HeadPos.Height + 2 + i).ToString).Func("=" & wkFunc(0), TotalSuu(i))
'分類iの「売上」
xlsCr.Cell(ColNMUriage & (HeadPos.Height + 2 + i).ToString).Func("=" & wkFunc(1), TotalKin(i))
'分類iの「粗利」
xlsCr.Cell(ColNMArari & (HeadPos.Height + 2 + i).ToString).Func("=" & wkFunc(2), TotalArari(i))
Next
'合計計算
Dim wkSuu As Integer = 0
Dim wkKin As Decimal = 0D
Dim wkArari As Decimal = 0D
For i = 0 To BunruiName.Length - 1
wkSuu += TotalSuu(i)
wkKin += TotalKin(i)
wkArari += TotalArari(i)
Next
'数量合計
xlsCr.Cell(ColNMSuu & (RowEnd + 1).ToString).Func("=sum(" _
& ColNMSuu & (RowStart + 1).ToString & ":" & ColNMSuu & RowEnd.ToString & ")", wkSuu)
'売上金額合計
xlsCr.Cell(ColNMUriage & (RowEnd + 1).ToString).Func("=sum(" _
& ColNMUriage & (RowStart + 1).ToString & ":" & ColNMUriage & RowEnd.ToString & ")", wkKin)
'粗利合計
xlsCr.Cell(ColNMArari & (RowEnd + 1).ToString).Func("=sum(" _
& ColNMArari & (RowStart + 1).ToString & ":" & ColNMArari & RowEnd.ToString & ")", wkArari)
Line 16-23 : RefSheetプロパティ
複数のワークシート参照を行う為にはRefSheetプロパティに参照するシート名を設定する必要があります。
ExcelCreator 5.0 for.NETヘルプより引用
Func メソッドの指定セルに参照シートを含む場合には、必ず Func メソッドを設定する前に参照されているシート名を設定してください。
まとめ
今回は複数のワークシートの中で、セルが同じ位置関係（同じ意味合い）にあるセル」のデータを集計しました。支店毎に売上分類が違う行になる場合には、一工夫が必要になります。
全社合計ワークシートにsumをセットする際、バカ正直に「=札幌支店!C4+帯広支店!C4+北見支店!C4」という式をセットしています。
が、「=sum('札幌支店:北見支店!C4」と言う様にも書けますね。
色々な表を作ってみて下さい、
履歴
- 2011/10/27
- 公開