贊助廠商

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

搜尋本部落格文章資料

同時有二個讀者問到類似的問題。在下圖左是一個 Excel 清單,如何計算日期區間中各個項目的數量小計(如下圖右)?

Excel-計算日期區間中各個項目的小計(SUMPRODUCT)


【公式設計與解析】

選取儲存格A1:C29,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、項目、數量。

儲存格F5:=SUMPRODUCT((日期>=$F$1)*(日期<=$F$2)*(項目=E5)*數量)

條件一:(日期>=$F$1)*(日期<=$F$2)

設定日期區間條件,其中『*』運算子相當於執行邏輯 AND 運算,兩個條件都成立時才會傳回 TRUE,否則傳回 FALSE。即日期介於儲存格F1和儲存格F2之間者才會傳回 TRUE

條件二:(項目=E5)

項目陣列中和儲存格E5相同者傳回 TRUE,否則傳回 FALSE

SUMPRODUCT 函數中執行『*』運算,會將 TRUE/FALSE 轉換為 1/0

參考以下的示意圖:

Excel-計算日期區間中各個項目的小計(SUMPRODUCT)

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

讀者問到:在下圖左的資料清單中有四個字元,分別被標示多個數字,該數字是其位置的順序。如何將其轉換為下圖右依其位置填入字元?

下圖左之中的所有數字因代表位置,所以均不會重複。

Excel-在資料清單中依位置數字填入對應位置(SUMPRODUCT,OFFSET)

【公式設計與解析】

儲存格G1:

=OFFSET($A$1,SUMPRODUCT(($B$1:$D$4=F1)*ROW($B$1:$D$4))-1,0)

(1) ROW($B$1:$D$4)

SUMPRODUCT 函數中,傳回儲存格B1:D4中每一個儲存格的列號。

(2) SUMPRODUCT(($B$1:$D$4=F1)*ROW($B$1:$D$4))

($B$1:$D$4=F1)*ROW($B$1:$D$4):在 SUMPRODUCT 函數中的『*』運算會將判斷式傳回值 TRUE/FALSE 轉換為 1/0,相當執行邏輯 AND 運算。最後會傳回符合條件的儲存格之列號。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式傳回的列號置於 OFFSET 函數即可找到對應的內容。

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

在下圖的 Word 文件中,若是已經有對章、節、小節等標題結構設定了樣式,並且有文字內容,要如何取出文件中的大綱標題?

如果你開啟了『功能窗格』,便可以清楚看到該文件的標題結構:

Word-如何取出文件中的大網標題

先查詢一下內容文字的樣式(此例為「內文」樣式)。

Word-如何取出文件中的大網標題

查詢得知「內文」樣式的大網階層為『本文』。其他(章、節、小節)使用大網階層1, 2, 3。

Word-如何取出文件中的大網標題

在此,若要直接取出大綱內容,目前我沒有特別的做法。但是若將本文樣式的內容全部以空白取代,結果相當於將內文全數刪除。參考以下的做法:

1. 開啟[尋找及取代]對話,切換至[取代]標籤。

2. 點選[尋找目標]文字方塊。

3. 點選[格式]按鈕,再選取「段落」選項。

Word-如何取出文件中的大網標題

4. 在[尋找段落]對話框的[縮排與行距]標籤下,在大網階層的下拉式清單中選取「本文」。

Word-如何取出文件中的大網標題

5. 在[取代為]文字方塊中保持空白(沒有任何字元)。

6. 按一下[全部取代]按鈕。

image

結果如下,所有的內文字均以刪除。

Word-如何取出文件中的大網標題

你也可以在[格式]按鈕中,選取「樣式」,再尋找「內文」樣式,並由空白取代。

Word-如何取出文件中的大網標題

其他做法:

你也可以在 PowerPoint 中新增投影片時,選取[從大綱插入投影片]選項:

Word-如何取出文件中的大網標題

如此即取得所有大綱標題,但也發現自訂的多層清單無法顯示。

Word-如何取出文件中的大網標題

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

網友問到一個 Excel 公式運算的問題:如下圖,如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)?

在下圖中,年月是10501者,有 1, 2, 3, 4 共四種配編類別,該如何求得?

Excel-計算符合條件者的不重覆數量(SUMPRODUCT,COUNTIF)


【公式設計與解析】

儲存格G2:

=SUMPRODUCT(($A$2:$A$26=F2)*(1/COUNTIF($C$2:$C$26,$C$2:$C$26)))

(1) $A$2:$A$26=F2

SUMPRODUCT 函數中設定條件判斷儲存格A2:A26中和儲存格F2相同者。

(2) COUNTIF($C$2:$C$26,$C$2:$C$26)

SUMPRODUCT 函數中,計算儲存格C2:C26中每個數的重覆個數。例如:『1, 1, 1, 2, 2, 3, 3, 4, 4』傳回『3, 3, 3, 2, 2, 2, 2, 2, 2』

(3) 1/COUNTIF($C$2:$C$26,$C$2:$C$26)

將第(2)的傳回值取倒數,例如『3, 3, 3, 2, 2, 2, 2, 2, 2』傳回『1/3, 1/3, 1/3, 1/2, 1/2, 1/2, 1/2, 1/2, 1/2』,當在 SUMPRODUCT 函數加總時會得到結果『4』。

可另外參考以下二篇的說明:

Excel-計算不重覆的數值個數

Excel-計算儲存格範圍中不重覆的數值個數(SUMPRODUCT,COUNTIF)

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

最近和學校同仁們討論到我自己在準備教材呈現上的做法。如果要將做好的講義放在網頁上,可以達到環保效果(不要列印),並且隨時、隨地都可以閱讀,教師可以隨時開講。然而每個老師們都有自己慣用的方式及最有效的法寶,不過使用上大多還是和個人專長及手邊工具為主。

這篇文章是要描述自己平常給老師們上研習課的時候,個人的教材呈現方式。

我會先準備的工具:

●個人部落格(學不完.教不停.用不盡)(http://isvincent.pixnet.net/blog)

●Evernote 記事本

●Google 雲端硬碟

●Dropbox 雲端硬碟

●Microsoft 雲端硬碟

●其他

我使用 Evernote 的筆記來編寫研習講義,除了平常就有使用 Evernote 搜集資料、記錄事情的習慣之外,因為其提供了『輕鬆轉換為網頁』及『方便使用者搜集』的二大好處。

以本篇文章用 Evernote 網頁呈現:https://goo.gl/t4Fa1m,你可以試著和你現在看到的網頁比較一下。或是使用 QR Code 掃描後在行動裝置上觀看。

在 Evernote 中編寫的講義,會一直保留在我的記事本中,方便管理和日後再利用。而編輯講義時也很方便從平常準備好的記事中,擷取資料加以組織成講義內容。透過電腦上的 Evernote 應用程式或是在瀏覽器中連線編輯記事都是可以的。

用Evernote編寫講義並轉換為網頁

將其轉換為網頁,使用者若是也有使用 Evernote,可以儲存講義網頁為自己的 Evernote 記事:

用Evernote編寫講義並轉換為網頁

在 Evernote 記事轉換成的網頁,對於圖片在呈現上會有特別的處理:

用Evernote編寫講義並轉換為網頁

如何將一個記事轉換為網頁?只要在 Evernote 的記事上按右鍵,選取「共用/複製URL」,該 URL 為網址,可於瀏覽器中看到該網頁的內容:

用Evernote編寫講義並轉換為網頁

該 URL 是一個很長的網址,可以先用 Google短網址(https://goo.gl/) 做轉換:

用Evernote編寫講義並轉換為網頁

除了獲取短網址以方便他人輸入網址,也可以產生 QR Code 的圖片檔方便手機和平板的掃描:

用Evernote編寫講義並轉換為網頁

日後你也可以根據短網址的分析報表(Analytics Data),得知該網頁被使用的狀況:

用Evernote編寫講義並轉換為網頁

而講義的網頁內容主要的呈現方式是一些文字搭配超連結,超連結主要有以下幾種:

●連結至自己的部落格:學不完.教不停.用不盡 http://isvincent.pixnet.net/blog

●連結至自己編寫的 Google 文件

●連結至自己放在 Docs.com 的文件(https://docs.com)

●連結至他人編寫的內容(網站、部落格)

●連結影片(YouTube或其他)

●連結投影片(Prezi、Microsoft Sway)

若有需要讓使用者下載檔案時,會先將一些檔案先行壓縮成一個檔案或是整個資料夾放至雲端硬碟,再開放下載(下載網址會先利用 Google 短網址轉換):

●Google 雲端硬碟

●Dropbox 雲端硬碟

●Microsoft OneDrive 雲端硬碟

有需要檔案上傳時,會利用 Dropbox 雲端硬碟的『檔案請求』功能:

用Evernote編寫講義並轉換為網頁

用Evernote編寫講義並轉換為網頁

如果有問卷要填寫,則會利用 Google 表單:

用Evernote編寫講義並轉換為網頁

對一個老師而言,製做教材和講義,其實都是無時、無刻都在進行的,所以我取用自己每天都會使用的工具(Evernote)來搭配,操作上就會方便且簡單。我的講義不講求過度美工,如果你強度要美化的網頁,Evernote 其實是做不到的。

講義和教材不只要製作方便,更要能易於分享和能讓使用者取得,這些因素也都是我使用 Evernote 來製作研習教材的主要考量因素。

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

有些人開始使用 Google 雲端硬碟的 Google 試算表來取代 Excel,或是你想要發佈一個試算表文件時,透過 Google 試算表也很方便。本文要來看看如何在 Google 試算表中製作一個圖表。

在 Google 試算表中,當你要建立一個圖表時,不妨可以由 Google 提供的「探索」來開始。以下圖在 Excel 中的一個資料表為例:

在Google雲端硬碟的試算表中建立圖表並且分享

將這個檔案上傳至 Google 雲端硬碟並開啟(如下圖),按一下視窗右下角的「探索」。

在Google雲端硬碟的試算表中建立圖表並且分享

在[探索]窗格中,由「格式設定」區中點選一種格式設定,資料表隨即會套用這個格式:

在Google雲端硬碟的試算表中建立圖表並且分享

按一下[格式設定]區中的「編輯」,即可自訂要套用至那些儲存格範圍及樣式的設定:

在Google雲端硬碟的試算表中建立圖表並且分享

設定成功後,按一下「完成」,隨即套用這個自訂的格式:

在Google雲端硬碟的試算表中建立圖表並且分享

挑一個想要的圖表格式,按一下「插入圖表」:

在Google雲端硬碟的試算表中建立圖表並且分享

工作表中會插入你選取的圖表,按一下「下拉式清單」圖示,有其他的處理工具:

在Google雲端硬碟的試算表中建立圖表並且分享

如果你想要自行插入一個圖表,則可以按一下工具列上的「插入圖表」圖示,則會開啟「圖表編輯器」,在此可以選取和設定圖表類型。在此也可以修改圖表的資料範圍。

在Google雲端硬碟的試算表中建立圖表並且分享

在[自訂圖表]標籤下,你可以自訂各種格式:

在Google雲端硬碟的試算表中建立圖表並且分享

按下[插入]即會將圖表放入工作表中,使用「進階編輯」,即可開啟「圖表編輯器」來設定:

在Google雲端硬碟的試算表中建立圖表並且分享

你可以選取圖表的「儲存圖片」功能,將這個圖表下載成一個圖片檔。

在Google雲端硬碟的試算表中建立圖表並且分享

你可以發佈這個文件和圖表:

在Google雲端硬碟的試算表中建立圖表並且分享

在Google雲端硬碟的試算表中建立圖表並且分享

發佈後如下範例:(試著移動滑鼠至圖表的曲線上)

你可以隨時停止這個圖表的分設:

在Google雲端硬碟的試算表中建立圖表並且分享

在 Google 試算表中提供的圖表工具或許和 Excel 比較顯的陽春,但是它是免費的工具,有些人也僅需簡單的呈現圖表,Google 試算表中的圖表工具也是一個不錯的選項。

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

有網友問到:在 Excel 中有一個資料清單(如下圖左),如何轉換為表格形式(如下圖右)?

在下圖左的資料清單是由類別和項目組成,在下圖右的表格中將相同的類別的項目集合在一起,該如何設計公式?

Excel-清單資料轉換為表格資料(OFFSET,陣列公式)


【公式設計與解析】

為了幫助公式理解,請先選取儲存格A1:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、項目。

儲存格E2:{=IFERROR(OFFSET($B$1,SMALL(IF(類別=$D2,ROW(類別),""),
COLUMN(A:A))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動加上「{}」。

(1) IF(類別=$D2,ROW(類別),"")

在陣列公式中,如果類別陣列的內容和儲存格D2相同者,傳回其列號,若不相同,則傳回空字串(『""』)(其目的是為了製造一個錯誤訊息)。

(2) SMALL(IF(類別=$D2,ROW(類別),""),COLUMN(A:A))

利用 SMALL 函數傳回第(1)式中的列號最小值(COLUMN(A:A)=1),當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...,可以分別取出第1, 2, 3, ... 的最小值。

(3) OFFSET($B$1,SMALL(IF(類別=$D2,ROW(類別),""),COLUMN(A:A))-1,0)

將第(2)式傳回的列號代入 OFFSET 函數可以找到對應的『項目』陣列內容。若是代入 OFFSET 函數的是空字串,則會傳回一個錯誤訊息。

(4) IFERROR(第(3)式,"")

利用 IFERROR 函數將錯誤訊息轉換為空白。


在下圖中,如果類別是不規則的排列(試和上圖比較),原公式仍可以得到想要的結果(項目內容的排序不同而已)。

Excel-清單資料轉換為表格資料(OFFSET,陣列公式)

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼