Theo sử dụng một bảng tính Excel để theo dõi các đặt phòng trong công ty của mình. Dữ liệu chỉ bao gồm ba cột. Đầu tiên là tên của một người, thứ hai là số tuần đầu tiên (1-52) của đặt chỗ và thứ ba là số tuần cuối cùng của đặt phòng.

Mọi người có thể được bảo lưu trong nhiều tuần (tức là tuần bắt đầu là 15 và tuần kết thúc là 19). Theo cần một cách để nhập một số tuần và sau đó có một công thức xác định tên nào (cột A) được liên kết với số tuần đó. Dữ liệu không được sắp xếp theo bất kỳ thứ tự cụ thể nào và công ty sẽ không cho phép Theo sử dụng macro để lấy kết quả (nó phải là một công thức).

Tình huống của Theo nghe có vẻ đơn giản, nhưng nó chứa đầy cạm bẫy khi nghĩ ra một giải pháp. Nhìn vào dữ liệu tiềm năng (như thể hiện trong hình sau) nhanh chóng minh họa tại sao lại như vậy. (Xem Hình 1.)

image

Hình 1. Dữ liệu tiềm năng cho vấn đề của Theo.

Lưu ý rằng dữ liệu (như Theo đã nói) không theo bất kỳ thứ tự cụ thể nào.

Cũng xin lưu ý rằng có một số tuần không có người đặt chỗ trước (chẳng hạn như tuần 5 hoặc 6), tuần có nhiều người (chẳng hạn như tuần 11 hoặc 16) và những tuần có người đặt chỗ, nhưng tuần số không hiển thị trong cột B hoặc C (chẳng hạn như tuần 12 hoặc 17).

Trước khi bắt đầu xem xét các giải pháp tiềm năng, hãy giả sử rằng tuần bạn muốn biết là ô E1. Bạn nên đặt tên cho phạm vi này là Truy vấn. Ngoài ra, hãy đặt tên cho phạm vi chứa tên của mọi người (trong ví dụ này là ô A2: A10) là ResNames, các tuần bắt đầu (B2: B10) là StartWeeks và các tuần kết thúc (C2: C10) là EndWeeks. Cuối cùng, xác định tên cho toàn bộ bảng (A2: C10), chẳng hạn như MyData. Việc đặt tên này, mặc dù không quá cần thiết nhưng sẽ giúp hiểu các công thức dễ dàng hơn nhiều.

Một giải pháp tiềm năng là thêm những gì thường được gọi là “cột trợ giúp”. Thêm phần sau vào ô D2:

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

Sao chép công thức xuống, cho bao nhiêu ô có tên trong bảng. (Ví dụ: sao chép nó qua ô D10.) Khi bạn đặt một số tuần vào ô E1, thì từ “ĐÃ ĐẶT CHỖ” sẽ xuất hiện ở bên phải của bất kỳ yêu cầu đặt chỗ nào liên quan đến số tuần đó. Cũng dễ dàng xem liệu có nhiều người được đặt trước cho tuần đó hoặc không có người nào được đặt trước cho tuần đó. Bạn thậm chí có thể áp dụng AutoFilter và chọn chỉ hiển thị những bản ghi có từ “RESERVED” trong cột D.

Nếu muốn, bạn có thể bỏ qua cột trợ giúp và cân nhắc sử dụng định dạng có điều kiện để hiển thị ai được dành cho một tuần mong muốn.

Chỉ cần chọn tên trong cột A và thêm quy tắc định dạng có điều kiện sử dụng công thức sau:

=AND(Query>=B2,Query<=C2)

(Cách bạn nhập các quy tắc định dạng có điều kiện đã được mô tả nhiều trong các số báo khác của ExcelTips.) Đặt quy tắc để nó thay đổi cách tô bóng (mẫu) được áp dụng cho ô và bạn sẽ dễ dàng biết được những yêu cầu đặt chỗ nào áp dụng tuần mà bạn quan tâm.

Một cách tiếp cận khác là sử dụng công thức mảng. Chọn nhiều ô hơn số lượng đặt trước chồng chéo mà bạn mong đợi, sau đó nhập thông tin sau vào các ô đó bằng cách nhấn Ctrl + Shift + Enter:

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)(EndWeeks>=Query)(ROW(ResNames)),ROW()-1)-1),"")

Khi chọn số lượng ô, bạn muốn công thức mảng này chiếm, ví dụ, hãy xem số người có thể được đặt trước trong tuần 11. Trong ví dụ được hiển thị trong mẹo này, đó là 2 người. Chọn nhiều hơn số ô đó và sau đó đặt công thức mảng trong các ô đó.

Nếu bạn mong đợi rằng bạn có thể có 20 người có khả năng được đặt trước trong cùng một tuần, thì bạn sẽ muốn chọn một số lượng ô lớn hơn, chẳng hạn như 20 hoặc 30. Chỉ cần chọn các ô, đặt công thức vào thanh Công thức, sau đó nhấn Ctrl + Shift + Enter.

Cuối cùng, bạn thực sự nên xem xét sửa đổi cách trình bày dữ liệu của mình.

Bạn có thể tạo một trang tính có số tuần trong cột A (1 đến 52 hoặc 53) và sau đó đặt tên trong cột B. Nếu một người được bảo lưu trong hai tuần, tên của họ sẽ xuất hiện trong cột B hai lần, một lần bên cạnh mỗi hai tuần mà họ đã đặt trước.

Với dữ liệu của bạn ở định dạng này, bạn có thể dễ dàng quét dữ liệu để xem những tuần nào còn trống, những tuần nào được thực hiện và chúng được thực hiện bởi ai.

Nếu bạn muốn thực hiện một số loại tra cứu, có thể dễ dàng sử dụng hàm Vlookup dựa trên số tuần, vì nó là cột đầu tiên của dữ liệu, theo thứ tự được sắp xếp.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (11078) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: