錦子老師

鄭依依：「錦子老師，下面是我們公司管理的大樓故障數統計表(編碼統計表工作表)，有V的儲存格表示故障：

我要在依日期工作表，依照每個日期一天一天排下來顯示故障明細：

錦子老師：「首先我們要一部一部的分解整個公式的運算。

首先要建立每個發生問題的儲存格編號，由於是從G欄到AS欄共有39欄，第4列到第113列，共110列，故為了方便管理需將有V字元的儲存格的欄編號值乘上1000再加上列編號。

(編碼統計表!$G$4:$AS$113="V")* (Column(編碼統計表!$G$4:$AS$113)*1000)+Row(編碼統計表!$G$4:$AS$113)

當我們將每個發生問題的儲存格編號完成後，接下來則是將每一天發生錯誤的記錄一一擷取出來，由於資料是從第二列開始向下顯示每個發生問題的日期，統計發生問題的儲存格編號第(Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2)大的值，故輸入公式：

Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2 統計錯誤(V)儲存格的數量，由於從第二列開始故須減列號加2，每列將其值依序減1一一統計出來。

Large((編碼統計表!$G$4:$AS$113="V")* (Column(編碼統計表!$G$4:$AS$113)*1000)+Row(編碼統計表!$G$4:$AS$113),Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2)

再來傳回發生問題的日期所在第幾列(將發生問題的儲存格編號值除以1000的商數)：

Quotient(Sumproduct(Large((編碼統計表!$G$4:$AS$113="V")* (Column(編碼統計表!$G$4:$AS$113)*1000)+Row(編碼統計表!$G$4:$AS$113),Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2)),1000)

在編碼統計表工作表第7欄開始依序抓取每一天發生問題的記錄，從A1儲存格移動2列再移動N欄數。

=Offset(編碼統計表!$A$1,2,Quotient(Sumproduct(Large((編碼統計表!$G$4:$AS$113="V")* (Column(編碼統計表!$G$4:$AS$113)*1000)+Row(編碼統計表!$G$4:$AS$113),Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2)),1000)-1)

所以在H2儲存格輸入公式：

=Offset(編碼統計表!$A$1,2,Quotient(Sumproduct(Large((編碼統計表!$G$4:$AS$113="V")* (Column(編碼統計表!$G$4:$AS$113)*1000)+Row(編碼統計表!$G$4:$AS$113),Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2)),1000)-1)

再將公式複製到H3:H113儲存格。

點取B2儲存格輸入公式：

=Offset(編碼統計表!$A$1,Mod(Sumproduct(Large((編碼統計表!$G$4:$AS$113="V")* (Column(編碼統計表!$G$4:$AS$113)*1000)+Row(編碼統計表!$G$4:$AS$113),Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2)),1000)-1,Column()-2)

再將公式複製到B2:G113儲存格。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

部落格相關範例