Excel。一覧表から行単位で抽出するならVLOOKUP関数よりもINDEX関数がおススメ。

Excel。一覧表から行単位で抽出するならVLOOKUP関数よりもINDEX関数がおススメ。

INDEX関数+配列

ちょっと前ですが、VLOOKUP関数だと、
修正が面倒で大変なんだよねぇ～とお聞きしたことがあって、どんなことなのかと聞いてみたら、
下記のような作業だそうでして、

抽出結果を表示する行があって、下なのか別シートなのかわかりませんが、データがあって、
その中から、NOを入力したら、該当するデータを抽出したいということらしいのです。

今回は4月から9月というサンプルですが、もっと列数が結構あるそうです。

確かにVLOOKUP関数でも結果を求めることはできますが、列数が多いとなると、
確かに大変かもしれませんね。

では、VLOOKUP関数で考えてみましょう。
B2をクリックして、VLOOKUP関数ダイアログボックスを表示しましょう。

検索値は、A2
範囲は、$A$6:$H$14　右方向にオートフィルハンドルを使って数式をコピーしますので、
絶対参照は欠かせませんね。

列番号は、2

検索方法は、0　の完全一致ですね。FALSEでもOKですね。

数式は、

=VLOOKUP(A2,$A$6:$H$14,2,0)

では、OKボタンをクリックしてみると、

NOが2なので、店舗名には渋谷と表示されましたね。

では、この数式を右方向にオートフィルハンドルを使ってコピーしてみると、

当然、#N/Aというエラーが表示されてしまいましたね。
これは、検索値がA2ではなくて、B2・C2とずれてしまったからですね。
ですので、複合参照のA2を$A2にするといいでしょう。

=VLOOKUP($A2,$A$6:$H$14,2,0)

ただ、これでは、全部渋谷になってしまいます。

もう一か所直さないといけません。実はそここそが、面倒といわれたところなのです。
その場所とは、
【列番号】
列番号が2のままなので、それを3・4・5・6…と変更しなければいけません。

列番号を算出させる、COLUMN関数をアレンジして…なんていう方法もあることはありますが、
それも大変です。

そこで、このような列数がたくさんある場合には、
INDEX関数+配列関数というテクニックを知っていると、アッサリ算出することが出来ますので、
紹介してきます。

では、B2:H2の数式を削除します。

B2をクリックして、INDEX関数ダイアログボックスを表示してきますが、
INDEX関数を選択すると、引数の選択ダイアログボックスが表示されますので、

今回は、配列,行番号,列番号　を選択します。
OKボタンをクリックすると、INDEX関数のダイアログボックスが表示されてきます。

配列は、リストの　B6:H14　を入力します。
行番号は、　A2　を入力します。

列番号は、複数列を参照しませんので、0（ゼロ）を入力します。

OKボタンをクリックしましょう。

渋谷と表示されました。
数式は、=INDEX(B6:H14,A2,0)　となっていますが、この数式をコピーするのではありません。

この数式を配列関数にしていきますので、
B2:H2までを範囲選択します。

そうしましたら、そのまま、数式バーにある数式を範囲選択します。

そして、配列関数にしますので、

Ctrl + Shift + Enter　

を押します。

すると、データが抽出されました。
数式は、

{=INDEX(B6:H14,A2,0)}

と変わったことがわかりますね。
では、A2を変えてみましょう。

このように、VLOOKUP関数でもOKですが、場合によっては、
INDEX関数+配列関数というテクニックもありかもしれませんので、
頭の片隅にあると、いざという時に便利ですよ。

ただ、ひとつ注意があります。A2は行番号です。

つまり、コード番号などの文字ではダメで、数値である必要があります。

Excel。Vertical_bar_graph。積み上げ縦棒グラフの合計値を折れ線グラフを使って表示する方法

Excel。積み上げ縦棒グラフの合計値を折れ線グラフを使って表示する方法

【積み上げ縦棒グラフの合計値】

以前、積み上げ縦棒グラフの合計値を表示する方法をご紹介しましたが、
http://infoyandssblog.blogspot.jp/2014/05/excel_14.html

そもそも、なぜか積み上げ縦棒グラフをつくると、合計値を表示させるところがないので、
どうしたらいいのか？ということで、以前書きましたが、

合計値をテキストボックスを使って表示するにはアイテム数が多すぎで、
面倒だし、大変だし、何か楽な方法ってないですか？というご質問がありました。

確かに、4アイテムぐらいならいいですが、多くなると大変ですよね。

そこで、今回は、
折れ線グラフと第2軸を使って積み上げ縦棒グラフの合計値を表示する方法をご紹介します。

今回は第2軸を使いますので、若干複雑ですが、
複合グラフを作ったことがある方でしたら、こちらの方が楽かもしれませんね。

完成はこんな感じです。

このように、アイテム数が多いと、積み上げ縦棒の合計値を表示させるのは大変ですよね。

では、次の表を用意して、作り方を説明していきます。

まずポイントは合計値を算出しておくこと、
そして、範囲選択はその合計値も含めて設定することです。B2:F11までを範囲選択しましたら、

挿入タブの縦棒の中から、2-D縦棒の積み上げ縦棒を選択しましょう。

すると、積み上げ縦棒グラフが挿入されましたね。わかりやすいように大きくしております。

合計値も混ざっていると思う方もいると思いますが、
この合計値を折れ線グラフに変えていきます。

合計を第2軸でまずは表示させていきますので、合計をクリックすると、
合計すべてが選択されます。

選択したら、グラフツールのレイアウトタブのグラフの要素が、
【系列”合計”】になっていることを確認して、その下の選択対象の書式設定をクリックしましょう。

データ系列の書式設定ダイアログボックスが表示されます。

系列のオプションにある、使用する軸を第2軸にして、閉じるボタンをクリックしましょう。

合計が前面に表示され、第2軸が右側に表示されましたので、
この合計を折れ線グラフに変えていきます。

選択されている状態のまま、
グラフツールのデザインタブにあるグラフの種類の変更をクリックします。

グラフの種類の変更ダイアログボックスが表示されますので、
折れ線を選択してOKボタンをクリックします。

処理が面倒になりますので、マーカー付を選択しないほうがいいでしょう。

合計が折れ線グラフになりました。積み上げ棒グラフの数値と合計の数値が同じですので、
接合しています。

あとは、この折れ線グラフを加工していきます。

合計値を表示させますので、折れ線グラフを選択して、グラフツールのレイアウトタブにある、
データラベルの中にある、上を選択しましょう。

グラフに合計値が表示されました。
あとは、削除関係の処理に入ります。
折れ線グラフの実線はいりませんので、
折れ線グラフを選択して、グラフツールの書式タブの図形の枠線をクリックして、

線なし　を選択します。
折れ線グラフの実線が消えました。

続いて、第2軸を選択して、DELキーを使って、削除します。

最後に凡例の合計のみを選択します。凡例を一度クリックして、
再度合計の上でクリックするといいでしょう。選択できましたら、DELキーで削除します。

コレで完成しました。

このように第2軸というテクニックを使うと、テキストボックスの表示を大量につくらなくても、
合計値を表示することもできますので、用途に合わせて使ってみてください。

Excel。Graph。グラフのデータテーブルもいいけど、図のリンク貼り付けという方法もあり。

Excel。グラフのデータテーブルもいいけど、図のリンク貼り付けという方法もあり。

グラフ&図のリンク貼り付け

なんでも、今回のリクエストは、Excelで様々なグラフを挿入して、
そのグラフにデータテーブルを表示しているのだけど、データテーブルもパッとしないし、
データテーブルを表示させると、グラフ自体が小さくなってしまって、
何かいい方法はないかなぁ～というものでして、

確かに、グラフだけだとわかりにくいので、データテーブルを表示させることもありますが、
確かにパッとしないし…ということで、図のリンク貼り付けという方法もありますので、
ちょっと、紹介してみます。

このようなグラフがあります。

ますは、このグラフにデータテーブルを表示させてみましょう。
グラフをクリックして、レイアウトタブのデータテーブルの▼をクリックして、

データテーブルの表示をクリックしてみましょう。
グラフにデータテーブルが表示されましたね。

グラフが小さくなって、下側にデータテーブルが表示されましたが、
なんかイマイチパッとしないようにも思えますね。

そこで、今回紹介したいのが、図のリンク貼り付けという方法ならば、
データテーブルよりもわかりやすいかと思います。

では、まずは準備からしていきましょう。

この表を貼り付けていくわけですが、このまま貼り付けてしまうと、背景が透明なので、
グラフに貼り付けた時に、透けて、うしろが見えてしまって、カッコ悪いので、
まずは、セルの塗りつぶしをしていないところを範囲選択をして、
白系で塗りつぶしを設定しておきましょう。

表を範囲選択して、コピーします。Ctrl + CでもOKです。

そして、ポイントは、その下のセルをクリックして、図としてリンク貼り付けをするのですが、
間違えてグラフをクリックして、貼り付けてしまうと、こうなってしまします。

ですので、まずは、図としてリンク貼り付けを先にしておいて、
それを、グラフ内に異動させてくるわけです。

では、表を範囲選択して、ちょっと下のセルをクリックして、貼り付けの▼をクリックして、

リンクされた図を選択しましょう。
下側に、図としてリンク貼り付けされましたね。

この図をグラフの中に移動して、適度な大きさに調整してみましょう。

このようにすると、データテーブルを表示した時に発生した、グラフが小さくなることもなく、
グラフの基のデータを合わせて見れるグラフになりましたので、状況によっては、
このような方法もあります。

また、リンク貼り付けなので、表の数値が変われば、
この貼り付けた表の数値も当然連動してかわりますよ。

ちょっとしたアイディアでしたが、現場に合わせて、
色々やってみるというのは、いい事ですから、やってみてはいかかでしょうか？

Excel。PMT。めざせ！100万円。PMT関数を勉強するならゴールシークも一緒にね。

Excel。めざせ！100万円。PMT関数を勉強するならゴールシークも一緒にね。

PMT関数とゴールシーク

Excelの講座をやっていますと、MOSの講座などで、PMT関数を講義することがあるのですが、
なかなか馴染みがないというか、日常、日々PMT関数を使うという事はあまりないので、
苦手とかわかりにくいというイメージを持っている方も多いようですが、
どうせなら分析機能のゴールシークも一緒に勉強したほうが、
PMT関数もゴールシークもいっぺんに覚えることができるし、
苦手なイメージもなにをやっているのかをイメージしやすくなるように思います。

そこで、今回はPMT関数とゴールシークを使って、100万円を目標にして積み立てるとして、
月額いくら積み立てたとしたら、頭金はいくらあればいいのか？
というのがわかる表を作って

PMT関数とゴールシークを紹介していきます。

では、下記の表があります。

目標積立金額が1,000,000円で積立回数が12ヵ月つまり1年で、
金利が0.03%で、まずは頭金が0（ゼロ）だとしたら、月々の積立額はいくらでしょう？
というのから算出していきます。

C4をクリックして、PMT関数のダイアログボックスを表示しましょう。

入力する項目が多くて、ココでまず嫌いになりますが、大したことは聞いていないので、
一つずつ確認してきましょう。

利率ですが、ここは、金利（年）のことですから、C6。

ただ、求めたいのは月々いくら積み立てればいいの？って訳ですから、
金利（年）を12で割って、月にします。ですから、

利率は、C6/12

期間は、積立回数（月）のC5。

現在価格は、頭金になりますので、C8。
ただ、この手の財務関数の特長なのですが、手元から出ていくものをマイナスで扱うので、
このままですと、マイナス表記にしないと正数になりません。ですから、-C5とします。

将来価値は、100万円ですから、C3。
支払期日は、今回は期首払いとしますので、1と入力します。

あとはOKボタンをクリックしましょう。

頭金ゼロだと、月々83,320円なら目標を達成できるという訳です。

C4は手元から出ていくので、マイナス表示になっていますが、
正数にしたい場合には、数式のPMTのPの前に-（マイナス）を入力するといいですね。

数式はこうなります。

=-PMT(C6/12,C5,-C8,C3,1)

しかしながら、月々83,320円も払えないよ。
月々3万円なら、頭金をいくら入れればいいのかな？とした場合、
ここで、ゴールシークが登場します。

シート内のどこでもいいので、アクティブにしておいて、
データタブのWhat-If分析にあるゴールシークをクリックします。

ゴールシークのダイアログボックスが表示されてきますね。

数式入力セルには、PMT関数が入っている、C4をクリックします。$C$4で入力されます。

ポイントは、数式が入力されているセルということです、
答えを出したいほうは変化させるセルになります。

目標値は、今回は月々3万円ですので、30000

変化させるセルは、頭金ですから、C8をクリックすると、$C$8と入力されます。
あとは、OKボタンをクリックします。

収束値がみつかりましたら、OKボタンをクリックします。
すると、639749.5434と表示されてきました。

これでは、よくわかりませんので通貨表示形式を設定してみましょう。

頭金が約64万円！
だそうです。

こりゃ～。この頭金を用意するほうが大変だよね。なかなか100万円は遠いものですね。

このように、PMT関数とゴールシークをペアにして、
ふれる回数を多くしていくと、わかりにくいスキルも定着していくことが出来ますよ。

Windows10。【緊急企画】これは何のボタンだ？Windows10を入手するボタン！！

Windows10。【緊急企画】これは何のボタンだ？
Windows10を入手するボタン！！

Windows10

色んな方から、【時計の近くに見たことがないボタンがある】。
とご連絡があり、どうしたらいいの？って、聞かれましたので、確認してみると、
たしかに、見慣れぬボタンが…

マウスカーソルを合わせると、なんと、【Windows10を入手する】っていうボタン。

なんだそうでして。
ウイルスかな？どうしたらいい？って質問がありましたので、
今回は、この【Windows10を入手する】ってボタンを押すとどうなるのか？
をご紹介しようと思います。

さて、マイクロソフトさんの発表によりますと、Windowsの新しいOS。
【Windows10】が7月29日にリリースするそうでして、
Windows7～8.1は、1年間無償アップデートの対象になるとのこと。

7月29日なんですかぁ～って、対岸の火事ではありませんが、
まぁリリースされてから様子見って思っていたら、なんと知らない間に、
タスクバーの時計の近くに、見たことのないボタンが…

なんだこれ？

うちにある、Windows7にも、Windows8.1にも登場しているこのボタン。
マウスカーソルを合わせると、なんと、

【Windows10を入手する】

っていうボタン。だそうでして、

恐る恐る、クリックしてみると。

無料アップグレードの手順っていう画面が表示されてきました。

なんだ、アップグレードされちゃうわけではないんですね。

Windows10に関する情報を6ステップにわけて紹介してあるんですね。
で、よく見ると、無料アップグレードの予約というボタンがありますので、
これも、恐る恐るクリックしてみると、

アップグレードが予約されています
っていう画面になりました。

この画面は、Windows10のインストール準備が整ったら、
メールアドレスにお知らせしますってことなんだけど。

よく読むと、7月29日に、Windows10が利用可能になると、
ご使用のデバイス（PC）にWindows10がダウンロードされます。って書いてある。

ということは、Windows7以降のPCには、
取りあえず、ダウンロードはしちゃうわけなんですね。

Windows7でないと困るとか、Windows10で動くかどうかわからいソフトとかあると思いますが、
関係なく、取りあえず、ダウンロードしちゃうわけですね。

なので、気を付けないと、知らない間に、Windows10になっちゃうわけですね。

う～む。気を付けないといけないのかもね。

で、この画面で、メールアドレスを登録して、確認の送信ボタンをクリックしてみると、

完了しました。
という画面にかわりました。

7月29日。どのようなメールが来るのかはわかりませんが、時計の近くに登場した、
この【Windows10を入手する】ボタンは、このような内容のボタンでした。

急に登場したら、ビックリする人も、そりゃ～いるでしょうね。

Excel。TRANSPOSE関数。表を行列入れ替えてして、さらにリンクさせる方法はないですか？だって。

Excel。表を行列入れ替えてして、さらにリンクさせる方法はないですか？だって。

配列+TRANSPOSE関数

先日雑談中に、表を作ったんだけど、横に長くなったので、
縦にして印刷できるようにしたいんだけど、
入力するから出来たら元のデータはそのままでっていうことは出来ないかなぁ～と、

質問されて、何をいっているかよくわからなかったので、よく聞いてみたら。

上の表みたいなものがあって、縦で印刷させたいと、

そこで、別のシートに、行列を入れ替えてコピーするだけではなくて、
上の表のデータを変更したら、別シートの数値もリンクされたように変わってくれないか？

ってことらしいので、今回はそれに挑戦したいと思います。

別シートだとわかりにくいので、同じシート上でやっていきたいと思います。

では、確認で、行列を入れ替えてコピーする方法から確認していきましょう。
このようなことをやっていきます。

まずは、A3:D7を範囲選択します。ホームタブのコピーボタンを押して、A10をクリックします。

そして貼り付けボタンの▼をクリックして、行列の入れ替えボタンをクリックしてみましょう。

すると、行列を入れ替えて貼り付けることが出来ましたね。

あとは、B10:E10までを中央揃えにしたりするといいですね。

まぁ、行列を入れ替えてのコピーだけならいいのですが、今回は、リンクさせたい訳ですね。
このままでは今回は片手落ちなのですね。

では、一つずつのセルをセル参照させていくとしたら、大変な処理になりますよね。

そこで、配列関数+TRANSPOSE関数を使うことによって、
今回のリクエストをかなえることができるのです。


TRANSPOSE関数ってなじみが全然ないですよね。

これは、行と列を入れ替る関数なのですが、配列関数で処理をしないといけません。

配列関数を説明は避けるとして、手順としては、TRANSPOSE関数を作ったら、
その関数を範囲選択して、

Ctrl+Shift+Enter

を押すと、数式が配列関数に変化することができます。

では、やっていきましょう。

ポイントは先に範囲選択をする必要があります。必要な範囲を先に確認しておきましょう。

今回は、G3:K6を範囲選択します。

そして、TRANSPOSE関数ダイアログボックスを表示しましょう

配列には、元のデータの範囲である、A3:D7と入力してOKボタンをクリックします。

#VALUE!というエラーが表示されていますけど、無視して、
数式バーの数式を範囲選択しましょう。

ここで、配列関数にチェンジしますので、Ctrl+Shift+Enterキーを押します。
すると、

{=TRANSPOSE(A3:D7)}と数式の前後に　｛　｝のカッコがついたら大成功。

配列関数になりました。G3:K6をみてみると、

エラーがなくなり、数値が反映されていますね。
では、2011年4月のデータを100に変えてみましょう。

H4は100に変わりましたが、単なる貼り付けしたB11はそのままなのが確認できますね。

Excelは、配列というアイディアも含めていくと、さらに奥深いものになりますので、
まずは、基本スキルを定着させてから、奥深い所に進んでいくのがいいでしょうね。