分を小数点に変える、かつ30分未満を0.5、30分以上を1として丸める

時間の表示を例えば「98:30」(98時間30分)と表示してるとします。(セルの書式設定のユーザー定義で[h]:mmとしてます)

これを「98.5」と小数点表示にしたい、かつ30分未満なら0.5で、30分以上なら1として表示したい場合です。

「98.5」なら分が30分以上になるので「99」の表示になります。

以下のようになります。

続きを読む

検索したい数値(文字列)が比較したいデータの何行目にあるのか求める

例えば2つの比較したいデータがある時に、「〇〇〇」というものがもう片方のデータの中のどこにあるのか?を探すときに便利です。

ここでは式の作りを分かりやすく把握するために、同ファイル同シート内に2つのデータを作成して比較しています。
別シートや別ファイルでももちろん作成可能です。
画像はExcel2016を使用していますが、古いバージョンでもMATCH関数はあるので互換性があります。

下図のような場合

20200102

続きを読む

【Excel2016 MAXIFS/MINIFS】複数条件を指定した最大値/最小値が簡単に計算できるようになった!

Excel2016やoffice365 Pro Plus で使えるようになったMAXIFS関数MINIFS関数です。

従来は条件を指定した中で最大値や最小値を出すのは結構大変でした。
※古いExcelバージョンでは以下の式を使ってください。

指定した条件の中から最大値を抽出する(MAX)
指定した条件の中から最小値を抽出する(MIN)[0]を無視する

MAXIFS関数MINIFS関数を使ってみます。

下図のような表があり、その中から条件を指定して「最大値」「最小値」を求めていきます。

20191231_4

続きを読む

【FILTER関数】検索値が範囲内で重複してる場合でも、すべて違った値を抽出できる【SORT関数】

Excel2016やoffice365 Pro Plusで使える関数で、FILTER関数というものです。

選択範囲内で検索値が重複してある場合、VLOOKUPでは検索した一番最初の結果しか返してくれませんので、2個目以降の値を返してくることができません。

今までは配列関数を使って「VLOOKUPではできない、検索値が範囲内で重複している場合」のように複雑でしたが、FILTER関数を使えば非常に簡単に抽出できるようになりました。
フィルター機能で抽出するような感じで取り出すことができます。

例えば以下のように表があります。

20200102_3

続きを読む

【Excel2016 SWITCH関数】条件から結果を抽出するのに便利、IFやVLOOKUPの合わせ技のように使える

Excel2016やoffice365 Pro Plusで使える便利な関数です。

新しいIFS関数は照会しましたが、求める値が「Aならば◎、Bならば△・・・」などと固定してるものならば、SWITCH関数が便利です。
VLOOKUP関数のように別のセルに表を作成しておく必要もなく、従来のIF関数のように何度も何度もIFやカッコを入れ子にする必要がありません。

例えば以下のような表があります。

20191231_7

続きを読む

【Excel2016 IFS関数】 IF関数の複数条件が簡単になった!条件によって値を変える方法

Excel2016やoffice365 Pro Plusで使える便利な関数です。
今まで条件によって答えを変える場合、例えばAという条件なら答えは◎、Bという条件なら答えは△・・・などと振り分けるとき、
=IF(A2="A","◎",IF(A2="B","△",IF(A2="C"・・・
とIF関数を何度も何度もネスト(入れ子)しないといけませんでした。
Excelのバージョンによってはネストできる個数も少なくなりますし、結構面倒でした。

あらたに使えるようになったIFS関数ではこの煩わしさが解消しました。
条件のネストも127個まで指定できるようになりました。

例えば以下のような表があったときの条件式を作成してみます。

20191231

続きを読む

VLOOKUP関数 複数列の条件に一致するものを抽出したい

抽出したいものが数値で合計を出したい場合はSUMIFS関数を使えば簡単にできますが、抽出したい値が文字列だった場合はどうしたら良いか?

VLOOKUPをそのまま使うと検索値はひとつだけしか指定できません。

これを応用して、複数列の中から検索値に合致する値の文字列を抽出する方法です。

例えば下図のような表があります。

左端には必ず番号を振っておくのがポイントです。

20180420

ここから下図のようなデータに一致するE列の値を抽出してみます。

20180420a

続きを読む

時間の引き算をするとマイナスの結果が####になる場合の関数

時間の引き算をすると結果がマイナスになる場合もあります。

その時に値が「######」となり、正しく表示されません。

例えば下図のように、D列の終了実績時刻からC列の終了予定時刻を引いた結果をE列に返すとき、

D>Cの数値ならば時刻の表示ができますが、

D<Cの数値だと結果がマイナスになるため値が表示できません。

20180414f

その場合は以下のような関数を使うと結果を表示することができます。

続きを読む

チェックボックスにチェックしたところだけを合計する

複数の項目があり、選択する項目が毎回変わる場合などに、チェックボックスで選択したところだけを合計できるようにする方法です。

※事前に「開発」タブを表示したいので、以下の確認をしておきます。
リボンの適当なところで右クリックし「リボンのユーザー設定」をクリックします。
表示された画面の右側にあるチェックボックスの中から「開発」にチェックを入れてOKします。

では、下図のような表を作成してみます。

20180414e

続きを読む