Thủ Thuật Excel: Thiết lập các công thức đơn giản (phần 2)

Chia sẻ: Le Linh Lan | Ngày: | Loại File: PDF | Số trang:28

Thêm vào BST

Báo xấu

143
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Ở bài : “Dùng tên cho dãy (range name)”, bạn đã học cách đặt tên cho các hằng số thường được sử dụng. Bạn có thể áp dụng cách tương tự như vậy cho những công thức thường được sử dụng. Cũng như các hằng số, công thức (đã được đặt tên sẽ) không xuất hiện trong một ô nào.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Thủ Thuật Excel: Thiết lập các công thức đơn giản (phần 2)

Học Excel - Thủ Thuật Excel
Thiết lập các công thức đơn giản (phần 2) Hướng dẫn thiết lập các công thức đơn giản (phần 2): Đặt tên cho các Công thức Ở bài : “Dùng tên cho dãy (range name)”, bạn đã học cách đặt tên cho các hằng số thường được sử dụng. Bạn có thể áp dụng cách tương tự như vậy cho những công thức thường được sử dụng. Cũng như các hằng số, công thức (đã được đặt tên sẽ) không xuất hiện trong một ô nào. Nó sẽ được lưu trong bộ nhớ và làm cho bảng tính của bạn dễ đọc hơn. Để đặt tên cho một công thức, bạn theo các bước sau đây: 1. Chọn Formulas, Define Name để hiển thị hộp thoại New Name. 2. Gõ tên mà bạn muốn đặt cho công thức trong khung Name. 3. Trong khung Refers To, nhập chính xác công thức giống như bạn nhập trong bảng tính. 4. Nhấn OK. Bây giờ bạn có thể nhập tên của công thức trong các ô của bảng tính (thay vì nhập công thức đó). Ví dụ, công thức sau đây tính thể tích của một hình cầu (với r là bán kính hình cầu): Vậy, giả sử rằng bạn có một ô tên là Radius (ô này có chứa con số chỉ bán kính của hình cầu, r) nằm đâu đó trong bảng tính, bạn có thể tạo một công thức có tên là SphereVolume, và nhập công thức sau đây trong
khung Refers To của hộp thoại New Name (với PI() là hàm trả về giá trị con số Pi): = (4 * PI() * Radius ^ 3) / 3 Làm việc với các liên kết trong công thức Nếu bạn có dữ liệu trong một bảng tính mà bạn muốn dùng chúng trong một bảng tính khác, bạn có thể thiết lập một liên kết giữa chúng. Cách làm này cho phép các công thức của bạn sử dụng các tham chiếu đến những ô hay những dãy trong một bảng tính khác. Khi dữ liệu (ở những bảng tính) khác thay đổi, Excel tự động cập nhật các liên kết. Ví dụ, hình 3.11 minh họa hai bảng tính đã được liên kết. Sheet Budget Summary trong bảng tính 2008 Budget-Summary chứa dữ liệu từ sheet Detail trong bảng tính 2008 Budget. Cụ thể hơn, công thức bạn thấy ở ô B2 trong bảng tính 2008 Budget-Summary chứa một tham chiếu ngoài dẫn đến ô R7 trong bảng tính 2008 Budget. Nếu giá trị ở R7 thay đổi, Excel sẽ cập nhật bảng tính 2008 Budget-Summary ngay tức khắc.
Figure 3.11 (Hình 3.11) Tìm hiểu các Tham chiếu ngoài Chẳng có gì bí ẩn đằng sau các liên kết này. Bạn thiết lập các liên kết bằng cách đưa vào một liên kết đến một ô hay một dãy trong một bảng tính khác (hay một sheet khác trong cùng bảng tính). Trong ví dụ được minh họa ở hình 3.11, tất cả những gì tôi làm là nhập một dấu bằng trong ô B2 của sheet
Budget Summary, và rồi nhấp chuột vào ô R7 của sheet Details. Điều duy nhất bạn cần làm quen là cấu trúc của một tham chiếu ngoài. Sau đây là cú pháp: ‘path[workbookname]sheetname’!reference · path : Ổ đĩa và thư mục chứa bảng tính (cần liên kết tới), đây có thể là một đường dẫn cục bộ, một đường dẫn mạng, hoặc thậm chí là một địa chỉ Internet. Bạn cần đưa vào đường dẫn (path) này chỉ khi bảng tính (cần liên kết tới) đã được đóng lại. · workbookname : Tên của bảng tính, kể cả phần mở rộng. Luôn luôn đặt tên của bảng tính trong một cặp dấu ngoặc vuông ([ ]). Bạn có thể bỏ qua tên của bảng tính nếu bạn tham chiếu đến một ô hoặc một dãy trong một sheet khác của cùng bảng tính (và dĩ nhiên là cũng không cần đến path). · sheetname : Tên của worksheet. Bạn có thể bỏ qua tên này nếu tham chiếu đến một tên đã được định nghĩa trong cùng một bảng tính. · reference : Một tham chiếu ô hoặc dãy, hoặc một tên đã được định nghĩa. Ví dụ, nếu bạn đóng bảng tính 2008 Budget, thì Excel sẽ tự động thay đổi tham chiếu ngoài được minh họa trong hình 3.11 như sau (ví dụ này còn phụ thuộc vào đường dẫn thực tế của bảng tính): = ‘D:\GPE\[2008 Budget.xlsx]Details’!$R$7 Cập nhật các liên kết Mục đích của một liên kết là tránh lặp lại các công thức và dữ liệu trong nhiều bảng tính. Nếu một bảng tính chứa thông tin mà bạn cần, bạn có thể sử dụng một liên kết để tham chiếu dữ liệu mà không cần tái tạo nó trong một
bảng tính khác. Tuy nhiên, để trở nên hữu ích, dữ liệu trong bảng tính phụ thuộc (bảng tính có chứa liên kết) luôn luôn phải phản ánh nội dung thực sự của bảng tính nguồn (bảng tính chứa dữ liệu được liên kết). Bạn có thể chắc chắn về điều này bẳng cách cập nhật liên kết, như được giải thích sau đây: · Nếu cả bảng tính nguồn và bảng tính phụ thuộc đang được mở, Excel sẽ tự động cập nhật liên kết bất kỳ khi nào dữ liệu nguồn có thay đổi. ·Nếu bảng tính nguồn đang được mở trước khi bạn mở bảng tính phụ thuộc, Excel cũng tự động cập nhật lại các liên kết (trong bảng tính phụ thuộc). · Nếu bảng tính nguồn chưa được mở khi bạn mở bảng tính phụ thuộc, Excel sẽ xuất hiện một thông báo Security Warning ở thanh thông báo (nằm cùng chỗ với thanh trạng thái – status bar), nhằm báo cho bạn biết rằng viêc tự động cập nhật liên kết đang tắt. Trong trường hợp này, bạn nhấn Options, nhấn tùy chọn Enable this Content rồi nhấn OK. · Nếu như bạn không cập nhật các liên kết khi bạn mở bảng tính phụ thuộc, bạn có thể cập nhật các liên kết này bất cứ lúc nào bằng cách chọn Data, Edit Links. Trong hộp thoại Edit Links vừa xuất hiện (xem hình 3.12), nhấn vào liên kết và nhấn Update Values.
Figure 3.12 (hình 3.12) Thay đổi nguồn của liên kết Nếu tên của tập tin nguồn thay đổi, bạn cần phải sửa lại liên kết để cập nhật dữ liệu. Bạn có thể sửa trực tiếp tham chiếu ngoài, hoặc bạn có thể thay đổi nguồn (của liên kết) bằng cách làm theo các bước sau đây: 1. Với bảng tính phụ thuộc được kích hoạt, bạn chọn Data, Edit Links để mở hộp thoại Edit Links. 2. Chọn liên kết mà bạn muốn sửa. 3. Nhấn Change Source. Excel hiển thị hộp thoại Change Source. 4. Tìm và chọn tài liệu nguồn mới, và rồi nhấn OK để trở về hộp thoại Edit Links. 5. Nhấn Close để quay về bảng tính. Định dạng Số, Ngày Tháng và Thời Gian Một trong những cách tốt nhất để làm cho các bảng tính của bạn trở nên dễ đọc là hiển thị dữ liệu theo một định dạng logic, nhất quán, và đơn giản. Định
dạng các lượng tiền tệ có các dấu đô-la ($) đứng ở đầu, tỷ lệ phần trăm có dấu phần trăm (%) đứng ở sau, và các số lớn được phân cách hàng ngàn bẳng dấu phẩy… là một vài cách mà bạn có thể cải thiện kiểu dáng bảng tính của bạn. Phần này sẽ hướng dẫn bạn cách để định dạng số, ngày tháng, và thời gian bằng cách dùng các tùy chọn định dạng có sẵn trong Excel. Bạn cũng sẽ học cách tạo các định dạng riêng của mình để có toàn quyền với diện mạo của dữ liệu. Các định dạng hiển thị Số Khi bạn nhập các con số trong một bảng tính, Excel sẽ loại bỏ bất kỳ số 0 (zero) nào đứng ở phía trước (số nguyên) hoặc phía sau (số lẻ). Ví dụ, nếu bạn nhập 0123.4500, Excel sẽ hiển thị nó là 123.45. Ngoại lệ cho quy tắc này xảy ra khi bạn nhập một con số dài hơn chiều rộng của ô. Trong trường hợp này, Excel thường điều chỉnh độ rộng của cột để chứa vừa con số này. Tuy nhiên, trong một số trường hợp, Excel tùy biến số để chứa vừa trong ô bằng cách làm tròn một vài con số ở hàng thập phân. Ví dụ, một con số như 123.45678 được hiển thị là 123.4568. Chú ý rằng, việc làm tròn số này chỉ là để hiển thị, còn Excel vẫn giữ lại con số gốc bên trong. Khi bạn tạo một bảng tính, mỗi ô (trong bảng tính mới này) sẽ mặc định sử dụng định dạng này, gọi là định dạng số kiểu General. Nếu bạn muốn những con số của mình xuất hiện khác nhau, bạn có thể chọn một trong 7 kiểu định dạng số của Excel: Number, Currency, Accounting, Percentage, Fraction, Scientific, và Special.
· Định dạng Số — Các định dạng số có 3 thành phần: có bao nhiêu số thập phân (0-30), có sử dụng dấu phân cách hàng ngàn hay không, và các số âm được hiển thị như thế nào. Đối với các số âm, bạn có thể hiển thị số với dấu trừ đứng trước, hoặc là màu đỏ, hoặc là nằm trong một cặp dấu ngoặc đơn, hay là số màu đỏ nằm trong cặp dấu ngoặc đơn màu đỏ. · Định dạng Tiền Tệ — Các định dạng tiền tệ tương tự như các định dạng số, ngoại trừ việc dấu phân cách hàng ngàn sẽ luôn được sử dụng, và bạn có thể tùy chọn để hiển thị số với dấu đô-la ($) ở trước hoặc là một ký hiệu đơn vị tiền tệ nào đó. · Định dạng Kế Toán — Với các định dạng kế toán, bạn có thể chọn có bao nhiêu con số ở hàng thập phân và việc có hiển thị dấu đô-la (hoặc đơn vị tiền tệ nào khác) ở trước hay không. Nếu bạn sử dụng dấu đô-la thì Excel sẽ hiển thị nó ở sát mép trái của ô. Tất cả các số âm được hiển thị ở bên trong cặp dấu ngoặc đơn. · Định dạng Phần Trăm — Định dạng phần trăm hiển thị số được nhân với 100 và có một dấu phần trăm (%) ở cuối. Ví dụ, .506 hiển thị dưới dạng 50.6%. Bạn có thể sử dụng đến 30 con số thập phân. · Định dạng Phân Số — Các định dạng phân số cho phép bạn biểu diễn các đại lượng thập phân dưới dạng các phân số. Có tất cả 9 kiểu định dạng phân số, bao gồm một phần hai, một phần tư, một phần tám, một phần mười sáu, một phần mười, và một phần trăm. · Định dạng Khoa Học — Các định dạng khoa học hiển thị con số có nghĩa nhất nằm bên trái dấu chấm thập phân, 2 đến 30 số thập phân nằm bên phải dấu chấm thập phân, và sau đó là số mũ. Vậy, 123000 hiển thị là 1.23E+05.
· Định dạng đặc biệt — Các định dạng đặc biệt là một tập hợp được thiết kế sẵn để xử lý những trường hợp đặc biệt. Đây là một danh sách những định dạng đặc biệt (kiểu Mỹ), với vài ví dụ: Thay đổi các định dạng số Cách nhanh nhất để định dạng các con số là xác định kiểu định dạng ngay khi bạn nhập dữ liệu. Ví dụ, nếu bạn bắt đầu một con số tiền tệ với một dấu đô-la ($), Excel sẽ tự động định dạng con số đó với dạng tiền tệ. Tương tự, nếu bạn nhập một dấu phần trăm (%) sau một số, Excel tự động định dạng số này ở dạng là một tỷ lệ phần trăm. Sau đây là một vài ví dụ về kỹ thuật này. Chú ý rằng, bạn có thể nhập một giá trị âm bằng cách sử dụng dấu âm (-) hoặc nhập số đó trong một cặp dấu ngoặc đơn.
Xác định định dạng số ngay khi nhập một con số thì nhanh và hiệu quả bởi vì Excel đoán được định dạng mà bạn muốn sử dụng. Nhưng thật không may, đôi khi Excel đoán sai (ví dụ, nó hiểu một phân số đơn giản là ngày tháng – xem ví dụ cuối cùng ở trên). Trong những trường hợp này, bạn không cần phải truy cập đến tất cả các định dạng (ví dụ, hiển thị số tiền âm bằng màu đỏ). Mà để khắc phục những số (bị định dạng sai) này, bạn có thể chọn các kiểu định dạng số từ một danh sách. Sau đây là các bước bạn cần làm theo: 1. Chọn ô hoặc dãy ô mà bạn muốn áp dụng kiểu định dạng mới. 2. Chọn tab Home. 3. Nhấn Number Format để mở ra một danh sách. Excel hiển thị các kiểu định dạng được cài sẵn của nó như ở hình 3.13. Bên dưới mỗi loại định dạng, Excel (còn) cho thấy ô hiện hành sẽ hiển thị như thế nào nến bạn chọn loại định dạng đó.
Figure 3.13 (Hình 3.13) 4. Nhấp chọn loại định dạng bạn muốn dùng. Để có thêm các tùy chọn khi định dạng cho số, bạn sử dụng tab Number của hộp thoại Format Cells. Bạn chọn ô hoặc dãy ô (muốn định dạng) rồi chọn Home, Number Format (hoặc nhấnCtrl+1). Như bạn thấy ở hình 3.14, khi bạn nhấp vào một định dạng số trong danh sách Category, Excel hiển thị
thêm các tùy chọn định dạng, chẳng hạn như hộp Decimal Places (dùng để xác định số con số sau dấu chấm thập phân). (Các tùy chọn bạn thấy phụ thuộc vào hạng mục mà bạn chọn). Khung Sample cho bạn xem trước kiểu định dạng của con số mà bạn sẽ áp dụng vào ô hiện hành. Figure 3.14 (Hình 3.14) Excel cũng cung cấp cho bạn một vài phím tắt để thay thế cho hộp thoại Format Cell, khi xác lập một kiểu định dạng số. Bạn chọn ô hay dãy ô muốn định dạng, và sử dụng một trong các tổ hợp phím tắt được liệt kê trong bảng 3.6.
Nếu dùng chuột, bạn có thể sử dụng các nút điều khiển trong nhóm Number của tab Home như là một phương pháp khác để chọn các kiểu định dạng số. Danh sách Number Format (xem hình 3.13) liệt kê tất cả các định dạng. Sau đây là những nút điều khiển khác của nhóm này: · Accounting Style button: Định dạng kiểu Tiền tệ của kế toán (có hai chữ số thập phân, sử dụng dấu đô-la) · Percent Style button: Định dạng kiểu Phần trăm (không có chữ số thập phân)
· Comma Style button: Định dạng kiểu Số (có hai chữ số thập phân, dùng dấu phân cách hàng ngàn) · Increase Decimal button: Tăng số chữ số thập phân trong định dạng hiện hành · Decrease Decimal button: Giảm số chữ số thập phân trong định dạng hiện hành (tương đương với viêc làm tròn số lên) Tùy biến các định dạng số Các định dạng số (có sẵn) của Excel cho bạn lựa chọn nhiều kiểu định dạng số, nhưng chúng có những giới hạn. Ví dụ, không có định dạng có sẵn nào cho phép hiển thị một số như là 0.5 mà không có số 0 đứng đầu (.5), hoặc hiển thị các nhiệt độ có thêm ký kiệu nhiệt độ ở sau. Để khắc phục những giới hạn trên và những giới hạn khác nữa, bạn cần tạo ra các kiểu định dạng số riêng của mình. Bạn có thể làm điều này bằng cách sửa một định dạng hiện có hoặc bằng cách tạo mới một định dạng của bạn ngay từ đầu. Cú pháp định dạng và các ký hiệu sẽ được giải thích chi tiết ở phần sau. Mỗi loại định dạng số trong Excel, có sẵn hay là tự tạo, đều có chung cú pháp sau đây: positive format;negative format;zero format;text format Bốn phần này, tách biệt nhau bằng dấu chấm phẩy (;), quyết định các số khác nhau được trình bày như thế nào. Phần đầu tiên định nghĩa cách hiển thị một số dương; phần thứ hai định nghĩa cách hiển thị một số âm; phần thứ ba định nghĩa cách hiển thị số 0 (zero) và phần thứ tư định nghĩa cách hiển thị text.
Nếu bạn bỏ qua một hay nhiều thành phần, cú pháp định dạng các số sẽ theo như sau đây: Số thành phần được dùng / Cú pháp định dạng: · Three (3 thành phần): positive format;negative format;zero format · Two (2 thành phần): positive and zero format; negative format · One (1 thành phần): positive, negative, and zero format Bảng 3.7 liệt kê các ký hiệu đặc biệt mà bạn sử dụng để định nghĩa trong những phần này:
Table 3.7 – Numeric Formatting Symbols Bảng 3.7 – Các ký hiệu định dạng số
Trước khi xem một số ví dụ, bạn hãy lướt qua các bước cơ bản. Để tùy biến một kiểu định dạng số, bạn chọn ô hay dãy mà bạn muốn định dạng và sau đó theo các bước sau: 1. Chọn Home, Number Format (hoặc nhấn Ctrl+1) và chọn tab Number tab nếu nó chưa hiển thị. 2. Trong danh sách Category, chọn Custom. 3. Nếu bạn muốn sửa lại một kiểu định dạng đã có, bạn chọn nó trong danh sách Type. 4. Sửa lại hoặc nhập mã định dạng của bạn. 5. Nhấn OK. Excel đưa bạn quay trở lại bảng tính với kiểu định dạng tùy ý được áp dụng. Excel lưu giữ mỗi kiểu định dạng mới trong mục Custom. Nếu bạn đã sửa một kiểu định dạng có sẵn, kiểu định dạng gốc được giữ nguyên vẹn và kiểu định dạng mới được thêm vào danh sách. Bạn có thể chọn các kiểu định dạng tùy biến giống như cách bạn chọn một kiểu định dạng có sẵn. Để dùng những kiểu định dạng tùy biến trong bảng tính khác, bạn sao chép một ô có chứa kiểu định dạng mới này sang bảng tính đó. Hình 3.15 sau đây minh họa 12 ví dụ về các kiểu định dạng tùy biến.
Figure 3.15 (Hình 3.15) Giải thích sơ qua các ví dụ: · Example 1 — Các kiểu định dạng này chỉ cho bạn cách viết gọn lại một số (từ một con số dài trở thành con số ngắn hơn), dễ đọc hơn, bằng cách dùng dấu phân cách hàng ngàn. Ví dụ, một định dạng như 0,000.0 sẽ hiển thị con số 123000 thành 12,300.0. Nếu bạn bớt đi ba con số zero giữa dấu phẩy và dấu chấm thập phân (để có định dạng 0,.0), Excel sẽ hiển thị con số dó là 12.3 (nhưng nó vẫn giữ nguyên con số gốc trong việc tính toán). Bản chất của việc này là bạn bảo Excel hiểu đơn vị tính của con số này là hàng ngàn. Để dùng đơn vị tính của một con số là hàng triệu, bạn chỉ cần thêm một dấu phân cách hàng ngàn nữa (xem hàng dưới của ví dụ 1, trong kiểu định dạng này, có thêm chú thích về đơn vị tính).
· Example 2 — Dùng loại định dạng này khi bạn không muốn hiển thị bất kỳ con số không nào (ở đầu và ở cuối số, sau dấu chấm thập phân). · Example 3 — Đây là những ví dụ về cách dùng phần thứ tư trong cú pháp định dạng. Ba phần đầu xác định cách Excel hiển thị số dương, số âm và số không. Phần thứ tư hiển thị một thông báo “Enter a number” (nhập vào một con số) nếu người dùng lỡ nhập một cái gì đó không phải là số trong ô này. · Example 4 —Trong ví dụ này, ký hiệu tiền xu (¢) được dùng sau giá trị. Để nhập ký hiệu này, bạn nhấn Alt+0162 bằng nhóm phím số ở bên phải bàn phím (dùng những phím số ở phía trên của bàn phím sẽ không có tác dụng). · Example 5 — Ví dụ này thêm chữ “Dollars” vào định dạng. · Example 6 — Trong ví dụ này, một chữ M được nối vào sau mỗi con số, dùng để chỉ đơn vị tính của con số này là megabyte. · Example 7 —Ví dụ này thêm ký hiệu chỉ độ (°) để hiển thị một con số chỉ nhiệt độ. (Mã ANSI của dấu ° này là Atl+0176). · Example 8 — Ba dấu chấm phẩy (;;;) dùng trong ví dụ này sẽ cho kết quả là không con số nào được hiển thị (là một trong những cách để dấu một con số). (Bạn yên tâm, dù không thấy nhưng con số trong ô được định dạng kiểu này vẫn hiện diện ở đó, và vẫn dùng để tính toán như bình thường). · Example 9 — Ví dụ này cho thấy bạn có thể hiển thị một ký tự # trong định dạng bằng cách đặt ký tự # này sau một dấu gạch chéo lùi (\). · Example 10 — Ở các ví dụ này, bạn thấy một cái mẹo để tạo một chuỗi các dấu chấm. Xin nhắc lại là ký hiệu dấu sao (*) sẽ lấp đầy một ô bất kỳ ký tự