shikumika’s diary

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

Excelでシリアル値のまま西暦表示から「元年」も含む和暦表示に変更できることを知らなかったので備忘録。 具体的には、「2019/10/1」を「令和元年10月1日」に書式設定で表示可能。 もちろん、”元年”ではなく”1年”と表示することもできる。 設定方法は次のとおり、[セルの書式設定]の日付で、[カレンダーの種類]を[和暦]とし、 [1 年を元年と表記する] のチェック ボックスがオンにするだけ。 日本の年号変更と元年 - Microsoft サポート なお、Power Queryで同様の機能は見つからなかった。 ただし、上述の設定はシリアル値の書式設定のことなので、アウトプットのテーブルで…

2023/05/29 18:00

【Power Query】Excelの日付文字列にある「元年」を「1年」に置換

Excelで日付文字列に和暦の「元年」があるとき、DATEVALUE関数ではシリアル値には変換できない（反対に、西暦から和暦の「元年」への表示は可能だが）。 そのため「検索と置換」の機能で”元年”を”1年”に一括置換をしたり、SUBSTITUTE関数で置換したりで対応することになります。 例：DATEVALUE関数とSUBSTITUTE関数の組み合わせ （セルのA2に日付文字列が入っている事例） =DATEVALUE(SUBSTITUTE(A2,"元年","1年")) Power Query（Excelの標準機能）も同様に、次のとおり和暦の「元年」があると、シリアル値に変換できないようなので、…

2023/05/28 18:00

【Word】VBAで、画像は「レイアウト（文字列の折り返し）」によってオブジェクトが変わる

Wordの画像は「レイアウト（文字列の折り返し）」で行内に設定されているか否かでVBAでの処理方法が違うのでメモ。 例えば、以下で作成したVBAサンプルは、レイアウトが行内に設定された図が対象で、他のレイアウトだと処理できない。Wordのファイル内にある同じ画像でもレイアウト（文字列の折り返し）を変更するとVBAの処理方法が変わる可能性があるので注意が必要です。 shikumika.org 上記との比較で具体的にいえば、行内に設定された画像はオブジェクトがInlineShapesで、行内に設定されていない画像はShapesと違いが生じる。 よって、行内に設定されていない画像は、次のとおりSha…

2023/05/27 18:00

【Word】VBAで画像のレイアウトを行内から「上下の文字配置」に変更

WordのVBAで、画像のレイアウトを行内から上下の文字配置に変更するサンプル。 必要になった背景は、Wordの画像は「レイアウト オプション」で行内に設定されていると、画像の複数選択ができないようで一括での更新が手間だったため。 それで、次のような簡易なVBAを作成したが、図のレイアウトを行内から上下の文字配置にしたい場面での備忘録。 shikumika.org 図のレイアウトを行内から上下の文字配置に変更する場合のVBAサンプル 「レイアウト オプション」で行内に設定された図のみが対象です。 この実行後は、上下の文字配置になるので、他のコードに行内の図を対象にした箇所があれば、VBA実行の…

2023/05/26 18:00

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

Excelでデータ集計をする際、元データの取得方法の違いなどによって、日付の表記方法が一貫していないことがあります。 たとえば、文字列で「2019/10/1」「2019年10月1日」「令和元年10月1日」といった様々な表記や、文字列中に空白が含まれるなど。 Excelでは、日付形式（シリアル値）に自動変換してくれるもの、単なる文字列と認識されるものがあり、期間集計の結果や並び替え等に問題が生じることもあります。 しかし、日付の表記方法が一貫していないデータを集計・分析する業務は時折発生。 日付に表記ゆれがあった場合の変換結果の違いについて、Excelの一般的な数式とPowerQueryで調べた…

2023/05/25 18:00

PowerPivotでのマトリクス表作成事例：CONCATENATEX関数の活用

PowerPivot（Excelの標準機能）は高機能なデータ分析ツールで、マトリクス表の作成にも役立ちます。特に、文字列を連結できるCONCATENATEX関数を活用すると、通常のピボットテーブルでは作成が困難なマトリクス表も作成できます。 例えば、次のようにピボットテーブルの中に、文字列をそのまま表示したり、列を結合した文字を表示することができます。 上記は、CONCATENATEX関数でできることを理解する目的で、少しだけ複雑なことをしてみた結果の備忘録。 横方向で2つのフィールドの文字列を連結したり、縦方向で改行での連結が可能です。具体的な数式サンプルは次のとおりです。 =CONCATE…

2023/05/24 18:00

PowerQueryの数式編集時、日本語変換で二重入力される事象の解決メモ

PowerQueryで数式を編集する際、日本語入力（漢字変換）をすると二重入力となり、都度、文字削除が必要でとても煩わしく感じていました。ATOK（ATOK for Windows ver32）を利用していましたが、Microsoft IMEに変更することで、適切に入力できるようになったので備忘のメモ。 具体的には、PowerQueryの数式編集で日本語入力時、漢字変換して確定すると次のように同じ単語が二重で入力されてしまうというもの。 例）”区分”という漢字変換を確定すると、同じ単語が二重で入力される 区分区分 日本語入力だから仕方がないのかと思ってそのまま利用続けていたが、Microsof…

2023/05/23 18:00

Wordで、ワイルドカードを使用した検索ができない事象の調査と対応

Wordで、ワイルドカードを使用した置換をしようとしたところ、検索条件はあっているにも関わらず「一致する項目はありません」が時折発生。 同じ検索条件で、何度か試していると、きちんと検索ができる時もあり動作が不安定。事象の調査と対応の備忘録。 具体的には、【Word】特定の単語を検索し、その単語間にある文章を削除する方法で、”Recorded?Steps*Steps^13”を検索していた時に発生。 結果、このケースで、チェックすべきはカーソルの位置と思われる。 どうやら、検索する文字列にワイルドカードを使用して「*（半角アスタリスク）」と「^13”（改行）」を検索する場面で、「*」に該当する文字…

2023/05/22 18:00

【PowerPivot】メジャーで改行文字を入力(CONCATENATEXの区切文字)

ExcelのPowerPivot のメジャーで、改行文字を入力する方法に悩んだので解決方法の備忘録。 結果、単純に改行するだけだった。 今回、「データ分析式 (DAX) の数式：CONCATENATEX」の区切文字に改行を使用したかった。この場合は次のように、ダブルクォーテーション間で改行する。 =CONCATENATEX(table,expression,"") ExcelにあるようなChr関数はDAXでUNICHAR関数になるが、ExcelのDAXにはない模様。 なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSO 。 そもそも、改行文字…

2023/05/21 12:00

【Word】VBAでステップ記録ツールのmhtファイルを編集する準備

WordのVBAで、ステップ記録ツールのmhtファイルをWordで編集するポイント で示した最初のルーチンについて、一通りのパーツ（VBAの各サンプル）が作成できました。 この最初のルーチン（各サンプルVBAの実行）を実行することで、「ステップ記録ツール」で保存されるmhtファイルを下図のようにできます。 文書から画像（スクリーンショット）のはみ出しや、余分なスペースが無くなっただけなので、手順書作成での必要な情報は維持。 個人的には、情報過多で編集に躊躇していたmhtファイルも編集してみようという気持ちになります。 一つの事例ですが、ステップが36ある手順がWordファイルの状態で 80ペー…

2023/05/20 12:00

【Word】特定の単語を検索し、その単語間にある文章を削除する方法

Wordで、特定の単語を検索し、その単語間にある文章を削除したい場合があります。 そのような時に役立つのも、Wordの置換機能で「ワイルドカードを使用した検索」です。 ワイルドカードを使用することで、特定の単語間にある文章を検索して削除することができます。 今回も、ステップ記録ツールのmhtファイルをWordで編集するポイント で示した作業事例です。 具体的には、ファイル内に”Additional Details”という文字列から”Return to top of page...”の文字列までの文章が不要というケース。 Additional DetailsThe following secti…

2023/05/19 18:00

【Word】特定のパターンに一致する文字列検索と置換のサンプル

Wordは特定のパターンに一致する文字列検索もできます。いわゆる正規表現に近い検索が可能です。 例えば、ステップ記録ツールのmhtファイルをWordで編集するポイント で示した作業では、 Step 1: (2023/05/18 18:41:10) ユーザーによる・・・ Step 2: (2023/05/18 18:42:11) ユーザーによる・・・ Step 3: (2023/05/18 18:43:15) ユーザーによる・・・ といったステップの番号と日付、時刻が記録された文字列が頻出します。 このような文字列に対して、ステップの番号と日付が不要というケースなども、Wordの置換機能で「ワイ…

2023/05/18 12:00

【Word】単語中の一部だけが違う類似文字列の一括検索と置換

Wordで、単語中の一部だけが違う類似文字列の一括検索と置換。いわゆる「ワイルドカードを使用した検索」と置換の備忘録。 具体的には、ステップ記録ツールのmhtファイルをWordで編集するポイント で示した作業で、ファイル内に 手順 1 のスクリーン ショット。 手順 2 のスクリーン ショット。 手順 3 のスクリーン ショット。 ・・・ といった数字だけが違うような文字列を一括検索と置換をしたいケースなど。画面でわかりづらいが、文字列内に空白文字もいくつかある。 この文字列を検索して削除する場合は、検索と置換で「ワイルドカードを使用する」にチェックし、次のように検索と置換をする。 「検索する…

2023/05/17 12:00

【Word】ファイル内のフィールドを一括削除する方法（一括置換とVBA）

Wordファイルで、ファイル内にある「フィールド」を一括で削除する方法の備忘録。 背景は、ステップ記録ツールのmhtファイルをWordで編集するポイント で示したように、「Previous Next」などのハイパーリンクが余分で削除したかったため。 「Previous Next」は、検索して一括で空白に置換する時は少し注意が必要。わかりづらいがフィールドの文字で、”Previous”と”Next”は別々に検索して置換が必要。ただし、この置換ではWordの表示上は空白で削除できているが、フィールドコードは残っている見込。 目的上は上記でも問題なさそうだが、フィールド文字を完全に消したい場合は、 …

2023/05/16 12:00

ステップ記録ツールのmhtファイルをWordで編集するポイント

PC画面の操作マニュアルの作成に便利な「ステップ記録ツール」。作成したmhtファイルをWordで編集しようとファイルを開くと、マニュアル作成に不要な情報量が多く、ページ数も多いことに戸惑います。 Wordでの編集作業を効率的にするポイントの備忘録。 まず、mhtファイルをWordで開くと最初に戸惑うと思うのが、スクリーンショットの画像の文書からはみ出し。例えば下図のようなイメージで、余分なスペースができていたり、水平線が数多くあったり、ファイル後半には詳細のUI情報なども記録されている。 「ステップ記録ツール」の本来の目的は、WindowsなどPCの問題をトラブルシューティングするためとのこと…

2023/05/15 12:00

Wordで、ファイル内にある余分な水平線を一括削除するVBAのサンプル

Wordファイルで、ファイル内にある余分な図形タイプ（水平線）を一括で削除するVBAサンプル。 変更対象の図は、行内に設定された図（インライン図形）で、水平線のタイプ。 Sub 水平線の一括変更() Dim 画像 As Object For Each 画像 In ActiveDocument.InlineShapes '行内の図が対象 If 画像.Type = wdInlineShapeHorizontalLine Then '水平線のみ抽出 画像.Delete End If Next End Sub 作成の背景は、PC画面の操作マニュアルの作成に便利な「ステップ記録ツール」で作成したmhtフ…

2023/05/14 18:00

PC作業の引継・手順化には、ステップ記録ツール（Windows標準）が有用

PC作業の業務引継や手順化には、Windows標準のステップ記録ツールが有用です。Windowsには、PCの操作手順を画面キャプチャも含めて自動保存してくれる標準ツールがあります。 業務の可視化やマニュアル化、業務引継の時間短縮に活用できます。 動画で作成するのも手間で「費用（時間）対効果」が得られない業務、マニュアル作成の専用ツールも導入が難しいケースでは、Windows標準の「ステップ記録ツール」が最適な印象。 support.microsoft.com 操作は単純で、上記のMicrosoft サポートのページのとおり、記録したい手順のスタートで「記録の開始」、手順の記録が終了したら「記録…

2023/05/13 18:00

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

VBAで、PowerQueryの更新を待ってピボットテーブルを更新するときの備忘録。PowerQueryの更新を待たずにピボットテーブルが更新されてしまい、データ更新前の状態でピボットテーブルが表示されてしまった。 元は、 ActiveWorkbook.RefreshAll Sheets("集計").PivotTables("ピボットテーブル1").PivotCache.Refresh で、一旦すべてのデータ（外部データは、ピボットテーブル）を更新した後、念のためピボットテーブルを再更新というもの。 更新されないまま、次の処理に進んでしまうので、調べてみたら、 RefreshAllの仕様として…

2023/05/12 12:00

【Excel】年度別の順位推移表のようなマトリクス表を作成する方法

Excelで、下図の左側のような元データから右側のような年度別の順位推移表（マトリクス表）を作成する方法についての備忘録。順位表に値をそのまま表示するようなケースです。 一覧データからマトリクス表（例：年度別のランキング推移表） なお、結論は、次のPower Pivot（Excelの標準機能） を利用する方法が基本。繰り返し発生する業務や、分析軸を編集したいなど後日のメンテナンス性を考えた場合はPower Pivotの方法が最適となるケースが多い印象。 shikumika.org 別の方法として、同じくExcelの標準機能であるPowerQueryの「列のピボット」を利用しても作成可能。ただし…

2023/05/11 12:00