跳到主要內容

如何在Google表格中創建相關的下拉列表?

在Google工作表中插入普通的下拉列表可能對您來說很容易,但是有時您可能需要插入一個依賴的下拉列表,這意味著第二個下拉列表取決於第一個下拉列表的選擇。 您如何在Google表格中處理此任務?

在Google表格中創建一個依賴下拉列表


在Google表格中創建一個依賴下拉列表

以下步驟可以幫助您插入從屬下拉列表,請按照以下步驟操作:

1。 首先,您應該插入基本的下拉列表,請選擇要放置第一個下拉列表的單元格,然後單擊 數據 > 數據驗證,請參見屏幕截圖:

2。 在彈出 數據驗證 對話框中選擇 範圍清單 從旁邊的下拉列表中 標準 部分,然後單擊 按鈕以選擇要基於其創建第一個下拉列表的單元格值,請參見屏幕截圖:

3。 然後點擊 節省 按鈕,第一個下拉列表已創建。 從創建的下拉列表中選擇一項,然後輸入以下公式: =arrayformula(if(F1=A1,A2:A7,if(F1=B1,B2:B6,if(F1=C1,C2:C7,"")))) 放入與數據列相鄰的空白單元格,然後按 Enter 鍵,一次顯示基於第一個下拉列表項的所有匹配值,請參見屏幕截圖:

備註:在以上公式中: F1 是第一個下拉列表單元格, A1, B1 C1 是第一個下拉列表中的項目, A2:A7, B2:B6C2:C7 是第二個下拉列表基於的單元格值。 您可以將它們更改為自己的。

4。 然後,您可以創建第二個從屬下拉列表,單擊要放置第二個下拉列表的單元格,然後單擊 數據 > 數據驗證數據驗證 對話框中選擇 範圍清單 從旁邊的下拉菜單 標準 部分,然後繼續單擊按鈕以選擇與第一個下拉項目匹配的公式單元格,請參見屏幕截圖:

5。 最後,單擊保存按鈕,第二個從屬下拉列表已成功創建,如以下屏幕截圖所示:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (18)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi there. Its been working but for it only appears that only one cell can be formulated.I need help 
For example: in Cell A1, it shows dependent dropdown, but when in cell a2 it does not work.
Thas what help that i need, From A:A, They have all different selection from the dropdown list. Thanks.
This comment was minimized by the moderator on the site
Would it been possible to create pull down menu based of formula?
I use formula "=SUMIFS(Items!$I:$I,Items!$A:$A,K$1,Items!$D:$D,$D2)" (=for example 150) to find the qty on pallet based on item and container size.
So I wanted to create pull down menu like qty 1 pallet, qty 2 pallets, qty 3 pallets,.....
Then user will always order qty of full pallets like 150, 2 x 150, 3 x150, up to 21 x 150.
This comment was minimized by the moderator on the site
Hi, I already created a drop down list like this one, what I'd like to know is how i can, when clicking on one option from that list, restrict what appears as options on another cell. For example, in one cell i can choose between laparoscopy and orthopaedics. If I choose orthopaedics then, the other cell which has a list of doctors, should only show the doctors for orthopedy and not all of them. Does anyone know how to do this?
This comment was minimized by the moderator on the site
Hii Sir,
I Want to Create A Dependent Drop Down List Like
- State(A1):- District(B1):- Taluka(C1) :- Then Information(D1) ,
So I need your Help .
Tell How do you do this Type of Dependent Drop down List in Excel or Google Sheet
This comment was minimized by the moderator on the site
Hi, Shelate,
Maybe the below article can solve your problem, please view it:
https://www.extendoffice.com/documents/excel/5852-excel-multiple-level-drop-down-list.html

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Now is needed to use: =arrayformula(if(F1=A1;A2:A7;if(F1=B1;B2:B6;if(F1=C1;C2:C7;""))))
This comment was minimized by the moderator on the site
Thank you soooo much!
This comment was minimized by the moderator on the site
Hi,
I was wondering if anyone could advise me with this? I've followed the above steps but on step 3 rather than getting:
"...all the matching values based on the first drop down list item have been displayed at once," I am just seeing one value (the top one from the drop down list).
My cells are placed slightly differently and the first drop down is filled from another sheet (from anywhere int eh column excluding the top row) so my formula is:

=arrayformula(if(VLE!D2:D=B11,B12,if(VLE!D2:D=C11,C12:C14,if(VLE!D2:D=D11,D12:D14,if(VLE!D2:D=E11,E12:E16,if(VLE!D2:D=F11,F12:F16,""))))))

It is correctly displaying the top drop down option for each of (using cell numbers from the step 3 example on this page) A1, B1, and C1 so, again using this page's example, if I select Asia form the F1 drop down, I would get China, but nothing further


Any suggestions?

Many thanks! Nick
This comment was minimized by the moderator on the site
If the first drop own is on sheet 1 A1, will this work were I want the second drop down as sheet 1 A2, but to have its data range on a separate sheet?
This comment was minimized by the moderator on the site
Ich bekomm immer die Meldung "Fehler beim Parsen der Formel" wenn ich =arrayformula(if(F1=A1,A2:A7,if(F1=B1,B2:B6,if(F1=C1,C2:C7,"")))) eingebe. Was ist der "Weiter-Schlüsel"? Reicht es nicht wie bei Excel die Formel einzugeben?
This comment was minimized by the moderator on the site
Ersetzen Sie den , zu ;
es hat für mich funktioniert
This comment was minimized by the moderator on the site
This is the best explanation I have found, so thank you for that!

In my situation, I am working with over 200 rows with more being continuously added. Is there an easier/more efficient way to handle this many rows?
This comment was minimized by the moderator on the site
Yes, if you don't put number index to specify ending cell.
Like instead of C2:C7 you put C2:C. This will automatically tell the array formula, that you want to consider all values in column "C" starting at "C2".
This comment was minimized by the moderator on the site
What happens if I want exactly the same dropdown list in F2 as you have in F1 ?
I tried, and it doesn't overwrite D1:D7 .. meaning D1:D7 is only available once.
I need something else, double dropdown, but on many-many lines .. guess I must use another system then .. not sure which ..
This comment was minimized by the moderator on the site
Hi, Xained,
This method can only applied to one cell, if you have some good ways for applying this to multiple cells, please comment here.

Thank you!
This comment was minimized by the moderator on the site
Please, guide me how to do that.Thanks
This comment was minimized by the moderator on the site
혹시, 하나의 셀이 아닌 여러셀에 할 수 있는 방법을 알 수 있을까요?
This comment was minimized by the moderator on the site
내말이... 시바 좃빠지게 따라했디만 밑에줄은 안되노..
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations