Hướng dẫn how do you edit an array formula in excel? - làm thế nào để bạn chỉnh sửa một công thức mảng trong excel?
|
Bạn đang cố gắng chỉnh sửa một công thức mảng và bạn dừng lại trong các bản nhạc của mình. Bạn nhấp vào công thức trong ô hoặc thanh công thức và bạn không thể thay đổi một điều. Công thức mảng là một trường hợp đặc biệt, vì vậy một trong những điều sau đây: Nếu bạn đã nhập một công thức mảng đơn bào, chọn ô, nhấn F2, thực hiện các thay đổi của bạn và sau đó nhấn Ctrl+Shift+Enter .. Nếu bạn đã nhập một công thức mảng đa tế bào, hãy chọn tất cả các ô chứa nó, nhấn F2 và tuân theo các quy tắc sau: Bạn không thể di chuyển các ô riêng lẻ có chứa công thức của bạn, nhưng bạn có thể di chuyển tất cả chúng thành một nhóm và các tham chiếu ô trong công thức sẽ thay đổi với chúng. Để di chuyển chúng, chọn tất cả các ô, nhấn Ctrl+X, chọn vị trí mới và nhấn Ctrl+V. Bạn không thể xóa các ô trong một công thức mảng (bạn sẽ thấy lỗi "bạn không thể thay đổi một phần của mảng"), nhưng bạn có thể xóa toàn bộ công thức và bắt đầu lại. Bạn không thể thêm các ô mới vào một khối các ô kết quả, nhưng bạn có thể thêm dữ liệu mới vào bảng tính của mình và sau đó mở rộng công thức của mình. Sau khi bạn thực hiện các thay đổi của mình, nhấn Ctrl+Shift+Enter. Cuối cùng, bạn có thể tiết kiệm thời gian nếu bạn sử dụng các hằng số mảng Phần của một công thức mảng mà bạn nhập vào thanh công thức. Nhưng, họ cũng có một vài quy tắc chỉnh sửa và sử dụng. Để biết thêm về chúng, hãy xem Sử dụng các hằng số mảng trong các công thức mảng.
Cần sự giúp đỡ nhiều hơn?Trong hướng dẫn này, bạn sẽ tìm hiểu công thức mảng Excel là gì, làm thế nào để nhập chính xác vào bảng tính của bạn và cách sử dụng các hằng số mảng và các hàm mảng. Các công thức mảng trong Excel là một công cụ cực kỳ mạnh mẽ và là một trong những công cụ khó thành thạo nhất. Một công thức mảng duy nhất có thể thực hiện nhiều tính toán và thay thế hàng ngàn công thức thông thường. Tuy nhiên, 90% người dùng chưa bao giờ sử dụng các chức năng mảng trong bảng tính của họ chỉ vì họ sợ hãi khi bắt đầu học chúng. Thật vậy, các công thức mảng là một trong những tính năng Excel khó hiểu nhất để học. Mục đích của hướng dẫn này là làm cho đường cong học tập dễ dàng và mượt mà nhất có thể. Một mảng trong excel là gì?Trước khi chúng tôi bắt đầu các hàm và công thức mảng, hãy tìm ra thuật ngữ "mảng" có nghĩa là gì. Về cơ bản, một mảng là một tập hợp các mặt hàng. Các mục có thể là văn bản hoặc số và chúng có thể cư trú trong một hàng hoặc cột hoặc trong nhiều hàng và cột. Ví dụ: nếu bạn đặt danh sách tạp hóa hàng tuần của mình vào định dạng mảng Excel, nó sẽ giống như: {"Sữa", "trứng", "bơ", "vảy ngô"} Sau đó, nếu bạn chọn các ô A1 đến D1, hãy nhập mảng trên trước một dấu bằng (=) trong thanh công thức và nhấn Ctrl + Shift + Enter, bạn sẽ nhận được kết quả sau:CTRL + SHIFT + ENTER, you will get the following result: Những gì bạn vừa làm là tạo ra một mảng ngang một chiều. Không có gì đáng sợ cho đến nay, phải không? Công thức mảng trong Excel là gì?Sự khác biệt giữa công thức mảng và công thức thông thường là công thức mảng xử lý một số giá trị thay vì chỉ một. Nói cách khác, một công thức mảng trong Excel đánh giá tất cả các giá trị riêng lẻ trong một mảng và thực hiện nhiều tính toán trên một hoặc một số mục theo các điều kiện được thể hiện trong công thức. Không chỉ một công thức mảng có thể xử lý đồng thời một số giá trị, nó còn có thể trả về một số giá trị tại một thời điểm. Vì vậy, kết quả được trả về bởi một công thức mảng cũng là một mảng. Các công thức mảng có sẵn trong tất cả các phiên bản của Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 và Lower. Và bây giờ, nó dường như là thời điểm thích hợp để bạn tạo công thức mảng đầu tiên của bạn. Ví dụ đơn giản về công thức mảng excelGiả sử bạn có một số mặt hàng trong cột B, giá của chúng trong cột C và bạn muốn tính tổng số doanh số của tất cả các doanh số. Tất nhiên, không có gì ngăn cản bạn tính toán các phép phụ trong mỗi hàng trước với một cái gì đó đơn giản như Tuy nhiên, một công thức mảng có thể tha cho bạn những nét khóa bổ sung đó vì nó được Excel lưu trữ kết quả trung gian trong bộ nhớ thay vì trong một cột bổ sung. Vì vậy, tất cả chỉ là một công thức mảng duy nhất và 2 bước nhanh:
Ví dụ đơn giản này cho thấy công thức mảng có thể mạnh mẽ như thế nào. Khi làm việc với hàng trăm và hàng ngàn hàng dữ liệu, chỉ cần nghĩ bạn có thể tiết kiệm bao nhiêu thời gian bằng cách nhập một công thức mảng trong một ô. Tại sao sử dụng các công thức mảng trong excel?Các công thức mảng Excel là công cụ cụ thể nhất để thực hiện các tính toán tinh vi và thực hiện các nhiệm vụ phức tạp. Một công thức mảng duy nhất có thể thay thế hàng trăm công thức thông thường. Công thức mảng rất tốt cho các nhiệm vụ như:
Cách nhập công thức mảng trong Excel (Ctrl + Shift + Enter)Như bạn đã biết, sự kết hợp của 3 phím Ctrl + Shift + Enter là một cảm ứng ma thuật biến một công thức thông thường thành một công thức mảng.CTRL + SHIFT + ENTER is a magic touch that turns a regular formula into an array formula. Khi nhập một công thức mảng trong Excel, có 4 điều quan trọng cần ghi nhớ:
Bởi vì tất cả các công thức mảng Excel yêu cầu nhấn Ctrl + Shift + Enter, đôi khi chúng được gọi là công thức CSE. Sử dụng phím F9 để đánh giá các phần của công thức mảngKhi làm việc với các công thức mảng trong Excel, bạn có thể quan sát cách họ tính toán và lưu trữ các mục của chúng (mảng bên trong) để hiển thị kết quả cuối cùng mà bạn nhìn thấy trong một ô. Để thực hiện việc này, chọn một hoặc một số đối số trong dấu ngoặc đơn của hàm, sau đó nhấn phím F9. Để thoát khỏi chế độ đánh giá công thức, nhấn phím ESC.F9 key. To exit the formula evaluation mode, press the Esc key. Trong ví dụ trên, để xem các tổng số của tất cả các sản phẩm, bạn chọn B2: B6*C2: C6, nhấn F9 và nhận kết quả sau.F9 and get the following result.  Ghi chú. Vui lòng chú ý rằng bạn phải chọn một phần của công thức trước khi nhấn F9, nếu không, phím F9 sẽ chỉ cần thay thế công thức của bạn bằng (các) giá trị tính toán. Please pay attention that you must select some part of the formula prior to pressing F9, otherwise the F9 key will simply replace your formula with the calculated value(s). Công thức mảng đơn bào và đa tế bào trong ExcelCông thức mảng Excel có thể trả về kết quả trong một ô hoặc trong nhiều ô. Một công thức mảng được nhập trong một loạt các ô được gọi là công thức đa tế bào. Một công thức mảng nằm trong một ô duy nhất được gọi là công thức đơn bào.multi-cell formula. An array formula residing in a single cell is called a single-cell formula. Có tồn tại một vài hàm mảng Excel được thiết kế để trả về các mảng đa tế bào, ví dụ như chuyển vị, xu hướng, tần số, kỹ lưỡng nhất, v.v. Các hàm khác, chẳng hạn như tổng, trung bình, tổng hợp, tối đa, tối thiểu, có thể tính toán các biểu thức mảng khi được nhập vào một ô duy nhất bằng cách sử dụng Ctrl + Shift + Enter.Ctrl + Shift + Enter. Các ví dụ sau đây chứng minh cách sử dụng công thức mảng đơn bào và đa tế bào. Ví dụ 1. Công thức mảng đơn bàoGiả sử bạn có hai cột liệt kê số lượng mặt hàng được bán trong 2 tháng khác nhau, giả sử các cột B và C và bạn muốn tìm thấy mức tăng doanh số tối đa. Thông thường, bạn sẽ thêm một cột bổ sung, giả sử cột D, tính toán thay đổi doanh số cho mỗi sản phẩm bằng công thức như Một công thức mảng không cần một cột bổ sung vì nó lưu trữ hoàn hảo kết quả trung gian trong bộ nhớ. Vì vậy, bạn chỉ cần nhập công thức sau và nhấn Ctrl + Shift + Enter:Ctrl + Shift + Enter:
Ví dụ 2. Công thức mảng đa tế bào trong ExcelTrong ví dụ tổng số trước, giả sử bạn phải trả 10% thuế từ mỗi lần bán và bạn muốn tính số tiền thuế cho mỗi sản phẩm với một công thức. Chọn phạm vi của ô trong một số cột trống, nói D2: D6 và nhập công thức sau vào thanh công thức:
Khi bạn nhấn Ctrl + Shift + Enter, Excel sẽ đặt một phiên bản của công thức mảng của bạn trong mỗi ô của phạm vi đã chọn và bạn sẽ nhận được kết quả sau:Ctrl + Shift + Enter, Excel will place an instance of your array formula in each cell of the selected range, and you will get the following result: Ví dụ 3. Sử dụng chức năng mảng Excel để trả về mảng đa tế bàoNhư đã đề cập, Microsoft Excel cung cấp một vài cái gọi là "hàm mảng" được thiết kế đặc biệt để hoạt động với các mảng đa tế bào. Chuyển vị là một trong những chức năng như vậy và chúng tôi sẽ sử dụng nó để chuyển đổi bảng trên, tức là chuyển đổi các hàng sang các cột.
Đây là cách bạn sử dụng một chức năng mảng trong Excel. Để tìm hiểu các đai ốc và bu lông của chuyển vị, vui lòng xem hướng dẫn này: Cách chuyển đổi trong Excel - Chuyển đổi các hàng sang cột. Cách làm việc với các công thức mảng đa tế bàoKhi làm việc với các công thức mảng đa tế bào trong Excel, hãy chắc chắn tuân theo các quy tắc này để có được kết quả chính xác:
Nếu công thức của bạn có thể trả về một mảng có số lượng phần tử thay đổi, hãy nhập nó vào một phạm vi bằng hoặc lớn hơn mảng tối đa được trả về bởi công thức và bọc công thức của bạn trong hàm iferror, như đã trình bày trong ví dụ này. Hằng số mảng excelTrong Microsoft Excel, một hằng số mảng chỉ đơn giản là một tập hợp các giá trị tĩnh. Các giá trị này không bao giờ thay đổi khi bạn sao chép một công thức vào các ô hoặc giá trị khác. Bạn đã thấy một ví dụ về một hằng số mảng được tạo từ một danh sách tạp hóa ngay từ đầu của hướng dẫn này. Bây giờ, chúng ta hãy xem những loại mảng khác tồn tại và cách bạn tạo chúng. Có 3 loại hằng số mảng: 1. Hằng số mảng ngangMột hằng số mảng ngang nằm trong một hàng. Để tạo một hằng số mảng hàng, hãy nhập các giá trị được phân tách bằng dấu phẩy và kèm theo sau đó trong niềng răng, ví dụ {1,2,3,4}. Ghi chú. Khi tạo một hằng số mảng, bạn nên nhập phần mở và đóng niềng răng bằng tay. When creating an array constant, you should type the opening and closing braces manually. Để nhập một mảng ngang trong bảng tính, chọn số lượng ô trống tương ứng theo một hàng, nhập công thức Như bạn thấy trong ảnh chụp màn hình, Excel kết thúc một hằng số mảng trong một bộ niềng răng khác, giống hệt như khi bạn vào một công thức mảng. 2. Hằng số mảng dọcMột hằng số mảng dọc nằm trong một cột. Bạn tạo nó theo cách tương tự như một mảng ngang với sự khác biệt duy nhất mà bạn phân định các mục với dấu chấm phẩy, ví dụ:
3. Hằng số mảng hai chiềuĐể tạo một mảng hai chiều, bạn tách từng hàng bằng dấu chấm phẩy và từng cột dữ liệu bằng dấu phẩy.
Làm việc với các hằng số mảng ExcelHằng số mảng là một trong những nền tảng của công thức mảng Excel. Các thông tin và mẹo sau đây có thể giúp bạn sử dụng chúng theo cách hiệu quả nhất.
Sử dụng các hằng số mảng trong các công thức excelBây giờ bạn đã quen thuộc với khái niệm hằng số mảng, hãy xem cách bạn có thể sử dụng các mảng thông tin để giải quyết các nhiệm vụ thực tế của bạn. Ví dụ 1. Sum n số lớn nhất / nhỏ nhất trong phạm viBạn bắt đầu bằng cách tạo một hằng số mảng dọc chứa bao nhiêu số tùy thích. Ví dụ: nếu bạn muốn thêm 3 số nhỏ nhất hoặc lớn nhất trong một phạm vi, hằng số mảng là {1,2,3}. Sau đó, bạn lấy chức năng lớn hoặc nhỏ, chỉ định toàn bộ phạm vi ô trong tham số đầu tiên và bao gồm hằng số mảng trong thứ hai. Cuối cùng, nhúng nó vào hàm tổng, như thế này: Tổng số 3 số lớn nhất: Tổng số 3 số nhỏ nhất: Đừng quên nhấn Ctrl + Shift + Enter vì bạn đang nhập một công thức mảng và bạn sẽ nhận được kết quả sau:Ctrl + Shift + Enter since you are entering an array formula, and you will get the following result: Theo cách tương tự, bạn có thể tính trung bình của N giá trị nhỏ nhất hoặc lớn nhất trong phạm vi: Trung bình của 3 số hàng đầu: Trung bình của 3 số dưới cùng: Ví dụ 2. Công thức mảng để đếm các ô có nhiều điều kiệnGiả sử, bạn có một danh sách các đơn đặt hàng và bạn muốn biết bao nhiêu lần một người bán nhất định đã bán các sản phẩm được đưa ra. Cách dễ nhất sẽ là sử dụng công thức Countifs với nhiều điều kiện. Tuy nhiên, nếu bạn muốn bao gồm nhiều sản phẩm, công thức đếm của bạn có thể phát triển quá lớn về kích thước. Để làm cho nó nhỏ gọn hơn, bạn có thể sử dụng Countifs cùng với tổng và bao gồm một hằng số mảng trong một hoặc một số đối số, ví dụ:
Công thức thực sự có thể trông như sau:
Mảng mẫu của chúng tôi chỉ bao gồm hai yếu tố vì mục tiêu là chứng minh cách tiếp cận. Trong các công thức mảng thực của bạn, bạn có thể bao gồm nhiều yếu tố như logic kinh doanh của bạn yêu cầu, với điều kiện là tổng thời lượng của công thức không vượt quá 8.192 ký tự trong Excel 2019 - 2007 (1.024 ký tự trong Excel 2003 và thấp hơn) và máy tính của bạn rất mạnh mẽ Đủ để xử lý các mảng lớn. Vui lòng xem các hạn chế của các công thức mảng để biết thêm chi tiết. Và đây là một ví dụ công thức mảng tiên tiến tìm thấy tổng của tất cả các giá trị khớp trong một bảng: sum và vlookup với hằng số mảng. Và hoặc các nhà khai thác trong các công thức mảng excelMột toán tử mảng cho biết công thức bạn muốn xử lý các mảng - sử dụng và hoặc logic.
Công thức mảng với và toán tửTrong ví dụ này, chúng tôi tìm thấy tổng doanh số trong đó nhân viên bán hàng là Mike và sản phẩm là táo:
Hoặc
Về mặt kỹ thuật, công thức này nhân lên các yếu tố của ba mảng trong cùng một vị trí. Hai mảng đầu tiên được biểu thị bằng các giá trị đúng và sai, là kết quả của việc so sánh A2: A9 với Mike "và B2: B9 với" Táo ". Mảng thứ ba chứa các số bán hàng từ phạm vi C2: C9. , phép nhân chuyển đổi đúng và sai thành 1 và 0, và vì nhân với 0 luôn luôn cho không, nên mảng kết quả có 0 khi cả hai điều kiện không được đáp ứng. Nếu cả hai điều kiện được đáp ứng, phần tử tương ứng từ mảng thứ ba được vào mảng cuối cùng (ví dụ: 1*1*c2 = 10). Vì vậy, kết quả của phép nhân là mảng này: {10; 0; 0; 30; 0; 0; 0; 0}. Cuối cùng, hàm tổng cộng thêm Các yếu tố của mảng và trả về kết quả của 40. Công thức mảng excel với hoặc toán tửCông thức mảng sau đây với hoặc toán tử (+) cộng tất cả doanh số khi người bán hàng là Mike hoặc sản phẩm là táo:
Trong công thức này, bạn thêm các phần tử của hai mảng đầu tiên (là điều kiện bạn muốn kiểm tra) và nhận đúng (> 0) nếu ít nhất một điều kiện đánh giá là đúng; Sai (0) khi tất cả các điều kiện đánh giá là sai. Sau đó, nếu kiểm tra xem kết quả bổ sung có lớn hơn 0 không và nếu có, tổng cộng sẽ thêm một phần tử tương ứng của mảng thứ ba (C2: C9). Mẹo. Trong các phiên bản hiện đại của Excel, không cần phải sử dụng một công thức mảng cho loại nhiệm vụ này - một công thức tổng hợp đơn giản xử lý chúng một cách hoàn hảo. Tuy nhiên, các nhà khai thác và và hoặc hoặc các nhà khai thác trong các công thức mảng có thể chứng minh hữu ích trong các tình huống phức tạp hơn, chứ đừng nói đến một môn thể dục dụng cụ rất giỏi của tâm trí :) In modern versions of Excel, there is no need to use an array formula for this kind of tasks - a simple SUMIFS formula handles them perfectly. Nevertheless, the AND and OR operators in array formulas may prove helpful in more complex scenarios, let alone a very good gymnastics of mind : ) Toán tử đôi trong công thức excel mảngNếu bạn đã từng làm việc với các công thức mảng trong Excel, rất có thể bạn đã bắt gặp một vài thứ chứa một dấu gạch ngang đôi (-) và bạn có thể đã tự hỏi nó được sử dụng để làm gì. Một dấu gạch ngang kép, được gọi về mặt kỹ thuật, toán tử Unary, được sử dụng để chuyển đổi các giá trị boolean không phải là số (Đúng / Sai) được trả về bởi một số biểu thức thành 1 và 0 mà hàm mảng có thể hiểu được.double unary operator, is used to convert non-numeric Boolean values (TRUE / FALSE) returned by some expressions into 1 and 0 that an array function can understand. Ví dụ sau đây hy vọng sẽ làm cho mọi thứ dễ hiểu hơn. Giả sử bạn có một danh sách các ngày trong Cột A và bạn muốn biết có bao nhiêu ngày xảy ra vào tháng 1, bất kể năm nào. Công thức sau đây sẽ hoạt động một điều trị:
Vì đây là công thức mảng Excel, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn thành nó.Ctrl + Shift + Enter to complete it. Nếu bạn quan tâm đến một số tháng khác, hãy thay thế 1 bằng một số tương ứng. Ví dụ, 2 là viết tắt của tháng 2, 3 có nghĩa là tháng ba, v.v. Để làm cho công thức linh hoạt hơn, bạn có thể chỉ định số tháng trong một số ô, như được trình bày trong ảnh chụp màn hình: Và bây giờ, hãy phân tích cách thức công thức mảng này hoạt động. Hàm tháng trả về tháng của mỗi ngày trong các ô A2 đến A10 được biểu thị bằng một số sê -ri, tạo ra mảng {2; 1; 4; 2; 12; 1; 2; 12; 1}. Sau đó, mỗi phần tử của mảng được so sánh với giá trị trong ô D1, là số 1 trong ví dụ này. Kết quả của sự so sánh này là một loạt các giá trị boolean đúng và sai. Như bạn nhớ, bạn có thể chọn một phần nhất định của công thức mảng và nhấn F9 để xem phần đó tương đương với những gì:F9 to see what that part equates to: Cuối cùng, bạn phải chuyển đổi các giá trị boolean này thành 1 và 0 mà hàm tổng có thể hiểu được. Và đây là những gì người vận hành đôi không cần thiết. Lần lượt là ép buộc đầu tiên đúng/sai thành -1/0, tương ứng. Unary thứ hai phủ nhận các giá trị, tức là đảo ngược dấu hiệu, biến chúng thành +1 và 0, điều mà hầu hết các hàm Excel có thể hiểu và làm việc với. Nếu bạn loại bỏ đôi đơn vị từ công thức trên, nó sẽ không hoạt động. Tôi hy vọng hướng dẫn ngắn này đã được chứng minh là hữu ích trên con đường của bạn để thành thạo các công thức mảng excel của bạn. Tuần tới, chúng tôi sẽ tiếp tục với các mảng Excel bằng cách tập trung vào các ví dụ công thức nâng cao. Hãy theo dõi và cảm ơn bạn đã đọc! Bạn cũng có thể quan tâmLàm thế nào để bạn chỉnh sửa một mảng bảng trong excel?Đây là những gì bạn cần làm ... Chọn phạm vi của các ô chứa công thức mảng hiện tại của bạn, cộng với các ô trống bên cạnh dữ liệu mới .. Nhấn F2. Bây giờ bạn có thể chỉnh sửa công thức .. Thay thế các phạm vi cũ của các ô dữ liệu bằng cái mới. .... Nhấn Ctrl+Shift+Enter .. Tại sao tôi không thể thay đổi một phần của một mảng trong excel?Trong khi đó, công thức mảng có trong tế bào đơn được gọi là công thức đơn bào.Lỗi Excel này Bạn không thể thay đổi một phần của mảng phát sinh do vấn đề trong phạm vi công thức mảng.Bây giờ bạn cần chọn toàn bộ ô từ mảng đó trước khi thực hiện bất kỳ thay đổi nào cho nó.
Mảng có thể được chỉnh sửa không?Vì vậy, một mảng cuối cùng có nghĩa là biến mảng thực sự là một tham chiếu đến một đối tượng, không thể được thay đổi để tham khảo bất cứ điều gì khác, nhưng các thành viên của mảng có thể được sửa đổi.the members of the array can be modified.
Làm thế nào để bạn loại bỏ một phần của một công thức mảng?Xóa một công thức mảng.. Nhấp vào một ô trong công thức mảng .. Trên tab Trang chủ, trong nhóm chỉnh sửa, nhấp vào Tìm & Chọn, sau đó nhấp vào Đi đến .. Nhấp vào Đặc biệt .. Nhấp vào mảng hiện tại .. Nhấn Xóa .. |
