エクセル

Excel で「うるう年」を見つける。

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

■考え方
ある年の2月末日が 29 日なら、その年は「うるう年」である。

月末の日にちとは、「翌月の一日(ついたち)の前日」といえる。
3月1日の1日前が2月29日であるかどうかを調べれば、その年がうるう年かどうかがわかる。

数式でいえば、ある年の3月1日(シリアル値)-1 の答えが2月29日であれば「うるう年」。

■処理
日にちの処理には、DATE 関数や DAY 関数など(場合によって、YEAR 関数や MONTH 関数も)を使う。

一つのセルの中でこれらの関数をネストさせて数式を作ると、パッと見て何をしているのかが理解しづらい数式になる。

作業列を使って計算の過程を分けることで、全体として何をしているのかをわかりやすくできる。

1.西暦(数字4桁)を入力する。
ある日にちから YEAR 関数で西暦を抜き出すやり方もある。

2.2月末日(シリアル値)をつくる。
1. の値を DATE 関数の引数に使い、3月1日(シリアル値)をつくって、 1 を引く。

3.DAY 関数で、2月末日の「日」の値を抜き出す。
2.を DAY 関数の引数にする。

4.IFS 関数(あるいは IF 関数)で、3. が 29 であれば「うるう年」とする式をつくる。

Uruudosi_hantei_2

=IFS(C22=29,"うるう年",28,"")

の式は書き方としては、

=IFS(C22=29,"うるう年",TRUE,"")

でも結果は同じになる。
前述の条件を満たさないときは TRUE が成り立つとしてセルが非表示になる。

ただし、この場合は2月末日を判定しているので、条件としては「29である」か「28である」の二通りしかない。
答えが二通りしかないのであれば、

・「29であれば「うるう年」と表示する」、「それ以外であればセルを非表示にする」

と記すよりは、

・「29であれば「うるう年」と表示する」、「28であればセルを非表示にする」

と書いたほうが、どんな条件にもとづいて処理をしているのかがより具体的で理解しやすくなると考えてそうしてある。

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

Excel で行高を固定して入力する。

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

■やりたいこと
セルの書式を「折り返して全体を表示する」にしてあっても、値の貼り付けや入力時に行高が高く変わらないようにする。

■手順
値貼り付けや入力をする前に「行の高さ」を指定しておく。

行を右クリックして「行の高さ」を選択し、固定したい行の高さを入力する。
標準では 18 になっている。同じ 18 で固定したい場合は、あらためて 18 を入力する。

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

Excel2016 でユーザー設定リストへの登録

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

■現象
『ユーザー設定リスト』の「リスト項目」欄に、登録したいリストをコピー貼り付けして「追加」ボタンをクリックすると、次の警告メッセージが表示されてリストを全部登録できない。

「ユーザー設定リストの最大長を超えています。最初の 255 文字のみ保存されます。」

■対処方法
・「リスト項目」欄にコピー貼り付けしない。
・「追加」ボタンをクリックしない。

1. ワークシート上で任意の「列」にリスト項目を用意しておく。
「行」(横)に並べると登録できない。

2. 1. を範囲選択しておいてから『ユーザー設定リスト』の画面を呼び出すか、『ユーザー設定リスト』の画面で「インポート」ボタンの左にあるボタンを押して 1.  の範囲を指定する。

3. 「リストの取り込み元範囲」が正しいことを確認して「インポート」ボタンをクリック。
これでユーザー設定リストが登録される。

OK ボタンをクリックしていって画面を閉じる。

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

Excel で満年齢を計算する。(DATEDIF関数は使わない)

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

「今年いくつになるか?」という「満年齢」(生まれた年を 0 として、以後の年の誕生日がきたら 1つ 増える年齢)を求める。

1.YEAR 関数で生年月日から「年」を抜き出す。

2.YEAR 関数と TODAY 関数で今日の日付から「年」を抜き出す。

3. 2. から 1. を引く。これで「今年いくつになる」かがわかる。

満年齢を求める。

「いまいくつなのか」を求める。

本年の誕生日がきていない場合

満年齢から 1 を引く。(満年齢未満ということ。)

「本年の誕生日がきていない」とは、「今日の日付(シリアル値)」が「本年の誕生日(シリアル値)」より小さいということ。
IF 関数または IFS 関数で対応。

・DATE 関数で「本年の誕生日」を生成
DATE(E2,MONTH(C2),DAY(C2))

誕生日を過ぎている場合

「満年齢には達している」ので、そのまま「(翌年の誕生日まで)満年齢をいまの年齢」とするか、「(本年の)満年齢は超えた」と考えて 1 を足して「いまの年齢」とするかはケースバイケース。

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

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

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

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)

Excel2016(Office 365 サブスクリプション)にだけある IFS 関数

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2016 Office 365 Business サブスクリプション)

K.F さん。

お互い、Office 365 が入ってよかったですね。
Office 365 Business だと聞いています。

同じ Office2016 (Excel2016, Wrod 2016, Powerpoint2016 など)でも、単体で買った人と、Office 365 に加入してインストールした人とでは、使える機能が少し違ってきます。

Office 365 は、定期的に利用料金を支払ってエクセルなど使いますが、そういう使い方を「サブスクリプション」というそうです。

関数も、サブスクリプションの Excel2016 にだけ搭載されている関数があります。
(Office2016 や Excel2016 を単体で購入した人は使えない関数です。)

その一つが IFS 関数です。

IFS 関数 (IFS 関数 - Office サポート)

IFS 関数を使ったファイルを、Excel2013 で開いてみました。
数式の前に _xlfn. という文字が付いて #NAME? というエラーが起きます。サブスクリプションではない Excel2016 でも同じかと思います。

自分以外の人にファイルを渡すときは注意が必要です。
先方がサブスクリプションの Excel2016 なのかどうかわからない場合は、IFS 関数は使わないでおくのが無難でしょうね。

■IFS 関数の利用について

IFS 関数を使うと「関数をネストする」必要がないので、見やすくて何をしているかの理解がしやすい式を書けます。
たとえば、 IF 関数で

  • セル A1 が空欄なら何も表示しない。
  • 76以上ならSを表示する。
  • 61以上76未満ならA
  • 46以上61未満ならB
  • 31以上46未満ならC
  • 0以上31未満ならD

を表示する。という式を書くと、

If

になります。

IFS 関数だとこうです。

Ifs

IF 関数よりもずっと見やすく、理解しやすいですよね。

■式の中でも Alt+Enter でセル内改行ができる。

ちなみに、僕もひとから教えてもらったのですが、式の中でも Alt+Enter のセル内改行ができます。
文字で説明するとなんのことだかわからないと思いますが、こういうことです。

Siki_no_nakade_kaigyo_ifs

2行目以降の A1 の左端はスペースを入れて頭をそろえています。
これでもなんのエラーも起こさないで答えが出ます。

こういう形にしておくと、何をしている式なのかがますます読み取りやすくなります。

IF関数だとこうです。

Siki_no_nakade_kaigyo_if

式の中での改行は、サブスクリプションではない Excel2016 はもちろん、 Excel2013 でも可能です。

あとから自分が見る場合にもわかりやすいです。自分以外の人もそうでしょう。業務で使うファイル(数式、VBA コード)は、自分以外の人が見てもわかりやすく作っておくことが大切です。

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

Excel でフォルダを作る。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

K.K さん。

Excel でフォルダを作るマクロです。

  1. フォルダ名の頭に 00 や 01 という番号をつけたい。
  2. 番号は連番とは限らない。
  3. 番号の横に任意のフォルダ名をつけたい。
  4. 多い場合は100個ほどのフォルダを作る場合もある。

ということでしたので、次の手順でお願いします。

番号から始まる文字列をワークシート上でつくってから、それをフォルダ名に使うという流れにしてみました。

■手順
1.任意のフォルダを作り、エクセルの新規作成したファイルを「名前を付けて保存」から「ファイルの種類」を「マクロ有効ブック」にしてそこへ保存。エクセルのファイル名は適当に。

2.保存したマクロ有効ブックを開く。

3.「開発」タブの「Visual Basic」をクリック
または、 Alt + F11 のショートカット。

4.Visual Basic Editor が起動してくるので「挿入」から「標準モジュール」を選択

5.下記のコードをコピー貼り付けして、いったん VBE は閉じる。

6.A2からセルを必要な数だけ選択し、セルの表示形式を「文字列」にする。
1行目は列見出しをつける想定。

7.00 や 01 から始まる番号を、A2 から入力。
コードのなかで、A列の最終行を取得しているので、A列は必ず入力を終えておく。

8.番号に続ける文字列を、B列に入力。

9.A列の番号とB列の文字列を、C列で結合する。(例 =A2&B2 )

10.今のシートを開いたままで、「開発」タブの「マクロ」をクリック

11.「フォルダの作成」が選択されていることを確認して「実行」をクリック。

12.エクセルのファイルを保存しているのと同じフォルダ内に、C列の値を使ったフォルダができる。
できたフォルダを切り取って、目的のフォルダ内に移動させる。

※付記
1.の時点で、既存のフォルダ内にファイルを保存しておけば、そのフォルダの直下に新しいフォルダを作ることができます。
ただ、パスの文字数が多すぎないかとか、フォルダ名には使えない文字を使っていないかといったチェックが、下記のコードではできていません。

いったんワークシート上でフォルダ名をつくってから、そのあたりのチェックをしておいた方が無難です。

いろいろ面倒ですみません。私の VBA のスキルの限界でして……。


Sub フォルダの作成()
    Dim i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        MkDir ThisWorkbook.Path & "\" & Cells(i, 3)
    Next i
'
'任意のフォルダを作り、このマクロを含むマクロ有効ブックをそこに保存しておく。
'
'開いているシートの2行目から値を入れていく。
'
'列見出しを使わずに1行目から値を入力する場合は、コードのなかの
' For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row の 2 を 1 にして、
' For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
'に変える。
'
'フォルダ名として使われるのは C 列に入っている文字列。
'マクロを実行すると、 C 列のセルにある文字列を名前としてフォルダが作られる。
'
End Sub

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

INDEX 関数の速さは、VLOOKUP 関数の約110倍

この記事は自分用の覚書です。
(Windows 10 Pro 64ビット + Excel2013 Home & Business)

K.Y さん。

このあいだ送った Excel のファイルで、「VLOOKUP 関数ではなくて、INDEX 関数なのはどうしてか」、「VLOOKUP 関数ではいけないのか」という件ですが、あの場合は、INDEX 関数でも VLOOKUP 関数でもどちらを使っても問題ないです。

どちらでもよかったのですが、一応、基本というか、定石というか、セオリー通りというか、原則というかで、INDEX 関数を使ってあります。

値を表引きしてくる速度は、VLOOKUP 関数よりも IDEX 関数のほうが速いんです。その速さはタイトルにある通り。

とはいえ、あの例では値を引っ張ってくる元になっているデータは、最大でも19件(行)しかありません。
19件程度では、人間が知覚できるような差は生じないので、どちらでもいいというわけです。

データの件数が大量になってくると、答えが出るまでの時間が違ってきます。
大量というのがどのくらいかというと、手元で試してみたら、9,000件超えてきたら見た目に「あれ?」と気づくぐらいの違いが出てきます。

1,000や2,000では、たいした差がありませんでした。

思い切って50万件で試してみました。
(時間がかかるだろうなと思っていたので昼飯を食べながら……)
試しに使った PC は以下のような機種です。
■計測に使ったPC
Windows 10 Pro 64bit
Intel(R) Core(TM) i5-4200U CPU @ 1.60GHz
メモリ4GB

試したやり方は以下のようです。
■INDEX 関数
A列とC列にあらかじめ 1 から 500,000 までを連番・昇順で入力。
セル D1 に INDEX 関数を使った次の数式を入力。

=INDEX($A1$:$A$500000,C1,1)

D1 のフィルハンドルをダブルクリックすることで、数式を D500000 までコピーし、すべてのセルに答えが表示されるまでの時間を計測。

約7秒。

■VLOOKUP 関数
A列、B列、D列にあらかじめ 1 から 500,000 までを連番・昇順で入力。
セル E1 に VLOOKUP 関数を使った次の数式を入力。

=VLOOKUP(D1,$A1$:$B$500000,2,FALSE)

E1 のフィルハンドルをダブルクリックすることで、数式を E500000 までコピーし、すべてのセルに答えが表示されるまでの時間を計測。

約13分。(約780秒)

INDEX 関数のほうが約110倍速いでしょ?
でもまあ、我々の日々の仕事でそんな大量のデータを扱うかというと――ないでしょうね。

というわけで、範囲の左端を目印にできるなら VLOOKUP 関数が使える。左端を目印にできないなら、INDEX 関数が使える。
このぐらいの認識でいいと思います。
(たまに勘違いして、 VLOOKUP 関数を使うときに、表の左端の列しか指定できないと思っている人がいますが、そんなことありません。選択した範囲の左端です。4列目から6列目まで選択したら、4列目が左端になります。)

ちなみに、1 から 500,000 まで連番で入れるやり方ですが、A1 に 1 を入れて、 A2 に 2 を入れて両方を選択してず~~っと下へ引っ張る――1分やってみました。5622までいきました――って、そんなやり方ではかなり暇がないとできないですよね。

「フィル」という機能の「連続データの作成」が便利です。

1.セル A1 に 1 を入力して選択しておく。

2.「ホーム」タブ-「編集」グループ-「フィル」-「連続データの作成」を選択。

Fill

3.「範囲」で「列」をクリック。

4.「種類」が「加算」になっていることを確認。

5.「増分値」には 1 を、「停止値」に 500000 を入れて、OK をクリック。

Renzokudata_2

これでA列に 1 から 500000 までの連番ができます。

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

より以前の記事一覧