컴퓨터를 만나는 기쁨

이번에는 VLookup을 사용하는데,조견표를 배열로 입력하는 방법에  대해 알아보겠습니다.  A열과 B열에 지점명과 성과급 지급률이 있고,오른쪽에 성명과 소속 지점이 있을 때 이에 맞는 지급률을 왼쪽 표에서 찾아 입력하려고 합니다. 1. VLookup 함수의 구문VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])로서 ① lookup_value는 찾으려는 값, ②table_array는 찾으려는 값이 첫 번째 열에 있고, 구하고자 하는 값이 들어 있는 표 형식의 배열 또는 범위입니다. 표 형식이라고 해서 삽입 탭 아래에 있는 표여야 하는 것은 아니고(표라도 상관은 없음), 우리가 일반적으로 많이 접하는 가로와 세로로 구성된 표, 위 지점별 ..

1. 문제  일위대가시트에서 해당되는 공종의 총액과 노무비를 찾는데, 분명히 일위대가시트의 F41셀과 H41셀에 금액이 있는데도 #N/A 에러가 납니다.  2. 해결 가. 와일드 카드 *와 ?의 의미*는 0개 이상, 다시 말해 없어도 되고 여러 개의 문자가 올 수 있는 것이고,?는 1개의 문자를 가르킵니다. 아래와 같은 데이터가 있을 때 "번"이 들어가는 것은 모두 합계를 내고 싶은 경우는=SUMIFS(P4:P10,O4:O10,"*번")라고 하면 번 앞에 글자가 하나이거나 두개이거나 모두 합산을 하므로 95+66+95=256이 되는 것입니다. 그러나 "번"앞에 글자가 하나만 있는 것만 더하고 싶다고 하면 *이 아니라 ?를 사용해야 합니다.=SUMIFS($P$4:$P$10,$O$4:$O$10,"?번") "..

나. 두 번째 방법 (1) Lookup 함수VLookup 함수는 많이 다뤄봤는데, Lookup 함수는 낯섭니다. 그렇지만 일련의 범위에서 일치하는 값을 찾은 후 반환값을 찾는 것은 동일합니다. Lookup 함수는 벡터형과 배열형으로 나뉘는데 배열형의 경우는 VLookup 또는 HLookup함수를 사용하라고 권장하고 있습니다. 벡터형의 구문은 LOOKUP(lookup_value, lookup_vector, [result_vector])로서lookup_value는 찾을 값,lookup_vector는 찾을 대상으로 행이나 열을 한 개만 포함하는 범위,result_vector는 반환 값이 있는 행이나 열을 한 개만 포함하는 범위입니다. 아래 예제의 수식은 =LOOKUP(4.19,G2:G6,H2:H6)로서4.19..

산인 경우 하이픈이 있는 경우가 없어서,A8셀의 주소를 경기도 가평군 북면 화악리 산 339에서 경기도 가평군 북면 적목리 산 1-1로 수정했습니다.   6. 필지구분(산과 일반) 구하기일반이면 1이고, 산이면 2이므로 간단한데,지번 데이터를 기준으로 생각하면 '산'이 있으면 2, 없으면 1이 됩니다. 따라서, 수식은=if(left(c2,1)="산",2,1)이 됩니다. F2셀의 채우기 핸들을 더블 클릭하면 '산'인 경우 2로 잘 표시됩니다. 7. 본번과 부번을 4자리로 변경하기가. 본번을 네 자리로 만들기본번과 부번을 모두 4자리로 표시해야 합니다.따라서, 앞에 '0"을 3개 붙인 후 뒤에서 4개를 가져와야 합니다. 그리고, 문제는 '산'이 있느냐 없느냐에 따라 다르고, 하이픈이 있느냐 없느냐에 따라 달..

5. 법정동에 해당하는 코드 구하기가. 필요한 파일주소에서 법정동 코드와 지번을 분리한 것은 아래 파일을 다운로드하면 되며,  법정동 코드 자료는 아래 엑셀 파일에 있습니다.  나. 법정동에 해당하는 코드 찾기(1) Index + Match 함수Vlookup 함수를 찾으려면 찾으려고 하는 법정동명이 왼쪽에 있어야 하는데 오른쪽에 있으므로 Index + Match 함수를 사용해야 합니다. Match함수로 법정동명에 해당하는 위치를 찾고, Index 함수와 연결해서 그 줄에 해당하는 법정동코드를 가져오는 것입니다. 수식은 =index(법정동코드 범위, match(법정동명, 법정동명 범위,0))입니다. 법정동코드 전체자료.xlsx의 내용을 PNU만들기(주소 분리).xlsx 파일에 시트로 추가할 수도 있지만 그..

1. 문제  아래와 같이 부서별, 성명별 구분(본부·지점)별, 직급별 평정 대상자 시트가 있고,   본부·지점별 , 직급별로 1,2차 평정자와 조정평정자를 입력한 시트가 있을 때   부서별로 1,2차 평정자와 조정평정자를 구하는 것을 해보겠습니다. 2. 논리  VLookup 함수의 구문은  VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])인데,  2번째 인수인 table_array(찾을 범위)를 지정하는 것이 중요합니다.   본부라면 B5셀에서 E20셀을 참고하고,  지점이고 직급이 3급 이상이면 G5셀에서 J12셀을 참고하고  지점이고 직급이 4급이하면 L5셀에서 O12셀을 참고하도록 해야 합니다. 3. 해법 가. 찾을 범위를 이름으..

1. 문제 아래와 같은 매출자료를 바탕으로 여러 가지 검색 및 집계를 해보겠습니다. 2.Vlookup 함수의 장점 및 한계 가. 장, 단점 VLookup 함수는 찾을 값을 빨리 찾아주는 기능이 있는데, 찾고 자 하는 값이 찾을 범위의 첫 번째 열에 있어야 하고, 반환받을 값은 인덱스가 +여야지 -면, 다시 말해 찾을 범위의 첫 번째 열의 왼쪽에 있으면 안 됩니다. 그리고, 중복된 값이 있어도 첫 번째 값만 반환해 주는 한계가 있습니다. 나. VLookup 함수를 이용한 예시 (1) A지점에 해당하는 매출일자 및 매출액 찾기 (가) 매출일자 찾기 E2셀에 A지점이라고 입력하고, F2셀과 G2셀에 A지점에 해당하는 매출일자와 매출액을 구해보겠습니다. 먼저 매출일자를 구하는 수식은 아래와 같습니다. =VLOO..

1. 문제고지서 서식은 한 장이고, 인쇄할 내용을 아래 명단 시트에서 순서대로 찾아서 인쇄하고자 할 때 먼저 생각나는 것이 워드나 한글의 메일 머지 기능입니다. 그러나, 여기서는 엑셀의 매크로로 구현하고자 하는 것입니다. 인쇄할 내용을 채우는 방법이 두 가지가 있습니다. 이름만 입력하면 나머지 내용은 수식으로 가져오는 방법, 이름뿐만 아니라 나머지 내용까지 매크로로 처리하는 방법인데, 한 가지씩 알아보겠습니다. 2. 해법 1 : 이름만 입력하고 나머지는 수식으로 가져오는 방법 가. 연번별로 이름에 순번 부여하기(1) 로직 연번을 기준으로 인쇄하는데, 연번에 동일인이 있다면 같은 고지서에 인쇄하고, 다른 사람이라면 별도의 고지서에 인쇄해야 합니다. 이를 위해서는 고지서 발행 기준인 순번과 동일인여부 순번 ..

1. 문제 아래와 같이 직원별, 일자별 근무 현황이 있는데, 근무 유형별, 일자별로 누가 근무했는지와 시간외근무를 오른쪽 '시간외근무 유형'에 따라 괄호 안에 시간수를 입력해서 표시하려고 합니다. 원 데이터는 다른 시트에 있는건데 이해와 설명의 편의를 위해 한 개 시트에 모았습니다. 2. 해결 방안 365 버전용 함수를 이용하는 방법과 이전 버전용 함수를 이용하는 두 가지 방법에 대해 알아보겠습니다. 가. 365 이전 버전 (1) d1, d2, n1, n2 유형에 따라 일자별 근무자 및 괄호안에 숫자 표시 일자와 유형 2가지 조건을 만족해야 하므로 두가지 조건을 * 연산자로 연결하면 =($B$3:$H$8=K$2)*($B$2:$H$2=$J3)이 되는데, #분산!라고 표시되고, 6*7의 크기로 테두리가 쳐집..

좌우 데이터가 좀 달라야 하는데 동일하게 만들었습니다. 다른 점은 형식이 왼쪽은 주계좌 및 서브계좌에 -이 있고, 오른쪽의 날짜가 날짜 형식이 아니라 문자로 되어 있어 변환이 필요한 상황입니다. 데이터 건수가 17951로 매우 큽니다. 1. 서브계좌 구하기 이와 같은 경우에 사용하는 함수가 VLookup입니다. 주계좌의 형식이 다르기 때문에 형식을 통일해야 하는데, 오른쪽 검색 범위는 바꿀 수 없으니 왼쪽 것을 Substitute 함수를 이용해 바꾸면 =substitute(a4,"-","")이 됩니다. 다시 말해 하이픈(-)을 공백으로 바꾸는 것입니다. 이제 Vlookup 함수와 결합하면 =vlookup(substitute(a4,"-",""),$a$4:$i$17951,2,0)이 됩니다. 그런데, 이상하게..