【Excel操作】資料自動帶入4 - Match+Index｜橫向

上週五分享的是縱向新增的流水帳，但如果碰到流水帳是以橫向來新增呢？那麼，上週五的公式可能就不太適合用在橫向發展的資料。不過，還是有解套的方法，我們可以再帶入一個 Match 來抓取特定位置的內容。 ▼以下圖示為範例▼「設備異常」的 設備、電池 欄位中，帶入原始「設備清單」資料。 B10儲存格輸入「=INDEX($A$E$6,MATCH($A10,$2:$A$2:$A$6,0),MATCH(B$9,$A$4:$E$4,0))」 圖一：抓資料所有的範圍「=INDEX($A$E$6,」 圖二：縱向項目「MATCH($A10,$2:$A$2:$A$6,0),」(1) 參照設備異常的單一項目「設備」(2) 設備清單的項目範圍(3) 「0」表示完全符合 圖三：橫向序號「MATCH(B$9,$A$4:$E$4,0))」(1) 參照設備異常的單一項目「序號」(2) 設備清單的序號範圍(3) 「0」表示完全符合 ▼補充說明▼ 資料範圍都已上鎖$，在複製B10儲存格貼到其他欄位，公式不會走位，也不需要再做其他調整。 【komoto_png】分享excel、word、powerpoint…等等的工作筆記，邊做紀錄邊學習，以最小篇幅的呈現方式來紀錄。如果覺得我的分享不錯的話，請給我一顆心，或者，也可以來訂閱小本喔 :D 【Patato註冊連結】點選我的「註冊連結」或是 輸入我的推薦碼：8sxgkEPHu #excel #小本的活頁簿 #match+index #查閱與參照

【Excel操作】資料自動帶入3 - Match+Index｜縱向

Vlookup 和 Hlookup 能解決大部份資料回寫的問題，不過，不曉得各位有沒有碰過，當資料回寫左側的來源時，Vlookup 是無法將左側的資料回寫。解決這個問題就要靠 Match+Index，比起 Vlookup 它更能靈活地應用。 ▼以下圖示為範例▼ 「設備異常」的 設備、電池 欄位中，帶入原始「設備清單」資料。 (黃色)B11欄位中帶入原始「設備清單」的設備名稱 B11儲存格輸入「=INDEX($A$2:$E$6,MATCH($A11,$C$2:$C$6,0),2)」 圖一：先從最裡面的Match括號來看「MATCH($A11,$C$2:$C$6,0)」 (1) 參照設備異常的「序號」 (2) 設備清單的範圍「序號」 (3) 「0」表示完全符合 圖二：再來看外面Index括號 「=INDEX($A$2:$E$6,-------,2)」 (1) 設備清單所有的資料範圍 (2) 回寫的資料是「設備」所以輸入「2」的位置 (綠色)C11欄位中帶入原始「設備清單」的電池名稱 C11儲存格輸入「=INDEX($A$2:$E$6,MATCH($A11,$C$2:$C$6,0),5)」 圖三：由於資料範圍及位置已上鎖$，所以在複製儲存格貼到C11時，公式不會走位，需要修改的地方只有「電池」的位置。這裡只需要將數字「2」改為「5」，就完成啦～ 【komoto_png】分享excel、word、powerpoint…等等的工作筆記，邊做紀錄邊學習，以最小篇幅的呈現方式來紀錄。如果覺得我的分享不錯的話，請給我一顆心，或者，也可以來訂閱小本喔 :D 【Patato註冊連結】點選我的「註冊連結」或是 輸入我的推薦碼：8sxgkEPHu #excel #小本的活頁簿 #match+index #查閱與參照

【Excel操作】資料自動帶入2 - Hlookup

如果你的資料是由左至右來新增流水帳，那麼資料回寫就要派出《Hlookup》函數。它與Vlookup的設定方法一樣，只不過範圍的回寫方向不同。小V適用在由上至下新增的流水帳，小H則是由左至右。 ▼以下圖示為範例▼ 「設備異常」的 開機、電池 欄位中，帶入原始「設備清單」資料。 圖一：(藍色)B10欄位中帶入原始「設備清單」的開機日期B10儲存格輸入「=HLOOKUP(B$9,$B$4:$E$6,2,0)」 圖二：(綠色)B11欄位中帶入原始「設備清單」的電池型號B11儲存格輸入「=HLOOKUP(B$9,$B$4:$E$6,3,0)」 ▼補充說明▼ Hlookup有四個引數，和昨天分享的Vlookup一樣。 【Lookup_value】B$9主標籤，它需要一個標籤，才能找到相對應的內容；位置上鎖「$」，鎖定9的位置。 【Table_array】$B$4:$E$6搜尋範圍，將主標籤及相關回寫的內容全部選取起來；範圍上鎖「$」，整個搜尋範圍是固定的（絕對位置）所以將整個範圍鎖定。 【Row_index_num】2回寫「開機」日期，它位於搜尋範圍「2」的位置；如果要回寫的是「電池」，這裡就要改成「3」。 【Range_lookup】0「0」表示完全符合，也可以輸入「FALSE」，得到的結果是一樣的。 ＊＊＊快捷鍵「F4」：上鎖欄位，可快速上鎖單一引數。 【komoto_png】分享excel、word、powerpoint…等等的工作筆記，邊做紀錄邊學習，以最小篇幅的呈現方式來紀錄。如果覺得我的分享不錯的話，請給我一顆心，或者，也可以來訂閱小本喔 :D 【Patato註冊連結】點選我的「註冊連結」或是 輸入我的推薦碼：8sxgkEPHu #excel #小本的活頁簿 #hlookup #查閱與參照

【Excel操作】資料自動帶入5 - Xlookup

先前分享的《Vlookup》《Hlookup》《Match+Index》會不會覺得太過複雜？其實，還有更簡單的函數，沒記錯的話，在去年微軟推出新函數《Xlookup》它擁有前面三個函數的特性，學會它之後，前幾篇分享的東西可以直接放生了XD ▼以下圖示為範例▼ 「設備異常」的 開機、電池 欄位中，帶入原始「設備清單」資料。 圖一：(藍色)B11欄位中帶入原始「設備清單」的開機日期B11儲存格輸入「=XLOOKUP($A11,$C$2:$C$6,$D$2:$D$6)」 圖二：(綠色)C11欄位中帶入原始「設備清單」的電池型號C11儲存格輸入「=XLOOKUP($A11,$C$2:$C$6,$E$2:$E$6)」 ▼補充說明▼Xlookup有四個引數，如下： 【Lookup_value】$A11主標籤，它需要一個標籤，才能找到相對應的內容；位置上鎖「$」，鎖定A的位置。 【Lookup_array】$C$2:$C$6對應設備清單的序號列；範圍上鎖「$」，序號範圍是固定的位置（絕對位置）所以將整個範圍鎖定。 【Return_array】$D$2:$D$6回寫設備清單的開機日期；也是絕對位置，將開機日期的範圍上鎖。 ＊＊＊快捷鍵「F4」：上鎖欄位，可快速上鎖單一引數。 ＊＊＊根據這一題，需要異動的地方，只有【Return_array】，範圍重新選取就可以分別去抓「開機」及「電池」的設備清單。 【komoto_png】分享excel、word、powerpoint…等等的工作筆記，邊做紀錄邊學習，以最小篇幅的呈現方式來紀錄。如果覺得我的分享不錯的話，請給我一顆心，或者，也可以來訂閱小本喔 :D 【Patato註冊連結】點選我的「註冊連結」或是 輸入我的推薦碼：8sxgkEPHu #excel#小本的活頁簿 #xlookup #查閱與參照

【Excel操作】資料自動帶入1 - Vlookup

無法忽視的excel函數《Vlookup》整理資料八九不離時一定都會使用到它。這也是小本第一個學到的函數，當時還請同事來教我，教了兩三遍小本才學會它。 ▼以下圖示為範例▼ 「設備異常」的 開機、電池 欄位中，帶入原始「設備清單」資料。 圖一：(藍色)B11欄位中帶入原始「設備清單」的開機日期B11儲存格輸入「=VLOOKUP($A11,$C$2:$E$6,2,0)」 圖二：(綠色)C11欄位中帶入原始「設備清單」的電池型號C11儲存格輸入「=VLOOKUP($A11,$C$2:$E$6,3,0)」 ▼補充說明▼ Vlookup有四個引數，如下說明： 【Lookup_value】$A11主標籤，它需要一個標籤，才能找到相對應的內容；位置上鎖「$」，鎖定A的位置。 【Table_array】$C$2:$E$6搜尋範圍，將主標籤及相關回寫的內容全部選取起來；範圍上鎖「$」，整個搜尋範圍是固定的（絕對位置）所以將整個範圍鎖定。 【Col_index_num】2回寫「開機」日期，它位於搜尋範圍「2」的位置；如果要回寫的是「電池」，這裡就要改成「3」。 【Range_lookup】0「0」表示完全符合，也可以輸入「FALSE」，得到的結果是一樣的。 【komoto_png】分享excel、word、powerpoint…等等的工作筆記，邊做紀錄邊學習，以最小篇幅的呈現方式來紀錄。如果覺得我的分享不錯的話，請給我一顆心，或者，也可以來訂閱小本喔 :D 【Patato註冊連結】點選我的「註冊連結」或是 輸入我的推薦碼：8sxgkEPHu #excel #小本的活頁簿 #vlookup #查閱與參照

#查閱與參照搜尋結果

【Excel操作】資料自動帶入4 - Match+Index｜橫向

【Excel操作】資料自動帶入4 - Match+Index｜橫向

【Excel操作】資料自動帶入3 - Match+Index｜縱向

【Excel操作】資料自動帶入3 - Match+Index｜縱向

【Excel操作】資料自動帶入2 - Hlookup

【Excel操作】資料自動帶入2 - Hlookup

【Excel操作】資料自動帶入5 - Xlookup

【Excel操作】資料自動帶入5 - Xlookup

【Excel操作】資料自動帶入1 - Vlookup

【Excel操作】資料自動帶入1 - Vlookup