8월 26 2015
엑셀(EXCEL) – 수식으로 된 셀에서 숫자만 각 셀로 분리하여 처리하기
언젠가 다른 사업부의 생산 부서에서 일하는 사원이 생산 일보를 엑셀에서 작성하는데
제품 아이템마다 F2를 눌러 =25+27+24 … 이런 식으로 입력하는 것을 보고 아이디어를
생각하다가 그만 둔 프로그램이 있었는데 아래의 질문과 같이 비슷한 내용이 있어서
이번에는 시간적 여유가 있고 해서 간단한 팁을 만들어 보았습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3501134
(엑셀에서 셀 입력내용을 가져올수있을까요?)
간단히 해석하면 셀에 지속적으로 +, – 등으로 추가된 데이터를 분리해 달라는 내용입니다.
저번에 해결하려는 내용을 거꾸로 해석해서 두가지 버전으로 작성해 보았습니다. 우선
해당 셀이 공식(즉 산술식으로 구성되어 있는지 확인)하는 루틴을 작성해서 그 셀 타입이
‘Formulra’일 때만 데이터를 분리 처리하도록 함수를 만들어 인자를 넘깁니다.
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 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
Option Explicit Function ShowFormulas(Check As Range) Dim TempStr As String ' 셀에 공식?이 있을 때만 처리하도록 코멘트 처리 Select Case True ' Case Check.HasArray ' TempStr = "{" & Check.Array & "}" Case Check.HasFormula TempStr = Check.Formula ' Case Else ' TempStr = Check.Value End Select ShowFormulas = TempStr End Function ' 특정 셀의 공식을 숫자로 분리하는 루틴 Sub Num_Ext_In_Cell(TargetStr As String) Dim TempStr As String Dim intNum As Integer Dim NumCnt As Integer NumCnt = 0 TempStr = "" For intNum = 1 To Len(TargetStr) If IsNumeric(Mid(TargetStr, intNum, 1)) Or Mid(TargetStr, intNum, 1) = "." Then TempStr = TempStr + Mid(TargetStr, intNum, 1) ActiveCell.Offset(0, NumCnt).Value = TempStr Else NumCnt = NumCnt + 1 TempStr = "" End If Next intNum End Sub ' 특정 영역을 선택해서 공식을 숫자로 분리하는 루틴 Sub Num_Ext_In_Range() Dim TempStr As String Dim RngStr As String Dim intNum As Integer Dim NumCnt As Integer Dim RowCnt As Integer Dim RngCel As Range Dim RngRef As Range NumCnt = 0 TempStr = "" ' 검색할 셀의 조건을 셀을 클릭해서 선택 Set RngRef = Application.InputBox("셀 선택", , Type:=8) ' 검색할 영역에서 선택된 셀과 같은 조건이 있으면 카운트 For Each RngCel In RngRef RngStr = ShowFormulas(RngCel) For intNum = 1 To Len(RngStr) If IsNumeric(Mid(RngStr, intNum, 1)) Or Mid(RngStr, intNum, 1) = "." Then TempStr = TempStr + Mid(RngStr, intNum, 1) RngCel.Offset(0, NumCnt).Value = TempStr Else ' 열의 위치 수정 NumCnt = NumCnt + 1 ' 임시 문자열 초기화 TempStr = "" End If Next intNum ' 열의 위치 리셋 NumCnt = 0 Next End Sub |
이제 버튼 하나 만드시고 만든 버튼에 각각의 매크로 연결하시면 사용 가능하십니다.
ps> 어제 잠깐 고민해 보니 해결 방법이 보여서 진행해 보니 실수부분도 처리가능하네요.
If IsNumeric(Mid(TargetStr, intNum, 1)) Or Mid(TargetStr, intNum, 1) = “.” Then
TempStr = TempStr + Mid(TargetStr, intNum, 1)
ActiveCell.Offset(0, NumCnt).Value = TempStr
위과 같이 바꾸면 실수도 처리가 가능합니다. 추가로 팁을 올리기는 뭐해서 추가합니다.
영역 선택 부분도 조건식 추가하고 정수 처리하는 부분 제거하시면 됩니다.
첨부 자료 : 20150826-셀에서 숫자만 분리 추출
8월 31 2015
엑셀(EXCEL) – 행별 중복 유형별 해당 숫자 추출
목감기에 걸려 코가 간질간질한데 재채기가 나올려고만 하고 정작 나오지 않는 것처럼
아래 질문은 이해가 되긴 되는데 뭔가 부족하여 전체를 수작업 비슷하게 해야될 것 같아
우선 해결 논리를 세울려고 고민에 고민을 거듭하였습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3517736
(엑셀 함수 질문드립니다.(매칭관련)
질문의 내용을 보면 어떤 형식은 있는데 정규화 시킬 방법을 찾기가 너무 힘들었습니다.
댓글의 내용을 보면 어느 정도 정규화 시킬 수 있을 것 같은데 코는 간질 간질 하는데
재채기가 나올 생각을 하지 않는 것처럼 마음은 답답한데 실마리는 보이지 않았습니다.
<댓글>
달린 순서는 상관없습니다.
——
OXXX
OOXX
OXOX
OXXO
OOOX
OOXO
OXOO
OOOO
XOXX
XOOX
XOXO
XOOO
XXOX
XXOO
XXXO
(일단 복사하고 – 엑셀상에서 다시 선택해서 복사
선택하여붙여넣기-행열바꿔서 식으로 하면 됩니다.)
…
</댓글>
. 고민 중
. 고민 중
. 고민 중
!!!
위 코드를 정렬시켜 봅니다. 그리고 행렬 바꿔 복사해서 보면 이렇게 됩니다.
(한 행에 걸쳐 있는데 이 본문을 보기좋게 하기위하여 줄바꿈했습니다.)
XXXO XXOX XXOO XOXX XOXO XOOX XOOO
OXXX OXXO OXOX OXOO OOXX OOXO OOOX OOOO
뭔가 규칙 아닌 규칙이 보입니다. 잘 보세요. 서로 다른 숫자를 X라 하고 같은 숫자를 O라 할 때
이 X를 0(Zero)로 O를 1로 치환을 해 봅시다. 그러면 아래와 같은 정렬로 됩니다. 뭔가 보이나요?
0001 0010 0011 0100 0101 0110 0111
1000 1001 1010 1011 1100 1101 1110 1111
일단은 어떻게든 숫자로 처리할 수 있을 것 같습니다. 그렇습니다. 컴퓨터는 좋아하고 우리들은
싫어하는? 이진수가 보이네요. 이제 우리의 아이디어가 들어갈 순서입니다. 이진수를 십진수로
바꾸어 주는 함수입니다. (제가 만든 것은 아니고 구걸했습니다. ^^;;;)
Function BinToDec(Bin As String) As Long
Dim i As Integer
For i = 1 To Len(Bin)
BinToDec = BinToDec * 2 + CInt(Mid(Bin, i, 1))
Next
End Function
이제 이 함수의 인자로 저 규칙을 전달하면 아래와 같은 정수를 얻습니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
이제 조건식을 만들고 그 위치에 찾아진 값들을 특정 셀에 뿌려주면 됩니다. 사실 코드를 생각하는
시간이 무지 많이 들었고 그 조건식을 비교하는 루틴에서 아래 설명에 있겠지만 조건을 비교하는
순서를 잘못해서 기준값이라는 개념없이 접근해서 오류가 많이 났습니다.
Option Explicit
Sub Kind_Count()
Dim rcnt As Integer
‘ 순환, 참조할 영역
Dim Cel As Range
Dim RefCel As Range
Dim RngCel As Range
Dim RngRef As Range
‘ 찾을 값들의 최초 참조 영역
Set RngCel = Range(“A4”, Range(“A4”).End(xlDown))
‘ 참조할 값
Set RngRef = Range(“E1:S1”)
‘ 속도를 위해 업데이트 하지 않고 계산
Application.ScreenUpdating = False
‘ 전체 행의 수를 확인하여 자동화 가능
rcnt = Range(“A4”).End(xlDown)
Range(“E4:S” & rcnt).ClearContents
‘ 참조 영역 순환
For Each Cel In RngCel
‘ 참조할 값 순환
For Each RefCel In RngRef
‘ 몇 번의 시행착오를 거치면서 참조할 위치를 선정하는 것이 중요함
‘ 즉 참조할 “1” 혹은 “O”의 위치를 기준으로 같다, 다르다를 설정해야 에러가 나지 않음
‘ 이 구문에서 무지 헤메임
Select Case RefCel.Value
‘ 1과 같다는 것은 4번째 행의 숫자가 전체와 다 다르다는 것을 의미함
‘ 즉 “1”과 “O”의 위치임
Case 1
If Cel.Offset(0, 3).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 3).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 3).Value
End If
Case 2
If Cel.Offset(0, 2).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 2).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 2).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 2).Value
End If
Case 3
If Cel.Offset(0, 2).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 3).Value = Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 2).Value
End If
Case 4
If Cel.Offset(0, 1).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 1).Value <>
Cel.Offset(0, 2).Value And Cel.Offset(0, 1).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 5
If Cel.Offset(0, 1).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 3).Value <>
Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 6
If Cel.Offset(0, 1).Value = Cel.Offset(0, 2).Value And Cel.Offset(0, 1).Value <>
Cel.Offset(0, 0).Value And Cel.Offset(0, 1).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 7
If Cel.Offset(0, 1).Value <> Cel.Offset(0, 0).Value And Cel.Offset(0, 1).Value =
Cel.Offset(0, 2).Value And Cel.Offset(0, 1).Value = Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 1).Value
End If
Case 8
If Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 9
If Cel.Offset(0, 0).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 10
If Cel.Offset(0, 0).Value = Cel.Offset(0, 2).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 11
If Cel.Offset(0, 0).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value =
Cel.Offset(0, 2).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 1).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 12
If Cel.Offset(0, 0).Value = Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value <>
Cel.Offset(0, 2).Value And Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 13
If Cel.Offset(0, 0).Value = Cel.Offset(0, 1).Value And Cel.Offset(0, 2).Value <>
Cel.Offset(0, 0).Value And Cel.Offset(0, 3).Value = Cel.Offset(0, 0).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
Case 14
If Cel.Offset(0, 0).Value <> Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value =
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value = Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
‘ …. “15”라는 숫자는 이진수로 하면 “1111”과 같으므로 전체 숫자가 동일하다는 것을
의미함
Case 15
If Cel.Offset(0, 0).Value = Cel.Offset(0, 3).Value And Cel.Offset(0, 0).Value =
Cel.Offset(0, 1).Value And Cel.Offset(0, 0).Value = Cel.Offset(0, 2).Value Then
Cel.Offset(0, 3 + RefCel.Value).Value = Cel.Offset(0, 0).Value
End If
End Select
Next
Next
‘ 업데이트 진행
Application.ScreenUpdating = True
End Sub
‘ 십진수를 이진수로 만드는 함수
Function DecToBin(Dec As Long) As String
Do
DecToBin = CStr(Dec Mod 2) & DecToBin
Dec = Dec \ 2
Loop Until Dec = 0
End Function
‘ 이진수를 십진수로 만드는 함수
Function BinToDec(Bin As String) As Long
Dim i As Integer
For i = 1 To Len(Bin)
BinToDec = BinToDec * 2 + CInt(Mid(Bin, i, 1))
Next
End Function
그리고 마지막 행에 중복 위치별로 숫자의 카운트를 하거나 해당 숫자의 갯수를
세거나 하는 등의 함수를 추가하여 가공이 가능합니다.
첨부 화일 : 20150831-행별 중복 유형별 해당 숫자 추출
By vinipapa • 무른모 • 0 • Tags: Excel, Tip, 엑셀, 행별 유형별 분리