Shulou(Shulou.com)11/24 Report--

Behind the headlines! Hello everyone ~ here is the workplace struggle satellite paste trying to introduce Excel knowledge in the most easy-to-understand language.

The custom format of Excel forms must be familiar to all of us. It is a great contribution to our rapid data entry.

The use of custom format code, we can make the input data in a variety of formats we want to present, it can be said that the use of custom format to dress up the data, it is all basic.

But do you really know the code? Or, do you really know the true face of the characters in the custom format?

If you care so much, why don't you just use it? No, no.

Characters in custom formats that occupy fixed positions instead of certain types of data are called placeholders, and here we roughly divide them into the following three categories:

1. Placeholders such as numeric placeholders are all numeric placeholders or numeric-related characters.

First of all, 0 is our symbol 0, and a 0 represents a number. If the number of digits of the actual number is greater than the number of 0, the actual number is displayed, otherwise 0 is used to make up. However, if the number after the decimal point is greater than 0, it is rounded and intercepted.

For example: now let's set the custom format of column B to 0. 0 (PS. 0. 0). Here, in order to show the effect, the B2 cell is separately formatted as text. ), if the actual number is 365, it is displayed as 365.0, the actual number is 5, it is displayed as 05.0, and when the actual number is 12.56, it is displayed as 12.6 rounded.

# # is also a number placeholder. If the number after the decimal point is greater than 0, it will also be rounded and intercepted. But unlike 0, # shows only meaningful zeros, not meaningless zeros.

For example: now we set the custom format of column B to # #. # #, and the custom format of column C to 00.00. If the actual number is 3.14 and 3.141 BC, the two columns are shown as follows:

We can see the difference between the two characters from the picture.

? This symbol does not represent a number, but it allows unequal length of data to be aligned to the decimal point.

? The principle is to add spaces on both sides of the decimal point until the numbers are aligned according to the decimal point.

For example: now let's set the custom format of column B to?

This is a half-width comma, it is the thousand separator, if you want your data to be displayed in the thousand quantile, you can use it.

Generally, we set the custom format of column B to #, #

In this way, the larger numbers will be easy to distinguish.

. Finally, there is ours.

Don't forget that it is also an important character in numbers.

Yes, it is-- decimal point.

2. The text placeholder is good, and then there are the placeholders related to the text.

@ @ represents text. Be careful! An @ represents the text value itself, not a @ character! In other words, @ equals the whole text you fill in that grid!

Like this: if I set the cell format of column B to [green] @, then the text of this column will turn green!

The combination of @ and @ will make the text value you enter repeat. For example: set the format of column B to @ @, then we enter "Little Star" and it will be displayed as "Little Star"! Isn't it very interesting?

\ and! These two symbols are not placeholders for text, but here I still classify them as text placeholders because both of them can escape characters with special meanings into text.

\ and! Are forced escape characters that can escape characters with special meanings into text. For example, if we set the cell format of column B to 0.0\ km, the result is as follows:

This code means: change the value after the kilometer to "kilometer" and keep a decimal place.

It's a little hard to understand? Let's see another one! The effect is the same: column B cell format is set to 0 ↓↓↓. 0, ten thousand yuan

This code means: change the value after ten thousand digits to "ten thousand yuan" and keep the thousand percentile as the decimal.

Here\ nand! It is interchangeable and has no effect.

Code illustration:

3. Date and time is followed by placeholders for date and time. This part is easier to remember. You can contact English slightly. Please see:

Yy refers to year, that is, the year. The meanings of different ways of writing are as follows:

Yy: displays a two-digit year

Yyyy: displays four-digit years

Mm means month, month. The meanings of different ways of writing are as follows:

M: date is displayed as month

Mm: displays as a two-digit month, and 1 displays as 01

Mmm: month is displayed in abbreviated English

Mmmm: the English full name of the month

Mmmmm: the first letter of the month is capitalized

Dd refers to day, indicating the number of days. The meanings of different ways of writing are as follows:

D: the date is displayed as days

Dd: displays as double-digit days, and 1 displays as 01

Ddd: displayed as an English abbreviation for week

Dddd: show the full English week

Aa is also a week, but in Chinese.

Enter aaaa to display the date as week, while enter aaa to omit the word "week" ~

Hh is hour, which means hours. The meanings of different ways of writing are as follows:

H: displays one hour, that is, 023

Hh: displays two-digit hours, each bit-hour will be filled with 0, such as 01

Although m is also m, but here is minute, meaning minutes! The meanings of different ways of writing are as follows:

M: show minutes in one bit (0059)

Mm: show minutes in two digits (01x59)

PS. Note that when using this code to represent minutes, it should be used with code that represents hours or seconds, otherwise it will be defaulted to the month code.

S this is second, which means seconds.

Similarly, s displays seconds in one bit (0159), while ss displays seconds in two bits (01x59)!

The placeholders of time and date are regular, right? A table summarizes:

4. Repetition and color the last repetition placeholder and color placeholder are two guys with no way and no school, so let them stay warm together:

The * asterisk * is a placeholder for repetition. It can display the contents after the * sign indefinitely until the entire cell is filled, as if you were asleep and pressed the keyboard.

It couldn't be more convenient to use it as a catalogue!

For example, I enter the code @ *-"1"

The color placeholder will fill your specified content with color, which makes it easy for us to mark and distinguish.

Its Chinese expressions are available in eight colors: [red], [black], [yellow], [green], [white], [blue], [cyan] and [magenta]. (note that if you enter [cyan] with an error, enter [turquoise] instead.)

If you want other colors, we can use [Color 1], [Color 2]. In this way, 56 different colors are expressed. The colors represented by different numbers are shown in the following figure:

5. a little summary today talked about the meaning of so many kinds of placeholders, let's review it:

Zero well occupies a good position for zero, and the well does not show meaninglessness.

Question mark (?) Let the data be aligned according to the decimal point

This comma (,) is called the delimiter of the thousandth.

There is also a decimal point (.), don't throw it away.

Aite (@) represents the text, and the lever sighs (\!) Forced escape

Don't forget the date and time, just remember it in English!

Finally, the asterisk (*) and color, don't discard it even though you use it less.

It's much easier to remember, hee hee.

In addition, in the code we have used before, it is more difficult to remember 0.0km-1000 km to 1km; 0.00.0000 yuan-10000 yuan to 10,000 yuan.

Remember these two examples and apply them directly next time!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: satellite paste

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.