Nội dung
Khi nhắc đến cơ sở dữ liệu (DB), điều đầu tiên nghĩ đến tất nhiên là tất cả các loại từ thông dụng như SQL, Oracle, 1C, hoặc ít nhất là Access. Tất nhiên, đây là những chương trình rất mạnh mẽ (và phần lớn là đắt tiền) có thể tự động hóa công việc của một công ty lớn và phức tạp với rất nhiều dữ liệu. Vấn đề là đôi khi sức mạnh như vậy chỉ đơn giản là không cần thiết. Doanh nghiệp của bạn có thể nhỏ và có quy trình kinh doanh tương đối đơn giản, nhưng bạn cũng muốn tự động hóa nó. Và đối với các công ty nhỏ, đây thường là vấn đề sống còn.
Để bắt đầu, hãy lập ĐKTC. Trong hầu hết các trường hợp, một cơ sở dữ liệu cho kế toán, ví dụ, bán hàng cổ điển sẽ có thể:
- giữ trong bảng thông tin về hàng hóa (giá cả), giao dịch đã hoàn thành và khách hàng và liên kết các bảng này với nhau
- có thoải mái biểu mẫu đầu vào dữ liệu (với danh sách thả xuống, v.v.)
- tự động điền vào một số dữ liệu mẫu in (thanh toán, hóa đơn, v.v.)
- vấn đề cần thiết báo cáo để kiểm soát toàn bộ quy trình kinh doanh theo quan điểm của người quản lý
Microsoft Excel có thể xử lý tất cả những điều này với một chút nỗ lực. Hãy cố gắng thực hiện điều này.
Bước 1. Dữ liệu ban đầu ở dạng bảng
Chúng tôi sẽ lưu trữ thông tin về sản phẩm, doanh số và khách hàng trong ba bảng (trên cùng một trang hoặc trên các bảng khác nhau - điều đó không quan trọng). Về cơ bản, điều quan trọng là biến chúng thành "bảng thông minh" với kích thước tự động, để không phải nghĩ về nó trong tương lai. Điều này được thực hiện với lệnh Định dạng dưới dạng bảng chuyển hướng Trang Chủ (Trang chủ - Định dạng dưới dạng Bảng). Trên tab sau đó xuất hiện xây dựng (Thiết kế) cung cấp các bảng tên mô tả trong trường Tên bảng để sử dụng sau này:
Tổng cộng, chúng ta sẽ nhận được ba “bảng thông minh”:
Xin lưu ý rằng các bảng có thể chứa thêm dữ liệu làm rõ. Vì vậy, ví dụ, Giá cảchứa thông tin bổ sung về danh mục (nhóm sản phẩm, bao bì, trọng lượng, v.v.) của từng sản phẩm và bảng Khách hàng - thành phố và khu vực (địa chỉ, TIN, chi tiết ngân hàng, v.v.) của mỗi người trong số họ.
Bàn Bán hàng sẽ được chúng tôi sử dụng sau này để nhập các giao dịch đã hoàn thành vào đó.
Bước 2. Tạo biểu mẫu nhập dữ liệu
Tất nhiên, bạn có thể nhập dữ liệu bán hàng trực tiếp vào bảng màu xanh lá cây Bán hàng, nhưng điều này không phải lúc nào cũng thuận lợi và dẫn đến sự xuất hiện của các lỗi và lỗi chính tả do “yếu tố con người”. Do đó, tốt hơn là bạn nên tạo một biểu mẫu đặc biệt để nhập dữ liệu trên một trang tính riêng biệt như sau:
Trong ô B3, để lấy ngày-giờ hiện tại được cập nhật, hãy sử dụng hàm TDATA (BÂY GIỜ). Nếu thời gian không cần thiết, thì thay vào đó TDATA chức năng có thể được áp dụng TODAY (HÔM NAY).
Trong ô B11, tìm giá của sản phẩm đã chọn trong cột thứ ba của bảng thông minh Giá cả sử dụng chức năng VPR (Vlookup). Nếu bạn chưa gặp phải nó trước đây, thì trước tiên hãy đọc và xem video tại đây.
Trong ô B7, chúng ta cần một danh sách thả xuống với các sản phẩm từ danh sách giá. Đối với điều này, bạn có thể sử dụng lệnh Dữ liệu - Xác thực dữ liệu (Xác nhận dữ liệu), chỉ định như một ràng buộc Danh sách (Danh sách) và sau đó nhập vào trường nguồn (Nguồn) liên kết đến cột Họ tên từ bảng thông minh của chúng tôi Giá cả:
Tương tự, một danh sách thả xuống với các máy khách được tạo, nhưng nguồn sẽ hẹp hơn:
= INDIRECT (“Khách hàng [Khách hàng]”)
Chức năng GIÁN TIẾP (GIÁN TIẾP) trong trường hợp này là cần thiết vì Excel không hiểu các liên kết trực tiếp đến bảng thông minh trong trường Nguồn. Nhưng cùng một liên kết được "bọc" trong một hàm GIÁN TIẾP đồng thời, nó hoạt động với một tiếng nổ (thêm về điều này trong bài viết về tạo danh sách thả xuống với nội dung).
Bước 3. Thêm macro mục nhập bán hàng
Sau khi điền vào biểu mẫu, bạn cần thêm dữ liệu đã nhập vào biểu mẫu đó vào cuối bảng Bán hàng. Sử dụng các liên kết đơn giản, chúng tôi sẽ tạo thành một dòng để được thêm vào ngay bên dưới biểu mẫu:
Những thứ kia. ô A20 sẽ có liên kết đến = B3, ô B20 sẽ có liên kết đến = B7, v.v.
Bây giờ, hãy thêm macro cơ bản 2 dòng sao chép chuỗi đã tạo và thêm nó vào bảng Bán hàng. Để thực hiện việc này, hãy nhấn tổ hợp Alt + F11 hoặc nút Visual Basic chuyển hướng nhà phát triển (Nhà phát triển). Nếu tab này không hiển thị, trước tiên hãy bật tab này trong cài đặt Tệp - Tùy chọn - Thiết lập ruy-băng (Tệp - Tùy chọn - Tùy chỉnh ruy-băng). Trong cửa sổ trình soạn thảo Visual Basic mở ra, hãy chèn một mô-đun trống mới qua menu Chèn - Mô-đun và nhập mã macro của chúng tôi vào đó:
Sub Add_Sell () Worksheets ("Input Form"). Range ("A20: E20"). Copy 'Sao chép dòng dữ liệu từ biểu mẫu n = Worksheets ("Sales"). Range ("A100000"). End (xlUp) . Hàng 'xác định số lượng của hàng cuối cùng trong bảng. Bảng tính bán hàng ("Bán hàng"). Ô (n + 1, 1) .PasteSpecial Paste: = xlPasteValues 'dán vào dòng trống tiếp theo Bảng tính ("Mẫu đầu vào"). Phạm vi ("B5, B7, B9"). ClearContents 'clear end sub form sub form Bây giờ chúng ta có thể thêm một nút vào biểu mẫu của mình để chạy macro đã tạo bằng cách sử dụng danh sách thả xuống Chèn chuyển hướng nhà phát triển (Nhà phát triển - Chèn - Nút):
Sau khi bạn vẽ nó, giữ nút chuột trái, Excel sẽ hỏi bạn cần gán macro nào cho nó - hãy chọn macro của chúng tôi Thêm_Bán. Bạn có thể thay đổi văn bản trên một nút bằng cách nhấp chuột phải vào nút đó và chọn lệnh Thay đổi văn bản.
Bây giờ, sau khi điền vào biểu mẫu, bạn có thể chỉ cần nhấp vào nút của chúng tôi và dữ liệu đã nhập sẽ tự động được thêm vào bảng Bán hàng, và sau đó biểu mẫu được xóa để tham gia giao dịch mới.
Bước 4 Liên kết các bảng
Trước khi xây dựng báo cáo, hãy liên kết các bảng của chúng ta với nhau để sau này chúng ta có thể nhanh chóng tính toán doanh số bán hàng theo khu vực, khách hàng hoặc danh mục. Trong các phiên bản Excel cũ hơn, điều này sẽ yêu cầu sử dụng một số hàm. VPR (Vlookup) để thay thế giá, danh mục, khách hàng, thành phố, v.v. vào bảng Bán hàng. Việc này đòi hỏi thời gian và nỗ lực của chúng tôi, đồng thời cũng “ngốn” rất nhiều tài nguyên Excel. Bắt đầu với Excel 2013, mọi thứ có thể được thực hiện đơn giản hơn nhiều bằng cách thiết lập mối quan hệ giữa các bảng.
Để làm điều này, trên tab Ngày (Ngày) Nhấp chuột mối quan hệ (Mối quan hệ). Trong cửa sổ xuất hiện, nhấp vào nút Tạo (Mới) và chọn từ danh sách thả xuống liệt kê các bảng và tên cột mà chúng nên có liên quan với nhau:
Một điểm quan trọng: các bảng phải được chỉ định theo thứ tự này, tức là bảng được liên kết (Giá cả) không được chứa trong cột khóa (Họ tên) các sản phẩm trùng lặp, như nó xảy ra trong bảng Bán hàng. Nói cách khác, bảng được liên kết phải là bảng mà bạn sẽ tìm kiếm dữ liệu bằng cách sử dụng VPRnếu nó đã được sử dụng.
Tất nhiên, bảng được kết nối theo một cách tương tự Bán hàng với bàn Khách hàng theo cột chung Khách hàng:
Sau khi thiết lập các liên kết, cửa sổ quản lý các liên kết có thể được đóng lại; bạn không phải lặp lại quy trình này.
Bước 5. Chúng tôi xây dựng báo cáo bằng cách sử dụng bản tóm tắt
Bây giờ, để phân tích doanh số bán hàng và theo dõi động thái của quy trình, chẳng hạn như chúng ta hãy tạo một số loại báo cáo bằng cách sử dụng bảng tổng hợp. Đặt ô hiện hoạt thành bảng Bán hàng và chọn tab trên ruy-băng Chèn - PivotTable (Chèn - Bảng tổng hợp). Trong cửa sổ mở ra, Excel sẽ hỏi chúng ta về nguồn dữ liệu (tức là bảng Bán hàng) và một nơi để tải lên báo cáo (tốt nhất là trên một trang tính mới):
Điểm quan trọng là cần phải bật hộp kiểm Thêm dữ liệu này vào mô hình dữ liệu (Thêm dữ liệu vào Mô hình Dữ liệu) ở cuối cửa sổ để Excel hiểu rằng chúng ta muốn tạo báo cáo không chỉ trên bảng hiện tại mà còn sử dụng tất cả các mối quan hệ.
Sau khi nhấp vào OK một bảng điều khiển sẽ xuất hiện ở nửa bên phải của cửa sổ Các trường trong bảng tổng hợpbấm vào liên kết ở đâu Tất cảđể xem không chỉ bảng hiện tại, mà tất cả các "bảng thông minh" có trong cuốn sách cùng một lúc. Và sau đó, như trong bảng tổng hợp cổ điển, bạn có thể chỉ cần kéo các trường chúng ta cần từ bất kỳ bảng nào có liên quan vào khu vực Lọc, Hàng, Stolbtsov or Các giá trị - và Excel sẽ ngay lập tức tạo bất kỳ báo cáo nào chúng ta cần trên trang tính:
Đừng quên rằng bảng tổng hợp cần được cập nhật định kỳ (khi dữ liệu nguồn thay đổi) bằng cách nhấp chuột phải vào bảng đó và chọn lệnh Cập nhật & Lưu (Làm mới), bởi vì nó không thể làm điều đó một cách tự động.
Ngoài ra, bằng cách chọn bất kỳ ô nào trong phần tóm tắt và nhấn nút Biểu đồ xoay vòng (Biểu đồ xoay vòng) chuyển hướng nghiên cứu (Phân tích) or Thông số (Tùy chọn) bạn có thể nhanh chóng hình dung kết quả được tính toán trong đó.
Bước 6. Điền vào các bản in được
Một nhiệm vụ điển hình khác của bất kỳ cơ sở dữ liệu nào là tự động điền các mẫu và biểu mẫu in khác nhau (hóa đơn, hóa đơn, hành vi, v.v.). Tôi đã viết về một trong những cách để làm điều này. Ở đây chúng tôi triển khai, ví dụ: điền vào biểu mẫu theo số tài khoản:
Giả định rằng trong ô C2 người dùng sẽ nhập một số (số hàng trong bảng Bán hàng, trên thực tế), và sau đó dữ liệu chúng ta cần được lấy lên bằng cách sử dụng hàm đã quen thuộc VPR (Vlookup) Và các tính năng INDEX (MỤC LỤC).
- Cách sử dụng hàm Vlookup để tra cứu và tra cứu các giá trị
- Cách thay thế hàm VLOOKUP bằng hàm INDEX và MATCH
- Tự động điền các biểu mẫu và biểu mẫu với dữ liệu từ bảng
- Tạo báo cáo với PivotTables