shikumika’s diary

【Word VBA】Excelシートの最終行取得（xlUpで実行時エラー 424の対応）

WordのVBAで、Excelのシートにある情報を使った繰り返し処理等をするために、最終行取得をしようとすると「実行時エラー424」となったので解決方法の備忘録。なお、使用のバージョンは「Microsoft® Word for Microsoft 365 MSO」。

2023/06/30 06:30

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

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

2023/06/28 18:30

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

Microsoft365のExcelでは、新しい関数の中に、・VBAを使用せず、カスタム関数を作成できるLAMBDA 関数 ・指定した文字の前を抽出できるTEXTBEFORE 関数・指定した文字の後を抽出できるTEXTAFTER 関数があります。 これら新しい関数の使い方の備忘録として、Excelファイルの保存場所（フォルダ・フルパス等）を取得する関数を作成したメモ。 結論としては、次の印象。 LAMBDA関数は、複雑な数式では有用。 TEXTBEFORE/ TEXTAFTER 関数は、従来のLEFT関数やFIND関数などの組み合わせより、シンプルに文字列を抽出可能。 使用例 この事例の前提知…

2023/06/26 18:00

【PowerPoint】アニメーションを印刷しない方法（疑似的な解決策）

PowerPointの配付資料で、映写用で作成したアニメーションは非表示にしたい場面での擬似的な解決策の備忘録（特定のオブジェクトを印刷したくないも同様）。

2023/06/24 13:00

【PowerPoint】配付資料で穴埋め問題の作成方法は答えを図形で隠す

PowerPointの配付資料で、対象者の関心を引くために答えを伏せるなど、穴埋め問題にしたい時の備忘録。資料作成時の基本は、次の順序です。 1．答えを先に書いておく 2．図形（白い図形など含む）で答えを隠す 3．アニメーションの「終了効果」で、図形を消す

2023/06/23 18:00

【Excel】ファイルの保存場所変更に備えた準備（ファイルパスの自動更新）

リンクの設定等があるExcelファイルの場合、保存場所の変更に注意が必要です。特に、VBAやPower Queryなどで固定のファイルパスに依存した構築を行うと、Excelに不慣れな人がファイルの保存場所変更に対応できない等の問題が生じる可能性があります。 ファイルの保存場所やファイル名が変わる可能性が高い場合は、事前に保存場所変更に備えた構築が必要です。 そのため、Excelファイル自体の移動があっても、データソース等との相対的な位置関係で動作できるようにしておくことも有効です。 相対的な位置関係の基準になる場所は、Excelファイル自体の保存場所にすると便利なケースが多いので、Excelの…

2023/06/21 18:00

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

Excelのテーブル機能やPower Queryを含むファイルを他者に渡すことが妥当でないケースが時折あります。そこで、VBAで「 テーブルをセル範囲に変換してエクスポート」のサンプルを作成してみた備忘録です。

2023/06/18 12:00

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

Excelの標準機能にあるPowerQuery（パワークエリ）を活用して、フォーマットは同じで、「ファイル名」が異なるデータのインポート業務を効率化する方法を調べた備忘録。 背景は、同一フォーマットで、ファイル名が取得時期や対象によって異なる次のようなファイルで、繰り返し発生する集計業務の効率化。 例） 売上データ_2023年4月.xlsx 売上データ_2023年5月.xlsx 売上データ_2023年6月.xlsx ・・・・ このようなファイル名が違うデータについて、毎月集計のためにコピーと貼り付けを繰り返しているようなケース。ファイル名を固定にする方法もあるが、データを識別するために固有なフ…

2023/06/14 06:00

【Excel】パワークエリで、複数のファイルを 1 つに結合する方法

Excelの標準機能にあるPowerQuery（パワークエリ）は、同一フォーマットで入力された複数ファイルを一つにまとめたい時に便利です。 毎月の売上データや、部門別の実績データといった複数のファイルを一つに結合する場面などに活用できます。これまでであれば、手作業で一つずつコピーと貼り付けを繰り返すか、VBAを利用して自動化するといった手間な業務がExcelの標準機能で効率化できます。 Microsoftの説明は以下ですが、アウトプットイメージと手順の補足です。使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。 support.micros…

2023/06/13 06:00

【Power Query】データソースの変更方法

PoweqQuery（パワークエリ）は、毎月更新されるようなファイル（データソース）から、必要な情報の抽出を容易にできるツールで、とても便利です。 しかし、操作になれていないと、Excelであれば簡単なことの実現方法が分からずに困ることがあります。 例えば、データソースのエラーなどは、Excelの数式でいえば参照しているシートが無くなったことと同様ですが、どのようにエラー解消するかが直ぐにわからず利用を躊躇します。 データソースのエラー解消する方法は次のとおりですが、意図的にデータソースを変更する方法の備忘録。 shikumika.org データソースの変更方法 データソースの変更は、「データ…

2023/06/12 06:00

【Power Query】データソースのエラーを解消する方法（DataSource.Error）

PoweqQuery（パワークエリ）で、データソースのファイル名や、ファイルの場所が変更になると、次のようなデータソースのエラーが発生します。 DataSource.Error: ファイル ‘〇〇.xlsx' が見つかりませんでした。 例えば、データソースとなるファイル名に「売上データ_〇月分」といった取得月の情報が含まれているケースでは、設定時と同じファイル名に修正するか、データソースの再設定が必要となります。 しかし、PoweqQueryに慣れていない場合、エラーメッセージに戸惑うことになるので、DataSource.Errorでデータソースを再設定する方法の備忘録。 （ファイル内の項目名…

2023/06/11 12:00

【Excel】VBAのメンテナンス性向上のため設定情報をテキストボックスに記載

VBAの利用で、業務の属人化によるリスクが指摘されることがよくあります。そのため、誰かとVBAを含むExcelファイルを共有するような場合、業務変更が生じても少しExcelに詳しければ修正ができるようにと、初期値や設定情報をExcelのシートに記載などの対応をしています。 しかし、初期値や設定情報をExcelのシートのセルに記載する場合、セル位置を変更したり、意図せず変更されるとコードの修正が必要になります。 シート内のテキストボックスであれば、セル位置の変更に対応できそうだったので、試行の備忘録。 フォームコントロールやActiveXコントロール だと、VBAを知らない人の設定変更が難しいと…

2023/06/10 12:00

【Power Query】空白ではない列の個数を行ごとにカウントする方法

Power Queryを使用して「ExcelのCOUNTA関数」のように、各行ごとに空白ではない列の個数をカウントする方法の備忘録。 ExcelのCOUNTA関数は、特定の範囲内の空白でないセルの数を数えるために使用しますが、Power Queryで同様な関数はList.NonNullCount関数。 アウトプットイメージ 横方向のカウントで、「ExcelのCOUNTA関数」であれば次のような数式。 例） =COUNTA(B2:F2) 具体的なアウトプットイメージは次のとおり。 PowerQueryで行ごとにB列からF列で、空白でない列の個数を行ごとにカウントする。 作成方法 「カスタム列」の…

2023/06/09 18:00

【Power Query】特定の列と同じ値の列の個数を行ごとにカウントする方法

Power Queryを使用して「ExcelのCOUNTIF関数」のように、特定の列と同じ値の列の個数を各行ごとにカウントする方法の備忘録。 なお、特定の列ではなく、"指定した値"でカウントしたい場合は、前回の以下です。 shikumika.org アウトプットイメージ 横方向のカウントで、「ExcelのCOUNTIF関数」でいうと次のような数式。 例） C2:F2の範囲で、B2と一致した個数をカウント =COUNTIF(C2:F2,B2) PowerQueryでの具体的なアウトプットイメージは次のとおり。 行ごとに、C列からF列（区分1～区分4の列）を対象にB列と一致する個数をカウントする。…

2023/06/08 18:00

【Power Query】指定した値と同じ値の列の個数を行ごとにカウントする方法

Power Query（パワークエリ）を使用して「ExcelのCOUNTIF関数」のように、指定した値と同じ値の列の個数を各行ごとにカウントする方法の備忘録。 アウトプットイメージ 横方向のカウントで、「ExcelのCOUNTIF関数」でいうと次のような数式。縦方向のカウントをしたい場合は、Power QueryでExcelのCOUNTIF関数のように、各行の値の重複を数える方法（List編）に記載。 例）B2からF2で、”〇”と一致する個数をカウント =COUNTIF(B2:F2,"〇") PowerQueryで具体的なアウトプットイメージは次のとおり。 行ごとにB列からF列にある”〇”の個…

2023/06/07 12:00

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

Excelで、1㎝の方眼紙や、㎝や㎜で幅や高さを指定した四角い枠を印刷したい時は、「ページ レイアウト ビュー」を使用すると可能です。 標準のビューや、改ページプレビューを使用している時は、ポイントでしか幅や高さを指定できませんが、「ページ レイアウト ビュー」であれば「cm」や「㎜」で幅や高さを指定できます。 ポイントの指定では大きさのイメージが持ちづらいので「cm」や「㎜」で指定できる「ページレイアウトビュー」は便利です。 具体的な方法は、次のとおり。 表示をレイアウトビューにすると、行の高さや幅の単位でセンチメートルが使用可能になる。 行の高さや列幅の設定では、次のように「cm」の単位と…

2023/06/06 18:00

【Excel】VBAで、印刷時に文字が切れないように行高さ設定

Excelを使用してセルに長文を入力し、複数行表示にしたデータを印刷すると、文章の下端が切れてしまうことがあります。 その原因と解決方法の基本は次のとおりで、標準フォントで調整が必要。 shikumika.org しかし、複数のフォントを使用していたり標準フォントを変更したくないケース、セル結合があるケースは、上記の解決方法だけでは十分に対応できません。 結果、何十ページにもわたる長文（フリーコメント）の一覧を印刷物としてアウトプットするときは、チェック時間が長くなる。 なので、VBAで、行高さを自動設定するサンプルを作成してみた備忘録。 （行高さのオートフィットでは対応できないケース用。セル…

2023/06/05 18:00

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

ExcelのVBAを使用して、列幅や行高さを設定する際は単位の違いに注意が必要。知らないと混乱の元になるので備忘録。 具体的には、行の高さを設定するRowHeightと、列の幅を設定するColumnWidthを使って、 Rows(1).RowHeight = 30 Columns(1).ColumnWidth = 30 で、同じ"30"の設定値としても、単位が違うので数値の意味が違うということ。 列の幅を設定する際の単位は、文字数 行の高さを設定する際の単位は、ポイント と違うので注意が必要です。 なお、文字数は、標準スタイルの 1 文字を基準に換算。プロポーショナルフォントでは、数字の 0 …

2023/06/04 18:00

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

Power Queryで、全角文字を半角文字に一括置換。Text.Replaceを使った時にエラーが発生して変換できなかったので原因と解決方法の備忘録。 全角文字を半角文字に一括置換が必要になった背景は、Excelで表記ゆれがある日付文字を日付データ（シリアル値）に変換結果に示したとおり、全角文字があるとPower Queryではシリアル値には変換できないため。 そのため、変換リストを作成し、Text.Replace関数で一括置換を試みたが、 Expression.Error: 値 1 を型 Text に変換できません。詳細: Value=1 Type=[Type] というエラーで、次のような…

2023/06/02 18:00