ページ

▼

2014/04/27

Excel かなり使える関数を紹介！「感激！超便利！」と個人的に感じているもの一覧

データ分析、収支管理、シフト作成･･･などExcelを使って様々なものを作成する際に、「これを知っていると楽！」を個人的な主観で紹介

今回の記事は、書こうかどうかかなり迷ったのだが、まぁ誰かの役には立つのではないかと思い紹介してみる。

私はExcelがかなり好きで仕事でもプライベートでも結構活用している。（Wordは嫌い）
この時点でキモッ（ﾟдﾟlll）と思った方は今回の記事は読まなくてもいいかも…。

仕事で使い続けていく中で独学で習得したことが大半で、関数はある程度扱えるが、VBAは扱うことができないためコードを書いてマクロを作成することできない。（いずれなりたい･･･）こんな感じのスキルレベルなので、VBAが使えて自在にマクロ化できるような方はこの記事は役に立たないと思う。ハイレベルな方が見れば「プッ（笑）」となったり、「もっとスマートなやり方あるじゃん」なんてこともあるかもしれない。スキルアップした将来の自分が見直した時に思わず赤面してしまうような内容かもしれない。

それでも「Excelのおかげで業務効率が大幅にアップした！」、「これを知っていたから思い描いたものがExcelで作成できた！」ということが多々あり、その中でも非常に重宝しているものがいくつかあるのでそれを今回紹介したいと考えた。基本的なことだったり、達人から見れば遠回りな方法もあるかもしれないが、Excelで仕事をする際に四苦八苦することが多いという方は一度目を通してみてほしい。

個人的にかなり使えるExcel関数

以下で私が個人的によく活用する関数を紹介するが、関数の詳細な解説に関しては省略する（というか、私よりも何倍もExcelを熟知している達人がたくさんいるので、そういった方が書いているWebサイトを参考にしてほしいというのが本音）。

INDEX関数とMATCH関数で検索

検索値よりも左側にあるセルを取り出したい場合や、VLOOKUP関数では「検索できない」「表の並びを変えたり作業例を追加しないと検索できない」「検索結果表示までに時間がかかる」というケースで役に立つのがINDEX関数とMATCH関数だ。

例えばこんなケース･･･

  • セルB5で選択した「管理ID」を元に「商品名」を取り出したい！
  • D列に作業セルを作成しVLOOKUP関数で検索するのは避けたい！
  • 表の並びや構成は変更できない！

INDEX関数とMATCH関数で検索
セルC5 で次の関数を使えば「商品名」を取り出すことが可能！

=INDEX(B8:D17,MATCH(B5,D8:D17,0),2)

INDIRECT関数とVLOOKUP関数で検索

VLOOKUP関数を使ってデータを検索をしたいが、参照先の表が複数あって「どうしよう...」と悩んでしまうケースでは、INDIRECT関数が役に立つ。

例えばこんなケース･･･

  • 部署別に別れた社員名簿から「社員番号」を検索したい！
  • セルB5で指定した「部署名」とセルC5で指定した「スタッフ名」を元に「社員番号」を取り出したい！
  • 表の並びや構成は変更できない！

INDIRECT関数とVLOOKUP関数で検索
セルD5 で次の関数を使えば「社員番号」を取り出すことが可能！

=VLOOKUP(C5,INDIRECT(B5),2,0)

ただこの式を使うためには、参照先となる表にあらかじめ名前を付けておく必要がある。
参照先となる表にあらかじめ名前を付けておく

空白セル除外して並び替える関数

作業途中で発生した空白セルを除外して（詰めて）並び替えたい場合に役に立つ！
個人的にこの数式はかなり重宝している！

例えばこんなケース･･･

  • オートフィルタでの並び替えはNG！
  • ジャンプ機能での空白セル削除はNG！
  • とにかく関数で空白セルを除外して並び替えをしたい！

空白セル除外して並び替える関数
セルJ4 に次の数式 =INDEX(F:F,SMALL(IF(F$1:F$29<>"",ROW(F$1:F$29),30),ROW(F1)))&"" を入力し「配列数式」にする。（配列数式にするには[Ctrl]と[Shift]と[Enter]キーで数式を確定するだけ）
{=INDEX(F:F,SMALL(IF(F$1:F$29<>"",ROW(F$1:F$29),30),ROW(F1)))&""}

後はオートフィルで好きな範囲までコピーすれば完了！

余談になるが上記式で空白セルを除外した後のデータを、そのままVLOOKUP関数などで検索をかけてもヒットしない。検索値に少し工夫が必要になる。例えばセルN4の値を検索値としてセルO4に「売上」を表示したい場合は以下のようにする。
=VLOOKUP(N4&"",J4:L18,3,0) 検索値に0の文字列を付ける

その他の個人的に重宝している関数

セル内の半角スペース/全角スペースを除外する関数

人名を検索値としてVLOOKUPやHLOOKUP関数などで検索する場合、参照先の表が自分ではなく第三者が作成したものだと姓と名の間のスペースが半角だったり全角だったりで、うまく検索できない場合がある。そんな時は以下の関数が役に立つ。（セルA1に入力されている文字列から空白を除外する場合）
=SUBSTITUTE(SUBSTITUTE(A1," ",""),"　","")

OFFSET関数

OFFSET関数は覚えておいた方がよい！具体例は省略するがこの関数がExcelで何かを作成している最中にブチ当たった大きな壁を壊してくれることがある。
参考サイト（私はこのサイトに何度も助けられている･･･ありがとうございます！）
http://officetanaka.net/excel/function/function/offset.htm

以上で関数の紹介は終わり。最後に業務効率を上げるための基本操作、「作業内容が消えてしまった･･･（ﾟдﾟlll）」などという悲しい結末にならないための基本操作を紹介する。

基本操作

便利なショートカット

上のセルと同じものを入力する場合
「Ctrl + D」
セルA1の値をセルA2にコピーする場合、セルA1で「Ctrl + C」セルA2で「Ctrl + V」とするのではなく、セルA2で「Ctrl + D」とするだけ。セルA1の値をセルA2からセルA100までコピーしたい場合は、コピー元となるセルA1からセルA100までを範囲選択し「Ctrl + D」でOK。

左のセルと同じものを入力する場合
「Ctrl + R」
使い方は「Ctrl + D」と同じ。

表全体を一発選択
「Ctrl + A」
Excel以外のアプリケーションで「Ctrl + A」というショートカットを すべて選択 という概念で普段多用している方は、この使い方を知らない場合が多い。大きな範囲の表データを端から端までドラックして選択していた方は今すぐやめよう。

上書き保存
「Ctrl + S」
何時間もかけて作成したファイルが消えた･･･とならぬようこまめに保存をするのは鉄則。このショートカットをこまめに実行しよう！一度も保存していないExcelファイルに対して「Ctrl + S」を実行すると名前を付けて保存のダイアログが表示される。

0 件のコメント:

コメントを投稿