動的リストの最後の値を取得するには、Microsoft Excel2010 / 2013のOFFSET関数とCOUNTA関数とともに[データ検証]オプションを使用します。

COUNTA:値を含むセルの数のカウントを返します。

「COUNTA」関数の構文:= COUNTA(value1、value2、value3……。)

例:範囲A1:A5では、セルA2、A3、およびA5に値が含まれ、セルA1およびA4は空白です。セルA6を選択し、数式を記述します- _ = COUNTA(A1:A5)関数は3_を返します

img1

OFFSET:別の範囲またはセルから行と列の数をオフセットした範囲への参照を返します。

OFFSET関数の構文:= OFFSET(参照、行、列、高さ、幅)参照:-__これはオフセットするセルまたは範囲です。

移動する行と列: -開始点を移動する行の数。これらは両方とも正、負、またはゼロにすることができます。

高さと幅 * :-これは、返す範囲のサイズです。これはオプションのフィールドです。Excelのオフセット関数を理解するための例を見てみましょう。

A1:D10の範囲のデータがあります。列Aには製品コード、列Bには数量、列Cには製品ごとのコスト、列Dには総コストが含まれます。セルE2のセルC5の値を返す必要があります。

img2

以下の手順に従ってください。

  • セルE2を選択し、数式を記述します。

OFFSET(A1,4,2,1,1)*そしてキーボードのEnterキーを押します。

  • 関数はセルC5の値を返します.

img3

img4

この例では、セルC5からE2に値を取得する必要があります。参照セルはA1である範囲の最初のセルであり、C5はA1から4行下、2列右にあります。したがって、式は= OFFSET(A1,4,2,1,1)または= OFFSET(A1,4,2)です(1,1はオプションであるため)。

次に、動的リストの最後の値を取得する例を見てみましょう。

ある範囲の国名があります。これで、このリストに国を追加すると、ドロップダウンリストで自動的に利用できるようになります。

img5

動的リストを準備するには、列の最後の値を取得し、新しい数値が追加されると自動的に更新される数式を作成する必要があります。

以下の手順に従ってください。-*セルB2を選択します。

  • [データ]タブに移動し、[データツール]グループから[データ検証]を選択します。

img6

※「データ検証」ダイアログボックスが表示されます。 [設定]タブで、[許可]ドロップダウンリストから[カスタム]を選択します。

img7

※数式ボックスが有効になります。

  • このボックスに式を記入してください。

* _ = OFFSET(A:A、1,0、COUNTA(A:A)-1,1)._

  • [OK]をクリックします。

__

img8

  • この段階で、最後に更新されたセルはA11です。

img9

  • データ検証が正しく機能しているかどうかを確認するには、セルA12に都市名を追加します。

img10

A12にエントリを追加するとすぐに、ドロップダウンリストに追加されます。

これは、Microsoft Excel2010および2013で動的リストを作成して新しいエントリを自動的に入力する方法です。

__