Excel のデーター入力規則が別列でソート

質問

Excelで　A列に日付け　B列に仕入れ先　C列にその仕入れ先の主な品名などをデーター入力規則で覚えさせていますが、

仕入れ日の都合及び仕入れ先伝票到着日の都合で最後の行に

追加記入、及び仕入れ先をサーチしてまとめて数件記入してから、A列の日付順にソートすると表面の書かれた文字はそのまま各行で並び替えされますが、

データー入力規則は記入した時の行に残ったままの様で次に仕入れ先のデーター入力規則をしようとすると違う表示になっています。

この場合日付をソートした場合その行の（隠れている入力規則）データーも一緒に移動できない物でしょうか？　よろしくお願いいたします。

回答No.1

kagakusuki · ベストアンサー

データの入力規則自体を、データとともにソートする方法は存じませんが、全ての行に同じ入力規則を設定しておいた上で、各行ごとに異なった入力規則を設定した場合と同様に、入力可能なデータに制限を加える事が出来るという方法は如何でしょうか？

　まず、表の直ぐ右側に隣接する列（ここでは仮にD列とします）を作業列として使用します。
　そして、「B列のセルに入力規則を設定する予定ではなかった行」のD列のセルには、次の関数を入力（入力規則ではなく、セルに関数として入力）して下さい。

=TRUE()

　又、例えば、「『-3～12の範囲の整数値のみを入力可能とする入力規則』を設定する予定だった行」のD列のセルには、次の関数を入力して下さい。

=ISNUMBER(1/ISNUMBER(INDEX($B:$B,ROW()))/(INT(INDEX($B:$B,ROW()))=INDEX($B:$B,ROW()))/(INDEX($B:$B,ROW())>=-3)/(INDEX($B:$B,ROW())<=12))

　因みに、INDEX($B:$B,ROW())は、その関数が入力されているセルがある行と、同じ行のB列のセルを表す関数です。
　又、例えば、「『-12.5～3.14の範囲の小数値のみを入力可能とする入力規則』を設定する予定だった行」のD列のセルには、次の関数を入力して下さい。

=ISNUMBER(1/ISNUMBER(INDEX($B:$B,ROW()))/(INDEX($B:$B,ROW())>=-12.5)/(INDEX($B:$B,ROW())<=3.14))

　又、例えば、「『2012/12/25～2013/1/15の範囲の日付のみを入力可能とする入力規則』を設定かする予定だった行」のD列のセルには、次の関数を入力して下さい。

=ISNUMBER(1/DAY(INDEX($B:$B,ROW()))/(INDEX($B:$B,ROW())>="2012/12/25"+0)/(INDEX($B:$B,ROW())<="2013/1/15"+0))

　又、例えば、「『文字数が1～12文字の文字列のみを入力可能とする入力規則』を設定する予定だった行」のD列のセルには、次の関数を入力して下さい。

=ISNUMBER(1/ISTEXT(INDEX($B:$B,ROW()))/(LEN(INDEX($B:$B,ROW()))>=1)/(LEN(INDEX($B:$B,ROW()))<=12))

　又、例えば、「『"あ","い","う","え","お"の中の何れかの値のみを入力可能とする入力規則』を設定する予定だった行」のD列のセルには、次の関数を入力して下さい。（ドロップダウンリストは表示されません）

=ISNUMBER(MATCH(INDEX($B:$B,ROW()),{"あ","い","う","え","お"},0))

　又、例えば、「『[ユーザー設定]の数式で入力可能な値を決める入力規則』を設定する予定だった行」のD列のセルには、次の様な形式の関数を入力して下さい。

=ISNUMBER(1/(「元の入力規則のユーザー設定の数式欄に入力する予定だった判定式」))

　次に、表中のB列の全てのセル（ソートの対象外となる行は除く）を選択してから、「データの入力規則」ダイアログボックスの[設定]タブにおいて「入力値の種類」欄を[ユーザー設定]とした上で、「数式」欄に次の数式を入力して下さい。

=INDEX($B:$B,ROW())

　尚、もしも、「数式」欄にINDEX関数を入力する事が出来なかった場合には、例えば、入力規則を設定するために範囲選択しているセル範囲のなかで、最も上の行が2行目である場合には、

=INDEX($B:$B,ROW())

の代わりに、

=$D2

と入力して下さい。

　これで入力可能な値を制限する様にした上で、特定の列基準で行の並べ替えを行った際にも、入力可能な値の制限条件が、データとともに移動する様にする事が出来ます。
　只、このままでは表に隣接して作業列が表示されてしまい、見苦しくなりますので、D列のセルの書式設定の表示形式を

;;;

に設定する事で、D列のセルを非表示にされると良いと思います。（下の添付画像では、解り易くするために、敢えてD列を非表示にはしておりません）
　それと、この方法は、特定の列基準で行の並べ替えを行った際に、D列の関数も一緒に並べ替えられることに意味がありますので、例え見栄えを良くするためであっても、作業列を別のシート上に設ける訳にはいきません。

　尚、上記の方法ではドロップダウンリストを設定する事は出来ませんが、もしも、全ての行に亘ってドロップダウンリストが必ず設定される場合には、各行毎に異なるドロップダウンリストが設定される様にした上であっても、データとともにドロップダウンリストに表示される内容も移動する様にする事が出来ます。

　今仮に、B2セルのドロップダウンリストに表示させたい値の一覧が、Sheet2のA1～A5の範囲に入力されていて、
同様に、B3セルのドロップダウンリストに表示させたい値の一覧がSheet2のB1～B6に、
B4セルのドロップダウンリストに表示させたい値の一覧がSheet2のC1～C7に、
それぞれ入力されているものとします。
　まず、例えば「B列のセルに『Sheet2のA1～A5の範囲に入力されている一覧の中から、選択出来る様にするドロップダウンリスト』を設定する予定だった行」のD列のセルには、次の文字列を入力して下さい。

Sheet2!A1:A5

　又、例えば「B列のセルに『Sheet2のB1～B6の範囲に入力されている一覧の中から、選択出来る様にするドロップダウンリスト』を設定する予定だった行」のD列のセルには、次の文字列を入力して下さい。

Sheet2!B1:B6

　又、例えば「B列のセルに『Sheet2のC1～C7の範囲に入力されている一覧の中から、選択出来る様にするドロップダウンリスト』を設定する予定だった行」のD列のセルには、次の文字列を入力して下さい。

Sheet2!C1:C7

　そして、「B列のセルにドロップダウンリストを設定しない予定だった行」のD列のセルには、次の関数を入力して下さい。

=CELL("address",INDEX($B:$B,ROW()))

　次に、表中のB列の全てのセル（ソートの対象外となる行は除く）を選択してから、「データの入力規則」ダイアログボックスの[設定]タブにおいて「入力値の種類」欄を[リスト]とした上で、「元の値」欄に、次の様な数式を入力して下さい。

=INDIRECT(INDEX($D:$D,ROW()))

　これでB列の各行毎に異なるドロップダウンリストを設定した上で、特定の列基準で行の並べ替えを行った際にも、ドロップダウンリストに表示される選択肢の内容が、データとともに移動する様にする事が出来ます。
　尚、ここで挙げさせて頂いたドロップダウンリストの設定方法では、「ドロップダウンリストを設定しない予定だったB列のセル」にも、ドロップダウンリストの逆三角マークボタン▼が現れて、そのボタンをクリックしますと、既にそのセルに入力済みの値が1つのみ表示されたドロップダウンリストが表示されますが、そのドロップダウンリストに表示されている以外の値であっても、入力する事は可能ですので、構わずに上書きして下さい。

　それから、ここで挙げさせて頂いた２つの方法（ソート可能なドロップダウンリストの設定方法と、ドロップダウンリスト以外のソート可能な入力規則の設定方法）は、１つの列の中では、混在させて使用することは出来ません。
　例えば、「B列には[ユーザー設定]の入力規則と、「整数のみの」入力規則を混在させて設定しておき、C列にはドロップダウンリストの入力規則のみを設定しておく」、という事は出来ますが、
「B列に[ユーザー設定]の入力規則と、ドロップダウンリストの入力規則を混在させて設定する」という事は出来ませんので、注意して下さい。

お礼

早速ご丁寧な説明ありがとうございました。
一寸（大分）難しそうですが挑戦してみます