エクセルの関数多用を簡略化して動くように

エクセル関数の票COUNTIF,ROW,OFFSET,MATCHが一つの関数として入ったものを作成していたが、動かなくなり、作業列を作ることでエクセルが楽に動くようになった。ネットを検索してみるとやはり複数の関数が入り込むと計算ができなくなり表示できないということが示されていた。そのことを覚えとして書いておく。上の表はおおよそこんな体裁ということでデータは変更してある。

エクセルに30行☓10列の表を作成。

その票は3つの分類ができるようなもので、表の右に分類ごとにその詳細を書き出すために、ネットを検索しながら次のように設定した。「型」は分類ができる範囲(D2:D30)の範囲名とする。AF1には分類名をいれている。E~P列に項目の詳細が入力されているとする。

AF3=IF(COUNTIF(型,$AF$1)<ROW(C1),””,OFFSET(D2,MATCH($AF$1,肥料型,0),1))

AG3=IF(COUNTIF(型,$AF$1)<ROW(D1),””,OFFSET(E2,MATCH($AF$1,肥料型,0),1))

AF4=IF(COUNTIF(型,$AF$1)<ROW(C2),””,OFFSET(D3,MATCH($AF$1,肥料型,0),1))

AG$=IF(COUNTIF(型,$AF$1)<ROW(D2),””,OFFSET(E3,MATCH($AF$1,肥料型,0),1))

10行くらいの票のときには確かに機能していたのだが、行数が増えると分類ができていないことに気づく。再計算をしても変わらない。

次に、同様の事ができるということで作業列を新たに設ける。A2~C2に分類名を入力D列にその分類名があればカウントするということで下記の関数をドラッグする。

=IF($D3=A$2,COUNTIF($D$3:$D3,A$2),””)

a型分類欄には

S3=IF(MAX(a型数)<ROW($A1),””,INDEX(E$3:E$100,MATCH(ROW($A1),a型数,0)))

T3=IF(MAX(a型数)<ROW($A1),””,INDEX(F$3:F$100,MATCH(ROW($A1),a型数,0)))

S4=IF(MAX(a型数)<ROW($A2),””,INDEX(E$3:E$100,MATCH(ROW($A2),a型数,0)))

T4=IF(MAX(a型数)<ROW($A2),””,INDEX(F$3:F$100,MATCH(ROW($A2),a型数,0)))

同じような計算式でも個数を分割して作業列を作ることだけで随分とエクセルソフトにとっての計算が楽になるようだ。

エクセルの複数シートでのセルの値を集計するINDIRECT(複数シートではcountifができない)

エクセルの複数シートでのセル集計にINDIRECTを利用する。
シート間をまたがってcountifができないので
集計をするシートのあいているところに集計に必要な数値を入れる
シート名:セルG1:K1
集計をするセルは各シートの同一セル。
集計セルの行列地番を列番号:F4
同         行番号:F5~
集計セルがF5からF20まで続くため行番号をF5:F20まで
=(INDIRECT(ADDRESS($F5,$F$4,,,G$1))
 
いくつかのサイトで記載されていることをURLとその表記を掲載する。
下記のサイトによる
=INDIRECT(G1&"!E5")
では、値がとれなかった。この時シート名を各シートのA1セルに入力している名前をシート名にしていた(マクロ使用)。このA1のセルに数字を入れると、
=INDIRECT(G1&"!E5")
で値を取ることができた。
 
には、集計作業をしているシートの空いている列などを作業領域にし、
INDIRECT()を使って一旦各表の該当セルへの参照を集めておけば、お望みのCOUNTIFが可能です。
例えば10枚あるシートのA3セルを集計したいとします。
ここでは作業領域をHとIにしてみます。
H1~H10にシート名を入れておき、Iには =INDIRECT(H1&"!A3")として、オートフィルでI1~I10「=INDIRECT(H10&"!A3")」までを埋めます。
これで、I列に各シートのA3の参照がならびましたので、同じシートの任意のセルで=COUNTIF(I1:I10,"○")を掛けてみてください。
 
INDIRECT関数は、引数に指定されたセルに入力されているデータを使ってセルを参照してくれる関数です。
A2セルに
「4月」と入力されているときに
「=INDIRECT(A2&"!B33")」は
「='4月'!B33」と同じ意味になります。
上記の数式でINDIRECT関数の引数のうち「A2」はセルの相対参照ですから、下方向にオートフィルすれば
「=INDIRECT(A3&"!B33")」
「=INDIRECT(A4&"!B33")」
と変化しA3・A4セルにシート名を入力しておけば、それぞれのシートのB33セルの値を表示するようになります。
 
セルA1に入力されたシート名の、セルB3を参照する場合
=INDIRECT(A1& "!B3")
また上記ではセルB3が固定になってますが、
セルA1 に参照したい【シート名】をいれ
セルA2 に参照したい【セル番地】まで入れておくと
なお、使い勝手がよくなるかも知れませんね。
=INDIRECT(A1& "!" & A2)
使用する関数:ADDRESS と INDIRECT
例:
Sheet1のA1にSheet2という文字列が入っているとします。
Sheet2のA2に「参照する値」とい文字列が入っているとします。
式:
=INDIRECT(ADDRESS(2,1,,,A1))
これで、=Sheet2!A2 と同じ結果になります。
解説:
=ADDRESS(2,1,,,A1) のみですと、Sheet2!$A$2という文字列になります。その後、INDIRECTを使うことにより、値を取得します。

エクセルの複数シートでのセル集計にINDIRECTを利用(countifができない)
シート間をまたがってcountifができないので集計をするシートのあいているところに集計に必要な数値を入れるシート名:セルG1:K1集計をするセルは各シートの同一セル。集計セルの行列地番を列番号:F4同         行番号:F5~集計セルがF5からF20まで続くため行番号をF5:F20まで(INDIRECT(ADDRESS($F5,$F$4,,,G$1))
いくつかのサイトで記載されていることをURLとその表記を掲載する。下記のサイトによる=INDIRECT(G1&"!E5")では、値がとれなかった。この時シート名を各シートのA1セルに入力している名前をシート名にしていた(マクロ使用)。このA1のセルに数字を入れると、=INDIRECT(G1&"!E5")で値を取ることができた。
http://q.hatena.ne.jp/1147068497

には、集計作業をしているシートの空いている列などを作業領域にし、INDIRECT()を使って一旦各表の該当セルへの参照を集めておけば、お望みのCOUNTIFが可能です。例えば10枚あるシートのA3セルを集計したいとします。ここでは作業領域をHとIにしてみます。H1~H10にシート名を入れておき、Iには =INDIRECT(H1&"!A3")として、オートフィルでI1~I10「=INDIRECT(H10&"!A3")」までを埋めます。これで、I列に各シートのA3の参照がならびましたので、同じシートの任意のセルで=COUNTIF(I1:I10,"○")を掛けてみてください。
http://www.relief.jp/itnote/archives/001697.php

にはINDIRECT関数は、引数に指定されたセルに入力されているデータを使ってセルを参照してくれる関数です。A2セルに「4月」と入力されているときに「=INDIRECT(A2&"!B33")」は「='4月'!B33」と同じ意味になります。上記の数式でINDIRECT関数の引数のうち「A2」はセルの相対参照ですから、下方向にオートフィルすれば「=INDIRECT(A3&"!B33")」「=INDIRECT(A4&"!B33")」と変化しA3・A4セルにシート名を入力しておけば、それぞれのシートのB33セルの値を表示するようになります。
http://oshiete.goo.ne.jp/qa/3547667.html

セルA1に入力されたシート名の、セルB3を参照する場合   =INDIRECT(A1& "!B3")また上記ではセルB3が固定になってますが、セルA1 に参照したい【シート名】をいれセルA2 に参照したい【セル番地】まで入れておくとなお、使い勝手がよくなるかも知れませんね。   =INDIRECT(A1& "!" & A2)
http://q.hatena.ne.jp/1166863251使用する関数:ADDRESS と INDIRECT
例: Sheet1のA1にSheet2という文字列が入っているとします。 Sheet2のA2に「参照する値」とい文字列が入っているとします。式: =INDIRECT(ADDRESS(2,1,,,A1)) これで、=Sheet2!A2 と同じ結果になります。解説: =ADDRESS(2,1,,,A1) のみですと、Sheet2!$A$2という文字列になります。その後、INDIRECTを使うことにより、値を取得します。