'엑셀보조파일,기능,함수' 카테고리의 글 목록

수식에서의 -- 역할 및 SUMPRODUCT 함수 예제 입니다.

엑셀보조파일,기능,함수 2016. 3. 2. 00:23

안녕하세요. 오즈맨 입니다.
시트명은 STEP1 입니다.


2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

B

C

D

E

F

G

H

I

J

K

L

M

N

상품 현황

수량

단가

금액

금액2

수량

단가

금액

금액2

100

10,000

100

10,000

=H5*I5

10,000

=PRODUCT(H5:I5)

10

200

2,000

10

200

2,000

=H6*I6

2,000

=PRODUCT(H6:I6)

300

500

150,000

300

미정

#VALUE!

=H7*I7

300

=PRODUCT(H7:I7)

합계

162,000

합계

#VALUE!

12,300

12,000

=SUM(J5:J7)

=SUM(L5:L7)

=SUMPRODUCT(H5:H7,I5:I7)

수량*단가 인 경우

숫자와 문자를 곱하면 오류가 납니다

따라서 오류가 있는 경우 합을 구할 수 없죠

그러나 product 함수는 숫자가 아닌 값은 없는 셈 칩니다

그렇다고 0 으로 취급하지는 않습니다

계산 자체를 하지 않는것입니다

이렇게 product 한 결과의 합을 구하는것이 sumproduct 입니다

사용자의 의도대로 수량 영역*단가영역의 결과를 보면

#VALUE!

=SUMPRODUCT(H5:H7*I5:I7)

역시나 오류가 납니다

두 영역의 각 값을 곱하라고 사용자가 시킨 때문이죠

그러나 곱하는건 내가 할테니 곱할 영역만 주세요

이런 경우는 프로그램은 계산 을 하게되고 그 결과가 만들어집니다

12,000

=SUMPRODUCT(H5:H7,I5:I7)

따라서 원하는 결과를 만들려면 수식을 아래와 같이 사용합니다.

=SUMPRODUCT( 영역 , 영역 )

STEP1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2007 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	D5	=B5*C5	10,000
2		D5 셀의 수식을 여기에 복사하세요 -→ D5:D7,J5:J7
3	F5	=PRODUCT(B5:C5)	10,000
4		F5 셀의 수식을 여기에 복사하세요 -→ F5:F7,L5:L7
5	K5	=MyFormula(J5)	=H5*I5
6		K5 셀의 수식을 여기에 복사하세요 -→ K5:K7,M5:M7,G24,G30
7	D8	=SUM(D5:D7)	162,000
8		D8 셀의 수식을 여기에 복사하세요 -→ D8,F8,J8,L8
9	N8	=SUMPRODUCT(H5:H7,I5:I7)	12,000
10	H10	=MyFormula(J$8)	=SUM(J5:J7)
11	H11	=MyFormula(L$8)	=SUM(L5:L7)
12	H12	=MyFormula(N$8)	=SUMPRODUCT(H5:H7,I5:I7)
13	F24	=SUMPRODUCT(H5:H7*I5:I7)	#VALUE!
14	F30	=SUMPRODUCT(H5:H7,I5:I7)	12,000
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일을 참조하시구요 원하는 부분을 시트(셀)에 붙여 넣으세요.(사용자정의함수가 있습니다) 도움이 되시기를 바랍니다. 참고로 SUMPROODUCT 함수는 =SUMPRODUCT( 영역 , 영역 , 영역 ) 으로도 응용 가능합니다 첨부 파일을 참조 바랍니다. 아울러 http://ozman.tistory.com/103 도 참조해보셔요

SUMPRODUCT이해.xlsm

저작자표시

Option Explicit

Function Dec_Bin(Dec_Value As Double, Optional HisLen As Double = 8) As String' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2015-06-23
Dim His_Dec As Currency
Dim ii      As Double
Dim AdStrr  As String
        His_Dec = Abs(Dec_Value)
    For ii = 1 To 2
        If His_Dec = 0 Then
        Else
            Dec_Bin = CStr(His_Dec Mod 2) & Dec_Bin
            His_Dec = His_Dec \ 2
                ii = ii - 1
        End If
    Next

        If HisLen - Len(Dec_Bin) > 0 Then
            AdStrr = String(HisLen - Len(Dec_Bin), "0")
            Dec_Bin = AdStrr & Dec_Bin
        End If
            If Dec_Value < 0 Then Dec_Bin = "-" & Dec_Bin

End Function         ' ___ 모듈 종료

저작자표시 비영리 동일조건

'엑셀보조파일,기능,함수' 카테고리의 다른 글

수식에서의 -- 역할 및 SUMPRODUCT 함수 예제 입니다. (1)	2016.03.02
숫자... 지수형식 오류 (0)	2015.05.14
사용자 함수를 찾는 vba 코드 입니다 (2) (0)	2014.08.21
사용자 함수를 찾는 vba 코드 입니다. (0)	2009.05.26
[ 문자열 비교하는 사용자 함수 ] 예제 입니다. (1)	2009.03.29
Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10

Posted by 오즈맨스머프

숫자... 지수형식 오류

엑셀보조파일,기능,함수 2015. 5. 14. 02:06

가끔은 아주 가끔은 애먹을 일이 있으면 보게 됩니다

숫자가 지수형식으로 display 됨으로 인한 오류 사전 예방입니다

Option Explicit

Sub NumericCheck()' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2015-05-14
' 배열내 숫자가 오류인지 아닌지를 검사할 임시 배열입니다.
Dim tmpVarr(1 To 20, 1 To 4)
Dim iip As Double
For iip = 1 To 20
     tmpVarr(iip, 1) = Rnd
     tmpVarr(iip, 2) = Rnd
     tmpVarr(iip, 3) = tmpVarr(iip, 2) - tmpVarr(iip, 1)
     tmpVarr(iip, 4) = "=rc[-1]-rc[-2]"
Next
Cells(1, 1).Resize(21, 5).ClearContents
Cells(1, 1).Resize(20, 4).Value = tmpVarr

Call ErrChk(tmpVarr)

End Sub         ' ___ 모듈 종료

Sub ErrChk(His_Variant As Variant)' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2015-05-14
' 배열내 숫자가 오류인지 아닌지를 검사합니다.
' 오류로 반환되는 경우에는 숫자의 형식을 미리 확인합니다.
Dim i_i   As Double
Dim j_j   As Double
Dim chkkV As String
Dim cnt_i As Double, cnt_j As Double
Dim AnswUniq   As New Collection

If TypeName(His_Variant) = "Variant()" Then
Else
     If IsNumeric(His_Variant) = True Then
          chkkV = "'" & His_Variant
               If chkkV Like "*E*" Then
                    If Mid(chkkV, 3, 1) = "." Then
                         AnswUniq.Add "Value Error =  " & Mid(chkkV, 2)
                    End If
               End If
     End If
     If AnswUniq.Count <> 0 Then GoTo Error_Found_OZman
End If

cnt_i = UBound(His_Variant, 1)
cnt_j = UBound(His_Variant, 2)

For i_i = 1 To cnt_i
For j_j = 1 To cnt_j
     If IsNumeric(His_Variant(i_i, j_j)) = True Then
          chkkV = "'" & His_Variant(i_i, j_j)
               If chkkV Like "*E*" Then
                    If Mid(chkkV, 3, 1) = "." Then
                         AnswUniq.Add "Error R/C " & i_i & "/" & j_j & "= " & Mid(chkkV, 2) & " ~~ " & CDec(Mid(chkkV, 2))
                    End If
               End If
     End If
Next
Next
     If AnswUniq.Count <> 0 Then GoTo Error_Found_OZman

Exit Sub

Error_Found_OZman:
cnt_i = AnswUniq.Count

Debug.Print
Debug.Print
Debug.Print "Errrrrr", Now
For i_i = 1 To cnt_i
     Debug.Print AnswUniq(i_i)
Next

Debug.Print "See Again"
Debug.Print

MsgBox "Numeric   Error   Check Again   ", vbOKOnly, "Special Module By OZMAN"

End Sub         ' ___ 모듈 종료

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

수식에서의 -- 역할 및 SUMPRODUCT 함수 예제 입니다. (1)	2016.03.02
10진수를 2진수로 변환하기(내장함수 한계극복) (0)	2015.06.23
사용자 함수를 찾는 vba 코드 입니다 (2) (0)	2014.08.21
사용자 함수를 찾는 vba 코드 입니다. (0)	2009.05.26
[ 문자열 비교하는 사용자 함수 ] 예제 입니다. (1)	2009.03.29
Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10

Posted by 오즈맨스머프

사용자 함수를 찾는 vba 코드 입니다 (2)

엑셀보조파일,기능,함수 2014. 8. 21. 01:17

Option Explicit

Sub UserFn_Find()' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2014-08-21

'루프 변수
Dim ii    As Double
Dim jj    As Double
Dim kk    As Double
Dim pp    As Double
Dim MySheet    As Worksheet

Dim TheLink As Variant        '외부링크 파일 찾아 저장
Dim My_Link As Variant        '그 중 사용자 함수만 추출

Dim tmpString As String
Dim tmpArray() As String      '목록을 임시로 저장하는 변수

TheLink = ActiveWorkbook.LinkSources(xlExcelLinks) '링크 차즌 담
kk = UBound(TheLink)          '개수를 세어보고

jj = 1
ReDim My_Link(1 To 2, 1 To jj) As Variant
      My_Link(1, 1) = "Path"
      My_Link(2, 1) = "File"

For ii = 1 To kk                   '링크 개수만큼 루프 돌되
     tmpString = TheLink(ii)            '임시 문자열에 링크명을 넣고
     pp = InStrRev(tmpString, "\")
     tmpString = Mid(tmpString, pp + 1) '임시문자열에서 파일명만 추출합니다

     If Workbooks(tmpString).IsAddin = True Then  '추가기능일 경우만 재적립 시킴
          jj = jj + 1
          ReDim Preserve My_Link(1 To 2, 1 To jj) As Variant
               My_Link(1, jj) = TheLink(ii)       '차후 File Open 용
               My_Link(2, jj) = tmpString         'UDF Find 용
     End If

Next

'찾기를 위한 임시변수
Dim c As Range, rng As Range
Dim tmpAddress As String

If jj < 2 Then
          MsgBox " 이 파일에는 읍사와유 ", , "그럼 이만..."
          Exit Sub
End If

Set rng = Application.InputBox(vbLf & "   가로로 네(4) 열 차지합니다", "   기록할 셀 지정합니다.", Type:=8)

pp = UBound(My_Link, 2)
For ii = 2 To pp                        '일단 닫고
     Workbooks(My_Link(2, ii)).Close
Next

jj = 1
ReDim tmpArray(1 To 4, 1 To jj) As String
     tmpArray(1, 1) = "추가기능 File"
     tmpArray(2, 1) = "사용된 시트"
     tmpArray(3, 1) = "셀 주소"
     tmpArray(4, 1) = "수식"

'여기서 파일명 검색으로 뒤져봅니다
For ii = 2 To pp
tmpString = My_Link(1, ii)
For Each MySheet In ActiveWorkbook.Sheets              '워크북에서 시트를 돌며 검색합니다.

     With MySheet.Cells                                '시트에서 ...
          Set c = .Find(tmpString, LookIn:=xlFormulas)      '해당 파일명을 검색합니다.
          If Not c Is Nothing Then
                    tmpAddress = c.Address
               Do
                    jj = jj + 1                             '만일 있으면 누적시켜 저장합니다.
                    ReDim Preserve tmpArray(1 To 4, 1 To jj) As String
                         tmpArray(1, jj) = tmpString        '파일명
                         tmpArray(2, jj) = MySheet.Name     '사용된 시트명
                         tmpArray(3, jj) = c.AddressLocal(0, 0)  '셀 주소
                         tmpArray(4, jj) = c.Formula         '수식
               Set c = .FindNext(c)
               Loop While Not c Is Nothing And c.Address <> tmpAddress
          End If
     End With

Next
Next

For ii = 2 To pp                       '닫은 추가기능을 다시 열고
     Workbooks.Open Filename:=My_Link(1, ii)
     Application.Volatile
Next


'그 결과를 행/열을 바꿔서 배열로 만들고
Dim TheAnswer
ReDim TheAnswer(1 To jj, 1 To 4)

For kk = 1 To 4
     TheAnswer(1, kk) = tmpArray(kk, 1)
Next

For ii = 2 To jj
For kk = 1 To 4
     TheAnswer(ii, kk) = tmpArray(kk, ii)
Next

     tmpString = TheAnswer(ii, 1)
     TheAnswer(ii, 1) = Mid(tmpString, InStrRev(tmpString, "\") + 1)

     tmpString = TheAnswer(ii, 4)
     TheAnswer(ii, 4) = " =" & Mid(tmpString, InStrRev(tmpString, "!") + 1)

Next

'바뀐 결과를 셀에 기록합니다
rng.Resize(jj, 4) = TheAnswer

'변수 초기화 마무리는 직접 하시기 바랍니다.

End Sub         ' ___ 모듈 종료

http://ozman.tistory.com/113 글의 신 버전입니다

저작자표시

'엑셀보조파일,기능,함수' 카테고리의 다른 글

수식에서의 -- 역할 및 SUMPRODUCT 함수 예제 입니다. (1)	2016.03.02
10진수를 2진수로 변환하기(내장함수 한계극복) (0)	2015.06.23
숫자... 지수형식 오류 (0)	2015.05.14
사용자 함수를 찾는 vba 코드 입니다. (0)	2009.05.26
[ 문자열 비교하는 사용자 함수 ] 예제 입니다. (1)	2009.03.29
Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10

Posted by 오즈맨스머프

사용자 함수를 찾는 vba 코드 입니다.

엑셀보조파일,기능,함수 2009. 5. 26. 19:48

Option Explicit

'새 버전으로 이동합니다'

Sub UserFunctionFind()' === 모듈 시작
' CodeBy [ User_1 ] , Date : 2009-05-26
On Error Resume Next
Dim Linked As Variant, Strs As String
Dim LnkArr As Variant
     Linked = ActiveWorkbook.LinkSources(xlExcelLinks) '링크 차즌 담
Dim i As Double, j As Double
Dim c As Range, rng As Range
Dim Addr As String, AnsAll
Dim AnsAdd As New Collection                           '셀 주소 누적용
     If UBound(Linked) = 0 Then
          MsgBox " 이 시트에는 읍네요", , "그럼 이만..."
          Exit Sub
     End If
Set rng = Application.InputBox(vbLf & "   가로로 두 열 차지합니다", "   기록할 셀 지정", Type:=8)
     ReDim LnkArr(1 To UBound(Linked), 1 To 2)
For i = 1 To UBound(Linked)
          LnkArr(i, 1) = Linked(i)                     '배열 1 째에는 전체이름
     For j = 1 To Len(LnkArr(i, 1))                    '폴더 명과 파일 구분
          Strs = Strs & Mid(LnkArr(i, 1), j, 1)
          If CStr(Mid(LnkArr(i, 1), j, 1)) = "\" Then Strs = ""
     Next
     LnkArr(i, 2) = Strs                               '배열 2 째에 파일명
Next
     For i = 1 To UBound(Linked)                       '일단 닫고
     Workbooks(LnkArr(i, 2)).Close
     Next
i = 0
Strs = Left(Strs, InStr(1, Strs, ".") - 1)
With ActiveSheet.Cells                                           '찾기 시작
     Set c = .Find(Strs, LookIn:=xlFormulas)
     If Not c Is Nothing Then
               Addr = c.Address
          Do
               i = i + 1
               AnsAdd.Add c.AddressLocal(0, 0), CStr(c.AddressLocal(0, 0))
          Set c = .FindNext(c)
          Loop While Not c Is Nothing And c.Address <> Addr
     End If
End With
     For i = 1 To UBound(Linked)
     Workbooks.Open Filename:=LnkArr(i, 1)
     Application.Volatile
     Next
ReDim AnsAll(1 To AnsAdd.Count + 1, 1 To 2)
     AnsAll(1, 1) = "셀 주소"
     AnsAll(1, 2) = "수식"
For i = 1 To AnsAdd.Count
     AnsAll(i + 1, 1) = AnsAdd(i)
     AnsAll(i + 1, 2) = "'" & Range(AnsAdd(i)).Formula
Next
     rng.Resize(AnsAdd.Count + 1, 2) = AnsAll
End Sub         ' ___ 모듈 종료

'새 버전으로 이동합니다'

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

10진수를 2진수로 변환하기(내장함수 한계극복) (0)	2015.06.23
숫자... 지수형식 오류 (0)	2015.05.14
사용자 함수를 찾는 vba 코드 입니다 (2) (0)	2014.08.21
[ 문자열 비교하는 사용자 함수 ] 예제 입니다. (1)	2009.03.29
Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10
매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기 (0)	2009.01.23

Posted by 오즈맨스머프

[ 문자열 비교하는 사용자 함수 ] 예제 입니다.

엑셀보조파일,기능,함수 2009. 3. 29. 10:51

안녕하세요. 오즈맨 입니다.
시트명은 Sheet1 입니다.
[ 문자열 비교하는 사용자 함수 ] 예제 입니다.


1
2
3
4
5
6
7
8
9
10

B	C	D	E
반복암기장			비교결과
단어	답안	뜻
aunt	고모 이모	이모 고모	같음
autumn		가을	<다름>
baby		아기	<다름>

banana		바나나	<다름>
band	끈 악단	끈 악단	같음
	부제 설명	부제,설명	같음
	부제 설명1	부제,설명2	<다름>
			같음

Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다.	결과	비고
1	E3	=StrComp(C3,D3)	같음
2		E3 셀의 수식을 여기에 복사하세요 -→ E3:E10
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일을 참조하시거나, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다. 수식에 사용자 정의 함수가 있습니다. 다른 파일에서는 안 될 수 있습니다. 주) 사용자 정의 함수를 많은 셀에 사용하면 버벅거릴 수 있습니다. 사용자 정의 함수를 사용했던 영역은 그 영역을 복사한 다음 편집/메뉴에서 선택하여 붙여넣기를 선택, 값으로 변환하는 것이 좋습니다.사용자 함수 만들기 입니다. [ 클릭 ]
예제에서는 "고모 이모" 와 "이모 고모" 를 같은 문자로 판단합니다.

Option Explicit
Function StrComp(ChkString_1 As String, ChkString_2 As String, Optional CheckString As String = " ")' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2009-03-29
Dim i As Double, cnt As Double
Dim HisStr1    As Variant
Dim HisStr2    As Variant
Dim MyStr1     As Variant
Dim MyStr2     As Variant
Dim ShrStr     As String
     ShrStr = ","
cnt = Len(ChkString_1) + Len(ChkString_2)
If CheckString = " " Then
     HisStr1 = Trim(ChkString_1)
     HisStr2 = Trim(ChkString_2)
Else
     HisStr1 = Trim(Replace(ChkString_1, CheckString, " "))
     HisStr2 = Trim(Replace(ChkString_2, CheckString, " "))
End If
     HisStr1 = Trim(Replace(HisStr1, ShrStr, " "))
     HisStr2 = Trim(Replace(HisStr2, ShrStr, " "))
For i = 1 To cnt
     HisStr1 = Replace(HisStr1, "  ", " ")
     HisStr2 = Replace(HisStr2, "  ", " ")
Next
     MyStr1 = Split(HisStr1)
     MyStr2 = Split(HisStr2)
If UBound(MyStr1) <> UBound(MyStr2) Then
     StrComp = "<다름>"
     Exit Function
End If
Dim StrNew As New Collection
cnt = UBound(MyStr1)
On Error Resume Next
For i = 0 To cnt
     StrNew.Add i, CStr(MyStr1(i))
Next
For i = 0 To cnt
     Err.Clear
     StrNew.Add i, CStr(MyStr2(i))
     If Err.Number = 0 Then
          StrComp = "<다름>"
          Exit Function
     End If
Next
     StrComp = "같음"
End Function         ' ___ 모듈 종료

파일 다운로드

StringCompareSample.xls

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

숫자... 지수형식 오류 (0)	2015.05.14
사용자 함수를 찾는 vba 코드 입니다 (2) (0)	2014.08.21
사용자 함수를 찾는 vba 코드 입니다. (0)	2009.05.26
Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10
매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기 (0)	2009.01.23
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가... (1)	2008.11.07

Posted by 오즈맨스머프

Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩

엑셀보조파일,기능,함수 2009. 3. 24. 11:00

Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 자료입니다. 최소한의 OS 및 설치된 응용 프로그램이 필요합니다. 이 설치 파일을 다운로드할수 있는 곳입니다 클릭하셔요(클릭).
필요한 사양	Windows 2000 Service Pack 4
	Windows XP Service Pack 1 , 2
	Windows Vista
	Windows Server 2003

사용 프로그램	Word	Excel	Microsoft PowerPoint

버전 2000	서비스팩 3	서비스팩 3	서비스팩 3
버전 2002	서비스팩 3	서비스팩 3	서비스팩 3
버전 2003	서비스팩 1 이상	서비스팩 1 이상	서비스팩 1 이상

기타	Microsoft Office Word Viewer 2003
	Microsoft Office Excel Viewer 2003
	Microsoft Office PowerPoint Viewer 2003
아래는
이전 버전의 Office 프로그램에서 2007 버전의 Word , Excel 및 PowerPoint 파일을 열고 저장하는 방법(클릭합니다)

이 글이 도움이 되기 바랍니다.

저작자표시 비영리 동일조건

'엑셀보조파일,기능,함수' 카테고리의 다른 글

사용자 함수를 찾는 vba 코드 입니다 (2) (0)	2014.08.21
사용자 함수를 찾는 vba 코드 입니다. (0)	2009.05.26
[ 문자열 비교하는 사용자 함수 ] 예제 입니다. (1)	2009.03.29
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10
매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기 (0)	2009.01.23
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가... (1)	2008.11.07
SUMPRODUCT 사용 예제 - 2 (0)	2008.10.04

Posted by 오즈맨스머프

수식에서 사용하는 -- 의 역할 예제 입니다.

엑셀보조파일,기능,함수 2009. 2. 10. 19:00

안녕하세요. 오즈맨 입니다.
시트명은 Sheet11 입니다.
[ 수식에서 사용하는 -- 의 역할 1 ] 예제 입니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14

A	B	C	D	E
2010 년 4월 의 개수는???
날짜	조건 비교 결과	결과를 숫자로	결과를 숫자로	결과를 숫자로
2010-03-18	FALSE	0	#VALUE!	0
2010-03-24	FALSE	0	#VALUE!	0
2010-04-08	TRUE	1	#VALUE!	1
2010-04-13	TRUE	1	#VALUE!	1
2010-04-21	TRUE	1	#VALUE!	1

2010-04-28	TRUE	1	#VALUE!	1
2010-05-31	FALSE	0	#VALUE!	0

	0	4	뭔가… 잘 안되네요	4
	합이 안나옵니다	되네요^^	뭔가… 잘 안되네요	되네요^^
		추천 1		추천 2
추천 2 방식이 속도가 가장 빠르더군요

Sheet11 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	B3	=TEXT(A3,"yyyy-mm")="2010-04"	FALSE
2		B3 셀의 수식을 여기에 복사하세요 -→ B3:B9
3	B11	=SUM(B3:B9)	0
4		B11 셀의 수식을 여기에 복사하세요 -→ B11:C11,E11
5	C3	=(TEXT(A3,"yyyy-mm")="2010-04")*1	0
6		C3 셀의 수식을 여기에 복사하세요 -→ C3:C9
7	D3	=VALUE(TEXT(A3,"yyyy-mm")="2010-04")	#VALUE!
8		D3 셀의 수식을 여기에 복사하세요 -→ D3:D9
9	E3	=--(TEXT(A3,"yyyy-mm")="2010-04")	0
10		E3 셀의 수식을 여기에 복사하세요 -→ E3:E9

시트명은 Sheet12 입니다.
[ 수식에서 사용하는 -- 의 역할 -2 ] 예제 입니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14

B	C	D	E	F	G
숫자입니다.	문자입니다	원 문자를 제거합니다.	제거하고 *1 합니다	Value 함수를 사용 합니다.	--를 사용 합니다.
다만
셀 서식이
# "원" 입니다.
10원	10원	10	10	10	10
20원	20원	20	20	20	20
30원	30원	30	30	30	30

40원	40원	40	40	40	40
100원	0	0	100	100	100
	합이 안 나옵니다.
	합이 안 나옵니다.	0	100	100	100
		역시나	되네요^^	되네요^^	되네요^^
		안됩니다.	방법 1	방법 2	방법 3
방법 3 방식이 속도가 가장 빠르더군요

Sheet12 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	B9	=SUM(B5:B8)	100원
2		B9 셀의 수식을 여기에 복사하세요 -→ B9:G9
3	D5	=SUBSTITUTE(C5,"원","")	10
4		D5 셀의 수식을 여기에 복사하세요 -→ D5:D8
5	D11	=SUMPRODUCT(SUBSTITUTE(C5:C8,"원",""))	0
6	E5	=SUBSTITUTE(C5,"원","")*1	10
7		E5 셀의 수식을 여기에 복사하세요 -→ E5:E8
8	E11	=SUMPRODUCT(SUBSTITUTE(C5:C8,"원","")*1)	100
9	F5	=VALUE(SUBSTITUTE(C5,"원","")*1)	10
10		F5 셀의 수식을 여기에 복사하세요 -→ F5:F8
11	F11	=SUMPRODUCT(VALUE(SUBSTITUTE(C5:C8,"원","")))	100
12	G5	=--SUBSTITUTE(C5,"원","")	10
13		G5 셀의 수식을 여기에 복사하세요 -→ G5:G8
14	G11	=SUMPRODUCT(--SUBSTITUTE(C5:C8,"원",""))	100
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다. 참고로 !!! 조건을 판단한 결과는 True / False 이라서 합을 구할 수 없습니다. 그리고 Left/Right/Substitute 등의 함수로 구한 결과는 숫자가 아닙니다. 그래서 -- 를 이용합니다. 논리값에 -- 를 하면 True 는 1 로 False 는 0 으로 변경되고 문자로 인식된 숫자는 올바른 숫자로 계산됩니다. 그 결과를 이용하면 합을 구할때 편리합니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

사용자 함수를 찾는 vba 코드 입니다. (0)	2009.05.26
[ 문자열 비교하는 사용자 함수 ] 예제 입니다. (1)	2009.03.29
Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기 (0)	2009.01.23
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가... (1)	2008.11.07
SUMPRODUCT 사용 예제 - 2 (0)	2008.10.04
SUMPRODUCT 사용 예제 - 1 (1)	2008.10.04

Posted by 오즈맨스머프

매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기

엑셀보조파일,기능,함수 2009. 1. 23. 15:00

엑셀에서 사용하는 파일은 매크로를 사용해서 사용자가 원하는 기능을 수행시킬 수 있습니다.

이 기능은 사용자에게는 매우 편리한 기능이지만
다른 사람이 만든 파일을 받을 경우 매크로로 인해 나도 모르는 사이에 엉뚱한 작동을 할 수 있습니다.

예를 들면
엑셀에서 취급하던 파일을 특정한 곳으로 메일을 발송하거나 수식이나 서식을 망가뜨릴 수 있겠지요.
그러므로 다른 사람이 만든 파일의 매크로는
특별히 믿을 수 있는 경우가 아니라면 일단 매크로를 실행하지 않아야 하겠지요?

아니면...
은행의 공인인증서를 사용하듯 매크로에다가 나만의 인증서를 넣어두고
“이 인증서가 있으면 안전한 매크로니 걱정 말고 실행해라” 고 하는 기능이 필요합니다. 혹은
“매크로에 서명을 해두고 서명이 있다면 걱정 말고 실행해라” 고 하는 기능이 필요합니다.

이 기능은 엑셀 2000부터 지원하며, 엑셀 자체 기능이 아닙니다.

Microsoft Internet Explorer 버전 4.0 이상에서 적용되는 기능으로
이 기능을 사용해서 엑셀 파일에 추가되는 매크로에 대한 안정성과 편리성을 높이는 것이지요

고로, 인증서를 만들고 지우는 거 엑셀에서 가능하지 않습니다.

인증서를 만들려면 : SelfCert.exe 파일로 만듭니다.
C:\Program Files\Microsoft Office\OFFICE11\SELFCERT.EXE 과 같은 곳에 있습니다.

지우는거 : 익스플로러에서 삭제할수 있습니다.

인증서를 만드는 방법

엑셀 파일에 매크로를 넣고 빼면서 사용하는 방법

인증서를 지우는 방법

주의 사항

주) 남이 넣어둔 인증서가 있는 매크로를 내가 수정하면
매크로에 저장된 인증서가 파기됩니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

[ 문자열 비교하는 사용자 함수 ] 예제 입니다. (1)	2009.03.29
Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가... (1)	2008.11.07
SUMPRODUCT 사용 예제 - 2 (0)	2008.10.04
SUMPRODUCT 사용 예제 - 1 (1)	2008.10.04
엑셀 2003 2007 동시에 설치하기 (4)	2008.10.03

Posted by 오즈맨스머프

엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가...

엑셀보조파일,기능,함수 2008. 11. 7. 08:56

엑셀 vbe 를 컨트롤 하다가 나오는 오류 중

'1004' 런타임 오류가 발생하였습니다:
'vbe'메서드('_Application'개체의)에서
오류가 발생하였습니다

라는 메시지가 나오면 황당합니다

이 오류의 해결책입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

Word, Excel 및 PowerPoint 2007 파일 형식용 Microsoft Office 호환팩 (2)	2009.03.24
수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10
매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기 (0)	2009.01.23
SUMPRODUCT 사용 예제 - 2 (0)	2008.10.04
SUMPRODUCT 사용 예제 - 1 (1)	2008.10.04
엑셀 2003 2007 동시에 설치하기 (4)	2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web) (3)	2008.10.02

Posted by 오즈맨스머프

SUMPRODUCT 사용 예제 - 2

엑셀보조파일,기능,함수 2008. 10. 4. 20:57

[ SUMPRODUCT 사용 예제 - 2 ] 에 대해 알아봅니다.
--->
두 개의 열에서 OR 조건 개수 구하기


1
2
3
4
5
6
7
8
9
10
11

A

B

C

D

E

F

G

H

I

J

K

L

M

홍길동 혹은 부산 의 개수는?

한방에 모은다면?

이름

지역

홍길동?

부산 ?

or 로...

숫자로

홍길동?

부산 ?

or 로...

숫자로

조건1+2

[1+2]>0 ?

숫자로

홍길동

부산

TRUE

1

TRUE

1

2

TRUE

1

삼식이

제주

FALSE

0

FALSE

TRUE

1

0

FALSE

0

심순애

부산

FALSE

TRUE

1

FALSE

TRUE

1

TRUE

1

홍길동

제주

TRUE

FALSE

TRUE

1

TRUE

FALSE

TRUE

1

TRUE

1

삼식이

부산

FALSE

TRUE

1

FALSE

TRUE

1

TRUE

1

심순애

제주

FALSE

0

FALSE

TRUE

1

0

FALSE

0

홍길동

부산

TRUE

1

TRUE

1

2

TRUE

1

5

뭔가 오류???

7

5

단순히 + 를 OR 로 적용한 수식은 엉뚱한 결과가 나올 수 있습니다.

7

5

Sheet2 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요. 수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다. $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	C3	=A3="홍길동"	TRUE
2		C3 셀의 수식을 여기에 복사하세요 -→ C3:C9
3	D3	=B3="부산"	TRUE
4		D3 셀의 수식을 여기에 복사하세요 -→ D3:D9
5	E3	=OR(C3,D3)	TRUE
6		E3 셀의 수식을 여기에 복사하세요 -→ E3:E9
7	F3	=E3*1	1
8		F3 셀의 수식을 여기에 복사하세요 -→ F3:F9
9	G3:G9	=A3:A9="홍길동" 셀 주소 모두를 선택하고 입력하세요	TRUE
10	H3:H9	=B3:B9="부산" 셀 주소 모두를 선택하고 입력하세요	TRUE
11	I3:I9	=OR(G3:G9,H3:H9) 셀 주소 모두를 선택하고 입력하세요	TRUE
12	J3:J9	=I3:I9*1 셀 주소 모두를 선택하고 입력하세요	1
13	K3	=(A3="홍길동")+(B3="부산")	2
14		K3 셀의 수식을 여기에 복사하세요 -→ K3:K9
15	L3	=K3>0	TRUE
16		L3 셀의 수식을 여기에 복사하세요 -→ L3:L9
17	M3	=--L3	1
18		M3 셀의 수식을 여기에 복사하세요 -→ M3:M9
19	F10	=SUM(F3:F9)	5
20		F10 셀의 수식을 여기에 복사하세요 -→ F10,J10:K10,M10
21	J11	=SUMPRODUCT((A3:A9="홍길동")+(B3:B9="부산")) [참고] 생각치 못한 엉뚱한 결과입니다.	7
22	M11	=SUMPRODUCT(--((A3:A9="홍길동")+(B3:B9="부산")>0)) [참고] 올바른 결과입니다.	5
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 분홍색 수식이 있으면 수식 입력에 주의하세요 !!! 도움이 되시기를 바랍니다. 참고로 배열수식은... 수식의 결과가 하나가 아닌 여러개의 결과를 가집니다. 배열수식의 사용의 주된 용도는 그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다. 수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은 수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며 이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은 수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고 이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은 실제 수식 내용이 배열수식일지라도 사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

수식에서 사용하는 -- 의 역할 예제 입니다. (1)	2009.02.10
매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기 (0)	2009.01.23
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가... (1)	2008.11.07
SUMPRODUCT 사용 예제 - 1 (1)	2008.10.04
엑셀 2003 2007 동시에 설치하기 (4)	2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web) (3)	2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수 (0)	2008.09.24

Posted by 오즈맨스머프

SUMPRODUCT 사용 예제 - 1

엑셀보조파일,기능,함수 2008. 10. 4. 20:41

[ SUMPRODUCT 사용 예제 - 1 ] 에 대해 알아봅니다.
--->
하나의 열에서 OR 조건 개수 구하기


1
2
3
4
5
6
7
8
9
10
11

A	B	C	D	E	F	G
	홍길동 혹은 삼식이 의 개수는?			한방에 모은다면?
이름	홍길동?	삼식이?	둘의 합	홍길동?	삼식이?	둘의 합
홍길동	TRUE	FALSE	1	TRUE	FALSE	1
삼식이	FALSE	TRUE	1	FALSE	TRUE	1
심순애	FALSE	FALSE	0	FALSE	FALSE	0

홍길동	TRUE	FALSE	1	TRUE	FALSE	1
삼식이	FALSE	TRUE	1	FALSE	TRUE	1
심순애	FALSE	FALSE	0	FALSE	FALSE	0
홍길동	TRUE	FALSE	1	TRUE	FALSE	1
	5		5			5
하나의 열에 or 조건을 사용하면 원하는 결과가 나옵니다.

Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요. 수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다. $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	B3	=A3="홍길동"	TRUE
2		B3 셀의 수식을 여기에 복사하세요 -→ B3:B9
3	C3	=A3="삼식이"	FALSE
4		C3 셀의 수식을 여기에 복사하세요 -→ C3:C9
5	D3	=B3+C3	1
6		D3 셀의 수식을 여기에 복사하세요 -→ D3:D9,G3:G9
7	E3:E9	=A3:A9="홍길동" 셀 주소 모두를 선택하고 입력하세요	TRUE
8	F3:F9	=A3:A9="삼식이" 셀 주소 모두를 선택하고 입력하세요	FALSE
9	B10	=SUMPRODUCT((A3:A9="홍길동")+(A3:A9="삼식이"))	5
10	D10	=COUNTIF(A3:A9,"홍길동")+COUNTIF(A3:A9,"삼식이")	5
11	G10	=SUM(G3:G9)	5
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 분홍색 수식이 있으면 수식 입력에 주의하세요 !!! 도움이 되시기를 바랍니다. 참고로 배열수식은... 수식의 결과가 하나가 아닌 여러개의 결과를 가집니다. 배열수식의 사용의 주된 용도는 그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다. 수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은 수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며 이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은 수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고 이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은 실제 수식 내용이 배열수식일지라도 사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

매크로 보안으로 안전하게 인증서 -1 인증서 만들기 사용하기 지우기 (0)	2009.01.23
엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가... (1)	2008.11.07
SUMPRODUCT 사용 예제 - 2 (0)	2008.10.04
엑셀 2003 2007 동시에 설치하기 (4)	2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web) (3)	2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수 (0)	2008.09.24
배열수식의 해설 - 3,4 (0)	2008.09.24

Posted by 오즈맨스머프

엑셀 2003 2007 동시에 설치하기

엑셀보조파일,기능,함수 2008. 10. 3. 03:53

엑셀 2003 과 엑셀 2007 을 동시에 사용하려면
서로 다른 폴더에 설치하면 사용 가능합니다.
다만 안정적으로 사용하기 위해서는 다음 순서대로 설치하시는게 가장 낫더군요

제가 사용했던 각 버전별 폴더입니다
c:\Program Files\Microsoft Office\0097\
c:\Program Files\Microsoft Office\2000\
c:\Program Files\Microsoft Office\2002\
c:\Program Files\Microsoft Office\2003\
c:\Program Files\Microsoft Office\2007\

프로그램의 설치순서는 낮은 버전부터 높은 버전까지 순서대로 설치했으며

마지막에 엑셀 2003의 추가기능을 설치했는데 그 이유는
xls 확장자가 무조건 2007 로 열리는 것 때문입니다.

설치 시디가 있을 경우 가장 간단한 방법이
엑셀 2007 이 깔린 상태에서 엑셀 2003 버전을 설치 하는 것입니다.

그러나
상위 버전이 설치된 이후 하위 버전을 설치할 경우
예기치 못한 오류가 나올 수 있으므로 아래와 같이

해결방법 1 엑셀 설치과정 중 복구 방법을 사용합니다.
1) 엑셀 2003의 설치화면에서 "다시 설치 또는 복구"를 선택합니다

2) 시작메뉴 바로가기 복원 을 선택합니다

해결방법 2 엑셀 2003의 추가기능만을 설치합니다.

1) 아래처럼 추가기능 파일이 있는 폴더를 삭제합니다.
c:\Program Files\Microsoft Office\2003\OFFICE11\Library\

2) 엑셀을 실행합니다
이때 다음과 같은 메시지가 나오면서 추가기능을 자동으로 설치합니다

이 메시지가 나온다면 아래는 더 볼 필요 없습니다.

3) 2) 의 메시지가 나오지 않으면 아래와 같이 추가기능을 선택합니다

프로그램 설치를 하지않고 지정하시려면 아래를 참조하십시오
http://ozman.tistory.com/65

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 VBE 에서 나오는 오류! VBE 메서드에서 _Application 개체의 오류가... (1)	2008.11.07
SUMPRODUCT 사용 예제 - 2 (0)	2008.10.04
SUMPRODUCT 사용 예제 - 1 (1)	2008.10.04
오즈맨의 Sheet to Web 2.2 (Sheet2Web) (3)	2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수 (0)	2008.09.24
배열수식의 해설 - 3,4 (0)	2008.09.24
배열수식의 해설 - 1,2 (0)	2008.09.24

Posted by 오즈맨스머프

오즈맨의 Sheet to Web 2.2 (Sheet2Web)

엑셀보조파일,기능,함수 2008. 10. 2. 00:07

시트의 내용이나 시트에 있는 수식을 설명할 때 유용한 도구입니다.

불필요하게 예제파일을 만들지 않아도 됩니다.
Sheet2Web (시트웹)은 아래와 같이 사용됩니다.


경미한 버그 조금 잡고 셀 너비 때문에 줄바꿈이 있거나 너비땜시 보기 안 좋을때 방법 개선했어유

다운로드 할 파일입니다. 둘 중 하나를 받으세요 (2008.10.01 파일 변경)	추가기능 xla 파일	Zip 압축파일
다운로드 할 파일입니다. 둘 중 하나를 받으세요 (2008.10.01 파일 변경)	invalid-file	invalid-file

이 기능을 사용하시려면 파일을 다운로드 받으신 다음
아래의 폴더 중 한곳에 복사해서 사용하세요
C:\Program Files\Microsoft Office\Office\XLStart
C:\Program Files\Microsoft Office\Office\Library

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

SUMPRODUCT 사용 예제 - 2 (0)	2008.10.04
SUMPRODUCT 사용 예제 - 1 (1)	2008.10.04
엑셀 2003 2007 동시에 설치하기 (4)	2008.10.03
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수 (0)	2008.09.24
배열수식의 해설 - 3,4 (0)	2008.09.24
배열수식의 해설 - 1,2 (0)	2008.09.24
배열수식의 응용-1 (0)	2008.09.24

Posted by 오즈맨스머프

숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수

엑셀보조파일,기능,함수 2008. 9. 24. 13:10

[ 숫자 문자 혼용한 숫자에 콤마를 삽입하기 ] 에 대해 알아봅니다.

2

3

4

A	B
홍길동1000000	홍길동1,000,000

홍길동1000 전우치 2000	홍길동1,000 전우치 2,000
12345만세34568조중동폐간	12,345만세34,568조중동폐간

Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	B2	=SampleStrNum(A2)	홍길동1,000,000
2		B2 셀의 수식을 여기에 복사하세요 -→ B2:B4
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일을 참조하시거나, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다.

Option Explicit

Function SampleStrNum(MyStr As String)
Dim i As Double, j As Double, k As Double
Dim Strr As String  '한 글자씩 오려내는 문자변수
Dim cnt As Double   '문자열의 길이
Dim VarStr() As String   '문자열끼리 모이는 임시변수
Dim VarNum() As String   '숫자끼리 모이는 임시변수
Dim Power1 As String     '직전에 처리한게 숫자였냐 문자냐
Dim Power2 As String     '지금 처리할 문자가 숫자냐 문자냐
Dim MyAnsw() As String   '최종 결과
Dim Num1st As Boolean    '첫 부분이 숫자이냐 아니냐
ReDim VarStr(1 To 1) As String     '문자열끼리 모이는 임시변수
ReDim VarNum(1 To 1) As String     '숫자끼리 모이는 임시변수
     cnt = Len(MyStr)    '입력받은 문자열의 길이

For i = 1 To cnt         '문자열 처음부터 끝까지를 보되
Strr = Mid(MyStr, i, 1)  '그 중 지정한 위치의 한 글자
     If IsNumeric(Strr) Then  '그 글자가 숫자이면
          Power2 = "num"      '지금 처리할 문자가 숫자면 Power2 를 num 으로
          If Power1 <> Power2 Then      'Power1 , Power2 가 다르다면
               j = j + 1      '숫자용 변수를 하나 증가시키고
               ReDim Preserve VarNum(1 To j) As String '변수를 재 선언하고
               Power1 = Power2     'Power1 , Power2 를 일치시킵니다.
          End If
               VarNum(j) = VarNum(j) & Strr  '해당 변수에 있는 숫자에 지금 숫자 한자리를 붙입니다.
     If i = 1 Then Num1st = True        '첫 부분이 숫자이냐 아니냐만을 판단합니다
     Else
          Power2 = "str"      '지금 처리할 문자가 문자라면 Power2 를 str 으로
          If Power1 <> Power2 Then      'Power1 , Power2 가 다르다면
               k = k + 1      '문자용 변수를 하나 증가시키고
               ReDim Preserve VarStr(1 To k) As String '변수를 재 선언하고
               Power1 = Power2     'Power1 , Power2 를 일치시킵니다.
          End If
               VarStr(k) = VarStr(k) & Strr  '해당 변수에 있는 숫자에 지금 숫자 한자리를 붙입니다.
     End If
Next
     If j > k Then  '숫자용 변수의 개수 j 와
          cnt = j   '문자용 변수 k 중 큰거를 기준으로합니다
     Else
          cnt = k
     End If
ReDim MyAnsw(1 To cnt) As String   '결과 배열을 만든 다음
If Num1st = True Then              '첫 부분이 숫자이면 여기를 수행
     For i = 1 To cnt
          If Not IsEmpty(VarNum(i)) Then     '숫자 부분이 공백 아니면(값이 있으면)
          MyAnsw(i) = Format(VarNum(i), "#,#")    ' 천단위 , 찍은값으로 표시
          End If
     MyAnsw(i) = MyAnsw(i) & VarStr(i)       '좀전에 받은 숫자와 문자변수중 하나를 연결합니다
     Next
Else                               '첫 부분이 문자이면 여기를 수행
     For i = 1 To cnt
     MyAnsw(i) = VarStr(i)         '문자변수중 하나를 대입한 다음
          If Not IsEmpty(VarNum(i)) Then     '숫자 부분이 공백 아니면(값이 있으면)
          MyAnsw(i) = MyAnsw(i) & Format(VarNum(i), "#,#")  ' 천단위 , 찍은값을 연결합니다
          End If
     Next
End If
SampleStrNum = Join(MyAnsw, "")    '숫자와 문자를 연결한 MyAnsw 의 각 값을 하나로 모읍니다.
End Function
잘 안될 경우 메일 주시거나 리플 남겨주세요

숫자문자혼용한숫자에콤마삽입.xls

숫자문자혼용한숫자에콤마삽입.zip

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

SUMPRODUCT 사용 예제 - 1 (1)	2008.10.04
엑셀 2003 2007 동시에 설치하기 (4)	2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web) (3)	2008.10.02
배열수식의 해설 - 3,4 (0)	2008.09.24
배열수식의 해설 - 1,2 (0)	2008.09.24
배열수식의 응용-1 (0)	2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings) (0)	2008.09.18

Posted by 오즈맨스머프

배열수식의 해설 - 3,4

엑셀보조파일,기능,함수 2008. 9. 24. 00:51

[ 배열수식의 해설 - 3,4 ] 에 대해 알아봅니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

A	B	C	D	E	F	G	H	I	J	K
			(3) =SUMPRODUCT((영역=조건),(영역=조건),(합계영역))
			=SUMPRODUCT((A3:A11=B16),(B3:B11=C16),(C3:C11))
지역	담당자	금액	FALSE	0	FALSE	0	금액	0	0	0 * 0 * 0
부산	홍길동	10	TRUE	0	TRUE	0	10	10	0	0 * 0 * 10
제주	심순애	20	FALSE	0	FALSE	0	20	20	0	0 * 0 * 20
대구	홍길동	30	FALSE	0	TRUE	0	30	30	0	0 * 0 * 30
독도	심순애	40	FALSE	0	FALSE	0	40	40	0	0 * 0 * 40
부산	홍길동	50	TRUE	0	TRUE	0	50	50	0	0 * 0 * 50
제주	심순애	60	FALSE	0	FALSE	0	60	60	0	0 * 0 * 60
대구	홍길동	70	FALSE	0	TRUE	0	70	70	0	0 * 0 * 70
독도	심순애	80	FALSE	0	FALSE	0	80	80	0	0 * 0 * 80
	계	360	조건1		조건2		합계영역		0	합계 [=조건1조건2합계]
							{=IF(ISNUMBER(H3:H11),H3:H11,0)}
					{=IF(ISNUMBER(F3:F11),F3:F11,0)}
조건	지역	담당자	{=IF(ISNUMBER(D3:D11),D3:D11,0)}
조건	부산	홍길동	SUMPRODUCT 함수는 각 인수를 1:1 로 곱하되 [[숫자가 아니면 모두 0 으로 변경합니다]] TRUE , FALSE 에 +-*/ 연산을 안하므로 0 으로 계산합니다.

합계	해설
0	해설 3	=SUMPRODUCT((A3:A11=B16),(B3:B11=C16),(C3:C11))
60	해설 4	=SUMPRODUCT(--(A3:A11=B16),--(B3:B11=C16),(C3:C11))


논리값 (TRUE,FALSE)을 그대로 두고 함수에 적용함과 논리값 (TRUE,FALSE)에 -- 연산을 함이 서로 다른 결과를 가져옵니다.	(4) =SUMPRODUCT(--(영역=조건),--(영역=조건),(합계영역))
	=SUMPRODUCT(--(A3:A11=B16),--(B3:B11=C16),(C3:C11))
	0	0	0	0	금액	0	0	0 * 0 * 0
	1	1	1	1	10	10	10	1 * 1 * 10
	0	0	0	0	20	20	0	0 * 0 * 20
	0	0	1	1	30	30	0	0 * 1 * 30
	0	0	0	0	40	40	0	0 * 0 * 40
	1	1	1	1	50	50	50	1 * 1 * 50
	0	0	0	0	60	60	0	0 * 0 * 60
	0	0	1	1	70	70	0	0 * 1 * 70
	0	0	0	0	80	80	0	0 * 0 * 80
	조건1		조건2		합계영역		60	합계 [=조건1조건2합계]
					{=IF(ISNUMBER(--(H24:H32)),--(H24:H32),0)}
			{=IF(ISNUMBER(--(F24:F32)),--(F24:F32),0)}
	{=IF(ISNUMBER(--(D24:D32)),--(D24:D32),0)}
	TRUE , FALSE 에 -- 연산을 하므로 1 로 계산합니다.
	논리결과(TRUE,FALSE)에 -1을 두번 곱합니다.
	그러나 사실은 [[부호변경 역할을 두번 수행합니다.]]
	숫자가 아닌것은 모두 0 으로 인식합니다(첫 행의 문자 "금액"은 0 으로 인식합니다)

Sheet3 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요. 수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다. $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	D3:D11	=(A3:A11=B16) 셀 주소 모두를 선택하고 입력하세요	FALSE
2	E3:E11	=IF(ISNUMBER(D3:D11),D3:D11,0) 셀 주소 모두를 선택하고 입력하세요	0
3		E3 셀의 수식을 여기에 복사하세요 -→ E3,G3,I3
4	F3:F11	=(B3:B11=C16) 셀 주소 모두를 선택하고 입력하세요	FALSE
5	H3:H11	=(C3:C11) 셀 주소 모두를 선택하고 입력하세요	금액
6	J3:J11	=E3:E11G3:G11I3:I11 셀 주소 모두를 선택하고 입력하세요	0
7		J3 셀의 수식을 여기에 복사하세요 -→ J3,J24
8	K3	=E3&" * "&G3&" * "&I3	0 * 0 * 0
9		K3 셀의 수식을 여기에 복사하세요 -→ K3:K11,K24:K32
10	J12	=SUM(J3:J11)	0
11		J12 셀의 수식을 여기에 복사하세요 -→ J12,J33
12	D24:D32	=--(A3:A11=B16) 셀 주소 모두를 선택하고 입력하세요	0
13	E24:E32	=IF(ISNUMBER(--(D24:D32)),--(D24:D32),0) 셀 주소 모두를 선택하고 입력하세요	0
14		E24 셀의 수식을 여기에 복사하세요 -→ E24,G24,I24
15	F24:F32	=--(B3:B11=C16) 셀 주소 모두를 선택하고 입력하세요	0
16	H24:H32	=(C3:C11) 셀 주소 모두를 선택하고 입력하세요	금액
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 분홍색 수식이 있으면 수식 입력에 주의하세요 !!! 도움이 되시기를 바랍니다. 참고로 배열수식은... 수식의 결과가 하나가 아닌 여러개의 결과를 가집니다. 배열수식의 사용의 주된 용도는 그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다. 수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은 수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며 이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은 수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고 이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은 실제 수식 내용이 배열수식일지라도 사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.
합계영역을 지정하기 않으면 조건에 맞는 개수를 구합니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2003 2007 동시에 설치하기 (4)	2008.10.03
오즈맨의 Sheet to Web 2.2 (Sheet2Web) (3)	2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수 (0)	2008.09.24
배열수식의 해설 - 1,2 (0)	2008.09.24
배열수식의 응용-1 (0)	2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings) (0)	2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) (0)	2008.09.09

Posted by 오즈맨스머프

배열수식의 해설 - 1,2

엑셀보조파일,기능,함수 2008. 9. 24. 00:51

[ 배열수식의 해설 - 1,2 ] 에 대해 알아봅니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

A	B	C	D	E	F
			(1) {=SUM((영역=조건)(영역=조건)(합계영역))}
			{=SUM((A3:A11=B16)(B3:B11=C16)(C3:C11))}
지역	담당자	금액	#VALUE!	=(지역=부산)(담당자=홍길동)금액	=(FALSE) * (FALSE) * 금액
부산	홍길동	10	10	=(부산=부산)(홍길동=홍길동)10	=(TRUE) * (TRUE) * 10
제주	심순애	20	0	=(제주=부산)(심순애=홍길동)20	=(FALSE) * (FALSE) * 20
대구	홍길동	30	0	=(대구=부산)(홍길동=홍길동)30	=(FALSE) * (TRUE) * 30
독도	심순애	40	0	=(독도=부산)(심순애=홍길동)40	=(FALSE) * (FALSE) * 40
부산	홍길동	50	50	=(부산=부산)(홍길동=홍길동)50	=(TRUE) * (TRUE) * 50
제주	심순애	60	0	=(제주=부산)(심순애=홍길동)60	=(FALSE) * (FALSE) * 60
대구	홍길동	70	0	=(대구=부산)(홍길동=홍길동)70	=(FALSE) * (TRUE) * 70
독도	심순애	80	0	=(독도=부산)(심순애=홍길동)80	=(FALSE) * (FALSE) * 80
	계	360	수식내용1	수식내용2	수식내용3
				{="=("&(A3:A11=B16)&") * ("&(B3:B11=C16)&") * "&C3:C11}
				{="=("&A3:A11&"="&B16&")("&B3:B11&"="&C16&")"&(C3:C11)}
조건	지역	담당자	{=(A3:A11=B16)(B3:B11=C16)(C3:C11)}
조건	부산	홍길동	#VALUE!	합계 오류=SUM(D3:D11)
			합을 구할 영역은 반드시 숫자 이어야하겠지요. 첫 행에서 당연 오류가 납니다
	합계	해설	이는 SUMPRODUCT 도 마찬가지입니다
	#VALUE!	해설 1	수식	{=SUM((A3:A11=B16)(B3:B11=C16)(C3:C11))}
	#VALUE!		수식	오류=SUMPRODUCT((A3:A11=B16)(B3:B11=C16)(C3:C11))

	60	해설 2	수식	{=SUM((A4:A11=B16)(B4:B11=C16)(C4:C11))}
	60		수식	=SUMPRODUCT((A4:A11=B16)(B4:B11=C16)(C4:C11))

첫 행에 논리값과 문자를 곱한게 문제입니다			(2) {=SUM((영역=조건)(영역=조건)(합계영역))}
			{=SUM((A4:A11=B16)(B4:B11=C16)(C4:C11))}
			[[ 합계영역에는 숫자만 있습니다 ]]
			10	(부산=부산)(홍길동=홍길동)10	(TRUE) * (TRUE) * 10
			0	(제주=부산)(심순애=홍길동)20	(FALSE) * (FALSE) * 20
			0	(대구=부산)(홍길동=홍길동)30	(FALSE) * (TRUE) * 30
			0	(독도=부산)(심순애=홍길동)40	(FALSE) * (FALSE) * 40
			50	(부산=부산)(홍길동=홍길동)50	(TRUE) * (TRUE) * 50
			0	(제주=부산)(심순애=홍길동)60	(FALSE) * (FALSE) * 60
			0	(대구=부산)(홍길동=홍길동)70	(FALSE) * (TRUE) * 70
			0	(독도=부산)(심순애=홍길동)80	(FALSE) * (FALSE) * 80
			수식내용1	수식내용2	수식내용3
				{="("&(A4:A11=B16)&") * ("&(B4:B11=C16)&") * "&C4:C11}
				{="("&A4:A11&"="&B16&")("&B4:B11&"="&C16&")"&(C4:C11)}
			{=(A4:A11=B16)(B4:B11=C16)(C4:C11)}
			60	합계 =SUM(D27:D34)
				TRUE , FALSE 에 +-*/ 연산을 하면 1,0 으로 계산합니다.

Sheet2 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요. 수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다. $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	D3:D11	=(A3:A11=B16)(B3:B11=C16)(C3:C11) 셀 주소 모두를 선택하고 입력하세요	#VALUE!
2	E3:E11	="=("&A3:A11&"="&B16&")("&B3:B11&"="&C16&")"&(C3:C11) 셀 주소 모두를 선택하고 입력하세요	=(지역=부산)(담당자=홍길동)금액
3	F3:F11	="=("&(A3:A11=B16)&") * ("&(B3:B11=C16)&") * "&C3:C11 셀 주소 모두를 선택하고 입력하세요	=(FALSE) * (FALSE) * 금액
4	D16	=SUM(D3:D11)	#VALUE!
5	B19	=SUM((A3:A11=B16)(B3:B11=C16)(C3:C11))	#VALUE!
6	B20	=SUMPRODUCT((A3:A11=B16)(B3:B11=C16)(C3:C11))	#VALUE!
7	B21	=SUM((A4:A11=B16)(B4:B11=C16)(C4:C11))	60
8	B22	=SUMPRODUCT((A4:A11=B16)(B4:B11=C16)(C4:C11))	60
9	D27:D34	=(A4:A11=B16)(B4:B11=C16)(C4:C11) 셀 주소 모두를 선택하고 입력하세요	10
10	E27:E34	="("&A4:A11&"="&B16&")("&B4:B11&"="&C16&")"&(C4:C11) 셀 주소 모두를 선택하고 입력하세요	(부산=부산)(홍길동=홍길동)10
11	F27:F34	="("&(A4:A11=B16)&") * ("&(B4:B11=C16)&") * "&C4:C11 셀 주소 모두를 선택하고 입력하세요	(TRUE) * (TRUE) * 10
12	D39	=SUM(D27:D34)	60
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 분홍색 수식이 있으면 수식 입력에 주의하세요 !!! 도움이 되시기를 바랍니다. 참고로 배열수식은... 수식의 결과가 하나가 아닌 여러개의 결과를 가집니다. 배열수식의 사용의 주된 용도는 그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다. 수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은 수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며 이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은 수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고 이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은 실제 수식 내용이 배열수식일지라도 사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

오즈맨의 Sheet to Web 2.2 (Sheet2Web) (3)	2008.10.02
숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수 (0)	2008.09.24
배열수식의 해설 - 3,4 (0)	2008.09.24
배열수식의 응용-1 (0)	2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings) (0)	2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) (0)	2008.09.09
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN) (0)	2008.09.07

Posted by 오즈맨스머프

배열수식의 응용-1

엑셀보조파일,기능,함수 2008. 9. 24. 00:50

[ 배열수식의 기본 ] 에 대해 알아봅니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

A	B	C	D	E	F	G	H
배열수식이란		홍길동이 광주에서 근무한 횟수는?			한방에 모은다면?
이름	근무처	이름=홍길동?	근무처=광주?	둘의 곱	이름=홍길동?	근무처=광주?	둘의 곱
홍길동	광주	TRUE	TRUE	1	TRUE	TRUE	1
삼식이	대전	FALSE	FALSE	0	FALSE	FALSE	0
심순애	광주	FALSE	TRUE	0	FALSE	TRUE	0
홍길동	대전	TRUE	FALSE	0	TRUE	FALSE	0
삼식이	광주	FALSE	TRUE	0	FALSE	TRUE	0

심순애	대전	FALSE	FALSE	0	FALSE	FALSE	0
홍길동	광주	TRUE	TRUE	1	TRUE	TRUE	1
삼식이	대전	FALSE	FALSE	0	FALSE	FALSE	0
심순애	대전	FALSE	FALSE	0	FALSE	FALSE	0
			합계	2		합계	2
참고로 엑셀에서는 TRUE/FALSE에 +-*/를 직접 계산하면 1/0 으로 인식합니다
한방에 모은 수식을 하나로 작성한다면 오른쪽과 같습니다 예제의 배열수식은 각행(열)의 연산을 한 다음 그들의 합을 구하는 방법입니다.							2
							2

Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요. 수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다. $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	C3	=A3="홍길동"	TRUE
2		C3 셀의 수식을 여기에 복사하세요 -→ C3:C11
3	D3	=B3="광주"	TRUE
4		D3 셀의 수식을 여기에 복사하세요 -→ D3:D11
5	E3	=C3*D3	1
6		E3 셀의 수식을 여기에 복사하세요 -→ E3:E11
7	F3:F11	=A3:A11="홍길동" 셀 주소 모두를 선택하고 입력하세요	TRUE
8	G3:G11	=B3:B11="광주" 셀 주소 모두를 선택하고 입력하세요	TRUE
9	H3:H11	=F3:F11*G3:G11 셀 주소 모두를 선택하고 입력하세요	1
10	E12	=SUM(E3:E11)	2
11		E12 셀의 수식을 여기에 복사하세요 -→ E12,H12
12	H14	=SUM((A3:A11="홍길동")*(B3:B11="광주"))	2
13	H15	=SUMPRODUCT((A3:A11="홍길동")*(B3:B11="광주"))	2
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 분홍색 수식이 있으면 수식 입력에 주의하세요 !!! 도움이 되시기를 바랍니다. 참고로 배열수식은... 수식의 결과가 하나가 아닌 여러개의 결과를 가집니다. 배열수식의 사용의 주된 용도는 그 결과중 특정한 몇 번째 값을 가져오는 등의 용도입니다. 수식을 입력할 때 수식입력줄에 {} 기호가 나오지 않는 것은 수식을 입력할때 {} 문자를 사용자가 입력하는 것을 방지함이 그 목적이며 이미 입력된 수식의 내용이 수식입력줄에서 {} 로 둘러싸이는 것은 수식 입력을 배열수식 형태로 입력했다(컨트롤+시프트+엔터)는 의미이고 이미 입력된 수식의 수식입력줄에 {} 가 보이지 않는것은 실제 수식 내용이 배열수식일지라도 사용자가 임의로(혹은 실수로) 일반수식처럼 입력했다는 의미입니다.

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

숫자 문자 혼용한 숫자에 콤마를 삽입하는 사용자 정의함수 (0)	2008.09.24
배열수식의 해설 - 3,4 (0)	2008.09.24
배열수식의 해설 - 1,2 (0)	2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings) (0)	2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) (0)	2008.09.09
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN) (0)	2008.09.07
엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003 (1)	2008.09.05

Posted by 오즈맨스머프

연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings)

엑셀보조파일,기능,함수 2008. 9. 18. 22:32

[ 연속적으로 이어지는 셀 주소 만들기 ] 에 대해 알아봅니다.

	A	B	C
1	원래문자열		연속으로 이어지는 문자열
2	C1~C3,C6,C7,C19		C1,C2,C3,C6,C7,C19
3	R25~R33		R25,R26,R27,R28,R29,R30,R31,R32,R33
4	C8~C17		C8,C9,C10,C11,C12,C13,C14,C15,C16,C17
5	R10		R10

Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다.	결과	비고
1	C2	=ShrStrings(A2)	C1,C2,C3,C6,C7,C19
2		C2 셀의 수식을 여기에 복사하세요 -→ C2:C5
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다. 수식에 사용자 정의 함수가 있습니다. 다른 파일에서는 안 될 수 있습니다. 주) 사용자 정의 함수를 많은 셀에 사용하면 버벅거릴 수 있습니다. 사용자 정의 함수를 사용했던 영역은 그 영역을 복사한 다음 편집/메뉴에서 선택하여 붙여넣기를 선택, 값으로 변환하는 것이 좋습니다.

Option Explicit

Function ShrStrings(str As String)
Dim tmp1, tmp2
Dim i As Double, j As Double, k As Double
tmp1 = Split("," & str, ",")
Dim cnt1 As Double
Dim cnt2 As Double
cnt1 = UBound(tmp1)
For i = 1 To cnt1
tmp2 = Split("~" & tmp1(i), "~")
k = UBound(tmp2)
If k = 1 Then
ShrStrings = ShrStrings & "," & tmp1(i)
Else
cnt2 = Range(tmp2(2)).Row - Range(tmp2(1)).Row + 1
For j = 1 To cnt2
ShrStrings = ShrStrings & "," & Range(tmp2(1))(j, 1).Address(0, 0)
Next
End If
Next
ShrStrings = Trim(Mid(ShrStrings, 2))
End Function

잘 안될 경우 메일 주시거나 리플 남겨주세요
이 파일을 다운로드 해보세요

invalid-file

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

배열수식의 해설 - 3,4 (0)	2008.09.24
배열수식의 해설 - 1,2 (0)	2008.09.24
배열수식의 응용-1 (0)	2008.09.24
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) (0)	2008.09.09
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN) (0)	2008.09.07
엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003 (1)	2008.09.05
엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003 (0)	2008.09.05

Posted by 오즈맨스머프

숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR)

엑셀보조파일,기능,함수 2008. 9. 9. 23:07

  안녕하세요. 오즈맨 입니다.
시트명은 Sheet1 입니다.
[ 숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) ] 예제 입니다.
   예제 - 1 NUM2DOLLAR (숫자)
   기본 화폐 단위 = 달러
   예제 - 2 NUM2DOLLAR (숫자, "fran")


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

A	B	C	D	E
			1	One Dollar.
			109	One Hundred Nine Dollars.
			218	Two Hundred Eighteen Dollars.
			326	Three Hundred Twenty Six Dollars.
			435	Four Hundred Thirty Five Dollars.
			1,443	One Thousand Four Hundred Forty Three Dollars.
			2,452	Two Thousand Four Hundred Fifty Two Dollars.
			12,460	Twelve Thousand Four Hundred Sixty Dollars.
			22,469	Twenty Two Thousand Four Hundred Sixty Nine Dollars.
			30,000	Thirty Thousand Dollars.
			30,003	Thirty Thousand Three Dollars.
			40,000	Forty Thousand Dollars.
			50,009	Fifty Thousand Nine Dollars.
			7,000,015	Seven Million Fifteen Dollars.
			7,001,015	Seven Million One Thousand Fifteen Dollars.
		1		One Dollar.
		109.01		One Hundred Nine Dollars and One Cents.

		218.02	Two Hundred Eighteen Dollars and Two Cents.
		326.08	Three Hundred Twenty Six Dollars and Eight Cents.
		435.09	Four Hundred Thirty Five Dollars and Nine Cents.
		1,043.10	One Thousand Forty Three Dollars and Ten Cents.
		2,052.91	Two Thousand Fifty Two Dollars and Ninety One Cents.
		12,060.00	Twelve Thousand Sixty Dollars.
		22,069.01	Twenty Two Thousand Sixty Nine Dollars and One Cents.
		30,010.02	Thirty Thousand Ten Dollars and Two Cents.
		30,013.08	Thirty Thousand Thirteen Dollars and Eight Cents.
		7,001,005.11	Seven Million One Thousand Five Dollars and Eleven Cents.
		7,001,015.11	Seven Million One Thousand Fifteen Dollars and Eleven Cents.
	102.1333		One Hundred Two Dollars and Thirteen Cents and Three Three.
	102.2536		One Hundred Two Dollars and Twenty Five Cents and Three Six.
	102.9042		One Hundred Two Dollars and Ninety Cents and Four Two.
	110.1351		One Hundred Ten Dollars and Thirteen Cents and Five One.
	200.1354		Two Hundred Dollars and Thirteen Cents and Five Four.
1			One franc.
109			One Hundred Nine franc's.

Sheet1 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	E1	=NUM2DOLLAR(D1)	One Dollar.
2		E1 셀의 수식을 여기에 복사하세요 -→ E1:E15
3	E16	=NUM2DOLLAR(C16)	One Dollar.
4		E16 셀의 수식을 여기에 복사하세요 -→ E16:E28
5	E29	=NUM2DOLLAR(B29)	One Hundred Two Dollars and Thirteen Cents and Three Three.
6		E29 셀의 수식을 여기에 복사하세요 -→ E29:E33
7	E34	=NUM2DOLLAR(A34,"franc")	One franc.
8		E34 셀의 수식을 여기에 복사하세요 -→ E34:E35
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일을 참조하시거나, 원하는 부분을 시트(셀)에 붙여 넣으세요. NUM2DOLLARsample.xls 도움이 되시기를 바랍니다. 수식에 사용자 정의 함수가 있습니다. 다른 파일에서는 안 될 수 있습니다. 주) 사용자 정의 함수를 많은 셀에 사용하면 버벅거릴 수 있습니다. 사용자 정의 함수를 사용했던 영역은 그 영역을 복사한 다음 편집/메뉴에서 선택하여 붙여넣기를 선택, 값으로 변환하는 것이 좋습니다.사용자 함수 만들기 입니다. [ 클릭 ]

Option Explicit

Function NUM2DOLLAR(WhatsNumber As Double, Optional MoneyType As String = "Dollar")' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2008-09-09
Dim iLoop As Integer               'Loop
Dim CommaNumbr(1 To 5) As String   'Share by 3 Digit
Dim StrngNum_1 As String           'String Number to Format 000 000...
Dim StrngNum_2 As String           'String Number of Decimal
Dim WorkNumber As Variant          '000 000... Split
Dim NumInt     As String           'Number Integer
Dim NumDec     As String           'Decimal Number
Dim PosDot     As Integer
Dim PosDec     As Integer
Dim DecCent    As String

On Error Resume Next
     CommaNumbr(1) = " Trillion ": CommaNumbr(2) = " Billion "
     CommaNumbr(3) = " Million ":  CommaNumbr(4) = " Thousand "

PosDot = InStr(WhatsNumber, ".")

If WhatsNumber = 0 Then
          NUM2DOLLAR = "No Dollar."
     If MoneyType <> "Dollar" Then
          NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType)
     End If
          Exit Function
ElseIf WhatsNumber = 1 Then
          NUM2DOLLAR = "One Dollar."
     If MoneyType <> "Dollar" Then
          NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType)
     End If
          Exit Function

End If

If PosDot = 0 Then
     StrngNum_1 = Format(WhatsNumber, " 000 000 000 000 000")
Else
     StrngNum_1 = Format(Left(WhatsNumber, PosDot - 1), " 000 000 000 000 000")
     StrngNum_2 = Mid(WhatsNumber, PosDot + 1)
     PosDec = Len(StrngNum_2)
End If
     WorkNumber = Split(StrngNum_1)

For iLoop = 1 To UBound(WorkNumber)
     If WorkNumber(iLoop) <> "000" Then
     NumInt = NumInt & String_1(Left(WorkNumber(iLoop), 1)) & String_2(Right(WorkNumber(iLoop), 2)) & CommaNumbr(iLoop)
     End If
Next

If NumInt = "" Then
     NumInt = "Zero Dollar"
Else
     NumInt = NumInt & " Dollars "
End If

     If PosDot = 0 Then
          NUM2DOLLAR = NumInt
     Else
          If PosDec = 1 Then
                    NUM2DOLLAR = NumInt & " and " & String_2(StrngNum_2 & "0") & " Cents "
          ElseIf PosDec = 2 Then
                    NUM2DOLLAR = NumInt & " and " & String_2(StrngNum_2) & " Cents "
          Else
               DecCent = Left(StrngNum_2, 2)
               If DecCent = "00" Then
                    NumDec = String_2(DecCent) & " NoCent "
               Else
                    NumDec = String_2(DecCent) & " Cents and"
               End If
               For iLoop = 3 To PosDec
                    If Mid(StrngNum_2, iLoop, 1) = "0" Then
                    NumDec = NumDec & " Zero"
                    Else
                    NumDec = NumDec & " " & String_3(Mid(StrngNum_2, iLoop, 1))
                    End If
               Next
                    NumDec = NumDec & " "
                    NUM2DOLLAR = NumInt & " and " & NumDec
          End If
     End If

     NUM2DOLLAR = RTrim(Replace(NUM2DOLLAR, "  ", " "))
If MoneyType <> "Dollar" Then
     NUM2DOLLAR = Replace(NUM2DOLLAR, "Dollar", MoneyType & "'")
     'If Want "francs" Instead of "franc's" then
     'Use This
     'NUM2Dollar = Replace(NUM2Dollar, "Dollar", MoneyType)
End If
     NUM2DOLLAR = NUM2DOLLAR & "."
End Function         ' ___ 모듈 종료

Function String_1(MyString As String) ' 100's Number' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2008-09-09
If MyString <> "0" Then
     String_1 = String_3(MyString) & " Hundred "
End If
End Function         ' ___ 모듈 종료

Function String_2(MyString As String) ' 10's Number (20-90)' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2008-09-09
Select Case Left(MyString, 1)
     Case "1"
          String_2 = String_21(MyString)
          Exit Function
     Case "2": String_2 = "Twenty ":    Case "3": String_2 = "Thirty "
     Case "4": String_2 = "Forty ":     Case "5": String_2 = "Fifty "
     Case "6": String_2 = "Sixty ":     Case "7": String_2 = "Seventy "
     Case "8": String_2 = "Eighty ":    Case "9": String_2 = "Ninety "
End Select
          String_2 = String_2 & String_3(Right(MyString, 1))
End Function         ' ___ 모듈 종료

Function String_21(MyString As String) ' 10-19's Number' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2008-09-09
Select Case MyString
     Case "10": String_21 = "Ten":      Case "11": String_21 = "Eleven"
     Case "12": String_21 = "Twelve":   Case "13": String_21 = "Thirteen"
     Case "14": String_21 = "Fourteen": Case "15": String_21 = "Fifteen"
     Case "16": String_21 = "Sixteen":  Case "17": String_21 = "Seventeen"
     Case "18": String_21 = "Eighteen": Case "19": String_21 = "Nineteen"
End Select
End Function         ' ___ 모듈 종료

Function String_3(MyString As String) ' 1-9's Number' === 모듈 시작
' CodeBy [ 오즈맨 ] , Date : 2008-09-09
Select Case MyString
     Case "1": String_3 = "One":        Case "2": String_3 = "Two"
     Case "3": String_3 = "Three":      Case "4": String_3 = "Four"
     Case "5": String_3 = "Five":       Case "6": String_3 = "Six"
     Case "7": String_3 = "Seven":      Case "8": String_3 = "Eight"
     Case "9": String_3 = "Nine"
End Select
End Function         ' ___ 모듈 종료
첨부파일

NUM2DOLLARsample.xls

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

배열수식의 해설 - 1,2 (0)	2008.09.24
배열수식의 응용-1 (0)	2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings) (0)	2008.09.18
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN) (0)	2008.09.07
엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003 (1)	2008.09.05
엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003 (0)	2008.09.05
엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04

Posted by 오즈맨스머프

여러 영역의 문자열 합치는 사용자 함수 (STRJOIN)

엑셀보조파일,기능,함수 2008. 9. 7. 18:31

[ 여러 영역의 문자열 합치기 (STRJOIN) ] 에 대해 알아봅니다.
   --->
   예제 - 1 STRJOIN (문자(영역)1, 문자(영역)2,...)
   기본연결문자 = ,
   예제 - 2 STRJOIN (문자(영역)1, 문자(영역)2, ..., 연결문자)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

A	B	C	D	E
길동		2008년12월31일		/
삼순		0.12
태한		a
연습		b

길동,2008년12월31일,/,삼순,0.12,태한,a,연습,b
기본 값 "," 으로 연결됩니다.
길동,2008년12월31일,/,삼순,태한,연습

기본 값 "," 으로 연결됩니다.
길동,2008년12월31일,삼순,0.12,연습,/
기본 값 "," 으로 연결됩니다.
길동/2008년12월31일/삼순/0.12/연습
사용자 지정문자 "/" 로 연결됩니다.
길동 2008년12월31일 삼순 0.12 연습
사용자 지정문자 " " 로 연결됩니다.
길동 , 2008년12월31일 , 삼순 , 0.12 , 연습
사용자 지정문자 " , " 로 연결됩니다.

StrMix 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	A6	=STRJOIN(A1:E4)	길동,2008년12월31일,/,삼순,0.12,태한,a,연습,b
2	A8	=STRJOIN(1:1,A2:A4)	길동,2008년12월31일,/,삼순,태한,연습
3	A10	=STRJOIN(A1:C2,A4,E1)	길동,2008년12월31일,삼순,0.12,연습,/
4	A12	=STRJOIN(A1:C2,A4,TEXT(E1,"#"))	길동/2008년12월31일/삼순/0.12/연습
5	A14	=STRJOIN(A1:C2,A4," ")	길동 2008년12월31일 삼순 0.12 연습
6	A16	=STRJOIN(A1:C2,A4," , ")	길동 , 2008년12월31일 , 삼순 , 0.12 , 연습
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다.

Option Explicit

Function STRJOIN(ParamArray VariantR())
Dim i As Double, j As Double
Dim AddStr     As String
Dim MaxB       As Double
Dim AnsS()     As String
Dim ChkStrN    As Double
     MaxB = UBound(VariantR)

If TypeName(VariantR(MaxB)) = "Range" Then
     AddStr = Chr(65000) & "," & Chr(65000)
Else
     AddStr = Chr(65000) & VariantR(MaxB) & Chr(65000)
     MaxB = MaxB - 1
End If

ReDim AnsS(MaxB) As String
     ChkStrN = Len(AddStr)
Dim r As Range
For i = 0 To MaxB
     If TypeName(VariantR(i)) = "Range" Then
     If VariantR(i).Count > 1 Then
          Set r = VariantR(i)
          AnsS(i) = MVV(r, AddStr)
          Else
          AnsS(i) = VariantR(i)
          End If
     Else
          AnsS(i) = VariantR(i)
     End If
Next
     STRJOIN = Join(AnsS, AddStr)
If InStr(STRJOIN, AddStr) = 1 Then
STRJOIN = Mid(STRJOIN, ChkStrN + 1)
ElseIf Right(STRJOIN, ChkStrN) = AddStr Then
STRJOIN = Left(STRJOIN, Len(STRJOIN) - ChkStrN)
End If
     STRJOIN = Replace(STRJOIN, Chr(65000), "")
End Function

Function MVV(tmpV As Range, tmpStr As String)
Dim ii As Double
Dim jj As Double
Dim kk As Double
Dim LB1 As Double, LB2 As Double
Dim AnsY
LB1 = tmpV.Rows.Count
LB2 = tmpV.Columns.Count
ReDim AnsY(1 To 1)
     For ii = 1 To LB1
     For jj = 1 To LB2
          If CStr(tmpV(ii, jj)) <> "" Then
               kk = kk + 1
               ReDim Preserve AnsY(1 To kk)
               AnsY(kk) = tmpV(ii, jj).Text
          End If
     Next
     Next
MVV = Join(AnsY, tmpStr)
End Function
잘 안될 경우 메일 주시거나 리플 남겨주세요

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

배열수식의 응용-1 (0)	2008.09.24
연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings) (0)	2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) (0)	2008.09.09
엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003 (1)	2008.09.05
엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003 (0)	2008.09.05
엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03

Posted by 오즈맨스머프

엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003

엑셀보조파일,기능,함수 2008. 9. 5. 02:10

[ 2007 의 SUMIFS 를 대신하는 SUMIFS2003 ] 에 대해 알아봅니다.
SUMIFS2003(합계영역, 영역,조건, 영역,조건, 영역,조건...) 으로 입력합니다.


10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

A	B	C	G	H	I
성명	출장지	수당	조건1	조건2	조건3
길동이	서울	100	길동이	서울	200
삼순이	부산	110	합계		비교결과
만식이	제주	120	320	320	TRUE
꺽정이	서울	130	320	320	TRUE
길동이	부산	140	0	0	TRUE
삼순이	제주	150	500	500	TRUE
만식이	서울	160	500	500	TRUE

꺽정이	부산	170	0	0	TRUE
길동이	제주	180	460	460	TRUE
삼순이	서울	190	460	460	TRUE
만식이	부산	200	0	0	TRUE
꺽정이	제주	210	320	320	TRUE
길동이	서울	220	320	320	TRUE
삼순이	부산	230	420	420	TRUE
꺽정이	부산	290	420	420	TRUE
		2600

Ifs_K 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	C26	=SUM(C11:C25)	2600
2	G13	=SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25=H11),$C$11:$C$25)	320
3	G14	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25="서울"),$C$11:$C$25)	320
4	G15	=SUMPRODUCT(($A$11:$A$25="=G11")*($B$11:$B$25="=H11"),$C$11:$C$25)	0
5	G16	=SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25>=H11),$C$11:$C$25)	500
6	G17	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25>="서울"),$C$11:$C$25)	500
7	G18	=SUMPRODUCT(($A$11:$A$25="=G11")*($B$11:$B$25>="=H11"),$C$11:$C$25)	0
8	G19	=SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25< =H11),$C$11:$C$25)	460
9	G20	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25< ="서울"),$C$11:$C$25)	460
10	G21	=SUMPRODUCT(($A$11:$A$25="=G11")*($B$11:$B$25< ="=H11"),$C$11:$C$25)	0
11	G22	=SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25< >H11),$C$11:$C$25)	320
12	G23	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25< >"서울"),$C$11:$C$25)	320
13	G24	=SUMPRODUCT(($A$11:$A$25=G11)*($C$11:$C$25< =200),$C$11:$C$25)	420
14	G25	=SUMPRODUCT(($A$11:$A$25=G11)*($C$11:$C$25< =I11),$C$11:$C$25)	420
15	H13	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,H11)	320
16	H14	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"길동이",$B$11:$B$25,"서울")	320
17	H15	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"=G11",$B$11:$B$25,"=H11")	0
18	H16	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,">="&H11)	500
19	H17	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"길동이",$B$11:$B$25,">=서울")	500
20	H18	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"=G11",$B$11:$B$25,">="&"H11")	0
21	H19	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,"< ="&H11)	460
22	H20	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"길동이",$B$11:$B$25,"< =서울")	460
23	H21	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"=G11",$B$11:$B$25,"< ="&"H11")	0
24	H22	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,"< >"&H11)	320
25	H23	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"길동이",$B$11:$B$25,"< >서울")	320
26	H24	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$C$11:$C$25,"< =200")	420
27	H25	=SUMIFS2003($C$11:$C$25,$A$11:$A$25,"길동이",$C$11:$C$25,"< ="&I11)	420
28	I13	=G13=H13	TRUE
29		I13 셀의 수식을 여기에 복사하세요 -→ I13:I25
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다. ※ 만일 수식에 사용된 공백때문에 오류(다른 값)가 나오면 공백을 확인바랍니다.

2007 의 SUMIFS 를 대신하는 SUMIFS2003
SUMIFS2003(합계영역, 영역,조건, 영역,조건, 영역,조건...) 으로 입력합니다.

Function SUMIFS2003(sumarr As Range, ParamArray var() As Variant)
On Error Resume Next
Dim i7 As Double, LB As Double, UB As Double
Dim adr As Variant
LB = LBound(var)
UB = UBound(var)
If UB Mod 2 = 0 Then
SUMIFS2003 = "Sum / Condition1,2 / 3,4"
Exit Function
End If
ReDim adr(LB To UB)
For i7 = LB To UB Step 2
adr(i7) = RT(var(i7), var(i7 + 1))
Next
adr(0) = RT(sumarr, "") & adr(0)

SUMIFS2003 = "=SUMPRODUCT(--(" & Join(adr, "),--(") & ")"
For i7 = LB To UB
SUMIFS2003 = Replace(SUMIFS2003, "--(),", "")
Next
SUMIFS2003 = Replace(SUMIFS2003, ",--()", ")")
SUMIFS2003 = Application.Evaluate(SUMIFS2003)
End Function

Function RT(vv, cc)
On Error Resume Next
RT = Application.Evaluate(cc)
If CStr(cc) = "" Then
RT = "" & vv.Address(External:=True) & "),--("
Else
If Not (IsError(RT)) Then
If IsEmpty(RT) Then
RT = vv.Address(External:=True) & "=""" & cc & """"
ElseIf IsNumeric(RT) Then
RT = vv.Address(External:=True) & "=" & cc
ElseIf IsDate(RT) Then
RT = vv.Address(External:=True) & "=" & cc
ElseIf TypeName(Range(cc)) = "Range" Then
If IsNumeric(RT) Then
RT = vv.Address(External:=True) & "=" & cc
Else
RT = vv.Address(External:=True) & "=""" & cc & """"
End If
Else
If IsNumeric(RT) Then
RT = vv.Address(External:=True) & RT
ElseIf IsDate(RT) Then
RT = vv.Address(External:=True) & RT
Else
RT = vv.Address(External:=True) & "=""" & RT & """"
End If
End If
Else
Select Case RT
Case CVErr(xlErrDiv0): GoTo ers
Case CVErr(xlErrNA): GoTo ers
Case CVErr(xlErrName): GoTo ers
Case CVErr(xlErrNull): GoTo ers
Case CVErr(xlErrNum): GoTo ers
Case CVErr(xlErrRef): GoTo ers
Case CVErr(xlErrValue): GoTo ers
End Select
End If
End If
Exit Function
ers:
If Err.Number = 13 Then
RT = "--(" & vv.Address(External:=True) & "),"""" & cc & """""
Else
If IsNumeric(cc) Then
RT = vv.Address(External:=True) & "=" & cc
Else
RT = vv.Address(External:=True) & GetRT(cc)
End If
End If
End Function

Function GetRT(RTstr)
Dim RTtemp1 As String
Dim RTtemp2 As String
Dim RTLeft As String
RTLeft = Left(RTstr, 1)
RTtemp1 = Mid(RTstr, 2)
RTtemp2 = Mid(RTtemp1, 2)
If IsNumeric(RTtemp1) Then
ElseIf IsDate(RTtemp1) Then
Else
RTtemp1 = """" & RTtemp1 & """"
End If

If IsNumeric(RTtemp2) Then
ElseIf IsDate(RTtemp2) Then
Else
RTtemp2 = """" & RTtemp2 & """"
End If

Select Case Left(RTstr, 2)
Case ">=": GetRT = ">=" & RTtemp2
Case "=>": GetRT = ">=" & RTtemp2
Case "<=": GetRT = "<=" & RTtemp2
Case "=<": GetRT = "<=" & RTtemp2
Case "<>": GetRT = "<>" & RTtemp2
Case Else
Select Case RTLeft
Case "=": GetRT = "=" & RTtemp1
Case ">": GetRT = ">" & RTtemp1
Case "<": GetRT = "<" & RTtemp1
Case Else
If IsNumeric(RTstr) Then
GetRT = "=" & RTstr
Else
GetRT = "=""" & RTstr & """"
End If
End Select
End Select
End Function
잘 안될 경우 메일 주시거나 리플 남겨주세요

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

연속적으로 이어지는 셀 주소 만드는 사용자 함수 (ShrStrings) (0)	2008.09.18
숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) (0)	2008.09.09
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN) (0)	2008.09.07
엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003 (0)	2008.09.05
엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28

Posted by 오즈맨스머프

엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003

엑셀보조파일,기능,함수 2008. 9. 5. 02:07

[ 2007 의 COUNTIFS 를 대신하는 COUNTIFS2003 ] 에 대해 알아봅니다.
COUNTIFS2003(영역,조건, 영역,조건, 영역,조건...) 으로 입력합니다.


10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

A	B	C	D	E	F
성명	출장지	수당	조건1	조건2	조건3
길동이	서울	100	길동이	서울	200
삼순이	부산	110	개수		비교결과
만식이	제주	120	2	2	TRUE
꺽정이	서울	130	2	2	TRUE
길동이	부산	140	0	0	TRUE
삼순이	제주	150	3	3	TRUE
만식이	서울	160	3	3	TRUE

꺽정이	부산	170	0	0	TRUE
길동이	제주	180	3	3	TRUE
삼순이	서울	190	3	3	TRUE
만식이	부산	200	0	0	TRUE
꺽정이	제주	210	2	2	TRUE
길동이	서울	220	2	2	TRUE
삼순이	부산	230	3	3	TRUE
꺽정이	부산	290	3	3	TRUE
		2600

Ifs_K 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	C26	=SUM(C11:C25)	2600
2	D13	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25=E11))	2
3	D14	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25="서울"))	2
4	D15	=SUMPRODUCT(($A$11:$A$25="=D11")*($B$11:$B$25="=E11"))	0
5	D16	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25>=E11))	3
6	D17	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25>="서울"))	3
7	D18	=SUMPRODUCT(($A$11:$A$25="=D11")*($B$11:$B$25>="=E11"))	0
8	D19	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25< =E11))	3
9	D20	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25< ="서울"))	3
10	D21	=SUMPRODUCT(($A$11:$A$25="=D11")*($B$11:$B$25< ="=E11"))	0
11	D22	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25< >E11))	2
12	D23	=SUMPRODUCT(($A$11:$A$25="길동이")*($B$11:$B$25< >"서울"))	2
13	D24	=SUMPRODUCT(($A$11:$A$25=D11)*($C$11:$C$25< =200))	3
14	D25	=SUMPRODUCT(($A$11:$A$25=D11)*($C$11:$C$25< =F11))	3
15	E13	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,E11)	2
16	E14	=COUNTIFS2003($A$11:$A$25,"길동이",$B$11:$B$25,"서울")	2
17	E15	=COUNTIFS2003($A$11:$A$25,"=D11",$B$11:$B$25,"=E11")	0
18	E16	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,">="&E11)	3
19	E17	=COUNTIFS2003($A$11:$A$25,"길동이",$B$11:$B$25,">=서울")	3
20	E18	=COUNTIFS2003($A$11:$A$25,"=D11",$B$11:$B$25,">="&"E11")	0
21	E19	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,"< ="&E11)	3
22	E20	=COUNTIFS2003($A$11:$A$25,"길동이",$B$11:$B$25,"< =서울")	3
23	E21	=COUNTIFS2003($A$11:$A$25,"=D11",$B$11:$B$25,"< ="&"E11")	0
24	E22	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,"< >"&E11)	2
25	E23	=COUNTIFS2003($A$11:$A$25,"길동이",$B$11:$B$25,"< >서울")	2
26	E24	=COUNTIFS2003($A$11:$A$25,D11,$C$11:$C$25,"< =200")	3
27	E25	=COUNTIFS2003($A$11:$A$25,"길동이",$C$11:$C$25,"< ="&F11)	3
28	F13	=D13=E13	TRUE
29		F13 셀의 수식을 여기에 복사하세요 -→ F13:F25
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다. ※ 만일 수식에 사용된 공백때문에 오류(다른 값)가 나오면 공백을 확인바랍니다.

2007 의 COUNTIFS 를 대신하는 COUNTIFS2003
COUNTIFS2003(영역,조건, 영역,조건, 영역,조건...) 으로 입력합니다.

Function COUNTIFS2003(ParamArray var() As Variant)
On Error Resume Next
Dim i7 As Double, LB As Double, UB As Double
Dim adr As Variant
LB = LBound(var)
UB = UBound(var)
If UB Mod 2 = 0 Then
COUNTIFS2003 = "Condition1,2 / 3,4"
Exit Function
End If
ReDim adr(LB To UB)
For i7 = LB To UB Step 2
adr(i7) = RT(var(i7), var(i7 + 1))
Next

COUNTIFS2003 = "=SUMPRODUCT(--(" & Join(adr, "),--(") & ")"
For i7 = LB To UB
COUNTIFS2003 = Replace(COUNTIFS2003, "--(),", "")
Next
COUNTIFS2003 = Replace(COUNTIFS2003, ",--()", ")")
COUNTIFS2003 = Application.Evaluate(COUNTIFS2003)
End Function

Function RT(vv, cc)
On Error Resume Next
RT = Application.Evaluate(cc)
If CStr(cc) = "" Then
RT = "" & vv.Address(External:=True) & "),--("
Else
If Not (IsError(RT)) Then
If IsEmpty(RT) Then
RT = vv.Address(External:=True) & "=""" & cc & """"
ElseIf IsNumeric(RT) Then
RT = vv.Address(External:=True) & "=" & cc
ElseIf IsDate(RT) Then
RT = vv.Address(External:=True) & "=" & cc
ElseIf TypeName(Range(cc)) = "Range" Then
If IsNumeric(RT) Then
RT = vv.Address(External:=True) & "=" & cc
Else
RT = vv.Address(External:=True) & "=""" & cc & """"
End If
Else
If IsNumeric(RT) Then
RT = vv.Address(External:=True) & RT
ElseIf IsDate(RT) Then
RT = vv.Address(External:=True) & RT
Else
RT = vv.Address(External:=True) & "=""" & RT & """"
End If
End If
Else
Select Case RT
Case CVErr(xlErrDiv0): GoTo ers
Case CVErr(xlErrNA): GoTo ers
Case CVErr(xlErrName): GoTo ers
Case CVErr(xlErrNull): GoTo ers
Case CVErr(xlErrNum): GoTo ers
Case CVErr(xlErrRef): GoTo ers
Case CVErr(xlErrValue): GoTo ers
End Select
End If
End If
Exit Function
ers:
If Err.Number = 13 Then
RT = "--(" & vv.Address(External:=True) & "),"""" & cc & """""
Else
If IsNumeric(cc) Then
RT = vv.Address(External:=True) & "=" & cc
Else
RT = vv.Address(External:=True) & GetRT(cc)
End If
End If
End Function

Function GetRT(RTstr)
Dim RTtemp1 As String
Dim RTtemp2 As String
Dim RTLeft As String
RTLeft = Left(RTstr, 1)
RTtemp1 = Mid(RTstr, 2)
RTtemp2 = Mid(RTtemp1, 2)
If IsNumeric(RTtemp1) Then
ElseIf IsDate(RTtemp1) Then
Else
RTtemp1 = """" & RTtemp1 & """"
End If

If IsNumeric(RTtemp2) Then
ElseIf IsDate(RTtemp2) Then
Else
RTtemp2 = """" & RTtemp2 & """"
End If

Select Case Left(RTstr, 2)
Case ">=": GetRT = ">=" & RTtemp2
Case "=>": GetRT = ">=" & RTtemp2
Case "<=": GetRT = "<=" & RTtemp2
Case "=<": GetRT = "<=" & RTtemp2
Case "<>": GetRT = "<>" & RTtemp2
Case Else
Select Case RTLeft
Case "=": GetRT = "=" & RTtemp1
Case ">": GetRT = ">" & RTtemp1
Case "<": GetRT = "<" & RTtemp1
Case Else
If IsNumeric(RTstr) Then
GetRT = "=" & RTstr
Else
GetRT = "=""" & RTstr & """"
End If
End Select
End Select
End Function
잘 안될 경우 메일 주시거나 리플 남겨주세요

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

숫자를 영어로 나타내는 사용자 함수 (NUM2DOLLAR) (0)	2008.09.09
여러 영역의 문자열 합치는 사용자 함수 (STRJOIN) (0)	2008.09.07
엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003 (1)	2008.09.05
엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28
오즈맨의 Sheet to Web 1.9 Up (0)	2008.08.19

Posted by 오즈맨스머프

엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003

엑셀보조파일,기능,함수 2008. 9. 4. 14:33

[ 2007 의 IFERROR 를 대신하는 IFERROR2003 ] 에 대해 알아봅니다.
=IF(수식,오류일때,수식) 을 =IFERROR2003(수식,오류일때) 로 사용 가능합니다.

1

2

3

4

A	B	C	D	E	F
성명	연락처		찾기	결과
길동이	101		길동이	101	101

삼순이	304		만수	없음	없음
만식이	502		삼순이	304	304

Sheet3 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	E2	=IF(ISERROR(VLOOKUP(D2,$A$2:$B$4,2,0)),"없음",VLOOKUP(D2,$A$2:$B$4,2,0))	101
2		E2 셀의 수식을 여기에 복사하세요 -→ E2:E4
3	F2	=IFERROR2003(VLOOKUP(D2,$A$2:$B$4,2,0),"없음")	101
4		F2 셀의 수식을 여기에 복사하세요 -→ F2:F4
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다.

Function IFERROR2003(WriteFormula, WriteAnswer)
Dim Answ As Variant
On Error Resume Next
Answ = Application.Evaluate(WriteFormula)
If Not (IsError(Answ)) Then
     IFERROR2003 = Answ
Else
Select Case Answ
     Case CVErr(xlErrDiv0):   IFERROR2003 = WriteAnswer
     Case CVErr(xlErrNA):     IFERROR2003 = WriteAnswer
     Case CVErr(xlErrName):   IFERROR2003 = WriteAnswer
     Case CVErr(xlErrNull):   IFERROR2003 = WriteAnswer
     Case CVErr(xlErrNum):    IFERROR2003 = WriteAnswer
     Case CVErr(xlErrRef):    IFERROR2003 = WriteAnswer
     Case CVErr(xlErrValue):  IFERROR2003 = WriteAnswer
     Case Else:               IFERROR2003 = Answ
End Select
End If
End Function
잘 안될 경우 메일 주시거나 리플 남겨주세요

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

여러 영역의 문자열 합치는 사용자 함수 (STRJOIN) (0)	2008.09.07
엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003 (1)	2008.09.05
엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003 (0)	2008.09.05
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28
오즈맨의 Sheet to Web 1.9 Up (0)	2008.08.19
엑셀 2007 2003 동시에 깔려있을때 xls 지정하기 (0)	2008.07.13

Posted by 오즈맨스머프

배열수식, SUMPRODUCT 의 속도를 올리자

엑셀보조파일,기능,함수 2008. 9. 3. 16:47

안녕하세요. 오즈맨 입니다.
배열수식의 내용을 알아봅니다. 시트명은 Korean 입니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
19994
19995
19996
19997
19998
19999
20000

A	B	C	D	E	F	G	H	I	J	K	L	M
1989-08/17			3955	형식1=SUMPRODUCT(--(조건))
1995-06/10			16045	형식1=SUMPRODUCT(--(조건))
1973-04/27			3955	형식2=SUMPRODUCT((조건)*1)
1963-06/13			16045	형식2=SUMPRODUCT((조건)*1)
1982-10/14			3955	형식3 배열수식=SUM(IF(조건,1,""))}
1967-01/07			16045	형식3 배열수식=SUM(IF(조건,1,""))}
1975-05/12		각 형식 별 소요시간 계산 (총 10 회) 단위 초/ %										평균
1954-01/16	형식 1	2.047	2.031	2.063	2.063	2.063	2.094	2.063	2.063	2.047	2.031	2.056
1957-01/22
1955-02/19	형식 2	2.078	2.047	2.078	2.078	2.109	2.094	2.094	2.109	2.125	2.063	2.088
1975-03/13	1과의 차이	0.031	0.016	0.016	0.016	0.047	0.000	0.031	0.047	0.078	0.031	0.031
1985-05/03	비율	1.53%	0.77%	0.76%	0.76%	2.27%	0.00%	1.52%	2.27%	3.82%	1.54%	1.52%
1956-02/24
1997-08/05	형식 3	2.172	2.219	2.203	2.188	2.250	2.203	2.172	2.172	2.203	2.203	2.198
1989-08/17	1과의 차이	0.125	0.188	0.141	0.125	0.188	0.109	0.109	0.109	0.156	0.172	0.142
1950-10/02	비율	6.11%	9.23%	6.82%	6.06%	9.09%	5.22%	5.30%	5.30%	7.63%	8.46%	6.92%
1954-06/07
1992-05/25
1958-03/01	준비한 자료 1949.10.30~2007.4.27 까지의 불규칙한 날짜 20,000 개 경과기간이 50년 이하, 초과 를 구분해서 개수를 구함 부득이하게 하나의 조건으로 sumproduct 혹은 배열수식 sum(if( 를 사용할 경우 자료입니다
2005-02/07
1990-09/04
1972-09/25
1951-03/20
1957-10/06

Korean 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 분홍색 수식이 있으면 수식 입력에 주의하세요 !!!배열수식이에요. 수식만 입력하고 Ctrl + Shift + Enter 하면 {} 는 자동으로 생깁니다. $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	C11	=C10-C$8	0.031
2		C11 셀의 수식을 여기에 복사하세요 -→ C11:L11,C15:L15
3	C12	=C11/C$8	1.53%
4		C12 셀의 수식을 여기에 복사하세요 -→ C12:L12,C16:L16
5	D1	=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),"y")>50))	3955
6	D2	=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),"y")< =50))	16045
7	D3	=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),"y")>50)*1)	3955
8	D4	=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),"y")< =50)*1)	16045
9	D5	=SUM(IF(DATEDIF(A1:A20000,TODAY(),"y")>50,1,""))	3955
10	D6	=SUM(IF(DATEDIF(A1:A20000,TODAY(),"y")< =50,1,""))	16045
11	M8	=AVERAGE(C8:L8)	2.056
12		M8 셀의 수식을 여기에 복사하세요 -→ M8,M10:M12,M14:M16
		sumproduct 앞의 -- 는 뺄셈이 아니라 단순히 ((부호변경))을 함으로써 논리값(True/False)을 1/0 으로 변경합니다. 뺄셈이 아니므로 소요시간이 덜 걸리는 잇점이 있습니다.
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 분홍색 수식이 있으면 수식 입력에 주의하세요 !!! ※ 만일 수식에 사용된 공백때문에 오류(다른 값)가 나오면 공백을 확인바랍니다.

-- 사용한 코드입니다 -- Option Explicit
Sub x()
Dim t(1 To 6) As Double, i As Double
Dim Answ(1 To 9, 1 To 1)
Dim rng As Range
Sheet1.Range("o1") = "."
Sheet1.Range("b1:b2").ClearContents
Sheet2.Range("o1") = "."
Sheet2.Range("b1:b2").ClearContents
Range("b1").Formula = "=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),""y"")>50))"
Range("b2").Formula = "=SUMPRODUCT(--(DATEDIF(A1:A20000,TODAY(),""y"")<=50))"
t(1) = Timer
For i = 1 To 10
Application.Calculate
Next
t(2) = Timer
Range("b1").Formula = "=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),""y"")>50)*1)"
Range("b2").Formula = "=SUMPRODUCT((DATEDIF(A1:A20000,TODAY(),""y"")<=50)*1)"
t(3) = Timer
For i = 1 To 10
Application.Calculate
Next
t(4) = Timer
Range("b1").FormulaArray = "=SUM(IF(DATEDIF(A1:A20000,TODAY(),""y"")>50,1,""""))"
Range("b2").FormulaArray = "=SUM(IF(DATEDIF(A1:A20000,TODAY(),""y"")<=50,1,""""))"
t(5) = Timer
For i = 1 To 10
Application.Calculate
Next
t(6) = Timer
Answ(1, 1) = (t(2) - t(1)) / 24 / 60 / 60 '첫결과
Answ(3, 1) = (t(4) - t(3)) / 24 / 60 / 60 '결과2
Answ(5, 1) = (t(6) - t(5)) / 24 / 60 / 60 '결과3
Set rng = Range("iv1").End(xlToLeft).Offset(, 1)
rng.Resize(5, 1).NumberFormat = "[s].000"
rng.Resize(5, 1) = Answ
Erase t
Erase Answ
Set rng = Nothing
Range("b1:b2").ClearContents
Beep
End Sub

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2007 에 있는 SUMIFS 를 2003 에서 ... SUMIFS2003 (1)	2008.09.05
엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003 (0)	2008.09.05
엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28
오즈맨의 Sheet to Web 1.9 Up (0)	2008.08.19
엑셀 2007 2003 동시에 깔려있을때 xls 지정하기 (0)	2008.07.13
Office. 자동 고침 옵션에 한/영 자동 고침이 없으면 (0)	2008.07.11

Posted by 오즈맨스머프

오즈맨의 Sheet to Web 2.00 (Sheet2Web)

엑셀보조파일,기능,함수 2008. 8. 28. 23:56

시트의 내용이나 시트에 있는 수식을 설명할 때 유용한 도구입니다.
불필요하게 예제파일을 만들지 않아도 됩니다.
Sheet2Web (시트웹)은 아래와 같이 사용됩니다.

다운로드 할 파일입니다. 둘 중 하나를 받으세요 (2008.08.31 파일 변경)	추가기능 xla 파일	Zip 압축파일
다운로드 할 파일입니다. 둘 중 하나를 받으세요 (2008.08.31 파일 변경)	invalid-file	invalid-file

이 기능을 사용하시려면 파일을 다운로드 받으신 다음
아래의 폴더 중 한곳에 복사해서 사용하세요
C:\Program Files\Microsoft Office\Office\XLStart
C:\Program Files\Microsoft Office\Office\Library

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2007 에 있는 COUNTIFS 를 2003 에서 ... COUNTIFS2003 (0)	2008.09.05
엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 1.9 Up (0)	2008.08.19
엑셀 2007 2003 동시에 깔려있을때 xls 지정하기 (0)	2008.07.13
Office. 자동 고침 옵션에 한/영 자동 고침이 없으면 (0)	2008.07.11
Visual Basic Editor 용 자동 스크롤 도구 FreeWare (0)	2008.04.23

Posted by 오즈맨스머프

오즈맨의 Sheet to Web 1.9 Up

엑셀보조파일,기능,함수 2008. 8. 19. 15:48

시트의 내용이나 시트에 있는 수식을 설명할 때 유용한 도구입니다.
불필요하게 예제파일을 만들지 않아도 됩니다.

다운로드 할 파일입니다.

invalid-file

이 기능을 사용하시려면 파일을 다운로드 받으신 다음
아래의 폴더 중 한곳에 복사해서 사용하세요
C:\Program Files\Microsoft Office\Office\XLStart
C:\Program Files\Microsoft Office\Office\Library

첨부파일을 다운로드하시면 됩니다

아래 내용은 사용하는 예제 몇가지 입니다.

여기로 업뎃 되었습니다.

이 기능을 사용하시려면 파일을 다운로드 받으신 다음
아래의 폴더 중 한곳에 복사해서 사용하세요
C:\Program Files\Microsoft Office\Office\XLStart
C:\Program Files\Microsoft Office\Office\Library

불필요한 vbe 창이 열림으로 인해 코드를 잠궜습니다.

여기로 업뎃 되었습니다.

이상이 있거나 추가할 내용이 있으면 연락주세요

다운로드 할 파일입니다.

invalid-file

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28
엑셀 2007 2003 동시에 깔려있을때 xls 지정하기 (0)	2008.07.13
Office. 자동 고침 옵션에 한/영 자동 고침이 없으면 (0)	2008.07.11
Visual Basic Editor 용 자동 스크롤 도구 FreeWare (0)	2008.04.23
VB 런타임 파일 vbruntimes70 FreeWare (0)	2008.04.22

Posted by 오즈맨스머프

엑셀 2007 2003 동시에 깔려있을때 xls 지정하기

엑셀보조파일,기능,함수 2008. 7. 13. 23:12

탐색기에서 폴더옵션을 변경하기 위해 아래와 같이 메뉴를 선택합니다.

이 내용은 엑셀 2003, 2007 이 설치된 경우
XLS 파일을 더블클릭했을 때 2003 으로 지정하려는 목적입니다
XLSX 처럼 2007 전용 파일을 더블클릭하면 2007 로 열립니다.

새로만들기, 열기, 인쇄, 읽기 전용으로 열기, printto 의 설정 중
DDE 사용 아래에 있는 메뉴의 내용을 변경합니다.



그림은 제 폴더옵션입니다.

	명령	새로만들기
새로 만들기	명령을 실행할 응용프로그램	"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e /n
	DDE 메시지	[new("%1")]
	응용프로그램	Excel
	동작않는DDE
	주제	system
	명령	열기
열기	명령을 실행할 응용프로그램	"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e
	DDE 메시지	[open("%1")]
	응용프로그램	Excel
	동작않는DDE
	주제	system
	명령	인쇄
인쇄	명령을 실행할 응용프로그램	"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e
	DDE 메시지	[open("%1")][print()][close()]
	응용프로그램	Excel
	동작않는DDE	[open("%1")][print()][quit()]
	주제	system
	명령	읽기 전용으로 열기
읽기 전용 으로 열기	명령을 실행할 응용프로그램	"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /h /e
	DDE 메시지	[open("%1",,,,,,,,,,,,,,1,,1)]
	응용프로그램	Excel
	동작않는DDE
	주제	system
	명령	printto
printto	명령을 실행할 응용프로그램	"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e
	DDE 메시지	[open("%1")][print(1,,,,,,,,,,,2,"%2")][close()]
	응용프로그램	Excel
	동작않는DDE	[open("%1")][print(1,,,,,,,,,,,2,"%2")][quit()]
	주제	system

위 그림대로 해도 안되는 경우에는 엑셀을 종료한 다음
엑셀 2003 CD 를 넣고 아래폴더에 있는 파일을 지우고
C:\Program Files\Microsoft Office\Library\Analysis\
엑셀을 실행하면 재설치가 자동으로 됩니다.

그것도 잘 안되면 엑셀 2003 CD 를 넣고

엑셀 2003 의 추가기능 재설치 과정 만을 진행 해보십시오.
(참고로 폴더명은 다를 수 있습니다)

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28
오즈맨의 Sheet to Web 1.9 Up (0)	2008.08.19
Office. 자동 고침 옵션에 한/영 자동 고침이 없으면 (0)	2008.07.11
Visual Basic Editor 용 자동 스크롤 도구 FreeWare (0)	2008.04.23
VB 런타임 파일 vbruntimes70 FreeWare (0)	2008.04.22

Posted by 오즈맨스머프

Office. 자동 고침 옵션에 한/영 자동 고침이 없으면

엑셀보조파일,기능,함수 2008. 7. 11. 23:43

오피스 설치도중 이상이 있어서 한영 표시가 올바르지 않을수 있습니다.
오피스(엑셀 등) 자동 고침 옵션 중 한/영 자동 고침이 없으면

먼저 오피스(엑셀등)를 제거합니다.

invalid-file

첨부파일 다운로드

저작자표시 비영리 변경금지

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28
오즈맨의 Sheet to Web 1.9 Up (0)	2008.08.19
엑셀 2007 2003 동시에 깔려있을때 xls 지정하기 (0)	2008.07.13
Visual Basic Editor 용 자동 스크롤 도구 FreeWare (0)	2008.04.23
VB 런타임 파일 vbruntimes70 FreeWare (0)	2008.04.22

Posted by 오즈맨스머프

Visual Basic Editor 용 자동 스크롤 도구 FreeWare

엑셀보조파일,기능,함수 2008. 4. 23. 23:21

Visual Basic Editor 를 사용하면 코드가 길 경우 스크롤 되지않아 불편한 경우가 있습니다.
엑셀 2007에서는 자동으로 되더군요
2003 이하 버전에서는 매우 유용한 도구입니다.

마우스 휠을 이용해 텍스트창을 위아래로 이동시킬수 있습니다.

SpeedVB 5 입니다.

많은 사용자분들의 의견을 수렴하여 몇가지 기능추가 및 개선이 이루어졌습니다.

현재
* Microsoft Visual Basic 코드창, 직접 실행창(디버그 출력 창) 등에서 마우스 휠 스크롤 지원
* Microsoft VBA (Microsoft Office Visual Basic Editor에 포함된) 코드창,
* Microsoft VBA 직접 실행창(디버그 출력 창) 등에서 마우스 휠 스크롤 지원
* 프로그램 시작시 메모리 할당률 줄임
* 엔진 DLL과 본체 EXE 파일의 사이즈 최소화 (0.4버전에 비해 약 52% 경량화)
* 마우스 휠 스크롤을 지원하지 않는 윈도우를 사용자가 직접 선택하여 스크롤 기능을 지원하게 할 수 있음
* SpeedVB 5 설치 프로그램 지원

--- 주의 ---
이 프로그램은 Windows 98 이상 , Windows NT 4.0 SP4 이상의 운영체제에서 동작합니다.
또한 Windows 98, Windows XP, Windows Server 2003에서 기능 테스트가 완료되었으며
주로 마이크로소프트 마우스 드라이버나 마우스 동작을 판단할 수 없게 하는 기타 소프트웨어가 설치된 컴퓨터에선 동작하지 않을 수 있습니다.

invalid-file

다운로드 하세요

파일을 더블클릭하거나
마우스 오른쪽을 클릭해서 설치 를 선택하십시오.

출처 : http://cena.us/zboard/zboard.php?id=pds&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=21

'엑셀보조파일,기능,함수' 카테고리의 다른 글

엑셀 2007 에 있는 IFERROR 를 2003 에서 ... IFERROR2003 (1)	2008.09.04
배열수식, SUMPRODUCT 의 속도를 올리자 (0)	2008.09.03
오즈맨의 Sheet to Web 2.00 (Sheet2Web) (0)	2008.08.28
오즈맨의 Sheet to Web 1.9 Up (0)	2008.08.19
엑셀 2007 2003 동시에 깔려있을때 xls 지정하기 (0)	2008.07.13
Office. 자동 고침 옵션에 한/영 자동 고침이 없으면 (0)	2008.07.11
VB 런타임 파일 vbruntimes70 FreeWare (0)	2008.04.22

Posted by 오즈맨스머프

십진수 시트 에 사용한 수식 입니다. by MicroSoft Excel v 2003 $ 가 있는 수식은 절대(혼합)참조로 셀 주소를 고정합니다. 참조하세요!!
No	셀주소	왼쪽의 셀에 수식을 넣으면 오른쪽 결과가 나옵니다. (복사)를 누르면 셀의 수식이 클립보드(메모리)로 복사되는데, 익스플로러의 종류에 따라 (복사)가 작동이 안 될 수 있습니다.	결과	수식을
1	B3	=DEC2BIN(A3)	1
2		B3 셀의 수식을 여기에 복사하세요 -→ B3:B7,B16:B20
3	C3	=Dec_Bin(A3)	00000001
4		C3 셀의 수식을 여기에 복사하세요 -→ C3:C7,C9:C14,C16:C20
5	E3	= LEN(C3)	8
6		E3 셀의 수식을 여기에 복사하세요 -→ E3:E7,E9:E14,E16:E20
7	B9	=DEC2BIN(A9,8)	00000001
8		B9 셀의 수식을 여기에 복사하세요 -→ B9:B14
9	D9	=IF(B9=C9,"같음","다름")	같음
10		D9 셀의 수식을 여기에 복사하세요 -→ D9:D14,D16:D20
보시는 내용은 위의 표에 값이, 아래의 표에는 해당 셀의 수식이 있습니다. 첨부파일이 없습니다, 원하는 부분을 시트(셀)에 붙여 넣으세요. 도움이 되시기를 바랍니다.
엑셀 프로그램에서는 2진수 기준 8자리 10진수 기준 511 까지 사용 가능합니다. (도움말 참조하세요 !!! )

'엑셀보조파일,기능,함수'에 해당되는 글 31건

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

'엑셀보조파일,기능,함수' 카테고리의 다른 글

카테고리

달력

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

태그목록

링크

티스토리툴바