中秋節要到了,行銷部準備一些禮物要送給往來藝人,希望禮物清單可以展開為一列一筆,好進行後續分配,該如何快速處理呢?
最終結果呈現方式:
蘋果有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成為你的專業招牌!