shikumika’s diary

【Excel】複数行ごとの表を一行に変換（リストのような単票の変換）

Excelの表で、1件のデータが複数行ごとに入力されている場合に、集計等の効率化のために1行のデータに変換する方法です。 例えば、下図はExcelの3行分に1件分のデータが入力されており、1行に変換するアウトプットイメージです。 複数行ごとの表を一行に変換するアウトプットイメージ このように、リストのように入力はされているが、同じ列に異なる値が入力された表を変換する事例です。 変換する方法はいくつかありますが、「Excelのみを使う場合」で個人的によく実施する方法です。 内容： 事例の前提 実施手順 1．セルを参照する数式で1行分のアウトプットを作成する 2．数式を対象となる件数分、下方向にコ…

2024/04/30 12:30

【Excel】複数のシートをまとめる方法（ツール含む）

Excelで、複数のシートを一覧表にまとめる方法に、VSTACKやINDIRECT関数、マクロ、PowerQueryなど、いくつかの選択肢があります。本ブログでもいくつか紹介しています。 よく似た方法がいくつかあり、全体像がわかりづらくなったので、選択する視点をまとめてみました。Microsoft365のExcelおよびWindowsの標準で使える方法（ツール含む）で整理しています。 内容： 方法（ツール含む）を選択する視点 リスト形式、単票形式の補足 リスト形式、Book内の複数Sheet 単票形式、フォルダ内の複数BookのSheet 参考 方法（ツール含む）を選択する視点 複数のシートを…

2024/04/28 12:30

【Excel】印刷範囲の設定は改ページプレビューが便利

Excelで印刷範囲の設定は、青い線がある「改ページプレビュー」が便利です。 レイアウト調整や印刷切れの確認がしやすいメリットがあります。 最終的に印刷することを想定した資料作成の作業環境は、原則、「改ページプレビュー」が良いと思います。 内容： 改ページプレビューの表示 改ページプレビューの設定例 青い点線をドラッグ 青い点線をドラッグした結果 余分な余白ページをなくす 改ページを解除する方法 改ページの設定で縮小されたときの対応 （参考）セルの文字が隠れる、印刷で文字が切れる場合の対応 改ページプレビューの表示 改ページプレビューについて、Microsoft サポートの説明ページは以下で、…

2024/04/25 12:30

【Power Automate】Webページの要素が表示されるまでフローを中断する方法

Power Automate Desktopには「Webページのコンテンツを待機」アクションがあります。 このアクションは、特定のテキストまたはWebページの要素がWebページに表示されるまで（または非表示になるまで）フローを中断します。 他に同様な機能として、Power Automate Desktopには、 アクションの詳細設定にある「ページが読み込まれるまで待機します」 というオプションがあります。 詳細は、以下のページ内で「2．Webフォーム入力などの場合はページが読み込まれるまで待機の有効化」をご覧ください。 shikumika.org このオプションの有効化で必要なフローを作成でき…

2024/04/23 12:30

【Excel】VLOOKUPとXLOOKUPで複数条件で検索する方法（スピルの活用）

ExcelのVLOOKUP関数とXLOOKUP関数で、複数条件による検索方法の事例です。スピルが利用できるExcel（Microsoft365など）では、シンプルな数式で複数条件による検索が可能です。 内容： 複数条件で検索する数式例 数式の説明 複数条件で検索する場合、検索条件にするセルを連結する 補足説明 スピルの説明 ”複数列”を検索範囲にする方法 （参考）XLOOKUP関数の特徴と活用事例 参考： 動的配列数式とスピル配列の動作 - Microsoft サポート 複数条件で検索する数式例 下図は検索対象の表（$B$3:$D$8）で「B列に合致、かつ、C列に合致」の複数条件で検索し、一致…

2024/04/22 12:30

【Excel】VLOOKUP関数とXLOOKUP関数で複数列を検索範囲にする方法

ExcelのVLOOKUP関数とXLOOKUP関数で複数列を検索範囲にして値を抽出する方法の事例です。1列の検索範囲で複数列の値を抽出したい場合は【Excel】VLOOKUPとXLOOKUPで複数列の値を抽出する方法の比較をご覧ください。 内容： 複数列を検索範囲にした数式例 VSTACKとHSTACKの計算結果例 その他、VSTACKとHSTACKを活用した事例 （参考）XLOOKUP関数の特徴と活用事例 HSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web で利用可能です。 詳…

2024/04/19 12:30

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

ExcelのXLOOKUP関数はVLOOKUP関数の改良版で、検索列と結果列の選択がしやすいなど、とても便利な関数です。 活用の考え方は、基本はXLOOKUP関数を使用し、VLOOKUP関数は何かしらの目的があるときだけ利用が良いと思います。なお、VLOOKUP関数が必要な場面としては、XLOOKUP関数が使用できない人とのファイル共有や、テーブル内での利用などです。 VLOOKUP関数と比較した特徴と、活用事例をまとめました。 なお、XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 内容： XLOOKU…

2024/04/17 12:30

【Excel】VLOOKUPとXLOOKUPで離れた位置の複数列を抽出する方法

ExcelのXLOOKUP関数は、抽出したい列数が多い場合（連続している場合）にVLOOKUP関数より便利です。抽出したい複数列が連続していない場合は、目的やデータの状況によりますがHSTACK関数と組み合わせる方法も有効です。 内容： VLOOKUPとXLOOKUPで複数列を抽出する方法の基本 離れた位置の複数列を抽出する方法 （参考）VLOOKUP関数の範囲にもHSTACK関数を使用可能 HSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web で利用可能です。 詳細：HSTACK…

2024/04/15 12:30

【Excel】VLOOKUPとXLOOKUPで複数列の値を抽出する方法の比較

ExcelのVLOOKUP関数と、その改良版であるXLOOKUP関数で複数列の値を抽出する方法の比較です。 抽出したい列数が多い場合（連続している場合）はXLOOKUP関数が便利です。なお、抽出したい複数列が連続していない場合は、目的やデータの状況によります。 内容： 連続した複数列の値を抽出する方法 VLOOKUP関数と配列数式の組み合わせ（非推奨） 連続しない複数列の値を抽出する方法 参考情報 複数列の抽出スピードは、XLOOKUP関数が遅い時あり XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 連…

2024/04/12 12:30

【Excel】VLOOKUP関数で複数シートのセル範囲から値を抽出する方法

ExcelのVLOOKUP関数やXLOOKUP関数で、単一の表（セル範囲）ではなく、複数シートのセル範囲から値を抽出したい場合はVSTACK関数が便利です。 VSTACK関数は複数のシートにある表を一つの表に結合できる関数です。 内容： アウトプットイメージ VSTACK関数の基本事例 数式の補足説明 （参考）Power Queryの活用 なお、VSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the webで利用可能です。 詳細：VSTACK 関数 - Microsoft サポート アウトプ…

2024/04/11 12:30

【Excel】VLOOKUP関数では不可能な「右側から値を取得する方法」

ExcelのVLOOKUP関数で、右側から左側の値を取得することは不可能ですが、VLOOKUP関数の改良版であるXLOOKUP関数なら可能です。 XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 内容： 右側から左側の値を取得する方法 XLOOKUP関数とVLOOKUP関数の補足 右側から左側の値を取得する方法 VLOOKUP関数は、表の左端列を検索して右側の値は取得可能ですが、右側から左側の値は取得できません。 そのため、従来であればINDEX関数とMATCH関数を組み合わせる方法などで対応が必要でした…

2024/04/10 12:30

【Excel】テーブルの作成と解除方法（VLOOKUPでの活用例）

Excelには、データの整理や抽出、集計業務を効率化させる「テーブル」機能があります。 便利な機能ですが不慣れな人も多い印象です。 以下では、Excelでテーブルを作成する方法と解除する方法、そしてVLOOKUP関数との組み合わせでの活用例を紹介します。 内容： Excelのテーブルとは テーブルの作成方法 数式でテーブルの活用方法 （1）数式での指定例 （2）VLOOKUP関数での活用例 テーブルにデータ追加・削除 テーブルの解除方法 （1）テーブル解除の基本操作 （2）テーブルの解除による数式への影響 （3）縞模様などの「テーブルスタイル」は「セルのスタイル」で標準に戻す Excelのテー…

2024/04/09 12:30

【Excel】データ追加時に「数式のセル範囲修正」を省力化する方法

ExcelのSUM関数やVLOOKUP関数などで「セル範囲を参照する数式」を作成した後、データ追加によりセル範囲を修正する作業は非効率です。 修正が漏れると、集計対象に含まれないなどのミスも発生します。 数式作成後のデータ追加に備えて「数式のセル範囲修正」を省力化するため、 セル範囲を「列」で指定 セル範囲を「テーブル」で指定 を活用できないか検討します。 内容： セル範囲を「列」で指定 VLOOKUP関数での事例 セル範囲を「列」で指定する場合の注意点 数式の仕様の一例 SUM関数は、範囲 (セルのグループ) の”数値”を合計する関数 AVERAGE 関数は、文字列や空白は無視されるが、値 …

2024/04/08 12:30

【Excel】VLOOKUP関数で#REF!エラーの原因と対応

ExcelのVLOOKUP関数で#REF!エラーが表示されることがあります。 この原因は、 VLOOKUP 関数の構文=VLOOKUP(検索値, 範囲, 列番号, [検索の型]) で、数式の作成時に「範囲」を超えて列番号を指定したことによるエラーが多いです。 なお、数式の作成後、検査値に指定していたセルを削除した場合も、次のように数式の検査値箇所が#REF!となり、エラーになります。 例） =VLOOKUP(#REF!,$B$3:$C$8,2,FALSE) と数式内に#REF!がある ただし、このケースは数式作成後に削除しており、原因が比較的わかりやすいと思います。 今回は数式の作成時に発生し…

2024/04/03 12:30

【Excel】VLOOKUP関数で検索対象の表に重複データがある場合の対応

ExcelのVLOOKUP関数で検索対象の表に重複データがある場合、先に一致した値の行を取得します。そのため、意図しない計算結果になることがあります。 内容： 検索対象の表に重複データがある場合の事例 VLOOKUP関数は先に一致のみだが、XLOOKUP関数は最後に一致した値も取得可能 検索対象の表に重複データがある場合の事例 具体的な事例は下図のとおりです。 セル範囲$B$3:$C$8（下図でマスタ表に該当）の左端列（B列）は重複した値があります。取得対象の値（C列）の値は全て違います。 このような場合、VLOOKUP関数の計算結果は先に一致した値の行を取得した結果となります（下図のセルF4…

2024/04/01 12:30