エクセル関数が分からない…エクセルって苦手…エクセル関数やエクセルの機能をどのように使えば良いかわからないという方を対象に、熟練のシステムエンジニアが動画を使って分かりやすくエクセル関数やエクセルの機能などエクセルの使い方を紹介します！
何文字入力されているか調べたいときにはlen関数を使うと便利です。
大文字や小文字も区別なく１文字と数えてくれ、セルに何文字入力されているか教えてくれちゃいます。
=len(文字列)
len関数、以前紹介したright関数やleft関数と組み合わせて使う事がとても多いです。
例えば、下記の文字列から先頭３文字を除いた文字列を取得したい（朱字部分を取得したい）場合は、全体の文字数から（先頭の）３文字を引いた文字数分を右側から取得すれば良いため、right関数とlen関数を使います。
東京都港区新橋ＸーＸＸ－Ｘ
東京都中央区日本橋ＸＸＸーＸ
=RIGHT(文字列,LEN(文字列) – 3)
何文字入力されているか教えてくれる「len()関数」使ってみてはいかがでしょうか。len関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。
ある文字が、文字列に含まれているか調べたいときにはfind関数を使うと便利です。
文字列の何番目に探している文字があるのかを教えてくれちゃいます。
=find(検索文字列,対象文字列,[開始場所])
[開始場所]：任意指定。例えば対象文字列の5文字目から検索を行う場合は5を指定します。指定しない場合は先頭から検索を行います。
ちなみに対象文字列の中に、検索文字列が複数個あった場合は、最初に見つかった位置を教えてくれます。
一方で、対象文字列の中に、検索文字列が１つも無かった場合は、エラー（#VALUE!）となります。
検索文字列が何番目に含まれているか教えてくれる「find()関数」使ってみてはいかがでしょうか。find関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。
前回、「エクセル 関数｜vlookup関数で一覧から値を取得しちゃおう」でvlookup関数を紹介しましたが、index関数とmatch関数を併用する事で同じ機能プラスαを実現できます。
vlookup関数では検索キーの右側（右の列）に取得する値が無くてはなりませんが、検索キーの左側（左の列）を取得したいケースもあるかと思います。
index関数とmatch関数を併用する事で、検索キーの左側の値を取得出来ちゃいます。
範囲内から指定した値を探して、範囲内の上から数えた位置を求める関数です。
=match(検索値 , 検索範囲 , 完全一致or近似値）
match(“ハサミ” , 検索範囲(赤い枠) , 完全一致）の様に指定した場合、”ハサミ”は赤い枠の３番目にあるので、”３”が返ってきます。
また、match(“消しゴム” , 検索範囲(赤い枠) , 完全一致）とした場合は、”消しゴム”は赤い枠の４番目にあるので、”４“が返ってきます。
この様にmatch関数は検索値が検索範囲の上から何番目にあるかを返してくれます。
範囲内の縦いくつ、横いくつの位置にあるセルの値を取得する関数です。
=index(範囲 , 範囲内の何行目 , 範囲内の何列目）
index(範囲(青い枠) , 2 , 1）の様に指定した場合、青い枠の上から２行目、左から１列目にある値の”B”が返ってきます。
また、index(範囲(青い枠) , 3 , 2）とした場合は、青い枠の上から３行目、左から２列目にある値の”ハサミ”が返ってきます。
この様にindex関数は指定した範囲において、上から何番目、左から何番目と位置を指定する事で格納されている値を返してくれます。
index関数の”範囲内の何行目”をmatch関数で求める事で、あとは何列目から取得するかを指定すれば、vlookup関数の機能を実現できます。
=index(範囲 , match関数 , 範囲内の何列目）
match関数で検索値が何行目にあるかを特定する事がポイントとなります。
◆”ハサミ”の商品コード、単価を取得しい場合
=index(範囲(青い枠),match(“ハサミ”,赤い枠,完全一致),範囲内の何列目)
“ハサミ”をmatch関数で求めると上から３行目にあるので、”３”となります。あとは、範囲内の何列目の値を取得するかを指定します。
=index(範囲(青い枠) , 3 , 1) ⇒ “C” （商品コード）
=index(範囲(青い枠) , 3 , 3) ⇒ “100”（単価）
一覧表の中から検索条件にマッチした行を探し、vlookup関数では取得できない検索キーより左側にある列の値も取得できちゃう「index()関数 + match関数」使ってみてはいかがでしょうか。index()関数、match関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。
vlookup関数は、商品コードを基に商品一覧から商品名、単価を取得して、商品売上明細を作成したいと言ったケースにとっても便利な関数です。この関数を使えばデータ入力が効率的に行えます。
=vlookup(検索キー,検索範囲,検索範囲の何列目から取得 , 検索方法)
上の例で言うと、検索キーが商品コード、検索範囲が商品一覧（検索キーの商品コードも含める）、商品名なら２列目・単価なら３列目、検索方法は近似値（TRUE）と完全一致（FALSE）のどちらかとなります。
検索方法ですが、完全一致(FALSE)とすると、検索キーが完全に一致した場合のみ値を取得します。近似値(TRUE)とすると、検索キーが完全に一致しない場合は、検索キーに一番近い値で検索を行い値を取得します。
うちが仕事で使う時は検索キーが一致しない時に勝手に値が取得されても困るので、完全一致(FALSE)しか使いません。
なお、検索方法を指定しない場合は近似値(TRUE)が採用されます。
【検索方法：完全一致（FALSE）】
【検索方法：近似値（TRUE）】
＜動画について補足＞
検索範囲（商品一覧）の参照は固定にしたいので”＄”を付けています。また、入力した関数の他セルへのコピーには”F2(ファンクションキー)”を使って行っています。詳しくは「エクセル 技｜参照先の変換をコントロールしちゃおう」「エクセル 技｜値のコピーには F2 (ファンクションキー)を使っちゃおう」を参照して下さい。
一覧表の中から検索条件にマッチした行を探し、指定した列の値を取得できちゃう「vlookup()関数」使ってみてはいかがでしょうか。vlookup()関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。
集計を行う場合、条件反射的に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()関数の詳細な使い方を知りたい方は、別のサイトや書籍等で確認してみてください。