主な項目
1.[MOD]関数
2.[MOD]関数を[IF]関数にネストする
3.年月日から曜日を取得し[TEXT]関数で文字列に変換する
4.[条件付書式の設定]に[MOD]関数を使い奇数行、偶数行を塗りつぶす
5.[MOD]関数を使い偶数行(or奇数行)の合計計算
数値を除数で割ったときの剰余を返す関数です。戻り値は除数と同じ符号になります。
符号の現れ方
=MOD(3,2) では「1」が返されます。
=MOD(-3,2) でも「1」が返されます。
=MOD(3,-2) では「-1」が返されます。
=MOD(-3,-2) でも「-1」が返されます。
引数1.の注釈:数値には除算の分子となる数値を指定します。
引数2.の注釈:除数には除算の分母となる数値を指定します。(当然のことながら、除数 に 0 を指定すると、エラー値 #DIV/0! が返されます。)
使用例
MOD(5,2) の戻り値「1」
MOD(8,3) の戻り値「2」と言う具合です。
これを判定の道具に使い100年カレンダー(1000年も多分)が作れるのです。以下その説明です。
ご承知のように閏年は「西暦年数/4」で割り切れた年、即ち「剰余が0の年」に設けられています。
解説:IF関数は [条件式] , [真の場合の値] , [偽の場合の値]で返されます。
[条件式]の中に[MOD]関数をネストし、(A1,4)のA1はセル[A1]に書かれた「西暦年」です。それを4で割って、剰余が[=0]までが、IF関数の
条件式です。第1[,]の次がIF関数の[真の場合の値]で[29]と表示しなさい。第2[,]の後の[""]は偽の場合の表示指定が無いということです。
そこで、この数式を、暦の2月29日に埋め込みます。
すると、例えば2004年、2008年は4で割り切れ剰余が[=0]のため「29日」と表示されることになります。
(ちなみに、「"aaa"」は例えば「土」、「"aaaa"」は例えば「土曜日」と表示されます。)
TEXT関数の数式パレットの引数1に「値」引数2が「表示形式」です。
注釈では:「数値に指定した書式を設定し、文字列に変換した結果を返します。」とあり、
「値には数値、結果が数値となる数式、または数値が入力されているセルへの参照を指定します。」となっています。
表示形式の注釈では:「表示形式には[表示形式]ダイアログボックスに表示されている数値形式を、文字列として指定します」となっています。
今年2004年の2月がそれに当たり2月は29日までありました。
例題として「2004年2月から2036年2月(2月限定)の日程表(暦)を作って見ました。
下表上段をコピーして、Excel[A1]セルに貼り付けてください。シート名を「月別」にしてください。
下段をコピーしてSheet2[A1]セルに貼り付け、シート名を2月にしてください。
その上で、設定することがあリます。
年リストのデータを範囲を決めます。
2008 | 95年表 | 2008 | ||||
スケジュール | 平成20年 | 2004 | ||||
日 | 曜日 | 出来事 | 適用 | 2005 | ||
1 | 2006 | |||||
2 | ■[A1]の2004は[F1]のオートフィルタと | 2007 | ||||
3 | 連動しています。{F1}をクリック▼をクリック | 2008 | ||||
4 | して年次を選択します。 | 2009 | ||||
5 | ■平成16年は[A1]参照で下式を埋め込んで | 2010 | ||||
6 | あります。=TEXT(DATE(A1,1,1),”ggge年”) | 2011 | ||||
7 | ■[DATE関数]で年を[A1]セル,月を[A2]セル | 2012 | ||||
8 | 日を[A4]セルに指定した為日付の数値は | 2013 | ||||
9 | ただの、算用数字です。 | 2014 | ||||
10 | 2015 | |||||
11 | ■問題の[MOD関数]の使用場所は2月末日 | 2016 | ||||
12 | にあります。2004年選択状態で、2月の | 2017 | ||||
13 | シートをクリックして29日をクリックすると、 | 2018 | ||||
14 | =IF(MOD(A1,4)=0,29,"") | 2019 | ||||
15 | が埋め込まれているので「閏年」だけ29日 | 2020 | ||||
16 | が付け加えられるのです。 | 2021 | ||||
17 | 2022 | |||||
18 | ■数式解説 | 2023 | ||||
19 | IF関数(引数2に | 2024 | ||||
20 | MOD関数の剰余0(MOD(A1=0で)の場合 | 2025 | ||||
21 | IF関数(引数2で)29と返し(入力し) | 2026 | ||||
22 | IR関数(引数3は)空欄(条件なし) | 2027 | ||||
23 | 補記:A1=0 は2004の剰余は0ということです。 | 2028 | ||||
24 | 2029 | |||||
25 | ■2008年の2月29日更に4年後の同日も | 2030 | ||||
26 | 見てください。 | 2031 | ||||
27 | ■B列曜日には下記が埋め込まれています。 | 2032 | ||||
28 | =TEXT(DATE($A$1,$A$2,A4),"aaa") | 2033 | ||||
29 | 2034 | |||||
30 | 注)上記数式には、トップにアポストロフィイ | 2035 | ||||
31 | が含まれ、ダブルクォーティションは全角使用 | 2036 |
2008 | 95年表 | ||
2 | スケジュール | 平成20年 | |
日 | 曜日 | 出来事 | 適用 |
1 | 金 | ||
2 | 土 | ||
3 | 日 | ||
4 | 月 | ||
5 | 火 | ||
6 | 水 | ||
7 | 木 | ||
8 | 金 | ||
9 | 土 | ||
10 | 日 | ||
11 | 月 | ||
12 | 火 | ||
13 | 水 | ||
14 | 木 | ||
15 | 金 | ||
16 | 土 | ||
17 | 日 | ||
18 | 月 | ||
19 | 火 | ||
20 | 水 | ||
21 | 木 | ||
22 | 金 | ||
23 | 土 | ||
24 | 日 | ||
25 | 月 | ||
26 | 火 | ||
27 | 水 | ||
28 | 木 | ||
29 | 金 | ||
Sheet1〜13まで使用すると、1〜12月までの月日表示の日程表など作成できます。2月が一番面倒なので ピックアップしてみました。
土、日の色付けが狂っているようです。曜日を全て選択したうえ、[書式]⇒[条件付き書式設定]を選択し下図のように設定し直してください。
土の書式設定がおわって、次へ進む場合には、下側の[追加(A)]ボタンを押します。
色付けは、関数直接ではできません。先ず範囲を選択して、[書式]→[条件付書式の設定]で行います。
条件1で「数式が」を選択し、右のテキストボックスに、下記数式を入力します。
1.表に行番号がある場合(下図Aに番号を入れた場合) 「 =MOD(($A18:$G22),2)=0 」
2.表に行番号がない場合 「 =MOD(ROW(),2)=0 」
3.奇数行にも異なった色を付けたいときは、
条件2で「数式が」を選択し、右のテキストボックスに、「 =MOD(ROW(),2)=1 」と入力します。
その上で、[書式]ボタンをクリックし、「塗りつぶし」なら、[パターン]タブから。文字色なら、[フォント]タブから設定します。
[A18]に18以下19,20,21,22と番号のある場合
[A18]〜[G22]の範囲を選択
[ROW]関数をネストして、シートの行番号を指定します。
「 (ROW() 」
[B18]〜[G22]の範囲を選択
割った余り 行番号を 2で 0ならば
=(MOD (ROW(), 2) = 0
何らかの必要があってか、または、間違ってか下図のような表を作ってしまったとします。また、奇数行にも「数値」が有った場合は、
表全体を選択して、「Σ」も使えません。たまたま、求める「合計」が偶数欄に集中していますので、[MOD]関数で処理します。
このような小さな表なら、[Ctrl]キーで拾い出してもわけなくできますが、
これが県全体の児童数となると大変な作業になってしまいます。そんなとき、[MOD]関数の出番です。
[IF]関数の条件式に[MOD]と[ROW]関数を使い、条件に合った場合[SUM]関数で合計を求め、条件外のときは、空欄にしなさい。
若し〜ならば 割った余り 行番号を 2で 0ならば B4:E4の合計 で無いときは空欄
構文:=IF (MOD (ROW(), 2) = 0, SUM(B4:E4), "" )
フィルハンドルを行った場合で、対象セルに[値]が無い場合、[0]が表示されます。[ツール]メニュー →[オプション]→[表示]タブで、[ゼロ値]のチエックを外します。