Một công thức để lấy dòng cho indirect (6 người xem)

[tieuthubuongbinh]

Chào các bác,

Em có sheet menu liệt kê các món ăn, các món đó sẽ có tên sheet để liệt kê nguyên liệu sử dụng cho món ăn đó.
Giờ em tổng hợp 1 sheet BOM để liệt kê toàn bộ món ăn với nguyên liệu (kiểu master data) nên em dùng indirect.

Mỗi sheet code món ăn đều có cùng số dòng từ A6:A28
Do đó, mảng màu em tạo trong sheet BOM là tương ứng với số dòng đó (vd màu xanh lá là liệt kê nguyên liệu của món số 119)

Câu hỏi 1:
Hiện giờ, em copy CT màu xanh lá và dùng replace để đổi dòng CT tô đen cho khu màu cam (5 thành 29). Nó cũng mắc công vì dữ liệu có thể lên đến cảm trăm code

Vd: A6=INDIRECT("'"&$A$5&"'!"&ADDRESS(MOD(ROW(A2)-1,24)+5,1))
A7=INDIRECT("'"&$A$5&"'!"&ADDRESS(MOD(ROW(A3)-1,24)+5,1))

A30=INDIRECT("'"&$A$29&"'!"&ADDRESS(MOD(ROW(A26)-1,24)+5,1))
A31=INDIRECT("'"&$A$29&"'!"&ADDRESS(MOD(ROW(A27)-1,24)+5,1))

Nhờ các bác giúp CT để nó tự chạy luôn ạ.

Câu hỏi 2:
A29=INDIRECT("'Menu'!"&ADDRESS(ROW()-COUNTA($A$5:A28)+COUNTA($A$5:A28)/24,1)) cái này em copy xuống dòng 53, 77, 101 thì tự nó đúng.
Nhưng CT ở A5=INDIRECT("'Menu'!"&ADDRESS(MOD(ROW(A1)-1,24)+5,1)) thì không làm giống được
Có cách nào dùng 1 CT để copy xuống cho nhanh không các bác?

Thân
TTBB

 

[bebo021999]

1-Tại sao trong các sheet 119,120...có những ô trống xen kẽ nguyên liệu vậy bạn?
Có thể xếp liên tục được không?
2- Từ đó, sheet BOM, Món ăn và NL cũng xếp liên tục
Để lập công thức tại sheet BOM không khó, nếu bạn chịu khó sắp xếp lại theo y/c 1, sau đó post lên mình sẽ giúp bạn.

 

[tieuthubuongbinh]

1-Tại sao trong các sheet 119,120...có những ô trống xen kẽ nguyên liệu vậy bạn?
Có thể xếp liên tục được không?
2- Từ đó, sheet BOM, Món ăn và NL cũng xếp liên tục
Để lập công thức tại sheet BOM không khó, nếu bạn chịu khó sắp xếp lại theo y/c 1, sau đó post lên mình sẽ giúp bạn.

1- Mình lấy 1 file sẵn có nên nó vậy. Có dòng trống là để có khi cần thì thêm vào, bên BOM vẫn sẽ hiện ra, nên mình ko gom nó lên sát dòng nhau là vậy. Mình dùng hàm để làm (ko muốn dùng VBA) nên nguyên cụm bê qua, cứ coi như dòng trống là dữ liệu cần luôn được không?
2-Không cần liên tục đâu, miễn bê qua y chang từ các sheet là được

Vì là file giả lập nên mình ko muốn dùng VBA, vì khi có VBA trên file thật mà data khác đi là mình mù tịt, CT chạy sai ko biết đường đâu mà lần. hihi

 

[Phan Thế Hiệp]

1- Mình lấy 1 file sẵn có nên nó vậy. Có dòng trống là để có khi cần thì thêm vào, bên BOM vẫn sẽ hiện ra, nên mình ko gom nó lên sát dòng nhau là vậy. Mình dùng hàm để làm (ko muốn dùng VBA) nên nguyên cụm bê qua, cứ coi như dòng trống là dữ liệu cần luôn được không?
2-Không cần liên tục đâu, miễn bê qua y chang từ các sheet là được

Vì là file giả lập nên mình ko muốn dùng VBA, vì khi có VBA trên file thật mà data khác đi là mình mù tịt, CT chạy sai ko biết đường đâu mà lần. hihi

Phải muốn vầy không?

Thân

 

[tieuthubuongbinh]

Phải muốn vầy không?

Thân

Dạ đúng ạ. Cám ơn bác rất nhiều.
CT của bác xa tầm hiểu biết của em quá nên em sợ không ứng dụng vào thực tế trong công việc được nên nhờ bác dạy thêm cho em nhé (vì đây chỉ là 1 file giả định để em học hỏi thêm):

1. Name: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Em không thấy bác viết code VBA, mà sao cái hàm này nó hiểu nhỉ. Em search thì hiểu get.workbook là 1 bộ hàm macro 04 (em có download file về như hướng dẫn mà không tìm được bài viết để hiểu cách dùng, cũng cố gắng google các kiểu mà ko ra).
https://www.giaiphapexcel.com/diend...ích-công-thức-shn-get-workbook-1-now-0.13030/
Em thử tạo 1 file voi name này, tên sheet khác thì ko ra. Em thử add thêm sheet vào file hiện tại thì list cũng ko tự thêm vào.
=>Câu hỏi: làm sao em có thể ứng dụng câu lệnh này?

2. LOOKUP(10^10,C$5:C6)
Bác giải thích cách dùng được không ạ?
Vì khi em cho E10=LOOKUP(10^10,C$5:C6), thì nó chạy đúng 24 dòng 119 thì chuyển sang mã tiếp theo 120...
Nhưng em đổi F10=Lookup(10^10,A$5:A6) nó lại ko ra. Trong khi data của cột A và cột C hoàn toàn giống nhau.

3. Hàm T...
Vì bài này mã nguyên liệu là text nên ổn, nhưng em thử thêm 1 mã là dạng số thì nó sẽ bị thiếu mất dòng đó. Nên em xin phép bỏ hàm T ra.

 

[Phan Thế Hiệp]

1. Name: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
=>Câu hỏi: làm sao em có thể ứng dụng câu lệnh này?

2. LOOKUP(10^10,C$5:C6)
Bác giải thích cách dùng được không ạ?

3. Hàm T...
Vì bài này mã nguyên liệu là text nên ổn, nhưng em thử thêm 1 mã là dạng số thì nó sẽ bị thiếu mất dòng đó. Nên em xin phép bỏ hàm T ra.

1. Hàng miễn phí trên mạng lấy về dùng cho tiện, vì không cần dùng VBA. Xem: https://www.howtoexcel.org/tips-and...t-of-sheet-names-from-a-workbook-without-vba/
2. LOOKUP(10^10,C$5:C6): xem ví dụ giải thích trong file kèm. Cái này chưa chính xác lắm vì chỉ áp dụng cho số, nếu là chuỗi thì nó vô phương, vì vậy anh đã điều chỉnh lại cho tốt hơn

   C5=IFERROR(IF(MOD(ROW(A1)-1,24)+1=1,INDEX($I$2:$I$100,INT((ROW(A1)-1)/24)+1),T(INDIRECT("'"&INDEX(C$1:C4,INT((ROW(A1)-1)/24)*24+5)&"'!"&ADDRESS(MOD(ROW(A1)-1,24)+5,1)))),"")

   Enter.
3. Thường mã số nên đưa về chuỗi ký tự tốt hơn.

Thân

 

[tieuthubuongbinh]

1. Hàng miễn phí trên mạng lấy về dùng cho tiện, vì không cần dùng VBA.
2. LOOKUP(10^10,C$5:C6): xem ví dụ giải thích trong file kèm. Cái này chưa chính xác lắm vì chỉ áp dụng cho số, nếu là chuỗi thì nó vô phương, vì vậy anh đã điều chỉnh lại cho tốt hơn

   C5=IFERROR(IF(MOD(ROW(A1)-1,24)+1=1,INDEX($I$2:$I$100,INT((ROW(A1)-1)/24)+1),T(INDIRECT("'"&INDEX(C$1:C4,INT((ROW(A1)-1)/24)*24+5)&"'!"&ADDRESS(MOD(ROW(A1)-1,24)+5,1)))),"")

   Enter.
3. Thường mã số nên đưa về chuỗi ký tự tốt hơn.

Thân

1. Em đã làm được rồi. Nhược điểm là nếu đổi tên sheet sau khi đã lập index là nó ko tự update. Thêm sheet vào thì cũng phải làm lại CT index nó mới nhảy vô. Dù sao cũng tận dụng được hì hì. Cám ơn anh nhiều lắm.

2. Em đã hiểu. Vì cột A của em có giá trị vừa text vừa số đan xen nên nó chạy "không đúng ý" hihi

3. Trong thực tế, có khi mã hàng mix tùm lum kiểu text hoặc number luôn mà.

4. Em thử đổi 119 thành A119, phải sửa CT của anh ở index (bỏ --) mới ra. Với CT cũ có lookup là ko ra rồi, CT mới của anh ổn.
Cái này là nhờ hôm qua học bài --a=a của anh mới hiểu đó.

Đôi khi anh ràng nhiều đk quá thành ra người sử dụng lại bị hạn chế khi áp dụng vào thực tế trên file thật đó nha. Hihi

Một lần nữa cám ơn anh rất nhiều. Mong được học hỏi ở anh nhiều hơn.

Thân
TTBB

 

[hml89]

1. Hàng miễn phí trên mạng lấy về dùng cho tiện, vì không cần dùng VBA. Xem: https://www.howtoexcel.org/tips-and...t-of-sheet-names-from-a-workbook-without-vba/
2. LOOKUP(10^10,C$5:C6): xem ví dụ giải thích trong file kèm. Cái này chưa chính xác lắm vì chỉ áp dụng cho số, nếu là chuỗi thì nó vô phương, vì vậy anh đã điều chỉnh lại cho tốt hơn

   C5=IFERROR(IF(MOD(ROW(A1)-1,24)+1=1,INDEX($I$2:$I$100,INT((ROW(A1)-1)/24)+1),T(INDIRECT("'"&INDEX(C$1:C4,INT((ROW(A1)-1)/24)*24+5)&"'!"&ADDRESS(MOD(ROW(A1)-1,24)+5,1)))),"")

   Enter.
3. Thường mã số nên đưa về chuỗi ký tự tốt hơn.

Thân

Xin chào @Phan Thế Hiệp . Bạn cho tôi hỏi 1 chút. Những định mức này đều chung số dòng, vậy nếu có 1 trong những định mức nhiều hơn 1 dòng thì sửa công thức sao để lấy được nhỉ?

 

[Phan Thế Hiệp]

Xin chào @Phan Thế Hiệp . Bạn cho tôi hỏi 1 chút. Những định mức này đều chung số dòng, vậy nếu có 1 trong những định mức nhiều hơn 1 dòng thì sửa công thức sao để lấy được nhỉ?

Số 24 là số dòng cố định cho các sheet "Chi tiết" phải giống nhau.
Nếu muốn thêm thì sửa số 24 này về theo số dòng quy định mới.

Thân

Đôi khi anh ràng nhiều đk quá thành ra người sử dụng lại bị hạn chế khi áp dụng vào thực tế trên file thật đó nha. Hihi

"Dĩ bất biến, ứng vạn biến"

Anh sẵn sàng trả lời mọi câu hỏi của em, cho đến khi em nắm rành công thức. Có hiểu nó thì sau này có biến động sẽ ứng phó được tốt.

Chúc anh em ngày vui.

Thân

 

[hml89]

@Phan Thế Hiệp Tôi có sửa rồi mà không có được.

 

[Phan Thế Hiệp]

@Phan Thế Hiệp Tôi có sửa rồi mà không có được.
View attachment 214567
View attachment 214568

Xem file kèm.

Thân

 

[hml89]

Xem file kèm.

Thân

Cám ơn @Phan Thế Hiệp .Ước gì kiến thức của tôi nó cũng tăng được nhanh như thời gian trôi qua vậy. Chúc bạn ngày mới vui vẻ!

 

[tieuthubuongbinh]

anh @Phan Thế Hiệp thân,

Nhờ anh gợi ý tìm trung bình 3 cột với 2 giả định trong file nha.

Cám ơn anh trước.

 

[Phan Thế Hiệp]

anh @Phan Thế Hiệp thân,

Nhờ anh gợi ý tìm trung bình 3 cột với 2 giả định trong file nha.

Cám ơn anh trước.

Dùng:

=AVERAGE(INDEX(A2:L2,AGGREGATE(14,6,COLUMN($A:$L)/(--A2:L2>0),3)):L2)

Enter fill xuống cho cả hai bảng.
Xem file kèm.

Thân

 

[tieuthubuongbinh]

Dùng:

=AVERAGE(INDEX(A2:L2,AGGREGATE(14,6,COLUMN($A:$L)/(--A2:L2>0),3)):L2)

Enter fill xuống cho cả hai bảng.
Xem file kèm.

Thân

Cách 1:
Em có nghĩ được hướng hơi tương tự cách này, chỉ là bị kẹt cái chỗ {1;2;3} . Với lại ko nghĩ đến offset.
=AVERAGE(N(OFFSET(M2,,AGGREGATE(14,6,COLUMN($A:$L)/(A2:L2>0),{1;2;3})-COLUMN(M2))))
Câu hỏi:
1. Vì ở đây em chỉ lấy có 3 cột nên gõ tay {1;2;3} được, nhưng nếu em lấy 8 cột thì sao anh? hình như {1:8} nó ko valid nhỉ?
2. a/ Vì sao column của offset phải -column(M2) nghĩa là mảng {9;8;7} - {13} ? Em chưa hiểu ý này
b/ Nếu học vẹt, em hiểu vậy được ko: bắt đầu offset ở đâu, thì cứ - column đúng cell đó là ra
3. Hàm N : ràng cho chắc ăn, vì với trường hợp 2 thì chắc chắn phải có N để loại suy các ô text?

Cách 2:
=AVERAGE(INDEX(A2:L2,AGGREGATE(14,6,COLUMN($A:$L)/(--A2:L2>0),3)):L2)
Câu hỏi:
4. Index đang trả về 1 số cụ thể, ko phải cell, vậy sao average hiểu địa chỉ cell chứa số đó?
5. 1 số ở vị trí lung tung : L2 có thể sẽ rất nhiều cell (băng qua rỗng và chữ), sao nó vẫn chỉ lấy số của 3 cột thôi?
6. Học vẹt thì chỉ cần thay số 3 thành 1 số x muốn lấy trung bình của x cột, phải ko anh?

Sao em thích cái hàm aggregate này dễ sợ. Nhưng nếu dùng nó với 1 data lớn quá, có nặng file ko anh?

 

[tieuthubuongbinh]

1. Hàng miễn phí trên mạng lấy về dùng cho tiện, vì không cần dùng VBA. Xem: https://www.howtoexcel.org/tips-and...t-of-sheet-names-from-a-workbook-without-vba/

Thân

anh Hiệp ơi, muốn dùng cái bài trong link anh cho thì phải lưu dưới dạng binary hoặc là macro mới được phải ko anh? em lưu file xlsx không được.