파란바람 이야기

안녕하세요. 데이터 파일 없이 글로 올려진 질문에 답을 할 때는 고급 필터에 COUNTIF 함수로 조건을 설정하면 되는 작업으로 보였었습니다. 막상 메일로 받은 데이터 파일을 보니 생각했던 것과는 다른 모습이었습니다. 6시 근무자와 14시 근무자를 D열부터 R열에 적었고, S열부터 근무하지 않은 명단을 나타내는 것입니다. 전체 명단은 AA열과 AB열에 기록되어 있습니다. 한참 생각해 봤지만 함수나 엑셀 기능으로 나타낼 방법은 없어 보입니다. 방법은 VBA로 작성하는 것입니다. 사용자 지정 함수로 만들고 싶었지만 결과값이 한 셀에만 나타나는 것이 아니라 여러 셀에 나타나는 것이라 프로시저로 처리했습니다. 커서는 결과를 나타낼 셀 [S7]에 둡니다. 매크로를 실행하야 하는데, [보기]-[매크로]-[매크로 보..

안녕하세요. VLOOKUP 함수 마지막 인수 Range_lookup과 MATCH 함수 마지막 인수 Match_type은 찾는 방법을 정하는 인수 입니다. Range_lookup 인수는 0, 1의 두 가지 값 중 하나를 입력하고, Match_type 인수는 1, 0, -1의 세가지 값 중 하나를 입력합니다. 0은 두 인수 모두 정확하게 찾기를 선택하는 값이고, 나머지는 비슷하게 찾기를 선택하는 값입니다. 확인된 내용은 아니지만, VLOOKUP 함수는 INDEX 함수와 MATCH 함수를 이용해 찾기/참조 수식을 작성하다 자주 사용되는 경우에 사용할 수 있도록 나중에 개발된 함수인 것으로 생각됩니다. 그래서 MATCH 함수 인수보다 VLOOKUP 함수 인수는 간단하게 만들어져 있습니다. MATCH 함수 마지막..

안녕하세요. 평균을 구할 범위와 기간을 정하면 범위로 선택한 데이터 중 최근 데이터 순서로 지정한 기간만큼 평균을 구하도록 사용자 지정 함수를 만들어 보겠습니다. 선택한 범위 모든 셀에 값이 들어 있는 것이 아니기 때문에 값이 들어 있는 셀만 대상으로 작업을 해야 합니다. VBE를 실행해 코드를 작성합니다. Public Function 주어진범위평균(범위 As Range, 개월 As Integer) Dim 배열(100) Dim 합 As Double i = 1 For Each 셀 In 범위 If 셀 "" Then 배열(i) = 셀 i = i + 1 End If Next 셀 i = i - 1 If i < 개월 Then 주어진범위평균 = "값 부족" Else For j = i To i - 개월 + 1 Step..

데이터베이스 프로그램에서처럼 여러 개의 테이블로 나뉘어져 여러 시트에 나타낸 데이터를 피벗 테이블로 분석할 때 관계를 설정해 나타내는 방법을 소개합니다. 여러 시트를 통합한 피벗테이블 생성하기 feat.데이터모델링 - YA-Hwang 기술 블로그 Excel에서 여러 시트를 통합하는 데이터모델링에 대해 알아본다. yahwang.github.io 문제는 엑셀에 이렇게 시트를 나눠 입력할 정도라면 그냥 데이터베이스 프로그램을 이용해 구성하는 경우가 대부분이라는 것입니다.

질문을 받아 처리했습니다. 자주 받는 질문인데, 많은 내용을 알지 못해도 여러 상황에서 이용할 수 있는 방법입니다. 매일 매출과 입금 내역을 특정 셀 범위에 입력한 뒤 정해진 셀 위치로 복사하는 작업입니다. 매크로를 이용하면 쉽게 처리할 수 있습니다. 두가지 작업인데, 첫번째는 매출입력입니다. [W4] 셀에 작업일이 있고, 아래에 카드사별 매출을 입력한 뒤 매출입력 단추를 누르면 해당 날짜에 나타나는 것입니다. 두번째는 입금입력입니다. [C39:M46] 셀 범위에 입력된 데이터를 해당 날짜 해당 위치로 복사합니다. Public Sub 매출입력() '작업일 확인 작업일 = Day(Range("W4")) 행 = 작업일 + 5 ' 1일인 경우 6행에 입력 Range("C" & 행).Value = Range("..

일정한 기준으로 배정하는 방법을 질문 받았는데, 질문하며 참조했다는 파일을 열어보니 함수를 이용해 멋지게 구현해 놓은 것이었습니다. 성적순위별 전공 배정표.xlsx 파일 이름으로 검색해서 블로그를 찾았습니다. svetak.tistory.com/m/75 1지망부터 4지망까지 있고 성적 순서대로 정렬한 상태에서 아래 학과별 정원을 고려해 배정하는 것입니다. 사용되는 함수는 IF, COUNTIF, VLOOKUP 함수로 많이 사용되는 함수들입니다. 원하는 작업이 성적순이 아니라 지망순으로 배정하길 원하는 것입니다. 1지망을 우선 배정하고, 다음 2지망, 3지망순으로 배정하는거죠. 함수로 할 방법을 떠오르지 않고 VBA로, 그것도 반복문을 세번이나 중첩해 작성했습니다. Public Sub 배정() For i = ..

사용자 지정 서식에서 직접 색상 이름을 지정해서 색상을 나타낼 수 있는 색은 8가지입니다. 검정, 파랑, 녹청, 녹색, 자홍, 빨강, 노랑, 흰색 '빨강'이라는 글자가 적혀 있는 [B3] 셀에 셀 서식을 지정해 글자색을 빨강색으로 나타내겠습니다. 단축키 을 눌러 셀 서식 대화상자를 나타내고 [표시 형식] 탭 [사용자 지정] 항목에서 [형식]에 '[빨강]@'를 입력합니다. 색상을 나타낼 때 대괄호( [ ] )로 묶어 나타냅니다. [확인]을 눌러 보면 글자가 빨간색으로 나타납니다. 이 방법 외에 다른 방법이 있습니다. 색상 인덱스를 이용하는 방법인데 56가지 색을 나타냅니다. [B2] 셀에서 다시 단축키 을 눌러 셀 서식 대화상자를 나타내고, [형식]에 '[색45]@'를 입력합니다. [확인]을 눌러 보면 다..

며칠전에 연락한 후 따로 연락이 없어서 다 끝난건가 싶었는데, 오늘 아침 다시 연락이 왔습니다. 마무리 작업을 진행 중이군요. 한 가지 일을 처리하는데도 완전히 끝내려면 꽤나 많은 일이 필요하군요. 연락 온 용건은 반복지급된 내역 중 중복자의 경우 한 번만 나타내도록 색상을 지정하는 것이었습니다. 아주 흔한 작업이고 공식 같은 코드가 있습니다. Public Sub 중복자한명만색상으로나타내기() i = 4 이름 = "" Do If Range("B" & i).Value 이름 Then Range("B" & i).Interior.ColorIndex = 19 이름 = Range("B" & i).Value End If i = i + 1 Loop While Range("B" & i).Value "" End Sub 단..

안녕하세요. 특정 셀의 값이 바뀌면 자동으로 다른 셀에 값을 복사하는 작업을 하려고 합니다. 이렇게 복사된 값을 이용해 차트를 만드는 일도 있습니다. 완성 파일 다운로드 사용자의 추가 작업 없이 동작해야 하므로 이벤트(Events)를 이용해야 합니다. 딱 생각난게 Change 이벤트 입니다. 키를 눌러 VBE를 엽니다. Sheet1 시트 Worksheet 개체 Change 이벤트에 작성합니다. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1")) Is Nothing Then Application.EnableEvents = False If Range("C2").Value "" Then 위치 = ..

아직 끝나지 않았습니다. ^^; 한동안 연락이 없어 다 끝난 줄 알았더니 갑작스럽게 연락이 왔습니다. 나머지 작업이 있다고. 한동안 연락이 없어 수작업으로 마무리 작업을 하나 싶었는데, 그동안 수작업을 하긴 했지만 아직 마무리된 것은 아니었다고 합니다. 두가지 작업을 요청했는데, 지급된 내역 중 중복된 항목을 걸러내 달라는 것과 지급 내역 중 전화번호가 없는 항목을 찾아 전화번호를 나타내 달라는 것이었습니다. 중복된 항목을 찾는 방법은 어렵지 않습니다. COUNTIF 함수로 중복값을 찾는 수식을 작성한 후 고급 필터로 해당하는 레코드를 뽑아 별도 공간에 표시하면 됩니다. 이때 고려해야할 부분이 있는데, 이떤 값을 기준으로 찾는가 입니다. 이름은 중복신청된 경우가 있고, 또 동명이인도 있습니다. 신청한 계..