Tổng hợp hỏi đáp về excel (theo pcworld)

[skyonline]

Hỏi: Theo hướng dẫn của TGVT về cách dùng hàm đổi số sang chữ, tôi đã áp dụng được trong Excel (gõ =usd(address of cell of number)) nhưng trong Word thì không biết phải gõ hàm như thế nào. Đề nghị hướng dẫn.



Đáp: Có 2 hoạt động liên quan đến 1 hàm “user-defined”: xây dựng nó và dùng nó. Bạn có thể xây dựng hàm “user-defined” bằng macro (viết bằng ngôn ngữ VBA) hay bằng công nghệ COM Add-Ins (có thể viết bằng nhiều ngôn ngữ khác nhau như VB, VC++...). Về việc sử dụng hàm “user-defined”, có thể dùng (gọi) nó ở bất kỳ vị trí nào mà ở đó bạn gọi được hàm định sẵn. Thí dụ có thể gọi hàm “user-defined” trong các macro, các hàm xử lý sự kiện của các “User Form” hay các công thức được chèn trực tiếp vào tài liệu Word. Để chèn 1 công thức vào tài liệu Word, bạn đưa con trỏ về vị trí cần chèn, chọn menu Insert.Field, rồi nhập công thức mong muốn. Tuy nhiên, theo kết quả kiểm tra trên một số máy, chúng tôi nhận thấy các version Word từ trước tới nay chưa xử lý được hàm “user-defined” trong công thức nằm trong tài liệu Word. Đây là 1 hạn chế, hy vọng version tương lai sẽ khắc phục được.

 

[skyonline]

Hỏi: Tôi có tập tin Excel quan trọng nhưng do cần dùng chung nên phải để trên mạng nội bộ. Vì không muốn người khác sửa hoặc xóa đi phần dữ liệu đã nhập, nên tôi sử dụng chức năng Protect workbook để bảo vệ, nhưng khi đó chức năng Auto filter lại không thể sử dụng được. Mong được hướng dẫn.



Đáp: Có 2 mức độ bảo vệ dữ liệu cho 1 file:

1. bảo vệ ở mức ứng dụng, thí dụ như bạn dùng chức năng bảo vệ của các chương trình trong bộ Office để bảo vệ dữ liệu của chúng. Mức độ bảo vệ này có nhiều khuyết điểm:

- rất khó quản lý vì bạn cần rất nhiều password khác nhau cho các file dữ liệu khác nhau, hay cho từng thành phần trong cùng 1 file.

- không đủ mạnh vì người biết định dạng file có thể viết ứng dụng riêng truy xuất trực tiếp dữ liệu của file đó mà không cần dùng ứng dụng đã tạo ra nó.

- có nguy cơ làm một số chức năng của ứng dụng không hoạt động tốt, thí dụ chức năng Auto Filter mà bạn nói...

2. bảo vệ ở mức hệ thống, các HĐH hiện nay đều có cơ chế này như WinXP, Linux. Ở mức độ này, mỗi người dùng sẽ có một account riêng với một số thẩm quyền nhất định về 1 số tài nguyên nhất định trên máy. Trước khi truy xuất các tài nguyên này, bạn phải khai báo account của mình (username và password). Mức độ bảo vệ này thường được thực hiện khá chặt chẽ, người dùng rất khó “len lỏi” qua được.

Tóm lại, bạn nên dùng mức độ bảo vệ hệ thống cho các tập tin Excel để đảm bảo an toàn, tin cậy hơn và giúp chức năng Auto Filter cũng như các chức năng khác của ứng dụng không bị lỗi. Ở mức độ này, bạn sẽ khai báo mỗi người dùng 1 account, mỗi account có thẩm quyền nhất định (theo yêu cầu của bạn) về các file Excel cũng như các tài nguyên khác của hệ thống.

 

[skyonline]

Hỏi: Trong Excel có hàm nào cho phép xóa chuỗi kí tự như sau: ví dụ xóa hết các kí tự phía sau hay phía trước chữ m trong chuỗi “XXXXXmYYYYY”, các kí tự trước và sau chữ m thay đổi (không cố định).


Đáp:
Việc xóa hết các ký tự đi trước và sau chữ m trong 1 chuỗi nào đó tương đương với việc thay thế chuỗi đó bằng chữ m. Vậy nếu muốn thực hiện trên 1 cell duy nhất, cách tốt nhất là nhập lại chữ m cho cell đó. Còn nếu muốn thực hiện trên nhiều cell (trong 1 vùng được chọn), bạn có thể viết 1 macro để thực hiện điều này theo giải thuật lặp trên từng cell trong vùng chọn đó.

 

[skyonline]

Hỏi: Trong Excel, dùng công thức nào để tính chuyến xe khi biết rằng: nếu giờ khởi hành từ 7g đến 9 g thì ghi “Chuyến 1”, từ 9g01 đến 12g thì ghi “Chuyến 2”, ngoài ra ghi “Chuyến 3”.



Đáp: Vì yêu cầu của bạn không phổ biến nên Excel không cung cấp hàm nào để giải quyết trực tiếp yêu cầu, tuy nhiên bạn có thể viết 1 hàm “user-defined” (bằng macro hay bằng ngôn ngữ lập trình chính quy) để giải quyết yêu cầu riêng tư của mình. Đoạn code sau đây miêu tả hàm TinhChuyen() viết bằng macro VBA để giải quyết yêu cầu của bạn. Lưu ý rằng kiểu Date của VB chẳng qua là kiểu thực (Double), mỗi giá trị Date là 1 số thực gồm 2 phần: phần nguyên miêu tả số lượng ngày giữa ngày tương ứng và ngày gốc 30/12/1899, phần lẻ miêu tả tỉ lệ phần trăm của thời điểm tương ứng so với thời điểm 0g00”:
tham số rng miêu tả cell chứa giờ cần tính chuyến

Function TinhChuyen(rng As Range) As String

Dim tb1 As Double

Dim tk1 As Double

Dim tk2 As Double

thiết lập giờ bắt đầu chuyến 1

tb1 = CDbl(#7:30:00 AM#)

thiết lập giờ kết thúc chuyến 1

tk1 = CDbl(#9:00:00 AM#)

thiết lập giờ kết thúc chuyến 2

tk2 = CDbl(#12:00:00 PM#)

Dim time As Double

tìm thời điểm cần xác định chuyến

time = rng.Item(1, 1).Value

kiểm tra xem có nằm trong chuyến 1 ?

If (tb1 <= time) And (time <= tk1) Then

TinhChuyen = “Chuyen 1”

kiểm tra xem có nằm trong chuyến 2 ?

ElseIf (tk1 < time) And (time <= tk2) Then

TinhChuyen = “Chuyen 2”

Else

trường hợp còn lại là chuyến 3

TinhChuyen = “Chuyen 3”

End If

End Function

 

[skyonline]

Hỏi: Tôi muốn theo dõi kết quả học tập bằng bảng tính với các cột: môn, điểm miệng, 15, 1 tiết và thi. Có một vấn đề là làm sao để tính trung bình các số đã nhập trong cùng một ô. Xin hướng dẫn hoặc đề xuất thành lập bảng tính theo cách khác để tiện lợi hơn. Cách tính trung bình như sau: TB miệng + TB 15 + TB1 tiết*2 sau đó chia trung bình, lấy trung bình đó nhân 2 + điểm thi chia 3 ra trung bình học kì.



Đáp:

Có 2 vấn đề chính trong việc xây dựng bảng tính theo dõi điểm học tập các môn mà bạn yêu cầu:

1. Tạo cột điểm TB học kỳ của từng môn học, bạn chỉ cần nhập công thức tính theo các cột điểm khác, thí dụ, nếu cột A là tên môn, cột B là TB miệng, cột C là TB 15, cột D là TB tiết, cột E là điểm thi, cột F là điểm TB môn học thì bạn hãy nhập công thức cho cột F như sau:
=((B1+C1+D1*2)/4+E1)/3

Sau khi nhập và kiểm tra công thức cho môn học đầu tiên (thí dụ ở đây là hàng 1), bạn hãy copy công thức này rồi dán vào các hàng còn lại cho các môn học khác.

2. Theo yêu cầu của bạn, từng ô điểm của các cột B, C, D, E lại là giá trị trung bình của nhiều điểm trong suốt học kỳ. Về nguyên tắc, các cell này không thể chứa giá trị nhập vào mà phải là công thức tính trị TB của các giá trị nhập vào. Để dễ kiểm tra và giám sát các trị nhập vào, bạn có thể qui định một số cột trên cùng hàng của môn học sẽ chứa các điểm miệng, điểm 15, điểm 1 tiết, điểm thi riêng lẻ (thí dụ bạn dành 10 cột từ H đến Q để nhập điểm miệng, 10 cột kế từ R đến AA để nhập điểm 15...). Lúc này, cell B1 sẽ là công thức tính TB của 10 giá trị điểm miệng riêng lẻ từ H1:Q1 (=sum(H1:Q1)/10). Tuy nhiên cách giải quyết này cứng nhắc và không đáp ứng được yêu cầu vì bạn muốn nhập từ từ các điểm kiểm tra, mỗi lần có 1 điểm mới thì điểm TB phải được tính lại ngay lập tức. Để giải quyết vấn đề này, bạn cần viết 1 hàm “user-defined” đặc biệt, nhiệm vụ của nó là dò từng cell trong 1 dãy các cell qui định và chỉ tính TB của các cell có giá trị. Qui trình điển hình để tạo 1 hàm “user-defined” trong Excel như sau: chọn menu Tools.Macro.Visual Basic Editor để hiển thị cửa sổ VBE, chọn menu Insert.Module để tạo mới module code với tên mặc định là “Module1” rồi viết hàm DiemTB như sau:

Function DiemTB(rng As Range) As Double

Dim sum As Double

Dim count As Integer

sum = 0

count = 0

Duyệt từng cell trong Range

For Each c In rng

kiểm tra cell có dữ liệu không

If Len(c.Text) <> 0 Then

sum = sum + c.Value

count = count + 1

End If

Next

Tính TB nếu cần

if count <> 0 then

DiemTB = sum / count

endif

End Function

Sau khi đã tạo hàm user-defined như trên, bạn nhập công thức tính TB cho cell B1 (TB miệng) như sau: =DiemTB(H1:Q1). Các cell điểm khác trong bảng tính cũng được viết tương tự như cell B1.

 

[skyonline]

Hỏi: Bảng tính Excel chứa các hàng có nội dung nằm xen kẽ với các hàng trống một cách ngẫu nhiên, tôi muốn chỉ in ra giấy các hàng có nội dung nằm sát nhau (còn các hàng trống xen kẽ thì bỏ). Xin hướng dẫn.



Đáp:

Không có lệnh định sẵn nào của Excel giải quyết được yêu cầu đặc biệt của bạn. Do đó nếu muốn, bạn phải tự làm thủ công bằng tay hoặc viết 1 macro giải quyết yêu cầu riêng. Một trong nhiều cách viết macro khá đơn giản là dùng menu Tool.Macro.Record New Macro để ghi nhận lại trình tự các thao tác mà bạn thực hiện thủ công lần đầu, để những lần sau bạn chỉ cần kích hoạt macro, nó sẽ tự thực hiện lại đúng trình tự thao tác mà bạn đã làm y như lần đầu.

 

[skyonline]

Hỏi: Xin hướng dẫn đoạn code VBA Việt hóa các menu và hộp thoại trong Word 2002 và Excel 2002.



Đáp: Đoạn code VBA sau cho phép xóa option “Save” trong menu “File” của Excel rồi add lại option
“Luu file”:

Public Function MenuEdit()

Dim myCmd As Object

With CommandBars(“Worksheet menu bar”).Controls(“File”)

.Controls(“Save”).Delete

.Controls.Add(Type:=msoControlButton, Before:=1).Caption = “Luu file”

.Controls(“Luu file”).OnAction = “FileSave”

End With

các đoạn code khác để bớt/thêm menu/option mong muốn

End Function

Sub FileSave()

MsgBox “Ban vua chon option Luu file”

End Sub

Bạn có thể phát triển đoạn code trên để Việt hóa toàn bộ hệ thống menu cho trình Excel hay thêm mới menu nếu cần thiết.

 

[skyonline]

Hỏi: Tôi đã nhập thử đoạn code của hàm doiso được trình bày trong số báo tháng 8/2001 và chạy thử nó, tôi có 1 số thắc mắc sau:
- Chương trình này khi nhập số 0 thì không cho kết quả, nếu không nhập gì thì lại cho kết quả là "không".
- Làm thế nào để cài đặt cố định hàm doiso này vào Excel, Word để dùng cho mọi trường hợp mà không phải mở file có hàm doiso này.
- Điều mà tôi rất cần đó là làm thế nào để hàm doiso này ứng dụng được trong Word.



Đáp: Trước hết, chúng tôi xin lỗi do sơ xuất trong nhập liệu nên mã nguồn của hàm doiso bằng ngôn ngữ VB trong số báo T8/2001 (t.92) bị thiếu 1 lệnh. Bạn hãy thêm dòng lệnh "End If" vào trước lệnh "If chuc < 0 then Exit Do" (ở hàng thứ 4 từ dưới đếm lên của cột 2). Ngoài ra, khi nhập source code của hàm doiso, bạn phải xuống hàng trước khi viết lệnh sau mệnh đề Then của các lệnh If.

- Đúng như bạn nói, hàm doiso trong số báo T8/2001 chưa xử lý tốt các lỗi trong thông số nhập, nên nếu bạn truyền chuỗi "0" thì nó trả về chuỗi NULL và nếu bạn truyền chuỗi không miêu tả số nguyên hợp lệ, nó sẽ hoạt động sai và trả về kết quả không tiên đoán trước. Để khắc phục lỗi này, bạn nên dùng tham số kiểu Long thay vì kiểu String, thí dụ bạn nên hiệu chỉnh lại hàm doiso như sau :

Function doiso(ByVal val As Long) As String

Dim s As String

s = CStr(val)

'phần còn lại y như cũ

- Để có thể dùng hàm doiso trong các file Excel, bạn viết nó trong 1 module add-ins (thường có phần nới rộng *.xla) rồi "add" file add-ins vào Excel (bằng menu Tools.Add-ins). Tương tự để có thể dùng hàm doiso trong các file Word, bạn viết nó trong 1 module add-ins (thường có phần nới rộng *.dot) rồi "add" file add-ins vào Word (bằng menu Tools.Template and Add-ins).
- Để tránh phải viết 2 module add-ins khác nhau cho 2 ứng dụng Excel và Word, bạn có thể viết hàm doiso trong module COM-Addins, lúc này cả Excel và Word đều có thể dùng chung file COM-Addins này. Qui trình chi tiết để viết 1 module Com-Addins được trình bày trong CD MSDN của Microsoft.

 

[skyonline]

Hỏi: Xin hỏi cách truyền tham số và xử lý hàm do người dùng tự định nghĩa trong MS Excel. Ví dụ hàm Tichdecart(A1:A10), nếu định nghĩa hàm Tichdecart(Arg1 as long, Arg2 as long.... Arg10 as long) thì bất tiện và không linh hoạt (khó thay đổi số lượng phần tử).



Đáp:
Cách phổ biến nhất để tạo hàm do người dùng định nghĩa (user-defined function) trong Excel là viết hàm này bằng ngôn ngữ VBA. VBA là tập con của VB, nó cũng kiểm tra kiểu tham số như bao ngôn ngữ lập trình mạnh khác, trong đó mỗi hàm có thể có nhiều tham số, mỗi tham số có thể truyền theo trị hay tham khảo, mỗi tham số cũng có kiểu dữ liệu cụ thể. Kiểu của 1 tham số có thể là kiểu đơn giản như Bool, Integer, Long,... Kiểu tham số cũng có thể là kiểu đối tượng, thí dụ trong trường hợp bạn muốn truyền 1 tập hợp nhiều cell trong bản tính Excel, bạn có thể dùng kiểu "Range" để miêu tả tham số.

Thí dụ muốn tạo hàm MySum(Range) tính tổng các cell được miêu tả trong tham số, bạn có thể chạy Excel, tạo/mở file Excel của mình, chọn menu Tools.Macro.Visual Basic Editor để hiển thị cửa sổ VBE, chọn menu Insert.Module để tạo mới module code với tên mặc định là "Module1" rồi viết hàm MySum như sau:

' hàm MySum có tham số thuộc kiểu Range miêu tả 1 khối các cell

Public Function MySum(rng As Range) As Double

Dim sum As Double

sum = 0

' duyệt từng cell trong Range

For Each c In rng

sum = sum + c.Value

Next

MySum = sum

End Function

Tương tự, bạn có thể định nghĩa hàm TichDecard như sau:

' hàm TichDecard có tham số thuộc kiểu Range miêu tả 1 khối các cell

Public Function TichDecard(rng As Range) As Double

' thân của hàm TichDecard
...

End Function

 

[skyonline]

Hỏi: Tôi thường làm việc với bảng tính Excel, để giảm bớt khối lượng tính toán, tôi dùng VBA để tạo một số file *.xla, tuy nhiên tôi gặp khó khăn trong việc đưa các file này vào ứng dụng thực tế:
1. Tôi copy tất cả những tập tin *.xla vào C:/Users\Tra^`n Ba' Tha`nh\applications data\office\add-ins và thực hiện việc "add" vào trong file Excel, nhưng khi những người cùng mạng (nội bộ) copy bảng tính của tôi (có thực hiện lệnh tôi đã tạo) vào trong máy họ thì máy báo không nhận được file *.xla, và phải thực hiện lại việc "add". Làm sao để người dùng trong mạng không phải "add" lại?
2. Dùng chương trình gì để tạo help cho các file *.xla?
3. Khi add các file *.xla vào Excel, trong hộp thoại add-ins hiện lên tên file. Tôi muốn trong hộp thoại hiện tên khác và những người khác không thể chỉnh sửa được khi không được phép, tôi phải làm thế nào?



Đáp: 1. Excel (cũng như các ứng dụng trong bộ Office) chỉ cung cấp một số hữu hạn và xác định các hàm chức năng phổ dụng. Tuy nhiên nó cho phép người dùng tạo thêm các hàm chức năng phục vụ nhu cầu cá nhân thông qua nhiều kỹ thuật khác nhau như: viết hàm bằng macro VBA, viết hàm bằng ngôn ngữ lập trình VC++ rồi dịch thành thành phần COM Add-ins. Nếu bạn đã viết hàm bằng macro VBA, bạn có thể nhúng hàm đó vào file Excel cần dùng ở nhiều cấp độ: macro add-ins (các file *.xla), "All open workbooks" (hàm toàn cục cho mọi bảng tính được mở bởi trình Excel trên máy bạn), "This workbook" (hàm chỉ được thấy cục bộ trong bảng tính chứa nó). Để giúp người dùng khác dễ dàng dùng file Excel có gọi hàm tự tạo, bạn nên viết thẳng các hàm tự tạo thành những macro nằm trong chính file Excel đó (dùng cấp độ nhúng "This workbook"). Điều này gây bất tiện cho người tạo macro, gây ra sự "nhân bản macro" trên nhiều file Excel cùng dùng macro đó, nhưng sẽ làm chúng "trong suốt" với người dùng. Trong trường hợp bạn viết các hàm tự tạo trong các file *.xla, người dùng Excel buộc phải "add" tường minh chúng vào ứng dụng Excel của họ (trong lần đầu tiên họ truy xuất file Excel có dùng hàm tự tạo). Nếu bạn muốn thực hiện tự động việc "add" các file add-ins vào Excel, bạn có thể viết 1 đoạn code VBA nhỏ trong thủ tục Autpen của mỗi file Excel có dùng hàm tự tạo, nhiệm vụ của đoạn code này là "add" tự động các file add-ins cần dùng. Thủ tục Autpen sẽ tự động chạy mỗi khi người dùng mở file Excel tương ứng. Cách viết macro này đơn giản và an toàn nhất là dùng menu Tools.Macro.Record New Macro và thao tác thủ công việc "add" các file add-ins để Excel chuyển thành các lệnh VBA tương ứng.

2. Bạn có thể dùng tiện ích "Windows Help WorkShop" để soạn file Help theo công nghệ cũ, công nghệ "Windows Help". Tiện ích này có sẵn trong bộ Visual Studio 2000 hay cũ hơn. Bạn cũng có thể dùng tiện ích "HTML Help WorkShop" để soạn file Help theo công nghệ mới, công nghệ "HTML Help". Tiện ích này có sẵn trong bộ Visual Studio .Net hay trên website của Microsoft. Để dùng 1 trong 2 tiện ích trên, bạn cài đặt nó vào máy, chạy và đọc phần Help online của nó, nói chung các ứng dụng của Microsoft có tính thân thiện và dễ dùng.

3. Mỗi file add-ins có nhiều thông tin, nếu nó chứa field "Title" thì khi cửa sổ "add-ins" hiển thị, hệ thống sẽ lấy chuỗi "title" để hiển thị trong danh sách thay vì là tên file add-ins. Trong trường hợp đã xây dựng rồi add-ins file và chỉ muốn hiệu chỉnh lại chuỗi hiển thị của add-ins, bạn có thể dùng Windows Explorer, duyệt tìm file add-ins, hiển thị cửa sổ Properties của file, chọn tab Summary, nhập tên gợi nhớ vào textbox "Title".

Nếu cần bảo vệ file add-ins không cho người khác xem và chỉnh sửa code, hãy chạy Excel, mở file *.xla, mở cửa sổ code của nó và chọn menu Tools.VBAProject Properties trong cửa sổ code "Visual Basic Editor", chọn tab Protection, đánh dấu chọn vào checkbox "Lock project for Viewing", nhập password vào rồi ấn OK. Lưu ý phải nhớ password cẩn thận để có thể hiệu chỉnh add-ins sau này (nên ghi vào sổ tay vì con người thường rất mau quên).

 

[skyonline]

Hỏi: Xin hướng dẫn cách bảo mật file Excel để chỉ cho phép Save chứ không cho phép Save As và Copy. Trang web nào cung cấp những thông tin về macro trong Excel?



Đáp: Bạn có thể đọc các bài báo (dạng web) trong CD MSDN để biết chi tiết và cụ thể các thông tin về Excel. Ngoài ra bạn cũng có thể đọc phần trợ giúp của chương trình Excel hay vào Internet để tìm kiếm và đọc thêm những trang web nói về Excel. Nếu muốn an toàn và bảo mật thông tin cho các file, bạn nên dùng HĐH có khả năng này như WinXP, Linux... Tuy nhiên, hầu hết các HĐH không có chế độ bảo vệ quá "kỳ cục" như bạn yêu cầu: làm sao chỉ cho đọc file mà không cho Copy! làm sao cho Save mà không được Save As! Thí dụ Linux cung cấp 3 quyền sau cho từng file:

• Quyền "Read" cho phép người dùng đọc thông tin trên file vào bộ nhớ, như vậy họ có quyền xem, copy hay save as sang file khác.

• Quyền "Write" cho phép người dùng được hiệu chỉnh nội dung file, xóa file.

• Quyền "Excute" cho phép người dùng chạy (thực thi) file.

Như vậy cơ chế bảo vệ của Linux không thể đáp ứng yêu cầu cá nhân của bạn (dĩ nhiên các HĐH khác cũng tương tự).

 

[skyonline]

Hỏi: Làm cách nào tìm ra nhanh nhất các số trùng nhau trong 1 hoặc nhiều cột Excel đồng thời chỉ ra các số đó nằm ở hàng nào?



Đáp: Excel (hay 1 ứng dụng khác trong bộ Microsoft Office) chỉ cung cấp một số chức năng hữu hạn và phổ dụng. Đối với những yêu cầu đặc thù thì Excel chỉ hỗ trợ người dùng giải quyết nó bằng cách cung cấp ngôn ngữ kịch bản (VBA) và cơ chế để người dùng tạo ra những module chương trình giải quyết đúng yêu cầu đặc thù của mình. Tóm lại để tìm ra các số trùng nhau trong 1 hay nhiều cột Excel, bạn có thể dùng VBA viết 1 macro (1 thủ tục VBA) duyệt tìm từng cell, so sánh chúng với nhau và hiển thị màu khác nhau cho từng nhóm cell có giá trị giống nhau.

 

[skyonline]

Hỏi: Có phương thức nào trong VBA có thể lần lượt mở, copy 1 cell rồi đóng lại cho tất cả các file Excel có trong 1 thư mục được chỉ định không? Số file cần mở khoảng 2000 file, có tên dạng MA0001 - MA2000. Vị trí cell cần copy trong từng file là cố định (ví dụ là D7). Tôi đã dùng thử lệnh For nhưng thường thì chỉ có thể mở 500 file là bị báo thiếu bộ nhớ (RAM128MB). Có cách nào mở ra, đóng lại từng file để giải phóng bộ nhớ không?



Đáp: Bạn có thể viết 1 macro để thực hiện yêu cầu riêng, đoạn code sau đây sẽ lặp 2000 lần, mỗi lần mở 1 file, copy cell D7 của nó, đóng file đó lại rồi paste kết quả vào cell qui định ở worksheet hiện hành:
Sub CopyCell()
' CopyCell Macro
' khai báo các biến cần dùng
Dim Path As String
Dim Dir As String
Dim i As Integer
Dim rg As String
' thiết lập thư mục
Dir = "c:\data\"
' lặp 2000 lần
For i = 1 To 2000
' tạo pathname truy xuất file thứ i
Path = Dir & "ma" & Format(i, "0000") & ".xls"
' mở file
Workbooks.Open Filename:=Path
' chọn cell d7
Range("d7").Select
' copy nó vào clipboard
Selection.Copy
' đóng file
ActiveWorkbook.Close
' thiết lập cell cần dán kết quả
rg = "A" & i
Range(rg).Select
' dán kết quả
ActiveSheet.Paste
Next i
End Sub
Lưu ý rằng trong mỗi vòng lặp ta đều thực hiện đóng file sau khi đã truy xuất. Nếu đoạn chương trình trên bị dừng giữa chừng và báo lỗi thiếu bộ nhớ thì có thể kết luận rằng hoạt động đóng file của Excel có lỗi chứ không phải là đoạn code.

 

[skyonline]

Hỏi: 1. Xin hỏi làm cách nào để đưa một hàm tự tạo vào thư viện hàm Excel.
2. Làm cách nào để xuất dữ liệu dạng text trong môi trường Acad14 sang dạng Text trong môi trường Excel.



Đáp: 1. Có nhiều cách để đưa 1 hàm tự tạo vào thư viện hàm của Excel, trong đó cách viết hàm dạng VBA, lưu hàm trong file add-ins rồi "add" file add-ins này vào môi trường Excel của bạn. Qui trình điển hình để viết và dùng hàm tự tạo bằng VBA như sau:

• Chạy Excel, tạo 1 Workbook mới, chọn menu Tools.Macro.Visual Basic Editor để mở cửa sổ viết macro cho Workbook hiện hành, ấn kép chuột vào phần tử gốc "VBAProject" trong cửa sổ Project, chọn mục Insert.Module để tạo 1 module phần mềm mới (tên mặc định là Module1), rồi viết code cho từng hàm tự tạo theo yêu cầu trong module mới này.

• Dịch project add-ins và debug nó bằng cách chọn menu Debug.Compile... trong cửa sổ soạn code "Visual Basic Editor".

• Nếu cần bảo vệ project add-ins không cho người khác xem và chỉnh sửa code, hãy chọn menu Tools.VBAProject Properties trong cửa sổ code "Visual Basic Editor", chọn tab Protection, đánh dấu chọn vào checkbox "Lock project for Viewing", nhập password vào rồi ấn OK. Lưu ý phải cẩn thận nhớ password để có thể hiệu chỉnh add-ins sau này (nên ghi vào sổ tay vì trí nhớ của con người thường rất ngắn hạn).

• Sau khi viết xong các hàm tự tạo, chọn menu File.Close and Return to Microsoft Excel để trở về cửa sổ Excel.

• Chọn menu File.Properties để hiển thị cửa sổ thuộc tính, chọn tab Summary, nhập chuỗi nhận dạng file add-ins, tức tên gợi nhớ của add-ins mà sẽ được hiển thị trong hộp thoại add-ins của người dùng.

• Dùng menu File.Save As để lưu add-ins lên file *.xla.

• Sau khi đã tạo ra file add-ins, bạn chọn menu Tools.Add-ins để hiển thị cửa sổ add-ins, chọn button Browse rồi duyệt file add-ins vừa tạo để "add" nó vào trong Excel.

Từ đây, mỗi khi bạn chạy Excel, file add-ins của bạn sẽ tự động được nạp vào Excel để bạn có thể gọi bất kỳ hàm nào đã được viết trong file add-ins đó.

2. Excel cho phép bạn "import" dữ liệu text từ bất kỳ ứng dụng khác miễn sao file dữ liệu text có định dạng qui định. Định dạng thường dùng là file text phải chứa n hàng dữ liệu, mỗi hàng dữ liệu gồm m thành phần dữ liệu được ngăn cách bởi dấu ngăn nào đó (thí dụ dấu phẩy, TAB...). Để "import" 1 file vào Excel, bạn chạy Excel, chọn menu File.Open như bình thường, duyệt và xác định file cần import, trả lời một số thông số của qui trình wizard...

 

[skyonline]

Hỏi: Lập trình VBA trong Excel, tôi muốn khi gõ 1 ký số vào 1 cell (thí dụ là 1, 2 ...) thì số đó sẽ được hiển thị theo màu tương ứng. Đoạn code như sau:
Set Worksheets("Sheet1").Range("A1:A30") as pt
Private sub Worksheets_Activate ()
Select case pt
Case pt.value = 1
pt.font.color = RGB(0, 0, 255)
Case pt.value = 2
pt.font.color = RGB(0, 255, 255)
...
End Select
End sub
Chương trình không làm việc đúng, xin hỏi sai ở đâu?



Đáp: Để giải quyết yêu cầu của bạn, trước hết bạn phải định nghĩa 10 giá trị màu cho 10 ký số khác nhau, sau đó bạn viết thủ tục xử lý sự kiện xảy ra sau khi nhập liệu xong 1 cell, thủ tục xử lý sự kiện này không phải là Worksheet_Activate() như bạn viết, mà là Worksheet_Change(). Để viết thủ tục này, bạn hãy chọn menu Tools.macro.Visual Basic Editor. Trong cửa sổ VBAProject, ấn kép chuột vào tên Sheet cần xử lý (thí dụ Sheet1), chọn mục "Worksheet" trong listbox các đối tượng, chọn mục "Change" trong listbox các sự kiện kết hợp, VB sẽ tự tạo hàm xử lý sự kiện tương ứng. Bạn viết code vào thân thủ tục này như sau:

Private Sub Worksheet_Change(ByVal Target As Range)
Color0 = RGB(0, 0, 255)
Color1 = RGB(0, 255, 0)
Color2 = RGB(255, 0, 0)
Color3 = RGB(255, 0, 255)
Color4 = RGB(0, 255, 255)
Color5 = RGB(255, 255, 0)
Color6 = RGB(128, 128, 128)
Color7 = RGB(128, 0, 128)
Color8 = RGB(0, 128, 255)
Color9 = RGB(255, 128, 0)
Select Case Target.Value
Case 0: Target.Font.Color = Color0
Case 1: Target.Font.Color = Color1
Case 2: Target.Font.Color = Color2
Case 3: Target.Font.Color = Color3
Case 4: Target.Font.Color = Color4
Case 5: Target.Font.Color = Color5
Case 6: Target.Font.Color = Color6
Case 7: Target.Font.Color = Color7
Case 8: Target.Font.Color = Color8
Case 9: Target.Font.Color = Color9
End Select
End Sub

Sau khi đã viết xong thủ tục, bạn hãy quay về Worksheet Excel và thử nghiệm kết quả, mỗi lần bạn nhập các số từ 0 tới 9 vào 1 cell nào đó rồi enter thì ký số mới nhập vào cell đó sẽ được hiển thị theo màu qui định với giá trị của nó.

 

[skyonline]

Hỏi: Trong Excel, thực hiện liên kết thông tin Sheet1 với Sheet2 (dùng Paste Link), khi xóa hay thêm bất kỳ thông tin gì trong "vùng copy" của Sheet1 thì thông tin trong Sheet2 cũng thay đổi theo. Nhưng nếu thêm 1 dòng hay 1 cột trong "vùng copy" của Sheet1 thì Sheet2 không thay đổi theo. Hỏi có cách nào để thay đổi Sheet2 tự động như đã trình bày không?



Đáp:

Không. Vì kích thước và vị trí của vùng link đã được xác định tĩnh tại thời điểm copy nên chỉ có những thay đổi về nội dung của bản gốc mới được cập nhật sang vùng link, còn các thay đổi về kích thước (số lượng hàng/cột) sẽ không tự động cập nhật sang vùng link. Nếu muốn, bạn phải cập nhật lại vùng link thủ công.

 

[skyonline]

Hỏi: Xin hỏi có thể tạo Autotext trên Excel giống như Word không?



Đáp:

Do chức năng của Excel tập trung vào việc xử lý bảng tính nên nó không hỗ trợ nhiều chức năng biên tập tài liệu như Word. Thí dụ, Excel không hỗ trợ chức năng AutoText như Word. Do đó bạn cần kết hợp 2 ứng dụng Excel và Word để biên tập tài liệu chứa các bảng tính phức tạp và in ấn chúng.

 

[skyonline]

Hỏi:

Trong Excel, kết quả của một field calculated (sau đây gọi là FC) có phần thập phân rất dài. Tôi muốn con số này được làm tròn theo nguyên tắc thông thường (phần đơn vị thêm 1 nếu phần thập phân >=0,5) rối lấy kết quả (đã có dạng số nguyên thật sự) để làm phần tính toán ở field khác. Tôi đã làm nhiều cách mà không được như ý, ví dụ "format" field đó thì chỉ được kết quả làm tròn về hình thức (không nhìn thấy phần thập phân) còn về mặt tính toán thì chương trình vẫn không gạt phần thập phân. Tôi đã phải làm kiểu "thủ công" như sau (sử dụng kết hợp function Iif và Fix)

Iif((FC-Fix(FC))<0,5,Fix(FC),Fix(FC)+1)

Xin hỏi có cách nào hay hơn?



Đáp: Đúng như bạn nói, việc "format" 1 field Excel chỉ ảnh hưởng đến hình thức hiển thị nội dung của cell đó chứ không biến đổi tường minh giá trị của cell đó. Như vậy, nếu muốn dùng giá trị sau khi làm tròn của cell nào đó và dùng trong công thức của cell khác, bạn phải viết tường minh biểu thức miêu tả yêu cầu của bạn. Hàm Iif mà bạn trình bày là 1 cách để làm tròn giá trị thực FC nhưng dài dòng và không trong sáng bằng cách dùng hàm Round(FC).

 

[skyonline]

Hỏi: Tôi muốn viết đoạn code bằng VBA nhúng vào Excel (macro) để hiển thị hộp thoại trợ giúp (listbox) ngay tại cell đang chọn (giống chế độ hỗ trợ cú pháp trong môi trường lập trình VB, VC++). Tuy nhiên tôi không biết cách xác định vị trí của cell đang chọn.



Đáp:

Bạn có 2 cách chính để hiển thị listbox ngay tại cell đang được chọn:

1. Bạn để listbox vào một UserForm rồi hiển thị UserForm lên vị trí mong muốn.

2. Bạn "add" trực tiếp listbox vào Worksheet Excel tại vị trí mong muốn.

Tùy theo cách nào được dùng mà công thức tính vị trí (x,y) của đỉnh trên trái listbox sẽ hơi khác nhau. Sau đây là macro chứa đoạn code VBA hiển thị listbox theo cách 2:

'Macro add & hiển thị listbox trong Worksheet

Sub ListBoxDisp()

Dim x As Integer

Dim y As Integer

' Tính tọa độ x vị trí dưới phải của cell hiện hành

x = ActiveCell.Offset(1, 1).Left

' Tính tọa độ y vị trí dưới phải của cell hiện hành

y = ActiveCell.Offset(1, 1).Top

' Add 1 listbox vào Worksheet hiện hành tại vị trí x,y

Set Box = ActiveSheet.ListBoxes.Add(x, y, 100, 50)

' Thiết lập nội dung của listbox

Box.AddItem "Mục 1"

Box.AddItem "Mục 2"

Box.AddItem "Mục 3"

End Sub

Còn sau đây là macro chứa đoạn code VBA hiển thị listbox theo cách 1 (trong UserForm):

'Macro hiển thị UserForm chứa listbox lên trên Worksheet

Sub UserFormDisp()

Dim x As Integer

Dim y As Integer

' Tính tọa độ x vị trí dưới phải của cell hiện hành

x = ActiveCell.Offset(1, 1).Left

' Tính tọa độ y vị trí dưới phải của cell hiện hành

y = ActiveCell.Offset(1, 1).Top

' Đổi vị trí tương đối x,y thành vị trí tuyệt đối so với vị trí trên trái màn hình

...

UserForm1.StartUpPosition = 0

' Thiết lập nội dung của listbox

UserForm1.ListBox1.AddItem "Muc 1"

UserForm1.ListBox1.AddItem "Muc 2"

UserForm1.ListBox1.AddItem "Muc 3"

' Thiết lập lại vị trí và kích thước của UserForm

UserForm1.Move x, y, 100, 50

' Hiển thị UserForm

UserForm1.Show

End Sub

Lưu ý trong macro UserFormDisp, lúc đầu ta cũng tính tọa độ (x,y) của điểm dưới phải của cell hiện hành để làm đỉnh trên trái của UserForm, nhưng tọa độ này là tọa độ tương đối so với điểm trên trái của cell đầu tiên trong Worksheet (cell A1), khi người dùng cuộn dọc hay cuộn ngang bảng tính thì cell A1 của bảng tính có thể được hiển thị ở đâu đó trên màn hình (hay ở ngoài màn hình và không thể thấy được). Do đó ta cần phải chuyển đổi tọa độ (x,y) này về dạng tuyệt đối so với điểm trên trái vật lý của màn hình (0,0) trước khi dùng nó để định vị UserForm vì UserForm là đối tượng độc lập với bảng tính, tọa độ của nó là tọa độ tuyệt đối so với điểm trên trái của màn hình máy tính.

 

[skyonline]

Hỏi:

Trong Excel, khi dùng các hàm address(), column(), Row() để lấy địa chỉ cell, làm sao để Excel hiểu đây là "địa chỉ cell" chứa dữ liệu tham chiếu trong công thức?

Ví du: tại cell C3 tôi cần công thức để lấy giá trị 900 của cell phía trên (tức cell C2), nếu nhập công thức sau:

=ADDRESS(ROW()-1,COLUMN())

thì nội dung hiển thị là chuỗi "$C$2" chứ không phải giá trị cần lấy là 900 như mong muốn.



Đáp: Bạn có thể dùng hàm "Indirect" để truy xuất nội dung của 1 cell bất kỳ nếu biết được địa chỉ dạng text của cell đó. Cụ thể trong thí dụ của bạn, trong cell C3 thay vì nhập công thức "=Address(row()-1,column())", bạn hãy nhập công thức "=Indirect(Address(row()-1,column()))", lúc này bạn sẽ thấy cell C3 hiển thị giá trị 900 (của cell C2) như mong muốn.