| エクセル技道場 HOME |
| エクセルの技 目次 |
| シート |
| 関数 |
| 名前定義 |
| ツールバー |
| セル選択 |
| ファイル |
| 条件付き書式 |
| 書式設定 |
| 編集 |
| 印刷 |
| 入力規則 |
| マクロ |
| グラフ |
| ピボットテーブル |
| エクセル・ショートカット |
| エクセル関数語源リスト |
| エクセル関数引数リスト |
名前定義と[入力規則]-[リスト]シリーズ（1-5）に関連したサンプルファイル(3KB)
メニュー[データ]-[入力規則]-[リスト]で、「リストからセルに入力できるデータを制限する」ことができます。
A列に以下のD2:D4の部署名のリストを使って入力したいとします。
| D | |
|---|---|
| 1 | 部署 |
| 2 | 企画部 |
| 3 | 人事部 |
| 4 | 総務部 |
手順は以下の通り。
1.列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスに、=$D$2:$D$4 と入力
5.[空白を無視する] チェック ボックスをオフに
6.[ドロップダウン リストから選択する] チェックボックスをオンに
7.Enter
これで、A列（B列）のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名（氏名）をリストから選択することができるようになります。
【解説】
ファイルをダウンロードされた方はシート「同一シート」をご覧ください。
制限が設定されたセルをクリックしたときに入力項目のリストからデータを選択できるようにするには、[ドロップダウンリストから選択する]
チェックボックスをオンにします。
制限が設定されたセルまたは有効なデータのリストが空白のときに無効なデータとして処理するには、[空白を無視する]チェックボックスをオフにします。
別のシート（例えばシート「リスト」）にあるリストを参照しようと、例えば、上記4の手順のところで、=リスト!$D$2:$D$4 を入力して最後にEnterを押すと「抽出条件
データの入力規則 で、他のワークシートまたはブックへの参照は使用しません。」というメッセージが出てしまい、「=シート名!セル参照」では他の
シートのリストを[元の値] ボックスに入力することができません。
しかし、リストが入っているシートと入力用のシートは別にした方がなにかと便利です。
名前定義することで別のシートのリストを使えるようになります。
シート「基本」のA列を以下のシート「リスト」のA2:A4の部署名のリストを使って入力したいとします。
A
1 部署
2 企画部
3 人事部
4 総務部
まずは、名前定義です。
1.シート「リスト」のA2:A4を選択
2.名前ボックス（数式バーの左横）をクリック
3.「部署」と入力
4.Enterキーを押す
次に[データ]-[入力規則]-[リスト]の設定です。
1.シート「基本」の列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「部署」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter
これで、シート「基本」のA列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名をシート「リスト」のリストから選択することができるようになります。
シート「可変範囲」のA列を、以下のシート「リスト」のA列の部署名のリストを使って入力したいとします。
A
1 部署
2 企画部
3 人事部
4 総務部
以下のようにA2:A4に名前をつけていると、A5に部署が追加された場合、自動的に範囲が拡張されません。
1.シート「リスト」のA2:A4を選択
2.名前ボックス（数式バーの左横）をクリック
3.「部署」と入力
4.Enterキーを押す
部署を追加／削除に自動的に対応するには、以下のようにします。
まずは、名前定義です。
1.シート「リスト」を選択（選択しているセルはどこでもかまいません）
2.Ctrl＋F3
3.「名前」に「部署2」と入力
4.「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」
5.Enterキーを押す
次に[入力規則]−[リスト]の設定です。
1.シート「可変範囲」の列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「部署」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter
これで、シート「可変範囲」のA列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名をシート「リスト」のリストから選択することができるようになります。
シート「リスト」に部署名を追加／削除した場合、自動的に追加／削除されるようになっています。
【解説】
今回の手順の前回との違いは名前定義のところの「参照範囲」だけです。
名前の定義の手順の1でシート「リスト」を選択としています。1を他のシートを選択した状態で行う場合には、4で
=OFFSET(リスト!$A$2,0,0,COUNTA(リスト!$A:$A)-1,1)
と入力する必要があります。
シート「リスト」を選択して「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」とした場合でも、あとで、Ctrl＋F3で「部署2」の参照範囲を見ると
=OFFSET(リスト!$A$2,0,0,COUNTA(リスト!$A:$A)-1,1)
のように自動的にシート名がついています。
「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」を解説します。
まずは、=COUNTA($A:$A)-1です。
COUNT関数はその範囲内で数値が入力されているセルの個数を返します。
一方、COUNTA関数は、数値だけでなく、論理値、文字列、またはエラー値が入力されているセルの個数を返します。
この式の場合、引数を「$A:$A」とA列全体を指定しています。A列には、部署名が入っているセルがいくつあるかをカウントするわけです。「-1」としているのは、「部署」が入っているセルA1もカウントされているのでそれを引くためです。
部署がセルA4の「総務部」までしか入力されていない場合には、「3」を返します。
OFFSET関数は、OFFSET(基準, 行数, 列数,
高さ, 幅) という引数をとります。
基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照を返します。
「基準」：基準となるセル範囲の参照を指定します。
この式の場合、「部署」はA2から始まってますので、$A$2としています。
「行数」： 基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。行数に5を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから
5 行下方向へシフトします。行数に正の数を指定すると下方向へシフトし、負の数を指定すると上方向へシフトします。
すなわち、基準のセルから行を上下にいくつずらすか、ということですが、この式の場合、基準をずらす必要がないので、「0」としています。
「列数」：基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。列数に5
を指定すると、オフセット参照の左上隅のセルは、基準
の左上隅のセルから 5 列右方向へシフトします。列数
に正の数を指定すると右方向へシフトし、負の数を指定すると左方向へシフトします。
すなわち、基準のセルから列を左右にいくつずらすか、ということですが、この式の場合、基準をずらす必要がないので、「0」としています。
「行数」、「列数」をずらす必要がない場合には、「O」のかわりに何も入力しないでもできます。例えば、この式だと以下の式になります。
「=OFFSET($A$2,,,COUNTA($A:$A)-1,1)」
「高さ」：オフセット参照の行数を指定します。高さは正の数である必要があります。
この式の場合、=COUNTA($A:$A)-1の返り値「3」です。
「幅」：オフセット参照の列数を指定します。幅は正の数である必要があります。
この式の場合、「幅」をかえる必要がないので、「1」としています。
「高さ」または「幅」を省略すると、基準
のセル範囲と同じ行数または列数であると見なされます。
さて、もう一回整理しましょう。
A2が基準になってます。
行は移動しません。
列は移動しません。
高さは3にします。
幅は1にします。
これで、A2:A4を参照していることになります。
例えばシート「リスト」のどこかのセルに
=OFFSET($A$2,,,COUNTA($A:$A)-1,1)
を入力してみてください。
「#VALUE!」となります。
そのセルでF2（ファンクションキーのF2）を押してから、F9（ファンクションキーのF9）を押してみてください。
数式バーなどに ={"企画部";"人事部";"総務部"}と表示されます。これがA2:A4を参照しているということなのです。
F2-F9の後でEnterを押すと、数式が={"企画部";"人事部";"総務部"}に変わります。セルには、一番始めの「企画部」が表示されます。
F2-F9の後でCancelキーを押すともともとの数式のままです。
シート「一つの名前定義」のA列は以下のシート「リスト」のA列の部署名のリストを、B列は以下のシート「リスト」のB列の氏名のリストを使って入力したいとします。
A
1 部署
2 企画部
3 人事部
4 総務部
B
1 氏名
2 田中
3 佐藤
4 鈴木
5 山田
6 山本
7 小林
8 小川
9 中村
10 斉藤
まずは、名前定義です。
1.シート「リスト」のA列のセルどこかを選択
2.Ctrl＋F3
3.「名前」に「LIST」と入力
4.「参照範囲」に「=OFFSET(A$2,0,0,COUNTA(A:A)-1,1)」と入力
5.Enterキーを押す
次に[入力規則]−[リスト]の設定です。
1.シート「一つの名前定義」の列番号Aをクリック
2.ドラッグして列A:Bを選択
→これでA1がアクティブで列A:Bを選択した状態になります。
3.メニュー[データ]-[入力規則]
4.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
5.[元の値] ボックスでF3（ファンクションキーのF3）を押す
6.「LIST」を選択してEnterを押す
7.[空白を無視する] チェック ボックスをオフに
8.[ドロップダウン リストから選択する] チェックボックスをオンに
9.Enter
これで、シート「一つの名前定義」のA列（B列）のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名（氏名）をシート「リスト」のリストから選択することができるようになります。
【解説】
名前定義の手順において、1.で「A列を選択」しているのは、4の4.「参照範囲」の「=OFFSET(A$2,0,0,COUNTA(A:A)-1,1)」の「A:A」と対応させるためです。
「LIST」という名前を定義してから、B列のセルどこかを選択してから、Ctrl+F3で名前「LIST」をクリックしてみてください。「参照範囲」は「=OFFSET(リスト!B$2,0,0,COUNTA(リスト!B:B)-1,1)」のように「B:B」になっています。
[入力規則]−[リスト]の設定のところで、1,2の手順で「A1がアクティブで列A:Bを選択」としているのも同様の理由です。
今回は、「一行目に部署名が入っていて、その下に所属の部員名といったリストをもとに、入力規則を使ってA列に部署名をリストから入力、B列にはA列に入力された部署に所属する部員をリストから入力。しかも部署名も氏名も可変範囲にする。」です。
シート「リスト2」に以下のリストが入っているとします。
A B C
1 企画部 人事部 総務部
2 田中 山本 中村
3 佐藤 小林 斉藤
4 鈴木 小川
5 山田
シート「縦横可変範囲」のA列は、シート「リスト2」の「部署名」からリストで入力できるようにする。
シート「縦横可変範囲」のB列は、シート「リスト2」の「氏名」からリストで入力できるようにする。
「部署名」「氏名」ともに追加／削除に自動的に対応できるようにする。
まずは、部署名の名前定義です。
1.シート「リスト2」を選択（選択しているセルはどこでもかまいません）
2.Ctrl＋F3
3.「名前」に「部署3」と入力
4.「参照範囲」に「=OFFSET($A$1,0,0,1,COUNTA($1:$1))」
5.Enterキーを押す
次に氏名の名前定義です。
1.シート「縦横可変範囲」のセルB2を選択
2.Ctrl＋F3
3.「名前」に「氏名」と入力
4.「参照範囲」に「=OFFSET(リスト2!$A$2,0,MATCH(!$A2,部署3,0)-1,COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1,1)」と入力
5.Enterキーを押す
続いて[入力規則]−[リスト]の設定です。部署名の方から。
1.シート「縦横可変範囲」の列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「部署3」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter
最後に氏名の[入力規則]−[リスト]の設定です。
1.シート「縦横可変範囲」の列番号BをクリックしてB列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「氏名」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter
これで、全ての設定が完了しました。
シート「縦横可変範囲」のA列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）とすると、シート「リスト2」の一行目の部署名のリストが出てきます。
シート「リスト2」の一行目に部署名を追加／削除すると、それがリストに自動的に反映されるようになっています。
シート「縦横可変範囲」のB列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）とすると、A列に入力されている部署名に所属する部員だけのリストが表示されます。
シート「リスト2」に氏名を追加／削除すると、それがリストに自動的に反映されるようになっています。
例えばシート「縦横可変範囲」のA2に「総務部」と入力されていた場合には、B2でAlt+↓で、「中村、斉藤」の二名の名前がリストに出てきます。
【解説】
「氏名」の名前定義の4の「参照範囲」に入力する式の中にシート名がその前についていない"!"があります。
これは、ペーストするときに消し忘れたわけではありません。わざと残してます。"!"を頭につけておくと、他のシートでも同じように使えます。
"!"なしだと名前定義したシートを参照しにいきます。すなわち、この式だと常に「縦横可変範囲」のA列を参照しにいくようになっちゃいます。
これは、No.018に出てきた
=MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,31)
と同じです。
氏名の名前定義の手順の4.「参照範囲」に入力する以下の式について解説します。
=OFFSET(リスト2!$A$2,0,MATCH(!$A2,部署3,0)-1,COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1,1)
まずは、OFFSETを各引数に分解してみましょう。
基準セル：リスト2!$A$2
移動する行数：0、すなわち行は2行目のまま。
移動する列数：MATCH(!$A2,部署3,0)-1
高さ（行数）の変更：COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1
幅（列数）の変更：1、すなわち列数は1列のまま
移動する列数：MATCH(!$A2,部署3,0)-1から説明します。シート「縦横可変範囲」のセルA2に「総務部」と入力されているとします。
MATCH(検査値, 検査範囲, 照合の型)という引数をとります。検査値が検査範囲の中で何番目にあるか、を返します。
検査値は、この式の場合、セルA2の「総務部」です。
検査範囲は、「部署3」という名前が参照している範囲、すなわち {"企画部","人事部","総務部"}です。
照合の型に0を指定すると、検査値に一致する値のみが検索の対象となります。
「総務部」は{"企画部","人事部","総務部"}の3番目にあるので、返り値は3となります。
OFFSETの移動する行数、列数は基準セルのままだと0なので、-1として調整して、2（=3-1）列移動する、としているわけです。
次に、高さ（行数）の変更：COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1 について、説明します。
まず、OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1)を各引数に分解してみましょう。
基準セル範囲：リスト2!$A:$A
移動する行数：0
移動する列数：MATCH(!$A2,部署3,0)-1 →上で説明した通り、この式では2です。
高さ（行数）の変更：省略しています。省略した場合には、基準セル範囲と同じ行数のままです。すなわち行全体です。
幅（列数）の変更：省略しています。省略した場合には、基準セル範囲と同じ列数のままです。すなわち1列です。
つまり、基準セル範囲：リスト2!$A:$Aから、2列右に移動した列全部という意味で、リスト2!$C:$Cを参照しています。リスト2!$C:$Cは、「総務部」の部員のリストが入っている列です。
COUNTA(リスト2!$C:$C)で、リスト2!$C:$C中に文字列などが入ったセルが何個あるかをカウントします。この例の場合だと、C1に総務部、C2に中村、C3に斉藤が入っているので、3を返します。「総務部」など部署名はカウント対象外なので、-1しているわけです。結果、返り値は2となります。
さて、それでは、最初の式、
=OFFSET(リスト2!$A$2,0,MATCH(!$A2,部署3,0)-1,COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1,1)
をそれぞれ返り値にしてみると、以下のようになります。
=OFFSET(リスト2!$A$2,0,2,2,1)
つまり、基準セルリスト2!$A$2から、行は移動せず2行目のまま、列は右に2移動してC列。新たな基準C2を起点に行数を2行に、列数は1列に、ということになり、C2:C3、すなわち{"中村","斉藤"}を参照している、ということになるのです。
【問題】
氏名をそれぞれ、
A列に漢字で入力
B列に半角英数字で入力
というふうに入力しています。
いちいち漢字キーを押して入力モードを切り替えていますが、これを自動的に切り替えるにはどうしたら良いでしょうか？
【回答】
1.列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[日本語入力]タブで[ひらがな]を選択
4.Enter
これでA列を選択した時に自動的に入力モードがひらがなになります。
1.列番号BをクリックしてB列を選択
2.メニュー[データ]-[入力規則]
3.[日本語入力]タブで[無効]を選択
4.Enter
これでB列を選択した時に自動的にIMEがオフになります。
【解説】
入力規則はExcel97以降の機能です。
日本語入力の種類は以下の9種類を指定することができます。
コントロールなし
オン
オフ（英語モード）
無効
ひらがな
全角カタカナ
半角カタカナ
全角英数字
半角英数字
【問題】
A2:A5に文字列を入力するのですが、入力できる文字数を3文字までにするにはどうしたら良いでしょうか？
【回答】
1.A2:A5を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を[文字列(長さ指定)]に
4.[データ]を[次の値以下]に
5.[最大値]に
3
と入力
6.Enter
【解説】
入力規則はExcel97以降の機能です。
上記手順4.の[データ]では以下の8種類から選択することができます。
次の値の間
次の値の間以外
次の値に等しい
次の値に等しくない
次の値より大きい
次の値より小さい
次の値以上
次の値以下
【問題】
セルA2に文字列を入力します。
全角スペースを含んだ文字列を入力しないようにするにはどうしたら良いでしょうか？
エクセル技道場 →OK
エクセル
技道場 →半角スペースなのでOK
エクセル 技道場 →全角スペースを含むので入力不可
エクセル技道場
→半角スペースなのでOK
エクセル 技道場 →全角スペースを含むので入力不可
【回答】
1.セルA2を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブで[入力値の種類]のところで[ユーザー設定]を選択
4.数式に =ISERROR(FIND(" ",A2)) と入力 " "内は全角スペース
5.Enter
【解説】
入力規則はExcel97以降の機能です。
FIND関数は、FIND(検索文字列,対象,開始位置)
という引数をとります。
FIND関数は、指定された文字列(検索文字列)を他の文字列(対象)の中で検索し、その文字列が他の文字列内で最初に現れる位置を左端から数え、その番号を返します。検索文字列が対象の中で見つからない場合、エラー値
#VALUE! が返されます。
ISERROR関数は、ISERROR(テストの対象)
という引数をとります。
ISERROR関数は、テストの対象 が任意のエラー値(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?
または #NULL! のいずれか)を参照するとき TRUE を返します。テストの対象がエラー値を参照しないときは
FALSE を返します。
入力規則のユーザー設定では、数式の計算結果がFALSEの場合、入力不可となります。
【問題】
A列に数値を入力する際に、00で終わらないと入力不可にするにはどうしたら良いでしょうか？
12300 →OK
12340 →入力不可
12345 →入力不可
【回答】
1.列番号Aをクリックして列Aを選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスで[ユーザー設定]をクリック
4.[数式]ボックスに「=MOD(A1,100)=0」と入力
5.Enter
【解説】
アクティブなセルと[数式ボックス]に入力するセルを一致させる必要があります。
例えば、上記1のところを
1.B10をクリック
2.B1までドラッグしてB1:B10を選択
のようにした場合には、B10がアクティブでB1:B10を選択した状態になっています。
その場合には、4.の数式は「=MOD(B10,100)=0」とする必要があります。
【問題】
A列に入力規則で条件を以下のようにしました。
入力値の種類：文字列(長さ指定)
データ
：次の値以下
最大値
：5
これで、全角／半角文字にかかわらず、5文字以内までしか入力できないようになります。
これを半角文字で10文字以内までは入力可能、というふうに入力規則を設定するにはどうしたら良いでしょうか？
つまり、以下のような感じです。
全て全角文字であれば5文字以内
全て半角文字であれば10文字以内
全角文字1文字+半角文字8文字以内
全角文字2文字+半角文字6文字以内
全角文字3文字+半角文字4文字以内
全角文字4文字+半角文字2文字以内
【回答】
1.列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を「ユーザー設定]に
4.[数式]を
=AND(ISTEXT(A1),LENB(A1)<=10)
に
5.Enter
【解説】
LEN関数は、文字列の文字数を返します。
一方、LENB関数は、文字列のバイト数を返します。
セルに あいうえお と全角文字で入力されていた場合、LEN関数は 5 を返します。LENB関数は10を返します。
ISTEXT関数は、文字列を参照するとき TRUE を返します。
列全体ではなく、セル範囲に上記のような入力規則を設定する場合にはアクティブセルと[数式]の参照セルを一致させることに注意が必要です。
例えば、A2:A10に入力規則を設定するには、以下のようになります。
1.セルA2をクリック
2.ドラッグしてA2:A10までを選択
→セルA2がアクティブでA2:A10が選択された状態に
3.メニュー[データ]-[入力規則]
4.[設定]タブの[入力値の種類]を「ユーザー設定]に
5.[数式]を
=AND(ISTEXT(A2),LENB(A2)<=10) に
6.Enter
【問題】
A列に、全角のみで5文字以内という制限をかけたいのですが、どうすれば良いでしょうか？
【回答】
1.A列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を[ユーザー設定]に
4.[数式]に
=AND(A1=JIS(A1),LEN(A1)<=5)
と入力
5.Enter
【解説】
JIS関数は、文字列内の半角（1バイト）の英数カナ文字を全角（2バイト）の文字に変換します。文字列に半角の英数カナ文字が含まれない場合は、文字列は変更されません。
つまり、セル内の文字列が全て全角文字であれば、 =A1=JIS(A1) の結果はTRUEということになります。
【問題】
A列に既に全く同じ内容のセルがある場合入力出来ないようにするにはどうしたら良いでしょうか？
なおデータは下の行にどんどん追加していきます。既に入力したセルに再度データを入力することはありません。
【回答】
1.列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を[ユーザー設定]に
4.[数式]に
=MATCH(A1,A:A,0)=ROW()
と入力
5.Enter
【解説】
列番号AをクリックしてA列を選択、でアクティブセルはセルA1になります。
アクティブセルと手順4.の数式で参照するセルがマッチしている必要があります。
【問題】
A列に既に全く同じ内容のセルがある場合入力出来ないようにするにはどうしたら良いでしょうか？
既に入力したセルに再度データを入力することもあります。
【回答】
1.列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を[ユーザー設定]に
4.[数式]に
=COUNTIF(A:A,A1)=1
と入力
5.Enter
【解説】
前回の問題では、「既に入力したセルに再度データを入力することはありません。」という前提でしたので、[数式]は
=MATCH(A1,A:A,0)=ROW() でした。
【問題】
A列のセルについて、○の入力を不可にするにはどうしたら良いでしょうか？
ただし、"○"だけでなく、"○なんとか"や"なんとか○"といった文字列の入力などはできることとします。
【回答】
1.列番号AをクリックしてA列を選択
→セルA1がアクティブでA列を選択している状態
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を[ユーザー設定]に
4.[数式]に
=A1<>"○"
と入力
5.Enter
【解説】
入力規則のユーザー設定によって、数式がTRUEを返すデータの入力のみを可とします。
比較演算子
<> は、右辺と左辺が等しくないとTRUEを返します。
つまり、A列内のセルに入力しているデータが ○ でなければTRUEなので入力可、 ○
であれば、FALSEが返って入力不可となるわけです。
入力規則はExcel97以降の機能です。
【問題】
A列のセルについて、○を含む文字列の入力を不可にするにはどうすれば良いでしょうか？
"○"の入力を不可にするだけでなく、"○なんとか"や"なんとか○"、"○○なんとか○かんとか"といったように、とにかく"○"を含む文字列の入力を不可にしたいのです。
また、○ が一番最初に来る文字列の入力を不可にするにはどうすれば良いでしょうか？
それから、○ が一番最後に来る文字列の入力を不可にするにはどうすれば良いでしょうか？
【回答】
1.列番号AをクリックしてA列を選択
→セルA1がアクティブでA列を選択している状態
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を[ユーザー設定]に
4.[数式]に
=COUNTIF(A1,"*○*")=0 と入力
5.Enter
【解説】
*
(アスタリスク)は、ワイルドカードの一つで”任意の数の文字”を表します。
○
が一番最初に来る文字列の入力を不可にするには、数式を
=COUNTIF(A1,"○*")=0
とします。
○
が一番最後に来る文字列の入力を不可にするには、数式を
=COUNTIF(A1,"*○")=0
とします。
入力規則はExcel97以降の機能です。
【問題】
A列のセルについて、セル内に○を三つ以上含む文字列を入力しようとするとエラーメッセージが出るようにしたいのですが、どのように入力規制を設定すれば良いのでしょうか？
○ →OK
○なんとか →OK
なんとか○かんとか○ →OK
○なんとか○かんとか○ →○が三つ以上含まれるので入力不可
なん○とか○かん○とか○ →○が三つ以上含まれるので入力不可
【回答】
1.列番号AをクリックしてA列を選択
→セルA1がアクティブでA列を選択している状態
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]を[ユーザー設定]に
4.[数式]に
=COUNTIF(A1,"*○*○*○*")=0 と入力
5.Enter
【解説】
*
(アスタリスク)は、ワイルドカードの一つで”任意の数の文字”を表します。
入力規則はExcel97以降の機能です。
【問題】
A列に入力規則を設定しました。
しかし、コピー＆ペーストすると条件に合わないデータが入力できてしまいます。
入力規則では、コピー＆ペーストによる条件外の入力を防ぐことはできないのでしょうか？
【回答】
はい。防げません。
エラーメッセージは、セルにデータが直接入力された場合だけに表示され、数式の計算結果、マクロによって入力されたデータ、貼り付けられたデータ、および
[フィル] コマンドで入力されたデータなどの場合には表示されません。
しかし、事後にチェックする方法があります。
1.A列を選択
2.メニュー[データ]-[入力規則]
→「選択範囲には、データの入力規則が設定されていないセルが含まれています。
データの入力規則をそれらのセルに適用しますか？」というメッセージが表示される。
3.[はい]をクリック
→[データの入力規則]ダイアログボックスが表示される。
4.[OK]ボタンをクリック
5.メニュー[ツール]-[ワークシート分析]-[[ワークシート分析] ツールバーの表示]
6.[無効データのマーク]をクリック
【解説】
これで、入力規則で設定した制限に合致しないセルに赤丸がつきます。
なお、コピー＆値貼り付けをしている場合には、上記手順の1.-4.は必要ありません。
