|
offset函数 功能： 以指定的（单元格或相连单元格区域的引用）为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格也可以是一个区域（可以指定行列数，即数组）。 语法： =offset( reference, rows, cols, [height], [width]) =offset(参照单元格， 行偏移量， 列偏移量， 返回几行， 返回几列) 参数解释： 参数Reference —— 参照单元格（单元格或相连单元格区域的引用）。否则，offset函数返回错误值#VALUE! 参数Rows——行偏移量。正数（代表在参照单元格的下方），负数（代表在参照单元格的上方）。 参数Cols——列偏移量。正数（代表在参照单元格的右边）或负数（代表在参照单元格的左边）。 参数Height——返回几行（必须为正数），即所要返回的引用区域的行数。 参数Width——返回几列(必须为正数），即所要返回的引用区域的列数。
最后2个参数不指定时，默认返回1行1列。 实例： 输入公式：=offset(A1,1,1,1,1) -- 返回A1的右下角单元格 输入公式：=sum(offset(A1,1,1,2,2)) -- 返回A1的右下角2*2单元格，并进行求和 可通过F9按钮查看数据。
另外，也可以通过选定2*2单元格，再进行输入公司，按住三键CTRL SHIFT ENTER进行数组计算。 三键（CTRL SHIFT ENTER） -- 数组计算
行列转换的三种方式1.右键转置复制源数据--右键--选择性粘贴--勾“转置”，确定。 2.转置公式TRANSPOSE$表示绝对引用，可按F4直接切换。
3.引用函数OFFSET ROWS/COLUMN（支持随时更新数据）方法一、方法二只能一次性转置，后期无法随时更新数据。 列转行 → 输入公式 =OFFSET($A$1,COLUMN(A1)-1,0,1,1)向右拉 行转列 → 输入公式=OFFSET($A$1,0,ROW(A1)-1,1,1)向下拉
一行（列）转多行多列结合OFFSET函数与ROW/COLUMN函数进行嵌套使用，即可实现。 1.一列转多列多行（或多行多列） ①先列后行，比如一列数据转为四列多行。 在目标区域第一个单元格输入以下公式，然后向右拉到第4列，再下拉直到出现空白。 =OFFSET($A$1,COLUMN(A1)-1 (ROW(A1)-1)*4,)&""
②先行后列，比如一列数据转为四行多列。 在单元格输入以下公式，然后向下拉到第4行，再右拉直到出现空白。 =OFFSET($A$1,ROW(A1)-1 COLUMN(A1)*4-4,)&""
2.一行转多列多行（或多行多列） ①先列后行，比如一行数据转为三列多行。 同样，此例在单元格输入以下公式，然后向右拉到第3列，再下拉直到出现空白。 =OFFSET($A$1,,(COLUMN(A1)-1) (ROW(A1)-1)*3,)&""
②先行后列，比如一列数据转为三行多列。 在单元格输入以下公式，然后向下拉到第3行，再右拉直到出现空白。 =OFFSET($A$1,,(ROW(A1)-1) (COLUMN(A1)-1)*3,)&""
3.多行多列转一列 ①先向下引用源数据，再向右引用。 单元格输入以下公式，下拉直到出现空白。 =OFFSET($A$1,MOD(ROW(A6),6),ROW(A6)/6-1,)&""
②先向右引用源数据，再向下引用。 单元格输入以下公式，下拉直到出现空白。 =OFFSET($A$1,ROW(A5)/5-1,MOD(ROW(A5),5))&""
4.多行多列转一行 多行多列数据转成单行的情况实际应用中应该不算常见，不过这里也一并整理了，若需要可直接套用公式。 ①先向右引用源数据，再向下引用。 单元格输入以下公式，右拉直到出现空白。 =OFFSET($A$1,INT(COLUMN(F1)/6)-1,MOD(COLUMN(F1),6))&""
②先向下引用源数据，再向右引用。 单元格输入以下公式，右拉直到出现空白。 =OFFSET($A$1,MOD(COLUMN(E1),5),INT(COLUMN(E1)/5)-1)&""
①ROW()返回行号，比如ROW(A2)=2； ②COLUMN()返回列号，比如COLUMN(F1)=6； ③MOD()求余数，比如MOD(1,5)=1，即1÷5余数是1，再比如MOD(5,5)=0，因为5被5整除，没有余数； ④上面每个公式最后都加了&""，作用是把引用空单元格得到的0去掉，显示为空白； ⑤上面公式中用到不少逗号,（英文半角状态下），不能随意省略或移动位置，每一个小豆芽都有它存在的意义。
|
