錦子老師

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

我要在低-高樓層工作表，依照由低樓層到高樓層依照每樓層發生故障的日期一層一層排下來顯示故障明細：

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

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

(編碼統計表!$G$4:$AS$113="V")*(ROW(編碼統計表!$G$4:$AS$113)*100)+COLUMN(編碼統計表!$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")*(Row(編碼統計表!$G$4:$AS$113)*100)+ Column(編碼統計表!$G$4:$AS$113), Countif(編碼統計表!$G$4:$AS$113,"V")-Row()+2)

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

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

在編碼統計表工作表第三列抓取發生問題的日期，從A1儲存格移動2列再移動前面算的的欄值-1的欄數。

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

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

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

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

點取B2儲存格輸入公式：

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

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

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

部落格相關範例