Excel で並べ替えを行うと、データ検証ルールがデータとともに移動されない

Question

Excel でテーブルまたは範囲を並べ替えると、セルの値と太字、斜体、色などの基本的な書式設定は移動しますが、次のものは移動しません。

• 国境
• データ検証ルール

私のスプレッドシートにはセルごとのデータ検証機能があるため、並べ替え操作を実行すると値は移動しますが、検証は保持されません。

並べ替える前の例を次に示します。

そして、並べ替えた後（「フルーツ」の名前のAZ）。

Excel が並べ替え操作を実行するときに、値と形式とともにデータ検証ルールを移動させる方法はありますか?

編集：

明確にするために言っておきますが、ここでは 2 つの分離可能な懸念事項があります。

1. 主な懸念事項: ユーザーが [並べ替え] ボタンをクリックするか、フィルター列見出しの [並べ替え] オプションをクリックすると、(複雑な!) データ検証ルールが無効になります。
2. 二次的な懸念：それはニースExcel に組み込まれている並べ替えボタンを使用してワークシートを並べ替えることができたら (これらはデータを並べ替える最も明白な方法です)。並べ替えを行うためだけの別のマクロは、少しハッキーに思えます。

編集：

最終的に私が採用した解決策は、ワークブックを保護し、すべてのシートの並べ替えを無効にすることで、並べ替えを完全に無効にすることでした。「並べ替え」ボタンを押すことはできますが、何も起こりません。

追加の安全策として、ワークブックを開くたびにワークブックの保護を再適用し、使用方法の説明と警告 (「これを並べ替えないでください」など) を含むワークシートを表示する VBA を追加しました。エンド ユーザーはいつでもワークブックの保護を解除できるため、これは絶対確実ではありませんが、私ができる最善の方法だと考えています。

Answer 1

pH 値の処理に関する一般的なルールを作成し、それを値の列全体に適用することができます。Key1 をチェックして値が pH であるかどうかを確認する条件を含めるだけで済みます。pH である場合は基準をチェックし、そうでない場合は TRUE を返すだけで、何でも許可されます。

たとえば、C2:C6テーブル内の (値 1 列) に適用されたカスタム データ検証ルールは次のとおりです。

=IF(B2="pH",AND(C2>=0,C2<=14,C2=INT(C2)),TRUE)

これにより、pH 値が 0<=pH<=14 に制限され、整数のみになります。ルールは列全体に適用されるため、並べ替えはデータ検証に影響しません。

条件付き書式を使用して適用する場合、境界線も同様に処理できます。適切な数式ルールを使用して、列内のすべてのデータに適用するだけです。

=B2="pH"

Answer 2

古いコードの一部を見ると、バリデーターを削除して再作成したようです。

var cellValues = activeSheet.Range[leftColumn + startingRow, Type.Missing];
cellValues.Validation.Delete();

Excel でプログラム的に何かを実行する場合は、マクロを記録 > 操作を手動で実行 > マクロの記録を停止 > Alt + F11 を押して、新しく作成されたモジュールのコードを表示します。これは、VBA プログラミングを習得する簡単な方法です。頑張ってください!

Answer 3

これはあなたの質問に対する完全な答えではありませんが、言及しておくべきだと感じています。

データをスプレッドシートに入力する方法は少し変わっていますが、特別な理由がない場合は、スプレッドシートのデザインを少し変更することで問題を解決できるかもしれません。あなたのようなデータを入力する最も簡単な方法は、キー (色、pH、直径など) ごとに 1 つの列、果物ごとに 1 つの行を用意することです。そうすれば、列全体のルールとスタイルを定義でき、並べ替えについて心配する必要がなくなります。

キーと値を使用してデータにアクセスする必要がある場合は、ピボット テーブルとピボット テーブル検索 (GETPIVOTDATA) または行/列検索 (VLOOKUP / HLOOKUP) を使用すると役立つ場合があります。

あなたの場合、キーはかなり明確に定義されているようです (そうでなければ、キーと値のアプローチを採用する理由になります)。また、Excel では何千もの列が許可されているため、キーが不足することもありません。キーごとに別の列を使用すると、一種の入力フォームとしても機能します。果物の値のセルが空になるため、果物の値を入力し忘れることはありません。

サンプルが複数ある場合（リンゴの直径がすべて同じではないなど）、スプレッドシートの各行を 1 つのサンプルとして扱うことができます。

このアプローチにより、図やピボット テーブルの作成など、データのさらなる処理も容易になります。