|
送人玫瑰，手有余香，請將文章分享給更多朋友
動手操作是熟練掌握EXCEL的最快捷途徑！ 在數(shù)據(jù)錄入時有一個原則，就是盡量不用合并單元格，避免在后續(xù)的統(tǒng)計過程中出現(xiàn)問題。今天要向大家分享的這個案例就是這樣的。 如果錄入數(shù)據(jù)是能夠像C列示例那樣錄入，那么這個題目毫無壓力。但現(xiàn)在，這樣的數(shù)據(jù)結構需要動一番腦筋了。 我們先來看看使用常規(guī)公式的解法。 在單元格G2中輸入公式“=SUMPRODUCT((LOOKUP(ROW($A$2:$A$16),MATCH($B$2:$B$16,$B$2:$B$16,)+1,$B$2:$B$16/FREQUENCY(LOOKUP(ROW($A$2:$A$16),MATCH($B$2:$B$16,$B$2:$B$16,)+1,ROW($A$2:$A$16)),ROW($A$2:$A$16))))*($A$2:$A$16=E2))”，向下拖曳即可。
思路： MATCH($B$2:$B$16,$B$2:$B$16,)+1部分，MATCH函數(shù)返回在單元格區(qū)域$B$2:$B$16各個數(shù)據(jù)的位置信息。由于在數(shù)據(jù)區(qū)域內(nèi)有“空”單元格存在，此函數(shù)返回的是一組數(shù)字和錯誤值組成的內(nèi)存數(shù)組。加上“1”是為了讓位置顯示為真實的位置信息。其結果為{2;3;#N/A;#N/A;6;7;#N/A;9;10;11;#N/A;#N/A;14;15;#N/A} LOOKUP(ROW($A$2:$A$16),MATCH($B$2:$B$16,$B$2:$B$16,)+1,ROW($A$2:$A$16))部分，利用LOOKUP函數(shù)在上述內(nèi)存數(shù)組中查找2-16這樣的一個序列，并返回第三個參數(shù)ROW($A$2:$A$16)中對應的值。其結果為{2;3;3;3;6;7;7;9;10;11;11;11;14;15;15}
利用FREQUENCY函數(shù)對上述結果在ROW($A$2:$A$16)這樣一個分段區(qū)間計頻，其結果為{1;3;0;0;1;2;0;1;1;3;0;0;1;2;0;0}。大家注意看，這個內(nèi)存數(shù)組中非零的數(shù)字其實就是B列中每個數(shù)字所對應的行數(shù) $B$2:$B$16/FREQUENCY(LOOKUP(ROW($A$2:$A$16),MATCH($B$2:$B$16,$B$2:$B$16,)+1,ROW($A$2:$A$16)),ROW($A$2:$A$16))部分，用數(shù)據(jù)區(qū)域$B$2:$B$16中的數(shù)值來除以上述計頻后的結果，實際上就是對合并單元格中的數(shù)值做了等分。其結果為{14;19;#DIV/0!;#DIV/0!;5;13;#DIV/0!;6;15;30;#DIV/0!;#DIV/0!;9;11;#DIV/0!;#N/A} 接下來，LOOKUP(ROW($A$2:$A$16),MATCH($B$2:$B$16,$B$2:$B$16,)+1,$B$2:$B$16/FREQUENCY(LOOKUP(ROW($A$2:$A$16),MATCH($B$2:$B$16,$B$2:$B$16,)+1,ROW($A$2:$A$16)),ROW($A$2:$A$16)))部分，再次利用LOOKUP函數(shù)，在MATCH($B$2:$B$16,$B$2:$B$16,)+1中（{2;3;#N/A;#N/A;6;7;#N/A;9;10;11;#N/A;#N/A;14;15;#N/A}）查找ROW($A$2:$A$16)，并返回在上步結果中所對應的值。其結果為{14;19;19;19;5;13;13;6;15;30;30;30;9;11;11}，這樣就為單元格區(qū)域$B$2:$B$16中對應的每一個單元格都賦值 接下來就比較簡單了，利用SUMPRODUCT函數(shù)按條件求和即可
下面的方法是運用了數(shù)組的方法。這個方法書寫起來更加簡潔易懂。 在單元格H2中輸入公式“=SUM(LOOKUP(ROW($2:$16),ROW($2:$16)/(B$2:B$16<>""),B$2:B$16/FREQUENCY(LOOKUP(ROW($2:$16),ROW($2:$16)/(B$2:B$16<>"")),ROW($2:$16)))*(A$2:A$16=E2))”，三鍵回車并向下拖曳即可。 思路： -END-
長按下方二維碼關注EXCEL應用之家 面對EXCEL操作問題時不再迷茫無助
|
