Xin giúp đỡ cách gộp nhiều hàng ngang thành một hàng dọc sang sheet khác? (1 người xem)

[dvadva]

Chào các anh chị trong diễn đàn!
Em có dữ liệu ở các hàng ngang bên sheet này muốn chuyển gộp thành hai hàng dọc sang sheet khác, và muốn bên hàng ngang bên này cập nhật số liệu thì hàng dọc bên kia cũng tự động cập nhật theo. Vậy cho em hỏi có cách kết hợp các hàm thể nào để thực hiện không ạ? xin mọi người hướng dẫn giúp em.
em có kèm file ví dụ bên dưới, cám ơn các anh chị!

 

[giaiphap]

Chào các anh chị trong diễn đàn!
Em có dữ liệu ở các hàng ngang bên sheet này muốn chuyển gộp thành một hàng dọc sang sheet khác, và muốn bên hàng ngang bên này cập nhật số liệu thì hàng dọc bên kia cũng tự động cập nhật theo. Vậy cho em hỏi có cách kết hợp các hàm thể nào để thực hiện không ạ? xin mọi người hướng dẫn giúp em.
em có kèm file ví dụ bên dưới, cám ơn các anh chị!

Ô C7 gõ công thức thế này, sau đó Fill sang phải và xuống dưới.

=OFFSET(Sheet2!$C$4,COLUMN(A1)+(ROW(A1)-1)*7,)

 

[Ba Tê]

Chào các anh chị trong diễn đàn!
Em có dữ liệu ở các hàng ngang bên sheet này muốn chuyển gộp thành một hàng dọc sang sheet khác, và muốn bên hàng ngang bên này cập nhật số liệu thì hàng dọc bên kia cũng tự động cập nhật theo. Vậy cho em hỏi có cách kết hợp các hàm thể nào để thực hiện không ạ? xin mọi người hướng dẫn giúp em.
em có kèm file ví dụ bên dưới, cám ơn các anh chị!

Bạn xem thử file này.
Nếu dữ liệu hàng trăm cột, hàng ngàn dòng thì nên dùng VBA.

 

[dvadva]

Ô C7 gõ công thức thế này, sau đó Fill sang phải và xuống dưới.

=OFFSET(Sheet2!$C$4,COLUMN(A1)+(ROW(A1)-1)*7,)

copy paste mà chưa được bác ạ, em sẽ nghiên cứu và thay đổi vài chỗ xem sao. có gì không hiểu sẽ xin thêm ý kiến của bác sau. em cám ơn bác nhiều!

Bài đã được tự động gộp: 1/12/18

Bạn xem thử file này.
Nếu dữ liệu hàng trăm cột, hàng ngàn dòng thì nên dùng VBA.

Đúng như này rồi bác ạ, dữ liệu của em lên tới hàng trăm hàng nhưng như thế này em thấy ổn quá rồi, làm một ô xong fill xuống dưới em thấy cũng khá nhanh.
Có thêm tùy biến là cũng gộp thành một cột như vậy ở một sheet khác nữa nhưng chỉ lấy mỗi chữ số hàng đơn vị của các số ấy, cái này em sẽ kết hợp thêm với hàm right xem sao. có gì không hiểu em sẽ tham khảo thêm ý kiến của bác sau. em cám ơn bác nhiều!

 

[dvadva]

Bạn xem thử file này.
Nếu dữ liệu hàng trăm cột, hàng ngàn dòng thì nên dùng VBA.

Bác giúp lại em file mẫu này với ạ, em quên mất còn có hàng ngày tháng. hàng ngày tháng bị xen kẽ với hàng số liệu nên em muốn chuyển thành một cột ngày tháng và một cột số liệu ở sheet2 như file mẫu thì dùng hàm thế nào vậy bác?

 

[dvadva]

có ai giúp em với ạ?

 

[snow25]

có ai giúp em với ạ?

Đây bạn xem.

Sub chuyen()
Dim arr, arr1
Dim a As Long, b As Long, i As Long, c As Long, j As Long
With Sheet1
     a = .Range("C" & Rows.Count).End(xlUp).Row
     arr = .Range("C7:I" & a).Value
     ReDim arr1(1 To UBound(arr, 1) * UBound(arr, 2), 1 To 2)
     For i = 1 To UBound(arr, 1) Step 2
         For j = 1 To UBound(arr, 2)
             b = b + 1
             arr1(b, 1) = arr(i, j)
             arr1(b, 2) = arr(i + 1, j)
         Next j
     Next i
End With
With Sheet2
      c = .Range("E" & Rows.Count).End(xlUp).Row
     If c > 4 Then .Range("e5:F" & c).ClearContents
    If b Then .Range("e5").Resize(b, 2).Value = arr1
End With
End Sub

 

[dvadva]

Đây bạn xem.

Sub chuyen()
Dim arr, arr1
Dim a As Long, b As Long, i As Long, c As Long, j As Long
With Sheet1
     a = .Range("C" & Rows.Count).End(xlUp).Row
     arr = .Range("C7:I" & a).Value
     ReDim arr1(1 To UBound(arr, 1) * UBound(arr, 2), 1 To 2)
     For i = 1 To UBound(arr, 1) Step 2
         For j = 1 To UBound(arr, 2)
             b = b + 1
             arr1(b, 1) = arr(i, j)
             arr1(b, 2) = arr(i + 1, j)
         Next j
     Next i
End With
With Sheet2
      c = .Range("E" & Rows.Count).End(xlUp).Row
     If c > 4 Then .Range("e5:F" & c).ClearContents
    If b Then .Range("e5").Resize(b, 2).Value = arr1
End With
End Sub

em cám ơn bác nhưng có cách nào dùng các hàm kết hợp được không bác?
file em gửi kèm là file mẫu một bảng có mấy hàng ngang, và em cần áp dụng lên file chính khoảng 32 bảng với mỗi bảng lên đến hàng trăm hàng ngang bác à. cách trên em thử thay đổi số liệu ở sheet1 nhưng sheet2 không thấy nhảy số liệu theo sheet1 bác ơi.
Các bác có cách nào giúp em với ạ?

 

[HieuCD]

em cám ơn bác nhưng có cách nào dùng các hàm kết hợp được không bác?
file em gửi kèm là file mẫu một bảng có mấy hàng ngang, và em cần áp dụng lên file chính khoảng 32 bảng với mỗi bảng lên đến hàng trăm hàng ngang bác à. cách trên em thử thay đổi số liệu ở sheet1 nhưng sheet2 không thấy nhảy số liệu theo sheet1 bác ơi.
Các bác có cách nào giúp em với ạ?

Gởi file nhiều bảng lên

 

[snow25]

em cám ơn bác nhưng có cách nào dùng các hàm kết hợp được không bác?
file em gửi kèm là file mẫu một bảng có mấy hàng ngang, và em cần áp dụng lên file chính khoảng 32 bảng với mỗi bảng lên đến hàng trăm hàng ngang bác à. cách trên em thử thay đổi số liệu ở sheet1 nhưng sheet2 không thấy nhảy số liệu theo sheet1 bác ơi.
Các bác có cách nào giúp em với ạ?

Đây là dùng VBA bạn phải kích hoạt nó mới chạy được.

 

[dvadva]

Đây là dùng VBA bạn phải kích hoạt nó mới chạy được.

em dùng excell 365 online không biết thế nào, vậy để em thử xem sao.

 

[Hoàng Nhật Phương]

em dùng excell 365 online không biết thế nào, vậy để em thử xem sao.

Hình như là office365 không sử dụng ngôn vba được ạ?

 

[dvadva]

Hình như là office365 không sử dụng ngôn vba được ạ?

Đúng là tìm không ra bạn ạ, cám ơn bạn!

 

[tam888]

Đúng là tìm không ra bạn ạ, cám ơn bạn!

Sử dụng công thức là được

 

[dvadva]

Sử dụng công thức là được

mình nhờ các bác trong đây rồi mà chưa được. mình đang cần loay hoay mấy hôm chưa ra, nếu bạn biết nhờ bạn chỉ giúp mình vụ này với?

 

[snow25]

mình nhờ các bác trong đây rồi mà chưa được. mình đang cần loay hoay mấy hôm chưa ra, nếu bạn biết nhờ bạn chỉ giúp mình vụ này với?

Nhờ anh Phan Thế Hiệp đó bạn.

 

[dvadva]

Nhờ anh Phan Thế Hiệp đó bạn.

Cám ơn bác nhé, để em liên hệ xem có giúp được vụ này không.

 

[Phan Thế Hiệp]

Nhờ anh Phan Thế Hiệp đó bạn.

Lại "làm mai"!?

Cám ơn bác nhé, để em liên hệ xem có giúp được vụ này không.

Lẽ ra bạn nên làm theo yêu cầu của anh @HieuCD ở bài #9, thì bạn đã được hỗ trợ rồi. Vã lại, bài này dùng VBA là tối ưu nhất, nhưng do bạn khoái xài "hàng hiệu excell 365 online" nên nó mới không tận dụng được VBA.

Nay, tôi hỗ trợ bạn trên cơ sở giả định file của bạn có cùng kết cấu cơ sở dữ liệu như file kèm.
Nếu khác đi thì bạn tự lo nhen!

Thân

 

[dvadva]

Lại "làm mai"!?


Lẽ ra bạn nên làm theo yêu cầu của anh @HieuCD ở bài #9, thì bạn đã được hỗ trợ rồi. Vã lại, bài này dùng VBA là tối ưu nhất, nhưng do bạn khoái xài "hàng hiệu excell 365 online" nên nó mới không tận dụng được VBA.

Nay, tôi hỗ trợ bạn trên cơ sở giả định file của bạn có cùng kết cấu cơ sở dữ liệu như file kèm.
Nếu khác đi thì bạn tự lo nhen!

Thân

Tại em không làm ở một máy cố định, máy Win XP dùng excel2003, máy win10 excel 365, và thỉnh thoảng cần thì nhờ nhanh máy nơi khác tùy hoàn cảnh nên em dùng excel 365 trên onedrive online cho đồng bộ và cơ động bác ạ.
Giả định file đúng cấu trúc số liệu 7 cột kèm hàng ngày tháng như file mẫu em đưa lên luôn.
Em cám ơn bác giúp em ca khó này!
Cám ơn các anh chị em trong diễn đàn đã nhiệt tình giúp đỡ!

 

[Phan Thế Hiệp]

@huonglien1901

Bài này có vận dụng cho cột A giống y như bài em đã tham khảo: https://www.giaiphapexcel.com/diendan/threads/copy-dữ-liệu-dựa-vào-số-lượng.139355/#post-894313

Bên kia "số lượng" thì tự cho, bên đây là sự tính toán để biết mỗi Sheet cần có bao nhiêu dòng.

Chúc em ngày vui.
Thân

 

[dvadva]

Lại "làm mai"!?


Lẽ ra bạn nên làm theo yêu cầu của anh @HieuCD ở bài #9, thì bạn đã được hỗ trợ rồi. Vã lại, bài này dùng VBA là tối ưu nhất, nhưng do bạn khoái xài "hàng hiệu excell 365 online" nên nó mới không tận dụng được VBA.

Nay, tôi hỗ trợ bạn trên cơ sở giả định file của bạn có cùng kết cấu cơ sở dữ liệu như file kèm.
Nếu khác đi thì bạn tự lo nhen!

Thân

Em gửi file nhiều bảng làm phiền bác lần cuối về trường hợp này bác ơi.
File mẫu công thức em dò để chỉnh theo nhưng nhiều hàm lạ với em quá nên bị lỗi.
Em gửi file gốc lên nhờ bác làm giúp em chuyển hàng ở sheet ATG sang cột ở sheet ATG Côt. Các sheet ADF ADU...còn lại cấu trúc và chuyển y hệt sheet ATG em sẽ copy nguyên công thức áp sang được.
Ví trí các dòng sheet ATG chuyển sang vị trí cột ở sheet ATG Cột nguyên vậy không phải chỉnh thêm gì nữa bác ạ.

 

[Phan Thế Hiệp]

Em gửi file nhiều bảng làm phiền bác lần cuối về trường hợp này bác ơi.
File mẫu công thức em dò để chỉnh theo nhưng nhiều hàm lạ với em quá nên bị lỗi.
Em gửi file gốc lên nhờ bác làm giúp em chuyển hàng ở sheet ATG sang cột ở sheet ATG Côt. Các sheet ADF ADU...còn lại cấu trúc và chuyển y hệt sheet ATG em sẽ copy nguyên công thức áp sang được.
Ví trí các dòng sheet ATG chuyển sang vị trí cột ở sheet ATG Cột nguyên vậy không phải chỉnh thêm gì nữa bác ạ.

Cấu trúc file của các bài trên không giống nhau, nên xử lý công thức cũng khác nhau.
Nếu các file dữ liệu đồng nhất dạng như file bạn vừa gửi trên thì phải tạo thêm các dữ liệu phụ như sau: (chỉ cần tạo trên 1 sheet mẫu: Vd sheet "ATG Côt", sau đó copy cả Vùng từ hàng 3 đến hàng 400, rồi dán công thức cho các Sheet: "xxxx Côt" ở phía sau)

1. Ô lấy tên Sheet:

   A1=SUBSTITUTE(SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100)," Côt","")," Cột","")

2. Vị trí cột của các bảng chi tiết có trong từng sheet dữ liệu:

   C4=IFERROR(MATCH(C$5,INDIRECT($A$1&"!2:2"),)-3,"")

   Enter, fill qua phải.
3. Tên của các bảng chi tiết hiện hữu trong Sheet dữ liệu:

   C5=IFERROR(INDEX(INDIRECT($A$1&"!2:2"),MATCH(,INDEX(COUNTIF($B$5:B$5,INDIRECT($A$1&"!2:2"))+(INDIRECT($A$1&"!2:2")=""),),)),"")

   Enter, fill qua phải.
4. Nếu bạn đảm bảo ngày là giống nhau giữa các bảng chi tiết hiện hữu trong Sheet dữ liệu:

   A7=IFERROR(OFFSET(INDIRECT(ADDRESS(7,$C$4,,,$A$1)),INT((ROW(A1)-1)/7)*2,MOD(ROW(A1)-1,7)),"")

   Enter, fill xuống.
5. Dòng chi tiết mã hàng:

   C7=IFERROR(T(OFFSET(INDIRECT(ADDRESS(7,C$4,,,$A$1)),INT((ROW(A1)-1)/7)*2+1,MOD(ROW(A1)-1,7))),"")

   Enter, fill qua phải rồi copy cả hàng xuống.

Thân

 

[dvadva]

Cấu trúc file của các bài trên không giống nhau, nên xử lý công thức cũng khác nhau.
Nếu các file dữ liệu đồng nhất dạng như file bạn vừa gửi trên thì phải tạo thêm các dữ liệu phụ như sau: (chỉ cần tạo trên 1 sheet mẫu: Vd sheet "ATG Côt", sau đó copy cả Vùng từ hàng 3 đến hàng 400, rồi dán công thức cho các Sheet: "xxxx Côt" ở phía sau)

1. Ô lấy tên Sheet:

   A1=SUBSTITUTE(SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100)," Côt","")," Cột","")

2. Vị trí cột của các bảng chi tiết có trong từng sheet dữ liệu:

   C4=IFERROR(MATCH(C$5,INDIRECT($A$1&"!2:2"),)-3,"")

   Enter, fill qua phải.
3. Tên của các bảng chi tiết hiện hữu trong Sheet dữ liệu:

   C5=IFERROR(INDEX(INDIRECT($A$1&"!2:2"),MATCH(,INDEX(COUNTIF($B$5:B$5,INDIRECT($A$1&"!2:2"))+(INDIRECT($A$1&"!2:2")=""),),)),"")

   Enter, fill qua phải.
4. Nếu bạn đảm bảo ngày là giống nhau giữa các bảng chi tiết hiện hữu trong Sheet dữ liệu:

   A7=IFERROR(OFFSET(INDIRECT(ADDRESS(7,$C$4,,,$A$1)),INT((ROW(A1)-1)/7)*2,MOD(ROW(A1)-1,7)),"")

   Enter, fill xuống.
5. Dòng chi tiết mã hàng:

   C7=IFERROR(T(OFFSET(INDIRECT(ADDRESS(7,C$4,,,$A$1)),INT((ROW(A1)-1)/7)*2+1,MOD(ROW(A1)-1,7))),"")

   Enter, fill qua phải rồi copy cả hàng xuống.

Thân

Lúc đầu em nghĩ đơn giản từ file mẫu xem kết cấu công thức rồi áp cho từng bảng, sau mới thấy sai thật.
File gốc em mới gửi ở trên nguyên vậy luôn bác ạ, đồng nhất cấu trúc các sheet. Các sheet chuyển như này quá ưng ý rồi bác ạ, bác giải thích các dòng công thức khá dễ hiểu.
Cám ơn bác và các anh chị em. Em chúc bác sức khỏe!

 

[dvadva]

Cấu trúc file của các bài trên không giống nhau, nên xử lý công thức cũng khác nhau.
Nếu các file dữ liệu đồng nhất dạng như file bạn vừa gửi trên thì phải tạo thêm các dữ liệu phụ như sau: (chỉ cần tạo trên 1 sheet mẫu: Vd sheet "ATG Côt", sau đó copy cả Vùng từ hàng 3 đến hàng 400, rồi dán công thức cho các Sheet: "xxxx Côt" ở phía sau)

1. Ô lấy tên Sheet:

   A1=SUBSTITUTE(SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100)," Côt","")," Cột","")

2. Vị trí cột của các bảng chi tiết có trong từng sheet dữ liệu:

   C4=IFERROR(MATCH(C$5,INDIRECT($A$1&"!2:2"),)-3,"")

   Enter, fill qua phải.
3. Tên của các bảng chi tiết hiện hữu trong Sheet dữ liệu:

   C5=IFERROR(INDEX(INDIRECT($A$1&"!2:2"),MATCH(,INDEX(COUNTIF($B$5:B$5,INDIRECT($A$1&"!2:2"))+(INDIRECT($A$1&"!2:2")=""),),)),"")

   Enter, fill qua phải.
4. Nếu bạn đảm bảo ngày là giống nhau giữa các bảng chi tiết hiện hữu trong Sheet dữ liệu:

   A7=IFERROR(OFFSET(INDIRECT(ADDRESS(7,$C$4,,,$A$1)),INT((ROW(A1)-1)/7)*2,MOD(ROW(A1)-1,7)),"")

   Enter, fill xuống.
5. Dòng chi tiết mã hàng:

   C7=IFERROR(T(OFFSET(INDIRECT(ADDRESS(7,C$4,,,$A$1)),INT((ROW(A1)-1)/7)*2+1,MOD(ROW(A1)-1,7))),"")

   Enter, fill qua phải rồi copy cả hàng xuống.

Thân

Nhờ bác Hiệp giúp em công thức tô màu tự động trong Conditional Formating như yêu cầu ở dưới bác ơi
Em muốn tự động tô màu Đỏ từ cột M và các cột bên phải cột M (các cột này được đánh số thứ tự từ 1 đến 120) khi tương ứng với từng dòng thì các giá trị ở các cột này bằng giá trị ở cột K.
Em cám ơn!

 

[Phan Thế Hiệp]

Nhờ bác Hiệp giúp em công thức tô màu tự động trong Conditional Formating như yêu cầu ở dưới bác ơi
Em muốn tự động tô màu Đỏ từ cột M và các cột bên phải cột M (các cột này được đánh số thứ tự từ 1 đến 120) khi tương ứng với từng dòng thì các giá trị ở các cột này bằng giá trị ở cột K.
Em cám ơn!

Khuyên bạn với nhiều bảng biểu, lại dùng Conditional Formatting để tô màu cho Vùng rộng lớn (hơn 300 dòng x 120 cột = 36.000 ô x Số sheets => Con số lớn) sẽ làm trì trệ máy mỗi khi bạn mở lên sử dụng.

Cách tốt nhất bạn nhờ anh em viết các đoạn lập trình để: trích lục bảng và tô màu thì sẽ làm cho file mở lên sử dụng mượt mà hơn.

Riêng file đính kèm tôi cũng làm thử cho bạn theo yêu cầu mới nhưng, theo tôi, chỉ để tham khảo.

Thân

 

[dvadva]

Khuyên bạn với nhiều bảng biểu, lại dùng Conditional Formatting để tô màu cho Vùng rộng lớn (hơn 300 dòng x 120 cột = 36.000 ô x Số sheets => Con số lớn) sẽ làm trì trệ máy mỗi khi bạn mở lên sử dụng.

Cách tốt nhất bạn nhờ anh em viết các đoạn lập trình để: trích lục bảng và tô màu thì sẽ làm cho file mở lên sử dụng mượt mà hơn.

Riêng file đính kèm tôi cũng làm thử cho bạn theo yêu cầu mới nhưng, theo tôi, chỉ để tham khảo.

Thân

Em cám ơn bác!
Em thấy hạn chế của công thức làm hệ thống máy xử lý chậm khi số lượng ô lớn như này, em phụ thuộc nhiều vào làm trên excel online mà VBA không thực hiện được cho excel online nên không biết làm thế nào để tận dụng VBA, bữa giờ toàn phải phụ thuộc vào công thức bác ạ.