Trong bài viết này mình muốn giới thiệu với 2 tùy chọn khá mạnh trong excel để xử lý dữ liệu và báo cáo. Bài viết này tập trung vào kỹ năng excel hơn là kỹ năng phân tích BCTC (sẽ viết ở một bài khác). Tuy nhiên mình muốn dùng 1 dữ liệu của một công ty đại chúng để xử lý cho "bõ".
Dữ liệu đầu ra đã được xử lý và dùng để minh họa cho phần hướng dẫn bên dưới
Phân tích BCTC Hòa Phát (HPG)
Dữ liệu đầu vào BCTC Tập đoàn Hòa Phát (HPG) từ 2012 đến 2015
1. Option Button (OB)
OB là tích chọn được tạo ra trong phần Developer/Design mode trong excel, chủ yếu để tạo các báo cáo động, giúp báo cáo trở nên rất sinh động và đỡ nhàm chám. Bạn nào hay làm về Dashboard thì OB thực sự là trợ thủ đắc lực. Ví dụ click chọn A sẽ bảng dữ liệu của A, biều đồ của B, click chọn B, hiện B....Cụ thể mời các bạn xem Sheet Graph
Khi click chọn từng chỉ tiêu, thì đồ thị và dữ liệu tương ứng sẽ hiện ra.
Cách làm như sau
Bước 1
Bạn tạo dữ liệu và vẽ biểu đồ từ các dữ liệu trong file excel, phần này bạn muốn xây dựng, vẽ vời gì cũng được. Mình không hướng dẫn chi tiết. Dữ liệu, chỉ số rất nhiều từ các sheet đính kèm.
(Click vào ảnh để xem full size - năm 2012 nếu bạn muốn tính các chỉ số trên thì cần lấy thêm dữ liệu năm 2011)
Bước 2:
Bước 2:
Mỗi phần biểu đồ và dữ liệu bạn đặt cho nó 1 cái tên như mình đã làm.
Để khỏi lẫn các bạn đánh cả số trước tên theo đúng tuần tự data với biều đồ bạn tạo từ trên xuống dưới
Bước 3:
Tạo Option Button như hình, lần lượt đặt tên và gán giá trị. Cụ thể ở file này là Link tới ô T4. Mỗi lần click 1 giá trị thì ô T4 sẽ nhảy ra 1 giá trị (Vì tính chất này mà bạn có dùng hàm If với T4 để tùy biến cho ra các dữ liệu khác nhau, nhưng bài này mình làm phức tạp hơn)
Bước 4:
Bạn copy 1 vùng đã đặt tên ở trên (gồm biểu đồ và data) bất kỳ và paste special - Linked picture (I) vào 1 vùng rộng bất kỳ (Ngay bên dưới)
Bước 5:
Tạo name Selection =
CHOOSE(Graph!$T$4;P1Tongquan;P2TSNH;P3TSDH;P4Nguonvon;P5PTNH;P6Doanhthu;P7Chiphi;P8Loinhuan;P9Thanhtoan;P10Hoatdong;P11LCTT)
Bước 6:
Click vào Picture mà bạn paste special ở trên rồi gõ vào thanh công thức = Selection và enter
Vậy là bạn đã tạo được link và bây giờ bạn click chọn là ảnh sẽ thay đổi tương ứng.
Hơi phức tạp tuy nhiên một khi bạn làm được bạn có thể làm được các báo cáo biến ảo.
2. Conditional formatting (CF)
CF là định dạng theo điều kiện, chủ yếu để lưu ý người đọc hay chính người chuẩn bị file. Thay vì bạn phải định dạng thủ công thì CF cho bạn định dạng cả 1 vùng, 1 sheet Ví dụ số âm và nhỏ hơn -20% thì sẽ màu đỏ, bôi đậm ; nhỏ hơn - 30% thì bôi đỏ ô đó; ô có giá trị FALSE sẽ tự động được bôi vàng để cảnh báo người làm....
Cụ thể trong trường hợp này.
Sheet Ratio/M2 mình đang để benchmark là 30% tức là mọi biến động dưới -30% và trên 30% đều được bôi đậm và đỏ từ cột Q tới U.
Bạn có thể thay đổi Benchmark để xem sự thay đổi.
Để tìm hiểu cách mình tạo rule, các bọn chọn vùng Q đến U trong file và vào Home/CF/Manage Rules.... Nó sẽ hiện toàn bộ rules mình làm với vùng này.
Hiểu được cơ chế bạn dễ dàng được New rule cho riêng mình
Và trong công việc của mình, khi làm Báo cáo kiểm toán thì mình định đạng đỏ số âm và highlight nếu báo FALSE khi đối chiếu chéo 2 dữ liệu với nhau (Báo cáo với thuyết minh, Phần trước với phần sau....)
No comments:
Post a Comment