名前定義編

名前定義で、計算式が見やすくなる ファイルをダウンロード <課題>A2*B2=C2の計算の場合 ▢ A列の単価列に名前定義をする。   ① 名前定義をする範囲を選択 (A1:A10)  ② 「数式」-「選択範囲から作成」…

Continue reading

入力規則

入力規則で、勝手なことをさせない 究極の設定

ファイルをダウンロード

1.特定のセルに数式を入力させない

  ① 入力規則のユーザー設定を利用  (図1)
    「データ」-「データの入力規則」
    =ISERROR(FORMULATEXT(A5)) 


  ② A5セルに数式を入力すると、メッセージを表示

2.文字数を入力制限する(1文字~3文字)(D4セル)

  ① 入力規則の文字列(長さ設定)を利用
  ② D4セルを選択
  ③ データの入力規則の文字列(長さ指定)を選択
  ④ 最小値=1、最大値=3 を入力

  ⑤ D4セルに4桁の数字を入れると下図のようにメッセージがでる。

3.土日不可の設定

  ① 入力規則のユーザー設定を利用
   ② =AND(TEXT(E3,”aaa”)<>”土”,TEXT(E3,”aaa”)<>”日”)

4.開始日から終了日まで3日以内の場合のみ入力可能

  ① 入力規則のユーザー設定を利用
  ② =E4-E3<4

5.セル範囲の中に入力は3つ以内

  ① 入力規則のユーザー設定を利用
  ② =COUNTA($D$1:$D$26)<4

6.文字(名前)の中に半角が必要

  ① 入力規則のユーザー設定を利用
  ② =ISERROR(FIND(” “,#REF!))=FALSE

7.名前が“ア”で始まる人専用

  ① 入力規則のユーザー設定を利用  
  ② =LEFT(PHONETIC(E4),1)=”ア”

8.入力禁止(何も入力できない)

  ① 入力規則のユーザー設定を利用
  ② =E3=“”

入力規則でリストを連動させる

ファイルをダウンロード

[課題]

 ▢ A列に地域(東京、大阪、広島)をリストに出し、地域リストに応じた
   名前をB列にリスト表示する。

  ① A1:B2をテーブル化
  ② D1:F1に「地域」で「名前定義」
  ③ D2:D4に「東京」で「名前定義」
  ④ E2:E5に「大阪」で「名前定義」
  ⑤ F2:F6に「広島」で「名前定義」
  ⑥ A2に入力規則でリストで数式欄に「=地域」と入力

  ⑦ B2に入力規則でリストで数式欄に「=INDIRECT($A$」と入力
  ⑧ B2をB3:B4にコピーする

条件付き書式編

条件付き書式①  セルの値(数字や文字)で書式を変更する

▢ 条件付き書式とは

   セルの状況によって、セルの書式を自動的に変化させる仕組みのこと。

▢ セルの状況は3種類 (図1)

  ① セルの値(数字や文字)
  ② 全体に対する割合や状態 計算式の結果と同じ

▢ セルの値(数字や文字)の条件で色付けする。

  条件:40より大きい 等 (簡単な使い方)(図2)

「ホーム」―「条件付き書式」ー「セルの強調表示ルール」-「指定の値より大きい」

▢ 全体に対する割合や状態で書式を変更する

  1.全体に対する割合や状態(データーバー)
    ① 全体の中でどれくらいかが一目でわかる(図1)
    ② 重複する値(図2)
    ③ 一意の値 (図3)
      ※ 重複する値をクリックすると一意の値が表示される

画像に alt 属性が指定されていません。ファイル名: image-63.png

▢ 計算式の結果で書式を変更する

  1.計算式の結果で書式を変更
  <課題> 数値が30以上の時、その行に色を付ける場合
    ① 図1の通り、表を選択する。
      その時のポイントは、左上(田中)をActiveにする。
    ② 図2の通り計算式欄
      =$C2>30を入力
      ※ $C2と、Cを絶対、2を相対とする

2.その他

<課題>データーバーを応用し図3の様にグラフを表示。

  ① C列にB列のデータを参照
  ② C列にデータバーを表示
  ③ C列のデータを消すために
   「棒のみ表示」にチェック 図4
  ④ グラフの最大を100にしたい場合は、図4を設定

チェックボックスで 条件付き書式 をオン/オフする

▢ チェックボックスのオン/オフでグラフをオン/オフする(図1,2)


 1.チェックボックスの設定

   ① 開発タブ-挿入でフォームコントロールのチェックボックスを挿入。(図1、図2)
   ② 「コントロールの書式設定」を開き「リンクするセル」に セル番地を入れる。(図3)
   ③ チェックボックスのオン/オフで、セルにFalse/Trueが表示される
   ④ False/Trueの表示を消したい場合は、「セルの書式設定」で
      「表示形式」-「ユーザー設定」を選択し、「種類」欄に“;;;”を入力すると表示が消える。

 2.条件付き書式設定の設定

   ① E列の合計欄に「データバーを設定する」
     E2~E11を選択し「条件付き書式」-「データーバー」を選択
   ② 「新しい書式のルール」の「次の数式を満たす場合に
     「値を書式設定」欄に「=$F$1=false」を入力 (図4)
   ③ 「条件付き書式ルールの管理」で、「条件を満たす場合は停止」のチェックを入れる(図5)

土日と祝日の色を変える技(関数+条件付き書式)

ファイルをダウンロード

▢ 年月日データの土日だけで無く、祝日の色も変える。
  ① 土・日=TEXT関数と条件付き書式で対応
  ② 祝日=別sheetに祝日データベースと、TEXT関数、MATCH関数、ISERROR関数、
    NOT関数を利用

▢ C,D列に土日の判定を入れる

  ① 書式に”aaa”を設定
  ② C列に土曜日判定の計算式を入れる。
    =TEXT(+A2,”aaa”)=”土”
  ③ その計算式を、条件付き書式にコピーする
  ④ 条件付き書式の範囲を設定する
  ⑤ 土曜日と同じ要領(②~④)で日曜日を設定する

▢  E列に祝日判定の式を入れる

  ① A列の日付が祝日シートのB列(祝日)にあるか
    判定するMATCH関数を利用
    =MATCH(A2,祝日,0)
  ② MATCH関数はデータが無い場合エラーとなるのを利用
    ISERROR関数を利用 =ISERROR(MATCH(A2,祝日,0))
    上記の式はエラーの場合TRUEとなる為に反転する必要がある。
  ③ NOT関数で、 =NOT(ISERROR(MATCH(A2,祝日,0
  ④ 式③を条件付き書式にコピー
  ⑤ 条件付き書式の範囲を設定する