贊助廠商

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

搜尋本部落格文章資料

如下圖的 Excel 資料表,其中的表格內容是散亂的資料分佈。要如何取出表格有資料的部分重新排列?

下圖左為原始資料,下圖右為重排後的結果。

Excel-取出表格有資料的部分重新排列(OFFSET,VLOOKUP,COUNTIF)

【公式設計與解析】

1.

先建立一個輔助欄位,用以計算資料表中每一列有內容儲存格的數量。

儲存格N2:1

儲存格N3:=COUNTA($A$3:L3)+1

計算由儲存格N2起始的累計結果。

複製儲存格N3,貼至儲存格N3:N12。

2.

儲存格P3:=MATCH(ROW(1:1),$N$2:$N$12,1)

藉由輔助欄位的內容,依序列出有資料內容的儲存格在第幾列。

複製儲存格P3,往下各列貼上。

3.

儲存格Q3:{=OFFSET($A$2,P3,SMALL(IF(OFFSET($A$2,P3,0,1,12)<>"",
COLUMN(A:L),99),COUNTIF($P$3:P3,P3))-1,1,1)}

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

複製儲存格Q3,往下各列貼上。


(1) OFFSET($A$2,P3,0,1,12)

根據儲存格P3的內容,取得同列資料的範圍,本例中,儲存格P3=1,資料範圍為儲存格A3:L3。


(2) IF(第(1)式<>"",COLUMN(A:L),99)

在陣列公式中,根據第(1)式傳回的資料範圍,若儲存格有內容,則傳回第幾欄,否則傳回99。(99是一個很大的數值,只要大於一列中的資料數量最大值(本例為12)即可)

COLUMN(A:L)代表 1~12。

本例傳回:{ 99,2,99,99,99,99,99,99,9,10,99,99 }


(3) SMALL(第(2)式,COUNTIF($P$3:P3,P3))

COUNTIF($P$3:P3,P3)用以傳回儲存格P3是同一列中的『第幾個』。(本例中有3個1,傳回 1, 2, 3。)

再利用 SMALL 函數取出『第幾個』的欄號,依序取得欄位 2, 9, 10。


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

最後,將欄號代入 OFFSET 函數取得對應的儲存格內容,依序取得 I, C, H。

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

網友根據下圖的 Excel 資料表,如何才能求得表一、表二、表三的結果。

Excel-雙條件計算個數(SUMPRODUCT)


【公式設計與解析】

選取儲存格B1:D23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:

條件一、水果、條件二。

(1) 儲存格G2:=SUMPRODUCT((條件一=$F2)*(條件二=G$1))

複製儲存格G2,貼至儲存格G2:H4。

(2) 儲存格G8:=SUMPRODUCT((水果=$F8)*(條件二=G$1))

複製儲存格G8,貼至儲存格G8:H13。

(3) 儲存格G17:=SUMPRODUCT((水果=$F17)*(條件一=G$16))

複製儲存格G17,貼至儲存格G17:I22。

Posted by vincent at 痞客邦 PIXNET Guestbook(1) 人氣()

有網友問到如下圖的 Excel 資料表中,如何計算各科的加權平均?

下圖中,每個科目都有一個加權和一個分數,要找出各科的加權平權。

Excel-計算加權平均(SUMPRODUCT)


【公式設計與解析】

選取儲存格A1:C16,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:

科目、加權、分數。

儲存格F2:

=SUMPRODUCT((科目=E2)*加權*分數)/SUMPRODUCT((科目=E2)*加權)

複製儲存格F2,貼至儲存格F2:F6。

(1) SUMPRODUCT((科目=E2)*加權*分數)

計算和儲存格E2內容相符的科目之「加權*分數」的總和。

(2) SUMPRODUCT((科目=E2)*加權)

計算和儲存格E2內容相符的科目之「加權」的總和。

(3) 加權平均=第(1)式/第(2)式

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

有老師問到:在整理資料時,如果想要將某一磁碟之下的資料夾(包含許多子資料夾),複製到另一個磁碟中,但是只要資料夾,而不要資料夾中的檔案。該如何處理?

參考下圖:在D磁碟中有一個 A 資料夾,其中包含了幾個檔案和資料夾,而資料夾之下可能還有資料夾和檔案。現在要複製 A 資料夾和子資料夾到另一個磁碟中,而不包含其中的任何一個檔案。

如何複製資料夾而不複製資料夾中的檔案

你可以藉助『命令提示字元』工具!其位於附屬應用程式之中:

如何複製資料夾而不複製資料夾中的檔案

你也可以按一下 WinKey+R,然後輸入 cmd,再按 Enter 鍵。同樣可以進入『命令提示字元』視窗。

接著,你要輸入以下格式的指令:

xcopy c:\來源資料夾 d:\目的資料夾\ /t/e

例如,將 D 磁碟的 temp 資料夾複製到 G 磁碟之下,輸入以下指令:

xcopy d:\temp g:\temp\ /t/e

其中的參數意義如下:

/E:複製每個目錄及子目錄,包含空目錄。

/T:建立目錄結構,但不複製其中的檔案。不包括空目錄或子目錄。

/T /E:建立目錄結構,並包含空目錄及子目錄。

如果要關閉這個視窗,只要輸入 exit,再按 Enter 鍵。

如何複製資料夾而不複製資料夾中的檔案

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

在 Excel 中如果你想要將資料清單中重覆者挑出來並移除,最方便的方式如下:

先選取資料範圍。(本例為儲存格A1:B20),再使用[資料/資料工具]功能表中的「移除範圍」指令。

Excel-使用進階篩選功能來移除重覆的資料

Excel 會詢問要列入檢查重覆的欄位:

Excel-使用進階篩選功能來移除重覆的資料

移除重覆後,會顯示找到幾個重覆,而保留了幾個唯一值。

Excel-使用進階篩選功能來移除重覆的資料

網友問到:如果使用的 Excel 版本並沒有如上的功能,該如何處理?你可以藉助「進階篩選」的操作。

1. 選取資料範圍。(本例:儲存格A1:B20)

2. 選取[資料/排序與篩選]功能表中的「進階」。

3. 在[進階篩選]對話框中,如下圖的設定:

勾選:將篩選結果複製到其他地方

資料範圍:$A$1:$B$20

準則範圍:$D$1:$E$1 (已先建立儲存格D1:E1和原標題儲存格A1:B1一致)

複製到:$D$1:$E$1

最重要的是要勾選:不選重覆的記錄

Excel-使用進階篩選功能來移除重覆的資料

被篩選出來的結果都不會重覆。

Excel-使用進階篩選功能來移除重覆的資料

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

有同仁問到:在 Excel 中如果開啟 Google 表單下載之填答記錄(如下圖),當在執行『排序』時,其中有一個條件要根據時間戳記來排序,卻遇到了錯誤結果。

關於Google表單填答記錄時間戳記排序的問題

如下圖,當使用「篩選」功能中的『從A到Z排序』:

關於Google表單填答記錄時間戳記排序的問題

卻發生了如下圖的錯誤結果。(其中11:31:38卻小於8:05:23)

關於Google表單填答記錄時間戳記排序的問題

以上是何種原因造成的?

因為 Google 表單下載的時間戳記是『文字』格式來表示,而非 Excel 預設的『數值』。該如何解決?

請你仔細觀察,Goolge 表單下載的報表本身即是以日期時間由小到大排序,如果你只是為了排序目的,而沒有要使用日期時間的內容,則只要將時間戳記欄位依預設的順序給予一個流水號(1,2,3,...),即可用來排序了,完全不用文字轉換為數字的程序。

但是,如果你是要運用時間戳記欄位的內容,則必須將這個欄位的內容,由文字轉換為數值,才能往下處理。

你可以將時間戳記欄位利用「資料剖析」工具,以分隔符號「空格」,將其分成日期、上下午、時間三個欄位。

關於Google表單填答記錄時間戳記排序的問題

儲存格D2:=IF(B2="上午",A2+C2,A2+C2+1/2)

如果B欄顯示為「上午」,則只要將日期+時間,如果B欄顯示為「下午」,則還要再加上 1/2。(因為 Excel 將一天視為數值 1,所以 12 小時為 1/2。)

複製儲存格D2,往下各列貼上。

關於Google表單填答記錄時間戳記排序的問題

透過數值格式設定,自訂:yyyy/mm/dd hh:mm:ss,可以顯示較適合人閱讀的格式。

關於Google表單填答記錄時間戳記排序的問題

結果如下:

關於Google表單填答記錄時間戳記排序的問題

此時,再將日期時間排序,即可得到正確的結果:

關於Google表單填答記錄時間戳記排序的問題

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

有網友問到:最近排班時會遇到七休一的問題,如何在 Excel 中如果連續排到 7 天時即給予警示?

以下圖中的排班格式為例(排班的形式可能很多種),『V』記號表示要排班,在連續 7 天以上被排班時,給予紅色粗體字來識別。

Excel-設定連續7天以上被排班時給予警示(設定格式化的條件)

通常這類問題,都只要透過「設定格式化的條件」來處理。假設,整個日期報表是由第 2 列開始,所以從第 8 列開始設定格式化的條件。

1. 選取B8:B27。

2. 選取[常用/樣式]功能表中的「設定格式化的條件/新增規則」選項。

3. 選取規則類型:使用公式來決定要格式化哪些儲存格。

4. 輸入編輯規則:=COUNTIF(B2:B8,"V")>6

5. 設定格式:紅色粗體字。

之後,如果增加日期時,只要將已設定好格式化的條件的儲存格格式,複製到新增日期的儲存格即可,不需要再重設。

Excel-設定連續7天以上被排班時給予警示(設定格式化的條件)

Posted by vincent at 痞客邦 PIXNET Guestbook(0) 人氣()

You haven’t logged in yet, please use guest status to leave message. You can also log in with above service account and leave message

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

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

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

Please input verification code on left:

Cannot understand, change to another image

請輸入驗證碼