van de chung excel

Hà Nội, ngày 10 tháng 1 năm 2007

http://www.thegioiebook.com/

Biểu đồ “động đậy” trong Excel - 12/11/2004 13h:13

Bạn có thấy một biểu đồ chuyển động trong Excel chưa? Với chương trình Viual Basic tự tạo đơn giản, bạn có thể làm cho biểu đồ của mình nhúc nhích trông thật sinh động. Giả sử, bạn có bảng số liệu với cột A chứa giá trị biến số X (từ A4:A19).

Giá trị của biến X sau luôn bằng giá trị của biến X trước nó cộng với trị quy định ở ô A1. Bạn cho giá trị ban đầu của biến X là 0 (ô A4), công thức cho ô A5 = A4 + $A$1. Tiếp đó bạn chọn một khoảng (trong trường hợp này là A5:A19) rồi nhấn Ctrl + D để copy công thức từ ô A5 xuống các ô còn lại. Cột B chứa giá trị hàm số Y=SIN(X). Sau đó bạn vào Insert\ Chartđể vẽ biểu đồ cho vùng giá trị mà bạn đã tạo. (Bạn nên dùng Chart Type là XY Scatter).

Để cho biểu đồ của mình nhúc nhích, bạn phải viết một hàm để thay đổi giá trị của ô A1. Khi đó giá trị của bảng số liệu sẽ thay đổi và làm cho biểu đồ cũng thay đổi theo. Bạn nhấn Alt +F11 để hiện ra cửa sổ Visual Basic Editor. Trong của sổ Visual Basic, nhấn F7 để xuất hiện cửa sổ Code. Viết một hàm nhưsau:

Sub BieuDoDongDay()Range (“A1”) = 0For i =1 to 150Range (“A1”) = Range (“A1”) + 0.035Next iRange (“A1”) = 0End Sub

Trở lại cửa sổ của Excel, nhấn Alt + F8 để mở cửa sổ Macro, rồi nhấn Run để thực thi chươngtrình. Bạn có thể đặt phím tắt cho hàm vừa tạo bằng cách nhấn Options. Vậy là xong, bạn có thể chiêm ngưỡng tác phẩm của mình rồi đó.

Thủ thuật với Microsoft Excel - 27/12/2004 13h:54

Mở tệp tin thường dùng

Phải thao tác thường xuyên trên một tệp tin (ví dụ bảng quyết toán), bạn có thể chỉ định Excel để chương trình mở tệp tin này (ví dụ quyettoan.xls) mỗi khi chương trình khởi động. Để thực hiện điều này, chỉ cần lưu tệp tin (File > Save As) vào thưmục XLStart nằm trong \Programfiles\Microsoft Office\Office.

* Chèn nhiều dòng trong một ô (cell)

Trong quá trình nhập dữ liệu, ở chế độ mặc định mỗi lần bạn nhấn Enter thì con chỏ chuột sẽ chọn ô (cell) ngay dưới ô bạn đang thao tác. Chính vì vậy đối với những người “mới vào nghề”, rất khó có thể nhập dữ liệu nhiều dòng trong cùng một ô. Thủ thuật hết sức đơn giản, bạn chỉ việc nhấn tổ hợp phím Alt + Enter.

* Thêm màu cho bảng tính

Để thêm màu hoặc ảnh nền cho bảng tính, bạn chọn Format > Sheet > Background. Tiếp đến, tìmtới ảnh nền (hoặc font màu) bạn thích và nhấn Open.

* “Đóng băng” dòng tiêu đề

Trong mỗi bảng tính thường có dòng tiêu đề cố định vị trí cho từng cột (column). Nếu bạn nhập dữ liệu trong nhiều dòng và tràn quá màn hình, công việc nhập dữ liệu sẽ rất khó khăn vì dòng tiêu đề “trôi” mất. Để “đóng băng” dòng tiêu đề, nhấn chuột chọn một ô ngay dưới dòng tiêu đề; chọn Window > Freeze Panes. Giờ bạn có thể thoải mái nhập dữ liệu với thanh tiêu đề luôn hiển thị ở phía trên bảng tính..

Tạo công cụ học từ vựng trong Excel

Có bao giờ các bạn nghĩ tới việc tạo công cụ học từ vựng trong Excel giống nhưcác chươngtrình học tiếng Anh không?

Đầu tiên bạn hãy tạo một file Excel mới và đặt tên là Timer (bạn có thể đặt tên khác). Bạn xóa hết các sheet, chỉ để lại một sheet và đặt tên là Data. Khối dữ liệu của chúng ta sẽ có 2 cột: Cột một làtựa đề, cột hai là nội dung. Chú ý là các hàng dữ liệu phải liên tục nhau.

Tiếp theo, chúng ta tạo một module và đặt tên là ModuleTimer (Hình 1) và nhập đoạn code 1. Ở đây chúng ta dùng hai hàm API là SetTimer và KillTimer.

Tạo một form với tên là frmMain với thuộc tính ShowModal là False nhưhình 3 và thêm các đối tượng sau:

TextBox txtTopic với thuộc tính WordWrap là True

- TextBox txtDescriptions với thuộc tính WordWrap là True

- 4 nút lệnh với tên lần lượt: cmdStart, cmdStop, cmdSetTime, cmdClose, với thuộc tính Caption lần lượt: Bắt đầu học, Ngừng học, Định thời gian, Đóng.(xem hình 2)

Và bây giờ bạn hãy nhập đoạn code 2 cho form frmMain.

Bước cuối cùng, bạn hãy trở về màn hình soạn thảo Excel.

Cho hiện thanh công cụ Visual Basic (View->Toolbars->Control Toolbox), chọn Command Button (Hình 3) và đặt lên sheet Data (nhưhình 4), đặt tên là cmdHoc với thuộc tính Caption làHọc. Sau đó nhấn đúp vào nút lệnh để nhập đoạn mã sau:

Private Sub cmdHoc_Click()

frmMain.Show

End Sub

Bây giờ bạn hãy trở về màn hình soạn thảo Excel và tắt chế độ Design (nhấn vào biểu tượng thước Ê ke và cây viết), và hãy thử nhấn vào nút lệnh vừa tạo xem sao. Một công cụ học từ vựng thật đơn

giản, phải không các bạn.

Hy vọng rằng bài viết này hữu ích cho các bạn.

CODE 1Public Declare Function SetTimerLib "user32"( ByVal HWnd AsLong, ByVal nIDEvent As Long,ByVal uElapse As Long, ByVallpTimerFunc As Long) As LongPublic Declare Function KillTimerLib "user32" ( ByVal HWnd AsLong, ByVal nIDEvent As Long) AsLongPublic TimerID As LongPublic TimerSeconds As SinglePublic BLDefaul As BooleanPublic StrTopic As StringPublic StrDes As StringPublic IntCount As IntegerPublic BLHaveStartTimer AsBooleanSub StartTimer()If BLDefaul = False Then

TimerSeconds = 3 ' Mặc định là 1giâyEnd IfTimerID = SetTimer(0&, 0&,

TimerSeconds * 1000&, AddressOfTimerProc)End SubSub SetTime()Dim VTime As SingleDim VAns As StringBLDefaul = FalseVAns = InputBox("Xin nhập vào

thời gian (giây) cho timer ", "Định thời gian") If Len(VAns) = 0 ThenBLDefaul = False

ElseVTime = CSng(VAns)TimerSeconds = VTimeBLDefaul = True

MsgBox "Dữ liệu của bạn không có!", vbOKOnly, "Công cụ học từ vựng"

Exit SubEnd IfElse

IntCount = IntCount + 1End IffrmMain.txtTopic.Text = StrTopicfrmMain.txtDescriptions.Text =StrDesDoEventsExit SubThongbao1:Call EndTimer

End Sub

CODE 2Private Sub cmdClose_Click()If BLHaveStartTimer = True ThenCall cmdStop_Click

End IfEnd

End Sub' Nhằm bảo đảm nếu đã gọi Timer rồi thì sẽ không gọi nữaPrivate Sub cmdSetTime_Click()Call SetTimeCall cmdStop_ClickCall cmdStart_Click

End SubPrivate Sub cmdStart_Click()If BLHaveStartTimer = False Then

Call StartTimerBLHaveStartTimer = True

End IfEnd SubPrivate Sub cmdStop_Click()

Call EndTimer

End IfEnd SubSub EndTimer()On Error Resume NextKillTimer 0&, TimerID

End SubSub TimerProc(ByVal HWnd AsLong, ByVal uMsg As Long, ByValnIDEvent As Long, ByVal dwTimerAs Long)'' The procedure is called byWindows. Put your' timer-related code here.'On Error GoTo Thongbao1If IntCount = 0 Then IntCount = 2

BLHaveStartTimer = FalseEnd SubPrivate Sub UserForm_

QueryClose(Cancel As Integer,CloseMode As Integer)

If CloseMode = vbFormControlMenuThen

Cancel = TrueMsgBox "Xin bạn đóng bằng nút

lệnh ĐÓNG!", vbOKOnly, "Công cụ học từ vựng"

End IfEnd Sub

StrTopic = HÃY KIỂM TRA LẠI ĐĨA MỀM

Application.Workbooks("Timer").Sheets("Data").Cells(IntCount, 1)StrDes =Application.Workbooks("Timer").Sheets("Data").Cells(IntCount, 2)If Len(Trim(StrTopic)) = 0 ThenIntCount = 2StrTopic =

Application.Workbooks("Timer").Sheets("Data").Cells(IntCount, 1)StrDes = Application.

Workbooks("Timer").Sheets("Data").Cells(IntCount, 2)

If Len(Trim(StrTopic)) = 0 ThenBLHaveStartTimer = FalseCall EndTimer

Một bạn đọc tự giới thiệu "nguyên làchuyên gia nghiên cứu đĩa từ" đã cómột số lời khuyên tốt về các đĩa mềm khởi động khẩn cấp và các đĩa cứu nguy của Windows và các chươngtrình khác: " Đừng quá tin là chúngcòn tốt". Đĩa mềm để lâu ngày có thể gây hỏng hóc các sector. Muốn xác nhận chúng vẫn còn làm việc, bạn phải kiểm tra chúng vài tháng một lần - bằng cách khởi động PC từ đĩa mềm đó, hoặc đánh giá bằng công cụ quét đĩa: Trong Windows 98 và Me, bạn chọn Start.Programs.Accessories.SystemTools.ScanDisk. Trong Windows2000 và XP, chọn Start.My Computer, nhấn phải đĩa mềm đó, chọn Properties, và nhấn Tools.Check Now.

Lê Văn Duyệt

Tính thuế thu nhập bằng Excel - 22/1/2005 9h:26

Cuối năm là thời điểm cần quyết toán thuế thu nhập cá nhân. Đây là vấn đề không "nhẹ nhàng" chút nào, nhất là khi qui mô công ty của bạn không nhỏ. Tuy nhiên bạn đừng lo, bạn có thể lập trình "bắt" Excel xử lý giúp "gánh nặng" này.

Công việc chuẩn bị

- PC cài HĐH Windows 2000 hoặc XP, Microsoft Excel XP hoặc 2003

- Phụ lục số 1 và số 2, kèm theo Thông tưsố 81/2004/ TT-BTC ngày 13/8/2004 của Bộ Tài chính.

- Lên kế hoạch tạo các hàm sau:

• PITLC: Hàm tính thuế thu nhập cá nhân dành cho công dân Việt Nam và các cá nhân khác định cưtại Việt Nam.

• PITFR: Hàm tính thuế thu nhập cá nhân người nước ngoài cưtrú tại Việt Nam và công dân Việt Nam laođộng, công tác ở nước ngoài.

• NET2GROSSLC: Hàm qui đổi thu nhập sau thuế (net) sang thu nhập trước thuế (gross) dành chocông dân Việt Nam và các cá nhân khác định cưtại Việt Nam.

• NET2GROSSFR: Hàm quiđổi thu nhập sau thuế (net) sang thu nhập trước thuế (gross) dành chongười nước ngoài cưtrú tại Việt Nam và công dân Việt Nam lao động, công tác ở nước ngoài.

Lưu ý: Tên hàm có thể thay đổi cho phù hợp với yêu cầu của bạn.

TẠO HÀM

1. Mở bảng tính (workbook) mới

2. Nhấn tổ hợp phím Alt+F11 để khởi động microsoft Visual Basic

3. Nhấn vào bảng tính của bạn tại cửa sổ VBA project. Ví dụ: VBA project (book1)

4. Chọn Insert.Module

5. Nhấn đúp vào module mới tạo ra và nhập vào các đoạn mã sau.

Function pitlc(gross_local)

'Personal Income Tax for Local Vietnamese Citizen

If (gross_local > 0) And (gross_local <= 5000000) Then

pitlc = 0

ElseIf (gross_local > 5000000) And (gross_local <= 15000000) Then

pitlc = (gross_local - 5000000) * 0.1


pitlc = 1000000 + ((gross_local - 15000000) * 0.2)



ElseIf (gross_local > 40000000) Then


End If

End Function

Function pitfr(gross_foreign)

'Personal Income Tax for Resident Foreigner in Vietnamese

If (gross_foreign > 0) And (gross_foreign <= 8000000) Then

pitfr = 0

ElseIf (gross_foreign > 8000000) And (gross_foreign <= 20000000) Then

pitfr = (gross_foreign - 8000000) * 0.1


pitfr = 1200000 + ((gross_foreign - 20000000) * 0.2)



ElseIf (gross_foreign > 80000000) Then


End If

End Function

Function net2grosslc(net_local)

'Local Net Salary to be gross-up to Gross Salary

If (net_local > 0) And (net_local <= 5000000) Then

net2grosslc = net_local

ElseIf (net_local > 5000000) And (net_local <= 14000000) Then

net2grosslc = Round((net_local - 500000) / 0.9, 0)





ElseIf (net_local > 32500000) Then


End If

End Function

Function net2grossfr(net_foreign)

'Foreigner Net Salary to be gross-up to Gross Salary

If (net_foreign > 0) And (net_foreign <= 8000000) Then

net2grossfr = net_foreign

ElseIf (net_foreign > 8000000) And (net_foreign <= 18800000) Then

net2grossfr = Round((net_foreign - 800000) / 0.9, 0)





ElseIf (net_foreign > 63800000) Then


End If

End Function

TẠO ADD-IN

Ở các bước trên bạn đã tạo được các hàm dùng để tính thuế thu nhập. Tuy nhiên các hàm này chỉ

sử dụng cho chính bảng tính có gắn hàm đã tạo mà thôi. Để tất cả các bảng tính đều có thể sử dụng hàmđã tạo ở trên hoặc bạn muốn gửi những hàm này cho bạn bè thì bạn cần phải tạo add-in. Cáchtạo add-in nhưsau:

1. Nhấn chuột phải vào Module đã tạo ở trên

2. Điền thông số trong tab General

a. Project Name: Điền tên hàm (ví dụ: PIT)

b. Project Description: Điền vào mô tả của dự án (ví dụ: PIT formular)

3. Trong tab Protection, nhấn vào Lock project for viewing sau đó điền mật khẩu và xác nhận mật khẩu nếu bạn không muốn người khác xem và chỉnh sửa đoạn mã của mình.

4. Đóng cửa sổ VBA

5. Vào File_Save As, nhấn vào khung Save as type và chọn Microsoft Office Excel Add-in (*.xla)

6. Đặt tên file và chọn đường dẫn để lưu file (ví dụ: c:\PITformular .xla)

7. Hoàn tất

Muốn gắn add-in vào bất cứ máy nào có Excel bạn chỉ việc vào Tools ->Add-Ins sauđó nhấn Browse... và tìmđến nơi chứa file .xla nêu trên rồi OK

Tự động trích ngang dữ liệu trong Excel - 16/3/2005 14h:36

Bạn được giao nhiệm vụ thực hiện một biểu dữ liệu chi tiết theo dạng bàn cờ nhưnhật ký chứng từ. Nhập liệu bằng tay cho biểu này phải dùng scroll bar kéo qua kéo lại để tìm chođúng cột dữ liệu, vừa tốn công vừa dễ nhầm. Chi bằng bạn cứ nhập các thông tin cần thiết, phần việc còn lại hãy cứ để cho Excel làm giúp,đảm bảo số liệu chính xác 100%.

Ví dụ, chúng ta có một mẫu nhật ký chi tiền mặt (hình 1) với quy ước nhập liệu là nếu một chứng từ có nhiều tài khoản đối ứng thì nhập trên nhiều dòng khác nhau nhưng các cột ngày, số chứng từ và nội dung phải giống nhau. Nhiệm vụ được chia nhỏ thành 3 thủ tục macro để tiện cho việc bảo trì, sửa đổi mã lệnh sau này khi cần thiết (xin được bỏ qua bước trình bày cách tạo, lưu giữ và quản lý module, các thao tác này kháđơn giản). Một số điểm cần lưu ý trước khi trình bày mã lệnh của các thủ tục:

- Để gán giá trị của một cell vào biến, hãy di chuyển đến cell này và dùng thuộc tính value của cellhiện hành gán cho biến đã khai báo (Bien=ActiveCell.Value). Xong các lệnh gán, nhớ quay trở về cell cũtrước khi di chuyển.

Sử dụng địa chỉ kiểu tương đối khi di chuyển cell bằng thuộc tính Offset (Offset(Row, Column)).

- Dùng một macro thứ tưgọi lần lượt 3 macro trên để hình thành một quá trình hoàn chỉnh, gán phím tắt cho macro này để tiện sử dụng.

1. Trích ngang dữ liệu theo tài khoản phát sinh. Trong thủ tục này, căn cứ vào số hiệu tài khoản tại cột TK, số tiền tương ứng được trải ra theo chiều ngang, tiền của tài khoản nào được điền vào cột mangđúng số hiệu tài khoản đó.

Sub TrichNgang()

Dim Taikhoan As String

Dim ThutuDong, SoCot As Integer

Dim Sotien As Long

Range(“D2”).Select

Lặp đến dòng cuối của danh sách

Do Until ActiveCell.Value = “”

Taikhoan = ActiveCell.Value

ActiveCell.Offset(0, 1).Range(“A1”).Select

Sotien = ActiveCell.Value

Range(“F1”).Select

SoCot = 2

Thực hiện cho đến cột tài khoản cuối cùng. Nếu tìm thấy số hiệu tài khoản thìđiền số tiền lên dòng trên cùng của chứng từ và thoát vòng lặp.


If ActiveCell.Value = Taikhoan Then

ActiveCell.Offset(ThutuDong + 1, 0).Range(“A1”).Select

ActiveCell.Value = Sotien

Exit Do

Else


End If

SoCot = SoCot + 1

Loop

Trường hợp không tìm thấy tài khoản thì điền số hiệu tài khoản vào cột cuối cùng và điền số tiền vào đúng dòng đầu tiên của chứng từ. Bằng không dời con trỏ xuống đầu dòng dưới và thêm thứ tự dòng 1 đơn vị.

If ActiveCell.Value = “” Then

ActiveCell.Value = Taikhoan

ActiveCell.Offset(ThutuDong + 1, 0).Range(“A1”).Select

ActiveCell.Value = Sotien

End If

ActiveCell.Offset(1, -SoCot).Range(“A1”).Select

ThutuDong = ThutuDong + 1

Loop

End Sub

2. Mang số tiền từ các dòng dưới cộng vào dòng đầu đối với những chứng từ có hơn một dòng phátsinh. Đối với một chứng từ chi đối ứng với nhiều tài khoản, bạn phải cộng dồn số tiền của từng tàikhoản vào cột tổng cộng và trích ngang số tiền này vào các tài khoản tương ứng trên cùng một dòng.

Sub CungCTu()

Dim Ngay, Ngay2 As Date

Dim Chungtu, Chungtu2, Taikhoan, Taikhoan2, Noidung, Noidung2 As String

Dim ThutuDong, SoCot, SoDong As Integer

Dim Sotien, Sotien2 As Long

Lặp đến dòng cuối của danh sách

Range(“A2”).Select

Ngay = ActiveCell.Value


Chungtu = ActiveCell.Value


Noidung = ActiveCell.Value


Taikhoan = ActiveCell.Value

ActiveCell.Offset(1, -3).Range(“A1”).Select

Lưu các dữ liệu cần thiết vào biến. Lặp cho đến dòng cuối của danh sách


Ngay2 = ActiveCell.Value


Chungtu2 = ActiveCell.Value


Noidung2 = ActiveCell.Value

If Ngay = Ngay2 And Chungtu = Chungtu2 And Noidung = Noidung2 Then

SoDong = SoDong + 1




Taikhoan2 = ActiveCell.Value


Sotien2 = ActiveCell.Value

Range(“E1”).Select

SoCot = 5

Lặp đến cột tài khoản cuối cùng. Nếu tìm thấy số hiệu tài khoản thì cộng số tiền các dòng dưới lêndòng trên cùng của chứng từ, rồi thoát vòng lặp.


If ActiveCell.Value = Taikhoan2 Then

ActiveCell.Offset(ThutuDong - SoDong + 1, 0).Range(“A1”).Select

ActiveCell.Value = Sotien2

ActiveCell.Offset(0, -SoCot + 5).Range(“A1”).Select

ActiveCell.Value = ActiveCell.Value + Sotien2

Exit Do

Else


End If

SoCot = SoCot + 1

Loop


Else

SoDong = 0



End If

Lưu giữ các giá trị hiện tại để tiếp tục so sánh trong vòng lặp

Ngay = Ngay2

Chungtu = Chungtu2

Noidung = Noidung2


Nếu một chứng từ có nhiều dòng, phải dời con trỏ đến đúng dòng cuối.

If SoDong > 1 Then

ActiveCell.Offset(SoDong - 1, 0).Range(“A1”).Select

End If

Loop

End Sub

3. Xóa các dòng thừa (dòng thứ hai trở đi) ở những chứng từ có nhiều tài khoản đối ứng, đồng thời xóa cột TK (tài khoản).

Sub XoaDong()

Dim Ngay, Ngay2 As Date

Dim Chungtu, Chungtu2, Noidung, Noidung2 As String

Lưu giữ các giá trị ở dòng đầu để so sánh trong vòng lặp


Ngay = ActiveCell.Value


Chungtu = ActiveCell.Value


Noidung = ActiveCell.Value


So sánh lần lượt dòng trên với dòng dưới, nếu xác định là trùng nhau thì xóa các dòng thừa phía dưới.


Ngay2 = ActiveCell.Value


Chungtu2 = ActiveCell.Value


Noidung2 = ActiveCell.Value


If Ngay = Ngay2 And Chungtu = Chungtu2 And Noidung = Noidung2 Then

Selection.EntireRow.Delete

ActiveCell.Offset(-1, 0).Range(“A1”).Select

Else

End If

Ngay = Ngay2

Chungtu = Chungtu2

Noidung = Noidung2


Loop

Xóa cột số hiệu tài khoản (TK)

Columns(“D:D”).Select

Selection.Delete Shift:=xlToLeft


End Sub

Các phương pháp giấu số 0 trong Excel - 16/3/2005 14h:39

Khi tính toán trong Excel, bạn có thể thấy kết quả số 0 nằm ở nhiều nơi trong bảng t ính.Điều này gây khó nhìn hoặc lúc in ra sẽ không thẩm mỹ. Bạn có thể tìm để xoá các số 0 nhưng rất mất thời gian và nếu chúng là ô có công thức thì bạn phải tạo lại sau này khi thayđổi tính toán. Trong bài viết này tôi muốn giới thiệu một số phương pháp “thủ tiêu” số 0 “đáng ghét” một cách nhanh chóng.

1- Giấu tất cả số 0 trong bảng tính:

Nhấn vào menu Tools>Options, chọn thẻ View. Xoá hộp kiểm Zero values.

2- Dùng dạng số để giấu các số 0 trong những ô được chọn:

- Chọn các ô có số 0 cần giấu. Nhấn menu Format>Cells (hoặc nhấn Ctrl+1), chọn thẻ Number.

- Trong hộp Category, chọn Custom. Trong hộp Type, gõ 0;-0;;@.

3- Dùngđịnh dạng có điều kiện để giấu số 0 được trả về từ kết quả của công thức:

- Chọn ô có số 0 cần giấu.

- Nhấn menu Format>Conditional Formatting. Ở hộp bên trái chọn Cell Value Is, hộp thứ hai tiếp theo chọn equal to, hộp kế tiếp gõ số 0.

- Nhấn nút Format, chọn thẻ Font. Trong hộp Color, chọn màu trắng (hay trùng với màu nền của bảng tính). Bấm OK 2 lần.

4- Dùng công thức để giấu số 0 hoặc thay bằng dấu gạch nối (-):

Giả sử tại ô A1, A2 bạn có các số tương ứng là 5, 5. Khi lấy A1-A2 thì kết quả sẽ là 0. Bạn có thể dùng các công thức sau để giấu số 0 hoặc thay bằng dấu gạch nối (-):

=IF(A1-A2=0,””,A1-A2)

=IF(A1-A2=0,”-”,A1-A2)

5- Giấu số 0 trong PivotTable:

- Trên thanh công cụ PivotTable, nhấn PivotTable và chọn Table Options.

- Đánh dấu chọn For empty cells, show. Nếu muốn thay số 0 bằng ký tự khác thì gõ vào hộp kế bên; ngược lại muốn giấu số 0 thì để trống hộp.ÿ

106 thủ thuật với Microsoft Office - Phần 6 - 3/8/2005 15h:10

Microsoft Excel

Phím tắt thông dụng trong Excel

Phím tắt trong các ứng dụng MS Office là một trong những tính năng hữu ích nhất, giúp cho người sử dụng thao tác nhanh hơn. Xin liệt kê ra đây một số phím tắt thông dụng nhất trong Excel.

F2 Sửa nội dung thông tin trong ô

Ctrl-1 Mở hộp thoại định dạng ô ( Format | Cell )

Ctrl-Page Up Tiến lên 1 sheet (Sheet 1 sang Sheet 2)

Ctrl-Page Down Lùi về 1 sheet (Sheet 3 về Sheet 2)

Ctrl-Shift-" Sao chép dữ liệu từ ô ngay phía trên ô hiện thời

Ctrl-' Sao chép công thức từ ô ngay phía trên ô hiện thời

Ctrl-$ Chuyển định dạng ô hiện thời sang định dạng tiền tệ với 2 con số sau dấu phẩy

Alt-Enter Xuống dòng trong một ô

Kiểm soát hướng di chuyển của con trỏ khi ấn Enter

Theo mặc định, con trỏ thường sẽ xuống di chuyển xuống ô bên dưới khi bạn gõ phím Enter.Nhưng nếu bạn không thích bạn hoàn toàn có thể thay đổi hướng di chuyển của con chỏ, điều khiển con trỏ di chuyển sang bên phải bên trái, lên trên hay xuống dưới theo ý thích của bạn mỗi khi bạn gõ phím Enter. Hãy thử thủ thuật sau đây.

Bạn vào Tools | Options rồi chuyển sang mục Edit. Trong mục này, bạn chú ý đến dòng “Move selection after Enter”, hãyđánh dấu lựa chọn lựa chọn trước dòng này và ở danh sách liệt kê bên cạnh bạn hãy chọn hướng di chuyển cho con trỏ chuột.

Sao chép dữ liệu và công thức nhanh chóng

Thông thường khi cần sao chép dữ liệu hay công thức sang một loại các ô không liền kề nhau, bạn thường phải mất công copy và paste sang từng ô một. Nhưng nếu đã biết thủ thuật sau đây bạn hoàn toàn có thể thực hiện công việc này một cách rất nhanh chóng và hiệu quả hơn.

Trước tiên bạn hãy sao chép dữ liệu từ ô nguồn – ô chứa thông tin cần được sao chép ra, hãy dùngphím tắt Ctrl-C cho nhanh. Sau đó bạn vẫn giữ nguyên phím Ctrl và nhắp chuột trái vào từng ô màbạn muốn sao chép dữ liệu sang. Lựa chọn xong bạn hãy ấn ổ hợp phím Ctrl-V là dữ liệu sẽ tự động dán vào những nơi cần thiết cho bạn.

Ứng dụng thủ thuật này để copy-paste dữ liệu cho một loạt ô liền kề nhưng không ở gần ỗ dữ liệu nguồn. Trước tiên bạn hãy dùng Ctrl-C để sao chép dữ liệu từ ô nguồn, sau đó vẫn giữ nguyênphím Ctrl và dùng chuột trái lựa chọn một loạt ô mà bạn muốn sao chép dữ liệu sang sau đó thả Ctrl ra và ấn Enter là xong.


Microsoft Excel

Một trang - Một biểu đồ

Thông thường các biểu đồ sẽ đi kèm với dữ liệu có liên quanđến nó. Nhưng đôi khi bạn lại muốn in biểu đồ đó ra một trang riêng biệt hoàntoàn, tách rời khỏi dữ liệu. Rất đơn giản, bạn hãy lựa chọn biểu đồ đó rồi vào File | Print, biểu đồ sẽ được in ra một trang riêng.

Biểu đồ chỉ có 2 màu đen-trắng

Một tính năng tiện lợi khác khi bạn in biểu đồ trong Excel chính là lệnh xem trước (Preview). Cho dù bạn có máy in màu bạn cũng vẫn có thể in các biểu đồ chỉ với 2 màu đen và trắng bằng cách vào File | Print sauđó chọn nút Preview. Trong cửa sổ Preview bạn hãy chọn nút Setup và chọn sang mục Chart, đánh dấu lựa chọn vào trước Black and white. Bây giờ trong phần xem trước Preview biểu đồ của bạn đã được hiển thị bằng 2 màu đen-trắng giúp cho bạn có thể điều chỉnh độ sáng tối tương phản của các thanh, dòng hay cột của biều đồ dễ dàng hơn.

Tên tệp tin được in ra ở Footers

Bắt đầu từ phiên bản Excel 2002, Microsoft đã bổ sung thêm khả năng chèn đường dẫn của tệp tin bảng tính vào Header hay Footer.Đường dẫn này cũng tự động cập nhật khi bạn di chuyển tệp tin. Để chèn đường dẫn tệp tin bảng tính vào Header hay Footer bạn hãy theo cách sau đây:

Vào View | Header and Footer hoặc File | Page Setup | Header/Footer, chọn Custom Header hoặc Custom Footer. Trong cửa sổ Custom Header hoặc Custom Footer bạn lựa chọn vị trí muốn đặt tên được dẫn của tệp tin - ở bên trái, bên phải hay ở giữa. Bạn hãy đặt con trỏ vào vị trí đó rồi nhắp chuột vào biểu tượng hình thưmục trong thanh công cụ ở ngay phía trên. Khi đó ở vị trí bạn chọn sẽ xuất hiện đoạn mã &[Path]&[File]. Nhưvậy đã thành công.

Xác nhận thông tin

Nếu phải đối mặt với một bảng tính có nhiều loại dữ liệu khác nhau sẽ rất dễ nhầm lẫn khi xử lý nhập liệu. Để tránh tình trạng nhầm lẫn bạn có thể sử dụng tính năng Xác nhận thông tin trong Excel.

Ví dụ bạn có cột mức thuế và chắc chắn mức thuế đó không vượt 100% thìbạn có thể quy định Excel chỉ nhận các giá trị nhỏ hơn 100. Nhưvậy nếu có lỡ tay thì cũng khôngsợ nhầm lẫn, Excel sẽ nhắc bạn phải nhớ là không được vượt quá 100. Hoặc bạn có thể đặt giá trị nằm trong khoảng nào đó …. Bạn có thể đặt Data Validation cho một ô, một loạt ô, một hàng một cột…

Để sử dụng tính năng này trong Excel trước tiên bạn hãy lựa chọn ô – hàng - cột muốn ứng dụng xác nhận thông tin sau đó vào Data | Validation. Bạn đưa ra quy định nhập liệu của mình rồi nhắp OK.

Nếu bạn có gửi bảng tính cho người khác sử dụng bạn nên đặt thêm chú thích cho các ô – hàng -cột có ứng dụng Data Validation để họ có thể nhập đúng thông tin bằng cách sau đây.

Trong cửa sổ Data | Validation bạn chuyển sang mục Input Message đặt tên và chú thích rõ ràngvào đó. Nhưvậy mỗi khi con trỏ chuột được chuyển đến các ô có tính năng Data Validation thì sẽ hiện ra hướng dẫn cụ thể cho người sử dụng.

Tương tự nhưthế bạn hoàn toàn có thể tuỳ biến cảnh báo khi nhập sai dữ liệu bằng cách chuyển sang mục Error Alert nhập tên và nội dung cảnh báo vào đó.

Tuỳ biến danh sách

Nếu bạn thường xuyên phải nhập cùng một loại dữ liệu giống nhau trên các bảng tính khác nhau – ví dụ danh sách tên các nhânviên trong công ty – bạn có thể sử dụng tính năng Tuỳ biến danh sách (Custom Lists) để tăng tốc và đơn giản hoá công việc này.

Bạn hãy vào Tools | Options rồi chuyển sang mục Custom Lists. Trong cửa sổ hiện ra bạn chọn New List trong ô bên tay trái, còn trong ô bên tay phải bạn nhập các giá trị trong danh sách của mình vàođó, mỗi một đối tượng trong danh sách là một dòng, cuối cùng bạn chọn nút Add. Hoặc nếu bạn đã có danh sách rồi bạn có thể chọn Import list from cell vàlựa chọn các ô chứa dữ liệu bạn muốn nhập danh sách.

Bây giờ bạn chỉ cần gõ bất kì một đối tượng nào đó có trong danh sách của bạn rồi di chuyển con trỏ đến góc dưới bên tay phải của ô đến khi con trỏ chuyển thành dấu cộng rồi kéo đến các ô bạn muốn danh sách hiện ra. Excel sẽ giúp bạn điền nối các giá trị còn lại.


Microsoft Excel

Định nghĩa hằng số trong bảng tính

Sử dụng công cụ Name trong Excel bạn có thể định nghĩa trước một hằng số trong tệp tin bảng tính của bạn. Lấy ví dụ, bạn có thể định nghĩa trước mức thuế bạn thường xuyên sử dụng nhất. Bạn hãy thử cách sau đây:

Tìmđến Insert | Name | Define và nhập tên TaxRate. Trong mục “Refers to” bạn hãy nhập vào giátrị - giả sử chúng ta chọn là 0.07, sau đó nhắp chuột vào OK. Bây giờ bạn đã có thể nhập công thức bất kì theo kiểu =A1*TaxRate và Excel sẽ tự động thay số cho bạn.

Bằng cách này bạn cũng có thể tiết kiệm thời gian trong việc xử lý bảng tính đi rất nhiều chỉ bằng cách định nghĩa những con số thường được sử dụng trong bảng tính.

Giả sử nếu có thay đổi gì đối với những hằng số do bạn định nghĩa ra bạn sẽ không mất quá nhiều thời gian để chỉnh sửa từng công thức. Bạn chỉ việc quay vào đó và thay đổi trong mục “Refers to” là xong.

Biểu đồ tự cập nhật

Trong các phiên bản Excel cũ để có thể tạo được một biểu đồ có khả năng tự động cập nhật mỗi khi giá trị tham chiếu thay đổi là rất khó. Nhưng kể từ phiên bản Excel 2003 vấn đề này đã được giải quyết.

Trước tiên bạn hãy biến dữ liệu tham chiếu vẽ biểu đồ thành một danh sách bằng cách vào Data |

List | Create List và lựa chọn vùng dữ liệu của bạn.

Giờ đây sử dụng danh sách này để vẽ biểu đồ thì sẽ rất tiện lợi. Mỗi khi có giá trị mới được bổ sung vào trong danh sách thì vùng dữ liệu tham chiếu và biểu đồ sẽ tự động cập nhật thêm giá trị.

Công thức đúng hay sai?

Khi nhập một công thức vào trong bảng tính bạn thường phải xem xét lại tính chính xác của chúngnhằm đạt được kết quả tính toán đúng nhưmong đợi. Excel cũng có công cụ giúp bạn đánh giá lại công thức vừa nhập đúng hay sai, xem xét quy trình tính toán trong công thức mà Excel sẽ đi theo. Lấy ví dụ bạn nhập vào công thức =7+5*3 sẽ nhận được kết quảlà 22 thay vì 36. Để nhận được kết quả 36 bạn phải nhập =(7+5)*3.

Nếu bạn vẫn thấy chưa chắc chắn vê quy trình tính toán công thức của mình bạn hãy lựa chọn ô chứa công thức rồi vào Tools | Formula Auditing | Evaluate Formula, Excel sẽ chỉ cho bạn biết.

Dấu cột hay hàng?

Đôi khi trong quá trình xử lý bảng tính bạn muốn ẩn đi một vài dòng hay một vài cột nào đó khôngcần thiết để tránh rối mắt. Để thực hiện việc này trước bạn hãy lựa chọn hàng và cột bạn muốn ẩn đi rồi vào Format | Row | Hide hay Format | Column | Hide. Để hiện trở lại những hàng hay cột đãbị giấu đi bạn hãy vào Format | Row | Undide hay Format | Column | Unhide.

Bằng cách này bạn cũng có thể giấu đi cả một Sheet của mình.

Bảo vệ bảng tính

Excel cung cấp cho bạn rất nhiều cách bảo vệ bảng tính khác nhau, bạn có thể bảo vệ cả bảng tính, bảo vệ sheet hay bảo vệ ô nào đó…

Để ngăn chặn người khác không thể mở và sửa đổi bảng tính trên máy của bạn, hãy bảo vệ bằng mật khẩu “Password to open”. Hãy vào Tool | Options | Security. Bạn nhập mật khẩu vào trường “Password to open” hay mật khẩu vào trường “Password to modify” rồi nhắp chuột vào OK. Bảng tính của bạn đã được bảo vệ, nếu không có mật khẩu thì sẽ không thể mở hay sửa đổi bảng tính của bạn được.

Bên cạnh đó tính năng Tool | Protection còn cung cấp cho bạn rất nhiều lựa chọn bảo vệ khác nhau nữa.

Trước tiên là tính năng Bảo vệ Sheet. Bạn hãy vào Tool | Protection | Protect Sheet. Bạn nhập mật khẩu vào trong mục “Password to unprotect sheet” – đây là mật khẩu để gỡ bỏ mọi hình thức bảo vệ đối với bảng tính đã được bảo vệ. Trong vùng “Allow all user of this worksheet to”, bạn lựa chọn cho phép người sử dụng có thể thực hiện được những thao tác gì trên Sheet đã được bảo vệ. Nếu bạn không lựa chọn gì thì không ai có thể thay đổi được gì trên Sheet đó trừ khi người đó có mật khẩu loại bỏ tính năng Protect Sheet.

Tính năng thứ 2 là Allow User to Edit Range. Đây là tính năng giúp bạn bảo vệ một vùng nhất định trên bảng tính, chỉ những ai được bạn cấp quyền hay có mật khẩu mới được phép sửa đổi trênvùng được bảo vệ này. Tính năng này cũng sử dụng quyền của các user trong hệ điều hànhWindows của bạn. Nếu nhiều người sử dụng chung 1 máy bạn có thể cho phép người đó được phép sửa đổi mà không cần đặt mật khẩu.

Tính năng Tool | Protection | Protect Workbook giúp bạn bảo vệ toàn bộ tệp tin bảng tính của bạn, từ khung cửa sổ đến cấu trúc bảng tính của bạn.

Tính năng Tool | Protection | Protect and Share workbook giúp bạn theo dõi mọi thay đổi trong bảng tính của bạn. Khi có ai đó thực hiện bất kì thay đổi gì trên bảng tính, Excel sẽ dùng TrackChanges để ghi lại và thông báo cho bạn biết.

Bảo vệ các tài liệu dùng chung - 17/11/2005 11h:23

Bạn muốn bảo vệ các tập tin Word và Excel không bị thay đổi khi bạn gửi chúng tới những người khác. Các tập tin dùng chung rất dễ bị thay đổi, chỉnh sửa lại. Vậy có cách nào để giữ nguyên định dạng và nội dung các tập tin bạn gửi?

Bảo vệ tài liệu Word

Bạn có thể bảo vệ được các tập tin Word, tuy nhiên sự bảo vệ này không phải là hoàn hảo. Trong Word 2003, bạn chọn menu Tools -> Protect Document. Bạn đánh dấu chọn vào mục Allow only this type of editing in the document. Bạn hãy để nguyên giá trị No changes (Read only), hoặc chọn Comments- lựa chọn này cho phép người nhận có thể thêm các chú thích vào tài liệu. Bạn có thể thêm tài liệu bằng cách Insert -> Comment mà không làm thay đổi nội dung tài liệu. Sau đó, bạn nhấn nút Yes, Start Enforcing Protection, nhập mật khẩu 2 lần, nhấn OK.

Trong Excel 2003, bạn chọn Tools -> Protection -> Protect Sheet. Trong danh sách các hoạt động được phép, bạn hãy bỏ chọn tất cả đánh dấu ngoại trừ 2 lựa chọn đầu tiên (Select locked cells vàSelect unlocked cells). Tiếp theo, bạn nhập mật khẩu, nhấn OK, và lặp lại bước này một lần nữa. Bởi vì tất cả các ô đều bị khóa nên người sử dụng không có mật khẩu sẽ không thể thay đổi được.

Tuy nhiên, phương pháp này sẽ không cho phép người sử dụng khác có thể thay đổi các tập tin của bạn, nhưng với những người sử dụng máy tính có kinh nghiệm thì sự bảo vệ này quá đơn giản với họ. Chẳng hạn, họ có thể sao chép (copy) và dán (paste) các đoạn văn bản từ tài liệu của bạn sang một tài liệu mới, xóa tài liệu gốc đi, thay đổi tên tài liệu mới thành tên tài liệu của bạn.

Bảo vệ tài liệu Excel

Không những thế, trên Internet còn có vô số các tiện ích cho phép bẻ khóa mật khẩu các tài liệu của Office.

Còn một cách nữa, bạn chuyển các tài liệu của mình sang định dạng PDF hoặc lưu lại thành dạng ảnh, cách này sẽ gây khó khăn hơn khi người nhận định thay đổi tài liệu của bạn. Tuy nhiên, bạn dù cách nào đi nữa thì người nhận cũng có thể tìm ra cách để phá vỡ. Vì vậy, điều đó còn tùy thuộc vào sự tin tưởng của bạn nơi người nhận.

Dùng VBA trong Excel để tạo và sửa chữa PivotTable - 1/9/2006 10h:19

Chức năng PivotTable là chức năng mạnh của Excel, nó giúp bạn tổng kết số liệu nhanh một cách kinh ngạc. Chức năng này đầu tiên xuất hiện trong Excel 5.

Tôi cho rằng các bạn đã làm quen với việc tạo và sửa chữa PivotTable bằng cách thủ công và bàiviết này sẽ hướng dẫn dùng VBA để tạo và sửa chữa PivotTable một cách linh động. Bài viết sử dụng cho Excel 2000.

Giả sử ở sheet1, tôi có khối dữ liệu cần phân tích nhưHình1. Khối dữ liệu này gồm các trường: SalesRep (đại diện bán hàng), Region (Vùng), Month (Tháng), Sales (doanh số bán).

Trước khi tạo bảng PivotTable nhưHình 2, tôiđã chọn Record New Macro... nhưHình 3, để xem đoạn mã được ghi lại nhưthế nào.

Sau đó tôi vào màn hình VBE bằng cách nhấn tổ hợp phím Alt + F11. Tôi vào Module1, thấy được đoạn mã nhưsau:

Sub Macro1()

Macro1 Macro

Macro recorded 17/03/2003 by Duyet

Range("A1:D13").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

"Sheet1!R1C1:R13C4").CreatePivotTableTableDestination:=Range("A1"), _

TableName:="PivotTable1"

ActiveSheet.PivotTables("PivotTable1").SmallGrid = False

ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="SalesRep", _

ColumnFields:="Month", PageFields:="Region"

ActiveSheet.PivotTables("PivotTable1").Pivot

Hình 3

Ghi chú:

Region Là trường page trong PivotTable.

SalesRep Là trường row trong PivotTable.

Month Là trường column trongPivotTable.

SalesLà trường data trong PivotTable sử dụng hàmSum

Fields("Sales").Orientation = _

xlDataField

End Sub

Khảo sát đoạn mã đã được ghi:

Để khảo sát đoạn mã trên bạn cần phải biết một số đối tượng liên quan. Tất cả các đối tượng nàyđều được giải thích trên online help.

PivotCaches là tập hợp các đối tượng PivotCache trong đối tượng Workbook

PivotTables là tập hợp các đối tượng PivotTable trong đối tượng Workbook

PivotTableFields là tập hợp các trường trong đối tượng PivotTable

CreatePivotTable

một phương thức của đối tượng PivotCacheđể tạo một PivotTable sử dụng dữ liệu trong một PivotCache

Ta có thể viết lại thủ tục trên bằng thủ tục CreatePivotTable (chú ý bạn nhập thủ tục này vàomodule1) sauđây, có thể nó hơi dài nhưng sẽ dễ hiểu hơn, và bạn có thể chạy chương trình bất cứ đâu bằng cách nhấn tổ hợp phím Alt + F8, sau đó chọn thủ tục CreatePivotTable và chọn Run nhưHình 4.

Sub CreatePivotTable()

Dim PTCache As PivotCache

Dim PT As PivotTable

Application.ScreenUpdating = False

Xoa PivotSheet neu no ton tai

On Error Resume Next

Application.DisplayAlerts = False

Sheets("PivotSheet").Delete

On Error GoTo 0

Tao Pivot Cache

Set PTCache =ActiveWorkbook.PivotCaches.Add _

(SourceType:=xlDatabase, _

SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion.Address)

Tao worksheet moi va dat ten

Worksheets.Add

ActiveSheet.Name = "PivotSheet"

Tao Pivot Table tu Cache

Set PT = PTCache.CreatePivotTable _

(TableDestination:=Sheets("PivotSheet").Range("A1"), _

TableName:="PivotTable1")

With PT

Them cac truong

.PivotFields("Region").Orientation = xlPageField

.PivotFields("Month").Orientation = xlColumnField

.PivotFields("SalesRep").Orientation = xlRowField

.PivotFields("Sales").Orientation = xlRowField

Application.ScreenUpdating = True

End With

End Sub

Khi chạy xong thủ tục trên, bạn sẽ được một PivotTable ở sheet2, trong trường hợp này sheet cótên là PivotSheet. (Hình 5)

Nếu chú ý, bạn sẽ thấy sự khác biệt của 2 đoạn mã trên. Trong Macro1 khi sử dụng phương thức Add để tạo PivotCache thì SourceData là "Sheet1!R1C1:R13C4" còn trong đoạn mã tôi viết làSheets("Sheet1").Range("A1").CurrentRegion.Address. Ở đây tôi dùng thuộc tính Current Region,có nghĩa là dữ liệu chúng ta sử dụng dựa trên vùng hiện tại xung quanh ô A1. Điều này để chắc chắn thủ tục CreatePivotTable vẫn tiếp tục làm việc tốt khi chúng ta thêm vào dữ liệu.

Bây giờ giả sử tôi có thêm trường Target (chỉ tiêu) trong khối dữ liệu, và trong PivotTable tôi sẽ đưa thêm trường target vào đồng thời cũng thêm trường tính toán Variance. Trường này(Variance) sẽ bằng Sales - Target. Khối dữ liệu mới của tôi nhưhình 6.

Đoạn mã trong thủ tục CreatePivotTable trên sẽ được thêm nhưsau (tôi chỉ thêm trong đoạn WithPT ....End With):

With PT

Them cac truong

.PivotFields("Region").Orientation =xlPageField

.PivotFields("Month").Orientation =xlColumnField

.PivotFields("SalesRep").Orientation =xlRowField

.PivotFields("Sales").Orientation =xlDataField

.PivotFields("Target").Orientation =xlDataField

Them truong tinh toan

.CalculatedFields.Add "Variance", "=Sales - Target"

.PivotFields("Variance").Orientation = xlDataField

Thay doi caption

.PivotFields("Sum of Sales").Caption = "Sales ($) "

.PivotFields("Sum of Target").Caption = "Target ($) "

.PivotFields("Sum of Variance").Caption = "Variance ($) "

End With

Sau khi chạy lại thủ tục trên tôi sẽ được nhưhình 7.

Giả sử bây giờ dữ liệu của tôi gồm 6 tháng (hình 8), tôi muốn đưa thêm cột tổng theo từng 3 tháng. Tôi phải sửa lại đoạn mã của mình nhưsau:

With PT

Them cac truong

.PivotFields("Region").Orientation = xlPageField

.PivotFields("Month").Orientation = xlColumnField

.PivotFields("SalesRep").Orientation =xlRowField

.PivotFields("Sales").Orientation =xlDataField

.PivotFields("Target").Orientation =xlDataField

Them truong tinh toan

.CalculatedFields.Add "Variance", "=Sales -Target"

.PivotFields("Variance").Orientation =xlDataField

Them muc tinh toan

.PivotFields("Month").CalculatedItems.Add "Q1", _

"= thang 1 + thang 2 + thang 3"

.PivotFields("Month").CalculatedItems.Add "Q2", _

"= thang 4 + thang 5 + thang 6"

Di chuyen cac muc tinh toan

.PivotFields("Month").PivotItems("Q1").Position = 4

.PivotFields("Month").PivotItems("Q2").Position = 8

Thay doi caption

.PivotFields("Sum of Sales").Caption = "Sales ($) "

.PivotFields("Sum of Target").Caption = "Target ($) "

.PivotFields("Sum of Variance").Caption = "Variance ($) "

End With

Sau khi chạy lại thủ tục CreatePivotTable tôi sẽ được kết quả nhưHình 9.

Vâng,đến đây các bạn thấy đó, nếu chúng ta biết sử dụng VBA thì công việc phân tích dữ liệu sẽ trở nên đơn giản hơn. Ngoài ra, ta cũng có thể tạo một PivotTable từ nguồn dữ liệu bên ngoài nhưAccess chẳng hạn. Để lập trình PivotTable được tốt, các bạn nên đọc phần online help của Excel về các đối tượng, phương thức, thuộc tính đã đề cập ở trên.

Hy vọng rằng bài viết trên sẽgiúp các bạn một phần nào trong công việc.

Hàm Excel tính tuổi nợ và số dưbình quân - 30/9/2006 10h:30

Kế toán công nợ thường phải tính tuổi số dưkhoản phải thu của khách hàng để biết khoản nào cần được thu trước, ngoài ra còn phải tính số dưbình quân của các khoản phải thu nàynhằm "nâng cao chất lượng quản lý tài chính". Nếu không có phần mềm kế toán cung cấp sẵn các chức năng này hoặc phải tác nghiệp bằng Excel một cách thủ công thì tôi đảm bảo bạn sẽ cảm thấy cuộc đời của kế toán công nợ không khác gì "cửu vạn". Hai hàm Excel dưới đây có thể giúp bạn nhanh chóng lấy lại cảm giác yêu đời.

Ví dụ chúng ta có 1 sheet dữ liệu của khách hàng nhưhình dưới

• Cột thứ nhất là ngày tháng giao dịch với khách hàng (sắp xếp theo ngày tăng dần)

• Cột thứ hai là số tiền phải thu của khách hàng (ghi nợ)

• Cột thứ ba là số đã thu được của khách hàng (ghi có)

• Cột thứ tưlà cột thứ 2 trừ cột thứ 3

• Cột thứ năm là cân đối còn phải thu sau từng giao dịch, cột 6 là ghi chú

Việc tính toán chỉ cần cột 1 đến cột 4.

Tạo hàm

1. Function OldOfDebt(mRange As Range, toDate As Date) As Double

Hàm này trả về tuổi của khoản phải thu theo ngày, bằng cách giải đáp số dưcòn phải thu tại ngàycuối cùng là các khoản phải thu của các ngày nào theo nguyên tắc khoản đã thu được sẽ là thanhtoán cho khoản phải thu đến trước, sau đó tính ra số ngày theo trọng số của từng khoản đối với số dư.

Hàm có hai tham số, thứ nhất mRange chính là vùng tính toán, trong ví dụ là A2:D13; thứ hai toDate là ngàyđể xác định tuổi (toDate phải lớn hơn ngày cuối cùng phát sinh giao dịch với khách hàng), trong hình là C19. Theo ví dụ hàm tính tuổi của khoản phải thu có giá trị 191.000 là 146,36ngày trả về tại ô E19.

2. Function AvgBalance(mRange As Range, toDate As Date) As Double

Hàm này có tham số y hệt hàm trên, trả về số dưcòn phải thu trung bình của các khách hàng theotỷ trọng về thời gian. Trong hình, hàm tính ra số dưbình quân trả về ô E21 là 106.791 với mRange là A2:D13 và toDate là 31/12/2005. (Bạn có thể tính được tổn thất do khách hàng này chiếm dụng bằng Hàm x lãi suất trong khoảng thời gian A2 đến A13).

Mã nguồn

Public Function OldOfDebt(mRange As Range, toDate As Date) As Double

Dim rDate As Range Cot ngay

Dim rDebit As Range Cot ghi no

Dim rCredit As Range Cot ghi co

Dim mPaid As Double Tong so da thu duoc

Dim mClose As Double So du cuoi tai ngay toDate

Dim mAccDebit As Double Debit cong don

Dim thisAmount As Double

Dim thisDate As Double

Dim mRow As Long Bien dem so dong

Dim i As Long

Dim ret As Double Gia tri tro ve

mRow = mRange.Rows.Count

Set rDate = mRange.Range(Cells(1, 1), Cells(mRow, 1))

Set rDebit = mRange.Range(Cells(1, 2), Cells(mRow, 2))

Set rCredit = mRange.Range(Cells(1, 3), Cells(mRow, 3))

mPaid = Application.WorksheetFunction.Sum(rCredit)

mClose = Application.WorksheetFunction.Sum(rDebit) -Application.WorksheetFunction.Sum(rCredit)

For i = 1 To mRow

If rDebit.Cells(i, 1).Value <> 0 Then

mAccDebit = mAccDebit + rDebit.Cells(i, 1).Value

If mAccDebit > mPaid Then

thisAmount = Application.WorksheetFunction.Min(mAccDebit - mPaid,rDebit.Cells(i, 1).Value)

thisDate = rDate.Cells(i, 1).Value

ret = ret + thisAmount * (toDate - thisDate) / mClose

End If

End If

Next i

OldOfDebt = ret

End Function

Public Function AvgBalance(mRange As Range, toDate As Date) As Double

Dim rDate As Range

Dim rAmount As Range

Dim mRow As Long

Dim mLenght As Long quang thoi gian tu ngay dau den toDate

Dim i As Long

Dim ret As Double

mRow = mRange.Rows.Count

Set rDate = mRange.Range(Cells(1, 1), Cells(mRow, 1))

Set rAmount = mRange.Range(Cells(1, 4), Cells(mRow, 4))

mLenght = toDate - rDate.Cells(1, 1)

For i = 1 To mRow

ret = ret + rAmount.Cells(i, 1) * (toDate - rDate.Cells(i, 1)) / mLenght

Next i

AvgBalance = ret

End Function

Làm quen với Microsoft Excel - 13/10/2006 10h:16

Song song với lượt bài về Microsoft Word đang được đăng, Quản Trị Mạng sẽ đưa ra loạt bài về bảng tính Microsoft Excel. Mong rằng những kiến thức này sẽ thực sự bổ ích cho các bạn mới làm quen với bộ Office của Microsoft cũng nhưvới những người làm công tác vănphòng.

Có thể một số người đã quá quen thuộc với bảng tính Excel cũng không hề biết rằng bảng tính bạn đang sử dụng có tối đa là 256 cột và 65.536 dòng; số lượn worksheet tối đa bạn có thể thêm được là 255 sheet. Với con số khổng lồ này thì việc quản lý các bảng sẽ trở nên đơn giản hơn nhiều vìhoàn toàn có thể lưu được 255 bảng nằm trên 255 sheet chỉ trong một file Excel.

1, Các kiểu dữ liệu trong bảng tính

Khi làm việc với bảng tính bạn sẽ phải làm quen với rất nhiều kiểu dữ liệu, nhưng tất cả các kiểu dữ liệu đó đề dựa vào 3 kiểu cơbản: Kiểu số, Kiểu chữ và Kiểu công thức.

a, Kiểu chữ:

Dữ liệu kiểu chữ luôn năm ở phía bên trái của ô tính (cell), nó bao gồm các chữ cái, chữ số và cácký tự đặc biệt. Nếu một ô tính có dữ liệu số muốn chuyển sang chữ thì phải có dấu nháy đơn(‘) ở trước ô đó.

b, Kiểu số:

Dữ liệu kiểu số luôn nằm ở bên phải của ô tính. Các giá trị ngày tháng, thời gian, tiền tệ, phần trăm… đều là dữ liệu kiểu số (có thể tính toán cộng, trừ, nhân, chia).

Chú ý: giá trị ngày tháng nếu bạn nhập đúng (thường là tháng/ngày/năm) thì sẽ nằm ở bên phải của ô, nếu nhập sai thì sẽ ở bên trái ô (tương đương với kiểu chữ)

c, Kiểu công thức:

Dữ liệu kiểu công thức là các dữ liệu bắt đầu bởi các dấu: =, +, -, * (thông thường nhất là sử dụng dấu =).

- Các công thức tính toán trong kiểu dữ liệu:

Cộng +Trừ -Nhân *Chia /Phần trăm %

Dấu ngăn cách giữa các phần thập phân thường là dấu chấm (.), còn dấu ngăn cách giữa các số hàng nghìn là dấu phẩy (,). (Ví dụ: 1000000 = 1,000,000; còn ½ = 0.5)

- Các hàm logic:

OR: hàm hoặc AND: hàm và NOT: hàm phủ định

- Ngoài ra với kiểu công thức thì thường sẽ kết hợp với các hàm tính toán (phần này sẽ được giới thiệu trong các bài tiếp theo)

2, Các loại địa chỉ

Mỗi ô tính (cell) đều có một địa chỉ riêng biệt để phân biệt và tính toán. Địa chỉ của ô được đặt têntheo ký hiệu cột và số dòng tương ứng của ô đó. (Ví dụ: ô C3 là ở cột C, dòng 3), địa chỉ của ô bạn sẽ nhìn thấy ở phía bên trái thanh Formular (thanh công cụ ngay phía trên của bảng tính).

Có 4 loại địa chỉ ô mà bạn phải ghi nhớ: Dùng phím F4 để thay đổi giữa các loại địa chỉ

- Địa chỉ tươngđối: là địa chỉ thông thường mà bạn hay thấy, địa chỉ này sẽ thay đổi cả cột cả dòng khi sao chép công thức (phần này sẽ giới thiệu về sau). Ký hiệu địa chỉ tương đối là:tencottendong (Ví dụ: C3)

- Địa chỉ tuyệt đối dòng: là địa chỉ có dòng không thay đổi nhưng cột thay đổi. Ký hiệu của địa chỉ tuyệt đối dòng là: tencot$tendong (ví dụ: C$3 là địa chỉ tuyệt đối dòng 3)

- Địa chỉ tuyệt đối cột: là địa chỉ có cột không thay đổi nhưng dòng thayđổi. Ký hiệu của địa chỉ tuyệt đối cột là: $tencottendong (ví dụ: $C3 là địa chỉ tuyệt đối cột C)

- Địa chỉ tuyệt đối: là địa chỉ mà cả cột và dòng đều không thay đổi khi sao chép công thức. Ký hiệu của địa chỉ này là: $tencot$tendong (ví dụ: $C$3 là địa chỉ tuyệt đối cả cột C và dòng 3)

Vùng địa chỉ: bạn sẽ phải sử dụng vùng địa chỉ rất nhiều khi làm bảng tính, vùng địa chỉ này thể hiện bạn đang chọn từ ô nào đến ô nào. Ký hiệu vùng địa chỉ nhưsau:tencot1tendong1:tencot2tendong2 (Ví dụ bạn đang chọn vùng từ ô C3 đến ô H5 thì vùng địa chỉ sẽ là C3:H5)

3, Căn chỉnh, định dạng dữ liệu trong bảng tính

Nếu bạn đã xem lượt bài về Microsoft Word thì hẳn các bạn cũng nắm được một số kiến thức cơbản về việc căn chỉnh văn bản. Vì chúng ta làm việc với bảng tính chủ yếu là tính toán nên cănchỉnh không nhiều, nhưng cũng phải đủ để người khác xem các bảng biểu của bạn có thể nắm được cấu trúc của nó.

Chỉnh sửa dữ liệu trong ô: có 3 cách sau

- Click đúp chuột trái vào ô cần sửa

- Chọn ô cần sửa rồi nhấn phím F2

- Chọn ô cần sửa và sửa nội dung của ô trên thanh Formular Bar

Nếu muốn căn chỉnh, định dạng cho phần nào thì trước tiên bạn phải bôi đen phần đó. Các bước tiếp theo làm nhưsau:

a, Định dạng kiểu dữ liệu hiển thị:

Vào Format -> Cells… -> Number

- Genaral: kiểu mặc định cơbản khi bạn nhập vào.

- Number: kiểu số có phân biệt phần thập phân với

Decimal places: số chữ số hiển thị sau phần thập phân Use 1000 Separator (,): có sử dụng dấu (,) ngăn cách giữa các phần nghìn hay không Negative numbers: định dạng cho phần số âm.

- Currency, Accounting: kiểu tiền tệ

Symbol: kiểu tiền tệ các nước

- Date: kiểu ngày tháng

- Time: kiểu thời gian

- Percentage: kiểu phần trăm

- Fraction: kiểu phân số

Scientific: kiểu số viết tắt

- Text: kiểu chữ

- Special: kiểu đặc biệt

- Custom: kiểu người dùng tự định dạng

Ví dụ: bạn muốn định dạng ngày tháng hiển thị (số hiển thị có thể khác với thực chất số bạn nhập vào) ở dạng ngày/tháng/năm: định dạng trong Custom là dd/mm/yyyy (d- day, m- month, y- year)

b, Căn chỉnh, định dạng dữ liệu trong ô:

Vào Format -> Cells… -> Alignment

- Text alignment: căn chỉnh lề cho dữ liệu

Horizontal: căn chỉnh theo chiều ngang của ô+ General: mặc định theo dữ liệu nhập vào là chữ hay số+ Left (Indent): căn theo bên trái ô+ Center: căn vào giữa ô (chiều ngang)+ Right (Indent): căn theo bên phải ô

+ Fill: lấp đầy ô bằng chính dữ liệu đã có trong ô+ Justify: căn đều 2 bên+ Center Across Selection: căn giữa theo vùng được chọn (vùng bôi đen)

Vertical:+ Top: căn theo mép trên của ô+ Center: căn nằm ở giữa ô (chiều dọc)+ Bottom: căn theo mép dưới ô+ Justify: căn đều+ Distributed: tự căn định dạng.

- Text control:

Wrap text: cho phép dữ liệu tự xuống dòng trong ô (nếu bạn muốn xuống dòng dữ liệu theo ý mình thì đặt con trỏ trước vị trí cần xuống dòng và nhấn Alt + Enter)

Shrink to fit: tự động co nhỏ dữ liệu khi ô bị thu nhỏ lại Merge cells: trộn ô

- Right-to-left: hướng viết văn bản

Context: tuỳ thuộc dữ liệu nhập vào Left-to-right: viết từ trái sang phải Right-to-left: viết từ phải sang trái

- Orientation: định hướng hiển thị văn bản nằm ngang hay nằm dọc theo ô, có thể chỉnh dữliệu nằm chéo bằng cách kéo trục text hoặc chọn độ quay của text ở ô Degrees phía dưới.

c, Định dạng Font

Vào Format -> Cells… -> Fonts

Phần này tương tự nhưWord với:

- Font: định dạng font

- Font style: định dạng kiểu chữ nghiêng, đậm hay thường

- Size: định dạng cỡ chữ

- Underline: kiểu gạch chân

- Color: màu chữ

- Effects:

Strikethrough: định dạng gạch ngang giữa chữ Superscript: chỉ số trên Subscript: chỉ số dưới

d,Định dạng viền ô, viền bảng:

Vào Format -> Cells… -> Border

Presets: định vị trước cho viền

None: không có viền Outline: định dạng đường viền ngoài Inside: định dạng đường viền trong

- Border: định đạng dường viền chi tiết với nét trên, nét dưới, nét trái, nét phải, nét sổ dọc giữa vànét sổ ngang giữa. - Line: kiểu đường viền

Style: các kiểu đường viền Color: màu cho đường viền

e, Nếu muốn định dạng màu cho ô thì vào Format -> Cells… -> Patterns và chọn màu.

Ngoài ra, nếu muốn định dạng nhanh bạn có thể sử dụng các chức năng sẵn có trên thanh công cụ Formatting để định dạng

4, Xoá toàn bộ định dạng đã làm

Trong trường hợp bạn đã định dạng xong nhưng lại muốn xoá định dạng đi để làm lại (xoá không

mất dữ liệu) bạn làm nhưsau:

- Bôi đen toàn bộ dữ liệu muốn bỏ định dạng - Vào Edit -> Clear -> Format

5, Cách thức tính toán và sao chép công thức trong Excel

a, Nhập công thức tính toán:

Nếu bạn muốn tính toán với giá trị của ô nào thì sử dụng địa chỉ của ô đó để tính toán

Ví dụ: Bạn muốn cộng 2 số ở địa chỉ B5 và địa chỉ C5 với nhau thì công thức tại ô cần đặt kết quả là =C5+B5 (không nhất thiết phải nhớ địa chỉ của các ô, bạn chỉ cần gõ dấu bằng (=) sau đó click chuột vào ô B5, gõ tiếp dấu cộng (+) rồi click chuột vào ô C5, cuối cùng nhấn Enter bạn sẽ có được kết quả)

b, Sao chép công thức:

Nếu với một bảng biểu nhưhình trên, bạn không phải tính cộng cho từng dòng của bảng mà chỉ cần nhập công thức ở một ô duy nhất trong cột kết quả. Sau khi nhập xong công thức và nhấn Enter, đưa chuột vào góc dưới bên phải của ô (trỏ chuột xuất hiện là dấu cộng màu đen) vừa nhập giữ chuột trái vừa kéo lên trên hoặc xuống dưới (có thể kéo theo hàng ngang nếu kết quả nằm ở hàng ngang)

-----------------------

Một số thao tác với bảng tính - 27/10/2006 10h:5

1, Định dạng Font mặc định cho bảng tính

Bài trước Quản Trị Mạng đã hướng dẫn bạn một số cách định dạng cơbản cho một vùng dữ liệu nhỏ, còn nếu bạn cần định dạng một vùng lớn hoặc định dạng mặc định cho một file mới thì bạn làm theo hướng dẫn sau.

a, Định dạng Font mặc định

- Vào Format -> Style…

Với các định dạng nhưsau:

- Style name: tên loại thuộc tính mà bạn sử dụng.

- Style includes: danh sách kèm theo thuộc tính các định dạng cần thiết

- Modify…: chỉnh sửa lại các thuộc tính định dạng

- Add: nhấn Add để lưu lại các thay đổi cần thiết trong một style.

- Delete: Xoá một style không cần thiết.

b, Bôi đen bảng tính

- Bôiđen toàn bộ bảng tính: Click chuột vào ô vuông trống giao nhau giữa tiêu đề dòng và tiêu đề cột.

Bôiđen dòng hoặc bôi đen cột: click chuột vào tên dòng hay cột cần bôi đen

2, Đánh số tự động

Một số cách để đánh số tự động:

a, Cách 1 (có thể áp dụng cách này cho những dãy số không liền nhau):

- Nhập 2 số đầu tiên của dãy số

- Bôi đen hai ô vừa nhập

- Đưa trỏ chuột vào phía dưới phải của vùng vừa bôi đen (đặt con trỏ giống nhưsao chép côngthức: xuất hiện con trỏ chuột là dấu cộng màu đen)

- Giữ chuột và kéo cho đến số cần thiết.

Chú ý: Bạn có thể kéo dãy số từ trên xuống, từ dưới lên, từ bên trái sáng và từ bên phải sang. Ngoài ra với cách này bạn đánh số cho những dãy số không liên nhau (các dãy số có cấp số cộng).

b, Cách 2:

- Nhập số đầu tiên của dãy số

Đưa trỏ chuột vào góc dưới phải của ô vừa nhập

- Giữ phím Ctrl + kéo chuột cho dãy số cần nhập

Vì ngày tháng là một dạng đặc biệt của kiểu số nên nếu bạn muốn điền nhanh ngày tháng thìcũng có thể sử dụng các cách trên.

3, Lấp đầy các giá trị số trên một vùng

Bảng tính Excel cho phép bạn lấp đầy giá trị số vào một vùng dữ liệu xác định trước (với tính năng này bạn cũng có thể điền số tự động được)

- Nhập giá trị vào ô đầu tiên

- Bôi đen vùng cần điền (có thể là cột hoặc dòng hoặc một vùng)

- Vào Edit -> Fill -> Series…

- Series in:

Rows:Điền giá trị theo dòng Columns:Điền giá trị theo cột

- Type:

Linear: theo tuyến tính (chiều dọc hoặc ngang) Growth: điền dãy số theo cấp số nhân Date: điền giá trị kiểu ngày tháng AutoFill: tự động điền số hoặc ngày tháng theo Step value là 1

- Date unit: chỉ áp dụng với điền giá trị kiểu ngày tháng

Day: tuần tự tăng, giảm theo ngày Weekday: tuần tự tăng, giảm theo ngày làm việc trong tuần (trừ ngày thứ 7 và Chủ nhật) Month: tuần tự tăng, giảm theo tháng Year: tuần tự tăng, giảm theo năm

- Step value: gõ vào giá trị bước nhảy (số âm: bước nhảy giảm, số dương: bước nhảy tăng

- Stop Value: giá trị tối đa của dãy số

Ngoài ra bạn có thể lấp đầy các giá trị nhanh hơn mà không cần vào hộp thoại Series: Vào Edit ->Fill

- Down: Lấp đầy xuống dưới

- Right: Lấp đầy sang phải

- Up: Lấp đầy lên trên

- Left: Lấp đầy sáng trái

4, Thao tác với dòng

a, Thêm dòng

- Với thao tác chèn dòng thì dòng mới sẽ được chèn lên trên dòng hiện tại.

- Cách 1: Đặt con trỏ tại dòng cần chèn, vào Insert -> Rows

- Cách 2: Click chuôt phải vào tên dòng cần chèn, chọn Insert

Cách 3: Click chuột phải vào ô tại dòng cần chèn, chọn Insert -> chọn vào Entire rows -> Ok

b, Xoá dòng

- Đặt con trỏ tại dòng cần xoá, vào Edit -> Delete… -> chọn Entire row -> Ok

- Click chuột phải vào tên dòng cần xoá, chọn Delete

c, Ẩn/Hiện dòng

Đôi khi với bảng tính quá dài bạn cần ẩn đi một số dòng không hay dùng cho dễ kiểm soát, bạn làm nhưsau

- Bôi đen các dòng cần ẩn, vào Format -> Row -> Hide

- Khi cần xem lại các dòng đã ẩn, bạn bôi đen hai dòng kề với vùng ẩn (nếu ẩn dòng 3 và 4 thì bạn phải bôi đen dòng 2-5) -> vào Format -> Row -> Unhide

d, Chiều cao của dòng

Nếu muốn thay đổi chiều cao của dòng bạn có thể sử dụng con trỏ chuột đưa vào đường phân cách giữa 2 tên dòng và kéo hoặc cũng có thể làm theo cách sau:

Vào Format -> Row -> Height , gõ vào chiều cao của dòng -> Ok

Bạn có thể fix chiều cao của ô tương ứng với dữ liệu có trong ô bằng cách vào Format -> Row ->AutoFit hoặc click đúp chuột vào đường phân cách giữa 2 tên dòng.

5, Thao tác với cột

a, Thêm cột

- Với thao tác chèn cột thì cột mới sẽ được chèn sang bên trái cột hiện tại.

- Cách 1: Đặt con trỏ tại cột cần chèn, vào Insert -> Columns

- Cách 2: Click chuôt phải vào tên cột cần chèn, chọn Insert

- Cách 3: Click chuột phải vào ô tại cột cần chèn, chọn Insert -> tick vào Entire columns -> Ok

b, Xoá cột

- Đặt con trỏ tại cột cần xoá, vào Edit -> Delete… -> chọn Entire column -> Ok

- Click chuột phải vào tên cột cần xoá, chọn Delete

c, Ẩn/Hiện cột

Đôi khi với bảng tính quá rộng bạn cần ẩn đi một số cột không cần thiết cho dễ kiểm soát, bạn làmnhưsau

- Bôi đen các cột cần ẩn, vào Format -> Column -> Hide

- Khi cần xem lại các cột đã ẩn, bạn bôi đen hai cột kề với vùng ẩn (nếu ẩn cột C, D và E thì bạn phải bôi đen từ cột B-F) -> vào Format -> Column -> Unhide

d,Độ rộng của cột

Nếu muốn thay đổi độ rộng của cột bạn có thể sử dụng con trỏ chuột đưa vàođường phân cách giữa 2 tên cột và kéo hoặc cũng có thể làm theo cách sau:

Vào Format -> Column -> Width…, gõ vào độ rộng -> Ok

Bạn có thể fix đô rộng của cột tương ứng với dữ liệu có trong cột bằng cách vào Format ->Column -> AutoFit Selection hoặc click đúp chuột vào đường phân cách giữa 2 tên cột.

6, Thao tác với ô

a, Chèn thêm ô

Đặt con trỏ tại ô cần thêm, vào Insert -> Cells… hoặc click chuột phải vào ô cần thêm chọn Insert

- Shift cells right: chèn thêm ô và dữ liệu sẽ đẩy sang bên phải

- Shift cells down: chèn thêm ô và dữ liệu sẽ bị đẩy xuống dưới

b, Xoá bớt ô

Đặt con trỏ tại ô cần xoá, vào Edit -> Delete… hoặc kích chuột phải vào ô cần xoá chọn Delete…

Shift cells left: xoá một ô và dữ liệu sẽ dồn sang bên trái

- Shift cells up: xoá ô và kéo dữ liệu từ dưới lên

7, Thao tác với Sheet (Worksheet)

a, Thêm sheet mới

- Worksheet mới sẽ được thêm vào bên trái của Sheet hiện tại

- Cách 1: Vào Insert -> Worksheet

- Cách 2: Click chuột phải vào tên sheet, chọn Insert... -> chọn Worksheet -> Ok

b, Thêm sheet là bản sao từ một sheet khác

Thực chất công việc này là bạn tạo ra một sheet mới có nội dung bên trong hoàn toàn giống với sheetđã có, thao tác nhưsau

- Click chuột phải vào tên sheet muốn sao chép chọn Move or Copy…

To book: là tên tệp đang chứa Sheet gốc

- Before sheet: là vị trí đặt Sheet mới ở trước sheet nào (trong ví dụ này là trước Sheet1 hoặc Sheet2 hoặc Sheet3 hoặc đặt ở cuối cùng

- Create a copy: là có tạo ra một bản copy hay không, nếu bạn không chọn lựa chọn này thì côngviệc bạn làm chỉ có tác dụng di chuyển Sheet.

c, Di chuyển và đổi tên Sheet

- Bạn có thể làm theo cách trên để di chuyển sheet hoặc đơn giản chỉ cần sử dụng chuột kéo thả sheet nào đó vào vị trí cần thiết.

- Nếu muốn đổi tên sheet nào thì bạn click đúp chuột vào tên sheetđó hoặc click chuột phải vàotên sheet cần đổi chọn Rename.

d, Xoá bỏ sheet

- Chú ý là khi xoá bỏ một sheet thì bạn không thể Redo lại được, vì vậy trước khi xoá sheet nàobạn phải chắc chắn.

- Cách 1: Click đúp chuột phải vào Sheet cần xoá, chọn Delete

- Cách 2: Chọn Sheet cần xoá, vào Edit -> Delete Sheet

Kiểm tra nhập liệu trong Excel - 25/11/2006 10h:14

Vềviệc kiểm tra nhập liệu trong Excel ngoài cách dùng VBA, còn có một cách khác đơn giản hơnđó là dùng công cụ Data Validation của Excel.

Ví dụ việc kiểm tra mã hàng, giả sử bạn có mã hàng chứa trong dãy A1:A20 của Sheet1, phần nhập dữ liệu mã hàng trong dãy A1:A10 trong Sheet2.

Để tiện quản lý dãy này ta đặt tên bằng cách chọn Insert Name Define, đặt tên vùngSheet1!A1:A20 là Mahang, nhấn OK.

Để nhập các điều kiện kiểm tra cho vùng này ta làm nhưsau:

Chuyển tới Sheet2 Chọn vùng A1:A10 Chọn Data Data Validation.

Ở thẻSettings, Validation Criteria chọn Allow: List, Source nhập = Mahang, đánh dấu chọn hộpIgnore Blank nếu muốn bỏ qua các ô trống, hộp In-cell dropdown nếu muốn có hộp danh sách thả xuống.

Chuyển qua thẻInput Messsage nhập các hướng dẫn, chọn Show input... nếu muốn hướng dẫn hiện lên khi ô được chọn.

Chuyển qua thẻError Alert nhập các thông tin cảnh báo khi nhập sai, chọn Show error... nếu muốn hiện thông tin cảnh báo.

Nhấn OK.

Giờ đây khi nhập liệu ở vùng Sheet2!A1:A10, một hộp Dropdown List xuất hiện và hầu nhưchắc chắn bạn không có cơhội để nhập sai mã hàng!

Ngoài ra, bạn có thể tham khảo các kỹ thuật thiết kế nhập liệu nâng cao khác ở các trang web sau:

- Tiếng Anh: www.contextures.com/tiptech.html, tải file ví dụwww.contextures.com/DataValSample.zip

- Tiếng Việt: www.webketoan.com, www.giaiphapexcel.com(http://www.giaiphapexcel.com/forum/showthread.php?t=98&highlight=Data+Validation).

Nguyễn Trọng ThànhEmail: [email protected]; [email protected]

Tài liệu học Microsoft Excel - 12/11/2004 12h:38

http://www.contextures.com/tiptech.html

http://www.contextures.com/DataValSample.zip

http://www.webketoan.com/

http://www.giaiphapexcel.com/

http://www.giaiphapexcel.com/forum/showthread.php?t=98&highlight=Data+Validation

Đây là tài liệu về Excel do Khoa Công nghệ thông tin trường ĐH Mở HN viết. Tài liệu nàyđược trình bày dưới dạng file PDF sử dụng phần mềm Acrobat Reader để đọc. Tài liệu hoàntoàn bằng tiếng Việt, bài viết được dạy dưới dạng hình ảnh + giải thích rất dễ học

Tài liệu dài: 75 trang

Dung lượng file: 658 Kb

Download:

Here

http://www.quantrimang.com/download/office/excel.pdf

van de chung excel

Technology

Transcript of van de chung excel