返回列表 上一主題 發帖

[發問] 請教 Countif ,Vlookup, COLUMN 函數運用問題

pupai

帖子
184 
主題
45 
精華
0 
積分
407 
點名
0  
作業系統
WIN 7 
軟體版本
office 2010 
閱讀權限
20 
性別
男 
註冊時間
2010-8-19 
最後登錄
2024-11-1 

論壇贊助勳章
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2014-5-27 10:27 | 只看該作者

[發問] 請教 Countif ,Vlookup, COLUMN 函數運用問題

您好
附件檔案中，我如果在分頁(過帳檔)插入欄位，資料會跑掉。
舉例：
1. I欄插入欄位，為何原本H欄及K欄資料會跑掉，但原本B、C及G欄資料不會跑掉。
2. 另外我在D欄插入欄位，為何原本G欄、H欄及K欄資料會跑掉，但原本B、C欄資料不會跑掉
我想請教有什麼方法，可以讓我隨意插入欄位，資料也不會跑掉。
謝謝!!

TEST.rar (499.58 KB)

收藏 分享

p212

帖子
618 
主題
17 
精華
0 
積分
663 
點名
1  
作業系統
WINDOWS 7 
軟體版本
OFFICE 2013 
閱讀權限
50 
性別
男 
來自
Pingtung 
註冊時間
2011-9-2 
最後登錄
2024-7-5 

2014馬年全勤獎章2013蛇年全勤獎章熱烈參與勳章
2#
發表於 2014-5-27 11:03 | 只看該作者
本帖最後由 p212 於 2014-5-27 11:17 編輯

回復 1# pupai
1、儲存格H2原公式 =IF(COUNTIF(資料!$A:$A,$A2),VLOOKUP($A2, 資料!$A:$AT,COLUMN(I2),),"")
I欄插入欄位後會變成=IF(COUNTIF(資料!$A:$A,$A2),VLOOKUP($A2, 資料!$A:$AT,COLUMN(J2),),"")
H欄資料一定會跑掉
2、「過帳」工作表之儲存格B2輸入
=IFERROR(INDEX(資料!Print_Area,MATCH($A2,資料!$A:$A,0),MATCH(B$1,資料!$1:$1,0)),"")
向下向右複製公式
3、「過帳」工作表之「(新)單價」欄位不在「資料」工作表內，故無資料可對應。
4、「過帳」工作表與「資料」工作表內的欄位名稱完全相同者，則可對應出資料，且不受插入欄位影響。
請參考！

TOP

pupai

帖子
184 
主題
45 
精華
0 
積分
407 
點名
0  
作業系統
WIN 7 
軟體版本
office 2010 
閱讀權限
20 
性別
男 
註冊時間
2010-8-19 
最後登錄
2024-11-1 

論壇贊助勳章
3#
發表於 2014-5-27 14:11 | 只看該作者
回復 2# p212


    請教如果用此種方式對應
資料如果上千筆以上(或更多)
將來索引對照會不會很花時間
謝謝!!

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
246  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-11-12 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
4#
發表於 2014-5-27 15:21 | 只看該作者
回復 1# pupai

如果資料與過帳資料的標題能夠一致那就比較容易

    TEST.zip (962.86 KB)
學海無涯_不恥下問

TOP

p212

帖子
618 
主題
17 
精華
0 
積分
663 
點名
1  
作業系統
WINDOWS 7 
軟體版本
OFFICE 2013 
閱讀權限
50 
性別
男 
來自
Pingtung 
註冊時間
2011-9-2 
最後登錄
2024-7-5 

2014馬年全勤獎章2013蛇年全勤獎章熱烈參與勳章
5#
發表於 2014-5-27 17:15 | 只看該作者
本帖最後由 p212 於 2014-5-27 17:25 編輯

回復 3# pupai
1、承如4#超版Hsieh指導的：「如果資料與過帳資料的標題能夠一致那就比較容易」，是否調整一下使「過帳」工作表與「資料」工作表內的欄位名稱完全相同。
2、2#提及的「過帳」工作表之儲存格B2輸入=IFERROR(INDEX(資料!Print_Area,MATCH($A2,資料!$A:$A,0),MATCH(B$1,資料!$1:$1,0)),"")部份，其「資料!Print_Area」您可自行調整範圍，或以OFFSET方式定義成動態範圍。
3、個人認為用2#的方法對待上千筆以上的資料，其所花費的時間應還算OK！(用「過帳」工作表的欄位名稱去對應「資料」工作表欄位，即可避免受插入欄位的影響。)
以上說明不知對您有無幫助？還是誤會您的想法(如調整「過帳」與「資料」兩工作表的欄位名稱)？

TOP

pupai

帖子
184 
主題
45 
精華
0 
積分
407 
點名
0  
作業系統
WIN 7 
軟體版本
office 2010 
閱讀權限
20 
性別
男 
註冊時間
2010-8-19 
最後登錄
2024-11-1 

論壇贊助勳章
6#
發表於 2014-5-27 18:19 | 只看該作者
回復 5# p212

   了解
    謝謝您的指導
   感恩

TOP

pupai

帖子
184 
主題
45 
精華
0 
積分
407 
點名
0  
作業系統
WIN 7 
軟體版本
office 2010 
閱讀權限
20 
性別
男 
註冊時間
2010-8-19 
最後登錄
2024-11-1 

論壇贊助勳章
7#
發表於 2014-5-28 14:44 | 只看該作者
回復 4# Hsieh


    謝謝版主
   感恩!!

TOP

pupai

帖子
184 
主題
45 
精華
0 
積分
407 
點名
0  
作業系統
WIN 7 
軟體版本
office 2010 
閱讀權限
20 
性別
男 
註冊時間
2010-8-19 
最後登錄
2024-11-1 

論壇贊助勳章
8#
發表於 2014-5-28 15:57 | 只看該作者
回復 4# Hsieh


    請教超版
依照你的方式
我在「資料」工作表的A欄位插入欄位
「過帳」工作表的數值就跑掉不對
請教該如何修飾

TOP

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
246  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-11-12 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
9#
發表於 2014-5-28 16:09 | 只看該作者
回復 8# pupai
將表格的原點定義名稱
只要該儲存格不被刪除應該都有效
TEST.zip (963.41 KB)
學海無涯_不恥下問

TOP

        靜思自在 : 生氣，就是拿別人的過錯來懲罰自己。
返回列表 上一主題