MOS受験に向けて１９(シートの保護）

今回はシートや保護について述べます。
MOS受験では一般ではなく、Expertの範囲になりますが、一般を受験する方も知っておくべきことなのでとりあげることにしました。

・シート全体を保護する

内容変更、改ざんができないように保護したり、セルに入力したデータや数式の変更を防ぐことができます。
① [校閲]タブ→ [シートの保護]クリック
②[シートの保護]ダイアログボックスで[シートとロックされたセルの内容を保護する]にチェックが入っていることを確認の上、必要なら[シートの保護を解除するためのパスワード]を入力し、[OK]クリック、その場合にはさらに[パスワードの確認]画面が開くので、再度パスワードを入力し、[OK]クリックします。

あるいは
①[ホーム]タブの[セル]グループの[書式]の▼をクリック、[シートの保護]をクリックすると[シートの保護]が表示されるので同様の方法で保護することができます。

なお、[シートの保護]ダイアログボックスで[このシートのすべてのユーザーに許可する操作]の[ロックされたセルセル範囲の選択]と[ロックされていないセル範囲の選択]にはチェックが入っていますが、そのほかには入っていません。
したがってこのままシートを保護すると、どのセルも選択はできますが、削除したりデータを追加したり、塗りつぶしたはできません。選択することも許さないというのであれば、チェックをすべて外せばセルの選択すらできないようにできま。
ただ、シートの移動やコピーは不可ですが、シートの名前は変更できるようです。

・特定セルだけ入力できるようにする
セルの書式設定では、初期状態でシートのすべてのセルがロックされているが、セルの保護が有効になるのはシートの保護をしたときだけなので、入力や編集が必要なセルをロックを解除して、シートの保護を行えばよいわけです。

下の表を基に説明します。


この表でセル範囲[B4：B13][E4:E13]には入力できるように、ロックを外します。
[セルの書式設定](Ctrlキー+１キーで開きます）ダイアログボックスの[保護]タブで[ロック]のチェックを外せばOKです。


そのあと、[校閲]タブ→[シートの保護]をクリックして開く[シートの保護]ダイアログボックスで[シートとロックされたセルの内容を保護する]にチェックが入っていることを確認の上、必要なら[シートの保護を解除するためのパスワード]を入力し、[OK]クリックして保護します。

あるいは、[ホーム]タブの[セル]グループの[セルのロック]をクリックして（初期設定ではすべてのセルがロックされているので、チェックをはずすことになる）、その後[シートの保護]をクリックしてもOKです。




この場合に、初期設定の[このシートのすべてのユーザーに許可する操作]の[ロックされたセルセル範囲の選択]と[ロックされていないセル範囲の選択]にはチェックが入っていますが、このままでもいいし、[ロックされたセルセル範囲の選択]のチェックをはずしてもOKです。ただ、[ロックされていないセル範囲の選択]のチェックを外すとセル範囲[B4：B13][E4:E13]への入力ができなくなるので注意が必要です。

数式が入力されているセル範囲[C4:C13][D4:D13][F4:F16]と商品一覧表の表を保護するために、このような操作を行うわけです。結果として入力可能なセル以外はすべて保護されるとう必要以上の保護になります。

では逆に数式や商品一覧のセル範囲だけ保護して、そのほかは保護しないようにできないか。
逆の操作を行えばできます。すなわち、最初に全セル選択ボタンで全セルを選択した状態で、ロックを外し、その状態で保護したいセルをロックし、シートの保護を行えば、保護したいセルだけが保護されます。
ただ、入力セル以外は保護する方法の方が操作が楽だし、不要な入力を防ぐこともできるしいいのではないでしょうか。

この場合に数式をどうしても見せたくないという場合には、例えば上の表の例でセル範囲[C4:C13][D4:D13]の数式を見せたくない場合にはセル範囲[B4：B13][E4:E13]には入力できるように、[セルの書式設定]ダイアログボックスにおいて、ロックを外したあとにセル範囲[C4:C13][D4:D13]を選択した状態で、同ダイアログボックスで、[ロック]ととも[表示しない]にもチェックを入れて、シート保護を行えばOKです。


・入力可能な人も制限する（範囲の編集を許可する）
上から2番目の表で、今回はセル範囲[B4：B13][E4:E13]の入力にもパスワードが必要となるように設定します。
最初の全セルがロックされている状態で説明します。

①セル範囲[B4：B13][E4:E13]を選択した状態で[校閲]タブの[範囲の編集を許可]クリック
②[範囲の編集の許可]ダイアログボックスが表示されるので、[新規]クリック


③[新しい範囲]ダイアログボックスが表示されるので、パスワードを入力し[OK]クリック


④再度パスワード入力が求められるので、同じパスワードを入力し{OK}クリック
⑤元の画面に戻るので[シートの保護]クリックすればOKです。

このように設定すると、セル範囲[B4：B13][E4:E13]への入力はパスワードを知っている人のみが入力可能になり、その他のセルは保護された状態になります。
シートの保護をするときに、別のパスワードを設定することも可能です。上位の管理者ともいうべき人がシートの保護を解除することができるようになります。

必要事項を入力して保存後、ファイルを開くと、シートの保護を解除しない限りこの機能は生きています。

MOS受験に向けて１８(入力規則２）

前回に続いて入力規則についてとりあげます。

問題文を再掲します。表の一部を変更しました。


上の表で
1．セル範囲[B7:B14]のカタログ名を入力する際、セル範囲[E7:E17]のデータをドロップダウンリストから選択できるように入力規則を設定してください。
2.セル[B18]の「希望納期」を入力する際、、セル［C2］の「依頼日」から7日目以降の日付しか入力できないように入力規則を設定してください。
また、7日目より前の日付を入力した場合には、次のようなエラーメッセージが表示されるようにしてください。
　スタイル：停止
　タイトル：希望納期エラー
　エラーメッセージ：希望納期は依頼日から7日目以降にしてください。


リスト入力について
①リスト入力させるセルを選択
②[データの入力規則]画面の[入力値の種類]をリストにして、
③[元の値]に半角コンマを介して
「東京,横浜,名古屋,大阪,京都」などと入力
④[OK]クリック
リストがある場合には
①入力規則を設定する範囲を選択
②[元の値]の範囲としてリスト範囲ｊの「$E$3：$E$12」などとして入力して［OK］クリック
これが問題1の解答です。
「LED AQUOS LC-22K9-W」などの面倒な入力を素早く、しかも間違いなくできるわけで、エクセルの素晴らしい機能の１つです。



[入力時メッセージ]について
　入力規則をせっていしただけでは、不正なデータが入力されたときに、エラーメッセージは表示されますが、どのようなデータを入力すればわかりません。入力規則では、セルを選択したときにポップヒントを表示してデータに関する説明を表示できます。

［データの入力規則］ダイアログボックスの［入力時メッセージ］タブでセル選択時のメッセージを［タイトル］に「配布部数」、［メッセージ］欄に「100部単位の数字を入力してください」などと設定できる

[エラーメッセージ]について
　不正なデータが入力されたときにエラーの原因を知らせるメッセージを表示させることができます。
　［エラーメッセージ］タブの[スタイル]で[停止]、[注意]、[情報]を選択できます。
　［停止］は条件に合わない場合入力できないようにするときに使います。これが既定で普通の使い方です。
　［注意］はデータを入力しなおすか、そのまま続行する場合に使用します。
　［情報］は注意を促しいたいLED AQUOS LC-22K9-WLED AQUOS LC-22K9-Wときに利用します。
既定では


このようなメッセージが表示されますが、オリジナルなメッセージを表示させることもできます


これが問題２の解答ですが、その前に次のような設定をします。



この問題をさらにメーカー別に液晶テレビの型式を表示させる問題だとどうでしょう。


この問題になるといっそう実用的になると思いますが・・・

①まず、シャープの液晶テレビの一覧であるセル範囲［F7:F11］、［F17：F24］に「シャープ」、「東芝」などの名前を付けておきます。
②メーカーのところはメーカー別にリスト入力できるようにします。①でつけた名前と完全に一致させる必要があります。
③セル範囲［C7:C14］を選択し、［入力規則]ダイアログボックスの[設定]タブで[入力値の種類]を「リスト」にして[元の値]に「=INDIRECT(B7）」と入力し、[OK]をクリックします。

INDIRECT関数を使うところがポイントです。
この関数はセルに入力されている文字列を介して間接的にセル範囲を指定する関数で、この場合のセル[C7]はセル[B7]を参照していますが、[B7]セルに入力されているのは「シャープ」という文字列なので、同じ「シャープ」という名前が付けられたセル範囲[F7:F14]を指定することになるというわけです。
この関数は使えます。いろいろな場面で活躍します。このような有用な関数こそMOSに出題すべきと思いますが、範囲外です。
さらに、メーカーとカタログ名の間に列を挿入して、」「テレビ」以外にも「DVDレコーダー」、「デジカメ」、「冷蔵庫」、「洗濯機」などの分類を追加することもできます。たくさんの表を用意して、それぞれに異なる名前、例えば「シャープテレビ」などとし、INDIRECT関数に使用する名前も「シャープ」、「テれビ」などを「＆」を使用して結合させればできるはずです。


そのほか、数式を使えば、
重複データのを入力できないよういするには
［元の値］に「COUNTIF(B:B,B3)=1」などと入力するとB3から下のB列のデータの重複を防ぐことができます。
WEEKDAY関数を使えば、平日しか入力できないようにしたり、土日しか入力できないようにすることができます。

もちろん手入力しても入力規則は機能しますが、コピー貼り付け、オートフィルなどで入力する場合は機能しませんので注意が必要です。

MOS受験に向けて17(入力規則1）

今回は入力規則について触れます。
MOS受験では、少なくとも2003までの上級、一時的に存在したMCASには出題の範囲に入っていましたが、2010ではどうも一般にもExpertにも入っていないようです。ただ、日経BPの問題集の「形式を選択して貼り付け」の章に「…セル範囲○○に設定されている入力規則をせる範囲●●にコピーしなさい」という問題はあります。
今一つはっきりしませんが、非常に有用な技術なので、あえて取り上げることにしました。

入力規則とは、セルに入力できるデータの条件を設定して、無効なデータが入力されないようにする機能です。
入力データを制限する以外にも、入力する値をリストから選択させたり、無効データの入力時にオリジナルメッセージを表示させることもできます。
帳票類などにデータを入力するときに、入力ミスを極力少なくするための有効な手段です。

・日本語入力の設定
セルを選択したときに入力モード指定
セルを選択したときに、自動的に指定した日本語入力システムの入力モードに切り替える。
入力するデータのタイプに合わせて設定すると、入力時に手動で入力モードを切り替える必要がありません。



手順は
①表内の［氏名」列と［住所］列を選択、[データ]→[入力規則]　、[データの入力規則]画面において、[日本語]タブで[ひらがな]を選択、[OK]　クリック
②表内の「フリガナ」列選択、②[データ-の入力規則]画面において、[全角カタカナ]を選択、[OK]クリック
③表内の「〒」列選択、[オフ(英語モード)]を選択、[OK]クリック

さらに、フリガナ列に「＝PHONETIC（氏名セル）」と入力すると自動的にフリガナが振られます。

・データの入力規則
セルに入力可能なデータの種類、データの範囲を設定
[データ]→[入力規則]、[入力値の種類]で整数、小数点数、リスト日付、時刻、文字列などを指定できるほかオリジナルなユーザー設定することもできる。






問題をやってみましょう。


上の表で
1．セル範囲[B7:B14]のカタログ名を入力する際、セル範囲[E7:E17]のデータをドロップダウンリストから選択できるように入力規則を設定してください。
2.セル[B18]の「希望納期」を入力する際、、セル［C2］の「依頼日」から7日目以降の日付しか入力できないように入力規則を設定してください。
また、7日目より前の日付を入力した場合には、次のようなエラーメッセージが表示されるようにしてください。
　スタイル：停止
　タイトル：希望納期エラー
　エラーメッセージ：希望納期は依頼日から7日目以降にしてください。

答えは次回行います。

オリオン座ベテルギウスいつ消える？

年間を通して、星座でもっともわかりやすいのは、冬の星座オリオン座でしょう。
ところが、このオリオン座び赤い星ベテルギウスがいつ大爆発を起こしてもおかしくないというのです。



以下に雑誌「ニュートン」2012年2月号）の記事から抜粋し紹介します。

題名は「爆発せまるベテルギウス」（爆発後は昼でもみえる！4年後にオリオン座が欠ける）
「・・・オリオン座の中でも、とりわけ目立つ星といえば、オレンジ色の1等星「ベテルギウス」だろう。子犬座ノプロキオン、大犬座のシリウスと合わせて「冬の大三角」を構成するおなじみの星である。ベテルギウスは地球から640光年にあり、太陽のおよそ20倍の質量をもつ。・・・
このベテルギウスは「超新星爆発」という大爆発をいつおこしてもおかしくないといわれている。若い星の中心部では、水素が核融合によって燃えている。このベテルギウスのように太陽の20倍の質量をもつ恒星の場合、この状態はおよそ1000万年つづく。これは一生の約9割の期間に相当する。
その後燃料の水素がなくなってくると、星の内部の圧力バランスがかわり、膨張する。これにともない表面の温度は下がり、赤くなる。このような星を「赤色巨星」という。中でも巨大なものは「赤色超巨星」と呼ばれている。これが今のベテルギウスである。星の質量が太陽の8倍以上であれば、赤色巨星となった星はいずれ「超新星爆発」をおこしてその一生を終える。質量が20倍であれば赤色巨星となってから約100万年後に超新星爆発をおこす。
超新星爆発は、夜空の１点が突然輝きをまし、たった一つの星が1000個ほどの恒星からなる銀河と同等の明るさに輝く瞬間である。ベテルギウスも最後にはこの超新星爆発をおこす。
今、ベテルギウスが爆発すれば、1604年に観察された「ケプラーの星」(1万3000光年先）以来となる。このケプラーの星は、ピーク時には木星ほどの明るさで約1年間輝いた。また、昼でもみえる超新星爆発としては、1504年に中国や日本で観察され、「かに星雲」（7200光年さき）をつくった爆発以来となる。かってない至近距離での爆発とあって、実際に爆発すれば、その観測で星の進化など様々な研究が進むと期待されている。
・・(中略）・・・
赤色巨星となり、余命100万年を切ったベテルギウスではあるが、正確にいつ死をむかえるかはわかっていなし。約100万年先かもしれないし、ひょっとしたら1年先、あるいは明日かもしれない。・・・・」




今みているベテルギウスは640年前の姿なので、実際には超新星爆発は起きているかもしれないというのです。
では爆発次期をあらかじめしるこ不可能かというと、ノーベル賞を受賞した小柴博士が初めて検出したことで知られるニュートリノを観測できれば可能なようです。
超新星爆発の直前ニュートリノが大量に放出されるので、スーパーカミオカンデなどで観測されればその後1.5日後にベテルギウスは突然明るく輝きだす。輝きだしてすぐに高温になり青くなる。3時間後には半月ほどの明るさになる。ただしこの光ははるかに小さな面積に凝縮されているので、面積当たりの明るさで比較すると、満月の100倍ほどの明るさで、3～4か月ギラギラと輝くことになるそうです。
1年ほどたつと、昼間にはみえなくなり、4年後には、夜でも肉眼ではみえなくなるとのこと。
オリオン座の一角が欠けるのは残念なことですが、それ以上にこの史上最大の天体ショーを生きているうちに見たいものです。

MOS受験に向けて１６表示形式３）

表示形式の続きになります。

表示形式による場合分け
セルに入力されているデータが正の場合、負の場合、０の場合、文字列の場合に表示形式を設定し、場合分けする。
セミコロン(;)で区切って書式記号を並べます。



この場合分けはいろいろな応用が利きます。


問1.文字は表示させ、数字を表示させないようにするには？
問2.文字も数字も表示させないようにするには？
問3.実際に入力されているのは「１」だけで集計しやすくしている（SUM関数を使える）が、表示は「○」にしたい場合には？

条件付き表示形式
指定された条件にしたがって数値の表示形式を変更することもできます。そのために上記の4つの書式のうち最初の２つ、すなわち正の数と負の数の書式のかわりに条件を指定します。3つ目の書式にはほかの2つの条件と条件を満たさない場合の表示形式を：（セミコロン）で区切って入力します。条件の指定には数値を比較演算子を使用します。

例　5000以上なら青の#,##0形式、2000未満なら赤の#,##0、それ以外は黒の#,##0形式で表示
　［>=5000］［青］#,##0；［<2000］［赤］#,##0；［黒］#,##0
<a href="http://blogimg.goo.ne.jp/user_image/56/a3/7eaef585d5055db86dc506f28fdebbca.jpg" border="0">

問4.　平成1年を平成元年と表示する（平成元年の開始日1989/1/8（シリアル値32516））には？
　平成1年の末尾のシリアル値はあえて書きませんので・・・


漢数字について
「三」、「参」などの漢数字で表示させることもできます。




ユーザー定義の種類ボックスに[DBNum1]、[DBNum2]、[DBNum3]を使って設定すればできます。「DBNum」なすべて小文字でも大文字でもかまいません。

それからNUMBERSTRING関数を使ってもOKです。
書式は「NUMBERSTRING(数値,書式番号）」となり上の表で書式番号1,2.3で求めたものです。

問1と問2について[ユーザー定義]ボックスの種類ボックスに「;;」(セミコロン２つ）と入力すれば数字だけを、「;;;」(セミ子rン3つ）入力すれば、文字も数字を非表示にすることができます。この場合、「；」と入力すると自動的に「;;」になって数字だけを非表示にすることができるようです。

問３について、[ユーザー定義]ボックスの種類ボックスに「"○";」、「"○";;」あるいは「"○";;;」と入力すれば数式バーには[1]が表示されていますが、表には○が表示されています。この場合に「0」が入力されている場合には「"○";」では「0」のセルも「○」と表示されます。文字列が入力されている場合には「"○";;」では文字列が表示されます。
場合によって使い分けたらよいと思います。

問４について
平成1年12月31日のシリアル値は「32873」になります。」
したがって、[ユーザー定義]の種類ボックスに「[<32516]ggge"年"m"月"d"日";[<32874]"平成元年"m"月"d"日";ggge"年"m"月"d"日"」と入力すればOKです。