- 다양한 주제에 대해 자유롭게 글을 작성하는 게시판입니다.
Date 18/09/23 15:38:05수정됨
Name   Iwanna
File #1   #특별편,_함수만으로_추출하기.xlsx (13.0 KB), Download : 52
File #2   #2_커플_공강표_만들기.xlsx (14.5 KB), Download : 28
Subject   [엑셀월드] #2. 커플공강표 만들기




이전 강의

#1. 체크리스트 만들기 https://kongcha.net/pb/pb.php?id=free&no=8229&category=3
"아아, 이건 조건부서식이라고 한다."



엑셀월드 #2. 커플공강표 만들기

#공강표
#벤치마킹
#데이터베이스
#countifs함수
#애자일









1.문제상황

두근두근 캠퍼스 커플이 된 A모씨. 그는 여친과 공강을 맞춰보는 재미에 푹 빠져 있습니다. 그런데 두 시간표를 번갈아 보다보니 조금 불편한 감이 없잖아 있네요. 둘의 시간을 한 눈에 동시에 볼 수 있는 방법이 없을까요? 덤으로 공강사이에 만날 시간을 넣어두는 것도 좋을 것 같습니다!




2.문제해결과 벤치마킹

제 주변에는 개발자들이 몇 있습니다. 그 중에는 개발에 좀 빠져서 저보고 개발을 배워보라고 하는 친구들도 있었지요. 그러면 저는 되물었죠.

“왜 굳이 해야 돼?”

사실 진지한 이유는 저도 예상하고 있었습니다. IT 업종에 진출하려면 익숙해 져야 한다. 혹은 체계적인 문제해결능력을 기를 수 있다. 그 친구가 평소에 하던 말이기도 했구요.

그러자 그 녀석이 말하더군요.

“앱 청첩장을 직접 만들 수 있거든요.”

저는 우스개 소리로 듣고 넘겼습니다. 그 뒤로 개발자가 되지도 않았지요. 그러나 엑셀을 접하고, VBA를 하면서 조금이나마 ‘개발맛’이라도 보니 그 친구가 말하고 싶었던 게 무엇인지 조금이나마 알게 되었습니다.

#1에서 저는 엑셀이란 문제해결을 위한 도구라고 말했습니다. 그러므로 우리가 엑셀을 배울 때도 단축키 하나, 서식 하나 공부하듯이 배우면 안 된다고도 말했죠. 크건 작건 자신만의 문제를 설정하고 해결해나야만 제대로 실력을 늘릴 수 있다구요.

그리고 가장 쉽게 할 수 있는 문제해결은, 내 나름대로 무언가를 만들어서 나나 주변의 요구를 충족시키는 것입니다. 다만 그 과정에서 헤매기 쉬우므로 벤치마킹도 많이 해야 되구요.



나만의 뭔가를 만들다보면 히든 테크트리를 탄다?
*출처: 로크미디어

또 하나, 문제를 해결할 때 중요한 요소는 벤치마킹이죠. 문제해결과 벤치마킹, 이 두 개 개념만 잘 이해하셔도 문송한 사람이 작성한 칼럼 필요 없을 겁니다, 스스로 자기에게 부족하고 또 필요한 부분을 알고, 프로젝트를 설정해서, 해결하며 실력을 쌓아나갈 수 있거든요.



3.문제해결
1)구글링으로 참고할 만한 것들을 찾는다
먼저 구글링으로 참고할 만한 자료들을 찾아봅니다.



A가 구상했던 모습이 딱히 보이지는 않는군요.



그래도 시간표 어플이기에 기본 이미지는 떠오릅니다. 디자인은 '에브리타임'에서 제공하는 이미지가 마음에 듭니다. 예시 이미지를 하나 구해서 참고 대상으로 삼습니다.



2)단계를 설정한다


일단 기존의 시간표 앱처럼 만들고, 내가 벤치마킹한 디자인을 끼얹는 것으로 하겠습니다.



A는 인간의 딴짓 집중력이 최대치로 차오르는 수업시간을 이용해(..) 나름대로 단계와 내용을 구상합니다.
        저기에 나와있는 핵심 부분은 다음과 같습니다.

(1)시간표 데이터 부분과 시간표 표시 부분을 따로 분리해서 안정성을 높인다.
(2)서로의 수업 시간표를 표시하고, 그 주의 둘 만의 일정을 반영하는 항목도 만든다.
(3)다른 건 몰라도 여친과 볼 디자인은 직관적으로 알아볼 수 있게, 깔끔하게 만든다.




3)작성 시작!



시트 하나를 선택해서 기본적인 시간표 데이터를 넣어줍니다. 나름 데이터베이스 원리를 살린 방식이기도 하고, 엑셀을 쓰다보면 본능적으로 Table 형태가 가장 편하다는 걸 느낄 수 있을 겁니다.



기본적인 시간표 틀을 잡아주구요.



디자인 벤치마킹을 참조하면서 선, 색, 크기 느낌을 잡아갑니다.
참고를 하면 할수록 나름 괜찮은 디자인이 나온다는 걸 느끼실 겁니다.



countifs 함수로 데이터베이스의 내용을 시간표 화면과 연동합니다.
0은 그 누구의 시간도 들어가지 않은 곳, 1은 둘 중 하나의 시간만 들어간 곳, 2는 둘 모두의 시간이 들어간 곳이 됩니다.


*ifs가 들어간 함수는 하나의 Table안에서, 여러 조건을 동시에 만족시키는 값을 합하거나(sumifs), 하나씩 셉니다(countifs)




(1)=countifs(                        )
=표시는 함수를 사용하겠다는 의미입니다.
countifs라는 이름의 함수를 사용하고, 조건은 (             ) 괄호 안에 넣습니다. 중고등학교 때 배웠던 함수의 개념을 생각하면 편합니다.

(2)countifs(     criteria_range1        ,           criteria1           ,              criteria_range2          ,            criteria2            ,           ...           )

countifs에서 criteria_range1는 내가 세고 싶은 첫 번째 조건 범위입니다.
criteria1에 있는 값과 동일한 값만 셉니다.(나중에 부등식 조건 등을 추가할 수도 있습니다)

range로 세고 싶은 범위를 정하고, criteria에 맞는 조건을 세고... 하는 식으로 계속 더해나갈 수 있습니다.
단, 각 range는 하나의 데이터 테이블에서 일정한 높이로 맞춰줘야 오류가 안 날 겁니다.


(3)데이터베이스!$B:$B (데이터베이스!$C:$C)
데이터베이스는 제가 앞서 시간표 데이터를 저장해 둔 시트 이름입니다.
데이터베이스! 라고 하면 데이터베이스 시트를 참조한다는 의미이지요.

B:B라고 하면 B열 전체를 범위로 정했다는 의미이며, 숫자가 붙지 않는 이유도 전체 열을 지정했기 때문입니다.
$B:$B라고 하면 수식의 위치가 바뀌거나, 자동 채워넣기를 해도 해당 값의 위치는 바꾸지 않는다는 절대참조의 의미입니다.
해당 열은 수업이 몇 교시인지 지정했었죠?

criteria_range2 칸에 적었던 내용도 C열을 절대참조 했을 뿐 의미는 똑같습니다.

(4)시간표!$A5, 시간표!E$1
criteria란에는 참조할 값을 넣어둡니다.
시간표!는 지금 시간표를 만들고 있는 시트의 값을 참조한다는 의미입니다. $A5는 A열을 고정시킨 채 값을 참조하는데, 교시가 적혀있군요.
마찬가지로 E$1에는 요일이 적혀 있습니다. 1행에 고정시킨 채 값을 참조합니다. 해당 수식을 행을 바꿔가며(즉, 세로로) 채우면 변하지 않지만, 해당 수식을 열을 바꿔가며(즉, 가로로) 채우면 D$1, E$1, F$1 이런 식으로 변합니다.






자 이제 센스가 조금 있다면 여기에 저번 강의(#1)에서 말했던 조건부 서식을 적용시키면 시간표가 완성되리라는 걸 예상할 수 있을겁니다.
시간표의 경우는 체크리스트와 달리 데이터 부분은 지워야겠죠..
채우기 색과 동일한 색으로 설정하면 글자가 사라지는 걸 볼 수 있습니다. 데이터부분을 살짝 가리는 방식 중 하나죠.

그런데...



           4)에자일

           가만히 보면 해당 수식은 문제가 있습니다.
(1)A의 시간표와 여친의 시간표가 구분되지 않는다
(2)여러 항목을 추가시키면 오류가 뜰 확률이 늘어난다.




따라서 다음과 같이 추가합니다
(1)조건을 하나 더 붙여서 "나"와 "소리"를 따로 검색한다
(2)각각의 경우 다른 가중치를 부여한다(소리 1, 나 100, 기타 1000)
화면을 보면 뚜렷하게 구분이 되는 것을 볼 수 있습니다.




조건부 서식을 적용해고, 예쁘게 색을 내보았지만 또 만족스럽진 못합니다.

(1)직관성이 조금 떨어집니다.





따라서 둘이 겹치는 시간대에는 무늬색을 활용하여 적용시킵니다.
이 경우 글씨를 배경색과 똑같이 할 수 없기에 테이블을 옆에 배치하구요. 대신에 조건부서식에서 옆에 떨어진 값을 참조시키면 됩니다.



이런식으로 아무리 작은 프로그래밍이더라도, 중간중간에 기획과 다르게 흘러갑니다.
이런 과정에서 더 좋은 화면, 더 잘 돌아가는 기능을 구현하기 위해
디자인, 혹은 데이터 구조, 심지어는 기획단계에 해당하는 내용을 바꾸는 일을 해야 하는데요.
이런 과정을 애자일(Agile)이라고 한다고 합니다.





이런 과정 끝에 깔끔하게 시간표를 뽑았고, A는 여친에게 보내봤다고 합니다.
(참고로 엑셀에 원하는 부분을 복사해서 pc카톡에 붙이면 그림 형태로 깔끔하게 보내지더군요)
여러분도 엑셀로 아기자기하게 만들어보면서 실력을 늘려보면 어떨까요?



4


    홍차넷 특)
    1. 커플이 잘 없다.
    2. 학생은 거의 없다...... 크흡.....

    +) 엑셀 강의는 잘 보았습니다. ㅎㅎ
    뜻밖의 학밍아웃...

    감사합니다 흐흐
    호라타래
    사고의 흐름을 보여주는 이런 작업 좋아요 ㅎㅎ 근데 중요한 조건을 체크하지 않으셨군요 ㅠㅠ 커플 시간표라니...
    언젠가 if문을 설명하는 사례로 보여주는로...
    April_fool
    센세, 다음에는 https://redtea.kr/pb/pb.php?id=qna&no=5476 이거를 VBA 없이 해결하는 법을 강의하는 것은 어떻겠습니까? 저는 VBA 없이는 저거 하는 방법을 모르겠는뎁쇼.
    쌉고수님 저에게 왜 이런 시련을....
    한 번 검토해 주시지요 ㅜㅜ 좀 지저분하게 되긴 했는데
    본문에 첨부했습니다!

    1.INDEX 함수 및 여타 함수 조합으로 데이터를 일렬로 만들기

    2.맨 왼쪽 자리가 A인지 확인하기
    2-1.LEFT함수로 맨 왼쪽 문자 추출하기
    2-2.EXACT와 UPPER의 조합으로 소문자 대문자 구분하기
    2-3.IF함수로 걸러내기

    3.전체 길이가 9인지 판별하기
    3-1.LEN 함수와 IF함수 조합

    4.왼쪽 8자리가 숫자인지 판별하기
    4-1.RIGHT로 8자리 추출(8자리 미만이면 전체 추출하지만 3 조건이 있기에 상관... 더 보기
    한 번 검토해 주시지요 ㅜㅜ 좀 지저분하게 되긴 했는데
    본문에 첨부했습니다!

    1.INDEX 함수 및 여타 함수 조합으로 데이터를 일렬로 만들기

    2.맨 왼쪽 자리가 A인지 확인하기
    2-1.LEFT함수로 맨 왼쪽 문자 추출하기
    2-2.EXACT와 UPPER의 조합으로 소문자 대문자 구분하기
    2-3.IF함수로 걸러내기

    3.전체 길이가 9인지 판별하기
    3-1.LEN 함수와 IF함수 조합

    4.왼쪽 8자리가 숫자인지 판별하기
    4-1.RIGHT로 8자리 추출(8자리 미만이면 전체 추출하지만 3 조건이 있기에 상관없음)
    4-2.VALUE함수로 숫자인지 파악 / ISERROR함수로 에러 걸러내기

    5.2, 3, 4의 조합을 전부 만족시키는지 판단하기.
    5-1.Concatenate 함수로 여러 조건들 연결하고, IF로 판별하기

    저렇게 만든 뒤에 필터를 써서 최종 결과가 만족하는 경우만 복붙하면 될 것 같네요.
    April_fool
    역시 논리 자체는 뭐 비슷한 것 같네요. 저 같으면 맨 먼저 전체 길이부터 확인할 것 같지만요. 엑셀 함수 중에서 정규 표현식 혹은 VBA의 Like 패턴매칭을 지원하는 게 있으면 가장 좋았겠지만, 현 시점에서는 (제가 알기로는) 엑셀 내에서 정규 표현식을 쓰려면 VBA를 쓰는 수밖에 없지요.

    근데 가장 큰 문제는, 저는 1번이나 맨 마지막 부분을 엑셀 내장 함수로 어찌 해야 할지 잘 모른다는 점입니다. 엑셀알못이라…
    1
    패턴 매칭도 어떻게 되긴 할겁니다. ㅜㅜ 제가 모를 뿐이죠. 밑천 다 탄로나네요

    마지막은 어거지로 했는데, 1번의 핵심은

    핵심은 3줄에 걸친 값을 한 줄로 만들었을 때,
    열번째 값은 원래 테이블의 1열 10행의 값이고
    열한번 째 값은 원래 테이블의 2열 1행의 값이 된다는 점입니다.
    index함수는 행과 열의 번호를 넣어주면 해당 테이블에서 값을 돌려주는 함수구요.

    즉, 나누기의 몫과 나머지를 요리조리 잘 주물러주고, index를 쓰면 어떻게든 뽑아낼 수 있다는 거죠.
    1
    김치찌개
    오 잘 보고 있습니다~
    저도 유머글 잘 보고 있슴다!!
    Cascade
    그냥 상대 시간표를 외우면 되는 거 아닙니까
    원래 이런 짓을 하다 보면 더 잘 외워지죠 (당당)
    저는 롱디라서 시간표가 쓸모없었다고 합니다...
    Crimson
    똑같이 따라해보고 있는데 데이터베이스 시트가 일부만 나와있어서

    파일을 다운받으려니 엑셀파일이 다운이 안되네요 ㅜ
    아.... 조만간 고쳐서 새로 올리겠습니다 : ( 주말에 조금 바빴어요.
    번거롭지만 그래도 고민하시다가 제가 올린 거 참조하시면 그만큼 실력이 늘 거에요!!
    따라하고 계시다니 제 강의를 120% 쓰시려는 것 같아 기쁘네요 ㅎㅎ
    늦어서 죄송합니다 ㅜㅜ 테스트를 해봤는데 아마 될 것 같습니다?? 확인해주세요!!
    Crimson
    네 잘 되네요 감사합니다!!
    목록
    번호 제목 이름 날짜 조회 추천
    9750 꿀팁/강좌(펌) 이사 준비할때 꿀팁 10개 모음 2 바보의결탁 19/10/01 6379 5
    9736 꿀팁/강좌유용한 생활꿀팁 40가지 모음 6 바보의결탁 19/09/29 4756 2
    9543 꿀팁/강좌영어 공부도 하고, 고 퀄리티의 기사도 보고 싶으시다면... 8 Jerry 19/08/14 6237 20
    9300 꿀팁/강좌[엑셀월드] #6. vlookup, countifs, sumifs 2 Iwanna 19/06/11 7785 4
    9146 꿀팁/강좌휴일 주말 어린이대공원, 상상나라 주차 어려울 때 11 토비 19/05/01 8273 7
    9049 꿀팁/강좌자동차 보험 갱신할 때 T맵 운전점수 할인 받기 19 토비 19/04/08 7713 0
    8788 꿀팁/강좌영어권 현지 방송을 들어보자 - inner circle편 2 Darker-circle 19/01/22 6134 7
    8730 꿀팁/강좌서울과 도쿄의 물가 전격비교 영상 1 19/01/05 5719 0
    8687 꿀팁/강좌GIF 이미지 리사이즈 하기 4 토비 18/12/27 6001 5
    8656 꿀팁/강좌누끼 자동으로 따주는 사이트 17 토비 18/12/19 47904 6
    8648 꿀팁/강좌영어의 격은 누가 줄까요? - Case assigner 5 DarkcircleX 18/12/18 6104 4
    8572 꿀팁/강좌지금 쓰는 안경에만 돈을 75만원씩 퍼부은 사람이 알려주는 안경 렌즈 선택의 거의 모든 것 19 April_fool 18/11/28 56871 42
    8560 꿀팁/강좌쉐이빙폼의 대체자를 찾아보자. 18 danielbard 18/11/24 6228 8
    8531 꿀팁/강좌' 5 18/11/16 5011 0
    8466 꿀팁/강좌홍차넷을 여행하는 히치하이커를 위한 안내서 20 Cascade 18/11/04 5985 21
    8382 꿀팁/강좌[엑셀월드] #5. 공부 타이머 + 체크리스트(1) 10 Iwanna 18/10/16 10535 10
    8371 꿀팁/강좌[엑셀월드] #4. 데이터를 요약보고하기(1) 6 Iwanna 18/10/14 7651 5
    8333 꿀팁/강좌[엑셀월드] #3. 함수만으로 데이터 추출하기 11 Iwanna 18/10/06 10998 7
    8267 꿀팁/강좌[엑셀월드] #2. 커플공강표 만들기 18 Iwanna 18/09/23 10023 4
    8256 꿀팁/강좌다른 사람 기분 나쁘지 않게 조언하는 화법 6 라밤바바밤바 18/09/21 7529 7
    8229 꿀팁/강좌[엑셀월드] #1. 체크리스트 만들기 12 Iwanna 18/09/14 16978 6
    8212 꿀팁/강좌직장인 여성 옷 나눔합니다 37 다람쥐 18/09/12 7322 23
    8197 꿀팁/강좌알쓸재수: 자연수는 무한할까? 26 기쁨평안 18/09/10 5982 15
    8103 꿀팁/강좌능동태, 수동태, 그리고 하나 더 - Ergative 3 DarkcircleX 18/08/23 5238 6
    8102 꿀팁/강좌영어 의미역 (Thematic Roles) 1 DarkcircleX 18/08/23 7460 4
    목록

    + : 최근 2시간내에 달린 댓글
    + : 최근 4시간내에 달린 댓글

    댓글