Cập nhật thông tin chi tiết về Cách Dùng Hàm Xếp Thứ Hạng Qua Ví Dụ 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.
5
/
5
(
3
bình chọn
)
Bạn có 1 bảng tính Excel và bạn đang muốn tìm thứ hạng vị trí của 1 dòng nào đó trong số hàng nghìn dòng thì bạn phải làm thế nào? Sắp xếp các dòng theo thứ tự nào đó rồi đếm ư? Vậy thì quá mất thời gian, trong khi Excel lại có hàm hỗ trợ bạn làm điều đó 1 cách nhanh chóng. Hàm mà mình đang muốn nói đó là hàm RANK – hàm xếp thứ hạng trong Excel.
Hướng dẫn cách dùng hàm RANK trong Excel
Hàm RANK trong Excel là hàm Excel cơ bản nên nó được hỗ trợ trên các phiên bản từ Excel 2016, Excel 2013, Excel 2010, Excel 2007 cho đến Excel cho điện thoại iPhone, MAC, Android. Ở đây, mình hướng dẫn bạn cách sử dụng hàm RANK trên Excel 2016, các phiên bản Excel khác bạn cũng làm tương tự.
Cú pháp hàm RANK
=RANK(number,ref,[order])
Các giá trị trong hàm RANK
number: Tham chiếu tới ô chứa giá trị số bạn muốn tìm thứ hạng. Đây là giá trị bắt buộc.
ref: Mảng hoặc tham chiếu tới 1 danh sách chứa các số. Các giá trị không phải số sẽ được bỏ qua. Đây là giá trị bắt buộc.
order: Cách sắp xếp. Bạn có thể sắp xếp theo thứ tự tăng dần hoặc giảm dần. Đây là giá trị tùy chọn.
order = 0 (số không) hoặc không có tham số này thì hàm RANK sẽ xếp hạng theo thứ tự giảm dần. Tức là giá trị lớn đứng, giá trị nhỏ đứng sau.
order là giá trị khác 0 (số không) thì hàm RANK sẽ xếp hạng theo thứ tự tăng dần. Tức là giá trị nhỏ đứng trước, giá trị lớn đứng sau.
Lưu ý
Nếu danh sách tham chiếu có 2 giá trị trùng lặp thì hàm RANK sẽ trả về cùng 1 thứ hạng. Và sự trùng lặp này sẽ ảnh hưởng đến thứ hạng của các giá trị sau đó. Ví dụ, cho 1 danh sách số nguyên tăng dần nếu số 1 xuất hiện 2 lần và có thứ hạng là 4, thì số 2 sẽ có thứ hạng 6 (không có số nào có thứ hạng là 5).
Ví dụ về cách dùng hàm RANK trong Excel
Yêu cầu: Cho bảng điểm của sinh viên như hình dưới. Yêu cầu xếp hạng sinh viên có điểm trung bình từ cao đến thấp
Ở đây, để giải quyết vấn đề này chúng ta sẽ sử dụng hàm RANK và đặt cách xếp hạng từ giá trị lớn tới giá trị nhỏ. Để đặt xếp hạng từ lớn tới nhỏ thì trong phần lý thuyết trên bạn cũng biết rồi, giá trị order chúng ta sẽ để là 0 hoặc để trống. Khi đó ta sẽ có công thức cho ô D4 như sau:
=RANK(C4,$C$4:$C$13,0) hoặc =RANK(C4,$C$4:$C$13)
Trong công thức trên chắc bạn hiểu rồi vì cũng đã đọc qua phần lý thuyết ở trên rồi mà nhỉ. Còn các dấu $ nếu bạn chưa hiểu ý nghĩa của nó là gì thì đọc bài viết ký hiệu $ trong Excel có ý nghĩa gì. Còn kết quả cho cả bảng tính sẽ được như sau:
Bài tập về hàm RANK trong Excel
Lời kết
Hàm Subtotal Trong Excel – Cách Dùng Hàm Subtotal Qua Ví Dụ
4.6
/
5
(
9
bình chọn
)
Hướng dẫn cách dùng hàm SUBTOTAL trong Excel
Cú pháp: =SUBTOTAL(function_num,ref1,[ref2],…)
Giải thích các giá trị
function_num: Nhập vào một số để chỉ định hàm sử dụng trong phép tính. Giá trị nhập vào là một số từ 1 đến 11 (tính toán bao gồm các giá trị ẩn) hoặc từ 101 đến 111 (tính toán không bao gồm các giá trị ẩn). Đây là giá trị bắt buộc. Các số tương ứng với các hàm như sau:
ref1: Phạm vi vùng chọn tham chiếu đầu tiên mà bạn muốn tính toán. Đây là giá trị bắt buộc.
ref2: Phạm vi hoặc chuỗi từ 2 đến 254 mà bạn muốn tính.
Lưu ý:
Nếu có hàm SUBTOTAL khác trong giá trị ref1, ref2,…thì các hàm lồng vào này sẽ được bỏ qua để tránh bị tính 2 lần.
Lựa chọn các function_num cho hàm SUBTOTAL sao cho phù hợp. Các hàm từ 1 đến 11 sẽ tính toán bao gồm các giá trị ẩn trong vùng dữ liệu được chọn. Các hàm từ 101 đến 111 sẽ loại trừ các giá trị ẩn trong vùng dữ liệu (chỉ tính toán với các giá trị hiển thị).
Hàm SUBTOTAL với function_num bất kỳ sẽ không được áp dụng nếu hàng bị ẩn bởi lệnh Filter (Auto Filter).
Hàm SUBTOTAL chỉ dùng cho cột dữ liệu hoặc phạm vi dọc mà không dùng cho hàng hay phạm vi ngang. Vì thế khi bạn ẩn cột sẽ không ảnh hưởng tới giá trị tính toán, còn ẩn hàng thì có ảnh hưởng.
Các tham chiếu nào là tham chiếu 3-D thì hàm SUBTOTAL sẽ trả về giá trị #VALUE!
Ví dụ về cách dùng hàm SUBTOTAL trong Excel
Giả sử chúng ta có bảng dữ liệu sau:
Yêu cầu ở đây là tính tổng số sản phẩm và tìm ra lượng sản phẩm còn ít nhất là bao nhiêu và nhiều nhất là bao nhiêu.
Chúng ta sẽ sử dụng hàm SUBTOTAL và dựa vào bảng giá trị function_num ở trên để chọn hàm và tính được lần lượt các giá trị như sau:
Tại ô D15 công thức tính tổng là
=SUBTOTAL(9;D5:D13)
Tại ô D16 công thức tính giá trị nhỏ nhất là
=SUBTOTAL(5;D5:D13)
Tại ô D17 công thức tính giá trị lớn nhất là
=SUBTOTAL(4;D5:D13)
Ta có kết quả như sau
Ví dụ về điểm nổi bật của hàm SUBTOTAL trong Excel
Cũng trong ví dụ trên bên cột E chúng ta sử dụng các hàm SUM, MIN, MAX để tính và có kết quả như sau
Kết quả tính toán là giống nhau đúng không nào. Bây giờ mình sẽ làm như sau và bạn sẽ thấy hàm SUBTOTAL phát huy được điểm mạnh của nó như nào.
Ví dụ trong bảng dữ liệu chúng ta dùng Filter để ẩn số liệu của nhà cung cấp Lazada khi đó chúng ta sẽ có kết quả như sau:
Kết quả đã có sự khác biệt khi sử dụng hàm SUBTOTAL
Với hàm SUBTOTAL tổng là 34 vì hàm SUBTOTAL chỉ tính tổng của các sản phẩm hiển thị của nhà cung cấp Adayroi, còn hàm SUM vẫn tính cả tổng các mặt hàng đã bị ẩn.
Giá trị nhỏ nhất thì cả 2 hàm giống nhau vì giá trị trong bảng lúc hiển thị và lúc ẩn đều là 7.
Giá trị lớn nhất của hàm SUBTOTAL là 10 vì hàm SUBTOTAL chỉ dò tìm các giá trị hiển thị còn hàm MAX vẫn dò tìm cả các giá trị bị ẩn.
Có 1 điểm đặc biệt trong kết quả này nếu bạn chú ý kỹ sẽ thấy đó là trong các công thức trên chúng ta dùng các giá trị function_num là 9,5,4 (các function_num sẽ tính toán bao gồm cả các giá trị ẩn), nhưng trong này nó lại không tính (có gì sai ư?).
Ví dụ về cách dùng công cụ SUBTOTAL
Cũng với ví dụ chúng tôi dùng ở trên mình sẽ dùng công cụ SUBTOTAL để tính tổng các sản phẩm của mỗi nhà cung cấp.
Yêu cầu cần thiết là bạn phải sắp xếp tên nhà cung cấp trùng nhau ở gần nhau. Ở ví dụ này mình đã sắp xếp trước rồi nên mình không cần làm việc này nữa.
Hộp thoại Subtotal hiện lên như sau:
Trong đó chúng ta có các thông số như sau:
Mục At each change in: Bạn chọn Nhà cung cấp vì chúng ta sẽ tính tổng cho từng Nhà cung cấp.
Mục Use function: Bạn chọn hàm cần để tính. Ở đây mình tính tổng nên sẽ chọn là SUM.
Mục Add subtotal to: Bạn chọn cột cần tính toán. Ở đây mình muốn tính số lượng nên chọn cột Số lượng.
Ấn OK và chúng ta có kết quả thu được như sau
Download file ví dụ về hàm SUBTOTAL trong Excel
Lời kết
Với những kiến thức về hàm SUBTOTAL ở trên hi vọng các bạn có thể hiểu được cách dùng hàm SUBTOTAL và vận dụng nó tốt hơn trong học tập và trong công việc. Hàm SUBTOTAL là 1 hàm rất linh hoạt, với những ưu điểm của hàm này bạn có thể dùng để tính toán hiệu quả.
Hướng Dẫn Dùng Hàm Counta Qua Ví Dụ
5
/
5
(
4
bình chọn
)
Hàm COUNTA trong Excel là hàm giúp bạn đếm các ô có chứa dữ liệu nằm trong 1 phạm vi hoặc nhiều phạm vi do bạn tùy chọn. Trong bài viết trước các bạn đã được tìm hiểu về hàm COUNT và bạn cũng đã biết hàm COUNT chỉ dùng để đếm các ô chứa dữ liệu dạng số. Còn trong bài hôm nay, bạn sẽ biết thêm cách dùng hàm COUNTA để đếm tất cả các ô có chứa dữ liệu (kể cả số, văn bản,…) hay còn gọi là các ô không trống.
Hướng dẫn cách dùng hàm COUNTA trong Excel
Hàm COUNTA là hàm thông dụng nên bạn có thể dùng nó trên tất cả các phiên bản Excel hiện nay. Ở đây chúng tôi sẽ hướng dẫn các bạn cách dùng hàm COUNTA trên Excel 2016, nếu bạn đang dùng Excel 2007, 2010 hay Excel 2013 thì cũng sử dụng tương tự.
Cú pháp hàm COUNTA
=COUNTA(value1, [value2], …)
Các giá trị trong hàm COUNTA
value1: Là phạm vi mà bạn muốn đếm. Đây là giá trị bắt buộc.
[value2], …: Là các phạm vi khác bạn cần đếm. Bạn có thể nhập vào tối đa là 255 phạm vi. Đây là giá trị tùy chọn.
Lưu ý
Hàm COUNTA sẽ đếm tất cả các ô có chứa bất kỳ kiểu thông tin nào kể cả ô chứa giá trị lỗi, văn bản trống. Ví dụ, nếu trong phạm vi bạn chọn có chứa 1 ô có công thức trả về chuỗi trống hoặc giá trị lỗi thì hàm COUNTA vẫn sẽ đếm giá trị đó. Hàm COUNTA chỉ không đếm các ô trống.
Nếu bạn muốn đếm các ô dữ liệu đáp ứng theo tiêu chí nào đó thì hãy dùng hàm COUNTIF hoặc hàm COUNTIFS.
Ví dụ về cách sử dụng hàm COUNTA trong Excel
Ví dụ 1: Đếm tổng số các ô chứa dữ liệu trong bảng tính.
Tại ô I5 nhập vào công thức: =COUNTA(C4:F8) và kết quả thu được như sau:
Bạn có thể kiểm tra bằng cách đếm lại các ô có chứa dữ liệu xem có đúng không. Yên tâm rằng nếu bạn làm đúng thì hàm này không bao giờ cho kết quả sai được 😀
Ví dụ 2: Đếm tổng số các ô có chứa dữ liệu ở nhiều phạm vi
Tại ô I12 nhập vào công thức: =COUNTA(C4:F8,C10:F14) và kết quả thu được như hình dưới.
Trong ví dụ này bạn thấy, hàm COUNTA đã đếm tất cả các giá trị nhập vào kể cả giá trị số, văn bản và giá trị lỗi. Ngoài ra bạn cũng thấy, hàm COUNTA đếm được tổng số các ô có chứa dữ liệu ở 2 phạm vi khác nhau.
Download file ví dụ về hàm COUNTA
Lời kết
Hướng Dẫn Cách Dùng Hàm Countifs Trong Excel Qua Ví Dụ
Hướng dẫn cách sử dụng hàm COUNTIFS trong Excel
Hàm COUNTIFS là hàm cơ bản nên bạn có thể sử dụng nó trong hầu hết các phiên bản Excel như Excel 2016, Excel 2013, Excel 2010, Excel 2007 và cả Excel cho MAC, iPhone, Android. Trong bài viết này mình sẽ sử dụng Excel 2016 để làm ví dụ, nếu bạn đang dùng phiên bản Excel khác thì cũng làm tương tự.
Cú pháp của hàm COUNTIFS
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
Các giá trị trong hàm COUNTIFS
criteria_range1: Vùng chọn đầu tiên cần thống kê. Đây là giá trị bắt buộc
criteria1: Điều kiện áp dụng cho vùng chọn criteria_range1. Giá trị này có thể là một số, tham chiếu ô, biểu thức hoặc văn bản. Đây là giá trị bắt buộc.
[criteria_range2, criteria2]: Các cặp vùng chọn và điều kiện bổ sung. Hàm COUNTIFS cho phép tối đa 127 cặp vùng chọn và điều kiện. Đây là giá trị tùy chọn.
Lưu ý khi sử dụng hàm COUNTIFS
Mỗi vùng chọn bổ sung (criteria_range2, criteria_range3,…) phải có cùng số hàng và cột với vùng chọn 1 (criteria_range1) và các vùng chọn không nhất thiết phải liền kề nhau.
Điều kiện áp dụng của mỗi vùng chọn sẽ được áp dụng cho một ô mỗi lần.
Nếu điều kiện của vùng chọn tham chiếu đến 1 ô trống thì hàm COUNTIFS coi ô trống là giá trị 0.
Trong hàm COUNTIFS, bạn có thể dùng các ký tự đại diện như dấu hỏi (?) thay cho bất kỳ ký tự đơn nào và dấu sao (*) thay cho bất kỳ chuỗi ký tự nào trong điều kiện. Khi bạn cần tìm 1 dấu chấm hỏi (?) hay dấu sao (*) thực thì hãy gõ dấu ngã (~) ở trước ký tự đó.
Để thực hành các ví dụ về hàm COUNTIFS chúng ta sẽ thực hành với bảng số liệu như sau
Để sử dụng công thức hàm COUNTIFS với nhiều điều kiện thì yêu cầu đặt ra là: Thống kê số lượng nhà cung cấp không bán được sản phẩm Samsung A5 nào.
Để giải bài toán này chúng ta sẽ có 2 điều kiện:
Điều kiện 1: Tìm số lượng nhà cung cấp bán sản phẩm Samsung A5
Dựa vào bảng đã cho thì ta sẽ có vùng chọn là C4:C13 và điều kiện là ” Samsung A5 “
Điều kiện 2: Tìm số lượng nhà cung cấp không bán được sản phẩm nào. Tức là số lượng bán bằng 0
Dựa vào bảng ta có vùng chọn là F4:F13 và điều kiện là ” 0 “
Dựa vào công thức hàm COUNTIFS và những phân tích ở trên ta sẽ có công thức tính như sau:
=COUNTIFS(C4:C13,”Samsung A5″,F4:F13,”0″)
Kết quả thống kê bằng 1. Bạn hoàn toàn có thể đếm bằng mắt thường để kiểm tra lại kết quả.
Trong nhiều trường hợp bạn sẽ phải kết hợp nhiều tiêu chí, khi đó công thức của bạn sẽ rất dài. Để công thức ngắn gọn hơn bạn hãy liệt kê tất cả các tiêu chí trong mảng, sau đó đưa mảng đó vào điều kiện của hàm COUNTIFS. Và để đếm tổng thì chỉ việc kết hợp hàm COUNTIFS với hàm SUM với công thức như sau:
=SUM(COUNTIFS(criteria_range,{“criteria1″,”criteria2″,”criteria3”,…}))
Áp dụng vào trong bảng tính ở trên, chúng ta sẽ đếm tổng số các sản phẩm không bán được và chưa thống kê được với công thức như sau:
=SUM(COUNTIFS(F4:F13,{“0″,”~?”}))
Và đây là kết quả
Như đã nói trong phần lý thuyết ở đầu bài, trong bảng tính Excel sẽ có lúc có những ô chứ ký tự đơn hoặc chuỗi ký tự. Và để thống kê các ô như vậy với hàm COUNTIFS bạn sẽ sử dụng các ký tự đại diện để đếm.
Dấu hỏi chấm (?) – đại diện cho bất kỳ ký tự đơn nào, sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.
Dấu sao (*) – đại diện cho bất kỳ chuỗi ký tự nào, bạn dùng nó để đếm ô chứa một từ cụ thể hoặc một ký tự trong ô.
Nếu muốn đếm các ô có chứa dấu hỏi chấm (?) hoặc dấu sao (*) thì bạn chỉ việc thêm dấu ngã (~) ở trước dấu hỏi chấm hoặc dấu sao.
Ví dụ chúng ta có bảng với các ký tự, chuỗi ký tự và số như hình dưới. Dựa vào đó chúng ta sẽ thống kê số lượng ô chứa ký tự đơn, ô chứa chuỗi ký tự và tìm dấu hỏi chấm, dấu sao thực.
Download bài tập về hàm COUNTIFS trong Excel
[symple_callout fade_in=”false” button_text=”Tải về” button_url=”https://1drv.ms/u/s!AnIwW-EZfINLhYYcdxf_P55A8Tn07w” button_color=”teal” button_size=”large” button_border_radius=”2px” button_target=”blank” button_rel=”nofollow” button_icon_left=”” button_icon_right=””]Download bài tập về hàm COUNTIFS trong Excel[/symple_callout]
Bạn đang xem bài viết Cách Dùng Hàm Xếp Thứ Hạng Qua Ví Dụ 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!