CÁC HÀM NÂNG CAO VÀ THÔNG KÊ

1. Hàm LARGE( )

Chức năng: Trả về giá trị lớn nhất thứ k trong một tập số liệu. Có thể dùng hàm này để chọn lựa giá trị dựa theo vị trí tương đối của nó. Ví dụ bạn có thể dùng hàm LARGE( ) để tính số điểm cao nhất, cao thứ nhì, cao thứ ba, v.v…
Cú pháp: LAGRE(array, k)

• array: Mảng hay dãy số liệu dùng để xác nhận giá trị lớn nhất thứ k.
• k: Vị trí (tính từ gí trị lớn nhất) trong mảng hay dãy số liệu.

Lưu ý:

• Nếu array rỗng, LARGE( ) sẽ trả về giá trị lỗi #NUM!
• Nếu k<0 hay k lớn hơn số lượng các số có trong array, LAEGE( ) sẽ trả về giá trị lỗi #NUL!
• Giả sử n là số lượng các số có trong array, thì LARGE(array, 1) trả về giá trị lớn nhất (MAX), và LARGE(array, n) sẽ trả về giá trị nhỏ nhất (MIN).

Ví dụ: Cho biết điểm cao thứ 3 trong danh sách dự thi.
=LARGE(C3:C8,3) -> 7.5

 

2. Hàm SMALL( )

Chức năng: Trả về giá trị nhỏ nhất thứ k trong một tập số liệu. Có thể dùng hàm này để chọn lựa giá trị dựa vào vị trí tương đối của nó.
Cú pháp: SMALL(array, k)

• array: Mảng hay dãy số liệu dùng để xác nhận giá trị nhỏ nhất thứ k.
• k: Vị trí (tính từ gí trị nhỏ nhất) trong mảng hay dãy số liệu.

Lưu ý:

• Nếu array rỗng, SMALL( ) sẽ trả về giá trị lỗi #NUM!
• Nếu k<0 hay k lớn hơn số lượng các số có trong array, SMALL( ) sẽ trả về giá trị lỗi #NUL!
• Giả sử n là số lượng các số có trong array, thì SMALL(array, 1) trả về giá trị nhỏ nhất (MIN), và LARGE(array, n) sẽ trả về giá trị lớn nhất (MAX).

Ví dụ: Cho biết điểm nhỏ thứ 2 trong danh sách dự thi.
=SMALL(C3:C8,3) ->5

 3. Hàm COUNTIFS( )
Chức năng: Đếm số lượng các ô trong một vùng thỏa điều kiện cho trước.
Cú pháp: COUNTIFS(range1, criteria1, range2, criteria2, …)

• Range1, range2…: Có thể có từ 1 đến 127 dãy các ô để đếm. Chúng có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số, ô rỗng sẽ được bỏ qua.
• Criteria1, criteria2…: Có thể có từ 1 đến 127 điều kiện để đếm. Chúng có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, “32”, hoặc “>32”, hoặc “apple:, v.v…

Lưu ý:

• Mỗi ô trong range chỉ được đếm nếu tất cả các điều kiện tương ứng với ô đó đều đúng.
• Nếu criteria là một ô rỗng, Excel sẽ xem như ô đó chứa số 0.
• Có thể dùng các ký tự đại diện trong các điều kiện: Dấu ? đại diện cho một ký tự, dấu * đại diện cho cho nhiều ký tự (Nếu điều kiện tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *.
• Khi điều kiện để đếm là những ký tự, COUNTIFS( ) không phân biệt chữ thường hay chữ hoa.

Ví dụ:

 4. Hàm SUMIFS( )
Chức năng: Tính tổng các ô trong một vùng thỏa nhiều điều kiện cho trước.
Cú pháp: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

• Sum_range: Dãy có ô để tính tổng, có thể là ô chứa số, tên, mảng, hay tham chiếu đến ô chứa số. Ô rỗng và ô chứa giá trị text sẽ được bỏ qua.
• Criteria1_range1, criteria2_range2…: Có thể có từ 1 đến 127 vùng để liên kết với các điều kiện trong vùng.
• Criteria1, criteria2…: Có thể có từ 1 đến 127 điều kiện để tính tổng. Chúng có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, “32”, “>32”, hoặc “apple”, v.v…

Lưu ý:

• Mỗi ô trong sum_range chỉ được tính tổng nếu tất cả các điều kiện tương ứng với ô đó điều đúng. Nếu thỏa các điều kiện, nó sẽ bằng 1, còn không, thì nó sẽ bằng 0.
• Không giống như những các đối số range và criteria của hàm SUMIF, trong hàm SUMIFS, mỗi vùng criteria_range phải có cùng kích thước và hình dạng giống như sum_range.
• Có thể dùng các ký tự đại diện trong các điều kiện: Dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hoặc *).
• Khi điều kiện để đếm là nhữ ký tự, SUMIFS( ) không phận biệt chữ thường hay chữ hoa.

Ví dụ: Có bảng tính như sau:

•  Tính tổng số tiền của các tài khoản có lãi suất năm 2000 > 3% và lãi suất năm 2001 ³ 2% ?

• Tính tổng số tiền của các tài khoản có lãi suất năm 2002 là từ 1% đến 3% và lãi suất năm 2001 >1% ?

 

5. Hàm SUBTOTAL( )

Chức năng: Là hàm tính toán cho một nhóm con trong một danh sách hay bảng dữ liệu tùy theo phép tính mà bạn chọn lựa trong đối số thứ nhất.
Cú pháp: SUBTOTAL(function_num, ref1, ref2,…)

• Function_num: Các con số từ 1 đến 11 (hay 101 đến 111) qui định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.

Giá trị của đối số thứ nhất (function_num)		Hàm tính toán thực sự
Tính toán bao gồm cả các giá trị ẩn	Không tính toán các giá trị ẩn
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

• Ref1, ref2: Các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó.
• Trong Excel 2010, bạn có thể dùng đến 254 ref (với Excel 2003 trở về trước thì con số này chỉ là 29)

Ví dụ:
 

6. Thống kê với lệnh SUBTOTAL

Công dụng:

• Tính năng Data\ Outline\ Subtotal từ thanh Ribbon giúp chúng ta chèn hàm Subtotal vào bảng số liệu một cách tự động nhằm thực hiện các phép tính có thể cho các nhóm dữ liệu khác nhau.

Thao tác:

• Để thực hiện chức năng này trước tiên tập số liệu phải được sắp xếp theo thứ tự tại các cột dữ liệu (Field) mà ta cần nhóm lại khi tính toán.

Vd: Cho bảng như sau:

• Khối bảng tính
• Chọn Data\ Outline\ Subtotal.

• Thiết lập các tùy chọn trong hộp thoại Subtotal:

•  At Each Change In: Chọn tên cột của vùng số liệu tính toán.
• Use Function: Chọn hàm để tính từ danh sách 11 hàm.
• Add Subtotal To: Liệt kê tất cả các trường của vùng số liệu tính toán. Đánh dấu kiểm kê bên tên trường để chọn các trường cần thực hiện tính toán.
• Replace Current Subtotals: Nếu được chọn, excel sẽ xóa bỏ Subtotal trước đó và thay thế bằng lệnh subtotal hiện hành.
• Page Break between Groups: Nếu được chọn, excel sẽ chèn các dấu ngắt trang sau mỗi nhóm tính subtotal.
• Summary below Data: Nếu được chọn, excel sẽ đặt các kết quả tính subtotal bên dưới mỗi nhóm số liệu (mặc định), ngược lại excel sẽ đặt kết quả tính subtotal bên trên mỗi nhóm số liệu.
• Remove All: Nếu chọn nút này thì Excel sẽ xóa bỏ tất cả các lệnh subtotal trong vùng số liệu.

 

7. Thống kê với lệnh CONSOLIDATE

Thao tác: Ví dụ tính tổng số lượng và thành tiền của từng loại tên hàng.

•  Sao chép tiều đề các cột cần tính toán (Ten vat tu, So luong, Thanh tien) đến vùng cần thống kê dữ liệu.

 

• Quét khối tiêu đề vùng cần thống kê.
• Vào Data\ Data Tools\ Consolidate.

•  Mục Function: Hàm cần thống kê [Sum].
• Muc Reference: Địa chỉ các vùng cần tính toán. Nhấn nút Add.
• Top Row: Nhằm cho Excel hiều dòng đầu tiên là tiêu đề cột.
• Left Column: Nhằm cho Excel hiểu cột đầu tiên là tiêu đề của hàng.
• Create links to source data: Liên kết với dữ liệu nguồn.
• Nếu dữ liệu thông kê trong cùng sheet hiện hành thì khi thay đổi dữ liệu ngồn Excel không tự động cập nhật (phải làm lại).
• Nếu dữ liệu thông kê trong sheet khác thì khi thay đổi dữ liệu nguồn thì excel tự động cập nhật.

Đây là kết quả. Lưu ý: Do bảng kết quả thông kê lấy field Ten Vat Tu làm tiêu chí chính nên khi quét khối dữ liệu của vùng cần tính toán ta bất đầu quét từ ô này.
 

8. Thống kê, phân tích báo cáo với lệnh PIVOTTABLE

1. Công dụng: Công cụ PivotTable rất tiện lợi cho việc tổng hợp, tóm tắc và phân tích dữ liệu từ các danh sách với nhiều góc độ và nhiều cấp khác nhau.
2. Thao tác:

• Để trỏ trong vùng dữ liệu cần tạo rồi vào Insert\ Tables\ PivotTable

 

• PivotTable: Thống kê tổng hợp.
• PivotChart: Thống kê thao dạng biểu đồ.

 

• Select a table or range: Vùng chứa dữ liệu cần thống kê.
• Use an external data source: Sử dụng nguồn dữ liệu khác (từ tập tin Excel khác hoặc tử Access,…)
• Choose where you want…: Xác định vùng đặt báo cáo.

•  Kéo thả trường phân tích vào Drag field between areas below:

 

3. Hiệu chỉnh PivotTable

• Gỡ bỏ Field: Chọn Field muốn bỏ -> Click hộp thoại -> Remove Field

 

• Chọn hàm thống kê cho Field Value: Chọn Field trong vùng value -> click vào hộp thoại Value Field Setting.
• Sau đó chọn lại hàm muốn thông kê.

 

4. Tạo PivotChar

• Để con trỏ trong vùng dữ liệu cần tạo.
• Chọn Insert\ Tables\ PivotChart.
• Xác định vùng dữ liệu thực hiện báo cáo kéo thả vào vùng cần thể hiện.