컴퓨터를 만나는 기쁨

선입선출법에 따른 재고월 구하기

1. 문제 아래와 같이 월별 매입수량과 매출수량이 있을 때 선입선출법에 따라 남는 매입월(재고월)이 어떻게 되는지 구해보려고 합니다. 예를 들어 A품목의 경우 매입이 워낙 많고, 매출은 겨우 158개로 기초 재고도 소진하고 있지 못하며, F품목의 경우는 5월까지의 매출 수량이 1,031개로 기초재고 1000개를 소진하고, 2월 매입 물량 34개 중 31개를 소진하고 3개가 남게 되므로 재고 월은 2월이 됩니다. 2. 해법 매입물량과 매출물량을 비교해서 매입물량이 최종적으로 매출물량을 커버하고 남는 달이 재고월이 됩니다. 따라서, 매입물량도 더해가고, 매출물량도 더해가야 하며, 수량이 초과되는지 여부를 판단해야 하므로 기존까지의 Match함수와 sum함수로는 처리하기가 너무 복잡해지는데, Microsoft..

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. 해법 병합셀 다루기 정말 어렵죠? 가. 진행 주의 셀 수 알아내기 =OFFSET(A6,1,0)이라고 A6셀을 기준으로 한 행 아래 값을 구하려고 하면 "미 진행 주"가 아니라 A7셀이 돼서 0이 반환됩니다. 그러면 어떻게 해야 할까요? Match 함수를 이용해 "미 진행 주"의 위치를 찾은 다음 빼기 1을 하면 됩니다. 먼저 '미 진행 주'의 위치를 찾기 위해 =MATCH("미 진행 주",A6:A25,0)라고 하면 11이 구해지는데, 이것은 '미 진행 주'의 처음을 검색해서 그런 것이므로 1을 빼면 진행 주의 개수는 10이 됩니다. 나. 미 진행 주의 셀 수 알아내기 미진행 주의 개수를 알아내려면 A열에서 빈..

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은 현재 위..

한글은 Code, UniCode 함수를 이용할 수도 있고, Mid 함수를 이용할 수도 있습니다. 5. 한글 앞의 숫자 추출하기 가. 한글에 해당하는 Code 또는 UniCode값 알아내기 코드 값을 알아내기 위해 i11셀에 =code(h11)이라고 입력하면 42145가 구해지고, 유니코드 값을 알아내기 위해 j11셀에 =unicode(h11)이라고 하면 12593이 구해집니다. 이제 i11셀과 j11셀을 선택한 후 j11셀의 채우기 핸들을 더블 클릭하면 나머지 한글의 (유니) 코드 값을 알 수 있습니다. 따라서, 이것을 이용해서 한글의 위치를 알 수 있고, 1을 빼면 숫자만 구할 수 있습니다. 나. 숫자 추출하기 (1) Code 함수 이용하기 영문자의 위치를 구해서 숫자를 추출하는 D3셀의 수식을 복사해..

4. Code 함수 이용하기 가. Code 함수의 정의 및 아스키코드 표 Code함수는 문자에 대한 Ascii 코드 값을 반환해 주는 함수입니다. 아래가 아스키코드 표인데, 숫자는 48부터 시작하고, 알파벳 A는 65, 소문자 a는 97부터 시작합니다. 나. Code 함수를 이용한 첫 번째 문자의 위치 찾기 (1) 문자가 알파벳인지 여부 판단 한 글자씩 Code 값을 찾아내는 수식은 CODE(MID(B3,COLUMN(A:K),1))>=65 입니다. 그러면 아래와 같이 False, True, True... #Value!... 등으로 표시됩니다. 2019 버전 등의 경우는 먼저 11개의 범위를 잡은 다음 수식을 입력하고, CSE 캐를 눌러야 합니다. (2) 첫 번째 알파벳 위치 파악하기 =MATCH(TRUE..

1. 문제 아래와 같이 일별 데이터가 있는데, 1월은 말일까지 있지만 4월의 경우는 2023/4/30일이 일요일이라 근무일인 4/28까지의 데이터만 있는 특이점이 있습니다. 따라서, 단순히 EOMonth로 말일을 구할 수 없는 한계가 있으며, 5월은 2일까지뿐이 없어서 월말이라고 볼 수가 없습니다. 이와 같은 경우 해결책에 대해 알아보겠습니다. 2. Scan 함수의 구문 Microsoft 공식 홈페이지의 도움말을 보면 =SCAN ([initial_value], array, lambda(accumulator, value)) 라고 되어 있는데, lambda의 인수로 세 번째에 Calculation이 있어야 하는데 누락된 것 같습니다. ​ 맞는 구문은 =SCAN ([initial_value], array, l..