đào tạo kế toán cấp tốc




truy Lượt Xem:10090
GIẢM 50% LỚP HỌC KẾ TOÁN THỰC HÀNH, TỔNG HỢP xem ngay
NHẬN GIA SƯ KẾ TOÁN THEO YÊU CẦU MỌI TRÌNH ĐỘ xem ngay
NHẬN LÀM DỊCH VỤ KẾ TOÁN TRỌN GÓI, LÀM BCTC xem ngay

Đếm các giá trị văn bản duy nhất trong một dải

Công thức chung 
 
= SUMPRODUCT ( - ( FREQUENCY ( MATCH ( dữ liệu , dữ liệu , 0 ), ROW ( dữ liệu ) - ROW ( data.firstcell ) + 1 ) > 0 ))
 
Giải trình
 
Nếu bạn cần tính các giá trị văn bản duy nhất trong một dải, bạn có thể sử dụng một công thức sử dụng một số hàm: FREQUENCY, MATCH, ROW và SUMPRODUCT.
 
Cũng có thể sử dụng COUNTIF, như được giải thích bên dưới.
 
Giả sử bạn có một danh sách tên nhân viên cùng với số giờ làm việc "Dự án X", và bạn muốn biết có bao nhiêu nhân viên làm việc trong dự án đó. Nhìn vào dữ liệu, bạn có thể thấy rằng cùng một nhân viên tên xuất hiện nhiều hơn một lần, do đó, những gì bạn muốn là một số các tên duy nhất.
 
dem-cac-gia-tri-van-ban-duy-nhat-trong-mot-dai
 
Tên nhân viên xuất hiện trong khoảng B3: B12. Để có được một số tên duy nhất, bạn có thể sử dụng công thức sau:
 
= SUMPRODUCT ( - ( FREQUENCY ( MATCH ( B3: B12 , B3: B12 , 0 ), ROW ( B3: B12 ) - ROW ( B3 ) + 1 ) > 0 ))
 
Công thức này hoạt động như thế nào
 
Công thức này phức tạp hơn một công thức tương tự sử dụng FREQUENCY để tính các giá trị số duy nhất vì FREQUENCY không hoạt động với các giá trị không phải là số. Do đó, phần lớn công thức chỉ đơn giản chuyển dữ liệu không phải số thành dữ liệu số mà FREQUENCY có thể xử lý.
 
Làm việc từ bên trong, chức năng MATCH được sử dụng để có được vị trí của mỗi mục xuất hiện trong dữ liệu. Bởi vì MATCH chỉ trả về vị trí của các giá trị "phù hợp đầu tiên" xuất hiện nhiều hơn một lần trong dữ liệu trả về cùng một số.
 
Bởi vì MATCH nhận được và mảng các giá trị cho đối số match_value, nó trả về một mảng các vị trí. Chúng được đưa đến FREQUENCY trong đối số mảng dữ liệu.
 
{ 1 ; 1 ; 1 ; 4 ; 4 ; 6 ; 6 ; 6 ; 9 ; 9 }
 
Đối số mảng bins được xây dựng từ phần này của công thức:
 
ROW ( B3: B12 ) - ROW ( B3 ) + 1
 
sử dụng số hàng của mỗi mục trong dữ liệu và số hàng của mục đầu tiên trong dữ liệu để xây dựng một mảng tuần tự thẳng, như sau: 
 
{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
 
Hàm FREQUENCY trả về một mảng các giá trị tương ứng với "thùng rác". Trong trường hợp này, chúng tôi cung cấp mảng trả về bởi MATCH cho mảng dữ liệu và mảng được trả về bởi mã ROW ở trên là mảng thùng.
 
Kết quả là FREQUENCY trả về một mảng các giá trị cho biết giá trị mà mỗi giá trị trong mảng dữ liệu xuất hiện. FREQUENCY có một tính năng đặc biệt tự động trả về số không cho bất kỳ số nào xuất hiện nhiều lần trong mảng dữ liệu, vì vậy mảng kết quả sẽ như sau:
 
{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}
 
Tiếp theo, mỗi giá trị này được chuyển thành TRUE hoặc FALSE bởi cấu trúc> 0, và sau đó là 1 hoặc không với cặp đôi (hai dấu gạch ngang). Điều này được thực hiện vì SUMPRODUCT cần các giá trị số, nó không thể làm việc trực tiếp với văn bản hoặc giá trị logic.
 
Mời bạn xem thêm:
 
 
 
 
Bên trong hàm SUMPRODUCT, mảng cuối cùng sẽ như sau:
 
{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}
 
Cuối cùng, SUMPRODUCT chỉ cần thêm các giá trị này lên và trả về tổng số, trong trường hợp này là 4.
 
Xử lý ô trống trong dải
 
Nếu bất kỳ ô nào trong dải ô trống, và bạn muốn sử dụng FREQUENCY thay vì COUNTIF, bạn sẽ cần sử dụng một công thức mảng phức tạp hơn bao gồm IF:
 
{ = SUM ( IF ( TẦN ( NẾU ( dữ liệu <> "" ,  Đội hình thi đấu ( dữ liệu , dữ liệu , 0 )), ROW ( dữ liệu ) - ROW ( data.firstcell ) + 1 ), 1 )) }
 
Lưu ý: vì phần kiểm tra logic của câu lệnh IF chứa một mảng, công thức trở thành một công thức mảng đòi hỏi phải kiểm soát-Shift-Enter. Đây là lý do tại sao SUMPRODUCT đã được thay thế bằng SUM. Ví dụ ở đây sử dụng dữ liệu dải có tên cho B3: B12.
 
Làm việc bên trong, lý do IF là bắt buộc là vì MATCH sẽ trả về # N / A nếu giá trị khớp lệnh chứa các giá trị rỗng. Bằng cách kiểm tra các giá trị trống với dữ liệu <> "", và bao gồm cả MATCH là giá trị nếu đúng, mảng kết quả sẽ chứa các số kết hợp với FALSE:
 
{1; 1; FALSE; 4; 4; 6; 6; FALSE; 9; 9}
 
được cung cấp cho FREQUENCY làm mảng dữ liệu. FREQUENCY sẽ trả lại một mảng như sau:
 
{2; 0; 0; 2; 0; 2; 0; 0; 2; 0; 0}
 
Các phần tử trong mảng này được chuyển thành 1 hoặc FALSE với câu lệnh IF cuối cùng (bên ngoài). Kết quả trông như sau:
 
{1; FALSE; FALSE; 1; FALSE; 1; FALSE; FALSE; 1; FALSE; FALSE}
 
SUM sau đó cộng thêm số 1 và trả về 4.
 
Công thức này là từ cuốn sách tuyệt vời của Mike Givin về các công thức mảng, Control-Shift-Enter.
 
Sử dụng COUNTIF thay vì FREQUENCY để đếm các giá trị duy nhất
 
Một cách khác để đếm các giá trị số duy nhất là sử dụng COUNTIF thay vì FREQUENCY . Đây là một công thức đơn giản hơn nhiều nhưng hãy cẩn thận khi sử dụng COUNTIF trên các bộ dữ liệu lớn hơn để đếm các giá trị duy nhất có thể gây ra các vấn đề về hiệu suất. Công thức dựa trên FREQUENCY, trong khi phức tạp hơn, tính toán nhanh hơn nhiều.

Tags: Chua co du lieu
Bình luận

Bình luận

Các bài viết mới

Các tin cũ hơn





HÃY LIKE PAGE HỮU ÍCH

HÃY LIKE PAGE HỮU ÍCH