エクセル 関数|vlookup関数で一覧から値を取得しちゃおう

エクセル関数,vlookup

vlookup関数は、商品コードを基に商品一覧から商品名、単価を取得して、商品売上明細を作成したいと言ったケースにとっても便利な関数です。この関数を使えばデータ入力が効率的に行えます。

 


=vlookup(検索キー,検索範囲,検索範囲の何列目から取得 , 検索方法)


上の例で言うと、検索キーが商品コード、検索範囲が商品一覧(検索キーの商品コードも含める)、商品名なら2列目・単価なら3列目、検索方法は近似値(TRUE)と完全一致(FALSE)のどちらかとなります。

検索方法ですが、完全一致(FALSE)とすると、検索キーが完全に一致した場合のみ値を取得します。近似値(TRUE)とすると、検索キーが完全に一致しない場合は、検索キーに一番近い値で検索を行い値を取得します。

うちが仕事で使う時は検索キーが一致しない時に勝手に値が取得されても困るので、完全一致(FALSE)しか使いません。

なお、検索方法を指定しない場合は近似値(TRUE)が採用されます。

 

【検索方法:完全一致(FALSE)】

【検索方法:近似値(TRUE)】

<動画について補足>
検索範囲(商品一覧)の参照は固定にしたいので”$”を付けています。また、入力した関数の他セルへのコピーには”F2(ファンクションキー)”を使って行っています。詳しくは「エクセル 技|参照先の変換をコントロールしちゃおう」「エクセル 技|値のコピーには F2 (ファンクションキー)を使っちゃおう」を参照して下さい。

一覧表の中から検索条件にマッチしたを探し、指定したの値を取得できちゃう「vlookup()関数」使ってみてはいかがでしょうか。vlookup()関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。

 

エクセル 関数|subtotal関数で小計・総合計を求めちゃおう

エクセル関数

集計を行う場合、条件反射的にSUM関数を使う事が多いのではないでしょうか。確かに、「ここからここまで」の合計を出すと言った集計では良いのですが

大きな表で、小計と総合計を出すといった場合は、総合計を出す(小計を足し合わせる)のがちょっと面倒ですよね。

こんなときは、
subtotal()関数で集計しちゃおう


=subtotal(集計方法,参照範囲)


集計方法 集計機能 同等の関数
非表示の値も含める 非表示の値は無視する
1 101 平均を求める average
2 102 数値の個数を求める count
3 103 データの個数を求める counta
4 104 最大を求める max
5 105 最小を求める min
6 106 積を求める product
7 107 不偏標準偏差を求める stdev
8 108 標本標準偏差を求める stdevp
9 109 合計を求める sum
10 110 不偏分散を求める var
11 111 標本分散を求める varp

実は指定した集計範囲内にある subtotal は無視して集計してくれます。したがって subtotal で小計を出していれば、小計を除いて集計してくれるので集計範囲を一気に指定しても重複計算されずに正しい集計結果を得ることが出来ちゃいます。

小計を除いて集計してくれる「subtotal()関数」使ってみてはいかがでしょうか。subtotal()関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。

 

エクセル 関数|mid関数でいくつか文字を抽出しちゃおう

エクセル関数

前回「エクセル 関数|right関数で右端から文字をいくつか抽出しちゃおう」、前々回「エクセル 関数|left関数で左端から文字をいくつか抽出しちゃおう」と文字列の先頭、最後から文字を抽出する方法を紹介しましたが、今回は文字列のある場所から指定した文字数を取り出す方法です。

例えば、7桁(商品コード)+ N桁(商品名称)で構成された文字列から商品名称だけを取得したいと言ったケースです。

商品コード+商品名称 商品名称
Excel01パソコン パソコン
Excel02マウス マウス
Excel03ディスプレイ ディスプレイ
Excel04スマートフォン スマートフォン

こんなときは
mid関数で好きな位置から値を取得しちゃえ!


=mid(文字列,開始位置,文字数)


任意の位置から指定した数だけ文字を取得しちゃう「mid()関数」使ってみてはいかがでしょうか。mid()関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください

 

エクセル 関数|right関数で右端から文字をいくつか抽出しちゃおう

エクセル関数

前回「エクセル 関数|left関数で左端から文字をいくつか抽出しちゃおう」紹介しましたが、今回は文字列の最後(右端)から指定した文字数を取り出す方法です。例えば、2桁(テスト工程)+4桁(連番)で構成されたコードから4桁(連番)を取得したいと言ったケースです。こんなときは
right関数で右端から値を取得しちゃえ!


=right(文字列,文字数)


指定した数だけ右端から文字を取得しちゃう「right()関数」使ってみてはいかがでしょうか。right()関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。

エクセル 関数|left関数で左端から文字をいくつか抽出しちゃおう

エクセル関数
入力している値の一部を左端から抽出したい場合もあるのではないでしょうか。例えば、2桁(テスト工程)+4桁(連番)で構成されたコードから2桁(テスト工程)を取得したいと言ったケースです。
こんなときは
left関数で左端から値を取得しちゃえ!


=left(文字列,文字数)


指定した数だけ左端から文字を取得しちゃう「left()関数」使ってみてはいかがでしょうか。left()関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。