Formulas & Functions Excel 2007 - 3. Building Basic Formulas

[BNTT]

Naming Formulas
Đặt tên cho các Công thức

In Chapter 2, you learned how to set up names for often-used constants. You can apply a similar naming concept for frequently used formulas. As with the constants, the formula doesn’t physically have to appear in a cell. This not only saves memory, but it often makes your worksheets easier to read as well. Follow these steps to name a formula:
Ở Chương 2, 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. Choose Formulas, Define Name to display the New Name dialog box.
   Chọn Formulas, Define Name để hiển thị hộp thoại New Name.
   
   
2. Enter the name you want to use for the formula in the Name text box.
   Gõ tên mà bạn muốn đặt cho công thức trong khung Name.
   
   
3. In the Refers To box, enter the formula exactly as you would if you were entering it in a worksheet.
   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. Click OK.
   Nhấn OK.

Now you can enter the formula name in your worksheet cells (instead of the formula itself). For example, the following is the formula for the volume of a sphere (r is the radius of the sphere):
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):

So, assuming that you have a cell named Radius somewhere in the workbook, you could create a formula named, say, SphereVolume, and make the following entry in the Refers To box of the New Name dialog box (where PI() is the Excel worksheet function that returns the value of Pi):
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​

 

[BNTT]

Working with Links in Formulas


Working with Links in Formulas
Làm việc với các liên kết trong công thức

If you have data in one workbook that you want to use in another, you can set up a link between them. This action enables your formulas to use references to cells or ranges in the other workbook. When the other data changes, Excel automatically updates the link.
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.

For example, Figure 3.11 shows two linked workbooks. The Budget Summary sheet in the 2008 Budget-Summary workbook includes data from the Details worksheet in the 2008 Budget workbook. Specifically, the formula shown for cell B2 in 2008 Budget-Summary contains an external reference to cell R7 in the Details worksheet of 2008 Budget. If the value in R7 changes, Excel immediately updates the 2008 Budget-Summary workbook.
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)​

File download: 2008 Budget-Summary.xlsx và 2008 Budget.xlsx​

 

[BNTT]

Working with Links in Formulas


Understanding External References
Tìm hiểu các Tham chiếu ngoài

There’s no big mystery behind these links. You set up links by including an external reference to a cell or range in another workbook (or in another worksheet from the same workbook). In the example shown in Figure 3.11, all I did was enter an equals sign in cell B2 of the Budget Summary worksheet, and then click cell R7 in the Details worksheet.
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.

The only thing you need to be comfortable with is the structure of an external reference. Here’s the syntax:
Đ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 : The drive and directory in which the workbook is located, which can be a local path, a network path, or even an Internet address. You need to include the path only when the workbook is closed.
  Ổ đĩ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 : The name of the workbook, including an extension. Always enclose the workbook name in square brackets ([ ]). You can omit workbookname if you’re referencing a cell or range in another sheet of the same workbook.
  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 : The name of the worksheet’s tab. You can omit sheetname if reference is a defined name in the same workbook.
  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 : A cell or range reference, or a defined name.
  Một tham chiếu ô hoặc dãy, hoặc một tên đã được định nghĩa.

For example, if you close the 2008 Budget workbook, Excel automatically changes the external reference shown in Figure 3.11 to this (depending on the actual path of the file):
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​

 

[BNTT]

Working with Links in Formulas


Updating Links
Cập nhật các liên kết

The purpose of a link is to avoid duplicating formulas and data in multiple worksheets. If one workbook contains the information you need, you can use a link to reference the data without recreating it in another workbook.
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.

To be useful, however, the data in the dependent workbook should always reflect what actually is in the source workbook. You can make sure of this by updating the link, as explained here:
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:

• If both the source and the dependent workbooks are open, Excel automatically updates the link whenever the data in the source file changes.
  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.

• If the source workbook is open when you open the dependent workbook, Excel automatically updates the links again.
  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).

• If the source workbook is closed when you open the dependent workbook, Excel displays a Security Warning in the message bar, which tells you automatic updating of links has been disabled. In this case, click Options, click the Enable this Content option, and then click OK.
  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.

• If you didn’t update a link when you opened the dependent document, you can update it any time by choosing Data, Edit Links. In the Edit Links dialog box that appears (see Figure 3.12), click the link and then click Update Values.
  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)​

 

[BNTT]

Working with Links in Formulas


Changing the Link Source
Thay đổi nguồn của liên kết

If the name of the source document changes, you’ll need to edit the link to keep the data up-to-date. You can edit the external reference directly, or you can change the source by following these steps:
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. With the dependent workbook active, choose Data, Edit Links to display the Edit Links dialog box.
   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. Click the link you want to work with.
   Chọn liên kết mà bạn muốn sửa.
   
   
3. Click Change Source. Excel displays the Change Source dialog box.
   Nhấn Change Source. Excel hiển thị hộp thoại Change Source.
   
   
4. Find and then choose the new source document, and then click OK to return to the Edit Links dialog box.
   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. Click Close to return to the workbook.
   Nhấn Close để quay về bảng tính.

 

[BNTT]

Formatting Numbers, Dates, and Times


Formatting Numbers, Dates, and Times
Định dạng Số, Ngày Tháng và Thời Gian

One of the best ways to improve the readability of your worksheets is to display your data in a format that is logical, consistent, and straightforward. Formatting currency amounts with leading dollar signs, percentages with trailing percent signs, and large numbers with commas are a few of the ways you can improve your spreadsheet style.
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.

This section shows you how to format numbers, dates, and times using Excel’s built-in formatting options. You’ll also learn how to create your own formats to gain maximum control over the appearance of your data.
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.​

Numeric Display Formats
Các định dạng hiển thị Số

When you enter numbers in a worksheet, Excel removes any leading or trailing zeros. For example, if you enter 0123.4500, Excel displays 123.45. The exception to this rule occurs when you enter a number that is wider than the cell. In this case, Excel usually expands the width of the column to fit the number. However, in some cases, Excel tailors the number to fit the cell by rounding off some decimal places. For example, a number such as 123.45678 is displayed as 123.4568. Note that, in this case, the number is changed for display purposes only; Excel still retains the original number internally.
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.

When you create a worksheet, each cell uses this format, known as the General number format, by default. If you want your numbers to appear differently, you can choose from among Excel’s seven categories of numeric formats: Number, Currency, Accounting, Percentage, Fraction, Scientific, and Special.
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.

• Number formats — The number formats have three components: the number of decimal places (0–30), whether the thousands separator (,) is used, and how negative numbers are displayed. For negative numbers, you can display the number with a leading minus sign, in red, surrounded by parentheses, or in red surrounded by parentheses.
  Đị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 đỏ.

• Currency formats — The currency formats are similar to the number formats, except that the thousands separator is always used, and you have the option of displaying the numbers with a leading dollar sign ($) or some other currency symbol.
  Đị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 đó.

• Accounting formats — With the accounting formats, you can select the number of decimal places and whether to display a leading dollar sign (or other currency symbol). If you do use a dollar sign, Excel displays it flush left in the cell. All negative entries are displayed surrounded by parentheses.
  Đị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.

• Percentage formats — The percentage formats display the number multiplied by 100 with a percent sign (%) to the right of the number. For example, .506 is displayed as 50.6%. You can display 0–30 decimal places.
  Đị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.

• Fraction formats — The fraction formats enable you to express decimal quantities as fractions. There are nine fraction formats in all including displaying the number as halves, quarters, eighths, sixteenths, tenths, and hundredths.
  Đị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.

• Scientific formats — The scientific formats display the most significant number to the left of the decimal, 2–30 decimal places to the right of the decimal, and then the exponent. So, 123000 is displayed as 1.23E+05.
  Đị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.

• Special formats — The special formats are a collection designed to take care of special cases. Here’s a list of the special formats, with some examples:
  Đị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ụ:
  
  

 

[BNTT]

Formatting Numbers, Dates, and Times


Changing Numeric Formats
Thay đổi các định dạng số

The quickest way to format numbers is to specify the format as you enter your data. For example, if you begin a dollar amount with a dollar sign ($), Excel automatically formats the number as currency. Similarly, if you type a percent sign (%) after a number, Excel automatically formats the number as a percentage. Here are a few more examples of this technique. Note that you can enter a negative value using either the negative sign (–) or parentheses.
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.

Specifying the numeric format as you enter a number is fast and efficient because Excel guesses the format you want to use. Unfortunately, Excel sometimes guesses wrong (for example, interpreting a simple fraction as a date). In any case, you don’t have access to all the available formats (for example, displaying negative dollar amounts in red). To overcome these limitations, you can select your numeric formats from a list. Here are the steps to follow:
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. Select the cell or range of cells to which you want to apply the new format.
   Chọn ô hoặc dãy ô mà bạn muốn áp dụng kiểu định dạng mới.
   
   
2. Choose the Home tab.
   Chọn tab Home.
   
   
3. Pull down the Number Format list. Excel displays its built-in formats, as shown in Figure 3.13. Under the name of each format, Excel shows you how the current cell would be displayed if you chose that format.
   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. Click the format you want to use.
   Nhấp chọn loại định dạng bạn muốn dùng.

For more numeric formatting options, use the Number tab of the Format Cells dialog box. Select the cell or range and then choose Home, Number Format (or press Ctrl+1). As you can see in Figure 3.14, when you click a numeric format in the Category list, Excel displays more formatting options, such as the Decimal Places spin box. (The options you see depend on the category you choose.) The Sample information box shows a sample of the format applied to the current cell’s contents.
Để 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ấn Ctrl+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)​

 

[BNTT]

Formatting Numbers, Dates, and Times


Changing Numeric Formats (continued)
Thay đổi các định dạng số (tiếp theo bài trước)

As an alternative to the Format Cells dialog box, Excel offers several keyboard shortcuts for setting the numeric format. Select the cell or range you want to format, and use one of the key combinations listed in Table 3.6.
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.

If your mouse is nearby, you can use the controls in the Home tab’s Number group as another method of selecting numeric formats. The Number Format list (see Figure 3.13) lists all the formats. Here are the other controls that appear in this group:
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: Accounting (two decimal places; using dollar sign)
  Đị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: Percentage (zero decimal places)
  Định dạng kiểu Phần trăm (không có chữ số thập phân)

• Comma Style button: Number (two decimal places; using thousands separator)
  Đị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: Increases the number of decimal places in the current format
  Tăng số chữ số thập phân trong định dạng hiện hành

• Decrease Decimal button: Decreases the number of decimal places in the current format
  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)

 

[BNTT]

Formatting Numbers, Dates, and Times


Customizing Numeric Formats
Tùy biến các định dạng số

Excel numeric formats give you lots of control over how your numbers are displayed, but they have their limitations. For example, no built-in format enables you to display a number such as 0.5 without the leading zero, or to display temperatures using, for example, the degree symbol.
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.

To overcome these and other limitations, you need to create your own custom numeric formats. You can do this either by editing an existing format or by entering your own from scratch. The formatting syntax and symbols are explained in detail later in this section.
Để 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.

Every Excel numeric format, whether built-in or customized, has the following syntax:
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​

​

The four parts, separated by semicolons, determine how various numbers are presented. The first part defines how a positive number is displayed, the second part defines how a negative number is displayed, the third part defines how zero is displayed, and the fourth part defines how text is displayed. If you leave out one or more of these parts, numbers are controlled as shown here:
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:

Number of Parts Used / Format Syntax:
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

 

[BNTT]

Formatting Numbers, Dates, and Times


Customizing Numeric Formats (continued)
Tùy biến các định dạng số (tiếp theo bài trước)

Table 3.7 lists the special symbols you use to define each of these parts.
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ố

 

[BNTT]

Formatting Numbers, Dates, and Times


Customizing Numeric Formats (continued)
Tùy biến các định dạng số (tiếp theo bài trước)

Before looking at some examples, let’s run through the basic procedure. To customize a numeric format, select the cell or range you want to format and then follow these steps:
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. Choose Home, Number Format (or press Ctrl+1) and select the Number tab, if it’s not already displayed.
   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. In the Category list, click Custom.
   Trong danh sách Category, chọn Custom.
   
   
3. If you’re editing an existing format, choose it in the Type list box.
   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. Edit or enter your format code.
   Sửa lại hoặc nhập mã định dạng của bạn.
   
   
5. Click OK. Excel returns you to the worksheet with the custom format applied.
   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 stores each new format definition in the Custom category. If you edited an existing format, the original format is left intact and the new format is added to the list. You can select the custom formats the same way you select the built-in formats. To use your custom format in other workbooks, you copy a cell containing the format to that workbook. Figure 3.15 shows a dozen examples of custom formats.
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)​

Here’s a quick explanation for each example:
Giải thích sơ qua các ví dụ:

• Example 1 — These formats show how you can reduce a large number to a smaller, more readable one by using the thousands separator. A format such as 0,000.0 would display, for example, 12300 as 12,300.0. If you remove the three zeros between the comma and the decimal (to get the format 0,.0), Excel displays the number as 12.3 (although it still uses the original number in calculations). In essence, you’ve told Excel to express the number in thousands. To express a larger number in millions, you just add a second thousands separator.
  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 — Use this format when you don’t want to display any leading or trailing zeros.
  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 — These are examples of four-part formats. The first three parts define how Excel should display positive numbers, negative numbers, and zero. The fourth part displays the message "Enter a number" if the user enters text in the cell.
  Đâ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 — In this example, the cents sign (¢) is used after the value. To enter the cents sign, press Alt+0162 on your keyboard’s numeric keypad. (This won’t work if you use the numbers along the top of the keyboard.)
  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 — This example adds the text string “Dollars" to the format.
  Ví dụ này thêm chữ "Dollars" vào định dạng.

• Example 6 — In this example, an M is appended to any number, which is useful if your spreadsheet units are in megabytes.
  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 — This example uses the degree symbol (°) to display temperatures.
  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 — The three semicolons used in this example result in no number being displayed (which is useful as a basic method for hiding a sensitive value).
  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 — This example shows that you can get a number sign (#) to display in your formats by preceding # with a backslash (\).
  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 — In this example, you see a trick for creating dot trailers. Recall that the asterisk (*) symbol fills the cell with whatever character follows it. So, creating a dot trailer is a simple matter of adding "*." to the end of the format.
  Ở 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ự nào đứng sau nó. Do đó, để tạo một chuỗi các dấu chấm, bạn chỉ việc thêm "*." vào cuối cú pháp định dạng.

• Example 11 — This example shows a similar technique that creates a dot leader. Here the first three semicolons display nothing; then comes "*.", which runs dots from the beginning of the cell up to the text (represented by the @ sign).
  Ví dụ này là một kỹ thuật tương tự để tạo môt chuỗi các dấu chấm. Có 3 dấu chấm phẩy ( để không hiện ra bất kỳ con số nào, rồi đến "*.", để tạo ra một chuỗi các dấu chấm chạy từ đầu ô đến trước một văn bản nào đó (đại diện bằng dấu @). (Ví dụ này tạo ra một chuỗi các dấu chấm nhưng ở hướng ngược lại với ví dụ 10).

• Example 12 — This example shows a format that’s useful for entering stock quotations.
  Ví dụ này chỉ ra một kiểu định dạng hữu ích cho việc nhập một số ở dạng phân số.

Bạn có thể download file ví dụ ở đây để tự thực hành: Custom Cell Format Examples.xlsx​

 

[BNTT]

Formatting Numbers, Dates, and Times


Hiding Zeros
Ẩn các số không

Worksheets look less cluttered and are easier to read if you hide unnecessary zeros. Excel enables you to hide zeros either throughout the entire worksheet or only in selected cells.
Các bảng tính trông ít bề bộn hơn và dễ đọc hơn nếu bạn ẩn đi những con số không (0) không cần thiết. Excel cho phép bạn ẩn các số 0 trong toàn bộ bảng tính hoặc chỉ trong những ô được chọn.

To hide all zeros, choose Office, Excel Options; choose the Advanced tab in the Excel Options dialog box; and clear the Show a Zero In Cells That Have Zero Value check box.
Để ẩn tất cả các số 0, bạn chọn Office, Excel Options; chọn tab Advanced trong hộp thoại Excel Options; và bỏ tùy chọn Show a Zero In Cells That Have Zero Value.

To hide zeros in selected cells, create a custom format that uses the following format syntax:
Để ẩn những số 0 trong các ô được chọn, bạn tạo một kiểu định dạng tùy biến với cú pháp như sau:

positive format;negative format;​

The extra semicolon at the end acts as a placeholder for the zero format. Because there’s no definition for a zero value, nothing is displayed. For example, the format $#,##0.00_);($#,##0.00); displays standard dollar values, but it leaves the cell blank if it contains zero.
Dấu chẩm phẩy phụ ở cuối có tác dụng giữ chỗ cho một định dạng số 0 (phần thứ ba của cú pháp định dạng chuẩn). Bởi vì không có định nghĩa gì cho định dạng số 0, nên chả có gì hiển thị ra. Ví dụ, định dạng $#,##0.00_);($#,##0.00); hiển thị một giá trị tiền đô chuẩn, nhưng để ô trống nếu nó có giá trị bằng 0.

TIP: If your worksheet contains only integers (no fractions or decimal places), you can use the format #,### to hide zeros.
Nếu bảng tính của bạn chỉ chứa toàn số nguyên (không có phân số hoặc số thập phân), bạn có thể dùng kiểu định dạng #,### để ẩn đi các số 0.​

 

[BNTT]

Formatting Numbers, Dates, and Times


Using Condition Values
Sử dụng điều kiện trong định dạng

The action of the formats you’ve seen so far have depended on whether the cell contents were positive, negative, zero, or text. Although this is fine for most applications, sometimes you need to format a cell based on different conditions. For example, you might want only specific numbers, or numbers within a certain range, to take on a particular format. You can achieve this effect by using the [condition value] format symbol. With this symbol, you set up conditional statements using the logical operators =, <, >, <=, >=, and <>, and the appropriate numbers. You then assign these conditions to each part of your format definition.
Cho tới lúc này, bạn đã biết cách tự tạo kiểu định dạng để hiển thị một số khi nó là số dương, là số âm, là số không hay có chứa thêm giá trị text. Mặc dù như vậy thì cũng khá ổn cho hầu hết các ứng dụng, nhưng đôi khi bạn sẽ cần định dạng một ô dựa theo những điều kiện khác. Ví dụ, bạn chỉ muốn (nhập vào) các con số đặc biệt, hay những con số nằm trong một phạm vi nhất định, dựa trên một kiểu định dạng đặc biệt. Bạn có thể làm được điều này bằng cách sử dụng ký hiệu định dạng [condition value]. Với ký hiệu này, bạn tạo ra một mệnh đề chỉ điều kiện bằng cách sử dụng những ký tự logic như =, <, >, <=, >=, và <>, và những con số thích hợp. Và rồi bạn đặt những điều kiện này vào mỗi phần trong cú pháp định dạng của mình.

For example, suppose that you have a worksheet for which the data must be within the range –1,000 and 1,000. To flag numbers outside this range, you set up the following format:
Ví dụ, giả sử bạn có một bảng tính mà dữ liệu trong đó phải nằm trong khoảng -1,000 đến 1,000. Để đánh dấu những con số nằm ngoài khoảng này, bạn tạo kiểu định dạng sau:

[>=1000]"Error: Value >= 1,000";[<=-1000]"Error: Value <= -1,000";0.00​

The first part defines the format for numbers greater than or equal to 1,000 (an error message).
Phần thứ nhất định nghĩa định dạng cho những số lớn hơn hoặc bằng 1,000 (có một thông báo lỗi).

The second part defines the format for numbers less than or equal to –1,000 (also an error message). The third part defines the format for all other numbers (0.00).
Phần thứ hai định nghĩa dịnh dạng cho những con số nhỏ hơn hay bằng -1,000 (cũng có một thông báo lỗi). Phần thứ ba định nghĩa định dạng cho tất cả những con số còn lại (0.00).

➔ You’re better off using Excel’s extensive conditional formatting features; see "Applying Conditional Formatting to a Range".
Tốt hơn, bạn nên sử dụng những định dạng có điều kiện với phạm vi rộng hơn. Xem lại bài "Áp dụng định dạng có điều kiện cho môt dãy" (từ bài 21 trở đi).​

 

[BNTT]

Formatting Numbers, Dates, and Times


Date and Time Display Formats
Các định dạng hiển thị ngày tháng và thời gian

If you include dates or times in your worksheets, you need to make sure that they’re presented in a readable, unambiguous format. For example, most people would interpret the date 5/8/07 as August 5, 2007. However, in some countries, this date would mean May 8, 2007. Similarly, if you use the time 2:45, do you mean a.m. or p.m.? To avoid these kinds of problems, you can use Excel’s built-in date and time formats, listed in Table 3.8.
Nếu bạn đưa vào bảng tính các giá trị ngày tháng và thời gian, cần bảo đảm chúng được trình bày theo một định dạng dễ đọc, rõ ràng. Ví dụ, hầu hết người ta hiểu rằng ngày 5/8/07 là ngày 5 tháng Tám năm 2007. Tuy nhiên, ở một vài quốc gia, thì ngày này lại được hiểu là ngày 8 tháng Năm năm 2007. Tương tự, nếu bạn sử dụng một giá trị thời gian là 2:45, bạn muốn nói đến thời gian buổi sáng hay buổi chiều? Để tránh những loại vấn đề như thế này, bạn có thể sử dụng các định dạng ngày tháng và thời gian có sẵn của Excel, được liệt kê ở bảng 3.8.

Table 3.8 - Excel’s Date and Time Formats
Bảng 3.8 - Các định dạng ngày tháng và thời gian trong Excel

The [h]:mm:ss (or [m]:ss or ) format requires a bit more explanation. You use this format when you want to display hours greater than 24 or minutes and seconds greater than 60. For example, suppose that you have an application in which you need to sum several time values (such as the time you’ve spent working on a project). If you add, say, 10:00 and 15:00, Excel normally shows the total as 1:00 (because, by default, Excel restarts times at 0 when they hit 24:00). To display the result properly (that is, as 25:00), use the format [h]:00.
Giải thích thêm về định dạng [h]:mm:ss (hay [m]:ss hoặc ). Bạn sử dụng định dạng này khi muốn hiển thị số giờ lớn hơn 24, hoặc số phút hay số giây lớn hơn 60. Ví dụ, bạn có một ứng dụng, trong đó bạn cần tính tổng một vài giá trị thời gian (chẳng hạn như thời gian dành để thực hiện một dự án). Nếu bạn cộng 10:00 và 15:00, theo bình thường, Excel hiển thị số tổng là 1:00 (bởi vì theo mặc định, Excel bắt đầu tính lại thời gian là 0 khi đạt đến 24 giờ). Để hiển thị kết quả cách chính xác (nghĩa là 25:00), bạn dùng định dạng [h]:00.

You use the same methods you used for numeric formats to select date and time formats. In particular, you can specify the date and time format as you input your data. For example, entering Jan-07 automatically formats the cell with the mmm-yy format. Also, you can use the following shortcut keys:
Bạn dùng những phương pháp tương tự với cách bạn đã sử dụng trong các định dạng số để chọn các định dạng ngày tháng và thời gian. Đặc biệt, bạn có thể xác định kiểu định dạng ngày tháng và thời gian ngay khi bạn nhập dữ liệu. Ví dụ, nếu bạn nhập Jan-07 vào một ô, Excel sẽ tự động định dạng cho ô đó với kiểu mmm-yy. Bạn cũng có thể sử dụng những phím tắt sau đây:

Ctrl+Shift+3 : dd-mmm-yy

Ctrl+Shift+2 : h:mm AM/PM

Ctrl+; : Current day - ngày tháng năm hiện hành (dd/mm/yyyy)

Ctrl+Shift+; : Current time - thời gian hiện hành (h:mm AM/PM)​

(Ghi chú thêm: kết quả của những phím tắt trên còn phụ thuộc vào cách thiết lập hiển thị thời gian của hệ thống - nghĩa là phụ thuộc vào những xác lập trong Regional Options của Control Panel)​

 

[BNTT]

Formatting Numbers, Dates, and Times


Customizing Date and Time Formats
Tùy biến Định dạng Ngày Tháng và Thời Gian

Although the built-in date and time formats are fine for most applications, you might need to create your own custom formats. For example, you might want to display the day of the week (for example, Friday). Custom date and time formats generally are simpler to create than custom numeric formats. There are fewer formatting symbols, and you usually don’t need to specify different formats for different conditions. Table 3.10 lists the date and time formatting symbols.
Mặc dù các kiểu định dạng ngày tháng và thời gian có sẵn cũng đủ dùng cho hầu hết các ứng dụng, nhưng có thể bạn cũng thấy cần tạo thêm các kiểu định dạng theo ý riêng của mình. Ví dụ, bạn có thể muốn hiển thị tên của ngày trong tuần (Firday chẳng hạn - ở VN chúng ta gọi là "thứ"). Các kiểu định dạng ngày tháng và thời gian tùy biến dễ tạo hơn các kiểu dịnh dạng số. Có ít ký hiệu định dạng hơn và bạn thường không cần phải xác định các định dạng khác nhau cho những điều kiện khác nhau. Bảng 3.9 liệt kê các ký hiệu cho định dạng ngày tháng và thời gian.

Table 3.9 The Date and Time Formatting Symbols
Bảng 3.9 - Các ký hiệu định dạng ngày tháng và thời gian

Figure 3.16 shows some examples of custom date and time formats.
Hình 3.16 là một số minh họa về các kiểu định dạng ngày tháng và thời gian tùy biến.

Figure 3.16 (Hình 3.16)​

Bạn có thể download file ví dụ ở đây để tự thực hành: Custom Cell Format Examples.xlsx​

 

[BNTT]

Formatting Numbers, Dates, and Times


Deleting Custom Formats
Xóa các kiểu định dạng tùy biến

The best way to become familiar with custom formats is to try your own experiments. Just remember that Excel stores each format you try. If you find that your list of custom formats is getting a bit unwieldy or that it’s cluttered with unused formats, you can delete formats by following the steps outlined here:
Cách tốt nhất để làm quen với việc tự tạo ra các kiểu định dạng là thử nghiệm. Chỉ việc nhớ rằng, Excel sẽ lưu lại các kiểu định dạng mà bạn thử. Nếu bạn thấy danh sách các kiểu định dạng tùy biến hơi cồng kềnh hoặc trở nên bề bộn với những loại định dạng không được sử dụng, bạn có thể xóa bớt chúng đi theo các bước sau đây:

1. Choose Home, Number Format (or press Ctrl+1)
   Chọn Home, Number Format (hoặc nhấn Ctrl+1)
   
   
2. Click the Custom category.
   Chọn mục Custom.
   
   
3. Click the format in the Type list box. (Note that you can delete only the formats you’ve created yourself.)
   Chọn loại định dạng trong hộp danh sách Type (nhớ rằng bạn chỉ có thể xóa những định dạng mà do bạn tự tạo ra).
   
   
4. Click Delete. Excel removes the format from the list.
   Nhấn Delete. Excel sẽ bỏ kiểu định dạng đó ra khỏi danh sách.
   
   
5. To delete other formats, repeat steps 2 through 4.
   Để xóa kiểu định dạng khác, lập lại từ bước 2 đến bước 4.
   
   
6. Click OK. Excel returns you to the spreadsheet.
   Nhấn OK để quay về bảng tính.

Hết chương 3 - Mời xem tiếp chương 4: Creating Advanced Formulas (Tạo các công thức nâng cao)