115:仕事でよく使うエクセルの式を紹介

100:エクセル

()括弧のなかに入れるものを、引数(ひきすう)といいます。引数を複数必要となる場合には、”,”(カンマ)で区切り、(引数1,引数2,引数3,・・・)となります。今のエクセルでは、”=”(イコール)を入れて式を入れるのが標準となっておりますが、”+”(半角たす)の方が、テンキーがある場合、”+”は一つのキーで賄えるので、”+”を入力するのを覚えたほうが仕事が早くなると思います。”=”はシフトを押さないと入力できないので、面倒です。

合計

合計 : +sum(範囲)

sumは、引数の順番とかなく、”,”(カンマ)で区切りれば、いつくでも選択できます。

普通は、”:”(コロン)で結んで、+sum(C10:C15)とか記載するのが一般的です。

ただ、セルが隣接しない場合や、行や列が複数にまたがる場合には、”,”(カンマ)を使って+SUM(C3,D4,E5:E8)のように使うこともできます。

個数

個数 :+count(範囲)

セルの中に数字がいくつあるかを数えるのが、+count()の式です。

また、数字も文字もなにか入っていれば数えるのが、+counta()です。

個数(条件にあうものだけ)

条件に合う個数 :+countif(範囲,条件)

この式は、引数の順番が決まってます。sumやcountとは”,”(カンマ)の意味が違います。

参照式 VLOOKUP関数

他のテーブルから参照:+vlookup( キーとなる値,検索テーブルの範囲.テーブルの列の相対位置,0か1)

サンプルのエクセルファイルを以下に入れておきました。

名称の個所はすべて+VLOOKUP()の式を立てておきましたので、その式を下にコピーして表を完成させてください。

説明

キーとなる値は、一つのセルを指定してください。 テーブルの範囲は、先に指定したキーとなる値が入っている列を一番左側になるように列を選択して、表示されたい値がはいっている列まで、範囲を広く選択してください。テーブルの横位置とは、選択されている列の最も左側の列を1.右に行くにつれて、2.3.4と数えて表示したい列の順番を入れてください。最後の0か1を入れる部分ですが、これは、何も考えずに0を入れるとしてください。1を入れると、近いものを持ってきてしまいます。無い時は、エラーを出してもらう方が良いので、なんとなくの数字は、普通はいりません。また、最後のこの引数は、省略できますが、省略すると、1とみなすようなので、いつも0を入れると覚えておいた方が良いです。

説明を加えます。まず、エクセルシートのマスターのシートを選んでください。左下にあります。

マスターのシートには、左側に数字、その右側に名称というテーブル(番号と関連されている内容が一覧表になっているもの)が4つあります。このテーブル毎が範囲に相当します。VLOOKUP(指定セル,範囲,左から番号,0)の範囲の部分です。小分類コードを例にとると、範囲は、”A4:B12”となります。

上記のエクセルはD4のセルの内容を、式の表示をしておりますが、

=+VLOOKUP(C4,マスター!$A$4:$B$12,2,0)

キーとなる値 : 引数1

上記()の中を説明をします。()の中の初めの引数が、C4とあるのが、キーになっている数字(この場合には文字)です。2つ上の小分類コードの左側にある数字と、上のシートにあるC4の数字に同じものがあるのがわかると思います。C4は、”030”です。2つ上のマスターシートの小分類テーブルにある”030”の隣に”動物”と入力してあるのがわかります。

範囲 : 引数2

なにやら、マスター!$A$4:$B$12 とすごく難しい内容が入っていますが、これは、マスターシートのA4からB12を絶対値しているだけです。この内容を直接手で入力する人はいないので、難しいようで超簡単です。まず、+VLOOKUP(C4,まで入ったら、マスターシートのタブを選択してマスターシートを表示して、A4:B12をマウスで選択してF4(ファンクション4)を押して絶対値にするだけです。絶対値にするのは、下にコピーしたときに相対値していだと、範囲も下に下がってしまうので、指定したいテーブルから外れてしまうことを防止するためです。

列番号 : 引数3

次の2は、左から数えて2列目を意味します。これは、テーブル内の左から数えてということで、今回のサンプルではすべて、2列からなっているテーブルなので、”2”が範囲の次の引数となってます。

最後の0 : 引数4

最後の0ですが、忘れずに必ず0を入れるようにしておいてください。+VLOOKUP()の式は、引数が3つでも成立しますが、4つめに必ず0を入れてください。

おまけ

それとわたしは、”+VLOOKUP(” を ”る” に単語登録しております。エクセルをよく使う人は、”る”で、一発で+VLOOKUP(がでるので、”る”で+VLOOKUP(を出したのち、漢字キーを外して、セルを選択していくことをお勧めいたします。

平均

平均:+average(範囲)

範囲の引数は、sumと同じ要領です。

IF関数

IF関数:+if(条件式,条件に合う時に表示するもの,条件が合わない時に表示するもの )

この関数も結構つかいます。他の人がピボットテーブルを作ったシートとか、標準のままで、項目が一番上にしかなくて、同じは、空白とかいうテーブルを扱うことがあります。

上のシートで、左側のテーブルから右のテーブルにしてデータベース形式としたい場合に、重宝する式です。

まず、集計行はいらないので、集計行がわかるようにしたいと思います。 D4のセルに=+IF(+RIGHT(A4,1)=”計”,”集計行”,””) と入れました。これは、計が最後についていると、集計行と表示しないさいという意味です。

また、製造区分は空白を埋めて、データベース形式とします。E4のセルに

=+IF(A4=””,E3,A4) と入れました。なにもなければ、今ある上のセルを持ってきて、なにか入っていればA4のセルを表示しなさい。という式です。

さらにサービスで、製造区分が同じであれば、累計していき、製造区分が変わったところで、累計がリセットされる列も作りました。F4には、

=+IF(E3=E4,F3+C4,C4) という式を入れて。すぐ上の行と同じであれば、足していくというものです。

どれも、相対値指定にしてあるので、最下部までコピーすることで、全部の計算ができます。

上記のように、どれも、if()関数で、可能です。

説明

条件式は、A3=5 とか入れます。これは、A3のセルに入っている数字が 数字の5ですか?と、聞いています。もしも、5であれば、[条件に合う時に表示するもの]を表示します。ここには、文字で有れば、””(ダブルコーテイション )で囲んだものを入れれば良いし、さらに式とか数字とか入れる事ができます。例えば、”正解”とか、5とかです。次の引数の[条件が合わない時に表示するもの]は、A3のセルに入っている数字が 数字の5では無い時に表示する内容です。

左からの文字列

文字列を左側から抜き取る:+left( 指定セル, 文字数)

右からの文字列

文字列を右側から抜き取る:+right( 指定セル, 文字数)

文字列の中の文字を抜き取る

文字列の真ん中の文字を抜き取る:+mid( 指定セル, 左から何番目, そこから右に何文字)

条件が合う場合だけ合計

指定した文字がテーブルの別の行にある場合、その条件があう数字の合計:+sumif(条件範囲, 条件のセル, 合計される数字が入った範囲)

例えば、先ほど使った、売上データのシートで、K55のセルに、+SUMIF(H4:H53,H53,K4:K53)と入れてみてください。H4:H5 は、条件の範囲で、この中からという意味です。次の H53 は、具体的に ”国産品” とかでも良いですが、だいたい条件の中に入っているものなので、ここでは、H53の”国産品”を条件としております。最後の K4:K53 は、売上金額が入った範囲です。つまり、「製造区分名の中で、”国産品”であるものを探して、その売上金額を合計してください。」という意味になります。あっているかどうかは、フィールド名でフィルターをかけて、国産品だけを出して、売上を合計してみて合っているか確認してください。

四捨五入

四捨五入:+round(セル, 桁数)

桁数は、小数点以下何桁という引数になります。この桁数を0にすれば、小数点第1位を四捨五入して整数で返します。この桁数を2にすれば、小数点3桁を四捨五入して、小数点2桁で返します。例えば、123.456 という数字を、+round(123.456,2) とすれば、123.46 を返すということになります。

ランダム数字を生成

ランダム:+rand()  引数は入れません。 0から1の間の数字です。

年齢の計算式

年齢の計算:=+YEAR(TODAY()-生年月日)-1900

上記の式を組み合わせる事でほとんどの仕事は、作業できると思います。

文字列も数字もくっつける「&」

文字と文字をくっつけることができますし、好きな文字を追加することもできます。

たとえば、文字と文字をつっくけた場合

D1に 「=+A1&B1&C1」という式を入れてあります。

こんなこともできます。文字の間に好きなものを入れた場合

県と市の間に” ”(ダブルコーテーションでスペースをくくったもの)、市と町のあいだに”の”を入れてみたもの

式ではありませんが、

数字が文字列として入っている時に数字に直したい解決方法 となりのセルに +(隣のセル)*1 と、1を乗じます。そのセルをコピーして値貼り付けします。

YouTube

115:仕事でよく使うエクセルの式を紹介

コメント

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