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 で、あるセルの内容によって別のセルに色を付ける。

この記事は自分用の覚書です。
(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