所以我在Excel中有很长的原始数据列表,其中我需要一个公式来获取特定行的值并将其粘贴到另一列中。这就是我希望它发生的方式。
So I have a long list of raw data in Excel wherein I need a formula to get the values of specific rows and paste them into another column. This is how I want it to happen.
Excel文件中的数据图片:
Picture of the Data in my Excel file:
我需要最终结果像这样:
I need the end result to be like this:
是否有任何公式?
推荐答案不完全是问题的答案,但是如果您至少有一组数据集可以用作输入值,则可以 match()将您的数据放入表格中...有点接近我想您要问的内容。
Not exactly the answer to your question, but if you have at least 1 of your data sets to use as an input value, you can match() your data into a table... a little bit closer to what I think you're asking.
您可以结合使用 Match(), Address()和 Indirect来实现此目的。
我输入红色值(在图像中为 headerB),然后可以使用以下公式在 D15 中输出后续值:
I input the red value (in the image, "headerB") and I can output the subsequent values in D15 with this formula:
=INDIRECT(ADDRESS(MATCH(C15,A1:A10)+1,1))我匹配使用 Match()在A列中找到输入值。
I match find the input value in column A using Match().
然后,我将其用于通过偏移<$ c $中的 +1 来获取单元格的地址 Address()的c> row 自变量。在这种情况下,输出将为 $ A $ 7 ...我使用 Indirect()获得该地址的值,因此您将看到 b1 。
Then, I use that to get an address of a cell by offsetting +1 in the row argument of Address(). The output would be $A$7 in this case... i get the value of that address using Indirect(), so you will see b1.
对于其他子值,您可以在以下行参数中更改偏移量 Address()分别为 +2 和 +3 。
For the other subvalues, you can change your offset within the row argument of Address() to be +2 and +3.
编辑:
与上述类似,您可以使用一些帮助器列来获取数据,而无需获取起始值:
Similar to the above, you could use some helper columns and get your data without needing to have a starting value:
=INDEX(A1,0) 'outputs headerA in the image example您可以根据需要拖动该公式。 。或者您可以执行以下操作:
You can drag that formula across as needed... or you can do something like:
它将在公式中使用辅助值:
This uses helper values in the formula:
=INDIRECT(ADDRESS(C$13+$G15,1))请注意1、6、11将允许您选择范围并向下拖动以获取偏移量,因为它们之间的间距始终相同距离。
Note that the 1, 6, 11, will allow you to select the range and "drag" down to get the offset since they're always the same spaced distance.
更多推荐
通过增加到Excel中的特定行来获取价值
发布评论