標題:
[發問]
請教如何對比查詢多筆日期資料，取出相符日期的特定刷卡資料
[打印本頁]
作者:
jackson7015
時間:
2014-8-19 09:08
標題:
請教如何對比查詢多筆日期資料，取出相符日期的特定刷卡資料
本帖最後由 jackson7015 於 2014-8-19 09:11 編輯
請問站上的大大們
如何對比查詢相符日期後，帶出相符日期的
中午12點以前
的
最後一筆
刷卡資料
因為每日的刷卡時間有許多段，而且所需的日期非固定日期
所以想請教各位大大該如何編寫此種公式函數?
[attach]18959[/attach]
作者:
p212
時間:
2014-8-19 10:38
本帖最後由 p212 於 2014-8-19 10:50 編輯
回復
1#
jackson7015
1、定義名稱
(1)定義「日期」，「參照到」輸入
=工作表!$A$2:$A$23
(2)定義「時間」，「參照到」輸入
=工作表!$B$2:$B$23
註:(1)與(2)可用直接選取工作表!$A$1:$B$23範圍，再按Ctrl+Shift+F3，勾選「頂端列」定義名稱。
(3)游標停於「(單張)」工作表之儲存格F8進行定義名稱「Date」，「參照到」輸入
=YEAR(' (單張)'!
$C
8)-1911&TEXT(MONTH(' (單張)'!
$C
8),"00")&TEXT(DAY(' (單張)'!
$C
8),"00")
2、儲存格F8輸入
=INDEX(時間,MATCH(
--
Date,日期,0)+COUNTIF(日期,Date)-1)
請參考！
作者:
jackson7015
時間:
2014-8-19 16:54
回復
2#
p212
感謝p212大大的指導
表格公式運作正常
想請教是否能不使用名稱管理員的方式做運算?
因為報支單的表格有數十份，所以在做名稱管理的時候會標示更多名稱，有時候會有點混亂
想請問是否有單一儲存格公式編寫的方式，直接運算取得數值?
作者:
p212
時間:
2014-8-20 08:56
本帖最後由 p212 於 2014-8-20 09:08 編輯
回復
3#
jackson7015
「(單張)」工作表之B欄與C欄的儲存格格式可以改用「通用格式」？如此可避免透過定義名稱(簡化公式)轉換原有C欄的資料格式。
例如：
(1)先「(單張)」工作表之B欄與C欄的儲存格格式改成「通用格式」
(2)再將「(單張)」工作表之儲存格F3公式依2#的=INDEX(時間,MATCH(--Date,日期,0)+COUNTIF(日期,Date)-1)架構修改為
=INDEX(
工作表!$B$2:$B$23
,MATCH(--(
LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"00")
),
工作表!$A$2:$A$23
,0)+COUNTIF(
工作表!$A$2:$A$23
,
LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"00")
)-1)
如此可將定義名稱的「
日期
」、「
時間
」與「
Date
」刪除。
注意！「(單張)」工作表之儲存格I4內容要清除左端空白方可正確回應答案
請參考！
作者:
jackson7015
時間:
2014-8-20 16:12
回復
4#
p212
感謝p212大大的不吝幫忙
公式修改的差不多了
自己要修改公式的部分剩下，「(單張)」工作表之B欄與C欄的儲存格格式改成「通用格式」
因為B、C欄位是從其他表格複製過來的，若只複製「值」的話，會呈現日期碼
所以再來就是自己想辦法把大大提供的公式，把TEXT的取得值換算成日期格式了
再次感謝大大的幫忙~
感激不盡~
作者:
jackson7015
時間:
2014-8-21 16:44
公式修正後，只改C欄部分的公式即可
=INDEX(工作表!$B$2:$B$23,MATCH(--(LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"
DD
")),工作表!$A$2:$A$23,0)+COUNTIF(工作表!$A$2:$A$23,LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"
DD
"))-1)
作者:
Hsieh
時間:
2014-8-22 10:46
回復
6#
jackson7015
F8公式
=LOOKUP(2,1/((OFFSET(工作表!$A$2,,,COUNT(工作表!A:A),)=--TEXT(C8,"emmdd"))*(OFFSET(工作表!$B$2,,,COUNT(工作表!A:A),)<0.5)),OFFSET(工作表!$B$2,,,COUNT(工作表!A:A),))
向下複製
作者:
jackson7015
時間:
2014-8-26 16:37
回復
7#
Hsieh
感謝Hsieh板主的救援
公式運算皆正常
不過可能要研究一段時間了，裡面有些不太懂的運算符號
例如
--TEXT
的
--
不曉得是做為什麼用途的
謝謝版主的協助
作者:
Hsieh
時間:
2014-8-26 21:14
回復
8#
jackson7015
轉成數值
負負得正
作者:
jackson7015
時間:
2014-11-5 08:51
回復
7#
Hsieh
Hsieh版大您好；
想請問公式中，在提取31日的時間或最後一日的時間，有時會有錯誤
是要修正哪個部分嗎?
[attach]19483[/attach]
作者:
ML089
時間:
2014-11-5 09:22
回復
10#
jackson7015
C18 = 2014/10/30
C19 = 1900/131
C19的資料有誤造成錯誤
若是每個月處理情況只要比對 "日" 不管年月可以使用下列公式
=MOD(LOOKUP(DAY(C8)+0.5,DAY(TEXT(S$2:S$99,"!r0-00-00"))+T$2:T$99),1)
若是C19的資料有誤能修正時，可用下列公式
=MOD(LOOKUP(C8+0.5,TEXT(S$2:S$99,"!r0-00-00")+T$2:T$99),1)
MOD()是將日期去掉剩時間，視情況MOD()也可以不用就回回傳 日期+時間
=LOOKUP(C8+0.5,TEXT(S$2:S$99,"!r0-00-00")+T$2:T$99)
作者:
jackson7015
時間:
2014-11-5 13:01
回復
11#
ML089
感謝ML089版主的指導
出錯的原因是自己直接Keyin日期，沒有按照格是輸入
感謝提醒錯誤，更正後已無問題了
感謝提供另外的公式，測試後都沒有問題
再次謝謝各位前輩的指導
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
