Excel 隣接しない列に値貼り付け

質問

Excel　隣接しない列に値貼り付け
※「Excel　隣接しない列に一発で値貼り付け」の書き直しです。

ある一定の書式にしたがって作成された20～30のシートのデータを1つのシートに集約しており（集約シートを含み全てのシートは同じデータ内です。）、データを集約した集約シートの「表A」は下記の添付画像左側のようになります。
やりたいことは、「表A」のB列、C列、D列、E列をある条件のもとに右側の「表B」のH列、J列、L列、N列に値の貼り付けを行いたいのです。

現在の作業工程は、C列の空白行は必要ないため（C列が空白＝他の列も空白）C列に文字入力があるとチェックが入るA列のオートフィルターで、「○」のみ表示にし、B～E列を順にそれぞれ、2行目からデータが記載された最終行までコピーし
「表B」のそれぞれの箇所に値貼り付けを行っています。

この時の注意点として、A列のオートフィルタ利用のためC列には空白がありませんが、B、D、E列には空白が存在します。
データがある最終行以下の空白は必要ありませんが、データ間の空白には意味があります。（下記の図で示すと「B3、4」や「D3、4」）

現在の作業工程でも問題はないのですが、この作業は最初に記載した20～30のシートに記載されたデータの校正に使うため取りこぼしをしたくなく、不特定多数の方が簡単に使えるようにしたいので、どうにか作業工程を減らしたいと考えています。

よろしくお願いします。

▼書式シート
＊シートの数は増減する。
＊集約シートが参照するセルには、必ずデータが入力されているわけではない。
＊入力データは全て数値ではなく文字である。

▼集約シート
＊他のブックと共通使用なため、一部の式は他のブックへのコピーに対応する形である。
＊空白セルを参照している場合に結果として返される「０」は、オプション設定で非表示にて対応
＊「表A」の行長は増減する。
＊貼り付け先「表B」の列は飛び飛びである。

▼ちなみに「表A」に使われている計算式（実際とは一部変えています）
■A列　 ：C列に文字入力がある場合「○」が表示されます
　　　　{=IF(OR($C1>""),"○","")}

■B－E列：他のシートの任意のセルの値をコピー
　　　　{=IF(ISERROR(INDIRECT("'シート名'!セル")),"",INDIRECT("'シート名'!セル"))}

PC環境：Win XP ／ Excel 2003

回答No.6

mu2011 · ベストアンサー

NO3です。
>ちなみに「shift＋ctrl＋enterキーを同時押下」にはどんな意味があるのですか？
　⇒配列数式の宣言です。
　　配列（複数の行、列で構成されるデータ）に対して１つの数式で結果を得る事ができます。

　例えば、SUM関数は指定範囲を計数しますが、A1:B5範囲でＡ列に「○」がつく、Ｂ列の合算は
　=SUM(IF(A1:A5="○",B1:B5))になります。
　一般的にSUMIF関数を選択すると思いますが、SUMとIF関数で同様の結果を得ることができます。

お礼

ありがとうございます。

「配列数式」初めて聞く言葉でした。（初心者すぎますね >_<;）
教えていいただいた内容をより理解するべく、調べているところです。
かなり難しそうですが、使いこなせると作業の幅が広がりそうですね。

ちなみに現時点で分かった事は、配列数式の宣言を行うと、数式の前後に”｛ ｝”がつくことと、作業用の列を使わないで計算できたりすことのみです。先は長いです…

大変勉強になりました。ありがとうございます。

回答No.5

kagakusuki

すみません、ANo.2の数式で、マイナス記号が1ヶ所抜けておりました。

[誤]

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($C:$C,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$2,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))ROW($C$1)),"><")=ROWS($2:2)))))

[正]

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($C:$C,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$2,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))-ROW($C$1)),"><")=ROWS($2:2)))))

　それから、他の列に対して、同様のパターンで変更した数式は以下の通りです。

H2セルの数式

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($B:$B,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$2,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))-ROW($C$1)),"><")=ROWS($2:2)))))

L2セルの数式

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($D:$D,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$2,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))-ROW($C$1)),"><")=ROWS($2:2)))))

N2セルの数式

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($E:$E,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$2,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))-ROW($C$1)),"><")=ROWS($2:2)))))

　尚、上記の修正は、値の表示欄よりも上にあるセルの中に、空欄ではないセルが在るか否かによって、セルの参照先がずれる事に、対処するためのものです。

>ご提示頂いた数式を入力下のですが、何も表示されません。

の件に関しては、当方のパソコン上で、表示が行われている事を確認した上で、投稿しておりますので、明確には解りませんが、もしかして、写された数式の冒頭部分が

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",

ではなく

=IF(ROWS($2:2)>COUNTIF($C$1:$C$1,"><"),"",

になっては、おられないでしょうか？

お礼

何度もご対応ありがとうございます。

表の構造、列の配置、は質問内容とまったく同じで、シートも同一シート内ですが、やはりまったく表示しませんでした。
ご回答頂いた式をコピーした後、式を１つ１つ確認したのですが、違いはありませんでした。たぶん私の側の問題だと思うのですが原因は分かりませんでした。
（新しいシートに表を再作成してトライしましたが結果は同じでした。）

折角ご対応いただいたのに、使いこなせずすみません。

回答No.4

kagakusuki

回答番号:ANo.2です。

>ご提示頂いた数式を入力下のですが、何も表示されません。

　表A及び表Bの各々において、表中の値が表示欄（空欄も含む、項目名欄は含まず）が始まる行番号が、画像の場合と、実際の場合が、異なっているという事は、御座いませんでしょうか？
　或いは、実際には表Aと表Bが異なるSheetになってはいないでしょうか？
　もしも、画像の場合と、実際の場合で、異なっている場合には、原因となる可能性も無きにしも非ずですから、念のために、データの表示欄（空欄も含む、項目名欄は含まず）が始まる行番号やSheet名を、御教え頂く訳にはまいりませんでしょうか？

　それから、質問者様が仰るものとは別の不具合が御座いましたので、J2セルに入力する数式を以下の様に変更された上で、他の列に関しましても、同様のパターンで変更願います。

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($C:$C,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$2,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))ROW($C$1)),"><")=ROWS($2:2)))))

回答No.3

mu2011

NO1です。
回答の通りに数式入力完了時にshift＋ctrl＋enterキーを同時押下していないからだと思います。
現在の状態でH2を選択→F2キー押下→shift＋ctrl＋enterキーを同時押下、H2を下方向にコピーして下さい。

お礼

再度、ご回答ありがとうございます。

＞回答の通りに数式入力完了時にshift＋ctrl＋enterキーを同時押下していないからだと思います。
その通りでした。
全ての列において正しく表示されました！ありがとうございます。
本当にお騒がせしてすみませんでした。

ちなみに「shift＋ctrl＋enterキーを同時押下」にはどんな意味があるのですか？
もしよろしければご教授お願いいたします。

回答No.2

kagakusuki

御質問内容が解り難かったため、確認したいのですが、
「C列に文字列データが入力されている行の、B列、C列、D列、E列の値を、それぞれH列、J列、L列、N列に反映させ、その際には、J列の途中に空欄が無い様に、H列、J列、L列、N列に関しては、行を詰めて表示させる」
だけであると考えて宜しいのでしょうか？

　もしも、そうであれば、以下の様な方法があります。（A列無しで処理可能です）

　まず、J2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($C:$C,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$1,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))),"><")=ROWS($1:2)))))

　尚、この数式中の"゜"の部分は半濁点ですが、入力される筈が無い文字列であれば何でも構いません。

MATCH("゜",$C:$C,-1)

の部分で、存在しない文字列データが入力されているセルの位置を探す事で、最下段の行を特定しています。
　ですから、"は゜"や"ヒ゜"等は構いませんが、"゜"の様に半濁点を単独で、C列のセルに入力すると、正常な動作が出来ませんから、注意して下さい。
　次に、J2セルをコピーして、J3以下に貼り付けて下さい。
　次に、J2セルに入力した数式中のINDEX関数の範囲を、$C:$Cから$B:$Bに変更した、次の数式をH2セルに入力して下さい。

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($B:$B,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$1,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))),"><")=ROWS($1:2)))))

　続いて、H2セルをコピーして、H3以下に貼り付けて下さい。
　次に、J2セルに入力した数式中のINDEX関数の範囲を、$C:$Cから$D:$Dに変更した、次の数式をL2セルに入力して下さい。

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($D:$D,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$1,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))),"><")=ROWS($1:2)))))

　続いて、L2セルをコピーして、L3以下に貼り付けて下さい。
　次に、J2セルに入力した数式中のINDEX関数の範囲を、$C:$Cから$E:$Eに変更した、次の数式をN2セルに入力して下さい。

=IF(ROWS($2:2)>COUNTIF($C:$C,"><")-COUNTIF($C$1:$C$1,"><"),"",INDEX($E:$E,SUMPRODUCT(ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))*(OFFSET($C$2,,,MATCH("゜",$C:$C,-1))<>"")*(COUNTIF(OFFSET($C$1,,,ROW(OFFSET($C$2,,,MATCH("゜",$C:$C,-1)))),"><")=ROWS($1:2)))))

　続いて、N2セルをコピーして、N3以下に貼り付けて下さい。

　以上です。

お礼

お手数取らせてすみません。
ご回答ありがとうございます。

ご提示頂いた数式を入力下のですが、何も表示されません。下方にコピーしてもどの列も表示なしのままです。エラーも出ず、表示もされないので原因がまったく分かりません。

また、"゜"の部分も使用していない他の文字に置き換えてみましたが何も起こりませんでした。なぜなんでしょうか？？

回答No.1

mu2011

一例です。
以下の数式を設定、入力完了時にshift＋ctrl＋enterキーを同時押下、下方向に必要分コピーして下さい。
H2は=IF(COUNTIF($A:$A,"○")>=ROW(A1),INDEX(B:B,SMALL(IF($A$2:$A$65536="○",ROW($A$2:$A$65536),10^6),ROW(A1))),"")
以降は上記数式の「INDEX(B:B」部分の列番号をそれぞれの参照したい列番号に変更して適用して下さい。

お礼

早々のご回答ありがとうございます。

説明下手ですみません。
そうです！A列に「○」が付いているB～E列を貼り付けたいんです。
ご理解頂き感謝いたします。

＞以降は上記数式の「INDEX(B:B」部分の列番号をそれぞれの参照したい列番号に変更して適用して下さい。

早速H列以下に入力いたしました。
結果、H列は下方向にコピー後に上手く表示されたのですが、J列、L列、N列は2行目は表示されるもののそれ以後は「＃NUM!」のエラーが表示されます。でも、2行目だけはどの列も正しく表示されます。
「表A」に入っている数式のせい？でも2行目だけOKっておかしいですよね？