엑셀 증감 표시
[빨강]"▲"#,##0;[파랑]"▼"#,##0;G/표준
[빨강]"▲"#,###,,;[파랑]"▼"#,###,,
단위 : 백만원
[빨강]##,##"▲"#,##0.0%;[파랑]###"▼"###0.0%
단위 : %
2012년 5월 21일 월요일
2012년 5월 18일 금요일
엑셀, VLOOKUP함수 사용법, INDEX함수와 MATCH함수 - 찾기,참조함수
엑셀로 데이터 작업을 할 때, 가장 많이 사용하는 함수중의 하나가 VLOOKUP함수일 것이다.
VLOOKUP함수는 그만큼 효과적이며 사용하기 쉽다.
어떤 백데이터가 있고 여기서 조건에 맞는 데이터를 찾아서 가져와 다른 작업을 하고자 할 때 바로 이 VLOOKUP함수가 쓰인다. LOOKUP의 뜻 그대로 주욱 살펴보는 함수이며 V는 VERTICAL의 머릿글자다. 대부분 데이터가 세로로 나열되어 있으므로 이것을 쓴다. 가로로 데이터를 검색할때는 HLOOKUP함수를 쓴다.
1. VLOOKUP함수 사용법
예제) 아래는 중간,기말성적표이다. 학생별로 중간,기말고사의 평균점수를 작성하라.
▶ VLOOKUP함수 사용법
VLOOKUP(찾을값 혹은 기준값, 찾을범위, 열번호, 옵션)
VLOOKUP함수는 찾을 범위에서 찾을값에 해당하는 행을 찾고 그 행에서 열번호 위치에 해당하는 셀을 참조하여 셀값을 반환하게 된다.
<B14>셀을 작성하기 위해 우선 "이대호"의 중간고사 국어성적을 찾아본다.
찾을값 : $A14 (여기서는 성명을 기준으로 일치하는 행을 찾음)
찾을 범위 : $A$3:$K$10 (성명에 해당하는 열이 첫번째 열이 되도록 하고, 중간기말성적의 데이터 범위를 포함하도록 지정하면 된다. 지정후 'F4'키를 한 번 눌러 절대참조 형태로 만든다. 채우기핸들을 사용하기 위함이다.),
열번호 : 2 (찾을범위 내에서 참조할 열이 몇번째 열에 위치하고 있는가를 지정)
옵션 : 0 (정확히 일치하는 값을 찾을때는 FALSE(0), 찾을값보다 작거나 같은 값일 때는 TRUE(1))
즉, 이대호의 중간고사 국어성적은
= VLOOKUP($A14,$A$3:$K$10,2,0)
을 하면 된다.
평균을 내야 하므로 중간, 기말을 각각 찾아 둘로 나누면 될 것이다.
B14 = (VLOOKUP($A14,$A$3:$K$10,2,0)+VLOOKUP($A14,$A$3:$K$10,7,0))/2
채우기 핸들을 이용하여 오른쪽으로 쭈욱 당기고, 또 아래로 쭈욱 당겨 나머지 셀들을 한꺼번에 구하도록 한다.
* 잊지 말아야 할 사항
(1) 찾을값이 포함된 열을 찾을범위의 첫열로 설정하여야 한다.
(2) 찾을범위는 보통 일정하게 정해지므로 절대참조로 묶어두고, 찾을값도 적절하게 혼합참조 형태로 하여야 채우기 핸들을 이용하여 나머지 셀을 쉽게 채울 수 있다.
2. INDEX함수와 MATCH함수
어떤 범위에서 행번호와 열번호를 이용하여 데이터를 가져오는 함수가 INDEX함수이며 보통 MATCH함수와 같이 쓰인다. (스타스키와 허치, 터너와 후치 같은 것이라 보면 된다.)
예제) 아래는 고객들의 저축액 표이다. 고객별로 이자를 구하라. 이자는 "저축액*이자율"로써 구하도록 한다.
(1) "저축액"은 위의 VLOOKUP함수를 이용하면 간단히 구할 수 있을 것이다.
<B16> "김정은" 고객의 저축액 = VLOOKUP(A16,$B$2:$D$12,3,FALSE)
(2) "이자율"은 은행별로 다르며 은행은 고객별로 설정되어 있으므로 우선 고객별 은행을 먼저 구하고 이후에 은행별 이자율을 구하도록 한다.
2-1) 고객별 은행은 고객의 왼쪽에 위치해 있으므로 그냥 VLOOKUP함수로는 찾을 수가 없다.
고객열(B열)의 고객 행위치가 은행열(A열)의 은행 행위치와 같으므로 이것을 (MATCH함수로 찾아) 이용하여 INDEX함수를 적용하도록 한다.
▶ INDEX,MATCH함수 사용법
INDEX(찾을 범위, 행위치, 열위치)
MATCH(찾을값 혹은 기준값, 찾을범위, 옵션)
INDEX함수로 <B16>셀에 해당하는 은행을 찾아보자.
찾을범위 : $A$2:$A$12 (은행열의 데이터를 지정하고 F4를 눌러 절대참조로 바꾼다.)
행위치 : MATCH(A16,$B$2:$B$12,0) (고객열에서 찾을 고객이 위치한 행을 구한다.)
- MATCH함수 인수 (찾을값 : A16 (찾고자 하는 고객명), 찾을범위 : $B$2:$B$12 (고객열 데이터), 옵션 : 0(정확한 값을 찾음))
열위치 : 1 (찾을범위의 열이 하나뿐이므로 그냥 1을 적는다.)
<B16> "김정은"고객의 은행 = INDEX($A$2:$A$12,MATCH(A16,$B$2:$B$12,0),1)
2-2) 은행명을 찾았으므로 이제 이것을 VLOOKUP함수의 인수로 하여 F2:G4 범위에서 이자율을 가져오도록 한다.
<B16> "김정은"고객의 이자율 = VLOOKUP("김정은"고객의 은행,$F$2:$G$4,2,FALSE)
= VLOOKUP(INDEX($A$2:$A$12,MATCH(A16,$B$2:$B$12,0),1),$F$2:$G$4,2,FALSE)
(3) 따라서, 최종적으로 구하는 B16셀의 식은
B16 = "김정은" 고객의 저축액 * "김정은"고객의 이자율
= VLOOKUP(A16,$B$2:$D$12,3,FALSE) *VLOOKUP(INDEX($A$2:$A$12,MATCH(A16,$B$2:$B$12,0),1),$F$2:$G$4,2,FALSE)
채우기 핸들을 더블클릭하면 아래의 셀들이 모두 채워진다.
* INDEX, MATCH 함수는 약간 까다롭지만 INDEX함수 사용법에 따라 차례로 인수를 대입하며, 찾을값의 행위치나 열위치를 MATCH함수로 찾는다는 것을 기억해두면 유용하게 써먹을 수 있다.
3. VLOOKUP함수 참고사항
- 찾고자 하는 값이 찾을범위에 없을 때는 #N/A 에러가 뜬다.
- VLOOKUP함수는 다른 시트의 데이터를 읽어올수도 있고, 다른 파일의 데이터를 읽어오는 것도 가능하다. 다만, 다른 파일의 데이터를 읽어오려면 그 파일도 열려있어야 한다. 안 그러면 #REF(참조 오류)가 뜬다.
- 또한, 여러조건에 해당하는 것을 찾을수는 없으며, 찾을값과 일치하거나 일정범위내에 있는 데이터만을 찾을 수 있다.
cf.)
* 여러조건에 해당하는 데이터들의 합 구하기 :
http://blog.naver.com/sejinssy/100133492345 참조
* 두가지 조건에 해당하는 행의 참조값 찾기
http://blog.naver.com/sejinssy/100152725456 참조
- 옵션값을 TRUE로 할 때는 찾을범위의 값들이 오름차순으로 일정하게 정렬이 되어 있어야 한다.
찾을값보다 작거나 같은 값을 순차적으로 찾기 때문이다. 데이터가 ~이상,~미만으로 정해진 표의 경우 이를 이용할 수 있다.
(예) 과세표를 참조하여 소득에 따른 과세 구하기
* VLOOKUP함수는 사용법이 비교적 간단하고, 백데이터에서 필요한 값을 찾아오는데 효과적이므로 여러 파생적인 작업을 하는데 유용하게 쓰인다. 엑셀 작업의 상당수가 데이터를 찾아오는 것임을 생각해보면 그 유용성을 짐작할 수 있다. 또한, INDEX, MATCH함수는 조금 까다롭기는 하지만 익혀두면 VLOOKUP함수보다 더 폭넓게 활용할 수 있다.)
[엑셀] 유효성 검사 (조건에 맞는 값만을 입력하기)
이원목적분류표에 점수를 수작업으로 기입해야하는데 입력실수로 계산이 틀린 경험을 많이 해 보셨을 겁니다. 예를들면, 1점에서 10점 사이의 점수만 입력해야하는데 3.3점이라고 기입할 것을 33점으로 기입하면 오류가 나겠지요. 아예 입력할때부터 10점 이상은 입력이 안되도록 설정하면 오류가 날 가능성이 줄지 않을까요?
오늘은 그런 기능을 하는 유효성검사를 알아보겠습니다.
먼저, 데이터를 입력할 부분을 선택합니다.
데이터—데이터도구—데이터유효성검사를 실행합니다.
빨간색원으로 표시한 역삼각형을 클릭하셔서 "소수점"을 선택하고, 제한방법, 최소값,최대값을 지정해 줍니다.
그리고나서 선택범위내의 셀에 값을 입력해 봅니다. 0.1과 10사이를 벗어나면 그림과 같은 오류메시지가 뜹니다.
입력범위를 숫자가 아닌 문자로 지정해 줄수도 있습니다. 마찬가지로, 입력할 범위를 선택하고
데이터유효성 검사를 실행하여, 창이 뜨면 제한대상을 "목록"으로, 원본에는 허용되는 문자들을 쉼표로 구분하여 입력합니다.
그러면 해당범위의 셀 옆에 역삼각형 표시가 뜹니다. 이 역삼각형을 클릭하면
허용되는 문자들이 나오므로, 그중에서 선택하시면 됩니다.
수작업으로 그외의 문자를 입력하면 마찬가지의 오류메시지가 뜹니다.
허용되는 문자가 너무 많아서 수작업으로 입력하기 힘들때는, 파란색 원부분을 클릭하여
이미 입력되어있는 범위를 지정해주어도 됩니다.
위와 같은 식으로 표현이 되면 해당범위안에 있는 셀내용만 허용하게 됩니다.
다른 시트에 있는 범위의 데이터를 원본으로 사용하고 싶다면, 마우스 클릭이 되지 않으므로, 그림처럼 수작업으로 시트이름과 셀범위를 지정해주어야합니다.
p.s. 2011.01.10 엑셀 2010부터는 원본을 다른 시트에 있는 데이터로 지정해줄때 마우스로 클릭이 가능하도록 바뀌었습니다.
2012년 5월 15일 화요일
슬픈 구글 사은품. ㅠㅠ
이번에 저글링 이벤트로 참여해서 받은 구글 사은품.
.
.
.
택배 기사님이 경비실에 놓고 간다하여 나의 하루를 들뜨게한 그 사은품.
.
.
.
구글... 구글에서... 나에게... 뭘까? 뭐지?
.
.
.
연필 한자루 + 노트 한권
노트 재생지이면서 질 안좋음..ㅡ.ㅡ;;
구글님, 이건 아닌듯해요. ㅠㅠ
.
.
.
2012년 5월 3일 목요일
인터넷 광고 용어 정리
■ CPC(Cost Per Click)
- 웹사이트에 특정한 방식으로 노출하여 클릭이 일어날때마다 과금하는 방식
ㄴ 1회 클릭마다 30원
■ CPA(Cost Per Action)
- 클릭후 회원가입완료 등과 같이 특정한 액션을 완료해야 과금
ㄴ 주문완료시 마다 해당 금액에 대한 수수료 과금
■ CPS(Cost Per Sale)
- 클릭후 고객이 특정상품을 구매해서 이윤이 발생되면 그에대한 특정 %를 수수료로 매체사에 지급
ㄴ 100만원 구매시 3% 수수료 과금
■ CPT(Cost Per Time)
- 기간을 정해놓고 정액제로 구매
ㄴ 100만원에 1개월동안 배너광고
■ ROI(Return on Investment)
- ROI는 수익 대비 비용 비율 을 말하며, ROI를 계산하는 방법은 캠페인의 목표에 따라 달라집니다.
예를 들어 100만원을 투자하여 120만원 상당의 매출을 올렸다면 수익이 20만원이 되는데, 이때 ROI는 (120만원-100만원)/100만원)으로 계산하여 20%가 됩니다.
일반적으로 ROI를 통해 광고가 비즈니스에 미치는 실제 효과를 알 수 있으므로 광고주에게 가장 중요한 측정 항목이라고 할 수 있습니다.
- ROI 의 중요성
ㄴ ROI를 계산하면 광고를 통해 거둔 수익을 확인할 수 있습니다.
ROI를 활용하면 예산 지출 방식을 결정하는 데 도움이 됩니다.
예를 들어 특정 캠페인이 다른 캠페인보다 높은 ROI를 창출한다는 사실을 알았으면 성공적인 캠페인에 예산을 더 배정하고 실적이 좋지 않은 캠페인의 예산은 삭감할 수 있습니다.
이 정보를 활용하여 실적이 저조한 캠페인의 실적을 개선할 수도 있습니다.
- 판매에 대한 ROI 계산
ㄴ 광고주의 비즈니스 목표가 온라인 판매인 경우 캠페인의 ROI를 간단히 파악할 수 있습니다.
캠페인 탭의 통계를 통해 캠페인 계정에 대한 특정 기간 동안의 광고비를 확인할 수 있으며 그런 다음 캠페인 광고를 통해 발생한 기업의 판매 수익에서 광고비를 제외하여 비즈니스에 대한 순이익을 계산할 수 있습니다. 이 순이익을 광고비로 나누면 해당 기간 동안의 ROI를 얻을 수 있습니다.
예를 들면 다음과 같습니다.
| 130만원 - | 100만원 = | 30만원 | ROI = 30만원/100만원 = 30% |
| 제품 판매금액: 130만원 상당(전환수로 측정) | 캠페인 지출: 100만원(캠페인 비용으로 측정) | 순이익: 30만원 | 수익 대비 비용 비율: 30%(ROI = 30%) |
■ ROAS(Return On Ad Spending)
- 광고를 통한 매출(광고 수익률)
- 광고수익률(ROAS) = 매출/광고*100
- 매출 광고비 ROAS
100만원 ÷ 100만원 * 100 = 100%
200만원 ÷ 100만원 * 100 = 200%
300만원 ÷ 100만원 * 100 = 300%
ex)
광고비 100만원을 투자했을 경우 매출이 광고비 원금과 같다면 ROAS는 100%가 된다. 따라서 100%에 미치지 못한다면 투자비용보다 광고비용이 더 많이 지출된 것이다. 하지만 ROAS가 100% 이상이라고 좋은 것은 아니다. 쇼핑몰 운영에 필요한 비용(상품 사입 비용, 건물 임대료, 교통비, 인건비 등)이 추가적으로 발생하기 때문에 ROAS가 300% 이상이어야 이익이 발생했다고 볼 수 있다.
■ CPI(Cost Per Impression)
- CPM과 비슷하지만 노출되는 순간 과금 발생
ㄴ 1회 노출에 1원
■ CPL(Cost Per Lead)
- 회원가입 유치를 위해 만들어진 과금방식
ㄴ 1회 회원가입마다 1000원
■ CPRP(Cost Per Rating Point)
- 표적 오디언스 1%에 도달하는데 드는 비용
ㄴ 광고 단가/ 구독ㄱ률 또는 시청취율
■ 광고호출수 (adimp)
■ GMS(gorss merchandise salse) - 총거래매출액
■ RPS (revenue per search) - 매출 / 전체쿼리
■ RPPV (Revene per pv) - 매출 / 노출
■ RPU (Revene per uv) - 매출 / uv
WOW week on week 전주대비 성장률 MOM month on month 전달 대비 성장률 QOQ quarter on quarter 전분기대비 성장률 YOY year on year 전년 대비 성장률 Tier 검색시 나타나는 층의 개념 DBPA digital branding performance ad 웹사이트에서 시행하는 브랜드 광고 CCDW costomer centered daum's way 고객에게 집중하는 다음 PPC pay per click 클릭당 받는 돈의 액수(10일간 1000원에 대한 PPC는 100원) CPM cost per impression 노출 10번에 집행비 2000원이면 CPM은 200원 CPS cost per sales 거래 수수료로, 방법론과 실가격 동시 가능 CPO/CPA cost per oder/action 광고노출을 해당 사이트에서 구매후 수수료책정 IR Investor relation 투자자에게 기업의 장단점을 설명하는 활동 SR sales rate 판매율 3screen pc, 모바일, DV +(tv) POI point of interest 대중들에게 인식되어있는 관심키워드(홍대 놀이터, 강남역 6번출구) 기저효과 과거에 침체기라서 현재 과도상승으로 보임 역기저효과 과거과도상승으로 인해 현재 과도침체로 보임 COVERAGE 광고노출된 쿼리 / 전체쿼리 broad match 핑클 = 서핑클럽 같이 핑클 관련 결과 나옴 exact match 정확하게 핑클만 매치시킴 Gross 총매출 NET 순이익 PV page view 페이지 전환수 UV unique visitor 순방문자수 CR conversion rate 구매전환률 CTR click through rate 온라인 광고에 대한 유저들의 반응 비율, 100번 노출에 10번클릭 CTR 10% ROI Return of Investment 투자대비 수익률 Depth 광고가 몇 개 차있는지. fillrate 광고 노출률 OLS online sign up 대행사 거치지 않고 바로 오는 광고주 CSI costomer satisfaction index 고객만족지수 KPI key performance index 달성해야 하는 목표
2012년 5월 2일 수요일
[엑셀] 콤보상자(Drop Down Selector) 사용하기
콤보상자는 미리 정해진 데이터를 Drop Down 창을 통해 그 중 하나를 선택할 때 사용한다.
이를 사용 하기 위해서는 미리 목록에 표시될 데이터를 준비해야 하는데
아래 표에서 처럼 B12 에서 B15 까지 오일 종류를 미리 입력해 놓아야 한다.
그리고 위와 같이 빠른 도구 모음에 빼 놓은 콤보상자를 선택해서
원하는 위치에 적당히 그려 넣은 다음에 오른쪽 버튼을 눌러 컨트롤 서식으로 들어간다.
제일 위의 입력 범위는 콤보박스 리스트에 나타낼 데이터의 범위를 설정하는 부분으로
새로 입력한 오일종류들 B12 에서 B15 까지를 선택한다.
셀 연결은 리스트에서 선택한 데이터가 표시될 셀을 선택하는데 여기서는 그냥 B4 로 선택한다.
이렇게 하면 표시는 되도 콤보박스에 가려서 안보이고 대신 콤보박스에서 선택한 내용만 보여 준다.
이 시트에서는 오일 종류가 특별한 역활을 안 하므로 일단 이렇게 해놓고
목록표시 줄수는 드롭다운 리스트에 나타날 최대 줄수를 지정한다.
확인을 누르고 콤보박스 셀렉터를 누르면 아래와 같이 오일 종류 목록이 나오고
그중 하나를 선택할 수 있게 된다.
[엑셀] 확인란(체크박스) 사용하기
확인란 (체크박스)란 어떤 조건을 수용(YES : TRUE) 하느냐 하지 않느냐(NO : FALSE) 를
선택하는 컨트롤로 꽤 익숙한 도구 중 하나이다.
이번에는 시트에 이 체크박스를 삽입하고 체크박스의 변화에 따라 금액을 계산하는 것을 공부한다.
우선 체크박스를 사용하기 위해서는 이전 포스트 "[31] 도구상자 삽입하기" 에서 설명한 대로
체크박스를 빠른 실행 도구 모음에 등록 시켜 놓은 다음 시트에 그려 넣어야 한다.
자 위와 같이 선택열에 하나씩 넣고 딸려 나오는 확인란 어쩌구는 다 지워 버린다.
이때 까지는 체크박스는 시트위에 놓여 있는 그림 이외에는 아무 것도 아니다.
체크 박스를 클릭했을 때 선택된 TRUE 혹은 FALSE 의 값을 받을 셀을 지정해야 한다.
지금 상태에는 그냥 해당 셀 위에 네모박스가 그려진 것 이외에는 아무 기능을 못하고
해당 체크박스를 선택했을 때 그 값 (TRUE, FALSE) 을 어느 셀이 받을 것인가를 정해야 한다.
이 말은 해당 체크 박스가 딴 곳에 있더라도 특정 셀에 TRUE 혹은 FALSE 데이터를 보낼 수 있다는
뜻이 된다.
체크박스에 마우스를 가져가서 오른쪽 버튼을 누르면 위와 같은 선택창이 나오는데 여기서
컨트롤 서식을 선택한다.
처음에 선택하지 않은 상태 즉 FALSE 상태로 두거나 선택된 상태 TRUE 로 두거나 맘대로 한다.
어떤 것이 마우스 클릭질을 적게 할 것인가 잘 판단해서 선택하면 된다.
다음이 셀 연결인데 체크 박스를 선택 혹은 해제 했을 때 TRUE 혹은 FALSE 값을 보낼 셀을
선택하는 부분이다.
C4 를 선택했다. 자동으로 $C$4 절대 번지로 변경 되었다.
즉 이 체크박스는 어디 가던 오직 C4 에만 데이타를 보낸다는 뜻이다.
3차원 음영은 네모 박스가 약간 3D 처럼 보이게 하는 것으로 별 의미 없다.
확인을 누르고 그 아래 모든 체크박스도 일일이 위와 같이 처리를 해 주어야 한다.
한번에 드래그로 하는 방법은 없다.
다음 D4 수량을 편집하는데
원하는 것은 각 행의 체크박스가 클릭이 되면 수량에는 1이 나오고 이 수량과 단가가 곱해져서
금액이 나오며 밑의 합계 금액까지 계산되도록 하는 것이다.
만약 체크박스가 해제되면 수량은 BLANK 따라서 금액도 BLANK 가 되도록 각 서식을
###,### 형태로 바꾸어 주었다.
수량 셀에서 함수 IF 를 선택한다.
다시 한번 설명하면 만약 C4 가 TRUE 즉 C4 와 연결된 체크박스가 선택 되었다면
해당 셀의 값은 1 이고 체크박스가 해제 되었다면 0 의 값을 갖는다라는 뜻이다.
함수 원형은 =IF(C4=TRUE,1,0) 이다.
자 이건 선택해서 아래로 쭈욱 긁어 내려가서 모든 행에 다 같이 적용하자.
단가는 그냥 임의의 숫자이고
금액은 =D4*E4 로 단순한 곱셈이다. 또 주욱 긁어 내려간다.
아랫쪽 합계금액은 위의 금액의 합계인 =SUM(F4:F8)
이제 하나씩 체크박스를 클릭해 보자.
선택이 되면 체크박스와 연결된 각 셀에 TRUE 가 표시되고 수량에는 1이 나타나며
금액이 계산될 것이다.
헤제가 되면 FALSE 가 표시되고 수량은 0 인 공백으로 금액도 공백으로 표시될 것이다.
피드 구독하기:
글 (Atom)