OpenOfficeやリブレオフィスに移行したいけど使い勝手わからない!そういうときのために役に立つ記事を書いていきます。VLOOKUP▼概要LibreOfficeやOpenOfficeやエクセルで「項目にあわせて連番を作成したいけれど、重複した項目は同じIDにしたい!」と言ったことはあるかと思います。今回の例ではLibreOfficeを使用して、路線や駅の表を作るときに駅のIDが複数に被らないやり方を解説します。駅で異なる沿線の場合は同じIDにします。これが10,20個の項目でしたら手動でやっても問題は無いのですが1000,2000個となってくるとそうはいきません。式やVLOOKUPを駆使してこれを解決します。■VLOOKUP右隣のセルへの参照を含む垂直検索。この関数は、特定の値が行列の最初の列に含まれているかどうかをチェックします。よって名前が付けられている列の同じ行の値を返します。設定されている場合、データは昇順に並べ替えられると想定されます。見つからない場合は、条件より小さい最後の値が返されます。ゼロが設定されている場合は、完全一致を見つける必要があります。したがって、値がゼロの場合、データを昇順に並べ替える必要はありません。行列の最初の列で検索される値です。列以上を含む参照です。返される値を含む行列の列の番号です。行列の最初の列を昇順で並べ替えるかどうかを示すオプションのパラメーターです。並べ替えた列はより一層高速に検索でき、並べ替えたリストの最小値と最大値の間にある場合は、検索値が完全に一致しなかったときでも、関数が値を返します。並べ替えていないリストでは、検索値は完全に一致する必要があります。いうメッセージを返します。■COUNTIFセル範囲内で特定の基準を満たすセルの数を返します。条件を適用する範囲です。条件は、数字、式、または文字列形式の条件を示します。これらの条件は、数えあげられるセルを決定します。検索テキストは、正規表現の形式でも入力できます。始まるすべての単語を表します。検索条件を含むセル範囲を指定することもできます。テキストそのものを検索する場合、二重引用符でテキストを囲みます。■SUM指定されたセル範囲にあるすべての数値を加算します。▼手順1.沿線表を作成2.重複チェック用の列を作る3.2を使って減算用の重複チェック合計値用の列を作る4.計算用ユニークID列を作る5.VLOOKUPと3,4の列を使ってIDを割り出す。▼2.重複チェック用の列を作る現在の行の駅名がそれより上で項目が作られているかをCOUNTIFを使ってチェックします。仕様は重複していた場合、1と表示されるようにします。COUNTIFの範囲には項目の最初から現在の上の項目までを指定します。検索対象の項目は現在の行の駅名を入力します。ここで注意しておくのは範囲の上は固定になるので、$をつけておきます。このような式になります。COUNTIF($C$3:C4,C5)これで重複のチェックが取れますがCOUNTIFのみだと範囲内に複数の重複された項目があると2以上とカウントされてしまう問題があります。これを解決するにはIF文をを使用します。1以上のときは必ず1にするようにIF文を書きます。これで重複していた場合1と表示されるようになる。IF(COUNTIF($C$3:C4,C5)>=1,1,0)▼3.減算用の重複チェック合計値用の列を作る現在の行より上で重複チェックの合計を取得させます。SUMを使って集計します。範囲は一番上の項目から現在の行の一つ上の項目にします。SUM($E$3:E6)4.計算用ユニークID列を作る単純な連番を作成します。5.VLOOKUPと3,4の列を使って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)かなり複雑なものとなりましたが、これで沿線を考慮した駅の連番を作成することが出来ました。一つの式で無理に行おうとするとメンテナンスもしにくくなると思います。今回のように他の列に計算した値を置いておくことによりスムーズに計算が行えるようになります。一行目は手動で入力して下さい。今回使用したデータです。xls形式でアップロードしています。シートを複数選択する。複数選択してシートの色を変える。オープンオフィスやリブレオフィスOpenOfficeのダウンロードLibreOfficeのダウンロードオープンオフィスやリブレオフィスは無料のオフィスソフトです。エクセルやワードやパワーポイント等、所謂マイクロソフトオフィスと同等のことが出来ます。