컴퓨터를 만나는 기쁨

1. 문제 1번과 같이 원시 데이터는 이름, 번호, 주소가 중복되는데, 품목과 수량을 건별로 입력되어 있습니다. 2. 해법 엑셀 버전에 따라 안될 수도 있습니다. 가. 구문 Unique 함수 구문 : =UNIQUE(array,[by_col],[exactly_once]) Unique 함수 구문 : =SEQUENCE(rows,[columns],[start],[step]) TextJoin 함수 구문 : TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) 나. 적용 예 B23셀 : =UNIQUE(TRIM(B3:D9)) => 결과 : 이름, 전화번호, 주소의 유일한 값만 구해줍니다. Trim함수는 텍스트의 좌, 우 공백을 제거하는 함수입니다. 위 데이터에서 주소를 보면 ..

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

1. 문제 아래와 같이 이름과 값이 자료가 있을 때, 이름에 해당하는 값의 합계를 구하려고 합니다. 2. 해법 1 위와 같은 표에서 이름이 홍길동이거나 장발산에 해당하는 값의 합계를 구하려면 =SUMPRODUCT(($A$3:$A$7=D3)+($A$3:$A$7=D4),$B$3:$B$7) 라고 입력해서 합계를 구합니다. 위 수식을 보면 ($A$3:$A$7=D3)+($A$3:$A$7=D4)라고 ($A$3:$A$7=D3)과 ($A$3:$A$7=D4)가 +로 연결되어 있는데, 이것은 두 가지 조건 중 하나만 일치해도 된다는 Or 조건입니다. 다시 말해 위 수식은 이름이 홍길동이거나, 이무인 경우가 됩니다. 그리고, 값의 범위 $B$3:$B$7에서 조건에 맞는 값을 찾아 합계를 구하는 것입니다. 이때 쉼표(,)를 ..

1. 문제 오른쪽 기준 표의 제품별, 잔량별 생산일수보다 왼쪽의 생산일수가 크다면 확인란에 "불량"이라고 표시하고, 아니면 공란으로 내버려 두려고 합니다. 2. 해법 여러 가지 함수로 해결할 수 있습니다. 가. Index + Match 함수 (1) 잔량을 0과 "있음"으로 구분하기 오른쪽 기준표를 보면 제품이 같더라도 잔량이 0인지 아닌지에 따라 생산일수가 다르다는 것을 알 수 있습니다. 그런데 왼쪽 표를 보면 잔량이 숫자로 표시되어 있으므로 두 개를 Match 하려면 왼쪽 표의 잔량 수치를 0과 "있음"으로 바꿔줘야 합니다. 따라서, if 함수를 이용해 0보다 크다면 "있음" , 0이면 0이라고 표시하면 되므로 수식은 =if(d3>0,"있음",0)이 됩니다. (2) 왼쪽 제품과 잔량에 해당하는 생산일수..

1. 문제 왼쪽에 데이터가 있고, 오른쪽에 prc_name과 eq.no가 일치하는 날짜를 찾는 것입니다. 2. 여러 가지 해결방안 prc_name과 eq.no에 해당하는 값을 찾은 다음 offset 함수를 이용해 오른쪽으로 다섯 번째 날짜를 찾을 수도 있고, index 함수를 이용해 날짜 범위를 지정한 후 행과 열 수를 지정해서 원하는 값을 구할 수도 있습니다. 3. Offset 함수 이용 가. Offset 함수의 구문 OFFSET(reference, rows, cols, [height], [width])로 reference는 참조 셀 rows는 이동할 행 수로 +면 아래, -면 위로 이동하고, 0은 현재 위치를 가리킵니다. cols는 이동할 열 수로 +면 오른쪽, -면 왼쪽으로 이동하고, 0은 현재 위..

나. 365 버전 (1) 사용되는 함수에 대한 설명 (가) Filter 함수 Filter함수는 필터 명령을 함수로 구현한 것입니다. 구문은 Filter( sourcearray , match [, include ] [, compare ] )인데, 첫 번째 인수는 필터 처리할 배열(범위)로서 머리글은 포함하지 않습니다., 두 번째 인수는 필터 조건으로 필드명=값 식으로 입력하는데, 참에 해당하는 조건식을 입력해도 됩니다. (나) Sort 함수 데이터 정렬명령을 함수로 구현한 것으로 구문은 =SORT(array,[sort_index],[sort_order],[by_col])으로서 첫 번째 인수는 정렬할 배열(범위)이고, 두 번째 인수는 정렬기준인 열, 세 번째 인수는 내림차순 또는 오름차순을 지정하는 것으로 ..

1. 문제 아래와 같이 당월 시트의 B열에는 거래처명이 있고, 고객 시트에 고객명이 있는데, 거래처명 중 일치하는 고객명을 추출하는데, 김영희와 영희는 모두 만족하지만 긴 고객명을 출력해야 합니다. 2. 해결 방안 여러 가지 방법이 있을 수 있습니다. Find 함수를 이용할 수도 있고, 365 버전이라면 Scan 함수를 이용할 수도 있습니다. 각각 해결 방법을 알아보겠습니다. 가. Find 함수 (1) Find 함수의 구문 FIND(find_text, within_text, [start_num])로서 find_text는 찾을 문자열, within_text는 찾는 문자가 들어 있는 문자열 또는 범위, start_num은 찾기 시작할 위치인데, 대괄호 안에 있으므로 옵션입니다. (2) 일치하는 고객명의 위치..

1. 문제 아래와 같이 1호기부터 5호기까지 일자별 작업 내용이 있고, 작업일자 중 마지막 날짜의 작업량 합계를 주, 야간을 구분해서 구하고자 하는 문제입니다. 2. 해결 방법 가. 호기별로 최종 작업 일자 구하기 LOT NO(K 열)이 있고, 작업량(P열)이 0보다 큰 것을 구하므로 배열 수식을 이용해야 합니다. 수식은 =INDEX(A:A,MAX(IF((K:K"")*(P:P>0),ROW(K:K)))) 인데, 배열 수식이므로 동적 배열 수식을 지원하는 경우는 CSE(Ctrl + Shift + Enter)키를 입력할 필요 없이 Enter키만 누르면 되며, 지원하지 않는 경우(레거시 배열 수식)는 수식을 작성한 후 CSE키를 눌러야 합니다. (수식의 이해) =INDEX(A:A,MAX(IF((K:K"")*(P..

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

아래와 같이 좌표와 도면 번호가 있을 때 특정 좌표에 해당하는 위치의 도면을 찾는 것을 해보겠습니다. 1. index 함수로 구하기 index함수의 구문은 INDEX(array, row_num, [column_num]) 로 배열, 행 수, 열 수인데 열 수는 대괄호 안에 있기 때문에 옵션이지만 위 경우에는 2차원 구조이기 때문에 열 수도 필요합니다. index함수는 위 표에서 아래로 몇 번째 있는지, 오른쪽으로 몇 번째 있는지 위치를 정해주면 값을 반환하므로, 첫 번째 array는 표의 범위인 J3셀부터 AC24셀까지입니다. 이제 문제는 행 수와 열 수를 구하는 것입니다. 그런데 특이한 점은 E(ast)는 100이 네 번째 칸에 있고, N(orth)는 100이 네 번째가 아닌 다섯 번째 칸에 있다는 것입..