錦子老師

高志航：「錦子老師您好，請問如何快速整理左邊的資料變成右邊的格式?謝謝，麻煩解惑 ~ 感恩，謝謝！」

錦子老師：「這個問題解決方法如下：

首先要了解C欄週期與L3:P3儲存格中的週期對應。

「91~150 days」對應C欄「> 90days」      「151~180 days」對應C欄「> 150days」。

「181~360 days」對應C欄「> 180days」       「361~540 days」對應C欄「> 360days」。

「大於540 days」對應C欄「> 540days」

步驟1：點取L5儲存格輸入公式：

=SUMPRODUCT((LEFT($C$2:$C$106,4)="> "&MID(L$3,1,FIND("~",L$3,1)-1)-1)*$A$2:$A$106*($D$2:$D$106=$J5)*($E$2:$E$106="v"))

並將公式複製到L6:L8儲存格。

步驟2：將L6儲存格公式中($E$2:$E$106="v")改成($F$2:$F$106="v")

步驟3：將L7儲存格公式中($E$2:$E$106="v")改成($G$2:$GF$106="v")

步驟4：將L8儲存格公式中($E$2:$E$106="v")改成($H$2:$H$106="v")

步驟5：將L5:L8儲存格框選起來，按CTRL+C鍵複製。

步驟6：點取L10儲存格，按CTRL+V鍵貼上。

步驟7：將L10:L13儲存格公式中的($D$2:$D$106=$J5)刪除。

步驟8：點取M5儲存格輸入公式：

=SUMPRODUCT((LEFT($C$2:$C$106,5)="> "&MID(M$3,1,FIND("~",M$3,1)-1)-1)*$A$2:$A $106*($D$2:$D$106=$J5)*($E$2:$E$106="v"))

並將公式複製到M5:O8儲存格。

步驟9：將M6儲存格公式中($E$2:$E$106="v")改成($F$2:$F$106="v")

步驟10：將M7儲存格公式中($E$2:$E$106="v")改成($G$2:$GF$106="v")

步驟11：將M8儲存格公式中($E$2:$E$106="v")改成($H$2:$H$106="v")

步驟12：將M5:O8儲存格框選起來，按CTRL+C鍵複製。

步驟13：點取M10儲存格，按CTRL+V鍵貼上。

步驟14：將M10:O13儲存格公式中的($D$2:$D$106=$J5)刪除。

步驟15：點取P5儲存格輸入公式：

=SUMPRODUCT((LEFT($C$2:$C$106,5)="> "&MID(P$3,3,3))*$A$2:$A$106*($D$2:$D$106= $J5)*($E$2:$E$106="v"))

並將公式複製到P6:P8儲存格。

步驟16：將P6儲存格公式中($E$2:$E$106="v")改成($F$2:$F$106="v")

步驟17：將P7儲存格公式中($E$2:$E$106="v")改成($G$2:$GF$106="v")

步驟18：將P8儲存格公式中($E$2:$E$106="v")改成($H$2:$H$106="v")

步驟19：將P5:P8儲存格框選起來，按CTRL+C鍵複製。

步驟20：點取P10儲存格，按CTRL+V鍵貼上。

步驟14：將P10:P13儲存格公式中的($D$2:$D$106=$J5)刪除。

【公式說明】

=SUMPRODUCT((LEFT($C$2:$C$106,5)="> "&MID(M$3,1,FIND("~",M$3,1)-1)-1)*$A$2:$A $106*($D$2:$D$106=$J5)*($E$2:$E$106="v"))

公式1：FIND("~",M$3,1)

在M3儲存格從第1個字元搜尋~位置。

公式2：MID(M$3,1,公式1-1)

在M3儲存格從第1個字元開始抓取N(公式1結果減1)個字元，以M5儲存格為例是151。

公式3：(LEFT($C$2:$C$106,5)="> "&公式2-1)

傳回C2:C106儲存格最左邊5個字元，是否含有「> 公式2結果數值減1」，以M5儲存格為例是「> 150」，是為TRUE(1)，否為FALSE(0)。

公式4：($D$2:$D$106=$J5)

傳回D2:D106儲存格中是否為J5儲存格內容，是為TRUE(1)，否為FALSE(0)。

公式5：($E$2:$E$106="v")

傳回E2:E106儲存格中內容是否為v，是為TRUE(1)，否為FALSE(0)。

公式6：SUMPRODUCT(公式3*$A$2:$A $106*公式4*公式5)

將同時符合公式3、公式4、公式5條件對應的A2:A106儲存格數值相加總。

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

更多相關影片教學：請點我

更多相關文章：請點我