ಠ_ಠ
@anonymous
偷偷說
Fri, Dec 30, 2022 6:25 PM
Wed, Apr 19, 2023 7:33 PM
190
24
【長駐】
EXCEL台
2023年集合串
✿ 提供各種Excel問題疑難排解
✿ 公式、CSE、巨集/VBA
✿ 不支援硬體或設備問題範疇外
✿
更多分享、過往回顧
✿ 賣藝求活
萬事問噗浪
試算表
函數
google試算表
轉蛋
ಠ_ಠ
Fri, Dec 30, 2022 6:26 PM
【前言︰近況】
從2021年開始Excel台，透過解答大家的問題我學了很多東西，也感謝各位無形與有形的支持。
與此同時，我也決定弄一個Buy me a coffee的頁面，把過往的例子歸檔外，未來會繼續分享一些
Excel台以外的內容
，大家可以視乎需要訂閱。（有免費內容）
☕
Excel On Demand
☕
就算不訂閱，未來日子也會繼續在這台為大家服務。
ಠ_ಠ
Fri, Dec 30, 2022 6:26 PM
目前想分享的到此以上。
在此祝願2023年，大家可以馴服Excel，
把省下來的時間都拿來摸魚。
----------------------------------
以下開放新一年度Excel提問
moon3492
講
Mon, Jan 16, 2023 1:56 AM
不好意思想問下噗主，自己有大量xlsm檔案想轉成xlsx格式，但因為是公司資料無法上傳線上網站
用google找過指令，目前找不到完全一樣格式（只找到xls轉xlsx的），但憑自己對VBA的87%理解能力無法自助手動調整，想問下噗主是否有什麼建議
ಠ_ಠ
Mon, Jan 16, 2023 4:29 AM
moon3492: 打開一個空白的excel, 把這個貼進去再運行看看行不行？記得改掉folder path
不好意思有點粗疏請見諒
moon3492
講
Mon, Jan 16, 2023 6:07 AM
ಠ_ಠ: 哇哇不好意思麻煩噗主直接幫忙想指令了，謝謝噗主的幫忙～剛剛試了有運作，有成功動作了.....！！不過輸出後的xlsx檔案好像會顯示為格式損毀無法開啟
ಠ_ಠ
Mon, Jan 16, 2023 6:18 AM
moon3492: 嗚啊抱歉剛剛沒有檢查清楚
看看這個應該可以，輸入跟輸出同位置
moon3492
講
Mon, Jan 16, 2023 9:37 AM
Mon, Jan 16, 2023 9:37 AM
沒問題了，謝謝您！！
另外想回報下，不知道是不是因為巨集設定打架的關係，有一部分檔案會跳出這樣的警告
依自己87%的理解應該是想要授權，所以在巨集中心試著每個模式都按按看發現這樣的設定後檔案就會跳得出來，不過在那之前會跑一個亂碼框框，然後點ok他就會一個個跳檔案
不確定什麼情況，先FB一下（？
ಠ_ಠ
Mon, Jan 16, 2023 11:28 AM
moon3492: 你的做法正確，謝謝回報~
亂碼框框我也不確定，如果之後還有這種情況出現需要解決，那就要看看是電腦、文件、還是巨集問題了(只出現某個情況還是在指定情況都會出現等)
horse6126
Tue, Feb 7, 2023 11:18 AM
您好，最近剛換新工作遇到一些瓶頸，本身excel理解不多，想請問如果要將多分店（60幾個分開的檔案）的下訂量彙整成一張，之後又需要一筆筆挑出來登打進pos，主要是他的版本停在2003版找的資料跟他上面都不太一樣，請問這有方法能處理嗎
ಠ_ಠ
Tue, Feb 7, 2023 11:34 AM
horse6126: 那找資料的時候可以加上excel 2003來搜尋
先看看這兩個可不可行，不行再看看是哪裡問題，我們再研究看看吧
How to Merge Workbooks in Excel 2003
How to combine Multiple Excel file into Single Excel...
horse6126
Tue, Feb 7, 2023 11:40 AM
好的，謝謝！
walnut775
Thu, Feb 9, 2023 9:09 AM
不好意思想問一下噗主，我目前在做的比較資料擷取其中一小段
例如我紅框處的那三行資料，我只想留下奧迪-A6L那行的資料，想問有沒有自動比較的方式幫我把另外兩行不需要的整行刪除?
比較的依據看文字後面的比例（那個欄位叫信任值），奧迪-A6L為這三筆重複資料信任值最高者，只留最高者就可以了
ಠ_ಠ
Thu, Feb 9, 2023 9:52 AM
Thu, Feb 9, 2023 1:36 PM
walnut775: 先把信任值的內容換成數字，用資料剖析就做得到，要是不知道方法可以提出一下。
用條件化格式，如果是365版可以選所有欄，公式寫=maxifs($C:$C,$A:$A,$A1)<>$C1
如果不是365，便選數據的範圍，公式寫=max (if($A$2:$A$11=$A2,$C$2:$C$11))<>$C2 留意max和括號之間有空間
之後格式可以改背景顏色/文字顏色，或者儲存格格式改””，後者的方法只會隱藏信任值
如果要刪除，需要為數據加上篩選，在篩選條件裡用色彩篩選，選擇被你用來隱藏的顏色（如白色），篩下來的數據全行刪掉就好
ಠ_ಠ
Thu, Feb 9, 2023 1:38 PM
Thu, Feb 9, 2023 1:39 PM
walnut775: 追記︰我回到家了所以可以補充一下，關於資料剖析的基本用法可以參考
這一篇
上記出來的效果大概長這樣
篩選裡選這個
之後全行刪除
ಠ_ಠ
Thu, Feb 9, 2023 1:43 PM
walnut775: 上記是考慮到你可能希望留下其餘2項資料的前提下設計，如果是希望用最快的方式刪除，那就不需要用格式化條件，只需要在旁邊的空白欄把公式填進去→按CSE→篩選找true→刪掉
這樣會比較快，另外解法是以資料的BQM6686那行做參照，但如果不能這樣判斷是要比較的數據，麻煩再說明一下
uranus858
Thu, Feb 16, 2023 3:55 PM
請問噗主，這個問題有解嗎？
ಠ_ಠ - Excel 解惑，要篩選表單時不知為何突然出現「固定的物件將會被移動」視窗，然後就一直跳針當...
ಠ_ಠ
Thu, Feb 16, 2023 4:50 PM
uranus858: 呃…我沒有遇過這類型的視窗，所以我只能跟你一起google
當機聽起來是電腦運算不過來才會發生，所以有可能是因為下面的原因才會這樣？
也許要看看那個表單是怎樣設計才有多點線索
2010 office excel蒒選時顯示固定的物件將會被移動,要怎麼解決
Excel固定物件將被移動對話方塊困擾很多上班族碰到就會頭大的問題 - ITW01
horse6126
Thu, Feb 16, 2023 5:19 PM
噗主你好，後來軟體升級就能使用power query的功能了！
不過power query檔案合併好像只能把標頭設在欄，請問有用列當標頭橫向合併的方法嗎？
ಠ_ಠ
Thu, Feb 16, 2023 5:28 PM
horse6126: 先恭喜你！然後因為個人行程關係，最快要下週才能回覆，先說一下抱歉
human624
Fri, Feb 17, 2023 6:12 AM
請問有辦法只依據儲存格中的部分內容進行排序嗎
手上的資料,時間儲存格的格式是日期+時間(例:2023-01-04 12:10:04)
不知道有辦法在排序時只計日期,不計時間
或是統一清除儲存格的資料,只保留日期,清除時間嗎?
ಠ_ಠ
Sat, Feb 18, 2023 6:01 AM
(不好意思跳過小馬) human624: 這個剛好上星期處理數據時剛好有研究，可以看看這個行不行
三步從日期+時間的排序改為只用日期的排序 — 從社畜變成Excel畜
ಠ_ಠ
Sat, Feb 18, 2023 6:12 AM
Sat, Feb 18, 2023 6:13 AM
horse6126: 有點不太懂…是像把這樣的內容︰
變成這樣子︰
然後用B欄(A/B/C/D)的部分合併的意思嗎？(所以A就是854+901=1755)
horse6126
Sat, Feb 18, 2023 7:04 AM
謝謝噗主回答，檔案格式都是Abc在最左、數字在右
本來是想問能不能ABC那欄固定剩下數字往右延伸
horse6126
Sat, Feb 18, 2023 7:06 AM
就是一方面需要統計總量，一方面需要把資料展開
但後來我是直接用power query 直接拉向噗主那樣，直接用樞紐算，所以算是找到方法了（
ಠ_ಠ
Sat, Feb 18, 2023 7:26 AM
horse6126: 整理數據的話個人是覺得往下延伸是比較容易處理的(剛好最近工作處理別的資料就是這樣子)，不過如果是希望往右延伸的話，可以考慮用VBA處理，大致流程就是「打開每個要合併的檔→找出跟合併的主檔案一樣的項目→把數據貼在主檔案最右側的欄」這樣，當然前提是要確定每個檔案用來參照的項目和位址都是一樣
horse6126
Sat, Feb 18, 2023 8:33 AM
ಠ_ಠ: 好的、謝謝建議！
human624
Sun, Feb 19, 2023 3:03 AM
ಠ_ಠ: 這樣子就解決了,謝謝!
human624
Sun, Feb 19, 2023 3:10 AM
不好意思,希望再請教一下:
原本數據是登記進項發票與預計付款日期的表單
(實際上有更多欄位)
希望呈現出每個預計付款日期中,各類型的金額與該日期加總金額
只有一張工作表的時候可以用樞紐分析處理
可是需要加總多張不同月份,相同格式的工作表時
使用"多重彙總資料範圍"後,預計付款日期,類型,金額等欄位全都跑到"欄";日期,金額,類型名稱等資料全都跑到"值"
沒辦法分別拉到欄,列,值 請問多張工作表應該如何彙整呢?
uranus858
Mon, Feb 20, 2023 1:09 PM
謝謝噗主回應！我是固定物件被移動的那個
結果照網路上的做也沒用orz
但我直接把活頁簿表格全選複製貼上在新活頁簿，問題就解決了
uranus858
Mon, Feb 20, 2023 1:14 PM
然後想問一個新問題，我要怎麼寫“不指定分頁”的語法？
我想在同一檔案裡的很多分頁做相同的處理（在每個分頁固定地方插入儲存格），可是錄製巨集後都只能在最初選的分頁處理一直跳回去那頁...
問題是不是出在選擇分頁那行？我只會簡單的錄製巨集 不知道怎麼辦
ಠ_ಠ
Mon, Feb 20, 2023 3:53 PM
human624: 我沒有用「多重彙總資料範圍」，用你附的數據來弄樞紐的話，可以拉成這樣︰
ಠ_ಠ
Mon, Feb 20, 2023 4:03 PM
human624: 我把幾個檔案加進來的時候表格大概是長這樣的
把資料改一改再拉樞紐是這樣
如果拉不到的話，能不能把資料併在同一張工作表才處理？
因為我覺得自己跟human處理資料的方法好像有點不一樣…
ಠ_ಠ
Mon, Feb 20, 2023 4:19 PM
uranus858: 首先恭喜你解決原本的問題
如果是換個檔就能解決的話，有可能是原檔有沒察覺到的物件或者副檔名的問題
我不太確定你的CODE的用意…是把工作表1的A20:G31複製→貼到工作表2和以後的頁面的A20:G21(應該是G31?)嗎？(A22:G31原有的內容也會被覆蓋可以嗎？)
如果你必須用巨集，這個你需要用到For Loop，for loop的概念可以先參考
這一篇
裡的流程圖
還有
這一篇
的做法
ಠ_ಠ
Mon, Feb 20, 2023 4:19 PM
uranus858: 如果不是經常用到的動作的話，我會建議直接在工作表1 COPY→把工作表2~5(假設)複選→在工作表2的A20直接貼上/ENTER (如果要把原資料下移的話就是按CTRL+加號) (當然這部分也可以用巨集錄製，視乎情況再用FOR LOOP修改)
litchi6868
Thu, Feb 23, 2023 6:21 AM
你好，想要諮詢「刪除重複項」這個選項裡面，因為我不希望他改變整個表格，有沒有辦法把重複的項目不是刪除而是改成空白或者是改為鍵入"重複項目"這樣
ಠ_ಠ
Thu, Feb 23, 2023 6:52 AM
litchi6868: 這樣我會選用格式化條件吧
=and(countif($L$1:$L1,$L1)<>1,isblank(L1)=false)
ಠ_ಠ
Thu, Feb 23, 2023 6:55 AM
litchi6868: 上班中寫得比較簡單，顏色調白字就像空白了（如果數字就改格式做雙引號）
如果要輸出「重複項目」就把算式放左空白欄
有不懂的部分請提出，回家後再補充
litchi6868
Thu, Feb 23, 2023 7:18 AM
Thu, Feb 23, 2023 7:23 AM
感謝，我大概試了一下已經成功(圖一)
如果想要再進一步把第二個重複項，例如說B、E的Email.刪掉但留下姓名欄的話，該如何處理比較好(圖二)
圖一
圖二
ಠ_ಠ
Thu, Feb 23, 2023 7:30 AM
litchi6868: 如果我猜錯了先說一下不好意思
但圖一看起來不像用了公式 而是普通的「重複的值」提示？
因為如果是我剛剛提供的公式，應該只會標示出重複了的內容而不是把第一項的內容都一併標示才對
ಠ_ಠ
Thu, Feb 23, 2023 7:31 AM
如果照我的圖那樣只標示第二項，用篩選把符合那個標示的條件挑出來就可以清空內容了
litchi6868
Thu, Feb 23, 2023 7:58 AM
Thu, Feb 23, 2023 7:59 AM
圖一旁邊的I列的False、Ture是你提供的公式，大概是我改錯了?
ಠ_ಠ
Thu, Feb 23, 2023 8:15 AM
litchi6868: 要把範圍改成「$C$1:$C1」
這樣才會順著數數而不是把整欄來計算
litchi6868
Thu, Feb 23, 2023 9:12 AM
了解，看起來已經成功了
我紅色是用常用項的條件儲存格樣式設定
ಠ_ಠ
Thu, Feb 23, 2023 11:12 AM
litchi6868: 了解！如果後續還有需要可以隨時再問
milk8484
Wed, Mar 1, 2023 9:52 AM
不好意思我想請問一下關於身分證驗證的問題(僅針對碼數跟第2碼是否正確)
我的公式寫這樣
=IFS(LEN(A4)<>10,"碼數有誤",AND(MID(A4,2,1)<>1,MID(A4,2,1)<>2),"第2碼有誤")
但最後得出卻是像K123456789 第2碼有誤的結果
ಠ_ಠ
Wed, Mar 1, 2023 1:03 PM
milk8484: 因為mid抽出來的是「字串」(string)不是數值(value/integer...)，所以要把後面的不等式改成「"1"/"2"」(加上引號代表這個「1」不是數值而是字串
這樣你的公式出來應該是#n/a，或許你可以考慮在外面多加一個iferror/ifna
milk8484
Thu, Mar 2, 2023 12:19 AM
感謝大大，我換成=IFS(LEN(A2)<>10,"碼數有誤",AND(MID(A2,2,1)<>"2",MID(A2,2,1)<>"1"),"第2碼有誤",OR(MID(A2,2,1)="2",MID(A2,2,1)="1"),"正確")後就成功了
ಠ_ಠ
Thu, Mar 2, 2023 12:36 AM
milk8484: 了解！
fairy9261
Sat, Mar 4, 2023 1:01 PM
噗主好！
想請問如要計算固定數字*變數
例如A3＊B1、A3＊C1、A3＊D1
用來比較之間的差額且小數點後自動進位
目前都是一個一個輸入=ROUNDUP
輸入了=ROUNDUP(A3＊B1)接續往C3拉的話會變成(B3＊C1)，而我需要的是(A3＊C1)
有試著用$的符號固定住A3，但不知道哪裡出了問題導致ERROR
想知道有沒有可以固定住A3並且可以整排自動填入的函數
ಠ_ಠ
Sat, Mar 4, 2023 1:18 PM
fairy9261: 嗯…抱歉我沒法重現你的問題
能確認你$是鎖在跟下圖一樣的位置嗎？鎖的快捷鍵是F4
如果是快速填充的話，先在B3寫好公式，可以像下圖選取範圍，在不是編輯模式下按CTRL+ENTER或者按CTRL+R和CTRL+D填充就可以了
fairy9261
Sat, Mar 4, 2023 4:32 PM
ಠ_ಠ: 天吶，對！就是噗主的這樣！
剛才最多最多只研究到先調整數字格式為0後再在B3建立公式=$A$3*B1快速填充到C3、D3
換到B4又要重新手動建立公式=$A$4*B1
ಠ_ಠ
Sat, Mar 4, 2023 4:40 PM
fairy9261: 是的，像這種把最左欄X最頂列，只需要各自鎖一邊就可以了，像A3鎖成$A$3而且不鎖B1的話就會在填充的過程跳範圍~
fairy9261
Sat, Mar 4, 2023 4:55 PM
ಠ_ಠ: 了解，超級感謝噗主！
剛又研究了一下第二種方法（噗主教的融合家人教的）
B3：D8設定數字格式為0
B3設定公式=$A3*B$1再快速填充
一樣也可行！
fairy9261
Sat, Mar 4, 2023 4:57 PM
$A3就是鎖定A列，數字自動變動
B$1就是鎖定B排，數字自動變動
fairy9261
Sat, Mar 4, 2023 4:58 PM
謝謝噗主，剛才跟家人研究超久，明天再用家人懂的方式跟他分享
ಠ_ಠ
Sat, Mar 4, 2023 5:02 PM
fairy9261: 是的
改儲存格格式是代替在公式上寫roundup，以結果來說兩邊都可以，如果是不擅長公式的話→改儲存格格式比較好
不過儲存格格式很容易被忽略設定，這樣的話用公式會比較安全(防止有遺漏)
ANYWAY, 能幫忙就好了
litchi6868
Mon, Mar 6, 2023 12:59 PM
想要諮詢一下，檔案應該是csv轉xlsx的，在每個EMAIL值錢面會有個分號，但是複製該符號在尋找與取代裡面沒法找到並取代，為了避免複製EMAIL寄送的時候出錯，請問該怎麼處理才能方便消除分號
ಠ_ಠ
Mon, Mar 6, 2023 1:12 PM
litchi6868: 那個分號是EXCEL內為儲存格的內容定義為字串的一個標記，它並沒有實際影響你的內容
跟你在儲存格打「=」為了不讓它被定義為公式時，在前方打「'=」是同一道理
我做了一個GIF可以參考一下
litchi6868
Mon, Mar 6, 2023 1:32 PM
ಠ_ಠ: 感謝釋疑
pirate5981
Tue, Mar 28, 2023 2:40 AM
噗主可以問VLOOKUP嗎
ಠ_ಠ
Tue, Mar 28, 2023 4:02 AM
pirate5981: 可以哦//
pirate5981
Tue, Mar 28, 2023 5:06 AM
Tue, Mar 28, 2023 5:07 AM
ಠ_ಠ: 想請問一下要用表1的a和b
要比對表2
的ab，回填表2的c到表1
這個應該怎麼操作呢？
（a欄是訂單編號，b是條碼會有很多個重覆的）
不知道這樣說明能否理解
雖然我後來手動填完了
ಠ_ಠ
Tue, Mar 28, 2023 5:38 AM
pirate5981: 表1的a和b兩項條件來比對表2的a和b項對不對？
這樣會有4個可能發生的結果，會有不同的輸出嗎？
1）ab兩項全對
2）a對b錯
3）a錯b對
4）ab兩項皆非
我還沒回家沒法驗證，但我記得vlookup不是能驗證複項的函數
如果只要1和4的結果，那可以把表1和表2的ab合併（成新欄，或者空白欄，驗證後才刪掉）才vlookup
Pirate 必須用vlookup嗎？還是別的函數也可以？
如果想再無腦一點，也可以用VBA解決，如果以後也會長期做這個的話，VBA會比較一了百了
pirate5981
Tue, Mar 28, 2023 5:41 AM
哦哦 所以這個VLOOKUP 沒辦法直接回填表2的c嗎（原來我搞錯重點？？
）
看來太難了
pirate5981
Tue, Mar 28, 2023 5:42 AM
還是感謝台主回答
ಠ_ಠ
Tue, Mar 28, 2023 5:46 AM
pirate5981: 我回家會再做給你看（兩小時左右）
只是想先行確認一下pirate 想要的結果是要多仔細（1-4）
如果是可以接受中間用空白欄+只要1和4的話是可以vlookup
如果有其他要求，也考慮到日後會再重複完成的話，我會提議VBA
難不難是依據pirate 實際需要而已，不是辦不到的//
ಠ_ಠ
Tue, Mar 28, 2023 10:05 AM
pirate5981: 先把兩邊的表都用一個空白欄來連結(圖例C欄)，如果你目標是用VLOOKUP找到D欄的結果，那空白欄就要放在D欄之前的位置(因為VLOOKUP是左至右數)
如果因為表格設計而不能在中間插入，那建議用INDEX/MATCH
之後在表2要輸出的位置(I欄)打公式，就可以了
之後把結果貼上值，就可以把用來VLOOKUP用的兩個欄刪掉
這部分是常見和簡單處理複數條件的VLOOKUP做法，缺點就是要多建一個欄來處理資料
ಠ_ಠ
Tue, Mar 28, 2023 10:12 AM
如果要追求極致，用一個公式寫完的話，可以考慮用SUMPRODUCT和INDEX找出來︰
不過要注意一點是，要小心只有純數字的儲存格是用「字串」還是「數字」的方式儲存，像上圖例子的黃色標示，儲存格左上方的綠色小旗幟就是代表它不是數字而是文字
這一點是會影響函數運作的 (PIRATE的表2B就是文字, 1A是數字)
ಠ_ಠ
Tue, Mar 28, 2023 10:12 AM
我寫得比較簡短，要是有看不懂的地方可以跟我說
pirate5981
Tue, Mar 28, 2023 10:18 AM
ಠ_ಠ: 哦哦哦哦雖然我看不太懂（單純我excel函數太不熟了和您沒有關係）
等於要把表1的ab先加總，表2再輸入函數（？
）但總之感覺很厲害還是謝謝台主
ಠ_ಠ
Tue, Mar 28, 2023 10:24 AM
pirate5981: 因為數值才需要加總，你的CASE是把A和B的「文字」
結合
比較正確，所以橙黃色的C和H欄就是用「&」來把兩邊的文字結合在一起
之後就照附圖那樣打公式，把對應的範圍改好就可以了
如果改不好的話可以再發圖上來問沒關係~
pirate5981
Tue, Mar 28, 2023 1:29 PM
ಠ_ಠ: 我明天上班有空試試看
謝謝噗主！
pirate2699
Wed, Apr 19, 2023 10:13 AM
您好，不好意思想請問一下
ಠ_ಠ - [問]EXCEL 如何回傳所有符合條件的對應值 圖一，出勤表 圖二，有薪假確認表 想要根據有...
謝謝您
cock8506
Wed, Apr 19, 2023 10:18 AM
不好意思，想請問一下！
ಠ_ಠ - excel問題，請各位旅人幫幫忙Orz 如果希望指定的人在幾月幾號上班，當天就打勾，怎麼寫函...
cola7834
Wed, Apr 19, 2023 2:14 PM
您好不好意思打擾了！
ಠ_ಠ - #萬事問噗浪 想求助各位excel大神旅人們 這兩道題到底怎麼解 嘗試了各種方法都不對 完全...
想請教一些date跟time的問題⋯
先謝謝了
cola7834
Wed, Apr 19, 2023 2:54 PM
卡住ㄉ點是負數ㄉ處理方法
本來想用time、hour等等去把時間拆開 計算後再組起來 但巴黎比台灣慢7小時 但=time(hour()...後面只能加不能減
後來想用serial number 算但一樣碰到負數ㄉ問題
ಠ_ಠ
Wed, Apr 19, 2023 6:07 PM
Wed, Apr 19, 2023 6:09 PM
不好意思晚了回覆，
pirate2699: 請參考這篇，如果有不清楚的地方可以再跟我說。
[期間限定] 根據出勤表生成員工對應休假紀錄 — 從社畜變成Excel畜
這跟你的要求無關，但如果pirate需要按員工輸出紀錄的話，是不是考慮用VBA比較好？如果用VBA的話就不用寫這麼長的公式，可以一鍵生成紀錄。如果有需要的話我們可以再討論。
cock8506: 因為順著處理，允許我沿用這份名單單來作例子。
[期間限定] 根據列出日期，輸出出勤表 — 從社畜變成Excel畜
不知道你是不是想要這個效果，如果不對，我們可以繼續討論。
抱歉我沒有用到原噗HAM提到的3個函數。
另外，如果你要處理很多員工的資料，這樣在A1逐個輸入不會很麻煩嗎…？這個部分也是可以用VBA來處理的，有需要我們也可以討論一下。
ಠ_ಠ
Wed, Apr 19, 2023 6:45 PM
Wed, Apr 19, 2023 6:48 PM
(不小心按到送出就分開兩篇回應了)
cola7834: 問題一的部分可以看這篇
[期間限定] 怎樣算時差 — 從社畜變成Excel畜
因為看起來像家課，所以……我不知道你的課程是不是期望你這樣解。
問題二，剛巧我以前工作要計算，所以請直接參考這篇吧
計算每月、每週的第一天和最後一天 — 從社畜變成Excel畜
cock8506
Wed, Apr 19, 2023 8:10 PM
ಠ_ಠ: 差不多100多人吧......因為資料太分散了所以只能一個一個打
原本其他同事是想要直接用勾的，但是我覺得想再簡化一點......
cock8506
Wed, Apr 19, 2023 8:12 PM
ಠ_ಠ: 想再問一個問題，那如果想要表格上顯示更多資料要怎麼寫，比如小明4/1上了早班，4/2上了午班，4/3早班跟午班一起上，這種有辦法顯示在表格中嗎？
ಠ_ಠ
Wed, Apr 19, 2023 8:18 PM
cock8506:
早安/雖然我要睡了，待會要上班
所以是大約100位員工各自提供能上班的日子→錄入到日曆→再手動微調(像某些日子需要強制某些員工上班)……之類？
這樣我會想到1) 可以用GOOGLE FORM讓大家各自填，COCK再拿數據用公式/VBA錄入日曆比較快 2)如果可以讓每個員工接觸檔案，就直接寫個vba的user form讓他們自行錄入 3) 類似1/2的形式，整合100人的內容(格式一致)，用vba讀取數據錄入
基本上重覆動作的東西還是用巨集處理會比較省心，我也同意用勾的真的……不太好……
ಠ_ಠ
Wed, Apr 19, 2023 8:20 PM
cock8506: 追加問題那個，如果欄數是4/1早, 4/1午, 4/1全這樣拆開的話，那就在sumproduct裡追加一個條件相乘就好
不建議用合併儲存格的方式顯示
這一部分我今天上班摸魚的時候再寫一寫，如果你有期望的格式提供的話，我可以到時嘗試配合
cola7834
Thu, Apr 20, 2023 12:32 AM
ಠ_ಠ:
謝謝！我去試試
感謝噗主指點
ಠ_ಠ
Thu, Apr 20, 2023 2:58 AM
cock8506: 早午安。追加了2A和2B的解法，感覺上應該是2B比較符合實際操作
[期間限定公開] [期間限定] 根據列出日期，輸出出勤表-2A — 從社畜變成Excel畜
[期間限定公開] [期間限定] 根據列出日期，輸出出勤表-2B — 從社畜變成Excel畜
cock8506
Fri, Apr 21, 2023 8:08 AM
ಠ_ಠ: 好的，真的非常感謝大佬
我找時間試試去！
ಠ_ಠ
Fri, Apr 21, 2023 9:10 AM
cock8506: 不客氣 有需要可以隨時再來討論～
cock8506
Wed, Apr 26, 2023 1:26 AM
不好意思又來打擾了
想問個問題
如果想要把excel的日期改成自己想要的格式怎麼調
像是2023/3/3改成1120303這樣
我原本是用取代的，但是幾百筆用取代的也很久，而且常常不小心取代成錯的資料Orz
ಠ_ಠ
Wed, Apr 26, 2023 1:38 AM
cock8506: 不用不好意思～無任歡迎發問哦
儲存格格式（快捷鍵Ctrl +1)可以解決你的問題嗎？
可以看一下這個
從社畜變成Excel畜 (@excel_od) • Instagram photos and video...
如果沒設定到可以加個擴充包
【EXCEL】無法顯示中華民國曆？microsoft-windows-internationalfeat...
ಠ_ಠ
Wed, Apr 26, 2023 2:58 AM
cock8506: 如果沒法下載擴充包/電腦沒有民國曆，可以考慮在新欄用公式轉換：
=text(year(A1)-1911,”0”)&text(A1,”MMDD”)
不知道這樣可以嗎？如果哪裡寫得不清楚，請跟我說一下，我再截圖
pirate2699
Wed, Apr 26, 2023 2:58 AM
您號，我是詢問
這篇
的旅人，謝謝您的回答
這邊卡在顯示有休紀錄那邊的函數，CSE之後會出現無法計算的警告框，但檢查後好像也沒有哪裡有遺漏，可以請您幫我看看嗎?
G4的函數內容=OFFSET(出勤表!$A$1,1,LARGE(IF((出勤表!$A:$A=有休確認!$B$2)*(出勤表!B:XFD="有"),COLUMN(出勤表!$1:$1),0),ROW()-3)-1)
ಠ_ಠ
Wed, Apr 26, 2023 3:50 AM
pirate2699: 無法計算的警告框會是長這樣子的嗎？
那是因為這個公式如果套用總範圍(即A:A而不是A1:A30)，計算量太龐大，一般會處理不來
所以範例裡我只可以指定範圍才能處理
我想了一下，考慮到你的範圍去到XFD，覺得旅人可以考慮用VBA處理會比較快，如果你的工作/這個檔案允許你修改副檔名嗎？
目前先把公式再修正一下，原來旅人提供的公式，Offset應該是0不是1(即是前面$A$1之後的變數)，
第二部分是要把COLUMN內的公式補上欄位，即是$1:$1→$B$1:$XFD$1
但由於總範圍的處理太龐大，所以我改成這樣，旅人可以試一試︰
ಠ_ಠ
Wed, Apr 26, 2023 3:50 AM
=OFFSET(出勤表!$A$1,0,LARGE(IF((INDIRECT("出勤表!$A$1:$A$"&COUNTA(出勤表!$A:$A))=有休確認!$B$2)*(INDIRECT("出勤表!$B$1:$XFD$"&COUNTA(出勤表!$A:$A))="有"),COLUMN(出勤表!$B$1:$XFD$1),0),ROW()-3)-1)
中間混了Indirect和Counta，是用來查收出勤表使用的最大行數=你的範圍，
用A行查找是因為A行每一個儲存格都有輸入(換言之，如果中間有空格，就要在COUNTA之上再加空格數)
注意，後面用了Indirect的部分，如果欄數追加(XFD以外還有追加)，就要手動修改
旅人看看這樣的調整可以接受嗎？
pirate2699
Wed, Apr 26, 2023 4:01 AM
ಠ_ಠ: 謝謝噗主！是的警告框是長那樣
這邊不太會操作VBA所以有點擔心，晚點試試看噗主提供的修正，然後再來向您回報
ಠ_ಠ
Wed, Apr 26, 2023 8:56 AM
pirate2699: 主要是考慮檔案如果多於一人使用、或者預想需要輸出多名員工的有休紀錄的話，VBA可以防止有人誤修改公式，也可以做到批量輸出的動作(如︰按鍵後把每個員工都輸出做獨立的工作表→甚至輸出成獨立XLSX檔案) (因為這次用到的公式比較長，不諳EXCEL的人在日後修改可能容易出錯)
可以參考一下這一篇
【公開】VBA101︰為甚麼要用VBA？普通人也能學得會嗎？ — 從社畜變成Excel畜
視乎實際想做到的效果，寫出來的巨集可難可易，如果只是我上述說的內容的話，個人覺得滿簡單和容易操作的
順帶一提，我剛剛算了一下，XFD欄是16384~44年的日期量...??
pirate2699
Mon, May 1, 2023 8:46 AM
噗主您好，這邊在套用您上述的公式之後已解決問題，唯一的缺點不知道是不是因為資料量大，每個動作都要等個2秒左右才會跑出來。
1.這邊有另一個問題，我在出勤記錄前面加了幾欄，想來計算指定月份的有休或公休等等的日數，不知道這邊的公式該怎麼寫
2.關於VBA的部分，這邊的電腦環境允許使用，但自己也只會錄製巨集的程度...。這個檔案是我一人在用，且每次只查找單一員工(主要是要看還剩幾天有薪假能用而已)，所以這邊的VBA可能比較不需要。
這邊會想用VBA做另一個部份的東西，就是經由出勤紀錄可以一鍵生成另一個固定格式的EXCEL文件，但我想這個部分會複雜很多，如果噗主有意願再來討論就好，謝謝
ಠ_ಠ
Mon, May 1, 2023 9:21 AM
pirate2699: 嗯這個2秒會是我為甚麼偏好VBA的原因 (應該會有為了減少使用記憶體的寫法但我會偏好舒適圈，所以沒有鑽研過)
如果平時實際使用的話，我會把檔案設做手動計算→使用時才按f9讓它運算一次
1. 我想先確認一下，B1的儲存格是哪一種？
「2023年4月(文字)」「2023/04/01 但把儲存格設成"YYYY年M月」如果是前者，記得要改成後者那一種只有顯示日期/數值而不是文字
這個用COUNTIFS應該就可以用了，
今天是休假所以比較簡略，有疑惑可以再提出，我上班的時候再回覆
pirate2699
Mon, May 1, 2023 9:32 AM
ಠ_ಠ: B1的格式是日期，我剛剛也想用countifs，但是技能太低寫不出來，謝謝噗主，這邊試試看來發問
ಠ_ಠ
Mon, May 1, 2023 9:46 AM
pirate2699: 至於 2. 推測第一部分是從出勤紀錄找對應名字和相應的日期和內容錄入P~AT欄，第二部分是根據紀錄再計算相應的出勤和休假數
如果是這樣直接的做法的話，VBA應該不會很難，還有一些細項需要確認，如果可以貼上檔案，還有能詳述實際做法嗎？
如果不方便在這裡說下去，請考慮以下方法連繫︰
1) 噗幣小房間
2)
BMC Requests
ಠ_ಠ
Mon, May 1, 2023 9:52 AM
pirate2699: 初步想法是把模版放在私人用的「出勤紀錄」EXCEL裡，另外多建一個工作表，用來設定檔案路徑
一鍵生成的時候直接套用模版，再把那張工作表輸出成檔
ant9885
Wed, May 3, 2023 5:44 AM
您好打擾了 想請問有關巨集的問題><
之前錄製完的巨集都會存放在personal 然後以隱藏式窗方式存在
但最近發現我只要關閉excel personal這個檔案也會跟著被關掉
下次再打開excel想使用巨集時 都需要另外再點開personal才可以使用巨集QQ
請問要如何設定 不用每次都要另外打開personal?
ಠ_ಠ
Wed, May 3, 2023 6:21 AM
ant9885: 聽起來好像在質疑螞蟻但你確定你關掉別的EXCEL檔案時是CTRL+F4那種只關掉一個視窗而不是ALT+F4的關掉所有視窗嗎？
目前手上的excel版本沒法重視所以不能肯定回覆。不能把對應的巨集放在相應的檔案裡面嗎？
如果"personal"是用來中央管理巨集的檔案，或許考慮把personal的巨集設定在快速存取工具列……？這樣就算關掉了檔案也可能按鍵打開→運行巨集……？
pirate2699
Mon, May 8, 2023 9:17 AM
噗主您好，我又來了，這次是要問跟之前不相關的問題
這邊做了一個單字測驗的頁面(?)
A~H是不熟的單字內容，B欄是rand函數，C欄是單字書內該單字的號碼
K4:N18是會隨機出現的單字測驗
L4==index ($D$2:$D$116,rank(B2,$B$2:$B$116))
K.M.N欄則是用vlookup代出
1.想要能依照自己的需求設定隨機出現的範圍，例如想著重複習序號150~300的單字，K4:N18就會依照設定跑出對應隨機單字
2.測驗完單字我會依照熟悉程度標上good/soso/bad，現在都是另外筆記號碼，再用篩選功能一個一個記上，有點麻煩且耗時，請問有沒有方法可以一鍵登錄?
ಠ_ಠ
Mon, May 8, 2023 9:32 AM
pirate2699: 嗯⋯你不考慮用Quizlet之類的FlashCard網站嗎？ google試算表的巨集是用JavaScript來寫，所以提出的解決方案沒法用VB，還是會牽涉手動處理，這樣也可以嗎？
pirate2699
Mon, May 8, 2023 9:35 AM
ಠ_ಠ: 是的沒關係，這邊主要的問題是1，2如果可以的話就好，謝謝您
ಠ_ಠ
Mon, May 8, 2023 12:24 PM
pirate2699: 先回1，序號150~300是指C欄的單字號碼嗎？那用RANDBETWEEN(150,300)可以？ (進階一點就是把最小值和最大值分別記左K1和L1→RANDBETWEEN(K1,L1)
ಠ_ಠ
Mon, May 8, 2023 12:34 PM
pirate2699: 2的部分，不知道你對MARK GOOD/SOSO/BAD的定義如何，這裡我有2個想法，兩個想法的前半部都是一樣的︰
每次抽出來的亂數→先找地方貼上值(因為後面每次修改，亂數會重置)→每題答對後手動標示+1/0/-1 (如答對是+1, 答錯是-1, 除此為0)
→之後序號與標示貼在工作表上，像附圖A欄是問題號碼，附圖B欄是對應分數
ಠ_ಠ
Mon, May 8, 2023 12:38 PM
Mon, May 8, 2023 12:39 PM
pirate2699: 之後就是分歧路線︰
1) 累積計算
=SUMIF(A:A,F1,B:B) 就這樣用SUM計算
換言之如果你沒有連續/長時間答對該題，就會一直是負數
2) 每次重置
=INDEX（B:B,MAX（IF(A:A=F1,ROW(A:A),0))) →CSE
留意要用CSE和INDEX/MAX後的括號是全形要手動改
這樣就只會計算最近一次的結果(假設你的表是往下更新)，會反映這題最後一列的結果，所以不會出現-2這些數字，而是只會-1/0/+1
ಠ_ಠ
Mon, May 8, 2023 12:40 PM
pirate2699: 最後用格式化條件(我看A欄，猜海盜應該會用就不詳細介紹) 調成這樣，可以省掉另外用IF反映文字的動作
ಠ_ಠ
Mon, May 8, 2023 12:43 PM
pirate2699: 以上，你覺得這樣可行嗎？
好奇問，海盜是有甚麼原因覺得用EXCEL/試算表比用FLASHCARD類的APP/網站好嗎？
其實我以前還真的有寫過用EXCEL來溫習的巨集，不過一直沒空更新到BMC，要是你對巨集/EXCEL有興趣，請跟我說一下，我會嘗試加緊速度把它分享出來
pirate2699
Tue, May 9, 2023 6:02 AM
謝謝噗主
1的部分，因為C欄的單字號是不連續的所以另外建了D欄給流水號，另在M2及M3加上希望的範圍號碼
M7=index ($E$2:$E$116,randbetween($M$2,$M$3))
還有個小問題是單字會重複出現
而且這邊之前的B欄亂數好像就沒用了，之前是用亂數來跑出隨機單字的，如果噗主的解決方案都不需要的話我再把這欄刪掉
pirate2699
Tue, May 9, 2023 7:35 AM
2的部分，經過嘗試之後沒問題了
然後我發現上面新增的D欄流水號好像沒意義XD
另外這邊回覆噗主問題，覺得用EXCLE來做複習，主要是可以用CTRL+F功能快速找到類似的單字去做註解跟記憶，FLASHCARD類的APP可能比較沒這個功能，另外就是自己比較適合用電腦學習，用手機容易分心
ಠ_ಠ
Tue, May 9, 2023 4:20 PM
pirate2699: D欄的流水號應該沒必要追加？如果有公式要用到，直接ROW()-1就可以了
(如果有經常改變排序，但同時生字必須按這個次序的情況例外)
替代RANDBETWEEN的重覆問題，有2個方案，2個方案都是需要1行欄來做參考︰
1) 用UNIQUE抽選獨立號碼
照舊保留RANDBETWEEN，另外一欄用UNIQUE(範圍)，這樣就只會抽獨一無二的號碼出來，不過像附圖例子，預設有9個號碼，但扣掉重覆號碼後，能測驗的題目少於9題。
ಠ_ಠ
Tue, May 9, 2023 4:21 PM
pirate2699:
2) 保留B的RAND()
把B欄的RAND()改成IF，=if(and(row()>$B$23,row()<$C$23),RAND(),"")
這樣符合你的條件內的題目才會出現亂數
然後查找可以用vlookup或者index/match之類，再用sortn找首5個(例)的內容
=ArrayFormula(vlookup(SORTN($A$24:$A$33,5),$A$24:$B$33,2,0))
ಠ_ಠ
Tue, May 9, 2023 4:30 PM
pirate2699: 這樣你覺得如何？
謝謝你的回覆，那作為ctrl+f，你可以考慮用hyperlink連結測驗的題目，直接跳到該當儲存格，那會不會比較方便? 方法是=hyperlink("#gid=？？？？？range=b"&MATCH(C24,$B:$B,0),"link")
問號的部分是對應你工作表連結gid最後的數字
pirate2699
Wed, May 10, 2023 10:04 AM
謝謝噗主，上面參考2) 保留B的RAND()方法，再加上條件，讓Good對應出1~999亂數，其他的則是出現小於1的亂數，這樣在考單字時就會出現不熟的單字
這邊讓Good對應出1~999的原因是，希望可以在考題20題中隨機出現4~5題Good的單字(一方面預防Good的太久沒看忘了，一方面要是其他單字都不會至少還有幾題熟悉的
，保護玻璃心的部分
)
因此讓熟悉(1~999亂數)跟不熟(小於1亂數)做出區別，想碩這樣是不是能在N7欄寫出個甚麼東西....?
另外hyperlink這邊也有做嘗試了，覺得挺方便的，謝謝噗主提供!
ಠ_ಠ
Wed, May 10, 2023 3:40 PM
pirate2699: 是機率哦…1~999會不會差太多，在研究的過程發現我忽略了SORTN的功能，所以在這裡順便修正一下，
先保留你的B欄亂數，不過公式改成這樣︰
=IF(A1="Good",IF(AND(ROW()>=$G$1,ROW()<=$G$2),RANDBETWEEN(6,20),0),IF(AND(ROW()>=$G$1,ROW()<=$G$2),RANDBETWEEN(1,10),0))
ಠ_ಠ
Wed, May 10, 2023 3:40 PM
這裡的IF A1=GOOD，你可以用你本來的公式套用一下，這邊因為簡便才隨意寫的。後面是跟之前說的差不多，分別是我直接用RANDBETWEEN取代了RAND，GOOD的亂數是6~20，非GOOD的亂數是1~10，這個部分你可以再調整一下，主要是為了讓GOOD更高機率出現在選題，然後GOOD和非GOOD的數值有重疊。
還有把""改成0，那是為了後面SORTN而改的。
ಠ_ಠ
Wed, May 10, 2023 3:45 PM
之後就是把抽選的地方改成=SORTN($B:$D,5,2,$B:$B,0)
原因是SORTN可以把陣列輸出，那就省掉了VLOOKUP/INDEX MATCH的部分
不過亂數的部分也被抽出來，可以視乎情況隱藏掉? 如果不喜歡也可以像以前一樣SORTN $B:$B，再補上VLOOKUP之類的函數。
後面的5是指抽首5項，2是可以省掉重複列的變數(這樣根本不用UNIQUE，我是白痴)，後面的$B:$B和0是指用B欄排序，並從遞減方式列出首5項內容
ಠ_ಠ
Wed, May 10, 2023 3:46 PM
由於前面RANDBETWEEN，GOOD的題目更容易出現較大的數字，這樣用遞減便更容易抽選出GOOD的題目。如果出現的頻率太高，可以在前面RANDBETWEEN的部分再調整一下，比如GOOD是1~10，非GOOD是6~10的話，GOOD的出現頻率應該會減少？抱歉數學這回事其實我不太懂…
ಠ_ಠ
Wed, May 10, 2023 3:47 PM
大概就是這樣吧？你覺得這樣可行嗎
pirate2699
Thu, May 11, 2023 10:08 AM
鋪主您好，謝謝提供！這邊觀察您的方法後，發現這樣出現的排序好像固定Good的都會出現在前幾個。因為這邊to跟from的範圍時大時小，RANDBETWEEN的範圍比較難調整
pirate2699
Thu, May 11, 2023 10:12 AM
於是這邊採用的方法是
B欄式子不變
=iferror(ifs(and(row()>=$N$2,row()<=$N$3,or(A2="Soso",A2="Bad",A2="")),rand(),and(row()>=$N$2,row()<=$N$3,or(A2="Good")),randbetween(1,999)),"")
測驗單字固定20題，前4題為Good，後16題為非Good
N7=sortn($D$2:$F,4,2,$B$2:$B,0)
N11==sortn($D$2:$F,16,2,$B$2:$B,1)
pirate2699
Thu, May 11, 2023 10:20 AM
但是這樣發現BUG
N7欄永遠出現to+1的結果
(例如我這邊to是20，則只會出現21列的accept)
不知道這樣該如何解?
pirate2699
Thu, May 11, 2023 10:21 AM
這邊想到最簡單的方法就是我直接多拉一題，然後無視第一題XD
ಠ_ಠ
Thu, May 11, 2023 10:41 AM
pirate2699: 抱歉今天生病了 初步是猜這2個地方其中一個， B2:B 被當成B:B ; 遞增/減的部分與亂數留白””的關係
我病好再研究看看
pirate2699
Thu, May 11, 2023 12:20 PM
ಠ_ಠ: 好的，感謝噗主生病了還來回覆
請多休息先不要動腦了，希望您早日康復
litchi6868
Tue, May 16, 2023 10:53 AM
想請問比對這種兩欄的重複值要怎麼做會比較簡單，我嘗試用條件式格式設定重複值，但他會把自己那欄的重複值也算進去，我只需要E和F比較而已
ಠ_ಠ
Tue, May 16, 2023 2:34 PM
Tue, May 16, 2023 2:38 PM
hi我歸來了
pirate2699: 這邊看了你的公式，B欄算式是"" (空白)的時候，在SORTN遞減會被當成最大數處理，加上N欄的sortn公式是把整個B欄的內容一起處理才會出現BUG。為了保證sortn只處理想要的範圍，我建議用indirect處理，方法如下︰
把你原有的N7和N11的公式裡，屬於範圍的部分用Indirect來引用，缺點是往後如果你有新增欄，這兩個公式也一定要手動改(因為不是參照，所以不會自動修正)
例子︰$D$2:$F→修改成indirect("D"&$N$2&":F"&$N$3)
後面$B$2:$B也是像上面修改，把D和F改成B和B。
附圖例子參考︰
ಠ_ಠ
Tue, May 16, 2023 2:37 PM
pirate2699: 另外B欄的公式，or(A2="Good")這個部分可以略掉or也沒關係(因為裡面只有一個條件)
ಠ_ಠ
Tue, May 16, 2023 2:57 PM
litchi6868: 一般來說用格式化條件檢視重複值的話，像荔枝的例子最好就是E欄和F欄各自的內容在該欄都沒有重複，這樣的話會建議你直接看︰
比較A表和B表相同/不同的公司名的六個方法︰（三）格式化條件 — 從社畜變成Excel畜
如果在E欄本來就有重複，可是還是要比對F欄有沒有重複的話……
建議你考慮移除重複，條件選E欄&F欄必須一致的話就可以了
比較A表和B表相同/不同的公司名的六個方法︰（二）移除重複 — 從社畜變成Excel畜
考慮到E和F欄都是一樣性質的內容，移除重複聽起來是最合適
假設E欄和F欄的內容並沒有關係，兩欄的性質不一樣，折衷可以用Match
示範圖如下
從社畜變成Excel畜 (@excel_od) • Instagram photos and video...
ಠ_ಠ
Tue, May 16, 2023 2:57 PM
litchi6868: 假如肯定兩欄的名字不會有差異，並且確定E或F欄為準，可以用那欄做參照，那寫一條Countif︰=countif(F:F,E1) 再篩選找大於0的內容就可以，如果只是需要標示，就把公式丟在格式化條件裡。
大概是這幾個方法吧，只是看這個圖，沒法判斷荔枝需要比較的目的和資料的性質，如果上面的方法覺得不貼題的話，也可以再多說一點要求和情境，那樣判斷會比較準確一點
ಠ_ಠ
Tue, May 16, 2023 2:57 PM
litchi6868: countif的部分可能寫得比較簡單，要是拿捏不到做法可以說一下，我再補充一下做法
litchi6868
Thu, May 18, 2023 8:55 AM
大概是這樣的，E欄是已經找好的目標學校36間，然後要從全台學校大概108間(F欄之後)裡面找資料，我一開始是想說就用學校名稱來比對，然後找出後續資料(例如學生人數)這樣
litchi6868
Thu, May 18, 2023 8:58 AM
因為有些不同縣的學校有同名，所以事先不能先砍掉F欄重覆的值，而是要整個原始資料下去篩一遍
大致是希望可以F欄只和E欄比，F欄自己不要跟自己比這樣
ಠ_ಠ
Thu, May 18, 2023 9:32 AM
litchi6868: 那這樣的做法你覺得如何︰
把36間/108間分開兩個工作表→兩邊分別用縣&市&校名連結在一起→(可以事先把連結後的校名比對一下有沒有重複, 沒重複的話)→用這個連結好的名字再用VLOOKUP查收出來
理論上同縣同市同校名的狀況應該很少
另一個方法是，假設36間的表不會有重覆，反過來用108間的表做Main，檢索有沒有對應36間的校名→篩選有找到的結果→用這個結果找重覆值/不找重覆值，另設一欄再查收縣市是否吻合
ಠ_ಠ
Thu, May 18, 2023 9:36 AM
個人覺得這個情況不需要先查找重覆值，可以直接查收就可以了
方法1是比較常見和簡便的做法，而方法2是以數據量較多的列表來做主體，但缺點是如果108間校名裡面不保證包含那36間的內容，那最後還是得找找少了哪間學校的資料沒找到(這部分我會用移除重複/match之類的方式彌補)
以上是我先簡略介紹做法的內容，有需要做法的話可以告訴我
litchi6868
Thu, May 18, 2023 11:10 AM
我試試VLOOKUP
sesame2710
Tue, May 23, 2023 6:49 AM
想請問噗主當我要比對的欄位職編碼不同的時候，我該怎麼樣更有效的進行比對？
有辦法在vlookup或xlookup的時候讓excel判定B-L202=BL202、CP-L101-1=CPL101-1嗎
ಠ_ಠ
Tue, May 23, 2023 7:05 AM
Tue, May 23, 2023 7:05 AM
1
sesame2710:
這個情況的話我喜歡用index/match，先用substitute把減號去掉，再用match找列數(即是上圖例子)
之後再補上Index就可以找到對應的內容
補上公式，留意X號是全形
F2 =INDEＸ(C:C,MATCH(SUBSTITUTE(E2,"-",""),SUBSTITUTE(A:A,"-",""),0))
sesame2710
Tue, May 23, 2023 7:51 AM
謝謝噗主！！！真的幫了好大的忙
ಠ_ಠ
Tue, May 23, 2023 8:05 AM
sesame2710: 不用客氣，謝謝你的支持！
coffee9771
Tue, May 23, 2023 9:05 AM
請問一下google sheets中用到排序功能，也確定範圍都有選到要排序的資料，但是有兩列就是沒有按照A->Z排序，要怎麼解決呢orz
已經確認排序基準的儲存格是沒有空格的
ಠ_ಠ
Tue, May 23, 2023 9:37 AM
coffee9771: 可以截圖和再詳細說明一下嗎？
coffee9771
Tue, May 23, 2023 9:59 AM
ಠ_ಠ:
我要排序但是這兩個沒有照A->Z排序
ಠ_ಠ
Tue, May 23, 2023 1:02 PM
coffee9771: 有可能是後面兩個數字是文字嗎？參考附圖，後面兩個數字我故意加上了「'」，在儲存格看不出來可是排序會像咖啡的圖一樣反映
如果用ISTEXT和ISNUMBER去驗證就會看到後面2個是文字不是數值
ಠ_ಠ
Tue, May 23, 2023 1:03 PM
coffee9771: 這樣的話像原本是長這樣的圖(前2個是數字後2個是文字)
排序後會變成這樣
coffee9771
Wed, May 24, 2023 2:06 AM
ಠ_ಠ: 有了!!!原來是因為他是文字所以沒有排序在一起
謝謝謝謝謝大大.....
ಠ_ಠ
Wed, May 24, 2023 2:10 AM
coffee9771: 太好了
litchi8905
Wed, May 24, 2023 2:57 AM
想請問噗主，例如工作表名稱為1日、2日…，B2公式為'1日'!AB$32，向下填滿時，怎麼讓B3自動變成'2日'!AB$32，B4變成='3日'!AB$32
目前直接向下填滿會變成都是'1日'!AB$32
ಠ_ಠ
Wed, May 24, 2023 3:17 AM
litchi8905: 用indirect, =INDIRECT(A1&"!$B$2")
如果範圍不是絕對參照，之後有可能增刪欄列而變化的話，這個部分需要手動修改，EXCEL不會自動跳
如果需要配合自動調整範圍，進階點的方法是把範圍改成ADDRESS像下圖
=INDIRECT(A1&"!"&ADDRESS(2,2)&"")
ADDRESS裡的2,2，利用LOOKUP系的函數等，取得欄數列數，那日後有增刪欄列也會自動調整
litchi8905
Wed, May 24, 2023 3:46 AM
ಠ_ಠ: 成功啦啊啊啊啊啊謝謝噗主，困擾我多年的問題終於解決了
ಠ_ಠ
Wed, May 24, 2023 3:47 AM
litchi8905: 不客氣 再有問題可以隨時說
coffee9771
Thu, May 25, 2023 2:01 AM
噗主我又來了........
舉例來說我的表格有A產品 B產品 C產品 他們的規格在甲工作表
我做了一個簡單統計庫存的乙工作表，有入庫/拿出會記錄在這個表格
現在用sumif反應在甲工作表內
但是並不是所有產品都有入庫拿出的紀錄，只要沒有資料sumif都會回傳0，那我有些產品是真的沒庫存了我無法辨別
想請教如何寫來區分無資料與0
謝謝..........
ಠ_ಠ
Thu, May 25, 2023 2:31 AM
coffee9771: 你可以提供截圖給我參考一下嗎？不一定要用真的數據，我照你提供的格式想想
coffee9771
Thu, May 25, 2023 2:53 AM
ಠ_ಠ:
這是甲工作表，有規格和總庫存量計算
這是乙工作表，入庫/拿出的統計在這邊做紀錄
透過SUMIF回傳，但像編號2 A02，在乙工作表是沒有資料但仍然回傳0，會造成統計上的困擾
ಠ_ಠ
Thu, May 25, 2023 3:25 AM
coffee9771: 那直接加一個IF條件式可以嗎？
E3(跟你截圖的儲存格位置一樣) =IF(COUNTIF(乙!$B:$B,B3)=0,"沒有",SUMIF(乙!$B:$B,B3,乙!$C:$C))
如果乙表沒有出現過商品，就直接反映「沒有」，有出現過才用你的公式計算庫存
coffee9771
Thu, May 25, 2023 6:39 AM
ಠ_ಠ: 對喔!!!!我又沒想到可以這樣設定了
已解決弄好了謝謝謝謝大大
ಠ_ಠ
Thu, May 25, 2023 7:43 AM
coffee9771: 不客氣
walrus1936
Thu, May 25, 2023 8:19 AM
噗主你好 想要發問是否有辦法做到:
我有複數個指定的日期 自動比對(裝置時間orGMT+8之類的)如果當天是指定日期時 賣價那一欄變色?
比方說這樣指定時
今天是5/25所以有變黃色 到了明天(5/26)就換成另一個顏色(或無顏色) 再到後天(5/27)因為是指定日期了所以又變成黃色
ಠ_ಠ
Thu, May 25, 2023 2:31 PM
walrus1936: 格式化條件, =COUNTIF($B:$B,today())>0
下圖參考是用$D$2是測試用，把$D$2換成TODAY()就可以指定當天日期
rum10
Fri, Jun 16, 2023 11:59 PM
噗主您好，想請教一下一個問題，我在用excel的時候處理類似編號的資料（089.0040），我有事先把儲存格格式都轉成文字，但是只要我做「取代」，他的格式就會跑掉，想請問您知不知道該如何避免呢？
Ex: 設定好儲存格格式為文字 > 貼上089.0040（此時正常顯示為文字）> control f 取代條件：刪除.之前的東西（*.）> 最後顯示40（但預期為0040）>我崩潰
ಠ_ಠ
Sat, Jun 17, 2023 9:56 PM
rum10: 試試這樣子︰
取代的時候結果不要空白，而是打「'」(即是平時用儲存格，為了不讓文字變成數字/公式時，會在前面補上的引號」
rum10
Sat, Jun 17, 2023 11:36 PM
ಠ_ಠ: 我上班試試看！十分感謝
honey301
Fri, Jun 23, 2023 12:01 PM
Fri, Jun 23, 2023 12:01 PM
噗主您好！想詢問使用公式=iferror(vlookup(indirect("B"&row()),'複製'!C:AY,1,0),)
公式都沒有錯誤，兩個工作表的文字也都一樣，但卻顯示不出資料TT
還有什麼方法可以檢查錯誤嗎？
ಠ_ಠ
Fri, Jun 23, 2023 4:34 PM
honey301: 嗯……公式沒有錯誤可是它跟你想表達的事情是完全相反的
接下來要解釋的比較長，請有心理準備。
現在你的公式意思是︰在「複製」C欄裡，找尋符合B欄某列的內容，找到後輸出第一個欄(即是C欄)的內容。如果沒找到，就輸出0。
如果要修改你的公式，可以以下面兩個方式進行︰
第一、VLOOKUP只能用左至右檢索，如果以你目前的做法比較偏向右至左，
VLOOKUP的用法你可以參考這一篇︰
Vlookup 應用指南 — 從社畜變成Excel畜
如果你希望右至左，你可能要考慮更新的XLOOKUP，或者把「複製」的G和C欄位置交換
ಠ_ಠ
Fri, Jun 23, 2023 4:34 PM
honey301: 第二、如果不能移動，你需要考慮用INDEX/MATCH的組合，可以參考這篇
比較A表和B表相同/不同的公司名的六個方法︰（五）以A表為基礎設計搜索表 — 從社畜變成Excel畜
這個組合可以彌補在XLOOKUP面世之前，沒有右往左檢索的問題。
在這裡不介紹XLOOKUP是因為我較少用，手邊的設備不方便跑XLOOKUP，就不特別介紹了。
在提供正確答案之前還必須提示原本公式三個需要注意的地方︰
1) 如果要指代B欄某列，不需要用INDIRECT，直接用B2→之後往下來就可以了。如果會往右拉，那就鎖B欄(即是$B2)就可以。
2) 選取範圍要有鎖範圍的好習慣，如果不會往右拉那可以不鎖。
ಠ_ಠ
Fri, Jun 23, 2023 4:37 PM
honey301: 3) IFERROR的話，最後只用逗號留白，ERROR時會出現「0」，希望結果為空白，可以用""。(這個非強制，視情況是希望出現甚麼結果，目的是方便分辨正確答案和非正確答案)
介紹了上述兩個方法，就要解釋怎麼用︰
一、VLOOKUP的話必須把
=IFERROR(VLOOKUP(B2,複製!C:AY,5,0),"")
ಠ_ಠ
Fri, Jun 23, 2023 4:39 PM
Fri, Jun 23, 2023 4:39 PM
honey301: 二、INDEX/MATCH的話是︰
=IFERROR(INDEX (複製!C:C,MATCH(B2,複製!G:G,0)),"")
以上，有看不懂的地方請說一下~
nori181
Tue, Jul 4, 2023 1:46 AM
想詢問VBA 相關
我的outlook 有兩個帳號在裡面
用Vba發信的話是使用預設帳號1，但我現在享用帳號2寄送應該要怎麼寫代碼呢？
ಠ_ಠ
Tue, Jul 4, 2023 3:48 AM
1
nori181: 謝謝你的問題，可惜我這邊沒有兩個帳號可以試，只能幫你查一下
Send email from another address using VBA
nori181
Tue, Jul 4, 2023 4:49 AM
我的天成功了
噗主找到這個網站寫的很清楚很好懂
感謝你
ಠ_ಠ
Tue, Jul 4, 2023 5:20 AM
nori181: 太好了！
tuna649
Wed, Jul 5, 2023 7:57 AM
您好，想請問有辦法讓數字個位數四捨五入嗎
比方577.92→變成580
442.47→440
這樣
謝謝
ಠ_ಠ
Wed, Jul 5, 2023 8:01 AM
tuna649: =round(A1,-1)
tuna649
Wed, Jul 5, 2023 9:24 AM
ಠ_ಠ: 哦哦哦哦哦原來填-1，看不到他要填什麼（⋯⋯）謝謝您
ಠ_ಠ
Wed, Jul 5, 2023 9:33 AM
tuna649: 簡單來說是看小數點來決定，小數點左邊就是負數
下記參考：
永遠的20歲︰是Round? Roundup? Rounddown? — 從社畜變成Excel畜
ferret8502
Sat, Jul 29, 2023 3:00 PM
噗主您好，我最近要辦電影包場
請問有沒有辦法從「方便參與的時間」x「參加人數」，計算出某天的參與人數？
以前兩個為例，在表單後台只會看到選8/19有兩個，實際上是四個人可以參加
不知道這樣形容清不清楚(?)，謝謝噗主
ಠ_ಠ
Sat, Jul 29, 2023 4:04 PM
ferret8502: SUMIF吧，首先你要列出你所有選項，
之後公式填SUMIF，選擇選項的部份前後加上星號，就可以SUM到人數了
ferret8502
Sun, Jul 30, 2023 7:33 AM
謝謝噗主回答，我來試試看
sesame2710
Mon, Jul 31, 2023 2:39 AM
噗主您好，我的excel資料中有三種不同格式的日期區間，怎麼樣可以快速的把它們調成一致格式呢？
保固校驗日期
2023/3/12~2023/12/31
5/28/2022~9/15/2023
2023.07.01~2024.06.30
ಠ_ಠ
Mon, Jul 31, 2023 4:03 AM
1
sesame2710: 哇噢…這樣的話建議先把前後的日期分開(這部分可以用資料剖析或者find+left/mid (關鍵詞是"~")
之後再判斷日期是哪一種，再用公式改回來
=IFERROR(IFERROR(TEXT(DATE(LEFT(A2,4),MID(A2,FIND("....
你看看用paste裡的公式能不能用到…我預設的日期格式是yyyymmdd，你把這部分改成你想要的日期格式就可以了(如yyyy/mm/dd)
sesame2710
Tue, Aug 1, 2023 3:52 AM
ಠ_ಠ: paste裡的公式幫了大忙，大部分都有成功修改，少數出現#VALUE!
出現#VALUE!的日期格式有這三個，不太明白為什麼只有他們不行
「10/15/2022~9/15/2023」
「9/16/2023~2024/6/13」
「2022/2/23-2023/1/3」
paste裡的公式看起來是直接抓A欄的值，所以沒有拆解成BCD欄好像也可以運作嗎
ಠ_ಠ
Tue, Aug 1, 2023 5:13 AM
sesame2710: 謝謝噗幣！昨天我寫得比較急，如果要方便修改的話，本來是要多分三欄比較好的
我因為要一欄處理它才把公式合併起來
抓A欄是因為我本來是用A欄做參照（就是之前的附圖）
統一把A欄的部分改做BCD欄就可以運作了
你舉的例子和對應不到的部分，我要慢點回公司/回家才能看看，麻煩請多等半天（左右）
ಠ_ಠ
Thu, Aug 3, 2023 4:22 PM
Thu, Aug 3, 2023 4:23 PM
sesame2710: 抱歉這兩天實在忙不過來，現在才有時間了解
首先解釋一下原本公式的用意，是假設你的文字(日期範圍)只有三種可能性而寫出來的，分別是︰
yyyy/mm/dd~yyyy/mm/dd
mm/dd/yyyy~mm/dd/yyyy(不過這部分我寫得好不，導致起始日的mm讀不到2位數字的月份)
https://yyyy.mm
.dd~
https://yyyy.mm
.dd
可以參考這個圖︰
所以在這三個模式以外的文字不能適用於這個公式。
[續]
ಠ_ಠ
Thu, Aug 3, 2023 4:30 PM
Thu, Aug 3, 2023 4:34 PM
sesame2710: 接下來要解釋為甚麼對應不到你第二次舉例的三個例子，
「10/15/2022(略)」見上記，「9/16/2023」的問題是由於年月日的排序不一致，而「2022/2/23」這部分是因為分割起始日和完結日的符號不是「~」而是橫線所致。針對第二次舉例的部分，用紅字修正後，便能反映出來。(見附圖)
附圖C欄是公式套用的結果，DGJ欄是對應上記3種日期模式的寫法，EFHIKL欄是該當寫法的起始日和完結日的部分，黃色標記表示它們成功對應在哪部分。
[續]
ಠ_ಠ
Thu, Aug 3, 2023 4:46 PM
sesame2710: 這裡要提出的問題是，因為現在偏離了原本公式的出發點，那就必須要了解︰
1) 這個拆解日期範圍的工作有多常用和頻繁，資料有多少
→很常用的話，那直接寫個轉換用的工作表、MACRO吧
[續]
ಠ_ಠ
Thu, Aug 3, 2023 4:46 PM
sesame2710: 2) 資料會有多少種日期模式和分割標記(如「~」)
→年月日的排序這麼多，如果掌握不到，那就不這樣寫公式了，像上記附圖般，盡量多用些欄位拆開可能比較好(我預想之所以旅人有這個煩惱，就是因為輸入的人都是照自己喜歡寫才會出現這個問題...?)
→我的話會視乎需要解決的頻率和數量，會找折衷方法(比如說如果年份只會限定在2022~2024年，可能就會用這個做參考來寫公式等等)(如果量很少的話，基本上公式判斷不到的部分我都直接偷懶手改)
→分割標記不統一的話，要不直接replace all(只有~和-之分)，判斷不到的話就直接資料剖析數次來拆開
[續]
ಠ_ಠ
Thu, Aug 3, 2023 4:53 PM
sesame2710: 說到資料剖析的意思就是這樣(附圖)，先把起始日和完結日用「~」判斷拆開，再指定為日期
這樣拆不開的就是非使用「~」的日期標記(也因為拆不開，B欄是空欄，這部分用篩選之類找出來就好)
至於拆出來後，如果不是日期(即是附圖選取的部分)，再另外對應就可以了(要找出來也很容易，用ISNUMBER找，非日期是FALSE)
大致上是這樣吧，這段時間我也再想想怎樣做
ಠ_ಠ
Thu, Aug 3, 2023 5:40 PM
sesame2710: 比如說，配合資料剖析後，獨立處理單一日期可以這樣做
這裡預想的是只有YMD和MDY這兩個情況，還有會用「/」或「.」做分隔的情況
如果還有2023-08-04這種情況，那就再套一個SUBSTITUTE就好
公式︰
=IFERROR(IF(ISNUMBER(B3),TEXT(B3,"YYYYMMDD●"),TEXT(DATEVALUE(RIGHT(B3,4)&"/"&LEFT(B3,FIND("/",B3,4)-1)),"YYYYMMDD●")),TEXT(DATEVALUE(SUBSTITUTE(B3,".","/")),"YYYYMMDD●"))
sesame2710
Fri, Aug 4, 2023 8:16 AM
ಠ_ಠ: 真的太感謝噗主了，說明的好詳細，我想我知道怎麼處理了
會有這樣的狀況是每家合作廠商給的日期格式都不一樣，
雖然我們有給指定格式但沒有人要配合
。值得慶幸的是合作廠商就這幾家，日後應該就也只有這幾種格式
ಠ_ಠ
Fri, Aug 4, 2023 11:14 AM
sesame2710: 辛苦旅人了⋯
如果是分開不同廠商的話，那收到資料時用資料剖析先改掉格式，之後才和其他廠商的併起來整理會比較好
sesame2710
Fri, Aug 4, 2023 1:14 PM
ಠ_ಠ: 好的！！！
sesame2710
Tue, Aug 8, 2023 3:08 AM
噗主我又來了
這次的問題是抓取欄位值的時候，該欄空白要怎麼自動抓取下一欄直到有值呢？
舉例：聯絡人需要填入最大的長官，有處長就帶入處長，沒有處長就帶入副處長，副組長也沒有就帶入一級主管
拜託噗主了
ಠ_ಠ
Tue, Aug 8, 2023 5:23 AM
sesame2710: 你是要帶出人名還是要職位？（阿明/處長）
sesame2710
Tue, Aug 8, 2023 5:27 AM
Tue, Aug 8, 2023 5:27 AM
ಠ_ಠ: 人名
我目前用了
=IF(ISBLANK(B3),TEXT(D3,0),TEXT(B3,0))
這樣可以在處長無人的時候帶出副處長的名字於聯絡人欄位
可是到這裡我就寫不下去了
不知道怎麼讓公式繼續判斷副處長也無人的時候改帶入一級主管的名字
ಠ_ಠ
Tue, Aug 8, 2023 5:53 AM
sesame2710: 因為時間關係，總之要帶出答案的列(ROW)是
=MIN(IF(LEN(A2:C4)>0,ROW(A2:C4)))
帶出答案的欄(COLUMN)是=MIN(IF(LEN(A2:C4)>0,COLUMN(A2:C4)))
接下來是看旅人想用OFFSET還是INDIRECT(ADDRESS()) LOCATE它的位置，
ADDRESSS的話把這兩條公式放在相應位置→CTRL+SHIFT+ENTER就可以了
如果是OFFSET就要兩邊-1(也是要用CSE)
ಠ_ಠ
Tue, Aug 8, 2023 5:54 AM
sesame2710: 抱歉說得很粗糙，附圖參考
sesame2710
Tue, Aug 8, 2023 6:20 AM
ಠ_ಠ: 不是很理解QQ，噗主如果忙沒關係可以等有空的時候再回我
我覺得我可能表達上有錯誤，再重新闡述一次問題。每一行都是獨立的，然後我要從各中撈出最優先的聯絡人，所以沒處長就抓副處長，沒副處長就抓一級主管
我現在是用
=IF(ISBLANK(B2),TEXT(C3,0),TEXT(B3,0))
這樣可以在處長無人的時候帶出副處長的名字於聯絡人欄位
所以阿明和阿富我能帶出來了，但阿花我還是不知道怎麼帶出來
ಠ_ಠ
Tue, Aug 8, 2023 8:13 AM
哦哦表是橫向搜尋呀XD 抱歉我以為是在裡面找就職位最高的那個
不好意思，那我回家改一下，預定接近12點
ಠ_ಠ
Tue, Aug 8, 2023 3:34 PM
1
sesame2710: =OFFSET(A2,0,MIN(IF(LEN(B2
2)>0,COLUMN(B2
2),99))-1) ←之後按CTRL+SHIFT+ENTER (一定要!!!
原理是找COLUMN最小的那行就是靠最左的負責人(用LEN判斷有沒有內容, 當然ISBLANK也可以)，之後再用OFFSET移動
ಠ_ಠ
Tue, Aug 8, 2023 3:35 PM
嗚……笑容很燦爛的是「：Ｄ」
sesame2710
Tue, Aug 8, 2023 4:04 PM
謝謝噗主！又學到新的了
ಠ_ಠ
Tue, Aug 8, 2023 4:27 PM
sesame2710: 不客氣，CSE是滿好用的，用來讓函數處理範圍的時候很方便，我記得之前的EXCEL串都有用過好幾遍，有興趣可以參考一下以前的例子
ಠ_ಠ
Tue, Aug 15, 2023 12:48 AM
hawk2985: power query我比較少用，這幾天公司在忙展覽，我盡量找個時間看看，麻煩多等幾天
ಠ_ಠ
Tue, Aug 22, 2023 1:17 PM
hawk2985: 抱歉上星期在忙展覽，這幾天還在處理不在公司的事務
我粗略看過你的疑問，可是我沒空看影片
可以指出你的問題是參考哪個時間點的教學、用了哪一個範例檔案、發生這個問題時的步驟是怎樣嗎？
我會嘗試重現你的問題，再看看是跟教學有哪裡不一樣
ferret8502
Fri, Sep 1, 2023 2:29 PM
請問要怎麼讓排序是照數字大小排列，用排序(A到Z)排出來不是由小到大
ಠ_ಠ
Fri, Sep 1, 2023 2:48 PM
ferret8502: 開一個新欄抽數字再排列？
抽數字的部分，如果只有E和F的話，可以用substitute換走，或者replace成數字0(後者方法是推論)
再用if條件式找有沒有頓號，沒有就輸出上記替換後的數字，有就輸出頓號前的數字
旅人覺得如何？
ferret8502
Sun, Sep 3, 2023 9:03 AM
因為英文字母不只有E、F，範圍是A到O，好像還是沒辦法照順序排(這是電影包場的座位表)
ಠ_ಠ
Sun, Sep 3, 2023 10:14 AM
ferret8502: OK, 這樣我會無視複選座位的部分、因為不保證是連座(後面另外劃分一欄處理)，只抽第一個座位的數字來看
先找有沒有頓號，然後用MID抽數字(因為前端英文字母是固定一位)
最後轉換成數字就可以排序了
=NUMBERVALUE(IFERROR(MID(A1,2,FIND("、",A1)-2),MID(A1,2,LEN(A1)-1)))
之後用同樣方式，用頓號找出複選座位的部分作標示
=IFERROR(IF(FIND("、",A1),">1位"),"")
ferret8502
Sun, Sep 3, 2023 1:48 PM
我剛剛試1～9如果前面補0就可以照順序排了，請問有方法一次讓所有的個位數都補0上去嗎？
例：E8→E08，以此類推
ಠ_ಠ
Sun, Sep 3, 2023 1:55 PM
ferret8502: 補 =text(xxx,”00”)
ferret8502
Sun, Sep 3, 2023 3:53 PM
用TEXT補不進去QQ
ಠ_ಠ
Sun, Sep 3, 2023 4:27 PM
ferret8502: ……嗯座位編號是用英文字母+數字來組成，它是一個字串，如果你要優先用數字排序，就必須要把數字跟字母分割來處理，而且數字還必須要用數字的方式呈現才行，這也是我上面的公式的用意……還是你有不能用這個方法的原因嗎……
litchi8905
Wed, Sep 13, 2023 2:10 AM
請問噗主，利用自訂儲存格格式，使儲存格輸入數字即跑出單位，單位的次方該如何讓他變上標？
ಠ_ಠ
Thu, Sep 14, 2023 1:53 AM
litchi8905: 嗯…以我所知，儲存格格式沒有上標的設定，要在字型格式那邊設定的
goose5639
Tue, Nov 7, 2023 8:10 AM
請問噗主
如果有兩個excel檔想要比較版本
但公司的程式是2010的
有辦法比較嗎？
我們想知道客戶修改了哪裡
excel檔裡面有很多個工作表
都是文字沒有圖片之類的檔案
感謝!!
ಠ_ಠ
Tue, Nov 7, 2023 2:28 PM
1
goose5639: 晚安
能想到的方法大概有3個，其中2個可以參考這個連結
How to compare two Excel files or sheets for differe...
就是用公式和格式化條件逐格對比
第3個方法是考慮修改的地方除了是數值，還有格式的情況下，可以寫巨集比較
剛好找資料時看到這一個ADD-IN，或許旅人可以試試看
https://www.addictivetips.com/...
goose5639
Tue, Nov 7, 2023 3:37 PM
非常感謝噗主釋疑!!
第一個方法「視窗比對」可能不行，
因為每一個工作表內雖然都是文字但非常多欄也非常多行
視窗可能需要縮小到25%以下才能將整個工作表完整納入的程度；
第三個方法則是因為我們公司無法自行下載或安裝軟體(資安疑慮)，因此很遺憾的也不行
明天上班我會先來嘗試第二個方法是否可行
再次感謝噗主!!!!
ಠ_ಠ
Tue, Nov 7, 2023 6:29 PM
Tue, Nov 7, 2023 6:33 PM
goose5639: add-in 那個應該不算是軟體（沒有安裝的成分）
打開那個xls就應該可以用，如果這樣不行的話那寫巨集也應該不許用⋯⋯
還有第4個方法就是用移除重複，但這個方法只應用於像訂單數據這樣的表格資料才能用，因為不知道GOOSE的檔案是怎樣的東西，如果是訂單的話也許就能用。
另外針對這個煩惱的對策是用追蹤修訂(不確定2010有沒有的功能)和鎖定儲存格(限制客戶可以輸入的部分, 減少確認的範圍)
sesame2242
Wed, Nov 8, 2023 1:29 AM
不好意思這裡是excel新手想要詢問噗主，如果今天需要算不同區間內套不同公式的金額，舉例C為原價、G為成本價，在2000-2999元內的C算G+250、3000-3999元內的C算G+300、大於5500的C算C*0.32，有嘗試用IF來寫但沒辦法運作也不知道錯在哪⋯
需要噗主的指點⋯非常謝謝您！
順便截個自己研究很久寫很醜的
ಠ_ಠ
Wed, Nov 8, 2023 5:17 AM
sesame2242: 2999>C2>2000 ←這部份條件不能這樣寫，可以用AND(條件1, 條件2)來代替
引號的部分會換成文字，不過我猜你是為了測試才這樣做？
還有大於5500是不包括5500嗎？(我現在是寫大於等於, 要是不對請自行修改)
修正如圖，盡量符合旅人要求是數字範圍內，所以不符合範圍的東西都一律ERROR處理
sesame2242
Wed, Nov 8, 2023 7:05 AM
ಠ_ಠ: 目前人剛好在外面沒辦法用電腦嗚嗚⋯到家的話會立刻試試看
我其實寫的時候不確定能不能寫成那樣⋯但就想說試試看因為身邊剛好也沒有人寫過類似的
因為那個5500可能還會再修改（不太確定）但如果知道這大概要怎麼寫的話我應該可以寫出來的
我老闆太善變了⋯
sesame2242
Wed, Nov 8, 2023 7:08 AM
ಠ_ಠ: 真的是超級謝謝excel神仙噗主您願意花時間指點我
ಠ_ಠ
Wed, Nov 8, 2023 8:09 AM
sesame2242: 沒關係 歡迎隨時來諮詢
會盡力解答大家疑難
goose5639
Wed, Nov 8, 2023 10:24 AM
來回報ADD-IN解決問題了(還好沒有擋巨集QDQ
順帶一提我們的不是數值(日期或統計資料等)也沒有公式
只是很多很長的字句
(為什麼不寫在word裡面QQ
非常非常感謝噗主!!!!
ಠ_ಠ
Wed, Nov 8, 2023 12:52 PM
goose5639: 不客氣 有解決到就好了
我也有用過excel去對比很長的字句
不過沒有很多工作表
旅人你不孤單，抱抱
ಠ_ಠ
Mon, Dec 25, 2023 2:15 PM
不知不覺Excel台又過了一年，今年4月中旬開始因為工作忙碌，沒法子努力在偷偷說宣傳，還是感謝大家一直以來的支持，謝謝大家需要Excel台！
因為年末會去旅行，所以只能預備一小部分的聖誕禮物(excel教學)在剩下來的幾天
Buy me a coffee
給大家使用，暫時預備了的只有這些︰
本來應該一併更新IG，可是我太遲準備了，所以嗯……
明年回來就會開2024年的Excel台，到時會努力把今年的存貨都放出來
ಠ_ಠ
Fri, Jan 5, 2024 1:06 PM
晚安！2024年的Excel台已經開了！
ಠ_ಠ - 【長駐】 #EXCEL台 2024年集合串 ✿ 提供各種Excel問題疑難排解 ✿ 公式、C...
希望大家今年也多多支持！
載入新的回覆
✿ 提供各種Excel問題疑難排解
✿ 公式、CSE、巨集/VBA
✿ 不支援硬體或設備問題範疇外
✿ 更多分享、過往回顧
✿ 賣藝求活
萬事問噗浪 試算表 函數 google試算表 轉蛋
從2021年開始Excel台，透過解答大家的問題我學了很多東西，也感謝各位無形與有形的支持。
與此同時，我也決定弄一個Buy me a coffee的頁面，把過往的例子歸檔外，未來會繼續分享一些Excel台以外的內容，大家可以視乎需要訂閱。（有免費內容）
☕ Excel On Demand ☕
就算不訂閱，未來日子也會繼續在這台為大家服務。
在此祝願2023年，大家可以馴服Excel，
把省下來的時間都拿來摸魚。----------------------------------
以下開放新一年度Excel提問
用google找過指令，目前找不到完全一樣格式（只找到xls轉xlsx的），但憑自己對VBA的87%理解能力無法自助手動調整，想問下噗主是否有什麼建議
不好意思有點粗疏請見諒
看看這個應該可以，輸入跟輸出同位置
另外想回報下，不知道是不是因為巨集設定打架的關係，有一部分檔案會跳出這樣的警告
依自己87%的理解應該是想要授權，所以在巨集中心試著每個模式都按按看發現這樣的設定後檔案就會跳得出來，不過在那之前會跑一個亂碼框框，然後點ok他就會一個個跳檔案
不確定什麼情況，先FB一下（？
亂碼框框我也不確定，如果之後還有這種情況出現需要解決，那就要看看是電腦、文件、還是巨集問題了(只出現某個情況還是在指定情況都會出現等)
先看看這兩個可不可行，不行再看看是哪裡問題，我們再研究看看吧
例如我紅框處的那三行資料，我只想留下奧迪-A6L那行的資料，想問有沒有自動比較的方式幫我把另外兩行不需要的整行刪除?
比較的依據看文字後面的比例（那個欄位叫信任值），奧迪-A6L為這三筆重複資料信任值最高者，只留最高者就可以了
用條件化格式，如果是365版可以選所有欄，公式寫=maxifs($C:$C,$A:$A,$A1)<>$C1
如果不是365，便選數據的範圍，公式寫=max (if($A$2:$A$11=$A2,$C$2:$C$11))<>$C2 留意max和括號之間有空間
之後格式可以改背景顏色/文字顏色，或者儲存格格式改””，後者的方法只會隱藏信任值
如果要刪除，需要為數據加上篩選，在篩選條件裡用色彩篩選，選擇被你用來隱藏的顏色（如白色），篩下來的數據全行刪掉就好
上記出來的效果大概長這樣
篩選裡選這個
這樣會比較快，另外解法是以資料的BQM6686那行做參照，但如果不能這樣判斷是要比較的數據，麻煩再說明一下
當機聽起來是電腦運算不過來才會發生，所以有可能是因為下面的原因才會這樣？
也許要看看那個表單是怎樣設計才有多點線索
不過power query檔案合併好像只能把標頭設在欄，請問有用列當標頭橫向合併的方法嗎？
手上的資料,時間儲存格的格式是日期+時間(例:2023-01-04 12:10:04)
不知道有辦法在排序時只計日期,不計時間
或是統一清除儲存格的資料,只保留日期,清除時間嗎?
三步從日期+時間的排序改為只用日期的排序 — 從社畜變成Excel畜
變成這樣子︰
然後用B欄(A/B/C/D)的部分合併的意思嗎？(所以A就是854+901=1755)
本來是想問能不能ABC那欄固定剩下數字往右延伸
但後來我是直接用power query 直接拉向噗主那樣，直接用樞紐算，所以算是找到方法了（
原本數據是登記進項發票與預計付款日期的表單
(實際上有更多欄位)
希望呈現出每個預計付款日期中,各類型的金額與該日期加總金額
只有一張工作表的時候可以用樞紐分析處理
可是需要加總多張不同月份,相同格式的工作表時
使用"多重彙總資料範圍"後,預計付款日期,類型,金額等欄位全都跑到"欄";日期,金額,類型名稱等資料全都跑到"值"
沒辦法分別拉到欄,列,值 請問多張工作表應該如何彙整呢?
結果照網路上的做也沒用orz
但我直接把活頁簿表格全選複製貼上在新活頁簿，問題就解決了
我想在同一檔案裡的很多分頁做相同的處理（在每個分頁固定地方插入儲存格），可是錄製巨集後都只能在最初選的分頁處理一直跳回去那頁...
把資料改一改再拉樞紐是這樣
如果拉不到的話，能不能把資料併在同一張工作表才處理？
因為我覺得自己跟human處理資料的方法好像有點不一樣…
如果是換個檔就能解決的話，有可能是原檔有沒察覺到的物件或者副檔名的問題
我不太確定你的CODE的用意…是把工作表1的A20:G31複製→貼到工作表2和以後的頁面的A20:G21(應該是G31?)嗎？(A22:G31原有的內容也會被覆蓋可以嗎？)
如果你必須用巨集，這個你需要用到For Loop，for loop的概念可以先參考 這一篇 裡的流程圖
還有 這一篇 的做法
=and(countif($L$1:$L1,$L1)<>1,isblank(L1)=false)
如果要輸出「重複項目」就把算式放左空白欄
有不懂的部分請提出，回家後再補充
如果想要再進一步把第二個重複項，例如說B、E的Email.刪掉但留下姓名欄的話，該如何處理比較好(圖二)
圖一
圖二
但圖一看起來不像用了公式 而是普通的「重複的值」提示？
因為如果是我剛剛提供的公式，應該只會標示出重複了的內容而不是把第一項的內容都一併標示才對
這樣才會順著數數而不是把整欄來計算
我紅色是用常用項的條件儲存格樣式設定
我的公式寫這樣
=IFS(LEN(A4)<>10,"碼數有誤",AND(MID(A4,2,1)<>1,MID(A4,2,1)<>2),"第2碼有誤")
但最後得出卻是像K123456789 第2碼有誤的結果
這樣你的公式出來應該是#n/a，或許你可以考慮在外面多加一個iferror/ifna
想請問如要計算固定數字*變數
例如A3＊B1、A3＊C1、A3＊D1
用來比較之間的差額且小數點後自動進位
目前都是一個一個輸入=ROUNDUP
輸入了=ROUNDUP(A3＊B1)接續往C3拉的話會變成(B3＊C1)，而我需要的是(A3＊C1)
有試著用$的符號固定住A3，但不知道哪裡出了問題導致ERROR
想知道有沒有可以固定住A3並且可以整排自動填入的函數
能確認你$是鎖在跟下圖一樣的位置嗎？鎖的快捷鍵是F4
如果是快速填充的話，先在B3寫好公式，可以像下圖選取範圍，在不是編輯模式下按CTRL+ENTER或者按CTRL+R和CTRL+D填充就可以了
剛才最多最多只研究到先調整數字格式為0後再在B3建立公式=$A$3*B1快速填充到C3、D3
換到B4又要重新手動建立公式=$A$4*B1
剛又研究了一下第二種方法（噗主教的融合家人教的）
B3：D8設定數字格式為0
B3設定公式=$A3*B$1再快速填充
一樣也可行！
B$1就是鎖定B排，數字自動變動
改儲存格格式是代替在公式上寫roundup，以結果來說兩邊都可以，如果是不擅長公式的話→改儲存格格式比較好
不過儲存格格式很容易被忽略設定，這樣的話用公式會比較安全(防止有遺漏)
ANYWAY, 能幫忙就好了
跟你在儲存格打「=」為了不讓它被定義為公式時，在前方打「'=」是同一道理
我做了一個GIF可以參考一下
（a欄是訂單編號，b是條碼會有很多個重覆的）
不知道這樣說明能否理解
雖然我後來手動填完了
這樣會有4個可能發生的結果，會有不同的輸出嗎？
1）ab兩項全對
2）a對b錯
3）a錯b對
4）ab兩項皆非
我還沒回家沒法驗證，但我記得vlookup不是能驗證複項的函數
如果只要1和4的結果，那可以把表1和表2的ab合併（成新欄，或者空白欄，驗證後才刪掉）才vlookup
Pirate 必須用vlookup嗎？還是別的函數也可以？
如果想再無腦一點，也可以用VBA解決，如果以後也會長期做這個的話，VBA會比較一了百了
看來太難了
只是想先行確認一下pirate 想要的結果是要多仔細（1-4）
如果是可以接受中間用空白欄+只要1和4的話是可以vlookup
如果有其他要求，也考慮到日後會再重複完成的話，我會提議VBA
難不難是依據pirate 實際需要而已，不是辦不到的//
如果因為表格設計而不能在中間插入，那建議用INDEX/MATCH
之後在表2要輸出的位置(I欄)打公式，就可以了
之後把結果貼上值，就可以把用來VLOOKUP用的兩個欄刪掉
這部分是常見和簡單處理複數條件的VLOOKUP做法，缺點就是要多建一個欄來處理資料
不過要注意一點是，要小心只有純數字的儲存格是用「字串」還是「數字」的方式儲存，像上圖例子的黃色標示，儲存格左上方的綠色小旗幟就是代表它不是數字而是文字
這一點是會影響函數運作的 (PIRATE的表2B就是文字, 1A是數字)
等於要把表1的ab先加總，表2再輸入函數（？
）但總之感覺很厲害還是謝謝台主
之後就照附圖那樣打公式，把對應的範圍改好就可以了
如果改不好的話可以再發圖上來問沒關係~
先謝謝了
本來想用time、hour等等去把時間拆開 計算後再組起來 但巴黎比台灣慢7小時 但=time(hour()...後面只能加不能減
後來想用serial number 算但一樣碰到負數ㄉ問題
pirate2699: 請參考這篇，如果有不清楚的地方可以再跟我說。
[期間限定] 根據出勤表生成員工對應休假紀錄 — 從社畜變成Excel畜
這跟你的要求無關，但如果pirate需要按員工輸出紀錄的話，是不是考慮用VBA比較好？如果用VBA的話就不用寫這麼長的公式，可以一鍵生成紀錄。如果有需要的話我們可以再討論。
cock8506: 因為順著處理，允許我沿用這份名單單來作例子。
[期間限定] 根據列出日期，輸出出勤表 — 從社畜變成Excel畜
不知道你是不是想要這個效果，如果不對，我們可以繼續討論。
抱歉我沒有用到原噗HAM提到的3個函數。
另外，如果你要處理很多員工的資料，這樣在A1逐個輸入不會很麻煩嗎…？這個部分也是可以用VBA來處理的，有需要我們也可以討論一下。
cola7834: 問題一的部分可以看這篇
[期間限定] 怎樣算時差 — 從社畜變成Excel畜
因為看起來像家課，所以……我不知道你的課程是不是期望你這樣解。
問題二，剛巧我以前工作要計算，所以請直接參考這篇吧
計算每月、每週的第一天和最後一天 — 從社畜變成Excel畜
早安/雖然我要睡了，待會要上班所以是大約100位員工各自提供能上班的日子→錄入到日曆→再手動微調(像某些日子需要強制某些員工上班)……之類？
這樣我會想到1) 可以用GOOGLE FORM讓大家各自填，COCK再拿數據用公式/VBA錄入日曆比較快 2)如果可以讓每個員工接觸檔案，就直接寫個vba的user form讓他們自行錄入 3) 類似1/2的形式，整合100人的內容(格式一致)，用vba讀取數據錄入
基本上重覆動作的東西還是用巨集處理會比較省心，我也同意用勾的真的……不太好……
不建議用合併儲存格的方式顯示
這一部分我今天上班摸魚的時候再寫一寫，如果你有期望的格式提供的話，我可以到時嘗試配合
謝謝！我去試試
感謝噗主指點
[期間限定公開] [期間限定] 根據列出日期，輸出出勤表-2A — 從社畜變成Excel畜
[期間限定公開] [期間限定] 根據列出日期，輸出出勤表-2B — 從社畜變成Excel畜
想問個問題
如果想要把excel的日期改成自己想要的格式怎麼調
像是2023/3/3改成1120303這樣
我原本是用取代的，但是幾百筆用取代的也很久，而且常常不小心取代成錯的資料Orz
儲存格格式（快捷鍵Ctrl +1)可以解決你的問題嗎？
可以看一下這個
如果沒設定到可以加個擴充包
=text(year(A1)-1911,”0”)&text(A1,”MMDD”)
不知道這樣可以嗎？如果哪裡寫得不清楚，請跟我說一下，我再截圖
這邊卡在顯示有休紀錄那邊的函數，CSE之後會出現無法計算的警告框，但檢查後好像也沒有哪裡有遺漏，可以請您幫我看看嗎?
G4的函數內容=OFFSET(出勤表!$A$1,1,LARGE(IF((出勤表!$A:$A=有休確認!$B$2)*(出勤表!B:XFD="有"),COLUMN(出勤表!$1:$1),0),ROW()-3)-1)
那是因為這個公式如果套用總範圍(即A:A而不是A1:A30)，計算量太龐大，一般會處理不來
所以範例裡我只可以指定範圍才能處理
我想了一下，考慮到你的範圍去到XFD，覺得旅人可以考慮用VBA處理會比較快，如果你的工作/這個檔案允許你修改副檔名嗎？
目前先把公式再修正一下，原來旅人提供的公式，Offset應該是0不是1(即是前面$A$1之後的變數)，
第二部分是要把COLUMN內的公式補上欄位，即是$1:$1→$B$1:$XFD$1
但由於總範圍的處理太龐大，所以我改成這樣，旅人可以試一試︰
中間混了Indirect和Counta，是用來查收出勤表使用的最大行數=你的範圍，
用A行查找是因為A行每一個儲存格都有輸入(換言之，如果中間有空格，就要在COUNTA之上再加空格數)
注意，後面用了Indirect的部分，如果欄數追加(XFD以外還有追加)，就要手動修改
旅人看看這樣的調整可以接受嗎？
這邊不太會操作VBA所以有點擔心，晚點試試看噗主提供的修正，然後再來向您回報
可以參考一下這一篇
【公開】VBA101︰為甚麼要用VBA？普通人也能學得會嗎？ — 從社畜變成Excel畜
視乎實際想做到的效果，寫出來的巨集可難可易，如果只是我上述說的內容的話，個人覺得滿簡單和容易操作的
順帶一提，我剛剛算了一下，XFD欄是16384~44年的日期量...??
1.這邊有另一個問題，我在出勤記錄前面加了幾欄，想來計算指定月份的有休或公休等等的日數，不知道這邊的公式該怎麼寫
2.關於VBA的部分，這邊的電腦環境允許使用，但自己也只會錄製巨集的程度...。這個檔案是我一人在用，且每次只查找單一員工(主要是要看還剩幾天有薪假能用而已)，所以這邊的VBA可能比較不需要。
這邊會想用VBA做另一個部份的東西，就是經由出勤紀錄可以一鍵生成另一個固定格式的EXCEL文件，但我想這個部分會複雜很多，如果噗主有意願再來討論就好，謝謝
如果平時實際使用的話，我會把檔案設做手動計算→使用時才按f9讓它運算一次
1. 我想先確認一下，B1的儲存格是哪一種？
「2023年4月(文字)」「2023/04/01 但把儲存格設成"YYYY年M月」如果是前者，記得要改成後者那一種只有顯示日期/數值而不是文字
這個用COUNTIFS應該就可以用了，
今天是休假所以比較簡略，有疑惑可以再提出，我上班的時候再回覆
如果是這樣直接的做法的話，VBA應該不會很難，還有一些細項需要確認，如果可以貼上檔案，還有能詳述實際做法嗎？
如果不方便在這裡說下去，請考慮以下方法連繫︰
1) 噗幣小房間
2) BMC Requests
一鍵生成的時候直接套用模版，再把那張工作表輸出成檔
之前錄製完的巨集都會存放在personal 然後以隱藏式窗方式存在
但最近發現我只要關閉excel personal這個檔案也會跟著被關掉
下次再打開excel想使用巨集時 都需要另外再點開personal才可以使用巨集QQ
請問要如何設定 不用每次都要另外打開personal?
目前手上的excel版本沒法重視所以不能肯定回覆。不能把對應的巨集放在相應的檔案裡面嗎？
如果"personal"是用來中央管理巨集的檔案，或許考慮把personal的巨集設定在快速存取工具列……？這樣就算關掉了檔案也可能按鍵打開→運行巨集……？
這邊做了一個單字測驗的頁面(?)
A~H是不熟的單字內容，B欄是rand函數，C欄是單字書內該單字的號碼
K4:N18是會隨機出現的單字測驗
L4==index ($D$2:$D$116,rank(B2,$B$2:$B$116))
K.M.N欄則是用vlookup代出
1.想要能依照自己的需求設定隨機出現的範圍，例如想著重複習序號150~300的單字，K4:N18就會依照設定跑出對應隨機單字
2.測驗完單字我會依照熟悉程度標上good/soso/bad，現在都是另外筆記號碼，再用篩選功能一個一個記上，有點麻煩且耗時，請問有沒有方法可以一鍵登錄?
每次抽出來的亂數→先找地方貼上值(因為後面每次修改，亂數會重置)→每題答對後手動標示+1/0/-1 (如答對是+1, 答錯是-1, 除此為0)
→之後序號與標示貼在工作表上，像附圖A欄是問題號碼，附圖B欄是對應分數
1) 累積計算 =SUMIF(A:A,F1,B:B) 就這樣用SUM計算
換言之如果你沒有連續/長時間答對該題，就會一直是負數
2) 每次重置 =INDEX（B:B,MAX（IF(A:A=F1,ROW(A:A),0))) →CSE
留意要用CSE和INDEX/MAX後的括號是全形要手動改
這樣就只會計算最近一次的結果(假設你的表是往下更新)，會反映這題最後一列的結果，所以不會出現-2這些數字，而是只會-1/0/+1
好奇問，海盜是有甚麼原因覺得用EXCEL/試算表比用FLASHCARD類的APP/網站好嗎？
其實我以前還真的有寫過用EXCEL來溫習的巨集，不過一直沒空更新到BMC，要是你對巨集/EXCEL有興趣，請跟我說一下，我會嘗試加緊速度把它分享出來
1的部分，因為C欄的單字號是不連續的所以另外建了D欄給流水號，另在M2及M3加上希望的範圍號碼
M7=index ($E$2:$E$116,randbetween($M$2,$M$3))
還有個小問題是單字會重複出現
而且這邊之前的B欄亂數好像就沒用了，之前是用亂數來跑出隨機單字的，如果噗主的解決方案都不需要的話我再把這欄刪掉
然後我發現上面新增的D欄流水號好像沒意義XD
另外這邊回覆噗主問題，覺得用EXCLE來做複習，主要是可以用CTRL+F功能快速找到類似的單字去做註解跟記憶，FLASHCARD類的APP可能比較沒這個功能，另外就是自己比較適合用電腦學習，用手機容易分心
(如果有經常改變排序，但同時生字必須按這個次序的情況例外)
替代RANDBETWEEN的重覆問題，有2個方案，2個方案都是需要1行欄來做參考︰
1) 用UNIQUE抽選獨立號碼
照舊保留RANDBETWEEN，另外一欄用UNIQUE(範圍)，這樣就只會抽獨一無二的號碼出來，不過像附圖例子，預設有9個號碼，但扣掉重覆號碼後，能測驗的題目少於9題。
把B欄的RAND()改成IF，=if(and(row()>$B$23,row()<$C$23),RAND(),"")
這樣符合你的條件內的題目才會出現亂數
然後查找可以用vlookup或者index/match之類，再用sortn找首5個(例)的內容
=ArrayFormula(vlookup(SORTN($A$24:$A$33,5),$A$24:$B$33,2,0))
謝謝你的回覆，那作為ctrl+f，你可以考慮用hyperlink連結測驗的題目，直接跳到該當儲存格，那會不會比較方便? 方法是=hyperlink("#gid=？？？？？range=b"&MATCH(C24,$B:$B,0),"link")
問號的部分是對應你工作表連結gid最後的數字
這邊讓Good對應出1~999的原因是，希望可以在考題20題中隨機出現4~5題Good的單字(一方面預防Good的太久沒看忘了，一方面要是其他單字都不會至少還有幾題熟悉的
，保護玻璃心的部分)因此讓熟悉(1~999亂數)跟不熟(小於1亂數)做出區別，想碩這樣是不是能在N7欄寫出個甚麼東西....?
另外hyperlink這邊也有做嘗試了，覺得挺方便的，謝謝噗主提供!
先保留你的B欄亂數，不過公式改成這樣︰
=IF(A1="Good",IF(AND(ROW()>=$G$1,ROW()<=$G$2),RANDBETWEEN(6,20),0),IF(AND(ROW()>=$G$1,ROW()<=$G$2),RANDBETWEEN(1,10),0))
還有把""改成0，那是為了後面SORTN而改的。
原因是SORTN可以把陣列輸出，那就省掉了VLOOKUP/INDEX MATCH的部分
不過亂數的部分也被抽出來，可以視乎情況隱藏掉? 如果不喜歡也可以像以前一樣SORTN $B:$B，再補上VLOOKUP之類的函數。
後面的5是指抽首5項，2是可以省掉重複列的變數(這樣根本不用UNIQUE，我是白痴)，後面的$B:$B和0是指用B欄排序，並從遞減方式列出首5項內容
B欄式子不變
=iferror(ifs(and(row()>=$N$2,row()<=$N$3,or(A2="Soso",A2="Bad",A2="")),rand(),and(row()>=$N$2,row()<=$N$3,or(A2="Good")),randbetween(1,999)),"")
測驗單字固定20題，前4題為Good，後16題為非Good
N7=sortn($D$2:$F,4,2,$B$2:$B,0)
N11==sortn($D$2:$F,16,2,$B$2:$B,1)
N7欄永遠出現to+1的結果
(例如我這邊to是20，則只會出現21列的accept)
不知道這樣該如何解?
我病好再研究看看
pirate2699: 這邊看了你的公式，B欄算式是"" (空白)的時候，在SORTN遞減會被當成最大數處理，加上N欄的sortn公式是把整個B欄的內容一起處理才會出現BUG。為了保證sortn只處理想要的範圍，我建議用indirect處理，方法如下︰
把你原有的N7和N11的公式裡，屬於範圍的部分用Indirect來引用，缺點是往後如果你有新增欄，這兩個公式也一定要手動改(因為不是參照，所以不會自動修正)
例子︰$D$2:$F→修改成indirect("D"&$N$2&":F"&$N$3)
後面$B$2:$B也是像上面修改，把D和F改成B和B。
附圖例子參考︰
比較A表和B表相同/不同的公司名的六個方法︰（三）格式化條件 — 從社畜變成Excel畜
如果在E欄本來就有重複，可是還是要比對F欄有沒有重複的話……
建議你考慮移除重複，條件選E欄&F欄必須一致的話就可以了
比較A表和B表相同/不同的公司名的六個方法︰（二）移除重複 — 從社畜變成Excel畜
考慮到E和F欄都是一樣性質的內容，移除重複聽起來是最合適
假設E欄和F欄的內容並沒有關係，兩欄的性質不一樣，折衷可以用Match
示範圖如下
大概是這幾個方法吧，只是看這個圖，沒法判斷荔枝需要比較的目的和資料的性質，如果上面的方法覺得不貼題的話，也可以再多說一點要求和情境，那樣判斷會比較準確一點
大致是希望可以F欄只和E欄比，F欄自己不要跟自己比這樣
把36間/108間分開兩個工作表→兩邊分別用縣&市&校名連結在一起→(可以事先把連結後的校名比對一下有沒有重複, 沒重複的話)→用這個連結好的名字再用VLOOKUP查收出來
理論上同縣同市同校名的狀況應該很少
另一個方法是，假設36間的表不會有重覆，反過來用108間的表做Main，檢索有沒有對應36間的校名→篩選有找到的結果→用這個結果找重覆值/不找重覆值，另設一欄再查收縣市是否吻合
方法1是比較常見和簡便的做法，而方法2是以數據量較多的列表來做主體，但缺點是如果108間校名裡面不保證包含那36間的內容，那最後還是得找找少了哪間學校的資料沒找到(這部分我會用移除重複/match之類的方式彌補)
以上是我先簡略介紹做法的內容，有需要做法的話可以告訴我
有辦法在vlookup或xlookup的時候讓excel判定B-L202=BL202、CP-L101-1=CPL101-1嗎
這個情況的話我喜歡用index/match，先用substitute把減號去掉，再用match找列數(即是上圖例子)
之後再補上Index就可以找到對應的內容
補上公式，留意X號是全形
F2 =INDEＸ(C:C,MATCH(SUBSTITUTE(E2,"-",""),SUBSTITUTE(A:A,"-",""),0))
已經確認排序基準的儲存格是沒有空格的
我要排序但是這兩個沒有照A->Z排序
如果用ISTEXT和ISNUMBER去驗證就會看到後面2個是文字不是數值
排序後會變成這樣
想請問噗主，例如工作表名稱為1日、2日…，B2公式為'1日'!AB$32，向下填滿時，怎麼讓B3自動變成'2日'!AB$32，B4變成='3日'!AB$32
目前直接向下填滿會變成都是'1日'!AB$32
如果範圍不是絕對參照，之後有可能增刪欄列而變化的話，這個部分需要手動修改，EXCEL不會自動跳
如果需要配合自動調整範圍，進階點的方法是把範圍改成ADDRESS像下圖
=INDIRECT(A1&"!"&ADDRESS(2,2)&"")
ADDRESS裡的2,2，利用LOOKUP系的函數等，取得欄數列數，那日後有增刪欄列也會自動調整
舉例來說我的表格有A產品 B產品 C產品 他們的規格在甲工作表
我做了一個簡單統計庫存的乙工作表，有入庫/拿出會記錄在這個表格
現在用sumif反應在甲工作表內
但是並不是所有產品都有入庫拿出的紀錄，只要沒有資料sumif都會回傳0，那我有些產品是真的沒庫存了我無法辨別
想請教如何寫來區分無資料與0
謝謝..........
這是甲工作表，有規格和總庫存量計算
這是乙工作表，入庫/拿出的統計在這邊做紀錄
透過SUMIF回傳，但像編號2 A02，在乙工作表是沒有資料但仍然回傳0，會造成統計上的困擾
E3(跟你截圖的儲存格位置一樣) =IF(COUNTIF(乙!$B:$B,B3)=0,"沒有",SUMIF(乙!$B:$B,B3,乙!$C:$C))
如果乙表沒有出現過商品，就直接反映「沒有」，有出現過才用你的公式計算庫存
我有複數個指定的日期 自動比對(裝置時間orGMT+8之類的)如果當天是指定日期時 賣價那一欄變色?
比方說這樣指定時
今天是5/25所以有變黃色 到了明天(5/26)就換成另一個顏色(或無顏色) 再到後天(5/27)因為是指定日期了所以又變成黃色
下圖參考是用$D$2是測試用，把$D$2換成TODAY()就可以指定當天日期
Ex: 設定好儲存格格式為文字 > 貼上089.0040（此時正常顯示為文字）> control f 取代條件：刪除.之前的東西（*.）> 最後顯示40（但預期為0040）>我崩潰
取代的時候結果不要空白，而是打「'」(即是平時用儲存格，為了不讓文字變成數字/公式時，會在前面補上的引號」
公式都沒有錯誤，兩個工作表的文字也都一樣，但卻顯示不出資料TT
還有什麼方法可以檢查錯誤嗎？
接下來要解釋的比較長，請有心理準備。
現在你的公式意思是︰在「複製」C欄裡，找尋符合B欄某列的內容，找到後輸出第一個欄(即是C欄)的內容。如果沒找到，就輸出0。
如果要修改你的公式，可以以下面兩個方式進行︰
第一、VLOOKUP只能用左至右檢索，如果以你目前的做法比較偏向右至左，
VLOOKUP的用法你可以參考這一篇︰
比較A表和B表相同/不同的公司名的六個方法︰（五）以A表為基礎設計搜索表 — 從社畜變成Excel畜
這個組合可以彌補在XLOOKUP面世之前，沒有右往左檢索的問題。
在這裡不介紹XLOOKUP是因為我較少用，手邊的設備不方便跑XLOOKUP，就不特別介紹了。
在提供正確答案之前還必須提示原本公式三個需要注意的地方︰
1) 如果要指代B欄某列，不需要用INDIRECT，直接用B2→之後往下來就可以了。如果會往右拉，那就鎖B欄(即是$B2)就可以。
2) 選取範圍要有鎖範圍的好習慣，如果不會往右拉那可以不鎖。
介紹了上述兩個方法，就要解釋怎麼用︰
一、VLOOKUP的話必須把
=IFERROR(VLOOKUP(B2,複製!C:AY,5,0),"")
=IFERROR(INDEX (複製!C:C,MATCH(B2,複製!G:G,0)),"")
以上，有看不懂的地方請說一下~
我的outlook 有兩個帳號在裡面
用Vba發信的話是使用預設帳號1，但我現在享用帳號2寄送應該要怎麼寫代碼呢？
噗主找到這個網站寫的很清楚很好懂
感謝你
比方577.92→變成580
442.47→440
這樣
下記參考：
永遠的20歲︰是Round? Roundup? Rounddown? — 從社畜變成Excel畜
噗主您好，我最近要辦電影包場
請問有沒有辦法從「方便參與的時間」x「參加人數」，計算出某天的參與人數？
以前兩個為例，在表單後台只會看到選8/19有兩個，實際上是四個人可以參加
不知道這樣形容清不清楚(?)，謝謝噗主
之後公式填SUMIF，選擇選項的部份前後加上星號，就可以SUM到人數了
保固校驗日期
2023/3/12~2023/12/31
5/28/2022~9/15/2023
2023.07.01~2024.06.30
之後再判斷日期是哪一種，再用公式改回來
=IFERROR(IFERROR(TEXT(DATE(LEFT(A2,4),MID(A2,FIND("....
你看看用paste裡的公式能不能用到…我預設的日期格式是yyyymmdd，你把這部分改成你想要的日期格式就可以了(如yyyy/mm/dd)
出現#VALUE!的日期格式有這三個，不太明白為什麼只有他們不行
「10/15/2022~9/15/2023」
「9/16/2023~2024/6/13」
「2022/2/23-2023/1/3」
paste裡的公式看起來是直接抓A欄的值，所以沒有拆解成BCD欄好像也可以運作嗎
我因為要一欄處理它才把公式合併起來
抓A欄是因為我本來是用A欄做參照（就是之前的附圖）
統一把A欄的部分改做BCD欄就可以運作了
你舉的例子和對應不到的部分，我要慢點回公司/回家才能看看，麻煩請多等半天（左右）
首先解釋一下原本公式的用意，是假設你的文字(日期範圍)只有三種可能性而寫出來的，分別是︰
yyyy/mm/dd~yyyy/mm/dd
mm/dd/yyyy~mm/dd/yyyy(不過這部分我寫得好不，導致起始日的mm讀不到2位數字的月份)
https://yyyy.mm.dd~https://yyyy.mm.dd
可以參考這個圖︰
所以在這三個模式以外的文字不能適用於這個公式。
[續]
「10/15/2022(略)」見上記，「9/16/2023」的問題是由於年月日的排序不一致，而「2022/2/23」這部分是因為分割起始日和完結日的符號不是「~」而是橫線所致。針對第二次舉例的部分，用紅字修正後，便能反映出來。(見附圖)
附圖C欄是公式套用的結果，DGJ欄是對應上記3種日期模式的寫法，EFHIKL欄是該當寫法的起始日和完結日的部分，黃色標記表示它們成功對應在哪部分。
[續]
1) 這個拆解日期範圍的工作有多常用和頻繁，資料有多少
→很常用的話，那直接寫個轉換用的工作表、MACRO吧
[續]
→年月日的排序這麼多，如果掌握不到，那就不這樣寫公式了，像上記附圖般，盡量多用些欄位拆開可能比較好(我預想之所以旅人有這個煩惱，就是因為輸入的人都是照自己喜歡寫才會出現這個問題...?)
→我的話會視乎需要解決的頻率和數量，會找折衷方法(比如說如果年份只會限定在2022~2024年，可能就會用這個做參考來寫公式等等)(如果量很少的話，基本上公式判斷不到的部分我都直接偷懶手改)
→分割標記不統一的話，要不直接replace all(只有~和-之分)，判斷不到的話就直接資料剖析數次來拆開
[續]
這樣拆不開的就是非使用「~」的日期標記(也因為拆不開，B欄是空欄，這部分用篩選之類找出來就好)
至於拆出來後，如果不是日期(即是附圖選取的部分)，再另外對應就可以了(要找出來也很容易，用ISNUMBER找，非日期是FALSE)
大致上是這樣吧，這段時間我也再想想怎樣做
這裡預想的是只有YMD和MDY這兩個情況，還有會用「/」或「.」做分隔的情況
如果還有2023-08-04這種情況，那就再套一個SUBSTITUTE就好
公式︰
=IFERROR(IF(ISNUMBER(B3),TEXT(B3,"YYYYMMDD●"),TEXT(DATEVALUE(RIGHT(B3,4)&"/"&LEFT(B3,FIND("/",B3,4)-1)),"YYYYMMDD●")),TEXT(DATEVALUE(SUBSTITUTE(B3,".","/")),"YYYYMMDD●"))
會有這樣的狀況是每家合作廠商給的日期格式都不一樣，
雖然我們有給指定格式但沒有人要配合。值得慶幸的是合作廠商就這幾家，日後應該就也只有這幾種格式如果是分開不同廠商的話，那收到資料時用資料剖析先改掉格式，之後才和其他廠商的併起來整理會比較好
這次的問題是抓取欄位值的時候，該欄空白要怎麼自動抓取下一欄直到有值呢？
舉例：聯絡人需要填入最大的長官，有處長就帶入處長，沒有處長就帶入副處長，副組長也沒有就帶入一級主管
拜託噗主了
我目前用了
=IF(ISBLANK(B3),TEXT(D3,0),TEXT(B3,0))
這樣可以在處長無人的時候帶出副處長的名字於聯絡人欄位
可是到這裡我就寫不下去了
不知道怎麼讓公式繼續判斷副處長也無人的時候改帶入一級主管的名字
=MIN(IF(LEN(A2:C4)>0,ROW(A2:C4)))
帶出答案的欄(COLUMN)是=MIN(IF(LEN(A2:C4)>0,COLUMN(A2:C4)))
接下來是看旅人想用OFFSET還是INDIRECT(ADDRESS()) LOCATE它的位置，
ADDRESSS的話把這兩條公式放在相應位置→CTRL+SHIFT+ENTER就可以了
如果是OFFSET就要兩邊-1(也是要用CSE)
我覺得我可能表達上有錯誤，再重新闡述一次問題。每一行都是獨立的，然後我要從各中撈出最優先的聯絡人，所以沒處長就抓副處長，沒副處長就抓一級主管
我現在是用
=IF(ISBLANK(B2),TEXT(C3,0),TEXT(B3,0))
這樣可以在處長無人的時候帶出副處長的名字於聯絡人欄位
所以阿明和阿富我能帶出來了，但阿花我還是不知道怎麼帶出來
不好意思，那我回家改一下，預定接近12點
原理是找COLUMN最小的那行就是靠最左的負責人(用LEN判斷有沒有內容, 當然ISBLANK也可以)，之後再用OFFSET移動
我粗略看過你的疑問，可是我沒空看影片
可以指出你的問題是參考哪個時間點的教學、用了哪一個範例檔案、發生這個問題時的步驟是怎樣嗎？
我會嘗試重現你的問題，再看看是跟教學有哪裡不一樣
請問要怎麼讓排序是照數字大小排列，用排序(A到Z)排出來不是由小到大
抽數字的部分，如果只有E和F的話，可以用substitute換走，或者replace成數字0(後者方法是推論)
再用if條件式找有沒有頓號，沒有就輸出上記替換後的數字，有就輸出頓號前的數字
旅人覺得如何？
先找有沒有頓號，然後用MID抽數字(因為前端英文字母是固定一位)
最後轉換成數字就可以排序了
=NUMBERVALUE(IFERROR(MID(A1,2,FIND("、",A1)-2),MID(A1,2,LEN(A1)-1)))
之後用同樣方式，用頓號找出複選座位的部分作標示
=IFERROR(IF(FIND("、",A1),">1位"),"")
例：E8→E08，以此類推
用TEXT補不進去QQ
如果有兩個excel檔想要比較版本
但公司的程式是2010的
有辦法比較嗎？
我們想知道客戶修改了哪裡
excel檔裡面有很多個工作表
都是文字沒有圖片之類的檔案
感謝!!
能想到的方法大概有3個，其中2個可以參考這個連結
第3個方法是考慮修改的地方除了是數值，還有格式的情況下，可以寫巨集比較
剛好找資料時看到這一個ADD-IN，或許旅人可以試試看
https://www.addictivetips.com/...
第一個方法「視窗比對」可能不行，
因為每一個工作表內雖然都是文字但非常多欄也非常多行
視窗可能需要縮小到25%以下才能將整個工作表完整納入的程度；
第三個方法則是因為我們公司無法自行下載或安裝軟體(資安疑慮)，因此很遺憾的也不行
明天上班我會先來嘗試第二個方法是否可行
再次感謝噗主!!!!
打開那個xls就應該可以用，如果這樣不行的話那寫巨集也應該不許用⋯⋯
還有第4個方法就是用移除重複，但這個方法只應用於像訂單數據這樣的表格資料才能用，因為不知道GOOSE的檔案是怎樣的東西，如果是訂單的話也許就能用。
另外針對這個煩惱的對策是用追蹤修訂(不確定2010有沒有的功能)和鎖定儲存格(限制客戶可以輸入的部分, 減少確認的範圍)
需要噗主的指點⋯非常謝謝您！
順便截個自己研究很久寫很醜的引號的部分會換成文字，不過我猜你是為了測試才這樣做？
還有大於5500是不包括5500嗎？(我現在是寫大於等於, 要是不對請自行修改)
修正如圖，盡量符合旅人要求是數字範圍內，所以不符合範圍的東西都一律ERROR處理
因為那個5500可能還會再修改（不太確定）但如果知道這大概要怎麼寫的話我應該可以寫出來的
我老闆太善變了⋯
順帶一提我們的不是數值(日期或統計資料等)也沒有公式
只是很多很長的字句
(為什麼不寫在word裡面QQ非常非常感謝噗主!!!!
我也有用過excel去對比很長的字句
旅人你不孤單，抱抱
因為年末會去旅行，所以只能預備一小部分的聖誕禮物(excel教學)在剩下來的幾天 Buy me a coffee 給大家使用，暫時預備了的只有這些︰
本來應該一併更新IG，可是我太遲準備了，所以嗯……
明年回來就會開2024年的Excel台，到時會努力把今年的存貨都放出來