1. 아별툴
  2. 아별툴 패밀리
  3. 엑셀 질문하기
  4. 엑셀 강좌
  5. 엑셀 팁
  6. 엑셀 자료실
  7. 엑셀 연구과제
  8. 엑셀 북마크
  9. 관련 프로그램 소개

배열 수식을 이용한 최근 판가 갖고 오기

엑셀일반 조회 수 5091 추천 수 0 2009.07.29 17:24:39

기말 재고자산의 평가시,

최근 판가와 기말 재고단가를 비교하여 판가가 낮을 경우 판가로 평가(저가법)하는 수식을 짜다가 만들었습니다.

 

내용추가 : 2011.11.29

가장 최근 값 하나만 갖고 오는 아래 팁 말고요..

오른쪽 값부터 차례로 갖고 오는 팁 게시물을 추가합니다.

http://www.abyul.com/zbxe/101764

 

[ 내용추가 2012.07.11 시작 ] ===================================================================

웹서핑하다가.. 비슷한 질문을 발견했는데.. 거기 답변이 예술이라 게시물을 업데이트합니다.

다운받기 : abyul_20120711_recentlyValue-2.xls    (이 파일만 받으면 다 들어있습니다. 다른 첨부파일 받을 필요 없습니다. ^_^;;)

=INDEX(C4:I4, MATCH(-1, C4:I4, -1))

 

미리보기

0012.png

 [ 내용추가 2012.07.11 끝 ] ===================================================================

 

 

내용추가 : 2009.11.18 15:45

아래 내용은 INDEX함수를 사용했는데, OFFSET함수를 사용하는게 더 편할 수 있다는 생각에 OFFSET함수를 활용한 수식도 추가합니다.

아래 파일을 참고하세요.

abyul_20091118_recentlyValue.xls

 

배열 수식은  =OFFSET(B4,0,MAX(IF(ISNUMBER(C4:I4),COLUMN(C4:I4)))-COLUMN(B4),1,1)

 

내용추가 : 2009.12.24

엑사모 질문에 대한 답변으로 만든 파일입니다.

숫자가 아닌 텍스트를 갖고 오는 것인데요.. 위 수식의 ISNUMBER가 NOT(ISBLANK()) 로 바뀐 것 뿐입니다.

abyul_20091224_recentlyValue.xls

 

배열 수식은  =OFFSET(A2,0,MAX(IF(NOT(ISBLANK(B2:J2)),COLUMN(B2:J2)))-COLUMN(A2),1,1)

 

참고로, 이 예제와 같이 데이터 중간에 비어 있는 셀이 없을 경우엔,

=INDEX(B2:J2,COUNTA(B2:J2)) 또는 =OFFSET(B2,0,COUNTA(B2:J2)-1,1,1) 식으로 단순히 사용할 수도 있다.

단.. 연속되는 데이터의 중간에 빈셀이 있으면 엉뚱한걸 갖고 올 수 있다.


 

판가는 DB에서 피벗테이블로 산출했는데,

그게 해당월에 판매 실적이 없으면 안 나오게 되어서 최근 판가를 구하기가 귀찮더라구요..

 

그래서 최근 실적이 있는 판가를 갖고오는 배열 수식을 만들어봤습니다.

 

참고하세요.. ( 다운 받기 : abyul_20090729_recentlyValue.xls  )

 

abyul.com: 배열 수식이므로 입력시엔 Ctrl+Shift+Enter로 입력할 것.
=IF(SUM(C4:I4)<=0,"",INDEX(C4:I4,,LARGE(IF(C4:I4<>"",COLUMN(C4:I4)-COLUMN(C4)+1),1)))

=====================================================================================

위 수식을 한번 뜯어볼까요? ㅎㅎ

 

다중 수식을 분석하실때는 안쪽 요소부터 보시라는 분도 계시지만,

저는 반대로 큰 틀에서 부터 시작하실 것을 권해드립니다.

 

  Phase. 1  

일단.. IF문이네요.. IF문은 조건, 참일때, 거짓일때로 구분되니까요.. 구분해보죠.

조건 : SUM(C4:I4)<=0

참일때 : ""

거짓일때 : INDEX(C4:I4,,LARGE(IF(C4:I4<>"",COLUMN(C4:I4)-COLUMN(C4)+1),1))

==> 결국.. 월별 판가들의 합계가 0보다 작으면 공백("")을 넣으란 얘기죠.

        우리가 관심있는건 0보다 작지 않을때.. 그러니까 INDEX()구문이 관심사항입니다.

 

  Phase. 2  

자.. 판가들의 합이 0보다 클때.. 어떻게 최신 판가를 갖고 오는지 보겠습니다.

INDEX 함수네요.. INDEX문은 배열, 배열에서 몇번째 행인지, 배열에서 몇번째 열인지 로 구성되어 있습니다.

배열 : C4:I4 ==> 월별 판가들 영역이네요.

몇번째 행인지 : ,, 이렇게 되어 있으니까.. 행은 생략한거죠? 1차원 배열이니까.. 생략한거겠죠?

몇번째 열인지 : LARGE(IF(C4:I4<>"",COLUMN(C4:I4)-COLUMN(C4)+1),1)

                             ==> 쉽게 안 알려주네요.. 월별 판가들 중에서 몇번째 열일까.. 다시 파악해보죠.

 

  Phase. 3  

LARGE 함수네요.. 일련의 범위에서 몇번째 큰 수를 알려주는 함수죠. LARGE( 배열 , 몇번째 )

배열 : IF(C4:I4<>"",COLUMN(C4:I4)-COLUMN(C4)+1)

           ==> 다시 IF문이네요.. IF문 안에 C4:I4로 되어 있죠? 그건 배열 수식으로 입력한거라서 그래요.

                   C4부터 I4까지 하나씩 확인한다는 겁니다. 아래 Phase. 4에서 자세히 보겠습니다.

몇번째 : 1 ==> 제일 큰수를 찾는 거죠? ㅎㅎ

 

  Phase. 4  

이제 진짜 배열 수식이네요..

배열이 들어가면 안되는 곳에 배열에 들어가 있으면 그건 해당 배열을 하나씩 순차적으로 증가시키면서 살펴본다는 겁니다.

for문이나 while문처럼 순환문으로 보시면 됩니다. 배열을 순환하면서 나올 결과값들을 묶어서 배열로 결과를 만들어줍니다.

IF(C4:I4<>"",COLUMN(C4:I4)-COLUMN(C4)+1)

 

반복 횟수는 배열 크기만큼.. 그러니가.. C4:I4.. 즉, {C4, D4, E4, F4, G4, H4, I4 } 이렇게 해서 7개죠.

첫번째 위 수식은 IF(C4<>"",COLUMN(C4)-COLUMN(C4)+1) 가 됩니다.

    C4값이 9,480이니까.. 공백이 아니어서( C4<>"" ), COLUMN(C4)-COLUMN(C4)+1 이 값을 계산하죠. 3-3+1 해서 1입니다.

두번째 위 수식은 IF(D4<>"",COLUMN(D4)-COLUMN(C4)+1) 가 됩니다.

    D5값이 공백이니까( D4<>"" 가 거짓이 되어서) FALSE 값을 갖게 되지요.

    위 IF문에는 거짓일때의 값이 없으므로 자동으로 FALSE값을 갖습니다.

세번째, 네번째도 해당 셀의 값이 없으니까 FALSE 값을 갖습니다.

다섯번째 G4 값이 9,300으로 공백이 아니니까.. COLUMN(G4)-COLUMN(C4)+1 해서 7-3+1 해서 5값이 됩니다.

여서번째부터 여덟번째까지도 다 공백이니까 FALSE 값을 갖구요..

그러면.. 위에서 말씀드린 것처럼.. 배열 수식은 배열을 돌면서 나온 각각의 결과값들을 배열로 되돌린다고 했으니까..

{ 첫번째 결과값, 두번째 결과값, ........ 여덟번째 결과값 } 해서 모아보면.. { 1, FALSE, FALSE, FALSE, 5, FALSE, FALSE } 가 됩니다.

 

저 결과값은 위에 Phase3에서 살펴본 LARGE 함수의 배열 부분에 해당하지요.

그래서 Phase3의 수식

LARGE(IF(C4:I4<>"",COLUMN(C4:I4)-COLUMN(C4)+1),1)는

LARGE( { 1, FALSE, FALSE, FALSE, 5, FALSE, FALSE } ,1) 가 됩니다. 저 중에서 제일 큰게 뭡니까? 5지요.. FALSE는 0입니다.

 

가장 최근월에 숫자가 들어있는 값이 배열에서 5번째라는 말이지요..

그래서.. Phase2의 INDEX 함수로 돌아가서 매칭해보면..

INDEX(C4:I4,,LARGE(IF(C4:I4<>"",COLUMN(C4:I4)-COLUMN(C4)+1),1)) 수식이

INDEX(C4:I4,,5) 가 되어서 C4부터 I4까지의 범위에서 5번째 셀.. 즉 G4셀의 값을 갖고 오게 됩니다.

 

결국 결과값은 G4셀의 값인 9,300원이 되는 것이지요.. 유후~

 

집에가서 곰곰히 생각해보세요.. ^^*

 

 

abyul_20090729_recentlyValue.jpg

 

 

문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)
List of Articles
번호 제목 글쓴이 날짜 조회 수
공지 엑셀일반 엑셀 짜투리 팁 모음.. imagefile [13] [레벨:30]아별 2005-07-22 31739
공지 엑셀일반 엑셀 잘 하는 요령은? _ 엑셀 추천사이트 포함 [2] [레벨:30]아별 2009-11-12 19993
공지 엑셀일반 엑셀 단축키(바로가는키, ShortCut) 총정리입니다. imagefile [243] [레벨:30]아별 2010-08-29 70165
» 엑셀일반 배열 수식을 이용한 최근 판가 갖고 오기 imagefile [레벨:30]아별 2009-07-29 5091
41 엑셀일반 베일벗은 오피스 2010 둘러보기 movie [레벨:30]아별 2009-07-19 4147
40 엑셀일반 본인의 엑셀 수준을 가늠해보세요.. ㅎㅎ [13] [레벨:30]아별 2009-07-03 5299
39 엑셀일반 배열 수식 및 배열 함수 활용하기 imagefile [레벨:30]아별 2009-03-04 6069
38 엑셀일반 MS OFFICE 2007 제공 서식 파일 file [레벨:30]아별 2008-11-21 3948
37 엑셀일반 엑셀2003 함수 기초 설명 [펌] imagefile [레벨:30]아별 2008-10-29 6988
36 엑셀일반 엑셀2003 단축키 모음 (Exel ShortCuts)-- file [레벨:30]아별 2008-10-29 8850
35 엑셀일반 PDF파일을 EXCEL파일로 변환하기 file [1] [레벨:30]아별 2008-10-24 11872
34 엑셀일반 엑셀에서 영어의 자간이 이상하게 벌어져서 타이핑 될때.. file [4] [레벨:30]아별 2008-10-20 8526
33 엑셀일반 엑셀에서 시간차이 구하기 imagefile [레벨:30]아별 2008-08-29 8275
32 엑셀일반 숫자를 한자, 한글, 영문으로 변환하여 표시하기. imagefile [레벨:30]아별 2008-08-21 8355
31 엑셀일반 열 방향의 데이터를 행방향 데이터로.. 데이터베이스(DB)형태로 변환하기.. imagefile [2] [레벨:30]아별 2008-08-20 8351
30 엑셀일반 셀에 입력된 수식을 문자열로 표시하기 imagefile [레벨:30]아별 2008-07-04 7383
29 엑셀일반 엑셀에서 날짜 데이터 가공/계산/변환 등 종합 선물 세트. file [2] [레벨:30]아별 2008-06-30 12579
28 엑셀일반 엑셀에서 날짜와 시간 계산하는 법 image [레벨:30]아별 2008-06-27 22923
27 엑셀일반 [펌] sumproduct 함수를 이용한 다중 조건 계산 imagefile [3] [레벨:30]아별 2008-04-11 19112
26 엑셀일반 행 기준 데이터를 열 기준 데이터로 바꾸기.. 행열 바꾸기.. imagefile [레벨:30]아별 2008-04-07 4908
25 엑셀일반 조건부서식으로 함수식있는셀들만 구분표시(color로)하기 imagefile [레벨:30]아별 2008-03-12 7169
24 엑셀일반 셀 색상별 합계 구하기( GET.CELL( TYPE,REFERENCE) ) imagefile [레벨:30]아별 2008-03-12 16564
23 엑셀일반 엑셀에서 외부 데이터 가져오기 imagefile [2] [레벨:30]아별 2008-03-11 6824