Tìm một số trong một khoảng số ??? (1 người xem)

[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!!!

 

[excel_lv1.5]

Bạn xem thử:

Bạn chỉnh lại như vầy:

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

 

[Phan Thế Hiệ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 ^^

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

Nếu bạn nói trước thì công thức đã khác!

Như bạn tham khảo các bài giải trên, dù đã có công thức xuất sắc của anh @HieuCD , và bạn @excel_lv1.5 hướng dẫn bạn cho ra kết quả chính xác và gọn gàng, nhưng tôi góp vui thêm công thức trên cơ sở công thức của bạn @befaint, như gợi ý tại bài #4 và cũng xử lý cho trường hợp có thể có số seri Voucher chỉ có 1 số, để bạn tiện tham khảo cho việc học tập:

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

kết thúc bằng Ctrl+Shift+Enter.

Về hàm Lookup(2,1/....): công thức truy lục theo nhiều điều kiện bạn có thể tham khảo hướng dẫn tại link: https://www.giaiphapexcel.com/diendan/threads/nhờ-các-anh-chị-lập-công-thức-tính-số-ngày-nhập-hàng-chân-thành-cám-ơn.123588/#post-773512

Chúc bạn học tập vui với anh em GPE.
Thân.

 

[excel_lv1.5]

Nếu bạn nói trước thì công thức đã khác!

Như bạn tham khảo các bài giải trên, dù đã có công thức xuất sắc của anh @HieuCD , và bạn @excel_lv1.5, nhưng sẵn tiện tôi cũng đưa luôn công thức theo hướng giải của @befaint nhưng bằng Lookup(), như tôi gợi ý bài #4, và cũng đã xử lý luôn nếu Voucher nếu chỉ có 1 số, để bạn tiện tham khảo thêm cho vui:

H6=LOOKUP(2,1/(D6*1>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5&"-")-1))/(D6*1<=IFERROR(--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,50),10^10)),$H$2:$H$5)

kết thúc bằng Ctrl+Shift+Enter.

Về hàm Lookup(2,1/....): công thức truy lục theo nhiều điều kiện bạn có thể tham khảo hướng dẫn tại link: https://www.giaiphapexcel.com/diendan/threads/nhờ-các-anh-chị-lập-công-thức-tính-số-ngày-nhập-hàng-chân-thành-cám-ơn.123588/#post-773512

Chúc bạn học tập vui với anh em GPE.
Thân.

LOOKUP(2,1/(D6*1>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5&"-")-1))/(D6*1<=IFERROR(--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,50),10^10)),$H$2:$H$5)=> Chỗ màu xanh... nguy hiểm!!!

 

[redeye]

Bạn chỉnh lại như vầy:

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

Vui thật 1 cơ hội để trao đổi công thức, rất hữu ích.

 

[Phan Thế Hiệp]

LOOKUP(2,1/(D6*1>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5&"-")-1))/(D6*1<=IFERROR(--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,50),10^10)),$H$2:$H$5)=> Chỗ màu xanh... nguy hiểm!!!

Vậy thì:

LOOKUP(2,1/(D6*1>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5&"-")-1))/(D6*1<=IFERROR(--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,50),D6*1)),$H$2:$H$5)

Vui thật 1 cơ hội để trao đổi công thức, rất hữu ích.

Chào mừng bạn đến hội của những anh em đam mê công thức mảng.

Chúc anh em ngày vui.

 

[excel_lv1.5]

Vậy thì:

LOOKUP(2,1/(D6*1>=--LEFT($D$2:$D$5,FIND("-",$D$2:$D$5&"-")-1))/(D6*1<=IFERROR(--MID($D$2:$D$5,FIND("-",$D$2:$D$5)+1,50),D6*1)),$H$2:$H$5)

Chào mừng bạn đến hội của những anh em đam mê công thức mảng.

Chúc anh em ngày vui.

Thật sự chỗ đấy vẫn chưa ổn với voucher chỉ có một seri.

 

[Phan Thế Hiệp]

Thật sự chỗ đấy vẫn chưa ổn với voucher chỉ có một seri.

Đã thử rồi mà!
Thấy vẫn ổn.
Xem lại giúp xem sao.
Thân.

 

[excel_lv1.5]

Đã thử rồi mà!
Thấy vẫn ổn.
Xem lại giúp xem sao.
Thân.

Ở D6 bạn thử với seri>355 xem, 400 chẳng hạn!!!

 

[Phan Thế Hiệp]

Ở D6 bạn thử với seri>355 xem, 400 chẳng hạn!!!

................
Tối qua về trễ, thấy anh em quây quần vui quá vào góp ý, nhưng đầu óc mụ mẫm!
Thay vì để 10^10 hay D6*1, thì cứ bê nguyên mảng D2: D5*1 vô thay thế là giải quyết xong, vậy là ổn hén!

Cũng đã sửa lại công thức sau cùng tại bài #22 để chủ thớt tham khảo.

Cảm ơn anh em đã sum vầy.

Thân.

 

[jiofreed]

Cảm ơn tất cả anh em đã nhiệt tình giúp đỡ,
Hôm qua đi làm về đuối quá nên đã lăn ra ngủ.
Sáng nay sẽ thử tất cả các giải pháp của anh em chỉ dạy.

Chúc anh em có ngày mới và cuối tuần vui vẻ,