E25: Các hàm thống kê cấu trúc CSDL trên Excel

  • Post author:
  • Post category:Excel
  • Post comments:0 Comments
  • Reading time:5 mins read

Các hàm thống kê cấu trúc CSDL trên Excel bao gồm DSUM, DAVERAGE, DCOUNT, DMAX và DMIN. Những hàm này mục đích tương tự như các hàm thông thường SUM, AVERAGE, COUNT, MAX, MIN, tuy nhiên sự khác biệt của các hàm tiền tố D này cho phép thêm vào các tiêu chí lọc tại các rows của vùng dữ liệu tính toán.

Cú pháp ví dụ như sau:

DSUM(database, field, criterial) với các tham số

  • Database: là vùng dữ liệu được chọn hoặc đặt tên. Trong đó hàng đầu tiên là tiêu đề cho mỗi cột dự liệu
  • Field: là cột chứa dữ liệu cần tính toán. Field có thể định dạng dưới dạng “column label” hoặc chỉ số của cột trong vùng dữ liệu
  • Criterial: là vùng dữ liệu để thực hiện lọc. Trong đó hàng đầu tiên cần chứa 1 hoặc nhiều tiêu đề của cột. Trong trường hợp có nhiều cột, tương đương với nhiều tiêu chí thì những giá trị dữ liệu trên cùng 1 hàng sẽ thực hiện theo phép toán AND và khác hàng sẽ thực hiện theo phép toán OR

Ví dụ:

Xét bài toán với dữ liệu bán hàng của một cửa hàng bán đồ mỹ phẩm chứa các thông tin như hình dưới:

  • Name: tên người bán
  • Date: ngày bán
  • Product: sản phẩm được bán
  • Units: số sản phẩm bán được
  • Dollars: Số tiền bán được
  • Location: vị trí cửa hàng bán

Bằng việc sử dụng các hàm Dfunction ta có thể trả lời các câu hỏi sau một cách dễ dàng

Trước hết ta đặt tên cho các vùng dữ liệu như sau:

  • Data: vùng dữ liệu bán hàng từ C4:H1895
  • Date: cột dữ liệu ngày bán D5:D1895
  • Dollars: cột dữ liệu số tiền bán được G5:G1895
  • Location: cột dữ liệu vị trí cửa hàng bán H5:H1895
  • Name: cột dữ liệu tên người bán C5:C1895
  • Product: cột dữ liệu sản phẩm bán được E5:E1895
  • Trans_Numbers: cột thứ tự B5:B1895
  • Units: cột số sản phẩm bán được F5:F1895

Doanh thu của Jen cho sản phẩm lip gloss$5,461.61 được tính theo công thức DSUM với bộ lọc 2 tiêu chí với dữ liệu lọc cho phép AND từ K4:L5

Trung số lipstickJen bán được trung bình ở vùng east42.25 được tính theo công thức DAVERAGE với bộ lọc 3 tiêu chí với dữ liệu lọc trên 1 hàng cho phép AND K7:M8

Doanh số của Emilee hoặc của vùng east76,156.48 được tính trong công thức DSUM với bộ lọc 2 tiêu chí và dữ liệu lọc nằm trên 2 dòng khác nhau cho phép OR K10:L12

Doanh số bán lipstick của Colleen hoặc Zaret bán vùng east1,073.20 được tính theo công thức DSUM với bộ lọc 3 tiêu chí và dữ liệu lọc nằm trên 2 dòng khác nhau cho phép OR K13:M15

Tổng số giao dịch lipstick không thuộc vùng east164 được tính trong công thức DCOUNT

Tổng số doanh thu sản phẩm lipstickJen bán được trong năm 2014 được tính trong công thức DSUM với 4 tiêu chí lọc K18:N19

Ngoài ra sử dụng công cụ Data Table 2 biến ta có thể thiết lập bảng tổng hợp doanh thu 2 chiều số liệu người bán và sản phẩm như sau. Trong đó

  • Vùng dữ liệu Data Table được xác định từ M25:R33
  • Giá trị ô đầu tiên M25 được tính theo công thức =DSUM(data,5,T25:U26)
  • Thiết lập Data Table với 2 biến đầu vào
    • Row input cell: U$26 chứa biến sản phẩm
    • Column input cell: T$26 chứa biến người bán

Leave a Reply