.st0{fill:#FFFFFF;}

Hướng dẫn tự động cập nhật dữ liệu với công cụ Spin Button 

 Tháng Chín 20, 2021

By  Lê Đình Chi

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ê

Spin Button

Spin Button

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)

Spin Button

Spin Button

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.

Developer

Developer

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ả.

Developer

Developer

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 EXG01 – Học Microsoft Excel từ cơ bản tới nâng cao dành cho người đi làm của zik.vn. Đâ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: zik.vn

Đánh giá bài viết này

zik.vn – Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm

Với sứ mệnh: Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển zik.vn đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Zik.vn tự hào khi được đồng hành cùng:

  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
  • 100.000+ học viên trên khắp Việt Nam

Tìm hiểu ngay các khóa học của Zik.vn TẠI ĐÂY



Lê Đình Chi


Your Signature

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

DMCA.com Protection Status
>