Cách Dùng Hàm If Để Lọc / TOP 5 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

Hướng dẫn sử dụng Hàm if cơ bản

Công dụng: trả về giá trị nếu thỏa mãn điều kiện đưa ra Công thức chung: =if(logical_test, value_if_true, value_if_ false) Trong đó: logical_test: biểu thức điều kiện, là biểu thức logic. Value_if_true: giá trị đúng. Là giá trị nhận được khi biểu thức điều kiện thỏa mãn đúng Value_if_false: giá trị sai. Giá trị nhận được khi biểu thức điều kiện saiVí dụ 1 về hàm if: Bảng lương nhân viên:

Yêu cầu: Tính thưởng cho mỗi nhân viên biết: Nếu nhân viên thuộc phòng nhân sự thì thưởng 800$. Nhân viên các phòng khác thưởng 750$ Khi đó, để tính thưởng ta dựa vào phòng ban của mỗi nhân viên Tại ô E2 ta nhập công thức như sau: =if( C2=”nhân sự”, 800, 750) Sao chép kết quả xuống các ô còn lại:

Hướng dẫn sử dụng hàm If kép

Trong trường hợp có nhiều hơn 2 giá trị nhận được. Mỗi điều kiện thỏa mãn đúng sẽ nhận được giá trị tương ứng, ta tiến hành lồng các hàm if vào nhau Công thức chung: =if(logical_test 1, value_if_true, if(logical_test 2, value_if_true, value_if_ false))Ví dụ 2 về hàm if kép: Cũng với bảng lương nhân viên trên. Yêu cầu tính thưởng cho mỗi nhân viên biết Nếu nhân viên thuộc phòng nhân sự thì được thưởng 800$, Nhân viên phòng IT được thưởng 780$ Nhân biên phòng kỹ thuật được thưởng 760$ Các phòng còn lại, mỗi nhân viên được thưởng 750$ Khi đó Tại ô E2 ta nhập công thức như sau: =if( C2= “Nhân sự”, 800,if(C2= “IT”, 780, if( C2= “kỹ thuật”, 760,750))) Sao chép kết quả xuống các ô còn lại

Hướng dẫn sử dụng hàm If kết hợp hàm and và hàm or

Trong trường hợp để nhận được giá trị trả về nào đó thì phải thỏa mãn đồng thời nhiều điều kiện cùng lúc hoặc thỏa mãn một trong các điều kiện. Ta tiến hành kết hợp các hàm and hoặc or vào trong ifVí dụ 3 về hàm if kết hợp hàm and và hàm or: Cũng với bảng lương nhân viên trên. Yêu cầu tính thưởng cho mỗi nhân viên biết Nếu nhân viên nữ thuộc phòng nhân sự thì được thưởng 800$, Nhân viên phòng IT hoặc nhân viên nam phòng nhân sự được 780$ Các phòng còn lại, mỗi nhân viên được thưởng 750$ Xét ví dụ trên. Để nhận được 800$ thì ngân viên đó cần thỏa mãn đồng thời 2 điều kiệnblaf giới tính nữ và thuộc phòng nhân sự để nhận được 780$ thì một nhân viên chỉ cần thỏa mãn 1 trong các điều kiện hiawcj thuộc phòng IT hoặc thuộc phòng nhâb sự trong các trường hợp còn lại Khi đó Tại ô E2 ta nhập công thức như sau: =if(and( C2= “Nhân sự”, D2= “Nữ”), 800,if(or(C2= “IT”, C2= “Nhân sự”), 780, 750)) Sao chép kết quả xuống các ô còn lại

Ví dụ 4 về hàm if kết hợp hàm and và hàm or: Tính thưởng cho mỗi nhân viên biết nếu nhân viên nữ thuộc phòng kỹ thuật hoặc nhân viên nữ phòng IT thì thưởng 800$. Các trường hợp còn lại được thưởng 780$ Khi đó Tại ô E2 ta nhập công thức như sau: =if(and( or(C2= “IT”,C2= “kỹ thuật”), D2= “Nữ”), 800,780) Sao chép kết quả xuống các ô còn lại:

Khi bạn cần xác định vị trí tương đối của một số trong danh sách các số, cách dễ nhất là bạn hãy sắp xếp các số này theo thứ tự tăng dần (hoặc giảm dần). Nếu vì một lý do nào đó khiến bạn không làm được điều này, công thức hàm RANK trong Excel là một công cụ hoàn hảo để hỗ trợ cho bạn.

Ghi chú: Hướng dẫn trong bài viết này áp dụng cho Excel 2019, 2016, 2013, 2010, 2007; Excel Online, Excel cho Microsoft 365, Excel cho Mac, Excel cho iPad, Excel cho iPhone, và Excel cho Android.

Công thức hàm RANK trong Excel

Hàm RANK trong Excel trả về thứ tự (hoặc thứ hạng) của một giá trị số so với các giá trị khác trong cùng một danh sách. Nói cách khác, với công dụng của hàm RANK, bạn sẽ biết được giá trị nào cao nhất (hoặc thấp nhất), giá trị nào cao thứ hai (hoặc thấp thứ hai).

Trong một danh sách đã được sắp xếp, thứ hạng của một số chính là vị trí của nó. Hàm RANK có thể xác định thứ hạng bắt đầu bằng giá trị lớn nhất (nếu được sắp xếp theo thứ tự giảm dần) hay giá trị nhỏ nhất (nếu được sắp xếp theo thứ tự tăng dần).

Công thức của hàm RANK sẽ được viết như sau:

=RANK(number,ref,[order])

Number (bắt buộc): giá trị mà bạn muốn tìm thứ hạng

Ref (bắt buộc): danh sách các giá trị số mà bạn muốn làm dữ liệu để xếp hạng. Nó có thể được cung cấp dưới dạng một mảng các số hoặc một tham chiếu đến danh sách các số.

Order (Tùy chọn): Cách sắp xếp giá trị mà bạn muốn (tăng dần hay giảm dần).

Nếu được ghi là 0 hay bị bỏ qua, thì các giá trị sẽ được sắp xếp theo thứ tự giảm dần (tức là từ lớn nhất đến nhỏ nhất).

Nếu được ghi là 1 hoặc bất kỳ giá trị khác 0 nào khác, thì các giá trị sẽ được sắp xếp theo thứ tự tăng dần (tức là từ lớn nhất đến nhỏ nhất).

Hàm chúng tôi trong Excel

RANK.EQ là phiên bản cải tiến của hàm RANK, được giới thiệu lần đầu tiên trong Microsoft Excel 2010. Nó có cùng cú pháp với hàm RANK và hoạt động với cùng logic: Nếu một vài giá trị được xếp hạng bằng nhau, thứ hạng cao nhất sẽ được gán cho tất cả các giá trị đó. EQ là viết tắt của từ “Equal”.

=RANK.EQ(number,ref,[order])

Trong Excel 2007 và các phiên bản thấp hơn, bạn luôn sẽ phải sử dụng hàm RANK. Trong Excel 2010, Excel 2013, và Excel 2016, bạn có thể sử dụng hàm RANK hoặc chúng tôi Và chúng tôi khuyên bạn nên sử dụng hàm chúng tôi vì hàm RANK có thể bị ngừng hỗ trợ bất kỳ lúc nào.

Hàm chúng tôi trong Excel

RANK.AVG là một hàm khác dùng để xác định thứ hạng trong Excel (chỉ có trong Excel 2010, 2013 và 2016 trở lên).

Nó cũng có công thức giống với hai hàm ở trên:

=RANK.AVG(number,ref,[order])

Sự khác biệt ở đây là nếu có nhiều hơn một số có cùng một thứ hạng, thì thứ hạng trung bình sẽ được trả về. AVG là viết tắt của từ “Average”.

Lưu ý:

Hàm RANK trong Excel chỉ hoạt động với các giá trị là số: số dương và số âm, số Không (0), giá trị ngày và thời gian. Các giá trị không phải không phải là số trong đối số Ref sẽ được bỏ qua.

Trong Excel 2010 và các phiên bản mới hơn, hàm RANK đã được thay thế bằng hàm chúng tôi và chúng tôi Để có thể tương thích ngược, hàm RANK vẫn hoạt động trong tất cả phiên bản Excel, nhưng nó có thể sẽ không còn khả dụng trong tương lai.

Nếu không thể tìm thấy số liệu trong đối số Ref, hàm RANK sẽ trả về lỗi #N/A.

Công thức hàm RANK cơ bản (xếp hạng từ cao nhất đến thấp nhất)

Để hiểu rõ hơn về dữ liệu xếp hạng trong Excel, bạn có thể xem qua ảnh chụp màn hình bên dưới:

Trong tất cả các phiên bản Excel 2003 – 2019:

=RANK($B2,$B$2:$B$7)

Trong Excel 2010 – 2019:

=RANK.EQ($B2,$B$2:$B$7) =RANK.AVG($B2,$B$2:$B$7)

Sự khác biệt duy nhất là cách các công thức này xử lý các giá trị trùng lặp. Như bạn có thể thấy, có hai học sinh cùng điểm số (trong hai ô B5 và B6) làm ảnh hưởng đến các thứ hạng tiếp theo:

Công thức hàm RANK và chúng tôi đều xếp hạng cho những học sinh có cột Điểm giống nhau. Điểm số cao nhất của học sinh tiếp theo (Tuấn) được xếp hạng 4. Và hạng 3 không được trao cho ai cả.

Công thức hàm chúng tôi thì gán thứ hạng trung bình cho cả hai học sinh này (hạng 2.5). Và một lần nữa, hạng 3 cũng không được trao cho bất kỳ học sinh nào.

Cách sử dụng hàm RANK trong Excel qua những ví dụ cụ thể

Cách xếp hạng trong Excel theo thứ tự tăng dần (từ nhỏ đến lớn)

Như trong ví dụ bên trên, để xếp hạng điểm số học sinh từ cao nhất đến thấp nhất, bạn sử dụng một trong những công thức hàm RANK ở trên với đối số Order được đặt thành giá trị 0 hoặc bỏ qua (mặc định).

Để có thể xếp hạng theo thứ tự ngược lại (từ thấp nhất đến cao nhất) thì bạn hãy sửa đối số Order thành 1 hoặc bất kỳ giá trị nào khác 0.

Lấy ví dụ: Nếu như bạn muốn xếp hạng thời gian chạy 100m của học sinh. Bạn có thể dùng một trong số các công thức hàm RANK bên dưới:

=RANK(B2,$B$2:$B$7,1) =RANK.EQ(B2,$B$2:$B$7,1)

Và bạn hãy khóa phạm vi ô trong đối số Ref bằng cách sử dụng các tham chiếu ô tuyệt đối bằng phím F4, để đảm bảo rằng nó sẽ không thay đổi khi bạn thực hiện sao chép công thức xuống phía dưới.

Hướng dẫn cách xếp hạng dữ liệu duy nhất trong Excel

Như chúng tôi đã chỉ ra ở các phần trước đó, tất cả các hàm RANK đều trả về cùng một thứ hạng cho các mục có giá trị bằng nhau. Nếu bạn không muốn điều này xảy ra, hãy sử dụng một trong các công thức bên dưới để giải quyết tình trạng này.

Xếp hạng từ cao đến thấp (dữ liệu duy nhất)

Để xếp hạng điểm số của các học sinh theo thứ tự giảm dần, bạn hãy sử dụng công thức sau đây:

=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Và để xếp hạng kết quả cuộc thi chạy 100 mét theo thứ tự tăng dần mà không có sự trùng lặp thứ hạng, bạn hãy sử dụng công thức bên dưới:

=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1

Tạo danh sách không có dòng trống thừa ở cuối danh sách

Ví dụ bạn có 1 danh sách nhân viên, bạn dự tính danh sách đó sẽ gồm tối đa là 20 người. Thế nhưng tại thời điểm tháng 1, bạn mới chỉ có 5 người trong danh sách, còn lại 15 dòng trống. Và trong những tháng tiếp theo thì số người có thể tăng thêm, giảm đi.

Vậy làm cách nào để danh sách nhân viên đó tự động thay đổi theo số nhân viên hiện có, và những dòng trống sẽ bị loại ra khỏi danh sách?

Hình 1: danh sách nhân viên khi có 5 người Hình 2: danh sách tự động mở rộng khi có thêm 2 người

Để danh sách có thể tự thay đổi được, chúng ta phải sử dụng hàm OFFSET như sau:

Tìm điểm bắt đầu

Danh sách bắt đầu từ ô B3, vì vậy trong hàm Offset chúng ta có thể:

Bắt đầu với tham chiếu B3, không thay đổi về dòng, cột

Bắt đầu với tham chiếu B2, xuống 1 dòng, không đổi về cột

Bắt đầu với tham chiếu A2, xuống 1 dòng, sang phải 1 cột

Việc chọn vị trí bắt đầu chỉ đơn giản là căn cứ để chúng ta tìm được điểm đầu của danh sách. Tùy theo vị trí tham chiếu mà chúng ta có thực hiện di chuyển số dòng, số cột không. Nếu có thì di chuyển bao nhiêu dòng, bao nhiêu cột.

Ví dụ công thức bắt đầu với ô B3

=OFFSET(B3,0,0,độ rộng theo sốdòng, độ rộng theo số cột)

Xác định độ rộng của danh sách

Tiếp đến là xác định danh sách đó có bao nhiêu ô. Để biết điều này, chúng ta dùng hàm COUNTA. Hàm COUNTA có tác dụng đếm những ô có chứa nội dung (không phải ô trống)

Chúng ta đếm trong cột B, từ dòng 3 tới dòng 22 (tương ứng với tối đa 20 nhân viên trong danh sách):

COUNTA(B3:B20)

Với 5 nhân viên, kết quả hàm COUNTA = 5

Với 7 nhân viên, kết quả hàm COUNTA=7

Khi đặt giá trị này vào tham số Weight của hàm OFFSET, chúng ta sẽ có độ rộng của danh sách là 5 hay 7 ô, tính từ ô B3.

=OFFST(B3,0,0,COUNTA(B3:B22))

Không cần nhập tham số độ rộng theo số cột, vì ở đây chúng ta chỉ áp dụng trên 1 cột B

Tạo danh sách chọn với Data Validation

Sau khi đã viết đúng được hàm OFFSET thì chúng ta chỉ việc copy công thức đó để đưa vào trong mục tạo danh sách chọn trong Data Validation:

Tạo danh sách phụ thuộc nhau, danh sách theo nhóm với hàm OFFSET

Ví dụ về danh sách phụ thuộc nhau, danh sách theo nhóm như sau:

Trong danh sách nhân viên ở trên, chúng ta có thêm cột Bộ phận. Tại đó chia làm 2 bộ phận là Kinh doanh và Kế toán. Khi chọn bộ phận Kinh doanh, chúng ta sẽ có 1 danh sách chỉ có những nhân viên thuộc bộ phận kinh doanh:

Còn khi chọn bộ phận Kế toán, danh sách nhân viên sẽ thay đổi chỉ có nhân viên thuộc bộ phận kế toán:

Để làm được việc này, chúng ta sẽ sử dụng hàm OFFSET như sau:

Xác định điểm bắt đầu làm tham chiếu gốc

Bởi vì bộ phận có thể thay đổi, nên chúng ta sẽ xác định điểm bắt đầu là 1 vị trí cố định. Ví dụ như ô B2

=OFFSET(B2, ….)

Xác định hướng thay đổi điểm bắt đầu theo dòng

Tùy vào bộ phận được chọn ở ô F2 mà chúng ta sẽ xác định từ vị trí B2 sẽ di chuyển bao nhiêu dòng. Việc này xác định một cách khá đơn giản với hàm MATCH như sau:

=MATCH(F2,B3:B22,0)

Dò tìm giá trị F2 ở trong vùng B3:B22. Kết quả sẽ là vị trí dòng thứ mấy trong vùng B3:B22 ứng với giá trị tại F2, nếu có nhiều hơn 1 giá trị đúng thì sẽ trả về vị trí của giá trị đầu tiên.

=OFFSET(B2,MATCH(F2,B3:B22,0), ….)

Xác định hướng thay đổi điểm bắt đầu theo cột

Do danh sách cần tìm là Họ tên nhân viên, cách vị trí ô B2 là 1 cột, nên sẽ thay đổi 1 cột. Chúng ta chọn số 1

=OFFSET(B2,MATCH(F2,B3:B22,0),1, …)

Xác định độ rộng theo dòng

Để xác định độ rộng theo dòng, chúng ta cần đếm xem có bao nhiêu dòng trong cột Bộ phận có giá trị giống với Bộ phận được chọn ở ô F2. Đây là việc đếm theo điều kiện, do đó chúng ta dùng hàm COUNTIF như sau:

=COUNTIF(B3:B22,F2)

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2), …)

Xác định độ rộng theo cột

Vì danh sách này chúng ta chỉ lấy trên 1 cột, do đó không cần tới điều chỉnh về độ rộng số cột. Có thể không cần nhập tham số này. Chúng ta kết thúc hàm OFFSET với nội dung

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2))

Tạo danh sách chọn với Data Validation

Cuối cùng chúng ta chỉ việc copy công thức trên và dán vào danh sách chọn Data Validation cho ô F3

Để cho chắc chắn dữ liệu không bị thay đổi, khi đưa công thức vào Data Validation thì chúng ta nên cố định lại tọa độ dữ liệu như sau:

=OFFSET($B$2,MATCH($F$2,$B$3:$B$22,0),1,COUNTIF($B$3:B$22,$F$2))

Hàm OFFSET và các kết hợp hàm của nó trong Excel, công thức ví dụ Tạo Drop-down list có giá trị phụ thuộc một list khác Hướng dẫn cách tạo danh sách nhân viên phụ thuộc theo chi nhánh

Microsoft Excel cho phép bạn dễ dàng thêm các giá trị vào trong bài báo cáo hay phân tích của bạn. Hàm SUM tiêu chuẩn có thể đáp ứng đầy đủ cho bạn trong hầu hết các trường hợp, nhưng nếu như bạn muốn thực hiện tính tổng có điều kiện trong Excel, bạn cần phải sử dụng hàm SUMIF.

Hàm SUMIF là kết hợp giữa hàm SUM và hàm IF trong Excel để cho phép bạn thêm các giá trị trong một phạm vi dữ liệu đã chọn đáp ứng các tiêu chí cụ thể. Phần IF của hàm xác định dữ liệu nào sẽ khớp với tiêu chí đã chỉ định và phần SUM có nhiệm vụ tính tổng các giá trị thỏa với điều kiện đó.

Ví dụ: Có thể bạn sẽ muốn tính tổng doanh thu hằng năm, nhưng chỉ tính các sản phẩm có doanh số bán lớn hơn 250 đơn hàng.

Ghi chú: Các hướng dẫn trong bài viết này áp dụng cho Excel 2019, 2016, 2013, 2010, và Excel Microsoft 365.

Công thức hàm SUMIF trong Excel

Trong Excel, cú pháp của hàm yêu cầu bạn phải có đầy đủ bố cục của hàm và bao gồm tên, dấu ngoặc và đối số của hàm.

Cấu trúc của hàm SUMIF là:

=SUMIF(Range,Criteria,Sum_range)

Các đối số của hàm cho biết nó đang kiểm tra điều kiện nào và phạm vi dữ liệu là ở đâu để tổng hợp thông tin chính xác.

Range (bắt buộc): Là nhóm các ô bạn muốn đánh giá theo các tiêu chí.

Criteria (bắt buộc): Là giá trị mà hàm sẽ so sánh với dữ liệu trong các ô đã chọn trong mục Range ở trên. Nếu nó tìm thấy kết quả khớp, thì nó sẽ tự động cộng dữ liệu tương ứng trong phần Sum_range. Bạn có thể nhập dữ liệu thực tế hoặc tham chiếu ô đến dữ liệu cho đối số này.

Sum_range (tùy chọn): Hàm sẽ cộng dữ liệu trong phạm vi những ô này nếu nó tìm thấy kết quả khớp. Trường hợp nếu như bạn bỏ trống ô này, nó sẽ tính tổng phạm vi những ô trong mục Range đầu tiên.

Hướng dẫn dùng hàm SUMIF để tính tổng có điều kiện trong Excel

Hướng dẫn này sẽ chỉ cho bạn cách tính tổng doanh số hằng năm của những mặt hàng đã bán được hơn 250 đơn đặt hàng.

Bước 2: Nhấp vào tab Formulas.

Bước 3: Nhấp vào biểu tượng Math & Trig trên thanh Menu chính để mở những hàm có thể sử dụng.

Bước 7: Chọn phạm vi các ô từ D3 đến D9 của bảng tính để nhập các tham chiếu ô này được dạng phạm vi cần tìm kiếm.

Bước 8: Bạn chọn dòng Criteria.

Bước 11: Tiếp theo, bạn nhấp vào dòng Sum_range.

Mẹo: Bạn có thử sử dụng tham chiếu ô để làm giá trị Criteria.