2014/10/31

[Excel] テーブルのすすめ　集計行

Excel 2007 以降の最も重要な機能追加である「テーブル機能」。
参照先範囲をテーブルにすることで、データの増減に対応することを紹介しました（VLOOKUP, 入力規則）が、今回は「集計行」を取り上げてみます。

Excel で表を作り、その最終行になんらかの計算結果を表示するものを出すことは多いです。


ここで「田中だけの合計」や「商品Aだけの合計」といった絞り込みを行う場合、Excel に慣れ親しんだユーザーはピボットテーブルを使うと思いますが、そこまでしなくても良い場合は「オートフィルター」を使うと思います。表のセルのどれかをアクティブセルにして [データ] タブの [フィルター] を押せば、オートフィルターが表の1行目に設定されます。


このオートフィルターで「担当　田中」で絞り込むと、合計の行が消えます。合計行はデータの範囲に隣接しているため、データ行として扱われるためです。無理やり担当の列が空白の合計行を出すために「空白のセル」を選択すると最終行は表示されますが、今度は合計の計算が元のままなことに気が付きます。


この計算結果が変わらないのも 2003 までの鉄板ネタのひとつで、オートフィルターは該当しない行を「高さ 0 で非表示」にしているだけであり、SUM 関数は指定範囲内の非表示の行の数値も計算してしまうため、表示されている行だけの計算をしません。この時は SUBTOTAL 関数を使え、といえるでしょう。

SUBTOTAL 関数 (Excel 2003 Office Online の解説）

SUBTOTAL 関数（Excel 2007 Office Online の解説）

解説を見て分かるように、合計なのか平均なのか、といったオプション指定を数値で行います。おおよそ 使うのは 3（COUNTA) と 9(SUM) ですが、関数を直接入力すれば数式オートコンプリートが使える数値とその意味を表示してくれるので問題ありません。（繰り返しますが、関数の挿入 ダイアログボックスは使ってはいけません。このような支援を受けられないからです）


ですが、絞り込みによって最終行が消えてしまうのはいただけません。空白を選択したくないケースもあるでしょう。

そこでテーブルの「集計行」の出番です。上記の問題をすべて解決してくれます。

一連の操作を以下で確認してください。


上手の操作を注意深く見ると、集計行で使われている関数は SUBTOTAL 関数ですが、集計方式で 9 ではなく 109 が使われています。これは、オートフィルターだけでなく、ユーザーが手動で行を非表示にした場合にも対応するためです。

テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

2014/10/28

[Excel] テーブルのすすめ　入力規則

Office 2007 以降で最も重要な機能追加は「テーブル機能」です。
テーブル機能を使えば、行数が増えても、列番号が変わっても、範囲指定しなおすことなく、参照を継続できます。VLOOKUP 関数での利用はすでに紹介しました。

実務で Excel ブックを「入力シート」や「申請シート」のように使っている企業・団体は多いですが、そのようなシートをみて非常に残念なのは「データの入力規則」の「リスト - ドロップダウン リストから選択する」の機能を使っていながら、このテーブル機能を活用していないブックが多いことです。（それも参照先に追加が発生するデータ入力をさせているのに「あとは自分で範囲指定しなおせ」的なものも多いです、、、）

ドロップダウン リストから選択させている例

もちろん、これも OFFSET 関数と COUNTA関数を使うことでデータ件数の増減に対応できます。

この入力規則のドロップダウン リストにテーブルを活用していないブックが多い理由はいくつかあると思われます。そもそもテーブル機能を知らない、というのもありますが、この「データの入力規則」のダイアログボックスで構造化参照が直接使えない、というのも大きな理由でしょう。




構造化参照を直接入力できないため利用を諦めているユーザーも多いかもしれません。

ただ、テーブルの範囲をデータの入力規則ダイアログから選択してリストの元データが絶対参照になっていても、絶対参照先が同じワークシートにあるテーブルである場合、行の増減に追随する仕様となっているのです。

この場合は、列名を除いたすべてのデータを範囲として指定します。（列名を含めると当然列名もドロップダウン リストに表示されるためです）



また、対象となるテーブルの列に「名前 (named range)」を付けることでも解決できます。
リスト参照の元データが別のワークシートにある場合はこの名前を利用します。
対象列のタイトル行を除く全データを選択して名前入力ボックスを使って名前を付けるか、タイトル行を含む列の全データを選択して、[数式] タブ [選択範囲から作成] - [上端行] を使って名前を作成し、その名前を「データの入力規則」のリストの「元の値」で入力します。

重要なのはテーブルの行、列を名前として登録するとデータの増減に名前の「範囲」が追随することです。

以下は名前入力ボックスから名前を付け、データの入力規則の元の値に指定し、データを追加した操作です。


Excel の機能の多くはテーブルの構造化参照を使うことができますが、このように直接入力できずエラーになる場合は「名前」を付けて試してみましょう。

なお、上で登録した「コースリスト」という名前は [数式] タブの [名前の管理] から確認できます。
名前の管理でコースリストの参照範囲を見ると「=courseTbl[コースリスト]」という構造化参照になっているのがわかります。この構造化参照に別名を名前でつけた、ということです。



テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

[PR]VBAセミナー受講後は、これさえあれば何もいらない

2014/10/24

[Excel] テーブルのすすめ　VLOOKUP関数

Office 2007 以降で最も重要な機能追加は「テーブル機能」です。
今後 Office 365 にシフトすればするほどその重要性は増しますが、そもそも「テーブル機能」がどれほど強力に Excel を使った業務を支援するか、という点を今回は VLOOKUP関数から紹介したいと思います。

VLOOKUP関数
VLOOKUP(検索値,範囲,列番号,検索の型)

この構文から、[範囲] と [列番号] をどう扱うかは Excel ユーザーにとっては定番ネタですね。

たとえば、Microsoft のサイトでは Excel 2003 向けの記事として以下のようなものがあります。

VLOOKUP, MATCH および INDEX を使用した動的検索

参照するリストの行が増えたら範囲を変更しなくてはならない、参照するリストの途中に列を追加したら列番号を変更しなければならない、というものです。これをワークシート関数を使って自動的に行うのが上記でいう「動的検索」です。

Excel 2007 以降は、参照先リストをテーブルに変換することで、範囲は「テーブル名」となり、行が追加されても追随します。列番号をとるのに列名の文字列を MATCH 関数を使い [#見出し] の構造化参照からとる方法を紹介している記事もいくつかありますが、テーブルの「構造化参照」の特徴を使うのであれば、COLUMN関数で構造化参照の列名を指定するのが「テーブル機能」らしい使い方です。（一番下の(追記)も参照してください）

VLOOKUP(検査値, テーブル名, COLUMN(列名）, 検索の型）


上記で「構造化参照」と言っていますが、関数を「関数の挿入ダイアログ」を使って入力していたらその便利さを見ることはありません。関数の挿入ダイアログは今となっては使ってはいけないと言えます。なぜなら構造化参照を使えないからです。

テーブルはブックの中でオブジェクトとして扱われ、そのテーブルの中にある列もまたオブジェクトとして扱われます。残念ながら日本語の Excel の場合は作成したテーブルに「テーブル１」といった日本語名が付いてしまうので、関数入力時に構造化参照の恩恵を受けられづらいのですが、table1 など英語表記にすると入力の手順が相当変わります。以下、その手順を追ってみましょう。

1) 表を選択して [挿入] タブの [テーブル] をクリックし、テーブルの作成ダイアログで [先頭行をテーブルの見出しとして使用する] にチェックがあるのを確認して [OK] を押します。

2) 作成したテーブルの名前を table1 に変更します

3) 社員番号から名前を VLOOKUP 関数で取得します。その際の操作は以下です。



最終的な数式は以下となりました。

=VLOOKUP(E2,table1,COLUMN(table1[名前]),FALSE)

範囲指定の際に "ta" と打っただけで ta から始まる関数とテーブル名がリストされます。COLUMN関数の引数でも "ta" と打って table1 を選び、その直後に "[" を入れると table1 の列名がリストされるので、そこから [名前] を選び、"]" で閉じます。

これが構造化参照であり、テーブルを使うべき理由のひとつです。

Excel 2003 までの鉄板ネタ(INDEX/MACTHを使ったテクニック）は、Excel 2007 以降はテーブルによって克服され、使い勝手のよい、見やすい数式になっているのです。

なお、テーブル名で日本語が使えないわけではありません。IME 入力による日本語入力のため数式オートコンプリートがすぐに反映しないだけです。「顧客テーブル」というテーブル名を付けた場合は、範囲名で「顧客テーブル」と入れればテーブル名として認識されます。

(追記) COLUMN(テーブル名[列名]) が有効なのは、テーブルの領域が A1 から始まっている場合です。テーブルのようなデータの持ち方の場合は A1 からデータが入力されますが、もし、A列をあけて、1行名をあけるようなデータの持ち方をしている場合や1つのワークシートの複数のテーブルを作った場合は期待した結果にならないことに注意してください。その場合は MATCH 関数を使って [#見出し] の配列から列番号をとることになるが、「テーブル」というデータを扱うのであれば、1シート1テーブル、A1 から始める、としたほうがトラブルは少ないでしょう。

テーブル関連の投稿
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

 [PR] VBAセミナー ベーシック、スタンダードを受講後は、これさえあれば何もいらない
 

2014/10/22

Excel を使いこなすものが Office 365 を使いこなす

Excel の位置づけが大きくかわっている。

Office 2007 から Office アプリケーションの位置づけは大きく変わっていることがうかがわれるが、Office 365 になって初めて現実と理想の方向性が一致した。

Office 365 を使うことにより、クライアント Office アプリケーションとサーバー（サービス）の連携をサーバーの導入を気にしなくてもすぐに使えるようになったからだ。

特に Excel の「テーブル機能」はその「橋渡し」をする重要な機能である。

ただ、残念なことに「テーブル機能」については日本マイクロソフトがそれほど前面に出しておらず、綺麗なリストを作るだけ、と勘違いしている方々も多い。

さらに昔からある機能の「名前」が重要になる。なぜか日本では「名前」という機能名になっているが、英語では "named range" であり、あえて言うならば「名前付き範囲」とでもいうべきか。

この「名前」（混乱するといけないので以降は named range と記す）だが、Excel 2003 まではブック内のグローバル変数のような扱いであった。

しかし、2007以降、テーブル機能が追加されてからは、このテーブルと named range の組みあわせが非常に強力な支援機能として存在している。

そもそもテーブル名も named range である。そして、このテーブルを参照先とした named range の長所は、「範囲が変わっても、再指定する必要がない」ということである。

グラフのデータ範囲にしかり、ピボットテーブルのデータソースの範囲にしかり、入力規則のリストの範囲も、この named range とテーブル機能の組みあわせでメンテナンス性が大きく変わる。

そして、テーブル機能は Excel と他のデータソースとの橋渡しなのだ。

もはやテーブルは Excel のリストではない。範囲でもない。それ自体が Excel の「オブジェクト」として独立している。そして、この「オブジェクト」のデータソースは Excel でなくてもいいのだ。

これが Office 365 との親和性のキーになる。

SharePoint Online のリストやライブラリ、Access Services による SQL Azure の利用も、このテーブル機能（同時にデータ接続や PowerQuery) を媒介して Excel からのアクセスを容易にしているわけである。

とは言え、重要なのは Excel でテーブル機能を使い倒しているか、である。これができていれば、Office 365 との共存・連携をする価値およびその実現性が具体化を帯びる。

逆に Excel を使いこなしていなければ、Office 365 を使いこなすことは難しい。SharePoint Online に無理・無駄・ムラな独自開発を入れ、サービスアップデートで右往左往するだけである。

Excel に始まり、Excel で終わる。Office 365 というクラウドの活用は、実は、Excel に始まり、Excel に終わると感じるのである。

キーワードは
  • テーブル機能
  • Named Range
  • Pivot Table
  • Power Query
  • Power Pivot
である。

なお、Personal や Home and Business は上記の Power BI 系のアドインを使うことができない。この点を注意されたい。
業務で使うのであれば、Professional 以上もしくは Solo だが、できれば Office 365 Business や Enterprise を検討すべきだ。特に　Enterprise の Excel Services は Excel を知っているものにとっては「これが欲しかった！」というであろう。

テーブルは綺麗な表を作るためのものではありません。

[追記] 2014/12/5
マイクロソフトのサポートに確認した方から、Office 365 Solo では Power 系アドインが使えないという情報が入りました。

2014/10/17

ビジネスで、どの Office を購入する？

Office Premium (プレインストール パソコン用）と Office 365 Solo が 2014年10月17日より販売が開始された。

http://www.microsoft.com/ja-jp/news/Press/2014/Oct14/141001_Office.aspx

Personal Premium や Home & Business Premium (いずれもプレインストール パソコン用）は個人・家庭向けという位置づけなので割愛するが、実はビジネス向け Office という点では Professional Premium と Solo の他にもう一つの SKU が存在する。

Office 365 Pro Plus


月額 1,160円のサブスクリプションであり、位置づけ的には Office 365 Solo と同じように見える。

が、すでに Office 365 を使っている人は「ん？」と思うだろう。
現在、マイクロソフトのクラウドサービスを使うには2つの認証方法・IDが存在する
  • Microsoft アカウント （Windows へのログイン、ストア利用、OneDrive 利用など）
  • 組織アカウント　（Office 365用）
Office 365 へのサインインは組織アカウントを使うのだが、さすがにコンシューマーに .onmicrosoft .com の組織アカウント用のドメインを作成し、メールアドレスを作成し、それを組織アカウントとして登録する手順を踏ませるわけはないだろうから、各 Premium や Solo は組織アカウントではなく、 Microsoft アカウントで利用されるものと考えられる。

その点さすがはマイクロソフト、すでに Solo のインストール方法に関するコンテンツが公開されている。

http://www.microsoft.com/ja-jp/office/setup/solo/default.aspx

ここからもわかるように、Microsoft アカウントで「マイアカウント ページ」を作成し、そこから Office 365 Solo がクイック実行インストール可能になる。


また、OneDrive は 1TB という容量であるが、あくまで Microsoft アカウントを利用したコンシューマー向けの OneDrive であり、SharePoint Online の OneDrive for Business ではない。Office Premium のすべてと Solo で 1TB の OneDrive の利用ができるが、それはあくまでコンシューマーベースの OneDrive の容量を大幅にアップさせたものだと推測できる。ちなみに通常のコンシューマー向け OneDrive で 1TB ブランを購入すると月額 6.99USD(日本円 108 円/USD 換算で 754円、年間で 9,054円）であり、Professional Premium の2年目以降の OneDrive+Skype の年間更新料 6,264円と比べると、更新料はかなりお安いのがわかる。

Ｏｆｆｉｃｅ Premium 搭載 PC 専用 Office 365 (OneDrive+Skype) サービスの更新

また、現実には個人のユーザーが Office 365 Pro Plus を購入する理由は全くなく、会社として Office 365 Pro Plus を購入し、社員に配布するのが本来の使い方であり、それゆえに組織アカウントと連動し、Ｏｆｆｉｃｅ だけでなく、SharePoint Online や Exchange Online/Outlook のメールアカウントを利用したくなった時にサービス追加、ライセンス割り当てによりスムーズに移行するのが、Office 365 Pro Plus 提供の目的である。

5人～20人程度の小企業の場合は悩まれると思う。プレインストールによる Office の利用も多いだろうし、ソフトウェア費用が安くなるとはいえボリュームライセンスで Office や Windows を購入しているところは稀であろう。

しかし、今後のマイクロソフトの Office 製品/サービスのクラウドへの移行はさらに加速されることになるだろうし、OneDrive/OneDrive for Business の 1TB の容量は魅力である。スマホやタブレットでの利用シーンも多くなることを考えると、企業で Office を利用する場合は、プレインストールやSoloを購入、利用するのではなく、Ｏｆｆｉｃｅ　のみであれば Office 365 Pro Plus や Office 365 Business の利用も検討したほうがよいと言えるだろう。

なお、価格的には、Office 365 Pro Plus よりも Office 365 Business をまずは検討すべきである。（Access を使わないのであれば）月額 800円/ユーザー・月（年間契約）で、Office と OneDrive for Biz (1TB) の利用が可能になる。(Office 365 Pro Plus は OneDrive for Biz はない）また、一人あたり 5ライセンス(Windows)が利用可能になるため、オフィスのPC、ノートＰＣ、自宅ＰＣヘのインストールも余裕で可能になる。

以下のマイクロソフトの Web ページが良く纏められているので参考にされたい。

企業向け Ｏｆｆｉｃｅ 365 の比較表
http://office.microsoft.com/ja-jp/business/FX104051403.aspx

個人/家庭向け Office の比較表
http://office.microsoft.com/ja-jp/buy/FX102898564.aspx

しかし、、、利用者数の上限以外に、Access が選択のキーになろうとは。
Office 365 の Access は SharePoint の Access アプリにより大きくその可能性が広がったため、キーになりうる製品であるだろう。JETエンジンを使わず SQL Azure をデータベースエンジンとして使う、と言えば、ピンと来る人がいるだろう。

[追記]
Office 365 における Office クライアント単体プランの比較 が公開されました。
http://community.office365.com/ja-jp/b/office_365_community_blog/archive/2014/10/18/comparison-of-office-365-client-standalones.aspx

[追記]
マイクロソフトから OneDrive の容量制限がなくなり無制限で利用できるアナウンスがされた。
http://blogs.technet.com/b/microsoft_office_/archive/2014/10/29/onedrive-now-with-unlimited-storage-for-office-365-subscribers.aspx
登録: 投稿 (Atom)
Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。