贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201701 (11)

瀏覽方式: 標題列表 簡短摘要

網友問到的 Excel 問題:參考下圖,如何在人員打卡時間的清單中,找出各日期中最早打卡和最晚打卡的時間?

在下圖中,每個日期有甲、乙、丙、丁、戊等五個人打卡,根據這個打卡清單,如何找出每天打卡的最小值(最早時間)和最大值(最晚時間)?

Excel-在多個日期的時間清單中找出每日最早和最晚時間(MIN,MAX,陣列公式)


【公式設計與解析】

(1) 定義名稱

選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、人員、打卡。


(2) 找出每日最早時間

{=MIN(IF((日期=E2),打卡,"X"))}

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

IF((日期=E2),打卡,"X"):在陣列公式中,若在日期陣列中的日期和儲存格E2相同,則傳回打卡對應的陣列內容;若不相同,則傳回「X」(這只一個任意的字元,只要不是數字即可)。

MIN(IF((日期=E2),打卡,"X")):在上式傳回的打卡陣列中,運用 MIN 函數取出陣列中的最小值。


(3) 找出每日最晚時間

{=MAX(IF((日期=E2),打卡,"X"))}

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

IF((日期=E2),打卡,"X"):在陣列公式中,若在日期陣列中的日期和儲存格E2相同,則傳回打卡對應的陣列內容;若不相同,則傳回「X」(這只一個任意的字元,只要不是數字即可)。

MAX(IF((日期=E2),打卡,"X")):在上式傳回的打卡陣列中,運用 MAX 函數取出陣列中的最大值。

文章標籤

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

很多軟體或系統的預設值,到底是對使用者有利,還是對設計者有利?

有些對你而言是垃圾郵件的郵件,或許對某些人而言,就不是垃圾了。

你有使用 Google Chrome 瀏覽器嗎?現在有愈來愈多的網站設定有一些訊息通知的功能,Chrome 瀏覽器在這方面,很貼心的會先要求你要授權是否顯示通知(預設會顯示這個授權通知)。

參考下圖,當你看到了要求權限設定時,可以有三個動作:

允許:按下「允許」時,在你沒有開啟 Chrome 瀏覽器時,也會在右下角工作列顯示該網站提供的通知。

封鎖:按下「封鎖」時,不會顯示該網站的通知,也不會再詢問要求權限。

忽略:按下「X」關閉視窗時,每次到了這個網站,就會重覆再詢問要求權限。

但是對多數人而言,不只沒有要接收通知,而且大部分的人都不想要顯示這個「要求授權顯示通知」的「通知」,該如何處理?

Chrome-關閉「要求授權顯示通知」的「通知」

作法如下:

1. 進入 Chrome 的設定頁面。(chrome://settings/)

2. 到[隱私權]區域,按一下[內容設定]按鈕。

Chrome-關閉「要求授權顯示通知」的「通知」

3. 到[通知]區域中,選取「不允許任何網站顯示通知」。

(預設在「網站要求顯通知時詢問我」)

4. 如果你想有些個別要接受通知的網站,則按一下[管理例外情況]按鈕。

Chrome-關閉「要求授權顯示通知」的「通知」

5. 在此輸入網址,再設定允許/封鎖。在這個視窗中也可以刪除已經允許的通知。

Chrome-關閉「要求授權顯示通知」的「通知」

Google 的說明中也提到,除了在「主機名稱模式」之下,輸入例外狀況的網址之外,也可以將整個網域設為例外,要先輸入『*.』。例如:設定 [*.]google.com,可以同時將 drive.google.com 和 calendar.google.com 也都設為例外。設定時也接受 IPv4 位址、IPv6 位址或 HTTP 以外的網址。

文章標籤

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

網友提問:在 Excel 中有一個資料清單(如下圖左),如何能由日期欄位篩選出某一月份中所有特定項目的資料?

在下圖中的原始資料有:日期、項目、敘述和金額共四個欄位,要根據月份和項目二個條件來篩選出敘述和金額這二個欄位的內容,該如何處理?

Excel-使用兩個條件篩選資料(OFFSET,SMALL,ROW,MONTH,陣列公式)

 

【公式設計與解析】

選取原始資料清單中日期和項目二個欄位中的所有資料,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、項目。

儲存格F5:{=IFERROR(OFFSET($C$1,SMALL(IF((MONTH(日期)=$G$1)*
(項目=$G$2),ROW(日期),""),ROW(1:1))-1,0),"")}

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

複製儲存格F5,貼至儲存格F5:F13。

(1) IF((MONTH(日期)=$G$1)*(項目=$G$2),ROW(日期),"")

在陣列公式中,根據二個條件:

條件一:MONTH(日期)=$G$1

MONTH 函數可以取出一個日期的月號。

判斷日期陣列中每個日期的月份是否和儲存格G1內容相符,傳回 TRUE/FALSE 陣列。

條件二:項目=$G$2

判斷日期陣列中每個項目是否和儲存格G2內容相符,傳回 TRUE/FALSE 陣列。

公式中的『*』運算子,相當於執行邏輯 AND 運算。當二個條件都符合者,藉由 ROW函數傳回日期列號的陣列。否則,傳回空字串。

(2)SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數,在傳回的列號陣列中,由小至大依序取出列號。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

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

藉由第(2)傳回的列號,代入 OFFSET 函數,取得對應的儲存格內容。

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

利用 IFERROR 函數在第(3)式的結果傳回錯誤訊息(錯誤來自第(2)式),則顯示空字串(空白)。

同理:

儲存格G5:{=IFERROR(OFFSET($D$1,SMALL(IF((MONTH(日期)=$G$1)*
(項目=$G$2),ROW(日期),""),ROW(1:1))-1,0),"")}

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

複製儲存格G5,貼至儲存格G5:G13。

文章標籤

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

今天是2017年1月13日星期五,有人覺得這是個特殊的日子。要如何利用 Excel 來找出近五年中,恰好是13日星期五的日子?

Excel-列出近五年13號星期五的日期(SMALL,ROW,WEEKDEY,陣列公式)


【公式設計與解析】

首先,把近五年的起訖日期(2017/1/1~2021/12/31)所代表的數值找出來。

儲存格B2:=N(A2);傳回第一天日期代表的數值:42736。

儲存格B3:=N(A3);傳回最末天日期代表的數值:44561。

接著,找出13日星期五的日期,設定公式:

儲存格D2:{=SMALL(IF((DAY(ROW(42736:44561))=13)*(WEEKDAY(ROW
(42736:44561),2)=5),ROW(42736:44561),99999),ROW(1:1))}

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

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

(1) ROW(42736:44561)

在陣列公式中可以代表一個日期區間(2017/1/1~2021/12/31)。

(2) DAY(ROW(42736:44561))=13

條件一:判斷每個日期中的『日』數是否為『13』,傳回 TRUE/FALSE 陣列。

(3) WEEKDAY(ROW(42736:44561),2)=5

條件一:判斷每個日期是否為『星期五』,傳回 TRUE/FALSE 陣列。

利用 WEEKDAY 函數,其中的參數『2』,表示傳回值 1~7 對應星期一~星期日。

Excel-列出近五年13號星期五的日期(SMALL,ROW,WEEKDEY,陣列公式)

(4) IF((條件一)*(條件二),ROW(42736:44561),99999)

『*』運算相當於執行邏輯 AND 運算,如果符合二個條件者(既是13日,也是星期五),傳回 ROW(42736:44561) 對應的數值,否則,傳回 999999(這只是一個大於44561的任意數)。

(5) SMALL(第(4)式,ROW(1:1))

當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。再利用 SMALL 函數由小到大依序取出第(4)式中傳回的數值。

(6) 將儲存格D2:D12設定日期格式為『yyyy/mm/dd』,大功告成。

Excel-列出近五年13號星期五的日期(SMALL,ROW,WEEKDEY,陣列公式)

文章標籤

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

網友問到 Excel 的問題:如何判斷三個項目中,那一個和其他二個不一樣?

如下圖,每一列中有三個項目,其中一個和另二個不相同,如何找出那一個不一樣?

Excel-挑出三個中不一樣的是那一個(INDEX,OFFSET,COUNTIF)


【公式設計與解析】

(以下公式不適用三者皆不相同者)

儲存格E2:=INDEX(A2:C2,1,(A2=B2)*3+(A2=C2)*2+(B2=C2)*1)

複製儲存格E2,貼至儲存格E2:E7。

A2=B2、A2=C2、B2=C2若是成立時,會傳回 TRUE,或是不成立,會傳回 FALSE,當經過運算(*3、*2、*1)時,會將 TRUE/FALSE 轉換為 1/0。而 3, 2, 1 代表第 3, 2, 1 欄。

透過 INDEX 函數將上述的第幾欄代入,求得對應的儲存格內容。

或是改成 OFFSET 函數的公式:

儲存格E2:=OFFSET(A1,1,(A2=B2)*3+(A2=C2)*2+(B2=C2)*1-1)

如果三個儲存格都是相同者,要顯示空白,若有一個不同者,再套用上述公式,則公式修改為:

儲存格E2:=IF(COUNTIF(A2:C2,A2)<3,INDEX(A2:C2,1,(A2=B2)*3+(A2=C2)*2+
(B2=C2)*1),"")

藉由 COUNTIF(A2:C2,A2)<3 判斷是否三個儲存格的內容都相同,若不是再代入上述的公式。

文章標籤

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

網友問到 Excel 的問題:如何在數個區間中根據資料求得編號?

如下圖,資料被置放在不同的四個區間中,現在要根據一個資料內容,來求得其編號,該如何處理?

Excel-在數個區間中根據資料求得編號(SUMPRODUCT,OFFSET)


【公式設計與解析】

儲存格B9:=OFFSET(A1,SUMPRODUCT((B2:K5=B8)*ROW(B2:K5))-1,
SUMPRODUCT((B2:K5=B8)*COLUMN(B2:K5))-2)

(1) 求得資料的列號:SUMPRODUCT((B2:K5=B8)*ROW(B2:K5))

ROW(B2:B5):會傳回每個儲存格的列號。

(2) 求得資料的欄號:SUMPRODUCT((B2:K5=B8)*COLUMN(B2:K5))

COLUMN(B2:K5):會傳回每個儲存格的欄號

(3) 根據欄號和列號求得編號:OFFSET(A1,第(1)式-1,第(2)式-2)

由儲存格A1為起始點,將列號減 1,將欄號減 2,即可獲得對應的儲存格位置。

文章標籤

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

網友問到:在 Excel 的工作表中有一個日期清單,如何依月份列出名冊?(參考下圖)

Excel-在日期清單中依月份列出名冊(OFFSET,SMALL,ROW,MONTH,陣列公式)


【公式設計與解析】

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

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

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


(1) IF(MONTH($A$2:$A$44)=COLUMN(A:A),ROW($A$2:$A$44),"")

在陣列公式中找出每個日期符合各欄位月份的列號。(參考以下示意圖)

Excel-在日期清單中依月份列出名冊(OFFSET,SMALL,ROW,MONTH,陣列公式)


(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數由小至大依序取出列號。ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(在 SMALL 函數中可能出現錯誤訊息(#NUM!))

image


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

依據第(2)式中的列號代入 OFFSET 函數,可以取得對應儲存格的內容。

Excel-在日期清單中依月份列出名冊(OFFSET,SMALL,ROW,MONTH,陣列公式)


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

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

文章標籤

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

網友問到:在 OneNote 中如果置入含有文字的圖片,在圖片上按右鍵,選取「複製圖片的文字」,即可自動辨識出文字到剪貼簿中。但是如果圖片中的文字是『直書』,則辨識後的結果,其中的文字排列順序,並非我們日常所即的樣子。該如何處理?

參考下圖,OneNote中的圖片最左側一行,辦識後置於第一列,而左側第二行,辨識後置於第二列,...。其結果和我們日常生活的習慣有違背,該如何處理?

Google文件-解決OneNote中無法處理直書文字的辨識(免費)

你可能要改採以下的方法,就不用想破頭找軟體來處理了,而且免費即可完成。

1. 先將圖片上傳至你的 Google 雲端硬碟。

Google文件-解決OneNote中無法處理直書文字的辨識(免費)

2. 接著在圖片上按一下右鍵,選取「選擇開啟工具/Google 文件」。

Google文件-解決OneNote中無法處理直書文字的辨識(免費)

這個新增的 Google 文件,立即會進行文字辨識。並且會很人性化的符合國人使用習慣,掃描文字時,順序為:由上至下,由右至左;文字顯示時,順序為:由左至右,由上至下。如此,便符合讀者所需。

image

文章標籤

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

網友問到:在 Excel 中如果要產生一個萬年月曆,該如何處理?

如下圖,每個月結束的日期各不相同,如何運用公式,讓結尾的儲存格產生空白?

Excel-產生一年的所有日期(DATE,COLUMN,IFERROR)


【公式設計與解析】

(1) 產生每個月的第一天

儲存格A3:=DATE($A$1,COLUMN(A1),1)

複製儲存格A3,貼至儲存格A3:L3。

COLUMN(A1)=1,公式向右複製時產生COLUMN(A1)=1→COLUMN(B1)=2→COLUMN(C1)=3→...。

DATE 函數代入年、月、日(=1)參數,即可產生一個日期(每月的第一天)。

(2) 產生每個月的其他天

儲存格A4:=IFERROR(IF(MONTH(A3+1)>MONTH(A$3),"",A3+1),"")

複製儲存格A4,貼至儲存格A4:L33。

MONTH(A3+1)>MONTH(A$3):判斷儲存格A3的日期加 1 後的月份,是否大於該欄的第一天的月份,若是表示已是下個月,顯示為空白;若不是則顯示前一個日期加 1 天。

若公式產生錯誤訊息,則以 IFERROR 函數令其顯示空白。

如果儲存格只想顯示『月/日』,則將日期儲存格的數值格式自訂為『mm/dd』。

Excel-產生一年的所有日期(DATE,COLUMN,IFERROR)

如果儲存格只想顯示『日』,則將日期儲存格的數值格式自訂為『dd』。

Excel-產生一年的所有日期(DATE,COLUMN,IFERROR)

文章標籤

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

網友問到 Excel 的問題:如下圖,如何把原始資料中的每個數字除以 100?

這個例子的原始資料中有三個數字,並且利用 2 個『*』加以隔開。

Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

 

【公式設計與解析】

本例將不使用 FIND 函數和 SEARCH 函數來找尋『*』的位置,再取出三組數字。這次將要用特殊的做法,巧妙來取出三組數字。

儲存格C2:

=LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100&"*"&MID(SUBSTITUTE(A2,
"*",REPT(" ",20)),15,15)/100&"*"&RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),
6)/100

(1)轉換儲存格內容:SUBSTITUTE(A2,"*",REPT(" ",20))

先利用 REPT 函數產生 20 個空格(" "),再利用 SUBSTITUTE 函數將『*』置換成 20 個空格。

Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

 

(2) 計算第一組數字:LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100

LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6):利用 LEFT 函數取出轉換後的儲存格內容最左邊 6 個字元(假設數字加小數點不超過 6 個數)。當這個 6 個字元的字串除以 100 時,Excel 會自動將串轉換為數值(空格會自動消除)。

Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

(3) 計算第二組數字:MID(SUBSTITUTE(A2,"*",REPT(" ",20)),15,15)/100

MID(SUBSTITUTE(A2,"*",REPT(" ",20)),15,15):利用 MID 函數由第 15 個字取 15 個字元(取出的 15 個字元中必定包含第二組數字)。再藉由將此結果除以 100,得到其中的數字。

Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

(4) 計算第三組數字:RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100

RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),6):利用 RIGHT 函數取出轉換後的儲存格內容最右邊 6 個字元(假設數字加小數點不超過 6 個數)。再藉由將此結果除以 100,得到其中的數字。

Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

(5) 組合字串:第(2)式&"*"&第(3)式&"*"&第(4)式

文章標籤

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

學校同仁問到的 Word 操作問題:(參考下圖)當操作段落縮排時,如果要符合首行凸排、左右對齊時,在多個段落中出現了幾個不一致的地方(下圖紅色方塊處),該如何處理?

在下圖中,編號2-1之後,再接一個空格,而左邊的縮排是對齊空格之後的位置,但是卻在所有段落中出現幾個對齊位置不一致的地方。尤是在段落中的文字不全然是全型字時,即半型字和全型字交雜的段落中更容易出現這種狀況。

Word-改善設定段落左右對齊時的未對齊問題

改進方式很簡單:只要將每個編號後的『空格』改成設定『定位點(按 Tab 鍵)』來取代,沒有對齊的現象即可改善。

Word-改善設定段落左右對齊時的未對齊問題

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼