vba Excel 从某些单元格中提取数据
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/6216408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel pulling data from certain cells
提问by ray
I have a file that I only want to extract cells B9, B19, B29, etc etc etc in a pattern throughout the entire file. I would preferably like it to be extracted to a different excel file or someway so that I can do stuff with only those cells in another excel worksheet.
我有一个文件,我只想在整个文件中以某种模式提取单元格 B9、B19、B29 等等等。我更希望将它提取到不同的 excel 文件或以某种方式提取,以便我可以仅使用另一个 excel 工作表中的那些单元格来执行操作。
Potentially, I may have several excel files that I may need to do this sort of thing so if there were a way where I had the same format throughout a lot of files that I could always extract cells B9, B19, B29 that would be great. any help appreciated
潜在地,我可能有几个 excel 文件,我可能需要做这种事情,所以如果有一种方法可以让我在很多文件中使用相同的格式,我总是可以提取单元格 B9、B19、B29,那就太好了. 任何帮助表示赞赏
I looking for syntax if possible
如果可能,我正在寻找语法
EDIT
编辑
Was thinking if I could somehow make an excel IF statement saying if Row has a 9 in it and the row is B then print it somewhere but I want it printed in a column
正在想是否我可以以某种方式制作一个 excel IF 语句,说明如果 Row 中有一个 9 并且该行是 B 然后将它打印在某处,但我希望它打印在一个列中
EDIT 2I just want column B not A like I mentioned before. B9, B19,B29,B39 through the whole file
编辑 2我只想要 B 列,而不是我之前提到的 A 列。B9、B19、B29、B39 贯穿整个文件
采纳答案by eggplant_parm
You could use the INDIRECT function. It takes a cell reference as a text string and returns the value in that cell. So instead of using
您可以使用 INDIRECT 函数。它将单元格引用作为文本字符串并返回该单元格中的值。所以而不是使用
=data!a9
to get the value in sheet "data" in cell a9, you use
要获取单元格 a9 中工作表“数据”中的值,请使用
=indirect("data!a9")
You can also use r1c1 notation, like this:
您还可以使用 r1c1 表示法,如下所示:
=indirect("data!r9c1",false)
From there you can use the ROW and COLUMN functions to go in steps of 10:
从那里您可以使用 ROW 和 COLUMN 函数以 10 步为单位进行操作:
=INDIRECT("data!r"&-1+10*ROW()&"c"&COLUMN(),FALSE)
If you put this formula in A1 of your output sheet and then copy and paste it down and across, it will give you the values in data!A9, data!A19, data!A29,... in cells A1, A2, A3... Depending on how you want your output arranged, you might have to modify the cell reference string.
如果您将此公式放在输出表的 A1 中,然后将其向下和横向复制并粘贴,它将为您提供 data!A9、data!A19、data!A29、... 单元格 A1、A2、A3 中的值... 根据您希望输出的排列方式,您可能需要修改单元格引用字符串。
回答by ray
Just in case you want to do it with code:
以防万一你想用代码来做:
Sub Test()
'Assumes Sheet1 has your values and Sheet2 will be the data extracted from every row ending in 9
Dim iCounter As Long
Dim newSheetRow As Long
Dim aValue As String
Dim bValue As String
newSheetRow = 1
'Start and nine and increment by 10 till you reach end of sheet
For iCounter = 9 To Sheet1.Rows.Count - 1 Step 10 'NOTE: You may not want to do it by RowCount, but just showing you could
aValue = Sheet1.Range("A" & iCounter)
bValue = Sheet1.Range("B" & iCounter)
Sheet2.Range("A" & newSheetRow).Value = "We were on row: " & iCounter
Sheet2.Range("B" & newSheetRow).Value = aValue
Sheet2.Range("C" & newSheetRow).Value = bValue
newSheetRow = newSheetRow + 1
Next iCounter
MsgBox "Done"
End Sub
回答by Harag
Depending on how often you want to do this depends on how you need to do it, if it's a one of them some simple excel commands might help.
根据您想要执行此操作的频率取决于您需要如何执行此操作,如果它是其中之一,则一些简单的 excel 命令可能会有所帮助。
e.g.
例如
In Cell C1 put the following:
在单元格 C1 中输入以下内容:
=MOD(ROW(),10)
then replicate this down to the bottom of your data. the command will return the numbers 1 through to 0. You can then filter the data on column C where value is 9 then select the visible rows and copy the data to a new sheet.
然后将其复制到数据的底部。该命令将返回数字 1 到 0。然后您可以过滤列 C 上的数据,其中值为 9,然后选择可见行并将数据复制到新工作表。
ROW() ' this returns the ROW number of cell the command is in.
MOD(number, divisor) ' this basically divides one number by the other and returns the remainder. so row 9 / 10 = 0 remainder of 9, row 19 / 10 = 1 remainder of 9.
Hope this helps.
希望这可以帮助。