[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『行挿入時、セルに入力した式の参照がずれる』（ご近所PG）
お世話になってます。 今回、マクロを使ってはならないシステムがあり悩んでいます。 質問にいたるまでの経緯は長いですが、質問はシンプルです…概要すっとばし可。 概要： 作成したテンプレートのExcelファイルを先方に渡し、編集していただく。 そして編集後のシートを受け取り、アプリで取り込む。 この時の取り込み方法として、ODBC経由でデータを読むという手法をとっています。 （そのアプリが動作するマシンにはExcelをインストールしてはいけない決まり…） ですが、ODBC経由で読み込んだデータの中に、 確かにセルの値は入っているのにもかかわらず、 どういうわけか空欄で取得されるという現象が発生。 さまざまなテストデータから判明した事として、どうやら 同じ列に「文字列」と「数値」が混在していたりすると、 型を特定できない為？なのか、不正と判断された物がNull扱いで取得される様子。 要は型が混在していると問題あり。（おそらく） これの回避方法として、以下のように考えました。 型が混在している事が問題なら、全ての型が一致していれば良いだろう。 なら、全てを文字列化したシートがあれば良い。 Sheet1を編集用シートとし、 Sheet2にはSheet1のデータを引っ張って来て、文字列化する式を埋め込んでおく。 ODBCからはSheet2のデータを読み込む形とする。 という事で以下のようなシートを用意しました。 Sheet1の内容 A B 1 TEST DATA 2 123 456 3 ： Sheet2の内容 A B 1 =""&Sheet1!A1 =""&Sheet1!B1 2 =""&Sheet1!A2 =""&Sheet1!B2 3 =""&Sheet1!A3 =""&Sheet1!B3 ： こうした場合に以下の問題が生じました。（ここらへんから本題） 「Sheet1に対して行挿入を行うと、Sheet2の式が変化する」 すなわち、 Sheet1の内容 A B 1 TEST DATA 2 挿入した行 挿入した行 3 123 456 ： Sheet2の内容 A B 1 =""&Sheet1!A1 =""&Sheet1!B1 2 =""&Sheet1!A3 =""&Sheet1!B3 ←ここより下の数式で、勝手に参照セルが変わる 3 =""&Sheet1!A4 =""&Sheet1!B4 ： 試しにSheet2の式を絶対参照にするなど試してみましたが、やはりずれます。 で、ようやく… 質問： こういった式のズレを回避する方法、設定はあるのでしょうか。 よろしくお願い致します。 環境：WindowsXP,Excel2002 ちなみに… もしODBC経由でExcelファイルを読み込んだ際、同じ現象に遭遇した事がある、 あるいは参考となりそうな情報をお持ちの方が居ましたら、ご教授願えれば幸いです。 （こっちに回答があればなとちょびっと期待して長々と書きました…笑）
ご希望では無い方の回答ですが・・・(^_^A; このようなことでしょうか。 Sheet2のA1 =""&OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1) （川野鮎太郎）
あまりスマートとはいえませんが、 たとえばSheet2のＥ列に Sheet1!A1 Sheet1!A2 と入れておいて、Ａ１には =" "&INDIRECT(E1) では？ (sato)
早速のご回答ありがとうございます。 川野さん おぉ…成る程…目から鱗が… これなら確かにずれませんね。 OFFSETが使えないかなとまではたどり着いたのですが、 ROW、COLUMNを利用する事に思い至らず…3時間くらい試行錯誤してたのですが（笑） いやはやExcelは奥が深いです。 satoさん その方法でも行けました。 実は一応INDIRECTについても検討したのですが、 作業枠無しに、式だけでうまく表現できればと思っていました。 本来、1シートで済ませたい所を苦肉の策として2シートにしている手前、 セル位置を示す情報を持つとさらにデータが重くなるという懸念もありまして… （でも数式が長いと、やっぱり重くなるかな…どっちが軽いんだろう。 って、範囲次第かな…） また、セル範囲も可変の可能性があるので、 その場合の変更箇所は少ない方が好ましいという面もありました。 今回は川野さん提示の式に置き換えて作成してみます。 （ご近所PG）しかしこう反応が早いと、うれしいものですねぇ
追記。補足情報。 =""&OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1) は、A1がずれるような行、列挿入があると結局ずれてしまう点に留意。 今回は1行目は変更無し（タイトル行のため） および列は増やさない決まり、なので今回の処理的には問題なかったです。 その点で、INDIRECTを用いた方法ならずれようが無いので、有用かも。 （ご近所PG）
おはようございます。
＞A1がずれるような行、列挿入があると結局ずれてしまう
そうなんですよね、列方向に式のコピーが出来ないから、OFFSET関数にしたんですけど、
面倒でなければ =""&INDIRECT("Sheet1!A"&ROW()) でも良いかもです。
（川野鮎太郎）
A～Z列までなら、以下の式のコピーでできます。
=""&INDIRECT("Sheet1!"&CHAR(64+COLUMN())&ROW())
AA～AZ列なら、
=""&INDIRECT("Sheet1!A"&CHAR(38+COLUMN())&ROW())
(ちゅうねん)
追加： > セル位置を示す情報を持つとさらにデータが重くなるという懸念もありまして… 以下は、確かめたわけではありませんので「無責任発言」です。 単なる参照に比べ、(種類にもよりますが)計算があると重いように想像します。 各セルで、COLUMN() や ROW() を計算するのは冗長なので、どこかの行と列(非表示に しても可)に、A,B,C…、1,2,3…、と入力しておいて、それを参照する方が軽いかも 知れません。 > 作業枠無しに、式だけでうまく表現できればと思っていました。 には反しますが…。 (ちゅうねん)
おはようございます。今年も最後の週ですね。 川野さん ちゅうねんさん 更なる方法を提示いただき感謝です。
>面倒でなければ =""&INDIRECT("Sheet1!A"&ROW()) でも良いかもです。
>=""&INDIRECT("Sheet1!"&CHAR(64+COLUMN())&ROW())
なるほど、INDIRECTは用は文字列によるセル表記を元に探すのだから
セルに入っている文字列でなくても良い道理ですね。気付かなかった。
更にCHARを組み合わせるとは…
どうにもまだ私は頭が固いというか、ワークシート関数を使いこなせないというか。
A1がらみでのズレが問題になりそうな場合の方法として、提示してみる事にします。
>単なる参照に比べ、(種類にもよりますが)計算があると重いように想像します。 >各セルで、COLUMN() や ROW() を計算するのは冗長なので、どこかの行と列(非表示に >しても可)に、A,B,C…、1,2,3…、と入力しておいて、それを参照する方が軽いかも >知れません。 一応、前回のOFFSETを用いた方法で試してもらってますが、 特に「入力が重い」という問題は連絡を受けていませんので、心配は現在無さそうです。 （ちなみにアプリ側での制限のため、行は最大6500行、列は固定でR列までとなっているようです） これは近年のPC性能に感謝ですかね。 でも先方のPCが古い場合もあるかな…一度古いPCで試して見ます。 一つ補足。 今回懸念していた「重い」という表現は、 シート入力時の動作に関するものではなく「ファイルサイズ」に関しての懸念でした。 そういう意味で作業枠を用意するとサイズが大きくなる、という話でした。 そのファイルをネットワーク上でやり取りすることになるので、 「ファイルサイズが大きい」＝「ダウンロードが遅い」＝「重い」という表現が （事情を知ってる自分としては）自然だったもので…失礼。 どっちとも取れる表現で紛らわしかったですね。 （ご近所PG）
蛇足ですが、 >各セルで、COLUMN() や ROW() を計算するのは冗長なので、どこかの行と列(非表示に >しても可)に、A,B,C…、1,2,3…、と入力しておいて、それを参照する方が軽いかも >知れません。 というのは、表の構成を A B C 1 Sheet1!A Sheet1!B ←この行を作業に使う 2 1 3 2 ↑ この列を作業に使う
とし、B2には =""&INDIRECT(B$1&$A2) を入力してこれを全体にコピーして作る (かつ、作業エリアは非表示)という意図です。
>シート入力時の動作に関するものではなく「ファイルサイズ」に関しての懸念でした。 とのことですが、作業エリアは1行と1列だけなので、ファイルサイズもさほど大きく ならないでしょうし、なるべく計算を省くことで動作が軽快にできると思います。 何より式が単純で他人にも分かりやすいでしょう。 「Sheet1」の部分はどこかのセルを参照するようにすれば、シート名が変わる場合でも 対応できます。 (ちゅうねん)
なるほど、面白いですね。こういう形の作業枠とは想像していませんでした。 応用が利きそう…覚えておこう。 A1にシート名を記載する形なら、見た目も綺麗で解りやすいですね。 （ご近所PG）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.