エクセル

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)

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

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

H.M さん。

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

ココログのアクセス解析を見ると、この類の情報を求めてうちのブログがぽろぽろと見られています。

「条件付き書式 別のセル」などのキーワードの組み合わせで。

そう思いながら、あらためて自分のブログを見返してみると、Excel2016 で「あるセルの内容によって別のセルに色を付ける」については書いてないのでまとめてみました。

といっても、Excel2016 になっても特別なことはなくて、旧バージョンと同じなんですけどね。条件付き書式というものです。

↓こういうことをしようというわけで……。

00joukentukisyosiki01

C列が「合格」ならA列のセルに色を付けています。

条件付き書式を設定する。

画像の場合は次のような手順になります。

1. 条件付き書式を設定したい範囲――図では A2 から A5 まで――を選択
2. 「ホーム」タブの「条件付き書式」から「新しいルール」を選択
3. 「ルールの種類を選択してください」」の欄で、「数式を使用して、書式設定するセルを決定」を選択
4.「次の数式を満たす場合に値を書式設定」の欄に以下の数式を入力
= $C2="合格"

01joukentukisyosiki_susiki

5. 「書式」ボタンをクリック
6. 「セルの書式設定」の画面が表示されるので、「塗りつぶし」タブをクリック
7. 任意の背景色を選択して、OK をクリック

これで、C列の値が「合格」の場合、A列のセルが選択した色で塗りつぶされます。

なお、行を塗りつぶすには、手順1. で A2 から C5 までを選択しておきます。
そうすると、C列のセルが「合格」のときには、その行に色が付きます。

02joukentukisyosiki_gyounuritubusi

これまでと同じことを書いても面白くないですし、せっかくなのでちょっと違うことも書いてみます。

以下、いろいろと――。

条件付き書式を設定する前に明らかにすること。

4つあります。

  1. 目的
    何のためにつくるファイルなのか。
  2. 条件付き書式を適用する場所
    どのセル、どの範囲に適用するのか。
  3. 条件は何か。
    セルの値が50以上である――など。
  4. どんな書式にするのか。
    セルを赤く塗りつぶす――とか。

「そんなの当たり前でしょう」という気がしますよね。私もそう思います。でも、これがなかなか……。

私が経験してきたなかでは、特に1番の「目的」がはっきりしていないことってあるんですよね。

たとえば、「セルの値が5未満だったら、その値を赤色の太字にしたい」って相談を受けたことがあります。
何に使うのかを尋ねたら、そうやって、5未満の値を目立たせた資料を作って、社内のミーティングのときに見やすくしたいという。

そういうことなら、文字を赤くするよりはセルを塗りつぶすか、網かけしたほうがよいと話したらそうすることになりました。

赤い文字は画面では目立ちますが、モノクロ印刷したら区別がつきません。
セルに色を付けておけば、モノクロ印刷のときでもそこがわかります。

「ミーティングのときに見やすくしたい」と思ってはいても、それが目的としてしっかり意識されていなかったんですね。
目的がはっきりしたら、設定するべき書式が変更になったわけです。

何のためにつくるファイルなのかをはっきりさせておけば、そのために何をどうすればよいのかを考えやすくなります。

行は相対参照にする。

上の図の場合は――ということです。

手順1.で セル A2 から A5 までを選択していますから、行のほうは相対参照にしておかないと、行の指定がずれていきません。

= $C$2="合格"

にすると、A2 から A5 までのセルの塗りつぶしを決める条件は、セル C2 の値のみにしたことになります。
もし、C2 が「合格」だったら、C列の他のセルに関係なく、A2 から A5 の全部が色付けされてしまいます。

列と行で指定をずらしていかなければいけないほうは、相対参照にする――です。

絶対参照と相対参照の切り替え

F4 キーを使います。

「次の数式を満たす場合に値を書式設定」の欄を入力する際に、欄内をクリックしたあとで、セル C2 をクリックすると、C2 が絶対参照になります。

=$C$2

と入ります。

03joukentukisyosiki_zettaisansyou

あるいは、条件のもとになるセルや範囲が、別のシートにあったりすると、手入力するよりはそのシートを開いてそこを選択したほうが手っ取り早いですよね。その場合もセル参照が絶対参照になります。

セル参照が必要な参照の状態になるまで F4 キーを押してください。
押すたびに、=$C$2 → =C$2 → =$C2 → =C2 → =$C$2 とセル参照が変わっていきます。

基本的なことですが、絶対参照を意味する「$」は、「列を参照する英字」と「行を参照する数字」の左側に添えます。
(列か行のどちらかを絶対参照にする場合は、その左側にだけ。)

既定では、Excel は 「A1 参照スタイル」と呼ばれるセル参照を使います。
(出典:マイクロソフトのサイトにある「Excel の数式の概要-Excel の数式にセル参照を使用する」)

セル参照の並びは、C2 のように「列を参照する英字」と「行を参照する数字」になりますから、列も行も絶対参照にする場合は、 $C と $2 で $C$2 です。
列 C と 行 2 が交差するセルを絶対参照する指定です。

「$ で挟んでいるから――」と考える人がいました。
$C$ というイメージをしたのでしょうね。そう考えては、セル参照はどういうものかがわからなくなります。

「次の数式を満たす場合に値を書式設定」の欄を編集するには。

F2 キーを使います。

冒頭の手順4. で「次の数式を満たす場合に値を書式設定」の欄内にカーソルがあるときに矢印キーを押してしまうと、わけのわからない表示になる場合があります。

たとえば、=$C$2="合格" と入力してしまってから、$2 になっていることを気付いて、2 の左側の $ だけを消したくて矢印キーでカーソルを $ の前に動かそうとすると、=$C$2="合格"+$A$2 となったり……。

04joukentukisyosiki_sansyou

エクセルのステータスバーを見てください。シートより左下のスミのあたりです。 「参照」とか「入力」となっているはずです。(通常では「準備完了」)

05joukentukisyosiki_nyuryoku

ここが「入力」となっているときに、「次の数式を満たす場合に値を書式設定」の欄のなかでカーソルを動かそうとすると、さきほどのようなわけのわからない表示になります。

この欄を編集したいときは、カーソルが欄内にある状態で F2 キーを押します。

ステータスバーに「編集」が表示されるまで F2 キーを押していってください。
「編集」が表示されれば、左右の矢印キーを使って、欄内で自由にカーソルを動かすことができます。
これは「編集モード」と呼ばれる状態です。

06joukentukisyosiki_hensyu

話は少し変わりますが、編集モード( F2 キーでステイタスバーに「編集」を表示)は、条件付き書式以外にも役に立ちます。

たとえば、「データの入力規則」を使って、セルに入れる値をリスト表示させているとき。
リスト表示させる値を「元の値」の欄に直に入力していて、リストの値を編集しようとして欄をクリック後に矢印キーを押すと、ここでもわけのわからないセル参照が入ってしまいます。

07f2_list_hensyuu

そういうときも、「元の値」の欄をクリックしてから、F2 キーを押して、編集モードにすれば、リストの元の値を自由に書き換えることができます。

08f2_list_hensyuumode

あるいは、セルやセル範囲に名前を付ける際に、参照範囲を編集するのにも使えます。

09f2_namae_hensyuumode

ほかにも、セルの数式を書き換える際にも F2 キーを押すとセルが編集モードになります。
(セルをダブルクリックするのと同じです。)

Windows 上でファイル名やフォルダ名を変えたいときにも使えますよ。

話を元へ戻します。

条件付き書式の数式を編集する際には、「次の数式を満たす場合に値を書式設定」の欄をクリックしてカーソルを置いたら、F2 キーを押してステータスバーに「編集」と表示されるようにする――これを忘れなければ、変なセル参照が表示されて困惑することはなくなります。

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

Excel で、数字と英字が混在した文字列からそれぞれを別々に取り出す。その2

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

H.K さん。

その1」の続きです。

数字が英字(あるいは、英字が数字に)挟まっている場合―― abc456defg とか 987WXY54 ――です。

大事なことなので、「その1」で挙げた要点にもう一度ふれておきます。

  • 「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」ことが大原則。
  • そのために、理解がややこしい関数のネストや、我々の職場で理解できる人がほとんどいない配列数式の使用は避ける。
  • 結果を得るまでの過程の一つひとつを作業列で処理することで、全体として何をしているのかを誰でも理解しやすくする。
  • 紹介したやり方が唯一の正解ではない。
  • 関数の理解は、個々人が頑張るしかない。周囲は、その努力への支援を頑張る。「仕事に必要だから覚えてください」と言うだけではだめ。
  • 関数のネストや配列数式を、我々の職場で使ってはいけないということではない。周囲の皆が理解できるか、使わなければならない具体的理由がある場合に使う。
  • 使うべき理由があって、関数のネストや配列数式を職場で使う場合は、どうなっていて何をしているかをわかりやすく説明したものを用意する。大原則の「できるかぎり誰もが簡単に理解できるようにしておく」というのは、そこまで含んでの話。

数字と英字が互いに挟まっている場合

Eisuukonzai06

「元の文字列を部分的に置換して共通の区切りを設ける」ことやその他の基本的な考え方、詳細は「その1」と同じです。

使っている関数も同じです。
CONCAT 関数、SUBSTITUTE 関数、FIND 関数、LEFT 関数と MID 関数です。

ただし、少し異なっている部分があります。
2つ目の区切りの位置を調べる FIND 関数(図ではK列)と、区切りと区切りの間を抜き出す MID 関数(M列)の使い方です。

図の2行目を使って説明します。

2つ目の区切りの位置を FIND 関数で調べる。

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

FIND(検索文字列, 対象, [開始位置])

セル K2 の数式は次のようになっています。

=FIND(G2,J2,F2+1)

引数「検索文字列」は、「/」(全角のスラッシュ)です。
「対象」は  6896/hth/56 です。

「開始位置」は、F列の値( 5 )に 1 を足しています。
引数「開始位置」を使っているのが「その1」と異なるところです。

値を入れると次のようになります。

=FIND("/","6896/hth/56",5+1)

引数「開始位置」を省くと、FIND 関数は左端からみて最初の「検索文字列」の位置を返します。 6896/hth/56 の場合は、1つ目のスラッシュの位置(5文字目なので 5 が返ってくる。)です。

「開始位置」は「検索文字列」が複数ある場合に使います。FIND 関数が検索を開始する位置を指定します。
6896/hth/56 では、「/」が2つあります。

K列の数式では、「開始位置」で1つ目のスラッシュの位置( 5 )に 1 を足しています。最初に現れる「/」より後ろの部分で次に現れる「/」を探すという指定です。
「6文字目から数えると、何文字目に次のスラッシュがあるか」ということです。

2つ目のスラッシュは9文字目なので FIND 関数は 9 を返してきます。

Eisuukonzai07

区切りと区切りの間を MID 関数で取り出す。

例では部分的な置換が終わると、6896hth56 が 6896/hth/56 になります。

1つ目の区切りより前の取り出しと、2つ目の区切りより後ろの取り出しは、「その1」で述べたものと同じ考え方をします。

・1つ目の区切りより前を取り出す。(図のL列)
LEFT 関数の引数「文字数」で、1つ目のスラッシュの直前(1つ目のスラッシュの位置 - 1)を指定して 6896 を得ます。

・2つ目の区切りより後ろを取り出す。(図のN列)
MID 関数の引数「開始位置」で、2つ目のスラッシュの直後(2つ目のスラッシュの位置 + 1)を指定します。
引数「文字数」は、ありえないぐらい大きな数(図では100)を入れておきます。それで 56 を得られます。

真ん中にあたる hth も MID 関数で取り出しますが、MID 関数に不慣れだとちょっと考え方がややこしいかと思います。そこの理解は、個々人の頑張りどころです。(図のM列)

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

MID(文字列, 開始位置, 文字数)

引数「文字列」は、6896/hth/56 です。
「開始位置」は、1つ目のスラッシュの位置 + 1 なので 5 + 1 になります。
(6文字目の h から取り出し始める。)

開始位置から何文字取り出すかを指定する「文字数」は、次のようになります。

2つ目のスラッシュの位置 - 1つ目のスラッシュの位置 - 1

上図に基づいて値を当てはめてみると、次のようになります。

MID("6896/hth/56",5+1,9-5-1)

最初、私は引数「文字数」を、2つ目のスラッシュの位置 - 1 とうっかり考えてしまいました。

6896/hth/56 の場合は、2つ目のスラッシュの位置は 9 文字目なので、それでは 8 文字を取り出す指定になります。
8 文字ということは、1つ目のスラッシュの後ろから残り全部( 6 文字)である hth/56 がセルに表示されます。

ほしいのは hth の3文字です。

2つの区切りに挟まれた部分が欲しいので、ここで注目すべきは、2つ目のスラッシュの位置を一番右端と捉えて、
6896/hth/
までの固まりです。

「開始位置」が、スラッシュの位置 + 1 なので、取り出しは 6 文字目の h から始まります。
ということは、5 文字目のスラッシュまでは不要といえます。2つ目のスラッシュの位置は左端から 9 文字目ですから不要な部分は引いて 9 - 5 = 4 と考えます。

「文字数」で 6 文字目から 4 文字を取り出す指定をしたということです。
これで、hth/ が残ったことになります。

最後に、右端のスラッシュも不要なのでさらに 1 を引きます。

9 - 5 - 1 となって、答えは 3 です。MID 関数で次のように指定したのと同じです。

MID("6896/hth/56",6,3)

「6 文字目から 3 文字を取り出し」ます。その結果、hth がセルに表示されます。

Eisuukonzai08

その1」から長くなってしまいました。

まとめてしまうと――関数のネストや配列数式は、ぱっと見て理解できる人が私たちの職場にはあまりいないので、具体的な理由なく使わないほうがよいです。作業列で処理するほうが誰にでもわかりやすくてよいです。――ということなんですけどね。

長くなってすみません。

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

Excel で、数字と英字が混在した文字列からそれぞれを別々に取り出す。その1

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

H.K さん。

夏休みもあったし、数字と英字が混在した文字列からそれらを別々に取り出すやり方を考えてみました。

ネット検索すると配列数式を使うやり方が見つかりますが、前に「配列数式は使わない。作業セルを使う。」でふれたように、我々の職場では配列数式は避けて、作業列を使うほうがよいです。

また、関数をネストするやり方もよく見つかりますが、関数のネストも何をしているかの理解を面倒にすることが多いです。

「関数をネストしてはいけない」ということではないです。
ネストするしかない場合もあるでしょうから。
( セルの数式がエラーかどうかを判定して、エラーだったらエラー表示を非表示にする――エラーを見せない――とか……。)

配列数式と同じで、関数のネストも、周囲の誰でも理解できるか、そうするべき具体的理由がある場合に使ったほうがよいという意味です。

「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」という大原則を踏まえて、我々の仕事でよく使う関数を使い、作業列で関数のネストをなくして、全体で何をしているかが理解しやすくなるように考えてみました。

なお、以下のやり方が唯一の正解ではないです。仕事でエクセルを使う際にそんなものは無いと私は思います。
(このあと、えらく長くなってしまいました。ごめんなさい。)

課題

  • 半角英数字が混在している。数字に続いて英字、英字に続いて数字といった並びになっている。
    ZW123 や 4537defft など。
  • 字数は一定ではない。
  • ハイフンやスペースなど区切り位置に使える共通の部分がない。

考え方

ZW-123 や 4537-defft といったように区切りとして使える共通のものがあれば、区切りの前後を分けて抜き出すことは難しくありません。FIND 関数、LEFT 関数、MID 関数を使って処理できます。

ということは、作業列を使って「区切りとして使える共通のもの」を付け加えてやればいいんです。
(共通の区切りがあれば、データツールの「区切り位置」機能でも処理できます。今回は前述の関数を使います。我々の職場では必須といえる関数ですし、数式をセルに入れてテンプレートにしておけば、同じ仕事で何度でも使えるツールにすることができます。)

数字と英字が混在した文字列からそれらを別々に取り出す。

何をするのかというと、たとえば元の文字列が 6896hthjd なら左から5文字目の h を、区切りを伴う h に置換します。
6896/hthjd とかに。

Eisuukonzai01

共通の区切りができたら、区切りの位置を FIND 関数で求めます。それをもとにして、区切りの直前までは LEFT 関数で、直後から残りを MID 関数で取り出せます。

それぞれの過程を作業列でやっていきます。

Eisuukonzai02

1行目はその列が何なのかを表す単なる名札なので、わかりやすいように変えてください。

例では、CONCAT 関数、SUBSTITUTE 関数、FIND 関数、LEFT 関数と MID 関数を使っています。

入力する列は3つです。A列、B列、C列です。

A列に「元の文字列」を入力しています。

B列には「区切りとして使う値」を入力しています。ここでは、全角の/ (スラッシュ)にしました。
"この位置で切り取って分ける" ということが、全角のスラッシュでイメージしやすいかなと思ったもので。

C列に入力するのは、区切りの前後で数字と英字を分けた際に、区切りより後ろの固まりの一文字目(区切りの直後)になる値です。
6896hthjd なら、6896/hthjd にしたいのでスラッシュの直後になる h です。

区切りの直前にある 6 ではなく、なぜ直後の h なのかというと、あとで使う SUBSTITUE 関数に関係していますので、そこでふれます。

なお、C列に入れる値はA列の値に合わせて大文字・小文字を守ってください。FIND 関数は大文字と小文字を区別します。

その区別がない SEARCH 関数を使うのも悪くはないのですが、やはり見たままを入力するほうが、"この値の前で分けている" とイメージしやすいでしょうから、FIND 関数のほうを使いました。

D列よりあとの列は関数の計算結果によって埋めています。

D列で使っている関数について

B列の区切りとC列の値を CONCAT 関数で結合しています。
あとで、A列にある元の文字列を部分的に置換しますが、その置換後である文字列をつくっています。

CONCAT 関数は、Office365サブスクライバーの Excel2016 でないと使えません。
もし、Office365サブスクライバーの Excel2016 ではない Excel で同じことをするなら、 =B2&C2 のように & を使って値を結合するか、CONCATENATE 関数を使ってください。

CONCAT 関数を使った理由は、今回は互換を考える必要がないですし、マイクロソフトが CONCAT 関数の使用を推奨しているからです。
(「今後は CONCAT を使用することを検討する必要があります。これは、CONCATENATE が Excel の将来のバージョンで利用できない可能性があるためです。」 出典:Excel2016 ヘルプ)

E列で使っている関数について

SUBSTITUTE 関数を使っています。
A列の値を部分的に置換することで共通の区切りを付け加えています。

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

SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

引数「文字列」が A 列のセル。
「検索文字列」は C 列のセル。
「置換文字列」は D 列のセル。
になります。

「置換対象」は 1 です。
A列のセルにある文字列のなかでC列の値を探しますが、文字列のなかに同じ値が複数ある場合、左端から何番目にあるその値を置換するのかを指定しています。

区切りの直後にあたる値を置換したいので、1 にします。ここを省略すると、同じ「検索文字列」があれば、全部置換されます。
6896hthjd なら、h は5文字目と7文字目にあります。「置換対象」は 1 なので、5文字目(1番目)の h だけが /h に置換されます。

6896hthjd が 6896/hthjd になります。

Eisuukonzai03

同じ値が複数ある場合、区切りの直前の値(ここでは4文字目の 6 )を 区切りと結合したものに置換( 6/ )するには、その値が左端からいくつ目になるかを常に数えないといけません。

課題にあるように「字数は一定ではない」からです。

そこで、区切りの直後にある値を結合するようにしておきます。そうすれば、区切りのところで分けると、直後の値は常に1番目です。
引数「置換対象」を 1 にしておけば、同じ値がいくつあって気にしなくてもよくなります。

Eisuukonzai04

REPLACE 関数を使っても同様のことができますが、やってみると、SUBSTITUTE 関数を使う場合より作業列が1つ増えるのがわかりました。もうひと手間かけて REPLACE 関数を使うべき理由がないので、SUBSTITUTE 関数を使うことにしました。

F列で使っている関数について

E列の値のなかにある区切りの位置を、FIND 関数で求めています。

FIND 関数は、「指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返します。」(出典:Excel2016 ヘルプ)

6896/hthjd では、スラッシュは5文字目なので 5 になります。

G列とH列で使っている数式

G列とH列で、数字と英字を別々に取り出しています。

G列では LEFT 関数を使って、E列の値のうち区切りより前を取り出しています。
H列では MID 関数を使って、区切りより後ろを全部取り出しています。

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

LEFT(文字列, [文字数])

左側から何文字取り出すかを、引数「文字数」で指定します。
左端から区切りの直前まで取り出すので、「文字数」は「区切りの位置 - 1」になります。

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

MID(文字列, 開始位置, 文字数)

引数「開始位置」は、元の文字列の何文字目から取り出し始めるかの指定です。
区切りの直後から取り出し始めるので、「区切りの位置 + 1」になります。

引数「文字数」は、「開始位置から何文字取り出すか」の指定ですから、あり得ないくらい大きい値を式のなかに書いてあります。(ここでは 100 )

これで、数字と英字が混在している文字列をそれぞれに分けることができます。

結果を得るまでに必要な一つひとつの処理を作業列にすることで、各数式はできるだけ単純なものにして、全体として何をしているかを誰にでも理解しやすくしたつもりですが、どうでしょう。

関数のネストや配列数式を使えば、値を入力する必要がなくなるのに……。

もしそう感じるなら、「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」という大原則を忘れています。

でもまあ確かに、区切りと結合する値を入力する作業が面倒と言えば面倒ですよね。

ネット検索で見つかるような関数のネストや配列数式を使えば、その作業は無くなります。
ですが、我々の職場では何をしているかを理解できる人が限られるファイルになります。

「(誰にでも理解しやすくしておくために)区切りと結合する値を入力する作業」を避けたくて、「(職場で理解できる人が限られる)配列数式や関数のネストを使う」というのは、私に言わせれば、乱暴な仕事の仕方です。

ここで紹介したやり方だと、何をしているかを誰もが簡単に理解しやすいようにしておけます。
「誰にでも理解しやすい」という効用は、区切りと結合する値を入力する少々の面倒を込みにしてもまだ利のほうが多いと、私は判断しました。
(今回はデータ数が何百、何千とあるわけでもないですし。)

元の文字列を見ながら値を入れていくだけなので、勤め始めたばかりの新入社員やパートタイマーの方、アルバイトの方にも無理なくやってもらえます。

上図でいえば、入力してもらうのは実質的にC列だけです。
元の文字列はすでにありますし、B列の「区切り」やD列からH列の数式は、あらかじめ入れておけます。

数式がエラーを起こしますが、C列に値を入力したら消えるエラーなのでそのままでよいと思います。
内部だけで使うものですし、エラーを非表示にしなければならない具体的な理由もありません。

もし、列が増えて見づらければ処理の途中にあたる列を非表示にしたり、必要なレイアウトをつくった別のシートから値を参照すればよいのは、前出のリンク先で述べた通りです。

Eisuukonzai05

使っている関数を知らない人もいるから、誰にでも理解しやすいとは言えないのでは――。

職場には各関数の使い方をよく知らない人もいるでしょうが、それらの理解は各人の頑張りどころだと私は思います。

我々の職場では、配列数式を使わなければならないことなんて「無い」と言い切っても言い過ぎではないぐらいですが、関数を使わないで済ませることはできません。

ここで使っている関数は、我々の日々の仕事でしばしば使います。理解しておかなければならない具体的な理由があるといえます。

これらの関数をよく知らない人が職場にいるかもしれませんが、理解できるように各々頑張ってください。仕事に必須ですから――ということです。

もちろん、理解のための支援は、私も含めて周囲が頑張らないといけません。「仕事に必要なんだから理解しておいてよ」と言うだけではだめです。

もし関数のネストや配列数式を使うべき具体的な理由があって使う場合も、大原則は変わりません。
何をしていて、どうなっているのかを、わかりやすく説明したものを用意するなどしてください。

「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」という大原則は、そこまで含んでの話です。

そもそも、「職場には、使っている関数をよく知らない人もいる」からこそ、配列数式や関数のネストなんてややこしいものを使わないで――作業列で――済ませられるならそのほうがいいでしょう。

やってみた感想

上記では「私、わかってます」風な書き方をしてますが、けっこう悩みました。

「共通の区切りがあれば話は簡単」というのは、割と早めに思いついたのですが、どうやれば区切りを設けられるかがわかりませんでした。
いっときは、本当に配列数式を使わないと、これはさすがに無理かも……という気持ちになりましたし。

1日中こればかりやっているわけにもいかなかったので、結局、「元の文字列を部分的に置換すればよい」と思いつくまでに延べで4日ぐらい、時間にすると4、5時間かかりました。

取っ掛かりさへつかめてしまえば、あとは早かったです。処理の一つひとつを行っているのは、普段から使い慣れている関数ですから。
(画像をつくるほうが時間がかかったぐらいです。)

最終的に、誰にでも任せてやってもらえる仕組みになったと思います。

冒頭のリンク先で、「作業列をどう使うかを考えるのに、それなりの時間はかかるでしょう」と述べたのはこういうことです。アイデアを思いつくまでに時間がかかることがあるんです。

そして、「ややこしい配列数式を頑張って理解して使うことに時間をかけるよりは、作業列をどう使うかを考えるのに時間をかけたほうが、結局は業務全体の効率を上げることにつながる」――という思いも変わっていません。

ちなみに、今回は課題に入っていませんが、数字が英字(あるいは、英字が数字に)挟まっている場合もあるかと思います。abc456defg とか、987WXY54 とか。

そういう場合も、上記と同様の考え方で処理できます。
長くなったので、それについては次の記事で。

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

Excel で別の文字列をセル内改行で付け加えて表示させる。

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

■数式で処理する。18年7月9日追記

T.N さん。

先に、ユーザー定義書式を使えばできると書きましたが、数式で処理できます。

「仕事で使うファイルは、できるかぎり誰もがわかりやすく作っておく」という大原則からすると、数式を使ったやり方のほうが、ぱっと見て何をしているかの理解しやすさが少しでも高いと思います。

="会長"&CHAR(10)&VLOOKUP(D5,A2:B3,2,FALSE)

といった数式になります。
セルの書式設定で「配置」タブを開き、「折り返して全体を表示する」にチェックしておくのは同じです。(なお、数式でのやり方のほうも■注意の内容は同じです。)

Hyoujikeishiki_cellnaikaigyou08

CHAR 関数は、使っているコンピュータの文字セットから、引数で指定した番号の文字を返します。

Widowsでの CHAR(10) は、改行コードを返します。

数式を見ると、「文字列・CHAR関数の戻り値・VLOOKUP関数の戻り値の3つのものを、& で結合している」とわかります。

先のやり方だと、「ユーザー定義書式」を知っていて、さらに Ctr + J のショートカットを知っていなければ、何をしているのかを理解できません。

消さなければならないものではないので、前の記事は以下に残しておきますが、誰にでもわかりやすくしておくには、この場合は数式を使うやり方を採ったほうがよいと思います。


T.N さん。

数式で文字列を抜き出してきているセルに、別の文字列をセル内改行の形で付け加えて表示させるやり方がわかりました。ユーザー定義書式を使ってできます。

Hyoujikeishiki_cellnaikaigyou01

1. セルの書式設定で「配置」タブを開き、「折り返して全体を表示する」にチェック
ここのチェックを必ず入れること。

2. 「表示形式」タブで「ユーザー定義」を選択。

3. 「種類」の欄が「G/標準」になっているのを消して、「@」を入力

4. 「@」の左右に表示したい文字列を入力

Hyoujikeishiki_cellnaikaigyou02

5. 入力した文字列と「@」の間にカーソルを置いて、Ctrl+J を押す。
Ctrl+J は改行コードを入れるショートカット。

6. カーソルを置いた位置に改行が入る。
改行マークなどの表示は何も見えない。

Hyoujikeishiki_cellnaikaigyou03

7. 「OK」をクリックすると、手順 4. で入力した文字列がセル内改行で表示される。
(画像では見やすいように中央揃えにしてあります。)

Hyoujikeishiki_cellnaikaigyou01_2


■注意

セルを F2 やダブルクリックで編集モードにしてから Enter キーを押すと、数式が表示されてしまう。

Hyoujikeishiki_cellnaikaigyou04

1. 表示形式で「標準」をいったん選んで OK をクリック。
セル内改行で表示させていた文字列が消えて数式だけが表示される。

Hyoujikeishiki_cellnaikaigyou05

2. セルを編集モードにして Enter キーを押す。
数式が返してくる文字列だけが表示される。

Hyoujikeishiki_cellnaikaigyou06

3. あらためてユーザー定義書式から、セル内改行で表示させる文字列を伴った書式を選ぶ。

Hyoujikeishiki_cellnaikaigyou07

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

Excel で2つのシートでセルの値を比較して検算する。

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

N.T さん。

2つのシートでセルの値を比較して検算するプロシージャです。

■課題

  • 1つのブックのなかで隣り合っている2つのシートにそれぞれ表が1つ載っている。それら2つの表の値が全く同じであるかを比較して検算する作業を楽にしたい。
  • ブックのなかのシートは3枚以上になることもある。
  • G列3行目のセルから比較していく。
  • 表の行数・列数はそのときどきで変わる。
  • どのセルの値が違っているかを、ぱっと見てわかるようにしたい。

隣り合っている2枚のシートということでしたので、右側のシートのさらに右に検算用のシートを挿入して処理するようにしました。
(このコードでは、離れたシート同士を比較することはできません)

2つの表で値が異なっているセルがあったら、検算用のシートで該当するセルを赤く塗るようにしました。

最後に、できあがった検算用シートの表示倍率を20%にしてあります。
もらったサンプルがけっこう大きな表だったので、20%で全体を眺めることができたもので。そこに赤いセルがなかったら、2つのシートにある表は全く同じということです。
(表示倍率を変えたいときは、最後の ActiveWindow.Zoom = 20 の行で 20 を変えてください。)

検算用のシートのシート名は、「検算用_左端のシートから数えた枚数」といった書式になります。
シートが2枚だったら、「検算用_3」というシートが右端にできます。

シートが3枚以上になることもあるということですが、比較したいシートをどうやって扱えば比較できるか、私のスキルではわかりませんでした。

幸い、ブックが出来上がった時点では、比較したいシート同士は隣り合って並んでいるということだったので、「比較するシートを左右に並べて、右側のシートを開いていることを確認してからこのプロシージャーを実行する。」ということにさせてください。

スキル不足ですみません。

G列3行目のセルから比較していくとのことでしたので、FOR...NEXTステートメントの繰り返しの初期値は、7 と 3 になっています。
コメントの中にも書いていますが、比較を始めたいセルの位置が変わったら、その部分を合わせて変えてください。

また、表の列数と行数がその都度変わってくるということで、表の最大列数と、最大行数を、それぞれ Cells(5, Columns.Count).End(xlToLeft).Column と Cells(Rows.Count, "G").End(xlUp).Row の部分で取得しています。

Cells(5, Columns.Count) としたのは、値の抜けがないセルが行の右端まで続いているのは5行目以降だったからです。
5 を選んだ理由は特にありません。一番上の行なので 5 としたまでです。

同じ理由で、Cells(Rows.Count, "G").End(xlUp).Row としたのは、空欄でないセルが並んでいるG列を基準にしたかったからです。
空欄でないセルが並んでいれば、どの列でもよいのですが、G列のセルから比較を始めるので、G列を基準にしておくのが自然な書き方かと思います。


Sub 二つシートでセルの値を比較して検算する()
'
'比較する二つのシートが左右に並んでいて、
'右側のシートを開いていることを確認してからこのプロシージャーを実行する。
'
    Dim i As Long
    Dim j As Long
    Dim 検算用シートの位置 As Long

    ActiveSheet.Copy After:=ActiveSheet
    検算用シートの位置 = ActiveSheet.Index '■1

    '■2
    For i = 7 To Worksheets(検算用シートの位置 - 2).Cells(5, Columns.Count).End(xlToLeft).Column
        For j = 3 To Worksheets(検算用シートの位置 - 2).Cells(Rows.Count, "G").End(xlUp).Row
            If Worksheets(検算用シートの位置 - 2).Cells(j, i).Value <> Worksheets(検算用シートの位置 - 1).Cells(j, i).Value Then
                Worksheets(検算用シートの位置).Cells(j, i).Interior.Color = 255
            End If
        Next j
    Next i

    '■3
    With Worksheets(検算用シートの位置)
        .Name = "検算用シート_" & 検算用シートの位置
        .Range("A1").Select
    End With

    ActiveWindow.Zoom = 20
'
'比較する二つのシートが左右に並んでいて、
'右側のシートを開いている状態からこのプロシージャーを実行しているので、
'右側のシートをさらに右にコピーして、検算用のシートとして挿入する。
'この状態で、二つのシートと検算用シートは、セルの値がまったく同じであるはず。
'それを確認するために表の値を比較していく。
'
'■1
'挿入された検算用シートが、一番左端のシートから何枚目にあるかを、整数で取得し、変数 検算用シートの位置 に代入。
'取得した値は、検算用シートの左にある二つのシートの位置を指定するのに使う。
'
'■2
'検算用シートの位置から 2 を引くと、左に2枚目のシートを指定できる。
'1 を引くと左に1枚目(すぐ左となり)のシートを指定できる。
'
'Columnプロパティで、検算用シートから左に2枚目のシート上にある表が何列あるかを取得して、変数 i に代入する値の最終値として使っている。
'表が何列あっても対応できるようにしている。
'G列のセルから値の比較を始めたいので、変数 i の初期値は 7 にしてある。比較を始めたい列が変わったら、7 の部分をそれに合わせる。(G列は7列目)
'列数を数える基準となるセルの行が変わったら、Cells(5, Columns.Count) の 5 の部分をそれに合わせる。
'
'Rowプロパティで、検算用シートから左に2枚目のシート上にある表が何行あるかを取得して、変数 j に代入する値の最終値として使っている。
'表が何行あっても対応できるようにしている。
'3行目のセルから比較を始めたいので、変数 j の初期値を 3 にしてある。比較を始めたい行が変わったら、3 の部分をそれに合わせる。
'行数を数える基準となるセルの列が変わったら、 Cells(Rows.Count, "G") の "G" の部分をそれに合わせる。
'
'検算用シートから左に2枚目のシートと1枚目のシートのセルの値を比較して、同じでなければ、検算用シートの該当セルを赤く塗る。
'セルを赤くすることで、二つのシートの同じセルで値が異なっている(どちらかのシートでセルの値が間違っている)ことがわかるようにしている。
'
'■3
'二つのシートのセルを比較する検算の繰り返しが終わったら、
'検算用シートのシート名を「検算用シート_検算用シートの位置」に変える。
'「検算用シート_3」というようなシート名になる。
'
'検算用シートの全体が見えるように表示倍率を 20% にする。
'シート全体を見渡せるようになるので、赤いセルがあったらわかる。
'
End Sub

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

Excel でワークシートのヘッダーやフッターを一括で置換する。

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

■ブック内の全てのワークシートのヘッダーやフッターを、一括で全く同じ(表記も、入れる位置も全部同じ)にする。

VBA を使わないで可能。

1.シート名の上で右クリックして「すべてのシートを選択」をクリック
2.「ページレイアウト」タブからヘッダーとフッターを任意に設定

これで、全てのワークシートのヘッダーやフッターが同じになる。

■VBAで処理する。

ワークシート関数の SUBSTITUE 関数を使うプロシージャーの例

Sub ヘッダーとフッターの置換_1()
'ワークシート関数の SUBSTITUTE 関数を呼び出して使う。
    Dim i As Long
    Dim 置換前 As String, 置換後 As String '■1

    置換前 = Application.InputBox(Prompt:="置換前の文字列", Default:="置換前の文字列を入力", Type:=2) '■2
    If 置換前 = "False" Then Exit Sub '■3

    置換後 = Application.InputBox(Prompt:="置換後の文字列", Default:="置換後の文字列を入力", Type:=2) '■2
    If 置換後 = "False" Then Exit Sub '■3

    For i = 1 To Worksheets.Count '■4
        With Worksheets(i).PageSetup
            '■5
            If .LeftHeader <> "" Then .LeftHeader = WorksheetFunction.Substitute(.LeftHeader, 置換前, 置換後)

            If .CenterHeader <> "" Then .CenterHeader = WorksheetFunction.Substitute(.CenterHeader, 置換前, 置換後)

            If .RightHeader <> "" Then .RightHeader = WorksheetFunction.Substitute(.RightHeader, 置換前, 置換後)

            If .LeftFooter <> "" Then .LeftFooter = WorksheetFunction.Substitute(.LeftFooter, 置換前, 置換後)

            If .CenterFooter <> "" Then .CenterFooter = WorksheetFunction.Substitute(.CenterFooter, 置換前, 置換後)

            If .RightFooter <> "" Then .RightFooter = WorksheetFunction.Substitute(.RightFooter, 置換前, 置換後)

        End With
    Next
'
'■1
'変数「置換前」と「置換後」を String(文字列型)として宣言。
'
'■2
'Excel VBAで使える InputBox には、InputBoxメソッドとInpuBox関数がある。
'ここでは、ApplicationオブジェクトのInputBoxメソッドを使っている。
'InputBoxメソッドでは引数Typeを使える。InputBox関数では引数Typeを指定できない。
'Type:=2 の指定で、文字列のみを受け取り、返すことができる。
'ヘッダーやフッターの値を置換するので、置換の対象は常に文字列。
'(そういう意味で、変数の宣言でも、変数「置換前」と「置換後」を文字列型(Strings)として宣言している。)
'
'InputBox で受け取る値を使って数学的な計算をするわけではないので、Type:=2 の指定で文字列を受け取る指定をしておけば、
' 第4期全体会合 といったような、文字と数字が混ざっている値でも受け取れるし、
' 2018 といった純粋な数字の場合でも、どちらでも受け取れる。
'
'■3
'InputBoxメソッドの「キャンセル」がクリックされたら、このプロシージャ―を抜ける。
'InputBoxメソッドは、「キャンセル」がクリックされたら、 False(論理値) を返す。
'文字列を受け取れるように、引数Typeに「2」を指定しているので、InputBoxメソッドが返してくる False も文字列型になる。
'そこで、IFステートメントでは、FALSE をダブルコーテーションで囲んで、文字列として扱っている。
'このコードで、False をダブルコーテーションで囲っていなくて、InputoBoxメソッドの入力欄に文字列(純粋な数字ではないもの)を入力すると、
'「型が一致しません」というエラーになる。
'
'■4
'Worksheets.Count で、ブック内にワークシートが何枚あるかを取得。
'ワークシートが何枚あっても、左端から右端まで全ワークシートを対象に処理を行うために使う。
'
'■5
'ワークシート関数の SUBSTITUE 関数を呼び出して使っている。
'If ステートメントで、ヘッダー(左・中央・右)やフッター(左・中央・右)に何か入っていれば、(空欄でなければ)置換を行うようにしている。
'ヘッダーやフッターに何も入っていなければ、(空欄なら)置換の処理を始めない。
'
'同様の処理を行うコードで、With ステートメントだけを使った下記のような書き方がある。
'
'For i = 1 To Worksheets.Count
'   With Worksheets(i).PageSetup
'       .LeftHeader = WorksheetFunction.Substitute(.LeftHeader, 置換前, 置換後)
'       .CenterHeader = WorksheetFunction.Substitute(.CenterHeader, 置換前, 置換後)
'       .RightHeader = WorksheetFunction.Substitute(.RightHeader, 置換前, 置換後)
'       .LeftFooter = WorksheetFunction.Substitute(.LeftFooter, 置換前, 置換後)
'       .CenterFooter = WorksheetFunction.Substitute(.CenterFooter, 置換前, 置換後)
'       .RightFooter = WorksheetFunction.Substitute(.RightFooter, 置換前, 置換後)
'   End With
'Next
'
'上記のコードの場合、ヘッダー(左・中央・右)とフッター(左・中央・右)に
'何か値が入っていても、入ってなくても、一つのシートにつき6か所で置換を行おうとする。
'値が入っていない場合、しなくてもよい作業をしていることになる。
'
'If .LeftHeader <> "" Then .LeftHeader = WorksheetFunction.Substitute(.LeftHeader, 置換前, 置換後)
'
'のように書くことで、左・中央・右のヘッダーやフッターに何か入っている(空欄ではない)場合にだけ置換を始める。
'これによって、処理にかかる時間を With ステートメントだけを使うより短くしている。
'
End Sub


全てのワークシート関数を呼び出せるわけではない。

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

Excel で漢字の読みを入力するマクロ

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

VBA を使って、B 列に入っている漢字の読みを C 列に入力する。

セル内の漢字の読みは PHONETIC 関数で取得できるが、セルの中で読みを打って漢字に変換していないとその読みは  PHONETIC 関数で取得できない。(別のファイルからコピー貼り付けしてきた場合など。)

下記のコードは、セルの中で変換していなくても漢字の読みを取得する。
(コードを実行する場合は、読みを入力したいシートを開いておく。)

ただし、エクセルが漢字を認識するので、期待通りの読みにならない場合がある。その場合は、手作業で読みの編集が必要。
PHONETIC 関数とは違って、漢字の読み( Shift + Alt + ↑)を正しく入れても反映はされない。

Yomi_1

コードを実行した結果。

Yomi_2


Sub 漢字の読みを入力()
Dim i As Long, 文字列 As String
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    文字列 = Cells(i, "B")
    Cells(i, "C") = Application.GetPhonetic(文字列)
Next i
'B 列に入っている文字列のヨミを C 列に入力する。
'
'For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row のコードで、
'B 列が何行あっても対応できるようにしている。
'
'B 列の値を、変数「文字列」に代入する。
'変数「文字列」を、Application オブジェクトの GetPhonetic メソッドの引数にする。
'C列のセルに GetPhonetic メソッドの実行結果を入れていく。
'
'B 列に入っている漢字の読みを、エクセルが内部的にもっている辞書と照らし合わせて認識する。
'そのため、人が期待する通りに漢字を読んでくれない場合がある。
'その場合は、手作業での調整が必要。
'
End Sub

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

より以前の記事一覧