[發問] 當出現數字的欄位不一定時,如何用一致性公式求答案?

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2017-8-6 16:07 | 只看該作者

[發問] 當出現數字的欄位不一定時,如何用一致性公式求答案?

求J欄的答案,該如何代入一致性的公式？

求J欄公式.rar (7.93 KB)
順位
1 訂單數量
2 BOM
3 請購數量
4 採購數量
5 進貨數量
6 領料數量
*******
1 B,H是被減數的欄位
2 (A,D:H)-(H+ B)=J欄的答案
3 (A,D:H)欄只能用其中一個數字
4 該用哪一個數字以順位決定
5 當第1順位有數字時,用該數字-(H+ B)=J
6 每個順位都有數字時,以第1順位為優先
7 (A,D:H)欄,哪個欄位會出現數字,沒有一定

樓主熱帖

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

2^#

發表於 2017-8-6 17:39 | 只看該作者

補充：空的欄位要補0,不是空格.

fengetianxia

中學生 Rank: 2

帖子: 93
主題: 3
精華: 0
積分: 134
點名: 0
作業系統: WINDOWS7
軟體版本: OFFICE 2010
閱讀權限: 20
性別: 男
註冊時間: 2016-3-21
最後登錄: 2020-11-30

3^#

發表於 2017-8-7 10:26 | 只看該作者

=OFFSET(A2,0,IF(A2<>"",1,MIN(IF(D2:H2<>"",COLUMN(D2:H2),9^9)))-1,1,1)-B2-H2
數組公式

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-12

4^#

發表於 2017-8-7 11:40 | 只看該作者

本帖最後由准提部林於 2017-8-7 11:55 編輯

一般公式：
=IF(COUNT(A2,D2:H2),1/LOOKUP(9^9,1/N(OFFSET(A2,,{7,6,5,4,3,0}))),0)-B2-H2

或：陣列公式（三鍵輸入）
=IFERROR(IF(A2,A2,HLOOKUP("*",IF(D2:H2,D2:H2&""),1,))-B2-H2,0)

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

5^#

發表於 2017-8-7 21:45 | 只看該作者

回復 4# 准提部林
感謝大大,
二個公式都可以求得正確答案.

回復 4# fengetianxia
謝謝您,
不過代出的答案無法完全正確.

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

6^#

發表於 2017-8-7 23:13 | 只看該作者

回復 4# 准提部林

請教大大,

在實際作業中,有某些無效訂單,會導致所有的儲存格全部為0，但又不能刪除該列,這時我希望可以在J欄的待配顯示為0，請問該如何修改公式？

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 247
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-14

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

7^#

發表於 2017-8-8 09:25 | 只看該作者

回復 6# PJChen
=IFERROR(LOOKUP(2,1/(N(OFFSET(A2,,{7,6,5,4,3,0}))<>0),N(OFFSET(A2,,{7,6,5,4,3,0})))-H2-B2,0)

學海無涯_不恥下問

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-12

8^#

發表於 2017-8-8 10:56 | 只看該作者

回復 6# PJChen

=IF(SUM(A2,D2:H2),1/LOOKUP(9^9,1/N(OFFSET(A2,,{7,6,5,4,3,0})))-B2-H2,0)
第二個陣列公式IFERROR, 本來就可以除錯為0

若有問題, 最好提供檔案及實例~~~

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

9^#

發表於 2017-8-8 12:01 | 只看該作者

回復 7# Hsieh

咦,...大大公式又不太一樣了,多學到一種,感謝.

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

10^#

發表於 2017-8-8 12:06 | 只看該作者

回復 8# 准提部林

是的,第一種全部儲存格為0時,會出現#N/A,看到Hsieh的公式之後,我知道可以在您原公式稍加修改也成.

=IFERROR(IF(COUNT(A10,D10:H10),1/LOOKUP(9^9,1/N(OFFSET(A10,,{7,6,5,4,3,0}))),0)-B10-H10,0)

第2種是原本就可以除錯.

感謝...