マンガでよめる痔のこと・薬のこと

数日前にココに質問させていただいて、回答をもらえたのですが
私の説明不足で、よい結果が得られなかったので、もう一度質問させていただきます

countif関数で範囲を等間隔にしたいです、と質問したところ

=SUMPRODUCT((A1:A1000="あ")*(MOD(ROW(A1:A1000),10)=1))
との回答をいただきました

残念ながら、都合でその数式が書かれているセルが
範囲の中に入ってしまっていて、循環になってしまいます

範囲を等間隔に出来れば循環にならないと思い質問させていただいたのですが
そこを書かなかったので、望みの数式になりませんでした

循環にならない方法で何とかなりませんでしょうか?

「エクセル数式・等間隔ごとの範囲にしたい(」の質問画像

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

A 回答 (5件)

こんにちは。

お邪魔します。

> 範囲の中に入ってしまっていて、循環になってしまいます
、、、"なってしまう"というよりは、敢えて循環参照になる位置に数式を置きたい、
という話のようにも思えますが、
基本的にはやはりシートの設計を見直すことを勧めるべきなのでしょうね、、、。

それでも敢えて循環参照を回避しながら参照先と同じ列に求める結果が得られる数式を設定する為には、
飛び飛びのセル範囲を参照先に指定することになりますが、まともにやるとすると、
=SUM(--(CHOOSE(ROW(1:101),A1,A11,A21,A31,A41,A51,A61,A71,A81,A91,A101,A111,A121,A131,A141,A151,A161,A171,A181,A191,A201,A211,A221,A231,A241,A251,A261,A271,A281,A291,A301,A311,A321,A331,A341,A351,A361,A371,A381,A391,A401,A411,A421,A431,A441,A451,A461,A471,A481,A491,A501,A511,A521,A531,A541,A551,A561,A571,A581,A591,A601,A611,A621,A631,A641,A651,A661,A671,A681,A691,A701,A711,A721,A731,A741,A751,A761,A771,A781,A791,A801,A811,A821,A831,A841,A851,A861,A871,A881,A891,A901,A911,A921,A931,A941,A951,A961,A971,A981,A991,A1001)="あ"))
を入力して、Ctrl+Shift+Enterで配列数式として確定するとかでしょうか。
数式を作成する為の数式活用が重要になりますが、
これなら、参照先のセルが他のセルの挿入や切り貼り等で移動されたとしても、
トレースを崩すことがないという意味での堅牢さ、を長所として多少は評価出来るものの、
参照先を変更する等の新たな編集の困難さは大きな短所となってしまいます。
そもそも、こんな冗長な数式を求めている訳ではないですよね?

飛び飛びのセル範囲を参照先に、その飛び飛びのセル範囲の間にあるセルを参照元に、
というのは、かなり特殊な扱い方な訳ですから、対症療法的な対策で済ますのが現実的かも、です。
添付画像のようにA2に数式を置くのなら、
=(A1="あ")+SUMPRODUCT((A11:A1001="あ")*(MOD(ROW(A11:A1001),10)=1))
みたいにするのが状況に対する素直な対応なんだと思います。
=(A1="あ")+SUMPRODUCT((A11:A1001="あ")*(MOD(ROW(A11:A1001),10)=MOD(ROW(A11),10)))
と書くと、上部の行挿入や対象セル以外の行削除、に対応して、ほんの少しだけ保守寄りな感じになりますか、、、。
もし課題の数式を多数のセルに設定したい場合は、却って手数が増えて難しいです。

でも、これまでのやりとりを見ていると、"等間隔"というのが、
"例えば"仮の話としての10行間隔ということだったり、(もしかして行の間隔が増減することもあるとか)
もっと編集が容易な数式を求めているとか、スマートなものを求めているとか、
そんな雰囲気を感じていたりもするのですが、、、。

少し設計の面からの対応にも触れてみますが、
今回の質問文でご提示の元の数式をそのまま活用する手立てとして、ですが、
一旦、A列以外のセル(どこかテキトーな、邪魔にならない、通常見える範囲の外)
にその数式を設定してから、そのセルをコピーして、
A2セルに[リンクされた図](旧バージョンUIでは[図のリンク貼付け])
として貼付けるのはどうでしょう?
表示上(見た目)は添付画像のように、そして循環参照とは無縁な形で、
求める値を配置することが出来ます。
手当てとしては最も簡単ですが、
表示するだけでいいのかどうか、
計算結果を他の数式から参照する場合は、どこに計算結果があるのか解り難いけど構わないか、
とか、これも条件次第、です。
循環参照を回避するような制限の中で難しい数式を考える、必要がないというのはメリットではあります。

最後に、私なら、という話。
循環参照の問題が無かったとしても、私ならユーザー定義関数で対応する場合が多いです。
とりあえず書いてみたので上げておきます。
特長は、一度設定してしまえば制約が少ないこと、数式の書き易さ、条件変更に伴う書換えのし易さ、です。
少しオプションを加えて、ワイルドカードで部分一致等も使えたりします。
設定の仕方他説明が必要でしたら遠慮なく訊いてください。
手順 Alt + F11 → Alt + i → Alt + m
下にあるVBAコードをModule 1(大きな空のシート)に貼り付けて、
Let_UDF_Descriptionを一度だけ実行。
シート上での数式の書き方としては、A2に
=MatchCount_RowSkip("あ",A1:A1001,10)
です。循環参照にはなりません。

Private Sub Let_UDF_Description()
  Application.MacroOptions _
    Macro:="MatchCount_RowSkip", _
    Description:="検査範囲の先頭から 行間隔で指定した等間隔の行位置にある検査範囲内のセル" _
          & vbLf & "すべてを対象に 検査値と一致するセルの数(カウント)を返します", _
    Category:=14, _
    ArgumentDescriptions:=Array( _
           "には カウント対象として 数値 文字列値 を指定します" _
           & vbLf & " 文字列値を指定する場合は" _
           & vbLf & " ワイルドカードとして ? * # が使用可能です", _
 _
           "には 検査値が入力されている【連続したセル範囲】" _
           & vbLf & " 【単列】を指定します", _
 _
           "には 検査範囲の先頭行を起点として" _
           & vbLf & " 【何行おきに】検査するかを指定します" _
           & vbLf & " 省略または1を指定した場合はすべての行を検査します")
End Sub

Public Function MatchCount_RowSkip(ByVal 検査値, ByVal 検査範囲, Optional ByVal 行間隔 As Long = 1)
Dim nEscAdrs As String
Dim nRows As Long
Dim nBottom As Long
Dim nLastRow As Long
Dim cnt As Long
Dim i As Long
Dim blnIsText As Boolean
  If UCase(TypeName(検査範囲)) <> "RANGE" Then
    MatchCount_RowSkip = CVErr(xlErrRef)
    Exit Function
  End If
  If 行間隔 < 1 Then
    MatchCount_RowSkip = CVErr(xlErrValue)
    Exit Function
  End If
  nRows = 検査範囲.Rows.Count
  nBottom = 検査範囲(nRows).Row
  nLastRow = 検査範囲(1).EntireColumn.Cells(Rows.Count).End(xlUp).Row
  If nBottom > nLastRow Then
    nRows = nRows - nBottom + nLastRow
    Set 検査範囲 = 検査範囲.Resize(nRows)
  End If
  blnIsText = VarType(検査値) = vbString
  nEscAdrs = Application.ThisCell.Address
  If blnIsText Then
    For i = 1 To nRows Step 行間隔
      If 検査範囲(i, 1).Address <> nEscAdrs Then
          If 検査範囲(i, 1).Text Like 検査値 Then cnt = cnt + 1
      End If
    Next i
  Else
    For i = 1 To nRows Step 行間隔
      If 検査範囲(i, 1).Address <> nEscAdrs Then
          If 検査範囲(i, 1) = 検査値 Then cnt = cnt + 1
      End If
    Next i
  End If
  MatchCount_RowSkip = cnt
End Function
    • good
    • 0
この回答へのお礼

VBA初心者ですが頑張ってみます

お礼日時:2014/09/10 11:14

>循環にならない方法で何とかなりませんでしょうか?


一般的には範囲指定を連続したセルが対象になるようなデータの配置にします。
あなたの設問では対象範囲の中に結果を求めようとしていますので無理が生じます。
数式を入力するセルのみを除外した範囲を2分割して処理することで対応できます。
A2=SUMPRODUCT((A$1:A1="あ")*(MOD(ROW(A$1:A1),10)=1))+SUMPRODUCT((A3:A$1000="あ")*(MOD(ROW(A3:A$1000),10)=1))
この式をA12セルへコピーすると次のようになります。
A12=SUMPRODUCT((A$1:A11="あ")*(MOD(ROW(A$1:A11),10)=1))+SUMPRODUCT((A13:A$1000="あ")*(MOD(ROW(A13:A$1000),10)=1))
A2セルの式が単独であれば循環を避けられますがA12の数式がA2セルの値に影響を及ぼす可能性があると判断されて循環数式となります。
敢えてSUBSTITUTE関数を強行するには計算方法を変更すれば良いでしょう。
「ツール」→「オプション」→「計算方法」→「反復計算」にチェック→「OK」ボタンをクリック
「最大反復回数」と「変化の最大値」はデータの状況で異なる値を指定する必要があるかも知れません。
「エクセル数式・等間隔ごとの範囲にしたい(」の回答画像5
    • good
    • 0
この回答へのお礼

回答有難う御座います
計算方法の変更は、したことが無いのでちょっと遠慮させていただきます

お礼日時:2014/09/10 11:18

わざわざデータ対象範囲の中に数式を入れることはないと思うのですが、どうしてもそのような位置に数式を入力したいなら、配列定数を利用した数式にします。



具体的には以下の式を入力し、数式バーで「OFFSET($A$1,ROW($1:$100)*10-10,0)」を選択し、F9キーを押して配列定数にすれば、ご希望のデータが表示できます(1000行までのデータの場合)。

=SUMPRODUCT((OFFSET($A$1,ROW($1:$100)*10-10,0)="あ")*1)

ただし、配列定数を利用した場合は、データが変更されると自動的には対応できませんので、上記の数式をどこかのセルに入力しておき、値を変更した場合は、このセルをコピーして上記のF9キーの操作を行う必要があります。
    • good
    • 0
この回答へのお礼

頻繁に値が変わるので無理ですね
関数式を範囲外に置くことにします
残念です

お礼日時:2014/09/10 11:10

こんばんは!



どうしても数式の範囲内に結果を表示させたい場合、循環参照になりますので
関数では無理だと思います。

お示しの画像のような配置(A2セル)に結果を表示させたい場合
VBAになってしまいますが、一例です。

画面左下の操作したいSheet見出し上で右クリック → コードの表示 → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻りマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)

Sub Sample1() 'この行から
Dim i As Long, cnt As Long
For i = 1 To 1000 Step 10
If Cells(i, "A") = "あ" Then
cnt = cnt + 1
End If
Next i
Range("A2") = cnt
End Sub 'この行まで

※ 関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m
    • good
    • 0
この回答へのお礼

VBAはさけたいです
関数式を範囲外に置くことにします
残念です

お礼日時:2014/09/10 11:09

 


   循環関数にならないよう、関数式を範囲外に置く

のが正解と思うんです。
循環関数にならないような関数式を示すのは無理ですから。
(だってどこに関数式を置くのか示されていませんからね)

でなければ関数式を置く場所と範囲を設定したい場所を具体的に示しましょう。
    • good
    • 0
この回答へのお礼

やっぱり関数式を範囲外に置くことにします
残念です

お礼日時:2014/09/10 11:08

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

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

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

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

Qエクセルで等間隔のセルの抽出

例えば、A1~A100にデータが入力されている状態で、A1,A11,A21というように等間隔のデータだけを列Bに空白のセルを作らず連続して抽出したいのですが、どうしたらよいでしょうか。
即ち、A1 → B1,A11 → B2、A21 → B3、・・・・

エクセル不慣れなもので、よろしくお願い致します。

Aベストアンサー

一例です。
■数式で抽出
 B1に=INDEX(A:A,(ROW(A1)-1)*10+1)を入力、下方向にコピー

■数式以外で抽出
 B1に#A1、B2に#A11を入力、A1:A2を選択して下方向にコピー、編集→置換で「#」を「=」に一括置換

Q【エクセル】数式のセル番地を一定間隔で入れたい

シート(1)、シート(2)があります。

シート(2)のA列に、以下のような数式を入れたいです。
  
  A
1 ='シート(1)'!D10
2 ='シート(1)'!D54
3 ='シート(1)'!D98


※44行ごとに下にずれる、イコールの数式です。

数字を入力した時と同じように、
ドラッグコピーで数式も等間隔になってくれるのかなあ?と思ったのですがならないようで・・・

手打ちで地道にセル番号を入力する方法しか思いついていません。
良い方法はないでしょうか?

ご教授いただけると幸いです。

Aベストアンサー

●方法1(非推奨ですが大概皆さんこれを喜ぶ)
A1に
=INDEX(Sheet1!D:D,ROW(A1)*44-34)
と記入し,下向けにコピーする



○方法2(推奨ですが評判は悪い)
A列を空っぽにしておく
A10に
=Sheet1!D10
を記入する
A10:A53を選択する
下向けにオートフィルドラッグする

A列を列選択する
Ctrl+Gを押す
現れたダイアログでセル選択をクリックする
現れたダイアログで空白セルにマークしてOKする
空白のセルが飛び飛びに選択されるので,右クリックして削除で上に詰める。




#方法1は,人に考えて貰ったのをただコピーするだけなら一番簡単ですが,自分で数合わせをして正しい数式を考えるのはメンドクサイと思います。
方法2は,猿にでもアナタが最初に欲しかった結果を残せますが,手を動かすのは嫌いな人が多いです。

Q別シートの値を等間隔で参照するには?

こちらのQ&Aを見て色々と試したんですが、うまくできないので教えてください。

Sheet1のA列に、A8からA23,A38…と15行ごとに氏名が入力されています。
それを、Sheet2のA列にA2からA3,A4…と順番に表示させたいのですが、
どうすればいいでしょうか?
ちなみに、Excel2003を使用しております。よろしくお願いいたします。

Aベストアンサー

Sheet2の
A2 =OFFSET(Sheet1!A$8,(ROW()-2)*15,0)

で、どうでしょう
あとはA3以下にコピーで..

Qエクセルで、等間隔ごとのデータを表示させるいい方法はありませんか?

エクセルで、等間隔ごとのデータを表示させるいい方法はありませんか?

エクセルシートに、A1に1月1日、A2に1月2日というように日にちが並んでいます。データは一年分です。

Cの列に、A列のデータを、一つ飛ばしで表示させようとしています。C1には1月1日、C2には1月3日、C3には1月5日となるようにしたいのです。

現状は関数で、C1には、IF(A1="","",A1)の関数をいれ、C2には
IF(A1="","",A3)、 C3には IF(A1="","",A5)というように、C列の行が増えるたびにA列の行を2つずつ増やすように直接入力しています。

件数が多くなった場合入力がたいへんなので、上記とは違った方法で、楽に表示できるようにしたいのですが、どのような式にしたらよいか分かりません。どうかお知恵をお貸しください。よろしくおねがいしますm(_ _)m

Aベストアンサー

C1には、#IF(A1="","",A1)
C2には、#IF(A1="","",A3)
として
C1とC2を選択
選択枠の右下角の■(フィルハンドル)を下方に必要数ドラッグ
セル範囲が選択状態のまま「編集」メニューの「置換」で
#

=
に「すべて置換」

Qエクセルで数行おきにデータを抽出

エクセルシートで、あるデータ膨大にを出しますよね。
それで、たとえば、三行ごと、四行ごととかであるデータを右の行もしくは左行に抜き出す操作をするにはどうしたらいいですか?
今は、フィルタと可視フィルタを駆使してやっているのですが、一発でバンと出すような方法があると思って・・・数式とかでありそうだなって思って。
教えてください。、

Aベストアンサー

=OFFSET(抽出したい列の一番初めのセルの絶対参照,(ROW(A1)-1)*行間隔,0)
又は
=INDEX(抽出したいセルの範囲の絶対参照,(ROW(A1)-1)*行間隔+1,1)

QEXCELで、セル間隔をあけて貼り付けしたい。

2つのEXCELファイルがあります。
ファイルAのデータが以下のようにありまして、

   A  B  C  D  E
---------------------------------
1| 5  10  3  4  6

この横にならんだ数値をコピーして、ファイルBに

   A  B  C  D  E
---------------------------------
1 | 5
2 | ×
3 | 10
4 | ×
5 | 3
6 | ×
7 | 4
8 | ×
9 | 6

こんな風に縦に、しかもセル間隔をひとつずつ空けてペーストしたいのです。

ファイルAの方の数値はSUBTOTAL関数を使用して出した結果の数値が入ったセルです。
貼り付けしたいセルだけをCtrlキーを押しながら選択して張り付けると 縦のセルの数×ファイルAの数値セルの数 で貼り付けされてしまいます。
「形式を選択して貼り付け」などもやってみましたが、うまくいきません。

なにかよい方法があったら教えていただきたいです。
よろしくお願いします。

2つのEXCELファイルがあります。
ファイルAのデータが以下のようにありまして、

   A  B  C  D  E
---------------------------------
1| 5  10  3  4  6

この横にならんだ数値をコピーして、ファイルBに

   A  B  C  D  E
---------------------------------
1 | 5
2 | ×
3 | 10
4 | ×
5 | 3
6 | ×
7 | 4
8 | ×
9 | 6

こんな風に縦に、しかもセル間隔をひとつずつ空けてペーストしたいのです。

ファイルAの方の数値はSUBTOTAL関...続きを読む

Aベストアンサー

A1=IF(MOD(ROW(),2)=1,INDIRECT(ADDRESS(1,ROW()/2+1,,,"[ファイル名.XLS]シート名")),"")
を下方にコピーでは駄目でしょうか?上記で
=[ファイル名.XLS]シート名!A1
と同じ表示になり偶数行は非表示になります。開始位置がずれる場合は
ADDRESS関数の行数・列数の設定を変更してください。
ADDRESS(行数,列数,参照型,参照形式,シート名)です。
行数は直接1をその行の数値に、列数はROW()/2-1の-1の数値を変更で対応できます。
展開後に確定する場合は式のある列をコピーして、形式を選択して貼り付けで値にしてください。

Qエクセルでセル12個間隔で合計する方法?

A列にタイトルB列以降に横長にたくさんのデータがあります。
これをたとえば
=B2+N2+Z2+~略~+BJ2
のような計算をしたい場合、もっと簡便な関数はないでしょうか?
(VBAならどってことないんですが・・・。ワークシート関数で何とかしたいのです。)

Aベストアンサー

B1,N1,Z1に共通の項目名があるなら
=SUMIF(B$1:BJ$1,"項目名",B2:BJ2)
上記がない場合
=SUMPRODUCT((MOD(COLUMN(B2:BJ2)-2,12)=0)*(B2:BJ2))

QExcelで数行間隔で離れているデータの合計をしたい

10列目から5行づつ離れたデータを合計したいと思っています。
例えばA10に1,A15に2,A20に3....とデータが続いているとします。
この5行離れているというのはかわりません。Excelの組み込み関数で
このような計算をすることができるでしょうか?
VBAを利用して5行間隔、X行間隔でデータを合計するというプログラムは作ることができたのですが、組み込み関数を利用してX行間隔のものを合計するといったことができるのかなと思い質問させていただきました。

Aベストアンサー

例データ
A1:A11
1
2
3
4
5
6
7
8
9
10
11
空きセルに =SUM(IF(MOD(ROW(A1:A15)-1,5)=0,A1:A15))
と入れて、SHIFT、CTRL,ENTERを同時押し。
配列数式。
結果
18
=1+6+11 
--
ROW(A1:A15)-1の部分を細工するとスタートセルを決められる。
また5のところで何行おを左右できる。
ーーー
判りやすいのは,
(作業列B列を使うが)
1
0
0
0
0
をB1:B5まで入れ、コピーして、下まで範囲指定して貼り付け。
ここで
=SUMPRODUCT((A1:A15)*(B1:B15))

Q【エクセル】2行に一行のように、規則的に行を新規挿入したい

お世話になります。

ぎっちり作ってしまったエクセルの表に対して、
2行に1行、空白行を挿入したいと思います。

この場合の作業について、考えてみたものの、
なかなかうまくできません。

お手数ですが、お知恵を拝借させていただければ、
幸いです。よろしくお願い致します

Aベストアンサー

たとえば、100行分のデータがあるとします。

1.まず、A列の前に一列挿入します。
2.A1セルに1、A2セルに3と入力します。
3.A1セルとA2セルを範囲指定し、右下をドラッグして
  下にフィルしていきます。
4.1・3・5・7・9・11・・・・199とデータの行数分
  フィルします。
5.データの終了行の次の行のセル(この場合199の下)に2、
  その下のセルに4を入力します。
6.3番と5番同様の操作で、2・4・6・8・・200まで
  作ります。
7.[ctrl]+[home]を押して、A1セルに移動します
8.shiftを押しながら、[ctrl]と[End]を同時に押します
  すべてのデータが選択されます。
9.メニューバーからデータ(D)-並び替え(S)を選びます。
10.データ範囲の先頭行を「データ(W)」にし、
  最優先されるキーを「A列」に指定して、
  [ok]を押します。

Qエクセルで等間隔のセルをオートフィル機能として使いたい

質問です。

エクセルでA1のセルには「=SEET1!$G1」、B1のセルには「=SEET1!$G36」、C1のセルには「=SEET1!$G71」というように、隣のseetのG列の35ずつずれたセルを反動させていきたいのですが、そのまま打ち込みオートフィルをかけると、この3つのセルの式を繰り返すだけになってしまいます。

このようなオートフィルは不可能なのでしょうか。

よろしくお願いします。

Aベストアンサー

COLUMN関数で列番号を取得して計算させればできます。

A1セルに
=INDIRECT("Sheet1!$G"&(COLUMN()-1)*35+1)
列方向にフィルします。


人気Q&Aランキング

おすすめ情報