• Thứ Sáu, 09/11/2007 10:48 (GMT+7)

    Góp ý “Trả lời thư bạn đọc”

    Trên TGVT A số tháng 8/2007, mục ứng dụng văn phòng, trang 144, có câu hỏi về CSDL trong Excel, ngoài cách sử dụng VBA như đã nêu, chúng ta còn có thể dùng công cụ có sẵn của Excel để giải quyết bài toán đặt ra một cách đơn giản và hiệu quả.

    1. Sử dụng Pivot Table

    Thực hiện như sau: đưa con trỏ vào vùng dữ liệu, chọn Menu Data > Pivot table and chart report, xuất hiện các hộp thoại.

    Bước 1: Chấp nhận các lựa chọn ngầm định, nhấn Next.


    Bước 2:
    Chỉ định vùng dữ liệu, chấp nhận ngầm định hay điều chỉnh theo ý muốn ở hộp Range, Browse để chọn dữ liệu ở file khác, nhấn Next.


    Bước 3:
    Chỉ định vị trí của bảng chọn Where do you... report? New worksheet hoặc ở ngay bảng tính chứa dữ liệu, nên chọn New, nhấn tiếp Layout.


    Thiết lập bảng Pivot bằng cách kéo các nút tên trường vào vị trí tương ứng trong bảng.


    Theo yêu cầu đặt ra, ta kéo các nút Thang, Ten_SF, Trong_luong, Loai_goi, Khuyen_mai vào khu vực Page, So_luong_ban vào khu vực Data. Ở đây, hàm thống kê ngầm định là Sum, hay tính tổng số lượng.


    Nhấn OK. Trở lại bước 3, Finish.


    Hiện giờ ở Worksheet1 (mới tạo) có bảng Pivot. Lần lượt ở các hộp danh sách thả xuống, ta có thể chọn:

    Thang :8

    Ten_SF: Convit

    Trong_luong:800g

    Loai_goi:Mau xanh

    Khuyen_mai: Chai nuoc xa

    Kết quả trả về ở Total là 60

    Tuy nhiên Pivot Table còn cho ta cách nhìn nhận so sánh các dữ liệu linh hoạt và sâu sắc hơn, ví dụ thể hiện số lượng từng loại SF bán ra theo tháng.


    2. Sử dụng hàm SUMPRODUCT


    (đã nêu ở TGVT A tháng 8/2006, trang 142)

    Thực hiện như sau:

    Chọn toàn bộ vùng dữ liệu (VD A1: F173), chọn Menu Insert > Name > Create, đánh dấu chọn hộp Top row.

    Như vậy, ta đã có các vùng dữ liệu như Thang, Ten_SF, Trong_luong, Loai_goi, Khuyen_mai được đặt theo tên.

    Từ một ô trong bảng cần tính tổng, nhập công thúc sau đây:

    =SUMPRODUCT((Thang=8) *(Ten_SF= "Convit")*(Trong_luong= "800g")*(Loai_goi= "M au xanh")*(Khuyen_mai=" Chai nuoc xa")*So_luong_ban)
    Tối ưu nhất là thay các giá trị 8, "Convit", "800g", ... bằng tham chiếu đến các ô, chỉ cần thay đổi các tham số ở ô này, tiện hơn sửa trực tiếp công thức.
    =SUMPRODUCT((Thang=I2)* (Ten_SF=J2)*(Trong_luong=K2)*(Loai_goi=L2)*(Khuyen_Mai=M2)*So_luong_ban)

    Kết quả trả về sẽ là 60 như ở phương pháp Pivot Table.

    Tuy nhiên, mức độ linh động ở Pivot Table tốt hơn, ví dụ khi tính toàn bộ các tháng, Pivot table chọn All ở hộp thả xuống nhưng ở đây phải sửa công thức bỏ (Thang=I2)* đi.

    Nguyễn Trọng Thành
    Email:
    ng.tr.thanh@gmail.com

    ID: A0710_150