Dòng tham chiếu bị trùng (1 người xem)

[Emyeuexcel]

Chào anh chị em PGE,

Mình có file kèm, mình không muốn tạo cột phụ cho những dòng bị trùng tham chiếu. Vậy có công thức nào để làm mà ko cần tạo cột này vẫn cho kết quả chính xác.
Mong anh chị giúp đỡ!
Mình chân thành cảm ơn!

 

[CHAOQUAY]

Chào anh chị em PGE,

Mình có file kèm, mình không muốn tạo cột phụ cho những dòng bị trùng tham chiếu. Vậy có công thức nào để làm mà ko cần tạo cột này vẫn cho kết quả chính xác.
Mong anh chị giúp đỡ!
Mình chân thành cảm ơn!

Dùng tạm công thức mảng

K5=INDEX(F$1:F$11,SMALL(IF($E$5:$E$11=$J5,ROW($E$5:$E$11),""),COUNTIF($J$5:J5,$J5)))

 

[Emyeuexcel]

Dùng tạm công thức mảng

K5=INDEX(F$1:F$11,SMALL(IF($E$5:$E$11=$J5,ROW($E$5:$E$11),""),COUNTIF($J$5:J5,$J5)))

Chào bạn,
Nếu bảng mình cần lấy chỉ lấy 1 số cột ko liên tiếp nhau so với bảng dữ liệu thì công thức này nó ko hiểu.
Vậy bạn có cách nào ko?
Mình đính kèm lại file ví dụ.
Cảm ơn bạn giúp cho!

 

[CHAOQUAY]

Chào bạn,
Nếu bảng mình cần lấy chỉ lấy 1 số cột ko liên tiếp nhau so với bảng dữ liệu thì công thức này nó ko hiểu.
Vậy bạn có cách nào ko?
Mình đính kèm lại file ví dụ.
Cảm ơn bạn giúp cho!

Công thức mảng

R5=INDEX($F$1:$J$11,SMALL(IF($E$5:$E$11=$Q5,ROW($E$5:$E$11),""),COUNTIF($Q$5:Q5,$Q5)),MATCH(R$4,$F$4:$J$4,0))

 

[huonglien1901]

Chào anh chị em PGE,

Mình có file kèm, mình không muốn tạo cột phụ cho những dòng bị trùng tham chiếu. Vậy có công thức nào để làm mà ko cần tạo cột này vẫn cho kết quả chính xác.
Mong anh chị giúp đỡ!
Mình chân thành cảm ơn!

mình góp vui!

=INDIRECT(CHAR(65+COLUMN(F1)-1)&AGGREGATE(15,6,ROW($F$5:$F$11)/($E$5:$E$11=$J5),COUNTIF($E$5:$E5,$E5)))

kéo sang phải rồi fill xuống!

 

[Emyeuexcel]

Công thức mảng

R5=INDEX($F$1:$J$11,SMALL(IF($E$5:$E$11=$Q5,ROW($E$5:$E$11),""),COUNTIF($Q$5:Q5,$Q5)),MATCH(R$4,$F$4:$J$4,0))

Chào bạn,

Cảm ơn bạn vì công thức quá hay!
Mình thắc mắc chỗ COUNTIF($Q$5:Q5. Cái này bạn ko cố định cột Q5, cho nó động, nhưng mình ko hiểu vì sao cho động, và mình thử cố định $Q5 thì kết quả vẫn đúng. Không biết mình làm vậy có được không và nó có đúng cho mọi trường hợp hay chỉ đúng cho bài này thôi. Mong bạn chỉ giáo giúp!

Bài đã được tự động gộp: 13/5/20

mình góp vui!

=INDIRECT(CHAR(65+COLUMN(F1)-1)&AGGREGATE(15,6,ROW($F$5:$F$11)/($E$5:$E$11=$J5),COUNTIF($E$5:$E5,$E5)))

kéo sang phải rồi fill xuống!

Chào bạn,

Công thức bạn cũng rất hay cho trường hợp này. Còn khi mình lấy cột không liên tiếp thì nó ko đúng.
Cảm ơn bạn đã cho mình biết thêm nhiều cái về excel!

 

[huonglien1901]

Chào bạn,

Cảm ơn bạn vì công thức quá hay!
Mình thắc mắc chỗ COUNTIF($Q$5:Q5. Cái này bạn ko cố định cột Q5, cho nó động, nhưng mình ko hiểu vì sao cho động, và mình thử cố định $Q5 thì kết quả vẫn đúng. Không biết mình làm vậy có được không và nó có đúng cho mọi trường hợp hay chỉ đúng cho bài này thôi. Mong bạn chỉ giáo giúp!

Bài đã được tự động gộp: 13/5/20

Chào bạn,

Công thức bạn cũng rất hay cho trường hợp này. Còn khi mình lấy cột không liên tiếp thì nó ko đúng.
Cảm ơn bạn đã cho mình biết thêm nhiều cái về excel!

Bạn thử gửi file lên chỗ nào không đúng. Mình chỉ làm theo ví dụ thôi. Thấy ra kết quả giống bạn mà.

 

[Emyeuexcel]

Bạn thử gửi file lên chỗ nào không đúng. Mình chỉ làm theo ví dụ thôi. Thấy ra kết quả giống bạn mà.

Chào bạn,
Mình ko nói là công thức bạn ko đúng, ý mình là mình thì cho cố định cột của countif, còn bạn thì cho động, khi kéo sang cột khác thì nó quét qua 1 cột nữa, or vùng. 2 kết quả vẫn như nhau.
Mình muốn hỏi thêm là dùng công thức mảng nó chạy chậm, nặng lắm phải không? Vì mình áp dụng vào thực tế file của mình đang làm thì nó chạy rất lâu.
File mình có nhiều dòng tham chiếu đến 1 file dữ liệu khác có nhiều dòng, nhiều cột, lưu ở thư mục khác. Mà file dữ liệu này thì được cập nhật thêm dòng từng ngày, mội ngày mình phải cập nhật thêm tham chiếu cho giống với bên file dữ liệu để lấy dữ liệu bên file kia.

 

[khuyenbachmao]

Chào anh chị em PGE,

Mình có file kèm, mình không muốn tạo cột phụ cho những dòng bị trùng tham chiếu. Vậy có công thức nào để làm mà ko cần tạo cột này vẫn cho kết quả chính xác.
Mong anh chị giúp đỡ!
Mình chân thành cảm ơn!

Excel 2016 có Countifs, Sumifs hoàn toàn đáp ứng nhu cầu của bạn nhé, nếu không trong countif, sumif bạn nối chuỗi tạo sự khác biệt là được.

 

[Emyeuexcel]

Excel 2016 có Countifs, Sumifs hoàn toàn đáp ứng nhu cầu của bạn nhé, nếu không trong countif, sumif bạn nối chuỗi tạo sự khác biệt là được.

Chào bạn,
Mình cũng ko giỏi logic cho lắm, bạn giúp mình theo cách bạn gợi ý với. Trên file ví dụ mình post ở trên.
Công thức mảng thì hay, giải quyết được ý của mình nhưng nặng quá.
Mình cảm ơn bạn nhiều!

 

[khuyenbachmao]

Chào bạn,
Mình cũng ko giỏi logic cho lắm, bạn giúp mình theo cách bạn gợi ý với. Trên file ví dụ mình post ở trên.
Công thức mảng thì hay, giải quyết được ý của mình nhưng nặng quá.
Mình cảm ơn bạn nhiều!

Bản chất nội dung bạn mong muốn chỉ là tìm

Chào bạn,
Mình cũng ko giỏi logic cho lắm, bạn giúp mình theo cách bạn gợi ý với. Trên file ví dụ mình post ở trên.
Công thức mảng thì hay, giải quyết được ý của mình nhưng nặng quá.
Mình cảm ơn bạn nhiều!

Mục tiêu của bạn là tìm điểm với tên và năm sinh --> sumifs(cột điểm, cột tên, ô tên đầu vào, cột năm, ô năm đầu vào)

VD: bản tính tính A1

Ten	SN	Giới tính	Điem	Hanh kiem	Ket qua
A	1970​	Nam	10​	Tot	len lop
D	1967​	Nu	9​	Kha	thi lai
B	1971​	Nu	5​	Tot	o lai
a	1972​	Nam	7​	Tot	len lop
D	1973​	Nam	8​	Kha	thi lai
D	1974​	Nam	3​	Kha	len lop
E	1975​	Nu	6​	Tot	o lai

H1="D", I1="1973 --> =SUMIFS($D$2:$D$8,$A$2:$A$8,H1,$B$2:$B$8,I1)

Theo ý của bạn là mã phụ thì bạn chỉ cần cuối bảng tạo 1 cột = A1 & B1 là xong --> từ đây bạn lookup dữ liệu từ chỗ khác lúc nào cũng được. Công thức lúc đó thay đổi thành Index(range, match(), match()). Dùng Vlookup thì mã phụ để cột đầu

 

[Emyeuexcel]

Bản chất nội dung bạn mong muốn chỉ là tìm


Mục tiêu của bạn là tìm điểm với tên và năm sinh --> sumifs(cột điểm, cột tên, ô tên đầu vào, cột năm, ô năm đầu vào)

VD: bản tính tính A1

Ten	SN	Giới tính	Điem	Hanh kiem	Ket qua
A	1970​	Nam	10​	Tot	len lop
D	1967​	Nu	9​	Kha	thi lai
B	1971​	Nu	5​	Tot	o lai
a	1972​	Nam	7​	Tot	len lop
D	1973​	Nam	8​	Kha	thi lai
D	1974​	Nam	3​	Kha	len lop
E	1975​	Nu	6​	Tot	o lai

H1="D", I1="1973 --> =SUMIFS($D$2:$D$8,$A$2:$A$8,H1,$B$2:$B$8,I1)

Theo ý của bạn là mã phụ thì bạn chỉ cần cuối bảng tạo 1 cột = A1 & B1 là xong --> từ đây bạn lookup dữ liệu từ chỗ khác lúc nào cũng được. Công thức lúc đó thay đổi thành Index(range, match(), match()). Dùng Vlookup thì mã phụ để cột đầu

Ý mình là ko cần dùng cột phụ mà. (cho cả bảng tham chiếu và bảng kết quả)
Có cột phụ thì mình làm được rồi.

 

[khuyenbachmao]

Ý mình là ko cần dùng cột phụ mà. (cho cả bảng tham chiếu và bảng kết quả)
Có cột phụ thì mình làm được rồi.

Mình có cần cột phụ đâu --> Bạn chỉ cần có 2 điều kiện tên và năm trong công thức sumifs là giải quyết nội dung. Cột phụ được sử dụng trong những vấn đề khác bạn nhé

 

[Emyeuexcel]

Mình có cần cột phụ đâu --> Bạn chỉ cần có 2 điều kiện tên và năm trong công thức sumifs là giải quyết nội dung. Cột phụ được sử dụng trong những vấn đề khác bạn nhé

Dùng sumifs, mà 2 điều kiện tham chiếu đều có nhiều dòng trùng, thì sao cho kết quả chính xác được vậy bạn? Ví dụ : có 2 cái A, 3 cái D, rồi năm cũng trùng. Do ví dụ mình thì năm ko trùng, nhưng khi đưa vào thực tế các data khác thì 2 điều kiện này có trùng. Mà dữ liệu cần lấy thì nhiều cột khác nữa. Số dòng thì có khi lên cả 1000 dòng.
Mình tâm đắc công thức mảng dùng SMALL(IF- Nhưng mảng nó làm chậm file quá.
Ko biết bạn có cách nào ko dùng mảng mà giải quyết đc khi dùng SMALL ko?
Cảm ơn bạn rất nhiều!

 

[huonglien1901]

Dùng sumifs, mà 2 điều kiện tham chiếu đều có nhiều dòng trùng, thì sao cho kết quả chính xác được vậy bạn? Ví dụ : có 2 cái A, 3 cái D, rồi năm cũng trùng. Do ví dụ mình thì năm ko trùng, nhưng khi đưa vào thực tế các data khác thì 2 điều kiện này có trùng. Mà dữ liệu cần lấy thì nhiều cột khác nữa. Số dòng thì có khi lên cả 1000 dòng.
Mình tâm đắc công thức mảng dùng SMALL(IF- Nhưng mảng nó làm chậm file quá.
Ko biết bạn có cách nào ko dùng mảng mà giải quyết đc khi dùng SMALL ko?
Cảm ơn bạn rất nhiều!

Hàm small(if tương ứng aggregate (15,6...) đó bạn.

 

[khuyenbachmao]

Dùng sumifs, mà 2 điều kiện tham chiếu đều có nhiều dòng trùng, thì sao cho kết quả chính xác được vậy bạn? Ví dụ : có 2 cái A, 3 cái D, rồi năm cũng trùng. Do ví dụ mình thì năm ko trùng, nhưng khi đưa vào thực tế các data khác thì 2 điều kiện này có trùng. Mà dữ liệu cần lấy thì nhiều cột khác nữa. Số dòng thì có khi lên cả 1000 dòng.
Mình tâm đắc công thức mảng dùng SMALL(IF- Nhưng mảng nó làm chậm file quá.
Ko biết bạn có cách nào ko dùng mảng mà giải quyết đc khi dùng SMALL ko?
Cảm ơn bạn rất nhiều!

Bạn nên cân nhắc vấn đề này nhé. Nếu có trùng thì công thức nào cũng không thể giải quyết được. VD trong trường có 2 học sinh trùng tên, tuổi, năm sinh, v.v. Vậy phân biệt thế nào? Rút cuộc cũng phải định danh thứ tự để tạo sự khác biệt cho sự tham chiếu. Cái này bạn linh hoạt trong từng trường hợp để giải quyết nhé.

 

[Emyeuexcel]

Hàm small(if tương ứng aggregate (15,6...) đó bạn.

Chào bạn,

Cảm ơn bạn! nhưng mình chưa hiểu về hàm aggregate (15,6...), mình có file kèm, bạn giúp mình sao cho công thức mảng cột H, nó có thể thay bằng công thức nào khác, vẫn ko dùng cột phụ.
Ở đây mình chỉ trích ra 1 ít dữ liệu ví dụ thôi, chứ thực tế sheet Data là 1 file rất nhiều dòng, nhiều cột lưu ở 1 đường dẫn khác- Còn file của mình cũng nhiều dòng, cột-lưu ở 1 chỗ khác.
Cảm ơn bạn nhiều!

 

[Emyeuexcel]

mình góp vui!

=INDIRECT(CHAR(65+COLUMN(F1)-1)&AGGREGATE(15,6,ROW($F$5:$F$11)/($E$5:$E$11=$J5),COUNTIF($E$5:$E5,$E5)))

kéo sang phải rồi fill xuống!

Bạn ơi! Bạn xem giúp mình sao công thức (trong file kèm theo) của bạn mình muốn lấy theo tiêu đề cột, mình thêm INDEX, và MATCH thì nó báo lỗi, chỉ lấy đc cái gì text như Kết quả: lên lớp hay ở lại.
Mình ko hiểu công thức bạn lắm nên làm thử thì bị vậy.
Mong bạn giúp cho.
Cảm ơn bạn nhiều!

 

[huonglien1901]

Bạn ơi! Bạn xem giúp mình sao công thức (trong file kèm theo) của bạn mình muốn lấy theo tiêu đề cột, mình thêm INDEX, và MATCH thì nó báo lỗi, chỉ lấy đc cái gì text như Kết quả: lên lớp hay ở lại.
Mình ko hiểu công thức bạn lắm nên làm thử thì bị vậy.
Mong bạn giúp cho.
Cảm ơn bạn nhiều!

Tối về mình xem cho. Giờ mình không có ngồi máy.

 

[Emyeuexcel]

Tối về mình xem cho. Giờ mình không có ngồi máy.

Ah, bạn ơi! Mình mới ngộ ra được Hàm small(if tương ứng aggregate (15,6...)- nhưng mình không hiếu lắm phần array của Aggregate ROW($E:$E)/($E:$E=$T5) (theo file ví dụ đính kèm), vì nếu mình làm như bạn ROW($F:$F)/($E:$E=$T5) vẫn đúng. ROW(cột E) hay ROW(cột F) thì nó vẫn ok.
Mong bạn giải thích giúp!
Cảm ơn bạn nhiều nhé!