事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

Excelの機能を最大限に生かして、業務の省力化や経営状況を分析できる資料の作成方法を解説します。

1つの表を「データ別に複数のシートに分割」する(取得と変換)

"取得と変換" の機能を使えば、1つの表を「データ別に複数のシートに分割」することができます

 

1つの表を、内容に応じた「複数の表(シート)に分割する

 複数の情報が入った「1つの表」「情報別に別々の表(シート)に分割するのは手間のかかる作業です。

 取得と変換の機能を使えば、簡単な操作で「表を情報別に別々の表に分割」することができます。

 そして、取得と変換の機能で "分割" した「表」「元の『表』の内容が変更されても最新の情報に更新する」ことができます。

 

「職員一覧表」「部署別の職員一覧」にする

 全部署が含まれた「全職員の一覧表」「部署別の表」に変換します。

  f:id:k-ohmori9616:20210217105225p:plain

"Power Query"を使って、「クエリ」を作成する

 「クエリ」は、"表(テーブル) のデータ" を「抽出」「並べ替え」「演算」「結合」「分割」することができる「仕組み」です。

 "取得と変換" は、外部プログラム(アドイン)の "Power Query" を使って「クエリ」の操作をExcelの標準機能のように使えるようにしたものです。

 「データの取得と変換」を実行すると、自動的に「Power Query エディタ」が立ち上がります。

※ "Power Query エディタ" が立ち上がると、"エディタ" を終了させるまでは一時的に "Excel" が使えなくなります

 

「表」を選択して、"PowerQueryエディタ"を立ち上げる

 分割したい「表」を選択して "PowerQueryエディタ" を立ち上げます。

 ※「表」が、"テーブル化" されていない場合は「テーブル」に変換されます

  1. 分割する「表」のどこか1つのセルを選択(※ アクティブ状態にする)
  2. 「データ」タブを選択
  3. 「テーブルまたは範囲から」をクリック
  4. テーブルの作成ダイアログで、変換(分割)する「表」が正しく選択されているのを確認
  5. 「先頭行をテーブルの見出しとして・・・」にチェック
  6. 「OK」ボタンをクリック

  f:id:k-ohmori9616:20210218103029p:plain 

表を「テーブル化」して作業効率を上げる - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

 

"PowerQueryエディタ"が立ち上がる

 選択した(アクティブセルがある)表が読み込まれた "Power Query エディタ" が立ち上がります。

 この ”Power Query エディタ"終了させる(閉じる)まで、Excelの操作はできません

  f:id:k-ohmori9616:20210218103254p:plain

"クエリの一覧" を開く

 画面の左にある「クエリ」の上の「 」をクリックして、"クエリの一覧" を開く

 作成済みの「クエリ」の一覧は画面左端に表示されます、表示されない場合は「>」ボタンをクリックすることで展開することができます。

  f:id:k-ohmori9616:20210217112938p:plain

 

部署「会計」"クエリ"を作成

 今回作成する「クエリ」は、フィルター機能で「会計」のデータだけに絞り込む内容です。

 作成した「クエリ」は、作成順に「テーブル1」「テーブル2」のように「テーブル名」が付けられています。

 クエリの名前を「会計」に変更し、「部署名」の項目のフィルター機能を使って、「会計」のデータに絞り込みます。

  1. クエリの設定のボックスのプロパティで、名前を「会計」に変更します
  2. 「部署名」の項目ラジオボタン「▼」をクリック
  3. フィルターのダイアログが開くので「すべて選択」を解除
  4. 「会計」のボックスにチェックを入れて選択
  5. 「OK」ボタンをクリック

  f:id:k-ohmori9616:20210217113558p:plain

 

「会計」の"クエリ"を複製して、他の部署の「クエリ」を作成

 作成した「会計」のクエリ複製して、他の部署のクエリを作成します。

  1. 「会計」のクエリを、右クリック
  2. 開いたダイアログで「複製」をクリック

  f:id:k-ohmori9616:20210218083459p:plain

 

複製(コピー)した「会計」のクエリを使って「営業部A」のクエリを作成

 複製コピー)した「会計」のクエリ「会計(2)」を使って、「営業部A」のクエリを作成します。

  1. 「部署名」のラジオボタン「▼」をクリック
  2. フィルターのダイアログで「営業部A」にチェックを入れる
  3. 「OK」ボタンをクリック

  f:id:k-ohmori9616:20210218105056p:plain

クエリの名前を変更し、同じ操作で他の部署を作成(複製)する

 クエリの名前を「営業部A」に変更し、同じように複製(コピー)の操作を繰り返して他の部署のクエリを作成する。

  1. クエリの設定のプロパティの名前を「営業部A」に変更
  2. 「営業部A」を複製する

  f:id:k-ohmori9616:20210218085555p:plain

作成した「全部署のクエリ」"接続"を作成する

 操作(複製とフィルター操作)を繰り返して、すべての部署の 「クエリ」 を作成が終わったら、Power Query エディタを閉じて(終了して)"Excel" と "Power Query エディタ" の「接続」を作成します。

 "クエリ" と "Excelのブック" の「接続」を作成することで、「クエリ」の内容を「テーブル」として表示することができます。

 クエリと接続されている「テーブル」は、"更新" することで最新の状態になります。 

  1. 「閉じて読み込む」ラジオボタン「▼」をクリック
  2. プルダウンリストから「閉じて次に読み込む」をクリック

  f:id:k-ohmori9616:20210218092307p:plain

「データのインポート」で、"PowerQueryエディタ"との接続を作成

 "PowerQueryエディタ" が閉じ(終了し)、Excelの画面に戻ります

 「データのインポート」のダイアログが開くので、「接続の作成のみ」を選択します。

  1. ブックで表示する方法を「接続の作成のみ」にする
  2. 「OK」ボタンをクリック

  f:id:k-ohmori9616:20210218092722p:plain

「元のデータ」のシートに、クエリとの接続が作成される

 「元のデータ(表)」があるシートに、クエリ(Power Query エディタ)との「接続」が作成されます。

 この「接続」により、Excelの「シート」とPower Query エディタとの連携(関連付け)がされた状態になります。

  f:id:k-ohmori9616:20210218095858p:plain

 

「クエリ」をExcelに読み込ん「ワークシート」を作成

 部署ごとの「接続」を使って、「クエリ」、の内容をExcel「ワークシート」に "テーブル"として読み込んでいきます。

  1. 「会計」のクエリを "右クリック" する
  2. プルダウンリストから「読み込み先...」を選択
  3. データのインポートのダイアログで表示方法「テーブル」にする
  4. データを返す先を「新規ワークシート」にする
  5. 「OK」ボタンをクリック

  f:id:k-ohmori9616:20210218095932p:plain

 

クエリがExcelに読み込まれ、「会計」のワークシートが作成

 クエリとの "接続" の機能で、「会計」のクエリの内容が、Excel "テーブル" に変換されて、新しい「会計」のワークシートが作成されます。

  f:id:k-ohmori9616:20210218100126p:plain

操作を繰り返して、すべての部署の「ワークシート」を作成

 同じ操作を繰り返して、すべての部署のワークシートを作成していきます。

  f:id:k-ohmori9616:20210218112653p:plain

元のデータの変更内容が、作成した「ワークシート」に反映される

 取得と変換で、分割(作成)した「テーブル(ワークシート)」は「クエリ」と "接続" された状態です。

 クエリを「更新」することで、"クエリ" で作業(抽出)が行われ、「テーブル」は最新の内容になります。

 

営業部A「新入社員」を追加

 「元の表(テーブル)」に、「営業部A」の「新入社員」のデータを追加して、クエリを「更新」すると、テーブル「営業部A」に追加されます。

  f:id:k-ohmori9616:20210218101641p:plain

クエリを「更新」する

 「営業部A」のワークシートで「表」を選択し、クエリを「更新」する。

  1. 「営業部A」の「表」にカーソルを置く(アクティブセルを移動)
  2. 「クエリ」タブを選択(※ 「表(テーブル)」にアクティブセルがないと表示されない)
  3. 「更新」をクリック

  f:id:k-ohmori9616:20210218101711p:plain

 今回の例のように、データの少ない「表」であれば手作業で表を分割する方が簡単かもしれません。

 しかし、取得と変換(PowerQueryエディタ)を使って分割された「表(テーブル)」は、「元の表(データ)」の内容が変わっても変更内容が反映されます。

 たとえば、会計ソフトから「仕訳データ」を書き出しCSVで)た「表」から自動的に「業者別の表」を作成したりすることができます。

 

「複数の表」「1つの表」"合体"させる

 さらに取得と変換を使えば、今回と逆の操作「部署別の表」から「全部署の一覧表」を作成することもできます。

 

複数の表(シート)を1つに "結合" する「取得と変換」(その1) - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

 

複数の表(シート)を1つに "結合" する「取得と変換」(その2) - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

 

 

k-ohmori9616.hatenablog.com