shikumika’s diary

データの集計・可視化の操作事例まとめ（主にExcel）

広く利用されているExcel（Power Query含む）などのツールを使って、「データの集計・可視化」を効率的に行う事例をこちらにまとめていきます。 なお、★マークのついた操作は、個人的に便利と感じている操作です。 内容： データの集計 PowerQueryでのカウント集計 ピボットテーブルで比較結果を見やすいレイアウトの集計 アンケートデータの集計 データの可視化 年度別の順位推移表のようなマトリクス表の作成 Excelピボットテーブルでのテクニック 参考： データの加工・抽出、管理 PowerQueryやVBAの記事 データの集計 PowerQueryでのカウント集計 ・ExcelのCO…

2023/10/30 12:30

【Power Query】エディターの [クエリ] ウィンドウでカテゴリに整理

PowerQueryエディターの [クエリ] ウィンドウでは、グループ機能を使用してクエリをカテゴリ別に整理することが可能です。 これは、クエリをフォルダのような構成で管理できますが、動作内容や影響が不明だったので調べた備忘録です。 内容： グループを使用してクエリをカテゴリに整理 クエリをカテゴリに整理の動作内容や影響 概要 調べた内容の詳細 グループを他のグループに移動するループへの移動 グループを解除 グループを作成 「その他のクエリ」の補足 グループを使用してクエリをカテゴリに整理 PowerQueryのメニューからファイルを結合するクエリを作成すると、次のようなフォルダが存在するヘル…

2023/10/28 12:30

【Power Query】ヘルパークエリがある場合のデータソース変更方法

PowerQueryの利用場面で、データソースを変更したいことは比較的多く、【Power Query】データソースの変更方法が基本的な操作です。 他にもデータソースを変更する方法はありますが、ファイルを結合するクエリでヘルパークエリが存在する場合、データソースの変更には次の注意点があります。 ヘルパークエリが存在する場合は「サンプル ファイル」にもソースの情報が含まれる。 修正は、「データ ソースの設定」から基本的に実施する。（もしくは「データ ソース設定」） 上記2の理由は、次の2つ方法でデータソースの修正範囲が異なるためです。理解すると当たり前の挙動ですが、慣れていないと注意が必要です。 …

2023/10/26 12:30

【Excel】図形や線の書式設定の既定を変更する方法

Excelで図形や線の書式を統一したいとき、作成の段階から同じ書式（色や太さなど）にしておきたいことがあります。簡単に、図形や線の書式設定の既定を変更できるので、同じ書式の図や線を作成するときは設定しておくと便利です。 基本の設定 図形や線について「既定にしたい書式」の設定後、右クリックで表示されるメニューで既定の書式を設定します。 なお、メニューの表示は次のとおりです。 図形の場合：「既定の図形に設定」ただし、テキストボックスの場合は、「既定のテキストボックスに設定」 線の場合：「既定の線に設定」 図形（テキストボックス）の設定例 補足説明と注意点など 既定の書式は、対象のExcelファイル…

2023/10/24 12:30

データの加工・抽出、管理の操作事例まとめ（主にExcel）

データの加工・抽出、管理の操作はビジネスで重要なスキルですが、様々なツールやテクニックがあります。広く利用されているExcel（Power Query含む）などの便利なツールを使って、データ操作を効率的に行う事例をこちらにまとめていきます。 なお、★マークのついた操作は、個人的に便利と感じている操作です。 ファイルの結合・抽出 複数シートの表（リスト）を一つのシートに結合する方法 複数シートの値（個票）を一つのシートに結合する方法 指定フォルダ内のExcelファイルを順番に処理 その他データの加工 データを繰り返し取得 表記ゆれの修正など 個別データの処理テクニック データの管理 参考： ファ…

2023/10/23 12:30

【Excel】図形の書式を繰り返し貼り付ける方法（Ctrl+Shift+Vの活用）

Excelで図形の書式を繰り返し貼り付けする方法は、Ctrl+Shift+Vも活用すると便利です。ある図形に適用した書式を別の図形に適用したいとき、「書式のコピー／貼り付け」を利用するケースは多いと思います。 そして、繰り返し書式を貼り付ける場合、次のような方法があります。使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2309）です。 マウス操作でする方法「書式のコピー／貼り付け」をダブルクリックして、「貼り付けの繰り返し状態」して、繰り返し図形を選択していく。 ショートカットキーを利用する方法Ctrl+Shift+C（書式のコ…

2023/10/22 12:30

【Excel】「コネクタ：カギ線」のズレをまっすぐにする方法

Excelの「コネクタ：カギ線」は、業務フロー図の作成などに便利です。しかし、直線となるように配置した図形であっても、まっすぐにならず、微妙なズレがある線となります（下図の左側）。 「コネクタ：カギ線」のズレと解消イメージ コネクタの微妙なズレの解消方法 直接コネクタに変更せず、「コネクタ：カギ線」のままで、ズレを解消してまっすぐに修正するには、「図形の書式設定」のサイズで高さを”0”に設定します（上図の右側）。 なお、「図形の書式設定」の設定イメージは次のとおりです。 「図形の書式設定」の設定イメージ 注意点 上記の手順で、「コネクタ：カギ線」のズレをまっすぐにできますが、図のレイアウト等が…

2023/10/20 12:30

【Excel VBA】選択しているオートシェイプの図形と同じタイプを一括で選択（複数タイプ可）

前回、VBAで選択しているオブジェクトと同じタイプを一括で選択する方法を記載しました。この方法は、 一括選択したいオブジェクトのタイプが一つのみで、複数タイプに対応していない オートシェイプの図形の詳細な分類に対応していない という仕様だったので、上記課題に対応したVBAの備忘録。 事例の前提 VBAで選択しているオブジェクトと同じタイプを一括で選択する方法ですが、修正前の詳細な前提、背景は以下のとおりです。 shikumika.org VBAサンプル Sub 複数選択して同じタイプの図形選択() 'AutoShapeTypeで判定' Dim 図形 As Shape Dim 選択した全ての図形…

2023/10/18 12:30

Excelで値の貼り付け（書式なし）がCtrl+Shift+Vで可能になった

Excelで値のみ貼り付け（書式なし貼り付け）のショートカットキーはなく、Ctrl+Shift+Vがこれまでは利用できませんでした。 そのため、テキストの内容だけをプレーンテキストとして貼り付けることが手間でしたが、Microsoft365のExcelでバージョン 2308 (ビルド 16731.20170)以降で利用可能になっていました。 2023年8月28日に公開されたバージョンです。 learn.microsoft.com 値のみ貼り付けは、Excelでテキストを扱う際によく利用します。 上記バージョン以降は、［Ctrl］＋［Shift］＋［V］キーで値のみ貼り付けが可能です。 なお、上…

2023/10/17 12:30

【Excel VBA】選択しているオブジェクトと同じタイプを一括で選択

Excelで図形を操作するときに、同じタイプのオブジェクトを一括で選択したい場合があります。例えば、シート内にあるすべての「コネクタ: カギ線」だけを選択したり、「テキスト ボックス」だけを選択して色を変更したいときなどです。 しかし、Excelにはそのような機能はありません。 シート内の全てのオブジェクトを選択することは可能ですが、オブジェクトのタイプを絞り込んで一括で選択することはできません。 そのため、一つずつ選択を繰り返すしか方法がなく、オブジェクトが多い場合は手間です。 そこで、VBAを使って、現在選択しているオブジェクトと同じタイプのオブジェクトを全て選択するコードの備忘録です。 …

2023/10/16 12:30

【Excel】フロー図の線の重なりを簡易的に見やすくする方法

Excelで業務フロー図の作成時など、線が交差する箇所を見やすくしたい時があります。しかし、Excelには「飛び越し点」（飛び越し線）の表示機能がありません。 線と半円をつなげて「飛び越し点」を作成する方法もありますが、フロー図の修正や線の重なりが多い場合は少し手間です。 そこで、線が交差する箇所を簡易的に見やすくする方法として、線の背景（輪郭）を白色にする方法の備忘録。 なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2309）です。 飛び越し点について 飛び越し点は、下図の右側のように線の交差箇所を一方の線がもう一方の線…

2023/10/14 12:30

【Excel】XLOOKUPのバイナリ検索とVLOOKUPの近似一致の速度比較

ExcelのXLOOKUP関数のバイナリ検索と、VLOOKUP関数の近似一致による検索で、IF文を用いて「完全一致のみを抽出」をした場合の速度を比較した事例です。 「XLOOKUP関数やVLOOKUP関数で検索の型を完全一致」で実施した場合の比較は、【Excel】VLOOKUP関数とXLOOKUP関数の速度比較の事例 のとおり、複数列以上の抽出時に処理時間に差があります。 以下は、バイナリ検索や近似一致を用いて「1列のみ抽出」をした時の処理時間です。 一つの事例ですが、結果は次のとおりでした。 XLOOKUP関数のバイナリ検索と、VLOOKUP関数の近似一致による検索で、処理時間は同程度だった…

2023/10/12 12:30

【Power Query】現在のExcelファイルのシートを直接取得する関数はない

Power Queryで、現在のExcelファイルのシートの内容を取得したい時があります。 しかし、テーブル等を介さず、直接取得する関数はありませんでした。 なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2309）です。 Excel ブックの内容を返す関数には Excel.CurrentWorkbook（現在の Excel ブックの内容を返す。シート内容の取得不可） Excel.Workbook（指定したExcel ブックの内容を返す。シート内容の取得可能） があります。 まず、現在の Excel ブックの内容を返すExc…

2023/10/11 12:30

【Power Query】ブック内にある複数シートの表を一つのシートに結合する方法

Power Queryで、ブック内にある複数シートの表を一つのシートに結合する方法の備忘録。Power Queryで作成する「クエリ」と「結合したいシート」も同じブック内にしたい場合と、クエリ自体は別ブックでもよい場合で若干手順が異なる。 内容： アウトプットイメージ 実施手順 「クエリ」と「結合したいシート」も同じブック内にしたい場合 結合したいデータ範囲のクエリを作成する。 Power Queryエディターで「クエリの追加」をクリック 結合したいテーブル（クエリ名）を「追加するテーブル」に設定 クエリ自体は別ブックでもよい場合 「データ」タブから結合したいシートがあるブックを開く シートで…

2023/10/10 12:30

【Power Query】List.FirstNやTable.FirstNのcountOrConditionの理解

Power QueryのList.FirstNとTable.FirstN、LastNなどの関数は、リストやテーブルから最初（LastNの場合は末尾）のN個の要素を取得するために使用します。 これらの関数の第二引数countOrConditionは、数値または条件を指定できます。 数値を指定した場合は、単純に最初（LastNの場合は末尾）のN個の要素が返されます。条件を指定した場合、「検索開始時から検索条件が満たされる要素が連続している間」が対象となります。検索条件を満たさない要素が見つかったら、それ以降の要素は無視されます。 例えば、List.FirstNの説明ページは以下です。 learn.…

2023/10/06 12:30

【Excel】VLOOKUP関数とXLOOKUP関数の速度比較の事例

最近のVLOOKUP関数は、かなり高速に改善されている印象です。過去、データを昇順にして二分探索する方法と通常の完全一致での検索では実感の速度に大きな違いがあったように思いますが、実感するほどの差が少なくなったと感じます。 一方、XLOOKUP関数は検索列と結果列の選択がしやすいなど、VLOOKUP関数と比較して便利な関数ですが、大量データの場合、VLOOKUP関数より速度が少し遅い印象です。 今回、5万件のデータ処理で時間が遅く感じたので、VLOOKUP関数とXLOOKUP関数の処理時間を調べてみました。 データ処理の内容やPC環境等もあるので、一つの事例の結果としてですが、XLOOKUP関…

2023/10/04 12:30

【Excel】「データ」タブの重複削除とPowerQueryで重複削除の違い

Excelで重複しているデータを削除するとき、「データ」タブの重複削除とPower Queryでの重複削除では、残すデータの仕様に違いがあります。 具体的には、Excelの「データ」タブの重複削除は、リスト内で最初に出現する値が保持されるとなっています。 support.microsoft.com 一方、Power Queryの「重複削除」では、一連の重複の最初のインスタンスが選択されるという保証はないとなっており、リスト内で最初に出現する値が保持されるとは限らないようです。 learn.microsoft.com この回避策として、 (Table.Buffer を使用して) データをバッファ…

2023/10/02 12:30