MS エクセルの関数を見ていたら気分が悪くなってきた

アプリ/ツール

エクセルって表作ったりとかちょっとした計算に使えて便利だよね。 とくに同じような計算させるときとか一つ式つくってセルの右下の小さい■をずら~って下に引っ張ると簡単に計算できちゃうし。 計算させたい行が多いとちょっとだるいけど。
オートフィル機能ですね。 もし、計算したい行に飛びがなく連続していれば、小さい■をダブルクリックすれば一番下の行まで行けちゃいますよ。
そうなの?もっと早く教えてよ! それじゃさ、B1とB4のセルの計算結果をC4にだして、B1とB5のセルの計算結果をC5にっていうのをオートフィル機能でできたら便利なんだけど、それやっちゃうと式がおかしくなっちゃうんだよね。 だから、今まではB1に入れたかった値をC列にいれて計算してたんだけど、もしそこを変えないといけなくなると全部の行を変えないといけなくなるから、めんどくさいなーと。

こんなイメージですね。
そう!そんなイメージ。

ということで、今回はExcelで覚えておくと便利な小技や関数についていくつか書いていきたいと思います。 たまに?ってなる言葉が出てくるかもしれませんが、実際に使うときは言葉は関係ないので「あ~そんな名前なのね」ってくらいで覚えておいてもらえればいいかなと思います。

参照するセルを固定する

ではまずは、上であった参照するセルを固定する方法から。

行も列も絶対動いちゃいや!【絶対参照】

オートフィルを行ったり、式が書いてあるセルをコピーして別のところに貼り付けると式内の番地(A1とかB1とか)が変わってしまいます。

たとえば、 C1のセルに=A1+B1という式があり、これをオートフィルすると C2のセルは=A2+B2 C3のセルは=A3+B3となります。

また、 C1のセルをコピーしてH6に貼り付けたとするとH6内の式は=F6+G6というようになります。 これを相対参照と言います。

それでは、次のようにオートフィルやコピーしてもA1は変えたくないというときは…

C1のセル:=A1+B1
C2のセル:=A1+B2
C3のセル:=A1+B3
H6のセル:=A1+G6

C1のセルに式を書くときに=$A$1+B1という風に書きます。

このがついていると絶対参照だよってことになります。 この状態でオートフィルやコピーをすると

C1のセル:=$A$1+B1
C2のセル:=$A$1+B2
C3のセル:=$A$1+B3
H6のセル:=$A$1+G6

となるはずです。

行だけ固定。列だけ固定。【複合参照】

次のような場合もしB列だけに結果を出したいのであれば、B3に=A3*$B$2と式を入れオートフィルをかければよさそうですが、それをそのままC列に持っていくとやっぱり=B3*$B$2となりおかしくなります。

では逆にB3に=&A$3*B2と式を入れて横方向にオートフィルをかければ3行目はできそうですね。 でも、やっぱり4行目にそのまま持っていくと=&A$3*B3となりおかしくなります。

もう!結局式書き直さないといけないじゃん!

まぁ、そうなりますよね。でも$は行だけもしくは列だけを指定して固定することができます。 これを複合参照と言います。

今回の例ではB3のセルに=$A3*B$2と式を入れます。 $A3では、A列は絶対に参照したいけど、行はセルの場所に合わせて変えたいな。 B$2では、列はセルの場所に合わせて変えたど、2行目からは絶対に目を離したくない。 ということになります。 それでは、オートフィルしてみましょう。

ちなみに$を入れたり消したりするのって、やってるうちに面倒くさくなってくるんですよね。 そんな時は、式内で参照先を切り替えたいセルのところにカーソルを持っていきF4を押してみてください。すると

F4を押すたびに【絶対参照】⇒【複合参照(行)】⇒【複合参照(列)】⇒【相対参照】と切り替わります。これでちょっと楽になりますね。

文字列を操作して綺麗にまとめる

文字列を操作といってもいまいちピンとこないかもしれませんが、いくつか例を紹介しながら書いていきたいと思います。 きっと「あ~こんなときあるわ~」ってあると思いますよ。

先頭文字が欲しい【LEFT関数】

たとえば下図のように文字列の最初だけや最初から何文字かを他のセルに移したい使用したいときがあったとします。

こんな時は文字列の先頭から指定された数の文字を返すLEFT関数を使用します。 式の書き方は=LEFT(文字列,文字数) 今回はB4のセルの一文字めだけ取り出したいので、=LEFT(B4,1)としました。

そしてオートフィルをかけるとこんな風に。

あれれ?買い物リストにも凡例が残っちゃってるよ。
参照セルの一文字めを移動したわけではなく、LEFTという式の結果はこうだよって表示しているだけなので、参照セルはそのままになります。 ですので、参照セル(ここではB列)の中の凡例を消してしまうとA列のLEFTの結果は”牛”や”ヨ”になってしまいます。

でも、B列の凡例は消したいですよね。そんな時はA列をLEFT関数の結果ではなく文字になおしておきます。 先ほどオートフィルしたLEFT関数で凡例を表示している範囲をコピーしてそのまま同じところに値に指定して貼り付けます。

これでA列にLEFT関数で表示されていた凡例は文字に変わりました。 次はB列の凡例をなくしていきましょう。

文字を自由にとりだしたい【MID関数】

本当は「一文字めを消したい」んですが、ここでは視点を変えて「一文字めをのぞいた値をとりだす」といったことをやってみます。

こんな時は文字列の指定した位置から指定された数の文字を返すMID関数を使用します。

式の書き方は=MID(文字列,開始位置,文字数)

今回はB4のセルの二文字め以降を取り出したいので、=MID(B4,2,10)としました。

文字数については、取り出したい文字の中でもっとも多い文字数以上に設定しておけばよいです。

今回の場合だと「ヨーグルト」や「じゃがいも」が一番文字数が多く5文字なので5以上にすればよいのですが、買い物リストが多くていちいちどれが一番多いかなんて数えるのは大変なんてこともあるので、これくらいあればいいかなってことで10文字にしてます。 つまり実際の文字数より多く設定しても問題ありません。他にも方法はあるんですが、別の関数で説明しますね。

では先ほどと同じようにオートフィルをかけてみます。

はい、できました。 あとはMID関数で買い物リストを表示しているF列の範囲をコピーしてB列に値指定で貼り付けて、いらなくなったF列を削除して表を整えれば完成です。

文字列の最後を取り出したい【RIGHT関数】

RIGHT関数はLEFT関数とは反対に文字列の末尾から文字数を指定します。 式は=RIGHT(文字列,文字数)

比較して表内を整理

一つひとつ見比べて結果を記入したいなんて思ったらExcelに任せておけばいいんです。 それが仕事ですから。

大きい?小さい?同じ?【IF関数】

よく見かけるテストの点数を例に説明していきます。面白い例がないかなぁと思ったんですが無理でした(笑) さて、ここにテストの結果表がありますが合格しているのかそれとも不合格なのか一目でわかるようにしたいと思います。

合格or不合格を合否判定欄に入れていきますが、ここでIF関数を使います。合格点はB1にある80点以上です。この合格基準にそってB列の点数を比較しC列に結果をだしていきます。

式は=IF(論理式,真の場合,偽の場合)となります。論理式には比較したい値の関係を示します。

真の場合には論理式が正立した場合にどういった処理を行うかを入れます。偽の場合には論理式が正立しなかった場合にどういった処理を行うかを入れます。

今回の例では「各個人の点数」が「合格点以上」かどうかを比較したいのでB列の点数とB1を「>=(以上)」を使って、論理式のところにB3>=B$1と入れます。
※オートフィルしたときにセルが動かないように$を忘れないようにしましょうね。

次に真(点数が合格点以上)の場合に「合格」と表示したいので“合格”とします。
※文字列を入れる場合は”(ダブルクォーテーション)で囲みます。

最後に偽(点数が合格点に未満)の場合に「不合格と」表示したいので“不合格”とします。 それらを合わせるとこのような式になりました。 =IF(B3>=B$1,”合格”,”不合格”)

ちなみに論理式では次の演算子がつかえます。

Point

  • = :等しい
  • <> :等しくない
  • > :大きい
  • < :小さい
  • >= :以上
  • <= :以下

それではオートフィルしてみます。ちゃんと結果がでていますね。

でももっとわかりやすくしてみたいですよね。そんなときは次の条件付き書式もやってみましょう。

条件付き書式でもっとわかりやすく

条件付き書式を使うと視覚的にもっとわかりやすくなります。 それはもう、パッと見でわかるようになります。 では条件付き書式から「新しいルール」を選びます。

次に「数式を使用して、書式設定するセルを決定」を選びます。

ルールの内容を編集します。 今回C列の合格or不合格を基準に書式設定したいので、ひとまず=C3=”合格”と入力し書式の設定でセルの塗りつぶしを緑にしました。

ここで「OK」を押してもなにも変わりません。。。 適用範囲を広げます。もう一度条件付き書式を開きルールの管理を開きます。 すると先ほど登録したルールが表示されるので、適用先を合格or不合格の入ったセル全体に広げます。

適用先を変更したら、「OK」を押して終了します。すると合格のところだけセルの色が変わりました。

また、同じ手順で不合格の方も書式を変えれば、こんな風になりますよ。

これ何個あるんだ…目がしょぼしょぼしてきたら

これ何文字あるのかな。とか同じ条件のものはあるのかな。とか画面上で数えてたらキリがないですよね。 そんな時におススメな関数です。

数値が含まれるセルの個数を数える【COUNT関数】

ではまた、テストの結果を例に集計してみます。

新しく参加者数、合格者数、不合格者数を追加しました。ここでは、数値が含まれるセルの個数を返すCOUNT関数を使って参加者数を出そうと思います。

式は=COUNT(値)です。値とかかれるとわかりづらいですが、数値が含まれているセルの数を数えたい範囲を指定します。

今回の場合は点数の記載がある=参加者数と考え=COUNT(B3:B13)としました。

これで参加者数(=点数の記載がある人数)が表示されました。

同じ条件を数える【COUNTIF関数】

さっきのIF関数とCOUNT関数を合わせたような関数です。

さっきは合格者と不合格者を判別したり色分けしたりしましたが、それぞれの人数がわかれば割合とかの計算もできちゃいますよね。

式は=COUNTIF(範囲,検索条件)です。

ではまず合格者数を数えてみます。 範囲は合否判定の一覧なのでC3~C13までで数えたい対象は合格なので=COUNTIF(C3:C13,”合格”)としました。

同様に不合格者数を数えてみます。 式は=COUNTIF(C3:C13,”不合格”)です。それぞれの結果を見ると

結果は合格者数5人、不合格者数6人でした。そしてCOUNT関数で参加者数は11人となるので、これでそれぞれの割合が出せますね。

文字数なんて数えることってあるかな?意外とあるんです【LEN関数】

最後に文字列の文字数を返すLEN関数を紹介します。 式は=LEN(文字列)になります。 実例は文字を自由にとりだしたい【MID関数】です。 【MID関数】のときには文字数を決め打ちで10としました。

でも、やっぱりもっと買い物リストの数が多かったら一番長い文字は何文字かなんて探せないかもしれませんよね。そんな時にLEN関数を使います。 今回はMID関数の文字数のところをLEN(B4)とします。

これであとはオートフィルをかければ、MID関数のときと同じ結果になりました。

結果は同じですが、文字数を気にしなくて済む汎用性のあるものになりましたね。

まとめ

どうでしたか?長々とエクセルの関数ばかり見ていたら、気持ち悪くなりませんでしたか。 僕はこの記事を書きながら、かりんやFB-800の存在を出し忘れるほど気持ち悪くなりました(笑) でも、ちょっとした作業のときにも使える小技や関数なのできっと役に立つと思います。

ちょっとぉ!わたしたちの出番ほとんどなかったじゃない! しかも思い出したかのように最後の最後でだしてくるんだから。
そんなこと言わないで。。。 きっと説明に夢中になってしまっただけなので。

コメント

タイトルとURLをコピーしました