VBA セルに数式を設定する

筒井.xls@エクセル関数擬人化本著者 @Tsutsui0524

#ExcelVBA のセルに関数を入れるのに Formula使ってA1形式で書いても参照セルが変わってくれるのを始めて知りました （図の例だと全部セルA2参照になると思ってました） ずっとFormulaR1C1使ってR1C1形式で書いてました 損してたなぁ・・・ pic.twitter.com/fZt5cEgkcN

拡大

SONO @SonoOpanda

@Tsutsui0524 VBAは今まで敷居が高いなぁと思っていたのですが、筒井さんのこの惹かれるマクロを拝見して、やっと一歩踏み込めました!!☺️イラストも素敵🥰 関数の始めの一歩はSUMちゃん、VBAの始めの一歩はSUBちゃんですね☘ あ…見様見真似で学習してみたら、関数以前のミステイクしてました…(*´ｪ`*)ﾄﾞﾝﾏｲ pic.twitter.com/KjPEcB2jUW

拡大

しるる @sirururun

@Tsutsui0524 これめちゃわかります！！ 私もA1形式だと絶対参照になると思っててそれでR1C1で書いてました😇w案外その辺の融通利くんだなぁって思いましたw

筒井.xls@エクセル関数擬人化本著者 @Tsutsui0524

@sirururun やっぱりそう思いますよね！ 直感的に挙動を想像してしまっていました 頭が硬いのは自分の方だったという・・・ A1なら相対、＄A$1なら絶対って、言われればその通りなんですけど・・・！

筒井.xls@エクセル関数擬人化本著者 @Tsutsui0524

@tokagedinosaur 文字列として代入する方法があるんですね！ ということは、Formulaの強みは相対参照式を自動で入れられることだったのですね 前任の方が良く使ってたので私も使ってましたが、メリットには今気づきました ありがとうございます！

とかげ @tokagedinosaur

@Tsutsui0524 条件によってセルの日付を当日にするか翌日にするかで、"=today()"か"=today()+1" を入れてただけなんです。やってみたら何かできたくらいの感覚でした😅素人なのであまり深く考えて無かったです。

☘𝑴𝒂𝒓𝒖𝒎𝒊☕48歳☺ @marumin21

@Tsutsui0524 これなです😇 私はわざわざ全部INDIRECT＋ROW()使って書いてました…激重の揮発性関数だということも当時は知らずに😇 先頭セルアドレスを指定しておくだけでよいと神髄先生に教えて頂きました😇 ほんとに何もかも神髄先生に教えて頂いたんだなぁ私…

筒井.xls@エクセル関数擬人化本著者 @Tsutsui0524

@kyrtnyy なるほど、ROW関数を使う手がありましたか！ そうですか、きょろさんですら…隠れ便利機能ですね 神髄先生は偉大です！

やすひさ @komakiyasuhisa

@Tsutsui0524 なるほど。 連続のセルに式を入れる際、VBA使わず普通にセルコピーでやってましたので、こういうことが出来るという以前に発想がなかったです。 Formula使うこともあった気がしますが、一つ一つ入れるパターンだったような…。

吉田拳/Excelで、経営は強くなる @sugoi_kaizen

ある支援先向けのExcelマクロが大詰めなので早起きして組んでるんですけど雨降ってると酒が飲みたくなりますね。おはようございます。 マクロで数字集計する場合 Worksheetfunction.Sumifsとかはほぼ使わず Range(範囲) = "=SUMIFS(～～～)" ってセルに式をセットする書き方が推奨です。検算のため。

吉田拳/Excelで、経営は強くなる @sugoi_kaizen

ですね、僕も演算する処理はすべてこの書き方です。値だけだと検算しづらいからセルに式をセットしたいというのもあり、ループより遥かに速いというのもありますね。ちなみに僕はFormulaは省略しちゃってます。 twitter.com/Tsutsui0524/st…

吉田拳/Excelで、経営は強くなる @sugoi_kaizen

ただこの書き方はどういうことなのか分からない…というご質問もよく頂きますが、手操作で言えばこれは入力範囲を選択して先頭のアクティブセルに式を入力してCtr+Enter押すと選択範囲の全セルに一括入力されますよね、あれです。

ほえほえ＠スプシマン @hoehoe1234

この仕様は改善さんのツイから知っていたのですが、なかなか想像しにくいと思います。同様に">="なども。文字列の中身を検索して適切に参照位置を変えてくれるとは思いませんから。 twitter.com/sugoi_kaizen/s…

ほえほえ＠スプシマン @hoehoe1234

ただ、これは、シャアさんの名前の回で、自分で検証したのですが、一回、RC形式、すなわち内部的にはセル相対で範囲にコピーしているんだと思います。名前の範囲が動的に変わったように見えても実はRC形式でみると変わってないこととおなじなんでしょうね。

ほえほえ＠スプシマン @hoehoe1234

①範囲に数式を設定する。 ②数式は１つであるが内部的にはA1形式でなくてRC形式として扱う。 ③同じ式が範囲に設定される。 ④これをA1形式でみるとあたかも「自動で変わったように見える」 ということなんだと推測しています。式はRC形式で考えると辻褄が合う挙動がおおいですね。

ほえほえ＠スプシマン @hoehoe1234

なので「自動で変わる」のではなくて、設定されている数式は同じなんですけど「違って見える」ということなんだと思います。A1のセル絶対形式は便利ですが、もう一つのRC形式、セル相対形式を意識するといろいろ理解が進むと思います。

セル質 @ExcelQBag

これの応用で、 IF関数のTRUEまたはFALSEを"=1/0"にして一括入力したあと、 Range("B:B").SpecialCells (xlCellTypeFormulas,xlErrors).EntireRow.Delete これでループもUnionも使わず削除したい行を一括削除。というのも出来ます。 twitter.com/Tsutsui0524/st…

セル質 @ExcelQBag

"=1/0" ↓ 1/0でした💦 エラーを返す式なら何でも大丈夫です。 もちろん前提として、IF関数の第一引数そのものがエラーを返す可能性のあるデータには使用できません。

セル質 @ExcelQBag

"=1/0"を使うのはこちらでした。 A列の"削除"が含まれたセルの行全体を一括削除したい場合。 Range("A:A").Replace "*削除*","=1/0" Range("A:A").SpecialCells (xlCellTypeFormulas,xlErrors).EntireRow.Delete これでA列の"削除"が含まれているセルの行全体が一括削除されます。