列内に複数の項目がありランダムに重複している場合。
例えば累計を出しているので、最終行だけを抽出して合計を出したい、といった場合などに便利です。
今回は、下図の赤字部分を合計してみます。
時間の引き算をすると結果がマイナスになる場合もあります。
その時に値が「######」となり、正しく表示されません。
例えば下図のように、D列の終了実績時刻からC列の終了予定時刻を引いた結果をE列に返すとき、
D>Cの数値ならば時刻の表示ができますが、
D<Cの数値だと結果がマイナスになるため値が表示できません。
その場合は以下のような関数を使うと結果を表示することができます。
複数の項目があり、選択する項目が毎回変わる場合などに、チェックボックスで選択したところだけを合計できるようにする方法です。
※事前に「開発」タブを表示したいので、以下の確認をしておきます。
リボンの適当なところで右クリックし「リボンのユーザー設定」をクリックします。
表示された画面の右側にあるチェックボックスの中から「開発」にチェックを入れてOKします。
では、下図のような表を作成してみます。
月末を取得したり(EOMONTH)、土日祝日を除いた営業日の日数を計算する(WORKDAY)、という関数はそれぞれ存在していますが、当月末の営業日を取得したい場合、少し工夫が必要です。
例えば今日が「2018年4月1日」だった場合の、月末の営業日「2018年4月27日」を取得してみたいと思います。
まず事前に祝日を書き出したシートを別途用意しておきましょう。
下図のような簡単な記載で良いです。
必要なのはA列だけなのですが、B列のようにメモを入れておくと自分でも分かりやすいです。
5月以降も必要なぶんを適宜入力してください。
では別のシートで実際に式を入力していきます。
時間の計算は意外と分かりにくかったりします。
秒数を分単位に変更する場合、単純に「秒数÷60」では値は出ません。
以下のような式にします。
=秒数÷60÷60÷24
例えばA列に秒数が入っていて、下図のB列のように式を入力します。
(式はB2に入力したら下方へコピーすれば良いです)
すると結果がC列のような表示になります。
このままでは分単位になりませんので、セルの書式設定から表示形式を変更します。
時間の計算は意外の分かりにくかったりします。
分単位で表記されているものを時間の形式に変更してみます。
下図をご覧ください。
A列に分単位の数値を入れてる場合B列のように式を入力します。
=分÷1440
※この「1440」の数値がどこから出てきたのかというと、
=24(時間)×60(分)=1440
という計算です。
分を1440で割っているので値はC列のような少数表示になります。
これを「セルの書式設定」の「ユーザー定義」で、「標準」のところを下記のように修正します。
Excelでスケジュールを管理する時に便利なカレンダーの作り方です。
月を変更するだけで曜日や土日祝日の塗りつぶしが自動的に変わるように作れますので、ひとつ作成しておけばシートコピーで何か月ぶんも作成できますので楽チンです。
完成系は下図ように作成してみたいと思います。(一番下にファイル添付あり)
それでは作成してみます。
MIN関数で最小値を抽出する際に、ゼロが入っていると、そのゼロを抽出してしまいます。
ゼロは無視した中から最小値を抽出したい場合、さらに項目を指定して抽出する場合の関数です。
下図のように「りんご」と「みかん」があります。
「りんご」の中からゼロを無視した最小値を抽出します。
フィルタで抽出した結果を計算するときにSUBTOTALを使うと思います。
しかしSUBTOTAL内で指定できる集計方法11種類の中にはCOUNTIFがありません。
例えば担当者「A」で抽出した結果の中から特定の商品を指定してカウントしたい、ということがあると思います。
値を出したいものがひとつだけならば、さらに商品の列でフィルタをかければ良いことですが、担当者「A」で絞り込んだときに同時に複数の商品をカウントしたい、という場合には、いちいち商品ごとにフィルタをかけ直すのは面倒ですね。
こういった時に便利な関数です。
下図のように各担当者ごとに商品があります。
文字列の中間に数字がある時に取り出す方法は「文字列の中から数字を取り出す(数字が中間にある時)」で記載しましたが、こちらは数字の前後にカッコ「」など検索しやすい記号がある場合に使うと、式が多少短くなるのと配列ではなくなるので使いやすいと思います。
数字の前後にカッコ「」がついてる例だと以下のようになります。
"あいうえおかきくけこ「123」"