【Excelアドイン】シート分割数式を使ってみる

今回は、Sharperlight Excelアドインの機能のひとつ、シート分割を使用してみます。
シート分割とは、あるシートにSharperlight数式を含むテンプレートを作成し、指定したフィルターの値別に新しいシートを複製するといった機能です。
今回の例では、実績と予算の集計表を部門別のシートに分割する方法を試します。

### 下準備 ###
Microsoft Excelを起動して、新しいシートに下記のように入力します
セル**A2**: 部門(Department)
セル**A3**: 会計期
セル**F7**: 収益
セル**F9**: 売上原価
セル**F11**: 粗利益
セル**F13**: 支出
セル**F15**: 純利益
セル**G4**: 実績(Actual)
セル**H4**: 予算(Budget)
セル**I4**: 差違(実績-予算)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/52854a50-9907-2625-6d04-e80628136f51.png)
更に、**収益**、**売上原価**、**支出**を表す勘定科目コードをセル**K7**、セル**K9**、セル**K13**にそれぞれ記入します。
`<<4000..4999`　これは勘定科目コードが4000から4999の範囲ですよという意味になります。
これらは、収益、売上原価、支出を表現するフィルター値となります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/84cbab9c-2bfb-3878-cb79-203f6605d65d.png)
セル**G17**、セル**H17**に`<ALL>`を記述します。これらのセルは、実績と予算を算出するクエリのコストセンターに関するフィルター値となります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/febc5cd3-54e9-50c8-c948-50387106cf47.png)
セル**B2**と**B3**の背景色を指定して、それぞれに`<ALL>`と入力します。
これらは、これから作成するSharperlight数式において、部門と会計期を絞り込むためのフィルターとなります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e251a55e-f90f-752b-c16e-2f0aab47bcf1.png)
### 実績(Actual) : 収益の取得 ###
ではSharperlight**値数式** `mdValue()`を使用して、実績の収益合計を求めるクエリをセル**G7**に作成します。
セル**G7**を選択し、Sharperlightリボンから**数式-値**アイコンをクリックし、クエリビルダを起動します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f0e480fb-0b6b-7d3e-9229-a27448bd5d1b.png)
#### フィルターの設定 ####
下記のようにフィルター値及びフィルターセル参照を設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f906834e-845e-52e9-3dbf-49a24e399c7c.png)
#### 出力値の設定 ####
選択領域から金額値が記録されている`Amount(s)`フィールドを出力領域に設定し、出力オプションでは、`逆符号`オプションと`合計`オプションを指定します。（逆符号オプション：今回使用しているサンプル会計データベースでは負の値として記録されているので、このオプションを利用して正の値に変換している）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/106321c9-d2af-743a-e586-9eb814dc5db1.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/168514cb-7a88-691d-a92f-24de323992f6.png)
では、`OK`ボタンを押してクエリの確定を行います。
セル**G7**にSharperlight値数式が設定され、クエリ結果が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1fd9d06b-dc3d-058a-558b-6ba7fd32ba56.png)
### 実績(Actual) : 売上原価の取得 ###
セル**G7**をダブルクリックして先ほどのクエリ（実績：収益）を開きます。
フィルター領域の右クリックメニューから**クリップボードにコピー**を選びます。
セル**G9**に移動し、**数式-値**アイコンでクエリビルダを開きます。
フィルター領域の右クリックメニューから**クエリのインポート** -> **クリップボードから**を選びます。
これで先ほどのクエリがコピーされます。
`勘定科目`フィルターの参照セルを変更し、売上原価を表す勘定科目のみに絞り込みます。
その他のフィルターと出力フィールドには変更ありません。
出力フィールドの`逆符号`オプションは外します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d5f29d41-3688-e2c1-600a-cd4bd41f5201.png)
`OK`ボタンを押してクエリの確定を行います。
セル**G9**にSharperlight値数式が設定され、クエリ結果が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d39b3b60-944e-ead8-38ec-67aa3cd00ee3.png)
### 実績(Actual) : 粗利益の取得 ###
粗利益は実績-売上原価なので `=G7-G9`をセル**G11**に設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/81837bad-059d-0475-78b0-06d98524aab6.png)
### 実績(Actual) : 支出の取得 ###
セル**G9**をダブルクリックして先ほどのクエリ（実績：売上原価）を開きます。
フィルター領域の右クリックメニューから**クリップボードにコピー**を選びます。
セル**G13**に移動し、**数式-値**アイコンでクエリビルダを開きます。
フィルター領域の右クリックメニューから**クエリのインポート** -> **クリップボードから**を選びます。
これで先ほどのクエリがコピーされます。
`勘定科目`フィルターの参照セルを変更し、支出を表す勘定科目のみに絞り込みます。
その他のフィルターと出力フィールドには変更ありません。
出力フィールドの`逆符号`オプションは外します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b3513e89-96a9-bb8c-c7d9-f35c555c355c.png)
`OK`ボタンを押してクエリの確定を行います。
セル**G13**にSharperlight値数式が設定され、クエリ結果が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/232e7e19-6e3c-6d68-7210-686cb8c6bde3.png)
### 実績(Actual) : 純利益の取得 ###
純利益は粗利益-支出なので `=G11-G13`をセル**G15**に設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9969eeb9-226f-0cc8-5de7-51c030e1c7b7.png)
以上で、実績に関する計算式の定義は終了です。次は予算に関する計算式の定義に入ります。
### 予算(Budget) : 収益の取得 ###
予算の計算式を実績の計算式と同じように定義していきます。予算の収益合計を求めるクエリをセル**H7**に作成します。
セル**H7**を選択し、Sharperlightリボンから**数式-値**アイコンをクリックし、クエリビルダを起動します。
#### フィルターの設定 ####
下記のようにフィルター値及びフィルターセル参照を設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fde68661-527d-bc35-4e03-69ddb01f93d8.png)
#### 出力値の設定 ####
選択領域から金額値が記録されている`Amount(s)`フィールドを出力領域に設定し、出力オプションでは、`逆符号`オプションと`合計`オプションを指定します。（逆符号オプション：今回使用しているサンプル会計データベースでは負の値として記録されているので、このオプションを利用して正の値に変換している）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5b5d65a8-2c71-e861-cc1c-3275bb323859.png)
では、OKボタンを押してクエリの確定を行います。
セル**H7**にSharperlight値数式が設定され、クエリ結果が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/02b84fae-2a48-a8ce-0335-b8f74a71c897.png)
### 予算(Budget) : 売上原価の取得 ###
セル**H7**をダブルクリックして先ほどのクエリ（予算：収益）を開きます。
フィルター領域の右クリックメニューから**クリップボードにコピー**を選びます。
セル**H9**に移動し、**数式-値**アイコンでクエリビルダを開きます。
フィルター領域の右クリックメニューから**クエリのインポート** -> **クリップボードから**を選びます。
これで先ほどのクエリがコピーされます。
`勘定科目`フィルターの参照セルを変更し、売上原価を表す勘定科目のみに絞り込みます。
その他のフィルターと出力フィールドには変更ありません。
出力フィールドの`逆符号`オプションは外します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb73eea3-4503-36d1-ebfa-5df1333a5077.png)
`OK`ボタンを押してクエリの確定を行います。
セル**H9**にSharperlight値数式が設定され、クエリ結果が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6a60568e-717a-2394-0ecd-3d2fa2f9e317.png)
### 予算(Budget) : 粗利益の取得 ###
粗利益は実績-売上原価なので `=H7-H9`をセル**H11**に設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/646fc796-873b-f6b6-0d26-afe282f91ff5.png)
### 予算(Budget) : 支出の取得 ###
セル**H9**をダブルクリックして先ほどのクエリ（予算：売上原価）を開きます。
フィルター領域の右クリックメニューから**クリップボードにコピー**を選びます。
セル**H13**に移動し、**数式-値**アイコンでクエリビルダを開きます。
フィルター領域の右クリックメニューから**クエリのインポート** -> **クリップボードから**を選びます。
これで先ほどのクエリがコピーされます。
`勘定科目`フィルターの参照セルを変更し、支出を表す勘定科目のみに絞り込みます。
その他のフィルターと出力フィールドには変更ありません。
出力フィールドの`逆符号`オプションは外します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ffb3ac5c-c413-8e5d-6556-b5a8bbcb8c07.png)
`OK`ボタンを押してクエリの確定を行います。
セル**H13**にSharperlight値数式が設定され、クエリ結果が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a4c56aa8-d6c7-90a2-96b7-bab368226303.png)
### 予算(Budget) : 純利益の取得 ###
純利益は粗利益-支出なので `=H11-H13`をセル**H15**に設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f8d59fd0-e310-1edf-06c4-cad0d2eefa65.png)
以上で、予算に関する計算式の定義は終了です。次は差違列への計算式の定義です。
### 差違(実績-予算) ###
セル**I7**、**I9**、**I11**、**I13**、**I15**へはそれぞれ`=実績-予算`の計算式を設定します。
**I7**へは、`=H7-H7`といった具合にです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c87902a7-b81e-9bed-cbf1-cd3d8dc3421f.png)
全ての金額セルを円フォーマットにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/22d2c10b-05f9-de43-4a2f-a441eef6fff1.png)
セル**B3**に`2015/007`という会計期を設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/90bf68ec-9fb6-0f2e-132e-f942400f796d.png)
### シート分割数式の定義 ###
以上でテンプレートシートの設定が終了したので、シート分割数式の定義に入ります。
シート分割数式は、テンプレートを指定したフィルター値別に複製する数式です。今回は部門別に分割してみます。
Sharperlightリボンから**スプリッター**アイコンを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b7bf0470-36ab-ea2f-e064-8d96beb3732f.png)
`スプリッター`の操作パネルがシート左側に表示されます。
先ず、双眼鏡アイコンをクリックして、どのようなフィルターがシート分割に利用できるかを検索します。
下記のような結果となりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3720d94d-977e-6def-ef84-238070ea55d9.png)
`Department:$B$2`を選択し、下部に表示されるすべての値にチェックをいれます。全ての部門を対象にするといった意味合いです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/97a1a28d-ed7e-408b-c1d1-7eec0379aad7.png)
次に、セル**A1**を選択し、リンクアイコンをクリックします。シート分割数式がセル**A1**に設定されます。
```
=mdSplitSheet("Department : $B$2","100,200,300,400,500,600","",$B$2,"Green",0)
```
この段階では、このシート分割数式のオプション等には初期設定が使われています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/42f61f61-7077-3aeb-6290-9569ee2a6d1d.png)
テンプレートのシート名も変更しておきましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/dd292dc8-2f95-ccb5-4953-5b6feaf79b51.png)
では、Sharperlightリボンから**再計算 -> ワークシート**アイコンを押して実行してみましょう。
部門コード別にシートが複製されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/92f0ca93-5026-a882-b80f-739f4a7504e3.png)
分割されたシートの名前が部門コードになっています。このままでは意味がわかりませんね。部門名に置き換えたいです。
シート分割数式を修正してみましょう。
その前に分割されたシートを削除します。
Sharperlightリボンから**ツリー**アイコンを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/37bdbf11-f76c-729d-6b76-f1c84f61b65e.png)
シート右側にツリーパネルが表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3d8cdf2b-0dbd-fe73-1a85-db558ab4a753.png)
`Departmnt:$B$2`ノードを選択して、削除アイコンを選択し制御シート以外のシートを全て削除します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/de9b65f1-e46c-6887-659e-d0971725cfb0.png)
では、部門コードを部門名に読み替えるマッピングテーブルを作成し、その参照をシート分割数式に追加します。
新しいシートを制御シートの左側に追加します。そして次のような表を作成し、名前を与えます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2df1defb-5096-843c-8f18-8c35114f427e.png)
制御シートに戻り、セル**A1**を選択し、フォーミュラーバーに数式を表示します。
以下のように部門コードのリスト部分をマッピングテーブルの名前に置き換えます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c2165e83-c88a-35ff-6b4f-6490d06ca0d6.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/dbfe7cbf-bbdc-8a4b-581f-1edb1e1cfdef.png)
Sharperlightリボンから**再計算 -> ワークシート**アイコンを押して再度実行してみましょう。
シート分割されたシート名が、マッピングテーブルで部門名に読み替えられています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d24dad87-e3c5-079d-3910-369f285af30d.png)
このようにシート分割数式は、テンプレートと同じフォーマットのシートが、キーとなるフィルター値別に作成される便利な数式です。
制御シートに戻り会計期を変更し再度実行すると、新しい会計期で全てのシート上の数式が再計算されます。
少し長くて複雑になってしまいましたが、拝見ありがとうございました。
シート分割以外にも、**列分割**や**行分割**機能もあるので追々記事にしていこうと思います。

では、失礼します。