Tìm một số trong một khoảng số ???

[jiofreed]

- Hi cả nhà,
Hiện tại mình đang quản lý 1 file Voucher và bị mắc kẹt khi thống kê lại những voucher đã sử dụng thuộc chương trình nào.
Có pro nào có cách giải quyết xin chỉ mình với.
Ví dụ mình phát hành số voucher từ 0001 đến 0100, sau đó số 0101 đến 0150,Cuối tháng thì có báo cáo là voucher 0050 đã sử dụng.
Có cách nào tìm kiếm một cách tự động không ?

Chi tiết hơn xin xem file đính kèm, thank!!!

 

[Phan Thế Hiệp]

- Hi cả nhà,
Hiện tại mình đang quản lý 1 file Voucher và bị mắc kẹt khi thống kê lại những voucher đã sử dụng thuộc chương trình nào.
Có pro nào có cách giải quyết xin chỉ mình với.
Ví dụ mình phát hành số voucher từ 0001 đến 0100, sau đó số 0101 đến 0150,Cuối tháng thì có báo cáo là voucher 0050 đã sử dụng.
Có cách nào tìm kiếm một cách tự động không ?

Chi tiết hơn xin xem file đính kèm, thank!!!

Thử:

H6=LOOKUP(2,1/(MMULT(N(D6*{1,-1}>=MID(SUBSTITUTE($D$2:$D$5,"-",REPT(" ",100)),{1,101},100)*{1,-1}),{1;1})>1),$H$2:$H$5)

Enter fill xuống.
Thân.

 

[befaint]

- Hi cả nhà,
Hiện tại mình đang quản lý 1 file Voucher và bị mắc kẹt khi thống kê lại những voucher đã sử dụng thuộc chương trình nào.
Có pro nào có cách giải quyết xin chỉ mình với.
Ví dụ mình phát hành số voucher từ 0001 đến 0100, sau đó số 0101 đến 0150,Cuối tháng thì có báo cáo là voucher 0050 đã sử dụng.
Có cách nào tìm kiếm một cách tự động không ?

Chi tiết hơn xin xem file đính kèm, thank!!!

Tạm:

H6=OFFSET($H$1,MAX((D6*1>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5)-1))*(D6*1<=--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,50))*(ROW($D$2:$D$5)-1)),)
'Nhấn ctrl + shift + enter

 

[Phan Thế Hiệp]

Tạm:

H6=OFFSET($H$1,MAX((D6*1>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5)-1))*(D6*1<=--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,50))*(ROW($D$2:$D$5)-1)),)
'Nhấn ctrl + shift + enter

Công thức của em làm Lookup(2,1/...) được luôn đó, và cũng hay lắm em.

Chúc em ngày vui.

 

[jiofreed]

Cảm ơn 2 bác,
Mình đã làm được, những là coppy dữ liệu từ file gốc sang file ví dụ để làm tiếp,
Còn coppy công thức sang phải gốc thì hok được, nếu có nhiều dữ liệu hơn thì cần thay đổi số nào trong công thức thì phù hợp

 

[jiofreed]

Cảm ơn 2 bác,
Mình đã làm được, những là coppy dữ liệu từ file gốc sang file ví dụ để làm tiếp,
Còn coppy công thức sang phải gốc thì hok được, nếu có nhiều dữ liệu hơn thì cần thay đổi số nào trong công thức thì phù hợp

Mình mới kiểm tra thì thấy thấy file gốc có những lần chỉ phát hành 1 voucher riêng lẻ nên làm cho công thức sai, thank 2 bác ^^

 

[batman1]

Mình mới kiểm tra thì thấy thấy file gốc có những lần chỉ phát hành 1 voucher riêng lẻ nên làm cho công thức sai, thank 2 bác ^^

Công thức dùng những hàm cơ bản, không dùng "tà đạo" với các hằng số mảng, nên bạn chắc chắn biết phân tích và hiểu.
Cho H6

=IFERROR(VLOOKUP(D6,D$2:H$5,5,0),LOOKUP(2,1/COUNTIFS(D6,">="&LEFT(D$2:D$5,FIND("-",D$2:D$5)-1),D6,"<="&REPLACE(D$2:D$5,1,FIND("-",D$2:D$5),"")),H$2:H$5))

Enter và kéo xuống

Tất nhiên nếu luôn là khoảng như ở tập tin đầu tiên thì chỉ

=LOOKUP(2,1/COUNTIFS(D6,">="&LEFT(D$2:D$5,FIND("-",D$2:D$5)-1),D6,"<="&REPLACE(D$2:D$5,1,FIND("-",D$2:D$5),"")),H$2:H$5)

--------------
Nhưng thôi, nếu bạn mới làm quen với Excel thì tôi giải thích:
1. Thuật toán dễ hiểu thôi. Trước tiên ta dùng VLOOKUP để tìm giá trị D6 trong cột D và trả về giá trị ở cột H. Nếu có lỗi, tức dữ liệu có ký tự "-", thì xử lý như sau: Cắt từ serial đoạn trước "-" và so sánh với D6 thì phải <=, cắt đoạn sau "-" và so sánh với D6 thì phải >=. Nếu cả 2 điều kiện thỏa thì D6 nằm trong phạm vi serial, tức COUNTIFS trả về 1. 1/COUNTIFS(...) trả về mảng gồm có 1 và #DIV/0!. LOOKUP sẽ trả về giá trị tại cột H.
2. Về công thức LOOKUP(2; 1/(...); ...) thì gõ vào tìm kiếm, vd. trên GPE, và tự đọc.
3. Vì serial ở cột D nên trong công thức có D. Nếu ở cột khác thì sửa D.
Vì Chương trình khuyến mại ở cột H nên công thức có H. Nếu khác thì tự sửa.
Do dữ liệu có từ dòng 2 tới 5 nên có những con số 2 và 5 trong công thức (vùng dữ liệu). Nếu khác thì tự sửa.
Do công thức bắt đầu nhập từ dòng 6 (vào H6) bởi SD đầu tiên ở dòng 6 nên có số 6 trong công thức. Nếu khác thì tự sửa.

 

[excel_lv1.5]

Mình mới kiểm tra thì thấy thấy file gốc có những lần chỉ phát hành 1 voucher riêng lẻ nên làm cho công thức sai, thank 2 bác ^^

Thử vầy xem:

H6=OFFSET($H$1,MATCH(1,(D6>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5&"-")-1))*(D6<=IFERROR(--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,6),$D$2:$D$5)),),)

Yêu cầu bấm Ctrl+Shift+Enter!!!

 

[redeye]

Thử:

H6=LOOKUP(2,1/(MMULT(N(D6*{1,-1}>=MID(SUBSTITUTE($D$2:$D$5,"-",REPT(" ",100)),{1,101},100)*{1,-1}),{1;1})>1),$H$2:$H$5)

Enter fill xuống.
Thân.

cái vụ so sánh với số âm hay phết nhỉ
Thay đổi 1 tí xem cho phù hợp yêu cầu mới:

=LOOKUP(2,1/(MMULT(N(D6*{1,-1}>=MID(SUBSTITUTE(REPT(D$2:D$5&"-",2),"-",REPT(" ",20)),{1,21},20)*{1,-1}),{1;1})=2),H$2:H$5)

 

[HieuCD]

Mình mới kiểm tra thì thấy thấy file gốc có những lần chỉ phát hành 1 voucher riêng lẻ nên làm cho công thức sai, thank 2 bác ^^

Tại sao bạn không thiết kế file theo dạng sau để thuận tiên việc xử lý và ít nhầm khi tính số lượng

 

[VetMini]

Tại sao bạn không thiết kế file theo dạng sau để thuận tiên việc xử lý và ít nhầm khi tính số lượng

Thiết kế mệt lắm. Hỏi lấy công thức nhanh và chuẩn hơn.
(thiết kế cũng còn có chỗ nhầm - hỏi ở đây mọi ngừoi tự động tìm ra chỗ nhầm và chỉnh cho mình)

 

[batman1]

(thiết kế cũng còn có chỗ nhầm - hỏi ở đây mọi ngừoi tự động tìm ra chỗ nhầm và chỉnh cho mình)

Thế mới khôn ngoan. Nướng liền hai xiên thịt trên cùng một lò

 

[excel_lv1.5]

Thử:

H6=LOOKUP(2,1/(MMULT(N(D6*{1,-1}>=MID(SUBSTITUTE($D$2:$D$5,"-",REPT(" ",100)),{1,101},100)*{1,-1}),{1;1})>1),$H$2:$H$5)

Enter fill xuống.
Thân.

Hình như là so sánh bình thường được không cần chuyển sang số âm để so sánh!!!

cái vụ so sánh với số âm hay phết nhỉ
Thay đổi 1 tí xem cho phù hợp yêu cầu mới:

=LOOKUP(2,1/(MMULT(N(D6*{1,-1}>=MID(SUBSTITUTE(REPT(D$2:D$5&"-",2),"-",REPT(" ",20)),{1,21},20)*{1,-1}),{1;1})=2),H$2:H$5)

Có thể rút thêm 1 tý nữa đấy!!!

 

[redeye]

Hình như là so sánh bình thường được không cần chuyển sang số âm để so sánh!!!

Có thể rút thêm 1 tý nữa đấy!!!

Nghĩ chưa thông, bạn có thể ra đáp án không. Vừa không âm lại rút gọn.

 

[excel_lv1.5]

Nghĩ chưa thông, bạn có thể ra đáp án không. Vừa không âm lại rút gọn.

Cái này như hàm match(,1) thôi bạn:

=LOOKUP(2,1/(MMULT(N(D6>MID(SUBSTITUTE(REPT(D$2:D$5&"-",2),"-",REPT(" ",20)),{1,21},20)+{-1,1}),{1;1})=1),H$2:H$5)

 

[HieuCD]

Hình như là so sánh bình thường được không cần chuyển sang số âm để so sánh!!!

Có thể rút thêm 1 tý nữa đấy!!!

Chuyển số âm để chuyển dấu >= thành <=
Muốn ngắn hơn thì dùng chiêu căn 2

=LOOKUP(2,1/((D6-IFERROR(--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5)-1),$D$2:$D$5))^0.5<$E$2:$E$5^0.5),$H$2:$H$5)

Ctrl+Shift+Enter
hoặc gọn hơn

=LOOKUP(2,1/((D6-LEFT(SUBSTITUTE($D$2:$D$5,"-",REPT(" ",10)),10))^0.5<$E$2:$E$5^0.5),$H$2:$H$5)

Enter bình thường

 

[redeye]

Cái này như hàm match(,1) thôi bạn:

=LOOKUP(2,1/(MMULT(N(D6>MID(SUBSTITUTE(REPT(D$2:D$5&"-",2),"-",REPT(" ",20)),{1,21},20)+{-1,1}),{1;1})=1),H$2:H$5)

Nếu số serial ở bảng tìm kiếm là số 21 thì kết quả vẫn ra thì hơi lạ.

 

[excel_lv1.5]

Nếu số serial ở bảng tìm kiếm là số 21 thì kết quả vẫn ra thì hơi lạ.

Thấy vẫn bình thường mà!!!

 

[redeye]

Thấy vẫn bình thường mà!!!

Bạn xem thử:

 

[excel_lv1.5]

Chuyển số âm để chuyển dấu >= thành <=
Muốn ngắn hơn thì dùng chiêu căn 2

=LOOKUP(2,1/((D6-IFERROR(--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5)-1),$D$2:$D$5))^0.5<$E$2:$E$5^0.5),$H$2:$H$5)

Ctrl+Shift+Enter
hoặc gọn hơn

=LOOKUP(2,1/((D6-LEFT(SUBSTITUTE($D$2:$D$5,"-",REPT(" ",10)),10))^0.5<$E$2:$E$5^0.5),$H$2:$H$5)

Enter bình thường

Bạn sài thêm cột E, có lẽ đây là cách ngắn nhất rồi!!!