【エクセル】一部列を除いて一括で値貼り付けする方法

質問

こんにちは。
エクセルで下記の通りVlookupにて他のファイルから値を参照し、小計・合計を
出しています。
ただ、参照先ファイルとのマッチングしなかった場合は#N/Aが返ってきてしまいます。
ISNA関数を使用し「0」に置き換えることも考えましたが他ファイルから参照する
値がかなり多く、また参照箇所もバラバラで関数を組み込むだけでかなりの稼動に
なってしまいます。
小計または合計以外について値貼り付けをしたのち、値貼り付けした部分を置換にて
#N/Aを0に置き換える方法が一番効率的ではないかと思っております。
ただ、小計・合計の列を除いて値の貼り付けをする作業もかなりの稼動となってしまいます。
小計・合計の列を非表示にして値貼り付けしたなども試してみましたが、小計・合計について
も値貼り付けされてしまいます。
小計・合計の列以外（一部の列を除いて）一括で値貼り付けする方法はありますでしょうか。
保護の機能も試しましたが上手くいきませんでした。
また別の方法でも構いません。良い方法がありましたらご教授ください。
説明が下手で分かりずらいかもしれませんがどうぞよろしくお願いいたします。

セルA　　セルB 　　　　
　　　　　　　　1　　… 　　Vlookupで他のファイルより参照
　　　　　　#N/A 　… 　　Vlookupで他のファイルより参照
　　　　　　　　3　　… 　　Vlookupで他のファイルより参照
小計　　　#N/A　… 　　上記値を足仕込むSum関数
　　　　　　　　4　　… 　　Vlookupで他のファイルより参照
　　　　　　　　5　　… 　　Vlookupで他のファイルより参照
　　　　　　#N/A 　… 　　Vlookupで他のファイルより参照
小計　　　#N/A　　… 　上記値を足仕込むSum関数
合計　　　#N/A　　… 　合計

回答No.6

KURUMITO

要は小計や合計を求めることが#N/Aなどが有ってできないということでしょうか。それでしたら小計や合計を求めるセル範囲に#N/Aが有っても正しく計算できればよいでしょう。次のようにしてはどうでしょう。
例えばB列ではB1セルからB10セルまでにデータがあるとして、A11セルには小計の文字が、そこでB11には次の式を入力します。

=SUMIF(B1:B10,">0",B1:B10)

続いて、B12セルからB20までにデータがあるとして、A21セルに小計の文字が、そこでB21には次の式を入力します。

=SUMIF(B12:B20,">0",B12:B20)

A22セルには合計の文字があるとします。B22セルには次の式を入力します。

=SUMIF(A1:A21,"小計",B1:B21)

このようにすることで小計や合計には#N/Aが表示されなくなり計算は正しく行われます。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

「稼動」って機械を動かすことだけじゃないんですね。
ただ、使い方は違う気がします。

もう一案。
1列だけの条件で
[Ctrl]+[H]置換 SUM( を SUBTOTL(9,
オートフィルタオプションで #N/A と等しくない

回答No.4

imogasi

質問の意味がわからない。
＝＝＝
結局、適当な実例を作り、何が駒って居るか、その特徴を読者に説明する力が不足しており、これはエクセルを旨く使いこなす術と、表裏一体となっているぐらい大切な修練項目（エクセルを十分勉強しないと質問文もしっかりかけないということ）といつも思う。
ーー
＞セルA　　セルB 　ーー＞列A,列Bではないのか？
データは縦に流れているのか、横方向に流れているのか？
ーー
＞　　1　　… 　　Vlookupで他のファイルより参照
　　　　　　#N/A 　… 　　Vlookupで他のファイルより参照
第１行目１、２行目エラー、３行目関数の結果の値が３と解して良いか。
ーー
VLOOKUP関数の「該当なし」の＃N/Aなら、IF関数でその場合（セルに限り） 0 にしてしまう手はあるのでは？
=IF(ISERROR(VLOOKUP("C",$H$1:$I$2,2,FALSE)),0,VLOOKUP("C",$H$1:$I$2,2,FALSE))のように。
配列数式などを使ってエラーセルを除外できる手もあろうが、大げさ。
例えば、A4が＃N/Aで他は数字で　=SUM(IF(ISNA(A1:A5),0,A1:A5))　　でSHIFT+CTRL+ENTER
＞他ファイルから参照する値がかなり多く、
関係ないのでは。参照する元のセルで既にエラーが出てしまっているのか？
＞また参照箇所もバラバラで関数を組み込むだけでかなりの稼動に
なってしまいます。
この意味（＝実情）がよくわからない。
この点の改良は、改めて質問したら。
＞小計・合計の列を除いて値の貼り付けをする作業もかなりの稼動となってしまいます
稼動ーー＞普通は「手間」や「手数」というのでは。
＞小計・合計の列を除いて値の貼り付けをする
なぜ除いて張り付けする必要があるのか。

回答No.3

chie65536（@chie65535）

以下のようにして、Ｂ列の「列の表示幅」をゼロにして、Ｂ列が見えないようにしてしまいましょう。

セルA　　セルB 　　セルＣ
　　　　　　　　1　　=IF(ISNA(B1),0,B1)
　　　　　　#N/A 　=IF(ISNA(B2),0,B2)
　　　　　　　　3　　=IF(ISNA(B3),0,B3)
小計　　　#N/A　　=SUM(C1:C3)
　　　　　　　　4　　=IF(ISNA(B5),0,B5)
　　　　　　　　5　　=IF(ISNA(B6),0,B6)
　　　　　　#N/A 　=IF(ISNA(B7),0,B7)
小計　　　#N/A　　=SUM(C5:C7)
合計　　　#N/A　　=C4+C8

具体的には、以下のようにします。

１．「列挿入」で、Ｂ列の右に、新しいＣ列を作る

２．Ｃ１セルに「=IF(ISNA(B1),0,B1)」と入力

３．Ｃ１セルを下方向に「セルのコピー」でコピーして、全部の行にコピー

４．小計、合計の行だけ、Ｂ列からＣ列に「セルのコピー」をする

上記の例では、B４セルをＣ４セルにコピー、B８セルをＣ８セルにコピー、B９セルをＣ９セルにコピーする。

すると、Ｂ４セルの「=SUM(B1:B3)」の式が、Ｃ４セルにコピーされ「=SUM(C1:C3）」になる筈。Ｃ８、Ｃ９セルも同様。

５．Ｂ列の「列の幅」を０にして、見えなくする

これが「#N/Aを0に置き換える、最も簡単な方法」になります。

回答No.2

keithin

Ｂ１からＢ３に数字や#N/Aとして小計のＢ４に
=SUMIF(B1:B3,">0")
と計算してみます。
式中の「">0"」の部分は，実際に入ってくる数字に応じて適切に直してください。

＃またはご利用のエクセルのバージョンがご質問で明記されていませんが，
Excel2007以降では
いま
=VLOOKUP(　)
が数字や#N/Aを出しているのを
=IFERROR(VLOOKUP(　),"")
などとしておくのも簡便な方法です。

＃おまけ
#N/Aを残したまま目に見えなくするよう
http://support.microsoft.com/kb/881331/ja
で「書式を使う」方法等も参考にしてみて下さい。

回答No.1

mu2011

ご質問の意図がよくわかりませんので、ご例示の表から結果的にどのような状態になれば良いかを補足して下さい。