チェックボックスにチェックしたところだけを合計する(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

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

続きを読む

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

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

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

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

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

20180319

続きを読む

フィルタで抽出した結果の中からCOUNTIFを使いたい(SUBTOTALではできない)

フィルタで抽出した結果を計算するときにSUBTOTALを使うと思います。

しかしSUBTOTAL内で指定できる集計方法11種類の中にはCOUNTIFがありません。

例えば担当者「A」で抽出した結果の中から特定の商品を指定してカウントしたい、ということがあると思います。

値を出したいものがひとつだけならば、さらに商品の列でフィルタをかければ良いことですが、担当者「A」で絞り込んだときに同時に複数の商品をカウントしたい、という場合には、いちいち商品ごとにフィルタをかけ直すのは面倒ですね。

こういった時に便利な関数です。

下図のように各担当者ごとに商品があります。

20160316a_1

続きを読む

文字列の中から数字を取り出す(検索しやすい記号などがある時)

文字列の中間に数字がある時に取り出す方法は「文字列の中から数字を取り出す(数字が中間にある時)」で記載しましたが、こちらは数字の前後にカッコ「」など検索しやすい記号がある場合に使うと、式が多少短くなるのと配列ではなくなるので使いやすいと思います。

数字の前後にカッコ「」がついてる例だと以下のようになります。
"あいうえおかきくけこ「123」"

続きを読む

指定した値より小さいデータだけをまとめて抽出する

下図の左側にあるデータの中で、最低点が70より小さいデータのみを抽出したい場合。

0606

1回で抽出できなくても良いならば、例えば空いている列に70より小さい場合は[1]とフラグを立てるようにIF関数を入力し、オートフィルタで[1]の行を抽出、それをコピーして別シートにでも貼り付ければ出来上がります。

ただ毎回データが変わるとなると、この手間を繰り返さないといけないので面倒です。

データが変わっても常に抽出できるようにしておきたい場合は以下の関数で出来ます。

続きを読む