CODE関数を賢く使う - taishiro1964の日記

OpenOfficeで名簿を管理したいとき、CODE関数を使うとイニシャルを基にして、グループ分けを行なうことができます。
ここでは、CODE関数と、CODE関数をさらに便利にする文字操作関数を紹介します。

関数名：CODE関数
書式：=CODE("文字列")　または、=CODE(セル番地)
何ができる：対象セルまたは文字列の一文字目の文字コードを返す
平たく書くと：=CODE("S")で83が返されます。

この関数をうまく使うと、名前のリスト管理が効率的にできます。
その一片を紹介します。

サンプルファイルは、SKYDRIVEからcalc140.odsをダウンロードしてください。

上の表では、59人の名前がA列に順不同で入力されています。
ここで、イニシャルがAからPの人と、QからZの人の人数をそれぞれ数えてみたいと思います。
上の手書きの表で(コピー用紙の再利用ですみません）、P（大文字)のときは80が返されるので、CODE関数で返される値が80以下のときは、この表ではAからPと考えることができます。そして、81以上のときはQからZと考えることができます。
ここで、B列にCODE関数で返された値が出ているので、この値をCOUNTIF関数で条件付き集計すれば、AからPの人の人数と、QからZの人の人数を集計できます。

AからPの人：=COUNTIF(B2:B60;"<="&80)　で42人
QからZの人：=COUNTIF(B2:B60;">="&81)　で17人
と、分かりました。メデタシメデタシ。

ですが、実は、上の集計方法には、突っ込みどころが少なくとも3点あります。

まず一点。名前が小文字で書かれていたらどうするのか。
全角で書かれているセルがあるとどうするか。
次に、２つに分けず、4つに分けたい場合はどうするのか。

まず最初の突っ込みどころです。

大文字と小文字は、CODE関数で返される値が違います。

たとえば、小文字のaは、=CODE("a")　で97が返されます。小文字のzは122が返されます。
この場合、上の集計の仕方では、aiko（がいたら）は、A-Pに含まれません。
この問題を回避するためには、
=CODE(UPPER("aiko"))　で処理すると、aikoは、AIKOとして集計の対象になります。
または
=CODE(PROPER("aiko"))　で処理すると、aikoは、集計のときAikoとして集計の対象になります。

関数名：UPPER関数
書式：=UPPER("文字列")　または、=UPPER(セル番地)
何ができる：対象セルまたは文字列の英文字を全て、大文字に変換する
平たく書くと：=UPPER("hello")でHELLOが返されます。
関数名：PROPER関数
書式：=PROPER("文字列")　または、=PROPER(セル番地)
何ができる：対象セルまたは文字列の一文字目の英文字を大文字に変換する
平たく書くと：=PROPER("hello")でHelloが返されます。2文字目以降に大文字があった場合、小文字に変換されます。
UPPER関数と反対の役目を果たすのが、LOWER関数です。
関数名：LOWER関数
書式：=LOWER("文字列")　または、=LOWER(セル番地)
何ができる：対象セルまたは文字列の英文字を全て、小文字に変換する
平たく書くと：=LOWER("HELLO")でhelloが返されます。

2番目の突っ込みどころです。

全角の場合、全てゼロが返されます。

例えば、=CODE("ｈ")でも、=CODE("あ")でも、=CODE("ん")でも、全てゼロが返されます。
(これは、エクセルと異なる点です。エクセルの場合、ひらがなや全角文字にもそれぞれの値を割り当てます。　たとえば、=CODE("あ") で9250が返されます。)
全角で書かれているａｉｋｏを、Aikoとして処理したいときは、上で処理したPROPER関数に、ASC関数をかぶせます。
=CODE(ASC(PROPER("ａｉｋｏ")))　で、Aikoとして処理されます。
　
関数名：ASC関数
書式：=ASC("文字列")　または、=ASC(セル番地)
何ができる：対象セルまたは文字列の全角の英文字を半角に変換する
平たく書くと：=ASC("ＨＥＬＬＯ")でHELLOが返されます。
ASC関数と反対の役目を果たすのは、JIS関数です。
関数名：JIS関数
書式：=JIS("文字列")　または、=JIS(セル番地)
何ができる：対象セルまたは文字列の半角の英文字を全角に変換する
平たく書くと：=JIS("HELLO")でＨＥＬＬＯが返されます。

ここまでで、B2セルに=CODE(ASC(PROPER(A2))) と入力すればいいことが分かりました。

４つに分類したい場合は、どうするか

AからG
HからN
OからU
VからZ
に分けたい場合、CODE関数で返される値が、
65から71 ....AからG
72から78 ....HからN　
79から85 ....OからU
86から90 ....VからZ
の４つに分類することになります。
ここでは、2つの場合と同じく、COUNTIF関数を使った場合について説明します。
COUNTIF関数では、複数の条件による検索集計はできないので、
A-G 14人 E2セル =COUNTIF($B$2:$B$60;"<="&71)
H-N 27人 E3セル =COUNTIF($B$2:$B$60;"<="&78)-SUM($E$2:E2)
O-U 9人 E4セル =COUNTIF($B$2:$B$60;"<="&85)-SUM($E$2:E3)
V-Z 9人 E5セル =COUNTIF($B$2:$B$60;"<="&90)-SUM($E$2:E4)
のように、1つ上のセルまでの合計を差し引く形にするのが、いいのではないかと思います。
4つに分ける場合のサンプルファイルは、SKYDRIVEからcalc140.odsをダウンロードしていただき、左下の4つの場合のタブをクリックしてください。

ここまでで、CODE関数の便利な使い方を感じていただけたかなあと思います。

4つに分ける場合、COUNTIF関数を使えば、上のように集計できますが、問題があります。