[[20150827180159]]　『入力規則が無効になる』（通行人）　ページの最後に飛ぶ

『入力規則が無効になる』（通行人）

エクセル2013を使用しています。

[データの入力規則]を作成後、ファイルを保存(xlsx形式)し、ファイルを閉じ、
再度ファイルを開くと、入力規則が反映されない状態に陥ります。

但し[データの入力規則]を開き、[設定]タブの
[同じ入力規則が設定されたすべてのセルに変更を適用する(P)｣に
チェックを入れると、何故か反映されます。

入力規則は常時有効にしたいのですが、
これを回避する方法を教えていただけないでしょうか。

[シートの保護]を使用することもありますが関係あるのでしょうか。

よろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

補足です。

無効になる入力規則は、
ユーザー定義「=AND(LEN(SUBSTITUTE(A1,$B$1:$B$199,""))=LEN(A1))」です。
セルB1:B199に置いている文字をセルA1に入力できなくしています。
マクロは使用していません。

 こちらでやっても再現しました。
 あらためて、入力規則を設定すると（すべてのセルに変更を適用する(P)にチェックをいれなくても）
 再び機能するんですけどね。

 関数は得意じゃないんですが,
 この式って【配列数式】でしょうか？　入力規則の数式に配列数式って使えましたっけ？

（β） 2015/08/27(木) 19:38

 XL2010でもXL2013でも再現しました。バグに類する気もするんですが・・・

 ＞この式って【配列数式】でしょうか？　入力規則の数式に配列数式って使えましたっけ？

 名前定義とか条件付き書式では、自然に配列数式として解釈されるんですが、
 入力規則のユーザー設定は、設定した時だけ配列数式として解釈され、
 再オープンしたらそんな取扱いはしないよー、なんて感じの不思議な現象ですね。

 試しに、普通Enterで済む SUMPRODUCT関数を使ってユーザー設定したら
 　　　　　　　　　　　　　↓
 =SUMPRODUCT(N(LEN(SUBSTITUTE(A1,$B$1:$B$199,""))=LEN(A1)))=199

 再オープンしても、生き続けていました。

 どなたか、再検証いただければと思います。

（半平太） 2015/08/27(木) 22:00

 半平太さんの通常数式で、閉じて、再度開いても機能することを確認しました。

 入力規則　配列数式　で検索すると、いろいろでてきますね。その中に以下がありました。

https://sites.google.com/site/toriaezunomemo/home/excelmemo/information_about_array_formulas/input_rule_limitation

 リストを指定して、そこで配列数式を入力すると、その場ではじかれるようですので、もしかしたら
 入力規則では配列数式は使えないというのが【正式仕様】で、ただ、入力時のチェックが片手落ちということなのかも。

 どうしても、配列数式を設定しなければいけないという場合は、（今のところ）設定はでき、ブックが閉じられるまでは有効のようですので
 WorkBook_Open あたりで

 Private Sub Workbook_Open()
    With Sheets("Sheet1").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=AND(LEN(SUBSTITUTE(A1,$B$1:$B$199,""""))=LEN(A1))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
 End Sub

 こんなようにブックを開くたびに自動設定するということでしょうかね。

（β） 2015/08/28(金) 06:01

 βさん

 再検証していただき、ありがとうございます。

 ＞入力規則　配列数式　で検索すると、いろいろでてきますね。その中に以下がありました。
 ＞https://sites.google.com/site/toriaezunomemo/home/excelmemo/information_about_array_formulas/input_rule_limitation

 そのページは「配列定数」を使った配列数式の話です。

 例えば、条件付き書式でも、値が１，２，３のどれかなら色を付ける場合
 これは、使えないと云うことであり、
 　↓
 =OR(A1={1,2,3})

 これは、配列数式ですけど使えます
 　↓
 =OR(A1=ROW($A$1:$A$3))

 今回の入力規則でも、「配列定数」は使っていないので、
 一回目の時に成功することは、そんなにおかしいとは感じないのですけど・・・

（半平太） 2015/08/28(金) 07:09

 >>そのページは「配列定数」を使った配列数式の話です。

 あ！そうでしたか。なるほどです。
 ご指摘ありがとうございます。

（β） 2015/08/28(金) 07:34

 INDEXを使って
 =AND(INDEX(LEN(SUBSTITUTE(A1,$B$1:$B$199,""))=LEN(A1),0))
 でも再読み込み後も有効になるようだ。
（ねむねむ） 2015/08/28(金) 09:13

質問者の通行人です。
皆さん、いろいろと有難うございます。
大変参考になります。
エクセルは奥が深いですね...
分からないことばかりです。
（通行人） 2015/08/28(金) 09:43

コメント返信：

[ 一覧(最新更新順) ]

[[20150827180159]] 『入力規則が無効になる』（通行人） ページの最後に飛ぶ

[[20150827180159]]　『入力規則が無効になる』（通行人）　ページの最後に飛ぶ