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-행별 중복 유형별 해당 숫자 추출

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

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

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

몇 달전 엑셀 자료를 편집하면서 흰색인 기타 색으로 아래로 쭉 리스트 된 자료를 편하게
편집하고 볼 수 있도록 아래의 팁을 올린 계기로 업무에 도움이 될만한 팁들을 지속적으로
올리고 있습니다. 그런데 저는 불편함이 없이 여러 업무에 필요할 때마다 VBA를 사용하여
자료를 정리하고 있어 팁을 올릴 계기가 없었습니다.

엑셀(EXCEL) – 자료보기 편하게 번갈아 가면서 행 배경색 자동으로 넣기
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=265108

오늘 클리앙 메모로 어떤 분이 위 팁을 사용하고 계시는데 조금 더 응용할 방법을 찾으시다가
본인이 해결하기 힘드시다고 방법을 요청해서 조금 변형해서 올립니다.

“안녕하세요. 엑셀팁 유용하게 쓰고 있습니다. 감사해요.
혹시 5개 단위로 색을 달리하는 조건부서식용 수식이 있을까요?
1-5행은 흰색, 6-10행은 녹색, 다시 11-15행은 흰색… 이런식으로요
많이 고민해 봤는데 좋은 방법을 모르겠기에 도움을 청합니다.”

=IF(MOD(ROW(A1),2)=0,1,0)
위 팁의 간단한 조건부 서식의 내용입니다. 현재 행을 2로 나누어 0과 같으면 조건부 서식대로
색상, 글꼴 등을 변형시키고 1이면 그냥 셀을 흰색으로 보여줍니다.

이제 조금 변형을 해 볼까요?
=QUOTIENT(ROW(A2)) 현재 행의 몫의 정수 부분을 보여줍니다. D2행에서 보이는 것처럼
아래로 수식을 복사하면 각 행을 5로 나눈 몫의 정수 부분을 보여줍니다. 첨부의 그림처럼
규칙이 일정하지를 않습니다.

조금 더 변형을 해 볼까요?
=QUOTIENT(ROW(E2)-2,5)
현재 행에서 2를 빼고 5로 나눈 몫의 정수 부분을 보여줍니다. 이제 일정한 규칙이 보입니다.

=MOD(QUOTIENT(ROW(A2)-2,5),2)
제일 최초의 팁에서 응용한 수식을 입력하고 필요한 부분에 수식 복사를 합니다.

간단하게 그냥 =MOD(QUOTIENT(ROW(A2),5),2)를 하면 번갈아서 색을 조건부 수식에 따라
보여주게 되는데 대부분의 엑셀을 사용하는 사용자라면 제목을 사용하고 데이터를 아래로 쭉
나열해서 자료를 정리하므로 제목행에 대해 예외 처리를 하기 위해서 입니다.

잘 사용하셨으면 합니다. 언제나 소스?는 여기 올리고 엑셀 자료는 블로그에 첨부해 둡니다.

첨부 : 20150708-일정한 간격의 행을 번갈아서 칠하기