Hỗ trợ tìm Kiếm dữ liệu "SIÊU RÙA BÒ" trong Excel" (1 người xem)

[batman1]

Chắc nhờ bác chỉ thêm ạ. Nhiều danh mục em chưa biết là thiếu luôn...

1. Trước hết thêm 2 Name.
Formulas -> Name Manager -> New -> thêm name data -> Refers to

=OFFSET(DATA!$B$7;;;COUNTA(DATA!$B$1:$B$10000))

Có DATA!$B$7 vì dữ liệu bắt đầu từ B7.

-> New -> thêm Name list -> Refers to

=OFFSET(DATA!$N$7;;;COUNTA(DATA!$N$1:$N$10000)-COUNTIF(DATA!$N$1:$N$10000;#NUM!))

Lưu ý là DATA!$N$7 bởi ta sẽ nhập công thức đầu tiên vào DATA!$N$7. Nếu công thức nhập vào ô khác thì phải sửa DATA!$N$7 thành ô đó. Nên nhớ là địa chỉ dòng và cột là tuyệt đối - có $. Tất nhiên lúc đó trong COUNTA và COUNTIF cũng sửa cột N thành cột được chọn.

2. Nhập vào DATA!N7 công thức

=INDEX(data;AGGREGATE(15;6;ROW(data)/(SEARCH(INDIRECT(CELL("address"));data)>0);ROW(1:1))-6)

Kéo (copy) công thức N7 xuống dưới. Đề phòng trong tương lai thêm nhiều dữ liệu thì kéo dư một chút.

Lưu ý là ở cuối công thức có - 6. Nguyên tắc là thế này: Nếu dữ liệu bắt đầu từ dòng k thì trong công thức phải là - (k-1). Tại sao? Bởi ROW(data) trả về mảng các chỉ số dòng bắt đầu từ 7 (B7): 7, 8, 9, ... Trong khi đó B7 có chỉ số là 1 trong mảng dữ liệu data (là mục đầu tiên). Phải - 6 để có chỉ số 1.

Công thức chỉ phục vụ quãng 10 000 dòng dữ liệu. Muốn nhiều hơn thì sửa trong 2 name.

Có thể có thông báo lỗi nhưng chỉ cần chọn ô có DV, nhập giá trị và xổ danh sách xuống thì sẽ đúng.

Lỗi tham chiếu vòng chỉ sảy ra khi nhập công thức hoặc khi sửa công thức. Vd. khi nhập công thức cho N7 thì CELL("address") chính là N7 - tức có tham chiếu tới chính nó. Nhưng công thức chỉ nhập 1 lần, và sau đó muôn đời không có tham chiếu vòng nữa. Cứ mạnh dạn nhập vào DV rồi xổ danh sách xuống. Không có gì phải sợ khi nhìn thấy thông báo lỗi.

3. Data Validation

=list

------------
Tất cả những việc trên tôi đã làm hộ bạn. Giải thích để sau này bạn tự biết làm. Và cho những ai quan tâm.

 

[HieuCD]

1. Trước hết thêm 2 Name.
Formulas -> Name Manager -> New -> thêm name data -> Refers to

=OFFSET(DATA!$B$7;;;COUNTA(DATA!$B$1:$B$10000))

Có DATA!$B$7 vì dữ liệu bắt đầu từ B7.

-> New -> thêm Name list -> Refers to

=OFFSET(DATA!$N$7;;;COUNTA(DATA!$N$1:$N$10000)-COUNTIF(DATA!$N$1:$N$10000;#NUM!))

Lưu ý là DATA!$N$7 bởi ta sẽ nhập công thức đầu tiên vào DATA!$N$7. Nếu công thức nhập vào ô khác thì phải sửa DATA!$N$7 thành ô đó. Nên nhớ là địa chỉ dòng và cột là tuyệt đối - có $. Tất nhiên lúc đó trong COUNTA và COUNTIF cũng sửa cột N thành cột được chọn.

2. Nhập vào DATA!N7 công thức

=INDEX(data;AGGREGATE(15;6;ROW(data)/(SEARCH(INDIRECT(CELL("address"));data)>0);ROW(1:1))-6)

Kéo (copy) công thức N7 xuống dưới. Đề phòng trong tương lai thêm nhiều dữ liệu thì kéo dư một chút.

Lưu ý là ở cuối công thức có - 6. Nguyên tắc là thế này: Nếu dữ liệu bắt đầu từ dòng k thì trong công thức phải là - (k-1). Tại sao? Bởi ROW(data) trả về mảng các chỉ số dòng bắt đầu từ 7 (B7): 7, 8, 9, ... Trong khi đó B7 có chỉ số là 1 trong mảng dữ liệu data (là mục đầu tiên). Phải - 6 để có chỉ số 1.

Công thức chỉ phục vụ quãng 10 000 dòng dữ liệu. Muốn nhiều hơn thì sửa trong 2 name.

Có thể có thông báo lỗi nhưng chỉ cần chọn ô có DV, nhập giá trị và xổ danh sách xuống thì sẽ đúng.

Lỗi tham chiếu vòng chỉ sảy ra khi nhập công thức hoặc khi sửa công thức. Vd. khi nhập công thức cho N7 thì CELL("address") chính là N7 - tức có tham chiếu tới chính nó. Nhưng công thức chỉ nhập 1 lần, và sau đó muôn đời không có tham chiếu vòng nữa. Cứ mạnh dạn nhập vào DV rồi xổ danh sách xuống. Không có gì phải sợ khi nhìn thấy thông báo lỗi.

3. Data Validation

=list

------------
Tất cả những việc trên tôi đã làm hộ bạn. Giải thích để sau này bạn tự biết làm. Và cho những ai quan tâm.

Mình nghỉ, dùng 1 cột phụ "N" và thêm Name thường làm nặng file, thay các name trung gian bằng cột phụ sẽ nhẹ file hơn

 

[HieuCD]

- Chào các bác. Em có sưu tầm được 1 file "Danh sach So xuong" hướng dẫn cách "Tìm Kiếm Dữ Liệu Siêu Tốc Gợi Ý Như Google Trong Excel". File em làm theo là "Mau BB". Tại ô R6 của sheet "DATA" thì chỉ cần gõ ký tự ví dụ "PY" thì chạy bình thường theo list sổ xuống, em thực hiện nó ở ô B2 của sheet "TEST" thì không thực hiện được... Mong nhận được sự giúp đỡ của các bác.
- Ghi chú: file tham khảo là "Danh sach So xuong", file cần trợ giúp là"Mau BB".
- Em xin cảm ơn trước ạ!

Tạo 2 cột phụ lọc danh sách, thêm hàm if để loại tính vòng

 

[GACON_VBA]

1. Trước hết thêm 2 Name.
Formulas -> Name Manager -> New -> thêm name data -> Refers to

=OFFSET(DATA!$B$7;;;COUNTA(DATA!$B$1:$B$10000))

Có DATA!$B$7 vì dữ liệu bắt đầu từ B7.

-> New -> thêm Name list -> Refers to

=OFFSET(DATA!$N$7;;;COUNTA(DATA!$N$1:$N$10000)-COUNTIF(DATA!$N$1:$N$10000;#NUM!))

Lưu ý là DATA!$N$7 bởi ta sẽ nhập công thức đầu tiên vào DATA!$N$7. Nếu công thức nhập vào ô khác thì phải sửa DATA!$N$7 thành ô đó. Nên nhớ là địa chỉ dòng và cột là tuyệt đối - có $. Tất nhiên lúc đó trong COUNTA và COUNTIF cũng sửa cột N thành cột được chọn.

2. Nhập vào DATA!N7 công thức

=INDEX(data;AGGREGATE(15;6;ROW(data)/(SEARCH(INDIRECT(CELL("address"));data)>0);ROW(1:1))-6)

Kéo (copy) công thức N7 xuống dưới. Đề phòng trong tương lai thêm nhiều dữ liệu thì kéo dư một chút.

Lưu ý là ở cuối công thức có - 6. Nguyên tắc là thế này: Nếu dữ liệu bắt đầu từ dòng k thì trong công thức phải là - (k-1). Tại sao? Bởi ROW(data) trả về mảng các chỉ số dòng bắt đầu từ 7 (B7): 7, 8, 9, ... Trong khi đó B7 có chỉ số là 1 trong mảng dữ liệu data (là mục đầu tiên). Phải - 6 để có chỉ số 1.

Công thức chỉ phục vụ quãng 10 000 dòng dữ liệu. Muốn nhiều hơn thì sửa trong 2 name.

Có thể có thông báo lỗi nhưng chỉ cần chọn ô có DV, nhập giá trị và xổ danh sách xuống thì sẽ đúng.

Lỗi tham chiếu vòng chỉ sảy ra khi nhập công thức hoặc khi sửa công thức. Vd. khi nhập công thức cho N7 thì CELL("address") chính là N7 - tức có tham chiếu tới chính nó. Nhưng công thức chỉ nhập 1 lần, và sau đó muôn đời không có tham chiếu vòng nữa. Cứ mạnh dạn nhập vào DV rồi xổ danh sách xuống. Không có gì phải sợ khi nhìn thấy thông báo lỗi.

3. Data Validation

=list

------------
Tất cả những việc trên tôi đã làm hộ bạn. Giải thích để sau này bạn tự biết làm. Và cho những ai quan tâm.

Bác hướng dẫn rất tận tình và chi tiết. Cảm ơn bác nhiều ạ.

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

Tạo 2 cột phụ lọc danh sách, thêm hàm if để loại tính vòng

Cảm ơn bác đã quan tâm và trợ giúp nhiều ạ!