エクセル練習問題：日付関連の計算

スポンサードリンク

問題　　　topへ

問題1：B,C,D列に年月日が入力されています。F列に和暦(平成○年○月○日)で表示しなさい。
なお、F列は計算に使いたいのでシリアル値とし、表示形式で対処することとします。　　　問題1の解答

	B	C	D	E	F
2	年	月	日		和暦
3	2011	5	3
4	2011	7	13
5	2011	11	23

問題2：各日付の曜日をC列に「月曜日」のように表示しなさい。
なお、C列の値は「文字列」としなさい。　　問題2の解答

	B	C
2	日付	曜日
3	2011/5/3
4	2011/7/13
5	2011/11/23

問題3：各日付の次の月曜日が提出期限です。提出期限日を求めなさい。
表示は「年/月/日」の形式とします。　　　　問題3の解答

	B	C	D
2	日付	曜日	提出期限日
3	2011/6/10	金
4	2011/7/5	火
5	2011/8/8	月

問題4：商品の購入履歴がランダムに入力されているリストがあります。各商品の最終購入日とその日の購入額を求めなさい。　　　　問題4の解答

	B	C	D	E	F	G	H	I
2	日付	商品名	購入額				最終購入日	購入金額
3	2014/4/11	みかん	84,339			みかん
4	2014/4/14	りんご	76,786			りんご
5	2014/5/5	みかん	66,765			バナナ
6	2014/4/2	みかん	85,819
7	2014/5/6	バナナ	15,711
8	2014/5/2	りんご	3,870
9	2014/4/7	バナナ	92,183
10	2014/4/3	りんご	13,800
11	2014/5/1	みかん	21,099
12	2014/4/15	バナナ	53,556
13	2014/4/9	りんご	76,246

問題1の解答例　　　　 topへ

解答

	B	C	D	E	F
2	年	月	日		和暦
3	2011	5	3		平成23年5月3日
4	2011	7	13		平成23年7月13日
5	2011	11	23		平成23年11月23日

DATE関数を使う方法

Date関数を使って日付のシリアル値にし、表示形式で和暦を表示します。
構文：=DATE(年,月,日)
DATE関数に説明がありますので参照してください。
F2:F5に下表のように数式を入力します。

B C D E F

2 年月日和暦

3 2011 5 3 =DATE(B3,C3,D3)

4 2011 7 13 =DATE(B4,C4,D4)

5 2011 11 23 =DATE(B5,C5,D5)
数式を入力すると、西暦での表示形式で表示されました。
表示形式を変更します
1. F3:F5セルを選択し、[ホーム]タブの数値グループの[表示形式]から[その他の表示形式]を実行します。
  ショートカットキーは　[Ctrl]+[1] です。
2. [表示形式]タブで[日付]を選択します。
  ロケールが「日本語」になっているのを確認し、カレンダーの種類を「和暦」にします。
  種類で[平成13年3月14日]を選択して、[OK]ボタンをクリックします。
3. 和暦で表示されました。

Excelが日付と認識する形式に変換する

Excelは「日付」と認識できる形式であれば、自動でシリアル値に変換します。

F3セルに　=B3&"/"&C3&"/"&D3　と入力すると、セルに表示される値は日付のシリアル値に変換されます。
[ホーム]タブの数値グループの[表示形式]から[その他の表示形式]を実行します。
ショートカットキーは　[Ctrl]+[1] です。
[表示形式]タブで[日付]を選択します。
ロケールが「日本語」になっているのを確認し、カレンダーの種類を「和暦」にします。
種類で[平成13年3月14日]を選択して、[OK]ボタンをクリックします。
和暦で表示されました。

問題2の解答例　　　　 topへ

解答

	B	C
2	日付	曜日
3	2011/5/3	火曜日
4	2011/7/13	水曜日
5	2011/11/23	水曜日

TEXT関数で曜日を表示する

表示形式で曜日を表示することができますが、問題文に「C列の値は「文字列」としなさい。」とあるので、TEXT関数を利用して曜日を表示します。
構文：=TEXT(値, 表示形式)
TEXT関数に説明がありますので参照してください。
表示形式についてはエクセル2010基本講座：表示形式(ユーザー定義)の設定方法　をご覧ください。
関数の引数ダイアログボックスを使う場合は、値に B3 、表示形式に　"aaaa"　と入力します。
B C

2 日付曜日

3 2011/5/3 =TEXT(B3,"aaaa")

4 2011/7/13 =TEXT(B4,"aaaa")

5 2011/11/23 =TEXT(B5,"aaaa")

WEEKDAY関数を使って曜日を表示する

WEEKDAY関数を使うこともできますが、結構長い数式になります。
=CHOOSE(WEEKDAY(B3,1),"日曜日","月曜日","火曜日","水曜日","木曜日","金曜日","土曜日")
=CHOOSE(WEEKDAY(B3,1),"日","月","火","水","木","金","土")&"曜日"
MID関数を使う方法も考えられます。
=MID("日月火水木金土",WEEKDAY(B3,1),1)&"曜日"

問題3の解答例　　　　 topへ

解答例

	B	C	D
2	日付	曜日	提出期限日
3	2011/6/10	金	2011/6/13
4	2011/7/5	火	2011/7/11
5	2011/8/8	月	2011/8/15

日付と曜日の関係はWEEKDAY関数が利用できます。

構文：=WEEKDAY(シリアル値,[種類])
WEEKDAY関数　 WEEKDAY関数(Excel2010) に説明がありますので参照してください。

WEEKDAY関数で種類を1～3としたときに返される値は下表のようになります。

	日	月	火	水	木	金	土
WEEKDAY(シリアル値,1)で返される値	1	2	3	4	5	6	7
WEEKDAY(シリアル値,2)で返される値	7	1	2	3	4	5	6
WEEKDAY(シリアル値,3)で返される値	6	0	1	2	3	4	5
計算で必要な値(月曜日までの日数)	1	7	6	5	4	3	2

WEEKDAY関数の種類を2としたときに返される値と、月曜日までの日数とを足した値は8となり都合がよい。
よって、　8-WEEKDAY(B3,2)　として数式に組み込めばよいことがわかります。

	B	C	D
2	日付	曜日	提出期限日
3	2011/6/10	金	=B3+8-WEEKDAY(B3,2)
4	2011/7/5	火	=B4+8-WEEKDAY(B4,2)
5	2011/8/8	月	=B5+8-WEEKDAY(B5,2)

問題4の解答例　　　　 topへ

解答例

	B	C	D	E	F	G	H	I
2	日付	商品名	購入額				最終購入日	購入金額
3	2014/4/11	みかん	84,339			みかん	2014/5/5	66,765
4	2014/4/14	りんご	76,786			りんご	2014/5/2	3,870
5	2014/5/5	みかん	66,765			バナナ	2014/5/6	15,711
6	2014/4/2	みかん	85,819
7	2014/5/6	バナナ	15,711
8	2014/5/2	りんご	3,870
9	2014/4/7	バナナ	92,183
10	2014/4/3	りんご	13,800
11	2014/5/1	みかん	21,099
12	2014/4/15	バナナ	53,556
13	2014/4/9	りんご	76,246

MAXIFS,XLOOKUP関数で求める	MAX,INDEX,SUMIFS関数で求める
配列数式とSUMIFS関数で求める

MAXIFS,XLOOKUP関数で求める

Microsoft365ではMAXIFS関数、XLOOKUP関数、スピルの機能を使うことができます。
詳細は　MAXIFS関数　 XLOOKUP関数　スピルを利用した計算の例　をご覧ください。

H3セルに　=MAXIFS(B3:B13,C3:C13,G3:G5)　と入力します。
I3セルに　=XLOOKUP(H3#&G3:G5,B3:B13&C3:C13,D3:D13)　と入力します。
H3# の　#はスピル範囲演算子と呼ばれるものです。

MAX,INDEX,SUMIFS関数で求める

日付はシリアル値なので一番値が大きいものが最終購入日となります。
よって、商品名が「みかん」のデータの中で最も大きな日付を求めればよいことになります。
H3セルの数式は　=MAX(INDEX(($C$3:$C$13=G3)*($B$3:$B$13),0))　とすることができます。
- INDEX(($C$3:$C$13=G3)*($B$3:$B$13),0)　の部分は　INDEX({41740;0;41764;41731;0;0;0;0;41760;0;0},0)　と計算されます。
購入金額は商品名と日付が一致するデータの購入額を求めればよいので、SUMIFS関数を使うことができます。
ただし、データに商品名と日付が一致するものは1つであることが条件になります。複数ある時はその合計になります。
I3セルの数式は　=SUMIFS($D$3:$D$13,$B$3:$B$13,H3,$C$3:$C$13,G3)　とすることができます。

SUMIFS関数を関数の引数ダイアログボックスを使って入力すると、合計対象範囲に $D$3:$D$13　、条件範囲1は　$B$3:$B$13　、条件1は　H3　、条件範囲2は　$C$3:$C$13　、条件2は　G3　と入力します。

配列数式とSUMIFS関数で求める

考え方は同じですが、配列数式で求めることもできます。
H3セルに　=MAX(IF($C$3:$C$13=G3,$B$3:$B$13))　と入力して、[Ctrl]+[Shift]+[Enter]で数式を確定して配列数式にします。
購入金額は解答例A　と同じくSUMIFS関数で求めます。

そのほかの求め方もあります。
Excel(エクセル)関数の技：条件付きで最大値、最小値を求める　複数条件に合うセルを合計する　をご覧ください。

スポンサードリンク

Home｜エクセル練習問題：目次｜日付関連の計算

PageViewCounter
Counter
Since2006/2/27