當您更改單元格中的條件值時，Excel可以使用公式和條件格式來自動擴展或收縮報表，而無需宏。這是如何做。Excel Tables是Excel 2007中引入的一項強大功能。您不僅可以直接向它們報告，還可以將它們用作動態(tài)報告（包括可變長度手風琴報告）的數(shù)據(jù)源。第一項任務是快速而輕松的；第二個更有趣。讓我們看看兩者。直接從Excel表報告假設您有一個這樣的表，并且想要為每個銷售人員生成一個呼叫分配報告。
最簡單的方法是直接從表格中報告。例如，如果要報告Alyson，可以單擊“銷售人員”過濾器，然后從列表中選擇Alyson，從而得到以下結果：
您可以打印她的報告，然后為Peter做同樣的事情。但是，您通常不希望直接從表格中報告。例如…您可能需要包括公式以顯示添加到表中數(shù)據(jù)的信息。（當然，您可以將計算出的列添加到表中，但是如果這些計算需要大量查找，則較長的表可能需要很長時間才能重新計算。）您可能需要在報告中為表格中的每一行報告多行信息。在這些情況下，您將需要創(chuàng)建一個動態(tài)報表，該報表從表中返回數(shù)據(jù)，這是一個非常有趣的任務。引入動態(tài)的“手風琴”報告下圖顯示了由同一工作表生成的三個報告。如您所見，報告的長度各不相同…像手風琴一樣展開和收縮。因此，我稱它們?yōu)槭诛L琴報告。通過從列表中選擇“ *”，我自動顯示了第一個報告。我選擇“ Alyson”顯示了第二個，而選擇“ Peter”顯示了第三個。所有這三個報告都從上面的兩個Excel表中獲取數(shù)據(jù)。以下說明向您展示了如何從頭開始構建此報告。
從一個電子表格動態(tài)創(chuàng)建三個手風琴報告。這些報告也是動態(tài)的，因為它們會根據(jù)您在一個或多個單元格中指定的條件動態(tài)變化。一覽無余，這是報告工作表的樣子，其中通配符被選為CurSeller標準：
注意…此頁面頂部的呼叫分配日志有六個分配。該報告也有六個分配，但是每個分配使用兩行。該報告在D列中顯示了銷售人員，當選擇了特定的銷售人員時，該字段將不成立…如下圖所示。第18和19行包含允許添加其他呼叫分配的公式。您可以根據(jù)需要在電子表格中向下復制這兩行。這是選擇Alyson的同一報告：
選擇Alyson后，報告結構將以兩種方式自動更改。首先，D列的內(nèi)容消失。其次，僅顯示Alyson的作業(yè)，在報告底部保留空白行。讓我們看看如何創(chuàng)造這種魔力…創(chuàng)建日志和客戶端表第一步是在新工作簿中創(chuàng)建日志表。為此，請輸入此處顯示的日期和文本。
然后，要將簡單表轉換為Excel 2007或更高版本中的Excel表，請選擇該表，然后選擇“插入”，“表”，“表”。在“創(chuàng)建表”對話框中，確保選中“ 我的表具有標題”，然后選擇“確定”。默認情況下，第一個表名為Table1。要將名稱Log分配給該表，請選擇表中的任何單元格，然后在“表工具”，“設計”，“屬性”中，輸入名稱Log 作為表名。同樣，我們需要一個“客戶詳細信息”表：
將名稱客戶端分配給該表。設置控制表第二步是設置控制表。首先，在報表工作簿中創(chuàng)建一個新工作表，并將其命名為Control。
右圖顯示了控制表的全部內(nèi)容。TopRow單元格返回“呼叫分配日志”的第一行號。這是顯示的單元格的公式：B2：= ROW（Log [#All]）為了創(chuàng)建這個公式，我輸入了…= row（…然后我選擇了整個日志表；輸入了右括號；然后按了Enter鍵。Excel設置了公式中所示的Table引用，即使我指定了較大的區(qū)域，這是因為 當我們指定多行范圍時，Excel的 ROW函數(shù)將返回第一行。NumRows單元格使用 ROWS函數(shù)返回日志表中當前數(shù)據(jù)的行數(shù)。這是顯示的單元格的公式：B3：= ROWS（Log [#All]）-1將A列中的兩個名稱分配為B列中的范圍名稱。選擇范圍A2：B3，選擇“公式”，“定義的名稱”，“根據(jù)選擇創(chuàng)建”。在對話框中，確保僅選中“ 左”列，然后選擇“確定”。要設置賣方列表，請輸入如圖所示的文本，并根據(jù)需要設置其格式。然后選擇范圍A5：A8，然后再次啟動“從選擇中創(chuàng)建”對話框。但是，這一次確保僅選中“ 頂行”。創(chuàng)建報告首先向您的報表工作簿添加一個新的工作表。將工作表命名為“報告”。現(xiàn)在，讓我們在單元格B1中設置列表框，如此處的報告所示（并在下面重復多次）：
選中單元格B1，然后選擇“數(shù)據(jù)”，“數(shù)據(jù)工具”，“數(shù)據(jù)驗證”，“數(shù)據(jù)驗證”。然后，在“數(shù)據(jù)驗證”對話框中，在“ 允許”列表框中選擇“ 列表”，然后輸入 = Sellers作為“ 源”。然后選擇確定。現(xiàn)在，您只能在單元格B1中輸入的數(shù)據(jù)是您在控制表中輸入的賣方列表的內(nèi)容。使用“創(chuàng)建名稱”，將范圍名稱CurSeller分配 給單元格B1。要生成實際報告，請先在F3：F4范圍內(nèi)輸入標簽。如下圖所示，要將標簽居中放置在三列上，請勿合并單元格！而是選擇范圍F3：H4，選擇用于對齊的對話框啟動器（由下面的紅色箭頭顯示）以啟動顯示“對齊”選項卡的“設置單元格格式”對話框。
對于對話框中的“水平文本對齊”設置，選擇“跨選區(qū)居中”。然后選擇確定。輸入F5：H5范圍內(nèi)的標簽，然后為每列輸入公式和數(shù)據(jù)。F列包含對報告中的每組行重復的序列號。因為每組呼叫分配信息使用兩行，所以同一序號出現(xiàn)兩次。H列中的公式依賴于這些數(shù)字。輸入為單元格F6和F7顯示的值。然后輸入此公式…F8：= F6 + 1…并將其復制到如圖所示的列中。G列計算每組行中的每一行。也就是說，組中的第一行是1，第二行是2。條件格式公式依賴于這些數(shù)字。要輸入這些數(shù)字，請輸入單元格G6和G7中顯示的值，然后將范圍G6：G7復制到該列中，如圖所示。
H列包含報告中最復雜的公式。這些數(shù)字的目的是在日志表中標識包含CurSeller過濾器指定的信息的行。這是單元格H6的公式。我將其分為七行，因此更易于閱讀和解釋，但您通常可以將其輸入一行。
類別1：H6：= IF（$ F6> NumRows，“”，類別2：IFERROR（類別3：IF（CurSeller =“ *”，$ F6，類別4：AGGREGATE（15,6，（1 /（Log [ SalesPerson] = CurSeller））*第5行：ROW（Log [SalesPerson]），$ F6）第6行：-TopRow），第7行：“”））第1行：如果當前行的F列中的序列號大于日志中的行數(shù)，則返回一個空字符串（“”）。除此以外…第2行和第7行：如果以下公式中有錯誤，請在第7行中返回空字符串。否則，返回以下公式的內(nèi)容。第3行：如果用戶在CurSeller單元格中選擇了通配符“ *”，我們需要顯示此行數(shù)據(jù)。因此，在F列中顯示序列號。第4和5行：使用 AGGREGATE函數(shù)返回包含Alyson數(shù)據(jù)的最小行號（假定選擇了她的名字）。當將此公式復制到單元格H8時，$ F6將更改為$ F8，其值為2。因此，在該單元格中，AGGREGATE函數(shù)將返回第二小的行編號，該行編號包含Alyson的數(shù)據(jù)。將其復制到H10后，它將返回最小的第三列，依此類推。在第4行中，部分（Log [SalesPerson] = CurSeller）生成一個TRUE和FALSE值的臨時數(shù)組。將數(shù)組劃分為1時，它將返回一個值等于1和＃DIV / 0的數(shù)組！錯誤，這正是我們在這種情況下想要的。然后，當我們乘以第5行返回的行號時，我們得到一個行號數(shù)組和＃DIV / 0！錯誤。因為AGGREGATE的第二個參數(shù)中的6告訴函數(shù)忽略錯誤，并且因為其第一個參數(shù)中的15告訴函數(shù)使用SMALL函數(shù)，所以AGGREGATE返回最小的非錯誤行號（由單元格F6中的值指定） ，這是找到Alyson姓名的第一行。（為了更好地了解AGGREGATE公式在這種情況下的工作方式，請在 AGGREGATE函數(shù)頁面上研究示例8至11。 ）第6行：第4行返回的行號是工作表的行號；但是我們需要日志表中的行索引號。為了獲得該數(shù)字，我們減去“控制”工作表中TopRow單元返回的行號。如圖所示，將公式向下復制到H列。創(chuàng)建報告的正文這是報告，從上方再次重復：
有了三個控制列之后，我們現(xiàn)在可以返回報告中顯示的文本。因此，輸入所示的公式…A3：=““＆IF（CurSeller =” *“，”所有賣家“，PROPER（CurSeller））的呼叫分配此公式返回報告標題的文本。范圍A4：C5包含顯示的標簽。只需輸入它們。單元格D4包含以下公式：D4：= IF（CurSeller =“ *”，“銷售人員”，“”）當CurSeller條件等于“ *”時，此公式將返回一個空字符串。在第6行中，輸入…A6：= IF（$ H6 =“”，“”，INDEX（Log [Client]，$ H6））B6：= IF（$ H6 =“”，“”，INDEX（Log [Date]，$ H6））C6：= IF（$ H6 =“”，“”，INDEX（Log [CallType]，$ H6））H列用作開關。如果此列中的指定單元格（由INDEX函數(shù)標識 ）包含空字符串（“”），則我們希望工作表不顯示任何內(nèi)容，既不顯示數(shù)字，也不顯示文本，也不顯示格式。當單元格H6不為空時，這些公式將返回客戶端的名稱，日期和呼叫類型。D6：= IF（OR（CurSeller <>“ *”，$ H6 =“”），“”，INDEX（Log [SalesPerson]，$ H6））單元格D6添加了另一項測試。如果CurSeller是通配符或單元格H6為空，則返回空字符串。否則返回銷售人員。接下來的三個公式從“客戶詳細信息表”中返回數(shù)據(jù)：
A7：= IF（$ H6 =“”，“”，INDEX（客戶[公司]，MATCH（$ A6，Clients [客戶]，0）））B7：= IF（$ H6 =“”，“”，INDEX （Clients [Phone]，MATCH（$ A6，Clients [Client]，0）））C7：= IF（$ H6 =“”，“”，INDEX（Clients [PastSales]，MATCH（$ A6，Clients [Client] ，0）））這三個公式相似。他們在單元格A6中獲取客戶的名稱，在“客戶詳細信息”表中查找該客戶的行號，然后在該行中分別返回“公司”，“電話”和“ PastSales”。在第6行和第7行中輸入公式后，將公式向下復制到第19行。格式化報告為了便于參考，下面是完整的報告：
將所需的所有填充顏色和字體分配給第3至5行。現(xiàn)在，讓我們分配條件格式，這使報表像魔術一樣工作。在分配格式時，請記住報告中的公式正在處理文本。也就是說，如果假定行或列為空白，則受影響的單元格中的公式已經(jīng)顯示了空字符串。該報告使用四種條件格式…條件格式1此格式控制報表正文中每兩行一組的第二行。分配方法如下：選擇范圍A6：D19，確保單元格A6是活動單元格。選擇“主頁”，“樣式”，“條件格式”，“新規(guī)則”。在“新格式設置規(guī)則”對話框中，選擇“ 使用公式來確定要格式化的單元格”。在標有“ 格式值，其中此公式為true的編輯框：”中，輸入：= AND（$ G6 = 2，$ H6 <>“”）（注意：該行號和下面的其他條件格式公式中的行號必須為與活動單元格相同的行號。）在“新建格式規(guī)則”對話框中，選擇“格式”以啟動“格式單元格”對話框。在“邊框”選項卡中，指定底部邊框。選擇確定，直到所有對話框都消失。條件格式2此格式控制報表正文中藍色和白色的交替填充顏色。步驟如下：選擇范圍A6：D19，確保單元格A6是活動單元格。選擇“主頁”，“樣式”，“條件格式”，“新規(guī)則”。在“新格式設置規(guī)則”對話框中，選擇“ 使用公式來確定要格式化的單元格”。在標簽格式為其中該公式為true的值的編輯框中：，輸入：= AND（ISODD（$ F6），$ H6 <>“”）在“新建格式規(guī)則”對話框中，選擇“格式”以啟動“格式單元格”對話框。在“填充”選項卡中，指定淺藍色填充。選擇確定，直到所有對話框都消失。條件格式3此格式是隱藏D列內(nèi)容的兩種格式之一：選擇范圍D4：D5，確保單元格D4是活動單元格。選擇“主頁”，“樣式”，“條件格式”，“新規(guī)則”。在“新格式設置規(guī)則”對話框中，選擇“ 使用公式來確定要格式化的單元格”。在標有“ 格式值”（在此公式為true的位置）的編輯框中，輸入：= CurSeller <>“ *”在“新建格式規(guī)則”對話框中，選擇“格式”以啟動“格式單元格”對話框。在“填充”選項卡中，指定“無顏色”。在“邊框”選項卡中，指定黑色的左邊框。選擇確定，直到所有對話框都消失。條件格式4這種格式是另一種隱藏D列內(nèi)容的格式：選擇范圍D6：D19，確保單元格D6是活動單元格。選擇“主頁”，“樣式”，“條件格式”，“新規(guī)則”。在“新格式設置規(guī)則”對話框中，選擇“ 使用公式來確定要格式化的單元格”。在標有“ 格式值”（在此公式為true的位置）的編輯框中，輸入：= CurSeller <>“ *”在“新建格式規(guī)則”對話框中，選擇“格式”以啟動“格式單元格”對話框。在“填充”選項卡中，指定“無顏色”。在邊框選項卡中，指定沒有邊框。選擇確定，直到所有對話框都消失。您現(xiàn)在應該有一份手風琴工作報告。
