Vấn đề
Chúng tôi có một danh sách các mã chữ và số. Mỗi mã bao gồm một chữ cái (A, B, C, v.v.) theo sau là một số có 3 chữ số. Những mã này sẽ xuất hiện theo thứ tự bảng chữ cái, nhưng đôi khi chúng không theo trình tự. Chúng tôi muốn gắn cờ các mã không nằm ngoài trình tự.
Thử thách số 1
Công thức nào trong cột "Kiểm tra" sẽ đặt dấu "x" bên cạnh mã nằm ngoài trình tự? Trong thử thách này, chúng tôi chỉ kiểm tra xem phần * số * của mã có nằm ngoài trình tự không, chứ không phải bản thân chữ cái đó nằm ngoài trình tự.
Thử thách số 2
Làm cách nào để mở rộng công thức trên để kiểm tra xem phần "alpha" của mã (A, B, C, v.v.) có nằm ngoài trình tự không? Ví dụ: chúng ta nên gắn cờ một mã bắt đầu bằng "A" nếu nó xuất hiện sau mã bắt đầu bằng "C" hoặc "B".
Tải xuống bảng tính dưới đây và thực hiện thử thách!
Lưu ý: có 2 trang trong sách bài tập, một cho Thử thách số 1, một trang cho Thử thách số 2.
Gợi ý - Video này trình bày một số mẹo về cách giải quyết vấn đề như thế này.
Giả định
- Tất cả các mã luôn chứa bốn ký tự: 1 ký tự hoa + 3 số.
- Số lượng mã trên mỗi chữ cái là ngẫu nhiên, nhưng không được có khoảng trống trong các giá trị số.
- Chỉ cần đánh dấu mã đầu tiên bằng một chữ cái không theo trình tự, không phải tất cả các mã tiếp theo.
Dưới đây là một số giải pháp làm việc. Điều quan trọng là phải hiểu rằng có rất nhiều cách để giải quyết các vấn đề phổ biến trong Excel. Các câu trả lời dưới đây chỉ là sở thích cá nhân của tôi. Trong tất cả các công thức bên dưới, có thể nhấp vào tên hàm nếu bạn muốn biết thêm thông tin.
Thử thách số 1
Ban đầu tôi đã đi với công thức này:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Lưu ý MID trả về văn bản. Bằng cách thêm 1 và thêm số 0, chúng tôi sẽ khiến Excel buộc văn bản thành một số. Phép nhân bên trong kiểm tra logic bên trong IF sử dụng logic boolean để tránh IF lồng nhau khác. Tôi không chắc tại sao tôi không sử dụng RIGHT, điều này cũng sẽ hoạt động tốt ở đây.
Cũng lưu ý rằng LEFT không yêu cầu số lượng ký tự và sẽ trả về ký tự đầu tiên nếu không được cung cấp.
Dựa trên một số phản hồi thông minh bên dưới, chúng tôi có thể tối ưu hóa nhiều hơn một chút:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Ở đây, phép toán trừ MID khỏi MID sẽ tự động ép các giá trị văn bản thành số.
Thử thách số 2
Đối với giải pháp này, tôi đã sử dụng một số IF lồng nhau (thêm ngắt dòng để dễ đọc):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Tôi đã làm điều này vì thử nghiệm đầu tiên LEFT (B5) = LEFT (B6) xác định xem chúng ta đang kiểm tra số hay chữ cái. Nếu ký tự đầu tiên giống nhau, chúng tôi đang kiểm tra các số như trên. Nếu không, chúng tôi chỉ kiểm tra chữ cái đầu tiên.
Lưu ý rằng hàm CODE sẽ trả về số ascii của ký tự đầu tiên nếu một chuỗi văn bản chứa nhiều hơn 1 ký tự. Điều này cảm thấy giống như một cuộc tấn công và nó làm cho mã ít hiểu hơn có lẽ, nhưng nó hoạt động :)
Nếu điều đó xúc phạm đến sự nhạy cảm của bạn, hãy sử dụng LEFT như trên bên trong CODE để chỉ cung cấp ký tự đầu tiên.
