[엑셀] 유효성 검사 (조건에 맞는 값만을 입력하기)

이원목적분류표에 점수를 수작업으로 기입해야하는데 입력실수로 계산이 틀린 경험을 많이 해 보셨을 겁니다. 예를들면, 1점에서 10점 사이의 점수만 입력해야하는데 3.3점이라고 기입할 것을 33점으로 기입하면 오류가 나겠지요. 아예 입력할때부터 10점 이상은 입력이 안되도록 설정하면 오류가 날 가능성이 줄지 않을까요?

오늘은 그런 기능을 하는 유효성검사를 알아보겠습니다.

먼저, 데이터를 입력할 부분을 선택합니다.

데이터—데이터도구—데이터유효성검사를 실행합니다.

 

빨간색원으로 표시한 역삼각형을 클릭하셔서 "소수점"을 선택하고, 제한방법, 최소값,최대값을 지정해 줍니다.

 

그리고나서 선택범위내의 셀에 값을 입력해 봅니다. 0.1과 10사이를 벗어나면 그림과 같은 오류메시지가 뜹니다.

 

입력범위를 숫자가 아닌 문자로 지정해 줄수도 있습니다. 마찬가지로, 입력할 범위를 선택하고

데이터유효성 검사를 실행하여, 창이 뜨면 제한대상을 "목록"으로, 원본에는 허용되는 문자들을 쉼표로 구분하여 입력합니다.

그러면 해당범위의 셀 옆에 역삼각형 표시가 뜹니다. 이 역삼각형을 클릭하면

허용되는 문자들이 나오므로, 그중에서 선택하시면 됩니다.

수작업으로 그외의 문자를 입력하면 마찬가지의 오류메시지가 뜹니다.

 

허용되는 문자가 너무 많아서 수작업으로 입력하기 힘들때는, 파란색 원부분을 클릭하여

이미 입력되어있는 범위를 지정해주어도 됩니다.

위와 같은 식으로 표현이 되면 해당범위안에 있는 셀내용만 허용하게 됩니다.

다른 시트에 있는 범위의 데이터를 원본으로 사용하고 싶다면, 마우스 클릭이 되지 않으므로, 그림처럼 수작업으로 시트이름과 셀범위를 지정해주어야합니다. 

p.s. 2011.01.10 엑셀 2010부터는 원본을 다른 시트에 있는 데이터로 지정해줄때 마우스로 클릭이 가능하도록 바뀌었습니다.

 

슬픈 구글 사은품. ㅠㅠ

이번에 저글링 이벤트로 참여해서 받은 구글 사은품.

.

.

.

택배 기사님이 경비실에 놓고 간다하여 나의 하루를 들뜨게한 그 사은품.

.

.

.

구글... 구글에서... 나에게... 뭘까? 뭐지? 

.

.

.

연필 한자루 + 노트 한권

노트 재생지이면서 질 안좋음..ㅡ.ㅡ;;

구글님, 이건 아닌듯해요. ㅠㅠ

.

.

.

인터넷 광고 용어 정리

■ CPC(Cost Per Click)

- 웹사이트에 특정한 방식으로 노출하여 클릭이 일어날때마다 과금하는 방식

  ㄴ 1회 클릭마다 30원

 

■ CPA(Cost Per Action)

- 클릭후 회원가입완료 등과 같이 특정한 액션을 완료해야 과금

  ㄴ 주문완료시 마다 해당 금액에 대한 수수료 과금

 

■ CPS(Cost Per Sale)

 - 클릭후 고객이 특정상품을 구매해서 이윤이 발생되면 그에대한 특정 %를 수수료로 매체사에 지급

   ㄴ 100만원 구매시 3% 수수료 과금

 

■ CPT(Cost Per Time)

 - 기간을 정해놓고 정액제로 구매

   ㄴ 100만원에 1개월동안 배너광고

■  CTS (Conversion Tracking System)  :  구매전환을 추적하는 시스템.

 

 

■ 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	달성해야 하는 목표

 

[엑셀] 콤보상자(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 를 선택한다.
 

    논리 함수인 IF 는 앞의 [12] 함수 : IF 함수 부분에서 배웠다.
    다시 한번 설명하면 만약 C4 가 TRUE 즉 C4 와 연결된 체크박스가 선택 되었다면
    해당 셀의 값은 1 이고 체크박스가 해제 되었다면 0 의 값을 갖는다라는 뜻이다.

    함수 원형은 =IF(C4=TRUE,1,0) 이다.
    자 이건 선택해서 아래로 쭈욱 긁어 내려가서 모든 행에 다 같이 적용하자.

    단가는 그냥 임의의 숫자이고
    금액은 =D4*E4 로 단순한 곱셈이다.  또 주욱 긁어 내려간다.
    아랫쪽 합계금액은 위의 금액의 합계인 =SUM(F4:F8) 

    이제 하나씩 체크박스를 클릭해 보자.
    선택이 되면 체크박스와 연결된 각 셀에 TRUE 가 표시되고 수량에는 1이 나타나며
    금액이 계산될 것이다.
    헤제가 되면 FALSE 가 표시되고 수량은 0 인 공백으로 금액도 공백으로 표시될 것이다.

    

[엑셀] SPIN 단추(수량변경 회전차) 사용하기

SPIN 단추란 아래 위 방향 버튼이 상하로 달려 있어서 
 이 상하 버튼을 누를 때 마다 특정 셀의 숫자를 늘리거나 줄이는 기능을 하는 도구이다.

 키보드 건들기 겁나 싫어하는 사람들이 많이 쓰거나
 아니면 특정 숫자 이하 이상을 제한하거나 증감을 제어하고자 할 경우에 사용한다.
 
 우선 앞의 체크박스 사용에서 처럼 빠른 도구모음에서 빼 놓은 스핀단추를
 시트의 원하는 부위에 그려 넣는다.

   다음 마우스 우측 버튼을 눌러 컨트롤 서식을 선택한다.

   제일 위의 현재 값은 초기 값으로 무조건 1부터 하고 싶으면 1을 넣고 아니면 0 을 넣던 뭘 넣던.
   다음 최소값 최대값은 SPIN 을 아무리 돌려도 더 이상 올라 가거나 내려 가지 않는 한계를 넣는다.
   다음 증분변경은 SPIN 을 한번 작동했을 때 증 감 되는 수치를 넣는다.
   5를 넣으면 한번 올릴 때 마다 5씩 증가하고 한번 내릴 때마다 5씩 감소한다.
   최소값이 0 이므로 - 는 표시되지 않는다.
   다음 셀 연결은 SPIN 에 의해서 발생한 수를 어느 셀에다 퍼 부을 것인가를 선택하는 것으로
   여기서는 D8 와셔액의 단가 칸에 해당 숫자를 보내도록 했다.
   역시 절대 번지로 바뀌게 된다.
   다음 수량은 이전에 있었던 IF 절을 없애 버리면 알아서 숫자 형태로 표시가 된다.

      위와 같이 완성되었다.   
      SPIN 버튼을 상하로 조절할 때 마다 수량이 증감하고 이에 따라 금액도 변경되는 것을 볼 수 있다.

[엑셀] 옵션단추(Radio Button) 사용하기

옵션단추란 여러개의 선택 사항 가운데 단 하나만 선택할 수 있는 기능을 수행 한다.
 즉 남자, 여자 혹은 세액 있슴 없슴 같이 두개의 조건이 동시에 만족할 수 없을 경우에
 이 도구를 사용하며 수개의 옵션 단추가 하나의 그룹으로 이루어 지며
 각각을 선택했을 때의 1번 부터 차례대로 숫자가 연결된 셀에 표시된다.

     위와 같이 빠른 도구 모음에 옵션단추를 빼 놓고 적당한 위치에 그려 준다.
     다음 각 옵션의 제목을 위와 같이 세액 별도 세액 포함으로 바꾸어 주고
     컨트롤 서식을 선택한다.

   서식 설정 창에서 위의 값은 처음에 해당 옵션이 선책된 것이냐 아니냐를 선택하는 것이고
   셀 연결은 옵션버튼에 의해 생성된 숫자가 들어갈 셀의 위치를 설정한다.
   이 셀 위치는 여러개의 옵션 버튼이 공동으로 사용할 수 있으며
   이때 한개의 셀에 지정된 옵션 버튼들이 한개의 그룹을 형성한다.
   설정된 순서에 따라 1 2 3 .... 의 값을 발생시키며 해당 값은 셀 연결에서 정의한 셀에 들어간다.

   우리는 옵션단추가 있는 E10 에 위치하였고 이렇게 하면 숫자가 나타나도
   옵션 버튼에 가려서 겉으로는 보이지 않게 된다.

 

     다음 F10 의 내용은 IF 문장을 써서 =IF(E10=1,F9*1.1,F9*1) 이라고 수식을 입력한다.
     즉 세액별도가 선택되면 E10 에는 1이라는 숫자가 발생하므로 F10의 값은 위의 합계금액 * 1.1 을 하며
     세액포함이 선택되면 F10 에는 그냥 위의 합계금액인 F9 값을 넣도록 해 주면 된다.