shikumika’s diary

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

Excel

【Excel】業務フローを効率的に作成する操作テクニックのまとめ

Excelで業務フローを効率的に作成するための操作テクニックのまとめ。 内容: 図形の配置を整列 図形の既定を設定 図形のコピー コネクタのカギ線のズレを修正 フロー図の線の重なりを簡易的に見やすくする方法 作成後、多くの図形の書式を変更 Excelの標準…

【Excel】同じ図形を続けて作成するときのポイント

Excelで、同じ図形(オートシェイプなど)を連続して作成する際に、知っておきたい機能の備忘録。個人的には、CTRLキー、SHIFTキー、ALTキーの活用が特に有用だと感じています。 内容: 描画モードのロック Ctrlキー、Shiftキー、Altキーの活用 Ctrl(図形の…

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

Excelで図形や線の書式を統一したいとき、作成の段階から同じ書式(色や太さなど)にしておきたいことがあります。簡単に、図形や線の書式設定の既定を変更できるので、同じ書式の図や線を作成するときは設定しておくと便利です。 基本の設定 図形や線につい…

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

Excelで図形の書式を繰り返し貼り付けする方法は、Ctrl+Shift+Vも活用すると便利です。ある図形に適用した書式を別の図形に適用したいとき、「書式のコピー/貼り付け」を利用するケースは多いと思います。 そして、繰り返し書式を貼り付ける場合、次のよう…

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

Excelの「コネクタ:カギ線」は、業務フロー図の作成などに便利です。しかし、直線となるように配置した図形であっても、まっすぐにならず、微妙なズレがある線となります(下図の左側)。 「コネクタ:カギ線」のズレと解消イメージ コネクタの微妙なズレの…

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

Excelで値のみ貼り付け(書式なし貼り付け)のショートカットキーはなく、Ctrl+Shift+Vがこれまでは利用できませんでした。 そのため、テキストの内容だけをプレーンテキストとして貼り付けることが手間でしたが、Microsoft365のExcelでバージョン 2308 (ビ…

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

Excelで業務フロー図の作成時など、線が交差する箇所を見やすくしたい時があります。しかし、Excelには「飛び越し点」(飛び越し線)の表示機能がありません。 線と半円をつなげて「飛び越し点」を作成する方法もありますが、フロー図の修正や線の重なりが多…

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

ExcelのXLOOKUP関数のバイナリ検索と、VLOOKUP関数の近似一致による検索で、IF文を用いて「完全一致のみを抽出」をした場合の速度を比較した事例です。 「XLOOKUP関数やVLOOKUP関数で検索の型を完全一致」で実施した場合の比較は、【Excel】VLOOKUP関数とXLO…

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

最近のVLOOKUP関数は、かなり高速に改善されている印象です。 過去には「データを昇順にして近似一致で検索する方法」と「通常の完全一致での検索」では実感の速度に大きな違いがあったように思いますが、実感するほどの差が少なくなったと感じます。 一方、…

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

データ分析時、数値データを適当な範囲(○以上△未満)で区切り、カテゴリーに変換して全体的な傾向を見たい時があります。例えば、「年齢」を「年代」に変換して年代ごとの度数分布表を作成するケースなどです。 このような場合は、ExcelのVLOOKUP関数、また…

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

Excelで、セル内にカンマで区切られたデータ(アンケートの複数回答など)を集計したいとき、Microsoft365のExcelなどにあるTEXTSPLIT関数が便利です。 TEXTSPLIT関数が使える場合、カンマなどの区切文字でセル内の文字列を分割できます。 具体的なアウトプ…

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

Excelでアンケートデータを分析する際など、ピボットテーブル機能を用いて分析するために、複数の列にあるデータを行方向のデータに変換したい時があります。マトリクス表をリストに変換する操作、つまり列のピボット解除です。 前提として、最近のExcelに標…

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

Excelの外部参照は、他のExcelファイルのデータを参照するための便利な機能です。しかし、OneDrive上にあるファイルを参照する場合、予期せぬ挙動でエラーとなったので備忘録。 なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。 発生し…

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

Microsoft365のExcelには、複数シートの表を一つのシートに結合する際に便利なVSTACK関数があります。 リスト形式のデータ(列ごとに同じ種類のデータが入力された表)を一つの表にまとめるような関数です。もちろん、複数の表が一つのシートにある場合の結…

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

Excelの計算結果をチェックする目的で、数式と計算結果を並べて表示や印刷したい場合、数式を文字列として表示するFORMULATEXT関数が便利です。 Excelの数式メニューにある「数式の表示」や、ショートカットキー「Ctrl+Shift+`」で、数式表示の有無を切り替…

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

Microsoft365のExcelで、特定の文字列の間の値を抽出する方法は、TEXTBEFORE/ TEXTAFTER 関数が便利です。この関数は、従来のLEFT関数やFIND関数などの組み合わせよりも、シンプルに文字列を抽出できます。 ただし、この関数が使えないバージョンのExcelでは…

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

Excelのシートから、 ・数式のみを選択したい。 ・数式以外のデータを削除したい。 ・数値のみを削除したい。 といった選択や削除をしたい時は、「検索と選択」のメニューにある機能を使用すると対象をまとめて選択できます。その後、必要ならそのままDelete…

【Excel】セルの参照元ファイルを効率的に開く、ジャンプする方法

Excelのシートで、他のブックの値を参照しているセルで、元データのファイルを効率的に開く方法の備忘録。例えば、セルに ='C:\aaa\[test.xlsx]Sheet1'!$A$1 という数式が入力されていて、参照元の値が表示されているとします。そのとき、元データのファイル…

【Excel】複数ブックのデータ(単票)を一覧表にまとめる方法の比較

Excelで複数のブックのデータ(単票)を一覧表にまとめる方法にはいくつかの選択肢があります。Excel関数、VBA、Power Queryなどを活用したこれまでの備忘録を中心にデータの状況と対応例をまとめてみました。 個人的な結論は、VBAを使った方法が汎用性も高…

【Excel】シートの保護で特定セルの編集は許可、メモの編集は不可にする

Excelで入力用の様式を作成し、記入依頼、回収する場合は、レイアウト等を変更されないようにシートを保護することが有効です。保護する内容もいくつか設定可能です。例えば、特定セルだけ編集できるようにし、「メモ」は編集・削除できないようにできます。…

【Excel】複数シートのデータを抽出して一つのシートにまとめる方法(INDIRECT関数の活用)

Excelで、同じフォーマットで入力されたデータが複数のシートにあるデータの集計作業では、INDIRECT関数の活用が有効な手段の一つです。 INDIRECT関数とは、セルに記述された「テキスト情報」が参照するセルの値を返す便利な関数です。 具体的な手順は以下の…

【Excel】XLOOKUP関数は、テーブル内で使用できない

XLOOKUP関数は、VLOOKUP関数と比較して便利な関数です。しかし、テーブル内で使用すると「#スピル!」となってエラー。XLOOKUP関数など、複数のセルに結果が戻る数式は使用できないようなので備忘録。 使用のExcelは「Microsoft® Excel® for Microsoft 365 MS…

【Excel VBA】置換リストで都度確認しながらWord文書の文字列を置換

前回の 【Excel VBA】Excelの置換リストでWord文書の文字列を一括置換 をベースに、置換対象を都度確認しながら実行するVBAのサンプル。置換リストで一致したキーワードについて、都度、置換するかのメッセージを表示し、確認しながら置換を実行します。 「…

【Excel VBA】Excelの置換リストでWord文書の文字列を一括置換

ExcelのVBAで、置換リストからWord文書の文字列を一括置換する方法のサンプル。前回のWordのVBAを使用した「Word文書の文字列をExcelの置換リストで一括置換する方法」と同様のことをExcelのVBAで実施するサンプルを作成してみました。置換リストの内容に応…

【Excel】OneDriveのファイルURLをローカルパスに変換(数式で文字列の置換)

Excelの数式で、CELL(“filename”,A1)やVBAのThisWorkbook.Pathを使用したとき、OneDriveに同期したフォルダではローカルパスが取得できず、VBA等が適切に動作しないことがあります。Excelの数式を利用して、OneDriveのファイルURLをローカルパスに変換してみ…

【Excel】LAMBDA関数とTEXTBEFORE関数の使い方(フルパス等の取得事例)

Microsoft365のExcelでは、新しい関数の中に、・VBAを使用せず、カスタム関数を作成できるLAMBDA 関数 ・指定した文字の前を抽出できるTEXTBEFORE 関数・指定した文字の後を抽出できるTEXTAFTER 関数があります。 これら新しい関数の使い方の備忘録として、E…

【Excel】行列の幅や高さをcm単位で指定は「ページレイアウトビュー」で可能

Excelで、1㎝の方眼紙や、㎝や㎜で幅や高さを指定した四角い枠を印刷したい時は、「ページ レイアウト ビュー」を使用すると可能です。 標準のビューや、改ページプレビューを使用している時は、ポイントでしか幅や高さを指定できませんが、「ページ レイア…

【Excel】VBAで、印刷時に文字が切れないように行高さを設定するサンプル

Excelを使用してセルに長文を入力し、複数行表示にしたデータを印刷すると、文章の下端が切れてしまうことがあります。 その原因と解決方法の基本は次のとおりで、標準フォントで調整が必要。 shikumika.org しかし、複数のフォントを使用していたり標準フォ…

【Excel】VBAで列幅と行高さを設定する際は単位の違いに注意

ExcelのVBAを使用して、列幅や行高さを設定する際は単位の違いに注意が必要。知らないと混乱の元になるので備忘録。 具体的には、行の高さを設定するRowHeightと、列の幅を設定するColumnWidthを使って、 Rows(1).RowHeight = 30 Columns(1).ColumnWidth = 3…

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

Excelでシリアル値のまま西暦表示から「元年」も含む和暦表示に変更できることを知らなかったので備忘録。 具体的には、「2019/10/1」を「令和元年10月1日」に書式設定で表示可能。 もちろん、”元年”ではなく”1年”と表示することもできる。 設定方法は次のと…