2019/8/31 10:52:55
經常遇到這樣的(de)朋(péng)友:掌握了足夠多的(de)函數,但是遇到複雜問題,沒辦法一(yī)步到位時就感到為(wèi)難了,覺得無從下手。尤其是數據量比較大、基礎的(de)手動操作費時費力的(de)時候更是讓人無法自(zì)拔。
在這裏,我分享一(yī)個“大數化小”,把原始的(de)“大數據”看作“小(1)數據”,構建解決方案,然後反推至“大數據”調整,最後進行(xíng)驗證,逐步解決的(de)思路。
1、從中拎出來一(yī)個企業的(de)數據來模拟操作:
單個企業數據的(de)手動操作步驟:①選中該單位的(de)全部關鍵詞所在區域B2:B9,②直接複制,③點選C2單元格,④然後采用選擇性粘貼進行(xíng)轉置;
Ok,單個企業的(de)操作就完成了。該企業的(de)關鍵詞就被轉成一(yī)行(xíng),放置在了C2:J2的(de)區域裏。
接下來如(rú)何将第1步的(de)操作推廣至全部數據呢(ne)?
2、找出必須的(de)手動操作步驟,匹配能将之實現的(de)函數:
從步驟1的(de)操作可(kě)以明确兩個必須的(de)操作:一(yī)個是轉置;另一(yī)個容易被忽略,那就是轉置的(de)基礎:選定單元格區域。
對應的(de)兩個函數是:轉置函數(Transpose)和(hé)選區函數(Offset),我個人稱之為(wèi):主體函數。
3、以必須的(de)函數為(wèi)框架配置輔助條件:
首先,構建一(yī)個數據庫,将企業名稱字段作為(wèi)唯一(yī)的(de)索引。這一(yī)步手動操作更便捷,可(kě)以對企業名稱列使用數據透視(shì)表,也可(kě)以複制企業名稱列到新的(de)sheet,然後删除重複項實現。
其次,找出數據量從1到n時,主體函數中發生變化的(de)參數(動态參數):Transpose函數的(de)參數(Array區域)雖然發生了變化,但這個變化是通過Offset函數來實現的(de),所以這裏我們(men)隻考慮Offset函數就可(kě)以了。
Offset函數有(yǒu)5個參數,動态參數有(yǒu)2個:區域的(de)起始行(xíng)号(Rows)和(hé)區域的(de)高(gāo)度(Height)。Offset函數語法如(rú)下:
最後,把動态參數也通過其他函數來獲得。動态參數1:區域的(de)起始行(xíng)号Rows,即為(wèi)原始數據裏該企業名稱首次出現的(de)行(xíng)數,用Match函數精确匹配即可(kě)得到。動态參數2:區域的(de)高(gāo)度(Height),即為(wèi)該企業名稱出現的(de)次數,用Countif函數可(kě)以得到。
4、把所有(yǒu)輔助條件彙總,編制一(yī)個嵌套函數公式:
由于嵌套函數的(de)結果為(wèi)一(yī)個Array,所以需要用到數組函數來鍵入(Ctrl + Shift + Enter),這樣就得到了文章(zhāng)開頭的(de)數組嵌套函數公式。
{=TRANSPOSE(OFFSET(關鍵詞!$B$1,MATCH(A2,關鍵詞! A:A,0)-1,0, COUNTIF(關鍵詞!A:A,Sheet2!A2),1))}。
5、最後一(yī)步也是最重要的(de)一(yī)步:數據量從n到1的(de)驗證。抽出任一(yī)單位的(de)關鍵詞進行(xíng)驗證。
小結
當我們(men)遇到量比較大的(de)數據處理(lǐ)時,應首先嘗試從單個數據處理(lǐ)中找到基礎的(de)解決方案,再放置到全數據的(de)場景中,根據數據量從1到n的(de)變化找出動态參數進行(xíng)修正,逐步補足所需條件,最終實現高(gāo)效的(de)目标。這種比較常規的(de)構思方式,不知道(dào)大家get到沒有(yǒu)?希望對大家的(de)Excel學(xué)習和(hé)工作有(yǒu)所幫助!