スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

ちょっと難しいが複雑なシート作成には欠かせない Excel INDIRECT関数(その2)





ちょっと難しいが複雑なシート作成には欠かせない Excel INDIRECT関数(その2)

今回はINDIRECT関数を使ったちょとした裏ワザです。

月毎の集計でSUM関数を良く使用します。
でも、下の図のように部の構成(課の数)が異なる場合
通常はB6のセルに
=SUM(B3:B5)
と記述しB9に式をコピーすると
=SUM(B6:B8)
となるため
6行目の式をそのまま9行目にコピーできません。(正しくは「=SUM(B7:B8)」)


でも、INDIRECT関数を使うことでSUMの範囲を外出しにできます。

この例ではは
B6からG6、I6からN6は同じ式で
=SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN())))

B9からG9、I9からN9も同じ式で
=SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN())))

となっています。
R6、R9にそれぞれ、SUMを行う範囲の列を「_」で連結して指定します。

こうすることで
B6からP6をコピーし

課の数が違っていても
B9にそのまま式を張り付けるだけでOKになります。

このExcelのTAB区切りテキスト
このリンクを「名前を付けて保存」し空のExcelシートのA1に貼り付けると再現できます。

テキストの中身はこちら

部別集計
部署 4月 5月 6月 7月 8月 9月 上期 10月 11月 12月 1月 2月 3月 下期 通期
A部1課 1 2 3 4 5 6 21 7 8 9 10 11 12 57 78
A部2課 11 12 13 14 15 16 81 17 18 19 20 21 22 117 198
A部3課 21 22 23 24 25 26 141 27 28 29 30 31 32 177 318
A部合計 =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R6,FIND("_",$R6)-1),COLUMN())&":"&ADDRESS(RIGHT($R6,LEN($R6)-FIND("_",$R6)),COLUMN()))) 3_5
B部1課 1 2 3 4 5 6 21 7 8 9 10 11 12 57 78
B部2課 11 12 13 14 15 16 81 17 18 19 20 21 22 117 198
B部合計 =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) =SUM(INDIRECT(ADDRESS(LEFT($R9,FIND("_",$R9)-1),COLUMN())&":"&ADDRESS(RIGHT($R9,LEN($R9)-FIND("_",$R9)),COLUMN()))) 7_8


部の数が2つだけでしたら
たいした手間ではないのですが
部の数が多かったり、課が他の部に異動したりすると
結構、修正が大変になります。
また、修正漏れも発生します。

特に、年度替わりなどでは修正が多くなり大変です。

でも、SUMの範囲を外出しにすることで
その部分の修正だけ実行すれば
式の修正が終わるのでメンテナンスが楽になります。
スポンサーサイト

コメントの投稿

非公開コメント

アクセスカウンタ
オンラインカウンター
現在の閲覧者数:
プロフィール

たっきー

Author:たっきー
たっきーのブログへようこそ!
パソコン・スマホを
より使いやすくするため奮闘中!
改造したり、root取ったり
色々やってます。

カテゴリ
最新記事
最新コメント
月別アーカイブ
最新トラックバック
検索フォーム
RSSリンクの表示
リンク
ブロとも申請フォーム

この人とブロともになる

QRコード
QR
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。