Excel sắp xếp các công thức lộn xộn

CHÀO,

Tôi đã gặp phải một lỗi có vẻ rất kỳ lạ và có thể sử dụng một số hướng dẫn. Sau đây là một minh chứng rất đơn giản về vấn đề tôi đang gặp phải:

Tôi có hai tờ, một tờ có danh sách các mặt hàng và số lượng bán hàng, tờ còn lại có danh sách các mặt hàng đã bán

Trang 1:

Mục	Số lượng bán ra (công thức được sử dụng)	Số lượng đã bán (Kết quả)
f	=COUNTIF(Trang 2!A:A,Trang 1!A2)	1
Một	=COUNTIF(Trang 2!A:A,Trang 1!A3)	4
b	=COUNTIF(Trang 2!A:A,Trang 1!A4)	2
e	=COUNTIF(Trang 2!A:A,Trang 1!A5)	3
c	=COUNTIF(Trang 2!A:A,Trang 1!A6)	1
d	=COUNTIF(Trang 2!A:A,Trang 1!A7)	2

tờ 2

chỉ là danh sách sau đây trong các ô a2:a14: a, a, b, b, c, c, d, e, e, e, a, a, f

Như bạn có thể thấy, tất cả đều tốt và kết quả đạt được như mong đợi.

Tuy nhiên nếu bây giờ tôi sắp xếp trang đầu tiên theo từng mục thì sẽ có điều gì đó kỳ lạ xảy ra. Công thức được di chuyển để không ảnh hưởng đến ô bên cạnh và do đó tạo ra kết quả sai:

Mục	Số lượng bán ra (công thức được sử dụng)	Số lượng đã bán (Kết quả)
Một	=COUNTIF(Trang 2!A:A,Trang 1!A3)	1
b	=COUNTIF(Trang 2!A:A,Trang 1!A4)	2
c	=COUNTIF(Trang 2!A:A,Trang 1!A7)	1
d	=COUNTIF(Trang 2!A:A,Trang 1!A6)	3
e	=COUNTIF(Trang 2!A:A,Trang 1!A5)	1
f	=COUNTIF(Trang 2!A:A,Trang 1!A2)	4

Dường như không có logic nào cho điều này - họ không chỉ ra giá trị trước đây trong dòng đó giờ đã kết thúc ở đâu, cũng như nó đến từ đâu. Nó dường như là ngẫu nhiên và do đó kết quả đếm sai nằm cạnh mỗi af.

Tôi nhận thấy rằng một giải pháp tiềm năng cho vấn đề này là bạn sao chép và dán các giá trị trước khi sắp xếp, nhưng tôi cần lập công thức để tiếp tục hoạt động vì dữ liệu đằng sau sẽ thay đổi.

Điều kỳ lạ là tôi cũng đã tìm ra cách khắc phục nhưng nó yêu cầu bạn phải chống lại hành vi excel mặc định. Nếu bạn thay đổi công thức thành =COUNTIF(Sheet2!A:A,A2) (tức là lấy tham chiếu đến sheet1 ra thì nó đã được sửa.

1. Tôi vẫn không hiểu tại sao vấn đề lại phát sinh ngay từ đầu.

2. Excel tự động đưa tham chiếu vào sheet1 (giả sử để xây dựng công thức bạn chuyển sang sheet 2 chọn cột A, chuyển về sheet1 chọn ô A2), nên bạn phải xóa tham chiếu theo cách thủ công... đây là hành vi hơi khó chịu.

Có ai có bất kỳ hiểu biết sâu sắc về điều này?

Trả lời:

Vấn đề là, như bạn nói, là kết quả của việc bao gồm Sheet1! trong công thức.

Sort nhìn thấy tham chiếu trang tính và quyết định sai rằng đó không phải là tham chiếu đến một ô trên cùng một hàng trong dữ liệu đang được sắp xếp, do đó, nó xử lý nó như một tham chiếu đến một ô bên ngoài khu vực được sắp xếp và di chuyển công thức không thay đổi cùng với mục ở bên trái.

Tôi đồng ý rằng điều này thật khó chịu và nó cũng làm tôi khó chịu khi tên trang tính được tự động đưa vào khi bạn truy cập một trang tính khác trước đó trong công thức và sau đó quay lại trang tính mà bạn đang nhập công thức.

Giải pháp duy nhất tôi biết là xóa Sheet1 theo cách thủ công! từ công thức đầu tiên trước khi sao chép nó xuống phần còn lại của bảng.