shikumika’s diary

【Power Query】VLOOKUPの近似一致と同様処理：区間分け（ビニング）の方法

PowerQueryで、ExcelのVLOOKUP関数の「検索の型をTRUE」（近似一致）にする方法の備忘録。Excelでは、「年齢」を「年代」に変換など、区間分け（ビニング）にVLOOKUP関数は便利です。 PowerQueryでも同様の処理を実現したい場合があり、その手順は次のとおりです。 内容： 事例の前提 アウトプットイメージ 実施手順 カスタム関数を使用する方法 「空のクエリ」の作成 カスタム関数の作成 カスタム関数の名称変更 カスタム関数の呼び出し カスタム関数の設定 カスタム列で実施する方法 カスタム列の追加 事例の前提 ExcelのVLOOKUP関数で実施する次の事例をPowe…

2023/09/30 12:30

【Excel】「年齢」を「年代」に変換など、区間分け（ビニング）の方法

データ分析時、数値データを適当な範囲（○以上△未満）で区切り、カテゴリーに変換して全体的な傾向を見たい時があります。例えば、「年齢」を「年代」に変換して年代ごとの度数分布表を作成するケースなどです。 このような場合は、ExcelのVLOOKUP関数、またはFLOOR関数を使用します。 VLOOKUP関数を使用する方法は、区間の範囲（○以上△未満）が等間隔でない場合にも可能な方法です。FLOOR関数は区間の範囲が等間隔の場合に可能な方法です。 アウトプットイメージ 実施方法 VLOOKUP関数を使用した方法 区間分けの基準となる表の作成 VLOOKUP関数の使用 FLOOR関数を使用した方法（区…

2023/09/29 12:30

【Power Query】Excelバージョンによる取込可能なデータソースの違い

ExcelのPowerQueryは、バージョンによって取込可能なデータソースが異なります。例えば、PDFをExcelにインポートする際にはPowerQueryが便利ですが、Office 2016やOffice 2019では利用できず、Microsoft 365のExcelで利用可能です。 JSONの取り込みは、Office 2019やMicrosoft 365のExcelで可能などの違いがあります。 ExcelのバージョンによるPowerQueryデータソースの違いについて以下に一覧があったので備忘録。 support.microsoft.com なお、PDFをExcelにインポートの便利な機…

2023/09/28 12:30

【Excel】複数回答の選択肢がセル内でカンマ区切りのデータ集計

Excelで、セル内にカンマで区切られたデータ（アンケートの複数回答など）を集計したいとき、Microsoft365のExcelなどにあるTEXTSPLIT関数が便利です。 TEXTSPLIT関数が使える場合、カンマなどの区切文字でセル内の文字列を分割できます。 具体的なアウトプットイメージは次のとおりです。 アウトプットイメージ 次のようにC1:C10に、カンマ区切のデータが入力されています。 TEXTSPLIT関数で文字列を分割しています。 TEXTSPLIT関数で文字列を分割イメージ 上記事例で数式は、次のとおり。 =TEXTSPLIT(分割対象のセル,",") 事例の補足 一つのセルに…

2023/09/26 12:30

【Excel】複数回答の選択肢が列ごとにあるアンケートデータの集計

Excelでアンケートデータを分析する際など、ピボットテーブル機能を用いて分析するために、複数の列にあるデータを行方向のデータに変換したい時があります。マトリクス表をリストに変換する操作、つまり列のピボット解除です。 前提として、最近のExcelに標準で含まれているPower Queryには、「列のピボット解除」という機能があります。繰り返しの業務や大量のデータなどはPower Queryがお勧めです。 shikumika.org ただし、一時的であったり、関数等でしたい場面もあります。 もし、Microsoft365のExcelなどでVSTACK関数、HSTACK関数が使える場合、数式だけで…

2023/09/24 12:30

【Power Query】列のピボット解除で、マトリクス表をリストに変換

Power Query（パワークエリ）には、マトリクス表をリストに変換できる「列のピボット解除」という機能があります。 「列のピボット解除」には、「その他の列のピボット解除」と「選択した列のみをピボット解除」を含めて3種類あり、それぞれの処理の違いについての備忘録です。 内容： アウトプットイメージ 実施方法 列のピボット解除 その他の列のピボット解除 選択した列のみピボット解除 それぞれの方法の違い アウトプットイメージ マトリクス表をリストに変換 実施方法 Power Query（パワークエリ）の基本操作が不明な場合は、【Power Queryの初歩】簡単な事例説明（使いながら覚える最初の…

2023/09/22 12:30

【Excel】外部参照で「値の更新」が出てファイル選択が求められた事例

Excelの外部参照は、他のExcelファイルのデータを参照するための便利な機能です。しかし、OneDrive上にあるファイルを参照する場合、予期せぬ挙動でエラーとなったので備忘録。 なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。 発生した事象 具体的には、次のような数式で外部参照（リンク）されたセルがあり、OneDriveに同期していた。リンク先の値も取得できていた。 例） ='C:\Users\〇〇\OneDrive\test\[サンプル.xlsx]日報'!$C$2 この状態で、リンクされたファイル（この事例では、サンプル.x…

2023/09/20 12:30

【Excel VBA】Setでワークブックを変数に割り当て（オートメーションエラーの発生事例）

ExcelのVBAを使って作業を自動化する際、ワークブックを変数に割り当てることはよくあります。このとき、変数が不要になったらSetステートメントでNothingを実施しておかないと、次のような"オートメーションエラー"が発生することがあります。 実行時エラー '-2147221080(800401a8)': オートメーション エラーです。 オートメーションエラーは、重いデータ処理や、メモリ不足等が原因にもなるようなので、エラーの一例として示します。 次のように、モジュールレベルの変数でワークブックを割り当て、最後にSetステートメントでNothingを実施していないケースでは注意が必要でした…

2023/09/19 12:30

DXに関する資料まとめ（リンク集）

DX（デジタルトランスフォーメーション）の取り組みについての情報収集で、公的資料等を中心にリンク先をまとめてみました。 内容: DXの定義 デジタルガバナンス・コード 中堅・中小企業等向け「デジタルガバナンス・コード」実践の手引きについて 「『デジタルガバナンス・コード』実践の手引き」（本体） AI導入ガイドブック DX推進指標について DX認定制度について その他、関連情報 DXの定義 経済産業省では「デジタルガバナンス・コード2.0（旧 DX推進ガイドライン）」で、次のように定義しています。 企業がビジネス環境の激しい変化に対応し、データとデジタル技術を活用して、顧客や社会のニーズを基に、…

2023/09/16 12:30

【Excel】複数シートの表を一つのシートに結合する方法（VSTACK関数の活用）

Microsoft365のExcelには、複数シートの表を一つのシートに結合する際に便利なVSTACK関数があります。 リスト形式のデータ（列ごとに同じ種類のデータが入力された表）を一つの表にまとめるような関数です。もちろん、複数の表が一つのシートにある場合の結合でも活用できます。 なお、リスト形式ではなく単票形式の表を一覧にしたい場合は、【Excel】複数シートのデータを抽出して一つのシートにまとめる方法（INDIRECT関数の活用） をご覧ください。 内容： 基本 具体的な事例 補足説明 数式で指定する表範囲について 他の方法について 基本 VSTACK関数は、次の構文となる。 =VSTA…

2023/09/15 12:30

【Excel VBA】セルのアクティブ化でエラーが発生する原因と対応

ExcelのVBAで、セルのアクティブ化や選択をしようとすると、セルが存在するにも関わらず「実行時エラー '1004': Range クラスの Select メソッドが失敗しました」「Range クラスのActivate メソッドが失敗しました」というエラーが発生する時があります。 しかも、元々動作していたが、コードの他の部分を修正した後に発生ということもあります。 エラーが発生する原因と対応 セルが存在しているにも関わらずエラーとなる場合、次の状態になってる可能性があります。 Activate メソッドの場合 「セルは現在の選択範囲内にある必要がある」が満たされていない。learn.micr…

2023/09/14 12:30

【Excel VBA】処理が止まらない時に強制停止する方法（Breakキーがない場合）

VBAの実行時、ループ処理が終わらないなど、処理が止まらず困った時に備えて、強制停止の方法を知っておくことは重要です。 基本の方法 基本の方法は、「Ctrl + BREAK」 キーを選択です。 もしくはEscキーの選択や、「Ctrl + Pause」キーなどで、VBAの実行を強制的に停止することができると言われています。 しかし、キーボードには「Break」キーがない場合などもあるので、その場合メーカー、キーボードに応じたショートカットキーを押す必要がある。 なお、私の場合（DELLのノートパソコン）では、「Fn + Ctrl + B」キー だった。 その他、後述のスクリーンキーボードを使用す…

2023/09/12 12:30

【Excel】数式と計算結果を並べて表示する方法

Excelの計算結果をチェックする目的で、数式と計算結果を並べて表示や印刷したい場合、数式を文字列として表示するFORMULATEXT関数が便利です。 Excelの数式メニューにある「数式の表示」や、ショートカットキー「Ctrl+Shift+`」で、数式表示の有無を切り替えは可能だが、シート内の全てが対象で、数式か計算結果の一方だけの表示となる。 一部のセルだけ、計算結果ではなく数式をそのまま表示したいケース、数式と計算を並べて同時に表示したいケースでFORMULATEXT関数が有効です。 support.microsoft.com 具体的なアウトプットイメージは次のとおりです。 FORMUL…

2023/09/10 12:30

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

Microsoft365のExcelで、特定の文字列の間の値を抽出する方法は、TEXTBEFORE/ TEXTAFTER 関数が便利です。この関数は、従来のLEFT関数やFIND関数などの組み合わせよりも、シンプルに文字列を抽出できます。 ただし、この関数が使えないバージョンのExcelでは「#NAME?」のエラーや、「_xlfn.」の数式で再計算されないといった問題が発生するので、関係者のバージョンが異なる場合には注意が必要です。 TEXTBEFORE/ TEXTAFTER 関数が使えるMicrosoft365のExcelの場合、 =TEXTAFTER(TEXTBEFORE(元の文字列,開始…

2023/09/08 12:30

【Excel】 セルを効率的に選択・削除する方法（数式のみ、数式以外、数値のみ）

Excelのシートから、 ・数式のみを選択したい。 ・数式以外のデータを削除したい。 ・数値のみを削除したい。 といった選択や削除をしたい時は、「検索と選択」のメニューにある機能を使用すると対象をまとめて選択できます。その後、必要ならそのままDeleteで削除できます。 Excelのバージョン Microsoft® Excel® for Microsoft 365 MSOでは次のとおりです。 まず、「検索と選択」のメニューで、次のように、数式はそのまま「数式」、数式以外は「定数」、数値は「条件を選択してジャンプ」をクリックします。 「検索と選択」のメニュー 数式や定数（数式以外）は以上で選択可…

2023/09/06 12:30

【Power Automate】処理速度は編集画面での実行が遅く、フロー一覧での実行が早い

Power Automate Desktopの実行は、編集画面とフロー一覧で処理速度が違う。ループ処理など処理回数が多い場合、編集画面から実行すると、実行遅延等の時間含めて処理時間が長くなる。編集画面での実行で処理速度が遅いと感じたら、フローの一覧表示から実行するだけで、かなり改善する。

2023/09/04 12:30

【Power Automate】2次元配列のDataTableでFor eachの実施例

Power Automateで、2次元配列のDataTableをFor eachループによる処理サンプル。 For each アクションを使用してデータテーブルをループする場合、反復処理の対象となる変数はデータ行となります。そのため、個々の値を抽出する場合は、データ行を再度For each アクションを使用してループするなどが必要。なお、使用のツールは「Power Automate Desktop」です。 内容： データ型の詳細 サンプルの前提 サンプル（DataTableでFor eachの実施） フローのテキスト情報 事例の補足説明 データ型の詳細 learn.microsoft.com …

2023/09/02 12:30