EXCELの最終列の数値の取得

質問

お世話になります。

EXCELにて画像のようなデータがあった場合についてです。
例えばA列には受注目安の1回目を入力する欄が設けられており、それぞれの状態を入力します。
OKの場合はそれ以降入力する必要がないのでA列が最終列となりますが、未定の場合はC列の
受注目安2に再度入力しており、それが最終列扱いとなります。
売上も同じような方法で入力していくのですが、その右側にそれぞれの行の一番最後の値が
最終売上、最終目安に反映されるようにしたいです。

ネットで調べてみて最終行の取得などはあったのですが、入力された最終行の取得について
分かりやすい解説が見つかりませんので質問いたしました。

お手数ですが、解決方法をご教授下さい。
よろしくお願いします。

回答No.9

kagakusuki · ベストアンサー

>売上、原価、確度にそれぞれ日付が入っており、それが一週間刻みで更新されていきます>(W行とX行の間に3行ずつ追加されていくイメージです)
>この時、例えば2602行ならば2015/11/5の欄に最終の数字が入っており、それぞれが右端の対応項目に入ります。
>一方、2604行は2015/10/29の欄が最後の入力なので、それらの数値が右端の対応項目に入っております。

>行ごとに一番最後に入力されている売上、原価、確度を転記したく、更に今後3行ずつ挿入をしても同じように反映されているとベターです。

　「売上　2015/11/5」がU1セルに入力されていて、3列で1組となっているという事は、最初の「売上」はC1セルに入力されているものと考えて宜しいのでしょうか？
　それと念の為に確認しておきたいのですが、最後の「原価　○○○○/○○/○○」の列には必ず数値データが入力されていて、尚且つ最後の「確度　○○○○/○○/○○」の列には数値データが入力されている事は絶対に無いものと考えれば宜しいのでしょうか？
　もし上記の条件通りであった場合には、Y2セルに入力する関数は次の様なものとなります。

=IF(COUNT($D2:INDEX(2:2,COLUMN()-3)),HLOOKUP(9E+307,$D2:INDEX(2:2,COLUMN()-3),1),"")

　同様に、X2セルに入力する関数は次の様なものとなります。

=IF(ISNUMBER($Y2),INDEX($C2:INDEX(2:2,COLUMN()-3),MATCH(9E+307,$D2:INDEX(2:2,COLUMN()-2))),"")

　同様に、Z2セルに入力する関数は次の様なものとなります。

=IF(ISNUMBER($Y2),INDEX($E2:INDEX(2:2,COLUMN()-3),MATCH(9E+307,$D2:INDEX(2:2,COLUMN()-4))),"")

お礼

遅くなりまして申し訳ありません。
無事ご教示頂いた方法で実現させることが出来ました。
あらためてINDEX関数、MATCH関数の組み合わせ方、ROW関数＆COLUMN関数の組み合わせの奥深さを知りました。
素人考えでは思いつかないことも応用によってできてしまうのですね、これから積極的にそういう組み合わせを応用できないか実践していきたいと思います。
kagakusukiさんの回答をベストアンサーに選ばせていただきます。
何度もお答えいただきありがとうございました、また機会が有りましたらぜひよろしくお願いいたします。

回答No.8

kagakusuki

回答No.2です。

>【質問時】最終売り上げ｜最終受注目安 となっていたのですが
>【改変後】最終売り上げ｜最終原価｜最終確度
>と３つの項目に増え、隣を参照するだけということにならなくなりました。

　その説明ではどう変わったのかさっぱり解かりません。
　「最終売り上げ」、「最終原価」、「最終確度」の3項目は、それぞれ何という列に入力されている様になったのですか？
　そして、それらの列にはそれぞれどのような結果を表示させたいと考えておられるのですか？
　それに、A列「受注目安1」、B列「売上1」、C列「受注目安2」、D列「売上2」というはターンは同じままなのでしょうか、それとも変わったのでしょうか？
　もし変わったのであれば、それぞれ何列にどの値が入力される様に変わったのかという事と、それらの入力値の内、どれとどれが数値になっているのかという事も御説明願います。
　上記の点が全て明らかにならない限りは、情報不足のため解決方法を示す事が出来ません。

補足

失礼しました、分かりやすく説明するため再度図を作成し直しました。
http://imgur.com/jdCP170

ご覧いただきます図のように、売上、原価、確度にそれぞれ日付が入っており、それが一週間刻みで更新されていきます(W行とX行の間に3行ずつ追加されていくイメージです)
この時、例えば2602行ならば2015/11/5の欄に最終の数字が入っており、それぞれが右端の対応項目に入ります。
一方、2604行は2015/10/29の欄が最後の入力なので、それらの数値が右端の対応項目に入っております。

行ごとに一番最後に入力されている売上、原価、確度を転記したく、更に今後3行ずつ挿入をしても同じように反映されているとベターです。
こちらの説明でうまく伝わりましたでしょうか…？
拙い説明で申し訳ありません、何卒よろしくお願いいたします。

回答No.7

keithin

Ｇ２に
=IF(COUNT(A2:E2),INDEX(A2:E2,COUNT(A2:E2)*2-1),"")
以下コピー

Ｆ２はＧ２の応用で出来ますが，別の式で
=IF(COUNT(A2:E2),LOOKUP(9E+99,A2:E2),"")
とかでも出来ます。

回答No.6

bunjii

提示の列数が少ないので列数を増やした状態で検証してみました。
最終売上=IF(COUNT(A2:H2),INDEX(A2:H2,COUNT(A2:H2)*2),"")
最終受注目安=IF(COUNT(A2:H2),INDEX(A2:H2,COUNT(A2:H2)*2-1),"")

回答No.5

dogs_cats

１．質問の画像のように、A~D列にデータを入力してF、G列にデータ取得する場合
F2式
=IF(B2="","",IF(D2="",B2,D2))
G2式
=IF(A2="","",IF(C2="",A2,C2))

２．目標３、４と今後２列づつ挿入する場合、この場合もF列のひとつ前の列は添付されてるE列のように１列空白列があるものとしています。
F2式
=IF($A2="","",OFFSET($A2,,COUNTA(OFFSET($A2,,,,COLUMN(F2)-2))-1))

G2式
=IF($A2="","",OFFSET($A2,,COUNTA(OFFSET($A2,,,,COLUMN(F2)-2))-2))

下方へオートフィル

回答No.4

Prome_Lin

私の理解が間違っていたらすみません。

この回答は、無視してください。

「Ａ」列～「Ｄ」列は、すでに入力してあり（データがすべてそろっていて）、「Ｆ」列と「Ｇ」列に、その結果を反映させたい。

という理解で、以下のようなマクロを組みました。

Option Explicit
Sub Test()
Dim c, i As Integer
For i = 2 To Range("A1").End(xlDown).Row
If Cells(i, 1).Value = "未定" Then
c = 3
Else
c = 1
End If
Cells(i, 6).Value = Cells(i, c + 1).Value
Cells(i, 7).Value = Cells(i, c).Value
Next i
End Sub

失礼ながら、「VBA」の知識が無い方と想定して説明させて頂きます。
（何となく、質問の仕方から、「VBA」の知識はおありのようなのですが、念のため）

目的のエクセルファイルを開き、「Alt＋F11」（Alt（「オルト」と読みます）キーを押しながら「F11」キー）を押します。

新しく画面が開きますので、その新しい画面でメニューから「挿入」→「標準モジュール」を選択すると、右側に、白い大きな画面が表示されます。

その白い大きな画面上にカーソルがすでにあるはずですので、上記のマクロを範囲指定して、「Ctrl＋c」（コピー・記憶）、エクセルに戻って、白い大きな画面上で、「Ctrl＋v」（ペースト・貼り付け）します。

あとは、「F5」を押せば、結果が出ています。

一応、説明致しますと、「Ａ」列を１つずつ読み込んでいって、「未定」かどうかだけで、判断しています。

回答No.3

objecraft

F2セルへ⇒=IF(ISBLANK(D2),B2,D2)
G2セルへ⇒=IF(ISBLANK(C2),A2,C2)
以下オートフィルで下まで下げる

受注目安2が空白であれば受注目安1を取得し、空白でなければ目安2を取得します。

回答No.2

kagakusuki

要するに、A列の値が何であるのかには関係なく、兎に角、数値が入力されている最終列のセルに入力されている数値をF列のセルに表示させ、その最終列の1つ左にある列の値をG列のセルに表示させる様にすると考えれば宜しいのでしょうか？
　もしそれで宜しければ、以下の様にされると良いと思います。
　まず、F2セルに次の関数を入力して下さい。

=IF(COUNT($A2:E2),HLOOKUP(9E+307,$B2:E2,1),"")

　次に、G2セルに次の関数を入力して下さい。

=IF(ISNUMBER($F2),INDEX($A2:D2,MATCH(9E+307,$B2:E2)),"")

　次に、F2～G2の範囲をコピーして、F列～G列の3行目以下に貼り付けて下さい。

　以上です。

補足

ありがとうございます、ほぼ完成系に近い状態になりました。
そしてせっかくお答えいただいた中再度質問となるのですが、
【質問時】最終売り上げ｜最終受注目安 となっていたのですが
【改変後】最終売り上げ｜最終原価｜最終確度
と３つの項目に増え、隣を参照するだけということにならなくなりました。
その際、お教え頂いた関数＋OFFSET関数を使うのでしょうか？
お手数ですが、再度お教えいただけますと幸いです。

回答No.1

aokii

A列には最終行までには、ブランクセルがないので、以下の式でいかがでしょう。
=COUNTA(A:A)

お礼

ご回答ありがとうございました。
残念ながら、やや式として足りませんでした…