2014/10/31

テーブルのすすめ 集計行

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 が使われている。これは、オートフィルターだけでなく、ユーザーが手動で行を非表示にした場合にも対応するためだ。

2014/10/28

テーブルのすすめ 入力規則

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

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

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

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

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




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

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

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



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

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

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


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

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




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

2014/10/24

テーブルのすすめ VLOOKUP関数

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

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

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

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

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

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

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

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


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

テーブルはブックの中でオブジェクトとして扱われ、そのテーブルの中にある列もまたオブジェクトとして扱われる。残念ながら日本語の Excel の場合は作成したテーブルに「テーブル1」といった日本語名が付いてしまうので、関数入力時に構造化参照の恩恵を受けられづらいのだが、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 から始める、としたほうがトラブルは少ない。

[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円と比べると、更新料はかなりお安いのがわかる。

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

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

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

しかし、今後のマイクロソフトの Office 製品/サービスのクラウドへの移行はさらに加速されることになるだろうし、OneDrive/OneDrive for Business の 1TB の容量は魅力である。スマホやタブレットでの利用シーンも多くなることを考えると、企業で Office を利用する場合は、プレインストールやSoloを購入、利用するのではなく、Office のみであれば 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、ノートPC、自宅PCヘのインストールも余裕で可能になる。

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

企業向け Office 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
Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018