Seperti untuk melakukan aktifitas pengurutan (sort), memfilter, mempercantik tampilan serta mempermudah dalam penulisan serta menggunakan rumus-rumus excel. Membuat tabel di excel ini bukan hanya sekedar memberikan border atau garis-garis pembatas pada sebuah range data. Tabel data adalah rentang sel yang memperlihatkan cara mengubah satu atau dua variabel dalam rumus akan mempengaruhi hasil rumus tersebut. Tabel data menyediakan pintasan untuk menghitung beberapa hasil di satu operasi dan cara untuk menampilkan dan membandingkan hasil dari berbagai variasi bersama-sama pada lembar.
Those who are familiar with the rich formatting features of Excel may long for a way to format numbers in a Word table just as easily. For instance, you may have a column of numbers that you want to format as currency, complete with the dollar sign. Unfortunately, Word does not allow you to do this type of formatting directly.There is a work-around, however. You can use a field to insert any number you want in any format you want.
For instance, let's say you have the number 65.78, and you want it formatted as currency in a particular table cell. All you need to do is follow these steps:. Position the insertion point in the table cell where the number will reside. Press Ctrl+F9 to insert a pair of field braces. Type the equal sign, followed by the number (65.78). Type a space, followed by # and another space.
This switch tells Word that you are about to specify the format for the number. Enter the format specification, in quote marks. In this case, you would enter '$#,###.00'. Press Shift+F9 to collapse the completed field.You can continue to insert numbers in this way. If you have quite a few of them, you may want to create a macro to automate the process. Entering numbers in this way will not affect your ability to create column totals, as well.
-->
Summary
This article contains and describes formulas that calculate the following:
More InformationFormula to Count the Number of Occurrences of a Text String in a Range
=SUM(LEN(range)-LEN(SUBSTITUTE(range,'text',')))/LEN('text')
Where range is the cell range in question and 'text' is replaced by the specific text string that you want to count.
Note
The above formula must be entered as an array formula. To enter a formula as an array in Excel for Windows, press CTRL+SHIFT+ENTER. To enter a formula as an array in Excel for Macintosh, press COMMAND+RETURN.
The formula must be divided by the length of the text string because the sum of the character length of the range is decreased by a multiple of each occurrence of the text string. This formula can replace all later formulas in this article except the formula to count the number of words in a cell.
Example 1: Counting the Number of Occurrences of a Text String in a Range
Formula to Count the Number of Occurrences of a Single Character in One Cell
=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,'a','))
Where cell_ref is the cell reference, and 'a' is replaced by the character you want to count.
Note
This formula does not need to be entered as an array formula.
Example 2: Counting the Number of Occurrences of a Character in One Cell
Use the same data from the preceding example; assuming you want to count the number of occurrences of the character 'p' in A7. Type the following formula in cell A9:
A9: =LEN(A7)-LEN(SUBSTITUTE(A7,'p','))
The value of cell A9 is 3 because the character 'p' appears three times in A7.
Formula to Count the Number of Occurrences of a Single Character in a Range
=SUM(LEN(range)-LEN(SUBSTITUTE(range,'a',')))
Where range is the cell range in question, and 'a' is replaced by the character you want to count.
Note
The above formula must be entered as an array formula. To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.
Example 3: Counting the Number of Occurrences of a Character in a Range
Use the same data from the preceding example; assuming you want to count the number of occurrences or the character 'p' in A2:A7. Type the following formula in cell A10:
A10: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,'p',')))
Note
The above formula must be entered as an array formula. To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.
The value of cell A10 is 11 because the character 'p' appears 11 times in A2:A7.
Formula to Count the Number of Words Separated by a Character in a Cell
=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,'))+1)
Where cell_ref is the cell reference, and char is the character separating the words.
Note
There are no spaces in the above formula; multiple lines are used only to fit the formula into this document. Do not include any spaces when you type it into the cell. This formula does not need to be entered as an array formula.
Example 4: Counting the Number of Words Separated by a Space in a Cell
To count the number of words in a cell where the words are separated by a space character, follow these steps:
The formula in cell A2 returns a value of 4 to reflect that the string contains four words separated by spaces. If words are separated by multiple spaces or if words start or end in a space, it does not matter. The TRIM function removes extra space characters and starting and ending space characters in the text of the cell.
In Excel, you can also use a macro to count the occurrences of a specific character in a cell, or range of cells.
References
For additional information about counting occurrences of text, click the following article number to view the article in the Microsoft Knowledge Base:
89794 How to use Visual Basic for Applications to count the occurrences of a character in a selection in Excel
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |