close

3646

Excel如何處理咖啡店資料分析-使用POWER QUERY

衛鳯:錦子老師您好,我們「月超愛」咖啡店的價目表,很多產品單價都是如此呈現,一目瞭然。注意到已經在Excel建立了名稱為「價目表」的表格。

image

而在銷售明細工作表中,如圖所示,表格裡已經有各項產品銷售數量,希望再填入單價,計算營業額。像這種場合,我第一個想到就是用VLOOKUP函數來抓取各個產品單價,但無奈VLOOKUP是以欄位為基準,面對價目表似乎無從下手起。

image

錦子老師:這個問題,其實妳理解錯誤就算是用VLOOKUP函數也是可以的,其做法如下:

首先由於VLOOKUP是以欄位為基準,所以我們要將價目表資料變成直式表格。

點取「資料 > 從表格」圖示。

image

帶入到Power Query編輯畫面如下:

image

點取「項次」欄標題,再點取Power Query編輯器上方功能區的「常用 > 移除資料行」圖示。

image

將項次欄刪除。

image

點取Power Query編輯器上方功能區的「轉換 > 取消資料行樞紐 > 取消其他行樞紐」指令。

image

會將目前呈現綠色的「濃縮咖啡」這一欄是目前所選資料行,這個指令會將「大杯」、「中杯」、「特大杯」這些內容都轉譯為「屬性」,而對應的「價格」欄位內容轉譯成「」。

image

按住SHIFT鍵不放,點取「濃縮咖啡」及「屬性」二個欄位標題,將二欄框選起來,再放開SHIFT鍵。

點取Power Query編輯器上方功能區的「轉換 > 合併資料行」圖示。

image

在【合併資料行】對話方塊,注意到「新資料行名稱」是「已合併」後,點取「確定」鈕。

image

點取Power Query編輯器上方功能區的「常用 > 關閉並載入」圖示。

image

會新增一張工作表並將Power Query編輯器載入。

image

點取「銷售明細」工作表D2儲存格輸入公式:「=VLOOKUP(CONCATENATE(A2,B2),工作表3!$A$5:$B$131,2,0)」後,再將公式向下複製到D3:D21(視筆數調整)儲存格。

image

CONCATENATE(字串1,字串2….字串255) 將所有字串合併成為一個字串。

其實公式也可以改為「=VLOOKUP(A2&B2,工作表3!$A$5:$B$131,2,0)」。

點取E2儲存格輸入公式「=C2*D2」,再將公式向下複製到E3:E21儲存格。

image

希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()