Tổng hợp các cách thống kê dữ liệu (1 người xem)

[haesangdoo]

Bài 3: TỔNG HỢP DỮ LIỆU ​

1. HÀM SUMIF(Cột chứa điều kiện,Điều kiện,Cột cộng)
Tính Tồng có điều kiện
2. HÀM COUNTIF(Cột chứa điều kiện,Điều kiện)
Đếm có điều kiện
* Lưu ý: Điều kiện 2 hàm trên chỉ nằm trên 1 cột
3. HÀM SUBTOTAL
Cú pháp: SUBTOTAL(Con số chỉ tên hàm, Vùng tham chiếu 1, Vùng tham chiếu 2, …)
- Công dụng Subtotal là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất.
- Con số chỉ tên hàm gồm danh sách sau:

[TABLE="class: MsoTableGrid"]
[TR]
[TD="width: 87"]

1​

[/TD]
[TD="width: 93"] AVERAGE
[/TD]
[TD="width: 245"] Tính trung bình cộng
[/TD]
[/TR]
[TR]
[TD="width: 87"]

2​

[/TD]
[TD="width: 93"] COUNT
[/TD]
[TD="width: 245"] Đếm ô có dữ liệu số
[/TD]
[/TR]
[TR]
[TD="width: 87"]

3​

[/TD]
[TD="width: 93"] COUNTA
[/TD]
[TD="width: 245"] Đếm ô có chứa dữ liệu
[/TD]
[/TR]
[TR]
[TD="width: 87"]

4​

[/TD]
[TD="width: 93"] MAX
[/TD]
[TD="width: 245"] Tính trị lớn nhất
[/TD]
[/TR]
[TR]
[TD="width: 87"]

5​

[/TD]
[TD="width: 93"] MIN
[/TD]
[TD="width: 245"] Tính trị nhỏ nhất
[/TD]
[/TR]
[TR]
[TD="width: 87"]

6​

[/TD]
[TD="width: 93"] PRODUCT
[/TD]
[TD="width: 245"] Tính nhân
[/TD]
[/TR]
[TR]
[TD="width: 87"]

7​

[/TD]
[TD="width: 93"] STDEV
[/TD]
[TD="width: 245"] Tính độ lệch chuẩn
[/TD]
[/TR]
[TR]
[TD="width: 87"]

8​

[/TD]
[TD="width: 93"] STDEVP
[/TD]
[TD="width: 245"] Tính độ lệch chuẩn
[/TD]
[/TR]
[TR]
[TD="width: 87"]

9​

[/TD]
[TD="width: 93"] SUM
[/TD]
[TD="width: 245"] Tính tổng
[/TD]
[/TR]
[TR]
[TD="width: 87"]

10​

[/TD]
[TD="width: 93"] VAR
[/TD]
[TD="width: 245"] Tính phương sai
[/TD]
[/TR]
[TR]
[TD="width: 87"]

11​

[/TD]
[TD="width: 93"] VARP
[/TD]
[TD="width: 245"] Tính phương sai
[/TD]
[/TR]
[/TABLE]
​

- Vùng tham chiếu 1, Vùng tham chiếu 2, …( Tối đa 30 tham chiếu) là địa chỉ mà ta muốn thực hiện phép tính
- Hàm chỉ sử dụng cho dòng, không dùng cho cột
4. Thống kê với lệnh DATA/SUBTOTAL
- Tính năng Data /Subtotal từ thanh thực đơn 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ể tính cho các nhóm dữ liệu khác nhau.
- Thao tác
[FONT=&quot]o [/FONT]Để sử dụng 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.
[FONT=&quot]o [/FONT]Menu Data/Subtotal. Thiết lập các tuỳ chọn trong hộp thoại Subtotal:

5. Thống kê với lệnh DATA/CONSOLIDATE
- Giới thiệu: Consolidat là lệnh dùng để kết hợp thông tin từ nhiều nguồn khác nhau, với các phép tính khác nhau ( cộng, đếm, lớn nhất, nhỏ nhất, trung bình ...... ). Đối với mảng hai chiều, để truy xuất tính tổng, hoặc count, hoặc ... thì không có cách gì hay bằng consolidate. Ví dụ bạn có nhiều bảng tính với nhiều cột và nhiều dòng thì làm sao kết hợp cho ra báo cáo tổng hợp nhất tất cả các bảng tính đó lại thành một bảng tính tổng hợp. Công cụ Consolidate hỗ trợ bạn việc này.
- 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

[FONT=&quot]o [/FONT]Sao chép tiêu đề các cột cần tính toán (Ten hang, So luong, Thanh tien) đến vùng cần thống kê dữ liệu

[TABLE="class: MsoNormalTable, width: 337"]
[TR]
[TD="width: 111"]

Ten hang​

[/TD]
[TD="width: 105"]

So luong​

[/TD]
[TD="width: 121"]

Thanh tien​

[/TD]
[/TR]
[/TABLE]
​

[FONT=&quot]o [/FONT]Quét khối tiêu đề vùng cần thống kê (càng nhiều dòng càng tốt)
[FONT=&quot]o [/FONT]Vào Menu/Data/Consolidate

​

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

[TABLE="class: MsoNormalTable, width: 377"]
[TR]
[TD="width: 149"]

Ten hang​

[/TD]
[TD="width: 104"]

So luong​

[/TD]
[TD="width: 124"]

Don gia​

[/TD]
[/TR]
[TR]
[TD="width: 149"] Quần áo TThao
[/TD]
[TD="width: 104"]

3071​

[/TD]
[TD="width: 124"]

688 USD​

[/TD]
[/TR]
[TR]
[TD="width: 149"] Quần áo trẻ em
[/TD]
[TD="width: 104"]

4582​

[/TD]
[TD="width: 124"]

180 USD​

[/TD]
[/TR]
[TR]
[TD="width: 149"] Bảo hộ LD
[/TD]
[TD="width: 104"]

1132​

[/TD]
[TD="width: 124"]

200 USD​

[/TD]
[/TR]
[/TABLE]
​

Lưu ý: Do bảng kết quả thống kê lấy field Ten hang 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 không quét field Ngay nhap hang
6. Thống kê, phân tích báo cáo với lệnh DATA/PIVOTTABLE
a. Giới thiệu: Công cụ PivotTable rất tiện lợi trong việc tổng hợp, tóm tắt và phân tích dữ liệu từ các danh sách.
b. Thao tác tạo Pivottable từ dữ liệu bảng trong excel
- Để trỏ trong dữ liệu cần tạo vào Data/PivotTable and PivotChart Report.
- Chọn Microsoft Opfice Excel list or database

- Nhấn Next Chọn vùng Dữ liệu

​

- Nhấn Next chọn nơi chứa

- Nhấn Finish để kết thúc

- Kéo thả trường phân tích cao nhất vào "DROP PAGE FIELDS HERE" (Trường Tháng)
- Kéo thả 1 hoặc 2 trường thuộc cấp phân tích thấp hơn vào " DROP ROW FIELDS HERE” (Trường Tên Nhân Viên)
- Kéo thả 1 hoặc 2 trường (nên chỉ 1 trường bảng tính không bị rối khó xem) vào " DROP ROW FIELDS HERE"
- Kéo thả trường dữ liệu cần phân tích vào " DROP DATA ITEMS HERE" (Trường Doanh Thu)

​

- Thanh công cụ Pvot Table

​

 

[haesangdoo]

1. Thống kê với lệnh DATA/TABLE
a. Công dụng: Tính giá trị cho một vùng có công thức tính toán giống nhau
b. Table 1 biến: Các ô trong vùng table sẽ được tính theo công thức tại dòng đầu tiên của table hay cột đầu tiên của table
Ví dụ: Thống kê Thành tiền và Số Lượng theo Tên hàng như bảng sau

​

Bước 1: Lập công thức chung tại 2 ô G4 và H4. Ở đây ta dùng hàm Database để tính toán nên phải lập vùng tiêu chuẩn chứa điều kiện đó là 2 ô F14:F15. Công thức tại ô Số Lượng là =DSUM(A325,C3,F14:F15)
Công thức tại ô Thành Tiền là =DSUM(A325,D3,F14:F15)
Bước 2: Quét khối vùng table F4:H8, menu Data/Table. Do biến để theo cột nên tại dòng Column input cell ta chọn ô $F$15. Click OK. Lệnh sẽ làm công việc sau: Lấy dữ liệu tại cột F để vào ô biến F15, được là gì sẽ tính toán theo công thức tại 2 ô G4, H4
c. Table 2 biến: Các ô trong vùng table sẽ được tính theo công thức tại ô góc trên bên trái của table
Ví dụ: Thống kê Thành tiền theo Mã Vật Tư và Mã Kho theo bảng sau

Bước 1: Lập công thức chung tại ô A20. Ở đây ta dùng hàm Database để tính toán nên phải lập vùng tiêu chuẩn chứa điều kiện đó là 2 ô F21:G22. Công thức tại ô A20 là = DSUM(A1:F18,F1,F21:G22)
Bước 2: Quét khối vùng table A1:F18, menu Data/Table.
Do Ma Vat Tu là biến theo cột nên tại Colum input cell chọn ô $F$22.
Do Ma Kho là biến theo dòng nên tại Row input cell chọn ô $G$22.
Click OK. Lệnh sẽ làm công việc sau: Lấy dữ liệu tại Cột A bỏ vào ô F22, lấy dữ liệu tại Dòng 19 bỏ vào ô G22 được là gì sẽ tính toán theo công thức tại ô A20

 

[haesangdoo]

TỔNG HỢP DỮ LIỆU 1. HÀM SUMIF(Cột chứa điều kiện,Điều kiện,Cột cộng) Tính Tồng có đi

TỔNG HỢP DỮ LIỆU ​

1. HÀM SUMIF(Cột chứa điều kiện,Điều kiện,Cột cộng)
Tính Tồng có điều kiện
2. HÀM COUNTIF(Cột chứa điều kiện,Điều kiện)
Đếm có điều kiện
* Lưu ý: Điều kiện 2 hàm trên chỉ nằm trên 1 cột
3. HÀM SUBTOTAL

 

[mayvo1908]

Hi haesangdoo ,
Bài viết này rất hữu ích với mình nhưng mà các link hình ảnh minh họa bị mất hết rồi. Bạn có thể up hình được không?

 

[SA_DQ]

Không rõ tác giả có đọc lại bài không, chứ các bảng biểu hư hết, có đọc được đâu?!

o Nếu dữ liệu thống kê trong sheet khác thì khi thay đổi dữ liệu nguồn excel tự động cập nhật

[TR]
[TD="width: 149"]
Ten hang
[/TD]
[TD="width: 104"]
So luong
[/TD]
[TD="width: 124"]
Don gia
[/TD]
[/TR]
[TR]
[TD="width: 149"] Quần áo TThao
[/TD]
[TD="width: 104"]
3071
[/TD]
[TD="width: 124"]
688 USD
[/TD]
[/TR]
[TR]
[TD="width: 149"] Quần áo trẻ em
[/TD]
[TD="width: 104"]
4582
[/TD]
[TD="width: 124"]
180 USD
[/TD]
[/TR]
[TR]
[TD="width: 149"] Bảo hộ LD
[/TD]
[TD="width: 104"]
1132
[/TD]
[TD="width: 124"]
200 USD
[/TD]
[/TR]

Lưu ý: Do bảng kết quả thống kê lấy field Ten hang 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 không quét field Ngay nhap hang

& đây là kết quả sau khi sửa:

Ten hang | So luong | Don gia
Quần áo TThao|3071|688 USD
Quần áo trẻ em |4582|180 USD
Bảo hộ LD|1132|200 USD