Cách Sử Dụng Rule Trong Excel / TOP 7 # Xem Nhiều Nhất & Mới Nhất 6/2023 # Top View

Tất nhiên với cách sử dụng những hàm IF, AND hay OR trong Excel thì các bạn đã từng học lập trình sẽ dễ dàng nắm bắt được hơn, tuy nhiên những ai chưa từng học thì cũng hoàn toàn có thể lĩnh hội hết sau một vài ví dụ.

Hàm điều kiện IF là một trong những hàm chức năng của Excel được dùng đến nhiều nhất. Nếu biết kết hợp hàm IF với các hàm khác như AND, OR, NOT thì người dùng sẽ càng thuận lợi hơn trong việc kê khai dữ liệu và xếp loại, đánh giá tự động. Ví dụ cụ thể hơn chúng ta có thể xem bên dưới.

Tất nhiên với những hàm IF, AND hay OR thì các bạn đã từng học lập trình sẽ dễ dàng nắm bắt được hơn, tuy nhiên những ai chưa từng học thì cũng hoàn toàn có thể lĩnh hội hết sau một vài ví dụ.

Làm thế nào để sử dụng hàm IF trong Excel 2013, 2010, 2007, 2003

Để sử dụng hàm IF chúng ta có cấu trúc sau: =IF(Mệnh đề điều kiện, Giá trị 1, Giá trị 2)

Trong đó, giá trị 1 được sử dụng nếu mệnh đề điều kiện đúng, giá trị 2 được sử dụng nếu mệnh đề điều kiện sai.

Một ví dụ là khi chúng ta có bảng kê khai số ngày nghỉ phép trong năm ở cột A, và muốn cột B là nơi tự động xếp loại được thưởng đối với những ai không nghỉ ngày phép nào. Khi đó, tương ứng với giá trị ở ô A2, chúng ta sẽ nhập cấu trúc hàm cho ô B2 như sau:

=IF(A2=0,”Có”,”Không”) (trong đó có thêm dấu ngoặc kép là để chỉ định giá trị là ký tự text)

Điều hay nhất là khi một giá trị ở cột A thay đổi thì giá trị tương ứng ở cột B cũng được cập nhật.

Cách sử dụng hàm IF có nhiều điều kiện

Trong trường hợp chúng ta muốn làm bảng xếp loại với nhiều hơn chỉ một trường hợp điều kiện, chúng ta có thể lồng ghép nhiều hàm IF với nhau theo cấu trúc dạng như sau:

=IF(Mệnh đề điều kiện 1, Giá trị 1, IF(Mệnh đề điều kiện 2, Giá trị 2.1, Giá trị 2.2))

Như trên có nghĩa là nếu mệnh đề điều kiện 1 sai thì Excel sẽ xét tiếp đến mệnh đề điều kiện 2. Ví dụ cho trường hợp sử dụng hàm IF có nhiều điều kiện có thể là khi chúng ta có bảng kê số tiền cân đối thu chi từng ngày ở cột A và muốn cột B tự động xếp loại xem ngày đó lỗ hay lãi.

Sử dụng hàm IF, AND, OR trong Excel kêt hợp

Sẽ có nhiều trường hợp mà điều kiện của hàm IF là bao hàm của nhiều điều kiện thành phần, và khi đó chúng ta sẽ phải phối hợp các điều kiện bằng các hàm AND, OR…

Ví dụ như khi chúng ta có bản kê điểm tốt nghiệp của các sinh viên ở cột A, có bản kê điểm thi quân sự ở cột B, và muốn cột C sẽ tự động xếp loại sinh viên nào được cấp bằng tốt nghiệp, sinh viên nào sẽ bị treo bằng. Điều kiện ở đây là cả điểm tốt nghiệp lẫn điểm quân sự, không điểm nào được dưới điểm 5. Vì thế chúng ta sẽ phải lồng ghép hàm AND vào mệnh đề điều kiện hàm IF trong ô C2 để đảm bảo phải thỏa mãn cả 2 điều kiện thành phần như sau:

Hàm OR khác với hàm AND, khi không cần cả 2 điều kiện thành phần đều đúng mà chỉ cần 1 trong 2 điều kiện thành phần đúng. Vẫn với trường hợp ví dụ như trên, chúng ta có thể lồng ghép hàm OR và mệnh đề điều kiện hàm IF như sau để đảm bảo kết quả cuối cùng vẫn vậy:

Hàm offset: Trả về giá trị của một ô hoặc một vùng các ô căn cứ vào số cột, số dòng, độ lớn các dòng, các cột được chỉ định trướcCú pháp hàm OFFSET có các đối số sau đây:

OFFSET(reference,rows,cols,[height],[width])

Trong đó:

Reference: Vùng tham chiếu hay tập hợp các ô liền kề nhau trong bảng tính excel mà bạn muốn trả về giá trị tham chiếu. Vùng tham chiếu phải tham chiếu tới một ô hoặc một phạm vi các ô liền kề; nếu không hàm OFFSET trả về giá trị lỗi #VALUE! .

Rows: Tham số này bắt buộc. chỉ Số hàng kể từ ô referene cần phải dịch lên trên nếu là số âm và dịch xuống dưới nếu là số dương.

Cols: Tham số này bắt buộc. Bắt buộc. Số cột cần dịch chuyển về bên trái ô reference nếu cols= số âm hoặc dịch chuyển sang phải nếu là số dương.

Height: Tùy chọn. Chiều cao, tính bằng số hàng, mà bạn muốn có cho tham chiếu trả về. Chiều cao phải là số dương.

Width: Tùy chọn. Độ rộng, tính bằng số cột, mà bạn muốn có cho tham chiếu trả về. Độ rộng phải là số dương.Ghi chú

Nếu đối số hàng và cột làm cho vùng tham chiếu trả về vượt ra ngoài đường biên của trang tính, thì hàm OFFSET trả về giá trị lỗi #REF! .

Nếu đối số chiều cao và độ rộng được bỏ qua, thì nó được giả định có cùng chiều cao và độ rộng với vùng tham chiếu.

Nếu chúng ta chỉ thực hiện việc tham chiếu và xuất ra một giá trị trong bảng tính thì giá trị Height và Width luôn luôn phải bắng 1 hoặc bỏ trống, nếu không hàm sẽ trả về giá trị lỗi:#REF! .

Khi chúng ta kết hợp hàm offset với hàm tính toán khác như Sum; Count, Counta thì Height và Width mới được phép mang giá trị lớn hơn 1.Ta xét ví dụ sau

Ở ô B1: ta đặt công thức: =OFFSET(D1,B2,B3,B4,B5)

Mô tả: Từ ô D1, dịch chuyển xuống 2 dòng và dịch chuyển sang phải 1 cột chính là giá trị của E3Với các giá trị trên thì ô B1= E3=”Small”

Ở đây chúng ta đặt các tham số vào các ô riêng biệt chứ không nhập trực tiếp vào hàm để dễ thấy sự thay đổi của nó. Tuy nhiên các bạn phải lưu ý rằng nếu làm theo cách này để xuất hiện tham chiếu thì giá trị Height và Width phải bằng 1; nếu lồng vào các công thức khác thì có thể khác 1.

Bây giờ chúng ta kết hợp hàm đếm counta sẽ đếm xem có bao nhiêu ô được trả về với giá trị Rows = B2 = 2; Cols = B3 = 1;Height = B4 = 2; Width = B5 = 3B1=Counta(offset(D1,B2,B3,B4,B5)= 6

Mô tả: từ ô E3 (Như trên) sẽ quét xuống 2 hàng và quét sang phải 3 cột ta được vùng dữ liệu E3:G4; Như vậy giá trị trả về của hàm offset là một mảng chứa các ô liền kề nhau: E3:G4 lúc này đối số của Hàm CountA sẽ là =counta(E3:G4)Và tiếp theo Hàm sẽ đếm từ ô E3:G4 tổng cộng có 6 ô trong vùng dữ liệu này.

Hàm ROUND trong Excel được sử dụng để tính số tròn của một số đã cho. Hàm này sử dụng rất phổ biến khi người dùng cần tính công làm việc, tính tiền lương, tiền hàng mà không muốn hiển thị ác số lẻ không cần thiết.

Hàm ROUND trong Excel được sử dụng để làm tròn bất kỳ giá trị nào với các giá trị gần hơn của nó. Ví dụ nếu có giá trị là 10.2 thì hàm này sẽ làm tròn giá trị thành 10 hoặc nếu giá trị là 10,7 thì giá trị làm tròn sẽ là 11. Đây là một hàm bảng tính sẵn có và lấy một đối số là giá trị.

Công thức ROUND trong Excel

Công thức ROUND trong Excel được sử dụng để trả về số được làm tròn theo số chữ số đã cho. Chúng ta có thể làm tròn số sang trái hoặc phải của dấu thập phân. Lúc này công thức chung cho hàm Round trong Excel như sau:

Trong đó

Number – Số muốn làm tròn và đây là trường bắt buộc.

n – Số chữ số mà số đã cho sẽ được làm tròn.

Nếu n < 0, số sẽ được làm tròn sang trái của dấu thập phân.

Nếu n = 0, số sẽ được làm tròn thành số nguyên gần nhất.

Hàm này sẽ trả về số được làm tròn đến vị trí số thập phân nhất định. Cho dù giá trị của số thập phân lớn hơn 5 hoặc nhỏ hơn 5 thì hàm ROUNDUP sẽ luôn làm tròn xuống.

Hàm này sẽ trả về số được làm tròn lên đến vị trí số thập phân nhất định. Trong trường hợp này, nếu số được làm tròn lớn hơn 5 hoặc là nhỏ hơn 5 thì hàm ROUNDDOWN sẽ luôn làm tròn xuống bất kể giá trị của số thập phân là bao nhiêu.

Hàm MROUND sẽ trả về một số được làm tròn thành một số nhiều điểm được chỉ định bởi chúng tôi. Điều này sẽ làm tròn số lên hoặc xuống tùy thuộc vào bội số gần nhất.

Làm tròn số bằng hàm ROUND

Bước 1: Nếu bạn muốn làm tròn số tiền bán hàng với số thập phân bằng 0, thì chúng ta có thể sử dụng công thức ROUND trong Excel như sau

Như kết quả hiển thị ở trên cho thấy, hàm Round trong Excel sẽ làm tròn lên hoặc làm tròn xuống dựa trên số thập phân (nếu lớn hơn 5 thì làm tròn lên và nếu nhỏ hơn 5 thì làm tròn xuống)

Làm tròn bằng hàm ROUDUP

Cũng với bảng số liệu như trên, nếu bạn luốn làm tròn lên tất cả các số trong cột Số tiền bán hàng bạn sử dụng công thức sau:

Như bảng kết quả trên cho thấy, hàm RONUDUP sẽ làm tròn lên tất cả các kết quả, bất kể số thập phân lớn hơn 5 (622.71; 491.586; 625.57;) hoặc nhỏ hơn 5 (912.35; 781.295).

Làm tròn bằng hàm ROUNDDOWN

Cũng với bảng số liệu như trên, nếu bạn luốn làm tròn xuống tất cả các số trong cột Số tiền bán hàng bạn sử dụng công thức sau:

Trái ngược với hàm RONUDUP ở trên, hàm RONUDDOWN sẽ làm tròn xuống tất cả các kết quả, bất kể số thập phân lớn hơn 5 (622.71; 491.586; 625.57;) hoặc nhỏ hơn 5 (912.35; 781.295).

Giả sử bạn có một số có dấu thập phân nhưng cần chuyển đổi thành một số nguyên gần nhất với bội số được chỉ định thì chúng ta có thể sử dụng hàm MROUND với công thức sau:

=MROUND(number,multiple)

Như vậy chúng ta có thể thấy số đã được thay đổi thành một số nguyên với bội số gần nhất là 10.

Trên là chúng tôi vừa giới thiệu đến bạn đọc cách sử dụng hàm RONUD trong Excel với nhiều cách khác nhau. Trong đó đi kèm những ví dụ cụ thể, đơn giản và rất dễ hiểu để bạn có thể học và làm theo dễ dàng.

Nếu cần học và biết thêm nhiều hàm hữu ích ích khác trong Excel, các bạn có thể đăng ký một Khóa học tin học văn phòng trực tiếp trên chúng tôi với những giáo viên kinh nghiệm lâu năm sẽ giúp bạn nhanh chóng sử dụng Excel thành thạo trong công việc.

SUM là một hàm rất phổ biến và hữu dụng trong Microsoft Excel, đồng thời cũng là một hàm số học cơ bản. Như cái tên của nó, hàm SUM biểu thị phép tính tổng trong toán học. Và các tham số có thể là tham số đơn lẻ hoặc những dãy ô.

Định nghĩa hàm SUM trong Excel

Excel định nghĩa SUM là một phép tính “tổng tất cả các số trong dãy ô”. Nghĩa là hàm SUM có nhiệm vụ tính tổng số những con số và tham số có trong các ô và dãy ô.

Cấu trúc của hàm SUM

Hàm SUM có hai cấu trúc do đó có thể được viết theo hai cách:

=SUM(num1,num2, … num_n). Ở đây ” num1,num2, … num_n” biểu thị cho những số chúng ta sẽ tính.

=SUM(CellRange1,CellRange2,…CellRange_n). Trong đó:“CellRange1,CellRange2,… CellRange_n” là những cột số.

Những chú ý trong phép tính SUM:

Hàm SUM có thể tính số thập phân và phân số.

Trong phép tính SUM: =SUM(num1,num2, … num_n), nếu bạn nhập tham số “num” không phải là số thì hàm SUM sẽ hiện thị lỗi #Name? Error.

Hàm SUM không phải là dạng hàm động lực học. Vì vây, nếu bạn sử dụng phép tính SUM vào dãy các ô rồi lọc ra một vài giá trị nào đó thì đầu ra của phép tính SUM sẽ không thay đổi theo gía trị gần nhất. Do đó tốt hơn hết, bạn nên dùng hàm Subtotal cho trường hợp này.

Những ví dụ về hàm SUM

1. Ví dụ 1, tổng các số nguyên: =SUM(10,11,19) nghĩa là cộng các số 10,11,19 và 40 là kết quả 40.

Rút gọn của phép tính SUM:

3. Ví dụ 3, tổng các số phân số: =SUM(4/2,8/2). Phép tính cộng các số 4/2 và 8/2 và kết quả là 6.

5. Sử dụng dạng thứ hai của hàm SUM [=SUM(B2:B10)] để tính tổng cho các số trong dãy số (B2:B10) thay cho các tham số và cho ra kết quả 60.

Thay vì viết phép tính theo cách đơn thuần, bạn có thể sử dụng cách sau:

Đầu tiên, lựa chọn dãy số rồi ấn ” Alt + ” và kết quả sẽ hiển thị ở ô tiếp theo.

Sử dụng các phép toán khác trong phép tính SUM

Các phép tính khác như (+, – , / và *) có thể được dùng trong hàm SUM. Ví dụ như =SUM(2*4) và hàm SUM sẽ cho ra là 8. Tương tự như vậy với các phép tính còn lại.

Sử dụng Hàm SUM cùng với những hàm khác

=SUM(A2:L2)/COUNTA(A2:L2)

2. SUM của A2:L2 chia cho số tháng trong năm trong A2:L2 không tính ô bị bỏ trống.

Sử dụng hàm SUM qua nhiều trang

=Sheet1!A1+Sheet2!A1+Sheet3!A1

Hàm SUM đã tính tổng các ô A1 có trong tất cả các trang từ 1 đến 3.

=AVERAGE(Sheet1:Sheet3!A1)

=MIN(Sheet1:Sheet3!A1)

=MAX(Sheet1:Sheet3!A1)

Cách này rất hữu ích để tính từng trang đơn của những tháng (tháng 1 đến tháng 12).

=SUM(January:December!A2) đã cộng tất cả ô A2 ở từng trang từ tháng 1 đến tháng 12

Phương pháp 3D này cũng được sử dụng tương tự như vậy trong các hàm AVERAGE, MIN, MAX,…

Các vấn đề thường gặp và cách giải quyết:

Trường hợp 1: Hàm SUM hiển thị #####.

Hãy kiểm tra độ dài cột, vì ##### thông báo không đủ chỗ trống để hiện thị kết quả.

Trường hợp 2: Hàm SUM hiển thị chữ và không ra kết quả. Hoặc chỉ hiện số nhưng không phải số thập phân.

Kiểm tra lại định dạng của cột. Hãy vào Format và chọn Number để lựa chọn dạng bạn muốn. Nếu sau khi định dạng, ô hiển thị chữ không thay đổi, bạn nên ấn F2 và enter.

Trường hợp 3: Hàm SUM không câp nhật.

Bạn phải chắc chắn rằng Calculation được đặt ở chế độ Automatic. Đầu tiên vào Formula, chọn tùy chỉnh Calculation. Sau đó ấn F9 để trang tự động cập nhật.

Trường hợp 4: Một vài giá trị không được sử dụng trong hàm SUM.

Chỉ có giá trị hoặc dãy có chứa số mới được đặt ở trong hàm. Những ô trống, hàm logic, hoặc chữ sẽ bị loại bỏ.

Trường hợp 5: Hàm cho ra kết quả #NAME? Error.

Đã có lỗi chính tả ở đây. Ví dụ, =sume(A1:A10) thay vì =SUM(A1:A10).