fbpx

-線上課程學習平台-

快速依數量展開資料列內容-Excel動態函數

中秋節要到了,行銷部準備一些禮物要送給往來藝人,希望禮物清單可以展開為一列一筆,好進行後續分配,該如何快速處理呢?

最終結果呈現方式:

蘋果有3個,資料就有3列蘋果,香蕉有2個,資料就有2列香蕉…以此類推…
解決的方式有很多種,用傳統函數或VBA都可以解決(不是很好寫而已)
而現在運用Office 365內建的動態函數(Sequence + Xlookup)
加上一點點邏輯思考,很快就可以處理,請看以下步驟…

操作步驟如下:

1..先在原資料表新增一個輔助欄位,計算出累計數量

G4儲存格是什麼意思呢?
=SUM($F4$4:F4) 意指將數量的起始儲存格鎖住,計算自起始值至目前儲存格相對位置的累計數量
完成後公式向下拉即可完成累計數量欄位

2.在I4儲存格輸入以下函數公式

=XLOOKUP(SEQUENCE(SUM(F4:F7)),$G$4:$G$7,$E$4:$E$7,,1)

分段講解如下:

一.用SUM(F4:F7) 計算出最終要產出多少列資料 (以本例說明,其值為9)
二.用Sequence(SUM(F4:F7)) 產生出連續數值 (以本例說明,即自I4開始,向下產生1~9的連續數列清單)
三. 用xlookup()依據sequence()產生的清單值進行資料內容比對

四. XLOOKUP比對方式說明:

函數中XLOOKUP(SEQUENCE(SUM(F4:F7)),$G$4:$G$7,$E$4:$E$7,,1)
=>1所在的位置為”Xlookup特有的資料比對”相符模式”,
 
有四種不同的引數可使用(預設值為:0)
 
0:完全符合,如果查找不到,回傳 #N/A 錯誤
 
-1:完全符合,如果查找不到,回傳下一個較小的值
 
1:完全符合,如果查找不到,回傳下一個較大的值
 
2:使用萬用字元。

5. 本例採用參數使用的是”1”=>意即查找不到,回傳一下較大的值

以I4儲存格說明,
 
a. Sequence()產生的連續清單在本儲存格數值為1,
b. 1在$G$4:$G$7的查找範圍中查找不到對應值
c. 故函數回傳查找範圍$G$4:$G$7中, 與查找值1相比, 下一個最大的資料值(即3)
d. 而3對應的$E$4:$E$7資料內容為”蘋果”,故I4就填入”蘋果”
e. 以此類推, 直到總共9列資料全部比對完成為止

成果如下:

作者:Excel探險家 Jackie
以學習和分享為樂,熱愛在無窮無盡的Excel裡探索Excel的無限可能!
目標用熱情和數據工具改善生活,讓Excel成為你的專業招牌!


請輸入要搜尋的內容