情報処理技能検定試験表計算（１級）のコツ

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

パータンを見極める！

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

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

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

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

次に＜出力形式１＞で作った表から抽出した表を作ります。

＜出力形式２＞は、ほぼDSUM関数を使った問題です。

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

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

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

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

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

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

時短を心がける！

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

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

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

～５分：データ入力

～１５分：＜出力形式１＞完成

～２０分：＜出力形式２＞完成

～２５分：＜出力形式３＞完成

～３０分：表の体裁とグラフ

ショートカットを使う

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

キー操作	実行されるコマンド
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」で繰り返しましょう。

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

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

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

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

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

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

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

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

３．数値（マウスでセルを選択、または、セル番地を直接キーボードから入力）、桁数を入力する。

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

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

テーブルでよく出てくるパーセント。数字を打って%（Shift＋５）でもいいですが遅いです。

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

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

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

確認しながらすすめる！

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

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

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

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

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

実際にやってみる！

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

１．準備

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

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

２．＜入力データ＞の内容を＜出力形式１＞にしたがって入力する

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

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

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

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

入力後はこんな感じ。

４．各テーブルを入力する

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

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

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

商品名は＜商品テーブル＞の商品を表検索し、”商品”の文字を関数または演算子を使用して結合しなさい（例：A商品）

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

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

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

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

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

できた関数の戻り値（答え）は「Ａ」が正解。

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

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

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

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

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

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

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

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

「文字列」はCO（A5)なので「A5,」文字数は１。「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。

手数料率は＜商品テーブル＞を表検索しなさい。（％の少数第１位までの表示とする）

表検索なのでVLOOKUP。＜商品テーブル＞の検索キーはＣＯです。

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

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

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

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

手数料＝売上額×手数料率（整数未満切り上げ）

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

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

乗率は＜乗率テーブル＞を表検索しなさい。（％の少数第１位までの表示とする）

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

＜乗率テーブル＞をよく見ると、区分が１０～１９まではテーブル内で２列め、２０～２９は３列め、３０～３９は４列めになっています。

つまり、区分を１０で割った答え（商）に１を足せば、○番めになります。これをVLOOKUPすればOK!

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

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

良さそうですね。

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

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

報奨金＝売上額×乗率（１０位未満切り捨て）

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

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

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

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

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

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

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

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

次に、＜出力形式１＞の表内をクリック（A5とか）して「並び替えとフィルタ」ー「フィルタ」をクリックします（Alt→H→S→F）。

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

数値を600に設定します。

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

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

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

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

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

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

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

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

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

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

やり方は、普通にコピーした後、貼り付ける時に、右クリックして「貼り付けのオプション」から「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キーを２回押して、列$行とします。最後に条件。先ほど準備した条件セルを指定します。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商品の売上額セルも修正します。

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

最後は＜出力形式３＞

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

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

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

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

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

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

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

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

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

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

項目	項目
条件１	条件２

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

項目

条件１

項目

条件２

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

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

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

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

では「後期数量が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、Y、Z」の３種類なので、「Yではない」という条件でも同じ結果になります。

等級

<>Y

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

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

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

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