Công thức mảng trong Excel cực kỳ mạnh mẽ. Bạn có thể thay thế hàng nghìn công thức bằng một công thức duy nhất sau khi học thủ thuật Ctrl + Shift + Enter. Ngày nay, một công thức mảng duy nhất thực hiện được 86.000 phép tính.
Triskaidekaphobia là nỗi sợ hãi của Thứ Sáu ngày 13. Chủ đề này sẽ không chữa khỏi bất cứ điều gì, nhưng nó sẽ cho bạn thấy một công thức hoàn toàn tuyệt vời thay thế cho 110.268 công thức. Trong cuộc sống thực, tôi không bao giờ phải đếm xem có bao nhiêu thứ sáu ngày 13 đã xảy ra trong đời mình, nhưng sức mạnh và vẻ đẹp của công thức này minh họa cho sức mạnh của Excel.
Nói rằng bạn có một người bạn mê tín về thứ sáu ngày 13. Bạn muốn minh họa bạn của bạn đã trải qua bao nhiêu ngày thứ Sáu trong ngày 13.
Thiết lập trang tính đơn giản bên dưới, với ngày sinh ở B1 và =TODAY()B2. Sau đó, một công thức hoang dã trong B6 đánh giá mỗi ngày mà bạn của bạn còn sống để tìm ra bao nhiêu ngày trong số đó là thứ Sáu và rơi vào ngày 13 của tháng. Đối với tôi, con số là 86. Không có gì phải sợ.
Nhân tiện, 17/2/1965 thực sự là sinh nhật của tôi. Nhưng tôi không muốn bạn gửi cho tôi một tấm thiệp chúc mừng sinh nhật. Thay vào đó, nhân ngày sinh nhật của tôi, tôi muốn bạn để tôi giải thích cách thức hoạt động của công thức tuyệt vời đó, từng bước một.
Bạn đã bao giờ sử dụng hàm INDIRECT chưa? Khi bạn yêu cầu =INDIRECT("C3"), Excel sẽ chuyển đến C3 và trả về bất kỳ thứ gì có trong ô đó. Nhưng INDIRECT mạnh hơn khi bạn tính toán tham chiếu ô một cách nhanh chóng. Bạn có thể thiết lập một vòng quay giải thưởng trong đó ai đó chọn một chữ cái giữa A và C và sau đó chọn một số từ 1 đến 3. Khi bạn nối hai câu trả lời, bạn sẽ có một địa chỉ ô và bất cứ thứ gì ở địa chỉ ô đó đều là giải thưởng . Có vẻ như tôi đã giành được một cuốn sách ảnh thay vì ở lại resort.
Bạn có biết cách Excel lưu trữ ngày tháng? Khi Excel hiển thị cho bạn 17/2/1965, nó đang lưu trữ 23790 trong ô, vì 17/2/1965, là ngày thứ 23790 của thế kỷ 20. Ở trung tâm của công thức là một liên kết nối ngày bắt đầu và dấu hai chấm và ngày kết thúc. Excel không sử dụng ngày đã định dạng. Thay vào đó, nó sử dụng số thứ tự đằng sau hậu trường. Vì vậy, B3&":"&B4trở thành 23790: 42167. Tin hay không thì tùy, đó là tham chiếu ô hợp lệ. Nếu bạn muốn thêm mọi thứ vào hàng 3 đến hàng 5, bạn có thể sử dụng =SUM(3:5). Vì vậy, khi bạn chuyển 23790: 42167 vào hàm INDIRECT, hàm này sẽ trỏ đến tất cả các hàng.
Điều tiếp theo mà công thức giết người làm là yêu cầu ROW(23790:42167). Thông thường, bạn chuyển một ô duy nhất: =ROW(D17)là 17. Nhưng trong trường hợp này, bạn đang chuyển hàng nghìn ô. Khi bạn yêu cầu ROW(23790:42167)và hoàn thành công thức bằng Ctrl + Shift + Enter, Excel thực sự trả về mọi số từ 23790, 23791, 23792, v.v. cho đến 42167.
Bước này là bước tuyệt vời. Trong bước này, chúng tôi đi từ hai số và "bật ra" một mảng 18378 số. Bây giờ, chúng ta phải làm gì đó với mảng câu trả lời đó. Ô B9 của hình trước chỉ đếm xem chúng ta nhận được bao nhiêu câu trả lời, điều này thật nhàm chán, nhưng nó chứng tỏ rằng ROW(23790:42167)đang trả về 18378 câu trả lời.
Hãy đơn giản hóa đáng kể câu hỏi ban đầu để bạn có thể thấy những gì đang xảy ra. Trong trường hợp này, chúng tôi sẽ tìm số thứ Sáu trong tháng 7 năm 2015. Công thức hiển thị bên dưới trong B7 cung cấp câu trả lời chính xác trong B6.
Trọng tâm của công thức là ROW(INDIRECT(B3&":"&B4)). Điều này sẽ trả về 31 ngày trong tháng 7 năm 2015. Nhưng sau đó công thức sẽ chuyển 31 ngày đó vào WEEKDAY(,2)hàm. Hàm này sẽ trả về giá trị 1 cho Thứ Hai, 5 cho Thứ Sáu, v.v. Vì vậy, câu hỏi lớn là có bao nhiêu trong số 31 ngày đó trả về giá trị 5 khi được truyền vào WEEKDAY(,2)hàm.
Bạn có thể xem tính toán công thức trong chuyển động chậm bằng cách sử dụng lệnh Đánh giá Công thức trên tab Công thức của dải băng.
Đây là sau khi INDIRECT chuyển đổi ngày tháng thành tham chiếu hàng.
Trong bước tiếp theo, Excel sắp chuyển 31 số vào hàm WEEKDAY. Bây giờ, trong công thức sát thủ, nó sẽ chuyển 18.378 số thay vì 31.
Đây là kết quả của 31 hàm WEEKDAY. Hãy nhớ rằng, chúng ta muốn đếm có bao nhiêu là 5.
Kiểm tra xem mảng trước đó có phải là 5 hay không trả về một loạt các giá trị Đúng / Sai. Có 5 giá trị True, một giá trị cho mỗi thứ Sáu.
Tôi không thể cho bạn thấy điều gì sẽ xảy ra tiếp theo, nhưng tôi có thể giải thích nó. Excel không thể SUM một loạt các giá trị Đúng và Sai. Nó là vi phạm các quy tắc. Nhưng nếu bạn nhân các giá trị True và False đó với 1 hoặc nếu bạn sử dụng hàm double-negative hoặc N (), bạn sẽ chuyển đổi các giá trị True thành 1 và False thành 0. Gửi các giá trị đó đến SUM hoặc SUMPRODUCT, và bạn sẽ nhận số lượng các giá trị True.
Đây là một ví dụ tương tự để đếm xem có bao nhiêu tháng có ngày 13 trong đó. Điều này thật đơn giản khi nghĩ về điều này: Mỗi tháng đều có ngày 13, vì vậy câu trả lời cho cả năm tốt hơn là 12. Excel đang thực hiện phép toán, tạo ra 365 ngày, gửi tất cả chúng đến hàm DAY () và tìm ra bao nhiêu kết thúc lên vào ngày 13 của tháng. Câu trả lời, đúng như dự đoán, là 12.
Hình tiếp theo là một trang tính thực hiện tất cả logic theo công thức duy nhất được hiển thị ở đầu chủ đề này. Tôi đã tạo một hàng cho mỗi ngày mà tôi còn sống. Trong cột B, tôi nhận được DAY () của ngày đó. Trong cột C, tôi nhận được WEEKDAY () của ngày. Trong cột D, B có bằng 13 không? Trong Cột E, C = 5? Sau đó, tôi nhân D * E để chuyển True / False thành 1/0.
Tôi đã ẩn rất nhiều hàng, nhưng tôi chỉ cho bạn ba ngày ngẫu nhiên ở giữa xảy ra là cả thứ Sáu và ngày 13.
Tổng trong F18381 bằng 86 mà công thức ban đầu của tôi đã trả về. Một dấu hiệu tuyệt vời. Nhưng trang tính này có 110.268 công thức. Công thức giết người ban đầu của tôi thực hiện tất cả logic của 110.268 công thức này trong một công thức duy nhất.
Chờ đợi. Tôi muốn làm rõ. Không có gì kỳ diệu trong công thức ban đầu trở nên thông minh và rút ngắn logic. Công thức gốc đó thực sự đang thực hiện 110.268 bước, thậm chí có thể nhiều hơn, bởi vì công thức gốc phải tính toán mảng ROW () hai lần.
Tìm cách sử dụng nó ROW(INDIRECT(Date:Date))trong cuộc sống thực và gửi cho tôi trong e-mail (pub at dot com). Tôi sẽ gửi giải thưởng cho 100 người trả lời đầu tiên. Có lẽ không phải là một kỳ nghỉ nghỉ dưỡng. Nhiều khả năng là một chiếc Big Mac. Nhưng đó là cách nó đi với giải thưởng. Rất nhiều Big Mac và không có nhiều khu nghỉ dưỡng.
Lần đầu tiên tôi thấy công thức này được đăng tại Bảng tin vào năm 2003 bởi Ekim. Tín dụng đã được trao cho Harlan Grove. Công thức này cũng xuất hiện trong cuốn sách Đây không phải là Excel, Đó là phép thuật của Bob Umlas. Mike Delaney, Meni Porat và Tim Sheets đều đề xuất mẹo trừ / trừ. SUMPRODUCT do Audrey Lynn và Steven White đề xuất. Cảm ơn tất cả.
Xem video
- Có một lớp công thức bí mật được gọi là Công thức mảng.
- Một công thức mảng có thể thực hiện hàng nghìn phép tính trung gian.
- Chúng thường yêu cầu bạn nhấn Ctrl + Shift + Enter, nhưng không phải lúc nào cũng vậy.
- Cuốn sách hay nhất về công thức mảng là Ctrl + Shift + Enter của Mike Girvin.
- INDIRECT cho phép bạn sử dụng phép nối để tạo một thứ gì đó trông giống như một tham chiếu ô.
- Ngày được định dạng độc đáo nhưng được lưu trữ dưới dạng một số ngày kể từ ngày 1 tháng 1 năm 1900.
- Việc kết hợp hai ngày sẽ trỏ đến một phạm vi hàng trong Excel.
- Yêu cầu ý
ROW(INDIRECT(Date1:Date2))chí "bật ra" một mảng gồm nhiều số liên tiếp - Sử dụng hàm WEEKDAY để tìm xem một ngày có phải là thứ Sáu hay không.
- Có bao nhiêu ngày thứ Sáu xảy ra trong tháng Bảy này?
- Để xem tính toán công thức trong chuyển động chậm, hãy sử dụng công cụ Đánh giá công thức
- Có bao nhiêu số 13 xảy ra trong năm nay?
- Có bao nhiêu thứ sáu ngày 13 xảy ra giữa hai ngày?
- Kiểm tra từng ngày để xem WEEKDAY có phải là Thứ Sáu không
- Kiểm tra từng ngày để xem NGÀY có phải là 13 không
- Nhân các kết quả đó bằng SUMPRODUCT
- Sử dụng - để chuyển đổi True / False thành 1/0
Bản ghi video
Học Excel từ podcast, tập 2026 - Công thức yêu thích của tôi trong tất cả Excel!
Podcasting toàn bộ cuốn sách này, nhấp vào "i" ở góc trên cùng bên phải để truy cập danh sách phát!
Được rồi, đây là chủ đề thứ 30 trong cuốn sách, chúng tôi nói ở cuối phần công thức hoặc ở giữa phần công thức, và tôi nói rằng tôi phải bao gồm công thức yêu thích của tôi mọi lúc. Đây chỉ là một công thức tuyệt vời, cho dù bạn có phải đếm số thứ sáu ngày 13 hay không, nó sẽ mở ra một thế giới trong một vùng bí mật của Excel được gọi là Array Formulas! Đặt ngày bắt đầu, nhập ngày kết thúc và công thức này tính toán số thứ sáu của ngày 13 xảy ra giữa hai ngày đó. Nó thực sự thực hiện năm phép tính vào mỗi ngày giữa hai ngày đó, 91895 phép tính + SUM, 91896 phép tính xảy ra bên trong một công thức nhỏ này, được thôi. Bây giờ, vào cuối tập này, bạn sẽ bị hấp dẫn bởi các công thức mảng. Tôi muốn chỉ ra,bạn tôi, Mike Girvin, có cuốn sách hay nhất về công thức mảng tên là “Ctrl + Shift” Enter ”, đây là bản in gần đây với bìa màu xanh lam, từng là bìa màu vàng và xanh lục. Bây giờ, bất kỳ cuốn nào bạn nhận được, đều là một cuốn sách tuyệt vời, nội dung giống nhau ở cả màu vàng và màu xanh lá cây.
Được rồi, chúng ta hãy bắt đầu từ bên trong của điều này, với một công thức có thể bạn chưa từng nghe gọi là INDIRECT. INDIRECT cho phép chúng ta nối hoặc theo cách nào đó xây dựng một đoạn văn bản giống như một tham chiếu ô. Được rồi, giả sử chúng ta có một vòng quay giải thưởng ở đây và tôi chỉ yêu cầu bạn chọn giữa A, B và C. Được rồi, vì vậy bạn chọn cái này và chọn C, sau đó chọn cái này và chọn 3, được rồi, và giải thưởng của bạn là nơi nghỉ dưỡng, vì đó là những gì được lưu trữ trong C3. Và công thức ở đây được nối với nhau bất kỳ thứ gì kể từ C5 và bất kỳ thứ gì trong C6 bằng cách sử dụng & và sau đó chuyển nó đến INDIRECT. Vì vậy, = INDIRECT (C5 & C6), trong trường hợp này, là C3, phải là một tham chiếu cân bằng. INDIRECT nói "Này, chúng ta sẽ đi đến C3 và trả lại câu trả lời từ đó, được chứ?" Trở lại Lotus 1-2-3, chức năng này được gọi là @@,trong Excel họ đã đổi tên nó thành INDIRECT. Được rồi, vậy là bạn có trong INDIRECT, bây giờ đây là điều tuyệt vời đang xảy ra bên trong đó.
Chúng ta có hai ngày tháng, Excel lưu trữ ngày tháng như thế nào, 17/2/1965, đó thực sự chỉ là định dạng. Nếu chúng ta đi và nhìn vào con số thực tế đằng sau đó, nó là 23790, có nghĩa là 23790 ngày kể từ ngày 1/1/1900 và 42167 ngày kể từ ngày 1/1/1980. Trên máy Mac, nó sẽ có từ ngày 1/1/1904, vì vậy ngày sẽ giảm khoảng 3000. Được rồi, đó là cách Excel lưu trữ nó, mặc dù nó hiển thị nó cho chúng ta, nhờ định dạng số này là ngày tháng, nhưng nếu chúng ta ghép B3 với a: và B4 với nhau, nó sẽ thực sự cung cấp cho chúng ta các số được lưu trữ phía sau. Vì vậy, = B3 & ”:” & B4, và nếu chúng ta chuyển nó cho INDIRECT, nó thực sự sẽ trỏ đến tất cả các hàng từ 23790 xuống 42167.
Vì vậy, có CHỈ SỐ của B6, tôi đã yêu cầu ROW của điều đó, điều đó sẽ cung cấp cho tôi cả đống câu trả lời và để tìm ra số lượng câu trả lời tôi đã sử dụng. Và để thực hiện điều này, nếu tôi chỉ nhấn Enter, nó không hoạt động, tôi phải giữ Ctrl và Shift và nhấn Enter, và xem, điều đó thêm dấu () xung quanh công thức ở đây. Nó yêu cầu Excel chuyển sang chế độ siêu công thức, chế độ công thức mảng và thực hiện tất cả các phép toán cho mọi thứ xuất hiện từ mảng đó, 18378, được rồi. Vì vậy, đó là một thủ thuật tuyệt vời, gián tiếp của date1: date2, chuyển nó vào hàm ROW và đây là một ví dụ nhỏ.
Vì vậy, chúng tôi chỉ muốn tìm xem có bao nhiêu thứ Sáu xảy ra trong tháng Bảy này, đây là ngày bắt đầu, đây là ngày kết thúc và đối với mỗi hàng trong số đó, tôi sẽ yêu cầu TUẦN. WEEKDAY cho chúng ta biết hôm nay là ngày nào trong tuần và ở đây, 2 đối số Thứ sáu sẽ có giá trị là 5. Vì vậy, tôi đang tìm câu trả lời và chúng tôi sẽ chọn công thức này, đi tới Công thức, và Đánh giá Công thức và Đánh giá Công thức là một cách tuyệt vời để xem một công thức được tính toán trong chuyển động chậm. Vì vậy, có B3, ngày 1 tháng 7, và bạn thấy nó thay đổi thành một số, và sau đó chúng ta nối dấu hai chấm, phải, có B4, sẽ thay đổi thành một số và bây giờ chúng ta nhận được văn bản, 42186: 42216. Tại thời điểm này, chúng tôi chuyển giá trị đó cho ROW và biểu thức nhỏ đơn giản đó sẽ chuyển thành 31 giá trị ngay tại đây.
Bây giờ, trong ví dụ mà tôi có mọi thứ từ năm 1965 đến năm 2015, nó sẽ hiện ra 86000 giá trị, phải không, và bạn không muốn làm điều đó và đánh giá công thức, bởi vì nó sẽ khá điên rồ, được chứ? Nhưng bạn có thể thấy điều gì đang xảy ra ở đây với ngày 31 và bây giờ tôi đang chuyển 31 ngày đó sang hàm WEEKDAY và chúng tôi nhận được 3-4-5. Vì vậy, 3 có nghĩa là đó là một ngày thứ Tư, và sau đó 4 có nghĩa là nó là một thứ năm, và sau đó 5 có nghĩa là nó là một thứ sáu. Lấy tất cả 31 giá trị đó và xem liệu chúng = 5 có phải là thứ Sáu hay không, và chúng ta sẽ nhận được một loạt các giá trị SAI và ĐÚNG, vì vậy, Thứ Tư, Thứ Năm, Thứ Sáu và 7 ô sau đó sẽ là TRUE tiếp theo, tuyệt vời!
Được rồi, trong trường hợp này chúng ta có 5 TRUE và 26 FALSE, Để cộng các giá trị đó, tôi cần chuyển FALSE thành 0 và TRUE thành 1, và một cách rất phổ biến để làm điều đó là sử dụng - . Được rồi, rất tiếc là nó không hiển thị câu trả lời ở đó, nơi chúng tôi đã thấy toàn bộ số 1 và số 0, nhưng đó thực sự là những gì sẽ xảy ra, sau đó SUMPRODUCT sẽ thêm nó và đưa chúng tôi đến số 5. Dưới đây, nếu chúng tôi muốn tính xem có bao nhiêu ngày 13 của tháng trong năm nay, từ ngày bắt đầu đến ngày kết thúc này, quá trình rất giống nhau. Mặc dù chúng ta sẽ có 365, nhưng hãy chuyển nó vào hàm DAY và kiểm tra xem có bao nhiêu là 13, được không. Đối với ví dụ 92000 hàng, bạn biết đấy, chúng ta đang tính ngày, chúng ta đang tính ngày trong tuần, kiểm tra xem liệu, DAY = 13, kiểm tra xem liệu WEEKDAY = FALSE, nhân với * này,và chỉ trong những trường hợp là Thứ Sáu, ngày 13 thì điều đó mới kết thúc là ĐÚNG. SUMPRODUCT sau đó nói "Thêm tất cả những thứ đó lên" và đó là cách chúng tôi nhận được 86, nghĩa đen là 91895 phép tính + SUM, 91896 xảy ra bên trong một công thức này, nó cực kỳ mạnh mẽ! Hãy mua cuốn sách của Mike, đó là một cuốn sách tuyệt vời, nó sẽ mở ra cả thế giới công thức Excel cho bạn, và thực ra, bạn chỉ nên mua cả hai cuốn sách. Mua sách của tôi, mua sách của Mike, và bạn sẽ có một bộ sưu tập tuyệt vời sẽ giúp bạn vượt qua phần còn lại của năm.nó sẽ mở ra toàn bộ thế giới công thức Excel cho bạn và thực sự, bạn chỉ nên mua cả hai cuốn sách. Mua sách của tôi, mua sách của Mike, và bạn sẽ có một bộ sưu tập tuyệt vời sẽ giúp bạn vượt qua phần còn lại của năm.nó sẽ mở ra toàn bộ thế giới công thức Excel cho bạn và thực sự, bạn chỉ nên mua cả hai cuốn sách. Mua sách của tôi, mua sách của Mike, và bạn sẽ có một bộ sưu tập tuyệt vời sẽ giúp bạn vượt qua phần còn lại của năm.
Được rồi, tóm lại: có một lớp công thức bí mật được gọi là công thức mảng và công thức mảng có thể thực hiện hàng nghìn phép tính trung gian. Chúng thường yêu cầu bạn nhấn Ctrl + Shift + Enter, nhưng không phải lúc nào cũng vậy, và cuốn sách hay nhất về công thức mảng là cuốn sách “Ctrl + Shift + Enter” của Mike Girvin. Được rồi, vì vậy INDIRECT cho phép bạn sử dụng phép nối để tạo thứ gì đó trông giống như một tham chiếu ô và sau đó INDIRECT chuyển đến tham chiếu ô đó. Nối hai ngày bằng dấu hai chấm sẽ trỏ đến một dải hàng trong Excel, sau đó yêu cầu ROW của CHỈ SỐ của date1: date2 sẽ xuất hiện một mảng gồm nhiều số liên tiếp, có thể là 31, có thể là 365 hoặc có thể là 85000. Kiểm tra mỗi ngày để xem WEEKDAY = Friday, kiểm tra mỗi ngày để xem DAY = 13 hay không, nhân hai mảng TRUE và FALSE bằng cách sử dụng SUMPRODUCT. Trong nhiều trường hợp, chúng tôiSẽ sử dụng - để chuyển đổi TRUE / FALSE thành 1 và 0 để cho phép SUMPRODUCT hoạt động. Đó là một công thức tuyệt vời, tôi không tạo ra nó, tôi tìm thấy nó trên bảng tin, khi tôi làm việc với nó, tôi giống như "Chà, điều này thực sự thú vị!"
Được rồi, tôi muốn cảm ơn bạn đã ghé qua, chúng ta sẽ gặp lại bạn vào lần sau với một netcast khác từ!
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2026.xlsx
