Xu Hướng 2/2023 # Sắp Xếp Công Thức Trong Excel Sử Dụng Các Hàm Sort Và Sortby # Top 9 View | Hoisinhvienqnam.edu.vn

Xu Hướng 2/2023 # Sắp Xếp Công Thức Trong Excel Sử Dụng Các Hàm Sort Và Sortby # Top 9 View

Bạn đang xem bài viết Sắp Xếp Công Thức Trong Excel Sử Dụng Các Hàm Sort Và Sortby được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. 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.

Tìm hiểu cách sử dụng hàm SORT và SORTBY để trả về phạm vi được sắp xếp dựa trên một hoặc nhiều tiêu chuẩn.

File sử dụng hàm Dynamic Array mới chỉ duy nhất có trên phiên bản mới nhất của Office 365. Bao gồm cả desktop và web app phiên bản Excel.

Trong bài viết này chúng ta hãy nhìn vào 2 hàm Dynamic Array, SORT và SORTBY. Hai hàm này thích hợp trong việc hiển thị danh sách được xếp loại theo thứ tư hiệu suất hoặc thứ tự thời gian (ngày, giờ).

Trong trường hợp ví dụ của chúng ta, chúng ta sẽ sắp xếp nhân viên dựa theo thời gian họ dành thời gian tại văn phòng (thời lượng) trong tuần. Hàm sẽ xếp loại nhân viên những ai dành nhiều thời gian nhất và ai dành ít thời gian nhất tai văn phòng.

Tuy nhiên, trước khi sắp xếp, tôi sẽ giới thiệu nhanh cho bạn biết làm cách nào để thêm tất cả thời lượng với nhau với mỗi nhân viên bằng cách sử dụng hàm SUMIF.

Đối số thứ hai là tiêu chí mà hàm sẽ tìm. Đối với đối số này, chúng ta có thể xác định phạm vi tràn chúng ta đã tạo chỉ liệt kê các nhân viên đối với phòng ban chúng ta đã xác định. Trong ví dụ, tham chiếu phạm vi tràn sẽ là A2#.

Cuối cùng, đối số phạm vi tổng hợp của chúng ta là cột Thời lượng. Đối số này báo Excel thêm tất cả thời lượng cho nhân viên mà chúng ta vừa xác định. Công thức cuối cùng sẽ như sau:

Bây giờ chúng ta vừa tổng hợp tất cả thời gian của mỗi nhân viên, chúng ta có thể sắp xếp nhân viên bằng số giờ mà họ đã làm việc.

Để sử dụng hàm SORTBY, chúng ta chỉ cần xác định dữ liệu cho 3 đối số. Đối số thứ nhất là Array, đây là set giá trị mà chúng ta muốn hàm trả về. Trong trường hợp của chúng ta đó là danh sách nhân viên, được xác định bời phạm vi tràn A2#.

Các metric khác trên sheet thường đơn giản và được liệt kê bên dưới.

Nguồn: Excel Campus

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

Tiếp theo trong chuỗi các bài viết về các hàm thông dụng trong Excel, trong bài viết này chúng tôi xin giới thiệu với các bạn cách sử dụng hàm Hlookup và kèm theo các ví dụ minh hoạ cụ thể giúp bạn dễ dàng nắm bắt hơn.

Ngoài ra, bạn có thể xem lại bài viết khá chi tiết mà chúng tôi đã từng chia sẻ về hàm SUM trong Excel.

Bảng thông tin được sử dụng trong bài viết

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

Lên đầu trang ↑

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

Hàm HLOOKUP sẽ dò tìm một cột (column) chứa giá trị mà bạn cần tìm ở hàng đầu tiên (trên cùng) của một bảng dữ liệu. Nếu tìm thấy, nó sẽ tìm tiếp trong cột này, và sẽ lấy giá trị ở hàng mà bạn đã chỉ định trước. Tên hàm có thể tách ra làm 2 phần: H (horizontal) + Lookup, nghĩa là tìm kiếm theo chiều ngang.

Trong đó

lookup_value: Giá trị dùng để tìm kiếm trong hàng đầu tiên của table_array, giá trị này có thể là một số, một chuỗi, hoặc là một tham chiếu.

table_array: Bảng dùng để dò tìm, có thể là một vùng tham chiếu hoặc là tên (name) của một vùng đã được đặt tên

row_index_num: Số thứ tự của hàng trong table_array chứa kết quả mà bạn muốn tìm kiếm. row_index_num: Số thứ tự này được tính từ trên xuống dưới (hàng chứa lookup_value là hàng đầu tiên)

range_lookup: Là một giá trị kiểu Boolean (chỉ mang giá trị TRUE hoặc FALSE) để chỉ kiểu tìm kiếm: chính xác hay tương đối.

+ TRUE (hoặc 1 là mặc định): Là kiểu dò tìm tương đối HLOOKUP sẽ tìm giá trị lookup_value đầu tiên mà nó tìm được trong hàng đầu tiên của table_array. Trong trường hợp không tìm ra, nó sẽ trả về giá trị lớn nhất mà nhỏ hơn lookup_value.

+ FALSE (hoặc 0): Là kiểu dò tìm chính xác HLOOKUP sẽ tìm chính xác giá trị lookup_value trong hàng đầu tiên của table_array. Trong trường hợp không có, hoặc lookup_value nhỏ hơn giá trị nhỏ nhất trong hàng đầu tiên của table_array, nó sẽ báo lỗi #N/A!

Lưu ý:

Để có kết quả chính xác khi range_lookup = TRUE, bạn phải sắp xếp các giá trị các giá trị trong hàng đầu tiên của table_array từ nhỏ đến lớn.

Nếu hàng đầu tiên của table_array chứa các giá trị kiểu text, bạn có thể dùng các ký tự đại diện cho lookup_value (dấu * đại diện cho nhiều ký tự/ hoặc dấu ? đại diện cho một ký tự)

Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong hàng đầu tiên của table_array, hàm sẽ báo lỗi #N/A!

Nếu không tìm thấy lookup_value khi range_lookup = FALSE, hàm sẽ báo lỗi #N/A!

Nếu row_index_num nhỏ hơn 1, hàm sẽ báo lỗi #VALUE!, còn nếu row_index_num lớn hơn số hàng trong table_array, hàm sẽ báo lỗi #REF!

Lên đầu trang ↑

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

Ví dụ 1: Trong ví dụ sau đây, chúng tôi sẽ trình bày với các bạn cách sử dụng hàm Hlookup.

Lên đầu trang ↑

Cách sử dụng hàm Hlookup với trường hợp cụ thể

Mọi trường hợp xử lý với Vlookup đều có thể được thay thế bằng hàm Hlookup kết hợp với công thức mảng và ngược lại. Trong ví dụ sau, chúng tôi sẽ dựa vào tên mã hàng để điền nội dung cho cột Tên sản phẩm theo hai cách, sử dụng Hlookup và Vlookup.

{=HLOOKUP(B4;TRANSPOSE($C$11:$D$14);2;0)}

Lưu ý:

Vì sử dụng công thức mảng nên chúng ta phải nhấn tổ hợp phím Ctrl + Shift + Enter để kết thúc công thức.

Với cách thực hiện như vậy, bạn có thể chuyển mọi trường hợp sử dụng hàm Vlookup sang dùng Hlookup và ngược lại.

Lên đầu trang ↑

Lời kết

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

Công thức SUBTOTAL trong Excel

Hàm SUBTOTAL có 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.

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.

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ếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì 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 sẽ bỏ qua.

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

1. Tính tổng các hàng được lọc

Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là:

=SUBTOTAL(9,pham_vi)

pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.

2. Đếm các ô được lọc không trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.

Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.

Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ.

Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.

Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.

3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau

Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.

Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.

Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.

Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.

Cách lỗi khi thực hiện hàm SUBTOTAL trong Excel

Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:

#VALUE!: Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.

#DIV/0!: Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số).

#NAME? tên hàm SUBTOTAL sai chính tả.

Cách Sử Dụng Hàm Rank Để Sắp Xếp Thứ Tự Trong Excel

Hàm rank trong excel là một hàm khá phổ biến và được sử dụng rất nhiều trong công việc của kế toán

Chức năng chính của hàm rank là sắp xếp thứ tự, trả về thứ hạng của một giá trị có trong danh sách. Thứ hạng của một giá trị là kích thước của giá trị đó so với các giá trị khác cùng danh sách.

Ưu điểm của hàm rank chính là tạo ra kết quả logic hơn và dễ dàng quản lý hơn, giúp người sử dụng thao tác nhanh hơn khi xuất dữ liệu cần phải sắp xếp.

Cú pháp của hàm rank:= RANK(number,ref,[order])

Diễn giải: Khi bạn cung cấp cho hàm rank một số và một danh sách các số thì hàm rank sẽ cho bạn biết thứ hạng của số đó, theo thứ tự tăng dần hoặc giảm dần trong danh sách.

number: là số lượng bạn muốn tìm.

ref: là một mảng, hay một danh sách tham chiếu đến các số.

order: tùy chọn.

Nếu thứ tự là 0, rank sẽ trả về một danh sách được sắp xếp theo thứ tự giảm dần.

Nếu thứ tự là giá trị bất kỳ khác, rank sẽ trả về một danh sách được sắp xếp theo thứ tự tăng dần.

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ới 2 giá trị trùng lặp thì hàm rank trả về cùng thứ hạng (Nguồn Internet)

Ví dụ, cho 1 danh sách các học sinh với điểm số như hình trên, có 2 học sinh trùng điểm 7 thì hàm rank trả về kết quả xếp hạng là 2 học sinh đó cùng thứ hạng với nhau.

Cách sử dụng hàm rank

Khi muốn đánh giá những tiêu chí không tốt, chúng ta sẽ sử dụng hàm rank để xếp hạng theo thứ tự giảm dần, rồi dựa vào đó để căn cứ và đánh giá tiêu chí nào có giá trị thấp nhất sẽ là tốt nhất và ngược lại.

Xếp hạng theo thứ tự giảm dần với hàm rank (Nguồn Internet)

Dựa vào miêu tả trên, ta có ví dụ: Chúng ta muốn xếp hạng theo số lỗi mà mỗi người mắc phải. Ai ít lỗi nhất thì xếp thứ 1. Số lỗi tăng dần thì thứ tự xếp hạng sẽ tăng dần.

Khi đó chúng ta sẽ dùng hàm rank với Order = 1.

C2=RANK(B2,$B$2:$B$8,1)

Giá trị xét xếp hạng: ô B2.

Vùng danh sách các số tham chiếu: B2:B8. Vùng này cần cố định để không thay đổi trong các công thức.

Thứ tự xếp hạng: giảm dần, dùng số 1.

Như vậy 2 người có lỗi ít nhất (là 1 lỗi) thì đều xếp thứ 1. Người mắc nhiều lỗi nhất (là 8 lỗi) xếp thứ 7.

Tuy nhiên, trong 2 cách sắp xếp trên, chúng ta thấy, hàm rank cho phép những giá trị giống nhau thì cùng chung 1 thứ hạng. Nhưng thực tế, đôi khi, chúng ta cần xếp hạng cụ thể cho từng bậc, nếu giống nhau thì cần có thêm tiêu chí phụ để làm căn cứ đánh giá xếp hạng. Khi đó chúng ta sẽ có thêm 2 kiểu xếp hạng:

Kiểu thứ 1: Xếp hạng liên tục không nhảy bậc (không đồng hạng).

Kiểu thứ 2: Xếp hạng theo điều kiện.

Cách dùng hàm rank trong excel để xếp hạng theo thứ tự tăng dần

Để xác định điểm của mỗi người xếp hạng thứ mấy trong danh sách điểm trung bình B2:B8, chúng ta sử dụng hàm Rank:

Xếp hạng theo thứ tự tăng dần với hàm rank (Nguồn Internet) C2=RANK(B2,$B$2:$B$8)

Xét điểm TB tại ô B2, trong danh sách điểm từ B2:B8

Không sử dụng tham số tại [order] tức là sắp xếp theo thứ tự tăng dần.

Như vậy ta thấy:

Người có điểm TB cao nhất là 9.5 được xếp hạng 1.

Người có điểm TB là 8.1 được xếp thứ 2, trong đó có 2 người cùng điểm, do đó có 2 vị trí xếp hạng 2 là dòng 3 và dòng 6.

Do 2 người đồng hạng 2 nên sẽ không có xếp hạng 3. Hạng tiếp theo sẽ là hạng 4, ứng với số điểm TB là 7.6

Một số ví dụ dùng hàm rank

Bước 1: Mở file excel có chứa dữ liệu cần được sắp xếp thứ hạng ra.

Mở file excel có chứa dữ liệu cần sắp xếp (Nguồn Internet)

Bước 2: Nhập công thức hàm rank rồi sau đó nhấp chuột chọn điểm của giá trị đầu tiên để tạo công thức number).

Tiến hành nhập công thức hàm rank trong excel (Nguồn Internet)

Bước 4:

Đối với cách sắp xếp theo thứ tự giảm dần (từ cao xuống thấp) trong hàm rank: Sau bước 3 thì tiếp tục nhập [,0] để chọn kiểu xếp hạng theo thứ tự giảm dần trong hàm rank, chọn order rồi cuối cùng nhấn phím enter để hoàn thành phép tính.

Đối với cách sắp xếp theo thứ tự tăng dần (từ thấp lên cao) trong hàm rank: Tương tự như cách sắp xếp theo tự giảm dần, sau khi thực hiện xong bước 3, ta tiếp tục nhâp [,1] để chọn kiểu xếp hạng theo thứ tự tăng dần trong hàm rank, sau đó nhấn Enter và Fill công thức ra tất cả giá trị cần tính xếp hạng.

Cập nhật thông tin chi tiết về Sắp Xếp Công Thức Trong Excel Sử Dụng Các Hàm Sort Và Sortby trên website Hoisinhvienqnam.edu.vn. 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. Chúc bạn một ngày tốt lành!