무지개타고

명절이 다가왔다. 김도 구웠고, 만두도 빚었고, 다음엔 고구마전을 만들어야 한단다. 차례상에 이것저것 올려놓으려니 덩달아 바빠지는데... 삼겹살에 콜라로 어찌 않되나??? 아래와 같은 판매목록이 있다고 할 때, 고객ID A001, A003은 수량이 부족했는지 추가로 구매해 갔다고 하고 집계를 해보자. 보통은 품목별로 집계하는데 빅데이터 시대(?)에 걸맞게 ID별로도 집계해 보자. 여기서 유일ID 빈도는 중복된 ID를 하나로 처리해 빈도를 구하겠다는 것. f3 셀 (ID*품목별 수량) =SUMPRODUCT(($A$3:$A$22=$E3)*($B$3:$B$22=F$2)*($C$3:$C$22)) f11 셀 (유일ID 빈도) =COUNTIF(F$3:F$10,">0") 그리고 이를 이용해 교차품목별 유일ID 빈도를..

년말쯤 되면 신년일출산행 준비하고 그랬지만... 설악산 아니 간지도 꽤 됐군. 저 날 엄청 추웠는데... 아무튼... 신년일출산행을 재끼는 대신 맛난거나 먹어볼까나? 일전에 한 번 다뤄본 자재입출고 관리와 비슷한 유형으로, 이번에는 자료 구성 방식을 조금 달리해 값을 구해본다. 위와 같이 수량과 세트&메뉴&상품 구성이 되어있을 때 상품별 수량은? 물론 배열수식이고 Ctrl+Shift+Enter 키를 동시에 눌러야 한다. c29 셀 =SUM((TRANSPOSE($B$4:$B$8)=$B$12:$B$16)*MMULT(1*ISNUMBER(MATCH($C$12:$D$16,IF(MMULT(1*($C$20:$D$25=$B29),TRANSPOSE(COLUMN($C$20:$D$25)^0))>0,$B$20:$B$25),0..

오튜에 올라온 질문인데.. 두 개의 필드로 구성된 목록을 정리하는게 목적이다. 필드가 하나 일때 보다는 조금 복잡하다. 이는 고급필터 또는 피벗테이블 기능으로 간편하게 구할 수 있다. 그러나 질문자는 기능이 아닌 수식으로 처리하길 바라는데... 자료가 어낙에나 많아 수식으로는 한계가 있는 질문이었다. 이쯤에서 몇번 소개한 구글닥스의 Unique 함수를 보자. 이름 그대로 Unique 함수는 목록 정리용으로 아주 딱이다. 너무나 유용한 함수이기에, 엑셀로 복제해 반영해줬으면 정말 좋겠다. 앞서 질문을 엑셀에서 수식으로 처리하려면 삽질 좀 많이 해야 한다. 위에서 봤듯이 구글닥스에서는 Unique 함수 딱 하나로 끝나는데 말이다. d3 셀 (구분) =IF(SUM(1*(FREQUENCY(MATCH($A$2:$..

오튜에 이동평균 구하기와 거의 유사한 질문이 올라왔는데, 각각의 순번에서 범위 만큼을 순차적으로 합산해 이동합을 원한다는 것. 일반적인 이동평균/합과 조금 다른 점이라면... 범위가 고정된 값이 아닌, 유동적으로 변할 때 적용 가능한 수식이랄까. 더불어 값이 채워져 있지 않아도 이동합을 계산할것. (이 조건이 필요한 경우가 있을까?) 어쨌든... 이동합을 구해봤다. c3 셀 (이동합) =IF($C$1+ROWS($B$3:$B$12)>=ROW($B2),SUM(OFFSET($B3,-MIN(ROWS($B$3:$B3),$C$1)+1,0,MIN(ROWS($B$3:$B3),$C$1),1)),"")

더하기 연재가 스무번까지 올 줄이야... 아무튼 더하기는 계속 된다. 지난 번에 다룬 오튜 질문이 좀더 복잡했다. 그래서 이를 다조건 행렬에 반영... 그리하여 나온 결과는... g2 셀 (판정) =INDEX($B$14:$D$14,MATCH(SUM(10^(ROW(INDIRECT("1:"&COUNTA($B$14:$C$14)))*(MMULT(1*(T(OFFSET($A$7,0,MMULT(COLUMN($B$7:$E$7)^0,1*($B2:$F2>=TRANSPOSE($B$8:$E$12)))+1))=TRANSPOSE($B$14:$C$14)),ROW($A$8:$A$12)^0)>=TRANSPOSE($B$15:$C$15)))),{11,101,2},0)) 물론 배열수식이고... 구글 크롬에서도 수식이 제대로 보여지지 않는다..

오튜에 흥미로운 질문이 올라왔는데... 언제나처럼 내 맘대로 정리를 해봤다. 질문은 대충 그랬다. 항목별 제시된 조건에 따라 각각의 점수를 구분짓고, 이를 종합해 최종 판정을 하겠다는 것. 그런데 제시된 조건이 많이 까탈스럽다. 그나마 부등호가 "미만"과 "이상"으로 일정하다는게 다행이랄까... 음... 어쩐담? 땡기긴 땡기는데... 그래서 조금 변형된 다조건 행렬을 구성했다. 제시된 조건을 "미만"이 기준값이 되게 먼저 입력하고, 행렬틀에 맞춰 빈 자리는 앞 조건에 제시된 값으로 채우고, 맨 우측 범위(최대값)가 지정되지 않은 경우는 임의로 10^100 을 부여했다. 설마 점수가 10^100을 초과한다면... 엑셀 도움말에 따르면 엑셀에서 최대로 사용할 수 있는 값은 9E+308이니 이를 적용하면 되겠..

더하기를 갖고 많이도 우려먹었다. 엑사모에 올라온 질문을 갖고 또 우려먹으련다. 얘기는 대충 이렇다. 제품별로 판매량이 날짜별로 집계되는데... 각각의 제품별을 구성하는 부품도 덩달아 몇개나 출고 됐는지를 집계하고 잡다는 것. '단순 더하기'의 수식을 원했다면, 아마도 질문 자체를 않았겠지? b10 셀 (단순 더하기) =$F3*B$3+$G3*B$4+$H3*B$5 그래서 좀 봤다. 참조하는 '제품별 부품구성' 표를... 훑어보니 제품과 부품의 배치 순서가 동일하다??? MMult 함수가 딱이다. f10 셀 (MMult 더하기(순서:동일)) =MMULT($F3:$H3,B$3:B$5) 그러나 순서가 동일하길 바라는건 욕심일게다. 아니나 다를까, 순서가 동일하지 않는 경우를 되묻던데... 이 경우에도 MMult..

음... 그닥 반갑지 않다. 그러나 이를 알면서도 블로그까지 찾아와 물어본 심정, 십분 이해는 된다. 그리고 내가 저지른 원죄(?)도 있고... 아는 만큼만 처리해 봤다. 일용직 노동자 임금을 시급으로 계산한단다. 그러나 임금 적용 방식은 너무나 다양해 통일된 처리 방법을 찾긴 어렵다. 따라서 각자 알맞게 적용해야 한다. 그런데 예전에 작성한 예제를 보니... 뭔 생각을 했는지, 아주 제한된 처리만 반영하고 있었다. 즉 경우에 따라 맞고 틀리고... 미친년 널을 뛴다. -_-;; 그래서 다시 구성해 봤다. 이번엔 허접한 재주가 티나지 않았으면 좋겠는데... 근무 조건은 대충 아래와 같다. 정상근무 시간대와 휴식 시간대 및 야근 시간대 그리고 특별수당. 휴식 시간이 왜 정상근무 시간에 포함되지 않는게 관례..