2017年1月31日 星期二

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

網友問到的 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 函數取出陣列中的最大值。

2017年1月20日 星期五

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

很多軟體或系統的預設值,到底是對使用者有利,還是對設計者有利?
有些對你而言是垃圾郵件的郵件,或許對某些人而言,就不是垃圾了。
你有使用 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 以外的網址。

2017年1月15日 星期日

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

網友提問:在 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。

2017年1月13日 星期五

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

今天是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,陣列公式)

2017年1月12日 星期四

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

網友問到 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 br="" style="letter-spacing: 0px;">(B2=C2)*1),"")
藉由 COUNTIF(A2:C2,A2)<3 p="">

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

網友問到 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,即可獲得對應的儲存格位置。

2017年1月11日 星期三

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

網友問到:在 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 函數將錯誤訊息轉換為空白(空字串)。

2017年1月10日 星期二

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

網友問到:在 OneNote 中如果置入含有文字的圖片,在圖片上按右鍵,選取「複製圖片的文字」,即可自動辨識出文字到剪貼簿中。但是如果圖片中的文字是『直書』,則辨識後的結果,其中的文字排列順序,並非我們日常所即的樣子。該如何處理?
參考下圖,OneNote中的圖片最左側一行,辦識後置於第一列,而左側第二行,辨識後置於第二列,...。其結果和我們日常生活的習慣有違背,該如何處理?
Google文件-解決OneNote中無法處理直書文字的辨識(免費)
你可能要改採以下的方法,就不用想破頭找軟體來處理了,而且免費即可完成。
1. 先將圖片上傳至你的 Google 雲端硬碟。
Google文件-解決OneNote中無法處理直書文字的辨識(免費)
2. 接著在圖片上按一下右鍵,選取「選擇開啟工具/Google 文件」。
Google文件-解決OneNote中無法處理直書文字的辨識(免費)
這個新增的 Google 文件,立即會進行文字辨識。並且會很人性化的符合國人使用習慣,掃描文字時,順序為:由上至下,由右至左;文字顯示時,順序為:由左至右,由上至下。如此,便符合讀者所需。
image

2017年1月9日 星期一

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

網友問到:在 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)

2017年1月6日 星期五

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

網友問到 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)式

2017年1月5日 星期四

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

學校同仁問到的 Word 操作問題:(參考下圖)當操作段落縮排時,如果要符合首行凸排、左右對齊時,在多個段落中出現了幾個不一致的地方(下圖紅色方塊處),該如何處理?
在下圖中,編號2-1之後,再接一個空格,而左邊的縮排是對齊空格之後的位置,但是卻在所有段落中出現幾個對齊位置不一致的地方。尤是在段落中的文字不全然是全型字時,即半型字和全型字交雜的段落中更容易出現這種狀況。
Word-改善設定段落左右對齊時的未對齊問題
改進方式很簡單:只要將每個編號後的『空格』改成設定『定位點(按 Tab 鍵)』來取代,沒有對齊的現象即可改善。
Word-改善設定段落左右對齊時的未對齊問題

檢視其他文章

好康東東