エクセルのセルに書かれた関数が解読できない

Question

Windows7上のexcel2010を使っています。
何を記録しているものなのか完全には理解できていないエクセルファイルを渡され、いくつかのセルに入っている関数をVBAコードに変える仕事をまかされました。しかし、あるセルの関数については、その意味すら理解できずに困っています。

セルAB26に、以下の関数が入っています。

=IF(ROW()<$C$6,"",IF(ROW()<$C$6+$AA26,1,IF(SUMPRODUCT((OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-2,0)=$Z26)(OFFSET($J$1,$C$6-1,0):OFFSET($J$1,ROW()-2,0)>0))/(COUNTIF(OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-2,0),$Z26)+1)-SUMPRODUCT((OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-1$AA26-1,0)=$Z26)(OFFSET($J$1,$C$6-1,0):OFFSET($J$1,ROW()-1$AA26-1,0)>0))/(COUNTIF(OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-1*$AA26-1,0),$Z26)+1)<0,1,-1)))

※このシートでは、C6には数字が入っていて、J1とZ1には何も値がない状態ですが、関数としてちゃんと機能しています

Q1)
「=A1+B3」という関数を、「セルA1の値とセルB3の値を足した数」と言うように、上記関数を日本語に砕いて翻訳していただけないでしょうか？

Q2)
関数の意味もわからないのに質問するのもへんですが、これをVBAコードに変えるのに、何か注意すべきことを、思い当たればお願いします。

私は、これまでエクセルVBAを使って、ACCESSとの連携や、データを加工してCSVファイルとして出力するフォームを作るなど、けっこう経験があり、エクセルVBAについては中級者だと思っていましたが、上記関数がまったく理解できないことでさっぱり自信をなくしました。
そういうレベルの人間が相手だと想定してご回答いただければ幸いです。

よろしくお願いします。

Comments

[otn]

何が分かりませんか？使われている関数の意味？複雑な式を分解するやり方？

Answer 1

ご要望にはお応えできませんが…
適当に整形してみましたので少しは分かりやすくなったと思います。

=IF(
     ROW()<$C$6,
     "",
     IF(
         ROW()<$C$6+$AA26,
         1,
         IF(
             SUMPRODUCT(
                 (OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-2,0) = $Z26)
                 *
                 (OFFSET($J$1,$C$6-1,0):OFFSET($J$1,ROW()-2,0)>0)
             )
             /
             (COUNTIF(
                 OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-2,0),$Z26)
              +
              1
             )
             -
             SUMPRODUCT(
                 (OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-1*$AA26-1,0)=$Z26)
                 *
                 (OFFSET($J$1,$C$6-1,0):OFFSET($J$1,ROW()-1*$AA26-1,0)>0)
             )
             /
             (COUNTIF(
                 OFFSET($Z$1,$C$6-1,0):OFFSET($Z$1,ROW()-1*$AA26-1,0),$Z26)
              +
              1
             )<0,
             1,
             -1
         )
     )
 )

Comments

[takasima20]

しかし、これって仕事の丸投げってことですよね。
いろんな意味で(なるべく)職場内で解決する方が
ベターなんじゃないかなあ。

[y_repres]

ありがとうございます。
すみません。職場内では助けてもらえない状態で。。。

この件は、もともとこの関数が入っていた列やこの関数が参照している列（およびそこに入っていた関数）を全部削除してしまった状態でも、この関数の結果じたいは元のセルに表示させたいからVBAに変換する、というのが当初の主旨なのですが、いま思いついた↓この発想はどうでしょうか？

もう関数を解読してそれをVBAコードに変換するのは諦めて、次のようなコードを書くことにする。
　・絶対に使用されない遥か右の方の列に、上記関数が参照している列を再現する（各セルに、コードで関数を書いていき、上記関数の結果を表示させたい列の各セルにも、上記関数をそのまま（参照列名は変えて）、コードから書いていく。
　・関数を書き終えた後、（関数の結果が表示されているはずの）その列をコピー→値をペーストする
　・上記関数が参照するために作った列を削除する
　・シレッと、「処理終了」のメッセージボックスを出す
↑
つまり、一時的に作業列を作りそこの各セルに関数を書いて、あとで値をペーストするという姑息な手段なんですが、これだと、もう解釈する必要もないですし、ミスの可能性も減ります。
ダメでしょうか？

[takasima20]

事情は分かりませんが…
私が判断できることではないですね。

[fcrow]

遥か右に書いていたとしても Ctrl+A → Delete で消えてしまいますよ。

[tacsheaven]

削除させたくない、更新もさせたくないなら、セル単位で保護指定した方がよっぽどマシなような気はしますけどね。

[退会済みユーザー]

今多いですよ。元々作成して利用していた人が、引き継ぐ暇もなくいなくなってしまうとか…。
(私も引継ぎの時間がないことがよくありました…。)
これは想像ですが、「関数ギッシリ詰め」のシートになっているような？
だとすると、再計算とか大変だと思いますよ？(今のPCですら1日かかるものも…。)

Answer 2

横入り失礼します。
確かに、何をしているのかを分からないと、コードも書きようがありませんね…。

一応、関数をざっと拝見したところ、次のような感じに見えますが…。

1. Row()で、関数の入っているセルの「行番号」を取得。
2. 1.の行番号とセル「C6」の値と比較し、小さい場合は空欄とする。それ以上は3.の比較。
3. 1.の行番号をさらに、セル「C6の値+AA26の値」を比較し、小さい場合は「1」を出力。
4. 2,3の条件に該当しない場合は、下記の関数で処理。

ここからは関数の説明になりますが…(御提示の関数の内容と比較してみて下さい)。

• SUMPRODUCT(配列1,配列2,…)で、配列1と配列2、…で「対応するセルの値を掛け合わせたもの」を合計する。

具体例としては(下記表のつもりですが、見づらいと思います…。)、
| | A | B | C |
|1| 名前 |単価(円)|個数|
|2|りんご| 100| 5|
|3|みかん| 120| 8|
の売上合計を求める場合、

=SUMPRODUCT(B2:B3,C2:C3)

という数式で、1005+1208=1460円という合計が求められるという関数です。

• OFFSET(基準セル,行数,列数)で、基準セルから何行何列目のセル参照を取得する。

(基準セルには値が無くても、参照先に値があれば大丈夫。)

• COUNTIF(範囲,検索条件)で、検索条件(この場合、Z列のみ絶対参照)に該当するデータの個数を調べる。

という所から、解析してみてはいかがでしょうか？

OFFSET(～):OFFSET(～)という所は、一見すると訳わからないように見えますが、
行範囲を指定しているだけですね…。
訂正：OFFSETの最初は、セルZ1が基準となり、そこからセルC6に入っている数値-1、つまりはC6の数値の1行上の行を最初の行として指定しています。私の間違いでした。混乱させて申し訳ございません

あと、データの最初に見出し行があるような気がしますが…。
Row()-2は、Row()-(見出し行1行分+最終行の行番号が1行分数字が大きくなるため、1行引く)
という、私も結構よく使うやり方です。

以上拙いですが、これらを手掛かりにしていただければ、VBAコードも作成しやすくなるかと
思います。

takasima20様
関数の整理ありがとうございます。お陰様で解析しやすくなりました。

Comments

[takasima20]

(･∀･)b

[退会済みユーザー]

takasima20様　ありがとうございます(#^^#)

[y_repres]

とてもよくわかりました。ありがとうございました。