時間の表示を例えば「98:30」(98時間30分)と表示してるとします。(セルの書式設定のユーザー定義で[h]:mmとしてます)
これを「98.5」と小数点表示にしたい、かつ30分未満なら0.5で、30分以上なら1として表示したい場合です。
「98.5」なら分が30分以上になるので「99」の表示になります。
以下のようになります。
時間の表示を例えば「98:30」(98時間30分)と表示してるとします。(セルの書式設定のユーザー定義で[h]:mmとしてます)
これを「98.5」と小数点表示にしたい、かつ30分未満なら0.5で、30分以上なら1として表示したい場合です。
「98.5」なら分が30分以上になるので「99」の表示になります。
以下のようになります。
例えば2つの比較したいデータがある時に、「〇〇〇」というものがもう片方のデータの中のどこにあるのか?を探すときに便利です。
ここでは式の作りを分かりやすく把握するために、同ファイル同シート内に2つのデータを作成して比較しています。
別シートや別ファイルでももちろん作成可能です。
画像はExcel2016を使用していますが、古いバージョンでもMATCH関数はあるので互換性があります。
下図のような場合
Excel2016やoffice365 Pro Plus で使えるようになったMAXIFS関数MINIFS関数です。
従来は条件を指定した中で最大値や最小値を出すのは結構大変でした。
※古いExcelバージョンでは以下の式を使ってください。
指定した条件の中から最大値を抽出する(MAX)
指定した条件の中から最小値を抽出する(MIN)[0]を無視する
MAXIFS関数MINIFS関数を使ってみます。
下図のような表があり、その中から条件を指定して「最大値」「最小値」を求めていきます。
Excel2016やoffice365 Pro Plusで使える関数で、FILTER関数というものです。
選択範囲内で検索値が重複してある場合、VLOOKUPでは検索した一番最初の結果しか返してくれませんので、2個目以降の値を返してくることができません。
今までは配列関数を使って「VLOOKUPではできない、検索値が範囲内で重複している場合」のように複雑でしたが、FILTER関数を使えば非常に簡単に抽出できるようになりました。
フィルター機能で抽出するような感じで取り出すことができます。
例えば以下のように表があります。
Excel2016やoffice365 Pro Plusで使える便利な関数です。
今まで条件によって答えを変える場合、例えばAという条件なら答えは◎、Bという条件なら答えは△・・・などと振り分けるとき、
=IF(A2="A","◎",IF(A2="B","△",IF(A2="C"・・・
とIF関数を何度も何度もネスト(入れ子)しないといけませんでした。
Excelのバージョンによってはネストできる個数も少なくなりますし、結構面倒でした。
あらたに使えるようになったIFS関数ではこの煩わしさが解消しました。
条件のネストも127個まで指定できるようになりました。
例えば以下のような表があったときの条件式を作成してみます。
抽出したいものが数値で合計を出したい場合はSUMIFS関数を使えば簡単にできますが、抽出したい値が文字列だった場合はどうしたら良いか?
VLOOKUPをそのまま使うと検索値はひとつだけしか指定できません。
これを応用して、複数列の中から検索値に合致する値の文字列を抽出する方法です。
例えば下図のような表があります。
左端には必ず番号を振っておくのがポイントです。
ここから下図のようなデータに一致するE列の値を抽出してみます。
時間の引き算をすると結果がマイナスになる場合もあります。
その時に値が「######」となり、正しく表示されません。
例えば下図のように、D列の終了実績時刻からC列の終了予定時刻を引いた結果をE列に返すとき、
D>Cの数値ならば時刻の表示ができますが、
D<Cの数値だと結果がマイナスになるため値が表示できません。
その場合は以下のような関数を使うと結果を表示することができます。
複数の項目があり、選択する項目が毎回変わる場合などに、チェックボックスで選択したところだけを合計できるようにする方法です。
※事前に「開発」タブを表示したいので、以下の確認をしておきます。
リボンの適当なところで右クリックし「リボンのユーザー設定」をクリックします。
表示された画面の右側にあるチェックボックスの中から「開発」にチェックを入れてOKします。
では、下図のような表を作成してみます。