関数」カテゴリーアーカイブ

重複した項目の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

大文字を小文字に変換する

▼概要

LibreOfficeやOpenOfficeやエクセルで「英語の大文字を小文字にしたい!」と言ったことはあるかと思います。LibreOfficeやOpenOffice、ExcelではLOWER関数を使うことにより簡単に行えます。今回の例ではC列の大文字をD列で小文字で表示させます。。

▼今回使用した関数。helpより抜粋。


LOWER

文字列のアルファベット文字を、すべて小文字に変換します。
構文
LOWER(“テキスト”)
テキスト は、変換されるテキストを指します。

=LOWER(“Sun”) は、sun を返します。

▼LibreOffice/OpenOffice/Excel

小文字を表示したいセル(D2)に =LOWER(C2)と入力 → ENTERを押して完了
画像はLbreOfficeのものですがOpenOffice、Excelも同じ操作になります。

▼この3つの大文字の単語を小文字にしていきます。
libre_lower1

▼LOWER(C2)と入力します。
libre_lower2

▼ENTERを押して小文字に
libre_lower3

▼D2をコピーしてD3、D4に貼り付けで完成
libre_lower4

 

 

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

ハイフンなしの入力済の郵便番号をハイフン有りに変更する方法 LEFT関数,RIGHT関数

▼概要

LibreOfficeやOpenOfficeで郵便番号のリストがハイフン無しで記述されているときがあると思います。ですが都合によっては郵便番号の書式をハイフンありに変更したい場合があります。例えば、「1112222を111-2222にしたい!」と言ったことはあるかと思います。この記事ではハイフンなしの郵便番号をハイフン有りの郵便番号にする方法を伝えていきます。
今回はLEFT()関数とRIGHT()関数を使います。

▼今回使用した関数。helpより抜粋。


LEFT

テキストの先頭の文字 (複数の文字も可) を返します。
構文
LEFT(“テキスト”; 数値)
テキスト は、最初の部分ワードが決定されるテキストです。
文字数 (オプション) は、テキストの左 (先頭) から取り出す文字数です。このパラメータが指定されていない場合、テキストの一番左 (先頭) の文字だけを返します。

=LEFT(“output”;3) は、「out」を返します。

RIGHT

テキストの最後の文字 (複数の文字も可) を返します。
構文
RIGHT(“テキスト”; 数値)
テキスト は、右部分が決定されるテキストです。
数値 (オプション) は、テキストの右部分の文字数です。

=RIGHT(“Sun”;2) は、un を返します。


▼LibreOffice/OpenOffice

郵便番号の隣のセルを選択 → =LEFT(D3;3)&”-“&RIGHT(D3;4)と入力。(D3のところは適宜変更する) → 入力したセルをコピーして終了位置まで貼付けする → 完了。
画像はOpenOfficeのものですがLbreOfficeも同じ操作になります。

▼郵便番号の一覧です。隣にハイフン有りのセルを用意します。
libre_zip_hypen_insert_b1

▼郵便番号が入力してある隣のセルに=LEFT(D3;3)&”-“&RIGHT(D3;4)と入力。(D3のところは適宜変更する)を入力してENTERを押します。
libre_zip_hypen_insert_b2

▼入力したセルをコピーして終了位置まで貼付けします。
libre_zip_hypen_insert_b3

▼ ハイフン付きで表示されました。完了です。
libre_zip_hypen_insert_b4


※基本的にMicrosoft Excelも似たような動作で行うことが出来ます。注意スべきところはLibreOfficeやOpenOfficeは関数の引数をセミコロン(;)で区切っています。Excelはカンマ(,)で区切ります。

 

 

Microsoft Excel 2000
LibreOffice version 4.1.0.4
OpenOffice 3.4.1
短縮URL http://eol.nagoring.com/?p=198

作成した表の値の個数を数えたい COUNT

▼概要

LibreOfficeやOpenOfficeで「この表に書かれてる値の個数が知りたい!」と言ったことはあるかと思います。ですがLibreOfficeやOpenOfficeにはそれを解決するための関数count()が用意されています。そこでcountを使って簡単に個数を求められる方法を伝えていきます。今回の例では都道府県一覧の個数を取得します。

▼LibreOffice/OpenOffice

合計値を表示させたいセルを選択 → =COUNT( と入力 → 合計したい範囲を選ぶ → ENTERを押して完了。
画像はLbreOfficeのものですがOpenOfficeも同じ操作になります。

▼値の個数の合計値を表示させたいセルを選択します。
libre_countnum1
▼=「COUNT(」と入力します。
libre_countnum2
▼合計したい範囲を選択します。マウスで選択する場合は開始位置で左クリックをおしっぱにして終了位置で左クリックを離します。
キーボードで行う場合は合計したいセルの端の位置にカーソルを移動させShiftキーを押しながら矢印キーを押して移動すると複数選択できます。
libre_countnum3
▼選択後はENTERキーを押すと完成です。=COUNT(セルの範囲) が完成した式になります。
libre_countnum4
 

 

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

作成した表のセルの個数を数えたい COUNTA

▼概要

LibreOfficeやOpenOfficeで「この表に書かれてるセルの個数が知りたい!」と言ったことはあるかと思います。ですがLibreOfficeやOpenOfficeにはそれを解決するための関数COUNTA()が用意されています。そこでCOUNTAを使って簡単に個数を求められる方法を伝えていきます。今回の例では都道府県一覧の個数を取得します。

▼LibreOffice/OpenOffice

合計値を表示させたいセルを選択 → =COUNTA( と入力 → 合計したい範囲を選ぶ → ENTERを押して完了。
画像はLbreOfficeのものですがOpenOfficeも同じ操作になります。

▼セルの個数の合計値を表示させたいセルを選択します。
libre_count1

▼=「COUNTA(」と入力します。
libre_count2

▼合計したい範囲を選択します。マウスで選択する場合は開始位置で左クリックをおしっぱにして終了位置で左クリックを離します。
キーボードで行う場合は合計したいセルの端の位置にカーソルを移動させShiftキーを押しながら矢印キーを押して移動すると複数選択できます。
libre_count3

▼選択後はENTERキーを押すと完成です。=SUM(D2:d6) が完成した式になります。
libre_count4

 

 

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

合計値の求め方

▼概要

LibreOfficeやOpenOfficeではエクセル同様に合計値を求める方法があります。「2行目から6行目までの合計値を求めたい!」と言ったことはあるかと思います。この記事では値の合計を求める方法を伝えていきます。

▼LibreOffice/OpenOffice

合計値を表示させたいセルを選択 → =SUM( と入力 → 合計したい範囲を選ぶ → ENTERを押して完了。
画像はLbreOfficeのものですがOpenOfficeも同じ操作になります。

▼合計値を表示させたいセルを選択します。
libre_sum1

▼=「SUM(」と入力します。
libre_sum2

▼合計したい範囲を選択します。マウスで選択する場合は開始位置で左クリックをおしっぱにして終了位置で左クリックを離します。
キーボードで行う場合は合計したいセルの端の位置にカーソルを移動させShiftキーを押しながら矢印キーを押して移動すると複数選択できます。
libre_sum3

▼選択後はENTERキーを押すと完成です。=SUM(D2:d6) が完成した式になります。
libre_sum4

 

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