在Excel中我們可以通過設(shè)置數(shù)據(jù)有效性更快速的輸入數(shù)據(jù)，或者限制數(shù)據(jù)的輸入范圍。通常數(shù)據(jù)有效性的數(shù)據(jù)來源于同一個(gè)工作簿，但有時(shí)也來源于其他工作簿。

當(dāng)我們使用其他工作簿的數(shù)據(jù)設(shè)置數(shù)據(jù)有效性時(shí)，我們發(fā)現(xiàn)Excel或者提示我們錯(cuò)誤，或者根本無法選中其他工作簿的數(shù)據(jù)。本文就和大家分享如何跨工作簿設(shè)置數(shù)據(jù)有效性。

一、案例

如下圖所示，為兩個(gè)Excel工作簿文件，文件名分別為“員工信息表”和“部門列表”?，F(xiàn)在要求為“員工信息表”的C列“部門”設(shè)置數(shù)據(jù)有效性，其數(shù)據(jù)來源為“部門列表”中的部門。

如果我們按照通常設(shè)置數(shù)據(jù)有效性的操作步驟，“允許”選擇“序列”，“來源”選擇其他工作簿的工作表的單元格區(qū)域，如下圖所示：

單擊確定后，Excel會(huì)提示我們引用錯(cuò)誤，如下圖所示。

這說明設(shè)置數(shù)據(jù)有效性時(shí)，如果有效性數(shù)據(jù)來自于其他工作簿，我們不能在【來源】中使用“[工作簿名]工作表名!單元格引用”這種引用方式。

那么跨工作簿設(shè)置數(shù)據(jù)有效性的正確操作步驟是什么呢？

二、操作步驟

方法一、INDIRECT函數(shù)法

選中需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域，單擊【數(shù)據(jù)】-【數(shù)據(jù)驗(yàn)證】，打開【數(shù)據(jù)驗(yàn)證】對(duì)話框?！驹试S】選擇【序列】?！緛碓础刻庉斎牍剑?/span>

=indirect("[部門列表.xlsx]部門列表!$A$2:$A$5")

注意：如果工作簿或工作表名稱中含有空格或特殊字符，需要在“[”前和“！”前使用半角單引號(hào)，即

=indirect("’[部門列表.xlsx]部門列表’!$A$2:$A$5")

單擊確定后就可以為目標(biāo)區(qū)域設(shè)置數(shù)據(jù)有效性。

方法二、定義名稱法

步驟1、為數(shù)據(jù)有效性數(shù)據(jù)源定義名稱

打開“部門列表”工作簿，為“部門列表”工作表的A2：A5定義名稱為“部門”，范圍為“工作簿”。

步驟2、在需要設(shè)置數(shù)據(jù)有效性的工作簿定義名稱

打開“員工信息”工作簿，單擊【公式】-【定義名稱】，打開【新建名稱】對(duì)話框。在名稱框中輸入定義的名稱（此處為“部門”），在引用位置處輸入 =部門列表.xlsx!部門。注意無需為工作簿名“部門列表.xlsx”

添加“[ ]”。單擊確定。

步驟3、設(shè)置數(shù)據(jù)有效性

選中需要設(shè)置有效性的單元格區(qū)域，單擊【數(shù)據(jù)】-【數(shù)據(jù)驗(yàn)證】，打開【數(shù)據(jù)驗(yàn)證】對(duì)話框?！驹试S】選擇“序列”，【來源】輸入“=部門”

三、注意事項(xiàng)

必須同時(shí)打開數(shù)據(jù)源工作簿和設(shè)置數(shù)據(jù)有效性的工作簿，這樣才能在設(shè)置數(shù)據(jù)有效性的工作表中看到下拉菜單。