[[20100427150929]]　『配列数式とＩＮＤＩＲＥＣＴの組み合わせを変えた』（yuyu）　ページの最後に飛ぶ

『配列数式とＩＮＤＩＲＥＣＴの組み合わせを変えたのですが』（yuyu）

 配列数式とINDIRECTの組み合わせがあまり良くないと教えて頂き、何箇所か使っているので直したいのですが、宜しくお願い致しますm(__)m
 それで　この数式は　どう変えればいいでしょうか？全く私は意味がわかりませんが、ﾈｯﾄで質問して教えてもらったものを入れたものです。
 別シートに抽出するようにしてあるのですが、

{IF(SUM(IF(月売上表!$B$3:$B$27=$C$2,1))<ROW()-2,"",INDIRECT("月売上表!J"&SMALL(IF(月売上表!$B$3:$B$27=$C$2,ROW(月売上表!$B$3:$B$27)),ROW()-2)))}

 と、他のファイルですが

 {=IF(SUM(IF(月売上表!$Z$3:$Z$27=A$2,1))<ROW()-2,"",INDIRECT("月売上表!Y"&SMALL(IF(月売上表!$Z$3:$Z$27=A$2,ROW(月売上表!$Z$3:$Z$27)),ROW()-2)))}
 と　又別のファイルで

{=IF(COUNTIF('1月'!$F$3:$F$116,A$2)>=ROW()-2,INDIRECT("1月!I"&SMALL(IF('1月'!$F$3:$F$116=A$2,ROW('1月'!$F$3:$F$116)),ROW()-2)),"")}
　

 この３箇所ですが、どう直したらいいでしょうか？大変にすみませんが　教えていただけませんでしょうかm(__)m　　宜しくお願い致します

 ↓こちらが関連のスレッドですかね？
[[20100423173247]]　『ゼロを表示させないように出来ますでしょうか？』（yuyu）
 勝手に追記しました。違っていたら消して下さい。
 （てつろう）

 >配列数式とINDIRECTの組み合わせがあまり良くないと教えて頂き、
 >何箇所か使っているので直したいのですが、宜しくお願い致しますm(__)m
 なぜその組み合わせが良くないか理由は聞かれましたか？
 もし、理由をお聞きしていないのであればこちらを読んでみてください。
[[20100425193706]]　『配列数式とINDIRECTの組み合わせ』（ken）
 上記を読んだ上で、「別にいいや」と思うならそのままでも良いのでは？

 回答ではなくて申し訳ないです。
 （てつろう）

てつろうさん　ありがとうございます。実は　その過去ログを読んで　変えておこうと思いまして、教えて頂こうと思いました。やはり　重たい感じがあったりして　他の関数で出来るならば教えて頂きたいと思います。宜しくお願い致します

 いまこちらを読み返しましたけど
[[20100423173247]]　『ゼロを表示させないように出来ますでしょうか？』（yuyu）
 skyさんが提示された数式に変えて解決したんですよね？（INDIRECTを使わないで）
 やっぱりだめだったってことですか？
 （てつろう）

(yuyu)
てつろうさん　ありがとうございます。Skyさんが教えて頂いたものは　とても上手くいっているのです。それで、配列数式とINDIRECTの組み合わせで　過去に作ったものがあって、現在もそれを仕事で　使っていますが、重たい感じです。それで、直したいものは、 Skyさんに教えて頂いたシートを　別シートへ拾い上げたのと、それと　あと
二つは　全く別のファイルです。　ありがとうございます。

 教えて頂いた数式がうまくいっているのならば、前回どこをどのように
 変更されているかを確認すれば、今回も同じように変更できると思いますけど。
 （てつろう）

(yuyu)

 すいません３つ目に書いた数式なのですが、Skyさんに教えて頂いたのを見て　意味は判りませんが　大体　でやってみましたが　何か駄目みたいです。何か違いますでしょうか？

 {IF(COUTIF('1月'!$F$4:$F$116,$A$2)<ROW(A1),"",INDEX('1月'!I:I,SMALL(IF('1月'!$F$4:$F$116=$A$2,ROW(A1))))}
 それと（A1）と数式の中に出てきますが、それは何の事でしょうか？　すいませんが宜しくお願い致します

すいません　間違ってました　これを入れたら　#NAME?　と出ます

 {=IF(COUTIF('1月'!$F$4:$F$116,$A$2)<ROW(A1),"",INDEX('1月'!I:I,SMALL(IF('1月'!$F$4:$F$116=$A$2,ROW('1月'!$F$4:$F$116)),ROW(A1))))}
　と入れました。すいませんが　宜しくお願い致しますm(__)m

 > #NAME?　と出ます 
 > COUTIF

 COUNTIF　のスペルを間違えてるからです。それを直してください。

 その要領で他のふたつもできるでしょう。

 > SUM(IF(月売上表!$B$3:$B$27=$C$2,1))

 ↑　の部分も　COUNTIF　にした方がいいです。
　
　
 ただ、INDIRECT を使った元の式と比べればまだマシというだけで
 劇的に軽くなるわけではありませんよ。

 重くなってるのは配列数式を多用してるからでしょう。
 根本的にやり方を見直す必要があるかもしれませんね。

 （sky）

配列数式を利用せずに　このように　文字や数字を別シートに抽出してくることは可能でしょうか？もし出来るならば　すいませんm(__)m　どのように関数を入れたらいいでしょうか？　

 大変すいませんが　教えて頂けませんでしょうか？　なにとぞ宜しくお願い致します

 数式で抽出ということにこだわるなら
 作業列を使えばいいでしょう。

 作業列を使うといってもいろんなやり方があると思いますが
 100行程度ならよく目にする　↓　の方法で

 数式を入れるシートがSheet1、作業列を1月シートのJ列だとすれば

 1月シートのJ3　=IF(F3=Sheet1!$A$2,ROW(),"")　　J116までオートフィル

 =IF(COUNT('1月'!$J$3:$J$116)<ROW(A1),"",INDEX('1月'!I:I,SMALL('1月'!$J$3:$J$116,ROW(A1))))

 普通にEnterだけで確定
　
　
 どこかのセルに　=COUNT('1月'!J3:J116)　と入れておけば
 同じ計算を何度もしなくて済みます。
 仮にそれがC1セルだとすると

 =IF($C$1<ROW(A1),"",INDEX('1月'!I:I,SMALL('1月'!$J$3:$J$116,ROW(A1))))

 普通にEnterだけで確定
　
　
 数式にこだわらないなら、抽出なんて作業はフィルタオプションを
 マクロ記録したものでやればいいと思います。
 それなら確実に軽さを実感できるでしょう。

 （sky）

(yuyu)　Ｓｋｙさん　ありがとうございます。助かりますm(__)m

 １月シートのＫ３に　IF(F3='1月経費'!$A$2,ROW(),"")　と入れて116行までこぴーして、そして　
 1月経費シートの、Ａ３に　=IF(COUNT('1月'!$K$3:$K$116)<ROW(A1),"",INDEX('1月'!$I:$I,SMALL('1月'!$K$3:$K$116,ROW(A1))))　と入れたら、Ａ２に入れてある　
 ガソリン代　をシート1月から　抽出してきました。ありがとうございました。
 それで、1月経費シートには　Ａ２にガソリン　Ｂ２に電話代という感じに　
 Ａ２からＴ２と　Ｉ１５からＴ１５と　Ｉ２７からＴ２７　まで　いろんな名目が付けてあります。
 それらを　１月シートから抽出させたいのですが、Ａ３以外のセルには　何と入れればいいでしょうか？
 1月シートのＫ行を作業列にする場合　何といれればいいでしょうか？

それと、

 >どこかのセルに　=COUNT('1月'!J3:J116)　と入れておけば
 >同じ計算を何度もしなくて済みます。
 >仮にそれがC1セルだとすると

 > =IF($C$1<ROW(A1),"",INDEX('1月'!I:I,SMALL('1月'!$J$3:$J$116,ROW(A1))))

 というのは　1月経費シートの方の　Ｃ１に　上の数式を入れておく…
　という事でしょうか？すみません　この意味が　よく判らなくて…　すみません　宜しくお願い致します

（yuyu)

 Ｓｋｙさん　それと　すみません

[[20100423173247]]　でＳｋｙさんに　教えてもらったのですが、1月シートのＱ４に
｛IF(COUNTBLANK($B$4:$B$116)<ROW(A1),"",INDEX(D:D,SMALL(IF($B$4:$B$116="",ROW($B$4:$B$116)),ROW(A1))))｝

 と
と入れて　1月シートを作りましたが、もしかしてこれも　
 配列数式でない関数で　今回教えて頂いたような　作業列を作る関数で
 出来るのでしょうか？　一つ一つ　文字を入力して、隣のセルに移るまでに2秒位　かかるので重たいのですが　使い勝手はいいのですが、
 配列数式でなくても　これも　他の関数で
 いけますでしょうか？　作業列を作れる行は　ＫからＯが　空いています。
 何か　沢山聞いてしまって　すいませんm(__)m
 どうか宜しくお願い致しますm(__)m

 最後の質問ですが
 > =IF(COUNTBLANK($B$4:$B$116)<ROW(A1),"",
 > INDEX(D:D,SMALL(IF($B$4:$B$116="",ROW($B$4:$B$116)),ROW(A1))))

 これで2秒もかかるっていくら何でも遅すぎるような気がします。
 その数式だけの問題じゃないと思いますが、一応作業列を使うなら
 ※数式はすべて普通にEnterだけで確定

 K3　=COUNT(K4:K116)
 K4　=IF(B4="",ROW(),"")　　K116までオートフィル

 Q4　=IF($K$3<ROW(A1),"",INDEX(D:D,SMALL($K$4:$K$116,ROW(A1))))

 右・下必要範囲にオートフィル
　
 さらに軽くしようと思うなら
 K列に加えてO列も作業列にして

 O4　=IF($K$3<ROW(A1),"",SMALL($K$4:$K$116,ROW(A1)))

 必要範囲まで下にオートフィル

 Q4　=IF($O4="","",INDEX(D:D,$O4))

 右・下必要範囲にオートフィル
　
　
 2つ目の質問
 > 1月経費シートの方の　Ｃ１に　上の数式を入れておく…という事でしょうか？

 上の数式というのが　=COUNT('1月'!J3:J116)　のことなら
 その通りです（C1セルは例です。空いてるセルどこでもかまいません）

 ただし、その質問だけ読めばその通りなんですが
 これは1つ目の質問と関連しますよね。

 その1つ目の質問がまったく理解できないんですけど？
 > 1月経費シートには　Ａ２にガソリン　Ｂ２に電話代という感じに

 A列は1月シートのF列がガソリンになっている行
 B列は1月シートのF列が電話代になっている行
 を抽出するってことですか？

 つまり列ごとに抽出条件が違うんですか？

 B列やC列に入っている、元の INDIRECT の数式を提示してみてください。

 > Ａ２からＴ２と　Ｉ１５からＴ１５と　Ｉ２７からＴ２７　まで
 > いろんな名目が付けてあります。

 もうこのあたりになると、1月経費シートがどうなっていて
 1月シートの何を抽出したいのか全然わかりません。

 （sky）

(yuyu)Ｓｋｙさんありがとうございました。Ｋ列に作業列で入れただけでスラスラいくようになりました！！凄いです！ありがとうございました。

 それと2つ目の質問ですが　判りにくくて申し訳ありませんでした。
 1月経費シートは　
 A2------------H2(A2:H2です)　 I2---------------------------------T2(I2：T2です)
                　　　       　↓　　　　　　　　　　　　　　　　　↓
 ↓　　　↓　　↓　　　        I13                                T13
 ↓　　　↓　　↓

 ↓      ↓　　↓　　　        I15                                T15
 ↓　　　↓　　↓　　　　      ↓　　　　　　　　　　　　　　　　↓
 ↓      ↓　　↓              I25                                I25

 ↓      ↓　　↓              I27                               T27
                               ↓　　　　　　　　　　　　　　 　　↓
 A37---------- H37　　         I37-------------------------------T37
 　
 となっていまして　A2:H2　にｶﾞｿﾘﾝ　とかの見出しが入っいて37行目までに　1月シートを　抽出してきて、38行で　合計金額となります。
 そしてI2：T2　に又　電話代とかの見出しがあって、13行目まであって　14行目で合計金額です
 そしてI15：T15　まで　又見出しがあって　25行まであって　26行目で合計金額です。
 そしてI27：T27　まで　又見出しがあって　37行まであって　38行目で合計金額となっています。

 Ａ３には　{=IF(COUNTIF('1月'!$F$3:$F$116,A$2)>=ROW()-2,INDIRECT("1月!I"&SMALL(IF('1月'!$F$3:$F$116=A$2,ROW('1月'!$F$3:$F$116)),ROW()-2)),"")}
 と入っています。

 Ｉ３には　｛=IF(COUNTIF('1月'!$F$3:$F$116,I$2)>=ROW()-2,INDIRECT("1月!I"&SMALL(IF('1月'!$F$3:$F$116=I$2,ROW('1月'!$F$3:$F$116)),ROW()-2)),"")｝
 と入っています

 Ｉ１６には　{=IF(COUNTIF('1月'!$F$3:$F$116,I$15)>=ROW()-15,INDIRECT("1月!I"&SMALL(IF('1月'!$F$3:$F$116=I$15,ROW('1月'!$F$3:$F$116)),ROW()-15)),"")}

 Ｉ２８には　{=IF(COUNTIF('1月'!$F$3:$F$116,E$27)>=ROW()-27,INDIRECT("1月!I"&SMALL(IF('1月'!$F$3:$F$116=E$27,ROW('1月'!$F$3:$F$116)),ROW()-27)),"")}

 となっています。すいません説明の仕方がが素人なので　ご面倒かと思います。すいません。なにとぞ宜しくお願い致します

(yuyu)
Ｓｋｙさん　すみません。さっき　やってみた時には　サクサクいったのですが、今もう一度１月シートに　入力してみましたが　やはり２秒位かかります。

 それで　作業列をＫだけでなく　Ｏにも教えて頂いたように　作ってやってみましたが　次のセルに移るのに２秒かかります。
 作業列が　Ｋだけのと、ＫとＯの二つにしたのと　いずれも秒数は同じみたいです。　これは　もう仕方ないですよね？

 深みにはまりそうなので、ピボットテーブルやフィルタオプションを使用したほうが良いと思う。
(PON)

 > 今もう一度１月シートに　入力してみましたが　やはり２秒位かかります。

 1月シートの他に2月から12月までシートがあるんですよね？

 2月から12月までのシートは現在どうなってるんですか？
 INDIRECT を使った配列数式が入ったままなら
 1月と同じようにしてみてください。

 それでも2秒もかかりますか？

 （sky）

今　全部の数式を　全部のシートから　消して　このファイルで調べてみましたが　秒数は2秒位かかったので　関数に関係なかったみたいです。すいませんでした。m(__)m　

 1月経費シートの　Ａ３　Ｉ３　Ｉ１６　Ｉ２８　には　関数を　何と入れたらいいでしょうか？
 大変すいませんm(__)m　宜しくお願い致します　m(__)m

(yuyu)
　Ｓｋｙさん　何か今　このファイルの名前を短く直したら　何故かすごく　早くなりました。サクサクいきます。

 ファイルの名前を　私が　長い名前を付けていたのですが、それを短くしたら　凄い早くなりましたm(__)m
 何か関係があるのか　判りませんが　いいみたいです　すいませんm(__)m
 　すいません　あと　1月経費シートの　Ａ３　Ｉ３　Ｉ１６　Ｉ２８　には　関数を　教えていただけますでしょうか？
 大変に　すいみませんm(__)m

 ファイル名を変更したら急に軽くなったというのが
 にわかに信じ難い話なんですが、1月経費シートというのは
 それとはまた別のファイルですか？

 > Ｉ２８には　{=IF(COUNTIF('1月'!$F$3:$F$116,E$27)>=ROW()-27,
 > INDIRECT("1月!I"&SMALL(IF('1月'!$F$3:$F$116=E$27,ROW('1月'!$F$3:$F$116)),ROW()-27)),"")}

 これホントに I28 の数式？　E$27 なんてのが出てきてますけど。

 E$27　→　I$27　の間違いだとして
 要するにどの列も1月シートの I列（金額）の値を費目ごとに抽出するんですよね？

 よくわからないのが
 経費シートに1行ずつ内訳の金額を抽出する必要があるのかってことなんですが。
 費目別の合計金額さえわかればいいなら
 金額を1行ずつ抽出しなくても　SUMIF　でできますよ。

 A2がガソリンなら　↓　がガソリンの合計金額です。

 =SUMIF('1月'!$F$3:$F$116,A2,'1月'!$I$3:$I$116)
　
　
 内訳の金額も1つずつ抽出する必要ありということなら
 仮に1月シートのK列を作業列とした場合

 1月シートのK3　=IF(F3="","",F3&COUNTIF($F$3:F3,F3))　　K116までオートフィル

 A3　=SUMIF('1月'!$K$3:$K$116,A$2&ROW(A1),'1月'!$I$3:$I$116)

 普通にEnterだけで確定し、T3セルまでオートフィル
 A列からH列、I列からT列をそれぞれ必要分下にオートフィル

 I16　=SUMIF('1月'!$K$3:$K$116,I$15&ROW(A1),'1月'!$I$3:$I$116)

 I28　=SUMIF('1月'!$K$3:$K$116,I$27&ROW(A1),'1月'!$I$3:$I$116)

 普通にEnterだけで確定し、必要範囲にオートフィル
　
 オプションのゼロ値のチェックはオフにしてますよね？　
　
　
 レイアウトは今のままでというわけにはいきませんが
 ピボットテーブルでもできます。
 1行ずつ明細も含めてということなら作業列が必要ですけどね。
 全部数式でやるより格段に軽くなると思いますよ。

 （sky）

(yuyu)

 ｓｋｙさん　ありがとうございます！！
 何故か判りませんが　名前が凄い長かったのですが、短くしたら　直っています。不思議です。

 1月シートから1月経費シート　に抽出が綺麗に出来ました!!
 ありがとうございましたーー。
 ところが　1月シートの方の　教えて頂いた数式で　ＰからＷに作ってある表が全部＃ＮＵＭ！　となってしまいました。　
 Ｑ４＝IF($K$3<ROW(A1),"",INDEX(D:D,SMALL($K$4:$K$116,ROW(A1))))  と入れてあり　あとはその数式を　全部にコピーして作った表です。
 すいません　どうしたらいいでしょうか？　宜しくお願い致しますm(__)m

 それ同じファイルなんですか？
 別ファイルだと思ってました。

 K列は経費シートのための作業列ですから
 Q列に入れる数式用の作業列は別に必要です。

 L列も空いてるんですよね？

 項目名を除くデータが3行目と4行目のどっちから始まってるのか
 よくわからないんですけど、4行目から116行目までだとして
 ↓　のように追加・修正してください（普通にEnterだけで確定）

 L3　=COUNT(L4:L116)

 L4　=IF(B4="",ROW(),"")　　L116までオートフィル

 Q4　=IF($L$3<ROW(A1),"",INDEX(D:D,SMALL($L$4:$L$116,ROW(A1))))

 必要範囲にオートフィル

 （sky）

(yuyu)
Ｓｋｙさん　できました！！ありがとうございました！！　本当にお世話になりました。

 本当に助かりました。　これで　仕事が　とてもとても　楽になれます！！！　本当にありがとうございました！！　m(__)m　m(__)m　m(__)m　　ありがとうございました！！！！！

コメント返信：

[ 一覧(最新更新順) ]

[[20100427150929]] 『配列数式とＩＮＤＩＲＥＣＴの組み合わせを変えた』（yuyu） ページの最後に飛ぶ

[[20100427150929]]　『配列数式とＩＮＤＩＲＥＣＴの組み合わせを変えた』（yuyu）　ページの最後に飛ぶ