몇일 찾았어요. ^.^
세상에 엑셀 고수는 많군요. ^.^
=ROUND(value,sigfigs-(1+INT(LOG10(ABS(value)))))
value :: the number you wish to round. (처리할 데이타값)
sigfigs :: the number of significant figures you want to round to. (유효자리숫자 갯수)
sigfigs :: the number of significant figures you want to round to. (유효자리숫자 갯수)
ex) =ROUND(A1,3-(1+INT(LOG10(ABS(A1)))))
앗..문제발생...ㅡ.ㅡa
마지막에 "0" 일 경우... 문제 발생..ㅡ.ㅡa
입력값 Round사용
| 12345 | 12300 |
| 1234.5 | 1230 |
| 123.45 | 123 |
| 12.345 | 12.3 |
| 1.2345 | 1.23 |
| 0.123450 | 0.123 |
| 0.012345 | 0.0123 |
| 0.001235 | 0.00123 |
| 0.000123 | 0.000123 |
| 30 | 30 |
| 30.1 | 30.1 |
| 30.04 | 30 |
| 1 | 1 |
| 10 | 10 |
| 100 | 100 |
해결책. 아래 박스에 나와 있는 것으로 문자로 처리하면 가능함....
=TEXT(TEXT(value,"."&REPT("0",sigfigs)&"E+000"),
"0"&REPT(".",(sigfigs-(1+INT(LOG10(ABS(value)))))>0)& REPT("0",(sigfigs-(1+INT(LOG10(ABS(value)))))*
((sigfigs-(1+INT(LOG10(ABS(value)))))>0)))
단점은 복잡하다..ㅡ.ㅡa
그렇지만 다른 설정 필요 없음.
다른 방법은...
그래서 VB로 명령어를 만들자.
만드는 방법은..아래와 같고 장점은 함수가 간단해진다.
단점은 초기 설정이 매우 복잡함. 다른 PC에서는 다시 설정해야하고
스크립터보안에 걸릴수 있음.
이렇게 하시고 사용하시면
| ROUNDSF | ||
| 12345 | 12300 | 12300 |
| 1234.5 | 1230 | 1230 |
| 123.45 | 123 | 123 |
| 12.345 | 12.3 | 12.3 |
| 1.2345 | 1.23 | 1.23 |
| 0.123450 | 0.123 | 0.123 |
| 0.012345 | 0.0123 | 0.0123 |
| 0.001235 | 0.00123 | 0.00123 |
| 0.000123 | 0.000123 | 0.000123 |
| 30 | 30 | 30.0 |
| 30.1 | 30.1 | 30.1 |
| 30.04 | 30 | 30.0 |
| 1 | 1 | 1.00 |
| 10 | 10 | 10.0 |
| 100 | 100 | 100 |
| 1000 | 1000 | 1000 |
해결은 가능하지만 아무래도 좀... 부담스러움... VBA는......ㅡ.ㅡa
그래서 새로운 해결책...
FIXED수식 사용
| 입력값 | ROUND(A4,3-(1+INT(LOG10(ABS(A4))))) | ROUNDSF | FIXED(A4,3-(1+INT(LOG10(ABS(A4))))) | FIXED(A4,3-(1+INT(LOG10(ABS(A4)))),TRUE) |
| 12345 | 12300 | 12300 | 12,300 | 12300 |
| 1234.5 | 1230 | 1230 | 1,230 | 1230 |
| 123.45 | 123 | 123 | 123 | 123 |
| 12.345 | 12.3 | 12.3 | 12.3 | 12.3 |
| 1.2345 | 1.23 | 1.23 | 1.23 | 1.23 |
| 0.12 | 0.12 | 0.120 | 0.120 | 0.120 |
| 0.012345 | 0.0123 | 0.0123 | 0.0123 | 0.0123 |
| 0.0012345 | 0.00123 | 0.00123 | 0.00123 | 0.00123 |
| 0.000123 | 0.000123 | 0.000123 | 0.000123 | 0.000123 |
| 30 | 30 | 30.0 | 30.0 | 30.0 |
| 30.1 | 30.1 | 30.1 | 30.1 | 30.1 |
| 30.04 | 30 | 30.0 | 30.0 | 30.0 |
| 1 | 1 | 1.00 | 1.00 | 1.00 |
| 10 | 10 | 10.0 | 10.0 | 10.0 |
| 100 | 100 | 100 | 100 | 100 |
| 1000 | 1000 | 1000 | 1,000 | 1000 |
| 숫자 | 문자 | 문자 | 문자 |
응용.
0.123 ± 0.002 이렇게 나타내기 수식 앞자리는 유효자리 3자리 ± 뒷자리는 앞의 자리수에 맞게 불확도 표시하기.
0.123456=A1
0.0021234=A2
=FIXED(A1,3-(1+INT(LOG10(ABS(A1)))),TRUE) &"±"& FIXED(A2,3-(1+INT(LOG10(ABS(A1)))))
결론...
유효숫자 3개 고정하기(콤마 자리표시) --->문자인식
=FIXED(value,sigfigs-(1+INT(LOG10(ABS(value)))))
value :: the number you wish to round. (처리할 데이타값)
sigfigs :: the number of significant figures you want to round to. (유효자리숫자 갯수)
=FIXED(A4,3-(1+INT(LOG10(ABS(A4)))))
유효숫자 3개 고정하기(콤마 제거) --->문자인식
=FIXED(A4,3-(1+INT(LOG10(ABS(A4)))),TRUE)
앞자리 숫자와 소수점 자리 일치하기 --->문자인식
0.123 ± 0.002
0.123456=A1
0.0021234=A2
How to Create Excel User Defined Functions
- Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- - Copy and Paste the Excel user defined function examples -
- Get out of VBA (Press Alt+Q)
- Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)
아래 링크 참조하시고 내용도 퍼왔어요.
How do you tell Excel to round to a specific number of significant figures without having to use exponential notation? This Excel formula will do the trick:
=ROUND(value,sigfigs-(1+INT(LOG10(ABS(value)))))
value :: the number you wish to round.
sigfigs :: the number of significant figures you want to round to.
sigfigs :: the number of significant figures you want to round to.
There. Quick and easy. Continue reading below for more explanation about how this formula works, and visit myExcel Tips and Excel Templates pages for a lot of other great resources, or check out the list of the most popular templates on the right.
The trick to this formula comes from understanding scientific notation. Reporting the number 12783 with three significant digits would give 1.28E4 or 1.28*10^4 or base*10^exponent.
Let's work backwards from what we want. We want to use the ROUND function for starters. But, we need to know the "location" of the digit to round to. Remember that the way the ROUND function works in Excel, rounding 12783 to the 100s place means you use a "location" of -2 or 12800=ROUND(12783,-2). If we want 3 significant digits, we just need to create a formula that gives -2 based upon the position of the first significant digit, or 1 plus the exponent.
The formula for the exponent of 12783 is:4=INT(LOG10(ABS(12783)))
There we have it: 3 - (1+4) = -2
You can also use the ROUNDDOWN or ROUNDUP function in place of the ROUND function.
Custom Function for Rounding Significant Figures
Syntax: ROUNDSIG(value,sigfigs)
Example: ROUNDSIG(-0.04589,2) equals -0.046
Function ROUNDSIG(num As Variant, sigs As Variant) Dim exponent As Double If IsNumeric(num) And IsNumeric(sigs) Then If sigs < 1 Then ' Return the " #NUM " error ROUNDSIG = CVErr(xlErrNum) Else If num <> 0 Then exponent = Int(Log(Abs(num)) / Log(10#)) Else exponent = 0 End If ROUNDSIG = WorksheetFunction.Round(num, _ Sigs - (1 + exponent)) End If Else ' Return the " #N/A " error ROUNDSIG = CVErr(xlErrNA) End If End Function
See Also: "How to Create Custom Excel Functions"
Note: It has been correctly pointed out (here) that trailing zeros on decimals numbers don't necessarily display correctly when using the above formulas. Although the value will be correct, Excel automatically formats a number with 5 sig figs such as 23.300 to display as 23.3 (unless the display format has been set to "0.000").
Working Around the Display Format Problem
To ensure that significant trailing zeros are displayed correctly when rounding a number to a certain number of significant digits, you need to work with text formats. The following formula is very confusing, but it gets the job done.
=TEXT(TEXT(value,"."&REPT("0",sigfigs)&"E+000"),
"0"&REPT(".",(sigfigs-(1+INT(LOG10(ABS(value)))))>0)& REPT("0",(sigfigs-(1+INT(LOG10(ABS(value)))))*
((sigfigs-(1+INT(LOG10(ABS(value)))))>0)))
I got the idea to use the text format in this way from John McGimpsey's site. However, the above megaformula lets you choose any number of significant digits. Below is a VBA function using this method to round significant digits. The function returns the value as a string, so when using the value in other formulas, you can use VALUE(cell) to convert the string to a numeric value.
Function ROUNDSF(num As Variant, sigs As Variant) As String Dim exponent As Integer Dim decplace As Integer Dim fmt_left As String Dim fmt_right As String Dim numround As Double If IsNumeric(num) And IsNumeric(sigs) Then If sigs < 1 Then ' Return the " #NUM " error ROUNDSF = CVErr(xlErrNum) Else numround = WorksheetFunction.text(num, "." & _ String(sigs, "0") & "E+000") If num = 0 Then exponent = 0 Else 'Round is needed to fix a ?truncation? 'problem when num = 10, 100, 1000, etc. exponent = Round(Int(Log(Abs(numround)) / Log(10)), 1) End If decplace = (sigs - (1 + exponent)) If decplace > 0 Then fmt_right = String(decplace, "0") fmt_left = "0." Else fmt_right = "" fmt_left = "0" End If ROUNDSF = WorksheetFunction.text(numround, _ fmt_left & fmt_right) End If Else ' Return the " #N/A " error ROUNDSF = CVErr(xlErrNA) End If End Function
References
- Eric W. Weisstein. "Significant Digits." From MathWorld--A Wolfram Web Resource. http://mathworld.wolfram.com/SignificantDigits.html
- iSigSigma.com, "Rounding Rules and Round-Off Rules for Statistical Data Analyses"
- "Calculations with Significant Figures." http://www.mcgimpsey.com
Disclaimer: This article is meant for educational purposes only.
