複数シートは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月になると稲作の準備も始まるようです。
トラクターが始動していたようでした。

<ご案内>

■林友範税理士事務所

ご依頼はこちら

■災害と税金の情報

災害と税金