formulas excel charts how to auto dynamic select l

问题:

how do i change this rows formulas

=OFFSET(sheet1($B$3,COUNT(sheet1!$B:$B)0,5,1) 

to become column show as image

回答1:

Like this?

=OFFSET(Sheet1!$B$3,,COUNT(Sheet1!$B$3:$XFD$3)-5,1,5)

It assumes no gaps in data.

And according to this...you get around less than 5 at end with

=OFFSET(B3,0,COUNT(3:3)-MIN(5,COUNT(3:3)),1,MIN(5,COUNT(3:3)))

回答2:

Tweaking the approach found here.

I created a named range for your labels: =OFFSET(Sheet1!$B$3,,COUNTA(Sheet1!$B$3:$XFD$3)-COUNTA(Sheet1!$B$3:$F$3),,COUNTA(Sheet1!$B$3:$F$3))

And a similar one for your values: =OFFSET(Sheet1!$B$4,,COUNTA(Sheet1!$B$4:$XFD$4)-COUNTA(Sheet1!$B$4:$F$4),,COUNTA(Sheet1!$B$4:$F$4)).

And then edited my chart source data to refer to those named ranges.

回答3:

The column number containing the last number in row 3 can be obtained with,

=match(1e99, 3:3)

You can put together two index functions to represent a range like this,

=index(3:3, , match(1e99, 3:3)-4):index(3:3, , match(1e99, 3:3))  '<~~K3:O3
=index(2:2, , match(1e99, 3:3)-4):index(3:3, , match(1e99, 3:3))  '<~~K2:O3

This can be proofed with a simple sum operation.

=sum(index(3:3, , match(1e99, 3:3)-4):index(3:3, , match(1e99, 3:3)))

... which correctly returns 12 from your sample data.

You might want to use one of those formulas as the RefersTo: of a named range and use the named range in your chart definition.