毎月追加される数値データを飛ばしたい

質問

エクセルの計算式に関する質問です。
二つのシートがあり、一つは社内の別部署からもらったデータを貼り付けるシート（黄色側）。もう一つは貼り付けた数値をオレンジへ飛ばして、その数値と単価で掛け算して売上を計算する様なエクセルになります。（オレンジ側）
毎月、別部署からもらうデータは右横に追加していくイメージで、数値を飛ばす方のシートも毎月、月を右横にずらしていくイメージです。H列のオレンジに入れるべき式を教えていただきたいです。
今回、添付したのは1ファイルしか添付できない為、一緒のシートに記載がありますが、実際は別シートで管理されています。
よろしくお願いします。

回答No.5

kkkkkm · ベストアンサー

> COLUMN(B1)のB1とは本来何を指定すればいいのでしょうか。試してみましたが、エラーとなってしまっています。

本来COLUMN(B1)の所を数値にするとH3(画像の表のセル位置で)
=IFERROR(VLOOKUP($F3,Sheet1!$A$3:$J$9,2,FALSE),"")
となり範囲の2番目の10が表示されると思います。

その式を右にコピーしても2は変化しないので範囲の2番目のデータ「10」が表示され続けます。
それを防ぐためにCOLUMN(B1)としてコピーした時にB1をC1,D1・・・と変化させようとしています。

COLUMN関数は指定したセルの列番号を返します。
どこかのセルに
=COLUMN(B1)
としたら2と表示されてそのセルを右へコピーしたら3,4,5と増えていくと思います。

ですので、式の中でCOLUMN(B1)としているのはH3では範囲の2番目を取り出し、右へコピーしたら3番目,4番目,5番目・・・と取り出せるようにしています。

なのでCOLUMN(B1)はそのままで利用してくださいということになります。

お礼

わかりやすく説明いただき有難うございました。無事に解決することができました。

Powered by GRATICA

回答No.6

msMike

式 =OFFSET($B$3,(ROW(A1)-1)/2,COLUMN(A1)-1)
を入力したセル H3 を セル H5、H7、H9、…、H15 にコピー＆ペースト
オ・シ・マ・イ

回答No.4

imogasi

＃３です。
例データ　質問添付画像より
Sheet1(シート名）　A2:C６
ー 2023年9月 2023年10月
A 10 　　　　（略）
B 20
C 30
D 40
===================
Sheet2（シート名） A2:B9
2023年9月　　　2023年10月　
A 10　　　　　　　（略

B 20

C 30

F 40
===================
Sheet2のB3の式　=INDEX(Sheet1!$B$3:$B$20,MATCH(A3,Sheet1!$A$3:$A$10,0),MATCH(B$2,Sheet1!$B$2:$E$2,0))
別方法として、VLOOKUP関数でもできそう。
飛び飛びのB5、B7。F9にこの式の適当な修正をした式を持ってきたいが
（１）B3の式を数式バー部でコピーして、手動でB5、B7。F9・・に貼り付ける
（２）行が多い場合は、別途VBAで関数式をコピー貼り付けする（VBAコード略）
（３）B4,B6,B8・・・に、データが未だ入っていない場合として
　　　（データが既に入っていると、そのデータが式の結果で上書きされて、消してしまうから）
　　　手動でB3に、＋ハンドルを出して下方向に引っ張る。
　　　その後、B4,B6,B8・・・の式（エラーが出ているだろう）は手動で消す。Delete キーで。
ーー
やや無理があるが、それだけエクセルの現状の機能では無理なのだろう、と推測する。

回答No.3

imogasi

>データを飛ばしたい
このイメージのことが、エクセル関数では出来ない。基本的な大原則だ。
値をセットするセル側に式を入れて、（イメージ的には）他のセル値（他シートでも可）か、それを加工した値しかセットできない。
初心者はこういうことを考えないのかもしれない。VBAをやると、これのイメージのことができる
ことが判る。
例えば、C5の値をA3セルにセットする。Cells（３,"A"）＝cells(5,"C")で。受ける側は左辺に書く。
両セルのあるシートが異なっても、手はある。
ーー
本件は添付画像のシート例で、オレンジのセルの在る方（右側）のH列に当たる列で、違う性格のデータが行的に交互に出現する。これが式の複写をする（＋ハンドルを引っ張って式を複写するやり方の場合）に、うまく行かず困るのだ。
目視・手動で人間が判断し、H3の式コピーして、それをH5、H7、F9・・に貼りつければ解決するが、そうしますか（Z）。
エクセルは、シートの（同じ）一行に、性格の異なるデータを並べる設計にすると、関数を使う上では、色々難しいことに出くわすだろう。
ーー
上記Zの式は、具体的な式は、質問者の反応を見つつ、明日でも挙げる。

回答No.2

kkkkkm

オレンジのシートのH3(添付画像の位置として9月のA社のオレンジ色の所))に
=IFERROR(VLOOKUP($F3,Sheet1!$A$3:$D$9,COLUMN(B1),FALSE),"")
として下と右に必要なだけコピーしてください。
$F3はオレンジのシートで実際の事業者名が入っている列に変更してください。
式を入れるところも実際の9月のセルに入れてください。

Sheet1は黄色のシートの事です。
COLUMN(B1)は黄色のシートの位置(9月がB列から始まらなくても)が変わってもそのままにしてください。
$A$3:$D$9は実際に貼り付ける可能性のある範囲に変更してください。

補足

教えていただき有難うございます。COLUMN(B1)のB1とは本来何を指定すればいいのでしょうか。試してみましたが、エラーとなってしまっています。COLUMN(B1)の部分を通常のVLOOKUP関数のあるべき値を入れると問題なく反映されたので、教えていただけますと幸いです。
よろしくお願いします。

回答No.1

redslove10

他のシートのセルを参照する方法を教えてください。
https://www.fmworld.net/cs/azbyclub/qanavi/jsp/qacontents.jsp?PID=6311-2106