出産前後の痔にはご注意!

エクセル2002を使用しています。
別シートに算出した合計金額を、シートを替えた表に、各桁をセルに振り分けたいのですが、合計金額が特定のセルである場合はできました。

合計する元データは、少ないときは1件、多いときは150件以上あるので、合計金額を算出するセルは行が変動します。

このようなときに、関数式に組み込む方法を教えていただきたいのです。

MAX関数やMATCH関数を使えばよいとアドバイスをいただいたのですが、それでもよく分からなく、質問させていただきます。

現在のシート等の状況です。

【合計金額を算出しているシート】
シート名・・・・・・・差込データ
合計金額を算出しているセル・・・・・・・行G(現在はセルG152にあります)
合計金額としての項目名・・・・・・・・・列Bに”合計”と表示しています。

【各桁に分けるシート】・・・・・印刷用の様式になっています。
シート名・・・・・・・金種連絡票
入力セル・・・・・・・セルH9からセルP9の9桁
現在、桁を分割するために使用している関数式(*********は半角スペース9個です)
セルH9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,9),1,1))
セルI9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,8),1,1))
セルJ9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,7),1,1))
セルK9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,6),1,1))
セルL9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,5),1,1))
セルM9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,4),1,1))
セルN9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,3),1,1))
セルO9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,2),1,1))
セルP9・・・・・・・=(MID(RIGHT("*********"&差込データ!$G$152,1),1,1))


この関数式の【差込データ!$G$152】を変えればよいのかと思ったのですが、うまく行きません。

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

このQ&Aに関連する最新のQ&A

A 回答 (3件)

差込データ!$G$152を次の数式に変更してお試し下さい。


因みにB列に合計の文字列が重複していない事を前提にしています。
VLOOKUP("合計",差込データ!$B:$G,5,FALSE)
    • good
    • 0
この回答へのお礼

ありがとうございます。
あてはめてみると、最初はできなかったのですが、VLOOKUPの参照列を6に変えて
VLOOKUP("合計",差込データ!$B:$G,6,FALSE)
としたところ、うまく行きました。

たいへんありがとうございました。

お礼日時:2007/11/07 11:18

質問分が長すぎて読む気がしない。

回答者に負担をかけないように。
・「別シートの合計金額欄の値を持ってきたい。データ数で合計金額セルの行は変動する」で良いのではないですか。
・シート名なぞSheet1とかで回答するから、類推して、質問者側で
「差込データ」とかに変えてください。名前が何であろうと、質問に影響しないでしょう。
「各桁に分ける」以下は質問者が類推で考えればよいことではないですか。このコーナーは、まる引き受けの回答を請け負うところで無いはず。
ーー
Sheet2のA,B列に
合計金額1000
とあるとする。
Sheet1の合計金額を入れるセルに
=INDEX(Sheet2!$A$1:$B$100,MATCH("合計金額",Sheet2!$A$1:$A$100),2)
と入れる。$B$100は100行目を越えないと仮定。
ーー
それよりも合計金額を見つけるのが
・合計金額という文字列の同行であることで見つけるのか
・データの最終行やその前の一定行数の前後か
・その他
をしっかり書くべきです。
    • good
    • 0
この回答へのお礼

質問の仕方が下手で申し訳ありません。
詳しく書いたほうが、回答をいただく時も、回答者の方が簡潔な回答を書けると思い、多くの文字を使用してしまいました。

また、質問のポイントを分かりやすく教えていただいてたいへんありがとうございました。

お礼日時:2007/11/07 11:23

シート「差込データ」のセル G152 を選択してから



エクセルの メニュー>挿入>名前>定義
にそのセルに名前を定義づけ  仮に  【 合計 】と名付けます。
※ 参照範囲には 「 =差込データ!$G$152 」と表示されています
で、 OK

次に、シート名 「金種連絡表」の数式を下記のように変更
H9  
=(MID(RIGHT("*********"&合計,9),1,1))

(I9~P9  も 定義した名前に 書き直し)
定義名で表す数式に置き換えれば、解決出来ます。
    • good
    • 0
この回答へのお礼

定義名使うと便利ですね。

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

お礼日時:2007/11/07 11:19

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qシート参照で変数を使いたい(EXCEL)

通常、Excelで別シートのセルを参照する時は「'シート名'!B3」といった数式になりますが、この「シート名」をユーザーに入力してもらうような仕組みを作りたいと考えています。

・あるシートのA3(例)にシート名を入力すると、B4セルに入力されたシートのB3セルの内容が表示される

このようなこと、可能でしょうか?

Aベストアンサー

INDIRECT関数を使うといいでしょう。

セルA1に入力されたシート名の、セルB3を参照する場合

   =INDIRECT(A1& "!B3")


また上記ではセルB3が固定になってますが、
セルA1 に参照したい【シート名】をいれ
セルA2 に参照したい【セル番地】まで入れておくと
なお、使い勝手がよくなるかも知れませんね。

   =INDIRECT(A1& "!" & A2)
 
 
 

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

QExcel 例A(1+9) のように番地のたし足し算の方法を教えてください

Excelで指定したセルの番地番号に+(9*1),+(9*2)と番地の等比数列の計算方法を教えてくださいお願いいたします。

例 A B C D
1 1 =A(1+1)
2 2 =A(1+2)
3 3 =A(1+3)

上のように番地番号を足したいのですがエラーが出てしまします。

Aベストアンサー

=INDIRECT("A"&(1+1))
では?
因みに、(1+1)のところは、数値が入っているセルを指定してもOK。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセル:データが入った最終行の行番号の取得

お世話になります。
A列にいろいろデータが入っているとして、データが入っている一番下(最終行)の行番号をB1のセルに表示させるにはどういう計算式で出来るでしょうか?
A列のデータは数値だけでなく文字列も含みます。また途中空白セルもあります。
A1~A20にデータが入っているときはB1に20と表示させる。
よろしくお願いします。

Aベストアンサー

=MAX(IF(COUNTIF(A:A,"*"),MATCH("",A:A,-1)),IF(COUNT(A:A),MATCH(MAX(A:A)+1,A:A,1)))

Qエクセルで他のシートを動的に参照する(シート名だけ)

http://oshiete1.goo.ne.jp/qa921075.html
に「エクセルで他のシートを動的に参照するには」
という質問で

=INDIRECT(A1&"!"&B1)

と入力することで

例えば
a1="いちまいめ"
b1="d5"

と入っていれば

=いちまいめ!d5

と同じ意味に動的に出来るとありましたが

シート名の参照だけを動的にしたい場合はどうすればよいのでしょうか?

ただし
=INDIRECT(A1&"!d5")とすると
上下左右にコピーした際も d5 が
例えば d6 d7 d8 d9 ... と相対的に変わってくれないのでNGです。
また

=INDIRECT(A1&"!"&B1) のままで
b1 b2 b3 b4 b5
にそれぞれ
"d5" "d6" "d7" "d8" "d9" と入れておく
などという、計算エリアを別にもう1つ使うやり方や
A1から参照する以外の汎用性がないもの(例えばrow()なんかを
使うとコレに陥りやすい)もNGです。 

ごちゃごちゃと注文が多い上に解りにくいですがどうぞご教授
下さいませ。

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

http://oshiete1.goo.ne.jp/qa921075.html
に「エクセルで他のシートを動的に参照するには」
という質問で

=INDIRECT(A1&"!"&B1)

と入力することで

例えば
a1="いちまいめ"
b1="d5"

と入っていれば

=いちまいめ!d5

と同じ意味に動的に出来るとありましたが

シート名の参照だけを動的にしたい場合はどうすればよいのでしょうか?

ただし
=INDIRECT(A1&"!d5")とすると
上下左右にコピーした際も d5 が
例えば d6 d7 d8 d9 ... と相対的に変わってくれないのでNGです。
また

...続きを読む

Aベストアンサー

=INDIRECT(A1&"!"&B"&ROW(A1))
または
=INDIRECT(ADDRESS(ROW(A1),2,,,A1))

=いちまいめ!B1
になります。
上記を下方にずらすとA1がA2,A3と変わり参照先もB2,B3に変化します。

QExcelですが、同一データが複数あるとき、検索して、その全部を抽出する方法

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

<データシート>
 列A  列B    列C 列C
行1 No 会社名   所属 担当者
行2 1 A株式会社  ○事業部  坂下順人
行3 2 A株式会社  △事業部  滿山友人
行4 3 A株式会社  △事業部  目標達子
行5 4 B株式会社  設計部山下清人
行6 5 B株式会社  営業部横浜美人
行7 6 C株式会社  営業部川崎次郎
行8・・・・・・・・・・・・

<検索結果:別のシートにおいて>
行1 会社名 所属  担当者
行2 A株式会社 ○事業部 坂下順人
行3 A株式会社 △事業部 滿山友人
行4 A株式会社 △事業部 目標達子

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお...続きを読む

Aベストアンサー

#03です
>最後のROW(T1)で、T1を参照しておりますが

ROW(T1)は「1番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは2番目のという意味です

#REF!エラーになりますか?
私が示した条件と何か変えていませんか?
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥ (テストしてありますので)

ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います

QExcel関数/数式内のセル番地を別セルの値で変化させたい

Excelの関数についての質問です。

たとえばセルA11に
=sum(A2:A10)
という数式が入っているとします。
ここで、A1に入っている値が「20」であったら、この数式の内容が
=sum(A2:A20)
となるようにコントロールしたいのですが、どのように参照式を作ればよいでしょうか。
なお、実際には複数セルの数式を同時に変化させることを想定しています。ご助言いただければ幸いです。

Aベストアンサー

ごめんなさい、SUMが多かったです・・・
正しくは
=SUM(INDIRECT("A2:A"&A1))
です。

Qエクセルでセルの値を数式の範囲として使うには?

タイトル通りなのですが、エクセルでセルの値を数式中で範囲として使うにはどうすればよいのでしょうか?

例えばA1セルに「1」という値が入っており、B2セルに「10」という値が入っているとします。この時、数式中で、「A1」と「B2」を用いて、「E1からE10まで」を指定することは可能でしょうか。可能でしたらどのような数式を組めば良いのでしょうか? よろしくお願いします。

Aベストアンサー

(1)タイプ1
セル範囲を(見かけは文字列で)入れるタイプの関数の場合
見かけで、普通に文字列で、手作業でセルに式を入れるような番地文字列を作ります。
A1に2、B1に10が入っているとします。
例えば D5に ="A"&A1&":B"&B1
するとD5は A2:B10 となります。
関数を手から入れるときは=SUM(A2:B10) ですから
=SUM(D5) で良さそうですが、そうは行きません。
エクセルに番地として解釈してくださいよ、という意味で
=SUM(INDIRECT(D5))
とします。
これで結果11
(A2:B10に
A列  B列
51
6
しか、入っていないときの例)になります。
(2)タイプ2
結果的に範囲を対象にしますが、列や行(の隔たり)の数などを引数に、それぞれ独立して数として入れるタイプの関数の場合
例OFFSET関数
=SUM(OFFSET($A$1,1,0,9,2))

=SUM(OFFSET($A$2,0,0,B1-1,A1))
セルの値をそのまま数字として考えればよい例。
この場合数の変わりにセル番地を入れると良い。

(1)タイプ1
セル範囲を(見かけは文字列で)入れるタイプの関数の場合
見かけで、普通に文字列で、手作業でセルに式を入れるような番地文字列を作ります。
A1に2、B1に10が入っているとします。
例えば D5に ="A"&A1&":B"&B1
するとD5は A2:B10 となります。
関数を手から入れるときは=SUM(A2:B10) ですから
=SUM(D5) で良さそうですが、そうは行きません。
エクセルに番地として解釈してくださいよ、という意味で
=SUM(INDIRECT(D5))
とします。
これで結果11
(A2:B10に
A列  B列
51
...続きを読む

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む


人気Q&Aランキング