Pages

Subscribe:

Ads 468x60px

Labels

顯示具有 Excel2007 標籤的文章。 顯示所有文章
顯示具有 Excel2007 標籤的文章。 顯示所有文章

2014年3月7日 星期五

excel輸入數值超過12位數會轉為科學符號..很困擾

儲存格格式/[數值]:類別(C): 選擇 自訂/將類型改為 0

注意:最長位數到 Double 的上限,1 開頭是 16 位數,其他開頭是 15 位數,超過此長度的話,請改用字串格式,也就是 '1234567890123456790

2013年8月24日 星期六

EXCEL 下拉式選單 - 多階式

EXCEL的下拉選單,大家都知道用資料驗證>>清單,這樣作下拉選單,又快速又輕鬆
之前介紹過一個二階式的下拉選單,這樣可以更輕鬆的輸入資料
而更有許多人在使用上,需要三階、四階選單,甚而更多階的下拉選單
通常參照這些清單位置,會以定義名稱來處理,這裡介紹不須定義名稱,多階式下拉選單的簡便作法

假設下圖為我們所需要輸入的表格:


每一個欄位的下拉選單,都須依照上一個選項而決定內容
而首先,我們必須先建立一個下拉選單對應的資料庫,如下圖


我們必須了解的是,EXCEL驗證清單,是不允許參照其他工作表的
所以比較簡單的作法,是將資料放在同一個工作表中,而針對對應的驗證清單,以不同的公式帶出
如下圖


各欄驗證清單公式如下

A2
=OFFSET($H$1,1,0,COUNTA(H:H)-1,1)

B2
=OFFSET(INDIRECT("H"&MATCH($A2,$H:$H,0)),0,1,1,4)

C2
=OFFSET(INDIRECT("N"&MATCH($B2,$N:$N,0)),0,1,1,3)

D2
=OFFSET(INDIRECT("S"&MATCH($C2,$S:$S,0)),0,1,1,3)

E2
=OFFSET(INDIRECT("X"&MATCH($D2,$X:$X,0)),0,1,1,4)

完成第2列的設定後,在將儲存格往下複製即可~
這樣,馬上就完成一個多階式的下拉選單了
可以參考以下附檔
http://www.FunP.Net/28199


當然,B2:E2 簡便一點,設一個公式就好
在B2的資料驗證清單,利用以下公式
=OFFSET(INDIRECT("R"&MATCH(A2,OFFSET($A$1,,MATCH(A$1,$H$1:$AB$1,)+6,100,1),0)&"C"&MATCH(A$1,$H$1:$AB$1,)+7,),0,1,1,4)

再將B2複製到B2:E10  這樣也可以囉~
可以參考以下附檔
http://www.FunP.Net/574188


因為多階式清單的資料庫,通常很多資料,與輸入表單放在同一個工作表,總會有很混亂的感覺
因此通常會將多階式清單的資料庫放在其他的工作表,再利用定義名稱設定來引用
這裡介紹一個不用定義名稱的方法,但是公式可會複雜多了~
假設輸入表單在"MENU"工作表,表單資料在"DATA"工作表,如下圖







MENU!A2驗證公式為
=INDIRECT("DATA!A2:A10")

B2
=OFFSET(INDIRECT("DATA!A1"),MATCH(A2,INDIRECT("DATA!A:A"),)-1,1,1,3)

C2
=OFFSET(INDIRECT("DATA!G1"),MATCH(B2,INDIRECT("DATA!G:G"),)-1,1,1,3)

D2
=OFFSET(INDIRECT("DATA!L1"),MATCH(C2,INDIRECT("DATA!L:L"),)-1,1,1,3)

E2
=OFFSET(INDIRECT("DATA!Q1"),MATCH(D2,INDIRECT("DATA!Q:Q"),)-1,1,1,3)

與之前一樣,第二列設訂完成後,再往下複製即可完成~
可以下載以下附件參考~
http://www.FunP.Net/122712


如果嫌棄這樣逐一設定太麻煩,B2:E2 也可以用一個公式來設定,
不過這得挑戰您對公式函數的運用能力了
B2:E10 
=OFFSET(INDIRECT("DATA!A1"),MATCH(A2,OFFSET(INDIRECT("DATA!A1"),,MATCH(A$1,INDIRECT("DATA!$1:$1"),)-1,100,1),)-1,MATCH(A$1,INDIRECT("DATA!$1:$1"),),1,3)



參考檔案~
http://www.FunP.Net/412720

以上介紹為不使用定義名稱時,多階式下拉選單作法
其重點多在公式函數的運用,當驗證清單跨工作表時,常常必須使用定義名稱,來簡化公式
以後有機會在來介紹包含定義名稱的多階式下拉選單吧~

http://tw.myblog.yahoo.com/vincent-excel/article?mid=2217&sc=1

火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(進階)

火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(進階): 前一篇文章提到:使用多層下拉式清單結構輸入資料(基礎) 參考網址: http://isvincent.blogspot.com/2010/06/excel_25.html 這次要來練習:不需要每個年級都定義一個名稱,而是要以整個基本資料表為單位來查詢。 ...

火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(基礎)

火星人的天空之城: Excel-使用多層下拉式清單結構輸入資料(基礎): 在Excel中輸入資料,如果完全以Key In方式輸入,除了耗費較多時間之外,還可能打錯字。所以如果能以選取方式來輸入資料,應該可以改善這些問題。解決這個問題,利用資料驗證、INDIRECT函數和定義名稱,可以很簡單的解決。 假設要有三個年級,每個年級的班級名稱都不相同,規則...

2013年5月29日 星期三

TQXC Excel 2007 310 監視器報價管理系統解題說明-7 洪浚銘老師主講

TQXC Excel 2007 310 監視器報價管理系統解題說明-6 洪浚銘老師主講

TQXC Excel 2007 310 監視器報價管理系統解題說明-5 洪浚銘老師主講

TQXC Excel 2007 310 監視器報價管理系統解題說明-4洪浚銘老師主講

TQXC Excel 2007 310 監視器報價管理系統解題說明-3 洪浚銘老師主講

TQXC Excel 2007 310 監視器報價管理系統解題說明-2 洪浚銘老師主講

TQXC Excel 2007 310 監視器報價管理系統解題說明-1 洪浚銘老師主講

2013年5月22日 星期三

.TQXC Excel 2007 308分期付款帳款管理系統-5洪浚銘老師主講 .

.TQXC Excel 2007 308分期付款帳款管理系統-4 洪浚銘老師主講 .

.TQXC Excel 2007 308分期付款帳款管理系統-3洪浚銘老師主講 .

.TQXC Excel 2007 308分期付款帳款管理系統-2 洪浚銘老師主講 .

.TQXC Excel 2007 308分期付款帳款管理系統-1 洪浚銘老師主講 .

TQC Excel2007 306 解題說明-3洪浚銘老師主講

TQC Excel2007 306 解題說明-2 洪浚銘老師主講

TQC Excel2007 306 解題說明-1 洪浚銘老師主講