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

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つとも、追加処理を行います

操作方法

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

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

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

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

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

③読み上げをスタート

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

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

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

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

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

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

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

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

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

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

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

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

今回は以上です!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 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へ


にほんブログ村

エクセルアドイン~長く複雑な数式の修正補助~

 エクセルを使用していて、長くて複雑な数式を修正するのに苦労したことはないでしょうか?

 過去に長くて複雑な数式を解読するアドインを紹介したところ、相当なアクセスが記事にありました

 エクセル関数を使いこなせるようになると、関数のネストをする機会がどうしても増えてきます

ネストをする度に数式が長く複雑になるのはどうしても避けられませんよね?

 今回紹介するこのアドインならば数式を引数毎に縦に分解してくれるので、長く複雑な数式でも修正しやすくしてくれます

1.アドインを入手

挿入タブの「アドインを入手」から「Formula Editor」を入手します

「Formula Editor」が見つかったら、右横の追加ボタンを押して下さい

もし「入手方法や詳細なアドインの使用の仕方が分からない」という場合には、過去のこの記事をご参照ください

2.数式の修正

1.でアドインの入手を行うと「Formula Editor」が画面上に追加されています

上の画像にある「Formula Editor」のタブをクリックすると、次のような画面が出てきます

左にある「Formula Editor」ボタンをクリックすると、アドインがエクセル画面の右に起動します

①Editorのアクティベート

修正したい数式があるセルにカーソルを置き、アドインのアクティベートを行います

Formula Editor

②数式の修正

数式の修正は、Formula Editorの画面内で行います

上のGIF画像の数式は、「MAX」の部分を「MIN」に修正する必要がありますので、MAXの部分をMINで上書きします

今回は、Editor内でショートカットキー:Ctrl+Fを使用して自動置換を行います

数式の修正が終わったら、下にあるボタン「Unformat & Write to Cell」を押し、修正した数式を書き込みます

<まとめ>

 筆者が使用してみた感触だと、このアドインを使用すれば数式の修正はとても楽になりそうです

 ただ、通常の数式入力の場合は数式にどこかエラーがあると、Enterキーを押した時にアラームがでます

 このアドインの場合には「エラー」があってもアラームが出ず、セルにも反映されないようです

 このアドインの情報が少ないので、詳細が判明したら記事を改めて書きたいと思います

 もし、使用してみた方がいらっしゃったら感想などをコメント欄に残して頂けると嬉しいです

 なお、このアドインを追加する時にはライセンス条項等に同意することになるので、内容を確認の上でのご使用をお願いします

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


にほんブログ村

 

 

文字の中に棒グラフを入れる

今回は、文字の中に棒グラフを入れることで、説明する数字により強いアクセントをつける方法を紹介します

文字の中に棒グラフ

棒グラフを活用するだけなので、作成の仕方はとても簡単です

今回は、作成に当たってのポイントだけ解説します

1.棒グラフを作成する

元データには2つの数字が必要です

この2つの数字を元にして、積み上げ棒グラフを作成します

こちらのグラフはタイトルや軸など無駄なものは全て消去し、次の画像のようにしましょう

2.棒グラフをリンクされた図としてコピーする

ここが、最大のポイントです

グラフを後で、%の文字にあわせて回転できるようにコピーします

この時グラフ自体ではなく、グラフが入ったセルを「リンクされた図」としてコピーします

リンクされた図とは、次の画像のコピーの仕方のことになります

3.文字に合わせて、棒グラフを設定する

パーセントの文字は2つのテキストボックスに分けましょう!

上の画像で言えば50の部分については、グラフの元データを50%から「50」に変換したうえでテキストボックスに設定しましょう

ちなみに、テキストボックスに数字を入れる時は「数式バー」で設定します

テキストボックスの中に数式を入れても機能しません

最後に、2.でコピーした棒グラフの図を文字に合わせて設定すれば終了です

今回の解説は以上になります

エクセルでも使い方を工夫すれば、これまで思いつかなかったようなことができるものです

このブログでは他にも様々なデザインのグラフを紹介しています

もし興味がある、という方がいらっしゃいましたらこちらをぜひ覗いてみてください

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


にほんブログ村

エクセルアドイン~表を自由自在にカラフルにする~

こんにちは、Excellent仕事術ガッツ鶴岡です

このブログでは、これまでも便利なアドイン(アプリ)を紹介してきましたが、今回紹介するアドインもなかなか便利です

表を1色で塗りつぶす方法はエクセルのデフォルト機能で行えます

一方、次の画像のようにストライプにする場合はどうでしょう?

自身の好みで色を調整しながら、ストライプで色をつけようとすると物凄いガッツと手間を要します

今回、紹介するアドインはこのストライプでの色付けを自動で行ってくれます

しかも、ウェーブやグラデーションを指定して色付けすることもできるのです

ウェーブ
グラデーション

では早速、このアドインの解説に入ります

1.アドインの入手

エクセルの挿入タブから「アドインを入手」をクリックして下さい

そして、「Excel Colorizer」を検索しましょう

「Excel Colorizer・・・」が出てきたら、右横の追加ボタンを押しましょう

入手方法の詳細や、使用方法が分からない場合には、こちらの記事をぜひご参照してください

2.ストライプをつける

このアドインを入手すると、エクセル画面の右に操作画面がでてきます

今回の解説では設定箇所をなるべく絞って解説します

①Type

Uniformで指定します

Uniformを指定したら、ベースとなる色も指定しましょう!

②Pattern

Interlacedを指定します

③Pattern Contrast %

ストライプ柄の強弱を指定します

50で指定すると次のGIF画像のようになります

ストライプ/Contrast50

5で指定すると、ストライプの強弱が弱くなります

ストライプ/Contrast5

なお、必ず色付けをする範囲をカーソルで指定してから実行ボタン「Colorize!」を押すようにしてください

それでないと、実行内容が範囲に反映されませんので注意が必要です

3.ウェーブをつける

Typeについてはストライプと同じ、Uniformを設定します

①Pattern

Wavesを指定します

②Pattern Waves

ウェーブのピッチを指定します

Contrast50、Pattern Wavesを3で指定すると次のGIFのようになります

同じくContrast50、Pattern Wavesを1で指定すると次の画像のようになります

少し、色の強弱のピッチ強まります

4.グラデーションをつける

①Type

今度はTypeを「Vertical」で指定します

そして、色も2種類を指定します

②Pattern

今度は「None」を指定します

①②を指定して実行すると次のGIFのようになります

<まとめ>

 今回紹介した3つ「ストライプ」「ウェーブ」「グラデーション」を手動で実行しようとしたら物凄い手間なので、このアドインを有効活用すればかなりの効率化になります

 今回は紹介できませんでしたが、水平方向やマトリックスでも色付けすることもできます

 色付けのイントネーションについても、下の画像の箇所で調整できます

個人的には、表の見せ方も立派な「DX」だと思っています。ぜひ、このアドインを有効活用してDXを小さなところから進めていきましょう!

なお、このアドインを入手する時にはライセンス条項などに同意することになっていますので、必ず確認をお願いします

他のアドインも興味があるという方は、こちらの記事から参照できます

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


にほんブログ村

【裏技】1列のデータを複数列に展開する

今回は、エクセルの特質を利用した裏技を紹介します!

下の図のように、1列にデータが規則的に並んでいるとします

この1列のデータを見やすいように複数列に展開します

この展開作業には関数やマクロは使用しません。エクセルの特徴を利用するだけです

1.セルの位置を2行だけ展開する

セル位置を、見出しの下に2行だけ記入していきます

「=」から始まる数式で記入しないのがポイントです

2.複数行をカーソルで指定して下にドラッグする

1.でセル位置を記入した2行をドラッグし、下にドラッグします

実は、この時点できちんと各セルの「セル位置」の指定がパターン化されています

3.「セル位置」を「=」で置換

ショートカットキー:Ctrl+Fにて置換え画面を立ち上げましょう!

置換を次のように実行しましょう

「セル位置」⇒「=」

すると2でパターン化された「セル位置」の指定に従って数式が展開されます

ちなみに、指定範囲の書式は「#,###」で指定してあります

この指定を行っておかないと、空欄を指定した数式が0を返します

試しに、参照もとの1列にデータを追加してみましょう!

0ではなく、追加された値を反映します

<まとめ>

 今回は、文字列をパターン化してコピーしたものを、後から=で置き換えることとにより、1列のデータを規則正しく複数列に展開しました

 日常業務で使う場面が多いテクニックだとは思いませんが、「エクセルは工夫次第で様々なことができる」と感じて頂ければ幸いです

長文を最後まで読んで頂きまことにありがとうございました

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


にほんブログ村