ドロップダウンリスト（INDIRECT関数）でエラー

質問

「QNo.2687724」の質問補足です。
ドロップダウンリストが表示されなくなる原因に心当たりがありました。入力規則を設定するときに、「元の値はエラーと判断されます。続けますか？」とメッセージがでていたのです。
Sheet1を売上帳、Sheet2を得意先リストにし、売上帳の「A1」に得意先の頭の文字を入力、「B1」に「入力規則」の「リスト」で「元の値」に「=INDIRECT(A1)]と入力しました。
エラーと判断される「元の値」というのは、Sheet2 の得意先リストのことなのでしょうか？
Sheet2 の得意先リストは、縮小表示するとこのような形で名前の定義をしています。
　Ａ　Ｂ　Ｃ　Ｄ　Ｅ　Ｆ　Ｇ　Ｈ　Ｉ
　あ　か　さ　た　な　は　ま　や　ら　←50行
　い　き　し　ち　に　ひ　み　ゆ　り　←30行
　う　く　す　つ　ぬ　ふ　む　よ　る　←30行
　え　け　せ　て　ね　へ　め　　　れ　←30行
　お　こ　そ　と　の　ほ　も　わ　ろ　←30行
つまり、「あ」の得意先は、５０セル、「い」は３０セル入力できるようにしました。
この方法で、どこかエラーになる原因あるのでしょうか？
「QNo.2687724」の質問に補足を付けたかったのですが、できないようなので、新規質問にさせていただきました。又、Excel のバージョンは2002ではなく、2003でした。
せっかくここまできたのに、このままでは使えないと言われてしまい解決を急いでおります。アドバイスを宜しくお願いいたします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。Wendy02です。

#2の返事を待たずに、今、時間が取れたので、入力規則が現れなくなる原因の問題に対処した、マクロを作ってしまいました。

>QNo.2677035　/Excel2002 で売上帳を作成しました。
>得意先名が250件を超え、得意先名の番号を捜すのが大変だと言われてしまい

入力するシートのシートタブ（下方）のところを右クリックして、「コードの表示」で、以下を貼り付けてください。DATAAREA のところは、Sheet2のA1 が、データリストの左端上にあるなら、そのままで結構ですが、必ず、一度「得意先の入ったデータリスト」を開けてください。その得意先のデータリストは、別に、並べてなくても良いです。離れたところにない限りは、すべて拾ってくれます。

ただし、これは、今までのものとは必ずしも同じではありません。
また、便利かどうかは、今の方法が良いかは分かりません。

たとえば、"野村会社" を探す場合は、「村」でも「野」でも、その中にある漢字を一字、A列に入れれば、B列の入力規則側に現れます。しかし、逆に、"会社"という言葉があれば、会社と入っているものが全部リストされてしまいます。その点に違いがあります。

また、単語が見つからないと、そのまま、B列の隣のセルにその文字が写されます。

この入力規則は、A列に検索語を入れたセルに対してのみ現れるもので、隣のB列にはひとつしかありません。これは、（書式や入力規則表示に対する）メモリ負担にならないように考えたものです。

なお、これは試作段階のもので、実用になるかは、調整が必要かもしれません。当初、私が「QNo.2677035」でイメージしていたものとは違ってしまっていますが、前のスタイルか、まったく別のものに変えることも可能です。

それから、もしも、反応しなくなったら、Alt + F8 から、「データ更新」というタイトルを探して、クリックすれば、復活します。

'シートモジュール設定
'-----------------------------------------------------------------
'Option Explicit
Private myData() As Variant
Const DATAAREA As String = "Sheet2!A1" '間に空白のない範囲は、左端上のひとつのセル
'解説：Ctrl +Shift + End で括られるデータのある範囲
'Ver.2690474.01
Const INPUTCOL As Integer = 1　'検索語を入れる列
Private Sub MakingList()
Dim i As Long
Dim myRange As Range
Dim buf As Variant
Dim c As Variant
　 If InStr(DATAAREA, "!") > 0 Then
　　 buf = Split(DATAAREA, "!")
　　 Set myRange = Worksheets(buf(0)).Range(buf(1))
　 ElseIf InStr(DATAAREA, ".") > 0 Then
　　 buf = Split(DATAAREA, ".")
　　 Set myRange = Worksheets(buf(0)).Range(buf(1))
　 End If
　 If InStr(DATAAREA, ":") = 0 Then
　　 Set myRange = myRange.CurrentRegion
　 End If
Erase myData
For Each c In myRange
　ReDim Preserve myData(i)
　 myData(i) = c.Value
　 i = i + 1
Next c
End Sub

Private Sub Worksheet_Activate()
　Call データ更新
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim myList As String
　Dim c As Range
　If Target.Column <> INPUTCOL Then Exit Sub 'A列
　If Target.Count > 1 Then Exit Sub
　If Target.Value <> "" Then
　　EnterValidationList Target.Value, myList
　Else
　　Exit Sub
　End If
　Application.EnableEvents = False
　If myList = "" Then Target.Offset(, 1).Value = Target.Value: GoTo Quit
　On Error Resume Next
　For Each c In Cells.SpecialCells(xlCellTypeAllValidation)
　　 c.Value = c.Value
　　 c.Validation.Delete
　Next c
　On Error GoTo 0
　On Error GoTo Quit
　With Target.Offset(, 1).Validation
　　.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
　　xlBetween, Formula1:=myList
　　.IgnoreBlank = True
　　.InCellDropdown = True
　　.InputTitle = ""
　　.ErrorTitle = ""
　　.InputMessage = ""
　　.ErrorMessage = ""
　　.IMEMode = xlIMEModeNoControl
　　.ShowInput = True
　　.ShowError = True
　End With
　　Target.Offset(, 1).Select
Quit:
　Application.EnableEvents = True
End Sub

Sub EnterValidationList(Matchwd As String, myList As String)
'変更の余地があります。
Dim Dummy As Variant
Dim ar As Variant
On Error GoTo ErrHandler
　Dummy = UBound(myData)
　Dummy = Empty
　ar = Filter(myData, Matchwd)
　On Error Resume Next
　　Dummy = ar(0)
　　If Dummy <> Empty Then
　　　 myList = Join(ar, ",")
　　End If
　On Error GoTo 0

Exit Sub
ErrHandler:
　Call MakingList
　Resume
End Sub
Sub データ更新()
'動かなくなった時もこれを使う
　Application.EnableEvents = True
　MakingList
End Sub

補足

Wendy02 さん、マクロも作れるなんてすごいですねぇ。

折角マクロを作ってくださったですが、私には使い方がよくわからず、申し訳ありません。「コードの表示」に貼り付けるのは、「'シートモジュール設定」以下全てで宜しいのでしょうか？又、名前の定義や、入力規則の設定はクリアするのでしょうか？

得意先リストは、以前はＡ列は番号、Ｂ列は得意先名、Ｃ列はフリガナを入力してあったのですが、入力規則を使うために QNo.2690474 のように並べ変えました。新規の得意先を増やせるように空白をたくさん入れてあるので、うまく作動しないのかもしれません。

回答No.6

noname#153814

１）、２）、３）、４）すべてできると思いますよ。試していませんが。
名前定義は、シートが変わってもブックが変わらない限り働きます。
名前をつけた後で、シート名を変更しても対応してくれます。

エクセルの「挿入」－「名前」－「定義」に保存されています。ここで、削除したり、セル範囲を変更したりできます。一度いろんなことをお試しください。駄目なら削除すればいいのですから。

INDIRECT関数は文字を取得する関数ですね。
=INDIRECT(A1)　とは　=INDIRECT(あ)
=INDIRECT(A2)　とは　=INDIRECT(い) のことですね。
このことを理解できたら、縦だろうが横だろうが大丈夫だと思います。

お礼

sannri さん、度々アドバイスありがとうございます。
自分なりに直してみたのですが、これでいいのか心配だったので、sannri さんにコメントをいただいて安心しました。
どうもありがとうございました。<(_ _)>

回答No.5

noname#153814

名前Ｂｏｘの名前のつけ方に問題があると思います。
最初は「Ａ１～Ａ５」までを選択して、たとえば「五十音」と名前をつけます。
続いて、「Ｂ１～Ｈ１」までを選択して、「あ」と付けます。（ここが大事です）つまり「Ａ１～Ａ５」までに使われている「文字」を「名前」に使わなければならないのです。これで最初につけた名前と後につけた名前が関連されます。

「Ｂ２～Ｈ２」には、「い」と名前をつけなければならないのです。

ぼけていたらごめんなさい。

お礼

もう１つ、すみません。(^^;
4）以前は、Ａ列は番号、Ｂ列は得意先名、Ｃ列はフリガナの得意先シートを使用していたのですが、これを利用することはできませでしょうか？

補足

できました。＼(^o^)／＼(^o^)／＼(^o^)／

見ず知らずの他人のために、寝る時間も割いてここまで親身になってご指導くださるなんて、本当に感謝しております。<(_ _)>

＞最初は「Ａ１～Ａ５」までを選択して、たとえば「五十音」と名前をつけます。
＞4続いて、「Ｂ１～Ｈ１」までを選択して、「あ」と付けます。（ここが大事です）つまり「Ａ１～Ａ５」までに使われている「文字」を「名前」に使わなければならないのです。

取り合えず、得意先リストの一部を教えていただいたとおりに直してを作り直しました。[Sheet1]にコードを全て貼り付けたら、できました。＼(^o^)／

申し訳ありませんが、もう少し教えてください。<(_ _)>
1）質問では[Sheet2]と書いていたのですが、実際は[得意先]というシート名にしていました。他の人が見て[得意先]シートだとすぐわかるようにしたいのですが、シートの名前の変更は可能でしょうか？

2）[Sheet2]の得意先名を並べ変えたいのですが、行に「五十音」、列に「客先名」を入れるようにしたらダメでしょうか？
3）又、客先名の行と列は、どの程度まで増やせますか？

回答No.4

Wendy02

こんばんは。Wendy02です。

>マクロも作れるなんてすごいですねぇ
私は、ここの掲示板ではマクロが専門です。関数は、みなさん出来ますからね。

ともかく、私は、マクロでも、関数でも、その方法は問わずに、これが完結するまでは、乗りかかった船として、可能な限りは、なんとか、ご要望にあわせてみたいと思います。（出来るだけ早くですが……）
本来は、ダイアログボックスのようなものをつけるのがよいのかもしれませんが、それは、出来合いで提供するなら良いけれど、マクロをやったことがない人には、ちょっと面倒です。

当初、入力補完が出来れば、と考えています。つまり、何かを入れると、候補が出てくるようなスタイルに持っていきたかったのですが、どうしても思いつきません。それで、今のスタイルの方法も悪くはないと思い、アイデアだけ利用させていただくことにしました。

>「'シートモジュール設定」以下全てで宜しいのでしょうか？又、名前の定義や、入力規則の設定はクリアするのでしょうか？

「全て」です。他に入れる必要のないものを考えました。

「名前の定義」も「入力規則」もワークシートからは触る必要はありませんが、「入力規則の設定」は、自動的にたったひとつになってしまいます。

>Ａ列は番号
の代わりに、文字を入れることにするわけですね。

>新規の得意先を増やせるように空白をたくさん入れてあるので、
>A1:AE170

それは、ちょっと変えなくてはならないようですね。ちょっと私は、うかつでした。

それと、「A1:AE170」の範囲を設定しますと、入力規則のためのセルの数が、5,270個となります。それをINDIRECT関数で任せるには、負担が大きすぎるのではないか、と思います。現状のマクロでも、5,270個分をそのまま検索させたら、遅くなるかもしれません。しかし、それは直せます。

ただ、とりあえず、バックアップを取っていただいて、今のを試してみていただいてもよいかと思います。その後で、他に不具合や要望を取り入れて、調整します。

A列に、取引先の名前の一文字（漢字であれば漢字）を入れてていただければよいのです。今の段階では、Sheet2 のデータの並びはまったく関係ありません。ぽっかりと、丸々一列の間が空かない限りは、どのように並べていただいてもよいです。

お礼

予想以上の便利なものができて嬉しくって舞い上がってしまい、得意先以外の部分をちゃんと確認していませんでした。

実は、得意先シートとは別に、分類・品名・担当者などの「リスト」シートを作成し、入力規則を使っていたのですが、それが全てクリアされてしまいます。

マクロの中の「Validation」が入力規則のことだということだけはわかったので「c.Validation.Delete」を削除すればいいのかと試してみたのですが、そんな簡単なものではないのですね。どう変更したら他のリストがクリアされなくなるのかわからず昨日から悩んでおります。
申し訳ありませんが、アドバイスを宜しくお願いいたします。

補足

Wendy02 さん、こんばんは！
今朝、出勤前でバタバタしていて、コメントを付ける場所を間違えてしまったようです。申し訳ありません。

Wendy02 さんが作ってくださったマクロを解読しようとプリントしてにらめってしていたのですが殆ど理解できず、Wendy02 さんの説明をヒントに自分でわかる範囲で自分の売上帳に合うように直してみました。

＞Const DATAAREA As String = "Sheet2!A1" '間に空白のない範囲は、左端上のひとつのセル
　"Sheet2!A1"　⇒　"得意先!A1"に変更（得意先という名前にしてある）

＞Const INPUTCOL As Integer = 1　'検索語を入れる列
　 = 1　⇒　 = 3　（検索後の入力は、C列になる）

得意先シートは、得意先名の他に、ふりがなや分類などがあったので、ふりがなや分類を削除し、得意先名だけにしました。
また、A2:AS1 に「あ」「い」・・・「わ」と頭の文字を入れ、A列は、「NO」と名前を付け、番号を入れました。

お陰さまで、売上帳で自分がやりたかったことを叶えることができました。検索文字もひらがなだけでなく漢字でも入力できるため、ドロップダウンリストで表示される候補も絞られ、また、頭の文字でなく、名前に含まれる文字で検索できるようになった為、「（株）」がつく会社名でも、２番目の文字でも検索できるようになり、自分が希望していた以上のものができあがり、満足しております。

Wendy02 さんには、本当に感謝しております。どうもありがとうございました。<(_ _)>

回答No.2

Wendy02

こんにちは。Wendy02です。

ドロップダウンリストの範囲を教えてください。

その状況は、ある程度、おぼろげながら想像はついています。ドロップダウンリストは、ひとつだけではありませんね。実験的には、ひとつなのでしょうけれど、実務上は違うわけですね。そこには、少し無理があるのです。ドロップダウンとして設定されたセルの抱えるセルの範囲の数が、論理的な限界を越えているのではないか、というように考えています。

お急ぎでなければ、こちらで、前々のご質問の時から、さかのぼって、私のほうで立て直してみたいと思っています。今、私の覚えている限りですと、会社の登録数が増えたので、頭文字でピックアップで、その場所に入力できれば、と考えていたかと思います。それで、入力規則でのドロップダウンのリストには、少し無理があるのではないか、と思います。

補足

度々コメントありがとうございます。
得意先名のリストは、「A1:AE170」まであります。
下は、縮小表示をした時に表示される定義した名前の位置です。
　Ａ　Ｂ　Ｃ　Ｄ　Ｅ　Ｆ　Ｇ　Ｈ　Ｉ
　あ　か　さ　た　な　は　ま　や　ら　←50行
　い　き　し　ち　に　ひ　み　ゆ　り　←30行
　う　く　す　つ　ぬ　ふ　む　よ　る　←30行
　え　け　せ　て　ね　へ　め　　　れ　←30行
　お　こ　そ　と　の　ほ　も　わ　ろ　←30行
頭が「あ」のセルが50、「い」=30、「う」=30、「え」=30、「お」=30、合計170行あります。
これからも得意先名が増えるので余裕を持って空白を多めに入れました。
「得意先」のシートのほかに、「リスト」のシートに「担当者」「品名」「分類」・・・等のリストも作成しています。
「得意先」を他のブックにすれば多少負担がへるのではないかと思い、別のブックから参照するようにしてみましたが、やはりダメでした。
でも、ここまで来て諦めきれないので、もう少し時間をもらえるよう上司に頼んできたところです。

回答No.1

Wendy02

こんばんは。

たぶん、おやりになりたいことは、

Sheet2 のA1:A5 を選択。
挿入－名前－登録　で、
「項目」　(任意）

次に、
Sheet2 のA1:I5 まで選択しておいて、
挿入－名前－作成　で、

□左端行　にチェック

Sheet1 に戻って、

B1 にセルポインタを置き、

データ－入力規則－リストで、

=INDIRECT("Sheet2!"&A1)

A1 に、「あ」と入れれば、B1 のドロップダウン・リストには、「あかさたなはまやら」が出てきます。（ただ、本来は、縦に扱ったほうがよいです。横は、勝手が良くありません。その場合は、名前－登録で、「A1:I1」 に項目。名前－作成で「上端行」にチェックにします）

ただ、前々から、私もマクロで考えていましたが、結局、手間が多くなるので、そのままになってしまいました。

お礼

ご報告が遅くなってしまい申し訳ありません。
昨夜、教えていただいた方法で作り直してみました。最初は順調に入力でき喜んでいたのですが、やはり、暫く入力すると、ドロップダウンリスト「▼」が表示されなくなってしまいました。今朝、もう一度試してみたのですが、残念ながらやはり同じ状況です。

補足

もう、回答がつかないのではないかと諦めかけていたので、今朝、Wendy02 さんの回答を拝見してとても嬉しかったです。ありがとうございます。出勤前で時間がないので、作り直して帰宅後、ご報告させていただきます。