Xu Hướng 2/2024 # Các Hàm Thống Kê Trong Excel Có Điều Kiện, Theo Thời Gian, Số Lượng # Top 5 Xem Nhiều

Bạn đang xem bài viết Các Hàm Thống Kê Trong Excel Có Điều Kiện, Theo Thời Gian, Số Lượng được cập nhật mới nhất tháng 2 năm 2024 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.

Các hàm thống kê trong Excel hay các hàm tính toán, hàm ngày tháng đều hỗ trợ công việc, học tập của mọi lứa tuổi hiện nay một cách hiệu quả nhất. Không chỉ nắm được tên mà cú pháp cũng như ứng dụng vào các bài toán thực tiễn có tốt hay không còn tùy thuộc vào mỗi bạn hiện nay.

Tổng hợp các hàm thống kê trong Excel 2024, 2013, 2010, 2007 Tổng hợp các hàm thống kê trong Excel

1. NHÓM HÀM VỀ THỐNG KÊ

AVEDEV (number1, number2, …): Tính trung bình độ lệch tuyệt đối các điểm dữ liệu theo trung bình của chúng. Thường dùng làm thước đo về sự biến đổi của tập số liệu.AVERAGE (number1, number2, …): Tính trung bình cộng.AVERAGEA (number1, number2, …): Tính trung bình cộng của các giá trị, bao gồm cả những giá trị logic.AVERAGEIF (range, criteria1): Tính trung bình cộng của các giá trị trong một mảng theo một điều kiện.AVERAGEIFS (range, criteria1, criteria2, …): Tính trung bình cộng của các giá trị trong một mảng theo nhiều điều kiện.COUNT (value1, value2, …): Đếm số ô trong danh sách.COUNTA (value1, value2, …): Đếm số ô có chứa giá trị (không rỗng) trong danh sách.COUNTBLANK (range): Đếm các ô rỗng trong một vùng.COUNTIF (range, criteria): Đếm số ô thỏa một điều kiện cho trước bên trong một dãy. Trên thực tế hàm thống kê COUNTIF là một trong những hàm thống kê có điều kiện được sử dụng nhiều trên Excel chính vì thế bạn đọc quan tâm có thể tìm đọc và tham khảo những thông tin, hướng dẫn, thủ thuật dùng hàm này qua bài viết hàm COUNTIF đếm có điều điện trong Excel mà chúng tôi đã thực hiện trước đó.

SKEW (number1, number2, …): Trả về độ lệch của phân phối, mô tả độ không đối xứng của phân phối quanh trị trung bình của nó.SMALL (array, k): Trả về giá trị nhỏ nhất thứ k trong một tập số.STDEV (number1, number2, …): Ước lượng độ lệch chuẩn trên cơ sở mẫu.STDEVA (value1, value2, …): Ước lượng độ lệch chuẩn trên cơ sở mẫu, bao gồm cả những giá trị logic.STDEVP (number1, number2, …): Tính độ lệch chuẩn theo toàn thể tập hợp.STDEVPA (value1, value2, …): Tính độ lệch chuẩn theo toàn thể tập hợp, kể cả chữ và các giá trị logic.VAR (number1, number2, …): Trả về phương sai dựa trên mẫu.VARA (value1, value2, …): Trả về phương sai dựa trên mẫu, bao gồm cả các trị logic và text.VARP (number1, number2, …): Trả về phương sai dựa trên toàn thể tập hợp.VARPA (value1, value2, …): Trả về phương sai dựa trên toàn thể tập hợp, bao gồm cả các trị logic và text.TRIMMEAN (array, percent): Tính trung bình phần trong của một tập dữ liệu, bằng cách loại tỷ lệ phần trăm của các điểm dữ liệu ở đầu và ở cuối tập dữ liệu.

2. NHÓM HÀM VỀ PHÂN PHỐI XÁC SUẤT

BETADIST (x, alpha, beta, A, B): Trả về giá trị của hàm tính mật độ phân phối xác suất tích lũy beta.BETAINV (probability, alpha, beta, A, B): Trả về nghịch đảo của hàm tính mật độ phân phối xác suất tích lũy beta.BINOMDIST (number_s, trials, probability_s, cumulative): Trả về xác suất của những lần thử thành công của phân phối nhị phân.CHIDIST (x, degrees_freedom): Trả về xác xuất một phía của phân phối chi-squared.CHIINV (probability, degrees_freedom): Trả về nghịch đảo của xác xuất một phía của phân phối chi-squared. Thực tế không phải ai cũng sử dụng hàm CHIINV thế nhưng khi cần tính toán giá trị nghịch đảo của xác suất chúng ta thực sự cần tới hàm CHIINV nó là một công cụ tính toán suất sắc.

CHITEST (actual_range, expected_range): Trả về giá trị của xác xuất từ phân phối chi-squared và số bậc tự do tương ứng.CONFIDENCE (alpha, standard_dev, size): Tính khoảng tin cậy cho một kỳ vọng lý thuyếtCRITBINOM (trials, probability_s, alpha): Trả về giá trị nhỏ nhất sao cho phân phối nhị thức tích lũy lớn hơn hay bằng giá trị tiêu chuẩn. Thường dùng để bảo đảm các ứng dụng đạt chất lượng…Hàm thống kê theo thời gian EXPONDIST (x, lambda, cumulative): Tính phân phối mũ. Thường dùng để mô phỏng thời gian giữa các biến cố…FDIST (x, degrees_freedom1, degrees_freedom2): Tính phân phối xác suất F. Thường dùng để tìm xem hai tập số liệu có nhiều mức độ khác nhau hay không…FINV (probability, degrees_freedom1, degrees_freedom2): Tính nghịch đảo của phân phối xác suất F. Thường dùng để so sánh độ biến thiên trong hai tập số liệu.FTEST (array1, array2): Trả về kết quả của một phép thử F. Thường dùng để xác định xem hai mẫu có các phương sai khác nhau hay không…

https://thuthuat.taimienphi.vn/cac-ham-thong-ke-trong-excel-5741n.aspx Microsoft đã cho ra mắt phiên bản Microsoft Office 2024. Đây là bộ ứng dụng văn phòng được cập nhật thêm rất nhiều tính năng hữu ích. Giúp tạo bảng, tính toán, thống kê các phép tính, cân đối thu chi cho doanh nghiệp, cơ quan, hay cả những công ty lớn.

Cách Sử Dụng Các Hàm Thống Kê Có Điều Kiện Trong Excel

Thống kê số liệu là bước quan trọng giúp bạn hoạch định trong quá trình kinh doanh của mình. Trong quá trình tính toán bạn không thể không sử dụng các hàm thống kê nhanh trong Excel. Ở bài viết này giúp các bạn tìm hiểu các hàm thống kê đó.

Với hàm tính trung bình có điều kiện có 2 dạng:

– Tính trung bình với 1 điều kiện: Averageif

– Tính trung bình với nhiều điện kiện: Averageifs

a) Hàm Averageif

Ý nghĩa: Hàm thực hiện tính trung bình cộng của các giá trị số trong vùng dữ liệu cho trước với một điều kiện nhất định.

Cú pháp: Averageif(Range, Criteria, Average_range)

Trong đó:

– Range: Vùng dữ liệu chứa điều kiện tính giá trị trung bình.

– Criteria: Điều kiện tính giá trị trung bình, có thể là ô địa chỉ chứa điều kiện hoặc nhập trực tiếp điều kiện trong công thức.

– Average_range: Vùng dữ liệu chứa các giá trị cần tính giá trị trung bình theo điều kiện.

Chú ý:

– Giá trị để tính trung bình phải là giá trị số.

– Trong biểu thức điều kiện có chứa các kí tự thay thế như ? và * bạn cần đặt dấu ~ trước biểu thức điều kiện đó.

Ý nghĩa: Hàm thực hiện tính trung bình cộng của các giá trị số cho trước trong vùng dữ liệu thỏa mãn nhiều hơn 1 điều kiện.

Cú pháp: AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

Trong đó:

– Average_range: Vùng dữ liệu chứa các giá trị cần tính giá trị trung bình theo điều kiện.

– Criteria1, criteria2: Điều kiện tính giá trị trung bình, có thể là ô địa chỉ chứa điều kiện hoặc nhập trực tiếp điều kiện trong công thức, tối đa 127 điều kiện.

– Criteria_range1, criteria_range2: Vùng dữ liệu chứa điều kiện tương ứng với Criteria1, criteria2… tương ứng có tối đa 127 vùng điều kiện.

Chú ý:

– Giá trị để tính trung bình phải là giá trị số.

– Trong biểu thức điều kiện có chứa các kí tự thay thế như ? và * bạn cần đặt dấu ~ trước biểu thức điều kiện đó.

2. Hàm đếm có điều kiện a) Hàm Countif

Ý nghĩa: Đếm số ô thỏa mãn 1 điều kiện trong phạm vi.

Cú pháp: Countif(range, criteria)

Trong đó:

– range: Vùng dữ liệu chứa dữ liệu cần đếm hay thống kê.

– criteria: Điều kiện đếm.

– Khi bạn sử dụng dấu phẩy để phân cách phần nghìn trong dữ liệu số, chú ý khi viết điều kiện bạn viết đúng giá trị dấu phẩy trong bảng dữ liệu.

Ý nghĩa: Đếm số ô thỏa mãn nhiều hơn 1 điều kiện trong vùng dữ liệu xác định.

Cú pháp: Countif(criteria_range1, criteria1, criteria_range2, criteria2,…)

Trong đó:

– criteria_range1, criteria_range2,…: Vùng dữ liệu chứa điều kiện cần đếm.

– criteria1, criteria2,…: Điều kiện tương ứng với các vùng dữ liệu criteria_range1, criteria_range2,…

a) Hàm Dmax

Ý nghĩa: Hàm Dmax trả về giá trị lớn nhất trong một vùng dữ liệu với điều kiện cho trước.

Cú pháp: =DMAX(database, field, criteria)

Trong đó:

– field: trường (cột) cần lấy giá trị lớn nhất. Các bạn có thể nhập trực tiếp tiêu đề cột trong dấu ngoặc kép hay một số thể hiện vị trí cột trong database: 1 cho cột đầu tiên, 2 cho cột thứ 2…, các bạn cũng có thể nhập ô chứa tiêu đề cột cần sử dụng ví dụ B3, C1…

– criteria: là phạm vi ô chứa điều kiện, các bạn có thể chọn bất kỳ miễn sao phạm vi đó chứa ít nhất một Tiêu đề cột và ô dưới tiêu đề cột chứa điều kiện cho cột trong đó tiêu đề cột trùng với tiêu đề cột trong bảng dữ liệu.

Ghi chú:

– Các bạn nên đặt phạm vi điều kiện criteria trên trang tính để khi thêm dữ liệu thì phạm vi chứa điều kiện không thay đổi.

– Phạm vi điều kiện cần tách rời không chèn lên danh sách hay cơ sở dữ liệu cần xử lý.

– criteria bắt buộc phải chứa ít nhất Tiêu đề cột và một ô chứa điều kiện dưới tiêu đề cột.

b) Hàm Dmin

Ý nghĩa: Hàm Dmin trả về giá trị nhỏ nhất trong một vùng dữ liệu với điều kiện cho trước.

Cú pháp: =DMIN(database, field, criteria)

Trong đó:

– field: trường (cột) cần lấy giá trị lớn nhất. Các bạn có thể nhập trực tiếp tiêu đề cột trong dấu ngoặc kép hay một số thể hiện vị trí cột trong database: 1 cho cột đầu tiên, 2 cho cột thứ 2…, các bạn cũng có thể nhập ô chứa tiêu đề cột cần sử dụng ví dụ B3, C1…

– criteria: là phạm vi ô chứa điều kiện, các bạn có thể chọn bất kỳ miễn sao phạm vi đó chứa ít nhất một Tiêu đề cột và ô dưới tiêu đề cột chứa điều kiện cho cột trong đó tiêu đề cột trùng với tiêu đề cột trong bảng dữ liệu.

Ghi chú:

– Các bạn nên đặt phạm vi điều kiện criteria trên trang tính để khi thêm dữ liệu thì phạm vi chứa điều kiện không thay đổi.

– Phạm vi điều kiện cần tách rời không chèn lên danh sách hay cơ sở dữ liệu cần xử lý.

– criteria bắt buộc phải chứa ít nhất Tiêu đề cột và một ô chứa điều kiện dưới tiêu đề cột.

Ví dụ:

Hướng Dẫn Cách Sử Dụng Các Hàm Thống Kê Có Điều Kiện Trong Excel

Trong Excel có rất nhiều hàm sử dụng cho mục đích thống kê, bởi việc thống kê được thực hiện chủ yếu thông qua công cụ Excel. Trong bài viết này chúng ta sẽ cùng nhau tìm hiểu một số hàm thống kê có điều kiện trong Excel. Các hàm đó là những hàm nào? Công dụng và cách sử dụng ra sao? Các bạn sẽ có câu trả lời sau khi tìm hiểu bài viết này.

Tính trung bình có điều kiện

Nhắc tới tính trung bình, chúng ta nghĩ ngay tới nhóm hàm AVERAGE. Và để phục vụ cho việc tính trung bình theo điều kiện thì Excel cung cấp cho chúng ta 2 hàm là:

AVERAGEIF(range,criteria,average_range): Tính trung bình theo 1 điều kiện

AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2, criteria2],…): Tính trung bình theo nhiều điều kiện

Về cấu trúc và cách viết các thành phần trong hàm thì hai hàm này khá giống với SUMIF và SUMIFS, nhưng thay vì tính tổng thì sẽ tính trung bình của các giá trị.

Để giải thích rõ hơn về 2 hàm này chúng ta xét ví dụ sau

Vùng tính trung bình: cột Mức lương

Vùng chứa điều kiện: cột Bộ phận

Điều kiện: Kinh doanh

Chúng ta có thể sử dụng cả 2 hàm để tính trong trường hợp này, bởi yêu cầu này chỉ có 1 điều kiện.

Kết quả như sau:

E12=AVERAGEIF(C2:C9,”Kinh doanh”,D2:D9)

F12=AVERAGEIFS(D2:D9,C2:C9,”Kinh doanh”)

Với yêu cầu 2: Tính mức lương trung bình bộ phận kinh doanh trong tháng 2

Phân tích yêu cầu:

Vùng tính trung bình: cột Mức lương

Vùng điều kiện 1: cột Bộ phận

Điều kiện 1: Kinh doanh

Vùng điều kiện 2: cột Tháng

Điều kiện 2: 2 (tháng 2)

Như vậy trong yêu cầu này có tới 2 điều kiện. Trường hợp này không dùng được hàm AverageIF mà chỉ dùng được hàm AverageIFS

Cách viết hàm như sau:

F13=AVERAGEIFS(D2:D9,C2:C9,”Kinh doanh”,A2:A9,2)

Hàm đếm theo điều kiện

Trong yêu cầu đếm thì chúng ta có nhóm hàm COUNT. Việc đếm theo điều kiện có thể sử dụng hàm COUNTIF để đếm theo 1 điều kiện, hàm COUNTIFS để đếm theo nhiều điều kiện.

Các bài viết về hàm đếm theo điều kiện đã có sẵn khá nhiều trên blog.hocexcel.online. Các bạn có thể tham khảo các bài viết sau:

Cách dùng hàm COUNTIFS và COUNTIF với nhiều điều kiện Hướng dẫn cách đếm theo nhiều điều kiện trong Excel bằng hàm COUNTIFS

Hàm tìm giá trị lớn nhất, nhỏ nhất theo điều kiện

Từ phiên bản OFFICE 2024, OFFICE 365 trở đi chúng ta có thêm hàm giúp tìm giá trị lớn nhất, nhỏ nhất theo điều kiện. Cụ thể là

MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],…) Tìm giá trị lớn nhất theo 1 hoặc nhiều điều kiện

MINIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],…) Tìm giá trị nhỏ nhất theo 1 hoặc nhiều điều kiện

Ví dụ về 2 hàm này như sau:

Vùng xét giá trị lớn nhất: cột Mức lương

Vùng điều kiện 1: cột Bộ phận

Điều kiện 1: Kinh doanh

Vùng điều kiện 2: cột Tháng

Điều kiện 2: 2 (tháng 2)

Như vậy cần dùng tới 2 điều kiện để tìm ra giá trị lớn nhất. Cách viết hàm MAXIFS như sau:

E12=MAXIFS(D2:D9,C2:C9,”Kinh doanh”,A2:A9,2)

Yêu cầu 2: tìm mức lương nhỏ nhất trong tháng 2

Phân tích yêu cầu:

Vùng xét giá trị nhỏ nhất: cột Mức lương

Vùng điều kiện 1: cột Tháng

Điều kiện 1: giá trị là 2 (tháng 2)

Như vậy trường hợp này chỉ cần dùng 1 điều kiện. Tuy nhiên hàm tìm giá trị nhỏ nhất theo điều kiện chỉ có hàm MINIFS nên chúng ta sử dụng hàm này như sau:

E13=MINIFS(D2:D9,A2:A9,2)

Hàm Countifs Trong Excel, Hàm Thống Kê Có Điều Kiện, Ví Dụ Minh Họa

Điều kiện trong hàm COUNTIFS có thể là ký tự, số, ngày tháng,…vì vậy bạn có thể áp dụng một cách linh hoạt vào việc tính toán trong bảng tính excel để đạt hiệu quả công việc cao nhất. Hướng dẫn sử dụng hàm CountIFS là bài học cơ bản trong giáo trình Học Excel trên chúng tôi đã từng đề cập đến trước kia.

Hàm COUNTIFS thống kê tính toán

Hàm COUNTIFS cũng được sử dụng rất nhiều trường hợp khác nhau, tuy nhiên với người mới thì trong bài viết này chúng tôi sẽ đưa ra ví dụ cụ thể, đơn giản nhất đến bạn đọc.

HƯỚNG DẪN DÙNG HÀM COUNTIFS TRONG EXCEL – VÍ DỤ MINH HỌA

1. Cú pháp hàm COUNTIFS trong EXCEL

Hàm Excel COUNTIFS trả về số lượng các ô thỏa mãn một hoặc nhiều điều kiện. COUNTIFS có thể được sử dụng với các điều kiện như ngày, số, text, và các điều kiện khác.

Cú pháp: COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)

range1 – phạm vi đầu tiên để đánh giá.

criteria1 – xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc.

range2 – [điều kiện bổ sung] phạm vi thứ hai để đánh giá.

criteria2 – [điều kiện bổ sung] xác định phạm vi áp dụng điều kiện thứ 2.

2. Một số lưu ý khi dùng hàm COUNTIFS trong EXCEL

COUNTIFS đếm số ô trong phạm vi khớp với điều kiện được cung cấp. Không giống như hàm COUNTIF, hàm COUNTIFS có thể áp dụng nhiều hơn một điều kiện với nhiều hơn một phạm vi. Phạm vi và điều kiện được áp dụng theo cặp, và chỉ yêu cầu cặp đầu tiên. Đối với mỗi điều kiện bổ sung, bạn phải cung cấp các cặp phạm vi / điều kiện khác. Cho phép đến 127 cặp phạm vi / điều kiện.

Lưu ý:

– Mỗi vùng dữ liệu bổ sung phải có cùng số hàng và số cột với vùng dữ liệu đầu tiên (range 1). COUNTIFS có thể đếm những vùng dữ liệu liền kề và không liền kề. Nếu vùng dữ liệu cung cấp không phù hợp, bạn sẽ nhận được lỗi # VALUE.

– Các ký tự đại diện? và * có thể được sử dụng trong điều kiện. Dấu hỏi chấm (?) có thể khớp với bất kỳ ký tự nào và dấu hoa thị (*) khớp với bất kỳ dãy ký tự nào.

– Để tìm dấu hỏi chấm (?) hoặc dấu hoa thị (*), sử dụng dấu ngã (~) trước dấu hỏi chấm trước hoặc dấu hoa thị (tức là ~ ?, ~ *).

Cấu trúc của hàm COUNTIFS khá khác so với hàm Count, Hàm COUNTIFS có thể có 127 cặp Criteria_range,Criteria

.Criteria_range là một hay nhiều ô dạng số, tên, mảng, hay tham chiếu chứa số.Criteria là ở dạng số, biểu thức, tham chiếu ô hay chuỗi văn bản xác định. Ô trống hoặc ô chứa dữ liệu dạng text bị bỏ qua.

Mẹo Để đếm các ô chứa dữ liệu trong bảng tính thì các bạn dùng hàm Counta

3. Các ví dụ cụ thể hàm COUNTIFS trong EXCEL

Ví dụ1: Cho bảng điểm một số học sinh, hãy đếm số học sinh đạt điểm 9 và 8 ở cả 3 môn:

Để đếm được ta dùng công thức COUNTIFS : Đếm số học sinh đạt cả 3 điểm 9 với công thức

Công thức ô D11 là : = COUNTIFS(D5:D9,9,E5:E9,9,F5:F9,9)

Còn nếu bạn muốn đếm số học sinh đạt cả 3 điểm 8 thì sử dụng công thức sau:

Công thức ô D11 là : = COUNTIFS(D5:D9,8,E5:E9,8,F5:F9,8)

Và các bạn đã đếm được số lượng học sinh đạt cả 3 điểm 8 hay 3 điểm 9 cùng lúc, tương tự với các điểm khác thì bạn cũng làm như vậy. Bạn cũng có thể lọc các điểm khác nhau.

https://thuthuat.taimienphi.vn/ham-countifs-trong-excel-962n.aspx

Các Hàm Liệt Kê Có Điều Kiện Trong Excel Là Gì?

Các hàm dùng để liệt kê trong excel

Cấu trúc hàm: = IFERROR(value, value_if_error).

Trong đó:

– IFERROR:là tên hàm sử dụng để phát hiện giá trị lỗi và trả về thành quả mà bạn cần.

– Value: thành quả mà con người cần chọn lựa có phải lỗi hay không.

– Value_if_error: giá trị trả về nếu thành quả value là lỗi.

Nếu giá trị của value là lỗi (#N/A, #REF,…) thì hàm IFERROR sẽ trả về là value_if_error, nếu không thì sẽ trả về là giá trị value.

Cú pháp hàm: =INDEX(array, row_num, [column_num]).

Trong đó:

– INDEX: là tên hàm sử dụng để lấy lại giá trị ở dòng n ( row_num) trong mảng dữ liệu array.

– Array: Mảng dữ liệu. Ở bài đăng này mánh phần mềm chỉ hướng dẫn hàm INDEX với array là mảng dữ liệu chỉ chứa một cột.

– row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị.

Chẳng hạn như sử dụng hàm INDEX:

Trong đó:

– SMALL: là tên hàm sử dụng để lựa chọn giá trị nhỏ thứ k.

– Array: Mảng hoặc phạm vi dữ liệu dạng số mà bạn mong muốn lựa chọn giá trị nhỏ thứ k của nó.

– K: Vị trí (từ thành quả nhỏ nhất) trong mảng hoặc phạm vi dữ liệu cần trả về.

Với đòi hỏi mà thủ thuật ứng dụng đưa rõ ra ở trên, bạn phải cần liệt kê 3 mục doanh thu của Chi nhánh Hải Dương ở vào cột J.

Ở cột J4 bạn nhập công thức: =IFERROR(INDEX($G$1:$G$12,SMALL(IF($J$2=$B$3:$B$12,ROW($G$3:$G$12)),ROW(A1))),””) và ấn tổ hợp phím Ctrl + Shif + Enter. Và sao chép phương pháp cho các dòng khác của cột.

$G$1:$G$12: là mảng dữ liệu tham chiếu cần lấy thành quả để trả về.

$J$2 là vị trí của ô chứa thành quả cần tìm.

$B$3:$B$12 là mảng dữ liệu cần tra cứu.

Vậy phụ thuộc vào công thức trên, bạn thay đổi các mục vị trí của mảng dữ liệu và ô dữ liệu tương ứng với vị trí bảng tính của mình.

Bạn nên để thành quả tuyệt đối của các mảng dữ liệu để tránh sai sót khi sao chép công thức.

Nếu như vùng dữ liệu tham chiếu của bạn dài, bạn cần phải sao chép phương pháp cho tất cả cột hoặc tối thiểu là bằng số dòng của vùng tham chiếu cho không gây hại. Ad có sao chép công thức cho đến ô J9, tuy nhiên do chỉ có 3 giá trị tìm kiếm được thỏa mãn điều kiện Chi nhánh Hải Dương nên chỉ các ô J3-J4 có dữ liệu số, các ô khác sẽ là dữ liệu trắng và sẽ không ảnh hưởng đến thẩm mỹ của báo cáo.

Để hạn chế nhầm lẫn, trước tiên, chúng ta cần hiểu những giá trị nào được gọi là là các giá trị duy nhất trong Excel. Thành quả duy nhất là các giá trị chỉ hiện hữu độc nhất một lần trong tất cả danh sách.

Để trích xuất một danh sách các thành quả duy nhất trong Excel, bạn có thể sử dụng một trong những công thức sau đây.

TRONG ĐÓ:

A2: A10 – danh sách nguồn B1 – ô đầu của danh sách độc nhất trừ đi 1. Trong ví dụ này, chúng ta tiếp tục danh sách các giá trị duy nhất ở B2, và thế nên chúng ta đưa B1 vào phương pháp (B2-1 = B1). nếu danh sách các thành quả độc nhất của bạn tiếp tục ở ô C3, ta sẽ thay đổi $B$1:B1 thành $C$2:C2.

Chú thích. Bởi vì công thức tham chiếu ô bên trên ô trước tiên của danh sách các thành quả duy nhất, thường là tiêu đề cột (như trong ví dụ này là B1), có thể bạn phải cần cam kết tiêu đề của bạn là một tên độc nhất không hiện diện ở bất cứ đâu trong cột.

Ở chẳng hạn như này đang trích xuất các tên độc nhất từ cột A (chính xác hơn là trong phạm vi A2: A20), và ảnh chụp màn hình sau sẽ minh họa cho công thức sẽ dùng:

– Chọn lựa một trong các công thức dựa theo dữ liệu của bạn. – Nhập công thức vào ô trước tiên của danh sách các giá trị duy nhất (trong ví dụ sẽ là ô B2). – Nếu như bạn đang dùng công thức mảng, nhấn Ctrl + Shift + Enter. nếu như bạn chọn công thức bình thường, hãy nhấn phím Enter như thường lệ. – Sao chép phương pháp xuống càng xa càng tốt bằng việc kéo chốt xử lý. Vì cả hai công thức giá trị độc nhất được gói gọn trong hàm IFERROR, bạn có khả năng sao chép phương pháp đến cuối bảng mà không làm lộn xộn dữ liệu vì bất kỳ lỗi nào dù các giá trị duy nhất đã được trích xuất ra sao.

Lộc Đạt-tổng hợp

Tham khato ( thuthuatphanmem, blog.hocexcel.online, … )

Hướng Dẫn Sử Dụng Chức Năng Subtotal Thống Kê Dữ Liệu Theo Điều Kiện Trong Excel

Đôi khi ta cần thể hiện doanh số bán hàng theo từng nhân viên, hoặc số lượng thực phẩm theo vùng miền, hay nhiệt độ trung bình của từng tháng… Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn làm điều trên thông qua chức năng SUBTOTAL thống kê dữ liệu theo điều kiện.

Chức năng SUBTOTAL thống kê dữ liệu theo điều kiện

Trong ví dụ lần này, ta sẽ mô phỏng đơn giản nhất bảng thống kê nhiệt độ và độ ẩm của Hà Nội 3 tháng đầu năm 2024.

Công việc của ta bây giờ là thống kê nhiệt độ và độ ẩm trung bình từng tháng. Và ta sẽ thực hiện bằng công cụ SUBTOTAL.

Công cụ SUBTOTAL Thêm các đối tượng thích hợp để sử dụng công cụ SUBTOTAL

Với một bảng dài ngoằng như này, hẳn bạn sẽ không muốn thêm những dòng tính trung bình vào dưới mỗi khi kết thúc tháng đâu nhỉ? Hãy đến với SUBTOTAL.

Chức năng SUBTOTAL sẽ giúp bạn tính được kết quả trung bình theo từng tháng một cách nhanh chóng.

Bước 1: Chọn toàn bộ bảng tính (trong trường hợp bạn chưa biết, chỉ cần chọn một ô bất kì trong bảng tính rồi chọn Subtotal, Excel sẽ tự tính toán và chọn tất cả những ô còn lại).

Bước 2: Chọn Subtotal. Một hộp thoại sẽ hiện ra:

Use function: Hàm được sử dụng để tính. Với hàm SUM, Excel sẽ trả về tổng, hàm AVERAGE trả về trung bình,… trả lời cho câu hỏi: Phương thức thống kê?

Add subtotal to: Mục được áp dụng hàm ở trên. Chọn các cột cần thiết và Excel sẽ tính toán giá trị ở đó. Trả lời câu hỏi: Thống kê dữ liệu gì?

Ngoài ra ta có:

Replace current subtotal: Nếu đã tồn tại trước đó một subtotal, nó sẽ thay thế bằng subtotal mới.

Page break between groups: Bạn biết page break (ngắt trang) chứ? Tùy chọn này giúp cho mỗi nhóm của at each change in được nằm trong một trang khác nhau.

Summary below data: Đưa function xuống dưới các nhóm. Nếu bỏ chọn ô này, kết quả sẽ không mất, mà đưa lên đầu.

Ok, ấn đi các bạn!

At each change in thời gian, use function AVERAGE, Add subtotal to Nhiệt độ ngày, Nhiệt độ đêm, Độ ẩm.

Mình biết điều đang xảy ra với trang tính của các bạn.

Để khắc phục, đầu tiên là Undo đi nào (Ctrl+Z).

Tại cột Tháng, nhập công thức vào như sau:

Sau đó định dạng lại về General nếu kết quả đang ở dạng Date.

Sử dụng autofill áp dụng kết quả cho các ô còn lại. Trong trường hợp bạn chưa biết autofill, hãy xem bài viết này.

Kết quả:

– Ứng dụng SUBTOTAL kết hợp COUNTIF thiết lập số thứ tự thành phần.

– Tổng quan về công cụ SUBTOTAL

Cập nhật thông tin chi tiết về Các Hàm Thống Kê Trong Excel Có Điều Kiện, Theo Thời Gian, Số Lượng 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!