【Excel】取り出した値を順番に表示

質問

Excel2003を使用しています。
A列に『計』と入力された行のGセルの値を取り出して、順番に表示することはできますか？
例えば、A20セル、A24セル、A37セルにそれぞれ『計』と入力されていて、G20セル、G24セル、G37セルにそれぞれ『100』、『200』、『300』と入力されていた場合、どこか別の列、仮にI1セルから順番に『100』、『200』、『300』と表示させたいのですが、関数で可能でしょうか？
F列の『計』は不規則に入っているので、空白行のない状態で順番に表示したいと思っています。
よろしくお願いします。

回答No.4

Wendy02 · ベストアンサー

こんばんは。Wendy02です。

一応、rx-z5815　さんの表の雰囲気を前回、読みきっているつもりです。確かに関数でも出来ますが、累計の計算式を貼り付ける時に、「ついで」という方法もあるわけですね。ただ、どうあるべきである、ということは、こちらでは、なんとも言えませんが、ただ、あまり広範囲にデータがある場合は、マクロかとは思います。

一応、ご質問にしたがって、こちらも数式で考えてみました。

>A20セル、A24セル、A37セルにそれぞれ『計』と入力
たぶん、こちらが「例えば」の方で、

>F列の『計』は不規則に入っているので、
こちらが本当に方ということかな？

経験的に、得てして、こういう作り込みは、後々から、いろんなアイデアが加わってきてしまい、最後にてんこ盛りになって計画倒れになることがあるのですが、それはそれで、決して、無駄にはならないはずです。

F列検索ということで、作りました。

=IF(ROW(A1)>COUNTIF($F$1:$F$100,"計"),"",INDEX($G$1:$G$100,SMALL(INDEX(($F$1:$F$100="計")*ROW($A$1:$A$100),,),COUNTIF($F$1:$F$100,"<>計")+ROW(A1)),1))

これは、F1 からでも、F2 からでも、最初のトップは同じ式で、フィルダウン・コピーして出します。

[ちょっと、本音トークを特別に書いておきます。これは、いわゆる『Q&A 掲示板的な回答』です。こういう書き方が、すべてではありません。補助列を使って書いた方がよいことも多いです。]

お礼

いつもありがとうございます。

>>A20セル、A24セル、A37セルにそれぞれ『計』と入力
>たぶん、こちらが「例えば」の方で、

>>F列の『計』は不規則に入っているので、
>こちらが本当に方ということかな？

おっしゃるとおりです。お恥ずかしい。。(^_^;)
質問を出す前に自分なりにいろいろ考えて試してみたのですが、なかなか思い通りにいかず、退社前に急いで質問文を作成し投稿したので、質問文的には“F列”→“A列”の間違いです。

この質問に限らず、質問文には例として内容を書き、回答をいただいた数式やマクロのコードを単にコピペするだけということはしないようにしています。実際に自分のやりたいことへの参考にさせていただいています。
仕事柄、毎月決まった書類をいくつも作成するので、ひな型がたくさんあります。もっと効率よく仕事ができるよう、いろんな書類を見直している最中です。今後ともよろしくお願いします<(_ _)>

回答No.7

Nouble

訂正
２つ目の式に誤記が見止られました

誤記
=IF(COUNTA(F:F)<ROW(F1),"",OFFSET($L$1,LOOKUP(1,{1},SMALL(($F$1:$F$6535<>"計")*65555+ROW($F$1:$F$6535),ROW(A1)))-1,0,1,1))

修正
…SMALL(($F$1:$F$6535<>"計")*65555+ROW($F$1:$F$6535),…
　　　　　　　　　↓
…SMALL(($A$1:$A$65535<>"計")*65555+ROW($F$1:$F$65535),…

正規
=IF(COUNTA(F:F)<ROW(F1),"",OFFSET($L$1,LOOKUP(1,{1},SMALL(($A$1:$A$65535<>"計")*65555+ROW($F$1:$F$65535),ROW(A1)))-1,0,1,1))

お礼

回答ありがとうございます。
配列数式は書籍等で目にしたことはあるのですが、実際使ったことはありません。
良い機会ですし、教えていただいた配列数式を使ってみようと思います。

回答No.6

Nouble

『計』はA列に,Gセルの値を取り出すものとします

=IF(COUNTA(B:B)<ROW(P1),"",OFFSET($G$1,SMALL(($A$1:$A$65535<>"計")*65555+ROW($A$1:$A$65535),ROW(A1))-1,0,1,1))
とし「CONTROL」+「ALT」+「Enter」で配列数式として確定

または
=IF(COUNTA(F:F)<ROW(F1),"",OFFSET($L$1,LOOKUP(1,{1},SMALL(($F$1:$F$6535<>"計")*65555+ROW($F$1:$F$6535),ROW(A1)))-1,0,1,1))
でそのままエンター

全データ行数の半分くらいまで下へコピーしてください

回答No.5

Wendy02

こんばんは。Wendy02です。

>配列数式は書籍等で目にしたことはあるのですが、実際使ったことはありません。
>良い機会ですので、教えていただいた配列数式を使ってみようと思います。

一度は、覚えておく必要があるかもしれません。私個人は、マクロで利用する時にすごいと思うのです。例を挙げておきます。

'--------------------------------------------
Sub TestList()
'データを作る
Dim i As Long
　For i = 1 To 100
　　Cells(i, 1).Value = i
　Next
End Sub
'--------------------------------------------
Sub GenMacro1()
'奇数の合計
Dim mTotal As Double
Dim i As Long
For i = 1 To 100
　If Cells(i, 1).Value Mod 2 = 1 Then
　　mTotal = mTotal + Cells(i, 1).Value
　End If
Next
　MsgBox mTotal
End Sub
'--------------------------------------------
Sub ArrayFomulaMacro1()
'奇数の合計(配列数式利用)
Dim mTotal As Double
　 mTotal = Evaluate("SUM((MOD(" & Range("A1:A100").Address & ",2)=1)*(" & _
　　　　　　Range("A1:A100").Address & "))")
　 MsgBox mTotal
End Sub

また、(いつまで使えるかは分かりませんが)Ver.4 マクロ関数は、配列で出力しますので、配列数式の知識がないと、歯が立たないのです。例えば、ページの境目の自動改行の入る位置の出力は、Ver.4 マクロ関数ですと配列で出力されます。

ところで、配列数式は、日進月歩とはいいませんが、少しずつ進化しているようです。私も配列は一通り知ってはいたつもりで、新しいものはないと思っていましたが、まだ、あるようですね。誰かが発表すると、またたく間に、その方法は広まっていまきすね。時には、海外まで広まるようです。

ただ、配列数式のパターンは、そんなに多くない(はず)です。

お礼

おはようございます。
マクロでの例まで挙げていただき、ありがとうございます！
配列数式を理解した上で、今回回答いただいたマクロでも試してみようと思います。

回答No.3

rin01

あれ～ツ！！

>A列に『計』と入力された行のGセルの値を取り出して
と
>F列の『計』は不規則に入っているので

A列ではないのでしょうか？

F列でしたら

I1: =IF(COUNTIF(F:F,"計")<ROW(),"",INDEX(G:G,1/LARGE(INDEX(($F$1:$F$100="計")/ROW($1:$100),),ROW())))

です。。。Ms.Rin～♪♪

回答No.2

mu2011

次の方法は如何でしょうか。
・仮に抽出範囲は２００行としています。
・Ｉ１セルに次の数式を設定して下方向にコピーして下さい
・配列数式の為、入力完了時にshift＋ctrl＋enterキーを同時押下して下さい。
=IF(ROW($A1)-1<COUNTIF($F$1:$F$200,"計"),INDEX(G:G,SMALL(IF($F$1:$F$200="計",ROW($F$1:$F$200),65536),ROW($A1))),"")

お礼

回答ありがとうございます。
配列数式は書籍等で目にしたことはあるのですが、実際使ったことはありません。
良い機会ですので、教えていただいた配列数式を使ってみようと思います。

回答No.1

rin01

こんにちは～♪

I1: =IF(COUNTIF(A:A,"計")<ROW(),"",INDEX(G:G,1/LARGE(INDEX(($A$1:$A$100="計")/ROW($1:$100),),ROW())))

下にコピーしてください。
データ行が多いと処理が重くなりますけれど～。。。

。。。Ms.Rinでした～♪♪

お礼

回答ありがとうございます。
データ行数は、そのときどきで違いますが、だいたい300行前後です。
教えていただいた数式で試してみます。

No.3でも再度回答をいただきまして、スミマセン。。
質問文は例として内容を書き、実際したいことへの参考にするようにしていますので、質問文的には“F列”→“A列”の間違いです。
お手数をおかけしました<(_ _)>