☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007
将值按 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

相关内容