【導(dǎo)讀】《excel問題集合》程香宙編輯

　　

【正文】 .................................. 60 SUMIF函數(shù) ................................................................................................................... 61 怎么能快速的將兩個(gè)單元格的內(nèi)容互換 .......................................................................... 61 如何能到兩個(gè)時(shí)間段的 17:008:00 小時(shí)數(shù) ? ..................................................................... 61 如何在單元格返回工作表名稱 ........................................................................................ 62 如何在輸入數(shù)字的加減乘除按 ENTE后能在另一單元格自動(dòng)出現(xiàn)計(jì)算數(shù)值 ..................... 62 有 A1， B1， C1， D1 四個(gè)單元格 D1的值要隨著Ａ１的變化而變化 ................................ 62 對 A列不重復(fù)的數(shù)值計(jì)數(shù) .............................................................................................. 63 永恒的求和 .................................................................................................................... 63 如何使用 EXCEL 進(jìn)行動(dòng)態(tài)跨表取數(shù) ............................................................................... 63 如何使用對照表 ............................................................................................................. 63 如何在單元格返回工作表名稱 ........................................................................................ 64 何在一列數(shù)據(jù)中統(tǒng)計(jì)限定范圍的數(shù)據(jù) .............................................................................. 64 《 excel 問題集合》程香宙編輯 1 引用問題 我在 excel 中想實(shí)現(xiàn)這么一個(gè)功能，如單元格 D12 有一個(gè)數(shù)據(jù)是 4，現(xiàn)在我想引用 A4 的數(shù)據(jù)，但 4 是由 D12提供的，即如何實(shí)現(xiàn) A4=A(D12)。也就是，在 Excel 中， A7 單元，能否實(shí)現(xiàn)把后面的數(shù)字用算式來代替，如 A(3+4)，或者是單元格的嵌套， A(D12），懇請高手解答。 解答：① =indirect(aamp。d12) ② 我的想法：借一個(gè)單元格如 B4 用，鍵入 =Aamp。D12 在需引用的單元格鍵入 =INDIRECT(B4) 即可，第二個(gè)： C4 是 3， C5 是 4 B4=A“ amp。C4+C5 ,同①。 如何在一列列出工作薄中的所有 表 (表名無規(guī)律 ) 以下宏將在 a 列傳回所有工作表名稱。 (很實(shí)用 ) Sub Maco1() For i = 1 To Cells(i, 1) = Sheets(i).Name Next End Sub 18 位身份證號碼輸入的幾種解決方法 相信在 EXCEL 這個(gè)軟件中如何輸入 15 位或 18 位身份證號碼的問題已經(jīng)困撓了許多人，因?yàn)樵?EXCEL 中，輸入超過 11 位數(shù)字時(shí)，會自動(dòng)轉(zhuǎn)為科學(xué)計(jì)數(shù)的方式，比如身份證號是： 123456789012345，輸入后就變成了： +14,要解決的方法有 非常非常 ......多種哦，呵呵，現(xiàn)在為大家說幾種比較簡單快速的方法： 在 A1 單元輸入號碼時(shí)，在前面先輸入’號，就是：’ 123456789012345,這樣單元格會默認(rèn)為該單元為文本方式，會完整顯示出 15 個(gè)號碼來，而不會顯示那令人討厭的科學(xué)計(jì)數(shù)方式來了。 如果已經(jīng)輸入了大量的號碼，全部以科學(xué)計(jì)數(shù)顯示的話，而又懶的按照上面的方法一個(gè)一個(gè)的重新輸入的話，也有這個(gè)好方法哦，比如已在單元 A1： A100 輸入了號碼，按以下步驟做：》選擇單元 A1： A100》單擊鼠標(biāo)右鍵，設(shè)置單元格式》選擇自定義，在‘ 類型’中輸入‘ 0’即可，輕松搞定，呵呵！！ 還有一種用函數(shù)來解決的方法：在 A1： A100已經(jīng)輸入大量的號碼時(shí)，在 B1單元中輸入： =trim(39。 39。a1),注意兩個(gè)’之間是空格，這個(gè)公式的意思 :先在 A1 單元 15 位號碼前加個(gè)空格符號，然后用 trim 這個(gè)函數(shù)把空格符號去掉。輸完后， B1 單元也是顯示出完整的 15 位數(shù)字了。 用 countif算出數(shù)值大小在 50~60 之間的單元格個(gè)數(shù) 解答：①你可以綜合一下呀 ! =countif(a1:a9,=50)countif(a1:a9,60) ② {=SUM((a1:a960)*(a1:a950))} ③ 為什么 {=SUM((a1:a960)*(a1:a950))}可以求出符合條件的個(gè)數(shù)，按理后面應(yīng)再加一待求和區(qū)域如B1:B9等等，不知數(shù)組計(jì)算的內(nèi)部機(jī)制是怎樣的，不知誰能給個(gè)解釋 ,謝謝！！ ④ 這個(gè)數(shù)組公式是這樣運(yùn)算的： a1:a9 區(qū)域的數(shù)值分別與 60 比較。假如 a1:a9 的數(shù)依次為15,25,35,45,55,65,75,85,95. 那么 (a1:a960) 返回{true,true,true,true,true,false,false,false,false} ，同理 (a1:a950) 返回｛ false,false,false,false,true,true,true,true,true} 。 然 后 (a1:a960)*(a1:a950) 則 返 回{false,false,false,false,true,false,false,false,false}。因?yàn)?*表示 and 的意思。只有當(dāng) and 的對象都為真時(shí)結(jié)果才為真。 excel 里用 1 表示 true。0 表示 false。有時(shí)需要將 true 或 false 轉(zhuǎn)換成數(shù)值，可以 *1 或 +0 《 excel 問題集合》程香宙編輯 2 幾個(gè)工作表生成一個(gè)打印文件一次打印 解 答： 先按 Ctrl 鍵 + 其它要一起打印的工作表 就會成為一個(gè)群組 ,打印的頁數(shù)可延續(xù)到下一個(gè) Sheet 自動(dòng)計(jì)算應(yīng)收款滯納金 要求在給定的應(yīng)收日期、滯納金率、當(dāng)前日期（自動(dòng)取）的基礎(chǔ)上自動(dòng)計(jì)算出應(yīng)收滯納金。 解答： =(DATEDIF(應(yīng)收日期 ,NOW(),d))*滯納金率（每天） *應(yīng)收金額 SUM 函數(shù)難題 A B 234 230 12 10 13 如何用函數(shù)計(jì)算出下面幾個(gè)數(shù)據(jù) 1。 A 和 B 都有數(shù)據(jù)且 AB 的個(gè)數(shù) 2。 A 欄在相應(yīng)的 B 欄有數(shù)據(jù)的情況下的數(shù)據(jù)總和，例如 A1 對應(yīng) B1 有數(shù)據(jù)， A3 對應(yīng) B3 有數(shù)據(jù)，則求 A1+A3的和。希望都能用一個(gè)函數(shù)求出所要的結(jié)果。謝謝 解答： =SUM(($A$1:$A$10)*($B$1:$B$10)*($A$1:$A$10$B$1:$B$10)) =SUM(($A$1:$A$10)*($B$1:$B$10)*($A$1:$A$10$B$1:$B$10)*($A$1:$A$10+$B$1:$B$10)) 記得輸入公式后要按 ctrl+shift+enter 工作表中的小寫字母都變成大寫 解答：請運(yùn)行以下程序： (測試通過 ) Sub ConvertToUpperCase() Dim Rng As Range Worksheets(Sheet1). For Each Rng In If = False Then = UCase() End If Next Rng End Sub 用 COUNTIF計(jì)算整個(gè) B列中含有“ F”這個(gè)字符的單元格的個(gè)數(shù) c1=COUNTIF(b1:b130,b129)，（ b129 單元格內(nèi)的字 符是“ F”），問題是隨后在向表格中添加新行 13 13133.....之后， c1 單元格中公式統(tǒng)計(jì)的結(jié)果還是 1129 行的，怎么才能讓 c1 中實(shí)時(shí)顯示添加新數(shù)據(jù)后的統(tǒng)計(jì)結(jié)果 ? 解答： c1=COUNTIF(b:b,b129) 自動(dòng)記錄出車情況 現(xiàn)有一表格，每天記錄 50 輛不同車號的車輛的“出車?yán)锍獭焙汀盎貛炖锍獭?，需要打印一清單，自?dòng)統(tǒng)計(jì)每輛在庫車輛的當(dāng)前里程，請問該如何做 (結(jié)果放在其它 sheet 中，為方便顯示這里和數(shù)據(jù)放在一個(gè) sheet《 excel 問題集合》程香宙編輯 3 中了 ) ？ 解答：可以用數(shù)組公式 : 假設(shè) 此處數(shù)據(jù)不斷增加 所在工作 表的名字為 SHEET1. 在 此處需要更新 所指的單元格內(nèi)輸入公式=MAX(IF(OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$65536))=A2,OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$B$2:$B$65536)))),按 CTRL+SHIFT+ENTER 結(jié)束 ,之后向下填充公式 . 整點(diǎn)記錄坐標(biāo)值 我在監(jiān)測一個(gè)點(diǎn)的電壓值，每天正點(diǎn)記錄一次，這樣每天就產(chǎn)生了 24 個(gè)值，現(xiàn)要進(jìn)行一個(gè)月，共產(chǎn)生了720 個(gè)值。根據(jù)這 720 個(gè)值，用 EXCEL 作一個(gè)圖表，橫坐標(biāo)是時(shí)間，單位是 天，縱坐標(biāo)是測量值。難點(diǎn)：橫坐標(biāo)每個(gè)刻度為一天，如設(shè)分類軸刻度線之間的分類數(shù)為 24 的話，橫坐標(biāo)每個(gè)刻度下顯示為： 1 25 49，而其單位為天，那不就成了第 1 天過了，就到了第 25 天？如設(shè)分類軸刻度線之間的分類數(shù)為 1 的話，可是我每天有 24 個(gè)值？ 解答：我試驗(yàn)了一下，發(fā)現(xiàn)還是能夠解決的。橫坐標(biāo)為時(shí)間，每天 24 小時(shí)，一個(gè)月 30 天，共計(jì) 720 個(gè)數(shù)據(jù)，縱坐標(biāo)為數(shù)值，這樣做出來的表格非常長，數(shù)據(jù)這樣排，第一列（ 0 小時(shí)， 1 小時(shí)， ......23 小時(shí)， 0小時(shí)， 1 小時(shí) ,.....23 小時(shí)，）這樣總共重復(fù) 30 天，第二列為每天 24 個(gè)數(shù)據(jù)，做折線圖（第四種），先按照向?qū)б徊讲阶鱿氯?，刪除第一個(gè)系列，就可以了。做出來的圖像橫坐標(biāo)（分類軸）要調(diào)整，刻度調(diào)整為標(biāo)簽間的分類數(shù)為 1，就可以達(dá)到目的！剩下可以手工在每天的開頭處加上日期（可以用文本框），不知道這樣解決可以嗎？ 請問如何把在 Excel 中繪制的曲線圖表另存為 jpg 或 gif 格式的圖片 解答：選定圖表部分，復(fù)制它，打開可以編輯 jpg 或 gif 格式的軟件，然后粘貼保存為 jpg 或 gif 格式文件即可。 round 函數(shù) 解答：不要用工具條上的顯示小數(shù)位數(shù) 來控制小數(shù)位數(shù)，它只是顯示這幾位，實(shí)際還是按原來的數(shù)計(jì)算。如： ，你顯示兩位，為 ，而實(shí)際他是按 去計(jì)算的，要用 round 函數(shù)， round(數(shù)據(jù) ,2)即可。如果怕繁的話，可在選項(xiàng)里設(shè)置按顯示計(jì)算，呵呵，試試看。 解答 2：我是做財(cái)務(wù)工作的，這一點(diǎn)有較深的體會 就是無論用什么函數(shù)都要在最外面加上 round(),不然的話因?yàn)槟且粌煞皱X定會被領(lǐng)導(dǎo)狠狠說一頓 再有兩條經(jīng)驗(yàn) 如果準(zhǔn)備作大一些的表格，盡量不要使用 “合并單元格” 以免以后使用函數(shù)麻煩； 要分清你作的表格主要是 使用來存儲數(shù)據(jù)，還是用來打印輸出，如果想兩者兼顧，小一點(diǎn)的表格還可以，大了就不好辦了。 工作表引用 比如說現(xiàn)在 Sheet2!a1 引用 Sheet1!a1,Sheet3!a1 引用 Sheet2!a1，那么有什么辦法可以表示 Sheet(n)!a1引用 Sheet(n1)!a1。我是 Execl方面的菜鳥，希望各位大蝦能給我解決的方法或者思路也行啊。 解答 1：用 (n).Cells(a, 1) =Sheets(n1).Cells(a, 1) 又問： = =SUM(RC[2]:RC[1],_______!R[1]C) .在 _____處，我應(yīng)該怎么填，才可以得到上一張表格？ 答：在空格處寫上： sheets() . 注意如果當(dāng)前工作表是第一張（即 ＝ 0）時(shí)會出錯(cuò)。 或： =INDIRECT(sheetamp。A1amp。!$A$1) 《 excel 問題集合》程香宙編輯 4 匯總 有這樣一個(gè)問題，學(xué)校里共有 24 個(gè)科目（每個(gè)科目都有其代碼，每門科目都有它相應(yīng)的教材費(fèi)），但學(xué)生最多只能選其中４門科目，那么我在一個(gè)表里每個(gè)記錄中存儲 學(xué)生所選的４門科目的代碼，并要在該表里匯總４門科目的總教材費(fèi)，怎么匯總。（各門科目代碼及對應(yīng)的教材費(fèi)存在另一個(gè)表里）。 解答：你可以這樣： 1，表 2： 序號 科目 教材費(fèi) /1 語文 120 /2 數(shù)學(xué) 100 /3