컴퓨터를 만나는 기쁨

5. Index 함수가. 기능 및 구문Index함수는 행 또는 열 번호로 지정된 데이터를 추출해 줍니다.구문은 INDEX(array, row_num, [column_num])입니다. 나. 사용법(1) 1개 또는 특정 셀 선택하기범위에서 셀 한개만을 선택하려면 해당하는 셀에 해당하는 행 과 열 번호를 입력하면 됩니다.여기서 행 번호와 열 번호는 시트 전체가 아니라 범위를 기준으로 1부터 시작하니 주의해야 합니다. 예를 들어 A3셀에서 C8셀에서 2번째 행, 3번째 열의 값은=INDEX(A4:C8,2,3)으로 구하는데,4행이 index 함수의 행 번호로는 1이고, A열은 A열부터 시작했기때문에 열 번호가 1로 시트 기준이나 같습니다.따라서, 행 번호 2와 열 번호 3이 교차하는 범위는 C5셀이 되고, C3셀..

3. ChooseCols 함수 이번에는 구분이 판매뿐이 없으므로 필드를 일자와 상품만 표시해 보겠습니다. 그러려면 열을 선택하는ChooseCols함수를 사용해야 합니다. 가. 구문ChooseCols 함수의 구문은=CHOOSECOLS(array,col_num1,[col_num2],…)로서 첫 번째로 배열을 입력하고, 표시할 열의 순번을 차례대로 입력하는 것입니다. 나. 수식=FILTER(A4:C8,B4:B8="판매")로 구한 결괏값인 E4:G5셀에서첫 번째 열인 일자와 세 번째 열인 상품만 표시하면 되므로Filter 수식 앞에 ChooseCols를 붙인 후 ,1,3)를 입력하면 됩니다.=CHOOSECOLS(FILTER(A4:C8,B4:B8="판매"),1,3) 그러면 일자와 상품만이 표시됩니다.=FILTER..

1. 필터 명령 아래와 같이 일자별, 구분별, 상품이 있을 때구분이 판매인 것의 일자와 상품을 구하려고 합니다. 홈 탭에서 '정렬 및 필터' 누르고, 그 아래 필터를 누릅니다. 그러면 일자, 구분, 상품 필드에 필터 버튼이 생기는데, 구분 옆의 필터 버튼을 누르면모두 선택, 구매와 판매가 모두 체크되어 있는데, 구매의 체크를 해제하면 판매만 남으므로 확인 버튼을 누릅니다. 그러면 구분이 판매인 일자와 상품이 표시됩니다. 2. Filter 함수가. 구문=FILTER(array,include,[if_empty])로서 array는 필터를 적용할 범위,include는 조건에 해당하며if_empty는 대괄호로 되어 있으므로 선택값으로 조건에 맞는 값이 없을 때 어떻게 처리할 것인가를 정해주는 것입니다. 나. 구분..

1. 문제 A1셀부터 B2셀까지 공백과 줄 바꿈 문자를 넣어서 연결해서 표시할 때 두 번째 단어의 위치를 맞추려면 어떻게 해야 하는지 탐구해 보겠습니다. 수식은 =CONCAT(A1," ",B1,CHAR(10),A2," ",B2)로서 Concat함수는 문자열을 결합해서 표시해 주는 함수이고, 공백 한 칸은 "(큰따옴표) 다음에 스페이스바를 한 번 밀어 삽입하며, 줄을 바꾸는 것은 키보드로 할 때는 Alt+Enter키를 누르는데, 아스키코드로 하면 10(Line Feed)이라 Char(10)을 사용합니다. 그런데 문제는 한글이 두 바이트이므로 두 번째 줄의 공백 한 칸을 스페이스바를 두 번 더 밀어 3칸으로 하면 맞을 듯한데 그게 아니고, 첫 번째 단어의 길이가 위가 길면 아래에서 스페이스바를 밀어야 하고,..

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,"?번") "..

3. 해법 2 - SubTotal 함수 이용 1편은 여기를 참고 바랍니다. 가. SubTotal 함수SubTotal 함수의 구문은SUBTOTAL(function_num,ref1,[ref2],...)로서 function_num에 따라 합계, 평균, 숫자의 개수 등 다양한 값을 구할 수 있습니다. Function_num(숨겨진 행 포함) Function_num(숨겨진 행 무시) 함수 1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP  아래와 같은 숫자의 합을 구할 때 Sum을 사용할 수도 있고, Subtotal(9,을 사용할 수도 있습니다.값이 같은지 비교하기 위해 그룹 1과 그..

1. 문제  위와 같이 C열에 매장별 입고량이 있고, D열에는 판매량이 있으며, 판매량 목표는 입고량의 80%입니다.이에 따른 달성 여부가 G열에 있는데,목표가 언제 달성되었는지, 다시 말해 H열부터의 일자별 판매량 누계가 목표량을 넘어가는 일수 또는 날짜를 구하려고 하는 것입니다. 2. 해법 1 - Scan 함수 이용 가. 누계 판매량 구하기 MIcrosoft 365 버전이라면 Scan 함수를 이용할 수 있습니다.Scan 함수의 사용법에 대해서는 아래 글을 참고 바랍니다. https://lsw3210.tistory.com/484 선입선출법에 따른 재고월 구하기1. 문제아래와 같이 월별 매입수량과 매출수량이 있을 때 선입선출법에 따라 남는 매입월(재고월)이 어떻게 되는지 구해보려고 합니다.예를 들어 A품..

통화 기호를 알아내는 사용자 정의 함수(UDF) 만드는 것은 아래 글을 참고 바랍니다.https://lsw3210.tistory.com/538 1. 문제 위 URL에서 통화 표시 형식에 따라 통화 기로를 C열에 수식을 입력해서 구했는데, 이들 통화 기호에 따라 F열에서 K열까지 있는 환율을 이용해 원화로 표기해 보겠습니다.  2. 해법 (가) 수식 1환율이 오른쪽으로 배치되어 있으므로HLookup함수를 사용해야 합니다. 따라서, D1셀에=HLOOKUP(C1,$F$1:$K$2,2,0)라고 입력해서, C1셀 값을 F1셀에서 K2셀까지의 범위의 첫 번째 행에서 찾아 두 번째 행의 값을 구하면 ₩이므로 1이 구해집니다. 이제 A1셀의 값을 구하면 됩니다. 100원이 구해졌습니다. 다시 D1셀의 채우기 핸들을 더..

Sub 프로시저를 실행하면 여러 가지 동작을 한 번에 실행하는 것이고,Function 프로시저는 내가 원하는 형태의 함수를 만드는 것입니다. 다시 말해 =함수명(셀 주소) 형식으로 매크로를 실행하는 것이 아니라함수로 결괏값을 반환받는 것입니다. 따라서, 사용자 정의 함수(User Defined Function, UDF)를 만들어 사용하면 편리합니다. 1. 논리NumberFormat을 했을 때는 원화 표시 ￦가 $로 표시되어서 사용할 수 없고,NumberFormatLocal을 사용해야 합니다.  그러나. 세 번째 줄부터는 NumberFormat이나 NumberFormatLocal이 같습니다. 그리고, 한 자리 기호인 경우, 다시 말해 '['표시가 없는 경우)는 첫 번째 기호인 ￦와 $를 가져오면 되는데세 ..

1. 엑셀에서 알아내기 - 실패 아래와 같이 통화 표시가 있을 때 B1셀에 =left(a1,1)이라고 하면￦가 구해지는 것이 아니라 100에서 왼쪽 한 글자인 1이 구해집니다. 다른 것도 마찬가지입니다.B1셀의 채우기 핸들을 더블 클릭해서 B6셀까지 수식을 복사하면 원하는 것과 는 달리 모두 1이 구해집니다. 2. VBA로 해결 - 성공 가. 구문 속성이므로 Range.NumberFormat 또는 Range.NumberFormatLocal이라고,Range 다음에 .(점)을 찍고 써서 표시형식을 알아내는데,숫자뿐만 아니라 통화, 시간 등 모든 표시형식이 가능하며, NumberFormat과 NumberFormatLocal의 차이는 Local은 사용자의 언어형식으로 표시한다는 것입니다. 나. Sub 프로시저 ..