エクセル関数を教えてください6

何度も同じような質問で申し訳ありません。
以前一度成立した配列数式が元のデータを更新したところ「#REF」のエラーが出てしまった質問をさせていただきました。エラーのサイトを見てみたのですが実際そのように対処したらよいかわかりませんでした。
具体的には以下のような感じです。
＜元データ＞
グループ 名前 作成月
A 　　　浅井 　1
A 　　　伊藤 　3
A 　　　小林 　1
B 　　　久野 　3
B 　　　酒井　1
C 　　　服部 　3
C 　　　林 　1

配列数式実施にて
グループ A B C A B C
作成月 1 1 1 3 3 3
　浅井 酒井 林 伊藤 久野 服部
小林
元データの「Ｂ　久野　３」の行を削除した場合「#REF」が出てしまいました。

詳しく教えていただけると助かります。
よろしくお願いします。

No.2 ベストアンサー 回答者： tom04 回答日時： 2017/10/12 09:57

こんにちは！

以前、回答した者です。
記憶によるとおそらくお使いのバージョンはExcel2003だったと思います。
バージョンに関係なく数式で処理する場合、行削除（行挿入）等はエラーの元です。
極力そのような操作は避けるべきなのです。

関数で無理やり処理は可能かもしれません。ただし数式そのものが極端に長いものになり
メンテナンス（数式の修正）などおそらくできなくなるのではないでしょうか？

VBAであれば行削除などがあっても対処可能です。
一例です。

ALT＋F11キー　→　メニュー　→　挿入　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（ALT＋F8キー　→　マクロ　→　マクロ実行です）

尚、↓の画像のような配置で元データはSheet1にあり、Sheet2に表示するとします。

Sub Sample1() '//この行から//
Dim j As Long, lastRow1 As Long, lastRow2 As Long, wS As Worksheet
Set wS = Worksheets(2)
Application.ScreenUpdating = False
With Worksheets(1)
lastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
For j = 2 To wS.Cells(1, Columns.Count).End(xlToLeft).Column
lastRow2 = wS.Cells(Rows.Count, j).End(xlUp).Row
If lastRow2 > 2 Then
Range(wS.Cells(3, j), wS.Cells(lastRow2, j)).ClearContents
End If
With .Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:=wS.Cells(1, j)
.AutoFilter Field:=3, Criteria1:=wS.Cells(2, j)
End With
If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(.Cells(2, "B"), .Cells(lastRow1, "B")).SpecialCells(xlCellTypeVisible).Copy
wS.Cells(3, j).PasteSpecial Paste:=xlPasteValues
End If
Next j
Application.CutCopyMode = False
.AutoFilterMode = False
wS.Activate
wS.Range("A1").Select
End With
Application.ScreenUpdating = True
MsgBox "完了"
End Sub '//この行まで//

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。
※　いったんマクロを実行すると元に戻せないので
元データを別シートにコピー＆ペーストしてマクロを試してみてください。m(_ _)m

この回答へのお礼

何度もありがとうございます。
ALT＋F11キー　の後メニューがどこにあるか分かりませんでした。
ちなみに仕事で活用したいのですが現在質問させていただいているのは自分のＰＣです。仕事は制限があり、ネットが直接つながらないためコピー＆ペーストができません。
他に何か良い方法があれば教えてください。

お礼日時：2017/10/13 23:30

No.7 回答者： tom04 回答日時： 2017/10/24 16:59

ん～～～

No.2の配置通りで、もう一度投降したコードを標準モジュールにコピー＆ペーストし、マクロを実行してみました。

手元ではちゃんと動作します。
①　「Sheet2」の1行目・2行目の項目はちゃんと入力済みでしょうか？
②　シート名は間違いないか？
③　Worksheets(1)　や　Worksheets(2)　をそのまま使っている場合、
シート見出しの順は左からちゃんと画像通りのシート順になっているか？

考えられる原因とすればこの程度ですかねぇ～～～

ごめんなさい。m(_ _)m

この回答へのお礼

ありがとうございました。Sheet2の１行目・２行目が入力していなかったのが原因でした。
元データのことですが・・・別に大元のデータがあり、別のシートに抽出したものです。大元のデータの行を削除した場合元データで「＃ＲＥＦ」と出てしまいますが大丈夫でしょうか？

お礼日時：2017/10/25 22:58

No.6 回答者： tom04 回答日時： 2017/10/24 09:55

＞シートに何も出てきませんでした。

何も変化がない！とはで該当データがない！というコトになります。

気になる点が・・・
No.2でアップした画像通りの配置だとすると、
Sheet1とSheet2の「作成月」とは単に数値だけなのでしょうか？
それともシリアル値が入っていて、表示形式を変えているのでしょうか？

VBAの場合、日付（シリアル値）の検索はかなり厄介です。
もし、シリアル値が入っているのであれば、当然コードは変わってきます。
MONTH関数もしくは、「年」まで考慮するのであればYEAR関数を使う必要があります。

その辺をご返事いただければ少しはお役に立てるかも・・・m(_ _)m

この回答へのお礼

ありがとうございます。
作成月は１か３の数値しか入りません。
他に何か原因がありますでしょうか？

お礼日時：2017/10/24 16:40

No.5 回答者： tom04 回答日時： 2017/10/20 23:31

＞「sheet1」が別名がついている場合はシート名を置き換えても問題ないでしょうか？

本来であればSheet名は実際のSheet名を記載するのがよいのですが、今回のコードは
＞Set wS = Worksheets(2)
や
＞With Worksheets(1)
のようにSheet名を指定するのではなく、
シート名は一切関係なくて、シート見出しの左から○番目のシートと指定しています。

実際のSheet名を入れる場合
コード内の「1」や「2」の数字部分を変更してください。

※　当然のコトですが、仮に「Sheet1」の場合は文字列になりますので、
「"Sheet1"」　のようのシート名の前後にダブルクォーテーションを入れてください。m(_ _)m

この回答へのお礼

ありがとうございました。シート名を変えたところマクロは成立しましたがシートに何も出てきませんでした。
どうしたらよいでしょうか？(;_:)

お礼日時：2017/10/24 00:22

No.4 回答者： tom04 回答日時： 2017/10/18 22:59

＞Type ブロック外では無効なステートメントです

今一度VBE画面を確認してみてください。

前回のコードの
＞Sub Sample1()
と
＞End Sub
の中に納まっていなければなりません。
（プロシージャ内に収まっていないといけない）

何か余計なコードがプロシージャ外に記載されていませんか？
もし余計なものがあれば消去してみてください。

こちらで考えられることといえばこの程度でしょうか。

※　「Option Explicit」という記載が画面の最初にある場合、
これはあっても構いません。（というか、あった方が良いです）m(_ _)m

この回答へのお礼

回答ありがとうございます。
もう１度見直してみたところ間違えていた部分は修正しましたが今後は「インデックスが有効ではありません」とエラーが出てしましました。
「sheet1」が別名がついている場合はシート名を置き換えても問題ないでしょうか？

お礼日時：2017/10/20 23:17

No.3 回答者： tom04 回答日時： 2017/10/13 23:53

続けてお邪魔します。

＞ALT＋F11キー　の後メニューがどこにあるか分かりませんでした。

どうも失礼しました。前回の投稿で「挿入」の後に「標準モジュール」と入れるのを忘れていました。
上記操作後、表示された画面がVBE（Visual Basic Editor)の画面になります。

その画面の上にメニューバー（ファイル・編集・表示・・・）となっている中に「挿入」があります。
挿入をクリック　→　その中に「標準モジュール」があるのでそれを選択！
後は前回投稿した通りです。

＞コピー＆ペーストができません。
＞他に何か良い方法があれば教えてください。

ん～～～
とりあえず、自宅でこの画面から前回のコードをWordにコピー＆ペースト　→　USBメモリー（SDカードでもOK）にファイルを保存し
会社のPCで開き、コピー＆ペーストしてみてはどうでしょうか？

※　外部記憶媒体は使用禁止の会社もあるはずです。（最近はその方が圧倒的かも）
その場合はコツコツ手入力しかないかもです。m(_ _)m

この回答へのお礼

回答いただきありがとうございます。お礼が遅くなってしまい申し訳ありません。
回答通り実施してみましたが下記のようなエラーが出ました。
コンパイル　エラー
Type ブロック外では無効なステートメントです。

どのようにしたらよいでしょうか？

お礼日時：2017/10/18 22:44

No.1 回答者： gootama_bao2 回答日時： 2017/10/12 01:08

関数の作りにより要因は複数考えられます。

一番多い発生原因はセル単位の挿入・削除によるものかと私は思います。
↓こういった方に相談すると丁寧に教えてくれます。パソコンスクールに行くより手軽ですし、安いですし。
https://coconala.com/services/355760
聞いてみてはいかがでしょうか？

この回答へのお礼

ありがとうございます。
参考にさせていただきます。

お礼日時：2017/10/13 23:32