Microsoft Excelで、新しい情報を含めるために名前付き範囲を拡張する方法があります。この記事では、動的に定義された名前を作成する方法を説明します。データが200行以下であることを前提にしています。OFFSET関数を使い、名前マネージャーで「Date」と「Sales」の名前を定義します。さらに、セルB2にRAND関数を用いた式を入力すると、データが自動的に更新されます。この設定により、列Bに新しいデータを入力するたびに、Salesの範囲が動的に拡張されます。

Microsoft Excelでの動的に定義された名前の作成

概要

Microsoft Excelを使用する際、追加情報を含む必要がある名前付き範囲が存在する場合があります。本記事では、動的に定義された名前を作成する方法について説明します。

定義された名前とOFFSET関数の使用方法

以下の手順に従って、使用しているExcelのバージョンに応じて、OFFSET関数を使った動的な名前を作成してください。

Microsoft Office Excel 2007、2010、および2013の場合

1. 新しいワークシートに以下のデータを入力します。

   番号	A	B
   1	月	売上
   2	1月	10
   3	2月	20
   4	3月	30

2. 数式 タブをクリックします。

3. 定義名 グループ内の 名前の管理 をクリックします。

4. 新規作成 をクリックします。

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

6. 参照先 ボックスに以下のテキストを入力し、OK をクリックします。
   =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

7. 新規作成 をクリックします。

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

9. 参照先 ボックスに以下のテキストを入力し、OK をクリックします。
   =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

10. 閉じる をクリックします。

11. セルB2をクリアし、次の数式を入力します。
    =RAND()*0+10

この数式は、揮発性のあるRAND関数を使用しています。この数式は、B列に新しいデータを入力すると、「Sales」という名前に使用されているOFFSET数式を自動的に更新します。数式における値「10」は、B2セルの元の値として使用されています。

Microsoft Office Excel 2003の場合

1. 新しいワークシートに以下のデータを入力します。

   番号	A	B
   1	月	売上
   2	1月	10
   3	2月	20
   4	3月	30

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

3. 作業ブックの名前 ボックスに「Date」と入力します。

4. 参照先 ボックスに以下のテキストを入力し、OK をクリックします。
   =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

5. 追加 をクリックします。

6. 作業ブックの名前 ボックスに「Sales」と入力します。

7. 参照先 ボックスに以下のテキストを入力し、追加 をクリックします。
   =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

8. OK をクリックします。

9. セルB2をクリアし、次の数式を入力します。
   =RAND()*0+10

この数式もまた、揮発性のあるRAND関数を使用しています。B列に新しいデータを入力すると、「Sales」という名前に使用されているOFFSET数式が自動的に更新されます。数式における値「10」は、B2セルの元の値として使用されています。

以上の手順に従うことで、Microsoft Excelにおける動的に定義された名前の作成が可能になります。この方法を利用すれば、新しいデータを簡単に管理し、常に最新の情報を参照することができます。

————-

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

Source link

The article discusses how to create a dynamic named range in Microsoft Excel to automatically include new data. It focuses on using the OFFSET function along with COUNTA and COUNT to adjust ranges dynamically, suitable for versions from Excel 2003 to 2013.

Key Steps:

1. Setting Up Data: Enter sample data for “Month” and “Sales.”
2. Creating Named Ranges:
   • Navigate to the Formulas tab and open Name Manager.
   • Create a name (e.g., “Date”) with a formula that specifies a range using OFFSET and COUNTA.
   • Create another name (e.g., “Sales”) using OFFSET and COUNT.
3. Using Dynamic Updates: To trigger updates in the named ranges, clear an initial value in the “Sales” column and replace it with =RAND()*0+10. This volatile function ensures that the named ranges are updated when new data is added.

Notes:

• COUNT is used for numerical data and COUNTA for text data.
• The method assumes a maximum of 200 rows but can be adjusted as needed.