John có một bảng tính khổng lồ bao gồm dữ liệu hàng ngày trong khoảng sáu mươi năm. Anh ấy muốn đưa ra một công thức sẽ tính toán các giá trị trung bình cho mỗi tháng trong khoảng thời gian được bao phủ bởi dữ liệu.

Trước khi đề xuất giải pháp, tốt nhất bạn nên đặt ra một số giả định. Với mục đích của mẹo này, hãy giả sử rằng dữ liệu hàng ngày nằm trong cột A và B. Trong cột A là các ngày được kết hợp với dữ liệu và trong cột B là giá trị dữ liệu thực tế cho mỗi ngày đó. Hơn nữa, các ô A1 và B1 chứa các tiêu đề cho mỗi cột. Điều này có nghĩa là dữ liệu thực tế xấp xỉ trong phạm vi A2: B22000.

Để làm cho các công thức của bạn dễ sử dụng hơn, bạn nên xác định tên cho dữ liệu trong cả hai cột A và B. Chọn phạm vi ngày (ví dụ: A2: A22000) và gán cho nó một tên chẳng hạn như AllDates. Chọn phạm vi dữ liệu tương ứng (ví dụ: B2: B22000) và sử dụng quy trình tương tự để tạo tên chẳng hạn như AllData.

Bạn có thể sử dụng công thức mảng để tính giá trị trung vị thực tế. Điều này liên quan đến việc thiết lập một bảng dữ liệu khác để chứa các trung bình. Đặt các tiêu đề như “Tháng” vào ô E1 và “Trung vị” trong ô F1. Trong ô E2, hãy đặt ngày đầu tiên của tháng đầu tiên trong tập dữ liệu của bạn, chẳng hạn như 1/1/1940. Trong ô E3 đặt một ngày sau đó một tháng, chẳng hạn như 2/1/1940. Chọn hai ô này (E2: E3) và kéo chốt điền xuống cho số tháng bạn muốn trong bảng dữ liệu.

Nếu không có khoảng trống nào trong dữ liệu sáu mươi năm của bạn, hãy nhập công thức sau vào ô F2:

=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))

Hoàn thiện công thức bằng cách nhấn Ctrl + Shift + Enter để cho Excel biết rằng đây là công thức mảng. Sau đó, bạn có thể sao chép công thức trong F2 vào mỗi ô trong cột F có một tháng tương ứng trong cột E. Công thức phân tích các ngày trong cột B và nếu năm và tháng bằng với bất kỳ ngày nào bạn đặt trong ô E2 , sau đó giá trị trung bình được tính từ tất cả các điểm dữ liệu tương ứng.

Nếu có khoảng trống trong dữ liệu sáu mươi năm của bạn (ngày trong cột A không có giá trị tương ứng trong cột B), thì công thức sẽ xử lý khoảng trống như thể nó là giá trị 0. Nếu bạn có khoảng trắng, điều này có thể dẫn đến các trung bình bị lệch. Để giải quyết vấn đề này, bạn có thể sử dụng một công thức mảng khác để kiểm tra và bỏ qua bất kỳ giá trị trống nào:

=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))

Có một lưu ý khi sử dụng công thức mảng theo cách này. Nếu bạn có sáu mươi năm dữ liệu, với khoảng 22.000 giá trị riêng lẻ, thì đó vẫn là nhiều tháng: khoảng 720 trong số đó. Điều đó có nghĩa là bạn đang tạo 720 công thức mảng, mỗi công thức phân tích 22.000 giá trị dữ liệu để đi đến câu trả lời. Đó là rất nhiều tính toán đang diễn ra, vì vậy bạn sẽ nhận thấy khả năng phản hồi của Excel chậm lại bất cứ khi nào nó tính toán lại trang tính.

Nếu sự chậm chạp trở thành một vấn đề, thì bạn có thể xem xét tổ chức lại dữ liệu ban đầu của mình để mỗi hàng của trang tính đại diện cho một tháng riêng lẻ. Cột A có thể chứa tháng cho hàng (1/1/1940, 2/1/1940, 3/1/1940, v.v.) và cột B: AF sẽ là các ngày từ 1 đến 31 cho mỗi tháng. Sau đó, các ô giao nhau trong bảng có thể chứa điểm dữ liệu cho mỗi ngày trong tháng và bạn có thể sử dụng hàm MEDIAN trong cột AG để tính giá trị trung bình cho mỗi tháng. Điều này vẫn dẫn đến 720 công thức, nhưng đây là những công thức thông thường mà mỗi công thức chỉ cần xử lý dữ liệu giá trị của một tháng thay vì công thức mảng cần xử lý mỗi sáu mươi năm dữ liệu. Kết quả là tính toán nhanh hơn nhiều.

Tất nhiên, đối với hầu hết mọi người, ý tưởng sắp xếp lại một lượng dữ liệu khổng lồ như vậy là đủ để khiến bạn thức trắng đêm. Thay vào đó, bạn có thể thực hiện một cách tiếp cận hoàn toàn khác để phân tích dữ liệu. Phương pháp này có thể thực hiện được vì giá trị trung vị là một hàm thống kê rất dễ tính toán theo cách thủ công. Bạn chỉ cần sắp xếp tập dữ liệu của mình và nếu số lượng mục trong tập dữ liệu là số lẻ, hãy chọn mục ở giữa. Nếu số lượng mặt hàng là số chẵn, thì bạn lấy giá trị trung bình của hai mặt hàng ở giữa.

Để sẵn sàng phân tích dữ liệu, có một số việc cần làm.

Đầu tiên, sẽ rất hữu ích nếu có một số cách để xác định duy nhất tháng của mỗi điểm dữ liệu. Trong ô C2, đặt công thức sau:

=100*Year(A2)+Month(A2)

Điều này dẫn đến các giá trị như 194001, 194002, 194003, v.v. được lưu trữ trong cột C. Đây là giá trị tháng duy nhất. Bây giờ, bạn nên sắp xếp dữ liệu theo cột C và sau đó theo cột B. Cả hai cách sắp xếp phải theo thứ tự tăng dần, để bạn kết thúc với dữ liệu của mình trước tiên được sắp xếp theo năm / tháng và sau đó theo giá trị trong năm / tháng.

Tiếp theo, bạn cần thêm tổng phụ vào dữ liệu của mình. Chọn Tổng phụ từ menu Dữ liệu, sẽ hiển thị hộp thoại Tổng phụ. Bạn muốn thêm tổng phụ tại mỗi thay đổi trong cột C. Hàm cần sử dụng là Đếm và bạn muốn thêm tổng phụ vào cột B. Khi bạn nhấp vào OK, bạn sẽ có 720 tổng phụ, một tổng cho mỗi tháng trong dữ liệu phạm vi, mỗi phạm vi hiển thị số lượng mục dữ liệu có trong tháng đó.

Để lấy giá trị trung bình, hãy thêm công thức vào ô D2:

IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")

Công thức kiểm tra những gì có trong ô B2 và nếu nó có chứa từ “Đếm”, thì nó biết rằng đây là một hàng tổng phụ. Trong trường hợp đó, nó sẽ kiểm tra xem số lượng trong ô C2 là lẻ hay chẵn. Nếu nó là số lẻ, thì hàm INDIRECT được sử dụng để lấy bất kỳ giá trị trung bình nào của tháng. Nếu nó là số chẵn, thì hai giá trị giữa của tháng được cộng lại với nhau và chia đôi.

Kết quả là bây giờ bạn có các giá trị trung bình cho mỗi tháng, ở cùng hàng với tổng phụ của bạn. Do đó, bạn có thể thu gọn đường viền cho dữ liệu (nhấp vào dấu cộng trong vùng phác thảo ở bên trái dữ liệu của bạn)

để bạn chỉ hiển thị các hàng tổng phụ đó.

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

Mẹo này (3086) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: