KAWANO's PukiWiki Plus! - Lecture/InfoPrac2003/9th の変更点
RIGHT:[[授業のページへ戻る>Lecture/InfoPrac2003]] *情報処理演習 第9回 -今回のテーマ「日付／時刻関数」「条件付き書式」 --関数を探して、計算式をつくれるようになる --条件付き書式設定をマスターする **前回までの課題の提出（準備中） **前回までの課題の提出 [[こちらの連絡事項>../Info]]を見て、前回課題の提出状況を確認してください。 -前回（第８回）の課題は、今回の課題と一緒にチェックします。 -まだ提出していない人は、課題を作成して提出してください。 -再提出するように指示があった人は、［マイドキュメント］にあるファイルの修正をして、提出用フォルダに再提出してください。 **前回の課題 前回の課題は、説明に訂正箇所が数ヶ所ありました。すいませんでした。 あと１つ訂正がありましたので、連絡します。 ***前回の訂正 「条件判定」のところで、成績をつけるときの条件を計算式で入力しますが、 次の説明が間違っていました。 -条件：''「出席回数が6回以上かどうか」'' --6回以上（真）：''「成績を課題、テスト、出席回数から計算する」'' --6回未満（偽）：''「0とする」'' この条件で、IF関数を使った計算式が、次のようになると説明しましたが、''これは誤りです''。 IF(O2>6, ROUND(M2*0.4+N2*0.4+O2*10*0.2,0), 0) 条件に合う、正しい計算式は次のとおりです。 IF(O2>=6, ROUND(M2*0.4+N2*0.4+O2*10*0.2,0), 0) ^^^^^ 前回の説明の内容は、すでに修正しました。 こちらの説明が間違えていましたので、 課題を修正しなくてもかまいませんが、余裕があるようであれば、正しい計算式を設定してください。 ***度数分布 この計算式は難しかったようですので、解説をします。 度数分布とは、「0点以上、10点未満の人の数」「10点以上、20点未満の人の数」のように、度数（値の一定の範囲）にどのくらいデータが分布しているかを見るものです。~ 前回の課題では、''「0〜100点までの10点ごとの分布の状況を調べる」''というものでした。 次のように、点数が分布している表を考えてみましょう（人数は10人です）。 これを''COUNTIF''関数を使って計算します（範囲は「点数」としておきます）。 |名前|Aさん|Bさん|Cさん|Dさん|Eさん|Fさん|Gさん|Hさん|Iさん|Jさん| |点数| 9 |15 |20 |22 |34 |38 |39 |41 |50 |57 | : 0点以上、10点未満の人の数| COUNTIF関数で使う検索条件は「10点未満」になりますから、 計算式は次のようになり、Aさん一人であるのがわかります。 = COUNTIF(点数の範囲, "<10") :10点以上、20点未満の人の数| 表をみれば、Bさん一人なのはすぐにわかりますが、計算式を次のように使えば、計算できます。~ まず、10点以上の人の数を考えましょう。BさんからJさんまでの「9人」であることがわかります。20点以上の人の数を考えてみましょう。CさんからJさんまでの「8人」のはずです。~ 10点以上の人の中には、20点以上の人も含まれています。したがって、''10点以上の人の数から、20点以上の人の数を引けば''、「10点以上、20点未満の人の数」が求められます。計算式は次のようになります。 = COUNTIF(点数の範囲, ">=10") - COUNTIF(点数の範囲, ">=20") :20点以上、30点未満の人の数| 上と同じように考えればよいだけです。~ まず20点以上の人はCさんからJさんまでの「8人」、 そして30点以上の人はEさんからJさんまでの「6人」です。 20点以上の人の中には、30点以上の人も含まれていますから、 20点以上の人の数から、30点以上の人の数を引けば、条件に合う人数（2人）を求められます。 = COUNTIF(点数の範囲, ">=20") - COUNTIF(点数の範囲, ">=30") あと、「80点以上90点未満」まで、同じように計算をしていけばいいだけです。 **今回の内容 今回は、日付／時刻関数を使った、万年カレンダーを作ります。 年月を指定すると、自動的にカレンダーを作成するというものです。 #ref(excel01.png,80%) &ref(1126.xls,こちら(1126.xls));を右クリックしてください。「リンクを名前を付けて保存」や「対象をファイルに保存」を選んで、「マイドキュメント」に保存してください。 保存できたら、ダウンロードしたファイルを開いてください。 // ここまでできたら、[[課題の説明>../8thExercise]]にすすんでください。 **日付のデータ ***日付のデータの表現 Excelでは、日付のデータ（2003年11月26日など）を、''「1900年1月1日からの日数」''で管理しています。たとえば、2003年11月26日は「1900年1月1日から37951日後」というようにです。「37951」のような日付のデータのことを「''日付のシリアル値''」といいます。ほかに、時刻のシリアル値もあります。 日付のデータを、西暦や和暦などの表示形式で表示することができます。 「日付のデータ」というシートを表示してください。 「37951」とシリアル値が並んでいますので、表示形式を設定して、さまざまな日付の表示形式を設定してください。表示形式の設定は、次のようにします。 +セルを右クリックして、「セルの書式設定」を選択します。 +「表示形式」タブをクリックし、分類から「日付」を選択します。 +種類から、表示形式を選択して、「OK」ボタンをクリックします。 次に、日付をシリアル値に変換してみましょう。 C12セルに、自分の誕生日を入力してください。誕生日が1984年7月2日であれば、「1984/7/2」のように入力してください。 そして、次のように表示形式を設定すると、誕生日のシリアル値がわかります。 +セルを右クリックして、「セルの書式設定」を選択します。 +「表示形式」タブをクリックし、分類から「標準」を選択します。 ***日付と日数の計算 日付をシリアル値として扱うおかげで、「○月○日の何日後」や「○月○日から○月○日までの日数」のような日付の計算をすることができます。 たとえば、「2003年11月26日の100日後」を調べるには、次のようにします。 +「2003/11/26」と「100」と入力したセルを用意しておきます。 今回はすでに、B16とC16セルに入力しておきました。 +D16セルに次の計算式を入力し、Enterキーを押します。 =B16+C16 また、「2003年11月26日から2004年3月31日までの日数」を求めるには、どうすればよいでしょうか。2004年3月31日のシリアル値から2003年11月26日のシリアル値をひけばよいだけです。D18セルに計算式を入力してください。そのままだと「1900/5/5」のように表示されるので、表示形式を「標準」にして、シリアル値（日数）で表示するようにしてください。 **カレンダーの作成 では、「カレンダー」のワークシートを表示してください。 カレンダーの作成と日付／時刻関数の説明をします。 年と月を、C2セルとE2セルにそれぞれ、「2003」「11」と入力してください。 ***曜日の入力（オートフィル） まずカレンダーの曜日を入力します。 Excelのオートフィル機能を使えば、連番の数字だけではなく、曜日もマウスで簡単に入力できます。 次のように操作してください。 +B4セルに「日」または「Sun」と入力します。 +B4セルをクリックして、右下の四角形（フィルハンドル）を、マウスでH4セルまでドラッグします。 次に、B5〜H5セルまでに、1〜7までの数字を入力してください。ここでもオートフィルを使うとよいでしょう。 #ref(excel02.png,nolink) ***第1週の計算 次に、第1週の日付の計算をします。 ここでは、「''その年月の1日が何曜日になるか''」がポイントになります。 ここで使う関数は、これまで登場したIF関数と、日付／時刻関数の''DATE''関数、''WEEKDAY''関数です。 -''DATE''（指定した年月日のシリアル値を返す） --書式 : DATE(年, 月, 日) --引数 : 年 : 1900〜9999までの数値 --引数 : 月 : 1〜12までの数値 --引数 : 日 : 1〜31までの数値 -''WEEKDAY''（シリアル値を曜日に変換した結果を返す） --書式 : WEEKDAY(シリアル値, 種類) --引数 : シリアル値 : 日付や時間のシリアル値 --引数 : 種類 : 戻り値の種類 ---1または省略 : 日曜=1〜土曜=7 ---2の場合 : 月曜=1〜 ---3の場合 : 月曜=0〜 -まず、その年月の1日は、DATE関数を使えば、シリアル値として求めることができます。次の計算式で表すことができます。 = DATE(C2, E2, 1) -次に、その年月の1日が何曜日かは、WEEKDAY関数を使えばわかります。上のDATE関数の結果を使えば、計算式は次のようになります（ここでは種類は1を選択します）。 = WEEKDAY(DATE(C2, E2, 1)) -「2003年11月1日」のシリアル値と曜日の値は、上の2つの計算式を使えば、それぞれ「37926」と「7」（土曜日）となるのがわかります。 これらの式を使って、第1週の計算をしてみましょう。 B5〜H5セルの値も利用します。 正解から紹介すると、B6セルの計算式は次のようになります。 =IF(B5>=WEEKDAY(DATE(C2,E2,1)), B5-WEEKDAY(DATE(C2,E2,1))+1, "") ^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^ 条件式(A) 真の場合(B) 偽の場合(C) つまり、次のような意味になります。 -その年月の1日の曜日がB5と同じかその後であれば...(A) --B5からその年月の1日の曜日を引き、1を足したものを返す --空白「""」を返す わかりにくいので、表にしてみましょう。 年月を少し変えて、「2003年10月」の場合を見てみます。 「2003年10月1日」は水曜日ですので、曜日の値は「4」となります。 |セル|5行目の値（曜日）|日付|条件式の結果|5行目の値-WEEKDAYの戻り値| |B6 |CENTER:1 | |CENTER:偽 |CENTER:-3 | |C6 |CENTER:2 | |CENTER:偽 |CENTER:-2 | |D6 |CENTER:3 | |CENTER:偽 |CENTER:-1 | |E6 |CENTER:4 |CENTER:1|CENTER:真 |CENTER:0 | |F6 |CENTER:5 |CENTER:2|CENTER:真 |CENTER:1 | |G6 |CENTER:6 |CENTER:3|CENTER:真 |CENTER:2 | |H6 |CENTER:7 |CENTER:4|CENTER:真 |CENTER:3 | つまり、まずセル（B6〜H6）の曜日の値と、その年月の1日の曜日の値を比較します。 もし、セルの曜日の値より1日の曜日の値が小さければ、そのセルには指定された年月の日付はあらわれないはずです（まだ前の月の日付）。 もし、セルの曜日の値より1日の曜日の値が同じか大きければ、そのセルには指定された年月の日付があわられます。セルの日付は、曜日の値を計算したものから求めることができるというわけです。 B6セルの計算式の「C2」と「E2」セルを指定している部分を、絶対参照の形式に変更してください。 変更できたら、B6セルの計算式をC6〜H6セルにコピーしましょう。 #ref(excel03.png,nolink) ***第2週〜第6週までの計算 第1週目は難しい数式を使いましたが、第2週から第6週は簡単な計算式で求めることができます。 第2週の日曜日の日付は、第1週の土曜日の日付に1を足したものですから、 B7セルの次のような計算式になります。 = H6 + 1 また、第2週の月曜日の日付は、日曜の日付に1を足したものですから、 C7セルの次のような計算式になります。 = B7 + 1 あとは、月曜日の計算式を土曜日のセル(H7）までコピーし、そのあと第2週の計算式全体を第6週までコピーすれば、すべての枠に計算式を入力することができます。 コピーには、マウスを使えば簡単にできます。 #ref(excel04.png,80%) ***月末の表示（条件付き書式） 今のままでは、月末に「32」や「33」などの余計な数字が表示されてしまっています。 そこで、「''条件付き書式''」を使って、数値の表示をコントロールします。 条件付き書式とは、普通の書式設定とは違い、セルや計算の値がある条件がときに設定せる書式のことです。 ここで使う関数は、さっき登場したDATE関数と、日付／時刻関数の''DAY''関数です。 -''DAY''（シリアル値に対応した日を1から31までの数字で返す） --書式 : DAY(シリアル値) --引数 : シリアル値 : 日付や時間のシリアル値 -指定された年月の1日は、DATE関数を使えば、シリアル値として求めることができます。次の計算式で表すことができます。 = DATE($C$2, $E$2, 1) -指定された年月の翌月の1日は、指定された月に1を足せばよいだけです。次の計算式で表すことができます。 = DATE($C$2, $E$2+1, 1) -指定された年月の翌月の1日の前日、つまり''指定された年月の最終日''は、''指定された月に1を足し、さらに日から１を引く、つまり0にすればよい''だけです。次の計算式で表すことができます。 = DATE($C$2, $E$2+1, 0) -したがって、指定された年月の最終日の日付は、次の計算式で求められます。 = DAY(DATE($C$2, $E$2+1, 0)) この式を使って、次のように条件付き書式を設定しましょう。 「その月の最終日より大きい数値は、背景と同じ文字色で表示する」という条件にします。 +B6〜H11までのセルを範囲指定します。 +メニューバーから「書式」→「条件付き書式」を選択します。 +条件を設定します。 ++中央の条件に「次の値より大きい」をマウスで選びます。 ++右側の入力欄に、次の計算式を入力します。 = DAY(DATE($C$2, $E$2+1, 0)) #ref(excel06.png,nolink) +「書式」ボタンをクリックします。 ++「フォント」タブをクリックします。 ++色を選びます。背景と同じ色にします。 ++「OK」ボタンを押します。 +「OK」ボタンを押します。 設定できると、月の最終日より大きい数字が見えなくなります。ただし、表示されていないわけではないので、セルの塗りつぶしを設定するときには気をつけてください。 **見映えの設定 カレンダーを見映えをよくするため、次のような設定をして下さい。 -文字色の設定（日曜日は赤、土曜日は青など） -文字のフォントの設定、大きさの変更 -罫線の太さ、パターン -セルの塗りつぶし 最後に、年月を自分の誕生日の年月にしてください。 ここまでできたら、[[課題の説明>../9thExercise]]にすすんでください。 |
|
|
Founded by Minoru Kawano.
Powered by PukiWiki Plus! 1.4.7plus-u2-i18n. HTML convert time to 1.022 sec. |
|