CSVファイル作成には、Excelを使用します。コピー&ペーストしたり、直接入力は、
なかなか時間のかかる作業ですが、関数を用いることで、効率よくデータを作成
できる場合がほとんどです。
その中でも、特に便利な関数をご紹介します。
※Googleスプレッドシート、LibreOfficeでも使用できます。
vlookup関数(ブイ・ルックアップ)
指定した範囲の中から検索条件に一致するデータを取り出す関数
目視でデータ確認することを思い浮かべてください。見間違いしそうですよね。
そんな時、便利なのがこの関数です。
vlookupの「v」は「vertical」の意味で、縦方向、つまりExcelの縦列を見て、
データを取り出す関数です。言葉では分かりにくいので、実際に使ってみましょう。
関数の作り方
=vlookup(検索値, 指定する範囲,列番号,検索の型)
項目名 | 内容 |
---|---|
検索値 |
検索したい値を入れます。検索したいセル番号を指定するか、直接入力します。 ・セルを指定する場合 ・直接入力の場合 |
指定する範囲 |
検索したい範囲を指定します。指定した範囲の一列目を見て、データを取り出します。 例)= vlookup ( A8, A2:D5 , … →この場合、以下の範囲を指定したことになります。 |
列番号 |
1列目から数えて何番目の列からデータを取り出すか指定します。 例)= vlookup ( A8 , A2D5 , 3 , … →3と入力しているので、3列目を指定しています。 |
検索の型 |
データが一致しない時に、どう表示させるかを指定します。 指定する値は「0」か「1」です。「0」を指定しましょう。 例)= vlookup ( A8 , A2:D5 , 3, 0 ) ・「0」を指定 ・「1」を指定 |
セルを固定する
数式は完成しましたが、この数式をそのままコピーしても、
指定したセルや、選択範囲がずれてしまいます。
これを避けるために、「$」を付けてセルを固定します。
= vlookup ( $A8 , $A$2:$D$5 , 3, 0 )
「$」は、「後ろにある文字を固定する」という役割があります。
$A8 → A列で固定する(列だけを固定する)
$A$2:$D$5→ A列と2行目~D列5行目までを固定する
例えば、「A$8」とした場合なら、8行目で固定することになります。
「$A$8」と指定したら、A8のセルで完全固定されます。
こうすることで、コピーしても数式がずれません。
Windowsの場合は、「F4」キーで「$」マークがつきます。
Macの場合は、「command+T」です。
数式の完成形
= vlookup ( $A8 , $A$2:$D$5 , 3, 0 )
注意事項
vlookupで気をつけたいのが、指定した範囲の一列目に、
同じ検索語句が複数入っている場合、最初にその語句が
出現した列で、データを取り出してしまうことです。
りんご
みかん
バナナ
キウイ
りんご
のように、2回目の「りんご」が出現すると、
1回目の「りんご」の情報だけ取りだそうとします。
vlookup関数を厳密に言い表すと、
「検索範囲の1列目で最初に出現した検索値から、
任意の列番号のデータを取り出す」
という関数です。
もし、選択範囲の1列目に同じ語句がある場合は、
「countif関数」を利用してみましょう。
countif関数(カウント・イフ)
通し番号をつける
りんご
みかん
バナナ
キウイ
りんご
先ほどの例でいえば、「りんご」が2回でているので、
「りんご1」「りんご2」と番号を振ってあげることで、
検索値が同じでも、それぞれのデータを取り出すことが可能です。
関数の作り方
=countif(検索する範囲,検索値)
左に2行、列を追加します。役割は下記の通りです。
A列:通し番号
B列:検索値
A列:通し番号を作る
項目名 | 内容 |
---|---|
検索する範囲 |
検索したい範囲を指定します。範囲の開始位置だけ、固定する必要があります。 例)=countif ( $C$2:C2 , … ) ※下のセルに数式をコピーすると、開始位置は固定されたまま、終了位置だけ変わる 例)=countif ( $C$2:C3 , … |
検索値 |
検索したいセルを選びます。 例)=countif ( $C$2:C2 , C2 ) |
この一行目の数式を、そのまま下のセルにコピーして貼り付けます。
これで、通し番号をつける事ができました。
B列:新しく、vlookup用の検索値を準備する
これは単純に、C列の商品名と通し番号をくっ付けるだけです。
= C2 & A2
このように、「&」で繋げれば完成です。必要な範囲をコピーして貼り付けてください。
新しく作った検索値を先頭行にして、vlookup関数を設定し直す
counifで追加した通し番号付きの検索値を先頭行にして、
範囲と列番号を指定し直せば、同じ検索値のデータも、
それぞれのデータとして取り出すことができます。