Chúng ta xét ví dụ sau để tìm hiểu việc ứng dụng của công cụ Spin Button:

Cho bảng tính sau, sử dụng Spin Button để thay đổi nội dung của Phiếu Xuất kho tương ứng theo mỗi số phiếu trong bảng kê

Các bước thực hiện như sau:

Tạo công cụ Spin Button

Spin Button còn được gọi với tên khác là “Nút bấm Up & Down”, sử dụng với mục đích để mỗi lần bấm nút Up sẽ tăng giá trị lên 1 đơn vị, bấm nút Down sẽ giảm giá trị xuống 1 đơn vị

Spin Button được tạo như sau:

Mở tab Developer trên thanh công cụ (Click chuột phải vào thanh công cụ, chọn mục Customize the Ribbon, trong mục này đánh dấu chọn vào thẻ Developer)

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Trong tab Developer trên thanh công cụ, bấm chọn mục Insert, chọn tới chức năng Spin Button rồi đặt tới vị trí bất kỳ trong Sheet. Lưu ý nếu vẽ Spin theo chiều ngang thì 2 nút mũi tên sẽ hướng Trái-Phải, còn vẽ chiều dọc thì 2 nút mũi tên sẽ hướng Trên-Dưới.

Sau khi tạo được công cụ Spin Button, chúng ta thiết lập nơi Link dữ liệu kết quả của nút Spin:

Trong Tab Developer > Chọn Design Mode > Click chuột phải vào Spin button và chọn Properties > Trong cửa sổ Properties chú ý mục LinkedCell. Tại đây bạn chọn nơi muốn liên kết tới vị trí đặt kết quả.

Nếu LinkedCell tới sheet khác thì phải kèm theo tên Sheet và vị trí Cell trong Sheet đó.

Xây dựng công thức liên kết dữ liệu theo Spin Button

Bước 1: Đánh giá đặc điểm dữ liệu của Spin Button

Spin button cho ta các số tự nhiên liên tiếp, bắt đầu từ 0.

Khi bấm nút Up (mũi tên đi lên hoặc sang phải) thì kết quả tăng lên 1

Khi bấm nút Down (mũi tên đi xuống hoặc sang trái) thì kết quả giảm đi 1

Do đó chúng ta phải dựa vào đặc điểm này để xây dựng công thức lấy dữ liệu từ Bảng kê vào PXK

Bước 2: Tạo cột phụ cho bảng kê

Dựa theo đặc điểm của bảng dữ liệu chúng ta sẽ xây dựng công thức cho phù hợp.

Với bảng dữ liệu này có đặc điểm: Mỗi phiếu gồm nhiều dòng, mỗi dòng khác nhau ở Tên hàng và Số lượng, còn các đối tượng Số phiếu, Ngày, Khách hàng là giống nhau.

=> 1 cột dùng để phân chia thứ tự các phiếu với nhau (Cột Thứ tự phiếu)

Diễn giải tính logic: Nếu dòng số phiếu đang xét = số phiếu tại dòng trước đó thì thứ tự phiếu không đổi; nếu khác thì sẽ tăng số thứ tự lên 1 đơn vị (giá trị lớn nhất của vùng trước đó + 1)

=> 1 cột dùng để phân chia từng dòng phát sinh trong mỗi phiếu (Cột Thứ tự dòng)

Diễn giải tính Logic: Đếm số lần lặp lại của số phiếu trong vùng từ vị trí ban đầu tới vị trí số phiếu ở dòng đang xét.

Bước 3: Xây dựng công thức lấy dữ liệu trong Phiếu Xuất kho

Ứng dụng hàm Index +Match để lấy dữ liệu, vì điều kiện xét là giá trị của nút Spin tương ứng với cột Thứ tự phiếu. Nếu xét bằng hàm Vlookup sẽ không tính được.

Giá trị Ngày của PXK (ô K2)

= INDEX(B1:B26,MATCH(M2,F1:F26,0))

Trong đó:

• B1:B26 là cột Ngày của Bảng kê
• F1:F26 là cột Thứ tự phiếu của Bảng kê
• M2 là giá trị của SpinButton

Người mua hàng (ô K3)

= INDEX(C1:C26,MATCH(M2,F1:F26,0))

Thay đổi đối tượng trong hàm Index, xét tới cột C là cột Người mua hàng, còn nội dung hàm Match không thay đổi

Công thức lấy Tên hàng:

Tại đây chúng ta sử dụng công thức mảng để xét hàm Index + Match với nhiều điều kiện. Cụ thể là gồm 2 điều kiện:

• Thứ tự phiếu = giá trị của Spin Button
• Thứ tự dòng = giá trị của cột STT

Do đó các nội dung trong công thức như sau:

=IFERROR(INDEX(D1:D26,MATCH(M2&$I$6:$I$20,F1:F26&G1:G26,0)),””)   Kết thúc bằng lệnh Ctrl+Shift+Enter

• D1:D26 là cột Tên hàng trong bảng kê
• M2&I6:I20 là ghép giữa 2 điều kiện Giá trị của Spin Button với giá trị của cột STT
• F1:F26&G1:G26 là ghép giữa 2 cột Thứ tự phiếu và Thứ tự dòng trong phần cột phụ của bảng kê
• Hàm IFERROR để xử lý những nội dung lỗi (không có phát sinh, không tìm thấy kết quả thì sẽ báo lỗi #NA) => Nếu lỗi thì trả về giá trị là “”

Tương tự như vậy chúng ta xây dựng công thức cho phần Số lượng, trong đó vẫn sử dụng công thức mảng giống Tên hàng, nhưng đối tượng là E1:E26 cho cột Số lượng

=IFERROR(INDEX(E1:E26,MATCH(M2&$I$6:$I$20,F1:F26&G1:G26,0)),””)

Kết luận

Như vậy chúng ta đã thấy được tính ứng dụng của công cụ Spin Button rất hữu ích trong việc trích xuất dữ liệu theo những số phiếu liên tiếp. Chỉ cần kết hợp với hàm Index+Match là chúng ta đã có thể lọc dữ liệu từ bảng kê ra các phiếu 1 cách dễ dàng.

Ứng dụng: Có thể ứng dụng nội dung này vào việc quản lý các đối tượng:

• Quỹ : Phiếu thu, chi,
• Kho: Phiếu nhập kho, xuất kho
• Đối tượng khác: Hóa đơn, hợp đồng
• Danh sách khen thưởng, tặng quà
• Đơn đặt hàng

Tải về file mẫu tại địa chỉ: http://bit.ly/2mDUJ2v

Rất nhiều kiến thức phải không nào? Những kiến thức ở Excel không khó. Nhưng nếu bạn không hệ thống và tổng hợp, cũng như rèn luyện thường xuyên thì sẽ không tận dụng tối đa tính năng của Excel. Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online