컴퓨터를 만나는 기쁨

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. SumProduct 함수 SumProduct 함수는 Product, 다시 말해 곱한 값을 더해서 반환해 주는 함수입니다. 가. Product와 Sum함수를 이용하는 경우 아래와 같이 숫자1과 숫자2를 곱한 다음 합계를 낸다고 할 때 Product와 Sum 함수만 알고 있다면 먼저 줄별로 Product, 예를 들어 =product(a2:b2)를 한 후 C7셀에서 Sum을 하면 됩니다. 나. SumProduct 함수를 사용하는 경우 (1) 구문 =SUMPRODUCT(array1, [array2], [array3], ...) 배열로 되어 있는데 범위라고 생각하면 간단합니다. (2) 사용 예 배열을 세로로 해서 A열을 배열1, B열을 배열2로 지정하면 됩니다. 따라서, 위와 같이 곱한 것의 합을 구하려면 ..

한 가지 조건을 만족하는 경우 채우기에 대해서는 아래 글에서 다뤘는데, https://lsw3210.tistory.com/entry/%EC%97%91%EC%85%80-%EB%B0%B0%EC%9A%B0%EA%B8%B036-%EC%A1%B0%EA%B1%B4%EB%B6%80-%EC%84%9C%EC%8B%9D5-%EC%88%98%EC%8B%9D%EC%9D%84-%EC%82%AC%EC%9A%A9%ED%95%B4-%EC%85%80-%EC%A7%80%EC%A0%95 엑셀 배우기(36) - 조건부 서식(5) - 수식을 사용해 셀 지정⑥ 수식을 사용하여 서식을 지정할 셀 결정 같은 줄, 성명 별로 최댓값 찾아서 서식 적용하기 ㉮ 마우스로 B3셀부터 E7셀까지 끌어 사각형 모양의 범위를 잡습니다. 그리고, 조건부 서식 -..

자료를 작성하다 보면 자료별로 자료가 다른지 비교하거나 다른데서 자료를 가져와야 할 때가 있습니다. 이 때도 Vlookup함수를 사용할 수도 있고, SumProduct, SumIfs, Sum+If 배열 수식 등 다양한 방법을 적용할 수 있습니다. 아래와 같이 왼쪽과 오른쪽에 지역별 월별 판매량 자료가 있고, 오른쪽에는 인원수 데이터만 있다고 할 때 왼쪽에 맞는 값을 찾아서 붙일 경우를 생각해 볼 수 있습니다. ※ 일반적으로는 시트를 달리하겠지만 이해가 쉽도록 왼쪽과 오른쪽에 배치했습니다. 1. 왼쪽과 오른쪽의 판매량 검증하기 Vlookup함수는 조건에 맞는 값을 찾아주는 함수이기 때문에 이 함수를 맨 먼저 사용해야 하지만 조건 2개를 지정할 수 없기 때문에 새로운 열을 추가해야 하기 때문에 맨 나중에 설..

1. 정의 product는 '곱하기'란 의미이고, sum이므로 곱한 것의 합계가 됩니다. 그러나, 곱하기뿐만 아니라 더하기, 빼기, 나누기한 후 합계를 구할 수도 있습니다. 2. 구문 =SUMPRODUCT(array1, [array2], [array3], ...) - array1만 필수이고, array2부터는 선택입니다. - array1에 배열 수식처럼 조건 여러 개를 *나 +로 연결해서 입력할 수 있습니다. - Sum+If 배열수식, Sum 또는 SumIfs 함수로 같은 결과를 얻을 수 있는 경우도 있고, 피벗 테이블이 더 효율적일 때도 있습니다. 3. 예제 가. 수량 * 단가로 총매출 구하기 SumProduct 함수의 전형적인 예입니다. 위 표와 같은 경우 수량 * 단가의 합을 구하려면 일반적으로는 ..

예전에 작성했던 엑셀 파일을 여니 @표시가 생겨서 살펴보니 @는 Implicit Intersection(암묵적 교차점) 연산자로서 과거 버전과의 호환성을 위해 Microsoft 365에 새로 도입된 기능이라고 합니다. 1. Microsoft 365의 배열 처리의 특수성 c2셀에 =b2:b13*2라고 입력하고 이전 버전의 경우는 Ctrl + Shift +Enter키를 눌렀는데, 엔터 키만 눌러도 C2셀부터 C13셀까지 B2셀부터 B13셀까지 값에 2를 곱한 값이 채워지고, 파란색 선으로 테두리가 쳐집니다. 다시 말해 배열인 경우 자동으로 배열의 크기만큼 계산이 이뤄집니다. 2. 이전 버전과의 호환성 확보 이전 버전의 경우는 Ctrl + Shift +Enter키를 누르지 않고 엔터 키만 누르면 배열이라도 셀..

indirect함수를 사용하면 편리한 경우로 계산하고자 하는 셀 주소가 변경되는 경우입니다. 아래 표는 구간별 명수만이 있어서 몇 개 구간에 대한 명수의 합계를 구할 경우 시작셀과 종료셀의 주소가 매번 달라지게 됩니다. 위와 같은 표로는 Match함수를 사용할 수 없으므로 아래와 같이 초과, 이하 수치 모두 기록하지 않고, 초과 수치만 지정해야 합니다. 그러면 0은 0 초과 60 이하가 되고, 60은 60 초과 70 이하가 됩니다. 1. Match함수를 이용하는 경우 가. 데이터 유효성 검사로 입력값 제한하기D10셀과 E10셀에서 입력을 받는데, 중간값 예를 들어 65 등은 입력하면 안 되므로 데이터 유효성 검사를 이용해 목록으로 값을 제한합니다. ① D10셀부터 E10셀까지 마우스로 끌어서 범위를 선택..

1. 최근 5년간 시군구 총인구수 자료 만들기 먼저 국가통계포털(https://kosis.kr/index/index.do)에 접속한 후 국내 통계 - 주제별 통계, 인구 > 인국총조사 > 인구부문 > 총조사인구(2015년 이후) > 전수부문 > 전수기본표에서 '인구, 가구 및 주택 - 읍면동, 시군구를 누릅니다. 그러면 기본값이 2021년도만 데이터를 보여주므로 콤보 상자 버튼을 누른 후 최근 5년으로 변경하고, 오른쪽 위 '다운로드' 버튼을 눌러 엑셀로 다운로드합니다. 그리고 파일을 열면 파일 형식과 확장명이 일치하지 않는다고 하면서 열 것인지를 묻는데, 예를 클릭합니다. 이것은 파일 형식은 xlsx인데, 확장명은 xls라 그런 것입니다. 연 다음 확장명을 xlsx로 바꾸는 것이 좋습니다. 다른 이름으..

필터에 텍스트 필터, 숫자, 날짜 등 필터가 있지만 이외에도 색 기준 필터가 있어 유용하게 사용할 수 있습니다. https://lsw3210.tistory.com/entry/%EC%97%91%EC%85%80-%EB%B0%B0%EC%9A%B0%EA%B8%B06-%ED%95%84%ED%84%B0-%ED%95%84%ED%84%B0-%EC%A7%80%EC%9A%B0%EA%B8%B0-%EB%8B%A4%EC%8B%9C-%EC%A0%81%EC%9A%A9 엑셀 배우기(6) - 필터, 필터 지우기, 다시 적용 필터는 원하는 데이터만을 걸러주는 기능입니다. 홈 탭의 편집 그룹에 필터 명령이 있고, 데이터 탭에는 필터와 고급 필터가 모두 있습니다. 필터와 고급 필터를 두 번에 나눠서 이번에는 필터 lsw3210.tistor..

1편에서는 표로 만들었는데, 입력하지 않은 주변 셀까지 수식이 자동으로 입력되어 범위로 변경하였습니다. 이전 편에서는 과목별 합계 등을 구해봤는데, 이번에는 성명을 기준으로 한 합계 등을 구해보겠습니다. 다른 점은 과목별 합계를 구할 때는 Sum 등이 아래에 있어서 4개를 뺄 수 있었는데, 성명별 합계를 구할 때는 처음에는 없다가 VBA를 실행하면 추가되기 때문에 두 가지 요소를 고려해서 매크로를 작성해야 한다는 것입니다. 또한 합계 등을 구할 때 행 별로 합계를 구해야 하고, 행을 내려가면서 합계 수식을 반복하는데 Sum이전의 end_row_of_calc(계산할 마지막 행) 까지만 실행해야 합니다. 1. 코드 작성 가. 계산할 마지막 열 구하기 Sum 등이 있을 경우와 없을 경우 end_col_of_c..