📓Báo Cáo Thu Nhập

Nếu bạn đi được đến đây thì đó là một điều rất thú vị. Vì bạn đang ở một nơi mà bạn có thể tự tạo ra các công cụ quản lý doanh nghiệp, các báo cáo tài chính kế toán mà không cần phải phụ thuộc vào các nhà cung cấp phần mềm.

Để tạo một báo cáo, bạn cần thực hiện ba bước:

• Bước 1: Tạo ra các bộ nhu liệu thu thập các dữ liệu cho báo cáo.

• Bước 2: Chọn một mẫu báo cáo mong muốn và gắn nhu liệu trên vào báo cáo.

• Bước 3: Trích xuất các dữ liệu cần thiết từ nhu liệu vào bảng trình bày báo cáo và chỉnh trang báo cáo theo phong cách của bạn.

Trong phần này và các phần tiếp theo, chúng ta sẽ tạo một báo cáo thu nhập của các cộng tác viên trông giống như sau:

Báo cáo thu nhập

Hãy tưởng tượng chúng ta có tất cả các giao dịch quyên góp tiền cho các chương trình gây quỹ được tập hợp trong một bảng tính Excel. Để tính toán tổng thu nhập của cộng tác viên trên mỗi chiến dịch, chúng ta sẽ lọc và nhóm các giao dịch này theo các chương trình. Sau đó, tính tổng số tiền theo từng nhóm và nhân với tỷ lệ hoa hồng cho cộng tác viên của chương trình. Đó sẽ là tổng thu nhập của cộng tác viên.

Nghe có vẻ là một kế hoạch khả thi phải không? Hãy thực hiện nó.

Chuẩn Bị Nhu Liệu

Chúng ta sẽ sử dụng lại tập dữ liệu ONEIRO_AF_TX đã tạo trong phần trước cho báo cáo này.

Thu Thập Dữ Liệu

Đi đến Lập Báo Cáo > Tổng Hợp và nhấp vào biểu tượng dấu cộng để thêm một báo cáo mới. Nhập mã báo cáo là ONEIRO_AF_INC. Sau đó, chọn nguồn dữ liệu là ONEIRO_AF_TX và lưu lại.

Tạo báo cáo thu nhập - ONEIRO_AF_INC

Chúng ta cũng muốn báo cáo thu nhập cho những khoảng thời gian nhất định, chẳng hạn như trong năm nay hoặc 3 quý trước. Để làm điều đó, chúng ta sẽ thiết lập một bộ lọc để chỉ thu thập các giao dịch trong khoảng thời gian đó.

Để lấy tổng số tiền quyên góp được theo các chương trình gây quỹ, bạn có thể làm như sau:

1. Kéo cột số tiền (amount) bên tay trái vào lưới kết xuất bên tay phải.

2. Chọn hàm tính gộp: TỔNG

3. Kéo cột địa chỉ ví của chương trình gây quỹ (contract_address) vào lưới kết xuất.

4. Kéo cốt địa chỉ ví của cộng tác viên (address) vào lưới bộ lọc phía trên và tạm thời nhập giá trị 0x78a02bb94b324bd2d671ba32f644530b55a0271e vào ô trong cột giá trị. Bạn có thể chọn toán tử lọc cho nó là Ví Tiền Số nếu muốn tự động gán giá trị của địa chỉ của ví đã dùng để đăng nhập.

5. Kéo cột ngày giao dịch (transaction_date) bên dưới nhu liệu ONEIRO_DT_TX vào lưới bộ lọc phía trên. Chọn giá trị cho nó từ 2023-07-01 đến 2023-12-31.

Tập hợp dữ liệu báo cáo - ONEIRO_AF_INC

Khi bạn chạy báo cáo, bộ dữ liệu sẽ trông giống như sau:

Nhu liệu bên dưới báo cáo ONEIRO_AF_INC

Trình Bày

Chúng tôi khuyên bạn nên sử dụng Microsoft Excel nếu có thể, vì Google Sheets có thể không luôn hoạt động đúng cách với các tệp Excel.

Nhấp vào biểu tượng Tải Mẫu để tải xuống mẫu Excel cho báo cáo này. Đi đến bảng <#Config>. Kiểm tra ô A10 và B10.

Lưu ý:

• Ô A10 xác định tên bộ nhu liệu bên dưới của báo cáo, mặc định là Report. Bạn có thể đổi tên nếu muốn.

• Ô B10 phải là mã báo cáo, mà trong ví dụ của chúng ta là ONEIRO_AF_INC. Điều này nói cho mẫu báo cáo biết rằng nó cần nạp cho bộ nhu liệu được xác định trong ô A10 với tất cả dữ liệu từ bộ nhu liệu bên dưới của nó. KHÔNG thay đổi giá trị của B10.

  

  Khai báo bộ nhu liệu bên dưới cho báo cáo

Xóa bảng REPORT_NAME và thay thế bằng mẫu mới này. Đảm bảo rằng mẫu này là trang đầu tiên trong bảng tính Excel.

Bảng trình bày - báo cáo thu nhập

Để thuận tiện cho bạn, mẫu báo cáo này có thể được tải xuống từ đây:

15KB

Affiliate Income Presentation - VN.xlsx

Cho đến nay, trong mẫu báo cáo có hai tập dữ liệu:

• Bộ nhu liệu Filters: lưu trữ giá trị người dùng nhập vào bộ lọc.

• Bộ nhu liệu Report: lưu trữ các kết xuất dữ liệu được chọn như liệt kê trong lưới đầu ra. Dữ liệu là các giao dịch quyên tiền đã được lọc.

Bây giờ, hãy trích xuất các giá trị này.

Trước tiên, hãy lấy ra các giá trị bộ lọc do người dùng nhập vào trong bộ lọc khi chạy báo cáo bằng cách dùng thẻ hàm <#Lookup> trong bộ nhu liệu Filters. Khai báo các thẻ tên Affiliate (cộng tác viên), FromDate (từ ngày), ToDate (đến ngày).

M12: Affiliate
N12: <#Lookup(Filters;Name;address;ValueFrom)>

M13: FromDate
N13: <#Lookup(Filters;Name;transaction_date;ValueFrom)>
M14: ToDate
N14: <#Lookup(Filters;Name;transaction_date;ValueFrom)>

Để tính tổng số tiền gây quỹ được, chúng ta sẽ dùng thẻ hàm <#Aggregate>, như sau:

M16: TotalRaisedFund
N16: <#Aggregate(SUM;Report.amount)>

Giải thích:

1. Ô M16 xác định một thẻ tên TotalRaisedFund.

2. Ô N16 là định nghĩa của thẻ tên này: Nó dùng thẻ hàm <#Aggregate(...)> để duyệt qua các hàng của bộ nhu liệu và áp dụng hàm tổng hợp, trong trường hợp này là hàm TỔNG (SUM), cho giá trị trong cột số tiền (amount) của bộ nhu liệu Report như được cung cấp trong tham số Report.amount, và trả về tổng số tiền của tất cả các khoản này. Điều này cũng giống như cách hoạt động của hàm Aggregate trong Excel.

Nhắc lại: Thẻ hàm <#Aggregate> tổng hợp một tập dữ liệu và trả về một giá trị duy nhất cho tất cả các bản ghi của nó. Bạn có thể sử dụng thẻ này để tìm ví dụ tổng số tiền trên một cột trong một tập dữ liệu.

Cú pháp: <#Aggregate(hàm tổng hợp; tên bộ nhu liệu và cột)> <#aggregate(hàm tổng hợp; bộ nhu liệu; biểu thức tổng hợp; bộ lọc)>

Tham số:

• hàm tổng hợp: Có thể là SUM để cộng các giá trị, COUNT để đếm các giá trị, AVG để tìm giá trị trung bình, MAX để tìm giá trị lớn nhất và MIN để tìm giá trị nhỏ nhất.

• tên nhu liệu (và cột): Tên của tập dữ liệu mà chúng ta muốn tổng hợp các giá trị. Nếu có "biểu thức tổng hợp", thì bạn không cần phải bao gồm tên cột, vì các cột cần tổng hợp sẽ được lấy từ biểu thức. Nếu không hiện diện, bạn cần phải bao gồm cột mà bạn muốn tổng hợp.

• biểu thức tổng hợp: Tham số này là tùy chọn. Một biểu thức sẽ được áp dụng cho mỗi bản ghi trong tập dữ liệu. (bất kỳ hàm Excel nào cũng hợp lệ ở đây, và bạn có thể sử dụng bất kỳ kết hợp nào của các hàm Excel). Các giá trị Null sẽ được bỏ qua, nhưng vẫn được tính vào tổng số bản ghi khi tính trung bình.

• bộ lọc: Tham số này là tùy chọn. Nếu có, nó nên là một biểu thức trả về true hoặc false.

Hãy áp dụng các giá trị này vào bảng trình bày của báo cáo. Đi đến các ô sau và nhập các thẻ này:

• Ô C2: Từ: <#FromDate> - <#FromDate>

• Ô D5: <#Affiliate>

• Ô D6: <#TotalRaisedFund>

Chạy báo cáo. Nó sẽ trông giống như vầy:

Chạy báo cáo thu nhập cộng tác viên - bước 1

Để thuận tiện, bạn có thể tài mẫu báo cáo đã hoàn thiện về tại đầy:

34KB

Affiliate Income Stage 1 - VN.xlsx

Tiếp theo, chúng ta sẽ điền tên cộng tác viên và tính toán tổng số tiền hoa hồng. Chúng ta sẽ kéo thêm dữ liệu vào kết xuất đầu ra của bộ nhu liệu bên dưới báo cáo.

Hoa Hồng Cộng Tác Viên

Trước tiên hãy kéo thêm cột tên cộng tác viên (name) từ bên trái vào lưới kết xuất.

Thêm cộng tác viên và mức hoa hồng vào kết xuất

Lưu ý: Luôn luôn kiểm tra xem số lượng hàng dữ liệu trong kết xuất đầu ra có bị thay đổi ngoài mong muốn của bạn hay không.

Hệ quả của việc kéo cột dữ liệu tên cộng tác viên vào kết xuất là cột tổng số tiền quyên góp, giờ đây, sẽ được tính theo các nhóm dữ liệu của tổ hợp hai giá trị địa chỉ ví của chương trình (contract_address) và tên cộng tác viên (name).

Ví dụ, cùng một chương trình gây quỹ, được quảng bá bởi 3 cộng tác viên có tên khác nhau, sẽ tạo ra kết xuất gồm 3 dòng dữ liệu với tổng số tiền quỹ gây được của từng cộng tác viên.

Trong báo cáo này, chúng ta đã lọc dữ liệu theo địa chỉ ví của cộng tác viên, nên tên của cộng tác viên sẽ là duy nhất trong dữ liệu dùng để tính toán tổng số tiền. Vì thế, số lượng tổ hợp giá trị của contract_address và name không tăng lên. Kết quả là số lượng hàng dữ liệu trong kết xuất không bị thay đổi, giống như kỳ vọng của chúng ta.

Tương nhự như vậy, kéo thêm cột địa chỉ ví của cộng tác viên (address) vào lưới kết xuất cũng không làm tăng số lượng các dòng dữ liệu.

Cuối cùng, kéo thêm cột tỷ lệ hoa hồng của chương trình dành cho cộng tác viên bên dưới nhu liệu ONEIRO_DT_CP vào lưới kết xuất. Do mỗi chương trình chỉ có một địa chỉ ví (contract_address) và một mức hoa hồng (affiliate_fee), nên tổ hợp giá trị contract_address và affiliate_fee cũng sẽ không làm thay đổi số lượng các dòng trong kết quả đầu ra.

Khi chạy báo cáo, bộ nhu liệu bên dưới của nó sẽ trông giống như sau:

Nhu liệu có thêm cộng tác viên và mức hoa hồng

Và như vậy để tính hoa hồng cho cộng tác viên, bạn chỉ cần nhân giá trị trong cột tỷ lệ hoa hồng affiliate_fee với giá trị trong cột tổng số tiền amount, và cộng các kết quả này với nhau. Chúng ta sẽ dùng lại thẻ hàm Aggregate. Đi đến bảng <#Config> và nhập các giá trị sau:

M17: TotalEarning
N17: <#Aggregate(SUM;Report;<#Report.amount>*<#Report.affiliate_fee>/100)>

Giải thích:

1. Ô M17 xác định một thẻ tên TotalEarning.

2. Ô N17 là định nghĩa của thẻ tên này: Nó dùng thẻ hàm Aggregate trên bộ nhu liệu Report, cho mỗi dòng dữ liệu nó nhân giá trị trong cột số tiền (amount) với giá trị trong cột tỷ lệ hoa hồng (affiliate_fee), sau đó trả về kết quả là tổng số của tất các các khoản này. Điều ngày tương đương với việc tính hoa hồng cộng tác viên cho từng chương trình và sau đó cộng tổng hoa hồng của tất cả các chương trình lại với nhau. Chính là tổng hoa hồng của cộng tác viên.

Định nghĩa thêm thẻ hàm Name để lấy ra tên của cộng tác viên sử dụng thẻ hàm Lookup, như sau:

M19: Name
N19: <#Lookup(Report;address;<#Affiliate>;name)>

Giải thích:

1. Ô M19 xác định một thẻ tên Name.

2. Ô N19 là định nghĩa của thẻ tên này: Nó dùng thẻ hàm Lookup để tìm trong bộ nhu liệu Report dòng có giá trị của cột address bằng với giá trị chứa trong thẻ tên <#Affiliate> và trả về giá trị trong cột name. Thẻ tên Affiliate được khai báo ở dòng 12 cột M phía trên. Nó chính là địa chỉ ví của cộng tác viên lấy từ bộ lọc của người dùng nhập vào. Vì thê hàm Lookup sẽ trả về đúng tên của cộng tác viên đó.

   

   Tính hoa hồng cho cộng tác viên

Một thẻ chỉ có thể nhìn thấy một thẻ khác nếu thẻ kia nằm ở hàng phía trên nó. Trong ví dụ này, thẻ Affiliate nằm ở hàng số 12, do vậy thẻ Name ở hàng số 19 có thể nhìn thấy và gọi nó.

Quay lại bảng trình bày của báo cáo và áp dụng các giá trị này vào. Đi đến các ô sau và nhập các thẻ này:

• Ô D4: <#Name>

• Ô F4: <#TotalEarning>

  

  Ứng dụng các thẻ hàm tính hoa hồng và tên

Chạy báo cáo để kiểm tra kết quả. Để thuận tiện, bạn có thể tài mẫu báo cáo đã hoàn thiện về tại đầy:

34KB

Affiliate Income Stage 2 - VN.xlsx

Trong bài tiếp theo, chúng ta sẽ xem cách điền vào danh sách các chương trình gây quỹ được quảng bá bởi cộng tác viên như thế nào. Chúng ta sẽ học một khái niệm mới: Dải dữ liệu, hay còn gọi là named ranged trong Excel.

Danh sách chương trình quảng bá bởi cộng tác viên