Excel - 用实际单元格引用替换偏移公式 [英] Excel - Replace offset formula with actual cell reference

问题描述

我有一张表，从一张很大的表中拉出数千行数据。表格中的一些列是从该表中的第5行获取数据。为了加快创建单元格引用的过程，我使用了一个OFFSET公式来从每第五行获取一个单元格：

  = OFFSET（'Large Sheet'！B $ 2572，（ROW（1：1）-1）* 5 ,,）
 = OFFSET（'Large Sheet'！B $ 2572，（ROW（2：2） 1）* 5 ,,）
 = OFFSET（'Large Sheet'！B $ 2572，（ROW（3：3）-1）* 5 ,,）
 = OFFSET（'Large Sheet'！B $ 2572，（ROW（4：4）-1）* 5 ,,）
 = OFFSET（'Large Sheet'！B $ 2572，（ROW（5：5）-1）* 5 ,,）
等... 
  

OFFSET可以在计算大桌子时吃掉资源，而我寻找一种加速/简化我的公式的方法。有没有什么简单的方法可以将OFFSET公式转换为简单的单元格引用，如：

  ='Large Sheet'！B2572 
 ='大单张'！B2577 
 ='大单张'！B2582 
 ='大张贴'！B2587 
 ='大张贴'！B2592 
等
  

我也不能只是粘贴值。

感谢您的帮助。

解决方案

这里是最后一种不使用VBA或公式的方法。

主要想法

• 添加对单元格 = Sheet1！A1 的引用，并将其复制到与主数据中相同的行数。 / li>
  
• 在 B1 中添加另一个公式为 = MOD（ROW（），5）


• 筛选列 B 并取消选中0（或任何单个数字）


• 删除所有可见的行


• 删除列B


• Voila，每第五行的公式

某些参考图片，都在 Sheet2 上。

准备好AutoFilter的公式。

已过滤并准备删除

删除所有这些行（选择 A1 ，CTRL + SHIFT + DOWN ARROW，SHIFT + SPACE，CTRL + MINUS）

删除列B以获得每5行纯公式的最终结果。 / p>

I have a table that is pulling thousands of rows of data from a very large sheet. Some of the columns in the table are getting their data from every 5th row on that large sheet. In order to speed up the process of creating the cell references, I used an OFFSET formula to grab a cell from every 5th row:

=OFFSET('Large Sheet'!B$2572,(ROW(1:1)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(2:2)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(3:3)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(4:4)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(5:5)-1)*5,,)
etc...

OFFSET can eat up resources during calculation of large tables though, and I'm looking for a way to speed up/simplify my formula. Is there any easy way to convert the OFFSET formula into just a simple cell reference like:

='Large Sheet'!B2572
='Large Sheet'!B2577
='Large Sheet'!B2582
='Large Sheet'!B2587
='Large Sheet'!B2592
etc...

I can't just paste values either. This needs to be an active reference, because the large sheet will change.

Thanks for your help.

解决方案

And here is one last approach to this that does not use VBA or formulas. It's just a quick and dirty use of AutoFilter and deleting rows.

Main idea

• Add a reference to a cell =Sheet1!A1 and copy it down to match as many rows as there are in the main data.
• Add another formula in B1 to be =MOD(ROW(), 5)
• Filter column B and uncheck the 0s (or any single number)
• Delete all the rows that are visible
• Delete column B
• Voila, formulas for every 5th row

Some reference images, these are all taken on Sheet2.

Formulas with AutoFilter ready.

Filtered and ready to delete

Delete all those rows (select A1, CTRL+SHIFT+DOWN ARROW, SHIFT+SPACE, CTRL+MINUS)

Delete column B to get final result with "pure" formulas every 5th row.

这篇关于Excel - 用实际单元格引用替换偏移公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！