creemaで書き出せるCSVを使ってMFクラウドの仕訳作業を自動化(2)
■前回まで
MFクラウドでの仕訳作業を自動化しよう！の2回目です。
前回は、私の作ったエクセルデータの配信と、数式の紹介でした。
今回は、数式の解説をします。
前回紹介した「必要なデータ」を開きながらぜひご覧下さい。
■基本の数式
前回紹介した数式は10種類ありましたが、8種類はこれから紹介する数式でカバーできます！なので、まずはこの数式から解説しますね。
※エクセルの基本的な関数の書き方が分かる方向けの解説です。
個々の関数の使い方、説明などは検索をお願いします。
基本の数式はこれ。
| G列 | =IF(MOD(ROW(), 3)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),13),IF(MOD(ROW(), 3)=1,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),10),IF(MOD(ROW(),3)=2, “”))) |
「仕訳帳」のG列では何をやりたいかと言うと、
1つの仕訳で3行分使っているけど、その中で
1行目は→売掛金の項目なので、「売上表」から「振込予定金額」の金額を表示させる
2行目は→販売手数料の項目なので、「売上表」から「成約手数料」の金額を表示させる
3行目は→空白にする
という内容を一つの式で表したいのです。
なので、一つの式の中にIF関数を3つ重ねて、
もし1行目だったらこうする、もし2行目だったら…と書いています。
詳しい説明の前ですが、この数式のポイントは3つ。
（1）今のセルが1つの仕訳の中の、1〜3行目の何行目にあたるかを判断する。
（2）「売上表」から仕訳帳の項目に合わせた内容を表示させる。
（3）1つの仕訳につき、仕訳帳は3行分下にセルが進むのに売上表は1行しか進まないので、式の複製をするとズレが出てくる。このズレを無くす。
この3つをクリアするための数式を考えていきます。
■（1）1つの仕訳の中の何行目にあたるのかを判断する数式
| G列 | =IF(MOD(ROW(), 3)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),13),IF(MOD(ROW(), 3)=1,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),10),IF(MOD(ROW(),3)=2, “”))) |
基本の数式のこの部分に当たります。
現在のセルの行数を3で割った余りで、1つの仕訳の中で何行目にあたるかを分析。
こんな感じです。
1つ目の仕訳は3行目から始まるので、
3行目：3÷3=1余り0
4行目：4÷3=1余り1
5行目：5÷3=1余り2
余り0だと1つの仕訳の中の1行目、
余り1だと1つの仕訳の中の2行目…
となります。
ちなみに続きは、
6行目：6÷3=2余り0
で、1つの仕訳の中の1行目にあたり、合っていますね。
この「3で割った余りが0の場合は」を数式で表すと、
| 数式 | (MOD(ROW(),3)=0 |
| 内容 | (余りを計算する(現在のセルの行数を取得(),3で割る)=0 |
これで、今のセルが1つの仕訳の何行目にあたるかが分かります。
■（2）「売上表」から仕訳帳の項目に合わせた内容を表示させる数式
| G列 | =IF(MOD(ROW(), 3)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),13),IF(MOD(ROW(), 3)=1,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),10),IF(MOD(ROW(),3)=2, “”))) |
基本の数式のこの部分に当たります。
INDEX関数を使って、「売上表」から項目に合わせた内容を表示させます。
| 数式 | INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(),3),13) |
| 内容 | (「売上表」の「A3〜N300」の範囲の◯◯行目,13列目）のセルの内容を表示 |
途中の「◯◯行目」部分は、また別の数式が入っているので、次で説明します。
13列目はAから13番目の列ということなので、「M列」のことですね。
この数式で「売上表」のM列にある「振込予定金額」の数値を表示できます。
ちなみに基本の式で2回目に出てくるINDEXの列は「10」なのでAから10番目の列、「J列」=「成約手数料」のことです。
■（3）仕訳帳と売上表のセルのズレを無くす数式
先ほど保留にした、◯◯行目の情報を表示する部分の説明が、ズレを無くす数式と繋がっています。
| G列 | =IF(MOD(ROW(), 3)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),13),IF(MOD(ROW(), 3)=1,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),10),IF(MOD(ROW(),3)=2, “”))) |
基本の数式のこの部分に当たります。
＜式を複製してズレるとは？＞
ズレるのがどんな風かと言うと、例えば
「仕訳帳」G3=「売上表」M3
として式の複製を下にしていくと、
仕訳帳 G3 = 売上表 M3
仕訳帳 G4 = 売上表 M4
仕訳帳 G5 = 売上表 M5
仕訳帳 G6 = 売上表 M6
両方とも1行ずつ下に進んでいきます。
でもこれだと、
2つめの仕訳の1行目(G6)=「売上表」の4行目の振込予定金額(M6)
になってしまい、「売上表」の2〜3行目の内容が飛ばされちゃっています。
本当は、
仕訳帳 G3 = 売上表 M3
仕訳帳 G4 = 売上表 J3
仕訳帳 G5 = 空白
仕訳帳 G6 = 売上表 M4
「仕訳帳」が3行進むごとに、「売上表」は1行だけ進む様にしたいのです。
＜3行を1つのグループにする＞
エクセルの世界では、こっちは3行であっちは1行、というバラバラの数字は難しいんですね。
なのでこれを「1と1」になるように無理矢理します。
「仕訳帳」が1つ進むと、「売上表」も1つ進む。
そこで、「仕訳帳」の3行を1つのグループにしちゃいます。
そうすると「仕訳帳」1グループに対して、「売上表」が1行となって「1と1」、数字が同じになります。
実際の数式では（2）と似て、3で割った計算を使います。
こちらは余りではなく、割った時の整数を利用。
3行目：3÷3=1
4行目：4÷3=1.333…
5行目：5÷3=1.666…
6行目：6÷3=2
7行目：7÷3=2.333…
8行目：8÷3=2.666…
割った整数だけ取り出すと、
3〜5行目：1
6〜8行目：2
となり、3行分が1つのグループになりました！
これで「仕訳帳」が1グループ進むと、「売上帳」が1行進む、という仕組みができます。
＜数式で表す＞
これを数式で表してみます。
| 数式 | QUOTIENT(ROW(),3) |
| 内容 | 割った時の整数を計算(現在のセルの行数を取得(),3で割る) |
これが、
1つ目の仕訳（3〜5行目）：1
2つ目の仕訳（6〜8行目）：2
3つ目の仕訳（9〜11行目）：3
・
・
↓以下つづく
となります。
ここで（2）で説明した数式に戻ります。
| 数式 | INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(),3),13) |
| 内容 | (「売上表」シートの「A3〜N300」の範囲の◯◯行目,13列目）のセルの内容を表示 |
の◯◯行目の部分に上の説明の整数が入るので、
3〜5行目：
INDEX($売上表.$A$3:$N$300,1,13)
「A3〜N300」の範囲の1行目,13列目=M3
6〜8行目：
INDEX($売上表.$A$3:$N$300,2,13)
「A3〜N300」の範囲の2行目,13列目=M4
となって、
仕訳帳が3行進むごとに、表は1行だけ進む。が出来る様になりました。
■（1）〜（3）を繋げる
今までやった3つのこと＋IF関数を使って、3パターンの内容を盛り込みます。
IF関数の中に更にIF関数をつなげて3段階に。
| G列 | =IF(MOD(ROW(), 3)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),13),IF(MOD(ROW(), 3)=1,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),10),IF(MOD(ROW(),3)=2, “”))) |
この数式を日本語で表すと、
もし
現在のセルの行数を3で割った時の余り=0の時は、
「売上表」シートの「A3〜N300」の範囲の
現在のセルの行数を3で割った時の整数の行数目、13列目
のセル内容を表示する
現在のセルの行数を3で割った時の余り=1の時は、
「売上表」シートの「A3〜N300」の範囲の
現在のセルの行数を3で割った時の整数の行数目、10列目
のセル内容を表示する
現在のセルの行数を3で割った時の余り=2の時は、
空白にする
となります。
な、長い…数式だとかなりコンパクトに指示できるんですね。
これで基本の数式の解説は終わりです！
かなり長かったですが、なんとなく分かりましたでしょうか？
今回使った関数は、
IF(もし)
MOD(割り算の余り)
ROW(現在のセルの行数を取得)
INDEX(範囲の中のセル位置を指定)
QUOTIENT(割り算の整数部分)
の5つでした。
前回紹介した他の数式も同じ関数を使ってアレンジしただけなので、
分かる方は見てみてください。
次回、G列以外の数式の簡単な解説と、
仕訳を2行しか使わない、4行ある、といった方向けのアレンジ方法をご説明します。
2017.2.10
