将值按 5 个一组进行分组,然后返回每组的第一个值

将值按 5 个一组进行分组,然后返回每组的第一个值

我有一长串的股票价格,以分钟为单位。C 列显示开盘价(每分钟),F 列显示相应的收盘价

现在我想将这些数据从 1 分钟转换为 5 分钟格式。这意味着对于 CI 列,希望将第一个值替换为一组 5 个值(即 c1 来自 C1:C5,然后是 c6 来自 C6:C10,然后是 C11 来自 C11:C15),而在 FI 列中,我想要该组的最后一个值。请参阅下面突出显示的我需要

(即从 F1:F5 到 F5,然后从 F6:F10 到 F10,然后从 F11:F15 到 F15)。

我不知道如何结合使用 OFFSET/INDEX/MATCH 来实现这些结果。稍后我还想将其转换为 10 分钟/30 分钟/60 分钟格式

答案1

谢谢大家的帮助。我一直在寻找一个简单的解决方案,幸运的是,INDEX 和 OFFSET 的组合有助于解决这个问题。我使用了以下公式:

=INDEX(OFFSET(A$1,(ROW(A1)-1)*2,0,2),2,0)

在上面的公式中,有助于将列表转换为 2 组(或根据要求的任何其他组,因此 2 可以更改为 5、10 等),并且 Index 有助于找到该组的最后一个值。对我来说效果很好。

再次感谢

答案2

我看到您标记了工作表函数,因此请尝试下面的 vba 代码。

您可以将 ipWorksheet 和 opWorksheet 变量更改为 Excel 中的工作表。此外,如果您想根据要从哪里开始获取数据,将 rowOn 更改为另一个数字

Sub Extract_Nth_rows()
'
' Extract_Nth_rows
'

    Dim rowOn, rowTotal, rowJump As Integer
    Dim ipWorksheet, opWorksheet As String

    ipWorksheet = "Sheet1"                              ' CHANGE THIS to the name of your Data sheet
    opWorksheet = "Sheet2"                              ' CHANGE THIS to the name of the sheet to put the data

    rowOn = 1                                           ' This will start on Row 1 of Sheet1
    rowTotal = Sheets(ipWorksheet).UsedRange.Rows.Count ' Count number of Rows


    Sheets(opWorksheet).Cells.Clear                     ' Clear all rows

    While rowOn < rowTotal
        Sheets(ipWorksheet).Select
        Rows(rowOn * 5 & ":" & rowOn * 5).Select
        Selection.Copy
        Sheets(opWorksheet).Select
        Range("A" & rowOn).Select
        ActiveSheet.Paste
        rowOn = rowOn + 1
    Wend
End Sub

答案3

我会为此使用 Power Query 插件。它有 Index 和 Group 命令,应该可以实现你想要的功能。

我已经构建了一个类似的解决方案,您可以查看或下载它:“Power Query 演示 - 将行对反转为两列.xlsx”在我的 One Drive 中:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

首先,我添加一个 Index 列(连续行号),然后应用 Number.Mod 函数以每 2 行分组。对于您的情况,只需将其更改为 5 行即可。

http://office.microsoft.com/en-au/excel-help/number-mod-HA104110103.aspx?CTT=5&origin=HA104122363

您可能希望按时间值分组(而不是假设始终会有 5 行) - Power Query 具有可帮助实现此目的的功能,例如 Time.Minute

http://office.microsoft.com/en-au/excel-help/time-minute-HA104110495.aspx?CTT=5&origin=HA104122363

将分组值计算为新列后,使用 Group 命令来汇总数据:

http://office.microsoft.com/en-au/excel-help/group-rows-in-a-table-HA103993875.aspx?CTT=5&origin=HA103993930

相关内容