○第161回目 こんな処理はどうしたらいいの？移植の変化技、ずらし移植
ずらし移植って、何？
ブロック単位の複数個移植で、移植先が列･行ともずれていくものです。
特にイメージしやすいのは、移植先が1行ずつ上がって(または、下がって)いく例でしょうか。
当該列の5行から20行までの和
当該列の6行から20行までの和
当該列の7行から20行までの和
当該列の8行から20行までの和
当該列の9行から20行までの和
これが5列ありました。
これを違う場所に、
最初の列には、5行空白行をつけて移します。
次の列には
空白行が4行
当該列の5行から20行までの和
当該列の6行から20行までの和
当該列の7行から20行までの和
当該列の8行から20行までの和
次の列には
空白行が3行
当該列の5行から20行までの和
当該列の6行から20行までの和
当該列の7行から20行までの和
当該列の8行から20行までの和
当該列の9行から20行までの和
……
となるものです。
この処理の結果では、同一行に、例えば、
当該列の5行から20行までの和
次の列の6行から20行までの和
その次の列の7行から20行までの和
その次の列の8行から20行までの和
その次の列の9行から20行までの和
というふうに並びます。
今までも同じのような問題がありましたが、今回は、移植元、移植先の列の飛びも可能となっている点が新しくなります。
このような操作が何回もあるとたまらないので作ってみたということです。基本はコピー版と同じですね。
実行の手順です。
まず移植のひとつのブロックを指定してから実行です。
次の移植元へのとび数を指定します。2列ごとに移植元がある場合は、2と入力します。
移植の回数を入力します
移植先のブック、シートを指定します
移植先の一番目のセルを指定します
次に移植する先のセルを指定します。このとき3列先で1行下のセルを指定します。
この関係で、3番目以降の移植の場所を計算で求めて自動的に処理を行います。
□同じ場所でできますか。
＝⇒できます。ただしずれていくので、もとの計算式が消えない箇所が出てきます。それを消すのは階段状に消すことになるので、手間になります。
違うところに移植して、それを再度通常移植するという処理がいいと思います。
2010年11月28日日曜日
2010年11月13日土曜日
第160回目 QXマクロ、30行上下の漢字またはカタカナ文字列を抽出
○第160回目 QXマクロ、30行上下の漢字またはカタカナ文字列を抽出
文章をうっていくと、同じ用語が頻繁に出てくることがあります。文字数が少なければそうでもないのですが、多くのキータッチを必要とするものについては、面倒くさくなってしまいます。これを何とかしようという発想です。
通常の操作では、重複している部分を除いて入力して、後でコピーしてくる、というのではないでしょうか。
今回考えるマクロでは、すでに入力した文字列を取得し、一覧表の形で表示しその中から選択して、文章に入れてしまおうというものです。
これまで入れた文章を見てみると、"重複"という漢字が、入力しにくそうですね。これをもう一度入れなければならないという時に、威力(？)を発揮します。
後で起き替えたい場所には数字をうっておきます。そしてマクロを動かします。すると、次のようになります。
｢この1では、｣
↓
｢この重複では、｣
さて、取得する文字列としては、漢字系、カタカナ系、数字系が考えられます。特に、人名などのカタカナは再入力が面倒くさいものではないでしょうか。
数字系については、イメージがつきずらいと思いますが、こういうことです。
QXマクロの中には、この式を計算するというマクロがあります。
計算式を入れていき、計算をさせます。その結果を後の行で使いたいというときに、使用します。
20*5=100
30*6=180
この合計はというときに、100と180を持ってくることができるのです、通常は桁数が大きく、数字がランダムなのとても便利です。
また、1カタカナ系では、人名のように、途中に･があったり=があったりしますので、その部分を含めて取得します。
"ヴィンセント･ヴァン･ゴッホ"の場合は、まとめて、"ヴィンセント･ヴァン･ゴッホ"を取得とするという意味です。カタカナ系には、英文字も含めていますので、プログラムの中での変数名のつなぎとして使っている記号(_)も含めて取得する必要があります。tate_hensaなどが例です。
ニーズにもよりますが、カッコで囲まれた部分という取得の仕方もあります。
さらに別機能として、選択した文字列をファイルに保持しておくというのもあります。
したがって、いままでに選択したものから選ぶか、文書中の文字列から選ぶかの選択ができるということです。これは便利な機能です。
基本的に考えてみると、このマクロの目的は、いかに早く文字を打てるか、ということになると思います。そう考えると、一番の方法は、キータッチの能力を上げることということになります。
試してみると、四文字程度の文字列の場合はメリットがありますが、2文字程度のものは直接打ってしまった方が速いと思います。
マクロの起動、置き換えたい場所への移動、文字列一覧からの選択という操作が必要となりますので、意外と時間がかかり、何でもかんでもこの方法でやるというのは、メリットはそれほど大きくなく、かえって時間がかかるということではないかと思います。
選択した文字列をファイル化するというのは、ハイパーコピデルでやったことなのでこの機能は省略します。
今回のマクロのポイントは、
1.文字列の初めと終わりを何で判断するのか
2.一度にどのくらいの文字列数を取得するのか
3.文字列の取得の方向(上に向かって取得するのか、下に向かって取得するのか)
4.取得する文字列の字数の条件(何文字以上とか)
5.同一文字列の取得を避ける
6.選択文字列(別に保管される)の重複を避ける
1.文字列の初めと終わりを何で判断するのか
一行単位でテキスト取得し、一文字ずつ調べていきます。
普通は、初めて漢字が出てきた場合が始まりで、漢字以外のものになった時に終了と考えます。
しかしながら、
輸入・輸出
ヴィンセント･ヴァン･ゴッホ
平成20年度
神聖ローマ帝国
テキスト取得
2009年
ルイ16世
2007
上記の例では、どのような文字列を取得しますか。
"輸入"と"輸出"の二つ
"ヴィンセント･ヴァン･ゴッホ"の一つ
"平成20年度"の一つ
"神聖ローマ帝国"の一つ
"テキスト取得"の一つ
"2009年"の一つ
"ルイ16世"の一つ
"2007"だけでは取得しない
とするのが普通でしょうか。
初めの判断は、初めて漢字がでてきた場合、または、初めてカタカナが出てきた場合、または、初めて数字が出てきた場合となります。
終わりの判断は漢字系とカタカナ系で違います。
特殊記号の取り扱いですが、漢字で始まった場合は、特殊記号(この例では"･")が出てきたら、それは終わりと判断します。カタカナで始まった場合は、まだ継続すると判断します。特殊記号には｢｣、句読点、･、カッコ、+-*/、小数点、等号などがありますので、それぞれでとあり扱いを区分することが必要です。
漢字で始まった場合は、特殊記号が出てきたら、終了。カタカナで始まった場合は、ある特殊記号が出てきても継続とするという感じでしょうか。
2.一度にどのくらいの文字列数を取得するのか
最初は、40個の文字列を取得して、それ以上のものは、再取得機能を付けて、取得するということにしていましたが、長い文章だと、かなり前にあるものは何回も再取得をしなければならなく、面倒になったので、1回限りの800個としました。
また、同様に保管する文字列の個数も800個としました。800個を越えた場合は、一番古いものをなくしていきます。
3.文字列の取得の方向
上方向、下方向を切り替えることができるという機能を付けるというのも考えられますが、上方向だけでいいのではないでしょうか。
4.取得する文字列の字数の条件(何文字以上とか)
二文字以上としました。これはマクロの中をちょっと変えることで、三文字以上でも、四文字以上でも可能になります。
5.同一文字列の取得を避ける
この機能は、当然必要でしょうね。
6.選択文字列(別に保管される)の重複を避ける
この機能は、当然必要でしょうね。
文章をうっていくと、同じ用語が頻繁に出てくることがあります。文字数が少なければそうでもないのですが、多くのキータッチを必要とするものについては、面倒くさくなってしまいます。これを何とかしようという発想です。
通常の操作では、重複している部分を除いて入力して、後でコピーしてくる、というのではないでしょうか。
今回考えるマクロでは、すでに入力した文字列を取得し、一覧表の形で表示しその中から選択して、文章に入れてしまおうというものです。
これまで入れた文章を見てみると、"重複"という漢字が、入力しにくそうですね。これをもう一度入れなければならないという時に、威力(？)を発揮します。
後で起き替えたい場所には数字をうっておきます。そしてマクロを動かします。すると、次のようになります。
｢この1では、｣
↓
｢この重複では、｣
さて、取得する文字列としては、漢字系、カタカナ系、数字系が考えられます。特に、人名などのカタカナは再入力が面倒くさいものではないでしょうか。
数字系については、イメージがつきずらいと思いますが、こういうことです。
QXマクロの中には、この式を計算するというマクロがあります。
計算式を入れていき、計算をさせます。その結果を後の行で使いたいというときに、使用します。
20*5=100
30*6=180
この合計はというときに、100と180を持ってくることができるのです、通常は桁数が大きく、数字がランダムなのとても便利です。
また、1カタカナ系では、人名のように、途中に･があったり=があったりしますので、その部分を含めて取得します。
"ヴィンセント･ヴァン･ゴッホ"の場合は、まとめて、"ヴィンセント･ヴァン･ゴッホ"を取得とするという意味です。カタカナ系には、英文字も含めていますので、プログラムの中での変数名のつなぎとして使っている記号(_)も含めて取得する必要があります。tate_hensaなどが例です。
ニーズにもよりますが、カッコで囲まれた部分という取得の仕方もあります。
さらに別機能として、選択した文字列をファイルに保持しておくというのもあります。
したがって、いままでに選択したものから選ぶか、文書中の文字列から選ぶかの選択ができるということです。これは便利な機能です。
基本的に考えてみると、このマクロの目的は、いかに早く文字を打てるか、ということになると思います。そう考えると、一番の方法は、キータッチの能力を上げることということになります。
試してみると、四文字程度の文字列の場合はメリットがありますが、2文字程度のものは直接打ってしまった方が速いと思います。
マクロの起動、置き換えたい場所への移動、文字列一覧からの選択という操作が必要となりますので、意外と時間がかかり、何でもかんでもこの方法でやるというのは、メリットはそれほど大きくなく、かえって時間がかかるということではないかと思います。
選択した文字列をファイル化するというのは、ハイパーコピデルでやったことなのでこの機能は省略します。
今回のマクロのポイントは、
1.文字列の初めと終わりを何で判断するのか
2.一度にどのくらいの文字列数を取得するのか
3.文字列の取得の方向(上に向かって取得するのか、下に向かって取得するのか)
4.取得する文字列の字数の条件(何文字以上とか)
5.同一文字列の取得を避ける
6.選択文字列(別に保管される)の重複を避ける
1.文字列の初めと終わりを何で判断するのか
一行単位でテキスト取得し、一文字ずつ調べていきます。
普通は、初めて漢字が出てきた場合が始まりで、漢字以外のものになった時に終了と考えます。
しかしながら、
輸入・輸出
ヴィンセント･ヴァン･ゴッホ
平成20年度
神聖ローマ帝国
テキスト取得
2009年
ルイ16世
2007
上記の例では、どのような文字列を取得しますか。
"輸入"と"輸出"の二つ
"ヴィンセント･ヴァン･ゴッホ"の一つ
"平成20年度"の一つ
"神聖ローマ帝国"の一つ
"テキスト取得"の一つ
"2009年"の一つ
"ルイ16世"の一つ
"2007"だけでは取得しない
とするのが普通でしょうか。
初めの判断は、初めて漢字がでてきた場合、または、初めてカタカナが出てきた場合、または、初めて数字が出てきた場合となります。
終わりの判断は漢字系とカタカナ系で違います。
特殊記号の取り扱いですが、漢字で始まった場合は、特殊記号(この例では"･")が出てきたら、それは終わりと判断します。カタカナで始まった場合は、まだ継続すると判断します。特殊記号には｢｣、句読点、･、カッコ、+-*/、小数点、等号などがありますので、それぞれでとあり扱いを区分することが必要です。
漢字で始まった場合は、特殊記号が出てきたら、終了。カタカナで始まった場合は、ある特殊記号が出てきても継続とするという感じでしょうか。
2.一度にどのくらいの文字列数を取得するのか
最初は、40個の文字列を取得して、それ以上のものは、再取得機能を付けて、取得するということにしていましたが、長い文章だと、かなり前にあるものは何回も再取得をしなければならなく、面倒になったので、1回限りの800個としました。
また、同様に保管する文字列の個数も800個としました。800個を越えた場合は、一番古いものをなくしていきます。
3.文字列の取得の方向
上方向、下方向を切り替えることができるという機能を付けるというのも考えられますが、上方向だけでいいのではないでしょうか。
4.取得する文字列の字数の条件(何文字以上とか)
二文字以上としました。これはマクロの中をちょっと変えることで、三文字以上でも、四文字以上でも可能になります。
5.同一文字列の取得を避ける
この機能は、当然必要でしょうね。
6.選択文字列(別に保管される)の重複を避ける
この機能は、当然必要でしょうね。
2010年11月6日土曜日
第159回目 範囲に名前を付ける。その名前を使った処理のメリット及び課題
○第159回目 範囲に名前を付ける。その名前を使った処理のメリット及び課題
第157回目で紹介した機能
1.指定した範囲を、指定したセルに名前として書き込む
2.名前のあるセルを指定して、その範囲を選択する。
3.名前がつけられた範囲を全部表示させて、その中から必要な場所に飛ぶ。
メリット
範囲印刷が簡単にできます。列行を挿入等しても、範囲は自動的に変わってくれます。
指定範囲が簡単に選択できます。その後のコピーなどの処理につながります。
名前の付いた場所を、範囲選択することができるので、場所の移動ができます。
課題
・選択した範囲を修正できないか。
印刷範囲を修正したいという場合があります。また、印刷以外の処理では、処理範囲が異なる場合が考えられます。
例えば、印刷ではタイトルは必要だが、罫線を設定する場合は、タイトル部分は不要で表部分のみでよいということがあります。この場合、二つの範囲に別々の名をつけるという対応方法も考えられますが…＝⇒数が多くなるのは覚えるのが大変です。
タイトル部分だけではなく、終わりの部分(例えば、欄外の注など)も除外することもあります。
そこで、終わりの部分はShiftを押しながらカーソルで変えられようにします。
範囲の修正
0.名前がはいっているセルにカーソルをおいて実行
1.該当範囲を選択し、選択座標情報を取得する。
2.範囲の修正1 1-0とか、除外列･行数を指定する。その範囲を指定して終了。
3.範囲の修正2 カーソルを動かしてその範囲を修正し、終了。
2.です。
選択範囲座標の情報から、入力した除外列行数の値を元に、修正します。列は最終的には列番号で考えます。
除外列行数を分解しそれぞれの数値を取得します。
それらによって、新しい範囲の列番号-行番号が求められます。列番号から列記号に変換して新しい範囲を指定します。
3.です。
選択範囲の座標を初期値として、座標を入力できるapplication.inputboxを出します。
カーソルを動かして新しい範囲を指定します。
Shiftを押しながら指定すれば、末尾が変わります。
Shiftを押さなければ、最初の場所からの指定になります。この場合は、指定したい先頭位置を指定した後に、Shiftを押しながら末尾を指定することになります。
指定が終わったら、Enterキーを押して新しい範囲を指定します。
プラスとして、先頭のセルを指定して、列行数を指定することによって、指定範囲を決める、さらにカーソルでの調整ができる、というマクロも考えられます。
これは、上の、2.範囲の修正1、3.範囲の修正2の利用で簡単にできます。
第157回目で紹介した機能
1.指定した範囲を、指定したセルに名前として書き込む
2.名前のあるセルを指定して、その範囲を選択する。
3.名前がつけられた範囲を全部表示させて、その中から必要な場所に飛ぶ。
メリット
範囲印刷が簡単にできます。列行を挿入等しても、範囲は自動的に変わってくれます。
指定範囲が簡単に選択できます。その後のコピーなどの処理につながります。
名前の付いた場所を、範囲選択することができるので、場所の移動ができます。
課題
・選択した範囲を修正できないか。
印刷範囲を修正したいという場合があります。また、印刷以外の処理では、処理範囲が異なる場合が考えられます。
例えば、印刷ではタイトルは必要だが、罫線を設定する場合は、タイトル部分は不要で表部分のみでよいということがあります。この場合、二つの範囲に別々の名をつけるという対応方法も考えられますが…＝⇒数が多くなるのは覚えるのが大変です。
タイトル部分だけではなく、終わりの部分(例えば、欄外の注など)も除外することもあります。
そこで、終わりの部分はShiftを押しながらカーソルで変えられようにします。
範囲の修正
0.名前がはいっているセルにカーソルをおいて実行
1.該当範囲を選択し、選択座標情報を取得する。
2.範囲の修正1 1-0とか、除外列･行数を指定する。その範囲を指定して終了。
3.範囲の修正2 カーソルを動かしてその範囲を修正し、終了。
2.です。
選択範囲座標の情報から、入力した除外列行数の値を元に、修正します。列は最終的には列番号で考えます。
除外列行数を分解しそれぞれの数値を取得します。
それらによって、新しい範囲の列番号-行番号が求められます。列番号から列記号に変換して新しい範囲を指定します。
3.です。
選択範囲の座標を初期値として、座標を入力できるapplication.inputboxを出します。
カーソルを動かして新しい範囲を指定します。
Shiftを押しながら指定すれば、末尾が変わります。
Shiftを押さなければ、最初の場所からの指定になります。この場合は、指定したい先頭位置を指定した後に、Shiftを押しながら末尾を指定することになります。
指定が終わったら、Enterキーを押して新しい範囲を指定します。
プラスとして、先頭のセルを指定して、列行数を指定することによって、指定範囲を決める、さらにカーソルでの調整ができる、というマクロも考えられます。
これは、上の、2.範囲の修正1、3.範囲の修正2の利用で簡単にできます。
2010年11月3日水曜日
第158回目 壁カレのデータがダブってしまった
○第158回目 壁カレのデータがダブってしまった
□壁カレでなんですか？
これから入っていきましょう。
壁カレのヘルプから
壁カレ3 使用説明
このソフトウエアは壁掛けカレンダーをイメージしたフリーのカレンダースケジューラです。
最大１２ヶ月のカレンダーを表示し、様々な情報を、カレンダー内に表示します。
単なるカレンダーとしても、LANで予定を参照することも出来ます。
つまり、予定、記録などを書き込めるカレンダーソフトです。
□何が問題だったのですか？
過去のデータ(6年前)を見たくなったので、現在の壁カレにデータを読み込みました。その分だけであれば問題はなかったのですが、元に戻すのがめんどくさかったので、現在のものに付加するという操作をしました。
すると、当然のことながら、ある期間のデータがダブってしまいました。
その当時の月間レポートを打ち出すと、同じ予定、記録がダブっていたということです。
(このときの目的は、6ヶ月間ほどの月間レポートをつくり、その中からある業務に関連するものだけを抜き出そうとすることでした。)
壁カレの元データから削除する必要があるので、元データを出力してそれを削除する必要が出てきました。
□どのようにして削除したのですか？
1件1件手作業で削除していったのですが、さすがにばかばかしくなってしまいました。
□これは、月間レポートを出した後のことになると思いますが、関係ない業務のものも手作業で削除したのですか？
これある程度、自動化しました。置換え機能で、完全に関係ない文字列を指定して、その文字列を含んでいる行を削除したのでした。これはワイルドカードでやれば簡単ですね。(*○○××*をヌルに置き換えるようにすれば、その行は改行のみになってしまいますね)
□本題のものはどうしたのですか？
QXマクロで専用のマクロを作りました。今までのものから使えそうなマクロをベースにして、30分ほどでできました。
処理の流れのです。
0.処理したい先頭にカーソルを置いて実行
1.1行を取得し、頭の28文字分の文字列をもとに、以下の文の中で同一文字列があるかどうかを検索する。
2.同一文字列があった行については、行そのものを削除する。文末まで繰り返す。
3.先頭に戻り、先頭の次の行から1－2を繰り返す。
4.文末まで処理を繰り返す。
□マクロ作成はスムーズに行ったのですか？
スムーズにいったのですが、当然ながらバグがありました。
途中に、カラ行に近い行(先頭に○だけがある行など)があって、そこで取得した文字列をもつ行を片っ端から削除してしまいました。(記録は、先頭に○を付して書いていくという形式になっているので、以下の記録がほとんど全部削除されてしまいました)
これの解明には、さらに20分ほどかかりました。
□どういう対応したのですか？
取得した文字列が5文字以下のものは、削除の元となる文字列の対象外としました。
昔のデータを追加した壁カレのデータは、7000行以上もあったので、途中でこんなものもあったのでしょうね。頭が働きませんでした。
□マクロの正味の行数としてはどのくらいになったのですか？
コメント行を含めて40行あたりです。
□今回のマクロのニーズ度はどうかは知りませんが、こんなに簡単にできるといいですね。
そのとおりだと思います。今回は、マクロに切り替えるまでに時間があったので、メリットとしてはどうかとは思いますが、まずマクロのほうが早いのでは、と思うことは必要ですな。
□壁カレでなんですか？
これから入っていきましょう。
壁カレのヘルプから
壁カレ3 使用説明
このソフトウエアは壁掛けカレンダーをイメージしたフリーのカレンダースケジューラです。
最大１２ヶ月のカレンダーを表示し、様々な情報を、カレンダー内に表示します。
単なるカレンダーとしても、LANで予定を参照することも出来ます。
つまり、予定、記録などを書き込めるカレンダーソフトです。
□何が問題だったのですか？
過去のデータ(6年前)を見たくなったので、現在の壁カレにデータを読み込みました。その分だけであれば問題はなかったのですが、元に戻すのがめんどくさかったので、現在のものに付加するという操作をしました。
すると、当然のことながら、ある期間のデータがダブってしまいました。
その当時の月間レポートを打ち出すと、同じ予定、記録がダブっていたということです。
(このときの目的は、6ヶ月間ほどの月間レポートをつくり、その中からある業務に関連するものだけを抜き出そうとすることでした。)
壁カレの元データから削除する必要があるので、元データを出力してそれを削除する必要が出てきました。
□どのようにして削除したのですか？
1件1件手作業で削除していったのですが、さすがにばかばかしくなってしまいました。
□これは、月間レポートを出した後のことになると思いますが、関係ない業務のものも手作業で削除したのですか？
これある程度、自動化しました。置換え機能で、完全に関係ない文字列を指定して、その文字列を含んでいる行を削除したのでした。これはワイルドカードでやれば簡単ですね。(*○○××*をヌルに置き換えるようにすれば、その行は改行のみになってしまいますね)
□本題のものはどうしたのですか？
QXマクロで専用のマクロを作りました。今までのものから使えそうなマクロをベースにして、30分ほどでできました。
処理の流れのです。
0.処理したい先頭にカーソルを置いて実行
1.1行を取得し、頭の28文字分の文字列をもとに、以下の文の中で同一文字列があるかどうかを検索する。
2.同一文字列があった行については、行そのものを削除する。文末まで繰り返す。
3.先頭に戻り、先頭の次の行から1－2を繰り返す。
4.文末まで処理を繰り返す。
□マクロ作成はスムーズに行ったのですか？
スムーズにいったのですが、当然ながらバグがありました。
途中に、カラ行に近い行(先頭に○だけがある行など)があって、そこで取得した文字列をもつ行を片っ端から削除してしまいました。(記録は、先頭に○を付して書いていくという形式になっているので、以下の記録がほとんど全部削除されてしまいました)
これの解明には、さらに20分ほどかかりました。
□どういう対応したのですか？
取得した文字列が5文字以下のものは、削除の元となる文字列の対象外としました。
昔のデータを追加した壁カレのデータは、7000行以上もあったので、途中でこんなものもあったのでしょうね。頭が働きませんでした。
□マクロの正味の行数としてはどのくらいになったのですか？
コメント行を含めて40行あたりです。
□今回のマクロのニーズ度はどうかは知りませんが、こんなに簡単にできるといいですね。
そのとおりだと思います。今回は、マクロに切り替えるまでに時間があったので、メリットとしてはどうかとは思いますが、まずマクロのほうが早いのでは、と思うことは必要ですな。
2010年9月18日土曜日
第157回目 範囲に名前を付ける。その名前を使った処理
○第157回目 範囲に名前を付ける。その名前を使った処理
印刷関係のマクロはよく使います。なかでも一、二を争うのが、範囲印刷とページ指定印刷です。
このうちの範囲印刷での話し
エクセルでは行列をいろいろと挿入して、表のかたちを修正していきます。最初に最終形ができていなくても、この機能があるので、後で修正がききます。このためやりながら考えていくということができます。
これはいいのですが、範囲印刷で座標で場所のなかで列･行の挿入･削除などがおきると、印刷範囲が狂ってしまい、印刷範囲の座標をもう一度求めなおさなければならなくなります。
あれこれ考えていたのですが、あるとき、ひらめいて、名前を付けていると、その後の列行の挿入･削除でどうなるのかな…。
やってみたところ、名前を付けられた範囲が、列行の挿入削除に伴い変わるのです。
これです。
操作のイメージです。
0.範囲を指定して、そこに名前をつけ、その名前を、その範囲のそばのセルに入れておきます。
・名前があるセルを指定して、範囲印刷をします。
範囲印刷のマクロに若干修正が必要ですが、この機能は、便利です。
別にふたつの機能も考えてみました(これまでの機能を1として加えた)。
1.指定した範囲を、指定したセルに名前として書き込む
2.名前のあるセルを指定して、その範囲を選択する。
3.名前を全部表示させて、その中から選択した場所に飛ぶ。
まずは、1.から
名前をいちいち考えるのは面倒なので、自動的に決めます。PR_先頭座標としました。これなら、ほとんど重複しないでしょう。
どう書くのかというと、これは知っているかいないかだけのことです。
まず選択された範囲の座標を取得します。
Rng = Selection.Address(ReferenceStyle:=xlR1C1)
名前を設定します。
ActiveWorkbook.Names.Add Name:="PR_" & AAA1, RefersToR1C1:="='" & _
shname0 & "'!" & Rng
AAA1には範囲の先頭セルの座標が入っています
shname0 には当該シート名が入っています。
座標は、R1C1形式でなければいけないようでした。
2.名前のあるセルを指定して、その範囲を選択する。
これは簡単です。
bbb = Selection.Value
Range(bbb).Select
3.名前を全部表示させて、その中から選択した場所に飛ぶ。
まずは範囲を取得しなければなりません。どう書くのかというと、これも知っているかいないかだけのことです。
i = 1
For Each MyName In ActiveWorkbook.Names
cell_name1(i) = MyName.Name '定義された名前の名称
cell_name2(i) = MyName 'セル範囲の座標であり、頭に = がついている。シート名＋座標
cell_name2(i) = Mid$(cell_name2(i), 2)
cell_name0(i) = cell_name1(i) & "--" & cell_name2(i)
i = i + 1
Next
あとは、インプットボックスにcell_name0(i)を表示し選択するだけです。
選択した場所の名前は、cell_name1(i) となります。
指定した番号の名前に飛ぶのは、2.でやったとおりです。
おまけです。
指定した名前の削除をします。
番号指定は、＊をつけることにして飛ぶ場合と区別します。
Range(cell_name1(name_no)).Select
ActiveWorkbook.Names(cell_name1(name_no)).Delete
※入力値から、＊をとることを忘れないように。
印刷関係のマクロはよく使います。なかでも一、二を争うのが、範囲印刷とページ指定印刷です。
このうちの範囲印刷での話し
エクセルでは行列をいろいろと挿入して、表のかたちを修正していきます。最初に最終形ができていなくても、この機能があるので、後で修正がききます。このためやりながら考えていくということができます。
これはいいのですが、範囲印刷で座標で場所のなかで列･行の挿入･削除などがおきると、印刷範囲が狂ってしまい、印刷範囲の座標をもう一度求めなおさなければならなくなります。
あれこれ考えていたのですが、あるとき、ひらめいて、名前を付けていると、その後の列行の挿入･削除でどうなるのかな…。
やってみたところ、名前を付けられた範囲が、列行の挿入削除に伴い変わるのです。
これです。
操作のイメージです。
0.範囲を指定して、そこに名前をつけ、その名前を、その範囲のそばのセルに入れておきます。
・名前があるセルを指定して、範囲印刷をします。
範囲印刷のマクロに若干修正が必要ですが、この機能は、便利です。
別にふたつの機能も考えてみました(これまでの機能を1として加えた)。
1.指定した範囲を、指定したセルに名前として書き込む
2.名前のあるセルを指定して、その範囲を選択する。
3.名前を全部表示させて、その中から選択した場所に飛ぶ。
まずは、1.から
名前をいちいち考えるのは面倒なので、自動的に決めます。PR_先頭座標としました。これなら、ほとんど重複しないでしょう。
どう書くのかというと、これは知っているかいないかだけのことです。
まず選択された範囲の座標を取得します。
Rng = Selection.Address(ReferenceStyle:=xlR1C1)
名前を設定します。
ActiveWorkbook.Names.Add Name:="PR_" & AAA1, RefersToR1C1:="='" & _
shname0 & "'!" & Rng
AAA1には範囲の先頭セルの座標が入っています
shname0 には当該シート名が入っています。
座標は、R1C1形式でなければいけないようでした。
2.名前のあるセルを指定して、その範囲を選択する。
これは簡単です。
bbb = Selection.Value
Range(bbb).Select
3.名前を全部表示させて、その中から選択した場所に飛ぶ。
まずは範囲を取得しなければなりません。どう書くのかというと、これも知っているかいないかだけのことです。
i = 1
For Each MyName In ActiveWorkbook.Names
cell_name1(i) = MyName.Name '定義された名前の名称
cell_name2(i) = MyName 'セル範囲の座標であり、頭に = がついている。シート名＋座標
cell_name2(i) = Mid$(cell_name2(i), 2)
cell_name0(i) = cell_name1(i) & "--" & cell_name2(i)
i = i + 1
Next
あとは、インプットボックスにcell_name0(i)を表示し選択するだけです。
選択した場所の名前は、cell_name1(i) となります。
指定した番号の名前に飛ぶのは、2.でやったとおりです。
おまけです。
指定した名前の削除をします。
番号指定は、＊をつけることにして飛ぶ場合と区別します。
Range(cell_name1(name_no)).Select
ActiveWorkbook.Names(cell_name1(name_no)).Delete
※入力値から、＊をとることを忘れないように。
2010年8月29日日曜日
第156回目 ある条件をもつ計算式の一斉削除
○第156回目 ある条件をもつ計算式の一斉削除
今回のテーマは、いったいどんなことでしょうか。
こういうことです。
ブックがたくさんあります。一つのブックで作ったシートを別のシートに移します。
すると不都合なことが起こります。
それは、他のブックを参照する計算式が、そのまま移ってしまうことです。つまり、元のブックのシートを参照してしまうのです。同様なことは、フォルダをコピーするときにも生じます。
このような時の対応として考えられるのは、置換え機能でブックの部分をヌルに置き換えてしまうことです。すると、ブック内のシートを参照することになります。
少し似たニーズとしてあるのは、別のシートから参照している計算式を全部削除したいという時です。
Q．そんな時ってあるんですか？
＝⇒あります。複雑な仕組みである基礎数値を求めました。その基礎数値を使って簡単な計算で表を作ります。この表の部分だけを誰かに渡したい時があります。
データだけなら、一シートに表を値コピーし、そのまま渡せばいいのです。
が、簡単ながら計算式が入っています。この部分だけは生かしたい。つまり、このシートの外部を参照している計算式だけをなくしたい、というニーズです。
Q．通常処理の置換えではだめなんですか？
＝⇒だんだん横着になって、めんどくさくなってしまうのです。置換え文字を作るのも結構めんどくさいのです。
通常置換えを実際に試してみるとどういうことか分かると思います。
マクロの機能としては
1.計算式から外部ブックの部分を削除する。
2.外部ブック及び外部シートから参照している計算式を削除する。
となります。
下の計算式(例)から考えて見ましょう。
xxx='D:\111\222\333\[AB7500.xls]010'!$C$6
xxx=乱数!B1
外部のブックは、]で分かりますね。
シートは、!でわかりますね。
でも、
'はどうしたらいいでしょうか。
＝⇒無条件に、''で囲んでしまうのがいいかもしれません。
複数の項目があった場合、削除する最初をどう判断しましょうか。
＝⇒: の位置と、[ と ] の位置関係で何とかなりそうですね。
まずは、簡単なほうから
2.外部ブック及び外部シートから参照している計算式を削除する。
これの場合、必ずシート名が出てきますので、シート名は、!があるかないかで分かりますので、計算式を取得して、!があるかないかを探します
流れです。
0.処理したい範囲を指定してから実行
1.指定範囲のセルごとに計算式があるかないか調べる
2.あった場合は、計算式を取得し、！が含まれているかを調べる
siki = cells(yy,xx).Formula
含まれているかいないかは、instr(siki,"!") で判断
3.！が含まれている場合は、セルの値を当該セルに入れる。
この処理は、cells(yy,xx).value = cells(yy,xx).value でよろしい。
1.計算式から外部ブックの部分を削除する。
これは少しめんどくさい
流れです。
0.処理したい範囲を指定してから実行
1.指定範囲のセルごとに計算式があるかないか調べる
2.あった場合は、計算式を取得し、'を取り除く。
3.2.の処理後のものに対して、削除すべき場所を求め削除する。
計算式に、:があるか p1
:以降ではじめて出てくる!を探す p3
その部分の中で]の場所を探す p2
削除すべき箇所は、p1-1からp2までとなる
4.シート名を''で囲う。
シート名は、p2+1からp3-1となるので、その部分を抜き出し、''で囲う。
5.3-4を繰り返す
6.全部のセルに対して処理を繰り返す
このくらいの構想で大丈夫でしょう。
注意点
1.については、計算式の適格性のチェックがありますので、範囲を広く設定すると、加工した計算式が適切でない場合(マクロが100%正しければいいのですが…)は、そのつど聞いてきます。最初は狭い範囲で試してから全体に広げてください。
1.の処理は、結構時間がかかりますので、単純なもので広範囲な場合は、通常の置換えでやったほうがいいかもしれません。
参照元ブックを開いていると、:の取得ができませんで、当該ブックを閉じてから行いましょう(計算式に振るパス名がつく)。
今回のテーマは、いったいどんなことでしょうか。
こういうことです。
ブックがたくさんあります。一つのブックで作ったシートを別のシートに移します。
すると不都合なことが起こります。
それは、他のブックを参照する計算式が、そのまま移ってしまうことです。つまり、元のブックのシートを参照してしまうのです。同様なことは、フォルダをコピーするときにも生じます。
このような時の対応として考えられるのは、置換え機能でブックの部分をヌルに置き換えてしまうことです。すると、ブック内のシートを参照することになります。
少し似たニーズとしてあるのは、別のシートから参照している計算式を全部削除したいという時です。
Q．そんな時ってあるんですか？
＝⇒あります。複雑な仕組みである基礎数値を求めました。その基礎数値を使って簡単な計算で表を作ります。この表の部分だけを誰かに渡したい時があります。
データだけなら、一シートに表を値コピーし、そのまま渡せばいいのです。
が、簡単ながら計算式が入っています。この部分だけは生かしたい。つまり、このシートの外部を参照している計算式だけをなくしたい、というニーズです。
Q．通常処理の置換えではだめなんですか？
＝⇒だんだん横着になって、めんどくさくなってしまうのです。置換え文字を作るのも結構めんどくさいのです。
通常置換えを実際に試してみるとどういうことか分かると思います。
マクロの機能としては
1.計算式から外部ブックの部分を削除する。
2.外部ブック及び外部シートから参照している計算式を削除する。
となります。
下の計算式(例)から考えて見ましょう。
xxx='D:\111\222\333\[AB7500.xls]010'!$C$6
xxx=乱数!B1
外部のブックは、]で分かりますね。
シートは、!でわかりますね。
でも、
'はどうしたらいいでしょうか。
＝⇒無条件に、''で囲んでしまうのがいいかもしれません。
複数の項目があった場合、削除する最初をどう判断しましょうか。
＝⇒: の位置と、[ と ] の位置関係で何とかなりそうですね。
まずは、簡単なほうから
2.外部ブック及び外部シートから参照している計算式を削除する。
これの場合、必ずシート名が出てきますので、シート名は、!があるかないかで分かりますので、計算式を取得して、!があるかないかを探します
流れです。
0.処理したい範囲を指定してから実行
1.指定範囲のセルごとに計算式があるかないか調べる
2.あった場合は、計算式を取得し、！が含まれているかを調べる
siki = cells(yy,xx).Formula
含まれているかいないかは、instr(siki,"!") で判断
3.！が含まれている場合は、セルの値を当該セルに入れる。
この処理は、cells(yy,xx).value = cells(yy,xx).value でよろしい。
1.計算式から外部ブックの部分を削除する。
これは少しめんどくさい
流れです。
0.処理したい範囲を指定してから実行
1.指定範囲のセルごとに計算式があるかないか調べる
2.あった場合は、計算式を取得し、'を取り除く。
3.2.の処理後のものに対して、削除すべき場所を求め削除する。
計算式に、:があるか p1
:以降ではじめて出てくる!を探す p3
その部分の中で]の場所を探す p2
削除すべき箇所は、p1-1からp2までとなる
4.シート名を''で囲う。
シート名は、p2+1からp3-1となるので、その部分を抜き出し、''で囲う。
5.3-4を繰り返す
6.全部のセルに対して処理を繰り返す
このくらいの構想で大丈夫でしょう。
注意点
1.については、計算式の適格性のチェックがありますので、範囲を広く設定すると、加工した計算式が適切でない場合(マクロが100%正しければいいのですが…)は、そのつど聞いてきます。最初は狭い範囲で試してから全体に広げてください。
1.の処理は、結構時間がかかりますので、単純なもので広範囲な場合は、通常の置換えでやったほうがいいかもしれません。
参照元ブックを開いていると、:の取得ができませんで、当該ブックを閉じてから行いましょう(計算式に振るパス名がつく)。
2010年8月22日日曜日
第155回目、縦のものを横に展開する計算式を作る既成関数の発見
○第155回目、縦のものを横に展開する計算式を作る既成関数の発見
縦のものを横に展開するにはどうするのか、人によっていろいろあるので、ある人に聞いてみたところ、すでにそのような関数があるとのこと。エクセルは幅が広いと感心しました。
これをきっかけにして…
通常のエクセル操作ではどうやって、計算式を入れるのか。
まず、計算式を入れたい先頭のセルに、Alt+I+Fから、TRANSPOSE関数の挿入を行います。
範囲には、A2:C2を入れておきます。
この段階では、まだ未完成です。
次に、このセル(計算式を入れたい範囲の先頭のセルセル)から下に向かって3セル縦に指定します。
F2を押します。
Ctrl+Shift+Enterを押します。
すると、A2:C2の内容が縦に展開されます。
計算式は3セルとも、表示上は=TRANSPOSE(A2:C2)となっています。
これは便利だ、便利だ、と思ってやっていましたが、思わぬ問題が発生しました。
それは、コピー元が配列として連結されてしまっているので、中を裂くようなセルの挿入ができないのです。
また、コピーもとにセルを挿入すると、計算式がずれてくれないのです。
エクセルの特徴である好きなときに好きなところにセルを挿入したり削除したりすることが不可能になってしまうのです。
これは悲劇です。TRANSPOSE関数を多用して計算システムを作ってしまうと、あとで修正ができなくなってしまい、最初から作り直しになってしまうのです。
それを知らずに、マクロでこの関数作りを使用と思い立ったのです。
ちなみに、マクロで、この関数を入れるには、次のようにします。
Selection.FormulaArray = "=TRANSPOSE(A2:C2)"
このような関数を作り上げるのは簡単なものですが、さきほどの大問題があって、作ったもののお蔵入りになってしまいました。
でも、よく考えますと、単純な計算式作りであれば、このような問題もないし、ニーズからいってまあまあかなと思い、改めて単純形式の縦横変換式つくりのお手伝いを作ることにしました。
流れです。
0.コピー元を選択してマクロを実行(縦横は自動判断)
1.ブック、シートを指定して、計算式を作る先頭のセルを指定
2.繰り返し回数の入力
3.(複数繰り返しの場合)参照先の横飛び、たて飛び数を入力
4.(複数繰り返しの場合)計算式を入れる場所の横飛び、たて飛び数を入力
このマクロのポイントは、列番号を列記号にするということです。
すでにこの機能はユーザ関数として登録していますので簡単にできます。
(第26回目 計算式関係、参照)
まずは、2-3を省略して、作ってみてください。
また、ブックの指定とかシートの指定とかはあとでやればいいと思います。
横を縦にするを例に取ると
0.段階の座標の取得を行います。
列番号はretu1、行番号はgyo1、列数はretu_cntとなります。
計算式を入れる先(gyo2,retu2が先頭)では、
for i=0 to retu_cnt-1
cells(gyou2+i,retu2).formula= 列記号関数(retu1+i) & gyo1
next
となります。
これは難易度がかなり低いですね。
なぜこんなのが、第155回になったのでしょうか。
(このケースは、"複雑形式の計算式作成のお手伝い"で行っていました。それでほとんど支障がなかったものですので、単純なかたちは考えていなかったというのが理由と思われます。これは今回、TRANSPOSE関数のおかげで日の目を見たということでしょうか)
それはさておき、くれぐれも、TRANSPOSE関数に入らないようにしてください。
縦のものを横に展開するにはどうするのか、人によっていろいろあるので、ある人に聞いてみたところ、すでにそのような関数があるとのこと。エクセルは幅が広いと感心しました。
これをきっかけにして…
通常のエクセル操作ではどうやって、計算式を入れるのか。
まず、計算式を入れたい先頭のセルに、Alt+I+Fから、TRANSPOSE関数の挿入を行います。
範囲には、A2:C2を入れておきます。
この段階では、まだ未完成です。
次に、このセル(計算式を入れたい範囲の先頭のセルセル)から下に向かって3セル縦に指定します。
F2を押します。
Ctrl+Shift+Enterを押します。
すると、A2:C2の内容が縦に展開されます。
計算式は3セルとも、表示上は=TRANSPOSE(A2:C2)となっています。
これは便利だ、便利だ、と思ってやっていましたが、思わぬ問題が発生しました。
それは、コピー元が配列として連結されてしまっているので、中を裂くようなセルの挿入ができないのです。
また、コピーもとにセルを挿入すると、計算式がずれてくれないのです。
エクセルの特徴である好きなときに好きなところにセルを挿入したり削除したりすることが不可能になってしまうのです。
これは悲劇です。TRANSPOSE関数を多用して計算システムを作ってしまうと、あとで修正ができなくなってしまい、最初から作り直しになってしまうのです。
それを知らずに、マクロでこの関数作りを使用と思い立ったのです。
ちなみに、マクロで、この関数を入れるには、次のようにします。
Selection.FormulaArray = "=TRANSPOSE(A2:C2)"
このような関数を作り上げるのは簡単なものですが、さきほどの大問題があって、作ったもののお蔵入りになってしまいました。
でも、よく考えますと、単純な計算式作りであれば、このような問題もないし、ニーズからいってまあまあかなと思い、改めて単純形式の縦横変換式つくりのお手伝いを作ることにしました。
流れです。
0.コピー元を選択してマクロを実行(縦横は自動判断)
1.ブック、シートを指定して、計算式を作る先頭のセルを指定
2.繰り返し回数の入力
3.(複数繰り返しの場合)参照先の横飛び、たて飛び数を入力
4.(複数繰り返しの場合)計算式を入れる場所の横飛び、たて飛び数を入力
このマクロのポイントは、列番号を列記号にするということです。
すでにこの機能はユーザ関数として登録していますので簡単にできます。
(第26回目 計算式関係、参照)
まずは、2-3を省略して、作ってみてください。
また、ブックの指定とかシートの指定とかはあとでやればいいと思います。
横を縦にするを例に取ると
0.段階の座標の取得を行います。
列番号はretu1、行番号はgyo1、列数はretu_cntとなります。
計算式を入れる先(gyo2,retu2が先頭)では、
for i=0 to retu_cnt-1
cells(gyou2+i,retu2).formula= 列記号関数(retu1+i) & gyo1
next
となります。
これは難易度がかなり低いですね。
なぜこんなのが、第155回になったのでしょうか。
(このケースは、"複雑形式の計算式作成のお手伝い"で行っていました。それでほとんど支障がなかったものですので、単純なかたちは考えていなかったというのが理由と思われます。これは今回、TRANSPOSE関数のおかげで日の目を見たということでしょうか)
それはさておき、くれぐれも、TRANSPOSE関数に入らないようにしてください。
2010年8月8日日曜日
第154回目、リンク貼り付け、なんて簡単なの
○第154回目、リンク貼り付け、なんて簡単なの
リンク貼付けって、とても大変なイメージを持っていました。大体はブックを超えてのものなので、操作がめんどうくさいという思いがまず出てきてしまうことが原因だと思います。
でも、よく見ると単純な計算式なのでした。
そこで、こんなマクロを。
他のブックからリンク貼付をしたいとします。
通常の操作では、
1.コピー元を指定し、コピー先に移動して、すべて貼付け及び列幅を貼付ける。
2.コピー元に戻り、リンク貼付けをしたい部分を指定し、コピー先に移動して、リンク貼付け
となるのではないでしょうか(1.でリンク貼付けをしてしまうことも考えられますが、関係のないところに計算式が入ってしまい、その場所は0が表示され、気になってしまいます。0の部分を消していくのもかったるい)。
これを一気にやってしまうマクロです。
流れです。
0.コピー元を指定し、マクロを実行します。
1.コピー先のブック、シートに移動して、貼付け位置を指定する。
2.リンク貼付けをしたくない行数(先頭位置からの行数)を入力します。
コピーの処理としては
1.書式
2.列幅
3.値と数値の書式
4.リンク貼付け
でしょうか。
まずは1－3まで行って、4.のリンク貼付けを、指定された範囲から除外行数の部分を除い範囲で行えばいいことになります。
リンク貼付けのコマンドは、次のとおりとなります。
Range("D8:D12").Select
Selection.Copy
(ブックを指定する)
(シートを指定する)
Range("AY25").Select
ActiveSheet.Paste Link:=True
範囲指定はCells指定で行うほうが、拡張性が広がるので、いいと思います。
参考のため、それぞれのコピーコマンドをあげておきます。
1.書式のコピー
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
2.列幅のコピー
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
3.値と数値の書式のコピー
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
SkipBlanks:=False, Transpose:=False
4.リンク貼付け
Selection.Paste Link:=True
行だけではなく、リンク貼付けをしたい範囲として、列も除外したいのですが。
＝⇒それは非常に簡単なので各自で試みてください。
例えば、2-6とか、列-列形式で入力するとスマートですね。
リンク貼付けって、とても大変なイメージを持っていました。大体はブックを超えてのものなので、操作がめんどうくさいという思いがまず出てきてしまうことが原因だと思います。
でも、よく見ると単純な計算式なのでした。
そこで、こんなマクロを。
他のブックからリンク貼付をしたいとします。
通常の操作では、
1.コピー元を指定し、コピー先に移動して、すべて貼付け及び列幅を貼付ける。
2.コピー元に戻り、リンク貼付けをしたい部分を指定し、コピー先に移動して、リンク貼付け
となるのではないでしょうか(1.でリンク貼付けをしてしまうことも考えられますが、関係のないところに計算式が入ってしまい、その場所は0が表示され、気になってしまいます。0の部分を消していくのもかったるい)。
これを一気にやってしまうマクロです。
流れです。
0.コピー元を指定し、マクロを実行します。
1.コピー先のブック、シートに移動して、貼付け位置を指定する。
2.リンク貼付けをしたくない行数(先頭位置からの行数)を入力します。
コピーの処理としては
1.書式
2.列幅
3.値と数値の書式
4.リンク貼付け
でしょうか。
まずは1－3まで行って、4.のリンク貼付けを、指定された範囲から除外行数の部分を除い範囲で行えばいいことになります。
リンク貼付けのコマンドは、次のとおりとなります。
Range("D8:D12").Select
Selection.Copy
(ブックを指定する)
(シートを指定する)
Range("AY25").Select
ActiveSheet.Paste Link:=True
範囲指定はCells指定で行うほうが、拡張性が広がるので、いいと思います。
参考のため、それぞれのコピーコマンドをあげておきます。
1.書式のコピー
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
2.列幅のコピー
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
3.値と数値の書式のコピー
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
SkipBlanks:=False, Transpose:=False
4.リンク貼付け
Selection.Paste Link:=True
行だけではなく、リンク貼付けをしたい範囲として、列も除外したいのですが。
＝⇒それは非常に簡単なので各自で試みてください。
例えば、2-6とか、列-列形式で入力するとスマートですね。
2010年7月25日日曜日
第153回目、SUM関数のニーズが高くなったぞ
○第153回目、SUM関数のニーズが高くなったぞ
第143回目 ユーザー関数(条件付SUMPRO関数、その2)の中で、指定が一つのみであった場合、それはSUM関数とする、として、それを使っていたのですが、なんせ頻度がはるかに多くなったので、独立して、SUM関数専用のものを作ったらどうか、ということです。
範囲を指定してからの処理なので、後は計算式を入れる場所の指定のみとなります。
計算式の作り方は、条件付SUMPRO関数より単純なので省略します。
知っていましたか…
ショートカットを使ってのSUM関数の作りかたです。
計算式を入れたいセルで、Alt+Shift+＝をおすと、たぶんこうだろうということでエクセルが自動的に判断して、SUM関数の範囲を求めてくれます。範囲は仮設定なので修正ができます。
単純な場合は、これも便利ですよ。
ついでに、
カンマを入れたいときは＝⇒ Ctrl+Shift+!となります。
ショートカットは非常に便利です。
第143回目 ユーザー関数(条件付SUMPRO関数、その2)の中で、指定が一つのみであった場合、それはSUM関数とする、として、それを使っていたのですが、なんせ頻度がはるかに多くなったので、独立して、SUM関数専用のものを作ったらどうか、ということです。
範囲を指定してからの処理なので、後は計算式を入れる場所の指定のみとなります。
計算式の作り方は、条件付SUMPRO関数より単純なので省略します。
知っていましたか…
ショートカットを使ってのSUM関数の作りかたです。
計算式を入れたいセルで、Alt+Shift+＝をおすと、たぶんこうだろうということでエクセルが自動的に判断して、SUM関数の範囲を求めてくれます。範囲は仮設定なので修正ができます。
単純な場合は、これも便利ですよ。
ついでに、
カンマを入れたいときは＝⇒ Ctrl+Shift+!となります。
ショートカットは非常に便利です。
2010年7月19日月曜日
第152回目、なじかは しぃらねーど…
○第152回目、なじかは しぃらねーど…
どうしてかはわからないが…という意味でござる。ローレライの歌の文句ではなかったかな。
今回は問題です。
数字が展開されています。ひとつが4列構成で、40単位あります。(最低でも160列)
明細は50行あり、その合計がとられています。合計行の下に、一部分、つまり、MM行からNN行までの小計もとられています。
ここで問題です。最初は、MM行からNN行までの小計でいいでのすが、次の単位では、MM+1行からNN行までの合計と次々に範囲が少なくなって来ます。場合によっては、途中でこのような小計を取ることが無意味になってしまうでしょう。その部分の自動化まではまあよいとして、基本的に、このような小計をどのようにして求めますか、という問題です。
小計を取る部分は4列中の2列のみです。
また、シートにはその表のみがあるのではなく、上下左右に違う表が存在します。
力技ならできることは確かですが、規則性があるだけにしゃくな気がします。
戦略としては(大げさですが…)、シートをコピーして、回りを気にせずに求めたい計算式を作成し、計算式を元のシートにはコピーする、ということにします。
小計をとる2列は、2列目と4列目とします。
小計を求める行の最初は、10行目から50行目とします。
まず、4列ごとの最初の単位で、2列目と4列目に、10-50行の小計を取ります。50行目は絶対参照としておきましょう。
小計をとった1列目から4列目を(1,3列目は空白)、通常のコピー操作で残りの39単位にコピーします。
この全体を、下に40行ぐらいコピーしましょう。
すると、コピーしたものを見てみると、最初の行は20-50の小計ですが、次の行は、21-50の小計となっています。順に22-50、23-50…となっています。
ここでセルを階段状に入れていきましょう。これは階段状のセルの挿入でできます。
処理対象列を4列ごとにするので、2回の操作になるでしょう。挿入数はだんだん減っていくパターンとします。
40単位目が｢1セル、下に挿入｣なので、一単位目は｢40セル、下に挿入｣になります。
すると、1単位目の20―50の小計の横には、次の単位では、21―50の小計が並ぶことになります。その次の単位も同様です。このようにして求める計算式が横に並びました。
明細と小計の間にある不要になった部分をセル削除します。コピー処理をしますので、コピー先に目的となる計算式が移るように、セルを挿入、削除していきます。
このように小計の位置関係に注意しながら、元のシートの小計行にコピーします。
これで求める計算式ができました。
お分かりになったでしょうか。
最後のコピーは、移植でもいいと思います。その場合は、セルを削除する操作が不要となります。
※元のシートの該当する表の下に何もなければ、同一シートで処理ができますので、シートのコピーは不要となります。
どうしてかはわからないが…という意味でござる。ローレライの歌の文句ではなかったかな。
今回は問題です。
数字が展開されています。ひとつが4列構成で、40単位あります。(最低でも160列)
明細は50行あり、その合計がとられています。合計行の下に、一部分、つまり、MM行からNN行までの小計もとられています。
ここで問題です。最初は、MM行からNN行までの小計でいいでのすが、次の単位では、MM+1行からNN行までの合計と次々に範囲が少なくなって来ます。場合によっては、途中でこのような小計を取ることが無意味になってしまうでしょう。その部分の自動化まではまあよいとして、基本的に、このような小計をどのようにして求めますか、という問題です。
小計を取る部分は4列中の2列のみです。
また、シートにはその表のみがあるのではなく、上下左右に違う表が存在します。
力技ならできることは確かですが、規則性があるだけにしゃくな気がします。
戦略としては(大げさですが…)、シートをコピーして、回りを気にせずに求めたい計算式を作成し、計算式を元のシートにはコピーする、ということにします。
小計をとる2列は、2列目と4列目とします。
小計を求める行の最初は、10行目から50行目とします。
まず、4列ごとの最初の単位で、2列目と4列目に、10-50行の小計を取ります。50行目は絶対参照としておきましょう。
小計をとった1列目から4列目を(1,3列目は空白)、通常のコピー操作で残りの39単位にコピーします。
この全体を、下に40行ぐらいコピーしましょう。
すると、コピーしたものを見てみると、最初の行は20-50の小計ですが、次の行は、21-50の小計となっています。順に22-50、23-50…となっています。
ここでセルを階段状に入れていきましょう。これは階段状のセルの挿入でできます。
処理対象列を4列ごとにするので、2回の操作になるでしょう。挿入数はだんだん減っていくパターンとします。
40単位目が｢1セル、下に挿入｣なので、一単位目は｢40セル、下に挿入｣になります。
すると、1単位目の20―50の小計の横には、次の単位では、21―50の小計が並ぶことになります。その次の単位も同様です。このようにして求める計算式が横に並びました。
明細と小計の間にある不要になった部分をセル削除します。コピー処理をしますので、コピー先に目的となる計算式が移るように、セルを挿入、削除していきます。
このように小計の位置関係に注意しながら、元のシートの小計行にコピーします。
これで求める計算式ができました。
お分かりになったでしょうか。
最後のコピーは、移植でもいいと思います。その場合は、セルを削除する操作が不要となります。
※元のシートの該当する表の下に何もなければ、同一シートで処理ができますので、シートのコピーは不要となります。
2010年7月10日土曜日
第151回目、複数個のコピー元をある規則性を持った場所にコピー(移植)する(その2)
○第151回目、複数個のコピー元をある規則性を持った場所にコピー(移植)する(その2)
こんなのにはお目にかからないかと思いますが、お目にかかったときは大変ことになります。いわゆる"経過措置"というようなものです。
支払う額を下げる必要が出てきました。
今日からすぐに下げますというわけにはいかないので、経過措置を設けます。
本来は20%カットします。でも現時点で制度に入っている人はカット幅を10％にします。また、いま入っている人でも、すでに15年以上いる人は、カットはしません。
これを基に考えると、結果年度が経るにしたがって、80%のブロック、90%のブロック、100%のブロックが斜めに移行していくことになります。
Qus.あれー。三つのブロックになっているが、原理的には前のと同じじゃないでしょうか。
＝⇒わかってしまいましたか。鋭いですね。ワッハッハッ…。
三つに分けたのはカムフラージュだったのでした。
これの眼目は、現在ある場所だけで処理を行う、形もできるだけ変えないということです。
修正前はすべてのところに100%という数字が入っています。またよく考えると、ある以降はすべて80%が入ることになります。そこで、100%の終わったところ以降は、あらかじめ80%を入れておくのです。このような状態のところで、必要な箇所に90%を入れていけばよいことになります。
一列目の必要なところに、90%を入れておき、それを斜めに移植していく、ということで目的のものが出来上がります。
さて、この90%の箇所に色を付けるにはどうしたらいいでしょうか。
まずは条件付き書式が考えられますね。
通常の操作ではどうでしょうか？
【参考(条件付書式でのトラブル)】
http://support.microsoft.com/kb/959029/ja
Excel 2003 で Type に 8 (セル参照 (Range オブジェクト)) を指定した InputBox メソッドを実行すると、エラー メッセージ "実行時エラー '424': オブジェクトが必要です。" が表示される
現象
Microsoft Office Excel 2003 の条件付き書式を含む Excel ブックで、Type に 8 (セル参照 (Range オブジェクト)) を指定した InputBox メソッドを実行すると、次のエラー メッセージが表示されます。
実行時エラー
'424': オブジェクトが必要です。
回避策
RefEdit コントロールを使用して同じ機能を実装するように開発します。
状況
マイクロソフトでは、この問題をこの資料の対象製品として記載されているマイクロソフト製品の問題として認識しています。
こんなのにはお目にかからないかと思いますが、お目にかかったときは大変ことになります。いわゆる"経過措置"というようなものです。
支払う額を下げる必要が出てきました。
今日からすぐに下げますというわけにはいかないので、経過措置を設けます。
本来は20%カットします。でも現時点で制度に入っている人はカット幅を10％にします。また、いま入っている人でも、すでに15年以上いる人は、カットはしません。
これを基に考えると、結果年度が経るにしたがって、80%のブロック、90%のブロック、100%のブロックが斜めに移行していくことになります。
Qus.あれー。三つのブロックになっているが、原理的には前のと同じじゃないでしょうか。
＝⇒わかってしまいましたか。鋭いですね。ワッハッハッ…。
三つに分けたのはカムフラージュだったのでした。
これの眼目は、現在ある場所だけで処理を行う、形もできるだけ変えないということです。
修正前はすべてのところに100%という数字が入っています。またよく考えると、ある以降はすべて80%が入ることになります。そこで、100%の終わったところ以降は、あらかじめ80%を入れておくのです。このような状態のところで、必要な箇所に90%を入れていけばよいことになります。
一列目の必要なところに、90%を入れておき、それを斜めに移植していく、ということで目的のものが出来上がります。
さて、この90%の箇所に色を付けるにはどうしたらいいでしょうか。
まずは条件付き書式が考えられますね。
通常の操作ではどうでしょうか？
【参考(条件付書式でのトラブル)】
http://support.microsoft.com/kb/959029/ja
Excel 2003 で Type に 8 (セル参照 (Range オブジェクト)) を指定した InputBox メソッドを実行すると、エラー メッセージ "実行時エラー '424': オブジェクトが必要です。" が表示される
現象
Microsoft Office Excel 2003 の条件付き書式を含む Excel ブックで、Type に 8 (セル参照 (Range オブジェクト)) を指定した InputBox メソッドを実行すると、次のエラー メッセージが表示されます。
実行時エラー
'424': オブジェクトが必要です。
回避策
RefEdit コントロールを使用して同じ機能を実装するように開発します。
状況
マイクロソフトでは、この問題をこの資料の対象製品として記載されているマイクロソフト製品の問題として認識しています。
2010年6月26日土曜日
第150回目、複数個のコピー元をある規則性を持った場所にコピーする
○第150回目、複数個のコピー元をある規則性を持った場所にコピーする
今までのコピーは、あるものをあるところにコピー
あるものをあるブロックにコピー
あるブロックのものをあるところにコピー
あるブロックのものをあるところに複数回コピー
というものでした。上から、1＝⇒1、1＝⇒N、N＝⇒N、N＝⇒N×i、ということを意味しています。
これで十分だと思うのですが、いろいろやっていくと、コピー元がブロックであり、複数個ある場合があります。N×i＝⇒N×iのパターンです。
具体的には、2列ごとにある4つのブロックを3列ごとにコピーするというものです。コピー先をずらしていくという形もあると思います。
【例１】
【例２】
次に、1＊N＝⇒N*iというのもあります。
【例３】
テーマはお分かりになったでしょうか。飛んだ場所を、飛んだところに、コピーしようということなのです。
ここでのコピーとは、計算式の場合は移植となり、数値の場合はコピーということにします。
Qus.計算式の移植、計算式のコピーとはどう違うのですか。
＝⇒計算式の移植とは、計算式がそのまま移ります。ですから計算値は変わりません。計算式のコピーとは、通常のコピー・ペーストで貼付けるもので、計算式内の座標が原則的に変わります。計算値も変わることになります。ですから移植では、計算式をデータとみなして、そのデータを別のセルに計算式として入れるということになります。
Qus.計算式の移植を選択したということですが、コピーじゃ何か都合が悪いことがあるんですか。
＝⇒例1のように、コピー元とコピー先の中での位置関係が違うことを想定しているので、二番目以降の計算式がまったくおかしなものになってしまうからです。
Qus.なるほど思いますが、前提に制約をつければ、コピー系でも有益なものがあるのではないでしょうか。
＝⇒コピー元とコピー先の中での位置関係を同じにして、コピー先の間の情報をまったく変えずに飛び飛びでコピーするというのは機能としてはありうるかもしれません。
しかし、間のセルの情報を気にしなければ、全体を指定して一気にコピーすればいいことになります。あまり先々の展開は望めないのではないかと思い、コピー系ではなく、移植系としました。
＝⇒なるほど。
【例１】と【例２】の場合
0.コピー元の最初のブロックを指定してマクロを実行する。
1.座標情報の取得
2.次のコピー元の先頭のセルを指定する。1と2で、たて・横の偏位がわかり、後のコピー元の位置がわかる
3.コピー回数を入力する
4.最初のコピー先の先頭のセルを指定する
5.次のコピー先の先頭のセルを指定する。4と5で、たて・横の偏位がわかり、後のコピー先の位置がわかる
6.コピー元の計算式を変数にとり、コピー先のセルに入れる(小ループ)
7.6をコピー回数分繰り返す(大ループ)
三種の神器ではないが、1.指定場所情報の取得、2座標情報の指定、3.パラメータの指定でほとんど終了になってしまいます。いわゆる、"おわ"ですね。
コピー元の偏差として、hensa1_xとhensa1_yとし、コピー先の偏差として、hensa2_xとhensa2_yとすればいいでしょう。
6の小ループは、イメージ的に言えば、1の行数です(列数の場合や行数×列数の場合もあります)。
7の大ループは、3のコピー回数です。
計算式の取得(ない場合は値の取得)は
If Cells(,).HasFormula = True Then
mm() = Cells(,).Formula
Else
mm() = Cells(,).Value
End If
計算式の移植は
Cells(,).Formula = mm()
または
Cells(,) = mm()
上のように、いったん配列の変数に入れなくても、計算式の取得と移植を一気にやる方法もありそうですね。
すると、
If Cells(,).HasFormula = True Then
Cells(,).Formula = Cells(,).Formula
Else
Cells(,) = Cells(,).Value
End If
となりますが、添え字のコントロールを間違えないようにしてください。
Qus.【例１】と【例２】の使用例を教えてください。事例が思い浮かびません。
＝⇒確かに、移植という事例はほとんどないですね。
一例です。ある制度では、年齢に応じた額を支払うことにしているとします。簡単にするために、毎年支払う金額は年齢と同額とします。60歳の人には60円を、70歳の人には70円をということになります。
あるとき制度を変更して、今時点で59歳以下の人には、これまでの額の80％、60歳以上の人にはこれまでの額の90%を支払うように制度を改めたとします。
すると、今後を経過年度別に見ると、年齢が一つ上がっていきますので、90%のグループと80%のグループが斜めに移動していくことになります。
さてこのような金額表をどうやってスムーズに作ったらいいでしょうか。
このようなときに威力を発揮します。金額の計算式が複雑であればあるほど威力を発揮します。
Qus.やり方のイメージがわきません。
＝⇒別なところで【例２】をやった後に、その結果を【例１】により、計算式を置きたい場所に移植します。
1.たてに、1～115までの数字を入れます。これは年齢です(場合によっては、-40～115でも結構です)。
2.その隣の2列に、59歳までは0.8と年齢×前の列の値(0.8)、60歳以上は0.9と年齢×前の列の値(0.9)という計算式を入れます。このとき年齢の列は絶対参照にしてください。
3.2の列を横に、20組、列コピーします。
4.例2により、このコピーした20組のうち奇数番の列20列(計算式ではなく、0.8とか0.9という値になっている列)を一つずつ下にずらして移植します。移植先の列は連続でかまいません。すると、四角にはなっていませんが求めたいものができたことになります。
5.4で求めたもので空きが出てくることがありますが、それは通常のコピーで埋めてください。
6.5で求めたもので必要な四角形を選択し、例1により、3.の値の場所に移植します。今度は移植先で列の間隔があいています。
Qus.処理対象は計算式ではないではないですか。
＝⇒ご指摘はごもっともです。ものごとをわかりやすく単純化したためにこのような例をとりました。ご勘弁願います。
本論には関係がないのですが、この処理は、計算式を取得して、その計算式を移植するという単純な処理なのですが、処理時間がかなり長いのが気になります。
※今回のテーマとは直接は関係がないように見えるのですが、コピー機能でこんなのに出会いました。スムーズに行うにはどうしたらいいでしょうか。
たてブロックである計算式があります。それを原型として、コピーしたいのですが、コピー先は6列ずつ離れています(間に5列あるということ。また、コピー先は複数あります)。
計算式中の参照セルは、次の列にして欲しいのです。(単純に次の列にコピーして、その間に5列または5列分のセルを挿入するという方法は、他の部分に影響を与えてしまいます。他の部分に影響を与えないという前提とします)
さらに、2.参照列の偏差がさまざまだったらどうでしょうか。
＝⇒2番目にはまったく手も足も出ないので、今回は考えないことにしますが、一番目はこうしたらどうでしょうかというアイデアがあります。
1.原型の計算式ブロックのよこに、10列挿入します。(列単位に10個コピーするとします)
2.その10列に原型の計算式をコピーします。行番号は変わらずに、列番号だけが原則的に一つずれていきます。
3.この10ブロックを、今回紹介したマクロで移植するのです。移植元は連続列、移植先は6列おきという規則性がありますので、今回のマクロが利用できます。
4.最後に挿入した10列を削除します。
Qus.なるほどそのようなニーズがあったらとても便利でしょうね。それはさておき【例3】はなんか面白そうなので早く紹介してください。
＝⇒長くなりましたので、今回はこれでおしまいにして、次回紹介します。
今までのコピーは、あるものをあるところにコピー
あるものをあるブロックにコピー
あるブロックのものをあるところにコピー
あるブロックのものをあるところに複数回コピー
というものでした。上から、1＝⇒1、1＝⇒N、N＝⇒N、N＝⇒N×i、ということを意味しています。
これで十分だと思うのですが、いろいろやっていくと、コピー元がブロックであり、複数個ある場合があります。N×i＝⇒N×iのパターンです。
具体的には、2列ごとにある4つのブロックを3列ごとにコピーするというものです。コピー先をずらしていくという形もあると思います。
【例１】
【例２】
次に、1＊N＝⇒N*iというのもあります。
【例３】
テーマはお分かりになったでしょうか。飛んだ場所を、飛んだところに、コピーしようということなのです。
ここでのコピーとは、計算式の場合は移植となり、数値の場合はコピーということにします。
Qus.計算式の移植、計算式のコピーとはどう違うのですか。
＝⇒計算式の移植とは、計算式がそのまま移ります。ですから計算値は変わりません。計算式のコピーとは、通常のコピー・ペーストで貼付けるもので、計算式内の座標が原則的に変わります。計算値も変わることになります。ですから移植では、計算式をデータとみなして、そのデータを別のセルに計算式として入れるということになります。
Qus.計算式の移植を選択したということですが、コピーじゃ何か都合が悪いことがあるんですか。
＝⇒例1のように、コピー元とコピー先の中での位置関係が違うことを想定しているので、二番目以降の計算式がまったくおかしなものになってしまうからです。
Qus.なるほど思いますが、前提に制約をつければ、コピー系でも有益なものがあるのではないでしょうか。
＝⇒コピー元とコピー先の中での位置関係を同じにして、コピー先の間の情報をまったく変えずに飛び飛びでコピーするというのは機能としてはありうるかもしれません。
しかし、間のセルの情報を気にしなければ、全体を指定して一気にコピーすればいいことになります。あまり先々の展開は望めないのではないかと思い、コピー系ではなく、移植系としました。
＝⇒なるほど。
【例１】と【例２】の場合
0.コピー元の最初のブロックを指定してマクロを実行する。
1.座標情報の取得
2.次のコピー元の先頭のセルを指定する。1と2で、たて・横の偏位がわかり、後のコピー元の位置がわかる
3.コピー回数を入力する
4.最初のコピー先の先頭のセルを指定する
5.次のコピー先の先頭のセルを指定する。4と5で、たて・横の偏位がわかり、後のコピー先の位置がわかる
6.コピー元の計算式を変数にとり、コピー先のセルに入れる(小ループ)
7.6をコピー回数分繰り返す(大ループ)
三種の神器ではないが、1.指定場所情報の取得、2座標情報の指定、3.パラメータの指定でほとんど終了になってしまいます。いわゆる、"おわ"ですね。
コピー元の偏差として、hensa1_xとhensa1_yとし、コピー先の偏差として、hensa2_xとhensa2_yとすればいいでしょう。
6の小ループは、イメージ的に言えば、1の行数です(列数の場合や行数×列数の場合もあります)。
7の大ループは、3のコピー回数です。
計算式の取得(ない場合は値の取得)は
If Cells(,).HasFormula = True Then
mm() = Cells(,).Formula
Else
mm() = Cells(,).Value
End If
計算式の移植は
Cells(,).Formula = mm()
または
Cells(,) = mm()
上のように、いったん配列の変数に入れなくても、計算式の取得と移植を一気にやる方法もありそうですね。
すると、
If Cells(,).HasFormula = True Then
Cells(,).Formula = Cells(,).Formula
Else
Cells(,) = Cells(,).Value
End If
となりますが、添え字のコントロールを間違えないようにしてください。
Qus.【例１】と【例２】の使用例を教えてください。事例が思い浮かびません。
＝⇒確かに、移植という事例はほとんどないですね。
一例です。ある制度では、年齢に応じた額を支払うことにしているとします。簡単にするために、毎年支払う金額は年齢と同額とします。60歳の人には60円を、70歳の人には70円をということになります。
あるとき制度を変更して、今時点で59歳以下の人には、これまでの額の80％、60歳以上の人にはこれまでの額の90%を支払うように制度を改めたとします。
すると、今後を経過年度別に見ると、年齢が一つ上がっていきますので、90%のグループと80%のグループが斜めに移動していくことになります。
さてこのような金額表をどうやってスムーズに作ったらいいでしょうか。
このようなときに威力を発揮します。金額の計算式が複雑であればあるほど威力を発揮します。
Qus.やり方のイメージがわきません。
＝⇒別なところで【例２】をやった後に、その結果を【例１】により、計算式を置きたい場所に移植します。
1.たてに、1～115までの数字を入れます。これは年齢です(場合によっては、-40～115でも結構です)。
2.その隣の2列に、59歳までは0.8と年齢×前の列の値(0.8)、60歳以上は0.9と年齢×前の列の値(0.9)という計算式を入れます。このとき年齢の列は絶対参照にしてください。
3.2の列を横に、20組、列コピーします。
4.例2により、このコピーした20組のうち奇数番の列20列(計算式ではなく、0.8とか0.9という値になっている列)を一つずつ下にずらして移植します。移植先の列は連続でかまいません。すると、四角にはなっていませんが求めたいものができたことになります。
5.4で求めたもので空きが出てくることがありますが、それは通常のコピーで埋めてください。
6.5で求めたもので必要な四角形を選択し、例1により、3.の値の場所に移植します。今度は移植先で列の間隔があいています。
Qus.処理対象は計算式ではないではないですか。
＝⇒ご指摘はごもっともです。ものごとをわかりやすく単純化したためにこのような例をとりました。ご勘弁願います。
本論には関係がないのですが、この処理は、計算式を取得して、その計算式を移植するという単純な処理なのですが、処理時間がかなり長いのが気になります。
※今回のテーマとは直接は関係がないように見えるのですが、コピー機能でこんなのに出会いました。スムーズに行うにはどうしたらいいでしょうか。
たてブロックである計算式があります。それを原型として、コピーしたいのですが、コピー先は6列ずつ離れています(間に5列あるということ。また、コピー先は複数あります)。
計算式中の参照セルは、次の列にして欲しいのです。(単純に次の列にコピーして、その間に5列または5列分のセルを挿入するという方法は、他の部分に影響を与えてしまいます。他の部分に影響を与えないという前提とします)
さらに、2.参照列の偏差がさまざまだったらどうでしょうか。
＝⇒2番目にはまったく手も足も出ないので、今回は考えないことにしますが、一番目はこうしたらどうでしょうかというアイデアがあります。
1.原型の計算式ブロックのよこに、10列挿入します。(列単位に10個コピーするとします)
2.その10列に原型の計算式をコピーします。行番号は変わらずに、列番号だけが原則的に一つずれていきます。
3.この10ブロックを、今回紹介したマクロで移植するのです。移植元は連続列、移植先は6列おきという規則性がありますので、今回のマクロが利用できます。
4.最後に挿入した10列を削除します。
Qus.なるほどそのようなニーズがあったらとても便利でしょうね。それはさておき【例3】はなんか面白そうなので早く紹介してください。
＝⇒長くなりましたので、今回はこれでおしまいにして、次回紹介します。
2010年6月19日土曜日
第149回目、漢字等の入力を早くするには
○第149回目、漢字等の入力を早くするには
答え＝⇒タイピングを早くすればいいのです。それも間違いが少ないようなレベルになるまで努力しましょう。
といっても、疲れてきたときなど、飲んだ後など、タイピングのレベルは落ちてきますよね。誤入力もなかなかなくなりませんよね。
何とか便利な方法はないのでしょうか？
ということで私が考えて実際に行っている方法を、一例として述べてみます。
1.単語登録を活用する。
2.既に打ち込んだ文章中から同じ漢字等を持ってくる。
3.単語登録を一括で行う。
4.よく使う漢字等を再利用しやすくする。
5.誤入力しやすいものを変換する。
1.単語登録を活用する。
これはよくやっている方法ですよね。現在、単語登録数は3000語を超えるほどにまでなりました。結構、便利です。が、読みを忘れてしまったり、登録したのを忘れてしまったりしています。この点はしょうがないですよね。
ここでのポイントは、読みの設定方法です。
地球温暖化という単語があったとします。これにどのような読みをつけますか。"ちきゅうおんだんか"では、何をか言わんですよね。
採用している方法では、基本的には、読みは三文字としています。上の単語の変種として、地球寒冷化というのもありますから、二つに分けて、"ちき＋お"としています。
地球寒冷化の場合は、"ちき＋か"とすることができるので、似ていても読みが違うようにすることができるのです(あたまからだと、両方とも"ちきう"となってしまいます)。
接続詞及び語尾の場合は、"ん＋X"としています。
"ん＋し"の場合は、"しかし、"というふうになります。
"ん＋あ"の場合は、"である。"というふうになります。
登録にむいているのはタイプしにくいものとなりますが、例えば、拗音のあるもの、ぱ行のもの、長音(ー)のあるもの、カタカナの単語などでしょうか。
この方法は、誰でもイメージがつきやすいと思います。
2.既に打ち込んだ文章中から同じ漢字等を持ってくる。
これは、QXマクロとなりますが、すぐ前にある文章群から、漢字等の単語を持ってきて、そのうちから選択して文章に挿入するというものです。
この場所で、マクロを動かしますと、
QXマクロ、文章群、漢字等、単語、選択、文章、挿入、
文章中、
方法、イメージ
登録、タイプ、拗音、長音、カタカナ、単語…
というような単語群が抜き出されます。
この中から選ぶというのですが、一度選んだものは別に格納しておいて、そちらからも選択できるようにしています。こちらの場合は、選択されたものは一番上に移動するという機能がついています。また、正確ではありませんが、漢字の読みに近いもので限定して表示させることもできます("あ-こ"までとか)。
長い文字列の場合はメリットとがありますが、タイプしたほうが速いのではないかと思われることが多いです。
連続処理が基本となっていますので、数パラグラフで、同じ単語が頻繁に出てくる場合は便利です(一括置換えという手もありますが)。
3.単語登録を一括で行う。
これは、1の準備作業を楽にするというものです。文中の単語を選択して読みを入れると、単語登録用の形式にしてくれるというマクロです。
単語登録用の形式とは、
こんほ 根本的 名詞
というものです(間はタブでつないでいる)。
一括して単語登録用の形式にしてしまい、ファイルに追加格納し、そのファイルを読み込んで一括で単語登録をしてしまおうというものです(IMEの辞書ツールのツールのテキストファイルからの登録)。
4.よく使う漢字等を再利用しやすくする。
これは現在、構想倒れですが、単語登録の内容を出力したものに、3.の機能で単語をどんどん登録していきます。このファイルを読みこんで、読み(の一部)を入れることによって、その読みを含む単語を表示させ、そこから選択しようというものです。例えば、"ちき"といれれば、"地球温暖化、地球、地球寒冷化など"が出てきてそこから選ぶことができます。
この機能は、単語登録をしてしまうということをやってしまえば、意味が薄れてしまうということで、現在ほとんど使っていません。
5.誤入力しやすいものを変換する。
これは既存のQXマクロで複数語一括変換マクロというのがあるので、それを利用すれば、やることは変換表を作ることだけになります。256組まで登録できるので、かなりのものと思われます。変換表は、拡張子を.TBLとするテキストファイルです。
変換表は、次の形式となります。
際m手 きわめて (間はタブ)
※eの入力がぬけてしまった。
誤入力の癖みたいのがあると思いますので、その都度変換表に追加しています。
※複数語一括変換マクロは下記のページを参照してください。
REPMULTI
複数文字列一括置換
http://www.vector.co.jp/soft/win95/writing/se107231.html
文章作成での一例
1.まず、文章を単純に入れていく
2.5.の誤入力変換マクロでタイプミスの癖を修正する
3.頻繁に出てくるような単語を文章中の単語から選択し追加していく。
4.文章をチェックして、間違いの部分を修正する。この際、タイプミスで誤入力変換マクロで対応したほうがいいものは、変換テーブルに変換文字を追加登録します。
5.文章の中で単語登録したい単語を選んで、一括して単語登録用の文字列をつくり、IMEの辞書機能で一括して単語登録する。
このようなことで文章を入力していますが、やはり、単語登録とタイピングを正確に早くするほうが一番大事な気がします。
そうはいっても、タイピングについてはあまり上達していないというのも事実です。そんな時には何かにすがりたくなるものです。
答え＝⇒タイピングを早くすればいいのです。それも間違いが少ないようなレベルになるまで努力しましょう。
といっても、疲れてきたときなど、飲んだ後など、タイピングのレベルは落ちてきますよね。誤入力もなかなかなくなりませんよね。
何とか便利な方法はないのでしょうか？
ということで私が考えて実際に行っている方法を、一例として述べてみます。
1.単語登録を活用する。
2.既に打ち込んだ文章中から同じ漢字等を持ってくる。
3.単語登録を一括で行う。
4.よく使う漢字等を再利用しやすくする。
5.誤入力しやすいものを変換する。
1.単語登録を活用する。
これはよくやっている方法ですよね。現在、単語登録数は3000語を超えるほどにまでなりました。結構、便利です。が、読みを忘れてしまったり、登録したのを忘れてしまったりしています。この点はしょうがないですよね。
ここでのポイントは、読みの設定方法です。
地球温暖化という単語があったとします。これにどのような読みをつけますか。"ちきゅうおんだんか"では、何をか言わんですよね。
採用している方法では、基本的には、読みは三文字としています。上の単語の変種として、地球寒冷化というのもありますから、二つに分けて、"ちき＋お"としています。
地球寒冷化の場合は、"ちき＋か"とすることができるので、似ていても読みが違うようにすることができるのです(あたまからだと、両方とも"ちきう"となってしまいます)。
接続詞及び語尾の場合は、"ん＋X"としています。
"ん＋し"の場合は、"しかし、"というふうになります。
"ん＋あ"の場合は、"である。"というふうになります。
登録にむいているのはタイプしにくいものとなりますが、例えば、拗音のあるもの、ぱ行のもの、長音(ー)のあるもの、カタカナの単語などでしょうか。
この方法は、誰でもイメージがつきやすいと思います。
2.既に打ち込んだ文章中から同じ漢字等を持ってくる。
これは、QXマクロとなりますが、すぐ前にある文章群から、漢字等の単語を持ってきて、そのうちから選択して文章に挿入するというものです。
この場所で、マクロを動かしますと、
QXマクロ、文章群、漢字等、単語、選択、文章、挿入、
文章中、
方法、イメージ
登録、タイプ、拗音、長音、カタカナ、単語…
というような単語群が抜き出されます。
この中から選ぶというのですが、一度選んだものは別に格納しておいて、そちらからも選択できるようにしています。こちらの場合は、選択されたものは一番上に移動するという機能がついています。また、正確ではありませんが、漢字の読みに近いもので限定して表示させることもできます("あ-こ"までとか)。
長い文字列の場合はメリットとがありますが、タイプしたほうが速いのではないかと思われることが多いです。
連続処理が基本となっていますので、数パラグラフで、同じ単語が頻繁に出てくる場合は便利です(一括置換えという手もありますが)。
3.単語登録を一括で行う。
これは、1の準備作業を楽にするというものです。文中の単語を選択して読みを入れると、単語登録用の形式にしてくれるというマクロです。
単語登録用の形式とは、
こんほ 根本的 名詞
というものです(間はタブでつないでいる)。
一括して単語登録用の形式にしてしまい、ファイルに追加格納し、そのファイルを読み込んで一括で単語登録をしてしまおうというものです(IMEの辞書ツールのツールのテキストファイルからの登録)。
4.よく使う漢字等を再利用しやすくする。
これは現在、構想倒れですが、単語登録の内容を出力したものに、3.の機能で単語をどんどん登録していきます。このファイルを読みこんで、読み(の一部)を入れることによって、その読みを含む単語を表示させ、そこから選択しようというものです。例えば、"ちき"といれれば、"地球温暖化、地球、地球寒冷化など"が出てきてそこから選ぶことができます。
この機能は、単語登録をしてしまうということをやってしまえば、意味が薄れてしまうということで、現在ほとんど使っていません。
5.誤入力しやすいものを変換する。
これは既存のQXマクロで複数語一括変換マクロというのがあるので、それを利用すれば、やることは変換表を作ることだけになります。256組まで登録できるので、かなりのものと思われます。変換表は、拡張子を.TBLとするテキストファイルです。
変換表は、次の形式となります。
際m手 きわめて (間はタブ)
※eの入力がぬけてしまった。
誤入力の癖みたいのがあると思いますので、その都度変換表に追加しています。
※複数語一括変換マクロは下記のページを参照してください。
REPMULTI
複数文字列一括置換
http://www.vector.co.jp/soft/win95/writing/se107231.html
文章作成での一例
1.まず、文章を単純に入れていく
2.5.の誤入力変換マクロでタイプミスの癖を修正する
3.頻繁に出てくるような単語を文章中の単語から選択し追加していく。
4.文章をチェックして、間違いの部分を修正する。この際、タイプミスで誤入力変換マクロで対応したほうがいいものは、変換テーブルに変換文字を追加登録します。
5.文章の中で単語登録したい単語を選んで、一括して単語登録用の文字列をつくり、IMEの辞書機能で一括して単語登録する。
このようなことで文章を入力していますが、やはり、単語登録とタイピングを正確に早くするほうが一番大事な気がします。
そうはいっても、タイピングについてはあまり上達していないというのも事実です。そんな時には何かにすがりたくなるものです。
2010年6月6日日曜日
第148回目、連続した年度を簡単に入れられないか
○第148回目、連続した年度を簡単に入れられないか
たまのことですが、連続した年度を見出しに入れたい時があります。例えば、平成20年度…とかです。
たて見出しでは、桁数が多いためうるさくなってしまうのですが、横見出しの時は、そうでもないので、ニーズが出てきます。
1.まず最初のセルに、平成20年度と入れます(B2とします)。
2.次のセルには、計算で文字列をつくります(C2とします)。
=left(B2,2) & (MID(B2,3,2) + 1 & RIGHT(B2,2)
3.この計算を必要なだけコピーします。
最初のセルに、平成01年度と入れると、一つ目(平成2年度という表示)はいいのですが、二つ目のところにエラーが出ます。
これは年度が一桁の数値になっているからです。
これを避けるために、年度の部分をTEXT関数で整形してあげます。
=LEFT(W23,2) & TEXT(MID(W23,3,2) + 1,"00") & RIGHT(W23,2)
これを基本として、マクロ化するとどうなるでしょうか。
マクロ化するのは、表示スタイルをさまざまに選べるという機能をつければいいでしょう。
平成20年度のスタイル
H20のスタイル
20年度のスタイル
フリースタイル…基にする文字列の頭の文字列の桁数、後ろの文字列の桁数を指定して、真ん中の数値のみ更新していきます。
これを利用すると、平成20年00月の月を更新したものを作ることができます。
0年目…とかいう経過年度のケースですが、これは、9から10にいくところをどうするか、という問題があります。
どうしたらいいのか考えていたら、アイデアが浮かびました。
すぐ左のセルを使うのではなく、最初のセルを使えばいいのでした。
この場合は、TEXT文で数値を編集する必要がなくなります。
ひるがえって、これまでのものも最初のセルをもとにして計算式を組み立てるという方式にすべきでしょう。
マクロ処理の流れ
1.最初の数値が入っているセルからこれから作り出したいセルまでを指定してからマクロを実行する
2.表示したいスタイルを選択する。
3.指定範囲の情報を取得する。
4.最初のセルに計算式を設定し、値貼付けをして計算式をなくす。
最初の数字は、セルに書き込まれているものとなりますが、それを変数から定数化するために、値としてセルに貼付けします。
5.次以降のセルに、最初のセルをもとにして、計算式を入れる。
※このような年度をつける処理が、エクセルの基本機能にあるのかどうかは確かめていません。あればこのマクロはほぼ空振りとなります。
たまのことですが、連続した年度を見出しに入れたい時があります。例えば、平成20年度…とかです。
たて見出しでは、桁数が多いためうるさくなってしまうのですが、横見出しの時は、そうでもないので、ニーズが出てきます。
1.まず最初のセルに、平成20年度と入れます(B2とします)。
2.次のセルには、計算で文字列をつくります(C2とします)。
=left(B2,2) & (MID(B2,3,2) + 1 & RIGHT(B2,2)
3.この計算を必要なだけコピーします。
最初のセルに、平成01年度と入れると、一つ目(平成2年度という表示)はいいのですが、二つ目のところにエラーが出ます。
これは年度が一桁の数値になっているからです。
これを避けるために、年度の部分をTEXT関数で整形してあげます。
=LEFT(W23,2) & TEXT(MID(W23,3,2) + 1,"00") & RIGHT(W23,2)
これを基本として、マクロ化するとどうなるでしょうか。
マクロ化するのは、表示スタイルをさまざまに選べるという機能をつければいいでしょう。
平成20年度のスタイル
H20のスタイル
20年度のスタイル
フリースタイル…基にする文字列の頭の文字列の桁数、後ろの文字列の桁数を指定して、真ん中の数値のみ更新していきます。
これを利用すると、平成20年00月の月を更新したものを作ることができます。
0年目…とかいう経過年度のケースですが、これは、9から10にいくところをどうするか、という問題があります。
どうしたらいいのか考えていたら、アイデアが浮かびました。
すぐ左のセルを使うのではなく、最初のセルを使えばいいのでした。
この場合は、TEXT文で数値を編集する必要がなくなります。
ひるがえって、これまでのものも最初のセルをもとにして計算式を組み立てるという方式にすべきでしょう。
マクロ処理の流れ
1.最初の数値が入っているセルからこれから作り出したいセルまでを指定してからマクロを実行する
2.表示したいスタイルを選択する。
3.指定範囲の情報を取得する。
4.最初のセルに計算式を設定し、値貼付けをして計算式をなくす。
最初の数字は、セルに書き込まれているものとなりますが、それを変数から定数化するために、値としてセルに貼付けします。
5.次以降のセルに、最初のセルをもとにして、計算式を入れる。
※このような年度をつける処理が、エクセルの基本機能にあるのかどうかは確かめていません。あればこのマクロはほぼ空振りとなります。
2010年5月30日日曜日
第147回目、指定した場所に色を塗る
○第147回目、指定した場所に色を塗る
指定されたセルを色を付けるというマクロですが、それほどたいしたものではないという感じはします。あると便利という程度のものかもしれません。
通常の操作で色をつけるにはどのような操作になるでしょうか。
1.範囲を指定する。
2.Ctrl+1でセル書式のダイアログを出す。
3.色のタブを選ぶ。
4.カラーパレットから色を指定する(ここでは単純な指定とします＝カラーコードで指定)。
違う場所にも同じ色を付ける場合は、他の書式が同じであれば、書式のみのコピーとなります。
一般的には、同じ操作を繰り返すということでしょう。
ニーズがどのくらいかるのかということになりますが、よかったら作ってみてくださいという程度の紹介とします。
色コードと色の対応は？
＝⇒1-56のカラーコード"、または"カラーパレットのインデックス番号"色
for i = 0 to 6
for j = 0 to 7
Cells(4 + i , 3 + j )= i * 8 + j + 1
Cells(4 + i , 3 + j ).Interior.ColorIndex = i * 8 + j + 1
next
next
を動かしてセルに付いた色をみてください。
なんだこういう仕組みだったのか
＝⇒そのとおりです。ほとんど操作マクロでできてしまいます。
これでは面白くないので、
1.連続処理にする(色は直前に指定したものを引き継ぐとします)
2.色をとるという機能を入れる
3.既に色が付いているセルには、新たな色をつけない、という選択ができる
マクロの構造はdoループとします。
終了は、セルの範囲指定の段階で、ESCキーとか終了キーを押した場合とします。
色をとる場合は、色コードを指定する際に0を入力します。
色が付いているセルをそのままにする場合は、色コードの指定の時に、コードに*をつけることで区分します。
マクロのポイントは、
1.二回目以降のセル範囲の指定
Dim セル範囲 As Range
On Error GoTo ErrorTrap
'色を付たいセル範囲の設定
Set セル範囲 = Application.InputBox(Prompt:=msg02 & "セル範囲を指定してください", Default:=in_rrcc1, Left:=2, Top:=2, Type:=8)
in_rrcc1 = セル範囲.Address
If errflag = 99 Then Exit Do 'ESCキーを押すとエラーになりループを抜ける
Range(in_rrcc1).Select
ErrorTrap:
errflag = 99
Resume Next
2.指定した色に*が付いているか
iro = InputBox("色コードを指定してください", , iro)
p1 = InStr(iro, "*")
If p1 <> 0 Then
3.色をとる
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = xlColorIndexNone
4.色情報の指定と色を付ける(色が付いているセルには新たに色をつけない場合)
Range(in_rrcc2).Select
iro = InputBox("色コードを指定してください", , iro)
For Each CCC In Selection 'cells()を使わないで、指定範囲内のセルひとつずつ処理する方法
iro0 = CCC.Interior.ColorIndex
If iro0 = -4142 Or iro = 0 Then '-4142はセルに色が付いていないという意味
CCC.Interior.ColorIndex = iro
End If
Next
こんなものでしょうか。
参考までに、系列別の色コードを掲げて置きます。
"黄色系＝⇒19-36-27-6"
"水色系＝⇒20-37-33-42-28-8"
"ダイダイ系＝⇒40-44-45-22-46-3"
"桃色系＝⇒38-26-7"
"紫系＝⇒24-17-39"
"緑系＝⇒43-4-50-10-14-31"
"こげ茶系＝⇒9-18-53-54-30"
"濃い紫系＝⇒13-29-21-12"
"濃い灰色系＝⇒15-48-16"
"群青系＝⇒41-23-32-5-55"
指定されたセルを色を付けるというマクロですが、それほどたいしたものではないという感じはします。あると便利という程度のものかもしれません。
通常の操作で色をつけるにはどのような操作になるでしょうか。
1.範囲を指定する。
2.Ctrl+1でセル書式のダイアログを出す。
3.色のタブを選ぶ。
4.カラーパレットから色を指定する(ここでは単純な指定とします＝カラーコードで指定)。
違う場所にも同じ色を付ける場合は、他の書式が同じであれば、書式のみのコピーとなります。
一般的には、同じ操作を繰り返すということでしょう。
ニーズがどのくらいかるのかということになりますが、よかったら作ってみてくださいという程度の紹介とします。
色コードと色の対応は？
＝⇒1-56のカラーコード"、または"カラーパレットのインデックス番号"色
for i = 0 to 6
for j = 0 to 7
Cells(4 + i , 3 + j )= i * 8 + j + 1
Cells(4 + i , 3 + j ).Interior.ColorIndex = i * 8 + j + 1
next
next
を動かしてセルに付いた色をみてください。
なんだこういう仕組みだったのか
＝⇒そのとおりです。ほとんど操作マクロでできてしまいます。
これでは面白くないので、
1.連続処理にする(色は直前に指定したものを引き継ぐとします)
2.色をとるという機能を入れる
3.既に色が付いているセルには、新たな色をつけない、という選択ができる
マクロの構造はdoループとします。
終了は、セルの範囲指定の段階で、ESCキーとか終了キーを押した場合とします。
色をとる場合は、色コードを指定する際に0を入力します。
色が付いているセルをそのままにする場合は、色コードの指定の時に、コードに*をつけることで区分します。
マクロのポイントは、
1.二回目以降のセル範囲の指定
Dim セル範囲 As Range
On Error GoTo ErrorTrap
'色を付たいセル範囲の設定
Set セル範囲 = Application.InputBox(Prompt:=msg02 & "セル範囲を指定してください", Default:=in_rrcc1, Left:=2, Top:=2, Type:=8)
in_rrcc1 = セル範囲.Address
If errflag = 99 Then Exit Do 'ESCキーを押すとエラーになりループを抜ける
Range(in_rrcc1).Select
ErrorTrap:
errflag = 99
Resume Next
2.指定した色に*が付いているか
iro = InputBox("色コードを指定してください", , iro)
p1 = InStr(iro, "*")
If p1 <> 0 Then
3.色をとる
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = xlColorIndexNone
4.色情報の指定と色を付ける(色が付いているセルには新たに色をつけない場合)
Range(in_rrcc2).Select
iro = InputBox("色コードを指定してください", , iro)
For Each CCC In Selection 'cells()を使わないで、指定範囲内のセルひとつずつ処理する方法
iro0 = CCC.Interior.ColorIndex
If iro0 = -4142 Or iro = 0 Then '-4142はセルに色が付いていないという意味
CCC.Interior.ColorIndex = iro
End If
Next
こんなものでしょうか。
参考までに、系列別の色コードを掲げて置きます。
"黄色系＝⇒19-36-27-6"
"水色系＝⇒20-37-33-42-28-8"
"ダイダイ系＝⇒40-44-45-22-46-3"
"桃色系＝⇒38-26-7"
"紫系＝⇒24-17-39"
"緑系＝⇒43-4-50-10-14-31"
"こげ茶系＝⇒9-18-53-54-30"
"濃い紫系＝⇒13-29-21-12"
"濃い灰色系＝⇒15-48-16"
"群青系＝⇒41-23-32-5-55"
2010年5月23日日曜日
第146回目 加重平均計算式からの応用
○第146回目 加重平均計算式からの応用
前回で｢加重平均の計算式｣を作りました。それを使っていたのですが、あとで件数の合計はどうなっているのかを知る必要が出てきました。どうしたらスムーズにつくれるのか。
これがテーマとなります。
簡単なのは、計算式を移植して、そこから分母だけ取り出してしまうというものです。一個作ればあとはコピーで複製できます。
もともと、加重平均の計算式を作る際に、合計の件数もセルに入れておくんじゃないの？
＝⇒それもそのとおりですね。加重平均値の計算式を入れたあとで、分母を入れる場所を指定して、そこに合計の件数の計算式を入れるということを、今のマクロに追加しましょう。
これは、これまでのものの簡単な機能アップですので、皆さんでやってください。
合計の件数(分母の値)を求めるかをきいて、求める場合はそのセルを指定するということでいいのではないでしょうか。
さて、ここでは、ある計算式から、分母を取り出すことを考えてみます。
当然、割り算があるということが前提です。
通常は、/の直後の()の内部が求めるものではないでしょうか。
＝⇒/が複数あったらどうするか＝⇒一番後ろにあるもののみ対象とする。
＝⇒/の直後に(がなかったらどうするか＝⇒その後の,(カンマ)の直前までとする。カンマもなければ処理しない。
という前提条件にしてしまい、100％ではない簡単なマクロとしましょう。
これの応用としては、#DIV/0!回避のマクロが考えられます。分母がゼロにもかかわらず、割算をしてしまったというものです。計算式から分母を取り出して、if文を作り、ゼロで割ることを回避するものです。
イメージとしては、
=B11/B12 ＝⇒ =IF(B12<> 0,B11/B12,0) とするものです。
このケースもよくあります。計算式を修正するのが面倒くさいので、結構最後までも残ってしまいます(当然手持ち資料ベース)。
もう一つ。
前の回に戻るのですが、値と件数の位置関係がまったく同じという場合は、二組以降は値のみの指定でよくなりますね。このように操作を簡素化したものを作るのもいいかもしれません。
多くの場合は、二つの項目の位置関係が同じだと言えるのではないでしょうか。
これは、一般的な場合は適用できないので、非一般系、又は、特殊系の加重平均という名をつけてもいいでしょう。
(特殊相対性理論…加速度を考慮しない特殊な場合の理論。加速度を考慮したものは一般相対性理論となる。これから"特殊系"と名づけてみました)
前回で｢加重平均の計算式｣を作りました。それを使っていたのですが、あとで件数の合計はどうなっているのかを知る必要が出てきました。どうしたらスムーズにつくれるのか。
これがテーマとなります。
簡単なのは、計算式を移植して、そこから分母だけ取り出してしまうというものです。一個作ればあとはコピーで複製できます。
もともと、加重平均の計算式を作る際に、合計の件数もセルに入れておくんじゃないの？
＝⇒それもそのとおりですね。加重平均値の計算式を入れたあとで、分母を入れる場所を指定して、そこに合計の件数の計算式を入れるということを、今のマクロに追加しましょう。
これは、これまでのものの簡単な機能アップですので、皆さんでやってください。
合計の件数(分母の値)を求めるかをきいて、求める場合はそのセルを指定するということでいいのではないでしょうか。
さて、ここでは、ある計算式から、分母を取り出すことを考えてみます。
当然、割り算があるということが前提です。
通常は、/の直後の()の内部が求めるものではないでしょうか。
＝⇒/が複数あったらどうするか＝⇒一番後ろにあるもののみ対象とする。
＝⇒/の直後に(がなかったらどうするか＝⇒その後の,(カンマ)の直前までとする。カンマもなければ処理しない。
という前提条件にしてしまい、100％ではない簡単なマクロとしましょう。
これの応用としては、#DIV/0!回避のマクロが考えられます。分母がゼロにもかかわらず、割算をしてしまったというものです。計算式から分母を取り出して、if文を作り、ゼロで割ることを回避するものです。
イメージとしては、
=B11/B12 ＝⇒ =IF(B12<> 0,B11/B12,0) とするものです。
このケースもよくあります。計算式を修正するのが面倒くさいので、結構最後までも残ってしまいます(当然手持ち資料ベース)。
もう一つ。
前の回に戻るのですが、値と件数の位置関係がまったく同じという場合は、二組以降は値のみの指定でよくなりますね。このように操作を簡素化したものを作るのもいいかもしれません。
多くの場合は、二つの項目の位置関係が同じだと言えるのではないでしょうか。
これは、一般的な場合は適用できないので、非一般系、又は、特殊系の加重平均という名をつけてもいいでしょう。
(特殊相対性理論…加速度を考慮しない特殊な場合の理論。加速度を考慮したものは一般相対性理論となる。これから"特殊系"と名づけてみました)
2010年5月8日土曜日
第145回目 加重平均計算式の作成
○第145回目 加重平均計算式の作成
あちこちに散らばっている件数、値(平均値)の加重平均をとりたいというニーズがあります。
通常の操作では、値×件数をどんどん足していって、最後に括弧でくくり、合計の件数でわる、ということになります。分母の件数も足していく必要があります。
散らばり度合いが大きいと面倒くさくなってしまいますね。また、分母の件数をもう一度指定しなければならないというのも、面倒さが増します。
このような計算式を手軽に作ろうという発想です。
手順
0.計算式を作りたいセルにおいてマクロを実行します
1.第一の組の場所にいって、値と件数を指定します。指定は値と件数という順番にします。
2.次の場所にいって、同様に指定します。これを必要だけ繰り返します。最大組数は6組としました。
3.指定終了の場合は、加重平均の計算式を作り、最初のセルに入れます。
これはこれまでの、マクロをベースにすれば簡単です。
機能をもう少し明確にしましょう。
1.別のシートでのセルを指定できるようにします。(余裕があれば別ブックまで拡張してください)
2.計算結果は、四捨五入して、端数は指定できるようにします。
3.別シートに飛ぶ場合は、今のシートのセルと同じ場所に飛ぶか、それとも、単純に飛ぶかの指定ができます。
4.同一の組の値と件数は、同じ一シートにあるものとします。
注意点としては、
1.各組ごとにシート名を取得しますが、計算式のあるセルと同じシートの場合は、シート名はなくすこと。
2.取得したセル座標から$をとる。(これを基にコピーする時に、$は邪魔になります)
3.デバック中は、計算式に何かの記号をつけて文字化しておいてください。
4.理由はよくわからないのですが、シート名を'で囲むことが必要な場合があり混乱しました。
計算式を、keisansikiに入れて、
Range(AAA1) = keisansikiとすると、シート名を'で囲まなくても大丈夫です。
Range(AAA1).Formula = keisansikiとすると、シート名に工夫(ある場合に限り、'で囲む)が必要です。
このマクロは、使用するセルが、どのシートでも同じ場所にあるときは非常に便利です。
あちこちに散らばっている件数、値(平均値)の加重平均をとりたいというニーズがあります。
通常の操作では、値×件数をどんどん足していって、最後に括弧でくくり、合計の件数でわる、ということになります。分母の件数も足していく必要があります。
散らばり度合いが大きいと面倒くさくなってしまいますね。また、分母の件数をもう一度指定しなければならないというのも、面倒さが増します。
このような計算式を手軽に作ろうという発想です。
手順
0.計算式を作りたいセルにおいてマクロを実行します
1.第一の組の場所にいって、値と件数を指定します。指定は値と件数という順番にします。
2.次の場所にいって、同様に指定します。これを必要だけ繰り返します。最大組数は6組としました。
3.指定終了の場合は、加重平均の計算式を作り、最初のセルに入れます。
これはこれまでの、マクロをベースにすれば簡単です。
機能をもう少し明確にしましょう。
1.別のシートでのセルを指定できるようにします。(余裕があれば別ブックまで拡張してください)
2.計算結果は、四捨五入して、端数は指定できるようにします。
3.別シートに飛ぶ場合は、今のシートのセルと同じ場所に飛ぶか、それとも、単純に飛ぶかの指定ができます。
4.同一の組の値と件数は、同じ一シートにあるものとします。
注意点としては、
1.各組ごとにシート名を取得しますが、計算式のあるセルと同じシートの場合は、シート名はなくすこと。
2.取得したセル座標から$をとる。(これを基にコピーする時に、$は邪魔になります)
3.デバック中は、計算式に何かの記号をつけて文字化しておいてください。
4.理由はよくわからないのですが、シート名を'で囲むことが必要な場合があり混乱しました。
計算式を、keisansikiに入れて、
Range(AAA1) = keisansikiとすると、シート名を'で囲まなくても大丈夫です。
Range(AAA1).Formula = keisansikiとすると、シート名に工夫(ある場合に限り、'で囲む)が必要です。
このマクロは、使用するセルが、どのシートでも同じ場所にあるときは非常に便利です。
2010年5月3日月曜日
第144回目 ユーザー関数(条件付SUMPRO関数、その3)
○第144回目 ユーザー関数(条件付SUMPRO関数、その3)
パラメータ='D20,A42,A20,42,B20,C21のコピーの問題です。
これをコピーしたら、計算式のように座標が変わってほしいということです。
考えてみましたら、これはそれほど難しくないのです。既存のマクロで何とかなります。
その前にパラメータの項目を修正しておきます。
パラメータ=/D19,D20,A20,42,B20,C21として
E19にこのパラメータを入れておきます。
関数式を入れるのは、D21にします。
D19,D20の数値を入れ替えることによって、答えが任意に求められることになります。
パラメータと関数式が、D19:E21の中に収まることになります。
このセットをコピーしていきます。
参照する表がかわらないとすると、パラメータの中で動くのは、D19、D20の二つになります。
そこでパラメータそのものを、/D19,D20,$A$20,42,$B$20,$C$21 とします。(パラメータをこう変えると、関数式の中に、頭の/を取る処理を加えればよろしいです)
1.まず、パラメータを、複数項目の置換えで、/を=、,を+にし、次のようにします。
=D19+D20+$A$20+42+$B$20+$C$21
2.関数式を含めたセットを任意の場所にコピーします。そうすれば座標が動きます。
3.次に全体を指定して、複数項目の置き換えをし、元の姿に戻します。 = は/、+は,に置換えです。
置換え文字
┏━━┳━━┳━━┓
┃, ┃+ ┃ ┃
┣━━╋━━╋━━┫
┃/ ┃= ┃/ ┃
┣━━╋━━╋━━┫
┃ ┃+ ┃, ┃
┗━━┻━━┻━━┛
と3列3行に置換文字を入れておけば便利でしょう。
最初の置換えは、先頭の2行2列を指定し、最後の置換えは、2行2列目から3行3列目を指定します。(計算式なので計算式の要件を満たしていなければ、=絡みの置換えが無効となる)
パラメータ='D20,A42,A20,42,B20,C21のコピーの問題です。
これをコピーしたら、計算式のように座標が変わってほしいということです。
考えてみましたら、これはそれほど難しくないのです。既存のマクロで何とかなります。
その前にパラメータの項目を修正しておきます。
パラメータ=/D19,D20,A20,42,B20,C21として
E19にこのパラメータを入れておきます。
関数式を入れるのは、D21にします。
D19,D20の数値を入れ替えることによって、答えが任意に求められることになります。
パラメータと関数式が、D19:E21の中に収まることになります。
このセットをコピーしていきます。
参照する表がかわらないとすると、パラメータの中で動くのは、D19、D20の二つになります。
そこでパラメータそのものを、/D19,D20,$A$20,42,$B$20,$C$21 とします。(パラメータをこう変えると、関数式の中に、頭の/を取る処理を加えればよろしいです)
1.まず、パラメータを、複数項目の置換えで、/を=、,を+にし、次のようにします。
=D19+D20+$A$20+42+$B$20+$C$21
2.関数式を含めたセットを任意の場所にコピーします。そうすれば座標が動きます。
3.次に全体を指定して、複数項目の置き換えをし、元の姿に戻します。 = は/、+は,に置換えです。
置換え文字
┏━━┳━━┳━━┓
┃, ┃+ ┃ ┃
┣━━╋━━╋━━┫
┃/ ┃= ┃/ ┃
┣━━╋━━╋━━┫
┃ ┃+ ┃, ┃
┗━━┻━━┻━━┛
と3列3行に置換文字を入れておけば便利でしょう。
最初の置換えは、先頭の2行2列を指定し、最後の置換えは、2行2列目から3行3列目を指定します。(計算式なので計算式の要件を満たしていなければ、=絡みの置換えが無効となる)
2010年4月25日日曜日
第143回目 ユーザー関数(条件付SUMPRO関数、その2)
○第143回目 ユーザー関数(条件付SUMPRO関数、その2)
マクロの中での記述
a = 条件付SUMPRO_02("D20,A42,A20,42,B20,C21")
または、
a = 条件付SUMPRO_02(D18) ‘D18には、D20,A42,A20,42,B20,C21が入っている。
先頭行、末尾行の記述
Function 条件付SUMPRO_02(ByVal NN) As Double
【中味】
End Function
中味の記述
パラメータ='D20,A42,A20,42,B20,C21
まずはパラメータの説明です。
D20…セルの座標です
A42…セルの座標です。場合によっては数値が入ってきます
A20…セルの座標です
42…行数です
B20…セルの座標です
C21…セルの座標です。省略された場合は、機能的には、B列に対するSUM関数となります。
D20のセルにある年齢から
A42のセルにある年齢までについて
B20からはじまる項目と
C21からはじまる項目とを掛け合わせた総和を求める。
(参照すべき基準年齢は、A20からA42にはいっている)
Range().Selectが使えませんので、違う方法で列･行を求めます。
流れです。
1.パラメータを分解します(mm(i))
2.座標の場合、それぞれの項目から、列･行を求めます(mm_retu(i)、mm_gyo(i))
3.列記号は列番号に変換します。(mm_retu(i)の置き換え)
4.数値情報の場合、セルの内容を求めます(XX,XX9)
5.積和をとる範囲(最初と最後の行数)を求めます(gyo91、gyo99)
6.関数式を作ります
1.パラメータを分解します(mm(i))
nn0 = NN
for i = 1 to 5
p1 = InStr(NN, ",")
mm(i) = Left(NN, p1 - 1)
NN = Mid(NN, p1 + 1)
next
mm(6) = NN
If mm(6) = "" Then flag1 = 1
2.座標の場合、それぞれの項目から、列･行を求めます(mm_retu(i)、mm_gyo(i))
ii0 = 6
If flag1 = 1 Then
ii0 = 5
mm_retu(6) = ""
mm_gyo(6) = ""
End If
For i = 1 To ii0
mm(i) = UCase(mm(i))
If Left(mm(i), 1) Like "[0-9]" = True Then '一桁目が数字
mm_retu(i) = ""
mm_gyo(i) = Val(mm(i))
ElseIf Mid(mm(i), 2, 1) Like "[A-Z]" = True Then '二桁目が英字
mm_retu(i) = Left(mm(i), 2)
mm_gyo(i) = Mid(mm(i), 3)
Else '二桁目が数字
mm_retu(i) = Left(mm(i), 1)
mm_gyo(i) = Mid(mm(i), 2)
End If
Next i
3.列記号は列番号に変換します。(mm_retu(i)の置き換え)
省略
4.数値情報の場合、セルの内容を求めます(XX,XX9)
変数を数値に置換えて、通常の変数名の変数に入れる
パラメータ='D20,A42,A20,42,B20,C21
retu00 = Val(mm_retu(1)) 集計したい最初の参照値が入っているセルの列番号
gyo00 = Val(mm_gyo(1)) 集計したい最初の参照値が入っているセルの行番号
retu09 = Val(mm_retu(2)) 集計したい最後の参照値が入っているセルの列番号 数値で入っていた場合、ここが0になります
gyo09 = Val(mm_gyo(2)) 集計したい最後の参照値が入っているセルの行番号
retu11 = Val(mm_retu(3)) 集計の参照となる先頭セルの列番号
gyo11 = Val(mm_gyo(3)) 集計の参照となる先頭セルの行番号
retu19 = retu11 集計の参照となる最後セルの列番号
gyo19 = Val(mm_gyo(4)) 集計の参照となる最後セルの行番号
retu21 = Val(mm_retu(5)) 集計項目1の先頭セルの列番号
gyo21 = Val(mm_gyo(5)) 集計項目1の先頭セルの行番号
retu31 = Val(mm_retu(6)) 集計項目2の先頭セルの列番号
gyo31 = Val(mm_gyo(6)) 集計項目2の先頭セルの行番号
gyo_cnt1 = gyo19 - gyo11 + 1 集計する項目数
XX = Cells(gyo00, retu00) 集計したい最初の参照値
If retu09 = 0 Then
XX9 = gyo09 集計したい最後の参照値。数値の場合
Else
XX9 = Cells(gyo09, retu09) 集計したい最後の参照値。座標の場合
End If
5.積和をとる範囲(最初と最後の行数)を求めます(gyo_cnt91、gyo_cnt99)
For i = 1 To gyo_cnt1
If XX = Cells(gyo11 + i - 1, retu11) Then Exit For
Next
gyo_cnt91 = i '先頭からの相対的な行数
For i = 1 To gyo_cnt1
If XX9 = Cells(gyo11 + i - 1, retu11) Then Exit For
Next
gyo_cnt99 = i '先頭からの行数
6.関数式を作ります
本体のSumProduct関数を使用します。
If flag1 = 1 Then '1項目しか指定していない場合
条件付SUMPRO_02 = Application.WorksheetFunction.Sum(Range(Cells(gyo21 + gyo_cnt91 - 1, retu21), Cells(gyo21 + gyo_cnt99 - 1, retu21)))
Else
条件付SUMPRO_02 = Application.WorksheetFunction.SumProduct(Range(Cells(gyo21 + gyo_cnt91 - 1, retu21), Cells(gyo21 + gyo_cnt99 - 1, retu21)), Range(Cells(gyo31 + gyo_cnt91 - 1, retu31), Cells(gyo31 + gyo_cnt99 - 1, retu31)))
End If
こんなものでしょうか。
ここでこんな問題が出てきました。
仕事ではたまにシミュレーション系のものがあります。その場合は、諸々の機能が複数年必要であり、通常は計算式のコピーで対応可能です。
しかし、この関数はパラメータとセットになっています。これが単純にコピーできないのです。パラメータ(D20,A42,A20,42,B20,C21)は文字ですからコピーしても座標は変わりません。ここでいうA列は変わらなくてもいいのですが、他のものは変わってほしいです。
何とかならないのでしょうか。
マクロの中での記述
a = 条件付SUMPRO_02("D20,A42,A20,42,B20,C21")
または、
a = 条件付SUMPRO_02(D18) ‘D18には、D20,A42,A20,42,B20,C21が入っている。
先頭行、末尾行の記述
Function 条件付SUMPRO_02(ByVal NN) As Double
【中味】
End Function
中味の記述
パラメータ='D20,A42,A20,42,B20,C21
まずはパラメータの説明です。
D20…セルの座標です
A42…セルの座標です。場合によっては数値が入ってきます
A20…セルの座標です
42…行数です
B20…セルの座標です
C21…セルの座標です。省略された場合は、機能的には、B列に対するSUM関数となります。
D20のセルにある年齢から
A42のセルにある年齢までについて
B20からはじまる項目と
C21からはじまる項目とを掛け合わせた総和を求める。
(参照すべき基準年齢は、A20からA42にはいっている)
Range().Selectが使えませんので、違う方法で列･行を求めます。
流れです。
1.パラメータを分解します(mm(i))
2.座標の場合、それぞれの項目から、列･行を求めます(mm_retu(i)、mm_gyo(i))
3.列記号は列番号に変換します。(mm_retu(i)の置き換え)
4.数値情報の場合、セルの内容を求めます(XX,XX9)
5.積和をとる範囲(最初と最後の行数)を求めます(gyo91、gyo99)
6.関数式を作ります
1.パラメータを分解します(mm(i))
nn0 = NN
for i = 1 to 5
p1 = InStr(NN, ",")
mm(i) = Left(NN, p1 - 1)
NN = Mid(NN, p1 + 1)
next
mm(6) = NN
If mm(6) = "" Then flag1 = 1
2.座標の場合、それぞれの項目から、列･行を求めます(mm_retu(i)、mm_gyo(i))
ii0 = 6
If flag1 = 1 Then
ii0 = 5
mm_retu(6) = ""
mm_gyo(6) = ""
End If
For i = 1 To ii0
mm(i) = UCase(mm(i))
If Left(mm(i), 1) Like "[0-9]" = True Then '一桁目が数字
mm_retu(i) = ""
mm_gyo(i) = Val(mm(i))
ElseIf Mid(mm(i), 2, 1) Like "[A-Z]" = True Then '二桁目が英字
mm_retu(i) = Left(mm(i), 2)
mm_gyo(i) = Mid(mm(i), 3)
Else '二桁目が数字
mm_retu(i) = Left(mm(i), 1)
mm_gyo(i) = Mid(mm(i), 2)
End If
Next i
3.列記号は列番号に変換します。(mm_retu(i)の置き換え)
省略
4.数値情報の場合、セルの内容を求めます(XX,XX9)
変数を数値に置換えて、通常の変数名の変数に入れる
パラメータ='D20,A42,A20,42,B20,C21
retu00 = Val(mm_retu(1)) 集計したい最初の参照値が入っているセルの列番号
gyo00 = Val(mm_gyo(1)) 集計したい最初の参照値が入っているセルの行番号
retu09 = Val(mm_retu(2)) 集計したい最後の参照値が入っているセルの列番号 数値で入っていた場合、ここが0になります
gyo09 = Val(mm_gyo(2)) 集計したい最後の参照値が入っているセルの行番号
retu11 = Val(mm_retu(3)) 集計の参照となる先頭セルの列番号
gyo11 = Val(mm_gyo(3)) 集計の参照となる先頭セルの行番号
retu19 = retu11 集計の参照となる最後セルの列番号
gyo19 = Val(mm_gyo(4)) 集計の参照となる最後セルの行番号
retu21 = Val(mm_retu(5)) 集計項目1の先頭セルの列番号
gyo21 = Val(mm_gyo(5)) 集計項目1の先頭セルの行番号
retu31 = Val(mm_retu(6)) 集計項目2の先頭セルの列番号
gyo31 = Val(mm_gyo(6)) 集計項目2の先頭セルの行番号
gyo_cnt1 = gyo19 - gyo11 + 1 集計する項目数
XX = Cells(gyo00, retu00) 集計したい最初の参照値
If retu09 = 0 Then
XX9 = gyo09 集計したい最後の参照値。数値の場合
Else
XX9 = Cells(gyo09, retu09) 集計したい最後の参照値。座標の場合
End If
5.積和をとる範囲(最初と最後の行数)を求めます(gyo_cnt91、gyo_cnt99)
For i = 1 To gyo_cnt1
If XX = Cells(gyo11 + i - 1, retu11) Then Exit For
Next
gyo_cnt91 = i '先頭からの相対的な行数
For i = 1 To gyo_cnt1
If XX9 = Cells(gyo11 + i - 1, retu11) Then Exit For
Next
gyo_cnt99 = i '先頭からの行数
6.関数式を作ります
本体のSumProduct関数を使用します。
If flag1 = 1 Then '1項目しか指定していない場合
条件付SUMPRO_02 = Application.WorksheetFunction.Sum(Range(Cells(gyo21 + gyo_cnt91 - 1, retu21), Cells(gyo21 + gyo_cnt99 - 1, retu21)))
Else
条件付SUMPRO_02 = Application.WorksheetFunction.SumProduct(Range(Cells(gyo21 + gyo_cnt91 - 1, retu21), Cells(gyo21 + gyo_cnt99 - 1, retu21)), Range(Cells(gyo31 + gyo_cnt91 - 1, retu31), Cells(gyo31 + gyo_cnt99 - 1, retu31)))
End If
こんなものでしょうか。
ここでこんな問題が出てきました。
仕事ではたまにシミュレーション系のものがあります。その場合は、諸々の機能が複数年必要であり、通常は計算式のコピーで対応可能です。
しかし、この関数はパラメータとセットになっています。これが単純にコピーできないのです。パラメータ(D20,A42,A20,42,B20,C21)は文字ですからコピーしても座標は変わりません。ここでいうA列は変わらなくてもいいのですが、他のものは変わってほしいです。
何とかならないのでしょうか。
2010年4月17日土曜日
第142回目 ユーザー関数
○第142回目 ユーザー関数
便利なユーザー関数を作りましょう。
＝⇒ユーザー関数？それは何でしょうか。
SUM関数と同じようなもので、処理内容を自分で作れるというものです。
＝⇒作った後の使い方は？
SUM関数と同じように、セルに計算式として設定できます。例えば、与えられた数値まで1からの総和を求めるというユーザー関数を作ったとします。名前として、SUM_from1()としておきます。
セルに、=SUM_from1(10)と入れますと、55という答えが表示されます。
()の中は他のセルの座標でもかまいません。
＝⇒作り方は今までのマクロと違うのですか
ほぼ同じです。SUBの変わりにFUNCTIONで囲んでやります。
FUNCTION SUM_from1(NN) as Double
：
SUM_from1 = ……
End FUNCTION
【具体例】
Function 加重按分01(ByVal nn1, nn0, mm, keta As Double) As Double '；MJ013__fx
'nn1は個別値,nn0は合計値,mmは按分したいもの,ketaは四捨五入した時の桁
加重按分01 = Round(mm * (nn1 / nn0), keta)
End Function
使えるコマンドに制限があります。Cells(lr007,lc026+jj*ll006+4).Selectはつかえませんでした。
＝⇒セルに設定する計算式以外にも使えるのですか。
マクロの中で使えます。AA=SUM_from1(NN)というように使います。
マクロ側からNNを渡して、計算結果をFUCTION側からマクロに渡すという流れです。
関数が違う文字にある場合は、文字名を頭につける必要があります。
AA = Module13.SUM_from1(NN)とか。
＝⇒これまでに便利なものはありましたか。
列番号を列記号に変換するという関数は便利でした。
他にも二、三あった気がします。
＝⇒サブルーチンとはどこが違うのですか。
大仰ではないと点でしょうか。もちろんサブルーチン(SUBという位置づけ、変数はパブリック変数、モジュール変数とすることが必要)でも可能です。処理結果が複数の場合は、FUNCTIONではできません。
メリットは、セルに書き込めるという点ではないでしょうか。つまり、通常のエクセル処理の中で処理可能という点でしょう。
＝⇒今回紹介してくれるものは、具体的にはどんなものですか。
条件付SUMPRO関数です。SUMPRODUCT関数を思い浮かべてください。
年齢(20-60歳)、人数、給料(平均)があります。
40歳から60歳までの給料の総額を求めるのは、通常SUMPRODUCT関数を使いますね。
このとき、範囲を自由に与えるということです。
イメージとしては、
SUMPRODUCT(bNN:b60,cNN:c60) ということでしょうか。
この場合、40歳を他から与えれば、SUMPRODUCT(b40:b60,c40:c60) ということになります。
SUMPRODUCT関数は2項目必要となりますが、1項目の指定であった場合は、SUM関数の機能とします。
＝⇒もっとイメージを得たいと思います。パラメータとして何を与えるのでしょうか。
パラメータは、"D18,D19,A20,60,B20,C21"と与えます。
この内容をセルに書き込んでおき、そのセルを参照することでも可能です。
このパラメータは次のことを意味します。
'D18のセルにある年齢から
D19のセルにある年齢までについて
B20からはじまるデータセットの中のセルと
C21からはじまるデータセットの中のセルとを掛け合わせた総和を求める。
(参照すべき年齢は、A20からA60にはいっている)
＝⇒4項目目の60は何を意味しているのですか。A60とはどんな関係があるのですか
これは、データセットの範囲を示すもので、行数を示します。
データセットは、A20から始まって、A60まであるということです。
A60の60と60が同じですが、これは、ある年齢から最後までの積和を求めるということを意味します。
＝⇒B20とC21が違うのはどういうことですか
第一項目目は基準年齢と行があっているはずですが、第二項目目はそうとも限りません。そこで第一項目と第二項目の先頭の位置が変わっている場合も可能ということです。通常は一緒です。
また、パラメータの別の与え方として、
"D18,60,A20,60,B20,C21" も可能としました。
2項目目の60は、この例では60歳を表しています。
ここまでのことを総合して、おおざっぱにいえば、指定された2つのセルに40、60と入れれば、40から60歳までの給料の総額が出るし、30、55と入れれば30から55歳までの給料の総額が出るということです。それぞれの場合の人数を出したかったら、一項目のみの指定をすればいいのです(すなわち、"D18,D19,A20,60,B20,")。
問題点
参照セルの内容が変わっても、自動計算されない。
＝⇒
先頭に
Application.Volatile を入れることで自動計算されます。
Volatileメソッド 【注：WebMaster】
ユーザー定義関数を自動再計算関数にする。この関数にしておくと、セルで計算が行われるたびに再計算される。
構文
expression.Volatile(Volatile)
expression Applicationオブジェクトを表すオブジェクト式を必ず指定する
Volatile True ： 自動再計算関数（規定値）、 False ：非自動再計算関数
ユーザー定義関数の中（冒頭）に記述する。
【参照先】
http://www.asahi-net.or.jp/~ZN3Y-NGI/YNxv926.html
中身はこんなもので、関数としては動きますが Excel2000で動かすと、再計算ができないんです。
Micrsoftサポートには、「ユーザー定義関数内で名前作成した場合の再計算について」という題で、index関数を使うとできないみたいな感じなのですが、何かいい方法はないでしょうか？
Answer 2001.6.6 ムラジロ～
中身は見ていませんが
Application.Volatile
を頭に入れてもダメでしょうか？
できました
ムラジロ～さんありがとうございました。もっと勉強します。
2010年4月10日土曜日
第141回目 列･行の操作、若干の余興も含む(その3)
○第141回目 列･行の操作、若干の余興も含む(その3)
列･行の並べ替えです。
こういう事例があります。
すでに、年度別(3年度)にみたグループ別(3グループで合計を入れて4グループ)の数値を列記した表があります。これらは横に展開されていて、下には項目(20項目ほど)が展開されています。
これはそのままとして、これから、グループ別にみた年度別の表にしたというニーズです。
16年度 …
A B C 合計 …
↓
A …
16 17 18年度 …
このような事例を一瞬(パラメータの設定を除く)で処理してしまおうというものです。
パラメータの設定はそれほど難しくありません。表の上及び左に行、列をつくり、そこに順番を示すパラメータを入れるのです。入らない列は並べ替えの対象外です。
(記述を簡単にするために列の並べ替えを前提とします)
眼目は二つ。
一つ目は、今回は2列ではないので、並べ替えたい列の計算式情報をすべて配列変数に格納します。
二つ目は、パラメータを並べ替えることです。
一つ目は前回の機能を複数繰り返すことになりますので、問題はありません。ただし、複数列対象なので、配列変数を二次元のものにしてください。パラメータが入っている列のみを対象として、計算式情報を取得していきます。
パラメータを含めた表全体を指定してから実行
1.指定範囲の場所情報を取得する
2.列のパラメータを取得する。1以上99999以下を対象とする(文字が入っている列は対象外)
3.パラメータをソートする
4.一番目のパラメータを持つ列を探し、その列の計算式情報を配列変数の一番目に格納する。
二番目以降繰り返す。
5.物理にみて最初の並べ替え対象列に、配列変数の一番目の計算式情報を移植する。
二番目以降繰り返す。
(6.行でも同じことを繰り返す)
2.列のパラメータを取得する。
1から99999までの数値が入っている列を対象とします。
対象となる列数(retu_cnt9)を求めます。
If 0 <>
ww(j) = Cells(gyo1, retu1 + i)
j = j + 1
End If
3.パラメータをソートする
パラメータはww(i)に入っています。
対象列の数は、retu_cnt9となります。
4.一番目のパラメータを持つ列を探し、その列の計算式情報を配列変数の一番目に格納する。二番目以降繰り返す。
For i = 1 To retu_cnt9 '処理対象列数だけ繰り替えす
For j = 0 To retu_cnt - 1 'i番目に該当する列かどうか
If Cells(gyo1, retu1 + j) = ww(i) Then Exit For
Next
For k = 1 To gyo_cnt - 1 '処理対象列の計算式情報を取得する
shiki(i, k) = Cells(gyo1 + k, retu1 + j).Formula
Next
Next i
5.物理にみて最初の並べ替え対象列に、配列変数の一番目の計算式情報を移植する。
二番目以降繰り返す。
j = 1
For i = 0 To retu_cnt - 1 '表の列数分繰り返す
If 0 <>
For k = 1 To gyo_cnt - 1 '計算式情報の移植
Cells(gyo1 + k, retu1 + i) = shiki(j, k)
Next
j = j + 1
End If
Next
同様に行についても処理してください。直列に処理すればいいです。
上記の例では、次のようにパラメータを設定します。
11 21 31 41 12 22 32 42 13 23 33 43
16年度 17年度 18年度
A B C 合計 A B C 合計 A B C 合計
見出しが結合セルになっている場合は、結合を解除してから処理してください。そのほうが無難です。
見出し等の修正は処理後にしてください。
【処理直後のイメージ】
16年度17年度18年度
A A A B B B C C C 合計 合計 合計
※B以降の年度見出しはもともとないので、でてこないと思います。
見出し部分に関して行の並べ替えのパラメータを入れておくと、その後の処理が一手間なくなります。
A A A B B B C C C 合計 合計 合計
16年度17年度18年度
となります。
注意点
このマクロは指定したエリアのセルを移動させてしまいますので、そのエリアの中のセルを参照する計算式がおかしくなって、意図しない計算結果となってしまいます。
表の中での計算式、表の外での計算式で、表の中のセルを参照しているものについては、あとで修正する必要がありますので注意してください。
上の例では、移動後の合計の計算式は、表内のセルを使用していると当てになりません。
列･行の並べ替えです。
こういう事例があります。
すでに、年度別(3年度)にみたグループ別(3グループで合計を入れて4グループ)の数値を列記した表があります。これらは横に展開されていて、下には項目(20項目ほど)が展開されています。
これはそのままとして、これから、グループ別にみた年度別の表にしたというニーズです。
16年度 …
A B C 合計 …
↓
A …
16 17 18年度 …
このような事例を一瞬(パラメータの設定を除く)で処理してしまおうというものです。
パラメータの設定はそれほど難しくありません。表の上及び左に行、列をつくり、そこに順番を示すパラメータを入れるのです。入らない列は並べ替えの対象外です。
(記述を簡単にするために列の並べ替えを前提とします)
眼目は二つ。
一つ目は、今回は2列ではないので、並べ替えたい列の計算式情報をすべて配列変数に格納します。
二つ目は、パラメータを並べ替えることです。
一つ目は前回の機能を複数繰り返すことになりますので、問題はありません。ただし、複数列対象なので、配列変数を二次元のものにしてください。パラメータが入っている列のみを対象として、計算式情報を取得していきます。
パラメータを含めた表全体を指定してから実行
1.指定範囲の場所情報を取得する
2.列のパラメータを取得する。1以上99999以下を対象とする(文字が入っている列は対象外)
3.パラメータをソートする
4.一番目のパラメータを持つ列を探し、その列の計算式情報を配列変数の一番目に格納する。
二番目以降繰り返す。
5.物理にみて最初の並べ替え対象列に、配列変数の一番目の計算式情報を移植する。
二番目以降繰り返す。
(6.行でも同じことを繰り返す)
2.列のパラメータを取得する。
1から99999までの数値が入っている列を対象とします。
対象となる列数(retu_cnt9)を求めます。
If 0 <>
ww(j) = Cells(gyo1, retu1 + i)
j = j + 1
End If
3.パラメータをソートする
パラメータはww(i)に入っています。
対象列の数は、retu_cnt9となります。
4.一番目のパラメータを持つ列を探し、その列の計算式情報を配列変数の一番目に格納する。二番目以降繰り返す。
For i = 1 To retu_cnt9 '処理対象列数だけ繰り替えす
For j = 0 To retu_cnt - 1 'i番目に該当する列かどうか
If Cells(gyo1, retu1 + j) = ww(i) Then Exit For
Next
For k = 1 To gyo_cnt - 1 '処理対象列の計算式情報を取得する
shiki(i, k) = Cells(gyo1 + k, retu1 + j).Formula
Next
Next i
5.物理にみて最初の並べ替え対象列に、配列変数の一番目の計算式情報を移植する。
二番目以降繰り返す。
j = 1
For i = 0 To retu_cnt - 1 '表の列数分繰り返す
If 0 <>
For k = 1 To gyo_cnt - 1 '計算式情報の移植
Cells(gyo1 + k, retu1 + i) = shiki(j, k)
Next
j = j + 1
End If
Next
同様に行についても処理してください。直列に処理すればいいです。
上記の例では、次のようにパラメータを設定します。
11 21 31 41 12 22 32 42 13 23 33 43
16年度 17年度 18年度
A B C 合計 A B C 合計 A B C 合計
見出しが結合セルになっている場合は、結合を解除してから処理してください。そのほうが無難です。
見出し等の修正は処理後にしてください。
【処理直後のイメージ】
16年度17年度18年度
A A A B B B C C C 合計 合計 合計
※B以降の年度見出しはもともとないので、でてこないと思います。
見出し部分に関して行の並べ替えのパラメータを入れておくと、その後の処理が一手間なくなります。
A A A B B B C C C 合計 合計 合計
16年度17年度18年度
となります。
注意点
このマクロは指定したエリアのセルを移動させてしまいますので、そのエリアの中のセルを参照する計算式がおかしくなって、意図しない計算結果となってしまいます。
表の中での計算式、表の外での計算式で、表の中のセルを参照しているものについては、あとで修正する必要がありますので注意してください。
上の例では、移動後の合計の計算式は、表内のセルを使用していると当てになりません。
2010年4月3日土曜日
第140回目 列･行の操作、若干の余興も含む(その2)
○第140回目 列･行の操作、若干の余興も含む(その2)
比較的簡単な、列･行の逆転をやります。
記述が面倒になりますので、用語として、"列･行"を"列"で代表させます。
このマクロは範囲を指定してからはじめます。その範囲とは、逆転したい範囲です。つまり、2列Ｎ行となります。一列だけ指定して、その右隣の列を逆転させるという指示の仕方もありますが、自分の意思で逆転したい範囲を明示したほうがいいと思います。
2列2行の場合は、ダミー行を挿入して3行にするなど、工夫してください。
流れです。どちらが2であるかで方向を判断します。
1.指定範囲の座標情報を取得する。逆転の方向は指定範囲の数が2のほうをとる。
2.最初の1列目から、計算式情報(ない場合は数値情報)を配列変数に格納します。
3.2列目の計算式情報を1列目に移します。
4.格納してある配列変数の内容を、2列目に移します。
眼目は計算式情報を取得して移植するところにあります。
２.の部分ですが、次の記述で、計算式がない場合は値が取り込まれます。
ww(i) = Cells(gyo1 + i * yoko, retu1 + i * tate).Formula
列の逆転という前提ですので、yokoとtateには、それぞれ1と0が入っています。
3.の部分です。
Cells(gyo1 + i * yoko, retu1 + i * tate) = Cells(gyo1 + i * yoko + tate, retu1 + i * tate + yoko).Formula
入れ先は計算式の指定をしなくても大丈夫でした。
ここでの"+ tate"と"+ yoko"の使い方は邪道でしょうね。
4.の部分です。
Cells(gyo1 + i * yoko + tate, retu1 + i * tate + yoko) = ww(i)
なお、iは0から始まっています。
＝⇒これを使う事例はどのくらいあるのでしょうか。
・エクセルで表を作り、一通りできた後で、よくよく考えてみれば、この部分を逆にしておいたほうが体裁がいいな、とか、体系が崩れているのここを逆にしたいな、ということはあります。問題の頻度は…、それなりにあるかもしれない、といった程度でしょうか。
複雑な計算式が絡まない場合であれば、通常の操作で気にしないでやっているのではないでしょうか。複雑な計算式が絡むとそうはいきません。多分、あきらめてしまう場合もあるのではないでしょうか。それゆえ記憶に残らないという場合もあると思います。
実は、列の逆転は、既存のマクロで可能なのです。すなわち、計算式を文字化してコピーするという機能を使うのです。でもこのマクロのほうがはるかにすっきりいきます。
一時的にどこかに計算式を移植しておいて、該当する箇所に計算式を移植していけば、よいのです。
比較的簡単な、列･行の逆転をやります。
記述が面倒になりますので、用語として、"列･行"を"列"で代表させます。
このマクロは範囲を指定してからはじめます。その範囲とは、逆転したい範囲です。つまり、2列Ｎ行となります。一列だけ指定して、その右隣の列を逆転させるという指示の仕方もありますが、自分の意思で逆転したい範囲を明示したほうがいいと思います。
2列2行の場合は、ダミー行を挿入して3行にするなど、工夫してください。
流れです。どちらが2であるかで方向を判断します。
1.指定範囲の座標情報を取得する。逆転の方向は指定範囲の数が2のほうをとる。
2.最初の1列目から、計算式情報(ない場合は数値情報)を配列変数に格納します。
3.2列目の計算式情報を1列目に移します。
4.格納してある配列変数の内容を、2列目に移します。
眼目は計算式情報を取得して移植するところにあります。
２.の部分ですが、次の記述で、計算式がない場合は値が取り込まれます。
ww(i) = Cells(gyo1 + i * yoko, retu1 + i * tate).Formula
列の逆転という前提ですので、yokoとtateには、それぞれ1と0が入っています。
3.の部分です。
Cells(gyo1 + i * yoko, retu1 + i * tate) = Cells(gyo1 + i * yoko + tate, retu1 + i * tate + yoko).Formula
入れ先は計算式の指定をしなくても大丈夫でした。
ここでの"+ tate"と"+ yoko"の使い方は邪道でしょうね。
4.の部分です。
Cells(gyo1 + i * yoko + tate, retu1 + i * tate + yoko) = ww(i)
なお、iは0から始まっています。
＝⇒これを使う事例はどのくらいあるのでしょうか。
・エクセルで表を作り、一通りできた後で、よくよく考えてみれば、この部分を逆にしておいたほうが体裁がいいな、とか、体系が崩れているのここを逆にしたいな、ということはあります。問題の頻度は…、それなりにあるかもしれない、といった程度でしょうか。
複雑な計算式が絡まない場合であれば、通常の操作で気にしないでやっているのではないでしょうか。複雑な計算式が絡むとそうはいきません。多分、あきらめてしまう場合もあるのではないでしょうか。それゆえ記憶に残らないという場合もあると思います。
実は、列の逆転は、既存のマクロで可能なのです。すなわち、計算式を文字化してコピーするという機能を使うのです。でもこのマクロのほうがはるかにすっきりいきます。
一時的にどこかに計算式を移植しておいて、該当する箇所に計算式を移植していけば、よいのです。
2010年3月27日土曜日
第139回目 列･行の操作、若干の余興も含む(その1)
○第139回目 列･行の操作、若干の余興も含む(その1)
列･行の操作とは何でしょうか。
列･行という言葉の中には、表の中での列･行という意味も含んでいます。違いは、シートの中の列･行と表の中の列･行ということになります。
列･行の操作ですから、まずは削除･追加ということが思い浮かびます。
さらに、このテーマでは、列･行の逆転及び並べ替えということを扱います。
列･行の逆転って…
左右の列･上下の行を入れ替えるということです。
並べ替えって…
指定する順番に列･行を入れ替えてしまうことです。
＝⇒とすると、逆転はこれに含まれるのではないでしょうか。
・そのとおりです。操作のスムーズさの点で分けべきかどうかを考えてください。
＝⇒ちょっと待った。逆転でも並べ替えでもいいのですが、計算式はどうなるのですか。
・ニーズとしては、逆転でも並べ替えでも、元にあった数値がそのままの値で移動するということが第一ではないでしょうか。ですから、計算式もまったく変わらずに移動します。
＝⇒既存のコマンドではそういうのはないのですか。
・調べてみましが、ありませんでした。調べ方が足らないかもしれません。
＝⇒通常の操作との差は。メリットはいかに。
・列･行の削除･追加は、もしかしたら通常の操作のほうがいいかもしれません。しかし、全体的に指示して一気に変えるとなるとメリットが出てくると思います。つまり、この列(または行)は削除、ここに3列追加するとか、複数にわたって指定をしてから、一気に処理する場合ということです。表の中の列･行を削除･追加するという時は、よりメリットが出てくるでしょう。
逆転･並べ替えは、その事例があるとしたら、その効果のほどは知れません。
＝⇒"この列(または行)は削除、ここに3列追加するとか、複数にわたって指定をしてから"ってどういう意味ですか。
・表の外側に余分な行と列を一つ作ります。そこに、削除は99、追加は10*追加したい数を入れていき、その部分を含めて表全体を指定して、一気に処理をするというものです。イメージはつきましたでしょうか。
＝⇒"若干の余興"とはどういう意味ですか。
・純粋な列･行の削除･追加は通常の操作が早いと思うこと、また、今回の処理が出てくる頻度がまれかもしれないことを、考えて、そのような言葉にしました。
＝⇒わかりました。では、進めてください。
列･行の削除･追加について
表の左と上に大体は空白の部分があるのでそれを利用します。ない場合は、通常の操作で追加してください。
その部分に、削除したい場合は99、その部分の前に追加したい場合は、追加したい数かける10(10の倍数)を入れてください。また、一番左端の一番上には、本当の列･行単位での処理か、表における列･行単位の処理(セル単位の処理)かの区分の入れてください。前者はなにもなし、後者は999を入れます(これはニーズによって逆でもいいです)。
処理情報を含めた表の範囲を指定した後実行します。
1.一列目、一行目の情報を取得します。
2.情報を一つ一つ見ていきながら削除･追加をします。本当の列･行の場合と表における列･行の場合で、削除･追加処理の場所情報のパラメータが変わります。
3.処理は、削除･追加で列番号等が変わってしまうので、後ろからしていきます。
今回の基本的な機能は、列･行の削除･追加です。どう書くのでしょうか。
【セル単位の場合】
行関係の削除
Range(Cells(gyo1+i,retu1), Cells(gyo1+i,retu9)).Delete Shift:=xlShiftUp
行関係の追加
Range(Cells(gyo1 + i, retu1), Cells(gyo1 + i, retu9)).Insert Shift:=xlDown
列関係の削除
Range(Cells(gyo1,retu1+i), Cells(gyo9,retu1+i)).Delete Shift:=xlShiftToLeft
列関係の追加
Range(Cells(gyo1,retu1 + i), Cells(gyo9, retu1 + i)).Insert Shift:=xlToRight
【列･行単位の場合】
行の削除
Rows(gyo1 + i).Delete
行関係の追加
Rows(gyo1 + i).EntireRow.Insert
列関係の削除
Columns(retu1 + i).Delete
列関係の追加
Columns(retu1 + i).EntireColumn.Insert
注意点は2つあります。
処理の順番としては、削除を行なって、それから追加をします。すると、追加のときに、処理対象となる列数(または行数)が変わってしまうことです。これが一つです。
これは削除した列数(または行数)をカウントして、追加処理の際に、列数(または行数)及び終わりの列(または行)を更新しておきます。
もう一つは、追加の場合の判断と追加する数の判断です。
ここでは、10の倍数を入れるとしていますので、10で割り切れるということを、追加と判断します。追加する数は、10で割った商となります。30であれば、3列(または行)追加と判断します。
セル単位か本来の列･行単位化で、ロジックは二つできますが、順番にくみ上げていけばそれほどの大変さはありません。しかし、注意事項の1に十分気をつけてください。
列･行の操作とは何でしょうか。
列･行という言葉の中には、表の中での列･行という意味も含んでいます。違いは、シートの中の列･行と表の中の列･行ということになります。
列･行の操作ですから、まずは削除･追加ということが思い浮かびます。
さらに、このテーマでは、列･行の逆転及び並べ替えということを扱います。
列･行の逆転って…
左右の列･上下の行を入れ替えるということです。
並べ替えって…
指定する順番に列･行を入れ替えてしまうことです。
＝⇒とすると、逆転はこれに含まれるのではないでしょうか。
・そのとおりです。操作のスムーズさの点で分けべきかどうかを考えてください。
＝⇒ちょっと待った。逆転でも並べ替えでもいいのですが、計算式はどうなるのですか。
・ニーズとしては、逆転でも並べ替えでも、元にあった数値がそのままの値で移動するということが第一ではないでしょうか。ですから、計算式もまったく変わらずに移動します。
＝⇒既存のコマンドではそういうのはないのですか。
・調べてみましが、ありませんでした。調べ方が足らないかもしれません。
＝⇒通常の操作との差は。メリットはいかに。
・列･行の削除･追加は、もしかしたら通常の操作のほうがいいかもしれません。しかし、全体的に指示して一気に変えるとなるとメリットが出てくると思います。つまり、この列(または行)は削除、ここに3列追加するとか、複数にわたって指定をしてから、一気に処理する場合ということです。表の中の列･行を削除･追加するという時は、よりメリットが出てくるでしょう。
逆転･並べ替えは、その事例があるとしたら、その効果のほどは知れません。
＝⇒"この列(または行)は削除、ここに3列追加するとか、複数にわたって指定をしてから"ってどういう意味ですか。
・表の外側に余分な行と列を一つ作ります。そこに、削除は99、追加は10*追加したい数を入れていき、その部分を含めて表全体を指定して、一気に処理をするというものです。イメージはつきましたでしょうか。
＝⇒"若干の余興"とはどういう意味ですか。
・純粋な列･行の削除･追加は通常の操作が早いと思うこと、また、今回の処理が出てくる頻度がまれかもしれないことを、考えて、そのような言葉にしました。
＝⇒わかりました。では、進めてください。
列･行の削除･追加について
表の左と上に大体は空白の部分があるのでそれを利用します。ない場合は、通常の操作で追加してください。
その部分に、削除したい場合は99、その部分の前に追加したい場合は、追加したい数かける10(10の倍数)を入れてください。また、一番左端の一番上には、本当の列･行単位での処理か、表における列･行単位の処理(セル単位の処理)かの区分の入れてください。前者はなにもなし、後者は999を入れます(これはニーズによって逆でもいいです)。
処理情報を含めた表の範囲を指定した後実行します。
1.一列目、一行目の情報を取得します。
2.情報を一つ一つ見ていきながら削除･追加をします。本当の列･行の場合と表における列･行の場合で、削除･追加処理の場所情報のパラメータが変わります。
3.処理は、削除･追加で列番号等が変わってしまうので、後ろからしていきます。
今回の基本的な機能は、列･行の削除･追加です。どう書くのでしょうか。
【セル単位の場合】
行関係の削除
Range(Cells(gyo1+i,retu1), Cells(gyo1+i,retu9)).Delete Shift:=xlShiftUp
行関係の追加
Range(Cells(gyo1 + i, retu1), Cells(gyo1 + i, retu9)).Insert Shift:=xlDown
列関係の削除
Range(Cells(gyo1,retu1+i), Cells(gyo9,retu1+i)).Delete Shift:=xlShiftToLeft
列関係の追加
Range(Cells(gyo1,retu1 + i), Cells(gyo9, retu1 + i)).Insert Shift:=xlToRight
【列･行単位の場合】
行の削除
Rows(gyo1 + i).Delete
行関係の追加
Rows(gyo1 + i).EntireRow.Insert
列関係の削除
Columns(retu1 + i).Delete
列関係の追加
Columns(retu1 + i).EntireColumn.Insert
注意点は2つあります。
処理の順番としては、削除を行なって、それから追加をします。すると、追加のときに、処理対象となる列数(または行数)が変わってしまうことです。これが一つです。
これは削除した列数(または行数)をカウントして、追加処理の際に、列数(または行数)及び終わりの列(または行)を更新しておきます。
もう一つは、追加の場合の判断と追加する数の判断です。
ここでは、10の倍数を入れるとしていますので、10で割り切れるということを、追加と判断します。追加する数は、10で割った商となります。30であれば、3列(または行)追加と判断します。
セル単位か本来の列･行単位化で、ロジックは二つできますが、順番にくみ上げていけばそれほどの大変さはありません。しかし、注意事項の1に十分気をつけてください。
2010年3月21日日曜日
第138回目 複数個所を一箇所にコピーする(その2)
○第138回目 複数個所を一箇所にコピーする(その2)
今回は指定された範囲のデータを貼付けるという処理です。
場所の3情報(プラス、それぞれの場所の列数と場所の数)はすでにパブリック変数にあります。
今回の眼目はコピー･ペーストの繰り返しです。
コピーもとの場所は独立していますが、貼付け先の場所は順次移動していきます。データ、一列あき、データ、一列あき…という形式で貼付けましょう。
今回のマクロの実行前には、貼付ける先の座標にカーソルがあるという前提です。
貼付先の座標は、"コピーもとの列数+1"で移動していきますので、ちょっと気をつけてください。
貼付け処理のメイン部分は次のようになります。
mm = 0
'貼付け処理
For i = 1 To p_m_cnt
Windows(p_m_bkname(i)).Activate
Sheets(p_m_shname(i)).Select
Range(p_m_xy(i)).Copy
Windows(bkname9).Activate
Sheets(shname9).Select
Cells(gyo1, retu1 + mm).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
mm = mm + p_r_cnt(i) + 1 '間に一列あけるため
Next
貼付けた後の印刷は、すでにできている"範囲印刷"となります。
前に、"計算式の中にある場所に飛ぶ"というのがありました(場所とは計算式の中に記述されている座標ということです)。
これに合体させることを考えるのがいいと思います。
場所に飛んでいったら、その周囲のデータを取得するかどうか、取得する場合はその範囲の座標を指定します。
情報が得られたら、今回の貼付けマクロです。
これでデバックがやりやすくなること請け合いです。
今回は指定された範囲のデータを貼付けるという処理です。
場所の3情報(プラス、それぞれの場所の列数と場所の数)はすでにパブリック変数にあります。
今回の眼目はコピー･ペーストの繰り返しです。
コピーもとの場所は独立していますが、貼付け先の場所は順次移動していきます。データ、一列あき、データ、一列あき…という形式で貼付けましょう。
今回のマクロの実行前には、貼付ける先の座標にカーソルがあるという前提です。
貼付先の座標は、"コピーもとの列数+1"で移動していきますので、ちょっと気をつけてください。
貼付け処理のメイン部分は次のようになります。
mm = 0
'貼付け処理
For i = 1 To p_m_cnt
Windows(p_m_bkname(i)).Activate
Sheets(p_m_shname(i)).Select
Range(p_m_xy(i)).Copy
Windows(bkname9).Activate
Sheets(shname9).Select
Cells(gyo1, retu1 + mm).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
mm = mm + p_r_cnt(i) + 1 '間に一列あけるため
Next
貼付けた後の印刷は、すでにできている"範囲印刷"となります。
前に、"計算式の中にある場所に飛ぶ"というのがありました(場所とは計算式の中に記述されている座標ということです)。
これに合体させることを考えるのがいいと思います。
場所に飛んでいったら、その周囲のデータを取得するかどうか、取得する場合はその範囲の座標を指定します。
情報が得られたら、今回の貼付けマクロです。
これでデバックがやりやすくなること請け合いです。
2010年3月6日土曜日
第137回目 複数個所を一箇所にコピーする(その1)
○第137回目 複数個所を一箇所にコピーする(その1)
コピーというのは、コピー元と貼付け先の二つが必要です。
コピーをしているとき、こことあそこと、それらをまとめて貼付けたいということがあると思います。
つまり、あるシートのここと、違うシートのあそこと、また違うシートのあそこを、一つの場所に貼り付けたい、というニーズです。これは、計算式などをデバックするときによく使います。
通常は、
1.該当箇所の大事な数字をメモしてからデバックする。
2.該当箇所をそれぞれプリントして、それらをつき合わせながらデバックする。
ということではないでしょうか。言うでもなく簡単なものは、外にださず、すべて自分の頭の中の記憶で大丈夫です。
ある程度にいくと、数値を書き出す、印刷するということが必要となってきます。
このためのものと考えていいでしょう。
一覧表になるので(必ずしもそうではないが)、その後の作業が非常に楽になります。
1.(最初の場所を指定してから実行)指定された場所の情報を取得する。
2.次の場所に移動し、範囲を指定する。
この繰り返し。
次の場所とは、ブック、シートの移動を含みます。
場所の情報とは、ブック名、シート名、範囲の座標の三つとなります。なお、貼付けの際に必要になるので、それぞれの列数をも取得しておきます。
ブックの移動、シートの移動、範囲の指定はこれまで何回もやってきたものですので、そのブロックをコピーしてつなぎ合わせるとできてしまいます(実際は、"つなぎ合わせ"というのが難しいのですが…)。
違うマクロで使うこととなるために、パブリック変数にしておいてください。さらに個数も取得しておいてください。
パブリック変数名
p_m_bkname(i)
p_m_shname(i)
p_m_xy(i) 範囲の座標
p_r_cnt(i) 範囲の列数
p_m_cnt 指定した範囲の数
次に、別のマクロで、3情報(ブック名、シート名、座標)をセルに書き込んで表示させてみてください。
また、別のマクロとすることになるため、セルに書き込んだ3情報を取得してパブリック変数に格納してみてください。
これらをまとめると、
1.場所を指定し場所の3情報を取得する
2.場所の3情報をセルに表示する
3.セルに書き込まれている3情報を取得する
ということになります。
このうち、1.はブックの移動、シートの移動と機能が多いのでちょっと大変ですね。
このあとは、取得した3情報を元に、それらの情報を一箇所に貼付けます。
コピーというのは、コピー元と貼付け先の二つが必要です。
コピーをしているとき、こことあそこと、それらをまとめて貼付けたいということがあると思います。
つまり、あるシートのここと、違うシートのあそこと、また違うシートのあそこを、一つの場所に貼り付けたい、というニーズです。これは、計算式などをデバックするときによく使います。
通常は、
1.該当箇所の大事な数字をメモしてからデバックする。
2.該当箇所をそれぞれプリントして、それらをつき合わせながらデバックする。
ということではないでしょうか。言うでもなく簡単なものは、外にださず、すべて自分の頭の中の記憶で大丈夫です。
ある程度にいくと、数値を書き出す、印刷するということが必要となってきます。
このためのものと考えていいでしょう。
一覧表になるので(必ずしもそうではないが)、その後の作業が非常に楽になります。
1.(最初の場所を指定してから実行)指定された場所の情報を取得する。
2.次の場所に移動し、範囲を指定する。
この繰り返し。
次の場所とは、ブック、シートの移動を含みます。
場所の情報とは、ブック名、シート名、範囲の座標の三つとなります。なお、貼付けの際に必要になるので、それぞれの列数をも取得しておきます。
ブックの移動、シートの移動、範囲の指定はこれまで何回もやってきたものですので、そのブロックをコピーしてつなぎ合わせるとできてしまいます(実際は、"つなぎ合わせ"というのが難しいのですが…)。
違うマクロで使うこととなるために、パブリック変数にしておいてください。さらに個数も取得しておいてください。
パブリック変数名
p_m_bkname(i)
p_m_shname(i)
p_m_xy(i) 範囲の座標
p_r_cnt(i) 範囲の列数
p_m_cnt 指定した範囲の数
次に、別のマクロで、3情報(ブック名、シート名、座標)をセルに書き込んで表示させてみてください。
また、別のマクロとすることになるため、セルに書き込んだ3情報を取得してパブリック変数に格納してみてください。
これらをまとめると、
1.場所を指定し場所の3情報を取得する
2.場所の3情報をセルに表示する
3.セルに書き込まれている3情報を取得する
ということになります。
このうち、1.はブックの移動、シートの移動と機能が多いのでちょっと大変ですね。
このあとは、取得した3情報を元に、それらの情報を一箇所に貼付けます。
2010年2月20日土曜日
第136回目 計算式を複数回コピー
○第136回目 計算式を複数回コピー
複数コピーはもうでてきました。その際にもう一つ、シート間のコピーもありました。これを合体させたものです。
シート数が多くなる複雑なエクセルの場合、あるシートのその1、その2…という形式になるのがあります。その場合、シート内の計算式式はほとんど同じ可能性が高くなります。
ある計算式をつくりました。それをシート内で複数回コピーします。また、複数シートにもコピーします。これを一気に行なうマクロです。
これが意外と気持ちがいいです。
既存のマクロを使うと2つのマクロによる処理になります。かつ、シート間は大ブロックでのコピーとなります。つまり不要なものまでをも含めてコピーすることになる可能性がでてきます。
大体は大丈夫なのですが、不要なもののコピーがでてくると、操作が簡単にはいかなくなります。(データを入れていない時は、全面的なコピーでいいのですが、いったんデータを入れてしまうと、そのデータの部分はコピーしたくないということが起こります)
これを一気に解消。1回の操作で完了です。
必要なもののみを必要な場所にコピーします。
コピー元を指定して実行です。
1.指定範囲の情報を取得
2.はじめに貼付けたい先頭のセルを指定(シート間を考えると、通常はコピーもとの先頭を指定することになる)
S 3.次に貼付けたい先頭のセルの指定
4.貼付け回数を指定
5.貼付けたいシート数を指定(現在のシートを含みます)
シート内のコピーのループの外側に、シートを動かすというループを入れればよいのです。とても簡単です。
何で、最初からこの機能にしなかったのでしょうかね。
最初の段階では考える余裕がなかった。
通常の操作をベースにしていたので、気がつかなかった。
少しでも前進すれば満足であった。
このコピーでは、計算式のみをコピーするものですが、それとは別に全部コピーのものを作っておくといいと思います。このマクロも簡単です。ペースト条件を変えればいいだけです。一つのマクロで中で調整してもいいでしょう。
両方同じ頻度であればいいのですが、一方に偏っていると、かえって指定が面倒になってきます。
いい方法がありました。パブリック変数を持って、通常は計算式のみという指示にしておいて、全部コピーの指定をした場合、そのパブリック変数を変えるのです。するとマクロは一つですみ、最初の入り口の操作だけで可能となります。処理後パブリック変数をクリアしておいてください。
通常は入り口が同一モジュールにあるので、モジュール変数でも大丈夫ですね。
質問があります。前回を含めて、コピーの方向はどう判断するのでしょうか。
＝⇒欠落していました。コピーの方向は自動的に判定しています。
今回の場合は、最初に指定されたセルと次に指定されたセルの行情報と列情報によって判定します。行が同じであれば、横方向のコピー、列が同じであれば縦方向のコピーです。でもこのロジックには穴があります。行･列とも同じでなかったらどうなるということです。この指定が有効の場合は別物となりますので、今回は決め打ちして判定してください。
前回の場合も同様です。
ところで、行も列も異なる場合というのはどういう事例なんでしょうね。
まっすぐにあるものを斜めにコピーする。
斜めにあるものをまっすぐにコピーする。これは計算式が違ってしまいますので文字化して移植でしょう。
何のために？ まっすぐで考えると…。
まっすぐなものを斜めにしたい。まっすぐなものを桂馬飛びにしたい。
次に、斜めにある値をまっすぐにとりたいケース。すなわち、斜めに並んでいる同一コーホートを見やすいようにまっすぐに表示したい、という感じですね。(この場合は、コピーもとの指定に一工夫が必要です。また、コピーなのか移植なのかを十分に考える必要がりますね。)
コピー機能ではなく移植機能の場合は、昔やった、セルの階段挿入の目的と同じですね。
セルの挿入よりは、こちらのほうがスマートですね。
まずは、コピー先の斜めコピーを考えたらどうでしょうか。
【頭の体操】
b30=sum(b3:b$29)
c30=sum(c3:c$29)
d30=sum(d3:d$29)
e30=sum(e3:e$29)
という計算式がありました。
これを、次のような計算式にします。
b30=sum(b3:b$29)
c30=sum(c4:c$29)
d30=sum(d5:d$29)
e30=sum(e6:e$29)
1.まずもとの計算式群を、下3セルの範囲にコピーします。
すると計算式は次のとおりとなります。
b31=sum(b4:b$29)
c31=sum(c4:c$29) ＝⇒○
d31=sum(d4:d$29)
e31=sum(e4:e$29)
b32=sum(b5:b$29)
c32=sum(c5:c$29)
d32=sum(d5:d$29) ＝⇒○
e32=sum(e5:e$29)
b33=sum(b6:b$29)
c33=sum(c6:c$29)
d33=sum(d6:d$29)
e33=sum(e6:e$29) ＝⇒○
○のところが求める計算式となります。
これらの箇所を一行にする必要がありますが、どうやったらいいのでしょうか。
これを行うための機能は移植となりますが、移植もとの2セルをそれぞれ指定する。そして移植先のセルを2つ、それぞれ指定することで、お互いのセルの関係が分かりますので、後は移植回数を指定すればできそうです。
ただし、移植もとの指定が一つ多くなるので、通常の場合は、操作がめんどくさくなっていまいます。
ニーズがどれほどあるかですが、やめておいたほうがいいでしょうね。この機能は別の形で対応したほうがいいかもしれません。
ちょっとした頭の体操でした。
複数コピーはもうでてきました。その際にもう一つ、シート間のコピーもありました。これを合体させたものです。
シート数が多くなる複雑なエクセルの場合、あるシートのその1、その2…という形式になるのがあります。その場合、シート内の計算式式はほとんど同じ可能性が高くなります。
ある計算式をつくりました。それをシート内で複数回コピーします。また、複数シートにもコピーします。これを一気に行なうマクロです。
これが意外と気持ちがいいです。
既存のマクロを使うと2つのマクロによる処理になります。かつ、シート間は大ブロックでのコピーとなります。つまり不要なものまでをも含めてコピーすることになる可能性がでてきます。
大体は大丈夫なのですが、不要なもののコピーがでてくると、操作が簡単にはいかなくなります。(データを入れていない時は、全面的なコピーでいいのですが、いったんデータを入れてしまうと、そのデータの部分はコピーしたくないということが起こります)
これを一気に解消。1回の操作で完了です。
必要なもののみを必要な場所にコピーします。
コピー元を指定して実行です。
1.指定範囲の情報を取得
2.はじめに貼付けたい先頭のセルを指定(シート間を考えると、通常はコピーもとの先頭を指定することになる)
S 3.次に貼付けたい先頭のセルの指定
4.貼付け回数を指定
5.貼付けたいシート数を指定(現在のシートを含みます)
シート内のコピーのループの外側に、シートを動かすというループを入れればよいのです。とても簡単です。
何で、最初からこの機能にしなかったのでしょうかね。
最初の段階では考える余裕がなかった。
通常の操作をベースにしていたので、気がつかなかった。
少しでも前進すれば満足であった。
このコピーでは、計算式のみをコピーするものですが、それとは別に全部コピーのものを作っておくといいと思います。このマクロも簡単です。ペースト条件を変えればいいだけです。一つのマクロで中で調整してもいいでしょう。
両方同じ頻度であればいいのですが、一方に偏っていると、かえって指定が面倒になってきます。
いい方法がありました。パブリック変数を持って、通常は計算式のみという指示にしておいて、全部コピーの指定をした場合、そのパブリック変数を変えるのです。するとマクロは一つですみ、最初の入り口の操作だけで可能となります。処理後パブリック変数をクリアしておいてください。
通常は入り口が同一モジュールにあるので、モジュール変数でも大丈夫ですね。
質問があります。前回を含めて、コピーの方向はどう判断するのでしょうか。
＝⇒欠落していました。コピーの方向は自動的に判定しています。
今回の場合は、最初に指定されたセルと次に指定されたセルの行情報と列情報によって判定します。行が同じであれば、横方向のコピー、列が同じであれば縦方向のコピーです。でもこのロジックには穴があります。行･列とも同じでなかったらどうなるということです。この指定が有効の場合は別物となりますので、今回は決め打ちして判定してください。
前回の場合も同様です。
ところで、行も列も異なる場合というのはどういう事例なんでしょうね。
まっすぐにあるものを斜めにコピーする。
斜めにあるものをまっすぐにコピーする。これは計算式が違ってしまいますので文字化して移植でしょう。
何のために？ まっすぐで考えると…。
まっすぐなものを斜めにしたい。まっすぐなものを桂馬飛びにしたい。
次に、斜めにある値をまっすぐにとりたいケース。すなわち、斜めに並んでいる同一コーホートを見やすいようにまっすぐに表示したい、という感じですね。(この場合は、コピーもとの指定に一工夫が必要です。また、コピーなのか移植なのかを十分に考える必要がりますね。)
コピー機能ではなく移植機能の場合は、昔やった、セルの階段挿入の目的と同じですね。
セルの挿入よりは、こちらのほうがスマートですね。
まずは、コピー先の斜めコピーを考えたらどうでしょうか。
【頭の体操】
b30=sum(b3:b$29)
c30=sum(c3:c$29)
d30=sum(d3:d$29)
e30=sum(e3:e$29)
という計算式がありました。
これを、次のような計算式にします。
b30=sum(b3:b$29)
c30=sum(c4:c$29)
d30=sum(d5:d$29)
e30=sum(e6:e$29)
1.まずもとの計算式群を、下3セルの範囲にコピーします。
すると計算式は次のとおりとなります。
b31=sum(b4:b$29)
c31=sum(c4:c$29) ＝⇒○
d31=sum(d4:d$29)
e31=sum(e4:e$29)
b32=sum(b5:b$29)
c32=sum(c5:c$29)
d32=sum(d5:d$29) ＝⇒○
e32=sum(e5:e$29)
b33=sum(b6:b$29)
c33=sum(c6:c$29)
d33=sum(d6:d$29)
e33=sum(e6:e$29) ＝⇒○
○のところが求める計算式となります。
これらの箇所を一行にする必要がありますが、どうやったらいいのでしょうか。
これを行うための機能は移植となりますが、移植もとの2セルをそれぞれ指定する。そして移植先のセルを2つ、それぞれ指定することで、お互いのセルの関係が分かりますので、後は移植回数を指定すればできそうです。
ただし、移植もとの指定が一つ多くなるので、通常の場合は、操作がめんどくさくなっていまいます。
ニーズがどれほどあるかですが、やめておいたほうがいいでしょうね。この機能は別の形で対応したほうがいいかもしれません。
ちょっとした頭の体操でした。
2010年2月6日土曜日
第135回目 計算式を複雑形式へコピー
○第135回目 計算式を複雑形式へコピー
複雑形式といっても、単純なコピーではないといった軽い意味です。
一群の計算式を作りました。これを文字化してコピー(移植)します。
しかし、貼付け先が、単純ではありません。既存の式の間にコピーしなければなりません。例えば、一つ飛びにとか二つ飛びにとかです(間のセルには何の影響を与えられません)。
このような場合に対応するマクロです。さらに、コピー元も単につながっていると限りませんので、こちらも飛びを考慮することにします。
コピー元全体(例えば、一行複数列)を指定し、そのコピー元の中でコピーしたい計算式はいくつ飛びにあるかを指定します(通常は連続ですね)。
範囲全体の情報と飛び数を元に計算式の移植回数がわかります。
次に、貼付け先にいき、最初に貼付けるセルと次に貼り付けるセルを指定します。これで飛び数を把握します。(用途はないと思いますが、次のセルとして行列とも違う場所を指定すると、斜めに計算式が移植されます)
そして、文字化した計算式を文字化を解いて貼付けします。(実際は、計算式という内容のデータを、違うセルに代入するという操作です)。
このマクロは、今までのものを加工することによってたやすくできます。
文字化した計算式のコピーあたりが参考になります。
(第51回目 ちょっとした小物、計算式を文字化してコピー編)
流れです。
1.指定範囲の情報をとる(例 1行9列を指定)
2.コピーもとの最初のセルにカーソルを移す
3.コピーもとの次のセルを指定(例 先頭から1列空けたセルを指定。移植回数は5回となります。(9-1)/2+1＝5)
4.計算式を文字化して変数に格納する(頭に***をつけます)
5.(ブック及びシートを変えることが可能)貼付け先を指定
6.次ぎの貼付け先を指定(列の飛び数と行の飛び数が求まります)
7.計算式を移植する(コピーより移植という感覚)。文字化を解除したものを計算式として代入する(配列変数の添え字で貼付け場所を結びつける)
これが利用できる事例に出会ったら、感激のあまり目から涙が出ますよ。
もう少し改善して、もとの計算式が複数のセット(複数行複数列)になっている場合もできるようにするといいでしょう。
Ｑ.飛び飛びコピーに似ていますね。
Ａ.そうでもないんですよ。コピーの場合は計算式の式の中の座標の関係がそのまま維持されます。それではダメなケースがあります。今回のマクロは、コピーではなく移植となっていますので、計算式がそっくりそのまま貼付けられます。
Ｑ.例としてはどんなものがありますか。
Ａ.単純な例では、通常のコピーをしてしまうと、コピー先が2列先であるために列が2列ずれてしまいます。しかし、求めたいのは、1列だけ動かした計算式であるというケースはどうでしょうか。間に何もなければ単純なコピーで可能なのですが、すでに間にも計算式を作ってしまっているので、それはそのままにしておきたい。
Ｑ.それは困りますね。どうやるのですか。
Ａ.別なところに計算式を作ります。この例の場合は、一つの計算式を作っておいて、次の列にその計算式をコピーすればいいのですから、単純ですね。そこでこのマクロで、元の計算式は連続ですが、貼付け先はひとつ飛びにするという指示にすれば、計算式が移植されます。ここで、コピーではなく移植ということが大きな意味をもってくるのです。
おわかりでしょうか。
Ｑ.わかりました。自分にもそんな事例が出てくるといいですね。
複雑形式といっても、単純なコピーではないといった軽い意味です。
一群の計算式を作りました。これを文字化してコピー(移植)します。
しかし、貼付け先が、単純ではありません。既存の式の間にコピーしなければなりません。例えば、一つ飛びにとか二つ飛びにとかです(間のセルには何の影響を与えられません)。
このような場合に対応するマクロです。さらに、コピー元も単につながっていると限りませんので、こちらも飛びを考慮することにします。
コピー元全体(例えば、一行複数列)を指定し、そのコピー元の中でコピーしたい計算式はいくつ飛びにあるかを指定します(通常は連続ですね)。
範囲全体の情報と飛び数を元に計算式の移植回数がわかります。
次に、貼付け先にいき、最初に貼付けるセルと次に貼り付けるセルを指定します。これで飛び数を把握します。(用途はないと思いますが、次のセルとして行列とも違う場所を指定すると、斜めに計算式が移植されます)
そして、文字化した計算式を文字化を解いて貼付けします。(実際は、計算式という内容のデータを、違うセルに代入するという操作です)。
このマクロは、今までのものを加工することによってたやすくできます。
文字化した計算式のコピーあたりが参考になります。
(第51回目 ちょっとした小物、計算式を文字化してコピー編)
流れです。
1.指定範囲の情報をとる(例 1行9列を指定)
2.コピーもとの最初のセルにカーソルを移す
3.コピーもとの次のセルを指定(例 先頭から1列空けたセルを指定。移植回数は5回となります。(9-1)/2+1＝5)
4.計算式を文字化して変数に格納する(頭に***をつけます)
5.(ブック及びシートを変えることが可能)貼付け先を指定
6.次ぎの貼付け先を指定(列の飛び数と行の飛び数が求まります)
7.計算式を移植する(コピーより移植という感覚)。文字化を解除したものを計算式として代入する(配列変数の添え字で貼付け場所を結びつける)
これが利用できる事例に出会ったら、感激のあまり目から涙が出ますよ。
もう少し改善して、もとの計算式が複数のセット(複数行複数列)になっている場合もできるようにするといいでしょう。
Ｑ.飛び飛びコピーに似ていますね。
Ａ.そうでもないんですよ。コピーの場合は計算式の式の中の座標の関係がそのまま維持されます。それではダメなケースがあります。今回のマクロは、コピーではなく移植となっていますので、計算式がそっくりそのまま貼付けられます。
Ｑ.例としてはどんなものがありますか。
Ａ.単純な例では、通常のコピーをしてしまうと、コピー先が2列先であるために列が2列ずれてしまいます。しかし、求めたいのは、1列だけ動かした計算式であるというケースはどうでしょうか。間に何もなければ単純なコピーで可能なのですが、すでに間にも計算式を作ってしまっているので、それはそのままにしておきたい。
Ｑ.それは困りますね。どうやるのですか。
Ａ.別なところに計算式を作ります。この例の場合は、一つの計算式を作っておいて、次の列にその計算式をコピーすればいいのですから、単純ですね。そこでこのマクロで、元の計算式は連続ですが、貼付け先はひとつ飛びにするという指示にすれば、計算式が移植されます。ここで、コピーではなく移植ということが大きな意味をもってくるのです。
おわかりでしょうか。
Ｑ.わかりました。自分にもそんな事例が出てくるといいですね。
2010年1月30日土曜日
第134回目 計算式の分析2
○第134回目 計算式の分析2
第107回から113回あたりで計算式の分析と称して、主に計算式の中の項目別の値を取得して一覧表にするという機能を作りました。
これに似たもので、画面に表示するものを作ってみたらどうかということです。
複雑な計算式をチェックするの場合、その中の項目ごとの値が必要となります。それを任意に取って、画面に表示できないかというものです。記録としてまでは必要ないかも、というレベルを中心にします。もちろん後で利用可能なようにクリップボードには貼付けてください。でもこれはあくまでも、付加機能。
具体的にどういうことかをもっと詳しくしましょう。
このような計算式があったとします。
=IF(M13<>0,ROUND(O77*(G13+0.5)*12*LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)/1000,1),0)
ここに区切り記号を入れて(これは手作業。入れる場所は任意)、
=IF(【M13<>0】,ROUND(【O77】【*(G13+0.5)】*12*【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】/1000,1),0)
それを分解し、
=IF(
【M13<>0】
,ROUND(
【O77】
【*(G13+0.5)】
*12*
【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】
/1000,1),0)
【 】で囲まれた部分の計算式の結果を表示する
、というものです。
これは前回のユーザフォームのテキストボックスを使うとできます。
テキストボックス内でコピーすれば、最後の部分を
【INT(F13+G13+0.5)】
【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】
/1000,1),0)
と、二重に記述することも可能です。
あわせて元の計算式も表示します。
(注)区切り記号は、入力しやすいものにするために、//とします。
計算結果はすべての行について求めます。計算式の形式が整っていないのはエラーになりますので、そのときはブランク表示となります。
このマクロは目新しいことはアイデアだけですので、マクロを組みことに関しては、今までのものをつぎはぎすれば大丈夫です。
1.//を探し、計算式をその部分で分解する。//がなくなるまで繰り返す。最後は注意。
2.計算結果を求める。
計算結果は、使われそうもないセルに、分解した計算式を入れて求めます。計算式の形式を満たしていないものはエラーになりますので、エラーがおきたら、その計算結果はブランクだとしてください。
3.表示内容を作成する。
表示内容は、
元の計算式と改行
特別な区切り記号(**********)と改行
分解したひとつずつごとに改行し、分解した部分と計算結果
とします。
例
【INT(F13+G13+0.5)】 ＝⇒24
【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】
/1000,1),0) ＝⇒0.253
4.終了かどうかの入力
処理は一回限りとしてもいいでしょうし、連続処理としてもいいと思います。
連続処理とは、もう一度もとの計算式に戻り、区切り記号を入れていくというパターンと、これまで区切り記号を入れた結果を用いて、さらに区切り記号を入れていくというパターンとなります。戻る先が異なりますので注意してください。
めんどくさいときは、最後のパターンのみとします。また最初からマクロを動かせば、一番目のパターンとなります。
※特別な区切り記号(**********)探し、その前後で区切ることになりますが、その際、その特別な区切り記号は10文字分ですが、その後に改行がありますので、12文字分あると思ってください。特別な区切り記号がある場所+13文字分から先が、これまでの処理結果となります。これを間違えると、不可解な改行が追加されていってしまいます(簡単なマクロなのですが、この点で引っかかってしまいました。一文字として認識していました)。
第107回から113回あたりで計算式の分析と称して、主に計算式の中の項目別の値を取得して一覧表にするという機能を作りました。
これに似たもので、画面に表示するものを作ってみたらどうかということです。
複雑な計算式をチェックするの場合、その中の項目ごとの値が必要となります。それを任意に取って、画面に表示できないかというものです。記録としてまでは必要ないかも、というレベルを中心にします。もちろん後で利用可能なようにクリップボードには貼付けてください。でもこれはあくまでも、付加機能。
具体的にどういうことかをもっと詳しくしましょう。
このような計算式があったとします。
=IF(M13<>0,ROUND(O77*(G13+0.5)*12*LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)/1000,1),0)
ここに区切り記号を入れて(これは手作業。入れる場所は任意)、
=IF(【M13<>0】,ROUND(【O77】【*(G13+0.5)】*12*【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】/1000,1),0)
それを分解し、
=IF(
【M13<>0】
,ROUND(
【O77】
【*(G13+0.5)】
*12*
【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】
/1000,1),0)
【 】で囲まれた部分の計算式の結果を表示する
、というものです。
これは前回のユーザフォームのテキストボックスを使うとできます。
テキストボックス内でコピーすれば、最後の部分を
【INT(F13+G13+0.5)】
【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】
/1000,1),0)
と、二重に記述することも可能です。
あわせて元の計算式も表示します。
(注)区切り記号は、入力しやすいものにするために、//とします。
計算結果はすべての行について求めます。計算式の形式が整っていないのはエラーになりますので、そのときはブランク表示となります。
このマクロは目新しいことはアイデアだけですので、マクロを組みことに関しては、今までのものをつぎはぎすれば大丈夫です。
1.//を探し、計算式をその部分で分解する。//がなくなるまで繰り返す。最後は注意。
2.計算結果を求める。
計算結果は、使われそうもないセルに、分解した計算式を入れて求めます。計算式の形式を満たしていないものはエラーになりますので、エラーがおきたら、その計算結果はブランクだとしてください。
3.表示内容を作成する。
表示内容は、
元の計算式と改行
特別な区切り記号(**********)と改行
分解したひとつずつごとに改行し、分解した部分と計算結果
とします。
例
【INT(F13+G13+0.5)】 ＝⇒24
【LOOKUP(INT(F13+G13+0.5),○○シート!$AU$7:$AU$63,○○シート!$AV$7:$AV$63)】
/1000,1),0) ＝⇒0.253
4.終了かどうかの入力
処理は一回限りとしてもいいでしょうし、連続処理としてもいいと思います。
連続処理とは、もう一度もとの計算式に戻り、区切り記号を入れていくというパターンと、これまで区切り記号を入れた結果を用いて、さらに区切り記号を入れていくというパターンとなります。戻る先が異なりますので注意してください。
めんどくさいときは、最後のパターンのみとします。また最初からマクロを動かせば、一番目のパターンとなります。
※特別な区切り記号(**********)探し、その前後で区切ることになりますが、その際、その特別な区切り記号は10文字分ですが、その後に改行がありますので、12文字分あると思ってください。特別な区切り記号がある場所+13文字分から先が、これまでの処理結果となります。これを間違えると、不可解な改行が追加されていってしまいます(簡単なマクロなのですが、この点で引っかかってしまいました。一文字として認識していました)。
2010年1月17日日曜日
第133回目 計算式作成のお手伝い(一般的な計算式、究極モドキ3)
○第133回目 計算式作成のお手伝い(一般的な計算式、究極モドキ3)
第131回目での質問です。
□質問があります。計算式を修正する時に、計算式が長い場合は、全部が表示できないのではないでしょうか。また、文字数の制限はあるのでしょうか。
＝⇒inputboxでの制限文字数は256バイトのようです。それより長いと駄目です。また、ご指摘のとおり、表示幅が小さいため、一覧できない場合があります。
□このマクロは、複雑な計算式の場合に力を発揮するというものだと思いますが、矛盾しませんか。
表示幅の問題とプラスアルファを紹介します。
inputoboxでの表示領域を拡大できないか、という問題の対応(アイデアレベル)は、簡単でした。
inputoboxの代わりに、ユーザーフォームのテキストボックスを使えばよいのです。
ユーザーフォームのマクロとモジュールでのマクロが行き来しますので、共通の変数はパブリック変数にしてください。、
テキストボックスはできるだけ大きく設定したほうがいいでしょう。ボタンは、『続ける』と、『終了』の二つです。ここでは、文字のフォントとサイズの指定が可能です。文字を大きく、そして自分の好きな字体での表示が可能となります。
イベントは4つになります。『初期化と値のセット』、『テキストの修正があった』、『続けるボタンが押された』、『終了ボタンが押された』、です。
Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
TextBox1.MultiLine = True '折り返し表示
TextBox1.Text = p_aa '値をセットする。p_aaに計算式が入っている
End Sub
Private Sub TextBox1_Change() 'ボックス内に動きがあると動く
p_aa = TextBox1.Text 'テキストを取得
End Sub
Private Sub CommandButton1_Click()
'続けるボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 0
End Sub
Private Sub CommandButton2_Click()
'終了ボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 9
End Sub
表示するフォントに関しては、ユーザフォームを設計する画面に一緒に出てくるプロパティのFontで指定してください。
モジュール(通常のマクロを記述するところ)側では、こんな感じです。
'計算式へ文字の付加
p_aa = 複雑カッコ文の解析(keisansiki) 'この関数はカッコをランク付けして置き換えるものです。
UserForm3.Show
'ここでUserForm3のマクロに飛ぶ。そのマクロが終了すると戻る。
a = 複雑カッコをもとに戻す(p_aa) 'この関数はカッコをすべて小カッコに直すものです。
p1 = InStr(a, "=")
If p1 <> 0 Then 'イコールがあると置き換え、ないと末尾に追加とした
keisansiki = a
Else
keisansiki = keisansiki & a
End If
If p_flag = 9 Then Exit Do
プラスアルファとは、上記でいうと"複雑カッコ文の解析(keisansiki)"という部分です。 第124回でカッコの置き換えをしましたが、それをすると複雑な計算式の構造が、少しわかりやすくなります。上記の例では、この機能を関数化して利用しています。反対の処理、すべて小カッコに戻すも同様です。
【今回のポイント】
・かなり長い計算式でも一覧できる。
・複雑な構造の計算式でも、カッコをランクごとに変えて表示するので、構造がわかりやすくなる。
・字体と字の大きさが代えられるので、見やすくなる。
※今回のやり方、ユーザフォームを使って表示領域を大きくするという基本の考え方は、他にも使えるかもしれません。
Msgboxを使用すると、その表示位置がコントロールできません。大体は画面の真ん中に来るようです。どこかほかに出てきてほしいということが多々あると思います。これをユーザフォームを使うことで対応することが可能です。位置はモジュールマクロの中で記述します。
第131回目での質問です。
□質問があります。計算式を修正する時に、計算式が長い場合は、全部が表示できないのではないでしょうか。また、文字数の制限はあるのでしょうか。
＝⇒inputboxでの制限文字数は256バイトのようです。それより長いと駄目です。また、ご指摘のとおり、表示幅が小さいため、一覧できない場合があります。
□このマクロは、複雑な計算式の場合に力を発揮するというものだと思いますが、矛盾しませんか。
表示幅の問題とプラスアルファを紹介します。
inputoboxでの表示領域を拡大できないか、という問題の対応(アイデアレベル)は、簡単でした。
inputoboxの代わりに、ユーザーフォームのテキストボックスを使えばよいのです。
ユーザーフォームのマクロとモジュールでのマクロが行き来しますので、共通の変数はパブリック変数にしてください。、
テキストボックスはできるだけ大きく設定したほうがいいでしょう。ボタンは、『続ける』と、『終了』の二つです。ここでは、文字のフォントとサイズの指定が可能です。文字を大きく、そして自分の好きな字体での表示が可能となります。
イベントは4つになります。『初期化と値のセット』、『テキストの修正があった』、『続けるボタンが押された』、『終了ボタンが押された』、です。
Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
TextBox1.MultiLine = True '折り返し表示
TextBox1.Text = p_aa '値をセットする。p_aaに計算式が入っている
End Sub
Private Sub TextBox1_Change() 'ボックス内に動きがあると動く
p_aa = TextBox1.Text 'テキストを取得
End Sub
Private Sub CommandButton1_Click()
'続けるボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 0
End Sub
Private Sub CommandButton2_Click()
'終了ボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 9
End Sub
表示するフォントに関しては、ユーザフォームを設計する画面に一緒に出てくるプロパティのFontで指定してください。
モジュール(通常のマクロを記述するところ)側では、こんな感じです。
'計算式へ文字の付加
p_aa = 複雑カッコ文の解析(keisansiki) 'この関数はカッコをランク付けして置き換えるものです。
UserForm3.Show
'ここでUserForm3のマクロに飛ぶ。そのマクロが終了すると戻る。
a = 複雑カッコをもとに戻す(p_aa) 'この関数はカッコをすべて小カッコに直すものです。
p1 = InStr(a, "=")
If p1 <> 0 Then 'イコールがあると置き換え、ないと末尾に追加とした
keisansiki = a
Else
keisansiki = keisansiki & a
End If
If p_flag = 9 Then Exit Do
プラスアルファとは、上記でいうと"複雑カッコ文の解析(keisansiki)"という部分です。 第124回でカッコの置き換えをしましたが、それをすると複雑な計算式の構造が、少しわかりやすくなります。上記の例では、この機能を関数化して利用しています。反対の処理、すべて小カッコに戻すも同様です。
【今回のポイント】
・かなり長い計算式でも一覧できる。
・複雑な構造の計算式でも、カッコをランクごとに変えて表示するので、構造がわかりやすくなる。
・字体と字の大きさが代えられるので、見やすくなる。
※今回のやり方、ユーザフォームを使って表示領域を大きくするという基本の考え方は、他にも使えるかもしれません。
Msgboxを使用すると、その表示位置がコントロールできません。大体は画面の真ん中に来るようです。どこかほかに出てきてほしいということが多々あると思います。これをユーザフォームを使うことで対応することが可能です。位置はモジュールマクロの中で記述します。
2010年1月10日日曜日
第132回目 罫線編-3
○第132回目 罫線編-3
罫線については、既に第52回目と第53回目ちょっとした小物、罫線編で紹介しましたが、多少の機能を追加したいと思います。
1.五とびに横線を引く(五は一例。飛び飛びに引く機能)
2.細線の横線を引く
3.太線の枠線を引く
4.罫線ダイアログを出す
をご紹介しました。
通常の処理とこれらのマクロを兼用して処理をしてきましたが、もう少しマクロの範囲を拡げて、通常の処理を使わないでよいという感じにまでちかづければと思いました。
そこで、
1.指定範囲の上側または左側の罫線を引く･削除する。
2."太線の枠線を引く"を拡張して、一つの表の中をブロックに分け、そのブロック全部に枠線を引く。これによって表にかなりのアクセントがつきます。
1.について
コーディングは簡単です。
入力値として、上側に罫線を引くを11として、左を13、上を削除を91、左を93とします。単純なケースわけの処理となります。(さらにこれを拡張して、14では上と左を同時に引く、94では上と左を同時に消す、というのを考えるといいでしょう)
上を引く場合の例です。
'上を引く
Case "11"
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
End With
これをもとに
xlEdgeTop＝⇒xlEdgeLeft
xlInsideHorizontal＝⇒xlInsideVertical
xlContinuous＝⇒xlNone(これは削除です)
に変えるとできます。
2.について
選択した範囲の枠線を太線で引くは次のとおりとなります。
Selection.BorderAround LineStyle:=xlContinuous
Selection.BorderAround Weight:=xlMedium
このマクロは全体の範囲を選択した後から動かします。
入力値は、たて見出しの列数、よこ見出しの行数、内部ブロックの列数、行数。これを－で区切って入力。不要な場合は、該当するところを0といれる。1-3-4-0とか。
入力値からそれぞれの値を取り出すことについては省略します。
それぞれを、tate_m、yoko_m、data_b1、data_b2とします。
さて、たて見出しの範囲は次のとおりとなります。
Range(Cells(gyo1, retu1), Cells(gyo9, retu1 + tate_m - 1)).Select
よこ見出しは
Range(Cells(gyo1, retu1), Cells(gyo1 + yoko_m - 1, retu9)).Select
内部のたてブロックは
ブロックの個数は、商で求めます。
p1 = (retu_cnt - tate_m) \ data_b1
となり、この回数分繰り返します。
For i = 1 To p1 Step 1 'p1がゼロの場合は引かない
Range(Cells(gyo1, retu1 + tate_m + (i - 1) * data_b1), _
Cells(gyo9, retu1 + tate_m + i * data_b1 - 1)).Select
Selection.BorderAround LineStyle:=xlContinuous
Selection.BorderAround Weight:=xlMedium
Next
内部のよこブロックは、たてと同様ですので考えてみてください。
当然各数値が0であった場合は処理をしませんので、if文できいてください。
※補足
線の太さの種類としては、太いもののほかに、通常のもの、細いものがあります。
それらは、Selection.BorderAround Weight:=のところを、xlMedium、xlThin、xlHairlineとすればいいのです。
これらの内部コードは、太線＝-4138、通常線＝2、細線＝1となっていますから、これを使って、Selection.BorderAround Weight:=-4138とすれば同じになります。つまり、太さの調整も簡単に出来るということです。
罫線については、既に第52回目と第53回目ちょっとした小物、罫線編で紹介しましたが、多少の機能を追加したいと思います。
1.五とびに横線を引く(五は一例。飛び飛びに引く機能)
2.細線の横線を引く
3.太線の枠線を引く
4.罫線ダイアログを出す
をご紹介しました。
通常の処理とこれらのマクロを兼用して処理をしてきましたが、もう少しマクロの範囲を拡げて、通常の処理を使わないでよいという感じにまでちかづければと思いました。
そこで、
1.指定範囲の上側または左側の罫線を引く･削除する。
2."太線の枠線を引く"を拡張して、一つの表の中をブロックに分け、そのブロック全部に枠線を引く。これによって表にかなりのアクセントがつきます。
1.について
コーディングは簡単です。
入力値として、上側に罫線を引くを11として、左を13、上を削除を91、左を93とします。単純なケースわけの処理となります。(さらにこれを拡張して、14では上と左を同時に引く、94では上と左を同時に消す、というのを考えるといいでしょう)
上を引く場合の例です。
'上を引く
Case "11"
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
End With
これをもとに
xlEdgeTop＝⇒xlEdgeLeft
xlInsideHorizontal＝⇒xlInsideVertical
xlContinuous＝⇒xlNone(これは削除です)
に変えるとできます。
2.について
選択した範囲の枠線を太線で引くは次のとおりとなります。
Selection.BorderAround LineStyle:=xlContinuous
Selection.BorderAround Weight:=xlMedium
このマクロは全体の範囲を選択した後から動かします。
入力値は、たて見出しの列数、よこ見出しの行数、内部ブロックの列数、行数。これを－で区切って入力。不要な場合は、該当するところを0といれる。1-3-4-0とか。
入力値からそれぞれの値を取り出すことについては省略します。
それぞれを、tate_m、yoko_m、data_b1、data_b2とします。
さて、たて見出しの範囲は次のとおりとなります。
Range(Cells(gyo1, retu1), Cells(gyo9, retu1 + tate_m - 1)).Select
よこ見出しは
Range(Cells(gyo1, retu1), Cells(gyo1 + yoko_m - 1, retu9)).Select
内部のたてブロックは
ブロックの個数は、商で求めます。
p1 = (retu_cnt - tate_m) \ data_b1
となり、この回数分繰り返します。
For i = 1 To p1 Step 1 'p1がゼロの場合は引かない
Range(Cells(gyo1, retu1 + tate_m + (i - 1) * data_b1), _
Cells(gyo9, retu1 + tate_m + i * data_b1 - 1)).Select
Selection.BorderAround LineStyle:=xlContinuous
Selection.BorderAround Weight:=xlMedium
Next
内部のよこブロックは、たてと同様ですので考えてみてください。
当然各数値が0であった場合は処理をしませんので、if文できいてください。
※補足
線の太さの種類としては、太いもののほかに、通常のもの、細いものがあります。
それらは、Selection.BorderAround Weight:=のところを、xlMedium、xlThin、xlHairlineとすればいいのです。
これらの内部コードは、太線＝-4138、通常線＝2、細線＝1となっていますから、これを使って、Selection.BorderAround Weight:=-4138とすれば同じになります。つまり、太さの調整も簡単に出来るということです。
2010年1月2日土曜日
○第131回目 計算式作成のお手伝い(一般的な計算式、究極モドキ２)
○第131回目 計算式作成のお手伝い(一般的な計算式、究極モドキ２)
今回のテーマは、以下のコーディングに関することです。
1.座標を入力する際に、1回で複数の座標を演算子でつないでいれることができる(この入力内容は計算式としての要件を備えている必要がある)。
2.計算式の途中に、座標を入れることが出来る。途中の座標を修正することが出来る。
1.のポイント
セルの設定の際に、Application.InputBox文のタイプ(Type:=0)として、計算式を指定する(既存のマクロを修正したので、変数名は多少ちぐはぐ)。
その結果の受け入れも、計算式として受け入れる。計算式なので、先頭のイコールがついているため、それを取り除いて使用する。
'座標の設定
セル範囲 = Application.InputBox(Prompt:="使用するセルを指定してください。終了はキャンセルキー。", Default:=in_rrcc01, Left:=10, Top:=2, Type:=0)
If セル範囲 = False Then errflag = 0: Exit Do
Range(in_rrcc0).Formula = セル範囲
in_rrcc2 = Range(in_rrcc0).Formula
in_rrcc2 = Mid(in_rrcc2, 2) ＝⇒これが入力された座標等の情報
この複数座標等の情報から、次回の基点となる座標を抜出す。これは単独の場合と、コロンがあり範囲指定の場合の二つに分けています。
【単独の場合】
'計算式の入力は必ず座標で終わり、その直前は四則演算子のみとする。これ以外はエラーとする。
p1 = InStrRev(in_rrcc2, "+")
p2 = InStrRev(in_rrcc2, "-")
p3 = InStrRev(in_rrcc2, "*")
p4 = InStrRev(in_rrcc2, "/")
If p1 + p2 + p3 + p4 = 0 Then
in_rrcc01 = in_rrcc2
ElseIf p1 > p2 And p1 > p3 And p1 > p4 Then
in_rrcc01 = Mid$(in_rrcc2, p1 + 1)
ElseIf p2 > p3 And p2 > p4 Then
in_rrcc01 = Mid$(in_rrcc2, p2 + 1)
ElseIf p3 > p4 Then
in_rrcc01 = Mid$(in_rrcc2, p3 + 1)
Else
in_rrcc01 = Mid$(in_rrcc2, p4 + 1)
End If
【範囲指定の場合】
'コロンと最後のカッコを考慮し、座標を切り出す
p1 = InStrRev(in_rrcc01, ":")
If p1 <> 0 Then in_rrcc01 = Mid$(in_rrcc01, p1 + 1)
p2 = InStrRev(in_rrcc01, ")")
If p2 <> 0 Then in_rrcc01 = Left$(in_rrcc01, p2 - 1)
in_rrcc01が次回の基点となるセルの座標です。
2.のポイント
既存の計算式に特殊記号("..")がある場合は、それを入力値に置き換えます。
'..がある場合は、そこを置き換える。
p0 = 0
p1 = InStr(keisansiki, "..")
If p1 = 0 Then
keisansiki = keisansiki & in_rrcc2 ＝⇒in_rrcc2は入力された座標等の情報
Else
Do While 1 置換え箇所が複数あることがあります
keisansiki = Left$(keisansiki, p1 - 1) & in_rrcc2 & Mid$(keisansiki, p1 + 2)
p1 = InStr(keisansiki, "..")
If p1 = 0 Then Exit Do
Loop
End If
注意点；このマクロで追加･修正した座標は相対座標になっていますので、絶対座標にしたい時は、キーボードによる文字列の追加・修正の際、又は終了後に修正してください。
□質問があります。計算式を修正する時に、計算式が長い場合は、全部が表示できないのではないでしょうか。また、文字数の制限はあるのでしょうか。
＝⇒inputboxでの制限文字数は256バイトのようです。それより長いと駄目です。また、ご指摘のとおり、表示幅が小さいため、一覧できない場合があります。
□このマクロは、複雑な計算式の場合に力を発揮するというものだと思いますが、表示できる長さに制約があるということですと、うまくいかないのではないでしょうか。
＝⇒確かにご指摘のとおりのことが起こります。その対応として、ヒント的なものをつかんでいます。
□それを早く教えてください。このままではアイデアはいいが…、ということになってしまうのではないでしょうか。
＝⇒ご指摘のとおりだと思います。早めに提示できるようにします。
今回のテーマは、以下のコーディングに関することです。
1.座標を入力する際に、1回で複数の座標を演算子でつないでいれることができる(この入力内容は計算式としての要件を備えている必要がある)。
2.計算式の途中に、座標を入れることが出来る。途中の座標を修正することが出来る。
1.のポイント
セルの設定の際に、Application.InputBox文のタイプ(Type:=0)として、計算式を指定する(既存のマクロを修正したので、変数名は多少ちぐはぐ)。
その結果の受け入れも、計算式として受け入れる。計算式なので、先頭のイコールがついているため、それを取り除いて使用する。
'座標の設定
セル範囲 = Application.InputBox(Prompt:="使用するセルを指定してください。終了はキャンセルキー。", Default:=in_rrcc01, Left:=10, Top:=2, Type:=0)
If セル範囲 = False Then errflag = 0: Exit Do
Range(in_rrcc0).Formula = セル範囲
in_rrcc2 = Range(in_rrcc0).Formula
in_rrcc2 = Mid(in_rrcc2, 2) ＝⇒これが入力された座標等の情報
この複数座標等の情報から、次回の基点となる座標を抜出す。これは単独の場合と、コロンがあり範囲指定の場合の二つに分けています。
【単独の場合】
'計算式の入力は必ず座標で終わり、その直前は四則演算子のみとする。これ以外はエラーとする。
p1 = InStrRev(in_rrcc2, "+")
p2 = InStrRev(in_rrcc2, "-")
p3 = InStrRev(in_rrcc2, "*")
p4 = InStrRev(in_rrcc2, "/")
If p1 + p2 + p3 + p4 = 0 Then
in_rrcc01 = in_rrcc2
ElseIf p1 > p2 And p1 > p3 And p1 > p4 Then
in_rrcc01 = Mid$(in_rrcc2, p1 + 1)
ElseIf p2 > p3 And p2 > p4 Then
in_rrcc01 = Mid$(in_rrcc2, p2 + 1)
ElseIf p3 > p4 Then
in_rrcc01 = Mid$(in_rrcc2, p3 + 1)
Else
in_rrcc01 = Mid$(in_rrcc2, p4 + 1)
End If
【範囲指定の場合】
'コロンと最後のカッコを考慮し、座標を切り出す
p1 = InStrRev(in_rrcc01, ":")
If p1 <> 0 Then in_rrcc01 = Mid$(in_rrcc01, p1 + 1)
p2 = InStrRev(in_rrcc01, ")")
If p2 <> 0 Then in_rrcc01 = Left$(in_rrcc01, p2 - 1)
in_rrcc01が次回の基点となるセルの座標です。
2.のポイント
既存の計算式に特殊記号("..")がある場合は、それを入力値に置き換えます。
'..がある場合は、そこを置き換える。
p0 = 0
p1 = InStr(keisansiki, "..")
If p1 = 0 Then
keisansiki = keisansiki & in_rrcc2 ＝⇒in_rrcc2は入力された座標等の情報
Else
Do While 1 置換え箇所が複数あることがあります
keisansiki = Left$(keisansiki, p1 - 1) & in_rrcc2 & Mid$(keisansiki, p1 + 2)
p1 = InStr(keisansiki, "..")
If p1 = 0 Then Exit Do
Loop
End If
注意点；このマクロで追加･修正した座標は相対座標になっていますので、絶対座標にしたい時は、キーボードによる文字列の追加・修正の際、又は終了後に修正してください。
□質問があります。計算式を修正する時に、計算式が長い場合は、全部が表示できないのではないでしょうか。また、文字数の制限はあるのでしょうか。
＝⇒inputboxでの制限文字数は256バイトのようです。それより長いと駄目です。また、ご指摘のとおり、表示幅が小さいため、一覧できない場合があります。
□このマクロは、複雑な計算式の場合に力を発揮するというものだと思いますが、表示できる長さに制約があるということですと、うまくいかないのではないでしょうか。
＝⇒確かにご指摘のとおりのことが起こります。その対応として、ヒント的なものをつかんでいます。
□それを早く教えてください。このままではアイデアはいいが…、ということになってしまうのではないでしょうか。
＝⇒ご指摘のとおりだと思います。早めに提示できるようにします。
登録:
コメント (Atom)
