タグ別アーカイブ: 複数シート

フリーアドイン~目的シートの検索~

エクセルファイルは、データをシート別に区分して管理できる点がとても便利です

但し、あまりにシート数が多いと効率が悪いですよね

ファイルの中から目的のエクセルシートを探したり、目的のシートに移動したりするのはとても面倒なものです

今回、紹介するアプリ(アドイン)であれば、シート名に関連するキーワードでも、シートの検索が可能です

しかも、検索したシートへの移動もワンクリックで行えます!

このアドインがあれば、シート間のリンクを貼った目次シートも作成不要ですので、作業効率があがること間違いなしです!!

1.アドインを入手

エクセルの挿入タブから「アドインを入手」をクリックし、「Sheet Explorer」を検索しましょう!

「Sheet Explorer」が検索できたら追加ボタンを押しましょう

追加ボタンを押したら、HOMEタブにアドインが追加されているはずです

もし、入手の仕方がよくわからない、もしくは、実際に使用して見て不明な点がありましたら、こちらの記事もご参照ください

2.「Sheet Explorer」を実行

入手したアドインを実行するには、アドイン自体をクリックします

すると、ファイルに含まれるシートが一覧で出力されます

一覧が出力された後は、シート名を含むキーワードでのシート検索も可能になります

シート一覧
シート一覧取得

3.キーワードでシート検索

検索窓にキーワードを入れれば、自動的に検索が行われます

シート検索

検索が終了した後は、リフレッシュを押せば元のシート一覧が再出力されます

4.シートへの移動

検索したシートへの移動は該当のシート名をクリックするだけで行われます

シート間
シート移動

5.シート内の関数を把握

このアドインが優れているのは、シート内の関数の数や、関数の他シートへのリンク状況も把握できる点です

例えば、上の画像のシートであれば

⇒シート内の関数は他の6シートを参照している

⇒シート内には36の関数がある

ということが分かります

<まとめ>

今回、「Sheet Explorer」で行える機能を3つ紹介しました

ⅰ)シート検索

ⅱ)シート移動

ⅲ)シート内関数の状況把握

ぜひ有効活用して、シート管理を効率化していきましょう

もしシートを追加したり、シートの中に関数を追加したりした場合には、3.で紹介したリフレッシュボタンを押してください

尚、アドインを追加する際には規約等の同意が求められますので、こちらはご確認の上でアドインをご使用ください

他にも便利なアドインを紹介していますので、興味のある方はこちらから!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

複数シートを自動集計~3D参照~

筆者は昔、予算管理の仕事をしていましたが、いつも複数シートの集計に苦しんでいました

月ごとの数字を管理するのに、ひたすらシート名+シート名+シート名のつるべ打ちをしていたのです

この方法だと手間がかかるだけでなく、シートを追加したり、削除した時にミスが起こり易いです

一方、今回の回で紹介する3D参照では、一気に複数シートをSUM関数で集計することができます

シート追加や削除した時にも、追加・削除に連動して集計値が変わりますので、集計ミスも防げます

下のGIFではシート追加前は200だったセルが、追加後は追加したシートの数字20が自動的に追加されています

この3D参照は特に難しい技術は必要なく、あくまで文法の問題だけをクリアすれば誰でも活用できます

それでは3D参照についてのポイントを2つ、以下に解説します

A.文法

以下は複数シートをシート名のつるべ打ちで集計する数式です

シート名とセル名を除くと同じパターンで文字が出てくるのが分かりますでしょうか?

 =’シート名’!セル名+’シート名’!セル名+・・・

今回の3D参照では、+でシート名を足す代わりに「’と’!」の中で、通常のSUM関数と同じ様に”:”で範囲を指定するのです

こんな↓感じです

B.ワイルドカード*

シート名を工夫した上での前提ですが、*カードを使って更に複数シートの集計作業の効率化を行うことができます

例えば、シート名を四半期単位で集計できるように工夫しておきます 

そうすれば、ワイルドカードを使った集計が行えるようになります

例えば、シート名が1Q_4月、1Q_5月、といった具合に並んでいる場合は、1Q以降を*で置き換えます

このワイルドカードを使えば、SUM関数内に1つのシートでも指定すれば、後は必要箇所を*で置き換えるだけですので、とても効率的です

ポイントを2点、文法とワイルドカード*について解説したところで、ぜひ、以下のサンプルファイルをダウンロードして頂き、実際のデータで試してみてください

サンプルファイルは完成と演習の2つのシートがあります

演習のシートでは実際の操作の様子を参照できるように、2つの方法を用意してあります

どちらもフリーアドインを使用した方法です

①Web Video Player

このアドインはエクセルシート上で動画を開くことができます

演習シート上に記載されたURLをWeb Video Playerにコピーして使用してください

➁QRコード

演習シート上のQRコードをスマホで読み込んでください

動画が自動的にスマホ上で開きます

<まとめ>

今回は、複数シートの集計を、シート名+シート名のつるべ打ちではなく、3D参照によりSUM関数で一気に集計する方法を学びました

3D集計は複雑ではないのですが、ひとつでも記述を間違えただけでもエラーになってしまいます

ですから、’や!の位置をひとつひとつ確認して頂きながら作業を進めていけばスムースに作業を完了させることができます!

後は、サンプルファイルを基にして、とにかく手を動かしてみましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村