3646 |
Excel如何處理咖啡店資料分析-使用POWER QUERY |
衛鳯:錦子老師您好,我們「月超愛」咖啡店的價目表,很多產品單價都是如此呈現,一目瞭然。注意到已經在Excel建立了名稱為「價目表」的表格。
而在銷售明細工作表中,如圖所示,表格裡已經有各項產品銷售數量,希望再填入單價,計算營業額。像這種場合,我第一個想到就是用VLOOKUP函數來抓取各個產品單價,但無奈VLOOKUP是以欄位為基準,面對價目表似乎無從下手起。
錦子老師:這個問題,其實妳理解錯誤就算是用VLOOKUP函數也是可以的,其做法如下:
首先由於VLOOKUP是以欄位為基準,所以我們要將價目表資料變成直式表格。
點取「資料 > 從表格」圖示。
帶入到Power Query編輯畫面如下:
。
點取「項次」欄標題,再點取Power Query編輯器上方功能區的「常用 > 移除資料行」圖示。
將項次欄刪除。
點取Power Query編輯器上方功能區的「轉換 > 取消資料行樞紐▼鈕 > 取消其他行樞紐」指令。
會將目前呈現綠色的「濃縮咖啡」這一欄是目前所選資料行,這個指令會將「大杯」、「中杯」、「特大杯」這些內容都轉譯為「屬性」,而對應的「價格」欄位內容轉譯成「值」。
按住SHIFT鍵不放,點取「濃縮咖啡」及「屬性」二個欄位標題,將二欄框選起來,再放開SHIFT鍵。
點取Power Query編輯器上方功能區的「轉換 > 合併資料行」圖示。
在【合併資料行】對話方塊,注意到「新資料行名稱」是「已合併」後,點取「確定」鈕。
點取Power Query編輯器上方功能區的「常用 > 關閉並載入」圖示。
會新增一張工作表並將Power Query編輯器載入。
點取「銷售明細」工作表D2儲存格輸入公式:「=VLOOKUP(CONCATENATE(A2,B2),工作表3!$A$5:$B$131,2,0)」後,再將公式向下複製到D3:D21(視筆數調整)儲存格。
CONCATENATE(字串1,字串2….字串255) 將所有字串合併成為一個字串。
其實公式也可以改為「=VLOOKUP(A2&B2,工作表3!$A$5:$B$131,2,0)」。
點取E2儲存格輸入公式「=C2*D2」,再將公式向下複製到E3:E21儲存格。
希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
更多相關影片教學:請點我
更多相關文章:請點我
留言列表