顯示具有 提問單 標籤的文章。顯示所有文章

2016-11-18

〔EXCEL〕INDIRECT，不可不知對欄位的"兇器"…更正…"利器"啦~

這又是一篇從舊部落格搬過來的…
當然原因有三…
一、這個函數其實非常有用，但多數人並不清楚。沒有它，一樣可以用最原始方式使用，但有了它真的是事半功不知幾倍。
二、前二週給一個學生上課，其實從頭到尾就在講這個函數←可見真的能應用到
三、剛剛有人在我舊部落格留言→可見真的有人在查詢

哈哈!廢話不說了…再讓我上堂偷懶的舊課吧!

今兒個某位學員在製作記帳總表…因為要把每月結餘金額連結帶到總表…
必需一個個寫入「='1月'!BN6」、「='2月'!BN6」…一路寫到12月…再改第二列

依他的總表，至少需對應400個以上的欄位…這的確是件辛苦的事情~

老師佛心來的~當然，交情不同咩~一句"傳來"~
只要利用INDIRECT這個函數…可以簡單的讓欄位名稱(例：'1月'!BN6)，成為一個字串帶入…
大家知道，"字串"這個東東，代表你可以隨心所欲的用規則去產生…

所以你可以產生欄位位置的字串…再例用這個函數去取得該欄位的"值"

作法及原理：

INDIRECT(欄位位置字串 , (真)位置判為A1的格式 / (假)位置判為R1C1的格式)

A1的格式：就是我們常見欄位位置的表示方式，例如A1、B1
R1C1的格式：以「列數+欄數」為表示方式，例如R2C3代表第二列第3欄…就是指C2囉~

《1》總表 C2裡函數要對應的位置是「'1月'!BN6」…月份我們可以例用C1的值來產生…其它的部份就直接加上雙引號，讓它變成字串即可
="'"&C$1&"月'!BN6"

《2》將欄位位置產生字串的計算式帶入函數裡…就完成了「一月份當月總計[薪資]」的對應囉
=INDIRECT("'"&C$1&"月'!BN6",TRUE)

直接將一月份薪資總計的計算式(C2)…複製到其它月份(D2:N2)…就完成囉!
其它列的對應，比照此模式~

就可以有效的把400次，減為30次囉~~

〔EXCEL〕如何讓儲存格依據條件"自動"變色(格式化條件&網路提問)

Scenic 11:09 No comments

以下是我在舊的部落格開的課…沒把舊課程搬到新的部落格…
為何會把這篇搬過來…我發現格式化條件在應用上，的確是多數使用者的需求，而且也有明顯的幫助…
提問也較多…
對於過去未回覆的提問，說聲抱歉(因為我沒去維護該部落格很久很久很久了，哈哈)
拉來這裡回覆好了…能看到是緣份…>"<

--------------------------------------------------------------------------------------------------------------------------------

相信很多人在建立或維護Excel資料時…常常為了凸顯某些特定的資料而使用"顏色"來做分類標示。

這是一個很好的方式，也讓你的資料更容易辨識，也顯得更專業些!
(不過，千萬別弄得花花綠綠滴，反而失去了焦點)

不過如果你要凸顯的資料是有特定條件的，例如金額大於10,000元，用綠色來標示；小於10,000元用紅色。你會怎麼做?
一筆一筆看…然後逐筆更改文字格式或儲存格底色嗎?
那下次金額更改時，又要手動調整一次?

今天我們要上的課，就是教你如何讓Excel自動幫你變更顏色~
一起來讓Excel幫你做事吧!!別讓它閒著了~

〔格式化條件〕

範例：業務員業績若大於等於業績目標，業績欄位更改為綠色，若未達目標，欄位改為紅色。

分析：需要B3~B7的儲存可與B1的目標來做比較，>=B1則更改底色為綠色。<B1則更改為紅色。

作法：
《1》我們先在第一筆資料(B3)，設定〔格式化條件〕

點選〔設定格式化條件〕→〔新增規則〕，會開啟以下視窗：

規則類型有非常多的選項，老師最常用的就是最後一項<1>〔使用公式來決定要格式化哪些儲存格〕，因為這一項大概就可以解決95%的問題，而剩下的5%問題…我想，你我要遇到的機率都非常的低~~(基於腦容量永遠都不夠的限制條件，只要學最常需要的即可)

《2》選取後，就會跳出格式化的公式輸入區，點選<2>

在格式化規則裡，輸入 =$B3>=$B$1
這裡要輸入的就是你要改變顏色的條件，複習一下題目：業務員業績($B3)若大於等於(>=)業績目標($B$1)，業績欄位更改為綠色

◎各位有沒注意到，儲存格欄列前老師有放上$這個絕對位置符號。這個很重要，有概念的學員就先試吧!因為這個要說明…實在是又得開一堂概念課了!!這裡就先暫時跳過去吧!
但業績目標，因為每個業務員要比對的目標，都是B1這個欄位，所以請務必打成$B$1喔~

◎另外有沒有人很厲害，注意到條件中有個顏色特別不一樣的…是的，就是那個=，這個是一般人在設定絛件時，很容易常漏掉的。記得"條件前"還要加"="喔!

《3》條件設好就來設定格式囉~

大夥兒看到這裡可以設定的，都可使用喔!包括字型的顏色、大小等，儲存格框線(老師常常用這個請Excel幫我畫框線，因為我太懶了)，儲存格底色。

●●到這裡…我們就已將第一個格式化條件設好了!!給自己鼓勵一下~~
第二個條件：業務員業績($B3)若小於(<)業績目標($B$1)，業績欄位更改為紅色
就當練習實作的功課吧!重覆《1》~《3》的動作，再設定一次!

如果你完成了…千萬別以為結束了喔!!因為目前只設定了B3這一個儲存格的格式化條件…別忘了我們的業績欄位是B3~B7

《4》〔設定格式化的條件〕→〔管理規則〕：我們來針對剛剛設定的條件，把適用的範圍加上去。

在〔套用到〕的設定裡，預設都=$B$3。請直接將它改為=$B$3:$B$7

◎套用到這個功能，是在MSOffice Excel 2007版以後才有的喔!如果是之前的版本，最多只能設3個格式化設定。而且適用的範圍只能用儲存格格式複製過去(這時就考驗你寫條件的絕對相對位置的功力…嘿嘿)

做完這一步…你會發現…耶~~格式自動改變了喔!

放鞭炮~~~這麼長的課程…都快睡著了!!下課~~~

提問A. 您好，想知道有可以使用在字串資料上的條件格式嗎？謝謝。
回答A. 若用公式判斷，只要能寫得出判斷式，不管是字串、數字甚至if等函數判斷式，都能拿來加以應用。

提問B. 老師,請問您~ 我若要設定數字等於0時變成空白可以嗎??
回答B. 其實若想讓結果等於0時輸出空白，用if判斷式就可達成。但若想用格式化條件…或許你可以考慮若數字等於0時，讓字體顏色變白色(跟底色一樣)，這樣其實視覺上也是一樣的(有時結果若也要拿來給其它欄位做使用時，想保留原本的值，這就是方式之一囉)

提問C. 老師,請問,如果要寫下一欄>上一欄變紅,下一欄小於上一欄變綠,用這個方向,好像不太對,請問要怎樣寫呢?謝謝老師
回答C. 其實用格式化條件是對的囉…只是你必需拆成2個條件…格式化條件是可以有多筆的。
第一個條件…=A2>A1，變紅
第二個條件…=A2<A1，變綠
而條件的順序是有影響的喔!Excel會由上而下判斷條件，每個條件最後有一個選項(如果True則停止，預設是沒勾選)，在沒勾選的情況下，第一個條件成立，第二個條件也成立的話，第二個條件所設定的格式會蓋過第一個條件喔!
當然需不需要勾選，完全看您的需求及條件怎麼設來決定…就像您目前的條件並沒有處理當A2=A1時，格式是否要變動囉。

提問D. 您好,請問可以設定欄位數字的未五碼顏色嗎?
回答D. 這個單用函數及格式化條件，似乎不太可能達到。至於用VBA…我也還真沒試過…

提問E. EXCEL字體顏色原本可以顯不同顏色，不知動到什麼，就都只有黑色，儲存格填色也是，請問是哪裡的設定被動到了？謝謝！

回答E. 嗯!這個問題沒看到原始檔案，很難判定是哪裡出問題呢!

提問F. 您好！如果我想將指定的儲存格條件設定為，連3或以上的倍數，有關儲存格的字體轉變為紅色，不知可以怎樣做到？

回答F. 您可判斷該儲存格的值是否為3的倍數即可。怎麼判是是否為3的倍數，可以利用除以3的餘數是否為0來判斷。

=MOD(參照儲存格,3)=0

MOD範例：mod(10,3)=1，mod(9,3)=0

提問G. 老師,請問如果我要設定A1,B1不一樣時，表格會自動填滿顏色

回答G. 您可在要變色的表格欄位內，加上=$A$1<>$B$1，套用到該表格的所有欄位範圍即可喔!

提問單〔Access〕：將日期加上指定的年區間的應用(iif / isnull / DateSerial / DateAdd )

Scenic 10:05 No comments

如果您有一份資料表中，有2個欄位如上
想要在查詢表或表單中，增加一個欄位，內容為[基準日期]+[年份]，要如何處理?

在撰寫運算式時，要注意其中[基準日期]有可能是空值，運算式在若遇到沒有日期的情況，就不進行加總年份。

所以，我們依以下兩個階段將運算式完成
A. 判斷是否[基準日期]為空值
B. 非空值時，我們要將[基準日期]加上[年份]得到我們想要的日期

首先，我們如何判斷日期為空值呢?
通常我們判斷字串為空值都使用空字串""，但日期的格式則為null，所以可以使用以下函數來確認

isnull([基準日期])

若為空值則回傳true，有值則回傳false

再來，如何將日期做加減呢?日期函數很多種，都可以達成加減的目的，可以例用以下2個函數來做

DateAdd(增加類別, 增加量, 日期)

例如：DateAdd("yyyy",[增加年份],[基準日期])
DateAdd("yyyy", 2 , 2016-09-05) → 2018-09-05

Value	Explanation
yyyy	Year
q	Quarter
m	Month
y	Day of the year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

或是您可以自已計算年、月、日，再組合成日期格式

DateSerial(年, 月, 日)

例如：DateSerial(Year([基準日期])+[增加年份], Month([基準日期]), Day([基準日期]))

DateSerial(Year(2016-09-05)+2, Month(2016-09-05), Day(2016-09-05))

→DateSerial(2016+2, 9, 5)→2018-09-05

這些運算式寫好時…就來做判斷的組合了…又要用到經典函數了

iif(判斷式,成立,不成立)

在Excel中函數為if，Access多了個i喔~iif...千萬別寫錯囉~

我們來完成這個判斷吧!

=iif(isnull([基準日期]),"",DateAdd("yyyy",[增加年份],[基準日期]))

您，組對了嗎??

提問單[Excel]：如何讓Excel日期裡，星期六日的欄位底色自動變色?(設定格式化的條件)

Scenic 12:53 No comments

續前一張提問單： 提問單[Excel]：如何讓Excel輸入起始日期後，自動展開整個月份的日期及星期?

要如何讓表中星期六、星期日自動更換底色呢?

我們可以善用「設定格式化的條件」喔!

第一步：[F3儲存格]，點選該儲存格再打開「設定格式化的條件」/「新增規則」

第二步：點選「使用公式來決定要格式化哪些儲存格」，並在規則說明中，打上以下判斷式

=AND(F$3<>"",OR(WEEKDAY(F$2,2)=6,WEEKDAY(F$2,2)=7))

AND(條件1 , 條件2 , …)：裡面所有條件都成立的話，回傳真(True)，就是必需全部成立才行~
OR(條件1 , 條件2 , … )：裡面只要有任何一個條件成立，回傳真(True)，就是只要任一個成立就行~
WEEKDAY(日期, 顯示類別)：依據日期及顯示類別設定，回傳星期幾。其中，顯示類別填2，代表星期一會回傳1、星期二會回傳2、星期天會回傳7。所以我使用2這個類別比較符合我對星期幾的習慣。

白話文的說法：只要F2日期不是空白，而且F3是星期六或星期日，那就可以依據格式設定自動調整底色。

請注意：判斷式中，針對儲存格有設定絕對位置的標記$(目前設定在列數)，主要就是該格式若要向上下、向左右套用到其它儲存格內時，判斷的列數不會因此而變動。這是一個非常重要好習慣~~