Nhờ giúp đỡ code tham chiếu dữ liệu từ hàng và cột

[S.Ha]

Chào các bác!
Em đang có một bài toán tính giá thành căn cứ vào bảng giá cho trước nhưng dữ liệu tham chiếu theo cả hàng và cột ạ
Các bác giúp em một code để có thể tự tìm kiếm với ạ
Em cảm ơn ạ

 

[hoangtuan.net]

Chào các bác!
Em đang có một bài toán tính giá thành căn cứ vào bảng giá cho trước nhưng dữ liệu tham chiếu theo cả hàng và cột ạ
Các bác giúp em một code để có thể tự tìm kiếm với ạ
Em cảm ơn ạ

Dùng hàm index và match là được bạn nhé!
Lưu ý: Hiện mình dùng Excel 365 còn bản thấp hơn mình không rõ có dùng được không!

 

[S.Ha]

Dùng hàm index và match là được bạn nhé!
Lưu ý: Hiện mình dùng Excel 365 còn bản thấp hơn mình không rõ có dùng được không!
View attachment 299662

Cảm ơn bác,
hiện tại em đang dùng Office 2016, muốn chạy cái này cần dùng dưới dạng mảng mới chạy được, mà dữ liệu nhiều quá, nó thực sự chạy quá chậm

 

[cantl]

chậm

Tình hình là bạn phải đặt tên riêng không thì sẽ rối ren như Nga và Út Na.
OBen: có nghĩa là "ở bển"

Sub Macro2()
    Application.CutCopyMode = False
    Dim Vung As Range
    On Error GoTo Thoat
    Set Vung = Application.InputBox("Chon vung tinh:", , "$D$4:$G$7", , , , , 8)
    Vung.Formula2R1C1 = Evaluate("""=SUM(MMULT((RC1=_CotKhachHangOBen)*(RC2=_CotLoaiDichVuOBen)*(R3C=_CotNoiDungDichVuOBen)*_BangGiaOBen,(--TRANSPOSE(RC3=_DongPhuongTienOBen))))""")
    Vung = Vung.Value
    Vung.Select
Thoat:
    Application.CutCopyMode = True
End Sub

 

[hoangtuan.net]

Cảm ơn bác,
hiện tại em đang dùng Office 2016, muốn chạy cái này cần dùng dưới dạng mảng mới chạy được, mà dữ liệu nhiều quá, nó thực sự chạy quá chậm

Nếu không dùng công thức mảng bạn có thể tham khảo cách sử dụng cột phụ trong bảng giá như sau:
=INDEX($D$4:$I$19;MATCH($A25&$B25&D$24;$J$4:$J$19;0);MATCH($C25;$D$3:$I$3;0))

 

[HUONGHCKT]

Chào các bác!
Em đang có một bài toán tính giá thành căn cứ vào bảng giá cho trước nhưng dữ liệu tham chiếu theo cả hàng và cột ạ
Các bác giúp em một code để có thể tự tìm kiếm với ạ
Em cảm ơn ạ

Góp vui.
@S.Ha tham khảo:

Option Explicit

Sub HA()
Dim i&, j&, lr&, t&, k&
Dim Arr(), Arr1(), KQ()
Dim Dic As Object, Key, Temp
Dim Sh As Worksheet, Ws As Worksheet
Dim Ten As String, LoaiDV As String, LoaiPT As String, NoiDungDV As String
 
Application.ScreenUpdating = False
Set Sh = Sheets("BangGia")
lr = Sh.Cells(100000, 1).End(xlUp).Row
Arr = Sh.Range("A3:I" & lr).Value
Set Dic = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
    For j = 4 To UBound(Arr, 2)
        Key = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3) & "|" & Arr(1, j)
        Dic(Key) = Arr(i, j)
    Next j
Next i

Set Ws = Sheets("Bang1")
Arr1 = Ws.Range("A14: G" & Ws.Cells(100000, 1).End(xlUp).Row).Value
ReDim KQ(1 To UBound(Arr1), 1 To 4)
For i = 2 To UBound(Arr1)
    t = t + 1: k = 0
    Ten = Arr1(i, 1)
    LoaiDV = Arr1(i, 2)
    LoaiPT = Arr1(i, 3)
    For j = 4 To UBound(Arr1, 2)
        NoiDungDV = Arr1(1, j): k = k + 1
        Temp = Ten & "|" & LoaiDV & "|" & NoiDungDV & "|" & LoaiPT
        For Each Key In Dic.Keys
            If Key = Temp Then KQ(t, k) = Dic(Key): Exit For
        Next Key
    Next j
Next i
If t Then
    Ws.Range("D15").Resize(100000, 4).ClearContents
    Ws.Range("D15").Resize(t, 4) = KQ
End If
Set Dic = Nothing
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

Lưu ý: Có thể code chỉ chạy cho ra kết quả đúng với dữ liệu trong file đính kèm.

 

[S.Ha]

Tình hình là bạn phải đặt tên riêng không thì sẽ rối ren như Nga và Út Na.
OBen: có nghĩa là "ở bển"

Sub Macro2()
    Application.CutCopyMode = False
    Dim Vung As Range
    On Error GoTo Thoat
    Set Vung = Application.InputBox("Chon vung tinh:", , "$D$4:$G$7", , , , , 8)
    Vung.Formula2R1C1 = Evaluate("""=SUM(MMULT((RC1=_CotKhachHangOBen)*(RC2=_CotLoaiDichVuOBen)*(R3C=_CotNoiDungDichVuOBen)*_BangGiaOBen,(--TRANSPOSE(RC3=_DongPhuongTienOBen))))""")
    Vung = Vung.Value
    Vung.Select
Thoat:
    Application.CutCopyMode = True
End Sub

View attachment 299665

E cảm ơn bác nhiều ạ,
em sẽ nghiên cứu phương pháp này

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

Nếu không dùng công thức mảng bạn có thể tham khảo cách sử dụng cột phụ trong bảng giá như sau:
=INDEX($D$4:$I$19;MATCH($A25&$B25&D$24;$J$4:$J$19;0);MATCH($C25;$D$3:$I$3;0))
View attachment 299666

Em cảm ơn bác ạ
em sẽ thử dùng ạ

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

Góp vui.
@S.Ha tham khảo:

Option Explicit

Sub HA()
Dim i&, j&, lr&, t&, k&
Dim Arr(), Arr1(), KQ()
Dim Dic As Object, Key, Temp
Dim Sh As Worksheet, Ws As Worksheet
Dim Ten As String, LoaiDV As String, LoaiPT As String, NoiDungDV As String
 
Application.ScreenUpdating = False
Set Sh = Sheets("BangGia")
lr = Sh.Cells(100000, 1).End(xlUp).Row
Arr = Sh.Range("A3:I" & lr).Value
Set Dic = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
    For j = 4 To UBound(Arr, 2)
        Key = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3) & "|" & Arr(1, j)
        Dic(Key) = Arr(i, j)
    Next j
Next i

Set Ws = Sheets("Bang1")
Arr1 = Ws.Range("A14: G" & Ws.Cells(100000, 1).End(xlUp).Row).Value
ReDim KQ(1 To UBound(Arr1), 1 To 4)
For i = 2 To UBound(Arr1)
    t = t + 1: k = 0
    Ten = Arr1(i, 1)
    LoaiDV = Arr1(i, 2)
    LoaiPT = Arr1(i, 3)
    For j = 4 To UBound(Arr1, 2)
        NoiDungDV = Arr1(1, j): k = k + 1
        Temp = Ten & "|" & LoaiDV & "|" & NoiDungDV & "|" & LoaiPT
        For Each Key In Dic.Keys
            If Key = Temp Then KQ(t, k) = Dic(Key): Exit For
        Next Key
    Next j
Next i
If t Then
    Ws.Range("D15").Resize(100000, 4).ClearContents
    Ws.Range("D15").Resize(t, 4) = KQ
End If
Set Dic = Nothing
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

Lưu ý: Có thể code chỉ chạy cho ra kết quả đúng với dữ liệu trong file đính kèm.

Em cảm ơn bác rất nhiều ạ
Em sẽ dùng thử và nghiên cứu thêm ạ