贊助廠商

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

搜尋本部落格文章資料

網友問到:在 Excel 中有一個如下圖(上)的資料表,要轉換成下圖(下)的格式,該如何處理?參考下圖,上下兩個表格,是要把「進貨、銷貨、收發」由橫向轉換為直向。現在來看看要如何處理。

Excel-資料表格式轉換及查詢(OFFSET,SUMPRODUCT)


【公式設計與解析】

1. 查詢每月「期初」的值

儲存格C9:=SUMPRODUCT(($A$3:$A$6=$A9)*$B$3:$B$6)

透過 SUMPRODUCT 函數在儲存格A3:A6中比對和儲存格A9是否相同,傳回 TRUE/FALSE 陣列。

($A$3:$A$6=$A9)*$B$3:$B$6:其中「*」(乘法運算),相當於執行邏輯 AND 運算。


2. 找出每月「進貨、銷貨、收發」

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

有網友問到:在一個工作表中如果使用某些物件便於各種操作,但是有時又不想讓它顯示,該如何處理?

例如如下圖的工作表,其中有三個「微調按鈕」物件,用以調整儲存格B2所顯示的時間。如果不想顯示微調按鈕,以免時間又被改變,該如何操作呢?

Excel-在工作表中隱藏某個物件不讓使用者操作(例如微調按鈕).

你可以在[常用/尋找與選取]功能表中,選取「選取窗格」選項。

Excel-在工作表中隱藏某個物件不讓使用者操作(例如微調按鈕)

在每個不想顯示的物件右方,按一下「眼睛」圖示,這個眼睛如果瞇起來了,表示該物件被隱藏了。微調按鈕不見了,自然也就無法被操作了。

如果想要再次顯示出來,只要在[選取範圍]窗格中,再按一下想要顯示的物件右側的「瞇眼」圖示,讓眼睛打開來即可。

Excel-在工作表中隱藏某個物件不讓使用者操作(例如微調按鈕)

特別提醒:當物件被隱藏了,在列印時也不會列印出來。

Excel-在工作表中隱藏某個物件不讓使用者操作(例如微調按鈕)

如果你只是單純不想列印某個控制項物件,則可以在[控制項格式]對話框中的「摘要資訊」下,不要勾選「列印物件」,這個物件也不會被列印出來。

Excel-在工作表中隱藏某個物件不讓使用者操作(例如微調按鈕)

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

有網友問到:如果在 Excel 中有地址或地名的清單,如何製作超連結,可以在點選後直接在 Google 地圖上顯示該地點?

以下由台北市政府教育局網站取得臺北市公立高中校址及電話一覽表,我們要建立「查詢地圖」欄位,以方便點選校名後,即可在 Google 地圖上標示出該學校的位置。

Excel-在工作表中點選地址或地名就可以在Google地圖顯示該地點(HYPERLINK)


【做法與說明】

1. 使用地址

儲存格D3:=HYPERLINK("https://www.google.com.tw/maps/place/" & B3,A3)

2. 使用校名

儲存格D3:=HYPERLINK("https://www.google.com.tw/maps/place/" & A3,A3)

Excel-在工作表中點選地址或地名就可以在Google地圖顯示該地點(HYPERLINK)

如果你是要用圖示符號來建立超連結,則沒有快速的方法。

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

常有人會問到在 Excel 中以日期為主的每日或分組的數值計算,例如:計算最近幾筆和最近幾天的每日平均,或是計算幾筆和幾天的日期區間的分組平均。本篇就來看看這樣的差異,如何處理?

1. 計算最近幾筆和最近幾天的每日平均

參考下圖,要計算以 8 天為單位來計算最近幾筆和最近幾天的每日平均。

Excel-最近幾筆和最近幾天的每日與分組平均(OFFSET,SUMPRODUCT)

(1) 計算最近 8 筆的每日平均

儲存格D9:=AVERAGE(C2:C9)

複製儲存格D9,貼至儲存格D9:D28。

(2) 計算最近 8 天的每日平均

儲存格E2:
=SUMPRODUCT(($B$2:B2>B2-8)*$C$2:C2)/SUMPRODUCT(--($B$2:B2>B2-8))

SUMPRODUCT(($B$2:B2>B2-8)*$C$2:C2):計算合於最近 8 天的數值總合。

SUMPRODUCT(--($B$2:B2>B2-8)):計算合於最近 8 天的日期個數。

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

最近整理了幾篇和二進制轉換有關的先前文章,請自行點選檢視。

(1) Excel-二進制和十六進制互轉

和二進制轉換有關的應用文章

 

(2) Excel-以2的補數模擬二進制的減法

和二進制轉換有關的應用文章

和二進制轉換有關的應用文章

 

(3) Excel-製作十進位數轉二進位數的換算過程

和二進制轉換有關的應用文章 和二進制轉換有關的應用文章 和二進制轉換有關的應用文章

 

(4) Excel-二進制的應用

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼