第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.の処理は、結構時間がかかりますので、単純なもので広範囲な場合は、通常の置換えでやったほうがいいかもしれません。
　参照元ブックを開いていると、:の取得ができませんで、当該ブックを閉じてから行いましょう(計算式に振るパス名がつく)。
　

第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関数に入らないようにしてください。

第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とか、列-列形式で入力するとスマートですね。