2015/11/22

Excel でゼロ パディングしたい – 0で埋める

一般にプログラミングの世界などで、足りない桁数分「0」を追加して長さを合わせることを「ゼロ パディング」と言います。
Excel では 0001 が 1 になる、といった「ゼロがなくなる」ことがよく話題になります。これをゼロ サプレスといいますが、あまりこちらの言葉は聞きませんね。
今回は Excel でのゼロ パディングの3つのポイントをご紹介します。

文字列設定で反映

0001 を 1 ではなく 0001 としてセルで扱うとなると、いくつかの方法があり、そのひとつは、入力されたデータを文字列として扱う方法です。
そのため、セルの書式を「文字列」に設定することが「第1のポイント」です。
  • 設定したいセル・範囲を選択する
  • Ctrl+1 で「セルの書式設定」ダイアログの「表示形式」で「文字列」を選択して [OK]
これで、 0001 と入力しても、1 になることはありません。シングルコーテーションを 0001 の前に入力する方法も紹介されることが多いのですが、セル書式設定本来の機能からすると、入力するセルの位置・範囲が決まっているのであれば、セルの書式設定による文字列の設定が基本です。(シングルコーテーションによる文字列入力については Office TANAKA のこちらの記事を参照ください。)

表示形式で設定

ですが、「ゼロ パディング」という意味は、桁数が決まっていて、足りない分だけ 0 を入れて長さを合わせることです。ですから、例えば 6 桁でゼロ パディングしてほしい場合は、1 と入力したら 000001 であり、342 と入力したら 000342 になってほしいこともあるわけです。
この場合は数値を入力しても「見た目を変えてあげる」と考えます。表示形式で「円」や「人」といった単位を加えても数値として扱うことができますが、これと同じ考え方です。「ユーザー定義」の「種類(T)」で「000000」と設定します。すると、この表示形式が設定されたセルに数値を入力すると、指定した6桁に足りない分を 0 で埋めてくれます。この表示形式を使うのが「第2のポイント」です。
  • 設定したいセル・範囲を選択する
  • Ctrl+1 で「セルの書式設定」ダイアログの「表示形式」で「ユーザー定義」を選択する
  • 「種類」でゼロ パディングしたい桁数の 0 を入れる
ZeroPad
文字列設定と違い、このセルに入力されているデータは「数値」となります。見た目は 0001 などですが、四則演算可能です。

TEXT関数でパディング

数値を数値として扱うのであれば、わざわざゼロ パディングする必要はないでしょう。たとえば社員番号のような E00001 のようなデータを作りたい場合、E と ゼロ パディングした数値をつなげるパターンになりますよね。
上述の表示形式は計算可能ですから、連番や何らかの演算で数値の計算が可能で、見た目はゼロ パディングすることが可能ですが、文字列 “E” などと組み合わせると、表示形式で設定されているゼロは消えてしまいます。
ZeroPad2
これを避けるためには TEXT関数を使ってゼロ パディングされた表示形式を使って数値を文字列にします。これが「第3のポイント」です。
=TEXT(セル、”00000”)
ZeroPad3
ちなみに、TEXT関数や表示形式で使った ”00000” の 0 は数字のゼロではなく、書式記号としての 0 です。
https://support.microsoft.com/ja-jp/kb/883199
なので “99999” としたところで 9 がパディングされることはありません。
また、小数点以下のゼロ パディングもあります。123.000 のようなものです。書式設定の [数値] で [小数点以下の桁数] を指定しますが、書式記号で書けば、ユーザー定義書式で小数点以下3桁は #.000 もしくは .000 となります。
ちなみに、#,##0 と #,### では同じ三桁カンマでも、0 の場合の表示が変わります。前者は 0 を表示、後者は何も表示しない、となります。

Format 関数を使ったゼロ パディング

VBA では TEXT関数同様に Format 関数を使ってゼロ パディングをすることができます。ゼロ パディングされた文字列をMsgBoxを使って表示可能です。
ZeroPad4 
ゼロ パディングはできるのですが、結果的に、このゼロ パディングされた「文字列」の “0001” をワークシートのセルに入力した時点で、0001 –> 1 になります。
よって、事前にセルの書式設定をしておくか、VBA からセルの書式設定が必要になります。
セルを文字列の書式に設定してゼロ パディングするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “@”
Range(“A1”).Value = Format(buf,”00000”)
数値のまま、表示形式でゼロ パディングのユーザー定義書式にするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “00000”
Range(“A1”).Value = buf
になります。

Power Query でゼロ パディング

ZeroPad5
パディングをする関数は Text.PadStart です。
Text.PadStart
https://msdn.microsoft.com/en-us/library/mt260573.aspx
そのため、数値を文字列に変更します。Number.ToText を使って、数値を文字列にして、6桁、”0” から始まる、とします。
似たような関数として Text.PadEnd もあります。これは、足りない桁の後ろを指定した文字で埋めます。
Text.PadEnd
https://msdn.microsoft.com/en-us/library/mt260477.aspx

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

2015/11/09

Excel 2016 と Power Query (取得と変換)

Excel 2016 の 「取得と変換」 は Power Query

このブログで紹介してきた Power BI Excel アドインの Power Query ですが、Excel 2016 の「データ」タブの「取得と変換」グループにあるリボンコマンドは、Excel 2013まで Power Query アドインとして提供されていたものが Excel 2016 の標準機能になったものです。

Power Query や Power Pivot などの機能は「Power BI」ブランドの仲間としてマイクロソフトから様々な形で提供されていますが、Excel 2016 のトピックとしては、これまでアドインだったり、Homeなどのエディションで利用不可能だった Power Query が標準機能の「取得と変換」となって Excel に組み込まれたことがあげられます。

取得と変換グループ

このため、似たような外部からのデータの取り込み機能が「データ」タブのすぐ近くにあったり、「Power Pivot」の機能でもデータの取り込み機能があったり、ちょっと複雑な状況になりました。

これまでのマイクロソフトの Excel の機能拡張から考えると、どれかの機能がばっさり「切り捨てられる」ことはないと考えられますが、その中でも機能拡張の優先順位がつけられ、あるものは引き続き機能拡張され、あるものは機能拡張されずに機能として残りながらもそのうちリボンから消え、ユーザーの目の届かないところに位置づけられていく、ということが考えられます。

その意味で、外部とのデータ連携、外部からのデータ取得といったエリアでは [データ] タブにある [取得と変換] のリボンコマンドが今後の主流になる可能性が高いことが予想されます。多種多様なデータソースにつながることももちろん重要ですが、この「取得と変換」は他にはない特徴があります。それはクラウド (Office 365) を中心とした「データの取得方法(クエリ)の共有」と「取得したデータの公開」が可能なことです。

 

外部データの取り込みの置き換えではない「取得と変換」

そして、上述したクエリやデータの共有や公開という「拡張機能」も重要ですが、「基本機能」そのものも強化されています。共有などを考えずに、ただ単に Excel の外にあるデータをワークシートに取り込む、という機能を見ても「取得と変換」(Power Query)は強力です。

1) 閉じているブックからデータを取得したい

他のブックのデータを Excel で開かずに取り込むは「鉄板ネタ」でしょう。
これができるからといって、Excel 4.0 のマクロを今の時代に使うのは辞めにしませんか。またはそのような情報をありがたく(使ってもいないのに)紹介するのも、もうどうかと思います。

取得と変換 (Power Query)の強力な機能のひとつは「他で保管されている Excel ブックからデータを取得」することができる、です。

取得と変換ーブックから

それも単純にすべてのデータをワークシートに取り込むのではなく、必要なデータを絞り込んで、変換(文字→数値、シリアル値・日付の表示形式の設定など)し、ワークシートに取り込むことができます。上級者であればワークシートにデータを取り込まず、データモデル・データ接続のみで外部の Excel ブックのデータをピボットテーブルのソースとして利用することもできます。

ネットワーク上にある Excel ブックを対象にできます。\\サーバー名\共有フォルダ\ で表現されるファイルサーバー上の Excel ブックはもちろん、Office 365 の OneDrive for Business や、SharePoint ドキュメント ライブラリー内の Excel ブックも利用可能です。

取得と変換(Power Query)によるデータ取得は「クエリ」をベースにしているため、「接続(クエリ)の更新」をすることで、最新のデータに更新されます。
そのため、一度取得する場所や条件、変換の設定をしてしまえば、あとは「更新」をするだけで最新のデータを利用することができます。

この例は以下ですでに紹介しています。

2015/7/7 Power Query から https 経由で SharePoint の Excel ブックを開く


2) 指定したフォルダー内のすべてのファイルからデータを取り込みたい

「ファイルから」のサブメニューの「フォルダーから」は、フォルダにどんなファイルがあるか、を調べる機能だけではありません。

取得と変換ーフォルダから

クエリエディタ-フォルダから1
フォルダだけを指定した場合のクエリ結果

これも Excel VBA での「鉄板ネタ」になりますが、指定したフォルダー内にある CSV ファイルや Excel ブックを「すべて」読み込む機能をプログラミングなしに実現することができます。
この機能が強力なのは、一度設定してしまえば、ファイルが増えても OK、サブフォルダーが増えても OK という点です。もちろん、基本的にすべてのファイルを参照することができるので、そこから「ある名前の規則で絞り込む」ということもオートフィルターの設定同様容易に可能になります。

ポイントは Excel の中のデータを取り出す「列」を追加してやることです。その追加する列で使われる関数が Excel.Workbook 関数になります。

当たり前ですが、取り出す先の Excel ブックのワークシートは同じフォーマット、もしくはその構造をあらかじめ理解していて、そこから何を取り出したいかは決まっているものとします。
取り出し先のデータは「テーブル」形式でなくても大丈夫ですが、テーブルであれば「ヘッダー」と「データ」を自動識別してくれます。テーブルでない「範囲」の場合は明示的に指示する必要がでますが、難しい作業・指定ではありません。とはいえ、テーブル形式は今後は必須になっていくので、できればこのタイミングでテーブル形式を使うようにすれば幸せになれるでしょう。

ざっくりと手順を紹介します。

まず、対象となるブックが入っているフォルダーを指定します。

PQ001

指定したフォルダーから該当するファイルを絞ります。この例では “Book” から始まっている名前の Excel ブック「だけ」に絞り込みます。
この絞り込みの設定手順も Excel のオートフィルターで慣れた手法になります。

PQ002

絞り込んだファイルから Excel.Workbook 関数を使って、ブックの中のデータ情報を抜き出す「列」を追加します。
ブックそれぞれのデータ情報は [Content] のバイナリデータから Excel.Workbook 関数を使って参照することができます。
そのため、バイナリデータの [Content] 以外の列は不要なので、削除してから新規列を追加します。
ただし、この段階ではまだ「セル」や「テーブル」のデータそのものを参照しているわけではありません。

PQ003

それぞれのブックにはテーブル形式でデータが入っていますが、 Power Query からは「テーブル形式」のデータと「シート」の範囲データの2つを認識しています。
今回使うのは「テーブル形式」のデータなので、「テーブル形式」のみのデータに絞り込み、不要な列を削除し、テーブルのデータを列として展開します。
テーブル形式のデータを利用するため、列ヘッダーとデータ部分を明示的に区別・指定する必要はありません。

PQ004

このクエリの結果をワークシートに読み込みます。ワークシートに読み込んだときに「日付」がシリアル値のままで読み込まれるので、この表示形式の設定をクエリ エディタで行います。あわせて日付で昇順ソートの設定も行います。

PQ005

ここで作成したクエリの情報は「ブック」に保存されています。このブックを開いて、「データ」タブの「接続」の「すべて更新」または「更新」や、「クエリ」ツールの「クエリ」タブの「読み込み」の「更新」、さらに結果テーブル上のコンテキストメニュー(右クリックメニュー)の「更新」によって、最新のデータ、最新のファイルをクエリで指定しているフォルダーから読み込んで、テーブルのデータを更新します。

Excel Online と Excel データの共有

Excel Services という機能が SharePoint Serer 2016 で無くなる、というリリースがありました。ですが、Office 365 / SharePoint Online 利用者にはほとんど影響ありません。
MOSS2007などの昔、Excel Services は SharePoint 上で Excel ブックを表示させる、という目的の機能でしたが、今の SharePoint ポータル上での Excel ブックの共有には Office Web Apps/Excel Online の Excel Web Access が使われ、Excel Services は使われていないからです。

Technet: SharePoint Server 2016 IT Preview で使用されなくなった機能と削除された機能
Technet: SharePoint Server 2016 IT Preview の既知の問題

そのため現状、Excel ブックの共有という意味では、SharePoint Onoine の例から見ても Excel Online が主流になってきているといえるでしょう。
さらに Power BI サービスと OneDrive との組み合わせにより、Excel Online を使った共有機能がさらに拡張されてきています。

つまり、田中メソッドでいうところの「入力・計算・出力」の「出力」の部分の大きな変革が始まっています。

いままでは「印刷」する、もしくはブックを「コピーして配布」するが主でしたが、今後は

  • OneDrive にアップしたブックのリンクで Excel ブックを共有する
  • SharePoint サイトのポータルページに埋め込み共有する
  • Power BI サービスを使って、ダッシュボードとして共有する
  • Power BI サービスを使って、最適化されたアプリでデータを閲覧する、させる

といった Excel Online をベースにした「出力」方法が主流になっていくと考えられます。

Power BI については、こちらのブログ記事も参照にしてもらえればと思います。

 

クエリの共有

実際に現場でデータを分析したい、データの傾向をみたい人が、必要なデータへのアクセス権限をすべて持っている、または、その権限を持っている人が現状を踏まえて分析できる、ということは稀でしょう。
規模が大きくなくても、パッケージやアプリケーションに組み込まれているデータは「正規化」され、複数のファイル、テーブルに分割されていることが多く、そのリレーションをすぐに把握できるものではないでしょう。

Power Query や Power Pivot を駆使して、正規化された複数のテーブルから必要なリレーションシップを基にして分析元のリストを作成することに労力をかけるよりも、必要であろうデータを収集できるクエリがすでにあり、そこから必要なデータを絞り込む、もしくは、いくつか用意されたクエリを結合して必要なデータを作り出し、傾向を把握する、分析することに時間をかけたほうがいいと考えるでしょう。

Power BI では「データ カタログ」としてクエリの共有機能を提供しています。データ カタログにクエリを登録、それを共有することによって、エンドユーザーがクエリをゼロから作らずに、データ管理をしている担当者がクエリを作成し、エンドユーザーに近い分析する部門がピボットテーブルを活用してデータの傾向を見る、といった活用が可能になります。

support.office.com Power BI データ カタログのクエリを共有する

 

パーソナルツールとしての Excel 機能拡張の限界

このようなことから、少なくとも今後の Excel の機能拡張の方向は Office 365 といったクラウドの進む方向と一緒だと考えるのが妥当でしょう。
そうすると、Excel を使いこなしたいユーザーにとって、以下を「知りません、得意じゃないんです」と言ってられなくなっています。

  • Office 365、SharePoint、各種データソースへのアクセス権限についての理解
  • 自分が共有したいブックの、他の人へのアクセス権設定

Excel ユーザーの方とお話をしていると「サーバー」や「アクセス権」、「認証」という言葉を聞いただけで拒否反応する人も少なくないのですが、それらの管理や設定を自分たちがすることがなくても、システム部門やデータ管理の担当者に適切な権限をください、もしくは組織内のデータを活用するために設定してほしい、というリクエストを出す、常々話をするくらいの考えは持ったほうがよいでしょうね。

また、管理者側からすれば、すべてをエンドユーザーに任せるのではなく、あらかじめ適切に権限設定された共有クエリを用意することで、必要なデータに対して必要な権限でアクセスさせることができます。そのような設定が管理者側で可能であることをエンドユーザーも知っておくべきでしょうね。

もう、一人で Excel ブックをいじってそれらのコピーを配布する時代の先行きは長くないようですから。

 

その他の参考情報

Power Query のクエリ編集についてはマイクロソフトからまとまった情報が出ているようには見えませんが、根気強く探すと出てきます。

キーワードは「M言語」ですが、検索するときは “M Language” で探しましょう。 “Power Query M Language” あたりのキーワードで msdn などの公式技術文書にいきつきます。

msdn: Power Query (informally known as “M”) Formula reference
https://msdn.microsoft.com/en-us/library/mt211003.aspx

そのほか、サイトに support.office.com を指定して Power Query のキーワード (& 1041 日本語指定)で検索するのも有効でしょう。

https://support.office.com/ja-JP/search/results?query=Power%20Query&lcid=1041

また、MSMVP の kagata さんが以下の方法による Power Query 関数のヘルプの確認方法を紹介しています。

http://mukkumuku.blogspot.jp/2015/10/power-bi-10-power-query-reference.html

最近では Power BI の新しい自習書が日本マイクロソフトさんから出ました。しかし、それは Excel の範疇では検索できません。
以下のリンク ページの「Power BI 自習書」の「Word版ダウンロード」や「PDF版ダウンロード」から自習書のデータをダウンロードしてください。

BI/Data Analytics – microsoft.com/ja-jp/ : Power BI 自習書

この自習書を作成した SQLQuality の松本さんのブログがこちらになります。

http://www.sqlquality.com/column/column_50.html

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を返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。