20150831131806

엑셀(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 • 무른모 • 0 • Tags: , , ,

20150826164950

엑셀(EXCEL) – 수식으로 된 셀에서 숫자만 각 셀로 분리하여 처리하기

 

언젠가 다른 사업부의 생산 부서에서 일하는 사원이 생산 일보를 엑셀에서 작성하는데
제품 아이템마다 F2를 눌러 =25+27+24 … 이런 식으로 입력하는 것을 보고 아이디어를
생각하다가 그만 둔 프로그램이 있었는데 아래의 질문과 같이 비슷한 내용이 있어서
이번에는 시간적 여유가 있고 해서 간단한 팁을 만들어 보았습니다.

http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3501134
(엑셀에서 셀 입력내용을 가져올수있을까요?)

간단히 해석하면 셀에 지속적으로 +, – 등으로 추가된 데이터를 분리해 달라는 내용입니다.

저번에 해결하려는 내용을 거꾸로 해석해서 두가지 버전으로 작성해 보았습니다. 우선
해당 셀이 공식(즉 산술식으로 구성되어 있는지 확인)하는 루틴을 작성해서 그 셀 타입이
‘Formulra’일 때만 데이터를 분리 처리하도록 함수를 만들어 인자를 넘깁니다.

이제 버튼 하나 만드시고 만든 버튼에 각각의 매크로 연결하시면 사용 가능하십니다.

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-셀에서 숫자만 분리 추출

By • 무른모 • 0 • Tags: , , ,

20150825154133

엑셀(EXCEL) – 일자가 기간내 포함된 것인지 확인하여 예약 상황 확인

이제 지금 지나가는 태풍이 끝나고 나면 가을걷이가 한창일 것입니다.
더운 여름 잠시 우리들은 일상을 떠나 휴가를 가서 지친 심신을 달래는데
휴가지의 민박집이나 펜션들은 손님 맞이에 정신이 없었을 것입니다.

휴가지에 숙박을 예약하려면 담당 직원들이 그 숙박지의 예약 상태를 알아야
적절히 룸을 운영하여 예약 잘못으로 인해 모처럼의 휴가를 망치지 않을 것입니다.
어떤 분이 홈페이지를 운영하기 전에 임시로 엑셀로 예약상황을 관리하시겠다고
질문을 올려주셨는데 그 해답?을 간단한 팁으로 올립니다.

http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3505287
(엑셀 날짜 작업 문의)

Option Explicit

Sub Count_Persons_In_Rooms()

Dim cnt As Integer
Dim Indate As Date
Dim RngCel As Range

Dim sht As Worksheet

‘ 검색된 자료를 출력할 위치를 구함
cnt = 1

‘ 검색된 자료를 출력할 시트를 생성, 있으면 경고없이 삭제
Application.DisplayAlerts = False

For Each sht In Worksheets
If sht.Name = “Persons_In_Rooms” Then
sht.Delete

End If
Next sht

‘ 검색된 자료를 출력하기 위한 시트 생성
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = “Persons_In_Rooms”

Set sht = Sheets(“Persons_In_Rooms”)

Sheets(“sheet1”).Select

‘ 같은 시트에 출력할 때 아래 코멘트를 지우고 사용하면 됨
‘ Range(“G2”, Range(“H2”).End(xlDown)).ClearContents

Indate = Cells(1, 6).Value

For Each RngCel In Range(“B2”, Range(“B65536”).End(xlUp))

If Indate >= RngCel.Value Then
‘ check out 날짜는 그날을 포함할지 안할지는 결정(<, <=)
If Indate <= RngCel.Offset(0, 1).Value Then

cnt = cnt + 1

‘ 새 시트에 자료 생성
sht.Range(“A1”).Value = “일자”
sht.Range(“B1”).Value = “호실”
sht.Range(“C1”).Value = “인원”

Range(“F1”).Copy sht.Range(“A” & cnt)
RngCel.Offset(0, 2).Copy sht.Range(“A” & cnt).Offset(0, 1)
RngCel.Offset(0, 3).Copy sht.Range(“A” & cnt).Offset(0, 2)

‘ 같은 시트에 자료 출력할 때는 위의 6줄에 코멘트를 지우고 아래 코드를 사용
‘ Range(“G” & cnt).Value = RngCel.Offset(0, 2).Value
‘ Range(“G” & cnt).Offset(0, 1).Value = RngCel.Offset(0, 3).Value

End If
End If

Next

Application.DisplayAlerts = True

Sheets(“Persons_In_Rooms”).Select

End Sub

그냥 간단하게 출력하는 루틴을 만들었는데 특정 영역을 지정하거나 동적이름을 정의해서
더 활용할 수 있음. 더 확장하면 특정일로 부터 체크아웃하는 날까지 일자별로 리스트를
만들어서 출력할 수도 있으나 간단한 팁으로 올리는 것이므로 응용해서 사용바랍니다.

첨부 화일 : 20150825-Persons_In_Rooms

By • 무른모 • 0 • Tags: , , , ,

«< 30 31 32 33 34 >»