FC2ブログ

Excelじゆうちょう

Excelのお絵描きツール『りっぷ2(りっぷつぅ)』のサポートページ、まずは「はじめに」をご覧ください。 [NewEntry] [Admin]

記事更新カレンダー

04 « 2019-05 « 06
- - - 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 -

やたらに多いカテゴリ

比較的新しい記事

新しいコメント

ありがたいブログ拍手

拍手コメント一覧(拍手はしない)

さみしいトラックバック

申し訳ないプロフィール

申し訳ない

管理人  [ 申し訳ない ]

pxivもやってます
リンクの一番上からのぞきに来てください
※閲覧にはユーザー登録が必要です

RSSってなんぞ?

広告は消せないらしい

FC2Ad

        --------       スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

        2011-03-06       INDIRECT関数の応用、おまけ

INDIRECT関数を使って、ごくごく簡単なじゃんけんゲームを作ってみました。
簡単というか、ゲームと名乗るにはあまりにみすぼらしいというか、まったく面白みのないじゃんけんです。
R23C2セルのドロップダウンリストからじゃんけんの手を選んだら、右側の手との勝ち負けを自動的に判定するだけのものです。
じゃんけん以外はできません、音も鳴りません、ノンマクロです。

↓じゃんけんの手を選ぶ↓
カメラDEじゃんけん

↓あいこだ↓
カメラDEじゃんけん、あいこ

↓勝ちだ!↓
カメラDEじゃんけん、かち

ただこれだけです。
せっかくですから、このブックをこの下にリンクで置いておきます。
ただし、拡張子を「.txt」に変更してますからね、各自で「.xls」に直してください。
そのまま開いてはいけません、右クリックから[名前を付けてリンク先を保存(K)]してくださいね、文字化けしますよ。

カメラDEじゃんけん_xls.txt

ブックの中ではシートの保護をかけてますがパスワードはありませんので、数式が気になる方は適当に解除して分析してみてください。



もひとつついでに、このカテゴリで紹介してきたサンプルもこの下に置いておきます。

↓これ↓
中間名で切り替え

INDIRECT関数の応用_xls.txt

文字化け注意は同様です。
ブログでは紹介してませんでしたが、このブックにはおまけを用意してます。
入力規則にIF関数を使用して、巡回リストを作りました。
じゃんけんの手をリストで選択できるのですが、現在選択しているのと同じ手は選べないリストです。
(現在がぐーなら、ちょきとぱーが選べるといった具合)

INDIRECT関数、ぜひぜひ使いこなせるようになってくださいね。
スポンサーサイト

        2011-03-01       INDIRECT関数の応用、つづき2

INDIRECT関数で図のリンク貼り付けを切り替えます。
前回 のラストで大きなヒントを残しておいたのですが、これにはもうひと手間が必要です。

図のリンク貼り付けの数式バーには、数式を使用することができません。
参照の名前を直接入力する必要があるんです。

↓試しに…↓
INDIRECT、数式は不可

↓エラー!↓
INDIRECT、エラー

第2クオータを参照させようとしましたが、うまくいきません。
参照名または定義名じゃないといけないと怒られてしまいました。
それじゃあ、それに従えばいいんですよね。

参照名は、R1C1やA1といったセル本来の名前のことです。
定義名は、 この前 セルに名付けた別名のことです。

[Ctrl]+[F3]を押してください。

↓名前の管理(旧Excelでは名前の定義)↓
名前の管理(名前の定義)

これが別名を管理する画面です、すでに各クオータと年間が登録してあります。
ここに図に参照させる名前を定義し、その名前を介して各クオータを参照させるようにします。
新しい名前を登録する場合は…わかりますね、[新規作成(N)]です。

↓もう入力しちゃった↓
名前の編集、中間名

必須なのは[名前(N)]と[参照範囲(R)]、[コメント(O)]は任意です。
[範囲(S)]は新規作成時のみ設定できる項目で、その名前を参照できるスコープを設定します。
例えば、範囲を特定のシートに限定してやれば、シートごとに同じ名前を、お互いが干渉しない状態で使用できるようになります。
今回はデフォルトのまま、ブック内ならどのシートからでも参照可能です。
ちなみに、これも新Excelから加わった機能なんですが地味に便利です。
ただし、名前ボックスから入力すると自動的にブックが指定されてしまいますので、[Ctrl]+[F3]のショートカットキーの役割がかなり大きくなります。

よく見ると、参照範囲でINDIRECT関数が使われてますね。
図に直接入力できないから、定義名を間にかませているんです。
最後に、図の数式バーへ「=中間名」と入力して完了です。

↓ちゃんと切り替わってますね↓
中間名で切り替え

どうでしょう、感動しません?(私はします)

さらにさらに、このカメラ機能は参照元のセル範囲上にあるものを映し出します。
ということは、その範囲上にグラフや画像があったとすれば、セル値ひとつでそれらを切り替えることが可能なんです。

        2011-02-17       INDIRECT関数の応用、つづき1

INDIRECT関数は、第1引数に与えられた名前の参照を返します。
それによって、セルの値を取得したりワークシート関数の引数に利用したりできました。
また、応用としてデータの入力規則のリストに使用すれば動的にリストを切り替えることもできました。

今回は、もうひとつの形で動的に結果を切り替える方法をご紹介します。
切り替える対象は、図のリンク貼り付けです。

旧Excelでは隠し機能であったため(メニューバーの[編集(E)]を[Shift]キーを押しながらクリックすると現れます)、いまいち地味な存在でした。
新Excelでは右クリックからでも選べるようになり、非常に使いやすくなってます。
まあ、機能自体は一緒ですけどね。
それだけ便利だということです。
あっそれから、なぜかアイコンは「カメラ」という名前で登録されてるのでご注意を。
アイコンの登録方法は こちら 、ぜひ登録しておくことをおすすめします。

このカメラ機能の詳しい説明は他のもっともっと気の利いたサイト様におまかせするとして、ここでは概要だけ紹介します。

↓まず、撮影したい範囲を選択してカメラアイコンをクリック↓
カメラ、シャッターチャンス

↓次に、お好きな場所をクリックするとリンク貼り付けされます↓
カメラ、リンク貼り付け

↓そして、数式バーを編集すればリンク元を変更できます↓
カメラ、リンク元を変更

カメラ撮影できました。
貼り付けられたのはあくまで図のオブジェクトですので、中のデータを図から変更することはできません。
ですが、リンクでの貼り付けですから、リンク元のデータを編集すれば図のほうにも反映されます。
うっかり編集されては困るような資料なんかを別シートからのリンク貼り付けにしておけば、ほんのちょっぴり安心できますね。(シートの保護があるからいらないなんてのは聞かないふり)

【禁忌事項】
元データのセル範囲上に、撮影した図を重ねてはいけません!
リンク元とリンク先が合わせ鏡のようになって、やりすぎるとPCのメモリがパンクしてしまう恐れがあります。
それから、あんまし大きい撮影範囲はCPUに負担がかかります。

↓PCのメモリとCPUを食い散らかす悪魔が現れるよ↓
カメラ、禁忌事項

さて、本題です。
この図のリンク貼り付けの参照元をINDIRECT関数で動的に切り替えてやろうというのが、この次のお題です。
これからがいいところですが、ちょっと長くなりそうなので続きます。

        2011-02-11       INDIRECT関数の応用

INDIRECT関数の応用技です。

なかなか説明が難しいので、簡単な実例に沿って解説します。

↓例えば、こんな表があったとします↓
国民の祝日表

国民の祝日を四半期ごとに分けた表です。
どんな用途があるかは考えないでください、あくまで例ですので。
この表から、下側にあるクオータ区分で1~4の四半期をリスト選択します。
そして、その右側の祝日で選択したクオータに含まれてる国民の祝日をリスト選択します。
リスト選択には[データの入力規則]ですね、新Excelでは[データ]タブの[データツール]の中にあります。

↓これ↓
入力規則(リスト)

まずは事前準備、セルに名前を付けます。
以下のように、それぞれのクオータのセル範囲に名前を付けます。
注意すべきは、項目名とセルの名前を一致させておくことです。
ですので、すでに使われてる名前や数字から始まる文字列など、名前に使えない項目名は避けなければなりません。

↓黄色の範囲に別名を付けました↓
別名(クオータ)

次に、これらの別名をリスト選択できるようにします。
先ほどの[データの入力規則]の[元の値(S)]にカンマ(,)で区切ってリストを作成します。
別の方法として、R1C1参照形式なら「=R2C1:R2C4」と指定してもOKです。

↓R9C1セル(クオータ区分)のデータの入力規則↓
入力規則(クオータ)

簡単ですね、ですが、祝日のリストはどうすればいいでしょうか?
「第1クオータ」なら「元日」、「成人の日」、「建国記念の日」、「春分の日」といった具合に、選択されたクオータ区分によって祝日のリストを切り替えないといけません。

それを可能にするのが、INDIRECT関数なのです。
INDIRECT関数は引数にセルの名前を文字列で受け取り、その参照を返します。
[元の値(S)]にクオータ区分を引数に取ったINDIRECT関数を適応させれば、それに対応したリストが作成されます。

↓R9C2セル(祝日)のデータの入力規則↓
入力規則(クオータ区分)

クオータ区分であるR9C1セルの値が「第1クオータ」なら「=INDIRECT("第1クオータ")」、「第2クオータ」なら「=INDIRECT("第2クオータ")」、「第3クオータ」なら「=INDIRECT("第3クオータ")」、「第4クオータ」なら「=INDIRECT("第4クオータ")」と同じ結果が得られます。
つまり、クオータ区分によってINDIRECT関数の引数が自動的に切り替わるのです。

↓リストが切り替わる↓
リスト(クオータ)

いかがでしたでしょうか?
例では祝日なんていう地味なものでしたが、それぞれに応用していただければかなり便利な機能ではないかと思います。
名前をつけたセル範囲の大きさを適切に設定しておけば、もっと大きなリストも可能です。
おまけとして、年間の国民の祝日リストも作ってみました。

↓「年間」という別名のセル範囲↓
年間リスト選択

        2011-02-09       INDIRECT関数の基本

ワークシート関数のINDIRECT関数、今回は非常に便利なこの関数を紹介します。

この関数は指定される文字列への参照を返します。
つまり、セルの名前を引数に指定してやれば、その名前のセルの値を取得することができます。
書式は以下の通り、引数をふたつ取ります。

INDIRECT(参照文字列,参照形式)
第1引数の参照文字列は、"A1:$B$3"や"R[1]C[1]:R2C3"などの参照したい対象の名前を指定します。
第2引数の参照形式は、左上端のセルをA1などと表示するA1参照形式と左上端のセルをR1C1などと表示するR1C1セルの参照形式を指定します。
A1参照形式ならTRUE、R1C1参照形式ならFALSE、省略するとTRUEとして扱われます。
これにより、数式上では常に表示形式統一することができます。

実際に見たほうがわかりやすいですよね。

↓基本的な使い方↓
INDIRECTの基本1

数式の左側のINDIRECT関数でR1C1セル(Excel)を、右側のINDIRECT関数でA2セル(じゆうちょう)を参照し、両者のセルの値を文字列連結(Excelじゆうちょう)してます。

少しだけ発展します。

↓これもまだ基本的な使い方↓
INDIRECTの基本2

R4C2セルの値(1)とR5C2セルの値(3)を使ってR1C3というセルの名前を作り、それを参照先として先ほどのExcelじゆうちょうを取得してます。
それにしても、R1C1参照形式は強いですね。
列だって数値で指定できます、A1参照形式だとアルファベットで指定しなければなりませんからね。

ところで、セルにはA1やR1C1といった本来の名前とは別に、任意の名前を付けることができます。
ウインドウの左上あたりにある名前ボックスに任意の名前を入力すれば、その時選択していたセル範囲の別名として名付けることができます。
任意とはいえ、一応命名規則があります。
・記号は基本的に使えない("\"や"?"、"_"などの一部例外はある)
・数字から始まる名前は不可("?"から始まるものも不可)
・すでにある名前は使えない(大文字と小文字は区別しない)
※本腰入れて調べてませんので他にも制約があるかもしれません、あくまで基本的なことだけ

↓テストという名前↓
セルの別名

R1C1:R2C2のセル範囲に"テスト"という別名を付けました。
この名前を使っても、INDIRECT関数を使用できます。

↓"テスト"の合計を計算↓
INDIRECTに別名

この別名は参照形式に左右されませんので、第2引数は省略してます。
よく使うセルにわかりやすい名前を付けておけば、数式の管理が楽になりますね。
ただし、もしミリ秒の応答速度を競うような場合は別名を使わないことをおすすめします。
内部計算で、別名から元のアドレスを取得する際のオーバーヘッドがほんのわずかなロスとなってしまうからです。
まあ、よっぽどシビアなプログラムでもない限り気にしないで大丈夫ですけども。

以上が基本的なINDIRECT関数の使い方です。
これらの方法を応用すれば、ユーザーに必要な行番号や列番号、セルの別名を入力してもらうだけで、数式を直接書き換えずに計算結果を取り出すことができます。

しかし、INDIRECT関数の真骨頂はここからです。
あっと驚く使い方があるのです…なんてハードルあげてみる。

 | HOME | 

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。