この記事の目次

1. まずは、各シートの関係性をもう一度
2. 使用した関数を、やりたかった事も交えて説明
   1. ・入力シートのセルを参照シートで自由に参照したい
   2. ・参照したセルを元に、セルを数えたい
   3. ・参照したセルを元に、計算したい
   4. ・表示の調整をしたい
   5. ・余談：IF関数で指定する「偽の場合」が無いけど条件指定したい
3. まとめ

当社で行った業務効率化の過程で、事務仕事の効率化も同時に行いました。

業務効率化の為に作成したエクセルシートを、画像を交えて前回紹介しました。

上記のリンクから行ける記事はエクセルシートの事を書いているのですが、シートを作る時に心がけることを書いただけです。

エクセルのキモである関数については書いていないので、今回書く事にします。

実際、エクセルで知りたい事って関数の内容か実際の使用方法だと思いますし。

なので、今回の記事は関数の実際の使い方であったり、数ある関数の中でこの関数を選んだ理由などについて書いて行きます。

前回の記事で当社が作成したシートを3種類掲載しました。その関係性の復習をします。

簡単に説明すると、入力シート→参照シート→合計シートという流れでデータが反映されていきます。

参照シートでは入力シートの内容を元に計算した結果を表示したかったので、実は設定シートというものも用意しています。

計算や参照の元になるデータを入力しておくシートという意味合いです。

これまでに説明したことを図解すると、下図のとおりです。

この関係性をもとに、各シートに数値を表示または反映していきます。

さて、まずは入力シートへ入力した内容を参照シートにそのまま反映させたいと思いました。

回りくどいですが、入力シートへ入力した内容を、参照シートの指定セルに表示させたいということです。

当社はウォータージェット工事が本業なので、現場ごとに管理出来ると現場作業員側が分かりやすいというのがあります。

なので、用意した現場毎のシートに入力シートの内容が参照されると楽です。

それを実現できる関数を考えて行きます。

・入力シートのセルを参照シートで自由に参照したい

入力されたデータを参照シートで参照するにあたり、必要な条件が「現場を特定できる」こと、「参照シートで表示するセルを特定できる」ことです。

要するに、指定したシートの指定したセルに参照するということです。

内容を参照する関数はVLOOKUPなどがありますが、指定するためのデータ処理をする必要があったりで、少し使い勝手の悪さがありました。

入力シートの入力はコピペで済ませたいので、データ処理の為のセルを用意しづらいというのもありました。

と考えた時に、INDEX関数とMATCH関数を使う事にしました。

INDEX関数：行と列の指定した位置を求められる関数

INDEX（配列,行番号,列番号）
配列→参照したい値のあるセルの範囲を指定します
行番号→指定したセルの範囲で何行目に参照したい値のあるセルがあるかを指定します
列番号→指定したセルの範囲で何列目に参照したい値のあるセルがあるかを指定します。

INDEX関数は、セルの位置を指定して参照します。

MATCH関数：検査値の相対位置を求める関数
MATCH（検査値,検査範囲,照合の種類）
検査値→検索する値を指定します
検査範囲→検査値を探す範囲を指定します
照合の種類→今回は一致する値を参照したいので、0を指定します。

MATCH関数は、参照するセルを探す役割をしてもらいます。

要するに、MATCH関数で条件に当てはまるセルを探して、INDEX関数で参照するという流れです。

ある意味条件を指定して自由に参照セルを探す事ができる、自由度の高い関数ですね。

INDEX関数とMATCH関数を使用した参照の流れは、行を探す、列を探す、交わるセルが参照値となる、該当の参照シートに参照するとなります。

まず、設定シートと入力シートの日付で参照する行を探します。

次に、参照シート上で参照したいセルの内容が入力されている、入力シート上の列を探します。

行と列が交わったセルが参照する値になりますが、これだけだと複数のセルが当てはまってしまいます。

絞り込むのために、入力された内容とその内容を参照したいシートを合わせるためにもう一つの条件をつけます。

この画像の例では現場名をその条件として指定しています。

その結果、参照シート上の日付の行に自動で反映されます(緑の矢印です）。

もちろん、参照シートが複数あっても現場名で自動的に振り分けられるようにできます。

これで、入力シート→参照シートの流れは出来ました。

ごちゃっとして画像で分かりにくかったらごめんなさい。

・参照したセルを元に、セルを数えたい

入力シートの内容を参照シートに反映できたので、次は参照したセルを計算して行きます。

まずは、ただ単に数えます。現場での作業員とかですね。

誰がどの現場に行ったかまでを管理したいので、これも参照して各セルに反映されている状態です。

COUNTA関数：データの個数を求める関数
COUNTA（引数）
引数→セルを指定します。範囲指定する事が出来ます。

COUNTA関数でデータの個数を数えることで、範囲内に入力されているセルの合計を出します。

当社のシートで言えば、該当の現場に行った作業員の人数が分かるといったところです。

ちなみに、セル内に入力されているデータの内容を参照するので、各セルに関数が入っているとデータとして扱われ、空白のセルも数えられてしまいます。

なので、空白セルの個数を数えてくれるCOUNTBLANK関数で足したり引いたりしながら正確な数値を出して行きます。

・参照したセルを元に、計算したい

参照したセルの内容を元に、計算した数値も出せれば電卓を叩く必要もありません。

コピペで完了を目指すには外せないポイントです。

単純に＋、－、×、÷がほとんどですが、参照したセルと設定シートで指定した数値で計算されるように書きます。

合計値はSUM関数を使用して出します。

SUM関数：数値の合計を求める関数
SUM（引数）
引数→合計するセルの範囲を指定します。

良く使われる関数ですね。範囲内の数値を合計してくれる便利な関数です。

・表示の調整をしたい

参照や計算についてはほぼ終わったので、見やすい表にしたいと思いました。

見難くなる原因は、0（ゼロ）が表示されていたり、エラー表示されているなど、不必要な情報が表の中に多数ある事です。もちろん、元々の表の作り方もありますが。

なので、単純に条件分岐させて調整しました。

「計算結果が0（ゼロ）の時は表示しない」とか、「参照出来なくてエラーが表示されるときは表示しない」という感じです。

IF関数：条件によって利用する式を変える関数
IF（論理式,真の場合,偽の場合）

論理式→条件設定です。例えば、A1＝0といった感じです。
真の場合→論理式で指定した条件と同じ場合に実行する動作です。
A1＝0の時、空白にするという指定をします。
偽の場合→論理式で指定した条件と違う場合に実行する動作です。
A1=0じゃない時、1+1の結果を表示するという指定です。

ちょっと分かりにくいので、この例を書いて見ると、

という数式の書き方となります。

この式を書いたセルに、A1に入力された内容が0の時は何も表示されず、0以外の時は1+1の計算結果である2が表示されます。

これを利用して、計算結果が0（ゼロ）の時は何も表示しないように設定します。

IFERROR関数：エラーの場合に返す値を指定する関数
IFERROR（値,エラーの場合の値）
値→今回の場合は参照した結果がエラーかを調べるので、参照する数式を書きます。
エラーの場合の値→値で書いた内容がエラーになる時の動作を指定します。

今回作成したシートでは、参照結果を表示させることがほとんどです。

そのため、参照するセルが見つからずほぼエラーが表示されてしまいます。

なので、エラーを表示させないためにこの関数を使用します。

これで表示調整もされ、見難くなる原因は取り除けています。

・余談：IF関数で指定する「偽の場合」が無いけど条件指定したい

IF関数を使用する場合、真の場合と偽の場合を指定する必要があるので、関数が少し複雑になります。

毎回、真の場合と偽の場合を指定するので長くなるからです。

また、IF関数の真の場合と偽の場合の両方でさらにIF関数を使い、複雑な条件の指定もできます。

しかし、IF関数の中でさらにIF関数を使用する（ネストと言います）のに使える数の上限があります。

真の場合の値だけ設定し、偽の場合は次の真の場合を判別するという関数があります。

IFS関数：複数の条件を順に調べた結果に応じて値を返す関数

IFS（論理式1,真の場合1,論理式2,真の場合2,・・・）
論理式1→条件設定です。これはIF関数と変わりません。
真の場合1→論理式で設定した内容が一致したときに返す値です。

IF関数では指定する必要のあった偽の場合を指定しない代わりに、偽の場合であれば次の条件を判定していくという流れです。

つまり、条件式と真の場合が1セットになっていて、当てはまる条件になるまで1セットずつ確認していく感じです。

なので、条件を複数設定する必要があるけど、偽の場合の動作は一つだけといった場合はIFS関数の方が良いでしょう。

注意点としては、エクセル2016より前のバージョンではこの関数は使えないことです。自社内でもありましたが、使う場合には社内の各PCのエクセルのバージョンに注意しましょう。

以上が前回の記事で紹介した当社のエクセルシートの中身です。

今回と前回の記事を書くきっかけは、エクセルシートの作り方を教える機会があり、気づいたことがあったからです。

①エクセルシートで何を実現したいか
②実現するためにどのような機能が必要か
③どの関数が②の機能を実現出来るか

というのが課題になると気づきました。

なので、自社でも少し時間を掛けて作成した事により、50分掛かっていた事務作業が10分で終わる様になったので、今回紹介してみました。

エクセルシートなど、作業の効率を上げる為のものは作り上げるまでが大変です。しかし、作ってしまえばその分の恩恵を受けることができます。

もし、エクセルシートでお困りでしたら、ご相談に乗りますのでお気軽にお問い合わせ下さい。

参照：Excel関数一覧 機能別 – 仕事で使えるエクセルの全476関数を網羅！ | できるネット様