shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

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

データの加工・抽出、管理の操作はビジネスで重要なスキルですが、様々なツールやテクニックがあります。広く利用されているExcel(Power Query含む)などの便利なツールを使って、データ操作を効率的に行う事例をこちらにまとめていきます。

なお、★マークのついた操作は、個人的に便利と感じている操作です。

ファイルの結合・抽出

複数シートの表(リスト)を一つのシートに結合する方法

【Power Query】による方法(フォルダ内の複数Book)

【Power Query】による方法(Book内の複数Sheet)

【Excel】のVSTACK関数による方法(同一ブック内のみ)

参考)【Excel】複数のシートをまとめる方法(ツール含む)

複数シートの値(個票)を一つのシートに結合する方法

【Power Query】の列のピボット解除による方法

その他(Excel、PowerQuery、VBA、PowerAutomate)の比較

指定フォルダ内のExcelファイルを順番に処理

【Excel VBA】での事例(FileSystemObjectの利用)

【Excel VBA】での事例(Dir関数の利用)

【Power Automate】での事例

Excelの表から検索値に一致する値を取得

【Excel】XLOOKUP関数の特徴と活用事例

【Excel】VLOOKUP関数の基本的な使い方とミス防止のポイント

その他データの加工

データを繰り返し取得

【Excel】フォーマットは同じで「ファイル名」が異なるデータのインポート効率化

PDFをExcelにインポート、繰り返し業務ならPower Query(パワークエリ)が便利

表記ゆれの修正など

Excelでシリアル値のまま西暦から「元年」も含む和暦表示に変更

Excelで表記ゆれがある日付文字を日付データ(シリアル値)に変換結果

【Power Query】全角を半角に一括置換(Text.Replaceを使う時の注意点)

区間分け(ビニング)

【Excel】による方法(「年齢」を「年代」に変換)

【Power Query】による方法(VLOOKUPの近似一致と同様処理)

個別データの処理テクニック

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

複数のデータを繰り返し貼り付けなら「Windows」+「V」が便利

【Excel】特定の文字列の間の値を抽出する方法(TEXTBEFORE/ TEXTAFTER 関数)

【Power Query】Excelで文字列から数字だけ抽出する方法

Excelで特定の文字より後ろにある文字列を抽出するユーザー定義関数サンプル

【Excel】2つのセルの値が同じかを簡単に調べる方法

データの管理

【Excel】ピボットテーブルで元データ範囲の設定変更を省力化する方法

【Power Query】データソースの管理方法(ファイルの状況と対応例) 

【Excel VBA】テーブルに別ファイルのデータをインポート

【Excel】VBAで「 テーブルをセル範囲に変換してエクスポート」

【Excel】VBAで、PowerQueryの更新を待ってピボットテーブルを更新

ExcelやWordの作成者や最終更新者の名前を削除する方法

参考:

データの集計・可視化

データの集計・可視化の操作事例は、以下にまとめています。

shikumika.org

PowerQueryやVBAの記事

PowerQueryや、VBAに関する記事は次のカテゴリーでまとめています。

PowerQuery カテゴリーの記事一覧 - shikumika’s diary

VBA カテゴリーの記事一覧 - shikumika’s diary