EXCEL VBA構文について

質問

EXCEL初心者です、退職された人が作成した関数を利用しているのですが、関数をデリートを押し消してしまう人がいて困っています。これは記号で入力したシフト表から1日～月末の日当たりの仕事別担当は誰かを検索して表を作成しているものです。この関数をVBAで作成したいのですが挑戦しているのですが出来ず困っています、置き換えの構文又はヒントを教えていただきたく宜しくお願いいたします。合わせて記号の意味をお願いいたします。
=IF(ISERROR(INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0))),"",INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0)))
今使用している関数です、sheet3にはA列に10人程度の名前、横の列には1日から月末まで7つ程度の記号が入っています。置き換えSheetにはB列に記号、横列に1日から月末ですここに名前が入るようにしたい。
お願いは
1.この関数からVBAの構文にするにはどうすればよいか
2.&"/"&の意味、Sheet3!B:B,0の最後の0の意味、,"",の意味を教えてください
作文が下手ですいません、無理なお願いですいません。

回答No.4

Nouble

関数を削除されて、
其れで、困る
訳、ですよね？
要するには

原本を
テンプレート形式て、保存し
新規作成は
此から、行えば
関数が、消されようが

壊したなら
新規作成して
値を、移してね

で、済みそうですが
駄目です？

因みに、
=IF(ISERROR(INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0))),"",INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0)))
ですが、

IF構文は
三つの因数に、より
構成されます

一つ目は
判断材料

前式では
ISERROR(INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0)))
です

二つ目は、
判断材料が
成立した時に
させる動作です

前式では
"'
ですね

三つ目は、
判断材料が
不成立の時に
させる動作です

前式では、
INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0))
ですね、

所で、
不成立時の
此の、式ですが

仮に、
此の、式を
「甲」と、すると
ISERROR (甲)
で、
判断材料と同じになります

詰まりIF構文は
甲の指揮をさせて
エラーにならないか
見ている
訳ですね

では、甲の式ですが
最初に
INDEX構文が、あります

此は、此の式で言うと
一つ目の項の範囲内、先頭から
二つ目の項の値だけ（正しくは、値から、1引かれる）
進んだ所の要素を
取り出しなさい

と、言う動作を
します

INDEX 構文の
一つ目の項は
Sheet3!$A:$A
二つ目の項は
MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0)
ですね、

此は、
Sheet3のA列から
マッチ構文で得た値の
其の行の、セルを
読み出しなさい

と、言う動作を
します

MATCH 構文は
他の方が、既に
解説されてますね

ただ
一つ目の項を、見ると
$B6
と、
列は固定されていますが
行は固定されていません

此は、
セルの　フィルに、つれて
B6、B7、B8、…
と、
変化させる意図
ですね

同じく、
COUNTIF($B$6:$B6,$B6)
中の
$B$6:$B6
も、同じ意図で

B6:B6、B6:B7、B6:B8、…
と、範囲を
広げていく、意図
ですね

と、すると
B列のx行の値を
B6:Bxの、範囲から
探す事に、なり

此の時、
Bxセルに、Bxセルが
必ず、ヒットするので

CountIF構文は
1以上を
返す事に、なりますね

と、よく見ると
B列は、氏名だけ
なのですよね？
氏名の後方に
数値、付きますか？
付きませんよね？

もし、だとしたら
CountIFで
数値付きになった
文字列で
B列を検索しても
ヒットは、しないはず

此は、
古い古いExcelの
LookUp構文で
裏技として、用いられていた
検索エラー回避法
&"/"&COUNTIF($B$6:$B6,$B6)
を、
意味も、分からず
MATCH構文に、持ち込んでいる
の、かも
知れませんね

でも、
此の、方式
MATCH構文では、
働いてません、よね？
恐らく

だとしたら、
こんなもの
判らなくて、当然ですし
無用の、長物
です

私が、書き換えましょう

IF構文で、エラー回避済みなので
仕様と、いう名の
バグを
逆手に、付いた
&"/"&COUNTIF($B$6:$B6,$B6)
は、恐らく
不要、です

MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0)
から、省きます
MATCH($B6,Sheet3!B:B,0)
シンプルに、なりました

INDEX構文は
データを抱える量が多く

また、一般に、
データ量(の二乗)に、反比例して
処理は、遅くなる
と、されます

ので、私は
INDEX構文は、避けます。

OFFSET構文で、行きましょう
初項は、
参照基準位置です

参照範囲が
Sheet3!$A:$A,
ですので
先頭は
Sheet3!$A$1
ですね

次項は、
参照基準位置からの、相対行数です

MATCH構文は
エラー、又は1以上を
返しますが
相対行数は0から欲しい
の、です

ので、1を引きます
MATCH($B6,Sheet3!B:B,0)-1
ですね

３項目は、
参照基準位置からの、相対列数です

読み出し範囲が
参照基準位置と
同列に、あるので
相対列数は
0　ですね

第４項、第5項は、
読み出すセルの範囲、です

読み出すのは、一つのセル
のみなので
共に
1が、入ります

組み立ててみましょう

OFFSET ( Sheet3!$A$1, MATCH($B6,Sheet3!B:B,0)-1,0,1,1)
基本式が、出来ました

因みに、
Offset構文で記述してある物は
VBA化に際しては
単純に
….Range(…　)
で、書けます

又、此の式中
エラーを、出すのは
ほぼ、
MATCH構文、だけです

エラー処理は、
MATCH構文、だけで
行います

さて、
エラー処理を、加えましょう

=IF(
　　ISERROR(
　　　MATCH($B6,Sheet3!B:B,0)
　　),
　　"",
　　OFFSET(Sheet3!$A$1, MATCH($B6,Sheet3!B:B,0)-1,0,1,1)
　)
ですね

すっきりしましたね

動作確認を、頂けますか？

因みに、
Excelのセル内で
関数式を、改行をしても
動作はします。

先頭の空白は、
無視されます

ので、
書き換えた式は
インデントを
外す必要は、ありません

まぁ、
Excelの、式解析機能は
働かなくなる、ので

其の分は、苦労する
かも、知れませんが

こっちの方が、見易い
ですよね？

回答No.3

tsubu-yuki

えーと、言いづらいのですが・・
式の意図を読み取れないのであれば、ＶＢＡで云々するのも困難と思いますよ。
素直に「シートの保護」をかけるのが良い選択肢ではないでしょうか？
編集しても良いセルからは「ロック」を外したうえで、ですね。

ＭＡＴＣＨやＩＮＤＥＸは理解できると便利な関数ですが、
迷い出すとドツボにはまりますので難しいですね。
とりあえず、分解してみましょうか。

ＭＡＴＣＨ関数は、
　　=MATCH(検査値，検査範囲，照合の型)
という風に使い、「検査範囲の何番目に検索値が出てくるか」を返します。
今回は
　　=MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0)
こんな形でお使いですので、疑問のうち、
> 最後の0の意味
については「照合の型」を意味しているとわかります。
詳細は別途お調べいただければ、と思うのですが、
ここに「０」を指定してやると「完全一致」を指定できます。
「以上でもない、以下でもない。イコールだ。」の意味ですね。
つまり、
Sheet3のＢ列から「$B6&"/"&COUNTIF($B$6:$B6,$B6)」と完全に一致する場所
を探してこい、という意味の式ですね。
では、この「$B6&"/"&COUNTIF($B$6:$B6,$B6)」は何なのか。
これは実はさっぱりわかりません。だって、サンプルもないですから。
> 置き換えSheetにはB列に記号
とのことですが、「記号」って何ですか？☆？□？●？
要するに、こういうことなんです。

で、終わるのもアレですから、ここからは仮定を含めて推察。
まずは、疑問のうち
> &"/"&の意味
この「＆」は、エクセルでは一般的に「文字列を結合する」ときに使います。
例えば任意のセルに
　　="A"&"b"
と入力して確定すると、「Ab」が返ります。
Ａ１セルに「Ａ」、Ｂ１セルに「ｂ」を入力しておいて、
　　Ｃ１セル：=A1&"/"&B1
としてやると、Ｃ１セルには「Ａ/ｂ」という文字列が返ります。
ご提示の式では、
　　「記号/(６行目以降自行までに出現する記号の個数)」
を返してきているものと推察できるのですが、
ここから先は実際のデータを見ないと何とも言えません。
差し支えない範囲でサンプルをご提示いただくか、
データを見なくてもわかるスーパーな回答者をお待ちいただくか、
ご自身で理解なさるか、いずれかです。

ＩＮＤＥＸ関数は「範囲の中から●●番目のデータ」を返す関数
とまずは単純に理解しましょう。
ＩＦ関数・ＩＳＥＲＲＯＲ関数についてはそこまで解説も必要ないものと思われます。

で、話は冒頭に返ります。
この式が理解できないとなると、ＶＢＡに持って行った後のメンテナンスが大変です。
実際、「ＶＢＡでこの式を記入する」のはものすごく簡単に実現できます。
記録に頼ったコードそのままですが、
　　　　Range("A6:A20").Select
　　　　Selection.FormulaR1C1 = _
　　　　"=IF(ISERROR(INDEX(Sheet3!C1,MATCH(RC2&""/""&COUNTIF(R6C2:RC2,RC2),Sheet3!C[1],0))),"""",INDEX(Sheet3!C1,MATCH(RC2&""/""&COUNTIF(R6C2:RC2,RC2),Sheet3!C[1],0)))"
これだけです。
が、理解できないまま使うのは危険ですよ、というところでしめておきます。

参考までにどうぞ。

回答No.2

thx_td

もし「VBAで作成したい」のが目的ではなく、「関数を消してしまう人がいて困る」のが問題なのであれば、シートの保護機能を使って関数が入力されているセルを修正（削除）できないようにするのが早道かな、と思います。

回答No.1

ushi2015

こんばんは
Excelのバージョンは？
もし2007以降でしたら、その数式は
=IFERROR(INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0)),"")
と書き換えられます。

＝$B6&"/"&COUNTIF($B$6:$B6,$B6)
という数式を例えばC10に入れてみて下さい。
「B10の値/B6からB10まで中のB10の値の個数」
B10の記号が「甲」として、B6からB10まで中の「甲」の個数が「3」とすると
「甲/3」となるはずです。
その時、D10に
=INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0))
と入れてみると、Sheet3のB列で「甲/3」と一致するセルが有れば、その行の
Sheet3のA列の値が表示されるはずです。
一致するものが無ければ「＃N/A」となります。

IFERRORはそのようなエラーになった時に「""」空のデータをセットする関数です。
一致するデータが有った場合は、
INDEX(Sheet3!$A:$A,MATCH($B6&"/"&COUNTIF($B$6:$B6,$B6),Sheet3!B:B,0))
の結果がセットされます。

この数式をセットするVBA、或いは置き換えSheetのB6以下に記号を入力した時点で
数式の結果だけをセットするVBAも作成可能ですが、まずは数式を置き換えSheetの
C6からAG列の必要な行までセットし、セル全体の書式でロックを外し、C6からAG
列の必要な行までの数式をセットした範囲とその他必要なセルの書式でロックし
シートを保護するのがいいのでは？