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 (1 người xem)

[hangnguyen.namchau]

Em nhờ các Anh Chị giúp em công thức để em có thể từ sheet Biên lai mà qua được sheet Tổng Hợp để theo dõi các lần đóng tiền của từng học viên, mỗi học viên đều có 1 mã nhưng có nhiều lần đóng tiền khác nhau.
cảm ơn các Anh chị nhiều nhiều ạ!

 

[eke_rula]

Em nhờ các Anh Chị giúp em công thức để em có thể từ sheet Biên lai mà qua được sheet Tổng Hợp để theo dõi các lần đóng tiền của từng học viên, mỗi học viên đều có 1 mã nhưng có nhiều lần đóng tiền khác nhau.
cảm ơn các Anh chị nhiều nhiều ạ!

Bạn dùng CT mảng này ở H3:

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

Ctrl+Shift+Enter fill xuống, rồi sang phải!!!
Định dạng lại mấy cột ngày của bạn nhe bạn!!!

 

[hangnguyen.namchau]

Bạn dùng CT mảng này ở H3:

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

Ctrl+Shift+Enter fill xuống, rồi sang phải!!!
Định dạng lại mấy cột ngày của bạn nhe bạn!!!

Cảm ơn bạn nhiều nhiều nha!
Mình làm được rồi!

 

[quocgiacan]

Em nhờ các Anh Chị giúp em công thức để em có thể từ sheet Biên lai mà qua được sheet Tổng Hợp để theo dõi các lần đóng tiền của từng học viên, mỗi học viên đều có 1 mã nhưng có nhiều lần đóng tiền khác nhau.
cảm ơn các Anh chị nhiều nhiều ạ!

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.

 

[Lê Duy Thương]

Thay vì làm 1 cột phụ thì không cần mấy cái CT loằng ngoằng kia . có thời gian nhâm nhi vài ve.
kha kha

 

[quocgiacan]

Thay vì làm 1 cột phụ thì không cần mấy cái CT loằng ngoằng kia . có thời gian nhâm nhi vài ve.
kha kha

Lời nhận định này là chân lý.
Lên nhà Bác SA liền!!!

 

[HieuCD]

Bạn dùng CT mảng này ở H3:

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

Ctrl+Shift+Enter fill xuống, rồi sang phải!!!
Định dạng lại mấy cột ngày của bạn nhe bạn!!!

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ông thức quá hay, vừa xử cột và dòng không theo thứ tự
Chúc các bạn ngũ ngon

 

[Mr Joker]

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.

Chào ANH/ CHỊ! em cũng có 1 vấn đề tương tự muốn nhờ các ANH/CHị giúp em với được không ạ
Em lại hơi ngược lại chút xíu là em có 2 sheet 1 (TONG HOP) và sheet 2, bên sheet 1 đã có sẵn dữ liệu.
Tại sheet 2!B1, giờ em muốn viết 1 hàm tìm kiếm giá trị sheet2!A1 (ở đây cụ thể giá trị ô A1 là 1) tham chiếu sang bên cột A sheet 1, tìm ra ô nào chứa giá trị như sheet2!A1 rồi xong phải thêm 1 điều kiện là : ô tương ứng theo hàng ở cột G của ô tìm được đó không được chứa "X" ( nếu chứa "X" thì bỏ qua, tìm tiếp những ô ở cột A khác cho đến khi có 1 ô phù hợp, do yêu cầu của dữ liệu em nhập vào nên nếu có nhiều ô có cùng giá trị ở cột A thì tại mọi thời điểm sẽ luôn chỉ có 1 ô mà ô tương ứng của nó ở cột G là không có chứa "X" , vì vậy nên giá trị tìm kiếm phù hợp được các điều kiện trên luôn là chỉ tìm được duy nhất), xong thì giá trị ô sheet2!B1 sẽ bằng ô tương ứng ở cột B của ô tìm được kia.
Đáp ứng đủ 2 điều kiện trên là ô có giá trị = Sheet2!A1 = 1 và có ô tương ứng ở cột G không chứa "X" chỉ có một chính là ô Sheet1!A3, vậy thì giá trị Sheet2!B1 = giá trị Sheet1!B3.
Tương tự với các ô Sheet2!C1 ; Sheet2!D1 ; Sheet2!E1 ; Sheet2!F1
Cụ thể anh chị xem file giúp em với ạ.

 

[be_09]

Lời nhận định này là chân lý.
Lên nhà Bác SA liền!!!

Vậy 10 giờ ngày hôm nay (16/7/2017) ghé nhà bác SA nè.

Dám không?????

 

[be_09]

Em nhờ các Anh Chị giúp em công thức để em có thể từ sheet Biên lai mà qua được sheet Tổng Hợp để theo dõi các lần đóng tiền của từng học viên, mỗi học viên đều có 1 mã nhưng có nhiều lần đóng tiền khác nhau.
cảm ơn các Anh chị nhiều nhiều ạ!

Theo dõi thu học phí gì mà phức tạp quá, tham khảo cái hình, để có định hướng in biên lai cho người đóng, sau khi in xong thì lưu dữ liệu vào sheet Theo dõi.



Muốn biết một học viên đóng bao nhiêu lần hoặc tháng nào còn nợ thì thêm 1 sheet lọc (dựa vào mã học viên để lọc).

 

[dazkangel]

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.

những bài công thức nhiều thế này thật đắn đo @@

 

[dazkangel]

Chào ANH/ CHỊ! em cũng có 1 vấn đề tương tự muốn nhờ các ANH/CHị giúp em với được không ạ
Em lại hơi ngược lại chút xíu là em có 2 sheet 1 (TONG HOP) và sheet 2, bên sheet 1 đã có sẵn dữ liệu.
Tại sheet 2!B1, giờ em muốn viết 1 hàm tìm kiếm giá trị sheet2!A1 (ở đây cụ thể giá trị ô A1 là 1) tham chiếu sang bên cột A sheet 1, tìm ra ô nào chứa giá trị như sheet2!A1 rồi xong phải thêm 1 điều kiện là : ô tương ứng theo hàng ở cột G của ô tìm được đó không được chứa "X" ( nếu chứa "X" thì bỏ qua, tìm tiếp những ô ở cột A khác cho đến khi có 1 ô phù hợp, do yêu cầu của dữ liệu em nhập vào nên nếu có nhiều ô có cùng giá trị ở cột A thì tại mọi thời điểm sẽ luôn chỉ có 1 ô mà ô tương ứng của nó ở cột G là không có chứa "X" , vì vậy nên giá trị tìm kiếm phù hợp được các điều kiện trên luôn là chỉ tìm được duy nhất), xong thì giá trị ô sheet2!B1 sẽ bằng ô tương ứng ở cột B của ô tìm được kia.
Đáp ứng đủ 2 điều kiện trên là ô có giá trị = Sheet2!A1 = 1 và có ô tương ứng ở cột G không chứa "X" chỉ có một chính là ô Sheet1!A3, vậy thì giá trị Sheet2!B1 = giá trị Sheet1!B3.
Tương tự với các ô Sheet2!C1 ; Sheet2!D1 ; Sheet2!E1 ; Sheet2!F1
Cụ thể anh chị xem file giúp em với ạ.

Bạn nên tạo 1 bài mới sẽ được nhiều người chú ý hơn

A1=INDEX('TONG HOP'!A$1:A$3,MATCH(TRUE,'TONG HOP'!$G$1:$G$3<>"X",0))

 

[Mr Joker]

Bạn nên tạo 1 bài mới sẽ được nhiều người chú ý hơn

A1=INDEX('TONG HOP'!A$1:A$3,MATCH(TRUE,'TONG HOP'!$G$1:$G$3<>"X",0))

Dạ chào bạn dazkangel !
cảm ơn bạn đã trả lời vấn đề của mình
Mình đã dùng công thức này cho ô A1 sheet 2, nhưng ko hiểu sao kết quả báo lỗi #N/A bạn à. Bạn giúp mình với nhé. mà giả sử mình muốn đánh vào ô A1 sheet 2 giá trị là 1, thì hàm sẽ tự động tìm giá trị này bên cột A sheet 1, nếu giá trị nào trùng + đồng thời ô tương ứng ở cột G không có "X" thì sẽ tham chiếu lấy lần lượt :
Sheet2! B1 = Sheet1! B1 ; Sheet2! C1 = Sheet1! C1;...

 

[huonglien1901]

Dạ chào bạn dazkangel !
cảm ơn bạn đã trả lời vấn đề của mình
Mình đã dùng công thức này cho ô A1 sheet 2, nhưng ko hiểu sao kết quả báo lỗi #N/A bạn à. Bạn giúp mình với nhé

Bạn đưa cái file lỗi dùng xông thức lên đi,

 

[Mr Joker]

Bạn đưa cái file lỗi dùng xông thức lên đi,

Chào bạn! mình đã upload lại file lên, phiền bạn xem giúp mình nhé, mình cảm ơn

 

[huonglien1901]

Chào bạn! mình đã upload lại file lên, phiền bạn xem giúp mình nhé, mình cảm ơn

File đó lỗi vì đây là công thức mảng nên phải nhấn Ctrl+shift+Enter đó,
Bạn làm thử đi

 

[Mr Joker]

File đó lỗi vì đây là công thức mảng nên phải nhấn Ctrl+shift+Enter đó,
Bạn làm thử đi[/QUOMìn

Mình cho chạy được công thức rồi, nhưng mà chưa giống nhu cầu của mình lắm, hoặc là mình gà mờ quá chưa biết áp dụng
Ý mình là cột A sheet 2 để nhập dữ liệu vào, sau đó mới dùng dữ liệu này tìm kiếm dữ liệu trùng bên cột A sheet 1 + điều kiện ô ở cột G tương ứng không chứa "X" , ví dụ nhập dữ liệu cho ô A1 sheet 2, tìm được ô A1 sheet 1 phù hợp vì A1 sheet 2 = A1 sheet 1 và G1 sheet 1 không chứa "X"
Sau đó thì mới lấy dữ liệu Sheet2! B1 = Sheet1! B1 ; Sheet2! C1 = Sheet1! C1;...

 

[dazkangel]

Mình cho chạy được công thức rồi, nhưng mà chưa giống nhu cầu của mình lắm, hoặc là mình gà mờ quá chưa biết áp dụng
Ý mình là cột A sheet 2 để nhập dữ liệu vào, sau đó mới dùng dữ liệu này tìm kiếm dữ liệu trùng bên cột A sheet 1 + điều kiện ô ở cột G tương ứng không chứa "X" , ví dụ nhập dữ liệu cho ô A1 sheet 2, tìm được ô A1 sheet 1 phù hợp vì A1 sheet 2 = A1 sheet 1 và G1 sheet 1 không chứa "X"
Sau đó thì mới lấy dữ liệu Sheet2! B1 = Sheet1! B1 ; Sheet2! C1 = Sheet1! C1;...

Như đã nói bạn nên tạo 1 bài mói hoặc nêu rõ cụ thể hơn, còn nếu ở đây bạn nên cho 1 file có ví dụ đa dạng và nhiều trường hợp để có hướng giải quyết chính xác hơn.
Còn công thức bạn phải nhấn Ctrl+Shift+Enter mới ra được

 

[Mr Joker]

Như đã nói bạn nên tạo 1 bài mói hoặc nêu rõ cụ thể hơn, còn nếu ở đây bạn nên cho 1 file có ví dụ đa dạng và nhiều trường hợp để có hướng giải quyết chính xác hơn.
Còn công thức bạn phải nhấn Ctrl+Shift+Enter mới ra được

Mình lập lại 1 ví dụ cụ thể hơn bạn vui lòng xem giúp mình nhé!
Miêu tả yêu cầu cụ thể mình viết trong file
Cảm ơn bạn!

 

[dazkangel]

Mình lập lại 1 ví dụ cụ thể hơn bạn vui lòng xem giúp mình nhé!
Miêu tả yêu cầu cụ thể mình viết trong file
Cảm ơn bạn!

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

 

[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

 

[quocgiacan]

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

Công thức của em muốn ra kết quả phải sửa như sau:
Chọn D13: K17 nhập công thức:

D13=INDEX(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(1:5)),COLUMN(A1:H1)),)

Ctrl+Shift+Enter (CSE).

Tức với ROW(1:5) (5 giá trị đầu của hàng thỏa điều kiện) và COLUMN(A1:H1) báo cho OFFSET() trả về 1 mảng 2 chiều: 5x8 ứng với Vùng D13:K17.

Và INDEX(...,) sử dụng ở đây không phải "Né" nhấn CSE, mà là hiển thị cho ra giá trị của mảng này.

Chúc em ngày vui.

 

[dazkangel]

Công thức của em muốn ra kết quả phải sửa như sau:
Chọn D13: K17 nhập công thức:

D13=INDEX(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(1:5)),COLUMN(A1:H1)),)

Ctrl+Shift+Enter.

Tức với ROW(1:5) (5 giá trị đầu của hàng thỏa điều kiện) và COLUMN(A1:H1) báo cho OFFSET() trả về 1 mảng 2 chiều: 5x8 ứng với Vùng D13:K17.

Chúc em ngày vui.

cảm ơn anh, em sẽ kiểm tra sau, em hiện chơi luôn cái index cho lành ^^ và trả về một mảng 2 chiều luôn.
chúc anh buổi tối vui vẻ

 

[Drghost]

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),),"")
Nhờ anh Quocgiacan giải thích rõ hơn từng thành phần trong công thúc giúp em được không. Em cũng có một biểu mẫu gần giống thế này mà áp dụng không được.
Em chưa rõ chỗ {0,8,1} lấy ở đâu và cả chỗ (COLUMN(A1)-1)/3 này nữa
Cảm ơn anh

 

[quocgiacan]

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),),"")
Nhờ anh Quocgiacan giải thích rõ hơn từng thành phần trong công thúc giúp em được không. Em cũng có một biểu mẫu gần giống thế này mà áp dụng không được.
Em chưa rõ chỗ {0,8,1} lấy ở đâu và cả chỗ (COLUMN(A1)-1)/3 này nữa
Cảm ơn anh

Hỏi: Em chưa rõ chỗ {0,8,1} lấy ở đâu

• Đó là số tương ứng với các cột "MBL"-"SỐ TIỀN"-"NGÀY BL" của Sheet "BienLai". Các số này là kết quả trả về trong đoạn công thức: INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1) bên sheet "TỔNG HỢP", tức cột đầu (Cột H) trả về 0, khi kéo công thức qua Cột I trả về 8, kéo qua Cột J trả về số 1. Các số này báo về cho hàm OFFSET('BIÊN LAI'!$A$1,,INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1)) để lấy các vị trí bên sheet "BienLai" các ô A1 (ứng cột 1), ô I1 (ứng cột 2), ô B1 (ứng cột 3). Rồi tuần tự khi qua cột K (thuộc Lần 2) thì trả về cột 1,2,3 tức lại trả về kết quả 0-8-1, việc được vậy là nhờ MOD(COLUMN(A1)-1,3)+1, hàm này cứ mỗi lần kéo qua các cột: bắt đầu từ cột H nó ra 1, qua cột I nó ra 2, qua cột J nó ra 3, qua cột K nó trả về đầu tức ra 1, qua cột L nó ra 2, qua cột M nó ra 3, cứ trở đi trở lại.

Hỏi: chỗ (COLUMN(A1)-1)/3 này nữa

• Riêng INT((COLUMN(A1)-1)/3)+1 nó trả về tuần tự 3 cột ra chung 1 số: 3 cột đầu (H-I-J) ra số 1 (ứng với Lần 1), 3 cột kế (K-L-M) ra số 2 (ứng với Lần 2)...., và tuần tự như vậy tương ứng với các Lần: 1,2,3,4,5,6.....Nó cung cấp 'giá trị cần tìm' cho hàm: MATCH( INT((COLUMN(A1)-1)/3)+1 , INDEX(COUNTIF(OFFSET('BIÊN LAI'!$C$2,,,ROW($1:$15)),$B3),) , 0). Bạn có thể hình dung công thức khi nó thực hiện là: MATCH ( 1 , {1;1;1;1;1;1;2;2;2;2;3;3;3;3;3;4;4} ,0), nó so khớp giá trị 1 (tương ứng Lần 1) hiện có dòng bao nhiêu thì sẽ nhảy đến ô đó tính từ hoặc A1, hoặc I1, hoặc B1 (Do kết quả như lời giải thích trên trả về), ví dụ: cho mã đầu tiên NC1701 thì sheet BienLai có dữ liệu của mã này tại các dòng 1 (Lần 1), dòng 7 (Lần 2).

Hơi khó hình dung nhưng hy vọng bạn đọc chậm và trích từng đoạn công thức ra thử sẽ hiểu.

Chúc bạn ngày vui.

 

[Drghost]

Hỏi: Em chưa rõ chỗ {0,8,1} lấy ở đâu

• Đó là số tương ứng với các cột "MBL"-"SỐ TIỀN"-"NGÀY BL" của Sheet "BienLai". Các số này là kết quả trả về trong đoạn công thức: INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1) bên sheet "TỔNG HỢP", tức cột đầu (Cột H) trả về 0, khi kéo công thức qua Cột I trả về 8, kéo qua Cột J trả về số 1. Các số này báo về cho hàm OFFSET('BIÊN LAI'!$A$1,,INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1)) để lấy các vị trí bên sheet "BienLai" các ô A1 (ứng cột 1), ô I1 (ứng cột 2), ô B1 (ứng cột 3). Rồi tuần tự khi qua cột K (thuộc Lần 2) thì trả về cột 1,2,3 tức lại trả về kết quả 0-8-1, việc được vậy là nhờ MOD(COLUMN(A1)-1,3)+1, hàm này cứ mỗi lần kéo qua các cột: bắt đầu từ cột H nó ra 1, qua cột I nó ra 2, qua cột J nó ra 3, qua cột K nó trả về đầu tức ra 1, qua cột L nó ra 2, qua cột M nó ra 3, cứ trở đi trở lại.

Hỏi: chỗ (COLUMN(A1)-1)/3 này nữa

• Riêng INT((COLUMN(A1)-1)/3)+1 nó trả về tuần tự 3 cột ra chung 1 số: 3 cột đầu (H-I-J) ra số 1 (ứng với Lần 1), 3 cột kế (K-L-M) ra số 2 (ứng với Lần 2)...., và tuần tự như vậy tương ứng với các Lần: 1,2,3,4,5,6.....Nó cung cấp 'giá trị cần tìm' cho hàm: MATCH( INT((COLUMN(A1)-1)/3)+1 , INDEX(COUNTIF(OFFSET('BIÊN LAI'!$C$2,,,ROW($1:$15)),$B3),) , 0). Bạn có thể hình dung công thức khi nó thực hiện là: MATCH ( 1 , {1;1;1;1;1;1;2;2;2;2;3;3;3;3;3;4;4} ,0), nó so khớp giá trị 1 (tương ứng Lần 1) hiện có dòng bao nhiêu thì sẽ nhảy đến ô đó tính từ hoặc A1, hoặc I1, hoặc B1 (Do kết quả như lời giải thích trên trả về), ví dụ: cho mã đầu tiên NC1701 thì sheet BienLai có dữ liệu của mã này tại các dòng 1 (Lần 1), dòng 7 (Lần 2).

Hơi khó hình dung nhưng hy vọng bạn đọc chậm và trích từng đoạn công thức ra thử sẽ hiểu.

Chúc bạn ngày vui.

Cảm ơn anh đã phân tích giúp em.
Em đưa file mẫu của em lên nhờ anh và mọi người giúp đỡ em làm hoài không được

 

[quocgiacan]

Cảm ơn anh đã phân tích giúp em.
Em đưa file mẫu của em lên nhờ anh và mọi người giúp đỡ em làm hoài không được

Bạn xem file đính kèm.

Chúc bạn ngày vui.

 

[HieuCD]

Cảm ơn anh đã phân tích giúp em.
Em đưa file mẫu của em lên nhờ anh và mọi người giúp đỡ em làm hoài không được

Bạn xem file
Dùng Table chi cho nặng file

 

[Drghost]

Bạn xem file
Dùng Table chi cho nặng file

Em cảm ơn 2 anh Quocgiacan và HieuCD đã giúp đỡ em