Giúp em cách tổng hợp dữ liệu từ sheet 1 sang sheet 2 (2 người xem)

[tom cun]

Các bác hướng dẫn cho em tình huống trong file kèm theo này với ạ. Em cần phải tổng hợp số khách hàng từ nguồn Tel, Onl, DM ,RM (mỗi một ô ghi Tel, Onl... tương ứng với 1 khách hàng) của mỗi một nhân viên bán hàng tính đến thời điểm nhập số liệu (trong file là ngày 21/4/2011). Em mới đi làm còn ít kinh nghiệm, em nghĩ mãi mà ko ra. Bác nào biết giúp em với. Em cảm ơn rất nhiều ạ!

 

[suoixanh_nt]

Các bác hướng dẫn cho em tình huống trong file kèm theo này với ạ. Em cần phải tổng hợp số khách hàng từ nguồn Tel, Onl, DM ,RM (mỗi một ô ghi Tel, Onl... tương ứng với 1 khách hàng) của mỗi một nhân viên bán hàng tính đến thời điểm nhập số liệu (trong file là ngày 21/4/2011). Em mới đi làm còn ít kinh nghiệm, em nghĩ mãi mà ko ra. Bác nào biết giúp em với. Em cảm ơn rất nhiều ạ!

Mình không biết kết quả từ ô B5 --> E5 bạn cần tổng hợp là gì? Bạn có thể nêu rõ hơn?

 

[tom cun]

Em muốn tổng hợp đến ngày cập nhật thông tin mới nhất (trong bảng là ngày 21) thì Nv Hải đã có bao nhiêu khách hàng từ nguồn Tel, bao nhiêu khách từ nguồn Onl,... Tương tự với các NV bán hàng khác ạ.

 

[huuthang_bd]

Mình không biết kết quả từ ô B5 --> E5 bạn cần tổng hợp là gì? Bạn có thể nêu rõ hơn?

Chắc bạn tom cun muốn đếm số khách hàng của từng nhân viên theo từng nguốn khách hàng. Nếu đúng vậy thì bạn dùng công thức này cho ô B5

=SUMPRODUCT((OFFSET('Chi tiet'!$B$4,,,COUNTA('Chi tiet'!$A$4:$A$65536))=$A5)*(OFFSET('Chi tiet'!$C$4,,,COUNTA('Chi tiet'!$A$4:$A$65536))=B$4))

Copy công thức cho các ô còn lại.
Lưu ý:
1. Ở Sheet Chi tiet ô C6 nguồn khách hàng là DR nhưng trong bản tổng hợp không có mã này.
2. Ở Sheet Chi tiet ô B10 chữ Hải khác với các chữ Hải còn lại. Có thể do dùng Unicode tổ hợp.

 

[nghiaphuc]

Em muốn tổng hợp đến ngày cập nhật thông tin mới nhất (trong bảng là ngày 21) thì Nv Hải đã có bao nhiêu khách hàng từ nguồn Tel, bao nhiêu khách từ nguồn Onl,... Tương tự với các NV bán hàng khác ạ.

Bạn đặt 2 name:
TenNV=OFFSET('Chi tiet'!$B$4,,,COUNTA('Chi tiet'!$B:$B)-1,)
NguonKH=OFFSET(TenNV,,1,,)
Sau đó, sử dụng công thức sau cho ô B5 trên sheet Tong hop và fill cho toàn bảng:
=SUMPRODUCT((TenNV=$A5)*(NguonKH=B$4))
Nếu sử dụng Excel 2007, 2010 thì bạn có thể sử dụng công thức sau cho ô B5:
=COUNTIFS(TenNV,$A5,NguonKH,B$4)

Tất nhiên dữ liệu phải chuẩn hóa đấy nhé.

 

[tom cun]

Sao khi em lọc ra thì thấy NV Hải có 2 Tel, 1DM và 1 Onl nhỉ?
TenNV=OFFSET('Chi tiet'!$B$4,,,COUNTA('Chi tiet'!$B:$B)-1,)
NguonKH=OFFSET(TenNV,,1,,)
Sau đó, sử dụng công thức sau cho ô B5 trên sheet Tong hop và fill cho toàn bảng:
=SUMPRODUCT((TenNV=$A5)*(NguonKH=B$4))
Nếu sử dụng Excel 2007, 2010 thì bạn có thể sử dụng công thức sau cho ô B5:
=COUNTIFS(TenNV,$A5,NguonKH,B$4)

Tất nhiên dữ liệu phải chuẩn hóa đấy nhé.[/QUOTE]

 

[tom cun]

Chắc bạn tom cun muốn đếm số khách hàng của từng nhân viên theo từng nguốn khách hàng. Nếu đúng vậy thì bạn dùng công thức này cho ô B5

=SUMPRODUCT((OFFSET('Chi tiet'!$B$4,,,COUNTA('Chi tiet'!$A$4:$A$65536))=$A5)*(OFFSET('Chi tiet'!$C$4,,,COUNTA('Chi tiet'!$A$4:$A$65536))=B$4))

Copy công thức cho các ô còn lại.
Lưu ý:
1. Ở Sheet Chi tiet ô C6 nguồn khách hàng là DR nhưng trong bản tổng hợp không có mã này.
2. Ở Sheet Chi tiet ô B10 chữ Hải khác với các chữ Hải còn lại. Có thể do dùng Unicode tổ hợp.

Có công thức nào đơn giản hơn được không bác?
Em gõ nhầm DM thành DR đấy ạ ^^

 

[nghiaphuc]

Ý em là muốn đếm số khách hàng của nhân viên Hải theo từng nguồn khách hàng. bảng này ít dữ liệu nên nếu lọc ra thì thấy NV Hải có 2 Tel, 1DM và 1 Onl. Các bác giúp em đặt công thức để nếu những ngày hôm sau em nhập thêm dữ liệu vào sheet 1 thì sheet 2 tự động tính đúng được. Thank các bác ạ ^^

Ô B10 (tên Hải) có dư 1 dấu cách nên thống kê không đúng.
Trong file đính kèm của mình, bạn cứ thử nhập thêm dữ liệu mới rồi xem lại bảng thống kê xem có đúng không nào.

 

[tom cun]

Ô B10 (tên Hải) có dư 1 dấu cách nên thống kê không đúng.
Trong file đính kèm của mình, bạn cứ thử nhập thêm dữ liệu mới rồi xem lại bảng thống kê xem có đúng không nào.

Cảm ơn anh Phúc nhé! Em hiểu rồi ạ. Cho em hỏi thêm là đặt Name TenNV và NguonKH ở đâu để dùng được hàm COUNTIFS như trên ạ?

 

[nghiaphuc]

Cảm ơn anh Phúc nhé! Em hiểu rồi ạ. Cho em hỏi thêm là đặt Name TenNV và NguonKH ở đâu để dùng được hàm COUNTIFS như trên ạ?

Bạn cứ nhấn Ctrl+F3 sẽ thấy 2 name này.
Lưu ý là hàm COUNTIFS chỉ hoạt động trên Excel 2007, 2010 thôi. Nếu bạn dùng Excel 2003 thì phải dùng SUMPRODUCT đấy nhé.

 

[ngodany135]

Alt+I-->Name-->Define-->Hiện ra 1 bảng, bạn nhập tên TenNV=OFFSET('Chi tiet'!$B$4,,,COUNTA('Chi tiet'!$B:$B)-1,)
NguonKH=OFFSET(TenNV,,1,,). Sau đó bạn bạn Add và ok. Bước tiếp theo bạn làm giống như a nghiaphuc huống dẫn ở trên.

 

[tom cun]

Bạn cứ nhấn Ctrl+F3 sẽ thấy 2 name này.
Lưu ý là hàm COUNTIFS chỉ hoạt động trên Excel 2007, 2010 thôi. Nếu bạn dùng Excel 2003 thì phải dùng SUMPRODUCT đấy nhé.

Em thấy rồi ạ. Ở phần đặt name TenKH, sao lại có thêm -1 và một số chỗ sau dấu phẩy ko ghi số có phải là 0 thì ko ghi ko ạ?

 

[nghiaphuc]

Em thấy rồi ạ. Ở phần đặt name TenKH, sao lại có thêm -1 và một số chỗ sau dấu phẩy ko ghi số có phải là 0 thì ko ghi ko ạ?

Chiều nay đang gõ trả lời cho bài này, gần xong rồi thì tự nhiên cúp điện. Thế đấy, đôi khi muốn nhiệt tình cũng đâu có được...
Trước hết, bạn phải biết công dụng và cú pháp hàm OFFSET cái đã. Hàm này trả về một vùng (thường dùng làm tham số cho các hàm thống kê). Cú pháp như sau:
OFFSET(reference,rows,cols,[height],[width])
Trong đó:
+ reference là ô/vùng ban đầu (có thể hiểu như gốc tọa độ trong mặt phẳng tọa độ)
+ rows,cols là số hàng, cột bị dịch chuyển so với reference. Trong công thức trên sheet thì 2 tham số này bắt buộc có, còn trong define name thì không bắt buộc. Nếu để trống thì xem như là giá trị 0.
+ [height],[width] là kích thước (số hàng, số cột) của vùng kết quả. Nếu để trống thì vùng kết quả có kích thước bằng kích thước của reference.
Như vậy, trong name TenNV=OFFSET('Chi tiet'!$B$4,,,COUNTA('Chi tiet'!$B:$B)-1,)
+ 2 khoảng trống ở giữa 3 dấu phẩy liên tiếp tương ứng với số 0. Có nghĩa là vùng kết quả được bắt đầu từ ô B4.
+ COUNTA('Chi tiet'!$B:$B)-1 là số ô có dữ liệu trên cột B trừ đi 1 (-1 chính là trừ đi ô tiêu đề). Như vậy, vùng kết quả có số hàng bằng với số hàng có dữ liệu (trừ tiêu đề) trên cột B
+ Khoảng trống sau dấu phẩy cuối cùng cho thấy vùng kết quả có số cột cùng với số cột của ô B4, tức là 1 cột.

 

[tom cun]

Chiều nay đang gõ trả lời cho bài này, gần xong rồi thì tự nhiên cúp điện. Thế đấy, đôi khi muốn nhiệt tình cũng đâu có được...
Trước hết, bạn phải biết công dụng và cú pháp hàm OFFSET cái đã. Hàm này trả về một vùng (thường dùng làm tham số cho các hàm thống kê). Cú pháp như sau:
OFFSET(reference,rows,cols,[height],[width])
Trong đó:
+ reference là ô/vùng ban đầu (có thể hiểu như gốc tọa độ trong mặt phẳng tọa độ)
+ rows,cols là số hàng, cột bị dịch chuyển so với reference. Trong công thức trên sheet thì 2 tham số này bắt buộc có, còn trong define name thì không bắt buộc. Nếu để trống thì xem như là giá trị 0.
+ [height],[width] là kích thước (số hàng, số cột) của vùng kết quả. Nếu để trống thì vùng kết quả có kích thước bằng kích thước của reference.
Như vậy, trong name TenNV=OFFSET('Chi tiet'!$B$4,,,COUNTA('Chi tiet'!$B:$B)-1,)
+ 2 khoảng trống ở giữa 3 dấu phẩy liên tiếp tương ứng với số 0. Có nghĩa là vùng kết quả được bắt đầu từ ô B4.
+ COUNTA('Chi tiet'!$B:$B)-1 là số ô có dữ liệu trên cột B trừ đi 1 (-1 chính là trừ đi ô tiêu đề). Như vậy, vùng kết quả có số hàng bằng với số hàng có dữ liệu (trừ tiêu đề) trên cột B
+ Khoảng trống sau dấu phẩy cuối cùng cho thấy vùng kết quả có số cột cùng với số cột của ô B4, tức là 1 cột.

Em làm được rồi ạ. Cảm ơn các bác nhiều lắm ^^!