COUNTIF」カテゴリーアーカイブ

重複した項目のIDは同じにして連番を作成する VLOOKUP

▼概要

LibreOfficeやOpenOfficeやエクセルで「項目にあわせて連番を作成したいけれど、重複した項目は同じIDにしたい!」と言ったことはあるかと思います。
今回の例ではLibreOfficeを使用して、路線や駅の表を作るときに駅のIDが複数に被らないやり方を解説します。

▼LibreOffice/OpenOffice/Excel
言葉にすると説明が複雑になるので沿線と駅の完成形を画像で見てください。

▼沿線
vlookup_high1

▼駅
vlookup_high_mihon1

同一の駅で異なる沿線の場合は同じIDにします。これが10,20個の項目でしたら手動でやっても問題は無いのですが1000,2000個となってくるとそうはいきません。式やVLOOKUPを駆使してこれを解決します。

▼今回使用する関数 ヘルプより抜粋
■VLOOKUP

右隣のセルへの参照を含む垂直検索。 この関数は、特定の値が行列の最初の列に含まれているかどうかをチェックします。この関数は、次に Index によって名前が付けられている列の同じ行の値を返します。並べ替え順序 パラメーターが省略されている場合や、TRUE か 1 が設定されている場合、データは昇順に並べ替えられると想定されます。この場合、完全一致の 検索条件 が見つからない場合は、条件より小さい最後の値が返されます。並べ替え順序 に FALSE またはゼロが設定されている場合は、完全一致を見つける必要があります。見つからない場合は、結果はエラー Error: Value Not Available になります。したがって、値がゼロの場合、データを昇順に並べ替える必要はありません。

構文
=VLOOKUP(検索条件; 行列; インデックス; 並べ替え順序)
検索条件 は、行列の最初の列で検索される値です。
行列 は、2 列以上を含む参照です。
インデックス は、返される値を含む行列の列の番号です。最初の列の番号は、1 です。
並べ替え順序 は、行列の最初の列を昇順で並べ替えるかどうかを示すオプションのパラメーターです。最初の列を昇順で並べ替えない場合は、ブール値の FALSE またはゼロを入力します。並べ替えた列はより一層高速に検索でき、並べ替えたリストの最小値と最大値の間にある場合は、検索値が完全に一致しなかったときでも、関数が値を返します。並べ替えていないリストでは、検索値は完全に一致する必要があります。そのほかの場合、関数は、Error: Value Not Available というメッセージを返します。

■COUNTIF
セル範囲内で特定の基準を満たすセルの数を返します。

構文
COUNTIF(範囲; 条件)
範囲 は、条件を適用する範囲です。
条件は、数字、式、または文字列形式の条件を示します。これらの条件は、数えあげられるセルを決定します。検索テキストは、正規表現の形式でも入力できます。たとえば、b.* は b で始まるすべての単語を表します。また、検索条件を含むセル範囲を指定することもできます。テキストそのものを検索する場合、二重引用符でテキストを囲みます。

■SUM
指定されたセル範囲にあるすべての数値を加算します。

構文
SUM(数値 1; 数値 2; …; 数値 30)
数値 1 から 30 は、合計を計算する最高 30 個の引数です。

▼手順
1.沿線表を作成
2.重複チェック用の列を作る
3.2を使って減算用の重複チェック合計値用の列を作る
4.計算用ユニークID列を作る
5.VLOOKUPと3,4の列を使ってIDを割り出す。

▼1.沿線表を作成 山手線、京浜東北線、総武線の3通り作ります。
vlookup_high1

▼2.重複チェック用の列を作る
現在の行の駅名がそれより上で項目が作られているかをCOUNTIFを使ってチェックします。
仕様は重複していた場合、1と表示されるようにします。
COUNTIFの範囲には項目の最初から現在の上の項目までを指定します。
検索対象の項目は現在の行の駅名を入力します。
ここで注意しておくのは範囲の上は固定になるので、$をつけておきます。

このような式になります。
COUNTIF($C$3:C4,C5)

vlookup_high2c

これで重複のチェックが取れますがCOUNTIFのみだと範囲内に複数の重複された項目があると2以上とカウントされてしまう問題があります。
これを解決するにはIF文をを使用します。
1以上のときは必ず1にするようにIF文を書きます。
これで重複していた場合1と表示されるようになる。
IF(COUNTIF($C$3:C4,C5)>=1,1,0)

vlookup_high2d

▼3.減算用の重複チェック合計値用の列を作る
現在の行より上で重複チェックの合計を取得させます。
SUMを使って集計します。
範囲は一番上の項目から現在の行の一つ上の項目にします。
SUM($E$3:E6)

vlookup_high3b

4.計算用ユニークID列を作る
単純な連番を作成します。
後の計算用です。

vlookup_high4a

5.VLOOKUPと3,4の列を使ってIDを割り出す。
最後の締めになります。
式で各と以下のようになります。
IF(現在行の減算用sum<>一つ前の行の減算用sum, VLOOKUP(駅名,1項目目からのnameから計算用IDまでの範囲,計算用IDのindex,0),計算用ID – 減算用sum)

現在の行が重複しているか否か調べるために現在行の減算用sumと一つ前の行の減算用sumを比較しています。
異なる場合、現在の行は重複されていることになります。
重複chk行でもチェックは行えますが、同じ駅が2つ並んだ場合判別が出来ないので使用しません。

IFが成立するケース。つまり重複している場合です。
このときはVLOOKUPを使って範囲内の駅名を探します。
VLOOKUPは一番最初に検索される行を取得します。
indexが5となっているので範囲の5列目、つまり、その駅が一番初めに宣言されている計算用ID(ユニークID)を取得します。
これで一番初めの駅のIDが取得出来ます。

IFが成立しないケース、つまり重複していないケースです。
計算用IDを参照してくるのですが一つ問題があります。
重複があった行があるとそのままの参照では数字の抜けが出てしまうため連番では無くなってしまいます。
それを避けるために減算用sumを使用します。
減算用sumはいままでの重複した項目の個数を数えているため、現在行の計算IDから減算用sumを引くと重複項目を考慮した連番を作成することができます。

例 4行目だと以下の感じになります。
IF(F6<>F5,VLOOKUP(C6,$C$3:G5,5,0),G6-F6)

vlookup_high5a

かなり複雑なものとなりましたが、これで沿線を考慮した駅の連番を作成することが出来ました。
一つの式で無理に行おうとするとメンテナンスもしにくくなると思います。
今回のように他の列に計算した値を置いておくことによりスムーズに計算が行えるようになります。

補足
一行目は手動で入力して下さい。

今回使用したデータです。xls形式でアップロードしています。
http://www1.axfc.net/u/3095369

LibreOffice version 4.1.0.4
OpenOffice 3.4.1
短縮URL http://eol.nagoring.com/?p=280