Hàm if điều kiện mảng

[vova2209]

Anh chị giúp em!
Chỗ phần điều kiện trong mục bôi đỏ dưới, em chuyển thành "data name" thi hàm không chạy được. Anh chị giúp em xem có cách nào tối giản công thức ko để luôn data vào công thức, mỗi lần thêm dữ liệu lại phải kéo lại công thức

 

[huhumalu]

Bạn thử cách sau:
1. Dùng define name để định nghĩa ItemList
2. Dùng công thức =IF(ISNUMBER(MATCH($E8, ItemList, 0)), INDIRECT("J"&ROW()), "")

 

[vova2209]

Bạn thử cách sau:
1. Dùng define name để định nghĩa ItemList
2. Dùng công thức =IF(ISNUMBER(MATCH($E8, ItemList, 0)), INDIRECT("J"&ROW()), "")

ồ! cảm ơn bạn nhiều, hàm chạy ngon rồi

 

[Phan Thế Hiệp]

Anh chị giúp em!
Chỗ phần điều kiện trong mục bôi đỏ dưới, em chuyển thành "data name" thi hàm không chạy được. Anh chị giúp em xem có cách nào tối giản công thức ko để luôn data vào công thức, mỗi lần thêm dữ liệu lại phải kéo lại công thức

View attachment 300813

Bạn dùng mảng đó làm chi cho khổ vậy? Sao không liệt kê các mục đó trong 1 cột khác, rồi dùng công thức sau:

=COUNT(1/SEARCH($S$6:$S$33,E6))*J6
(Kết thúc bằng Ctrl+Shift+Enter, fill xuống)

Không cần dùng 'name' hoặc indirect() gì cả.

Thân

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

Vầy thì đúng hơn:
=COUNTIF($S$6:$S$33,TRIM(E6))*J6
Enter

Thân

 

[vu_tuan_manh_linh]

Tôi không hiểu cái đoạn INDIRECT("J"&ROW()), vì hàm kéo 1 phát từ trên xuống dưới thì trỏ thẳng nó vào ô cột J cùng hàng chứ mắc gì dùng Indirect() cho nặng file.

 

[vova2209]

Bạn dùng mảng đó làm chi cho khổ vậy? Sao không liệt kê các mục đó trong 1 cột khác, rồi dùng công thức sau:

=COUNT(1/SEARCH($S$6:$S$33,E6))*J6
(Kết thúc bằng Ctrl+Shift+Enter, fill xuống)

Không cần dùng 'name' hoặc indirect() gì cả.

Thân

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

Vầy thì đúng hơn:
=COUNTIF($S$6:$S$33,TRIM(E6))*J6
Enter

Thân

vâng cảm ơn bạn.. nhiều cách hay!

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

Tôi không hiểu cái đoạn INDIRECT("J"&ROW()), vì hàm kéo 1 phát từ trên xuống dưới thì trỏ thẳng nó vào ô cột J cùng hàng chứ mắc gì dùng Indirect() cho nặng file.

Indirect() hàm này dùng nặng bảng tính hơn à bạn, file mình hay dùng nhiều hàm này. để mình đổi lại chắc cải thiện thêm tốc độ

 

[vu_tuan_manh_linh]

vâng cảm ơn bạn.. nhiều cách hay!

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

Indirect() hàm này dùng nặng bảng tính hơn à bạn, file mình hay dùng nhiều hàm này. để mình đổi lại chắc cải thiện thêm tốc độ

Đương nhiên là nặng hơn, nhưng ko rõ cải thiện được bao nhiêu, nhưng bạn cứ hiểu đơn giản là thay vì đi thẳng đến cột J, thì Excel nó phải tính toán ra xem địa chỉ ô ở đâu rồi mới đi đến đó nếu dùng Indirect.

Có 1 cách nữa bạn cũng thử: đặt tất cả các loại vật tư bạn cần lọc vào 1 sheet, đặt cho vùng đó 1 cái name (giả sử là Danhmuc), tại sheet Chiết tính, bạn đặt hàm Q7=If(countif(Danhmuc, E7)=1;J7;0). Trânh dùng các hàm mảng ra, nó nặng file và thông thường thì ít người biết đến nó nên trong trường hợp cần share file thì người không biết sẽ khó sử dụng hoặc kiểm tra.

 

[VetMini]

Đương nhiên là nặng hơn, nhưng ko rõ cải thiện được bao nhiêu, nhưng bạn cứ hiểu đơn giản là thay vì đi thẳng đến cột J, thì Excel nó phải tính toán ra xem địa chỉ ô ở đâu rồi mới đi đến đó nếu dùng Indirect.
...

Indirect là hàm volatile.
Có thể tác giả muốn công thức tính lại mỗi lần có thay đổi (mục đích chính của volatile).

Indirect có làm nặng công thức bao nhiêu tôi không nói tới. Tôi chỉ lưu ý là hàm volatile không nên sử dụng bừa bải, vì chúng khá nặng tính toán.

 

[nonut]

vâng cảm ơn bạn.. nhiều cách hay!

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

Indirect() hàm này dùng nặng bảng tính hơn à bạn, file mình hay dùng nhiều hàm này. để mình đổi lại chắc cải thiện thêm tốc độ

Indirect sử dụng chủ yếu để trỏ dứ liệu vào nhiều column hoăc nhiều sheets khác nhau hoặc từ các file khác nhau. Trong trường hợp cùng 1 cột, chỉ thay đổi số dòng thì ko cần dùng.

Hàm Indirect ko làm nặng máy (đặc biệt các máy đời mới thì bạn sẽ gần như ko nhận biết nếu sử dụng indirect cho dưới khoảng 1 triệu formula) nhưng sẽ làm khó cho người dò lại công thức.

 

[VetMini]

Indirect có hai công dụng chính và một công dụng phụ. Những cách dùng khác là mẹo.

Công dụng chính:
1. Dùng để xác định cứng vùng dữ liệu (range,cell,...) truy cập. Khi công thức được chép sang nơi khác
=INDIRECT("'Sheet2'!a1") luôn luôn trỏ vào 'Sheet2'!a1. Bất cứ bạn dời/copy công thức hay thay đổi bảng tính thế nào, nó vẫn nhất định trỏ vào chỗ đó.
2. Có vẻ hơi ngược lại với cách dùng 1. Ở đây Indirect cho phép tính ra vùng dữ liệu cần truy cập
=INDIRECT("'Sheet" & b2 & "'!b" & c2)
Nếu b2 chứa 2 và c2 chứa 5 thì hàm trên trả về 'Sheet2'!b5

Công dụng phụ là để lập name động. Ví dụ khi vùng dữ liệu truy cập thay đổi, thêm/bớt dòng/cột thường xuyên, name động sẽ tự dãn co theo.

 

[hongphuong1997]

Indirect có hai công dụng chính và một công dụng phụ. Những cách dùng khác là mẹo.

Công dụng chính:
1. Dùng để xác định cứng vùng dữ liệu (range,cell,...) truy cập. Khi công thức được chép sang nơi khác
=INDIRECT("'Sheet2'!a1") luôn luôn trỏ vào 'Sheet2'!a1. Bất cứ bạn dời/copy công thức hay thay đổi bảng tính thế nào, nó vẫn nhất định trỏ vào chỗ đó.
2. Có vẻ hơi ngược lại với cách dùng 1. Ở đây Indirect cho phép tính ra vùng dữ liệu cần truy cập
=INDIRECT("'Sheet" & b2 & "'!b" & c2)
Nếu b2 chứa 2 và c2 chứa 5 thì hàm trên trả về 'Sheet2'!b5

Công dụng phụ là để lập name động. Ví dụ khi vùng dữ liệu truy cập thay đổi, thêm/bớt dòng/cột thường xuyên, name động sẽ tự dãn co theo.

Bác oi hàm INDIRECT về cú pháp khó dùng và hay quên, vậy dùng Index() có hay hơn không hở bác?

 

[Hoàng Tuấn 868]

Bác oi hàm INDIRECT về cú pháp khó dùng và hay quên, vậy dùng Index() có hay hơn không hở bác?

Cái này tùy từng trường hợp cụ thể chứ không phải khi nào cũng dùng Index thay cho Indirect được.

 

[vu_tuan_manh_linh]

Indirect là hàm volatile.
Có thể tác giả muốn công thức tính lại mỗi lần có thay đổi (mục đích chính của volatile).

Indirect có làm nặng công thức bao nhiêu tôi không nói tới. Tôi chỉ lưu ý là hàm volatile không nên sử dụng bừa bải, vì chúng khá nặng tính toán.

Em thường dùng indirect() trong trường hợp tạo vùng tham chiếu cho 1 name, nó cho phép thay đổi vùng tham chiếu của name, mà name này em lại dùng vào 1 việc khác là tạo 1 List data validation. Khi đó, list xổ ra sẽ luôn vừa vặn với vùng dữ liệu.

 

[huhumalu]

Em thường dùng indirect() trong trường hợp tạo vùng tham chiếu cho 1 name, nó cho phép thay đổi vùng tham chiếu của name, mà name này em lại dùng vào 1 việc khác là tạo 1 List data validation. Khi đó, list xổ ra sẽ luôn vừa vặn với vùng dữ liệu.

Mình cũng rất không thích vụ list data validation có dòng thừa.
Bạn có thể gửi file ví dụ nào đó để mình tham khảo học hỏi không ?

 

[vu_tuan_manh_linh]

Mình cũng rất không thích vụ list data validation có dòng thừa.
Bạn có thể gửi file ví dụ nào đó để mình tham khảo học hỏi không ?

Bạn tham khảo file này. Mấu chốt ở đây là hàm Indirect() trong Refers to của name ListData.

 

[VetMini]

Bác oi hàm INDIRECT về cú pháp khó dùng và hay quên, vậy dùng Index() có hay hơn không hở bác?

Nếu bạn làm được thì đúng là hay hơn, tránh cái tội volatile của Indirect.
Riêng tôi thì thấy khi cần truy cập thật phức tạp thì bọn Indirect, Offset,... dễ hơn nhiều

Ở đây (GPE) có một bạn rất hay dùng Indirect, Offset,... Bạn thử hỏi ý kiến người ấy xem.

Chú thích về volatile:
Có một số công thức cần refresh liên tục. Khi dùng chúng, người ta thường nhét thêm một hàm volatile, Hmf này chả làm gì hết, chỉ kích động công thức thôi. Điển hình là công thức tính tên của sheet.

 

[ptm0412]

Nếu chỉ có 1 sheet, 1 cột thì dùng Offset cho đỡ nhức đầu, chưa cần tới Indirect.

Bạn tham khảo file này. Mấu chốt ở đây là hàm Indirect() trong Refers to của name ListData.

Indirect dùng cho động rộng hơn: Nhiều sheet, mỗi sheet 1 vùng nhỏ dữ liệu chạy lên chạy xuống trong bảng dữ liệu lớn.
File dưới đây là 1 thí dụ: khi chọn C1 là nhiệt độ:



Khi chọn C1 là lượng mưa, và thử kéo 3 thanh trượt

 

[ptm0412]

Ở đây (GPE) có một bạn rất hay dùng Indirect, Offset,... Bạn thử hỏi ý kiến người ấy xem.

Tôi không phải "người ấy" nhưng tôi cũng có ý kiến: Chỉ dùng Indirect khi cần thiết, không phải vì nhanh chậm mà là nếu đơn giản được thì cứ đơn giản.

 

[VetMini]

Nếu chỉ có 1 sheet, 1 cột thì dùng Offset cho đỡ nhức đầu, chưa cần tới Indirect.
...

Chỉ thắc mắc thôi, không một ý nào bắt bẻ:
- Bên khí tượng dùng trung bình cộng để dự đoán tình hình năm mới hở?

 

[ptm0412]

Bên khí tượng dùng trung bình cộng để dự đoán tình hình năm mới hở?

Tôi không biết anh ơi. File này làm giúp cho 1 người khác và cũng lâu rồi