複数シートはVSTACK関数とFILTER関数で統合！
Excel処理で後工程も効率化する！

「Excel」も「関数」もおなじみのツールのはずです。

とはいえ、結果には依然としてポカ～ンとしてしまいます(笑)。

大量のデータや繁忙期は効率化を進める機会です。

関数で統合　データは入手したがやる気が出ない！？

税理士業は資料がないとどうにもならない面があります。

たとえば、確定申告。

事業所得であれば帳簿・決算書・申告書作成のために
それぞれ必要な資料が必要です。

漏れなく迅速に資料収集ができるかどうかが仕事の分かれ目です。

一方、資料があればOKか？というと、困るときもあります。

先日、お客様から仕訳日記帳のデータをPDFファイルで入手しました。

重要な資料なのですが、PDFでは柔軟な運用が困難です。

私は「PDFelement」でファイルをExcelファイルに変換しました。
　(デスクワークはPDFの扱い方で大差がつきます！)

Excelファイルへの変換はサクッと済んでホッとしましたが、
ワークシートが「96」…(笑)。

まとめ用のワークシートをコピー・アンド・ペーストで作成は
パッとしないどころかやる気すら起きません(笑)。

関数で統合　VSTACK関数とFILTER関数を利用

複数のワークシートを統合するために「Power Query」も選択肢ですが、
Excel定番の処理である「関数」でも可能です。

複数のワークシートの統合では以下の関数を組み合わせて使いました。

• VSTACK関数‐垂直方向にデータを追加していく
• FILTER関数‐関数で目的のデータをフィルター(抽出)する

メインはVSTACK関数ですが、FILTER関数を使うことで実用性が上がります。

複数のワークシートの統合は以下の手順で進めます。

1. VSTACK関数でワークシートを統合して、
2. 無駄な部分をFILTER関数で取り除く

■第1段階　VSTACK関数でワークシートを統合

新規のワークシートにVSTACK関数を入力していきます。

• =VSTACK(対象ワークシート!対象セル範囲)
• 対象セル範囲‐各ワークシートの見出しより下

対象セル範囲はワークシートごとに異なるので広めに設定します。

関数を入力すると、あっさり複数のワークシートが串刺しで統合されます。

上記のVSTACK関数は以下の処理を設定しています。

• ワークシートTable1からTable96のシートを対象に
• 各ワークシートのA2からF30までのセルを
• 垂直に統合する

■第2段階　FILTER関数で無駄な部分を取り除く

第1段階のワークシートでは対象セル範囲が広く設定されており、
無駄な部分が目立ちます。

VSTACK関数の処理にFILTER関数を組み合わせることで、
無駄な部分を取り除きます。

• =FILTER(VSTACK関数で選択した範囲, 抽出条件)
• 抽出条件‐VSTACK関数で返された値がゼロ以外

関数を入力すると、スッキリとした表にまとまります。

上記のFILTER関数の設定は以下の通りです。

• VSTACK関数で選択した範囲を対象に
• VSTACK関数で返された値がゼロ以外の値を
• FILTER関数で抽出する

関数で統合　後工程でもExcelと関数を利用

複数のワークシートをを統合すると、一仕事終えた気分になります。

錯覚です(笑)。

本来やるべきだった業務にようやく着手できます。

統合したシートにまとめた後はデータを利用する段階となります。

ここでもExcelの機能を使っていきます。

私がワークシート統合後に行った処理は以下の通りです。

• 統合したワークシート全体を選択して、
• 新しいシートに値を貼り付けて、
• 表をテーブル化して、
• 「AGGREGATE」関数で集計機能を追加

ワークシート全体の選択はワークシート左上をクリックします。

値の貼り付けはワークシート全体を選択した状態で右クリックより選べます。

AGGREGATE関数はマイナーな印象ですが集計用の関数です。

下記のAGGREGATE関数の設定では、

• 「9」‐集計方法、合計値を指定
• 「5」‐オプション、表示を無視する
• 「C4:C3000」-集計範囲、C列4行目から3,000行目まで

上記の処理はどれもExcelの定番の機能でしかありません。

とはいえ、せっかくのデータも扱えるだけの準備なしでは
宝の持ち腐れになります。

「Excel」も「関数」もおなじみのはずのツールですが、
劇的に効率化できる余地はあります。

とりあえず試してみるかな！？がおすすめです。

 

蛇足
Excelの「関数」は一行の入力で劇的に業務が改善できます。
反面、期待過多で関数の入力でヘマをすると凹みます(笑)。
FILTER関数で「<>0」と入力するところを「<>""」で入力して、
エラーが出てしまい慌てました。
失敗は次の機会に活かすしかありませんね。

蛇足2
アイキャッチ画像は散歩中にみかけた光景です。
2月になると稲作の準備も始まるようです。
トラクターが始動していたようでした。

＜ご案内＞

■林友範税理士事務所

ご依頼はこちら

■災害と税金の情報

災害と税金