配列数式は使わない。作業セルを使う。

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

H.K さん

配列数式の件ですが、タイトル通りです。

誤解のないように付け加えておくと「我々の業務上では」という話です。
「配列数式そのものがだめ」だとか、単に「使ってはいけない」という意味ではありません。

我々の業務で配列数式をなぜ使ってはいけないのか。

周囲の誰も配列数式について知らないからです。
おそらく「配列数式」という言葉を知っているのが、私と H.K さんだけだと思います。

仕事でつくるファイルは、誰のものでもなくて皆のものです。
ほかの人に引き継ぐかもしれませんし、「先月つくったのが役立つから、今回使えるよ」というように既存のファイルを流用することだってあるでしょう。

そんなときに、誰も知らない配列数式なんてものを使ったファイルを渡されても困ることのほうが多いでしょう。つくった本人が、異動や退職などでいなかったら尋ねることもできません。

H.K さんと同等かそれ以上のスキルと知識をもった人が仕事を引き継ぐとは限りません。
引き継ぐ人が「関数ってよくわからないので、これから勉強します」というような人かもしれないじゃないですか。

仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにつくっておくべきです。

例外があるとしたら、職場の誰もが、そして今後入社してくる誰もが、配列数式を十分に理解している場合でしょうか。
そんな職場だったら、配列数式を使っても何の問題もないと思います。誰もが理解しているのですから。

そもそも、我々の業務で「配列数式を使わなければならない具体的な理由」なんてよほどのことがない限り存在しないでしょう。たいていのことは使わなくてもできます。

ネットで次のような記事を見つけました。

Excelの配列数式で合計を一発で計算する方法 (@IT - アットマーク・アイティ)

リンク先では、

「配列数式」と呼ばれる式を使うと、小計を計算せずに、1つの式で簡単に合計を計算できる。
(出典:@IT - アットマーク・アイティ)

と書いてありますが、H.K さんや周囲の人にとって、リンク先の記事の内容は "1つの式で簡単に合計を計算できる" と断言できるほど簡単ですか?

たぶん理解するのにちょっと苦労すると思います。

リンク先の例では、確かに1つの式で答えが得られています。だから、「1つの式で簡単に合計を計算できる」と書いてあるだけのことです。
配列数式というものは、誰にでも簡単に理解できるもので、簡単に使えるものだという意味ではありません。

また、リンク先でいう "行が多い表となると結構面倒な作業" は、Excel2016では面倒ではありません。
ああいう形の表なら、セル D2 に式を入れてセルのフィルハンドルをダブルクリックすればいいだけです。何百何千、何万行あっても、一番下のセルまであっという間に式が入力されます。

リンク先であげている「1行おきの合計」という例も、配列数式の解説記事なので配列数式を使うように書いているだけです。
SUMIF関数を使えばできることです。

配列数式とSUMIF関数――どちらが理解しやすいですか?
私は、SUMIF関数のほうが理解しやすいです。仕事で同じことをするのならSUMIF関数を使います。

使わなければならない具体的な理由が無いものは、仕事では使わないほうがよいということです。

作業セル(作業列)は使う。

リンク先では「MOD関数を使って行番号の偶数奇数を求めて、それを各行を判別する材料にするやり方」を挙げています。それはそれとして覚えておいて損はないです。

もし、MOD関数を知らなくて同じことをしたい場合はどうしたらよいでしょう?

各行を区別できるように何らかの印をつけてやって、それを判別材料に使えば、SUMIF関数で対応できます。
「何らかの印をつける」ための列を追加してやればいいんです。つまり、作業列(セル)です。

作業列を使うことを避けないでください。作業列を使って困ることはありませんが、使わないで苦労することはあります。

「作業列を使うとどんどん列が増えていって見づらくなるので使わない」と主張する人がいます。
あるいは、「1つのセルのなかで配列数式を使えば、作業セルを使わないで済む」というものもあります。

私に言わせれば、そもそも考え方が間違っています。

作業セルを使うことと配列数式を使うこと。どちらがより簡単でしょうか。どちらが皆にとって理解しやすいでしょうか。
作業列のほうではないですか。より簡単にできる(皆が理解しやすい)ほうを採ったほうがよいでしょう。

「どんどん列が増えていって見づらい」なら、列を非表示にすればいいだけです。

「作業セルを使わないで済むので、(より難しい)配列数式を使う」のではなくて、「(より難しい)配列数式を使うよりは、(より簡単な)作業セルを使う」ほうが、仕事としては正しいと思います。

1つのセルで答えを得ることにこだわらないでください。
そのことに固執すると1つのセルにやたら長い数式を入れがちです。そんな数式は、関数に不慣れな人には荷が重いです。
作業列で、計算の過程をいくつかにわけてやれば、全体として何をしているのかが誰にでも理解しやすくなります。

おそらく、「作業列を使うとどんどん列が増えていって見づらくなるので使わない」と主張する人は、やりたいことができあがった最終形としての表を強くイメージしているのだと思います。
作業列を増やすごとに、そのイメージから離れていくので列が増えることを嫌うのでしょう。

見た目で列が増えることにどうしても支障があるなら、あるシートで作業列を使った計算をしておいて、最終形としてのレイアウトを作った別のシートから、そのシートを参照してやればいいんです。

そうすれば、計算の過程をわかりやすくしておくことと、必要な列だけが表示されている状態を同時につくれます。

作業列を使って値を抜き出す例

英字と数字がハイフォンで区切られていて、数字のところだけを抜き出す例です。

英字と数字がハイホンで区切られていて、数字のところだけを抜き出す例1

"-" (ハイフォン)が、文字列のなかで左から何文字目にあるかわかれば、その1つ右隣から数字を抜き出せます。

例では、FIND関数でハイフォンの位置をまず求めています。
その位置の1つ右隣から数字が始まっているので、MID関数のなかでは、ハイフォンの位置に 1 を足すことで「抜き出し始める位置」を指定しています。

英字と数字がハイホンで区切られていて、数字のところだけを抜き出す例2

MID関数の 100 という引数は適当に入れています。

「(抜き出し始める位置から)何文字を抜き出すか」の指定ですから、ありえないぐらい多めに指定しておけばいいだけです。

LEN関数と組み合わせて元の文字列の字数を抜き出す文字数として使う方法がありますが、「なるべく簡単にする」という方針でやりたかったので図のようにしています。

このように作業列で計算の過程を分けると、全体として何をしているのかが理解しやすいと思います。

これでもう「数字のところだけ抜き出せている」ので作業は終わっています。ここからは「得たい見た目を作る」話です。

英字と数字がハイホンで区切られていて、数字のところだけを抜き出す例3

ここでは表の左端に「ID」という作業列を追加しました。

英字と数字がハイホンで区切られていて、数字のところだけを抜き出す例4

ID で各行を区別できるので、別のシートでVLOOKUP関数の引数に使っています。
これで最終形として得たいレイアウトになります。

英字と数字がハイホンで区切られていて、数字のところだけを抜き出す例5

作業列が増えていっても、こんなふうに考えれば「得たい見た目」をつくれます。

ちょっと話がそれました。

大事なことは、使うべき具体的理由がないのに配列数式をわざわざ使おうと頑張るのではなく、作業列(セル)を使って処理するやり方を頑張って考えてください、ということです。

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

追伸:
最後の例は、VLOOKUP関数なんか使わなくても単にセル参照すればいいですね。
言いたかったことは「作業列を活用すれば、異なるレイアウトに値を表示できる」ということなんですが、その例としては失敗してます。

これでは、「作業列なんて不要」という話になりそうです。説得力のない例ですみません。

とはいえ、作業列の価値と私の失敗は何にも関係がありません。作業列をぜひ活用してください。

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

日付が土日、祝日・休日の場合にセルに色を付ける。(Googleスプレッドシート編)

この記事は自分用の覚書です。
Windows 8.1 Pro 64ビット + Google Chrome バージョン: 62.0.3202.94(Official Build) (64 ビット)

土・日の場合にセルに色を付ける。

  1. (日付がA1から入っている場合)A1から色を付けたいセル範囲を選択
  2. 選択状態の上で右クリックして「条件付き書式」を選択
  3. 「範囲に適用」の欄で、選択したセル範囲が指定されていることを確認
  4. 「セルの書式設定の条件」の欄で「カスタム数式」を選択
  5. 「値または数式」の欄に土曜日に対応した数式を入力
  6. 任意の色を指定して「完了」をクリック
    土曜日に応じたセルに色がつく。
  7. 「新しい条件を追加」をクリック
    同様に、日曜日に対応した数式を入力して「完了」をクリック。
    これで土日に応じたセルに色がつく。

土日に応じたセルに色付け

・土曜日に対応した数式(日付がセル A1 に入っている場合)
必要に応じてセル参照の絶対参照・相対参照を変える。
この例の場合は、1行目に日付を入れているので、1行目を絶対参照にしている。

土曜日への対応

・日曜日に対応した数式
=weekday(A$1)=1

日曜日への対応

ほかのシートに作った祝日・休日(振替休日)のリストを参照する。

祝日・休日に応じてセルに色を塗るには、祝日・休日のリストが必要。

2017年12月時点で、Google スプレッドシートでは条件付き書式のカスタム数式内で直接ほかのシートを参照することができないので、リストに名前を付けて、それをindirect関数でセル範囲に変換してやる。

祝日・休日のセル範囲に名前を付ける。
  1. シートを追加して祝日・休日の日付が入ったリストを作る。
  2. 日付を入れた範囲を選択して右クリック
    「憲法記念日」や「こどもの日」など、祝日・休日の名称も合わせて入力している場合は、名称の範囲は選択しない。日付が入っている範囲のみを選択する。
  3. 「名前付き範囲を定義」を選択
  4. 「名前付けされた範囲」に名前を入力し、選択している範囲を確認して「完了」をクリック
    ここでは、シート9の A2 から A21 のセル範囲に「平成30年の祝日・休日」という名前を付けている。

日付の範囲に名前を付ける

ほかのシートで名前付き範囲を参照して、祝日・休日の場合にセルに色を付ける。

先に土日の場合にセルに色付けした範囲を選択して、条件付き書式のカスタム数式に次を入力。

=countif(indirect("平成30年の祝日・休日"),A$1)=1

・日付が入ったセル範囲のみを選択して名前を付ける。

日付が入ったセル範囲のみを選択して名前を付ける。

・平成30年の祝日・休日への対応

平成30年の祝日・休日への対応

・土日、祝日・休日に応じてセル範囲に色が付く。

土日、祝日・休日に応じてセルに色付け

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

Chrome で、選択した部分のソースを表示する。

この記事は自分用の覚書です。
Windows 8.1 Pro 64ビット + Google Chrome バージョン: 62.0.3202.94(Official Build) (64 ビット)

  1. Webページで任意の部分を選択
  2. 選択した部分の上で右クリックして「検証」を選択
  3. 「デベロッパーツール」が Chrome の下部に表示されて、該当するソース(タグ)が選ばれている。
  4. ソースを確認したら「デベロッパーツール」の右端にある×印で閉じる。
    ショートカットは、 Ctrl + Shift + I

選択した部分のタグによっては、全体が表示されない場合がある。

「デベロッパーツール」のなかで、

►<p>...</p>

といったような表示になっていたら、左の ► をクリックすると展開した表示になる。

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

Excel2016 でファイルの一覧を作る。

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

1.ワークシートを開く。(新規でも既存のものでもよい。)

2.「データ」タブ-「データの取得」-「ファイルから」-「フォルダから」をクリック

3.「フォルダーパス」を入力する画面になるので「参照」をクリックして、ファイルの一覧を作りたいフォルダを選んで、OKをクリック

4.「フォルダーパス」の欄で目的のフォルダが指定されていることを確認して、OKをクリック

5.プレビュー画面が表示されるので「読み込み」をクリック

6.テーブル形式でファイル名の一覧が作成される。
3. で選んだフォルダの下のフォルダにもエクセルのファイルがある場合、それも一覧に含まれる。「上のフォルダでだけ一覧を作る(フォルダの下のフォルダは対象にしない)」という設定はない。

一度一覧を作ると、該当のフォルダのなかで新規作成・ファイル名の変更・あとから保存したファイルも、「クエリ」タブの「更新」を実行することでファイル名を追記・変更できる。
また、上記3.と4.で指定したフォルダのなかであれば、新たにフォルダを作ってファイルを移動させても「更新」でファイル名の追記・変更に対応できる。

「データ」タブ-「クエリと接続」グループ-「プロパティ」-「クエリ プロパティ」ボタンで、「定期的に更新する」を使うと、1分刻みに更新の間隔を指定することができる。
最低「1分」から最大「32767分」。

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

Excel とメモ帳で、ダブルコーテーションで値を囲った CSV ファイルを作る。

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

■元の表

Dq_kakomu1

■考え方

最後に、ダブルコーテーションで値を囲った CSV ファイルになればよい。
値の左右に、何か目印になるものを付け加えて CSV ファイルを作り、あとで目印を置換して、ダブルコーテーションで値が囲まれているようにする。

■作業の手順

1.元の表とは別の列で、生年月日のセルを TEXT 関数で文字列に変換する。
1967/12/12 2のように / で区切った数字は、「日付」として扱われている(エクセルの内部で「シリアル値」という数字として扱われている。)ので、文字列に変換しておくことが必要。
・例
=TEXT(B2,"yyyy/mm/dd")

Dq_kakomu2

2.変換した文字列のセルを、生年月日の列へ「値貼り付け」する。
貼り付け終わったら、文字列に変換する式を入れたほうの列は不要なので削除。
(そのままにしておいてもよい。)

Dq_kakomu3

4.元の表とは別のセルに、 " (ダブルコーテーション)を入れて、そのセルを値の左右に結合する式を入れる。ダブルコーテーションを入れたセルは絶対参照にしておく。
・例
=$E$1&A1&$E$1

5.式をほかのセルにコピーして表を完成させる。

Dq_kakomu4

7.完成した表を選択してコピーする。

8.Ctrl + N で新しいワークシートを起こして、「値貼り付け」する。

Dq_kakomu5

9.値貼り付けしたワークシートを CSV で保存する。

8.メモ帳で CSV を開く。
"""氏名""" のように値の左右を3つのダブルコーテーションが囲っている。

Okikae0

9.置換で、検索する文字列に "" (2つのダブルコーテーション)を入力。

10.置換後の文字列の欄は空欄にしておく。
左右にある3つのうち2つを消せば、ダブルコーテーションを左右に1つずつ残せる。

Okikae1

11.「すべて置換」をクリック

Okikae2

13.メモ帳を上書き保存する。
これで、ダブルコーテーションで値を囲った CSV ファイルが完成。

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

«わかる人にだけわかる話