Sử dụng Ctrl + Shift + Enter (công thức CSE) để tăng cường công thức của bạn trong Excel! Vâng, đó là sự thật - có một lớp công thức bí mật trong Excel. Nếu bạn biết ba phím kỳ diệu, bạn có thể nhận được một công thức mảng Excel duy nhất để thay thế hàng nghìn công thức khác.
95% người dùng Excel không biết về Công thức CSE. Khi hầu hết mọi người nghe thấy tên thật của họ, họ nghĩ "Điều đó nghe có vẻ không hữu ích chút nào" và không bao giờ bận tâm tìm hiểu về họ. Nếu bạn cho rằng SumIf và CountIf là tuyệt vời, bạn sẽ sớm phát hiện ra rằng các công thức Ctrl + Shift + Enter (CSE) sẽ chạy vòng tròn xung quanh SumIf và CountIf. Công thức CSE cho phép bạn thay thế 1000 ô công thức theo đúng nghĩa đen bằng một công thức duy nhất. Vâng, các đồng nghiệp của tôi về Excel Guru, có một thứ gì đó mạnh mẽ này nằm ngay dưới mũi chúng tôi và chúng tôi không bao giờ sử dụng nó.
Trước khi có SumIf, bạn có thể sử dụng công thức CSE để thực hiện điều tương tự như SumIf. Microsoft đã cung cấp cho chúng tôi SumIf và CountIf, nhưng các công thức CSE không hề lỗi thời. Ồ không, và họ có thể làm được nhiều hơn thế! Còn nếu bạn muốn làm AverageIf? Làm thế nào về GrowthIf? AveDevIf? Ngay cả MultiplyByPiAndTakeTheSquareRootIf. Bất cứ điều gì bạn có thể tưởng tượng đều có thể được thực hiện với một trong các công thức CSE này.
Đây là lý do tại sao tôi nghĩ rằng các công thức CSE không bao giờ thành công. Đầu tiên, tên thật của họ khiến mọi người sợ hãi. Thứ hai, họ yêu cầu một xử lý ngoại lai, phản trực giác để làm cho chúng hoạt động. Sau khi nhập công thức CSE, bạn không thể nhấn Enter. Bạn không thể chỉ thoát khỏi ô bằng một cú nhấp chuột vào phím mũi tên. Ngay cả khi bạn hiểu đúng công thức và nhấn phím enter, Excel vẫn cung cấp cho bạn "VALUE!" Hoàn toàn không thân thiện với người dùng. lỗi. Nó không nói, "Chà - thật là đẹp. Bạn đã đi được 99,1% con đường đến đó", mà có lẽ bạn đã từng như vậy.
Đây là bí mật: Sau khi bạn nhập công thức CSE, bạn phải giữ Ctrl và phím Shift, sau đó nhấn Enter. Lấy một ghi chú dính và viết ra giấy: Ctrl Shift Enter. Người dùng Power Excel sẽ có cơ hội sử dụng các công thức này khoảng một tháng một lần. Nếu bạn không viết Ctrl Shift Enter ngay bây giờ, bạn sẽ quên nó vào lúc có thứ gì đó xuất hiện lại.
Kiểm tra ví dụ này: Giả sử bạn chỉ muốn tính trung bình các giá trị trong cột C trong đó cột A ở vùng Đông.
Công thức trong ô A13 là một ví dụ về công thức CSE.
=AVERAGE(IF(A2:A10="East",C2:C10))
Cắm nó vào và bạn sẽ nhận được 7452,667, giá trị trung bình của chỉ các giá trị Đông. Mát mẻ? Bạn vừa tạo phiên bản của riêng bạn của hàm AverageIf không tồn tại trong Excel. Hãy nhớ: Nhấn Ctrl + Shift + Enter để nhập công thức.
Kiểm tra ví dụ tiếp theo bên dưới.
Trong ví dụ này, chúng tôi làm cho công thức CSE tổng quát hơn. Thay vì chỉ định rằng chúng tôi đang tìm kiếm "Đông", hãy cho biết bạn muốn bất kỳ giá trị nào nằm trong A13. Công thức là bây giờ =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).
Thật kỳ lạ, Excel sẽ cho phép bạn sao chép và dán các công thức CSE mà không cần bất kỳ tổ hợp phím đặc biệt nào. Tôi đã sao chép C13 thành C14: C15 và bây giờ tôi có giá trị trung bình cho tất cả các vùng.
Hệ thống máy tính lớn của chúng tôi lưu trữ Số lượng và Đơn giá, nhưng không lưu trữ giá mở rộng. Chắc chắn, thật dễ dàng để thêm một cột C và điền nó với =A2*B2và sau đó là tổng cột C, nhưng bạn không cần phải làm như vậy!
Đây, công thức CSE của chúng tôi là =SUM(A2:A10*B2:B10). Nó lấy mỗi ô trong A2: A10, nhân với ô tương ứng trong B2: B10 và tính tổng kết quả. Nhập công thức, giữ Ctrl và Shift trong khi nhấn enter và bạn sẽ có câu trả lời trong một công thức thay vì 10.
Bạn có thấy điều này có sức mạnh như thế nào không? Ngay cả khi tôi có 10.000 hàng dữ liệu, Excel sẽ lấy công thức đơn này, thực hiện 10.000 phép nhân và cho tôi kết quả.
OK, đây là quy tắc: Bạn phải nhấn Ctrl + Shift + Enter bất cứ lúc nào bạn nhập hoặc chỉnh sửa các công thức này. Không làm như vậy dẫn đến lỗi #VALUE hoàn toàn không rõ ràng! lỗi. Nếu bạn có nhiều phạm vi, tất cả chúng phải có cùng hình dạng chung. Nếu bạn có một dải ô bị trộn với các ô đơn lẻ, các ô đơn lẻ sẽ được "sao chép" trong bộ nhớ để phù hợp với hình dạng của dải ô của bạn.
Sau khi bạn nhập thành công một trong những cái này và nhìn vào nó trong thanh công thức, bạn sẽ có dấu ngoặc nhọn xung quanh công thức. Bạn không bao giờ tự nhập dấu ngoặc nhọn. Nhấn Ctrl + Shift + Enter sẽ đặt chúng ở đó.
Những công thức này rất khó để làm chủ. đau đầu khi nghĩ về chúng. Nếu tôi gặp khó khăn để nhập, tôi đi tới Công cụ> Trình hướng dẫn> Trình hướng dẫn tính tổng có điều kiện và xem qua các hộp thoại. Đầu ra của trình hướng dẫn tính tổng có điều kiện là một công thức CSE, vì vậy điều này thường có thể cung cấp cho tôi đủ gợi ý về cách nhập những gì tôi thực sự cần.
Bạn có phải học CƠ BẢN năm lớp 11 với thầy Irwin không? Hoặc tệ hơn, bạn đã nhận được điểm "D" trong đại số tuyến tính với Bà Duchess ở trường đại học? Nếu vậy, bạn đang ở trong một công ty tốt và sẽ rùng mình bất cứ khi nào bạn nghe thấy từ "mảng". - Tôi chạy la hét về hướng khác mỗi khi có ai nói mảng. Tôi hiểu chúng, nhưng đây là Excel, tôi không cần mảng ở đây !. Bí mật độc ác là Excel và Microsoft gọi những công thức này là "công thức mảng". Dừng lại - đừng chạy trốn. Nó là OK, thực sự. đã đổi tên chúng là các công thức CSE vì nó nghe có vẻ ít đáng sợ hơn và vì cái tên giúp bạn nhớ cách nhập chúng - Ctrl Shift Enter. Tôi chỉ đề cập đến tên thật ở đây trong trường hợp bạn tình cờ gặp ai đó từ Microsoft, người chưa bao giờ có Bà Duchess cho đại số tuyến tính, hoặc, trong trường hợp bạn quyết định xem xét các tệp Trợ giúp. Nếu bạn giúp đỡ,tìm kiếm dưới bí danh xấu là "công thức mảng", nhưng giữa chúng ta là bạn bè, hãy gọi họ là CSE - OK?
Đọc Sử dụng Công thức Mảng Excel Đặc biệt để Mô phỏng SUMIF với Hai Điều kiện.
