みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

投稿日時 - 2006-11-16 17:14:31

QNo.2543452

暇なときに回答ください

質問者が選んだベストアンサー

こんばんは。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 掲示板的な回答』です。こういう書き方が、すべてではありません。補助列を使って書いた方がよいことも多いです。]

投稿日時 - 2006-11-16 22:08:10

お礼

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

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

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

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

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

投稿日時 - 2006-11-17 09:40:52

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

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

誤記
=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))

投稿日時 - 2006-11-20 14:02:05

お礼

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

投稿日時 - 2006-11-21 08:57:22

ANo.6

『計』は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))
でそのままエンター

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

投稿日時 - 2006-11-20 13:53:56

ANo.5

こんばんは。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 マクロ関数ですと配列で出力されます。

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

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

投稿日時 - 2006-11-17 19:38:52

お礼

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

投稿日時 - 2006-11-21 08:53:43

ANo.3

あれ~ツ!!

>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~♪♪

投稿日時 - 2006-11-16 17:37:47

ANo.2

次の方法は如何でしょうか。
・仮に抽出範囲は200行としています。
・I1セルに次の数式を設定して下方向にコピーして下さい
・配列数式の為、入力完了時に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))),"")

投稿日時 - 2006-11-16 17:31:03

お礼

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

投稿日時 - 2006-11-17 09:17:10

ANo.1

こんにちは~♪

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

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

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

投稿日時 - 2006-11-16 17:24:00

お礼

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

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

投稿日時 - 2006-11-17 09:12:21

あなたにオススメの質問