EXcelの関数の１つの数値を一括で変換する方法

質問

Ｅｘｃｅｌにて次のような関数を作りました。
｛=SUM(IF(入出庫管理!$C$5:$C$153=1,IF(入出庫管理!$D$5:$D$153=1,入出庫管理!$G$5:$G$153,0),0))｝
入出庫管理表なるものを作ってそのシートに入力した数値をカレンダーに反映させるものです。
入出庫管理!$C$5:$C$153=1は製品番号、入出庫管理!$D$5:$D$153=1は日付、入出庫管理!$G$5:$G$153,0),0は入庫数です。ここでセル一つ一つに入力するのが面倒なので一月分のセルに最初に記述した関数をコピーした後、製品番号や日付を同時に変換する方法は無いでしょうか？ご教授お願い致します。

回答No.3

taisuke555 · ベストアンサー

補足どうもです。

＃２の方法は理解して頂けたでしょうか？
（わからないようなら、また補足してください。）

置換する方法もあるとは思いますが、修正の度に置換するのも面倒なのでお勧めしません。

入出庫管理表の入出庫数に入庫数も出庫数も入力するのでしょうか？
その場合、出庫数はマイナス入力なのでしょうか？

＃１さんの方法で、列番号を求める関数COLUMNがありますので、
日付の=1の部分を=COLUMN(A1)-1に置き換える方法もあると思います。

私が作るとすれば、カレンダーの１行目に日付を入力(B1セルに1、C1セルに2)

B2セル　=SUMPRODUCT((入出庫管理表!$C$2:$C$153=B$1)*(入出庫管理表!$E$2:$E$153=$A2)*(入出庫管理表!$F$2:$F$153>0)*(入出庫管理表!$F$2:$F$153))

B3セル　=SUMPRODUCT((入出庫管理表!$C$2:$C$153=B$1)*(入出庫管理表!$E$2:$E$153=$A2)*(入出庫管理表!$F$2:$F$153<0)*(入出庫管理表!$F$2:$F$153))

（入庫はプラス、出庫はマイナスで入力していると仮定）
（カレンダーの品名は、入出庫管理表の品名（品番ではない）が入力されている）

として、B2とB3を選択して右方向へドラッグ（コピー・貼り付け）
さらにそのまま、下方向へドラッグします。

入出庫数の入力方法が違っていたら補足してください。

カレンダーの１行目に日付を表示できなければ、COLUMNを使用すればいいと思います。

お礼

ご丁寧に解説有難う御座います。無事目的を達成出来ました。感謝致します。

回答No.4

taisuke555

＃３で訂正があります。

COLUMN(A1)-1　→　COLUMN(B1)-1

関数の部分で
入出庫管理表!　→　入出庫管理!

です。すみません。

回答No.2

taisuke555

もう少しカレンダーの詳細が欲しいところですね。

＃１さんのROW関数は行番号を取得する関数ですので、

１行目がタイトル行で、２行目からカレンダーが入っている場合、ROW(A2)-1のような修正が必要になります。

後、カレンダーには、製品番号の１や日付の１はどこかのセルにタイトルがあるのでしょうか？

例えば、カレンダーが

　　Ａ　　　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　Ｆ　　←列
１　日付　　１　　２　　３　　４　　５
２　製品１
３　製品２
４　製品３
↑
行

のような表だとすると、（分かりやすいように製品番号を製品１・・・にしてあります。）
Ｂ２セルに　=SUMPRODUCT((入出庫管理!$C$5:$C$153=$A2)*(入出庫管理!$D$5:$D$153=B$1)*(入出庫管理!$G$5:$G$153))

のようにタイトルの値を参照するようにすれば、いちいちコピーした関数を修正しなくてもいいと思います。
（日付と、製品番号が逆でも同じようにできます。）
（配列数式でもできます。）

$A2やB$1のように、列や行の前に＄をつけると
コピーした場合に、
＄をつけた列、行指定は値が変わらず、＄をつけてない指定のみ値が変化します。

＄Ａ２を横にコピーしても、どの式も＄Ａ２のままで、
　　　　縦にコピーすると、＄Ａ３、＄Ａ４のように変化
Ｂ＄１を横にコピーすると、Ｃ＄１、Ｄ＄１のように変化し、
　　　　縦にコピーしても、どの式もＢ＄１のままです。

複雑なカレンダーですと、面倒ですが・・・

カレンダーの詳細が分かれば、もう少し具体的な回答もできると思います。

補足

お忙しい中ご回答本当に有難う御座います。
taisuke555様のご指摘がありましたのでもう少し表の詳細を説明致します。
表は備品管理表と入出庫管理表とカレンダーに分かれております。備品管理表で
　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ
　品番　　品名　　入庫数　　出庫数　　現在庫
1　1　　　鉛筆　　　10　　　　　　　　　10　　　
2
3
というように品名に品番をつけます。
入出庫管理表では
　　Ｃ　　Ｄ　　Ｅ　　　　　　　　　　　 ＦＧ
1　日付　品番　品名（備品管理表より抽出）入出庫数
2　１日　　1　　　鉛筆 10
3
となっております。
入出庫管理表に日付と品番、入出庫数を記入する様になっております。
（入出庫数はトータルで備品管理表に反映されます。）
本来の管理目的はここで終了なのですが、どうしてもカレンダーで入出庫を書き出す必要がある為
　Ａ　　　　Ｂ　　　　　Ｃ　　　　　　Ｄ　～
1品名　１日の入庫数　２日の入庫数　３日の～　
2 　　 １日の出庫数　２日の出庫数　３日の～
3品名　　同上～
となっております。
たとえば入出庫管理表に「２日に品番１が１０入庫」とするとカレンダーのＣ１に「１０」と記入されるようにしました。品番が多くなるとカレンダーも相当数になるので出来れば１日から３１日までの最初に記述した関数（最初の説明で品番と日付が逆でした。）を一列作ってそれをコピー。品番のみを列ごとに一括で変更するか、最初のセルの日付を１にして品番を変えづに日付だけが連番で変わるようにする方法が無いかということなのです。説明べたで申し訳ありません。

回答No.1

maruru01

こんにちは。maruru01です。

つまり、日付の場合、

入出庫管理!$D$5:$D$153=1
入出庫管理!$D$5:$D$153=2
入出庫管理!$D$5:$D$153=3
・・・

と連番コピーをしたいということでしょうか。

それなら、最初のセルに、

入出庫管理!$D$5:$D$153=ROW(A1)

と入力して下の行へコピーすれば出来ます。
整理番号も同様だと思います。

ちなみに、質問の式は配列関数を使用しなくても出来ますよ。

=SUMPRODUCT((入出庫管理!$C$5:$C$153=1)*(入出庫管理!$D$5:$D$153=1)*(入出庫管理!$G$5:$G$153))

です。

お礼

SUMPRODUCT使えました。目的の作業以上の収穫でした。有難う御座います。

補足

お忙しい中ご回答本当に有難う御座います。
taisuke555様のご指摘がありましたのでもう少し表の詳細を説明致します。
表は備品管理表と入出庫管理表とカレンダーに分かれております。備品管理表で
　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ
　品番　　品名　　入庫数　　出庫数　　現在庫
1　1　　　鉛筆　　　10　　　　　　　　　10　　　
2
3
というように品名に品番をつけます。
入出庫管理表では
　　Ｃ　　Ｄ　　Ｅ　　　　　　　　　　　 ＦＧ
1　日付　品番　品名（備品管理表より抽出）入出庫数
2　１日　　1　　　鉛筆 10
3
となっております。
入出庫管理表に日付と品番、入出庫数を記入する様になっております。
（入出庫数はトータルで備品管理表に反映されます。）
本来の管理目的はここで終了なのですが、どうしてもカレンダーで入出庫を書き出す必要がある為
　Ａ　　　　Ｂ　　　　　Ｃ　　　　　　Ｄ　～
1品名　１日の入庫数　２日の入庫数　３日の～　
2 　　 １日の出庫数　２日の出庫数　３日の～
3品名　　同上～
となっております。
たとえば入出庫管理表に「２日に品番１が１０入庫」とするとカレンダーのＣ１に「１０」と記入されるようにしました。品番が多くなるとカレンダーも相当数になるので出来れば１日から３１日までの最初に記述した関数（最初の説明で品番と日付が逆でした。）を一列作ってそれをコピー。品番のみを列ごとに一括で変更するか、最初のセルの日付を１にして品番を変えづに日付だけが連番で変わるようにする方法が無いかということなのです。説明べたで申し訳ありません。