元帳摘要集計表の活用
CSVファイルからの読み込みが終了しましたので、データを抽出しましょう。
預り金の補助元帳の源泉徴収税で入力された取引の抽出ができました。それでは、ワークシート関数だけでデータを抽出し、表を作成してみましょう。結果は、次の通りになります。弥生会計のときと違い、外注費が消費税込みの金額で集計されています。
それでは、ワークシート関数を紹介します。配列式を使用していますので、式の確定入力は「[Ctrl]+[Shift]+[Enter]」と入力してください。
システム工房
外注費
{=SUM(IF((R2C7:R1388C7=R1390C11)*(R2C19:R1388C19=RC[-1])=1,R2C11:R1388C11))}
預り金
{=SUM(IF((R2C13:R1388C13=R1390C12)*(R2C19:R1388C19=RC[-2])=1,R2C17:R1388C17))}
夢ソフトとギガ開発についても、全く同じ数式です。それでは、数式の内容を簡単に説明しましょう。システム開発の現場では、アルゴリズムなんて言われていますが、そんな大袈裟なものではありません。
言葉で表現すると、「借方科目のセルの値が外注費。摘要のセルの値がシステム工房。この2つの条件を満たす場合、借方金額のセルを合計しなさい。」となります。それでは、部分的に紹介していきましょう。
R2C7:R1388C7=R1390C11
絶対参照で指定したセル範囲のセル(R2C7:R1388C7)の値が、同じく絶対参照で指定した別のセル(R1390C11)の値と等しいかどうかを検査し、等しければ「TRUE」、等しくなければ「FALSE」を返します。
(R2C19:R1388C19=RC[-1])
絶対参照で指定したセル範囲のセル(R2C19:R1388C19)の値が、今度は相対参照で指定した別のセル(RC[-1])の値と等しいかどうかを検査し、等しければ「TRUE」、等しくなければ「FALSE」を返します。
(R2C7:R1388C7=R1390C11)*(R2C19:R1388C19=RC[-1])=1
それぞれのセル範囲の検査の結果の論理積を検査します。
???なんてなっていませんか。「TRUE、FALSEとか論理積って何???」ですね。IF関数を思い出してください。IF(論理式,真,偽)ですね。書き換えるとIF(倫理式,TRUE,FALSE)です。つまり、TRUEは真。FALSEは偽です。では、論理積とは???簡単にいうとAND関数と同じ働きです。つまり、「何々かつ何々」です。したがって、上記の式は次のように書き換えることができます。
AND(R2C7:R1388C7=R1390C11,(R2C19:R1388C19=RC[-1]
もっと簡単に言うと「積」ですから、掛け算ということです。では、何と何を掛けているのでしょう。また上記の式を書き換えてみましょう。
TRUE*TRUE=1
真と真を掛けています。EXCELの場合、TRUEは「1」、FALSEは「0」という整数値です。また、多くのコンピュータ言語でBoolean型の定数としてTRUEとFALSEがそれぞれ「1」と「0」の値が返ってきます。そうすると、上記の式をさらに書き換えると。
1*1=1
当たり前ですね。結局、「2つの条件を2つとも満たす場合」という条件をIF関数に渡しています。指定したセル範囲で、掛け算の結果が「1」のなるときだけ合計するという式です。ただし、この式を通常の確定方法で入力しても正しい結果が得られません。配列式にすることで期待する値が得られます。配列式の詳細な説明は、EXCELのヘルプを参照してください。
さて、表を完成させましょう。先にワークシートを一つ挿入させておきます。そして、計算して集計した表を選択して、「編集」メニューより「コピー」を選択します。
コピーの次は貼り付けですね。ここでも単に貼り付けるのでなく、値だけ貼り付けます。
罫線を設定します。
数字の表示を整えます。
各列の幅を調整し、セル[R1C1]に表題を入力して表の中央に配置されるように調整します。
ようやく表が完成しました。
ここで注意点を少し紹介しておきましょう。
EXCELの行は、最大65,536行しかありません。その数を超える取引量は、読み込むことができません。その場合は、弥生会計の仕訳日記帳をエクスポートする前に、ある程度仕訳データを絞っておく必要があります。
また、エクスポートの形式ですが、通常は標準インポート形式のカンマ区切りのCSV形式でよいのですが、取引量が多いとEXCELでの読み込みが正常に行われず、いわゆる文字化けの状態になってしまします。今回、1,300程度の取引量でしたが、文字化けを起こしてしまいました。できれば、弥生会計以外のアプリケーションでインポートする予定のときは、「カンマで区切られた形式」を選択することをお勧めします。
戻る|インデックス|次へ