Excel。Aggregate。曜日別に金額を集計する方法を知りたいということで。その1。

Excel。曜日別に金額を集計する方法を知りたいということで。その1。

＜WEEKDAY&SUMIF関数　編＞

ちょっとしたご質問から、
なるほど、確かに現場ではほしいものなんだろうなぁ～というものが先日ありまして、
それが、今回ご紹介する、曜日別に金額を集計する方法なんです。

平日と週末とかでというのは、ありそうですが、
今回は、月曜日なら月曜日というように集計した金額を知りたいそうなんですね。

つまり下記のような表のようにしたいわけです。

ただ、売上表の曜日は、日付が変わったとしても、イチイチ修正したくないので、
日付と連動したいというリクエストも加わっています。

そこで、まず、表を作る所からポイントがありますので、ご紹介していきます。

B列の日付はそのまま入力しております。

C列の曜日ですが、ここは、連動させる必要がありますので、ここは、曜日を算出してくれる、WEEKDAY関数を使って数値を算出しております。

それでは、C4に数式を作っていきます。
WEEKDAY関数ダイアログボックスを表示します。

シリアル値は、B4を入力します。

種類は、1を入力します。

この種類によって、算出される数値が変わっていきます。
今回は、日曜日を1にしたいので、1と入力しております。

ちなみに、

1は、（日曜日=1～土曜日=7）

2は、（月曜日=1～日曜日=7）

3は、（月曜日=0～日曜日=6）

と設定されております。
数式をオートフィルを使ってコピーします。

曜日が数値として算出されました。

これでは、何曜日だかわかりませんので、曜日にしていきたいわけですね。

そこで、IF関数を使うのではなくて、表示形式を使って変えていきます。

C4:C18を範囲選択して、セルの書式設定ダイアログボックスを表示しましょう。

表示形式タブの分類をユーザー定義にして、
種類を aaa と入力してOKボタンをクリックしましょう。

すると、数値が曜日に替わりましたね。

さて、こちらの表は完成しておりますので、集計の表を作っていきましょう。

F4から、日から土と入力した場合はどうなるかを確認してみます。
そして、いよいよ集計する数式をG4に作りますので、SUMIF関数ダイアログボックスを表示していきます。

範囲には、$C$4:$C$18。絶対参照を忘れないように設定しましょう。

検索条件は、F4
合計範囲は、$D$4:$D$18

そして、OKボタンをクリックしましょう。

あれれ？数値が算出されないというかゼロが表示されてしまいましたね。

これは、F列に原因があります。
それは、”日”と直接文字を入力してしまったからです。

では、どうしたらいいのかというと、F4に1と数値を入力しましょう。
そして、1～7まで数値を入力します。

数値に変えた時点で、G4に集計結果が表示されてきましたね。

要するに、C列の曜日は表示形式を曜日にしているだけで、元は数値なわけです。
ですから、F列を文字にしては、ダメで、数値に変える必要があるのです。

だた、数値では、何曜日なのかわかりにくいので、
これを、先程と同じように表示形式を曜日に変えてみましょう。

そして、G4の数式もG10までコピーします。

これで、完成しましたね。

ところで、1が日曜日と表示されている訳です。
実は、1は、表示形式を日付に変えると、1990/1/1になり、曜日が日曜日となっている訳です。

ですので、日曜日を1として算出させるために、WEEKDAY関数の種類を1にした訳です。

ちょっと、長くなりましたが、難易度はそれほど高くありませんので、
機会がありましたら作ってみると面白いですよ。

そうそう、実は、WEEKDAY関数を使わなくても算出することが出来ますので、
次回はその方法をご紹介しましょう。

Excel。NETWORKDAYS。期間中の土日と祝日・祭日の日数を数える方法はありませんか？

Excel。期間中の土日と祝日・祭日の日数を数える方法はありませんか？

＜NETWORKDAYS関数＞

先日ご紹介した、期間内の祝日・祭日の日数を数える方法をご紹介したところ、
期間内の土日・祝祭日の数を数える方法を教えてほしいとのリクエストがありましたので、
今回はそれをご紹介します。

まず、今回も前回の時と同様に、
下記のような祝日・祭日の一覧を作っておかないといけません。

このような一覧が無いと、求めることが大変難しくなります。

さて、今回求めたいものの逆。
つまり、平日を算出するNETWORKDAYS関数というのはあるのですが、
今回求めたい、土日祝日・祭日の日数を算出する関数は残念ながら無いようなので、
アイディアが必要になりそうですね。

考え方としてですが、平日の日数を求めることは出来るわけですから、
その期間の日数から平日の日数を引けば、
土日祝日・祭日の日数を求めることが出来ることに気づきます。

ところが、Excelで日にちの減算には癖があります。

開始日と終了日を同日、今回は2016/1/1で入力してみます。

そして、G4は、=G3-G2　と数式を作成してみると、ゼロと算出されてしまいましたね。
けど、1日は存在している訳ですから、＋1をする必要があります。

また、NETWORKDAYS関数も確認しておきましょう。

開始日を2016/1/1。終了日を2016/1/5。として、
今後はG4にNETWORKDAYS関数で算出してみましょう。

G4をクリックして、NETWORKDAYS関数ダイアログボックスを表示しましょう。

開始日は、G2。
終了日は、G3。
祭日は、B2:B17。
と入力してOKボタンをクリックしましょう。

4日と5日が平日ですよね。なので、2と算出されましたね。

ではいよいよ本題ですね。

G5にまずは、NETWORKDAYS関数ダイアログボックスを表示しましょう。

開始日は、G2。
終了日は、G3。
祭日は、B2:B17。
と入力してOKボタンをクリックしましょう。

まずは、平日の日数が算出できましたので、次にこの数式を修正していきます。
数式バーの=のあとをクリックして、カーソルを表示します。

そして、次のように数式を修正していきます。

=G3-G2+1-NETWORKDAYS(G2,G3,B2:B17)

すると、G5はどうなったかというと。

表示形式が、日付になってしまっていますので、
これでは何が何だかさっぱりわかりませんので、表示形式を変更していきます。

G5をクリックして、

表示形式を標準にしましょう。

3と算出されましたね1月1日が元日で祝日・1月2日は土曜日で、
1月3日が日曜日ということで、3日と算出されて、間違いありませんよね。

このようにちょっとした、アイディアで、数式を修正すると、
より一層、Excelのスキルがアップできますので、アレコレ考えてみるのも、
スキルアップには、最適かもしれませんね。

Excel。fill。塗りつぶしを1行目・2行目・3行目と異なり色で設定して繰り返したい

Excel。塗りつぶしを1行目・2行目・3行目と異なり色で設定して繰り返したい

＜条件付き書式＋ROW&MOD関数＞

今回ご紹介するのは、以前ご紹介したことがある、
1行おきに行を塗りつぶすテクニックの応用編という感じですが、
1・2・3行目のそれぞれが異なる色で塗りつぶすにはどうしたらいいのという、
ご質問から紹介することになったテクニックです。

やり方・作り方は、1行おきと変わらないのですが、
アレンジをするとイマイチわかりにくいということでしたので、
今回は、1・2・3行目のそれぞれが異なる色で塗りつぶす設定をご紹介していきます。

下記の表があります。

これを、

このように、
1・2・3行目のそれぞれで色分けされるように設定するにはどうしたらいいのでしょうか？
というのが、今回ご紹介するテクニックです。

まず、考え方ですが、
行番号を算出するROW関数と除算した余りを算出するMOD関数の組み合わせと、
さらに、条件付き書式を使っていくことになりますが、
どのようなパターンになっているのか？と考えてみると、

行数を3で割って、その余りが0なのか？1なのか？2なのか？を導き出して、
その算出された余りの数値を使って、判断させるようにすればいいように思えます。

さて、そのまま、条件付き書式を設定するとしても、
まずはどのような数式を設定したらいいのか？を考えてから、
条件付き書式を設定していく方がよいと思われます。

Excelの経験値が高い方は、ストレートに設定が出来ますが、
Excelの経験値が少ない方は、
なかなか条件付き書式の数式は難易度が多少高いように思われますので、
一度、数式を作ってみることにしましょう。

G4をクリックして、MOD関数ダイアログボックスを表示しましょう。

数値には、ROW()
除数には、3
と入力してOKボタンをクリックしましょう。

答えは1ですよね。行数は4を3で割ったら、余りは1ですよね。

そして、G4の数式は、

=MOD(ROW(),3)

この数式のあとに、=1　とつけて、条件付き書式の数式を作っていくわけです。

それでは、B4:F16を範囲選択して、条件付き書式から新しいルールをクリックしましょう。

次に、新しい書式ルールダイアログボックスが表示されますので、
「ルールの種類を選択してください」の中から、
「数式を使用して、書式設定するセルを決定」をクリックします。

「ルールの内容を編集してください」のボックスをクリックして、

=mod(row(),3)=1

と入力して、書式ボタンをクリックしましょう。

セルの書式設定ダイアログボックスが表示されますので、
塗りつぶしの中から、好きな色を選択してOKボタンをクリックしましょう。

新しい書式ルールダイアログボックスに戻りますので、OKボタンをクリックします。

これで、1行目の塗りつぶしが出来ましたね。

これと同じように2行目、3行目も設定していきます。
2行目は、

数式は、

=MOD(ROW(),3)=2

これで、2行目が塗りつぶせましたので、最後ですね。

数式は、

=MOD(ROW(),3)=0

これで、完成しましたね。

このテクニックを知っていると、
このように用途に合わせて塗りつぶしを設定することが出来ますよ。

Excel。SORT。並び替えをしても、常に1～の通し番号順にしたいけど、どうしたらいいの？

Excel。並び替えをしても、常に1～の通し番号順にしたいけど、どうしたらいいの？

＜ROW関数＞

今回ご紹介するのは、並び替えをしても、
常に、1～の通し番号順にしたいというものなのですが、意外かもしれませんが、
よくご質問があるものの一つなんですよ。

どのようなことなのかというと、下記の表があります。

B列には、1～の通し番号が振られています。

この表をG列の合計を降順で並び替えを行うと、

当然のことながら、レコード（行）ごとに並び替えされますから、
Noも連動して、1～ではなく、並び替わるわけですよね。

そこで、今回のご質問。この通し番号をこのような並び替えをしても、
常に1～の通し番号にしたいというわけです。

たしかに、自分で再度振りなおせばいい。
わけですが、それを毎度毎度やるのは、ナンセンスですし、面倒くさいですよね。

そこで、今回は、関数。ROW関数を使って常に、
1～の通し番号にする方法をご紹介していきます。

さて、どうやって、1という数値を持ってくることができるのか？を考えてみましょう。

並び替えをしても、
その表やテーブルとは無縁のところのデータを使わないといけないわけですよね。

そこで、行番号に着目してみましょう。

行番号は、表だろうがテーブルだろうが、行番号は行番号です。
その行番号を算出する関数はROW関数ですね。

では、NoのB列のデータをいったんすべて削除して、B4に

=ROW()

という数式を作ってみましょう。

行番号の4が算出されましたね。

ただ、この数式をオートフィルを使ってコピーしても、単に行番号が表示されるだけですよね。

そこで、もう一つアイディアを加える必要があります。

要するに、4を1にしたいわけですよね。
1行目からデータまでの行数。すなわち、3を減算してあげれば、1になりますよね。
すなわち、B4の数式を、

=ROW()-3

と修正すればいいわけですね。

これで、B4の数値は、1になりましたね。

それでは、オートフィルハンドルをつかって、数式をコピーしてみましょう。

さて、問題なのは、ココからですね。並び替えをしても、
1～の通し番号にしたいというのが目的でしたので、合計を降順で並び替えをしてみましょう。

G3をクリックして、データタブの並び替えとフィルターにある、降順ボタンをクリックしましょう。

それでは、どうなったのでしょうか？確認してみましょう。

希望通りに、1～の通し番号順になっていますよね。

これは、今回のような表でも出来ますし、テーブルにしても使えますので、
ちょっと、覚えておくといい、テクニックのひとつかもしれませんね。

Excel。holiday。期間内の祝日・祭日の日数を数えたいけど、どうしたらいい？

Excel。期間内の祝日・祭日の日数を数えたいけど、どうしたらいい？

＜COUNTIF関数＞

とあるご質問をいただきまして、指定の期間の中で、

祝日・祭日が何日あったのかを数える方法はありませんかね？

というもの。

なるほど～。祝日・祭日を数える方法は簡単そうですが、ちょっとアイディアが必要ですよね。

まずは、祝日・祭日がExcelに登録されている訳ではありませんので、
いの一番は、下記のような祝日・祭日の一覧表を作っておく必要があります。

このような一覧表がないと、判断させる方法がありません。
ですので、複数年にわたる場合は、その期間の祝日・祭日の一覧が必要になります。

そして、いつからいつまでと入力できるところも合わせて作っておきましょう。

さて、ここからが本題ですが、開始日以上の祝日・祭日の数を数えて、
終了日より大きい祝日・祭日の数を数えて、
引いてあげればその間の祝日・祭日の数を求めることができますよね。

いっぺんに、一つの数式で算出するのもいいのですが、
まずは、開始日と終了日の数式を作って確認してみることにしましょう。

開始日に、2016/3/1と、終了日に、2016/4/30と入力しておきます。

G2をクリックして、COUNTIF関数ダイアログボックスを表示しましょう。

範囲は、祝日・祭日の一覧のB2:B17を選択します。

検索条件には、G2以上という条件ですので、以上なので、”>=”　と入力して、&G2　と入力します。

">="&G2

比較演算子を入力する場合には、”(ダブルコーテーション)を忘れずに。

では、OKボタンをクリックしましょう。

続いて、終了日も作っていきましょう。COUNTIF関数ダイアログボックスを表示しましょう。

範囲は、祝日・祭日の一覧のB2:B17を選択します。

検索条件には、G3より大きいという条件ですので、以上なので、”>”　と入力して、
&G3　と入力します。">"&G3

では、OKボタンをクリックしましょう。

両方の数値が算出できましたね。

開始日に13。終了日に10。と算出されましたので、13-10で3。
つまり、祝日・祭日の数は、3日ということが求められました。

実際確認してみると、3/20・21と4/29なので、確かに3日間ですね。

では、なぜ終了日がより大きいという比較演算子なのかというと、
その日を含めてみるとわかりますので、終了日の数式を

=COUNTIF(B2:B17,">="&G3)

としてみて、開始日と終了日をそれぞれ、2016/1/1としてみます。

以上と含めてしまうと、同数になってしまい、減算すると、ゼロになってしまいます。

しかし実際は、該当日は1日ありますので、1を算出する為に、
より大きいという条件にしている訳ですね。

さてそれでは、G5に祝日・祭日の数を求める数式を作っていきましょう。

両方の数式を合体させるだけですので、

=COUNTIF(B2:B17,">="&G2)-COUNTIF(B2:B17,">"&G3)

これで、完成ですね。

Excel。Hide。データの行を非表示にしても合計値が変わらないので、合致するようにしたい。

Excel。データの行を非表示にしても合計値が変わらないので、合致するようにしたい。

＜SUBTOTAL関数＞

Excelの経験値が少ない方向けの講座というものありまして、
そこで次のようなご質問がありまして、なるほど、それは確かに困るよね。
という実務ならではのご質問がありましたので、今回はそれをご紹介します。

データ量は違うのですが、下記のような表があります。

10行目に合計行がありまして、SUM関数の数式が設定されています。
C10には、=SUM(C4:C9)　というようにです。

それで、上野店と品川店を非表示にしてみます。

非表示をしてみると、合計値は変わっていませんよね。

Excelの経験値が多い方なら当たり前だろうってことになりますが、
Excelの経験値の少ない方ですと、
この合計値も連動して変わると思っている方も多いようでして、
今回のご質問は、今回のように行を非表示にしても、
合計値が連動して変わってほしいというものなのです。

確かに、テーブルとかを使えばいいのですが、
今回は、テーブルにしないで対応する方法をご紹介したいので、登場するテクニック。

SUBTOTAL関数

を使うと希望通りに対応できるようになります。


ではC10をクリックして、SUBTOTAL関数を作っていきますので、
SUBTOTAL関数ダイアログボックスを表示しましょう。

集計方法には、9
参照1には、C4:C9

と入力して、OKボタンをクリックしましょう。

SUM関数と同じように合計値が算出されましたね。

ところで、SUBTOTAL関数の集計方法が9とはいったいどういう事なのか？説明しましょう。

集計方法101にはAVERAGE関数が対応されていて、平均を算出します。
集計方法102にはCOUNT関数が対応されていて、数値の件数を算出します
集計方法103にはCOUNTA関数が対応されていて、データの件数を算出します。
集計方法104にはMAX関数が対応されていて、最大値を算出します
集計方法105にはMIN関数が対応されていて、最小値を算出します
集計方法106にはPRODUCT関数が対応されていて、積を算出します。
集計方法107にはSTDEV関数が対応されていて、標本標準偏差を算出します
集計方法108にはSTDEVP関数が対応されていて、標準偏差を算出します。
そして
集計方法109にはSUM関数が対応されていて、合計値を算出します
集計方法110にはVAR関数が対応されていて、不偏分散を算出します
集計方法111にはVARP関数が対応されていて、分散を算出します

まぁ、関数がアルファベット順に割り振られているのです、
今回は合計値を算出するので、109を入力したわけです。

では、本題に戻りまして、上野店と品川店を非表示にしてみましょう。

合計値が連動して変わりましたね。そして当然ですが、合計値。合っていますよね。

今回のように行を非表示にするような場合は、
SUM関数ではなくて、SUBTOTAL関数を使うといいですよ。

ところで、このSUBTOTAL関数の集計方法。

109ではなくて9ではないの？と思う方もいらっしゃるかもしれませんが、
100番台でないと、非表示では対応してくれません。

この一桁の場合は、オートフィルターを使う時には、ちゃんとリアクションしてくれるのですが、
単なる非表示ではリアクションしてくれません。

そこで、100番台を使うと、オートフィルターでも、非表示でもリアクションしますので、
100番台を使うほうがよろしいかと思います。