Lấy kết quả dựa theo tiêu đề cột

[pvcuong18]

Em chào các bác
Các bác giúp em để có kết quả mong muốn với ạ. Em không biết giải thích thế nào nên em xin phép gửi file ạ.
Em cảm ơn các bác nhiều

 

[hoamattroicoi]

Mình làm với unpivot Column.
Xem kết quả ở sheet "Kết quả trả về" vùng từ cột G nhé.
Muốn thêm dữ liệu thì kéo chuột mở rộng bảng rồi vào Data\Refresh All nha.

Nếu muốn dùng code thì thế này :

Sub Loc()
Dim i As Long, rW As Long, j As Long
Dim k As Long
Dim SortRange As Range, SortKey As Range
Dim sArr(), dArr()
rW = Sheet1.Range("A65536").End(xlUp).Row

sArr = Sheet1.Range("A1", Sheet1.Range("XFC1").End(xlToLeft).Offset(rW)).Value
ReDim dArr(1 To UBound(sArr, 2) * UBound(sArr, 1), 1 To 3)


For i = 2 To UBound(sArr, 1)
For j = 6 To UBound(sArr, 2)
    If sArr(i, j) > 0 Then
        k = k + 1
        dArr(k, 1) = sArr(1, j)
        dArr(k, 2) = sArr(i, 1)
        dArr(k, 3) = sArr(i, j)
      End If
Next
Next
With Sheet2
If k Then

    .Range("K3:M10000").ClearContents
    .Range("K3").Resize(k, 3) = dArr
End If
Set SortRange = .Range("K2", .Range("M65536").End(xlUp))
Set SortKey = .Range("K2")
SortRange.Sort Key1:=SortKey, Order1:=xlAscending, Header:=xlYes
End With

End Sub

 

[longtay1111]

Em chào các bác
Các bác giúp em để có kết quả mong muốn với ạ. Em không biết giải thích thế nào nên em xin phép gửi file ạ.
Em cảm ơn các bác nhiều

Nếu bạn muốn dùng công thức thì bạn thử

A2=INDEX('Dữ liêu'!$F$1:$J$1,,AGGREGATE(15,6,TRANSPOSE(ROW($1:$20))/(OFFSET('Dữ liêu'!$F$1:$J$1,MATCH('Kết quả trả về'!B2,'Dữ liêu'!$A$2:$A$7,0),,)>0),COUNTIF($B$2:B2,B2)))

B2=INDEX('Dữ liêu'!$A$2:$A$7,AGGREGATE(15,6,ROW($1:$20)/('Dữ liêu'!$F$2:$J$7>0),ROW(1:1)))

C2=INDEX(OFFSET('Dữ liêu'!$F$1:$J$1,MATCH('Kết quả trả về'!B2,'Dữ liêu'!$A$2:$A$7,0),,),,AGGREGATE(15,6,TRANSPOSE(ROW($1:$20))/(OFFSET('Dữ liêu'!$F$1:$J$1,MATCH('Kết quả trả về'!B2,'Dữ liêu'!$A$2:$A$7,0),,)>0),COUNTIF($B$2:B2,B2)))


Mình làm cho bạn cả 2 Sheet để bạn tham khảo nhé


Thân

 

[Phan Thế Hiệp]

Em chào các bác
Các bác giúp em để có kết quả mong muốn với ạ. Em không biết giải thích thế nào nên em xin phép gửi file ạ.
Em cảm ơn các bác nhiều

Tham khảo thêm:
A2:

=INDEX('Dữ liêu'!$A$1:$K$1,INT(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1))/10^3))

B2:

=INDEX('Dữ liêu'!$A$1:$A$7,MOD(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1)),10^3))

C2:

=SUMPRODUCT(('Dữ liêu'!$F$1:$K$1=A2)*('Dữ liêu'!$A$2:$A$7=B2)*'Dữ liêu'!$F$2:$K$7)

Thân

 

[hocexcel_1991]

1 cách dùng excel365

=LET(a,'Dữ liêu'!F2:J7,HSTACK(TEXTSPLIT(CONCAT(TOCOL(IF(a>0,'Dữ liêu'!F1:J1&"#"&'Dữ liêu'!A2:A7,k),3,1)&"|"),"#","|",1),TOCOL(IF(a>0,a,k),3,TRUE)))

 

[longtay1111]

Tham khảo thêm:
A2:

=INDEX('Dữ liêu'!$A$1:$K$1,INT(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1))/10^3))

B2:

=INDEX('Dữ liêu'!$A$1:$A$7,MOD(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1)),10^3))

C2:

=SUMPRODUCT(('Dữ liêu'!$F$1:$K$1=A2)*('Dữ liêu'!$A$2:$A$7=B2)*'Dữ liêu'!$F$2:$K$7)

Thân

Đọc các bài của bác Hiệp thật sự khâm phục cách giải của bác, mỗi bài của bác đối với em như 1 SIÊU PHẨM, mỗi bài của bác như một bầu trời kiến thức để cho em học hỏi nâng cao trình độ.

Em rất ngại dùng MOD và INT vì không thạo cách sử dụng 2 hàm này, đặc biệt thì khi nào ROW( )*10^3+COLUMN( ) hay ROW( )+COLUMN( )*10^3.....hoặc khi nào trả về DÒNG/ CỘT...em thường bị rối khi sử dụng( do em cũng chưa hiểu bản chất của ROW( )*10^3+COLUMN hay ROW( )+COLUMN( )*10^3...)

Nếu có thể bác Hiệp có tài liệu về vấn đề này có thể cho em xin để em tham khảo thêm vấn đề này được không ạ.

Em chân thành cảm ơn bác rất nhiều, chúc bác 1 ngày mới nhiều niềm vui.

Trân trọng

 

[Ngocminh19]

Tham khảo thêm:
A2:

=INDEX('Dữ liêu'!$A$1:$K$1,INT(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1))/10^3))

B2:

=INDEX('Dữ liêu'!$A$1:$A$7,MOD(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1)),10^3))

C2:

=SUMPRODUCT(('Dữ liêu'!$F$1:$K$1=A2)*('Dữ liêu'!$A$2:$A$7=B2)*'Dữ liêu'!$F$2:$K$7)

Thân

Em chào anh
Cảm ơn anh đã chia sẻ công thức,đúng cái em cần
Em có vấn đề khác chủ topic là cột mã hàng của em có nhiều mã trùng lặp(không duy nhất như ví dụ của chủ đề)
Em áp dụng công thức của anh cũng ra kết quả đúng ,nhưng phải coppy giá trị sau xóa trùng lặp và giá trị 0 đi.
Mong anh xem file và giúp đỡ sửa công thức phần trùng lặp với ạ
Em cảm ơn anh!

 

[Phan Thế Hiệp]

Nếu có thể bác Hiệp có tài liệu về vấn đề này có thể cho em xin để em tham khảo thêm vấn đề này được không ạ.

Đâu có gì ghê gớm lắm đâu mà gọi là "siêu phẩm"! Chẳng qua là bạn chưa biết thì thấy nó lạ, biết rồi thì thấy nó cũng là "một ngày như mọi ngày" thôi.

Đây, diễn đàn này là kho tài liệu bạn cần đây. "Hạnh phúc đang trong tay, sao tìm đâu mãi mãi...(Đức Huy)"

Về công thức mảng bạn có thể lục tìm hơn chục năm trước với những nickname sau: @excel_lv1.5, @huuthang_bd, @HieuCD...và còn nhiều người khác nữa trước hơn nữa, nhiều lắm! Học không hết.

Row()*10^3, hay Column()*10^3 là ăn thua yêu cầu của thớt muốn sắp xếp ra sao thôi, như bài này thì thớt muốn cột xếp trước thứ tự từ trái qua phải, trong từng cột mới tính thứ tự từ trên xuống dưới, nên phải là Column(...)*10^3+row(...)

Chẳng qua chỉ là tiểu xảo để định vị: dòng-cột của 1 mảng. Việc này là thuộc về tư duy của mỗi người khi xử lý vấn đề, không thể hỏi rằng làm sao nghĩ được vậy. Tuy nhiên, có thể học hỏi và bắt chước theo thôi!

Cố gắng lên, bạn còn nhiều cơ hội khám phá những điều thú vị về công thức mảng lắm.

Bài đã được tự động gộp: 9/8/24

Em chào anh
Cảm ơn anh đã chia sẻ công thức,đúng cái em cần
Em có vấn đề khác chủ topic là cột mã hàng của em có nhiều mã trùng lặp(không duy nhất như ví dụ của chủ đề)
Em áp dụng công thức của anh cũng ra kết quả đúng ,nhưng phải coppy giá trị sau xóa trùng lặp và giá trị 0 đi.
Mong anh xem file và giúp đỡ sửa công thức phần trùng lặp với ạ
Em cảm ơn anh!

Với dữ liệu trên 1000 dòng mà bạn dùng công thức mảng để xử lý, chính nó sẽ làm cho máy bạn chạy ì ạch.

Phương án tối ưu là bạn đem yêu cầu của bạn qua chuyên mục "Lập trình với Excel" nhờ anh em hỗ trợ VBA.

Thân

 

[pvcuong18]

Em cảm ơn bác đã giúp đỡ em ạ.
Em không hiểu về VBA, phiền bác giúp em thêm 1 lần nữa chỉnh code để trả về giá trị ở cột ABC với ạ,
Em cảm ơn bác nhiều

Mình làm với unpivot Column.
Xem kết quả ở sheet "Kết quả trả về" vùng từ cột G nhé.
Muốn thêm dữ liệu thì kéo chuột mở rộng bảng rồi vào Data\Refresh All nha.

Nếu muốn dùng code thì thế này :

Sub Loc()
Dim i As Long, rW As Long, j As Long
Dim k As Long
Dim SortRange As Range, SortKey As Range
Dim sArr(), dArr()
rW = Sheet1.Range("A65536").End(xlUp).Row

sArr = Sheet1.Range("A1", Sheet1.Range("XFC1").End(xlToLeft).Offset(rW)).Value
ReDim dArr(1 To UBound(sArr, 2) * UBound(sArr, 1), 1 To 3)


For i = 2 To UBound(sArr, 1)
For j = 6 To UBound(sArr, 2)
    If sArr(i, j) > 0 Then
        k = k + 1
        dArr(k, 1) = sArr(1, j)
        dArr(k, 2) = sArr(i, 1)
        dArr(k, 3) = sArr(i, j)
      End If
Next
Next
With Sheet2
If k Then

    .Range("K3:M10000").ClearContents
    .Range("K3").Resize(k, 3) = dArr
End If
Set SortRange = .Range("K2", .Range("M65536").End(xlUp))
Set SortKey = .Range("K2")
SortRange.Sort Key1:=SortKey, Order1:=xlAscending, Header:=xlYes
End With

End Sub

Bài đã được tự động gộp: 9/8/24

Nếu bạn muốn dùng công thức thì bạn thử

A2=INDEX('Dữ liêu'!$F$1:$J$1,,AGGREGATE(15,6,TRANSPOSE(ROW($1:$20))/(OFFSET('Dữ liêu'!$F$1:$J$1,MATCH('Kết quả trả về'!B2,'Dữ liêu'!$A$2:$A$7,0),,)>0),COUNTIF($B$2:B2,B2)))

B2=INDEX('Dữ liêu'!$A$2:$A$7,AGGREGATE(15,6,ROW($1:$20)/('Dữ liêu'!$F$2:$J$7>0),ROW(1:1)))

C2=INDEX(OFFSET('Dữ liêu'!$F$1:$J$1,MATCH('Kết quả trả về'!B2,'Dữ liêu'!$A$2:$A$7,0),,),,AGGREGATE(15,6,TRANSPOSE(ROW($1:$20))/(OFFSET('Dữ liêu'!$F$1:$J$1,MATCH('Kết quả trả về'!B2,'Dữ liêu'!$A$2:$A$7,0),,)>0),COUNTIF($B$2:B2,B2)))


Mình làm cho bạn cả 2 Sheet để bạn tham khảo nhé


Thân

Em cảm ơn bác nhiều ạ

Bài đã được tự động gộp: 9/8/24

Tham khảo thêm:
A2:

=INDEX('Dữ liêu'!$A$1:$K$1,INT(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1))/10^3))

B2:

=INDEX('Dữ liêu'!$A$1:$A$7,MOD(AGGREGATE(15,6,(ROW('Dữ liêu'!$F$2:$F$7)+COLUMN('Dữ liêu'!$F$1:$K$1)*10^3)/('Dữ liêu'!$F$2:$K$7>0),ROWS($1:1)),10^3))

C2:

=SUMPRODUCT(('Dữ liêu'!$F$1:$K$1=A2)*('Dữ liêu'!$A$2:$A$7=B2)*'Dữ liêu'!$F$2:$K$7)

Thân

Em cảm ơn bác nhiều ạ

 

[bebo021999]

Em có vấn đề khác chủ topic là cột mã hàng của em có nhiều mã trùng lặp(không duy nhất như ví dụ của chủ đề)

Data của bạn có nhiều dữ liệu, nên dùng VBA thì hợp lý hơn.

Option Explicit
Sub tonghop()
Dim i&, j&, k&, rng, res()
Dim dic As Object, key, st As String
Set dic = CreateObject("Scripting.Dictionary")
With Sheet3
    rng = .[B2].CurrentRegion.Value
    For i = 2 To UBound(rng)
        For j = 11 To UBound(rng, 2)
            st = rng(i, 2) & "|" & rng(1, j)
            If Not dic.exists(st) Then
                dic.Add st, rng(i, j)
            Else
                dic(st) = dic(st) + rng(i, j)
            End If
        Next
    Next
End With
ReDim res(1 To dic.Count, 1 To 3)
For Each key In dic.keys
    If dic(key) > 0 Then
        k = k + 1
        res(k, 1) = Split(key, "|")(1)
        res(k, 2) = Split(key, "|")(0)
        res(k, 3) = dic(key)
    End If
Next
Set dic = Nothing
Sheet4.Activate
Range("A3:C10000").ClearContents
If k > 0 Then Range("A3").Resize(k, 3).Value = res
End Sub

 

[Ngocminh19]

Data của bạn có nhiều dữ liệu, nên dùng VBA thì hợp lý hơn.

Option Explicit
Sub tonghop()
Dim i&, j&, k&, rng, res()
Dim dic As Object, key, st As String
Set dic = CreateObject("Scripting.Dictionary")
With Sheet3
    rng = .[B2].CurrentRegion.Value
    For i = 2 To UBound(rng)
        For j = 11 To UBound(rng, 2)
            st = rng(i, 2) & "|" & rng(1, j)
            If Not dic.exists(st) Then
                dic.Add st, rng(i, j)
            Else
                dic(st) = dic(st) + rng(i, j)
            End If
        Next
    Next
End With
ReDim res(1 To dic.Count, 1 To 3)
For Each key In dic.keys
    If dic(key) > 0 Then
        k = k + 1
        res(k, 1) = Split(key, "|")(1)
        res(k, 2) = Split(key, "|")(0)
        res(k, 3) = dic(key)
    End If
Next
Set dic = Nothing
Sheet4.Activate
Range("A3:C10000").ClearContents
If k > 0 Then Range("A3").Resize(k, 3).Value = res
End Sub

Em xin cảm ơn bác nhé.Đúng rồi ạ