Excle 10 simple ways to use common sequences

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

Ten strokes of the use of excle common sequences

Because of the need for work, I often use Excel to deal with a large number of forms of documents, I feel that the most headache is the text input, due to my speed is not high, so I have to often work overtime, can barely finish the task entrusted to the boss. After years of constant groping, I found some can improve the speed of Excel input small moves, is summed up, I hope we can enjoy.??? First trick--inline sequence method.

If you often need to enter some regular sequence text, such as numbers (1, 2 ...), date (1st, 2nd ...) And so on, you can use Excel's inline sequence to implement its quick input:

First enter the first two elements of the text, such as "A, B," in the 1th, 22 cells where you want to enter the sequence text. Also select the two cells above, and move the mouse over the lower-right corner of cell 2nd into a fine crosshair (which we usually call the "fill handle"), hold down the left mouse button and drag backward (or down) to the last cell that you want to fill in the sequence, and then release the left button, followed by the following elements of the sequence (such as "C, D, e ...") Automatically fill in the corresponding cells.

The second trick--right-dragging method.

Sometimes you need to enter a number that is not naturally incremented (such as the equal-ratio sequence: 2, 4, 8 ...), we can use the right to drag the method to complete:

First, enter the first two values (2, 4) of the sequence in cell 1th and 22. When the two cells are selected and the mouse is moved to a fine cross in the lower-right corner of Cell 2nd, press and hold the right button to drag backward (or downward) to the last cell of the sequence, loosen the right button, and a menu pops up, select the "Compare sequence" option, then the sequence (2, 4, 8, 16 ...) and its cell format into the appropriate cells (if you select the "Linear series", enter 2, 4, 6, 8 ...).

The third trick is to define the sequence method.

The name of the staff in the unit is often required to input, some employees have the name of uncommon input is extremely difficult, if we define a good "employee name sequence", the input will be much faster:

Enter the employee's name in a contiguous cell, select them, open options with the tools → options command, select the Custom Sequence tab, and press the import, OK button. Enter the name of a worker in any cell later (not necessarily the name of the 1th employee), and use the fill handle to quickly fill in subsequent cells with the worker's name behind it.

Four strokes--automatic correction method.

Due to the needs of the work, I often need to have some national standards (such as "Building installation engineering quality inspection and evaluation of unified standards") input cells, the input requirements for these texts must be unified. I solved the problem with the AutoCorrect feature:

With the tools → AutoCorrect command, open AutoCorrect, fill in the box under replace with "G300", and fill in the box below "Replace with" to insert "the unified standard for quality inspection and evaluation of construction and installation works" and press the OK button. Later in the cell "G300" (Note: g must be capitalized!) After the confirmation, the system will automatically change the period to "" Building installation Engineering quality inspection and evaluation of unified standards, both fast and accurate, unified.

In particular, if you want the text "G300" in the table, you can first type "G3000" and later text, and then delete "1". You can define the text in accordance with the above method and make it easier to use later. Specifically, the AutoCorrect entries that are defined here are also available in other applications in the Office family, such as word.

The five strokes-function merging method.

I often need to enter the name of some construction enterprises (such as "Maanshan ninth construction and Installation Engineering Limited liability company", etc.), these texts are much the same, and if you take the "AutoCorrect" method, you may not be able to remember its code later (you can make a list and put it in front of the computer to find it). In this case, we can use a function of Excel to implement a quick input like these text:

Assuming that the above text needs to be entered in the D column of a SHEET1 worksheet (such as D2 cell) in a workbook, we first enter text "B1" and "Construction Installation Engineering LLC" in two subsequent cells in the SHEET2 worksheet, such as A1 and Maanshan, Then enter the formula in the Sheet1 cell D2: =concatenate (sheet2! $A $1,c2,sheet2! $B $), and then we'll just enter "Nineth" in the C2 cell, and then the quick enter " Maanshan Ninth construction and Installation Engineering Co., Ltd. "text.

There are three points to this trick:

① We can use the fill handle to copy the formula above to other cells in column B, and when you enter a keyword for an enterprise name (such as "Nineth") in the corresponding cell in column C, the full name of the enterprise is filled in the corresponding cell in column D.

② in order to facilitate the replication of the formula, we then take the reference to the cell in the formula "absolute reference" (such as SHEET2A1 and SHEET2B1, this part of the cell does not change with the copy of the formula, plus the "$" symbol is the absolute reference, and the relative reference (such as C2 cell, we do not add "$" Symbol, the cell is automatically adjusted as the formula is copied.

③ There's one more column in the worksheet (column C), and we don't want to print it out when we print it, we check the column right after the mouse, and then the menu pops up? Hide to hide the column without being printed.

The first one is the substitution method.

8630.html "> Sometimes we have to enter the same text more than once in a worksheet, especially to enter some special symbols (such as ※) several times, which is very troublesome and has a great effect on the speed of entry. At this point we can overcome this defect by using a one-time substitution method:

First enter a substituted letter (such as x) in the cell where you want to enter the symbols. Note: cannot be the letter that is needed in the table, and when the form is finished, use the Edit Replace command (or press CTRL) Open Replace, enter the substituted letter "X" in the box under Find what, and enter "※" in the box under "Replace Value", "∨" in front of "Cell match" (otherwise it will not be replaced), and then press the Replace button one to replace it (if you do need the letter X in the table, this will meet this requirement.)

Note: The mouse is best selected in the cell before the cell you want to replace, preferably the A1 cell. Or you can press the Replace All button and replace it all at once (all of the X in this table is replaced and the mouse can be selected in any cell).

The seventh recruit-fast copy method.

Sometimes the text you need to enter is previously entered, and you can take a quick copy (not the usual CTRL + C, CTRL, CTRL + V) method to complete the input:

① If you need to enter the same text (such as "Brick Mix") in a contiguous cell, first enter the text in cell 1th, and then use the fill handle to copy the period to the subsequent cells.

② if the text you want to enter is previously entered in the same column before, when you enter the text in front of several characters, the system will prompt you, you simply press the "enter" key to the subsequent text input.

③ If you need to enter the same text as the previous cell, you can press the Ctrl+\ key to complete the entry.

④ if more than one cell needs to enter the same text, we can hold down the Ctrl key while you click all the cells you want to enter the same text, enter the text, and then press the "CTRL" button.

The eighth trick is to define the format method.

Sometimes we need to add units to the input values (such as "square meters", etc.), a small number of us can directly input, and a large number of if one input is too slow. We "Customize" the format of the cell to achieve the automatic input unit:

Let's first enter the values into the corresponding cells (note: numeric only!)., and then while holding down the CTRL key, select the cell that you want to add the same unit to, with the format → cell command, open cell formatting, in the Number tab, select the Custom option under category, and then in the type In the box below, enter "#" "Square" "Rice", press the OK key, the unit (square meter) that is one-time added to the corresponding value of the following.

Nineth strokes-Define the phrase method.

For some text that often needs to be entered (such as "Construction Engineering Quality Supervision"), I think, take the use of Input method "manual build quot function to achieve better (here with" five Input method "as an example):

Start "Five Input Method", with the right mouse click State Bar, select the "Handmade word" option, open "hand-made words", the "words" in the box after the phrase (such as "construction quality supervision", a maximum of 20 Chinese characters, including some symbols can also be), the "Outside Code" in the box after the input "encoding" ( It is best to use the system automatically generated outside code, he signed the input method encoding rules for easy memory and use, and then press the "add" and "close" button. You can enter your custom phrase as you would enter other phrases.

The tenth recruit-0 to find the crack method.

The following methods do not seem to have a direct relationship with the input, but simplify the setup process for Excel, and naturally increase the input speed:

① you know, if you enter the number of digits in Excel (such as the identity card number), then the system will convert it into a scientific notation format, in order to not match our input intent, the solution is to set the value of the cell in the text format. If you use the command method to set directly, naturally very slow. In fact, when we enter these values, we simply add a small "\" to the value (note: the "\" must be entered in the English state).

② if the text entered in a cell does not fit in one line, we press the "ALT" key while pressing the "enter" button to quickly force the switch.