YENLAND TIME :  

 Top >  Library  |  印刷する印刷ページ       はてなブックマークに追加 このページを Google Bookmarks に追加 RSS

大区分

その他

中区分

PC

小区分

エクセル演習(初級)

項目

エクセルテスト(初級:第4回)

入力者 山下章太 更新日 20130821


業務上で、パソコンを使えるか使えないかで、作業効率が大きく変わってきます。
ここでは、当社で社員教育用に実施しているPCテストの一部を公開しています。

難易度も合わせて記載しますので、参考にして下さい。

テストの内容

難易度
ファイル エクセル
内容 基本的な関数の使用
制限時間 5分

ファイルをダウンロード(ZIPファイル)



解説

ダウンロードすると、エクセルファイルが2つ入っています。
問題ファイル:pc_test_4.xls
解答ファイル:pc_test_4a.xls

簡単な関数、ショートカットに関する問題です。これらを使えるかどうかで、大きく作業時間が変わってきます。



解説:問題1

問題1はフィルタで2013年3月の仕訳を抽出する問題です。
フィルタの操作だけでも抽出は可能ですが、ここでは年月を関数で表示してから抽出しています。

ここで利用しているショートカットは、「フィルタ」だけです。

ショートカット 内容
[Alt]+[D]+[S]+[S] フィルタを掛ける

また、文字列を加工するために利用している関数は、以下の通りです。

関数 内容
=LEFT([文字列],[文字数]) [文字列]の左から○文字を表示する
=MID([文字列],[開始位置],[文字数]) [文字列]の左から○番目(開始位置)から○文字(文字数)を表示する
=RIGHT([文字列],[文字数]) [文字列]の右から○文字を表示する

文字列操作をするLEFT関数、MID関数、RIGHT関数は、色んなところで活躍する関数です。
一度習得しておくと、役に立つことが多いと思います。



解説:問題2

次に、問題2では、前回学習したSUMIFの応用版の練習です。

SUMIF関数は「条件に合うものを合計する」タイプの関数ですが、使い方は下記のようになります。

関数 内容
=SUMIF([範囲],[検索条件],[合計範囲]) 選択した条件に合致する値を合計する

この関数は、「条件に合致した数値を合計」するために用いますが、 問題2では「2013年3月」という条件が入っているため、そのままでは利用できません。

SUMIF関数でデータを集計するためには、「勘定科目コード+年月」でコードを作成して集計を行う必要があります。

なお、この問題では仕訳データを部分的に抽出していますので、合計残高試算表の貸借は一致しません。



解説:問題3

次に、問題3は金額上位3件を抽出して、表示する問題です。

解答は、2つの方法で作成しています。
@表の入れ替えを行ったうえでVLOOKUP関数で商品コードを表示する方法
A該当する売上の金額の順番(上から何番目か?)をMATCH関数で取得して、商品コードを表示する方法

まず、数値の「上から○件目」、「下から○件目」を表示する関数は下記の通りです。

関数 内容
=LARGE([範囲],[順位]) 上から○件目を表示する
=SMALL([範囲],[順位]) 下から○件目を表示する

前回、最大値、最小値を表示するMAX関数、MIN関数を説明しましたが、LARGE([範囲],1)は、MAX([範囲])と同じです。
SMALL([範囲],1)は、MIN([範囲])と同じです。

「上から○件」はフィルタでも抽出できますが、「上から○件目」を表示するために、知っておけば便利な関数です。

次に、商品コードを表示する方法の一つとして、VLOOKUP関数を利用する方法を解答では説明しています。

改めて説明の必要はないかも知れませんが、VLOOKUP関数の使用方法は下記の通りです。

関数 内容
=VLOOKUP([検索値],[範囲],[列番号],[検索方法]) 選択した[範囲]の中から[検索値]を発見して、[列番号]の値を表示する

VLOOKUP関数は、選択した[範囲]の一番左から[検索値]を発見しますので、一番左の列に目的の値が入っていなければなりません。
元々の表では、「商品コード」「売上」の順番になっていますので、そのままでは「売上」をキーにして「商品コード」を取得することができません。

このため、下図のように「商品コード」と「売上」の順番を入れ替えて、VLOOKUP関数で「商品コード」を取得しています。



もう一つの解答では、MATCH関数とINDEX関数を利用して数値を取得しています。

MATCH関数は、選択した範囲の「上から何番目に該当の値があるか?」を表示する関数です。

INDEX関数は、選択した[範囲]の[行]と[列]が交差する値を発見する関数です。

関数 内容
=INDEX([配列],[行番号],[列番号]) 選択した[範囲]の[行]と[列]が交差する値を発見して表示する
=MATCH([検索値],[範囲],[照合の種類]) 選択した[範囲]の[検索値]が何番目にあるかを発見して表示する

これらの関数を利用すれば、表をそのままで集計をすることができます。



解説:補足

エクセルの計算式は、上のセルの計算式をコピーして下のセルに貼り付けるようなケースがありますが、 範囲指定している場合は、下に範囲がずれてきますので、「絶対参照」という方法が必要になります。

エクセルのセルは、「列番号(A〜IV)」「行番号(1〜65536)」というようになっています。
例えば、「A1」というセルの列番号(A)を固定したい場合は「$A1」というように、 列番号の前に「$」という絶対参照するための記号を入力します。
行番号(1)を固定したい場合は、「A$1」とします。
行も列も固定したい場合(A1)は、「$A$1」とします。

この指定は、ショートカットキーの「F4」で切り替えをすることができますので、利用してみて下さい。



基本的な操作ですが、習得するだけで、仕事のスピードが変わってきますので、試してみて下さい。





   はてなブックマークに追加 このページを Google Bookmarks に追加 RSS