複数シートはVSTACK関数とFILTER関数で統合！
Excel処理で後工程も効率化する！
「Excel」も「関数」もおなじみのツールのはずです。
とはいえ、結果には依然としてポカ～ンとしてしまいます(笑)。
大量のデータや繁忙期は効率化を進める機会です。
こちらも準備段階
目次
関数で統合 データは入手したがやる気が出ない！？
税理士業は資料がないとどうにもならない面があります。
たとえば、確定申告。
事業所得であれば帳簿・決算書・申告書作成のために
それぞれ必要な資料が必要です。
漏れなく迅速に資料収集ができるかどうかが仕事の分かれ目です。
一方、資料があればOKか？というと、困るときもあります。
先日、お客様から仕訳日記帳のデータをPDFファイルで入手しました。
重要な資料なのですが、PDFでは柔軟な運用が困難です。
私は「PDFelement」でファイルをExcelファイルに変換しました。
(デスクワークはPDFの扱い方で大差がつきます！)
Excelファイルへの変換はサクッと済んでホッとしましたが、
ワークシートが「96」…(笑)。
まとめ用のワークシートをコピー・アンド・ペーストで作成は
パッとしないどころかやる気すら起きません(笑)。
関数で統合 VSTACK関数とFILTER関数を利用
複数のワークシートを統合するために「Power Query」も選択肢ですが、
Excel定番の処理である「関数」でも可能です。
複数のワークシートの統合では以下の関数を組み合わせて使いました。
- VSTACK関数‐垂直方向にデータを追加していく
- FILTER関数‐関数で目的のデータをフィルター(抽出)する
メインはVSTACK関数ですが、FILTER関数を使うことで実用性が上がります。
複数のワークシートの統合は以下の手順で進めます。
- VSTACK関数でワークシートを統合して、
- 無駄な部分を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月になると稲作の準備も始まるようです。
トラクターが始動していたようでした。
＜ご案内＞
■林友範税理士事務所
■災害と税金の情報
■確定申告のご依頼も受付中！
