Nhờ giúp chuyển dữ liệu từ nhiều cột về 1 cột có điều kiện

[tranquangdiep]

Chào cả nhà, em có vấn đề này mong cả nhà giúp đỡ ạ
Em có chuỗi số liệu nhiệt độ nhiều năm, đang để định dạng mỗi năm như sau (31 hàng (ngày), 12 cột (tháng trong năm).
Giờ e muốn chuyển hết tất cả dữ liệu thành 1 cột liên tiếp nhau theo ngày (dữ liệu cuỗi cùng chỉ còn 2 cột đó là 1 cột ngày tháng năm và 1 cột giá trị nhiệt độ tương ứng)
Ví dụ như hình ạ:
Em xin cảm ơn!

 

[longtay1111]

Chào cả nhà, em có vấn đề này mong cả nhà giúp đỡ ạ
Em có chuỗi số liệu nhiệt độ nhiều năm, đang để định dạng mỗi năm như sau (31 hàng (ngày), 12 cột (tháng trong năm).
Giờ e muốn chuyển hết tất cả dữ liệu thành 1 cột liên tiếp nhau theo ngày (dữ liệu cuỗi cùng chỉ còn 2 cột đó là 1 cột ngày tháng năm và 1 cột giá trị nhiệt độ tương ứng)
Ví dụ như hình ạ: View attachment 288124
Em xin cảm ơn!

Bạn thử công thức tại R2

R2=INDIRECT(ADDRESS(MOD(ROW()-2,COUNT($B$2:$B$32))+2,INT((ROW()-2)/COUNT($B$2:$B$32))+2))

 

[SA_DQ]

Dữ liệu nhiều vầy mà xài công thức có mà mút chỉ!

 

[VetMini]

Dữ liệu nhiều vầy mà xài công thức có mà mút chỉ!

20 năm thì chỉ 7000 ngày chứ mấy.
Biết cách cho vào Table và/hoặc Data Model thì sẽ hiệu quả.
Chỉ hiềm là công thức dùng Indirect cho nên có triển vọng hơi mệt (chỉ triển vọng thôi, chứ cẩn thận cũng chả sao)

Muốn xịn thì dùng Power Query để unpivot chúng.

 

[tranquangdiep]

Bạn thử công thức tại R2

R2=INDIRECT(ADDRESS(MOD(ROW()-2,COUNT($B$2:$B$32))+2,INT((ROW()-2)/COUNT($B$2:$B$32))+2))

Cảm ơn bác, công thức trên vẫn chưa trả về đúng giá trị ngày đầu tiên của tháng mới ạ ví dụ,ngày 01/3/1961 phải là ô D2, trong khi công thức trả về các ngày 29,30,31/2 (k có trong năm này)

 

[HieuCD]

Chào cả nhà, em có vấn đề này mong cả nhà giúp đỡ ạ
Em có chuỗi số liệu nhiệt độ nhiều năm, đang để định dạng mỗi năm như sau (31 hàng (ngày), 12 cột (tháng trong năm).
Giờ e muốn chuyển hết tất cả dữ liệu thành 1 cột liên tiếp nhau theo ngày (dữ liệu cuỗi cùng chỉ còn 2 cột đó là 1 cột ngày tháng năm và 1 cột giá trị nhiệt độ tương ứng)
Ví dụ như hình ạ:
Em xin cảm ơn!

Với dữ liệu nầy không dùng hàm Int và mod được

=INDEX($B$2:$M$1984,DAY(Q2)+(YEAR(Q2)-1961)*32,MONTH(Q2))

 

[tranquangdiep]

Với dữ liệu nầy không dùng hàm Int và mod được

=INDEX($B$2:$M$1984,DAY(Q2)+(YEAR(Q2)-1961)*32,MONTH(Q2))

Em cảm ơn bác, đúng ý em cần rồi ạ.

 

[bebo021999]

Xài VBA nhé:

Option Explicit
Sub test()
Dim lr&, i As Long, i2 As Long, j&, k&, rng, res(1 To 100000, 1 To 2)
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A1:M" & lr).Value
For i = 1 To UBound(rng) - 31
    If i > 31 Then
        For j = 2 To 13
            For i2 = i + 1 To i + 31
                If rng(i2, j) <> "" Then
                    k = k + 1
                    res(k, 1) = DateSerial(rng(i, 1) - 1, j - 1, rng(i2, 1))
                    res(k, 2) = rng(i2, j)
                End If
            Next
        Next
    End If
    i = i + 31
Next
With Range("O2")
    .Resize(100000, 2).ClearContents
    .Resize(k, 2).Value = res
End With
End Sub