Excel中的“點選輸入”——次級下拉列表創建

在Excel中，用“數據驗證”功能可以設置下拉列表，二級下拉列表需要設置公式。

(筆記模板由python腳本于2024年06月16日 18:36:37創建，本篇筆記適合經常使用Excel處理數據的coder翻閱)

【學習的細節是歡悅的歷程】

Python 官網：https://www.python.org/
Free：大咖免費“圣經”教程《 python 完全自學教程》，不僅僅是基礎那么簡單……
地址：https://lqpybook.readthedocs.io/

??自學并不是什么神秘的東西，一個人一輩子自學的時間總是比在學校學習的時間長，沒有老師的時候總是比有老師的時候多。
????????????—— 華羅庚

My CSDN主頁、My HOT博、My Python 學習個人備忘錄
好文力薦、老齊教室

Excel的“數據驗證”功能 單元格下拉列表 (設置下拉列表，次級下拉需要公式)

本文質量分：

【 $96$ 】
本文地址： https://blog.csdn.net/m0_57158496/

CSDN質量分查詢入口：http://www.csdn.net/qc

目?錄

◆?單元格下拉列表
- 1、下拉列表制作
- - 1.1 下拉列表數據準備
  - 1.2 實景試煉
- 2、二級下拉列表
- 3、公式版本優劣
- - 3.1 OFFSET、MATCH函數組合
  - 3.2 INDEX、MATCH函數組合

◆?單元格下拉列表

1、下拉列表制作

??在Excel中創建單元格的下拉列表，可以提供一系列預設選項供用戶選擇，這不僅可以提高數據輸入的準確性和效率，還可以保證數據的一致性。

以下是在Excel中創建下拉列表的步驟：

1.1 下拉列表數據準備

準備數據列表：
- 首先，在某個工作表中，你需要準備一個包含所有可能選項的列表。
- 例如，假設你想要在A列的單元格中創建下拉列表，你可以在工作表的某個區域（比如E1:E10）輸入這些選項。
  下拉列表截屏圖片
設置數據驗證：
- 選中你想要設置下拉列表的單元格或單元格范圍（在A列的假設中）。
- 在“數據”菜單中，找到“數據驗證”選項(在Excel 2003及更早版本中，這一功能在“工具”菜單下的“數據驗證”。我的Excel版本是“數據有效性”按鈕)，點擊按鈕打開數據驗證對話框。
  我的版本截屏圖片
- 在彈出的“數據驗證”對話框中，選擇“設置”標簽頁。
- 在“允許”下拉列表中選擇“列表”。
- 在“來源”框中，輸入你的選項列表所在的范圍引用，例如E1:E10。

回頁目錄

1.2 實景試煉

實景試煉截屏圖片
可以實時編輯下拉列表

自定義下拉列表：
- 在“數據驗證”對話框中，你還可以在“輸入信息”和“錯誤警告”標簽頁中自定義提示信息和錯誤警告信息。
- 如果你想讓用戶在單元格中輸入時能夠看到所有選項，可以在“數據驗證”對話框的“輸入法模式”區域中勾選“下拉列表”。
應用并測試下拉列表：
- 點擊“確定”后，選定的單元格或單元格范圍就會添加下拉列表。
- 你可以嘗試在不同的單元格中點擊下拉箭頭，看看是否能夠選擇預設的選項。
保護工作表（可選）：
- 如果你不希望用戶在設置了下拉列表的單元格中輸入列表外的值，可以通過“審閱”菜單中的“保護工作表”功能來鎖定這些單元格。
- 在保護工作表時，確保選中“允許所有用戶進行”列表中的“選擇鎖定單元格”。

??完成以上步驟后，你就在Excel中成功創建了一個單元格下拉列表。如果需要在其他單元格或工作表中復制這個下拉列表，請確保引用的選項列表地址是正確的，或者將數據驗證的設置通過復制粘貼應用到其他單元格。

回頁目錄

2、二級下拉列表

??在Excel中創建二級下拉列表，也稱為依賴性下拉列表，可以讓第二個下拉列表的內容依賴于第一個下拉列表的選擇。例如，如果你有一個下拉列表包含不同的國家，第二個下拉列表可以根據選擇的國家顯示該國家的城市。

以下是創建二級下拉列表的步驟：

準備數據：
- 在工作表中，準備好兩個列表。例如，一個列表是不同國家的名稱（A列），另一個列表是每個國家的城市名稱（B列），且每個國家的城市都連續排列。
設置一級下拉列表：
- 選中你想要設置一級下拉列表的單元格或單元格范圍。
- 打開“數據驗證”對話框，選擇“列表”，并在“來源”中引用包含國家名稱的范圍。
使用公式創建二級下拉列表：
- 選中你想要設置二級下拉列表的單元格或單元格范圍。
- 打開“數據驗證”對話框，選擇“列表”，然后在“來源”中輸入一個公式，該公式將基于一級列表的選擇動態變化。例如，如果國家列表在A2:A10，城市列表在B2:C10，公式可能如下：
```
=INDIRECT(ADDRESS(ROW(), COLUMN()-1))
```
- 這個公式會根據當前行的左側單元格（國家單元格）的位置來返回相應的城市列表范圍。
復制二級下拉列表：
- 將含有二級下拉列表的單元格向下復制到需要下拉列表的每個單元格。
保護工作表（可選）：
- 如果你希望用戶只能從下拉列表中選擇，不能手動輸入，可以選擇“審閱”菜單中的“保護工作表”，并確保在保護工作表時勾選了“選擇鎖定單元格”。
測試二級下拉列表：
- 在一級下拉列表中選擇不同的選項，確保二級下拉列表的內容會相應地變化。

請注意：使用INDIRECT函數創建的二級下拉列表可能需要確保數據格式正確，并且避免創建可能引用錯誤單元格的公式。此外，如果數據量很大或者需要頻繁更改，可能需要考慮使用VBA宏來實現更復雜的依賴性下拉列表。

回頁目錄

3、公式版本優劣

??公式有兩個版本，都可以用來創建二級下拉列表，但它們的適用場景和優缺點略有不同。

3.1 OFFSET、MATCH函數組合

使用OFFSET和MATCH函數的公式：
- 公式：
```
     
=OFFSET($B$1, MATCH(C1, $A$1:$A$10, 0), 0, COUNTIF($A$1:$A$10, C1), 1)
```
- 這個公式的優點是它可以動態地計算出城市列表的范圍，不需要預先知道每個國家城市的具體數量。
- 缺點是OFFSET函數在某些情況下可能會受到Excel計算模型的影響，導致公式計算不準確。此外，如果數據量很大，這個公式的計算可能會變慢。

回頁目錄

3.2 INDEX、MATCH函數組合

使用INDEX和MATCH函數的公式：
- 公式：
```
=INDEX($B$2:$B$30, MATCH(C2, $A$2:$A$10, 0), 0)
```
- 這個公式的優點是它更加穩定，不容易受到Excel計算模型的影響，且計算速度通常比OFFSET函數快。
- 缺點是它假設每個國家的城市數量是固定的，并且你需要預先知道第一個城市的起始位置。如果城市數量不固定，這個公式可能不適用。

??在實際應用中，如果你的數據結構比較簡單，每個國家的城市數量固定，使用INDEX和MATCH函數的公式會更加可靠。如果你的數據結構比較復雜，城市數量不固定，那么使用OFFSET和MATCH函數的公式可能更合適。
?
??建議根據你的具體需求和數據結構來選擇合適的公式。如果可能的話，可以先在一個小規模的數據集上測試兩個公式，看看哪個更適合你的情況。

回頁首

上一篇：? 功能強大的偷懶神器：數據透視表_{(數控透視表，用于總結、分析、探索和呈現數據。允許用戶重新組織、匯總和簡化大量數據)}
下一篇：?

我的HOT博：

??本次共計收集 311 篇博文筆記信息，總閱讀量43.82w。數據于2024年03月22日 00:50:22完成采集，用時6分2.71秒。閱讀量不小于6.00k的有 $7$ 篇。

001
標題：讓QQ群昵稱色變的神奇代碼
_{(瀏覽閱讀 5.9w )}
地址：https://blog.csdn.net/m0_57158496/article/details/122566500
點贊：25?收藏：86?評論：17
摘要：讓QQ昵稱色變的神奇代碼。
首發：2022-01-18 19:15:08
最后編輯：2022-01-20 07:56:47
002
標題：Python列表(list)反序(降序)的7種實現方式
_{(瀏覽閱讀 1.1w )}
地址：https://blog.csdn.net/m0_57158496/article/details/128271700
點贊：8?收藏：35?評論：8
摘要：Python列表(list)反序(降序)的實現方式：原址反序，list.reverse()、list.sort()；遍歷，全數組遍歷、1/2數組遍歷；新生成列表，resersed()、sorted()、負步長切片[::-1]。
首發：2022-12-11 23:54:15
最后編輯：2023-03-20 18:13:55
003
標題：pandas 數據類型之 DataFrame
_{(瀏覽閱讀 9.7k )}
地址：https://blog.csdn.net/m0_57158496/article/details/124525814
點贊：7?收藏：36?
摘要：pandas 數據類型之 DataFrame_panda dataframe。
首發：2022-05-01 13:20:17
最后編輯：2022-05-08 08:46:13
004
標題：個人信息提取(字符串)
_{(瀏覽閱讀 8.2k )}
地址：https://blog.csdn.net/m0_57158496/article/details/124244618
點贊：2?收藏：15?
摘要：個人信息提取(字符串)_個人信息提取python。
首發：2022-04-18 11:07:12
最后編輯：2022-04-20 13:17:54
005
標題：Python字符串居中顯示
_{(瀏覽閱讀 7.6k )}
地址：https://blog.csdn.net/m0_57158496/article/details/122163023
評論：1
006
標題：羅馬數字轉換器|羅馬數字生成器
_{(瀏覽閱讀 7.5k )}
地址：https://blog.csdn.net/m0_57158496/article/details/122592047
摘要：羅馬數字轉換器|生成器。
首發：2022-01-19 23:26:42
最后編輯：2022-01-21 18:37:46
007
標題：回車符、換行符和回車換行符
_{(瀏覽閱讀 6.0k )}
地址：https://blog.csdn.net/m0_57158496/article/details/123109488
點贊：2?收藏：3?
摘要：回車符、換行符和回車換行符_命令行回車符。
首發：2022-02-24 13:10:02
最后編輯：2022-02-25 20:07:40