Dò tìm vừa dòng , vừa cột (1 người xem)

[truckhoa2006]

Em có một file có các Sheet như sau:

NVL.M: Nguyên Vật Liệu mua vào
(Nguyên vật liệu mua vào tuỳ theo giá từng ngày, từng tháng khác nhau)

DMCB: Định mức chế biến
(Tại cột thành tiền từng Nguyên vật liệu = dò giá từng nguyên vật liệu ở NVL.M * Số lượng) Dò giá nguyên vật liệu tuỳ thuộc vào ô C3 ngày tháng năm Sheet KTDM

KTDM: Kiểm tra định mức
Tại Cột C: ta chọn Súp cua ( cột B) thì Cột NVL Chế biến sẽ hiện ra nguyên vật liệu gì ( tuỳ thuộc vào DMCB nếu số lượng > 0 thì cho xuất hiện nguyên vật liệu đó)
Tại cột D: Nếu thoả mãn điều kiện Nón súp cua và nguyên liệu đó thì lấy số lượng của nguyên liệu món đó

Mong các anh chị giúp đỡ, gặp yêu cầu này của sếp em hết đỡ luôn.
Cám ơn các anh chị đã xem bài.

 

[leonguyenz]

Công thức tính số lượng:

=IFERROR(OFFSET(DMCB!$B$5,MATCH(LOOKUP(REPT("z",255),$B$6:$B6),DMCB!$B$5:$B$7,0)-1,MATCH($C6,DMCB!$C$3:$N$3,0)),"")

Công thức thành tiền thì + thêm 1 ở bước nhảy cột:

=IFERROR(OFFSET(DMCB!$B$5,MATCH(LOOKUP(REPT("z",255),$B$6:$B6),DMCB!$B$5:$B$7,0)-1,MATCH($C6,DMCB!$C$3:$N$3,0)+1),"")

 

[truckhoa2006]

Thật tuyệt vời.
- Thêm một chút nữa nha anh: Tại sheet KTDM anh có thể cho em ý kiến hay viết công thức cho cột C. Khi ta đánh nội dung bên cột B ( tên món ăn) thỉ cột C hiện ra tất cả nguyên liệu chế biến không anh. Em nghĩ có thể đặt câu hỏi hơi quá, nhưng em anh c1o thể cho em ý kiến về chỗ này không?
Cám ơn anh

 

[leonguyenz]

Thật tuyệt vời.
- Thêm một chút nữa nha anh: Tại sheet KTDM anh có thể cho em ý kiến hay viết công thức cho cột C. Khi ta đánh nội dung bên cột B ( tên món ăn) thỉ cột C hiện ra tất cả nguyên liệu chế biến không anh. Em nghĩ có thể đặt câu hỏi hơi quá, nhưng em anh c1o thể cho em ý kiến về chỗ này không?
Cám ơn anh

Đặt con trỏ tại dòng 6 sheet KTDM, đặt Name Loc:

=IF((DMCB!$C$4:$N$4="SL")*(OFFSET(DMCB!$C$4:$N$4,MATCH(LOOKUP(REPT("z",255),KTDM!$B$6:$B6),DMCB!$B$5:$B$20,0),)>0),COLUMN(DMCB!$C$4:$N$4)-2,"")

Công thức cho NVL chế biến, C6:

=IF(COUNT(Loc)>=MOD(ROW(A7),7)+1,INDEX(DMCB!$C$3:$N$3,,SMALL(Loc,MOD(ROW(A7),7)+1)),"")

Sửa lại IFERROR nếu thấy cần.
Công thức cột C chỉ đúng với mỗi món ăn cách đều nhau 7 dòng, nếu khác thì sửa lại theo hàm MOD.

 

[truckhoa2006]

Cám ơn anh. Không gì bằng Giải pháp Excel

 

[truckhoa2006]

Anh nói là : " Công thức cột C chỉ đúng với mỗi món ăn cách đều nhau 7 dòng, nếu khác thì sửa lại theo hàm MOD."
Bậy giờ em muốn mỗi món ăn cách nhau 10 dòng, và em thay số 7 thành số 10 nó ra sai, mà thay số 7 thành số 4 nó cũng ra sai. Mong anh chỉ giúp em .
MOD(ROW(A7),7) thay thành MOD(ROW(A7),10) = sai; MOD(ROW(A7),7) thay thành MOD(ROW(A7),4) = sai
Cám ơn anh.

 

[leonguyenz]

Anh nói là : " Công thức cột C chỉ đúng với mỗi món ăn cách đều nhau 7 dòng, nếu khác thì sửa lại theo hàm MOD."
Bậy giờ em muốn mỗi món ăn cách nhau 10 dòng, và em thay số 7 thành số 10 nó ra sai, mà thay số 7 thành số 4 nó cũng ra sai. Mong anh chỉ giúp em .
MOD(ROW(A7),7) thay thành MOD(ROW(A7),10) = sai; MOD(ROW(A7),7) thay thành MOD(ROW(A7),4) = sai
Cám ơn anh.

Tôi cố tình để ROW(A7) và lồng vào MOD(ROW(A7),7). Nếu bạn làm 10 dòng thì sửa thành: MOD(ROW(A10),10).

 

[truckhoa2006]

Ha ha. Cám ơn anh nhiều nhiều

 

[nginh]

Thêm một chút nữa nha anh: Tại sheet KTDM anh có thể cho em ý kiến hay viết công thức cho cột C. Khi ta đánh nội dung bên cột B ( tên món ăn) thỉ cột C hiện ra tất cả nguyên liệu chế biến không anh. Em nghĩ có thể đặt câu hỏi hơi quá, nhưng em anh c1o thể cho em ý kiến về chỗ này không?
Cám ơn anh

Dạng này làm bằng VBA cho khoẻ

 

[truckhoa2006]

Dạng này làm bằng VBA cho khoẻ

Mình cũng nghĩ đến VBA nhưng mình cùi bắp lắm biết sơ sơ thui, lỡ sai không biết làm sao luôn . Hic
Cái của bạn hay lắm cho mình email hay yahoo để tối đi học về hỏi được không ?
Cám ơn bạn

 

[leonguyenz]

Mình cũng nghĩ đến VBA nhưng mình cùi bắp lắm biết sơ sơ thui, lỡ sai không biết làm sao luôn . Hic
Cái của bạn hay lắm cho mình email hay yahoo để tối đi học về hỏi được không ?
Cám ơn bạn

Tham khảo code sau, khỏi cần gõ lại tên món ăn, canh số dòng:
Mở sheet4:
[gpecode=vb]
Private Sub Worksheet_Activate()
Dim Mon As Range, Nvl As Range, i As Integer, j As Integer
Dim Kq(), k As Integer, l As Integer
Set Mon = Sheet2.Range("B5:B" & Sheet2.Range("A65535").End(3).Row)
Set Nvl = Mon.Offset(, 1).Resize(, 11)
ReDim Kq(1 To (Mon.Rows.Count) * (Nvl.Columns.Count), 1 To 5)
For i = 1 To Mon.Rows.Count
l = 0
For j = 1 To 11 Step 2
If Nvl(i, j) > 0 Then
l = l + k + 1
k = k + 1
Kq(k, 1) = IIf(k < l, "", i)
Kq(k, 2) = IIf(k < l, "", Mon(i))
Kq(k, 3) = Sheet2.Cells(3, j + 2)
Kq(k, 4) = Nvl(i, j)
Kq(k, 5) = Nvl(i, j + 1)
End If
Next j
If l Then
k = k + 1
Kq(k, 2) = "Total:"
Kq(k, 5) = Nvl(i, j)
End If
Next i
Sheet4.[A6:E1000].Clear
If k Then
Sheet4.[A6].Resize(k, 5) = Kq
Sheet4.[A6].Resize(k, 5).Borders.LineStyle = 1
Sheet4.[E6].Resize(k).NumberFormat = "#,##0"
End If
End Sub
[/gpecode]
Save file có đuôi mở rộng .xlsm (Excel 2007 trở lên) hoặc .xls.