Microsoft Excelで、動的に定義された名前を作成して新しい情報を含むように命名範囲を拡張する方法を説明します。新しいワークシートにデータを入力し、[数式]タブで[名前の管理]をクリックして新しい名前を作成します。例えば、「Date」には=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)、また「Sales」には=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)を使用します。B2セルをクリアし、=RAND()*0+10を入力すると、データが更新されます。

Excelで動的な定義名を作成する方法

Microsoft Excelでは、新しい情報を含めるために名前付き範囲を拡張する必要がある場合があります。この記事では、動的な定義名を作成する方法について説明します。この手順は、使用しているExcelのバージョンに応じて適宜実行してください。

ステップ1: データの入力

新しいワークシートを開き、以下のデータを入力します。

ステップ2: 定義名の設定

Excelのメニューを操作する

1. [数式] タブをクリックします。

2. [定義名] グループで、[名前の管理] をクリックします。

3. [新規作成] をクリックします。

4. [名前] ボックスに「Date」と入力します。

5. [参照先] ボックスに以下の式を入力し、[OK] をクリックします:

   =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

6. 再び [新規作成] をクリックします。

7. [名前] ボックスに「Sales」と入力します。

8. [参照先] ボックスに以下の式を入力し、[OK] をクリックします:

   =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

9. [閉じる] をクリックします。

ステップ3: 売上データの更新

セルB2をクリアし、以下の数式を入力します。

=RAND()*0+10

この数式では、COUNT関数が数値の列に、COUNTA関数がテキスト値の列に使用されます。この数式は、ボラティルなRAND関数を使用しています。新しいデータをB列に入力すると、定義名「Sales」で使用されるOFFSET数式が自動的に更新されます。10という値は、B2セルの元の値です。

ステップ4: 別の方法での設定

新しいワークシートに同様のデータを手入力し、次の手順に従ってください。

1. [挿入] メニューから [名前] にカーソルを合わせ、[定義] をクリックします。

2. [ブック内の名前] ボックスに「Date」と入力します。

3. [参照先] ボックスに以下の式を入力し、[OK] をクリックします:

   =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1).

4. [追加] をクリックします。

5. [ブック内の名前] ボックスに「Sales」と入力します。

6. [参照先] ボックスに以下の式を入力し、[追加] をクリックします:

   =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

7. [OK] をクリックします。

8. 再びセルB2をクリアし、以下の数式を入力します。

   =RAND()*0+10

このプロセスを通じて、Excelでは新しいデータに基づいて動的に拡張できる名前付き範囲を作成することができます。このテクニックは、大量のデータを扱う際に非常に便利です。

————-

Create a dynamic defined range in a worksheet – Microsoft 365 Apps

Source link

This article explains how to create dynamic named ranges in Microsoft Excel to automatically extend a defined name range when new data is added. Here’s a summary of the steps for both creating named ranges using the Formulas tab and the Insert menu:

1. Data Entry: Start by entering a table with two columns: "Month" and "Sales," with sample data for January to March and corresponding sales figures (10, 20, 30).

2. Using the Formulas Tab:

   • Navigate to the Formulas tab and click on Name Manager.
   • Click New to create a new named range.
     • Name it "Date" and set its reference with the formula:

       =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

     • Click OK.
   • Click New again to create another named range.
     • Name it "Sales" and use the formula:

       =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

     • Click OK and then Close.
   • Clear cell B2 and enter the formula:

     =RAND()*0+10

     This uses the volatile RAND() function to ensure that the "Sales" named range updates dynamically when new data is entered.

3. Using the Insert Menu:
   • Go to the Insert menu, point to Name, and click Define.
   • For the name "Date," set the reference to the same formula as above.
   • Click Add and then create the "Sales" named range using the corresponding formula and click OK.
   • Similar to above, clear cell B2 and type the same formula to dynamically update the named range.

Important Notes:

• The COUNTA function is used for counting text entries (months), while COUNT is used for numerical entries (sales).
• The dynamic ranges adjust automatically as new data is entered, facilitating effective data management in Excel.