エクセル列の削除・挿入と関数、計算式の範囲について
- エクセルのセル保護を使用している場合、列の削除や挿入が制限されます。しかし、保護されたセルと計算式を連動させたい場合、新しいシートに合計だけを表示する表を作成する方法があります。
- 保護されたセルと計算式を連動させるためには、保護されたセルがないシートに合計のみを表示する表を作成し、計算式を入れます。この表には削除や挿入を行っても問題ありません。また、参照先がおかしくならないよう、合計のみの表を作成するシートと元のデータを表示するシートを別々に設定することも重要です。
- もし保護されたセルと計算式を連動させる方法が上手く機能しない場合、エクセルのVBAマクロを使用することも考えられます。VBAマクロを使用すると、より柔軟な条件を設定することが可能ですが、プログラミングの知識が必要です。
- ベストアンサー
エクセル 列の削除・挿入と関数、計算式の範囲について
A B C D E 1 2 2 3 4 2 2 1 1 2 3 5 3 2 5 6 7 2 4 2 2 3 4 4 5 2 1 5 8 6 6 9 11 19 26 19 ←各列合計 sheet1に上記のような表があったとします。 6行目以外は誰でも入力可能です。しかし6行目の各列合計の行は計算式が入っているため、改変できないようにセルを保護します。 本来ならそれで解決なんですが… 列の削除や挿入をしなければならないんです。 セルの保護をしている箇所があると、当然削除は出来ません。 これはどうしようもないものでしょうか? もし上記が無理なら、sheet2に合計のみの表を作って計算式を入れたいのですが、今度は削除や挿入をするため、参照先がおかしくなりエラーが出ます。 何か良い解決方法があれば教えてください。
- sabiiro
- お礼率36% (29/79)
- オフィス系ソフト
- 回答数5
- ありがとう数3
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
#2です。 >もしよろしければマクロで行う手順を教えていただけないでしょうか? Sub GOUKEIRESET() '合計式を再セットするマクロ Dim AN As Integer Range("A1").Select Selection.End(xlDown).Select AN = ActiveCell.Row Range(Selection, Selection.End(xlToRight)).Select Selection.FormulaR1C1 = "=SUM(R[" & (2 - AN) & "]C:R[-1]C)" End Sub
その他の回答 (4)
- zap35
- ベストアンサー率44% (1383/3079)
#03です。実際に試してみました 質問のデータが入力されて、6行目に保護をかけた状態で行を挿入、削除してもSUM関数のセル参照範囲は自動的に変更され正しい結果が得られます。 #01さんの補足でもふれていますが、シートの保護をするときに、「行の挿入」「行の削除」を可能とするよう設定しても#REF!になりますか?
お礼
細かくお答えいただきありがとうございました。 多分、私がしたいのは行ではなく列だったのでうまくいかなかったのだと思います。
補足
なぜでしょう…私のファイルではエラーが出ます。 自動的に修正はされないみたいです。
- zap35
- ベストアンサー率44% (1383/3079)
求めるものが良く理解できません 6行目に数式が入っているとして、3行目が削除された場合は、6行目にあった数式はどのセル範囲を参照するようになるのが希望ですか 同様に3行目と4行目の間に行を挿入した場合はどうなれば良いのですか? もしかしたら式は6行目に固定して1行目:5行目を合計するのですか? その条件が明示されなければマクロも組めません。
補足
sheet2に合計だけを表示する方法をとりたいのです。 普通なら、 =SUM(sheet1!A1:A5) と入力すれば事足りますが、列の削除をすると、 ='1'!#REF! とエラーが出ます。 これをどうにかしたいのです。 よろしくお願い致します。
- mshr1962
- ベストアンサー率39% (7418/18948)
マクロを使うなら、最終行に数式を再設定するとか可能です。 使わないならSheet2で =SUM(Sheet1!A:A) のように列全体を集計するとかすればいいのでは?
補足
列の削除、挿入をすると参照先が変わってエラーになります。 もしよろしければマクロで行う手順を教えていただけないでしょうか?
- akio_myau
- ベストアンサー率34% (515/1480)
EXCELのバージョンが書いていないのですが この質問からするとEXCEL2000あたりを使っているのでしょうか。 EXCEL2002以上のバージョンにアップグレードすれば シートの保護の際に 列の挿入や列の削除が出来るようにするチェックボックスがあります。 アップグレードを検討してはどうでしょうか。
お礼
お答えありがとうございました。
補足
すみません、バージョンはXPです。 列の削除等のチェックボックスの存在は知っていますが、6行目はロックをかけています。 最終的にシートの保護にすると、削除は出来なくなります。
関連するQ&A
- Excelで行挿入しても計算範囲を変えたくない
Excel2003です 例えばセルD200にD$4:D$100とセル範囲を指定した計算式があります。 この表に行を追加したり削除したりすると自動的にセル範囲が修正されますが、このセルの計算式だけ自動修正がされず常にD$4:D$100で固定する方法を教えてください。 困る一例として一番最初のデータ行に新しいデータを追加する場合、4行目を指定して行挿入しデータを入れると計算式はD$5:D$101に変わってしまい新しいデータが無視されてしまいます。 なおセル範囲に名前を付けてやってみましたが同じ結果でした。また行の挿入と削除を使わずデータ内容のクリヤー、移動などで表を更新すれば問題ないことは解っているのですが面倒です。
- ベストアンサー
- オフィス系ソフト
- Excelの行挿入で
Excelのシートにデータがつまっているのですが(これから新しく表を作るということではないということです。)、行の挿入をしたいのです。 このとき、行を選択して、右クリックで「挿入」を選べば挿入自体はできます。 しかし、 既にある表は、行が、A列とB列が結合されているのですが、 単に上記のように挿入すると、 A列B列が分かれている行が挿入されてしまいます。 新しく挿入された行のA列とB列を選択して、「セルの書式設定」の「配置」でセルを結合させてもいいのですが、 いちいちそうしなくても 挿入時点ですでに結合されているようにするにはどうすればよいですか。 (Excel2000)
- 締切済み
- オフィス系ソフト
- Excel97で列の挿入が出来ない
会社でExcel97を使っています。 他の人が作った表に手を加えようとしているのですが、 作った本人が出張で不在の為、質問することにしてみました。 ある集計表で列には「月」が入っています。 9月までしかないので、10月以降の列を増やそうと 思うのですが、列を選択して、右クリック→挿入を クリックすると、以下のようなエラー文が出てきます。 クリアしてみて、と書いてありますが、 クリアはしたくないです(汗) <エラー文> データの消失を防ぐため、 空白でないセルをワークシートの外にシフトする事は出来ません。 はみ出してしまう右側または下側のセルを削除またはクリアして みてください。その後、セルA1を選択し、 ブックを保存して最後のセルをリセットしてください。 または、データを新しい位置に移動し、もう一度行って下さい。 マクロなどは使用しておらず、一見何の変哲もない表です。 ブックやシートの保護はしていないようです。 フィルタもはずしてみましたが同じ文が出ました。 ウィンドウの分割もしていません。 表示が改ページプレビューだったので、標準に戻してみましたが 無意味だったようです(汗) 何が原因なのでしょうか? 同じブック内の他のシートでは、問題なく挿入できます。 解る方がいらっしゃれば、原因と解決法を教えて下さい。
- ベストアンサー
- オフィス系ソフト
- エクセルでの列挿入
OSはWindowsME エクセル2000を使用しています。 既存ファイルにて列の挿入をしようとした所 [データの消失を防ぐため空白でないセルをワークシートの外にシフトする事はできません。…]とメッセージがでました。 まだ 列の余裕は沢山あるのですが、一応、空白の列(~IV行)を選択して、Deieteをし罫線も消しました(見た目的には罫線は初めからありませんでした)が結果は同じで 次に空白の列を削除した所、罫線が出てきました。 この罫線は多分、表を作成する際に 行番号で全列に対して罫線を引いたもの?かとも思いますが、まるで、IV列の外側に列が存在し、内側(~IV列)を削除すると それが出てきている様に見えてしまいます。そんな事は無いとは思うのですが、挿入方法を教えて下さい。 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセルマクロでのセルの削除、挿入
A1:G20の表があります、A1:G17にデータが入っています、20行目は合計欄です。 ここでデータ面の任意のセルをアクティブにしアクティプセルとその右のセル2個をマクロで削除し上に詰めます。(B5がアクティブだとB5、C5、D5が削除) 当然合計欄の当該列のセルが上に移動します。その為18行でセル挿入を行い20行目に合計が表示されるマクロの記載を教えてください。よろしくお願いいたします。
- ベストアンサー
- Visual Basic
- エクセル 列の削除をしたらエラーが出る
いきなりですが、例えばsheet1に =SUM(C3:C17) =SUM(D3:D17) ・ ・ ・ という式が入っています。 sheet2に =SUM(sheet1!C3:C17) =SUM(sheet1!D3:D17) ・ ・ という合計のみの式の入った表があります。 sheet1のD列がいらなくなったので削除することにしました。 そうしたら =SUM(sheet1!#REF!) というエラーが出るようになりました。 参照先が無効になってしまったのです。 列を削除しても数式の参照先を自動に変更してエラーを出さないようにと、列を挿入した際も、数式が自動に変更してエラーが出ないようにしたいのですが無理でしょうか?
- ベストアンサー
- オフィス系ソフト
- Excelで列(行)挿入で計算式を保持する方法
あるセルを参照する際に、セル番号の指定ではなく、 「一つ上のセル」「1つ左のセル」 といった指定がしたいです。(R1C1方式、という意味ではなく) 行のコピー、挿入の影響を受けないようにするのにいい方法はありますか? 例えば・・・ B3のセルに"1"を入力して、 C3~E3のセルにそれぞれ "=B3+1", "=C3+1", "=D3+1" と計算式を入力すると、 B3~E3がそれぞれ "1", "2", "3", "4" となります。 この状態で、"3"の値が表示されているDの列を列ごとコピーして そのままC列とD列の間にコピーした行を挿入します。 すると、B3~F3がそれぞれ "1", "2", "2", "3", "4" となります。 この時点でC3~F3の各セルの計算式は "=B3+1", "=B3+1", "=C3+1", "=E3+1" となっています。 そうではなく、この例の場合こういったオペレーションを行った後にも 「左隣のセルに1を追加」という状態を保持したいです。 この現象をうまく回避できる方法や関数はありますでしょうか? ちなみに、上記のように1列だけコピーして挿入、という操作が前提です。 D,Eの列をコピーして、E,Fの列に貼り付けるとこの現象は回避できますが、 そうではなく、あくまで1列だけコピー&挿入という操作での解決策を探ってます。 説明がわかりにくいかもしれません。すみません。 補足が必要であれば言って下さい。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルでセルの挿入・削除を禁止し、他のセルの列や行がずれないようにし
エクセルでセルの挿入・削除を禁止し、他のセルの列や行がずれないようにしたい。 ■共有データのリスト更新時に、誰かの仕業で列や行の内容が途中からズレて困っています。その為原因となりそうなセルの挿入や削除を規制し、他のセルが上下左右にズレるのを防ぎたいのです。 なおリスト更新時に個々のセルの記載内容は変更出来る必要があります。 シートの保護を応用すれば出来そうな気がしますが、、 具体的な方法を教えてください。 ちなみに皆さんはこんな経験ないですか
- ベストアンサー
- その他MS Office製品
- エクセルでHLOOKUP関数の選択範囲について
エクセルでHLOOKUP関数を使って、検索したいのですが、 シートは、一覧表のシートと データが入っているA101、B203、C305、...シートは300シートくらいあります。 一覧表のシートには、下のような表になっていて、 A列 B列 C列 D列 ... 1003、1004、1005、1006、... 2行目 A101 3行目 B203 4行目 C305 . . . データのはいっているシート、A101は下の表になっています。 B列 C列 D列、・・・、Z列 2行目 1004、1005、1006、... 3行目 100、 200、 150、... 一覧表のB列の2行目には HLOOKUP(B2、シートA2のB2:Z3、2行目、FALSE) という感じで、シート名をセルA2のものを参照にして 探して表示させ、B列、C列、D列の2行目から下の行も 表示させたいのですが、うめくできませんでした。 INDIRECT関数を使ってみましたが、セル範囲が無効という エラーがでてしまいます。↓こんな感じで入力してみたのですが... SUMPRODUCT((INDIRECT($A2&"!$B$2:$Z$3"))=$B$1,(INDIRECT($A2&"!$B$2:$Z$3"))) 1つづつデータを見て手打ちはデータが多く、 どんどんデータが増えていくので できれば関数を使って表示させたいと思っています。 詳しい方いらっしゃいましたら、どうか教えてください よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルで1行目が空白の場合、列を削除したい
こんにちは!質問をお願いします。 エクセルで表を作成していますが、1行目が空白のセルがある場合に、その列を削除したいのですが、VBAでどのようなコードにすればよいでしょうか? ※上記のD列を削除し左詰めにしたい。(空白セルはAからBBまでの間で、特定の位置以降で発生します。) ※ちなみに1行目には、=N というようにほかのセルを参照し、文字の入力があれば表示するようにしています。 初心者につき、うまく説明できていないかもしれませんが、よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
お礼
ありがとうございます。 試してみます。