MOS受験に向けて8（関数１）

MOS受験に向けての記事の前にSOCHIでレジェンド葛西選手が銀メダル、７回目のオリンピックで初メダル41歳の快挙！
羽生選手のフィギュアの金メダルもすごかったですね。高梨沙羅選手は普通に力をだせば金メダルだったと思うけど、彼女はまだ若いので次回に期待しましょう。理研の小保方晴子さんのSTAP細胞作製のニュースに続きいいニュースが続きますね。うれしいことです。

MOS受験に向けての内容は問題集の並びに沿って説明してきましたが、ここからは問題集の順序にこだわらず説明していきます。
今回から関数を取り上げます。

関数の数は2010時点で419個あります。2013でさらに新関数が50個増えて469個になっているようです。

2007でSUMIFS、COUNTIFS、AVERAGEIF、AVERAGEIFS、IFERRORなど使える関数が導入されました。
2010ではRANK.EQ(ランクイコール）、RANK.AVE(ランクアベレージ）、以外にもCUBEMEMBER、CUBEVALUEなどのキューブ関数（外部データを扱う関数）が導入されています。

MOS試験では一般のレベルでは
　SUM(合計値）、AVERAGE(平均値）、MAX（最大値）、MIN(最小値）、COUNT(数値の個数）、COUNTA(数値、文字列のデータ個数）、
　IF(条件によって場合分け）、AND（すべての論理式を満たすかどうか判断）、OR(複数の論理式のうち、どれか一つでも成り立つか判断）
　NOT（論理式が真のとき偽を、偽のとき真を返す）、IFERROR（数式がエラーのとき指定の値を返す）、
　SUMIF（条件に合致した数値の合計）、SUMIFS(複数の条件に合致した数値の合計）、AVERAGEIF（条件に合致した数値の平均）、
　AVERAGEIFS(複数の条件に合致した数値の平均）、COUNTIF（条件に合致したデータの個数）、COUNTIFS(複数の条件に合致したデータの個　　数）　

Expertレベルでは、
　SUMPRODUCT（配列同士の積の合計）、FREQUENCY（指定した期間の度数分布）、LARGE（大きい順から指定した順位に当たる数値）、
　SMALL(小さい順から指定した順位に当たる数値）、RANK.EQ(指定範囲内での順位）、RANK.AV(同順位の場合に順位の平均値）、
　TODAY（本日のシリアル値）、DATEDIF(開始日と終了日から期間を求める）、DATE（年月日の数値からシリアル値）、
　YEAR(シリアル値から年（1900～9999)を求める）、MONTH(シリアル値から月（1～12）、DAY(シリアル値から日（１～31)を求める）
　TIME(時分秒から時刻のシリアル値）、HOUR(シリアル値から時(0～23）を求める）、MINUTE(シリアル値から分（0～59）を求める）
　SECOND(シリアル値から病（0～59）を求める）、
　ROUND(指定した桁数で四捨五入）、ROUNDUP(指定した桁数で切り上げ）、ROUNDDOWN(指定した桁数で切り捨て）、INT（少数切り捨て）
　FV(複利による将来価値）、PMT(（ローンや投資の定期支払額）、NPER(ローン返済や目標金額をためるに必要な回数）、RATE(ローン返済は　目標金額をためるのに必要な利率）
　PROPER(英単語の先頭大文字、2文字目以降小文字）、UPPER（英単語のすべて大文字）、LOWER(英単語のすべて小文字）
　SUBSTITUTE(文字列中の特定文字を別の文字に置換）、LEFT（文字列の左から指定された数の文字列取り出す）、RIGHT(文字列の右側から指　定された数の文字列取り出す）、MID(文字列の途中から指定された数の文字列）、ASC(全角文字を半角文字に変換）、JIS(半角文字を全角文　字に変換）、TRIM(余分な空白削除）、REPLACE(文字列の任意の開始位置から指定された数の文字列）、LEN(文字数）、FIND(指定した文字列　先頭から何番目か）、SUBTOTAL(表示しているデータだけ集計）
　VLOOKUP(縦に並んだ一覧表から対応データ）、HLOOKUP(横に並んだ一覧表から対応データ）
　CUBEMEMBER、CUBEVALUEなどのキューブ関数

ほぼ以上のような関数がMOS試験に出題されるようですが、実用的には十分ではないと思います。
上記以外では、
　CEILING(倍数に切り上げ）、FLOOR(倍数に切り下げ）、MROUND(数値の値に最も近い基準値の倍数）、INDEX(行列で検索）、MATCH（検査値の　位置）、INDIRECT（セル範囲を間接的に参照）、MOD(余りを求める）、OFFSET(可変の範囲指定）、COLUMN（列番号）、ROW（行番号）、
　PHONETIC（ふりがな表示）、WEEKDAY(曜日）、WORKDAY(平日で指定した日数の後の日付）、REPT(繰り返し表示）
などの関数が実用レベルでは役に立ちますよ。

次回以降にできるだけ具体例をあげながら説明していきます。



　

MOS受験に向けて７（オプション２）

オプション１の続きです。
オートコンプリートという機能はどなたもご存じの機能です。
キーボードからの入力を補助する機能の一つで、過去の入力履歴を参照して次の入力内容を予想し、あらかじめ表示してくれる機能です。
文字を入力すると、同じ列内に同じ読みから始まるデータがあれば、そのデータが表示され簡単に入力できます。

ただ、上の列と同じデータを入力する場合はともかく、それ以外の場合にはわずらわしいことになります。
そこで[Excelのオプション]画面の[詳細設定]タブにおいて[オートコンプリートを使用する]のチェックを外せばOKです。



オートコレクトという機能もあります。
入力しやすい単語を自動的に修正する機能で先頭文字を大文字に変換したり、スペルを自動修正する機能猪飼にもURLやメールアドレスを入力すると自動的にハイパーリンクが設定される機能をもっています。
とくにハイパーリンクに設定したくないという場合もあると思いますが、そのときはExcelのオプション]画面の[文書校正]タブにおいて、[オートコレクトのオプション]ボタンクリックして開く[オートコレクト]画面の[入力オートフォーマット]タブの[インターネットとネットワークのアドレスをハイパーリンクに変更する]のチェックを外せばOKです。



「日」と入力してフィルハンドルでドラッグすると、順に「火」、「水」・・・「土」と入力することができます。
このようなオートフィルや並べ替えの基準となるのは[ユーザー設定リスト]です。
[Excelのオプション]画面の[詳細設定]タブにおいて[並べ替えや連続データ入力設定で使用するリストを作成します]の[ユーz-設定リストの編集]ボタンをクリックします。
開いた[ユーザー設定リスト]画面の[リストの項目]欄に「横浜」、「川崎」、「藤沢」・・・などと入力して[追加]ボタンをクリックすると、新たなリストが作成できます。
ところが、「北海道」、「青森」・・・「沖縄」などのように数十項目もあるときには、手入力していては大変です。
項目が多い場合にはリストを用意して、リストを選択して(ボックスに範囲が表示される）、[インポート]ボタンをクリックすると、新しいリストを作成することができます。







並べ替えをおこなうときに、並べ替えの基準となる列の任意セルを選択して、[データ]タブにおいて、[並べ替え]ボタンをクリックすると、[並べ替え]画面が開くので、[順序]の▼をクリックして、さらに[ユーザー設定リスト]をクリックすると[ユーザー設定リスト]画面が開きます。





どうでしょうか？前述の[ユーザー設定リスト]との違っています。[インポート]ボタンがないのです。
このリストを見てインポートできないと思われる方もおられるのでは？
不親切と言わざるを得ません。設定に関することは、[Excelのオプション]画面で行うようにということでしょうか？

なお、エクセル2007でも同じことが言えますが、[Excelのオプション]画面では、[詳細設定]ではなく、[基本設定]タブに[並べ替えや連続データ入力設定で使用するリストを作成します]の[ユーz-設定リストの編集]ボタンはあるので注意が必要です。

そのほか、VBAでマクロを組むときに便利だといわれるR1C1参照形式を表示させるには[Excelのオプション]画面の[数式]タブにおいて、[R1C1参照形式を参照する]にチェックを入れればOKです。
この形式にすると列が数字で表示され、最終列の「XFDが「16384」と表示されます。

本稿に書いたことはMOS試験に出題される可能性は低いと思いますが、実用的なので覚えておかれて損はないとおもいます。

MOS受験に向けて６（オプション１）

エクセルのオプションについてもMOS試験の対象になりますが、範囲が広いので出題の可能性の高いものを考察してみます。

まず、ワークシートの数については出題の可能性が高いようです。
ワークシートの数は既定で3枚になっていますが、これはオプション画面の[基本設定]タブの[ブックのシートの数]で行います。




1～255まで設定ができます。
ただ、今開いているブックのシートの数は変わりません。
新しいブックを開いたときに設定されます。
このためでしょうが、シートの数は255までで256以上には増やせないと思っている方もいるようですが、そんなことはありません。
1000でも5000でもパソコンの能力（メモリー）しだいで増加できます。
最初に255のシートからすべて選択した状態で［挿入］→［ワークシート］でまず、510にして、あと1020、2040、4080まで増やした例を示します。



顧客ごとにシートを使う場合などには、もっと必要になるかもしれませんが、シートを選択するのが面倒になりますので、実務上は数百のシートが限度かもしれません。

それからシートについては、既定で3(枚）になっていますが、シートを削除したり、非表示にできることをご存じの方は多いと思いますが、最低1枚のシートは残しておく必要があります。
ところが、シートすべてを非表示にすることができます。

[Excelのオプション]画面の[詳細設定]タブの[シート見出しを表示する]のチェックを外せば非表示にすることができます。


次のような画面になります



ただ、非表示にする意味があるのでしょうか？
見せたくないのなら、ほかの保護機能を使うべきだと思いますが・・・

[Excelのオプション]タブにはそのほかに、[フォントサイズ]（既定で11ポイント）、[ユーザー名]などの設定(変更）ができます。

そのほかでは、FOM出版社社の問題集には「Enterキーを押したあとにセルの移動する方向を右に変更してください」という問題もありました。

[Excelのオプション]ダイアログボックスの[詳細設定]タブの[Enterキーを押したあとにセルを移動する]にチェック（既定でチェックされている。チェックを外すとEnterキーを押してもセルは移動しない）を入れ。[方向]を[右]にすればOKです。
この機能は多量のデータを右方向に入力していくときにとても便利な機能で、このように設定されている方もいます。

それから、これはMOSの試験には出ないと思いますが、選択範囲入力といいう機能があります。
入力する範囲を選択してEnterキーを押したときに移動する方向を「下」(既定）に設定してあれば、下表のようにB3セルにデータを入力してEnterキーを押すと、B4セルに移動、以下B16セルまで移動、次にC3セルに自動的に移動し、以下C16まで移動、データの入力ができます。
この機能のメリットはB16からC3セルに自動的に移動してくれるので、効率的です。



力する範囲を選択してEnterキーを押したときに移動する方向を「右に設定し、下表のようにB3セルにデータを入力してEnterキーを押すと、C3セルに移動、以下F3セルまで移動、次にB4セルに自動的に移動し、ここから右方向に入力し、同じように入力して最後のF8まで自動的に効率よく入力することができます。



この機能で注意しておくことがあります。入力間違いなので、セルをクリックすると、範囲選択が解除されてしまうので、もとに戻る場合んはShift+」Enterで戻って入力する必要があります。

入力に関してAltキーと↓キーを押すと、その上の同じ列に入力されている文字列を一覧表示することができるので、所望の文字列をクリックすると入力することができます。この場合に選択セルの上のセルが連続している範囲に限定されますが・・・
入力規則のリスト入力に似た機能だと思います。


まだまだオプションには設定に関することは数多くありますので、ここでいったん終了して次回にまた続きを書きます。

わいわいデジタルサロン（万年カレンダーの別の作り方）

昨日、万年カレンダーについてふれました。
その方法は、WEEKDAY関数を使ってその月の1日がどの曜日に該当するか調べ、該当する曜日に「１」とする方法でした。
その際に別の方法もあると書いていますが、その方法について説明します。
それと説明を省略している部分がありましたので、初級者の方にも理解していただけるように書き加えました。

まず、WEEKDAY関数は3種類あります。
WEEKDAY(シリアル値、1または省略）→日曜：1、月曜：2、・・・・土曜：7
WEEKDAY(シリアル値、2）→月曜：1、火曜：2、・・・・日曜：7
WEEKDAY(シリアル値、3）→月曜：01、火曜：1、・・・・日曜：6

カレンダーが日曜から始まる場合はWEEKDAY(シリアル値、1または省略）
月曜から始まる場合はWEEKDAY(シリアル値、2）を使えばいいですね。

今日述べる方法はWEEKDAY(シリアル値）を使って第1週の日曜日を求めて、その値からそれ以外の数字を挿入していく方法です。
WEEKDAY(1日のシリアル値）を求めると1から7までの数字が求まるので、「1」の場合に日曜日に「1」日、「2」の場合月曜日が「1」日というようになりますが、、「１」の場合に日曜なので、「１」日。「２」の場合は月曜日が1日になるので、日曜は月初めの1日から1を引いたもの、同様に「7」の場合は土曜日が1日なので日曜日は1日から「6」を引いたものになります。
したがって、カレンダーの先頭の一番左の日曜日は1にのシリアル値からWEEKDAY関数で求めた値を引いて「1」を加えたものになります。

まず、
下のような表を用意します。



次に
A1セルに「年」、B1セルに「月」を入力できるようにしておく。
H1セルにその月の1日のシリアル値（=DATE(A1,B1,1)）
I1セルに月末のシリアル値（=DATE(A1,B1+1,1-1)
J3セルにWEEKDAY関数（=WEEKDAY(H1))
を入力します。

いよいよA3セルに数式を入力していきますが、一つにまとめてみました。
「=$H$1-WEEKDAY($H$1)+COLUMN(A1)+(ROW(A1)-1)*7」と入力してコピーすれば下のような表ができます。
1つの式からすべてのセルにコピーできるということは、A3～G8を選択して、数式バーに「=$H$1-WEEKDAY($H$1)+COLUMN(A1)+(ROW(A1)-1)*7」と入力して、Ctrlキーを押しながらEnterするとこの範囲のすべてのセルにコピーされます。
数式をコピーできる場合には、複数セルを選択して、同様にCtrlキーを押しながらEnterすると一気にコピー・貼り付けができます。
このCtrlキーを押しながらEnterすると一気に入力できるキーは同じ数値を複数セルに入力する際に便利な機能です。エクセル2000まではこの機能は必須でした。2000ではスマートタグの機能がなかったので、ドラッグしてコピーすると罫線までコピーされて表の体裁がくずれるケースが多々ありました。

それからROW関数とCOLUMN関数は行や列を移動する際に数字が変わる場合にはとても便利な関数です。



前の月と後の月の日にちが混在しているので、不要なデータを条件付き書式でみえないようにします。
①A3～G3選択して、[ホーム]→[条件付き書式]→[新しいルール]、開いた画面の[数式を使用して書式設定するセルを決定]クリック、[次の数式を満たす場合に書式設定]ボックスに「=A3<$H$1」と入力、、[書式]ボタンクリック、[セルの書式設定]ダイアログボックスが開くので、[フォント]タブでフォントの色を「白」に設定します。前月の数字が見えなくなるはずです。
②A7～G8を選択して、①と同様の手順で条件付き書式で、「=A7>$I$1」と入力、同様にフォントの色を「白」にします。
③この状態（A7～G8を選択した状態）で、「=$G$7>=$I$1」と入力し、[罫線]タブで上辺の線だけ残してあとは消します。


その結果後の月の不要なデータと7行目のセルに入力がない場合に罫線を見えなくすることができます。



この方法のメリットはカレンダー内に入力する式を1つにすることができることにあります。

ほかにもいろいろとあるかもしれませんね。
ぜひ教えてください。

わいわいデジタルサロン（万年カレンダー、エクセルグラフ、などいろいろ）

今日もわいわいデジタルサロンは９名だと思いますが、盛況でした。
Ａ子さん、Ｂ子さんもこられ、エクセルで前回Ｎ子さんとＥ子さんが取り組んでいた、日にちと体重、血圧などの表を作ってもらい、折れ線グラフにして結果をみることにチャレンジしてもらいましたが、理解が速くあっという間にできていました。
ただ、前回と同じように範囲選択を間違っておかしなグラフもできたりしました。

男性のＷさんは前回ピクチャに保存した写真をUSBメモリにバックアップを取る作業を行い、その後サンプルを見ながらチラシを作ってもらいました。
最初からセンタリングや右揃えをスペースキーを使用して入力しておられたので両端揃えにて、べたに入力してもらい、最後にセンタリングや、右揃え、フォントの変更などを変更するようにサポートしました。
ただ、「拝啓」と入力されたのですが、「敬具」がでませんでした。
どこで、設定しているかすっかり忘れていました。
かえってから調べてみました。
[オプション]→[文書校正]、[入力オートフォーマット]クリックして、開く[オートコレクト]ダイアログボックスの[入力オートフォーマット]タブで、箇条書き、「記」、「拝啓」などの入力に対して「結語」などを挿入するかどうか設定できることがわかりました。
すっかり忘れてました。



それから女性のUさんからすごい要望がありました。
エクセルで万年カレンダーを作りたいということでしたが、まだ、関数もあまりやっておられないので、スケジュール表のような上から順に並んだ表から始めてくださいと言っておきましたが、このニーズは結構高いので、方法の一つを書いておきます。

考え方はWEEKDAY(シリアル値）関数を使うと
下記のように日曜日が1、月曜日が2、・・・・土曜日が7を返してきます。
そこでWEEKDAY(1日のシリアル値）を求めると1から7までの数字が帰るので、「1」の場合に日曜日に「1」日、「2」の場合月曜日が「1」日というようになります。



B3セルに「年」、C3セルに「月」を入力すると、その年月のカレンダーが表示されます。

B5セルに「=IF(A5="",IF(WEEKDAY($I$3)=COLUMN(A1),$I$3,""),A5+1)」と入力し、H5までコピー
B6セルに「=IF(H5>=$J$3,"",H5+1)」と入力、H6までコピーし、さらにこの行の数式を10行までコピーすれば終わりです。
なお、5行の式を一気にコピーできるようにA列を空欄にしています。
空欄にしない場合には、A5セルに「=IF(WEEKDAY($H$3)=COLUMN(A1),$H$3,"")」、B5セルに「=IF(A5="",IF(WEEKDAY($H$3)=COLUMN(B1),$H$3,""),A5+1)」を入力してG5までコピーします。
さらにA6セルも「=IF(G5>=$I$3,"",G5+1)」と入力しこの式をもとにコピーする必要があります。

条件付き書式を使えば一番下の行が空白になる場合がありますが、その行の罫線の色を白にしてを見えなくすることもできます。

ほかにも第1週の日曜日かあるいは土曜日の日にちを求めて、それを基準に作っていく方法もあります。この場合に土曜日は必ず1から7の数字が入りますが、日曜日は数字が前月の数字出て聞くるケースが多くなります。前月になる場合には条件付き書式などを使って見えなくするか、非表示にする必要があります。

Uさんは、まだ初心者ですが、熱心に勉強されているのでに説明すれば理解してもらえる日が必ず来ると信じてます。