VLOOKUP応用、複数列に一致するものを抽出したい

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

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

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

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

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

20180420

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

20180420a

続きを読む

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

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

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

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

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

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

20180414f

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

続きを読む

チェックボックスにチェックしたところだけを合計する(Excel2016-2010)

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

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

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

20180414e

続きを読む

指定した月の月末営業日を取得する(土日祝日を除く)

月末を取得したり(EOMONTH)、土日祝日を除いた営業日の日数を計算する(WORKDAY)、という関数はそれぞれ存在していますが、当月末の営業日を取得したい場合、少し工夫が必要です。

例えば今日が「2018年4月1日」だった場合の、月末の営業日「2018年4月27日」を取得してみたいと思います。

まず事前に祝日を書き出したシートを別途用意しておきましょう。

下図のような簡単な記載で良いです。

20180407g

必要なのはA列だけなのですが、B列のようにメモを入れておくと自分でも分かりやすいです。

5月以降も必要なぶんを適宜入力してください。

では別のシートで実際に式を入力していきます。

続きを読む

秒数を「〇分〇秒」の形式に変更する

時間の計算は意外と分かりにくかったりします。

秒数を分単位に変更する場合、単純に「秒数÷60」では値は出ません。

以下のような式にします。

=秒数÷60÷60÷24

例えばA列に秒数が入っていて、下図のB列のように式を入力します。
(式はB2に入力したら下方へコピーすれば良いです)

すると結果がC列のような表示になります。

20180407

このままでは分単位になりませんので、セルの書式設定から表示形式を変更します。

続きを読む

分単位を時間に「〇時間〇分」の形式に変更する

時間の計算は意外の分かりにくかったりします。

分単位で表記されているものを時間の形式に変更してみます。

下図をご覧ください。

A列に分単位の数値を入れてる場合B列のように式を入力します。

=分÷1440

20180407d

※この「1440」の数値がどこから出てきたのかというと、
=24(時間)×60(分)=1440
という計算です。

分を1440で割っているので値はC列のような少数表示になります。

これを「セルの書式設定」の「ユーザー定義」で、「標準」のところを下記のように修正します。

続きを読む

Excel2016 条件付き書式で切り替えカレンダー、スケジュール表の作り方

Excelでスケジュールを管理する時に便利なカレンダーの作り方です。

月を変更するだけで曜日や土日祝日の塗りつぶしが自動的に変わるように作れますので、ひとつ作成しておけばシートコピーで何か月ぶんも作成できますので楽チンです。

完成系は下図ように作成してみたいと思います。(一番下にファイル添付あり)

20183021

それでは作成してみます。

続きを読む

Excel2016 列単位で並べ替え(横に移動させる)

並べ替えをするときに、行単位で並べ替える方法はよく知られています。

フィルタを使用したり「並べ替え」の機能でできますね。

列単位で並べ替えもできるのですが、意外と知られてないようです。

以下のような図の中で単純に昇順降順の並べ替えもできますが、今回は左側から項目を指定して並べ替えしてみます。

20180319a

続きを読む

指定した条件の中から最小値を抽出する(MIN)ゼロを無視する

MIN関数で最小値を抽出する際に、ゼロが入っていると、そのゼロを抽出してしまいます。

ゼロは無視した中から最小値を抽出したい場合、さらに項目を指定して抽出する場合の関数です。

下図のように「りんご」と「みかん」があります。

「りんご」の中からゼロを無視した最小値を抽出します。

20180319

続きを読む