{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["조건부서식","3차원차트","세로막대형차트","매크로 기본예제","부분합 매크로","자동필터 매크로","피벗테이블","시나리오관리자","목표값찾기","데이터표","데이터통합","2급 고급필터","1급 고급필터"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":11,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"조건부서식","index":0,"n_rows":24,"n_cols":9,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[]
["문제: 성별이 '남' 이고, 지점명이 '명동' 인 행 전체를 채우기색 노랑, 빨간 글꼴 굵게 설정하시오."]
[null,"(if, and, or 중 알맞은 함수 사용)"]
[]
["가입자명","성별","가입년월일","상품종류","지점명","월불입액","납입시점","연이율","만기금액"]
["송민규","남","2019-03-04","청약예금","명동",120000,"월초",0.03,16811000]
["김선재","남","2017-01-26","정기적금","강남",150000,"월말",0.02,19908000]
["이가영","여","2017-07-01","정기적금","여의도",50000,"월초",0.02,6648000]
["민태희","여","2017-08-18","청약예금","여의도",80000,"월초",2.9000000000000001E-2,11149000]
["김은창","남","2018-09-26","청약저축","명동",100000,"월초",2.8000000000000001E-2,13863000]
["전호식","여","2017-12-22","정기적금","명동",90000,"월초",0.02,11965000]
["김현주","남","2018-11-14","정기적금","여의도",100000,"월초",0.02,13295000]
["조애라","여","2018-04-19","정기적금","합정",120000,"월말",0.02,15927000]
["최규현","남","2018-11-29","정기적금","여의도",250000,"월초",0.02,33236000]
["장진구","남","2019-01-04","청약저축","명동",10000,"월말",2.1000000000000001E-2,1335000]
["김우석","남","2018-12-01","정기적금","합정",120000,"월말",0.02,15927000]
[]
["문제: 가입자명이 '김' 으로 시작하고, 상품종류가 '정기적금' 인 월불입액 만 연한녹색 채우기 색을 설정하시오."]
[null,"(left, and 함수 사용)"]
[null,"*조건부 서식 '수식을 사용하여 서식을 지정할 셀 결정' 메뉴는 와일드 카드 사용 불가."]
[]
[]
["유툽 = 함수바라기"]
["다음블로그 = http://blog.daum.net/comtc1004/"]

{"sheet":"3차원차트","index":1,"n_rows":6,"n_cols":6,"rows_kept":6,"cut":false,"hidden":false,"merged":[[0,1,0,5]]}
[null,"성과 현황"]
[null,"부서","첫째주","둘째주","셋째주","넷째주"]
[null,"물류부",92,95,98,78]
[null,"생산관리부",59,65,87,92]
[null,"인사재무부",95,95,85,78]
[null,"전산정보부",70,92,57,96]

{"sheet":"세로막대형차트","index":2,"n_rows":11,"n_cols":4,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["관람 현황"]
[]
["공연이름","10월","11월","12월"]
["7080콘서트",1250,1250,5420]
["피노키오",2543,251,2541]
["아이돌대축제",1251,3541,5235]
["요술친구",2543,2541,2152]
["백설공주",1245,2354,5254]
["헨리의모험",2515,2510,1442]
["천사의노래",5212,2358,2541]
["행복한우리집",1254,2510,5841]

{"sheet":"매크로 기본예제","index":3,"n_rows":15,"n_cols":9,"rows_kept":15,"cut":false,"hidden":false,"merged":[[1,0,1,8]]}
[]
["납품현황"]
["번호","납품일자","제품코드","제품명","납품단가","납품수량","금액","세금","세후금액"]
[6,40799,"B-021","볼펜",250,50,71250,4987.5000000000009,66262.5]
[11,40835,"B-020","볼펜",320,840,84000,5880.0000000000009,78120]
[1,40791,"C-239","색연필",195,160,16000,1120,14880]
[14,40826,"C-235","색연필",150,890,106800,7476.0000000000009,99324]
[17,40844,"C-212","색연필",200,90,23400,1638.0000000000002,21762]
[3,40807,"A-541","색종이",125,450,76500,5355.0000000000009,71145]
[5,40793,"A-351","색종이",150,570,165750,11602.500000000002,154147.5]
[9,40787,"C-555","색종이",230,90,13500,945.00000000000011,12555]
[13,40825,"C-525","연필",150,370,210900,14763.000000000002,196137]
[23,40873,"C-521","연필",100,820,82000,5740.0000000000009,76260]
[4,40799,"J-251","자",100,850,90000,6300.0000000000009,83700]
[12,40828,"J-152","자",105,270,28350,1984.5000000000002,26365.5]

{"sheet":"부분합 매크로","index":4,"n_rows":22,"n_cols":9,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["문제: 제품명 기준으로 금액과 세후금액의 합계를 계산하는 부분합 매크로를 생성하세요. "]
["        단추 매크로 버튼은 K3:L5 영역에 생성하세요."]
[]
["번호","납품일자","제품코드","제품명","납품단가","납품수량","금액","세금","세후금액"]
[6,"2011-09-13","B-021","볼펜",250,50,71250,4987.5000000000009,66262.5]
[7,"2011-09-26","K-210","칼",260,80,12000,840.00000000000011,11160]
[21,"2011-11-19","K-651","칼",350,80,20800,1456.0000000000002,19344]
[17,"2011-10-28","C-212","색연필",200,90,23400,1638.0000000000002,21762]
[9,"2011-09-01","C-555","색종이",230,90,13500,945.00000000000011,12555]
[1,"2011-09-05","C-239","색연필",195,160,16000,1120,14880]
[13,"2011-10-09","C-525","연필",150,370,210900,14763.000000000002,196137]
[3,"2011-09-21","A-541","색종이",125,450,76500,5355.0000000000009,71145]
[20,"2011-11-10","C-332","형관펜",230,480,110400,7728.0000000000009,102672]
[8,"2011-09-27","Q-521","테이프",350,560,128800,9016,119784]
[22,"2011-11-21","Q-552","테이프",550,780,273000,19110,253890]
[23,"2011-11-26","C-521","연필",100,820,82000,5740.0000000000009,76260]
[11,"2011-10-19","B-020","볼펜",320,840,84000,5880.0000000000009,78120]
[4,"2011-09-13","J-251","자",100,850,90000,6300.0000000000009,83700]
[19,"2011-11-07","G-952","지우개",120,870,478500,33495,445005]
[14,"2011-10-10","C-235","색연필",150,890,106800,7476.0000000000009,99324]
[10,"2011-09-06","C-395","형관펜",260,950,209000,14630.000000000002,194370]
[18,"2011-10-14","J-352","자",570,980,343000,24010.000000000004,318990]

{"sheet":"자동필터 매크로","index":5,"n_rows":24,"n_cols":8,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
["문제: 지점명이 명동 이거나 합정인 자동필터 매크로를 생성하세요."]
["        구름도형 매크로 버튼은 J3:K5영역에 위치 시키세요."]
[]
["가입자명","가입년월일","상품종류","지점명","월불입액","계약기간(월)","연이율","만기금액"]
["송민규","2019-03-04","청약예금","명동",120000,120,0.03,16811000]
["김선재","2017-01-26","정기적금","강남",150000,120,0.02,19908000]
["이가영","2017-07-01","정기적금","여의도",50000,120,0.02,6648000]
["민태희","2017-08-18","청약예금","여의도",80000,120,2.9000000000000001E-2,11149000]
["김은창","2018-09-26","청약저축","명동",100000,120,2.8000000000000001E-2,13863000]
["전호식","2017-12-22","정기적금","명동",90000,120,0.02,11965000]
["오현주","2018-11-14","정기적금","여의도",100000,120,0.02,13295000]
["조애라","2018-04-19","정기적금","합정",120000,120,0.02,15927000]
["장진구","2019-01-04","청약저축","명동",10000,120,2.1000000000000001E-2,1335000]
["김우석","2018-12-01","정기적금","합정",120000,120,0.02,15927000]
["진혜영","2019-12-26","청약저축","명동",100000,120,2.8000000000000001E-2,13863000]
["이윤희","2019-05-12","청약예금","합정",50000,120,0.03,6988000]
["정석현","2018-04-10","청약예금","강남",150000,120,0.03,20962000]
["함성민","2018-12-15","청약예금","여의도",75000,120,2.9000000000000001E-2,10427000]
["선주대","2018-04-17","정기적금","강남",120000,120,0.02,15927000]
["우연희","2018-12-10","청약저축","강남",10000,120,2.3E-2,1348000]
["문태진","2019-12-04","청약저축","여의도",120000,120,2.8000000000000001E-2,16635000]
["이현애","2019-02-26","청약예금","합정",250000,120,2.8000000000000001E-2,34656000]
["조남수","2018-12-09","정기적금","명동",70000,120,0.02,9291000]
["정만호","2019-08-22","청약저축","여의도",50000,120,3.3000000000000002E-2,7098000]

{"sheet":"피벗테이블","index":6,"n_rows":15,"n_cols":7,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
[]
[null,"제품코드","분류","브랜드","가격","제조년도","판매량"]
[null,"C1393-322","휴대용","퀴디",587000,2012,11]
[null,"C1933-931","휴대용","퀴디",640000,2012,4]
[null,"C1333-321","휴대용","스토키",654000,2013,1]
[null,"E1333-333","휴대용","스토키",695000,2011,8]
[null,"B1333-319","휴대용","스토키",750000,2013,8]
[null,"C1993-337","휴대용","퀴디",793000,2012,6]
[null,"D1333-339","휴대용","스토키",835000,2011,18]
[null,"D1993-325","절충형","퀴디",853000,2013,4]
[null,"C1333-317","휴대용","스토키",890000,2013,1]
[null,"E1333-314","휴대용","스토키",890000,2011,8]
[null,"D1993-318","절충형","퀴디",938000,2012,4]
[null,"D1993-316","절충형","퀴디",956000,2012,14]
[null,"D1333-323","절충형","스토키",995000,2011,4]

{"sheet":"시나리오관리자","index":7,"n_rows":19,"n_cols":7,"rows_kept":19,"cut":false,"hidden":false,"merged":[[6,0,6,6],[16,0,16,5]]}
["아래 표에서 환율[G19]이 다음과 같이 변동되는 경우 총판매액 합계[G17]의 변동 시나리오를 작성하시오."]
["▶ 셀이름 정의 : [G17] 셀은 ‘총판매액합계’, [G19] 셀은 ‘환율’로 정의하시오. "]
["▶ 시나리오1 : 시나리오 이름은 ‘환율증가’, 환율을 1250 으로 설정하시오. "]
["▶ 시나리오2 : 시나리오 이름은 ‘환율감소’, 환율을 1050 으로 설정하시오. "]
["▶ 시나리오 요약 시트는 ‘시나리오관리자’ 시트의 바로 앞에 위치시키시오. "]
[]
["수입과자 판매 현황"]
["과자명","현지가격(달러)","수입량","수입가격(원)","판매가격(원)","판매량","총판매액"]
["레몬웨하스",1.8,15000,{"$f":"ROUND(B9*$G$19+(B9*$G$19*25%),-2)","v":2600},{"$f":"ROUND(D9+D9*30%,-2)","v":3400},11352,{"$f":"E9*F9","v":38596800}]
["피넛비스켓",2.4,12000,3500,4600,10248,47140800]
["스피드칩",3.5,10000,5000,6500,9348,60762000]
["키즈옹",1.7,13500,2400,3100,12354,38297400]
["스위트칩",2.5,12000,3600,4700,9758,45862600]
["치즈팡",2.9,11000,4200,5500,8465,46557500]
["다크초코칩",2.4,12000,3500,4600,11235,51681000]
["러브롤링",2.7,13000,3900,5100,12397,63224700]
["합계",null,null,null,null,null,{"$f":"SUM(G9:G16)","v":392122800}]
[]
[null,null,null,null,null,"환율",1150]

{"sheet":"목표값찾기","index":8,"n_rows":16,"n_cols":5,"rows_kept":16,"cut":false,"hidden":false,"merged":[[3,0,3,4]]}
["‘대리점별 판매 현황’ 표에서 최저값인 최은주의 목표달성률(E11)이 85% 가 되려면"]
["판매액(D11)이 얼마가 되어야 하 는지 목표값 찾기 기능을 이용하여 계산하시오."]
[]
["대리점별 판매 현황"]
[]
["대리점","담당자","목표액","판매액","목표달성률"]
["서울","이미영",12000000,10500000,{"$f":"D7/C7","v":0.875}]
["경기","김정훈",15000000,13240000,0.88266666666666671]
["인천","최은정",10000000,8800000,0.88]
["대전","한성호",12000000,10420000,0.86833333333333329]
["천안","최은주",15000000,11250000,0.75]
["부산","남성훈",12000000,10800000.000000002,0.90000000000000013]
["광주","장서연",14000000,12700000,0.90714285714285714]
["원주","이민준",13000000,10540000,0.8107692307692308]
["목포","김민서",15000000,12800000,0.85333333333333339]
["울산","이지훈",12000000,11500000,0.95833333333333337]

{"sheet":"데이터표","index":9,"n_rows":12,"n_cols":6,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[null,"운동화 판매현황 표는 '생산량(C7)'과 '판매량(C8)'을 이용하여 '판매율(C10)'을 계산한 것이다."]
[null,"[데이터표] 기능을 이용하여 판매량의 변동에 따른 판매율의 변화를 [표1]영역에 계산하시오."]
[]
[]
[null,"운동화 판매 현황",null,null,"[표1]"]
[null,"상품명","에어스프링",null,"판매량","판매율"]
[null,"생산량",20000]
[null,"판매량",16258,null,12000]
[null,"재고량",{"$f":"C7-C8","v":3742},null,14000]
[null,"판매율",{"$f":"C8/C7","v":0.81289999999999996},null,16000]
[null,null,null,null,18000]
[null,null,null,null,20000]

{"sheet":"데이터통합","index":10,"n_rows":23,"n_cols":10,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,3],[0,5,0,8],[12,0,12,3],[12,5,12,7]]}
["[표1] 안산시 판매현황",null,null,null,null,"[표2] 수원시 판매현황"]
["제품코드","단가","판매량","판매금액",null,"제품코드","단가","판매량","판매금액"]
["A-001",10000,120,{"$f":"B3*C3","v":1200000},null,"A-001",10000,84,{"$f":"G3*H3","v":840000}]
["A-002",15000,83,1245000,null,"A-002",15000,111,1665000]
["A-003",18000,73,1314000,null,"A-003",18000,48,864000]
["A-004",20000,52,1040000,null,"A-004",20000,93,1860000]
["A-005",16000,48,768000,null,"A-005",16000,27,432000]
["A-006",22000,93,2046000,null,"A-006",22000,86,1892000]
["A-007",24000,27,648000,null,"A-007",24000,57,1368000]
["A-008",25000,86,2150000,null,"A-008",25000,23,575000]
["A-009",27000,43,1161000,null,"A-009",27000,24,648000]
[]
["[표3] 안양시 판매현황",null,null,null,null,"[표4] 경기지역 총판매현황",null,null,null,"각 지점의 판매현황을"]
["제품코드","단가","판매량","판매금액",null,"제품코드","판매량","판매금액",null,"해당하는 제품코드별로"]
["A-001",10000,99,{"$f":"B15*C15","v":990000},null,null,null,null,null,"'판매량' 과 '판매금액'의 평균을"]
["A-002",15000,114,1710000,null,null,null,null,null,"[표4] 의 영역에 계산하시오."]
["A-003",18000,101,1818000]
["A-004",20000,98,1960000]
["A-005",16000,86,1376000]
["A-006",22000,52,1144000]
["A-007",24000,76,1824000,null,"*필요 시 와일드카드( * , ? ) 사용 가능"]
["A-008",25000,68,1700000]
["A-009",27000,55,1485000]

{"sheet":"2급 고급필터","index":11,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["문제: 판매금액이 50000원 이상이고, 분류가 '컴퓨터' 이거나 '사회과학'인 '판매일, 분류, 도서번호, 수량, 판매금액' 만을 "]
["        결과로 표시하시오."]
["        조건은 A22:C24 영역에 작성하시오. "]
["        결과는 A26셀 부터 표시하시오."]
[]
["판매일","고객코드","분류","도서번호","수량","정가","판매금액"]
["2017-02-15","O020","사회과학","S10-152",1,17600,15840]
["2017-01-01","I001","컴퓨터","C10-519",10,19800,188100]
["2017-01-01","O025","소설","J10-441",8,7000,53200]
["2017-01-12","O003","취미/레저","G10-152",5,9900,47025]
["2017-01-22","O015","소설","J20-541",7,8800,58520]
["2017-01-26","O009","컴퓨터","C10-120",2,15000,28500]
["2017-02-03","O050","컴퓨터","C20-250",8,9900,75240]
["2017-02-05","I007","사회과학","S30-414",2,19800,35640]
["2017-02-15","I006","사회과학","S10-152",3,9900,26730]
["2017-03-02","I025","취미/레저","G20-512",3,12000,30600]
["2017-03-05","O050","소설","J10-250",5,19800,94050]
["2017-03-06","O028","취미/레저","G30-474",4,11000,41800]
["2017-03-15","I006","사회과학","S30-541",6,19800,106920]
["2017-03-17","I008","취미/레저","G40-552",1,10000,8500]

{"sheet":"1급 고급필터","index":12,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["문제: 판매금액이 평균 이상이고, 분류가 '컴퓨터' 이거나 '사회과학'인 '판매일, 분류, 도서번호, 수량, 판매금액' 만을 "]
["        결과로 표시하시오."]
["        조건은 A22:C24 영역에 작성하시오. (and, or, average 함수 사용)"]
["        '결과는 A26셀 부터 표시하시오."]
[]
["판매일","고객코드","분류","도서번호","수량","정가","판매금액"]
["2017-02-15","O020","사회과학","S10-152",1,17600,15840]
["2017-01-01","I001","컴퓨터","C10-519",10,19800,188100]
["2017-01-01","O025","소설","J10-441",8,7000,53200]
["2017-01-12","O003","취미/레저","G10-152",5,9900,47025]
["2017-01-22","O015","소설","J20-541",7,8800,58520]
["2017-01-26","O009","컴퓨터","C10-120",2,15000,28500]
["2017-02-03","O050","컴퓨터","C20-250",8,9900,75240]
["2017-02-05","I007","사회과학","S30-414",2,19800,35640]
["2017-02-15","I006","사회과학","S10-152",3,9900,26730]
["2017-03-02","I025","취미/레저","G20-512",3,12000,30600]
["2017-03-05","O050","소설","J10-250",5,19800,94050]
["2017-03-06","O028","취미/레저","G30-474",4,11000,41800]
["2017-03-15","I006","사회과학","S30-541",6,19800,106920]
["2017-03-17","I008","취미/레저","G40-552",1,10000,8500]