ひとり税理士のＤＸ -13.関数
ＧＡＳやＶＢＡを利用する前に、Googleスプレッドシートやエクセルで使える関数も使えるようにした方が良いと思います。
関数で簡単に結果が出るのであれば、それに越したことはないでしょう。
しかし、関数の数が多すぎて、とても覚えられません。
各表計算ソフトの「ヘルプページ」を見ながらマスターしてください。
Googleスプレッドシートの関数とエクセルの関数を見比べてみると、ほとんど同じです。
今回は、税理士が使うと便利かなと思う関数を紹介します。
別の表計算のデータを利用する方法
「データ」のシートと「集計表」等の計算シートは、できるだけ別にした方がいいと思います。
関与先には、不要な情報はなるべく外し、必要な情報だけを提供した方がわかりやすいと思いますし、個人情報が含まれているファイルを沢山作成することは、セキュリティ上好ましくありません。
また、一つのファイルのデータ量は軽くした方が早く処理できます。
「データ」のシートと「集計表」等の計算シートの連携は、エクセルの場合、「リンク貼り付け」等の操作でできます。Googleスプレッドシートでは、IMPORTRANGE等の関数でできます。
エクセルの場合、フォルダ名やファイル名を変更するとリンクが解除されてしまい、「再リンク」する必要が生じます。
Googleｽﾌﾟﾚｯﾄﾞｼｰﾄの関数 (IMPORTRANGE)でデータ連携
Googleスプレッドシートの場合、別のスプレッドシートの「ＵＲＬ」で連携します。
下図では、「DATA」という名前のスプレッドシートの「data」シートにデータが格納されていて、スプレットシートのＵＲＬは、アドレスバーに表示されている「/edit#git・・・」前の部分です。
関数のヘルプページには、
これを参考に、リンク先のセルをクリックし、
上図の例では、「DATA」スプレッドシートの「data」シートの「A」列から「C」列のデータを読み込みすることになります。
初めてリンクする際、下図のように「アクセスを許可」するよう指示が表示されます。
リンク先のＵＲＬが間違っていると、
リンク先のファイルにアクセス権がない場合もエラーが表示されます。
UNIQUE関数で利用している科目を抽出
Googleスプレッドシートにもエクセルにも「UNIQUE」という関数があります。ヘルプページには、下図のように記載されています。
説明を見ても、ピンと来ないので、例で説明します。
下図の「リンク」シートのＣ列に入力されている勘定科目のリストを作成しようと思います。
新しいシートの「A1」セルに、下図のように入力します。
上図のように、重複なしのリストが表示されました。
SUMIFS関数で月次推移表を作成
上で作成した勘定科目のリストの表を加工します。
1行目に行を挿入し、「A1」セルに該当年を入力します。
「B2」～「M2」に月を追加します。
「B3」セルに、月ごと・勘定科目ごとの合計を表示する数式を入力します。（下図のとおり）
SUMIFS関数について
「B3」セルの数式は、
上数式の例では、
「'リンク'!」は「リンク」シートのこと
「$B:$B」は「B列」の範囲を指定
「$」は絶対参照。数式をコピーしても指定した列は「B列」のまま（「$」を付けないで数式を他の列にコピーすると、「B列」以外になってしまいます。）
「条件１」は、「指定した年」の「指定した月」の「１日」以降を指定しています（Date関数を使用しています）。
「条件２」は、「指定した年」の「指定した月の翌月」の「１日」前を指定しています。（「2022年12月」の翌月は、「2022年13月」とならないのかと思ったのですが、「2023年1月」で判断されているようです。）
体裁を整えるために「表示形式」を変更
「B2」～「M2」も「#月」の表示形式に変更
「B2」セルに「1月」と入力したら、どうなると思います？
QUERY関数で元帳作成
Googleスプレッドには、QUERYという関数があります。
下図の数式で、元帳を作成することができます。
ＳＱＬをかじったことがある人なら、簡単に使いこなせると思います。
プログラムが作れるようになると関数を覚えなくなってしまいます・・・関数を理解するより、自分で関数を作ってしまえ・・・と思うので。
でも、Googleスプレッドシートを使い始めて、関数を調べるようになったら、こんなに沢山あるのかと・・・。
また、関数を使った方が楽に処理できるものもあるということがわかりました。