贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (1908)

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

國際標準書號(International Standard Book Number)簡稱 ISBN,是由 13 碼所組成。參考維基百科(https://zh.wikipedia.org/wiki/國家標準書號)上的說明:
國際標準書號號碼由 13 個碼的數字所組成,並以『-』或空格加以分隔,每組數字有其義涵。

○第一組:978或979。

○第二組:國家、語言或區位代碼

○ 第三組:出版社代碼:由各國家或地區的國際標準書號分配中心,分給各個出版社。

○ 第四組:書序碼,該出版物代碼,由出版社建立。

○ 第五組:檢驗碼,一位數,從 0 到 9。


檢驗碼為為了用來驗證 ISBN 是否正確之用,如何使用試算表來加以驗證?

ISBN 範例:9789863088363

前規則是:

1. 取前12碼計算加權的和(S)

依據下圖將每個位元乘上不同的加權(1、3)

Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)

2. 計算 S 除以 10 的餘數

147 ÷ 10 的餘數是 7。

3. 求 10 減掉餘數 = 檢驗碼

若餘數為 0,則檢驗碼=0。

本例 10-7=3,檢驗碼=3,兩者相符。

Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)

只要有一個數字變動,其檢驗碼就應該會不一樣。(下圖檢驗碼:0)

Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)


【公式設計與解析】

1. 計算數字加權和(S)

儲存格C5:=SUMPRODUCT(B1:M1*B2:M2)


2. 計算檢驗碼

儲存格C7:=MOD(10-MOD(SUMPRODUCT(B1:M1*B2:M2),10),10)

(1) MOD(SUMPRODUCT(B1:M1*B2:M2),10)

利用 MOD 函數計算加權和除以 10 的餘數。

(2) MOD(10-第(1)式,10)

10-第(1)式:計算 10 減餘數的結果。

再利用 MOD 函數計算除以 10 的餘數。

讓第(1)式結果為 0 者,本式傳回 0,否則傳回第(2)的結果。


如果你想試試一個儲存格就寫出公式,可以這樣做:

Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)

儲存格B2:

{=MOD(10-MOD(SUM((MOD(ROW(1:12),2)=1)*MID(B1,ROW(1:12),1)*1)+
SUM((MOD(ROW(1:12),2)=0)*MID(B1,ROW(1:12),1)*3),10),10)}

(1) SUM((MOD(ROW(1:12),2)=1)*MID(B1,ROW(1:12),1)*1)

計算加權乘積中要乘以 1 者的和。

(2) SUM((MOD(ROW(1:12),2)=0)*MID(B1,ROW(1:12),1)*3)

計算加權乘積中要乘以 3 者的和。


文章標籤

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

網友問到:在 Excel 中,如果想要將一大堆有題號的題目要刪除題號,該如何處理?

參考下圖:在A欄中的題目每一題都有題號,題號有2碼、3碼、4碼,之後還有『.』。

Excel-刪除每個題目的題號(FIND,MID)


【公式設計與解析】

儲存格B2:=MID(A2,FIND(".",A2)+1,999)

(1) FIND(".",A2)

利用 FIND 函數找出儲存格A2中的內容第 1 個『.』。

(2) MID(A2,FIND(".",A2)+1,999)

利用 MID 函數找出『.』之後的文字,其參數『999』只是一個很大的數,只要比儲存格中的字數多即可。

複製儲存格B2,貼至儲存格B2:B14。


文章標籤

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

日前有六天的日本京阪神自由行,回顧整個行程對數位科技的應用及其帶來的幫助,以下就跟著我一起來探討。(文中的伙伴其實都是家人,一行人都是伙伴。)

○ 人:5人(含家庭成員兩大兩小+外甥一人)

○ 時:2017/8/9~2017/8/14

○ 地:途經日本京都、大阪、神戶等地

日本自由行從規劃到旅行結束之數位科技的應用


《行前規劃》

(為了聚焦,所以省略訂房與訂機票等。)由我負責主要行程規劃,大約花了二週時間找資料和規劃路線行程。我是菜鳥一個,所以相當仰賴網路上部落客提供的資料。

人家說「有錢 AND 有閒」才能去旅行,我們全家硬是擠出一點錢和一點閒,當然要好好的規劃,才不會辜負這難得的放空之旅。雖然是自由行的新手,但是只要好好做功課,應該也不會差到那裡去。(回來後才發現,所做的功課全派的上用場。而且給孩子一個「境教」,不管孩子參與規劃程度的多寡,大家都一起共同經歷了這一些。)


●在電腦和手機利用 Evernote 擴充程式儲存搜尋的網頁

平常我就使用 Evernote 來做筆記和搜集資料,所以這次自由行規劃路線,也是採用 Evernote 來完成。透過電腦版和手機版的 Evernote 的結合,資料源源不斷的被收錄。

Evernote:https://evernote.com/

在手機中安裝了 Evernote App,在瀏覽器上查詢到的資料,透過分享即可傳輸到雲端的筆記本中。透過和雲端同步,電腦上就能看到這些手機搜集的資料。畢竟手機還是比較能隨時、隨地的瀏覽資料,所以如何保存這些資料,相對重要多了。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

在 Google Chrome 瀏覽器上,透過 Evernote Web Clipper 擴充功能,將查詢到網頁收納於 Evernote 筆記中。

日本自由行從規劃到旅行結束之數位科技的應用

一篇一篇搜集而來的網路文章,就像是做筆記般的整理在 Evernote 中,隨時可以在手機和電腦中查閱並整理。

日本自由行從規劃到旅行結束之數位科技的應用

有些旅遊相關的文章,每日都會透過 Feedly 進到我的閱讀世界,閱讀過後,如果要保留,我也是透過 Evernote 搜集保存下來。網頁內容會自動送來,收錄又如此的方便,所以每天都有一些資料被保留下來。養兵千日,終有一朝會用上。

Feedly:https://feedly.com

日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用

●利用 Evernote 做旅遊規劃

看了別人提供的那麼多資料,也該是自己要實際規劃路線了!過程中當然是一日數變,改來改去,直到出發了,還是不滿意、不確定。因為規劃內容放在筆記中,在手機可以改,在電腦可以改,在雲端可以改,隨時可以改、隨地可以改,不怕改!

日本自由行從規劃到旅行結束之數位科技的應用

因為要給多人知道規劃的狀況,所以將 Evernote 筆記產生網頁,並透過 Google(https://goo.gl)轉換為短網址,用 Line 傳送給伙伴們知悉。這樣大伙們討論的基礎點才會一致。

日本自由行從規劃到旅行結束之數位科技的應用


●利用 Google Chrome 列印網頁成 PDF 檔放在手機上

因為查詢到的網頁內容有其參考價值,為了減少查詢時間和網路數據用量,也可以加快資料取得速度。(當然不考慮列印出來,即不環保也不好攜帶。廉航重量=費用)直接將在 Google Chrome 中查到的網頁資料列印成 PDF 檔,再儲存至手機中。雖然不見得都用的到,但是有備無患,而且查到資料後,舉手之勞而已!(手機存了很多檔案,但是重量都沒有變重。)

日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用



●將檔案上傳 Google 雲端硬碟並設定為離線閱讀

以上所儲存的 PDF 檔如何傳輸至手機呢?方法很多,隨你方便,至少可以用 USB 傳輸到手機上。而我是在電腦中先上傳至 Google 雲端硬碟(https://drive.google.com),再將手機的 Google 雲端硬碟 App 中設定檔案為「離線閱讀」。如此,在手機中開啟這些 PDF 檔,便不用再連線取得檔案來開啟。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用


●利用 Backup and Sync from google 將檔案上傳 Google 雲端硬碟

我是利用 Goolge 剛推出的 Backup and Sync from google 工具,透過同步方式來上傳至 Google 雲端硬碟。用此工具,可以同步需端和我的電腦中的檔案,相當方便!

Backup and Sync from google:https://www.google.com/drive/download/

日本自由行從規劃到旅行結束之數位科技的應用

稍加設定,便可輕鬆使用!

日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用


●使用 Google 地圖進行路線規劃

關於整個自由行中對於 Google 地圖的相關運用,請參考這篇前面的文章:

帶著我的Goolge地圖一起到日本京阪神自由行,其主要內容有:

 

 《旅行前》
 ●在 Google 地圖上標記想去的地點並且儲存
 ●在 Google 地圖上建立我的地點
 ●在 Google 地圖上量測到達地點的距離
 ●在 Google 地圖上查詢地點、計算時間並規劃路線
 ●將 Google 地圖上的路線新增至主畫面
 ●在 Google 地圖上先利用3D模式檢視景點
 ●在 Google 地圖上先行下載「離線地圖」
 ●在 Google 地圖上先將需要查詢的地點都輸入查詢一次

 《旅行中》
 ●在 Google 地圖上找到自己所處的位置
 ●在 Google 地圖上查詢要去的地點並且使用街景檢視
 ●在 Google 地圖上對要去的地點查詢評價及熱門時段
 ●在 Google 地圖上量測到達地點的距離
 ●在 Google 地圖上標記到達的地點以加入我的時間軸
 ●在 Google 地圖上即時查詢地點並規劃路線
 ●在 Google 地圖上開啟事先已規劃的路線
 ●在 Google 地圖上將已規劃的路線分享給同行伙伴
 ●在 Google 地圖上使用「標籤」來定位飯店或特定地點
 ●在 Google 地圖上使用「儲存停車位」來定位集合地點
 ●在 Google 地圖上分享位置給同行伙伴
 ●在 Google 地圖上「啟動行車模式」來導航路線
 ●在 Google 地圖上檢視訊息

 《旅行後》
 ●在 Google 地圖上調整我的時間軸上的地點和時間
 ●在 Google 地圖上將 Google 相簿中的相片加入地點
 ●在 Google 地圖上檢視「我的貢獻」



●利用 Line App 分享資訊和互相討論對話 

平常伙伴們想到什麼,就用 Line 即時對話,傳遞超連結和照片等。由於有些圖片和內容會有時效的問題,所以先將伙伴們建立一個群組,再將討論資料和照片放在群組的記事本和相簿中。自由行途中,也是都這樣互傳訊息和照片等。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用


●利用 Chromecast 和 Chrome 瀏覽器將電腦投放到電視上多人進行討論

為了進行行前會議等討論工作,所以透過 Chrome 瀏覽器,結合 Google 的 Chromecast,將我的電腦上的相關資料內容,投影到家裡的電視機上,以進行討論工作。(家裡PC和電視有段距離)

我一邊在電腦上操作,伙伴們看著電視,七嘴八舌的你一言我一語,要修改的地方,我就直接在電腦上修改,陸陸續續就這樣形成了行程的共識。

當你接好了 Google chromecast 後,再選取 Chrome 瀏覽器中的「投放」。

日本自由行從規劃到旅行結束之數位科技的應用

再選取:投放桌面。(所有電腦的操作都會顯示在電視上)

日本自由行從規劃到旅行結束之數位科技的應用

連線成功,瀏覽器右上角會顯示 chromecast 圖示。

日本自由行從規劃到旅行結束之數位科技的應用

電視畫面已和電腦同步顯示了。

日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用


●先行在 Google Translate App 中下載日文語言以離線翻譯

設想面對日文和日語時,應該會用到 Google Translate App 來執行翻譯的工作,為了加快翻譯的速度,所以先行下載日文資料庫,就可以方便進行離線翻譯。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

文字輸入或語音輸入中文字來翻譯成日文字,或是掃描日文字來翻譯成日文字,甚至唸出來,都不成問題。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

買東西、看資料時,拿起來掃描一下日文字,答案隨手可得,還真是方便。


《旅行途中》

●使用 Google 地圖查詢景點、路線、時間、票價和交通狀況

關於整個自由行中對於 Google 地圖的相關運用,請參考這篇:

帶著我的Goolge地圖一起到日本京阪神自由行

日本自由行從規劃到旅行結束之數位科技的應用


●租用 WiFi 分享器讓伙伴共用網路

出國沒有 WiFi 是不行的!WiFi 分享器極為重要,此行的伙伴共有 5 人,小孩的網路用量高,所以租用了二台 WiFi 分享器,每台每日 99 元。從早上8:00,開到晚上8:00,電力就快沒了,所以常常出動行動電源來救援。

規格如下:

○使用機型 / SoftBank-303ZT、SoftBank-501HW(2款隨機出貨)
○下載速度 / 4G LTE 最高187.5Mbps
○每日使用流量 / 無限制
○使用時間 / 持續使用約12小時
○連線台數 / 5-8台
○訊號涵蓋範圍 / 全日本適用 / 距離12~25公尺


●取另一支沒在使用的手機當作查詢地圖用

你的上一隻手機現在在那裡?

手邊正好有一隻汰換下來幾個月的 HTC M8,自由行的時候順便帶出國來使用(當做備用手機),主要是用在 Google 地圖的使用上,可以減少現用手機的使用電量和其他不時之需。先將出國前製作好的「你的地點中的地圖」分享給備用手機的登入的帳號,相關地點即可以在備用手機中輕鬆取得。

出國前已先將 Google 地圖 App 的版本更新到最新版,查詢路線、班次和時間、票價等,相當的方便。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用


●使用 Google Translate 查詢日文

出發前,你已在手機的 Google Translate App 中下載了日文資料庫,當你遇到想要了解的日文字,可以使用 App 對著日文字拍照。現在 Google 提供多元的翻譯方式,無論利用拍照、麥克風輸入或手寫輸入等都能接受。

日本自由行從規劃到旅行結束之數位科技的應用

在拍好的照片中(掃描),用心指選取一些日文字,會即時翻譯成中文。(記得先設定日文→中文)

日本自由行從規劃到旅行結束之數位科技的應用

關於翻譯,你還有別的 App 可以選擇,例如使用 Microsoft Translate App,使用方式也是都可以透過用寫的、用唸的、用拍照的等方法來做翻譯。並且除了翻成文字,還能唸出翻譯好的文字。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用


●使用 Google Fit 記錄活動狀況

自由行中最重要的『行』,如何知道這幾天中,每一天的步行時間、步行距離、消耗熱量、步行步數等數據?你的自由行是都在坐車?還是都在走路?還是...

我的手機裝有 Google Fit App,所以可以隨時隨地記錄我的活動狀況,並且可以在時間軸中回顧每一天的活動狀態。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

我的小米手環和 App 也隨時在記錄我的運動狀態,都可以提供一些參考。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用


●自由行利用 Google 表單記帳

有記帳習慣的人,可以先行利用 Google 表單來簡單設計一個記帳的表單。仔細想想,你真的會這麼認真費工的每一筆消費都加以記錄?你願意花費額外的時間來記帳?

放在 Google 表單的消費記錄,可以轉成 Excel 檔,日後才能統計與分析。

日本自由行從規劃到旅行結束之數位科技的應用


●將相機中的照片利用藍牙傳輸到手機

其他伙伴都是使用手機拍照,只有我是使用單眼相機來拍照!一天下來,每天都拍了超過 300 張照片。我都是啟動相機中的藍牙,配合手機上的 APP,每天晚上將一天的成果,一次傳輸到我的手機上。再由我的手機,挑選照片上傳 Facebook 或是傳到 Line 分享給同行伙伴們。

日本自由行從規劃到旅行結束之數位科技的應用

一躺難得的旅程,只用手機來拍照,對我而言是不夠的。使用單眼相機,就算不是攝影高手,拍出來的影質也都不會太差,上千張照片,總有一些是好的吧!不枉費我每天背相機14小時以上。(不好意思!路人借我用一下。)

日本自由行從規劃到旅行結束之數位科技的應用


●隨時用 Google Keep 記錄所看、所思

自由行過程,有時步調快,有時步調慢,例如搭火車時,就有些空閒時間可用。我會利用手機中的 Google keep App,用唸方式把看到、想到的事情即時記錄下來,之後再微調文字。

Google keep 可以輸入文字之外,還可以附加照片和錄音檔等,也可以利用手寫方式來記錄自己的想法,是個輕巧、實用的好幫手。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

這些記錄的文字,還可以直接放在 Google 文件中繼續編輯。

日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用


《旅行結束》

●利用 Google 相簿整理相片

旅行結束後,大量的照片一定要馬上整理,不然以人的墮性,很快就會遺忘了這件事。

整理的照片除了放在電腦硬碟中,我還會上傳至 Google 雲端硬碟當做備份。如此,可以在電腦上回味,也可以分享給他人看。如果用手機上的 Google 相簿 App 來看,也是相當方便實用。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

當相片已分類放在雲端相簿中,將來可以運用關鍵字進行智慧搜尋,例如:食物、河…。當然你也可以找『人』,如果你有先設定人的臉孔,只要輸入例如:兒子,Google 會將所有的兒子照片全數列出。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

如果你拍攝照片的手機或相機有啟動 GPS 記錄,則檢視放在 Google 相簿中的照片時候,也會同時顯示照片所在的位置。

日本自由行從規劃到旅行結束之數位科技的應用

當你在雲端檢視 Google 相簿中的一張有人的照片時,Goolge 也會即時的幫你找出照片中可以辨識的人臉有那些人。

日本自由行從規劃到旅行結束之數位科技的應用


●將相簿中的照片上傳各個地點(我的貢獻)

將拍的還不錯的照片提供給其他網友參考,所謂「取之於網路,用之於網路,回饋於網路」。所以,你可以進一步將雲端相簿中的照片放入各個地點,以供其他網友查看。不論美景、美食,通常照片都會比文字來的更有吸引力。(在地點中加入照片,不一定要從Google雲端相簿選取照片,也可以由手機和電腦硬碟中上傳照片檔。)

日本自由行從規劃到旅行結束之數位科技的應用

日本自由行從規劃到旅行結束之數位科技的應用


●統計 Google Fit 搜集的活動記錄

Google Fit App 已在自由行的過程中,完整的記錄了每一天的步行時間、步行距離、消耗熱量、步行步數等數據,行程結束可以來檢視和統計相關資料了。

日本自由行從規劃到旅行結束之數位科技的應用 日本自由行從規劃到旅行結束之數位科技的應用

自由行結束了,連上 Google Fit 網站(https://fit.google.com),可以在此擷取自由行這幾天的資訊。

日本自由行從規劃到旅行結束之數位科技的應用

當你點選了一個日期(本例:8/13),可以看到你要的資訊。

日本自由行從規劃到旅行結束之數位科技的應用

逐一將每一天的資訊複製到 Excel 的工作表中,再透過公式來計算六天來總共的:步行時間、步行距離、消耗熱量、步行步數。

日本自由行從規劃到旅行結束之數位科技的應用

計算方式可參考這篇:

Excel-計算多個儲存格中文數字內容的數字總和(SUMPRODUCT,MID,INT,MOD)

日本自由行從規劃到旅行結束之數位科技的應用

旅行也是生活的一部分,旅行會用到的數位工具,也是生活中會用到的數位工具。工具是為幫助我們生活的更快樂,為了幫助我們解決問題的。旅行結束了,你要重新檢視數位工具對於你的生活產生的影響,是否有些數位工具要重新來學習了呢?

孩子們擅長使用的自拍棒,也算是一項數位工具?

日本自由行從規劃到旅行結束之數位科技的應用

文章標籤

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

先前到日本自由行六天,由 Google fit 中(https://fit.google.com)取出每一天的步行時間、步行距離、消耗熱量、步行步數等資料清單。(如下圖)

如何利用 Excel 來計算下圖中的小計?

Excel-計算多個儲存格中文數字內容的數字總和(SUMPRODUCT,MID,INT,MOD)


【公式設設與解析】

選取儲存格B2:E7,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:步行時間、步行距離、消耗熱量、步行步數。

1. 步行時間(儲存格B8)

=INT(SUMPRODUCT(MID(步行時間,1,1)*60+MID(步行時間,4,2))/60)&"小時"&
TEXT(MOD(SUMPRODUCT(MID(步行時間,1,1)*60+MID(步行時間,4,2)),60),"00")&
"分鐘"

(1) MID(步行時間,1,1)*60

SUMPRODUCT 函數中取出儲存格陣列裡步行時間中的小時數。

(2) MID(步行時間,4,2))/60

SUMPRODUCT 函數中取出儲存格陣列裡取出步行時間中的分鐘數。

(3) INT(SUMPRODUCT(MID(步行時間,1,1)*60+MID(步行時間,4,2))/60)

SUMPRODUCT 函數中計算總時間的小時部分。

(4) MOD(SUMPRODUCT(MID(步行時間,1,1)*60+MID(步行時間,4,2)),60)

SUMPRODUCT 函數中計算總時間的分鐘部分。

(5) TEXT(第(4)式,"00")

利用 TEXT 函數將分鐘數顯示為 2 位元。


2. 步行距離(儲存格C8)

=SUMPRODUCT(MID(步行距離,1,LEN(步行距離)-3)*1)&" 公里"


3. 消耗熱量(儲存格D8)

=TEXT(SUMPRODUCT(MID(消耗熱量,1,LEN(消耗熱量)-4)*1),"0,000")&" 卡路里"


4. 步行步數(儲存格E8)

=TEXT(SUMPRODUCT(MID(步行步數,1,LEN(步行步數)-2)*1),"000,000")&" 步"



在 Google fit 中(https://fit.google.com)每一天的步行時間、步行距離、消耗熱量、步行步數等如下圖,複製到 Excel 即可運算。

Excel-計算多個儲存格中文數字內容的數字總和(SUMPRODUCT,MID,INT,MOD)

文章標籤

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

網友問到的Excel問題,可以供其他網友參考。

Excel-利用SMALL,LARGE函數將MIN,MAX傳回值0改為空白

參考上圖,如果使用以下陣列公式來求得上班打卡時間和下班打卡時間。

儲存格B2:{=MIN(IF((日期=$A2)*(姓名=$A$1)*(刷卡時間>=TIME(6,0,0))*
(刷卡時間<TIME(10,30,0)),刷卡時間,""))}

儲存格C2:{=MAX(IF((日期=$A2)*(姓名=$A$1)*(刷卡時間>=TIME(12,0,0))*
(刷卡時間<TIME(23,59,59)),刷卡時間,""))}

其中未取得資料的儲存格會因儲存格原先的格式設定,而出現00:00的結果,要如何將其以空白顯示?

修改如下圖:

Excel-利用SMALL,LARGE函數將MIN,MAX傳回值0改為空白

【公式設設與解析】

儲存格B2:{=IFERROR(SMALL(IF((日期=$A2)*(姓名=$A$1)*(刷卡時間>=TIME
(6,0,0))*(刷卡時間<TIME(10,30,0)),刷卡時間,""),1),"")}

儲存格BC:{=IFERROR(LARGE(IF((日期=$A2)*(姓名=$A$1)*(刷卡時間>=TIME
(12,0,0))*(刷卡時間<=TIME(23,59,59)),刷卡時間,""),1),"")}

其中主要的調整是將 MIN、MAX 函數改以 SMALL、LARGE 函數取代。

因 MIN 函數和 MAX 函數當查不資料時所傳回的空字串("")會視為 0,而改用 SMALL、LARGE 函數時會因傳回空字串("")而找不到對應資料,會傳回錯誤訊息(#NUM!),再利用 IFERROR 函數將其改以空字串顯示。

文章標籤

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

(延伸閱讀:日本自由行從規劃到旅行結束之數位科技的應用

今年暑假的重要大事,是在日前進行了我的第一次日本京阪神自由行!(下圖為金閣寺)

帶著我的Goolge地圖一起到日本京阪神自由行


在六天的旅行過程中,Google 地圖扮演了重要的角色。平常我就手機不離手,Google 地圖也是天天在使用,當到了人生地不熟的異域自由『行』的時候,Google 地圖更是發揮了小兵立大功的作用。

在旅行的前、中、後不同階段,透過電腦、手機和雲端的綜合運用,將 Google 地圖與規劃的行程緊密結合。為了讓 Google 地圖知道你的位置,當然你得先開啟手機中的 GPS 位置功能。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行



《旅行前》

●在 Google 地圖上標記想去的地點並且儲存

帶著我的Goolge地圖一起到日本京阪神自由行

開始規劃自由行時,可以先在電腦或手機上,將要去的地點加以儲存,以利後續的搜尋。(你得登入 Google 帳號,才能儲存。)

帶著我的Goolge地圖一起到日本京阪神自由行

在手機上你可以將地點分三類儲存:最愛、想去地點、已加星號的地點。(這三類在電腦版上都是以星號表示)。

帶著我的Goolge地圖一起到日本京阪神自由行

相同地點可以『複選』:最愛、想去地點、已加星號的地點。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

當你點選:你的地點,可以在「已儲存」中查詢各類已存的地點。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

(下圖是在京都二寧板八坂茶屋,是全球唯一榻榻米座席星巴克!)

帶著我的Goolge地圖一起到日本京阪神自由行



●在 Google 地圖上建立我的地點

帶著我的Goolge地圖一起到日本京阪神自由行

出發旅行前,先將你要去的地點建立在我的地圖上,可以設定不同圖層,並且給予不同圖示和色彩。我點選「分享」後,即可分享給同行的伙伴們檢視。

帶著我的Goolge地圖一起到日本京阪神自由行

在手機上也可以檢視和分享自己規劃的地點,一人規劃,多人共享。我準備了1.5倍數量的地點,使自由行造訪的地點能有些彈性。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

停一下!你規劃的行程是要緊湊的不斷趕火車,還是要悠閒的慢遊?在規劃之時,要先在心中多想一想!

帶著我的Goolge地圖一起到日本京阪神自由行

帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上量測到達地點的距離

自由行規劃路線時,距離的長短對行程相當的重要!因為大家的腳力是否能承受,也要考量。你可以先透過電腦的 Google 地圖先量測兩個地點之間的距離。

在一個地點上按右鍵,選取「測量距離」。

帶著我的Goolge地圖一起到日本京阪神自由行

拖曳黑線上的圓點來改變路線及長度,立即會顯示總長度。

帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上查詢地點、計算時間並規劃路線

你只要設定起迄地點,就可以進行路線規劃:

(這篇或許有幫助:Google地圖-只留下多個路線規劃的其中一條路線

帶著我的Goolge地圖一起到日本京阪神自由行

點選:立即出發位置的下拉式清單,可以改變出發時間、抵達時間。

帶著我的Goolge地圖一起到日本京阪神自由行

點選:選項,則可以規劃想要的交通工具,及對自己有利的路線、轉乘次數、步行時間較短等。

帶著我的Goolge地圖一起到日本京阪神自由行

在下圖中,你可以清楚看到公車路線(同時列出多個符合的公車路線)、所需時間、花費金額、行走時間,公車每幾分鐘一班等資訊,以利行程的安排。

帶著我的Goolge地圖一起到日本京阪神自由行

點選:詳細資訊,還可以看到每一個站(Bus Stop)。

帶著我的Goolge地圖一起到日本京阪神自由行

如果你點選:時間表查閱功能,則可以調整時間、日期和路線選項,完全配合你的行程來評估。

帶著我的Goolge地圖一起到日本京阪神自由行

你也可以在此找到想要的路線方式,點選其中一個路線。

帶著我的Goolge地圖一起到日本京阪神自由行

再來看這條路線的細節:

帶著我的Goolge地圖一起到日本京阪神自由行


●將 Google 地圖上的路線新增至主畫面

當你規劃好一段行程(路線),想要留下來,可以點選:將路線傳送至手機。

帶著我的Goolge地圖一起到日本京阪神自由行

在手機中開啟該路線後,點選「選項/將路線新增至主畫面」,如此便可以在手機上建立該路線的超連結,到達日本後,只要點選這個捷徑即可還原該路線了。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

思考一下!自由行的時候,你會用相機留下看到的景:

帶著我的Goolge地圖一起到日本京阪神自由行

還是也會留下看景的景:

帶著我的Goolge地圖一起到日本京阪神自由行

帶著我的Goolge地圖一起到日本京阪神自由行

你是要安排的行程,是要去看塔景,還是要去塔上看景。

帶著我的Goolge地圖一起到日本京阪神自由行



●在 Google 地圖上先利用3D模式檢視景點

在電腦版的 Google Chrome 上利用3D模式來檢視景點,有利對於整個景點周邊和相對位置的掌握,也能有助於約略了解相關的動線。

(大阪城)

帶著我的Goolge地圖一起到日本京阪神自由行

(姬路城)

帶著我的Goolge地圖一起到日本京阪神自由行

(金閣寺)

帶著我的Goolge地圖一起到日本京阪神自由行

(二條城)

帶著我的Goolge地圖一起到日本京阪神自由行

(西本願寺)

帶著我的Goolge地圖一起到日本京阪神自由行

(阿倍野展望台)

帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上先行下載「離線地圖」

在手機的 Google 地圖上可以先行下載離線地圖,只要指定一個區域,下載後存於SD卡上,雖然佔些空間,但是查詢時不需佔頻寬,也比較節省手機電力。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

但是我要下載自由行區域的地圖時,Google 顯示這張地圖無法離線使用。沒想到日本區域還不支援離線下載地圖!下次你要去時,再來查查是否已支援。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上先將需要查詢的地點都輸入查詢一次

當你在查詢地點和規劃路線時,只要你有登入帳號,Google 會記錄這些地點名稱。這有什麼幫助呢?因為你到日本時,只要輸入第1,2個字,Google 就會自動顯示接近的清單讓你選,除了查詢速度加快之外,也可以免去輸入日文字來查詢。

例如:(要查詢金閣寺)在手機中輸入『金閣』,Google 會自動顯示『鹿苑寺』。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

(因為鹿苑寺又名金閣寺)

帶著我的Goolge地圖一起到日本京阪神自由行



《旅行中》使用手機操作 Google 地圖

在地圖中記得要標記和旅途有關的資訊站,可以購得較為便宜的ICOCA卡、週遊卡和火車票等,也可以查詢相關旅遊資訊。一到達機場,就會先跑到這些資訊站。

帶著我的Goolge地圖一起到日本京阪神自由行

帶著我的Goolge地圖一起到日本京阪神自由行

已有許多資訊和服務人員提供中文服務。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上找到自己所處的位置

在旅行途中,想要知道自己的所在位置,可以檢視 Google 地點上的『藍點』位置,就是你的位罝,按一下右下角的圖示,便可以放大地圖,顯示所在位置周邊的地圖。

帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上查詢要去的地點並且使用街景檢視

當查詢到想要的地點,你可以查詢地點說明,也可以按一下『街景檢視』,先行瀏覽實際景像。(我想,你如果有先做一些功課,應該不會要去時才預覽。)

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上對要去的地點查詢評價及熱門時段

當你親臨了一個地點,除了看到別人的評價之外,你也可以給幾顆星的評分和給予評論。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

有時 Google 也會問你是否在某個地點?熟悉該地點?並且要求回答一些問題。(若你有參與 Google 在地嚮導,回答問題可以獲得分數。)

先看看其熱門時段,也有助於你的時間安排。你還可以挑選星期幾和調整時間來觀看。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上量測到達地點的距離

當你在一個地點,想要即時評估到另一個地點的距離時,也可以使用 Google 地圖上的「測量距離」功能。

帶著我的Goolge地圖一起到日本京阪神自由行

做法:

1. 在一個點(起點)上長按,會顯示:已放置圖釘,然後在資訊中找到:測量距離。

2. 拖曳地圖讓黑色圓圈放在你想要量測的地點上,會即時顯示距離的數值。

3. 按右下角的『+』符號,可以不斷的新增地點來量測,距離會一直累加。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上標記到達的地點以加入我的時間軸

當你在地圖上點選一個鄰近的地點標示,Google 會問你是否目前在這裡嗎?你可以加以回答,如果你回答『是』,則該地點會被加入『你的時間軸』中,並標示為綠色打勾。時間不得會百分百符合。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

自由行的速度要如何拿捏?

帶著我的Goolge地圖一起到日本京阪神自由行



●在 Google 地圖上即時查詢地點並規劃路線

在出發前已經有在電腦上規劃路線,到達日本後,才真正要實地使用手機來查詢路線。這和當時規劃的地點、時間會有密切的關係。

先問一個問題,例如在京都時,最常使用巴士來移動,你如何知道附近的公車站牌?而四面八方都有站牌時,你到底要走到那一個站牌搭巴士?

你可以在手機的 Google 地圖中搜尋『Bus Stop』,再點選進入查看不同時間有沒有你要搭的路線。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

你也可以在直接查詢路線時,獲得搭公車的資訊(巴士站和時間表)。

帶著我的Goolge地圖一起到日本京阪神自由行 Screenshot_20170818-161731_resize

基本上使用 Google 地圖來規劃路線,可以參考的資訊有:

○交通工具 ○啟程時間 ○所需時間 ○所需費用 ○步行時間 ○行車時間

 帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行 

旅行中要多留意各個國家、城市裡各種交通工具的符號、色彩和編號,在 Google 地圖上會顯示相同的表示方式。

帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上開啟事先已規劃的路線

事先規劃好的路線已儲存在手機了,點選即可開啟,再依當時的時間微調交通工具。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

(如果可以的話,自己要坐下來欣賞美景,如果不行,也可以看看別人欣賞美景。)

帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上將已規劃的路線分享給同行伙伴

當你規劃好了一條路線,在選項中可以選取「分享路線」,再指定一種分享方式傳遞給伙伴,同行伙伴即可取得相同路線了。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上使用「標籤」來定位飯店或特定地點

例如:選取飯店後,在選項中選取「新增標籤」,然後輸入一個名稱,例如:住宿飯店。再點選新增標籤。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

該地點在地圖會改為顯示「住宿飯店」。

帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上使用「儲存停車位」來定位集合地點

例如,伙伴們要由一個地點四散去逛街,像是大阪的道頓堀、心齋橋等地,可以透過「儲存停車位」功能來標記集合地點。

你只要在大伙要解散所在位置的藍點上長按(顯示如下左圖畫面),再點選「儲存停車位」。地圖上會顯示P點:你的車停在這裡。這就是大家的集合地點。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上分享位置給同行伙伴

伙伴們解散了,如何知道大家跑到那裡去了呢?可以藉由「分享你的位置」功能,來讓別人知道自己的位置。當多人互相分享位置,就可以知道大伙都跑到那裡去了。

在 Google 地圖所在位置的藍點上長按(顯示如下左圖畫面),再點選「分享你的位置」,再設定分享期限(可使用+/-號來微調時間長度)或直接選取「直到手動關閉這項設定為止」。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

伙伴的手機上即會在地圖上依你的所在位置顯示頭貼(下圖藍點是伙伴的位置),伙伴按一下頭貼,即可查詢你在那裡的詳細資訊。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上「啟動行車模式」來導航路線

雖然你可能是用走的,但是如果你覺得路線不知如何走才好,也可以開啟「啟動行車模式」來讓 Google 導航。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

或是你想要模擬路線怎麼走,也可以在路線上點選「預覽」,再利用向左圖示和向右圖示,查看細部路線說明。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

在自由行來匆匆、去匆匆之間,你有對某些場景的瞬間,留下美好的回憶?

帶著我的Goolge地圖一起到日本京阪神自由行



●在 Google 地圖上檢視訊息

使用 Google 地圖時,可以觀察地圖上顯示的資訊,例如:在球場上顯示職棒對戰狀況;例如:查詢百貨公司大樓各樓層的室內地圖,或是查詢電梯在那裡。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


《旅行後》

●在 Google 地圖上調整我的時間軸上的地點和時間

你可以在電腦或是手機上調整 Google 為你的行程而自動標記在時間軸上的地點。點選「你的時間軸」:

帶著我的Goolge地圖一起到日本京阪神自由行

點選某個時間點的停留地點,進入後調整停留地點和停留時間。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上將 Google 相簿中的相片加入地點

自由行一定會留下美美的照片,如果能提供他人參考,也是美事一件。(下圖為姬路城)

帶著我的Goolge地圖一起到日本京阪神自由行

你也可將手機上和電腦的照片或是 Google 相簿在雲端中的照片加入某些景點中,供遊客參考。點選「新增照片」後,選取想要加入景點的照片。(在照片上長按,可以放大照片,以利檢視。)

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行


●在 Google 地圖上檢視「我的貢獻」

當你在各個地點上加入照片和予以評分、評論,則可以在「你的貢獻」中檢視內容。

帶著我的Goolge地圖一起到日本京阪神自由行

在評論區中可以修改自己的評分和評論,相片區可以看到每張相片被多少人檢視過。

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

當你結束自由行時,你曾感受到一個都市的用心之處嗎?

(下圖在京都拍攝,不得已要擺路障,也都有處理過。)

帶著我的Goolge地圖一起到日本京阪神自由行

帶著我的Goolge地圖一起到日本京阪神自由行

(下圖在大阪拍攝,既然不能免除電線桿,就美化它。電線桿上有燈泡,也能做廣告)

帶著我的Goolge地圖一起到日本京阪神自由行 帶著我的Goolge地圖一起到日本京阪神自由行

這是用來除草的機器人:

帶著我的Goolge地圖一起到日本京阪神自由行

在一趟花時間、花錢、花體力的旅途中,手機和 Google 地圖,到底幫上多少忙,應該也是見人見智!也不會有標準答案但是善用手邊的資訊工具,多多少少都能在旅途中發揮一些成效的。

(延伸閱讀:日本自由行從規劃到旅行結束之數位科技的應用


文章標籤

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

網友問到 Excel 的問題:如何依照以下的工時計算標準來計算加班時數?

●星期一到星期五基本工時8小時,超過為加班時數。

●星期六、日列為加班:

*加班時數4小時以內者,以4小時計;

*加班時數逾4小時至8小時以內者,以8小時計;

*加班時數逾8小時至12小時以內者,以12小時計。

Excel-分別計算平時和假日加班時數(WEEKDAY,VLOOKUP)


【公式設計與解析】

儲存格D2:

=IF(WEEKDAY(A2,2)<6,(C2-8)*(C2>8),VLOOKUP(C2,{0.1;4.1;8.1},1)+3.9)

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

(1) WEEKDAY(A2,2)<6

判斷儲存格A2是否是星期一~星期五。

Excel-分別計算平時和假日加班時數(WEEKDAY,VLOOKUP)

(2) (C2-8)*(C2>8)

(星期一~星期五)當儲存格C2數值大於8者,才計算其加班時數。

(3) VLOOKUP(C2,{0.1;4.1;8.1},1)+3.9)

(星期六~星期日)根據以下標準換算加班時數。

*加班時數4小時以內者,以4小時計;

*加班時數逾4小時至8小時以內者,以8小時計;

*加班時數逾8小時至12小時以內者,以12小時計。

文章標籤

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

如下圖,在 Excel 中如何依日期欄位來按月編號?

Excel-按月編號(COUNTIF)

 

【公式設計與解析】

觀察上圖,月份是由小至大依序排列。

儲存格B2:=MONTH(A2)

儲存格C2:="A-"&B2&"-"&COUNTIF($B$2:B2,B2)

複製儲存格B2:C2,貼至儲存各B2:C23。

在此使用輔助欄位B欄,先取出每個日期的月份。

再利用 COUNTIF 計算相同月份由第一個儲存格起算的個數。

文章標籤

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

在 Excel 中,如何將同一欄的資料中找出最大值/最小值所在的儲存格位址,或是將同一列的資料中找出最大值/最小值儲存格位址?

《資料在一欄》

Excel-查詢最大值的位址(ADDRESS,MATCH,INDIRECT)

【公式設計與解析】

選取B1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。

1. 最大值位置

儲存格F2:=ADDRESS(MATCH(MAX(數值),數值,0)+1,1)

(1) MAX(數值)

利用 MAX 函數找出數值陣列的最大值。

(2) MATCH(MAX(數值),數值,0)

利用 MATCH 函數找出最大值的位置(傳回一個數值)。

(3) ADDRESS(MATCH(MAX(數值),數值,0)+1,1)

利用 ADDRESS 函數根據第(2)式傳回的位置找出其儲存格位址。

2. 最大值內容

儲存格E2:=INDIRECT(F2)

利用 INDIRECT 函數將儲存格F2的內容轉換為儲存格位置並取得內容。

3. 最小值位置

儲存格F5:=ADDRESS(MATCH(MIN(數值),數值,0)+1,1)

4. 最小值內容

儲存格E2:=INDIRECT(F5)


《資料在一列》

Excel-查詢最大值的位址(ADDRESS,MATCH,INDIRECT)

【公式設計與解析】

選取B2:N2,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:數值。

儲存格C2:=ADDRESS(1,MATCH(MAX(數值),數值,0)+1)

儲存格B2:=INDIRECT(C5)

儲存格G2:=ADDRESS(1,MATCH(MIN(數值),數值,0)+1)

儲存格F2:=INDIRECT(G5)

原理同《資料在一欄》,注意 ADDRESS 函數中的參數差異

文章標籤

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

網友問到:在下圖中,如何在 Excel 中將左表轉換右表?

下圖中,同一日期中,A組、B組、C組的人不會重覆。

Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)

【公式設計與解析】

選取日期中有資料儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

選取資料的所有儲存格(本例:儲存格C2:E32),定義名稱:資料。

儲存格H3:=IF(SUMPRODUCT((日期=H$1)*(資料=$G3)),"V","")

因為同一日期中,A組、B組、C組的人不會重覆,所以在 SUMPRODUCT 函數中利用雙條件:(日期=H$1)*(資料=$G3),結果只會傳回 1/0。在 IF 函數中將 1/0 對應顯示V/空白

(完整結果如下圖)

Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)


以下提供第二種不同公式寫法,當作練習公式運用:

Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)

選取日期中有資料儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格F3:

=IFERROR(IF(MATCH($G3,OFFSET($A$1,MATCH(H$1,日期,0),2,1,3),0),"V",),"")

複製儲存格F3,貼至儲存格F3:AL14。

(1) MATCH(H$1,日期,0)

MATCH 函數中判斷儲存格H1位於日期陣列的位置,傳回一個數值。

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

將第(1)式的傳回值代入 OFFSET 函數,本例傳回儲存格C2:E2。

(3) MATCH($G3,第(2)式,0)

MATCH 函數中判斷儲存格G3位於第(2)式取得的儲存格範圍中的位置,傳回一個數值。(傳回值:1、2、3和錯誤訊息)

(4) IF(第(3)式,"V",)

判斷第(3)式的傳回值,若是 1,2,3,則顯示「V」。

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

利用 IFERROR 函數將錯誤訊息顯示為空白。

文章標籤

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

網友提問:在 Excel 中,如何透過欄(列)標題和內容來反求列(欄)標題?

【經由列標題和內容求得欄標題】

本例:透過列標題『丁』和內容『R』,要求得欄標題『寅』。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

1. 定義儲存格名稱

選取儲存格A2:F6,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:甲、乙、丙、丁、戊。

2. 選取儲存格I2,設定「資料驗證」。設定準則:

儲存格內允許:清單。來源:=INDIRECT(H2)。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

3. 設定公式。

儲存格H3:=OFFSET(A1,0,SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))-1)

(1) SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))

利用 SUMPRODUCT 函數求得儲存格I2的欄號。

(2) OFFSET(A1,0,第(1)式-1)

OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。


【經由欄標題和內容求得列標題】

本例:透過欄標題『卯』和內容『S』,要求得列標題『丁』。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

1. 定義儲存格名稱

選取儲存格B1:F6,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:子、丑、寅、卯、辰。

2. 選取儲存格I2,設定「資料驗證」。設定準則:

儲存格內允許:清單。來源:=INDIRECT(H2)。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

3. 設定公式。

儲存格H3:=OFFSET(A1,SUMPRODUCT((B2:F6=I2)*ROW(A2:A6))-1,0)

(1) SUMPRODUCT((B2:F6=I2)*ROW(A2:A6)).

利用 SUMPRODUCT 函數求得儲存格I2的欄號。

(2) OFFSET(A1,第(1)式-1,0)

OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。

文章標籤

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

新同學進入新的校園,註冊組長又面臨「分班」的問題了!如果你已經由某一個規則將學生予以排序完成,如何能以 S 型分班的概念來分班呢?Excel 如何幫助你?以下分別以『手動』和『自動』二種方式來說明。

參考下圖,左側是有數百名學生已排序後清單,右側是自動以 S 型來分班的結果。(本例沒有考慮男女生等因素)

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)


【手動處理】

本例假設要分7班。

(1) 在分班欄位依序輸入「1,2,3,4,5,6,7,6,5,4,3,2,1」。

(2) 複製上述的儲存格範圍,往下各列貼上。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(3) 進入自動篩選,篩選「1」項。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN) Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(4) 複製篩選「1」的儲存格,貼至101班。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(5) 重覆步驟(3)和(4),分別篩選「1,2,3,4,5,6,7」對應貼至「101,102,103,104,105,106,107」。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)


【公式自動處理】

如果你想試試以公式來自動分班,則可以輸入以下公式。

1. 產生分班欄位

儲存格C2:=IF(MOD(INT((ROW(1:1)-1)/$D$1),2),$D$1-MOD(ROW(1:1)-1,
$D$1),MOD(ROW(1:1)-1,$D$1)+1)

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

(1) MOD(INT((ROW(1:1)-1)/$D$1),2)

(2) $D$1-MOD(ROW(1:1)-1,$D$1)

(3) MOD(ROW(1:1)-1,$D$1)+1

第(1)式、第(2)式、第(3)式產生的結果如下圖:

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(4) IF(第(1)式,第(2),第(3))

當第(1)式傳回『1』時,相當於邏輯 TRUE,會顯示第(2)式的結果。

當第(1)式傳回『0』時,相當於邏輯 FALSE,會顯示第(3)式的結果。


2. 產生分班結果

先選取C欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:分班。

儲存格F2:{=OFFSET($B$1,SMALL(IF(分班=COLUMN(A:A),ROW(分班),),
ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A)))-1,0)}

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

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

(1) IF(分班=COLUMN(A:A),ROW(分班),)

在陣列公式中,判斷分班的儲存格陣列中和 COLUMN(A:A) 相同者,傳回其列號。COLUMN(A:A)=1,向右複製後,得到:COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(2) SMALL(第(1)式,ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A))

利用 SMALL 函數,由小到大依序取出不為 0 的列號。

COUNTIF(分班,"<>"&COLUMN(A:A):計算在分班陣列中,不為「1」的個數。

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

最後,透過 OFFSET 函數,代入第(2)式的傳回值,得到對應的儲存格內容。

3.使用公式的好處是:不管要分幾班都可以適用。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

文章標籤

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

在 Excel 中有一個數值清單(如下圖B欄),如何計算累計至指定第幾個?

在下圖中,C欄為計算每一個累計的結果,如何在沒有C欄輔助時計算指定個數的累計?

Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)

【公式設計與解析】

方法一:儲存格C2:=SUM($B$2:B2)

方法二:儲存格C2:=SUM(OFFSET($B$2,0,0,ROW(1:1),1))

方法三:儲存格C2:=SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:1),1))

複製儲存格C2,貼至儲存格C21。

以上三種方式都可以得到每個數值的累計結果。

現在,根據儲存格E2的指定個數,要計算累計結果。

儲存格E4:=INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)

(1) OFFSET($B$2,0,0,ROW(1:20),1))

利用 OFFSET 函數取得B欄中要計算累計的儲存格區間。

(2) SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1))

透過 SUBTOTAL 函數指定參數 9,用以指定執行 SUM 功能。

Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)

使用 SUBTOTAL 函數的用意,是可以利用 ROW(1:20) 產生 1~20  的陣列。

image

在執行 SUM 運算時得到累計的結果。

image

(3) INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)

最後,再利用 INDEX  函數取出指定(儲存格E2)的累計結果。

文章標籤

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

網友想要在 Excel 中找出每一年各個月的第一個星期六是那一天,該如何處理?

參考以下的二個範例,基本上要使用 WEEKDAY 函數。


1. 每個月第1個星期六的日期

Excel-找出一年中每個月的第一個星期六(WEEKDAY,DATE)

【公式設計與解析】

儲存格B3=8-WEEKDAY(DATE($A3,COLUMN(A:A),1),17)

複製儲存格B3,貼至儲存格B3:M3。

先來看看 WEEKDAY 函數的傳回值:

儲存格B4=WEEKDAY(DATE($A3,COLUMN(A:A),1),17)

複製儲存格B4,貼至儲存格B4:M4。

在 WEEKDAY 函數中使用參數『17』,表示傳回的數字1~7對應星期日~星期六。

將 8 減掉 WEEKDAY 的傳回值,即為所求。


2. 求每個月第1個星期三的日期

Excel-找出一年中每個月的第一個星期六(WEEKDAY,DATE)

【公式設計與解析】

儲存格B3=8-WEEKDAY(DATE($A3,COLUMN(A:A),1),14)

複製儲存格B3,貼至儲存格B3:M3。

儲存格B4=WEEKDAY(DATE($A3,COLUMN(A:A),1),14)

複製儲存格B4,貼至儲存格B4:M4。

如果要找每個月的第一個星期三,則只要將 WEEKDAY 的參數指定為『14』,即表示傳回的數字1~7對應星期四~星期三。將 8 減掉 WEEKDAY 的傳回值,即為所求。


根據以上兩個例子,不難發現,要求每個月第一個星期幾和參數的對應關係:

星期日:11

星期一:12

星期二:13

星期三:14

星期四:15

星期五:16

星期六:17

文章標籤

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

網友問到:在 Excel 中的一個資料表,想要求得每一欄中,不為 0 的第 1, 2 個並且予以加總。該如何處理?

參考下圖,每一欄都有數個連續內容為 0 的儲存格,如何求得不為 0 的第 1, 2 個並且予以加總?

Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)


【公式設計與解析】

儲存格A19:

=SUM(OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1))

複製儲存格A19,貼至儲存格A19:G19。

(1) (A1:A18=0)*ROW(A1:A18)

SUMPRODUCT 函數中傳回符合條件 A1:A18=0 者的列號。ROW 函數可以傳回儲存格列號。

(2) MAX((A1:A18=0)*ROW(A1:A18))

利用 MAX 函數取得不為 0 者儲存格列號中的最大值。

(3) SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18)))

利用 SUMPRODUCT 函數可以使用陣列運算。

(4) OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1)

透過 OFFSET 函數,以儲存格A1為起點,位移至不為 0 的第 1 個儲存格,再取高度為 2、寬度為 1 的儲存格範圍。本例傳回儲存格A15:A16。

(5) 最後再透過 SUM 函數予以加總,即為所求。

Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

文章標籤

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

老師們在暑假中可沒有閒著呢!

許多老師積極的參與研習、備課,為新學期做足準備。以往讓學生以 Email 方式繳交作業,又受限於 Email 的檔案附件大小問題。現在你使用 Google 表單就可以做為學生繳交作業檔案的平台了。

最近 Google 表單已開放任何使用者,都可以在表單上使用「檔案上傳」的功能了!

當你選取了新增:檔案上傳,可以指定允許上傳的檔案類型、檔案數量上限和檔案大小上限。

使用Google表單的檔案上傳功能讓學生繳交作業檔案

學生上傳檔案前必須先登入 Google 帳戶才能使用:

使用Google表單的檔案上傳功能讓學生繳交作業檔案

學生點選「新增檔案」:

使用Google表單的檔案上傳功能讓學生繳交作業檔案

學生可以由自己的雲端硬碟挑選檔案,也可以點選「從您的電腦中選取檔案」來上傳作業的檔案。一次可以上傳的檔案數量和檔案格式,依你在表單的設定為準。

使用Google表單的檔案上傳功能讓學生繳交作業檔案

按下「提交」,才完成作業繳交。

使用Google表單的檔案上傳功能讓學生繳交作業檔案

你可以看到學生繳交作業的檔案名稱(檔案名稱+學生姓名):

使用Google表單的檔案上傳功能讓學生繳交作業檔案

如果開啟表單回覆記錄的試算表,作業檔案的網址也被集合在一個儲存格中。(你不用特別去處理這些檔案的網址)

使用Google表單的檔案上傳功能讓學生繳交作業檔案

到雲端硬碟中,你會發現 Goolge 已自動為你將學生上傳的作業檔案放在一個資料夾中,而且檔案標題已都依照檔名+姓名來命名了。

使用Google表單的檔案上傳功能讓學生繳交作業檔案

注意:這個方式很方便的搜集了學生的作業,但也同樣會吃掉你的雲碟容量。如果你使用 G Suite,就沒有檔案容量的限制。

文章標籤

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

在 Excel 中使用一般篩選功能,是常見的篩選工具,大多數都可以滿足想要的篩選結果。但是如果要一般篩選以外的篩選功能,還是得靠「進階篩選」了。

【例1】如何篩選生日月份是9月~12月的人?

在以下圖中,有一個生日的欄位。

Excel-在進階篩選中使用公式運算

如果你使用一般篩選,Excel 會自動辨識這是日期欄位,並且提供篩選的選項中即有「年、月、日」的選項。所以,你只要勾選「九月、十月、十一月、十二月」即可。

Excel-在進階篩選中使用公式運算

結果如下:

Excel-在進階篩選中使用公式運算

Excel 還有提供其他日期篩選的功能可以使用:

Excel-在進階篩選中使用公式運算

如何使用進階篩選來篩選生日月份是9月~12月的人?(參考下圖)

Excel-在進階篩選中使用公式運算

做法如下:

1. 在儲存格G2中輸入一個欄位標題(自訂,不要使用和原欄位相同的名稱)

2. 在儲存格G3中輸入公式:=MONTH(C2)>=9

該公式是想要利用 MONTH 函數找出生日的月份,利用「>=9 」條件找出9月~12月者。儲存格C2是生日欄位中的一個儲存格,其結果為 TRUE,是因為生日 2001/10/20 符合9月~12月者。

Excel-在進階篩選中使用公式運算

結果如下:

Excel-在進階篩選中使用公式運算


【例2】找出9月~12月的女生

如果使用一般篩選,先篩選生日為9月~12月者,再篩選性別為女者。這兩次篩選動作,相當於兩個條件執行邏輯 AND 運算。

Excel-在進階篩選中使用公式運算

如果使用進階篩選(做法如下圖),當兩個條件寫在同一列中,表示兩個條件執行邏輯 AND 運算。

Excel-在進階篩選中使用公式運算


【例3】找出9月~12月的女生和1月~4月的男生

參考下圖,儲存格G2:H3為條件設定,當條件寫在不同列中,表示兩個條件執行邏輯 OR 運算。而同當同時要執行 AND 運算和 OR 運算時,會先執行 AND 運算,再執行 OR 運算。

先輸入以下公式:

儲存格G3:=MONTH(C2)>=9

儲存格G4:=MONTH(C2)<=4

相當於執行條件:(儲存格G2 AND 儲存格H2) OR (儲存格G3 AND 儲存格 H3)

Excel-在進階篩選中使用公式運算


【例4】找出國文及格的女生和英文及格的男生

你可以如下圖的做法:

Excel-在進階篩選中使用公式運算

也可以這樣做,將所有的條件全寫在同一個儲存格:

1. 在儲存格G2中輸入一個欄位標題(自訂,不要使用和原欄位相同的名稱)

2. 輸入公式:=(B2="女")*(D2>=60)+(B2="男")*(E2>=60)

公式中的『*』運算子相當於執行邏輯 AND 運算;『+』運算子相當於執行邏輯 OR 運算。

Excel-在進階篩選中使用公式運算

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

在 Excel 中使用陣列公式時,常會因為儲存格陣列的數量龐大,而造成系統效能下降,所以不得已要放棄陣列公式的使用,所以得發展不需使用陣列公式的方式。

繼前二篇文章:

Excel-列出篩選和不篩選項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

本篇要分別以陣列公和非陣列公式來處理將項目清單中的空白予以忽略,重新列出有資料的項目。

Excel-列出非空白項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

【公式設計與解析】

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

1. 陣列公式

儲存格C2:

{=IFERROR(OFFSET($A$1,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,0),"")}

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

複製儲存格C2,貼至儲存格C2:D18。

(1) IF(項目<>"",ROW(項目),"")

判斷項目陣列中具有空白的儲存格,傳回列號的陣列。ROW 函數可以儲存格的列號。

(2) SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))

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

(3) OFFSET($A$1,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,0)

將第(2)式傳回的列號代入 OFFSET 函數,傳回對應的儲存格內容。

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

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。


2. 非陣列公式

儲存格C2:

=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((項目<>"")*ROW(項目),
ROW(1:1)+COUNTIF(項目,"")))-1,0),"")

複製儲存格C2,貼至儲存格C2:D18。

(1) (項目<>"")*ROW(項目)

判斷項目陣列中具有空白的儲存格,傳回列號的陣列。ROW 函數可以儲存格的列號。

(2) SMALL((項目<>"")*ROW(項目),ROW(1:1)+COUNTIF(項目,"")

COUNTIF(項目,""):計算在項目欄位中共有幾個空白儲存格。

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

(3) SUMPRODUCT(SMALL((項目<>"")*ROW(項目),ROW(1:1)+COUNTIF(項目,""))

SUMPRODUCT 函數中,第(1)式和第(2)式可以執行相當於陣列公式的功能。

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

最後,將第(3)式的列號代入 OFFSET 函數,傳回對應的儲存格內容。

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

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

文章標籤

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

網友常問的問題:在 Excel 的資料清單中,如何挑選重複/不重覆的項目?本篇要以陣列公式和非陣列公式二種方式來處理。(在 Excel 中使用陣列公式時,常會因為儲存格陣列的數量龐大,而造成系統效能下降,所以不得已要放棄陣列公式的使用,所以得發展不需使用陣列公式的方式。)


【列出不重覆的項目】

在下圖中,A欄是資料清單,在B欄中有一輔助欄位:重覆數,如何以公式來篩選不重複的項目?

Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、重覆數。

在輔助欄位輸入公式,儲存格B2:=COUNTIF(項目,A2),複製儲存格B2,貼至儲存格B2:B23。


1. 陣列公式

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(COUNTIF(項目,項目)=1,
ROW(項目),""),ROW(1:1))-1,0),"")}

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

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

(1) IF(COUNTIF(項目,項目)=1,ROW(項目),"")

在陣列公式中找出項目陣列重覆個數為 1 者(表示未重覆項目)的列號。

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

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

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

將第(2)式傳回的列號代入 OFFSET 函數,傳回對應的儲存格內容。

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

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。


2. 非陣列公式

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((重覆數=1)*
ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1")))-1,0),"")

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

(1) (重覆數=1)*ROW(項目)

SUMPRODUCT 函數中找出重覆數為 1 的列號。

(2) SMALL((重覆數=1)*ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1"))

COUNTIF(重覆數,">1"):計算在重覆數欄位中共有幾個大於 1 的儲存格。

本式可依列號由小至大依序傳回第1, 2, 3, ... 個具有「V」的儲存格列號。

(3) SUMPRODUCT(第(2)式)

SUMPRODUCT 函數中,第(1)式和第(2)式可以執行相當於陣列公式的功能。

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

最後,將第(3)式的列號代入 OFFSET 函數,傳回對應的儲存格內容。

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。


【列出重覆的項目】

在下圖中,A欄是資料清單,在B欄中有一輔助欄位:重覆數,如何以公式來篩選重複的項目?

Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、重覆數。

在輔助欄位輸入公式,儲存格B2=COUNTIF($A$2:A2,A2),複製儲存格B2,貼至儲存格B2:B23。


1. 陣列公式

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(重覆數=2,ROW(項目),""),
ROW(1:1))-1,0),"")}

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

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

參考列出非重覆的項目的1.陣列公式,其差異為將「COUNTIF(項目,項目)=1」,更新為「重覆數=2」。


2. 非陣列公式

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((重覆數=1)*
ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1")))-1,0),"")

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

參考列出非重覆的項目的2.非陣列公式。

文章標籤

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

網友常問的問題:在 Excel 的資料清單中,如何挑選勾選/不勾選的項目?本篇要以陣列公式和非陣列公式二種方式來處理。(在 Excel 中使用陣列公式時,常會因為儲存格陣列的數量龐大,而造成系統效能下降,所以不得已要放棄陣列公式的使用,所以得發展不需使用陣列公式的方式。)


【列出篩選已勾選的項目】

在下圖中,A欄是資料清單,在B欄中有以『V』標示為勾選的項目,如何以公式來篩選這些『V』的項目?

Excel-列出篩選和不篩選項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:清單、勾選。

1. 陣列公式

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(勾選="V",ROW(清單),""),
ROW(1:1))-1,0),"")}

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

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

(1) IF(勾選="V",ROW(清單),"")

在勾選陣列中判斷具有「V」的儲存格,傳回列號的陣列。ROW 函數可以儲存格的列號。

(2) SMALL(IF(勾選="V",ROW(清單),""),ROW(1:1))

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

(3) OFFSET($A$1,SMALL(IF(勾選="V",ROW(清單),""),ROW(1:1))-1,0)

將第(2)式傳回的列號代入 OFFSET 函數,傳回對應的儲存格內容。

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。


2. 非陣列公式

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((勾選<>"")*
ROW(勾選),ROW(1:1)+COUNTIF(勾選,"")))-1,0),"")

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

(1) (勾選<>"")*ROW(勾選)

SUMPRODUCT 函數中找出不具有「V」的列號。

(2) SMALL((勾選<>"")*ROW(勾選),ROW(1:1)+COUNTIF(勾選,""))

COUNTIF(勾選,""):計算在勾選欄位中共有幾個空白(非「V」儲存格)。

本式可依列號由小至大依序傳回第1, 2, 3, ... 個具有「V」的儲存格列號。

(3) SUMPRODUCT(SMALL((勾選<>"")*ROW(勾選),ROW(1:1)+COUNTIF(勾選,"")))

SUMPRODUCT 函數中,第(1)式和第(2)式可以執行相當於陣列公式的功能。

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

最後,將第(3)式的列號代入 OFFSET 函數,傳回對應的儲存格內容。

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。


【列出篩選未勾選的項目】

在下圖中,A欄是資料清單,在B欄中有以『V』標示為勾選的項目,如何以公式來篩選這些『V』以外的項目?

Excel-列出篩選和不篩選項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:清單、勾選。

1. 陣列公式

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(勾選<>"V",ROW(清單),""),
ROW(1:1))-1,0),"")}

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

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

參考列出篩選已勾選的項目的1.陣列公式,其差異為將「勾選="V"」,更新為「勾選<>"V"」。


2. 非陣列公式

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((勾選="")*
ROW(勾選),ROW(1:1)+COUNTIF(勾選,"V")))-1,0),"")

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

參考列出篩選已勾選的項目的2.非陣列公式,其差異為將「COUNTIF(勾選,"")」更新為「COUNTIF(勾選,"V")」。

文章標籤

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼