安曇野ライク・ブログ

今回はExcelでカレンダーを作る解説です。具体例として、昨年末に作成した素材を選びました。作業の順を追って、簡単に説明します。実際のExcelファイルはここをクリックしてダウンロードし、解凍閲覧できます。

１．レイアウト
添付のカレンダーは２Lサイズなので、まずレイアウトをそのサイズに合わせます。上部の写真はデジカメの標準の縦横サイズ（３X４）を縮小して貼り付けるとして、残ったエリアを中段に年月表示（写真例では、前後の月のミニカレンダーを配置）し、下段に日付エリアにすると、日付の１セルあたりのサイズは縦0.85cm、横1.69cmとなり、余りを外枠としました。日付エリアの最上部には７行の曜日欄を作ります。大外の青の部分を印刷範囲とし、これが２Lサイズの 178X127 mmとなります。この青枠のエリアを縦方向に１２ヶ月分、コピーしてレイアウトの出来上がりです。

２．計算式による日付の作成
カレンダーの日付を計算式でレイアウト表のセルに自動配置できるのはExcelの醍醐味です。詳細はダウンロードしたファイルの各セルの計算式を見ていただくこととし、以下の表から概略説明をします。
このカレンダーはK15のセルに年号を入力すると、その年のカレンダーを自動作成するものです。そのロジックは、
①年号を入力すると、その下のセル（K16）にその年の第１日曜日が何日であるか（第１日曜日が１月１日の時のみ第２日曜日の日付）を表示させます。この表示は年月日なので、年月を省いた日付を取得して、これをK18のセルに表示させます。このK18の日付がその月の全日付を配置する大事なデータです。
②第１日曜日の日付が決まり、これがC19のセルに配置されます。後はこの日の前後を遡れば、１日から３１日まで自動配置されます。コツは、日付が０以下になれば非表示とし、また１月は３１日までなので、K17のセルに入力した３１を超える場合も非表示にすることで、１月の日付が自動配置されます。
③さて、難問は翌月の初日の曜日をどう計算式で表すかの問題です。さんざ悩んだ末に以下を思いつきました。
その月の基準となる第１日曜日の日付を前の月からどう割り出せば良いか、つまり作表した７つの曜日の中で最後の週（行）では空欄がいくつあったか数えれば、明確となることに気づきました。上の表で基準日のK42のセルの計算式は、
=IF(I22=””,COUNTIF(C22:I22,””)+1,COUNTIF(C23:I23,””)+1)
空欄の数に１を加えた数値が正解で、基準の日曜日から前にさかのぼること、この空欄数が前月の最終日に合致して、問題解決です。なお、IF文にして、カウントする対象を２行にしたのは、前月が第何週で終わるか年によって変化するので、これに対応した計算式です。
④各月の日数は固定ですが、うるう年の２月だけ異なります。自動計算に対応するため、上表のK41は計算式は、
=IF($K$15/4=INT($K$15/4), 29, 28)
とし、最初に入力した年号を４で割って数値が整数ならば、その月は２９日、そうでなければ、２８日にするものです。今年は２９日ですね。

以上で、完成しました。さて、添付のExcelファイルは祝日などの文字編集や追加文字の対応をしていません。例えば、２月１１日の建国記念日を対象セルに入力すると、それ以降の日付がバグってしまいます。さあ、どうしましょう！本日はちょっと時間がオーバーしましたので、続きは後日にて．．．

Excelシリーズも終わりに近づきました。今回は表中のデータをExcelの関数を使って計算する例として、以下の課題を選びました。
①ある期間の数値データを集計する
②ある期間で、ある条件に一致しているものをカウントする
具体例として、車の給油記録の事例を使って上記の説明をします。

①の課題として、給油記録でタイヤの装着期間中の走行距離を求める方法です。上記の表ではタイヤ種「S1」が2014/1/1〜2014/3/31の期間に走行した距離を左の数値データから抽出して、その合計を求めるものです。ここでは、複数の条件を指定して配列の合計を求めることができる関数SUMPRODUCT (サムプロダクト)の数式を使っています。右の表の青で囲ったセルO8がその計算式で、
=SUMPRODUCT((D$8:D$199>=M8)*(D$8:D$199<=N8),F$8:F$199)
計算式では、ピンクの式で日付の等号、不等号の２つの条件から期間を指定し、茶色の式でその期間のF列の合計を求めることができます。

②の課題では、複数の検索条件に一致するすべてのセルの個数を求める COUNTIFS 関数を使っています。上記の右下の表で、2014/1/1〜2014/12/31の１年間で給油時に記録した燃費が10Km/L以上が何回あったかの回数を求める計算式は、
=COUNTIFS(D$8:D$199,”>=” & M27,D$8:D$199,”<=” & N27,G$8:G$199,”>=” & R27)
計算式では、ピンクの式で期間、茶色の式で燃費の条件を指定し、左の表からその両者の条件に一致するセルの個数をカウントするものです。

今回はちょっと複雑な計算式でしたが、ご興味のある方はこのExcelファイルをアップロードしましたので、以下をクリックしてダウンロードしてみてください。今回の分を含めたTip 1〜 Tip 4までのシートが入ったExcelTips.xlsx.zipを解凍して中身をご確認ください。Good Luck!

今日のExcel シリーズは日付操作に関わる小技で、以下の課題を選びました。

① 現在日の表示ならびに記念日からの経過日を計算する
② 経過日を年月日で表示する

まずは①の例を以下に示します。

現在日の今日の日付とは、Excelでファイルを開いた日に合わせて、その日を表示するもので、上記の例ではB3のセルが本日投稿日の３月２７日と表示されたものです。B3のセルには、その計算式を =TODAY() とすればOKです。次にある記念日から現在日までの経過日を計算するのは単純に引き算をすれば、ことが済みます。上記の例では、C3のセルに震災日を入力してD3のセルに、その計算式 =B3-C3 を入力すると、経過日は1843日となります。

②では、経過日を年月日で表示させる方法です。例として、①の経過日 1843日では味気ないので、以下に②の課題に沿った表示例を示します。

上記ではG1のセルに入力された日にちと現在日との相違を H1の計算式で求めたもので、結果は年月日で表示されます。ポイントは計算式に DATEDIF を使って、
=DATEDIF(G1,TODAY(),”Y”)&”年”&DATEDIF(G1,TODAY(),”YM”)&”ヶ月”&DATEDIF(G1,TODAY(),”MD”)&”日”
とするところです。ところで、数字に混ざって文字が表示されますが、前回に示した応用で、&”任意文字” の最後尾に & を加えると、計算結果の数値の間にも文字を挿入することができます。

本日のテーマはその活用範囲が限定されると思いますが、私は購入品リストなどの記録ファイルに経過日を求める欄を設けたりしています。
テレビや冷蔵庫の家電品、パソコン本体や周辺機器の経過日など、時おり眺めては「もうこんなに経ったのか」はまあいいとして、自分の結婚記念日などを入力すると、ハッとするどころでは済まなくなったり．．．

本日のExcel「便利な技」第２弾は、セル中の文字に関する下記の操作方法です。
① 数値セルに表示されている文字を無視する
② 計算結果に文字を添える
一例として、アウタロウが今月、外部で充電した記録をインターネット上で調べてコピペしたものを用意しました。

インターネット上の表から該当部分をドラグコピーしてExcelにペーストしたものが、AからF列に相当します。そこで、今日の演題である①をG列に、②をH列で説明します。
①　数値を表示したセルで単位などの文字が入ったものをよく見かけますが、このセルの数値を単純に計算式に取り込むとエラーが出ます。例えば、表では税抜を税込にするべく、1.08倍の掛け算をすれば良いのですが、G2のセルで、 =F2*1.08 と入力しても文字が邪魔してエラーとなります。そこで、問題の”円”を省いて処理するために、=SUBSTITUTE(F2,”円”,””)*1.08 とすることで、計算が可能となります。
②　今度は逆に、計算結果に円などの文字を添えたい時にはどうするか、の問題です。H列のケースでは左のセルの計算結果を整数で切上げたものに”円”の文字を加える方法で、例えばH2のセルの計算式は、=ROUNDUP(G2,0)&”円” となります。一般的に文字を加えるには、計算式に &”任意文字” と書き込むだけなので、便利ですね。

文字を添えるのに、もう少し複雑なケースの処理は次回（多分）にしたいと思います。

今朝起きると、薄っすらと雪が積もっていました。道路に積もるほどではなく、周辺はやがて溶けてしまいましたが、近くの山はしばらく樹氷が楽しめそうです。
さて、掲題はシリーズが始まったばかりなのに、いきなりの番外編で恐縮です。何分、昨夜の投稿は時間切れで、途中止まりでした。以下は前回の②の応用編です。

② 空欄のセルはその行を表示しないように纏めて処理する
⇨　”②” 空欄の行を纏めて削除する

上記 “②” の操作例をよくやっているインターネットからのコピペを例に説明します。右の表は安曇野・穂高の今年２月の気象データです。このデータをExcelにコピペすると、やたらと空欄が生じてしまうので、この空欄を一括削除する例を以下に示します。入手した情報をこうして、簡単にExcelへ移植できます。

ところで、今回のインターネット情報では先月の穂高の気象データを例にしましたが、この情報のURLは、「http://www.data.jma.go.jp/obd/stats/etrn/view/daily_a1.php?prec_no=48&block_no=0404&year=2016&month=2&day=&view=」
開いたページから任意の年月のデータが見れますが、もしダイレクトに昨年１月のデータを見たい場合は、上述の中で、2015&month=1とリネームしてブラウザのURL欄にコピペすれば、その月の気象データが表示されます。皆さんのお住まいの地域を調べたい場合は、ここをクリックしてみてください。都道府県別の各地の情報が見れます。では、Good Luck!

そろそろ年度末となりました。年度末とは何ら関係がないのですが、今回から数回（多分５〜６回）に渡って、掲題についてのご紹介をしたいと思います。自分で今までExcelを使った中で、これは便利な技だと思ったものをブログ毎に２つづつ解説します。【その１】では、以下の解説です。
① 計算式のセルで、その計算対象のセルが空欄で計算結果に意味がない時には対象のセルに何も表示させない
② 空欄のセルはその行を表示しないように纏めて処理する

①　上表は支払額から個数を割った単価を計算したものです。単価の列で例えば、セル【E3】は計算式が【=C3/D3】で、このセルをドラッグしてコピーするとこの列が出来上がります。しかしながら、黄色のセル、E6やE９は計算対象のセルが空欄で、計算エラーの表示となります。このエラー表示を消すのが今回の技で、対象のセルの計算式に細工をして、B表のセルJ6ではその計算式を【=IF(H6=””,””,H6/I6)】とIF文を書き加えると、H6のセルが空欄ならば計算をしない、となってB表のようにJ6, J9のセルは空欄となります。このJ6の数式をJ列にドラグコピーすれば、B表の完成です。

②　次にC表では空欄のセルは一括操作で行を非表示にさせる技です。上表では対象は２行ですが、行が多いほど効果を発揮します。その方法の結果を示したのが表３で、18行と21行が非表示となりました。その手順は、
対象となるセル範囲、表３の元となる表で、 C15からE24 を選択してから「ホーム」タブにある編集グループの「検索と選択」ボタンをクリックして「条件を選択してジャンプ」をクリックします。ジャンプ機能の選択オプションが開きます。オプション一覧より、空白セルをクリックして、OKをクリックします。 その後、ホームタブのセルグループの書式をクリックして、表示設定の、「行を表示しない」を選択します。空白だった行が一気に非表示となりました。
【MacのExcelでは】
対象セルを選択して、「編集」⇨「ジャンプ」⇨「セル選択」⇨「空白セル」⇨「OK」とクリックして、その後「書式」⇨「行」⇨「表示しない」をクリックします。
なお、表３の非表示を元に戻すには、マウスをドラグして対象の行を選択し、右クリックして「再表示」をすれば元通りになります。行に限らず、列の非表示も同様な処理で可能となります。あまり利用価値はないかも知れませんが、できるだけ印刷範囲を絞りたい時などは便利です。

この方法をすでにご存知の方や、まずは利用することがないと思われる方には冗長なブログでした。次回は別テーマから選択してみます。