標題:
多層下拉式清單問題
[打印本頁]
作者:
074063
時間:
2015-7-25 15:40
標題:
多層下拉式清單問題
請問多層下拉式清單定義資料一些問題..
如附件:
資料範圍$A:$B
篩選不重複分類並列清單於$E:$E
篩選品名符合分類條件(F1:I1)並列清單於$F:$I
於$A:$B資料範圍內新增/刪除資料時,$E:$I能隨即更新資料
以上請教, 或是有更好的方法....
作者:
ML089
時間:
2015-7-26 20:44
本帖最後由 ML089 於 2015-7-26 20:45 編輯
定義名稱
分類 =OFFSET(!$A$2,,,COUNTA(!$A:$A)-1)
品名 =OFFSET(!$B$2,,,COUNTA(!$A:$A)-1)
E2 {=INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&""}
下拉複製公式
F2 {=INDEX($B:$B,SMALL(IF(分類=F$1,ROW(分類),4^8),ROW(A1)))&""}
右拉下拉複製公式
{...} 公式需用 CTRL+SHIFT+ENTER 三鍵輸入
作者:
074063
時間:
2015-7-26 23:28
回復
2#
ML089
感謝ML089大大解答, 定義名稱時會出現錯誤如圖...請問是什麼原因
[attach]21501[/attach]
作者:
ML089
時間:
2015-7-26 23:40
回復
3#
074063
你的EXCEL版本?
定義名稱
分類 =OFFSET($A$2,,,COUNTA($A:$A)-1)
品名 =OFFSET($B$2,,,COUNTA($A:$A)-1)
作者:
ML089
時間:
2015-7-26 23:43
回復
5#
074063
=IFERROR(..... , "")
作者:
074063
時間:
2015-7-26 23:44
回復
4#
ML089
Excel版本是2007, 錯誤訊息已解決，=OFFSET(!$A$2,,,COUNTA(!$AA)-1,) 在-1後加上逗號就好了
但有個問題.... E2 {=INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&""} 下拉複製公式
會有#NUM字串, 如何不顯示呢
作者:
074063
時間:
2015-7-26 23:46
回復
5#
ML089
感恩大大指導:D
作者:
ML089
時間:
2015-7-27 07:37
回復
6#
074063
Excel版本是2007, 錯誤訊息已解決，=OFFSET(!$A$2,,,COUNTA(!$AA)-1,) 在-1後加上逗號就好了
我也是用 2007版，不會有此問題，一般 OFFSET後面兩個參數應該是不用輸入，加上逗號後面的值不是變成 0 ，0欄應該是不對?
有些疑惑
不管怎樣能用就好。
作者:
074063
時間:
2015-7-27 08:03
回復
8#
ML089
E2 {=INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),
ROW(分類),4^8)
,
ROW(A1)
))&""}
請問上述陣列涵數藍色標示處是什麼意思呢
作者:
ML089
時間:
2015-7-27 09:29
回復
9#
074063
E2 {=INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&""}
ROW(分類) : 資料的列號
4^8 : EXCEL 2003版最後一列
ROW(A1) : 等於 1，往下拉時會改為 ROW(A2) = 2
作者:
074063
時間:
2015-7-28 00:01
回復
10#
ML089
定義名稱
分類 =OFFSET(!$A$2,,,COUNTA(!$A:$A)-1)
品名 =OFFSET(!$B$2,,,COUNTA(!$A:$A)-1)
E2{=IFERROR(INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&"","")}
F2 {=IFERROR(INDEX($B:$B,SMALL(IF(分類=F$1,ROW(分類),4^8),ROW(A1)))&"","")}
下拉複製公式
以上有個問題
$E:$E資料範圍 另定義一個名稱 "分類一" =OFFSET(!$E$2,,,COUNTA(!$E:$E))
在J1資料驗證→清單 =分類一
結果J1下拉式清單沒辦法排除空白值
請問J1清單是否能只顯示出$E:$E有資料範圍
作者:
c_c_lai
時間:
2015-7-28 07:21
回復
6#
074063
請試著將：
E2{=INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&""}
E2{=IFERROR(INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&"","")}
F2 {=IFERROR(INDEX($B:$B,SMALL(IF(分類=F$1,ROW(分類),4^8),ROW(A1)))&"","")}
等等修改為：
E2={INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&""}
E2={IFERROR(INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&"","")}
F2={IFERROR(INDEX($B:$B,SMALL(IF(分類=F$1,ROW(分類),4^8),ROW(A1)))&"","")}
然後再看看結果如何？
作者:
074063
時間:
2015-7-28 08:11
本帖最後由 074063 於 2015-7-28 08:12 編輯
回復
12#
c_c_lai
c_c_lai 大大, 看了一下涵數好像都一樣, 只是把{括弧移至=等號右側, 結果涵數錯誤
作者:
ML089
時間:
2015-7-28 10:08
回復
11#
074063
$E:$E資料範圍 另定義一個名稱 "分類一" =OFFSET(!$E$2,,,COUNTA(!$E:$E))
在J1資料驗證→清單 =分類一
結果J1下拉式清單沒辦法排除空白值
回覆
$E$1 若有表頭時，請修改如下
名稱 "分類一" =OFFSET(!$E$2,,,COUNTA(!$E:$E)-1)
作者:
074063
時間:
2015-7-28 12:36
回復 074063
$EE資料範圍 另定義一個名稱 "分類一" =OFFSET(!$E$2,,,COUNTA(!$EE))
在J1資料 ...
ML089 發表於 2015-7-28 10:08
名稱: 分類一 =OFFSET(!$E$2,,,COUNTA(!$E:$E)-1) 試了下拉清單還是有空白值
分類一 $E:$E 資料來自 E2{=IFERROR(INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&"","")}
作者:
ML089
時間:
2015-7-28 15:57
回復
15#
074063
名稱: 分類一 =OFFSET(!$E$2,,,COUNTA(!$E:$E)-1) 試了下拉清單還是有空白值
分類一 $E:$E 資料來自 E2{=IFERROR(INDEX(A:A,SMALL(IF(MATCH(分類,A:A,)=ROW(分類),ROW(分類),4^8),ROW(A1)))&"","")}
儲存格內有公式對COUNTA函數都認為有東西，所以有公式的儲存格不能使用COUNTA直接計數
COUNTA(!$E:$E)-1 改為 COUNTIF($E:$E,">=A") 看看可不可以
作者:
074063
時間:
2015-7-28 16:41
回復
16#
ML089
可以了, 謝謝你:)
想問一下,">=A"是何意思
作者:
ML089
時間:
2015-7-28 17:08
回復
17#
074063
想問一下,">=A"是何意思
就是字首大於等於 A的字碼
一般字碼排列是 .... 數字 0 1 2 .....大寫 A B C .....小寫 a b c .....中文字
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
