カテゴリー別アーカイブ: 裏技

バブルチャートをBIツールで見やすくする

 企画の仕事をしている時、バブルチャートは3つの値(X値、Y値、そしてバブルの大きさ)で関係性を表せるのでかなり重宝していました

但し、バブルの数が多いと下の図のようになってしまい、一目で見る気が失せてしまいます

ところが、Power BI Desktopを使うと、バブル数が多くてもシンプルに表現できますので、ぜひ概要だけでも紹介しておきたいと思います

Power BI Desktopインストールとデータ取得

今回の記事では詳細は省かせて頂きますが、インストールはこちらから行います

インストールしたPower BI Desktopを開くと上の画像のように「データを取得」ができるようになります

下の画像が今回使用するデータです

「データを取得」をクリックすると、データの取得先のファイルおよびシートを指定できます

バブルチャートの作成

データを取得すると画面右に取得した内容が列別に表示されます

その後は、バブルチャートのマークをクリックします

するとバブルチャートの設定を行えるようになります

こちらはピボットテーブルの感覚で行えます

取り込んだ列を該当箇所にドラックしていくだけです

通常のバブルチャートであれば、ここから軸の最大値等を調整して作業終了となります

ここから「再生軸」に月データを設定します

再生軸を設定すると、バブルチャートの左下に再生ボタンが設定されます

再生ボタンを押すと、再生軸に指定した月別にバブルが表示がされていきます

ちなみにバブルをクリックすると、次の画像のように月間の関係性が見えるようになります

<まとめ>

今回はPower BI Desktopを使用してバブルチャートをシンプルに表示する方法を解説しました

通常のバブルチャートの設定に「再生軸」という軸を加えて、再生軸別に表示ができるようになります

グラフを動画で表示することになるので、数字の背後にあるストーリーも分かり易くなります

データの世界もまた新しい次元にきたという感じがします

今後は、このPower BIによるデータビジュアライゼーションの世界を深堀していきたいと思います


にほんブログ村

名前機能について

皆さんは、学生時代に友達にあだ名とかつけていませんでしたでしょうか?

例えば、佐藤という苗字の友人が複数いたら、呼ぶのに便利ですよね?

このあだ名はエクセルを使用する際にも結構、有効活用できます

エクセルのセミナーを行っていて、「あれ?あまり知られていないな・・」と感じたのが名前機能です

これは結構、便利なのでぜひ有効活用して頂きたいと思います

この名前機能を有効活用すると、次のGIF画像のようなこともできてしまいます!

名前機能とは何?

文字通り、セルや範囲に名前、すなわちあだ名をつけてあげる機能です

他人が作成したエクセルファイルは使いにくいですよね

実は自分自身でも「あれってこの数式ってどうなってたっけ?」と後から思うこともあります

常に重要なセルの箇所や範囲に名前をつけて管理できるようにしておけば、エクセルを使いやすくできます

ちなみに、付けた名前は「数式タブ」の「名前の管理」で一覧化されます

つけた名前の一覧は名前ボックスからも確認できます

名前の付け方

何種類か方法はありますが、今回は一番シンプルな方法だけ解説します

下の画像のように、「名前の管理」の右にある名前の定義から①名前と②参照範囲を設定します

上の画像の場合は、下の画像のペンギンを参照範囲として指定しています

もし、後から「ペンギン」とは何のことか忘れてしまった場合は、名前ボックスからペンギンの場所に移動することができます

関数内での名前の使用

名前は関数の中でも使用することができます

例えば、下の画像の表に「検索範囲」と名前を付けておきます

この検索用の範囲をVLOOKUP関数に使うのですが、名前で代用することが可能です

<まとめ>

短い内容でしたが、いかがでしたでしょうか?

名前機能についてのイメージは明確になりましたでしょうか?

この名前機能は様々な応用が効きます

このブログでも2つ事例を紹介しています

興味のある方はぜひそちらの記事もご参照ください

1.VLOOKUP関数の参照範囲を切り替える 記事

下のGIF画像では、VLOOKUP関数の2つの参照範囲に「レディース」「メンズ」と名前をつけています。

VLOOKUP関数内のINDIRECT関数が都度、2つの名前を切り替えながら参照しているイメージになります

2.表示画像の切り替え記事

スライサーなどで表示を切り替える際に、表示画像も変えて「何に切り替えたのか?」を分かり易くします

こちらも1と同じくINDIRECT関数を使用します


にほんブログ村

ピボットテーブルに引いた罫線を維持する方法

 作成したピボットテーブルに、罫線を引いても更新すると消えてしまう・・・、または表を変更すると消えてしまう・・・こんな悩みはないですか?この悩みには2つの意外な抜け道があります!

1つ目は、ピボットテーブルオプションの設定を変える方法

2つ目は、ピボットテーブルのデザイン自体を新規に設定する方法です

ピボットテーブルオプションの設定を変更

ピボットテーブル上で右クリックをすると、下の画像の黄色い印を付けた箇所のように「ピボットテーブル オプション」のマークが出てきます

こちらをクリックすると、下の画像のように「更新時にセル書式を保持する」が表示されます

こちらにチェックを入れると、更新しただけでは罫線は消えなくなります

但し、これだけだとピボットテーブルの表自体を変更すると罫線は消えてしまいます

デザインを新規設定

ピボットテーブルのデザイン自体を「罫線」を引くように設定すると、表自体を変更しても罫線は消えません

このデザイン自体の設定を行うには、まずデザインタブ内で以下の画像の「黄色の箇所」をクリックしてください

すると様々なデザインが選択できるようになります

どれか自分のイメージに近いデザインを選択し、右クリックしましょう!

すると上の画像のように「複製」という文字が表示されますので、こちらをクリックします

これで、選択したデザインをテンプレートにして、自分なりのデザインが設定できるようになります

上の画像のように「テーブル全体」を選択したまま、画像下にある「書式」を選択すると罫線を引く画面が選択できるようになります

上の画面にて、罫線を引いた後は、元の画面に戻りますのでOKボタンを押します

通常であれば、これでピボットテーブルに罫線が引かれていそうですが、ここからがトリッキーです

デザインタブ内に戻り、設定したデザインを選択しないと罫線は反映されません

こちらは注意が必要です

<まとめ>

今回は、ピボットテーブルに引いた罫線を維持する方法を解説しました

こちらは元々は、ストアカの講座内で生徒様から質問を受けた内容です

 私のストアカのレッスンでは、ピボットテーブルをそのままレポートに使えるデザインにするレッスンも行っていましたが、「罫線」についてはあまり深く考えたことがありませんでした

やはり、

教えることとは、教えて頂く事

とても勉強になります!


にほんブログ村

図形の意外な使い方を2つ教えます!

 皆さんは、図形の中に文字列を入れる時はありますか?その作業、資料作成の度に毎回行っているのであれば手間を削減できる可能性があります

後、図形には別シートや別セルへのリンクも埋め込むことができます

図形にリンクを埋め込むことができるとクリックが行いやすくなり、とても便利です

文字列を図形に入れる

直接は、図形の中に数式は入れることができません

但し、数式バーを活用すれば「数式」を図形の中に入れることができます

こうしておけば、資料を作成する度に図形の中の文字を変えなくても、図形の中の文字列は変わります

注意点としては、数式バーの中であっても関数は使えません

ですので、文字列を組み合わせたり、桁区切りをする場合は他のセルを経由する工夫が必要です

上の画像のようにB2セルに関数を組み込んで置き、図形はB2セルを参照させればOKです

リンクを埋め込む

図形の上で右クリックをすると「リンク」という文字が表示されると思います

こちらから、他シートやセルへのリンクが設定できます

上の画像のリンクをクリックした後に表示されるボックスでは下に「リンクを挿入」という文字が見えますので、こちらをクリックしてください

すると、下の画像の画面が開きます

こちらでリンク先の「シート」「セル」を指定します

まずデフォルトでは、左の「ファイル、Webページ」が選択されていますので、こちらを下の「このドキュメント内」に選択し直してください

シートの指定についてですが、一見では分かりにくいですが、「セル範囲」の下に各シート(テーブル演習・・・)が表示されています

こちらでシートを選択します

次に、「セル参照を入力してください」の下にセル名の指定を入れてください

<まとめ>

教えることは教えてもらうこと!

最近、ストアカで講師をする機会が増えて感じるのはこのことです

「上の図形の工夫は意外と知られていない」という事は生徒さんとの話で気づきました

この図形の工夫をするだけでも、皆さんの業務は随分変わってくると思います

小さなことでも皆さんのお役に立てたら幸いです

今後も、新たな気づきを得たら積極的に発信していきたいと思います


にほんブログ村

INDIRECT関数を使いこなす!

5百あるエクセル関数の中でも、「ややこしい」が「使い勝手があり」「個性が強い」のがINDIRECT関数です

「INDIRECT関数って何ができるの?」と聞かれたら、少し返答に困ってしまいます

でも、確実に言えるのはこの関数は使えるということです!

今回は、INDIRECT関数の世界を覗いてみましょう!

基本形

今、下の画像のようにD2セルに「おやすみなさい」と入力されているとします

そして、B2セルには「D2」と「おやすみなさい」の文字が入力されているセルの名称が入力されています

上のGIF画像のようにINDIRECT関数の引数としてB2セルを指定すると、D2セルの内容が出力されます

名前参照

今度は、下の画像のようにD2セルに「挨拶」と名前を付けます

B3セルには「挨拶」の文字が既に入力されています

では、基本形と同じ様にようにINDIRECT関数の引数にB3セルを指定します

繰返しになりますが、「おはようございます」の挨拶が入力されたD2セルは「挨拶」という名前が設定されています

すると、下の画像のようにD2セルの内容が基本形と同じ様に出力されます

画像の抽出

INDIRECT関数をうまく使えば、画像の抽出も行えます

上の画像のように、東京タワーや自由の女神などの画像が入ったセルに名前をつけておきます

この画像をカメラで別な場所に映しだすと、下の画像のように数式バーでは「画像の入ったセル」を参照しています

なお、上の画像は実際にはトリミングを行っています

こちらの数式バーを先ほど名前をつけた「アメリカ」に変えても画像は同じです

では、名前と画像の関係をお話したので、前述のINDIRECT関数の仕組みを使用して画像を自由に変えられるようにしてみましょう

今回は、画像という名前の中にINDIRECT関数を設定します

下の画像のように「画像」という名前にて、上の画像のF1セルをINDIRECT関数を通じて参照するように設定します

こうしておけば、INDIRECT関数を通じて、F1セルに表示された名前の画像をカメラ機能で表示することができます

ちなみに、セルは絶対参照にしておきましょう!(セルの位置がズレていくことがあります)

<まとめ>

いかがだったでしょうか?INDIRECT関数の面白さを体感して頂けたと思います

今回は、最後、画像の抽出の事例を紹介しましたが、INDIRECT関数を使用してVLOOKUP関数の参照範囲を変えることもできます

つまり、INDIRECT関数を使って関数を二次元で使うことができるということです

興味のある方はそちらの記事も参照してみてください


にほんブログ村

関数で比率をN:N形式で自動出力

GCDというエクセル関数をご存知でしょうか?

こちらの関数をもし知っているという方がいたら、とても通ですね!

今回は、このGCD関数を使用して「比率/N:N形式」を自動出力します

上のGIF画像では、700と2100の2つの値の比率「1:3」を自動出力しています

では、ポイントを絞ってこの比率の出力方法を解説します

前述のGCD関数とは、引数に指定した値間の最大公約数を算出する関数です

今回は、まずはGCD関数を使用して2つの値間の最大公約数を計算します

次に、算出した最大公約数で「値1」と「値2」を割ります

例えば、上の画像でケース2の値1は21なので、算出された最大公約数で割ると7になります

一方、値2の方は1になります

このように、各値を最大公約数で割れば比率が出力できるようになります

今の最大公約数を算出してから、比率を出力する過程を数式にすると、次の画像のようになります

結構、シンプルな数式で比率が出力できたと思います

ちなみに、今回使用したエクセルのバージョンは次の通りです

今回の解説は以上です

最後まで記事を読んで下さり、誠にありがとうございます

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

話すエクセル機能

Office365では、エクセルに話す機能が付きました

セルに記入してある文字列や数字を、そのまま読み上げてくれます

使い道は別として、話すエクセルの操作方法を2種類に分けて解説したいと思います

クイックアクセスツールバーに追加する方法

追加処理

まずは画面上の、下の画像の黄色に印をつけた箇所をクリックしてください

次に、下の画像の画面が開くので、下のほうにある「その他のコマンド」をクリックします

次に開いた画面で「リボンにないコマンド」を選択します

そして、下のほうにスクロールしていくと、吹き出しのようなマークのコマンドがあります

上の画像で黄色に印をつけた4つとも、追加処理を行います

操作方法

①表の上にカーソルを置く

見出しではなく、データ部分にカーソルを置きます

②行単位で読み上げるか?列単位かを選択

読み上げるセルは、行単位か、それとも列単位かを選択できます

行単位を選択すると、カーソルを右へ右へ移動しながらセルの内容を読み上げていってくれます

③読み上げをスタート

②で列単位か、行単位かを選択したら、セルの読み上げをスタートします

スタートするには、下の画像の、黄色に印をつけた箇所をクリックします

下の動画のように、セルを読み上げて移動していきます

https://youtu.be/QoaF2hd8JQ0

読み上げを止める時は、下の黄色に印をつけたところをクリックします

マクロでセルを読み上げる方法

こちらは、とても単純です

コードを次の様に書くだけです

セル範囲.speak (例:Range(“C3”).Speak)

下の動画で動かしているマクロの中には、 「Range(“C3”).Speak」のコードが入っています

「同意する」にチェックを入れないで保存ボタンを押すと、「同意にチェックを入れてください」というC3セルの内容が読み上げられます

https://youtu.be/x9W0I78DsKI

こちらは、神エクセルのような、エクセルで作成する「申込書関係」なんかに使えると思いますね!

ちなみに、動画の中の「クリック」マークもマクロで動かしています

今回は以上です!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

条件付き書式で背景を変更

 コロナ疲れで「桜」が恋しくなってきましたので、エクエルシートの中に桜を咲かせてみました

仕組みはとても単純です!

 条件付き書式を使用して、各セルに入力されてある値が「参照セル」の値以下だと「セルの背景」が白になるようにしてあります

つまり、上の画像の黄色の箇所(セルK1)がちいさくなる

   ➡各セルの背景の色が「白」から「元の背景の色」に変わる

という仕組みになっています

では、桜をエクセルシート内に咲かせる仕組みについて解説します

但し、桜の画像については事前に準備する必要があります

1.背景の設定

ページレイアウトタブから準備した画像を背景に設定します

上の画像の「背景」をクリックすると、画像を指定できるようになります

背景を設定したら、1列は白にして後の列は非表示にします

下の画像で詳細を解説すると、K列は白色に設定し、L列より右は「非表示」に設定します

次に表示タブにて、目盛線も非表示にします!

2.数字をランダムにセルに入力

RANDBETWEEN関数を使用し、「1~100」の数字を各セルに散りばめます

フォントの色は以下の画像のように、「白」で設定します

更に、フォンの文字が目立たないように「フォントサイズ」を極小にします

3.数字を変動させるセルを設定

条件付き書式で参照するセルは「スピンオフ」ボタンで設定します

スピンオフをはじめ、「フォームコントロール」の詳細は過去記事をご参照ください

スピンオフボタンをエクセルシートに設定したら、スピンオフボタン上で右クリックします

次に開いた画面で、2.で各セルに設定した「1~100」の値を「リンクするセル」のK1セルに出力できるようにします

4.条件付き書式の設定

記事の冒頭で紹介したように、3.で設定したセルK1の値以下の場合には、セルの色が白になるように設定します

次の画像が、設定する条件式です

上の画像の条件に合致する場合には、セルの色が白になるように書式を設定します

上記の1~4を設定すれば完成です

今回紹介した条件付き書式の使い方は下のGIF画像の「ワッフルチャート」でも使用しています

興味のある方はこのワッフルチャートにも、ぜひ取り組んでみてください

では、長文に最後までお付き合い頂き誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

Office365小技集~右クリックで一発処理~

 今回は、Office365で「右クリック」を使用して、エクセル処理を省力化する技を紹介します

1.Power Query

表の上で右クリックすると、次の画像の箇所がクリックできるようになります

表をテーブル化していない場合には、次の画像の画面でOKボタンを押せば、そのままPower Queryエディタが立ち上がります

もちろん、表をテーブル化してあればそのままPower Queryエディタが開きます

これでPower Queryがより簡単に処理できるようになりました!

2.テーブル

1.と同じ様に右クリックすれば、集計行を出したり範囲に変換することができます

これでわざわざ画面上のタブを操作しなくて済みます

3.フィルター

こちらもとても便利です

わざわざ画面上でフィルターをかけた後に、フィルターをかける値を選択しなくて済みます

今回は以上です

右クリックを活用して、エクセルの単純作業はぜひ爆速化していきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

エクセルアドイン~ファイル内の動きが重い箇所を見つける

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

 忙しい時に、動きが重たいエクセルファイルを開いてしまい、憂鬱になることってありませんか?

これ、いつ動くんだろう??

計算処理があまりに遅いので、時計を見ながら恐怖さえ感じることもあります

 今回、紹介するのはファイル内で「動きが重い箇所」をみつけてくれるアドインです

尚、今回紹介するアドインは情報が少ないので、推測で書いた場所は(推測)と記しています

1.アドインの入手

挿入タブの「アドインを入手」から「Bottleneck detector」を検索します

次の画面が出たら追加をおします

すると、ホームタブにアドインが追加されています

2.アドインの起動

アドインのアイコンをクリックすると、画面右に次のような画面が出てきます

①重たいシートの発見

まずは、「Detect Slow Sheets」を押して「動きが重いシート」を見つけましょう

 すると、再計算処理を行った時に秒数(推測)を要した順でシートが表示されます

上の図では「A1:K53」と範囲が記されています

これは、シートの中の「動きの重い箇所」を示しているのではなく、シート内のデータが含まれる範囲を示しています(推測)

表示されたシート名をクリックすると該当シートに移動します

②重たい範囲の発見

次にどのシートかは考慮せずに、「重い箇所」を見つけます

上の画像の「Detect Slow Ranges」をクリックします

すると、次の画像のように重たい箇所が順に表示されます

<まとめ>

 今回はファイル内の動きが重たい「シート」と「範囲」を見つけるアドインを紹介しました

そもそも、ファイルを重たくしない工夫が事前に必要かとは思います

 ボタンが「シート」と「範囲」に分かれていますが、この2つは連動はしていません

 重たいシートとして表示されたシートをクリックすると、該当シートに移動します

 但し、その後に「範囲」の方をクリックしても、該当シートの中で重たい範囲を絞り込んで表示するわけではありませんので注意が必要です

後、使用する上で注意点が記されていたので、抜粋して次に紹介します(推測)

・このアドインを起動する前に、ファイルは保存しておいてください

・複数のファイルを開いたままこのアドインは使用しないでください

尚、このアドインを使用する際には、「ポリシー」等に同意する必要があります

こちらについては各自でご確認の上で使用してください

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村