Chào các bác, dạo này em đang làm báo cáo quản trị cho công ty, có phần tính toán Lỗ/Lãi gộp (Gross Profit) cho từng sản phẩm theo tháng. File gốc thì hơi thủ công, mỗi lần lại phải copy paste dữ liệu rồi dùng hàm để tính, mất khá nhiều thời gian.
Em có mày mò và xây dựng được một file Excel tự động hóa phần này, giúp em tiết kiệm được khối thời gian. Em xin chia sẻ lại cách làm để mọi người tham khảo, có thể áp dụng cho các bài toán tương tự.
Nguyên tắc chung:
- Lãi gộp = Doanh thu thuần - Giá vốn hàng bán
- Doanh thu thuần = Tổng doanh thu - Các khoản giảm trừ (chiết khấu, trả hàng...)
- Giá vốn hàng bán: Phần này tùy thuộc vào phương pháp kế toán của công ty (FIFO, LIFO, Bình quân gia quyền...). Trong ví dụ này, em giả định có sẵn cột giá vốn cho mỗi đơn hàng.
Các bước thực hiện:
- Chuẩn bị dữ liệu: Đảm bảo dữ liệu của bạn có các cột cần thiết như: Ngày, Mã sản phẩm, Tên sản phẩm, Số lượng, Đơn giá bán, Doanh thu, Giá vốn.
- Sử dụng hàm SUMIFS: Đây là chìa khóa để tự động hóa. Chúng ta sẽ dùng
SUMIFSđể tính tổng doanh thu và tổng giá vốn cho từng sản phẩm dựa trên các điều kiện (ví dụ: theo tháng, theo sản phẩm). - Công thức ví dụ:
Giả sử dữ liệu của bạn nằm ở SheetData, từ dòng 2 trở đi.
Bảng tổng hợp bạn tạo ở SheetSummary, cột A là tên sản phẩm, cột B là tháng.Trong ô C2 (tính Doanh thu thuần cho sản phẩm ở A2, tháng ở B2):
=SUMIFS(Data!$E:$E, Data!$B:$B, $A2, Data!$F:$F, B$2)Trong ô D2 (tính Giá vốn cho sản phẩm ở A2, tháng ở B2):
=SUMIFS(Data!$G:$G, Data!$B:$B, $A2, Data!$F:$F, B$2) - Tính Lãi gộp: Tại cột E2, công thức sẽ là:
=D2-C2 - Tạo biểu đồ (tùy chọn): Bạn có thể thêm biểu đồ để trực quan hóa dữ liệu lãi gộp theo tháng hoặc theo sản phẩm.
Với cách này, mỗi khi có dữ liệu mới, bạn chỉ cần paste vào Sheet Data, Sheet Summary sẽ tự động cập nhật. Rất tiện lợi!
Có bác nào có cách làm hay hơn hoặc gặp khó khăn gì khi áp dụng thì cùng thảo luận nhé!