creemaで書き出せるCSVを使ってMFクラウドの仕訳作業を自動化(3)
■前回まで
MFクラウドでの仕訳作業を自動化しよう！の3回目です。
仕訳作業を自動化(1)：自作のエクセルデータの配信と、数式の紹介
仕訳作業を自動化(2)：基本の数式の解説
今回は、前回解説していない列の数式と、
仕訳を2行しか使わない、4行ある、といった方向けのアレンジ方法をご説明します。
■他の数式の解説
| A列 | =QUOTIENT(ROW(),3) |
＜1つの仕訳ごとに1,2,3と番号を振りたい＞
現在のセル行を取得、3で割ったときの整数を取得
3行ごとに1,2,3と増えていきます
| B列 | =INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3), 2) |
＜売上表の「確定日」を表示したい＞
A3〜N300の範囲で、
(現在のセルの行数÷3の整数行,2列目)
のセルの内容を表示
| C列 | =IF(MOD(ROW(), 3)=0,”売掛金”,IF(MOD(ROW(), 3)=1,”販売手数料”,IF(MOD(ROW(),3)=2, “”))) |
＜1仕訳の中で1行目は「売掛金」、2行目は「販売手数料」、3行目は空白にしたい＞
現在のセルの行数÷3の余りの数値ごとに、3パターンの指示を出す。
| D〜F列 | =なし |
| 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, “”))) |
＜基本の数式＞
| H列 | なし |
| I列 | =IF(MOD(ROW(), 3)=0,”売上高”,IF(MOD(ROW(), 3)=1,””,IF(MOD(ROW(), 3)=2, “荷造運賃”))) |
＜1仕訳の中で1行目は「売上高」、2行目は空白、3行目は「荷造運賃」と表示したい＞
現在のセルの行数÷3の余りの数値ごとに、3パターンの指示を出す。
| J列 | =IF(MOD(ROW(), 3)=0,”アイテム販売”,IF(MOD(ROW(), 3)=1,””,IF(MOD(ROW(), 3)=2, “”))) |
＜1仕訳の中で1行目は「アイテム販売」、2行目は空白、3行目も空白としたい＞
現在のセルの行数÷3の余りの数値ごとに、3パターンの指示を出す。
| K〜L列 | なし |
| M列 | =IF(MOD(ROW(), 3)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),8),IF(MOD(ROW(), 3)=1,””,IF(MOD(ROW(), 3)=2,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3), 11)))) |
＜基本の数式から取得する列だけ変更＞
| N列 | なし |
| O列 | =IF(MOD(ROW(), 3)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 3),3)&”様へのメッセージカード売上”,IF(MOD(ROW(), 3)=1,””,IF(MOD(ROW(), 3)=2, “発送料”))) |
＜1仕訳の中で1行目は「取引相手+様へのメッセージカード売上」、2行目は空白、3行目は「発送料」と表示したい＞
1行目は表のC列の情報を表示し、加えて「様へのメッセージカード売上」という分を表示。取得した情報に好きな文章を足すことも出来ます。
| P列 | なし |
| Q列 | =”creema” |
＜全てのセルで「creema」と表示したい＞
| R〜S列 | なし |
| T〜U列 | =NOW() |
＜セルを入力した日時を表示したい＞
■仕訳の行数によるアレンジ
私は3行に分けて仕訳していますが、2行だけだったり、4行あったりする方もいらっしゃると思います。
ご紹介した数式をアレンジすれば、色んなパターンに対応可能です！
基本の数式であれば、こんな感じ。
例えば、仕訳を2行に分けているパターン。
（1）「IF」関数を2つに減らす
3パターンに分けていたものが、2パターンだけで良いので、ひとつ減らします。
仕訳を4行にしている方は、逆に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, “”))) |
↓
| 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))) |
（2）割り算の余り、割り算の整数部分を計算する部分の「3」を「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))) |
↓
| G列 | =IF(MOD(ROW(), 2)=0,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 2),13),IF(MOD(ROW(), 2)=1,INDEX($売上表.$A$3:$N$300,QUOTIENT(ROW(), 2),10))) |
他の列の数式でも、「IF」を減らす、「MOD」、「QUOTIENT」を使った割り算の数値を変更する、という2点で応用ができます。
■数式ができあがったら
A3〜U3を選択して式の複製で、下にぐーっと引っ張ります。
1回目に配信したエクセルデータだとこんな感じに。
勝手にセルが埋まっていくのは快感です。
この後、CSVに書き出して、MFクラウドの仕訳帳からインポートして完了です！
一度数式を作れば、百件以上の仕訳でも10分で終わってしまいます。
あっという間！
面倒な確定申告が、少しでも楽になれれば嬉しいです！
2017.2.15
