{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["練習課題","練習解答","課題","課題２","課題３","解答","解答２","解答３","設定"],"not_rendered":{"charts":0,"images":7,"macros":1,"external_links":0,"defined_names":0,"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":48,"n_cols":12,"rows_kept":48,"cut":false,"hidden":false,"merged":[[26,1,26,2],[38,1,39,1],[38,2,38,4],[38,5,38,7],[38,8,38,10]]}
[]
["問題１","達成率と全体比率を求め、小数点以下１桁の百分率で表示させなさい。"]
[null,"なお、達成率は販売目標に対する販売数の比率、全体比率は合計販売数に対する各販売数とする。"]
[]
[null,"品名","販売目標","販売数","達成率","全体比率"]
[null,"商品Ａ",3500,3119]
[null,"商品Ｂ",2500,2981]
[null,"商品Ｃ",2000,2134]
[null,"商品Ｄ",3000,3316]
[null,"商品Ｅ",4000,3992]
[null,"商品Ｆ",3000,2903]
[null,"商品Ｇ",4000,3044]
[null,"合計",{"$f":"SUM(C6:C12)","v":22000},{"$f":"SUM(D6:D12)","v":21489}]
[]
[]
["問題２","販売額、売上、原価、粗利を求め、桁区切りで表示させなさい。"]
[null,"販売額は「定価×売掛率」、売上は「販売額×販売数」、原価は「定価×原価率×仕入数」、粗利は売上と原価の差額とする。"]
[]
[null,"品名","定価","仕入数","販売額","販売数","売上","原価","粗利",null,"売掛率","原価率"]
[null,"商品Ａ",53000,60,null,58,null,null,null,null,0.8,0.6]
[null,"商品Ｂ",24000,100,null,85]
[null,"商品Ｃ",25000,120,null,120]
[null,"商品Ｄ",15000,300,null,295]
[null,"商品Ｅ",45000,50,null,45]
[null,"商品Ｆ",52000,50,null,48]
[null,"商品Ｇ",32000,100,null,70]
[null,"合計",null,{"$f":"SUM(D20:D26)","v":780},0,721,0,0,{"$f":"SUM(I20:I26)","v":0}]
[]
[]
["問題３","売上高と利益を求め、桁区切り形式で表示させなさい。"]
[null,"売価は定価と売掛率の積、売上高は販売数と売価の積、仕入額は仕入価と販売数の積、利益は売上高と仕入額の差とする。"]
[]
[]
[null,"品名","商品Ａ","商品Ｂ","商品Ｃ"]
[null,"定価",52000,20000,25000]
[null,"売掛率",0.85,0.7,0.75]
[null,"仕入価",40000,9800,13500]
[]
[null,null,"販売数",null,null,"売上高",null,null,"利益"]
[null,null,"商品Ａ","商品Ｂ","商品Ｃ","商品Ａ","商品Ｂ","商品Ｃ","商品Ａ","商品Ｂ","商品Ｃ"]
[null,"東京本店",180,144,183]
[null,"東北支店",149,361,128]
[null,"名古屋店",127,120,335]
[null,"日本橋店",287,190,212]
[null,"岡山支店",431,122,410]
[null,"四国支店",393,405,186]
[null,"福岡支店",125,237,295]
[null,"沖縄支店",127,450,114]

{"sheet":"練習解答","index":1,"n_rows":48,"n_cols":12,"rows_kept":48,"cut":false,"hidden":false,"merged":[[26,1,26,2],[38,1,39,1],[38,2,38,4],[38,5,38,7],[38,8,38,10]]}
[]
["問題１","達成率と全体比率を求め、小数点以下１桁の百分率で表示させなさい。"]
[null,"なお、達成率は販売目標に対する販売数の比率、全体比率は合計販売数に対する各販売数とする。"]
[]
[null,"品名","販売目標","販売数","達成率","全体比率"]
[null,"商品Ａ",3500,3119,{"$f":"D6/C6","v":0.89114285714285713},{"$f":"D6/$D$13","v":0.14514402717669506}]
[null,"商品Ｂ",2500,2981,{"$f":"D7/C7","v":1.1923999999999999},{"$f":"D7/$D$13","v":0.13872213690725488}]
[null,"商品Ｃ",2000,2134,1.0669999999999999,9.9306621992647406E-2]
[null,"商品Ｄ",3000,3316,1.1053333333333333,0.15431150821350459]
[null,"商品Ｅ",4000,3992,0.998,0.18576946344641446]
[null,"商品Ｆ",3000,2903,0.96766666666666667,0.13509237284191913]
[null,"商品Ｇ",4000,3044,0.76100000000000001,0.14165386942156452]
[null,"合計",{"$f":"SUM(C6:C12)","v":22000},{"$f":"SUM(D6:D12)","v":21489}]
[]
[]
["問題２","販売額、売上、原価、粗利を求め、桁区切りで表示させなさい。"]
[null,"販売額は「定価×売掛率」、売上は「販売額×販売数」、原価は「定価×原価率×仕入数」、粗利は売上と原価の差額とする。"]
[]
[null,"品名","定価","仕入数","販売額","販売数","売上","原価","粗利",null,"売掛率","原価率"]
[null,"商品Ａ",53000,60,{"$f":"C20*$K$20","v":42400},58,{"$f":"E20*F20","v":2459200},{"$f":"C20*$L$20*D20","v":1908000},{"$f":"G20-H20","v":551200},null,0.8,0.6]
[null,"商品Ｂ",24000,100,{"$f":"C21*$K$20","v":19200},85,{"$f":"E21*F21","v":1632000},{"$f":"C21*$L$20*D21","v":1440000},{"$f":"G21-H21","v":192000}]
[null,"商品Ｃ",25000,120,20000,120,2400000,1800000,600000]
[null,"商品Ｄ",15000,300,12000,295,3540000,2700000,840000]
[null,"商品Ｅ",45000,50,36000,45,1620000,1350000,270000]
[null,"商品Ｆ",52000,50,41600,48,1996800,1560000,436800]
[null,"商品Ｇ",32000,100,25600,70,1792000,1920000,-128000]
[null,"合計",null,{"$f":"SUM(D20:D26)","v":780},196800,721,15440000,12678000,{"$f":"SUM(I20:I26)","v":2762000}]
[]
[]
["問題３","売上高と利益を求め、桁区切り形式で表示させなさい。"]
[null,"売価は定価と売掛率の積、売上高は販売数と売価の積、仕入額は仕入価と販売数の積、利益は売上高と仕入額の差とする。"]
[]
[]
[null,"品名","商品Ａ","商品Ｂ","商品Ｃ"]
[null,"定価",52000,20000,25000]
[null,"売掛率",0.85,0.7,0.75]
[null,"仕入価",40000,9800,13500]
[]
[null,null,"販売数",null,null,"売上高",null,null,"利益"]
[null,null,"商品Ａ","商品Ｂ","商品Ｃ","商品Ａ","商品Ｂ","商品Ｃ","商品Ａ","商品Ｂ","商品Ｃ"]
[null,"東京本店",180,144,183,{"$f":"C$35*C$36*C41","v":7956000},{"$f":"D$35*D$36*D41","v":2016000},3431250,{"$f":"F41-C$37*C41","v":756000},{"$f":"G41-D$37*D41","v":604800},960750]
[null,"東北支店",149,361,128,{"$f":"C$35*C$36*C42","v":6585800},5054000,2400000,{"$f":"F42-C$37*C42","v":625800},1516200,672000]
[null,"名古屋店",127,120,335,5613400,1680000,6281250,533400,504000,1758750]
[null,"日本橋店",287,190,212,12685400,2660000,3975000,1205400,798000,1113000]
[null,"岡山支店",431,122,410,19050200,1708000,7687500,1810200,512400,2152500]
[null,"四国支店",393,405,186,17370600,5670000,3487500,1650600,1701000,976500]
[null,"福岡支店",125,237,295,5525000,3318000,5531250,525000,995400,1548750]
[null,"沖縄支店",127,450,114,5613400,6300000,2137500,533400,1890000,598500]

{"sheet":"課題","index":2,"n_rows":30,"n_cols":10,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["下図のとおりの表を作成しなさい。"]
["条件"]
["・11-13行と15-30行の行高は１５、１４行は行高５"]
["・Ａ列の幅は２、B:J列は自動調整とする"]
["・タイトルは28P、HGP行書体、背景は青で枠線は太線、J15のセルと連動。"]
["・タイトル以外の文字サイズは１０ポイント"]
["・セル装飾は図のとおりとする"]
["・仕入額は売上額に仕入掛率を掛けた額とし、利益は売上から仕入額を引いた額とする。"]
["・データの不足部は全て数式で補うこと。"]
[]
[null,"仕入掛率"]
[null,"商品A","商品B"]
[null,0.8,0.75]
[]
[null,"月","今年",null,null,null,null,null,"前年売上","売上増減"]
[null,null,"売上",null,null,"利益"]
[null,null,"商品A","商品B","合計","商品A","商品B","合計"]
[null,"１月",1473000,1458000,null,{"$f":"C18*(1-$B$13)","v":294599.99999999994},null,null,941400]
[null,"２月",630000,1159000,null,{"$f":"C19*(1-$B$13)","v":125999.99999999997},null,null,1920600]
[null,"３月",786000,1493000,null,157199.99999999997,null,null,916200]
[null,"４月",580000,822000,null,115999.99999999997,null,null,1688400]
[null,"５月",741000,831000,null,148199.99999999997,null,null,2615400]
[null,"６月",608000,591000,null,121599.99999999997,null,null,2070000]
[null,"７月",970000,1493000,null,193999.99999999997,null,null,2539800]
[null,"８月",540000,1445000,null,107999.99999999997,null,null,1202400]
[null,"９月",776000,544000,null,155199.99999999997,null,null,2262600]
[null,"１０月",756000,952000,null,151199.99999999997,null,null,1751400]
[null,"１１月",945000,751000,null,188999.99999999997,null,null,1866600]
[null,"１２月",1177000,1327000,null,235399.99999999994,null,null,1836000]
[null,"計",{"$f":"SUM(C18:C29)","v":9982000},{"$f":"SUM(D18:D29)","v":12866000},0,1996399.9999999998,0,0,21610800,0]

{"sheet":"課題２","index":3,"n_rows":29,"n_cols":8,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
["下図のとおりの表を作成しなさい。",null,null,null,null,"価格・利益表"]
["条件"]
["・タイトルはF1のセル参照とし、MS明朝16P太字とします。"]
["・タイトル以外の文字サイズは１１ポイント"]
["・Ａ列の幅は２、Ｂ列の幅は５、Ｃ～Ｈ列の幅は１４とする"]
["・通常売価は定価から値引率を差し引いた値"]
["・最低売価は定価から最大値引率を引いた値"]
["・利益は売価から仕入価を引いた値とする"]
["・定価と仕入価は図を見て数値を入力し、他は全て数式で補うこと。"]
["・15-25行の行高は12、タイトル行は20"]
[]
[]
[]
[]
[null,"型番","定価（税込）","仕入価格（税込）","通常売価（税込）","最低売価（税込）","通常利益","最低利益"]
[null,"A-1"]
[null,"A-2"]
[null,"A-3"]
[null,"A-4"]
[null,"A-5"]
[null,"A-6"]
[null,"A-7"]
[null,"A-8"]
[null,"A-9"]
[null,"A-10"]
[]
[]
[null,null,null,"値引率",0.1]
[null,null,null,"最大値引率",0.2]

{"sheet":"課題３","index":4,"n_rows":24,"n_cols":14,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
["下図のとおりの表を作成しなさい。"]
["条件"]
["・タイトルは「設定」のシートのA1セル参照とし、MSゴシック18P、行高は24とします。"]
["・B14:N24の文字サイズは10P"]
["・出荷期限は入荷日の３日後とする。"]
["・値引率は「値引額÷定価」とする。"]
["・利益は販売額－仕入額とし、利益率は全利益に対する各型番の利益の比率とする。"]
["・14-24の行高は15、列幅はデータに合わせること。"]
["・データの不足部分は全て数式で補うこと。"]
[]
[]
[]
[]
[null,"型番","入荷日","出荷期限","定価（単価）","売価（単価）","入荷額（単価）","入荷数","出荷数","値引率","仕入額","販売額","利益","利益率"]
[null,"A-100","2006-05-01",null,50000,41500,35000,3,3]
[null,"10-25","2006-05-01",null,480000,395000,336000,2,2]
[null,"10-26","2006-05-01",null,1550000,1512680,1085000,3,2]
[null,"C-101","2006-05-06",null,28000000,28000000,24000000,1,1]
[null,"21-205","2006-05-07",null,50,50,35,120,83]
[null,"C-101","2006-05-07",null,65800,60000,46060,10,9]
[null,"21-206","2006-05-09",null,120,115,84,250,101]
[null,"A-101","2006-05-10",null,52000,43000,38000,25,21]
[null,"A-102","2006-05-16",null,85000,69800,62000,8,8]
[null,null,null,null,null,null,"合計",{"$f":"SUM(H15:H23)","v":422},{"$f":"SUM(I15:I23)","v":230},null,{"$f":"SUM(K15:K23)","v":0},{"$f":"SUM(L15:L23)","v":0},{"$f":"SUM(M15:M23)","v":0}]

{"sheet":"解答","index":5,"n_rows":30,"n_cols":10,"rows_kept":30,"cut":false,"hidden":false,"merged":[[10,1,10,2],[10,4,12,9],[14,1,16,1],[14,2,14,7],[14,8,16,8],[14,9,16,9],[15,2,15,4],[15,5,15,7]]}
["下図のとおりの表を作成しなさい。"]
["条件"]
["・11-13行と15-30行の行高は１５、１４行は行高５"]
["・Ａ列の幅は２、B:J列は自動調整とする"]
["・タイトルは28P、HGP行書体、背景は青で枠線は太線"]
["・タイトル以外の文字サイズは１０ポイント"]
["・セル装飾は図のとおりとする"]
["・仕入額は売上額に仕入掛率を掛けた額とし、利益は売上から仕入額を引いた額とする。"]
["・データの不足部は全て数式で補うこと。"]
[]
[null,"仕入掛率",null,null,{"$f":"J15","v":"売上増減"}]
[null,"商品A","商品B"]
[null,0.8,0.75]
[]
[null,"月","今年",null,null,null,null,null,"前年売上","売上増減"]
[null,null,"売上",null,null,"利益"]
[null,null,"商品A","商品B","合計","商品A","商品B","合計"]
[null,"１月",1473000,1458000,{"$f":"C18+D18","v":2931000},{"$f":"C18-C18*B$13","v":294600},{"$f":"D18-D18*C$13","v":364500},{"$f":"F18+G18","v":659100},941400,{"$f":"E18-I18","v":1989600}]
[null,"２月",630000,1159000,{"$f":"C19+D19","v":1789000},{"$f":"C19-C19*B$13","v":126000},289750,{"$f":"F19+G19","v":415750},1920600,{"$f":"E19-I19","v":-131600}]
[null,"３月",786000,1493000,2279000,157200,373250,530450,916200,1362800]
[null,"４月",580000,822000,1402000,116000,205500,321500,1688400,-286400]
[null,"５月",741000,831000,1572000,148200,207750,355950,2615400,-1043400]
[null,"６月",608000,591000,1199000,121600,147750,269350,2070000,-871000]
[null,"７月",970000,1493000,2463000,194000,373250,567250,2539800,-76800]
[null,"８月",540000,1445000,1985000,108000,361250,469250,1202400,782600]
[null,"９月",776000,544000,1320000,155200,136000,291200,2262600,-942600]
[null,"１０月",756000,952000,1708000,151200,238000,389200,1751400,-43400]
[null,"１１月",945000,751000,1696000,189000,187750,376750,1866600,-170600]
[null,"１２月",1177000,1327000,2504000,235400,331750,567150,1836000,668000]
[null,"計",{"$f":"SUM(C18:C29)","v":9982000},{"$f":"SUM(D18:D29)","v":12866000},22848000,1996400,3216500,5212900,21610800,1237200]

{"sheet":"解答２","index":6,"n_rows":29,"n_cols":8,"rows_kept":29,"cut":false,"hidden":false,"merged":[[13,1,13,7]]}
["下図のとおりの表を作成しなさい。",null,null,null,null,"価格・利益表"]
["条件"]
["・タイトルはF1のセル参照とし、MS明朝16P太字とします。"]
["・タイトル以外の文字サイズは１１ポイント"]
["・Ａ列の幅は２、Ｂ列の幅は５、Ｃ～Ｈ列の幅は１４とする"]
["・通常売価は定価から値引率を差し引いた値"]
["・最低売価は定価から最大値引率を引いた値"]
["・利益は売価から仕入価を引いた値とする"]
["・定価と仕入価は図を見て数値を入力し、他は全て数式で補うこと。"]
["・15-25行の行高は12、タイトル行は20"]
[]
[]
[]
[null,{"$f":"F1","v":"価格・利益表"}]
[null,"型番","定価（税込）","仕入価格（税込）","通常売価（税込）","最低売価（税込）","通常利益","最低利益"]
[null,"A-1",28800,21600,{"$f":"C16-C16*$E$28","v":25920},{"$f":"C16-C16*$E$29","v":23040},{"$f":"E16-D16","v":4320},{"$f":"F16-D16","v":1440}]
[null,"A-2",19800,14850,{"$f":"C17-C17*$E$28","v":17820},{"$f":"C17-C17*$E$29","v":15840},{"$f":"E17-D17","v":2970},{"$f":"F17-D17","v":990}]
[null,"A-3",24800,18600,22320,19840,3720,1240]
[null,"A-4",21000,15750,18900,16800,3150,1050]
[null,"A-5",2200,1650,1980,1760,330,110]
[null,"A-6",8500,6375,7650,6800,1275,425]
[null,"A-7",7500,5625,6750,6000,1125,375]
[null,"A-8",6800,5100,6120,5440,1020,340]
[null,"A-9",9800,7350,8820,7840,1470,490]
[null,"A-10",1000,750,900,800,150,50]
[]
[]
[null,null,null,"値引率",0.1]
[null,null,null,"最大値引率",0.2]

{"sheet":"解答３","index":7,"n_rows":24,"n_cols":14,"rows_kept":24,"cut":false,"hidden":false,"merged":[[11,1,11,13]]}
["下図のとおりの表を作成しなさい。"]
["条件"]
["・タイトルは「設定」のシートのA1セル参照とし、MSゴシック18P、行高は24とします。"]
["・B14:N24の文字サイズは10P"]
["・出荷期限は入荷日の３日後とする。"]
["・値引率は「値引額÷定価」とする。"]
["・利益は販売額－仕入額とし、利益率は全利益に対する各型番の利益の比率とする。"]
["・14-24の行高は15、列幅はデータに合わせること。"]
["・データの不足部分は全て数式で補うこと。"]
[]
[]
[null,{"$f":"設定!A1","v":"利益率計算表"}]
[]
[null,"型番","入荷日","出荷期限","定価（単価）","売価（単価）","入荷額（単価）","入荷数","出荷数","値引率","仕入額","販売額","利益","利益率"]
[null,"A-100","2006-05-01",{"$f":"C15+3","v":"2006-05-04"},50000,41500,35000,3,3,{"$f":"(E15-F15)/E15","v":0.17},{"$f":"G15*H15","v":105000},{"$f":"F15*I15","v":124500},{"$f":"L15-K15","v":19500},{"$f":"M15/$M$24","v":4.8832710403245499E-3}]
[null,"10-25","2006-05-01",{"$f":"C16+3","v":"2006-05-04"},480000,395000,336000,2,2,{"$f":"(E16-F16)/E16","v":0.17708333333333334},{"$f":"G16*H16","v":672000},{"$f":"F16*I16","v":790000},{"$f":"L16-K16","v":118000},{"$f":"M16/$M$24","v":2.9550050397861377E-2}]
[null,"10-26","2006-05-01","2006-05-04",1550000,1512680,1085000,3,2,2.407741935483871E-2,3255000,3025360,-229640,-5.7507403164109211E-2]
[null,"C-101","2006-05-06","2006-05-09",28000000,28000000,24000000,1,1,0,24000000,28000000,4000000,1.0016966236563178]
[null,"21-205","2006-05-07","2006-05-10",50,50,35,120,83,0,4200,4150,-50,-1.2521207795703974E-5]
[null,"C-101","2006-05-07","2006-05-10",65800,60000,46060,10,9,8.8145896656534953E-2,460600,540000,79400,1.9883677979577911E-2]
[null,"21-206","2006-05-09","2006-05-12",120,115,84,250,101,4.1666666666666664E-2,21000,11615,-9385,-2.3502307032536359E-3]
[null,"A-101","2006-05-10","2006-05-13",52000,43000,38000,25,21,0.17307692307692307,950000,903000,-47000,-1.1769935327961735E-2]
[null,"A-102","2006-05-16","2006-05-19",85000,69800,62000,8,8,0.17882352941176471,496000,558400,62400,1.5626467329038558E-2]
[null,null,null,null,null,null,"合計",{"$f":"SUM(H15:H23)","v":422},{"$f":"SUM(I15:I23)","v":230},null,{"$f":"SUM(K15:K23)","v":29963800},{"$f":"SUM(L15:L23)","v":33957025},{"$f":"SUM(M15:M23)","v":3993225}]

{"sheet":"設定","index":8,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["利益率計算表"]