iT邦幫忙

0

EXCEL 網站裡面公式是如何設定的?(iferror和vlookup)

https://www.toutiao.com/i6526332091414610446/
我想知道↑網站上公式是如何設定
並且想改良是否同樣4/17 可以跑出2筆資料
tina 010703,
妳的要求,是不難,只是有點複雜哦!
我的回答儘量讓妳看懂,不懂的還是要自行查詢一下哦!!
0
舜~
iT邦高手 1 級 ‧ 2019-04-17 09:06:05

公式教學與設定
Excel 中的公式概觀

公式與法

1.IFERROR 函數
如果公式計算結果錯誤,就會傳回您所指定的值,否則傳回公式的結果。 使用 IFERROR 函數可以捕捉並處理公式中的錯誤。

語法
IFERROR(value, value_if_error)
Value 必要。 這會檢查此引數是否有錯誤。
Value_if_error 必要。 這是公式計算錯誤時要傳回的值。

2.VLOOKUP 函數
當您需要在表格或範圍中按列搜尋資料時,請使用查閱與參照函數其中之一的 VLOOKUP。 例如按零件編號查閱某個汽車零件的價格時。

語法
VLOOKUP(想要查閱的值, 想要查閱值的範圍, 範圍中包含傳回值的欄號, 完全符合或大約符合 (以 0/FALSE 或 1/TRUE 指示))

其他
Excel 行事曆範本
如何在 Excel 中建立行事曆

0
海綿寶寶
iT邦大神 1 級 ‧ 2019-04-17 18:08:55

先講結論
點4/17要出現一筆以上的資料
唯一的方法就是寫 VBA

至於原作者設定的公式為

=IFERROR(VLOOKUP(INDIRECT("r"&IF(MOD(CELL("row"),2)=0,CELL("row")-1,CELL("row"))&"c"&CELL("col")),表1,2,FALSE),"無事項記錄")

講到底只是一個多了一些取值的 VLOOKUP
VLOOKUP(...,表1,2,FALSE)後面三個參數已經寫好了
以下說明第一個值的做法

以上圖的 4/17來看
公式要用的是日期(D7)的值以公式來寫就是 INDIRECT("r7c4")

所以要想辦法組出這個結果
column=4的值與「游標點的儲存格」相同,即CELL("col")
row 的值則要多加一個判斷
4/17 有可能點的儲存格是 D7 或者 D8 (要的是 D7)
所以就要以「目前的 row 值若為偶數就要減一,否則就直接用 row 值」
這就是IF(MOD(CELL("row"),2)=0, CELL("row")-1, CELL("row"))

有了 row, column 值再用 "r" & row值 & "c" & column值串起來
放進 INDIRECT 裡變成

INDIRECT("r"&IF(MOD(CELL("row"),2)=0,CELL("row")-1,CELL("row"))&"c"&CELL("col"))

再放進 VLOOKUP 裡

VLOOKUP(INDIRECT("r"&IF(MOD(CELL("row"),2)=0,CELL("row")-1,CELL("row"))&"c"&CELL("col")),表1,2,FALSE)

就大功告成了

最外面再加個 IFERROR
只是為了「如果沒資料不要顯示 N/A,顯示無事項記錄」而已

=IFERROR(VLOOKUP(INDIRECT("r"&IF(MOD(CELL("row"),2)=0,CELL("row")-1,CELL("row"))&"c"&CELL("col")),表1,2,FALSE),"無事項記錄")

另外
如果妳只是在練習 Excel 的功能就算了
如果妳打算拿這個應用在工作上
我就非常不建議了

VBA寫個Selection_Change事件

0
starcity1003
iT邦新手 4 級 ‧ 2019-06-09 20:59:52

析解J4欄位"詳細事項"的語法:
=IFERROR(VLOOKUP(INDIRECT("r"&IF(MOD(CELL("row"),2)=0,CELL("row")-1,CELL("row"))&"c"&CELL("col"),0),工作記事!L:N,2,0),"無事項")

首先你要瞭解幾個語法的用法:
語法1.CELL("row")
   找出選取(編輯游標所在)儲存格的"列"數
語法2.MOD(語法1的結果,2)
   前項"列"數除2的餘數(意義是找出單數列與雙數列)
語法3.IF(函數2的結果=0,函數1的結果-1,函數1的結果)
   如果是單數列就顯示列數,如果不是單數列就顯示雙數列的後一個單數列數
參數4."r"&函數3的結果&"c"&函數1的結果
   儲存格的位置表示方式,
   例如A1等於r1c1, A2等於r2c1, B1等於r1c2…依此類推,
   但公式用if把單、雙數列都表示成單數列了
語法5.INDIRECT(參數4,0)
   INDIRECT是用來表示儲存格的內容,
   在公式中用語法3搭配參數4的意義,是為了
   不論你選單數列(日期)還是雙數列(標題),
   都能用單數列(日期)當做查詢值去比對出對應值的內容。
語法6.VLOOKUP(查詢值,查詢範圍,對應值位置,0)
   VLOOKUP(函數5的結果,查詢範圍,對應值位置,0)
   vlookup是用來尋找對應值的,此處搭配語法5,
   是為了求出該日期,所對應的事項。
語法7.IFERROR(函數7的結果,錯誤回應)
   IFERROR是判斷是否錯誤,
   因為語法6如果找不到查詢值,就會發生錯誤,
   此處是為了把錯誤顯示為空白(表示工作記事沒有這個標題事項)

再來,
想要達到你的目的在事項比對出2筆或以上資料的話,
你要先對比對資料做一些處理:
1.在表單"工作記事"的欄位"時間"前面,先插入6個欄位:
https://ithelp.ithome.com.tw/upload/images/20190609/200999218VFt15XjIW.jpg
第1欄:4碼日期

=IF(MONTH(G2)<10,"0"&MONTH(G2),MONTH(G2))&IF(DAY(G2)<10,"0"&DAY(G2),DAY(G2))

第2欄:日期出現的次序

=COUNTIF($A$1:A2,A2)

第3欄:日期與次序

=A2&B2

第4欄:上一個日期次序

=A2&B2-1

第5欄:日期出現的次數

=COUNTIF(A:A,A2)

第6欄:日期與次數

=A2&E2

https://ithelp.ithome.com.tw/upload/images/20190609/20099921sIAB66ITo4.jpg

2.在表單"工作記事"的欄位"標題"(你的範例叫"主要標記")後面,插入5個欄位:
https://ithelp.ithome.com.tw/upload/images/20190609/20099921zUrs04dBwS.jpg
第1欄:累加事項(注意:在H2&IFERROR("後面是換行,同時按ALT+ENTER)

=H2&IFERROR("
"&VLOOKUP(D2,$C$1:K2,8,0),"")

https://ithelp.ithome.com.tw/upload/images/20190609/20099921DPyhLXgcAr.jpg
第2欄:累加標題(注意:在I2&IFERROR("後面是換行,同時按ALT+ENTER)

=I2&IFERROR("
"&VLOOKUP(D2,$C$1:K2,9,0),"")

第3欄:最後顯示日期

=IF($C2=$F2,G2,"")

第4欄:最後顯示事項

=IF($C2=$F2,J2,"")

第5欄:最後顯示標題

=IF($C2=$F2,K2,"")

3.表單"行事曆"中的公式範圍要修改:
每天的"標題"(主要標記)部份:
修改前:

=IFERROR(VLOOKUP(B5,工作記事!$A:$C,3,0),"")

修改後:

=IFERROR(VLOOKUP(B5,工作記事!$L:$N,3,0),"")

https://ithelp.ithome.com.tw/upload/images/20190609/20099921uIClzHesn5.jpg

"詳細事項"部份:
修改前:

=IFERROR(VLOOKUP(INDIRECT("r"&IF(MOD(CELL("row"),2)=0,CELL("row")-1,CELL("row"))&"c"&CELL("col"),0),工作記事!A:C,2,0),"無事項")

修改後:

=IFERROR(VLOOKUP(INDIRECT("r"&IF(MOD(CELL("row"),2)=0,CELL("row")-1,CELL("row"))&"c"&CELL("col"),0),工作記事!L:N,2,0),"無事項")

https://ithelp.ithome.com.tw/upload/images/20190609/20099921cT1BFRpxpL.jpg

這樣就完成了!

我要發表回答

立即登入回答