|
先看下源數據和要實現(xiàn)的效果。下圖左表是源數據，包含要重復的內容和次數，如①所示。右邊兩列是重復后的效果，如②所示。也就是A重復2次，B重復3次，以此類推。首先，我們用輔助列的方法來完成。在C列添加輔助列，C3單元格輸入公式=SUM(B$3:B3)，向下填充。得到上圖C列的效果，實際就是對次數的累加。輔助列構建好，在E2單元格輸入公式=LOOKUP(1,0/FREQUENCY(ROW(A1),C$3:C$6),A$3:A$6)&""，向下填充，完成。這里主要用到frequency和lookup。lookup的經典查詢套路，lookup(1,0/(條件區(qū)域=條件),返回的區(qū)域)，我相信很多同學都會了。主要的是frequency的用法，它是一個頻率分步函數，統(tǒng)計一組數據在區(qū)間間隔中出現(xiàn)的次數。函數語法結構如下，FREQUENCY(data_array, bins_array) ，有2個參數。第1參數是要計算頻率分布的數組，第2參數是區(qū)間間隔。frequency會返回一個垂直數組。下面舉一個簡單的例子說明一下frequency的基礎用法，如下圖所示。左表是一個成績表，現(xiàn)在要統(tǒng)計各區(qū)間分數的個數有多少個？各區(qū)間如綠色部門所示。簡單來說，就是在B列的成績中，統(tǒng)計小于等于60分的有幾個，大于60且小于等于70分的有幾個，以此類推。那么就可以用frequency來計算，選中E2:E6單元格，輸入公式=FREQUENCY(B2:B9,{60,70,80,90})，按ctrl+shift+enter三鍵結束。要注意的地方是frequecny返回的結果是一個垂直數組{0;2;1;5;0}，也就是元素之間用分號分隔，同時它的元素個數比第2參數{60,70,80,90}多一個。它的統(tǒng)計規(guī)則其實就是我上面寫的區(qū)間規(guī)則，60對應的是小于等于60的個數，70對應的是大于60小于等于70的個數，80對應的是大于70小于等于80的個數，90對應的是大于80小于等于90的個數，最后多出來的一個是大于90的個數。現(xiàn)在{60,70,80,90}是按升序排列的，假如它不是按升序排序的，函數統(tǒng)計的時候自己會先排序，但返回的結果還是要對應的。（這句話可能不好理解，但很重要）。frequency基礎用法和原理說明后，就可以看我們最開始的公式了。如下圖所示，我們先看frequency的這部分，也就是標①的那部分。第1參數row(a1)就是{1}，第2參數C3:C6就是{2;5;6;10}。frequency返回的結果是{1;0;0;0;0}，也就是②那部分，我用紅色寫出來的。那這個結果是怎么產生的？其實就是在第1參數{1}中小于等于2的有1個，大于2小于等于5的有0個，大于5小于等于6的有0個，大于6小于等于10的有0個，大于10的有0個。如果上面我說的frequency你明白了，剩下的就是lookup的用法。其他單元格是同樣的計算規(guī)律，你可以自己查看，就知道為什么可以按指定次數重復內容了。不用輔助列的方法和用輔助列是一樣的，只不過用offset的多維引用代替輔助列，直接給出公式，不再詳細說明。在G2單元格輸入公式=LOOKUP(1,0/FREQUENCY(ROW(A1),SUMIF(OFFSET(B$3,,,ROW($1:$4)),"<>")),A$3:A$6)&""，向下填充，完成。frequency的進階用法，我還在學習研究中，希望能有所進步。感興趣的同學，可以下載文件練習起來。https://pan.baidu.com/s/1_5RgUd9T3REYMbX3VkM4eg
|
