贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到 Excel 的問題:參考下圖,如何將十六進制數右移1位?

Excel-將十六進制數右移1位元(BIN2HEX,HEX2BIN,LEN)


【公式設計與解析】

儲存格D2:=BIN2HEX(LEFT(HEX2BIN(A2),LEN(HEX2BIN(A2))-1))

複製儲存格D2,貼至儲存格D2:D15。

(1) HEX2BIN(A2)

利用 HEX2BIN 函數將儲存格A2內的十六進制數轉換為二進制數。

(2) LEFT(HEX2BIN(A2),LEN(HEX2BIN(A2))-1)

將第(1)式轉換後的二進制數右移 1 位元。

LEN(HEX2BIN(A2)):利用 LEN 函數計算HEX2BIN(A2)的位元數。

(3) BIN2HEX(LEFT(HEX2BIN(A2),LEN(HEX2BIN(A2))-1))

利用 BIN2HEX 函數將第(2)式的傳回值(二進制數)轉換為十六進制數。

, , ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友提問:在 Excel 的工作表中有二組資料(如下圖),如何指定起始和終止欄位,並計算在這個區間中二組相同和不相同的個數?

Excel-比對二組資料在指定起始和終止欄位內計算相同個數(SUMPRODUCT,INDIRECT)


【公式設計與解析】

儲存格B6:=SUMPRODUCT(1*((INDIRECT(B4&"1:"&B5&"1"))=
(INDIRECT(B4&"2:"&B5&"2"))))

(1) INDIRECT(B4&"1:"&B5&"1")

利用儲存格B4和儲存格B5的內容,轉換取得儲存格範圍(本例為儲存格D1:H1)。

(2) INDIRECT(B4&"2:"&B5&"2"):

利用儲存格B4和儲存格B5的內容,轉換取得儲存格範圍(本例為儲存格D2:H2)。

(3) (INDIRECT(B4&"1:"&B5&"1"))=(INDIRECT(B4&"2:"&B5&"2"))

SUMPRODUCT 函數中判斷第(1)式和第(2)式的儲存格陣列是否相同,傳回 TRUE/FALSE

(4) 1*(INDIRECT(B4&"1:"&B5&"1"))=(INDIRECT(B4&"2:"&B5&"2"))

將第(3)式傳回的 TRUE/FALSE,轉換為 1/0

第(4)式所有 1 的個數和,即為第1列和第2列相同的個數。


同理:

儲存格B7:=SUMPRODUCT(1*((INDIRECT(B4&"1:"&B5&"1"))<>
(INDIRECT(B4&"2:"&B5&"2"))))

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:在 Excel 中的工作表有一個原始資料(如下圖左),有一個比對欄位(如下圖右),經比對之後,要列出重覆者的位置(儲存格位址),該如何處理?

Excel-兩個清單比對後傳回重覆者的位址(MATCH,ADDRESS)


【公式設計與解析】

儲存格D2:=IFERROR(ADDRESS(MATCH(C2,$A$2:$A$20,0)+1,1,4),"")

複製儲存格D2,貼至儲存格D2:D27。

(1) MATCH(C2,$A$2:$A$20,0)

利用 MATCH 函數比對儲存格C2的內容,在儲存格A2:A20範圍的位址。若是比對後,是不存在的內容,則會傳回錯誤訊息。

(2) ADDRESS(MATCH(C2,$A$2:$A$20,0)+1,1,4)

根據第(1)式傳回的位址,利用 ADDRESS 函數傳回其對應的儲存格名稱。

(3) IFERROR(ADDRESS(MATCH(C2,$A$2:$A$20,0)+1,1,4),"")

利用 IFERROR 函數將第(1)式傳回的錯誤訊息以空白(空字串)顯示。

, ,

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

網友提問:

如下圖,在 Excel 的工作表中如何將圖上方的資料表,摘要成圖下方的結果?

Excel-根據日期、人員摘要對應星期幾各天小計(SUMPRODUCT,INDIRECT)


【公式設計與解析】

選取儲存格A2:O8,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:日期、甲、乙、丙、丁、戊、己。

儲存格B13:

=SUMPRODUCT((WEEKDAY(日期,2)=COLUMN(A:A))*INDIRECT($A13))

複製儲存格B13,貼至儲存格B13:H18。

條件:WEEKDAY(日期,2)=COLUMN(A:A),其中 WEEKDAY 函數的參數『2』,表示傳回的數字「1~7」對應「星期一~星期日」。

Excel-根據日期、人員摘要對應星期幾各天小計(SUMPRODUCT,INDIRECT)

COLUMN(A:A)向右複製公式時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。

INDIRECT($A13):將儲存格A12的文字內容透過 INDIRECT 函數轉換為儲存格位址,而該儲存格內容(「甲」),已經在先前定義好名稱(範圍是儲存格B3:O3)。

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友根據這篇:Excel-向下填滿空白儲存格,詢問如何在 Google 試算表中完成相同工作。參考下圖,要如何能自動填空白儲存格為上一個儲存格的內容?

Excel-在Google試算表中執行向下填滿空白儲存格

我能想到的做法如下:(圖中的標題AAA是有做用的)

1. 選取儲存格A1:A17。

Excel-在Google試算表中執行向下填滿空白儲存格

2. 選取功能表中的「篩選」。

Excel-在Google試算表中執行向下填滿空白儲存格

3. 點選儲存格A1中的篩選圖示,再點選「清除」,再勾選『空白』。

(結果是只選取了空白儲存格)

Excel-在Google試算表中執行向下填滿空白儲存格

4. 在儲存格A3中輸入公式:=A2。

Excel-在Google試算表中執行向下填滿空白儲存格

5. 拖曳儲存格A3右下角的控制點(滑鼠形狀會改變)至儲存格A17。

Excel-在Google試算表中執行向下填滿空白儲存格

結果如下:

Excel-在Google試算表中執行向下填滿空白儲存格

6. 接著取消篩選,或是點選「全部選取」。

Excel-在Google試算表中執行向下填滿空白儲存格

結果如下,已將空白儲存格填入上一個儲存格的內容了。

Excel-在Google試算表中執行向下填滿空白儲存格

,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼