【軟件】Excel三級級聯(lián)下拉框的設(shè)計與實現(xiàn)

(2010-06-01 11:51:51)

    在網(wǎng)上瀏覽了些有關(guān)在Excel中實現(xiàn)多重級聯(lián)的帖子，好復(fù)雜，數(shù)組都用上了，在這里，設(shè)計幾個相關(guān)的示例，整理一些我的設(shè)計思路。

示例一：設(shè)計三個下拉框菜單，即大分類（Category Name），子分類（Sub Category Name）及產(chǎn)品名(Produt Name), 產(chǎn)品名下拉內(nèi)容對應(yīng)上層分類菜單的選項。

1 基本思路：如何實現(xiàn)二級級聯(lián)下拉框，即子分類與大分類的聯(lián)動：

a. 使用數(shù)據(jù)有效性（Data Validation）實現(xiàn)下拉框。

b. 子分類（Sub Category Name）（二級下拉框）有效性數(shù)據(jù)范圍運用Offset + Match + Countif/Countifs公式

關(guān)鍵步驟：

1） 設(shè)計輔助表（表一）

在上述輔助表的基礎(chǔ)上，例如：大分類“Category Name 02”中所有子分類“Sub Category Name 04-06”的數(shù)據(jù)范圍如何??？如果運用Offset公式的話，下列2) - 4) 是主要考慮因素。

2）從哪個數(shù)據(jù)開始

從輔助表“Category Name”表標題（tmpStart）開始。

3）往下移動幾個位置（Match公式）

=MATCH(CategoryName,tmpColumn,0)

公式大意是所選的大分類（一級下拉框）在輔助表Category Name列（tmpColumn）中匹配的第一個位置，“Category Name 02”返回5

4）符合條件的數(shù)據(jù)有幾個(countif公式)

=COUNTIF(tmpColumn,CategoryName)

“Category Name 02”返回3

有了上述數(shù)據(jù)，對子分類（二級下拉框）設(shè)置“序列”（List）數(shù)據(jù)有效性，并設(shè)如下公式。

=OFFSET(tmpStart,MATCH(CategoryName,tmpColumn,0)-1,1,COUNTIF(tmpColumn,CategoryName),1)

2 如果產(chǎn)品（三級下拉框）與上級分類呈下表（表二）關(guān)系，且子分類Sub Category Name（二級）對應(yīng)唯一一個大分類（一級），對產(chǎn)品（三級下拉框）設(shè)置序列有效性，并設(shè)下列公式，同基本思路。

表二：

=OFFSET(subCategoryStart,MATCH(subCategoryName,subCategoryColumn,0)-1,1,COUNTIF(subCategoryColumn,subCategoryName),1)

如果子分類Sub Category（二級）對應(yīng)上級大分類存在重復(fù)，如表三所示，則產(chǎn)品（三級下拉框）的設(shè)計稍微會復(fù)雜些，具體請閱“示例二”部分。

表三：

示例二：

3 產(chǎn)品（三級下拉框）設(shè)計的關(guān)鍵步驟：

依舊遵循示例一的思路（數(shù)據(jù)有效性 + Offset）設(shè)計產(chǎn)品（三級下拉框）。

1）從哪個數(shù)據(jù)開始

2）往下移動幾個位置（Match + Indirect公式），即在表三中符合所選大分類（一級）與子分類（二級）的第一個位置是什么。

a. 符合所選大分類（一級）的第一個位置

= MATCH(CategoryName,categoryColumn,0)

例如：“Category Name 02”大分類，返回12

b.符合所選大分類（一級）的數(shù)據(jù)范圍

Range = StartRow:EndRow

StartRow = MATCH(CategoryName,categoryColumn,0), 即1）的結(jié)果

EndRow = COUNTIF(categoryColumn,CategoryName) + startRow -1

Range ="E" & startRow &  ":E" & endRow

例如：“Category Name 02”大分類的數(shù)據(jù)范圍是E12:E25。

c.所選子分類（二級）在所選大分類（一級）數(shù)據(jù)范圍中的第一個位置

subStartRow = MATCH(subCategoryName,INDIRECT(Range),0)

例如：“Category Name 02”（一級）-->“M”（二級）返回5

d.向下偏移量

downRows = startRow -1+ subStartRow -1

例如：“Category Name 02”（一級）-->“M”（二級）返回15

3) 符合條件的數(shù)據(jù)有幾個(Countifs)

在表三中，既符合所選大分類又符合所選子分類的個數(shù)

Rows =COUNTIFS(categoryColumn,CategoryName,subcategorycolumn,subcategoryName)

4) 基于上述1-3，產(chǎn)品（三級下拉框）序列有效性設(shè)置下列公式。

=OFFSET(subCategoryStart,downRows,1,Rows,1)

基于上述兩個示例，我們還可考慮

4 如何設(shè)計級聯(lián)下拉框中的“ALL”？

5 若需復(fù)選選項，又如何設(shè)計？

對Offset, Indirect, countif/countifs的基本使用，請閱相關(guān)文章。

發(fā)現(xiàn)一個Excel技巧應(yīng)用網(wǎng)址，內(nèi)容挺全面，推薦，http://www./tiptech.html ，例如，關(guān)于這個Mutiple dependent dropdown list的技巧介紹，有點啟發(fā)。