[[20100427150929]] 『配列数式とINDIRECTの組み合わせを変えた』(yuyu) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『配列数式とINDIRECTの組み合わせを変えたのですが』(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)),"")}
 

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

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

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

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

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

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

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

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

(yuyu)
 すいません3つ目に書いた数式なのですが、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) Skyさん ありがとうございます。助かりますm(__)m
 1月シートのK3に IF(F3='1月経費'!$A$2,ROW(),"") と入れて116行までこぴーして、そして 
 1月経費シートの、A3に =IF(COUNT('1月'!$K$3:$K$116)<ROW(A1),"",INDEX('1月'!$I:$I,SMALL('1月'!$K$3:$K$116,ROW(A1)))) と入れたら、A2に入れてある 
 ガソリン代 をシート1月から 抽出してきました。ありがとうございました。
 それで、1月経費シートには A2にガソリン B2に電話代という感じに 
 A2からT2と I15からT15と I27からT27 まで いろんな名目が付けてあります。
 それらを 1月シートから抽出させたいのですが、A3以外のセルには 何と入れればいいでしょうか?
 1月シートのK行を作業列にする場合 何といれればいいでしょうか?

それと、

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

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

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

(yuyu)
 Skyさん それと すみません

[[20100423173247]] でSkyさんに 教えてもらったのですが、1月シートのQ4に
{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秒位 かかるので重たいのですが 使い勝手はいいのですが、
 配列数式でなくても これも 他の関数で
 いけますでしょうか? 作業列を作れる行は KからOが 空いています。
 何か 沢山聞いてしまって すいません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月経費シートの方の C1に 上の数式を入れておく…という事でしょうか?

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

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

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

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

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

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

 > A2からT2と I15からT15と I27からT27 まで
 > いろんな名目が付けてあります。

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

 (sky)


(yuyu)Skyさんありがとうございました。K列に作業列で入れただけでスラスラいくようになりました!!凄いです!ありがとうございました。
 それと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行目で合計金額となっています。

 A3には {=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)),"")}
 と入っています。

 I3には {=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)),"")}
 と入っています

 I16には {=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)),"")}

 I28には {=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)
Skyさん すみません。さっき やってみた時には サクサクいったのですが、今もう一度1月シートに 入力してみましたが やはり2秒位かかります。
 それで 作業列をKだけでなく Oにも教えて頂いたように 作ってやってみましたが 次のセルに移るのに2秒かかります。
 作業列が Kだけのと、KとOの二つにしたのと いずれも秒数は同じみたいです。 これは もう仕方ないですよね?

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

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

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

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

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

 (sky)


今 全部の数式を 全部のシートから 消して このファイルで調べてみましたが 秒数は2秒位かかったので 関数に関係なかったみたいです。すいませんでした。m(__)m 
 1月経費シートの A3 I3 I16 I28 には 関数を 何と入れたらいいでしょうか?
 大変すいませんm(__)m 宜しくお願い致します m(__)m

(yuyu)
 Skyさん 何か今 このファイルの名前を短く直したら 何故かすごく 早くなりました。サクサクいきます。
 ファイルの名前を 私が 長い名前を付けていたのですが、それを短くしたら 凄い早くなりましたm(__)m
 何か関係があるのか 判りませんが いいみたいです すいませんm(__)m
  すいません あと 1月経費シートの A3 I3 I16 I28 には 関数を 教えていただけますでしょうか?
 大変に すいみませんm(__)m

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

 > I28には {=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)
 skyさん ありがとうございます!!
 何故か判りませんが 名前が凄い長かったのですが、短くしたら 直っています。不思議です。

 1月シートから1月経費シート に抽出が綺麗に出来ました!!
 ありがとうございましたーー。
 ところが 1月シートの方の 教えて頂いた数式で PからWに作ってある表が全部#NUM! となってしまいました。 
 Q4=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)
Skyさん できました!!ありがとうございました!! 本当にお世話になりました。
 本当に助かりました。 これで 仕事が とてもとても 楽になれます!!! 本当にありがとうございました!! m(__)m m(__)m m(__)m  ありがとうございました!!!!!

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.