Cập nhật thông tin chi tiết về Hướng Dẫn Cách Gán Công Thức Vào Ô Và Tự Động Lấy Kết Quả Công Thức Excel Vba mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
Tự động gán công thức tính vào ô bằng VBA
Để có thể gán công thức vào trong 1 ô, chúng ta sử dụng thuộc tính Formula. Cách viết như sau:
Sub GanCongThuc()
‘Gan cong thuc vao o A7
Sheet1.Range(“A7”).Formula = “=SUM(A2:A6)”
End Sub
Công macro trên, chúng ta gán hàm SUM tính tổng các ô trong vùng A2:A6 vào ô A7
Khi thực hiện Macro này, Excel sẽ tự động tạo ra công thức =SUM(A2:A6) vào ô A7 mà chúng ta không cần phải viết bằng tay trực tiếp vào ô A7
Áp dụng được cho cả đối tượng là Cells hoặc Range
Công thức được gán phải bao gồm cả dấu bằng và đặt trong cặp dấu nháy kép
Nếu trong công thức có chứa sẵn dấu nháy kép thì phải viết 2 lần dấu nháy kép
Mở rộng:
Ngoài ra chúng ta có thể kết hợp thêm phương thức FillDown để có thể fill công thức cho nhiều ô cùng 1 lúc.
Filldown là việc sao chép công thức xuống các dòng phía dưới.
Việc này giúp chúng ta không phải gán công thức vào từng ô nếu công thức tương tự nhau. Khi làm thao tác này sẽ tiết kiệm được thời gian nếu số lượng ô cần gán công thức nhiều.
Tự động lấy kết quả của công thức trong ô bằng VBA
Khi đã gán công thức vào trong ô, chúng ta thu được kết quả của công thức đó. Nhưng làm thế nào để chỉ giữ lại kết quả mà không còn công thức nữa? Cách thực hiện như sau:
Sheet1.Range(“A7”).value là giá trị trong ô A7, thuộc Sheet1 (code name của sheet)
Như vậy chỉ với 2 bước đơn giản, chúng ta đã có thể tự động thu được kết quả trong ô mà không để lại công thức tính. Việc này thực hiện rất đơn giản và dễ làm đúng không. Nếu bạn chưa giỏi VBA thì hoàn toàn có thể sử dụng cách này.
Ứng dụng của cách làm này:
Trong những trường hợp công thức đơn giản, dễ viết.
Hạn chế công thức động, giúp file nhẹ hơn, chỉ lấy kết quả trong những thời điểm nhất định.
Giấu công thức tính không cho người khác biết (tốt hơn cách dùng Protect/ Hidden)
Tiết kiệm thời gian nếu cần tính nhiều công thức cùng lúc.
Cách ẩn và khóa công thức trong Excel 2010, 2013, 2016 Cách ẩn công thức trong Excel Chuyển công thức Excel sang hàm Vba sử dụng WorkSheetFunction
Công Thức Mảng Động Và Vùng Tràn Trong Excel
Công thức mảng động và vùng tràn trong Excel
Microsoft vừa trình làng một tính năng mới cho Excel. Tính năng này sẽ thay đổi cách ta làm việc với các công thức sau này: Các công thức mảng động (Dynamic array formula) Tính năng này cho phép trả về nhiều kết quả trên một vùng thông qua chỉ một công thức. Nó được gọi là vùng tràn (spill range). Hiện tại Excel đang có 7 hàm mảng động mà sau này chắc chắn sẽ nhiều hơn. Ta có thể sử dụng để tạo 1 list các giá trị độc nhất (loại bỏ giá trị trùng lặp), sắp xếp danh sách, lọc dữ liệu… Một điểm nữa là những hàm cũ cũng được cải tiến để áp dụng vùng tràn này.
Chúng ta sẽ tạm biệt Ctrl + Shift + Enter
Với hàm UNIQUE ta có thể tạo một list độc nhất (hay còn gọi là thao tác xóa trùng). Để dùng hàm này, chỉ cần nhập như trong hình trên thôi.
SPILL RANGE
Với công thức trên chúng ta sử dụng dấu # – đây là tham chiếu tới vùng tràn. Ký hiệu này cho phép ta tham chiếu toàn bộ vùng bằng cách đặt nó sau địa chỉ đầu tiên của ô thuộc vùng tràn đó. Đây là một ký hiệu vô cùng hữu dụng. Bạn có thể sử dụng như một tham chiếu tới nguồn dữ liệu cho công thức mảng động khác như ví dụ sử dụng SORT. Đồng thời bạn cũng có thể kết hợp với các công thức thông thường như SUM, COUNT,… hoặc tham chiếu như VLOOKUP, INDEX, MATCH… Kể cả đặt tên vùng hay data validation cũng không loại lệ – ta có thể sử dụng # để tham chiếu.
KẾT HỢP CÔNG THỨC MẢNG ĐỘNG
Công thức mảng động có thể kết hợp với nhau. Chẳng hạn như ta có thể rút ngắn ví dụ trên bằng việc đưa SORT và UNIQUE vào cùng một công thức.
Thay vì chọn cả vùng như trước, ta có thể tạo một danh sách cho data validation bằng cách đưa dấu # vào sau tham chiếu tới ô đầu tiên trong vùng tràn: Phần thú vị nhất ở đây là vùng tràn luôn tự động update. Mọi thứ đều “động”, có nghĩa là ta không phải lo tới việc kiểm soát vùng hoặc update lại công thức. Ký hiệu tham chiếu luôn bao gồm tất cả những gì được thêm vào vùng tràn.
Ngoài những hàm mảng động mới, ngay cả những hàm đang hiện hữu trong Excel cũng có thể thể hiện dưới dạng mảng động và hiển thị kết quả trong vùng tràn.
Hướng Dẫn Cách Viết Công Thức Lập Báo Cáo Chi Tiết Tự Động Trong Excel
Trình bày báo cáo khoa học, đẹp mắt trong excel
Cách để lấy nội dung báo cáo từ dữ liệu ban đầu ở bảng kê theo đúng điều kiện lập báo cáo
Chúng ta có 1 bảng kê thông tin bán hàng của từng mặt hàng theo các ngày (Bảng A2:E12), trong đó đã được sắp xếp theo thứ tự cột Tên mặt hàng.
Lập báo cáo chi tiết cho từng mặt hàng (vùng G1:J12), biết rằng khi thay đổi tên hàng thì báo cáo sẽ tự động thay đổi nội dung tương ứng với mặt hàng đó.
Bước 1: Tạo danh sách chọn tên mặt hàng
Ở bảng kê bán hàng có 3 mặt hàng là Hàng A, Hàng B, Hàng C. Do đó chúng ta có thể tạo danh sách chọn tên mặt hàng tại ô H2 như sau:
Chọn tab Data / Data Validation
Mục Allow chúng ta chọn List
Mục Data nhập nội dung như sau: Hàng A, Hàng B, Hàng C
Khi đó chúng ta tại ô H2 chúng ta có thể chọn tên hàng theo danh sách gồm 3 mặt hàng này.
Bước 2: Công thức lấy dữ liệu vào báo cáo
Bởi vì kết quả trả về là 1 mảng dữ liệu nên chúng ta cần sử dụng tới công thức mảng.
Khi viết công thức mảng, chúng ta chọn toàn bộ vùng sẽ thể hiện kết quả (G5:J12), sau đó nhập công thức và kết thúc công thức này chúng ta sẽ dùng tổ hợp phím Ctrl + Shift + Enter
Cấu trúc hàm OFFSET gồm:
=OFFSET(reference, rows, cols, [height], [width])
row: số dòng bắt đầu tính từ kết quả, ở đây tính tại dòng đầu tiên xuất hiện tên hàng nên row = 0
cols: Số cột bắt đầu tính từ kết quả, ở đây lấy kết quả bắt đầu từ cột B tới các cột khác (cột B cách cột A là 1 cột) nên cols = 1
Độ lớn của vùng cần lấy bởi hàm OFFSET sẽ là:
+) Weight (số cột): ở đây báo cáo chi tiết cần 4 cột, do đó lấy 4 cột tương ứng theo tên hàng này.
Trong đó:
Hàm Index + Match được viết như sau:
INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0))
Hàm COUNTIF được viết như sau:
COUNTIF($A$3:$A$12,$H$2)
Công thức hoàn chỉnh là:
=OFFSET(INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0)),0,1,COUNTIF($A$3:$A$12,$H$2),4)
Chúng ta có kết quả như sau:
Khi sử dụng công thức OFFSET cho vùng bảng G5:J12, tên hàng là Hàng A, chúng ta thu được 4 dòng kết quả tương ứng theo bảng kê. Như vậy là kết quả đã đúng.
Nhưng vùng bảng G5:J12 có tới 8 dòng. Vậy những dòng thừa sẽ xuất hiện lỗi #N/A
Để bẫy lỗi này, không để lỗi hiển thị thì chúng ta làm như sau:
Sử dụng chức năng Conditional Formatting:
Chọn toàn bộ bảng tính từ G5:J12
Mục Select a Rule Type chọn Format only cells that conain
Mục Format only cells with chọn Errors
Do hàm OFFSET lấy ra kết quả là 1 mảng liền nhau, do đó dữ liệu trong bảng kê (phần nguyên liệu để lập báo cáo) sẽ phải được sắp xếp theo thứ tự theo tên hàng
Các cột kết quả trong báo cáo chi tiết sẽ lấy tương ứng theo vị trí trong bảng kê tương ứng theo vị trí cột tên hàng. Do đó tùy theo vị trí các vùng dữ liệu cần lấy trong báo cáo chi tiết mà chúng ta có thể thiết lập các tham số: Row, Cols, Height, Weight trong hàm offset cho phù hợp. Để thuận lợi hơn khi làm việc này thì chúng ta đặt các cột kết quả liền kề nhau, tránh việc cột điều kiện lập báo cáo (tên hàng) nằm xen giữa các cột kết quả.
Tải về file mẫu: http://bit.ly/2IhmiYq
Video hướng dẫn chi tiết
Mẹo Sửa Lỗi Công Thức Excel Không Tính Toán Và Tự Động Cập Nhật
Trong quá trình lập công thức để tính toán giá trị nào đấy, cho dù bạn đã lập công thức đúng 100% nhưng khi Enter thì nó không hiển thị kết quả mà nó hiển thị nội dung của công thức hoặc khi bạn lập được công thức tính toán xong trên một ô sau đó bạn tiến hành kéo công thức này để áp dụng cho nhiều dữ liệu thì nó không tự động tính toán cho bạn? chẳng lẽ phải nhập công thức cho từng ô dữ liệu?
Sửa lỗi công thức Excel không tính toán
Lỗi excel không tính toán và tự động cập nhật là một tình trạng không hiếm gặp ở nhiều người. Cho dù chúng ta có nhập công thức đúng 100% thì việc khi nhấn Enter không hiển thị lên kết quả mà hiển thị nội dung của công thức đó là điều hoàn toàn có thể xảy ra.
Nguyên nhân Excel không tính toán và tự động cập nhật?
Sử dụng Microsoft Excel chắc chắn là có công thức, và đột ngột công thức excel không hoạt động. Bạn sẽ nghĩ công thức của bạn sao không tính toán? Tại sao công thức này không tự động cập nhật giá trị của nó? Excel bị hỏng hay do lỗi. Và làm cách nào để Excel của tự động tính toán và cập nhật công thức?
Sau khi bạn chắc chắn mình đã nhập đúng công thức trong Excel mà kết quả vẫn không hiển thị kết quả thì có thể là do một số nguyên nhân sau:
Định dạng ô không đúng
Nếu bạn không để ý và nhập công thức vào ô có định dạng Text thì excel không nhảy công thức dẫn đến kết quả cho ra sẽ không đúng.
Do cài đặt chế độ công thức
Nếu phần mềm Excel mà bạn đang dùng được cài đặt ở chế độ chỉ hiển thị công thức thì toàn bộ các công thức trong sheet đó sẽ chỉ hiển thị nội dung công thức mà không hiển thị kết quả vì vậy sẽ không cho ra kết quả chính xác.
Do cài đặt chế độ tính toán thủ công
Khi Excel ở chế độ tính toán thủ công thì hệ thống sẽ không thể tự động tính toán công thức được. Điều này dẫn đến tình trạng Excel không nhảy công thức.
Công thức bị lặp vòng
Excel không nhảy công thức có thể là do công thức của bạn đang bị vòng lặp dẫn đến kết quả của công thức nào cũng sẽ bằng 0.
Dùng phím tắt sửa lỗi công thức Excel
Một trong những cách phổ biến nhất để sửa lỗi excel không tự động tính toán hoặc cập nhật công thức đó là sử dụng phím tắt. Cứ sau mỗi lần nhập xong công thức thì bạn cần thêm một lần nhấn phím tắt F9 để cập nhật giá trị.
Đây là cách khá đơn giản nhưng chỉ là giải pháp tạm thời vì nhược điểm của nó là không tự động tính toán giá trị, mà chỉ tính toán sau khi bạn nhấn F9. Do đó, với file thường xuyên muốn thấy giá trị cập nhật sau khi thay đổi thì đây không phải cách hay.
Tuy nhiên với cách này có một ưu điểm có thể áp dụng được cho excel 2003, 2007, 2010, 2013.
Định dạng ô công thức để sửa lỗi công thức Excel
Nguyên nhân là do định dạng ô dữ liệu của bạn được định dạng kiểu Text vì thế khi lập công thức tính toán nó không hiển thị kết quả mà hiển thị công thức!
Lúc này bạn hãy chuyển sang định dạng khác không phải là Text như là: General, Number, … làm sao cho đúng với kiểu dữ liệu bạn cần.
Chuyển Excel về tự động tính toán
Khi bạn lập xong công thức trên một ô sau đó bạn kéo công thức này để áp dụng cho các ô còn lại, tuy nhiên khi kéo cho các ô còn lại thì giá trị các ô không thay đổi vẫn giữ nguyên giá trị của ô dữ liệu ban đầu, mặc dù công thức vẫn đúng vậy phải làm sao?
Đối với Excel 2003
Thực hiện thao tác với mục Tool rồi chọn Options thì hộp thoại Option sẽ hiện ra. Lúc này, bạn hãy chọn vào mục Caculation rồi chọn tiếp Automatic rồi nhần OK để lưu cài đặt như hình bên dưới là được.
Đối với Excel 2007 trở lên
Tắt chế độ hiển thị công thức để sửa lỗi công thức Excel
Nếu xác định được lỗi excel không sửa lỗi công thức Excel không tính toán và tự động cập nhật công thức thì hãy khắc phục bằng cách sau:
Tắt chế độ này bằng cách mở hộp thoại Options, chọn Advanced rồi bỏ dấu tích trong mục Show formulas in cells instead of their calculated results.
Thực hiện bấm phím tắt ” Ctrl + ` ” để tắt tính năng chỉ hiển thị công thức.
Loại bỏ vòng lặp để sửa lỗi công thức Excel
Khi nhân thấy giá trị trong những ô mà bạn tính toàn luôn bằng 0 thì bạn hãy kiểm tra và loại bỏ vòng lặp bằng các như sau:
Bạn di chuyển chuột đến tab Formulas, chọn Error Checking. Lúc này sẽ có một menu rthả xuống với nhiều lựa chọn khác nhau. Tại đây bạn tiến hành chọn Circular References thì Excel sẽ dẫn bạn đến ô bị vòng lặp công thức.
Hãy nhìn vào bảng Excel xem có công thức nào bị tính chéo nhau hay không. Nếu có thì chỉnh hoặc paste value 1 giá trị bị chéo là được.
//Bạn đang “phiền muộn” vì trong File Excel có rất nhiều liên kết mà không biết cách xóa toàn bộ liên kết? hãy tham khảo bài viết cách xóa tất cả các liên kết trong excel đơn giản.
Như vậy là mình đã hướng dẫn một số mẹo sửa lỗi công thức Excel không tính toán và tự động cập nhật rồi đấy.
Như bạn thấy lỗi công thức Excel không tính toán và tự động cập nhật có rất nhiều nguyên nhân phải không? tùy theo nguyên nhân bạn có thể áp dụng một số phương pháp bao gồm:
Dùng phím tắt sửa lỗi công thức Excel không tính toán và tự động cập nhật
Định dạng ô công thức sửa lỗi công thức Excel không tính toán và tự động cập nhật
Chuyển Excel về tự động tính toán để sửa lỗi công thức Excel không tính toán và tự động cập nhật
Tắt chế độ hiển thị công thức để sửa lỗi công thức Excel không tính toán và tự động cập nhật
Loại bỏ vòng lặp để sửa lỗi công thức Excel không tính toán và tự động cập nhật
Bạn đang xem bài viết Hướng Dẫn Cách Gán Công Thức Vào Ô Và Tự Động Lấy Kết Quả Công Thức Excel Vba trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!