情報処理技能検定試験 表計算(1級)のコツ

スポンサーリンク
スポンサーリンク

日本情報処理検定協会が行っている「情報処理技能検定試験 表計算」のためのコツをメモっておきます。 

パータンを見極める!

出題はパターンが決まっています。

1級の場合は、問題文の左上に「入力データ」

右上に「表検索・参照するデータ」があります。

<出力形式1>では「入力データ」および「表検索・参照するデータ」から表を作ります。

次に<出力形式1>で作った表から抽出した表を作ります。

<出力形式2>は、ほぼDSUM関数を使った問題です。

<出力形式3>は、ほぼDSUM、DAVERAGE、DCOUNTなどのデータベース関数を使った問題です。

このパターンを覚えて、どの関数を使えばいいかを考えよう。

また、使われている言葉にもパターンがあります。

「表検索」「VLOOKUPを使いなさい」という意味です。

「参照」はどんな手段を使ってもいいです。VLOOKUP以外でもIFとかその複合とか。

「整数未満四捨五入の表示とする」などの「~の表示とする」はROUND系関数の使用ではなく表示形式の変更でOKです。

スポンサーリンク

時短を心がける!

検定の解答時間は30分。ゆっくりやっていると全然時間が足りません。

少しでも早く作業をするための工夫が必要です。

時間配分の目安としては、

~5分:データ入力

~15分:<出力形式1>完成

~20分:<出力形式2>完成

~25分:<出力形式3>完成

~30分:表の体裁とグラフ

ショートカットを使う

よく使う機能はショートカットキーを覚えましょう。

キー操作 実行されるコマンド
Ctrl+C 選択した文字や画像などをクリップボードにコピー
Ctrl+V クリップボードの内容を貼り付け
Ctrl+X 選択した文字や画像などをクリップボードに切り取り
Ctrl+A すべてを選択
Ctrl+Z 直前の操作を取り消す
Ctrl+Y 直前の操作を繰り返す
F4 直前の操作を繰り返す
Ctrl+Home A1セルに移動する
Ctrl+End データが含まれている最後のセルに移動する
Ctrl+右矢印 連続したデータセルの最終列に移動する
Ctrl+下矢印 連続したデータセルの最終行に移動する
Ctrl+Shift+右矢印 連続したデータセルの最終列まで選択する
Ctrl+Shift+下矢印 連続したデータセルの最終行まで選択する
Ctrl+1(フルキーの数字) セルの書式設定を表示する
F2 選択しているセルを編集モードにする
【以降セル編集時の操作】
Home カーソルを行頭に移動する
End カーソルを行末に移動する
Ctrl+右矢印 1 単語分右へ移動
Ctrl+左矢印 1 単語分左へ移動
Ctrl+Shift+右矢印 1 単語分右を選択
Ctrl+Shift+左矢印 1 単語分左を選択

表の体裁は後回し

表の体裁はあとでまとめてやります。

なおかつ、同じ操作が出てくるので「F4」または「Ctrl+Y」で繰り返しましょう。

オートフィルを使いこなす

特に、<出力形式2>の作表は「複合参照」を使ってオートフィルすると早いです。

関数の入力は「=」から!

関数を入力するときは「関数の挿入(入力フィールドにあるfxのボタン)」だと遅いです。

入力フィールドで=を打つと関数入力になります。また、Excelの補完機能も利用します。

例えば、数値の切り捨てに使う「ROUNDDOWN」を入力したいときは、

1.入力フィールドで「=roun」くらいまで打つ。

2.候補が出てくるので「↓」キーで「ROUNDDOWN」を選択して「TAB」キーを押す。

3.数値(マウスでセルを選択、または、セル番地を直接キーボードから入力)、桁数を入力する。

わからなくなったら、この時点で「Fx」を押してもOK!

パーセントを入力するときのワザ

テーブルでよく出てくるパーセント。数字を打って%(Shift+5)でもいいですが遅いです。

入力セルの書式を%にしてから数字を入力すれば、%を打たなくてもOK!

入力するセルをマウスで選択しておいて、リボンにある「%」をクリック(ショートカットならCtrl+Shift+%)。

セルを選択しているので、Enterキーを押すと、選択範囲内でカーソルが移動します。これも便利です。

確認しながらすすめる!

「売価=定価×0.93(整数未満切り捨て)※定価は<商品テーブル>を参照する」のような処理条件が出題されます。

この場合は、それぞれを分けて考えます。

<商品テーブル>を参照する→VLOOKUPで商品テーブルを検索

整数未満切り捨て→ROUNDDOWNで第二引数は0

それぞれの関数で作ってみて、戻り値(答え)が間違っていないことも確認した方がいいです。

スポンサーリンク

実際にやってみる!

こちらの問題を実際に解いてみます。

1.準備

Excelを起動してA1に受験番号と名前を入力します。

表は一行開けてA3から入力していきます。

2.<入力データ>の内容を<出力形式1>にしたがって入力する

A3に「販売手数料一覧表」

A4から順に見出しの項目を入力します。

3.入力データを表の該当する項目に入力していく

<入力データ>に書かれている内容を「販売手数料一覧表」に入力していきます。この時使えるところはコピペで!

入力後はこんな感じ。

4.各テーブルを入力する

「販売手数料一覧表」の見出しを入力したら、1列あけて、<商品テーブル><乗率テーブル>を入力します。

パーセントの数字は、セルの書式を変更しておこう。

4.表とテーブルができたら、問題を解いていきます。問題は上から順序良くやるのがいいです。

商品名は<商品テーブル>の商品を表検索し、”商品”の文字を関数または演算子を使用して結合しなさい(例:A商品)

この問題は、「表検索」と「結合」の2つをやる必要があります。

なので、分けて考えましょう。

まずは「表検索」と書いてあるので「VLOOKUP」です。

「販売手数料一覧表」の「商品名」の下セル(B5)で「=vl」でvlookupが出てくるのでTABキー。

検索値はCOなので「A5,」カンマを打つと、次の引数入力に移動です。範囲はさっき作った<商品テーブル>なので、「O5:R8」をドラッグ選択して「F4」キーで絶対参照。またカンマを打って、列番号は2。閉じカッコはExcelが付けてくれるので打たなくてもOKですが関数がネストした時などエラーになることもあるので、自分入力するクセをつけよう。

できた関数の戻り値(答え)は「A」が正解。

確認できたら、次は「商品」の文字を連結します。

連結は簡単で、文字&文字と「&」でつなげればOK。

B5のセルを以下のように書き換えます。この時マウスでクリックしてもいいですが「F2」で編集モードにしたほうが早いです。

=VLOOKUP(A5,$O$5:$R$8,2)&”商品”

B5が「A商品」と表示されたら正解なので、オートフィルでB20までコピー。フィルハンドルをダブルクリックで!

等級は、COの右から1文字目とし、関数を使用して求めなさい。

右からと言われたらRIGHT関数です。

「販売手数料一覧表」の「等級」の下セル(C5)で「=ri」でRIGHTが出てくるのでTABキー。

「文字列」はCO(A5)なので「A5,」文字数は1。「X」と表示されたら正解なので、オートフィルで下までコピー。

売価=定価×0.93(整数未満切り捨て)※定価は<商品テーブル>を参照する。

「参照する」なのでVLOOKUP。商品テーブルを見ると、COがキーになっていて定価が決まっていることが分かります。

ここでは、「定価を検索して」「それに0.93かけて」「整数未満切り捨て処理する」というように分けて考えます。

定価の検索は、VLOOKUP。E5セルを選択して「=vl」で出てきたVLOOKUPでTAB。「検索値」はA5、「範囲」はマウスでO5:R8を選択して「F4」で絶対参照。列番号は定価なので3。E5セルに1240が表示されたら正解。

次に、F2でE5セルを編集モードにして「*0.93」と入力。

最後に「整数未満切り捨て処理する」ので、ROUNDDOWNを追加します。

セルが編集モードになっていると思うので、Homeキーを押します。カーソルが行の先頭に移動したら、=の右にカーソルを持って行きたいので、一文字右に矢印キーで移動。=とVの間にカーソルが移ったら「rou」と入力。候補に「ROUNDDOWN」が表示されるのでTABキー。数値は既に入力済みなので、桁数を入れます。Endキーで行末に移動したら、「,0)」と入力してEnterキーで完成です。

なお、関数を複数使った時は、閉じカッコを自分で入力しないとExcelに軽く怒られます。

増減絶対値=前期数量-後期数量 または、増減絶対値=後期数量-前期数量

どっちを計算したらいいかわからなくなりそうですが、絶対値なのでどっちでも答えは一緒です(引っ掛け?)

絶対値はABSという関数を使います。

H5セルを選択して「=ab」で出てきたABSでTAB。「数値」は前期数量-後期数量なので、F5-G5。128が正解。あとはオートフィル。

売上額=売価×(前期数量+後期数量)

これは、式をそのまま入力すればOK。

手数料率は<商品テーブル>を表検索しなさい。(%の少数第1位までの表示とする)

表検索なのでVLOOKUP。<商品テーブル>の検索キーはCOです。

最初に「商品名」を<商品テーブル>で検索したので、この式をコピーして使ってみます。

B5セルをコピーして、J5に貼り付け。「#N/A」と表示されますが気にせず「F2」を押してセル内を編集。まず「&”商品”」はいらないので削除。次に「検索値」が「I5」になっているので「A5」に修正。列番号を2から4にしてOKです。

「%の少数第1位までの表示とする」とあるので、リボンの「数値」にある%と桁数増やすボタンで変更します。

J5が7.3%になったらオートフィル。

手数料=売上額×手数料率(整数未満切り上げ)

ここは計算結果をROUNDUPですね。K5セルで「=rou」で出てきた候補から矢印キーでROUNDUPを選択してTABキー。

「数値」はI5*J5、桁数は0です。答えが出たらオートフィル。

 

乗率は<乗率テーブル>を表検索しなさい。(%の少数第1位までの表示とする)

この問題の山場の一つ。等級と区分のふたつから乗率を検索します。

<乗率テーブル>をよく見ると、区分が10~19まではテーブル内で2列め、20~29は3列め、30~39は4列めになっています。

つまり、区分を10で割った答え(商)に1を足せば、○番めになります。これをVLOOKUPすればOK!

まず、「10で割る」処理を考えます。割り算した答えを切り捨てればいいので、INTまたはROUNDDOWNを使います。

L5セルを選択して「=INT」でINTをTABキー。数値は区分なのでD5、それを10で割るのでD5/10。1と出てきました。試しにオートフィルでコピーして合ってるか見てみます。

良さそうですね。

考え方は合っているので、これに1を足して、VLOOKUPする式を作ります。

L5セルを選択して「F2」で編集モード。「Home」「右矢印」で=の後ろにカーソル移動。「vl」と入力して出てきたVLOOKUPでTAB。「検索値」は等級なのでC5、(カンマを忘れずに)「範囲」はマウスでO12:R14を選択して「F4」で絶対参照。カンマを打って、列番号にはINT(D5/10)の後ろに+1を追加。最後に)で閉じます。書式を%の少数第1位までの表示(1.7%が正解)にしてオートフィルします。

報奨金=売上額×乗率(10位未満切り捨て)

切り捨てなのでROUNDDOWNです。M5セルで「=rou」で出てきた候補から矢印キーでROUNDDOWNを選択してTABキー。

「数値」はI5*L5、10位未満切り捨てなので桁数は-1です。答えが出たらオートフィル。

これで表が出来ました。次は、合計を求めます。

<出力形式1>の例に「合計」欄があるので、この通りに合計を計算します。

出来上がった表を1行空けて、B22セルに「合計」と入力。求める列の22行目をCtrlキーを押しながらクリック。今回はF22、G22、I22、K22、M22です。クリックしたらホームリボンの右上の「オートSUM」をクリックすれば一度の全ての合計値を入力することができます。

<出力形式1>と同じ形式で、後期数量が600以下で手数料が60,000以上を抽出しなさい。

表題は”販売手数料一覧(後期数量が600以下で手数料が60,000以上)”とし、売上額の降順にソートしなさい。

まず、表題を作ります。先ほどの合計行から1行空けた、A24に「販売手数料一覧(後期数量が600以下で手数料が60,000以上)」と入力します。

次に、<出力形式1>の表内をクリック(A5とか)して「並び替えとフィルタ」ー「フィルタ」をクリックします(Alt→H→S→F)。

各項目にフィルタが付くので、「後期数量」をクリックして「数値フィルタ」ー「指定の値以下」をクリックします。

数値を600に設定します。

同様に、手数料が60,000以上も抽出します。

これで表示された表を「販売手数料一覧(後期数量が600以下で手数料が60,000以上)」と書いたセルの下(A25)に貼り付けます。

そして、合計を上の表と同じようにオートSUMします。

次に「売上額の降順にソートしなさい」とあるので、売上額の列のどこか(I26など)をクリックして「並び替えとフィルタ」ー「降順」をクリックします。

抽出した表を「○○の○○順にソートしなさい」もパターンなので、お忘れなく!

最後に、「販売手数料一覧表」のフィルタは不要なので、「並び替えとフィルタ」ー「フィルタ」で設定を解除します(Alt→H→S→F)。

販売手数料一覧表を基に<出力形式2>のような処理をしなさい。

ここはたいていはDSUMを使った問題です。パターンなのでやり方を覚えましょう。

まずは、表を作ります。今回だと、A34に「商品別集計表」A35から横に「商品名」「売上額」「手数料」「報奨金」、A36から縦に「A商品」「B商品」「C商品」「D商品」です。この時、見出しは全て最初に作った表からコピーしてきます。手入力は間違いの元になるので。

なお、商品名は関数で作ったのでそのままコピーするとエラーになります。「値」をコピーしましょう。

やり方は、普通にコピーした後、貼り付ける時に、右クリックして「貼り付けのオプション」から「123」と書かれたアイコンをクリックします。

これで、値がコピーされます。

なお、ショートカットキーでやることもできます。貼り付ける時に「Ctrl+Alt+V」で「形式を指定して貼り付け」ダイアログが表示されます。

「値の貼り付け」は「V」です。確定は「Enter」キー。

もう一つ、「貼り付けのオプション」のやり方もご紹介。

普通に「コピー(Ctrl+C)」「貼り付け(Ctrl+V)」します。

すると、「#REF!」とエラになります。この時、貼り付けたセルの右下にある「」をクリックします(Ctrlキーを押してもOK)。

出てきた一覧から「」を選択します。

一連の作業は、全てキーボードでできます。

コピー:Ctrl+C

貼り付け:Ctrl+V

形式を指定:Ctrl

値を指定:V

これが一番早いかも。お好きな方法でどうぞ。

次は、データベース関数のための準備です。データベース関数は条件をセルに書く必要があります。

「商品別集計表」は商品名をキーにして、売上額、手数料、報奨金の合計を計算します。

合計なのでDSUMを使います。

キーになるのは、商品名なので、こんな条件のセルをコピペで作ります。

では、やり方。

まず「商品名(A35セル)」をコピーします。A41から右に4列ドラックして貼り付けます。

次に、商品の名前(A商品~D商品)も貼り付けます。こちらはコピーして「行列を入替えて」貼り付けます。

以上で、検索条件セルができました。次はDSUM関数を入力します。

B36セルで「=dsu」でDSUMが出てくるのでTABキー。データベースは最初に作った販売手数料一覧表。合計以外を全て選択します。「A4:M20」でF4キーを押して絶対参照にします。次にフィールド。これは集計したい列の名前です。売上額だったら「売上額」なので、上のセルを指定します。この時、あとで右にずらしたいので、行だけ固定しておきます。F4キーを2回押して、列$行とします。最後に条件。先ほど準備した条件セルを指定します。A41:A42で絶対参照。

計算できたらオートフィルで下にコピーします。

次に、A商品以外の行にコピーされた式を修正します。B商品の売上額セル(B37)を選択してF2を押します。

B37セルが編集モードになったら、検索条件の「$A$41:$A$42」を「$B$41:$B$42」に直します。

「$A$41:$A$42」の紫色の枠を「$B$41:$B$42」にドラックすればOK。

同様に、C商品、D商品の売上額セルも修正します。

修正できたら、手数料、報奨金のところにオートフィルします。これで、<出力形式2>が完成です。

最後は<出力形式3>

まずは、<出力形式3>の表を作ります。文字列が長いのでセル結合して下さい。

項目ができたら、データベース関数用の条件セルを作ります。

条件を作るコツは、問題文内の「項目」と「数値」です。

「○○(項目)が××(数値)」となっているところが条件になります。

一番目は「後期数量が500より多く700より少ない」とあるので、これが条件。

「多い」「少ない」「以外」などの比較条件は次のように書きます。

より大きい >
以上 >=
等しい =(省略可)
以下 <=
より小さい(未満) <
以外 <>

今回の問題なら、「500より多く」は「>500」、「700より少ない」は「<700」になります。

 

数値が二つ以上あるときは、条件をその数だけ書きます。これがAND(かつ)なのかOR(または)なのかで少し書き方が変わります。

AND条件は、条件を横並びにします。

項目 項目
条件1 条件2

OR条件は、条件を縦並びにします。

項目
条件1
項目
条件2

「500より多く700より少ない」は「かつ」が隠れているのでAND条件です。

項目は「後期数量」なので、セルに書く条件はこのようになります。

この時の「後期数量」は以前DSUMした時のように、表の見出しからコピーしてきましょう。

あとは問題文を見て、平均なら「DAVERAGE」合計なら「DSUM」件数なら「DCOUNT」を使います(基本はこの3つのどれか)。

では「後期数量が500より多く700より少ない中での売上額の平均」をやってみましょう。

「平均」なので「DAVERAGE」です。M36セルを選択して「=dav」で出てきたDAVERAGEでTAB。「データベース」はマウスでA4:M20を選択して「F4」で絶対参照。「フィールド」は「見出し」なので「売上額」の文字列が入っているセルを選択。このセルはデータベースで選択した中でなくてもOK。近くにあるI25あたりのものでいいです。「条件」は先ほど用意した検索条件セルを絶対参照で指定します。

ここで、データベース(表)を選択するときの小技を。

マウスでドラックするよりも、キーボード操作のほうがラクです。

まず、始点のセルを選択します。

次に、Ctrl+Shift+右矢印キーを押すと、右端まで選択されます。

次に、Ctrl+Shift+下矢印キーを押すと、右下端まで選択されます。これが一番早くて正確!

これで「後期数量が500より多く700より少ない中での売上額の平均」ができました。

次は「手数料が60,000円以上で報奨金が25,000円未満の件数」です。

条件は、こんな感じでExcelに作りましょう。

手数料 報奨金
>=60000 <25000

2つの条件は「かつ」なのでAND条件。条件は横に並べます。

「件数」とあるので、DCOUNTを使います。

ここで、ちょっとでも早く作業するために、コピペを使います。

まず、「後期数量が500より多く700より少ない中での売上額の平均」で作った「=DAVERAGE(~~)」のセルをコピー。

エラーが表示されますが、気にせず、F2で編集モード。

Homeキーを押して、カーソルが行の先頭に移動したら、=DCOUNTと入力したいので、=の右に矢印キーで移動して、「DAVERAGE」を消します。=とDの間にカーソルが移ったら「Ctrl+Shift+右矢印」で「DAVERAGE」を選択して「dc」と入力して出てきた「DCOUNT」でTABキー。条件を作成したセルへドラック、フィールドは、件数の場合どこでもいいので、手数料あたりに修正。以上で「手数料が60,000円以上で報奨金が25,000円未満の件数」が完成です。

最後は「等級がXまたはZの手数料の合計」です。

検索条件は、「等級がX」「等級がZ」で、つなぎは「または」なので、セルを縦に並べます。

等級
X
等級
Z

今回の場合、等級は「X、Y、Z」の3種類なので、「Yではない」という条件でも同じ結果になります。

等級
<>Y

では、ここもコピペでやってみましょう。使う関数は「合計」なので「DSUM」ですね。

まず、「手数料が60,000円以上で報奨金が25,000円未満の件数」で作った「=DCOUNT(~~)」のセルをコピー。

エラーが表示されますが、気にせず、F2で編集モード。

Homeキーを押して、カーソルが行の先頭に移動したら、=DSUMと入力したいので、=の右に矢印キーで移動して、「DAVERAGE」を消します。=とDの間にカーソルが移ったら「Ctrl+Shift+右矢印」で「DCOUNT」を選択して「dsu」と入力して出てきた「DSUM」でTABキー。検索条件を作成したセルへドラック、フィールドは、「手数料」。以上で「等級がXまたはZの手数料の合計」が完成です。

以上で、作成すべき表ができたので、体裁を整えます。

まずは、表題。

「A3」セルに書いた「販売手数料一覧表」を中央揃えにします。

表と同じ列幅のセルだけ選択して「セルを結合して中央揃え」

同じ作業を「販売手数料一覧(後期数量が600以下で手数料が60,000以上)」と「商品別集計表」でもやります。

表と同じ列幅のセルだけ選択したら、F4またはCtrl+Yで繰り返すのがいいです。

次は、罫線。

外枠を太く、内を細く作るのが指定なので、これを一度にやります。

まずは、外枠を書きたい表を選択します。

右クリックして「セルの書式設定」または「Ctrl+1(フルキーの1)」をクリックします。

「セルの書式設定」が表示されるので、「罫線」タブをクリック。

はじめに外枠を引くので、線種を太くして、「外枠」をクリック、続けて、線種を細くして、「内側」をクリック。

「OK」ボタンをクリックすると、表の罫線を一度に引くことができます。

この処理を、F4キーで繰り返すことが出来るので、次の表を範囲選択して「F4」

全部で4つの表の罫線をあっという間に引けます!

次は、見出しを中央揃えにします。

最初の表の見出しを選択して、中央揃え。

あとは、中央揃えしたい見出しを選択してF4です。その時、表の下に書いた「合計」も忘れずに!

最後は、数値の桁です。

基本、数値は3桁区切りにするので、数字が入っているセルをすべて選択して、桁区切りにします。

ここで、数字だけが入っているセルを選択する技を。

まずはシートを全選択。

F5でジャンプを呼び出します。

「セル選択」をクリック

「選択オプション」で「数式」を選択して「文字」のチェックを外して「OK」ボタン。

一部、選択されないセルがあったら、Ctrlキーを押しながらセルをドラックします。

※万能じゃないので、%とかも選択されちゃうことがありますのでご注意を。

 

表の体裁が整ったら、グラフを作ります。

たいていは、<出力形式2>のデータから作成します。

今回は

「<出力形式2>の手数料・報奨金を商品名別の積み上げ縦棒グラフにしなさい」

「タイトルは“手数料・報奨金の比較“とし、凡例は“手数料“・“報奨金“とする」

必要なデータだけをドラックしてグラフを作ります。

その時、凡例になる文字とデータを選びます。

データを選択したら「挿入」ー「グラフ」ー「縦棒」ー「積み上げ縦棒グラフ」を選択。

※キーボードではAlt→N→C

ほぼ完成形のグラフになります。

あとは、タイトルを入力します。

「グラフタイトル」という文字が表示されているのでで、修正します。

「グラフタイトル」のどこでもいいのでクリックして、

Ctrl+Aで全選択後に文字入力するのがいいです。

なお、ここのエリアは右クリックメニューが出てこないので、貼り付けなどをするときはキーボードショートカットでやりましょう。

最後に、表とグラフが重ならないように配置すればOK!

全部出来たら、印刷です。

表計算検定の1級では、1ページに印刷した紙と、データを提出します。

「ファイル」ー「印刷」から印刷の設定を行います。

用紙を「横方向」サイズは「A4」拡大縮小を「シートを1ページに印刷」にするといい感じになります。

印刷プレビューで確認しましょう。

練習すれば30分でできるようになります!がんばりましょう!!

2018年8月追記

報奨金の計算式が間違えておりました。文面と画像を修正いたしました。

ご指摘くださいました方、ありがとうございますm(__)m

 

 

 

 

コメント

  1. 名無し より:

    《出力形式1》報奨金の求め方がまちがっています。手数料×乗率になっています。問題の方では売上額×乗率となっているので、《出力形式3》のDCOUNTの値が間違っています。

    • いろいろメモ より:

      当サイトをご覧くださいましてありがとうございます。また、記事の間違いをご指摘くださいましてありがとうございました。本日、文面と画像を修正いたしました。今後ともよろしくお願いいたします。

タイトルとURLをコピーしました