PTT數位生活區 / Office

[算表] 找個別值、最大值及最小值

看板Office作者 (萬物靜觀皆自得)時間10年前 (2015/12/24 21:23), 10年前編輯推噓0(0推 0噓 50→)
留言50則, 2人參與, 最新討論串1/1
軟體:Excel 2007 有一個原始資料內容如下： 物件 區域 異動日 A 1 201407 A 2 201409 B 3 201506 B 2 201403 A 3 201501 A 1 201412 C 2 201509 想要求得以下結果，請問區域、異動日要怎麼下公式？ 區域 ：例如A在原始資料中有1、2、3三個區域，並且用 - 區隔 B 2、3 C 只有 2 異動日：找出A最小及最大的異動日，並以 - 區隔，若只有一個日期就直接顯示(例如C) 物件 區域 異動日 A 1-2-3 201407-201501 B 2-3 201403-201506 C 2 201509 再麻煩大家指點一下，謝謝 -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 1.162.224.179 ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1450963416.A.C78.html
→
12/25 00:55, , 1F
12/25 00:55, 1F
→
12/25 06:57, , 2F
感謝s大回覆，想要的結果應該寫得不清楚，再說明一下~
12/25 06:57, 2F
應該是說從原始資料表中，彙整出 每一個物件在什麼區域出現過，以及異動日的最大最小值，例如： A2儲存格為，A曾在區域1-2-3這三個地方出現過，其最大最小的異動日為201407-201501 A B C 1 物件 區域 異動日 2 A 1-2-3 201407-201501 3 B 2-3 201403-201506 4 C 2 201509 ※ 編輯: Fmajor (1.162.224.179), 12/25/2015 07:14:11
→
12/25 07:14, , 3F
檔案的結果看起來和原po回文是
12/25 07:14, 3F
→
12/25 07:15, , 4F
一樣的，因此不太了解
12/25 07:15, 4F
→
12/25 07:48, , 5F
s大，我看到的是http://imgur.com/FHGaxTn
12/25 07:48, 5F
→
12/25 07:49, , 6F
會不會網址貼錯了？
12/25 07:49, 6F
→
12/25 07:58, , 7F
https://goo.gl/yB50JP 抱歉回文的第一筆連結錯誤
12/25 07:58, 7F
→
12/25 07:59, , 8F
請以上面連結為準
12/25 07:59, 8F
→
12/25 08:09, , 9F
s大好厲害，都有辦法幾個步驟就解決，這個我都要花好多步
12/25 08:09, 9F
→
12/25 08:10, , 10F
驟做，樞紐、if再加上還是要人工處理，沒辦法一個公式就
12/25 08:10, 10F
→
12/25 08:10, , 11F
解決，先謝謝s大的指點，先來研究看看，感謝！！
12/25 08:10, 11F
※ 編輯: Fmajor (1.162.224.179), 12/25/2015 11:32:10 請問輔助A的A3公式中，{.....COUNTIF(A$1:A2,資料!$A$2:$A$100)...} 這段countif的功用是什麼呢？ 另外，請問陣列公式中的的countif是： COUNTIF(A$1:A2,資料!$A$2:$A$100) =COUNTIF({"";"A"},{"A";"A";"B";"B";"A";"A";"C"}) =COUNTIF({"";"A"},{"A"}) =1 請問1是這樣來的嗎？ ※ 編輯: Fmajor (1.162.224.179), 12/25/2015 12:28:37
→
12/25 13:04, , 12F
這裡countif的功用為取不重覆的值
12/25 13:04, 12F
→
12/25 13:05, , 13F
1如原po所述是這樣來的
12/25 13:05, 13F
→
12/25 13:08, , 14F
補充功用隨著A$1:A2下拉時範圍的擴大，可計數A$1:A2內是否
12/25 13:08, 14F
→
12/25 13:09, , 15F
已出現
12/25 13:09, 15F
→
12/25 13:09, , 16F
0為無出現，就可以抓出無重覆的值
12/25 13:09, 16F
→
12/25 16:18, , 17F
感謝s大的說明，我再研究一下，謝謝
12/25 16:18, 17F
s大，不好意思，陣列公式還在努力學習，有些地方還是不太了解，再麻煩指導 請問輔助A，A2儲存格中， {...IF(COUNTIF(A$1:A1,資料!$A$2:$A$100)=0,ROW($2:$100))...} 為什麼if只要寫value is true的條件，value is false的不用寫？ 請問countif中的的Criterial為什麼要陣列呢？ (這邊我看不太出來Criterial用陣列的用意) {.....COUNTIF(A$1:A2,資料!$A$2:$A$100)...} 例如輔助A的A4的公式， COUNTIF(A$1:A3,資料!$A$2:$A$100) =COUNTIF({"";"A";"B"},{"A";"A";"B";"B";"A";"A";"C"}) ~~~ =COUNTIF({"";"A";"B"},{"A"}) ~~~~~請問這邊是出現第一個A嗎？ =1 才會等於1嗎？(這樣拆解對嗎？) ※ 編輯: Fmajor (1.162.224.179), 12/25/2015 17:15:41 ※ 編輯: Fmajor (1.162.224.179), 12/25/2015 17:18:02
→
12/25 17:35, , 18F
false不寫，是因為這個範例應用上只會需要true所帶出的
12/25 17:35, 18F
→
12/25 17:36, , 19F
row()的部分，因此省略；value_if_false要寫也可以的
12/25 17:36, 19F
→
12/25 17:43, , 20F
用意是要以工作表"資料"內a2~a100的值，和公式所在的工作
12/25 17:43, 20F
→
12/25 17:43, , 21F
表內的a$1:a2來計數
12/25 17:43, 21F
→
12/25 17:47, , 22F
原po這樣拆解也可說是正確，因為資料!A2的"A"，帶入
12/25 17:47, 22F
→
12/25 17:48, , 23F
{"";"A";"B"}會帶出1
12/25 17:48, 23F
→
12/25 17:50, , 24F
但以公式來看會以a2~a100為criteria來計數範圍，假設a4的
12/25 17:50, 24F
→
12/25 17:51, , 25F
範圍為$a1:a3而產生{1;1;1;1;1;1;0;0;0.......}等99個元素
12/25 17:51, 25F
→
12/25 23:21, , 26F
不好意思，這段公式還在研究中，請問：
12/25 23:21, 26F
舉例：=COUNTIF(A:A,"A") 這個是計算A:A的範圍，A出現的次數， 請問： A2儲存格中： IF(COUNTIF(A$1:A1,資料!$A$2:$A$5)=0,ROW($2:$100)) =IF(COUNTIF({""},{"A";"A";"B";"B";"A";"A";"C"})=0,ROW($2:$100)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 計算A$1:A1範圍中，?????出現的次數 也就是countif的條件：{"A";"A";"B";"B";"A";"A";"C"}這是什麼意思呢？ 謝謝 ※ 編輯: Fmajor (1.162.226.139), 12/25/2015 23:22:23
→
12/25 23:23, , 27F
A2內計數會是{0;0;0;0;......}等99個元素
12/25 23:23, 27F
→
12/25 23:29, , 28F
{"A";"A";"B";"B";"A";"A";"C"} 稱一維陣列或垂直陣列
12/25 23:29, 28F
→
12/25 23:29, , 29F
行陣列
12/25 23:29, 29F
剛剛又想了一下， IF(COUNTIF(A$1:A1,資料!$A$2:$A$100)=0,ROW($2:$100)) =IF(COUNTIF({""},{"A";"A";"B";"B";"A";"A";"C";...})=0,ROW($2:$100)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 這是計算A$1:A1範圍中，A、B、C出現的次數（這樣說對嗎？） 另外，A4儲存格中 IF(COUNTIF(A$1:A3,資料!$A$2:$A$100)=0,ROW($2:$100)) =IF(COUNTIF({"";"A";"B"},{"A";"A";"B";"B";"A";"A";"C";...})=0,ROW($2:$100)) =IF(2=0,ROW($2:$100)) <--因為A有1個，B有1個，總共2個？ =IF(false,ROW($2:$100)) =false 請問這樣拆解過程對嗎？ ※ 編輯: Fmajor (1.162.226.139), 12/26/2015 01:22:00 ※ 編輯: Fmajor (1.162.226.139), 12/26/2015 01:23:43 ※ 編輯: Fmajor (1.162.226.139), 12/26/2015 01:25:40
→
12/26 01:25, , 30F
a$1:a1範圍中，A、B、C{"A";"A";"B";"B";"A";"A";"C";...}
12/26 01:25, 30F
→
12/26 01:26, , 31F
出現的次數，這方面如原po所述
12/26 01:26, 31F
→
12/26 01:27, , 32F
COUNTIF(A$1:A3,資料!$A$2:$A$100)會以資料!a2:a100為準則
12/26 01:27, 32F
→
12/26 01:29, , 33F
到範圍A$1:A3計數，而產生{1;1;1;1;1;1;0;0....}等99個元
12/26 01:29, 33F
→
12/26 01:30, , 34F
素，而不是2，請以資料!a2、資料!a3、資料!a4....
12/26 01:30, 34F
→
12/26 01:31, , 35F
到資料!a100到範圍a$1:a3內進行計數
12/26 01:31, 35F
→
12/26 01:33, , 36F
{1;1;1;1;1;1;0;0....}=0就為{false;false;false;false;
12/26 01:33, 36F
→
12/26 01:33, , 37F
false;false;true.......}，對應到row(2:100)時為
12/26 01:33, 37F
→
12/26 01:34, , 38F
{false;false;false;false;false;false;8;9;10...}
12/26 01:34, 38F
→
12/26 01:35, , 39F
small({false;false...;8;9},1)回傳為8，index(資料!a:a,8
12/26 01:35, 39F
→
12/26 01:36, , 40F
)時，回傳C
12/26 01:36, 40F
→
12/26 02:01, , 41F
如果覺得陣列公式不太順的話，也可用輔助欄的方式
12/26 02:01, 41F
→
12/26 02:02, , 42F
12/26 02:02, 42F
→
12/26 14:21, , 43F
感謝s大這麼晚還花時間講解，s大寫的陣列公式真的很厲害！
12/26 14:21, 43F
→
12/26 14:22, , 44F
之前有研究過，似懂非懂的，剛好趁這個機會再好好研究一下
12/26 14:22, 44F
→
12/26 14:23, , 45F
陣列公式用的好，真的可以省好多步驟，希望有一天可以像
12/26 14:23, 45F
→
12/26 14:23, , 46F
s大這樣囉～
12/26 14:23, 46F
→
12/26 14:25, , 47F
s大的這個檔案，花三天時間研究，覺得很值得，對陣列公式
12/26 14:25, 47F
→
12/26 14:26, , 48F
、還有一些公式組合的用法都感覺學到很多，再次謝謝s大詳
12/26 14:26, 48F
→
12/26 14:26, , 49F
細的講解喲～大致上的使用概念應該瞭解了
12/26 14:26, 49F
※ 編輯: Fmajor (114.25.108.157), 12/26/2015 14:27:40
→
12/26 16:18, , 50F
^^
12/26 16:18, 50F
‣ 更多 Fmajor 的文章
文章代碼(AID): #1MU_7Onu (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 Fmajor 的文章
文章代碼(AID): #1MU_7Onu (Office)