ハロー＼(^o^)／
最近学んだ便利なエクセル技を紹介します。
何か参考になることがあれば、嬉しいです。
① 【値段に”￥”マークをつける】
Ctrl + Shift + $
を押すと出来ます。
ちなみに、
Ctrl + Shift + 1を押すと、1000の単位ごとにコンマ区切り出来ます。
② 【テーブル化】
表はテーブルにしていきましょう。
Ctrl + T
を押して、
”先頭行をテーブルの見出しとして使用する”
をクリックして”OK”です。
③ 【関数式を見ながら仕事をする】
FORMULATEXT
というワークシート関数を使用すると、関数の内容が見れます。
B1セルには下記の関数が入ってます。
=FORMULATEXT (B3)
④ 【VLOOKUP関数で”値段”を探す】
A3セルの品目を商品テーブルから探して、値段を自動で出せるように、
B3セルにVLOOKUP関数を書いていきましょう。
VLOOKUP関数の作り方は、
=VLOOKUP(①検査値, ②範囲, ③ 列番号, ④ [検索方法])
です。
①の検査値が表から探したいもの
②が該当の表
③が表の中のどの列の情報が欲しいか
④は”FALSE(完全一致)”にします。
今回、①は品目です。A3セルとします。
②は表の範囲です。
VLOOKUP関数の時の注意点としては、
表の一番左の列に①の選択した内容（今回のA3セル）が無いといけません。
③は、MATCH関数で探しましょう。
今回は表の中の2列目なので、”2”と手打ちしても良いですけど。
④は"FALSE"としておきます。
③ですが、行列の中で何個目にその情報が来るのか調べたいときに
MATCH関数が非常に便利です。
=MATCH (⑤検査値, ⑥検査範囲, ⑦[照合の種類])
として使います。
VLOOKUPに似ていて、
⑤検査値： 今回は”値段”が表の中、何列目なのか？調べます。
⑥検査範囲： A6：B6（表のタイトル行）にします。
⑦は"0 (完全一致)"です。
何故このように面倒なことするのか？
と言うと、表を更新していくと列がどんどん増えていくんですね。
1か月後には、
店名、月、仕入先、作るのに要する作業時間とか追加していこう！
と上司に指示されているかもしれないですよね？
最初は、2列目にあっても、
1か月後には、4列目、
2か月後には、5列目...etcと
対象としていた列が動くことってありますよね？
この時に、MATCH関数を使用しておくと、
列を追加していても何も問題ないので、便利なんです！
とここまで、
VLOOKUP & MATCHの便利さを説いておきながら、
私は普段INDEX & MATCHを使ってます。
先ほど書いたこの文章。
”VLOOKUP関数の時の注意点としては、
表の一番左の列に①の選択した内容（今回のA3セル）が無いといけません。”
これが最大の弱点です。
INDEX&MATCH関数の場合は、この問題を解決しますし、
行も列もMATCH関数で探せるので、
品目、タイトル行さえ揃えておけば、表の修正が少なくて良いです。
ただ、検索値が表の一番左にあるなら、VLOOKUPの方がすぐに書けて便利です。
さらに面倒な仕事を頼まれた場合は、下記も対応していきます。
VLOOKUPと他に相性良いのが
TRIM関数と
例えば、
ハンバーガー__（__はスペース）
とか入っているとエラー出ますよね。
この時に、スペースを消すのが
=TRIM()
トリム関数。
草刈りとか写真のトリミングとかに使う英語です。
生データに"=TRIM(A7)"とか施して、スペースが無いようにして、
先ほどの検索に引っかかることようにする。
といった使い方です。
それより便利なのが、ワイルドカード。
=VLOOKUP("*"&A3&"*",A7:B13,2,FALSE)
＊ 見づらいので列は”2”にしました。
例えば、
Z1セル： ハン
Z2セル： バーガー
をつなげて、ハンバーガーと書きたければ、
例①： Z1 & Z2
例②： "ハン" & Z2
例③： Z1 & "バーガー"
例④： "ハンバーガー"
のようなやり方があります。
例④のように、何か文字の両側を””（ダブルクオーテーション）で囲めば
文字として認識してくれます。
何か特定のセルに言葉が入っているなら、
& 【セル番地】
で良いです。
これを利用して、
ワイルドカード（*）を & (アンパサンド）でつないで作っていきます。
例えば、
とあったものを引っかけたい場合は、
”*” & A3
とします。
＊ A3セル = ハンバーガー
反対に、
を引っかける場合は、
A3 & "*"
です。
＊ A3セル = ハンバーガー
両側どちらかにスペース入りそうなら、
"*" & A3 & "*"
です。
＊ A3セル = ハンバーガー
このワイルドカードの弱点は、
A3セル = バーガー
にして、
チーズバーガー
が表にあると、表の中で一番上のものが返ってきます。。。
最後におまけとして、
A3セルの品目をリストから選ぶようにブルダウン形式で選択できるようにします。
データタブ→データの入力規則をクリックして、
入力値の種類を”すべての値” → ”リスト”
にします。
元の値の所に、下記の数式を直打ちします。
=INDIRECT("商品テーブル[品目]")
商品テーブル → A6:B13の表（テーブル化）の名前です。
品目 → 表のタイトル行 "品目"を表してます。
INDIRECT関数は比較的新しいエクセルで使えると思いますが、
数学で言うと逆関数の役割を示す印象です。
A1セルにごはんと書いて、
=A1
とすると、”ごはん”と出ると思いますが、
= "A1"
とすると、”A1”と出ます。
これに対し、
=INDIRECT("A1")
とすると、”ごはん”と出ます。
つまり、INDIRECTで、文字を囲むと、
そのセル番地とかシート番号を読み込んでくれます。
したがって、
商品テーブル[品目]
の中の品目を全て取ってきたいなと思った場合は、
”商品テーブル[品目]"
と両側にダブルクオーテーションつけて、文字化してしまい、
=INDIRECT("商品テーブル[品目]")
とすると、その部分がリスト化できるのです。
さらに、テーブル化しておけば、
13行目 → 14行目
とデータを増やしても、リストは勝手に自動的に対応して増えてくれます。
これは非常に便利なのでぜひ使ってみてください！
エクセルのメンテナンスが楽になりますよ！！
VLOOKUP & MATCH関数の素晴らしさを伝えたかったのに、
・テーブル化
・INDIRECT
・FORMULATEXT
・Ctrl + Shift + $ (円マーク）／Ctrl + Shift + 1 (コンマ区切り）
・VLOOKUP & TRIM
・VLOOKUP & ワイルドカード
の紹介になってしまいました。
あー疲れた笑
しかし、XLOOKUPというさらに素晴らしい関数を既に出してきたマイクロソフト。
私が働く会社は新関数が使えないエクセルのPCもあるので、知っておいて後悔はないですが、やはり最新のものっていいですよね。
それではまた＼(^o^)／
