How to automatically convert currency values from numeric amounts to Chinese uppercase amounts in Excel. When using Excel, sometimes you need to convert data, especially when doing financial work, such as salary tables, etc., you need to use Chinese capital amounts. If you enter it manually, it will be more troublesome and will often increase the workload. We can use functions in Excel to automatically convert currency values from numeric amounts to Chinese uppercase amounts. This article uses a table as an example to explain how to convert numerical amounts into Chinese uppercase amounts. First calculate the total amount in the Excel table, as shown below. Use a formula to convert lowercase currency to Chinese uppercase and write it into the merged cells. The formula is as follows: =IF((I41-INT(I41))=0,TEXT(I41,"[]")&"元全",IF(INT(I41*10)-I41*10=0, TEXT(INT (I41),"[]")&"元"&TEXT(INT(I41*10)-INT(I41)*10,"[]")&"angle",TEXT(INT(I41)," [] " )&"元"&IF(INT(I41*10)-INT(I41)*10,"zero",TEXT(INT(I41*10)-INT(I41)*10,"[]")& "angle" )&TEXT(right(I41,1),"[]")&"point")).
Enter the formula in the merged cell to the right of the total as shown below. After input, the result is displayed as shown below. The text connector "&" and the corresponding Chinese characters are used in the formula, a total of 4 functions. These four functions are explained below: Function: It is a conditional judgment function. The basic format of this function is: IF(a,b,c), where "a" is the condition, "b" and "c" have two judgment results, which can be expressed as: if condition a is true, then the result is b , otherwise the result is c. The above formula is a multi-layer nested judgment function that generates Chinese capital letters in different formats according to different currency amounts. INT function: It is a rounding function that takes the integer part of the parameter number. For example: INT(I41) is the integer part of cell number I41, (I41-INT(I41))=0 represents an integer, INT(I41*10)-INT(I41)*10 represents one digit of the decimal point of cell I41, INT(I41*10)-INT(I41)*10=0 means that cell I41 has one decimal place. TEXT function: can convert Arabic format numbers into Chinese uppercase format. For example, TEXT(I41,"[]") in the formula converts the number in cell I41 to Chinese uppercase format, where "[]" converts the number to Chinese uppercase format, and what is copied is the RIGHT function: several characters can be extracted on the right side of the string. For example, "RIGHT(I41,1)" in the formula means to extract a character from the right side of the number in cell I41.
This formula uses multiple levels of nesting and looks a bit complicated. The entire conditional function formula can be expressed as: If the number in cell A7 is an integer (for example, 35566), then the capital RMB is "Thirty-five thousand six hundred yuan", otherwise: If the number in cell A7 is cell A7 Contains one decimal place (if the number is 35566.2), then the uppercase RMB is "Three Five Thousand Five Hundred and Twenty-One", otherwise: If the number in cell A7 has two decimal places, the tenth digit is 0 (if is 35566.08), then the capital letters of RMB are "35,000,500,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000. Otherwise: If the number in cell A7 is two decimal places, and The tenth digit is not 0 (for example, 35566.28), then the capital letters of RMB are "Thirty Five Thousand and Five Thousand Five" "Bai Lu Stone Road Yuan Twenty-eight" Note: This article refers to articles related to Internet content. Do the entire columns have values or only column A1 has values? If column A has values and column B displays 1, the formula is =IF(COUNT (A:A)0,1,0) If column A has the value Value, and if B1 displays 1, the formula is as follows =IF((A1),1,"") The custom cell format is 0.00;-0.00;; @If it is not displayed, you can see the following custom cell format: #,# #0.00_);_(*(#,##0.00); _(* Just know that the complete format code consists of four parts. This The four parts define positive numbers, negative numbers, zeros, and text in the format. The various parts of the format code are separated by semicolons.
I probably know that the decimal points are aligned when displaying positive and negative numbers, but I want to ask: 1. The first three parts start with _(, what is the purpose of putting them here? 2. The first and third parts start with _) At the end, what is the purpose of putting it here? That's a good question: This is the most complex of the custom formats, accounting-specific. Only by understanding it can you truly understand custom formats. The meaning is the same! #, 0; I don’t need to introduce these symbols! (#, ##0.00) Negative numbers are represented by parentheses! It is used to align the decimal points! Place the negative sign directly opposite the two decimal places. It is equivalent to occupying one decimal place! Method 1: Set through options, but do not display when the cell value is 0. Open the "Tools" menu; click "Options"; select the "View" card in the opened dialog box and remove the check mark in front of "Zero Value". Method 2: Formula method, divided into two categories, one is the judgment method, that is, if = if(A1="","",A1); the other is the format method =A1&"" or =""&A1; Three , Conditional formatting method: Set conditional formatting. If equal to 0, the font is white. This is just a workaround, the 0 value is still there, just not visible. Supplement: After setting this option, it is only for this workbook, so it will still be effective after changing the computer, but I do not recommend this method. Because this setting will prevent the value 0 from being displayed where it should be. It's better to use formulas or conditional formatting. Ancient and modern famous sayings: Diligent and eager to learn, not ashamed to ask - Confucius was good at diligence and wasted on playing; success comes from thinking, failure comes from following - Han Yu became popular in "Poetry", established himself in etiquette, and succeeded in music - Confucius did not Something you are willing to do. , don’t do this to others - Confucius read thousands of books, and his writing seemed to be spiritual - Du Fu has three methods of reading, that is, understanding with the heart, understanding with the eyes, and understanding the mouth with the heart - Zhu Xi puts learning first, and learning puts reading first - - Ouyang Xiu has read thousands of books and traveled thousands of miles - Liu Yi has long been black-haired and has not known how to study diligently, Bai Shoufang regrets studying late - Yan Zhenqing's books are as affectionate as old friends, joy and sorrow in the morning and evening - Yu Qian does not wait for a moment, the young man's head has turned gray . Sadness and emptiness - Yue Fei worked hard to read all the books in the world and determined to read all the books in the world - Su Shi. If a bird wants to fly high, it must first flutter its wings. If a person wants to make progress, he must first flutter his wings and fly high. The first reading - Li Kuchan is determined to think about genuine works, and he must work hard in reading - Ruan Yuanfei is indifferent and indifferent, and his ambitions are lofty. Without tranquility, there is no distance - Zhuge Liang is familiar with three hundred Tang poems and can recite them if he can't write them down - Sun Zhu Xi's "Three Hundred Prefaces to Tang Poems" only regretted it after using it, and only after experiencing it did he realize the difficulties - Lu You asked how clear the canal was, so the living water had a source - Zhu Xi took the trouble to read the ancient book a hundred times, and understood himself after reading it carefully - —Su Shi declares that visitors may use the content provided in this material for personal study, study or research. Appreciation and other non-commercial or non-profit purposes, but at the same time must comply with the provisions of copyright law and other relevant laws, and must not infringe the legal rights of this document and related rights holders. Thank you for your cooperation!
评论列表