Excel for Mac の VBE で変数の宣言を強制する。

この記事は自分用の覚え書きです。
(MacBook Pro (macOS Mojave 10.14.1) + Excel for Mac 16.19(181109) Office365 Business サブスクリプション)

Excel を起動したら、option + F11 で VBE を起動する。
(Touch Bar にファンクションキーが表示されていないときは、fn キーを押しながら、option + F11 を押す。)

画面上部にあるメニューバーの「Excel」から「環境設定」を選択。

「変数宣言が必要です」にチェックを入れて、OKをクリック

S20181124_125926

これで、変数を必ず宣言して使う状態になっている。

| | コメント (0) | トラックバック (0)

Excel で第二月曜日は何日かを求める。

この記事は自分用の覚書です。
(Windows 10 Pro 64ビット + Excel2016 Office365 Business サブスクリプション  バージョン1810(ビルド11001.20074)

H.K さん。

ある月の第二月曜日は、何日になるかを求めるやり方です。
月曜日を対象にしていますが、何曜日であっても基本的な考え方、やり方は同じです。

日数や曜日を扱う計算については、インターネット上でたくさんの方々がいろいろな解説をしてくれています。

もっとわかりやすい説明があるかと思います。見つけたら私にも教えてくださいね。

考え方

以下、当たり前の話ですが大事なところなのであらためてふれておきます。

第二月曜日というのは、その月で二度目の月曜日です。
「ある日が二度目の月曜日」なのは、一度目の月曜日が過ぎてから初めておとずれた月曜日だからです。

一週間は七日あります。

ということは、「第一月曜日の日付(シリアル値)」がわかれば、それに 7 を足すと第二月曜日の日付を求めることができます。

たとえば、2018年11月なら、第一月曜日が 11月5日 だとわかれば、2018/11/5 + 7 の計算で、第二月曜日である 2018/11/12 を求めることができます。

そして、「ある日付が第一月曜日である」とわかるには、その月の「一日は何曜日か」がわからなければなりません。

一日が日曜日なら第一月曜日は翌日の2日だとわかります。
一日が火曜日なら第一月曜日は7日です。

「その月の一日の日付」を求めることが最初にすることです。

「すること」を順番で並べると――

1.「その月の一日は何曜日か」を調べる。

2.1 の答えをもとに、「第一月曜日の日付(シリアル値)」を求める。

3.2 の答えである日付に 7 を足して「第二月曜日」を求める。

となります。

ちょっと話がそれますが、曜日を考えるとき、横軸上に左から右に順に並んでいるものとしてイメージしませんか? 
日・月・火・水・木・金・土――このように曜日を捉えると、今回の処理は考えづらいかと思います。

曜日が円形につながっているものとして捉えると、今回の処理は考えやすいと私には思えました。

週の始めを日曜日からとしたほうが考えやすい人もいるでしょうし、月曜日からにしたほうが考えやすい人もいるでしょう。
一週間の曜日を円形に並べて捉えれば、どちらの考え方をする人でも、今回の処理は少しでも考えやすくなると思うのですが、どうでしょうか。

Shitiyou_2

その月の一日の曜日を 1 として時計回りに数えてください。

一日が日曜なら、日曜を 1 として時計回りに数えると、第一月曜日は2日です。一日が火曜なら、火曜を 1 として 2,3,4,5,6,7 と数えて第一月曜日は7日……というふうに。

一つひとつの処理を作業列に分けて行う。

話を元に戻します。上記の3つを一つのセルでやろうとすると、数式がネストされたややこしいものになってしまいます。

仕事でつくるファイルは、できるかぎり、誰が見てもわかりやすくつくるべきです。
そういう意味で、作業列を使って処理の過程を分けるのが望ましいです。作業列で過程を分ければ全体として何をしているかを理解しやすいです。

仕事でつくるファイルでは、ネストしなければならない具体的な理由がない場合は、数式のネストは避けるのが望ましいと私は思っています。

1.「その月の一日は何曜日か」を調べる。

全体としては、次の図のようにしてみました。

01dainangetuyoubi

A列からD列で、「その月の一日は何曜日か」を調べています。

C列では、DATE 関数で一日の日付をつくっています。
もともとは年と月しか入っていない表だということでしたので、そこからそれぞれの月の一日を求めています。

また、C列のセルの書式を yyyy/m/d(aaa) にして、何曜日であるかが見た目でもわかりやすいようにしてみました。

一日の日付をつくったら、何曜日かを求めます。それがD列です。

C列はセルの書式で曜日を表示していますが、書式は計算につかえる値にはなりません。
曜日を、計算で使える値にしてやる必要があります。

WEEKDAY 関数でそれができます。書式は次のようです。

WEEKDAY(シリアル値,[週の基準])

D列で使っている WEEKDAY 関数では、引数「週の基準」を省略しているので、日付が日曜だったら 1 を返します。

月曜なら 2 を、火曜は 3 というように返してきます。土曜なら 7 が返ってきます。
一週間の曜日を表す数字なので、返ってくる値は 1 から 7 までです。

・引数「週の基準」を省略した際に WEEKDAY 関数が返す値
日曜日→1
月曜日→2
火曜日→3
水曜日→4
木曜日→5
金曜日→6
土曜日→7

2.「第一月曜日の日付(シリアル値)」を求める。

一日が何曜日かを表す数字ができたので、それをもとに第一月曜日が何日かを求めます。
E列とF列でそれをやっています。

なお、図では、少しでもわかりやすくなるかと思って、E列にはセルの書式設定で「日」を表示するようにしてあります。

ここで、冒頭の円の図をもう一度見てみてください。
一日が何曜日かによって変わってくる「第一月曜日が何日か」の確認です。

一日が日曜だったら、時計回りに 1,2 と数えて第一月曜日は2日です。
一日が月曜なら第一月曜日は当日です。
一日が火曜なら第一月曜日は7日になります。
水曜なら6日です。
木曜なら5日です。
金曜なら4日です。
土曜なら3日です。

――というように、一日の曜日によって、第一月曜日が何日なのかが変わってきます。

どんな数式をつくればよいかというと、

WEEKDAY 関数が返す値が、

・ 1 (一日が日曜)なら 2 を返す。
・ 2 (一日が月曜)なら 1 を返す。
・ 3 (一日が火曜)なら 7 を返す。
・ 4 (一日が水曜)なら 6 を返す。
・ 5 (一日が木曜)なら 5 を返す。
・ 6 (一日が金曜)なら 4 を返す。
・ 7 (一日が土曜)なら 3 を返す。

ができればいいわけです。
返ってきた値が、第一月曜日は何日かを示しています。

02dainangetuyoubi

Office365サブスクリプションの Excel2016 には、こういうときに最適な関数が実装されています。 SWITCH 関数です。

円の次の図では、セル E2 にはこういう数式が入っています。

=SWITCH(D2,1,2,2,1,3,7,4,6,5,5,6,4,7,3)

SWITCH 関数の書式は次のようです。

SWITCH(式, value1, result1, [既定値または value2, result2],…[既定値または value3, result3])

SWITCH 関数は、(式と呼ばれる) 1 つの値に対して値の一覧を評価し、最初に一致する値に対応する結果を返します。
(出典:Excel「ヘルプ」)

上記の式でいうと、( )のなかで最初は D2,1,2, になっています。
D2 の値が 1 の場合は、2 を返すという指定です。

その月の一日が日曜(D2 が 1)なら、第一月曜日は2日ということです。

その次を見ると、D2,1,2,2,1, になっていますから、一日が月曜(D2 が 2)なら、返す値は 1 です。当日が月曜なので。

その次は、3,7 が続きます。一日が火曜(D2 が 3)なら 7 を返します。第一月曜日は7日です。

そうやって残りも、4 なら 6、5 なら 5 、6 なら 4 、7 なら 3 を返すというように指定しています。

実際には、E列のセルは次のように入力しています。
2行目以降ではスペースを入れて頭をそろえています。

=SWITCH(D2,1,2,
                   2,1,
                   3,7,
                   4,6,
                   5,5,
                   6,4,
                   7,3)

こうしておけば、「(D2が)1 なら 2 を返す、2 なら 1 を返す、3 なら 7 を返す……」といった組み合わせが理解しやすいですよね。

03dainangetuyoubi_switch

なお、Office365サブスクリプションではない Excel では、CHOOSE 関数で対応できます。

CHOOSE 関数の数式は、

=CHOOSE(D2,2,1,7,6,5,4,3)

となります。

D2 に入るのは、1 から 7 です。
2,1,7,6,5,4,3 の部分は、D2 の値によって返す順番に並べています。

D2 が 1 なら、2 を返します。(並びの一番目を返す)
D2 が 2 なら、1 を返します。(並びの二番目を返す)
D2 が 3 なら、7 を返します。(並びの三番目を返す)……

書き方が違うだけで、 SWITCH 関数も CHOOSE 関数も結果は同じです。

IF 関数でも可能ですがお勧めしません。その理由は想像すればわかりますよね。
「IF 関数ならこうなります」という例を作ろうとしたくもありません。かなり面倒です。

=IF(D2=1,2,IF(D2=2,1,""))

2つだけやってみました。2つ目のIFを入力したときにもうやりたくなくなりました。

E列で第一月曜日は何日なのかがわかったので、それをもとにF列では、 DATE 関数で第一月曜日の日付をつくっています。

3.第一月曜日の日付に 7 を足して「第二月曜日」を求める。

G列が第二月曜日の日付です。
F列の第一月曜日の日付に 7 を足しています。

F列の時点で、 =DATE(A2,B2,E2)+7 というように 7 を足せば、F列で第二月曜日を求められますが、ここでは、「第一月曜日の日付(シリアル値)がわかれば、それに 7 を足すことで第二月曜日を求めることができる」という思考の過程がよりわかりやすいように分けてみました。

これで作業終了です。
こうやって作業列で処理を分けると全体で何をやっているかが理解しやすくないですか。

仕事でつくるファイルでは、作業列を積極的に使ってください。

別の曜日について

なお、別の曜日でも考え方は同じです。
違ってくるのは、この例でいうなら SWITCH 関数の引数です。

また、第三○曜や第四○曜日を求めるには、第一月曜日の日付に 7 を足して第二月曜日を求めたように、処理を分けて 7 を足していけばいいです。

| | コメント (0) | トラックバック (0)

Excel for Mac でも VBA で日本語が使える。

この記事は自分用の覚書です。
・Windows 10 Pro 64ビット バージョン:1803 OSビルド:17134.345
Excel2016 Office365 Business サブスクリプション  バージョン1809(ビルド10827.20150)
・MacBook Pro (High Sierra 10.13.6)
Excel for Mac 16.18(181014) Office365 Business サブスクリプション

N.T さん。

Windows 側でつくったマクロですが、H.K さんに手伝ってもらって Excel for Mac で正常に動くことを確認できました。

マクロ名や変数名が日本語でも問題なく動きました。
少なくとも、試した環境においては日本語を含むという理由で正常に動かないことはないといえます。
標準モジュール上での日本語の入力も問題なくできましたよ。

試した流れは次のようです。

1. Windows 版 Excel のほうでコードを書く。

2. 拡張子xlsm で保存してからファイルを Mac 側に渡す。

3. Excel for Mac でファイルを開いてもらってマクロを実行

テストに使ったプロシージャは以下のようです。
ちょっとだけひねくれた書き方をしてみました。この程度で不具合が起きるなんてありえんだろうと思いましたが、テストなのであまりしない書き方を。

このプロシージャなら、普通なら For の行を For i = 2 To 11 にして、 Cells プロパティでは i に 1 は足さないですよね。

■試したプロシージャ

Sub サンプル()
Dim i As Long
Dim 連番用足し込み As Long

For i = 1 To 10
    連番用足し込み = 連番用足し込み + 1
    Cells(i + 1, "A").Value = 連番用足し込み
Next i
'
'セル A2 から A11 に、1 から 10 までの連番を入力する。
'
End Sub

| | コメント (0) | トラックバック (0)

Excel の表全体でどこかにある重複する値を見つける。

この記事は自分用の覚書です。
(Windows 10 Pro 64ビット + Excel2016 Office365 Business サブスクリプション  バージョン1809(ビルド10827.20138)

H.N さん

Excel で、表全体を対象にしてどこかにあるかもしれない重複する値を見つける―― VBA でコードを書いたり、関数で数式をつくったりしないで――のやり方です。

難しく考える必要はありません。
「表のどこかのセルが同じ値であることがわかる」というのでよければ、条件付き書式を使って「同じ値ならセルを塗りつぶす」などとするのが一番簡単―― Excel がもっている機能だけで、いくつかの手順をたどれば求める結果が得られる――でしょう。
(もっと手軽なやり方がわかったら私にも教えてくださいね。)

条件付き書式では、条件に「重複する値」あるいは「一意(重複しない)の値」を選ぶことができます。
次の図では A2 の「菊池寛」は C2 と C9 にもあるので、それらが色付けされています。

Hyouzentai_tyouhukusuruatai00

1.色付けしたい範囲を選択。
上図では、A2 から C10。

2.「ホーム」タブの「条件付き書式」の「セルの強調表示ルール」から「重複する値」を選ぶ。

Hyouzentai_tyouhukusuruatai01

3.「重複する値」のダイアログボックスが表示されて、表の中で重複している値のセルに、指定している色付けがされる。
「値」の欄で「一意」を選ぶと、重複していない値を条件にしたことになる。

Hyouzentai_tyouhukusuruatai02

4.そのままでよければ、これで完了。
「書式」の欄で、どのような色付け方をするかを選択できる。
標準で入っている色付け方以外の指定をする場合は、「ユーザー定義の書式」を選択。

Hyouzentai_tyouhukusuruatai03

5.OK をクリックして画面を閉じる。

これで、表のなかで重複している値を見分けることができます。

書式を意識しなくてよいので、上記の手順が楽だと私は感じています。

「組み込みの書式以外でなければならない具体的な理由」なんてそうはないですし。
自分の好みの色付け方でなくても、目的が達成できているならそこはもうそれでよしと私はしています。

重複している値の一覧を作る。

重複している値の一覧についても「一番簡単」に済ませるやり方を考えました。
今回の場合は、行や列が何十、何百あるわけではないですし、手作業で抜き出してもそんなに負担はないでしょうから。

1.表内のどれかのセルをクリックしてからオートフィルターを有効にする。

2.A列で「色フィルター」を実行して、セルの色かフォントの色を条件に絞り込む。

Hyouzentai_tyouhukusuruatai04

3.絞り込んだA列の値をコピーして、別の場所へ貼り付ける。
下図では E12 を選択して貼り付けている。

Hyouzentai_tyouhukusuruatai05

4.A列のフィルターで「すべて選択」を選んで「色フィルター」を解除する。

5.B列で「色フィルター」を実行。

6.絞り込んだ結果をコピーして、先に貼り付けた値の下へ貼り付ける。
図では、絞り込んだB列の値を、E17 から貼り付けている。

Hyouzentai_tyouhukusuruatai06

7.B列で「すべて選択」を選んで「色フィルター」を解除する。

8.残りの列すべてで 5.から 7.と同様の操作を行う。

9.貼り付けた範囲を選択して、「データ」タブの「重複の削除」をクリック。
図では E12 から下を選択。

Hyouzentai_tyouhukusuruatai07

10.「重複の削除」画面が表示されたら OK をクリック。
この場合は、E12 から選択したので、「先頭行をデータの見出しとして使用する」のチェックは入れない。

Hyouzentai_tyouhukusuruatai08

11.「重複する〇〇個の値が見つかり、削除されました。一意の値が〇個残っています。」の表示が出たら OK をクリック。

Hyouzentai_tyouhukusuruatai09

12.重複している値の一覧ができあがる。

Hyouzentai_tyouhukusuruatai10

手順3.で貼り付ける場所はどこでもいいです。たとえば、新しいシートを挿入して貼り付けるとかでも。
この場合は、例なので全体が見えたほうがよいかなと思って、同じシートを使っています。

なお、表の行単位で全く同じ値がある場合も、前述の「重複の削除」で重複している行を削除して1つの行だけ残すことができます。

Hyouzentai_tyouhukusuruatai14

A2 から C10 を選択して「重複の削除」を実行します。
残るのは選択した範囲内で一番上にある行で、下のほうにある行が削除されます。

上の表なら、2行目が残って5行目が削除されます。

Hyouzentai_tyouhukusuruatai15

また、ちょっとしたことですが、表の近くに別のセル範囲を作るときは、先にある表と接しないように作るのがコツです。
ここでは、それでもなるべく近くにしたかったので、表から1列空けて1行下から始まるようにしました。

Hyouzentai_tyouhukusuruatai11

というのは、ショートカットキーを使いやすくしておくためです。
たとえば、表全体を選択する際によく使うショートカットキー( Ctrl + Shift + : )では、セル範囲が接していると、その範囲まで含んで選択されてしまいます。

Hyouzentai_tyouhukusuruatai12

また、1行下から始めておかないと、表のオートフィルターの条件によっては、1行目のセルが非表示になってしまいます。

Hyouzentai_tyouhukusuruatai13

| | コメント (0) | トラックバック (0)

凄すぎる人の話は普通の人には難しい。

「ダウンタウン」の松本人志さんがツィートした「天才の成功話はほどほどに聞いといた方が良いよ。天才は振り返り方も天才だから」という言葉が話題になっているのだとか。

確かに。――という気がする。

「ベストプラクティス」を社内で共有するために、成果を上げている人の話を聞いて社内報に載せたり、研修で紹介したりすることがある。

その際に思うのは、「凄すぎる人の話は普通の人が聞いてもうまく役に立てられない」ということ。

「ベストプラクティスは、なるべくたくさん集める」ことを言われるのは、そういうことからなのかと思ったことがあるぐらい。

凄すぎる人の話をいくら集めても、普通の人はうまく役に立てることができない。たくさんの数の体験談を集めておけば、うちのいくつかは普通の人が聞いてもわかるし、役に立てやすかったりするという意味――なのかも。

| | コメント (0) | トラックバック (0)

«Excel2016 で、あるセルの内容によって別のセルに色を付ける。