フェイスブックページへ

ホールスタッフブログ

このエントリーをはてなブックマークに追加

2015/06/05

エクセルのお勉強の時間です。

皆様こんにちは。さや香です。
今日は、いつもと全然違う志向で、

パソコンでよく使うエクセルの機能について書こうと思います。

ちょっとお勉強チックです。
私は、エクセルの数式が大好きで、前職でも前々職でもとにかく作業をシンプルにするために
機能をいろいろ調べて使っていました。もちろん今でも多用しております。
つい最近、エクセルでこんなことしたいんだけど、という相談を受けたので
これまでの私のお気に入りの数式を2つほどご紹介したいと思います。
パソコンに興味なし、もしくはそんなことはわかってるよ!という方には申し訳ないですが
ちょっと興味がある方には面白いと思います。
①indexという数式と、matchという数式と、そのドッキング
・indexという数式
これは、vlookupとhlookupをいっぺんに使える数式で、
上から何行目、左から何行目のセルの値を持ってこいという数式です。
数式: =INDEX(B2:E12,5,3)
「B2からE12の範囲(水色のセル)の中で、上から5行目、左から3列目の値は何か」
 

 

 

 

結果: せ
・matchという数式
これは、検索したい値は何番目にあるか探せという数式です。
数式: =MATCH(B15,B1:E1,0)
「B15の値(黄色いセル)が、B1からE1で何番目にあるか」
 

 

結果: 2
この数式は、検索範囲を縦に指定すると、上から何番目か探します。
数式: =MATCH(D15,A2:A12,0)
「D15の値(オレンジのセル)が、A2からA12で何番目にあるか」
結果: 4
・indexとmatchのドッキング
検索したい値を上から・左から何番目かを探して、

それがぶつかるセルの値を探すことになります。

数式: =INDEX(B2:E12,MATCH(D15,A2:A12,0),MATCH(B15,B1:E1,0))
「B15の値(黄色いセル)が、B1からE1で何番目にあるか探して、
 D15の値(オレンジのセル)が、A2からA12で何番目にあるか探して、
 B2からE12の範囲(水色のセル)の中でぶるかるセルの値は何か」

 

 

結果: う
上記のシートで、黄色いセルに「3列目」、オレンジのセルに「5行目」と打ち込むと、
「せ」という結果が返ってくるという表が出来上がりました。
私はこれを使って、250店舗の数値がデータベースとして無機質に並んでいるのを、
店舗名を入れればその店舗のPLが出てくるというフォーマットを作ってみたのでした。
②iserrorという数式
これは、エラーを探せという数式です。

 

 

 

数式で出した数値をさらに合計したいとき、B列の数式にエラーが出たため
合計がエラーになってしまった、ということはありませんか?
将来使うことになるので、まだ入力はしないけど表は作ったが、

ゼロ割るゼロなのでエラーとか、

エラーはすっ飛ばしてもいいから合計をしたいとか、

エラーが邪魔な時にはもってこいな数式です。

数式: =ISERROR(I17)
「I17(オレンジのセル)がエラーだったらTRUEと言え」
結果: TRUE

 

 

・if関数とドッキング
「エラーです」と言われただけではどないすんねんという感じなので、
if関数とドッキングしてみましょう。
数式: =IF(ISERROR(VLOOKUP($G6,$A$5:$C$14,1,0)),”",VLOOKUP($G6,$A$5:$C$14,1,0))
 →これをセルH6に入力し、15行目までコピペ
「もしVLOOK関数がエラーならブランクを、そうじゃなければVLOOK関数の結果を出せ」
結果: 1~9まではVLOOK関数結果(1~9)、10はエラーだったためブランク
もともとB列にはVLOOK関数を入れていましたが、
IF、ISERRORを挿入することによって、エラーがブランクに変わったため、
合計の数値も出てくるようになりました。

 

 

ということで私のお気に入りの数式を2つご紹介しました。
ちょっとマニアックかもしれませんが、味方にしたらとてもいい働きをします。
暇な時にちょっとエクセルで遊んでみてみてください。
新たな発見があるかもしれません。
もし他にも興味がある数式とかあれば、リクエストください。
もしくはこんな面白い数式、マニアックな数式あるよ!という方がいらっしゃれば、
是非ご紹介してください。
楽しみにお待ちしております!