2022/05/11
【6】要望ボックス

【6】要望ボックス

特定の色を数えるユーザー定義関数について、色を変えたタイミングで反映させる方法はありますか?
(参考:https://www.youtube.com/watch?v=kVaO0IGirGE

【回答】

色を変更する際に、他のセルからコピペするなどの場合は自動更新されますが、色を変更するだけでは自動更新されないです。
関数が更新されるタイミングは、引数に対して変更があった場合になります。
ただ、色を変更するだけでは、引数が変更されたとはならないみたいです。

上記の内容を踏まえて思いつく対策を2つ挙げますと以下になります。
※他にもいろいろあると思います。

①余分な書式もコピーされてしまいますが、色付けを書式のコピーなどにより行う

②リアルタイムの更新ではありませんが、変更後に別のセルを選択したと同時に更新する

「②」の場合は、対象のシートモジュール、もしくはブックモジュールにて特定のイベントプロシージャに以下の内容を記述する必要があります。

Application.CalculateFull
※「Application.Calculate」では計算されません。

「②」について、特定のシートのみに自動更新を適応する場合は、シートモジュールの「SelectionChange」を用います。
こちらは特定のシートに対し、セルの選択が変更されたタイミングで実行されるイベントプロシージャになります。

上記の画像のように選択して、下記の内容「Application.CalculateFull」を追記します。

これだけで完了です。
「Application.CalculateFull」ではなく「Application.Calculate」の場合、色変更では反応してくれないです。
なので、「Application.CalculateFull」にして、すべてに対して更新をするようにしています。

次にブック全体に自動更新を適応する場合は、ブックモジュールの「SheetSelectionChange」を用います。
こちらはシートモジュールで説明した内容と同じで、セルの選択が変更されたタイミングで実行されるイベントプロシージャになります。
違いは、すべてのシートの適応される点です。

上記の画像のように選択して、下記の内容「Application.CalculateFull」を追記します。

これだけで完了です。

2022/04/17
【5】要望ボックス

【5】要望ボックス

指定した列の中に空白がある場合、
その行を自動で削除したいのですが、
VBAでどのように実現しますか?

【回答】

削除する方法はいろいろありますが、その中の1つを紹介します。

・「条件を選択してジャンプ」の機能を活用する

「ホーム」内にある「検索と選択」→「条件を選択してジャンプ」を使うと空白セルをまとめて選択することができます。
使い方のイメージは次の画像です。

これをVBAで再現すると、SpecialCells(xlCellTypeBlanks)になります。
後は、これによって選択された範囲をEntireRowで行選択をして行全体を削除するだけです。
※この時、空白セルが1つも見つからない場合、エラーになります。
そのため、IsNullを用いてデータが格納されていることをチェックしています。

上記の参考ファイルはこちら▼

他の方法として対象範囲を上からループさせ空白なら削除するというように実現することもできますが、行を削除した場合は繰り返している数がずれるので、少し複雑になります。

また、今回紹介した方法は完全に削除しています。
削除ではなく、非表示にしたいという場合は、以下の内容が参考になるかと思います。

>>空白行を自動でグループ化

2022/03/07
【4】要望ボックス

【4】要望ボックス

表から
「複数条件を満たしたデータの数」を
数えたいのですが、どの関数を使えばよいのでしょうか?

【回答】

COUNTIFS関数で実現できます。

使い方は、以下になります。

=COUNTIFS(範囲①,条件①,範囲②,条件②,…)
※すべての範囲は同じ行数、列数を指定する必要があります

使用例は以下のようになります。


=COUNTIFS(A3:A13,”>=2022/1/2″,A3:A13,”<=2022/1/7″,C3:C13,”男”,E3:E13,”>=170″)

A3:A13,”>=2022/1/2″
→登録日が2022/1/2以降の件数
A3:A13,”<=2022/1/7″
→登録日が2022/1/7以前の件数

この2つで2022/1/2~2022/1/7の期間で絞れます。

更に、
C3:C13,”男”
→性別が男性
E3:E13,”>=170″
→身長が170以上

この2つで170以上の男性に絞れます。

よって実行結果は、「2」になります。

条件に使える演算子は以下のものがあります。

・「 = 」等しい
・「<>」等しくない
・「 > 」より大きい
・「 < 」より小さい
・「>=」以上
・「<=」以下

他にもワイルドカードというものがあります。

・「*」:0文字以上の任意の文字列
・「?」:1文字以上の任意の文字列

例えば、先ほどの表の中から名前が「name1」の数を数える場合、

=COUNTIF(B3:B13,”=name1″) または、=COUNTIF(B3:B13,”name1″)
→「1」

になります。
ここで「name1」を含む数としたい場合、

=COUNTIF(B3:B13,”*name1*”)
→「3」

になります。
※今回の場合は、「*name1*」が「name1*」でも同じ結果になります。

ちなみに、次の場合はどうなるでしょうか?

=COUNTIF(B3:B13,”name1?”)

こちらでは「name1」の後に「?」を入れています。
「?」という文字は1文字以上の任意の文字列になります。
よって答えは「name10」と「name11」の「2」になります。

演算子に関しては、「演算子+比較値」で表現できます。

・10より大きい場合は、「>10」
・2022/1/1以降の場合は「>=2022/1/1」

といった感じです。

2022/02/27
【3】要望ボックス

【3】要望ボックス

CSV読込について、YouTubeやネットで調べたもので試したのですが、
(参考:https://www.youtube.com/watch?v=xHmiB_XnIyM
1行で出力されてしまいます。
なぜでしょうか?

【回答】

この原因は「改行コード」にあります。
全ての文字には文字コードと呼ばれるコードが割り当てられています。
「改行」も、「改行」という文字になります。

ややこしいことに、この「改行」には複数のものが存在します。
それは「CR、LF、CR+LF」です。
一般的に、Windowsでは「CR+LF」、MacOSでは「CR」、UNIXでは「LF」が使われています。

参考動画のプログラム内の「Line Input」では、「LF」コードのみで改行されたデータを1行ずつ読み取ることができません。
そのため、1行で取得されてしまいます。

改修方法としたら、1行で取得された内容をSplitで「LF」コードで分割して実行すると良いです。

具体的な方法として、参考動画について解説します。
プログラム内の「Line Input」は、 「Line Input」 した内容が1行ずつという前提で、その情報を「,」区切りで配列に格納しています。

この1行ずつという手順を追記することで解決します。

改行コードが「LF」の場合は、この前提が使えなくなります。
なので、取得したデータを「LF」ごとに分割し、配列に格納します。
※「vbLf」とは「LF」コードを意味します。

「LF」ごとに分割された配列のデータを1行ずつ読み込むようにすることで解決します。

<補足>

画像のプログラムでは、1行ずつのデータを「lists」に格納して、「list」に1行ずつを渡しています。
必要に応じて、下記のような変数宣言を追記した方がよいです。

Dim list As Variant, lists As Variant

2022/02/19
【2】要望ボックス

【2】要望ボックス

商品名に連動して金額入力できますか?

【回答】

VLOOKUPやXLOOKUPのような参照式を活用することで解決するかと思います。
商品マスタを作成し、入力された商品名を元に金額を抽出するというイメージです。

注意点としましては、商品名が正しく入力されない場合は、抽出されないことです。
ドロップダウンリストなどで対応すると良いかと思います。

※商品の数が可変の場合は、テーブル化がオススメです。
ドロップダウンリストの参照式は「=INDIRECT(“テーブル名[項目名]”)」で対応できます。

以下のようなイメージになります。

1.商品マスタを選択し、「挿入」→「テーブル」でテーブルにします。

※テーブルに項目名がある場合は、「先頭行をテーブルの見出しとして使用する」にチェックを入れ「OK」を押下します。

2.作成されたテーブルに名前を付けます。
テーブルを選択すると「テーブルデザイン」というタブが表示されるため、その中の「テーブル名」に好みの名前を設定します。

3.参照式を作成します。
=VLOOKUP(商品名を入力する項目,テーブル名,表示したい列番号 ※値段の場合は、テーブルの2列目なので「2」,[省略])
この内容の場合、検索結果がないとエラーが表示されます。
気になる場合は、IFERROR関数を活用すると便利です。
=IFERROR(VLOOKUP(…),””)

4.入力用の項目をリスト化します。
リスト化したい項目を選択し、「データ」→「データの入力規則」を押下します。

設定内容は「入力値の種類」を「リスト」にし、「元の値」を「=INDIRECT(“テーブル名[項目名]”)」にします。

5.完成
商品マスタにデータを追加した場合でも、リストに反映されるようになっています。
※商品マスタは別のシートで管理しても問題ございません。

2022/02/16
【1】要望ボックス

【1】要望ボックス

顧客管理システムなどで、
(参考:https://www.youtube.com/watch?v=Pq2PbGDy2iM
更新した日時を自動で入力する項目を追加することはできますか?

【回答】

上記の動画の場合、
「Worksheets(“従業員一覧”).Cells(no,”B”).Value = .Range(“D5”).Value」
など、管理シートに反映させるコードがあります。
ここで、同時に日時も反映させれば解決するかと思います。

例えば、「従業員一覧」シートのG列に更新日という項目を用意する場合は、下記のようなものを追加すると解決します。
「Worksheets(“従業員一覧”).Cells(no,”G”).Value = Now」