English 中文(简体)
如何使单元格参考变量变异?
原标题：MS Excel: How do I make cell references variable?
I probably didn t word that title correctly, so let me explain what I m trying to do. I need to find cycles in a series of data. So let’s say I have all of my data in column A of an Excel spreadsheet. If a condition that I’m looking for is true in, say, cell A7, I want to check to check if it’s true in every second cell (A9, A11, A13 and so forth). If it’s not true in every second cell, I want to adjust the model to check every third cell starting with that A7 cell (A10, A13, A16, and so on). If the condition is not true in every third cell, then I want to check every fourth cell (A11, A15, A19, etc.). Programming the formula to check if the condition is true should be somewhat easy using either IF or AND formulas. My problem is how do I change the model to switch from every second cell to every third cell, and then run it to check every fourth cell, and so on. I’d like to, say, set up the formulas in column B and have cell C1 be a user input that determines which cells are used in the formulas in column B. Is there a way to do this? Example: if cell C1 says “2”, then the formulas in column B check every other cell in column A, and if I change the value in cell C1 from “2” to “3”, then the formulas in column B switch from checking every second cell to checking every third cell and report back to me. I could just manually change the cell references in the formulas in column B, but that could take bloody ages and I figure there’s got to be a better way. So I’m looking to make the cell references ‘variable’ in a sense. Instead of hardcoding the cell references and saying “look at cell A7, then look at cell A9, then look at cell A11…” I want to tell Excel “look at A7, then the next cell you look at is dependent upon what I say in cell C1.”
问题回答
The image below shows my setup: I have simple data in column A. Column B determines whether the formula in C should be evaluated. For example, B2 contains the following formula: =IF(MOD(ROW(A2),$D$2)=0,1,0) Column C would contain the logic you want to apply. In this case, I return the data in A if B is 1; otherwise, I return "": =IF(B2=1,A2,"") Column D determines the number of rows to skip.
时间：2016-06-03 16:14:21
If you want just one column, this formula would go in B1 and you can drag it down. Update the $A$1:$A$20 range to include your entire range. This formula will check whether the previous cell in your cycle (every 2,3,4,etc) matches. =IF(AND(ROW()>$C$1,MOD(ROW(),$C$1)=0),INDEX($A$1:$A$20,ROW()-$C$1)=$A1,"") Do not use INDIRECT...ever.
时间：2016-06-03 16:16:00


上一篇：
下一篇：


相关问题
import of excel in SQL imports NULL lines

I have a stored procedure that imports differently formatted workbooks into a database table, does work on them then drops the table. Here is the populating query. SELECT IDENTITY(INT,1,1) AS ID ...

时间：2009-11-13 11:07:29 回答：1 展示：1
Connecting to Oracle 10g with ODBC from Excel VBA

The following code works. the connection opens fine but recordset.recordCount always returns -1 when there is data in the table. ANd If I try to call any methods/properties on recordset it crashes ...

时间：2009-11-13 05:17:54 回答：2 展示：1
Excel date to Unix timestamp

Does anyone know how to convert an Excel date to a correct Unix timestamp?

时间：2009-11-09 20:21:55 回答：11 展示：1
C# GemBox Excel Import Error

I am trying to import an excel file into a data table using GemBox and I keep getting this error: Invalid data value when extracting to DataTable at SourceRowIndex: 1, and SourceColumnIndex: 1. As ...

时间：2009-11-09 16:33:55 回答：3 展示：1
Importing from excel "applications" using SSIS

I am looking for any tips or resources on importing from excel into a SQL database, but specifically when the information is NOT in column and row format. I am currently doing some pre-development ...

时间：2009-11-09 15:06:45 回答：3 展示：1
热门标签

友情链接

Allggapp Alljchome-教程家园 mvfinale.com-影视剧情大结局大全