ルックアップ、チャート、統計、およびピボットテーブル

基本機能、セル参照、および日付と時刻の機能を確認したので、今度はMicrosoft Excelのより高度な機能のいくつかに飛び込みます。私たちは、財務、売上報告書、配送料、統計に関する古典的な問題を解決する方法を紹介します.

学校のナビゲーション

なぜ式や関数が必要なのか?
式の定義と作成
相対セル参照と絶対セル参照、および書式設定
知っておくべき便利な機能
ルックアップ、チャート、統計、およびピボットテーブル

これらの機能は、ビジネス、学生、そして単にもっと学びたい人にとって重要です。.

VLOOKUPとHLOOKUP

これは垂直参照（VLOOKUP）と水平参照（HLOOKUP）関数を説明するための例です。これらの関数は、数値やその他の値をわかりやすいものに変換するために使用されます。たとえば、VLOOKUPを使用して部品番号を取得し、商品の説明を返すことができます。.

これを調査するために、パート4の「Decision Maker」スプレッドシートに戻りましょう。ここでは、Janeが学校に何を着るかを決めようとしています。彼女は新しいボーイフレンドを上陸させたので、彼女はもはや自分が着ているものには興味がありません。.

Janeのスプレッドシートで、彼女は衣装を縦の列と靴、横の列に並べています。.

彼女はスプレッドシートを開き、関数RANDBETWEEN（1,3）は、着ることができる3つのタイプの服装に対応して、1から3の間の数を生成します。.

彼女は5種類の靴の中から選ぶために関数RANDBETWEEN（1,5）を使います.

Janeは数字を身につけることができないので、これを名前に変換する必要があります。そのため、ルックアップ関数を使用します。.

VLOOKUP関数を使用して、服装番号を服装名に変換します。 HLOOKUPは靴の番号から並んで様々な種類の靴に変換します.

スプレッドシートは、服装では次のように機能します。

彼女は3つの衣装オプションを持っているので、Excelは1から3までの乱数を選びます.

次に、式は= VLOOKUP（B11、A2：B4,2）を使用して数値をテキストに変換します。これは、乱数を使用してB11からの値をA2：B4の範囲で検索します。次に、2列目にリストされているデータから結果（C11）を得ます。.

今回はHLOOKUPではなくVOOKUPを使用していますが、同じ方法で靴を選ぶこともできます。.

例：基本的な統計

ほとんどすべての人が統計から1つの式 - 平均 - を知っていますが、ビジネスにとって重要なもう1つの統計があります。標準偏差.

たとえば、大学に通ったことのある人の多くは、SATスコアに苦しんでいます。彼らは他の学生と比較してどのようにランク付けされているか知りたいと思うかもしれません。多くの大学、特に一流大学はSATスコアが低い学生を辞退させるため、大学もこれを知りたい.

では、私たち、あるいは大学はどのようにしてSATスコアを測定し、解釈するのでしょうか。以下は、1,870人から2,230人までの5人の生徒のSATスコアです。.

理解するべき重要な数字は以下のとおりです。

平均 - 平均は「平均」とも呼ばれます。

標準偏差（STDまたはσ） - この数字は、数字の集合がどれほど広く分散しているかを示しています。標準偏差が大きい場合は数値が大きく離れており、ゼロの場合はすべての数値が同じです。標準偏差は、平均値と実測値の差の平均、つまり1,998と各SATスコアであると言えます。ギリシャ語のシグマ「σ」を使用して標準偏差を省略するのが一般的です。

百分位数ランク - 学生が高得点を受けたとき、彼らは彼らがトップ99パーセンタイルまたはそのようなものであることを自慢することができます。「パーセンタイルランク」とは、スコアの割合が特定のスコアより低いことを意味します。.

標準偏差と確率は密接に関連しています。標準偏差ごとに、その数が標準偏差の数の範囲内にある確率または尤度は、次のようになります。

STD	スコアの割合	SATスコアの範囲
1	68％	1,854-2,142
2	95％	1,711〜2,285
3	99.73％	1,567〜2,429
4	99.994％	1,424-2,572

ご覧のとおり、SATスコアが3 STDを超えている可能性は事実上ゼロです。これは、99.73パーセントのスコアが3 STD以内であるためです。.

スプレッドシートをもう一度見て、その仕組みを説明しましょう。.

それでは、式について説明します。

= AVERAGE（B2：B6）

B2：B6の範囲ですべてのスコアの平均。具体的には、すべてのスコアの合計をテストを受けた人の数で割ったもの.

= STDEV.P（B2：B6）

範囲B2：B6の標準偏差。「．Ｐ」は、ＳＴＤＥＶ．Ｐがすべてのスコア、すなわちサブセットだけではなく全母集団にわたって使用されることを意味する。.

= PERCENTRANK.EXC（$ B $ 2：$ B $ 6、B2）

これにより、SATスコアに基づいてB2：B6の範囲にわたる累積パーセンテージ、この場合はB2が計算されます。たとえば、83％のスコアがWalkerのスコアを下回っています.

結果のグラフ化

結果をグラフにすると結果がわかりやすくなるだけでなく、プレゼンテーションに表示して自分の主張をより明確にすることもできます。.

学生は横軸にあり、彼らのSATスコアは1,600から2,300までのスケール（縦軸）上に青い棒グラフとして表示されます。.

百分位数の順位は、0から90パーセントの右側の縦軸で、灰色の線で表されます。.

チャートを作成する方法

チャートを作成すること自体はトピックですが、上記のチャートがどのように作成されたかについて簡単に説明します。.

まず、チャートに表示するセル範囲を選択します。この場合はA2からC6になります。なぜなら、生徒の名前だけでなく数字も欲しいからです。.

「挿入」メニューから「チャート」 - >「推奨チャート」を選択します。

コンピューターは、 "クラスター化された柱、二次軸"のチャートを推奨します。「二次軸」の部分は、2つの垂直軸を描くことを意味します。この場合、このチャートが欲しいものです。他に何もする必要はありません.

チャートを移動してサイズを変更し、希望の位置に移動することができます。満足したら、スプレッドシートにチャートを保存できます.

グラフを右クリックしてから[データの選択]を選択すると、その範囲に対して選択されているデータが表示されます。.

「推奨チャート」機能では、通常、含めるデータ、ラベルの割り当て方法、左右の垂直軸の割り当て方法など、複雑な詳細情報を扱う必要がありません。.

[データソースの選択]ダイアログで、[凡例エントリ（シリーズ）]の下の[スコア]をクリックし、[編集]を押して[スコア]と表示されるように変更します。

次に、系列2（「百分位数」）を「百分位数」に変更します。

あなたのチャートに戻って、「チャートタイトル」をクリックして、それを「SATスコア」に変更してください。これで、完成したチャートが完成しました。 2つの横軸があります。1つはSATスコア用（青）、もう1つは累積パーセンテージ用（オレンジ）です。.

例：交通問題

輸送問題は、「線形計画法」と呼ばれる一種の数学の典型的な例です。これにより、特定の制約を受ける値を最大化または最小化できます。さまざまなビジネス上の問題に対して多くのアプリケーションがあるため、その機能を学ぶのに役立ちます。.

この例を始める前に、「Excelソルバー」を有効にする必要があります。

ソルバーアドインを有効にする

[ファイル] - > [オプション] - > [アドイン]を選択します。アドインオプションの下部にある[管理：Excelアドイン]の横にある[移動]ボタンをクリックします。

表示されたメニューで、[ソルバーアドイン]を有効にするチェックボックスをクリックし、[OK]をクリックします。

例：iPadの最低配送料を計算する

我々がiPadを出荷していて、我々が可能な限り低い輸送費を使って我々の流通センターを満たすことを試みていると仮定しよう。上海、北京、香港からiPadを下記の配送センターに配送することをトラック会社と提携しています。.

各iPadの出荷価格は、工場から物流センターまでの距離から工場までの距離を20,000キロメートルで割ったものです。たとえば、上海からメルボルンまでの距離は8,024 kmで、iPad 1台につき8,024 / 20,000、つまり.40ドルです。.

問題は、これら3つの工場からこれら4つの仕向地まで、可能な限り低いコストでこれらすべてのiPadをどのように出荷するかです。?

あなたが想像できるように、これを理解することは何らかの公式と道具なしでは非常に難しいかもしれません。この場合、462,000（F12）個のiPadを出荷する必要があります。植物は500,250（G12）ユニットの限られた容量を持っています.

スプレッドシートでは、動作を確認できるように、セルB10に1を入力しました。上海からメルボルンに1台のiPadを出荷する必要があります。そのルートに沿った交通費はiPadあたり0.40ドルなので、合計コスト（B17）は0.40ドルです。.

数値は、機能= SUMPRODUCT（原価、出荷）を使用して計算されました。「原価」は、範囲B3：E5です。.

そして「出荷される」の範囲はB9：E11です。

SUMPRODUCTは、「コスト」に「出荷済み」の範囲を掛けたものです（B14）。これは「行列乗算」と呼ばれます。

SUMPRODUCTが正しく機能するためには、2つのマトリックス（コストと出荷済み）が同じサイズでなければなりません。配列が同じサイズになり、総コストに影響が出ないように、追加コストを作成し、列と行をゼロ値で出荷することで、この制限を回避できます。.

ソルバーを使う

私たちがしなければならなかったのは、マトリックスの「コスト」×「出荷」を掛け合わせることであり、それほど複雑ではありませんが、そこでは制約も同様に扱う必要があります.

各流通センターが必要とするものを出荷する必要があります。この定数をソルバーに次のように入れます。$ B $ 12：$ E $ 12> = $ B $ 13：$ E $ 13つまり、出荷されたものの合計、つまりセル内の合計$ B $ 12：$ E $ 12は、各流通センターが必要とするもの（$ B $ 13：$ E $ 13）以上でなければなりません。.

我々が生産する以上に出荷することはできません。その制約を次のように書きます。$ F $ 9：$ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.

それでは「データ」メニューに行き、「ソルバー」ボタンを押してください。「ソルバー」ボタンが表示されていない場合は、ソルバーアドインを有効にする必要があります。.

前述の2つの制約を入力し、「出荷」範囲を選択します。これは、Excelに計算させたい数値の範囲です。また、デフォルトのアルゴリズム "Simplex LP"を選択し、セルB15（ "合計配送料"）を "最小化"し、 "目的を設定"と表示します。

「解決」を押すと、Excelが結果をスプレッドシートに保存します。これを保存して他のシナリオで遊ぶこともできます.

解決策を見つけることができないとコンピューターが言っているなら、あなたは論理的ではない何かをしました、例えば、あなたは植物が生産することができるより多くのiPadを要求したかもしれません.

ここでExcelはそれが解決策を見つけたと言っています。「OK」を押して解決策を保存してスプレッドシートに戻る.

例：正味現在価値

会社はどのようにして新しいプロジェクトに投資するかどうかを決定しますか？「正味現在価値」（NPV）が正であれば、投資します。これは、ほとんどの金融アナリストが採用している標準的なアプローチです。.

たとえば、Codelcoの鉱山会社がAndinas銅山を拡張しようとしているとします。プロジェクトを進めるかどうかを判断するための標準的な方法は、正味現在価値を計算することです。もしNPVがゼロより大きければ、（1）時間と（2）資本コストの2つの投入を考えると、プロジェクトは有益である.

平易な英語では、資本コストとは、銀行に預けた場合にその金額がいくら稼げるかを意味します。現在価値に現金価値を割り引くために資本コストを使用します。つまり、5年間で100ドルが今日の80ドルになる可能性があります。.

初年度には、4,500万ドルがプロジェクトの資金調達のための資本として確保されています。会計士は、資本コストが6％であると判断しました.

彼らが採掘を始めると、会社が生産する銅を見つけて売るので、現金が入ってきます。明らかに、彼らがより多くのものを採掘すればするほど、彼らはより多くのお金を稼ぎ、そして彼らの予測はそれが年間900万ドルに達するまで彼らのキャッシュフローが増加することを示す。.

13年後のNPVは3,945,074米ドルなので、このプロジェクトは黒字になります。金融アナリストによると、「返済期間」は13年です。.

ピボットテーブルの作成

「ピボットテーブル」は基本的にレポートです。ピボットテーブルと呼びます。新しいレポートを作成しなくても、レポートの種類を簡単に切り替えることができるからです。ので ピボット 所定の位置に。基本概念を教える基本的な例を見てみましょう.

例：売上レポート

営業担当者は非常に競争力があるため（セールスマンになるための一部です）、四半期の終わりと年の終わりにお互いにどのように対決するのか、さらに手数料はいくらになるのかを知りたいのです。.

Carlos、Fred、Julieの3人の販売員がすべて石油を販売しているとします。以下のスプレッドシートに、2014年の四半期ごとの売上高をドルで示します。.

これらのレポートを生成するために、ピボットテーブルを作成します。

[挿入] - > [ピボットテーブル]を選択します。これはツールバーの左側にあります。

次に示すように、すべての行と列（セールスマン名を含む）を選択します。

スプレッドシートの右側にピボットテーブルダイアログボックスが表示されます。.

ピボットテーブルダイアログボックスの4つのフィールド（四半期、年、売上、および営業担当）をすべてクリックした場合、Excelではスプレッドシートに意味のないレポートが追加されますが、その理由は?

ご覧のとおり、レポートに追加する4つのフィールドすべてを選択しました。 Excelのデフォルトの動作では、テキストフィールドで行をグループ化してから、残りのすべての行を合計します。.

ここでは、2014年+ 2014年+ 2014年+ 2014年= 24,168年の合計が意味をなさない。また、1 + 2 + 3 + 4 = 10 * 3 = 3 0の4分の1の合計も得られます。この情報は必要ないので、これらのフィールドを選択解除してピボットテーブルから削除します。.

"Sum of Sales"（総売上）が適切なので、修正します。.

例：セールスマンによる販売

「Sum of Sales」を編集して「Total Sales」と言うことができます。これはより明確です。また、他のセルをフォーマットするのと同じように、セルを通貨としてフォーマットすることもできます。まず「Sum of Sales」をクリックして、「Value Field Settings」を選択します。

ダイアログが表示されたら、名前を「Total Sales」に変更してから、「Number Format」をクリックして「Currency」に変更します。

これでピボットテーブルに手仕事が表示されます。

例：セールスマンおよび四半期別の売上

それでは、四半期ごとに小計を追加しましょう。小計を追加するには、「四半期」フィールドを左クリックし、それを押したまま「行」セクションにドラッグします。下のスクリーンショットで結果を見ることができます。

作業中に、「Sum of Quarter」の値を削除しましょう。矢印をクリックして[フィールドの削除]をクリックするだけです。スクリーンショットでは、各営業担当者の売上を四半期ごとに分類した[Quarter]行が追加されています。.

これらのスキルを念頭に置いて、自分のデータからピボットテーブルを作成することができます。!

結論

まとめて、私たちはあなたがあなたのビジネス、学術、または他のニーズにMicrosoft Excelを適用できるMicrosoft Excelの式と機能のいくつかの特徴をあなたに示しました.

ご存じのとおり、Microsoft Excelは非常に多くの機能を備えた非常に優れた製品であるため、ほとんどの人は、上級ユーザーでさえも、それらすべてを知っているわけではありません。一部の人々はそれがそれを複雑にすると言うかもしれません。私たちはそれがより包括的だと感じます.

実例をいくつか紹介することで、Microsoft Excelで利用可能な関数だけでなく、統計、線形計画法、グラフの作成、乱数の使用、および現在採用可能なその他のアイデアについても説明しました。学校や職場で使用する.

覚えておいてください、あなたが戻って再びクラスを受講したい場合は、レッスン1から新たに始めることができます。!