作者Andy5566 (小妹56)
看板Office
標題[問題] Excel函數會在儲存後失效
時間Sun Mar 15 23:30:50 2020
軟體:Excel
版本:2016
之前有幸在本版上解決Excel下拉選單的問題
但現在又碰到新的問題來請教一下版上鄉民
藉由B3與C3的下拉選項，求得D3可能的下拉選項
https://i.imgur.com/MKSPD6l.png
D3的函數為
{=IFERROR(INDEX($AC:$AC,SMALL(IF($B3&$C3=$AA$1:$AA$12&$AB$1:$AB$12,ROW($1:$12)),COLUMN(AA1))),"")}
資料驗證的清單來源為
=OFFSET(AC$1,MATCH(B3&C3,AA$1:AA$12&AB$1:AB$12,0)-1,,COUNTIFS(AA:AA,B3,AB:AB,C3))
但每當儲存檔案後，D3的函數都會失效
若重新寫入函數，則會出現「此值不符合此儲存格定義的資料驗證限制。」
必須將資料驗證的清單重新確定才能恢復正常
想請問這個問題的原因為何？是否有解決方案？感謝！<(_ _)>
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 123.194.41.178 (臺灣)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1584286253.A.961.html
※ 編輯: Andy5566 (123.194.41.178 臺灣), 03/15/2020 23:40:06
1F：→ soyoso: 之所以要於儲存格d3打上的公式的原因是？ 03/16 07:16
2F：→ soyoso: 因為下拉清單選取後，內文d3的公式也會被取代為所選取的清 03/16 07:17
3F：→ soyoso: 單 03/16 07:17
4F：→ soyoso: 內文d3公式於前篇發文，是用於輔助欄內，另提供不用輔助欄 03/16 07:19
5F：→ soyoso: 就是內文所寫到的，直接用於清單來源，那儲存格d3也就不用 03/16 07:20
6F：→ soyoso: 再打上公式 03/16 07:20
感謝提醒，剛剛把D3清除，清單也重新整理，重填上
=OFFSET(AC$1,MATCH(B3&C3,AA$1:AA$12&AB$1:AB$12,0)-1,,COUNTIFS(AA:AA,B3,AB:AB,C3))
但依然會有儲存檔案後，開啟下拉就會失效的問題
有下拉的鍵頭，卻拉不出任何選項出來
必須重新開啟資料驗證按確定，才能重新使用....囧
7F：→ soyoso: 將match(...)的部分置於自訂名稱內 03/16 10:39
8F：→ soyoso: 清單來源，原本match(..)則置換自訂名稱 03/16 10:40
9F：→ soyoso: 補充一下match(..)的部分要以公式，就是=match(..)方式 03/16 10:41
我將清單來源改為
=OFFSET(AC$1,MATCH(B3&C3,產品&包裝,0),,COUNTIFS(AA:AA,B3,AB:AB,C3))
一樣可以使用，但存檔後重啟還是必須重新開啟並儲存資料驗證才能使用
啊....忘了加=，我需要想一下要怎麼將=加入才不會有異常
10F：→ soyoso: 要將match(...)置於自訂名稱，以=match(...)而非回文這樣 03/16 10:54
11F：→ soyoso: 如果也不要以自訂名稱的話 03/16 10:54
13F：→ soyoso: 將match置於自訂名稱，再清單來源以自訂名稱的方式 03/16 10:55
14F：→ soyoso: 或是回文10:54連結的方式，我都測試，重新開啟時是可行的 03/16 10:55
15F：→ soyoso: 補充一下match(..)置於自訂名稱，是指=match(...)以自訂名 03/16 10:59
16F：→ soyoso: 稱的方式，而非將自訂名稱aa2:aa12的範圍為產品或是包裝 03/16 11:00
感謝soyoso大耐心的講解<(_ _)>
因為基礎不夠所以一知半解的，我會繼續了解這些指令的意涵！
※ 編輯: Andy5566 (123.194.41.178 臺灣), 03/16/2020 11:08:19