Excel 資料分析 - 資料驗證

---

---

資料驗證是 Excel 中一個非常有用且易於使用的工具，您可以使用它來設定對輸入工作表中的資料的驗證。

對於工作表上的任何單元格，您可以

• 顯示輸入訊息，說明需要輸入的內容。
• 限制輸入的值。
• 提供一個值列表供選擇。
• 顯示錯誤訊息並拒絕無效資料輸入。

考慮以下風險跟蹤器，可用於輸入和跟蹤已識別的風險資訊。

在此跟蹤器中，輸入以下列中的資料會根據預設的資料約束進行驗證，只有當輸入資料滿足驗證條件時才會被接受。否則，您將收到錯誤訊息。

• 機率
• 影響
• 風險類別
• 風險來源
• 狀態

“風險暴露”列將包含計算值，您無法輸入任何資料。即使刪除一行，“序號”列也會設定計算值進行調整。

現在，您將學習如何設定這樣的工作表。

準備工作表結構

準備工作表結構：

• 從空白工作表開始。
• 將標題放在第 2 行。
• 將列標題放在第 3 行。
• 對於“機率”、“影響”和“風險暴露”列標題：
  • 右鍵單擊單元格。
  • 從下拉選單中單擊“設定單元格格式”。
  • 在“設定單元格格式”對話方塊中，單擊“對齊”選項卡。
  • 在“方向”下輸入 90。
• 合併並居中第 3、4 和 5 行中每個列標題的單元格。
• 設定第 2-5 行單元格的邊框。
• 調整行和列的寬度。

您的工作表將如下所示：

設定風險類別的有效值

在單元格 M5-M13 中輸入以下值（M5 是標題，M6-M13 是值）

• 單擊“風險類別”列下的第一個單元格 (H6)。
• 單擊功能區上的“資料”選項卡。
• 單擊“資料工具”組中的“資料驗證”。
• 從下拉列表中選擇“資料驗證…”。

將出現“資料驗證”對話方塊。

• 單擊“設定”選項卡。
• 在“驗證條件”下，“允許：”下拉列表中，選擇“列表”選項。

• 在出現的“來源：”框中選擇區域 M6:M13。
• 選中出現的“忽略空白”和“單元格下拉列表”複選框。

設定風險類別的輸入訊息

• 在“資料驗證”對話方塊中單擊“輸入訊息”選項卡。
• 選中“選中單元格時顯示輸入訊息”複選框。
• 在“標題：”框中，鍵入“風險類別”。
• 在“輸入訊息：”框中，鍵入“從列表中選擇風險類別”。

設定風險類別的錯誤警告

設定錯誤警告：

• 在“資料驗證”對話方塊中單擊“錯誤警告”選項卡。
• 選中“輸入無效資料後顯示錯誤警告”複選框。
• 在“樣式：”下拉選單中選擇“停止”。
• 在“標題：”框中，鍵入“無效輸入”。
• 在“錯誤訊息：”框中，鍵入“從下拉列表中選擇一個值”。
• 單擊“確定”。

驗證風險類別的數 據驗證

對於“風險類別”下選定的第一個單元格，

• 已設定資料驗證條件
• 已設定輸入訊息
• 已設定錯誤警告

現在，您可以驗證您的設定。

單擊您已設定資料驗證條件的單元格。將出現輸入訊息。單元格右側將出現下拉按鈕。

輸入訊息正確顯示。

• 單擊單元格右側的下拉按鈕。將出現包含可選擇值的下拉列表。

• 將下拉列表中的值與用於建立下拉列表的值進行交叉檢查。

兩組值匹配。請注意，如果值的數量較多，則下拉列表的右側將出現捲軸。

從下拉列表中選擇一個值。它將出現在單元格中。

您可以看到有效值的選中功能正常。

最後，嘗試輸入無效條目並驗證錯誤警告。

在單元格中鍵入“人員”並按 Enter 鍵。將顯示您為單元格設定的錯誤訊息。

• 驗證錯誤訊息。
• 您可以選擇“重試”或“取消”。驗證這兩個選項。

您已成功設定單元格的資料驗證。

注意 - 檢查訊息的拼寫和語法非常重要。

為“風險類別”列設定有效條件

現在，您可以將資料驗證條件應用於“風險類別”列中的所有單元格。

此時，您需要記住兩件事：

• 您需要為可能使用的最大單元格數量設定條件。在我們的示例中，根據工作表的使用位置，它可以從 10 到 100 不等。

• 您不應為不需要的單元格範圍或整列設定條件。這會不必要地增加檔案大小。這被稱為過度格式化。如果您從外部來源獲取工作表，則必須刪除多餘的格式，您將在本教程中關於查詢的章節中學習。

按照以下步驟操作：

• 為“風險類別”下方的 10 個單元格設定驗證條件。
• 您可以透過單擊第一個單元格的右下角輕鬆完成此操作。
• 按住出現的“+”符號並將其向下拖動。

已為所有選定單元格設定資料驗證。

單擊選定的最後一列並進行驗證。

“風險類別”列的資料驗證已完成。

設定風險來源的驗證值

在這種情況下，我們只有兩個值：內部和外部。

• 單擊“風險來源”列下的第一個單元格 (I6)。
• 單擊功能區上的“資料”選項卡。
• 單擊“資料工具”組中的“資料驗證”。
• 從下拉列表中選擇“資料驗證…”。

將出現“資料驗證”對話方塊。

• 單擊“設定”選項卡。
• 在“驗證條件”下，“允許：”下拉列表中，選擇“列表”選項。
• 在出現的“來源：”框中鍵入“內部，外部”。
• 選中出現的“忽略空白”和“單元格下拉列表”複選框。

設定風險來源的輸入訊息。

設定風險來源的錯誤警告。

對於“風險來源”下選定的第一個單元格：

• 已設定資料驗證條件
• 已設定輸入訊息
• 已設定錯誤警告

現在，您可以驗證您的設定。

單擊您已設定資料驗證條件的單元格。將出現輸入訊息。單元格右側將出現下拉按鈕。

輸入訊息正確顯示。

• 單擊單元格右側的下拉箭頭按鈕。將出現包含可選擇值的下拉列表。

• 檢查值是否與您鍵入的值相同 - 內部和外部。

兩組值匹配。從下拉列表中選擇一個值。它將出現在單元格中。

您可以看到有效值的選中功能正常。最後，嘗試輸入無效條目並驗證錯誤警告。

在單元格中鍵入“財務”並按 Enter 鍵。將顯示您為單元格設定的錯誤訊息。

• 驗證錯誤訊息。您已成功設定單元格的資料驗證。

• 為“風險來源”列設定有效條件

• 將資料驗證條件應用於“風險來源”列中的單元格 I6-I15（即與“風險類別”列相同的範圍）。

已為所有選定單元格設定資料驗證。“風險來源”列的資料驗證已完成。

設定狀態的驗證值

• 重複您用於設定風險來源驗證值的步驟。

• 將列表值設定為“開啟”、“關閉”。

• 將資料驗證條件應用於“狀態”列中的單元格 K6-K15（即與“風險類別”列相同的範圍）。

已為所有選定單元格設定資料驗證。“狀態”列的資料驗證已完成。

設定機率的驗證值

風險機率分數的值範圍為 1-5，1 表示低，5 表示高。該值可以是 1 到 5 之間的任何整數，包含 1 和 5。

• 單擊“風險來源”(I6)列下的第一個單元格。
• 單擊功能區上的“資料”選項卡。
• 單擊“資料工具”組中的“資料驗證”。
• 從下拉列表中選擇“資料驗證…”。

將出現“資料驗證”對話方塊。

• 單擊“設定”選項卡。
• 在“資料驗證”下的“允許：”下拉列表中，選擇“整數”。

• 在“資料”下選擇“之間”。
• 在“最小值”下的框中鍵入 1。
• 在“最大值”下的框中鍵入 5。

設定機率的輸入資訊。

設定機率的錯誤警告，然後單擊“確定”。

對於“機率”下選擇的第一個單元格，

• 資料驗證條件已設定。
• 輸入資訊已設定。
• 錯誤警告已設定。

現在，您可以驗證您的設定。

單擊您已設定資料驗證條件的單元格。將顯示輸入資訊。在本例中，不會出現下拉按鈕，因為輸入值設定為範圍而不是列表。

輸入訊息正確顯示。

在單元格中輸入 1 到 5 之間的整數。它將顯示在單元格中。

有效值的選取工作正常。最後，嘗試輸入無效條目並驗證錯誤警告。

在單元格中鍵入 6 並按 Enter 鍵。將顯示您為單元格設定的錯誤訊息。

您已成功設定單元格的資料驗證。

• 為“機率”列設定有效條件。

• 將資料驗證條件應用於“機率”列中的單元格 E6-E15（即與“風險類別”列相同的範圍）。

已為所有選定的單元格設定資料驗證。“機率”列的資料驗證已完成。

設定影響力的驗證值

要設定影響力的驗證值，請重複用於設定機率驗證值的步驟。

將資料驗證條件應用於“影響”列中的單元格 F6-F15（即與“風險類別”列相同的範圍）。

已為所有選定的單元格設定資料驗證。“影響”列的資料驗證已完成。

設定包含計算值的“風險暴露”列

風險暴露計算為風險機率和風險影響的乘積。

風險暴露 = 機率 * 影響

在單元格 G6 中鍵入 =E6*F6 並按 Enter 鍵。

由於 E6 和 F6 為空，單元格 G6 中將顯示 0。

將公式複製到單元格 G6-G15。單元格 G6-G15 中將顯示 0。

由於“風險暴露”列用於計算值，因此您不應允許在該列中輸入資料。

• 選擇單元格 G6-G15。

• 右鍵單擊，然後在出現的下拉列表中選擇“設定單元格格式”。將出現“設定單元格格式”對話方塊。

• 單擊“保護”選項卡。

• 選中“鎖定”選項。

這是為了確保不允許在這些單元格中輸入資料。但是，只有在工作表受保護後（您將在工作表準備就緒後執行最後一步）才會生效。

• 單擊“確定”。
• 對單元格 G6-G15 進行著色以指示它們是計算值。

設定序列號值的格式

您可以讓使用者填寫“序號”列。但是，如果您設定“序號”值的格式，工作表看起來更美觀。此外，它還顯示了工作表的格式化行數。

在單元格 B6 中鍵入 =ROW()-5 並按 Enter 鍵。

單元格 B6 中將顯示 1。將公式複製到單元格 B6-B15。將顯示值 1-10。

對單元格 B6-B15 進行著色。

總結

您的專案即將完成。

• 隱藏包含資料類別值的 M 列。
• 為單元格 B6-K16 設定邊框格式。

• 右鍵單擊工作表標籤。
• 從選單中選擇“保護工作表”。

將出現“保護工作表”對話方塊。

• 選中“保護工作表和鎖定單元格的內容”選項。
• 在“取消保護工作表密碼”下輸入密碼 -
  • 密碼區分大小寫
  • 如果忘記密碼，則無法恢復受保護的工作表
  • 最好將工作表名稱和密碼列表儲存在某個地方
• 在“允許所有使用者執行以下操作”下，選中“選擇未鎖定單元格”複選框。

您已保護“風險暴露”列中的鎖定單元格免受資料輸入，並保持其餘未鎖定單元格可編輯。單擊“確定”。

將出現“確認密碼”對話方塊。

• 重新輸入密碼。
• 單擊“確定”。

您已準備好使用為選定單元格設定了資料驗證的工作表。