Excel將數值資料上下/垂直翻轉(不需巨集)

Excel數值上下翻轉

Excel要怎麼將數值資料上下/垂直翻轉呢?如將下方的左圖垂直翻轉成右圖。
以下帶你使用簡單的函數公式(不需巨集),快速將左圖垂直翻轉成右圖。

操作方式

Step1 插入函數

選取G1儲存格後點選「fx(插入函數)」,搜尋函數Offset」,點選「開始」。

Step2 函數引數

輸入以下函數引數

Reference:A$5
Rows:-ROW(A1)+1
Cols:0

確定後公式為以下:

=OFFSET(A$5,-ROW(A1)+1,0)

則會得到最下方的數字「21」。

Step3 拖曳公式

最後再向右與向下拖曳公式,則完成數值資料上下/垂直翻轉

公式說明

OFFSET函數

Offset根據所指定參照位址取得列數及欄數的範圍。簡單來說就是將儲存格向欄(左/右)與列(上/下)移動幾格取得資料

向上/向下移動

Reference:參照的儲存格。
Rows:要垂直(往上或往下)移動的列數。

Reference:A1
Rows:1
Cols:0

=Offset(A1,1,0)

A1儲存格向下移動1格」,則會代入「A2儲存格的值 – 「4

若將Rows輸入為「-1」,則是「向上移動1格」。
※若儲存格位於頂端如「A1」儲存格,向上移動則會出現錯誤#REF!」。

向左/向右移動

Reference:參照的儲存格。
Cols:要水平(往左或往右)移動的列數。

Reference:A1
Rows:0
Cols:1

=Offset(A1,0,1)

A1儲存格向右移動1格」,則會代入「B1儲存格的值 – 「2

若將Cols輸入為「-1」,則是「向左移動1格」。
※若儲存格位於最左側如「A1」儲存格,向左移動則會出現錯誤#REF!」。

ROW函數

Row:傳回參照位址之列號。

Reference:A1

=ROW(A1)

傳回「A1儲存格列號,則為「1」。

正數遞增

若「=ROW(A1)向下拖曳,則會依序「=ROW(A2)」、「=ROW(A3)」,則呈現正數遞增1、2、3。

負數遞減

若加上負號「-ROW(A1)拖曳,則會依序「=-ROW(A2)」、「=-ROW(A3)」,則呈現負數遞減-1、-2、-3。

垂直翻轉公式

Reference:A$5
Rows:-ROW(A1)+1
Cols:0

=OFFSET(A$5,-ROW(A1)+1,0)

Offset參照「A5儲存格
-ROWS(A1)向下拖曳時,則會呈現-1、-2、-3,也就是「A5儲存格依序向上移動一格
-ROW(A1)+1」因第一個數值是代入「A5儲存格,「-ROW(A1)向上移動1格後,再「+1向下移動一格,則位置維持不變

Excel數值左右翻轉

其他文章分享

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *