1. Home »
2. エクセル練習問題:目次 »
3. 日付関連の計算

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

問題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関数で求める

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

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

MAX,INDEX,SUMIFS関数で求める

• 日付はシリアル値なので一番値が大きいものが最終購入日となります。
  よって、商品名が「みかん」のデータの中で最も大きな日付を求めればよいことになります。
  H3セルの数式は =MAX(INDEX(($C3ドル:$C13ドル=G3)*($B3ドル:$B13ドル),0)) とすることができます。
  
  • INDEX(($C3ドル:$C13ドル=G3)*($B3ドル:$B13ドル),0) の部分は INDEX({41740;0;41764;41731;0;0;0;0;41760;0;0},0) と計算されます。
    
• 購入金額は商品名と日付が一致するデータの購入額を求めればよいので、SUMIFS関数を使うことができます。
  ただし、データに商品名と日付が一致するものは1つであることが条件になります。複数ある時はその合計になります。
  I3セルの数式は =SUMIFS($D3ドル:$D13,ドル$B3ドル:$B13,ドルH3,$C3ドル:$C13,ドルG3) とすることができます。
  
  SUMIFS関数を関数の引数ダイアログボックスを使って入力すると、合計対象範囲に $D3ドル:$D13ドル 、条件範囲1は $B3ドル:$B13ドル 、条件1は H3 、条件範囲2は $C3ドル:$C13ドル 、条件2は G3 と入力します。
  

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

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

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

スポンサードリンク
(追記) (追記ここまで)

PageViewCounter
Counter
Since2006/2/27