MOS受験に向けて２７(データの集計）

今回はデータの集計について書きます。
MOSについては、2003まではExpertの範囲でした。2007で維持的にMCASになったときも範囲に入ってましたが、MOS2010になって範囲から外れたようです。
ただ、データベースの集計を行うときに必要な機能だと思います。

・データの小計
データをグループごとに、例えば店舗ごと、担当別などの集計をおこなうことができます。「小計」を行うには準備が必要。あらかじめ目的のグループに分類するために「並べ替え」を行っておく必要があります。




上のような表において「セミナー名」を「Word2010基本」、「Word2010応用」、「Excel2010基本」、「Excel2010応用」の順に並べ替え、これらのセミナー名が同じ場合には昇順に並べ替えて「セミナー名」グループごとに「売上金額」、「受講者数」の合計を集計します。

まず、並べ替えを行います。
①表の一部を選択した状態で[データ]タブの[並べ替えとフィルター]グループの[並べ替え]クリック
②[並べ替え]ダイアログボックスにおいて、[最優先されるキー]に「セミナー名」選択、「順序」で「ユーザー設定リスト」選択、
③[ユーザ設定リスト]ダイアログボックスにおいて、[リストの項目]欄にWord2010基本」、「Word2010応用」、「Excel2010基本」、「Excel2010応用」と入力、[追加]クリックして[OK]クリック



④[並べ替え]画面に戻って、[レベルの追加]クリックして、[次に優先されるキー]で「開催地」選択、[順序]欄で「昇順」選択して[OK]クリックします。





ここまでは「並べ替え」でした。
ここからいよいよ「データの集計」を行います。

①表が目的のグループごとに並べ替えられていることを確認
②表の任意のセルが選択されている状態で、[データ]→[アウトライン]グループの[小計]クリックして、[集計の設定]ダイアログボックスを表示
③[グループの基準]を「セミナー名」にして、[集計の方法]を「合計」にして、[集計するフィールド]の「受講者数」と「売上金額」にチェックを入れ[OK]クリックすると集計できます。





アウトラインが同時に設定されるので、「-]をクリックして折りたたんで表示させることもできます。



ここで[集計の設定]ダイアログボックスについて説明しておきます。

・[集計の方法]：合計以外にも「データの個数」、「平均」、「最大値」、「最小　　　値」、「積」、「数値の個数」、「標本標準偏差」、「標準偏差」、「標本分散」、　「分散」なども指定することができます。
すなわち、集計を行っているセルにはSUBTOTAL関数が自動的に挿入され、集計されます。
・SUBTOTAL関数：表示しているデータだけ集計する関数で、オートフィルタで表示され　ているデータだけの集計などによく使われます。
　書式：SUBTOTAL(集計方法,範囲1[,範囲2,…範囲254]) (2007,2010)
　集計方法
　

ちなみに、セル[J15]には「=SUBTOTAL(9,J4:J14)」と入力されています。


・[現在の小計をすべて置き換える]：すでぶ集計行を設定している場合に使います。チ　ェックを入れると、現在の集計行が削除され、新規の集計行に置き換わります。

・[グループごとに改ページを挿入する]：チェックを入れると、自動的に改ページが　　挿入されます。

・[集計行をデータの下に挿入する]：チェックを外すとグループの上に集計行が挿入さ　れます。

・[すべて削除]：表からすべての集計行を削除します。アウトラインも解除されます。

では上記の「セミナー名」ごとに集計して表に、さらに「開催地」ごとの「受講者数」と「売上金額」を合計する集計行を追加してみましょう。

①表の任意のセルを選択した状態で
②[データ]タブ→[アウトライン]グループの[小計]クリックして、[集計の設定]ダイアログボックスを表示
③[グループの基準]を「開催地」にして、[集計の方法]が「合計」に、[集計するフィールド]の「受講者数」と「売上金額」にチェックが入っていることを確認して、さらに[現在の小計をすべて置き換える]のチェックを外して[OK]クリックすると集計できます。



結果の表は一部省略しています。

MOS受験に向けて２６(データ統合２：位置、項目による統合）

前回の位置による統合の続きからです。
２．ワークシートの統合
・位置による統合
今回はリンク貼り付けについて述べます。

前回の表を再掲します。









手順は前回と同じですが、選択する範囲に注意が必要です。

①統合先のワークシート（集計）のセル[B4]または、集計しようとするセル全体[B4:D10]選択
②[データ]タブの[]データツール[グループの[統合]クリック、統合の設定]画面を表示させます。
③統合元範囲]の[統合元範囲]ボックスをクリックし、一番左のシート「新宿店」に切り替え、「合計」欄を含むB4:D10の範囲選択し、[追加]クリック、同様に次のシートの渋谷店、池袋店で、範囲が同じB4：D10になっていることを確認し、それぞれ[追加]をクリック、統合の基準[上端行]と[左端列]のチェックを外し、[統合元データとリンクする]にチェックをいれ、[OK]クリックすると集計ができます。





統合元と統合先にリンクが設定され、アウトラインが自動的に作成されます。
この表を通常のコピーをするとつぎのようにすべてが展開された表になります。



項目名の上の３つの欄が新宿店、渋谷店、池袋店の内訳お表示しています。

したがって、見えている状態のまま表示するのであれば、コピーしたい範囲を選択しF５キーを押してジャンプ画面を表示、[セルの選択]クリック、[選択オプション]画面で
[可視セル]にチェックを入れ、[OK]後にコピー(Ctrl+C)、貼り付け（Ctrl+V）を押せば、見えている状態の表をコピーできます。




・項目による統合

統合するワークシートの項目名、その並びや位置が一致していない場合項目を基準に統合することができます。

下記のシート「新宿店」から「池袋店」の合計をシート「集計」に求めてみます。
これぞれのシートは項目名も位置も異なります。










①統合先のワークシート（集計）の基準（開始)位置A3あるいはA3:C10を選択した状態で、
②[データ]タブの[データツール]]グループの[統合]クリック、[統合の設定]画面で一番左のシート「新宿店」に切り替え、上端列、左端列を含み「合計」欄を含むA3:D13の範囲選択し、[追加]クリック、同様に次のシートの渋谷店でA4:D12、池袋店でB３:E１１を選択、それぞれ[追加]をクリックし、
③[統合の基準]の[上端行]、[左端列]にチェックをいれ、[OK]クリック
で合計を求めることができます。





この場合に③の手順で[統合元とリンクする]にチェックを入れると統合先の表に統合元の変更などがリンクされます。

MOS受験に向けて２５(データの統合１:リンク、シートの統合）

今回はデータの統合についてかきます。

1. セルの参照

数式にセルを参照させると、セルの値が変更されると再計算されて計算結果が更新されます。
・セルの参照元
数式では「同じワークシート内」、「同じブック内の異なるシート」、「別のブック」のセルを参照することができます。



2. ワークシートの統合

[データ]→[統合]は、最大255枚までの統合元ワークシートのデータを1つの統合先ワークシートにまとめることができます。

・3D集計（串刺し集計）
統合するワークシートの項目名が同じ並び、同じ位置にある場合の集計によく使われる方法です。下記の4つの表は一つのブックの中にあり、上からシート「新宿店」、「渋谷店」、「池袋店」、「集計」を表します。









これらの表をもとにシート「集計」のセル[D4]に３店舗の合計を求めます。

①シート「集計」の基準のセル[B4]4を選択して、[数式タブのΣボタンクリック。
　(=SUM()と入力される）
②統合元の一番左シートのセル[B4]セルクリック
③Shiftキーを押しながら統合元シートの一番右のシート「池袋店」をクリックし、こ　のとき数式バーには「=SUM('新宿店:池部店'!B4)」と表示されていることを確認して
　Enterキーを押してあとはコピすれば求めることができます。



上記の方法中に、①でセル範囲[B4:C9]を選択して②の操作を行い、③で[数式]タブのΣボタンをクリックするか、あるいはCtrlキーを押しながらEnterをクリックすると一気に求めることができます。

なお、この問題では「合計」を求めましたが、SUM関数の代わりにAVERAGE(平均）、MAX(最大）、MIN(最少）、COUNT(数値の個数）、COUNTA(空白以外の個数）、PRODUCT(引数の積）、VAR（母集団に対する分散）、STDEV(標準偏差）なども使うことができます。


・位置による統合
MOSに関していえばExpertの範囲になります。
統合するワークシートの項目名が同じ並び、同じ位置にある場合（前記の3D集計でもできる）に求める方法です。
前記の3枚の表をもとに集計します。

①統合先のワークシート（集計）のセル[B4]または、集計しようとするセル全体[B4:C9]選択
②[データ]タブの[]データツール[グループの[統合]クリック、統合の設定]画面を表示させます。
③統合元範囲]の[統合元範囲]ボックスをクリックし、一番左のシート「新宿店」に切り替え、B4:C9の範囲選択し、[追加]クリック、同様に次のシート
渋谷店、池袋店で、範囲が同じB4：C9になっていることを確認し、それぞれ[追加]をクリック、統合の基準[上端行]と[左端列]のチェックを外し[OK]クリックすると集計ができます。



位置による統合を使えば、同じ表(店の名前が同じ順）が同じ位置にある必要はありません。
同じ表であれば、位置が変わってもその位置を選択すれば集計できるわけです。

この例では「合計」を算出しましたが、３D集計の場合と同じように、「平均」など多数の集計方法が用意されています。

リンク貼り付けを行う場合には注意が必要です。
次回にふれることにいたします。

MOS受験に向けて２４(データベース４：並べ替え２）

並べ替えの続きです。

前回の漢字の場合について、まとめると[並べ替えオプション]画面で[方法]が初期設定で「ふりがなを使う」にチェックが入っているので、漢字に変換する前の「ふりがな情報」によって決まります。昇順の場合に50音順
ふりがな情報がない場合や[並べ替えオプション]画面で「ふりがなを使わない」にチェックが入っている場合には、文字コード（JIS)のによってきまります。昇順の場合（小→大）
ふりがな情報を調べるには「=PHONETIC(C5)](C5セルには横浜と入力されている）とすると、ふりがな情報がある場合には「ヨコハマ」、ない場合には「横浜」と表示されるはずです。



・アイコンや色を基準にした並べ替え（2007からの新機能）

条件付き書式で適用したアイコンや、セルの背景色、フォントの色などを基に、データを並べ替えることもできます。



上の表は条件津式書式で売り上げ金額の上位10％に緑色の塗りつぶしが、受講率の10％に赤色フォントになるように設定しています。
この表で、


この場合に、[並べ替え]画面で「並べ変えのキー」に[値]を選択すると、「順序」で[昇順]、[降順]、[ユーザー設定リスト]しか選択できませんが「並べ替えのキー」に[セルの色]、[フォントの色]、[セルのアイコン]のどれかを選択すると、下のような画面になります（セルのアイコンは省略）。




ここで、「順序」の右欄に[上]と[下]と選択することができ、[上]を選択すると表の上部に移動させ、[下]を選択すると表の(株)に移動させることができます。特に[下]を選択するということは従来の並べ替えが上から下に配置することと真逆のことになります。
MOSの試験にも出題の可能性があります。FOMの問題集に[上]と[下]に配置する問題が載っています。

上の表で「売上金額」のセルに「緑」の背景色が設定されたレコードを表の「上」に、受講率のセルに「赤色」のフォントが設定されたレコードを「下」に並べ替えてみましょう。
①[データ]タブ→[並べ替えとフィルター]グループの[並べ替え]ボタンクリック
②開いた[並べ替え]画面で下の表のように設定して[OK]クリック



結果は



のようになります。

この問題で、2つのキーともに「上」に、設定した場合には、ともに上部に配置されますが、当然のことながら優先順位の高い方が「上」に配置されます。
2つのキーともにに「下」に、設定した場合には、ともに下部に配置されますが、優先順位の高い方が「下」に配置されます。


・ユーザー設定リストを利用した並べ替え

ユーザー設定リストを使うとユーザーが独自に指定する順次で並べ替えることができます。
すでに登録されている項目以外に新たに項目を追加することもできます。

次の表で「開催地」を「東京」、「神奈川」、「千葉」、「埼玉」、「群馬」、「栃木」、「茨城」の順に並べ替えてみましょう。



①[並べ替え]画面で「順序」で[ユーザー設定リスト]をクリック
②[ユーザー設定リスト]画面が開くので、「リストの項目」欄に「東京」、「神奈川」、「千葉」、「埼玉」、「群馬」、「栃木」、「茨城」と順に入力して[追加]ボタンクリックしたあとに、さらに[OK]クリック
③[並べ替え]画面に戻って、[OK]クリックでできます。(結果は省略）







この場合に項目が10個程度までなら、[ユーザー設定リスト]の[リストの項目]欄に手入力しても、それほどの負担になりませんが、それ以上の場合や化学式などのように入力が面倒な場合には、例えば47都道府県を北から並べたい場合にその一覧を用意して
インポートすることができます。

その場合には[ファイル]タブ→[オプション]、[Excelのオプション]ダイアログボックスが開くので、[詳細設定]タブにおいて、[全般]グループの[並べ替え順や連続データ入力設定]の[ユーザー設定リストの編集]ボタンクリックすると、[ユーザ設定リスト]

ダイアログボックスが開きます。
[インポート]ボタンでこの欄に用意した範囲をドラッグして[インポート]ボタンをクリックすれば、ユーザ設定リストの中に組み込まれ使えるようになります。
この件についてはMOS受験に向けて７(オプション２）でもふれています。

MOS受験に向けて２３（データベース３：並べ替え）

データベースのか3回目は「並べ替え」についてです。
これはMOSの一般の試験範囲で、必ず出題されるでしょう。

リストのデータは、特定の列を基準にして昇順や降順に並べ替えることができます。
並べ替えの基準


漢字の場合に気を付ける必要があります。次の表をごらんください。
ふりがな情報がある場合には、通常は下記のように入力じのふりがなの昇順（アイウエオ順）になります。
「東」を「ひがし」と入力するか、「あづま」と入力して「東」に変換したのかによって異なってきます。
「PHONETIC]という関数を使えばふりがな情報がわかります。



ところがコピー・貼り付けなどによってふりがな情報がない場合、あるいは[データ]タブの[並べ替えとフィルタ]グループの[並べ替え]ボタンをクリックして開く[並べ替え]ダイアログボックスにおいて、[オプション]クリック、[並べ替えオプション]画面の「方法」の[ふりがなを使わない]にチェックを入れた場合には次の表のようになります。



それぞれのコードを「CODE]関数でしらべてみると、上から順に「16446」、「17772」、「18030」、「19276」となり、コード順になっていることがわかります。同時にこのコードから該当する文字を「CHAR(キャラクタ）」関数で調べてみると、それぞれ「西」、「東」、「南」、「北」であることがわかります。

では、次のような場合にはどうでしょうか？


この場合には、つぎのようになります。
最初の「西」のコードは「16446」ですが、2文字目の「山」、「本、「野」がそれぞれ「15155」、「19292」、「19566」と昇順になっていることがわかります。




・1つの列を基準とした並べ替え

1つの列のだけを基準として並べ替えるには、基準となる列内任意のセルをクリックし、[データ]タブの[並べ替えとフィルタ]sグループの[昇順]ボタンや[降順]ボタンクリックすればOKです。
先ほど述べた[並べ替えオプション]画面の「方法」は初期設定で[ふりがなを使う]にチェックが入っています。
ふりがなを使わない場合には[並べ替え]ボタンをクリックして並べ替える必要があります。

・複数の列を基準とした並べ替え
前回で使用した表で開催地を昇順で、開催地が同じ場合には、受講率が高い順に、受講率が同じ場合には売上金額が高い順に並べ替えてみましょう。



①表内の任意のセルをクリック
②[データ]タブの[並べ替えとフィルタ]グループの[並べ替え]ボタンクリックし、[並べ替え]ダイアログボックス表示

③[レべルの追加]ボタンクリックし、[次に優先されるきー]とその設定項目が表示されるので、同様の操作で並べ替えの基準となる列などを指定、「受講率」、「値」、「降順」選択
④3番目の基準「売上金額」について、[レベルの追加］をクリックして基準を追加、[売上金額]、「値」、「降順」を選択し、[OK]クリック




並べ替えの結果を下表のとおりです。



並べ替えには、そのほかユーザ設定リストによるものや、「セルの色」や「フォントの色」、「セルのアイコン」によっても並べ替えることができますが、長くなるので次回にいたします。