Hình ảnh bởi Editor | Midjourney
Liệt kê sản phẩm là yếu tố thiết yếu để quản lý doanh nghiệp. Một danh mục sản phẩm tương tác cho phép người dùng tìm kiếm, lọc và duyệt sản phẩm một cách động. Excel là công cụ tuyệt vời cho việc catalog, vì nó sử dụng các công thức động, danh sách thả xuống, bộ lọc, slicer, v.v. Trong hướng dẫn này, chúng ta sẽ tạo danh mục sản phẩm tương tác trong Excel.
Xét một doanh nghiệp thương mại điện tử nhỏ bán phụ kiện điện tử cần danh mục sản phẩm tương tác trong Excel để tìm kiếm sản phẩm, lọc theo danh mục, kiểm tra trạng thái tồn kho, v.v.
Bước 1: Thiết lập Cơ sở dữ liệu Sản phẩm
1.1. Tạo Bảng Danh sách Sản phẩm
- Tạo một sheet mới trong Excel.
- Chèn các cột sản phẩm liên quan đến doanh nghiệp của bạn với các chi tiết phù hợp như:
- ID Sản phẩm
- Tên sản phẩm
- Danh mục
- Giá
- Trạng thái tồn kho
- Hình ảnh (tùy chọn)
- Mô tả
1.2. Chuyển đổi Dữ liệu thành Bảng
Chuyển đổi dữ liệu của bạn thành bảng. Việc lọc, sắp xếp và áp dụng định dạng có điều kiện sẽ dễ dàng hơn trong bảng.
- Chọn toàn bộ tập dữ liệu (bao gồm tiêu đề).
- Vào tab Chèn >> chọn Bảng.
- Chọn "Bảng của tôi có tiêu đề" và nhấp OK.
Kết quả:
Bước 2: Tạo Chức năng Tìm kiếm
Để cho phép người dùng tìm kiếm theo tên sản phẩm:
- Tạo một sheet mới và đặt tên là Category.
- Chọn một ô và gán nhãn "Tìm kiếm Sản phẩm:".
- Trong ô B1, người dùng có thể nhập từ khóa tìm kiếm.
- Chọn ô A3 và chèn công thức sau.
Công thức:
=FILTER(ProductTable[[Tên sản phẩm]:[Trạng thái tồn kho]], ISNUMBER(SEARCH(B1, ProductTable[Tên sản phẩm])), "Không tìm thấy kết quả")
Công thức này chỉ lọc các sản phẩm chứa từ khóa tìm kiếm của ô B1 sau đó trả về thông tin sản phẩm. Bạn sẽ nhận được chi tiết Tên sản phẩm, Danh mục, Giá và Trạng thái tồn kho của giá trị tìm kiếm đó.
Bước 3: Thêm Danh sách Thả xuống
3.1. Thêm Danh sách Thả xuống cho Danh mục
Để lọc sản phẩm theo danh mục, bạn có thể thêm danh sách thả xuống.
- Chọn một ô và gán nhãn "Chọn Danh mục:".
- Nhấp vào F2 >> vào tab Dữ liệu >> chọn Xác thực dữ liệu.
- Trong Tiêu chí xác thực,
- Trong trường Cho phép >> chọn Danh sách
- Trong trường Nguồn >> chèn công thức sau để tạo danh sách danh mục duy nhất.
=OFFSET(Products!$C$2, 0, 0, COUNTA(Products!$C$2:$C$11), 1)
Danh sách thả xuống này sẽ hiển thị danh sách danh mục duy nhất để chọn.
3.2. Thêm Danh sách Thả xuống cho Trạng thái Tồn kho
- Nhấp vào F4 >> vào tab Dữ liệu >> chọn Xác thực dữ liệu.
- Trong Tiêu chí xác thực,
- Trong trường Cho phép >> chọn Danh sách
- Trong trường Nguồn >> chèn công thức sau để tạo danh sách trạng thái tồn kho duy nhất từ sheet khác:
=OFFSET(Products!$E$2, 0, 0, COUNTA(Products!$E$2:$E$11), 1)
Danh sách thả xuống này sẽ hiển thị danh sách trạng thái tồn kho duy nhất để chọn.
Bước 4: Hiển thị Sản phẩm Được Lọc Động
Bạn có thể chọn giá trị từ danh sách thả xuống để hiển thị các sản phẩm cụ thể.
- Chọn một khu vực trong Sheet Catalog nơi kết quả lọc sẽ được hiển thị.
- Gán nhãn tiêu đề cột.
- Bạn có thể sử dụng các công thức động để lọc danh sách sản phẩm dựa trên giá trị thả xuống.
4.1. Sử dụng hàm FILTER để Lọc Dữ liệu Động
- Chọn ô H2 và chèn công thức sau.
Công thức:
=FILTER(ProductTable, (ProductTable[Danh mục] = F2) * (ProductTable[Trạng thái tồn kho] = F4), "Không tìm thấy sản phẩm")
Công thức này sẽ chỉ hiển thị các sản phẩm khớp với cả danh mục và trạng thái tồn kho.
- F2 là danh mục được chọn và F4 là trạng thái tồn kho được chọn từ danh sách thả xuống
- Nếu cả hai điều kiện không khớp, nó hiển thị "Không tìm thấy sản phẩm".
Bây giờ, việc chọn danh mục và trạng thái sẽ lọc động các sản phẩm liên quan.
4.2. Áp dụng Định dạng Có Điều kiện cho Trạng thái Tồn kho
Làm nổi bật các mặt hàng hết hàng để dễ dàng quan sát.
- Chọn cột Trạng thái tồn kho.
- Vào tab Trang chủ >> từ Định dạng có điều kiện >> chọn Quy tắc mới.
- Chọn "Sử dụng công thức để xác định ô cần định dạng".
- Nhập công thức:
- Nhấp Định dạng >> chọn Màu nền >> chọn OK.
Kết quả cuối cùng:
Bây giờ, các sản phẩm "Hết hàng" sẽ xuất hiện được làm nổi bật bằng màu cam đào.
Bước 5: Chèn Slicer để Tăng Tương tác
- Đi đến Bảng sản phẩm >> nhấp vào bên trong bảng sản phẩm.
- Vào tab Chèn >> chọn Slicer.
- Chọn Danh mục và Trạng thái tồn kho >> nhấp OK.
- Sắp xếp các slicer trong sheet Sản phẩm để lọc dễ dàng.
Slicer cho phép người dùng nhấp vào nút bấm thay vì sử dụng danh sách thả xuống.
- Chọn một Danh mục và Còn hàng từ slicer để lọc bảng sản phẩm.
Kết luận
Bằng cách làm theo các bước trên, bạn có thể tạo danh mục sản phẩm tương tác trong Excel. Danh mục sản phẩm này hỗ trợ lọc động, tìm kiếm, hiển thị sản phẩm trực tiếp, v.v. Dựa trên yêu cầu của bạn, bạn có thể tùy chỉnh danh mục sản phẩm này. Giải pháp này hoàn hảo cho các doanh nghiệp nhỏ, cửa hàng thương mại điện tử hoặc theo dõi tồn kho. Bạn có thể quản lý tồn kho của mình mà không cần phần mềm trả phí.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!