- 최초 작성일: 2000-12-26
- 최종 수정일: 2026-09-29
- 조회수: 35 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 언론사별 홍보효과 분석하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
Christmas는 잘들 보내셨는지요? 살아가면서 과연 몇 번의 화이트 크리스마스를 맞이할 수 있을지 모르겠지만 말 그대로 White Christmas였습니다.
얼마 전에 Exceller 옆 부서에서 근무하는 어느 분이 찾아 오셨습니다. 그런데 그냥 오셨을까요?
아닙니다! 숙제(질문거리)를 들고 오셨습니다. ^^
그 분의 질문을 요약하자면…
(1) 아래의 (표 1)과 같은 자료가 있습니다(데이터는 보안상 수정했습니다).
이것은 각 언론사별 광고 노출 빈도를 정리한 것으로(질문을 주신 분은 홍보실에 근무하는 분입니다) 수시로 Update 됩니다.
이 자료를 가지고 (표 2)와 같은 보고용 자료를 만들어야 하는데 어떻게 손쉽게 분석자료를 만들 수 있느냐 하는 것입니다. 거기서 한걸음 더 나아가 (그림 1)과 같이 Visual한 그래프까지도 가능하냐고 물어오셨습니다.
맨날 질문을 받고 답변만 해 오던 Exceller가 오늘은 반대로 여러분들께 질문을 드려볼까요? 어떻게 하면 될까요? 오늘 강좌를 완전히 소화하시면 내공이 한단계 상승하게 됩니다.
(1) 그냥 속편하게 시신경과 날쌘 손발을 최대한 이용한다. (속은 편할지 몰라도 손발은 결코 편하지 않을 껄요? ^^) (2) VBA로 코딩을 한다. (3) EXCEL의 고유기능을 활용한다.
만약 여러분이라면 위 1~3의 방법 중 어떤 것을 선택하시겠습니까? (1)을 선택하신 분은 EXCEL 초급 사용자, (2)를 선택하신 분은 EXCEL 중급 사용자, (3)을 선택하신 분은 EXCEL Power-user
이렇게 생각하시면 될 것 같습니다. 그러면 또 이렇게 항의(?)를 하시는 분이 있겠지요?
"아니 어떻게 VBA를 사용하는 사람은 중급자이고, EXCEL 고유의 기능을 활용하는 사람이 파워유저란 말입니까? 뭔가 착각하신 게 아닙니까?" "절대 아닙니다! VBA로 프로그래밍을 하시기 이전에 맨 먼저 염두에 두어야 할 것은 함수 또는 EXCEL 고유의 기능으로 해결할 수 있는가를 확인하는 것입니다. 그래서 고유의 기능으로 해결할 수 없다는 판단이 들 때 그제서야 VBA의 도움을 받는 것이 순서입니다."
데생을 할 줄 모르는 피카소였다면 게르니카나 아비뇽의 처녀들 같은 불후의 명작이 나올 수 없었을 것입니다. 마찬가지로 EXCEL에 어떠한 고유 기능들이 구비되어 있는지 모르고서는 훌륭한 EXCEL Programmer 또는 Spreadsheet Developer가 될 수 없습니다.
- 최신 글을 불러오는 중...
언론사별 홍보효과 분석하기
핵심 요약
- 원본 홍보 데이터를 조건별로 집계할 때는 여러 조건을 곱하는 배열수식으로 매체·구분별 홍보효과를 합산할 수 있습니다.
- 동적 이름 정의에 OFFSET과 COUNTA를 사용하면 원본 데이터가 추가되어도 집계 범위가 자동으로 확장됩니다.
- 집계표를 차트와 연결하면 원본 자료가 갱신될 때 분석표와 시각화 결과도 함께 업데이트되도록 구성할 수 있습니다.
그렇다면 EXCEL의 고유 기능 중에서 어떤 것을 사용하면 될까요? 그렇습니다! 바로 Array Formula(배열수식)를 사용하시면 되겠지요. (표 2)의 "자사단독" 항목에 가 보시면 아래와 같은 수식이 들어있을 것입니다.
{=SUM(($C$56:$C$101=$B108)*($E$56:$E$101=C$107)*($I$56:$I$101))}배열수식에서 *는 And에 해당됩니다. 따라서 위의 수식을 우리말로 풀이하자면, "C56:C101 영역 내의 값이 B108 셀의 값과 같으면서 E56:E101 영역 내의 값이 C107셀과 같은 레코드일 경우, I56:I101 셀의 해당 값을 더하라" 이렇게 될 것입니다.
잘 이해가 안되는 분은 <F2>키를 눌러 수식편집 모드를 만든 다음 공식을 몇 도막으로 나누어 살펴보세요. 이 때 즉석 계산 기능(블록으로 잡은 다음 <F9>키를…)을 활용하세요.
이렇게 입력하신 다음에 공식을 주욱 복사하면 (표 2)와 같은 집계표가 만들어집니다.
(표 1) 원본 자료
| 게재일자 | 매체명 | 기사 내용 | 구분 | 가로 | 세로 | 면적 (㎠) | 홍보효과 (금액) |
| 2000-11-01 | 매월경제 | 자사단독 | 4.2340475638 | 4.0800412517 | =F70*G70 | 1781.9171439898 | |
| 2000-11-02 | 매월경제 | 업계종합 | 4.6943945444 | 2.1984033845 | =F71*G71 | 2321.541123822 | |
| 2000-11-03 | 동양일보 | 자사단독 | 5.7620906115 | 2.6329091346 | =F72*G72 | 1968.6470557928 | |
| 2000-11-04 | 매월경제 | 업계종합 | 1.60516574 | 9.9138804704 | =F73*G73 | 6196.0049414854 | |
| 2000-11-05 | 중원일보 | 업계종합 | 6.4460029844 | 6.3333702784 | =F74*G74 | 3077.8003281803 | |
| 2000-11-06 | 스포츠한국 | 자사단독 | 8.3085799159 | 2.1427739437 | =F75*G75 | 9190.2930589239 | |
| 2000-11-07 | 내부경제 | 타사단독 | 0.6766238755 | 6.2961708348 | =F76*G76 | 9981.4016917923 | |
| 2000-11-08 | 매월경제 | 업계종합 | 7.2363508914 | 6.8070381012 | =F77*G77 | 9459.2850917408 | |
| 2000-11-09 | 중원일보 | 기타 | 0.3153740304 | 4.0344620771 | =F78*G78 | 1752.1559859717 | |
| 2000-11-10 | 내부경제 | 업계종합 | 9.4016227272 | 4.5653290298 | =F79*G79 | 37.2082277775 | |
| 2000-11-11 | 대한신보 | 타사단독 | 5.4500734497 | 8.2096716297 | =F80*G80 | 7535.275003317 | |
| 2000-11-12 | 한불경제 | 기타 | 7.2846196633 | 9.2983456325 | =F81*G81 | 6049.0775760952 | |
| 2000-11-13 | 한불경제 | 업계종합 | 4.3629828157 | 1.1657267555 | =F82*G82 | 104.1448306411 | |
| 2000-11-14 | 문화신문 | 자사단독 | 0.9675025305 | 0.7157362453 | =F83*G83 | 4919.6517259784 | |
| 2000-11-15 | 내부경제 | 기타 | 3.9556224079 | 1.3589351259 | =F84*G84 | 8012.8708340578 | |
| 2000-11-16 | 문화신문 | 업계종합 | 0.2809377729 | 8.2151411559 | =F85*G85 | 6517.7312815152 | |
| 2000-11-17 | 국민신문 | 자사단독 | 1.2031371888 | 3.014561633 | =F86*G86 | 2752.1857605173 | |
| 2000-11-18 | 중원일보 | 자사단독 | 8.1236011491 | 8.123067872 | =F87*G87 | 1329.6240407142 | |
| 2000-11-19 | 스포츠한국 | 업계종합 | 4.0001551754 | 8.7628409623 | =F88*G88 | 350.973854345 | |
| 2000-11-20 | 국민신문 | 타사단독 | 7.6372474879 | 1.9743610934 | =F89*G89 | 9027.4935226185 | |
| 2000-11-21 | 국민신문 | 업계종합 | 7.5208483141 | 2.3135680561 | =F90*G90 | 1127.1556359987 | |
| 2000-11-22 | 내부경제 | 업계종합 | 4.3645631548 | 3.6651487524 | =F91*G91 | 1908.7711430152 | |
| 2000-11-23 | 대한신보 | 기타 | 6.488013229 | 7.5391676357 | =F92*G92 | 8154.6655521038 | |
| 2000-11-24 | 문화신문 | 타사단독 | 0.8911916465 | 4.2786201047 | =F93*G93 | 9083.6572110846 | |
| 2000-11-25 | 국민신문 | 기타 | 4.5064299015 | 2.4413181803 | =F94*G94 | 2710.7400618054 | |
| 2000-11-26 | 중원일보 | 기타 | 9.7613322959 | 8.2991187086 | =F95*G95 | 4291.4169906704 | |
| 2000-11-27 | 동양일보 | 자사단독 | 8.1577480776 | 7.7581075738 | =F96*G96 | 8119.5353742192 | |
| 2000-11-28 | 대한신보 | 자사단독 | 9.3571399461 | 4.4877518097 | =F97*G97 | 4070.180206698 | |
| 2000-11-29 | 스포츠한국 | 타사단독 | 8.5846805589 | 6.3237934034 | =F98*G98 | 5353.0799807065 | |
| 2000-11-30 | 동양일보 | 업계종합 | 2.2610389841 | 8.0882846899 | =F99*G99 | 4711.7605185249 | |
| 2000-12-01 | 한불경제 | 자사단독 | 7.7771325377 | 7.1572091651 | =F100*G100 | 6313.3224330545 | |
| 2000-12-02 | 한불경제 | 자사단독 | 1.3319231296 | 3.0644802208 | =F101*G101 | 5252.8850287692 | |
| 2000-12-03 | 중원일보 | 타사단독 | 2.9303257393 | 8.3735183746 | =F102*G102 | 697.1375425176 | |
| 2000-12-04 | 내부경제 | 자사단독 | 0.8423553217 | 8.4372271689 | =F103*G103 | 8701.0750650734 | |
| 2000-12-05 | 매월경제 | 자사단독 | 4.8848844344 | 4.1212955296 | =F104*G104 | 5371.4840855376 | |
| 2000-12-06 | 대한신보 | 업계종합 | 6.563300467 | 7.3566660591 | =F105*G105 | 5458.4740649335 | |
| 2000-12-07 | 한불경제 | 자사단독 | 0.2871567698 | 1.9306367428 | =F106*G106 | 9787.0617122804 | |
| 2000-12-08 | 동양일보 | 기타 | 2.2799059708 | 2.8967208945 | =F107*G107 | 846.3463053331 | |
| 2000-12-09 | 동양일보 | 타사단독 | 3.0698094037 | 0.8091745265 | =F108*G108 | 4080.3346759712 | |
| 2000-12-10 | 문화신문 | 기타 | 0.7114236912 | 9.0162949194 | =F109*G109 | 5080.1227136734 | |
| 2000-12-11 | 한불경제 | 타사단독 | 9.0053410375 | 1.1456738917 | =F110*G110 | 3998.4409867074 | |
| 2000-12-12 | 한불경제 | 업계종합 | 3.5675996918 | 3.7765599671 | =F111*G111 | 802.200453539 | |
| 2000-12-13 | 중원일보 | 기타 | 9.5098434885 | 1.7087071186 | =F112*G112 | 523.9242062719 | |
| 2000-12-14 | 스포츠한국 | 기타 | 8.2861705748 | 3.3258816835 | =F113*G113 | 7572.8997968223 | |
| 2000-12-15 | 매월경제 | 기타 | 0.5944850588 | 5.5972838117 | =F114*G114 | 5562.1745913282 | |
| 2000-12-16 | 매월경제 | 타사단독 | 0.5510324084 | 3.0375848465 | =F115*G115 | 8229.9314344651 |
(표 2) 보고자료1(집계표)
| 자사단독 | 업계종합 | 타사단독 | 기타 | 합계 | |
| 매월경제 | =SUM(C121:F121) | ||||
| 동양일보 | =SUM(C122:F122) | ||||
| 중원일보 | =SUM(C123:F123) | ||||
| 스포츠한국 | =SUM(C124:F124) | ||||
| 내부경제 | =SUM(C125:F125) | ||||
| 대한신보 | =SUM(C126:F126) | ||||
| 한불경제 | =SUM(C127:F127) | ||||
| 문화신문 | =SUM(C128:F128) | ||||
| 국민신문 | =SUM(C129:F129) |
참고: (표 2)의 집계 값은 원본 워크시트의 배열수식으로 계산되는 값이라 이 페이지의 표에는 비어 있습니다.
이 집계표를 가지고 누적 막대 그래프를 그리면 아래와 같이 됩니다.
(그림 1) 보고자료 2 - 언론사별/섹션별 홍보효과 분석 -
이렇게 하시면 되겠지요? 그런데 뭔가 좀 아쉬운 생각이 들지 않으시는지…
(표 1)의 맨 마지막 라인에 보면 날짜가 00/12/16일로 되어 있지요? 그러니까 이 자료는 계속적으로 업데이트가 되어야 할 자료입니다. 그런데 현재 상태대로라면 자료가 추가될 때마다 공식을 일일이 새로 지정해 주어야 합니다. 매일매일 또는 하루에도 몇 건씩 새로운 자료가 추가된다면 그 때마다 일일이 영역을 수정해 주는 것도 무척이나 번거로운 일입니다. 무슨 좋은 방법이라도… ?
이런 경우에는 범위에 이름을 정의해 주는, 일명 동적 이름정의(Dynamic Naming)를 활용하시면 해결이 됩니다(동적 이름정의라는 용어는 Exceller가 붙여본 이름입니다. 다행히 그런 용어가 이미 있는지는 모르겠지만… ^^)
WorkPlace 시트로 가셔서 "원본 자료"의 마지막 부분에 새로운 데이터를 추가해 보세요. 그리고 새로운 자료를 추가했을 때 집계표와 차트에 어떠한 변화가 생기는지 눈여겨 보시기 바랍니다.
잘 보셨나요? WorkPlace 시트의 집계표 중에서 빨간 셀 부분을 보면, 셀 주소들은 다 어디가고 매체, 구분, 홍보효과 이런 단어들이 들어있지요? 바로 이것이 동적 이름정의를 사용한 것입니다.
=SUM((매체=$J4)*(구분=K$3)*(홍보효과))"동적 이름정의"에 대해서는 아주 오래 전에 설명을 드린 기억이 납니다. 혹시 기억이 나지 않는 분은 X0026, 27 강좌 등을 다시 한번 읽어보고 오세요. "삽입-이름-정의" 메뉴를 선택하시면 아래와 같은 대화상자가 나타나는데 "정의된 이름" 중 하나를 클릭해 보면 "참조"란에 얄궂은(?) 문자들이 나타나지요?
=OFFSET(WorkPlace!$B$4,0,0,COUNTA(WorkPlace!$B:$B)-1)Offset 함수에 대해서는 몇 차례 소개해 드렸지요? 언제 했냐구요? X0024, X0143 강좌 등을 살펴보시기 바랍니다.
여기서 왜 공식의 마지막 부분에 -1을 해 주었을까요? 심심해서? 아니면 졸다가?? 원본 자료의 타이틀 부분은 카운팅에서 제외하기 위해서입니다. Offset 함수나 위 빨간 선 부분의 식에 대한 상세한 설명은 X0024~27 강좌를 참고하시면 되겠습니다.
이번 강좌는 분량도 좀 많은 것 같고 약간 까다로운 개념도 등장하였습니다만 아주 유용하게 사용하실 수 있는 것이니까 확실히 내 것으로 만드시기 바랍니다.
오늘은 여기까지…
2000-12-26
정리 — 언론사별 홍보효과 분석하기
| 구분 | 내용 |
|---|---|
| 집계 수식 | {=SUM(($C$56:$C$101=$B108)*($E$56:$E$101=C$107)*($I$56:$I$101))} |
| 수식 해석 | 배열수식에서 *는 And에 해당하며, 매체와 구분이 모두 일치하는 행의 홍보효과를 합산 |
| 동적 이름 정의 | =OFFSET(WorkPlace!$B$4,0,0,COUNTA(WorkPlace!$B:$B)-1) |
| 이름을 사용한 수식 | =SUM((매체=$J4)*(구분=K$3)*(홍보효과)) |
| -1의 이유 | 원본 자료의 타이틀 행을 카운팅에서 제외하기 위함 |
| 차트 | 집계표로 누적 막대 그래프를 작성 |
자주 묻는 질문 (FAQ)
Q1. 언론사별 홍보효과를 집계하는 방법은 무엇인가요?
배열수식을 사용합니다. 매체명 조건과 구분 조건과 홍보효과 범위를 곱한 뒤 SUM으로 합하면 매체와 구분별 집계표를 만들 수 있습니다.
Q2. 자료가 계속 추가되어도 수식을 고치지 않으려면 어떻게 하나요?
OFFSET과 COUNTA를 이용한 동적 이름 정의를 사용합니다. 원본 자료에 행이 추가되면 이름의 범위가 자동으로 늘어나 집계표와 차트가 함께 갱신됩니다.
Q3. 동적 이름 정의 수식 끝의 -1은 무슨 의미인가요?
COUNTA로 센 행 수에서 원본 자료의 타이틀 행 하나를 제외하기 위한 것입니다.
마치며
VBA로 코딩하기 전에 배열수식과 동적 이름 정의 같은 Excel 고유 기능으로 해결할 수 있는지 먼저 확인해 보시기 바랍니다. VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
