☰

Excel 2010 で、常に変化する値の列のドロップダウン リストを作成するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Excel 2010 で、常に変化する値の列のドロップダウン リストを作成するにはどうすればよいですか?

年数を示す列とヘッダーがある Excel スプレッドシートがあります。

Years
1993
1993
1994
1994
1994
...
2011
2011

年ごとに重複した値があり、時間の経過とともに行が追加されます。

年をドロップダウン リストで表示する必要がある別のセルがありますが、一意の年のみを表示する必要があります。Excel 2011 のデータ検証機能を使用しようとしましたが、2 つの問題があります。

  1. 重複した年が表示されます。
  2. 列全体を使用するように指示すると、ドロップダウン リストに空のセルが含まれます。

行が追加されると自動的に更新され、一意の値のみが表示される年のドロップダウン リストを取得するにはどうすればよいですか?

編集: もう少し情報があります。ドロップダウン リストは、Access フォームのように、計算されたデータを表示するために別のシートで使用されます。ユーザーは年の範囲を選択でき、それに応じてデータが更新されます。元のシートは、すべてのデータのリストにすぎません。

答え1

この種の検証には、VBA と 1 つの汚いトリックを使用します。

まず、Alt + F11 で VBA エディターに入ります。次に、それぞれのワークシートに「動的リスト検証コード」(tm) :) を入力します。

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

このコードは、データ -> 検証 -> エラー メッセージ -> タイトルに入力された数式によって生成されたリストでセル検証リストを更新します。これにより、リスト検証のある各セルに独自の数式を設定できます。

次に、モジュールを追加し (挿入 > モジュール)、新しいモジュールに次のコードを配置します。

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

この関数は、空白や重複のない範囲内のすべてのセルを返します。次に、リスト検証のある各セルで、データ検証のエラー メッセージのタイトルに GenDynList(range) を追加します。

答え2

面倒です。自動的に更新する組み込みの方法はありません。データセット内の年に限定するのではなく、関心がある可能性のあるすべての年を含む別のリストを作成する方が簡単です。

答え3

別のシートのデータからピボット テーブルを追加します。行として年を使用します。テーブルの残りの部分は関係ありません。必要に応じて行を並べ替えてフィルター処理します (たとえば、明示的に "[空白]" をフ​​ィルター処理します)。行ラベルがあるセルをデータ検証の対象にします。

拡張名前付き範囲を使用して、常にすべての新しいラベルを使用します。 http://www.ozgrid.com/Excel/DynamicRanges.htm

国、州などの他の列についても繰り返します。すべてのピボットを同じデータ範囲に基づいている場合は、いずれか 1 つを更新すると、すべてが一緒に更新されます。プロセスは、新しいデータを追加し、ピボットを更新し、更新されたデータ検証を使用するというようになります。

さらに考慮すべき点: ソース データにテーブルを使用すると、常にテーブル全体が使用されるため、ピボット テーブルの更新が容易になります。または、行を追加するときに問題を回避するために、拡張可能な名前付き範囲をデータ ソースとして使用します。

名前付き範囲を定義すると、別のワークシートの範囲をデータ検証のソースとして使用できることに注意してください。別のシートを明示的に参照する通常の範囲は使用できません。

関連情報