Excel小技 というか仕様： あなたの情報が・・・漏れているかも

エクセルを使っていて、ふと、ファイルのプロパティを見たときがあった。そこには、いろいろと情報が掲載されている。前から知っていたことではあるが、ふと、気になったのでまとめておく。もし、ご存じない方がいらっしゃったら、気をつけておいてはどうかと思う。この情報とは、ファイルを作った人、そして最後に更新した人の情報がある程度入ってしまうということだ。情報は、ファイルを開いている状態で、ファイルメニューのプロパティ、もしくは、ファイル自体を指してプロパティを開くと見ることができる。ここでは、後者について説明したい。
プロパティを開くと、全般、カスタム、というタブがある。ここで、カスタムを開いてほしい。原作者というところがある。ここに、作者、最終保存者、会社名というのがあり、これでどこの誰がもともとのファイルを作ったのか、また、最後に更新したのが誰かというのがわかるようになっているのだ。知っている人には、当たり前じゃないか？と思われるかもしれない。でも、意外と知らない人が多かったりする。
通常、使用している限りでは、別に使用者情報が人に見られても問題ないが、パブリックな場所にファイルを公開するとき、また、ファイルの作成者情報が漏れてはまずいときなど、気をつけないといけない。特に、コンサルティング・ファームや、銀行・証券会社など、企業のMAに関与する人々は、重々注意して欲しい。というのは、私自身、作成者名、会社名については、これまでもしっかり確認してきたが、更新者情報については、確認してこなかったからだ。この更新者情報は、じつは、プロパティからでは変更することができない。ここが曲者だ。
実は、エクセルのアプリケーションソフトウェア自体で設定を変える必要がある。それは、ツール、オプション、全般のユーザ名というところだ。ユーザ名は、インストールするときに、シリアル番号とともに入れたものがここに表記されているようだ。ここにある名前により、新たにファイルを保存した段階で、ファイルのプロパティが書き変わるようになっている。こんな機能いらない、という人も結構いることだろう。だが、パソコンでつくったファイル、特に、MS系のアプリなどでつくったファイルでは、いろいろと作成者の情報が入っているものだ。私自身、履歴とかが残るのがいやなので、ワードではあまり最初から文書を作ったりしない。他のエディタなどで書きためておき、コピー・ペーストしている。
個人情報保護の時代だが、これには保護されるべき人についての対策ばかりではなく、場合によっては、人から嵌められるリスクもある。そうならないためにも、自分でできる情報管理はしっかりしたいものだ。

エクセル小技 ムリヤリPIVOTでデータを抜き出す

いろいろな場面で、データをまとめた資料を受け取ることがあります。紙でもらっても仕方がないので、データでもらうようにしていますが、そのデータは、印刷するままの形で数字が入力されていたりして、いろいろな分析などを行うときに非常に使いづらかったりします。そういう時、どの様にしてデータを抜き出すのか？そこで、PIVOTテーブルが役立つことを思い出していただきたいと思います。通常、PIVOTテーブルは、整理されたテーブルに対して用い、いろいろな集計をするために使います。エクセルの中級者以上はばりばり使いこなしていることでしょう。（初級という級があるのかどうかわからないけれど、相対的な表現として捉えていただければうれしいです）
さてそのPIVOTテーブルを使って、ムリヤリデータを抜き出す方法ですが・・・。非常に、テキストベースのblogでは難しい。
たとえば、コンビニなど多店舗の計数が一つのシートにまとまっているデータを考えてみましょうか。この画像見れますか？このデータ、そのままだと非常に使いにくいですよね。データをあちこちから拾ってこないと、分析に使いにくい。で、そういうときに、一番左と一番上に、ピボットで使いやすそうなフィールド名と店舗名を入れてしまい、一つの大きなテーブルとして使ってしまうのです。この例だと、左側に店名を入れます。店名の入れ方は、というと、if文を使って、店名と書かれているところの横には空白を、店名とかかれているところの左下側には、右と同じを、それ以外が右側にくるときには、上と同じを入れるなんていう計算式を入れます。上の方のフィールド名は、それぞれのデータがわかるように入れます。
これを、ドン、とpivotに入れてしまえば好きな方向でデータを並び換えられ、余計なデータを目の前に出さなくてすむわけです。
え？こんな文字の説明ではわからない？と言う方は、メールをいただければ、当方で作ったエクセルファイルをお送りします。ここで、お願いです。紳士的にメール下さい。また、件名には、迷惑メールと間違えないように、わかるような言葉を入れて下さい。毎日、百通以上の迷惑メールが届いており、一気に削除したりしており、せっかくのメールを消去してしまうおそれがあります。それと、ネットとは言えど紳士的なメールをお願いします。

LARGE、SMALLを使った並び換え

先日、LARGE、SMALLについて記事を載せたが、それなりにご覧いただいたようでした。もっとも、Googleで上の方に表示されるSubtotalの記事に比べると、まだまだアクセスが多いとは言えないのですが。さて、今日は、LARGEとSMALLを使った、データの並び換えをご紹介したいと思います。とっても簡単なので、ぜひ、覚えて活用してください。（といってもなかなか活用する機会がないかもしれませんが）
LARGEは、選択範囲の中で、○番目に大きい数字を、SMALLは○番目に小さい数字を表示してくれます。これをつかって、○番目というところに、1,2,3,4,5・・と数字を入れていけばいいのです。え？いちいち、数字を入れるのが大変？そういう場合は、1～必要なランク前の数字を、どこかのセルに入れておくと便利です。もしくは、ROW()、かCOLUMN()をつかってあげる。ROWは、A1とかでいうところの何行目かという1の側の数字。A1なら1と表示されますし、D34なら34が表示されます。COLUMN()は、A2などでいうところの何列目かというところ。Aなら1、Bなら2とAから数えて何列目かが出ます。たとえば、C5から縦に1,2,3と振りたい場合は、=ROW()-4と入力すれば、1から数字が下へできていきます。
文字で表現するのは、なかなか難しいですが・・。セルの並び換え機能を使わずに、上位からデータを見るときなどに重宝します。（他にも、ざっと見るだけならば、オートフィルタで見ることもできますけれど、こちらの場合は、同じ数字がいくつあっても、一つとして見えてしまうので、別の目的で使用するのがいいでしょうね）

エクセル小技 LARGE関数の使い方（私はRMSの算出に使っております）

LARGE関数、SMALL関数、そしてMAX関数、MIN関数をうまく使うと、いろいろな計算ができます。MAXとMINは、結構有名な関数ですよね。ある範囲で、MAXで指定すると、その中の最大値がかえってきます。MINは、最小値ですね。最大値については、標準ツールバーにあるΣマークを押せば、同様の計算がされます。こちらを使われていらっしゃる方も多いでしょう。この辺りは、基本的なものになります。さて、ご紹介は、LARGEとSMALLです。これらは、範囲の中で○番目に大きい（小さい）数字が何か？を出してくれる関数です。同様の関数には、RANKと言う関数もありますが、LARGE、SMALLとはちょっと異なっています。
このLARGE関数、私はRMS（相対的マーケットシェア）を計算するのに使っています。RMSとは、ビジネススクールなどでは誰もが知っている考え方で、マーケットシェアを、全体を100%ととして考えるのではなく、競合他社との相対的な位置づけでどのポジションにいるかを表す指標です。これは、市場規模がわからなくても、上位数社の規模がわかれば、大体の業界マップがわかる、という便利な考え方で、いろいろな分析で用いられています。数字自体は、1位の数字は2位で割り、2位以下の数字は１位のもの対してどれくらいか、という計算をします。この計算を、一発でできんかな？と考えて、LARGE関数を使うことを思い立ったのですね。
=IF(F3/MAX($F$3:$F$13)=1,F3/LARGE(F3:F13,2),F3/MAX($F$3:$F$13))
これは、F3からF13までに11社の数字が入っていると考えてください。上にあげたのはG3に入れた数式です。F3からF13において、最大の数字でF3-F13自身を割り算し、それがもし1の場合には、二番目に大きい数字で割る、という式です。たいしたことはないですが、一発でRMSが計算されます。

上で、ちらっとRANK関数についても触れましたが、RANK関数とは、範囲の中で、何番目にその数字大きいかということを返してくれる関数です。LARGEと非常に似ていると言えますね。ただ、RANKとLARGEとは、使い勝手が、ほんのちょっと違っています。例えば、以下ですが、
数字 　 RANK順位
1　　　　　　9
3　　　　　　8
1　　　　　　9
4　　　　　　5
4　　　　　　5
8　　　　　　2
4　　　　　　5
7　　　　　　4
10　　　　　 1
8　　　　　　2
とRANKの場合は同じ数字の場合、同じ順位になります。だから、例えばこの数字で6番目に大きい数字を知りたいと思っても、RANKが6の数字を引っ張ってこようとしてもうまく行かないわけです。なぜなら、RANKが6というデータがないからですね。こういうとき、LARGE関数が役立つわけです。LARGEの場合は、5番目、6番目、7番目と指定しても、全て4が返ってきます。うまく使いこなせると、便利ですね。

ちなみに、上で例に上げた数字ですが、前に紹介した乱数で作りました。INT(RAND()*10+1)で、1から10までの間の乱数を作り、整数部分を取り出したものです。

クイズで学ぼう!エクセルの小技の本が！！

有名メールマガジン「クイズで学ぼう!エクセルの小技」の著者の本がついに刊行された、とメルマガで見た。このメルマガは、いろんな意味で、参考になることが多く、ずっと読んできている。これで、最近のエクセルの名著と言えば、以下の二冊ということになった。勉強法は、基本的にはどちらの本も読み流して欲しい。そして、困った時に、思い出したように、これらの本をめくって、実際にやってみる。これの繰り返し。一度も、こういう実用的な本を読むこと無しに、エクセルを使っていたのではいつまでたっても、やり方（情報）の引き出しは増えていかないもの。
私は、これ（上記のやり方で）エクセルのスキルアップを図ってきました（＾＾；

嫉妬されるほどエクセルが上手くなる222の技 技術評論社 このアイテムの詳細を見る

最新版 Excel計算式表現事典―2003/2002対応 技術評論社 このアイテムの詳細を見る

Excel 日付に曜日をつける

エクセルで日付を月日で入力すると、入れた年のデータに変換されます。たとえば、2/1と入力すると、2005年2月1日と入力したのと同じことになるのですね。実際は38384という数字が入っているのだけど、表示形式として日付が選択されているために、日付に見えるわけ。さて、問題となるのは、日付を、フィルハンドルでざーっと作ったときなど、曜日を入れたいのに、ある日が何曜日だったっけ？なんて考えてしまうことがあります。わざわざ、カレンダーを出してきて曜日を調べて、それを入力した後にフィルハンドルでずるずると引っ張ったりしていませんか？。。（自分自身が、以前そうしていたので、そういう人がいるだろうなんて考えるのだけれど）

実は、日付を入れたセルの表示の仕方を変更すれば、同じセルの中に曜日も表示されます。また、別のセルに表示させるための関数も存在しているのです。基本的な話だけれど、以外と文字列データ関数については、あまりご存じない人が多いなと感じてもおり、ちょっとまとめます。
セルの書式設定というメニューで、表示形式を選べるのはご存じですよね。ここで、日付を選択すると、残念ながら曜日が出てきません。ここで、一つ勉強してもらいたいのが、表示形式の中のユーザー定義というところです。中程から下の方に、yyyymmddとかそういうのがあると思います。yyyyは西暦、mmが月、ddが日の意味です。ここでm"月"d"日"とすれば、○月○日と表示されるわけです。そして、ここにaaaと入れてあげると、なんと曜日の漢字が出てきます。aaaaと入れれば、○曜日、と3文字の漢字が出てくるのですね。
実は、ユーザー設定にせずに、入力ロケールのところを外国に設定すると、デフォルトで曜日が入っている設定があるのもわかります。例えば、中国にすれば、星期一（月曜）とか星期日（日曜）という表示もできるのですよ。曜日だけが入るのです。日本の場合は、標準には入っていないのですが、入れてもらいたいですね。

さて、もう一つの関数の話ですが、=text(日付の入ったセル, "aaa"）とすると、曜日の名前が一文字で表示されます。"aaa"を"aaaa"にすれば日曜日、など三文字で表示されます。英語で表示したい？その場合は、"ddd"か"dddd"にすればいいのです。セルの書式設定のユーザー設定のところに入力するものを、Text関数のところに入れてあげれば、表示を変更できるのですね。
文字列データ関数については、結構Tipsがありますので、また投稿します。

お役に立ちましたでしょうか？お役に立った場合クリック！

エクセルでの乱数の作り方（ちょっとマニアック?）

エクセルに、RANDという乱数を作ることができる関数があるのをご存じな人は多いことだろう。どんなことに使うのか、というと、あまり使い道がないかもしれない。良く聞くのは、1～100などの数字を、とりあえず番号を振りたいというときなどだ。これには、重複していい場合と、重複してはいけない場合とがある。私なんかは、データのハンドリングの仕方を説明するために、あえてダミーの表を作ったりする。その時にデータを入れるためにつかうことの方が多い。
使い方だが、単純に整数で乱数を出したければ　=INT(RAND()*数字)+1　これで、数字に入れたものを最大にしての整数による乱数を出すことができる。なお、エクセルでは15桁の乱数が作られるようだ。999兆のパターンの乱数である。
さて、上であげた、番号を振る方法だが、いっさい重複しないものを作る場合を考えてみよう。それも、整数で。これは、1つの列（行）で、やることもできるのだろうが、私の場合は二つの列を使う。例えば、A列に、rand()を入れて、数字を100個つくる。B列にrank関数を入れて、乱数の順位付けをしてやるわけだ。15桁の組み合わせなので、100個つくる程度で、同じ順位の数字が出てくることはまずないと思っていいだろう。
余談だが、エクセルの1シートのセルの数は、1677万程度。一つのシートで、同じ順序が入ることもまずないと思っていい。といっても、全てのセルに、=rand()を入れようとしても、まぁ、時間がかかることこの上ない。一気にやろうとすれば、メモリ不足になるというダイアログも出てしまう。なお、先に上げたrank関数は、行、列に範囲を固定されない、複数の行列にわたる範囲を指定できる。理論上は、あるシートでA1からIV65536までに、rand()を入れて、別のシートで同じくすべてのセルにrank関数を入れても、まぁ、ほとんど同じ順序になることはないと言えるだろう。同じ数字が出るのは、1/666,666に近いところになると思われる（正確な計算ではないけれどね）。

エクセルの話題 ショートカットについて

コピー、切り取り、貼り付け、その程度のショートカットキー、と言ってしまえばおしまいですが、会社などで、いちいちマウスをもってきて、上記の作業をしている人を多く見かけます。上書き保存もそうですね。これらの機能は、Ctrlキーと、C（コピー），X（切り取り-もともとXが、見た目的にはさみをイメージすることから定められたようです），V（貼り付け）、S(
保存）、と二つのキーを押すことで達成できる、本当に基本的なものです。

この他にも、セルの書式設定は、日本語入力がオフ（半角直接入力になっている状態）で、Ctrlと数字の1。Ctrlを押しながら、矢印のついたカーソルキーを押せば、連続して入力されているデータの端っこまで一気に飛ぶ。CtrlとShiftをおしながらカーソルキーを押せば、連続データをひとまとめに選択できる。日本語入力オフで、Ctrlとスペースキーをおせば、アクティブセルの含まれる縦の列すべてを選択する。Shiftとスペースキーで、横の行自体を全て選択する。などなど、いろいろ小技があります。

CtrlとFで検索、CtrlとHで置換。CtrlとPで印刷。

またAltを使うと、別の作業も効率的に行えます。Altについては、同時に押さなくても平気なので、アクロバティックな動きだなんて思わなくても、誰でもできます。Altを押して、Dを押して、Fを押す。そこでエンターを押せば、一気にオートフィルタが出来上がります。また、形式を選択して貼り付けるときも、Alt, E, Sを押せば、あのダイアログが出てきます。中身も、キーボードで選択できるのですよね。

こうしたショートカットキーを使うのが嫌いな人（教えても、なかなか使おうとしない人）は、たいてい、キーボードで文章を打つのも嫌いな人です。でも、嫌いだからって、できないではすまないし、ショートカットを効率よく使って、作業時間も短縮できれば、よりいいのではないでしょうか？

もっとも、初心者の内は、マウスをつかっての作業時間とかそういうことよりも、コンピュータを使うことによる脳内での作業変換に時間がかかることでしょうから。。たいしたスピード化も測れないのかもしれないです。それに、最後に大事になるのは、コンテンツですからね。


（本記事ですが、変なコメントがあったため、再アップしました）

エクセルについて

前回は、いきなりsubtotalからはじめてしまいました。今後、適当にやっていくと、思いついたままに記事を出してしまいそうなので、これから記事をアップしていこうとしている機能についてまとめておきます。

○hlookup, vlookup　テーブルなどから、データを検索してきて、取りたい場所にあるデータを持ってくる関数ですね
○キーショートカットのいくつか　特にshift+ctrlとか、shift+space, ctrl+spaceとか、そういうのをまとめます
○文字列操作関数の基礎　right, left, mid, substitute, concatenate, & あたりをまとめます
○ピボットテーブルの活用方法
○Large, Small関数の活用によるRMS(Relative Market Share）の一括計算
○ゴールシーク、ソルバーの利用
○オートフィルタの活用（そもそも、この機能を知らない人も多いようですし・・・）
○find, matchなどを利用した、データの参照方法
○offsetを利用しての、データの参照方法
○sumifなどを利用した、集計方法

これくらいを基本として捉えてまとめていきます。
多少知っている人は、ここの記事はあまり読む必要はないと思いますし、Excelファンクラブなどで調べていらっしゃるでしょう。それにマクロとかは、私が対象としている方々にはあまり必要ないかなぁ、とも思いますので説明はしません。以前、ググるという本の紹介をアップしたことがありましたが、そこでは情報の検索力は、発想力だと申しておりました。エクセルでも、ある程度の方法の引き出しを持っていれば、同じ答えにたどり着く方法は複数あるのですね。だから、そういう視点でものをまとめていきます。

エクセル小技 subtotal

仕事で、エクセルを使うことは結構多いです。それで、よく思うのは、基本的な関数の使い方についてあまりにも知らない人が多いということです。そこで、ちょっとエクセルの関数についていくつか記事を載せようと思います。まず、最初に、subtotalから。エクセルで計算式を入れる際に、=A1+A2とかで計算する人、sumを使う人、様々だが、subtotalを使う人は少ないですね。。オートフィルタを使った時、ざっと表示されている数字の合計や個数、平均を見たりするのに便利だし、また、途中に小計が入っているような表で、選択範囲をわざわざ飛ばしたりせず、ひとまとめにして指定できるという意味でもなかなか重宝する関数なんだけれど。

たとえば、縦に3つ数字があります。その下に、小計で上の3つの小計を入れます。そして、また3つ数字が、そして小計。。。と続き、一番下に総合計が入るとします。ここで、小計欄にsubtotalをして、また、総合計欄にsubtotalで全部の範囲を指定する。そうすると、ぴしっと全部の数字が入るのですね。いちいち、小計を足し算したりする必要がないわけです。もっとも、検算というかチェックをするとき、セル参照を見るときなどがありますが、その時は、範囲が全部表示されてしまいますが。。。subtotalであれば、きちんと計算されていることになります。

例？まぁ、そう言わずに、Excelのヘルプを見たり、自分で試してみてくださいよ♪

パソコンソフトの効率的勉強法

Excel計算式表現事典

資格をとることを目標としない、パソコンソフトの効率的な勉強法について、どうも多くの人が非効率な勉強をしていると感じることが多いので、ここに紹介したい。かならずしも、全ての人にあてはまるものではないと思うから、できないと言って、文句を言われては困る。あくまで、参考にしてほしい。
多くの企業で使用しているだろうソフトとして、ワードとエクセルについて話をするのがいいだろう。本当に、たくさんの人が、もっと使えるようになりたいと考えていながら、数時間の勉強すらしたがらない、そんなモノだと思う。揚げ句の果ては、自分は、頭がよくないから、覚えられないとか、そんなバカなことを言う。今後の、ずっとやっていく仕事生活における長ーい時間と、目の前の2、3時間、どっちが大切でしょうか？
さて、そんな戯れ言は、置いておき、私が、前の会社で、エクセル初心者から、比較的使える人間に変貌した方法を紹介したい。それは、そのソフトで、○○ができる、ということを片っ端から頭に入れることからはじまった。
最初、所謂、関数事典なるものを目を通そうとしていたが、いかんせん、わけがわからない。どのような時に使うのやら、さっぱりわからない。また、どう応用を効かしていいのかわからなかったりした。
その中で、読みはじめた本が、「Excel禁断の秘技」という本だった。名称から、ちょっと敬遠したくなるような本ではあるが、実は、この本、エクセルで、○○のことができる、という実用的な事例集だったのだ。

最初、そういう類いの本を、1冊、読み通してしまう。パソコンなんて触らなくていいから、とにかく、どんなことが出来るのか、ということを頭に入れる。これで、実際のパソコンワークにおいて、何かをやろうとした時に、そういや、あれでできたはずだ、という手法の引き出しが、頭の中にできることになる。
なんか、書いてあったなぁ、と思い出したら、それを読み返してからやればいい。たったこれだけのことだ。一度、そういう本を、通読しておくのとおかないのとでは、その後のスキルアップのスピードが圧倒的に違う。（私なんかも、エクセルはかなり得意な方と言われているが、いまだに、sumifとcountifの表記方法を、ヘルプを見て確認したり、subtotalの計算方法を確認したりしている）

逆に、良くないのは、初歩から、本に書いてある通りに試してみて勉強していきましょうという類いのもの。これは、時間がかかるわりには、実践力がつかない。資格でも取ろうというのであれば、この方法でも良いかもしれないが、時間がかかる。

ワードでも、エクセルでも同じだと思う。実践的な、何ができるんだ、ということを書いてある本を、1日かけて、読み通して欲しい。2、3時間もあれば、計算式の書き方は覚えなくても、何ができるかは通読できるはず。計算式の使い方を暗記しようとするから、いつまでたっても出来ない。使って覚えるのが本来のやり方。車の運転と一緒です。いくら、暗記したところでできません。パソコンソフトも同じ、どんなことができるか、頭の片隅にいれておき、使って覚えるしかないのですよ。

パソコンソフトの勉強は、語学の勉強とは違います。また、所謂資格試験の暗記とも違う。同じ座学だからといって、同じ勉強法が役立つとは限らない。いっぺん、3時間くらいかけて、本を読んでみたら？
なお、エクセルについてはこれがお薦めhttp://www.amazon.co.jp/exec/obidos/ASIN/4774121002です。