shikumika’s diary

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

VBAでFunctionとSubの使い分けに悩んだ場合の対応

VBAで「Functionプロシージャ」と「Subプロシージャ」の使い分けの説明に悩んだので、考え方のメモ。

個人的な結論として、悩んだときは全て「Functionプロシージャ」で良いになりました。

 

VBAでしている業務を別担当に引き継ぐことになり、少し整理してみました。

 

一般論としては、「Functionプロシージャ」と「Subプロシージャ」の主な違いは、次のとおりです。

項目 Function Sub

「マクロの表示」から実行できるか

(単独で実行できるか)

×
戻り値があるか ×
ユーザー定義関数として、EXCELワークシートから使用できるか ×

 

「マクロの表示」から実行できるかというのは、Excelの画面から「マクロの表示」で、次にマクロが表示されるかどうかということ。

「Functionプロシージャ」は表示されず、実行できないのでデメリットのようにも思えます。しかし、VBAのプロシージャを分割して作成すると「表示されないことがメリット」になります。

 

Private Sub」や引数付きのSubにすると、「マクロの表示」の一覧からは消えます。

これまでは、余分なプロシージャを見せたくないので、分割したプロシージャは「Private Sub」なども活用していました。

 

「Functionプロシージャ」にしていても、

  • 「Functionプロシージャ」から「Subプロシージャ」への変更は、名前変更するだけで容易
  • 「Functionプロシージャ」で戻り値がなくてもエラーにならない

です。もし「Subプロシージャ」が適切と思えるような場面になったら、その時にすればいいだけです。

 

私はないですが、「Functionプロシージャ」で、ユーザー定義関数を無駄に表示させたくないというケースがあるかもしれません。その場合は、日本語のプロシージャ名にしておけば、数式オートコンプリートで表示されることもないと思います。

(日常、プログラム開発をしない誰かに引き継ぐなら、プロシージャや変数名の日本語化は内容理解のメリットが大きいと思うので)

 

まとめると、「マクロの表示」で表示させて実行したいなど、単独実行したい場面がない限り、「Functionプロシージャ」だけで良いという結論になりました。

もちろん、メインのプロシージャだけは「Subプロシージャ」になると思います。

 

Power QueryでCOUNTIF関数のように各行の値の重複を数える方法(List編)

前回、Power QueryでCOUNTIF関数のように各行の値の重複を数える方法として、グループ化の機能を使用した方法を紹介しました。今回はListを使った方法です。

 

ただし、前提として、今回の方法は使用できる場面が少ない印象です。

大量データには不向きなので、その場合は前回の「グループ化」を使う方法が最適と思います。

shikumika.org

 

今回の方法だと数千件程度まででないと、処理時間がとても長くなる印象です。

(私の環境で、3000件のサンプルデータで試したところ、グループ化なら1秒程度の計算時間が、今回の方法で30秒ほどかかり、処理時間が大きく異なりました)

 

アウトプットイメージ

前回と同様、「名前」という列にある人と同じ名前の件数を「カウント」に表示するイメージ

下図で、「名前のカウント」部分をクエリで集計します。

 

作成手順

「テーブルまたは範囲から」で、クエリの作成

次のような、顧客ID、名前、都道府県、年代などのデータがあり、名寄せチェックのために「名前」の重複カウントをパワークエリで数えたい、とします。

 

「カスタム列」の追加

上記の実施後、次のクエリの画面が表示されるので「カスタム列」をクリック

 

新しい列名は、適当な名前を入力し、カスタム列の式には次のような式を入力します。

=List.Count(List.FindText(list as list, text as text))

 

具体的には、今回の場合は、

  • list部分: 一つ前のステップ名である”変更された型”と、重複を数える対象の列名
  • text部分: 重複を数える対象の列名

を使って、以下を追加する。

List.Count(List.FindText(変更された型[名前], [名前]))

 

「名前」の重複カウントが追加されます。

 

他の方法との違い

なお、前回記載のとおり、現状のPower Query(パワークエリ)で、各行の値の重複件数を数える方法は「グループ化」が最適と思われる。

 

理屈は不明で推測でしかないですが、今回の方法は、レコード毎にList.FindText(変更された型[名前]を計算して、List.Count関数で計算することになる印象。

(Excelで大量データ時にするCOUNTIF関数も遅いので)

 

データ量が少ない時以外は、使えなさそうです。

Power Queryを使う場面で、データ量が少ないケースはそれほどないと思いますが。

Power QueryでCOUNTIF関数のように各行の値の重複を数える方法(グループ化編)

Power Query(パワークエリ)に、Excelの数式でいうCOUNTIF関数がありません。

名簿の名寄せなど、各行の値の重複を数えたいときに悩んだので解決方法のメモ。

良さそうな方法は次の2つ

  • 「グループ化」を使う方法
  • List.Count関数、List.FindText関数を組み合わせて使う方法
     ※ ただし、こちらは大量データには不向きな印象

今回は、「グループ化」を使う方法について説明します。

 

アウトプットイメージ

「名前」という列にある人と同じ名前の件数を「カウント」に表示するイメージ

作成手順

「テーブルまたは範囲から」で、クエリの作成

次のような、顧客ID、名前、都道府県、年代などのデータがあり、名寄せチェックのために「名前」の重複カウントをパワークエリで数えたい、とします。

 

「グループ化」をクリック

上記の実施後、次のクエリの画面が表示されるので「グループ化」をクリック

 

重複件数を集計したい「名前」でグループ化

ポイントは、次のとおり。

①「詳細設定」にする

②重複件数を集計したい列を選択

③「行数のカウント」とする。「新しい列名」は適当な名前で可。

「すべての行」という項目を追加する。こちらも「新しい列名」は適当な名前で可。

 

Tableを展開する。

グループ化した列(今回は「名前」という列)で、レコードの重複数が計算される。

今回、実施したいことは、レコードの重複数を元のデータに付与したいので、赤枠の箇所のボタン(データを展開)をクリックする。

次のように元の列名が表示される。グループ化に使用した「名前」の列は既にあるので、今回はチェックを外す。

*チェックを外さない場合は、同じデータの列ができるだけ。

(補足)「元の列名をプレフィックスとして使用します」のチェックを外した方が、元の列名になるので良い。今回の場合でいえば、「他の列.顧客ID」という列名か、「顧客ID」という列名になるかの違いです。

 

すると、レコードの重複数を含めた一覧が作成できる。

 

ただし、上記はグループ化した順番の一覧。

もし顧客IDなどの並び替えの基準があるデータなら、次のように並び替えると元データにレコードの重複数を含めた一覧ができる。

 

他の方法との違い

色々調べた結果、現状のPower Query(パワークエリ)で、各行の値の重複件数を数える方法は「グループ化」が最適と思われる。

 

List.Count関数、List.FindText関数を組み合わせて使う方法は、処理時間が異常に長くなり、大量データには不向きな印象。

Excelで大量データ時にするCOUNTIF関数と同様な処理時間?)

 

なお、今回はテーブルを展開したが、重複件数のクエリと元データのクエリを2つ作成し、マージする方法もある。グループ化した重複件数のクエリ結果自体をアウトプットとして使用する場合は、マージする方法が良さそうです。

 

Microsoft 365 Familyの商用利用、 別居の家族の利用に関する規約等

「Microsoft 365 Family」が、2022 年 7 月 19 日 (火) より日本国内向けに提供開始されました。家族用のお得なサブスクリプションで、Office アプリや、1TB のクラウド ストレージの OneDriveなどのサービスを、最大 6 人まで利用可能とのこと。

 

家庭で利用するOfficeのライセンスを見直しをしようとしていたので、商用利用や、同居でない家族での利用について規約等を調べてみました。

商用利用について

「Microsoft 365 Family」を仕事用で使用してよいかについては、以下規約をみると駄目とのこと。一般向けのライセンスでは、「Microsoft 365 Personal サブスクリプション」の日本での利用のみが例外で「非商用目的の制限が適用されず、商用利用が可」となっています。

  1. インストールと使用権: アクティブ化

a.ライセンス、サインインするユーザーとデバイスの数、その他の条件。本サービス/ソフトウェアは使用許諾されるものであり、販売されるものではありません。Microsoftは、お客様が本契約のすべての条項に従うことを条件として、個人利用に限り、お客様が所有および管理するデバイスに本サービス/ソフトウェアの複製をインストールして実行する権利を以下のとおり許諾します。

(i)Microsoft 365 Family サブスクリプション。お客様とその世帯の一員 (5 名まで) が使用できます (合計 6 名まで)。

(ii)Microsoft 365 Personal サブスクリプション。お客様のみ使用できます。Microsoft サービス規約に記載されている非商用目的の制限は、日本に居住するお客様、または日本に居住していたときにサービス / ソフトウェアに対するサブスクリプションを取得したお客様には適用されません。

していたときにサービス / ソフトウェアに対するサブスクリプションを取得したお客様には適用されません。

引用:Microsoft サービス規約の補遺

 

Officeの商用利用とは?

そもそも商用利用について何かという部分は、職種を問わない企業の業務 (在宅勤務、持ち帰り仕事も商用利用にあたります)などが決められています。

Microsoft 365 Familyを自宅での持ち帰り仕事用としては使えないことになります。

代表的な商用利用のケース
・職種を問わない企業の業務 (在宅勤務、持ち帰り仕事も商用利用にあたります)
・個人事業主の業務
・教育機関の業務 (学生が個人所有のデバイスを利用して行う学業に関する活動のみ商用利用にはあたりません)
・NPO の業務
・国、地方公共団体の業務

引用:一般消費者向け Office および 一般消費者向け Office 関連製品の商用利用権について - 楽しもう Office

 

家族の定義

どこまでが家族かについて、冒頭の「Microsoft サービス規約の補遺」において「お客様とその世帯の一員 」となっているので、生計を共にしているかどうかのようです。

 

仕事でも使用するなら、Microsoft 365 Personal  など、商用利用できるライセンスが必要なようです。

 

Power Query(パワークエリ)でフォルダ指定を相対パスにする方法

Power Query(パワークエリ)のデータソース場所は絶対パスでの指定しかできないようだ。

データソースとなるファイルの場所を変更した場合、都度変更の手間が発生する。

 

社外など別環境の人に渡したり、毎月などデータソースの場所を都度変更したい場合など、どのようにデータソースを変更するかで悩む。

特に、Power Queryに慣れていない人に渡すには、データソースの変更は難易度が若干高い。

 

その解決方法の一つとして、データソースを相対パスにする方法がある。

ただし、相対パスでのフォルダ指定は、セキュリティ面でのリスクも検討必要らしいが、使い方を限定していれば問題なさそう。

とりあえず、手順のメモと、その他の方法を整理。

 

まず、相対パスの設定方法として、参考になった情報。

sakatakablog.com

 

ポイントとなりそうなのは、次の点。

  1. 読み込む先(Power Queryがあるファイル)と、対象データを同じフォルダ内とする
  2. Excelの数式で、相対パスの情報(その絶対パス)を抽出し、Excelのテーブルを作成する
  3. Power Queryの詳細エディターで、上記2の情報を変数として利用する設定をする。(2箇所)

 

なお、次のような情報もあったので、一応参考に。

要は、上記1の「読み込む先(Power Queryがあるファイル)と、対象データを同じフォルダ内とする」というケースなら問題がなさそう。自分の使い方では、セキュリティのエラー等も発生しなかった。

 

memo.furyutei.com

 

その他の方法

その他の方法に、相対パスの情報(その絶対パス)を、Excelのテーブルではなく「名前の定義」の機能を使うものもある。個人的な印象でしかないが、「名前の定義の重複エラーが苦手」なので、とりえず上記のテーブルの機能を使った方法にしようと思う。

 

なお、データソースの変更は以下で「データソースの設定」で、フォルダ(ファイル)の参照を変更することで簡単にできる。

繰り返しの頻度が少ない場合は、まずはこの方法から。

 

 

Excelで「値のみ貼り付けのショートカットキー」の代用(Windows)

Excelで値のみ貼り付けの作業を効率化したいが、なぜかWindowsでは「値のみ貼り付けするショートカットキーがない」とのこと。

その代用(Windows)として、個人的には、次の方法が最適と思う。

コピー後、

  1. Ctrl+V
  2. Ctrl
  3. V

の順で、キーをおす。


Windowsの場合で、「Ctrl+Alt+V」キーを含めた方法など、他の方法もあるが、自分の指ではどうも押しづらくて苦手。

 

具体的な手順

コピーする(Ctrl+C)

例えば、次のような書式情報を含んだ表。

 

Ctrl+Vで、貼り付けする

書式情報も含んで、一旦貼り付けされる。

すると、[貼り付けオプション]ボタンが表示される。

「Ctrl」キーを押す

上記の[貼り付けオプション]ボタンが表示されている状態で、「Ctrl」を押す。

マウスでクリックしても良いが、ショートカットキーということで)

 

「V」キーを押す

値のみ貼り付けは、「値(V)」なので、「V」キーを押す。

 

値のみ貼り付けの状態になる。

 

他の方法との違い

形式を選択して貼り付けのショートカットキー Ctrl+Alt+V

「Ctrl+Alt+V」キーでの方法は、個人的には押しづらい。また余分の「Enter」キーの操作が増える。

 

値貼り付けのマクロを登録して、ショートカットキー登録

値貼り付けの場面がとても多い場合は有効。ただし、マクロでの値貼り付けとすると、「元に戻す Ctrl+Z 」ができないのがデメリット。

 

 

なお、「Ctrl+Shift+V」の書式なし貼り付けの方法は、Excelでは利用できない。

 

 

ピボットテーブルの並び替えが思いどおりにならない時のチェックポイント

Excelピボットテーブルの並び替えが思うようにいかず、出来ないものと勘違いしていたので、その解決方法のメモ。

並び替えができないと思っていたのは、

  • 行フィールドに2つ以上の項目あり
  • 列フィールドにある総計以外

といったパターン。

この場合も、降順など並び替えがきちんとできた。単に操作方法が誤っていた。

Excelをよく利用している人でも、勘違いしている人が多いのでは?)

 

手順

行フィールドに2つ以上の項目をいれた場合、値の昇順などが思うようにできずに困っていた。

 

例えば、このようなピボットで、並び替えをしたい値(この場合は「$C$7」)をクリック後、値順に並び替えしようとしたとき、思うように並び替えができない。

 

赤枠内の文字をよく読むと、

個数 / 商品IDをキーとして、この列の値を使用して 年代 を降順 で並び替えます

となっている。並び替えの対象は、行フィールドにある一階層上の項目ということ。

それで最上位の階層の並び替えが思うようにできなかった。

 

勘違いしていたが、並び替えをしたい方の項目や値(この場合は「$A$6」「$A$7」など)を選択して、並び替えをクリックすると、次のように先ほどとは違う画面が表示される。

 

ここで、昇順・降順の基準となる値を選択して、実行する。

 

そうすると、意図どおり並び替えができた。

 

特定の列の値で並び替え

さらに、次のように総計ではなく特定の列の値で並び替えをしたい場合がある。

 

この場合は、前述と同様、並び替えをしたい方の項目や値(この場合は「$A$6」「$A$7」など)を選択して、並び替えをクリック、「その他のオプション」をクリックする。

 

その後、並び替えをしたい値(この場合は「$C$7」)を選択して並び替える。

 

そうすると、特定の列の値で並び替えができた。