提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

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

在Google工作表中插入正常的下拉列表對您來說可能是一件容易的事,但有時您可能需要插入一個依賴下拉列表,這意味著第二個下拉列表取決於第一個下拉列表的選擇。 你怎麼能在Google工作表中處理這個任務?

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


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


以下步驟可能會幫助您插入相關的下拉列表,請按照以下步驟操作:

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

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

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

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

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

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


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ana Miguel · 17 days ago
    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?
  • To post as a guest, your comment is unpublished.
    Vaibhav Shelate · 25 days ago
    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
  • To post as a guest, your comment is unpublished.
    Guest · 8 months ago
    Now is needed to use: =arrayformula(if(F1=A1;A2:A7;if(F1=B1;B2:B6;if(F1=C1;C2:C7;""))))
  • To post as a guest, your comment is unpublished.
    Nick · 10 months ago
    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
  • To post as a guest, your comment is unpublished.
    Andrew · 11 months ago
    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?
  • To post as a guest, your comment is unpublished.
    Peter · 1 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Geyza · 10 months ago
      Ersetzen Sie den , zu ;
      es hat für mich funktioniert
  • To post as a guest, your comment is unpublished.
    Alexis · 1 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Lad · 1 years ago
      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".
  • To post as a guest, your comment is unpublished.
    Xained · 2 years ago
    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 ..
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      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!
      • To post as a guest, your comment is unpublished.
        Tram · 8 months ago
        Please, guide me how to do that.Thanks
      • To post as a guest, your comment is unpublished.
        Lucas.Gu · 1 years ago
        혹시, 하나의 셀이 아닌 여러셀에 할 수 있는 방법을 알 수 있을까요?
        • To post as a guest, your comment is unpublished.
          kill · 2 months ago
          내말이... 시바 좃빠지게 따라했디만 밑에줄은 안되노..