Tìm hiểu thêm về MS EXCEL(2)
II.Bảng tính tự động
Phần I : “ Theo dõi sự tiến bộ bằng Excel” đã được giới thiệu trên trang /Web/Content.aspx?distid=1116
Mời bạn đọc xem tiếp phần II: “Bảng tính tự động” dưới đây.
Tóm tắt:
Một bảng tính có thể cảnh báo cho bạn biết những thay đổi quan trọng về dữ liệu trong cơ sở dữ liệu của mạng máy tính hoặc trên Internet. Chức năng Excel có thể được sử dụng để: 1. Theo dõi các hoạt động của thị trường chứng khoán, 2. Giám sát các thông tin cần được xử lý nhanh trong cơ sở dữ liệu của tổ chức, và 3. Giữ được sự phối hợp giữa các thương gia thường xuyên phải di chuyển trên thị trường chính hoặc các thông tin về đầu tư.
Bạn sẽ phải đi công tác xa. Bạn đã chuẩn bị mang theo máy tính xách tay, máy Palm kỹ thuật số hỗ trợ giúp cá nhân, máy nhận e-mail và điện thoại di động. Mặc dù đã có tất cả những thiết bị công nghệ cao này thì bạn cảm thấy bị cắt đứt liên lạc với văn phòng của mình, thậm chí kể cả trước khi bạn lên máy bay. Cuối cùng, bạn nhận ra rằng việc bạn đi công tác xa sẽ càng khiến bạn khó có thể kiểm soát chặt chẽ công việc kinh doanh của mình hơn, thậm chí kể cả khi bạn có thể liên lạc với văn phòng từ xa thông qua các Internet.
Bạn sẽ như thế nào nếu như có một chức năng có thể tự động thực hiện một số công việc giám sát giúp bạn, tự động thực hiện 24 giờ một ngày, bảy ngày một tuần - và thông báo lại cho bạn biết vào những thời điểm thích hợp?
Có thể bạn đã có chức năng này rồi, Excel ở trong cặp của bạn. Nhưng để sử dụng được chức năng này, bạn cần phải có một máy tính kết nối với Internet.
Bài báo này sẽ chỉ cho bạn cách sử dụng Excel để tìm kiếm dữ liệu bạn cần, và khi đạt đến một tiêu chí nào đó đã được cài đặt từ trước thì bạn sẽ được thông báo để hành động cho dù bạn đang ở bất kỳ đâu.
Dưới đây là một số việc mà chức năng của Excel có thể thực hiện được:
Theo dõi hoạt động của thị trướng chứng khoán. Excel có thể tự động nhập các dữ liệu lấy từ Internet và thông báo lại cho bạn biết qua e-mail những thay đổi quan trọng của thị trường cần phải được xử lý ngay lập tức.
Giám sát các thông tin trong hệ thống cơ sở dữ liệu của công ty bạn mà cần phải được xử lý nhanh. Ví dụ, những cảnh báo có thể sẽ được gửi đi khi có một ngưỡng giới hạn bị vượt qua, khi có một hợp đồng bán hàng lớn, khi có các đơn đặt hàng của những khách hàng lớn nhất của bạn, khi nhận được một lô hàng quan trọng, khi không giao hàng theo đúng thời gian quy định, khi có những thay đổi bất thường về số lượng và chất lượng trong sản xuất, khi có thông báo về sự vượt quá giới hạn tài chính cho phép và có những biến động bất thường. Bạn có thể lập trình để gửi cho một số người đã được chỉ định từ trước có trách nhiệm xử lý những cảnh báo này qua e-mail.
Giúp những thương nhân thường xuyên phải di chuyển nắm bắt được các thông tin về đầu tư và các thông tin của thị trường quan trọng. Excel có thể tự động gửi thư điện tử cho những người thương nhân này khi họ đã ra khỏi văn phòng, thông báo cho họ biết những thay đổi về giá sản phẩm và dịch vụ, về những sản phẩm đang có, những đơn chào hàng được giảm giá đặt biệt - và mỗi người chỉ nhận được những thông tin có liên quan đến chính cá nhân họ thôi.
Bây giờ chúng ta sẽ học cách tạo ra những thông báo tự động này.
THU NHẬP CÁC THÔNG TIN THỰC
Trong những số báo trước của Tạp chí Kế toán. (“Spreadsheet, Meet Database; Database, Meet Spreadsheet,” JofA, Dec.99, page 33), Philip Haase đã chỉ ra một cách chi tiết thách thức liên kết Excel với một cơ sở dữ liệu Access với các câu hỏi về cơ sở dữ liệu, và Jon Woodroof (“How to Link to Web Data,” JofA, Mar.99, page 55) đã trình bày cách sử dụng Excel như thế nào để thu thập các dữ liệu sống trên Internet với chương trình Web Queries. Bạn đọc có thể tham khảo thêm những bài báo này.
Trong bài báo này chúng ta cũng sẽ đi theo chiều hướng của những bài báo trên để liên kết một bảng tính Excel với một cơ sở dữ liệu và một bảng tính Excel với một Website. Sau đó chúng ta sẽ sử dụng những bảng tính đã được liên kết này để tạo ra và gửi đi những thông báo tự động qua e-mail.
Có một thay đổi quan trọng đối với các quá trình liên kết được trình bày ở những bài báo trước của Tạp chí Kế toán. Phiên bản mới nhất của Excel (phiên bản năm 2000) cho phép bạn cài đặt tần suất cập nhật (là khoảng thời gian giữa các lần cập nhật thông tin) đối với việc truy vấn dữ liệu từ một Website hoặc từ một cơ sở dữ liệu. Excel 97 không làm được điều này.
Trước khi thiết kế hệ thống gửi thông báo e-mail tự động, bạn hãy xem trước phần tiến trình liên kết cơ bản Excel và Web, phần này sẽ được trình bày kỹ hơn trong bài “How to Link to Web Data”. Trước tiên, bạn hãy tạo ra một tập tin Excel với hai bảng tính. Bảng thứ nhất gọi là Mua bán chứng khoán (Trading Stock), (phần trình bày 1), bảng tính này sẽ luôn theo sát giá chứng khoán (trong ví dụ này là Oracle và Cisco) và bảng tính này sẽ thực hiện các phép tính cần thiết – và bảng tính thứ hai tên là WebQuery. Sau đó bạn hãy tạo ra các ô như trong phần phần trình bày 1.
Trong bảng tính Mua bán chứng khoán, trong ô C5, phía dưới cột Market (thị trường), hãy đánh dòng lệnh = WebQuery!D4 vào; các dữ liệu đã được thu thập trong ô D4 của bảng tính Web Query sẽ được copy vào trong ô C5, như trong phần trình bày 1. Copy công thức này, với những thay đổi tên các ô cho phù hợp, vào trong ô của cột Market đối với mỗi cổ phiếu mà bạn muốn theo dõi và điền vào trong cột Stock Symbol (Ký hiệu chứng khoán). Bây giờ bạn đã có thể tạo ra một Câu hỏi Truy vấn để điền các giá trị của thị trường hiện tại vào trong hồ sơ của bạn.
Để tạo câu hỏi truy vấn, bạn hãy đặt con trỏ trong ô A1 của bảng tính WebQuery (phần trình bày 2, dưới đây) và bấm vào nút Data trên Toolbar (thanh công cụ). Bây giờ hãy bấm vào nút Get External Data, bấm tiếp vào nút Run Saved Query (tại thời điểm này bạn cũng có thể tạo ra một Web Query mới bằng cách bấm vào nút New Web Query). Tiếp theo bạn hãy lựa chọn một câu hỏi đã được lưu sẵn trong đó và bấm vào nút Get Data. Chúng ta sẽ chọn câu hỏi đã được lưu sẵn trong đó có tên là Microsoft Investor Stock Quotes.
Trong bài báo của Woodroof, tác giả đã chọn một câu hỏi truy vấn khác (Multiple Stock Quotes của PC Quote, Inc. tại địa chỉ http:// webservices.pcquote.com/cgi-bin/excel.exe). Chúng ta chọn câu hỏi truy vấn của Microsoft là bởi vì PC Quote không cung cấp câu hỏi truy vấn cùng với những phiên bản mới của Microsoft Office.
Bấm vào nút Get Data để gọi ra hộp hội thoại các dữ liệu bên ngoài (external data dialog box). Bấm vào nút Properties (trong phần trình bày 3, bên phải) và nhảy tới hộp Hội thoại Properties. Bạn hãy đặt tần suất cập nhật là 30 phút một lần (hoặc bất kỳ khoảng thời gian nào phù hợp với bạn) và Excel sẽ tự động cập nhật đều đặn các dữ liệu mới lấy từ Internet (phần trình bày 4, bên phải). Một hộp hội thoại tương tự cũng sẽ xuất hiện sau khi bạn tạo ra một câu hỏi truy vấn dữ liệu trong cơ sở dữ liệu, và bạn cũng có thể cài đặt tần suất cập nhật đối với các câu hỏi truy vấn theo đúng cách như trên. Bấm vào nút OK sau khi bạn vài đặt xong tần suất cập nhật và bạn sẽ quay trở lại hộp hội thoại các dữ liệu bên ngoài (phần trình bày 3).
Tiếp theo, bạn hãy bấm vào nút Parameter (thông số) và chọn mục Get the value from the following cell (chọn giá trị từ những ô sau). Bấm vào biểu tượng hình vuông ở phía bên phải textbox (hộp ký tự) và đánh dấu tất các ô có chứa ký hiệu chứng khoán trong bảng tính Trading Stock. Gõ phím Enter và bấm vào nút OK. Tại thời điểm này, Excel sẽ giám sát tất cả những biến động xảy ra vào bất kỳ thời điểm nào mà câu hỏi truy vấn đwocj cài đặt và chương trình này có thể tự động gửi e-mail về bất kỳ sự kiện nào mà bạn chỉ định.
THỰC HIỆN TỰ ĐỘNG
Bây giờ bạn đã sẵn sàng tạo ra những thư điện tử tự động cho tập tin Excel mà đã được liên kết với những dữ liệu thực trên Web. Tất nhiên là bạn sẽ không bị giới hạn liên kết chỉ với giá cổ phiếu mà bạn có thể liên kết Excel tới bất kỳ một Website tương thích nào.
Bước tiếp theo đòi hỏi bạn phải lập trình cho Visual Basis (VB). Để thuận tiện, bạn có thể tải (download) một chương trình hoàn chỉnh xuống từ địa chỉ http://www.bus.utk.edu/Acct/rose/jofa.html và bạn có thể dễ dàng sửa lại chương trình vày cho phù hợp với mục đích sử dụng của mình. Mã hiệu và ví dụ chúng tôi đưa ra là sử dụng Excel 2000 và Outlook Express 5.0.
Để bắt đầu, bạn hãy mở bảng tính Trading Stock, trong bảng tính này đã chứa các dữ liệu được nhập từ Wedata. Excel sẽ sử dụng những dữ liệu đã được nhập và này để theo dõi những thay đổi và xác định khi nào phải gửi e-mail. Trong ví dụ này chúng ta sẽ theo dõi giá cổ phiếu này giảm dưới một mức do bạn quy định từ trước. Bạn có thể tạo ra các e-mail tự động dựa trên bất kỳ một tiêu chí nào do bạn chọn.
Bấm vào Tools, và tiếp theo là bấm vào Visual Basis Editor (phần trình bày 6, bên phải).
Tiếp theo, bạn hãy bấm vào Sheet 1 (trong bảng Trading Stock) trong phần Project – màn hình VBA Project, màn hình này nằm ở phía bên trái của Bộ soạn thảo VB (phần trình bày 7, bên phải).
Điều này sẽ mở ra một cửa sổ bên phải của bộ soạn thảo VB, bạn hãy nhập mã hiệu của e-mail tự động và đưa ra một hàm con gọi là Private Sub Worksheet- Calculate ( ). Mã hiệu hoàn chỉnh này được trình bày và minh họa trong Phần trình bày 8, trang 37). Hàm con Private Sub Excel hoặc cho bất kỳ tiêu chí nào do bạn đặt ra để hình thành nên thông báo e-mail tự động.
THÔNG BÁO LẶP ĐI LẶP LẠI
Một trong những hạn chế tiếm ẩn của chương trình này là cách thức quản lý các thông báo lặp đi lặp lại. Ví dụ, nếu bảng tính tự động của bạn được cài đặt cập nhật dữ liệu 30 một lần, và giá cổ phiếu của Công ty Oracle vẫn giữ nguyên trong suốt cả ngày, thì bạn sẽ nhận được các thông báo giống nhau khi giá trị của ô thay đổi.
Tuy là những thông báo trùng lặp có thể hữu ích trong một số trường hợp thì bạn vẫn muốn chỉ nhận được một vài thông báo mỗi ngày. Để làm được điều này, bạn có thể giảm tần suất cập nhật thông tin hoặc thay đổi mã hiệu VB để chương trình này chỉ gửi một thông báo duy nhất cho mỗi sự thay đổi.
Để thay đổi mã VB, bạn hãy nhắp đúp vào This Workbook (Phần trình bày 9, bên phải).
Sau đó gõ những dòng lệnh sau:
Private Sub Workbook_ Open ( )
Range (“15”).Select.
ActiveCell.FormulaR1C1=”MAIL” End Sub
Mã này sẽ tạo ra một chương trình con, chương trình này sẽ hạot động bất cứ khi nào workbook trong Excel được mở ra. Chương trình con này sẽ tạo ra dòng chữ “MAIL” trong ô 15. Bạn hãy sử dụng trường này để lệnh cho Excel gửi e-mail khi giá cổ phiếu của Oracle phù hợp với tiêu chí của bạn (tức là khi giá cổ phiếu này giảm xuống dưới 30$). Bây giờ bạn hãy cập nhật dòng mã lệnh của bạn vào trong chương trình con Worksheet_Calculate. Nhắp đúp vào bảng tính Trade Stock trong bộ soạn thảo VB và nhập những thay đổi cho các mà lệnh của e-mail, phần mã này sẽ xuất trong phần trình bày 10, trang 41, dưới dạng màu đỏ.
Dòng mã lệnh thêm vào này nhằm phục vụ hai mục đích. Một là thêm dòng And control=”MAIL” vào câu lệnh If để nói cho Excel biết là nó chỉ được gửi e-mail trong trường hợp ô 15 có chứa từ MAIL. Câu lệnh vi phạm
Range(“15”).Select
ActiveCell.FormulaR1C1=”STOPMAIL”
sẽ thay đổi nội dung trong ô 15 thành “STOPMAIL”. Nếu giá cổ phiếu của công ty Oracle vẫn giữ dưới mức 15$ vào những lần cập nhật tiếp theo thì sẽ không gửi thêm thông báo qua e-mail nữa
LÀM VIỆC VỚI CƠ SỞ DỮ LIỆU
Mã lệnh được trình bày trên đây cũng có thể tạo ra thu điện tử dựa trên dữ liệu được nhập từ một cơ sở dữ liệu chứ không chỉ từ Web với một vài sửa đổi nhỏ. Trước hết, hãy liên kết một bảng tính Excel với một cơ sở dữ liệu Acces. Để tạo ra câu hỏi truy vấn cơ sở dữ liệu, hãy làm theo trình tự các thủ tục được một tả trong “Spreadsheet, Meet Database; Database, Meet Spreadsheet.”Bạn có thể liên kết Excel với rất nhiều dạng cơ sở dữ liệu khác nhau
Ngay khi được liên kết, hãy mở bảng tính Excel có chứa các câu hỏi truy vấn cơ sở dữ liệu ra. Bạn hãy bấm một lần nữa vào Tools, sau đó bấm vào nút Macro và tiếp theo là bấm vào nút Visual Basic Editor. Nhắp đúp vào bảng tính có chứa câu hỏi truy vấn cơ sở dữ liệu từ Project Screen (chỉ khi bạn đã thực hiện như đối với Câu hỏi Truy vấn trong Phần trình bày 7). Dòng mã lệnh như đã được mô tả ở trên cho các bảng tính đã được liên kết với các Website vẫn được giữ nguyên. Chỉ có vài thay đổi cần thiết là tiêu chí được sử dụng để sinh ra các thông báo và nội dung của chính các thông báo đó
Ví dụ: giả định rằng công ty của bạn đang theo dõi các mức độ đầu tư trong một cơ sở dữ liệu thời gian thực và bạn đã liên kết bảng tính Excel của bạn với cơ sở dữ liệu này. Giả định tiếp theo nữa là bạn muốn có thông báo khi số lượng của mặt hàng số 104 thấp hơn 100 (chỉ số đặt hàng tiếp). Để sửa đổi mã lệnh có từ trước, bạn hãy thay đổi định nghĩa về biến giá mà đã được sử dụng trong ví dụ Web Query với biến mới như sau: quantity 1 = Range (“H5”) & “.”Nếu số lượng của mặt hàng thứ 104 chỉ còn 89 thì bạn sẽ nhận được thông báo như sau: “The stock of Item Number 104 has fallen to 89” (Số lượng của Mặt hàng thứ 104 trong kho chỉ còn 89).
Giống như các câu hỏi truy vấn Web, bạn có thể giám sát nhiều biến khác nhau với nhiều câu lệnh If – Then khác nhau, và bạn cũng có thể giới hạn số lượng các thông báo theo các phương pháp đã được trình bày ở phần trên.
Để kích hoạt bộ giám sát cơ sở dữ liệu và hện thống gửi thư điện tử tự động, bạn hãy mở bảng tính Excel có chứa câu hỏi cơ sở dữ liệu và dòng lệnh VB. Bạn có thể đi ra khỏi văn phòng với niềm tin là bộ giám sát cơ sở dữ liệu đang liên tục làm việc vì lợi ích của bạn. Có rất nhiều cơ hội cho bạn sử dụng Excel. Với một chút sáng tạo, bạn có thể sửa đổi các câu hỏi truy vấn và các mã lệnh để tạo ra rất nhiều hệ thống tự động khác nhau.
{Sidebar]
Nếu bạn có một chủ đề công nghệ đặc biệt về cách hướng dẫn sử dụng mà bạn muốn Tạp chí Kế toán xem xét đề cập đến trong đợt này, hoặc bạn có một ứng dụng tắt mà bạn muốn chia xẻ với những nhà chuyên môn khác, hãy liên lạc với Biên tập viên chính Stanley Zarowin theo địa chỉ e-mail: zarowin@mindspring.com
(www.vncold.vn)
|