엑셀에서 524 계산에 필수가 되는 수식

이과다 이과가 나타낫다 ! 

저는 제가 이과인지 문과인지 모르겠어요 ㅋㅋㅋ 분류하는거 애매한거 같아요. 히고님은 빼박 문과?

이과 출신들이 오히려 업무에 엑셀을 잘 안써요..

저는 지난 20몇년동안 대학원/회사에서 엑셀 한번도 안써봤다는...

그쵸 ㅋㅋㅋ 엑셀은 문과의 전유물

저 이과인데.. 회사에선 워드대신 엑셀 써요;;;

1. staffing schedule

2. issue tracker

3. step-by-step instruction/checklist... 

역시 문과적 업무에만 쓰시네요 ㅋㅋㅋ

아 맞네요 

저도 액셀만 쓰네요 문관데 ㅋㅋ

근데 저 펑션은 첨 봐요 

맨날 썸만 타고 평균만 

그냥 귀찮으면 엑셀로 다 돌리는 겁니다....... 심지어 단순계산조차도 엑셀 ㅋㅋㅋ

펑했습니다 죄송합니다.

오마나 저도 개인 상담 들어가야 하나요 처너형님!! 잘 모르는 오메에게 한수 부탁드리옵니다 꾸벅

펑했습니다 죄송합니다.

비즈가 많은데도 생각해보니 그렇네요? 플레이어 없는 싱글같은 2인 가족이라 그런가붑니다. 이제 달리지 말아야 하는데 호호.(ㅠ_ㅠ)

저는 쉬트 하나 새로 만들어서 현 상황 오버뷰를 정리해놨습니다.

예를 들면, 플레이어별로 24개월 내에 만든 카드 개수, 12개월 내에 만든 카드 개수, 총 연회비, 총 크레딧리밋, 카드사별 크레딧리밋, 카드사별 스페셜룰에 맞춰서 걸리는 게 없는지 보는 부분도 만들어놨고요.

예를 들어 체이스 5/24 상황을 보기 위한 부분은 수식이 좀 복잡하긴 하지만 이런 식입니다...

다소 복잡해보이긴 하지만 요약을 하자면

1. 플레이어별로

2. 승인날짜가 24개월 미만인 카드가 5개 이상인지 체크

3. 5개 이상이면 최근 5번째 카드 승인날짜로부터 24개월 되는 날짜를 표기

4. 아니면 오늘 날짜를 표기

이렇게 하면 5/24에 걸리는 카드가 몇 개인지 볼 필요도 없이 5/24가 풀리는 날짜를 알려줍니다.

물론 저는 현재 체이스와 아멕스만 파고 있기 때문에 비즈니스 어카운트만 제외하는 조건을 붙였지만 카드를 늘려갈수록 시티와 캐피탈원 등은 비즈니스 어카운트도 포함하도록 조건을 업데이트 해야하긴 합니다. 아직 먼 훗날의 이야기라서 일단은 저렇게 뒀습니다. ㅎㅎ

EDIT: 생각난 김에 데이터 쉬트에 5/24 적용 여부 칼럼(승인 24개월 이내, 비즈 제외, 아멕스/체이스 외의 비즈 포함, 프로덕트 체인지 제외)을 만들고 거기서 계산하는 걸로 바꿨습니다. 수정하다보니 위에 스샷 걸어놓은 수식에 오류가 있더라고요. ㅎㅎ

펑했습니다 죄송합니다.

제가 그다지 공격적으로 달리지 않기 때문에 그렇게 디테일한 룰들을 다 적용시키진 않았습니다. 복잡해지니까요~ ㅎㅎ 그냥 말그대로 현재 생황을 오버뷰 하기 위해 만든 쉬트라서 보고 날짜가 간당간당한 상황이면 데이터 쉬트로 가서 최근 기록 슬슬 살펴봐야죠!

앗 그런데 체이스가 EX를 풀하는 동네면 어떤 부분이 달라지는 건가요? 하나 또 배울 게 생겼나본데요?!

펑했습니다 죄송합니다.

아하! 어쩐지 EX 리포트엔 오픈 날짜가 다 1일로 뜨더라고요! ㅎㅎ 그냥 음? 하고 대수롭지 않게 넘어갔던 기억이 있네요. 그런 건 그냥 안전빵으로 원래 날짜에 맞춰서 하는 게 좋을 것 같습니다. 적당히 굵고 적당히 길게 ㅋㅋ

아하 이게 이런말씀이었군요. 형님 자세한 설명 감사드려요 홍홍

펑했습니다 죄송합니다.

그때부터 허리가 아파서 제정신이 아니였죠 ㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠ

키야 죽음인데요!! 저도 이 수식좀 적어두고 연구좀 해서 효율적으로 쓸라고요.

저것은 그냥 수식이지만 지금 하이라이트 옵션을 더해서 잊지 않게끄름 하는 걸 생각중입니다. (필터라고들 하지요.)

요런거. 그래서 단순 날짜만 적어도 하이라이트 되게 하는건데......... 이건 좀 어렵네요

중국어가 쓰여있는데 크레오메님 쉬트인줄 알고 깜짝 놀랐네요 ㅎㅎㅎㅎ

Conditional Formating 사용하시면 됩니다. 위에 사용 중이신 그 수식을 가지고 24개월 미만이면 빨간색으로 칠하고 24개월 지났으면 초록색으로 칠해줘 이런 식으로 설정해 놓으면 됩니다.

Filter는 그거랑은 살짝 다르게 아예 보여주고 숨기고 하는 설정입니다.

제 경우엔 데이터 쉬트에 Archived 항목을 넣어서 승인된 지 27개월(24+3개월 버퍼)이 지나고, 클로즈된 어카운트를 필터해서 숨겨버리는 설정을 사용 중 입니다.

이렇게 크레오메님 3개국어 사용자이신게 딱 걸리신 겁니다~

하루에 10번도 넘게 보는 제가 쓰는 쉬트 맞고요 ㅋㅋ 중국어인지는 상상에 맞기겠습니다.

네 그게 필터가 딱 지정을 해줘야 하는데 제가 그냥 복붙해서 쓰는 엑셀정도의 실력이라 창의력인 상상이참...ㅠㅠ 

일단 해보고 대댓글로 모르면 질문해도 될까요오오오?!?

일본어 일수도..

그럼요 질문 언제든 환영입니다. 전 구글 쉬츠 쓰고 있긴 한데 수식이나 뭐나 얼추 비슷할 거에요. 

제가 conditional format 말씀드리려 했는데 벌써 나왔네요. 조건 잘 만들어 놓으면 자동 계산해서 색깔 막 바꿔놓으면 보기가 편해요.

네 저도 그 기능을 마스터하려고 노력중입니다 흐흐흐 감사해요 makeawish 님. 곧 꿈은 이뤄지시겠어요!!

와 그나저나 작년 11월 한 달 동안만 카드 6개요?? 대단하십니다!

6개는 아니고 엄밀히는 두개입니다. 비즈1개 개인1개 다운4개 ......!! 날짜의 순서대로 적다보니 저렇게...

아하! 프로덕트 체인지도 따로 기록을 해두셔서 그런 거였군요. ㅋㅋ 자세히 안 봐서 라인 하나당 카드 추가인 줄 알았습니다~ 저도 아직 프로덕트 체인지 할 일이 없었는데 앞으로 그걸 어떻게 효율적으로 기록할지 고민해 봐야겠네요!

작년에는 3..아니 4천불 가까이 냈던거 같은데 많이 줄였습니다. 500불대 큰 카드가 몇개만 있어도 뭉텅뭉텅 나가는거 같아요!

제가, 그냥 식을 써보니까... now()를 그냥 쓰게 되면, 달 계산이 조금 애매해지더라고요.. 예를들어, 2019/01/15에 카드를 열었으면, 2019/02/28이 1달인건 OK인데, 2019/03/01에는 2달로 치고싶거든요. 그런데, 2019/03/14까지도 1달로 계산되고, 2019/03/15이 되어서야 2달로 계산됩니다.

이걸 막기 위해서 NOW()을 DATE(YEAR(NOW()),MONTH(NOW())+1,0) 으로 치환하는 것을 추천합니다.

아이고, 이것도 완전하지 않네요.. ㅠㅠ 좀 깔끔하게 계산할수없을까요

주신 예에서 3/15가 돼야 2달로 계산하는 게 맞지 않나요? 

굳이 그런 계산을 꼭 원하신다면 DATEDIF 수식 쓰실 때 비교 날짜에 15를 더해주시면 Round up이 됩니다. 해당 수식 디폴트가 Round down 이기 때문에 지금 보시는 결과가 나오는 거거든요. 

DATEDIF( 승인날짜, TODAY() + 15, “m” )

오 datedif!!  진짜 같이 가는 수식 넘나 많네요 ㅎㅎㅎ

그래도 으리으리님처럼 analyzing 하는거 엄청 중요하다고 생각해요. 제가 발견한 수식인데 다른 고수분들은 더 깔끔하게 하지 않을까 싶어 이 글을 파봤어요. 대댓글을... 기대하고 있습니다 ㅎㅎㅎ

아, 확실한 방법을 찾았습니다.

오픈날짜가 K29셀, 오늘 날짜가 NOW()이라고 한다면,

오픈날짜가 있는 달의 1번째 날: DATE(YEAR(K29),MONTH(K29),1)

오늘날짜가 있는 달의 1번째 날: DATE(YEAR(NOW()),MONTH(NOW()),1)

이렇게 하면, 이번달이 오픈날짜로부터 몇 달이 걸렸는지 다음과 같이 계산합니다.

DATEDIF(DATE(YEAR(K29),MONTH(K29),1),DATE(YEAR(NOW()),MONTH(NOW()),1),"m")

이렇게 오늘 날짜 기준으로, 각 카드의 현재 달 수를 다음과 같이 구했다고 해봅시다 (i.e., $M$8:$M$29).

그리고 5/24 테스트하고싶은 날짜가 지금으로부터 "A1" 달 이후라면,

=COUNTIF($M$8:$M$29,CONCAT("<=",TEXT(24-A1,0)))

하면, 테스트하고싶은 날짜 기준으로, 24개월 내에 만든 카드 갯수가 나오네요.

아..조잡흐다...ㅠ

위에 댓글 못 보셨나봐요 ㅠㅠ

DATEDIF(start_date, TODAY()+15, "M")

으로 하시면 승인날짜와 오늘 날짜를 비교하되 달수를 Round up 해서 이번 달 1일을 기준으로 달수 차이를 계산합니다.

이 수식 쓰시면 원하시는 대로 1월 15일에 만든 카드 3월 1일에 2달 지난 걸로 계산합니다.

음... 날짜15일 더하기는 좀 애매한 것 같은데요.. 예를들어2000년1월1일에 카드 새로 만들면, 2000년2월13일까지는 1달 지난걸로, 2000년2월14일부터는2달지난걸루계산되어요.

아하~ 제가 첫번째 예제만 두고 생각을 해서 다른 경우를 생각 안 했습니다. ㅎㅎ Day를 아예 무시하고 Month로만 계산을 하고 싶으셨나봐요.

넵ㅎㅎ

전 그냥 간단히 

if(datedif(D12,today(),"Y")<2,"Yes","") 그냥 만 2년이 안되었다 싶으면 'Yes'. 그래서 위에 Total에서 Yes만 세요. 뭐 날짜까지 새는건 좀 무리다 싶어서요.

저는 먼 옛날부터 5/24는 배제하고 하고있어요! 

티메동생 나도 그렇다네........요 하지만 당분간은 장기 동굴이라 그냥 슬프다 겸 올렸어요!!

89년생 26세 크레오메!

누나라매!

ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ

저는 제가 89년생이라고 말씀 안드렸는디요? (방년 30세가 그립낟.........)

결론은 

웰컴투 동굴입니다 

인사해주셔서 정말정말 감사하옵니다

동굴생활 가즈아!!

이걸 효율적으로 관리하는 방법을 고민 중인데, 엑셀만 갖고는 쉽지 않을 것  같아요. 뭐 이것저것 해서 되긴 하겠지만 깔끔하지 않겠지요.

product change, annual fee waive, retention bonus 등을 위해서는 카드별로 일종의 lifecycle을 기록해야 하잖아요. 데이터가 longitudinal data 형태를 갖는 건데. 엑셀로 이걸 어떻게 깔끔하고 보기 편하게 다룰 수 있을까요. long type panel data처럼 기록하는 원자료 시트와 그걸 요약하는 시트를 분리하는 방법 정도?

펑했습니다 죄송합니다.

ㅋㅋㅋㅋㅋㅋㅋㅋㅋ 저도 한 14개정도까진 그랬던거 같은데 작년 리츠달리기 시작하면서 버거워졌습니다ㅠㅠㅠㅠㅠㅠㅠㅠㅠ

제가 그런 컨셉으로 하고 있는 거긴 한데 요약 쉬트가 턱없이 간료화 되어 있습니다 ㅎㅎ

펑했습니다 죄송합니다.

네 윤도현밴드이고 싶지만 운도형밴드에 머물고 있는 컨셉입니다. 

아마 아래 전문가님들이 대댓글을 달아주시리라 믿어보며 초보 오메는 그냥 훨훨 바람만 불고 가옵니다. 히히 

연회비 $2213불 후덜덜....근데 오메님은 충분히 다 뽑아먹으실것 같아요 ^^ 

정모하느라 다 뽑아먹었어요 ^^ ㅎㅎㅎㅎㅎㅎ

그렇게 해서 마지막에 (C 가 Elapsed month 컬럼이라고 치면)

=COUNTIF(C1:C29, "<=24")

하면 깔끔하게 최근 24개월 안에 만든 카드 갯수가 딱 나오네요.

이런 깔끔한 한줄정리 감사합니다 맥주한잔님!

그런데 슬프게도 제가 이미 퇴근해버렸으므로 내일 회사가서 다시 한번 째려보고 적용해보겠습니다(........)

엇 저도 이전부터 엑셀에 비슷한걸 만들어 놨었는데요 ㅎㅎ

저는 특정날짜에서 최근 24개월동안 오픈된 어카운트 개수를 카운트 해 주는 방식으로요.. (countif 문을 썼습니다)

그리고 그 특정 날짜를 1개월 단위로 만들어서 아래로 쭉 긁어서 반복시켜 놓았죠.

언제 5/24 아래로 떨어지는지도 볼 수 있게요.

근데 전 이게 하나도 안 유용합니다 ㅠㅜ

5/24 아래로 떨어지는 시점은, 제가 이 엑셀 파일을 언제 열어보든 상관없이 항상 거의 2년 후 (최소한 1년 반 후) 로 뜨네요 ㅜㅜ

엑셀 고수님들이 많으시니... 저는 논의 끝나는 시점에서

최적화된 포멧 2-3개 정도 받아서 마모 뉴비킷만 만들면 되는거죠!?

쓰시다 남는 시트들 제게 버려주시면 잘 재활용해보겠습니다 ㅎㅎㅎ

+1

저랑 같은 생각을 하고 계셨네요ㅋ

+2

그래서 제가 이 글을 썼어요 ㅎㅎㅎ

+3.  아주 좋은 생각이십니다. 뉴비킷은 뉴비들과 쉐어하실거죠?!!!!