錦子老師

鄭依依：「錦子老師，下面是我們公司管理的大樓故障數統計表(編碼統計表工作表)，有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)

當我們將每個發生問題的儲存格編號完成後，接下來由於要從最高樓層由下往上一一擷取出來，由於資料是從第二列開始向下顯示每個發生問題的日期，統計發生問題的儲存格編號第(Row()-1)大的值，故輸入公式：

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

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

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

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

【公式解說】

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

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

再傳回該列A欄到F欄對應的內容，由於是從編碼統計表工作表A1儲存格開始，故列移動值要減1，而其A欄內容傳回高-低樓層工作表B欄，故欄移動值要減2。

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

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

部落格相關範例