1. 首頁 > 生活百科 > >

excel表格文字橫向縱向變換 excel表格橫向縱向變換

生活百科

Vlookup橫豎查詢，大家在做周報，月報和季報時，會經常用到，特別是做數據分析的個人或部門，如財務部，采購部，銷售部，人事部等 。
財務部，根據月份和部門，查詢部門費用 。采購部，根據月份和采購員，查詢采購金額 。銷售部，根據月份和區域，查詢銷售額 。人事部，根據月份和部門，查詢工資 。
案例 。
如以下數據，我們要根據月份和區域查詢銷售額 。根據前面幾篇文章介紹的經驗，我們可以歸類為多條件查詢 。但條件是橫豎分布的，和以前介紹過的不一樣 。
我們該如何處理呢？

excel表格文字橫向縱向變換 excel表格橫向縱向變換

文章插圖
下面分享橫豎查詢的幾種 ***。
之一種 ***。添加輔助列的橫豎查詢 。
步驟1. 在銷售額前插入輔助列E列 。在輔助列E2輸入=A2&B2，并向下復制填充 。
excel表格文字橫向縱向變換 excel表格橫向縱向變換

文章插圖
步驟2. 創建復合查詢條件，$J2&K$1 。根據上篇文章介紹的多列數據查詢的經驗，條件橫向分布的，要加$鎖定行號；條件豎向分布的，要加$鎖定列號 。
步驟3. 將以上復合查詢列和復合查詢條件代入Vlookup公式 。
在K2輸入公式=VLOOKUP($J2&K$1,E:F,2,0) 。并向下復制填充 。
excel表格文字橫向縱向變換 excel表格橫向縱向變換

文章插圖
步驟4. 同理，
在L2輸入公式=VLOOKUP($J2&L$1,E:F,2,0) 。并向下復制填充 。在M2輸入公式=VLOOKUP($J2&M$1,E:F,2,0) 。并向下復制填充 。在N2輸入公式=VLOOKUP($J2&N$1,E:F,2,0) 。并向下復制填充 。
excel表格文字橫向縱向變換 excel表格橫向縱向變換

文章插圖
第二種 ***。Vlookup+if. 不需要添加輔助列 。
如果不能修改報表格式，或不想添加輔助列，或想展示一下自己的高超的Excel技能，就可以使用vlookup+if的方式 。
步驟1. 創建兩列復合數據列 。公式為 IF({1,0},A:A&B:B,E:E)
之一列，A:A&B:B 。將A列和B列連接成一列 。第二列，E:E 。是結果列銷售額 。If({1,0}）的目的是將之一列和第二列組合成一個兩列的數組 。步驟2. 創建復合查詢條件，$I2&J$1 。同之一種 ***。
步驟3. 將以上復合查詢列和復合查詢條件代入Vlookup公式.
在J2輸入公式=VLOOKUP($I2&J$1,IF({1,0},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。
excel表格文字橫向縱向變換 excel表格橫向縱向變換

文章插圖
步驟4. 同理，
在K2輸入公式=VLOOKUP($J2&K$1,IF({1,0},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。在L2輸入公式=VLOOKUP($J2&L$1,IF({1,0},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。在M2輸入公式=VLOOKUP($J2&M$1,IF({1,0},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。第三種 ***。Vlookup+choose. 不需要添加輔助列 。
原理同第二種 ***，只是將if換成choose.
步驟1. 創建兩列復合數據列 。公式為 Choose({1,2},A:A&B:B,E:E)
之一列，A:A&B:B 。將A列和B列連接成一列 。第二列，E:E 。是結果列銷售額 。Choose({1,2}）的目的是將之一列和第二列組合成一個兩列的數組 。步驟2. 創建復合查詢條件，$I2&J$1 。同之一種 ***。
步驟3. 將以上復合查詢列和復合查詢條件代入Vlookup公式.
在J2輸入公式=VLOOKUP($I2&J$1,Choose({1,2},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。
步驟4. 同理，
在K2輸入公式=VLOOKUP($I2&K$1,Choose({1,2},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。在L2輸入公式=VLOOKUP($I2&L$1,Choose({1,2},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。在M2輸入公式=VLOOKUP($I2&M$1,Choose({1,2},A:A&B:B,E:E),2,0) 。并按Ctrl+Shift+Enter組合鍵 。再向下復制填充 。第四種 ***。Sumproduct. 不需要添加輔助列 。
此 *** 只適用于，查詢結果為數值的情況 。但比以上 *** 簡單一些，不用添加輔助行，不需要設置復合數據列，不需要按Ctrl+Shift+Enter組合鍵 。
步驟如下 。
在J2輸入公式=SUMPRODUCT((A:A=$I2)*(B:B=J$1),E:E)，并向下復制填充；
在K2輸入公式=SUMPRODUCT((A:A=$I2)*(B:B=K$1),E:E)，并向下復制填充；
在L2輸入公式=SUMPRODUCT((A:A=$I2)*(B:B=L$1),E:E)，并向下復制填充；
在M2輸入公式=SUMPRODUCT((A:A=$I2)*(B:B=M$1),E:E)，并向下復制填充；
Sumproduct的語法結構為: Sumproduct((查詢區域1)=條件1)*(查詢區域2=條件2),結果區域) 。前面的違章“Vlookup之多條件查詢”有介紹過，大家可以翻閱前面的文章 。

推薦閱讀