Công Thức Và Cách Sử Dụng Các Hàm Trong Excel / 2023 / Top 15 # Xem Nhiều Nhất & Mới Nhất 11/2022 # Top View | Hoisinhvienqnam.edu.vn

Sử Dụng Công Thức Và Hàm Trong Excel / 2023

Công thức là cốt lõi của bảng tính Excel, là cốt lõi và tinh túy của MS Excel, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần.

Bài này giới thiệu về cách sử dụng công thức và hàm trong excel, cũng như cách sử dụng và ý nghĩa các hàm thông dụng.

1. Giới thiệu công thức và hàm

1.1. Công thức

Công thức là cốt lõi của bảng tính Excel, là cốt lõi và tinh túy của MS Excel, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì?

Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.

Bảng thứ tự ưu tiên của các toán tử trong Excel 2007

Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc dử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn.

Ví dụ:

=Rand() hàm không có đối số

=PMT(10%,4,1000,,1 ) hàm nhiều đối số và đối số tùy chọn không bắt buộc phải có.

Trong Excel 2007 có tổng cộng 12 hàm mới: AverageIf, AverageIfs, CountIfs, IfError, SumIf và nhóm hàm Cube. Excel 2007 có các nhóm hàm chính như:

Add-In và DDE: Call, Registed.ID,…

Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue,…

Hàm dữ liệu: Dmin, Dmax, Dcount,…

Hàm ngày và thời gian: Time, Now, Date,….

Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct,…

Hàm tài chính: Npv, Pv, Fv, Rate,…

Hàm thông tin: Cell, Thông tin, IsNa,…

Hàm tham chiếu và tìm kiếm: Choose, Vlookup, Hlookup, OffSet,…

Hàm toán và lượng giác: Log, Mmult, Round,…

Hàm thống kê: Stdev, Var, CountIf,…

Hàm văn bản: Asc, Find, Text,…

Các hàm tự tạo

1.3. Nhập công thức và hàm

Nhập công thức trong Excel rất đơn giản, muốn nhập công thức vào ô nào bạn chỉ việc nhập dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Bạn có thể nhìn vào thanh Formula để thấy được trọn công thức. Một điều hết sức lưu ý khi làm việc trên bảng tính là tránh nhập trực tiếp các con số, giá trị vào công thức mà bạn nên dùng tham chiếu.

Vì dụ:

Trong ví dụ trên, ở đối số thứ nhất của hàm NPV chúng ta không nhập trực suất chiết tính 10% vào hàm mà nên tham chiếu đến địa chỉ ô chứa nó là I2, vì nếu lãi suất có thay đổi thì ta chỉ cần nhập giá trị mới vào ô I2 thì chúng ta sẽ thu được kết quả NPV mới ngay không cần phải chỉnh lại công thức.

Giả sử các ô C2:G2 được đặt tên là DongTien, và ô I2 đặt tên là LaiSuat (Xem lại cách đặt tên vùng ở bài số 1) thì trong quá trình nhập công thức bạn có thể làm như sau:

B1. Tại ô B4 nhập vào =NPV(

B2. Nhấn F3, cửa sổ Paste Name hiện ra

B3. Chọn LaiSuat và nhấn OK

B4. Nhập dấu phẩy (,) và gõ F3

B5. Chọn DongTien và nhấn OK

B6. Nhập dấu đóng ngoặc rồi nhập dấu +

B7. Nhấp chuột vào ô B2

B8. Nhấn phím Enter

Một trong những cách dễ dàng nhất để sử dụng hàm trong Excel là sử dụng thư viện hàm. Khi bạn muốn sử dụng hàm nào chỉ việc vào chọn hàm cần sử dụng. Ngoài ra bạn có thể nhấn vào nútđể gọi hộp thoại Insert Function một cách nhanh chóng và khi cần tìm hiểu về hàm này bạn chỉ cần nhấn vào Help on this function.

1.4. Tham chiếu trong công thức

Các tham chiếu sử dụng trong công thức giúp cho chúng ta khỏi tốn công sửa chữa các công thức khi các giá trị tính toán có sự thay đổi. Có 3 loại tham chiếu sau:

Lưu ý: Dấu $ trước thứ tự cột là cố định cột và trước thứ tự dòng là cố định dòng

Ví dụ: Tính thành tiền bằng Số lượng nhân Giá. Đổi sang giá trị Thành tiền sang VND. Tính tổng các cột Thành tiền và cột VND.

B3. Tại ô D15 nhập vào =Sum(D2:D14) và chép sang ô E15.

Lưu ý:

Tham chiếu đến địa chỉ ở worksheet khác nhưng cùng workbook thì có dạng Ví dụ: =A2* !A2 =A2* B4

Tham chiếu đến địa chỉ trong workbook khác thì có dạng [Tên_Workbook] Ví dụ: =A2*[Bai2.xlsx] A4 =A2*'[Bai tap 2.xlsx] A4

Khi tên Sheet hay Workbook có chứa khoản trắng để trong VD: =A2*’C:Tai lieu[Bai tap 2.xlsx]Sheet3′!A4

Thủ thuật về cách kéo để copy công thức

Thủ thuật về cách kéo để copy công thức

(1) Dùng chuột kéo phần dưới của ô công thức đến một ô cũng sử dụng cùng một công thức, sau đó thả chuột ra. (2) Nút Auto Fill Options xuất hiện và kết quả cũng hiện ra

1.5. Tính toán trong bảng (Table)

1.6. Các lỗi thông dụng và một số lưu ý

Các lỗi thông dụng

#####

Một khi dấu ##### xuất hiện trên bang tính thì nó cho biết do bị lỗi hoặc cột quá hẹp để hiển thị kết quả của nó.

#####: Cột không đủ rộng để hiện thị kết quả trong ô, nên kéo chiều rộng cho ô.

#DIV/0!

#NAME?

#N/A

#NULL!

#VALUE!

Tham chiếu vòng

Trong một số trường hợp nhập công thức thì bạn nhận được hộp thoại thông báo tham chiếu vòng (Circular Reference) vì trong công thức có sử dụng giá trị trong ô mà bạn đang nhập công thức.

Ví dụ: Tại ô A3 bạn nhập vào công thức =A1+A2+A3

Maximun Iterations: Số lần tính vòng tối đa

Maximun Change: Sự thay đổi tối đa của giá trị

Ví dụ: Ô A1 chứa số 2, ô A2 chứa số 3, ô A3 nhập vào công thức =A1+A2+A3 và với thiết lập tính vòng như trên thì kết quả ô A3 lần đầu tiên sau khi nhập công thức là 15.

1.7. Kiểm tra công thức bằng Formulas Auditing

Ví dụ: Kết quả tính NPV có lỗi như hình dưới để tìm lỗi ta vào Error Checking. Excel sẽ kiểm tra lỗi toàn bộ bảng tính và báo cáo về các ô có chứa lỗi. Trường hợp này thì B4 chứa lỗi.

Bạn muốn tìm hiểu thêm về dạng lỗi thì nhấn vào nút Help on this error

Muốn kiểm lỗi từng bước tính toán của công thức thì nhấn vào Show Calculation Steps…

Muốn hiệu chỉnh công thức thì nhấn vào Edit in Formula Bar

Nhấn Next đến lỗi kế và Previous về lỗi trước (nếu có)

Nhấn vào Option nếu muốn hiệu chỉnh tùy chọn báo lỗi của Excel.

Để kiểm tra các bước tính toán của công thức bị lỗi trên ta chọn Show Calculation Steps…(hoặc nhấn trực tiếp vào nút Evaluate Formula trong nhóm Formulas Auditing). Nhấn vào các nút Evaluate để xem các bước tính toán của công thức.

2. Hàm luận lý

Công dụng:

Trả về TRUE nếu tất cả các điều kiện thỏa (TRUE).

Cú pháp:

=AND(Logical1,Logical2,…)

Logical1,Logical2… có từ 1đến 255 điều kiện cần kiểm tra TRUE hay FALSE

2.2 Hàm FALSE

Công dụng:

Trả về giá trị FALSE (=0)

Cú pháp:

=FALSE()

Công dụng:

Trả về giá trị ở đối số thứ 2 nếu điều kiện là TRUE và trả về giá trị ở đối số thứ 3 nếu điều kiện FALSE

Cú pháp:

=IF(logical_test,value_if_true,value_if_false)

Với:

2.4 Hàm IFERROR

Công dụng: Cú pháp:

=IFERROR(value,value_if_error)

Với:

Value là biểu thức cần kiểm tra lỗi

Công dụng:

Nghịch đảo giá trị luận lý

Cú pháp:

=NOT(Logical)

Với:

Logical là giá trị hay biểu thức mà khi tính sẽ trả về TRUE hoặc FALSE

Công dụng:

Trả về TRUE chỉ cần một trong các điều kiện là TRUE

Cú pháp:

=OR(logical1,logical2,…)

Với:

Logical1,logical2,… Có từ 1 đến 255 điều kiện cần kiểmtra xem TRUE hay FALSE

2.7 Hàm TRUE

Công dụng:

Trả về giá trị TRUE (=1)

Cú pháp:

=TRUE()

Ví dụ 1: Kiểm tra ngày ở các dòng có thuộc khoảng thời gian cho trước hay không. Nếu thuộckhoảng thời gian đó thì kết quả trả về là TRUE, không thuộc thì trả về FALSE. Ví dụ 2: Tính xem tháng ở cột A thuộc Quí mấy?

Tại ô B2 nhập vào:

=IF(OR(A2=”Tháng 1″, A2=”Tháng 5″,A2=”Tháng 6″),”Quí 2″,IF ,”Quí 3″,”Quí 4″)))

Ví dụ 4: Excel phiên bản cũ cho phép lồng tối đa 7 hàm trong hàm IF (Excel 2007 cho phép lồng tới 64 lần). Giải quyết trường hợp này mời các bạn xem ví dụ: tại ô A1 nhập vào số 12 và tại ô B1 nhập vào công thức sau:

Ví dụ này không có ý nghĩa nào ngoài việc minh họa cách giải quyết vấn đề giới hạn lồng hàm. Nếu có quá nhiều điều kiện rẽ nhánh chúng ta cần nhóm lại 7 hàm lồng nhau thành một nhóm và các nhóm nối nhau là dấu +.

Ở trường hợp này kết quả trả về là 144 vì nhóm 7 hàm đầu trả về FALSE (=0) cộng với nhóm hàm sau trả về số 144.

Ví dụ 5: (Minh họa hàm If dùng tính thuế thu nhập cá nhân) Tính thuế thu nhập cá nhân theo biểu thuế sau Ví dụ 6: Xét một số ví dụ về hàm IfError. Kiểm tra các biểu thức ở cột D nhập vào có lỗi hay không, nếu có lỗi sẽ trả về chuỗi “Có lỗi” còn không thì trả về kết qảu của biểu thức. Hàm minh họa ở dạng thông thường và ở dạng hàm mãng.

3. Các hàm về dò tìm dữ liệu trong bảng Excel

Phần nầy mô tả cách tìm dữ liệu bảng bằng nhiều hàm khác nhau cài sẵn trong Microsoft Excel. Bạn có thể sử dụng các công thức khác nhau để có được kết quả tương tự.

Chức năng:

Cú pháp hàm:

VLOOKUP(lookup_value,table_array,col_index_num,option_lookup) HLOOKUP(lookup_value,table_array,col_index_num,option_lookup)

Đối với hàm Vlookup thì các tham số là:

– Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.

– Option_lookup: là tùy chọn xác định kiểu dò tìm, có 2 kiểu dò tìm:

True hoặc 1 hoặc để trống: là kiểu dò tìm tương đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm.

False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, hàm sẽ trả về #N/A.

Đối với hàm Hlookup thì các tham số như Vlookup nhưng thay vì dò tìm theo cột, lấy cột đầu tiên dò tìm thì Hlookup dò tìm theo hàng, lấy hàng đầu tiên làm hàng dò tìm.

Ví dụ về hàm HLOOKUP VÀ VLOOKUP

Cho bảng tính sau:

1. Căn cứ vào MANV và Bảng tên phòng ban, điền tên phòng ban ở cột P_BAN.

2. Căn cứ vào Chức vụ và Bảng phụ cấp chức vụ, tính tiền phụ cấp chức vụ cho cột PCCV.

Theo yêu cầu của câu số 1 thì bạn cần thực hiện hàm tại ô H4 (cột P_BAN) như sau:

Theo câu lệnh trên, Excel sẽ lấy giá trị của ô B4 (cột MANV), đem so sánh với khu vực dãy ô từ(dãy ô được khóa cứng bởi để tránh khi dùng chức năng Fill Handle cho các ô phía dưới) và sẽ lấy giá trị dòng thứ 2 trong dãy ô phù hợp với giá trị ô B4 để điền vào ôKhi xong câu lệnh và nhấp Enter bạn được kết quả như hình sau:

Bây giờ bạn dùng chức năng Fill Handle để điền cho toàn bộ dãy ô ở cột P_BAN.

Tiếp theo câu 2, ở ô I4 (cột PCCV) bạn điền nội dung hàm VLOOKUP và được kết quả như hình sau:

Tương tự câu lệnh HLOOKUP, nhưng tại câu lệnh VLOOKUP này, Excel sẽ lấy giá trị của ôso sánh với dãy ô từ B18 đến C22 (được đặt trong dấu $ để khóa cứng), kế đến lấy giá trị tương ứng của cột thứ 2 trong dãy ô này để điền vào ô I4. Xong bạn nhấp Entervà dùng chức năng Fill Handle để điền cho tất cả các ô trong cột PCCV.

3.2 Hàm Match

Bài viết này mô tả cú pháp công thức và việc sử dụng hàm MATCH trong Microsoft Excel.

Chức năng

Hàm MATCH tìm kiếm một mục đã xác định trong một phạm vi ô, rồi trả về vị trí tương đối của mục trong phạm vi đó. Ví dụ, nếu phạm vi A1:A3 có chứa các giá trị 5, 25 và 38, thì công thức

=MATCH(25,A1:A3,0)

Hãy dùng hàm MATCH thay cho một trong các hàm LOOKUP khi bạn cần biết vị trí của một mục trong một phạm vi thay vì chính mục đó.

giá trị tìm kiếm (Bắt buộc): Giá trị mà bạn muốn so khớp trong mảng tìm kiếm. Ví dụ, khi bạn tra cứu số điện thoại của một ai đó trong sổ điện thoại, bạn sẽ dùng tên của người đó làm giá trị tra cứu nhưng số điện thoại mới là giá trị mà bạn muốn tìm. Đối sốgiá trị tra cứu có thể là một giá trị (số, văn bản hoặc giá trị lô-gic) hoặc một tham chiếu ô đến một số, văn bản hoặc giá trị lô-gic.

mảng tìm kiếm (Bắt buộc): Phạm vi ô được tìm kiếm.

1 hoặc bỏ qua

Hàm MATCH tìm kiếm giá trị lớn nhất mà giá trị đó nhỏ hơn hoặc bằng giá trị tìm kiếm. Giá trị trong đối số mảng tìm kiếm phải được sắp theo thứ tự tăng dần, ví dụ: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.

0

Hàm MATCH tìm kiếm giá trị thứ nhất bằng chính xác giá trị tìm kiếm. Các giá trị trong đối số mảng tìm kiếm có thể được sắp theo bất kỳ trật tự nào.

-1

Hàm MATCH tìm kiếm giá trị nhỏ nhất mà giá trị đó lớn hơn hoặc bằng giá trị tìm kiếm. Các giá trị trong đối số mảng tìm kiếm phải được sắp theo thứ tự giảm dần, ví dụ: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2 v.v.

Ghi chú

Hàm MATCH trả về vị trí của giá trị khớp đúng trong mảng tìm kiếm, chứ không trả về chính giá trị đó. Ví dụ, MATCH(“b”,{“a”,”b”,”c”},0) trả về 2, là vị trí tương đối của “b” trong mảng {“a”,”b”,”c”}.

Hàm MATCH không phân biệt chữ hoa và chữ thường khi so khớp các giá trị văn bản.

Nếu hàm MATCH không tìm thấy giá trị khớp nào, nó trả về giá trị lỗi #N/A.

Nếu kiểu khớp là 0 và giá trị tìm kiếm là chuỗi văn bản, thì bạn có thể dùng ký tự đại diện – dấu hỏi (?) và dấu sao (*) – trong đối số giá trị tìm kiếm. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực, hãy gõ một dấu ngã (~) trước ký tự đó.

3.3 Hàm INDEX

Trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng hay vùng dữ liệu.

Cú pháp:

=INDEX(Array,Row_num,Col_num)

Các tham số:

– Array: Là một vùng chứa các ô hoặc một mảng bất biến.

Nếu Array chỉ chứa một hàng và một cột, tham số Row_num hoặc Col_num tương ứng là tùy ý.

Nếu Array có nhiều hơn một hàng hoặc một cột thì chỉ một Row_num hoặc Col_num được sử dụng.

– Row_num: Chọn lựa hàng trong Array. Nếu Row_num được bỏ qua thì Col_num là bắt buộc.

– Col_num: Chọn lựa cột trong Array. Nếu Col_num được bỏ qua thì Row_num là bắt buộc.

4. Hàm văn bản

4.1 Hàm LOWER

Chuyển đổi chữ hoa trong chuỗi văn bản thành chữ thường.

Cú pháp

LOWER(text)

Ví Dụ:

=LOWER(“Trung Tâm Tin Học”)

Sẽ cho kết quả là: trung tâm tin học

4.1 Hàm RIGHT

Hàm RIGHT trả về một hoặc nhiều ký tự cuối cùng trong một chuỗi, dựa vào số ký tự mà bạn chỉ định.

Cú pháp:

=RIGHT(text,[num_chars])

Hàm RIGHT có các đối số sau đây:

Text: Bắt buộc. Chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất.

Num_chars: Tùy chọn. Chỉ rõ sốhàm RIGHT trích xuất.

Ghi chú:

Số ký tự phải lớn hơn hoặc bằng không.

Nếu số ký tự lớn hơn độ dài của văn bản, hàm RIGHT trả về toàn bộ văn bản.

Nếu số ký tự được bỏ qua, thì nó được giả định là 1.

Ví dụ:

Hàm LEFT trả về một hoặc nhiều ký tự đầu tiên trong một chuỗi, dựa vào số ký tự mà bạn chỉ định.

Cú pháp:

Cú pháp hàm có các đối số sau đây:

Text: Bắt buộc. Chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất.

Num_chars: Tùy chọn. Chỉ rõ số LEFT trích xuất.

Num_chars phải lớn hơn hoặc bằng không.

Nếu num_chars lớn hơn độ dài của văn bản, hàm LEFT trả về toàn bộ văn bản.

Nếu num_chars được bỏ qua, thì nó được giả định là 1.

Ví dụ:

4.4 Hàm SUBSTITUTE

Thay thế text trong một chuỗi văn bản. Hãy dùng hàm SUBSTITUTE khi bạn muốn thay thế văn bản cụ thể trong chuỗi văn bản.

Cú pháp:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Cú pháp hàm SUBSTITUTE có các đối số sau đây:

Text: Bắt buộc. Văn bản hoặc tham chiếu đến ô chứa văn bản mà bạn muốn thay thế các ký tự trong đó.

Old_text: Bắt buộc. Văn bản mà bạn muốn được thay thế.

New_text: Bắt buộc. Văn bản mà bạn muốn thay thế cho old_text.

Instance_num: Tùy chọn. Xác định số lần xuất hiện của old_text mà bạn muốn thay bằng new_text. Nếu bạn xác định instance_num, thì số lần xuất hiện đó của old_text được thay thế. Nếu không, mọi lần xuất hiện của old_text trong văn bản được đổi thành new_text.

Ví dụ:

Loại bỏ tất cả khoảng trống ra khỏi văn bản, chỉ để lại một khoảng trống giữa các từ.

Cú pháp:

Cú pháp của hàm TRIM có các đối số sau đây:

Text: Bắt buộc. Văn bản bạn muốn loại bỏ các khoảng trống.

Ví dụ: =TRIM(” Thu nhập Quý Một “) sẽ cho kết quả: Thu nhập Quý Một

4.6 Hàm UPPER

Chuyển đổi văn bản thành chữ hoa.

Cú pháp:

Ví dụ:

=UPPER(“Trung Tâm tin học “) sẽ cho kết quả: TRUNG TÂM TIN HỌC

4.7 Hàm LOWER

Chuyển đổi văn bản thành chữ thường.

Cú pháp:

Ví dụ:

=LOWER(“Trung Tâm tin HỌC”) sẽ cho kết quả: trung tâm tin học

Hàm trả về giá trị là một chuỗi con được cắt ở giữa chuỗi ban đầu.

Cú pháp:

+ Chuổi: là chuổi văn bản hoặc một địa chỉ ô chứa giá trị chuổi cần cắt lấy giá trị.

+ n: Vị trí bắt đầu trích lấy chuỗi.

+ m: Số ký tự cần trích lấy chuỗi

Ví dụ:

MID( “Trung Tâm Tin Học”, 4, 2) sẽ cho kết quả là chuỗi “ng”

MID( “Trung Tâm Tin Học”, 4, 4) sẽ cho kết quả là chuỗi “ng T”

5 Các hàm toán học

Lấy giá trị tuyệt đối của một số

Cú pháp:

=ABS(Number)

Đối số Number là một giá trị số, một tham chiếu hay một biểu thức.

Ví dụ:

=ABS(40) cho kết quả là 40

=ABS(-4) cho kết quả là 4

5.2. Hàm POWER

Hàm trả về lũy thừa của một số.

Cú pháp:

=POWER(Number, Power)

Các tham số:

– Number: Là một số thực mà bạn muốn lấy lũy thừa.

– Power: Là số mũ.

Ví dụ =POWER(5,2) cho kết quả là 25

5.3. Hàm PRODUCT

Bạn có thể sử dụng hàm PRODUCT thay cho toán tử nhân * để tính tích của một dãy.

Cú pháp:

=PRODUCT(Number1, Number2…)

Các tham số: Number1, Number2… là dãy số mà bạn muốn nhân.

Lấy giá trị dư của phép chia.

Cú pháp:

=MOD(Number, Divisor)

Các đối số:

– Number: Số bị chia.

– Divisor: Số chia.

5.5. Hàm ROUNDUP

Làm tròn một số.

Cú pháp:

=ROUNDUP(Number, Num_digits)

Các tham số:

– Number: Là một số thực mà bạn muốn làm tròn lên.

– Number_digits: là bậc số thập phân mà bạn muốn làm tròn.

– Nếu Num_digits = 0 sẽ làm tròn lên số tự nhiên gần nhất.

– Nếu Num_digits < 0 sẽ làm tròn phần nguyên sau dấu thập phân.

Làm tròn lên thành số nguyên chẵn gần nhất.

Cú pháp:

=EVEN(Number)

Tham số Number là số mà bạn muốn làm tròn.

Chú ý:

– Nếu Number không phải là kiểu số thì hàm trả về lỗi #VALUE!

Làm tròn lên thành số nguyên lẻ gần nhất.

Cú pháp:

=ODD(Number)

Tham số: Number là số mà bạn muốn làm tròn.

5.8. Hàm ROUNDDOWN

Làm tròn xuống một số.

Cú pháp:

=ROUNDDOWN(Number, Num_digits)

Các tham số tương tự như hàm ROUNDUP.

6. Các hàm thống kê

A. Nhóm hàm tính tổng

Cộng tất cả các số trong một vùng dữ liệu được chọn.

Cú pháp:

=SUM(Number1, Number2…)

Các tham số: Number1, Number2… là các số cần tính tổng.

6.2. Hàm SUMIF

Tính tổng của các ô được chỉ định bởi những tiêu chuẩn đưa vào.

Cú pháp:

=SUMIF(Range, Criteria, Sum_range)

Các tham số:

– Range: Là dãy mà bạn muốn xác định.

– Sum_range: Là các ô thực sự cần tính tổng.

Ví dụ:

= SUMIF(B3:B8,”<=10″)

= SUMIF(B3:B8,”iPhone*”, C2:C8)

B. Nhóm hàm tính giá trị trung bình

6.3. Hàm AVERAGE:

Trả về gi trị trung bình của các đối số.

Cú pháp:

=AVERAGE(Number1, Number2…)

Các tham số: Number1, Number2 … là các số cần tính giá trị trung bình.

6.4. Hàm SUMPRODUCT

Lấy tích của các dãy đưa vào, sau đó tính tổng của các tích đó.

Cú pháp:

=SUMPRODUCT(Array1, Array2, Array3…)

Các tham số: Array1, Array2, Array3… là các dãy ô mà bạn muốn nhân sau đó tính tổng các tích.

Chú ý: Các đối số trong các dãy phải cùng chiều. Nếu không hàm sẽ trả về giá trị lỗi #VALUE.

C. Nhóm hàm tìm giá trị lớn nhất và nhỏ nhất

Trả về số lớn nhất trong dãy được nhập.

Cú pháp:

=MAX(Number1, Number2…)

Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị lớn nhất ở trong đó. Ví dụ.

Tìm số lớn thứ k trong một dãy được nhập.

Cú pháp:

=LARGE(Array, k)

Các tham số:

– Array: Là một mảng hoặc một vùng dữ liệu.

– k: Là thứ hạng của số bạn muốn tìm kể từ số lớn nhất trong dãy.

Trả về số nhỏ nhất trong dãy được nhập vào.

Cú pháp:

=MIN(Number1, Number2…)

Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị nhỏ nhất ở trong đó.

Tìm số nhỏ thứ k trong một dãy được nhập vào.

Cú pháp:

=SMALL(Array, k)

Các tham số:

– Array: Là một mảng hoặc một vùng của dữ liệu.

– k: Là thứ hạng của số mà bạn muốn tìm kể từ số nhỏ nhất trong dãy.

D. Nhóm hàm đếm dữ liệu

6.9. Hàm COUNT

Hàm COUNT đếm các ô chứa dữ liệu kiểu số trong dãy.

Cú pháp:

=COUNT(Value1, Value2, …)

Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

Đếm tất cả các ô chứa dữ liệu.

Cú pháp:

=COUNTA(Value1, Value2, …)

Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

Hàm COUNTIF đếm các ô chứa giá trị số theo một điều kiện cho trước.

Cú pháp:

=COUNTIF(Range, Criteria)

Các tham số:

– Range: Dãy dữ liệu mà bạn muốn đếm.

– Criteria: Là tiêu chuẩn cho các ô được đếm.

6.12. Hàm COUNTIFS

Hàm COUNTIF đếm các ô chứa giá trị số theo nhiều điều kiện cho trước.

Cú pháp:

=COUNTIFS(Range1, Criteria1, Range2, Criteria2,…)

Các tham số:

– Range1: Dãy dữ liệu mà bạn muốn áp điều kiện 1.

– Criteria1: Là tđiều kiện 1 tương ứng choDãy dữ liệu 1.

Ví dụ:

= COUNTIFS(B3:B11,”Toán”, C3:C11, “Nam”): Đếm tất cả các ô trong dãy B3:B11 có chứa chữ “Toán” và dãy C3:C11 có chứa chữ “Nam”.

7. Các hàm về xử lý ngày tháng

Hàm Date trả về một chuỗi trình bày một kiểu ngày đặc thù.

Cú pháp:

=DATE(year,month,day)

Các tham số:

– Year: miêu tả năm, có thể từ 1 đến 4 chữ số. Nếu bạn nhập 2 chữ số, theo mặc định Excel sẽ lấy năm bắt đầu là: 1900.(Ví dụ)

– Month: miêu tả tháng trong năm. Nếu month lớn hơn 12 thì Excel sẽ tự động tính thêm các tháng cho số miêu tả năm.(Ví dụ)

– Day: miêu tả ngày trong tháng. Nếu Day lớn hơn số ngày trong tháng chỉ định, thì Excel sẽ tự động tính thêm ngày cho số miêu tả tháng.(Ví dụ)

Lưu ý:

– Excel lưu trữ kiểu ngày như một chuỗi số liên tục, vì vậy có thể sử dụng các phép toán cộng (+), trừ (-) cho kiểu ngày.(Ví dụ)

Trả về ngày tương ứng với chuỗi ngày đưa vào. Giá trị trả về là một số kiểu Integer ở trong khoảng từ 1 đến 31.

Cú pháp:

=DAY(Serial_num)

Tham số Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.(Ví dụ)

7.3. Hàm MONTH

Trả về tháng của chuỗi ngày được mô tả. Giá trị trả về là một số ở trong khoảng 1 đến 12.

Cú pháp:

=MONTH(Series_num)

Tham số Series_num: Là một chuỗi ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác. (Ví dụ)

Trả về năm tương ứng với chuỗi ngày đưa vào. Year được trả về là một kiểu Integer trong khoảng 1900-9999.

Cú pháp:

=YEAR(Serial_num)

Tham số Serial_num: Là một dữ liệu kiểu ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.(ví dụ)

7.5. Hàm TODAY

Trả về ngày hiện thời của hệ thống.

Cú pháp:

=TODAY()

Hàm này không có đối số.

7.6. Hàm WEEKDAY

Trả về số chỉ thứ trong tuần.

Cú pháp:

=WEEKDAY(Serial, Return_type)

Các đối số:

– Serial: một số hay giá trị kiểu ngày.

– Return_type: chỉ định kiểu dữ liệu trả về.

8. Các hàm về xử lý thời gian

Trả về một chuỗi trình bày một kiểu thời gian đặc thù. Giá trị trả về là một số trong khoảng từ 0 đến 0.99999999, miêu tả thời gian từ 0:00:00 đến 23:59:59.

Cú pháp:

=TIME(Hour,Minute,Second)

Các tham số: Được tính tương tự ở hàm DATE.

– Hour: miêu tả giờ, là một số từ 0 đến 32767.

– Minute: miêu tả phút, là một số từ 0 đến 32767.

– Second: miêu tả giây, là một số từ 0 đến 32767.

Trả về giờ trong ngày của dữ liệu kiểu giờ đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 (12:00A.M) đến 23 (11:00P.M).

Cú pháp:

=HOUR(Serial_num)

Tham số Serial_num: Là dữ liệu kiểu Time. Thời gian có thể được nhập như:

– Kết quả của một công thức hay một hàm khác.

8.3. Hàm MINUTE

Trả về phút của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

Cú pháp:

=MINUTE(Serial_num)

Tham số Serial_num: Tương tự như trong công thức HOUR.

8.4. Hàm SECOND

Trả về giây của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

Cú pháp:

=SECOND(Serial_num)

Tham số Serial_num: Tương tự như trong công thức HOUR.

Trả về ngày giờ hiện thời của hệ thống.

Cú pháp:

=NOW()

Hàm này không có đối số.

Còn rất nhiều hàm nữa, các bạn có thể xem trong mục Insert Function của Excel 2007

Vào thư viện hàm của Excel 2007 bằng cách vào chọn hàm cần sử dụng. Ngoài ra bạn có thể nhấn vàođể gọi hộp thoại Insert Function một cách nhanh chóng và khi cần tìm hiểu về hàm này bạn chỉ cần nhấn vào Help on this function.

Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.

Công Thức Và Cách Sử Dụng Hàm Subtotal Trong Excel / 2023

Tuy nhiên, đừng lo lắng! Trong bài biết này, chúng tôi sẽ giới thiệu với các bạn cách sử dụng đầy đủ và chi tiết nhất của hàm Subtotal trong Excel.

Cách sử dụng hàm Subtotal trong Excel

Cấu trúc và cách sử dụng

– Công dụng: sử dụng một hàm tính toán do người dùng lựa chọn và áp dụng lên vùng dữ liệu được quy định.

– Cú pháp:

=SUBTOTAL(function_num, ref1, ref2,...)

Trong đó:

function_num: Các con số từ 1 đến 11 và 101 đến 111) quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.

Đối số thứ nhất của hàm function_num bắt buộc bạn phải nhớ. Tuy nhiên, từ Excel 2007+, Microsoft đã bổ sung tính năng AutoComplete giúp chúng ta lựa chọn mà không cần nhớ nữa.

ref1, ref2, ….: Các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó. Đối với Excel 2007+, bạn có thể dùng đến 254 vùng, còn với Excel 2003 trở về trước thì con số này chỉ là 29.

function_num

Hàm tính toán (Ý nghĩa của đối số function_num)

Tính toán cả giá trị ẩn (do ẩn hàng)

Không tính toán giá trị ẩn (do ẩn hàng)

Lưu ý:

– Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc, nó không được thiết kế để tính theo chiều ngang. Do đó, một số tính năng có thể không đúng khi bạn sử dụng SUBTOTAL để tính theo chiều ngang (ví dụ, ẩn cột).

– Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì các công thức này sẽ được bỏ qua để tránh tính trùng 2 lần.

– Nếu function_num từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).

– Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL đều bỏ qua khi tính toán dù function_num là bất cứ giá trị nào.

– Nếu dữ liệu tham chiếu dạng 3-D thì hàm SUBTOTAL báo lỗi # VALUE!. Ví dụ, để tính tổng giá trị các ô A1 trong các sheet liên tục từ Sheet1 đến Sheet2, nếu sử dụng tham chiếu dạng 3-D là =SUM(Sheet1:Sheet5!A1), tuy nhiên nếu áp dụng kiểu tham chiếu này cho SUBTOTAL, bạn sẽ nhận được thông báo lỗi # VALUE!

Lên đầu trang ↑

Ví dụ minh họa về hàm Subtotal

Ví dụ 1: Trong ví dụ sau đây, chúng tôi minh họa cách sử dụng hàm SUBTOTAL với đối số là 9 và 109 (hàm tính tổng) và so sánh với kết quả khi sử dụng hàm SUM.

Ví dụ 2: Như các bạn đã biết, tham chiếu 3-D tỏ ra đặc biệt hữu hiệu khi cần tổng hợp dữ liệu giữa các sheet có cấu trúc tương tự nhau.

Trong ví dụ sau, tại ô A1 của các sheet từ Sheet1:Sheet5 chứa một số tự nhiên lần lượt từ 1-5. Bằng cách sử dụng tham chiếu 3-D, chúng ta nhanh chóng có được tổng của các ô A1 trong 5 sheet này (tại ô F5).

Lên đầu trang ↑

Lời kết

Các Lỗi Thường Gặp Khi Sử Dụng Hàm (Công Thức) Trong Excel Và Cách Khắc Phục / 2023

Khi sử dụng các hàm trong Excel việc phân tích bài toán và nhập đúng công thức cho ra kết quả chính xác là điều mà tất cả chúng ta hướng tới, tuy nhiên trong nhiều trường hợp chúng ta nhập đúng công thức nhưng lại không cho ra kết quả như mong muốn thì chúng ta làm thế nào?

Đặc biệt đối với các hàm dò tìm hay tham chiếu như hàm Hookup, hàm Vlookup, Index và Match,… việc xảy ra lỗi là rất thường xuyên do nhiều nguyên nhân, vậy làm sao để tìm ra nguyên nhân gây lỗi trong các công thức excel được nhập, trong bài viết này chúng ta cùng tìm hiểu một số lỗi thường gặp khi sử dụng các hàm trong Excel và cách khắc phục.

1. Lỗi độ rộng cột: #####

* Trường hợp xảy ra : Khi bạn nhập giá trị mà khoảng rộng của cột không đủ để hiển thị.

2. Lỗi giá trị: #VALUE!

* Trường hợp xảy ra:

Bạn nhập vào công thức một chuỗi trong khi hàm yêu cầu một số hoặc một giá trị logic.

Đang nhập một hoặc chỉnh sửa các thành phần của công thức mà nhấn Enter.

Nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. Ví dụ =LEFT(A2:A5)

Xuất hiện khi trong công thức có các phép toán không cùng một kiểu dữ liệu. Chẳng hạn công thức =A1+A2, trong đó A1 có giá trị là 1, A2 là Tây Ninh.Vì 1 là một ký tự số học, còn Tây Ninh lại là một chuỗi mẫu tự nên không thực hiện được phép tính.

* Khắc phục: Để khắc phục, bạn phải chỉnh sửa cho giá trị của các phép toán có cùng một kiểu dữ liệu là ký tự số học hoặc chuỗi mẫu tự.

3. Lỗi dữ liệu: #N/A

* Trường hợp xảy ra:

Giá trị trả về không tương thích từ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH.

Dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong bảng chưa được sắp xếp.

* Nguyên nhân:

Không đồng nhất dữ liệu khi bạn sử dụng địa chỉ mảng trong Excel.

Quên một hoặc nhiều đối số trong các hàm tự tạo.

Dùng một hàm tự tạo không hợp lý.

Xảy ra khi công thức tham chiếu đến một ô trong mảng nhưng ô đó lại không có giá trị. Lỗi này thường xảy ra với các hàm tìm kiếm như VLOOKUP, HLOOKUP…

* Khắc phục: Bạn chỉ cần xem kỹ lại công thức và chỉnh sửa ô mà hàm tham chiếu tới.

4. Sai vùng tham chiếu: #REF!

* Trường hợp xảy ra: Xóa những ô đang được tham chiếu bởi công thức. Hoặc dán những giá trị được tạo ra từ công thức lên chính vùng tham chiếu của công thức đó.

* Nguyên nhân:

Đây là lỗi tham chiếu ô, chẳng hạn bạn xóa dòng hay cột mà dòng cột đó được dùng trong công thức, khi đó sẽ bị báo lỗi #REF!

Liên kết hoặc tham chiếu đến một ứng dụng không thể chạy được.

5. Lỗi chia cho 0: #DIV/0!

* Trường hợp xảy ra: Nhập vào công thức số chia là 0. Ví dụ = MOD(10,0).

* Nguyên nhân: Số chia trong công thức là một tham chiếu đến ô trống.

* Khắc phục: không để số bị chia là số 0

6. Lỗi Sai tên: #NAME!

* Trường hợp xảy ra:

Bạn dùng những hàm không thường trực trong Excel như EDATE, EOMONTH, NETWORKDAYS, WORKDAYS,… Khi đó cần phải vào menu Tools – Add-in. Đánh dấu vào tiện ích Analysis ToolPak.

Nhập sai tên một hàm. Trường hợp này xảy tra khi bạn dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm như IF thành Ì, VLOOKUP thành VLÔKUP, hoặc bạn nhớ chưa chính xác và nhập sai tên hàm,…

Dùng những ký tự không được phép trong công thức.

Nhập một chuỗi trong công thức mà không có đóng và mở dấu nháy đôi.””

Không có dấu 2 chấm : trong dãy địa chỉ ô trong công thức.

Excel không nhận diện được tên trong công thức. Ví dụ như hàm =SUMI(UU), ở hàm này sai cả tên công thức và tên mảng tham chiếu tới.

* Khắc phục: Bạn phải sửa lại tên hàm cho đúng hoặc nhập đúng mảng cần tham chiếu.

7. Lỗi dữ liệu kiểu số: #NUM!

* Trường hợp xảy ra:

Dùng một đối số không phù hợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ bạn đưa vào công thức số âm trong khi nó chỉ tính số dương.

Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về.

Dùng một hàm trả về một số quá lớn hoặc quá nhỏ so với khả năng tính toán của Excel.

Giá trị số trong công thức không hợp lệ, số quá lớn hoặc quá bé. Chẳng hạn như phép tính =99^999 sẽ bị báo lỗi #NUM!, bạn chỉ cần giảm bớt giá trị tính toán lại.

* Khắc phục: Sử dụng đối số, hàm, giá trị tính toán chuẩn xác.

8. Lỗi dữ liệu rỗng: #NULL!

* Trường hợp xảy ra

Dùng một dãy toán tử không phù hợp

Dùng một mảng không có phân cách.

Lỗi này xảy ra khi sử dụng các phép toán tử như =SUM(A1:A5 B1:B5), giữa hai hàm này không có điểm giao nhau,

* Khắc phục: Sử dụng dãy toán tử phù hợp, dùng mảng có phân cách, bạn phải xác định chính xác điểm giao nhau giữa hai hàm.

Các Hàm, Công Thức Và Những Hằng Số Mảng Trong Excel, Ví Dụ Và Hướng Dẫn Sử Dụng / 2023

Trong bài viết này, Học Excel Online sẽ hướng dẫn công thức mảng Excel, cách để nhập nó chính xác trong bảng tính của bạn, cách sử dụng các hằng số và các công thức mảng trong Excel.

Các công thức mảng trong Excel là một công cụ vô cùng hữu ích và cũng khó nhất để kiểm soát được nó. Một công thức mảng Excel đơn có thể thực hiện nhiều phép tính và thay thế hàng ngàn công thức thông thường. Tuy nhiên, ước chừng đến 90% người dùng Excel chưa bao giờ sử dụng các công thức mảng trong bảng tính, chỉ vì họ sợ phải bắt đầu học chúng từ con số 0.

Hàm mảng trong Excel là gì?

Trước khi chúng ta bắt đầu tìm hiểu về hàm và các công thức mảng Excel, thì chúng ta hãy tìm hiểu rằng thế nào là thuật ngữ “mảng”. Về cơ bản, một mảng trong Excel là một tập hợp các mục. Các mục có thể là văn bản hoặc số và chúng có thể nằm trong một hàng/cột, hoặc trong nhiều hàng và cột.

Ví dụ: nếu bạn nhập danh sách mua hàng hàng tuần của bạn vào một định dạng mảng Excel, thì nó sẽ kiểu như:

{“Milk”, “Eggs”, “Butter”, “Corn flakes”}

Sau đó, nếu bạn chọn các ô từ A1 đến D1, thì hãy nhập mảng ở trên với dấu bằng (=) trên thanh công thức và nhấn CTRL + SHIFT + ENTER, và bạn sẽ nhận được kết quả như sau:

Bây giờ, công thức mảng Excel là gì? Sự khác biệt giữa một hàm mảng và các hàm Excel thông thường khác là hàm mảng sẽ xử lý nhiều thay vì chỉ một giá trị mà thôi. Nói cách khác, một công thức mảng trong Excel sẽ đánh giá tất cả các giá trị riêng lẻ trong một mảng, và thực hiện nhiều phép tính trên một hoặc nhiều mục mà có thỏa các điều kiện được biểu thị trong công thức.

Một công thức mảng không chỉ xử lý đồng thời một số giá trị, mà nó cũng có thể cùng 1 lúc trả lại một số các giá trị. Vì thế, các kết quả trả lại bởi công thức mảng cũng là một mảng.

Công thức mảng có sẵn trong tất cả các phiên bản của Excel 2013, Excel 2010, Excel 2007 và trước đó nữa.

Và bây giờ, có vẻ như đã đến lúc bạn nên tạo công thức mảng đầu tiên cho bạn.

Ví dụ đơn giản của công thức mảng Excel

Giả sử bạn có một số mục trong cột B, và cột C biểu thị giá của chúng, bài toán của bạn là tính tổng doanh thu.

Tất nhiên, không có gì ngăn cản bạn tính các tổng phụ (Sub total) trong mỗi hàng với một công thức đơn giản như = B2 * C2, và rồi sau đó cộng các giá trị đó lại với nhau:

Chọn một ô trống và nhập vào công thức sau:

= SUM (B2: B6 * C2: C6)

Nhấn phím tắt CTRL + SHIFT + ENTER để hoàn tất công thức mảng.

Khi nhấn tổ hợp phím trên, Microsoft Excel đặt công thức trên trong {dấu ngoặc nhọn}, hay được gọi một dấu hiệu dễ nhìn thấy cho biết đó là một công thức mảng.

Công thức sẽ nhân giá trị trong mỗi hàng riêng của mảng được chỉ định (các ô từ B2 đến C6), có thêm các tổng (phụ), và kết quả cuối cùng là tổng doanh thu.

Tại sao phải sử dụng công thức mảng trong Excel?

Công thức mảng Excel là công cụ tiện dụng nhất để thực hiện các phép tính tinh vi và các tác vụ phức tạp. Một công thức mảng đơn có thể thay thế hàng trăm công thức thông thường. Công thức mảng có thể sử dụng cho các công việc như:

Tính tổng các số thỏa các điều kiện đã cho, ví dụ tính tổng N giá trị lớn nhất hoặc nhỏ nhất trong một dải.

Tính tổng các giá trị các hàng, hoặc trong hàng/cột thứ N.

Đếm các giá trị của tất cả hoặc đếm những ký tự nhất định trong một dải xác định.

Làm thế nào để nhập công thức mảng trong Excel (Ctrl + Shift + Enter)

Như bạn đã biết, sử dụng tổ hợp phím CTRL + SHIFT + ENTER là một phương thức kỳ diệu để biến một công thức thông thường thành một công thức mảng.

Khi nhập một công thức mảng trong Excel, có 4 điều quan trọng mà bạn cần ghi nhớ:

Một khi bạn đã hoàn thành gõ công thức và đồng thời nhấn các phím CTRL + SHIFT+ ENTER, thì Excel sẽ tự động đặt công thức đó trong {dấu ngoặc nhọn}. Khi bạn chọn một/các ô như vậy, bạn có thể thấy các dấu ngoặc nhọn trong thanh công thức, điều này cho bạn biết rằng có một công thức mảng trong đó.

Việc nhập các dấu ngoặc nhọn bao quanh công thức bằng tay sẽ không chuyển công thức trên sang định dạng mảng. Bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter để hoàn thành công thức mảng.

Mỗi lần bạn chỉnh sửa một công thức mảng, các dấu ngoặc nhọn sẽ biến mất và bạn phải nhấn Ctrl + Shift + Enter để lưu các thay đổi trong công thức của bạn.

Nếu bạn quên nhấn Ctrl + Shift + Enter, thì công thức của bạn sẽ hoạt động như một công thức Excel thông thường và chỉ xử lý các giá trị đầu tiên trong mảng được chỉ định.

Bởi vì tất cả các công thức mảng trong Excel đều đòi hỏi phải nhấn tổ hợp phím Ctrl + Shift + Enter, đôi khi được gọi là công thức CSE.

Sử dụng phím F9 để đánh giá các phần trong một công thức mảng:

Khi 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 chúng tính toán và lưu trữ các mục của mảng (mảng nội bộ) để hiển thị kết quả cuối cùng trong một ô. Để làm điều này, hãy chọn một hoặc nhiều đối số bên trong ngoặc đơn của một hàm, rồi sau đó nhấn phím F9. Để tắt chế độ đánh giá công thức, hãy nhấn phím Esc.

Trong ví dụ trên, để xem các tổng phụ (sub-total) của tất cả các sản phẩm, bạn cần chọn B2: B6 * C2: C6, rồi nhấn F9 và thu được kết quả như sau:

Công thức mảng với một ô và nhiều ô trong Excel

Công thức mảng có thể trả lại kết quả trong một ô hoặc trong nhiều ô. Một công thức mảng được nhập vào trong một dải ô được gọi là công thức đa ô. Công thức mảng nằm trong một ô duy nhất được gọi là công thức ô đơn.

Có một vài hàm mảng được thiết kế để trả về các mảng đa ô, ví dụ như TRANSPOSE, TREND, FREQUENCY, LINEST, v.v.

Các hàm khác, chẳng hạn như SUM, AVERAGE, AGGREGATE, MAX, MIN, có thể tính toán biểu thức mảng khi nhập vào một ô duy nhất bằng tổ hợp phím Ctrl + Shift + Enter.

Các ví dụ sau minh hoạ cách sử dụng một công thức mảng một ô và nhiều ô trong Excel.

Ví dụ 1. Một công thức mảng với ô đơn

Giả 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, ví dụ cột B và cột C, và bạn muốn tìm số doanh thu tăng lớn nhất có thể.

Thông thường, bạn sẽ thêm một cột bổ sung, ví dụ cột D, tính doanh thu thay đổi cho mỗi sản phẩm, sử dụng công thức như = C2-B2, và sau đó tìm giá trị lớn nhất trong cột bổ sung đó = MAX (D: D).

Tuy nhiên, một công thức mảng Excel không cần một cột bổ sung vì nó lưu trữ hoàn hảo tất cả các 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 mà thôi:

= MAX (C2: C6-B2: B6)

Trong ví dụ trước của hàm SUM, giả sử bạn phải trả 10% thuế cho mỗi lần bán và bạn muốn tính số tiền trả thuế cho mỗi sản phẩm chỉ với một công thức.

Trước tiên, bạn cần chọn dải ô trong một cột trống, ví như D2: D6, và nhập công thức sau vào thanh công thức:

= B2: B6 * C2: C6 * 0,1

Một khi bạn nhấn Ctrl + Shift + Enter, thì Excel sẽ đặt công thức mảng của bạn trong mỗi ô trong dải đã chọn, và bạn sẽ nhận được kết quả trả về như sau:

Như đã đề cập, thì Microsoft Excel cung cấp một vài cái gọi là “các hàm mảng” được thiết kế đặc biệt để làm việc với các mảng đa ô. Hàm TRANSPOSE là một trong những hàm như vậy và chúng tôi sẽ sử dụng nó để chuyển đổi vị trí trong bảng ở trên, tức là chuyển hàng sang cột và ngược lại.

Chọn một dải các ô trống mà bạn muốn xuất ra bảng sau khi chuyển đổi. Vì chúng ta đang chuyển đổi các hàng thành các cột, nên hãy chắc chắn rằng bạn chọn cùng một số hàng và cột như số các cột và các hàng tương ứng trong bảng nguồn (bảng gốc) bạn có. Trong ví dụ này, chúng tôi đang lựa chọn 6 cột và 4 hàng.

Nhấn F2 để vào chế độ chỉnh sửa.

Nhập công thức mảng: = TRANSPOSE (array) và nhấn Ctrl + Shift + Enter. Trong ví dụ trên, công thức là = TRANSPOSE ($A$1: $D$6).

Kết quả sẽ tương tự như sau:

Chọn dải ô mà bạn muốn xuất ra kết quả trước khi nhập công thức.

Để xóa một công thức mảng đa ô, hoặc là bạn sẽ chọn tất cả các ô chứa nó và nhấn DELETE, hoặc bạn phải chọn toàn bộ công thức trong thanh công thức, nhấn DELETE, và sau đó nhấn Ctrl + Shift + Enter.

Bạn không thể chỉnh sửa hoặc di chuyển nội dung của một ô riêng lẻ trong một công thức mảng của Excel, và bạn cũng không thể chèn các ô mới vào hoặc xóa các ô hiện tại từ một công thức mảng đa ô. Bất cứ khi nào bạn thử làm điều này, thì Microsoft Excel sẽ đều đưa ra cảnh báo You cannot change part of an array (“Bạn không thể thay đổi một phần của mảng”).

Để thu nhỏ công thức mảng, tức là áp dụng nó cho ít ô hơn, bạn cần phải xoá công thức hiện tại trước rồi mới nhập một công thức mới vào.

Để mở rộng công thức mảng, ví dụ áp dụng nó cho nhiều ô hơn, bạn cần chọn tất cả các ô có chứa công thức hiện tại cộng với các ô rỗng mà bạn muốn có công thức trong đó, rồi nhấn F2 để chuyển sang chế độ chỉnh sửa, để điều chỉnh các tham chiếu trong công thức và nhấn Ctrl + Shift + Enter để cập nhật nó.

Bạn không thể sử dụng công thức mảng đa ô trong định dạng bảng Excel.

Bạn nên nhập một công thức mảng đa ô trong một dải ô có cùng kích cỡ với mảng có kết quả được trả về. Nếu công thức mảng trong Excel của bạn chứa một mảng mà lớn hơn dải bạn đã chọn, thì những giá trị vượt quá sẽ không xuất hiện trên bảng tính. Nếu một mảng trả về bởi công thức nhỏ mà hơn dải đã chọn, lỗi # N / A sẽ xuất hiện trong các ô bổ sung.

Nếu công thức của bạn có thể trả về một mảng với một số lượng các phần tử có thể thay đổi, thì hãy nhập nó vào một dải bằng hoặc lớn hơn mảng lớn nhất mà được bởi công thức trả về và hãy lồng công thức của bạn trong hàm IFERROR.

Hằng số mảng của Excel

Trong Microsoft Excel, một hằng số mảng chỉ đơn giản là một tập các giá trị cố định. Các giá trị này không bao giờ thay đổi khi bạn sao chép công thức vào các ô hoặc các giá trị khác.

Tồn tại 3 loại của hằng số mảng trong Excel:

1. Hằng số của mảng ngang

Hằng số của mảng ngang được đặt chỉ 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 cách nhau bởi dấu phẩy, và đặt chúng trong dấu ngoặc nhọn, ví dụ: {1,2,3,4}

Lưu ý. Khi tạo một hằng số mảng, bạn phải nhập bằng tay các dấu đóng và mở ngoặc. Để nhập một mảng theo chiều ngang trong một bảng tính, hãy chọn số tương ứng của các ô trống trong một hàng, và nhập công thức = {1,2,3,4} trong thanh công thức, rồi kế tiếp nhấn Ctrl + Shift + Enter. Kết quả sẽ giống như sau:

2. Hằng số của mảng dọc:

Hằng số của mảng dọc được đặt trong một cột. Bạn tạo ra nó bằng cách tương tự như một mảng ngang, với sự khác biệt duy nhất là bạn ngăn cách các mục bằng dấu chấm phẩy, ví dụ: = {11; 22; 33; 44}

Để tạo một mảng hai chiều trong Excel, bạn tách các mục trong mỗi hàng ra bằng dấu phẩy, và ngăn cách mỗi cột bằng dấu chấm phẩy = {“a”, “b”, “c”; 1, 2, 3}

1. Các thành phần của một hằng số mảng

Hằng số của mảng có thể chứa các con số, giá trị văn bản, giá trị Booleans (TRUE và FALSE) và các giá trị lỗi, chúng được ngăn tách bằng dấu phẩy hoặc dấu chấm phẩy.

Bạn có thể nhập một giá trị số dưới dạng số nguyên, thập phân, hoặc ký hiệu khoa học. Nếu bạn sử dụng các giá trị văn bản, chúng sẽ được đặt trong dấu nháy kép (“”) như trong bất kỳ công thức Excel nào.

Hằng số mảng không thể bao gồm các mảng, tham chiếu ô, dải, ngày tháng, tên xác định, công thức, hoặc các hàm trong Excel khác.

2. Hằng số mảng được đặt tên:

Để làm cho mảng trở nên dễ sử dụng hơn, hãy đặt cho nó một cái tên:

Nhập tên bạn muốn đặt vào ô Name

Trong hộp Refers to, nhập các mục của mảng, mà được đặt trong dấu ngoặc nhọn, với dấu bằng ở trước (=). Ví dụ:

= {“Su”, “Mo”, “Tu”, “We”, “Th”, “Fr”, “Sa”}

Nhấn OK để lưu mảng đã đặt tên và đóng cửa sổ.

Để nhập hằng số của mảng đã được đặt tên trong một trang tính, hãy chọn nhiều ô trong một hàng hoặc cột là các mục trong mảng của bạn, rồi nhập tên của mảng vào thanh công thức với dấu = đằng trước và nhấn Ctrl + Shift + Enter.

Kết quả đưa ra như sau:

Nếu mảng của bạn không hoạt động được, hãy kiểm tra các vấn đề sau:

Ngăn cách các thành phần của hằng số mảng với các ký tự thích hợp – dấu phẩy với hằng số của mảng ngang và dấu chấm phẩy với các mảng dọc.

Chọn một dải có các ô đúng với số lượng các mục trong hằng số mảng của bạn. Nếu bạn chọn nhiều ô hơn, thì mỗi ô phụ sẽ có lỗi # N / A. Nếu ít hơn, thì chỉ một phần của mảng sẽ được xuất ra.

Sử dụng hằng số mảng trong công thức của Excel

Ví dụ 1. Cộng N các số lớn nhất / nhỏ nhất trong một dải

Bạn bắt đầu bằng cách tạo một mảng dọc có chứa những số bạn muốn tính tổng. Ví dụ: nếu bạn muốn cộng 3 số nhỏ nhất hoặc lớn nhất trong một dải, thì hằng số mảng sẽ là {1,2,3}.

Sau đó, bạn sử dụng một trong hai hàm công thức sau LARGE hoặc SMALL, xác định toàn bộ dải của ô trong tham số đầu tiên và đặt hằng số mảng trong tham số thứ hai. Cuối cùng, lồng nó vào hàm SUM, như sau:

Tính tổng 3 số lớn nhất: = SUM (LARGE (range, {1,2,3}))

Tính tổng nhỏ nhất 3 số: = SUM (SMALL (range, {1,2,3}))

Đừng quên nhấn Ctrl + Shift + Enter khi bạn nhập công thức mảng, và bạn sẽ nhận được kết quả như sau:

Trung bình của 3 số lớn nhất: = AVERAGE (LARGE (range, {1,2,3}))

Trung bình của 3 số nhỏ nhất: = AVERAGE (SMALL (range, {1,2,3}))

Ví dụ 2. Công thức mảng để tính các ô thỏa nhiều điều kiện

Giả sử bạn có một danh sách đơn đặt hàng và bạn muốn biết người bán hàng đã bán được bao nhiêu lần sản phẩm được chỉ định.

Cách đơn giản nhất sẽ sử dụng một công thức COUNTIFS với nhiều điều kiện. Tuy nhiên, nếu bạn muốn đưa nhiều sản phẩm vào, thì công thức COUNTIFS của bạn có thể sẽ quá dài. Để làm cho nó nhỏ gọn hơn, bạn có thể sử dụng COUNTIFS cùng với SUM và bao gồm một hằng số mảng trong một hoặc một vài đối số, ví dụ:

= SUM (COUNTIFS (range1, “criteria1”, range2, {“criteria1”, “criteria2”})))

Công thức thực có thể trông như sau:

= SUM (COUNTIFS (B2: B9, “sally”, C2: C9, {“Apples”, “lemons”}))

Các toán tử AND và OR trong công thức mảng của Excel

Một toán tử của mảng cho thấy cách bạn muốn xử lý mảng – sử dụng hàm logic AND và OR.

Toán tử AND là dấu hoa thị (*). Nó hướng dẫn Excel trả về TRUE nếu TẤT CẢ các điều kiện được đánh giá là TRUE.

Toán tử OR là dấu cộng (+). Nó trả về TRUE nếu bất kỳ điều kiện nào trong một biểu thức đã cho là TRUE.

Công thức mảng với toán tử AND

Trong ví dụ này, chúng tôi cho biết tổng doanh thu tương ứng mà người bán hàng là Mike và sản phẩm là Apples:

= SUM ((A2: A9 = “Mike”) * (B2: B9 = “Apples”) * (C2: C9))

Hoặc là

= SUM (IF (((A2: A9 = “Mike”) * (B2: B9 = “Apples”)), (C2: C9)))

Công thức mảng Excel với toán tử OR

Công thức mảng sau với toán tử OR (+) cho phép thêm tất cả các bán hàng ứng với người bán hàng là Mike hoặc sản phẩm là Apples:

= SUM (IF (((A2: A9 = “Mike”) + (B2: B9 = “Apples”)), (C2: C9)))

Toán tử đơn vị đôi điều hành trong công thức mảng của Excel

Nế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 đã lướt qua một vài công thức có chứa hai dấu gạch ngang (-) và bạn có thể đã tự hỏi rằng nó được sử dụng cho những việc gì.

Hai dấu gạch ngang đôi, gọi là toán tử đơn vị đôi, được sử dụng để chuyển các giá trị Boolean không bằng số (TRUE / FALSE) bởi một số biểu thức thành 1 và 0 – thứ mà một hàm mảng có thể hiểu được.

Ví dụ sau hy vọng làm cho mọi thứ dễ hiểu hơn cho bạn. Giả sử bạn có một danh sách 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 Giêng, bất kể năm nào.

Các công thức sau đây sẽ giải quyết vấn đề trên:

= SUM (-(MONTH (A2: A10) = 1))

Vì đây là công thức mảng Excel, hãy nhớ nhấn Ctrl + Shift + Enter để hoàn tất.

Nếu bạn quan tâm đến một tháng khác, hãy thay thế 1 bằng một số tương ứng. Ví dụ, 2 là Tháng 2, 3 có nghĩa là Tháng 3, v.v. Để làm cho công thức linh hoạt hơn, thì bạn có thể chỉ định số của Tháng trong một số ô, như trong hình:

Sau đó, mỗi phần của mảng sẽ được so sánh với giá trị trong ô D1, là số 1 trong ví dụ này. Kết quả của phép so sánh này là một mảng các giá trị Boolean (TRUE và FALSE). Như bạn nhớ, thì bạn có thể chọn một phần nhất định trong một công thức mảng và nhấn F9 để xem phần đó tương đương với:

Cuối cùng, bạn phải chuyển đổi các giá trị Boolean này thành 1 và 0 để hàm SUM có thể hiểu được. Và đây là những gì các toán tử đơn vị đôi cho là cần thiết. Các toán tử đơn vị đầu tiên sẽ làm TRUE / FALSE thành -1/0, tương ứng. Cái thứ hai thì sẽ phủ định giá trị, tức là đảo ngược dấu, biến chúng thành +1 và 0, để mà hầu hết các hàm của Excel có thể hiểu và làm việc với nó. Nếu bạn loại bỏ toán tử đơn vị đôi khỏi công thức trên, thì nó sẽ không hoạt động.