[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『自動ナンバーの許可範囲』（祈）
お尋ねいたします。
Sheet1にSheet2のデータを参照して、自動ナンバーを取り出す設定をしています。
実際の設定関数などは以下のとおりです。
この場でご指導いただいたものです。
Sheet1
D4にデータの入力規則のリストに=$H$4を設定
H4=LOOKUP(1,0/('Sheet2'!$B:$B=VALUE(TEXT($H$2,"0000"))),'Sheet2'!$A:$A+1),RIGHT(VALUE(TEXT($H$2,"0000")),3)*100+1)
実際のナンバーは四桁表示で、「2001」となり、20は西暦の下二桁、01はナンバーです。
Sheet2
A列に上記のナンバー
B列に西暦
今回、教えていただきたいことは、Sheet1のD4セルにデータの入力規則を設定しているのですが、現在はエラーメッセージの表示のチェックを外しています。
このチェックを入れて、Sheet1のA列にあるナンバーの入力は許可するようにしたいのです。
例えば、2001〜2020まであるとしたら、新規の2021を含めてそれ以下の入力は許可するようにしたいのです。逆に言うと、2022以上は許可しないようにしたいのです。
どうぞよろしくお願いいたします。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
でだめですかね。
（閲覧者） 2020/05/10(日) 19:46
RIGHT(VALUE(TEXT($H$2,"0000")),3)*100+1) という式の内容については見ていません。前提に不明な点があるため。
（γ） 2020/05/10(日) 23:23
（γ） 2020/05/11(月) 08:46
リストとして表示すべき項目の数 1項目です。新規で作成しようとするナンバーです。
ここで言いますと、2021を表示できたらと思います。(現状、表示できています。)
その式の意図を明示してもらえますか？特に、2項目ですね。 これも説明が不足していて、すみません。
Sheet1は、いわゆる名簿を登録するためのシートです。
その登録名簿の?ｂﾉなります。
例えば、2020までのナンバー(2001〜2020まで)をSheet1のD4に入力しようとすると、エラーになってしまうので、
データの入力規則のエラーメッセージのチェックを外して使用しています。
γさんがおっしゃるように、このチェックを入れるとエラーが表示されるわけなので、うまくいっていない、というのが状況なのかもしれません。
また、新規のナンバーは、2021ですよとH4の式が示してはくれますが、2022を手入力で入れることも可能なのです。(エラーメッセージのチェックを外しているので)
そうなると、番号が連番にならなくなるおそれもあるため、今回、ご質問させていただいた次第です。
エラーメッセージのチェックを付けて、かつ、2001〜2020の入力を許可できるようにしたいというのが私の希望です。
これは、新規で作成していけば、2001〜の範囲は広がっていくことになります。
何か簡単なサンプルがあったほうが 取り急ぎ、上記のとおり回答をいたします。
γさん、お忙しい中に誠にありがとうございます。
どうぞよろしくお願いいたします。
（祈） 2020/05/11(月) 09:15
（γ） 2020/05/11(月) 09:43
# あと気になるのは、Sheet2のA列の更新されるタイミングくらいですか。
（γ） 2020/05/11(月) 12:23
Sheet2のA列の更新のタイミングは、新規が追加されたときなので、不規則というか、、、
言葉足らず、説明足らずで、本当に申し訳ありません。
どうぞよろしくお願いいたします。
（祈） 2020/05/11(月) 12:51
＞実際のナンバーは四桁表示で、「2001」となり、20は西暦の下二桁、01はナンバーです
＞ H2にはTODAY関数を入れてまして、そこで年を判断するようなつくりにしています。
TEXT($H$2,"0000") → TEXT($H$2,"yymm")
に変更？ （GobGob） 2020/05/11(月) 14:21
いや。。。yymmではないかｗ。
H2が =TODAY() ならナンバーなんて設定できないのでは？ （GobGob） 2020/05/11(月) 14:23
ぜんぜん勘違いかも。。。
H2に =TODAY() なら（シリアル値）
=TEXT(H2,"0000") ってなにを求めたいの？
（GobGob） 2020/05/11(月) 14:30
γさんの言う通り、何が何だか分からなくなったけど。。。
＞D4にデータの入力規則のリストに=$H$4を設定
H4の結果 ＋ Sheet2のA列 をリスト化する 数式を どっかのセル範囲にリストを造ってそれをリストで参照すればいいんでないの？
って思う今日この頃。 （GobGob） 2020/05/11(月) 14:41
=OR(AND(A1>=2001,A1<=2021),ISNA(MATCH(A1,B1:B10,0))=FALSE)
？？？ B1:B10 2001〜2021以外の入力可能なリスト 因みに、自動ナンバーの意味が全く解ってないです。 （BJ） 2020/05/11(月) 15:40
（閲覧者） 2020/05/10(日) 19:46ではどうしてダメなんですかね。
（理解できない人） 2020/05/11(月) 19:49
データ
↓
データの入力規則
↓
設定
↓
入力値の種類 → 整数
↓
データ → 次の値の間
↓
最小値 → 2001 =MIN(Sheet2!$A:$A)
↓
最大値 → 2021 =MAX(Sheet2!$A:$A)
↓
OK
これで、最大値1に+1して、リストで表示させる？
ただ、整数なので、リストにはできないですよね…
（祈） 2020/05/11(月) 21:15
私もここまでとします。
（閲覧者） 2020/05/11(月) 21:57
# 夕刻に書いたメモ。
最初に言い残したことを書いておきます。
それは「入力規則」の制約的なことです。
「入力規則」利用の前提に立つと、
(1)リストを使って、選択肢を表示すること
と
(2)満たすべき条件を指定して、それを満たさない入力に対して
警告を発すること
の二つを同時に行うことはできません。
これは、入力規則の選択肢として、 ・「リスト」指定と、 ・「数式を使ったユーザー設定」は のどちらかしか選べないことからも明らかです。
VBAを使ったからといって、この制約が緩くなることはありません。 まず、このことを念頭に置いて欲しいと思います。 なにか、二つのことを同時に満たそうとしているとしたら、 そのゴールには今後とも到達できません。
# 以上メモ。 ところで、入力規則を設定すべきセルは一つだけなんですか？ 基本的なことの確認ができていなかったですが。
（γ） 2020/05/11(月) 21:58
#メモ続き 「よろしくお願いします」という他人依存ではなく、 ご自分でしっかり考えて欲しいと思います。
実は、まだ仕様（やりたいこと）が明確でないように思います。 ・H2には何が入っていて、 ・Sheet2のB列、A列には何が入っていて、 最終的にH4セルには、どんな入力をさせたいのか。
また、入力は一カ所だけでなく、多数あるのではないか。 下2桁の番号がカウントアップしていくだろうから、 一時点で計算した、その時点での新規発行番号も、 件数が増えるに伴って変わっていくはず。
するとあるときは入力規則のエラーにならなくても、 後日、入力規則を触ると、候補は変わってきてしまう。 これは問題になることはないか。等々
これらを具体的な実例をもとに、きちんと示すという作業をすれば、 おのずと考え方も明確になるのではないかと思います。 このことは回答者ではなく、質問者がすべきことです。 # メモ終わり （γ） 2020/05/11(月) 22:05
入力規則を設定すべきセルは一つだけなんですか？ はい。H4セルのみ一つだけです。
・H2には何が入っていて これも説明不足でした。すみません。以下の数式を入れています。
$H$5=TODAY()関数を入れています。
この式で、西暦を数字(例:2020)にしています。
=IF($H$5<>"",VALUE(TEXT(DATE(YEAR($H$5),MONTH($H$5),DAY($H$5)),"yyyy")),"")
下二桁あれば、多くても年間50件に満たないものなので、十分に対応できます。
・Sheet2のB列、A列には何が入っていて A列=??(例:2001)
B列=西暦を数字にしたH2
最終的にH4セルには、どんな入力をさせたいのか。 皆さんからいろいろと考える機会をいただき、最初にH4に入れていた数式を再度考え直してみました。
それで、自分なりに以下の式に修正してみて、試してみたところ、見事に自分の思うとおりの動作が可能になりました！！！！
VLOOKUP関数にしたことで、データの入力規則のエラーメッセージのチェックを付けてもSheet2のA列にある?ｂ?入れてたら許可してくれるようになりました！！！！
=IFERROR(IF(COUNT('Sheet2'!$A:$A)=0,RIGHT($H$2,2)*100+1,IF(AND(COUNT('Sheet2'!$A:$A)>=1,$D$4=""),MAX('Sheet2'!$A:$A)+1,VLOOKUP($D$4,'Sheet2'!$A:$A,1,FALSE))),"")
皆さんのおかげです。
特にγさんからいただいた、「他人依存」「質問者がすべきこと」を頭において、ますは自分自身でしっかり考えていきたいと思います。
また、質問させてください。
皆さん、ありがとうございました。
（祈） 2020/05/11(月) 23:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.