Harold đang cố gắng đưa ra một công thức để cho anh ta biết năm năm qua, trong đó một ngày cụ thể (giả sử, ngày 10 tháng 5) xảy ra vào một ngày cụ thể trong tuần (giả sử, thứ Năm).

Có một số cách bạn có thể thực hiện để xác định thông tin này. Một cách đơn giản là, trong một trang tính mới, hãy nhập ngày bạn muốn kiểm tra (chẳng hạn như 5/10/18) vào ô A1. Ngay bên dưới ô đó, trong ô A2, hãy nhập một ngày trước đó một năm: 5/10/17. Chọn hai ô này và kéo chốt điền xuống cho tuy nhiên nhiều ô mong muốn, và bạn sẽ có một cột chứa ngày 10 tháng 5 trên các năm giảm dần.

Tiếp theo, trong ô B1, nhập công thức sau:

=WEEKDAY(A1)

Sao chép công thức này xuống cho bao nhiêu hàng tùy theo ngày tháng trong cột A.

Điều này trả về một giá trị, từ 1 đến 7, đại diện cho ngày trong tuần cho các ngày trong cột A. Vì 1 = Chủ nhật, 2 = Thứ Hai, v.v., điều đó có nghĩa là bất kỳ giá trị nào của 5 sẽ đại diện cho Thứ Năm. Do đó, bạn có thể đặt công thức này vào ô C1 và sao chép nó xuống:

=IF(B1=5,YEAR(A1),"")

Điều này “gọi ra” các năm mà ngày trong cột A là thứ Năm.

Nếu muốn, bạn chỉ cần bỏ qua việc thêm cột C và thay vào đó, sử dụng tính năng lọc để chỉ hiển thị những ngày có giá trị trong cột B là 5 (Thứ Năm).

Bạn cũng có thể sử dụng công thức mảng để tìm thông tin bạn muốn.

Đầu tiên, hãy chọn năm ô trong bất kỳ cột nào bạn muốn. Sau đó, nhập nội dung sau vào thanh Công thức:

=LARGE((WEEKDAY(DATE(ROW(1918:2018),5,10))=5)*ROW(1918:2018),ROW(1:5))

Nhập công thức bằng cách nhấn Ctrl + Shift + Enter. Kết quả là năm ô sẽ chứa năm năm qua, trong đó ngày 10 tháng Năm là thứ Năm. Bạn có thể làm cho công thức linh hoạt hơn bằng cách thay thế các phần tử chính bằng các phạm vi đã đặt tên, theo cách này:

=LARGE((WEEKDAY(DATE(ROW(1918:2018),MO,DA))=DW)*ROW(1918:2018),ROW(1:5))

Bây giờ, tất cả những gì bạn cần làm là tạo các phạm vi có tên MO (tháng), DA (ngày) và DW (ngày trong tuần). Khi bạn thay đổi các giá trị trong các phạm vi được đặt tên này, công thức mảng sẽ cập nhật năm ô để phản ánh các năm mong muốn.

Cách thứ ba để tìm thông tin là tạo một macro yêu cầu ngày bắt đầu và ngày trong tuần. Sau đó, vĩ mô có thể lùi lại từng năm một cho đến khi có được năm năm đáp ứng các tiêu chí.

Sub CalcDates()

Dim sTemp As String     Dim dBegin As Date     Dim dWork As Date     Dim J As Integer     Dim iDoW As Integer     Dim iYears(5) As Integer

sTemp = InputBox("Beginning Date?")

dBegin = CDate(sTemp)

If dBegin > 0 Then         sTemp = InputBox("Day of Week?")

sTemp = LCase(Trim(sTemp))

iDoW = 0         For J = 1 To 7             If sTemp = LCase(WeekdayName(J)) Then iDoW = J         Next J         If iDoW > 0 Then             dWork = dBegin             J = 0             While J < 5                 If Weekday(dWork) = iDoW Then                     J = J + 1                     iYears(J) = Year(dWork)

End If                 dWork = DateAdd("yyyy", -1, dWork)

Wend

sTemp = "These are the previous five years in which "

sTemp = sTemp & MonthName(Month(dBegin)) & " " & Day(dBegin)

sTemp = sTemp & " falls on a " & WeekdayName(iDoW) & ":"

sTemp = sTemp & vbCrLf & vbCrLf             For J = 5 To 1 Step -1                 sTemp = sTemp & iYears(J) & vbCrLf             Next J             MsgBox sTemp         End If     End If End Sub

Khi macro tìm thấy năm năm, nó sẽ hiển thị chúng trong một hộp thông báo.

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

Mẹo này (13529) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.