컴퓨터를 만나는 기쁨

일반적으로 UDF(User Defined Function, 사용자 정의 함수)는 다른 셀 주소를 참조해서 계산을 하며, 아래는 제곱값을 구하는 함수의 코드입니다. Function square(rngA As Range) square = rngA.Value * rngA.Value End Function 그러나, 자기 자신의 셀을 참조해야 할 수도 있습니다. 이때 사용하는 것이 Application.Caller 속성(Property)입니다. Application.Caller 대충 감을 잡으면 응용 프로그램이 호출하는 곳 또는 어디서 호출되었는지 정도가 될 것입니다. 따라서, 자기 자신 셀을 가리킵니다. 위의 경우에 항상 왼쪽 두 번째 셀의 값을 참고한다고 하면 범위를 이용하지 않고 아래와 같이 Applicat..

1편은 이 링크로 접속하면 됩니다. 2. Offset 함수로 구하기 가. Offset 함수의 구문 Offset함수의 구문은 OFFSET(reference, rows, cols, [height], [width])으로 reference는 기준점이고, rows는 떨어진 행 수, cols는 떨어진 열 수, height는 높이, width는 너비입니다. 대괄호 안에 있는 것은 옵션이므로 생략할 수 있는데, 높이와 너비를 지정하면 일정한 열과 행만큼 떨어진 위치에서 크기와 높이로 범위를 지정할 수 있습니다. 여기서, rows는 현재 셀이 0이고, 아래로 내려갈 때는 +, 위로 올라갈 때는 -, cols는 현재 셀을 기준으로 오른쪽으로 이동할 때는 +, 왼쪽으로 이동할 때는 -입니다. 나. index함수와 비교 in..

1편에서는 도수분포표를 데이터 분석 도구 명령을 이용해서 구했는데, https://lsw3210.tistory.com/entry/%ED%9E%88%EC%8A%A4%ED%86%A0%EA%B7%B8%EB%9E%A8%EA%B3%BC-Frequency-%ED%95%A8%EC%88%981 함수를 이용해 구간별 빈도수를 구하는 것이 Frequency 함수입니다. 1. 구문 FREQUENCY(data_array, bins_array) data_array : 필수 요소입니다. 빈도를 계산할 값 집합의 참조 또는 배열입니다. bins_array : 필수 요소입니다. data_array에서 값을 분류할 간격의 참조 또는 배열입니다. 1편에서 data_array는 입력 범위, bins_array는 계급 구간이라고 명명했습니..

1. 데이터 유효성 검사 - 날짜 목록아래와 같이 날짜별 지점별 판매량 데이터가 있을 경우 E2셀을 클릭하고, 데이터 탭, 데이터 도구 그룹, 데이터 유효성 검사 명령을 누른 후 제한 대상으로 목록을 선택하고, 원본에 커서를 넣고, A열을 클릭해서 A열 모두로 지정하고 확인 버튼을 누르면, 유효한 데이터만 표시되고, 아래에 공백이 없습니다. 날짜가 모두 표시되지 않으므로 E열 오른쪽의 경계선을 오른쪽으로 끌어서 너비를 조절합니다. 이때 열 너비를 자동 조절하기 위해 E열과 F열의 경계선을 더블 클릭하면 오히려 선택된 값에 맞게 좁아져서 안됩니다. 이때 홈 탭, 표시 형식 그룹에서 표시 형식을 숫자에서 간단한 날짜로 변경하면 열 너비가 자동 조절됩니다. 2. Unique 함수를 사용해 목록을 만들 경우 문..

1. 문제 1 가. 데이터 및 원하는 표 양식 지점별, 연도별 판매량과 판매액이 있는데, 지점별 판매액만을 추출하고 싶을 때 몇 개가 안된다고 하면 복사해서 붙여넎기로도 가능하지만, 수식을 이용해 구하는 것이 맞는 방법입니다. 나. 해결 논리 중요한 것은 데이터는 2칸 씩 떨어져 있는데, 기록은 한 칸씩 떨어져서 해야 하므로 데이터의 열 변화를 2로 나눠서 기록할 열을 정하면 됩니다. 다. 수식 M3셀의 값은 C3셀이고, N3셀의 값은 E3셀에 있습니다. 따라서, M3셀을 기준으로 한 칸 떨어진 셀의 값을 두 칸 떨어진 셀의 값으로 해야 하는데, M3셀은 C3셀와 같은 값이므로 Offset함수를 이용하면 =OFFSET(C3,0,0) 그리고, N3셀의 수식은 2칸 떨어진 E3셀의 값이므로 =OFFSET(C..

함수를 이용하면 다른 PC에서도 그대로 작동하기 때문에 편리한 대신, 워크시트 함수에는 병합 셀과 관련된 함수가 없으므로 https://lsw3210.tistory.com/entry/%EB%B3%91%ED%95%A9-%EC%85%80%EC%9D%98-%EC%B2%AB%EB%B2%88%EC%A7%B8-%EC%85%80-%EA%B0%92-%EC%B0%BE%EA%B8%B0 위 글과 같이 If함수를 중첩적으로 사용해야 하는 불편함이 있습니다. 따라서, VBA로 사용자 정의 함수를 만들어보겠습니다. 사용자 정의 함수는 PC별로 존재하기 때문에 다른 PC에서 열어보면 폴더명과 함께 사용자 정의 함수명이 표시되는 불편함이 있습니다. 그래도 수식으로 작성하면 복잡한데 사용자 정의 함수를 만들면 간단하게 해결할 수 있는..

아래 데이터를 이용하여 연도별, 지점별로 판매량과 판매액을 집계하는 것을 피벗 테이블과 SumIfs함수를 이용해 만들어 보겠습니다. 1. 피벗 테이블 이용 피벗 테이블 기능을 이용하면 쉽게 아래와 같이 만들 수 있는데, 열 레이블은 연도별로, 행 레이블은 지점별로 수정할 수는 있지만, 연도별까지 없앨 수는 없고, '합계 : 판매량'과 '합계 : 판매액'을 판매량과 판매액으로 수정하고 싶어도 '이미 사용 중인 피벗 테이블 필드의 이름입니다'라고 하면서 수정이 안되는 한계가 있습니다. 확인 버튼을 누른 후 Esc키를 눌러 원래 필드명으로 돌아옵니다. 2. SumIfs 함수 이용 위와 같은 한계점을 극복하기 위해 SumIfs함수를 이용할 수 있으며 이 방법은 피벗 테이블에 비해 어려운 단점이 있지만, 원하는 ..

1. 기능 참조 영역의 주소, 데이터 형식, 파일 등의 속성을 반환하는 함수로 단일 셀 또는 범위에 따라 반환하는 값이 다른 경우가 있습니다. 2. 구문 CELL(info_type, [reference]) 가. info_type(정보 유형) 정보 유형 반환 값 "address" 참조 영역에 있는 첫째 셀(A1)의 참조를 텍스트로 반환합니다. "col" 참조 영역에 있는 셀의 열 번호를 반환합니다. "type" 셀의 데이터 형식에 해당하는 텍스트 값입니다. 셀이 비어 있는 경우 비어 있는 경우 "b"를 반환하고, 셀에 텍스트 상수가 포함된 경우 레이블의 경우 "l", 셀에 다른 것이 포함된 경우 값에 대해 "v"를 반환합니다. "contents" 참조 영역에 있는 왼쪽 위 셀(범위인 경우 A1셀, 단일 셀..

3. 이름 관리자를 이용하는 경우 C3셀부터 E3셀까지는 계절에 해당하므로 수식 탭의 이름 관리자를 클릭한 다음 새로 만들기 버튼을 누르고, 이름에는 '계절', 설명에는 '계절에 해당하는 셀', 참조 대상에는 C2셀부터 마우스로 E2셀까지 끌어 지정합니다. 그리고, 확인 버튼을 누르면 아래와 같이 '계절'이란 이름이 생기고 값에는 여름철 등이 표시되고, 참조대상은 아래를 보면 ='Sheet1 (2)'!$C$2:$E$2입니다. 또다시 새로 만들기를 한 후 부하는 A3에서 A8셀로 지정하고, 요금구분은 B3셀에서 B8셀로 지정하고, 더할 범위는 C3:E8셀로 지정합니다. 그러면 아래와 같이 계절, 부하, 요금, 요금구분 등 네 개의 이름이 정의되었습니다. 이제 C13셀의 수식에서 $A$3:$A$8이라는 부분..

필터로 조건을 걸면 조건에 해당되지 않는 행은 보이지 않고 조건에 맞는 행만 보이기 때문에 SpecialCells(xlCellTypeVisible)으로 VBA에서 데이터를 처리해야 합니다. 1. SpecialCells(xlCellTypeVisible) SpecialCells(xlCellTypeVisible)란 홈 탭 > 편집 그룹에 있는 찾기 및 선택을 누르고, 아래에서 이동 옵션을 누르면 나오는 이동 옵션 중 '화면에 보이는 셀만'에 해당합니다. 나머지 이동 옵션은 아래와 같습니다. 이름 값 설명 xlCellTypeAllFormatConditions -4172 조건부 서식 - 모두 xlCellTypeAllValidation -4174 데이터 유효성 - 모두 xlCellTypeBlanks 4 빈 셀 xl..