請教欄位增減自動排序及下拉選單關鍵變動的相關公式問題?

hairyhead
個人積分：5分
文章編號：58812087

5分

樓主

2016-01-06 16:09

請教各位關於EXCEL公式的寫法

1.如果當月共15筆的欄位為A2~A16，如果下個月到20筆或10筆時，有辨法項次可以隨增加或減少自動排序產生嗎?

2.當日期欄位為B2至B16，因收到票據時候點不同(日期是按照發票寫的)，這部份該怎麼讓它自動按照日期排序呢? (配合第一個項次問題)

3.類別的部份預以前都是用自已辦判在KEY，如果想改成下拉選單方式，分門市、工程、代理三部份，這部份有辨法配合C2~C16的欄位做連動嗎? 例如…當C2打門市-淨水器，H2的下拉選單自動帶出門市部，如果C2只有打淨水單，H2下拉選單則手動去拉? (一樣也是要配合第一個項次的問題，但…如果再加上色塊區別有辨法嗎? )

不知道以上三個問題公式有辨法做得到? 或者可以做到什麼程度呢? 因為手上當月的筆項其實都是100筆~200筆之間在跳，我只抓一小部份來，之前都是用傳統方式一項一項KEY(以前比較少)，現在筆數、類別變多了，光要核對1個月看到眼花了

謝謝

2016-01-06 16:09 發佈

文章關鍵字欄位排序下拉選單公式問題

Wesley Hsu

Wesley Hsu
個人積分：988分
文章編號：58813093

988分

2樓

2016-01-06 17:25

1. 你去查Excel流水號，看看能否解決你的問題

或者直接使用=ROW()這個函數，因為你是從第二行開始的，所以填=ROW()-1就可以了

2. 查Excel排序，流水號是自動算的，不影響

3. 查Excel下拉，你的需求應該會使用到二層下拉

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：58816533

7527分

3樓

2016-01-06 23:05

1.
A 欄函數：=IF(B2="","",ROW()-1)
可直接複製到第 250 列（預留彈性空間），B 欄有輸入日期時項次才會出現！

2.
排序部份可等到月底資料都輸入完畢後再進行，
請依圖一步驟操作即可！

3.
如果要簡單一點，可在名稱前加上單位代碼的數字，
H 欄函數：=IFERROR(CHOOSE(LEFT(C2,1),"門市","工程部","代理"), "")
類別欄則會自動帶出部門名稱（函數同樣可複製到第 250 列），
套色則可依圖二步驟操作！

My Interior Knowledge is Extraordinaire

hairyhead

hairyhead
個人積分：5分
文章編號：58821170

5分

樓主

2016-01-07 11:15

Mystique Hsiao wrote:
1.
A 欄函數：=IF...(恕刪)

照這個方式簡化很多了

第3點問題

,1若改成中文可以嗎?

剛剛試了一下，不簡化1、2、3來表示

好像就不行了 (上面寫引號過長)

然後下拉選單 (我是用資料驗證的清單做)

選單裡面不會出現門市、工程部、代理

另外2個問題

1.因為只要做增刪的動作，公式就是會少掉一行呢?

我是先做滿250列表格公式(按+下拉複製)

若當月只有100筆(共100列表格)

把其它150列空白表格做刪除之後

上面的公式也跟著被清除了

就會變成若次月有200筆(共200列表格)

我就是要用插入表格方式

從原100筆/100列表格

新增到200筆/200列表格

這新增出來的100筆/100列表格公式是空白

變成得從再複製貼公式上去

2.日期方面是否可以單純輸入20150101

然後自動變成2015/01/01方式嗎?

知道儲存格方式可以改日期

但原系統預設是方式 2015/1/1 (0不見了)

然後須要補/才有辨法正常顯示日期格式

謝謝

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：58827409

7527分

5樓

2016-01-07 20:11

hairyhead wrote:
1若改成中文可以嗎?

改成中文的話，H 欄的函數可修改如下：
=IFERROR(LEFT(C2,SEARCH("-",C2)-1),"")

hairyhead wrote:
另外2個問題
1.因為只要做增刪的動作，公式就是會少掉一行呢?

其實可以不用刪除，
因為只要該行沒有輸入資料，有函數的欄位自然會變成空白（已經有考慮過了），
若真的想刪除，而後續又有更多資料，
只要直接輸入 A, F, H 等欄以外的資料，不用再插入表格，
而該三欄的函數直接向下複製即可！

hairyhead wrote:
2.日期方面是否可以單純輸入20150101
然後自動變成2015/01/01方式嗎?
知道儲存格方式可以改日期
但原系統預設是方式 2015/1/1 (0不見了)
然後須要補/才有辨法正常顯示日期格式

不行，因為會被當成純數字，或是日期的序列值，
（日期的序列值為距離 1900 年 1 月 1 日的天數）
所以會造成日期錯亂，
日期格式的設定請參考下圖（滑鼠右鍵＞儲存格格式，或直接按 Ctrl+1）：

My Interior Knowledge is Extraordinaire

hairyhead

hairyhead
個人積分：5分
文章編號：58833547

5分

樓主

2016-01-08 11:00

Mystique Hsiao wrote:
改成中文的話，H ...(恕刪)

想請問一下?
原第一個問題是說該欄若沒有輸入資料
則會顯示空白

因為後來試過提供的公式
A欄 =IF(B2="","",ROW()-1)
H欄 =IFERROR(CHOOSE(LEFT(C2,1),"門市","工程部","代理"), "")
各複製公式到250列

然後列印顯示則變成這樣 (沒有輸入的空白欄也會跟著跑出，複製250列則共有6頁之多)

我的最後一列則是上面全部累加，會列印到6頁，15號之後都是空白欄

這有辨法只顯示欄位內有資料的嗎? (最後一列的總計會自動補位到有資料的最後一列)

謝謝

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：58834179

7527分

7樓

2016-01-08 11:39

hairyhead wrote:
想請問一下?
原第一個問題是說該欄若沒有輸入資料
則會顯示空白
然後列印顯示則變成這樣 (沒有輸入的空白欄也會跟著跑出，複製250列則共有6頁之多)

因為您前面沒有提到列印的需求，所以我也沒考慮進去，
如果是這樣的話，那麼每個月就可以把下方無資料的部份刪除，
或是在列印時選擇要列印的頁數範圍！

hairyhead wrote:
我的最後一列則是上面全部累加，會列印到6頁，15號之後都是空白欄
這有辨法只顯示欄位內有資料的嗎? (最後一列的總計會自動補位到有資料的最後一列)

建議將總計放在第二列，並開啟檢視標籤中的凍結窗格，
這樣的話，即使資料往下輸入，也可以隨時看到總計數值！
A 欄函數改為：=IF(B3="","",ROW()-2)
（從 A3 開始）

My Interior Knowledge is Extraordinaire

hairyhead

hairyhead
個人積分：5分
文章編號：58834582

5分

樓主

2016-01-08 12:08

Mystique Hsiao wrote:
因為您前面沒有提到...(恕刪)

瞭解~ 可能我把事情想太多了
把總計拉到上面可以解決...

公式方式辨法鎖死在某一個欄位嗎?
例如… A3:A252已經寫好公式
它的公式就是一直會在A3:A252不變
本月有100筆
然後等到次月的時候變成50筆
一般我的做法就是會把多出來的50筆選取做刪除的動作
但是原A53:A102的公式
會因為做了刪除的動作則消失了
所以預先做好的250格的公式就變少成200格了

請問這部份有辨法鎖定某欄位公式就是不變
還是我的做法要變呢?

謝謝

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：58835228

7527分

9樓

2016-01-08 13:04

hairyhead wrote:
公式方式辨法鎖死在某一個欄位嗎?
例如… A3:A252已經寫好公式
它的公式就是一直會在A3:A252不變
本月有100筆
然後等到次月的時候變成50筆
一般我的做法就是會把多出來的50筆選取做刪除的動作
但是原A53:A102的公式
會因為做了刪除的動作則消失了
所以預先做好的250格的公式就變少成200格了

請問這部份有辨法鎖定某欄位公式就是不變
還是我的做法要變呢?

建議另外建立一個包含公式的空白表格當作制式範本，
月初時另外複製一份出來使用，
這樣前一個月下方無資料的空白表格就可以刪除！

此外，D2, E2, F2 三個合計欄位的函數可參考下圖：

My Interior Knowledge is Extraordinaire