帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2017-9-1 06:27 | 只看該作者

[發問] 求指定的對應數值

求指定的對應數值.rar (5.01 KB)

Q6,R5,R6=手動填入數值

需求︰
1.請將J︰P有顯示=$R$5值的I欄期數填入R7
EX︰Sheet1!R7=05；R8=11；....；R19=100
EX︰Sheet2!R7=60；R8=62；....；R13=100

2.請將J︰P有顯示=$R$5值之B︰H的對應數值填入Q7
EX︰Sheet1!Q7=26；R8=23；....；Q19=20
EX︰Sheet2!Q7=05；R8=20；....；Q13=07

PS︰Sheet1和Sheet2的I欄起始期數不同。

求Q7和R7函數的公式解。

請各位大大幫忙!謝謝先!

樓主熱帖

hcm19522

大學生 Rank: 4

帖子: 1392
主題: 4
精華: 0
積分: 1402
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-12-26

熱烈參與勳章

2^#

發表於 2017-9-1 14:43 | 只看該作者

Q7{=INDIRECT(TEXT(SMALL(IF(J$7:P$199=R$5,ROW(I$7:I$199)*1000+COLUMN(B$7:H$199)),ROW(A1)),"!R0C000"),)

R7{=SMALL(IF(J$7:P$199=R$5,I$7:I$199),ROW(A1))

Q7 利用R7期數=OFFSET(A$6,R7,MATCH(R$5,OFFSET(J$6,R7,,,7),))

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

3^#

發表於 2017-9-1 15:51 | 只看該作者

回復 2# hcm19522
hcm19522大大:
貴解公式的測試答案正確!感恩!

不好意思~
Q7 利用R7期數=OFFSET(A$6,R7,MATCH(R$5,OFFSET(J$6,R7,,,7),))
Sheet2!Q7無法顯示正確答案；
應該如何修改?
謝謝您!

TOP

hcm19522

大學生 Rank: 4

帖子: 1392
主題: 4
精華: 0
積分: 1402
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-12-26

熱烈參與勳章

4^#

發表於 2017-9-1 17:03 | 只看該作者

回復 3# ziv976688

OFFSET(A$6,MATCH(R7,I$7:I$199,),MATCH(R$5,OFFSET(J$6,MATCH(R7,I$7:I$199,),,,7),))

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

5^#

發表於 2017-9-1 18:17 | 只看該作者

回復 4# hcm19522

求指定的對應數值-2.rar (5.6 KB)

hcm19522大大:
完全解決了!感恩!

再請教:
如果將B:H和J:P資料互換，R7與Q7的公式要如何修改?
謝謝您!

TOP

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

6^#

發表於 2017-9-2 13:29 | 只看該作者

回復 4# hcm19522
如果將B:H和J:P資料互換，
則Q7的公式亦可符合需求；
但R7的公式無法符合需求。

請問:
為能適用新表格，R7的公式應如何修改?
謝謝您!

TOP

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

7^#

發表於 2017-9-3 00:29 | 只看該作者

回復 2# hcm19522

如果將B:H和J:P資料對調，
我沒有辦法以您的R7公式作修改；
最後我只能改成R7=
=IF(COUNTIF(INDIRECT("J7:P"&COUNT(I:I)+6),$R$5)<ROW(1:1),"",INDEX(INDIRECT("I7:I"&COUNT(I:I)+6),SMALL(IF(INDIRECT("J7:P"&COUNT(I:I)+6)=$R$5,INDIRECT("A7:A"&COUNT(A:A)+6)-$A$7+1,""),ROW(1:1))))
陣列
公式N長的^^"

再次謝謝您的熱心幫忙!感恩!

TOP