CÔNG THỨC MẢNG INDEX, LỌC RA NHIỀU GIÁ TRỊ NHƯNG CHỈ LẤY 1 GIÁ TRỊ ĐÚNG ĐIỀU KIỆN

[Mr Joker]

Bạn xem được chưa:

Dạ đúng chuẩn rồi bạn à.
Thật sự rất cảm ơn sự nhiệt tình của bạn.
Chúc bạn vui vẻ!

 

[vanthe_bk6]

Minh có thêm 1 một công thức
=IFERROR(INDEX(OFFSET('BIÊN LAI'!$A$1,1,MATCH(H$2,'BIÊN LAI'!$A$1:$I$1,0)-1,COUNTA(MHV),1),SMALL(IF($B3=MHV,ROW(INDIRECT("1:"&COUNTA(MHV))),""),INT((COLUMN(A2)-1)/3+1))),"")
Mà mọi người cho mình hỏi cách phân tích công thức của mọi người như thế nào, vì nó dài mà có nhiều ngoặc kép làm mình khó phân tích.
Ngay cả công thức của em viết, xem lại cũng khó phân tích.

 

[dazkangel]

Minh có thêm 1 một công thức
=IFERROR(INDEX(OFFSET('BIÊN LAI'!$A$1,1,MATCH(H$2,'BIÊN LAI'!$A$1:$I$1,0)-1,COUNTA(MHV),1),SMALL(IF($B3=MHV,ROW(INDIRECT("1:"&COUNTA(MHV))),""),INT((COLUMN(A2)-1)/3+1))),"")
Mà mọi người cho mình hỏi cách phân tích công thức của mọi người như thế nào, vì nó dài mà có nhiều ngoặc kép làm mình khó phân tích.
Ngay cả công thức của em viết, xem lại cũng khó phân tích.

phân tích loại bỏ và chia từng khu vực
như ở trên loại bỏ iferror đầu tiên
rồi phân tích vào trong chú ý nhất là mấy cái count và match vì nó là tìm kiếm vị trí.
hàm small hay được sử dụng để lấy thứ mảng từ bé đến lớn

 

[akanepro]

Mấy anh chị ơi em cũng có 1 file tương tự như thế này, mấy anh chị giúp em với, em có 1 mã hàng sản xuất nhưng ra nhiều sản phẩm, em muốn cho nó từ sheet 1 qua sheet 2 nhưng em dùng vlookup thì chỉ lấy được dòng đầu tiên, em có tham khảo Index, match rồi nhưng không làm được, mấy anh chị giúp dùm em, em đang tự học excel mà excel nâng cao khó quá.

 

[huonglien1901]

Mấy anh chị ơi em cũng có 1 file tương tự như thế này, mấy anh chị giúp em với, em có 1 mã hàng sản xuất nhưng ra nhiều sản phẩm, em muốn cho nó từ sheet 1 qua sheet 2 nhưng em dùng vlookup thì chỉ lấy được dòng đầu tiên, em có tham khảo Index, match rồi nhưng không làm được, mấy anh chị giúp dùm em, em đang tự học excel mà excel nâng cao khó quá.

Ý bạn muốn như thế nào? Đọc chưa hiểu vấn đề nơi.

 

[akanepro]

Ý là em có 1 mã hàng nhưng có nhiều sản phẩm trong đó (sheet 1), bên phiếu giao hàng (sheet 2) thì em điền mã hàng vào 1 ô thì tự động bảng ở dưới sẽ cập nhật thông tin của sản phẩm theo mã hàng đó, em biết là dùng index, match, count sẽ làm được nhưng nó rối và khó quá, em lại đang tự học excel nên không có ai chỉ bảo, mong anh chị giúp dùm.

 

[dazkangel]

Ý là em có 1 mã hàng nhưng có nhiều sản phẩm trong đó (sheet 1), bên phiếu giao hàng (sheet 2) thì em điền mã hàng vào 1 ô thì tự động bảng ở dưới sẽ cập nhật thông tin của sản phẩm theo mã hàng đó, em biết là dùng index, match, count sẽ làm được nhưng nó rối và khó quá, em lại đang tự học excel nên không có ai chỉ bảo, mong anh chị giúp dùm.

nhìn hoài sẽ quen chứ thấy khó mà ngại giống mình thì sao chịu nổi

 

[akanepro]

Cái này em đang tự học, em không có ngại nhưng có người chỉ dạy hướng dẫn thì vẫn tốt hơn chứ

 

[dazkangel]

Cái này em đang tự học, em không có ngại nhưng có người chỉ dạy hướng dẫn thì vẫn tốt hơn chứ

khó thì mang lên hỏi rồi tự học, chứ bạn nói giải ra mà rối với khó @@ trên đây chỉ cũng 1 phần thôi à, viết hàm còn dễ hơn giải thích. À file bạn gửi bộ phận là SX 2 bên tổng hợp lại SX 1 không hiểu. File mẫu của bạn nhìn không biết làm gì luôn ?? Bạn thử nêu 1 số kết quả có sẵn xem.

 

[akanepro]

khó thì mang lên hỏi rồi tự học, chứ bạn nói giải ra mà rối với khó @@ trên đây chỉ cũng 1 phần thôi à, viết hàm còn dễ hơn giải thích. À file bạn gửi bộ phận là SX 2 bên tổng hợp lại SX 1 không hiểu. File mẫu của bạn nhìn không biết làm gì luôn ?? Bạn thử nêu 1 số kết quả có sẵn xem.

Tức là có 2 cty, 1 cty chuyên sản xuất và in còn 1 cty đóng gói và bán thành phẩm.
Em bỏ vào kết quả em cần lấy rồi, khi em thay đổi số phiếu thì tự động kết quả sẽ được cập nhật lại.

 

[dazkangel]

Tức là có 2 cty, 1 cty chuyên sản xuất và in còn 1 cty đóng gói và bán thành phẩm.
Em bỏ vào kết quả em cần lấy rồi, khi em thay đổi số phiếu thì tự động kết quả sẽ được cập nhật lại.

cho hỏi đã làm ra nhưng nếu 1 phiếu xuất nhiều hơn 5 thì làm sao

 

[dazkangel]

@quocgiacan chào anh, em lại có vài thắc mắc về kiến thức cơ bản, mong anh giúp đỡ:
như file sau: sao em dùng 1 mảng rồi kéo dài từ D13:K13 mà nó không ra nếu làm từng ô sẽ ra:

=TRIM(OFFSET('Tong hop'!$D$8,SMALL(IF(('Tong hop'!$C$9:$C$22=$K$1)*('Tong hop'!$D$9:$D$22=$K$2),(ROW($1:$14))),ROW(A1)),COLUMN(A1),,8))

tiếp đến em dùng iferror để xóa lỗi thì nó mất hết luôn.

 

[akanepro]

cho hỏi đã làm ra nhưng nếu 1 phiếu xuất nhiều hơn 5 thì làm sao

Dạ demo là 5, có thể lên nhiều hơn, cám ơn anh/chị đã hỗ trợ em, em đã tự làm được rồi, em dùng vlookup kết hợp match và indirect.

 

[dazkangel]

Dạ demo là 5, có thể lên nhiều hơn, cám ơn anh/chị đã hỗ trợ em, em đã tự làm được rồi, em dùng vlookup kết hợp match và indirect.

vậy thì tốt quá.

 

[hangnguyen.namchau]

Minh có thêm 1 một công thức
=IFERROR(INDEX(OFFSET('BIÊN LAI'!$A$1,1,MATCH(H$2,'BIÊN LAI'!$A$1:$I$1,0)-1,COUNTA(MHV),1),SMALL(IF($B3=MHV,ROW(INDIRECT("1:"&COUNTA(MHV))),""),INT((COLUMN(A2)-1)/3+1))),"")
Mà mọi người cho mình hỏi cách phân tích công thức của mọi người như thế nào, vì nó dài mà có nhiều ngoặc kép làm mình khó phân tích.
Ngay cả công thức của em viết, xem lại cũng khó phân tích.

cảm ơn bạn nhiều nhiều!

 

[hangnguyen.namchau]

Góp vui thêm công thức để bạn tham khảo thêm:

H3=IFERROR(OFFSET(OFFSET('BIÊN LAI'!$A$1,,INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1)),MATCH(INT((COLUMN(A1)-1)/3)+1,INDEX(COUNTIF(OFFSET('BIÊN LAI'!$C$2,,,ROW($1:$15)),$B3),),0),),"")

Chỉ Enter, Fill xuống, rồi copy qua phải.

Chúc bạn ngày vui.

cảm ơn bạn nhiều nhiều!

 

[quocgiacan]

@quocgiacan chào anh, em lại có vài thắc mắc về kiến thức cơ bản, mong anh giúp đỡ:
như file sau: sao em dùng 1 mảng rồi kéo dài từ D13:K13 mà nó không ra nếu làm từng ô sẽ ra:

=TRIM(OFFSET('Tong hop'!$D$8,SMALL(IF(('Tong hop'!$C$9:$C$22=$K$1)*('Tong hop'!$D$9:$D$22=$K$2),(ROW($1:$14))),ROW(A1)),COLUMN(A1),,8))

tiếp đến em dùng iferror để xóa lỗi thì nó mất hết luôn.

Do OFFSET() chỉ mang về kết quả duy nhất của 1 ô, từ hàm ROW() và COLUMN() (đều là đơn ô), nên em chọn dãy ô để gán "Mảng từ trong bộ nhớ" mà lúc này trong bộ nhớ làm gì có mảng để trả ra kết quả cho em nên nó báo lỗi.

Ví dụ sau cho em hình dung được dễ:

• Cho A1-A5 bằng tuần tự: 1;2;3;4;5
• Đứng tại ô B1, rồi chọn B1:B5, sau đó em đánh: =INDEX(A1:A5*5,) nhấn Ctrl+Shift+Enter (CSE), sẽ hiện: B1-B5 theo tuần tự: 5;10;15;20;25
  • Tại sao phải có thêm hàm INDEX(,): nếu em đánh A1:A5*5 rồi nhấn CSE thì cũng được, nhưng khi em vào Evaluate Formula (Vd: ô B1) chỉ thấy thuần có 1 giá trị là 5. Nên anh muốn yêu cầu nó phải thể hiện "Mảng trong bộ nhớ" trước khi nó gán từng thành phần mảng vào ô cụ thể tương ứng với thành phần mảng, mà do em đã chọn cùng lúc B1:B5, tức TP.1 ứng vào ô B1, TP.2 ứng vào ô B2,..., TP.5 ứng vào ô B5. Nhờ có INDEX(...,) khi em vào Evaluate Formula em sẽ thấy hình dạng của Mảng bộ nhớ này. Nên nhớ: ô (Cell) không thể nào chứa kết quả của toàn bộ mảng, việc chọn cùng lúc dãy ô (địa chỉ cụ thể) rồi nhấn CSE nhằm để báo với excel em muốn gán từng thành phần mảng bộ nhớ vào ô tương ứng.
  • Vì vậy ở công thức OFFSET() em hỏi trên, nếu em không chọn cùng lúc dãy ô, mà nạp công thức vào ô riêng lẻ thì lại được, vì đúng nó chỉ có kết quả là 1 thành phần thôi mà.
• Tóm lại, việc nhấn CSE cho công thức mảng có 2 tác dụng:
  • Để khai báo nhập vào cho excel biết trong công thức hàm có sử dụng Vùng/Mảng tại các vị trí đối số của công thức hàm không phải là đối số: Array (Mảng).
  • Để khai báo trả ra cho excel biết hãy lấy từng thành phần mảng tạo ra bởi công thức gán vào từng địa chỉ ô cụ thể cho Vùng mà em đã chọn trước, với điều kiện: kết quả của công thức phải ra là 1 mảng.

Chúc em ngày vui.

 

[dazkangel]

Do OFFSET() chỉ mang về kết quả duy nhất của 1 ô, từ hàm ROW() và COLUMN() (đều là đơn ô), nên em chọn dãy ô để gán "Mảng từ trong bộ nhớ" mà lúc này trong bộ nhớ làm gì có mảng để trả ra kết quả cho em nên nó báo lỗi.

Ví dụ sau cho em hình dung được dễ:

• Cho A1-A5 bằng tuần tự: 1;2;3;4;5
• Đứng tại ô B1, rồi chọn B1:B5, sau đó em đánh: =INDEX(A1:A5*5,) nhấn Ctrl+Shift+Enter (CSE), sẽ hiện: B1-B5 theo tuần tự: 5;10;15;20;25
  • Tại sao phải có thêm hàm INDEX(,): nếu em đánh A1:A5*5 rồi nhấn CSE thì cũng được, nhưng khi em vào Evaluate Formula (Vd: ô B1) chỉ thấy thuần có 1 giá trị là 5. Nên anh muốn yêu cầu nó phải thể hiện "Mảng trong bộ nhớ" trước khi nó gán từng thành phần mảng vào ô cụ thể tương ứng với thành phần mảng, mà do em đã chọn cùng lúc B1:B5, tức TP.1 ứng vào ô B1, TP.2 ứng vào ô B2,..., TP.5 ứng vào ô B5. Nhờ có INDEX(...,) khi em vào Evaluate Formula em sẽ thấy hình dạng của Mảng bộ nhớ này. Nên nhớ: ô (Cell) không thể nào chứa kết quả của toàn bộ mảng, việc chọn cùng lúc dãy ô (địa chỉ cụ thể) rồi nhấn CSE nhằm để báo với excel em muốn gán từng thành phần mảng bộ nhớ vào ô tương ứng.
  • Vì vậy ở công thức OFFSET() em hỏi trên, nếu em không chọn cùng lúc dãy ô, mà nạp công thức vào ô riêng lẻ thì lại được, vì đúng nó chỉ có kết quả là 1 thành phần thôi mà.
• Tóm lại, việc nhấn CSE cho công thức mảng có 2 tác dụng:
  • Để khai báo nhập vào cho excel biết trong công thức hàm có sử dụng Vùng/Mảng tại các vị trí đối số của công thức hàm không phải là đối số: Array (Mảng).
  • Để khai báo trả ra cho excel biết hãy lấy từng thành phần mảng tạo ra bởi công thức gán vào từng địa chỉ ô cụ thể cho Vùng mà em đã chọn trước, với điều kiện: kết quả của công thức phải ra là 1 mảng.

Chúc em ngày vui.

Tại em có lần dùng offset trả ra cả mảng mà nó ra anh ạ @@

 

[quocgiacan]

Tại em có lần dùng offset trả ra cả mảng mà nó ra anh ạ @@

Như anh nói trên: chỉ khi nào kết quả ra cả mảng thì việc chọn dãy ô và nhấn CSE thì mới thực hiện được.

Vd: OFFSET() có thể trả ra địa chỉ 1 ô hoặc địa chỉ 1 Vùng, thì chỉ có kết quả Vùng em mới làm được chuyện gán vào dãy ô tương ứng. Cũng ví dụ: A1-A5=1;2;3;4;5. Chọn D1: D5 Gõ D1=INDEX(OFFSET(A1,,,ROW(1:5)),) nhấn CSE sẽ ra được kết quả, nhưng nếu =OFFSET(A1,,,ROW(1:5)) nhấn CSE thì báo lỗi.
Hoặc công thức sau để em vào Evaluate Formula xem dễ hơn: D1: D5=INDEX(N(OFFSET(A1,ROW(1:5)-1,)),) CSE.

Chúc em ngày vui.

 

[dazkangel]

Như anh nói trên: chỉ khi nào kết quả ra cả mảng thì việc chọn dãy ô và nhấn CSE thì mới thực hiện được.

Vd: OFFSET() có thể trả ra địa chỉ 1 ô hoặc địa chỉ 1 Vùng, thì chỉ có kết quả Vùng em mới làm được chuyện gán vào dãy ô tương ứng. Cũng ví dụ: A1-A5=1;2;3;4;5. Chọn D1: D5 Gõ D1=INDEX(OFFSET(A1,,,ROW(1:5)),) nhấn CSE sẽ ra được kết quả, nhưng nếu =OFFSET(A1,,,ROW(1:5)) nhấn CSE thì báo lỗi.
Hoặc công thức sau để em vào Evaluate Formula xem dễ hơn: D1: D5=INDEX(N(OFFSET(A1,ROW(1:5)-1,)),) CSE.

Chúc em ngày vui.

dạ để em xem lại, chúc anh ngày vui