プロが教えるわが家の防犯対策術!

質問を見ていただき、ありがとうございます。

1_1から1_10という10枚のシートの、A1のセルを、串刺しで幾何平均(GEOMEAN)したいと思っています。
これを普通に行うと、
=GEOMEAN('1_1:1_10'!A5)
で計算できます。

しかし、今後シートが増えることが予想されますので、シート名を可変にしたいと考えています(つまり、現在はシート名が1_1~1_10ですが、将来的には1_1~1_108などになる可能性があります)。
そこで、INDIRECTを使って、先ほどの式の「1_10」の部分で別セル(B1など)を参照するようにして、
=GEOMEAN(INDIRECT("'1_1"&B1&"'!A1"))
のようにしてみましたが、うまくいきませんでした(B1には、「1_10」という文字列が入っています)。

似たような悩みとしては、以下のご質問がありましたので、もしかすると複数シートをINDIRECTで指定することは不可能なのかもしれません。
http://oshiete.goo.ne.jp/qa/954807.html

そこで、INDIRECTにこだわるものではございませんので、複数シートにわたる幾何平均で、シート名を随時任意に変えられる方法をご存知の方がいらっしゃいましたら、ぜひ教授ください。

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

A 回答 (1件)

「開始」シートと「終了」という名前のシートを作っておいて


計算対象シートをはさめば良いです。増えるだけなら「開始」シートは不要です。
=GEOMEAN(開始:終了!A5)
ただし、常に「開始」シートは「終了」シートより左側にあること
    • good
    • 0
この回答へのお礼

早速のご回答、ありがとうございました。うまく解決することができました。

これは驚きの方法でした。なるほど、範囲指定の仕組みを利用する、こんな方法があったのですね。大変勉強になりました。

お礼日時:2010/12/24 10:07

この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エクセル:シート名を手入力でなく、セル「A1」の文字を出したい。

いつもお世話になります。
エクセルのシート名についての質問です。
いつもはシート名を変えるとき、シートタブの上を右クリックして「変更」しています。

◆そこで、
(1) セル「A1」に入力されてある文字を自動で出す
(2) もしくはマクロボタンを押すと「A1」に入力されてあるものが「シート名」として変わる

というようにしたいのですが、その方法について教えてください。よろしくお願いいたします。

Aベストアンサー

こんにちは。


(1)の場合は、下記のコードを ThisWorkbook に記述してください。
どのワークシートでも機能します。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target.Range("A1").Value
End Sub


(2)場合は、下記のコードを標準モジュールに記述しボタンにマクロ登録してください。
(すべてのシートにボタンを貼り付けるのは面倒でしょうから、ツールバーにボタンとして追加すると良いと思います。)

Public Sub SheetName()
ActiveSheet.Name = Range("A1").Value
End Sub

Qエクセルでシート数が分からない場合の串刺し計算

エクセルで串刺し計算をする場合、データ→統合 等でできますが、ファイルによってシート数が異なる場合でも一発で串刺し計算(合計)できるように、VBAでプログラムを組むにはどのうようにすればよいのでしょうか?
なお、各シートのデータはすべて同じレイアウトで作成されているとします。

Aベストアンサー

2番目から最後までのシートを一番左のシートに合計します。
コメントアウトしている r.Value = r.Value を生かすと値になります。
範囲は選択したセル範囲です。
参考に。

Sub test()
Dim mySum As String, r As Range
 mySum = Worksheets(2).Name & ":" & _
     Worksheets(Worksheets.Count).Name & "!"
 Worksheets(1).Activate
 For Each r In Selection
  r.Value = "=sum(" & mySum & r.Address(0, 0) & ")"
  'r.Value = r.Value
 Next r
End Sub

Qエクセルでシート数が増加する場合の串刺し計算

エクセルで串刺し計算をする場合、データ→統合等でできますが、ファイルによってシート数が毎日増加する場合でも一発で串刺し計算(合計)できるように、VBAでプログラムを組むにはどのうようにすればよいのでしょうか?
なお、各シートのデータはすべて同じレイアウトで作成されているとします。
VBAでプログラムの記入例があれば、助かります。

Aベストアンサー

ユーザ定義関数例です。
シート上でAlt+F11キー押下→挿入→標準モジュール→サンプルコード貼り付けてお試しください。
因みに合計シート名は「合計」として下さい。

合計シートの任意セルに=mycnt(串刺し対象セル)

Function mycnt(rng)
Dim i As Long, tot As Long
Application.Volatile
For i = 1 To Sheets.Count
If Sheets(i).Name <> "合計" Then
tot = tot + Sheets(i).Range(rng.Address)
End If
Next
mycnt = tot
End Function

Qエクセル 0や空白のセルをグラフに反映させない方法

以下の点でどなたかお教えください。

H18.1~H20.12までの毎月の売上高を表に記載し、その表を元にグラフを作成しています。グラフに反映させる表の範囲はH18.1~H20.12の全てです。
そのためまだ経過していない期間のセルが空白になり、そこがグラフに反映され見づらくなります。
データを入力する都度グラフの範囲を変更すればいいのですが、うまく算式や設定等で空白や0円となっているセルをグラフに反映させない方法はありますか?

お手数ですが、よろしくお願いいたします。

Aベストアンサー

売上高のセルは数式で求められているのですよね?
それなら
=IF(現在の数式=0,NA(),現在の数式)
としてみてください。
つまり、0の場合はN/Aエラーにしてしまうんです。N/Aエラーはグラフに反映されません。

QEXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには

お世話になっております。
タイトルの通りの質問なのですが、
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するにはどうすればよいのでしょうか?

たとえば、A1のセルに「ABC」と入力されていたとします。
その「ABC」をヘッダーに自動的に出力できるようにしたいのです。
できればマクロは使いたくありません。

宜しくお願いします。

Aベストアンサー

「表示」メニューの「ヘッダーとフッター」では、セル参照を指定することはできません。
「行タイトル」や「列タイトル」しか使用できません。
「行タイトル」を使用するには「ファイル」メニューの「ページ設定」で「シート」タグを選択してください。
ここで「行タイトル」にA1でしたら1行目を選択すればOKです。
ただし、行丸ごとですので注意してください。

Qエクセルで開いていないbookのセルの値が欲しい

エクセルが少し使える程度です。
1000個のブック内に同じ名前のシートが1個づつできています。
その1000個のシート毎にK列の最大値を求めたいのですが、
1000個のシートを”全て開くことなしに”できないでしょうか?

シート名は整理表から求めることができます。
シート名="C9&"\"&D9&"\["&A9&".xls]sheet1'"

シートが開いていれば、以下で求めることができるのですが。
=max(シート名!$k$2:$k$1250)

Aベストアンサー

ご質問の内容を取り違えていましたらすみません。
状況・課題の確認も兼ねて冗長な説明になるかと思いますがご容赦ください。

まず、開いているブックへの参照は
A:=max([Sample.xls]Sheet1!$k$2:$k$1250)
といった形になりますよね。

ここでリンク元のブックを閉じると、
数式は自動的に
B:=max('D:\***\[Sample.xls]Sheet1'!$k$2:$k$1250)
といった形に変わります。

この状態であれば、閉じたブックの値を参照することができます。
また、Bの形の数式を直接入力した場合でも
閉じたブックの値を参照できます。

問題は、対象のブックが大量にある場合に
Bの形の数式を順次入力していくのは現実的でない
ということだろうと思います。

対象ブックが開いている場合には、indirectを使用して
C:=max(INDIRECT("'"&A1&":\"&B1&"\["&C1&".xls]Sheet1'!$k$2:$k$1250"))
とすれば参照を動的に生成することができますが、
indirectでは閉じたブックを参照することはできません。

で、ようやく対策ですが、
Bの形の数式自体を文字列として生成し、
D:="=max('"&A1&":\"&B1&"\["&C1&".xls]Sheet1'!$k$2:$k$1250)"
メモ帳等を経由して貼り付けることで、
イの形の数式を一気に入力することができます。
(エクセルからエクセルへ直接の値貼り付けや数式貼り付けでは不可)

ただし、対象のブックが動いた場合には
その都度参照を貼りなおす必要がありますので、
対象のブックが頻繁に動くような場合には、
マクロを利用するほかはなかろうと思います。
1.#1さんの方法で直接参照する。
2.「整理表」の変更をイベントで取得して参照数式を書き換える。
3.リンク元ブックをバックグラウンドで開いてしまう(!)
といったアプローチが考えられます。

ご質問の内容を取り違えていましたらすみません。
状況・課題の確認も兼ねて冗長な説明になるかと思いますがご容赦ください。

まず、開いているブックへの参照は
A:=max([Sample.xls]Sheet1!$k$2:$k$1250)
といった形になりますよね。

ここでリンク元のブックを閉じると、
数式は自動的に
B:=max('D:\***\[Sample.xls]Sheet1'!$k$2:$k$1250)
といった形に変わります。

この状態であれば、閉じたブックの値を参照することができます。
また、Bの形の数式を直接入力した場合でも
閉じたブッ...続きを読む

QExcel 関数返り値を「値」に

Excel の関数の返り値そのものを数値に置き換える関数はないのでしょうか。

たとえば、=VLOOKUP()でみているセル値をそのまま「値」に置き換える。
     (LOOKUPの対象が変わっても、前の値を保持するため)など

関数がドグサければマクロでも。

教えて下さい。よろしくお願いします。

Aベストアンサー

>数値に置き換える関数はないのでしょうか
ありません。

そのセルをコピーして、「編集」→「形式を選択して貼り付け」→「値」で式ではなく値に変換することは可能です。
それをマクロにすると以下のようになります
Sub Macro2()
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues
 Application.CutCopyMode = False
End Sub

私もこのマクロの貼り付け部分だけのマクロをショートカットキーに割り付けて使用していますが、結構重宝します。
またマクロでVLOOKUP関数相当の処理を行って、セルに値を書き込むことも可能ですが、質問内容だけではマクロにできません。

ところで「ドグサければ」って何でしょう

QEXCEL関数でシート名を変数にする。

EXCEL関数で以下の事をしたく色々ためしたり過去の質問も
見たのですが分かりませんので教えてください。

一つのファイル内にシート1、シート2、シート3・・・のシートがあります。
シート3のA1のセルに =変数A1の式を入れたい。
変数A1の意味は、他のファイルのあるセルにシート1かシート2…を
入れておきその値でシート1のA1から値を読むかシート2…のA1から
値を読み込みかという事をしたのですがどのようにすればよいですか?
二つくらいだとif文でも出来るのですが、多数のシートを対象としているので変数で処理をしたいのですがどのようにすればよろしいでしょうか?

=[別ファイル]シート4!A4!A1
のような式で、[別ファイル]シート4!A4のセルに入っているシート名で
読み込むセルのシートを変えたいと言うことです。

分かり辛い説明で申し訳ありません。

Aベストアンサー

これでどうですか?

=INDIRECT("[ブック1.xls]" &[別ファイル]Sheet4!$A$4&"!A"&ROW())

ROW()は数式が入力されている行の行番号を調べる関数です。
1行目でこの数式を使うと1が戻ってきます。
2行目でこの数式を使うと2が戻ってきます。

Q【Excel】 すべてのシートのA1を合計する式

こんにちは

何枚かのシートがありますが、
それら全シートのA1セル値を合計する式を教えてください。

=SUM(Sheet1!A1,Sheet2!A1,・・・・)
ということではなく、このブック内の全シートのA1の合計という式は無いでしょうか。

毎月シートが増えていきますが、式を変更しないようにしたいのです。

Excel2003です。
よろしくお願いします。

Aベストアンサー

書き方としては、
=SUM(Sheet1:Sheet5!A1)
の様になります。このとき間に挟まれているシートが対象になります。
なので、最初のシートを決めたら一番最後にダミーのシートを入れて最初のシートとダミーのシートの間に新しいシートを増やしていけばOKでしょう。


人気Q&Aランキング