[[20130528094927]]　『可視セルのみオートフィル』（くう）　ページの最後に飛ぶ

『可視セルのみオートフィル』（くう）
エクセル2010

A1
A2　ROW(A1)
A8 ROW(A2)
A14 ROW(A3)
A20 ROW(A4)
A26 ROW(A5)と　６行おきに相対させたいが可能なのか。

ほんとの式は、=IF(ISERROR(VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE))
のB11を６行おきに連番にしたいけど　可視セル　貼り付けにするとB17になるのです。
今は、そこだけを　一つ一つ　打ち直してます。
あまりの　手直しの量に疲れてきました。

なにか　いい方法ありますでしょうか？
　
フィルターかけた表をコピーして式を入れて元の所へ貼り付けても　値だけか。
式が狂うかでした。

可視セルで　オートフィルは　相対では、無理なんでしょう…
しかし、この　６行おきの規則を使って
なんとか　ならないものでしょうか？
B11からB12に簡単に　６行おきに　ならないものでしょうか

ネット検索かけても　ないもので…

よろしくお願いいたします。

	A	B	C	
1	ママ	可視		
2	1	1	A	←可視
3	1	1		←不可視
4	1	1		←不可視
5	1	1		←不可視
6	1	1		←不可視
7	1	1	A	←可視
8	2	1		←不可視
9	2	1		←不可視
10	2	1	A	←可視
11	2	1		←不可視
12	2	1		←不可視
13	2	1		←不可視
14	3	1		←不可視
15	3	1	A	←可視
16	3	1	A	←可視
17	3	1	A	←可視
18	3	2	A	←可視
19	3	2	A	←可視
20	4	2	A	←可視
21	4	2	A	←可視
22	4	2	A	←可視
23	4	2	A	←可視
24	4	3	A	←可視
25	4	3		←不可視
26	5	3	A	←可視
27	5	3		←不可視
28	5	3	A	←可視
29	5	3		←不可視
30	5	3	A	←可視

 A2 =INT((ROW(A1)-1)/6)+1  … そのまま6行おきに連番
 B2 =INT((SUBTOTAL(3,C$2:C2)-1)/6)+1 … 可視セルで6行おきに連番

 こういうこと？

 ※空白セルのフィルタには未対応

 (GobGob)

ありがとうございます
連番と書いたことが　いけませんでした。捕捉させてください。

　
=IF(ISERROR(VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE))
が　たとえばF11に入ってます。

つぎにこの式をF17（６行開けて）に入れると
　=IF(ISERROR(VLOOKUP(B17,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B17,従業員マスタ!$A$1:$S$190,2,FALSE))
となります。
でも　ほんとに入れたいのは、B12なんです。

このセル番地を連番で入れたくても
可視セルで貼り付けても　B17になってしまいます。

しょうがないので　この　参照セル番地を手で　今　一つ一つ直してます。（くう）

 VLOOKUPだけ回答。（例）

 F2セルに

 =VLOOKUP(INDEX(B$2:B2,SUBTOTAL(3,A$2:A2)),従業員マスタ!$A$1:$S$190,2,FALSE)

 ってこと？ (A列でフィルタされていて空白セルでないとしてま）

 (GobGob)

題目にも書いてある通り
　
例えば=IF(ISERROR(VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE))が下記の様にF11に入っていたとする
次にオートフィルターをかけて　可視セルに上記の式を貼り付けると下記の様になってしまう。
勤務表のため　IDから名前を呼び出して（上記の式がその役割です。）

  A  B  C  D  E  F
1 
2 
3 
4 
5
6
7
8
9
10
11　11 　　　　 =IF(ISERROR(VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE))      
12　25
13　36
14　55
15
16
17　　　　　　　=IF(ISERROR(VLOOKUP(B17,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B17,従業員マスタ!$A$1:$S$190,2,FALSE))　　　　　　
18
19
20
21
22
23              =IF(ISERROR(VLOOKUP(B23,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B23,従業員マスタ!$A$1:$S$190,2,FALSE))
24　　　　　　 
25
ここでしたいことは、この検索値がF17では、B17でなくB2
F23では、検索値は、B3である。
フィルターで６行おきに色を付け抽出し式のコピーを貼り付けると上記のように違った参照先になってしまう。
オートフィルターで抽出した表をコピーし　式を入れてから　もとのシートに貼り付けるとうまくいかない。
従業員50人分　この検索値のところを式のB17からB2　B24からB3と打ち直している。
可視セルでオートフィルかけたとしても　この直しからは、出られない。
でも　６行おきに式入れるのだから　検索値を　B11からB12と呼び出せないものかと思った次第です。
（くう）

 イマイチよくわからないんだけど。

 F11 → B1
 F12 → B2
 F13 → B3

 を参照するってこと？

 F11のB11参照を INDEX(B:B,INT((ROW()-11)/6)+1) にする。

 ってこと？

 (GobGob)

上記に書かれた通りの方法ですと簡単にすむのですが　この作った方が相対考えてなくて…
それと、急いで　前回書いたから　説明間違ってました。すみません。
改めて書き直します。

F11 → B1

 F12 → B2
 F13 → B3
ということをわかっていれば　よかったのです。
今　私が直せと言われた（他の社員が行削除やら非表示して狂ったファイル）上司が作ったファイルなんです。

 A  B  C  D  E  F
1 
2 
3 
4 
5
6
7
8
9
10
11　11 　　　　 =IF(ISERROR(VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE))      
12　25
13　36
14　55
15
16
17　　　　　　　=IF(ISERROR(VLOOKUP(B12,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B17,従業員マスタ!$A$1:$S$190,2,FALSE))　　　　　　
18
19
20
21
22
23              =IF(ISERROR(VLOOKUP(B13,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B23,従業員マスタ!$A$1:$S$190,2,FALSE))
24　　　　　　 
25

F11→B11
F17→B12
F23→B13
を検索値としたい。
オートフィルでやっても　可視セルでやっても
F11→B11
F17→B17
F23→B23
となる。当たり前ですが…しょうがないので式をコピーして　可視セルで貼り付けて
一つ一つ検索値を直している状態です。
列を増やして検索値を連番にして　従業員マスタのシートも連番にしてと思ったのです。
上司いわく　そんなややこしいこと　しなくていいから　式を直してとのこと。
たしかに　列を増やすと　ほかのシートも影響受けて　直しの嵐ですが…
連番いれとけば、エラーにもならないのに…
つまり　エラー出たら　式が直さなければならないということは、考えてない模様。

で、６行という　規則性から　なんとかならないものかと思いました。
いろいと式を書いていただいてありがとうございます。

 A2 =INT((ROW(A1)-1)/6)+1  … そのまま6行おきに連番
 B2 =INT((SUBTOTAL(3,C$2:C2)-1)/6)+1 … 可視セルで6行おきに連番
特にsubtotalに感動してます。
上司のおかげで　勉強になります。
　
（くう）

 オートフィルタでなく行の表示/非表示での対応なら

 =SUBTOTAL(103,***

 でできるよ。

 (GobGob)

行は、きれいに直してしまったのです。現在は、すべて表示です。
セルリンクでつくってあるシートもありまして、その行削除のせいでくるってしまってました。
今は、きれいにエクセルの行番号は、直してあります。

subtotal　では、確かに連番はでますが、セル番地はでないのでは？
いろいろ　ためしてみませたが…すみません
わかりませんでした。

（くう）

 どんな作業でこうなってるのか？じぇんじぇん見えないので
 一度条件を整理？すると・・・

 ＞ =IF(ISERROR(VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE))

 ①F11セルにこの数式が「6行おき」に入力されている。
 ②どこかわからへんけどオートフィルタで絞り込む
 ③見た目（可視セル）6行おきが崩れる。
 ④フィルタかけた状態でコピーし、貼り付け（これが何してるかよくわからない）
 ⑤データが「連続データ」で貼り付けされる
 ⑥しかし、数式は「6行おき」のままで連続とならないのでありゃりゃ。

 ってことなの？

 時系列で今一度整理してくれない？

 ちなみに、カウントする関数でセル参照は返せるよ。INDEXなどと組み合わせれば。

 (GobGob)

①F11から6行おき　この式が　はじまってます。
　A1からF10までは、別の内容が書かれているためです。
　例題では、なく　上司がつくったままを時系列にしてます。
②式が入ってる列は、名前を　従業員名簿で出すため　色がついてます
　それを　色でオートフィルターかけると式を入れたいとことだけが抽出されます。
③A1からだと　そうですが　
　この式は　F11から　はじまってます。
④この　行為は、検索値のセル番地が　B11のつぎにB12としたいために
　試しに　別のシートに貼り付けて　式を入れ、オートフィルかけた。
　その後　元のシートに貼り付けたけど、エラーがでたということなんです。
⑤データではなく　Vlookの検索値のセル番地がB11→B12→B13としたいのです。
　可視セル（②で色フィルターかけた後の表に）
　しかし　実際は、B11→B17→B23
⑥F11から始まってます。

 A  B  C  D  E  F
1 
2 
3 
4 
5
6
7
8
9
10　↓これはその月の　担当社員の社員番号　本当は、もっと長い
11　11 　　　　 =IF(ISERROR(VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B11,従業員マスタ!$A$1:$S$190,2,FALSE))      
不　25　　　　　　　　　　　　　　　
不　36
不　55
不
17　　　　　　　=IF(ISERROR(VLOOKUP(B12←上の式を貼り付けるとB17になります。ここをB12としたいのです,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B12,従業員マスタ!$A$1:$S$190,2,FALSE))
不
不                                 
不
不
23               =IF(ISERROR(VLOOKUP(B13←上の式を貼り付けるとB23になります。ここをB13としたいのです,従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(B13,従業員マスタ!$A$1:$S$190,2,FALSE))
上記の不は不可視の列です。式をいれたらすべて可視にフィルターを戻します。
ところで　気になったのですが　
６行おきでなく　５行おきっていうのかしら？もし　間違ってたらこの表の通りです。
　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　
勤務表なのです
それも　早出　遅出というような　簡単なものでないのです。(くう)

 オートフィルタで絞り込んだデータを別の場所にコピペして戻す。

 この作業が悪さしてるってことでOK？

 このデータはもともと5行おきにあったデータで
 別の場所にコピペ後、元の表に貼り付けした結果。

 ってこと？

 んで、この結果は連続だけど、数式は行の連鎖性が崩れるけど5行おきのままで処理したい

 ってことかいな？

 ん～まだよくわかってないけど。

  (GobGob)

 VLOOKUP関数の、B列を参照している所が
 INDEX(B:B,INT(ROW()/6)+10)
 に変更では？

 結局
 ＞A2 =INT((ROW(A1)-1)/6)+1  … そのまま6行おきに連番
 と同じ考えですが。。。

 (HANA)

 オイラもHANAさん回答でOKかと思ってたんだけど、

 「B列データが連続でもn行おきでもF列n行おき数式で対応できる」ってのが
 最終目標のような気がしてならないんすよねぇ。。。

 (GobGob)

 う～ん。。。
 ＞F11→B11 
 ＞F17→B12 
 ＞F23→B13 
 ＞を検索値としたい。
 って書いてありますしねぇ。。。

 最初にGobGobさんが載せられた式だと、F11から始めると
 B1,B2,B3・・・って参照してしまいますでしょ？

 私が感じたイメージとしては
 　F列以降に6行毎の印刷様式が設定されており、6行毎に名前を入れていかなきゃいけない。
 　B列に印刷したい社員番号がずらずらっと入力してあるので、コピーしただけで勝手に順番に参照して欲しい。
 　今は、普通に貼りつけただけだと、6行毎に参照してしまうので。
 って雰囲気なんですが。

 もしかしたら、B列はB列の様式があってとかその他の事情で
 途中で空セルをはさむのかもしれませんが。。。今の所そんな説明見当たらないですよね？

 (HANA)

いろいろ　ありがとうございます。

とりあえず　言えることは、この上司の作ったシートは、式が盛りだくさん(笑)

で　妙に入り組んでいる。

式で対応しているより、セルリンクが多いため　非表示、削除したら…

狂う　狂う状態なんです。

おまけに　F11から始まってるし…でなかったら　教えてもらった式で　出来たのですが…

手直ししてますが…エラー出やすいから　エラーの対処の式入れてますが…
このエラー出たら式の入れ直し状態なんです。

やはり、ここはあきらめて　コピーして貼り付けで　検索値をなおしたほうが良いみたいです。

HANAさんの　おっしゃられる通り　空列あります。（なるほど　空列も注意ですね。）

GodGodさん　そこまで　考えられる余裕が　私には、ないです。とにかく６行ごとに名前をいれたい。
でも　検索値が違うのでなおす。の５０回繰り返し。　いろいろ　address　Cell　とGodGodさんからのSubtotalでやったけど　うまくいかない…　挫折。
手直し　必至です。この表　F11の上にある　表を別のシートに移動したいと言ったら…だめでした。
セルリンクの嵐です。
このセルリンクも曲者で　シート丸ごとでなく　１行飛ばしてでもなく　F12からAI12を飛ばして
F13からF13はリンクでという　ややこしさです。って　いうか　社員IDは　アルバイトと社員のごちゃまぜ。原因　Vlookって昇順でのもの。だから　勤務表では、その順序でなく地位順で名前を　６行おきにいれる。という　ややこしさ。
社員番号から名前　名前から雇用名または、役名ってな感じです。参照が重複してます。と警告もあります。
ここに　そのファイル貼り付けたい心境です。できないけど…

でも　今回　できないこと理解したので　　手直し　式を手入れでがんばります。セル番地に拘っていたけど、INDEX(B:B,INT(ROW()/6)+10)で感動しました。
空列あります。もしかしたら　それで　出来なかったのかも。家で試してみます。

それ　以前か…なにせ　エクセル2003で作ったものを　エクセル2010で編集したらしい。

ほんと　ご親切にありがとうございました。勉強になりました。
それと　表はシートごとに作ると効率よしということも勉強になりました。

ありがとうございました。
（くう）

 ＞おまけに　F11から始まってるし…でなかったら　教えてもらった式で　出来たのですが… 
 この発言が「出来ないと思い込んでるだけでは？？」と思っちゃうのですが？

 6行毎に参照する式が使えない理由として「11行目から始まっているから」をあげるなら
 それは関係ないですよ。

 その他の理由で使えないなら、なぜ使えないかを説明してもらえないとわかりません。
 もしも「F11から始まっていて使えない」と言うのなら
 「○○になってほしいのに、その式を入れると××になるから」の様に書いてもらえると良いかもしれません。

 もちろん、たとえば
 　F17に式を入れていても、17行目が削除されて 18行目が17行目になった時
 　新しいF17に式が入ってないから使えない
 ってのは、「6行毎に参照したい」と言うご希望とは、また違った問題になると思います。

 ＞Vlookって昇順でのもの。
 今回ご呈示の式は、検索の型が「FALSE」になっていますので、参照範囲(従業員マスタ)は昇順でなくて構いませんが。

 ＞勤務表では、その順序でなく地位順で名前を　６行おきにいれる。という　ややこしさ。 
 むしろ、従業員マスタの並びとF列の並びをかえたいとき（F列に好きな順で並べたい時）に
 VLOOKUP関数なんて使うと思いますので、「6行おき」と言うのは少し面倒ですが
 「その順序でなく」と言うのは、普通の事だと思います。

 くうさんのご説明から「大変だ！大変だ！」ってのは伝わりますが
 何がどうなっているのが大変だと言っているのか、わかりません。
 「あれも大変、これも大変」と言うのではなく、一つずつ説明してもらえると良いのですが？

 上の方に書いておられた
 ＞F11→B11 
 ＞F17→B12 
 ＞F23→B13 
 ＞を検索値としたい。
 と言うご希望は満たされていますよね？
 でしたら、少なくともこの部分に関しては
 ＞コピーして貼り付けで　検索値をなおしたほうが良いみたいです。
 は不要になりますよね？

 B11,B12,B13と参照にならないのでしょうか？
 一つずつ、何がやりたいか。出来たか、出来ないか。やっていきませんか？

 (HANA)

HANAさん　とてもありがとうございます！！後日談です。

もしかすると…それ以前の問題かと　思ってきました。

試しに式をそのシートに貼り付けると　=ADDRESS(1,1)←たとえば　こんなの　貼り付けるとそのまま　式が表示されるセルとちゃんと計算してくれるセルの２通り。次に＝B4&B5は　そのセルには、何も入ってないからできないと警告または式がそのまま表示または、ちゃんと住所が青梅市新町と出るの３通り。

「大変だわ」は、　勤務表の使い方わかってない方が、式を消されたり　非表示にされてたりしてます。エラー値があらゆるところに点々とある。そして、エクセルの機能の試行が盛りだくさん…　おまけに　シートロックされてたり　表の途中でグループ化したあるし…面白いことになってます。

できないと思い込んでいるも当りです。…でも！ほんと　できないんですよ。あれ？なんで？となるくらい。
私、わけわかんなくなってました。それを　HANAさんに　気が付かれてしまった。

上司に　説明しますと。それは　君の勘違いとか思い込みといわれ
F11からだから　出来ないんだとか。エクセルってシートごとの表が適切なのかと思いました。
というのは、参照セルありません。とか参照重複してます…などとエラーの説明ボックス登場しまして。上司とパソコンから私は、まちがっていると連呼されて、頭にきて、　左の　数字を500まで　一つ一つ　数えて…みたら！行数字が空欄！を発見！数字が隠れて見えないとか非表示とかでなく、数字が書かれてないのです。　
行自体ないのもありまして、調べるとセル高さ0発見しました。シート選択をし、行高さを直したら　ものすごい隠れてました。それでも　左の行数字がいくつか…空欄がなおりません。行自体は、たかさ15ポイントにしても　行番号空欄。
非表示でもない。とても　不思議。
「その順序でなく」と言うのは、普通の事だと思います。確かに、そうなんですが、アルバイトと社員の番号が混ざった発行は、各社員に発行書類とアルバイトの書類が混ざって届くという結果になるんですよ。本社は、ID順でも　支社は、地位順。それを　並び替えするんです。時たま　社員になのかあるバイトなのか　即座に判断できないものは。読んで仕訳します。どちらにも　考えようには取れるときは、電話して確認。
ID発行時に　別々アルバイトと社員になっているなら　たとえば、0から500は社員に　501から999がアルバイトとなると届く書類が混ざらないという。希望が盛り込んでありました。

 B11,B12,B13と参照にならないのでしょうか？↓
教えて頂いた。(INDEX(B:B,INT(ROW()/6)+10)は　式だけ計算してみると希望道理できている気がしまして嬉しく試したら。パソコンから参照ありませんとか上司曰く　違うんじゃないと。
なんでだああああああ。となりました。でも　どうしても　出来ている気がして…この　エクセルの学校に戻ってきて　マイぱそこんで試しました。

=IF(ISERROR(VLOOKUP(INDEX(B:B,INT(ROW()/6)+10),従業員マスタ!$A$1:$S$190,2,FALSE)),"",VLOOKUP(INDEX(B:B,INT(ROW()/6)+10),従業員マスタ!$A$1:$S$190,2,FALSE))
できました。　なんの　エラーもなくすんなりとできました。完璧にできました。

来週　こっそり新しいファイルに作り治します。
HANAさん　ありがとうございます。

すみません。それと　一つ教えて頂きたいのですが　時系列書くとき
プレビューでは、上手く表示されてもWeb上では、時系列が狂っちゃうのですが。
どんな風に　皆さんは、時系列書いてますのでしょうか？
試しに　エクセルの例のG11の内容を貼り付けてます。
貼り付け方　プレーンテキスト
日付 2/11 2/12 2/13 2/14 2/15 2/16 2/17 2/18
曜日月火水木金土日月
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等
普通の貼り付け
日付 2/11 2/12 2/13 2/14 2/15 2/16 2/17 2/18
曜日月火水木金土日月
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等
入力欄
指定
入力欄
変更
入力欄
休日等

（くう）

 結局、解決した（あるいは解決しそう）って事かな。。。？

 挿入や削除で、参照先がずれたり　#REF!エラーが出たりするとの事ですが
 それを「もともと参照していたセル番地」に直すのでしょうか？
 それとも、データがどこに移動されたのかを見て　そのセル番地に変更するのでしょうか？

 ＞試しに式をそのシートに貼り付けると　=ADDRESS(1,1)←たとえば　こんなの　貼り付けると
 ＞そのまま　式が表示されるセルとちゃんと計算してくれるセルの２通り。
 これは、セルの表示形式が「文字列」になっているセルと　そうでないセルの違いだと思います。
 セルの表示形式が「文字列」になっていると、「=」で始まる入力をしても
 文字とみなされてそのまま表示されます。

 ＞次に＝B4&B5は　そのセルには、何も入ってないからできないと警告または
 ＞式がそのまま表示または、ちゃんと住所が青梅市新町と出るの３通り。
 警告は、もしかしたら入力規則が設定されているかもしれません。

 気合を入れて、新しいブックで作り直すことをお勧めします。
 ＞それでも　左の行数字がいくつか…空欄がなおりません。
 ＞行自体は、たかさ15ポイントにしても　行番号空欄。 
 このあたりが非常に不気味に思います。

 その際は、遺恨を残さない為に、コピペではなく
 元のシートを見ながら　一つずつ入力で作り直す
 位の心意気で臨んでもらえると良いと思います。

 ＞プレビューでは、上手く表示されてもWeb上では、時系列が狂っちゃうのですが。 
 に関しては　まずは
_←部分に半角スペースを入れてみて下さい。
 プレビュー画面で確認してもらうと、
 くうさんの書き込みがピンク色の一番左端から始まっていて
 私の書き込みは、半角空いて始まっているのがわかりやすいと思います。

 それと合わせて、一つのセルに入れる文字数を減らしてみるのも効果があるかもしれません。

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20130528094927]] 『可視セルのみオートフィル』（くう） ページの最後に飛ぶ

[[20130528094927]]　『可視セルのみオートフィル』（くう）　ページの最後に飛ぶ