通路王

Excel 系列(十) - 如何搜尋單一儲存格回傳多個匹配的對應值?

Views

如何重設原始表格, 並搜尋單一儲存格回傳多個匹配的對應值? 

有時候, 現有的原始表格長的實在不太實用, 如下圖, 這是一個以日期為表格標題的出勤紀錄表.
但如果到了月底結算時, 想要有個表格如下圖, 可以一目了然的知道某人的出勤日有那幾天時, 怎麼辦? 

一個一個找嗎? 那肯定眼睛受不了, 沒關係, 可以先利用Excel 的 Pivot Table 來幫忙轉換表格, 然後用=TEXTJOIN()就可以輕輕鬆鬆的做出上圖的以人員為主的出勤日期表了.


步驟一:
先將表格用PivotTable Wizard 轉換成3欄式表格.

在轉換前, 要把所有員工先加入第一行. 這將會成為你Pivot Table的參考值.



用 Alt+D+P快捷鍵打開 Pivot table Wizard



自行選取表格範圍, 就Pivot Table Wizard 就會自動幫你生成一個如下圖的表格.
** 重點: 連按二下Pivot Table Grand Total, 如上圖的111, 就會自動幫你產生一個3欄位的表格.



A行會是所有員工對應所有的日期.

B行是日期

C行則是在該日有出勤的員工名, 也會是你接下來要用到的出勤日的對應名字.









步驟二:
接下來, 就是準備你的新表格了, 新表格會是一個以全體員工名字為主體的一個表格.

以全體員工名為主軸, 一邊用=countif()來計算員工的出勤日數, 另一邊則用=textjoin()來呈現出勤的日期.
在"出勤日數"下, 輸入下列公式:
E2=countif(C:C, F2)
這是在計算在C欄裡出現名叫"王一成"的人一共幾次, 出現的次數, 就是他當月的出勤日數.


然後, 就是使用=textjoin()來找出相對應人名的實際出勤的日期了.
G2=TEXTJOIN(",",TRUE,IF(C:C=F2,B:B,""))
** 重點: 按下 Ctrl+Shift+Enter
這是Excel中用於使用array公式計算的快捷鍵.

這個公式的口語化就是: 如果出現的人名="王一成", 那麼傳回所有有對應到"王一成"的B欄的資料, 中間以","來做間隔.

{=TEXTJOIN(", ", TRUE, IF(被對照值範圍=對照儲存格, 回傳值範圍, ""))}

詳情請看影片:

張貼留言

0 留言