Excel可变范围图表制作：INDIRECT函数不可用的解决方案示例

Hey there! I’ve dealt with this exact Excel chart headache before—turns out INDIRECT just doesn’t play nice with chart data sources, but using dynamic named ranges is the perfect fix. Let me walk you through a step-by-step solution that’ll make your chart update automatically whenever C1 or C2 changes.

We’ll use INDEX (a non-volatile, reliable function) to define named ranges that pull data from the rows specified in C1 (start) and C2 (end). Here’s how to set it up:

Step 1: Create Dynamic Named Ranges #

1. Open the Name Manager by going to the Formulas tab → clicking Name Manager, or pressing Ctrl+F3.
2. Click New to make a range for Column A’s data:
   • Name: DynamicSeriesA (pick any descriptive name you like)
   • Refers to: Paste this formula:

     =INDEX($A:$A, $C$1):INDEX($A:$A, $C$2)
     

     Breakdown: INDEX($A:$A, $C$1) grabs the starting cell (e.g., A3 if C1=3), INDEX($A:$A, $C$2) grabs the ending cell (e.g., A10 if C2=10), and the colon combines them into a full range like A3:A10.
3. Repeat for Column B’s data:
   • Name: DynamicSeriesB
   • Refers to:

     =INDEX($B:$B, $C$1):INDEX($B:$B, $C$2)
     

4. Click OK to save both ranges.

Alternative (using OFFSET): If you don’t mind volatile functions (they recalculate more frequently), you can use OFFSET instead:

=OFFSET($A$1, $C$1-1, 0, $C$2-$C$1+1, 1)

But INDEX is better for performance, especially with large datasets.

Step 2: Build Your Chart with Dynamic Series #

1. Insert your preferred chart type (Line, Column, etc.) from the Insert tab—it’ll start with placeholder data, which we’ll replace.
2. Right-click the chart → select Select Data.
3. In the Select Data Source window:
   • Click Add to add Series A:
     • Series name: Type a name like "Series A" or reference a header cell (e.g., $A$1 if that’s your column header)
     • Series values: Enter =YourWorkbookName.xlsx!DynamicSeriesA (replace YourWorkbookName with your actual file name; if the workbook is saved, you can shorten it to =DynamicSeriesA)
   • Click Add again to add Series B:
     • Series name: "Series B" or reference $B$1
     • Series values: =YourWorkbookName.xlsx!DynamicSeriesB
4. Click OK—your chart will now display the exact range from C1 to C2 in columns A and B.

Step 3: Test the Dynamic Update #

Change the values in C1 or C2 (just make sure C2 is ≥ C1), and your chart will automatically refresh to show the new data range. No manual data source edits needed—sweet!

Bonus: Dynamic Category Labels (If You Need Them) #

If you want category labels (e.g., from another column like Column D), create a third named range:

• Name: DynamicCategories
• Refers to: =INDEX($D:$D, $C$1):INDEX($D:$D, $C$2) (replace $D:$D with your category column)
  Then go back to Select Data Source → click Edit under Horizontal (Category) Axis Labels and enter =DynamicCategories.

内容的提问来源于stack exchange，提问作者Studix